"""ORM models for 放松一下 (Security Arcade) — points wallet, ledger, and
server-judged game sessions. Tables created by migration 411.

Design notes
------------
* **Wallet row = the per-person concurrency anchor.** Every mutation
  (quiz-credit reconcile, game-entry spend) starts with ``SELECT … FOR
  UPDATE`` on ``arcade_wallets`` so balance math and the daily play cap
  are race-free without touching ``identity_persons`` (locking that table
  during fan-out once caused a login brown-out — see
  [[reference_outbox_fk_lock_login_outage]]).
* **Ledger is the audit trail**, wallet.balance is the transactional
  truth; ``UNIQUE(person_id, reason, ref_id)`` makes every earn/spend
  idempotent (re-running a reconcile can never double-credit).
* **Sessions hold the server-side game truth**: the ``payload`` column
  stores the full secret state (questions + correct keys, grids, …); the
  player only ever receives the public projection. Scores are computed
  server-side at submit, so leaderboards can trust them.
"""
from __future__ import annotations

import uuid
from datetime import date, datetime

from sqlalchemy import (
    JSON,
    Date,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    String,
    UniqueConstraint,
    func,
)
from sqlalchemy.orm import Mapped, mapped_column

from app.core.database import Base

# Platform UUID convention: hyphenated CHAR(36) (GUID), matching
# identity_persons and the sibling security_quiz tables. A bare
# ``Mapped[uuid.UUID]`` would resolve to SQLAlchemy's stock ``Uuid``
# (CHAR(32), dashes stripped) which binds/compares in a different string
# form and silently breaks FK inserts / cross-table joins on TiDB. See
# [[reference_asyncmy_uuid_bind]].
from app.core.sql_types import GUID as Uuid

# ── Session lifecycle ────────────────────────────────────────────────
SESSION_ACTIVE = "active"
SESSION_COMPLETED = "completed"
SESSION_EXPIRED = "expired"
SESSION_STATUSES = (SESSION_ACTIVE, SESSION_COMPLETED, SESSION_EXPIRED)

# ── Ledger reasons ───────────────────────────────────────────────────
REASON_QUIZ_BACKFILL = "quiz_backfill"   # one-time historical grant (ref=as-of date)
REASON_DAILY_QUIZ = "daily_quiz"         # per-day quiz credit (ref=quiz_date iso)
REASON_GAME_ENTRY = "game_entry"         # entrance fee (ref=session id)
# 安全宣贯素材小测通过 (ref=material id). Credited once per material by
# ``core.coordinators.awareness_points_coordinator`` — the awareness
# domain must not import this one, and the amount is deliberately small
# (see that module: the moment points become the reason to click, the
# acknowledgement stops meaning anything).
REASON_AWARENESS_QUIZ = "awareness_quiz"
LEDGER_REASONS = (
    REASON_QUIZ_BACKFILL, REASON_DAILY_QUIZ, REASON_GAME_ENTRY,
    REASON_AWARENESS_QUIZ,
)


class ArcadeWallet(Base):
    __tablename__ = "arcade_wallets"

    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), primary_key=True
    )
    balance: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    total_earned: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    total_spent: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    # Watermark: daily-quiz answers with quiz_date <= this are already
    # credited (either individually or via the one-time backfill row).
    quiz_credited_through: Mapped[date | None] = mapped_column(Date)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(), onupdate=func.now()
    )


class ArcadePointsLedger(Base):
    __tablename__ = "arcade_points_ledger"
    __table_args__ = (
        UniqueConstraint("person_id", "reason", "ref_id", name="uq_arcade_ledger_ref"),
        Index("ix_arcade_ledger_person", "person_id", "created_at"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), nullable=False
    )
    delta: Mapped[int] = mapped_column(Integer, nullable=False)
    balance_after: Mapped[int] = mapped_column(Integer, nullable=False)
    reason: Mapped[str] = mapped_column(String(32), nullable=False)
    # quiz_backfill → as-of date iso; daily_quiz → quiz_date iso;
    # game_entry → session UUID. Never empty (the unique key relies on it).
    ref_id: Mapped[str] = mapped_column(String(64), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )


class ArcadeGameSession(Base):
    __tablename__ = "arcade_game_sessions"
    __table_args__ = (
        # Belt-and-braces on the daily play cap: the wallet row lock
        # serializes normal traffic; this unique key kills any residual
        # duplicate-seq race at the DB layer.
        UniqueConstraint("person_id", "play_date", "play_seq", name="uq_arcade_sess_seq"),
        Index("ix_arcade_sess_person", "person_id", "play_date"),
        Index("ix_arcade_sess_game_score", "game_key", "score"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), nullable=False
    )
    game_key: Mapped[str] = mapped_column(String(32), nullable=False)
    # Game sub-mode a round was played in — e.g. the trivia category, or
    # 'mixed'. Drives per-category leaderboards. NULL for games with no
    # sub-modes. (Added by migration 412.)
    variant: Mapped[str | None] = mapped_column(String(32))
    status: Mapped[str] = mapped_column(String(16), nullable=False, default=SESSION_ACTIVE)
    # Person-local calendar date (resolve_timezone) — drives the daily cap.
    play_date: Mapped[date] = mapped_column(Date, nullable=False)
    play_seq: Mapped[int] = mapped_column(Integer, nullable=False)
    cost_points: Mapped[int] = mapped_column(Integer, nullable=False)
    # Full server-side game state incl. answers/solution (NEVER sent to the
    # client as-is — routers return the public projection only).
    payload: Mapped[dict] = mapped_column(JSON, nullable=False)
    # Submission + grading breakdown, set at completion.
    result: Mapped[dict | None] = mapped_column(JSON)
    score: Mapped[int | None] = mapped_column(Integer)
    # Denormalized right-answer count from the grade (games with a
    # right/wrong notion only); powers the 科举 knowledge leaderboard's
    # lifetime-correct SUM. NULL for 舒尔特/记忆翻牌. (migration 413)
    correct_count: Mapped[int | None] = mapped_column(Integer)
    duration_ms: Mapped[int | None] = mapped_column(Integer)
    started_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )
    finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


__all__ = [
    "ArcadeWallet",
    "ArcadePointsLedger",
    "ArcadeGameSession",
    "SESSION_ACTIVE",
    "SESSION_COMPLETED",
    "SESSION_EXPIRED",
    "SESSION_STATUSES",
    "REASON_AWARENESS_QUIZ",
    "REASON_QUIZ_BACKFILL",
    "REASON_DAILY_QUIZ",
    "REASON_GAME_ENTRY",
    "LEDGER_REASONS",
]