-- Transport AI Planning module (AI Route Optimization + AI Scheduling Assistant)
-- Run against your ERP database
-- Safe to re-run: IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

-- ---------------------------------------------------------------------------
-- AI settings (singleton row)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_ai_settings` (
    `id`                        TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `fuel_price_per_litre`      DECIMAL(10,3)   NOT NULL DEFAULT 0.200,
    `cost_per_km`               DECIMAL(10,3)   NOT NULL DEFAULT 0.050,
    `consumption_car`           DECIMAL(6,2)    NOT NULL DEFAULT 8.00,
    `consumption_van`           DECIMAL(6,2)    NOT NULL DEFAULT 12.00,
    `consumption_truck`         DECIMAL(6,2)    NOT NULL DEFAULT 25.00,
    `consumption_bus`           DECIMAL(6,2)    NOT NULL DEFAULT 22.00,
    `consumption_motorcycle`    DECIMAL(6,2)    NOT NULL DEFAULT 4.00,
    `consumption_other`         DECIMAL(6,2)    NOT NULL DEFAULT 12.00,
    `driver_max_daily_minutes`  INT UNSIGNED    NOT NULL DEFAULT 480,
    `pickup_service_minutes`    INT UNSIGNED    NOT NULL DEFAULT 3,
    `arrival_buffer_minutes`    INT UNSIGNED    NOT NULL DEFAULT 15,
    `road_factor`               DECIMAL(5,2)    NOT NULL DEFAULT 1.35,
    `average_speed_kmh`         DECIMAL(6,2)    NOT NULL DEFAULT 40.00,
    `osrm_base_url`             VARCHAR(255)    NOT NULL DEFAULT 'https://router.project-osrm.org',
    `llm_provider`              ENUM('none','openai') NOT NULL DEFAULT 'none',
    `llm_api_base_url`          VARCHAR(255)    NULL,
    `llm_api_key`               VARCHAR(255)    NULL,
    `llm_model`                 VARCHAR(120)    NULL,
    `updated_by`                BIGINT UNSIGNED NULL,
    `updated_at`                DATETIME        NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `transport_ai_settings` (`id`, `updated_at`)
SELECT 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `transport_ai_settings` WHERE `id` = 1);

-- ---------------------------------------------------------------------------
-- Employee pickup points (geo coordinates for workforce transportation)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_employee_pickups` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id`           BIGINT UNSIGNED NOT NULL,
    `address`               VARCHAR(255)    NULL,
    `latitude`              DECIMAL(10,7)   NOT NULL,
    `longitude`             DECIMAL(10,7)   NOT NULL,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `notes`                 TEXT            NULL,
    `created_by`            BIGINT UNSIGNED NULL,
    `updated_by`            BIGINT UNSIGNED NULL,
    `created_at`            DATETIME        NULL,
    `updated_at`            DATETIME        NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `employee_id` (`employee_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Contract transport sites (destination + default shift per contract)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_contract_sites` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `contract_id`           BIGINT UNSIGNED NOT NULL,
    `transport_location_id` BIGINT UNSIGNED NOT NULL,
    `default_shift_id`      BIGINT UNSIGNED NULL,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `notes`                 TEXT            NULL,
    `created_by`            BIGINT UNSIGNED NULL,
    `updated_by`            BIGINT UNSIGNED NULL,
    `created_at`            DATETIME        NULL,
    `updated_at`            DATETIME        NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `contract_id` (`contract_id`),
    KEY `transport_location_id` (`transport_location_id`),
    KEY `default_shift_id` (`default_shift_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Distance / duration cache (avoids re-querying the routing provider)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_distance_cache` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `origin_lat`            DECIMAL(8,4)    NOT NULL,
    `origin_lng`            DECIMAL(8,4)    NOT NULL,
    `dest_lat`              DECIMAL(8,4)    NOT NULL,
    `dest_lng`              DECIMAL(8,4)    NOT NULL,
    `distance_km`           DECIMAL(10,3)   NOT NULL,
    `duration_minutes`      DECIMAL(10,2)   NOT NULL,
    `provider`              ENUM('osrm','haversine') NOT NULL DEFAULT 'osrm',
    `fetched_at`            DATETIME        NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `pair` (`origin_lat`, `origin_lng`, `dest_lat`, `dest_lng`),
    KEY `fetched_at` (`fetched_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- AI transportation plans (draft -> pending_approval -> approved / rejected)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_ai_plans` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `title`                 VARCHAR(200)    NOT NULL,
    `plan_start_date`       DATE            NOT NULL,
    `plan_end_date`         DATE            NOT NULL,
    `status`                ENUM('draft','pending_approval','approved','rejected') NOT NULL DEFAULT 'draft',
    `total_routes`          INT UNSIGNED    NOT NULL DEFAULT 0,
    `total_employees`       INT UNSIGNED    NOT NULL DEFAULT 0,
    `total_distance_km`     DECIMAL(12,2)   NOT NULL DEFAULT 0,
    `total_duration_minutes` INT UNSIGNED   NOT NULL DEFAULT 0,
    `total_fuel_litres`     DECIMAL(12,2)   NOT NULL DEFAULT 0,
    `total_cost`            DECIMAL(15,3)   NOT NULL DEFAULT 0,
    `conflicts_json`        LONGTEXT        NULL,
    `fleet_summary_json`    LONGTEXT        NULL,
    `optimization_summary`  TEXT            NULL,
    `rejection_reason`      TEXT            NULL,
    `generated_by`          BIGINT UNSIGNED NULL,
    `approved_by`           BIGINT UNSIGNED NULL,
    `approved_at`           DATETIME        NULL,
    `created_by`            BIGINT UNSIGNED NULL,
    `updated_by`            BIGINT UNSIGNED NULL,
    `created_at`            DATETIME        NULL,
    `updated_at`            DATETIME        NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `number` (`number`),
    KEY `status` (`status`),
    KEY `plan_start_date` (`plan_start_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Plan routes (one optimized route proposal per row)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_ai_plan_routes` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `plan_id`               BIGINT UNSIGNED NOT NULL,
    `service_date`          DATE            NOT NULL,
    `route_name`            VARCHAR(200)    NOT NULL,
    `contract_id`           BIGINT UNSIGNED NULL,
    `shift_id`              BIGINT UNSIGNED NULL,
    `origin_location_id`    BIGINT UNSIGNED NULL,
    `destination_location_id` BIGINT UNSIGNED NULL,
    `vehicle_id`            BIGINT UNSIGNED NULL,
    `driver_id`             BIGINT UNSIGNED NULL,
    `employee_count`        INT UNSIGNED    NOT NULL DEFAULT 0,
    `total_distance_km`     DECIMAL(10,2)   NOT NULL DEFAULT 0,
    `estimated_duration_minutes` INT UNSIGNED NOT NULL DEFAULT 0,
    `estimated_fuel_litres` DECIMAL(10,2)   NOT NULL DEFAULT 0,
    `estimated_cost`        DECIMAL(12,3)   NOT NULL DEFAULT 0,
    `departure_time`        TIME            NULL,
    `arrival_time`          TIME            NULL,
    `shift_start_time`      TIME            NULL,
    `shift_end_time`        TIME            NULL,
    `return_departure_time` TIME            NULL,
    `return_arrival_time`   TIME            NULL,
    `route_geometry`        TEXT            NULL,
    `optimization_notes`    TEXT            NULL,
    `published_schedule_id` BIGINT UNSIGNED NULL,
    `created_at`            DATETIME        NULL,
    `updated_at`            DATETIME        NULL,
    PRIMARY KEY (`id`),
    KEY `plan_id` (`plan_id`),
    KEY `service_date` (`service_date`),
    KEY `vehicle_id` (`vehicle_id`),
    KEY `driver_id` (`driver_id`),
    KEY `contract_id` (`contract_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Plan route stops (ordered employee pickups)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_ai_plan_stops` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `plan_route_id`         BIGINT UNSIGNED NOT NULL,
    `stop_order`            INT UNSIGNED    NOT NULL DEFAULT 1,
    `employee_id`           BIGINT UNSIGNED NOT NULL,
    `latitude`              DECIMAL(10,7)   NOT NULL,
    `longitude`             DECIMAL(10,7)   NOT NULL,
    `address`               VARCHAR(255)    NULL,
    `pickup_time`           TIME            NULL,
    `leg_distance_km`       DECIMAL(10,3)   NOT NULL DEFAULT 0,
    `leg_duration_minutes`  DECIMAL(10,2)   NOT NULL DEFAULT 0,
    `created_at`            DATETIME        NULL,
    `updated_at`            DATETIME        NULL,
    PRIMARY KEY (`id`),
    KEY `plan_route_id` (`plan_route_id`),
    KEY `employee_id` (`employee_id`),
    KEY `stop_order` (`stop_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Vehicle fuel consumption column (MariaDB supports IF NOT EXISTS here)
