talos-access / backend /app /api /v1 /endpoints /representative.py
Greg
Claude Opus 4.7 (1M context)
perf(representative): eliminate 1+6N in /children with full batch preload
dcc7429
Raw History Blame Contribute Delete
6.99 kB
from fastapi import APIRouter, Depends
from sqlalchemy.orm import Session
from sqlalchemy import cast, Date, func, or_, and_
from datetime import date, timedelta, datetime
from collections import defaultdict
from app.core.database import get_db
from app.dependencies.auth import get_current_user
from app.middleware.rbac import RBAC
from app.models.user import User
from app.models.enrollment import Enrollment
from app.models.attendance import Attendance
from app.models.entrance_log import EntranceLog
from app.models.section import Section
from app.models.section_schedule import SectionSchedule
router = APIRouter(prefix="/representative", tags=["representative"])
WEEKDAY_NAMES = ["Lunes", "Martes", "Miércoles", "Jueves", "Viernes", "Sábado", "Domingo"]
@router.get("/children")
def my_children(
current_user: User = Depends(RBAC(["representative"])),
db: Session = Depends(get_db),
):
children = db.query(User).filter(
User.parent_id == current_user.id,
User.account_id == current_user.account_id,
User.is_active == "1",
).all()
today = date.today()
today_dow = today.weekday()
thirty_days_ago = today - timedelta(days=30)
if not children:
return []
child_ids = [c.id for c in children]
# Fix 3: batch-load everything upfront — zero extra queries per child
# 1. Enrollments
enrollments_by_child = {
e.student_id: e
for e in db.query(Enrollment).filter(
Enrollment.student_id.in_(child_ids),
Enrollment.account_id == current_user.account_id,
).all()
}
# 2. Sections for those enrollments
section_ids = {e.section_id for e in enrollments_by_child.values() if e.section_id is not None}
sections_by_id = (
{s.id: s for s in db.query(Section).filter(Section.id.in_(section_ids)).all()}
if section_ids else {}
)
# 3. Today's schedules for those sections
schedules_by_section: dict = defaultdict(list)
if section_ids:
for sched in db.query(SectionSchedule).filter(
SectionSchedule.section_id.in_(section_ids),
SectionSchedule.account_id == current_user.account_id,
SectionSchedule.day_of_week == today_dow,
).order_by(SectionSchedule.entry_time).all():
schedules_by_section[sched.section_id].append(sched)
# 4. Today's class attendance (Attendance table) — latest "in" per child
today_in_by_child = {}
for att in db.query(Attendance).filter(
Attendance.user_id.in_(child_ids),
Attendance.account_id == current_user.account_id,
cast(Attendance.time_log, Date) == today,
or_(
Attendance.action == "in",
Attendance.action.is_(None),
),
).order_by(Attendance.time_log.asc()).all():
# keep latest (loop ascending, overwrite = last wins)
today_in_by_child[att.user_id] = att
# 5. Total attendance count per child (last 30 days)
count_rows = db.query(
Attendance.user_id,
func.count(Attendance.id).label("cnt"),
).filter(
Attendance.user_id.in_(child_ids),
Attendance.account_id == current_user.account_id,
cast(Attendance.time_log, Date) >= thirty_days_ago,
cast(Attendance.time_log, Date) <= today,
or_(
Attendance.action == "in",
and_(Attendance.action.is_(None), Attendance.checkout_time.is_(None)),
),
).group_by(Attendance.user_id).all()
total_att_by_child = {row.user_id: row.cnt for row in count_rows}
# 6. Latest EntranceLog per child for today (campus gate)
gate_by_child = {}
for gate in db.query(EntranceLog).filter(
EntranceLog.user_id.in_(child_ids),
EntranceLog.account_id == current_user.account_id,
EntranceLog.date == today,
).order_by(EntranceLog.checkin_time.asc()).all():
# keep latest (loop ascending, overwrite = last wins)
gate_by_child[gate.user_id] = gate
# Build response using only dict lookups
now_minutes = datetime.now().hour * 60 + datetime.now().minute
result = []
for child in children:
enrollment = enrollments_by_child.get(child.id)
section_name = None
section_id = None
today_schedule = None
if enrollment:
sec = sections_by_id.get(enrollment.section_id) if enrollment.section_id is not None else None
if sec:
section_name = sec.name
section_id = sec.id
day_schedules = schedules_by_section[sec.id]
if day_schedules:
blocks = []
for s in day_schedules:
entry_min = int(s.entry_time.split(":")[0]) * 60 + int(s.entry_time.split(":")[1])
exit_min = int(s.exit_time.split(":")[0]) * 60 + int(s.exit_time.split(":")[1])
if now_minutes >= entry_min and now_minutes < exit_min:
status = "active"
elif now_minutes < entry_min:
status = "upcoming"
else:
status = "passed"
blocks.append({
"entry_time": s.entry_time,
"exit_time": s.exit_time,
"subject_id": s.subject_id,
"status": status,
})
today_schedule = {"day": today_dow, "blocks": blocks}
today_in = today_in_by_child.get(child.id)
is_present = False
checkin_time = None
if today_in:
if today_in.action == "in" or (today_in.action is None and today_in.checkout_time is None):
is_present = True
checkin_time = today_in.time_log.isoformat() if today_in.time_log else None
total_att = total_att_by_child.get(child.id, 0)
gate = gate_by_child.get(child.id)
entered_campus = gate is not None
campus_checkin_time = gate.checkin_time.isoformat() if gate and gate.checkin_time else None
campus_checkout_time = gate.checkout_time.isoformat() if gate and gate.checkout_time else None
skipped_class = entered_campus and not is_present
result.append({
"id": child.id,
"name": child.name,
"lastname": child.lastname,
"card_id": child.card_id,
"section_name": section_name,
"section_id": section_id,
"entered_campus_today": entered_campus,
"campus_checkin_time": campus_checkin_time,
"campus_checkout_time": campus_checkout_time,
"is_present_today": is_present,
"checkin_time": checkin_time,
"skipped_class": skipped_class,
"attendance_last_30_days": total_att,
"today_schedule": today_schedule,
})
return result