-- 03-memory-tables.sql
-- 记忆管理模块：PostgreSQL 主存储 + Redis 缓存层
-- 幂等执行，IF NOT EXISTS

-- pgvector 扩展（容器启动时已通过 docker-compose entrypoint 自动安装）
CREATE EXTENSION IF NOT EXISTS vector;

-- ============================================================
-- L0: 原始对话消息
-- ============================================================
CREATE TABLE IF NOT EXISTS memory_messages (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    flow_id     UUID NOT NULL REFERENCES flow_definitions(id) ON DELETE CASCADE,
    session_id  UUID NOT NULL,
    role        VARCHAR(20) NOT NULL,
    content     TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_memory_messages_session ON memory_messages(user_id, flow_id, session_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_memory_messages_user_flow ON memory_messages(user_id, flow_id, created_at DESC);

-- ============================================================
-- L1: 结构化记忆原子
-- ============================================================
CREATE TABLE IF NOT EXISTS memory_atoms (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    flow_id     UUID REFERENCES flow_definitions(id) ON DELETE SET NULL,
    atom_type   VARCHAR(20) NOT NULL,
    content     TEXT NOT NULL,
    priority    SMALLINT DEFAULT 50,
    source_session_id UUID,
    metadata    JSONB DEFAULT '{}',
    embedding   vector(1536),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_memory_atoms_user ON memory_atoms(user_id, atom_type, priority DESC);
CREATE INDEX IF NOT EXISTS idx_memory_atoms_embedding ON memory_atoms USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- ============================================================
-- L2: 场景块
-- ============================================================
CREATE TABLE IF NOT EXISTS memory_scenes (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    flow_id     UUID REFERENCES flow_definitions(id) ON DELETE SET NULL,
    scene_name  VARCHAR(200) NOT NULL,
    summary     TEXT NOT NULL,
    heat        INTEGER DEFAULT 0,
    content     JSONB DEFAULT '{}',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_memory_scenes_user ON memory_scenes(user_id, flow_id, heat DESC);

-- ============================================================
-- L3: 用户画像
-- ============================================================
CREATE TABLE IF NOT EXISTS memory_personas (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    content     JSONB NOT NULL DEFAULT '{}',
    raw_text    TEXT DEFAULT '',
    version     INTEGER DEFAULT 1,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 会话元数据
-- ============================================================
CREATE TABLE IF NOT EXISTS memory_sessions (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    flow_id     UUID NOT NULL REFERENCES flow_definitions(id) ON DELETE CASCADE,
    session_id  UUID NOT NULL,
    flow_name   VARCHAR(200) DEFAULT '',
    message_count INTEGER DEFAULT 0,
    last_active_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, flow_id, session_id)
);

CREATE INDEX IF NOT EXISTS idx_memory_sessions_user ON memory_sessions(user_id, last_active_at DESC);