-- ============================================================
-- 人狼戦績 PostgreSQL スキーマ（フルセット）
-- サーバA（解析用）・サーバB（API用）共通
-- 初回: psql -U zinro -d zinro_meta -f schema_pg.sql
-- ============================================================

-- ── games ──
CREATE TABLE IF NOT EXISTS games (
    id            SERIAL PRIMARY KEY,
    log_id        INTEGER NOT NULL,
    game_index    INTEGER NOT NULL DEFAULT 0,
    created_at    BIGINT,
    started_at    BIGINT,
    finished_at   BIGINT,
    village_name  TEXT    NOT NULL DEFAULT '',
    haiyaku       TEXT    NOT NULL DEFAULT '',
    haiyaku_class TEXT    NOT NULL DEFAULT '通常',
    rm_name       TEXT    DEFAULT '',
    game_time     INTEGER DEFAULT 0,
    wait_time     INTEGER DEFAULT 0,
    review_time   INTEGER DEFAULT 0,
    tsuiho_json   TEXT    DEFAULT '',
    raw_line      TEXT    DEFAULT ''
);

CREATE INDEX IF NOT EXISTS idx_games_log_id          ON games (log_id);
CREATE UNIQUE INDEX IF NOT EXISTS uq_games_log_id_game_index ON games (log_id, game_index);
CREATE INDEX IF NOT EXISTS idx_games_started_at       ON games (started_at DESC);
CREATE INDEX IF NOT EXISTS idx_games_village_name     ON games (village_name);
CREATE INDEX IF NOT EXISTS idx_games_village_started   ON games (village_name, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_games_haiyaku_class    ON games (haiyaku_class);

-- ── players ──
CREATE TABLE IF NOT EXISTS players (
    id            SERIAL PRIMARY KEY,
    game_id       INTEGER NOT NULL REFERENCES games(id),
    log_id        INTEGER NOT NULL,
    name          TEXT    NOT NULL DEFAULT '',
    trip          TEXT    DEFAULT '',
    role          TEXT    DEFAULT '',
    win           INTEGER NOT NULL DEFAULT 0,
    talk_count    INTEGER DEFAULT 0,
    live_days     INTEGER DEFAULT 0,
    is_spectator  INTEGER NOT NULL DEFAULT 0,
    tsuiho_flag   INTEGER NOT NULL DEFAULT 0,
    created_at    BIGINT  DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_players_game_id     ON players (game_id);
CREATE INDEX IF NOT EXISTS idx_players_trip        ON players (trip) WHERE is_spectator = 0;
CREATE INDEX IF NOT EXISTS idx_players_name        ON players (name) WHERE is_spectator = 0;
CREATE INDEX IF NOT EXISTS idx_players_trip_name   ON players (trip, name) WHERE is_spectator = 0;
CREATE INDEX IF NOT EXISTS idx_players_game_spec   ON players (game_id, is_spectator);
CREATE INDEX IF NOT EXISTS idx_players_tsuiho      ON players (tsuiho_flag) WHERE tsuiho_flag = 1;

-- ── 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 ──
CREATE TABLE IF NOT EXISTS rank_cache_json (
    cache_key  TEXT NOT NULL PRIMARY KEY,
    scope      TEXT,
    ym         TEXT,
    kind       TEXT,
    key_type   TEXT,
    payload    TEXT,
    total      BIGINT,
    built_at   BIGINT,
    updated_at BIGINT DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_rank_cache_scope ON rank_cache_json (scope, kind, ym);
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
);

-- ── trip chain 関連 ──
CREATE TABLE IF NOT EXISTS plain_trip_chain_summary (
    plain_trip     TEXT PRIMARY KEY,
    games_total    INTEGER NOT NULL DEFAULT 0,
    name_count     INTEGER NOT NULL DEFAULT 0,
    first_ts       BIGINT DEFAULT 0,
    last_ts        BIGINT DEFAULT 0,
    span_seconds   BIGINT DEFAULT 0,
    plain_games    INTEGER NOT NULL DEFAULT 0,
    fixed_games    INTEGER NOT NULL DEFAULT 0,
    first_plain_ts BIGINT DEFAULT NULL,
    first_fixed_ts BIGINT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS plain_trip_chain_names (
    id          SERIAL PRIMARY KEY,
    plain_trip  TEXT NOT NULL,
    name        TEXT NOT NULL DEFAULT '',
    games_total INTEGER NOT NULL DEFAULT 0,
    first_ts    BIGINT DEFAULT 0,
    last_ts     BIGINT DEFAULT 0,
    plain_games INTEGER NOT NULL DEFAULT 0,
    fixed_games INTEGER NOT NULL DEFAULT 0,
    first_plain_ts BIGINT DEFAULT NULL,
    first_fixed_ts BIGINT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS name_trip_chain (
    id                  SERIAL PRIMARY KEY,
    representative_trip TEXT,
    latest_trip         TEXT,
    first_ts            BIGINT DEFAULT 0,
    last_ts             BIGINT DEFAULT 0,
    games_total         INTEGER NOT NULL DEFAULT 0,
    trip_count          INTEGER NOT NULL DEFAULT 0
);

-- ── バッチ用 ──
CREATE TABLE IF NOT EXISTS batch_state (
    key        TEXT PRIMARY KEY,
    value_int  BIGINT NOT NULL DEFAULT 0,
    value_text TEXT   NOT NULL DEFAULT '',
    updated_at BIGINT NOT NULL DEFAULT 0
);

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 '[]',
    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)
);
