from core.config import get_db
from core.security.exceptions import CoreDBError
from models import Attendance, Course


class AttendanceService:

    def save(self, request):

        with get_db() as db:
            payload = request.model_dump()
            query = db.query(Attendance).where(Attendance.attendance_date == request.attendance_date,
                                               Attendance.staff_id == request.staff_id,
                                               Attendance.participant_id == request.participant_id,
                                               Attendance.course_id == request.course_id,
                                               Attendance.deleted_at.is_(None))
            if query.count()>0:
                instance=query.first()
                result = (
                    db.query(Attendance)
                    .filter(Attendance.id == instance.id, Attendance.deleted_at.is_(None))
                    .update(payload, synchronize_session="fetch")
                )
                db.commit()

            else:
                try:
                    new_attendance = Attendance(**payload)
                    db.add(new_attendance)
                    db.commit()
                    db.refresh(new_attendance)
                    return new_attendance
                except Exception as e:
                    db.rollback()
                    raise CoreDBError(f"Could not create new attendance: {e}")

    def view_attendance_from_staff(self, request, user):
        with get_db() as db:
            query = db.query(Attendance).filter(Attendance.course_id == request.course_id,Attendance.staff_id == request.staff_id)
            if request.attendance_date:
                query = query.filter(Attendance.attendance_date == request.attendance_date)

            data = []
            for row in query.all():
                data.append({
                    "attendance_id":row.id,
                    "participant_id":row.participant_id,
                    "course_id": row.course_id,
                    "staff_id": row.staff_id,
                    "attendance_status": row.attendance_status,
                    "remarks": row.remarks,
                    "attendance_date": row.attendance_date,
                    "modified_by_admin": row.modified_by_admin,
                    "staff":dict(first_name=row.staff.first_name,last_name=row.staff.last_name),
                    "course": dict(name=row.course.name, id=row.course.id,age_group=row.course.age_group.label),
                    "participant": dict(first_name=row.participant.first_name, id=row.participant.id,
                                        last_name=row.participant.last_name)

                })

        return data

    def view_attendance_from_parent(self, request, user):
        with get_db() as db:
            query = db.query(Attendance).filter(Attendance.participant_id == request.participant_id).join(Course,Attendance.course_id == Course.id)
            if request.academic_year_id:
                query = query.filter(Course.academic_year_id == request.academic_year_id)

            if request.term_id:
                query = query.filter(Course.term_id == request.term_id)

            if request.course_id:
                query = query.filter(Course.id== request.course_id)

            if request.attendance_date:
                query = query.filter(Attendance.attendance_date == request.attendance_date)

            data = []
            for row in query.all():

                data.append({
                    "attendance_id": row.id,
                    "participant_id":row.participant_id,
                    "course_id": row.course_id,
                    "staff_id": row.staff_id,
                    "attendance_status": row.attendance_status,
                    "remarks": row.remarks,
                    "attendance_date": row.attendance_date,
                    "modified_by_admin": row.modified_by_admin,
                    "staff":dict(first_name=row.staff.first_name,last_name=row.staff.last_name),
                    "course": dict(name=row.course.name, id=row.course.id,age_group=row.course.age_group.label),
                    "participant": dict(first_name=row.participant.first_name, id=row.participant.id,
                                        last_name=row.participant.last_name)

                })

        return data

    def view_attendance_from_admin(self, request, user):
        with get_db() as db:
            query = db.query(Attendance).filter(Course.academic_year_id == request.academic_year_id,Course.term_id == request.term_id,Attendance.attendance_date == request.attendance_date).join(Course,Attendance.course_id == Course.id)
            if request.course_category_id:
                query = query.filter(Course.course_category_id == request.course_category_id)

            if request.course_id:
                query = query.filter(Course.id== request.course_id)

            data = []
            for row in query.all():

                data.append({
                    "attendance_id": row.id,
                    "participant_id":row.participant_id,
                    "course_id": row.course_id,
                    "staff_id": row.staff_id,
                    "attendance_status": row.attendance_status,
                    "remarks": row.remarks,
                    "attendance_date": row.attendance_date,
                    "modified_by_admin": row.modified_by_admin,
                    "staff":dict(first_name=row.staff.first_name,last_name=row.staff.last_name),
                    "course": dict(name=row.course.name, id=row.course.id,age_group=row.course.age_group.label),
                    "participant": dict(first_name=row.participant.first_name, id=row.participant.id,
                                        last_name=row.participant.last_name)

                })

        return data

    def update_attendance_from_admin(self, request,user):

        with get_db() as db:
            payload = request.model_dump(exclude=["attendance_id"])
            payload["modified_by_admin"]=True
            # query = db.query(Attendance).get(request.attendance_id)
            result = (
                    db.query(Attendance)
                    .filter(Attendance.id == request.attendance_id, Attendance.deleted_at.is_(None))
                    .update(payload, synchronize_session="fetch")
                )
            db.commit()