-- ============================================================
-- Migration 010: Durable Bot State Snapshots
-- Date: 2026-02-16
-- Purpose: Persist runtime snapshots in Supabase with UUID ownership.
-- ============================================================

CREATE TABLE IF NOT EXISTS bot_state_snapshots (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    state jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE bot_state_snapshots IS 'Periodic runtime snapshot for bot state, aligned with tenant ownership.';
COMMENT ON COLUMN bot_state_snapshots.state IS 'Serialized BotState payload captured for restart recovery.';

CREATE INDEX IF NOT EXISTS idx_bot_state_snapshots_user_created
    ON bot_state_snapshots (user_id, created_at DESC);

ALTER TABLE bot_state_snapshots ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can manage own snapshots" ON bot_state_snapshots;
CREATE POLICY "Users can manage own snapshots" ON bot_state_snapshots
    FOR ALL
    USING (auth.uid() = user_id)
    WITH CHECK (auth.uid() = user_id);