-- ============================================
-- Fleet & Equipment Operations Management System
-- Database Schema - MySQL
-- Organization: GEM
-- ============================================

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;
SET collation_connection = 'utf8mb4_unicode_ci';

-- ============================================
-- 1) جدول الصلاحيات
-- ============================================
CREATE TABLE IF NOT EXISTS `roles` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL UNIQUE,
    `display_name` VARCHAR(100) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 2) جدول المستخدمين
-- ============================================
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `full_name` VARCHAR(150) NOT NULL,
    `username` VARCHAR(100) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `role_id` INT NOT NULL,
    `phone` VARCHAR(30) DEFAULT NULL,
    `email` VARCHAR(150) DEFAULT NULL,
    `avatar` VARCHAR(255) DEFAULT NULL,
    `status` ENUM('active','inactive') DEFAULT 'active',
    `last_login` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3) جدول المشغلين (سائق / مشغل معدة)
-- ============================================
CREATE TABLE IF NOT EXISTS `operators` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `operator_type` ENUM('driver','equipment_operator','both') DEFAULT 'driver',
    `license_number` VARCHAR(100) DEFAULT NULL,
    `license_expiry` DATE DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `status` ENUM('active','inactive') DEFAULT 'active',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_operators_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4) جدول أنواع الأصول
-- ============================================
CREATE TABLE IF NOT EXISTS `asset_types` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `category` ENUM('vehicle','equipment','machine') NOT NULL,
    `icon` VARCHAR(50) DEFAULT 'bi-box',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5) جدول الأصول الداخلية
-- ============================================
CREATE TABLE IF NOT EXISTS `assets` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `asset_type_id` INT NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `code` VARCHAR(100) NOT NULL UNIQUE,
    `serial_number` VARCHAR(100) DEFAULT NULL,
    `plate_number` VARCHAR(50) DEFAULT NULL,
    `brand` VARCHAR(100) DEFAULT NULL,
    `model` VARCHAR(100) DEFAULT NULL,
    `year_number` INT DEFAULT NULL,
    `color` VARCHAR(50) DEFAULT NULL,
    `energy_type` ENUM('diesel','petrol','electric','hybrid','none') DEFAULT 'none',
    `operation_mode` ENUM('km','hours','daily') NOT NULL,
    `tracking_method` ENUM('odometer','hour_meter','manual_time','daily') NOT NULL,
    `current_odometer` DECIMAL(12,2) DEFAULT NULL,
    `current_hourmeter` DECIMAL(12,2) DEFAULT NULL,
    `current_location` VARCHAR(150) DEFAULT NULL,
    `ownership_type` ENUM('internal') DEFAULT 'internal',
    `status` ENUM('active','maintenance','stopped','inactive') DEFAULT 'active',
    `image` VARCHAR(255) DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_assets_type` FOREIGN KEY (`asset_type_id`) REFERENCES `asset_types`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 6) جدول الأصول الخارجية
-- ============================================
CREATE TABLE IF NOT EXISTS `external_assets` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `asset_type_id` INT NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `owner_name` VARCHAR(150) DEFAULT NULL,
    `owner_phone` VARCHAR(50) DEFAULT NULL,
    `plate_number` VARCHAR(50) DEFAULT NULL,
    `serial_number` VARCHAR(100) DEFAULT NULL,
    `energy_type` ENUM('diesel','petrol','electric','hybrid','none') DEFAULT 'none',
    `operation_mode` ENUM('km','hours','daily') NOT NULL,
    `tracking_method` ENUM('odometer','hour_meter','manual_time','daily') NOT NULL,
    `notes` TEXT DEFAULT NULL,
    `status` ENUM('active','inactive') DEFAULT 'active',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_external_assets_type` FOREIGN KEY (`asset_type_id`) REFERENCES `asset_types`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 7) جدول تعيين الأصل للمشغل
-- ============================================
CREATE TABLE IF NOT EXISTS `asset_assignments` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `asset_id` INT NOT NULL,
    `operator_id` INT NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE DEFAULT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `notes` TEXT DEFAULT NULL,
    `created_by` INT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_assignments_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`),
    CONSTRAINT `fk_assignments_operator` FOREIGN KEY (`operator_id`) REFERENCES `operators`(`id`),
    CONSTRAINT `fk_assignments_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 8) جدول الأعطال
