from fastapi import APIRouter, Depends, HTTPException, status, Query from sqlalchemy.orm import Session, joinedload from typing import Optional, List from collections import defaultdict import io import csv from fastapi import UploadFile, File from fastapi.responses import StreamingResponse from app.core.database import get_db from app.dependencies.auth import get_current_user from app.middleware.rbac import RBAC from app.schemas.common import PaginatedResponse from app.schemas.section import SectionCreate, SectionUpdate, SectionResponse, SectionTeacherResponse from app.models.user import User from app.models.section import Section from app.models.subject import Subject from app.models.teacher_section import TeacherSection from app.models.section_subject import SectionSubject from app.models.enrollment import Enrollment from app.models.section_schedule import SectionSchedule from app.schemas.section_schedule import SectionScheduleResponse, SectionSchedulesBulkUpdate from sqlalchemy import or_ from app.utils.csv_import import generate_csv_template, parse_csv_upload, process_csv_rows router = APIRouter(prefix="/sections", tags=["sections"]) def _get_section_subjects(section: Section, db: Session): ssl = db.query(SectionSubject).filter(SectionSubject.section_id == section.id).all() result = [] for ss in ssl: sub = db.query(Subject).filter(Subject.id == ss.subject_id).first() from app.schemas.section import SubjectRef result.append(SubjectRef(subject_id=ss.subject_id, subject_name=sub.name if sub else None)) return result def _base_query(current_user: User, db: Session): query = db.query(Section).filter(Section.account_id == current_user.account_id) if current_user.role == "teacher": subq = db.query(TeacherSection.section_id).filter(TeacherSection.teacher_id == current_user.id).subquery() query = query.filter(Section.id.in_(subq)) return query @router.get("", response_model=PaginatedResponse[SectionResponse]) def list_sections( page: int = Query(1, ge=1), per_page: int = Query(20, ge=1, le=500), search: Optional[str] = None, subject_id: Optional[int] = None, level: Optional[str] = None, academic_period: Optional[str] = None, student_name: Optional[str] = None, teacher_name: Optional[str] = None, current_user: User = Depends(get_current_user), db: Session = Depends(get_db), ): query = _base_query(current_user, db) if search: pattern = f"%{search}%" query = query.filter(or_(Section.name.like(pattern), Section.level.like(pattern), Section.academic_period.like(pattern))) if subject_id: query = query.filter(Section.subject_id == subject_id) if level: query = query.filter(Section.level == level) if academic_period: query = query.filter(Section.academic_period == academic_period) if student_name: pattern = f"%{student_name}%" subq = db.query(Enrollment.section_id).filter( Enrollment.account_id == current_user.account_id, or_(User.name.like(pattern), User.lastname.like(pattern)), ).join(User, User.id == Enrollment.student_id).subquery() query = query.filter(Section.id.in_(subq)) if teacher_name: pattern = f"%{teacher_name}%" subq1 = db.query(TeacherSection.section_id).filter( TeacherSection.account_id == current_user.account_id, or_(User.name.like(pattern), User.lastname.like(pattern)), ).join(User, User.id == TeacherSection.teacher_id).subquery() query = query.filter(Section.id.in_(subq1)) total = query.count() items = query.order_by(Section.name).offset((page - 1) * per_page).limit(per_page).all() # Fix 1.1: batch-load SectionSubjects and Subjects for all sections at once from app.schemas.section import SubjectRef section_ids = [s.id for s in items] if section_ids: all_ss = db.query(SectionSubject).filter(SectionSubject.section_id.in_(section_ids)).all() all_subject_ids = {ss.subject_id for ss in all_ss} subjects_by_id = ( {s.id: s for s in db.query(Subject).filter(Subject.id.in_(all_subject_ids)).all()} if all_subject_ids else {} ) ss_by_section: dict = defaultdict(list) for ss in all_ss: ss_by_section[ss.section_id].append(ss) else: subjects_by_id = {} ss_by_section = defaultdict(list) result = [] for s in items: sr = SectionResponse.model_validate(s) sr.subjects = [ SubjectRef( subject_id=ss.subject_id, subject_name=subjects_by_id[ss.subject_id].name if ss.subject_id in subjects_by_id else None, ) for ss in ss_by_section[s.id] ] result.append(sr) return PaginatedResponse( items=result, total=total, page=page, per_page=per_page, total_pages=(total + per_page - 1) // per_page, ) SECTION_TEMPLATE_HEADERS = ["nombre", "nivel", "periodo_académico", "máximo_estudiantes"] @router.get("/template") def download_section_template(): csv_content = generate_csv_template(SECTION_TEMPLATE_HEADERS) return StreamingResponse( iter([csv_content]), media_type="text/csv", headers={"Content-Disposition": "attachment; filename=plantilla_secciones.csv"}, ) @router.post("/bulk") def bulk_create_sections( file: UploadFile = File(...), current_user: User = Depends(RBAC(["admin"])), db: Session = Depends(get_db), ): content = file.file.read().decode("utf-8-sig") rows = parse_csv_upload(content, ["nombre", "periodo_académico"]) seen_names = set() def process(row: dict, line: int): max_students = row.get("máximo_estudiantes", "").strip() try: max_students = int(max_students) if max_students else 40 except ValueError: return "máximo_estudiantes debe ser un número entero" section_name = row.get("nombre", "").strip() if section_name in seen_names: return "updated" seen_names.add(section_name) level = row.get("nivel", "").strip().lower() or None academic_period = row.get("periodo_académico", "").strip() existing = db.query(Section).filter( Section.name == section_name, Section.account_id == current_user.account_id, ).first() if existing: existing.level = level existing.academic_period = academic_period existing.max_students = max_students db.add(existing) return "updated" sec = Section( account_id=current_user.account_id, name=section_name, level=level, academic_period=academic_period, max_students=max_students, ) db.add(sec) return "created" result = process_csv_rows(rows, process) db.commit() return result @router.get("/{section_id}", response_model=SectionResponse) def get_section(section_id: int, current_user: User = Depends(get_current_user), db: Session = Depends(get_db)): obj = _base_query(current_user, db).filter(Section.id == section_id).first() if not obj: raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Section not found") sr = SectionResponse.model_validate(obj) sr.subjects = _get_section_subjects(obj, db) return sr @router.post("", response_model=SectionResponse, status_code=status.HTTP_201_CREATED) def create_section(payload: SectionCreate, current_user: User = Depends(RBAC(["admin"])), db: Session = Depends(get_db)): data = payload.model_dump(exclude={"subject_ids"}) subject_ids = payload.subject_ids or [] if not subject_ids: raise HTTPException(status_code=400, detail="At least one subject_id is required") # use first subject_id as the section's primary subject_id for backward compat data["subject_id"] = subject_ids[0] if "level" in data and data["level"] is None: del data["level"] obj = Section(account_id=current_user.account_id, **data) db.add(obj); db.flush() subject_ids = payload.subject_ids if subject_ids: # ensure subject_ids exist existing = {s.id for s in db.query(Subject).filter( Subject.id.in_(subject_ids), Subject.account_id == current_user.account_id ).all()} for sid in subject_ids: if sid in existing: db.add(SectionSubject( section_id=obj.id, subject_id=sid, account_id=current_user.account_id )) db.commit(); db.refresh(obj) sr = SectionResponse.model_validate(obj) sr.subjects = _get_section_subjects(obj, db) return sr @router.put("/{section_id}", response_model=SectionResponse) def update_section(section_id: int, payload: SectionUpdate, current_user: User = Depends(RBAC(["admin"])), db: Session = Depends(get_db)): obj = db.query(Section).filter(Section.id == section_id, Section.account_id == current_user.account_id).first() if not obj: raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Section not found") for f, v in payload.model_dump(exclude_unset=True).items(): setattr(obj, f, v) db.commit(); db.refresh(obj) sr = SectionResponse.model_validate(obj) sr.subjects = _get_section_subjects(obj, db) return sr @router.get("/{section_id}/teachers", response_model=List[SectionTeacherResponse]) def list_section_teachers(section_id: int, current_user: User = Depends(get_current_user), db: Session = Depends(get_db)): sec = db.query(Section).filter(Section.id == section_id, Section.account_id == current_user.account_id).first() if not sec: raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Section not found") ts_list = db.query(TeacherSection).filter( TeacherSection.section_id == section_id, TeacherSection.account_id == current_user.account_id, ).all() # Fix 1.2: batch-load Users and Subjects for all TeacherSection rows at once teacher_ids = {ts.teacher_id for ts in ts_list} ts_subject_ids = {ts.subject_id for ts in ts_list if ts.subject_id is not None} users_by_id = ( {u.id: u for u in db.query(User).filter(User.id.in_(teacher_ids)).all()} if teacher_ids else {} ) subjects_by_id = ( {s.id: s for s in db.query(Subject).filter(Subject.id.in_(ts_subject_ids)).all()} if ts_subject_ids else {} ) result = [] for ts in ts_list: teacher = users_by_id.get(ts.teacher_id) sub = subjects_by_id.get(ts.subject_id) if ts.subject_id is not None else None if teacher: result.append(SectionTeacherResponse( teacher_id=ts.teacher_id, teacher_name=f"{teacher.lastname}, {teacher.name}", subject_id=ts.subject_id, subject_name=sub.name if sub else None, )) return result @router.delete("/{section_id}/teachers/{teacher_id}", status_code=status.HTTP_200_OK) def remove_teacher_from_section(section_id: int, teacher_id: int, current_user: User = Depends(RBAC(["admin"])), db: Session = Depends(get_db)): deleted = db.query(TeacherSection).filter( TeacherSection.section_id == section_id, TeacherSection.teacher_id == teacher_id, TeacherSection.account_id == current_user.account_id, ).delete(synchronize_session=False) db.commit() return {"removed": deleted} @router.delete("/{section_id}", status_code=status.HTTP_204_NO_CONTENT) def delete_section(section_id: int, current_user: User = Depends(RBAC(["admin"])), db: Session = Depends(get_db)): obj = db.query(Section).filter(Section.id == section_id, Section.account_id == current_user.account_id).first() if not obj: raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Section not found") db.delete(obj); db.commit() @router.get("/{section_id}/schedules", response_model=List[SectionScheduleResponse]) def list_section_schedules( section_id: int, current_user: User = Depends(get_current_user), db: Session = Depends(get_db), ): sec = db.query(Section).filter(Section.id == section_id, Section.account_id == current_user.account_id).first() if not sec: raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Section not found") schedules = db.query(SectionSchedule).filter( SectionSchedule.section_id == section_id, SectionSchedule.account_id == current_user.account_id, ).order_by(SectionSchedule.day_of_week, SectionSchedule.entry_time).all() # Batch-load Subjects for all schedules sched_subject_ids = {s.subject_id for s in schedules if s.subject_id is not None} subjects_by_id = ( {s.id: s for s in db.query(Subject).filter(Subject.id.in_(sched_subject_ids)).all()} if sched_subject_ids else {} ) # Batch-load TeacherSection for this section to find teacher per subject, # then batch-load the Users for those teachers. teacher_assignments = ( db.query(TeacherSection).filter( TeacherSection.section_id == section_id, TeacherSection.account_id == current_user.account_id, TeacherSection.subject_id.in_(sched_subject_ids), ).all() if sched_subject_ids else [] ) teacher_ids = {ta.teacher_id for ta in teacher_assignments} teachers_by_id = ( {u.id: u for u in db.query(User).filter(User.id.in_(teacher_ids)).all()} if teacher_ids else {} ) teacher_by_subject = { ta.subject_id: teachers_by_id.get(ta.teacher_id) for ta in teacher_assignments if ta.subject_id is not None } result = [] for s in schedules: sr = SectionScheduleResponse.model_validate(s) if s.subject_id: sub = subjects_by_id.get(s.subject_id) sr.subject_name = sub.name if sub else None teacher = teacher_by_subject.get(s.subject_id) if teacher: sr.teacher_id = teacher.id sr.teacher_name = f"{teacher.name} {teacher.lastname}" result.append(sr) return result @router.put("/{section_id}/schedules", response_model=List[SectionScheduleResponse]) def update_section_schedules( section_id: int, payload: SectionSchedulesBulkUpdate, current_user: User = Depends(RBAC(["admin"])), db: Session = Depends(get_db), ): sec = db.query(Section).filter(Section.id == section_id, Section.account_id == current_user.account_id).first() if not sec: raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Section not found") db.query(SectionSchedule).filter( SectionSchedule.section_id == section_id, SectionSchedule.account_id == current_user.account_id, ).delete() # Batch-load Subjects for all incoming schedule rows at once payload_subject_ids = {s.subject_id for s in payload.schedules if s.subject_id is not None} subjects_by_id = ( {s.id: s for s in db.query(Subject).filter(Subject.id.in_(payload_subject_ids)).all()} if payload_subject_ids else {} ) # Batch-load TeacherSection for this section to populate teacher info per subject teacher_assignments = ( db.query(TeacherSection).filter( TeacherSection.section_id == section_id, TeacherSection.account_id == current_user.account_id, TeacherSection.subject_id.in_(payload_subject_ids), ).all() if payload_subject_ids else [] ) teacher_ids = {ta.teacher_id for ta in teacher_assignments} teachers_by_id = ( {u.id: u for u in db.query(User).filter(User.id.in_(teacher_ids)).all()} if teacher_ids else {} ) teacher_by_subject = { ta.subject_id: teachers_by_id.get(ta.teacher_id) for ta in teacher_assignments if ta.subject_id is not None } result = [] for s in payload.schedules: obj = SectionSchedule( section_id=section_id, account_id=current_user.account_id, day_of_week=s.day_of_week, entry_time=s.entry_time, exit_time=s.exit_time, subject_id=s.subject_id, ) db.add(obj) db.flush() sr = SectionScheduleResponse.model_validate(obj) if s.subject_id: sub = subjects_by_id.get(s.subject_id) sr.subject_name = sub.name if sub else None teacher = teacher_by_subject.get(s.subject_id) if teacher: sr.teacher_id = teacher.id sr.teacher_name = f"{teacher.name} {teacher.lastname}" result.append(sr) db.commit() return result