#!/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

# ─── サードパーティ ─────────────────────────────────
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"
    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


# ╔═══════════════════════════════════════════════════════╗
# ║  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


# ╔═══════════════════════════════════════════════════════╗
# ║  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"})

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", "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",
    "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",
    "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_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",
}

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()
    logging.info("DB pool ready (min=%d, max=%d)", pool.get_min_size(), pool.get_max_size())
    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("/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

async def _rank_respond(pool, kind, key_type, ym, limit, offset, extra=None):
    scope = _parse_scope(ym)
    if scope is None: return {"ok":False,"error":"ym の形式が不正です"}
    if kind == "games" and key_type in ("name", "trip"):
        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}
    async with pool.acquire() as c:
        row = await c.fetchrow("SELECT payload,total 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_type)
    if row:
        p = json_mod.loads(row["payload"] or "{}"); ar = p.get("rows",[])
        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":p.get("total",len(ar)),"limit":limit,"offset":offset}
    return await _rank_fallback(pool, kind, key_type, ym, scope, limit, offset, extra or {})

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]
    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} 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} 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} 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} 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":
            rows = await c.fetch(f"SELECT p.name AS k,COUNT(*) AS times FROM players p JOIN games g ON g.id=p.game_id WHERE p.is_spectator=0 {ymf} AND p.live_days<(SELECT MAX(p2.live_days) FROM players p2 WHERE p2.game_id=p.game_id AND p2.is_spectator=0) AND p.name IS NOT NULL AND p.name<>'' GROUP BY p.name ORDER BY times DESC",*pb)
            out = [{"name":r["k"],"times":r["times"]} for r in rows]
        else:
            return {"ok":False,"error":f"フォールバック未実装: {kind}/{key_type}"}
    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),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})
    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("/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()
    if not rm: return {"ok":False,"error":"rm_name が必要です"}
    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}

@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}

