-- Targeted migration script to change only specific key tables from numeric to UUID
-- This script will:
-- 1. Add new UUID columns to the specified key tables
-- 2. Populate the UUID columns with generated UUIDs
-- 3. Update foreign key references to use UUIDs for these key tables
-- 4. Drop old numeric columns in these key tables
-- 5. Rename new UUID columns to original names

-- NOTE: This migration only affects the following tables:
-- public.adventures, public.beach_places, public.bookings, public.companies, 
-- public.markets, public.market_items, public.restaurants, public.restaurant_items, 
-- public.reviews, public.users

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 1. Add new UUID columns to the specified key tables

-- Users table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'user_uuid') THEN
        ALTER TABLE users ADD COLUMN user_uuid UUID DEFAULT uuid_generate_v4();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'users_user_uuid_key') THEN
        ALTER TABLE users ADD CONSTRAINT users_user_uuid_key UNIQUE (user_uuid);
    END IF;
END $$;

-- Companies table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'companies' AND column_name = 'company_uuid') THEN
        ALTER TABLE companies ADD COLUMN company_uuid UUID DEFAULT uuid_generate_v4();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'companies_company_uuid_key') THEN
        ALTER TABLE companies ADD CONSTRAINT companies_company_uuid_key UNIQUE (company_uuid);
    END IF;
END $$;

-- Beach places table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places' AND column_name = 'beach_place_uuid') THEN
        ALTER TABLE beach_places ADD COLUMN beach_place_uuid UUID DEFAULT uuid_generate_v4();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'beach_places_beach_place_uuid_key') THEN
        ALTER TABLE beach_places ADD CONSTRAINT beach_places_beach_place_uuid_key UNIQUE (beach_place_uuid);
    END IF;
END $$;

-- Restaurants table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'restaurant_uuid') THEN
        ALTER TABLE restaurants ADD COLUMN restaurant_uuid UUID DEFAULT uuid_generate_v4();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'restaurants_restaurant_uuid_key') THEN
        ALTER TABLE restaurants ADD CONSTRAINT restaurants_restaurant_uuid_key UNIQUE (restaurant_uuid);
    END IF;
END $$;

-- Restaurant items table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurant_items' AND column_name = 'item_uuid') THEN
        ALTER TABLE restaurant_items ADD COLUMN item_uuid UUID DEFAULT uuid_generate_v4();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'restaurant_items_item_uuid_key') THEN
        ALTER TABLE restaurant_items ADD CONSTRAINT restaurant_items_item_uuid_key UNIQUE (item_uuid);
    END IF;
END $$;

-- Markets table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'market_uuid') THEN
        ALTER TABLE markets ADD COLUMN market_uuid UUID DEFAULT uuid_generate_v4();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'markets_market_uuid_key') THEN
        ALTER TABLE markets ADD CONSTRAINT markets_market_uuid_key UNIQUE (market_uuid);
    END IF;
END $$;

-- Market items table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'market_items' AND column_name = 'item_uuid') THEN
        ALTER TABLE market_items ADD COLUMN item_uuid UUID DEFAULT uuid_generate_v4();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'market_items_item_uuid_key') THEN
        ALTER TABLE market_items ADD CONSTRAINT market_items_item_uuid_key UNIQUE (item_uuid);
    END IF;
END $$;

-- Adventures table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'adventure_uuid') THEN
        ALTER TABLE adventures ADD COLUMN adventure_uuid UUID DEFAULT uuid_generate_v4();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'adventures_adventure_uuid_key') THEN
        ALTER TABLE adventures ADD CONSTRAINT adventures_adventure_uuid_key UNIQUE (adventure_uuid);
    END IF;
END $$;

-- Bookings table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'bookings' AND column_name = 'booking_uuid') THEN
        ALTER TABLE bookings ADD COLUMN booking_uuid UUID DEFAULT uuid_generate_v4();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'bookings_booking_uuid_key') THEN
        ALTER TABLE bookings ADD CONSTRAINT bookings_booking_uuid_key UNIQUE (booking_uuid);
    END IF;
END $$;

-- Reviews table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'review_uuid') THEN
        ALTER TABLE reviews ADD COLUMN review_uuid UUID DEFAULT uuid_generate_v4();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'reviews_review_uuid_key') THEN
        ALTER TABLE reviews ADD CONSTRAINT reviews_review_uuid_key UNIQUE (review_uuid);
    END IF;
END $$;

-- 2. Update foreign key references to use UUIDs for the key tables

-- Update foreign keys in email_verification_tokens table (references users)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'email_verification_tokens' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE email_verification_tokens ADD COLUMN user_uuid_fk UUID;
    END IF;
END $$;

-- Update foreign keys in user_companies table (references users and companies)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'user_companies' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE user_companies ADD COLUMN user_uuid_fk UUID;
    END IF;
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'user_companies' AND column_name = 'company_uuid_fk') THEN
        ALTER TABLE user_companies ADD COLUMN company_uuid_fk UUID;
    END IF;
END $$;

-- Update foreign keys in beach_places table (references companies)
-- NOTE: We'll update the existing company_id column later, not add a new one

-- Update foreign keys in beach_places_photos table (references beach_places)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places_photos' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE beach_places_photos ADD COLUMN beach_place_uuid_fk UUID;
    END IF;
END $$;

-- Update foreign keys in beach_places_schedules table (references beach_places)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places_schedules' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE beach_places_schedules ADD COLUMN beach_place_uuid_fk UUID;
    END IF;
END $$;

-- Update foreign keys in beach_place_terrains table (references beach_places)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_place_terrains' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE beach_place_terrains ADD COLUMN beach_place_uuid_fk UUID;
    END IF;
END $$;

-- Update foreign keys in restaurants table (references companies and beach_places)
-- NOTE: We'll update the existing company_id and beach_place_id columns later, not add new ones

-- NOTE: restaurant_categories is not a key table and should keep numeric IDs
-- Update foreign keys in restaurant_items table (references restaurants and restaurant_categories)
-- NOTE: We'll update the existing restaurant_id column later, not add a new one
-- NOTE: category_id remains as numeric foreign key to restaurant_categories (no change needed)