-- ---------------------------------------------------------------------------
ALTER TABLE `transport_vehicles`
    ADD COLUMN IF NOT EXISTS `fuel_consumption_l_per_100km` DECIMAL(6,2) NULL AFTER `capacity`;

-- ---------------------------------------------------------------------------
-- AI change flags on official schedules: published/changed driver schedules
-- show a red indicator until they have been seen on the scheduling screen.
-- ---------------------------------------------------------------------------
ALTER TABLE `transport_schedules`
    ADD COLUMN IF NOT EXISTS `ai_flag` ENUM('new','updated') NULL AFTER `status`,
    ADD COLUMN IF NOT EXISTS `ai_flag_seen_at` DATETIME NULL AFTER `ai_flag`;

-- ---------------------------------------------------------------------------
-- Default garage/depot for all AI routes (multi-site outbound + return pickups)
-- ---------------------------------------------------------------------------
ALTER TABLE `transport_ai_settings`
    ADD COLUMN IF NOT EXISTS `default_base_location_id` BIGINT UNSIGNED NULL AFTER `arrival_buffer_minutes`;

-- ---------------------------------------------------------------------------
-- Staff transport leg per contract assignment (morning / return / both)
-- ---------------------------------------------------------------------------
ALTER TABLE `contract_employees`
    ADD COLUMN IF NOT EXISTS `transport_leg` ENUM('both','outbound_only','return_only') NOT NULL DEFAULT 'both' AFTER `hours_allocated`;