@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 = ["日", "月", "火", "水", "木", "金", "土"]

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"
    # game_idが多いときはANY句が重いのでサブクエリ方式に切り替え
    if len(gids) > 200:
        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 IN (
                    SELECT game_id FROM players
                    WHERE is_spectator=0 AND {col}=$1
                )
                AND p.is_spectator = 0
                AND p.{col} IS NOT NULL AND p.{col} <> ''
                AND p.{col} <> $1
                GROUP BY p.{col}
                ORDER BY cnt DESC
                LIMIT 30
            """, sv)
    else:
        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 DISTINCT g.log_id,g.village_name{extra},g.started_at 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 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

def _agg(rows, kt):
    total=len(rows); wins=talk_sum=live_sum=0
    hai:dict=defaultdict(lambda:{"games":0,"wins":0}); role:dict=defaultdict(lambda:{"games":0,"wins":0}); vill:dict=defaultdict(int)
    hai_n:dict=defaultdict(lambda:{"games":0,"wins":0}); hai_c:dict=defaultdict(lambda:{"games":0,"wins":0})
    role_n:dict=defaultdict(lambda:{"games":0,"wins":0}); role_c:dict=defaultdict(lambda:{"games":0,"wins":0})
    bh=[[0,0] for _ in range(24)]; bw=[[0,0] for _ in range(7)]; bm:dict=defaultdict(lambda:[0,0])
    cd:dict={}; cs=ce=None
    for r in rows:
        w=int(r["win"]);ts=int(r["started_at"]);tk=int(r["talk_count"]);lv=int(r["live_days"])
        wins+=w;talk_sum+=tk;live_sum+=lv
        d=ts_to_jst_date(ts)
        if d not in cd: cd[d]={"games":0,"wins":0,"loses":0}
        cd[d]["games"]+=1
        if w==1: cd[d]["wins"]+=1
        elif w==0: cd[d]["loses"]+=1
        if cs is None or d<cs: cs=d
        if ce is None or d>ce: ce=d
        hkey=str(r["haiyaku"]); rkey=str(r["role"])
        hcls = str(r["haiyaku_class"] or "通常")
        hai[hkey]["games"]+=1;hai[hkey]["wins"]+=w
        role[rkey]["games"]+=1;role[rkey]["wins"]+=w
        if hcls=="通常":
            hai_n[hkey]["games"]+=1;hai_n[hkey]["wins"]+=w
            role_n[rkey]["games"]+=1;role_n[rkey]["wins"]+=w
        else:
            hai_c[hkey]["games"]+=1;hai_c[hkey]["wins"]+=w
            role_c[rkey]["games"]+=1;role_c[rkey]["wins"]+=w
        vill[str(r["village_name"])]+=1
        h=ts_to_jst_hour(ts);wd=ts_to_jst_weekday(ts);ym=ts_to_jst_ym(ts)
        bh[h][0]+=1;bh[h][1]+=w;bw[wd][0]+=1;bw[wd][1]+=w;bm[ym][0]+=1;bm[ym][1]+=w
    def _r(g,w): return round(w/g*100,1) if g>0 else 0
    def _mk_hai(d): return sorted([{"haiyaku":k,"games":v["games"],"wins":v["wins"],"win_rate":_r(v["games"],v["wins"])} for k,v in d.items()],key=lambda x:-x["games"])
    def _mk_role(d): return sorted([{"role":k,"games":v["games"],"wins":v["wins"],"win_rate":_r(v["games"],v["wins"])} for k,v in d.items()],key=lambda x:-x["games"])
    ng=sum(v["games"] for v in hai_n.values()); nw=sum(v["wins"] for v in hai_n.values())
    cg=sum(v["games"] for v in hai_c.values()); cw=sum(v["wins"] for v in hai_c.values())
    return {"summary":{"total_games":total,"wins":wins,"win_rate":round(wins/total*100,1) if total else 0,"talk_sum":talk_sum,"live_days_sum":live_sum,"avg_talk_per_day":round(talk_sum/live_sum,2) if live_sum else 0,
            "normal_games":ng,"normal_wins":nw,"normal_win_rate":_r(ng,nw),"chaos_games":cg,"chaos_wins":cw,"chaos_win_rate":_r(cg,cw)},
        "calendar":{"start":cs,"end":ce,"days":cd},
        "haiyaku_stats":_mk_hai(hai),
        "haiyaku_stats_normal":_mk_hai(hai_n),
        "haiyaku_stats_chaos":_mk_hai(hai_c),
        "role_stats":_mk_role(role),
        "role_stats_normal":_mk_role(role_n),
        "role_stats_chaos":_mk_role(role_c),
        "village_top":sorted([{"village":k,"games":v} for k,v in vill.items()],key=lambda x:-x["games"])[:30],
        "by_hour":[{"hour":i,"games":bh[i][0],"wins":bh[i][1],"win_rate":_r(bh[i][0],bh[i][1])} for i in range(24)],
        "by_weekday":[{"weekday":i,"label":WEEKDAY_LABELS[i],"games":bw[i][0],"wins":bw[i][1],"win_rate":_r(bw[i][0],bw[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 bm.items()],key=lambda x:x["ym"]),
        "game_ids":list({int(r["game_id"]) for r in rows})}

@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()
    # Phase 1
    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,"data":None}
    agg=_agg(rows,key_type);sv=rows[0]["trip"] if key_type=="trip" else rows[0]["name"]
    # Phase 2
    p2=[_pf_partners(pool,agg["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]
    return {"ok":True,"found":True,"query":q,"key_type":key_type,"first_ts":ft,"last_ts":lt,"first_ym":fym,
        "summary":agg["summary"],"calendar":agg["calendar"],
        "haiyaku_stats":agg["haiyaku_stats"],
        "haiyaku_stats_normal":agg["haiyaku_stats_normal"],
        "haiyaku_stats_chaos":agg["haiyaku_stats_chaos"],
        "role_stats":agg["role_stats"],
        "role_stats_normal":agg["role_stats_normal"],
        "role_stats_chaos":agg["role_stats_chaos"],
        "village_top":agg["village_top"],
        "partners_top":pa,"recent_logs":rl,"by_hour":agg["by_hour"],"by_weekday":agg["by_weekday"],"by_month":agg["by_month"],
        "profile":prof,"wiki":wiki,"comments":cmts,"rating":ri,"trip_chain":tc,"reverse":rev,"recent_limit":limit,"recent_offset":offset,"recent_has_more":rhm}
