-- This migration adds a position field to the actions table to support drag-and-drop prioritization

-- Step 1: Add position column to actions table (if it doesn't exist)
ALTER TABLE actions ADD COLUMN IF NOT EXISTS position INTEGER DEFAULT 0;

-- Step 2: Update existing records to have sequential positions within each key result
-- This is a PostgreSQL-compatible approach using window functions
-- For MySQL, you might need a different approach
DO $$
DECLARE
  kr RECORD;
BEGIN
  -- For each key_result_id, update positions sequentially
  FOR kr IN SELECT DISTINCT key_result_id FROM actions
  LOOP
    WITH indexed_actions AS (
      SELECT 
        id, 
        ROW_NUMBER() OVER (
          PARTITION BY key_result_id 
          ORDER BY created_at
        ) - 1 as new_position
      FROM actions
      WHERE key_result_id = kr.key_result_id
    )
    UPDATE actions
    SET position = indexed_actions.new_position
    FROM indexed_actions
    WHERE actions.id = indexed_actions.id;
  END LOOP;
END $$;

-- Step 3: Create an index on the position field for faster sorting
CREATE INDEX IF NOT EXISTS idx_actions_position ON actions(key_result_id, position);

-- MySQL alternative for Step 2 (uncomment if using MySQL instead of PostgreSQL):
/*
-- Set user-defined variable to track position within each key result
SET @position := -1;
SET @current_kr := '';

-- Update positions sequentially for each key result
UPDATE actions a
JOIN (
  SELECT 
    id,
    key_result_id,
    @position := IF(@current_kr = key_result_id, @position + 1, 0) AS new_position,
    @current_kr := key_result_id
  FROM actions
  ORDER BY key_result_id, created_at
) AS indexed 
ON a.id = indexed.id
SET a.position = indexed.new_position;
*/
