-- AUTO TESTER - Vehicle Rating & Pricing System Database Setup
-- Created: 2024-01-15
-- Purpose: Setup tables for vehicle rating and pricing system

-- ===== TABLE CREATION =====

-- Vehicle Ratings Table
CREATE TABLE IF NOT EXISTS `vehicle_ratings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `inspection_id` INT NOT NULL COMMENT 'Reference to inspection request',
    `overall_score` DECIMAL(5,2) NOT NULL COMMENT 'Overall percentage score (0-100)',
    `star_rating` DECIMAL(2,1) NOT NULL COMMENT 'Star rating (1-5)',
    `condition_level` ENUM('excellent', 'very_good', 'good', 'fair', 'poor') NOT NULL,
    `condition_text_ar` VARCHAR(50) NOT NULL COMMENT 'Arabic condition text',
    `condition_color` VARCHAR(7) NOT NULL COMMENT 'Color code for condition',
    `good_count` INT DEFAULT 0 COMMENT 'Number of good items',
    `warning_count` INT DEFAULT 0 COMMENT 'Number of warning items',
    `fail_count` INT DEFAULT 0 COMMENT 'Number of failed items',
    `total_items` INT DEFAULT 0 COMMENT 'Total checklist items',
    `recommendation` TEXT COMMENT 'Inspector recommendations',
    `rated_by` INT NOT NULL COMMENT 'Inspector ID who rated',
    `rated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`inspection_id`) REFERENCES `inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`rated_by`) REFERENCES `users`(`id`),
    INDEX `idx_inspection` (`inspection_id`),
    INDEX `idx_condition` (`condition_level`),
    INDEX `idx_rated_by` (`rated_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vehicle Pricing Base Table
CREATE TABLE IF NOT EXISTS `vehicle_pricing` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `make` VARCHAR(50) NOT NULL COMMENT 'Vehicle make (Toyota, Honda, etc.)',
    `model` VARCHAR(50) NOT NULL COMMENT 'Vehicle model (Camry, Corolla, etc.)',
    `year` INT NOT NULL COMMENT 'Vehicle year',
    `base_price` DECIMAL(12,2) NOT NULL COMMENT 'Base price for excellent condition',
    `price_per_km` DECIMAL(5,2) DEFAULT 0 COMMENT 'Deduction per kilometer',
    `depreciation_rate` DECIMAL(4,2) DEFAULT 10 COMMENT 'Annual depreciation rate (%)',
    `min_price` DECIMAL(12,2) DEFAULT 0 COMMENT 'Minimum market price',
    `max_price` DECIMAL(12,2) DEFAULT 0 COMMENT 'Maximum market price',
    `market_trend` ENUM('rising', 'stable', 'declining') DEFAULT 'stable',
    `popularity_score` INT DEFAULT 50 COMMENT 'Market popularity (1-100)',
    `last_updated` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_vehicle` (`make`, `model`, `year`),
    UNIQUE KEY `unique_vehicle` (`make`, `model`, `year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Price Adjustments by Condition
CREATE TABLE IF NOT EXISTS `price_adjustments` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `condition_level` ENUM('excellent', 'very_good', 'good', 'fair', 'poor') NOT NULL,
    `adjustment_percentage` DECIMAL(5,2) NOT NULL COMMENT 'Adjustment percentage (-20 to +20)',
    `description_ar` VARCHAR(255) COMMENT 'Arabic description',
    `description_en` VARCHAR(255) COMMENT 'English description',
    `color_code` VARCHAR(7) COMMENT 'Color for UI display',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_condition` (`condition_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vehicle Pricing History
CREATE TABLE IF NOT EXISTS `vehicle_pricing_history` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `vehicle_pricing_id` INT NOT NULL,
    `old_base_price` DECIMAL(12,2) NOT NULL,
    `new_base_price` DECIMAL(12,2) NOT NULL,
    `price_change_percentage` DECIMAL(5,2) NOT NULL,
    `change_reason` VARCHAR(255) COMMENT 'Reason for price change',
    `changed_by` INT NOT NULL COMMENT 'Admin ID who made the change',
    `changed_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`vehicle_pricing_id`) REFERENCES `vehicle_pricing`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`changed_by`) REFERENCES `users`(`id`),
    INDEX `idx_pricing_id` (`vehicle_pricing_id`),
    INDEX `idx_changed_at` (`changed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Market Price Comparisons
