-- ============================================================================
-- ZUPCO FleetOS — Database Schema
-- Engine: MySQL 5.7+/8.0, InnoDB, utf8mb4
-- ============================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `zupco_fleetos`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `zupco_fleetos`;

-- ============================================================================
-- 1. AUTH / RBAC / AUDIT
-- ============================================================================

CREATE TABLE `roles` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(80) NOT NULL,
  `slug` VARCHAR(80) NOT NULL UNIQUE,
  `description` VARCHAR(255) DEFAULT NULL,
  `is_system` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'system roles cannot be deleted',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `permissions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `module` VARCHAR(60) NOT NULL COMMENT 'e.g. personnel',
  `page` VARCHAR(60) NOT NULL COMMENT 'e.g. employees',
  `action` VARCHAR(30) NOT NULL COMMENT 'view|create|edit|delete|export|approve',
  `slug` VARCHAR(150) NOT NULL UNIQUE COMMENT 'module.page.action',
  `description` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `role_permissions` (
  `role_id` INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rp_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `depots` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(120) NOT NULL,
  `code` VARCHAR(20) NOT NULL UNIQUE,
  `address` VARCHAR(255) DEFAULT NULL,
  `latitude` DECIMAL(10,7) DEFAULT NULL,
  `longitude` DECIMAL(10,7) DEFAULT NULL,
  `manager_id` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `employees` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_number` VARCHAR(30) NOT NULL UNIQUE,
  `first_name` VARCHAR(80) NOT NULL,
  `middle_name` VARCHAR(80) DEFAULT NULL,
  `last_name` VARCHAR(80) NOT NULL,
  `display_name` VARCHAR(160) DEFAULT NULL,
  `photo_path` VARCHAR(255) DEFAULT NULL,
  `date_of_birth` DATE DEFAULT NULL,
  `gender` ENUM('male','female','other') DEFAULT NULL,
  `marital_status` ENUM('single','married','divorced','widowed') DEFAULT NULL,
  `nationality` VARCHAR(60) DEFAULT NULL,
  `country_of_birth` VARCHAR(60) DEFAULT NULL,
  `place_of_birth` VARCHAR(120) DEFAULT NULL,
  `blood_group` VARCHAR(5) DEFAULT NULL,
  `preferred_language` VARCHAR(40) DEFAULT NULL,
  `primary_mobile` VARCHAR(30) DEFAULT NULL,
  `alternate_mobile` VARCHAR(30) DEFAULT NULL,
  `whatsapp_number` VARCHAR(30) DEFAULT NULL,
  `personal_email` VARCHAR(120) DEFAULT NULL,
  `work_email` VARCHAR(120) DEFAULT NULL,
  `preferred_contact_method` ENUM('phone','email','whatsapp','sms') DEFAULT NULL,
  `employee_type` VARCHAR(60) DEFAULT NULL COMMENT 'driver|conductor|admin|technician|...',
  `department` VARCHAR(80) DEFAULT NULL,
  `branch_depot_id` INT UNSIGNED DEFAULT NULL,
  `reporting_manager_id` INT UNSIGNED DEFAULT NULL,
  `job_title` VARCHAR(100) DEFAULT NULL,
  `employment_type` ENUM('full_time','part_time','contract','probation') DEFAULT 'full_time',
  `join_date` DATE DEFAULT NULL,
  `status` ENUM('active','inactive','suspended','terminated') NOT NULL DEFAULT 'active',
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  KEY `idx_emp_status` (`status`),
  KEY `idx_emp_type` (`employee_type`),
  CONSTRAINT `fk_emp_depot` FOREIGN KEY (`branch_depot_id`) REFERENCES `depots`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_emp_manager` FOREIGN KEY (`reporting_manager_id`) REFERENCES `employees`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` INT UNSIGNED DEFAULT NULL,
  `username` VARCHAR(60) NOT NULL UNIQUE,
  `email` VARCHAR(120) DEFAULT NULL UNIQUE,
  `phone` VARCHAR(30) DEFAULT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `role_id` INT UNSIGNED NOT NULL,
  `must_change_password` TINYINT(1) NOT NULL DEFAULT 0,
  `status` ENUM('active','inactive','locked') NOT NULL DEFAULT 'active',
  `failed_login_attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `locked_until` DATETIME DEFAULT NULL,
  `last_login_at` DATETIME DEFAULT NULL,
  `last_login_ip` VARCHAR(45) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_users_role` (`role_id`),
  CONSTRAINT `fk_users_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB;

ALTER TABLE `depots` ADD CONSTRAINT `fk_depot_manager` FOREIGN KEY (`manager_id`) REFERENCES `users`(`id`) ON DELETE SET NULL;
ALTER TABLE `employees` ADD CONSTRAINT `fk_emp_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL;

