#I LOVE YOU EVERDAY#

CREATE DATABASE IF NOT EXISTS reown_db;
USE reown_db;

CREATE TABLE website_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) NOT NULL UNIQUE,
    setting_value TEXT NOT NULL
);

CREATE TABLE social_media_links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    platform VARCHAR(50) NOT NULL,
    url VARCHAR(255) NOT NULL,
    icon_class VARCHAR(50)
);

CREATE TABLE admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('superadmin', 'inspector', 'sales') DEFAULT 'sales',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    city VARCHAR(100),
    password VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE bikes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reference_id VARCHAR(50) UNIQUE,
    model_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    year INT NOT NULL,
    registration_year INT,
    km_driven INT NOT NULL,
    ownership ENUM('1st', '2nd', '3rd', '4th+') DEFAULT '1st',
    insurance_status VARCHAR(50),
    insurance_valid_upto VARCHAR(100),
    rc_status VARCHAR(50),
    rto_code VARCHAR(50),
    location VARCHAR(100) NOT NULL,
    map_url TEXT,
    colour VARCHAR(50),
    engine_capacity VARCHAR(50),
    fuel_type VARCHAR(50) DEFAULT 'Petrol',
    fuel_tank_capacity VARCHAR(50),
    description TEXT,
    engine_condition ENUM('Excellent', 'Good', 'Average') DEFAULT 'Good',
    tyres_condition ENUM('Excellent', 'Good', 'Average') DEFAULT 'Good',
    brakes_condition ENUM('Excellent', 'Good', 'Average') DEFAULT 'Good',
    battery_condition ENUM('Excellent', 'Good', 'Average') DEFAULT 'Good',
    suspension_condition ENUM('Excellent', 'Good', 'Average') DEFAULT 'Good',
    status ENUM('available', 'sold', 'pending') DEFAULT 'available',
    is_featured BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE bike_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bike_id INT NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    is_main BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (bike_id) REFERENCES bikes(id) ON DELETE CASCADE
);

CREATE TABLE bike_videos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bike_id INT NOT NULL,
    video_url VARCHAR(255) NOT NULL,
    FOREIGN KEY (bike_id) REFERENCES bikes(id) ON DELETE CASCADE
);

CREATE TABLE bike_360_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bike_id INT NOT NULL,
    folder_path VARCHAR(255) NOT NULL,
    frame_count INT NOT NULL DEFAULT 36,
    FOREIGN KEY (bike_id) REFERENCES bikes(id) ON DELETE CASCADE
);

CREATE TABLE bike_specifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bike_id INT NOT NULL,
    spec_key VARCHAR(100) NOT NULL,
    spec_value VARCHAR(255) NOT NULL,
    FOREIGN KEY (bike_id) REFERENCES bikes(id) ON DELETE CASCADE
);

CREATE TABLE inspection_bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bike_id INT,
    customer_id INT,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address TEXT,
    bike_location VARCHAR(100),
    preferred_date DATE NOT NULL,
    preferred_time TIME,
    additional_notes TEXT,
    status ENUM('pending', 'assigned', 'completed', 'cancelled') DEFAULT 'pending',
    assigned_inspector_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bike_id) REFERENCES bikes(id) ON DELETE SET NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_inspector_id) REFERENCES admin_users(id) ON DELETE SET NULL
);

CREATE TABLE sell_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reference_id VARCHAR(50) UNIQUE,
    customer_id INT,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    city VARCHAR(100),
    model_name VARCHAR(100) NOT NULL,
    year INT NOT NULL,
    km_driven INT NOT NULL,
    expected_price DECIMAL(10,2),
    registration_number VARCHAR(50),
    description TEXT,
    images_folder VARCHAR(255),
    video_url VARCHAR(255),
    three60_folder VARCHAR(255),
    status ENUM('pending', 'reviewing', 'approved', 'rejected', 'converted') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

CREATE TABLE wishlist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    bike_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (bike_id) REFERENCES bikes(id) ON DELETE CASCADE
);

CREATE TABLE enquiries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    bike_id INT,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    message TEXT,
    status ENUM('new', 'read', 'replied') DEFAULT 'new',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (bike_id) REFERENCES bikes(id) ON DELETE SET NULL
);

CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT,
    action VARCHAR(255) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
);

-- Insert default admin (password is 'password')
INSERT INTO admin_users (username, password, role) VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'superadmin');