-- Discussions: a per-repo forum next to issues, opt-in per repo. Discussion numbers come from
-- repos.next_issue_number, the same counter as issues and pull requests, in the same transaction
-- as the insert (store/discussions.rs). Comments form one level of replies: a reply's parent_id is
-- a top-level comment of the same discussion. A deleted comment keeps its row (deleted_at is set,
-- the body is emptied) so its replies still make sense. answer_comment_id is only set in 'qa'.

ALTER TABLE repos ADD COLUMN discussions_enabled INTEGER NOT NULL DEFAULT 0;

CREATE TABLE discussions (
    id TEXT PRIMARY KEY, repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    number INTEGER NOT NULL,
    category TEXT NOT NULL CHECK (category IN ('general','ideas','qa','announcements')),
    title TEXT NOT NULL, body TEXT NOT NULL DEFAULT '',
    author_id TEXT NOT NULL REFERENCES users(id),
    answer_comment_id TEXT,
    locked INTEGER NOT NULL DEFAULT 0,
    comment_count INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL, updated_at TEXT NOT NULL,
    UNIQUE (repo_id, number));
CREATE INDEX discussions_repo_category ON discussions(repo_id, category, number);

CREATE TABLE discussion_comments (
    id TEXT PRIMARY KEY,
    discussion_id TEXT NOT NULL REFERENCES discussions(id) ON DELETE CASCADE,
    parent_id TEXT REFERENCES discussion_comments(id) ON DELETE CASCADE,
    author_id TEXT NOT NULL REFERENCES users(id),
    body TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL, updated_at TEXT NOT NULL,
    deleted_at TEXT);
CREATE INDEX discussion_comments_discussion ON discussion_comments(discussion_id, created_at);

-- Inbox rows for @mentions in discussions. Issues use the notifications table, which points at
-- issues, so discussions get their own. One row per user and discussion.
CREATE TABLE discussion_notifications (
    user_id TEXT NOT NULL REFERENCES users(id),
    discussion_id TEXT NOT NULL REFERENCES discussions(id) ON DELETE CASCADE,
    repo_id TEXT NOT NULL REFERENCES repos(id),
    reason TEXT NOT NULL DEFAULT 'mention',
    last_actor_id TEXT REFERENCES users(id),
    unread INTEGER NOT NULL DEFAULT 1,
    updated_at TEXT NOT NULL,
    PRIMARY KEY (user_id, discussion_id));
CREATE INDEX discussion_notifications_inbox ON discussion_notifications(user_id, unread, updated_at);