-- AUTO TESTER - Comprehensive Inspection System Migration
-- Created: 2026-04-22
-- Description: Complete 200-point inspection system with auto-population and workflow

-- ============================================
-- 1. Comprehensive Inspection Master Tables
-- ============================================

-- Inspection Request Master
CREATE TABLE IF NOT EXISTS `comprehensive_inspection_requests` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_number` VARCHAR(50) UNIQUE NOT NULL COMMENT 'Unique inspection number',
    `customer_id` INT NOT NULL,
    `vehicle_id` INT NOT NULL,
    `inspector_id` INT NOT NULL,
    `supervisor_id` INT,
    `inspection_type_id` INT NOT NULL,
    `request_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `scheduled_date` DATETIME,
    `start_time` DATETIME,
    `completion_time` DATETIME,
    `status` ENUM('requested', 'assigned', 'in_progress', 'paused', 'completed', 'cancelled', 'under_review') DEFAULT 'requested',
    `priority_level` ENUM('low', 'medium', 'high', 'urgent', 'emergency') DEFAULT 'medium',
    `location_type` ENUM('center', 'on_site', 'customer_location') DEFAULT 'center',
    `location_address` TEXT,
    `special_instructions` TEXT,
    `customer_requirements` JSON COMMENT 'Customer specific requirements',
    `preliminary_notes` TEXT COMMENT 'Notes from initial assessment',
    `estimated_duration_minutes` INT DEFAULT 120,
    `actual_duration_minutes` INT,
    `total_points` INT DEFAULT 200,
    `completed_points` INT DEFAULT 0,
    `overall_score` DECIMAL(5,2),
    `grade` ENUM('A+', 'A', 'B+', 'B', 'C+', 'C', 'D', 'F'),
    `pass_fail_status` ENUM('pass', 'fail', 'conditional_pass') DEFAULT 'fail',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`customer_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`inspector_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`supervisor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`inspection_type_id`) REFERENCES `inspection_types`(`id`) ON DELETE RESTRICT,
    
    INDEX `idx_inspection_number` (`inspection_number`),
    INDEX `idx_customer_id` (`customer_id`),
    INDEX `idx_vehicle_id` (`vehicle_id`),
    INDEX `idx_inspector_id` (`inspector_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_request_date` (`request_date`),
    INDEX `idx_scheduled_date` (`scheduled_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Comprehensive Inspection Sections (200 Points Structure)
