-- AI assistants (hub spec §13.4, #97): chat-completion endpoints the hub

-- may ask to review a proposal or draft its description. Each lives at

-- one level (site: scope_id 0; an organisation; a repository) and each

-- level may add a prompt. Tokens are sealed with keys/assistants.key.

CREATE TABLE assistants (

    id          INTEGER PRIMARY KEY,

    scope       TEXT NOT NULL CHECK (scope IN ('site', 'org', 'repo')),

    scope_id    INTEGER NOT NULL DEFAULT 0,

    name        TEXT NOT NULL COLLATE NOCASE,

    endpoint    TEXT NOT NULL,

    api         TEXT NOT NULL,

    model       TEXT NOT NULL,

    headers     TEXT NOT NULL DEFAULT '',

    token       BLOB NOT NULL,

    enabled     INTEGER NOT NULL DEFAULT 1,

    budget      INTEGER NOT NULL DEFAULT 120000,

    created_ms  INTEGER NOT NULL,

    updated_ms  INTEGER NOT NULL,

    UNIQUE (scope, scope_id, name)

);

CREATE TABLE assistant_prompts (

    scope       TEXT NOT NULL CHECK (scope IN ('site', 'org', 'repo')),

    scope_id    INTEGER NOT NULL DEFAULT 0,

    prompt      TEXT NOT NULL,

    updated_ms  INTEGER NOT NULL,

    PRIMARY KEY (scope, scope_id)

);