-- Auto Tester Database Setup
-- Inspection Types and Checklist System

-- 1. Inspection Types Table
CREATE TABLE IF NOT EXISTS inspection_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name_ar VARCHAR(100) NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    description_ar TEXT,
    description_en TEXT,
    category ENUM('exterior', 'mechanical', 'electronic', 'comprehensive') NOT NULL,
    estimated_time_minutes INT DEFAULT 60,
    price DECIMAL(10,2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. Checklist Categories Table
CREATE TABLE IF NOT EXISTS checklist_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name_ar VARCHAR(100) NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    icon VARCHAR(50),
    sort_order INT DEFAULT 0,
    inspection_type_id INT,
    FOREIGN KEY (inspection_type_id) REFERENCES inspection_types(id) ON DELETE CASCADE
);

-- 3. Checklist Items Table
CREATE TABLE IF NOT EXISTS checklist_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    title_ar VARCHAR(200) NOT NULL,
    title_en VARCHAR(200) NOT NULL,
    description_ar TEXT,
    description_en TEXT,
    input_type ENUM('radio', 'checkbox', 'text', 'number', 'select', 'image') DEFAULT 'radio',
    options_ar JSON, -- For select/radio options
    options_en JSON,
    required BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    points_value INT DEFAULT 0, -- Points for scoring
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES checklist_categories(id) ON DELETE CASCADE
);

-- 4. Inspection Results Table
CREATE TABLE IF NOT EXISTS inspection_results (
    id INT PRIMARY KEY AUTO_INCREMENT,
    inspection_request_id INT NOT NULL,
    checklist_item_id INT NOT NULL,
    value TEXT, -- The actual result value
    score INT DEFAULT 0, -- Points earned
    notes TEXT,
    images JSON, -- Array of image URLs
    inspector_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (inspection_request_id) REFERENCES inspection_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (checklist_item_id) REFERENCES checklist_items(id) ON DELETE CASCADE,
    FOREIGN KEY (inspector_id) REFERENCES users(id)
);

-- 5. Inspector Permissions Table
CREATE TABLE IF NOT EXISTS inspector_permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    inspector_id INT NOT NULL,
    inspection_type_id INT NOT NULL,
    granted_by INT, -- Admin who granted permission
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (inspector_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (inspection_type_id) REFERENCES inspection_types(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by) REFERENCES users(id),
    UNIQUE KEY unique_permission (inspector_id, inspection_type_id)
);

-- Insert Inspection Types Data
INSERT INTO inspection_types (name_ar, name_en, description_ar, description_en, category, estimated_time_minutes, price) VALUES
('????? ?????', 'Exterior Inspection', '????? ????? ?????? ?????? ???????', 'Complete exterior body inspection', 'exterior', 90, 500.00),
('????? ?????', 'Mechanical Inspection', '????? ?????? ?????? ???????', 'Complete mechanical systems inspection', 'mechanical', 120, 800.00),
('????? ??????', 'Electrical Inspection', '????? ?????? ???????', 'Complete electrical systems inspection', 'electronic', 60, 500.00),
('????? ?????', 'Engine Inspection', '????? ?????? ?????? ???????', 'Detailed engine and transmission inspection', 'mechanical', 90, 600.00),
('????? ???????', 'Computer Diagnostic', '????? ?????? ?????? ECU', 'ECU and computer systems diagnostic', 'electronic', 30, 400.00),
('????? ?????', 'Brake Inspection', '????? ?????? ?????? ???????', 'Complete brake system inspection', 'mechanical', 45, 300.00),
('????? ??????', 'Comprehensive Inspection', '????? ?????? ?????? (200 ????), 'Complete 200-point inspection', 'comprehensive', 240, 1500.00),
('????? ??????????', 'Dyno Test', '????? ?????? ?????? ???????', 'Performance testing on dynamometer', 'electronic', 30, 800.00),
('????? ??????', 'Uber Inspection', '????? ??????? ?????? ?????? ???????', 'Complete ride-hailing vehicle inspection for Uber/Careem', 'comprehensive', 180, 1200.00);

-- Insert Checklist Categories for Uber Inspection (ID: 9)
INSERT INTO checklist_categories (name_ar, name_en, icon, sort_order, inspection_type_id) VALUES
('????? ??????', 'Vehicle Documents', 'fa-file-alt', 1, 9),
('????? ??????', 'Exterior Condition', 'fa-car', 2, 9),
('????? ??????', 'Interior Condition', 'fa-couch', 3, 9),
('????? ??????', 'Safety Equipment', 'fa-shield-alt', 4, 9),
('????? ??????', 'Mechanical Systems', 'fa-cogs', 5, 9),
('????? ??????', 'Electrical Systems', 'fa-plug', 6, 9),
('????? ??????', 'Uber Requirements', 'fa-mobile-alt', 7, 9);

-- Insert Checklist Items for Uber Inspection

-- Vehicle Documents Category
INSERT INTO checklist_items (category_id, title_ar, title_en, description_ar, description_en, input_type, options_ar, options_en, required, sort_order, points_value) VALUES
-- Get the category ID for Vehicle Documents (should be the first one inserted for Uber)
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Vehicle Registration', '????? ?????? ??????', 'Valid vehicle registration document', 'radio', '["?????", "??????", "????? ??????"]', '["Valid", "Expired", "Missing"]', TRUE, 1, 10),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Vehicle License', '????? ?????? ??????', 'Valid driving license', 'radio', '["?????", "??????", "????? ??????"]', '["Valid", "Expired", "Missing"]', TRUE, 2, 10),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Insurance Policy', '????? ?????? ??????', 'Valid insurance policy', 'radio', '["?????", "??????", "????? ??????"]', '["Valid", "Expired", "Missing"]', TRUE, 3, 15),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Vehicle Inspection Card', '????? ?????? ??????', 'Vehicle inspection card', 'radio', '["?????", "??????", "????? ??????"]', '["Valid", "Expired", "Missing"]', TRUE, 4, 10);

