-- Force cleanup by using CASCADE to drop the beach_place_uuid constraint and column

-- Drop the unique constraint for beach_place_uuid with CASCADE to remove all dependent constraints
ALTER TABLE beach_places DROP CONSTRAINT IF EXISTS beach_places_beach_place_uuid_key CASCADE;

-- Now drop the redundant beach_place_uuid column from beach_places table
ALTER TABLE beach_places DROP COLUMN IF EXISTS beach_place_uuid;

-- Re-add proper foreign key constraints for all tables that should reference beach_places.beach_place_id
-- (Only if they don't already exist with the same name)

-- beach_place_terrains
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 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 ADD CONSTRAINT beach_place_terrains_beach_place_id_fkey 
            FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- beach_places_photos
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 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 ADD CONSTRAINT beach_places_photos_beach_place_id_fkey 
            FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- beach_places_schedules
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 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 ADD CONSTRAINT beach_places_schedules_beach_place_id_fkey 
            FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- adventures
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints 
                   WHERE table_name = 'adventures' 
                   AND constraint_name = 'adventures_beach_place_id_fkey') THEN
        ALTER TABLE adventures ADD CONSTRAINT adventures_beach_place_id_fkey 
            FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- bookings
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints 
                   WHERE table_name = 'bookings' 
                   AND constraint_name = 'bookings_beach_place_id_fkey') THEN
        ALTER TABLE bookings ADD CONSTRAINT bookings_beach_place_id_fkey 
            FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- markets
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints 
                   WHERE table_name = 'markets' 
                   AND constraint_name = 'markets_beach_place_id_fkey') THEN
        ALTER TABLE markets ADD CONSTRAINT markets_beach_place_id_fkey 
            FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- restaurants
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints 
                   WHERE table_name = 'restaurants' 
                   AND constraint_name = 'restaurants_beach_place_id_fkey') THEN
        ALTER TABLE restaurants ADD CONSTRAINT restaurants_beach_place_id_fkey 
            FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;

-- reviews
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints 
                   WHERE table_name = 'reviews' 
                   AND constraint_name = 'reviews_beach_place_id_fkey') THEN
        ALTER TABLE reviews ADD CONSTRAINT reviews_beach_place_id_fkey 
            FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
    END IF;
END $$;