-- ============================================================
-- PostgreSQL 最適化用 SQL
-- 初回デプロイ時 or インデックス未作成時に実行
-- ============================================================

-- ── players テーブル ──────────────────────────────
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_players_trip
    ON players (trip) WHERE is_spectator = 0;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_players_name
    ON players (name) WHERE is_spectator = 0;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_players_game_id
    ON players (game_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_players_trip_name
    ON players (trip, name) WHERE is_spectator = 0;

-- 同村検索の高速化（game_id IN (...) + is_spectator）
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_players_game_spec
    ON players (game_id, is_spectator);

-- ── games テーブル ────────────────────────────────
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_games_started_at
    ON games (started_at DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_games_log_id
    ON games (log_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_games_village_name
    ON games (village_name);

-- village_recent 高速化用（村名ごとの最新 started_at）
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_games_village_started
    ON games (village_name, started_at DESC);

-- ── rank_cache_json ──────────────────────────────
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_rank_cache_lookup
    ON rank_cache_json (scope, ym, kind, key_type);

-- ── player_ratings ───────────────────────────────
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ratings_rank
    ON player_ratings (games, rating DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ratings_trip
    ON player_ratings (trip);

-- ── wiki / comments ──────────────────────────────
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_wiki_key_type_value
    ON wiki (key_type, key_value);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_comments_key
    ON comments (key_type, key_value, created_at DESC)
    WHERE deleted = 0;

-- ── wiki_hist ────────────────────────────────────
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_wiki_hist_key
    ON wiki_hist (key_type, key_value, created_at DESC);

-- ── player_profile ───────────────────────────────
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_player_profile_trip
    ON player_profile (trip);

-- ── trip chain ───────────────────────────────────
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ptcs_span
    ON plain_trip_chain_summary (span_seconds DESC, games_total DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ptcn_plain_trip
    ON plain_trip_chain_names (plain_trip, games_total DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ntc_rep_trip
    ON name_trip_chain (representative_trip);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ntc_latest_trip
    ON name_trip_chain (latest_trip);

-- ============================================================
-- ANALYZE で統計情報を更新
-- ============================================================
ANALYZE players;
ANALYZE games;
ANALYZE rank_cache_json;
ANALYZE player_ratings;
ANALYZE wiki;
ANALYZE comments;
ANALYZE wiki_hist;
ANALYZE player_profile;
ANALYZE plain_trip_chain_summary;
ANALYZE plain_trip_chain_names;
ANALYZE name_trip_chain;