-- Exterior Condition Category
INSERT INTO checklist_items (category_id, title_ar, title_en, description_ar, description_en, input_type, options_ar, options_en, required, sort_order, points_value) VALUES
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '??? ?????? ???????', 'Front Bumper', '????? ?????? ??????', 'Front bumper condition', 'radio', '["?????", "??? ????????"]', '["Good", "Damaged"]', TRUE, 1, 8),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ???????', 'Rear Bumper', '????? ?????? ??????', 'Rear bumper condition', 'radio', '["?????", "??? ????????"]', '["Good", "Damaged"]', TRUE, 2, 8),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ???????', 'All Doors', '????? ?????? ???????', 'All doors condition and operation', 'radio', '["?????", "??? ????????"]', '["Good", "Damaged"]', TRUE, 3, 10),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Vehicle Lights', '????? ?????? ???????', 'All lights working condition', 'radio', '["?????", "????? ??????"]', '["Working", "Not Working"]', TRUE, 4, 12),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ???????', 'Tires Condition', '????? ?????? ???????', 'All tires condition and tread depth', 'radio', '["?????", "???????", "????? ??????"]', '["Good", "Worn", "Damaged"]', TRUE, 5, 15);

-- Interior Condition Category
INSERT INTO checklist_items (category_id, title_ar, title_en, description_ar, description_en, input_type, options_ar, options_en, required, sort_order, points_value) VALUES
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Driver Seat', '????? ?????? ??????', 'Driver seat condition and adjustment', 'radio', '["?????", "??? ????????"]', '["Good", "Damaged"]', TRUE, 1, 8),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ???????', 'Passenger Seats', '????? ?????? ???????', 'All passenger seats condition', 'radio', '["?????", "??? ????????"]', '["Good", "Damaged"]', TRUE, 2, 8),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Seat Belts', '????? ?????? ???????', 'All seat belts working condition', 'radio', '["?????", "????? ??????"]', '["Working", "Not Working"]', TRUE, 3, 12),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Air Conditioning', '????? ?????? ??????', 'AC system working condition', 'radio', '["?????", "????? ??????"]', '["Working", "Not Working"]', TRUE, 4, 10),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Interior Cleanliness', '????? ?????? ???????', 'Interior cleanliness and odor', 'radio', '["?????", "????", "????"]', '["Clean", "Fair", "Dirty"]', TRUE, 5, 8);

-- Safety Equipment Category
INSERT INTO checklist_items (category_id, title_ar, title_en, description_ar, description_en, input_type, options_ar, options_en, required, sort_order, points_value) VALUES
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Fire Extinguisher', '????? ?????? ??????', 'Fire extinguisher presence and condition', 'radio', '["?????", "????? ??????"]', '["Available", "Missing"]', TRUE, 1, 10),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'First Aid Kit', '????? ?????? ??????', 'First aid kit presence and condition', 'radio', '["?????", "????? ??????"]', '["Available", "Missing"]', TRUE, 2, 8),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Warning Triangle', '????? ?????? ??????', 'Warning triangle presence', 'radio', '["?????", "????? ??????"]', '["Available", "Missing"]', TRUE, 3, 8),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Airbags', '????? ?????? ??????', 'Airbags system condition', 'radio', '["?????", "????? ??????"]', '["Working", "Not Working"]', TRUE, 4, 15),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ABS', 'ABS System', '????? ?????? ABS', 'ABS braking system condition', 'radio', '["?????", "????? ??????"]', '["Working", "Not Working"]', TRUE, 5, 12);

