-- Add "done" status and auto-close workflow
--
-- Migration: 022_add_done_status
-- Purpose: Add "done" status for verification period before auto-closing
-- Related: CF-454
--
-- Status lifecycle with done:
--   - pending: Created, awaiting approval or dependencies
--   - open: Approved and ready to start
--   - in_progress: Currently being worked on
--   - testing: Implementation complete, being tested
--   - blocked: Stuck waiting for something
--   - done: Implementation complete, in 7-day verification period
--   - completed: Verified stable, auto-closed after 7 days
--
-- Verification period:
--   - Tasks marked "done" wait 7 days before auto-closing to "completed"
--   - If related issues detected during period, task stays "done" (no auto-close)
--   - Prevents premature closure and catches regressions
--
-- Auto-close workflow:
--   1. Task marked as "done" → auto_close_at = now() + 7 days
--   2. Cron job checks daily for tasks past auto_close_at
--   3. Semantic search detects related issues created since task marked "done"
--   4. If no related issues → auto-close to "completed"
--   5. If related issues found → notify, keep as "done"

-- Drop existing constraint
ALTER TABLE tasks DROP CONSTRAINT IF EXISTS tasks_status_check;

-- Add new constraint with done status
ALTER TABLE tasks ADD CONSTRAINT tasks_status_check
    CHECK (status IN ('pending', 'open', 'in_progress', 'testing', 'blocked', 'done', 'completed'));

-- Add auto_close_at column for tracking when to auto-close
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS auto_close_at TIMESTAMP WITH TIME ZONE;

-- Create index for efficient cron job queries
CREATE INDEX IF NOT EXISTS idx_tasks_auto_close ON tasks(auto_close_at) WHERE auto_close_at IS NOT NULL;

-- Add comment for documentation
COMMENT ON COLUMN tasks.auto_close_at IS 'Timestamp when task should auto-close from done to completed (7 days after marked done)';