-- Migration: Add organization_id column to tasks table
-- This migration safely adds the organization_id column if it doesn't exist

-- Check if organization_id column exists, if not add it
SET @exist := (SELECT count(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tasks' AND COLUMN_NAME = 'organization_id');
SET @sqlstmt := IF(@exist > 0, 'SELECT ''Column organization_id already exists in tasks table''', 'ALTER TABLE tasks ADD COLUMN organization_id INT NULL AFTER project_id');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add index for organization_id if the column was just created
SET @exist := (SELECT count(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tasks' AND INDEX_NAME = 'idx_tasks_org');
SET @sqlstmt := IF(@exist > 0, 'SELECT ''Index idx_tasks_org already exists''', 'ALTER TABLE tasks ADD INDEX idx_tasks_org (organization_id)');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Update existing tasks with organization_id from their projects
-- This safely updates tasks that don't have organization_id set
UPDATE tasks t 
JOIN projects p ON t.project_id = p.id 
SET t.organization_id = p.organization_id 
WHERE t.organization_id IS NULL AND p.organization_id IS NOT NULL;

-- For tasks without projects, set organization_id to 1 (default organization)
-- Only if there are tasks with NULL organization_id
UPDATE tasks 
SET organization_id = 1 
WHERE organization_id IS NULL 
AND EXISTS (SELECT 1 FROM organizations WHERE id = 1);

SELECT 'Migration completed: organization_id column added to tasks table' as message;