-- Mechanical Systems Category
INSERT INTO checklist_items (category_id, title_ar, title_en, description_ar, description_en, input_type, options_ar, options_en, required, sort_order, points_value) VALUES
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Engine Condition', '????? ?????? ??????', 'Engine running condition and sounds', 'radio', '["?????", "????? ??????"]', '["Good", "Issues"]', TRUE, 1, 15),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Transmission', '????? ?????? ??????', 'Transmission shifting condition', 'radio', '["?????", "????? ??????"]', '["Good", "Issues"]', TRUE, 2, 12),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Brake System', '????? ?????? ??????', 'Brake system condition and pedal feel', 'radio', '["?????", "????? ??????"]', '["Good", "Issues"]', TRUE, 3, 15),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Suspension', '????? ?????? ??????', 'Suspension system condition and noises', 'radio', '["?????", "????? ??????"]', '["Good", "Issues"]', TRUE, 4, 10),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Exhaust System', '????? ?????? ??????', 'Exhaust system condition and smoke', 'radio', '["?????", "????? ??????"]', '["Good", "Issues"]', TRUE, 5, 10);

-- Electrical Systems Category
INSERT INTO checklist_items (category_id, title_ar, title_en, description_ar, description_en, input_type, options_ar, options_en, required, sort_order, points_value) VALUES
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Battery Condition', '????? ?????? ??????', 'Battery condition and terminals', 'radio', '["?????", "????? ??????"]', '["Good", "Issues"]', TRUE, 1, 10),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Starting System', '????? ?????? ??????', 'Engine starting condition', 'radio', '["?????", "????? ??????"]', '["Good", "Issues"]', TRUE, 2, 10),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ???????', 'Lighting System', '????? ?????? ???????', 'All lights and indicators', 'radio', '["?????", "????? ??????"]', '["Working", "Issues"]', TRUE, 3, 12),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Dashboard', '????? ?????? ??????', 'Dashboard lights and warnings', 'radio', '["?????", "????? ??????"]', '["Good", "Issues"]', TRUE, 4, 8),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? GPS', 'GPS System', '????? ?????? GPS', 'GPS navigation system', 'radio', '["?????", "????? ??????"]', '["Working", "Not Working"]', FALSE, 5, 5);

-- Uber Requirements Category
INSERT INTO checklist_items (category_id, title_ar, title_en, description_ar, description_en, input_type, options_ar, options_en, required, sort_order, points_value) VALUES
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Uber Sticker', '????? ?????? Uber', 'Uber company sticker placement', 'radio', '["?????", "????? ??????"]', '["Placed", "Missing"]', TRUE, 1, 8),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Phone Mount', '????? ?????? ??????', 'Phone mount for GPS navigation', 'radio', '["?????", "????? ??????"]', '["Available", "Missing"]', TRUE, 2, 8),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Charging Ports', '????? ?????? ??????', 'USB charging ports for passengers', 'radio', '["?????", "????? ??????"]', '["Available", "Missing"]', FALSE, 3, 5),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Water Bottles', '????? ?????? ??????', 'Water bottles for passengers', 'radio', '["?????", "????? ??????"]', '["Available", "Missing"]', FALSE, 4, 5),
((SELECT id FROM checklist_categories WHERE name_ar = '????? ??????' AND inspection_type_id = 9 LIMIT 1), '????? ??????', 'Vehicle Age', '????? ?????? ??????', 'Vehicle age meets Uber requirements', 'radio', '["?????", "????? ??????"]', '["Compliant", "Not Compliant"]', TRUE, 5, 10);

-- Insert Checklist Categories for Exterior Inspection (ID: 1)
INSERT INTO checklist_categories (name_ar, name_en, icon, sort_order, inspection_type_id) VALUES
('????? ??????', 'Body Paint', 'fa-paint-brush', 1, 1),
('????? ??????', 'Chassis Frame', 'fa-cogs', 2, 1),
('????? ?????', 'Body Parts', 'fa-car', 3, 1),
('????? ??????', 'Tires & Wheels', 'fa-circle', 4, 1),
('????? ?????', 'Interior', 'fa-couch', 5, 1);

