-- ============================================================
--  نظام صيانة الحافلات - SignalBusMaint
--  قاعدة البيانات MySQL - cPanel
-- ============================================================

SET NAMES 'utf8mb4';
SET CHARACTER SET utf8mb4;

-- ============================================================
-- 1. المستخدمون والصلاحيات
-- ============================================================
CREATE TABLE users (
    user_id       INT AUTO_INCREMENT PRIMARY KEY,
    full_name     VARCHAR(100) NOT NULL,
    username      VARCHAR(50)  NOT NULL UNIQUE,
    email         VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role          ENUM('admin','fleet_manager','technician','inventory_staff') NOT NULL DEFAULT 'technician',
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 2. الحافلات
-- ============================================================
CREATE TABLE buses (
    bus_id        INT AUTO_INCREMENT PRIMARY KEY,
    plate_number  VARCHAR(20)  NOT NULL UNIQUE,
    model         VARCHAR(100) NOT NULL,
    bus_type      ENUM('city','intercity','school','articulated','minibus') NOT NULL,
    manufacturer  VARCHAR(100) NOT NULL,
    year          YEAR         NOT NULL,
    capacity      SMALLINT     NOT NULL,
    mileage       INT          NOT NULL DEFAULT 0,
    status        ENUM('operational','maintenance','out_of_service','retired') NOT NULL DEFAULT 'operational',
    purchase_date DATE         NOT NULL,
    notes         TEXT,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 3. السائقون
-- ============================================================
CREATE TABLE drivers (
    driver_id      INT AUTO_INCREMENT PRIMARY KEY,
    full_name      VARCHAR(100) NOT NULL,
    license_number VARCHAR(50)  NOT NULL UNIQUE,
    license_expiry DATE         NOT NULL,
    phone          VARCHAR(20),
    email          VARCHAR(100),
    status         ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
    assigned_bus   INT,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (assigned_bus) REFERENCES buses(bus_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 4. الفنيون (داخليون وخارجيون)
-- ============================================================
CREATE TABLE technicians (
    tech_id       INT AUTO_INCREMENT PRIMARY KEY,
    full_name     VARCHAR(100) NOT NULL,
    tech_type     ENUM('internal','external') NOT NULL DEFAULT 'internal',
    -- للفني الداخلي: ربط بجدول users
    user_id       INT,
    -- للفني الخارجي أو مركز الصيانة
    company_name  VARCHAR(150),   -- اسم مركز الصيانة / الشركة
    specialty     VARCHAR(100),   -- التخصص
    phone         VARCHAR(20),
    phone2        VARCHAR(20),   -- رقم هاتف ثاني
    email         VARCHAR(100),
    address       TEXT,
    contract_ref  VARCHAR(100),   -- رقم العقد (للخارجي)
    notes         TEXT,          -- ملاحظات خاصة بالفني
    hourly_rate   DECIMAL(10,2),
    is_active     TINYINT(1) NOT NULL DEFAULT 1,
    created_at    DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 5. أوامر العمل (Work Orders)
-- ============================================================
CREATE TABLE work_orders (
    wo_id             INT AUTO_INCREMENT PRIMARY KEY,
    wo_number         VARCHAR(20)  NOT NULL UNIQUE,  -- مثل WO-2501
    bus_id            INT          NOT NULL,
    tech_id           INT,
    maintenance_type  ENUM('oil_change','brake_inspection','tire_rotation','engine_service','battery_replacement','other') NOT NULL,
    priority          ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
    status            ENUM('open','in_progress','pending_parts','completed','cancelled') NOT NULL DEFAULT 'open',
    -- نوع الصيانة: داخلية أو خارجية
    service_location  ENUM('internal','external') NOT NULL DEFAULT 'internal',
    external_center   VARCHAR(150),  -- اسم مركز الصيانة الخارجي
    external_ref      VARCHAR(100),  -- رقم مرجعي لدى المركز الخارجي
    -- التواريخ
    scheduled_date    DATE,
    started_at        DATETIME,
    completed_at      DATETIME,
    -- التكاليف
    labor_cost        DECIMAL(10,2) DEFAULT 0,
    parts_cost        DECIMAL(10,2) DEFAULT 0,
    -- الملاحظات
    description       TEXT,         -- وصف المشكلة
    notes             TEXT,         -- ملاحظات إضافية (الفني / المدير)
    created_by        INT,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (bus_id)      REFERENCES buses(bus_id)       ON DELETE RESTRICT,
    FOREIGN KEY (tech_id)     REFERENCES technicians(tech_id) ON DELETE SET NULL,
    FOREIGN KEY (created_by)  REFERENCES users(user_id)       ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 6. الصيانة الوقائية (جداول دورية)
-- ============================================================
CREATE TABLE preventive_maintenance (
    pm_id           INT AUTO_INCREMENT PRIMARY KEY,
    bus_id          INT  NOT NULL,
    maintenance_type ENUM('oil_change','brake_inspection','tire_rotation','engine_service','battery_replacement','other') NOT NULL,
    interval_km     INT,            -- كل كم
    interval_days   INT,            -- أو كل كذا يوم
    last_done_date  DATE,
    last_done_km    INT,
    next_due_date   DATE,
    next_due_km     INT,
    notes           TEXT,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bus_id) REFERENCES buses(bus_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 7. قطع الغيار (المخزون)
-- ============================================================
CREATE TABLE spare_parts (
    part_id       INT AUTO_INCREMENT PRIMARY KEY,
    part_name     VARCHAR(150) NOT NULL,
    part_number   VARCHAR(100),
    category      ENUM('engine','brakes','tires','electrical','filters','lubricants','body','other') NOT NULL,
    unit          VARCHAR(20)  NOT NULL DEFAULT 'قطعة',
    quantity      INT          NOT NULL DEFAULT 0,
    min_quantity  INT          NOT NULL DEFAULT 5,   -- الحد الأدنى للتنبيه
    unit_price    DECIMAL(10,2),
    supplier      VARCHAR(150),
    location      VARCHAR(100), -- مكان التخزين في المستودع
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 8. استخدام قطع الغيار في أوامر العمل
-- ============================================================
CREATE TABLE work_order_parts (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    wo_id      INT          NOT NULL,
    part_id    INT          NOT NULL,
    qty_used   INT          NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2),
    FOREIGN KEY (wo_id)   REFERENCES work_orders(wo_id)   ON DELETE CASCADE,
    FOREIGN KEY (part_id) REFERENCES spare_parts(part_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 9. الوقود
-- ============================================================
CREATE TABLE fuel_records (
    fuel_id     INT AUTO_INCREMENT PRIMARY KEY,
    bus_id      INT           NOT NULL,
    driver_id   INT,
    date        DATE          NOT NULL,
    liters      DECIMAL(8,2)  NOT NULL,
    cost        DECIMAL(10,2) NOT NULL,
    mileage_at  INT,          -- عداد الكيلومترات وقت التعبئة
    station     VARCHAR(100),
    notes       TEXT,
    created_by  INT,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bus_id)     REFERENCES buses(bus_id)     ON DELETE RESTRICT,
    FOREIGN KEY (driver_id)  REFERENCES drivers(driver_id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(user_id)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 10. الإطارات
-- ============================================================
CREATE TABLE tire_records (
    tire_id       INT AUTO_INCREMENT PRIMARY KEY,
    bus_id        INT  NOT NULL,
    action        ENUM('install','rotation','replace','inspection') NOT NULL,
    tire_position VARCHAR(20),  -- مثل: أمامي يسار
    tire_brand    VARCHAR(100),
    tire_size     VARCHAR(50),
    mileage_at    INT,
    date          DATE NOT NULL,
    cost          DECIMAL(10,2),
    notes         TEXT,
    created_by    INT,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bus_id)     REFERENCES buses(bus_id)   ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(user_id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 11. التنبيهات
-- ============================================================
CREATE TABLE alerts (
    alert_id    INT AUTO_INCREMENT PRIMARY KEY,
    type        ENUM('maintenance_due','low_stock','license_expiry','fuel_low','inspection_due') NOT NULL,
    severity    ENUM('info','warning','critical') NOT NULL DEFAULT 'warning',
    bus_id      INT,
    part_id     INT,
    message     TEXT NOT NULL,
    is_read     TINYINT(1) NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bus_id)  REFERENCES buses(bus_id)       ON DELETE CASCADE,
    FOREIGN KEY (part_id) REFERENCES spare_parts(part_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- بيانات أولية
-- ============================================================
-- مستخدم admin افتراضي (password: Admin@1234)
INSERT INTO users (full_name, username, email, password_hash, role) VALUES
('المدير العام', 'admin', 'admin@signalbusmaint.kw', '$2b$10$rQZ8K3mX2vL9pN1jY6wHuO5sT4cA7dE0fI8gB2hC3nM9oP6qR1tU', 'admin');

-- أنواع الصيانة الأساسية مع الفترات الافتراضية
-- (ستُدار من خلال preventive_maintenance لكل حافلة)