-- Complete Booking System Migration
-- This script creates a new unified booking system with main bookings and sub-bookings tables

-- Step 1: 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', -- 'pending', 'confirmed', 'cancelled', 'completed'
    payment_status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'paid', 'refunded'
    special_requests TEXT,
    booking_reference VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 2: Create sub-bookings tables

-- Beach bookings (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
);

-- Restaurant bookings (items)
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
);

-- Market bookings (items)
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
);

-- Adventure bookings
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[]
);

-- Step 3: Update reviews table to work with new booking system
ALTER TABLE reviews ADD COLUMN IF NOT EXISTS main_booking_id BIGINT REFERENCES main_bookings(booking_id);

-- Step 4: Drop old bookings table
DROP TABLE IF EXISTS bookings;

-- Step 5: Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_main_bookings_user_id ON main_bookings(user_id);
CREATE INDEX IF NOT EXISTS idx_main_bookings_booking_reference ON main_bookings(booking_reference);
CREATE INDEX IF NOT EXISTS idx_beach_bookings_main_booking_id ON beach_bookings(main_booking_id);
CREATE INDEX IF NOT EXISTS idx_restaurant_bookings_main_booking_id ON restaurant_bookings(main_booking_id);
CREATE INDEX IF NOT EXISTS idx_market_bookings_main_booking_id ON market_bookings(main_booking_id);
CREATE INDEX IF NOT EXISTS idx_adventure_bookings_main_booking_id ON adventure_bookings(main_booking_id);
CREATE INDEX IF NOT EXISTS idx_reviews_main_booking_id ON reviews(main_booking_id);