-- Script to drop old numeric primary key columns and foreign key columns in key tables
-- This is the third step of the migration process

-- 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 CASCADE;
    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;
    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 CASCADE;
    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_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 CASCADE;
    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_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 CASCADE;
    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 CASCADE;
    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 CASCADE;
    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 $$;