-- 1. Users Table (Clients & Admins) 
CREATE TABLE users ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    full_name VARCHAR(255) NOT NULL, 
    email VARCHAR(255) UNIQUE NOT NULL, 
    phone VARCHAR(20), 
    password_hash VARCHAR(255) NOT NULL, 
    role ENUM('client', 'admin') DEFAULT 'client', 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
); 
 
-- 2. Services Catalog 
CREATE TABLE services ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(255) NOT NULL, 
    category ENUM('Surprises', 'Decor', 'Special Effects', 'Rentals', 'Party Entertainments', 'Complete Services') NOT NULL, 
    sub_category VARCHAR(100), 
    pricing_type ENUM('fixed', 'quote') NOT NULL, -- Determines if user sees a price or "Request Quote" 
    base_price DECIMAL(10,2) NULL, 
    description TEXT, 
    is_active BOOLEAN DEFAULT TRUE 
); 
 
-- 3. Bookings & Quote Requests 
CREATE TABLE bookings ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    user_id INT NOT NULL, 
    event_date DATE NOT NULL, 
    event_time TIME, 
    status ENUM('pending', 'quote_requested', 'quote_provided', 'confirmed', 'cancelled') DEFAULT 'pending', 
    custom_notes TEXT, 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE 
); 
 
-- 4. Booking Items (The "Cart" contents) 
CREATE TABLE booking_items ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    booking_id INT NOT NULL, 
    service_id INT NOT NULL, 
    quantity INT DEFAULT 1, 
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE, 
    FOREIGN KEY (service_id) REFERENCES services(id) 
); 
 
-- 5. Portfolio & Gallery 
CREATE TABLE gallery ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    image_url VARCHAR(255) NOT NULL, 
    category VARCHAR(100), -- E.g., 'Special Effects', 'Decor' 
    alt_text VARCHAR(255), -- Critical for SEO 
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
);