-- Migration: Fix user_settings and customer_settings to use UUID for user_id
-- Date: 2025-11-15
-- Issue: user_settings.user_id was INTEGER but users.user_id is UUID
-- This causes: SQLSTATE[22P02]: Invalid text representation error

BEGIN;

-- Fix user_settings table
ALTER TABLE user_settings 
  ALTER COLUMN user_id TYPE uuid USING user_id::text::uuid;

-- Add foreign key constraint if not exists
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint 
    WHERE conname = 'user_settings_user_id_fkey'
  ) THEN
    ALTER TABLE user_settings 
      ADD CONSTRAINT user_settings_user_id_fkey 
      FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE;
  END IF;
END $$;

-- Note: customer_settings references customers table which may use integer IDs
-- Check if customers table exists and what type customer_id is
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.tables 
    WHERE table_name = 'customers'
  ) THEN
    -- If customers.customer_id is also UUID, uncomment the following:
    -- ALTER TABLE customer_settings 
    --   ALTER COLUMN customer_id TYPE uuid USING customer_id::text::uuid;
    
    -- Add foreign key if customers table exists
    IF NOT EXISTS (
      SELECT 1 FROM pg_constraint 
      WHERE conname = 'customer_settings_customer_id_fkey'
    ) THEN
      ALTER TABLE customer_settings 
        ADD CONSTRAINT customer_settings_customer_id_fkey 
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE;
    END IF;
  END IF;
END $$;

COMMIT;
