-- 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
ALTER TABLE users ADD COLUMN IF NOT EXISTS user_uuid UUID DEFAULT uuid_generate_v4();
ALTER TABLE users ADD CONSTRAINT IF NOT EXISTS users_user_uuid_key UNIQUE (user_uuid);

-- Companies table
ALTER TABLE companies ADD COLUMN IF NOT EXISTS company_uuid UUID DEFAULT uuid_generate_v4();
ALTER TABLE companies ADD CONSTRAINT IF NOT EXISTS companies_company_uuid_key UNIQUE (company_uuid);

-- Beach places table
ALTER TABLE beach_places ADD COLUMN IF NOT EXISTS beach_place_uuid UUID DEFAULT uuid_generate_v4();
ALTER TABLE beach_places ADD CONSTRAINT IF NOT EXISTS beach_places_beach_place_uuid_key UNIQUE (beach_place_uuid);

-- Restaurants table
ALTER TABLE restaurants ADD COLUMN IF NOT EXISTS restaurant_uuid UUID DEFAULT uuid_generate_v4();
ALTER TABLE restaurants ADD CONSTRAINT IF NOT EXISTS restaurants_restaurant_uuid_key UNIQUE (restaurant_uuid);

-- Restaurant items table
ALTER TABLE restaurant_items ADD COLUMN IF NOT EXISTS item_uuid UUID DEFAULT uuid_generate_v4();
ALTER TABLE restaurant_items ADD CONSTRAINT IF NOT EXISTS restaurant_items_item_uuid_key UNIQUE (item_uuid);

-- Markets table
ALTER TABLE markets ADD COLUMN IF NOT EXISTS market_uuid UUID DEFAULT uuid_generate_v4();
ALTER TABLE markets ADD CONSTRAINT IF NOT EXISTS markets_market_uuid_key UNIQUE (market_uuid);

-- Market items table
ALTER TABLE market_items ADD COLUMN IF NOT EXISTS item_uuid UUID DEFAULT uuid_generate_v4();
ALTER TABLE market_items ADD CONSTRAINT IF NOT EXISTS market_items_item_uuid_key UNIQUE (item_uuid);

-- Adventures table
ALTER TABLE adventures ADD COLUMN IF NOT EXISTS adventure_uuid UUID DEFAULT uuid_generate_v4();
ALTER TABLE adventures ADD CONSTRAINT IF NOT EXISTS adventures_adventure_uuid_key UNIQUE (adventure_uuid);

-- Bookings table
ALTER TABLE bookings ADD COLUMN IF NOT EXISTS booking_uuid UUID DEFAULT uuid_generate_v4();
ALTER TABLE bookings ADD CONSTRAINT IF NOT EXISTS bookings_booking_uuid_key UNIQUE (booking_uuid);

-- Reviews table
ALTER TABLE reviews ADD COLUMN IF NOT EXISTS review_uuid UUID DEFAULT uuid_generate_v4();
ALTER TABLE reviews ADD CONSTRAINT IF NOT EXISTS reviews_review_uuid_key UNIQUE (review_uuid);

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

-- Update foreign keys in email_verification_tokens table (references users)
ALTER TABLE email_verification_tokens ADD COLUMN IF NOT EXISTS user_uuid_fk UUID REFERENCES users(user_uuid);

-- Update foreign keys in user_companies table (references users and companies)
ALTER TABLE user_companies ADD COLUMN IF NOT EXISTS user_uuid_fk UUID REFERENCES users(user_uuid);
ALTER TABLE user_companies ADD COLUMN IF NOT EXISTS company_uuid_fk UUID REFERENCES companies(company_uuid);

-- 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)
ALTER TABLE beach_places_photos ADD COLUMN IF NOT EXISTS beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);

-- Update foreign keys in beach_places_schedules table (references beach_places)
ALTER TABLE beach_places_schedules ADD COLUMN IF NOT EXISTS beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);

