-- New Booking System Migration
-- This script creates the new booking system tables

-- Create main bookings table
CREATE TABLE main_bookings (
    booking_id BIGSERIAL PRIMARY KEY,
    user_id UUID REFERENCES users(user_id),
    total_price DECIMAL(12, 2) NOT NULL,
    currency_id BIGINT REFERENCES currencies(currency_id),
    status VARCHAR(20) DEFAULT 'pending',
    payment_status VARCHAR(20) DEFAULT 'pending',
    special_requests TEXT,
    booking_reference VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create beach bookings table (for beach place terrains)
CREATE TABLE beach_bookings (
    beach_booking_id BIGSERIAL PRIMARY KEY,
    main_booking_id BIGINT REFERENCES main_bookings(booking_id) ON DELETE CASCADE,
    beach_place_id UUID REFERENCES beach_places(beach_place_id),
    terrain_id UUID REFERENCES beach_place_terrains(terrain_id),
    booking_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    quantity INTEGER DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL,
    subtotal DECIMAL(10, 2) NOT NULL
);

-- Create restaurant bookings table
CREATE TABLE restaurant_bookings (
    restaurant_booking_id BIGSERIAL PRIMARY KEY,
    main_booking_id BIGINT REFERENCES main_bookings(booking_id) ON DELETE CASCADE,
    restaurant_id UUID REFERENCES restaurants(restaurant_id),
    item_id UUID REFERENCES restaurant_items(item_id),
    quantity INTEGER DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL,
    subtotal DECIMAL(10, 2) NOT NULL,
    special_instructions TEXT
);

-- Create market bookings table
CREATE TABLE market_bookings (
    market_booking_id BIGSERIAL PRIMARY KEY,
    main_booking_id BIGINT REFERENCES main_bookings(booking_id) ON DELETE CASCADE,
    market_id UUID REFERENCES markets(market_id),
    item_id UUID REFERENCES market_items(item_id),
    quantity INTEGER DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL,
    subtotal DECIMAL(10, 2) NOT NULL,
    delivery_address TEXT,
    delivery_time TIMESTAMP
);

-- Create adventure bookings table
CREATE TABLE adventure_bookings (
    adventure_booking_id BIGSERIAL PRIMARY KEY,
    main_booking_id BIGINT REFERENCES main_bookings(booking_id) ON DELETE CASCADE,
    adventure_id UUID REFERENCES adventures(adventure_id),
    schedule_id BIGINT REFERENCES adventure_schedules(schedule_id),
    participant_count INTEGER DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL,
    subtotal DECIMAL(10, 2) NOT NULL,
    participant_names TEXT[]
);