-- Update foreign keys in markets table (references companies and beach_places)
-- NOTE: We'll update the existing company_id and beach_place_id columns later, not add new ones

-- NOTE: market_categories is not a key table and should keep numeric IDs
-- Update foreign keys in market_items table (references markets and market_categories)
-- NOTE: We'll update the existing market_id column later, not add a new one
-- NOTE: category_id remains as numeric foreign key to market_categories (no change needed)

-- Update foreign keys in adventures table (references companies and beach_places)
-- NOTE: We'll update the existing company_id and beach_place_id columns later, not add new ones

-- Update foreign keys in adventure_schedules table (references adventures)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventure_schedules' AND column_name = 'adventure_uuid_fk') THEN
        ALTER TABLE adventure_schedules ADD COLUMN adventure_uuid_fk UUID;
    END IF;
END $$;

-- Update foreign keys in bookings table (references users, beach_places, and beach_place_terrains)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'bookings' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE bookings ADD COLUMN user_uuid_fk UUID;
    END IF;
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'bookings' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE bookings ADD COLUMN beach_place_uuid_fk UUID;
    END IF;
END $$;

-- Update foreign keys in reviews table (references users, beach_places and bookings)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE reviews ADD COLUMN user_uuid_fk UUID;
    END IF;
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE reviews ADD COLUMN beach_place_uuid_fk UUID;
    END IF;
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'booking_uuid_fk') THEN
        ALTER TABLE reviews ADD COLUMN booking_uuid_fk UUID;
    END IF;
END $$;

-- Update foreign keys in user_wallets table (references users)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'user_wallets' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE user_wallets ADD COLUMN user_uuid_fk UUID;
    END IF;
END $$;

-- 3. Populate the new UUID foreign key columns with matching UUIDs

-- Populate foreign keys in email_verification_tokens table
UPDATE email_verification_tokens e SET user_uuid_fk = (SELECT user_uuid FROM users WHERE user_id = e.user_id);

-- Populate foreign keys in user_companies table
UPDATE user_companies uc SET user_uuid_fk = (SELECT user_uuid FROM users WHERE user_id = uc.user_id);
UPDATE user_companies uc SET company_uuid_fk = (SELECT company_uuid FROM companies WHERE company_id = uc.company_id);

-- Populate foreign keys in beach_places_photos table
UPDATE beach_places_photos bpp SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = bpp.beach_place_id);

-- Populate foreign keys in beach_places_schedules table
UPDATE beach_places_schedules bps SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = bps.beach_place_id);

-- Populate foreign keys in beach_place_terrains table
UPDATE beach_place_terrains bpt SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = bpt.beach_place_id);

-- NOTE: restaurant_categories is not a key table and should keep numeric IDs
-- Populate foreign keys in restaurant_items table
-- No change for category_id, it remains numeric and already has the correct values

-- NOTE: market_categories is not a key table and should keep numeric IDs
-- Populate foreign keys in market_items table
-- No change for category_id, it remains numeric and already has the correct values

-- Populate foreign keys in adventure_schedules table
UPDATE adventure_schedules a_s SET adventure_uuid_fk = (SELECT adventure_uuid FROM adventures WHERE adventure_id = a_s.adventure_id);

-- Populate foreign keys in bookings table
UPDATE bookings b SET user_uuid_fk = (SELECT user_uuid FROM users WHERE user_id = b.user_id);
UPDATE bookings b SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = b.beach_place_id);

-- Populate foreign keys in reviews table
UPDATE reviews r SET user_uuid_fk = (SELECT user_uuid FROM users WHERE user_id = r.user_id);
UPDATE reviews r SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = r.beach_place_id);
UPDATE reviews r SET booking_uuid_fk = (SELECT booking_uuid FROM bookings WHERE booking_id = r.booking_id);

-- Populate foreign keys in user_wallets table
UPDATE user_wallets uw SET user_uuid_fk = (SELECT user_uuid FROM users WHERE user_id = uw.user_id);

-- 4. Drop old numeric primary key columns and foreign key columns in key tables only

-- Drop foreign key constraints that reference users table first (these will be recreated with UUID references)
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'email_verification_tokens_user_id_fkey') THEN
        ALTER TABLE email_verification_tokens DROP CONSTRAINT email_verification_tokens_user_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'user_companies_user_id_fkey') THEN
        ALTER TABLE user_companies DROP CONSTRAINT user_companies_user_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'bookings_user_id_fkey') THEN
        ALTER TABLE bookings DROP CONSTRAINT bookings_user_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'reviews_user_id_fkey') THEN
        ALTER TABLE reviews DROP CONSTRAINT reviews_user_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'user_wallets_user_id_fkey') THEN
        ALTER TABLE user_wallets DROP CONSTRAINT user_wallets_user_id_fkey;
    END IF;
END $$;

-- Drop primary key constraint in users table
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'users_pkey') THEN
        ALTER TABLE users DROP CONSTRAINT users_pkey;
    END IF;
END $$;

-- Drop foreign key constraints that reference companies table first (these will be recreated with UUID references)
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'user_companies_company_id_fkey') THEN
        ALTER TABLE user_companies DROP CONSTRAINT user_companies_company_id_fkey;
    END IF;
    -- Drop foreign key constraints in other tables that reference companies table
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'beach_places_company_id_fkey') THEN
        ALTER TABLE beach_places DROP CONSTRAINT beach_places_company_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'restaurants_company_id_fkey') THEN
        ALTER TABLE restaurants DROP CONSTRAINT restaurants_company_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'markets_company_id_fkey') THEN
        ALTER TABLE markets DROP CONSTRAINT markets_company_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'adventures_company_id_fkey') THEN
        ALTER TABLE adventures DROP CONSTRAINT adventures_company_id_fkey;
    END IF;
END $$;

-- Drop primary key constraint in companies table
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'companies_pkey') THEN
        ALTER TABLE companies DROP CONSTRAINT companies_pkey;
    END IF;
END $$;

