-- Collaboration: per-repo toggles + shared issue/PR number counter, labels,
-- issues (with is_pull discriminator), comments, issue↔label join, and the PR
-- side table. Issues and PRs share the `issues` table and `repos.next_iid`, so
-- `#N` is unique across both.
ALTER TABLE repos ADD COLUMN issues_enabled INTEGER NOT NULL DEFAULT 1;
ALTER TABLE repos ADD COLUMN pulls_enabled  INTEGER NOT NULL DEFAULT 1;
ALTER TABLE repos ADD COLUMN next_iid       BIGINT  NOT NULL DEFAULT 1;

CREATE TABLE labels (
    id          TEXT PRIMARY KEY,
    repo_id     TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    name_lower  TEXT NOT NULL,
    color       TEXT NOT NULL,               -- '#rrggbb'
    description TEXT,
    created_at  BIGINT NOT NULL
);
CREATE UNIQUE INDEX idx_labels_repo_name ON labels (repo_id, name_lower);

CREATE TABLE issues (
    id          TEXT PRIMARY KEY,
    repo_id     TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    number      BIGINT NOT NULL,
    author_id   TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title       TEXT NOT NULL,
    body        TEXT NOT NULL,
    state       TEXT NOT NULL DEFAULT 'open', -- 'open' | 'closed'
    is_pull     INTEGER NOT NULL DEFAULT 0,
    assignee_id TEXT REFERENCES users(id) ON DELETE SET NULL,
    created_at  BIGINT NOT NULL,
    updated_at  BIGINT NOT NULL,
    closed_at   BIGINT
);
CREATE UNIQUE INDEX idx_issues_repo_number ON issues (repo_id, number);
CREATE INDEX idx_issues_repo_state ON issues (repo_id, is_pull, state);

CREATE TABLE comments (
    id         TEXT PRIMARY KEY,
    issue_id   TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
    author_id  TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    body       TEXT NOT NULL,
    created_at BIGINT NOT NULL,
    updated_at BIGINT NOT NULL
);

CREATE TABLE issue_labels (
    issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
    label_id TEXT NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
    PRIMARY KEY (issue_id, label_id)
);

CREATE TABLE pull_requests (
    issue_id       TEXT PRIMARY KEY REFERENCES issues(id) ON DELETE CASCADE,
    base_ref       TEXT NOT NULL,
    head_ref       TEXT NOT NULL,
    merged_at      BIGINT,
    merged_by      TEXT REFERENCES users(id) ON DELETE SET NULL,
    merge_sha      TEXT,
    merge_strategy TEXT
);