"""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)