-- AUTO TESTER - Add Customer Expected Price to Comprehensive Inspection
-- Created: 2026-04-22
-- Description: Add customer expected price field to comprehensive inspection requests

-- Add customer_expected_price field to comprehensive_inspection_requests
ALTER TABLE `comprehensive_inspection_requests` 
ADD COLUMN `customer_expected_price` DECIMAL(12,2) COMMENT 'Customer expected selling price' 
AFTER `special_instructions`;

-- Update the vehicle evaluation system to use customer expected price
-- Update the trigger that creates evaluation requests to pass the customer price
DELIMITER $$

DROP TRIGGER IF EXISTS `create_evaluation_on_inspection_complete`$$

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 with customer expected price
        INSERT INTO vehicle_evaluation_requests 
        (inspection_request_id, vehicle_id, customer_id, evaluation_type, 
         evaluation_purpose, requested_by, status, priority_level, customer_expected_price)
        VALUES (
            NEW.id,
            NEW.vehicle_id,
            NEW.customer_id,
            'market_value',
            'sale',
            NEW.inspector_id,
            'pending',
            'medium',
            NEW.customer_expected_price
        );
    END IF;
END$$

DELIMITER ;

-- Update the vehicle evaluation calculation to include customer expected price
DELIMITER $$

DROP TRIGGER IF EXISTS `update_evaluation_history`$$

CREATE TRIGGER `update_evaluation_history`
AFTER INSERT ON `vehicle_evaluation_results`
FOR EACH ROW
BEGIN
    -- Get vehicle and customer price information
    DECLARE vehicle_make VARCHAR(100);
    DECLARE vehicle_model VARCHAR(100);
    DECLARE vehicle_year INT;
    DECLARE vehicle_mileage INT;
    DECLARE customer_price DECIMAL(12,2);
    
    SELECT v.make, v.model, v.year, v.mileage, ver.customer_expected_price 
    INTO vehicle_make, vehicle_model, vehicle_year, vehicle_mileage, customer_price
    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 with customer price consideration
    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)),
        JSON_OBJECT(
            'customer_expected_price', customer_price,
            'difference_from_customer_price', (NEW.final_value - customer_price),
            'price_alignment_percentage', CASE 
                WHEN customer_price > 0 THEN (NEW.final_value / customer_price * 100)
                ELSE NULL
            END
        ),
        CONCAT('Evaluation by ', NEW.evaluation_provider, ' with confidence score: ', NEW.confidence_score, 
               '. Customer expected: EGP ', customer_price)
    );
END$$

DELIMITER ;

-- Log the migration
INSERT INTO system_logs (log_type, message, details, created_at) VALUES
('migration', 'Customer expected price field added to comprehensive inspections', JSON_OBJECT(
    'migration_version', '011',
    'tables_modified', 1,
    'triggers_updated', 2,
    'migration_date', CURRENT_TIMESTAMP
), CURRENT_TIMESTAMP);
