from __future__ import annotations

import csv
import io
from datetime import date, datetime, timedelta
from pathlib import Path
from urllib.parse import quote

from fastapi import Depends, FastAPI, Form, HTTPException, Request, status
from fastapi.responses import HTMLResponse, RedirectResponse, StreamingResponse
from fastapi.staticfiles import StaticFiles
from fastapi.templating import Jinja2Templates
from sqlalchemy import func
from sqlalchemy.orm import Session, joinedload
from starlette.middleware.sessions import SessionMiddleware

from .auth import can_manage_evaluation, current_user, require_roles, require_user
from .config import settings
from .database import Base, SessionLocal, engine, get_db
from .schema import ensure_legacy_columns
from .models import (
    AcademicPeriodWindow,
    Article112Act,
    Article112Eligibility,
    AuditLog,
    CoordinationRequest,
    Evaluation,
    EvaluationWorkflow,
    Grade,
    PeriodPlanning,
    PromotionBatch,
    SchoolYear,
    SchoolYearSection,
    Section,
    Student,
    StudentEnrollment,
    Subject,
    TeacherAssignment,
    User,
)
from .security import (
    clear_session,
    generate_temporary_password,
    get_or_create_csrf,
    hash_password,
    set_login_session,
    validate_csrf,
    verify_password,
)
from .services.article112 import calculate_article112, create_article112_evaluation, definitive_score
from .services.article112_act import get_or_create_article112_act, original_article112_evaluation
from .services.academic import (
    PROMOTION_RESULTS,
    activate_school_year,
    close_school_year,
    create_school_year,
    get_active_school_year,
    promote_students,
    promotion_source_enrollments,
    section_counts_for_year,
    sections_for_year,
    students_for_section_year,
    sync_school_year_structure,
)
from .services.audit import audit
from .services.reports import ANNUAL_PERIODS, build_grade_report
from .services.seed import seed_database
from .services.workflow import (
    PERIOD_NAMES,
    REQUEST_LABELS,
    approved_request,
    effective_grade_deadline,
    evaluation_config_window_open,
    find_assignment,
    get_or_create_workflow,
    grade_entry_state,
    institution_now,
    mark_conducted,
    pending_article112,
    period_window,
    planning_for_assignment,
    planning_window_open,
    seed_period_windows,
)


BASE_DIR = Path(__file__).resolve().parent
app = FastAPI(title=settings.app_name)
app.add_middleware(
    SessionMiddleware,
    secret_key=settings.secret_key,
    max_age=settings.session_max_age,
    same_site="lax",
    https_only=settings.cookie_secure,
)
app.mount("/static", StaticFiles(directory=BASE_DIR / "static"), name="static")
templates = Jinja2Templates(directory=BASE_DIR / "templates")


@app.middleware("http")
async def security_headers(request: Request, call_next):
    response = await call_next(request)
    response.headers["X-Content-Type-Options"] = "nosniff"
    response.headers["X-Frame-Options"] = "DENY"
    response.headers["Referrer-Policy"] = "strict-origin-when-cross-origin"
    response.headers["Permissions-Policy"] = "camera=(), microphone=(), geolocation=()"
    response.headers["Content-Security-Policy"] = (
        "default-src 'self'; img-src 'self' data:; style-src 'self' 'unsafe-inline'; "
        "script-src 'self'; frame-ancestors 'none'; form-action 'self'"
    )
    return response


@app.on_event("startup")
def startup():
    Base.metadata.create_all(bind=engine)
    ensure_legacy_columns(engine)
    db = SessionLocal()
    try:
        seed_database(db)
    finally:
        db.close()


def render(request: Request, template: str, context: dict, status_code: int = 200):
    db_user = context.get("user")
    context.update(
        {
            "request": request,
            "settings": settings,
            "csrf_token": get_or_create_csrf(request),
            "flash": request.session.pop("flash", None),
            "user": db_user,
        }
    )
    return templates.TemplateResponse(template, context, status_code=status_code)


def redirect(url: str):
    return RedirectResponse(url=url, status_code=status.HTTP_303_SEE_OTHER)


def set_flash(request: Request, message: str, level: str = "success"):
    request.session["flash"] = {"message": message, "level": level}


@app.get("/health")
def health():
    return {"status": "ok", "service": settings.app_name}


@app.get("/login", response_class=HTMLResponse)
def login_page(request: Request, db: Session = Depends(get_db)):
    if current_user(request, db):
        return redirect("/")
    return render(request, "login.html", {"user": None})