-- Drop foreign key constraints that reference beach_places table first (these will be recreated with UUID references)
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'beach_places_photos_beach_place_id_fkey') THEN
        ALTER TABLE beach_places_photos DROP CONSTRAINT beach_places_photos_beach_place_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'beach_places_schedules_beach_place_id_fkey') THEN
        ALTER TABLE beach_places_schedules DROP CONSTRAINT beach_places_schedules_beach_place_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'beach_place_terrains_beach_place_id_fkey') THEN
        ALTER TABLE beach_place_terrains DROP CONSTRAINT beach_place_terrains_beach_place_id_fkey;
    END IF;
    -- Drop foreign key constraints in other tables that reference beach_places table
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'restaurants_beach_place_id_fkey') THEN
        ALTER TABLE restaurants DROP CONSTRAINT restaurants_beach_place_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'markets_beach_place_id_fkey') THEN
        ALTER TABLE markets DROP CONSTRAINT markets_beach_place_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'adventures_beach_place_id_fkey') THEN
        ALTER TABLE adventures DROP CONSTRAINT adventures_beach_place_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'bookings_beach_place_id_fkey') THEN
        ALTER TABLE bookings DROP CONSTRAINT bookings_beach_place_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'reviews_beach_place_id_fkey') THEN
        ALTER TABLE reviews DROP CONSTRAINT reviews_beach_place_id_fkey;
    END IF;
END $$;

-- Drop primary key constraint in beach_places table
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'beach_places_pkey') THEN
        ALTER TABLE beach_places DROP CONSTRAINT beach_places_pkey;
    END IF;
END $$;

-- Drop foreign key constraints that reference restaurants table first (these will be recreated with UUID references)
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'restaurant_categories_restaurant_id_fkey') THEN
        ALTER TABLE restaurant_categories DROP CONSTRAINT restaurant_categories_restaurant_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'restaurant_items_restaurant_id_fkey') THEN
        ALTER TABLE restaurant_items DROP CONSTRAINT restaurant_items_restaurant_id_fkey;
    END IF;
END $$;

-- Drop primary key constraint in restaurants table
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'restaurants_pkey') THEN
        ALTER TABLE restaurants DROP CONSTRAINT restaurants_pkey;
    END IF;
END $$;

-- Drop primary key constraint in restaurant_items table
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'restaurant_items_pkey') THEN
        ALTER TABLE restaurant_items DROP CONSTRAINT restaurant_items_pkey;
    END IF;
END $$;

-- Drop foreign key constraints that reference markets table first (these will be recreated with UUID references)
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'market_categories_market_id_fkey') THEN
        ALTER TABLE market_categories DROP CONSTRAINT market_categories_market_id_fkey;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'market_items_market_id_fkey') THEN
        ALTER TABLE market_items DROP CONSTRAINT market_items_market_id_fkey;
    END IF;
END $$;

-- Drop primary key constraint in markets table
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'markets_pkey') THEN
        ALTER TABLE markets DROP CONSTRAINT markets_pkey;
    END IF;
END $$;

-- Drop primary key constraint in market_items table
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'market_items_pkey') THEN
        ALTER TABLE market_items DROP CONSTRAINT market_items_pkey;
    END IF;
END $$;

-- Drop foreign key constraints that reference adventures table first (these will be recreated with UUID references)
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'adventure_schedules_adventure_id_fkey') THEN
        ALTER TABLE adventure_schedules DROP CONSTRAINT adventure_schedules_adventure_id_fkey;
    END IF;
END $$;

-- Drop primary key constraint in adventures table
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'adventures_pkey') THEN
        ALTER TABLE adventures DROP CONSTRAINT adventures_pkey;
    END IF;
END $$;

-- Drop foreign key constraints that reference bookings table first (these will be recreated with UUID references)
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'reviews_booking_id_fkey') THEN
        ALTER TABLE reviews DROP CONSTRAINT reviews_booking_id_fkey;
    END IF;
END $$;

-- Drop primary key constraint in bookings table
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'bookings_pkey') THEN
        ALTER TABLE bookings DROP CONSTRAINT bookings_pkey;
    END IF;
END $$;

-- Drop primary key constraint in reviews table
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'reviews_pkey') THEN
        ALTER TABLE reviews DROP CONSTRAINT reviews_pkey;
    END IF;
END $$;

-- Drop foreign key constraints that reference user_wallets table first (these will be recreated with UUID references)
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'wallet_transactions_wallet_id_fkey') THEN
        ALTER TABLE wallet_transactions DROP CONSTRAINT wallet_transactions_wallet_id_fkey;
    END IF;
END $$;

-- Drop primary key constraint in user_wallets table
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'user_wallets_pkey') THEN
        ALTER TABLE user_wallets DROP CONSTRAINT user_wallets_pkey;
    END IF;
END $$;

-- Now drop the actual columns
-- Drop primary keys and foreign keys in users table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'user_id') THEN
        ALTER TABLE users DROP COLUMN user_id;
    END IF;
END $$;

-- Drop primary keys and foreign keys in companies table
-- NOTE: Do not drop company_id yet, it will be dropped after renaming in section 5

-- Drop primary keys and foreign keys in beach_places table
-- NOTE: Do not drop beach_place_id yet, it will be dropped after renaming in section 5
-- NOTE: Do not drop company_id, terrain_type_id, country_id as they reference system tables with numeric IDs

-- Drop primary keys and foreign keys in restaurants table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'restaurant_id') THEN
        ALTER TABLE restaurants DROP COLUMN restaurant_id;
    END IF;
END $$;
-- NOTE: Do not drop beach_place_id, company_id, category_id, currency_id as they reference other tables
-- beach_place_id references beach_places (key table) - will be handled in section 7
-- company_id references companies (key table) - will be handled in section 7
-- category_id references restaurant_categories (system table) - keep numeric
-- currency_id references currencies (system table) - keep numeric

-- Drop primary keys and foreign keys in restaurant_items table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurant_items' AND column_name = 'item_id') THEN
        ALTER TABLE restaurant_items DROP COLUMN item_id;
    END IF;
