-- Script to populate UUID columns with data and update foreign key references
-- This is the second step of the migration process

-- Add UUID foreign key columns to referencing tables

-- Update foreign keys in email_verification_tokens table (references users)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'email_verification_tokens' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE email_verification_tokens ADD COLUMN user_uuid_fk UUID REFERENCES users(user_uuid);
    END IF;
END $$;

-- Update foreign keys in user_companies table (references users and companies)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'user_companies' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE user_companies ADD COLUMN user_uuid_fk UUID REFERENCES users(user_uuid);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'user_companies' AND column_name = 'company_uuid_fk') THEN
        ALTER TABLE user_companies ADD COLUMN company_uuid_fk UUID REFERENCES companies(company_uuid);
    END IF;
END $$;

-- Update foreign keys in beach_places_photos table (references beach_places)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'beach_places_photos' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE beach_places_photos ADD COLUMN beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);
    END IF;
END $$;

-- Update foreign keys in beach_places_schedules table (references beach_places)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'beach_places_schedules' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE beach_places_schedules ADD COLUMN beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);
    END IF;
END $$;

-- Update foreign keys in beach_place_terrains table (references beach_places)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'beach_place_terrains' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE beach_place_terrains ADD COLUMN beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);
    END IF;
END $$;

-- Update foreign keys in adventure_schedules table (references adventures)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'adventure_schedules' AND column_name = 'adventure_uuid_fk') THEN
        ALTER TABLE adventure_schedules ADD COLUMN adventure_uuid_fk UUID REFERENCES adventures(adventure_uuid);
    END IF;
END $$;

-- Update foreign keys in bookings table (references users, beach_places, and beach_place_terrains)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'bookings' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE bookings ADD COLUMN user_uuid_fk UUID REFERENCES users(user_uuid);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'bookings' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE bookings ADD COLUMN beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);
    END IF;
END $$;

-- Update foreign keys in reviews table (references users, beach_places and bookings)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE reviews ADD COLUMN user_uuid_fk UUID REFERENCES users(user_uuid);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'beach_place_uuid_fk') THEN
        ALTER TABLE reviews ADD COLUMN beach_place_uuid_fk UUID REFERENCES beach_places(beach_place_uuid);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'reviews' AND column_name = 'booking_uuid_fk') THEN
        ALTER TABLE reviews ADD COLUMN booking_uuid_fk UUID REFERENCES bookings(booking_uuid);
    END IF;
END $$;

-- Update foreign keys in user_wallets table (references users)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'user_wallets' AND column_name = 'user_uuid_fk') THEN
        ALTER TABLE user_wallets ADD COLUMN user_uuid_fk UUID REFERENCES users(user_uuid);
    END IF;
END $$;

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

-- Populate foreign keys in email_verification_tokens table
UPDATE email_verification_tokens e SET user_uuid_fk = (SELECT user_uuid FROM users WHERE user_id = e.user_id);

-- Populate foreign keys in user_companies table
UPDATE user_companies uc SET user_uuid_fk = (SELECT user_uuid FROM users WHERE user_id = uc.user_id);
UPDATE user_companies uc SET company_uuid_fk = (SELECT company_uuid FROM companies WHERE company_id = uc.company_id);

-- Populate foreign keys in beach_places_photos table
UPDATE beach_places_photos bpp SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = bpp.beach_place_id);

-- Populate foreign keys in beach_places_schedules table
UPDATE beach_places_schedules bps SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = bps.beach_place_id);

-- Populate foreign keys in beach_place_terrains table
UPDATE beach_place_terrains bpt SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = bpt.beach_place_id);

-- Populate foreign keys in adventure_schedules table
UPDATE adventure_schedules a_s SET adventure_uuid_fk = (SELECT adventure_uuid FROM adventures WHERE adventure_id = a_s.adventure_id);

-- Populate foreign keys in bookings table
UPDATE bookings b SET user_uuid_fk = (SELECT user_uuid FROM users WHERE user_id = b.user_id);
UPDATE bookings b SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = b.beach_place_id);

-- Populate foreign keys in reviews table
UPDATE reviews r SET user_uuid_fk = (SELECT user_uuid FROM users WHERE user_id = r.user_id);
UPDATE reviews r SET beach_place_uuid_fk = (SELECT beach_place_uuid FROM beach_places WHERE beach_place_id = r.beach_place_id);
UPDATE reviews r SET booking_uuid_fk = (SELECT booking_uuid FROM bookings WHERE booking_id = r.booking_id);

-- Populate foreign keys in user_wallets table
UPDATE user_wallets uw SET user_uuid_fk = (SELECT user_uuid FROM users WHERE user_id = uw.user_id);