CREATE TABLE IF NOT EXISTS `comprehensive_inspection_sections` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `section_code` VARCHAR(20) UNIQUE NOT NULL COMMENT 'Section identifier (e.g., EXT-01)',
    `section_name_ar` VARCHAR(200) NOT NULL,
    `section_name_en` VARCHAR(200) NOT NULL,
    `section_description_ar` TEXT,
    `section_description_en` TEXT,
    `section_order` INT NOT NULL DEFAULT 0,
    `section_category` ENUM('exterior', 'interior', 'mechanical', 'electrical', 'safety', 'documentation') NOT NULL,
    `total_points_in_section` INT DEFAULT 0,
    `weight_percentage` DECIMAL(5,2) DEFAULT 0 COMMENT 'Weight in overall score',
    `is_required` BOOLEAN DEFAULT TRUE,
    `requires_special_tools` BOOLEAN DEFAULT FALSE,
    `estimated_time_minutes` INT DEFAULT 15,
    `parent_section_id` INT NULL COMMENT 'For sub-sections',
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`parent_section_id`) REFERENCES `comprehensive_inspection_sections`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_section_code` (`section_code`),
    INDEX `idx_section_order` (`section_order`),
    INDEX `idx_section_category` (`section_category`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Comprehensive Inspection Points (200 Points)
CREATE TABLE IF NOT EXISTS `comprehensive_inspection_points` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `point_number` VARCHAR(20) UNIQUE NOT NULL COMMENT 'Point identifier (e.g., EXT-001)',
    `section_id` INT NOT NULL,
    `point_name_ar` VARCHAR(300) NOT NULL,
    `point_name_en` VARCHAR(300) NOT NULL,
    `point_description_ar` TEXT,
    `point_description_en` TEXT,
    `point_order` INT NOT NULL DEFAULT 0,
    `inspection_method_ar` TEXT COMMENT 'How to inspect this point',
    `inspection_method_en` TEXT,
    `acceptance_criteria_ar` TEXT COMMENT 'What constitutes pass/fail',
    `acceptance_criteria_en` TEXT,
    `required_tools` JSON COMMENT 'Tools needed for this point',
    `safety_precautions` JSON COMMENT 'Safety measures',
    `point_type` ENUM('visual', 'measurement', 'functional', 'diagnostic', 'documentation') NOT NULL,
    `measurement_unit` VARCHAR(50) COMMENT 'Unit if measurement required',
    `min_acceptable_value` DECIMAL(10,2),
    `max_acceptable_value` DECIMAL(10,2),
    `pass_fail_type` ENUM('binary', 'graded', 'measurement') DEFAULT 'binary',
    `weight_in_section` DECIMAL(5,2) DEFAULT 1.0,
    `is_critical` BOOLEAN DEFAULT FALSE COMMENT 'Critical safety point',
    `requires_photo` BOOLEAN DEFAULT FALSE,
    `requires_measurement` BOOLEAN DEFAULT FALSE,
    `estimated_time_seconds` INT DEFAULT 60,
    `notes_template_ar` TEXT COMMENT 'Template for inspector notes',
    `notes_template_en` TEXT,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`section_id`) REFERENCES `comprehensive_inspection_sections`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_point_number` (`point_number`),
    INDEX `idx_section_id` (`section_id`),
    INDEX `idx_point_order` (`point_order`),
    INDEX `idx_point_type` (`point_type`),
    INDEX `idx_is_critical` (`is_critical`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 2. Inspection Results and Data Tables
-- ============================================

-- Comprehensive Inspection Results
CREATE TABLE IF NOT EXISTS `comprehensive_inspection_results` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_request_id` INT NOT NULL,
    `point_id` INT NOT NULL,
    `inspector_id` INT NOT NULL,
    `result_status` ENUM('pass', 'fail', 'not_applicable', 'not_inspected') DEFAULT 'not_inspected',
    `result_value` DECIMAL(10,2) COMMENT 'For measurement points',
    `result_grade` ENUM('excellent', 'good', 'fair', 'poor', 'critical') COMMENT 'For graded points',
    `notes_ar` TEXT,
    `notes_en` TEXT,
    `findings_ar` TEXT COMMENT 'Specific findings',
    `findings_en` TEXT,
    `recommendations_ar` TEXT,
    `recommendations_en` TEXT,
    `photo_evidence` JSON COMMENT 'Array of photo URLs',
    `measurement_data` JSON COMMENT 'Detailed measurement data',
    `tool_used` VARCHAR(100),
    `inspection_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `time_taken_seconds` INT,
    `confidence_level` ENUM('high', 'medium', 'low') DEFAULT 'high',
    `requires_follow_up` BOOLEAN DEFAULT FALSE,
    `follow_up_priority` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    `is_critical_finding` BOOLEAN DEFAULT FALSE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`inspection_request_id`) REFERENCES `comprehensive_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`point_id`) REFERENCES `comprehensive_inspection_points`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`inspector_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    
    UNIQUE KEY `unique_inspection_point` (`inspection_request_id`, `point_id`),
    INDEX `idx_inspection_request_id` (`inspection_request_id`),
    INDEX `idx_point_id` (`point_id`),
    INDEX `idx_result_status` (`result_status`),
    INDEX `idx_inspector_id` (`inspector_id`),
    INDEX `idx_inspection_time` (`inspection_time`),
    INDEX `idx_is_critical_finding` (`is_critical_finding`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Section Summary Results
CREATE TABLE IF NOT EXISTS `comprehensive_section_results` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_request_id` INT NOT NULL,
    `section_id` INT NOT NULL,
    `total_points_in_section` INT NOT NULL,
    `points_inspected` INT DEFAULT 0,
    `points_passed` INT DEFAULT 0,
    `points_failed` INT DEFAULT 0,
    `points_not_applicable` INT DEFAULT 0,
    `section_score` DECIMAL(5,2),
    `section_grade` ENUM('A+', 'A', 'B+', 'B', 'C+', 'C', 'D', 'F'),
    `section_status` ENUM('pass', 'fail', 'conditional_pass') DEFAULT 'fail',
    `critical_issues_count` INT DEFAULT 0,
    `recommendations_count` INT DEFAULT 0,
    `section_notes_ar` TEXT,
    `section_notes_en` TEXT,
    `section_supervisor_notes` TEXT,
    `review_status` ENUM('pending', 'approved', 'rejected', 'needs_revision') DEFAULT 'pending',
    `reviewed_by` INT,
    `review_date` DATETIME,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`inspection_request_id`) REFERENCES `comprehensive_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`section_id`) REFERENCES `comprehensive_inspection_sections`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    UNIQUE KEY `unique_inspection_section` (`inspection_request_id`, `section_id`),
    INDEX `idx_inspection_request_id` (`inspection_request_id`),
    INDEX `idx_section_id` (`section_id`),
    INDEX `idx_section_status` (`section_status`),
    INDEX `idx_review_status` (`review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. Inspection Workflow and Tracking
-- ============================================

-- Inspection Workflow Stages
CREATE TABLE IF NOT EXISTS `comprehensive_inspection_workflow` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_request_id` INT NOT NULL,
    `stage_name` VARCHAR(100) NOT NULL,
    `stage_type` ENUM('preparation', 'inspection', 'documentation', 'review', 'completion') NOT NULL,
    `stage_status` ENUM('not_started', 'in_progress', 'completed', 'failed', 'skipped') DEFAULT 'not_started',
    `assigned_to` INT COMMENT 'Person responsible for this stage',
    `started_at` DATETIME,
    `completed_at` DATETIME,
    `duration_minutes` INT,
    `stage_notes` TEXT,
    `stage_requirements` JSON COMMENT 'Requirements for this stage',
    `stage_deliverables` JSON COMMENT 'Deliverables from this stage',
    `quality_check_passed` BOOLEAN DEFAULT FALSE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`inspection_request_id`) REFERENCES `comprehensive_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_inspection_request_id` (`inspection_request_id`),
    INDEX `idx_stage_type` (`stage_type`),
    INDEX `idx_stage_status` (`stage_status`),
    INDEX `idx_assigned_to` (`assigned_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inspection Timeline and Events
CREATE TABLE IF NOT EXISTS `comprehensive_inspection_timeline` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_request_id` INT NOT NULL,
    `event_type` ENUM('request_created', 'inspection_assigned', 'inspection_started', 'section_completed', 'point_inspected', 'issue_found', 'inspection_paused', 'inspection_resumed', 'inspection_completed', 'review_started', 'review_completed', 'report_generated', 'customer_notified') NOT NULL,
    `event_description_ar` TEXT NOT NULL,
    `event_description_en` TEXT NOT NULL,
    `event_data` JSON COMMENT 'Additional event data',
    `performed_by` INT NOT NULL,
    `event_timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `duration_seconds` INT COMMENT 'For timed events',
    `location` VARCHAR(200),
    `affected_points` JSON COMMENT 'Array of point IDs affected',
    `is_critical_event` BOOLEAN DEFAULT FALSE,
    
    FOREIGN KEY (`inspection_request_id`) REFERENCES `comprehensive_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`performed_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    
    INDEX `idx_inspection_request_id` (`inspection_request_id`),
    INDEX `idx_event_type` (`event_type`),
    INDEX `idx_performed_by` (`performed_by`),
    INDEX `idx_event_timestamp` (`event_timestamp`),
    INDEX `idx_is_critical_event` (`is_critical_event`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. Media and Documentation
-- ============================================

-- Inspection Photos and Media
CREATE TABLE IF NOT EXISTS `comprehensive_inspection_media` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_request_id` INT NOT NULL,
    `point_id` INT,
    `media_type` ENUM('photo', 'video', 'document', 'audio_note') NOT NULL,
    `media_url` VARCHAR(500) NOT NULL,
    `media_title_ar` VARCHAR(200),
    `media_title_en` VARCHAR(200),
    `media_description_ar` TEXT,
    `media_description_en` TEXT,
    `file_name` VARCHAR(255),
    `file_size_bytes` INT,
    `file_mime_type` VARCHAR(100),
    `taken_by` INT NOT NULL,
    `taken_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `location_coordinates` JSON COMMENT 'GPS coordinates',
    `is_evidence` BOOLEAN DEFAULT FALSE COMMENT 'Evidence for pass/fail decision',
    `is_public` BOOLEAN DEFAULT FALSE COMMENT 'Can be shown to customer',
    `media_tags` JSON COMMENT 'Tags for categorization',
    `quality_rating` ENUM('high', 'medium', 'low') DEFAULT 'medium',
    
    FOREIGN KEY (`inspection_request_id`) REFERENCES `comprehensive_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`point_id`) REFERENCES `comprehensive_inspection_points`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`taken_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    
    INDEX `idx_inspection_request_id` (`inspection_request_id`),
    INDEX `idx_point_id` (`point_id`),
    INDEX `idx_media_type` (`media_type`),
    INDEX `idx_taken_by` (`taken_by`),
    INDEX `idx_taken_at` (`taken_at`),
    INDEX `idx_is_evidence` (`is_evidence`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5. Quality Control and Review
-- ============================================

-- Quality Control Reviews
CREATE TABLE IF NOT EXISTS `comprehensive_quality_reviews` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_request_id` INT NOT NULL,
    `review_type` ENUM('auto_quality_check', 'peer_review', 'supervisor_review', 'customer_complaint_review') NOT NULL,
    `reviewer_id` INT NOT NULL,
    `review_criteria` JSON COMMENT 'Review checklist and criteria',
    `review_scores` JSON COMMENT 'Scores for each criterion',
    `overall_quality_score` DECIMAL(5,2),
    `issues_found` JSON COMMENT 'Issues identified during review',
    `recommendations` JSON COMMENT 'Recommendations for improvement',
    `required_actions` JSON COMMENT 'Actions that must be taken',
    `review_status` ENUM('in_progress', 'completed', 'requires_follow_up') DEFAULT 'in_progress',
    `follow_up_required` BOOLEAN DEFAULT FALSE,
    `follow_up_date` DATETIME,
    `review_notes` TEXT,
    `review_completed_at` DATETIME,
    
    FOREIGN KEY (`inspection_request_id`) REFERENCES `comprehensive_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewer_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    
    INDEX `idx_inspection_request_id` (`inspection_request_id`),
    INDEX `idx_review_type` (`review_type`),
    INDEX `idx_reviewer_id` (`reviewer_id`),
    INDEX `idx_review_status` (`review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 6. Reporting and Analytics
