-- Adds the `paused` state, and todos.
--
-- The state list is a CHECK constraint and SQLite cannot alter one, so the
-- tasks table is rebuilt. Everything else about it is unchanged: the column
-- list, the other constraints, and the index are reproduced exactly, because a
-- rebuild that quietly drops a constraint is worse than no rebuild at all.
--
-- Foreign keys must be off while this runs, or the DROP below fires the
-- cascades and takes every task's repos and events with it. That cannot be done
-- here -- the pragma is a no-op inside a transaction, and migrations run in one
-- -- so `migrate` turns it off around the batch and checks integrity before it
-- commits.

CREATE TABLE tasks_new (
    id             INTEGER PRIMARY KEY,
    title          TEXT    NOT NULL,
    prompt         TEXT    NOT NULL,
    state          TEXT    NOT NULL
                   CHECK (state IN ('queued', 'running', 'blocked',
                                    'awaiting-review', 'paused', 'committed',
                                    'failed', 'cancelled')),
    blocked_kind   TEXT
                   CHECK (blocked_kind IS NULL
                          OR blocked_kind IN ('permission-prompt', 'question', 'silence')),
    blocked_reason TEXT,
    failure_reason TEXT,
    workspace_dir  TEXT    NOT NULL,
    session_name   TEXT,
    created_at     INTEGER NOT NULL,
    updated_at     INTEGER NOT NULL,

    CHECK (state = 'blocked'
           OR (blocked_kind IS NULL AND blocked_reason IS NULL)),
    CHECK (state <> 'blocked' OR blocked_kind IS NOT NULL),
    CHECK (state = 'failed' OR failure_reason IS NULL),
    CHECK (state <> 'failed' OR failure_reason IS NOT NULL)
);

INSERT INTO tasks_new
SELECT id, title, prompt, state, blocked_kind, blocked_reason, failure_reason,
       workspace_dir, session_name, created_at, updated_at
FROM tasks;

DROP TABLE tasks;
ALTER TABLE tasks_new RENAME TO tasks;

CREATE INDEX tasks_state_idx ON tasks (state, id);

-- Notes, at two scopes.
--
-- A NULL task_id is a global todo: something to do that has not been scoped
-- into a task yet, and whose use is to *become* one. A todo with a task_id
-- belongs to that task and its use is to be sent into that agent's session.
-- One table because they are the same shape and the same screen; the scope is
-- what decides what "use it" means.
CREATE TABLE todos (
    id         INTEGER PRIMARY KEY,
    task_id    INTEGER REFERENCES tasks (id) ON DELETE CASCADE,
    text       TEXT    NOT NULL CHECK (length(trim(text)) > 0),
    done       INTEGER NOT NULL DEFAULT 0 CHECK (done IN (0, 1)),
    created_at INTEGER NOT NULL
);

-- Reads are always "the todos for this scope", so the scope leads.
CREATE INDEX todos_scope_idx ON todos (task_id, id);
