-- Script to add UUID columns to all key tables
-- This is the first step of the migration process

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Users table
ALTER TABLE users ADD COLUMN IF NOT EXISTS user_uuid UUID DEFAULT uuid_generate_v4();
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'users_user_uuid_key') THEN
        ALTER TABLE users ADD CONSTRAINT users_user_uuid_key UNIQUE (user_uuid);
    END IF;
END $$;

-- Companies table
ALTER TABLE companies ADD COLUMN IF NOT EXISTS company_uuid UUID DEFAULT uuid_generate_v4();
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'companies_company_uuid_key') THEN
        ALTER TABLE companies ADD CONSTRAINT companies_company_uuid_key UNIQUE (company_uuid);
    END IF;
END $$;

-- Beach places table
ALTER TABLE beach_places ADD COLUMN IF NOT EXISTS beach_place_uuid UUID DEFAULT uuid_generate_v4();
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'beach_places_beach_place_uuid_key') THEN
        ALTER TABLE beach_places ADD CONSTRAINT beach_places_beach_place_uuid_key UNIQUE (beach_place_uuid);
    END IF;
END $$;

-- Restaurants table
ALTER TABLE restaurants ADD COLUMN IF NOT EXISTS restaurant_uuid UUID DEFAULT uuid_generate_v4();
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'restaurants_restaurant_uuid_key') THEN
        ALTER TABLE restaurants ADD CONSTRAINT restaurants_restaurant_uuid_key UNIQUE (restaurant_uuid);
    END IF;
END $$;

-- Restaurant items table
ALTER TABLE restaurant_items ADD COLUMN IF NOT EXISTS item_uuid UUID DEFAULT uuid_generate_v4();
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'restaurant_items_item_uuid_key') THEN
        ALTER TABLE restaurant_items ADD CONSTRAINT restaurant_items_item_uuid_key UNIQUE (item_uuid);
    END IF;
END $$;

-- Markets table
ALTER TABLE markets ADD COLUMN IF NOT EXISTS market_uuid UUID DEFAULT uuid_generate_v4();
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'markets_market_uuid_key') THEN
        ALTER TABLE markets ADD CONSTRAINT markets_market_uuid_key UNIQUE (market_uuid);
    END IF;
END $$;

-- Market items table
ALTER TABLE market_items ADD COLUMN IF NOT EXISTS item_uuid UUID DEFAULT uuid_generate_v4();
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'market_items_item_uuid_key') THEN
        ALTER TABLE market_items ADD CONSTRAINT market_items_item_uuid_key UNIQUE (item_uuid);
    END IF;
END $$;

-- Adventures table
ALTER TABLE adventures ADD COLUMN IF NOT EXISTS adventure_uuid UUID DEFAULT uuid_generate_v4();
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'adventures_adventure_uuid_key') THEN
        ALTER TABLE adventures ADD CONSTRAINT adventures_adventure_uuid_key UNIQUE (adventure_uuid);
    END IF;
END $$;

-- Bookings table
ALTER TABLE bookings ADD COLUMN IF NOT EXISTS booking_uuid UUID DEFAULT uuid_generate_v4();
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'bookings_booking_uuid_key') THEN
        ALTER TABLE bookings ADD CONSTRAINT bookings_booking_uuid_key UNIQUE (booking_uuid);
    END IF;
END $$;

-- Reviews table
ALTER TABLE reviews ADD COLUMN IF NOT EXISTS review_uuid UUID DEFAULT uuid_generate_v4();
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'reviews_review_uuid_key') THEN
        ALTER TABLE reviews ADD CONSTRAINT reviews_review_uuid_key UNIQUE (review_uuid);
    END IF;
END $$;