-- ============================================
-- CMS DATABASE SCHEMA - PRODUCTION READY (v2)
-- ============================================

-- 1. ROLES
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE COMMENT 'super_admin, agent, user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. USERS
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(50),
    country VARCHAR(100),
    password VARCHAR(255) NOT NULL,
    profile_picture VARCHAR(255),
    status ENUM('active','inactive','blocked') DEFAULT 'inactive',
    otp VARCHAR(10) DEFAULT NULL,
    otp_expires_at DATETIME NULL,
    agent_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT,
    FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX (role_id),
    INDEX (agent_id)
);

-- 3. BRANDS
CREATE TABLE brands (
    id INT AUTO_INCREMENT PRIMARY KEY,
    brand_name VARCHAR(100) NOT NULL UNIQUE,
    brand_logo VARCHAR(255), -- path to logo image
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 4. CARS TABLE
CREATE TABLE cars (
    id INT AUTO_INCREMENT PRIMARY KEY,
    brand_id INT,             -- linked to brands (makes table)
    stock_no VARCHAR(100),      -- ref_no
    name VARCHAR(255) NOT NULL, -- car_title
    make_region VARCHAR(100),
    
    chassis_no VARCHAR(100),
    category VARCHAR(100),
    model VARCHAR(100),         -- car_model
    make_year VARCHAR(50),      -- Year from years table
    fuel_type VARCHAR(50),
    transmission VARCHAR(50),
    steering VARCHAR(50),
    doors VARCHAR(20),
    seat VARCHAR(20),           -- seating
    driver_type VARCHAR(50),    -- drive_type
    body_type VARCHAR(50),
    color VARCHAR(50),
    engine VARCHAR(100),        -- engine cc
    mileage VARCHAR(50),
    budget DECIMAL(12,2),       -- car_price
    price DECIMAL(12,2),        
    image1 TEXT,                -- featured_image
    image2 TEXT,                -- gallery 1
    image3 TEXT,                -- gallery 2
    image4 TEXT,                -- gallery 3
    image5 TEXT,                -- gallery 4
    image6 TEXT,                -- gallery 5
    image7 TEXT,                -- gallery 6
    image8 TEXT,                -- gallery 7
    image9 TEXT,                -- gallery 8
    image10 TEXT,               -- gallery 9
    status ENUM('available','sold','pending') DEFAULT 'available',
    created_by INT,
    dimensions VARCHAR(255),    -- added per your header
    m3 VARCHAR(100),            -- added per your header
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Relationships
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    -- Note: Ensure your brands table is named 'brands' or 'makes' to match the FK below
    FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE SET NULL,
    
    -- Performance Indexes
    INDEX (brand_id),
    INDEX (created_by),
    INDEX (stock_no)
);

-- 5. ORDERS
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    user_id INT NOT NULL,
    car_id INT NOT NULL,

    -- STATUS
    status ENUM('pending','approved','in_transit','completed','cancelled','returned') DEFAULT 'pending',
    payment_status ENUM('pending','paid','partial') DEFAULT 'pending',

    -- INSPECTION & SHIPMENT
    inspection_done BOOLEAN DEFAULT FALSE,
    shipment_booked BOOLEAN DEFAULT FALSE,
    documents_issued BOOLEAN DEFAULT FALSE,

    -- SHIPPING INFO
    country_id INT NOT NULL,        -- Which country the car will be shipped to
    port_id INT NOT NULL,           -- Which port in that country
    

    total_amount DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- FOREIGN KEYS
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (car_id) REFERENCES cars(id) ON DELETE CASCADE,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE RESTRICT,
    FOREIGN KEY (port_id) REFERENCES ports(id) ON DELETE RESTRICT,

    INDEX (user_id),
    INDEX (car_id),
    INDEX (country_id),
    INDEX (port_id)
);

