-- Script to add UUID foreign key columns to referencing tables
-- This is the second part of step 5

-- 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;
    END IF;
END $$;

-- Add UUID foreign key columns 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;
    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;
    END IF;
END $$;

-- Add UUID foreign key columns 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;
    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;
    END IF;
END $$;

-- Add UUID foreign key columns 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;
    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;
    END IF;
END $$;