-- Proposals and review (hub spec §7). Approvals are seals in the object

-- store; rows here only record intent and discussion.

-- One number space per repository, shared by proposals and issues.

CREATE TABLE numbers (

    repo_id INTEGER PRIMARY KEY REFERENCES repositories(id) ON DELETE CASCADE,

    next    INTEGER NOT NULL DEFAULT 1

);

CREATE TABLE proposals (

    id               INTEGER PRIMARY KEY,

    repo_id          INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    number           INTEGER NOT NULL,

    title            TEXT NOT NULL,

    body             TEXT NOT NULL DEFAULT '',

    author_id        INTEGER REFERENCES accounts(id) ON DELETE SET NULL,

    author           TEXT NOT NULL DEFAULT '',

    state            TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('draft', 'open', 'landed', 'closed')),

    target           TEXT NOT NULL,

    source_kind      TEXT NOT NULL CHECK (source_kind IN ('bookmark', 'fork', 'changes')),

    source_repo_id   INTEGER REFERENCES repositories(id) ON DELETE SET NULL,

    source_bookmark  TEXT NOT NULL DEFAULT '',

    source_changes   TEXT NOT NULL DEFAULT '',

    projected_state  TEXT NOT NULL DEFAULT '',

    projected_heads  TEXT NOT NULL DEFAULT '',

    projected_target TEXT NOT NULL DEFAULT '',

    projected_changes TEXT NOT NULL DEFAULT '',

    conflicted       INTEGER NOT NULL DEFAULT 0,

    landed_state     TEXT NOT NULL DEFAULT '',

    created_ms       INTEGER NOT NULL,

    updated_ms       INTEGER NOT NULL,

    UNIQUE (repo_id, number)

);

CREATE TABLE proposal_comments (

    id          INTEGER PRIMARY KEY,

    proposal_id INTEGER NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,

    author_id   INTEGER REFERENCES accounts(id) ON DELETE SET NULL,

    author      TEXT NOT NULL DEFAULT '',

    body        TEXT NOT NULL,

    path        TEXT NOT NULL DEFAULT '',

    -- The atom the comment anchors to: the change that introduced the line and its index.

    anchor_change TEXT NOT NULL DEFAULT '',

    anchor_index  INTEGER NOT NULL DEFAULT 0,

    resolved    INTEGER NOT NULL DEFAULT 0,

    created_ms  INTEGER NOT NULL

);

CREATE INDEX proposal_comments_by_proposal ON proposal_comments (proposal_id, created_ms);

CREATE TABLE proposal_reviews (

    id          INTEGER PRIMARY KEY,

    proposal_id INTEGER NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,

    author_id   INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    author      TEXT NOT NULL DEFAULT '',

    verdict     TEXT NOT NULL CHECK (verdict IN ('comment', 'changes', 'approve')),

    body        TEXT NOT NULL DEFAULT '',

    created_ms  INTEGER NOT NULL

);

CREATE INDEX proposal_reviews_by_proposal ON proposal_reviews (proposal_id, created_ms);

CREATE TABLE proposal_events (

    id          INTEGER PRIMARY KEY,

    proposal_id INTEGER NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,

    at_ms       INTEGER NOT NULL,

    actor       TEXT NOT NULL DEFAULT '',

    event       TEXT NOT NULL,

    detail      TEXT NOT NULL DEFAULT ''

);

-- Landing requirements per repository (hub spec §7.4).

ALTER TABLE repositories ADD COLUMN required_approvals INTEGER NOT NULL DEFAULT 0;

ALTER TABLE repositories ADD COLUMN require_owners INTEGER NOT NULL DEFAULT 0;

ALTER TABLE repositories ADD COLUMN allow_conflicts INTEGER NOT NULL DEFAULT 0;