from __future__ import annotations

import uuid
from datetime import date, datetime, timezone
from typing import Any

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

from fan_passport.database import Base


def new_uuid() -> str:
    return str(uuid.uuid4())


def utcnow() -> datetime:
    return datetime.now(timezone.utc)


class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=utcnow, nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=utcnow, onupdate=utcnow, nullable=False
    )


class Competition(Base, TimestampMixin):
    __tablename__ = "competitions"

    code: Mapped[str] = mapped_column(String(64), primary_key=True)
    name: Mapped[str] = mapped_column(String(160), nullable=False)
    season: Mapped[str] = mapped_column(String(40), nullable=False)
    governing_body: Mapped[str | None] = mapped_column(String(80), nullable=True)
    starts_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    ends_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)


class Team(Base, TimestampMixin):
    __tablename__ = "teams"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    competition_code: Mapped[str] = mapped_column(
        String(64), ForeignKey("competitions.code"), index=True, nullable=False
    )
    name: Mapped[str] = mapped_column(String(160), nullable=False)
    short_name: Mapped[str | None] = mapped_column(String(60), nullable=True)
    fifa_code: Mapped[str | None] = mapped_column(String(12), nullable=True)
    country_code: Mapped[str | None] = mapped_column(String(3), nullable=True)
    confederation: Mapped[str | None] = mapped_column(String(32), nullable=True)
    group_name: Mapped[str | None] = mapped_column(String(8), index=True, nullable=True)
    active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)

    __table_args__ = (
        UniqueConstraint("competition_code", "fifa_code", name="uq_team_competition_fifa_code"),
    )


class Stadium(Base, TimestampMixin):
    __tablename__ = "stadiums"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    competition_code: Mapped[str] = mapped_column(
        String(64), ForeignKey("competitions.code"), index=True, nullable=False
    )
    name: Mapped[str] = mapped_column(String(180), nullable=False)
    city: Mapped[str] = mapped_column(String(120), nullable=False)
    country: Mapped[str] = mapped_column(String(80), nullable=False)
    capacity: Mapped[int | None] = mapped_column(Integer, nullable=True)
    latitude: Mapped[str | None] = mapped_column(String(40), nullable=True)
    longitude: Mapped[str | None] = mapped_column(String(40), nullable=True)
    active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)


class Sticker(Base, TimestampMixin):
    __tablename__ = "stickers"

    id: Mapped[str] = mapped_column(String(96), primary_key=True)
    competition_code: Mapped[str] = mapped_column(
        String(64), ForeignKey("competitions.code"), index=True, nullable=False
    )
    name: Mapped[str] = mapped_column(String(180), nullable=False)
    rarity: Mapped[str] = mapped_column(String(32), default="common", nullable=False)
    team_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("teams.id"), nullable=True)
    image_url: Mapped[str | None] = mapped_column(String(500), nullable=True)
    active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)


class Match(Base, TimestampMixin):
    __tablename__ = "matches"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    competition_code: Mapped[str] = mapped_column(
        String(64), ForeignKey("competitions.code"), index=True, nullable=False
    )
    fifa_match_no: Mapped[int | None] = mapped_column(Integer, nullable=True)
    stage: Mapped[str] = mapped_column(String(40), default="group", nullable=False)
    group_name: Mapped[str | None] = mapped_column(String(8), index=True, nullable=True)
    home_team_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("teams.id"), nullable=True)
    away_team_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("teams.id"), nullable=True)
    stadium_id: Mapped[str | None] = mapped_column(
        String(64), ForeignKey("stadiums.id"), nullable=True
    )
    kickoff_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    status: Mapped[str] = mapped_column(String(24), default="scheduled", index=True, nullable=False)
    home_score: Mapped[int | None] = mapped_column(Integer, nullable=True)
    away_score: Mapped[int | None] = mapped_column(Integer, nullable=True)
    winner_team_id: Mapped[str | None] = mapped_column(
        String(64), ForeignKey("teams.id"), nullable=True
    )
    is_giant_killing: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)

    __table_args__ = (
        UniqueConstraint("competition_code", "fifa_match_no", name="uq_match_competition_number"),
        Index("ix_matches_competition_stage", "competition_code", "stage"),
    )


class UserProfile(Base, TimestampMixin):
    __tablename__ = "user_profiles"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_uuid)
    external_subject: Mapped[str | None] = mapped_column(String(160), unique=True, nullable=True)
    display_name: Mapped[str] = mapped_column(String(80), nullable=False)
    country_code: Mapped[str | None] = mapped_column(String(3), nullable=True)
    favorite_team_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("teams.id"))
    avatar_url: Mapped[str | None] = mapped_column(String(500), nullable=True)

    points_total: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    collection_points: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    quiz_points: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    prediction_points: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    challenge_points: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    achievement_points: Mapped[int] = mapped_column(Integer, default=0, nullable=False)

    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)


class UserCollectionItem(Base):
    __tablename__ = "user_collection_items"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_uuid)
    user_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("user_profiles.id", ondelete="CASCADE"), index=True, nullable=False
    )
    item_type: Mapped[str] = mapped_column(String(24), nullable=False)
    item_id: Mapped[str] = mapped_column(String(96), nullable=False)
    source: Mapped[str] = mapped_column(String(40), default="manual", nullable=False)
    points_awarded: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    acquired_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=utcnow, nullable=False
    )
    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)

    __table_args__ = (
        UniqueConstraint("user_id", "item_type", "item_id", name="uq_user_collection_item"),
        Index("ix_user_collection_type", "user_id", "item_type"),
    )


