"""SQLAlchemy models — full data model for SprintQuiz MVP."""
import uuid
from datetime import datetime, timezone
from enum import Enum as PyEnum

from sqlalchemy import (
    JSON,
    Boolean,
    Column,
    DateTime,
    Float,
    ForeignKey,
    Index,
    Integer,
    String,
    Table,
    Text,
    UniqueConstraint,
)
from sqlalchemy import (
    Enum as SAEnum,
)
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import relationship

from app.db.base import Base


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


# ── Mixin ──────────────────────────────────────────────
class TimestampMixin:
    created_at = Column(DateTime(timezone=True), default=utcnow, nullable=False)
    updated_at = Column(DateTime(timezone=True), default=utcnow, onupdate=utcnow, nullable=False)


# ── Users / Roles / Teams / Cohorts ─────────────────────
class RoleModel(Base, TimestampMixin):
    __tablename__ = "roles"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String(50), unique=True, nullable=False)
    description = Column(Text)


class UserModel(Base, TimestampMixin):
    __tablename__ = "users"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    entra_oid = Column(String(128), unique=True, nullable=False, index=True)
    email = Column(String(255), unique=True, nullable=False)
    full_name = Column(String(255), nullable=False)
    display_name = Column(String(100))
    is_active = Column(Boolean, default=True, nullable=False)
    team_id = Column(UUID(as_uuid=True), ForeignKey("teams.id"), nullable=True)
    roles = relationship("RoleModel", secondary="user_roles", backref="users", lazy="selectin")
    cohort_memberships = relationship("CohortMember", back_populates="user")
    attempts = relationship("Attempt", back_populates="learner")
    signoffs_given = relationship(
        "ManagerSignoff", back_populates="manager",
        foreign_keys="[ManagerSignoff.manager_id]",
    )


class TeamModel(Base, TimestampMixin):
    __tablename__ = "teams"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String(255), unique=True, nullable=False)
    description = Column(Text)


class CohortModel(Base, TimestampMixin):
    __tablename__ = "cohorts"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String(255), nullable=False)
    program_id = Column(UUID(as_uuid=True), ForeignKey("programs.id"), nullable=False)
    batch_size = Column(Integer, default=5)
    is_active = Column(Boolean, default=True, nullable=False)
    members = relationship("CohortMember", back_populates="cohort")


class CohortMember(Base):
    __tablename__ = "cohort_members"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    cohort_id = Column(UUID(as_uuid=True), ForeignKey("cohorts.id"), nullable=False)
    joined_at = Column(DateTime(timezone=True), default=utcnow, nullable=False)
    user = relationship("UserModel", back_populates="cohort_memberships")
    cohort = relationship("CohortModel", back_populates="members")
    __table_args__ = (UniqueConstraint("user_id", "cohort_id", name="uq_user_cohort"),)


class ProgramModel(Base, TimestampMixin):
    __tablename__ = "programs"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String(255), nullable=False)
    description = Column(Text)
    is_active = Column(Boolean, default=True, nullable=False)
    modules = relationship("ModuleModel", back_populates="program")


# ── Modules & Rules ─────────────────────────────────────
class ModuleModel(Base, TimestampMixin):
    __tablename__ = "modules"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    program_id = Column(UUID(as_uuid=True), ForeignKey("programs.id"), nullable=False)
    name = Column(String(255), nullable=False)
    description = Column(Text)
    order_index = Column(Integer, nullable=False, default=0)
    is_active = Column(Boolean, default=True, nullable=False)
    program = relationship("ProgramModel", back_populates="modules")
    rules = relationship("ModuleRule", back_populates="module", uselist=False)
    quizzes = relationship("QuizModel", back_populates="module")


class ModuleRule(Base, TimestampMixin):
    __tablename__ = "module_rules"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    module_id = Column(UUID(as_uuid=True), ForeignKey("modules.id"), unique=True, nullable=False)
    pass_threshold = Column(Float, nullable=False, default=70.0)  # percentage
    is_mandatory = Column(Boolean, default=True, nullable=False)
    max_replays = Column(Integer, nullable=True)  # null = unlimited
    module = relationship("ModuleModel", back_populates="rules")


# ── Quizzes / Versions / Questions ──────────────────────
class QuizStatus(str, PyEnum):
    DRAFT = "draft"
    IN_REVIEW = "in_review"
    PUBLISHED = "published"
    ARCHIVED = "archived"


class QuizModel(Base, TimestampMixin):
    __tablename__ = "quizzes"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    module_id = Column(UUID(as_uuid=True), ForeignKey("modules.id"), nullable=False)
    title = Column(String(500), nullable=False)
    description = Column(Text)
    status = Column(SAEnum(QuizStatus, values_callable=lambda x: [e.value for e in x]), default=QuizStatus.DRAFT, nullable=False)
    created_by = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    requires_credit_head = Column(Boolean, default=False, nullable=False)
    module = relationship("ModuleModel", back_populates="quizzes")
    versions = relationship("QuizVersion", back_populates="quiz")
    questions = relationship("Question", back_populates="quiz")
    assignments = relationship("Assignment", back_populates="quiz")