-- Update foreign keys in beach_place_terrains table (references beach_places)
ALTER TABLE beach_place_terrains ADD COLUMN IF NOT EXISTS beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);

-- 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)
ALTER TABLE adventure_schedules ADD COLUMN IF NOT EXISTS adventure_uuid_fk UUID REFERENCES adventures(adventure_uuid);

-- Update foreign keys in bookings table (references users, beach_places, and beach_place_terrains)
ALTER TABLE bookings ADD COLUMN IF NOT EXISTS user_uuid_fk UUID REFERENCES users(user_uuid);
ALTER TABLE bookings ADD COLUMN IF NOT EXISTS beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);

-- Update foreign keys in reviews table (references users, beach_places and bookings)
ALTER TABLE reviews ADD COLUMN IF NOT EXISTS user_uuid_fk UUID REFERENCES users(user_uuid);
ALTER TABLE reviews ADD COLUMN IF NOT EXISTS beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);
ALTER TABLE reviews ADD COLUMN IF NOT EXISTS booking_uuid_fk UUID REFERENCES bookings(booking_uuid);

-- Update foreign keys in user_wallets table (references users)
ALTER TABLE user_wallets ADD COLUMN IF NOT EXISTS user_uuid_fk UUID REFERENCES users(user_uuid);

-- 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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'email_verification_tokens' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'user_companies' AND constraint_name = 'user_companies_user_id_fkey') THEN
        ALTER TABLE user_companies DROP CONSTRAINT user_companies_user_id_fkey;
    END IF;
    IF EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'bookings' AND constraint_name = 'bookings_user_id_fkey') THEN
        ALTER TABLE bookings DROP CONSTRAINT bookings_user_id_fkey;
    END IF;
    IF EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'reviews' AND constraint_name = 'reviews_user_id_fkey') THEN
        ALTER TABLE reviews DROP CONSTRAINT reviews_user_id_fkey;
    END IF;
    IF EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'user_wallets' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'users' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'user_companies' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'beach_places' AND constraint_name = 'beach_places_company_id_fkey') THEN
        ALTER TABLE beach_places DROP CONSTRAINT beach_places_company_id_fkey;
    END IF;
    IF EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'restaurants' AND constraint_name = 'restaurants_company_id_fkey') THEN
        ALTER TABLE restaurants DROP CONSTRAINT restaurants_company_id_fkey;
    END IF;
    IF EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'markets' AND constraint_name = 'markets_company_id_fkey') THEN
        ALTER TABLE markets DROP CONSTRAINT markets_company_id_fkey;
    END IF;
    IF EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'adventures' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'companies' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'beach_places_photos' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'beach_places_schedules' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'beach_place_terrains' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'restaurants' AND constraint_name = 'restaurants_beach_place_id_fkey') THEN
        ALTER TABLE restaurants DROP CONSTRAINT restaurants_beach_place_id_fkey;
    END IF;
    IF EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'markets' AND constraint_name = 'markets_beach_place_id_fkey') THEN
        ALTER TABLE markets DROP CONSTRAINT markets_beach_place_id_fkey;
    END IF;
    IF EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'adventures' AND constraint_name = 'adventures_beach_place_id_fkey') THEN
        ALTER TABLE adventures DROP CONSTRAINT adventures_beach_place_id_fkey;
    END IF;
    IF EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'bookings' AND constraint_name = 'bookings_beach_place_id_fkey') THEN
        ALTER TABLE bookings DROP CONSTRAINT bookings_beach_place_id_fkey;
    END IF;
    IF EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'reviews' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'beach_places' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'restaurant_categories' AND constraint_name = 'restaurant_categories_restaurant_id_fkey') THEN
        ALTER TABLE restaurant_categories DROP CONSTRAINT restaurant_categories_restaurant_id_fkey;
    END IF;
    IF EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'restaurant_items' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'restaurants' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'restaurant_items' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'market_categories' AND constraint_name = 'market_categories_market_id_fkey') THEN
        ALTER TABLE market_categories DROP CONSTRAINT market_categories_market_id_fkey;
    END IF;
    IF EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'market_items' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'markets' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'market_items' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'adventure_schedules' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'adventures' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'reviews' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'bookings' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'reviews' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'wallet_transactions' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'user_wallets' AND constraint_name = '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;
    -- 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
