-- pull_request.sql
-- Reference DDL for pull request tables used by lib/repo/features/pull_request.
-- This file is documentation only; it is NOT executed by the SeaORM migration runner
-- per project policy ("数据库就不要动了"). Apply manually when DBA window allows.
--
-- Entity module: lib/db/pull_request/
--   pull_requests            -> table pull_request
--   pull_request_comment     -> table pull_request_comment
--   pull_request_review      -> table pull_request_review
--   pull_request_event_line  -> table pull_request_event_line
--
-- Issue backing rows live in the existing `issue` table (collection_id = repo_id).

CREATE TABLE IF NOT EXISTS pull_request (
    id                UUID        PRIMARY KEY,
    repo_id           UUID        NOT NULL,
    issue_id          UUID        NOT NULL,
    autoincrement_id  INTEGER     NOT NULL,
    author_id         UUID        NOT NULL,
    title             TEXT        NOT NULL,
    body              TEXT        NOT NULL DEFAULT '',
    head_branch       TEXT        NOT NULL,
    head_sha          TEXT        NOT NULL,
    base_branch       TEXT        NOT NULL,
    base_sha          TEXT        NOT NULL,
    has_merged        BOOLEAN     NOT NULL DEFAULT false,
    merged_commit_sha TEXT        NULL,
    additions         INTEGER     NOT NULL DEFAULT 0,
    deletions         INTEGER     NOT NULL DEFAULT 0,
    files_changed     INTEGER     NOT NULL DEFAULT 0,
    merger_id         UUID        NULL,
    merged_at         TIMESTAMPTZ NULL,
    is_locked         BOOLEAN     NOT NULL DEFAULT false,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NULL,
    closed_at         TIMESTAMPTZ NULL,
    CONSTRAINT uq_pull_request_repo_number UNIQUE (repo_id, autoincrement_id)
);

CREATE INDEX IF NOT EXISTS idx_pull_request_repo_id ON pull_request (repo_id);
CREATE INDEX IF NOT EXISTS idx_pull_request_issue_id ON pull_request (issue_id);

CREATE TABLE IF NOT EXISTS pull_request_comment (
    id              UUID        PRIMARY KEY,
    pull_request_id UUID        NOT NULL,
    user_id         UUID        NOT NULL,
    review_id       UUID        NULL,
    commit_sha      TEXT        NOT NULL,
    path            TEXT        NOT NULL DEFAULT '',
    line            INTEGER     NULL,
    body            TEXT        NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NULL
);

CREATE INDEX IF NOT EXISTS idx_pr_comment_pr_id ON pull_request_comment (pull_request_id);
CREATE INDEX IF NOT EXISTS idx_pr_comment_review_id ON pull_request_comment (review_id);

CREATE TABLE IF NOT EXISTS pull_request_review (
    id              UUID        PRIMARY KEY,
    pull_request_id UUID        NOT NULL,
    user_id         UUID        NOT NULL,
    commit_sha      TEXT        NOT NULL,
    official        BOOLEAN     NOT NULL DEFAULT false,
    content         TEXT        NULL,
    state           SMALLINT    NOT NULL DEFAULT 0,  -- 0=Approved 1=ChangesRequested 2=Commented
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NULL
);

CREATE INDEX IF NOT EXISTS idx_pr_review_pr_id ON pull_request_review (pull_request_id);

CREATE TABLE IF NOT EXISTS pull_request_event_line (
    id              UUID        PRIMARY KEY,
    pull_request_id UUID        NOT NULL,
    user_id         UUID        NOT NULL,
    event_type      TEXT        NOT NULL,
    commit_sha      TEXT        NULL,
    content         TEXT        NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_pr_event_pr_id ON pull_request_event_line (pull_request_id);