-- AUTO TESTER - Vehicle Evaluation System Migration
-- Created: 2026-04-22
-- Description: Vehicle valuation and evaluation system integration with inspection reports

-- ============================================
-- 1. Vehicle Evaluation Master Tables
-- ============================================

-- Vehicle Evaluation Requests
CREATE TABLE IF NOT EXISTS `vehicle_evaluation_requests` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_request_id` INT NOT NULL COMMENT 'Link to comprehensive inspection',
    `vehicle_id` INT NOT NULL,
    `customer_id` INT NOT NULL,
    `evaluation_type` ENUM('market_value', 'trade_in', 'insurance', 'auction', 'quick_sale') NOT NULL,
    `evaluation_purpose` ENUM('sale', 'purchase', 'insurance', 'loan', 'legal', 'curiosity') NOT NULL,
    `requested_by` INT NOT NULL,
    `request_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `status` ENUM('pending', 'in_progress', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    `priority_level` ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    `evaluation_currency` VARCHAR(3) DEFAULT 'EGP',
    `target_date` DATETIME COMMENT 'When evaluation is needed by',
    `special_instructions` TEXT,
    `customer_expected_price` DECIMAL(12,2),
    `customer_notes` TEXT,
    `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 (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`customer_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`requested_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    
    INDEX `idx_inspection_request_id` (`inspection_request_id`),
    INDEX `idx_vehicle_id` (`vehicle_id`),
    INDEX `idx_customer_id` (`customer_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_request_date` (`request_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vehicle Evaluation Results
CREATE TABLE IF NOT EXISTS `vehicle_evaluation_results` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `evaluation_request_id` INT NOT NULL,
    `evaluation_provider` VARCHAR(100) NOT NULL COMMENT 'Source of evaluation (AUTO TESTER, external API, etc.)',
    `evaluation_method` ENUM('automated', 'manual', 'hybrid', 'market_comparison', 'algorithmic') NOT NULL,
    `evaluation_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `base_value` DECIMAL(12,2) NOT NULL COMMENT 'Base vehicle value',
    `condition_adjustment` DECIMAL(12,2) DEFAULT 0 COMMENT 'Adjustment based on condition',
    `mileage_adjustment` DECIMAL(12,2) DEFAULT 0 COMMENT 'Adjustment based on mileage',
    `age_adjustment` DECIMAL(12,2) DEFAULT 0 COMMENT 'Adjustment based on vehicle age',
    `market_adjustment` DECIMAL(12,2) DEFAULT 0 COMMENT 'Market condition adjustment',
    `location_adjustment` DECIMAL(12,2) DEFAULT 0 COMMENT 'Geographic adjustment',
    `final_value` DECIMAL(12,2) NOT NULL COMMENT 'Final evaluated value',
    `value_range_min` DECIMAL(12,2),
    `value_range_max` DECIMAL(12,2),
    `confidence_score` DECIMAL(5,2) DEFAULT 0 COMMENT 'Confidence in evaluation (0-100)',
    `evaluation_notes_ar` TEXT,
    `evaluation_notes_en` TEXT,
    `key_factors` JSON COMMENT 'Factors affecting valuation',
    `comparable_vehicles` JSON COMMENT 'Similar vehicles used for comparison',
    `market_trends` JSON COMMENT 'Market trend data',
    `evaluation_data` JSON COMMENT 'Complete evaluation data',
    `is_primary_evaluation` BOOLEAN DEFAULT TRUE COMMENT 'Main evaluation for this request',
    `evaluated_by` INT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`evaluation_request_id`) REFERENCES `vehicle_evaluation_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`evaluated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_evaluation_request_id` (`evaluation_request_id`),
    INDEX `idx_evaluation_provider` (`evaluation_provider`),
    INDEX `idx_evaluation_date` (`evaluation_date`),
    INDEX `idx_is_primary_evaluation` (`is_primary_evaluation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vehicle Condition Factors (from inspection results)
CREATE TABLE IF NOT EXISTS `vehicle_condition_factors` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `evaluation_request_id` INT NOT NULL,
    `factor_category` ENUM('exterior', 'interior', 'mechanical', 'electrical', 'safety', 'documentation') NOT NULL,
    `factor_name_ar` VARCHAR(200) NOT NULL,
    `factor_name_en` VARCHAR(200) NOT NULL,
    `factor_impact` ENUM('positive', 'negative', 'neutral') NOT NULL,
    `impact_weight` DECIMAL(5,2) DEFAULT 1.0 COMMENT 'Weight of this factor in overall evaluation',
    `condition_score` DECIMAL(5,2) COMMENT 'Score for this factor (0-100)',
    `value_impact` DECIMAL(12,2) DEFAULT 0 COMMENT 'Monetary impact on value',
    `description_ar` TEXT,
    `description_en` TEXT,
    `evidence_data` JSON COMMENT 'Evidence from inspection supporting this factor',
    `is_critical` BOOLEAN DEFAULT FALSE COMMENT 'Critical factor affecting value significantly',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`evaluation_request_id`) REFERENCES `vehicle_evaluation_requests`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_evaluation_request_id` (`evaluation_request_id`),
    INDEX `idx_factor_category` (`factor_category`),
    INDEX `idx_factor_impact` (`factor_impact`),
    INDEX `idx_is_critical` (`is_critical`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 2. Market Data and Comparables
-- ============================================

-- Vehicle Market Data
CREATE TABLE IF NOT EXISTS `vehicle_market_data` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `make` VARCHAR(100) NOT NULL,
    `model` VARCHAR(100) NOT NULL,
    `year` INT NOT NULL,
    `trim_level` VARCHAR(100),
    `body_type` VARCHAR(50),
    `fuel_type` VARCHAR(50),
    `transmission` VARCHAR(50),
    `engine_size` DECIMAL(4,1),
    `region` VARCHAR(50) DEFAULT 'Egypt',
    `city` VARCHAR(50),
    `average_price` DECIMAL(12,2) NOT NULL,
    `price_range_min` DECIMAL(12,2),
    `price_range_max` DECIMAL(12,2),
    `sample_size` INT DEFAULT 1 COMMENT 'Number of vehicles in sample',
    `data_source` VARCHAR(100) NOT NULL,
    `data_date` DATE NOT NULL,
    `market_condition` ENUM('excellent', 'good', 'fair', 'poor') DEFAULT 'good',
    `demand_level` ENUM('high', 'medium', 'low') DEFAULT 'medium',
    `seasonal_adjustment` DECIMAL(5,2) DEFAULT 0,
    `confidence_level` DECIMAL(5,2) DEFAULT 0,
    `notes` TEXT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY `unique_vehicle_market_data` (`make`, `model`, `year`, `trim_level`, `region`, `data_date`),
    INDEX `idx_make_model_year` (`make`, `model`, `year`),
    INDEX `idx_region` (`region`),
    INDEX `idx_data_date` (`data_date`),
    INDEX `idx_average_price` (`average_price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Comparable Vehicles
CREATE TABLE IF NOT EXISTS `comparable_vehicles` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `evaluation_request_id` INT NOT NULL,
    `comparable_id` VARCHAR(100) NOT NULL COMMENT 'ID from external source',
    `make` VARCHAR(100) NOT NULL,
    `model` VARCHAR(100) NOT NULL,
    `year` INT NOT NULL,
    `mileage` INT,
    `condition_rating` ENUM('excellent', 'good', 'fair', 'poor'),
    `price` DECIMAL(12,2) NOT NULL,
    `location` VARCHAR(200),
    `listing_date` DATE,
    `source` VARCHAR(100) NOT NULL,
    `similarity_score` DECIMAL(5,2) COMMENT 'How similar to target vehicle (0-100)',
    `price_difference` DECIMAL(12,2) COMMENT 'Difference from target valuation',
    `listing_url` VARCHAR(500),
    `vehicle_images` JSON,
    `additional_features` JSON,
    `notes` TEXT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`evaluation_request_id`) REFERENCES `vehicle_evaluation_requests`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_evaluation_request_id` (`evaluation_request_id`),
    INDEX `idx_make_model_year` (`make`, `model`, `year`),
    INDEX `idx_similarity_score` (`similarity_score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. Evaluation History and Tracking
-- ============================================

-- Vehicle Evaluation History
CREATE TABLE IF NOT EXISTS `vehicle_evaluation_history` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `vehicle_id` INT NOT NULL,
    `evaluation_date` DATE NOT NULL,
    `evaluation_value` DECIMAL(12,2) NOT NULL,
    `evaluation_type` VARCHAR(50) NOT NULL,
    `mileage_at_evaluation` INT,
    `condition_score` DECIMAL(5,2),
    `market_conditions` JSON,
    `evaluation_notes` TEXT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_vehicle_id` (`vehicle_id`),
    INDEX `idx_evaluation_date` (`evaluation_date`),
    INDEX `idx_evaluation_value` (`evaluation_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Evaluation Quality Metrics
CREATE TABLE IF NOT EXISTS `evaluation_quality_metrics` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `evaluation_request_id` INT NOT NULL,
    `accuracy_score` DECIMAL(5,2) COMMENT 'Accuracy of evaluation (if verified)',
    `customer_satisfaction` DECIMAL(5,2) COMMENT 'Customer satisfaction with evaluation',
    `market_alignment` DECIMAL(5,2) COMMENT 'How well aligned with actual market',
    `data_completeness` DECIMAL(5,2) COMMENT 'Completeness of evaluation data',
    `processing_time_minutes` INT,
    `data_sources_count` INT DEFAULT 1,
    `comparables_count` INT DEFAULT 0,
    `confidence_score` DECIMAL(5,2),
    `quality_notes` TEXT,
    `review_status` ENUM('pending', 'approved', 'rejected', 'needs_improvement') DEFAULT 'pending',
    `reviewed_by` INT,
    `review_date` DATETIME,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`evaluation_request_id`) REFERENCES `vehicle_evaluation_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_evaluation_request_id` (`evaluation_request_id`),
    INDEX `idx_accuracy_score` (`accuracy_score`),
    INDEX `idx_review_status` (`review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. Integration with Inspection Reports
-- ============================================

-- Report Valuation Section
CREATE TABLE IF NOT EXISTS `report_valuation_sections` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_report_id` INT NOT NULL,
    `evaluation_request_id` INT NOT NULL,
    `section_title_ar` VARCHAR(200) NOT NULL,
    `section_title_en` VARCHAR(200) NOT NULL,
    `section_order` INT DEFAULT 1,
    `is_visible_to_customer` BOOLEAN DEFAULT TRUE,
    `display_format` ENUM('summary', 'detailed', 'chart', 'comparison') DEFAULT 'summary',
    `section_data` JSON COMMENT 'Data to display in this section',
    `chart_config` JSON COMMENT 'Chart configuration if display_format is chart',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`evaluation_request_id`) REFERENCES `vehicle_evaluation_requests`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_inspection_report_id` (`inspection_report_id`),
    INDEX `idx_evaluation_request_id` (`evaluation_request_id`),
    INDEX `idx_section_order` (`section_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Valuation Summary for Reports
CREATE TABLE IF NOT EXISTS `valuation_report_summaries` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `evaluation_request_id` INT NOT NULL,
    `inspection_request_id` INT NOT NULL,
    `summary_type` ENUM('executive', 'detailed', 'customer', 'internal') NOT NULL,
    `currency` VARCHAR(3) DEFAULT 'EGP',
    `primary_value` DECIMAL(12,2) NOT NULL,
    `value_range_min` DECIMAL(12,2),
    `value_range_max` DECIMAL(12,2),
    `condition_impact` DECIMAL(12,2) DEFAULT 0,
    `market_trend` ENUM('increasing', 'stable', 'decreasing') DEFAULT 'stable',
    `confidence_level` ENUM('high', 'medium', 'low') DEFAULT 'medium',
    `key_highlights_ar` TEXT,
    `key_highlights_en` TEXT,
    `value_factors_ar` TEXT,
    `value_factors_en` TEXT,
    `recommendations_ar` TEXT,
    `recommendations_en` TEXT,
    `next_steps_ar` TEXT,
    `next_steps_en` TEXT,
    `summary_data` JSON COMMENT 'Complete summary data',
    `generated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`evaluation_request_id`) REFERENCES `vehicle_evaluation_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`inspection_request_id`) REFERENCES `comprehensive_inspection_requests`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_evaluation_request_id` (`evaluation_request_id`),
    INDEX `idx_inspection_request_id` (`inspection_request_id`),
    INDEX `idx_summary_type` (`summary_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5. Default Data and Configuration
-- ============================================

-- Insert default market data for popular vehicles in Egypt
INSERT INTO `vehicle_market_data` 
(`make`, `model`, `year`, `trim_level`, `body_type`, `fuel_type`, `transmission`, `engine_size`, 
 `region`, `average_price`, `price_range_min`, `price_range_max`, `sample_size`, 
 `data_source`, `data_date`, `market_condition`, `demand_level`, `confidence_level`) VALUES

-- Toyota vehicles
('Toyota', 'Camry', 2022, 'LE', 'Sedan', 'Gasoline', 'Automatic', 2.5, 
 'Egypt', 850000, 750000, 950000, 25, 
 'AUTO TESTER Database', '2026-04-22', 'good', 'medium', 85),

('Toyota', 'Corolla', 2022, 'L', 'Sedan', 'Gasoline', 'Automatic', 1.8, 
 'Egypt', 650000, 550000, 750000, 30, 
 'AUTO TESTER Database', '2026-04-22', 'good', 'high', 90),

('Toyota', 'Yaris', 2022, 'Sedan', 'Sedan', 'Gasoline', 'Automatic', 1.5, 
 'Egypt', 450000, 380000, 520000, 20, 
 'AUTO TESTER Database', '2026-04-22', 'good', 'medium', 80),

-- Hyundai vehicles
('Hyundai', 'Elantra', 2022, 'GLS', 'Sedan', 'Gasoline', 'Automatic', 1.6, 
 'Egypt', 550000, 480000, 620000, 18, 
 'AUTO TESTER Database', '2026-04-22', 'good', 'medium', 85),

('Hyundai', 'Accent', 2022, 'GL', 'Sedan', 'Gasoline', 'Automatic', 1.4, 
 'Egypt', 380000, 320000, 440000, 22, 
 'AUTO TESTER Database', '2026-04-22', 'fair', 'medium', 75),

-- Nissan vehicles
('Nissan', 'Sentra', 2022, 'S', 'Sedan', 'Gasoline', 'Automatic', 1.6, 
 'Egypt', 520000, 450000, 590000, 15, 
 'AUTO TESTER Database', '2026-04-22', 'good', 'medium', 80),

('Nissan', 'Sunny', 2022, 'S', 'Sedan', 'Gasoline', 'Automatic', 1.6, 
 'Egypt', 420000, 360000, 480000, 25, 
 'AUTO TESTER Database', '2026-04-22', 'good', 'high', 85),

-- Kia vehicles
('Kia', 'Rio', 2022, 'LX', 'Sedan', 'Gasoline', 'Automatic', 1.4, 
 'Egypt', 400000, 340000, 460000, 20, 
 'AUTO TESTER Database', '2026-04-22', 'fair', 'medium', 75),

('Kia', 'Cerato', 2022, 'L', 'Sedan', 'Gasoline', 'Automatic', 1.6, 
 'Egypt', 480000, 410000, 550000, 18, 
 'AUTO TESTER Database', '2026-04-22', 'good', 'medium', 80),

-- Chevrolet vehicles
('Chevrolet', 'Optra', 2022, 'LS', 'Sedan', 'Gasoline', 'Automatic', 1.6, 
 'Egypt', 350000, 290000, 410000, 12, 
 'AUTO TESTER Database', '2026-04-22', 'fair', 'low', 70);

-- ============================================
-- 6. Views for Evaluation System
-- ============================================

-- Vehicle Evaluation Overview View
CREATE VIEW `vehicle_evaluation_overview` AS
SELECT 
    ver.id,
    ver.inspection_request_id,
    ver.vehicle_id,
    v.make,
    v.model,
    v.year,
    v.plate_number,
    u.full_name as customer_name,
    ver.evaluation_type,
    ver.evaluation_purpose,
    ver.status,
    ver.request_date,
    ver.target_date,
    ver.customer_expected_price,
    vr.final_value,
    vr.value_range_min,
    vr.value_range_max,
    vr.confidence_score,
    vr.evaluation_provider,
    vr.evaluation_method,
    TIMESTAMPDIFF(DAY, ver.request_date, COALESCE(vr.evaluation_date, CURRENT_DATE)) as processing_days
FROM vehicle_evaluation_requests ver
JOIN vehicles v ON ver.vehicle_id = v.id
JOIN users u ON ver.customer_id = u.id
LEFT JOIN vehicle_evaluation_results vr ON ver.id = vr.evaluation_request_id AND vr.is_primary_evaluation = TRUE;

-- Market Data Comparison View
CREATE VIEW `vehicle_market_comparison` AS
SELECT 
    vmd.make,
    vmd.model,
    vmd.year,
    vmd.trim_level,
    vmd.average_price,
    vmd.price_range_min,
    vmd.price_range_max,
    vmd.sample_size,
    vmd.market_condition,
    vmd.demand_level,
    vmd.data_date,
    (vmd.average_price - LAG(vmd.average_price) OVER (PARTITION BY vmd.make, vmd.model ORDER BY vmd.data_date)) as price_change,
    ((vmd.average_price - LAG(vmd.average_price) OVER (PARTITION BY vmd.make, vmd.model ORDER BY vmd.data_date)) / LAG(vmd.average_price) OVER (PARTITION BY vmd.make, vmd.model ORDER BY vmd.data_date) * 100) as price_change_percentage
FROM vehicle_market_data vmd
ORDER BY vmd.make, vmd.model, vmd.data_date DESC;

-- Evaluation Quality Metrics View
CREATE VIEW `evaluation_quality_summary` AS
SELECT 
    eqm.evaluation_request_id,
    eqm.accuracy_score,
    eqm.customer_satisfaction,
    eqm.market_alignment,
    eqm.data_completeness,
    eqm.processing_time_minutes,
    eqm.confidence_score,
    eqm.review_status,
    ver.evaluation_type,
    ver.evaluation_purpose,
    vr.final_value,
    vr.evaluation_provider,
    CASE 
        WHEN eqm.accuracy_score >= 90 AND eqm.confidence_score >= 80 THEN 'excellent'
        WHEN eqm.accuracy_score >= 80 AND eqm.confidence_score >= 70 THEN 'good'
        WHEN eqm.accuracy_score >= 70 AND eqm.confidence_score >= 60 THEN 'fair'
        ELSE 'poor'
    END as overall_quality_rating
FROM evaluation_quality_metrics eqm
JOIN vehicle_evaluation_requests ver ON eqm.evaluation_request_id = ver.id
JOIN vehicle_evaluation_results vr ON ver.id = vr.evaluation_request_id AND vr.is_primary_evaluation = TRUE;

-- ============================================
-- 7. Triggers for Automated Processes
-- ============================================

DELIMITER $$

-- Create evaluation request when inspection is completed
CREATE TRIGGER `create_evaluation_on_inspection_complete`
AFTER UPDATE ON `comprehensive_inspection_requests`
FOR EACH ROW
BEGIN
    IF NEW.status = 'completed' AND OLD.status != 'completed' THEN
        -- Create automatic evaluation request
        INSERT INTO vehicle_evaluation_requests 
        (inspection_request_id, vehicle_id, customer_id, evaluation_type, 
         evaluation_purpose, requested_by, status, priority_level)
        VALUES (
            NEW.id,
            NEW.vehicle_id,
            NEW.customer_id,
            'market_value',
            'sale',
            NEW.inspector_id,
            'pending',
            'medium'
        );
    END IF;
END$$

-- Update evaluation history when evaluation is completed
CREATE TRIGGER `update_evaluation_history`
AFTER INSERT ON `vehicle_evaluation_results`
FOR EACH ROW
BEGIN
    -- Get vehicle information
    DECLARE vehicle_make VARCHAR(100);
    DECLARE vehicle_model VARCHAR(100);
    DECLARE vehicle_year INT;
    DECLARE vehicle_mileage INT;
    
    SELECT v.make, v.model, v.year, v.mileage 
    INTO vehicle_make, vehicle_model, vehicle_year, vehicle_mileage
    FROM vehicles v
    JOIN vehicle_evaluation_requests ver ON v.id = ver.vehicle_id
    WHERE ver.id = NEW.evaluation_request_id;
    
    -- Add to evaluation history
    INSERT INTO vehicle_evaluation_history 
    (vehicle_id, evaluation_date, evaluation_value, evaluation_type, 
     mileage_at_evaluation, condition_score, market_conditions, evaluation_notes)
    VALUES (
        (SELECT vehicle_id FROM vehicle_evaluation_requests WHERE id = NEW.evaluation_request_id),
        NEW.evaluation_date,
        NEW.final_value,
        (SELECT evaluation_type FROM vehicle_evaluation_requests WHERE id = NEW.evaluation_request_id),
        vehicle_mileage,
        (SELECT AVG(section_score) FROM comprehensive_section_results 
         WHERE inspection_request_id = (SELECT inspection_request_id FROM vehicle_evaluation_requests WHERE id = NEW.evaluation_request_id)),
        NEW.evaluation_data,
        CONCAT('Evaluation by ', NEW.evaluation_provider, ' with confidence score: ', NEW.confidence_score)
    );
END$$

-- Create quality metrics entry when evaluation is completed
CREATE TRIGGER `create_quality_metrics_on_evaluation`
AFTER INSERT ON `vehicle_evaluation_results`
FOR EACH ROW
BEGIN
    INSERT INTO evaluation_quality_metrics 
    (evaluation_request_id, data_completeness, comparables_count, 
     confidence_score, processing_time_minutes, quality_notes)
    VALUES (
        NEW.evaluation_request_id,
        CASE 
            WHEN NEW.evaluation_data IS NOT NULL AND JSON_LENGTH(NEW.evaluation_data) > 0 THEN 90
            ELSE 70
        END,
        (SELECT COUNT(*) FROM comparable_vehicles WHERE evaluation_request_id = NEW.evaluation_request_id),
        NEW.confidence_score,
        TIMESTAMPDIFF(MINUTE, 
            (SELECT request_date FROM vehicle_evaluation_requests WHERE id = NEW.evaluation_request_id),
            NEW.evaluation_date
        ),
        CONCAT('Automated quality metrics for evaluation by ', NEW.evaluation_provider)
    );
END$$

DELIMITER ;

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

-- Log the migration
INSERT INTO system_logs (log_type, message, details, created_at) VALUES
('migration', 'Vehicle evaluation system migration completed', JSON_OBJECT(
    'migration_version', '009',
    'tables_created', 9,
    'views_created', 3,
    'triggers_created', 3,
    'market_data_inserted', 10,
    'migration_date', CURRENT_TIMESTAMP
), CURRENT_TIMESTAMP);
