-- Initial schema.
--
-- Timestamps are unix seconds. Paths are stored as text; marver is a
-- macOS/Linux tool and paths there are practically always valid UTF-8.

CREATE TABLE repos (
    id            INTEGER PRIMARY KEY,
    -- Absolute path to the working directory. Identity for upserts.
    path          TEXT    NOT NULL UNIQUE,
    name          TEXT    NOT NULL,
    -- Hidden from pickers, but remembered across rescans.
    ignored       INTEGER NOT NULL DEFAULT 0 CHECK (ignored IN (0, 1)),
    discovered_at INTEGER NOT NULL,
    -- Refreshed by each scan that still finds it; a stale value means the
    -- repo has moved or been deleted.
    last_seen_at  INTEGER NOT NULL
);

CREATE INDEX repos_last_seen_idx ON repos (last_seen_at);

CREATE TABLE tasks (
    id             INTEGER PRIMARY KEY,
    title          TEXT    NOT NULL,
    prompt         TEXT    NOT NULL,
    state          TEXT    NOT NULL
                   CHECK (state IN ('queued', 'running', 'blocked',
                                    'awaiting-review', 'committed',
                                    'failed', 'cancelled')),
    blocked_kind   TEXT
                   CHECK (blocked_kind IS NULL
                          OR blocked_kind IN ('permission-prompt', 'question', 'silence')),
    blocked_reason TEXT,
    -- Why the task failed. Set only in the failed state.
    failure_reason TEXT,
    -- Parent directory holding this task's worktrees; the session's cwd.
    workspace_dir  TEXT    NOT NULL,
    -- Null until a tmux session exists.
    session_name   TEXT,
    created_at     INTEGER NOT NULL,
    updated_at     INTEGER NOT NULL,

    -- Blocking details belong only to the blocked state.
    CHECK (state = 'blocked'
           OR (blocked_kind IS NULL AND blocked_reason IS NULL)),
    -- A blocked task must say why.
    CHECK (state <> 'blocked' OR blocked_kind IS NOT NULL),
    -- Failure details belong only to the failed state, and are required there.
    CHECK (state = 'failed' OR failure_reason IS NULL),
    CHECK (state <> 'failed' OR failure_reason IS NOT NULL)
);

CREATE INDEX tasks_state_idx ON tasks (state, id);

-- One row per (task, repo). Written twice: once when the task is created, to
-- record which repos it targets, and again when it is launched, to record the
-- worktree made for each.
--
-- The worktree columns are nullable because a queued task has selected its
-- repos but owns no worktrees yet. Provisioning at creation time instead would
-- mean a long queue holding disk and branch names for work that has not begun.
-- SQLite permits repeated NULLs in a UNIQUE column, so unprovisioned rows do
-- not collide.
CREATE TABLE task_repos (
    task_id       INTEGER NOT NULL REFERENCES tasks (id) ON DELETE CASCADE,
    repo_id       INTEGER NOT NULL REFERENCES repos (id) ON DELETE RESTRICT,
    worktree_path TEXT    UNIQUE,
    branch        TEXT,
    -- What `branch` was cut from, as resolved at provision time.
    base_ref      TEXT,

    PRIMARY KEY (task_id, repo_id),
    -- The three are filled in together or not at all.
    CHECK ((worktree_path IS NULL AND branch IS NULL AND base_ref IS NULL)
        OR (worktree_path IS NOT NULL AND branch IS NOT NULL AND base_ref IS NOT NULL))
);

CREATE INDEX task_repos_repo_idx ON task_repos (repo_id);

-- Append-only. Never updated or deleted except by task cascade.
CREATE TABLE events (
    id         INTEGER PRIMARY KEY,
    -- Null for events not tied to a task, such as a repo scan.
    task_id    INTEGER REFERENCES tasks (id) ON DELETE CASCADE,
    -- Dotted identifier, e.g. 'task.transition' or 'hook.notification'.
    kind       TEXT    NOT NULL,
    -- JSON object.
    payload    TEXT    NOT NULL,
    created_at INTEGER NOT NULL
);

CREATE INDEX events_task_idx ON events (task_id, id);
CREATE INDEX events_kind_idx ON events (kind, id);
