# New Booking System Implementation

## Overview
This document describes the implementation of a new comprehensive booking system that replaces the old single `bookings` table with a more flexible structure that can handle all types of bookings in the system.

## New Database Structure

### 1. Main Bookings Table
The main table that holds general booking information:

```sql
CREATE TABLE main_bookings (
    booking_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT 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
);
```

### 2. Sub-Booking Tables

#### Beach Bookings
```sql
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 BIGINT 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
```sql
CREATE TABLE restaurant_bookings (
    restaurant_booking_id BIGSERIAL PRIMARY KEY,
    main_booking_id BIGINT REFERENCES main_bookings(booking_id) ON DELETE CASCADE,
    restaurant_id BIGINT REFERENCES restaurants(restaurant_id),
    item_id BIGINT 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
```sql
CREATE TABLE market_bookings (
    market_booking_id BIGSERIAL PRIMARY KEY,
    main_booking_id BIGINT REFERENCES main_bookings(booking_id) ON DELETE CASCADE,
    market_id BIGINT REFERENCES markets(market_id),
    item_id BIGINT 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
```sql
CREATE TABLE adventure_bookings (
    adventure_booking_id BIGSERIAL PRIMARY KEY,
    main_booking_id BIGINT REFERENCES main_bookings(booking_id) ON DELETE CASCADE,
    adventure_id BIGINT 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[]
);
```

### 3. Updated Reviews Table
The reviews table is updated to work with the new booking system:

```sql
ALTER TABLE reviews ADD COLUMN IF NOT EXISTS main_booking_id BIGINT REFERENCES main_bookings(booking_id);
ALTER TABLE reviews ADD COLUMN IF NOT EXISTS review_type VARCHAR(20); -- 'beach', 'restaurant', 'market', 'adventure'
```

## Migration Scripts

### 1. Drop Old Bookings Table
File: `07_drop_old_bookings.sql`
```sql
-- Drop the old bookings table and related constraints
ALTER TABLE reviews DROP CONSTRAINT IF EXISTS reviews_booking_id_fkey;
DROP TABLE IF EXISTS bookings CASCADE;
```

### 2. Create New Booking Tables
File: `06_new_booking_system.sql`
```sql
-- New comprehensive booking system
-- Main bookings table with general information
CREATE TABLE main_bookings (
    booking_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT 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
);

-- Beach bookings (sunbeds/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 BIGINT 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 BIGINT REFERENCES restaurants(restaurant_id),
    item_id BIGINT 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 BIGINT REFERENCES markets(market_id),
    item_id BIGINT 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 BIGINT 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[]
);

-- Updated reviews table to reference main_bookings instead of old bookings
ALTER TABLE reviews DROP CONSTRAINT IF EXISTS reviews_booking_id_fkey;
ALTER TABLE reviews ADD CONSTRAINT reviews_main_booking_id_fkey 
    FOREIGN KEY (booking_id) REFERENCES main_bookings(booking_id);
```

### 3. Complete Migration Script
File: `09_complete_booking_migration.sql`
```sql
-- Complete booking system migration script

-- Step 1: Drop old bookings table
ALTER TABLE reviews DROP CONSTRAINT IF EXISTS reviews_booking_id_fkey;
DROP TABLE IF EXISTS bookings CASCADE;

-- Step 2: Create new booking tables
-- Main bookings table with general information
CREATE TABLE main_bookings (
    booking_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT 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
);

-- Beach bookings (sunbeds/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 BIGINT 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 BIGINT REFERENCES restaurants(restaurant_id),
    item_id BIGINT 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 BIGINT REFERENCES markets(market_id),
    item_id BIGINT 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 BIGINT 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);
ALTER TABLE reviews ADD COLUMN IF NOT EXISTS review_type VARCHAR(20); -- 'beach', 'restaurant', 'market', 'adventure'

-- Update existing reviews to set the main_booking_id (temporary solution)
UPDATE reviews 
SET main_booking_id = booking_id 
WHERE booking_id IS NOT NULL AND main_booking_id IS NULL;

-- Set review_type based on what the review is for
UPDATE reviews 
SET review_type = 'beach' 
WHERE beach_place_id IS NOT NULL AND review_type IS NULL;

-- 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_status ON main_bookings(status);
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);
CREATE INDEX IF NOT EXISTS idx_reviews_review_type ON reviews(review_type);
```

## Backend Updates

The backend APIs for popular items have been updated to use real data instead of fake data:

1. **Popular Restaurants**: Now calculates popularity based on review count and average rating
2. **Popular Markets**: Updated to use the new booking system for popularity calculation
3. **Popular Adventures**: Updated to use the new booking system for popularity calculation
4. **Popular Beaches**: Already using real data

## Implementation Steps

1. Run the migration scripts in order:
   - `07_drop_old_bookings.sql`
   - `06_new_booking_system.sql`
   - `09_complete_booking_migration.sql`

2. Update the backend code to work with the new booking system

3. Test all APIs to ensure they return real data instead of fake data

4. Implement data migration for existing bookings (separate task)

## Benefits

1. **Unified Booking System**: All types of bookings are now handled in a consistent manner
2. **Better Data Integrity**: Foreign key relationships ensure data consistency
3. **Improved Performance**: Proper indexing and normalized structure
4. **Real Data**: Popular items now show real popularity metrics instead of fake data
5. **Scalability**: The new structure can easily accommodate new types of bookings