-- 0003_issues: per-repo issues and comments with guest filing + moderation.
--
-- Author identity: an authenticated author sets author_id (and author_name
-- as a display copy); a guest leaves author_id NULL and supplies a
-- self-claimed author_name plus optional author_email (owner-visible only).
-- pending=1 hides guest content from the public until the owner approves it;
-- owner-authored rows are never pending. number is per-repo (MAX+1).
CREATE TABLE issues (
    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 '',
    state        TEXT    NOT NULL DEFAULT 'open'
                 CHECK (state IN ('open', 'closed')),
    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,
    UNIQUE (repo_id, number)
);

CREATE INDEX issues_repo_id_idx ON issues (repo_id);

CREATE TABLE issue_comments (
    id           INTEGER PRIMARY KEY,
    issue_id     INTEGER NOT NULL REFERENCES issues(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 issue_comments_issue_id_idx ON issue_comments (issue_id);