from __future__ import annotations

from datetime import date, datetime
from typing import Optional

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

from .database import Base


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(String(80), unique=True, index=True)
    full_name: Mapped[str] = mapped_column(String(160))
    role: Mapped[str] = mapped_column(String(30), index=True)  # admin, director, evaluacion, docente
    password_hash: Mapped[str] = mapped_column(String(255))
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    must_change_password: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    assignments: Mapped[list[TeacherAssignment]] = relationship(back_populates="teacher")


class Section(Base):
    __tablename__ = "sections"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30), unique=True, index=True)
    grade_level: Mapped[str] = mapped_column(String(20))
    letter: Mapped[str] = mapped_column(String(5))
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)

    students: Mapped[list[Student]] = relationship(back_populates="section", cascade="all, delete-orphan")
    assignments: Mapped[list[TeacherAssignment]] = relationship(back_populates="section")
    evaluations: Mapped[list[Evaluation]] = relationship(back_populates="section")
    school_year_links: Mapped[list[SchoolYearSection]] = relationship(back_populates="section", cascade="all, delete-orphan")
    enrollments: Mapped[list[StudentEnrollment]] = relationship(back_populates="section")


class Student(Base):
    __tablename__ = "students"

    id: Mapped[int] = mapped_column(primary_key=True)
    ci: Mapped[str] = mapped_column(String(20), unique=True, index=True)
    full_name: Mapped[str] = mapped_column(String(180), index=True)
    sex: Mapped[Optional[str]] = mapped_column(String(10), nullable=True)
    list_number: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    section_id: Mapped[int] = mapped_column(ForeignKey("sections.id"), index=True)

    section: Mapped[Section] = relationship(back_populates="students")
    grades: Mapped[list[Grade]] = relationship(back_populates="student", cascade="all, delete-orphan")
    enrollments: Mapped[list[StudentEnrollment]] = relationship(back_populates="student", cascade="all, delete-orphan")


class Subject(Base):
    __tablename__ = "subjects"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(120), unique=True, index=True)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)

    assignments: Mapped[list[TeacherAssignment]] = relationship(back_populates="subject")
    evaluations: Mapped[list[Evaluation]] = relationship(back_populates="subject")


