-- Direct SQL migration script for adding position to actions
-- This can be run directly on your MySQL or PostgreSQL database
-- Run with: mysql -u username -p database_name < direct_db_migration.sql

-- Step 1: Add position column if it doesn't exist
SET @column_exists = 0;
SELECT COUNT(*) INTO @column_exists 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'actions' AND COLUMN_NAME = 'position';

SET @add_column = CONCAT('ALTER TABLE actions ADD COLUMN position INT DEFAULT 0');

-- Only run if column doesn't exist
SET @sql = IF(@column_exists = 0, @add_column, 'SELECT "Position column already exists"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 2: Update positions based on created_at for each key result's actions
-- First, create a temporary table to hold key_result_id and action_id pairs
DROP TEMPORARY TABLE IF EXISTS temp_action_positions;
CREATE TEMPORARY TABLE temp_action_positions (
  action_id INT,
  key_result_id INT,
  new_position INT
);

-- Populate the temporary table with each action's new position
INSERT INTO temp_action_positions (action_id, key_result_id, new_position)
SELECT 
  a.id, 
  a.key_result_id,
  (@row_number := IF(@current_kr = a.key_result_id, @row_number + 1, 0)) AS position,
  (@current_kr := a.key_result_id)
FROM (
  SELECT id, key_result_id, created_at 
  FROM actions 
  ORDER BY key_result_id, created_at
) AS a,
(SELECT @row_number := 0, @current_kr := 0) AS t;

-- Update the actions table with the new positions
UPDATE actions a
JOIN temp_action_positions t ON a.id = t.action_id
SET a.position = t.new_position;

-- Step 3: Create an index on position for better performance
-- Check if index exists
SET @index_exists = 0;
SELECT COUNT(*) INTO @index_exists 
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_NAME = 'actions' AND INDEX_NAME = 'idx_actions_position';

SET @create_index = CONCAT('CREATE INDEX idx_actions_position ON actions(key_result_id, position)');

-- Only run if index doesn't exist
SET @sql_index = IF(@index_exists = 0, @create_index, 'SELECT "Index already exists"');
PREPARE stmt_index FROM @sql_index;
EXECUTE stmt_index;
DEALLOCATE PREPARE stmt_index;

-- Final result
SELECT 'Migration completed successfully' AS Result;