CREATE TABLE `user_sessions` (
  `id` CHAR(36) NOT NULL PRIMARY KEY COMMENT 'UUID session id',
  `user_id` INT UNSIGNED NOT NULL,
  `token_hash` CHAR(64) NOT NULL COMMENT 'sha256 of the bearer/csrf token',
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `payload` TEXT DEFAULT NULL,
  `last_activity_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_sess_user` (`user_id`),
  KEY `idx_sess_expires` (`expires_at`),
  CONSTRAINT `fk_sess_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `password_resets` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `token_hash` CHAR(64) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `used_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_pwreset_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `audit_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `username` VARCHAR(60) DEFAULT NULL COMMENT 'denormalized snapshot',
  `action` VARCHAR(40) NOT NULL COMMENT 'create|update|delete|login|logout|export|approve|...',
  `module` VARCHAR(60) DEFAULT NULL,
  `entity_type` VARCHAR(80) DEFAULT NULL,
  `entity_id` VARCHAR(40) DEFAULT NULL,
  `old_values` JSON DEFAULT NULL,
  `new_values` JSON DEFAULT NULL,
  `method` VARCHAR(10) DEFAULT NULL,
  `endpoint` VARCHAR(255) DEFAULT NULL,
  `status_code` SMALLINT UNSIGNED DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `request_id` CHAR(36) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_audit_user` (`user_id`),
  KEY `idx_audit_entity` (`entity_type`, `entity_id`),
  KEY `idx_audit_created` (`created_at`),
  KEY `idx_audit_module` (`module`)
) ENGINE=InnoDB;