CREATE TABLE IF NOT EXISTS `market_price_comparisons` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `make` VARCHAR(50) NOT NULL,
    `model` VARCHAR(50) NOT NULL,
    `year` INT NOT NULL,
    `source` VARCHAR(50) NOT NULL COMMENT 'Source of price data (website, dealer, etc.)',
    `average_price` DECIMAL(12,2) NOT NULL,
    `min_price` DECIMAL(12,2) NOT NULL,
    `max_price` DECIMAL(12,2) NOT NULL,
    `sample_size` INT DEFAULT 1 COMMENT 'Number of vehicles analyzed',
    `data_date` DATE NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_vehicle` (`make`, `model`, `year`),
    INDEX `idx_source` (`source`),
    INDEX `idx_data_date` (`data_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== INITIAL DATA INSERTION =====

-- Price Adjustments Data
INSERT INTO `price_adjustments` (`condition_level`, `adjustment_percentage`, `description_ar`, `description_en`, `color_code`) VALUES
('excellent', 15, 'Increase 15% above base price', 'Increase 15% above base price', '#00ff88'),
('very_good', 5, 'Increase 5% above base price', 'Increase 5% above base price', '#00c8ff'),
('good', 0, 'Base price', 'Base price', '#ffaa00'),
('fair', -15, 'Discount 15% from base price', 'Discount 15% from base price', '#ff8800'),
('poor', -30, 'Discount 30% from base price', 'Discount 30% from base price', '#ff4444');

-- Vehicle Pricing Data - Toyota
INSERT INTO `vehicle_pricing` (`make`, `model`, `year`, `base_price`, `price_per_km`, `depreciation_rate`, `min_price`, `max_price`, `popularity_score`) VALUES
('Toyota', 'Camry', 2024, 850000, 0.50, 8, 750000, 950000, 85),
('Toyota', 'Camry', 2023, 780000, 0.50, 8, 680000, 880000, 85),
('Toyota', 'Camry', 2022, 720000, 0.50, 8, 620000, 820000, 85),
('Toyota', 'Corolla', 2024, 650000, 0.40, 7, 550000, 750000, 90),
('Toyota', 'Corolla', 2023, 600000, 0.40, 7, 500000, 700000, 90),
('Toyota', 'Corolla', 2022, 550000, 0.40, 7, 450000, 650000, 90),
('Toyota', 'Yaris', 2024, 420000, 0.30, 10, 350000, 490000, 75),
('Toyota', 'Yaris', 2023, 380000, 0.30, 10, 310000, 450000, 75),
('Toyota', 'Yaris', 2022, 340000, 0.30, 10, 270000, 410000, 75);

-- Vehicle Pricing Data - Hyundai
INSERT INTO `vehicle_pricing` (`make`, `model`, `year`, `base_price`, `price_per_km`, `depreciation_rate`, `min_price`, `max_price`, `popularity_score`) VALUES
('Hyundai', 'Elantra', 2024, 580000, 0.35, 9, 500000, 660000, 80),
('Hyundai', 'Elantra', 2023, 530000, 0.35, 9, 450000, 610000, 80),
('Hyundai', 'Elantra', 2022, 480000, 0.35, 9, 400000, 560000, 80),
('Hyundai', 'Tucson', 2024, 750000, 0.45, 8, 650000, 850000, 82),
('Hyundai', 'Tucson', 2023, 690000, 0.45, 8, 590000, 790000, 82),
('Hyundai', 'Tucson', 2022, 630000, 0.45, 8, 530000, 730000, 82),
('Hyundai', 'Accent', 2024, 480000, 0.30, 11, 400000, 560000, 70),
('Hyundai', 'Accent', 2023, 430000, 0.30, 11, 350000, 510000, 70);

-- Vehicle Pricing Data - Nissan
INSERT INTO `vehicle_pricing` (`make`, `model`, `year`, `base_price`, `price_per_km`, `depreciation_rate`, `min_price`, `max_price`, `popularity_score`) VALUES
('Nissan', 'Sentra', 2024, 530000, 0.35, 10, 450000, 610000, 75),
('Nissan', 'Sentra', 2023, 480000, 0.35, 10, 400000, 560000, 75),
('Nissan', 'Sentra', 2022, 430000, 0.35, 10, 350000, 510000, 75),
('Nissan', 'Sunny', 2024, 420000, 0.30, 12, 350000, 490000, 78),
('Nissan', 'Sunny', 2023, 370000, 0.30, 12, 300000, 440000, 78),
('Nissan', 'Sunny', 2022, 320000, 0.30, 12, 250000, 390000, 78),
('Nissan', 'Patrol', 2024, 1800000, 1.00, 6, 1600000, 2000000, 60),
('Nissan', 'Patrol', 2023, 1700000, 1.00, 6, 1500000, 1900000, 60);

