-- AUTO TESTER - Quality Control System Migration
-- Created: 2026-04-22
-- Description: Complete quality control and monitoring system

-- ============================================
-- 1. Inspection Reviews Table
-- ============================================
CREATE TABLE IF NOT EXISTS `inspection_reviews` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_id` INT NOT NULL COMMENT 'ID of the inspection being reviewed',
    `reviewer_id` INT NOT NULL COMMENT 'ID of reviewer (supervisor/peer/admin)',
    `reviewer_type` ENUM('supervisor', 'peer', 'admin', 'ai') NOT NULL DEFAULT 'supervisor',
    `review_type` ENUM('random', 'scheduled', 'complaint', 'auto', 'peer') DEFAULT 'random',
    
    -- Original inspection data
    `original_status` VARCHAR(50) COMMENT 'Status before review',
    `original_score` DECIMAL(5,2) COMMENT 'Original inspection score',
    
    -- Review results
    `review_status` VARCHAR(50) COMMENT 'Status after review',
    `is_match` BOOLEAN DEFAULT TRUE COMMENT 'Whether review matches original',
    `discrepancy_score` DECIMAL(5,2) DEFAULT 0 COMMENT 'Level of discrepancy (0-100)',
    
    -- Quality scores (0-100)
    `accuracy_score` DECIMAL(5,2) COMMENT 'Accuracy of inspection (0-100)',
    `completeness_score` DECIMAL(5,2) COMMENT 'Completeness of inspection (0-100)',
    `professionalism_score` DECIMAL(5,2) COMMENT 'Professionalism of inspector (0-100)',
    `documentation_score` DECIMAL(5,2) COMMENT 'Documentation quality (0-100)',
    `overall_score` DECIMAL(5,2) COMMENT 'Overall review score',
    
    -- Review details
    `notes` TEXT COMMENT 'Review notes and observations',
    `corrections` JSON COMMENT 'Required corrections in JSON format',
    `attachments` JSON COMMENT 'Review attachments URLs',
    `strengths` TEXT COMMENT 'Inspector strengths observed',
    `weaknesses` TEXT COMMENT 'Areas needing improvement',
    
    -- Status and timestamps
    `status` ENUM('pending', 'in_progress', 'approved', 'rejected', 'revision_needed') DEFAULT 'pending',
    `priority` ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    `assigned_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `started_at` DATETIME,
    `completed_at` DATETIME,
    `review_duration` INT COMMENT 'Review duration in minutes',
    
    -- Reviewer feedback
    `reviewer_confidence` ENUM('low', 'medium', 'high') DEFAULT 'medium',
    `would_recommend` BOOLEAN DEFAULT TRUE,
    `additional_training_needed` BOOLEAN DEFAULT FALSE,
    `training_recommendations` JSON,
    
    FOREIGN KEY (`inspection_id`) REFERENCES `inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_inspection` (`inspection_id`),
    INDEX `idx_reviewer` (`reviewer_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_review_type` (`review_type`),
    INDEX `idx_assigned_at` (`assigned_at`),
    INDEX `idx_completed_at` (`completed_at`),
    INDEX `idx_overall_score` (`overall_score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 2. Inspector Performance Tracking
-- ============================================
CREATE TABLE IF NOT EXISTS `inspector_performance` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspector_id` INT NOT NULL,
    `period_start` DATE NOT NULL,
    `period_end` DATE NOT NULL,
    `period_type` ENUM('daily', 'weekly', 'monthly', 'quarterly', 'yearly') DEFAULT 'monthly',
    
    -- Quantity statistics
    `total_inspections` INT DEFAULT 0,
    `completed_inspections` INT DEFAULT 0,
    `cancelled_inspections` INT DEFAULT 0,
    `avg_completion_time` INT DEFAULT 0 COMMENT 'Average completion time in minutes',
    `total_inspection_time` INT DEFAULT 0 COMMENT 'Total time spent on inspections',
    
    -- Quality statistics
    `avg_accuracy` DECIMAL(5,2) DEFAULT 0 COMMENT 'Average accuracy score (0-100)',
    `avg_completeness` DECIMAL(5,2) DEFAULT 0 COMMENT 'Average completeness score (0-100)',
    `avg_professionalism` DECIMAL(5,2) DEFAULT 0 COMMENT 'Average professionalism score (0-100)',
    `avg_documentation` DECIMAL(5,2) DEFAULT 0 COMMENT 'Average documentation score (0-100)',
    `peer_review_score` DECIMAL(5,2) DEFAULT 0 COMMENT 'Average peer review score (0-100)',
    `supervisor_review_score` DECIMAL(5,2) DEFAULT 0 COMMENT 'Average supervisor review score (0-100)',
    
    -- Customer feedback
    `avg_customer_rating` DECIMAL(3,2) DEFAULT 0 COMMENT 'Average customer rating (1-5)',
    `total_customer_reviews` INT DEFAULT 0,
    `positive_review_percentage` DECIMAL(5,2) DEFAULT 0,
    
    -- Error and complaint rates
    `error_rate` DECIMAL(5,2) DEFAULT 0 COMMENT 'Error rate percentage',
    `revision_rate` DECIMAL(5,2) DEFAULT 0 COMMENT 'Revision rate percentage',
    `complaint_rate` DECIMAL(5,2) DEFAULT 0 COMMENT 'Complaint rate percentage',
    `reinspection_rate` DECIMAL(5,2) DEFAULT 0 COMMENT 'Reinspection rate percentage',
    
    -- Performance classification
    `performance_grade` ENUM('A+', 'A', 'B+', 'B', 'C+', 'C', 'D', 'F') DEFAULT 'C',
    `performance_score` DECIMAL(5,2) DEFAULT 0 COMMENT 'Overall performance score (0-100)',
    `trend` ENUM('improving', 'stable', 'declining') DEFAULT 'stable',
    `rank_in_period` INT DEFAULT 0 COMMENT 'Rank among inspectors in period',
    
    -- Training and development
    `needs_training` BOOLEAN DEFAULT FALSE,
    `training_areas` JSON COMMENT 'Areas needing training',
    `training_completed` JSON COMMENT 'Completed training sessions',
    `certifications` JSON COMMENT 'Current certifications',
    `skill_level` ENUM('beginner', 'intermediate', 'advanced', 'expert') DEFAULT 'intermediate',
    
    -- Specializations
    `specializations` JSON COMMENT 'Inspector specializations',
    `preferred_inspection_types` JSON COMMENT 'Preferred inspection types',
    `expertise_areas` JSON COMMENT 'Areas of expertise',
    
    -- Metrics and KPIs
    `productivity_score` DECIMAL(5,2) DEFAULT 0,
    `efficiency_score` DECIMAL(5,2) DEFAULT 0,
    `quality_score` DECIMAL(5,2) DEFAULT 0,
    `customer_satisfaction_score` DECIMAL(5,2) DEFAULT 0,
    
    -- Timestamps
    `calculated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`inspector_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_inspector` (`inspector_id`),
    INDEX `idx_period` (`period_start`, `period_end`),
    INDEX `idx_period_type` (`period_type`),
    INDEX `idx_performance_grade` (`performance_grade`),
    INDEX `idx_performance_score` (`performance_score`),
    INDEX `idx_calculated_at` (`calculated_at`),
    UNIQUE KEY `unique_period` (`inspector_id`, `period_start`, `period_end`, `period_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. Customer Complaints Management
-- ============================================
CREATE TABLE IF NOT EXISTS `customer_complaints` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_id` INT NOT NULL,
    `customer_id` INT NOT NULL,
    `complaint_number` VARCHAR(20) UNIQUE COMMENT 'Unique complaint number',
    
    -- Complaint details
    `complaint_type` ENUM('accuracy', 'professionalism', 'delay', 'damage', 'pricing', 'communication', 'other') NOT NULL,
    `severity` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    `urgency` ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT NOT NULL,
    `expected_resolution` TEXT,
    
    -- Supporting evidence
    `attachments` JSON COMMENT 'Supporting documents and images',
    `photo_evidence` JSON COMMENT 'Photo URLs as evidence',
    `video_evidence` JSON COMMENT 'Video URLs as evidence',
    `witness_contacts` JSON COMMENT 'Witness contact information',
    
    -- Investigation details
    `investigator_id` INT COMMENT 'ID of investigator',
    `investigation_notes` TEXT,
    `investigation_findings` JSON,
    `root_cause_analysis` TEXT,
    
    -- Resolution details
    `status` ENUM('pending', 'assigned', 'investigating', 'resolved', 'dismissed', 'escalated') DEFAULT 'pending',
    `resolution` TEXT,
    `resolution_type` ENUM('refund', 'reinspection', 'training', 'compensation', 'apology', 'other'),
    `compensation_amount` DECIMAL(10,2),
    `compensation_type` ENUM('cash', 'credit', 'discount', 'free_service'),
    `action_taken` TEXT,
    
    -- Impact assessment
    `customer_satisfaction_after` ENUM('very_dissatisfied', 'dissatisfied', 'neutral', 'satisfied', 'very_satisfied'),
    `would_return` BOOLEAN,
    `would_recommend` BOOLEAN,
    
    -- Follow-up
    `follow_up_required` BOOLEAN DEFAULT FALSE,
    `follow_up_date` DATE,
    `follow_up_notes` TEXT,
    
    -- Timestamps
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `assigned_at` DATETIME,
    `investigation_started_at` DATETIME,
    `resolved_at` DATETIME,
    `closed_at` DATETIME,
    
    -- SLA tracking
    `response_time_hours` INT COMMENT 'Response time in hours',
    `resolution_time_hours` INT COMMENT 'Resolution time in hours',
    `sla_met` BOOLEAN DEFAULT TRUE,
    
    FOREIGN KEY (`inspection_id`) REFERENCES `inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`customer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`investigator_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_inspection` (`inspection_id`),
    INDEX `idx_customer` (`customer_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_complaint_type` (`complaint_type`),
    INDEX `idx_severity` (`severity`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_resolved_at` (`resolved_at`),
    INDEX `idx_investigator` (`investigator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. Peer Review System
-- ============================================
CREATE TABLE IF NOT EXISTS `peer_reviews` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_id` INT NOT NULL,
    `reviewer_inspector_id` INT NOT NULL,
    `reviewee_inspector_id` INT NOT NULL,
    `review_cycle` VARCHAR(20) DEFAULT 'monthly' COMMENT 'Review cycle identifier',
    
    -- Review scores (1-5)
    `technical_accuracy_score` INT CHECK (technical_accuracy_score BETWEEN 1 AND 5),
    `thoroughness_score` INT CHECK (thoroughness_score BETWEEN 1 AND 5),
    `documentation_score` INT CHECK (documentation_score BETWEEN 1 AND 5),
    `professionalism_score` INT CHECK (professionalism_score BETWEEN 1 AND 5),
    `communication_score` INT CHECK (communication_score BETWEEN 1 AND 5),
    `overall_score` DECIMAL(3,2) COMMENT 'Calculated overall score',
    
    -- Review content
    `feedback` TEXT COMMENT 'General feedback',
    `strengths` TEXT COMMENT 'Observed strengths',
    `areas_for_improvement` TEXT COMMENT 'Areas needing improvement',
    `specific_examples` JSON COMMENT 'Specific examples with timestamps',
    `recommendations` TEXT COMMENT 'Recommendations for improvement',
    
    -- Review metadata
    `is_anonymous` BOOLEAN DEFAULT TRUE,
    `is_mandatory` BOOLEAN DEFAULT FALSE,
    `review_type` ENUM('routine', 'complaint_related', 'training_assessment', 'promotion_review') DEFAULT 'routine',
    `confidence_level` ENUM('low', 'medium', 'high') DEFAULT 'medium',
    
    -- Follow-up actions
    `requires_follow_up` BOOLEAN DEFAULT FALSE,
    `follow_up_date` DATE,
    `improvement_plan` JSON,
    `training_suggested` JSON,
    
    -- Status and timestamps
    `status` ENUM('pending', 'completed', 'skipped', 'disputed') DEFAULT 'pending',
    `submitted_at` DATETIME,
    `reviewed_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `acknowledged_at` DATETIME,
    
    -- Dispute resolution
    `dispute_reason` TEXT,
    `dispute_resolved_by` INT,
    `dispute_resolution` TEXT,
    `dispute_resolved_at` DATETIME,
    
    FOREIGN KEY (`inspection_id`) REFERENCES `inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewer_inspector_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewee_inspector_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`dispute_resolved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_reviewee` (`reviewee_inspector_id`),
    INDEX `idx_reviewer` (`reviewer_inspector_id`),
    INDEX `idx_inspection` (`inspection_id`),
    INDEX `idx_review_cycle` (`review_cycle`),
    INDEX `idx_status` (`status`),
    INDEX `idx_reviewed_at` (`reviewed_at`),
    INDEX `idx_overall_score` (`overall_score`),
    
    -- Prevent self-review and duplicate reviews
    CHECK (`reviewer_inspector_id` != `reviewee_inspector_id`),
    UNIQUE KEY `unique_review` (`inspection_id`, `reviewer_inspector_id`, `reviewee_inspector_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5. Photo Audit and AI Analysis
-- ============================================
CREATE TABLE IF NOT EXISTS `photo_audit` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_id` INT NOT NULL,
    `photo_url` VARCHAR(500) NOT NULL,
    `photo_hash` VARCHAR(64) COMMENT 'SHA-256 hash for integrity verification',
    `photo_type` ENUM('exterior', 'interior', 'damage', 'engine', 'undercarriage', 'documents', 'other') DEFAULT 'other',
    
    -- AI Analysis Results
    `ai_analysis` JSON COMMENT 'Complete AI analysis results',
    `ai_confidence` DECIMAL(5,2) COMMENT 'AI confidence level (0-100)',
    `ai_model_version` VARCHAR(20) DEFAULT 'v1.0',
    `analysis_timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- Authenticity and tampering detection
    `is_authentic` BOOLEAN DEFAULT TRUE,
    `tampering_detected` BOOLEAN DEFAULT FALSE,
    `tampering_type` ENUM('none', 'photoshop', 'blur', 'crop', 'composite', 'ai_generated') DEFAULT 'none',
    `tampering_confidence` DECIMAL(5,2) DEFAULT 0,
    `original_metadata` JSON COMMENT 'Original EXIF metadata',
    `current_metadata` JSON COMMENT 'Current EXIF metadata',
    
    -- Quality assessment
    `quality_score` DECIMAL(5,2) COMMENT 'Overall photo quality (0-100)',
    `resolution_score` DECIMAL(5,2) COMMENT 'Resolution quality (0-100)',
    `clarity_score` DECIMAL(5,2) COMMENT 'Image clarity (0-100)',
    `lighting_score` DECIMAL(5,2) COMMENT 'Lighting quality (0-100)',
    `composition_score` DECIMAL(5,2) COMMENT 'Composition quality (0-100)',
    
    -- Object detection
    `detected_objects` JSON COMMENT 'Objects detected in photo',
    `anomaly_detected` BOOLEAN DEFAULT FALSE,
    `anomaly_details` JSON COMMENT 'Details of detected anomalies',
    `missing_elements` JSON COMMENT 'Expected elements that are missing',
    
    -- Location and context
    `gps_coordinates` POINT COMMENT 'GPS coordinates if available',
    `location_match` BOOLEAN DEFAULT TRUE,
    `timestamp_match` BOOLEAN DEFAULT TRUE,
    `weather_conditions` VARCHAR(50),
    
    -- Audit trail
    `audited_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `audited_by` INT COMMENT 'ID of auditor (AI system or human)',
    `audit_status` ENUM('passed', 'failed', 'warning', 'manual_review_required') DEFAULT 'passed',
    `audit_notes` TEXT,
    
    -- Human review
    `human_review_required` BOOLEAN DEFAULT FALSE,
    `human_reviewer_id` INT,
    `human_review_notes` TEXT,
    `human_review_at` DATETIME,
    `final_status` ENUM('approved', 'rejected', 'needs_reshoot') DEFAULT 'approved',
    
    FOREIGN KEY (`inspection_id`) REFERENCES `inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`audited_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`human_reviewer_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_inspection` (`inspection_id`),
    INDEX `idx_audit_status` (`audit_status`),
    INDEX `idx_tampering_detected` (`tampering_detected`),
    INDEX `idx_quality_score` (`quality_score`),
    INDEX `idx_audited_at` (`audited_at`),
    INDEX `idx_photo_hash` (`photo_hash`),
    
    SPATIAL INDEX `idx_gps_coordinates` (`gps_coordinates`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 6. Quality Control Settings and Configuration
-- ============================================
CREATE TABLE IF NOT EXISTS `quality_control_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(100) UNIQUE NOT NULL,
    `setting_value` JSON NOT NULL,
    `setting_type` ENUM('percentage', 'number', 'boolean', 'string', 'array', 'object') NOT NULL,
    `category` VARCHAR(50) NOT NULL,
    `description` TEXT,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_category` (`category`),
    INDEX `idx_setting_key` (`setting_key`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 7. Quality Control Alerts and Notifications
-- ============================================
CREATE TABLE IF NOT EXISTS `quality_alerts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `alert_type` ENUM('performance_drop', 'high_error_rate', 'complaint_spike', 'missed_review', 'tampering_detected', 'quality_decline', 'training_needed') NOT NULL,
    `severity` ENUM('info', 'warning', 'error', 'critical') NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    
    -- Target information
    `target_type` ENUM('inspector', 'supervisor', 'admin', 'system') NOT NULL,
    `target_id` INT COMMENT 'ID of target user or system',
    `inspection_id` INT COMMENT 'Related inspection ID',
    
    -- Alert data
    `alert_data` JSON COMMENT 'Detailed alert data',
    `threshold_value` DECIMAL(10,2),
    `actual_value` DECIMAL(10,2),
    `comparison_operator` ENUM('greater_than', 'less_than', 'equals', 'percentage_change'),
    
    -- Status and resolution
    `status` ENUM('active', 'acknowledged', 'resolved', 'dismissed') DEFAULT 'active',
    `acknowledged_by` INT,
    `acknowledged_at` DATETIME,
    `resolved_by` INT,
    `resolved_at` DATETIME,
    `resolution_notes` TEXT,
    
    -- Notifications
    `notification_sent` BOOLEAN DEFAULT FALSE,
    `notification_channels` JSON COMMENT 'Channels used for notification',
    `notification_attempts` INT DEFAULT 0,
    
    -- Timestamps
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATETIME,
    
    FOREIGN KEY (`target_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`inspection_id`) REFERENCES `inspection_requests`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`acknowledged_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`resolved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_alert_type` (`alert_type`),
    INDEX `idx_severity` (`severity`),
    INDEX `idx_status` (`status`),
    INDEX `idx_target` (`target_type`, `target_id`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 8. Quality Control Reports and Analytics
-- ============================================
CREATE TABLE IF NOT EXISTS `quality_reports` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `report_type` ENUM('daily', 'weekly', 'monthly', 'quarterly', 'annual', 'custom') NOT NULL,
    `report_period_start` DATE NOT NULL,
    `report_period_end` DATE NOT NULL,
    `report_title` VARCHAR(255) NOT NULL,
    `report_description` TEXT,
    
    -- Report data
    `report_data` JSON NOT NULL COMMENT 'Complete report data',
    `summary_statistics` JSON COMMENT 'Key summary statistics',
    `trend_analysis` JSON COMMENT 'Trend analysis data',
    `recommendations` JSON COMMENT 'System recommendations',
    
    -- Performance metrics
    `overall_quality_score` DECIMAL(5,2),
    `total_inspections_reviewed` INT,
    `accuracy_rate` DECIMAL(5,2),
    `complaint_rate` DECIMAL(5,2),
    `average_response_time` INT COMMENT 'In minutes',
    
    -- Report generation
    `generated_by` INT NOT NULL,
    `generation_method` ENUM('automatic', 'manual', 'scheduled') DEFAULT 'automatic',
    `report_format` ENUM('json', 'pdf', 'excel', 'dashboard') DEFAULT 'json',
    
    -- Status and distribution
    `status` ENUM('generating', 'completed', 'failed', 'archived') DEFAULT 'generating',
    `file_path` VARCHAR(500),
    `file_size` INT,
    `download_count` INT DEFAULT 0,
    
    -- Sharing and access
    `shared_with` JSON COMMENT 'User IDs who have access',
    `is_public` BOOLEAN DEFAULT FALSE,
    `access_level` ENUM('restricted', 'team', 'department', 'all') DEFAULT 'restricted',
    
    -- Timestamps
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `completed_at` DATETIME,
    `last_accessed_at` DATETIME,
    `expires_at` DATETIME,
    
    FOREIGN KEY (`generated_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_report_type` (`report_type`),
    INDEX `idx_period` (`report_period_start`, `report_period_end`),
    INDEX `idx_status` (`status`),
    INDEX `idx_generated_by` (`generated_by`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Insert Default Quality Control Settings
-- ============================================
INSERT INTO `quality_control_settings` (`setting_key`, `setting_value`, `setting_type`, `category`, `description`) VALUES
('random_review_percentage', '15', 'percentage', 'review_settings', 'Percentage of inspections to randomly review'),
('minimum_accuracy_threshold', '85', 'number', 'quality_thresholds', 'Minimum acceptable accuracy score'),
('performance_review_frequency', 'monthly', 'string', 'review_settings', 'Frequency of performance reviews'),
('peer_review_participation', '100', 'percentage', 'peer_review', 'Required peer review participation rate'),
('complaint_response_sla_hours', '24', 'number', 'sla_settings', 'SLA for complaint response in hours'),
('photo_analysis_enabled', 'true', 'boolean', 'ai_settings', 'Enable AI photo analysis'),
('tampering_detection_threshold', '70', 'number', 'ai_settings', 'Confidence threshold for tampering detection'),
('training_recommendation_threshold', '75', 'number', 'performance_settings', 'Score threshold for training recommendations'),
('alert_notification_channels', '["email", "sms", "dashboard"]', 'array', 'notification_settings', 'Default alert notification channels'),
('report_retention_days', '365', 'number', 'report_settings', 'Days to retain quality reports'),
('max_peer_reviews_per_month', '5', 'number', 'peer_review', 'Maximum peer reviews per inspector per month'),
('quality_score_weight_accuracy', '40', 'percentage', 'scoring_weights', 'Weight for accuracy in quality score'),
('quality_score_weight_completeness', '30', 'percentage', 'scoring_weights', 'Weight for completeness in quality score'),
('quality_score_weight_professionalism', '30', 'percentage', 'scoring_weights', 'Weight for professionalism in quality score'),
('automatic_review_assignment', 'true', 'boolean', 'automation_settings', 'Enable automatic review assignment'),
('escalation_threshold_complaints', '3', 'number', 'escalation_settings', 'Number of complaints before escalation'),
('performance_grade_a_threshold', '90', 'number', 'grading_settings', 'Minimum score for Grade A'),
('performance_grade_b_threshold', '80', 'number', 'grading_settings', 'Minimum score for Grade B'),
('performance_grade_c_threshold', '70', 'number', 'grading_settings', 'Minimum score for Grade C'),
('performance_grade_d_threshold', '60', 'number', 'grading_settings', 'Minimum score for Grade D');

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

-- Inspector Performance Summary View
CREATE VIEW `inspector_performance_summary` AS
SELECT 
    u.id as inspector_id,
    u.full_name,
    u.email,
    u.phone,
    ip.performance_grade,
    ip.performance_score,
    ip.avg_accuracy,
    ip.avg_customer_rating,
    ip.total_inspections,
    ip.error_rate,
    ip.complaint_rate,
    ip.needs_training,
    ip.skill_level,
    ip.period_start,
    ip.period_end,
    ROW_NUMBER() OVER (PARTITION BY ip.period_start, ip.period_end ORDER BY ip.performance_score DESC) as rank_in_period
FROM users u
JOIN inspector_performance ip ON u.id = ip.inspector_id
WHERE u.role = 'inspector' 
AND u.status = 'active'
AND ip.period_type = 'monthly'
AND ip.period_end >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH);

-- Quality Control Dashboard View
CREATE VIEW `quality_control_dashboard` AS
SELECT 
    COUNT(DISTINCT ir.id) as total_reviews,
    COUNT(DISTINCT CASE WHEN ir.status = 'pending' THEN ir.id END) as pending_reviews,
    AVG(ir.overall_score) as avg_review_score,
    COUNT(DISTINCT cc.id) as total_complaints,
    COUNT(DISTINCT CASE WHEN cc.status = 'pending' THEN cc.id END) as pending_complaints,
    COUNT(DISTINCT CASE WHEN pa.tampering_detected = 1 THEN pa.id END) as tampering_cases,
    AVG(ip.avg_accuracy) as avg_inspector_accuracy,
    AVG(ip.avg_customer_rating) as avg_customer_satisfaction
FROM inspection_requests ir
LEFT JOIN inspection_reviews rev ON ir.id = rev.inspection_id
LEFT JOIN customer_complaints cc ON ir.id = cc.inspection_id
LEFT JOIN photo_audit pa ON ir.id = pa.inspection_id
LEFT JOIN inspector_performance ip ON ir.inspector_id = ip.inspector_id
WHERE ir.created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

-- ============================================
-- Create Triggers for Automated Quality Control
-- ============================================

-- Trigger to automatically create performance records when inspection is completed
DELIMITER $$
CREATE TRIGGER `trigger_inspector_performance_update`
AFTER UPDATE ON `inspection_requests`
FOR EACH ROW
BEGIN
    IF NEW.status = 'completed' AND OLD.status != 'completed' THEN
        -- Update inspector's performance metrics
        INSERT INTO inspector_performance 
        (inspector_id, period_start, period_end, total_inspections, completed_inspections, period_type)
        VALUES 
        (NEW.inspector_id, DATE(NEW.created_at), DATE(NEW.created_at), 1, 1, 'daily')
        ON DUPLICATE KEY UPDATE
        total_inspections = total_inspections + 1,
        completed_inspections = completed_inspections + 1,
        updated_at = CURRENT_TIMESTAMP;
        
        -- Schedule random review if applicable
        IF (SELECT setting_value FROM quality_control_settings WHERE setting_key = 'random_review_percentage') > 0 THEN
            IF RAND() * 100 < (SELECT JSON_EXTRACT(setting_value, '$') FROM quality_control_settings WHERE setting_key = 'random_review_percentage') THEN
                INSERT INTO inspection_reviews 
                (inspection_id, reviewer_id, reviewer_type, review_type, status)
                SELECT 
                    NEW.id,
                    u.id,
                    'supervisor',
                    'random',
                    'pending'
                FROM users u 
                WHERE u.role = 'supervisor' 
                AND u.status = 'active'
                ORDER BY RAND()
                LIMIT 1;
            END IF;
        END IF;
    END IF;
END$$
DELIMITER ;

-- ============================================
-- Add Quality Control Permissions to User Roles
-- ============================================

-- Update existing roles to include quality control permissions
UPDATE users SET 
    permissions = JSON_SET(
        COALESCE(permissions, '{}'),
        '$.quality_control', JSON_OBJECT(
            'view_dashboard', 'role IN ("supervisor", "admin")',
            'review_inspections', 'role IN ("supervisor", "admin")',
            'manage_complaints', 'role IN ("supervisor", "admin")',
            'view_reports', 'role IN ("supervisor", "admin")',
            'peer_review', 'role = "inspector"',
            'view_own_performance', 'role = "inspector"'
        )
    )
WHERE role IN ('inspector', 'supervisor', 'admin');

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

-- Log the migration
INSERT INTO system_logs (log_type, message, details, created_at) VALUES
('migration', 'Quality control system migration completed', JSON_OBJECT(
    'migration_version', '003',
    'tables_created', 8,
    'views_created', 2,
    'triggers_created', 1,
    'settings_inserted', 20,
    'migration_date', CURRENT_TIMESTAMP
), CURRENT_TIMESTAMP);
