#!/usr/bin/env python3
"""人狼戦績 API — 1ファイル版

PHP の api.php を Python (FastAPI + asyncpg) に移植。
全34エンドポイントを1ファイルに統合。

起動:
  uvicorn api:app --host 0.0.0.0 --port 8000
  uvicorn api:app --host 0.0.0.0 --port 8000 --workers 4  # 本番

PHP互換:
  /api.php?mode=xxx&... → /xxx?... に自動リライト
"""

from __future__ import annotations

# ─── 標準ライブラリ ─────────────────────────────────
import asyncio
import base64
import hashlib
import html as html_mod
import json as json_mod
import logging
import os
import re
import sys
import time
import traceback
from collections import defaultdict
from contextlib import asynccontextmanager
from dataclasses import dataclass
from datetime import datetime, timezone, timedelta
from functools import lru_cache
from typing import Any
from urllib.parse import urlencode, parse_qs
from urllib.request import Request as UrlRequest, urlopen

# ─── サードパーティ ─────────────────────────────────
import asyncpg
from fastapi import FastAPI, Depends, Query, HTTPException, Request
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import ORJSONResponse
from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials
from jose import jwt, JWTError
from pydantic import BaseModel
from pydantic_settings import BaseSettings
from starlette.middleware.base import BaseHTTPMiddleware
from starlette.types import ASGIApp, Receive, Scope, Send

# crypt: Python 3.13 で削除されたため passlib にフォールバック
try:
    import crypt as _crypt_mod
    def _crypt_compat(word: str, salt: str) -> str:
        return _crypt_mod.crypt(word, salt)
except ImportError:
    from passlib.hash import des_crypt
    def _crypt_compat(word: str, salt: str) -> str:
        return des_crypt.using(salt=salt[:2]).hash(word)


# ╔═══════════════════════════════════════════════════════╗
# ║  1. 設定                                              ║
# ╚═══════════════════════════════════════════════════════╝

class Settings(BaseSettings):
    database_url: str = "postgresql://jinro_user:password@localhost:5432/jinro_db"
    app_env: str = "development"
    session_secret: str = "CHANGE-ME-IN-PRODUCTION-64-chars"
    notice_file: str = "../notice.txt"
    openai_api_key: str = ""
    rag_llm_model: str = "gpt-4.1-mini"
    db_pool_min: int = 5
    db_pool_max: int = 20
    jwt_algorithm: str = "HS256"
    jwt_expire_hours: int = 168
    model_config = {"env_file": ".env", "env_file_encoding": "utf-8"}

@lru_cache
def get_settings() -> Settings:
    return Settings()


# ╔═══════════════════════════════════════════════════════╗
# ║  2. DB コネクションプール                               ║
# ╚═══════════════════════════════════════════════════════╝

_pool: asyncpg.Pool | None = None

async def init_pool() -> asyncpg.Pool:
    global _pool
    if _pool is not None:
        return _pool
    s = get_settings()
    _pool = await asyncpg.create_pool(
        dsn=s.database_url,
        min_size=s.db_pool_min,
        max_size=s.db_pool_max,
        statement_cache_size=256,
        server_settings={"timezone": "UTC", "statement_timeout": "30000"},
    )
    return _pool

async def close_pool() -> None:
    global _pool
    if _pool:
        await _pool.close()
        _pool = None

def get_pool() -> asyncpg.Pool:
    if _pool is None:
        raise RuntimeError("DB pool not initialized")
    return _pool

RUNTIME_DDL = """
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)
);
"""

async def ensure_runtime_tables(pool: asyncpg.Pool) -> None:
    async with pool.acquire() as c:
        await c.execute(RUNTIME_DDL)


# ╔═══════════════════════════════════════════════════════╗
# ║  3. ユーティリティ                                      ║
# ╚═══════════════════════════════════════════════════════╝

JST = timezone(timedelta(hours=9))
_security = HTTPBearer(auto_error=False)

# --- JWT ---
def create_token(trip: str, name: str) -> str:
    s = get_settings()
    return jwt.encode(
        {"trip": trip, "name": name, "exp": int(time.time()) + s.jwt_expire_hours * 3600},
        s.session_secret, algorithm=s.jwt_algorithm,
    )

def decode_token(token: str) -> dict[str, Any] | None:
    s = get_settings()
    try:
        return jwt.decode(token, s.session_secret, algorithms=[s.jwt_algorithm])
    except JWTError:
        return None

async def optional_login(creds: HTTPAuthorizationCredentials | None = Depends(_security)) -> dict | None:
    if creds is None: return None
    p = decode_token(creds.credentials)
    return {"trip": p["trip"], "name": p.get("name", "")} if p else None

async def require_login(creds: HTTPAuthorizationCredentials | None = Depends(_security)) -> dict:
    if creds is None:
        raise HTTPException(401, "ログインが必要です。")
    p = decode_token(creds.credentials)
    if p is None:
        raise HTTPException(401, "トークンが無効です。")
    return {"trip": p["trip"], "name": p.get("name", "")}

# --- トリップ計算 ---
def normalize_trip_key_input(raw: str) -> str:
    """キー単体、#key、name#key、##secure、name##secure を受理する。"""
    value = raw.strip().replace("＃", "#")
    if not value:
        return ""
    if value.startswith("##"):
        return value[1:]
    if value.startswith("#"):
        return value[1:]
    if "##" in value:
        return "#" + value.split("##", 1)[1]
    if "#" in value:
        return value.split("#", 1)[1]
    return value

def calc_trip_from_key(key: str) -> str:
    key = normalize_trip_key_input(key)
    if not key: return ""
    try:
        key_sjis = key.encode("shift_jis")
    except UnicodeEncodeError:
        key_sjis = key.encode("utf-8")
    if len(key_sjis) >= 12:
        mark = chr(key_sjis[0]) if key_sjis else ""
        if mark in ("#", "$"):
            m = re.match(rb"^#([0-9a-fA-F]{16})([./0-9A-Za-z]{0,2})", key_sjis)
            if m:
                trip = _crypt_compat(bytes.fromhex(m.group(1).decode("ascii")).decode("latin-1"), "H.")[-12:]
            else:
                trip = "???"
        else:
            trip = base64.b64encode(hashlib.sha1(key_sjis).digest()).decode("ascii")[:12]
            trip = trip.replace("+", ".").replace("/", "_")
    else:
        salt_src = key + "H."
        rmap = {":":"A",";":"B","<":"C","=":"D",">":"E","?":"F","@":"G",
                "[":"a","\\":"b","]":"c","^":"d","_":"e","`":"f"}
        safe = ""
        for ch in salt_src[1:3]:
            if ch < "." or ch > "z": safe += "."
            elif ch in rmap: safe += rmap[ch]
            else: safe += ch
        trip = _crypt_compat(key, safe)[-10:]
    return "◆" + trip

def expand_trip(name: str, trip_key: str) -> str:
    return calc_trip_from_key(trip_key)

# --- 正規化 ---
_ZENKAKU = "０１２３４５６７８９ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ"
_HANKAKU = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
_ZH_TABLE = str.maketrans(_ZENKAKU, _HANKAKU)

def normalize_plain_trip(trip: str) -> str:
    trip = trip.strip()
    if not trip: return ""
    return re.sub(r"^[◆＃#★☆]+", "", trip).translate(_ZH_TABLE).strip()

# --- 時刻ヘルパ ---
def ts_to_jst_date(ts: int) -> str:
    return datetime.fromtimestamp(ts, tz=JST).strftime("%Y-%m-%d")
def ts_to_jst_ym(ts: int) -> str:
    return datetime.fromtimestamp(ts, tz=JST).strftime("%Y-%m")
def ts_to_jst_hour(ts: int) -> int:
    return datetime.fromtimestamp(ts, tz=JST).hour
def ts_to_jst_weekday(ts: int) -> int:
    return (datetime.fromtimestamp(ts, tz=JST).weekday() + 1) % 7
def clamp(v: int, lo: int, hi: int) -> int:
    return max(lo, min(hi, v))

# --- サニタイズ ---
def sanitize_text(text: str, max_length: int = 10000) -> str:
    if not text: return ""
    text = text.replace("\x00", "")
    text = re.sub(r"[\x01-\x08\x0b\x0c\x0e-\x1f\x7f]", "", text)
    return text[:max_length].strip()

def sanitize_html_text(text: str, max_length: int = 10000) -> str:
    return html_mod.escape(sanitize_text(text, max_length))

def sanitize_wiki_body(body: str, max_length: int = 50000) -> str:
    return sanitize_text(body, max_length)

def validate_key_value(kv: str) -> bool:
    return bool(kv) and kv not in ("undefined", "null", "None") and len(kv) <= 500

# --- Pydantic モデル ---
class LoginRequest(BaseModel):
    name: str = ""; trip_key: str
class MeResponse(BaseModel):
    ok: bool = True; logged_in: bool; trip: str | None = None; name: str | None = None
class ProfileUpdateRequest(BaseModel):
    self_intro: str = ""; x_id: str = ""; x_display: str = ""
class WikiSaveRequest(BaseModel):
    key_type: str; key_value: str; body: str
class CommentAddRequest(BaseModel):
    key_type: str; key_value: str; body: str
class MultiTripRequest(BaseModel):
    trips: str
class BbsPostRequest(BaseModel):
    name: str; body: str; trip_key: str = ""; mode: str = ""
    model_config = {"extra": "ignore"}


# ╔═══════════════════════════════════════════════════════╗
# ║  4. レート制限                                         ║
# ╚═══════════════════════════════════════════════════════╝

@dataclass
class _Bucket:
    tokens: float; last_refill: float

class RateLimiter:
    def __init__(self, rate: float = 10.0, burst: int = 30, cleanup_interval: int = 300):
        self.rate = rate; self.burst = burst
        self.cleanup_interval = cleanup_interval
        self._buckets: dict[str, _Bucket] = {}
        self._last_cleanup = time.monotonic()

    def allow(self, key: str, cost: float = 1.0) -> bool:
        now = time.monotonic()
        if key not in self._buckets:
            self._buckets[key] = _Bucket(tokens=float(self.burst), last_refill=now)
        b = self._buckets[key]
        b.tokens = min(float(self.burst), b.tokens + (now - b.last_refill) * self.rate)
        b.last_refill = now
        if b.tokens >= cost:
            b.tokens -= cost; return True
        return False

    def cleanup(self) -> None:
        now = time.monotonic()
        if now - self._last_cleanup < self.cleanup_interval: return
        self._last_cleanup = now
        cutoff = now - self.cleanup_interval * 2
        for k in [k for k, b in self._buckets.items() if b.last_refill < cutoff]:
            del self._buckets[k]

_write_limiter = RateLimiter(rate=2.0, burst=10)
_read_limiter = RateLimiter(rate=20.0, burst=60)
_WRITE_PATHS = frozenset({"/login", "/profile_update", "/wiki_save", "/comment_add", "/multi_trip", "/bbs"})

def _get_client_ip(scope: Scope) -> str:
    headers = dict(scope.get("headers", []))
    xff = headers.get(b"x-forwarded-for", b"").decode("latin-1")
    if xff: return xff.split(",")[0].strip()
    xri = headers.get(b"x-real-ip", b"").decode("latin-1")
    if xri: return xri.strip()
    c = scope.get("client")
    return c[0] if c else "unknown"


# ╔═══════════════════════════════════════════════════════╗
# ║  5. ミドルウェア                                       ║
# ╚═══════════════════════════════════════════════════════╝

MODE_TO_PATH: dict[str, str] = {
    "ping": "/ping", "login": "/login", "logout": "/logout", "me": "/me",
    "personal": "/personal", "personal_summary": "/personal_summary",
    "village_detail": "/village_detail",
    "village_recent": "/village_recent", "name_chain_list": "/name_chain_list",
    "trip_chain_rank": "/trip_chain_rank", "trip_chain_detail": "/trip_chain_detail",
    "trip_lineage_index": "/trip_lineage_index", "trip_lineage": "/trip_lineage",
    "trip_lineage_search": "/trip_lineage_search",
    "ai_eval_job": "/ai_eval_job", "ai_eval_submit": "/ai_eval_submit",
    "vote_acc_rank": "/vote_acc_rank", "rivalry": "/rivalry",
    "shared_trips": "/shared_trips", "trip_identities": "/trip_identities",
    "roster_check": "/roster_check", "mvp_recent": "/mvp_recent",
    "rating_rank": "/rating_rank", "profile_get": "/profile_get",
    "profile_update": "/profile_update", "wiki_save": "/wiki_save",
    "comment_add": "/comment_add", "months": "/months",
    "multi_trip": "/multi_trip", "notice": "/notice",
    "stats_by_class": "/stats_by_class",
    "monthly_name_rank": "/monthly_name_rank", "monthly_trip_rank": "/monthly_trip_rank",
    "monthly_name_win_rank": "/monthly_name_win_rank",
    "monthly_trip_win_rank": "/monthly_trip_win_rank",
    "monthly_name_talk_rank": "/monthly_name_talk_rank",
    "monthly_name_days_rank": "/monthly_name_days_rank",
    "monthly_career_rank": "/monthly_career_rank",
    "haiyaku_player_rank": "/haiyaku_player_rank",
    "monthly_role_win_rank": "/monthly_role_win_rank",
    "monthly_haiyaku_win_rank": "/monthly_haiyaku_win_rank",
    "monthly_village_rank": "/monthly_village_rank",
    "monthly_haiyaku_rank": "/monthly_haiyaku_rank",
    "monthly_rm_rank": "/monthly_rm_rank", "monthly_tsuiho_rank": "/monthly_tsuiho_rank",
    "room_rank": "/room_rank", "rm_rank": "/rm_rank",
    "tsuiho_rank": "/tsuiho_rank", "haiyaku_rank": "/haiyaku_rank",
    "personal_lite": "/personal_lite",
    "multi_summary": "/multi_summary",
    "rm_list": "/rm_list",
    "rm_detail": "/rm_detail",
    "rm_games": "/rm_games",
    "rm_participants": "/rm_participants",
    "trip12_cache_audit": "/trip12_cache_audit",
    "recent_player_keys": "/recent_player_keys",
    "player_talks": "/player_talks",
    "player_rag_analysis": "/player_rag_analysis",
    "calc_trip": "/calc_trip",
    "stats_updated_at": "/stats_updated_at",
    "bbs": "/bbs",
}

class PHPCompatMiddleware:
    def __init__(self, a: ASGIApp): self.app = a
    async def __call__(self, scope: Scope, receive: Receive, send: Send) -> None:
        if scope["type"] != "http" or scope["path"] not in ("/api.php", "/api.php/"):
            await self.app(scope, receive, send); return
        qs = scope.get("query_string", b"").decode("latin-1")
        params = parse_qs(qs, keep_blank_values=True)
        ml = params.pop("mode", []); mode = ml[0] if ml else ""
        if not mode and scope.get("method", "GET").upper() == "POST":
            body = b""
            while True:
                msg = await receive(); body += msg.get("body", b"")
                if not msg.get("more_body", False): break
            try:
                parsed = json_mod.loads(body)
                if isinstance(parsed, dict): mode = parsed.get("mode", "")
            except Exception: pass
            sent = False
            async def replay():
                nonlocal sent
                if not sent: sent = True; return {"type": "http.request", "body": body, "more_body": False}
                return {"type": "http.disconnect"}
            receive = replay
        if not mode: mode = "ping"
        np = MODE_TO_PATH.get(mode)
        if np:
            scope = dict(scope); scope["path"] = np
            scope["query_string"] = urlencode([(k, v) for k, vs in params.items() for v in vs]).encode("latin-1")
        await self.app(scope, receive, send)

class RateLimitMiddleware:
    def __init__(self, a: ASGIApp): self.app = a
    async def __call__(self, scope: Scope, receive: Receive, send: Send) -> None:
        if scope["type"] != "http": await self.app(scope, receive, send); return
        ip = _get_client_ip(scope); path = scope.get("path", "")
        _write_limiter.cleanup(); _read_limiter.cleanup()
        lim = _write_limiter if path in _WRITE_PATHS else _read_limiter
        if not lim.allow(f"{'w' if path in _WRITE_PATHS else 'r'}:{ip}"):
            r = ORJSONResponse(429, {"ok": False, "error": "リクエストが多すぎます。"}, headers={"Retry-After": "3"})
            await r(scope, receive, send); return
        await self.app(scope, receive, send)

class TimingMiddleware(BaseHTTPMiddleware):
    async def dispatch(self, request: Request, call_next):
        t0 = time.perf_counter(); resp = await call_next(request)
        ms = (time.perf_counter() - t0) * 1000
        if ms > 1000: logging.warning("%s %s → %d (%.0fms)", request.method, request.url.path, resp.status_code, ms)
        resp.headers["X-Response-Time"] = f"{ms:.1f}ms"; return resp


# ╔═══════════════════════════════════════════════════════╗
# ║  6. アプリ生成                                         ║
# ╚═══════════════════════════════════════════════════════╝

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)-8s %(message)s", stream=sys.stdout)
logging.getLogger("asyncpg").setLevel(logging.WARNING)

@asynccontextmanager
async def lifespan(a: FastAPI):
    pool = await init_pool()
    await ensure_runtime_tables(pool)
    logging.info("DB pool ready")
    yield
    await close_pool()

app = FastAPI(title="人狼戦績 API", version="2.0.0", default_response_class=ORJSONResponse, lifespan=lifespan)

@app.exception_handler(Exception)
async def _exc(req: Request, exc: Exception):
    logging.error("Error on %s %s: %s\n%s", req.method, req.url.path, exc, traceback.format_exc())
    return ORJSONResponse(500, {"ok": False, "error": f"Internal error: {type(exc).__name__}"})

app.add_middleware(TimingMiddleware)
app.add_middleware(RateLimitMiddleware)
app.add_middleware(CORSMiddleware, allow_origins=["*"], allow_credentials=True, allow_methods=["*"], allow_headers=["*"])
app.add_middleware(PHPCompatMiddleware)


# ╔═══════════════════════════════════════════════════════╗
# ║  7. エンドポイント                                      ║
# ╚═══════════════════════════════════════════════════════╝