-- Vehicle Pricing Data - Kia
INSERT INTO `vehicle_pricing` (`make`, `model`, `year`, `base_price`, `price_per_km`, `depreciation_rate`, `min_price`, `max_price`, `popularity_score`) VALUES
('Kia', 'Sportage', 2024, 680000, 0.40, 9, 580000, 780000, 77),
('Kia', 'Sportage', 2023, 620000, 0.40, 9, 520000, 720000, 77),
('Kia', 'Sportage', 2022, 560000, 0.40, 9, 460000, 660000, 77),
('Kia', 'Cerato', 2024, 530000, 0.35, 10, 450000, 610000, 72),
('Kia', 'Cerato', 2023, 480000, 0.35, 10, 400000, 560000, 72),
('Kia', 'Cerato', 2022, 430000, 0.35, 10, 350000, 510000, 72),
('Kia', 'Picanto', 2024, 380000, 0.25, 13, 320000, 440000, 68),
('Kia', 'Picanto', 2023, 340000, 0.25, 13, 280000, 400000, 68);

-- Vehicle Pricing Data - Chevrolet
INSERT INTO `vehicle_pricing` (`make`, `model`, `year`, `base_price`, `price_per_km`, `depreciation_rate`, `min_price`, `max_price`, `popularity_score`) VALUES
('Chevrolet', 'Express', 2024, 650000, 0.45, 11, 550000, 750000, 65),
('Chevrolet', 'Express', 2023, 580000, 0.45, 11, 480000, 680000, 65),
('Chevrolet', 'Captiva', 2024, 750000, 0.50, 10, 650000, 850000, 60),
('Chevrolet', 'Captiva', 2023, 680000, 0.50, 10, 580000, 780000, 60),
('Chevrolet', 'Aveo', 2024, 320000, 0.25, 14, 270000, 370000, 55),
('Chevrolet', 'Aveo', 2023, 280000, 0.25, 14, 230000, 330000, 55);

-- Luxury Vehicle Pricing Data - Mercedes
INSERT INTO `vehicle_pricing` (`make`, `model`, `year`, `base_price`, `price_per_km`, `depreciation_rate`, `min_price`, `max_price`, `popularity_score`) VALUES
('Mercedes', 'E-Class', 2024, 2800000, 1.50, 7, 2500000, 3100000, 70),
('Mercedes', 'E-Class', 2023, 2600000, 1.50, 7, 2300000, 2900000, 70),
('Mercedes', 'C-Class', 2024, 2200000, 1.20, 8, 1900000, 2500000, 75),
('Mercedes', 'C-Class', 2023, 2000000, 1.20, 8, 1700000, 2300000, 75),
('Mercedes', 'A-Class', 2024, 1800000, 1.00, 9, 1500000, 2100000, 65),
('Mercedes', 'A-Class', 2023, 1600000, 1.00, 9, 1300000, 1900000, 65);

-- Luxury Vehicle Pricing Data - BMW
INSERT INTO `vehicle_pricing` (`make`, `model`, `year`, `base_price`, `price_per_km`, `depreciation_rate`, `min_price`, `max_price`, `popularity_score`) VALUES
('BMW', 'X5', 2024, 2500000, 1.40, 7, 2200000, 2800000, 68),
('BMW', 'X5', 2023, 2300000, 1.40, 7, 2000000, 2600000, 68),
('BMW', 'X3', 2024, 2000000, 1.20, 8, 1700000, 2300000, 72),
('BMW', 'X3', 2023, 1800000, 1.20, 8, 1500000, 2100000, 72),
('BMW', '3-Series', 2024, 1900000, 1.10, 8, 1600000, 2200000, 78),
('BMW', '3-Series', 2023, 1700000, 1.10, 8, 1400000, 2000000, 78);

-- ===== STORED PROCEDURES =====

