CREATE TABLE IF NOT EXISTS vehicle_inspections (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    inspection_date DATE NOT NULL,
    inspector_name VARCHAR(200) NOT NULL DEFAULT '',
    overall_result ENUM('pass','fail') NOT NULL DEFAULT 'pass',
    notes TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    deleted_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_inspection_vehicle (vehicle_id),
    KEY idx_inspection_date (inspection_date),
    CONSTRAINT fk_inspection_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS inspection_checklist_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    category ENUM('body_interior','devices_routing') NOT NULL,
    item_name VARCHAR(200) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    KEY idx_checklist_category (category, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO inspection_checklist_items (category, item_name, sort_order) VALUES
('body_interior', 'Exterior Body Panels', 1),
('body_interior', 'Windshield & Windows', 2),
('body_interior', 'Doors & Locks', 3),
('body_interior', 'Seats & Upholstery', 4),
('body_interior', 'Dashboard & Controls', 5),
('body_interior', 'Interior Lighting', 6),
('body_interior', 'Floor Mats & Carpets', 7),
('body_interior', 'Steering Wheel & Column', 8),
('body_interior', 'Sun Visors & Mirrors', 9),
('body_interior', 'Center Console & Storage', 10),
('body_interior', 'AC / Heater Operation', 11),
('body_interior', 'Audio System', 12),
('body_interior', 'Seat Belts', 13),
('body_interior', 'Headliner Condition', 14),
('devices_routing', 'Headlights (Low / High Beam)', 1),
('devices_routing', 'Tail Lights & Brake Lights', 2),
('devices_routing', 'Turn Signals (Front / Rear)', 3),
('devices_routing', 'Hazard Lights', 4),
('devices_routing', 'Reverse Lights', 5),
('devices_routing', 'License Plate Light', 6),
('devices_routing', 'Fog Lights', 7),
('devices_routing', 'Horn', 8),
('devices_routing', 'Windshield Wipers (Front / Rear)', 9),
('devices_routing', 'Washer Fluid Spray', 10),
('devices_routing', 'Tire Pressure Monitoring System', 11),
('devices_routing', 'Rearview Camera', 12),
('devices_routing', 'Parking Sensors', 13),
('devices_routing', 'GPS Tracker', 14),
('devices_routing', 'Dash Camera', 15),
('devices_routing', 'Speedometer / Odometer', 16),
('devices_routing', 'Warning Lights (Check Engine, ABS, Airbag)', 17),
('devices_routing', 'Power Windows', 18),
('devices_routing', 'Power Mirrors', 19),
('devices_routing', 'Fuel Gauge', 20);

CREATE TABLE IF NOT EXISTS inspection_results (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    inspection_id BIGINT UNSIGNED NOT NULL,
    checklist_item_id BIGINT UNSIGNED NOT NULL,
    result ENUM('pass','fail','na') NOT NULL DEFAULT 'na',
    remarks TEXT NULL,
    PRIMARY KEY (id),
    KEY idx_result_inspection (inspection_id),
    KEY idx_result_item (checklist_item_id),
    CONSTRAINT fk_result_inspection FOREIGN KEY (inspection_id) REFERENCES vehicle_inspections(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_result_checklist FOREIGN KEY (checklist_item_id) REFERENCES inspection_checklist_items(id) ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE KEY uq_inspection_item (inspection_id, checklist_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (code, description) VALUES
('vehicle_inspection.view', 'View vehicle inspections'),
('vehicle_inspection.create', 'Create vehicle inspections'),
('vehicle_inspection.edit', 'Edit vehicle inspections'),
('vehicle_inspection.delete', 'Delete vehicle inspections');