-- Ash Transfer Database Schema
-- SQLite Database for Airport Transfer Service

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    phone TEXT,
    role TEXT DEFAULT 'customer' CHECK(role IN ('customer', 'admin', 'driver')),
    is_verified INTEGER DEFAULT 0,
    is_first_booking INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Vehicles Table
CREATE TABLE IF NOT EXISTS vehicles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT NOT NULL UNIQUE CHECK(type IN ('saloon', 'estate', 'mpv', 'executive')),
    name TEXT NOT NULL,
    description TEXT,
    capacity INTEGER NOT NULL,
    luggage_capacity TEXT,
    price_per_mile REAL NOT NULL,
    minimum_fare REAL NOT NULL,
    features TEXT, -- JSON array of features
    image_url TEXT,
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Pricing Configuration Table
CREATE TABLE IF NOT EXISTS pricing_config (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    config_key TEXT UNIQUE NOT NULL,
    config_value TEXT NOT NULL,
    description TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Extras/Add-ons Table
CREATE TABLE IF NOT EXISTS extras (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    price REAL NOT NULL,
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Bookings Table
CREATE TABLE IF NOT EXISTS bookings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    booking_reference TEXT UNIQUE NOT NULL,
    user_id INTEGER,
    guest_email TEXT,
    guest_name TEXT,
    guest_phone TEXT,
    
    -- Journey Details
    pickup_location TEXT NOT NULL,
    pickup_postcode TEXT,
    pickup_lat REAL,
    pickup_lng REAL,
    dropoff_location TEXT NOT NULL,
    dropoff_postcode TEXT,
    dropoff_lat REAL,
    dropoff_lng REAL,
    distance_miles REAL NOT NULL,
    
    -- DateTime
    pickup_date DATE NOT NULL,
    pickup_time TIME NOT NULL,
    
    -- Vehicle & Passengers
    vehicle_id INTEGER NOT NULL,
    passengers INTEGER NOT NULL,
    
    -- Pricing
    base_fare REAL NOT NULL,
    extras_total REAL DEFAULT 0,
    discount_amount REAL DEFAULT 0,
    discount_type TEXT,
    total_price REAL NOT NULL,
    
    -- Status
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'confirmed', 'assigned', 'in_progress', 'completed', 'cancelled')),
    payment_status TEXT DEFAULT 'pending' CHECK(payment_status IN ('pending', 'paid', 'refunded')),
    payment_method TEXT,
    payment_intent_id TEXT,
    
    -- Assignment
    driver_id INTEGER,
    assigned_at DATETIME,
    
    -- Return Journey
    is_return_journey INTEGER DEFAULT 0,
    return_booking_id INTEGER,
    
    -- Notes
    customer_notes TEXT,
    admin_notes TEXT,
    
    -- Timestamps
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
    FOREIGN KEY (driver_id) REFERENCES users(id),
    FOREIGN KEY (return_booking_id) REFERENCES bookings(id)
);

-- Booking Extras (Many-to-Many)
CREATE TABLE IF NOT EXISTS booking_extras (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    booking_id INTEGER NOT NULL,
    extra_id INTEGER NOT NULL,
    quantity INTEGER DEFAULT 1,
    price REAL NOT NULL,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    FOREIGN KEY (extra_id) REFERENCES extras(id)
);

-- Quotes Table
CREATE TABLE IF NOT EXISTS quotes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    quote_reference TEXT UNIQUE NOT NULL,
    user_id INTEGER,
    guest_email TEXT,
    guest_name TEXT,
    
    -- Journey Details
    pickup_location TEXT NOT NULL,
    dropoff_location TEXT NOT NULL,
    distance_miles REAL NOT NULL,
    pickup_date DATE,
    pickup_time TIME,
    
    -- Vehicle & Passengers
    vehicle_id INTEGER NOT NULL,
    passengers INTEGER,
    
    -- Pricing
    base_fare REAL NOT NULL,
    extras_total REAL DEFAULT 0,
    discount_amount REAL DEFAULT 0,
    total_price REAL NOT NULL,
    
    -- Quote extras as JSON
    extras_json TEXT,
    
    -- Status
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'sent', 'accepted', 'declined', 'expired')),
    expires_at DATETIME,
    
    -- Notes
    customer_notes TEXT,
    
    -- Timestamps
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
);

-- Drivers Table (Extended user info)
CREATE TABLE IF NOT EXISTS drivers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER UNIQUE NOT NULL,
    license_number TEXT NOT NULL,
    license_expiry DATE NOT NULL,
    vehicle_registration TEXT,
    vehicle_model TEXT,
    status TEXT DEFAULT 'available' CHECK(status IN ('available', 'busy', 'offline')),
    rating REAL DEFAULT 5.0,
    total_trips INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Messages/Contact Table
CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    phone TEXT,
    subject TEXT,
    message TEXT NOT NULL,
    status TEXT DEFAULT 'new' CHECK(status IN ('new', 'read', 'replied', 'archived')),
    replied_at DATETIME,
    replied_by INTEGER,
    reply_message TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (replied_by) REFERENCES users(id)
);

-- Reviews Table
CREATE TABLE IF NOT EXISTS reviews (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    booking_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    driver_id INTEGER,
    rating INTEGER NOT NULL CHECK(rating >= 1 AND rating <= 5),
    comment TEXT,
    is_published INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (driver_id) REFERENCES users(id)
);

-- Payment Logs Table
CREATE TABLE IF NOT EXISTS payment_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    booking_id INTEGER NOT NULL,
    amount REAL NOT NULL,
    currency TEXT DEFAULT 'GBP',
    payment_method TEXT NOT NULL,
    payment_intent_id TEXT,
    status TEXT NOT NULL,
    response_json TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id)
);

