-- Script to clean up remaining foreign key columns
-- This is the final cleanup step

-- Drop old numeric foreign key columns and rename UUID foreign key columns

-- Update foreign keys in market_items table
ALTER TABLE market_items DROP COLUMN market_id;
ALTER TABLE market_items RENAME COLUMN market_uuid_fk TO market_id;
-- Add foreign key constraint back
ALTER TABLE market_items ADD CONSTRAINT market_items_market_id_fkey FOREIGN KEY (market_id) REFERENCES markets(market_id);

-- Update foreign keys in bookings table
ALTER TABLE bookings DROP COLUMN user_id;
ALTER TABLE bookings DROP COLUMN beach_place_id;
ALTER TABLE bookings RENAME COLUMN user_uuid_fk TO user_id;
ALTER TABLE bookings RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
-- Add foreign key constraints back
ALTER TABLE bookings ADD CONSTRAINT bookings_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id);
ALTER TABLE bookings ADD CONSTRAINT bookings_beach_place_id_fkey FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);

-- Update foreign keys in reviews table
ALTER TABLE reviews DROP COLUMN user_id;
ALTER TABLE reviews DROP COLUMN beach_place_id;
ALTER TABLE reviews DROP COLUMN booking_id;
ALTER TABLE reviews RENAME COLUMN user_uuid_fk TO user_id;
ALTER TABLE reviews RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
ALTER TABLE reviews RENAME COLUMN booking_uuid_fk TO booking_id;
-- Add foreign key constraints back
ALTER TABLE reviews ADD CONSTRAINT reviews_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id);
ALTER TABLE reviews ADD CONSTRAINT reviews_beach_place_id_fkey FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);
ALTER TABLE reviews ADD CONSTRAINT reviews_booking_id_fkey FOREIGN KEY (booking_id) REFERENCES bookings(booking_id);