-- Create Users table
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'customer',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Cars table
CREATE TABLE IF NOT EXISTS cars (
    id SERIAL PRIMARY KEY,
    brand VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    year INTEGER NOT NULL,
    price_per_day DECIMAL(10, 2) NOT NULL,
    price_per_hour DECIMAL(10, 2),
    price_per_week DECIMAL(10, 2),
    price_per_month DECIMAL(10, 2),
    image_url TEXT,
    description TEXT,
    status VARCHAR(20) DEFAULT 'available',
    insurance VARCHAR(255),
    owner VARCHAR(255),
    kilometers INTEGER,
    start_date_of_insurance DATE,
    end_date_of_insurance DATE,
    car_mulkiya_id VARCHAR(100) UNIQUE,
    start_date_of_mulkiya DATE,
    end_date_of_mulkiya DATE,
    maintenance_company VARCHAR(255),
    maintenance_start_date DATE,
    maintenance_end_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Bookings table
CREATE TABLE IF NOT EXISTS bookings (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    car_id INTEGER REFERENCES cars(id),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert demo admin user (password: admin123)
-- bcrypt hash for 'admin123' is $2a$10$7XyH6q6Y0S9Y0Y6Y0S9Y0Oe5l5l5l5l5l5l5l5l5l5l5l5l5l5l5
-- Actually I'll use a script to hash it properly.
