-- 0004_pulls: branch-to-branch pull requests and PR-level comments.
--
-- Same authorship/moderation model as 0003_issues: an authenticated author
-- sets author_id (+ a display author_name); a guest leaves author_id NULL
-- and supplies a self-claimed name and optional email. pending=1 hides guest
-- content until the owner approves it. base/head are branch names within the
-- repo (no forks). number is per-repo (MAX+1).
CREATE TABLE pulls (
    id           INTEGER PRIMARY KEY,
    repo_id      INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    number       INTEGER NOT NULL,
    title        TEXT    NOT NULL,
    body         TEXT    NOT NULL DEFAULT '',
    base         TEXT    NOT NULL,
    head         TEXT    NOT NULL,
    state        TEXT    NOT NULL DEFAULT 'open'
                 CHECK (state IN ('open', 'closed', 'merged')),
    pending      INTEGER NOT NULL DEFAULT 0
                 CHECK (pending IN (0, 1)),
    author_id    INTEGER REFERENCES users(id) ON DELETE SET NULL,
    author_name  TEXT    NOT NULL DEFAULT '',
    author_email TEXT    NOT NULL DEFAULT '',
    created_at   INTEGER NOT NULL DEFAULT (unixepoch()),
    closed_at    INTEGER,
    merged_at    INTEGER,
    merge_commit TEXT    NOT NULL DEFAULT '',
    UNIQUE (repo_id, number)
);

CREATE INDEX pulls_repo_id_idx ON pulls (repo_id);

CREATE TABLE pull_comments (
    id           INTEGER PRIMARY KEY,
    pull_id      INTEGER NOT NULL REFERENCES pulls(id) ON DELETE CASCADE,
    body         TEXT    NOT NULL,
    pending      INTEGER NOT NULL DEFAULT 0
                 CHECK (pending IN (0, 1)),
    author_id    INTEGER REFERENCES users(id) ON DELETE SET NULL,
    author_name  TEXT    NOT NULL DEFAULT '',
    author_email TEXT    NOT NULL DEFAULT '',
    created_at   INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE INDEX pull_comments_pull_id_idx ON pull_comments (pull_id);