-- Pull request reviews and line comments. Applied by store/migrate.rs.
-- A review belongs to a pull request (an issues row with kind = 'pull'). Review comments belong
-- to a review; a comment with reply_to set is a reply in the root comment's thread.

CREATE TABLE pull_reviews (
    id TEXT PRIMARY KEY, issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
    author_id TEXT NOT NULL REFERENCES users(id),
    state TEXT NOT NULL,                 -- pending | commented | approved | changes_requested | dismissed
    body TEXT NOT NULL DEFAULT '',
    commit_oid TEXT NOT NULL,            -- head at submit (or at creation while pending)
    dismissed_reason TEXT,
    created_at TEXT NOT NULL, submitted_at TEXT);
CREATE INDEX pull_reviews_issue ON pull_reviews(issue_id, submitted_at);
CREATE TABLE review_comments (
    id TEXT PRIMARY KEY, issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
    review_id TEXT NOT NULL REFERENCES pull_reviews(id) ON DELETE CASCADE,
    author_id TEXT NOT NULL REFERENCES users(id),
    path TEXT NOT NULL, side TEXT NOT NULL, line INTEGER NOT NULL,
    commit_oid TEXT NOT NULL, diff_hunk TEXT NOT NULL DEFAULT '',
    reply_to TEXT REFERENCES review_comments(id) ON DELETE CASCADE,
    body TEXT NOT NULL, outdated INTEGER NOT NULL DEFAULT 0, resolved INTEGER NOT NULL DEFAULT 0,
    resolved_by TEXT REFERENCES users(id),
    created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
CREATE INDEX review_comments_issue ON review_comments(issue_id, path, created_at);