-- Vehicle Book-In System Database Schema
-- Created for Graham Auctions

CREATE DATABASE IF NOT EXISTS vehicle_bookin;
USE vehicle_bookin;

-- Vehicle bookings table
CREATE TABLE IF NOT EXISTS vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Tracking
    consignor_code VARCHAR(50),
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('draft', 'submitted') DEFAULT 'draft',
    
    -- Consignor Info
    consignor_name VARCHAR(255),
    consignor_phone VARCHAR(20),
    reserve_price VARCHAR(50),
    
    -- Vehicle Identification
    vin VARCHAR(17),
    year VARCHAR(4),
    make VARCHAR(100),
    model VARCHAR(100),
    trim VARCHAR(100),
    colour VARCHAR(50),
    
    -- Odometer
    odometer VARCHAR(20),
    odometer_unit ENUM('km', 'miles') DEFAULT 'km',
    mileage_unknown BOOLEAN DEFAULT FALSE,
    
    -- Engine/Drivetrain
    cylinders VARCHAR(20),
    engine_size VARCHAR(20),
    fuel_type VARCHAR(20),
    transmission_type VARCHAR(20),
    driveline VARCHAR(20),
    
    -- Features (stored as JSON for flexibility)
    entertainment_features JSON,
    comfort_features JSON,
    
    -- Status/Condition
    registration_status VARCHAR(20),
    registration_province VARCHAR(5),
    engine_light BOOLEAN DEFAULT FALSE,
    tpms_light BOOLEAN DEFAULT FALSE,
    abs_light BOOLEAN DEFAULT FALSE,
    airbag_light BOOLEAN DEFAULT FALSE,
    other_dash_lights TEXT,
    
    -- CARFAX
    carfax_url TEXT,
    carfax_file_path VARCHAR(255),
    carfax_order_requested BOOLEAN DEFAULT FALSE,
    carfax_email_requested BOOLEAN DEFAULT FALSE,
    
    -- Drop-off
    dropoff_date DATE,
    dropoff_time TIME,
    
    -- Email sent
    email_sent BOOLEAN DEFAULT FALSE,
    email_sent_at TIMESTAMP NULL,
    
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_consignor_code (consignor_code),
    INDEX idx_created_by (created_by),
    INDEX idx_status (status),
    INDEX idx_vin (vin),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Edit history table
CREATE TABLE IF NOT EXISTS edit_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vehicle_id INT NOT NULL,
    edited_by INT NOT NULL,
    edited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    action ENUM('created', 'updated', 'deleted') NOT NULL,
    changes TEXT,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
    FOREIGN KEY (edited_by) REFERENCES users(id),
    INDEX idx_vehicle_id (vehicle_id),
    INDEX idx_edited_by (edited_by),
    INDEX idx_edited_at (edited_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