class QuizVersion(Base, TimestampMixin):
    __tablename__ = "quiz_versions"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    quiz_id = Column(UUID(as_uuid=True), ForeignKey("quizzes.id"), nullable=False)
    version_number = Column(Integer, nullable=False)
    snapshot = Column(JSON, nullable=False)  # full question/options snapshot
    published_by = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    quiz = relationship("QuizModel", back_populates="versions")
    __table_args__ = (UniqueConstraint("quiz_id", "version_number", name="uq_quiz_version"),)


class Question(Base, TimestampMixin):
    __tablename__ = "questions"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    quiz_id = Column(UUID(as_uuid=True), ForeignKey("quizzes.id"), nullable=False)
    question_text = Column(Text, nullable=False)
    topic_tag = Column(String(255), nullable=False, default="general")
    explanation = Column(Text)
    confidence_score = Column(Float, default=0.0)
    is_ai_generated = Column(Boolean, default=False)
    source_document_id = Column(UUID(as_uuid=True), ForeignKey("source_documents.id"), nullable=True)
    quiz = relationship("QuizModel", back_populates="questions")
    options = relationship("QuestionOption", back_populates="question", cascade="all, delete-orphan")
    answers = relationship("AttemptAnswer", back_populates="question")


class QuestionOption(Base, TimestampMixin):
    __tablename__ = "question_options"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    question_id = Column(UUID(as_uuid=True), ForeignKey("questions.id"), nullable=False)
    option_text = Column(Text, nullable=False)
    is_correct = Column(Boolean, nullable=False, default=False)
    order_index = Column(Integer, nullable=False, default=0)
    question = relationship("Question", back_populates="options")


# ── Documents / Jobs ────────────────────────────────────
class DocJobStatus(str, PyEnum):
    UPLOADED = "uploaded"
    PARSING = "parsing"
    GENERATING = "generating"
    REVIEW_READY = "review_ready"
    FAILED = "failed"


class SourceDocument(Base, TimestampMixin):
    __tablename__ = "source_documents"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    filename = Column(String(500), nullable=False)
    file_path = Column(String(1000), nullable=False)
    file_type = Column(String(20), nullable=False)  # pdf, docx, pptx
    file_size = Column(Integer, nullable=False)
    uploaded_by = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    extracted_text = Column(Text)
    questions = relationship("Question", backref="source_document")


class DocumentJob(Base, TimestampMixin):
    __tablename__ = "document_jobs"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    document_id = Column(UUID(as_uuid=True), ForeignKey("source_documents.id"), nullable=False)
    status = Column(
        SAEnum(DocJobStatus, name="docjobstatus", values_callable=lambda x: [e.value for e in x]),
        default=DocJobStatus.UPLOADED,
        nullable=False,
    )
    error_message = Column(Text)
    generated_quiz_id = Column(UUID(as_uuid=True), ForeignKey("quizzes.id"), nullable=True)
    document = relationship("SourceDocument")


# ── Assignments / Attempts / Answers ────────────────────
class Assignment(Base, TimestampMixin):
    __tablename__ = "assignments"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    quiz_id = Column(UUID(as_uuid=True), ForeignKey("quizzes.id"), nullable=False)
    cohort_id = Column(UUID(as_uuid=True), ForeignKey("cohorts.id"), nullable=True)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=True)  # single-user assignment
    due_date = Column(DateTime(timezone=True))
    is_active = Column(Boolean, default=True, nullable=False)
    quiz = relationship("QuizModel", back_populates="assignments")
    attempts = relationship("Attempt", back_populates="assignment")


class AttemptStatus(str, PyEnum):
    IN_PROGRESS = "in_progress"
    COMPLETED = "completed"
    ABANDONED = "abandoned"


class Attempt(Base, TimestampMixin):
    __tablename__ = "attempts"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    assignment_id = Column(UUID(as_uuid=True), ForeignKey("assignments.id"), nullable=False)
    learner_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    module_id = Column(UUID(as_uuid=True), ForeignKey("modules.id"), nullable=False)
    status = Column(
        SAEnum(AttemptStatus, name="attemptstatus", values_callable=lambda x: [e.value for e in x]),
        default=AttemptStatus.IN_PROGRESS,
        nullable=False,
    )
    score = Column(Float, default=0.0)  # percentage
    correct_count = Column(Integer, default=0)
    total_questions = Column(Integer, default=0)
    started_at = Column(DateTime(timezone=True), default=utcnow, nullable=False)
    completed_at = Column(DateTime(timezone=True))
    assignment = relationship("Assignment", back_populates="attempts")
    learner = relationship("UserModel", back_populates="attempts")
    answers = relationship("AttemptAnswer", back_populates="attempt", cascade="all, delete-orphan")


