-- Script to clean up redundant beach_place_uuid columns and references
-- This script will:
-- 1. For tables with both beach_place_id (bigint) and beach_place_uuid_fk (uuid):
--    - Drop the old bigint beach_place_id column
--    - Rename beach_place_uuid_fk to beach_place_id
-- 2. Remove the redundant beach_place_uuid column from beach_places table

-- Step 1: Clean up beach_place_terrains table
-- Drop the old bigint beach_place_id column and rename beach_place_uuid_fk
ALTER TABLE beach_place_terrains DROP CONSTRAINT IF EXISTS beach_place_terrains_beach_place_id_fkey;
ALTER TABLE beach_place_terrains DROP COLUMN IF EXISTS beach_place_id;
ALTER TABLE beach_place_terrains RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
-- Add back the foreign key constraint
ALTER TABLE beach_place_terrains ADD CONSTRAINT beach_place_terrains_beach_place_id_fkey 
    FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);

-- Step 2: Clean up beach_places_photos table  
-- Drop the old bigint beach_place_id column and rename beach_place_uuid_fk
ALTER TABLE beach_places_photos DROP CONSTRAINT IF EXISTS beach_places_photos_beach_place_id_fkey;
ALTER TABLE beach_places_photos DROP COLUMN IF EXISTS beach_place_id;
ALTER TABLE beach_places_photos RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
-- Add back the foreign key constraint
ALTER TABLE beach_places_photos ADD CONSTRAINT beach_places_photos_beach_place_id_fkey 
    FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);

-- Step 3: Clean up beach_places_schedules table
-- Drop the old bigint beach_place_id column and rename beach_place_uuid_fk
ALTER TABLE beach_places_schedules DROP CONSTRAINT IF EXISTS beach_places_schedules_beach_place_id_fkey;
ALTER TABLE beach_places_schedules DROP COLUMN IF EXISTS beach_place_id;
ALTER TABLE beach_places_schedules RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
-- Add back the foreign key constraint
ALTER TABLE beach_places_schedules ADD CONSTRAINT beach_places_schedules_beach_place_id_fkey 
    FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);

-- Step 4: Remove the redundant beach_place_uuid column from beach_places table
-- Drop the unique constraint for beach_place_uuid
ALTER TABLE beach_places DROP CONSTRAINT IF EXISTS beach_places_beach_place_uuid_key;
-- Drop the redundant beach_place_uuid column from beach_places
ALTER TABLE beach_places DROP COLUMN IF EXISTS beach_place_uuid;