-- Procedure to calculate vehicle rating
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS `CalculateVehicleRating`(
    IN p_inspection_id INT,
    IN p_inspector_id INT
)
BEGIN
    DECLARE v_total_items INT DEFAULT 0;
    DECLARE v_good_count INT DEFAULT 0;
    DECLARE v_warning_count INT DEFAULT 0;
    DECLARE v_fail_count INT DEFAULT 0;
    DECLARE v_total_score DECIMAL(10,2) DEFAULT 0;
    DECLARE v_overall_percentage DECIMAL(5,2) DEFAULT 0;
    DECLARE v_star_rating DECIMAL(2,1) DEFAULT 0;
    DECLARE v_condition_level VARCHAR(20) DEFAULT 'good';
    DECLARE v_condition_text_ar VARCHAR(50) DEFAULT 'good';
    DECLARE v_condition_color VARCHAR(7) DEFAULT '#ffaa00';
    
    -- Get checklist results
    SELECT 
        COUNT(*) as total_items,
        COUNT(CASE WHEN status = 'good' THEN 1 END) as good_count,
        COUNT(CASE WHEN status = 'warning' THEN 1 END) as warning_count,
        COUNT(CASE WHEN status = 'fail' THEN 1 END) as fail_count
    INTO v_total_items, v_good_count, v_warning_count, v_fail_count
    FROM inspection_checklist 
    WHERE inspection_id = p_inspection_id;
    
    -- Calculate percentage
    IF v_total_items > 0 THEN
        SET v_total_score = (v_good_count * 100) + (v_warning_count * 60) + (v_fail_count * 20);
        SET v_overall_percentage = ROUND(v_total_score / v_total_items, 2);
    ELSE
        SET v_overall_percentage = 0;
    END IF;
    
    -- Determine condition level
    IF v_overall_percentage >= 90 THEN
        SET v_condition_level = 'excellent';
        SET v_star_rating = 5.0;
        SET v_condition_text_ar = 'excellent';
        SET v_condition_color = '#00ff88';
    ELSEIF v_overall_percentage >= 80 THEN
        SET v_condition_level = 'very_good';
        SET v_star_rating = 4.5;
        SET v_condition_text_ar = 'very_good';
        SET v_condition_color = '#00c8ff';
    ELSEIF v_overall_percentage >= 70 THEN
        SET v_condition_level = 'good';
        SET v_star_rating = 4.0;
        SET v_condition_text_ar = 'good';
        SET v_condition_color = '#ffaa00';
    ELSEIF v_overall_percentage >= 60 THEN
        SET v_condition_level = 'fair';
        SET v_star_rating = 3.0;
        SET v_condition_text_ar = 'fair';
        SET v_condition_color = '#ff8800';
    ELSE
        SET v_condition_level = 'poor';
        SET v_star_rating = 2.0;
        SET v_condition_text_ar = 'poor';
        SET v_condition_color = '#ff4444';
    END IF;
    
    -- Insert or update rating
    INSERT INTO vehicle_ratings (
        inspection_id, overall_score, star_rating, condition_level, 
        condition_text_ar, condition_color, good_count, warning_count, 
        fail_count, total_items, rated_by
    ) VALUES (
        p_inspection_id, v_overall_percentage, v_star_rating, v_condition_level,
        v_condition_text_ar, v_condition_color, v_good_count, v_warning_count,
        v_fail_count, v_total_items, p_inspector_id
    ) ON DUPLICATE KEY UPDATE
        overall_score = VALUES(overall_score),
        star_rating = VALUES(star_rating),
        condition_level = VALUES(condition_level),
        condition_text_ar = VALUES(condition_text_ar),
        condition_color = VALUES(condition_color),
        good_count = VALUES(good_count),
        warning_count = VALUES(warning_count),
        fail_count = VALUES(fail_count),
        total_items = VALUES(total_items),
        rated_by = VALUES(rated_by),
        updated_at = NOW();
    
    -- Return the results
    SELECT 
        v_overall_percentage as percentage,
        v_star_rating as star_rating,
        v_condition_level as condition_level,
        v_condition_text_ar as condition_text_ar,
        v_condition_color as condition_color,
        v_good_count as good_count,
        v_warning_count as warning_count,
        v_fail_count as fail_count,
        v_total_items as total_items;
        
END //
DELIMITER ;