END $$;
-- NOTE: Do not drop restaurant_id, category_id, currency_id as they reference other tables
-- restaurant_id references restaurants (key table) - will be handled in section 7
-- category_id references restaurant_categories (system table) - keep numeric
-- currency_id references currencies (system table) - keep numeric

-- Drop primary keys and foreign keys in markets table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'market_id') THEN
        ALTER TABLE markets DROP COLUMN market_id;
    END IF;
END $$;
-- NOTE: Do not drop beach_place_id, company_id, category_id, currency_id as they reference other tables
-- beach_place_id references beach_places (key table) - will be handled in section 7
-- company_id references companies (key table) - will be handled in section 7
-- category_id references market_categories (system table) - keep numeric
-- currency_id references currencies (system table) - keep numeric

-- Drop primary keys and foreign keys in market_items table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'market_items' AND column_name = 'item_id') THEN
        ALTER TABLE market_items DROP COLUMN item_id;
    END IF;
END $$;
-- NOTE: Do not drop market_id, category_id, currency_id as they reference other tables
-- market_id references markets (key table) - will be handled in section 7
-- category_id references market_categories (system table) - keep numeric
-- currency_id references currencies (system table) - keep numeric

-- Drop primary keys and foreign keys in adventures table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'adventure_id') THEN
        ALTER TABLE adventures DROP COLUMN adventure_id;
    END IF;
END $$;
-- NOTE: Do not drop beach_place_id, company_id, currency_id as they reference other tables
-- beach_place_id references beach_places (key table) - will be handled in section 7
-- company_id references companies (key table) - will be handled in section 7
-- currency_id references currencies (system table) - keep numeric

-- Drop primary keys and foreign keys in bookings table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'bookings' AND column_name = 'booking_id') THEN
        ALTER TABLE bookings DROP COLUMN booking_id;
    END IF;
END $$;
-- NOTE: Do not drop user_id, beach_place_id, beach_place_terrain_id, currency_id as they reference other tables
-- user_id references users (key table) - will be handled in section 6
-- beach_place_id references beach_places (key table) - will be handled in section 6
-- beach_place_terrain_id references beach_place_terrains (system table) - keep numeric
-- currency_id references currencies (system table) - keep numeric

-- Drop primary keys and foreign keys in reviews table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'review_id') THEN
        ALTER TABLE reviews DROP COLUMN review_id;
    END IF;
END $$;
-- NOTE: Do not drop user_id, beach_place_id, booking_id as they reference other tables
-- user_id references users (key table) - will be handled in section 6
-- beach_place_id references beach_places (key table) - will be handled in section 6
-- booking_id references bookings (key table) - will be handled in section 6

-- Drop primary keys and foreign keys in user_wallets table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'user_wallets' AND column_name = 'wallet_id') THEN
        ALTER TABLE user_wallets DROP COLUMN wallet_id;
    END IF;
END $$;
-- NOTE: Do not drop user_id, currency_id as they reference other tables
-- user_id references users (key table) - will be handled in section 6
-- currency_id references currencies (system table) - keep numeric

-- NOTE: We do not drop columns that reference system tables (countries, currencies, languages, etc.)
-- as they should keep their numeric IDs according to the targeted approach

-- 5. Rename new UUID columns to original names and set as primary keys

-- Rename and set primary keys in users table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'user_uuid') THEN
        ALTER TABLE users RENAME COLUMN user_uuid TO user_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'users_pkey') THEN
        ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);
    END IF;
END $$;

-- Rename and set primary keys in companies table
-- NOTE: Fixed the incorrect DROP COLUMN statement
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'companies' AND column_name = 'company_uuid') THEN
        ALTER TABLE companies RENAME COLUMN company_uuid TO company_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'companies_pkey') THEN
        ALTER TABLE companies ADD CONSTRAINT companies_pkey PRIMARY KEY (company_id);
    END IF;
END $$;

-- Rename and set primary keys in beach_places table
-- NOTE: Fixed the incorrect DROP COLUMN statement
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places' AND column_name = 'beach_place_uuid') THEN
        ALTER TABLE beach_places RENAME COLUMN beach_place_uuid TO beach_place_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'beach_places_pkey') THEN
        ALTER TABLE beach_places ADD CONSTRAINT beach_places_pkey PRIMARY KEY (beach_place_id);
    END IF;
END $$;

-- Rename and set primary keys in restaurants table
-- NOTE: Fixed the incorrect DROP COLUMN statement
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'restaurant_uuid') THEN
        ALTER TABLE restaurants RENAME COLUMN restaurant_uuid TO restaurant_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'restaurants_pkey') THEN
        ALTER TABLE restaurants ADD CONSTRAINT restaurants_pkey PRIMARY KEY (restaurant_id);
    END IF;
END $$;

-- NOTE: restaurant_categories is not a key table and should keep numeric IDs
-- Rename and set primary keys in restaurant_items table
-- NOTE: Fixed the incorrect DROP COLUMN statement
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurant_items' AND column_name = 'item_uuid') THEN
        ALTER TABLE restaurant_items RENAME COLUMN item_uuid TO item_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'restaurant_items_pkey') THEN
        ALTER TABLE restaurant_items ADD CONSTRAINT restaurant_items_pkey PRIMARY KEY (item_id);
    END IF;
END $$;

-- Rename and set primary keys in markets table
-- NOTE: Fixed the incorrect DROP COLUMN statement
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'market_uuid') THEN
        ALTER TABLE markets RENAME COLUMN market_uuid TO market_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'markets_pkey') THEN
        ALTER TABLE markets ADD CONSTRAINT markets_pkey PRIMARY KEY (market_id);
    END IF;
END $$;

-- NOTE: market_categories is not a key table and should keep numeric IDs
-- Rename and set primary keys in market_items table
-- NOTE: Fixed the incorrect DROP COLUMN statement
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'market_items' AND column_name = 'item_uuid') THEN
        ALTER TABLE market_items RENAME COLUMN item_uuid TO item_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'market_items_pkey') THEN
        ALTER TABLE market_items ADD CONSTRAINT market_items_pkey PRIMARY KEY (item_id);
    END IF;
END $$;

