-- =====================================================
-- Sandbox Kids Zone - Database Schema
-- Run this SQL to create all required tables
-- =====================================================


-- Party rooms table
CREATE TABLE IF NOT EXISTS party_rooms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    base_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    max_capacity INT NOT NULL DEFAULT 20,
    photo VARCHAR(255) NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Room timeslots (available booking times per room)
CREATE TABLE IF NOT EXISTS room_timeslots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_id INT NOT NULL,
    day_of_week TINYINT NOT NULL DEFAULT 0,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    INDEX idx_room_timeslots_room (room_id),
    INDEX idx_room_timeslots_day (room_id, day_of_week)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Party packages (pricing tiers)
CREATE TABLE IF NOT EXISTS party_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    duration_minutes INT DEFAULT 120,
    max_children INT DEFAULT 15,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add-ons (extra items for bookings)
CREATE TABLE IF NOT EXISTS addons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Party bookings table
CREATE TABLE IF NOT EXISTS party_bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_id INT NULL,
    package_id INT NULL,
    date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NULL,

    -- Parent/contact info
    parent_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(30) NULL,

    -- Child info
    child_name VARCHAR(100) NULL,
    child_age INT NULL,
    num_children INT DEFAULT 1,

    -- Pricing
    base_price DECIMAL(10,2) DEFAULT 0.00,
    addons_price DECIMAL(10,2) DEFAULT 0.00,
    total_price DECIMAL(10,2) DEFAULT 0.00,
    addons_json TEXT NULL,

    -- Payment
    stripe_payment_id VARCHAR(255) NULL,
    payment_status VARCHAR(20) DEFAULT 'pending',

    -- Booking status
    status VARCHAR(20) DEFAULT 'pending',
    notes TEXT NULL,

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Indexes
    INDEX idx_party_bookings_date (date),
    INDEX idx_party_bookings_status (status),
    INDEX idx_party_bookings_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- General bookings table (for walk-in/general play - used by functions.php)
CREATE TABLE IF NOT EXISTS bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_id INT NULL,
    package_id INT NULL,
    date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NULL,

    -- Contact info
    parent_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(30) NULL,

    -- Children
    num_children INT DEFAULT 1,

    -- Pricing
    total_price DECIMAL(10,2) DEFAULT 0.00,

    -- Status
    status VARCHAR(20) DEFAULT 'pending',

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Indexes
    INDEX idx_bookings_date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Waivers table
CREATE TABLE IF NOT EXISTS waivers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    booking_id INT NULL,

    -- Parent info
    parent_name VARCHAR(100) NOT NULL,
    parent_email VARCHAR(255) NULL,
    parent_phone VARCHAR(30) NULL,
    email VARCHAR(255) NULL,
    phone VARCHAR(30) NULL,

    -- Legacy fields (for compatibility)
    children_json TEXT NULL,
    child_names TEXT NULL,

    -- Files
    signature_path VARCHAR(255) NULL,
    pdf_path VARCHAR(255) NULL,

    -- Type: 'party' for party bookings, 'walk_in' for general play
    waiver_type VARCHAR(20) DEFAULT 'walk_in',

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Indexes
    INDEX idx_waivers_created (created_at),
    INDEX idx_waivers_booking (booking_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Waiver children (individual children on a waiver)
CREATE TABLE IF NOT EXISTS waiver_children (
    id INT AUTO_INCREMENT PRIMARY KEY,
    waiver_id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    dob DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Indexes
    INDEX idx_waiver_children_waiver (waiver_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Play sessions table (walk-in and online play bookings)
CREATE TABLE IF NOT EXISTS play_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_type VARCHAR(20) NOT NULL DEFAULT 'online',
    parent_name VARCHAR(100) NOT NULL,
    parent_email VARCHAR(255) NULL,
    parent_phone VARCHAR(30) NULL,
    num_children INT NOT NULL DEFAULT 1,
    hours DECIMAL(4,2) NOT NULL DEFAULT 1.00,
    hourly_rate DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(20) NOT NULL DEFAULT 'cash',
    payment_status VARCHAR(20) DEFAULT 'pending',
    stripe_payment_id VARCHAR(255) NULL,
    square_payment_id VARCHAR(255) NULL,
    waiver_id INT NULL,
    created_by_admin_id INT NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_play_created (created_at),
    INDEX idx_play_status (payment_status),
    INDEX idx_play_type (session_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Add Foreign Keys (separate for compatibility)
-- =====================================================

ALTER TABLE room_timeslots
    ADD CONSTRAINT fk_timeslots_room
    FOREIGN KEY (room_id) REFERENCES party_rooms(id) ON DELETE CASCADE;

ALTER TABLE party_bookings
    ADD CONSTRAINT fk_party_bookings_room
    FOREIGN KEY (room_id) REFERENCES party_rooms(id) ON DELETE SET NULL;

ALTER TABLE party_bookings
    ADD CONSTRAINT fk_party_bookings_package
    FOREIGN KEY (package_id) REFERENCES party_packages(id) ON DELETE SET NULL;

ALTER TABLE bookings
    ADD CONSTRAINT fk_bookings_room
    FOREIGN KEY (room_id) REFERENCES party_rooms(id) ON DELETE SET NULL;

ALTER TABLE bookings
    ADD CONSTRAINT fk_bookings_package
    FOREIGN KEY (package_id) REFERENCES party_packages(id) ON DELETE SET NULL;

ALTER TABLE waivers
    ADD CONSTRAINT fk_waivers_booking
    FOREIGN KEY (booking_id) REFERENCES party_bookings(id) ON DELETE SET NULL;

ALTER TABLE waiver_children
    ADD CONSTRAINT fk_waiver_children_waiver
    FOREIGN KEY (waiver_id) REFERENCES waivers(id) ON DELETE CASCADE;

ALTER TABLE play_sessions
    ADD CONSTRAINT fk_play_sessions_waiver
    FOREIGN KEY (waiver_id) REFERENCES waivers(id) ON DELETE SET NULL;

ALTER TABLE play_sessions
    ADD CONSTRAINT fk_play_sessions_admin
    FOREIGN KEY (created_by_admin_id) REFERENCES admins(id) ON DELETE SET NULL;