-- Procedure to calculate vehicle price
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS `CalculateVehiclePrice`(
    IN p_inspection_id INT,
    IN p_mileage INT
)
BEGIN
    DECLARE v_base_price DECIMAL(12,2) DEFAULT 0;
    DECLARE v_mileage_discount DECIMAL(12,2) DEFAULT 0;
    DECLARE v_depreciation DECIMAL(12,2) DEFAULT 0;
    DECLARE v_condition_adjustment DECIMAL(5,2) DEFAULT 0;
    DECLARE v_final_price DECIMAL(12,2) DEFAULT 0;
    DECLARE v_vehicle_age INT DEFAULT 0;
    DECLARE v_make VARCHAR(50) DEFAULT '';
    DECLARE v_model VARCHAR(50) DEFAULT '';
    DECLARE v_year INT DEFAULT 0;
    DECLARE v_condition_level VARCHAR(20) DEFAULT 'good';
    
    -- Get vehicle details
    SELECT 
        ir.car_make, ir.car_model, ir.car_year, ir.mileage,
        vr.condition_level
    INTO v_make, v_model, v_year, v_mileage, v_condition_level
    FROM inspection_requests ir
    LEFT JOIN vehicle_ratings vr ON ir.id = vr.inspection_id
    WHERE ir.id = p_inspection_id;
    
    -- Get base price from database
    SELECT base_price, depreciation_rate
    INTO v_base_price, v_depreciation_rate
    FROM vehicle_pricing 
    WHERE make = v_make AND model = v_model AND year = v_year;
    
    -- If no base price found, estimate it
    IF v_base_price IS NULL OR v_base_price = 0 THEN
        SET v_base_price = EstimateBasePrice(v_make, v_model, v_year);
        SET v_depreciation_rate = 10;
    END IF;
    
    -- Calculate mileage discount
    SET v_mileage_discount = (v_mileage / 10000) * 500;
    
    -- Calculate depreciation
    SET v_vehicle_age = YEAR(CURRENT_DATE) - v_year;
    SET v_depreciation = v_base_price * (v_depreciation_rate / 100) * v_vehicle_age;
    
    -- Get condition adjustment
    SELECT adjustment_percentage
    INTO v_condition_adjustment
    FROM price_adjustments 
    WHERE condition_level = v_condition_level;
    
    -- Calculate final price
    SET v_final_price = (v_base_price - v_mileage_discount - v_depreciation) * (1 + v_condition_adjustment / 100);
    
    -- Return pricing details
    SELECT 
        v_base_price as base_price,
        v_mileage_discount as mileage_discount,
        v_depreciation as depreciation,
        v_condition_adjustment as condition_adjustment,
        v_final_price as final_price,
        ROUND(v_final_price * 0.9, -3) as min_price,
        ROUND(v_final_price * 1.1, -3) as max_price,
        v_mileage as mileage,
        v_vehicle_age as vehicle_age;
        
END //
DELIMITER ;

-- Function to estimate base price
DELIMITER //
CREATE FUNCTION IF NOT EXISTS `EstimateBasePrice`(
    p_make VARCHAR(50),
    p_model VARCHAR(50),
    p_year INT
) RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
    DECLARE v_base_price DECIMAL(12,2) DEFAULT 500000;
    DECLARE v_year_diff INT DEFAULT 0;
    
    -- Set base price based on make and model
    CASE LOWER(p_make)
        WHEN 'toyota' THEN
            CASE LOWER(p_model)
                WHEN 'camry' THEN SET v_base_price = 850000;
                WHEN 'corolla' THEN SET v_base_price = 650000;
                WHEN 'yaris' THEN SET v_base_price = 420000;
                ELSE SET v_base_price = 600000;
            END CASE;
        WHEN 'hyundai' THEN
            CASE LOWER(p_model)
                WHEN 'elantra' THEN SET v_base_price = 580000;
                WHEN 'tucson' THEN SET v_base_price = 750000;
                WHEN 'accent' THEN SET v_base_price = 480000;
                ELSE SET v_base_price = 550000;
            END CASE;
        WHEN 'nissan' THEN
            CASE LOWER(p_model)
                WHEN 'sentra' THEN SET v_base_price = 530000;
                WHEN 'sunny' THEN SET v_base_price = 420000;
                WHEN 'patrol' THEN SET v_base_price = 1800000;
                ELSE SET v_base_price = 500000;
            END CASE;
        WHEN 'mercedes' THEN
            CASE LOWER(p_model)
                WHEN 'e-class' THEN SET v_base_price = 2800000;
                WHEN 'c-class' THEN SET v_base_price = 2200000;
                WHEN 'a-class' THEN SET v_base_price = 1800000;
                ELSE SET v_base_price = 2000000;
            END CASE;
        WHEN 'bmw' THEN
            CASE LOWER(p_model)
                WHEN 'x5' THEN SET v_base_price = 2500000;
                WHEN 'x3' THEN SET v_base_price = 2000000;
                WHEN '3-series' THEN SET v_base_price = 1900000;
                ELSE SET v_base_price = 1800000;
            END CASE;
        WHEN 'kia' THEN
            CASE LOWER(p_model)
                WHEN 'sportage' THEN SET v_base_price = 680000;
                WHEN 'cerato' THEN SET v_base_price = 530000;
                WHEN 'picanto' THEN SET v_base_price = 380000;
                ELSE SET v_base_price = 500000;
            END CASE;
        ELSE SET v_base_price = 500000;
    END CASE;
    
    -- Adjust for year
    SET v_year_diff = YEAR(CURRENT_DATE) - p_year;
    SET v_base_price = v_base_price * (1 - (v_year_diff * 0.05));
    
    -- Ensure minimum price
    IF v_base_price < 100000 THEN
        SET v_base_price = 100000;
    END IF;
    
    RETURN v_base_price;