-- Rename and set primary keys in adventures table
-- NOTE: Fixed the incorrect DROP COLUMN statement
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'adventure_uuid') THEN
        ALTER TABLE adventures RENAME COLUMN adventure_uuid TO adventure_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'adventures_pkey') THEN
        ALTER TABLE adventures ADD CONSTRAINT adventures_pkey PRIMARY KEY (adventure_id);
    END IF;
END $$;

-- Rename and set primary keys in bookings table
-- NOTE: Fixed the incorrect DROP COLUMN statement
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'bookings' AND column_name = 'booking_uuid') THEN
        ALTER TABLE bookings RENAME COLUMN booking_uuid TO booking_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'bookings_pkey') THEN
        ALTER TABLE bookings ADD CONSTRAINT bookings_pkey PRIMARY KEY (booking_id);
    END IF;
END $$;

-- Rename and set primary keys in reviews table
-- NOTE: Fixed the incorrect DROP COLUMN statement
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'review_uuid') THEN
        ALTER TABLE reviews RENAME COLUMN review_uuid TO review_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'reviews_pkey') THEN
        ALTER TABLE reviews ADD CONSTRAINT reviews_pkey PRIMARY KEY (review_id);
    END IF;
END $$;

-- 6. Update foreign key columns in tables that reference key tables to store UUIDs

-- For tables that reference key tables with UUIDs, we need to:
-- 1. Drop the old numeric foreign key columns that reference key tables
-- 2. Rename the temporary UUID columns to the original names
-- 3. Add new foreign key constraints for key tables

-- Update user_companies table
-- Drop the old numeric foreign key columns (only those that reference key tables)
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'user_companies' AND column_name = 'user_id') THEN
        ALTER TABLE user_companies DROP COLUMN user_id;  -- references users (key table)
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'user_companies' AND column_name = 'company_id') THEN
        ALTER TABLE user_companies DROP COLUMN company_id;  -- references companies (key table)
    END IF;
END $$;

-- Rename the UUID columns to the original names
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'user_companies' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE user_companies RENAME COLUMN user_uuid_fk TO user_id;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'user_companies' AND column_name = 'company_uuid_fk') THEN
        ALTER TABLE user_companies RENAME COLUMN company_uuid_fk TO company_id;
    END IF;
END $$;

-- Add new foreign key constraints
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_user_companies_user') THEN
        ALTER TABLE user_companies ADD CONSTRAINT fk_user_companies_user FOREIGN KEY (user_id) REFERENCES users(user_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_user_companies_company') THEN
        ALTER TABLE user_companies ADD CONSTRAINT fk_user_companies_company FOREIGN KEY (company_id) REFERENCES companies(company_id);
    END IF;
END $$;

-- Update beach_places_photos table
-- Drop the old numeric foreign key column (only those that reference key tables)
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places_photos' AND column_name = 'beach_place_id') THEN
        ALTER TABLE beach_places_photos DROP COLUMN beach_place_id;  -- references beach_places (key table)
    END IF;
END $$;

-- Rename the UUID column to the original name
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places_photos' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE beach_places_photos RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
    END IF;
END $$;

-- Add new foreign key constraint
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_beach_photos_beach_place') THEN
        ALTER TABLE beach_places_photos ADD CONSTRAINT fk_beach_photos_beach_place FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- Update beach_places_schedules table
-- Drop the old numeric foreign key column (only those that reference key tables)
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places_schedules' AND column_name = 'beach_place_id') THEN
        ALTER TABLE beach_places_schedules DROP COLUMN beach_place_id;  -- references beach_places (key table)
    END IF;
END $$;

-- Rename the UUID column to the original name
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places_schedules' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE beach_places_schedules RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
    END IF;
END $$;

-- Add new foreign key constraint
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_beach_schedules_beach_place') THEN
        ALTER TABLE beach_places_schedules ADD CONSTRAINT fk_beach_schedules_beach_place FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- Update beach_place_terrains table
-- Drop the old numeric foreign key column (only those that reference key tables)
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_place_terrains' AND column_name = 'beach_place_id') THEN
        ALTER TABLE beach_place_terrains DROP COLUMN beach_place_id;  -- references beach_places (key table)
    END IF;
END $$;

-- Rename the UUID column to the original name
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_place_terrains' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE beach_place_terrains RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
    END IF;
END $$;

-- Add new foreign key constraint
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_beach_terrains_beach_place') THEN
        ALTER TABLE beach_place_terrains ADD CONSTRAINT fk_beach_terrains_beach_place FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- Update adventure_schedules table
-- Drop the old numeric foreign key column (only those that reference key tables)
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventure_schedules' AND column_name = 'adventure_id') THEN
        ALTER TABLE adventure_schedules DROP COLUMN adventure_id;  -- references adventures (key table)
    END IF;
END $$;

-- Rename the UUID column to the original name
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventure_schedules' AND column_name = 'adventure_uuid_fk') THEN
        ALTER TABLE adventure_schedules RENAME COLUMN adventure_uuid_fk TO adventure_id;
    END IF;
END $$;

-- Add new foreign key constraint
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_adventure_schedules_adventure') THEN
        ALTER TABLE adventure_schedules ADD CONSTRAINT fk_adventure_schedules_adventure FOREIGN KEY (adventure_id) REFERENCES adventures(adventure_id);
    END IF;
END $$;

-- Update bookings table
-- Drop the old numeric foreign key columns (only those that reference key tables)
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'bookings' AND column_name = 'user_id') THEN
        ALTER TABLE bookings DROP COLUMN user_id;  -- references users (key table)
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'bookings' AND column_name = 'beach_place_id') THEN
        ALTER TABLE bookings DROP COLUMN beach_place_id;  -- references beach_places (key table)
    END IF;
END $$;

-- Rename the UUID columns to the original names
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'bookings' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE bookings RENAME COLUMN user_uuid_fk TO user_id;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'bookings' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE bookings RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
    END IF;
END $$;

