talos-access / backend /app /api /v1 /endpoints /sections.py
Greg
Claude Opus 4.7 (1M context)
feat(schedule): spinner, filtered subjects, and teacher name in modal
fc9e304
Raw History Blame Contribute Delete
16.9 kB
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