-- Battle DB Schema v1
-- SQLite, WAL mode, foreign keys enabled
-- 위치: orchestration/data/battle.db

PRAGMA journal_mode=WAL;
PRAGMA foreign_keys=ON;

-- ============================================================
-- 1. users
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
    id              TEXT PRIMARY KEY,                -- UUID (BattleShim 생성)
    display_name    TEXT,                            -- 유저 입력 or null
    level           INTEGER NOT NULL DEFAULT 1,
    exp             INTEGER NOT NULL DEFAULT 0,
    preferred_locale TEXT NOT NULL DEFAULT 'ko',
    settings        TEXT NOT NULL DEFAULT '{}',      -- UserSettings JSON
    device_hash     TEXT,                            -- UA + screen hash
    created_at      REAL NOT NULL,                   -- unix timestamp
    last_seen       REAL NOT NULL,
    updated_at      REAL NOT NULL
);

-- ============================================================
-- 2. games (파이프라인 생성 게임 레지스트리)
-- ============================================================
CREATE TABLE IF NOT EXISTS games (
    game_id         TEXT PRIMARY KEY,                -- game-4c664179
    request_id      TEXT,                            -- 파이프라인 요청 ID
    name            TEXT,
    genre           TEXT,
    adapters_used   TEXT NOT NULL DEFAULT '[]',      -- JSON array of adapter ids
    battle_slot_count INTEGER DEFAULT 0,
    version         TEXT NOT NULL DEFAULT '1.0',
    serving_url     TEXT,                            -- /p/game-4c664179/
    created_at      REAL NOT NULL,
    play_count      INTEGER NOT NULL DEFAULT 0,      -- 캐시 집계
    avg_completion  REAL NOT NULL DEFAULT 0.0        -- 캐시 집계
);

-- ============================================================
-- 3. game_sessions (1회 플레이스루)
-- ============================================================
CREATE TABLE IF NOT EXISTS game_sessions (
    session_id          TEXT PRIMARY KEY,            -- UUID
    user_id             TEXT NOT NULL,
    game_id             TEXT NOT NULL,
    started_at          REAL NOT NULL,
    ended_at            REAL,                        -- NULL = 진행 중
    end_reason          TEXT,                        -- completed/abandoned/timeout
    ending_id           TEXT,                        -- 도달 엔딩 ID
    chapters_completed  INTEGER NOT NULL DEFAULT 0,
    battles_played      INTEGER NOT NULL DEFAULT 0,
    battles_won         INTEGER NOT NULL DEFAULT 0,
    last_node_id        TEXT,                        -- 마지막 서사 위치
    drop_node_id        TEXT,                        -- 이탈 지점
    client_build        TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (game_id) REFERENCES games(game_id)
);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON game_sessions(user_id, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_sessions_game ON game_sessions(game_id, started_at DESC);

-- ============================================================
-- 4. battles
-- ============================================================
CREATE TABLE IF NOT EXISTS battles (
    battle_id           TEXT PRIMARY KEY,
    user_id             TEXT,
    session_id          TEXT,                        -- game_sessions 연결
    game_id             TEXT,                        -- 비정규화 (세션 없이도 조회)
    adapter             TEXT NOT NULL,
    battle_slot_id      TEXT,                        -- BATTLE_SLOT_001 등
    scenario_node_id    TEXT,                        -- 서사 내 위치
    result              TEXT NOT NULL DEFAULT 'unknown',  -- win/lose/unknown
    remaining_hp        REAL,
    turns_used          INTEGER,
    total_damage        REAL,
    enemy_level         INTEGER DEFAULT 1,
    exp_gained          INTEGER DEFAULT 0,
    duration_ms         REAL,                        -- ms 단위 통일
    player_stats        TEXT,                        -- BattleStats JSON
    enemy_stats         TEXT,                        -- BattleStats JSON
    special_conditions  TEXT DEFAULT '[]',           -- JSON array of strings
    input_count         INTEGER,
    is_autoplay         INTEGER NOT NULL DEFAULT 0,  -- SQLite boolean
    client_build        TEXT,
    raw_data            TEXT DEFAULT '{}',           -- 어댑터별 추가 데이터
    created_at          REAL NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (session_id) REFERENCES game_sessions(session_id),
    FOREIGN KEY (game_id) REFERENCES games(game_id)
);
CREATE INDEX IF NOT EXISTS idx_battles_user ON battles(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_battles_game ON battles(game_id, adapter, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_battles_session ON battles(session_id);

-- ============================================================
-- 5. events (범용 이벤트 로그)
-- ============================================================
CREATE TABLE IF NOT EXISTS events (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         TEXT,
    session_id      TEXT,
    game_id         TEXT,
    event_type      TEXT NOT NULL,                   -- EventType enum
    event_data      TEXT NOT NULL DEFAULT '{}',      -- 이벤트별 JSON
    created_at      REAL NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_events_game_type ON events(game_id, event_type, created_at);
CREATE INDEX IF NOT EXISTS idx_events_session ON events(session_id, created_at);

-- ============================================================
-- 6. schema_version (마이그레이션 추적)
-- ============================================================
CREATE TABLE IF NOT EXISTS schema_version (
    version     INTEGER PRIMARY KEY,
    applied_at  REAL NOT NULL,
    description TEXT
);
INSERT OR IGNORE INTO schema_version VALUES (1, strftime('%s','now'), 'Initial schema');

-- ============================================================
-- 7. error_cases (에러-해결 로그)
-- ============================================================
CREATE TABLE IF NOT EXISTS error_cases (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    source          TEXT NOT NULL,           -- subagent/e2e/server/exec/manual
    task_label      TEXT,                    -- 서브에이전트 label
    adapter         TEXT,                    -- 관련 어댑터
    phase           TEXT,                    -- 파이프라인 단계
    model           TEXT,                    -- 사용 모델
    error_type      TEXT NOT NULL,           -- 분류 태그
    error_message   TEXT NOT NULL,           -- 원문 에러 메시지
    error_context   TEXT,                    -- 주변 컨텍스트
    resolution      TEXT,                    -- 해결 방법 서술
    resolution_type TEXT,                    -- code_fix/model_switch/approach_change/config/retry/prompt_constraint
    resolution_diff TEXT,                    -- 변경 내용
    resolved        INTEGER DEFAULT 0,       -- SQLite boolean
    iterations      INTEGER DEFAULT 1,       -- 해결까지 시도 횟수
    time_to_resolve_ms REAL,
    created_at      REAL NOT NULL,
    resolved_at     REAL,
    embedding       BLOB                     -- 향후 sqlite-vec용
);
CREATE INDEX IF NOT EXISTS idx_errors_type ON error_cases(error_type, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_errors_source ON error_cases(source, resolved);

-- ============================================================
-- Migration v2: Session Restore + Analytics
-- ============================================================
-- Note: Migration is now handled entirely in db.py _apply_migrations()
-- This ensures ALTER TABLE runs before schema_version is updated