-- Insert Sample Checklist Items for Body Paint Category
INSERT INTO checklist_items (category_id, title_ar, title_en, description_ar, description_en, input_type, options_ar, options_en, required, sort_order, points_value) VALUES
(1, '????? ???????', 'Front Bumper', '????? ?????? ??????', 'Front bumper condition check', 'radio', '["?????", "??? ????????", "????/???", "????? ??????"]', '["Good", "Minor Scratch", "Dent/Scratch", "Needs Replacement"]', TRUE, 1, 10),
(1, '??? ?????? ???????', 'Front Fender Condition', '????? ?????? ???????', 'Front fender condition check', 'radio', '["?????", "??? ????????", "????/???", "????? ??????"]', '["Good", "Minor Scratch", "Dent/Scratch", "Needs Replacement"]', TRUE, 2, 10),
(1, '????? ??????', 'Hood Condition', '????? ??????', 'Hood condition check', 'radio', '["?????", "??? ????????", "????/???", "????? ??????"]', '["Good", "Minor Scratch", "Dent/Scratch", "Needs Replacement"]', TRUE, 3, 10);

-- Insert Sample Checklist Items for Chassis Frame Category
INSERT INTO checklist_items (category_id, title_ar, title_en, description_ar, description_en, input_type, options_ar, options_en, required, sort_order, points_value) VALUES
(2, '??? ?????? ???????', 'Front Frame Condition', '????? ?????? ???????', 'Front chassis frame condition', 'radio', '["?????", "????? ??????", "????? ??????", "???/?????"]', '["Good", "Minor Damage", "Major Damage", "Cut/Welded"]', TRUE, 1, 15),
(2, '????? ?????? (????/????)', 'Control Arms (Front/Rear)', '????? ?????? ???????', 'Control arms condition check', 'radio', '["?????", "????? ??????", "????? ??????", "???/?????"]', '["Good", "Minor Damage", "Major Damage", "Cut/Welded"]', TRUE, 2, 15),
(2, '????? (A, B, C)', 'Pillars (A, B, C)', '????? ?????? ???????', 'Pillar structure condition', 'radio', '["?????", "??? ??????", "????? ??????", "???/?????"]', '["Good", "Minor Damage", "Major Damage", "Cut/Welded"]', TRUE, 3, 15);

-- Insert Sample Checklist Items for Tires & Wheels Category
INSERT INTO checklist_items (category_id, title_ar, title_en, description_ar, description_en, input_type, options_ar, options_en, required, sort_order, points_value) VALUES
(4, '????? ???????', 'All Tires', '????? ?????? ???????', 'All tires condition check', 'radio', '["?????", "?????", "???????"]', '["Good", "Worn", "Damaged"]', TRUE, 1, 8),
(4, '????? ???????', 'All Wheels', '????? ?????? ???????', 'All wheels condition check', 'radio', '["?????", "?????", "?????"]', '["Good", "Damaged", "Scratched"]', TRUE, 2, 6),
(4, '????? ??????', 'Spare Tire', '????? ??????', 'Spare tire condition', 'radio', '["?????", "?????", "????? ??????", "?? ?????"]', '["Good", "Worn", "Damaged", "Not Available"]', TRUE, 3, 4);

-- Insert Inspector Permissions Sample Data
INSERT INTO inspector_permissions (inspector_id, inspection_type_id, granted_by) VALUES
(1, 1, 1), -- Inspector 1 can do Exterior Inspection
(1, 2, 1), -- Inspector 1 can do Mechanical Inspection
(1, 7, 1), -- Inspector 1 can do Comprehensive Inspection
(1, 9, 1), -- Inspector 1 can do Uber Inspection
(2, 3, 1), -- Inspector 2 can do Electrical Inspection
(2, 5, 1), -- Inspector 2 can do Computer Diagnostic
(2, 9, 1), -- Inspector 2 can do Uber Inspection
(3, 4, 1), -- Inspector 3 can do Engine Inspection
(3, 6, 1), -- Inspector 3 can do Brake Inspection
(3, 9, 1); -- Inspector 3 can do Uber Inspection

-- Create indexes for better performance
CREATE INDEX idx_inspection_results_request ON inspection_results(inspection_request_id);
CREATE INDEX idx_inspection_results_item ON inspection_results(checklist_item_id);
CREATE INDEX idx_inspector_permissions_inspector ON inspector_permissions(inspector_id);
CREATE INDEX idx_inspector_permissions_type ON inspector_permissions(inspection_type_id);
CREATE INDEX idx_checklist_items_category ON checklist_items(category_id);
CREATE INDEX idx_checklist_categories_type ON checklist_categories(inspection_type_id);
