#!/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 dce: 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 ice): 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 dtslt: 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化する画面側でのみエスケープする。 # ここでもエスケープすると & 等が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}