-- migrate-v6.sql: 회고 시스템 (retrospect) 스키마 + 테이블
-- 실행: docker exec -i bot-postgres psql -U bot -d chatbot < init/migrate-v6.sql

CREATE SCHEMA IF NOT EXISTS retrospect;

CREATE TABLE IF NOT EXISTS retrospect.entries (
    id              BIGSERIAL PRIMARY KEY,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    raw_text        TEXT NOT NULL,
    domain          VARCHAR(20) NOT NULL,       -- work | health | finance
    entry_type      VARCHAR(20) NOT NULL,       -- event | reflection | regret | log
    sentiment       VARCHAR(10) NOT NULL,       -- positive | neutral | negative
    tags            TEXT[] DEFAULT '{}',
    confidence      REAL DEFAULT 0.0,
    source          VARCHAR(20) DEFAULT 'chat',
    reviewed        BOOLEAN DEFAULT FALSE,
    source_post_id  BIGINT,
    username        VARCHAR(100)
);

CREATE INDEX IF NOT EXISTS idx_entries_created ON retrospect.entries (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_entries_domain ON retrospect.entries (domain);
CREATE INDEX IF NOT EXISTS idx_entries_type ON retrospect.entries (entry_type);
CREATE INDEX IF NOT EXISTS idx_entries_reviewed ON retrospect.entries (reviewed) WHERE NOT reviewed;
CREATE INDEX IF NOT EXISTS idx_entries_tags ON retrospect.entries USING GIN(tags);
-- 중복 방지: chat_bridge 재시작 시 같은 post 재처리 방지
CREATE UNIQUE INDEX IF NOT EXISTS idx_entries_post_id ON retrospect.entries (source_post_id)
    WHERE source_post_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS retrospect.reviews (
    id              BIGSERIAL PRIMARY KEY,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    review_type     VARCHAR(10) NOT NULL,       -- daily | weekly | monthly
    period_start    DATE NOT NULL,
    period_end      DATE NOT NULL,
    content         TEXT NOT NULL,
    entry_ids       BIGINT[] DEFAULT '{}',
    model_used      VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS retrospect.patterns (
    id              BIGSERIAL PRIMARY KEY,
    discovered_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    domain          VARCHAR(20) NOT NULL,
    pattern_type    VARCHAR(20) NOT NULL,       -- recurring_mistake | habit | correlation | improvement
    description     TEXT NOT NULL,
    occurrences     INTEGER DEFAULT 1,
    last_seen       DATE NOT NULL,
    status          VARCHAR(20) DEFAULT 'active'
);