-- Asurada Supabase schema (사용자 본인 Postgres 에 적용).
-- brain.db 의 테이블들과 1:1 대응. sync 모듈이 양방향으로 일관성 유지.
--
-- 적용:
--   psql "$ASURADA_DATABASE_URL" -f migrations/supabase/0001_init.sql
--
-- 또는 Supabase Studio SQL 에디터에서 실행.

CREATE SCHEMA IF NOT EXISTS asurada;

-- ── memories ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS asurada.memories (
    id          TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL,
    text        TEXT NOT NULL,
    scope       TEXT NOT NULL,            -- 'user' | 'project' | 'tech'
    priority    TEXT NOT NULL,            -- 'constraint' | 'strong' | 'preference' | 'info'
    source      TEXT NOT NULL,            -- 'user' | 'asurada' | 'imported'
    project     TEXT,
    tech        TEXT,
    metadata    JSONB NOT NULL DEFAULT '{}'::jsonb,
    status      TEXT NOT NULL DEFAULT 'active',  -- active | proposed | archived | deleted
    created_at  TIMESTAMPTZ NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL,
    deleted_at  TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_asurada_memories_user ON asurada.memories(user_id);
CREATE INDEX IF NOT EXISTS idx_asurada_memories_user_status
    ON asurada.memories(user_id, status);
CREATE INDEX IF NOT EXISTS idx_asurada_memories_user_project
    ON asurada.memories(user_id, project);
CREATE INDEX IF NOT EXISTS idx_asurada_memories_updated
    ON asurada.memories(updated_at);

-- ── events (Devist 가 생성, Asurada 가 처리) ────────────────
CREATE TABLE IF NOT EXISTS asurada.events (
    id          TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL,
    project     TEXT NOT NULL,
    event_type  TEXT NOT NULL,
    path        TEXT,
    payload     JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_asurada_events_user_project
    ON asurada.events(user_id, project);
CREATE INDEX IF NOT EXISTS idx_asurada_events_created
    ON asurada.events(created_at);
CREATE INDEX IF NOT EXISTS idx_asurada_events_updated
    ON asurada.events(updated_at);

-- ── advice (Asurada 가 생성, 사용자가 처리) ────────────────
CREATE TABLE IF NOT EXISTS asurada.advice (
    id              TEXT PRIMARY KEY,
    user_id         TEXT NOT NULL,
    project         TEXT NOT NULL,
    text            TEXT NOT NULL,
    severity        TEXT NOT NULL,
    paths           JSONB NOT NULL DEFAULT '[]'::jsonb,
    verifiable      BOOLEAN NOT NULL DEFAULT FALSE,
    state           TEXT NOT NULL DEFAULT 'pending',
    confirmed_at    TIMESTAMPTZ,
    confirmed_by    TEXT,
    metadata        JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at      TIMESTAMPTZ NOT NULL,
    updated_at      TIMESTAMPTZ NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_asurada_advice_user ON asurada.advice(user_id);
CREATE INDEX IF NOT EXISTS idx_asurada_advice_user_pending
    ON asurada.advice(user_id, created_at DESC) WHERE confirmed_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_asurada_advice_updated
    ON asurada.advice(updated_at);

-- ── projects (Devist 레지스트리, Asurada 가 sync) ──────────
CREATE TABLE IF NOT EXISTS asurada.projects (
    user_id     TEXT NOT NULL,
    name        TEXT NOT NULL,
    path        TEXT NOT NULL,
    metadata    JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (user_id, name)
);

CREATE INDEX IF NOT EXISTS idx_asurada_projects_updated
    ON asurada.projects(updated_at);

-- ── heartbeat (데몬 alive 신호 — 대시보드용) ──────────────
-- per-user singleton row. Asurada 데몬이 sync loop 마다 갱신.
CREATE TABLE IF NOT EXISTS asurada.heartbeat (
    user_id    TEXT PRIMARY KEY,
    last_seen  TIMESTAMPTZ NOT NULL,
    version    TEXT NOT NULL DEFAULT '0.1.0'
);
