File size: 4,818 Bytes
6e876c1 837a707 6e876c1 384e6b6 4bc82b0 8193c6f 4bc82b0 8193c6f 23938a8 6e876c1 837a707 6e876c1 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | """SQLAlchemy ORM 模型：User / Match / Stat。
数据关系（核心数据结构）：
- User 一名队员/管理员（管理员同时也可是球员）
- Match 一场比赛（比分由管理员录入）
- Stat 某队员在某场的进球/助攻/出勤（队员自己录入）
唯一约束 (user_id, match_id)：一人一场至多一条记录。
"无记录" 在聚合层视为「默认出勤、0 球 0 助攻」，从而消除特殊分支。
"""
from __future__ import annotations
from datetime import date, datetime
from sqlalchemy import (
Boolean,
Date,
DateTime,
ForeignKey,
Integer,
String,
UniqueConstraint,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(Integer, primary_key=True)
username: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
display_name: Mapped[str] = mapped_column(String(64), nullable=False)
password_hash: Mapped[str] = mapped_column(String(255), nullable=False)
jersey_number: Mapped[int | None] = mapped_column(Integer, nullable=True)
is_admin: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
# is_player=True 才计入球员榜单；领队/教练等可设为 False
is_player: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
# is_goalkeeper=True 的球员才显示/统计扑救数据，并出现在扑救榜
is_goalkeeper: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.now)
class Match(Base):
__tablename__ = "matches"
id: Mapped[int] = mapped_column(Integer, primary_key=True)
match_date: Mapped[date] = mapped_column(Date, nullable=False)
opponent: Mapped[str] = mapped_column(String(128), nullable=False)
home_away: Mapped[str] = mapped_column(String(8), default="home", nullable=False)
our_score: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
opp_score: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
note: Mapped[str | None] = mapped_column(String(255), nullable=True)
# mvp_locked=True：该场 MVP 投票已由管理员结算锁定，不再接受投/改票
mvp_locked: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
# 本场 MVP（由管理员结算时指定；None=尚未结算）。榜单统计获得次数。
# 支持双 MVP：mvp2_user_id 为第二人（可空；单 MVP 时留空）。
mvp_user_id: Mapped[int | None] = mapped_column(
ForeignKey("users.id"), nullable=True
)
mvp2_user_id: Mapped[int | None] = mapped_column(
ForeignKey("users.id"), nullable=True
)
# 铁血防守 MVP（同样由管理员结算指定；独立于最佳球员 MVP，可同场同人）
dmvp_user_id: Mapped[int | None] = mapped_column(
ForeignKey("users.id"), nullable=True
)
created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.now)
class Stat(Base):
__tablename__ = "stats"
__table_args__ = (UniqueConstraint("user_id", "match_id", name="uq_user_match"),)
id: Mapped[int] = mapped_column(Integer, primary_key=True)
user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), nullable=False)
match_id: Mapped[int] = mapped_column(ForeignKey("matches.id"), nullable=False)
goals: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
assists: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
# saves 扑救数：仅门将有意义，但所有行统一存储（默认 0），避免为门将单开一张表
saves: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
attended: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
updated_at: Mapped[datetime] = mapped_column(
DateTime, default=datetime.now, onupdate=datetime.now
)
class Vote(Base):
"""MVP 投票：每人每场至多投一票（投给某位队员）。
唯一约束 (voter_id, match_id)：一人一场一票，可改可撤。
"""
__tablename__ = "votes"
__table_args__ = (UniqueConstraint("voter_id", "match_id", name="uq_voter_match"),)
id: Mapped[int] = mapped_column(Integer, primary_key=True)
voter_id: Mapped[int] = mapped_column(ForeignKey("users.id"), nullable=False)
match_id: Mapped[int] = mapped_column(ForeignKey("matches.id"), nullable=False)
voted_for_id: Mapped[int] = mapped_column(ForeignKey("users.id"), nullable=False)
created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.now)
|