from sqlalchemy import (
    Column, Integer, String, Boolean, DateTime, Enum, Text, JSON,
    ForeignKey, DECIMAL, func
)
from sqlalchemy.orm import relationship
from app.core.database import Base


class PlayerProgress(Base):
    __tablename__ = "player_progress"

    id = Column(Integer, primary_key=True)
    student_user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    topic_level_id = Column(Integer, ForeignKey("topic_levels.id", ondelete="CASCADE"), nullable=False)
    attempts = Column(Integer, default=0)
    best_score = Column(Integer, default=0)
    best_stars = Column(Integer, default=0)
    is_completed = Column(Boolean, default=False)
    completed_at = Column(DateTime, nullable=True)
    total_time_seconds = Column(Integer, default=0)
    last_played_at = Column(DateTime, nullable=True)

    student_user = relationship("StudentProfile", primaryjoin="foreign(PlayerProgress.student_user_id) == StudentProfile.user_id", viewonly=True)
    topic_level = relationship("TopicLevel", back_populates="player_progress")


class Award(Base):
    __tablename__ = "awards"

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    slug = Column(String(255), unique=True, nullable=False)
    type = Column(Enum("badge", "trophy", "certificate", "star", "medal", "ribbon"), nullable=False)
    category = Column(
        Enum("academic", "performance", "consistency", "social", "milestone", "special"),
        default="academic"
    )
    description = Column(Text, nullable=True)
    image_url = Column(Text, nullable=True)
    criteria = Column(JSON, nullable=True)
    xp_value = Column(Integer, default=0)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, server_default=func.now())

    player_awards = relationship("PlayerAward", back_populates="award")


class PlayerAward(Base):
    __tablename__ = "player_awards"

    id = Column(Integer, primary_key=True)
    student_user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    award_id = Column(Integer, ForeignKey("awards.id"), nullable=False)
    session_id = Column(Integer, ForeignKey("game_sessions.id"), nullable=True)
    earned_at = Column(DateTime, server_default=func.now())

    student_user = relationship("StudentProfile", primaryjoin="foreign(PlayerAward.student_user_id) == StudentProfile.user_id", viewonly=True)
    award = relationship("Award", back_populates="player_awards")
    session = relationship("GameSession", back_populates="awards_given")


class AcademicProgressSummary(Base):
    __tablename__ = "academic_progress_summaries"

    id = Column(Integer, primary_key=True)
    student_user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    subject_id = Column(Integer, ForeignKey("subjects.id"), nullable=False)
    grade_id = Column(Integer, ForeignKey("grades.id"), nullable=False)
    topics_attempted = Column(Integer, default=0)
    topics_completed = Column(Integer, default=0)
    levels_completed = Column(Integer, default=0)
    total_score = Column(Integer, default=0)
    average_score = Column(DECIMAL(5, 2), default=0.00)
    total_xp = Column(Integer, default=0)
    total_time_seconds = Column(Integer, default=0)
    last_activity_at = Column(DateTime, nullable=True)
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    student_user = relationship("StudentProfile", primaryjoin="foreign(AcademicProgressSummary.student_user_id) == StudentProfile.user_id", viewonly=True)
    subject = relationship("Subject", back_populates="academic_summaries")
    grade = relationship("Grade", back_populates="academic_summaries")


class OfflineDownload(Base):
    __tablename__ = "offline_downloads"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    topic_id = Column(Integer, ForeignKey("topics.id", ondelete="CASCADE"), nullable=False)
    download_status = Column(
        Enum("pending", "downloading", "completed", "failed", "outdated"),
        default="pending"
    )
    downloaded_at = Column(DateTime, nullable=True)
    last_synced_at = Column(DateTime, nullable=True)
    content_version = Column(String(20), default="1.0.0")
    content_hash = Column(String(64), nullable=True)
    size_bytes = Column(Integer, default=0)

    topic = relationship("Topic", back_populates="offline_downloads")


class OfflineSyncQueue(Base):
    __tablename__ = "offline_sync_queue"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    entity_type = Column(Enum("player_progress", "player_response", "session", "award"), nullable=False)
    entity_local_id = Column(String(100), nullable=True)
    action = Column(Enum("create", "update"), nullable=False)
    data = Column(JSON, nullable=False)
    is_synced = Column(Boolean, default=False)
    sync_error = Column(Text, nullable=True)
    synced_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, server_default=func.now())


class AuditLog(Base):
    __tablename__ = "audit_logs"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    action = Column(String(255), nullable=False)
    entity_type = Column(String(100), nullable=True)
    entity_id = Column(Integer, nullable=True)
    old_values = Column(JSON, nullable=True)
    new_values = Column(JSON, nullable=True)
    ip_address = Column(String(45), nullable=True)
    user_agent = Column(Text, nullable=True)
    created_at = Column(DateTime, server_default=func.now())