"""数据访问与聚合层。 对外只暴露「返回普通 dict / list」的函数,Shiny 层不直接碰 ORM 对象, 从而避免会话过期、跨线程等坑。聚合一律用 left-join 思路: matches 为基准,缺失的 Stat 记录按「默认出勤、0 球 0 助攻」处理。 """ from __future__ import annotations from datetime import date from typing import Any, Optional from sqlalchemy import extract from .db import get_session from .models import Match, Stat, User, Vote from .repo_base import _match_dict, _result, _season_clause, _season_match_ids from .security import hash_password, verify_password # ----------------------------------------------------------------------------- 赛季(按比赛年份) def available_seasons() -> list[int]: """所有出现过比赛的年份(赛季),降序。""" with get_session() as s: years = s.query(extract("year", Match.match_date)).distinct().all() return sorted({int(y[0]) for y in years if y[0] is not None}, reverse=True) # ----------------------------------------------------------------------------- 认证 / 用户 def authenticate(username: str, password: str) -> Optional[dict[str, Any]]: with get_session() as s: u = s.query(User).filter_by(username=username).first() if u and u.active and verify_password(password, u.password_hash): return { "id": u.id, "username": u.username, "display_name": u.display_name, "is_admin": u.is_admin, "is_goalkeeper": u.is_goalkeeper, "jersey": u.jersey_number, } return None def list_users(active_only: bool = False) -> list[dict[str, Any]]: with get_session() as s: q = s.query(User).order_by(User.is_admin.desc(), User.id) if active_only: q = q.filter_by(active=True) return [ { "id": u.id, "username": u.username, "display_name": u.display_name, "jersey": u.jersey_number, "is_admin": u.is_admin, "is_player": u.is_player, "is_goalkeeper": u.is_goalkeeper, "active": u.active, } for u in q.all() ] def add_user( username: str, display_name: str, password: str, jersey: Optional[int] = None, is_admin: bool = False, is_player: bool = True, is_goalkeeper: bool = False, ) -> None: username = (username or "").strip() display_name = (display_name or "").strip() if not username or not display_name or not password: raise ValueError("用户名、昵称、密码均不能为空") with get_session() as s: if s.query(User).filter_by(username=username).first(): raise ValueError(f"用户名「{username}」已存在") s.add( User( username=username, display_name=display_name, password_hash=hash_password(password), jersey_number=jersey, is_admin=bool(is_admin), is_player=bool(is_player), is_goalkeeper=bool(is_goalkeeper), active=True, ) ) def update_player_profile( user_id: int, display_name: str, jersey: Optional[int] ) -> None: """管理员修改队员的显示名(昵称)与球衣号;不改登录名与密码。 display_name 为空则忽略改名(避免误清空);jersey 为 None 表示无球衣号。 """ with get_session() as s: u = s.get(User, user_id) if u is None: return name = (display_name or "").strip() if name: u.display_name = name u.jersey_number = jersey def set_goalkeeper(user_id: int, is_goalkeeper: bool) -> None: """标记/取消某队员为门将(决定是否显示扑救录入、是否进扑救榜)。""" with get_session() as s: u = s.get(User, user_id) if u: u.is_goalkeeper = bool(is_goalkeeper) def reset_password(user_id: int, new_password: str) -> None: if not new_password: raise ValueError("新密码不能为空") with get_session() as s: u = s.get(User, user_id) if u: u.password_hash = hash_password(new_password) def change_password(user_id: int, old_password: str, new_password: str) -> bool: """队员自助改密:先校验旧密码,正确才更新。返回是否成功。""" if not new_password: raise ValueError("新密码不能为空") with get_session() as s: u = s.get(User, user_id) if u is None or not verify_password(old_password, u.password_hash): return False u.password_hash = hash_password(new_password) return True # ----------------------------------------------------------------------------- 比赛 def list_matches(season: Optional[int] = None) -> list[dict[str, Any]]: with get_session() as s: q = s.query(Match) cl = _season_clause(season) if cl is not None: q = q.filter(cl) ms = q.order_by(Match.match_date.desc(), Match.id.desc()).all() return [_match_dict(m) for m in ms] def add_match( match_date: date, opponent: str, home_away: str, our: int, opp: int, note: str = "", ) -> None: opponent = (opponent or "").strip() if not opponent: raise ValueError("对手名称不能为空") with get_session() as s: s.add( Match( match_date=match_date, opponent=opponent, home_away=home_away if home_away in ("home", "away") else "home", our_score=int(our or 0), opp_score=int(opp or 0), note=(note or "").strip(), ) ) def update_match( match_id: int, opponent: str, home_away: str, our: int, opp: int, ) -> None: with get_session() as s: m = s.get(Match, match_id) if not m: return m.opponent = (opponent or m.opponent).strip() m.home_away = home_away if home_away in ("home", "away") else m.home_away m.our_score = int(our or 0) m.opp_score = int(opp or 0) def delete_match(match_id: int) -> None: with get_session() as s: s.query(Stat).filter_by(match_id=match_id).delete() s.query(Vote).filter_by(match_id=match_id).delete() # 该场投票一并清理 m = s.get(Match, match_id) if m: s.delete(m) def clear_matches_and_stats() -> int: """清空所有比赛与个人数据(保留队员账号)。返回删除的比赛场数。""" with get_session() as s: n = s.query(Match).count() s.query(Stat).delete() s.query(Vote).delete() # 投票依附比赛,一并清空 s.query(Match).delete() return n # ----------------------------------------------------------------------------- 个人数据录入 def upsert_stat( user_id: int, match_id: int, goals: Any, assists: Any, attended: Any, saves: Any = 0, ) -> None: with get_session() as s: st = s.query(Stat).filter_by(user_id=user_id, match_id=match_id).one_or_none() if st is None: st = Stat(user_id=user_id, match_id=match_id) s.add(st) st.goals = max(0, int(goals or 0)) st.assists = max(0, int(assists or 0)) st.saves = max(0, int(saves or 0)) st.attended = bool(attended) # ----------------------------------------------------------------------------- 聚合 def team_summary(season: Optional[int] = None) -> dict[str, Any]: with get_session() as s: q = s.query(Match) cl = _season_clause(season) if cl is not None: q = q.filter(cl) ms = q.all() played = len(ms) w = d = l = gf = ga = 0 for m in ms: gf += m.our_score ga += m.opp_score r = _result(m.our_score, m.opp_score) if r == "W": w += 1 elif r == "D": d += 1 else: l += 1 return { "played": played, "wins": w, "draws": d, "losses": l, "gf": gf, "ga": ga, "gd": gf - ga, "points": w * 3 + d, "win_rate": round(100 * w / played) if played else 0, } def player_aggregate(season: Optional[int] = None) -> list[dict[str, Any]]: """每名在役队员的累计:进球、助攻、出场(总场次 - 缺勤场次)。 仅统计 is_player=True 的在役账号;领队/教练不计入榜单。 """ with get_session() as s: users = ( s.query(User) .filter_by(active=True, is_player=True) .order_by(User.id) .all() ) match_ids = _season_match_ids(s, season) total = len(match_ids) stats = ( s.query(Stat).filter(Stat.match_id.in_(match_ids)).all() if match_ids else [] ) agg: dict[int, dict[str, int]] = {} for st in stats: a = agg.setdefault(st.user_id, {"g": 0, "a": 0, "sv": 0, "absent": 0}) a["g"] += st.goals or 0 a["a"] += st.assists or 0 a["sv"] += st.saves or 0 if not st.attended: a["absent"] += 1 out: list[dict[str, Any]] = [] for u in users: a = agg.get(u.id, {"g": 0, "a": 0, "sv": 0, "absent": 0}) out.append( { "user_id": u.id, "name": u.display_name, "jersey": u.jersey_number, "is_admin": u.is_admin, "is_goalkeeper": u.is_goalkeeper, "goals": a["g"], "assists": a["a"], "saves": a["sv"], "ga": a["g"] + a["a"], "attended": total - a["absent"], "total": total, } ) return out def player_matchlog(user_id: int) -> list[dict[str, Any]]: """某队员逐场数据;无记录的场次按默认(出勤/0/0)返回。""" with get_session() as s: ms = s.query(Match).order_by(Match.match_date.desc(), Match.id.desc()).all() smap = {st.match_id: st for st in s.query(Stat).filter_by(user_id=user_id).all()} out: list[dict[str, Any]] = [] for m in ms: st = smap.get(m.id) row = _match_dict(m) row.update( { "attended": st.attended if st else True, "goals": st.goals if st else 0, "assists": st.assists if st else 0, "saves": st.saves if st else 0, "has_record": st is not None, } ) out.append(row) return out def player_rank(user_id: int) -> Optional[dict[str, Any]]: agg = player_aggregate() me = next((x for x in agg if x["user_id"] == user_id), None) if me is None: return None by_goals = sorted(agg, key=lambda x: (-x["goals"], -x["assists"])) by_assists = sorted(agg, key=lambda x: (-x["assists"], -x["goals"])) goal_rank = next(i for i, x in enumerate(by_goals, 1) if x["user_id"] == user_id) assist_rank = next(i for i, x in enumerate(by_assists, 1) if x["user_id"] == user_id) my_mvps = next( (r["mvps"] for r in mvp_ranking() if r["user_id"] == user_id), 0 ) my_dmvps = next( (r["dmvps"] for r in dmvp_ranking() if r["user_id"] == user_id), 0 ) return { **me, "goal_rank": goal_rank, "assist_rank": assist_rank, "n_players": len(agg), "mvps": my_mvps, "dmvps": my_dmvps, } def all_fifa_cards() -> list[dict[str, Any]]: """全队 FIFA 卡(按 OVR 降序):六维按「队内相对值」折算到 58–99。 维度:SHO 射术(进球) / PAS 传球(助攻) / STA 体能(出勤率) / HON 荣誉(最佳球员MVP) / DEF 防守(防守MVP,门将加扑救) / IMP 影响(综合)。 换算:58 + (你的值/队内峰值)^0.65 × 41——幂次曲线让「有一点数据就明显上分」, 不再是只有队内第一名才有分;地板 58 避免全员挤在 50 一坨,分层更真实。 没数据=58,队内该项最高者=99,随数据实时成长。 OVR:六维加权(射术/荣誉/传球权重高)。一次聚合产全队卡,避免逐人重复查询。 """ agg = player_aggregate() if not agg: return [] dmvp_all = {r["user_id"]: r["dmvps"] for r in dmvp_ranking()} mvp_all = {r["user_id"]: r["mvps"] for r in mvp_ranking()} def scale(mine: float, peak: float) -> int: """幂次相对值折算:地板 58,峰值 99,曲线 0.65 让中段更快抬头。""" if peak <= 0 or mine <= 0: return 58 return 58 + round(41 * (mine / peak) ** 0.65) peak_g = max(r["goals"] for r in agg) peak_a = max(r["assists"] for r in agg) peak_sv = max(r["saves"] for r in agg) peak_mvp = max(list(mvp_all.values()) + [0]) peak_dmvp = max(list(dmvp_all.values()) + [0]) peak_ga = max(r["ga"] for r in agg) peak_att = max((r["attended"] for r in agg), default=0) weights = {"SHO": .22, "PAS": .18, "STA": .12, "HON": .20, "DEF": .13, "IMP": .15} cards: list[dict[str, Any]] = [] for r in agg: uid = r["user_id"] my_mvp, my_dmvp = mvp_all.get(uid, 0), dmvp_all.get(uid, 0) # 体能:出勤场次相对队内最勤者(同曲线),全勤或队内最高即 99 # 防守分:防守 MVP 为主;门将再叠加扑救占比(取较高者) def_score = scale(my_dmvp, peak_dmvp) if r.get("is_goalkeeper"): def_score = max(def_score, scale(r["saves"], peak_sv)) # 影响分:参与进球与两类 MVP 的综合占比 imp_raw = (r["ga"] / peak_ga if peak_ga else 0) * 0.6 + ( (my_mvp + my_dmvp) / (peak_mvp + peak_dmvp) if (peak_mvp + peak_dmvp) else 0 ) stats = { "SHO": scale(r["goals"], peak_g), "PAS": scale(r["assists"], peak_a), "STA": scale(r["attended"], peak_att), "HON": scale(my_mvp, peak_mvp), "DEF": def_score, "IMP": scale(imp_raw, 1.0), } cards.append({ "user_id": uid, "ovr": round(sum(stats[k] * w for k, w in weights.items())), "pos": "GK" if r.get("is_goalkeeper") else "SDE", "stats": stats, "name": r["name"], "jersey": r["jersey"], # 背面真实数据(翻面展示 + 视图层生成寄语用) "goals": r["goals"], "assists": r["assists"], "ga": r["ga"], "saves": r["saves"], "attended": r["attended"], "total": r["total"], "mvps": my_mvp, "dmvps": my_dmvp, "is_goalkeeper": r.get("is_goalkeeper", False), }) return sorted(cards, key=lambda c: (-c["ovr"], c["name"])) def player_fifa_card(user_id: int) -> Optional[dict[str, Any]]: """单人 FIFA 卡(口径与 all_fifa_cards 完全一致)。""" return next((c for c in all_fifa_cards() if c["user_id"] == user_id), None) # ----------------------------------------------------------------------------- MVP 投票 def my_votes(voter_id: int) -> dict[int, int]: """某人已投的票:{match_id: voted_for_id},用于预填投票界面。""" with get_session() as s: rows = s.query(Vote.match_id, Vote.voted_for_id).filter_by(voter_id=voter_id).all() return {mid: vid for mid, vid in rows} def set_vote(voter_id: int, match_id: int, voted_for_id: Optional[int]) -> None: """投 / 改 / 撤 某场 MVP 票;voted_for_id 为 None 表示撤票。 若该场已被管理员结算锁定(mvp_locked),则忽略任何改票(防止结算后被改)。 """ with get_session() as s: m = s.get(Match, match_id) if m is not None and m.mvp_locked: return v = s.query(Vote).filter_by(voter_id=voter_id, match_id=match_id).one_or_none() if voted_for_id is None: if v is not None: s.delete(v) return if v is None: s.add(Vote(voter_id=voter_id, match_id=match_id, voted_for_id=voted_for_id)) else: v.voted_for_id = voted_for_id def mvp_ranking(season: Optional[int] = None) -> list[dict[str, Any]]: """当季 MVP 榜:按「获得当场 MVP 的次数」降序(含双 MVP,两人各计一次)。 仅统计已结算场次;投票只作结算参考,不直接上榜。 """ with get_session() as s: match_ids = _season_match_ids(s, season) if not match_ids: return [] rows = ( s.query(Match.mvp_user_id, Match.mvp2_user_id) .filter(Match.id.in_(match_ids), Match.mvp_user_id.isnot(None)) .all() ) users = { u.id: (u.display_name, u.jersey_number) for u in s.query(User).all() } tally: dict[int, dict[str, Any]] = {} for u1, u2 in rows: for uid in (u1, u2): if uid is None or uid not in users: continue name, jersey = users[uid] t = tally.setdefault( uid, {"user_id": uid, "name": name, "jersey": jersey, "mvps": 0} ) t["mvps"] += 1 return sorted(tally.values(), key=lambda x: (-x["mvps"], x["name"])) def dmvp_ranking(season: Optional[int] = None) -> list[dict[str, Any]]: """当季铁血防守 MVP 榜:按获得次数降序(每场由领队结算指定一人)。""" with get_session() as s: match_ids = _season_match_ids(s, season) if not match_ids: return [] rows = ( s.query(Match.dmvp_user_id, User.display_name, User.jersey_number) .join(User, Match.dmvp_user_id == User.id) .filter(Match.id.in_(match_ids), Match.dmvp_user_id.isnot(None)) .all() ) tally: dict[int, dict[str, Any]] = {} for uid, name, jersey in rows: t = tally.setdefault( uid, {"user_id": uid, "name": name, "jersey": jersey, "dmvps": 0} ) t["dmvps"] += 1 return sorted(tally.values(), key=lambda x: (-x["dmvps"], x["name"])) def settle_mvp( match_id: int, mvp_user_id: Optional[int], mvp2_user_id: Optional[int] = None, dmvp_user_id: Optional[int] = None, ) -> None: """管理员结算某场:最佳球员 MVP(可双)+ 铁血防守 MVP;全传 None=取消结算重开。 数据不变式:任一奖项有人=已结算锁票;最佳球员两栏同一人自动去重、第一栏空则 第二栏顶上;防守 MVP 独立评选,可与最佳球员同场同人。 """ if mvp_user_id is None and mvp2_user_id is not None: mvp_user_id, mvp2_user_id = mvp2_user_id, None if mvp2_user_id == mvp_user_id: mvp2_user_id = None with get_session() as s: m = s.get(Match, match_id) if m is None: return m.mvp_user_id = mvp_user_id m.mvp2_user_id = mvp2_user_id m.dmvp_user_id = dmvp_user_id m.mvp_locked = mvp_user_id is not None or dmvp_user_id is not None def settled_mvps() -> dict[int, dict[str, Any]]: """已结算场次的奖项:{match_id: {"user_ids": [...], "names": [...], "dmvp_name": str|None}}。 names 为最佳球员 MVP(单/双);dmvp_name 为铁血防守 MVP(可空)。 """ with get_session() as s: rows = ( s.query(Match.id, Match.mvp_user_id, Match.mvp2_user_id, Match.dmvp_user_id) .filter((Match.mvp_user_id.isnot(None)) | (Match.dmvp_user_id.isnot(None))) .all() ) names = {u.id: u.display_name for u in s.query(User).all()} out: dict[int, dict[str, Any]] = {} for mid, u1, u2, ud in rows: ids = [u for u in (u1, u2) if u is not None] out[mid] = { "user_ids": ids, "names": [names.get(u, "?") for u in ids], "dmvp_name": names.get(ud) if ud is not None else None, } return out def match_mvp_summary(match_ids: Optional[list[int]] = None) -> dict[int, dict[str, Any]]: """每场得票最高者(可并列):{match_id: {"names": [显示名,...], "votes": n}}。 仅含有票的场次;锁定与否都计算(锁定场即「已结算 MVP」,未锁定即「当前领先」)。 """ with get_session() as s: q = ( s.query(Vote.match_id, Vote.voted_for_id, User.display_name) .join(User, Vote.voted_for_id == User.id) ) if match_ids is not None: if not match_ids: return {} q = q.filter(Vote.match_id.in_(match_ids)) rows = q.all() tally: dict[int, dict[int, list[Any]]] = {} for mid, uid, name in rows: per = tally.setdefault(mid, {}) if uid not in per: per[uid] = [name, 0] per[uid][1] += 1 out: dict[int, dict[str, Any]] = {} for mid, per in tally.items(): top = max(c for _, c in per.values()) names = [name for name, c in per.values() if c == top] out[mid] = {"names": names, "votes": top} return out def match_contributions(season: Optional[int] = None) -> dict[int, dict[str, list[str]]]: """每场的进球/助攻明细:{match_id: {"goals": ["Leo×2", "Mark"], "assists": ["HWL"]}}。 仅含该场进球或助攻 > 0 的球员;同一人多球记为「名字×N」,按数量降序。 一次查询批量返回,供主页逐场展示,不影响既有聚合逻辑。 """ g_tmp: dict[int, list[tuple[str, int]]] = {} a_tmp: dict[int, list[tuple[str, int]]] = {} with get_session() as s: season_ids = set(_season_match_ids(s, season)) if season else None rows = ( s.query(Stat.match_id, Stat.goals, Stat.assists, User.display_name) .join(User, Stat.user_id == User.id) .filter((Stat.goals > 0) | (Stat.assists > 0)) .all() ) for mid, goals, assists, name in rows: if season_ids is not None and mid not in season_ids: continue if goals and goals > 0: g_tmp.setdefault(mid, []).append((name, goals)) if assists and assists > 0: a_tmp.setdefault(mid, []).append((name, assists)) def _fmt(items: list[tuple[str, int]]) -> list[str]: return [f"{n}×{c}" if c > 1 else n for n, c in sorted(items, key=lambda x: -x[1])] out: dict[int, dict[str, list[str]]] = {} for mid in set(g_tmp) | set(a_tmp): out[mid] = {"goals": _fmt(g_tmp.get(mid, [])), "assists": _fmt(a_tmp.get(mid, []))} return out