-- ============================================
CREATE TABLE IF NOT EXISTS `breakdowns` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `asset_id` INT NOT NULL,
    `operator_id` INT DEFAULT NULL,
    `breakdown_date` DATETIME NOT NULL,
    `location` VARCHAR(150) DEFAULT NULL,
    `description` TEXT NOT NULL,
    `severity` ENUM('low','medium','high','critical') DEFAULT 'medium',
    `status` ENUM('open','under_repair','closed') DEFAULT 'open',
    `repair_cost` DECIMAL(12,2) DEFAULT NULL,
    `repair_notes` TEXT DEFAULT NULL,
    `closed_at` DATETIME DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `image` VARCHAR(255) DEFAULT NULL,
    `created_by` INT NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_breakdowns_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`),
    CONSTRAINT `fk_breakdowns_operator` FOREIGN KEY (`operator_id`) REFERENCES `operators`(`id`),
    CONSTRAINT `fk_breakdowns_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 9) جدول العمليات والحركات (الأهم)
-- ============================================
CREATE TABLE IF NOT EXISTS `asset_operations` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `asset_source_type` ENUM('internal','external') NOT NULL,
    `internal_asset_id` INT DEFAULT NULL,
    `external_asset_id` INT DEFAULT NULL,
    `original_internal_asset_id` INT DEFAULT NULL,
    `breakdown_id` INT DEFAULT NULL,
    `operator_id` INT NOT NULL,
    `operation_date` DATE NOT NULL,
    `operation_type` ENUM('trip','warehouse','machine_run','daily_task') NOT NULL,
    `from_location` VARCHAR(150) DEFAULT NULL,
    `to_location` VARCHAR(150) DEFAULT NULL,
    `work_location` VARCHAR(150) DEFAULT NULL,
    `purpose` VARCHAR(255) DEFAULT NULL,
    `start_odometer` DECIMAL(12,2) DEFAULT NULL,
    `end_odometer` DECIMAL(12,2) DEFAULT NULL,
    `distance_km` DECIMAL(12,2) DEFAULT NULL,
    `start_hourmeter` DECIMAL(12,2) DEFAULT NULL,
    `end_hourmeter` DECIMAL(12,2) DEFAULT NULL,
    `worked_hours` DECIMAL(10,2) DEFAULT NULL,
    `start_time` TIME DEFAULT NULL,
    `end_time` TIME DEFAULT NULL,
    `work_day_count` DECIMAL(10,2) DEFAULT NULL,
    `is_replacement` TINYINT(1) DEFAULT 0,
    `replacement_reason` VARCHAR(255) DEFAULT NULL,
    `status` ENUM('pending','approved','rejected') DEFAULT 'approved',
    `notes` TEXT DEFAULT NULL,
    `created_by` INT NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_operations_internal_asset` FOREIGN KEY (`internal_asset_id`) REFERENCES `assets`(`id`),
    CONSTRAINT `fk_operations_external_asset` FOREIGN KEY (`external_asset_id`) REFERENCES `external_assets`(`id`),
    CONSTRAINT `fk_operations_original_asset` FOREIGN KEY (`original_internal_asset_id`) REFERENCES `assets`(`id`),
    CONSTRAINT `fk_operations_breakdown` FOREIGN KEY (`breakdown_id`) REFERENCES `breakdowns`(`id`),
    CONSTRAINT `fk_operations_operator` FOREIGN KEY (`operator_id`) REFERENCES `operators`(`id`),
    CONSTRAINT `fk_operations_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 10) جدول سجلات العدادات
-- ============================================
CREATE TABLE IF NOT EXISTS `meter_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `asset_source_type` ENUM('internal','external') NOT NULL,
    `internal_asset_id` INT DEFAULT NULL,
    `external_asset_id` INT DEFAULT NULL,
    `operator_id` INT DEFAULT NULL,
    `log_date` DATETIME NOT NULL,
    `meter_type` ENUM('odometer','hour_meter') NOT NULL,
    `meter_value` DECIMAL(12,2) NOT NULL,
    `source_type` ENUM('operation_start','operation_end','energy_entry','manual','maintenance') NOT NULL,
    `reference_id` INT DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_meter_logs_internal_asset` FOREIGN KEY (`internal_asset_id`) REFERENCES `assets`(`id`),
    CONSTRAINT `fk_meter_logs_external_asset` FOREIGN KEY (`external_asset_id`) REFERENCES `external_assets`(`id`),
    CONSTRAINT `fk_meter_logs_operator` FOREIGN KEY (`operator_id`) REFERENCES `operators`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 11) جدول الطاقة والوقود