-- ---------------------------------------------------------------------------
-- Round-trip times on official schedules (published from AI plans)
-- ---------------------------------------------------------------------------
ALTER TABLE `transport_schedules`
    ADD COLUMN IF NOT EXISTS `shift_start_time` TIME NULL AFTER `arrival_time`,
    ADD COLUMN IF NOT EXISTS `shift_end_time` TIME NULL AFTER `shift_start_time`,
    ADD COLUMN IF NOT EXISTS `return_departure_time` TIME NULL AFTER `shift_end_time`,
    ADD COLUMN IF NOT EXISTS `return_arrival_time` TIME NULL AFTER `return_departure_time`;

-- ---------------------------------------------------------------------------
-- Official schedule stops (board / drop / return pickup legs)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_schedule_stops` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `schedule_id`           BIGINT UNSIGNED NOT NULL,
    `stop_order`            INT UNSIGNED    NOT NULL DEFAULT 1,
    `employee_id`           BIGINT UNSIGNED NOT NULL,
    `latitude`              DECIMAL(10,7)   NOT NULL,
    `longitude`             DECIMAL(10,7)   NOT NULL,
    `address`               VARCHAR(255)    NULL,
    `pickup_time`           TIME            NULL,
    `leg_distance_km`       DECIMAL(10,3)   NOT NULL DEFAULT 0,
    `leg_duration_minutes`  DECIMAL(10,2)   NOT NULL DEFAULT 0,
    `created_at`            DATETIME        NULL,
    `updated_at`            DATETIME        NULL,
    PRIMARY KEY (`id`),
    KEY `schedule_id` (`schedule_id`),
    KEY `employee_id` (`employee_id`),
    KEY `stop_order` (`stop_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Permissions
-- ---------------------------------------------------------------------------
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_ai', 'transport_ai.view', 'View AI Transport Plans', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_ai.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_ai', 'transport_ai.generate', 'Generate AI Transport Plans', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_ai.generate');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_ai', 'transport_ai.approve', 'Approve AI Transport Plans', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_ai.approve');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_ai', 'transport_ai.settings', 'Manage AI Transport Settings', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_ai.settings');

INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created_at`)
SELECT r.id, p.id, NOW()
FROM `roles` r
CROSS JOIN `permissions` p
WHERE r.slug = 'administrator'
  AND p.slug LIKE 'transport_ai%'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
