-- Script to drop old numeric foreign key columns and rename UUID foreign key columns
-- This is the fifth and final part of step 5

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

-- Update foreign keys in restaurant_items table
ALTER TABLE restaurant_items DROP COLUMN restaurant_id;
ALTER TABLE restaurant_items RENAME COLUMN restaurant_uuid_fk TO restaurant_id;
-- Add foreign key constraint back
ALTER TABLE restaurant_items ADD CONSTRAINT restaurant_items_restaurant_id_fkey FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id);

-- Update foreign keys in markets table
ALTER TABLE markets DROP COLUMN company_id;
ALTER TABLE markets DROP COLUMN beach_place_id;
ALTER TABLE markets RENAME COLUMN company_uuid_fk TO company_id;
ALTER TABLE markets RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
-- Add foreign key constraints back
ALTER TABLE markets ADD CONSTRAINT markets_company_id_fkey FOREIGN KEY (company_id) REFERENCES companies(company_id);
ALTER TABLE markets ADD CONSTRAINT markets_beach_place_id_fkey FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);

-- Update foreign keys in restaurants table
ALTER TABLE restaurants DROP COLUMN company_id;
ALTER TABLE restaurants DROP COLUMN beach_place_id;
ALTER TABLE restaurants RENAME COLUMN company_uuid_fk TO company_id;
ALTER TABLE restaurants RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
-- Add foreign key constraints back
ALTER TABLE restaurants ADD CONSTRAINT restaurants_company_id_fkey FOREIGN KEY (company_id) REFERENCES companies(company_id);
ALTER TABLE restaurants ADD CONSTRAINT restaurants_beach_place_id_fkey FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);

-- Update foreign keys in adventures table
ALTER TABLE adventures DROP COLUMN company_id;
ALTER TABLE adventures DROP COLUMN beach_place_id;
ALTER TABLE adventures RENAME COLUMN company_uuid_fk TO company_id;
ALTER TABLE adventures RENAME COLUMN beach_place_uuid_fk TO beach_place_id;
-- Add foreign key constraints back
ALTER TABLE adventures ADD CONSTRAINT adventures_company_id_fkey FOREIGN KEY (company_id) REFERENCES companies(company_id);
ALTER TABLE adventures ADD CONSTRAINT adventures_beach_place_id_fkey FOREIGN KEY (beach_place_id) REFERENCES beach_places(beach_place_id);