# ─── ヘルスチェック ───
@app.get("/health")
async def ep_health():
    return {"status": "ok"}

@app.get("/health/db")
async def ep_health_db(pool: asyncpg.Pool = Depends(get_pool)):
    async with pool.acquire() as c: await c.fetchval("SELECT 1")
    return {"status": "ok", "db": "connected", "pool_size": pool.get_size(),
            "pool_free": pool.get_idle_size(), "pool_min": pool.get_min_size(), "pool_max": pool.get_max_size()}

@app.get("/ping")
async def ep_ping():
    return {"ok": True, "env": get_settings().app_env}

# ─── 認証 ───
@app.post("/login")
async def ep_login(body: LoginRequest, pool: asyncpg.Pool = Depends(get_pool)):
    name = body.name.strip(); trip_key = body.trip_key.strip()
    if not name: return ORJSONResponse(200, {"ok": False, "error": "名前を入力してください。"})
    if not trip_key: return ORJSONResponse(200, {"ok": False, "error": "トリップキーを入力してください。"})
    trip_norm = expand_trip(name, trip_key)
    if not trip_norm: return ORJSONResponse(200, {"ok": False, "error": "トリップ展開に失敗しました。"})
    async with pool.acquire() as c:
        row = await c.fetchrow("SELECT 1 FROM players WHERE trip=$1 LIMIT 1", trip_norm)
    if not row: return ORJSONResponse(401, {"ok": False, "error": f"このトリップ（{trip_norm}）は戦績DBに存在しません。"})
    return {"ok": True, "trip": trip_norm, "name": name, "token": create_token(trip_norm, name), "mode": "login"}

@app.post("/logout")
async def ep_logout():
    return {"ok": True}

@app.get("/me", response_model=MeResponse)
async def ep_me(login: dict | None = Depends(optional_login)):
    if login: return MeResponse(logged_in=True, trip=login["trip"], name=login["name"])
    return MeResponse(logged_in=False)

# ─── プロフィール ───
@app.get("/profile_get")
async def ep_profile_get(pool: asyncpg.Pool = Depends(get_pool), login: dict | None = Depends(optional_login)):
    if not login: return {"ok": True, "logged_in": False, "profile": None}
    async with pool.acquire() as c:
        r = await c.fetchrow("SELECT self_intro, x_id, x_display FROM player_profile WHERE trip=$1 LIMIT 1", login["trip"])
    return {"ok": True, "logged_in": True, "profile": {"self_intro": (r["self_intro"] if r else "") or "", "x_id": (r["x_id"] if r else "") or "", "x_display": (r["x_display"] if r else "") or ""}}

@app.post("/profile_update")
async def ep_profile_update(body: ProfileUpdateRequest, pool: asyncpg.Pool = Depends(get_pool), login: dict = Depends(require_login)):
    trip = login["trip"]; now = int(time.time())
    si = sanitize_text(body.self_intro, 5000); xi = sanitize_text(body.x_id, 100); xd = sanitize_text(body.x_display, 200)
    async with pool.acquire() as c:
        await c.execute("INSERT INTO player_profile (trip,self_intro,x_id,x_display,updated_at) VALUES($1,$2,$3,$4,$5) ON CONFLICT(trip) DO UPDATE SET self_intro=EXCLUDED.self_intro,x_id=EXCLUDED.x_id,x_display=EXCLUDED.x_display,updated_at=EXCLUDED.updated_at", trip, si, xi, xd, now)
    return {"ok": True, "trip": trip, "self_intro": si, "x_id": xi, "x_display": xd, "updated_at": now}

# ─── Wiki / コメント ───
@app.post("/wiki_save")
async def ep_wiki_save(body: WikiSaveRequest, pool: asyncpg.Pool = Depends(get_pool), login: dict = Depends(require_login)):
    kt = sanitize_text(body.key_type, 50); kv = sanitize_text(body.key_value, 500); text = sanitize_wiki_body(body.body)
    if not kt or not validate_key_value(kv): return {"ok": False, "error": "パラメータ不足です。"}
    async with pool.acquire() as c:
        async with c.transaction():
            await c.execute("INSERT INTO wiki (key,key_type,key_value,body,updated_at,updated_at_text) VALUES($1,$2,$3,$4,EXTRACT(EPOCH FROM NOW())::BIGINT,CAST(CURRENT_DATE AS TEXT)) ON CONFLICT(key_type,key_value) DO UPDATE SET key=EXCLUDED.key,body=EXCLUDED.body,updated_at=EXCLUDED.updated_at,updated_at_text=EXCLUDED.updated_at_text", f"{kt}:{kv}", kt, kv, text)
            await c.execute("INSERT INTO wiki_hist (key_type,key_value,body,editor_name,editor_trip,created_at) VALUES($1,$2,$3,$4,$5,EXTRACT(EPOCH FROM NOW())::BIGINT)", kt, kv, text, login.get("name",""), login["trip"])
    return {"ok": True}

@app.post("/comment_add")
async def ep_comment_add(body: CommentAddRequest, pool: asyncpg.Pool = Depends(get_pool), login: dict = Depends(require_login)):
    kt = sanitize_text(body.key_type, 50); kv = sanitize_text(body.key_value, 500); text = sanitize_html_text(body.body, 5000)
    if not kt or not text or not validate_key_value(kv): return {"ok": False, "error": "パラメータ不足です"}
    an = sanitize_text(login.get("name",""), 100) or "名無しさん"
    async with pool.acquire() as c:
        await c.execute("INSERT INTO comments (key_type,key_value,body,author_name,author_trip,created_at) VALUES($1,$2,$3,$4,$5,EXTRACT(EPOCH FROM NOW())::BIGINT)", kt, kv, text, an, login["trip"])
    return {"ok": True}

# ─── Months / Notice ───
@app.get("/months")
async def ep_months(pool: asyncpg.Pool = Depends(get_pool)):
    async with pool.acquire() as c:
        rows = await c.fetch("SELECT DISTINCT TO_CHAR(TO_TIMESTAMP(started_at) AT TIME ZONE 'Asia/Tokyo','YYYY-MM') AS ym FROM games ORDER BY ym DESC")
    return {"ok": True, "months": [r["ym"] for r in rows]}

@app.get("/notice")
async def ep_notice():
    path = get_settings().notice_file; text = ""; updated_at = ""
    if os.path.isfile(path):
        try:
            with open(path, "r", encoding="utf-8") as f: text = f.read().strip()
            updated_at = datetime.fromtimestamp(os.path.getmtime(path)).strftime("%Y-%m-%d %H:%M:%S")
        except OSError: pass
    return {"ok": True, "notice": {"text": text, "updated_at": updated_at}}

# ─── 配役分類別統計（全体/通常/カオス） ───
@app.get("/stats_by_class")
async def ep_stats_by_class(ym:str=Query("all"),pool:asyncpg.Pool=Depends(get_pool)):
    """全体/通常/カオスのゲーム数・勝率を返す。ym=all, 2025-01, 2025 のいずれか。"""
    # キャッシュ確認
    async with pool.acquire() as c:
        row = await c.fetchrow("SELECT payload FROM rank_cache_json WHERE cache_key=$1 LIMIT 1", f"class_stats:{ym}")
    if row:
        return {"ok": True, **json_mod.loads(row["payload"])}

    # フォールバック: 直接計算
    if ym == "all":
        ymf = ""
        pb = []
    elif re.match(r"^\d{4}-\d{2}$", ym):
        ymf = "AND TO_CHAR(TO_TIMESTAMP(g.started_at) AT TIME ZONE 'Asia/Tokyo','YYYY-MM')=$1"
        pb = [ym]
    elif re.match(r"^\d{4}$", ym):
        ymf = "AND TO_CHAR(TO_TIMESTAMP(g.started_at) AT TIME ZONE 'Asia/Tokyo','YYYY')=$1"
        pb = [ym]
    else:
        return {"ok": False, "error": "ym の形式が不正です"}

    async with pool.acquire() as c:
        rows = await c.fetch(f"""
            SELECT COALESCE(g.haiyaku_class, '通常') AS cls,
                   COUNT(*) AS games,
                   SUM(p.win) AS wins,
                   ROUND(SUM(p.win)::numeric / NULLIF(COUNT(*), 0) * 100, 1) AS win_rate
            FROM players p JOIN games g ON g.id = p.game_id
            WHERE p.is_spectator = 0 {ymf}
            GROUP BY cls
        """, *pb)

    result = {"all": {"games":0,"wins":0,"win_rate":0.0}, "normal": {"games":0,"wins":0,"win_rate":0.0}, "chaos": {"games":0,"wins":0,"win_rate":0.0}}
    total_games = total_wins = 0
    for r in rows:
        g = int(r["games"]); w = int(r["wins"]); wr = float(r["win_rate"] or 0)
        total_games += g; total_wins += w
        if r["cls"] == "通常":
            result["normal"] = {"games": g, "wins": w, "win_rate": wr}
        else:
            result["chaos"]["games"] += g; result["chaos"]["wins"] += w
    if result["chaos"]["games"] > 0:
        result["chaos"]["win_rate"] = round(result["chaos"]["wins"] / result["chaos"]["games"] * 100, 1)
    result["all"] = {"games": total_games, "wins": total_wins, "win_rate": round(total_wins / total_games * 100, 1) if total_games else 0.0}
    result["ym"] = ym
    try:
        now = int(time.time())
        async with pool.acquire() as c:
            await c.execute("""
                INSERT INTO rank_cache_json (cache_key,scope,ym,kind,key_type,payload,total,built_at,updated_at)
                VALUES ($1,$2,$3,'class_stats',NULL,$4,0,$5,$5)
                ON CONFLICT (cache_key) DO UPDATE
                SET payload=EXCLUDED.payload, built_at=EXCLUDED.built_at, updated_at=EXCLUDED.updated_at
            """, f"class_stats:{ym}", _parse_scope(ym) or "all", ym, json_mod.dumps(result, ensure_ascii=False), now)
    except Exception:
        pass

    return {"ok": True, "ym": ym, **result}

# ─── 村系 ───
@app.get("/village_detail")
async def ep_village_detail(id: int = Query(0, ge=0), log_id: int = Query(0, ge=0), pool: asyncpg.Pool = Depends(get_pool)):
    if id <= 0 and log_id <= 0: raise HTTPException(400, "id か log_id を指定してください。")
    async with pool.acquire() as c:
        game = await c.fetchrow("SELECT id,log_id,village_name,started_at,haiyaku FROM games WHERE " + ("id=$1" if id > 0 else "log_id=$1") + " LIMIT 1", id if id > 0 else log_id)
    if not game: return {"ok": False, "error": "NOT_FOUND", "msg": "指定された村が見つかりません。"}
    gid = game["id"]
    async with pool.acquire() as c:
        prows = await c.fetch("SELECT id,name,trip,role,haiyaku,win,is_spectator,talk_count,live_days FROM players WHERE game_id=$1 ORDER BY is_spectator ASC, id ASC", gid)
    tp = ts = wc = tks = lds = 0; rs: dict = defaultdict(lambda:[0,0]); hs: dict = defaultdict(lambda:[0,0]); pl = []
    for r in prows:
        isp = int(r["is_spectator"]) != 0; w = int(r["win"]); tk = int(r["talk_count"]); ld = int(r["live_days"])
        if isp: ts += 1
        else: tp += 1; wc += w; tks += tk; lds += ld; rs[r["role"]][0] += 1; rs[r["role"]][1] += w; hs[r["haiyaku"]][0] += 1; hs[r["haiyaku"]][1] += w
        pl.append({"id":r["id"],"name":r["name"],"trip":r["trip"],"role":r["role"],"haiyaku":r["haiyaku"],"win":w,"is_spectator":1 if isp else 0,"talk_count":tk,"live_days":ld})
    def _r(g,w): return round(w/g*100,1) if g>0 else 0
    return {"ok":True,"village":{"id":gid,"log_id":game["log_id"],"village_name":game["village_name"],"started_at":game["started_at"],"haiyaku":game["haiyaku"]},
        "summary":{"total_players":tp,"total_spectators":ts,"winners":wc,"losers":max(0,tp-wc),"talk_sum":tks,"live_days_sum":lds,"avg_talk_per_player":round(tks/tp,2) if tp else 0,"avg_talk_per_day":round(tks/lds,2) if lds else 0},
        "players":pl,"role_stats":sorted([{"role":k,"games":v[0],"wins":v[1],"win_rate":_r(v[0],v[1])} for k,v in rs.items()],key=lambda x:-x["games"]),
        "haiyaku_stats":sorted([{"haiyaku":k,"games":v[0],"wins":v[1],"win_rate":_r(v[0],v[1])} for k,v in hs.items()],key=lambda x:-x["games"])}

@app.get("/village_recent")
async def ep_village_recent(limit: int = Query(100, ge=1, le=200), pool: asyncpg.Pool = Depends(get_pool)):
    scan_limit = min(10000, max(1000, limit * 200))
    async with pool.acquire() as c:
        rows = await c.fetch("""
            WITH recent AS (
                SELECT id,log_id,village_name,started_at,haiyaku
                FROM games
                ORDER BY started_at DESC
                LIMIT $2
            ), picked AS (
                SELECT DISTINCT ON (village_name) id,log_id,village_name,started_at,haiyaku
                FROM recent
                ORDER BY village_name, started_at DESC
            )
            SELECT id,log_id,village_name,started_at,haiyaku
            FROM picked
            ORDER BY started_at DESC
            LIMIT $1
        """, limit, scan_limit)
    return {"ok":True,"limit":limit,"rows":[{"id":r["id"],"log_id":r["log_id"],"village_name":r["village_name"],"started_at":r["started_at"],"haiyaku":r["haiyaku"]} for r in rows]}

# ─── ランキング ───
def _parse_scope(ym):
    if ym=="all": return "all"
    if re.match(r"^\d{4}-\d{2}$",ym): return "month"
    if re.match(r"^\d{4}$",ym): return "year"
    return None

# 最低集計数(min_count)で絞り込む際の、集計種別ごとの件数キー
_RANK_MIN_COUNT_KEYS = {
    "games": "games", "win_rate": "games", "talk": "live_sum", "days": "unique_days",
    "career": "games", "village": "games", "haiyaku": "games", "rm": "games", "tsuiho": "times",
}

# トリップなし除外(exclude_no_trip)に対応できる集計種別（players由来の名前別集計）
_RANK_NT_KINDS = ("games", "win_rate", "talk", "days", "career")

async def _rank_respond(pool, kind, key_type, ym, limit, offset, extra=None):
    extra = extra or {}
    min_count = int(extra.get("min_count") or 0)
    exclude_nt = bool(int(extra.get("exclude_no_trip") or 0)) and kind in _RANK_NT_KINDS and key_type != "trip"
    extra["exclude_no_trip"] = 1 if exclude_nt else 0
    mkey = _RANK_MIN_COUNT_KEYS.get(kind)
    scope = _parse_scope(ym)
    if scope is None: return {"ok":False,"error":"ym の形式が不正です"}
    if kind == "games" and key_type in ("name", "trip") and min_count <= 0 and not exclude_nt:
        paged = await _rank_json_page(pool, scope, ym, kind, key_type, limit, offset)
        if paged is not None:
            rows, total = paged
            if any("wins" not in r or "win_rate" not in r for r in rows):
                rows = await _add_win_stats_to_game_rank(pool, rows, key_type, ym, scope)
            return {"ok": True, "rows": rows, "total": total, "limit": limit, "offset": offset}
    kind_key = f"{kind}:nt" if exclude_nt else kind
    async with pool.acquire() as c:
        row = await c.fetchrow("SELECT payload,total,built_at FROM rank_cache_json WHERE scope=$1 AND ym=$2 AND kind=$3 AND COALESCE(key_type,'__NULL__')=COALESCE($4,'__NULL__') LIMIT 1", scope, ym, kind_key, key_type)
    # :nt キャッシュは自己温存方式なので24時間で失効させ再集計する
    if row and exclude_nt and int(time.time()) - int(row["built_at"] or 0) >= 86400:
        row = None
    if row:
        p = json_mod.loads(row["payload"] or "{}"); ar = p.get("rows",[])
        if min_count > 0 and mkey:
            ar = [r for r in ar if int(r.get(mkey) or 0) >= min_count]
            total = len(ar)
        else:
            total = p.get("total", len(ar))
        rows = ar[offset:offset+limit]
        if kind == "games" and key_type in ("name", "trip") and any("wins" not in r or "win_rate" not in r for r in rows):
            rows = await _add_win_stats_to_game_rank(pool, rows, key_type, ym, scope)
        return {"ok":True,"rows":rows,"total":total,"limit":limit,"offset":offset}
    return await _rank_fallback(pool, kind, key_type, ym, scope, limit, offset, extra)

async def _rank_json_page(pool, scope, ym, kind, key_type, limit, offset):
    async with pool.acquire() as c:
        rows = await c.fetch("""
            SELECT r.total, a.elem
            FROM rank_cache_json r
            CROSS JOIN LATERAL jsonb_array_elements((r.payload::jsonb)->'rows') WITH ORDINALITY AS a(elem, ord)
            WHERE r.scope=$1 AND r.ym=$2 AND r.kind=$3
              AND COALESCE(r.key_type,'__NULL__')=COALESCE($4,'__NULL__')
              AND ord > $5 AND ord <= $6
            ORDER BY ord
        """, scope, ym, kind, key_type, offset, offset + limit)
    if not rows:
        return None
    return [json_mod.loads(r["elem"]) if isinstance(r["elem"], str) else dict(r["elem"]) for r in rows], int(rows[0]["total"] or 0)

