-- Add custom_task_id column to tasks table for month-based sequential numbering
-- Task IDs will be in format YYYY-MM-N where N resets to 1 each month

ALTER TABLE tasks 
ADD COLUMN custom_task_id VARCHAR(20) NULL AFTER task_id,
ADD INDEX idx_custom_task_id (custom_task_id);

-- Update existing tasks with custom IDs based on their scheduled date
UPDATE tasks t
SET custom_task_id = CONCAT(
    DATE_FORMAT(t.scheduled_date, '%Y-%m'),
    '-',
    (
        SELECT COUNT(*) + 1
        FROM tasks t2
        WHERE DATE_FORMAT(t2.scheduled_date, '%Y-%m') = DATE_FORMAT(t.scheduled_date, '%Y-%m')
        AND t2.task_id < t.task_id
    )
)
WHERE custom_task_id IS NULL;