-- Email Logs Table
CREATE TABLE IF NOT EXISTS email_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    recipient_email TEXT NOT NULL,
    subject TEXT NOT NULL,
    template_name TEXT,
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'sent', 'failed')),
    error_message TEXT,
    sent_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for Performance
CREATE INDEX IF NOT EXISTS idx_bookings_user ON bookings(user_id);
CREATE INDEX IF NOT EXISTS idx_bookings_status ON bookings(status);
CREATE INDEX IF NOT EXISTS idx_bookings_date ON bookings(pickup_date);
CREATE INDEX IF NOT EXISTS idx_bookings_reference ON bookings(booking_reference);
CREATE INDEX IF NOT EXISTS idx_quotes_user ON quotes(user_id);
CREATE INDEX IF NOT EXISTS idx_quotes_reference ON quotes(quote_reference);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_drivers_user ON drivers(user_id);
CREATE INDEX IF NOT EXISTS idx_messages_status ON messages(status);

-- Insert Default Vehicles
INSERT OR IGNORE INTO vehicles (type, name, description, capacity, luggage_capacity, price_per_mile, minimum_fare, features, is_active) VALUES
('saloon', 'Mercedes E-Class Saloon', 'Comfortable and spacious Mercedes E-Class 2025 model', 4, '2 large suitcases', 2.00, 25.00, '["Leather seats", "Air conditioning", "Professional driver", "Flight monitoring", "USB charging"]', 1),
('estate', 'Mercedes E-Class Estate', 'Extra luggage space with Mercedes E-Class Estate', 4, '3-4 large suitcases', 2.20, 30.00, '["Extra luggage space", "Golf clubs/ski equipment", "Comfortable seating", "Air conditioning", "Professional driver"]', 1),
('mpv', 'Mercedes V-Class MPV', 'Spacious for groups with Mercedes V-Class - CURRENTLY OUT OF SERVICE', 6, '4-5 large suitcases', 2.50, 35.00, '["Spacious for groups", "Child seat compatible", "Ample luggage space", "Comfortable seating", "Air conditioning"]', 0),
('executive', 'Mercedes S-Class Executive', 'Premium luxury with Mercedes S-Class or BMW 7 Series', 4, '2-3 large suitcases', 3.00, 40.00, '["Premium leather interior", "Professional chauffeur", "WiFi & charging", "Refreshments", "Climate control", "Executive service"]', 1);

-- Insert Default Extras
INSERT OR IGNORE INTO extras (code, name, description, price, is_active) VALUES
('meet_greet', 'Meet & Greet', 'Driver waits at arrivals with name board', 15.00, 1),
('child_seat', 'Child Seat', 'Safety-approved child seat (various sizes)', 0.00, 1),
('booster_seat', 'Booster Seat', 'Booster seat for older children', 0.00, 1),
('infant_seat', 'Infant Car Seat', 'Infant car seat for babies', 0.00, 1);

-- Insert Default Pricing Configuration
INSERT OR IGNORE INTO pricing_config (config_key, config_value, description) VALUES
('return_discount_percent', '10', 'Discount percentage for return journey bookings'),
('first_ride_discount_percent', '10', 'Discount percentage for first-time customers'),
('bulk_booking_discount_percent', '15', 'Discount percentage for 5+ journeys'),
('night_surcharge_percent', '15', 'Surcharge percentage for night bookings (11pm-6am)'),
('airport_heathrow_adjustment', '5', 'Additional fee for Heathrow Airport'),
('airport_gatwick_adjustment', '5', 'Additional fee for Gatwick Airport'),
('airport_manchester_adjustment', '3', 'Additional fee for Manchester Airport'),
('airport_birmingham_adjustment', '3', 'Additional fee for Birmingham Airport'),
('free_waiting_heathrow', '60', 'Free waiting time at Heathrow in minutes'),
('free_waiting_gatwick', '60', 'Free waiting time at Gatwick in minutes'),
('free_waiting_manchester', '45', 'Free waiting time at Manchester in minutes'),
('free_waiting_birmingham', '45', 'Free waiting time at Birmingham in minutes'),
('waiting_charge_per_15min', '5', 'Charge per 15 minutes waiting time after free period'),
('currency', 'GBP', 'Currency code'),
('tax_rate_percent', '0', 'Tax rate percentage (VAT)'),
('stripe_enabled', '1', 'Enable Stripe payment processing'),
('email_notifications_enabled', '1', 'Enable email notifications');

-- Insert Default Admin User (password: Global2026@)
INSERT OR IGNORE INTO users (email, password, first_name, last_name, phone, role, is_verified) VALUES
('admin@ashtransfer.com', '$2a$10$YourHashedPasswordHere', 'Admin', 'User', '+44 7860 261578', 'admin', 1);

-- Quotes Table (for storing quote requests)
CREATE TABLE IF NOT EXISTS quotes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    guest_email TEXT,
    guest_name TEXT,
    guest_phone TEXT,
    pickup_location TEXT NOT NULL,
    dropoff_location TEXT NOT NULL,
    pickup_date DATE NOT NULL,
    pickup_time TIME,
    passengers INTEGER NOT NULL,
    vehicle_type TEXT,
    estimated_price REAL,
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'sent', 'converted', 'expired')),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Contact Messages Table
CREATE TABLE IF NOT EXISTS contact_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    phone TEXT,
    subject TEXT,
    message TEXT NOT NULL,
    is_read INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