-- Add new foreign key constraints
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_bookings_user') THEN
        ALTER TABLE bookings ADD CONSTRAINT fk_bookings_user FOREIGN KEY (user_id) REFERENCES users(user_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_bookings_beach_place') THEN
        ALTER TABLE bookings ADD CONSTRAINT fk_bookings_beach_place FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- Update reviews table
-- Drop the old numeric foreign key columns (only those that reference key tables)
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'user_id') THEN
        ALTER TABLE reviews DROP COLUMN user_id;  -- references users (key table)
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'beach_place_id') THEN
        ALTER TABLE reviews DROP COLUMN beach_place_id;  -- references beach_places (key table)
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'booking_id') THEN
        ALTER TABLE reviews DROP COLUMN booking_id;  -- references bookings (key table)
    END IF;
END $$;

-- Rename the UUID columns to the original names
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE reviews RENAME COLUMN user_uuid_fk TO user_id;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE reviews RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'booking_uuid_fk') THEN
        ALTER TABLE reviews RENAME COLUMN booking_uuid_fk TO booking_id;
    END IF;
END $$;

-- Add new foreign key constraints
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_reviews_user') THEN
        ALTER TABLE reviews ADD CONSTRAINT fk_reviews_user FOREIGN KEY (user_id) REFERENCES users(user_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_reviews_beach_place') THEN
        ALTER TABLE reviews ADD CONSTRAINT fk_reviews_beach_place FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_reviews_booking') THEN
        ALTER TABLE reviews ADD CONSTRAINT fk_reviews_booking FOREIGN KEY (booking_id) REFERENCES bookings(booking_id);
    END IF;
END $$;

-- Update user_wallets table
-- Drop the old numeric foreign key column (only those that reference key tables)
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'user_wallets' AND column_name = 'user_id') THEN
        ALTER TABLE user_wallets DROP COLUMN user_id;  -- references users (key table)
    END IF;
END $$;

-- Rename the UUID column to the original name
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'user_wallets' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE user_wallets RENAME COLUMN user_uuid_fk TO user_id;
    END IF;
END $$;

-- Add new foreign key constraint
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_user_wallets_user') THEN
        ALTER TABLE user_wallets ADD CONSTRAINT fk_user_wallets_user FOREIGN KEY (user_id) REFERENCES users(user_id);
    END IF;
END $$;

-- Update email_verification_tokens table
-- Drop the old numeric foreign key column (only those that reference key tables)
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'email_verification_tokens' AND column_name = 'user_id') THEN
        ALTER TABLE email_verification_tokens DROP COLUMN user_id;  -- references users (key table)
    END IF;
END $$;

-- Rename the UUID column to the original name
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'email_verification_tokens' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE email_verification_tokens RENAME COLUMN user_uuid_fk TO user_id;
    END IF;
END $$;

-- Add new foreign key constraint
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_email_tokens_user') THEN
        ALTER TABLE email_verification_tokens ADD CONSTRAINT fk_email_tokens_user FOREIGN KEY (user_id) REFERENCES users(user_id);
    END IF;
END $$;

-- 7. For foreign key columns that reference system tables (keep numeric), we need to:
-- For tables that have foreign keys to BOTH key tables and system tables:
-- 1. Add temporary UUID columns for key table references
-- 2. Populate them with the corresponding UUIDs
-- 3. Drop the old numeric foreign key columns for key tables only
-- 4. Rename the temporary UUID columns to the original names
-- 5. Add new foreign key constraints for key tables

-- First, we need to add backup columns to store the original numeric foreign key values
-- This is necessary because we need to match the original values with the new UUID values

-- Add backup columns for beach_places (company_id references companies - key table)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places' AND column_name = 'company_id_backup') THEN
        ALTER TABLE beach_places ADD COLUMN company_id_backup BIGINT;
    END IF;
END $$;

-- Add backup columns for restaurants (company_id references companies - key table, beach_place_id references beach_places - key table)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'company_id_backup') THEN
        ALTER TABLE restaurants ADD COLUMN company_id_backup BIGINT;
    END IF;
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'beach_place_id_backup') THEN
        ALTER TABLE restaurants ADD COLUMN beach_place_id_backup BIGINT;
    END IF;
END $$;

-- Add backup columns for markets (company_id references companies - key table, beach_place_id references beach_places - key table)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'company_id_backup') THEN
        ALTER TABLE markets ADD COLUMN company_id_backup BIGINT;
    END IF;
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'beach_place_id_backup') THEN
        ALTER TABLE markets ADD COLUMN beach_place_id_backup BIGINT;
    END IF;
END $$;

-- Add backup columns for adventures (company_id references companies - key table, beach_place_id references beach_places - key table)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'company_id_backup') THEN
        ALTER TABLE adventures ADD COLUMN company_id_backup BIGINT;
    END IF;
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'beach_place_id_backup') THEN
        ALTER TABLE adventures ADD COLUMN beach_place_id_backup BIGINT;
    END IF;
END $$;

-- Populate the backup columns with the original foreign key values
UPDATE beach_places SET company_id_backup = company_id;
UPDATE restaurants SET company_id_backup = company_id, beach_place_id_backup = beach_place_id;
UPDATE markets SET company_id_backup = company_id, beach_place_id_backup = beach_place_id;
UPDATE adventures SET company_id_backup = company_id, beach_place_id_backup = beach_place_id;

-- Update beach_places table (company_id references companies - key table, others reference system tables)
-- Add temporary column for UUID
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places' AND column_name = 'company_uuid_temp') THEN
        ALTER TABLE beach_places ADD COLUMN company_uuid_temp UUID;
    END IF;
END $$;
-- Populate the temporary column with the corresponding UUIDs using the backup column
-- We need to get the UUID from the companies table where the company_id matches our backup
UPDATE beach_places bp SET company_uuid_temp = (SELECT company_id FROM companies WHERE company_id = bp.company_id_backup);
-- Drop the old numeric foreign key column for key table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places' AND column_name = 'company_id') THEN
        ALTER TABLE beach_places DROP COLUMN company_id;
    END IF;
END $$;
-- Rename the temporary UUID column to the original name
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places' AND column_name = 'company_uuid_temp') THEN
        ALTER TABLE beach_places RENAME COLUMN company_uuid_temp TO company_id;
    END IF;