class QuizQuestion(Base, TimestampMixin):
    __tablename__ = "quiz_questions"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    competition_code: Mapped[str] = mapped_column(
        String(64), ForeignKey("competitions.code"), index=True, nullable=False
    )
    slug: Mapped[str] = mapped_column(String(140), unique=True, nullable=False)
    prompt: Mapped[str] = mapped_column(Text, nullable=False)
    options_json: Mapped[list[dict[str, Any]]] = mapped_column(JSON, default=list, nullable=False)
    correct_option: Mapped[str] = mapped_column(String(40), nullable=False)
    explanation: Mapped[str | None] = mapped_column(Text, nullable=True)
    difficulty: Mapped[str] = mapped_column(String(24), default="medium", nullable=False)
    scheduled_date: Mapped[date | None] = mapped_column(Date, index=True, nullable=True)
    active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)


class QuizAnswer(Base):
    __tablename__ = "quiz_answers"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_uuid)
    user_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("user_profiles.id", ondelete="CASCADE"), index=True, nullable=False
    )
    question_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("quiz_questions.id"), index=True, nullable=False
    )
    selected_option: Mapped[str] = mapped_column(String(40), nullable=False)
    is_correct: Mapped[bool] = mapped_column(Boolean, nullable=False)
    points_awarded: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    answered_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=utcnow, nullable=False
    )
    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)

    __table_args__ = (
        UniqueConstraint("user_id", "question_id", name="uq_quiz_answer_user_question"),
    )


class UserPrediction(Base):
    __tablename__ = "user_predictions"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_uuid)
    user_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("user_profiles.id", ondelete="CASCADE"), index=True, nullable=False
    )
    match_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("matches.id"), index=True, nullable=False
    )
    predicted_home_score: Mapped[int] = mapped_column(Integer, nullable=False)
    predicted_away_score: Mapped[int] = mapped_column(Integer, nullable=False)
    predicted_winner_team_id: Mapped[str | None] = mapped_column(
        String(64), ForeignKey("teams.id"), nullable=True
    )
    giant_killing_pick: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    submitted_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=utcnow, nullable=False
    )
    locked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    evaluated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    points_awarded: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    score_breakdown_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)

    __table_args__ = (
        UniqueConstraint("user_id", "match_id", name="uq_prediction_user_match"),
    )


class Challenge(Base, TimestampMixin):
    __tablename__ = "challenges"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_uuid)
    competition_code: Mapped[str] = mapped_column(
        String(64), ForeignKey("competitions.code"), index=True, nullable=False
    )
    slug: Mapped[str] = mapped_column(String(140), unique=True, nullable=False)
    title: Mapped[str] = mapped_column(String(180), nullable=False)
    description: Mapped[str] = mapped_column(Text, default="", nullable=False)
    category: Mapped[str] = mapped_column(String(40), index=True, nullable=False)
    points: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    repeatable: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    rules_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)


class UserChallengeProgress(Base):
    __tablename__ = "user_challenge_progress"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_uuid)
    user_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("user_profiles.id", ondelete="CASCADE"), index=True, nullable=False
    )
    challenge_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("challenges.id", ondelete="CASCADE"), index=True, nullable=False
    )
    progress_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    target_count: Mapped[int] = mapped_column(Integer, default=1, nullable=False)
    completed: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    points_awarded: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    state_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=utcnow, onupdate=utcnow, nullable=False
    )

    __table_args__ = (
        UniqueConstraint("user_id", "challenge_id", name="uq_user_challenge_progress"),
    )


class Badge(Base, TimestampMixin):
    __tablename__ = "badges"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_uuid)
    competition_code: Mapped[str] = mapped_column(
        String(64), ForeignKey("competitions.code"), index=True, nullable=False
    )
    slug: Mapped[str] = mapped_column(String(140), unique=True, nullable=False)
    title: Mapped[str] = mapped_column(String(180), nullable=False)
    description: Mapped[str] = mapped_column(Text, default="", nullable=False)
    tier: Mapped[str] = mapped_column(String(40), default="bronze", nullable=False)
    icon: Mapped[str | None] = mapped_column(String(160), nullable=True)
    points: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    criteria_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)


class UserBadge(Base):
    __tablename__ = "user_badges"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_uuid)
    user_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("user_profiles.id", ondelete="CASCADE"), index=True, nullable=False
    )
    badge_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("badges.id", ondelete="CASCADE"), index=True, nullable=False
    )
    unlocked_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=utcnow, nullable=False
    )
    reason_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)

    __table_args__ = (
        UniqueConstraint("user_id", "badge_id", name="uq_user_badge"),
    )


class AchievementEvent(Base):
    __tablename__ = "achievement_events"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_uuid)
    user_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("user_profiles.id", ondelete="CASCADE"), index=True, nullable=False
    )
    event_type: Mapped[str] = mapped_column(String(80), index=True, nullable=False)
    points_delta: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    payload_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=utcnow, index=True, nullable=False
    )


class ModerationReport(Base):
    __tablename__ = "moderation_reports"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_uuid)
    reporter_user_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("user_profiles.id", ondelete="SET NULL"), nullable=True
    )
    target_type: Mapped[str] = mapped_column(String(40), nullable=False)
    target_id: Mapped[str] = mapped_column(String(96), nullable=False)
    reason: Mapped[str] = mapped_column(Text, nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="open", index=True, nullable=False)
    details_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=utcnow, nullable=False
    )
    resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    resolution_note: Mapped[str | None] = mapped_column(Text, nullable=True)


class AdminAuditLog(Base):
    __tablename__ = "admin_audit_logs"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_uuid)
    actor_subject: Mapped[str | None] = mapped_column(String(160), nullable=True)
    action: Mapped[str] = mapped_column(String(80), index=True, nullable=False)
    entity_type: Mapped[str | None] = mapped_column(String(60), nullable=True)
    entity_id: Mapped[str | None] = mapped_column(String(120), nullable=True)
    payload_json: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=utcnow, nullable=False
    )