migrations/topic_log.sql

-- Run this in the Supabase SQL editor
-- Tracks which story beats and philosophy threads each user has visited,
-- enabling PREVIOUSLY_DISCUSSED injection, topic navigation, and coverage checks.

CREATE TABLE IF NOT EXISTS public.user_topic_log (
    user_id       UUID        NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    character_id  TEXT        NOT NULL,

    -- 'story' or 'philosophy_thread'
    loop_type     TEXT        NOT NULL,

    -- Stable identifier:
    --   story              → 'ch{chapter_index}_b{beat_index}'  e.g. 'ch0_b3'
    --   philosophy_thread  → thread_index as text               e.g. '5'
    topic_ref     TEXT        NOT NULL,

    -- Human-readable label (from focus field or theme field in JSON)
    topic_label   TEXT        NOT NULL,

    -- Only set for philosophy_thread: the deepest level reached ('light','deep','deepest')
    level         TEXT,

    visit_count   INTEGER     NOT NULL DEFAULT 1,
    first_visited TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_visited  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    PRIMARY KEY (user_id, character_id, loop_type, topic_ref),
    CONSTRAINT chk_tl_loop_type CHECK (loop_type IN ('story', 'philosophy_thread'))
);

CREATE INDEX IF NOT EXISTS idx_topic_log_user
    ON public.user_topic_log (user_id, character_id, loop_type);