END //
DELIMITER ;

-- ===== TRIGGERS =====

-- Trigger to log price changes
DELIMITER //
CREATE TRIGGER IF NOT EXISTS `log_price_change`
AFTER UPDATE ON `vehicle_pricing`
FOR EACH ROW
BEGIN
    IF OLD.base_price != NEW.base_price THEN
        INSERT INTO vehicle_pricing_history (
            vehicle_pricing_id, old_base_price, new_base_price, 
            price_change_percentage, changed_by
        ) VALUES (
            NEW.id, OLD.base_price, NEW.base_price,
            ROUND(((NEW.base_price - OLD.base_price) / OLD.base_price) * 100, 2),
            NEW.id  -- This should be updated to actual admin ID
        );
    END IF;
END //
DELIMITER ;

-- ===== VIEWS =====

-- View for vehicle ratings with details
CREATE OR REPLACE VIEW `vehicle_rating_details` AS
SELECT 
    vr.id,
    vr.inspection_id,
    vr.overall_score,
    vr.star_rating,
    vr.condition_level,
    vr.condition_text_ar,
    vr.condition_color,
    vr.good_count,
    vr.warning_count,
    vr.fail_count,
    vr.total_items,
    vr.recommendation,
    vr.rated_by,
    vr.rated_at,
    ir.customer_name,
    ir.car_make,
    ir.car_model,
    ir.car_year,
    ir.mileage,
    u.username as inspector_name
FROM vehicle_ratings vr
JOIN inspection_requests ir ON vr.inspection_id = ir.id
JOIN users u ON vr.rated_by = u.id;

-- View for pricing with vehicle details
CREATE OR REPLACE VIEW `vehicle_pricing_details` AS
SELECT 
    vp.id,
    vp.make,
    vp.model,
    vp.year,
    vp.base_price,
    vp.price_per_km,
    vp.depreciation_rate,
    vp.min_price,
    vp.max_price,
    vp.popularity_score,
    vp.last_updated,
    COUNT(vr.id) as rated_vehicles_count,
    AVG(vr.overall_score) as avg_condition_score
FROM vehicle_pricing vp
LEFT JOIN inspection_requests ir ON vp.make = ir.car_make 
    AND vp.model = ir.car_model 
    AND vp.year = ir.car_year
LEFT JOIN vehicle_ratings vr ON ir.id = vr.inspection_id
GROUP BY vp.id;

-- ===== INDEXES FOR PERFORMANCE =====

-- Additional indexes for better performance
CREATE INDEX IF NOT EXISTS `idx_vehicle_ratings_date` ON `vehicle_ratings` (`rated_at`);
CREATE INDEX IF NOT EXISTS `idx_vehicle_ratings_score` ON `vehicle_ratings` (`overall_score`);
CREATE INDEX IF NOT EXISTS `idx_pricing_popularity` ON `vehicle_pricing` (`popularity_score`);
CREATE INDEX IF NOT EXISTS `idx_market_price_vehicle` ON `market_price_comparisons` (`make`, `model`, `year`);
CREATE INDEX IF NOT EXISTS `idx_market_price_date` ON `market_price_comparisons` (`data_date`);

-- ===== FINAL SETUP VERIFICATION =====

-- Verify setup
SELECT 'Vehicle Rating & Pricing System Setup Completed' as status;
SELECT COUNT(*) as total_pricing_records FROM vehicle_pricing;
SELECT COUNT(*) as total_adjustment_records FROM price_adjustments;
