import { prisma } from "../../config/db.ts";
import type { Request, Response } from "express";
import asyncHandler from "express-async-handler";
import PDFDocument from "pdfkit";
import dayjs from "dayjs";
import fs from "fs";
import path from "path";

// Helpers
const formatDateShort = (date: Date): string => {
    return date.toLocaleDateString('en-US', { month: 'short', day: 'numeric' });
};

const getWeekRangeLabel = (weekIndex: number, fromDate: Date): string => {
    const startOfWeek = new Date(fromDate);
    startOfWeek.setDate(fromDate.getDate() + (weekIndex * 7));

    const endOfWeek = new Date(startOfWeek);
    endOfWeek.setDate(startOfWeek.getDate() + 6);

    return `From ${formatDateShort(startOfWeek)} to ${formatDateShort(endOfWeek)}`;
};

const getCompactWeekRangeLabel = (start: Date, end: Date): string => {
    const startMonth = start.toLocaleDateString('en-US', { month: 'short' });
    const endMonth = end.toLocaleDateString('en-US', { month: 'short' });
    const endMonthLong = end.toLocaleDateString('en-US', { month: 'long' });
    const startDay = start.getDate();
    const endDay = end.getDate();

    if (startMonth === endMonth) {
        return `${startMonth} ${startDay} - ${endDay}`;
    }

    return `${startMonth} ${startDay} - ${endMonthLong} ${endDay}`;
};

const getWeekLabelForDate = (createdDate: Date, fromDate: Date): string => {
    const diffTime = Math.abs(createdDate.getTime() - fromDate.getTime());
    const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
    const weekIndex = Math.min(Math.ceil(diffDays / 7), 13) - 1;
    return getWeekRangeLabel(Math.max(0, weekIndex), fromDate);
};

// Practice Test

// All students practice test trend exam wise
export const getOverallPracticeWeeklyTrend = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const isAdmin = user.role === "ADMIN";
    const institutionId = isAdmin ? (req.query.institutionId as string | undefined) : user.institutionId;

    const examId = req.query.examId as string | undefined;

    let examName = "—";
    if (examId) {
        const exam = await prisma.exam.findUnique({
            where: { id: examId },
            select: { examName: true }
        });
        if (exam) {
            examName = exam.examName;
        }
    }

    const ninetyDaysAgo = new Date();
    ninetyDaysAgo.setDate(ninetyDaysAgo.getDate() - 90);
    ninetyDaysAgo.setHours(0, 0, 0, 0);

    const whereClause: any = {
        practiceTestId: { not: null },
        createdAt: { gte: ninetyDaysAgo }
    };

    if (examId) {
        whereClause.practiceTest = { examId };
    }

    if (institutionId) {
        whereClause.studentInstitution = {
            institutionId: institutionId
        };
    }

    const results = await prisma.examResult.findMany({
        where: whereClause,
        orderBy: { createdAt: 'asc' }
    });

    const weeklyDataMap: Record<string, any[]> = {};
    const orderedWeeks: string[] = [];
    for (let w = 0; w < 13; w++) {
        const label = getWeekRangeLabel(w, ninetyDaysAgo);
        weeklyDataMap[label] = [];
        orderedWeeks.push(label);
    }

    results.forEach(r => {
        const weekLabel = getWeekLabelForDate(new Date(r.createdAt), ninetyDaysAgo);
        const totalMarks = r.totalMarks || 100;
        const scorePercentage = totalMarks > 0 ? (r.obtainedMarks / totalMarks) * 100 : 0;
        if (weeklyDataMap[weekLabel]) {
            weeklyDataMap[weekLabel].push(scorePercentage);
        }
    });

    const graphData = orderedWeeks.map((week) => {
        const scores = weeklyDataMap[week];
        if (scores.length === 0) {
            return { week, percentage: null };
        }
        const avg = scores.reduce((a, b) => a + b, 0) / scores.length;
        return { week, percentage: Math.round(avg) };
    });

    res.status(200).json({
        message: "Overall practice test weekly trend fetched successfully",
        period: {
            from: ninetyDaysAgo.toISOString().split('T')[0],
            to: new Date().toISOString().split('T')[0],
            totalDays: 90
        },
        data: {
            examId: examId || "",
            examName,
            graphData
        }
    });
});

