-- §5: threads, thread_messages, changes — the conversation and revision
-- substrate.
--
-- Per the §5 super-draft scoping note, rows with rfc_slug pointing at a
-- super-draft entry use branch_name to name a meta-repo branch rather
-- than a per-RFC-repo branch. The schema is identical either way; the
-- interpretation flows from the entry's state in cached_rfcs.
--
-- Threads on a pending-idea PR (§9.3) carry the proposed slug as rfc_slug
-- pre-merge — slugs are reserved during the idea PR per §9.1's uniqueness
-- check — and surface under the super-draft on merge with no data movement.

CREATE TABLE threads (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    rfc_slug            TEXT NOT NULL,
    branch_name         TEXT,                       -- NULL = scoped to the RFC's main view
    anchor_kind         TEXT NOT NULL CHECK (anchor_kind IN ('whole-doc', 'range', 'paragraph')),
    anchor_payload      TEXT,                       -- JSON: ProseMirror range or paragraph id
    thread_kind         TEXT NOT NULL CHECK (thread_kind IN ('chat', 'flag', 'review')),
    label               TEXT,                       -- short summary, or full flag content
    state               TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'resolved', 'stale')),
    created_by          INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at          TEXT NOT NULL DEFAULT (datetime('now')),
    resolved_at         TEXT,
    resolved_by         INTEGER REFERENCES users(id) ON DELETE SET NULL
);

CREATE INDEX idx_threads_scope ON threads (rfc_slug, branch_name, state);
CREATE INDEX idx_threads_kind ON threads (thread_kind, state);

-- §5: chat content. Only chat-kind threads have rows here unless a flag
-- has been converted to a chat (§8.13). System-author messages (role='system',
-- author_user_id=NULL) carry the §10.6 manual-edit-flush markers, the §9.3
-- decline-comment record, and similar system-narration entries.
CREATE TABLE thread_messages (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    thread_id       INTEGER NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
    role            TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
    author_user_id  INTEGER REFERENCES users(id) ON DELETE SET NULL,
    model_id        TEXT,                       -- set when role='assistant'
    text            TEXT NOT NULL,
    quote           TEXT,                       -- optional selection the user attached
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_thread_messages_thread ON thread_messages (thread_id, created_at);
CREATE INDEX idx_thread_messages_author ON thread_messages (author_user_id, created_at);

-- §5 / §8.6 / §8.9 / §8.11: structured proposed edits. AI-proposed (parsed
-- from <change> blocks per the §18 carryover) or manually authored.
-- stale_since is orthogonal to state: a stale AI proposal stays 'pending'
-- until the contributor acts on the staleness warning per §8.11.
CREATE TABLE changes (
    id                          INTEGER PRIMARY KEY AUTOINCREMENT,
    rfc_slug                    TEXT NOT NULL,
    branch_name                 TEXT NOT NULL,
    thread_id                   INTEGER REFERENCES threads(id) ON DELETE SET NULL,
    source_message_id           INTEGER REFERENCES thread_messages(id) ON DELETE SET NULL,
    kind                        TEXT NOT NULL CHECK (kind IN ('ai', 'manual')),
    state                       TEXT NOT NULL DEFAULT 'pending' CHECK (state IN ('pending', 'accepted', 'declined')),
    original                    TEXT NOT NULL,
    proposed                    TEXT NOT NULL,
    reason                      TEXT,
    was_edited_before_accept    INTEGER NOT NULL DEFAULT 0,
    stale_since                 TEXT,
    acted_by                    INTEGER REFERENCES users(id) ON DELETE SET NULL,
    acted_at                    TEXT,
    commit_sha                  TEXT,
    created_at                  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_changes_scope ON changes (rfc_slug, branch_name, state);
CREATE INDEX idx_changes_thread ON changes (thread_id);