async def _add_win_stats_to_game_rank(pool, rows, key_type, ym, scope):
    if not rows: return rows
    col = "name" if key_type == "name" else "trip"
    keys = [r.get(col) for r in rows if r.get(col)]
    if not keys: return rows
    stats = await _win_stats_from_rank_cache(pool, key_type, ym, scope, keys)
    if stats:
        out = []
        for r in rows:
            nr = dict(r)
            st = stats.get(nr.get(col))
            if st:
                nr["wins"] = st["wins"]
                nr["win_rate"] = st["win_rate"]
            out.append(nr)
        return out
    if scope == "all":
        return rows
    ymf = ""
    pb = [keys]
    if scope != "all":
        fmt = "YYYY-MM" if scope == "month" else "YYYY"
        ymf = f"AND TO_CHAR(TO_TIMESTAMP(g.started_at) AT TIME ZONE 'Asia/Tokyo','{fmt}')=$2"
        pb.append(ym)
    async with pool.acquire() as c:
        stat_rows = await c.fetch(f"""
            SELECT p.{col} AS k, COUNT(*) AS games, SUM(p.win) AS wins,
                   ROUND(SUM(p.win)::numeric / NULLIF(COUNT(*),0) * 100, 1) AS wr
            FROM players p JOIN games g ON g.id=p.game_id
            WHERE p.is_spectator=0
              AND p.{col}=ANY($1::text[])
              {ymf}
            GROUP BY p.{col}
        """, *pb)
    stats = {r["k"]: {"games": int(r["games"]), "wins": int(r["wins"] or 0), "win_rate": float(r["wr"] or 0)} for r in stat_rows}
    out = []
    for r in rows:
        nr = dict(r)
        st = stats.get(nr.get(col))
        if st:
            nr["wins"] = st["wins"]
            nr["win_rate"] = st["win_rate"]
        out.append(nr)
    return out

async def _win_stats_from_rank_cache(pool, key_type, ym, scope, keys):
    col = "name" if key_type == "name" else "trip"
    async with pool.acquire() as c:
        rows = await c.fetch(f"""
            SELECT a.elem
            FROM rank_cache_json r
            CROSS JOIN LATERAL jsonb_array_elements((r.payload::jsonb)->'rows') AS a(elem)
            WHERE r.scope=$1 AND r.ym=$2 AND r.kind='win_rate'
              AND COALESCE(r.key_type,'__NULL__')=COALESCE($3,'__NULL__')
              AND a.elem->>$4 = ANY($5::text[])
        """, scope, ym, key_type, col, keys)
    stats = {}
    for r in rows:
        elem = json_mod.loads(r["elem"]) if isinstance(r["elem"], str) else dict(r["elem"])
        k = elem.get(col)
        if k:
            stats[k] = {"wins": int(elem.get("wins") or 0), "win_rate": float(elem.get("win_rate") or 0)}
    return stats

async def _rank_fallback(pool, kind, key_type, ym, scope, limit, offset, extra):
    ymf = ""
    if scope != "all":
        fmt = "YYYY-MM" if scope=="month" else "YYYY"
        ymf = f"AND TO_CHAR(TO_TIMESTAMP(g.started_at) AT TIME ZONE 'Asia/Tokyo','{fmt}')=$1"
    pb = [] if scope=="all" else [ym]
    exclude_nt = bool(int(extra.get("exclude_no_trip") or 0))
    ntf = "AND p.trip IS NOT NULL AND p.trip<>'' AND p.trip<>'なし'" if exclude_nt else ""
    async with pool.acquire() as c:
        if kind=="games" and key_type=="name":
            rows = await c.fetch(f"SELECT p.name AS k,COUNT(*) AS games,SUM(p.win) AS wins,ROUND(SUM(p.win)::numeric/COUNT(*)*100,1) AS wr FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 {ymf} {ntf} AND p.name IS NOT NULL AND p.name<>'' GROUP BY p.name ORDER BY games DESC",*pb)
            out = [{"name":r["k"],"games":r["games"],"wins":r["wins"],"win_rate":float(r["wr"])} for r in rows]
        elif kind=="games" and key_type=="trip":
            rows = await c.fetch(f"SELECT p.trip AS k,COUNT(*) AS games,SUM(p.win) AS wins,ROUND(SUM(p.win)::numeric/COUNT(*)*100,1) AS wr FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 {ymf} AND p.trip IS NOT NULL AND p.trip<>'' GROUP BY p.trip ORDER BY games DESC",*pb)
            out = [{"trip":r["k"],"games":r["games"],"wins":r["wins"],"win_rate":float(r["wr"])} for r in rows]
        elif kind=="win_rate" and key_type in("name","trip"):
            mg=max(1,extra.get("min_games",30));n=len(pb)+1;col="name" if key_type=="name" else "trip"
            rows = await c.fetch(f"SELECT p.{col} AS k,COUNT(*) AS games,SUM(p.win) AS wins,ROUND(SUM(p.win)::numeric/COUNT(*)*100,1) AS wr FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 {ymf} {ntf} AND p.{col} IS NOT NULL AND p.{col}<>'' GROUP BY p.{col} HAVING COUNT(*)>=${n} ORDER BY wr DESC,games DESC",*pb,mg)
            out = [{col:r["k"],"games":r["games"],"wins":r["wins"],"win_rate":float(r["wr"])} for r in rows]
        elif kind=="talk" and key_type=="name":
            mld=max(1,extra.get("min_live_days",10));n=len(pb)+1
            rows = await c.fetch(f"SELECT p.name AS k,SUM(p.talk_count) AS ts,SUM(p.live_days) AS ls,ROUND(SUM(p.talk_count)::numeric/NULLIF(SUM(p.live_days),0),2) AS d FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 {ymf} {ntf} AND p.name IS NOT NULL AND p.name<>'' GROUP BY p.name HAVING SUM(p.live_days)>=${n} ORDER BY d DESC",*pb,mld)
            out = [{"name":r["k"],"talk_sum":r["ts"],"live_sum":r["ls"],"density":float(r["d"] or 0)} for r in rows]
        elif kind=="days" and key_type=="name":
            rows = await c.fetch(f"SELECT p.name AS k,COUNT(DISTINCT TO_CHAR(TO_TIMESTAMP(g.started_at) AT TIME ZONE 'Asia/Tokyo','YYYY-MM-DD')) AS ud,COUNT(*) AS games FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 {ymf} {ntf} AND p.name IS NOT NULL AND p.name<>'' GROUP BY p.name ORDER BY ud DESC,games DESC",*pb)
            out = [{"name":r["k"],"unique_days":r["ud"],"games":r["games"]} for r in rows]
        elif kind=="role_win":
            mg=max(1,extra.get("min_games",30));n=len(pb)+1
            rows = await c.fetch(f"SELECT p.role,COUNT(*) AS games,SUM(p.win) AS wins,ROUND(SUM(p.win)::numeric/COUNT(*)*100,1) AS wr FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 {ymf} GROUP BY p.role HAVING COUNT(*)>=${n} ORDER BY wr DESC",*pb,mg)
            out = [{"role":r["role"],"games":r["games"],"wins":r["wins"],"win_rate":float(r["wr"])} for r in rows]
        elif kind=="haiyaku_win":
            mp=max(1,extra.get("min_players",100));n=len(pb)+1
            rows = await c.fetch(f"SELECT g.haiyaku,COUNT(*) AS pl,SUM(p.win) AS wins,ROUND(SUM(p.win)::numeric/COUNT(*)*100,1) AS wr FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 {ymf} GROUP BY g.haiyaku HAVING COUNT(*)>=${n} ORDER BY wr DESC",*pb,mp)
            out = [{"haiyaku":r["haiyaku"],"players":r["pl"],"wins":r["wins"],"win_rate":float(r["wr"])} for r in rows]
        elif kind in ("village","haiyaku","rm"):
            ymfg = ""
            if scope!="all":
                tf="YYYY-MM" if scope=="month" else "YYYY"
                ymfg=f"AND TO_CHAR(TO_TIMESTAMP(g.started_at) AT TIME ZONE 'Asia/Tokyo','{tf}')=$1"
            if kind=="village":
                rows = await c.fetch(f"SELECT g.village_name,COUNT(*) AS games FROM games g WHERE 1=1 {ymfg} GROUP BY g.village_name ORDER BY games DESC",*pb)
                out = [{"village_name":r["village_name"],"games":r["games"]} for r in rows]
            elif kind=="haiyaku":
                rows = await c.fetch(f"SELECT g.haiyaku,COUNT(*) AS games FROM games g WHERE 1=1 {ymfg} GROUP BY g.haiyaku ORDER BY games DESC",*pb)
                out = [{"haiyaku":r["haiyaku"],"games":r["games"]} for r in rows]
            else:
                rows = await c.fetch(f"SELECT COALESCE(g.rm_name,'不明') AS rn,COUNT(*) AS games FROM games g WHERE 1=1 {ymfg} GROUP BY rn ORDER BY games DESC",*pb)
                out = [{"rm_name":r["rn"],"games":r["games"]} for r in rows]
        elif kind=="tsuiho":
            # 参加者フラグ(tsuiho_flag)ではなくゲームごとの追放記録(tsuiho_json)から集計する。
            # ロビーで追放された荒らしは参加者名簿に載らずフラグが立たないため。
            rows = await c.fetch(f"SELECT k AS name,COUNT(*) AS times FROM games g CROSS JOIN LATERAL jsonb_object_keys(g.tsuiho_json::jsonb) k WHERE g.tsuiho_json IS NOT NULL AND g.tsuiho_json LIKE '{{%' {ymf} AND k<>'' GROUP BY k ORDER BY times DESC",*pb)
            out = [{"name":r["name"],"times":r["times"]} for r in rows]
        elif kind=="career":
            col = "trip" if key_type == "trip" else "name"
            rows = await c.fetch(f"SELECT p.{col} AS k,MIN(g.started_at) AS mn,MAX(g.started_at) AS mx,COUNT(*) AS games FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 {ymf} {ntf} AND p.{col} IS NOT NULL AND p.{col}<>'' GROUP BY p.{col}",*pb)
            _jst = timezone(timedelta(hours=9))
            out = []
            for r in rows:
                fd = datetime.fromtimestamp(int(r["mn"]), _jst).date()
                ld = datetime.fromtimestamp(int(r["mx"]), _jst).date()
                out.append({col:r["k"],"first_day":fd.isoformat(),"last_day":ld.isoformat(),"span_days":(ld-fd).days+1,"games":int(r["games"])})
            out.sort(key=lambda x:(-x["span_days"],-x["games"]))
        else:
            return {"ok":False,"error":f"フォールバック未実装: {kind}/{key_type}"}
    if exclude_nt and kind in _RANK_NT_KINDS:
        # トリップなし除外はバッチ対象外なので、初回集計を自己温存キャッシュに保存する(24h)
        try:
            now = int(time.time())
            ck = f"nt:{scope}:{ym}:{kind}:{key_type or '-'}"
            async with pool.acquire() as c:
                await c.execute("""
                    INSERT INTO rank_cache_json (cache_key,scope,ym,kind,key_type,payload,total,built_at,updated_at)
                    VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$8)
                    ON CONFLICT (cache_key) DO UPDATE
                    SET payload=EXCLUDED.payload, total=EXCLUDED.total, built_at=EXCLUDED.built_at, updated_at=EXCLUDED.updated_at
                """, ck, scope, ym, f"{kind}:nt", key_type, json_mod.dumps({"rows": out[:10000], "total": len(out)}, ensure_ascii=False), len(out), now)
        except Exception:
            pass
    min_count = int(extra.get("min_count") or 0)
    mkey = _RANK_MIN_COUNT_KEYS.get(kind)
    if min_count > 0 and mkey:
        out = [r for r in out if int(r.get(mkey) or 0) >= min_count]
    total=len(out); return {"ok":True,"rows":out[offset:offset+limit],"total":total,"limit":limit,"offset":offset}

def _make_rank_ep(kind, key_type, force_ym=None):
    async def h(ym:str=Query(""),limit:int=Query(100,ge=1,le=1000),offset:int=Query(0,ge=0),min_games:int=Query(30,ge=1),min_live_days:int=Query(10,ge=1),min_players:int=Query(100,ge=1),min_count:int=Query(0,ge=0),exclude_no_trip:int=Query(0,ge=0,le=1),pool:asyncpg.Pool=Depends(get_pool)):
        aym=force_ym or ym
        if not aym: return {"ok":False,"error":"ym が指定されていません"}
        return await _rank_respond(pool,kind,key_type,aym,limit,offset,{"min_games":min_games,"min_live_days":min_live_days,"min_players":min_players,"min_count":min_count,"exclude_no_trip":exclude_no_trip})
    s=f"_{key_type}" if key_type else ""; h.__name__=f"rank_{kind}{s}"; h.__qualname__=h.__name__; return h

for _path, _args in [
    ("/monthly_name_rank",("games","name")),("/monthly_trip_rank",("games","trip")),
    ("/monthly_name_win_rank",("win_rate","name")),("/monthly_trip_win_rank",("win_rate","trip")),
    ("/monthly_name_talk_rank",("talk","name")),("/monthly_name_days_rank",("days","name")),
    ("/monthly_role_win_rank",("role_win",None)),("/monthly_haiyaku_win_rank",("haiyaku_win",None)),
    ("/monthly_village_rank",("village",None)),("/monthly_haiyaku_rank",("haiyaku",None)),
    ("/monthly_rm_rank",("rm",None)),("/monthly_tsuiho_rank",("tsuiho",None)),
]: app.get(_path)(_make_rank_ep(*_args))
for _path, _args in [
    ("/room_rank",("village",None,"all")),("/rm_rank",("rm",None,"all")),
    ("/tsuiho_rank",("tsuiho",None,"all")),("/haiyaku_rank",("haiyaku",None,"all")),
]: app.get(_path)(_make_rank_ep(*_args))

@app.get("/monthly_career_rank")
async def ep_career_rank(ym:str=Query(""),key_type:str=Query("name"),limit:int=Query(100,ge=1,le=1000),offset:int=Query(0,ge=0),min_count:int=Query(0,ge=0),exclude_no_trip:int=Query(0,ge=0,le=1),pool:asyncpg.Pool=Depends(get_pool)):
    if not ym: return {"ok":False,"error":"ym が指定されていません"}
    kt = "trip" if key_type == "trip" else "name"
    return await _rank_respond(pool,"career",kt,ym,limit,offset,{"min_count":min_count,"exclude_no_trip":exclude_no_trip})

@app.get("/haiyaku_player_rank")
async def ep_haiyaku_player_rank(haiyaku:str=Query(""),ym:str=Query("all"),key_type:str=Query("name"),limit:int=Query(100,ge=1,le=1000),offset:int=Query(0,ge=0),min_count:int=Query(0,ge=0),exclude_no_trip:int=Query(0,ge=0,le=1),pool:asyncpg.Pool=Depends(get_pool)):
    """指定配役のゲームに絞ったプレイヤーランキング。初回集計をキャッシュし24hで再集計する。"""
    hy = haiyaku.strip()
    if not hy: return {"ok":False,"error":"haiyaku が指定されていません"}
    scope = _parse_scope(ym)
    if scope is None: return {"ok":False,"error":"ym の形式が不正です"}
    kt = "trip" if key_type == "trip" else "name"
    ex_nt = bool(exclude_no_trip) and kt == "name"
    cache_key = f"hp:{scope}:{ym}:{hy}:{kt}" + (":nt" if ex_nt else "")
    now = int(time.time())
    rows_all = None
    async with pool.acquire() as c:
        row = await c.fetchrow("SELECT payload,built_at FROM rank_cache_json WHERE cache_key=$1 LIMIT 1", cache_key)
    if row and now - int(row["built_at"] or 0) < 86400:
        rows_all = json_mod.loads(row["payload"] or "{}").get("rows", [])
    if rows_all is None:
        ymf = ""
        pb = [hy]
        if scope != "all":
            fmt = "YYYY-MM" if scope == "month" else "YYYY"
            ymf = f"AND TO_CHAR(TO_TIMESTAMP(g.started_at) AT TIME ZONE 'Asia/Tokyo','{fmt}')=$2"
            pb.append(ym)
        async with pool.acquire() as c:
            ntf = "AND p.trip IS NOT NULL AND p.trip<>'' AND p.trip<>'なし'" if ex_nt else ""
            rows = await c.fetch(f"SELECT p.{kt} AS k,COUNT(*) AS games,SUM(p.win) AS wins,ROUND(SUM(p.win)::numeric/COUNT(*)*100,1) AS wr FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 AND g.haiyaku=$1 {ymf} {ntf} AND p.{kt} IS NOT NULL AND p.{kt}<>'' GROUP BY p.{kt} ORDER BY games DESC", *pb)
        rows_all = [{kt: r["k"], "games": int(r["games"]), "wins": int(r["wins"] or 0), "win_rate": float(r["wr"] or 0)} for r in rows]
        try:
            async with pool.acquire() as c:
                await c.execute("""
                    INSERT INTO rank_cache_json (cache_key,scope,ym,kind,key_type,payload,total,built_at,updated_at)
                    VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$8)
                    ON CONFLICT (cache_key) DO UPDATE
                    SET payload=EXCLUDED.payload, total=EXCLUDED.total, built_at=EXCLUDED.built_at, updated_at=EXCLUDED.updated_at
                """, cache_key, scope, ym, f"hp:{hy}" + (":nt" if ex_nt else ""), kt, json_mod.dumps({"rows": rows_all[:10000], "total": len(rows_all)}, ensure_ascii=False), len(rows_all), now)
        except Exception:
            pass
    if min_count > 0:
        rows_all = [r for r in rows_all if int(r.get("games") or 0) >= min_count]
    total = len(rows_all)
    return {"ok":True,"rows":rows_all[offset:offset+limit],"total":total,"limit":limit,"offset":offset,"haiyaku":hy}

@app.get("/rating_rank")
async def ep_rating_rank(limit:int=Query(100,ge=1,le=500),offset:int=Query(0,ge=0),min_games:int=Query(20,ge=0),pool:asyncpg.Pool=Depends(get_pool)):
    async with pool.acquire() as c:
        total = await c.fetchval("SELECT COUNT(*) FROM player_ratings WHERE games>=$1",min_games)
        if not total: return {"ok":True,"total":0,"limit":limit,"offset":offset,"rows":[]}
        rows = await c.fetch("SELECT trip,rating,games,main_name FROM player_ratings WHERE games>=$1 ORDER BY rating DESC,games DESC,trip ASC LIMIT $2 OFFSET $3",min_games,limit,offset)
    return {"ok":True,"total":total,"limit":limit,"offset":offset,"rows":[{"trip":r["trip"],"name":r["main_name"] or "","main_name":r["main_name"],"rating":float(r["rating"]),"games":int(r["games"])} for r in rows]}

