-- Chatbot backend support, milestone M1: chatbot tables.
-- See plans/chatbot_plan_codex.v10.md §4 (Data Model) for the canonical
-- definition. Existing tables retain camelCase column names (per migrations
-- 1-130); new bot_* tables use snake_case to match migrations 127-130.
--
-- Tables created in dependency order:
--   1. bot_sessions      — owner-scoped chatbot conversations
--   2. bot_session_documents  — explicit per-session document scope
--   3. bot_session_folders    — explicit per-session folder scope
--   4. bot_turns          — append-only turn log per session

-- ---------- bot_sessions ----------

CREATE TABLE bot_sessions (
    id          uuid         NOT NULL DEFAULT uuid_generate_v7(),
    client_id   varchar(255) NOT NULL REFERENCES clients(clientId) ON DELETE CASCADE,
    created_by  varchar(255) NOT NULL,
    created_at  timestamptz  NOT NULL DEFAULT NOW(),
    updated_at  timestamptz  NOT NULL DEFAULT NOW(),
    title       text         NOT NULL DEFAULT '',
    state       jsonb        NOT NULL DEFAULT '{}'::jsonb,
    is_deleted  boolean      NOT NULL DEFAULT false,
    CONSTRAINT pk_bot_sessions PRIMARY KEY (id)
);

CREATE INDEX idx_bot_sessions_client_user_active
    ON bot_sessions (client_id, created_by, updated_at DESC)
    WHERE is_deleted = false;

CREATE INDEX idx_bot_sessions_client_active
    ON bot_sessions (client_id, updated_at DESC)
    WHERE is_deleted = false;

-- ---------- bot_session_documents (scope) ----------

CREATE TABLE bot_session_documents (
    session_id  uuid NOT NULL REFERENCES bot_sessions(id) ON DELETE CASCADE,
    document_id uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    CONSTRAINT pk_bot_session_documents PRIMARY KEY (session_id, document_id)
);

CREATE INDEX idx_bsd_document ON bot_session_documents(document_id);

-- ---------- bot_session_folders (scope) ----------

CREATE TABLE bot_session_folders (
    session_id uuid NOT NULL REFERENCES bot_sessions(id) ON DELETE CASCADE,
    folder_id  uuid NOT NULL REFERENCES folders(id) ON DELETE CASCADE,
    CONSTRAINT pk_bot_session_folders PRIMARY KEY (session_id, folder_id)
);

CREATE INDEX idx_bsf_folder ON bot_session_folders(folder_id);

-- ---------- bot_turns ----------
--
-- bot_turns_status_fields_consistent groups session_deleted with errored
-- and abandoned: completion IS NULL AND error IS NOT NULL. This matches
-- plan §4 verbatim.

CREATE TABLE bot_turns (
    session_id          uuid        NOT NULL REFERENCES bot_sessions(id) ON DELETE CASCADE,
    ordinal             int         NOT NULL CHECK (ordinal > 0),
    prompt              text        NOT NULL,
    completion          text,
    status              text        NOT NULL DEFAULT 'in_flight',
    attempt_id          uuid        NOT NULL,
    attempt_started_at  timestamptz NOT NULL DEFAULT NOW(),
    created_at          timestamptz NOT NULL DEFAULT NOW(),
    latency_ms          int,
    tokens_in           int,
    tokens_out          int,
    error               jsonb,
    CONSTRAINT pk_bot_turns PRIMARY KEY (session_id, ordinal),
    CONSTRAINT bot_turns_status_values CHECK (
        status IN ('in_flight', 'completed', 'errored', 'abandoned', 'session_deleted')
    ),
    -- bot_turns_status_fields_consistent enforces the (status, completion,
    -- error) consistency matrix from plan §4. The trailing OR-clause
    -- (`status NOT IN (...)`) is an evaluation-order guard, not a relaxation
    -- of the rule: Postgres evaluates table-level CHECK constraints in
    -- alphabetical order by constraint name, so without this guard a row
    -- with an UNKNOWN status (e.g. 'frobnicated') trips this constraint
    -- before bot_turns_status_values gets a chance to reject the unknown
    -- enum value. With the guard, unknown statuses pass this consistency
    -- check (every branch becomes irrelevant) and bot_turns_status_values
    -- owns the rejection. For every documented status value the plan §4
    -- literal still holds verbatim.
    CONSTRAINT bot_turns_status_fields_consistent CHECK (
        (status = 'in_flight' AND completion IS NULL AND error IS NULL)
        OR (status = 'completed' AND completion IS NOT NULL AND error IS NULL)
        OR (status IN ('errored', 'abandoned', 'session_deleted') AND completion IS NULL AND error IS NOT NULL)
        OR status NOT IN ('in_flight', 'completed', 'errored', 'abandoned', 'session_deleted')
    )
);

CREATE UNIQUE INDEX bot_turns_one_inflight_per_session
    ON bot_turns (session_id)
    WHERE status = 'in_flight';

CREATE INDEX idx_bot_turns_session_completed
    ON bot_turns (session_id, ordinal)
    WHERE status = 'completed';