-- S.I Auto Database Schema
-- Car Selling Platform for Zambia

CREATE TABLE IF NOT EXISTS admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    fullname VARCHAR(255) NOT NULL,
    role ENUM('admin', 'moderator') DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS car_listings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    make VARCHAR(100),
    model VARCHAR(100),
    year INT,
    price DECIMAL(12, 2),
    condition ENUM('new', 'used') DEFAULT 'used',
    mileage INT,
    transmission ENUM('manual', 'automatic'),
    fuel_type ENUM('petrol', 'diesel', 'hybrid', 'electric'),
    color VARCHAR(50),
    seller_name VARCHAR(255),
    seller_phone VARCHAR(20),
    seller_email VARCHAR(255),
    seller_type ENUM('dealer', 'individual'),
    location VARCHAR(255),
    status ENUM('pending', 'verified', 'rejected', 'sold') DEFAULT 'pending',
    featured BOOLEAN DEFAULT FALSE,
    views INT DEFAULT 0,
    featured_until DATETIME,
    image_main VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX(status),
    INDEX(year),
    INDEX(price),
    INDEX(created_at)
);

CREATE TABLE IF NOT EXISTS car_images (
    id INT PRIMARY KEY AUTO_INCREMENT,
    car_id INT NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    is_verified BOOLEAN DEFAULT FALSE,
    upload_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(car_id) REFERENCES car_listings(id) ON DELETE CASCADE,
    INDEX(car_id)
);

CREATE TABLE IF NOT EXISTS verification_videos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    car_id INT NOT NULL,
    video_path VARCHAR(255) NOT NULL,
    video_thumbnail VARCHAR(255),
    is_verified BOOLEAN DEFAULT FALSE,
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(car_id) REFERENCES car_listings(id) ON DELETE CASCADE,
    INDEX(car_id)
);

CREATE TABLE IF NOT EXISTS categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    icon VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    `key` VARCHAR(255) UNIQUE NOT NULL,
    `value` LONGTEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO categories (name, slug) VALUES 
('Sedans', 'sedans'),
('SUVs', 'suvs'),
('Trucks', 'trucks'),
('Vans', 'vans'),
('Hatchbacks', 'hatchbacks'),
('Wagons', 'wagons');

INSERT INTO settings (`key`, `value`) VALUES
('site_title', 'S.I Auto - Buy & Sell Quality Cars in Zambia'),
('site_description', 'Verified car listings for dealers and individuals in Zambia'),
('min_images_required', '3'),
('video_required', '1');