from __future__ import annotations

import re
from datetime import date, datetime

from sqlalchemy.orm import Session, joinedload

from ..config import settings
from ..models import (
    Evaluation,
    PeriodPlanning,
    PromotionBatch,
    PromotionRecord,
    SchoolYear,
    SchoolYearSection,
    Section,
    Student,
    StudentEnrollment,
    User,
)


ENROLLMENT_ACTIVE_STATUSES = ("enrolled", "promoted", "repeater")
PROMOTION_RESULTS = {
    "promoted": "Promovido",
    "repeater": "Repitiente",
    "graduated": "Egresado",
    "withdrawn": "Retirado",
}


def parse_school_year(name: str) -> tuple[int, int]:
    match = re.fullmatch(r"\s*(\d{4})\s*[-/]\s*(\d{4})\s*", name or "")
    if not match:
        raise ValueError("El año escolar debe tener el formato 2026-2027")
    start_year, end_year = int(match.group(1)), int(match.group(2))
    if end_year != start_year + 1:
        raise ValueError("El segundo año debe ser consecutivo al primero")
    return start_year, end_year


def default_school_year_dates(name: str) -> tuple[date, date]:
    start_year, end_year = parse_school_year(name)
    return date(start_year, 9, 1), date(end_year, 7, 31)


def get_active_school_year(db: Session) -> SchoolYear:
    year = db.query(SchoolYear).filter(SchoolYear.is_active.is_(True)).order_by(SchoolYear.id.desc()).first()
    if year:
        return year
    return ensure_default_school_year(db)


def ensure_default_school_year(db: Session) -> SchoolYear:
    year = db.query(SchoolYear).filter(SchoolYear.name == settings.school_year).first()
    if not year:
        start_date, end_date = default_school_year_dates(settings.school_year)
        year = SchoolYear(
            name=settings.school_year,
            start_date=start_date,
            end_date=end_date,
            status="active",
            is_active=True,
        )
        db.add(year)
        db.flush()
    if not db.query(SchoolYear).filter(SchoolYear.is_active.is_(True)).first():
        year.is_active = True
        year.status = "active"
    db.flush()
    sync_school_year_structure(db, year, include_students=True)
    backfill_legacy_year_references(db, year)
    db.commit()
    return year


def backfill_legacy_year_references(db: Session, school_year: SchoolYear) -> None:
    db.query(Evaluation).filter(Evaluation.school_year_id.is_(None)).update(
        {Evaluation.school_year_id: school_year.id}, synchronize_session=False
    )
    db.query(PeriodPlanning).filter(PeriodPlanning.school_year_id.is_(None)).update(
        {PeriodPlanning.school_year_id: school_year.id}, synchronize_session=False
    )


def sync_school_year_structure(db: Session, school_year: SchoolYear, *, include_students: bool = False) -> None:
    sections = db.query(Section).order_by(Section.name).all()
    existing = {
        link.section_id: link
        for link in db.query(SchoolYearSection).filter(SchoolYearSection.school_year_id == school_year.id).all()
    }
    for section in sections:
        link = existing.get(section.id)
        if not link:
            db.add(
                SchoolYearSection(
                    school_year_id=school_year.id,
                    section_id=section.id,
                    display_name=section.name,
                    is_active=section.is_active,
                )
            )
    db.flush()

    if include_students:
        enrollment_student_ids = {
            row[0]
            for row in db.query(StudentEnrollment.student_id)
            .filter(StudentEnrollment.school_year_id == school_year.id)
            .all()
        }
        students = db.query(Student).filter(Student.is_active.is_(True)).all()
        for student in students:
            if student.id not in enrollment_student_ids:
                db.add(
                    StudentEnrollment(
                        student_id=student.id,
                        school_year_id=school_year.id,
                        section_id=student.section_id,
                        status="enrolled",
                    )
                )
    db.flush()


def create_school_year(
    db: Session,
    *,
    name: str,
    start_date: date,
    end_date: date,
    copy_sections_from: SchoolYear | None = None,
) -> SchoolYear:
    normalized = name.strip().replace("/", "-")
    parse_school_year(normalized)
    if start_date >= end_date:
        raise ValueError("La fecha de inicio debe ser anterior a la fecha de cierre")
    if db.query(SchoolYear).filter(SchoolYear.name == normalized).first():
        raise ValueError("Ese año escolar ya existe")
    year = SchoolYear(
        name=normalized,
        start_date=start_date,
        end_date=end_date,
        status="planning",
        is_active=False,
    )
    db.add(year)
    db.flush()

    if copy_sections_from:
        source_links = (
            db.query(SchoolYearSection)
            .filter(
                SchoolYearSection.school_year_id == copy_sections_from.id,
                SchoolYearSection.is_active.is_(True),
            )
            .all()
        )
        for link in source_links:
            db.add(
                SchoolYearSection(
                    school_year_id=year.id,
                    section_id=link.section_id,
                    display_name=link.display_name,
                    is_active=True,
                )
            )
    else:
        sync_school_year_structure(db, year, include_students=False)
    db.flush()
    return year


def activate_school_year(db: Session, school_year: SchoolYear) -> None:
    if school_year.is_closed:
        raise ValueError("Un año escolar cerrado no puede activarse")
    db.query(SchoolYear).update({SchoolYear.is_active: False}, synchronize_session=False)
    db.query(SchoolYear).filter(SchoolYear.status == "active").update(
        {SchoolYear.status: "archived"}, synchronize_session=False
    )
    school_year.is_active = True
    school_year.status = "active"
    sync_school_year_structure(db, school_year, include_students=False)