class AttemptAnswer(Base):
    __tablename__ = "attempt_answers"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    attempt_id = Column(UUID(as_uuid=True), ForeignKey("attempts.id"), nullable=False)
    question_id = Column(UUID(as_uuid=True), ForeignKey("questions.id"), nullable=False)
    selected_option_id = Column(UUID(as_uuid=True), ForeignKey("question_options.id"), nullable=True)
    is_correct = Column(Boolean, nullable=False, default=False)
    answered_at = Column(DateTime(timezone=True), default=utcnow, nullable=False)
    attempt = relationship("Attempt", back_populates="answers")
    question = relationship("Question", back_populates="answers")


# ── Progress / Weak Topics ──────────────────────────────
class LearnerProgress(Base, TimestampMixin):
    __tablename__ = "learner_progress"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    learner_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    module_id = Column(UUID(as_uuid=True), ForeignKey("modules.id"), nullable=False)
    best_score = Column(Float, default=0.0)
    latest_score = Column(Float, default=0.0)
    attempt_count = Column(Integer, default=0)
    is_passed = Column(Boolean, default=False)
    signed_off = Column(Boolean, default=False)
    __table_args__ = (UniqueConstraint("learner_id", "module_id", name="uq_learner_module"),)


class WeakTopicSnapshot(Base, TimestampMixin):
    __tablename__ = "weak_topic_snapshots"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    learner_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    module_id = Column(UUID(as_uuid=True), ForeignKey("modules.id"), nullable=False)
    topic_tag = Column(String(255), nullable=False)
    total_questions = Column(Integer, default=0)
    correct_questions = Column(Integer, default=0)
    weakness_ratio = Column(Float, default=0.0)  # 1.0 - accuracy


# ── Sign-off / Certificates / Badges ────────────────────
class SignoffStatus(str, PyEnum):
    PENDING = "pending"
    APPROVED = "approved"
    REJECTED = "rejected"


class ManagerSignoff(Base, TimestampMixin):
    __tablename__ = "manager_signoffs"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    learner_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    module_id = Column(UUID(as_uuid=True), ForeignKey("modules.id"), nullable=False)
    manager_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    status = Column(
        SAEnum(SignoffStatus, name="signoffstatus", values_callable=lambda x: [e.value for e in x]),
        default=SignoffStatus.PENDING,
        nullable=False,
    )
    comments = Column(Text)
    manager = relationship("UserModel", back_populates="signoffs_given", foreign_keys="[ManagerSignoff.manager_id]")
    __table_args__ = (UniqueConstraint("learner_id", "module_id", name="uq_signoff_learner_module"),)


class Certificate(Base, TimestampMixin):
    __tablename__ = "certificates"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    learner_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    module_id = Column(UUID(as_uuid=True), ForeignKey("modules.id"), nullable=False)
    certificate_number = Column(String(100), unique=True, nullable=False)
    issued_at = Column(DateTime(timezone=True), default=utcnow, nullable=False)


class Badge(Base, TimestampMixin):
    __tablename__ = "badges"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    learner_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    badge_type = Column(String(100), nullable=False)  # streak, mastery, improvement, shoutout
    metadata_ = Column("metadata", JSON, default=dict)


class LeaderboardSnapshot(Base, TimestampMixin):
    __tablename__ = "leaderboard_snapshots"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    week_key = Column(String(20), nullable=False)  # ISO week e.g. "2026-W32"
    rank = Column(Integer, nullable=False)
    score = Column(Float, nullable=False, default=0.0)
    streak_count = Column(Integer, default=0)
    mastery_points = Column(Float, default=0.0)
    improvement_points = Column(Float, default=0.0)
    consistency_points = Column(Float, default=0.0)
    __table_args__ = (
        Index("ix_leaderboard_week", "week_key"),
        UniqueConstraint("user_id", "week_key", name="uq_user_week"),
    )


# ── Audit ───────────────────────────────────────────────
class AuditLog(Base, TimestampMixin):
    __tablename__ = "audit_logs"
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    actor_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=True)
    action = Column(String(100), nullable=False)  # upload, generate, publish, assign, signoff, admin
    entity_type = Column(String(100), nullable=False)
    entity_id = Column(UUID(as_uuid=True), nullable=True)
    details = Column(JSON, default=dict)
    ip_address = Column(String(45))


# ── User-Role association ────────────────────────────────

user_roles = Table(
    "user_roles",
    Base.metadata,
    Column("user_id", UUID(as_uuid=True), ForeignKey("users.id"), primary_key=True),
    Column("role_id", UUID(as_uuid=True), ForeignKey("roles.id"), primary_key=True),
)