-- ============================================
CREATE TABLE IF NOT EXISTS `energy_entries` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `asset_source_type` ENUM('internal','external') NOT NULL,
    `internal_asset_id` INT DEFAULT NULL,
    `external_asset_id` INT DEFAULT NULL,
    `operator_id` INT DEFAULT NULL,
    `operation_id` INT DEFAULT NULL,
    `entry_date` DATETIME NOT NULL,
    `energy_type` ENUM('diesel','petrol','electric') NOT NULL,
    `quantity` DECIMAL(12,2) NOT NULL,
    `unit` ENUM('liter','kwh','charge') NOT NULL,
    `price_per_unit` DECIMAL(12,2) DEFAULT NULL,
    `total_amount` DECIMAL(12,2) DEFAULT NULL,
    `odometer_reading` DECIMAL(12,2) DEFAULT NULL,
    `hourmeter_reading` DECIMAL(12,2) DEFAULT NULL,
    `source_name` VARCHAR(150) DEFAULT NULL,
    `receipt_number` VARCHAR(100) DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `created_by` INT NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_energy_internal_asset` FOREIGN KEY (`internal_asset_id`) REFERENCES `assets`(`id`),
    CONSTRAINT `fk_energy_external_asset` FOREIGN KEY (`external_asset_id`) REFERENCES `external_assets`(`id`),
    CONSTRAINT `fk_energy_operator` FOREIGN KEY (`operator_id`) REFERENCES `operators`(`id`),
    CONSTRAINT `fk_energy_operation` FOREIGN KEY (`operation_id`) REFERENCES `asset_operations`(`id`),
    CONSTRAINT `fk_energy_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 12) جدول الصيانة
-- ============================================
CREATE TABLE IF NOT EXISTS `maintenance` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `asset_id` INT NOT NULL,
    `maintenance_date` DATE NOT NULL,
    `maintenance_type` VARCHAR(100) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `odometer_reading` DECIMAL(12,2) DEFAULT NULL,
    `hourmeter_reading` DECIMAL(12,2) DEFAULT NULL,
    `cost` DECIMAL(12,2) DEFAULT NULL,
    `vendor_name` VARCHAR(150) DEFAULT NULL,
    `vendor_phone` VARCHAR(50) DEFAULT NULL,
    `next_due_date` DATE DEFAULT NULL,
    `next_due_odometer` DECIMAL(12,2) DEFAULT NULL,
    `next_due_hourmeter` DECIMAL(12,2) DEFAULT NULL,
    `status` ENUM('scheduled','in_progress','completed','cancelled') DEFAULT 'completed',
    `notes` TEXT DEFAULT NULL,
    `image` VARCHAR(255) DEFAULT NULL,
    `created_by` INT NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_maintenance_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`),
    CONSTRAINT `fk_maintenance_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 13) جدول المواقع 🆕
-- ============================================
CREATE TABLE IF NOT EXISTS `locations` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL,
    `type` ENUM('warehouse','site','city','station','other') DEFAULT 'other',
    `address` TEXT DEFAULT NULL,
    `status` ENUM('active','inactive') DEFAULT 'active',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 14) جدول الإعدادات 🆕
-- ============================================
CREATE TABLE IF NOT EXISTS `settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE,
    `setting_value` TEXT DEFAULT NULL,
    `setting_group` VARCHAR(50) DEFAULT 'general',
    `display_name` VARCHAR(150) DEFAULT NULL,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 15) جدول سجل التدقيق 🆕
-- ============================================
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `action` ENUM('create','update','delete','login','logout') NOT NULL,
    `table_name` VARCHAR(100) DEFAULT NULL,
    `record_id` INT DEFAULT NULL,
    `old_data` JSON DEFAULT NULL,
    `new_data` JSON DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` TEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Indexes for Performance
-- ============================================
ALTER TABLE `asset_operations` ADD INDEX `idx_operations_date` (`operation_date`);
ALTER TABLE `asset_operations` ADD INDEX `idx_operations_operator` (`operator_id`);
ALTER TABLE `asset_operations` ADD INDEX `idx_operations_internal` (`internal_asset_id`);
ALTER TABLE `asset_operations` ADD INDEX `idx_operations_external` (`external_asset_id`);
ALTER TABLE `energy_entries` ADD INDEX `idx_energy_date` (`entry_date`);
ALTER TABLE `energy_entries` ADD INDEX `idx_energy_internal` (`internal_asset_id`);
ALTER TABLE `meter_logs` ADD INDEX `idx_meter_date` (`log_date`);
ALTER TABLE `meter_logs` ADD INDEX `idx_meter_internal` (`internal_asset_id`);
ALTER TABLE `breakdowns` ADD INDEX `idx_breakdowns_status` (`status`);
ALTER TABLE `maintenance` ADD INDEX `idx_maintenance_date` (`maintenance_date`);
ALTER TABLE `maintenance` ADD INDEX `idx_maintenance_next` (`next_due_date`);
ALTER TABLE `audit_logs` ADD INDEX `idx_audit_user` (`user_id`);
ALTER TABLE `audit_logs` ADD INDEX `idx_audit_date` (`created_at`);
ALTER TABLE `assets` ADD INDEX `idx_assets_status` (`status`);