@app.post("/login")
def login(
    request: Request,
    username: str = Form(...),
    password: str = Form(...),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = db.query(User).filter(func.lower(User.username) == username.strip().lower()).first()
    if not user or not user.is_active or not verify_password(password, user.password_hash):
        audit(db, request, user, "login_failed", details={"username": username})
        db.commit()
        return render(
            request,
            "login.html",
            {"user": None, "error": "Usuario o contraseña incorrectos."},
            status_code=401,
        )
    set_login_session(request, user.id)
    audit(db, request, user, "login_success")
    db.commit()
    if user.must_change_password:
        return redirect("/cambiar-clave")
    return redirect("/")


@app.post("/logout")
def logout(request: Request, csrf_token: str = Form(...), db: Session = Depends(get_db)):
    validate_csrf(request, csrf_token)
    user = current_user(request, db)
    if user:
        audit(db, request, user, "logout")
        db.commit()
    clear_session(request)
    return redirect("/login")


@app.get("/cambiar-clave", response_class=HTMLResponse)
def change_password_page(request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    return render(request, "change_password.html", {"user": user})


@app.post("/cambiar-clave")
def change_password(
    request: Request,
    current_password: str = Form(...),
    new_password: str = Form(...),
    confirm_password: str = Form(...),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    if not verify_password(current_password, user.password_hash):
        return render(request, "change_password.html", {"user": user, "error": "La clave actual no es correcta."}, 400)
    if len(new_password) < 10 or new_password != confirm_password:
        return render(
            request,
            "change_password.html",
            {"user": user, "error": "La nueva clave debe tener al menos 10 caracteres y coincidir."},
            400,
        )
    user.password_hash = hash_password(new_password)
    user.must_change_password = False
    audit(db, request, user, "password_changed", "user", user.id)
    db.commit()
    set_flash(request, "Contraseña actualizada correctamente.")
    return redirect("/")


@app.get("/", response_class=HTMLResponse)
def dashboard(request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    if user.must_change_password:
        return redirect("/cambiar-clave")

    total_students = db.query(Student).filter(Student.is_active.is_(True)).count()
    total_sections = db.query(Section).filter(Section.is_active.is_(True)).count()
    pending_112 = (
        db.query(Evaluation)
        .filter(Evaluation.is_article112.is_(True), Evaluation.status == "draft")
        .count()
    )
    query = db.query(Evaluation).options(joinedload(Evaluation.section), joinedload(Evaluation.subject))
    workflow_query = (
        db.query(Evaluation)
        .options(joinedload(Evaluation.workflow))
        .join(EvaluationWorkflow, EvaluationWorkflow.evaluation_id == Evaluation.id)
        .filter(Evaluation.status == "draft", EvaluationWorkflow.conducted_at.is_not(None))
    )
    request_query = db.query(CoordinationRequest).filter(CoordinationRequest.status == "pending")
    if user.role == "docente":
        allowed = db.query(TeacherAssignment).filter(TeacherAssignment.teacher_id == user.id).all()
        pairs = {(a.section_id, a.subject_id) for a in allowed}
        evaluations = [e for e in query.order_by(Evaluation.id.desc()).limit(100).all() if (e.section_id, e.subject_id) in pairs][:10]
        assignments = allowed
        workflow_evaluations = workflow_query.filter(Evaluation.teacher_id == user.id).all()
        pending_requests = request_query.filter(CoordinationRequest.requester_id == user.id).count()
    else:
        evaluations = query.order_by(Evaluation.id.desc()).limit(10).all()
        assignments = []
        workflow_evaluations = workflow_query.all()
        pending_requests = request_query.count()
    overdue_uploads = sum(1 for item in workflow_evaluations if grade_entry_state(item, item.workflow)["overdue"])
    return render(
        request,
        "dashboard.html",
        {
            "user": user,
            "total_students": total_students,
            "total_sections": total_sections,
            "pending_112": pending_112,
            "pending_requests": pending_requests,
            "overdue_uploads": overdue_uploads,
            "evaluations": evaluations,
            "assignments": assignments,
        },
    )


@app.get("/usuarios", response_class=HTMLResponse)
def users_page(request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    require_roles(user, "admin")
    users = db.query(User).order_by(User.full_name).all()
    return render(request, "users.html", {"user": user, "users": users})


@app.get("/usuarios/nuevo", response_class=HTMLResponse)
def user_new_page(request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    require_roles(user, "admin")
    sections = db.query(Section).order_by(Section.name).all()
    subjects = db.query(Subject).order_by(Subject.name).all()
    return render(request, "user_new.html", {"user": user, "sections": sections, "subjects": subjects})


@app.post("/usuarios/nuevo")
def user_new(
    request: Request,
    username: str = Form(...),
    full_name: str = Form(...),
    role: str = Form(...),
    section_id: int | None = Form(None),
    subject_id: int | None = Form(None),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    actor = require_user(request, db)
    require_roles(actor, "admin")
    username = username.strip().lower()
    if role not in {"admin", "director", "evaluacion", "docente"}:
        raise HTTPException(400, "Rol inválido")
    if db.query(User).filter(User.username == username).first():
        set_flash(request, "Ese nombre de usuario ya existe.", "danger")
        return redirect("/usuarios/nuevo")
    temporary = generate_temporary_password()
    new_user = User(
        username=username,
        full_name=full_name.strip(),
        role=role,
        password_hash=hash_password(temporary),
        must_change_password=True,
    )
    db.add(new_user)
    db.flush()
    if role == "docente" and section_id and subject_id:
        db.add(TeacherAssignment(teacher_id=new_user.id, section_id=section_id, subject_id=subject_id))
    audit(db, request, actor, "user_created", "user", new_user.id, {"username": username, "role": role})
    db.commit()
    set_flash(
        request,
        f"Usuario creado. Clave temporal (se muestra una sola vez): {temporary}",
        "warning",
    )
    return redirect("/usuarios")


@app.get("/usuarios/{user_id}/asignaciones", response_class=HTMLResponse)
def user_assignments_page(user_id: int, request: Request, db: Session = Depends(get_db)):
    actor = require_user(request, db)
    require_roles(actor, "admin")
    target = db.get(User, user_id)
    if not target or target.role != "docente":
        raise HTTPException(404, "Docente no encontrado")
    assignments = (
        db.query(TeacherAssignment)
        .options(joinedload(TeacherAssignment.section), joinedload(TeacherAssignment.subject))
        .filter(TeacherAssignment.teacher_id == target.id)
        .order_by(TeacherAssignment.id)
        .all()
    )
    sections = db.query(Section).filter(Section.is_active.is_(True)).order_by(Section.name).all()
    subjects = db.query(Subject).filter(Subject.is_active.is_(True)).order_by(Subject.name).all()
    return render(request, "user_assignments.html", {"user": actor, "target": target, "assignments": assignments, "sections": sections, "subjects": subjects})


@app.post("/usuarios/{user_id}/asignaciones")
def user_assignment_add(
    user_id: int,
    request: Request,
    section_id: int = Form(...),
    subject_id: int = Form(...),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    actor = require_user(request, db)
    require_roles(actor, "admin")
    target = db.get(User, user_id)
    if not target or target.role != "docente":
        raise HTTPException(404, "Docente no encontrado")
    exists = db.query(TeacherAssignment).filter_by(teacher_id=target.id, section_id=section_id, subject_id=subject_id).first()
    if not exists:
        assignment = TeacherAssignment(teacher_id=target.id, section_id=section_id, subject_id=subject_id)
        db.add(assignment)
        db.flush()
        audit(db, request, actor, "assignment_created", "teacher_assignment", assignment.id, {"teacher_id": target.id, "section_id": section_id, "subject_id": subject_id})
        db.commit()
        set_flash(request, "Asignación agregada.")
    else:
        set_flash(request, "La asignación ya existe.", "warning")
    return redirect(f"/usuarios/{target.id}/asignaciones")


@app.post("/asignaciones/{assignment_id}/eliminar")
def user_assignment_delete(
    assignment_id: int,
    request: Request,
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    actor = require_user(request, db)
    require_roles(actor, "admin")
    assignment = db.get(TeacherAssignment, assignment_id)
    if not assignment:
        raise HTTPException(404, "Asignación no encontrada")
    teacher_id = assignment.teacher_id
    audit(db, request, actor, "assignment_deleted", "teacher_assignment", assignment.id, {"teacher_id": teacher_id})
    db.delete(assignment)
    db.commit()
    set_flash(request, "Asignación eliminada.")
    return redirect(f"/usuarios/{teacher_id}/asignaciones")


@app.post("/usuarios/{user_id}/restablecer-clave")
def reset_user_password(
    user_id: int,
    request: Request,
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    actor = require_user(request, db)
    require_roles(actor, "admin")
    target = db.get(User, user_id)
    if not target:
        raise HTTPException(404, "Usuario no encontrado")
    temporary = generate_temporary_password()
    target.password_hash = hash_password(temporary)
    target.must_change_password = True
    audit(db, request, actor, "password_reset", "user", target.id)
    db.commit()
    set_flash(request, f"Clave temporal nueva (se muestra una sola vez): {temporary}", "warning")
    return redirect("/usuarios")


@app.post("/usuarios/{user_id}/estado")
def user_toggle(
    user_id: int,
    request: Request,
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    actor = require_user(request, db)
    require_roles(actor, "admin")
    target = db.get(User, user_id)
    if not target or target.id == actor.id:
        raise HTTPException(400, "Operación no válida")
    target.is_active = not target.is_active
    audit(db, request, actor, "user_status_changed", "user", target.id, {"active": target.is_active})
    db.commit()
    set_flash(request, "Estado del usuario actualizado.")
    return redirect("/usuarios")


@app.get("/secciones", response_class=HTMLResponse)
def sections_page(request: Request, school_year_id: int | None = None, db: Session = Depends(get_db)):
    user = require_user(request, db)
    active_year = get_active_school_year(db)
    selected_year = db.get(SchoolYear, school_year_id) if school_year_id else active_year
    if not selected_year:
        raise HTTPException(404, "Año escolar no encontrado")
    sections = section_counts_for_year(db, selected_year.id)
    years = db.query(SchoolYear).order_by(SchoolYear.start_date.desc()).all()
    return render(
        request,
        "sections.html",
        {"user": user, "sections": sections, "years": years, "selected_year": selected_year, "active_year": active_year},
    )


@app.get("/secciones/{section_id}", response_class=HTMLResponse)
def section_detail(section_id: int, request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    section = db.get(Section, section_id)
    if not section:
        raise HTTPException(404, "Sección no encontrada")
    active_year = get_active_school_year(db)
    students = students_for_section_year(db, section.id, active_year.id)
    evaluations = (
        db.query(Evaluation)
        .options(joinedload(Evaluation.subject))
        .filter(Evaluation.section_id == section.id, Evaluation.school_year_id == active_year.id)
        .order_by(Evaluation.id.desc())
        .all()
    )
    subjects = db.query(Subject).filter(Subject.is_active.is_(True)).order_by(Subject.name).all()
    return render(
        request,
        "section_detail.html",
        {"user": user, "section": section, "students": students, "evaluations": evaluations, "subjects": subjects, "active_year": active_year},
    )


@app.get("/evaluaciones/nueva", response_class=HTMLResponse)
def evaluation_new_page(
    request: Request,
    section_id: int | None = None,
    subject_id: int | None = None,
    db: Session = Depends(get_db),
):
    user = require_user(request, db)
    if user.role == "docente":
        assignments = (
            db.query(TeacherAssignment)
            .options(joinedload(TeacherAssignment.section), joinedload(TeacherAssignment.subject))
            .filter(TeacherAssignment.teacher_id == user.id)
            .all()
        )
    else:
        assignments = (
            db.query(TeacherAssignment)
            .options(joinedload(TeacherAssignment.teacher), joinedload(TeacherAssignment.section), joinedload(TeacherAssignment.subject))
            .all()
        )
    active_year = get_active_school_year(db)
    sections = sections_for_year(db, active_year.id)
    subjects = db.query(Subject).order_by(Subject.name).all()
    period_windows = (
        db.query(AcademicPeriodWindow)
        .filter(AcademicPeriodWindow.school_year == active_year.name, AcademicPeriodWindow.is_active.is_(True))
        .order_by(AcademicPeriodWindow.id)
        .all()
    )
    return render(
        request,
        "evaluation_new.html",
        {
            "user": user,
            "assignments": assignments,
            "sections": sections,
            "subjects": subjects,
            "selected_section": section_id,
            "selected_subject": subject_id,
            "today": institution_now().date().isoformat(),
            "period_windows": period_windows,
            "active_year": active_year,
        },
    )


@app.post("/evaluaciones/nueva")
def evaluation_new(
    request: Request,
    section_id: int = Form(...),
    subject_id: int = Form(...),
    period: str = Form(...),
    evaluation_number: int = Form(...),
    title: str = Form(...),
    technique: str = Form(""),
    instrument: str = Form(""),
    content: str = Form(""),
    competencies: str = Form(""),
    evaluation_date: date = Form(...),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    if period not in PERIOD_NAMES:
        raise HTTPException(400, "Período académico inválido")
    probe = Evaluation(section_id=section_id, subject_id=subject_id, teacher_id=user.id, title=title)
    if not can_manage_evaluation(user, probe, db):
        raise HTTPException(403, "No tiene asignación para esta sección y asignatura")

    blocked = pending_article112(db, section_id, subject_id)
    if blocked:
        set_flash(
            request,
            f"No puede configurar ni cargar evaluaciones posteriores. Debe completar primero el Artículo 112 pendiente: {blocked.title}.",
            "danger",
        )
        return redirect(f"/evaluaciones/{blocked.id}/notas")

    assignment = find_assignment(db, user.id, section_id, subject_id)
    if user.role == "docente":
        if not assignment:
            raise HTTPException(403, "No posee asignación docente para esta sección y materia")
        planning = planning_for_assignment(db, user.id, section_id, subject_id, period)
        if not planning:
            set_flash(
                request,
                "Debe cargar y cerrar la planificación del período antes de configurar evaluaciones.",
                "danger",
            )
            return redirect(f"/planificaciones?nueva_asignacion={assignment.id}&periodo={quote(period)}")
        window = period_window(db, period)
        late_auth = approved_request(
            db,
            requester_id=user.id,
            request_type="evaluation_config_late",
            target_type="assignment_period",
            target_id=assignment.id,
            period=period,
        )
        if not evaluation_config_window_open(window) and not late_auth:
            set_flash(
                request,
                "El período de configuración está cerrado. Debe solicitar por escrito a Coordinación una nueva fecha.",
                "danger",
            )
            return redirect(f"/solicitudes?nueva=evaluation_config_late&target_id={assignment.id}&periodo={quote(period)}")
    else:
        late_auth = None

    active_year = get_active_school_year(db)
    evaluation = Evaluation(
        section_id=section_id,
        subject_id=subject_id,
        teacher_id=user.id,
        school_year_id=active_year.id,
        period=period,
        evaluation_number=evaluation_number,
        title=title.strip(),
        technique=technique.strip(),
        instrument=instrument.strip(),
        content=content.strip(),
        competencies=competencies.strip(),
        evaluation_date=evaluation_date,
        passing_score=settings.article112_passing_score,
    )
    db.add(evaluation)
    db.flush()
    db.add(EvaluationWorkflow(evaluation_id=evaluation.id, configured_at=institution_now(), config_locked_at=institution_now()))
    if late_auth:
        late_auth.consumed_at = institution_now()
    audit(
        db,
        request,
        user,
        "evaluation_created_and_locked",
        "evaluation",
        evaluation.id,
        {"period": period, "late_authorization": late_auth.id if late_auth else None},
    )
    db.commit()
    set_flash(
        request,
        "Evaluación configurada y cerrada. Cualquier modificación requiere solicitud y acta aprobada por Coordinación.",
        "warning",
    )
    return redirect(f"/evaluaciones/{evaluation.id}/notas")


@app.get("/evaluaciones/{evaluation_id}/notas", response_class=HTMLResponse)
def grades_page(evaluation_id: int, request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    evaluation = (
        db.query(Evaluation)
        .options(
            joinedload(Evaluation.section),
            joinedload(Evaluation.subject),
            joinedload(Evaluation.eligibilities),
            joinedload(Evaluation.workflow),
            joinedload(Evaluation.parent),
        )
        .filter(Evaluation.id == evaluation_id)
        .first()
    )
    if not evaluation:
        raise HTTPException(404, "Evaluación no encontrada")
    if not can_manage_evaluation(user, evaluation, db) and user.role not in {"director", "evaluacion"}:
        raise HTTPException(403, "No autorizado")
    if evaluation.is_article112:
        eligible_ids = {e.student_id for e in evaluation.eligibilities}
        students = (
            db.query(Student)
            .filter(Student.id.in_(eligible_ids))
            .order_by(Student.list_number, Student.full_name)
            .all()
            if eligible_ids
            else []
        )
    else:
        report_year = evaluation.school_year or get_active_school_year(db)
        students = students_for_section_year(
            db, evaluation.section_id, report_year.id, include_inactive=True
        )
    workflow = get_or_create_workflow(evaluation, db)
    db.commit()
    grades = {g.student_id: g for g in evaluation.grades}
    result = calculate_article112(evaluation, db) if not evaluation.is_article112 else None
    entry_state = grade_entry_state(evaluation, workflow)
    pending_extension = (
        db.query(CoordinationRequest)
        .filter_by(requester_id=evaluation.teacher_id, request_type="grade_extension", target_type="evaluation", target_id=evaluation.id, status="pending")
        .order_by(CoordinationRequest.id.desc())
        .first()
    )
    blocker = None
    if not evaluation.is_article112:
        blocker = pending_article112(db, evaluation.section_id, evaluation.subject_id, exclude_evaluation_id=evaluation.id)
    return render(
        request,
        "grade_entry.html",
        {
            "user": user,
            "evaluation": evaluation,
            "students": students,
            "grades": grades,
            "article112": result,
            "workflow": workflow,
            "entry_state": entry_state,
            "pending_extension": pending_extension,
            "article112_blocker": blocker,
            "now_local": institution_now(),
        },
    )


@app.post("/evaluaciones/{evaluation_id}/notas")
async def save_grades(evaluation_id: int, request: Request, db: Session = Depends(get_db)):
    form = await request.form()
    validate_csrf(request, str(form.get("csrf_token", "")))
    user = require_user(request, db)
    evaluation = db.get(Evaluation, evaluation_id)
    if not evaluation or not can_manage_evaluation(user, evaluation, db):
        raise HTTPException(403, "No autorizado")
    if evaluation.status == "published":
        raise HTTPException(400, "Una evaluación publicada no puede editarse sin reapertura administrativa")

    if not evaluation.is_article112:
        blocker = pending_article112(db, evaluation.section_id, evaluation.subject_id, exclude_evaluation_id=evaluation.id)
        if blocker:
            set_flash(
                request,
                f"Carga bloqueada: debe completar primero la segunda evaluación del Artículo 112 ({blocker.title}).",
                "danger",
            )
            return redirect(f"/evaluaciones/{blocker.id}/notas")

    workflow = get_or_create_workflow(evaluation, db)
    entry_state = grade_entry_state(evaluation, workflow)
    if not entry_state["allowed"]:
        set_flash(request, entry_state["message"], "danger")
        db.commit()
        return redirect(f"/evaluaciones/{evaluation.id}/notas")

    if evaluation.is_article112:
        eligible_ids = {x.student_id for x in db.query(Article112Eligibility).filter_by(evaluation_id=evaluation.id).all()}
    else:
        report_year = evaluation.school_year or get_active_school_year(db)
        eligible_ids = {
            student.id for student in students_for_section_year(
                db, evaluation.section_id, report_year.id, include_inactive=True
            )
        }

    for student_id in eligible_ids:
        score_raw = str(form.get(f"score_{student_id}", "")).strip().replace(",", ".")
        absent = form.get(f"absent_{student_id}") == "on"
        comment = str(form.get(f"comment_{student_id}", "")).strip()[:255]
        grade = db.query(Grade).filter_by(evaluation_id=evaluation.id, student_id=student_id).first()
        if not grade:
            grade = Grade(evaluation_id=evaluation.id, student_id=student_id, updated_by_id=user.id)
            db.add(grade)
        grade.absent = absent
        grade.comment = comment
        grade.updated_by_id = user.id
        if absent:
            grade.score = None
        elif score_raw == "":
            grade.score = None
        else:
            try:
                score = float(score_raw)
            except ValueError:
                raise HTTPException(400, f"Calificación inválida para estudiante {student_id}")
            if score < 0 or score > evaluation.max_score:
                raise HTTPException(400, f"La calificación debe estar entre 0 y {evaluation.max_score}")
            grade.score = round(score, 2)
    audit(
        db,
        request,
        user,
        "grades_saved",
        "evaluation",
        evaluation.id,
        {"deadline": str(effective_grade_deadline(evaluation, workflow))},
    )
    db.commit()
    set_flash(request, "Calificaciones guardadas dentro del plazo habilitado.")
    return redirect(f"/evaluaciones/{evaluation.id}/notas")


@app.post("/evaluaciones/{evaluation_id}/publicar")
def publish_evaluation(
    evaluation_id: int,
    request: Request,
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    evaluation = db.get(Evaluation, evaluation_id)
    if not evaluation or not can_manage_evaluation(user, evaluation, db):
        raise HTTPException(403, "No autorizado")
    if evaluation.status == "published":
        return redirect(f"/evaluaciones/{evaluation.id}/notas")

    if not evaluation.is_article112:
        blocker = pending_article112(db, evaluation.section_id, evaluation.subject_id, exclude_evaluation_id=evaluation.id)
        if blocker:
            set_flash(
                request,
                f"No puede publicar otras notas hasta completar el Artículo 112 pendiente: {blocker.title}.",
                "danger",
            )
            return redirect(f"/evaluaciones/{blocker.id}/notas")

    workflow = get_or_create_workflow(evaluation, db)
    entry_state = grade_entry_state(evaluation, workflow)
    if not entry_state["allowed"]:
        set_flash(request, entry_state["message"], "danger")
        db.commit()
        return redirect(f"/evaluaciones/{evaluation.id}/notas")

    if evaluation.is_article112 and not evaluation.approved_by_id:
        set_flash(request, "La segunda evaluación debe ser aprobada por Dirección o Evaluación antes de publicarse.", "danger")
        return redirect(f"/evaluaciones/{evaluation.id}/notas")

    if evaluation.is_article112:
        expected = len(evaluation.eligibilities)
        grade_rows = db.query(Grade).filter(Grade.evaluation_id == evaluation.id).all()
        completed = sum(1 for grade in grade_rows if grade.score is not None or grade.absent)
        if completed < expected:
            set_flash(request, "Faltan calificaciones o inasistencias de estudiantes habilitados para el Artículo 112.", "danger")
            return redirect(f"/evaluaciones/{evaluation.id}/notas")
    else:
        result = calculate_article112(evaluation, db)
        if result["incomplete_ids"]:
            set_flash(request, "Debe registrar nota o inasistencia para todos los estudiantes antes de publicar.", "danger")
            return redirect(f"/evaluaciones/{evaluation.id}/notas")

    evaluation.status = "published"
    evaluation.published_at = institution_now()
    workflow.completed_at = evaluation.published_at
    created_second = None
    if not evaluation.is_article112:
        result = calculate_article112(evaluation, db)
        evaluation.article112_percentage = result["percentage"]
        evaluation.article112_triggered = result["triggered"]
        if result["triggered"]:
            created_second = create_article112_evaluation(evaluation, db)
            get_or_create_article112_act(db, evaluation, user)
    audit(
        db,
        request,
        user,
        "evaluation_published",
        "evaluation",
        evaluation.id,
        {
            "article112_created": created_second.id if created_second else None,
            "conducted_at": str(workflow.conducted_at),
            "grade_deadline": str(effective_grade_deadline(evaluation, workflow)),
        },
    )
    db.commit()
    if created_second:
        set_flash(
            request,
            f"Evaluación publicada. Se activó el Artículo 112 ({evaluation.article112_percentage:.1%}). El sistema bloqueó las evaluaciones posteriores hasta completar la segunda oportunidad.",
            "warning",
        )
        return redirect(f"/evaluaciones/{created_second.id}/notas")
    set_flash(request, "Evaluación publicada correctamente.")
    return redirect(f"/evaluaciones/{evaluation.id}/notas")


@app.post("/evaluaciones/{evaluation_id}/aprobar-112")
def approve_article112(
    evaluation_id: int,
    request: Request,
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    evaluation = db.get(Evaluation, evaluation_id)
    if not evaluation or not evaluation.is_article112:
        raise HTTPException(404, "Evaluación Artículo 112 no encontrada")
    evaluation.approved_by_id = user.id
    evaluation.approved_at = datetime.utcnow()
    audit(db, request, user, "article112_approved", "evaluation", evaluation.id)
    db.commit()
    set_flash(request, "Segunda forma de evaluación aprobada y registrada bajo supervisión institucional.")
    return redirect(f"/evaluaciones/{evaluation.id}/notas")


@app.post("/evaluaciones/{evaluation_id}/realizada")
def mark_evaluation_conducted(
    evaluation_id: int,
    request: Request,
    conducted_at: str = Form(...),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    evaluation = db.get(Evaluation, evaluation_id)
    if not evaluation or not can_manage_evaluation(user, evaluation, db):
        raise HTTPException(403, "No autorizado")
    if evaluation.status == "published":
        raise HTTPException(400, "La evaluación ya está publicada")
    if evaluation.is_article112 and not evaluation.approved_by_id:
        set_flash(request, "Dirección o Evaluación debe aprobar la segunda oportunidad antes de marcarla como realizada.", "danger")
        return redirect(f"/evaluaciones/{evaluation.id}/notas")
    if not evaluation.is_article112:
        blocker = pending_article112(db, evaluation.section_id, evaluation.subject_id, exclude_evaluation_id=evaluation.id)
        if blocker:
            set_flash(request, "Debe completar el Artículo 112 pendiente antes de continuar con otra evaluación.", "danger")
            return redirect(f"/evaluaciones/{blocker.id}/notas")
    workflow = get_or_create_workflow(evaluation, db)
    if workflow.conducted_at:
        set_flash(request, "La fecha de realización ya fue registrada y no puede alterarse sin acta.", "warning")
        return redirect(f"/evaluaciones/{evaluation.id}/notas")
    try:
        actual = datetime.fromisoformat(conducted_at)
    except ValueError:
        raise HTTPException(400, "Fecha y hora de realización inválidas")
    if actual > institution_now() + timedelta(minutes=5):
        raise HTTPException(400, "No puede registrar una evaluación como realizada en una fecha futura")
    mark_conducted(workflow, actual)
    audit(
        db,
        request,
        user,
        "evaluation_marked_conducted",
        "evaluation",
        evaluation.id,
        {"conducted_at": str(actual), "grades_due_at": str(workflow.grades_due_at)},
    )
    db.commit()
    set_flash(request, f"Evaluación marcada como realizada. Las notas deben publicarse en un máximo de {settings.grade_upload_hours} horas.", "warning")
    return redirect(f"/evaluaciones/{evaluation.id}/notas")


@app.get("/evaluaciones/{evaluation_id}/configuracion", response_class=HTMLResponse)
def evaluation_configuration_page(evaluation_id: int, request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    evaluation = (
        db.query(Evaluation)
        .options(joinedload(Evaluation.section), joinedload(Evaluation.subject), joinedload(Evaluation.workflow))
        .filter(Evaluation.id == evaluation_id)
        .first()
    )
    if not evaluation:
        raise HTTPException(404, "Evaluación no encontrada")
    if not can_manage_evaluation(user, evaluation, db):
        raise HTTPException(403, "No autorizado")
    workflow = get_or_create_workflow(evaluation, db)
    authorization = approved_request(
        db,
        requester_id=evaluation.teacher_id,
        request_type="config_modification",
        target_type="evaluation",
        target_id=evaluation.id,
    )
    db.commit()
    return render(
        request,
        "evaluation_config.html",
        {"user": user, "evaluation": evaluation, "workflow": workflow, "authorization": authorization},
    )


@app.post("/evaluaciones/{evaluation_id}/configuracion")
def evaluation_configuration_update(
    evaluation_id: int,
    request: Request,
    title: str = Form(...),
    technique: str = Form(""),
    instrument: str = Form(""),
    content: str = Form(...),
    competencies: str = Form(...),
    evaluation_date: date = Form(...),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    evaluation = db.get(Evaluation, evaluation_id)
    if not evaluation or not can_manage_evaluation(user, evaluation, db):
        raise HTTPException(403, "No autorizado")
    if evaluation.is_article112:
        raise HTTPException(400, "La configuración del Artículo 112 se deriva de la evaluación original y no puede alterarse por esta vía")
    authorization = approved_request(
        db,
        requester_id=evaluation.teacher_id,
        request_type="config_modification",
        target_type="evaluation",
        target_id=evaluation.id,
    )
    if not authorization:
        set_flash(request, "La configuración está cerrada. Requiere solicitud y acta aprobada por Coordinación.", "danger")
        return redirect(f"/evaluaciones/{evaluation.id}/configuracion")
    before = {
        "title": evaluation.title,
        "technique": evaluation.technique,
        "instrument": evaluation.instrument,
        "content": evaluation.content,
        "competencies": evaluation.competencies,
        "evaluation_date": str(evaluation.evaluation_date),
    }
    evaluation.title = title.strip()
    evaluation.technique = technique.strip()
    evaluation.instrument = instrument.strip()
    evaluation.content = content.strip()
    evaluation.competencies = competencies.strip()
    evaluation.evaluation_date = evaluation_date
    authorization.consumed_at = institution_now()
    audit(
        db,
        request,
        user,
        "evaluation_configuration_modified_by_act",
        "evaluation",
        evaluation.id,
        {"act_number": authorization.act_number, "before": before},
    )
    db.commit()
    set_flash(request, f"Configuración modificada conforme al acta {authorization.act_number}.")
    return redirect(f"/evaluaciones/{evaluation.id}/configuracion")


@app.get("/periodos", response_class=HTMLResponse)
def periods_page(request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    active_year = get_active_school_year(db)
    periods = (
        db.query(AcademicPeriodWindow)
        .filter(AcademicPeriodWindow.school_year == active_year.name)
        .order_by(AcademicPeriodWindow.id)
        .all()
    )
    return render(request, "periods.html", {"user": user, "periods": periods, "today": institution_now().date(), "active_year": active_year})


@app.post("/periodos/{period_id}")
def period_update(
    period_id: int,
    request: Request,
    planning_start: date = Form(...),
    planning_end: date = Form(...),
    evaluation_config_start: date = Form(...),
    evaluation_config_end: date = Form(...),
    is_active: str | None = Form(None),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    period = db.get(AcademicPeriodWindow, period_id)
    if not period:
        raise HTTPException(404, "Período no encontrado")
    if planning_start > planning_end or evaluation_config_start > evaluation_config_end:
        set_flash(request, "Las fechas de inicio no pueden ser posteriores a las fechas de cierre.", "danger")
        return redirect("/periodos")
    period.planning_start = planning_start
    period.planning_end = planning_end
    period.evaluation_config_start = evaluation_config_start
    period.evaluation_config_end = evaluation_config_end
    period.is_active = is_active == "on"
    audit(db, request, user, "academic_period_updated", "academic_period", period.id, {"name": period.name})
    db.commit()
    set_flash(request, f"Fechas del {period.name} actualizadas.")
    return redirect("/periodos")


@app.get("/planificaciones", response_class=HTMLResponse)
def plannings_page(
    request: Request,
    nueva_asignacion: int | None = None,
    periodo: str | None = None,
    db: Session = Depends(get_db),
):
    user = require_user(request, db)
    if user.role == "docente":
        assignments = (
            db.query(TeacherAssignment)
            .options(joinedload(TeacherAssignment.section), joinedload(TeacherAssignment.subject))
            .filter(TeacherAssignment.teacher_id == user.id)
            .order_by(TeacherAssignment.id)
            .all()
        )
        plannings = (
            db.query(PeriodPlanning)
            .options(joinedload(PeriodPlanning.section), joinedload(PeriodPlanning.subject))
            .filter(PeriodPlanning.teacher_id == user.id)
            .order_by(PeriodPlanning.id.desc())
            .all()
        )
    else:
        require_roles(user, "admin", "director", "evaluacion")
        assignments = (
            db.query(TeacherAssignment)
            .options(joinedload(TeacherAssignment.teacher), joinedload(TeacherAssignment.section), joinedload(TeacherAssignment.subject))
            .order_by(TeacherAssignment.id)
            .all()
        )
        plannings = (
            db.query(PeriodPlanning)
            .options(joinedload(PeriodPlanning.teacher), joinedload(PeriodPlanning.section), joinedload(PeriodPlanning.subject))
            .order_by(PeriodPlanning.id.desc())
            .all()
        )
    active_year = get_active_school_year(db)
    plannings = [p for p in plannings if p.school_year_id in {None, active_year.id}]
    windows = {
        item.name: item
        for item in db.query(AcademicPeriodWindow)
        .filter(AcademicPeriodWindow.school_year == active_year.name)
        .all()
    }
    planning_map = {(p.teacher_id, p.section_id, p.subject_id, p.period): p for p in plannings}
    return render(
        request,
        "plannings.html",
        {
            "user": user,
            "assignments": assignments,
            "plannings": plannings,
            "planning_map": planning_map,
            "period_names": PERIOD_NAMES,
            "windows": windows,
            "new_assignment": nueva_asignacion,
            "new_period": periodo,
            "today": institution_now().date(),
            "active_year": active_year,
        },
    )


@app.post("/planificaciones")
def planning_submit(
    request: Request,
    assignment_id: int = Form(...),
    period: str = Form(...),
    purpose: str = Form(...),
    contents: str = Form(...),
    competencies: str = Form(...),
    strategies: str = Form(...),
    evaluation_schedule: str = Form(...),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    assignment = (
        db.query(TeacherAssignment)
        .options(joinedload(TeacherAssignment.section), joinedload(TeacherAssignment.subject))
        .filter(TeacherAssignment.id == assignment_id)
        .first()
    )
    if not assignment or assignment.teacher_id != user.id:
        raise HTTPException(403, "La asignación no pertenece al usuario")
    if period not in PERIOD_NAMES:
        raise HTTPException(400, "Período inválido")
    existing = planning_for_assignment(db, user.id, assignment.section_id, assignment.subject_id, period)
    if existing:
        set_flash(request, "La planificación ya fue cargada y está cerrada. Para modificarla debe generar una solicitud mediante acta.", "warning")
        return redirect(f"/planificaciones/{existing.id}")
    window = period_window(db, period)
    late_auth = approved_request(
        db,
        requester_id=user.id,
        request_type="planning_late",
        target_type="assignment_period",
        target_id=assignment.id,
        period=period,
    )
    if not planning_window_open(window) and not late_auth:
        set_flash(request, "El período de planificación está cerrado. Solicite por escrito una nueva fecha a Coordinación.", "danger")
        return redirect(f"/solicitudes?nueva=planning_late&target_id={assignment.id}&periodo={quote(period)}")
    active_year = get_active_school_year(db)
    planning = PeriodPlanning(
        teacher_id=user.id,
        section_id=assignment.section_id,
        subject_id=assignment.subject_id,
        school_year_id=active_year.id,
        period=period,
        purpose=purpose.strip(),
        contents=contents.strip(),
        competencies=competencies.strip(),
        strategies=strategies.strip(),
        evaluation_schedule=evaluation_schedule.strip(),
        submitted_at=institution_now(),
        locked_at=institution_now(),
    )
    db.add(planning)
    db.flush()
    if late_auth:
        late_auth.consumed_at = institution_now()
    audit(db, request, user, "planning_submitted_and_locked", "planning", planning.id, {"period": period, "late_authorization": late_auth.id if late_auth else None})
    db.commit()
    set_flash(request, "Planificación cargada y cerrada. Toda modificación posterior requiere acta aprobada.", "warning")
    return redirect(f"/planificaciones/{planning.id}")


@app.get("/planificaciones/{planning_id}", response_class=HTMLResponse)
def planning_detail(planning_id: int, request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    planning = (
        db.query(PeriodPlanning)
        .options(joinedload(PeriodPlanning.teacher), joinedload(PeriodPlanning.section), joinedload(PeriodPlanning.subject))
        .filter(PeriodPlanning.id == planning_id)
        .first()
    )
    if not planning:
        raise HTTPException(404, "Planificación no encontrada")
    if user.role == "docente" and planning.teacher_id != user.id:
        raise HTTPException(403, "No autorizado")
    authorization = approved_request(
        db,
        requester_id=planning.teacher_id,
        request_type="planning_modification",
        target_type="planning",
        target_id=planning.id,
    )
    return render(request, "planning_detail.html", {"user": user, "planning": planning, "authorization": authorization})


@app.post("/planificaciones/{planning_id}")
def planning_update(
    planning_id: int,
    request: Request,
    purpose: str = Form(...),
    contents: str = Form(...),
    competencies: str = Form(...),
    strategies: str = Form(...),
    evaluation_schedule: str = Form(...),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    planning = db.get(PeriodPlanning, planning_id)
    if not planning or (user.role == "docente" and planning.teacher_id != user.id):
        raise HTTPException(403, "No autorizado")
    authorization = approved_request(
        db,
        requester_id=planning.teacher_id,
        request_type="planning_modification",
        target_type="planning",
        target_id=planning.id,
    )
    if not authorization:
        set_flash(request, "La planificación está cerrada. Debe solicitar modificación mediante acta.", "danger")
        return redirect(f"/planificaciones/{planning.id}")
    planning.purpose = purpose.strip()
    planning.contents = contents.strip()
    planning.competencies = competencies.strip()
    planning.strategies = strategies.strip()
    planning.evaluation_schedule = evaluation_schedule.strip()
    authorization.consumed_at = institution_now()
    audit(db, request, user, "planning_modified_by_act", "planning", planning.id, {"act_number": authorization.act_number})
    db.commit()
    set_flash(request, f"Planificación actualizada conforme al acta {authorization.act_number}.")
    return redirect(f"/planificaciones/{planning.id}")


@app.get("/solicitudes", response_class=HTMLResponse)
def requests_page(
    request: Request,
    nueva: str | None = None,
    target_id: int | None = None,
    periodo: str | None = None,
    db: Session = Depends(get_db),
):
    user = require_user(request, db)
    query = db.query(CoordinationRequest).options(
        joinedload(CoordinationRequest.requester),
        joinedload(CoordinationRequest.resolved_by),
        joinedload(CoordinationRequest.section),
        joinedload(CoordinationRequest.subject),
    )
    if user.role == "docente":
        query = query.filter(CoordinationRequest.requester_id == user.id)
    else:
        require_roles(user, "admin", "director", "evaluacion")
    requests = query.order_by(CoordinationRequest.id.desc()).all()
    return render(
        request,
        "requests.html",
        {
            "user": user,
            "requests": requests,
            "request_labels": REQUEST_LABELS,
            "new_type": nueva,
            "new_target_id": target_id,
            "new_period": periodo,
            "now_local": institution_now(),
        },
    )


@app.post("/solicitudes")
def request_create(
    request: Request,
    request_type: str = Form(...),
    target_id: int = Form(...),
    period: str = Form(""),
    act_number: str = Form(...),
    written_request: str = Form(...),
    reason: str = Form(...),
    proposed_changes: str = Form(""),
    requested_new_date: str = Form(""),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    if request_type not in REQUEST_LABELS:
        raise HTTPException(400, "Tipo de solicitud inválido")
    target_type = ""
    section_id = None
    subject_id = None
    if request_type in {"planning_late", "evaluation_config_late"}:
        assignment = db.get(TeacherAssignment, target_id)
        if not assignment or assignment.teacher_id != user.id:
            raise HTTPException(403, "Asignación inválida")
        target_type = "assignment_period"
        section_id, subject_id = assignment.section_id, assignment.subject_id
        if period not in PERIOD_NAMES:
            raise HTTPException(400, "Período inválido")
    elif request_type == "planning_modification":
        planning = db.get(PeriodPlanning, target_id)
        if not planning or planning.teacher_id != user.id:
            raise HTTPException(403, "Planificación inválida")
        target_type = "planning"
        section_id, subject_id, period = planning.section_id, planning.subject_id, planning.period
    else:
        evaluation = db.get(Evaluation, target_id)
        if not evaluation or evaluation.teacher_id != user.id:
            raise HTTPException(403, "Evaluación inválida")
        target_type = "evaluation"
        section_id, subject_id, period = evaluation.section_id, evaluation.subject_id, evaluation.period
    parsed_new_date = None
    if requested_new_date:
        try:
            parsed_new_date = datetime.fromisoformat(requested_new_date)
        except ValueError:
            raise HTTPException(400, "Nueva fecha solicitada inválida")
    if request_type in {"planning_late", "evaluation_config_late", "config_modification", "planning_modification", "grade_extension"} and not parsed_new_date:
        raise HTTPException(400, "Debe indicar la nueva fecha o fecha límite solicitada")
    if parsed_new_date and parsed_new_date <= institution_now():
        raise HTTPException(400, "La nueva fecha solicitada debe ser posterior a la fecha institucional actual")
    if not act_number.strip() or not written_request.strip() or not reason.strip():
        raise HTTPException(400, "La referencia de acta, la solicitud escrita y el motivo son obligatorios")
    duplicate = (
        db.query(CoordinationRequest)
        .filter_by(requester_id=user.id, request_type=request_type, target_type=target_type, target_id=target_id, status="pending")
        .first()
    )
    if duplicate:
        set_flash(request, "Ya existe una solicitud pendiente para este caso.", "warning")
        return redirect("/solicitudes")
    item = CoordinationRequest(
        requester_id=user.id,
        request_type=request_type,
        target_type=target_type,
        target_id=target_id,
        section_id=section_id,
        subject_id=subject_id,
        period=period,
        act_number=act_number.strip(),
        written_request=written_request.strip(),
        reason=reason.strip(),
        proposed_changes=proposed_changes.strip(),
        requested_new_date=parsed_new_date,
        status="pending",
    )
    db.add(item)
    db.flush()
    audit(db, request, user, "coordination_request_created", "coordination_request", item.id, {"type": request_type, "act_number": item.act_number})
    db.commit()
    set_flash(request, "Solicitud escrita registrada y enviada a Coordinación.", "warning")
    return redirect("/solicitudes")


@app.get("/solicitudes/{request_id}", response_class=HTMLResponse)
def request_detail(request_id: int, request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    item = (
        db.query(CoordinationRequest)
        .options(
            joinedload(CoordinationRequest.requester),
            joinedload(CoordinationRequest.resolved_by),
            joinedload(CoordinationRequest.section),
            joinedload(CoordinationRequest.subject),
        )
        .filter(CoordinationRequest.id == request_id)
        .first()
    )
    if not item:
        raise HTTPException(404, "Solicitud no encontrada")
    if user.role == "docente" and item.requester_id != user.id:
        raise HTTPException(403, "No autorizado")
    return render(
        request,
        "request_detail.html",
        {"user": user, "item": item, "request_labels": REQUEST_LABELS},
    )


@app.post("/solicitudes/{request_id}/resolver")
def request_resolve(
    request_id: int,
    request: Request,
    decision: str = Form(...),
    resolution: str = Form(...),
    approved_new_date: str = Form(""),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    item = db.get(CoordinationRequest, request_id)
    if not item or item.status != "pending":
        raise HTTPException(404, "Solicitud pendiente no encontrada")
    if decision not in {"approved", "rejected"}:
        raise HTTPException(400, "Decisión inválida")
    new_date = item.requested_new_date
    if approved_new_date:
        try:
            new_date = datetime.fromisoformat(approved_new_date)
        except ValueError:
            raise HTTPException(400, "Nueva fecha aprobada inválida")
    if decision == "approved" and not new_date:
        raise HTTPException(400, "Debe fijar una nueva fecha de autorización")
    if decision == "approved" and new_date <= institution_now():
        raise HTTPException(400, "La nueva fecha aprobada debe ser posterior a la fecha institucional actual")
    item.status = decision
    item.resolution = resolution.strip()
    item.resolved_by_id = user.id
    item.resolved_at = institution_now()
    item.requested_new_date = new_date
    if decision == "approved" and item.request_type == "grade_extension":
        evaluation = db.get(Evaluation, item.target_id)
        if not evaluation:
            raise HTTPException(404, "Evaluación vinculada no encontrada")
        workflow = get_or_create_workflow(evaluation, db)
        workflow.extended_due_at = new_date
        item.consumed_at = institution_now()
    audit(db, request, user, "coordination_request_resolved", "coordination_request", item.id, {"decision": decision, "new_date": str(new_date)})
    db.commit()
    set_flash(request, "Solicitud resuelta y registrada en auditoría.")
    return redirect("/solicitudes")


@app.get("/reportes", response_class=HTMLResponse)
def reports_page(request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    active_year = get_active_school_year(db)
    evaluations_query = (
        db.query(Evaluation)
        .options(joinedload(Evaluation.section), joinedload(Evaluation.subject), joinedload(Evaluation.school_year))
        .filter(Evaluation.status == "published", Evaluation.is_article112.is_(False))
    )
    if user.role == "docente":
        assignment_pairs = db.query(TeacherAssignment.section_id, TeacherAssignment.subject_id).filter(
            TeacherAssignment.teacher_id == user.id
        ).all()
        allowed_pairs = set(assignment_pairs)
        evaluations = [
            item for item in evaluations_query.order_by(Evaluation.id.desc()).limit(300).all()
            if (item.section_id, item.subject_id) in allowed_pairs
        ][:150]
        section_ids = {section_id for section_id, _ in allowed_pairs}
        subject_ids = {subject_id for _, subject_id in allowed_pairs}
        sections = db.query(Section).filter(Section.id.in_(section_ids)).order_by(Section.grade_level, Section.letter).all() if section_ids else []
        subjects = db.query(Subject).filter(Subject.id.in_(subject_ids), Subject.is_active.is_(True)).order_by(Subject.name).all() if subject_ids else []
    else:
        evaluations = evaluations_query.order_by(Evaluation.id.desc()).limit(150).all()
        sections = db.query(Section).order_by(Section.grade_level, Section.letter).all()
        subjects = db.query(Subject).filter(Subject.is_active.is_(True)).order_by(Subject.name).all()
    years = db.query(SchoolYear).order_by(SchoolYear.start_date.desc()).all()
    return render(request, "reports.html", {
        "user": user, "evaluations": evaluations, "years": years, "sections": sections,
        "subjects": subjects, "period_names": PERIOD_NAMES, "active_year": active_year,
    })


@app.get("/reportes/evaluacion/{evaluation_id}.csv")
def evaluation_csv(evaluation_id: int, request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    evaluation = (
        db.query(Evaluation)
        .options(joinedload(Evaluation.section), joinedload(Evaluation.subject))
        .filter(Evaluation.id == evaluation_id)
        .first()
    )
    if not evaluation or evaluation.status != "published" or evaluation.is_article112:
        raise HTTPException(404, "Reporte no disponible")
    if not can_manage_evaluation(user, evaluation, db):
        raise HTTPException(403, "No autorizado para consultar este reporte")
    report_year = evaluation.school_year or get_active_school_year(db)
    students = students_for_section_year(db, evaluation.section_id, report_year.id, include_inactive=True)
    out = io.StringIO()
    writer = csv.writer(out)
    writer.writerow([settings.institution_name, settings.institution_code])
    writer.writerow(["Año escolar", report_year.name])
    writer.writerow(["Sección", evaluation.section.name, "Asignatura", evaluation.subject.name])
    writer.writerow(["Evaluación", evaluation.title, "Publicada", evaluation.published_at])
    writer.writerow([])
    writer.writerow(["N°", "Cédula", "Nombres y apellidos", "Sexo", "Nota original", "Artículo 112", "Definitiva"])
    original_map = {g.student_id: g for g in evaluation.grades}
    second = db.query(Evaluation).filter_by(parent_evaluation_id=evaluation.id, is_article112=True).first()
    second_map = {g.student_id: g for g in second.grades} if second else {}
    for s in students:
        original = original_map.get(s.id)
        second_grade = second_map.get(s.id)
        writer.writerow(
            [
                s.list_number,
                s.ci,
                s.full_name,
                s.sex,
                "I" if original and original.absent else (original.score if original else ""),
                second_grade.score if second_grade and second_grade.score is not None else "",
                definitive_score(evaluation, s.id, db),
            ]
        )
    filename = f"notas-{evaluation.section.name}-{evaluation.subject.name}.csv".replace(" ", "-")
    audit(db, request, user, "report_exported", "evaluation", evaluation.id, {"format": "csv"})
    db.commit()
    return StreamingResponse(
        iter([out.getvalue().encode("utf-8-sig")]),
        media_type="text/csv; charset=utf-8",
        headers={"Content-Disposition": f"attachment; filename*=UTF-8''{quote(filename)}"},
    )


@app.get("/anios-escolares", response_class=HTMLResponse)
def school_years_page(request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    active_year = get_active_school_year(db)
    years = db.query(SchoolYear).order_by(SchoolYear.start_date.desc()).all()
    year_stats = {}
    for year in years:
        year_stats[year.id] = {
            "sections": db.query(SchoolYearSection).filter_by(school_year_id=year.id, is_active=True).count(),
            "students": db.query(StudentEnrollment).filter(
                StudentEnrollment.school_year_id == year.id,
                StudentEnrollment.status.in_(("enrolled", "promoted", "repeater")),
            ).count(),
            "evaluations": db.query(Evaluation).filter(Evaluation.school_year_id == year.id).count(),
        }
    return render(request, "school_years.html", {
        "user": user, "years": years, "active_year": active_year, "year_stats": year_stats,
    })


@app.post("/anios-escolares")
def school_year_create(
    request: Request,
    name: str = Form(...),
    start_date: date = Form(...),
    end_date: date = Form(...),
    copy_from_year_id: int | None = Form(None),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    copy_from = db.get(SchoolYear, copy_from_year_id) if copy_from_year_id else get_active_school_year(db)
    try:
        year = create_school_year(
            db, name=name, start_date=start_date, end_date=end_date, copy_sections_from=copy_from
        )
        seed_period_windows(db, year.name)
        audit(db, request, user, "school_year_created", "school_year", year.id, {"name": year.name})
        db.commit()
    except ValueError as exc:
        db.rollback()
        set_flash(request, str(exc), "danger")
        return redirect("/anios-escolares")
    set_flash(request, f"Año escolar {year.name} creado en estado de planificación.")
    return redirect("/anios-escolares")


@app.post("/anios-escolares/{school_year_id}/editar")
def school_year_update(
    school_year_id: int,
    request: Request,
    name: str = Form(...),
    start_date: date = Form(...),
    end_date: date = Form(...),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    year = db.get(SchoolYear, school_year_id)
    if not year:
        raise HTTPException(404, "Año escolar no encontrado")
    normalized = name.strip().replace("/", "-")
    try:
        from .services.academic import parse_school_year
        parse_school_year(normalized)
        if start_date >= end_date:
            raise ValueError("La fecha de inicio debe ser anterior a la fecha de cierre")
        duplicate = db.query(SchoolYear).filter(SchoolYear.name == normalized, SchoolYear.id != year.id).first()
        if duplicate:
            raise ValueError("Ya existe otro año escolar con ese nombre")
        old_name = year.name
        year.name = normalized
        year.start_date = start_date
        year.end_date = end_date
        if old_name != normalized:
            db.query(AcademicPeriodWindow).filter(AcademicPeriodWindow.school_year == old_name).update(
                {AcademicPeriodWindow.school_year: normalized}, synchronize_session=False
            )
        audit(db, request, user, "school_year_updated", "school_year", year.id, {"old_name": old_name, "name": normalized})
        db.commit()
    except ValueError as exc:
        db.rollback()
        set_flash(request, str(exc), "danger")
        return redirect("/anios-escolares")
    set_flash(request, "Datos del año escolar actualizados.")
    return redirect("/anios-escolares")


@app.post("/anios-escolares/{school_year_id}/activar")
def school_year_activate(
    school_year_id: int,
    request: Request,
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    year = db.get(SchoolYear, school_year_id)
    if not year:
        raise HTTPException(404, "Año escolar no encontrado")
    try:
        activate_school_year(db, year)
        seed_period_windows(db, year.name)
        audit(db, request, user, "school_year_activated", "school_year", year.id, {"name": year.name})
        db.commit()
    except ValueError as exc:
        db.rollback()
        set_flash(request, str(exc), "danger")
        return redirect("/anios-escolares")
    set_flash(request, f"{year.name} quedó como año escolar activo.", "warning")
    return redirect("/anios-escolares")


@app.post("/anios-escolares/{school_year_id}/cerrar")
def school_year_close(
    school_year_id: int,
    request: Request,
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    year = db.get(SchoolYear, school_year_id)
    if not year:
        raise HTTPException(404, "Año escolar no encontrado")
    if year.is_active:
        set_flash(request, "Active primero otro año escolar antes de cerrar el año vigente.", "danger")
        return redirect("/anios-escolares")
    close_school_year(db, year)
    audit(db, request, user, "school_year_closed", "school_year", year.id, {"name": year.name})
    db.commit()
    set_flash(request, f"Año escolar {year.name} cerrado. Su historial permanece disponible.", "warning")
    return redirect("/anios-escolares")


@app.post("/secciones/nueva")
def section_create(
    request: Request,
    grade_level: str = Form(...),
    letter: str = Form(...),
    school_year_id: int = Form(...),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    year = db.get(SchoolYear, school_year_id)
    if not year:
        raise HTTPException(404, "Año escolar no encontrado")
    grade = grade_level.strip().upper()
    section_letter = letter.strip().upper()[:5]
    if not grade or not section_letter:
        set_flash(request, "Debe indicar año/grado y literal de sección.", "danger")
        return redirect(f"/secciones?school_year_id={year.id}")
    name = f"{grade} {section_letter}"
    section = db.query(Section).filter(Section.name == name).first()
    if not section:
        section = Section(name=name, grade_level=grade, letter=section_letter, is_active=True)
        db.add(section)
        db.flush()
    else:
        section.is_active = True
    link = db.query(SchoolYearSection).filter_by(school_year_id=year.id, section_id=section.id).first()
    if not link:
        link = SchoolYearSection(school_year_id=year.id, section_id=section.id, display_name=name, is_active=True)
        db.add(link)
    else:
        link.is_active = True
        link.display_name = name
    audit(db, request, user, "section_created_or_enabled", "section", section.id, {"school_year": year.name, "name": name})
    db.commit()
    set_flash(request, f"Sección {name} habilitada para {year.name}.")
    return redirect(f"/secciones?school_year_id={year.id}")


@app.post("/secciones/{section_id}/editar")
def section_update(
    section_id: int,
    request: Request,
    grade_level: str = Form(...),
    letter: str = Form(...),
    is_active: str | None = Form(None),
    school_year_id: int | None = Form(None),
    csrf_token: str = Form(...),
    db: Session = Depends(get_db),
):
    validate_csrf(request, csrf_token)
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    section = db.get(Section, section_id)
    if not section:
        raise HTTPException(404, "Sección no encontrada")
    grade = grade_level.strip().upper()
    section_letter = letter.strip().upper()[:5]
    new_name = f"{grade} {section_letter}"
    duplicate = db.query(Section).filter(Section.name == new_name, Section.id != section.id).first()
    if duplicate:
        set_flash(request, "Ya existe una sección con ese nombre.", "danger")
        return redirect(f"/secciones?school_year_id={school_year_id or ''}")
    old_name = section.name
    section.name = new_name
    section.grade_level = grade
    section.letter = section_letter
    # El catálogo base permanece disponible para conservar los históricos.
    # La habilitación se administra por año escolar mediante SchoolYearSection.
    section.is_active = True
    year_active = is_active == "on"
    if school_year_id:
        link = db.query(SchoolYearSection).filter_by(school_year_id=school_year_id, section_id=section.id).first()
        if not link:
            link = SchoolYearSection(
                school_year_id=school_year_id, section_id=section.id, display_name=new_name, is_active=year_active
            )
            db.add(link)
        else:
            link.display_name = new_name
            link.is_active = year_active
    audit(db, request, user, "section_updated", "section", section.id, {
        "old_name": old_name, "name": new_name, "active_in_school_year": year_active,
        "school_year_id": school_year_id,
    })
    db.commit()
    set_flash(request, f"Sección {new_name} actualizada.")
    return redirect(f"/secciones?school_year_id={school_year_id or get_active_school_year(db).id}")


@app.get("/promociones", response_class=HTMLResponse)
def promotions_page(
    request: Request,
    source_year_id: int | None = None,
    target_year_id: int | None = None,
    source_section_id: int | None = None,
    db: Session = Depends(get_db),
):
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    years = db.query(SchoolYear).order_by(SchoolYear.start_date).all()
    active_year = get_active_school_year(db)
    source_year = db.get(SchoolYear, source_year_id) if source_year_id else active_year
    later_years = [year for year in years if year.start_date > source_year.start_date]
    target_year = db.get(SchoolYear, target_year_id) if target_year_id else (later_years[0] if later_years else None)
    source_sections = sections_for_year(db, source_year.id) if source_year else []
    source_section = db.get(Section, source_section_id) if source_section_id else (source_sections[0] if source_sections else None)
    target_sections = sections_for_year(db, target_year.id) if target_year else []
    enrollments = promotion_source_enrollments(db, source_year.id, source_section.id) if source_year and source_section else []
    batches = (
        db.query(PromotionBatch)
        .options(
            joinedload(PromotionBatch.source_school_year), joinedload(PromotionBatch.target_school_year),
            joinedload(PromotionBatch.source_section), joinedload(PromotionBatch.target_section),
            joinedload(PromotionBatch.created_by), joinedload(PromotionBatch.records),
        )
        .order_by(PromotionBatch.id.desc()).limit(30).all()
    )
    return render(request, "promotions.html", {
        "user": user, "years": years, "active_year": active_year, "source_year": source_year,
        "target_year": target_year, "source_sections": source_sections, "source_section": source_section,
        "target_sections": target_sections, "enrollments": enrollments, "batches": batches,
        "promotion_results": PROMOTION_RESULTS,
    })


@app.post("/promociones/ejecutar")
async def promotions_execute(request: Request, db: Session = Depends(get_db)):
    form = await request.form()
    validate_csrf(request, str(form.get("csrf_token", "")))
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    try:
        source_year = db.get(SchoolYear, int(str(form.get("source_year_id"))))
        target_year = db.get(SchoolYear, int(str(form.get("target_year_id"))))
        source_section = db.get(Section, int(str(form.get("source_section_id"))))
        target_raw = str(form.get("target_section_id", "")).strip()
        target_section = db.get(Section, int(target_raw)) if target_raw else None
        student_ids = [int(value) for value in form.getlist("student_ids")]
        result = str(form.get("result", "promoted"))
        notes = str(form.get("notes", ""))
        if not source_year or not target_year or not source_section:
            raise ValueError("Faltan datos de origen o destino")
        batch = promote_students(
            db, actor=user, source_year=source_year, target_year=target_year,
            source_section=source_section, target_section=target_section,
            student_ids=student_ids, result=result, notes=notes,
        )
        audit(db, request, user, "student_promotion_batch", "promotion_batch", batch.id, {
            "students": len(batch.records), "result": result,
            "source_year": source_year.name, "target_year": target_year.name,
        })
        db.commit()
    except (ValueError, TypeError) as exc:
        db.rollback()
        set_flash(request, str(exc), "danger")
        return redirect("/promociones")
    set_flash(request, f"Promoción registrada para {len(batch.records)} estudiantes. Lote #{batch.id}.")
    return redirect(f"/promociones?source_year_id={source_year.id}&target_year_id={target_year.id}&source_section_id={source_section.id}")


@app.get("/reportes/consolidado", response_class=HTMLResponse)
def consolidated_report(
    request: Request,
    school_year_id: int,
    section_id: int,
    subject_id: int,
    scope: str = "period",
    period: str | None = None,
    db: Session = Depends(get_db),
):
    user = require_user(request, db)
    year = db.get(SchoolYear, school_year_id)
    section = db.get(Section, section_id)
    subject = db.get(Subject, subject_id)
    if not year or not section or not subject:
        raise HTTPException(404, "Parámetros del reporte no encontrados")
    probe = Evaluation(section_id=section.id, subject_id=subject.id, teacher_id=user.id, title="Reporte")
    if not can_manage_evaluation(user, probe, db):
        raise HTTPException(403, "No autorizado para consultar este reporte")
    try:
        report = build_grade_report(db, school_year=year, section=section, subject=subject, scope=scope, period=period)
    except ValueError as exc:
        raise HTTPException(400, str(exc))
    audit(db, request, user, "consolidated_report_viewed", "school_year", year.id, {
        "scope": scope, "period": period, "section_id": section.id, "subject_id": subject.id,
    })
    db.commit()
    return render(request, "report_consolidated.html", {"user": user, "report": report})


@app.get("/reportes/consolidado.csv")
def consolidated_report_csv(
    request: Request,
    school_year_id: int,
    section_id: int,
    subject_id: int,
    scope: str = "period",
    period: str | None = None,
    db: Session = Depends(get_db),
):
    user = require_user(request, db)
    year = db.get(SchoolYear, school_year_id)
    section = db.get(Section, section_id)
    subject = db.get(Subject, subject_id)
    if not year or not section or not subject:
        raise HTTPException(404, "Parámetros del reporte no encontrados")
    probe = Evaluation(section_id=section.id, subject_id=subject.id, teacher_id=user.id, title="Reporte")
    if not can_manage_evaluation(user, probe, db):
        raise HTTPException(403, "No autorizado para consultar este reporte")
    try:
        report = build_grade_report(db, school_year=year, section=section, subject=subject, scope=scope, period=period)
    except ValueError as exc:
        raise HTTPException(400, str(exc))
    out = io.StringIO()
    writer = csv.writer(out)
    writer.writerow([settings.institution_name, f"Código {settings.institution_code}"])
    writer.writerow(["Año escolar", year.name, "Sección", section.name, "Asignatura", subject.name])
    writer.writerow(["Tipo", "Reporte anual" if scope == "annual" else f"Reporte por lapso: {period}"])
    writer.writerow([])
    if scope == "period":
        writer.writerow(["N°", "Cédula", "Nombres y apellidos"] + [column["label"] for column in report["columns"]] + ["Promedio", "Condición"])
        for index, row in enumerate(report["rows"], 1):
            writer.writerow([row["student"].list_number or index, row["student"].ci, row["student"].full_name] + row["scores"] + [row["average"], row["status"]])
    else:
        writer.writerow(["N°", "Cédula", "Nombres y apellidos"] + list(ANNUAL_PERIODS) + ["Promedio anual", "Condición"])
        for index, row in enumerate(report["rows"], 1):
            writer.writerow([row["student"].list_number or index, row["student"].ci, row["student"].full_name] + [row["period_averages"].get(name) for name in ANNUAL_PERIODS] + [row["average"], row["status"]])
    filename = f"reporte-{scope}-{year.name}-{section.name}-{subject.name}.csv".replace(" ", "-")
    audit(db, request, user, "consolidated_report_exported", "school_year", year.id, {"format": "csv", "scope": scope})
    db.commit()
    return StreamingResponse(
        iter([out.getvalue().encode("utf-8-sig")]), media_type="text/csv; charset=utf-8",
        headers={"Content-Disposition": f"attachment; filename*=UTF-8''{quote(filename)}"},
    )


@app.get("/actas/articulo-112/{evaluation_id}", response_class=HTMLResponse)
def article112_act_page(evaluation_id: int, request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    evaluation = (
        db.query(Evaluation)
        .options(
            joinedload(Evaluation.section), joinedload(Evaluation.subject), joinedload(Evaluation.teacher),
            joinedload(Evaluation.school_year), joinedload(Evaluation.parent), joinedload(Evaluation.grades),
        )
        .filter(Evaluation.id == evaluation_id).first()
    )
    if not evaluation:
        raise HTTPException(404, "Evaluación no encontrada")
    original = original_article112_evaluation(evaluation)
    if not can_manage_evaluation(user, original, db) and user.role not in {"admin", "director", "evaluacion"}:
        raise HTTPException(403, "No autorizado")
    try:
        act = get_or_create_article112_act(db, original, user)
    except ValueError as exc:
        raise HTTPException(400, str(exc))
    result = calculate_article112(original, db)
    second = db.query(Evaluation).filter_by(parent_evaluation_id=original.id, is_article112=True).first()
    eligible_ids = [item.student_id for item in second.eligibilities] if second else result["failed_ids"]
    students = db.query(Student).filter(Student.id.in_(eligible_ids)).order_by(Student.full_name).all() if eligible_ids else []
    original_grades = {grade.student_id: grade for grade in original.grades}
    audit(db, request, user, "article112_act_viewed", "article112_act", act.id)
    db.commit()
    return render(request, "article112_act.html", {
        "user": user, "act": act, "evaluation": original, "second": second,
        "article112": result, "students": students, "original_grades": original_grades,
    })


@app.get("/auditoria", response_class=HTMLResponse)
def audit_page(request: Request, db: Session = Depends(get_db)):
    user = require_user(request, db)
    require_roles(user, "admin", "director", "evaluacion")
    logs = (
        db.query(AuditLog)
        .options(joinedload(AuditLog.user))
        .order_by(AuditLog.id.desc())
        .limit(300)
        .all()
    )
    return render(request, "audit.html", {"user": user, "logs": logs})