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