-- ============================================================
-- Python API が書き込むテーブルの DDL
-- 既存テーブルがあればスキップ（IF NOT EXISTS）
-- ============================================================

-- ── player_profile ──
CREATE TABLE IF NOT EXISTS player_profile (
    trip         TEXT PRIMARY KEY,
    self_intro   TEXT NOT NULL DEFAULT '',
    x_id         TEXT DEFAULT NULL,
    x_display    TEXT DEFAULT NULL,
    updated_at   BIGINT DEFAULT 0
);

-- ── wiki ──
CREATE TABLE IF NOT EXISTS wiki (
    id              SERIAL PRIMARY KEY,
    key             TEXT NOT NULL DEFAULT '',
    key_type        TEXT NOT NULL,
    key_value       TEXT NOT NULL,
    body            TEXT NOT NULL DEFAULT '',
    updated_at      BIGINT DEFAULT 0,
    updated_at_text TEXT DEFAULT '',
    UNIQUE (key_type, key_value)
);

-- ── wiki_hist ──
CREATE TABLE IF NOT EXISTS wiki_hist (
    id           SERIAL PRIMARY KEY,
    key_type     TEXT NOT NULL,
    key_value    TEXT NOT NULL,
    body         TEXT NOT NULL DEFAULT '',
    editor_name  TEXT DEFAULT '',
    editor_trip  TEXT DEFAULT '',
    created_at   BIGINT DEFAULT 0
);

-- ── comments ──
CREATE TABLE IF NOT EXISTS comments (
    id           SERIAL PRIMARY KEY,
    key_type     TEXT NOT NULL,
    key_value    TEXT NOT NULL,
    body         TEXT NOT NULL DEFAULT '',
    author_name  TEXT DEFAULT '',
    author_trip  TEXT DEFAULT '',
    created_at   BIGINT DEFAULT 0,
    deleted      SMALLINT NOT NULL DEFAULT 0
);

-- ── bbs_posts ──
CREATE TABLE IF NOT EXISTS bbs_posts (
    id           SERIAL PRIMARY KEY,
    body         TEXT NOT NULL DEFAULT '',
    author_name  TEXT DEFAULT '',
    author_trip  TEXT DEFAULT '',
    created_at   BIGINT DEFAULT 0,
    deleted      SMALLINT NOT NULL DEFAULT 0
);

-- ── rank_cache_json ──
-- ★ バッチの UPSERT に必要なユニーク制約
CREATE TABLE IF NOT EXISTS rank_cache_json (
    id         SERIAL PRIMARY KEY,
    scope      TEXT NOT NULL,           -- 'all' / 'month' / 'year'
    ym         TEXT NOT NULL,           -- '2025-01' / '2025' / 'all'
    kind       TEXT NOT NULL,           -- 'games' / 'win_rate' / ...
    key_type   TEXT DEFAULT NULL,       -- 'name' / 'trip' / NULL
    payload    TEXT NOT NULL DEFAULT '{}',
    total      INTEGER NOT NULL DEFAULT 0,
    updated_at BIGINT DEFAULT 0
);

-- ユニーク制約（key_type が NULL の場合も区別できるように COALESCE を使う）
-- PostgreSQL は UNIQUE (a, b, NULL) を同じ行と見なさないので、
-- 関数ユニークインデックスを使う
CREATE UNIQUE INDEX IF NOT EXISTS uq_rank_cache_json_lookup
    ON rank_cache_json (scope, ym, kind, COALESCE(key_type, '__NULL__'));

-- ── player_ratings ──
CREATE TABLE IF NOT EXISTS player_ratings (
    trip       TEXT PRIMARY KEY,
    rating     NUMERIC(8,2) NOT NULL DEFAULT 1500.00,
    games      INTEGER NOT NULL DEFAULT 0,
    main_name  TEXT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS personal_stats_cache (
    key_type           TEXT   NOT NULL,
    key_value          TEXT   NOT NULL,
    watermark_game_id  BIGINT NOT NULL DEFAULT 0,
    stats_json         TEXT   NOT NULL DEFAULT '{}',
    game_ids_json      TEXT   NOT NULL DEFAULT '[]',
    partners_json      TEXT   NOT NULL DEFAULT '{}',
    first_ts           BIGINT DEFAULT NULL,
    last_ts            BIGINT DEFAULT NULL,
    updated_at         BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (key_type, key_value)
);
ALTER TABLE personal_stats_cache ADD COLUMN IF NOT EXISTS partners_json TEXT NOT NULL DEFAULT '{}';

CREATE TABLE IF NOT EXISTS personal_search_priority (
    key_type         TEXT NOT NULL,
    key_value        TEXT NOT NULL,
    search_count     INTEGER NOT NULL DEFAULT 0,
    last_searched_at BIGINT NOT NULL DEFAULT 0,
    last_cache_hit   SMALLINT NOT NULL DEFAULT 0,
    PRIMARY KEY (key_type, key_value)
);
CREATE INDEX IF NOT EXISTS idx_personal_search_priority_hot
    ON personal_search_priority (last_searched_at DESC, search_count DESC);

CREATE TABLE IF NOT EXISTS player_talks (
    id          BIGSERIAL PRIMARY KEY,
    game_id     INTEGER REFERENCES games(id) ON DELETE CASCADE,
    log_id      INTEGER NOT NULL,
    game_index  INTEGER NOT NULL DEFAULT 0,
    seq         INTEGER NOT NULL,
    created_at  BIGINT DEFAULT NULL,
    day_label   TEXT NOT NULL DEFAULT '',
    phase       TEXT NOT NULL DEFAULT '',
    from_user   TEXT NOT NULL DEFAULT '',
    to_user     TEXT NOT NULL DEFAULT '',
    name        TEXT NOT NULL DEFAULT '',
    trip        TEXT NOT NULL DEFAULT '',
    message     TEXT NOT NULL DEFAULT ''
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_player_talks_log_game_seq
    ON player_talks (log_id, game_index, seq);
CREATE INDEX IF NOT EXISTS idx_player_talks_trip_created
    ON player_talks (trip, created_at DESC) WHERE trip <> '';
CREATE INDEX IF NOT EXISTS idx_player_talks_name_created
    ON player_talks (name, created_at DESC) WHERE name <> '';
CREATE INDEX IF NOT EXISTS idx_player_talks_game
    ON player_talks (game_id, seq);

CREATE TABLE IF NOT EXISTS player_rag_analysis_cache (
    key_type      TEXT NOT NULL,
    key_value     TEXT NOT NULL,
    since_ts      BIGINT NOT NULL,
    source_hash   TEXT NOT NULL,
    analysis_json TEXT NOT NULL DEFAULT '{}',
    updated_at    BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (key_type, key_value, since_ts)
);
