-- AUTO TESTER - Customer Valuations Table Migration
-- Created: 2026-04-22
-- Description: Customer vehicle valuations storage table

-- Customer Valuations Table
CREATE TABLE IF NOT EXISTS `customer_valuations` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL COMMENT 'Customer who requested valuation',
    `vehicle_make` VARCHAR(100) NOT NULL,
    `vehicle_model` VARCHAR(100) NOT NULL,
    `vehicle_year` INT NOT NULL,
    `vehicle_condition` ENUM('excellent', 'good', 'fair', 'poor') NOT NULL,
    `mileage` INT NOT NULL,
    `trim_level` VARCHAR(100),
    `fuel_type` VARCHAR(50),
    `transmission` VARCHAR(50),
    `body_type` VARCHAR(50),
    `valuation_value` DECIMAL(12,2) NOT NULL COMMENT 'Final calculated value',
    `value_range_min` DECIMAL(12,2),
    `value_range_max` DECIMAL(12,2),
    `confidence_score` DECIMAL(5,2) DEFAULT 0,
    `valuation_data` JSON COMMENT 'Complete valuation data',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_vehicle_make_model` (`vehicle_make`, `vehicle_model`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Log the migration
INSERT INTO system_logs (log_type, message, details, created_at) VALUES
('migration', 'Customer valuations table created', JSON_OBJECT(
    'migration_version', '010',
    'tables_created', 1,
    'migration_date', CURRENT_TIMESTAMP
), CURRENT_TIMESTAMP);
