-- 009_agent_tasks.sql  -  multi-agent coordination hub.
-- Each row is one unit of work; agents (free Copilot windows, Mira, Gwen,
-- subagents) claim, work, and complete. Atomic claim via UPDATE...WHERE
-- claimed_by IS NULL keeps two agents from grabbing the same task.

CREATE TABLE IF NOT EXISTS agent_tasks (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    brief TEXT NOT NULL,
    role_required VARCHAR(40) NOT NULL DEFAULT 'any',
    -- open | claimed | review | done | blocked
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    priority TINYINT UNSIGNED NOT NULL DEFAULT 5, -- 1=urgent, 9=trivial
    claimed_by VARCHAR(80) NULL,
    claim_expires_at DATETIME NULL,
    -- JSON arrays/objects
    depends_on JSON NULL,        -- [task_id, ...]
    touches_paths JSON NULL,     -- ["app/Views/foo.php", ...] for conflict avoidance
    notes JSON NULL,             -- [{by, at, msg}, ...] running comments
    output_summary TEXT NULL,
    output_commit VARCHAR(80) NULL,
    created_by VARCHAR(80) NOT NULL DEFAULT 'mira',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_role_status (role_required, status),
    KEY idx_status_priority (status, priority),
    KEY idx_claimed (claimed_by, claim_expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
