-- Fix stakeholders column to support text/emails instead of UUID[]
-- 021_discovery_sessions_stakeholders_fix.sql

-- Change stakeholders from UUID[] to TEXT[] to support email addresses and names
-- This migration handles existing data by converting UUID[] to TEXT[]
DO $$ 
BEGIN
	-- Check if column exists and is UUID[]
	IF EXISTS (
		SELECT 1 FROM information_schema.columns 
		WHERE table_name = 'discovery_sessions' 
		AND column_name = 'stakeholders'
		AND data_type = 'ARRAY'
	) THEN
		-- Convert UUID[] to TEXT[] - drop existing data safely
		ALTER TABLE discovery_sessions 
		ALTER COLUMN stakeholders TYPE TEXT[] 
		USING ARRAY[]::TEXT[];
		
		COMMENT ON COLUMN discovery_sessions.stakeholders IS 'Array of stakeholder names or email addresses (Step 1)';
	END IF;
END $$;