class TeacherAssignment(Base):
    __tablename__ = "teacher_assignments"
    __table_args__ = (UniqueConstraint("teacher_id", "section_id", "subject_id", name="uq_assignment"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    teacher_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)
    section_id: Mapped[int] = mapped_column(ForeignKey("sections.id"), index=True)
    subject_id: Mapped[int] = mapped_column(ForeignKey("subjects.id"), index=True)

    teacher: Mapped[User] = relationship(back_populates="assignments")
    section: Mapped[Section] = relationship(back_populates="assignments")
    subject: Mapped[Subject] = relationship(back_populates="assignments")


class SchoolYear(Base):
    __tablename__ = "school_years"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(20), unique=True, index=True)
    start_date: Mapped[date] = mapped_column(Date)
    end_date: Mapped[date] = mapped_column(Date)
    status: Mapped[str] = mapped_column(String(20), default="planning", index=True)
    is_active: Mapped[bool] = mapped_column(Boolean, default=False, index=True)
    is_closed: Mapped[bool] = mapped_column(Boolean, default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    section_links: Mapped[list[SchoolYearSection]] = relationship(back_populates="school_year", cascade="all, delete-orphan")
    enrollments: Mapped[list[StudentEnrollment]] = relationship(back_populates="school_year", cascade="all, delete-orphan")
    evaluations: Mapped[list[Evaluation]] = relationship(back_populates="school_year")


class SchoolYearSection(Base):
    __tablename__ = "school_year_sections"
    __table_args__ = (UniqueConstraint("school_year_id", "section_id", name="uq_school_year_section"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    school_year_id: Mapped[int] = mapped_column(ForeignKey("school_years.id"), index=True)
    section_id: Mapped[int] = mapped_column(ForeignKey("sections.id"), index=True)
    display_name: Mapped[str] = mapped_column(String(40))
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    school_year: Mapped[SchoolYear] = relationship(back_populates="section_links")
    section: Mapped[Section] = relationship(back_populates="school_year_links")


class StudentEnrollment(Base):
    __tablename__ = "student_enrollments"
    __table_args__ = (UniqueConstraint("student_id", "school_year_id", name="uq_student_school_year"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    student_id: Mapped[int] = mapped_column(ForeignKey("students.id"), index=True)
    school_year_id: Mapped[int] = mapped_column(ForeignKey("school_years.id"), index=True)
    section_id: Mapped[int] = mapped_column(ForeignKey("sections.id"), index=True)
    status: Mapped[str] = mapped_column(String(20), default="enrolled", index=True)
    enrolled_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    exit_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True)
    notes: Mapped[str] = mapped_column(Text, default="")

    student: Mapped[Student] = relationship(back_populates="enrollments")
    school_year: Mapped[SchoolYear] = relationship(back_populates="enrollments")
    section: Mapped[Section] = relationship(back_populates="enrollments")


class PromotionBatch(Base):
    __tablename__ = "promotion_batches"

    id: Mapped[int] = mapped_column(primary_key=True)
    source_school_year_id: Mapped[int] = mapped_column(ForeignKey("school_years.id"), index=True)
    target_school_year_id: Mapped[int] = mapped_column(ForeignKey("school_years.id"), index=True)
    source_section_id: Mapped[Optional[int]] = mapped_column(ForeignKey("sections.id"), nullable=True)
    target_section_id: Mapped[Optional[int]] = mapped_column(ForeignKey("sections.id"), nullable=True)
    result: Mapped[str] = mapped_column(String(20), default="promoted")
    notes: Mapped[str] = mapped_column(Text, default="")
    created_by_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, index=True)

    source_school_year: Mapped[SchoolYear] = relationship(foreign_keys=[source_school_year_id])
    target_school_year: Mapped[SchoolYear] = relationship(foreign_keys=[target_school_year_id])
    source_section: Mapped[Optional[Section]] = relationship(foreign_keys=[source_section_id])
    target_section: Mapped[Optional[Section]] = relationship(foreign_keys=[target_section_id])
    created_by: Mapped[User] = relationship(foreign_keys=[created_by_id])
    records: Mapped[list[PromotionRecord]] = relationship(back_populates="batch", cascade="all, delete-orphan")


class PromotionRecord(Base):
    __tablename__ = "promotion_records"
    __table_args__ = (UniqueConstraint("batch_id", "student_id", name="uq_promotion_batch_student"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    batch_id: Mapped[int] = mapped_column(ForeignKey("promotion_batches.id"), index=True)
    student_id: Mapped[int] = mapped_column(ForeignKey("students.id"), index=True)
    source_enrollment_id: Mapped[Optional[int]] = mapped_column(ForeignKey("student_enrollments.id"), nullable=True)
    target_enrollment_id: Mapped[Optional[int]] = mapped_column(ForeignKey("student_enrollments.id"), nullable=True)
    source_section_id: Mapped[Optional[int]] = mapped_column(ForeignKey("sections.id"), nullable=True)
    target_section_id: Mapped[Optional[int]] = mapped_column(ForeignKey("sections.id"), nullable=True)
    result: Mapped[str] = mapped_column(String(20), index=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    batch: Mapped[PromotionBatch] = relationship(back_populates="records")
    student: Mapped[Student] = relationship()
    source_enrollment: Mapped[Optional[StudentEnrollment]] = relationship(foreign_keys=[source_enrollment_id])
    target_enrollment: Mapped[Optional[StudentEnrollment]] = relationship(foreign_keys=[target_enrollment_id])
    source_section: Mapped[Optional[Section]] = relationship(foreign_keys=[source_section_id])
    target_section: Mapped[Optional[Section]] = relationship(foreign_keys=[target_section_id])


class Evaluation(Base):
    __tablename__ = "evaluations"

    id: Mapped[int] = mapped_column(primary_key=True)
    section_id: Mapped[int] = mapped_column(ForeignKey("sections.id"), index=True)
    subject_id: Mapped[int] = mapped_column(ForeignKey("subjects.id"), index=True)
    teacher_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)
    school_year_id: Mapped[Optional[int]] = mapped_column(ForeignKey("school_years.id"), nullable=True, index=True)
    period: Mapped[str] = mapped_column(String(40), default="I Momento")
    evaluation_number: Mapped[int] = mapped_column(Integer, default=1)
    title: Mapped[str] = mapped_column(String(160))
    technique: Mapped[str] = mapped_column(String(160), default="")
    instrument: Mapped[str] = mapped_column(String(160), default="")
    content: Mapped[str] = mapped_column(Text, default="")
    competencies: Mapped[str] = mapped_column(Text, default="")
    evaluation_date: Mapped[date] = mapped_column(Date, default=date.today)
    max_score: Mapped[float] = mapped_column(Float, default=20)
    passing_score: Mapped[float] = mapped_column(Float, default=10)
    status: Mapped[str] = mapped_column(String(20), default="draft")  # draft, published
    published_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    is_article112: Mapped[bool] = mapped_column(Boolean, default=False)
    article112_triggered: Mapped[bool] = mapped_column(Boolean, default=False)
    article112_percentage: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    article112_deadline: Mapped[Optional[date]] = mapped_column(Date, nullable=True)
    parent_evaluation_id: Mapped[Optional[int]] = mapped_column(ForeignKey("evaluations.id"), nullable=True)
    approved_by_id: Mapped[Optional[int]] = mapped_column(ForeignKey("users.id"), nullable=True)
    approved_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)

    section: Mapped[Section] = relationship(back_populates="evaluations")
    subject: Mapped[Subject] = relationship(back_populates="evaluations")
    teacher: Mapped[User] = relationship(foreign_keys=[teacher_id])
    approved_by: Mapped[Optional[User]] = relationship(foreign_keys=[approved_by_id])
    school_year: Mapped[Optional[SchoolYear]] = relationship(back_populates="evaluations")
    parent: Mapped[Optional[Evaluation]] = relationship(remote_side=[id], foreign_keys=[parent_evaluation_id])
    grades: Mapped[list[Grade]] = relationship(back_populates="evaluation", cascade="all, delete-orphan")
    eligibilities: Mapped[list[Article112Eligibility]] = relationship(
        back_populates="evaluation", cascade="all, delete-orphan"
    )
    workflow: Mapped[Optional[EvaluationWorkflow]] = relationship(
        back_populates="evaluation", uselist=False, cascade="all, delete-orphan"
    )


class Grade(Base):
    __tablename__ = "grades"
    __table_args__ = (UniqueConstraint("evaluation_id", "student_id", name="uq_grade"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    evaluation_id: Mapped[int] = mapped_column(ForeignKey("evaluations.id"), index=True)
    student_id: Mapped[int] = mapped_column(ForeignKey("students.id"), index=True)
    score: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    absent: Mapped[bool] = mapped_column(Boolean, default=False)
    comment: Mapped[str] = mapped_column(String(255), default="")
    updated_by_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    evaluation: Mapped[Evaluation] = relationship(back_populates="grades")
    student: Mapped[Student] = relationship(back_populates="grades")
    updated_by: Mapped[User] = relationship(foreign_keys=[updated_by_id])


class Article112Eligibility(Base):
    __tablename__ = "article112_eligibilities"
    __table_args__ = (UniqueConstraint("evaluation_id", "student_id", name="uq_eligibility"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    evaluation_id: Mapped[int] = mapped_column(ForeignKey("evaluations.id"), index=True)
    student_id: Mapped[int] = mapped_column(ForeignKey("students.id"), index=True)
    original_score: Mapped[Optional[float]] = mapped_column(Float, nullable=True)

    evaluation: Mapped[Evaluation] = relationship(back_populates="eligibilities")
    student: Mapped[Student] = relationship()


class AcademicPeriodWindow(Base):
    """Institutional deadlines for planning and evaluation configuration."""

    __tablename__ = "academic_period_windows"
    __table_args__ = (UniqueConstraint("school_year", "name", name="uq_period_school_year"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    school_year: Mapped[str] = mapped_column(String(20), index=True)
    name: Mapped[str] = mapped_column(String(40), index=True)
    planning_start: Mapped[date] = mapped_column(Date)
    planning_end: Mapped[date] = mapped_column(Date)
    evaluation_config_start: Mapped[date] = mapped_column(Date)
    evaluation_config_end: Mapped[date] = mapped_column(Date)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


class PeriodPlanning(Base):
    """Locked planning submitted by a teacher for an assignment and period."""

    __tablename__ = "period_plannings"
    __table_args__ = (
        UniqueConstraint("teacher_id", "section_id", "subject_id", "period", "school_year_id", name="uq_period_planning"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    teacher_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)
    section_id: Mapped[int] = mapped_column(ForeignKey("sections.id"), index=True)
    subject_id: Mapped[int] = mapped_column(ForeignKey("subjects.id"), index=True)
    school_year_id: Mapped[Optional[int]] = mapped_column(ForeignKey("school_years.id"), nullable=True, index=True)
    period: Mapped[str] = mapped_column(String(40), index=True)
    purpose: Mapped[str] = mapped_column(Text, default="")
    contents: Mapped[str] = mapped_column(Text, default="")
    competencies: Mapped[str] = mapped_column(Text, default="")
    strategies: Mapped[str] = mapped_column(Text, default="")
    evaluation_schedule: Mapped[str] = mapped_column(Text, default="")
    submitted_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    locked_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    teacher: Mapped[User] = relationship(foreign_keys=[teacher_id])
    section: Mapped[Section] = relationship()
    subject: Mapped[Subject] = relationship()
    school_year: Mapped[Optional[SchoolYear]] = relationship()


class EvaluationWorkflow(Base):
    """Operational controls without altering the historical evaluations table."""

    __tablename__ = "evaluation_workflows"

    id: Mapped[int] = mapped_column(primary_key=True)
    evaluation_id: Mapped[int] = mapped_column(ForeignKey("evaluations.id"), unique=True, index=True)
    configured_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    config_locked_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    conducted_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    grades_due_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    extended_due_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    completed_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)

    evaluation: Mapped[Evaluation] = relationship(back_populates="workflow")


class CoordinationRequest(Base):
    """Written request/acta workflow for exceptions and locked modifications."""

    __tablename__ = "coordination_requests"

    id: Mapped[int] = mapped_column(primary_key=True)
    requester_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)
    request_type: Mapped[str] = mapped_column(String(50), index=True)
    target_type: Mapped[str] = mapped_column(String(40), index=True)
    target_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    section_id: Mapped[Optional[int]] = mapped_column(ForeignKey("sections.id"), nullable=True, index=True)
    subject_id: Mapped[Optional[int]] = mapped_column(ForeignKey("subjects.id"), nullable=True, index=True)
    period: Mapped[str] = mapped_column(String(40), default="")
    act_number: Mapped[str] = mapped_column(String(80), default="")
    written_request: Mapped[str] = mapped_column(Text)
    reason: Mapped[str] = mapped_column(Text, default="")
    proposed_changes: Mapped[str] = mapped_column(Text, default="")
    requested_new_date: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    status: Mapped[str] = mapped_column(String(20), default="pending", index=True)
    resolution: Mapped[str] = mapped_column(Text, default="")
    resolved_by_id: Mapped[Optional[int]] = mapped_column(ForeignKey("users.id"), nullable=True)
    resolved_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    consumed_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, index=True)

    requester: Mapped[User] = relationship(foreign_keys=[requester_id])
    resolved_by: Mapped[Optional[User]] = relationship(foreign_keys=[resolved_by_id])
    section: Mapped[Optional[Section]] = relationship(foreign_keys=[section_id])
    subject: Mapped[Optional[Subject]] = relationship(foreign_keys=[subject_id])


class Article112Act(Base):
    __tablename__ = "article112_acts"

    id: Mapped[int] = mapped_column(primary_key=True)
    evaluation_id: Mapped[int] = mapped_column(ForeignKey("evaluations.id"), unique=True, index=True)
    act_number: Mapped[str] = mapped_column(String(80), unique=True, index=True)
    addressee: Mapped[str] = mapped_column(String(180), default="Coordinación de Evaluación")
    observations: Mapped[str] = mapped_column(Text, default="")
    issued_by_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    issued_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, index=True)

    evaluation: Mapped[Evaluation] = relationship(foreign_keys=[evaluation_id])
    issued_by: Mapped[User] = relationship(foreign_keys=[issued_by_id])


class AuditLog(Base):
    __tablename__ = "audit_logs"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[Optional[int]] = mapped_column(ForeignKey("users.id"), nullable=True, index=True)
    action: Mapped[str] = mapped_column(String(100), index=True)
    entity_type: Mapped[str] = mapped_column(String(80), default="")
    entity_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    details: Mapped[str] = mapped_column(Text, default="")
    ip_address: Mapped[str] = mapped_column(String(64), default="")
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, index=True)

    user: Mapped[Optional[User]] = relationship()