migrations/loop_hierarchy.sql

-- Run this in the Supabase SQL editor
-- Tracks which main loops (Story / Philosophy) are active and in what order,
-- so the system knows which is main_1 (opened first) and main_2 (opened second).
--
-- Ordering rule: order by opened_at ASC → first row = main_1, second = main_2.
-- Max 2 active/suspended main loops per user × character at once.

CREATE TABLE IF NOT EXISTS public.user_active_main_loops (
    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,

    -- Extra reference within the loop type:
    --   story           → NULL (only one story per character)
    --   philosophy_thread → thread_index as text (e.g. '3')
    loop_ref      TEXT,

    -- 'active'    → currently the foreground loop
    -- 'suspended' → auto-suspended (18 h + 2 msg) or pushed to background; re-openable
    -- 'closed'    → completed or user explicitly exited
    status        TEXT        NOT NULL DEFAULT 'active',

    -- When this loop session was first opened (determines main_1 vs main_2)
    opened_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    PRIMARY KEY (user_id, character_id, loop_type),
    CONSTRAINT chk_loop_type   CHECK (loop_type IN ('story', 'philosophy_thread')),
    CONSTRAINT chk_loop_status CHECK (status     IN ('active', 'suspended', 'closed'))
);

CREATE INDEX IF NOT EXISTS idx_loop_hierarchy_user
    ON public.user_active_main_loops (user_id, character_id, status, opened_at);

-- ── Helper view: returns main_1 / main_2 labels per user × character ──────────
-- main_1 = the loop opened first (older), main_2 = the loop opened second.
-- Only considers 'active' and 'suspended' rows (not 'closed').
CREATE OR REPLACE VIEW public.v_main_loop_order AS
SELECT
    user_id,
    character_id,
    loop_type,
    loop_ref,
    status,
    opened_at,
    CASE ROW_NUMBER() OVER (
        PARTITION BY user_id, character_id
        ORDER BY opened_at ASC
    )
        WHEN 1 THEN 'main_1'
        WHEN 2 THEN 'main_2'
    END AS loop_rank
FROM public.user_active_main_loops
WHERE status IN ('active', 'suspended');