from datetime import datetime
from typing import Optional

from sqlalchemy import BigInteger, DateTime, Integer, String, UniqueConstraint, func
from sqlalchemy.orm import Mapped, mapped_column

from app.db.base import Base


class XpEvent(Base):
    """Immutable XP ledger. users.points / week_xp are only a fast cache of this table.
    UNIQUE(user_id, kind, ref) makes every rewarded action idempotent (no double pay)."""

    __tablename__ = "xp_events"
    __table_args__ = (UniqueConstraint("user_id", "kind", "ref", name="uq_xp_idem"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(BigInteger, index=True)
    kind: Mapped[str] = mapped_column(String(24))
    amount: Mapped[int] = mapped_column(Integer)
    ref: Mapped[Optional[str]] = mapped_column(String(64))
    week: Mapped[str] = mapped_column(String(8), index=True)
    day: Mapped[str] = mapped_column(String(10))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class SeasonResult(Base):
    """One row per winner per weekly season, plus a 'closed' marker (rank 0) per week."""

    __tablename__ = "season_results"
    __table_args__ = (UniqueConstraint("week", "kind", "rank", name="uq_season_slot"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    week: Mapped[str] = mapped_column(String(8), index=True)
    kind: Mapped[str] = mapped_column(String(8))  # top | raffle | closed
    rank: Mapped[int] = mapped_column(Integer, default=0)
    user_id: Mapped[int] = mapped_column(BigInteger, default=0)
    xp: Mapped[int] = mapped_column(Integer, default=0)
    prize_birr: Mapped[int] = mapped_column(Integer, default=0)
    status: Mapped[str] = mapped_column(String(12), default="info")  # info | pending | paid
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())