

CREATE TABLE `agent_profiles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `mobile` VARCHAR(50),
  `address` TEXT,
  `id_proof_path` VARCHAR(255),
  `profile_edit_pending` TINYINT(1) DEFAULT 0,
  `status` ENUM('pending','active','suspended','closed') DEFAULT 'pending',
  `approved_at` DATETIME NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `agent_companies` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `company_name` VARCHAR(255),
  `company_type` VARCHAR(100),
  `company_address` TEXT,
  `gstin` VARCHAR(50),
  `reg_docs` JSON,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `vehicles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `vehicle_type` VARCHAR(150) NOT NULL,
  `max_capacity_adults` INT NOT NULL,
  `max_capacity_with_children` INT NOT NULL,
  `active` TINYINT(1) DEFAULT 1,
  `sort_order` INT DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `vehicle_package_rate` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT NOT NULL,
  `daily_basic_rate` DECIMAL(10,2) NOT NULL,
  `extra_km_rate` DECIMAL(10,2) NOT NULL,
  `included_km_per_day` INT DEFAULT 100,
  `valid_from` DATE NOT NULL,
  `valid_to` DATE NOT NULL,
  `remarks` VARCHAR(255),
  `active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `vehicle_transfer_rate` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT NOT NULL,
  `base_rate` DECIMAL(10,2) NOT NULL,
  `base_km` INT NOT NULL,
  `extra_km_rate` DECIMAL(10,2) NOT NULL,
  `valid_from` DATE NOT NULL,
  `valid_to` DATE NOT NULL,
  `remarks` VARCHAR(255),
  `active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `routes` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255),
  `approx_km` INT,
  `default_days` INT DEFAULT 1,
  `active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `pickup_zones` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `zone_name` VARCHAR(191),
  `latitude` DECIMAL(10,7),
  `longitude` DECIMAL(10,7),
  `radius_km` DECIMAL(6,2),
  `active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `enquiries` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `agent_id` INT NOT NULL,
  `type` ENUM('multi-day','one-way') NOT NULL,
  `route_id` INT NULL,
  `pickup` VARCHAR(255),
  `pickup_lat` DECIMAL(10,7),
  `pickup_lng` DECIMAL(10,7),
  `drop` VARCHAR(255),
  `drop_lat` DECIMAL(10,7),
  `drop_lng` DECIMAL(10,7),
  `travel_date` DATE,
  `days` INT DEFAULT 1,
  `passengers_adult` INT DEFAULT 1,
  `passengers_children` INT DEFAULT 0,
  `vehicle_type_id` INT,
  `estimated_km` INT,
  `total_amount` DECIMAL(12,2),
  `status` ENUM('pending','approved','confirmed','rejected') DEFAULT 'pending',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `google_api_settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `maps_js_key` VARCHAR(255),
  `places_key` VARCHAR(255),
  `distance_matrix_key` VARCHAR(255),
  `geocoding_key` VARCHAR(255),
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `system_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `actor_type` ENUM('admin','agent'),
  `actor_id` INT,
  `action` VARCHAR(255),
  `meta` JSON,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