END $$;
-- Add new foreign key constraint for key table reference
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_beach_places_company') THEN
        ALTER TABLE beach_places ADD CONSTRAINT fk_beach_places_company FOREIGN KEY (company_id) REFERENCES companies(company_id);
    END IF;
END $$;

-- Update restaurants table (company_id references companies - key table, beach_place_id references beach_places - key table)
-- Add temporary columns for UUIDs
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'company_uuid_temp') THEN
        ALTER TABLE restaurants ADD COLUMN company_uuid_temp UUID;
    END IF;
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'beach_place_uuid_temp') THEN
        ALTER TABLE restaurants ADD COLUMN beach_place_uuid_temp UUID;
    END IF;
END $$;
-- Populate the temporary columns with the corresponding UUIDs using the backup columns
UPDATE restaurants r SET company_uuid_temp = (SELECT company_id FROM companies WHERE company_id = r.company_id_backup);
UPDATE restaurants r SET beach_place_uuid_temp = (SELECT beach_place_id FROM beach_places WHERE beach_place_id = r.beach_place_id_backup);
-- Drop the old numeric foreign key columns for key tables
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'company_id') THEN
        ALTER TABLE restaurants DROP COLUMN company_id;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'beach_place_id') THEN
        ALTER TABLE restaurants DROP COLUMN beach_place_id;
    END IF;
END $$;
-- Rename the temporary UUID columns to the original names
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'company_uuid_temp') THEN
        ALTER TABLE restaurants RENAME COLUMN company_uuid_temp TO company_id;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'beach_place_uuid_temp') THEN
        ALTER TABLE restaurants RENAME COLUMN beach_place_uuid_temp TO beach_place_id;
    END IF;
END $$;
-- Add new foreign key constraints for key table references
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_restaurants_company') THEN
        ALTER TABLE restaurants ADD CONSTRAINT fk_restaurants_company FOREIGN KEY (company_id) REFERENCES companies(company_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_restaurants_beach_place') THEN
        ALTER TABLE restaurants ADD CONSTRAINT fk_restaurants_beach_place FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- Update restaurant_items table (restaurant_id references restaurants - key table, others reference system tables)
-- Add temporary column for UUID
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurant_items' AND column_name = 'restaurant_uuid_temp') THEN
        ALTER TABLE restaurant_items ADD COLUMN restaurant_uuid_temp UUID;
    END IF;
END $$;
-- Populate the temporary column with the corresponding UUIDs
UPDATE restaurant_items ri SET restaurant_uuid_temp = (SELECT restaurant_id FROM restaurants WHERE restaurant_id = ri.restaurant_id);
-- Drop the old numeric foreign key column for key table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurant_items' AND column_name = 'restaurant_id') THEN
        ALTER TABLE restaurant_items DROP COLUMN restaurant_id;
    END IF;
END $$;
-- Rename the temporary UUID column to the original name
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurant_items' AND column_name = 'restaurant_uuid_temp') THEN
        ALTER TABLE restaurant_items RENAME COLUMN restaurant_uuid_temp TO restaurant_id;
    END IF;
END $$;
-- Add new foreign key constraint for key table reference
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_restaurant_items_restaurant') THEN
        ALTER TABLE restaurant_items ADD CONSTRAINT fk_restaurant_items_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id);
    END IF;
END $$;

-- Update markets table (company_id references companies - key table, beach_place_id references beach_places - key table)
-- Add temporary columns for UUIDs
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'company_uuid_temp') THEN
        ALTER TABLE markets ADD COLUMN company_uuid_temp UUID;
    END IF;
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'beach_place_uuid_temp') THEN
        ALTER TABLE markets ADD COLUMN beach_place_uuid_temp UUID;
    END IF;
END $$;
-- Populate the temporary columns with the corresponding UUIDs using the backup columns
UPDATE markets m SET company_uuid_temp = (SELECT company_id FROM companies WHERE company_id = m.company_id_backup);
UPDATE markets m SET beach_place_uuid_temp = (SELECT beach_place_id FROM beach_places WHERE beach_place_id = m.beach_place_id_backup);
-- Drop the old numeric foreign key columns for key tables
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'company_id') THEN
        ALTER TABLE markets DROP COLUMN company_id;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'beach_place_id') THEN
        ALTER TABLE markets DROP COLUMN beach_place_id;
    END IF;
END $$;
-- Rename the temporary UUID columns to the original names
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'company_uuid_temp') THEN
        ALTER TABLE markets RENAME COLUMN company_uuid_temp TO company_id;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'beach_place_uuid_temp') THEN
        ALTER TABLE markets RENAME COLUMN beach_place_uuid_temp TO beach_place_id;
    END IF;
END $$;
-- Add new foreign key constraints for key table references
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_markets_company') THEN
        ALTER TABLE markets ADD CONSTRAINT fk_markets_company FOREIGN KEY (company_id) REFERENCES companies(company_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_markets_beach_place') THEN
        ALTER TABLE markets ADD CONSTRAINT fk_markets_beach_place FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- Update market_items table (market_id references markets - key table, others reference system tables)
-- Add temporary column for UUID
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'market_items' AND column_name = 'market_uuid_temp') THEN
        ALTER TABLE market_items ADD COLUMN market_uuid_temp UUID;
    END IF;
END $$;
-- Populate the temporary column with the corresponding UUIDs
UPDATE market_items mi SET market_uuid_temp = (SELECT market_id FROM markets WHERE market_id = mi.market_id);
-- Drop the old numeric foreign key column for key table
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'market_items' AND column_name = 'market_id') THEN
        ALTER TABLE market_items DROP COLUMN market_id;
    END IF;
END $$;
-- Rename the temporary UUID column to the original name
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'market_items' AND column_name = 'market_uuid_temp') THEN
        ALTER TABLE market_items RENAME COLUMN market_uuid_temp TO market_id;
    END IF;
END $$;
-- Add new foreign key constraint for key table reference
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_market_items_market') THEN
        ALTER TABLE market_items ADD CONSTRAINT fk_market_items_market FOREIGN KEY (market_id) REFERENCES markets(market_id);
    END IF;