-- 5a. ORDER HISTORY
CREATE TABLE order_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    status ENUM('pending','approved','in_transit','completed','cancelled','returned') NOT NULL,
    changed_by INT,
    note TEXT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX (order_id),
    INDEX (changed_by)
);

-- 6. PAYMENTS
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    car_id INT NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    payment_type ENUM('cash','bank_transfer','card','online','manual') NOT NULL,
    status ENUM('pending','submitted','confirmed','rejected','failed') DEFAULT 'pending',
    transaction_id VARCHAR(100),
    receipt_image VARCHAR(255), -- uploaded receipt proof
    submitted_at TIMESTAMP NULL, -- when user submitted
    verified_by INT NULL, -- admin who verifies
    verified_at TIMESTAMP NULL, -- when admin verified
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (car_id) REFERENCES cars(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX (order_id),
    INDEX (car_id),
    INDEX (user_id),
    INDEX (verified_by)
);

-- 7. INQUIRIES
CREATE TABLE inquiries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    car_make VARCHAR(100),
    car_model VARCHAR(100),
    fuel_type VARCHAR(50),
    transmission VARCHAR(50),
    body_type VARCHAR(50),
    color VARCHAR(50),
    budget DECIMAL(12,2),
    status ENUM('pending','responded','closed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX (user_id)
);

-- 7a. INQUIRY MESSAGES
CREATE TABLE inquiry_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inquiry_id INT NOT NULL,
    sender_id INT NOT NULL,
    receiver_id INT NOT NULL,
    message TEXT NOT NULL,
    is_admin_sender BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (inquiry_id) REFERENCES inquiries(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX (inquiry_id),
    INDEX (sender_id),
    INDEX (receiver_id)
);

-- 8. MESSAGING SYSTEM
CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT NOT NULL,
    receiver_id INT NOT NULL,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX (sender_id),
    INDEX (receiver_id)
);

-- 9. REPORTS
CREATE TABLE sales_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_type ENUM('daily','monthly','yearly') NOT NULL,
    total_orders INT NOT NULL,
    total_revenue DECIMAL(12,2) NOT NULL,
    total_cars_sold INT NOT NULL,
    report_date DATE NOT NULL,
    generated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (generated_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX (generated_by)
);

CREATE TABLE purchase_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    total_cars_purchased INT NOT NULL,
    total_cost DECIMAL(12,2) NOT NULL,
    report_date DATE NOT NULL,
    generated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (generated_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX (generated_by)
);

CREATE TABLE return_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    reason TEXT,
    refund_amount DECIMAL(12,2),
    status ENUM('pending','processed','rejected') DEFAULT 'pending',
    processed_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX (order_id),
    INDEX (processed_by)
);

-- 10. NOTIFICATIONS
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info','warning','success','error') DEFAULT 'info',
    target_role VARCHAR(50) DEFAULT NULL COMMENT 'super_admin, agent, user',
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX (user_id)
);

-- 11. SETTINGS & PROFILE SECURITY
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    notification_enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX (user_id)
);
CREATE TABLE countries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    code VARCHAR(10) UNIQUE,          -- optional (e.g. PK, JP, UAE)
    status TINYINT DEFAULT 1,          -- 1 = active, 0 = inactive
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE ports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    country_id INT NOT NULL,
    port_name VARCHAR(150) NOT NULL,

    inspection_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    price_per_m3 DECIMAL(10,2) NOT NULL DEFAULT 0.00,

    status TINYINT DEFAULT 1,           -- 1 = active, 0 = inactive
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_ports_country
        FOREIGN KEY (country_id)
        REFERENCES countries(id)
        ON DELETE CASCADE
);

-- 12. LEADS
CREATE TABLE leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150),
    phone VARCHAR(50),
    message TEXT,
    source VARCHAR(100), -- e.g. 'contact_form', 'landing_page'
    status ENUM('new', 'contacted', 'converted', 'lost') DEFAULT 'new',
    agent_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX (agent_id)
);