END $$;

-- 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;
    -- 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
END $$;

-- 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;
    -- 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
END $$;

-- 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;
    -- 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
END $$;

-- 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;
    -- 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
END $$;

-- 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;
    -- 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
END $$;

-- 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;
    -- 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
END $$;

-- 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;
    -- 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
END $$;

-- 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;
        ALTER TABLE users ADD 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;
        ALTER TABLE companies ADD 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;
        ALTER TABLE beach_places ADD 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;
        ALTER TABLE restaurants ADD 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;
        ALTER TABLE restaurant_items ADD 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;
        ALTER TABLE markets ADD 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;
        ALTER TABLE market_items ADD 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;
        ALTER TABLE adventures ADD 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;
        ALTER TABLE bookings ADD 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;
        ALTER TABLE reviews ADD 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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'user_companies' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'user_companies' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'beach_places_photos' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'beach_places_schedules' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'beach_place_terrains' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'adventure_schedules' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'bookings' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'bookings' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'reviews' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'reviews' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'reviews' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'user_wallets' AND constraint_name = '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 constraint_name FROM information_schema.table_constraints WHERE table_name = 'email_verification_tokens' AND constraint_name = '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)
ALTER TABLE beach_places ADD COLUMN IF NOT EXISTS company_id_backup BIGINT;

-- Add backup columns for restaurants (company_id references companies - key table, beach_place_id references beach_places - key table)
ALTER TABLE restaurants ADD COLUMN IF NOT EXISTS company_id_backup BIGINT;
ALTER TABLE restaurants ADD COLUMN IF NOT EXISTS beach_place_id_backup BIGINT;

-- Add backup columns for markets (company_id references companies - key table, beach_place_id references beach_places - key table)
ALTER TABLE markets ADD COLUMN IF NOT EXISTS company_id_backup BIGINT;
ALTER TABLE markets ADD COLUMN IF NOT EXISTS beach_place_id_backup BIGINT;

-- Add backup columns for adventures (company_id references companies - key table, beach_place_id references beach_places - key table)
ALTER TABLE adventures ADD COLUMN IF NOT EXISTS company_id_backup BIGINT;
ALTER TABLE adventures ADD COLUMN IF NOT EXISTS beach_place_id_backup BIGINT;

