-- Migration 002: Task-Delegation Integration
-- Session 374: Unified task management with code delegation

-- Task delegation tracking table
CREATE TABLE IF NOT EXISTS task_delegations (
    id SERIAL PRIMARY KEY,
    task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    delegation_id TEXT NOT NULL UNIQUE,
    backend TEXT NOT NULL,
    quality_score INTEGER,
    learnings_injected TEXT,  -- Comma-separated learning IDs
    status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'success', 'failed', 'partial')),
    started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    completed_at TIMESTAMP WITH TIME ZONE,
    input_tokens INTEGER DEFAULT 0,
    output_tokens INTEGER DEFAULT 0,
    cost_usd NUMERIC(10,6) DEFAULT 0,
    context_hash TEXT,  -- MD5 of task file for dedup
    error_message TEXT
);

CREATE INDEX IF NOT EXISTS idx_task_delegations_task ON task_delegations(task_id);
CREATE INDEX IF NOT EXISTS idx_task_delegations_status ON task_delegations(status);
CREATE INDEX IF NOT EXISTS idx_task_delegations_backend ON task_delegations(backend);
CREATE INDEX IF NOT EXISTS idx_task_delegations_started ON task_delegations(started_at);

-- Per-task learning effectiveness tracking
CREATE TABLE IF NOT EXISTS task_learning_effectiveness (
    id SERIAL PRIMARY KEY,
    task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    learning_id INTEGER NOT NULL,
    outcome TEXT NOT NULL CHECK (outcome IN ('success', 'failed')),
    recorded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_task_learning_task ON task_learning_effectiveness(task_id);
CREATE INDEX IF NOT EXISTS idx_task_learning_learning ON task_learning_effectiveness(learning_id);
CREATE INDEX IF NOT EXISTS idx_task_learning_outcome ON task_learning_effectiveness(outcome);

-- Migration tracking
INSERT INTO schema_migrations (version, applied_at) VALUES ('002_task_delegations', NOW())
ON CONFLICT DO NOTHING;