END $$;

-- Update adventures table (company_id references companies - key table, beach_place_id references beach_places - key table)
-- Add temporary columns for UUIDs
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'company_uuid_temp') THEN
        ALTER TABLE adventures ADD COLUMN company_uuid_temp UUID;
    END IF;
    IF NOT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'beach_place_uuid_temp') THEN
        ALTER TABLE adventures ADD COLUMN beach_place_uuid_temp UUID;
    END IF;
END $$;
-- Populate the temporary columns with the corresponding UUIDs using the backup columns
UPDATE adventures a SET company_uuid_temp = (SELECT company_id FROM companies WHERE company_id = a.company_id_backup);
UPDATE adventures a SET beach_place_uuid_temp = (SELECT beach_place_id FROM beach_places WHERE beach_place_id = a.beach_place_id_backup);
-- Drop the old numeric foreign key columns for key tables
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'company_id') THEN
        ALTER TABLE adventures DROP COLUMN company_id;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'beach_place_id') THEN
        ALTER TABLE adventures DROP COLUMN beach_place_id;
    END IF;
END $$;
-- Rename the temporary UUID columns to the original names
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'company_uuid_temp') THEN
        ALTER TABLE adventures RENAME COLUMN company_uuid_temp TO company_id;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'beach_place_uuid_temp') THEN
        ALTER TABLE adventures RENAME COLUMN beach_place_uuid_temp TO beach_place_id;
    END IF;
END $$;
-- Add new foreign key constraints for key table references
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_adventures_company') THEN
        ALTER TABLE adventures ADD CONSTRAINT fk_adventures_company FOREIGN KEY (company_id) REFERENCES companies(company_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_adventures_beach_place') THEN
        ALTER TABLE adventures ADD CONSTRAINT fk_adventures_beach_place FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- Drop the backup columns as they are no longer needed
DO $$ 
BEGIN
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'beach_places' AND column_name = 'company_id_backup') THEN
        ALTER TABLE beach_places DROP COLUMN company_id_backup;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'company_id_backup') THEN
        ALTER TABLE restaurants DROP COLUMN company_id_backup;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'beach_place_id_backup') THEN
        ALTER TABLE restaurants DROP COLUMN beach_place_id_backup;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'company_id_backup') THEN
        ALTER TABLE markets DROP COLUMN company_id_backup;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'beach_place_id_backup') THEN
        ALTER TABLE markets DROP COLUMN beach_place_id_backup;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'company_id_backup') THEN
        ALTER TABLE adventures DROP COLUMN company_id_backup;
    END IF;
    IF EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'beach_place_id_backup') THEN
        ALTER TABLE adventures DROP COLUMN beach_place_id_backup;
    END IF;
END $$;

-- 8. Create indexes for better performance on the key tables

-- Create indexes on foreign key columns
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_email_verification_tokens_user_id') THEN
        CREATE INDEX idx_email_verification_tokens_user_id ON email_verification_tokens(user_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_user_companies_user_id') THEN
        CREATE INDEX idx_user_companies_user_id ON user_companies(user_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_user_companies_company_id') THEN
        CREATE INDEX idx_user_companies_company_id ON user_companies(company_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_beach_places_company_id') THEN
        CREATE INDEX idx_beach_places_company_id ON beach_places(company_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_beach_places_photos_beach_place_id') THEN
        CREATE INDEX idx_beach_places_photos_beach_place_id ON beach_places_photos(beach_place_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_beach_places_schedules_beach_place_id') THEN
        CREATE INDEX idx_beach_places_schedules_beach_place_id ON beach_places_schedules(beach_place_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_beach_place_terrains_beach_place_id') THEN
        CREATE INDEX idx_beach_place_terrains_beach_place_id ON beach_place_terrains(beach_place_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_restaurants_company_id') THEN
        CREATE INDEX idx_restaurants_company_id ON restaurants(company_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_restaurants_beach_place_id') THEN
        CREATE INDEX idx_restaurants_beach_place_id ON restaurants(beach_place_id);
    END IF;
END $$;
-- NOTE: restaurant_categories.restaurant_id remains numeric
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_restaurant_categories_restaurant_id') THEN
        CREATE INDEX idx_restaurant_categories_restaurant_id ON restaurant_categories(restaurant_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_restaurant_items_restaurant_id') THEN
        CREATE INDEX idx_restaurant_items_restaurant_id ON restaurant_items(restaurant_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_markets_company_id') THEN
        CREATE INDEX idx_markets_company_id ON markets(company_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_markets_beach_place_id') THEN
        CREATE INDEX idx_markets_beach_place_id ON markets(beach_place_id);
    END IF;
END $$;
-- NOTE: market_categories.market_id remains numeric
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_market_categories_market_id') THEN
        CREATE INDEX idx_market_categories_market_id ON market_categories(market_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_market_items_market_id') THEN
        CREATE INDEX idx_market_items_market_id ON market_items(market_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_adventures_company_id') THEN
        CREATE INDEX idx_adventures_company_id ON adventures(company_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_adventures_beach_place_id') THEN
        CREATE INDEX idx_adventures_beach_place_id ON adventures(beach_place_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_adventure_schedules_adventure_id') THEN
        CREATE INDEX idx_adventure_schedules_adventure_id ON adventure_schedules(adventure_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_bookings_user_id') THEN
        CREATE INDEX idx_bookings_user_id ON bookings(user_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_bookings_beach_place_id') THEN
        CREATE INDEX idx_bookings_beach_place_id ON bookings(beach_place_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_reviews_user_id') THEN
        CREATE INDEX idx_reviews_user_id ON reviews(user_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_reviews_beach_place_id') THEN
        CREATE INDEX idx_reviews_beach_place_id ON reviews(beach_place_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_reviews_booking_id') THEN
        CREATE INDEX idx_reviews_booking_id ON reviews(booking_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'idx_user_wallets_user_id') THEN
        CREATE INDEX idx_user_wallets_user_id ON user_wallets(user_id);
    END IF;
END $$;

-- Migration completed for key tables only