from datetime import datetime, timezone
from core.config import get_db
from core.security.exceptions import CoreDBError, GenericError
from models import WaitingList,Course
from schema.waiting_list import WaitingListResponseSchema


class WaitingListService:

    def save(self, request,user):
        with get_db() as db:
            payload = request.model_dump()
            payload["created_by"]= user['id']
            payload["user_type"]= user['user_type']
            try:
                new_waiting_list = WaitingList(**payload)
                db.add(new_waiting_list)
                db.commit()
                db.refresh(new_waiting_list)
                return new_waiting_list
            except Exception as e:
                db.rollback()
                raise CoreDBError(f"Could not create new waiting list: {e}")

    def fetch_filtered_waiting_list(self, filters):
        
        with get_db() as db:
            query = db.query(WaitingList).where(WaitingList.deleted_at.is_(None))
            
            if filters.academic_year_id:
                query = query.join(WaitingList.course).filter(
                    Course.academic_year_id == filters.academic_year_id
                )
            if filters.term_id:
                query = query.join(WaitingList.course).filter(
                    Course.term_id == filters.term_id
                )
                
            records = query.all()
            data = []
            for row in records:
                data.append({
                    "id": row.id,
                    "course": {
                        "course_id": row.course_id,
                        "course_name": row.course.name,
                        "academic_year_id": row.course.academic_year_id,
                        "term_id": row.course.term_id,
                        "course_start_date": row.course.start_date,
                        "course_end_date": row.course.end_date,
                        "course_start_time": row.course.start_time,
                        "course_end_time": row.course.end_time,
                        "term": row.course.term.name,
                        "academic_year": row.course.academic_year.title,
                        "course_category": row.course.course_category.title,
                        "age_group": row.course.age_group.label,
                        "time_slot": row.course.time_slot.title
                    },
                    "participant": {
                        "participant_id": row.participant_id,
                        "participant_first_name": row.participant.first_name,
                        "participant_last_name": row.participant.last_name,
                        "participant_email": row.participant.email,
                       
                    },
                    "primary_contact_number": row.primary_contact_number,
                    "remarks": row.remarks,
                    "is_enrolled": row.is_enrolled
                })
            return data

    def waiting_list_detail(self, waiting_list_id: int):
        with get_db() as db:
            row = db.query(WaitingList).where(
                WaitingList.id == waiting_list_id,
                WaitingList.deleted_at.is_(None)
            ).first()
            if not row:
                raise GenericError(status_code=404, exc="Waiting list not found")
            return {
                    "id": row.id,
                    "course": {
                        "course_id": row.course_id,
                        "course_name": row.course.name,
                        "course_start_date": row.course.start_date,
                        "course_end_date": row.course.end_date,
                        "course_start_time": row.course.start_time,
                        "course_end_time": row.course.end_time,
                        "term": row.course.term.name,
                        "academic_year": row.course.academic_year.title,
                        "course_category": row.course.course_category.title,
                        "age_group": row.course.age_group.label,
                        "time_slot": row.course.time_slot.title
                    },
                    "participant": {
                        "participant_id": row.participant_id,
                        "participant_first_name": row.participant.first_name,
                        "participant_last_name": row.participant.last_name,
                        "participant_email": row.participant.email,
                       
                    },
                    "primary_contact_number": row.primary_contact_number,
                    "remarks": row.remarks,
                    "is_enrolled": row.is_enrolled,
                   
                    
                }



    def delete_waiting_list(self, waiting_list_id):
        with get_db() as db:
            waiting_list = db.query(WaitingList).where(WaitingList.id == waiting_list_id, WaitingList.deleted_at.is_(None)).first()

            if not waiting_list:
                raise GenericError(status_code=404, exc="Waiting list not found")

            waiting_list.deleted_at = datetime.now(timezone.utc)
            db.commit()
            return {"message": "Waiting list deleted successfully"}

    def update_waiting_list(self, waiting_list_id: int, request):
        with get_db() as db:
            waiting_list = db.query(WaitingList).where(
                WaitingList.id == waiting_list_id,
                WaitingList.deleted_at.is_(None)
            ).first()

            if not waiting_list:
                raise GenericError(status_code=404, exc="Waiting list not found")

            if request.remarks is not None:
                waiting_list.remarks = request.remarks
            if request.is_enrolled is not None:
                waiting_list.is_enrolled = request.is_enrolled
            
            db.commit()
            db.refresh(waiting_list)
            return waiting_list