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

-- Add UUID foreign key columns to tables that are missing them

-- Add UUID foreign key column to restaurant_items table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'restaurant_items' AND column_name = 'restaurant_uuid_fk') THEN
        ALTER TABLE restaurant_items ADD COLUMN restaurant_uuid_fk UUID REFERENCES restaurants(restaurant_uuid);
    END IF;
END $$;

-- Add UUID foreign key column to markets table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'company_uuid_fk') THEN
        ALTER TABLE markets ADD COLUMN company_uuid_fk UUID REFERENCES companies(company_uuid);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'markets' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE markets ADD COLUMN beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);
    END IF;
END $$;

-- Add UUID foreign key column to restaurants table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'company_uuid_fk') THEN
        ALTER TABLE restaurants ADD COLUMN company_uuid_fk UUID REFERENCES companies(company_uuid);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'restaurants' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE restaurants ADD COLUMN beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);
    END IF;
END $$;

-- Add UUID foreign key column to adventures table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'company_uuid_fk') THEN
        ALTER TABLE adventures ADD COLUMN company_uuid_fk UUID REFERENCES companies(company_uuid);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'adventures' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE adventures ADD COLUMN beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);
    END IF;
END $$;

-- Populate the new UUID foreign key columns with matching UUIDs

-- Populate foreign keys in restaurant_items table
UPDATE restaurant_items ri SET restaurant_uuid_fk = (SELECT restaurant_uuid FROM restaurants WHERE restaurant_id = ri.restaurant_id);

-- Populate foreign keys in markets table
UPDATE markets m SET company_uuid_fk = (SELECT company_uuid FROM companies WHERE company_id = m.company_id);
UPDATE markets m SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = m.beach_place_id);

-- Populate foreign keys in restaurants table
UPDATE restaurants r SET company_uuid_fk = (SELECT company_uuid FROM companies WHERE company_id = r.company_id);
UPDATE restaurants r SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = r.beach_place_id);

-- Populate foreign keys in adventures table
UPDATE adventures a SET company_uuid_fk = (SELECT company_uuid FROM companies WHERE company_id = a.company_id);
UPDATE adventures a SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = a.beach_place_id);

-- Drop foreign key constraints that reference the key tables first
DO $$ 
BEGIN
    -- Drop foreign key constraints in restaurant_items table
    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;
    
    -- Drop foreign key constraints in markets table
    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 = 'markets' AND constraint_name = 'markets_beach_place_id_fkey') THEN
        ALTER TABLE markets DROP CONSTRAINT markets_beach_place_id_fkey;
    END IF;
    
    -- Drop foreign key constraints in restaurants table
    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 = 'restaurants' AND constraint_name = 'restaurants_beach_place_id_fkey') THEN
        ALTER TABLE restaurants DROP CONSTRAINT restaurants_beach_place_id_fkey;
    END IF;
    
    -- Drop foreign key constraints in adventures table
    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;
    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;
END $$;

-- 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);