"""ORM models for the 成就徽章 (achievements) domain.

Only UNLOCKS are persisted — the badge *catalog* (codes, names, icons,
criteria) lives in code (`catalog.py`) because the unlock criteria ARE code
logic (tenure math, quiz streak, cloud-grant existence …) that a definition
table couldn't express. One row per (person, badge) the first time it's
earned; the unique key makes the unlock idempotent (re-evaluating never
double-inserts). No FK columns → TiDB-safe (table created by migration 319).
"""
from __future__ import annotations

import uuid
from datetime import datetime

from sqlalchemy import DateTime, Index, String, 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 (see sibling security_quiz/models.py). A bare
# ``Mapped[uuid.UUID]`` resolves to SQLAlchemy's stock ``Uuid`` (CHAR(32),
# dashes stripped on MySQL/TiDB) — which binds/compares in a *different* string
# form than the hyphenated values every other table stores, so any raw-SQL /
# cross-table string join on ``person_id`` silently matches 0 rows. Declare the
# explicit ``Uuid`` (= GUID) type on id / person_id. See
# [[reference_asyncmy_uuid_bind]] and [[reference_scheduled_jobs_nodash_uuid_bug]].
from app.core.sql_types import GUID as Uuid


class GamificationUnlock(Base):
    __tablename__ = "gamification_unlocks"
    __table_args__ = (
        UniqueConstraint("person_id", "badge_code", name="uq_gamif_unlock_person_badge"),
        Index("ix_gamif_unlock_person", "person_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    person_id: Mapped[uuid.UUID] = mapped_column(Uuid, nullable=False)
    badge_code: Mapped[str] = mapped_column(String(64), nullable=False)
    unlocked_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )


__all__ = ["GamificationUnlock"]