// All students subject wise performance in practice test
export const getSubjectWisePracticePerformance = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const isAdmin = user.role === "ADMIN";
    const institutionId = isAdmin ? (req.query.institutionId as string | undefined) : user.institutionId;

    const examId = req.query.examId as string | undefined;
    if (!examId) {
        res.status(400).json({ message: "examId is required" });
        return;
    }

    const page = parseInt(req.query.page as string) || 1;
    const limit = parseInt(req.query.limit as string) || 10;
    const skip = (page - 1) * limit;
    
    const sortOrder = (req.query.sortOrder as string) === "asc" ? "asc" : "desc";

    const availableSubjects = await prisma.subject.findMany({
        where: {
            OR: [
                {
                    questions: {
                        some: {
                            practiceTests: {
                                some: {
                                    examId: examId,
                                    ...(institutionId ? { institutionId } : {})
                                }
                            }
                        }
                    }
                },
                {
                    questions: {
                        some: {
                            testQuestions: {
                                some: {
                                    test: {
                                        practiceTests: {
                                            some: {
                                                examId: examId,
                                                ...(institutionId ? { institutionId } : {})
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                },
                {
                    tests: {
                        some: {
                            practiceTests: {
                                some: {
                                    examId: examId,
                                    ...(institutionId ? { institutionId } : {})
                                }
                            }
                        }
                    }
                }
            ]
        },
        select: {
            id: true,
            subjectName: true,
            institutionId: true,
            institution: {
                select: {
                    user: {
                        select: { institutionName: true }
                    }
                }
            }
        }
    });

    const publishedSubjects = await prisma.subject.findMany({
        where: {
            OR: [
                {
                    questions: {
                        some: {
                            practiceTests: {
                                some: {
                                    examId: examId,
                                    publish: true,
                                    ...(institutionId ? { institutionId } : {})
                                }
                            }
                        }
                    }
                },
                {
                    questions: {
                        some: {
                            testQuestions: {
                                some: {
                                    test: {
                                        practiceTests: {
                                            some: {
                                                examId: examId,
                                                publish: true,
                                                ...(institutionId ? { institutionId } : {})
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                },
                {
                    tests: {
                        some: {
                            practiceTests: {
                                some: {
                                    examId: examId,
                                    publish: true,
                                    ...(institutionId ? { institutionId } : {})
                                }
                            }
                        }
                    }
                }
            ]
        },
        select: { id: true }
    });

    const publishedSubjectIds = new Set(publishedSubjects.map(s => s.id));

    const subjectScoresMap: Record<string, { name: string; isShared: boolean; sharedInstitutionName: string | null | undefined; scores: number[] }> = {};
    
    availableSubjects.forEach((s) => {
        const isShared = !isAdmin && s.institutionId !== institutionId;
        const sharedInstitutionName = isShared ? s.institution?.user?.institutionName : null;
        
        subjectScoresMap[s.id] = {
            name: s.subjectName,
            isShared,
            sharedInstitutionName,
            scores: []
        };
    });

    const ninetyDaysAgo = new Date();
    ninetyDaysAgo.setDate(ninetyDaysAgo.getDate() - 90);
    ninetyDaysAgo.setHours(0, 0, 0, 0);

    const resultWhereClause: any = {
        practiceTestId: { not: null },
        createdAt: { gte: ninetyDaysAgo },
        practiceTest: {
            examId: examId
        }
    };

    if (institutionId) {
        resultWhereClause.OR = [
            { institutionId: institutionId },
            { studentInstitution: { institutionId: institutionId } }
        ];
    }

    const results = await prisma.examResult.findMany({
        where: resultWhereClause,
        include: {
            practiceTest: {
                include: {
                    questions: {
                        select: {
                            subjectId: true
                        }
                    },
                    test: {
                        include: {
                            testQuestions: {
                                include: {
                                    question: {
                                        select: {
                                            subjectId: true
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    });

    results.forEach((result) => {
        if (!result.practiceTest) return;

        const totalMarks = result.totalMarks || 100;
        const scorePercentage = totalMarks > 0 ? (result.obtainedMarks / totalMarks) * 100 : 0;

        const uniqueSubjectIdsInResult = new Set<string>();

        if (result.practiceTest.questions) {
            result.practiceTest.questions.forEach(q => {
                if (q.subjectId) uniqueSubjectIdsInResult.add(q.subjectId);
            });
        }
        
        if (result.practiceTest.test?.testQuestions) {
            result.practiceTest.test.testQuestions.forEach(tq => {
                if (tq.question?.subjectId) uniqueSubjectIdsInResult.add(tq.question.subjectId);
            });
        }

        if (result.practiceTest.test?.subjectId) {
            uniqueSubjectIdsInResult.add(result.practiceTest.test.subjectId);
        }

        uniqueSubjectIdsInResult.forEach((subId) => {
            if (subjectScoresMap[subId]) {
                subjectScoresMap[subId].scores.push(scorePercentage);
            }
        });
    });

    const allSubjectData = Object.keys(subjectScoresMap).map((subjectId) => {
        const item = subjectScoresMap[subjectId];
        
        const average = item.scores.length > 0 
            ? Math.round(item.scores.reduce((a, b) => a + b, 0) / item.scores.length)
            : null; 

        return {
            subjectId,
            subjectName: item.name,
            percentage: average,
            isShared: item.isShared,
            sharedInstitutionName: item.sharedInstitutionName
        };
    });

    const filteredSubjectData = allSubjectData.filter(item => 
        (subjectScoresMap[item.subjectId]?.scores && subjectScoresMap[item.subjectId].scores.length > 0) || 
        publishedSubjectIds.has(item.subjectId)
    );

    filteredSubjectData.sort((a, b) => {
        const aMissing = a.percentage === null;
        const bMissing = b.percentage === null;
        
        if (aMissing && bMissing) return 0;
        
        if (sortOrder === "asc") {
            if (aMissing) return -1;
            if (bMissing) return 1;
            return a.percentage! - b.percentage!;
        } else {
            if (aMissing) return 1;
            if (bMissing) return -1;
            return b.percentage! - a.percentage!;
        }
    });

    const totalSubjects = filteredSubjectData.length;
    const paginatedData = filteredSubjectData.slice(skip, skip + limit);
    const totalPages = Math.ceil(totalSubjects / limit);

    res.status(200).json({
        message: "Subject wise performance trends fetched successfully",
        pagination: {
            totalSubjects,
            currentPage: page,
            limit,
            totalPages,
            hasNextPage: page < totalPages,
            hasPrevPage: page > 1
        },
        data: paginatedData
    });
});

// All students specific subject trend
export const getParticularSubjectPracticeTrend = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const isAdmin = user.role === "ADMIN";
    const institutionId = isAdmin ? (req.query.institutionId as string | undefined) : user.institutionId;

    const examId = req.query.examId as string | undefined;
    const subjectId = req.query.subjectId as string | undefined;

    if (!examId || !subjectId) {
        res.status(400).json({ message: "Both examId and subjectId are required" });
        return;
    }

    const ninetyDaysAgo = new Date();
    ninetyDaysAgo.setDate(ninetyDaysAgo.getDate() - 90);
    ninetyDaysAgo.setHours(0, 0, 0, 0);

    const resultWhereClause: any = {
        practiceTestId: { not: null },
        createdAt: { gte: ninetyDaysAgo },
        practiceTest: {
            examId: examId,
            OR: [
                {
                    questions: {
                        some: {
                            subjectId: subjectId
                        }
                    }
                },
                {
                    test: {
                        testQuestions: {
                            some: {
                                question: {
                                    subjectId: subjectId
                                }
                            }
                        }
                    }
                },
                {
                    test: {
                        subjectId: subjectId
                    }
                }
            ]
        }
    };

    if (institutionId) {
        resultWhereClause.OR = [
            { institutionId: institutionId },
            { studentInstitution: { institutionId: institutionId } }
        ];
    }

    const results = await prisma.examResult.findMany({
        where: resultWhereClause,
        select: {
            createdAt: true,
            obtainedMarks: true,
            totalMarks: true
        },
        orderBy: {
            createdAt: "asc"
        }
    });

    const weeks: { start: Date; end: Date; label: string; scores: number[] }[] = [];
    
    for (let i = 12; i >= 0; i--) {
        const start = new Date();
        start.setDate(start.getDate() - (i * 7 + 6));
        start.setHours(0, 0, 0, 0);

        const end = new Date();
        end.setDate(end.getDate() - (i * 7));
        end.setHours(23, 59, 59, 999);

        const label = getCompactWeekRangeLabel(start, end);

        weeks.push({
            start,
            end,
            label,
            scores: []
        });
    }

    results.forEach((result) => {
        const totalMarks = result.totalMarks || 100;
        const scorePercentage = totalMarks > 0 ? (result.obtainedMarks / totalMarks) * 100 : 0;
        const resultDate = new Date(result.createdAt);

        const matchingWeek = weeks.find(w => resultDate >= w.start && resultDate <= w.end);
        if (matchingWeek) {
            matchingWeek.scores.push(scorePercentage);
        }
    });

    const trendData = weeks.map((w) => {
        const average = w.scores.length > 0 
            ? Math.round(w.scores.reduce((a, b) => a + b, 0) / w.scores.length)
            : null;

        return {
            week: w.label,
            percentage: average 
        };
    });

    res.status(200).json({
        message: "Particular subject practice weekly trend fetched successfully",
        data: trendData
    });
});

// All students performance table
export const getParticularSubjectStudentTable = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const isAdmin = user.role === "ADMIN";
    const institutionId = isAdmin ? (req.query.institutionId as string | undefined) : user.institutionId;

    const examId = req.query.examId as string | undefined;
    const subjectId = req.query.subjectId as string | undefined;

    if (!examId) {
        res.status(400).json({ message: "examId is required" });
        return;
    }

    const page = parseInt(req.query.page as string) || 1;
    const limit = parseInt(req.query.limit as string) || 10;
    const skip = (page - 1) * limit;

    const search = (req.query.search as string | undefined)?.toLowerCase();
    const filterParam = ((req.query.filter || req.query.statusFilter) as string | undefined)?.toUpperCase() || "ALL";
    const filter = filterParam === "NOTATTENDED" ? "NOT_ATTENDED" : filterParam;
    const sortBy = (req.query.sortBy as string) || "studentName";
    const sortOrder = (req.query.sortOrder as string) || (sortBy === "studentName" ? "asc" : "desc"); 

    const weeks: { start: Date; end: Date; label: string }[] = [];
    for (let i = 12; i >= 0; i--) {
        const start = new Date();
        start.setDate(start.getDate() - (i * 7 + 6));
        start.setHours(0, 0, 0, 0);

        const end = new Date();
        end.setDate(end.getDate() - (i * 7));
        end.setHours(23, 59, 59, 999);

        const label = getCompactWeekRangeLabel(start, end);
        weeks.push({ start, end, label });
    }

    const weekHeaders = weeks.map(w => w.label);
    const ninetyDaysAgo = weeks[0].start; 

    const studentWhereClause: any = {};
    if (institutionId) {
        studentWhereClause.institutions = {
            some: {
                institutionId,
                deletedAt: null,
                isVerified: true
            }
        };
    }

    if (search) {
        const searchParts = search.trim().split(/\s+/).filter(Boolean);
        if (searchParts.length > 0) {
            studentWhereClause.user = {
                AND: searchParts.map((part) => ({
                    OR: [
                        { firstName: { contains: part } },
                        { lastName: { contains: part } },
                        { email: { contains: part } }
                    ]
                }))
            };
        }
    }

    const students = await prisma.student.findMany({
        where: studentWhereClause,
        select: {
            id: true,
            user: {
                select: {
                    firstName: true,
                    lastName: true,
                    email: true
                }
            },
            institutions: {
                orderBy: { createdAt: "desc" },
                select: {
                    exam: {
                        select: {
                            examName: true
                        }
                    }
                }
            }
        }
    });

    const results = await prisma.examResult.findMany({
        where: {
            practiceTestId: { not: null },
            createdAt: { gte: ninetyDaysAgo },
            studentId: { in: students.map(s => s.id) },
            ...(institutionId ? { studentInstitution: { institutionId } } : {}),
            practiceTest: {
                examId: examId,
                ...(subjectId ? {
                    OR: [
                        {
                            questions: {
                                some: {
                                    subjectId: subjectId
                                }
                            }
                        },
                        {
                            test: {
                                testQuestions: {
                                    some: {
                                        question: {
                                            subjectId: subjectId
                                        }
                                    }
                                }
                            }
                        },
                        {
                            test: {
                                subjectId: subjectId
                            }
                        }
                    ]
                } : {})
            }
        },
        select: {
            studentId: true,
            createdAt: true,
            obtainedMarks: true,
            totalMarks: true
        }
    });

    const resultsByStudent: Record<string, typeof results> = {};
    results.forEach((res) => {
        if (!resultsByStudent[res.studentId]) {
            resultsByStudent[res.studentId] = [];
        }
        resultsByStudent[res.studentId].push(res);
    });

    const attendedExamWhere: any = {
        studentId: { in: students.map((s) => s.id) },
        practiceTestId: { not: null }
    };

    if (institutionId) {
        attendedExamWhere.studentInstitution = { institutionId };
    }

    const attendedExamRows = await prisma.examResult.findMany({
        where: attendedExamWhere,
        select: {
            studentId: true,
            practiceTest: {
                select: {
                    exam: {
                        select: {
                            examName: true
                        }
                    }
                }
            }
        }
    });

    const attendedExamsByStudent: Record<string, Set<string>> = {};
    attendedExamRows.forEach((row) => {
        const examName = row.practiceTest?.exam?.examName;
        if (!examName) return;
        if (!attendedExamsByStudent[row.studentId]) {
            attendedExamsByStudent[row.studentId] = new Set<string>();
        }
        attendedExamsByStudent[row.studentId].add(examName);
    });

    let tableRows = students.map((student) => {
        const studentResults = resultsByStudent[student.id] || [];
        const attended = studentResults.length > 0;

        const weeklyScores: Record<string, { totalScore: number; count: number }> = {};
        weekHeaders.forEach(label => {
            weeklyScores[label] = { totalScore: 0, count: 0 };
        });

        let allScoresSum = 0;
        let totalAttendedCount = 0;

        studentResults.forEach((res) => {
            const totalMarks = res.totalMarks || 100;
            const percentage = totalMarks > 0 ? (res.obtainedMarks / totalMarks) * 100 : 0;
            const resDate = new Date(res.createdAt);

            const matchedWeek = weeks.find(w => resDate >= w.start && resDate <= w.end);
            if (matchedWeek) {
                weeklyScores[matchedWeek.label].totalScore += percentage;
                weeklyScores[matchedWeek.label].count += 1;
            }

            allScoresSum += percentage;
            totalAttendedCount += 1;
        });

        const weekData: Record<string, number | null> = {};
        weekHeaders.forEach(label => {
            const bucket = weeklyScores[label];
            weekData[label] = bucket.count > 0 ? Math.round(bucket.totalScore / bucket.count) : null;
        });

        const overallPercentage = totalAttendedCount > 0 
            ? Math.round(allScoresSum / totalAttendedCount) 
            : null;

        const resolvedName = (student.user?.firstName || student.user?.lastName) 
            ? `${student.user?.firstName || ''} ${student.user?.lastName || ''}`.trim() 
            : student.user?.email || "Anonymous Student";

        const currentExam = student.institutions && student.institutions.length > 0 && student.institutions[0].exam
            ? student.institutions[0].exam.examName
            : "No Exam Assigned";

        const attendedExamNames = Array.from(attendedExamsByStudent[student.id] || []);
        const orderedAttendedExamNames = attendedExamNames.includes(currentExam)
            ? [currentExam, ...attendedExamNames.filter((name) => name !== currentExam)]
            : attendedExamNames;
        const previousExams = orderedAttendedExamNames.filter((name) => name !== currentExam);
        const assignedExam = orderedAttendedExamNames.length > 0
            ? orderedAttendedExamNames.join(", ")
            : currentExam;

        return {
            studentId: student.id,
            studentName: resolvedName,
            email: student.user?.email || "",
            assignedExam,
            currentExam,
            previousExams,
            weeks: weekData, 
            overallAverage: overallPercentage ?? 0,
            hasAttended: attended                     
        };
    });

    if (filter === "ATTENDED") {
        tableRows = tableRows.filter(row => row.hasAttended === true);
    } else if (filter === "NOT_ATTENDED") {
        tableRows = tableRows.filter(row => row.hasAttended === false);
    }

    tableRows.sort((a, b) => {
        if (sortBy === "studentName") {
            return sortOrder === "desc"
                ? b.studentName.localeCompare(a.studentName)
                : a.studentName.localeCompare(b.studentName);
        } else {
            const valA = a.hasAttended ? a.overallAverage : -1; 
            const valB = b.hasAttended ? b.overallAverage : -1;

            if (sortOrder === "asc") {
                if (!a.hasAttended) return 1;
                if (!b.hasAttended) return -1;
                return valA - valB;
            } else {
                return valB - valA;
            }
        }
    });

    const totalRecords = tableRows.length;
    const paginatedRows = tableRows.slice(skip, skip + limit);
    const totalPages = Math.ceil(totalRecords / limit);

    res.status(200).json({
        message: "Student subject-wise tabular report fetched successfully",
        columns: ["Student Name", ...weekHeaders, "Overall Average"],
        pagination: {
            total: totalRecords,
            currentPage: page,
            limit,
            totalPages,
            hasNextPage: page < totalPages,
            hasPrevPage: page > 1
        },
        data: paginatedRows
    });
});

// Export PDF for the students table (all records, respects filters)
export const getOverallMockWeeklyTrend = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const isAdmin = user.role === "ADMIN";
    const institutionId = isAdmin ? (req.query.institutionId as string | undefined) : user.institutionId;

    const examId = req.query.examId as string | undefined;

    let examName = "—";
    if (examId) {
        const exam = await prisma.exam.findUnique({
            where: { id: examId },
            select: { examName: true }
        });
        if (exam) {
            examName = exam.examName;
        }
    }

    const ninetyDaysAgo = new Date();
    ninetyDaysAgo.setDate(ninetyDaysAgo.getDate() - 90);
    ninetyDaysAgo.setHours(0, 0, 0, 0);

    const whereClause: any = {
        createdAt: { gte: ninetyDaysAgo }
    };

    if (examId) {
        whereClause.mockTest = {
            examId: examId
        };
    }

    if (institutionId) {
        whereClause.studentInstitution = {
            institutionId: institutionId
        };
    }

    const results = await prisma.mockTestResult.findMany({
        where: whereClause,
        orderBy: { createdAt: 'asc' }
    });

    const weeklyDataMap: Record<string, number[]> = {};
    const orderedWeeks: string[] = [];
    for (let w = 0; w < 13; w++) {
        const label = getWeekRangeLabel(w, ninetyDaysAgo);
        weeklyDataMap[label] = [];
        orderedWeeks.push(label);
    }

    results.forEach(r => {
        const weekLabel = getWeekLabelForDate(new Date(r.createdAt), ninetyDaysAgo);
        const totalMarks = r.totalMarks || 100;
        const scorePercentage = totalMarks > 0 ? (r.obtainedMarks / totalMarks) * 100 : 0;

        if (weeklyDataMap[weekLabel]) {
            weeklyDataMap[weekLabel].push(scorePercentage);
        }
    });

    const graphData = orderedWeeks.map((week) => {
        const scores = weeklyDataMap[week];
        if (scores.length === 0) {
            return { week, percentage: null };
        }
        const avg = scores.reduce((a, b) => a + b, 0) / scores.length;
        return { week, percentage: Math.round(avg) };
    });

    res.status(200).json({
        message: "Overall Mock test weekly trend fetched successfully",
        period: {
            from: ninetyDaysAgo.toISOString().split('T')[0],
            to: new Date().toISOString().split('T')[0],
            totalDays: 90
        },
        data: {
            examId: examId || "",
            examName,
            graphData
        }
    });
});

// 2. Student's mock weekly report
export const getStudentMockWeeklyReport = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const isAdmin = user.role === "ADMIN";
    const institutionId = isAdmin ? (req.query.institutionId as string | undefined) : user.institutionId;

    const examId = req.query.examId as string | undefined;
    const sortBy = (req.query.sortBy as string) || "studentName";
    const sortOrder = (req.query.sortOrder as string) || (sortBy === "studentName" ? "asc" : "desc");
    
    const statusFilter = (req.query.statusFilter as string) || "all"; 
    const search = (req.query.search as string || "").trim();

    const page = parseInt(req.query.page as string) || 1;
    const limit = parseInt(req.query.limit as string) || 10;
    const skip = (page - 1) * limit;

    const ninetyDaysAgo = new Date();
    ninetyDaysAgo.setDate(ninetyDaysAgo.getDate() - 90);
    ninetyDaysAgo.setHours(0, 0, 0, 0);

    let examName = "All Exams";
    if (examId) {
        const exam = await prisma.exam.findUnique({
            where: { id: examId },
            select: { examName: true }
        });
        if (exam) examName = exam.examName;
    }

    const studentWhereClause: any = {};
    if (institutionId) {
        studentWhereClause.institutions = {
            some: { institutionId, deletedAt: null, isVerified: true }
        };
    }

    if (search) {
        const searchParts = search.trim().split(/\s+/).filter(Boolean);
        if (searchParts.length > 0) {
            studentWhereClause.user = {
                AND: searchParts.map((part) => ({
                    OR: [
                        { firstName: { contains: part } },
                        { lastName: { contains: part } },
                        { email: { contains: part } }
                    ]
                }))
            };
        }
    }

    const students = await prisma.student.findMany({
        where: studentWhereClause,
        select: {
            id: true,
            user: {
                select: {
                    firstName: true,
                    lastName: true,
                    email: true
                }
            },
            institutions: {
                orderBy: { createdAt: "desc" },
                select: {
                    exam: {
                        select: {
                            examName: true
                        }
                    }
                }
            }
        }
    });

    const whereClause: any = {
        createdAt: { gte: ninetyDaysAgo },
        studentId: { in: students.map((s) => s.id) }
    };

    if (examId) {
        whereClause.mockTest = { examId: examId };
    }
    if (institutionId) {
        whereClause.studentInstitution = { institutionId: institutionId };
    }

    const results = await prisma.mockTestResult.findMany({
        where: whereClause,
        select: {
            studentId: true,
            obtainedMarks: true,
            totalMarks: true,
            createdAt: true
        },
        orderBy: { createdAt: 'asc' }
    });

    const orderedWeeks: string[] = [];
    for (let w = 0; w < 13; w++) {
        orderedWeeks.push(getWeekRangeLabel(w, ninetyDaysAgo));
    }

    const studentDataMap: Record<string, {
        studentName: string;
        email: string;
        assignedExam: string;
        weeklyScores: Record<string, number[]>;
        allScores: number[]
    }> = {};

    students.forEach((student) => {
        const firstName = student.user?.firstName || "";
        const lastName = student.user?.lastName || "";
        const studentName = `${firstName} ${lastName}`.trim() || student.user?.email || `Student (ID: ${student.id.substring(0, 5)}...)`;
        const assignedExam = student.institutions && student.institutions.length > 0 && student.institutions[0].exam
            ? student.institutions[0].exam.examName
            : "No Exam Assigned";

        studentDataMap[student.id] = {
            studentName,
            email: student.user?.email || "",
            assignedExam,
            weeklyScores: {},
            allScores: []
        };

        orderedWeeks.forEach((w) => {
            studentDataMap[student.id].weeklyScores[w] = [];
        });
    });

    results.forEach(r => {
        if (!studentDataMap[r.studentId]) return;

        const weekLabel = getWeekLabelForDate(new Date(r.createdAt), ninetyDaysAgo);
        const totalMarks = r.totalMarks || 100;
        const scorePercentage = totalMarks > 0 ? (r.obtainedMarks / totalMarks) * 100 : 0;

        if (studentDataMap[r.studentId].weeklyScores[weekLabel]) {
            studentDataMap[r.studentId].weeklyScores[weekLabel].push(scorePercentage);
        }
        studentDataMap[r.studentId].allScores.push(scorePercentage);
    });

    const studentsReport: any[] = [];

    Object.keys(studentDataMap).forEach(studentId => {
        const student = studentDataMap[studentId];
        const hasAttended = student.allScores.length > 0;

        if (statusFilter === "attended" && !hasAttended) return; 
        if (statusFilter === "notattended" && hasAttended) return;   

        if (search && !student.studentName.toLowerCase().includes(search.toLowerCase())) {
            return;
        }

        const weeksBreakdown: Record<string, number | null> = {};
        orderedWeeks.forEach(week => {
            const scores = student.weeklyScores[week];
            weeksBreakdown[week] = scores.length > 0
                ? Math.round(scores.reduce((a, b) => a + b, 0) / scores.length)
                : null;
        });

        const overallAverage = hasAttended
            ? Math.round(student.allScores.reduce((a, b) => a + b, 0) / student.allScores.length)
            : 0;

        studentsReport.push({
            studentId,
            studentName: student.studentName,
            email: student.email,
            assignedExam: student.assignedExam,
            weeks: weeksBreakdown,
            overallAverage,
            hasAttended
        });
    });

    studentsReport.sort((a, b) => {
        if (sortBy === "studentName") {
            return sortOrder === "desc"
                ? b.studentName.localeCompare(a.studentName)
                : a.studentName.localeCompare(b.studentName);
        } else {
            const valA = a.hasAttended ? a.overallAverage : -1;
            const valB = b.hasAttended ? b.overallAverage : -1;

            if (sortOrder === "asc") {
                if (!a.hasAttended) return 1;
                if (!b.hasAttended) return -1;
                return valA - valB;
            } else {
                return valB - valA;
            }
        }
    });

    const totalItems = studentsReport.length;
    const totalPages = Math.ceil(totalItems / limit);
    const paginatedStudents = studentsReport.slice(skip, skip + limit);

    res.status(200).json({
        message: "Student Mock test weekly performance reports matrix fetched successfully",
        examName,
        columns: ["Student Name", ...orderedWeeks, "Overall Average"],
        data: paginatedStudents,
        pagination: {
            total: totalItems,
            page,
            limit,
            totalPages
        },
    });
});

// Previous Year Questions Test

// 1. Overall pyq test trend exam wise
export const getOverallPyqWeeklyTrend = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const isAdmin = user.role === "ADMIN";
    const institutionId = isAdmin ? (req.query.institutionId as string | undefined) : user.institutionId;

    const examId = req.query.examId as string | undefined;

    let examName = "—";
    if (examId) {
        const exam = await prisma.exam.findUnique({
            where: { id: examId },
            select: { examName: true }
        });
        if (exam) {
            examName = exam.examName;
        }
    }

    const ninetyDaysAgo = new Date();
    ninetyDaysAgo.setDate(ninetyDaysAgo.getDate() - 90);
    ninetyDaysAgo.setHours(0, 0, 0, 0);

    const whereClause: any = {
        testType: "OldQuestionPaper",
        createdAt: { gte: ninetyDaysAgo }
    };

    if (examId) {
        whereClause.oldQuestionPaper = {
            examId: examId
        };
    }

    if (institutionId) {
        whereClause.studentInstitution = {
            institutionId: institutionId
        };
    }

    const results = await prisma.examResult.findMany({
        where: whereClause,
        orderBy: { createdAt: 'asc' }
    });

    const weeklyDataMap: Record<string, number[]> = {};
    const orderedWeeks: string[] = [];
    for (let w = 0; w < 13; w++) {
        const label = getWeekRangeLabel(w, ninetyDaysAgo);
        weeklyDataMap[label] = [];
        orderedWeeks.push(label);
    }

    results.forEach(r => {
        const weekLabel = getWeekLabelForDate(new Date(r.createdAt), ninetyDaysAgo);
        const totalMarks = r.totalMarks || 100;
        const scorePercentage = totalMarks > 0 ? (r.obtainedMarks / totalMarks) * 100 : 0;

        if (weeklyDataMap[weekLabel]) {
            weeklyDataMap[weekLabel].push(scorePercentage);
        }
    });

    const graphData = orderedWeeks.map((week) => {
        const scores = weeklyDataMap[week];
        if (scores.length === 0) {
            return { week, percentage: null };
        }
        const avg = scores.reduce((a, b) => a + b, 0) / scores.length;
        return { week, percentage: Math.round(avg) };
    });

    res.status(200).json({
        message: "Overall PYQ test weekly trend fetched successfully",
        period: {
            from: ninetyDaysAgo.toISOString().split('T')[0],
            to: new Date().toISOString().split('T')[0],
            totalDays: 90
        },
        data: {
            examId: examId || "",
            examName,
            graphData
        }
    });
});

// 2. Student's weekly report
export const getStudentPyqWeeklyReport = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const isAdmin = user.role === "ADMIN";
    const institutionId = isAdmin ? (req.query.institutionId as string | undefined) : user.institutionId;

    const examId = req.query.examId as string | undefined;
    const sortBy = (req.query.sortBy as string) || "studentName";
    const sortOrder = (req.query.sortOrder as string) || (sortBy === "studentName" ? "asc" : "desc");
    
    const statusFilter = (req.query.statusFilter as string) || "all"; 
    const search = (req.query.search as string | undefined)?.trim();

    const page = parseInt(req.query.page as string) || 1;
    const limit = parseInt(req.query.limit as string) || 10;
    const skip = (page - 1) * limit;

    const ninetyDaysAgo = new Date();
    ninetyDaysAgo.setDate(ninetyDaysAgo.getDate() - 90);
    ninetyDaysAgo.setHours(0, 0, 0, 0);

    let examName = "All Exams";
    if (examId) {
        const exam = await prisma.exam.findUnique({
            where: { id: examId },
            select: { examName: true }
        });
        if (exam) examName = exam.examName;
    }

    const studentWhereClause: any = {};
    if (institutionId) {
        studentWhereClause.institutions = {
            some: { institutionId, deletedAt: null, isVerified: true }
        };
    }

    if (search) {
        const searchParts = search.trim().split(/\s+/).filter(Boolean);
        if (searchParts.length > 0) {
            studentWhereClause.user = {
                AND: searchParts.map((part) => ({
                    OR: [
                        { firstName: { contains: part } },
                        { lastName: { contains: part } },
                        { email: { contains: part } }
                    ]
                }))
            };
        }
    }

    const students = await prisma.student.findMany({
        where: studentWhereClause,
        select: {
            id: true,
            user: {
                select: {
                    firstName: true,
                    lastName: true,
                    email: true
                }
            },
            institutions: {
                orderBy: { createdAt: "desc" },
                select: {
                    exam: {
                        select: {
                            examName: true
                        }
                    }
                }
            }
        }
    });

    const whereClause: any = {
        testType: "OldQuestionPaper",
        createdAt: { gte: ninetyDaysAgo },
        studentId: { in: students.map((s) => s.id) }
    };

    if (examId) {
        whereClause.oldQuestionPaper = { examId: examId };
    }
    if (institutionId) {
        whereClause.studentInstitution = { institutionId: institutionId };
    }

    const results = await prisma.examResult.findMany({
        where: whereClause,
        select: {
            studentId: true,
            obtainedMarks: true,
            totalMarks: true,
            createdAt: true
        },
        orderBy: { createdAt: 'asc' }
    });

    const orderedWeeks: string[] = [];
    for (let w = 0; w < 13; w++) {
        orderedWeeks.push(getWeekRangeLabel(w, ninetyDaysAgo));
    }

    const studentDataMap: Record<string, {
        studentName: string;
        email: string;
        assignedExam: string;
        weeklyScores: Record<string, number[]>;
        allScores: number[]
    }> = {};

    students.forEach((student) => {
        const firstName = student.user?.firstName || "";
        const lastName = student.user?.lastName || "";
        const studentName = `${firstName} ${lastName}`.trim() || student.user?.email || `Student (ID: ${student.id.substring(0, 5)}...)`;
        const assignedExam = student.institutions && student.institutions.length > 0 && student.institutions[0].exam
            ? student.institutions[0].exam.examName
            : "No Exam Assigned";

        studentDataMap[student.id] = {
            studentName,
            email: student.user?.email || "",
            assignedExam,
            weeklyScores: {},
            allScores: []
        };

        orderedWeeks.forEach((w) => {
            studentDataMap[student.id].weeklyScores[w] = [];
        });
    });

    results.forEach(r => {
        if (!studentDataMap[r.studentId]) return;

        const weekLabel = getWeekLabelForDate(new Date(r.createdAt), ninetyDaysAgo);
        const totalMarks = r.totalMarks || 100;
        const scorePercentage = totalMarks > 0 ? (r.obtainedMarks / totalMarks) * 100 : 0;

        if (studentDataMap[r.studentId].weeklyScores[weekLabel]) {
            studentDataMap[r.studentId].weeklyScores[weekLabel].push(scorePercentage);
        }
        studentDataMap[r.studentId].allScores.push(scorePercentage);
    });

    const studentsReport: any[] = [];

    Object.keys(studentDataMap).forEach(studentId => {
        const student = studentDataMap[studentId];
        const hasAttended = student.allScores.length > 0;

        if (statusFilter === "attended" && !hasAttended) return; 
        if (statusFilter === "notattended" && hasAttended) return;   

        if (search && !student.studentName.toLowerCase().includes(search.toLowerCase())) {
            return;
        }

        const weeksBreakdown: Record<string, number | null> = {};
        orderedWeeks.forEach(week => {
            const scores = student.weeklyScores[week];
            weeksBreakdown[week] = scores.length > 0
                ? Math.round(scores.reduce((a, b) => a + b, 0) / scores.length)
                : null;
        });

        const overallAverage = hasAttended
            ? Math.round(student.allScores.reduce((a, b) => a + b, 0) / student.allScores.length)
            : 0;

        studentsReport.push({
            studentId,
            studentName: student.studentName,
            email: student.email,
            assignedExam: student.assignedExam,
            weeks: weeksBreakdown,
            overallAverage,
            hasAttended
        });
    });

    studentsReport.sort((a, b) => {
        if (sortBy === "studentName") {
            return sortOrder === "desc"
                ? b.studentName.localeCompare(a.studentName)
                : a.studentName.localeCompare(b.studentName);
        } else {
            const valA = a.hasAttended ? a.overallAverage : -1;
            const valB = b.hasAttended ? b.overallAverage : -1;

            if (sortOrder === "asc") {
                if (!a.hasAttended) return 1;
                if (!b.hasAttended) return -1;
                return valA - valB;
            } else {
                return valB - valA;
            }
        }
    });

    const totalItems = studentsReport.length;
    const totalPages = Math.ceil(totalItems / limit);
    const paginatedStudents = studentsReport.slice(skip, skip + limit);

    res.status(200).json({
        message: "Student PYQ weekly performance reports matrix fetched successfully",
        examName,
        columns: ["Student Name", ...orderedWeeks, "Overall Average"],
        pagination: {
            total: totalItems,
            page,
            limit,
            totalPages
        },
        data: paginatedStudents
    });
});