"""ORM models for 安全每日一题. Tables created by migration 313 (charset/
collation inherited from identity_persons so inline FKs are TiDB-safe)."""
from __future__ import annotations

import uuid
from datetime import date, datetime

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

from app.core.database import Base

# Platform UUID convention: hyphenated CHAR(36), matching identity_persons and
# every other domain. The bare `Mapped[uuid.UUID]` annotation would otherwise
# resolve to SQLAlchemy's stock ``Uuid`` (CHAR(32), dashes stripped) — which
# binds/compares in a *different* string form than the hyphenated values the
# 313/314 migrations seeded, silently breaking FK inserts and PK lookups on
# TiDB (where FKs are enforced). Always declare the explicit `Uuid` (= GUID)
# type on id / FK columns here. See [[reference_asyncmy_uuid_bind]].
from app.core.sql_types import GUID as Uuid

QUESTION_ACTIVE = "active"
QUESTION_ARCHIVED = "archived"

SOURCE_SEED = "seed"
SOURCE_AI = "ai"
SOURCE_MANUAL = "manual"
SOURCE_IMPORT = "import"

# Question types. ``single`` = one correct option (legacy default). ``multiple``
# = 2..N correct options (correct_key is the sorted concatenation, e.g. "ABD";
# an answer is correct only when the selected set matches exactly). ``boolean``
# = a 对/错 (true/false) question with two fixed options keyed T/F.
QTYPE_SINGLE = "single"
QTYPE_MULTIPLE = "multiple"
QTYPE_BOOLEAN = "boolean"
QTYPES = (QTYPE_SINGLE, QTYPE_MULTIPLE, QTYPE_BOOLEAN)


class SecQuizQuestion(Base):
    __tablename__ = "secquiz_questions"
    __table_args__ = (
        Index("ix_secquiz_questions_status", "status"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    prompt: Mapped[str] = mapped_column(Text, nullable=False)
    # JSON list of {"key": "A", "text": "..."} — 2..N options.
    options: Mapped[list] = mapped_column(JSON, nullable=False)
    # "single" | "multiple" | "boolean" — see QTYPES above.
    qtype: Mapped[str] = mapped_column(String(16), nullable=False, default=QTYPE_SINGLE)
    # single/boolean: one option key (e.g. "A"/"T"). multiple: sorted
    # concatenation of the correct keys (e.g. "ABD").
    correct_key: Mapped[str] = mapped_column(String(8), nullable=False)
    explanation: Mapped[str | None] = mapped_column(Text)
    # Non-baseline translations of the three reader-visible strings
    # (migration 627). ``{"en": {"prompt": …, "options": [{"key","text"}…],
    # "explanation": …}, "ja": {…}}``; a language key is present only when
    # that language has content, so "which languages does this question
    # exist in" is ``localized.keys()`` rather than six NULL checks.
    #
    # **Chinese is NOT in here.** ``prompt`` / ``options`` / ``explanation``
    # above stay the zh baseline, unlike ``secaw_materials.localized``
    # which carries all three languages. Two reasons: the 309 rows this
    # column was added to already hold their Chinese in the flat columns
    # (a move would have been a rewrite of every row plus every reader),
    # and ``correct_key`` is language-independent — it has to live beside
    # exactly one authoritative option list, and that list is the
    # baseline's.
    #
    # **Option KEYS are the contract, not the text.** A localized block's
    # option keys must be the same SET as the baseline's, because
    # ``correct_key`` is compared against what the reader picked and the
    # reader picked from the localized list. ``localization.py`` rejects a
    # block whose key set differs (falling back to the baseline rather
    # than grading somebody against options they never saw), and the
    # write path validates the same rule up front.
    localized: Mapped[dict | None] = mapped_column(JSON, nullable=True)
    category: Mapped[str] = mapped_column(String(32), nullable=False, default="general")
    difficulty: Mapped[str] = mapped_column(String(16), nullable=False, default="normal")
    status: Mapped[str] = mapped_column(String(16), nullable=False, default=QUESTION_ACTIVE)
    source: Mapped[str] = mapped_column(String(16), nullable=False, default=SOURCE_MANUAL)
    created_by: Mapped[uuid.UUID | None] = mapped_column(Uuid, ForeignKey("identity_persons.id"))
    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 SecQuizDaily(Base):
    __tablename__ = "secquiz_daily"
    __table_args__ = (
        UniqueConstraint("quiz_date", name="uq_secquiz_daily_date"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    quiz_date: Mapped[date] = mapped_column(Date, nullable=False)
    question_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("secquiz_questions.id"), nullable=False
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )


class SecQuizAnswer(Base):
    __tablename__ = "secquiz_answers"
    __table_args__ = (
        UniqueConstraint("person_id", "quiz_date", name="uq_secquiz_ans_person_date"),
        Index("ix_secquiz_answers_person", "person_id", "quiz_date"),
        Index("ix_secquiz_answers_date", "quiz_date", "is_correct"),
    )

    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
    )
    quiz_date: Mapped[date] = mapped_column(Date, nullable=False)
    question_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("secquiz_questions.id"), nullable=False
    )
    selected_key: Mapped[str] = mapped_column(String(8), nullable=False)
    is_correct: Mapped[bool] = mapped_column(Boolean, nullable=False)
    answered_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )


class SecQuizTeamSnapshot(Base):
    """Frozen weekly 部门擂台 standings — one row per (ISO-week, department).

    Written by the ``secquiz.team_battle_snapshot`` weekly cron so the page can
    show last week's winner + a week-over-week trend without recomputing
    history. ``department`` is the org-node name (no FK — departments are
    org-tree nodes, not a first-class table here); week_start is the Monday.
    No FK columns → TiDB-safe; week_start+department uniquely identifies a row.
    """
    __tablename__ = "secquiz_team_snapshots"
    __table_args__ = (
        UniqueConstraint(
            "week_start", "department", name="uq_secquiz_team_snap_week_dept"
        ),
        Index("ix_secquiz_team_snap_week", "week_start"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    week_start: Mapped[date] = mapped_column(Date, nullable=False)
    department: Mapped[str] = mapped_column(String(128), nullable=False)
    headcount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    participants: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    answered: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    correct: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    rank: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )


__all__ = [
    "SecQuizQuestion",
    "SecQuizDaily",
    "SecQuizAnswer",
    "SecQuizTeamSnapshot",
    "QUESTION_ACTIVE",
    "QUESTION_ARCHIVED",
    "SOURCE_SEED",
    "SOURCE_AI",
    "SOURCE_MANUAL",
    "SOURCE_IMPORT",
    "QTYPE_SINGLE",
    "QTYPE_MULTIPLE",
    "QTYPE_BOOLEAN",
    "QTYPES",
]