-- PostgreSQL Migration Script for adding position to actions
-- Run with: psql -d your_database -f postgresql_migration.sql

-- Step 1: Add position column if it doesn't exist
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT FROM information_schema.columns 
        WHERE table_name = 'actions' AND column_name = 'position'
    ) THEN
        ALTER TABLE actions ADD COLUMN position INTEGER DEFAULT 0;
        RAISE NOTICE 'Position column added successfully.';
    ELSE
        RAISE NOTICE 'Position column already exists.';
    END IF;
END $$;

-- Step 2: Update positions for existing actions by key_result_id
DO $$
DECLARE
    kr_record RECORD;
    action_record RECORD;
    position_counter INTEGER;
BEGIN
    -- For each key result ID
    FOR kr_record IN SELECT DISTINCT key_result_id FROM actions LOOP
        position_counter := 0;
        
        -- For each action in this key result, ordered by created_at
        FOR action_record IN 
            SELECT id 
            FROM actions 
            WHERE key_result_id = kr_record.key_result_id 
            ORDER BY created_at
        LOOP
            -- Update the position
            UPDATE actions 
            SET position = position_counter 
            WHERE id = action_record.id;
            
            position_counter := position_counter + 1;
        END LOOP;
        
        RAISE NOTICE 'Updated % actions for key_result_id %', position_counter, kr_record.key_result_id;
    END LOOP;
END $$;

-- Step 3: Create an index for better performance
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE indexname = 'idx_actions_position'
    ) THEN
        CREATE INDEX idx_actions_position ON actions(key_result_id, position);
        RAISE NOTICE 'Index created successfully.';
    ELSE
        RAISE NOTICE 'Index already exists.';
    END IF;
END $$;

-- Final verification
SELECT 'Migration complete. Actions count: ' || COUNT(*) AS result FROM actions;
SELECT 'Actions with position = 0: ' || COUNT(*) AS result FROM actions WHERE position = 0;
