-- Transport / Fleet Management module
-- Run against your ERP database
-- Safe to re-run: IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

-- ---------------------------------------------------------------------------
-- Map settings (singleton row)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_map_settings` (
    `id`                    TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `default_provider`      ENUM('openstreetmap','google') NOT NULL DEFAULT 'openstreetmap',
    `google_api_key`        VARCHAR(255)    NULL,
    `default_latitude`      DECIMAL(10,7)   NOT NULL DEFAULT 26.0667000,
    `default_longitude`     DECIMAL(10,7)   NOT NULL DEFAULT 50.5577000,
    `default_zoom`          TINYINT UNSIGNED NOT NULL DEFAULT 12,
    `updated_by`            BIGINT UNSIGNED NULL,
    `updated_at`            DATETIME        NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `transport_map_settings` (`id`, `default_provider`, `updated_at`)
SELECT 1, 'openstreetmap', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `transport_map_settings` WHERE `id` = 1);

-- ---------------------------------------------------------------------------
-- Locations
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_locations` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `name`                  VARCHAR(200)    NOT NULL,
    `code`                  VARCHAR(50)     NULL,
    `location_type`         ENUM('depot','stop','hub','warehouse','customer','other') NOT NULL DEFAULT 'stop',
    `address`               VARCHAR(255)    NULL,
    `city`                  VARCHAR(100)    NULL,
    `country`               VARCHAR(100)    NULL,
    `latitude`              DECIMAL(10,7)   NULL,
    `longitude`             DECIMAL(10,7)   NULL,
    `contact_name`          VARCHAR(150)    NULL,
    `contact_phone`         VARCHAR(50)     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 `number` (`number`),
    KEY `status` (`status`),
    KEY `location_type` (`location_type`),
    KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Drivers (linked to employees)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_drivers` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `employee_id`           BIGINT UNSIGNED NOT NULL,
    `license_number`        VARCHAR(80)     NOT NULL,
    `license_class`         VARCHAR(20)     NULL,
    `license_expiry`        DATE            NULL,
    `phone`                 VARCHAR(50)     NULL,
    `status`                ENUM('active','inactive','suspended') 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 `number` (`number`),
    UNIQUE KEY `employee_id` (`employee_id`),
    KEY `status` (`status`),
    KEY `license_expiry` (`license_expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Vehicles
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_vehicles` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `registration_number`   VARCHAR(50)     NOT NULL,
    `make`                  VARCHAR(80)     NULL,
    `model`                 VARCHAR(80)     NULL,
    `year`                  SMALLINT UNSIGNED NULL,
    `vehicle_type`          ENUM('car','van','truck','bus','motorcycle','other') NOT NULL DEFAULT 'van',
    `fuel_type`             ENUM('petrol','diesel','electric','hybrid','other') NOT NULL DEFAULT 'diesel',
    `capacity`              INT UNSIGNED    NULL,
    `odometer_km`           INT UNSIGNED    NULL,
    `last_service_date`     DATE            NULL,
    `current_driver_id`     BIGINT UNSIGNED NULL,
    `current_location_id`   BIGINT UNSIGNED NULL,
    `latitude`              DECIMAL(10,7)   NULL,
    `longitude`             DECIMAL(10,7)   NULL,
    `status`                ENUM('active','maintenance','retired') 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 `number` (`number`),
    UNIQUE KEY `registration_number` (`registration_number`),
    KEY `status` (`status`),
    KEY `vehicle_type` (`vehicle_type`),
    KEY `current_driver_id` (`current_driver_id`),
    KEY `current_location_id` (`current_location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Routes
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_routes` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `name`                  VARCHAR(200)    NOT NULL,
    `code`                  VARCHAR(50)     NULL,
    `origin_location_id`    BIGINT UNSIGNED NULL,
    `destination_location_id` BIGINT UNSIGNED NULL,
    `distance_km`           DECIMAL(10,2)   NULL,
    `estimated_duration_minutes` INT UNSIGNED NULL,
    `route_geometry`        TEXT            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 `number` (`number`),
    KEY `status` (`status`),
    KEY `origin_location_id` (`origin_location_id`),
    KEY `destination_location_id` (`destination_location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `transport_route_stops` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `route_id`              BIGINT UNSIGNED NOT NULL,
    `location_id`           BIGINT UNSIGNED NOT NULL,
    `stop_order`            INT UNSIGNED    NOT NULL DEFAULT 1,
    `estimated_arrival_offset_minutes` INT UNSIGNED NULL,
    `notes`                 VARCHAR(255)    NULL,
    `created_at`            DATETIME        NULL,
    `updated_at`            DATETIME        NULL,
    PRIMARY KEY (`id`),
    KEY `route_id` (`route_id`),
    KEY `location_id` (`location_id`),
    KEY `stop_order` (`stop_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Scheduling (trips)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_schedules` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `title`                 VARCHAR(200)    NOT NULL,
    `route_id`              BIGINT UNSIGNED NULL,
    `vehicle_id`            BIGINT UNSIGNED NULL,
    `driver_id`             BIGINT UNSIGNED NULL,
    `schedule_date`         DATE            NOT NULL,
    `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,
    `priority`              ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    `status`                ENUM('planned','scheduled','in_progress','completed','cancelled') NOT NULL DEFAULT 'planned',
    `notes`                 TEXT            NULL,
    `started_at`            DATETIME        NULL,
    `completed_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 `schedule_date` (`schedule_date`),
    KEY `route_id` (`route_id`),
    KEY `vehicle_id` (`vehicle_id`),
    KEY `driver_id` (`driver_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_vehicles', 'transport_vehicles.view', 'View Vehicles', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_vehicles.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_vehicles', 'transport_vehicles.create', 'Create Vehicles', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_vehicles.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_vehicles', 'transport_vehicles.edit', 'Edit Vehicles', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_vehicles.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_vehicles', 'transport_vehicles.delete', 'Delete Vehicles', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_vehicles.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_drivers', 'transport_drivers.view', 'View Drivers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_drivers.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_drivers', 'transport_drivers.create', 'Create Drivers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_drivers.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_drivers', 'transport_drivers.edit', 'Edit Drivers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_drivers.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_drivers', 'transport_drivers.delete', 'Delete Drivers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_drivers.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_locations', 'transport_locations.view', 'View Transport Locations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_locations.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_locations', 'transport_locations.create', 'Create Transport Locations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_locations.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_locations', 'transport_locations.edit', 'Edit Transport Locations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_locations.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_locations', 'transport_locations.delete', 'Delete Transport Locations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_locations.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_routes', 'transport_routes.view', 'View Routes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_routes.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_routes', 'transport_routes.create', 'Create Routes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_routes.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_routes', 'transport_routes.edit', 'Edit Routes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_routes.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_routes', 'transport_routes.delete', 'Delete Routes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_routes.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_scheduling', 'transport_scheduling.view', 'View Transport Scheduling', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_scheduling.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_scheduling', 'transport_scheduling.create', 'Create Transport Schedules', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_scheduling.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_scheduling', 'transport_scheduling.edit', 'Edit Transport Schedules', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_scheduling.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_scheduling', 'transport_scheduling.delete', 'Delete Transport Schedules', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_scheduling.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_maps', 'transport_maps.view', 'View Transport Maps', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_maps.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_maps', 'transport_maps.edit', 'Edit Transport Map Settings', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_maps.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'transport_reports', 'transport_reports.view', 'View Transport Reports', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'transport_reports.view');

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%'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