def close_school_year(db: Session, school_year: SchoolYear) -> None:
    school_year.is_closed = True
    school_year.status = "closed"
    school_year.is_active = False


def sections_for_year(db: Session, school_year_id: int, *, only_active: bool = True) -> list[Section]:
    query = (
        db.query(Section)
        .join(SchoolYearSection, SchoolYearSection.section_id == Section.id)
        .filter(SchoolYearSection.school_year_id == school_year_id)
    )
    if only_active:
        query = query.filter(SchoolYearSection.is_active.is_(True), Section.is_active.is_(True))
    return query.order_by(Section.grade_level, Section.letter, Section.name).all()


def section_counts_for_year(db: Session, school_year_id: int) -> list[tuple[Section, int]]:
    sections = sections_for_year(db, school_year_id, only_active=False)
    from collections import Counter

    counter = Counter(
        section_id
        for (section_id,) in db.query(StudentEnrollment.section_id)
        .filter(
            StudentEnrollment.school_year_id == school_year_id,
            StudentEnrollment.status.in_(ENROLLMENT_ACTIVE_STATUSES),
        )
        .all()
    )
    return [(section, counter.get(section.id, 0)) for section in sections]


def students_for_section_year(
    db: Session,
    section_id: int,
    school_year_id: int,
    *,
    include_inactive: bool = False,
) -> list[Student]:
    query = (
        db.query(Student)
        .join(StudentEnrollment, StudentEnrollment.student_id == Student.id)
        .filter(
            StudentEnrollment.section_id == section_id,
            StudentEnrollment.school_year_id == school_year_id,
        )
    )
    if not include_inactive:
        query = query.filter(StudentEnrollment.status.in_(ENROLLMENT_ACTIVE_STATUSES))
    return query.order_by(Student.list_number, Student.full_name).all()


def promotion_source_enrollments(db: Session, school_year_id: int, section_id: int) -> list[StudentEnrollment]:
    return (
        db.query(StudentEnrollment)
        .options(joinedload(StudentEnrollment.student))
        .filter(
            StudentEnrollment.school_year_id == school_year_id,
            StudentEnrollment.section_id == section_id,
            StudentEnrollment.status == "enrolled",
        )
        .order_by(StudentEnrollment.id)
        .all()
    )


def promote_students(
    db: Session,
    *,
    actor: User,
    source_year: SchoolYear,
    target_year: SchoolYear,
    source_section: Section,
    target_section: Section | None,
    student_ids: list[int],
    result: str,
    notes: str = "",
) -> PromotionBatch:
    if source_year.id == target_year.id:
        raise ValueError("El año de origen y el año de destino deben ser distintos")
    if target_year.start_date <= source_year.start_date:
        raise ValueError("El año de destino debe ser posterior al año de origen")
    if target_year.is_closed:
        raise ValueError("No se puede promover matrícula hacia un año escolar cerrado")
    if result not in PROMOTION_RESULTS:
        raise ValueError("Resultado de promoción inválido")
    if result in {"promoted", "repeater"} and not target_section:
        raise ValueError("Debe seleccionar una sección de destino")
    if not student_ids:
        raise ValueError("Debe seleccionar al menos un estudiante")

    source_enrollments = {
        item.student_id: item
        for item in promotion_source_enrollments(db, source_year.id, source_section.id)
        if item.student_id in set(student_ids)
    }
    if not source_enrollments:
        raise ValueError("No se encontraron estudiantes habilitados en la sección de origen")

    if target_section:
        link = (
            db.query(SchoolYearSection)
            .filter_by(school_year_id=target_year.id, section_id=target_section.id)
            .first()
        )
        if not link:
            db.add(
                SchoolYearSection(
                    school_year_id=target_year.id,
                    section_id=target_section.id,
                    display_name=target_section.name,
                    is_active=True,
                )
            )
            db.flush()

    batch = PromotionBatch(
        source_school_year_id=source_year.id,
        target_school_year_id=target_year.id,
        source_section_id=source_section.id,
        target_section_id=target_section.id if target_section else None,
        result=result,
        notes=notes.strip(),
        created_by_id=actor.id,
    )
    db.add(batch)
    db.flush()

    for student_id, source_enrollment in source_enrollments.items():
        student = db.get(Student, student_id)
        target_enrollment = None
        if result in {"promoted", "repeater"} and target_section:
            target_enrollment = (
                db.query(StudentEnrollment)
                .filter_by(student_id=student_id, school_year_id=target_year.id)
                .first()
            )
            if not target_enrollment:
                target_enrollment = StudentEnrollment(
                    student_id=student_id,
                    school_year_id=target_year.id,
                    section_id=target_section.id,
                    status="enrolled",
                    notes=notes.strip(),
                )
                db.add(target_enrollment)
                db.flush()
            else:
                target_enrollment.section_id = target_section.id
                target_enrollment.status = "enrolled"
                target_enrollment.notes = notes.strip()
            if student:
                student.section_id = target_section.id
                student.is_active = True
        else:
            if student:
                student.is_active = False

        source_enrollment.status = result
        if result in {"graduated", "withdrawn"}:
            source_enrollment.exit_date = date.today()

        db.add(
            PromotionRecord(
                batch_id=batch.id,
                student_id=student_id,
                source_enrollment_id=source_enrollment.id,
                target_enrollment_id=target_enrollment.id if target_enrollment else None,
                source_section_id=source_section.id,
                target_section_id=target_section.id if target_section else None,
                result=result,
            )
        )
    db.flush()
    return batch