# ─── 変遷系 ───
@app.get("/name_chain_list")
async def ep_name_chain_list(page:int=Query(1,ge=1),limit:int=Query(50,ge=1,le=200),order:str=Query("last_ts_desc"),pool:asyncpg.Pool=Depends(get_pool)):
    offset=(page-1)*limit
    om={"first_ts_asc":"c.first_ts ASC","games_desc":"c.games_total DESC","span_days_desc":"(c.last_ts-c.first_ts) DESC","last_ts_desc":"c.last_ts DESC"}
    osql=om.get(order,"c.last_ts DESC")
    if order not in om: order="last_ts_desc"
    async with pool.acquire() as c:
        total = await c.fetchval("SELECT COUNT(*) FROM name_trip_chain")
        rows = await c.fetch(f"SELECT c.id,c.representative_trip,c.latest_trip,c.first_ts,c.last_ts,c.games_total,c.trip_count,(SELECT p2.name FROM players p2 JOIN games g2 ON g2.id=p2.game_id WHERE p2.trip=c.latest_trip ORDER BY g2.started_at DESC LIMIT 1) AS latest_name FROM name_trip_chain c ORDER BY {osql} LIMIT $1 OFFSET $2",limit,offset)
    return {"ok":True,"total":total or 0,"page":page,"limit":limit,"order":order,"rows":[{"id":r["id"],"representative_trip":r["representative_trip"],"latest_trip":r["latest_trip"],"latest_name":r["latest_name"] or "","first_ts":r["first_ts"],"last_ts":r["last_ts"],"span_days":max(0,(r["last_ts"]-r["first_ts"])//86400) if r["last_ts"] and r["first_ts"] and r["last_ts"]>r["first_ts"] else 0,"games_total":r["games_total"],"trip_count":r["trip_count"]} for r in rows]}

@app.get("/trip_chain_rank")
async def ep_trip_chain_rank(limit:int=Query(50,ge=1,le=200),offset:int=Query(0,ge=0),min_games:int=Query(10,ge=0),min_span_days:int=Query(30,ge=0),both_modes:int=Query(0,ge=0,le=1),pool:asyncpg.Pool=Depends(get_pool)):
    mss=min_span_days*86400; wc=["s.games_total>=$1","s.span_seconds>=$2"]; pa=[min_games,mss]
    if both_modes: wc.append("s.plain_games>0 AND s.fixed_games>0")
    async with pool.acquire() as c:
        rows = await c.fetch(f"SELECT s.*,c.latest_trip,c.trip_count,(SELECT n.name FROM plain_trip_chain_names n WHERE n.plain_trip=s.plain_trip ORDER BY n.games_total DESC,n.first_ts ASC LIMIT 1) AS main_name FROM plain_trip_chain_summary s LEFT JOIN name_trip_chain c ON c.representative_trip=s.plain_trip WHERE {' AND '.join(wc)} ORDER BY s.span_seconds DESC,s.games_total DESC,s.plain_trip ASC LIMIT $3 OFFSET $4",*pa,limit,offset)
    return {"ok":True,"rows":[{"plain_trip":r["plain_trip"],"main_name":r["main_name"] or "","latest_trip":r.get("latest_trip"),"trip_count":int(r["trip_count"]) if r.get("trip_count") is not None else None,"games_total":int(r["games_total"]),"name_count":int(r["name_count"]),"first_ts":int(r["first_ts"]),"last_ts":int(r["last_ts"]),"span_seconds":(sp:=int(r["span_seconds"])),"span_days":round(sp/86400,1) if sp>0 else 0.0,"plain_games":int(r["plain_games"]),"fixed_games":int(r["fixed_games"]),"first_plain_ts":r["first_plain_ts"],"first_fixed_ts":r["first_fixed_ts"]} for r in rows],"limit":limit,"offset":offset}

# 公開用アーカイブでは運用時の既定値を除去しています。
AI_EVAL_TOKEN = os.environ.get("AI_EVAL_TOKEN", "CHANGE_ME")
_AI_SYS_RE = re.compile(r"投票|処刑|追放|襲撃|占い|噛|勝利|勝ちました|敗北|ゲームを開始|ゲームを終了|朝になりました|夜になりました|日目|吊|死亡|犠牲|霊能|狩人|GJ")

_WOLF_SIDE_ROLES = ("人狼", "狂人", "狂信者")

def _side_of(role: str) -> str:
    if role in _WOLF_SIDE_ROLES: return "wolf"
    if role == "妖狐": return "fox"
    return "village"

RM_EXEMPT_MIN = 10  # 割れ酉でも、当該トリップでのRM実績がこれ以上なら自動追放を免除

@app.get("/shared_trips")
async def ep_shared_trips(trips:str=Query(""),pool:asyncpg.Pool=Depends(get_pool)):
    """与えたトリップ群のうち割れ酉判定に該当するものを返す（Chrome拡張の自動追放用）。

    rm_trips は該当のうちRM実績が RM_EXEMPT_MIN 以上のトリップ。
    拡張は既定でこれを自動追放の対象から外す。
    """
    lines = [t.strip() for t in trips.replace("\r\n", "\n").split("\n") if t.strip()][:40]
    if not lines: return {"ok":True,"shared_trips":[],"rm_trips":[]}
    async with pool.acquire() as c:
        rows = await c.fetch("SELECT trip, rm_games FROM shared_trips WHERE trip = ANY($1::text[])", lines)
    return {"ok":True,
            "shared_trips":[r["trip"] for r in rows],
            "rm_trips":[r["trip"] for r in rows if int(r["rm_games"] or 0) >= RM_EXEMPT_MIN]}

@app.get("/trip_identities")
async def ep_trip_identities(trips: str = Query(""), pool: asyncpg.Pool = Depends(get_pool)):
    """トリップごとの最多利用名と参戦数を一括返却する（RM本人照合用）。"""
    lines = []
    seen = set()
    for raw in trips.replace("\r\n", "\n").split("\n"):
        trip = raw.strip().lstrip("◆")
        if trip and trip not in seen:
            seen.add(trip); lines.append(trip)
    lines = lines[:40]
    if not lines:
        return {"ok": True, "results": {}}
    variants = list(dict.fromkeys([v for trip in lines for v in (trip, f"◆{trip}")]))
    async with pool.acquire() as c:
        rows = await c.fetch("""
            WITH grouped AS (
                SELECT regexp_replace(p.trip, '^◆', '') AS trip_key,
                       p.name,
                       COUNT(DISTINCT p.game_id)::int AS name_games,
                       MAX(COALESCE(g.started_at, p.created_at, 0))::bigint AS last_ts
                  FROM players p
                  JOIN games g ON g.id = p.game_id
                 WHERE p.is_spectator = 0
                   AND p.trip IS NOT NULL AND p.trip <> ''
                   AND p.name IS NOT NULL AND p.name <> ''
                   AND p.trip = ANY($1::text[])
                 GROUP BY regexp_replace(p.trip, '^◆', ''), p.name
            ), ranked AS (
                SELECT *,
                       SUM(name_games) OVER (PARTITION BY trip_key)::int AS total_games,
                       ROW_NUMBER() OVER (
                           PARTITION BY trip_key
                           ORDER BY name_games DESC, last_ts DESC, name ASC
                       ) AS rank_no
                  FROM grouped
            )
            SELECT trip_key, name AS top_name, name_games AS top_name_games,
                   total_games, last_ts
              FROM ranked
             WHERE rank_no = 1
        """, variants)
    by_trip = {r["trip_key"]: {
        "found": True,
        "top_name": r["top_name"],
        "top_name_games": int(r["top_name_games"] or 0),
        "total_games": int(r["total_games"] or 0),
        "last_ts": int(r["last_ts"] or 0),
    } for r in rows}
    return {"ok": True, "results": {
        trip: by_trip.get(trip, {"found": False, "top_name": "", "top_name_games": 0, "total_games": 0, "last_ts": 0})
        for trip in lines
    }}

@app.get("/vote_acc_rank")
async def ep_vote_acc_rank(limit:int=Query(100,ge=1,le=1000),offset:int=Query(0,ge=0),min_count:int=Query(0,ge=0),pool:asyncpg.Pool=Depends(get_pool)):
    """村側の対人狼投票率ランキング（取得済みゲームログ範囲での集計）。"""
    mv = max(min_count, 30)  # 最低30票は要求（少票数の100%を防ぐ）
    async with pool.acquire() as c:
        total = await c.fetchval("SELECT COUNT(*) FROM vote_stats WHERE votes>=$1", mv)
        rows = await c.fetch("""
            SELECT v.trip, v.votes, v.wolf_hits, v.accuracy,
                   COALESCE(ti.main_name,'') AS main_name
            FROM vote_stats v LEFT JOIN trip_index ti ON ti.trip=v.trip
            WHERE v.votes >= $1
            ORDER BY v.accuracy DESC, v.votes DESC LIMIT $2 OFFSET $3""", mv, limit, offset)
    return {"ok":True,"total":int(total or 0),"limit":limit,"offset":offset,
            "rows":[{"trip":r["trip"],"main_name":r["main_name"],"votes":int(r["votes"]),
                     "wolf_hits":int(r["wolf_hits"]),"accuracy":float(r["accuracy"])} for r in rows]}

@app.get("/rivalry")
async def ep_rivalry(trip_a:str=Query(""),trip_b:str=Query(""),pool:asyncpg.Pool=Depends(get_pool)):
    """2人の因縁データ: 同村数・同陣営/敵陣営勝率（全ゲーム対象）と相互投票数（ログ取得済み範囲）。"""
    a, b = trip_a.strip(), trip_b.strip()
    if not a or not b: return {"ok":False,"error":"trip_a と trip_b が必要です"}
    async with pool.acquire() as c:
        # ◆の有無ゆらぎを実在するトリップ表記に解決する
        async def _resolve(t):
            for cand in (t, "◆" + t.lstrip("◆"), t.lstrip("◆")):
                if await c.fetchval("SELECT 1 FROM trip_name_pairs WHERE trip=$1 LIMIT 1", cand):
                    return cand
            return t
        a = await _resolve(a); b = await _resolve(b)
        rows = await c.fetch("""
            SELECT pa.role AS ra, pa.win AS wa, pb.role AS rb, pb.win AS wb
            FROM players pa JOIN players pb ON pb.game_id = pa.game_id
            WHERE pa.trip=$1 AND pb.trip=$2 AND pa.is_spectator=0 AND pb.is_spectator=0""", a, b)
        vab = await c.fetchval("""
            SELECT COUNT(*) FROM game_votes v
            JOIN games g ON g.log_id=v.log_id AND g.game_index=v.game_index
            JOIN players pa ON pa.game_id=g.id AND pa.name=v.voter_name AND pa.trip=$1
            JOIN players pb ON pb.game_id=g.id AND pb.name=v.target_name AND pb.trip=$2""", a, b)
        vba = await c.fetchval("""
            SELECT COUNT(*) FROM game_votes v
            JOIN games g ON g.log_id=v.log_id AND g.game_index=v.game_index
            JOIN players pa ON pa.game_id=g.id AND pa.name=v.voter_name AND pa.trip=$1
            JOIN players pb ON pb.game_id=g.id AND pb.name=v.target_name AND pb.trip=$2""", b, a)
    same = [r for r in rows if _side_of(r["ra"]) == _side_of(r["rb"])]
    opp = [r for r in rows if _side_of(r["ra"]) != _side_of(r["rb"])]
    return {"ok":True,"trip_a":a,"trip_b":b,"common_games":len(rows),
            "same_side":{"games":len(same),"wins":sum(int(r["wa"] or 0) for r in same)},
            "opposite":{"games":len(opp),"a_wins":sum(int(r["wa"] or 0) for r in opp),
                        "b_wins":sum(int(r["wb"] or 0) for r in opp)},
            "votes_a_to_b":int(vab or 0),"votes_b_to_a":int(vba or 0)}

async def _badges_for(c, trip, games_total, first_ts, last_ts):
    badges = []
    now = int(time.time())
    if games_total >= 3000: badges.append({"id":"g3000","label":"三千戦の猛者"})
    elif games_total >= 1000: badges.append({"id":"g1000","label":"千戦錬磨"})
    if first_ts and now - first_ts >= 5*365*86400: badges.append({"id":"veteran","label":"古参"})
    if first_ts and now - first_ts <= 90*86400 and games_total >= 50: badges.append({"id":"rising","label":"新星"})
    rt = await c.fetchrow("SELECT rating FROM player_ratings WHERE trip=$1 OR trip=$2 LIMIT 1", trip, trip.lstrip("◆"))
    if rt and float(rt["rating"]) >= 1700: badges.append({"id":"legend","label":"レジェンド級レート"})
    vs = await c.fetchrow("SELECT accuracy, votes FROM vote_stats WHERE trip=$1", trip)
    if vs and int(vs["votes"]) >= 100 and float(vs["accuracy"]) >= 35: badges.append({"id":"sniper","label":"投票の名手"})
    fox = await c.fetchrow("SELECT COUNT(*) AS g, SUM(win) AS w FROM players WHERE trip=$1 AND role='妖狐' AND is_spectator=0", trip)
    if fox and int(fox["g"] or 0) >= 30 and int(fox["w"] or 0) / int(fox["g"]) >= 0.35: badges.append({"id":"fox","label":"妖狐使い"})
    return badges

@app.post("/roster_check")
async def ep_roster_check(request: Request, pool:asyncpg.Pool=Depends(get_pool)):
    """Chrome拡張用: いま村にいる面子(name,trip)一括照会。なりすまし判定・称号・レート・戦績。"""
    try:
        body = await request.json()
    except Exception:
        return {"ok":False,"error":"BAD_JSON"}
    players = body.get("players") or []
    if not isinstance(players, list) or len(players) > 30:
        return {"ok":False,"error":"BAD_PAYLOAD"}
    out = []
    ratings = []
    async with pool.acquire() as c:
        for pl in players[:30]:
            name = str(pl.get("name") or "").strip()
            trip = str(pl.get("trip") or "").strip()
            if not name: continue
            usual = await c.fetchrow("""
                SELECT trip, games FROM trip_name_pairs WHERE name=$1 AND trip<>'なし'
                ORDER BY games DESC LIMIT 1""", name)
            usual_trip = usual["trip"] if usual and int(usual["games"]) >= 20 else None
            norm = lambda t: (t or "").lstrip("◆")
            impersonation = bool(usual_trip and trip and norm(usual_trip) != norm(trip))
            ti = await c.fetchrow("SELECT games, first_ts, last_ts FROM trip_index WHERE trip=$1", trip) if trip else None
            games_total = int(ti["games"]) if ti else 0
            rt = await c.fetchrow("SELECT rating, games FROM player_ratings WHERE trip=$1 OR trip=$2 LIMIT 1", trip, norm(trip)) if trip else None
            rating = float(rt["rating"]) if rt else None
            if rating: ratings.append(rating)
            shared = False
            if trip:
                shared = bool(await c.fetchval("SELECT 1 FROM shared_trips WHERE trip=$1 LIMIT 1", trip))
            badges = await _badges_for(c, trip, games_total, int(ti["first_ts"]) if ti else 0, int(ti["last_ts"]) if ti else 0) if trip else []
            out.append({"name":name,"trip":trip,"usual_trip":usual_trip,"impersonation":impersonation,
                        "games":games_total,"rating":rating,"is_shared_trip":shared,"badges":badges})
    summary = {"players":len(out),
               "avg_rating":round(sum(ratings)/len(ratings)) if ratings else None,
               "beginners":sum(1 for o in out if o["games"] < 50),
               "veterans":sum(1 for o in out if o["games"] >= 1000),
               "shared_trips":sum(1 for o in out if o["is_shared_trip"]),
               "impersonations":sum(1 for o in out if o["impersonation"])}
    return {"ok":True,"summary":summary,"rows":out}

@app.get("/mvp_recent")
async def ep_mvp_recent(village:str=Query(""),names:str=Query(""),pool:asyncpg.Pool=Depends(get_pool)):
    """直近24hの該当ゲームのMVPを機械計算で返す（拡張のゲーム終了演出用）。

    village=村名 か names=参加者名カンマ区切り のどちらかで対象ゲームを特定する。
    """
    v = village.strip()
    nlist = [x.strip() for x in names.split(",") if x.strip()][:24]
    if not v and not nlist: return {"ok":False,"error":"village か names が必要です"}
    async with pool.acquire() as c:
        if nlist:
            g = await c.fetchrow("""
                SELECT g.id, g.log_id, g.started_at, g.haiyaku, COUNT(*) AS m
                FROM games g JOIN players p ON p.game_id=g.id AND p.name=ANY($1::text[])
                WHERE g.started_at > EXTRACT(EPOCH FROM NOW())::bigint - 86400
                GROUP BY g.id, g.log_id, g.started_at, g.haiyaku
                ORDER BY m DESC, g.started_at DESC LIMIT 1""", nlist)
            if g and int(g["m"]) < max(3, len(nlist) // 2): g = None
        else:
            g = await c.fetchrow("""
                SELECT id, log_id, started_at, haiyaku FROM games
                WHERE village_name=$1 AND started_at > EXTRACT(EPOCH FROM NOW())::bigint - 86400
                ORDER BY started_at DESC LIMIT 1""", v)
        if not g: return {"ok":True,"found":False,"msg":"集計待ち（取り込みは約10分毎です）"}
        prs = await c.fetch("""
            SELECT name, trip, role, win, talk_count, live_days, tsuiho_flag
            FROM players WHERE game_id=$1 AND is_spectator=0""", g["id"])
    scored = []
    for p in prs:
        s = 100 * int(p["win"] or 0) + 15 * int(p["live_days"] or 0) + min(int(p["talk_count"] or 0), 40)
        if p["role"] in ("占い師", "狩人", "霊能者") and int(p["win"] or 0): s += 30
        if p["role"] == "人狼" and int(p["win"] or 0): s += 25
        if p["role"] == "妖狐" and int(p["win"] or 0): s += 40
        if int(p["tsuiho_flag"] or 0): s -= 100
        scored.append({"name":p["name"],"role":p["role"],"win":int(p["win"] or 0),"score":s})
    scored.sort(key=lambda x: -x["score"])
    return {"ok":True,"found":True,"log_id":int(g["log_id"]),"haiyaku":g["haiyaku"],
            "mvp":scored[0] if scored else None,"top3":scored[:3]}

@app.get("/ai_eval_job")
async def ep_ai_eval_job(token:str=Query(""),pool:asyncpg.Pool=Depends(get_pool)):
    """AI評価ワーカー用: 次の未評価トリップ（戦数100以上・戦数降順）のジョブを返す。"""
    if token != AI_EVAL_TOKEN: return {"ok":False,"error":"FORBIDDEN"}
    async with pool.acquire() as c:
        t = await c.fetchrow("""
            SELECT ti.trip, ti.games, ti.main_name FROM trip_index ti
            LEFT JOIN player_ai_eval e ON e.trip = ti.trip
            WHERE ti.games >= 100 AND e.trip IS NULL
            ORDER BY ti.games DESC LIMIT 1""")
        if not t: return {"ok":True,"job":None,"done":True}
        trip = t["trip"]
        # 一晩の連戦に偏らないよう「1日あたり最大2戦」で直近10戦をサンプリングする
        grows = await c.fetch("""
            SELECT log_id, game_index, village_name, haiyaku, role, win, talk_count, live_days, name FROM (
              SELECT g.log_id, g.game_index, g.village_name, g.haiyaku, p.role, p.win,
                     p.talk_count, p.live_days, p.name, g.started_at,
                     ROW_NUMBER() OVER (
                       PARTITION BY TO_CHAR(TO_TIMESTAMP(g.started_at) AT TIME ZONE 'Asia/Tokyo','YYYY-MM-DD')
                       ORDER BY g.started_at DESC) AS rn_day
              FROM players p JOIN games g ON g.id = p.game_id
              WHERE p.trip = $1 AND p.is_spectator = 0
              ORDER BY g.started_at DESC LIMIT 300
            ) s WHERE rn_day <= 2 ORDER BY started_at DESC LIMIT 10""", trip)
        wr = await c.fetchrow("SELECT COUNT(*) AS g, SUM(win) AS w FROM players WHERE trip=$1 AND is_spectator=0", trip)
        out_games = []
        have_chat = 0
        for g in grows:
            cc = await c.fetchrow("SELECT lines_json, n_lines FROM game_chat_cache WHERE log_id=$1 AND game_index=$2", g["log_id"], g["game_index"])
            lines = json_mod.loads(cc["lines_json"]) if cc else None
            keep = []
            if lines:
                have_chat += 1
                name = g["name"] or ""
                idx = set()
                for i, l in enumerate(lines):
                    if name and l.startswith(name + ": "):
                        idx.add(i)
                        if i > 0: idx.add(i - 1)  # 直前の文脈行も入れる
                        if i + 1 < len(lines): idx.add(i + 1)
                    elif l.startswith("[鯖] "):
                        if _AI_SYS_RE.search(l): idx.add(i)
                    elif name and name in l:
                        idx.add(i)
                keep = [lines[i] for i in sorted(idx)]
            out_games.append({"log_id":g["log_id"],"village":g["village_name"],"haiyaku":g["haiyaku"],
                              "role":g["role"],"win":int(g["win"] or 0),"talk":int(g["talk_count"] or 0),
                              "live_days":int(g["live_days"] or 0),"name":g["name"],"lines":keep[:110]})
    if have_chat < min(6, len(out_games)):
        # ログ収集がまだ追いついていない
        return {"ok":True,"job":None,"waiting":trip,"have_chat":have_chat}
    games_total = int(wr["g"] or 0); wins = int(wr["w"] or 0)
    async with pool.acquire() as c:
        role_rows = await c.fetch("""
            SELECT role, COUNT(*) AS g, SUM(win) AS w FROM players
            WHERE trip=$1 AND is_spectator=0 GROUP BY role ORDER BY g DESC LIMIT 6""", trip)
        tstat = await c.fetchrow("""
            SELECT ROUND(AVG(talk_count),1) AS at, ROUND(AVG(live_days),1) AS al
            FROM players WHERE trip=$1 AND is_spectator=0""", trip)
        rt = await c.fetchrow("SELECT rating, games FROM player_ratings WHERE trip=$1", trip.lstrip("◆"))
        if not rt:
            rt = await c.fetchrow("SELECT rating, games FROM player_ratings WHERE trip=$1", trip)
    stats = {
        "roles": [{"role": r["role"], "games": int(r["g"]), "wins": int(r["w"] or 0)} for r in role_rows],
        "avg_talk": float(tstat["at"] or 0), "avg_live_days": float(tstat["al"] or 0),
        "rating": float(rt["rating"]) if rt else None, "rating_games": int(rt["games"]) if rt else 0,
    }
    return {"ok":True,"job":{"trip":trip,"main_name":t["main_name"],"games_total":games_total,
            "wins":wins,"win_rate":round(wins/games_total*100,1) if games_total else 0,
            "stats":stats,"games":out_games}}

@app.post("/ai_eval_submit")
async def ep_ai_eval_submit(request: Request, pool:asyncpg.Pool=Depends(get_pool)):
    """AI評価ワーカー用: 評価結果を保存する。保存した瞬間から個人戦績に表示される。"""
    try:
        body = await request.json()
    except Exception:
        return {"ok":False,"error":"BAD_JSON"}
    if body.get("token") != AI_EVAL_TOKEN: return {"ok":False,"error":"FORBIDDEN"}
    trip = str(body.get("trip") or "").strip()
    axes = body.get("axes") or {}
    if not trip or not isinstance(axes, dict) or len(axes) < 5:
        return {"ok":False,"error":"BAD_PAYLOAD"}
    axes = {k: max(0, min(1000, int(v))) for k, v in axes.items() if isinstance(v, (int, float))}
    total = max(0, min(1000, int(body.get("total") or 0)))
    comment = sanitize_text(str(body.get("comment") or ""), 1000)
    model = str(body.get("model") or "")[:64]
    games = [int(x) for x in (body.get("games") or []) if isinstance(x, (int, float))][:20]
    now = int(time.time())
    async with pool.acquire() as c:
        await c.execute("""
            INSERT INTO player_ai_eval (trip, axes_json, total, comment, model, games_json, evaluated_at)
            VALUES ($1,$2,$3,$4,$5,$6,$7)
            ON CONFLICT (trip) DO UPDATE SET axes_json=EXCLUDED.axes_json, total=EXCLUDED.total,
              comment=EXCLUDED.comment, model=EXCLUDED.model, games_json=EXCLUDED.games_json,
              evaluated_at=EXCLUDED.evaluated_at
        """, trip, json_mod.dumps(axes, ensure_ascii=False), total, comment, model,
             json_mod.dumps(games), now)
    return {"ok":True,"trip":trip,"total":total}

@app.get("/trip_lineage_index")
async def ep_trip_lineage_index(limit:int=Query(50,ge=1,le=200),offset:int=Query(0,ge=0),pool:asyncpg.Pool=Depends(get_pool)):
    """変名図タブのインデックス。10戦以上のトリップを戦数が多い順に列挙する。"""
    async with pool.acquire() as c:
        total = await c.fetchval("SELECT COUNT(*) FROM trip_index")
        rows = await c.fetch("SELECT trip, main_name, games, first_ts, last_ts FROM trip_index ORDER BY games DESC, last_ts DESC LIMIT $1 OFFSET $2", limit, offset)
    return {"ok":True,"total":int(total or 0),"limit":limit,"offset":offset,
            "rows":[{"trip":r["trip"],"main_name":r["main_name"],"games":int(r["games"]),"first_ts":int(r["first_ts"] or 0),"last_ts":int(r["last_ts"] or 0)} for r in rows]}

@app.get("/trip_lineage_search")
async def ep_trip_lineage_search(name:str=Query(""),limit:int=Query(50,ge=1,le=200),offset:int=Query(0,ge=0),pool:asyncpg.Pool=Depends(get_pool)):
    """名前からその名前を名乗ったことのあるトリップを最新参戦順に列挙する（変名図の名前検索用）。"""
    nm = name.strip()
    if not nm: return {"ok":False,"error":"name が指定されていません"}
    async with pool.acquire() as c:
        total = await c.fetchval("SELECT COUNT(*) FROM trip_name_pairs WHERE name=$1", nm)
        rows = await c.fetch("""
            SELECT p.trip, p.games AS name_games, p.first_ts, p.last_ts,
                   COALESCE(ti.games, 0) AS total_games, COALESCE(ti.main_name, '') AS main_name
            FROM trip_name_pairs p
            LEFT JOIN trip_index ti ON ti.trip = p.trip
            WHERE p.name = $1
            ORDER BY p.last_ts DESC
            LIMIT $2 OFFSET $3
        """, nm, limit, offset)
    return {"ok":True,"name":nm,"total":int(total or 0),"limit":limit,"offset":offset,
            "rows":[{"trip":r["trip"],"name_games":int(r["name_games"]),"first_ts":int(r["first_ts"] or 0),"last_ts":int(r["last_ts"] or 0),"total_games":int(r["total_games"]),"main_name":r["main_name"]} for r in rows]}

@app.get("/trip_lineage")
async def ep_trip_lineage(trip:str=Query(""),max_nodes:int=Query(80,ge=10,le=200),pool:asyncpg.Pool=Depends(get_pool)):
    """起点トリップから「トリップ⇄名前」の共有を連鎖的に辿った系譜。

    起点トリップが名乗った名前 → その名前が使った別トリップ → …を最古ログまで辿る。
    表示可能な規模に抑えるため、各段の名前は使用戦数上位60、ノード総数は max_nodes で打ち切り、
    打ち切りが起きた場合は truncated を返す。
    """
    root = trip.strip()
    if not root: return {"ok":False,"error":"trip が指定されていません"}
    async with pool.acquire() as c:
        r = await c.fetchrow("SELECT trip, SUM(games) AS g, MIN(first_ts) AS f, MAX(last_ts) AS l, (ARRAY_AGG(name ORDER BY games DESC))[1] AS mn FROM trip_name_pairs WHERE trip=$1 GROUP BY trip", root)
        if not r:
            alt = "◆" + root.lstrip("◆") if not root.startswith("◆") else root.lstrip("◆")
            r = await c.fetchrow("SELECT trip, SUM(games) AS g, MIN(first_ts) AS f, MAX(last_ts) AS l, (ARRAY_AGG(name ORDER BY games DESC))[1] AS mn FROM trip_name_pairs WHERE trip=$1 GROUP BY trip", alt)
            if not r: return {"ok":False,"error":"NOT_FOUND","msg":"このトリップの参戦記録がありません"}
            root = alt
        nodes = {root: {"trip": root, "main_name": r["mn"] or "", "via_name": None, "depth": 0,
                        "games": int(r["g"]), "first_ts": int(r["f"] or 0), "last_ts": int(r["l"] or 0)}}
        seen_names = set()
        frontier = [root]
        truncated = False
        for depth in range(1, 7):
            if not frontier or len(nodes) >= max_nodes: break
            nrows = await c.fetch("SELECT name, SUM(games) AS g FROM trip_name_pairs WHERE trip = ANY($1::text[]) GROUP BY name ORDER BY g DESC LIMIT 60", frontier)
            new_names = [x["name"] for x in nrows if x["name"] not in seen_names]
            if len(nrows) >= 60: truncated = True
            if not new_names: break
            seen_names.update(new_names)
            k = (max_nodes - len(nodes)) + 5
            trows = await c.fetch("""
                SELECT trip, (ARRAY_AGG(name ORDER BY games DESC))[1] AS via, SUM(games) AS g
                FROM trip_name_pairs WHERE name = ANY($1::text[]) GROUP BY trip ORDER BY g DESC LIMIT $2
            """, new_names, k)
            fresh = [x for x in trows if x["trip"] not in nodes]
            if len(trows) >= k: truncated = True
            if not fresh: continue
            take = fresh[: max(0, max_nodes - len(nodes))]
            if len(take) < len(fresh): truncated = True
            trips_new = [x["trip"] for x in take]
            inforows = await c.fetch("SELECT trip, SUM(games) AS g, MIN(first_ts) AS f, MAX(last_ts) AS l, (ARRAY_AGG(name ORDER BY games DESC))[1] AS mn FROM trip_name_pairs WHERE trip = ANY($1::text[]) GROUP BY trip", trips_new)
            info = {x["trip"]: x for x in inforows}
            for x in take:
                i = info.get(x["trip"])
                if not i: continue
                nodes[x["trip"]] = {"trip": x["trip"], "main_name": i["mn"] or "", "via_name": x["via"],
                                    "depth": depth, "games": int(i["g"]), "first_ts": int(i["f"] or 0), "last_ts": int(i["l"] or 0)}
            frontier = trips_new
    out = sorted(nodes.values(), key=lambda n: (n["first_ts"] or 0))
    return {"ok":True,"root":root,"count":len(out),"truncated":truncated,"nodes":out}

@app.get("/trip_chain_detail")
async def ep_trip_chain_detail(plain_trip:str=Query(""),trip:str=Query(""),pool:asyncpg.Pool=Depends(get_pool)):
    plain=plain_trip.strip()
    if not plain:
        raw=trip.strip()
        if not raw: return {"ok":False,"error":"NO_TRIP","msg":"plain_trip か trip を指定してください"}
        plain=normalize_plain_trip(raw)
    if not plain: return {"ok":False,"error":"INVALID_TRIP","msg":"plain_trip が正しく解釈できませんでした"}
    async with pool.acquire() as c:
        r = await c.fetchrow("SELECT * FROM plain_trip_chain_summary WHERE plain_trip=$1 LIMIT 1",plain)
        if not r: return {"ok":False,"error":"NOT_FOUND","msg":"指定のトリップの変遷情報がありません"}
        sp=int(r["span_seconds"])
        nrows = await c.fetch("SELECT * FROM plain_trip_chain_names WHERE plain_trip=$1 ORDER BY first_ts ASC",plain)
    return {"ok":True,"summary":{"plain_trip":r["plain_trip"],"games_total":int(r["games_total"]),"name_count":int(r["name_count"]),"first_ts":int(r["first_ts"]),"last_ts":int(r["last_ts"]),"span_seconds":sp,"span_days":round(sp/86400,1) if sp>0 else 0.0,"plain_games":int(r["plain_games"]),"fixed_games":int(r["fixed_games"]),"first_plain_ts":r["first_plain_ts"],"first_fixed_ts":r["first_fixed_ts"]},
        "names":[{"name":n["name"],"games_total":int(n["games_total"]),"first_ts":int(n["first_ts"]),"last_ts":int(n["last_ts"]),"plain_games":int(n["plain_games"]),"fixed_games":int(n["fixed_games"]),"first_plain_ts":n["first_plain_ts"],"first_fixed_ts":n["first_fixed_ts"]} for n in nrows]}

# ─── Multi Trip ───
@app.post("/multi_trip")
async def ep_multi_trip(body: MultiTripRequest, pool: asyncpg.Pool = Depends(get_pool)):
    text = body.trips.strip()
    if not text: return {"ok": False, "error": "NO_TRIPS"}
    lines = [l.strip() for l in text.replace("\r\n", "\n").split("\n") if l.strip()]
    if not lines: return {"ok": False, "error": "NO_VALID_TRIPS"}
    seen: set[str] = set(); trips: list[str] = []
    for t in lines:
        if t not in seen: seen.add(t); trips.append(t)
    async with pool.acquire() as c:
        sr = await c.fetch("SELECT trip,COUNT(*) AS tg,COUNT(DISTINCT name) AS un FROM players WHERE trip=ANY($1::text[]) AND is_spectator=0 GROUP BY trip", trips)
        sm = {r["trip"]: {"total_games": r["tg"], "unique_names": r["un"]} for r in sr}
        nr = await c.fetch("SELECT trip,name,COUNT(*) AS cnt FROM players WHERE trip=ANY($1::text[]) AND is_spectator=0 AND name IS NOT NULL AND name<>'' GROUP BY trip,name", trips)
        best: dict[str, tuple[str,int]] = {}
        for r in nr:
            if r["trip"] not in best or r["cnt"] > best[r["trip"]][1]: best[r["trip"]] = (r["name"], r["cnt"])
        lr = await c.fetch("SELECT u.t AS trip,sub.log_id FROM UNNEST($1::text[]) AS u(t) JOIN LATERAL (SELECT DISTINCT g2.log_id FROM players p2 JOIN games g2 ON g2.id=p2.game_id WHERE p2.trip=u.t AND p2.is_spectator=0 ORDER BY g2.log_id DESC LIMIT 5) sub ON true", trips)
        lm: dict[str, list[int]] = {}
        for r in lr: lm.setdefault(r["trip"], []); lid=r["log_id"]; (lm[r["trip"]].append(lid) if lid not in lm[r["trip"]] else None)
    return {"ok": True, "results": [{"trip": t, "total_games": sm.get(t, {}).get("total_games", 0), "unique_names": sm.get(t, {}).get("unique_names", 0), "logs": sorted(lm.get(t, []), reverse=True)[:5], "main_name": best[t][0] if t in best else None} for t in trips]}


# ╔═══════════════════════════════════════════════════════╗
# ║  8. 個人戦績（最重量エンドポイント）                       ║
# ╚═══════════════════════════════════════════════════════╝

WEEKDAY_LABELS = ["日", "月", "火", "水", "木", "金", "土"]

# 通常配役セット（これ以外はすべて「カオスその他」に集約して表示）
KNOWN_HAIYAKU: frozenset[str] = frozenset([
    '夏至配役', 'ルアー配役', '4A', '4A占', '6A', '6B', '6C', 'メチ配役',
    '8CFO', '8CNO（おはよーぐる配役）', '9D', '10A狩', '10C猫', '11C猫',
    '11A', '12A猫', '12B', '12B背徳猫（ゆきうさ配役）', '12W',
    '12R猫FO（ライス配役）', '13Z', '14D猫', '16A', '16D猫', '17A',
    '18A猫', '19D猫',
])

async def _pf_first_last(pool, kt, q):
    col = "trip" if kt == "trip" else "name"
    async with pool.acquire() as c:
        r = await c.fetchrow(f"SELECT MIN(g.started_at) AS ft, MAX(g.started_at) AS lt FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 AND p.{col}=$1", q)
    return (r["ft"], r["lt"]) if r else (None, None)

async def _pf_rating(pool, trip):
    async with pool.acquire() as c:
        r = await c.fetchrow("SELECT rating,games FROM player_ratings WHERE trip=$1 LIMIT 1", trip)
    return {"rating": float(r["rating"]), "games": int(r["games"])} if r else None

async def _pf_rows(pool, kt, q):
    col = "trip" if kt == "trip" else "name"; extra = ",g.rm_name" if kt == "trip" else ""
    async with pool.acquire() as c:
        return await c.fetch(f"SELECT p.*,g.village_name,g.log_id,g.started_at,g.haiyaku,g.haiyaku_class{extra} FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 AND p.{col}=$1 ORDER BY g.started_at ASC", q)

async def _pf_partners(pool, gids, kt, sv):
    if not gids: return {}
    col = "trip" if kt == "trip" else "name"
    async with pool.acquire() as c:
        rows = await c.fetch(f"""
            SELECT p.{col} AS k, COUNT(*) AS cnt
            FROM players p
            WHERE p.game_id = ANY($1::int[])
              AND p.is_spectator = 0
              AND p.{col} IS NOT NULL AND p.{col} <> ''
              AND p.{col} <> $2
            GROUP BY p.{col}
            ORDER BY cnt DESC
            LIMIT 30
        """, gids, sv)
    return {r["k"]: int(r["cnt"]) for r in rows}

async def _pf_recent(pool, kt, q, lim, ofs):
    col="trip" if kt=="trip" else "name"; extra=",g.rm_name" if kt=="trip" else ""
    async with pool.acquire() as c:
        rows = await c.fetch(f"SELECT g.log_id,g.village_name{extra},g.started_at FROM games g WHERE g.id IN (SELECT DISTINCT p.game_id FROM players p WHERE p.is_spectator=0 AND p.{col}=$1) ORDER BY g.started_at DESC LIMIT $2 OFFSET $3", q, lim+1, ofs)
    hm=len(rows)>lim; res=[]
    for r in rows[:lim]:
        d={"log_id":r["log_id"],"village_name":r["village_name"],"started_at":r["started_at"]}
        if kt=="trip": d["rm_name"]=r.get("rm_name")
        res.append(d)
    return res, hm

async def _pf_profile(pool, trip):
    async with pool.acquire() as c:
        r = await c.fetchrow("SELECT self_intro,x_id,x_display,updated_at FROM player_profile WHERE trip=$1 LIMIT 1", trip)
    if not r: return {"trip":trip,"self_intro":"","x_id":None,"x_display":None,"updated_at":None,"editable":False}
    return {"trip":trip,"self_intro":r["self_intro"] or "","x_id":r["x_id"],"x_display":r["x_display"],"updated_at":r["updated_at"],"editable":False}

async def _pf_wiki(pool, kt, kv):
    async with pool.acquire() as c:
        r = await c.fetchrow("SELECT id,body,updated_at FROM wiki WHERE key_type=$1 AND key_value=$2 LIMIT 1", kt, kv)
        if not r: return {"id":None,"body":"","updated_at":None,"updated_by_trip":None,"updated_by_name":None}
        h = await c.fetchrow("SELECT editor_name,editor_trip FROM wiki_hist WHERE key_type=$1 AND key_value=$2 ORDER BY created_at DESC LIMIT 1", kt, kv)
    return {"id":r["id"],"body":r["body"] or "","updated_at":r["updated_at"],"updated_by_trip":h["editor_trip"] if h else None,"updated_by_name":h["editor_name"] if h else None}

async def _pf_comments(pool, kt, kv, lim=50):
    async with pool.acquire() as c:
        rows = await c.fetch("SELECT id,body,author_name,author_trip,created_at FROM comments WHERE key_type=$1 AND key_value=$2 AND deleted=0 ORDER BY created_at DESC LIMIT $3", kt, kv, lim)
    return [{"id":r["id"],"body":r["body"],"author_name":r["author_name"] or "","author_trip":r["author_trip"],"created_at":r["created_at"]} for r in rows]

async def _pf_reverse(pool, kt, kv, lim=20):
    out={"names_to_trips":[],"trips_to_names":[]}
    async with pool.acquire() as c:
        if kt=="name":
            rows = await c.fetch("SELECT trip,COUNT(*) AS games FROM players WHERE is_spectator=0 AND name=$1 AND trip IS NOT NULL AND trip<>'' GROUP BY trip ORDER BY games DESC,trip ASC LIMIT $2", kv, lim)
            out["names_to_trips"]=[{"name":kv,"trips":[{"trip":r["trip"],"games":r["games"]} for r in rows]}]
        elif kt=="trip":
            rows = await c.fetch("SELECT name,COUNT(*) AS games FROM players WHERE is_spectator=0 AND trip=$1 AND name IS NOT NULL AND name<>'' GROUP BY name ORDER BY games DESC,name ASC LIMIT $2", kv, lim)
            out["trips_to_names"]=[{"trip":kv,"names":[{"name":r["name"],"games":r["games"]} for r in rows]}]
    return out

async def _pf_chain(pool, plain):
    async with pool.acquire() as c:
        r = await c.fetchrow("SELECT * FROM plain_trip_chain_summary WHERE plain_trip=$1 LIMIT 1", plain)
        if not r: return None
        nrs = await c.fetch("SELECT name,games_total,first_ts,last_ts,plain_games,fixed_games FROM plain_trip_chain_names WHERE plain_trip=$1 ORDER BY first_ts ASC", plain)
    sp=int(r["span_seconds"])
    return {"plain_trip":plain,"span_days":round(sp/86400,1) if sp>0 else 0.0,"games_total":int(r["games_total"]),"name_count":int(r["name_count"]),"names":[{"name":n["name"],"games_total":n["games_total"],"first_ts":n["first_ts"],"last_ts":n["last_ts"],"plain_games":n["plain_games"],"fixed_games":n["fixed_games"]} for n in nrs]}

async def _noop(): return None


# ── 内部集計（3分類: all/normal/chaos） ──────────

def _empty_bucket():
    return {"total_games":0,"wins":0,"talk_sum":0,"live_days_sum":0,
        "haiyaku":{},"role":{},"village":{},
        "by_hour":[[0,0] for _ in range(24)],"by_weekday":[[0,0] for _ in range(7)],
        "by_month":{},"calendar":{"start":None,"end":None,"days":{}}}

def _add_row_to_bucket(b, r):
    w=int(r["win"]); ts=int(r["started_at"]); tk=int(r["talk_count"]); lv=int(r["live_days"])
    b["total_games"]+=1; b["wins"]+=w; b["talk_sum"]+=tk; b["live_days_sum"]+=lv
    d=ts_to_jst_date(ts)
    if d not in b["calendar"]["days"]: b["calendar"]["days"][d]={"g":0,"w":0,"l":0}
    b["calendar"]["days"][d]["g"]+=1
    if w==1: b["calendar"]["days"][d]["w"]+=1
    elif w==0: b["calendar"]["days"][d]["l"]+=1
    cs=b["calendar"]["start"]; ce=b["calendar"]["end"]
    if cs is None or d<cs: b["calendar"]["start"]=d
    if ce is None or d>ce: b["calendar"]["end"]=d
    hk=str(r.get("haiyaku",""))
    if hk not in b["haiyaku"]: b["haiyaku"][hk]={"g":0,"w":0}
    b["haiyaku"][hk]["g"]+=1; b["haiyaku"][hk]["w"]+=w
    rk=str(r["role"])
    if rk not in b["role"]: b["role"][rk]={"g":0,"w":0}
    b["role"][rk]["g"]+=1; b["role"][rk]["w"]+=w
    vk=str(r["village_name"])
    b["village"][vk]=b["village"].get(vk,0)+1
    h=ts_to_jst_hour(ts); wd=ts_to_jst_weekday(ts); ym=ts_to_jst_ym(ts)
    b["by_hour"][h][0]+=1; b["by_hour"][h][1]+=w
    b["by_weekday"][wd][0]+=1; b["by_weekday"][wd][1]+=w
    if ym not in b["by_month"]: b["by_month"][ym]=[0,0]
    b["by_month"][ym][0]+=1; b["by_month"][ym][1]+=w


def _agg_internal_3class(rows):
    """DB行 → {buckets: {all,normal,chaos}, game_ids}"""
    bk = {"all": _empty_bucket(), "normal": _empty_bucket(), "chaos": _empty_bucket()}
    gids = set()
    for r in rows:
        _add_row_to_bucket(bk["all"], r)
        hc = r.get("haiyaku_class", "通常") if hasattr(r, 'get') else "通常"
        if hc == "通常":
            _add_row_to_bucket(bk["normal"], r)
        else:
            _add_row_to_bucket(bk["chaos"], r)
        gids.add(int(r["game_id"]))
    return {"buckets": bk, "game_ids": sorted(gids)}


def _merge_bucket(a, b):
    """2つのバケットを加算マージ"""
    if not a or a.get("total_games",0)==0: return b
    if not b or b.get("total_games",0)==0: return a
    m = {"total_games": a["total_games"]+b["total_games"], "wins": a["wins"]+b["wins"],
         "talk_sum": a["talk_sum"]+b["talk_sum"], "live_days_sum": a["live_days_sum"]+b["live_days_sum"]}
    for key in ("haiyaku","role"):
        d = defaultdict(lambda:{"g":0,"w":0})
        for src in (a,b):
            for k,v in src.get(key,{}).items(): d[k]["g"]+=v["g"]; d[k]["w"]+=v["w"]
        m[key]=dict(d)
    vill=defaultdict(int)
    for src in (a,b):
        for k,v in src.get("village",{}).items(): vill[k]+=v
    m["village"]=dict(vill)
    bh=[[0,0] for _ in range(24)]
    for src in (a,b):
        for i in range(24):
            s=src.get("by_hour",[[0,0]]*24)
            if i<len(s): bh[i][0]+=s[i][0]; bh[i][1]+=s[i][1]
    m["by_hour"]=bh
    bw=[[0,0] for _ in range(7)]
    for src in (a,b):
        for i in range(7):
            s=src.get("by_weekday",[[0,0]]*7)
            if i<len(s): bw[i][0]+=s[i][0]; bw[i][1]+=s[i][1]
    m["by_weekday"]=bw
    bm=defaultdict(lambda:[0,0])
    for src in (a,b):
        for k,v in src.get("by_month",{}).items(): bm[k][0]+=v[0]; bm[k][1]+=v[1]
    m["by_month"]=dict(bm)
    ca=a.get("calendar",{}); cb=b.get("calendar",{})
    cs=ca.get("start"); ce=ca.get("end"); bs=cb.get("start"); be_=cb.get("end")
    if bs and (cs is None or bs<cs): cs=bs
    if be_ and (ce is None or be_>ce): ce=be_
    cd={}
    for sc in (ca,cb):
        for d,v in sc.get("days",{}).items():
            if d not in cd: cd[d]={"g":0,"w":0,"l":0}
            cd[d]["g"]+=v.get("g",v.get("games",0)); cd[d]["w"]+=v.get("w",v.get("wins",0)); cd[d]["l"]+=v.get("l",v.get("loses",0))
    m["calendar"]={"start":cs,"end":ce,"days":cd}
    return m


def _merge_3class(a, b):
    """2つの3分類集計をマージ"""
    if not a: return b
    if not b: return a
    ab=a if isinstance(a,dict) and "all" in a else a.get("buckets",a)
    bb=b if isinstance(b,dict) and "all" in b else b.get("buckets",b)
    merged = {}
    for cat in ("all","normal","chaos"):
        merged[cat] = _merge_bucket(ab.get(cat,_empty_bucket()), bb.get(cat,_empty_bucket()))
    ga=set(a.get("game_ids",[])); gb=set(b.get("game_ids",[]))
    return {"buckets": merged, "game_ids": sorted(ga|gb)}


def _build_haiyaku_stats(haiyaku_dict: dict) -> list:
    """通常配役は個別表示、それ以外は「カオスその他」に集約"""
    def _r(g, w): return round(w / g * 100, 1) if g > 0 else 0
    normal_list = []
    chaos_g = chaos_w = 0
    for k, v in haiyaku_dict.items():
        if k in KNOWN_HAIYAKU:
            normal_list.append({"haiyaku": k, "games": v["g"], "wins": v["w"], "win_rate": _r(v["g"], v["w"])})
        else:
            chaos_g += v["g"]
            chaos_w += v["w"]
    result = sorted(normal_list, key=lambda x: -x["games"])
    if chaos_g > 0:
        result.append({"haiyaku": "カオスその他", "games": chaos_g, "wins": chaos_w, "win_rate": _r(chaos_g, chaos_w)})
    return result


def _format_bucket(b):
    """バケット → APIレスポンス形式"""
    total=b["total_games"]; wins=b["wins"]; ts=b["talk_sum"]; ls=b["live_days_sum"]
    def _r(g,w): return round(w/g*100,1) if g>0 else 0
    cal=b.get("calendar",{})
    api_days={}
    for d,v in cal.get("days",{}).items():
        api_days[d]={"games":v.get("g",0),"wins":v.get("w",0),"loses":v.get("l",0)}
    return {
        "summary":{"total_games":total,"wins":wins,"win_rate":round(wins/total*100,1) if total else 0,"talk_sum":ts,"live_days_sum":ls,"avg_talk_per_day":round(ts/ls,2) if ls else 0},
        "calendar":{"start":cal.get("start"),"end":cal.get("end"),"days":api_days},
        "haiyaku_stats": _build_haiyaku_stats(b.get("haiyaku", {})),
        "role_stats":sorted([{"role":k,"games":v["g"],"wins":v["w"],"win_rate":_r(v["g"],v["w"])} for k,v in b.get("role",{}).items()],key=lambda x:-x["games"]),
        "village_top":sorted([{"village":k,"games":v} for k,v in b.get("village",{}).items()],key=lambda x:-x["games"])[:30],
        "by_hour":[{"hour":i,"games":b["by_hour"][i][0],"wins":b["by_hour"][i][1],"win_rate":_r(b["by_hour"][i][0],b["by_hour"][i][1])} for i in range(24)],
        "by_weekday":[{"weekday":i,"label":WEEKDAY_LABELS[i],"games":b["by_weekday"][i][0],"wins":b["by_weekday"][i][1],"win_rate":_r(b["by_weekday"][i][0],b["by_weekday"][i][1])} for i in range(7)],
        "by_month":sorted([{"ym":k,"games":v[0],"wins":v[1],"win_rate":_r(v[0],v[1])} for k,v in b.get("by_month",{}).items()],key=lambda x:x["ym"]),
    }


def _format_3class(data):
    """3分類データ → API形式 (all のフル＋normal/chaosのsummaryのみ)"""
    bk = data.get("buckets", data) if isinstance(data, dict) else data
    if "all" not in bk:
        bk = {"all": bk, "normal": _empty_bucket(), "chaos": _empty_bucket()}
    all_fmt = _format_bucket(bk["all"])
    all_fmt["game_ids"] = data.get("game_ids", [])
    all_fmt["by_class"] = {
        "normal": _format_bucket(bk.get("normal", _empty_bucket()))["summary"],
        "chaos": _format_bucket(bk.get("chaos", _empty_bucket()))["summary"],
    }
    return all_fmt


def _agg(rows, kt):
    """旧互換: DB行 → API形式（3分類対応）"""
    internal = _agg_internal_3class(rows)
    return _format_3class(internal)


# ── キャッシュ読み書き ──

async def _read_personal_cache(pool, key_type, key_value):
    async with pool.acquire() as c:
        row = await c.fetchrow("SELECT watermark_game_id,stats_json,game_ids_json,partners_json,first_ts,last_ts FROM personal_stats_cache WHERE key_type=$1 AND key_value=$2 LIMIT 1", key_type, key_value)
    if not row: return None
    try:
        stats = json_mod.loads(row["stats_json"])
        gids = json_mod.loads(row["game_ids_json"])
        if "all" in stats:
            buckets = stats
        elif "buckets" in stats:
            buckets = stats["buckets"]
        else:
            buckets = {"all": stats, "normal": _empty_bucket(), "chaos": _empty_bucket()}
        partners = []
        try:
            partners = json_mod.loads(row["partners_json"] or "[]")
        except Exception:
            partners = []
        return {"watermark": int(row["watermark_game_id"]), "buckets": buckets, "game_ids": gids, "partners": partners, "first_ts": row["first_ts"], "last_ts": row["last_ts"]}
    except Exception:
        return None


async def _get_max_game_id(pool):
    async with pool.acquire() as c:
        return int(await c.fetchval("SELECT COALESCE(MAX(id),0) FROM games"))


async def _get_stats_updated_at(pool):
    """戦績DBへ最後に取り込まれたゲームの終了（なければ開始）日時。"""
    async with pool.acquire() as c:
        value = await c.fetchval(
            "SELECT COALESCE(finished_at, started_at, created_at, 0) "
            "FROM games ORDER BY id DESC LIMIT 1"
        )
    return int(value or 0)


async def _get_stats_checked_at(pool):
    """収集処理がzinro.netの最新ログ一覧を正常確認した時刻（新規ログ0件でも進む）。"""
    async with pool.acquire() as c:
        value = await c.fetchval(
            "SELECT updated_at FROM batch_state WHERE key='last_crawl_checked_at' LIMIT 1"
        )
    return int(value or 0)


async def _get_ranking_built_at(pool):
    """日次ランキングキャッシュが最後に構築された日時。"""
    async with pool.acquire() as c:
        value = await c.fetchval("SELECT COALESCE(MAX(built_at), 0) FROM rank_cache_json")
    return int(value or 0)


def _partners_list_to_map(partners):
    out = {}
    if isinstance(partners, list):
        for r in partners:
            if isinstance(r, dict) and r.get("key"):
                out[str(r["key"])] = int(r.get("games") or 0)
    elif isinstance(partners, dict):
        out = {str(k): int(v or 0) for k, v in partners.items()}
    return out


async def _write_personal_cache(pool, key_type, key_value, buckets, game_ids, partners, first_ts, last_ts, watermark=None):
    now = int(time.time())
    if watermark is None:
        watermark = await _get_max_game_id(pool)
    partners_json = json_mod.dumps(partners, ensure_ascii=False)
    async with pool.acquire() as c:
        await c.execute("""
            INSERT INTO personal_stats_cache
              (key_type,key_value,watermark_game_id,stats_json,game_ids_json,partners_json,first_ts,last_ts,updated_at)
            VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)
            ON CONFLICT (key_type,key_value) DO UPDATE SET
                watermark_game_id=EXCLUDED.watermark_game_id,
                stats_json=EXCLUDED.stats_json,
                game_ids_json=EXCLUDED.game_ids_json,
                partners_json=EXCLUDED.partners_json,
                first_ts=EXCLUDED.first_ts,
                last_ts=EXCLUDED.last_ts,
                updated_at=EXCLUDED.updated_at
        """, key_type, key_value, watermark, json_mod.dumps(buckets, ensure_ascii=False),
            json_mod.dumps(game_ids), partners_json, first_ts, last_ts, now)


async def _remember_personal_search(pool, key_type, key_value, cache_hit=False):
    now = int(time.time())
    try:
        async with pool.acquire() as c:
            await c.execute("""
                INSERT INTO personal_search_priority (key_type,key_value,search_count,last_searched_at,last_cache_hit)
                VALUES ($1,$2,1,$3,$4)
                ON CONFLICT (key_type,key_value) DO UPDATE SET
                    search_count=personal_search_priority.search_count+1,
                    last_searched_at=EXCLUDED.last_searched_at,
                    last_cache_hit=EXCLUDED.last_cache_hit
            """, key_type, key_value, now, 1 if cache_hit else 0)
    except Exception:
        logging.exception("failed to remember personal search")


async def _fetch_delta_rows(pool, key_type, key_value, watermark):
    col = "trip" if key_type == "trip" else "name"
    extra = ",g.rm_name" if key_type == "trip" else ""
    async with pool.acquire() as c:
        return await c.fetch(f"SELECT p.*,g.village_name,g.log_id,g.started_at,g.haiyaku,g.haiyaku_class{extra} FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 AND p.{col}=$1 AND g.id>$2 ORDER BY g.started_at ASC", key_value, watermark)


async def _pf_ai_eval(pool, key_type, kv):
    """AI（ローカルLLM）によるプレイヤー評価。トリップ検索時のみ返す。"""
    if key_type != "trip" or not kv: return None
    try:
        async with pool.acquire() as c:
            r = await c.fetchrow("SELECT axes_json,total,comment,model,games_json,evaluated_at FROM player_ai_eval WHERE trip=$1 LIMIT 1", kv)
        if not r: return None
        return {"axes": json_mod.loads(r["axes_json"] or "{}"), "total": int(r["total"]),
                "comment": r["comment"] or "", "model": r["model"] or "",
                "games": json_mod.loads(r["games_json"] or "[]"), "evaluated_at": int(r["evaluated_at"] or 0)}
    except Exception:
        return None

async def _pf_shared_trip(pool, key_type, kv) -> bool:
    """割れ酉判定（同一トリップを多数の名前で使用）に該当するか"""
    if key_type != "trip" or not kv: return False
    try:
        cand = {kv, "◆" + kv.lstrip("◆"), kv.lstrip("◆")}
        async with pool.acquire() as c:
            r = await c.fetchval("SELECT 1 FROM shared_trips WHERE trip = ANY($1::text[]) LIMIT 1", list(cand))
        return bool(r)
    except Exception:
        return False

def _build_personal_response(agg, ft, lt, fym, q, key_type, ri, pa, rl, rhm, wiki, cmts, prof, tc, rev, login, kv, limit, offset):
    resp = {"ok":True,"found":True,"query":q,"key_type":key_type,"first_ts":ft,"last_ts":lt,"first_ym":fym}
    resp.update({k: agg[k] for k in ("summary","calendar","haiyaku_stats","role_stats","village_top","by_hour","by_weekday","by_month")})
    resp["by_class"] = agg.get("by_class", {})
    resp.update({"partners_top":pa,"recent_logs":rl,"profile":prof,"wiki":wiki,"comments":cmts,
        "rating":ri,"trip_chain":tc,"reverse":rev,"recent_limit":limit,"recent_offset":offset,"recent_has_more":rhm})
    return resp


@app.get("/personal")
async def ep_personal(key_type:str=Query(...,pattern="^(trip|name)$"),q:str=Query(...,min_length=1),limit:int=Query(10,ge=1,le=50),offset:int=Query(0,ge=0,le=10000),page:int=Query(0,ge=0),reverse_limit:int=Query(20,ge=1,le=100),pool:asyncpg.Pool=Depends(get_pool),login:dict|None=Depends(optional_login)):
    if offset==0 and page>0: offset=page*limit
    offset=clamp(offset,0,10000); limit=clamp(limit,1,50); kv=q.strip()
    stats_updated_at, stats_checked_at = await asyncio.gather(
        _get_stats_updated_at(pool),
        _get_stats_checked_at(pool),
    )

    # ── キャッシュ確認 ──
    cache = await _read_personal_cache(pool, key_type, kv)
    await _remember_personal_search(pool, key_type, kv, cache_hit=bool(cache))
    if cache:
        delta_rows = await _fetch_delta_rows(pool, key_type, kv, cache["watermark"])
        if not delta_rows:
            # ★ 高速パス: キャッシュヒット＆差分ゼロ
            ft=cache["first_ts"]; lt=cache["last_ts"]
            fym=ts_to_jst_ym(ft) if ft and ft>0 else None
            agg=_format_3class({"buckets":cache["buckets"],"game_ids":cache["game_ids"]})
            game_ids=cache["game_ids"]; sv=kv
            ri=(await _pf_rating(pool,kv)) if key_type=="trip" else None
            cached_partners = _partners_list_to_map(cache.get("partners"))
            partner_task = _noop() if cached_partners else _pf_partners(pool,game_ids,key_type,sv)
            p2=[partner_task,_pf_recent(pool,key_type,kv,limit,offset),_pf_wiki(pool,key_type,kv),_pf_comments(pool,key_type,kv),_pf_reverse(pool,key_type,kv,reverse_limit),_pf_profile(pool,kv) if key_type=="trip" else _noop(),(_pf_chain(pool,normalize_plain_trip(q)) if normalize_plain_trip(q) else _noop()) if key_type=="trip" else _noop()]
            r2=await asyncio.gather(*p2)
            ps=r2[0]; rl,rhm=r2[1]; wiki=r2[2]; cmts=r2[3]; rev=r2[4]
            prof=r2[5] if key_type=="trip" else None; tc=r2[6] if key_type=="trip" else None
            if prof and login and login.get("trip")==kv: prof["editable"]=True
            if cached_partners:
                ps = cached_partners
            pa=sorted([{"key":k,"games":v} for k,v in ps.items()],key=lambda x:-x["games"])[:30]
            resp = _build_personal_response(agg,ft,lt,fym,q,key_type,ri,pa,rl,rhm,wiki,cmts,prof,tc,rev,login,kv,limit,offset)
            resp["is_shared_trip"] = await _pf_shared_trip(pool, key_type, kv)
            resp["ai_eval"] = await _pf_ai_eval(pool, key_type, kv)
            resp["stats_updated_at"] = stats_updated_at
            resp["stats_checked_at"] = stats_checked_at
            return resp

        # キャッシュ＋差分マージ
        delta=_agg_internal_3class(delta_rows)
        merged=_merge_3class({"buckets":cache["buckets"],"game_ids":cache["game_ids"]}, delta)
        agg=_format_3class(merged); game_ids=merged.get("game_ids",[])
        ft=cache["first_ts"]; lt=cache["last_ts"]
        for dr in delta_rows:
            dts=int(dr["started_at"])
            if ft is None or dts<ft: ft=dts
            if lt is None or dts>lt: lt=dts
        fym=ts_to_jst_ym(ft) if ft and ft>0 else None
        ri=(await _pf_rating(pool,kv)) if key_type=="trip" else None; sv=kv
        await _write_personal_cache(pool, key_type, kv, merged["buckets"], game_ids, cache.get("partners") or [], ft, lt)
    else:
        # フル計算フォールバック
        p1=[_pf_first_last(pool,key_type,kv),_pf_rows(pool,key_type,kv),_pf_rating(pool,kv) if key_type=="trip" else _noop()]
        r1=await asyncio.gather(*p1)
        ft,lt=r1[0]; rows=r1[1]; ri=r1[2] if key_type=="trip" else None
        fym=ts_to_jst_ym(ft) if ft and ft>0 else None
        if not rows: return {"ok":True,"found":False,"query":q,"key_type":key_type,"first_ts":ft,"last_ts":lt,"first_ym":fym,"stats_updated_at":stats_updated_at,"stats_checked_at":stats_checked_at,"data":None}
        agg=_agg(rows,key_type); game_ids=agg.get("game_ids",[]); sv=rows[0]["trip"] if key_type=="trip" else rows[0]["name"]

    # ── Phase 2 ──
    p2=[_pf_partners(pool,game_ids,key_type,sv),_pf_recent(pool,key_type,kv,limit,offset),_pf_wiki(pool,key_type,kv),_pf_comments(pool,key_type,kv),_pf_reverse(pool,key_type,kv,reverse_limit),_pf_profile(pool,kv) if key_type=="trip" else _noop(),(_pf_chain(pool,normalize_plain_trip(q)) if normalize_plain_trip(q) else _noop()) if key_type=="trip" else _noop()]
    r2=await asyncio.gather(*p2)
    ps=r2[0]; rl,rhm=r2[1]; wiki=r2[2]; cmts=r2[3]; rev=r2[4]
    prof=r2[5] if key_type=="trip" else None; tc=r2[6] if key_type=="trip" else None
    if prof and login and login.get("trip")==kv: prof["editable"]=True
    pa=sorted([{"key":k,"games":v} for k,v in ps.items()],key=lambda x:-x["games"])[:30]
    if not cache:
        internal = _agg_internal_3class(rows)
        await _write_personal_cache(pool, key_type, kv, internal["buckets"], game_ids, pa, ft, lt)
    resp = _build_personal_response(agg,ft,lt,fym,q,key_type,ri,pa,rl,rhm,wiki,cmts,prof,tc,rev,login,kv,limit,offset)
    resp["is_shared_trip"] = await _pf_shared_trip(pool, key_type, kv)
    resp["ai_eval"] = await _pf_ai_eval(pool, key_type, kv)
    resp["stats_updated_at"] = stats_updated_at
    resp["stats_checked_at"] = stats_checked_at
    return resp


@app.get("/personal_summary")
async def ep_personal_summary(
    key_type: str = Query(..., pattern="^(trip|name)$"),
    q: str = Query(..., min_length=1),
    pool: asyncpg.Pool = Depends(get_pool),
):
    """通常/カオスの戦数・勝率・初登場日だけを返す軽量エンドポイント。
    プロフィール一覧やカード表示など、概要だけ欲しい場面に使う。
    """
    kv = q.strip()
    def _r(g, w): return round(w / g * 100, 1) if g > 0 else 0

    # ── キャッシュ高速パス ──
    cache = await _read_personal_cache(pool, key_type, kv)
    if cache:
        bk = cache["buckets"]
        nb = bk.get("normal", _empty_bucket())
        cb = bk.get("chaos",  _empty_bucket())
        ft = cache["first_ts"]; lt = cache["last_ts"]
        return {
            "ok": True, "found": True, "key_type": key_type, "query": q,
            "first_ts": ft, "last_ts": lt,
            "first_ym": ts_to_jst_ym(ft) if ft else None,
            "normal": {
                "games": nb["total_games"], "wins": nb["wins"],
                "win_rate": _r(nb["total_games"], nb["wins"]),
            },
            "chaos": {
                "games": cb["total_games"], "wins": cb["wins"],
                "win_rate": _r(cb["total_games"], cb["wins"]),
            },
        }

    # ── フォールバック: 直接DB集計 ──
    col = "trip" if key_type == "trip" else "name"
    async with pool.acquire() as c:
        rows = await c.fetch(f"""
            SELECT g.haiyaku_class,
                   COUNT(*)        AS games,
                   SUM(p.win)      AS wins,
                   MIN(g.started_at) AS first_ts,
                   MAX(g.started_at) AS last_ts
            FROM players p JOIN games g ON g.id = p.game_id
            WHERE p.is_spectator = 0 AND p.{col} = $1
            GROUP BY g.haiyaku_class
        """, kv)

    if not rows:
        return {"ok": True, "found": False, "key_type": key_type, "query": q}

    normal = {"games": 0, "wins": 0, "win_rate": 0.0}
    chaos  = {"games": 0, "wins": 0, "win_rate": 0.0}
    first_ts = last_ts = None
    for r in rows:
        g = int(r["games"]); w = int(r["wins"])
        ft = r["first_ts"]; lt = r["last_ts"]
        if first_ts is None or (ft and ft < first_ts): first_ts = ft
        if last_ts  is None or (lt and lt > last_ts):  last_ts  = lt
        if r["haiyaku_class"] == "通常":
            normal = {"games": g, "wins": w, "win_rate": _r(g, w)}
        else:
            chaos["games"] += g; chaos["wins"] += w
    if chaos["games"] > 0:
        chaos["win_rate"] = _r(chaos["games"], chaos["wins"])

    return {
        "ok": True, "found": True, "key_type": key_type, "query": q,
        "first_ts": first_ts, "last_ts": last_ts,
        "first_ym": ts_to_jst_ym(first_ts) if first_ts else None,
        "normal": normal,
        "chaos":  chaos,
    }


# ─── Chrome拡張 一括サマリー系 ───

@app.get("/personal_lite")
async def ep_personal_lite(key_type: str = Query(..., pattern="^(trip|name)$"), q: str = Query(..., min_length=1), pool: asyncpg.Pool = Depends(get_pool)):
    """村一覧バッジ用: 名前/トリップの簡易戦績（total/normal/chaos の games・wins のみ）"""
    kv = q.strip(); col = "trip" if key_type == "trip" else "name"
    async with pool.acquire() as c:
        rows = await c.fetch(f"SELECT p.win, g.haiyaku_class FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 AND p.{col}=$1", kv)
    if not rows:
        return {"ok": True, "found": False, "total_games": 0, "normal_games": 0, "normal_wins": 0, "chaos_games": 0, "chaos_wins": 0}
    total = len(rows); wins = sum(int(r["win"]) for r in rows)
    ng = sum(1 for r in rows if str(r["haiyaku_class"] or "通常") == "通常")
    nw = sum(int(r["win"]) for r in rows if str(r["haiyaku_class"] or "通常") == "通常")
    cg = total - ng; cw = wins - nw
    return {"ok": True, "found": True, "total_games": total, "total_wins": wins, "win_rate": round(wins / total * 100, 1) if total else 0,
            "normal_games": ng, "normal_wins": nw, "normal_win_rate": round(nw / ng * 100, 1) if ng else 0,
            "chaos_games": cg, "chaos_wins": cw, "chaos_win_rate": round(cw / cg * 100, 1) if cg else 0}


@app.get("/multi_summary")
async def ep_multi_summary(
    key_type: str = Query(..., pattern="^(trip|name)$"),
    trips: str = Query(""),
    pool: asyncpg.Pool = Depends(get_pool),
):
    """Chrome拡張の戦績解析ボタン用: 複数名前/トリップの通常・カオス別集計を一括返却"""
    lines = [l.strip() for l in trips.replace("\r\n", "\n").split("\n") if l.strip()]
    if not lines:
        return {"ok": False, "error": "trips が空です"}
    col = "trip" if key_type == "trip" else "name"
    results = {}
    async with pool.acquire() as c:
        rows = await c.fetch(
            f"""SELECT p.{col} AS key, p.win, g.haiyaku_class,
                       MIN(g.started_at) OVER (PARTITION BY p.{col}) AS first_ts
                FROM players p
                JOIN games g ON g.id = p.game_id
                WHERE p.is_spectator = 0
                  AND p.{col} = ANY($1::text[])""",
            lines
        )
        shared_set: set = set()
        if key_type == "trip":
            try:
                shared_rows = await c.fetch("SELECT trip FROM shared_trips WHERE trip = ANY($1::text[])", lines)
                shared_set = {r["trip"] for r in shared_rows}
            except Exception:
                pass
    from collections import defaultdict
    per: dict = defaultdict(lambda: {"normal": [0, 0], "chaos": [0, 0], "first_ts": None})
    for r in rows:
        k = r["key"]; w = int(r["win"]); cls = str(r["haiyaku_class"] or "通常"); ft = r["first_ts"]
        if per[k]["first_ts"] is None or (ft and ft < per[k]["first_ts"]):
            per[k]["first_ts"] = ft
        if cls == "通常":
            per[k]["normal"][0] += 1; per[k]["normal"][1] += w
        else:
            per[k]["chaos"][0] += 1; per[k]["chaos"][1] += w
    def _r(g, w): return round(w / g * 100, 1) if g > 0 else 0
    for key in lines:
        if key not in per:
            results[key] = {"found": False, "normal": None, "chaos": None, "first_ym": None, "is_shared_trip": False}
            continue
        d = per[key]; ng, nw = d["normal"]; cg, cw = d["chaos"]; ft = d["first_ts"]
        fym = ts_to_jst_ym(ft) if ft else None
        results[key] = {
            "found": True,
            "normal": {"games": ng, "wins": nw, "win_rate": _r(ng, nw)},
            "chaos":  {"games": cg, "wins": cw, "win_rate": _r(cg, cw)},
            "first_ym": fym,
            "is_shared_trip": key in shared_set,
        }
    return {"ok": True, "results": results}


@app.get("/rm_games")
async def ep_rm_games(name: str = Query(..., min_length=1), limit: int = Query(10, ge=1, le=50), pool: asyncpg.Pool = Depends(get_pool)):
    """RM管理パネル用: RMが過去に主催したゲーム一覧（新しい順）"""
    rm = name.strip()
    async with pool.acquire() as c:
        total = await c.fetchval("SELECT COUNT(*) FROM games WHERE rm_name=$1", rm)
        rows = await c.fetch(
            "SELECT g.log_id, g.started_at, g.village_name, g.haiyaku, (SELECT COUNT(*) FROM players p WHERE p.game_id=g.id AND p.is_spectator=0) AS player_count FROM games g WHERE g.rm_name=$1 ORDER BY g.started_at DESC LIMIT $2",
            rm, limit
        )
    return {"ok": True, "rm_name": rm, "total_games": int(total or 0),
            "rows": [{"log_id": r["log_id"], "started_at": r["started_at"], "village_name": r["village_name"], "haiyaku": r["haiyaku"], "player_count": int(r["player_count"])} for r in rows]}


@app.get("/rm_list")
async def ep_rm_list(
    q: str = "",
    limit: int = Query(50, ge=1, le=100),
    offset: int = Query(0, ge=0),
    pool: asyncpg.Pool = Depends(get_pool),
):
    """RM集計タブ用: RMごとの主催ゲーム数一覧"""
    query = q.strip()
    where = ["rm_name IS NOT NULL", "rm_name<>''"]
    params: list[Any] = []
    if query:
        params.append(f"%{query}%")
        where.append(f"rm_name ILIKE ${len(params)}")
    where_sql = " AND ".join(where)
    limit_pos = len(params) + 1
    offset_pos = len(params) + 2
    async with pool.acquire() as c:
        total = await c.fetchval(
            f"SELECT COUNT(*) FROM (SELECT rm_name FROM games WHERE {where_sql} GROUP BY rm_name) s",
            *params,
        )
        rows = await c.fetch(
            f"""
            SELECT rm_name, COUNT(*) AS games, MIN(started_at) AS first_ts, MAX(started_at) AS last_ts
            FROM games
            WHERE {where_sql}
            GROUP BY rm_name
            ORDER BY games DESC, last_ts DESC, rm_name ASC
            LIMIT ${limit_pos} OFFSET ${offset_pos}
            """,
            *params, limit, offset,
        )
    return {
        "ok": True,
        "total": int(total or 0),
        "limit": limit,
        "offset": offset,
        "rows": [
            {
                "rm_name": r["rm_name"],
                "games": int(r["games"]),
                "first_ts": r["first_ts"],
                "last_ts": r["last_ts"],
            }
            for r in rows
        ],
    }


@app.get("/rm_detail")
async def ep_rm_detail(
    rm_name: str = Query(..., min_length=1),
    limit: int = Query(20, ge=1, le=100),
    offset: int = Query(0, ge=0),
    pool: asyncpg.Pool = Depends(get_pool),
):
    """RM集計タブ用: RM別サマリーとゲーム一覧"""
    rm = rm_name.strip()
    if not rm:
        return {"ok": False, "error": "rm_name required"}
    async with pool.acquire() as c:
        summary = await c.fetchrow(
            "SELECT COUNT(*) AS total_games, MIN(started_at) AS first_ts, MAX(started_at) AS last_ts FROM games WHERE rm_name=$1",
            rm,
        )
        total = int(summary["total_games"] or 0) if summary else 0
        if total == 0:
            return {"ok": True, "found": False, "rm_name": rm}
        village_rows = await c.fetch(
            "SELECT village_name, COUNT(*) AS games FROM games WHERE rm_name=$1 GROUP BY village_name ORDER BY games DESC, village_name ASC LIMIT 20",
            rm,
        )
        haiyaku_rows = await c.fetch(
            "SELECT haiyaku, COUNT(*) AS games FROM games WHERE rm_name=$1 GROUP BY haiyaku ORDER BY games DESC, haiyaku ASC LIMIT 20",
            rm,
        )
        game_rows = await c.fetch(
            """
            SELECT g.log_id, g.started_at, g.village_name, g.haiyaku,
                   (SELECT COUNT(*) FROM players p WHERE p.game_id=g.id AND p.is_spectator=0) AS player_count
            FROM games g
            WHERE g.rm_name=$1
            ORDER BY g.started_at DESC, g.log_id DESC
            LIMIT $2 OFFSET $3
            """,
            rm, limit, offset,
        )
    return {
        "ok": True,
        "found": True,
        "rm_name": rm,
        "total_games": total,
        "first_ts": summary["first_ts"],
        "last_ts": summary["last_ts"],
        "village_top": [{"village_name": r["village_name"], "games": int(r["games"])} for r in village_rows],
        "haiyaku_top": [{"haiyaku": r["haiyaku"], "games": int(r["games"])} for r in haiyaku_rows],
        "games": [
            {
                "log_id": r["log_id"],
                "started_at": r["started_at"],
                "village_name": r["village_name"],
                "haiyaku": r["haiyaku"],
                "player_count": int(r["player_count"] or 0),
            }
            for r in game_rows
        ],
        "limit": limit,
        "offset": offset,
    }


@app.get("/rm_participants")
async def ep_rm_participants(rm_name: str = Query(..., min_length=1), limit: int = Query(50, ge=1, le=200), pool: asyncpg.Pool = Depends(get_pool)):
    """RMが過去主催したゲームへの参加者一覧（参加回数降順・ログID付き）"""
    rm = rm_name.strip()
    async with pool.acquire() as c:
        game_rows = await c.fetch("SELECT id, log_id, started_at FROM games WHERE rm_name=$1 ORDER BY started_at DESC", rm)
    if not game_rows:
        return {"ok": True, "rm_name": rm, "total_games": 0, "players": []}
    game_ids = [r["id"] for r in game_rows]
    async with pool.acquire() as c:
        rows = await c.fetch(
            """SELECT p.name, p.trip, COUNT(*) AS cnt, ARRAY_AGG(DISTINCT g.log_id) AS log_ids
               FROM players p JOIN games g ON g.id=p.game_id
               WHERE p.game_id=ANY($1::int[]) AND p.is_spectator=0
               GROUP BY p.name, p.trip ORDER BY cnt DESC, p.name ASC LIMIT $2""",
            game_ids, limit
        )
    players = []
    for r in rows:
        log_ids = sorted([lid for lid in r["log_ids"] if lid], reverse=True)[:10]
        players.append({"name": r["name"], "trip": r["trip"] or "", "count": int(r["cnt"]), "log_ids": log_ids})
    return {"ok": True, "rm_name": rm, "total_games": len(game_rows), "players": players}


# ─── トリップ監査 / 発言RAG ───

@app.get("/trip12_cache_audit")
async def ep_trip12_cache_audit(
    limit: int = Query(100, ge=1, le=500),
    offset: int = Query(0, ge=0),
    pool: asyncpg.Pool = Depends(get_pool),
):
    """12桁トリップが戦績キャッシュへ格納済みか確認する。"""
    async with pool.acquire() as c:
        rows = await c.fetch("""
            WITH trip_keys AS MATERIALIZED (
                SELECT trip
                FROM players
                WHERE is_spectator=0 AND trip IS NOT NULL AND trip<>''
                GROUP BY trip
                HAVING length(trim(leading '◆' from trip))=12
            ), ranked AS (
                SELECT tk.trip,
                       CASE WHEN pc.key_value IS NULL THEN 0 ELSE 1 END AS cached,
                       pc.updated_at AS cache_updated_at
                FROM trip_keys tk
                LEFT JOIN personal_stats_cache pc ON pc.key_type='trip' AND pc.key_value=tk.trip
            ), counts AS (
                SELECT COUNT(*) AS total_12_trip, SUM(cached) AS cached_12_trip FROM ranked
            )
            SELECT r.trip, r.cached, r.cache_updated_at, c.total_12_trip, c.cached_12_trip
            FROM ranked r CROSS JOIN counts c
            ORDER BY r.cached ASC, r.trip ASC
            LIMIT $1 OFFSET $2
        """, limit, offset)
    total = int(rows[0]["total_12_trip"] or 0) if rows else 0
    cached = int(rows[0]["cached_12_trip"] or 0) if rows else 0
    return {
        "ok": True,
        "total_12_trip": total,
        "cached_12_trip": cached,
        "missing_12_trip": max(0, total - cached),
        "limit": limit,
        "offset": offset,
        "rows": [
            {"trip": r["trip"], "cached": bool(r["cached"]), "cache_updated_at": r["cache_updated_at"]}
            for r in rows
        ],
    }


async def _recent_player_keys(pool, days: int = 31, limit: int = 500):
    since = int(time.time()) - days * 86400
    async with pool.acquire() as c:
        rows = await c.fetch("""
            SELECT p.name, p.trip, COUNT(*) AS games, MAX(g.started_at) AS last_ts
            FROM players p JOIN games g ON g.id=p.game_id
            WHERE p.is_spectator=0 AND g.started_at >= $1
            GROUP BY p.name, p.trip
            ORDER BY games DESC, last_ts DESC
            LIMIT $2
        """, since, limit)
    return [{"name": r["name"] or "", "trip": r["trip"] or "", "games": int(r["games"]), "last_ts": r["last_ts"]} for r in rows]


@app.get("/recent_player_keys")
async def ep_recent_player_keys(days: int = Query(31, ge=1, le=366), limit: int = Query(500, ge=1, le=2000), pool: asyncpg.Pool = Depends(get_pool)):
    return {"ok": True, "days": days, "rows": await _recent_player_keys(pool, days, limit)}


@app.get("/player_talks")
async def ep_player_talks(
    key_type: str = Query(..., pattern="^(trip|name)$"),
    q: str = Query(..., min_length=1),
    days: int = Query(31, ge=1, le=366),
    limit: int = Query(500, ge=1, le=5000),
    pool: asyncpg.Pool = Depends(get_pool),
):
    kv = q.strip()
    col = "trip" if key_type == "trip" else "name"
    since = int(time.time()) - days * 86400
    async with pool.acquire() as c:
        rows = await c.fetch(f"""
            SELECT t.log_id, t.game_index, t.seq, t.created_at, t.day_label, t.phase,
                   t.from_user, t.to_user, t.name, t.trip, t.message,
                   g.village_name, g.haiyaku, g.started_at
            FROM player_talks t
            JOIN games g ON g.id=t.game_id
            WHERE t.{col}=$1 AND COALESCE(t.created_at, g.started_at) >= $2
            ORDER BY COALESCE(t.created_at, g.started_at) ASC, t.log_id ASC, t.game_index ASC, t.seq ASC
            LIMIT $3
        """, kv, since, limit)
        available = await c.fetchval("SELECT COUNT(*) FROM player_talks")
    return {
        "ok": True,
        "key_type": key_type,
        "query": q,
        "days": days,
        "talk_table_rows": int(available or 0),
        "rows": [
            {
                "log_id": r["log_id"], "game_index": r["game_index"], "seq": r["seq"],
                "created_at": r["created_at"], "day_label": r["day_label"], "phase": r["phase"],
                "name": r["name"], "trip": r["trip"], "message": r["message"],
                "village_name": r["village_name"], "haiyaku": r["haiyaku"], "started_at": r["started_at"],
            }
            for r in rows
        ],
    }


def _build_rag_context(talk_rows, max_chars: int = 12000):
    chunks = []
    used = 0
    for r in talk_rows:
        ts = r.get("created_at") or r.get("started_at") or 0
        line = f"[log:{r['log_id']} game:{r['game_index']} seq:{r['seq']} ts:{ts} {r.get('phase') or ''}] {r['name']} {r.get('trip') or ''}: {r['message']}"
        if used + len(line) + 1 > max_chars:
            break
        chunks.append(line)
        used += len(line) + 1
    return "\n".join(chunks)


async def _call_openai_analysis(prompt: str):
    s = get_settings()
    if not s.openai_api_key:
        return None
    payload = json_mod.dumps({
        "model": s.rag_llm_model,
        "messages": [
            {"role": "system", "content": "あなたは人狼ログの発言時系列を根拠に、プレイ傾向を簡潔に分析する。断定しすぎず、根拠ログIDを添える。"},
            {"role": "user", "content": prompt},
        ],
        "temperature": 0.2,
    }).encode("utf-8")
    req = UrlRequest(
        "https://api.openai.com/v1/chat/completions",
        data=payload,
        headers={"Authorization": f"Bearer {s.openai_api_key}", "Content-Type": "application/json"},
        method="POST",
    )
    def _run():
        with urlopen(req, timeout=45) as resp:
            return json_mod.loads(resp.read().decode("utf-8"))
    data = await asyncio.to_thread(_run)
    return data.get("choices", [{}])[0].get("message", {}).get("content")


@app.get("/player_rag_analysis")
async def ep_player_rag_analysis(
    key_type: str = Query(..., pattern="^(trip|name)$"),
    q: str = Query(..., min_length=1),
    days: int = Query(31, ge=1, le=93),
    limit: int = Query(800, ge=1, le=3000),
    run_llm: int = Query(0, ge=0, le=1),
    pool: asyncpg.Pool = Depends(get_pool),
):
    talks = await ep_player_talks(key_type=key_type, q=q, days=days, limit=limit, pool=pool)
    context = _build_rag_context(talks["rows"])
    source_hash = hashlib.sha256(context.encode("utf-8")).hexdigest()
    if not context:
        return {"ok": True, "found": False, "key_type": key_type, "query": q, "reason": "player_talks に対象発言がありません。parse_logs.php の再取り込みが必要です。"}
    since_ts = int(time.time()) - days * 86400
    async with pool.acquire() as c:
        cached = await c.fetchrow("SELECT analysis_json FROM player_rag_analysis_cache WHERE key_type=$1 AND key_value=$2 AND since_ts=$3 AND source_hash=$4 LIMIT 1", key_type, q.strip(), since_ts, source_hash)
    if cached:
        return {"ok": True, "found": True, "cached": True, **json_mod.loads(cached["analysis_json"])}

    prompt = "次の人狼オンライン発言ログをRAGコンテキストとして分析してください。\n観点: 発言量、進行/推理/雑談の傾向、同村者への反応、注意すべき偏り。根拠はlog_idを示してください。\n\n" + context
    llm_text = await _call_openai_analysis(prompt) if run_llm else None
    payload = {
        "key_type": key_type, "query": q, "days": days, "talk_count": len(talks["rows"]),
        "source_hash": source_hash, "rag_context": context,
        "analysis": llm_text,
        "llm_enabled": bool(llm_text),
    }
    if llm_text:
        async with pool.acquire() as c:
            await c.execute("""
                INSERT INTO player_rag_analysis_cache (key_type,key_value,since_ts,source_hash,analysis_json,updated_at)
                VALUES ($1,$2,$3,$4,$5,$6)
                ON CONFLICT (key_type,key_value,since_ts) DO UPDATE SET
                    source_hash=EXCLUDED.source_hash,
                    analysis_json=EXCLUDED.analysis_json,
                    updated_at=EXCLUDED.updated_at
            """, key_type, q.strip(), since_ts, source_hash, json_mod.dumps(payload, ensure_ascii=False), int(time.time()))
    return {"ok": True, "found": True, "cached": False, **payload}


# ─── 掲示板 ───

@app.get("/stats_updated_at")
async def ep_stats_updated_at(pool: asyncpg.Pool = Depends(get_pool)):
    stats_updated_at, stats_checked_at, ranking_built_at = await asyncio.gather(
        _get_stats_updated_at(pool),
        _get_stats_checked_at(pool),
        _get_ranking_built_at(pool),
    )
    return {
        "ok": True,
        "stats_updated_at": stats_updated_at,
        "stats_checked_at": stats_checked_at,
        "ranking_built_at": ranking_built_at,
    }


@app.get("/calc_trip")
async def ep_calc_trip(key: str = Query("", max_length=100)):
    trip = calc_trip_from_key(key)
    if not trip:
        return {"ok": False, "error": "トリップキーを入力してください"}
    return {"ok": True, "trip": trip}

_BBS_LIMIT = 20

@app.get("/bbs")
async def ep_bbs_get(
    limit: int = Query(_BBS_LIMIT, ge=1, le=50),
    offset: int = Query(0, ge=0),
    pool: asyncpg.Pool = Depends(get_pool),
):
    async with pool.acquire() as c:
        total = await c.fetchval("SELECT COUNT(*) FROM bbs_posts WHERE deleted=0")
        rows = await c.fetch(
            "SELECT id, author_name, author_trip, body, created_at FROM bbs_posts WHERE deleted=0 ORDER BY created_at DESC LIMIT $1 OFFSET $2",
            limit, offset,
        )
    posts = [
        {"id": r["id"], "name": r["author_name"], "trip": r["author_trip"] or "",
         "body": r["body"], "created_at": r["created_at"]}
        for r in rows
    ]
    return {"ok": True, "posts": posts, "total": int(total), "has_more": offset + limit < total}


@app.post("/bbs")
async def ep_bbs_post(
    body: BbsPostRequest,
    pool: asyncpg.Pool = Depends(get_pool),
):
    name = sanitize_text(body.name, 30).strip()
    # JSON/APIにはプレーンテキストを保存し、HTML化する画面側でのみエスケープする。
    # ここでもエスケープすると &amp; 等がDBに入り、画面側との二重処理になる。
    text = sanitize_text(body.body, 100).strip()
    raw_key = sanitize_text(body.trip_key, 100).strip() if body.trip_key else ""
    trip = calc_trip_from_key(raw_key) if raw_key else ""
    if not name:
        return {"ok": False, "error": "名前を入力してください"}
    if not text:
        return {"ok": False, "error": "本文を入力してください"}
    async with pool.acquire() as c:
        post_id = await c.fetchval(
            "INSERT INTO bbs_posts (body, author_name, author_trip, created_at) VALUES ($1,$2,$3,EXTRACT(EPOCH FROM NOW())::BIGINT) RETURNING id",
            text, name, trip,
        )
    return {"ok": True, "id": int(post_id), "trip": trip}
