from datetime import datetime
from io import BytesIO

import pandas as pd
from sqlalchemy import extract

from core.config import get_db
from models import Course, Attendance, Participant, PaymentDetails, Payment


class ReportService:

    def fetch_attendance_report(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).join(Course,
                                                                                        Attendance.course_id == Course.id)

            if request.end_date and request.start_date:
                query = query.filter(Attendance.attendance_date >= request.start_date,
                                     Attendance.attendance_date <= request.end_date)
            elif request.start_date:
                query = query.filter(Attendance.attendance_date == request.start_date)

            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 = []
            field_mapping = {
                    "attendance_id": lambda row: row.id,
                    "course_name": lambda row: row.course.name,
                    "participant_first_name": lambda row: row.participant.first_name,
                    "participant_last_name": lambda row: row.participant.last_name,
                    "age_group": lambda row: row.course.age_group.label,
                    "attendance_date": lambda row: row.attendance_date,
                    "tutor_name": lambda row: row.staff.first_name,
                    "attendance_status": lambda row: row.attendance_status,
                }

            for row in query.all():

                # Define the mapping of field names to value extractors
                
                # Determine if all fields should be returned
                return_all = "*" in request.fields

                row_data = {}

                for field, extractor in field_mapping.items():
                    if return_all or field in request.fields:
                        try:
                            row_data[field] = extractor(row)
                        except AttributeError:
                            row_data[field] = None  # Handle missing relationships gracefully

                data.append(row_data)

        return {"data": data, "fields": list(field_mapping.keys())}

    def view_attendance_report(self, request, user):
        return self.fetch_attendance_report(request, user)

    def view_attendance_report_download(self, request, user):
        df = pd.DataFrame(self.fetch_attendance_report(request, user)["data"])
        output = BytesIO()
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
            df.to_excel(writer, index=False, sheet_name='Attendance')
        output.seek(0)
        return output

    def fetch_student_data(self, request, user):

        with get_db() as db:
            query = db.query(Participant)
            if request.gender:
                query = query.filter(Participant.gender == request.gender)
            if request.age:
                current_year = datetime.now().year
                target_dob_year = current_year - request.age
                query = query.filter(
                    extract('year', Participant.dob) == target_dob_year
                )
            if request.is_participated_in_wac:
                query = query.filter(Participant.is_participated_in_wac == request.is_participated_in_wac)
            if request.photography_consent:
                query = query.filter(Participant.photography_consent == request.photography_consent)

            # Define the field-to-extractor mapping
            field_mapping = {
                "participant_first_name": lambda row: row.first_name,
                "participant_dob": lambda row: row.dob,
                "participant_middle_name": lambda row: row.middle_name,
                "participant_last_name": lambda row: row.last_name,
                "parent_first_name": lambda row: row.parent.first_name if row.parent else None,
                "parent_last_name": lambda row: row.parent.last_name if row.parent else None,
                "parent_email": lambda row: row.parent.email if row.parent else None,
                "parent_mobile_number": lambda row: row.parent.mobile_number if row.parent else None,
                "gender": lambda row: row.gender,
                "is_participated_in_wac": lambda row: row.is_participated_in_wac,
                "photography_consent": lambda row: row.photography_consent,
            }

            data = []

            # Loop through the rows and extract the data based on the field mapping
            for row in query.all():
                row_data = {}

                for field, extractor in field_mapping.items():
                    # Only include the field in the row_data if it is in the requested fields (if `*` is not present)
                    if "*" in request.fields or field in request.fields:
                        try:
                            row_data[field] = extractor(row)
                        except AttributeError:
                            row_data[field] = None  # Gracefully handle missing attributes

                data.append(row_data)

            return {"data": data, "fields": list(field_mapping.keys())}

    def view_student_data(self, request, user):
        return self.fetch_student_data(request, user)

    def view_student_data_download(self, request, user):
        df = pd.DataFrame(self.fetch_student_data(request, user)["data"])
        output = BytesIO()
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
            df.to_excel(writer, index=False, sheet_name='Student')
        output.seek(0)
        return output

    def fetch_enrolled_data(self, request, user):

        with get_db() as db:
            query = db.query(Participant).join(PaymentDetails, PaymentDetails.purchased_for_id == Participant.id).join(Course,PaymentDetails.course_id == Course.id).join(Payment,PaymentDetails.payment_id == Payment.id).filter(Payment.is_payment_completed == True)
            if request.course_id:
                query = query.filter(PaymentDetails.course_id == request.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.age_group_id:
                query = query.filter(Course.age_group_id == request.age_group_id)
            if request.course_category_id:
                query = query.filter(Course.course_category_id == request.course_category_id)

            # Define the field-to-extractor mapping
            field_mapping = {
                "participant_id": lambda row: row.id,
                "participant_first_name": lambda row: row.first_name,
                "participant_email": lambda row: row.email,
                "participant_gender": lambda row: row.gender,
                "participant_dob": lambda row: row.dob,
                "participant_middle_name": lambda row: row.middle_name,
                "participant_last_name": lambda row: row.last_name,
                "parent_first_name": lambda row: row.parent.first_name if row.parent else None,
                "parent_last_name": lambda row: row.parent.last_name if row.parent else None,
                "parent_email": lambda row: row.parent.email if row.parent else None,
                "parent_mobile_number": lambda row: row.parent.mobile_number if row.parent else None,
                "gender": lambda row: row.gender,
                "is_participated_in_wac": lambda row: row.is_participated_in_wac,
                "photography_consent": lambda row: row.photography_consent,
            }

            data = []

            # Loop through the rows and extract the data based on the field mapping
            for row in query.all():
                row_data = {}

                for field, extractor in field_mapping.items():
                    # Only include the field in the row_data if it is in the requested fields (if `*` is not present)
                    if "*" in request.fields or field in request.fields:
                        try:
                            row_data[field] = extractor(row)
                        except AttributeError:
                            row_data[field] = None  # Gracefully handle missing attributes
                if len(row_data) > 0:
                    data.append(row_data)

            return {"data": data, "fields": list(field_mapping.keys())}

    def view_enrolled_download(self, request, user):
        df = pd.DataFrame(self.fetch_enrolled_data(request, user)["data"])
        output = BytesIO()
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
            df.to_excel(writer, index=False, sheet_name='Student')
        output.seek(0)
        return output