-- ============================================

-- Final Inspection Reports
CREATE TABLE IF NOT EXISTS `comprehensive_inspection_reports` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_request_id` INT NOT NULL,
    `report_number` VARCHAR(50) UNIQUE NOT NULL,
    `report_type` ENUM('preliminary', 'final', 'summary', 'detailed') NOT NULL,
    `report_format` ENUM('pdf', 'html', 'json', 'excel') DEFAULT 'pdf',
    `report_url` VARCHAR(500),
    `report_file_path` VARCHAR(500),
    `generated_by` INT NOT NULL,
    `generation_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `report_data` JSON COMMENT 'Complete report data in JSON format',
    `customer_accessible` BOOLEAN DEFAULT TRUE,
    `public_accessible` BOOLEAN DEFAULT FALSE,
    `share_token` VARCHAR(100) UNIQUE COMMENT 'For secure sharing',
    `share_expires_at` DATETIME,
    `download_count` INT DEFAULT 0,
    `last_downloaded_at` DATETIME,
    `is_final_report` BOOLEAN DEFAULT FALSE,
    
    FOREIGN KEY (`inspection_request_id`) REFERENCES `comprehensive_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`generated_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    
    INDEX `idx_inspection_request_id` (`inspection_request_id`),
    INDEX `idx_report_number` (`report_number`),
    INDEX `idx_report_type` (`report_type`),
    INDEX `idx_generated_by` (`generated_by`),
    INDEX `idx_share_token` (`share_token`),
    INDEX `idx_is_final_report` (`is_final_report`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 7. Default Data - 200 Points Structure
-- ============================================

-- Insert Inspection Sections (200 Points Structure)
INSERT INTO `comprehensive_inspection_sections` 
(`section_code`, `section_name_ar`, `section_name_en`, `section_description_ar`, `section_description_en`, `section_order`, `section_category`, `total_points_in_section`, `weight_percentage`, `estimated_time_minutes`) VALUES

-- Exterior Section (40 Points)
('EXT-01', ' inspection', 'Exterior Body Inspection', 'inspection of all exterior body panels, paint condition, and structural integrity', 'Complete inspection of all exterior body panels, paint condition, and structural integrity', 1, 'exterior', 40, 20.0, 25),

('EXT-02', ' inspection', 'Lighting and Signals', 'inspection of all exterior lights, signals, and reflectors', 'Complete inspection of all exterior lights, signals, and reflectors', 2, 'exterior', 15, 7.5, 15),

('EXT-03', ' inspection', 'Glass and Mirrors', 'inspection of all glass components and mirrors', 'Complete inspection of all glass components and mirrors', 3, 'exterior', 10, 5.0, 10),

('EXT-04', ' inspection', 'Wheels and Tires', 'inspection of wheels, tires, and pressure monitoring systems', 'Complete inspection of wheels, tires, and pressure monitoring systems', 4, 'exterior', 15, 7.5, 15),

-- Interior Section (35 Points)
('INT-01', ' inspection', 'Dashboard and Controls', 'inspection of dashboard instruments and controls', 'Complete inspection of dashboard instruments and controls', 5, 'interior', 20, 10.0, 15),

('INT-02', ' inspection', 'Seats and Interior Trim', 'inspection of seats, upholstery, and interior trim', 'Complete inspection of seats, upholstery, and interior trim', 6, 'interior', 15, 7.5, 15),

-- Mechanical Section (60 Points)
('MEC-01', ' inspection', 'Engine and Engine Compartment', 'inspection of engine components and engine compartment', 'Complete inspection of engine components and engine compartment', 7, 'mechanical', 25, 12.5, 30),

('MEC-02', ' inspection', 'Transmission and Drivetrain', 'inspection of transmission, driveshaft, and differential', 'Complete inspection of transmission, driveshaft, and differential', 8, 'mechanical', 15, 7.5, 20),

('MEC-03', ' inspection', 'Suspension and Steering', 'inspection of suspension components and steering system', 'Complete inspection of suspension components and steering system', 9, 'mechanical', 20, 10.0, 25),

-- Electrical Section (35 Points)
('ELC-01', ' inspection', 'Battery and Electrical System', 'inspection of battery and electrical system components', 'Complete inspection of battery and electrical system components', 10, 'electrical', 20, 10.0, 20),

('ELC-02', ' inspection', 'Wiring and Connectors', 'inspection of wiring harnesses and connectors', 'Complete inspection of wiring harnesses and connectors', 11, 'electrical', 15, 7.5, 15),

-- Safety Section (25 Points)
('SAF-01', ' inspection', 'Brakes and ABS', 'inspection of brake system and ABS components', 'Complete inspection of brake system and ABS components', 12, 'safety', 15, 7.5, 20),

('SAF-02', ' inspection', 'Safety Systems', 'inspection of airbags, seatbelts, and safety systems', 'Complete inspection of airbags, seatbelts, and safety systems', 13, 'safety', 10, 5.0, 15),

-- Documentation Section (5 Points)
('DOC-01', ' inspection', 'Vehicle Documentation', 'inspection of vehicle documents and legal requirements', 'Complete inspection of vehicle documents and legal requirements', 14, 'documentation', 5, 2.5, 10);

-- Insert Sample Inspection Points (First few points from each section)
INSERT INTO `comprehensive_inspection_points` 
(`point_number`, `section_id`, `point_name_ar`, `point_name_en`, `point_description_ar`, `point_description_en`, `point_order`, `inspection_method_ar`, `inspection_method_en`, `acceptance_criteria_ar`, `acceptance_criteria_en`, `point_type`, `pass_fail_type`, `weight_in_section`, `is_critical`, `requires_photo`, `estimated_time_seconds`) VALUES

-- Exterior Body Points
('EXT-001', 1, ' inspection', 'Front Bumper Condition', 'inspection of front bumper for damage, cracks, and proper alignment', 'Visual inspection of front bumper for damage, cracks, and proper alignment', 1, ' inspection', 'Visual inspection from multiple angles', 'No cracks, dents, or misalignment', 'No cracks, dents, or misalignment', 'visual', 'binary', 1.0, FALSE, TRUE, 30),

('EXT-002', 1, ' inspection', 'Front Fender Condition - Right', 'inspection of right front fender for damage and rust', 'Visual inspection of right front fender for damage and rust', 2, ' inspection', 'Visual inspection and touch test for rust', 'No significant damage or rust', 'No significant damage or rust', 'visual', 'binary', 1.0, FALSE, TRUE, 30),

('EXT-003', 1, ' inspection', 'Front Fender Condition - Left', 'inspection of left front fender for damage and rust', 'Visual inspection of left front fender for damage and rust', 3, ' inspection', 'Visual inspection and touch test for rust', 'No significant damage or rust', 'No significant damage or rust', 'visual', 'binary', 1.0, FALSE, TRUE, 30),

-- Lighting Points
('EXT-041', 2, ' inspection', 'Headlight Operation - Right', 'inspection of right headlight functionality and alignment', 'Test right headlight operation and check alignment', 1, ' inspection', 'Turn on headlights and check operation', 'Headlight operates correctly and properly aligned', 'Headlight operates correctly and properly aligned', 'functional', 'binary', 1.0, TRUE, FALSE, 20),

('EXT-042', 2, ' inspection', 'Headlight Operation - Left', 'inspection of left headlight functionality and alignment', 'Test left headlight operation and check alignment', 2, ' inspection', 'Turn on headlights and check operation', 'Headlight operates correctly and properly aligned', 'Headlight operates correctly and properly aligned', 'functional', 'binary', 1.0, TRUE, FALSE, 20),

-- Interior Points
('INT-001', 5, ' inspection', 'Speedometer Operation', 'inspection of speedometer functionality and accuracy', 'Test speedometer operation and verify readings', 1, ' inspection', 'Start vehicle and check speedometer response', 'Speedometer responds accurately to vehicle speed', 'Speedometer responds accurately to vehicle speed', 'functional', 'binary', 1.0, TRUE, FALSE, 15),

('INT-002', 5, ' inspection', 'Fuel Gauge Operation', 'inspection of fuel gauge functionality', 'Test fuel gauge operation and accuracy', 2, ' inspection', 'Check fuel gauge response with known fuel level', 'Fuel gauge shows accurate fuel level', 'Fuel gauge shows accurate fuel level', 'functional', 'binary', 1.0, FALSE, FALSE, 10),

-- Engine Points
('MEC-001', 7, ' inspection', 'Engine Oil Level', 'inspection of engine oil level and condition', 'Check engine oil level and condition', 1, ' inspection', 'Check dipstick and inspect oil condition', 'Oil level between min and max marks, oil clean', 'Oil level between min and max marks, oil clean', 'measurement', 'binary', 1.0, TRUE, FALSE, 25),

('MEC-002', 7, ' inspection', 'Engine Oil Condition', 'inspection of engine oil condition and contamination', 'Inspect engine oil for contamination and degradation', 2, ' inspection', 'Visual inspection of oil on dipstick', 'Oil clean, no metal particles, proper color', 'Oil clean, no metal particles, proper color', 'visual', 'graded', 1.0, TRUE, TRUE, 20);

-- ============================================
-- 8. Views for Comprehensive Inspection System
-- ============================================

-- Inspection Overview View
CREATE VIEW `comprehensive_inspection_overview` AS
SELECT 
    cir.id,
    cir.inspection_number,
    cir.customer_id,
    u.full_name as customer_name,
    cir.vehicle_id,
    v.make,
    v.model,
    v.year,
    v.plate_number,
    cir.inspector_id,
    ins.full_name as inspector_name,
    cir.status,
    cir.request_date,
    cir.start_time,
    cir.completion_time,
    cir.total_points,
    cir.completed_points,
    cir.overall_score,
    cir.grade,
    cir.pass_fail_status,
    CASE 
        WHEN cir.status = 'completed' THEN cir.actual_duration_minutes
        WHEN cir.status = 'in_progress' THEN TIMESTAMPDIFF(MINUTE, cir.start_time, NOW())
        ELSE NULL
    END as current_duration_minutes,
    (cir.completed_points * 100.0 / cir.total_points) as completion_percentage
FROM comprehensive_inspection_requests cir
JOIN users u ON cir.customer_id = u.id
JOIN vehicles v ON cir.vehicle_id = v.id
JOIN users ins ON cir.inspector_id = ins.id;

-- Section Progress View
CREATE VIEW `comprehensive_section_progress` AS
SELECT 
    cir.inspection_request_id,
    cir.section_id,
    cis.section_name_ar,
    cis.section_name_en,
    cis.total_points_in_section,
    cir.points_inspected,
    cir.points_passed,
    cir.points_failed,
    cir.section_score,
    cir.section_grade,
    cir.section_status,
    (cir.points_inspected * 100.0 / cis.total_points_in_section) as section_completion_percentage
FROM comprehensive_section_results cir
JOIN comprehensive_inspection_sections cis ON cir.section_id = cis.id;

-- Critical Issues View
CREATE VIEW `comprehensive_critical_issues` AS
SELECT 
    cir.inspection_request_id,
    cir.inspection_number,
    cip.point_number,
    cip.point_name_ar,
    cip.point_name_en,
    cir.result_status,
    cir.findings_ar,
    cir.findings_en,
    cir.recommendations_ar,
    cir.recommendations_en,
    cir.inspection_time,
    ins.full_name as inspector_name
FROM comprehensive_inspection_results cir
JOIN comprehensive_inspection_points cip ON cir.point_id = cip.id
JOIN comprehensive_inspection_requests cirr ON cir.inspection_request_id = cirr.id
JOIN users ins ON cir.inspector_id = ins.id
WHERE cir.is_critical_finding = TRUE AND cir.result_status = 'fail';

-- ============================================
-- 9. Triggers for Automated Calculations
-- ============================================

DELIMITER $$

-- Update section results when point results change
CREATE TRIGGER `update_section_results_on_point_change`
AFTER INSERT ON `comprehensive_inspection_results`
FOR EACH ROW
BEGIN
    -- Update section summary
    INSERT INTO comprehensive_section_results 
    (inspection_request_id, section_id, total_points_in_section, points_inspected, points_passed, points_failed, points_not_applicable)
    VALUES (
        NEW.inspection_request_id,
        (SELECT section_id FROM comprehensive_inspection_points WHERE id = NEW.point_id),
        (SELECT COUNT(*) FROM comprehensive_inspection_points WHERE section_id = (SELECT section_id FROM comprehensive_inspection_points WHERE id = NEW.point_id)),
        1,
        CASE WHEN NEW.result_status = 'pass' THEN 1 ELSE 0 END,
        CASE WHEN NEW.result_status = 'fail' THEN 1 ELSE 0 END,
        CASE WHEN NEW.result_status = 'not_applicable' THEN 1 ELSE 0 END
    )
    ON DUPLICATE KEY UPDATE
        points_inspected = points_inspected + 1,
        points_passed = points_passed + CASE WHEN NEW.result_status = 'pass' THEN 1 ELSE 0 END,
        points_failed = points_failed + CASE WHEN NEW.result_status = 'fail' THEN 1 ELSE 0 END,
        points_not_applicable = points_not_applicable + CASE WHEN NEW.result_status = 'not_applicable' THEN 1 ELSE 0 END;
    
    -- Update overall inspection progress
    UPDATE comprehensive_inspection_requests 
    SET completed_points = (
        SELECT COUNT(*) FROM comprehensive_inspection_results 
        WHERE inspection_request_id = NEW.inspection_request_id AND result_status != 'not_inspected'
    )
    WHERE id = NEW.inspection_request_id;
END$$

-- Update inspection timeline
CREATE TRIGGER `log_inspection_timeline_event`
AFTER INSERT ON `comprehensive_inspection_results`
FOR EACH ROW
BEGIN
    INSERT INTO comprehensive_inspection_timeline 
    (inspection_request_id, event_type, event_description_ar, event_description_en, event_data, performed_by, affected_points, is_critical_event)
    VALUES (
        NEW.inspection_request_id,
        'point_inspected',
        CONCAT('Point inspected: ', (SELECT point_name_ar FROM comprehensive_inspection_points WHERE id = NEW.point_id)),
        CONCAT('Point inspected: ', (SELECT point_name_en FROM comprehensive_inspection_points WHERE id = NEW.point_id)),
        JSON_OBJECT('point_id', NEW.point_id, 'result', NEW.result_status, 'value', NEW.result_value),
        NEW.inspector_id,
        JSON_ARRAY(NEW.point_id),
        NEW.is_critical_finding
    );
END$$

DELIMITER ;

-- ============================================
-- Migration Complete
-- ============================================

-- Log the migration
INSERT INTO system_logs (log_type, message, details, created_at) VALUES
('migration', 'Comprehensive inspection system migration completed', JSON_OBJECT(
    'migration_version', '008',
    'tables_created', 10,
    'views_created', 3,
    'triggers_created', 2,
    'sections_inserted', 14,
    'sample_points_inserted', 10,
    'migration_date', CURRENT_TIMESTAMP
), CURRENT_TIMESTAMP);
