-- AUTO TESTER - Inspection Workflow Migration
-- Created: 2026-04-22
-- Description: Complete inspection workflow management system

-- ============================================
-- 1. Inspection Types Table
-- ============================================
CREATE TABLE IF NOT EXISTS `inspection_types` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `type_code` VARCHAR(20) UNIQUE NOT NULL COMMENT 'Unique type identifier',
    `type_name_ar` VARCHAR(255) NOT NULL COMMENT 'Type name in Arabic',
    `type_name_en` VARCHAR(255) NOT NULL COMMENT 'Type name in English',
    `type_description_ar` TEXT COMMENT 'Type description in Arabic',
    `type_description_en` TEXT COMMENT 'Type description in English',
    `type_category` ENUM('comprehensive', 'specialized', 'diagnostic', 'certification', 'mobile') NOT NULL,
    
    -- Inspection details
    `inspection_points_count` INT DEFAULT 0 COMMENT 'Number of inspection points',
    `estimated_duration_minutes` INT DEFAULT 60 COMMENT 'Estimated duration in minutes',
    `base_price` DECIMAL(10,2) NOT NULL DEFAULT 0,
    `requires_appointment` BOOLEAN DEFAULT TRUE,
    `requires_supervisor_approval` BOOLEAN DEFAULT FALSE,
    `is_mobile_available` BOOLEAN DEFAULT FALSE,
    
    -- Workflow requirements
    `required_documents` JSON COMMENT 'Required documents list',
    `vehicle_requirements` JSON COMMENT 'Vehicle type requirements',
    `location_requirements` ENUM('center_only', 'mobile_only', 'both') DEFAULT 'center_only',
    `special_equipment_needed` JSON COMMENT 'Special equipment required',
    
    -- Status and visibility
    `is_active` BOOLEAN DEFAULT TRUE,
    `is_featured` BOOLEAN DEFAULT FALSE,
    `sort_order` INT DEFAULT 0,
    `icon_class` VARCHAR(100) COMMENT 'Font Awesome icon class',
    `color_code` VARCHAR(7) DEFAULT '#002b5c' COMMENT 'Hex color code',
    
    -- Timestamps
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_by` INT COMMENT 'User who created this type',
    
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_type_code` (`type_code`),
    INDEX `idx_category` (`type_category`),
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 2. Inspection Points Table
-- ============================================
CREATE TABLE IF NOT EXISTS `inspection_points` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_type_id` INT NOT NULL,
    `point_code` VARCHAR(50) NOT NULL COMMENT 'Unique point identifier',
    `point_name_ar` VARCHAR(255) NOT NULL COMMENT 'Point name in Arabic',
    `point_name_en` VARCHAR(255) NOT NULL COMMENT 'Point name in English',
    `point_description_ar` TEXT COMMENT 'Point description in Arabic',
    `point_description_en` TEXT COMMENT 'Point description in English',
    
    -- Point categorization
    `point_category` ENUM('exterior', 'interior', 'engine', 'transmission', 'brakes', 'suspension', 'electrical', 'safety', 'emissions', 'documents') NOT NULL,
    `point_priority` ENUM('critical', 'high', 'medium', 'low') DEFAULT 'medium',
    `point_order` INT DEFAULT 0 COMMENT 'Display order within category',
    
    -- Point requirements
    `is_required` BOOLEAN DEFAULT TRUE COMMENT 'Must be completed',
    `requires_photo` BOOLEAN DEFAULT FALSE COMMENT 'Photo evidence required',
    `requires_measurement` BOOLEAN DEFAULT FALSE COMMENT 'Measurement required',
    `requires_notes` BOOLEAN DEFAULT FALSE COMMENT 'Notes required',
    `measurement_unit` VARCHAR(20) COMMENT 'Unit of measurement if applicable',
    `acceptable_range_min` DECIMAL(10,2) COMMENT 'Minimum acceptable value',
    `acceptable_range_max` DECIMAL(10,2) COMMENT 'Maximum acceptable value',
    
    -- Point options
    `result_options` JSON COMMENT 'Available result options (pass/fail/good/fair/poor)',
    `default_value` VARCHAR(100) COMMENT 'Default value if any',
    `help_text_ar` TEXT COMMENT 'Help text in Arabic',
    `help_text_en` TEXT COMMENT 'Help text in English',
    
    -- Status
    `is_active` BOOLEAN DEFAULT TRUE,
    
    -- Timestamps
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`inspection_type_id`) REFERENCES `inspection_types`(`id`) ON DELETE CASCADE,
    
    UNIQUE KEY `unique_point_code` (`inspection_type_id`, `point_code`),
    INDEX `idx_inspection_type` (`inspection_type_id`),
    INDEX `idx_category` (`point_category`),
    INDEX `idx_priority` (`point_priority`),
    INDEX `idx_point_order` (`point_order`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. Inspection Workflow Stages Table
-- ============================================
CREATE TABLE IF NOT EXISTS `inspection_workflow_stages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `stage_name_ar` VARCHAR(255) NOT NULL COMMENT 'Stage name in Arabic',
    `stage_name_en` VARCHAR(255) NOT NULL COMMENT 'Stage name in English',
    `stage_code` VARCHAR(50) UNIQUE NOT NULL COMMENT 'Unique stage code',
    `stage_description_ar` TEXT COMMENT 'Stage description in Arabic',
    `stage_description_en` TEXT COMMENT 'Stage description in English',
    
    -- Stage properties
    `stage_order` INT NOT NULL COMMENT 'Sequential order in workflow',
    `stage_type` ENUM('initial', 'approval', 'assignment', 'execution', 'review', 'completion', 'final') NOT NULL,
    `estimated_duration_minutes` INT DEFAULT 0 COMMENT 'Estimated stage duration',
    
    -- Stage requirements
    `responsible_role` ENUM('customer', 'supervisor', 'inspector', 'admin', 'system') NOT NULL,
    `required_actions` JSON COMMENT 'Actions required in this stage',
    `completion_criteria` JSON COMMENT 'Criteria for stage completion',
    `auto_advance` BOOLEAN DEFAULT FALSE COMMENT 'Auto-advance to next stage',
    
    -- Stage permissions
    `can_edit` JSON COMMENT 'Roles that can edit in this stage',
    `can_view` JSON COMMENT 'Roles that can view this stage',
    `can_approve` JSON COMMENT 'Roles that can approve this stage',
    
    -- Notifications
    `notification_triggers` JSON COMMENT 'When to send notifications',
    `notification_templates` JSON COMMENT 'Notification message templates',
    
    -- Status
    `is_active` BOOLEAN DEFAULT TRUE,
    `is_required` BOOLEAN DEFAULT TRUE COMMENT 'Required stage or optional',
    
    -- Timestamps
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_stage_code` (`stage_code`),
    INDEX `idx_stage_order` (`stage_order`),
    INDEX `idx_stage_type` (`stage_type`),
    INDEX `idx_responsible_role` (`responsible_role`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. Inspection Requests Enhanced Table (Update existing)
-- ============================================
CREATE TABLE IF NOT EXISTS `inspection_requests_enhanced` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `request_number` VARCHAR(20) UNIQUE NOT NULL COMMENT 'Unique request number',
    `customer_id` INT NOT NULL,
    `inspection_type_id` INT NOT NULL,
    
    -- Vehicle information
    `vehicle_make` VARCHAR(100) NOT NULL,
    `vehicle_model` VARCHAR(100) NOT NULL,
    `vehicle_year` INT NOT NULL,
    `vehicle_color` VARCHAR(50),
    `vehicle_vin` VARCHAR(17) COMMENT 'Vehicle Identification Number',
    `vehicle_plate_number` VARCHAR(20),
    `vehicle_mileage` INT COMMENT 'Current mileage',
    `vehicle_engine_type` ENUM('gasoline', 'diesel', 'hybrid', 'electric', 'lpg') DEFAULT 'gasoline',
    `vehicle_transmission` ENUM('manual', 'automatic', 'cvt', 'dsg') DEFAULT 'manual',
    
    -- Request details
    `preferred_date` DATE,
    `preferred_time` TIME,
    `location_type` ENUM('center', 'mobile', 'online') DEFAULT 'center',
    `mobile_address` TEXT COMMENT 'Address for mobile inspection',
    `special_instructions` TEXT,
    `urgency_level` ENUM('normal', 'urgent', 'emergency') DEFAULT 'normal',
    
    -- Pricing and payment
    `quoted_price` DECIMAL(10,2),
    `final_price` DECIMAL(10,2),
    `payment_status` ENUM('pending', 'paid', 'refunded', 'partial') DEFAULT 'pending',
    `payment_method` ENUM('cash', 'card', 'online', 'wallet') DEFAULT 'cash',
    `payment_reference` VARCHAR(100),
    
    -- Workflow tracking
    `current_stage` VARCHAR(50) DEFAULT 'registration',
    `current_status` ENUM('draft', 'submitted', 'under_review', 'approved', 'assigned', 'in_progress', 'completed', 'rejected', 'cancelled') DEFAULT 'draft',
    `priority_level` ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    
    -- Assignment details
    `assigned_inspector_id` INT,
    `assigned_supervisor_id` INT,
    `assigned_at` DATETIME,
    `assignment_notes` TEXT,
    
    -- Timing
    `submitted_at` DATETIME,
    `approved_at` DATETIME,
    `started_at` DATETIME,
    `completed_at` DATETIME,
    `estimated_completion_time` DATETIME,
    `actual_duration_minutes` INT,
    
    -- Quality control
    `quality_score` DECIMAL(5,2) COMMENT 'Overall quality score (0-100)',
    `customer_rating` INT COMMENT 'Customer rating (1-5)',
    `customer_feedback` TEXT,
    `supervisor_rating` INT COMMENT 'Supervisor rating (1-5)',
    `supervisor_feedback` TEXT,
    
    -- Documents and photos
    `required_documents` JSON COMMENT 'Documents required for this inspection',
    `uploaded_documents` JSON COMMENT 'Documents uploaded by customer',
    `inspection_photos` JSON COMMENT 'Photos taken during inspection',
    `report_file_path` VARCHAR(500) COMMENT 'Path to generated report',
    `qr_code_data` TEXT COMMENT 'QR code data for verification',
    
    -- Status tracking
    `status_history` JSON COMMENT 'History of status changes',
    `stage_history` JSON COMMENT 'History of stage transitions',
    `activity_log` JSON COMMENT 'Detailed activity log',
    
    -- Communication
    `customer_notified` BOOLEAN DEFAULT FALSE,
    `inspector_notified` BOOLEAN DEFAULT FALSE,
    `supervisor_notified` BOOLEAN DEFAULT FALSE,
    `last_notification_at` DATETIME,
    
    -- Cancellation details
    `cancellation_reason` TEXT,
    `cancellation_by` INT COMMENT 'User who cancelled',
    `cancellation_at` DATETIME,
    `refund_amount` DECIMAL(10,2),
    
    -- Metadata
    `source` ENUM('web', 'mobile', 'api', 'phone', 'walk_in') DEFAULT 'web',
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    
    -- Timestamps
    `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 CASCADE,
    FOREIGN KEY (`inspection_type_id`) REFERENCES `inspection_types`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`assigned_inspector_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`assigned_supervisor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`cancellation_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_request_number` (`request_number`),
    INDEX `idx_customer_id` (`customer_id`),
    INDEX `idx_inspection_type` (`inspection_type_id`),
    INDEX `idx_current_stage` (`current_stage`),
    INDEX `idx_current_status` (`current_status`),
    INDEX `idx_assigned_inspector` (`assigned_inspector_id`),
    INDEX `idx_preferred_date` (`preferred_date`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_status_priority` (`current_status`, `priority_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5. Inspection Results Table
-- ============================================
CREATE TABLE IF NOT EXISTS `inspection_results` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_request_id` INT NOT NULL,
    `inspection_point_id` INT NOT NULL,
    `inspector_id` INT NOT NULL,
    
    -- Result data
    `result_value` VARCHAR(255) COMMENT 'Result value (pass/fail/good/fair/poor)',
    `result_numeric` DECIMAL(10,2) COMMENT 'Numeric result if applicable',
    `result_text` TEXT COMMENT 'Textual result or notes',
    `result_score` DECIMAL(5,2) COMMENT 'Score for this point (0-100)',
    
    -- Evidence
    `photo_evidence` JSON COMMENT 'Array of photo URLs',
    `video_evidence` JSON COMMENT 'Array of video URLs',
    `document_evidence` JSON COMMENT 'Array of document references',
    `measurement_data` JSON COMMENT 'Measurement details',
    
    -- Quality indicators
    `confidence_level` ENUM('low', 'medium', 'high') DEFAULT 'medium',
    `compliance_status` ENUM('compliant', 'non_compliant', 'requires_attention', 'not_applicable') DEFAULT 'compliant',
    `risk_level` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'low',
    
    -- Timing
    `inspected_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `time_taken_seconds` INT DEFAULT 0 COMMENT 'Time spent on this point',
    
    -- Review and approval
    `reviewed_by` INT COMMENT 'Supervisor who reviewed this result',
    `reviewed_at` DATETIME,
    `review_status` ENUM('pending', 'approved', 'rejected', 'requires_revision') DEFAULT 'pending',
    `review_notes` TEXT,
    
    -- Corrections
    `original_result` JSON COMMENT 'Original result before correction',
    `correction_reason` TEXT,
    `corrected_by` INT,
    `corrected_at` DATETIME,
    
    -- Metadata
    `device_used` VARCHAR(100) COMMENT 'Device used for inspection',
    `gps_coordinates` POINT COMMENT 'GPS coordinates where inspection was done',
    `weather_conditions` VARCHAR(50),
    `lighting_conditions` VARCHAR(50),
    
    FOREIGN KEY (`inspection_request_id`) REFERENCES `inspection_requests_enhanced`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`inspection_point_id`) REFERENCES `inspection_points`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`inspector_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`corrected_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    UNIQUE KEY `unique_inspection_result` (`inspection_request_id`, `inspection_point_id`),
    INDEX `idx_inspection_request` (`inspection_request_id`),
    INDEX `idx_inspection_point` (`inspection_point_id`),
    INDEX `idx_inspector` (`inspector_id`),
    INDEX `idx_compliance_status` (`compliance_status`),
    INDEX `idx_risk_level` (`risk_level`),
    INDEX `idx_inspected_at` (`inspected_at`),
    
    SPATIAL INDEX `idx_gps_coordinates` (`gps_coordinates`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 6. Workflow Notifications Table
-- ============================================
CREATE TABLE IF NOT EXISTS `workflow_notifications` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_request_id` INT NOT NULL,
    `notification_type` ENUM('stage_change', 'assignment', 'reminder', 'deadline', 'completion', 'cancellation', 'escalation') NOT NULL,
    `recipient_id` INT NOT NULL COMMENT 'User who should receive notification',
    `recipient_role` ENUM('customer', 'inspector', 'supervisor', 'admin') NOT NULL,
    
    -- Message content
    `title_ar` VARCHAR(255) NOT NULL COMMENT 'Notification title in Arabic',
    `title_en` VARCHAR(255) NOT NULL COMMENT 'Notification title in English',
    `message_ar` TEXT NOT NULL COMMENT 'Message content in Arabic',
    `message_en` TEXT NOT NULL COMMENT 'Message content in English',
    
    -- Delivery channels
    `channels` JSON COMMENT 'Delivery channels (email, sms, push, in_app)',
    `delivery_status` JSON COMMENT 'Status for each channel',
    `sent_at` DATETIME,
    `read_at` DATETIME,
    `acknowledged_at` DATETIME,
    
    -- Priority and urgency
    `priority` ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    `requires_action` BOOLEAN DEFAULT FALSE,
    `action_deadline` DATETIME,
    `action_taken` BOOLEAN DEFAULT FALSE,
    `action_taken_at` DATETIME,
    
    -- Metadata
    `trigger_event` VARCHAR(100) COMMENT 'Event that triggered this notification',
    `template_used` VARCHAR(100) COMMENT 'Template used for this notification',
    `personalization_data` JSON COMMENT 'Data used for personalization',
    
    FOREIGN KEY (`inspection_request_id`) REFERENCES `inspection_requests_enhanced`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`recipient_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_inspection_request` (`inspection_request_id`),
    INDEX `idx_recipient` (`recipient_id`),
    INDEX `idx_recipient_role` (`recipient_role`),
    INDEX `idx_notification_type` (`notification_type`),
    INDEX `idx_priority` (`priority`),
    INDEX `idx_sent_at` (`sent_at`),
    INDEX `idx_action_deadline` (`action_deadline`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 7. Inspection Reports Table
-- ============================================
CREATE TABLE IF NOT EXISTS `inspection_reports` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_request_id` INT NOT NULL,
    `report_number` VARCHAR(20) UNIQUE NOT NULL COMMENT 'Unique report number',
    `report_type` ENUM('standard', 'detailed', 'summary', 'certificate') DEFAULT 'standard',
    
    -- Report content
    `report_title_ar` VARCHAR(255) NOT NULL,
    `report_title_en` VARCHAR(255) NOT NULL,
    `report_summary_ar` TEXT,
    `report_summary_en` TEXT,
    `report_content_ar` LONGTEXT COMMENT 'Full report content in Arabic',
    `report_content_en` LONGTEXT COMMENT 'Full report content in English',
    
    -- File information
    `file_path` VARCHAR(500) COMMENT 'Path to generated PDF file',
    `file_size` INT COMMENT 'File size in bytes',
    `file_hash` VARCHAR(64) COMMENT 'SHA-256 hash for integrity',
    `qr_code_url` VARCHAR(500) COMMENT 'URL to QR code image',
    `qr_code_data` TEXT COMMENT 'QR code data content',
    
    -- Report statistics
    `total_points_inspected` INT DEFAULT 0,
    `points_passed` INT DEFAULT 0,
    `points_failed` INT DEFAULT 0,
    `points_not_applicable` INT DEFAULT 0,
    `overall_score` DECIMAL(5,2) DEFAULT 0,
    `grade` ENUM('A+', 'A', 'B+', 'B', 'C+', 'C', 'D', 'F') DEFAULT 'C',
    
    -- Recommendations
    `recommendations_ar` JSON COMMENT 'Recommendations in Arabic',
    `recommendations_en` JSON COMMENT 'Recommendations in English',
    `required_actions_ar` JSON COMMENT 'Required actions in Arabic',
    `required_actions_en` JSON COMMENT 'Required actions in English',
    
    -- Generation details
    `generated_by` INT NOT NULL COMMENT 'User who generated the report',
    `generated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `template_used` VARCHAR(100),
    `generation_method` ENUM('automatic', 'manual', 'batch') DEFAULT 'automatic',
    
    -- Distribution
    `sent_to_customer` BOOLEAN DEFAULT FALSE,
    `sent_to_inspector` BOOLEAN DEFAULT FALSE,
    `sent_to_supervisor` BOOLEAN DEFAULT FALSE,
    `email_sent_at` DATETIME,
    `whatsapp_sent_at` DATETIME,
    
    -- Verification
    `is_verified` BOOLEAN DEFAULT FALSE,
    `verified_by` INT,
    `verified_at` DATETIME,
    `verification_code` VARCHAR(20) COMMENT 'Code for online verification',
    
    -- Access control
    `access_count` INT DEFAULT 0 COMMENT 'Number of times report was accessed',
    `last_accessed_at` DATETIME,
    `download_count` INT DEFAULT 0 COMMENT 'Number of times report was downloaded',
    `share_token` VARCHAR(100) COMMENT 'Token for secure sharing',
    `share_expires_at` DATETIME,
    
    FOREIGN KEY (`inspection_request_id`) REFERENCES `inspection_requests_enhanced`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`generated_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`verified_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_inspection_request` (`inspection_request_id`),
    INDEX `idx_report_number` (`report_number`),
    INDEX `idx_generated_by` (`generated_by`),
    INDEX `idx_generated_at` (`generated_at`),
    INDEX `idx_overall_score` (`overall_score`),
    INDEX `idx_grade` (`grade`),
    INDEX `idx_share_token` (`share_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Insert Default Inspection Types
-- ============================================
INSERT INTO `inspection_types` (`type_code`, `type_name_ar`, `type_name_en`, `type_description_ar`, `type_description_en`, `type_category`, `inspection_points_count`, `estimated_duration_minutes`, `base_price`, `icon_class`, `color_code`, `sort_order`) VALUES
('COMP_FULL', ' inspection shamel', 'Comprehensive Inspection', ' inspection shamel 200 nokta bel thakaf etefnai le jami' ajza\' al-sayara', 'Comprehensive inspection with 200 points using AI technology for all vehicle parts', 'comprehensive', 200, 240, 1500.00, 'fas fa-car', '#002b5c', 1),
('COMP_ENG', 'fahs al-moharrik', 'Engine Inspection', 'fahs mutakhasas lel-moharrik wa naqil al-haraka', 'Specialized inspection for engine and transmission systems', 'specialized', 21, 120, 500.00, 'fas fa-cogs', '#dc2626', 2),
('COMP_ECU', 'fahs al-kompyoter', 'Computer Diagnostic', 'fahs ECU wa OBD2 lel-tashkhis al-kompyoteri', 'ECU and OBD2 computer diagnostic scan', 'diagnostic', 0, 30, 400.00, 'fas fa-laptop', '#0ea5e9', 3),
('COMP_DYNO', 'fahs al-daino', 'Dyno Test', 'qiyas quwa wa 'azm al-moharrik ala al-daino', 'Engine power and torque measurement on dynamometer', 'diagnostic', 0, 30, 800.00, 'fas fa-tachometer-alt', '#f59e0b', 4),
('COMP_BRAKE', 'fahs al-faramel', 'Brake & Suspension', 'fahs anzimat al-aman wa ta'liq', 'Brake system and suspension inspection', 'specialized', 15, 60, 350.00, 'fas fa-stop-circle', '#16a34a', 5),
('COMP_ELEC', 'fahs al-kahraba\'', 'Electrical System', 'fahs al-anzimat al-kahraba\'iyya', 'Electrical system and wiring inspection', 'specialized', 10, 60, 450.00, 'fas fa-bolt', '#8b5cf6', 6),
('COMP_HANDOVER', 'fahc istilam', 'Handover Inspection', 'fahs khariji mubaddir lel-istilam', 'External preliminary inspection for vehicle handover', 'certification', 89, 120, 500.00, 'fas fa-handshake', '#059669', 7),
('COMP_RIDE', 'fahc al-tatbiqat', 'Ride Hailing Inspection', 'fahc khass le-sayarat al-tatbiqat (Uber, Careem)', 'Special inspection for ride-hailing applications', 'certification', 150, 60, 400.00, 'fas fa-taxi', '#ea580c', 8),
('COMP_PREMIUM', ' inspection premium', 'Premium Inspection', ' inspection bel khususiya wa awla', 'Premium inspection with priority service and detailed analysis', 'comprehensive', 250, 180, 2500.00, 'fas fa-crown', '#fbbf24', 9),
('COMP_MOBILE', ' inspection mobile', 'Mobile Inspection', ' inspection fi makanak', 'Mobile inspection service at your location', 'mobile', 200, 180, 2000.00, 'fas fa-mobile-alt', '#10b981', 10);

-- ============================================
-- Insert Default Workflow Stages
-- ============================================
INSERT INTO `inspection_workflow_stages` (`stage_name_ar`, `stage_name_en`, `stage_code`, `stage_description_ar`, `stage_description_en`, `stage_order`, `stage_type`, `responsible_role`, `auto_advance`, `notification_triggers`) VALUES
('registration', 'Registration', 'REGISTRATION', 'tasjil al-\'amil wa talab al-fahs', 'Customer registration and inspection request submission', 1, 'initial', 'customer', FALSE, '["on_submit", "on_save_draft"]'),
('supervisor_approval', 'Supervisor Approval', 'SUPERVISOR_APPROVAL', 'mowafaqat al-mushrif', 'Supervisor review and approval of inspection request', 2, 'approval', 'supervisor', FALSE, '["on_submit", "on_approve", "on_reject"]'),
('inspector_assignment', 'Inspector Assignment', 'INSPECTOR_ASSIGNMENT', 'ta\'in al-fahhis', 'Assignment of qualified inspector to the request', 3, 'assignment', 'supervisor', TRUE, '["on_assign", "on_reassign"]'),
('inspection_execution', 'Inspection Execution', 'INSPECTION_EXECUTION', 'tanfidh al-fahs', 'Actual inspection execution by inspector', 4, 'execution', 'inspector', FALSE, '["on_start", "on_pause", "on_resume", "on_complete"]'),
('supervisor_review', 'Supervisor Review', 'SUPERVISOR_REVIEW', 'muraja\'at al-mushrif', 'Supervisor review of inspection results', 5, 'review', 'supervisor', FALSE, '["on_submit", "on_approve", "on_reject"]'),
('report_generation', 'Report Generation', 'REPORT_GENERATION', 'insha\' al-taqrir', 'Generation of inspection report and certificate', 6, 'completion', 'system', TRUE, '["on_generate", "on_send"]'),
('final_delivery', 'Final Delivery', 'FINAL_DELIVERY', 'taslim al-nihai', 'Final delivery of report to customer', 7, 'final', 'system', TRUE, '["on_deliver", "on_complete"]');

-- ============================================
-- Create Views for Common Queries
-- ============================================

-- Current Inspection Requests View
CREATE VIEW `current_inspections` AS
SELECT 
    ire.*,
    it.type_name_ar,
    it.type_name_en,
    it.estimated_duration_minutes,
    it.base_price,
    u.full_name as customer_name,
    u.phone as customer_phone,
    u.email as customer_email,
    insp.full_name as inspector_name,
    sup.full_name as supervisor_name,
    ws.stage_name_ar as current_stage_name,
    ws.stage_name_en as current_stage_name_en
FROM inspection_requests_enhanced ire
JOIN inspection_types it ON ire.inspection_type_id = it.id
JOIN users u ON ire.customer_id = u.id
LEFT JOIN users insp ON ire.assigned_inspector_id = insp.id
LEFT JOIN users sup ON ire.assigned_supervisor_id = sup.id
LEFT JOIN inspection_workflow_stages ws ON ire.current_stage = ws.stage_code
WHERE ire.current_status NOT IN ('cancelled', 'completed')
ORDER BY ire.created_at DESC;

-- Inspection Statistics View
CREATE VIEW `inspection_statistics` AS
SELECT 
    DATE(ire.created_at) as inspection_date,
    COUNT(*) as total_requests,
    SUM(CASE WHEN ire.current_status = 'completed' THEN 1 ELSE 0 END) as completed_inspections,
    SUM(CASE WHEN ire.current_status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_inspections,
    AVG(ire.actual_duration_minutes) as avg_duration,
    AVG(ire.quality_score) as avg_quality_score,
    AVG(ire.customer_rating) as avg_customer_rating,
    SUM(ire.final_price) as total_revenue,
    AVG(ire.final_price) as avg_revenue_per_inspection
FROM inspection_requests_enhanced ire
GROUP BY DATE(ire.created_at)
ORDER BY inspection_date DESC;

-- Inspector Performance View
CREATE VIEW `inspector_performance_summary` AS
SELECT 
    insp.id as inspector_id,
    insp.full_name,
    COUNT(ire.id) as total_inspections,
    SUM(CASE WHEN ire.current_status = 'completed' THEN 1 ELSE 0 END) as completed_inspections,
    AVG(ire.actual_duration_minutes) as avg_duration,
    AVG(ire.quality_score) as avg_quality_score,
    AVG(ire.customer_rating) as avg_customer_rating,
    SUM(ire.final_price) as total_revenue,
    AVG(ire.final_price) as avg_revenue_per_inspection,
    DATE_FORMAT(MAX(ire.completed_at), '%Y-%m') as last_inspection_month
FROM users insp
JOIN inspection_requests_enhanced ire ON insp.id = ire.assigned_inspector_id
WHERE insp.role = 'inspector'
GROUP BY insp.id, insp.full_name
ORDER BY avg_quality_score DESC;

-- ============================================
-- Create Triggers for Workflow Automation
-- ============================================

DELIMITER $$
CREATE TRIGGER `trigger_workflow_notification`
AFTER UPDATE ON `inspection_requests_enhanced`
FOR EACH ROW
BEGIN
    -- Send notification when stage changes
    IF NEW.current_stage != OLD.current_stage THEN
        INSERT INTO workflow_notifications 
        (inspection_request_id, notification_type, recipient_id, recipient_role,
         title_ar, title_en, message_ar, message_en, channels, priority)
        SELECT 
            NEW.id,
            'stage_change',
            CASE 
                WHEN NEW.assigned_inspector_id IS NOT NULL THEN NEW.assigned_inspector_id
                WHEN NEW.assigned_supervisor_id IS NOT NULL THEN NEW.assigned_supervisor_id
                ELSE NEW.customer_id
            END,
            CASE 
                WHEN NEW.assigned_inspector_id IS NOT NULL THEN 'inspector'
                WHEN NEW.assigned_supervisor_id IS NOT NULL THEN 'supervisor'
                ELSE 'customer'
            END,
            CONCAT('Stage updated: ', NEW.current_stage),
            CONCAT('Stage updated: ', NEW.current_stage),
            CONCAT('Your inspection has moved to stage: ', NEW.current_stage),
            CONCAT('Your inspection has moved to stage: ', NEW.current_stage),
            '["in_app", "email"]',
            'medium'
        FROM DUAL
        WHERE NEW.current_stage != OLD.current_stage;
    END IF;
    
    -- Update stage history
    IF NEW.current_stage != OLD.current_stage THEN
        UPDATE inspection_requests_enhanced 
        SET stage_history = JSON_ARRAY_APPEND(
            COALESCE(stage_history, JSON_ARRAY()),
            '$',
            JSON_OBJECT(
                'stage', NEW.current_stage,
                'previous_stage', OLD.current_stage,
                'changed_at', NOW(),
                'changed_by', NEW.updated_by
            )
        )
        WHERE id = NEW.id;
    END IF;
END$$
DELIMITER ;

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

-- Log the migration
INSERT INTO system_logs (log_type, message, details, created_at) VALUES
('migration', 'Inspection workflow system migration completed', JSON_OBJECT(
    'migration_version', '005',
    'tables_created', 7,
    'views_created', 3,
    'triggers_created', 1,
    'inspection_types_inserted', 10,
    'workflow_stages_inserted', 7,
    'migration_date', CURRENT_TIMESTAMP
), CURRENT_TIMESTAMP);