CREATE TABLE `security_alerts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `alert_type` VARCHAR(60) NOT NULL COMMENT 'failed_login|geofence_breach|harsh_braking|speeding|unauthorized_access|...',
  `severity` ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `related_entity_type` VARCHAR(60) DEFAULT NULL,
  `related_entity_id` VARCHAR(40) DEFAULT NULL,
  `message` VARCHAR(500) NOT NULL,
  `status` ENUM('open','acknowledged','resolved') NOT NULL DEFAULT 'open',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` DATETIME DEFAULT NULL,
  `resolved_by` INT UNSIGNED DEFAULT NULL,
  KEY `idx_alert_status` (`status`),
  KEY `idx_alert_type` (`alert_type`),
  CONSTRAINT `fk_alert_resolver` FOREIGN KEY (`resolved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `notifications` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(160) NOT NULL,
  `message` VARCHAR(500) DEFAULT NULL,
  `link` VARCHAR(255) DEFAULT NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_notif_user` (`user_id`, `is_read`),
  CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `system_settings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(100) NOT NULL UNIQUE,
  `setting_value` TEXT DEFAULT NULL,
  `type` VARCHAR(20) NOT NULL DEFAULT 'string',
  `updated_by` INT UNSIGNED DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_setting_user` FOREIGN KEY (`updated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================================
-- 2. PERSONNEL
-- ============================================================================

CREATE TABLE `employee_documents` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` INT UNSIGNED NOT NULL,
  `doc_type` VARCHAR(60) NOT NULL COMMENT 'national_id|license|passport|contract|cert|other',
  `file_path` VARCHAR(255) NOT NULL,
  `original_filename` VARCHAR(255) DEFAULT NULL,
  `uploaded_by` INT UNSIGNED DEFAULT NULL,
  `uploaded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_doc_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_doc_uploader` FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `employee_emergency_contacts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `relationship` VARCHAR(60) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(120) DEFAULT NULL,
  CONSTRAINT `fk_emerg_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `drivers` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` INT UNSIGNED NOT NULL UNIQUE,
  `driver_code` VARCHAR(20) NOT NULL UNIQUE,
  `license_number` VARCHAR(60) DEFAULT NULL,
  `license_class` VARCHAR(20) DEFAULT NULL,
  `license_expiry` DATE DEFAULT NULL,
  `defensive_driving_cert` TINYINT(1) NOT NULL DEFAULT 0,
  `performance_score` DECIMAL(5,2) DEFAULT NULL,
  `status` ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_driver_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `conductors` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` INT UNSIGNED NOT NULL UNIQUE,
  `badge_number` VARCHAR(30) DEFAULT NULL UNIQUE,
  `status` ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_conductor_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `shifts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(40) NOT NULL,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE `vehicles` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `fleet_number` VARCHAR(30) NOT NULL UNIQUE,
  `registration_number` VARCHAR(30) NOT NULL UNIQUE,
  `vin` VARCHAR(60) DEFAULT NULL,
  `make` VARCHAR(60) DEFAULT NULL,
  `model` VARCHAR(60) DEFAULT NULL,
  `year` SMALLINT UNSIGNED DEFAULT NULL,
  `capacity` SMALLINT UNSIGNED DEFAULT NULL,
  `depot_id` INT UNSIGNED DEFAULT NULL,
  `traccar_device_id` INT UNSIGNED DEFAULT NULL COMMENT 'FK to Traccar device (external system)',
  `traccar_unique_id` VARCHAR(120) DEFAULT NULL COMMENT 'IMEI/identifier registered in Traccar',
  `status` ENUM('active','inactive','maintenance','decommissioned') NOT NULL DEFAULT 'active',
  `fuel_level_pct` DECIMAL(5,2) DEFAULT NULL,
  `odometer_km` DECIMAL(10,2) DEFAULT NULL,
  `last_service_date` DATE DEFAULT NULL,
  `next_service_due` DATE DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_veh_status` (`status`),
  KEY `idx_veh_traccar` (`traccar_device_id`),
  CONSTRAINT `fk_veh_depot` FOREIGN KEY (`depot_id`) REFERENCES `depots`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `routes` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(20) NOT NULL UNIQUE,
  `name` VARCHAR(160) NOT NULL,
  `origin` VARCHAR(120) DEFAULT NULL,
  `destination` VARCHAR(120) DEFAULT NULL,
  `distance_km` DECIMAL(8,2) DEFAULT NULL,
  `depot_id` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_route_depot` FOREIGN KEY (`depot_id`) REFERENCES `depots`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `route_stops` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `route_id` INT UNSIGNED NOT NULL,
  `stop_name` VARCHAR(160) NOT NULL,
  `sequence` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `latitude` DECIMAL(10,7) DEFAULT NULL,
  `longitude` DECIMAL(10,7) DEFAULT NULL,
  CONSTRAINT `fk_stop_route` FOREIGN KEY (`route_id`) REFERENCES `routes`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `schedules` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `driver_id` INT UNSIGNED DEFAULT NULL,
  `conductor_id` INT UNSIGNED DEFAULT NULL,
  `vehicle_id` INT UNSIGNED DEFAULT NULL,
  `route_id` INT UNSIGNED DEFAULT NULL,
  `shift_id` INT UNSIGNED DEFAULT NULL,
  `schedule_date` DATE NOT NULL,
  `start_time` TIME DEFAULT NULL,
  `end_time` TIME DEFAULT NULL,
  `status` ENUM('scheduled','confirmed','absent','completed','cancelled') NOT NULL DEFAULT 'scheduled',
  `notes` VARCHAR(500) DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_sched_date` (`schedule_date`),
  CONSTRAINT `fk_sched_driver` FOREIGN KEY (`driver_id`) REFERENCES `drivers`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sched_conductor` FOREIGN KEY (`conductor_id`) REFERENCES `conductors`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sched_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sched_route` FOREIGN KEY (`route_id`) REFERENCES `routes`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sched_shift` FOREIGN KEY (`shift_id`) REFERENCES `shifts`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sched_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `attendance` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` INT UNSIGNED NOT NULL,
  `schedule_id` INT UNSIGNED DEFAULT NULL,
  `attendance_date` DATE NOT NULL,
  `clock_in` DATETIME DEFAULT NULL,
  `clock_out` DATETIME DEFAULT NULL,
  `status` ENUM('present','absent','late','on_leave','half_day') NOT NULL DEFAULT 'present',
  `notes` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_att_employee_date` (`employee_id`, `attendance_date`),
  CONSTRAINT `fk_att_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_att_schedule` FOREIGN KEY (`schedule_id`) REFERENCES `schedules`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `payroll_runs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `period_start` DATE NOT NULL,
  `period_end` DATE NOT NULL,
  `status` ENUM('draft','processing','completed','cancelled') NOT NULL DEFAULT 'draft',
  `processed_by` INT UNSIGNED DEFAULT NULL,
  `processed_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_payrun_user` FOREIGN KEY (`processed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `payroll_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `payroll_run_id` INT UNSIGNED NOT NULL,
  `employee_id` INT UNSIGNED NOT NULL,
  `gross_pay` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `deductions` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `net_pay` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `currency` VARCHAR(10) NOT NULL DEFAULT 'USD',
  CONSTRAINT `fk_payitem_run` FOREIGN KEY (`payroll_run_id`) REFERENCES `payroll_runs`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_payitem_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================================
-- 3. DISPATCHER / TRACKING (Traccar-backed)
-- ============================================================================

CREATE TABLE `trips` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `schedule_id` INT UNSIGNED DEFAULT NULL,
  `route_id` INT UNSIGNED DEFAULT NULL,
  `vehicle_id` INT UNSIGNED DEFAULT NULL,
  `driver_id` INT UNSIGNED DEFAULT NULL,
  `conductor_id` INT UNSIGNED DEFAULT NULL,
  `dispatch_status` ENUM('pending','dispatched','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
  `scheduled_departure` DATETIME DEFAULT NULL,
  `actual_departure` DATETIME DEFAULT NULL,
  `actual_arrival` DATETIME DEFAULT NULL,
  `passenger_count` INT UNSIGNED DEFAULT NULL,
  `revenue_collected` DECIMAL(10,2) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_trip_status` (`dispatch_status`),
  CONSTRAINT `fk_trip_schedule` FOREIGN KEY (`schedule_id`) REFERENCES `schedules`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_trip_route` FOREIGN KEY (`route_id`) REFERENCES `routes`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_trip_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_trip_driver` FOREIGN KEY (`driver_id`) REFERENCES `drivers`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_trip_conductor` FOREIGN KEY (`conductor_id`) REFERENCES `conductors`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `vehicle_positions_cache` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `traccar_device_id` INT UNSIGNED NOT NULL,
  `latitude` DECIMAL(10,7) NOT NULL,
  `longitude` DECIMAL(10,7) NOT NULL,
  `speed_kmh` DECIMAL(6,2) DEFAULT NULL,
  `course` DECIMAL(6,2) DEFAULT NULL,
  `ignition` TINYINT(1) DEFAULT NULL,
  `fuel_level_pct` DECIMAL(5,2) DEFAULT NULL,
  `address` VARCHAR(255) DEFAULT NULL,
  `device_time` DATETIME NOT NULL,
  `fetched_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_pos_vehicle` (`vehicle_id`, `device_time`),
  CONSTRAINT `fk_pos_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Local cache of latest Traccar positions to avoid hammering the Traccar API on every page load';

CREATE TABLE `speed_events` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `driver_id` INT UNSIGNED DEFAULT NULL,
  `event_type` VARCHAR(40) NOT NULL COMMENT 'speeding|harsh_braking|harsh_acceleration|sharp_turn|idling',
  `speed_kmh` DECIMAL(6,2) DEFAULT NULL,
  `speed_limit_kmh` DECIMAL(6,2) DEFAULT NULL,
  `latitude` DECIMAL(10,7) DEFAULT NULL,
  `longitude` DECIMAL(10,7) DEFAULT NULL,
  `occurred_at` DATETIME NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_speedevt_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_speedevt_driver` FOREIGN KEY (`driver_id`) REFERENCES `drivers`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `route_adherence_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `trip_id` INT UNSIGNED NOT NULL,
  `deviation_meters` DECIMAL(8,2) DEFAULT NULL,
  `status` ENUM('on_route','deviated','off_route') NOT NULL DEFAULT 'on_route',
  `recorded_at` DATETIME NOT NULL,
  CONSTRAINT `fk_adherence_trip` FOREIGN KEY (`trip_id`) REFERENCES `trips`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `crew_messages` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sender_id` INT UNSIGNED NOT NULL,
  `recipient_id` INT UNSIGNED DEFAULT NULL COMMENT 'NULL = broadcast to a channel',
  `channel` VARCHAR(60) DEFAULT NULL COMMENT 'e.g. depot:1, trip:123',
  `message` VARCHAR(1000) NOT NULL,
  `sent_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `read_at` DATETIME DEFAULT NULL,
  CONSTRAINT `fk_msg_sender` FOREIGN KEY (`sender_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_msg_recipient` FOREIGN KEY (`recipient_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================================
-- 4. MAINTENANCE
-- ============================================================================

CREATE TABLE `work_orders` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `wo_number` VARCHAR(30) NOT NULL UNIQUE,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `priority` ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `status` ENUM('open','in_progress','on_hold','completed','cancelled') NOT NULL DEFAULT 'open',
  `assigned_to` INT UNSIGNED DEFAULT NULL,
  `opened_by` INT UNSIGNED DEFAULT NULL,
  `opened_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `closed_at` DATETIME DEFAULT NULL,
  `cost` DECIMAL(10,2) DEFAULT NULL,
  KEY `idx_wo_status` (`status`),
  CONSTRAINT `fk_wo_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wo_assignee` FOREIGN KEY (`assigned_to`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_wo_opener` FOREIGN KEY (`opened_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `preventive_maintenance_schedules` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `maintenance_type` VARCHAR(100) NOT NULL COMMENT 'oil_change|tyre_rotation|brake_check|...',
  `interval_km` INT UNSIGNED DEFAULT NULL,
  `interval_days` INT UNSIGNED DEFAULT NULL,
  `last_done_at` DATE DEFAULT NULL,
  `last_done_km` DECIMAL(10,2) DEFAULT NULL,
  `next_due_at` DATE DEFAULT NULL,
  `next_due_km` DECIMAL(10,2) DEFAULT NULL,
  CONSTRAINT `fk_pm_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `workshop_jobs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `work_order_id` INT UNSIGNED NOT NULL,
  `technician_id` INT UNSIGNED DEFAULT NULL,
  `bay` VARCHAR(20) DEFAULT NULL,
  `started_at` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  CONSTRAINT `fk_wsjob_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wsjob_tech` FOREIGN KEY (`technician_id`) REFERENCES `employees`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `suppliers` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(160) NOT NULL,
  `contact_person` VARCHAR(120) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(120) DEFAULT NULL,
  `address` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `parts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `part_number` VARCHAR(60) NOT NULL UNIQUE,
  `name` VARCHAR(160) NOT NULL,
  `category` VARCHAR(80) DEFAULT NULL,
  `quantity_in_stock` INT NOT NULL DEFAULT 0,
  `reorder_level` INT NOT NULL DEFAULT 0,
  `unit_cost` DECIMAL(10,2) DEFAULT NULL,
  `supplier_id` INT UNSIGNED DEFAULT NULL,
  CONSTRAINT `fk_part_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `part_usage` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `work_order_id` INT UNSIGNED NOT NULL,
  `part_id` INT UNSIGNED NOT NULL,
  `quantity_used` INT UNSIGNED NOT NULL DEFAULT 1,
  `cost` DECIMAL(10,2) DEFAULT NULL,
  CONSTRAINT `fk_partuse_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_partuse_part` FOREIGN KEY (`part_id`) REFERENCES `parts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================================
-- 5. FINANCE
-- ============================================================================

CREATE TABLE `revenue_records` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `trip_id` INT UNSIGNED DEFAULT NULL,
  `depot_id` INT UNSIGNED DEFAULT NULL,
  `record_date` DATE NOT NULL,
  `cash_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `digital_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `total_amount` DECIMAL(12,2) GENERATED ALWAYS AS (`cash_amount` + `digital_amount`) STORED,
  `recorded_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_rev_date` (`record_date`),
  CONSTRAINT `fk_rev_trip` FOREIGN KEY (`trip_id`) REFERENCES `trips`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_rev_depot` FOREIGN KEY (`depot_id`) REFERENCES `depots`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_rev_recorder` FOREIGN KEY (`recorded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `expenses` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category` VARCHAR(80) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `currency` VARCHAR(10) NOT NULL DEFAULT 'USD',
  `depot_id` INT UNSIGNED DEFAULT NULL,
  `incurred_at` DATE NOT NULL,
  `status` ENUM('pending','approved','rejected','paid') NOT NULL DEFAULT 'pending',
  `approved_by` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_exp_depot` FOREIGN KEY (`depot_id`) REFERENCES `depots`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_exp_approver` FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_exp_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `collections` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `depot_id` INT UNSIGNED DEFAULT NULL,
  `collected_by` INT UNSIGNED DEFAULT NULL,
  `collection_date` DATE NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `deposited` TINYINT(1) NOT NULL DEFAULT 0,
  `deposit_reference` VARCHAR(80) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_coll_depot` FOREIGN KEY (`depot_id`) REFERENCES `depots`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_coll_user` FOREIGN KEY (`collected_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `reconciliations` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `depot_id` INT UNSIGNED DEFAULT NULL,
  `reconciliation_date` DATE NOT NULL,
  `expected_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `actual_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `variance` DECIMAL(12,2) GENERATED ALWAYS AS (`actual_amount` - `expected_amount`) STORED,
  `reconciled_by` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('pending','matched','variance_flagged') NOT NULL DEFAULT 'pending',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_recon_depot` FOREIGN KEY (`depot_id`) REFERENCES `depots`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_recon_user` FOREIGN KEY (`reconciled_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================================
-- 6. PROCUREMENT
-- ============================================================================

CREATE TABLE `purchase_requests` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `pr_number` VARCHAR(30) NOT NULL UNIQUE,
  `requested_by` INT UNSIGNED DEFAULT NULL,
  `department` VARCHAR(80) DEFAULT NULL,
  `justification` VARCHAR(500) DEFAULT NULL,
  `status` ENUM('pending','approved','rejected','converted') NOT NULL DEFAULT 'pending',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_pr_user` FOREIGN KEY (`requested_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `purchase_request_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `purchase_request_id` INT UNSIGNED NOT NULL,
  `item_name` VARCHAR(160) NOT NULL,
  `quantity` INT UNSIGNED NOT NULL DEFAULT 1,
  `estimated_cost` DECIMAL(10,2) DEFAULT NULL,
  CONSTRAINT `fk_pritem_pr` FOREIGN KEY (`purchase_request_id`) REFERENCES `purchase_requests`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `purchase_orders` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `po_number` VARCHAR(30) NOT NULL UNIQUE,
  `purchase_request_id` INT UNSIGNED DEFAULT NULL,
  `supplier_id` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('draft','sent','partially_received','received','cancelled') NOT NULL DEFAULT 'draft',
  `total_amount` DECIMAL(12,2) DEFAULT 0,
  `ordered_by` INT UNSIGNED DEFAULT NULL,
  `ordered_at` DATETIME DEFAULT NULL,
  CONSTRAINT `fk_po_pr` FOREIGN KEY (`purchase_request_id`) REFERENCES `purchase_requests`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_po_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_po_user` FOREIGN KEY (`ordered_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `inventory_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sku` VARCHAR(60) NOT NULL UNIQUE,
  `name` VARCHAR(160) NOT NULL,
  `category` VARCHAR(80) DEFAULT NULL,
  `quantity_on_hand` INT NOT NULL DEFAULT 0,
  `reorder_level` INT NOT NULL DEFAULT 0,
  `unit_cost` DECIMAL(10,2) DEFAULT NULL,
  `location` VARCHAR(120) DEFAULT NULL
) ENGINE=InnoDB;

-- ============================================================================
-- 7. PASSENGER
-- ============================================================================

CREATE TABLE `passenger_feedback` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `route_id` INT UNSIGNED DEFAULT NULL,
  `trip_id` INT UNSIGNED DEFAULT NULL,
  `category` VARCHAR(60) DEFAULT NULL COMMENT 'cleanliness|driver_conduct|punctuality|safety|other',
  `rating` TINYINT UNSIGNED DEFAULT NULL COMMENT '1-5',
  `comment` VARCHAR(1000) DEFAULT NULL,
  `submitted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` ENUM('new','reviewed','actioned','dismissed') NOT NULL DEFAULT 'new',
  CONSTRAINT `fk_fb_route` FOREIGN KEY (`route_id`) REFERENCES `routes`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_fb_trip` FOREIGN KEY (`trip_id`) REFERENCES `trips`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `passenger_demand_stats` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `route_id` INT UNSIGNED NOT NULL,
  `stat_date` DATE NOT NULL,
  `hour` TINYINT UNSIGNED NOT NULL,
  `boardings` INT UNSIGNED NOT NULL DEFAULT 0,
  `alightings` INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY `uniq_demand` (`route_id`, `stat_date`, `hour`),
  CONSTRAINT `fk_demand_route` FOREIGN KEY (`route_id`) REFERENCES `routes`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;