-- Conversation (spec §10): channels, messages and threads, reactions, each

-- principal's inbox, and full-text search over messages (§13.1).

CREATE TABLE channels (

    id        TEXT PRIMARY KEY,

    name      TEXT NOT NULL UNIQUE,     -- general, humans, system, a project's name, or dm:A+B

    kind      TEXT NOT NULL,            -- project | shared | direct | system

    project   TEXT,

    purpose   TEXT,

    members   TEXT NOT NULL,            -- JSON array of actors; empty: everyone

    seq       INTEGER NOT NULL

);

CREATE TABLE messages (

    id              TEXT PRIMARY KEY,

    channel         TEXT NOT NULL,

    reply_to        TEXT,

    author          TEXT NOT NULL,      -- the event's actor

    kind            TEXT NOT NULL,

    body            TEXT NOT NULL,

    payload         TEXT,

    mentions        TEXT NOT NULL,      -- JSON array of actors

    refs            TEXT NOT NULL,      -- JSON array

    at_ms           INTEGER NOT NULL,

    seq             INTEGER NOT NULL,

    replies         INTEGER NOT NULL DEFAULT 0,

    redacted        TEXT                -- the reason, once redacted

);

CREATE INDEX messages_channel ON messages(channel, seq);

CREATE INDEX messages_thread ON messages(reply_to, seq);

CREATE TABLE reactions (

    message   TEXT NOT NULL,

    actor     TEXT NOT NULL,

    reaction  TEXT NOT NULL,

    PRIMARY KEY (message, actor, reaction)

);

-- What each principal has to look at: mentions and direct messages for now.

CREATE TABLE inbox (

    principal  TEXT NOT NULL,

    item       TEXT NOT NULL,           -- the message id

    kind       TEXT NOT NULL,           -- mention | dm

    seq        INTEGER NOT NULL,

    delivered  INTEGER NOT NULL DEFAULT 0,

    read       INTEGER NOT NULL DEFAULT 0,

    PRIMARY KEY (principal, item)

);

CREATE VIRTUAL TABLE messages_fts USING fts5(id UNINDEXED, channel UNINDEXED, author UNINDEXED, body, tokenize = 'porter unicode61');