-- 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
ALTER TABLE beach_places ADD COLUMN IF NOT EXISTS company_uuid_temp UUID;
-- Populate the temporary column with the corresponding UUIDs using the backup column
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
ALTER TABLE beach_places DROP COLUMN IF EXISTS company_id;
-- Rename the temporary UUID column to the original name
ALTER TABLE beach_places RENAME COLUMN IF EXISTS company_uuid_temp TO company_id;
-- Add new foreign key constraint for key table reference
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'beach_places' AND constraint_name = '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
ALTER TABLE restaurants ADD COLUMN IF NOT EXISTS company_uuid_temp UUID;
ALTER TABLE restaurants ADD COLUMN IF NOT EXISTS beach_place_uuid_temp UUID;
-- 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
ALTER TABLE restaurants DROP COLUMN IF EXISTS company_id;
ALTER TABLE restaurants DROP COLUMN IF EXISTS beach_place_id;
-- Rename the temporary UUID columns to the original names
ALTER TABLE restaurants RENAME COLUMN IF EXISTS company_uuid_temp TO company_id;
ALTER TABLE restaurants RENAME COLUMN IF EXISTS beach_place_uuid_temp TO beach_place_id;
-- Add new foreign key constraints for key table references
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'restaurants' AND constraint_name = 'fk_restaurants_company') THEN
        ALTER TABLE restaurants ADD CONSTRAINT fk_restaurants_company FOREIGN KEY (company_id) REFERENCES companies(company_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'restaurants' AND constraint_name = '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
ALTER TABLE restaurant_items ADD COLUMN IF NOT EXISTS restaurant_uuid_temp UUID;
-- 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
ALTER TABLE restaurant_items DROP COLUMN IF EXISTS restaurant_id;
-- Rename the temporary UUID column to the original name
ALTER TABLE restaurant_items RENAME COLUMN IF EXISTS restaurant_uuid_temp TO restaurant_id;
-- Add new foreign key constraint for key table reference
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'restaurant_items' AND constraint_name = '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
ALTER TABLE markets ADD COLUMN IF NOT EXISTS company_uuid_temp UUID;
ALTER TABLE markets ADD COLUMN IF NOT EXISTS beach_place_uuid_temp UUID;
-- 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
ALTER TABLE markets DROP COLUMN IF EXISTS company_id;
ALTER TABLE markets DROP COLUMN IF EXISTS beach_place_id;
-- Rename the temporary UUID columns to the original names
ALTER TABLE markets RENAME COLUMN IF EXISTS company_uuid_temp TO company_id;
ALTER TABLE markets RENAME COLUMN IF EXISTS beach_place_uuid_temp TO beach_place_id;
-- Add new foreign key constraints for key table references
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'markets' AND constraint_name = 'fk_markets_company') THEN
        ALTER TABLE markets ADD CONSTRAINT fk_markets_company FOREIGN KEY (company_id) REFERENCES companies(company_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'markets' AND constraint_name = '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
ALTER TABLE market_items ADD COLUMN IF NOT EXISTS market_uuid_temp UUID;
-- 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
ALTER TABLE market_items DROP COLUMN IF EXISTS market_id;
-- Rename the temporary UUID column to the original name
ALTER TABLE market_items RENAME COLUMN IF EXISTS market_uuid_temp TO market_id;
-- Add new foreign key constraint for key table reference
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'market_items' AND constraint_name = '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
ALTER TABLE adventures ADD COLUMN IF NOT EXISTS company_uuid_temp UUID;
ALTER TABLE adventures ADD COLUMN IF NOT EXISTS beach_place_uuid_temp UUID;
-- 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
ALTER TABLE adventures DROP COLUMN IF EXISTS company_id;
ALTER TABLE adventures DROP COLUMN IF EXISTS beach_place_id;
-- Rename the temporary UUID columns to the original names
ALTER TABLE adventures RENAME COLUMN IF EXISTS company_uuid_temp TO company_id;
ALTER TABLE adventures RENAME COLUMN IF EXISTS beach_place_uuid_temp TO beach_place_id;
-- Add new foreign key constraints for key table references
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'adventures' AND constraint_name = 'fk_adventures_company') THEN
        ALTER TABLE adventures ADD CONSTRAINT fk_adventures_company FOREIGN KEY (company_id) REFERENCES companies(company_id);
    END IF;
END $$;
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'adventures' AND constraint_name = '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
ALTER TABLE beach_places DROP COLUMN IF EXISTS company_id_backup;
ALTER TABLE restaurants DROP COLUMN IF EXISTS company_id_backup;
ALTER TABLE restaurants DROP COLUMN IF EXISTS beach_place_id_backup;
ALTER TABLE markets DROP COLUMN IF EXISTS company_id_backup;
ALTER TABLE markets DROP COLUMN IF EXISTS beach_place_id_backup;
ALTER TABLE adventures DROP COLUMN IF EXISTS company_id_backup;
ALTER TABLE adventures DROP COLUMN IF EXISTS beach_place_id_backup;

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

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

-- Migration completed for key tables only