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";

// Students stats count
export const getStudentStats = asyncHandler(
    async (req: Request, res: Response) => {
        const user = req.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 search = (req.query.search as string)?.trim() || "";
        const subscriptionStatus = req.query.subscriptionStatus as string | undefined;
        const planStatus = (req.query.planStatus as string || subscriptionStatus)?.trim();
        const planType = (req.query.planType as string)?.trim();
        const planName = (req.query.planName as string)?.trim();
        const isVerified = (req.query.isVerified as string)?.trim();
        const language = (req.query.language as string)?.trim();
        const regStartDate = (req.query.regStartDate as string)?.trim();
        const regEndDate = (req.query.regEndDate 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 statsWhere: any = { 
            deletedAt: null,
            student: { user: { isDeleted: false } }
        };
        if (institutionId) statsWhere.institutionId = institutionId;
        if (examId) statsWhere.examsId = examId;

        const where: any = { ...statsWhere };

        if (search) {
            const searchWords = search.split(/\s+/).filter(Boolean);

            where.student = {
                user: {
                    isDeleted: false,
                    OR: [
                        { firstName: { contains: search } },
                        { lastName: { contains: search } },
                        { email: { contains: search } },
                        { phone: { contains: search } },
                        ...(searchWords.length > 1 ? [{
                            AND: searchWords.map(word => ({
                                OR: [
                                    { firstName: { contains: word } },
                                    { lastName: { contains: word } }
                                ]
                            }))
                        }] : [])
                    ],
                },
            };
        }

        if (isVerified) {
            where.isVerified = isVerified === "true";
        }

        if (language) {
            where.language = language;
        }

        if (planStatus || planName || planType) {
            const subConditions: any = { isCurrent: true };
            if (planStatus && planStatus !== "ALL" && planStatus !== "UNPAID") {
                subConditions.status = planStatus.toUpperCase();
            }
            if (planName || planType) {
                subConditions.plan = {};
                if (planName) subConditions.plan.planName = planName;
                if (planType) subConditions.plan.planType = planType;
            }
            where.subscriptions = {
                some: subConditions
            };
        }

        if (regStartDate || regEndDate) {
            where.createdAt = {};

            const convertToISOFormat = (dateStr: string): string => {
                const parts = dateStr.split('/');
                if (parts.length === 3) {
                    const [day, month, year] = parts;
                    return `${year}-${month}-${day}`;
                }
                return dateStr;
            };

            if (regStartDate) {
                const formattedStartDate = convertToISOFormat(regStartDate);
                where.createdAt.gte = new Date(`${formattedStartDate}T00:00:00.000Z`);
            }
            if (regEndDate) {
                const formattedEndDate = convertToISOFormat(regEndDate);
                where.createdAt.lte = new Date(`${formattedEndDate}T23:59:59.999Z`);
            }
        }

        const [statsRecords, students, totalStudents] = await Promise.all([
            prisma.studentInstitution.findMany({
                where: statsWhere,
                select: {
                    isVerified: true,
                    subscriptions: {
                        where: { isCurrent: true },
                        select: { status: true }
                    }
                }
            }),
            prisma.studentInstitution.findMany({
                where,
                include: {
                    student: {
                        include: {
                            user: {
                                select: {
                                    firstName: true,
                                    lastName: true,
                                    email: true,
                                    phone: true,
                                },
                            },
                        },
                    },
                    exam: { select: { examName: true } },
                    subscriptions: {
                        where: { isCurrent: true },
                        select: {
                            id: true,
                            status: true,
                            amount: true,
                            startDate: true,
                            endDate: true,
                            plan: {
                                select: {
                                    id: true,
                                    planName: true,
                                    planType: true,
                                    duration: true
                                }
                            }
                        },
                    },
                },
                orderBy: { createdAt: "desc" },
                skip,
                take: limit,
            }),
            prisma.studentInstitution.count({ where }),
        ]);

        let totalEnrolled = statsRecords.length;
        let verifiedCount = 0;
        let pendingCount = 0;
        let paidCount = 0;
        let expiredCount = 0;

        statsRecords.forEach((record) => {
            if (record.isVerified) {
                verifiedCount++;
            } else {
                pendingCount++;
            }

            const currentSub = record.subscriptions?.[0];
            if (currentSub) {
                if (currentSub.status === "ACTIVE") {
                    paidCount++;
                } else if (currentSub.status === "EXPIRED") {
                    expiredCount++;
                }
            }
        });

        const data = students.map((si) => {
            const latestSub = (si as any).subscriptions?.[0] ?? null;
            const status = latestSub?.status && latestSub?.status !== "UNPAID" ? latestSub.status : null;
            return {
                id: si.id,
                studentName: si.student?.user
                    ? `${si.student.user.firstName} ${si.student.user.lastName}`.trim()
                    : "—",
                email: si.student?.user?.email || "—",
                phone: (si.student?.user as any)?.phone || "—",
                examName: si.exam?.examName || "—",
                language: si.language || "—",
                isVerified: si.isVerified,
                registrationDate: si.createdAt,
                subscriptionStatus: status,
                planName: latestSub?.plan?.planName || "—",
                expiryDate: latestSub?.endDate || null,
                subscription: latestSub ? {
                    status: latestSub.status,
                    amount: latestSub.amount,
                    startDate: latestSub.startDate,
                    endDate: latestSub.endDate,
                    trialEndsAt: latestSub.endDate,
                } : null,
                subscriptionPlan: latestSub?.plan ? {
                    planName: latestSub.plan.planName,
                    planType: latestSub.plan.planType,
                    duration: latestSub.plan.duration,
                } : null
            };
        });

        res.status(200).json({
            message: "Exam student stats fetched successfully",
            stats: {
                totalEnrolled,
                verifiedCount,
                pendingCount,
                paidCount,
                expiredCount,
            },
            data,
            meta: {
                total: totalStudents,
                page,
                limit,
                totalPages: Math.ceil(totalStudents / limit),
            },
        });
    }
);

// Get student all test result report ( reports and score tab )
export const getStudentPerformance = asyncHandler(
    async (req: Request, res: Response) => {
        const { studentId } = req.params; 
        const search = (req.query.search as string)?.trim() || "";
        const testType = req.query.testType as string | undefined;
        const subjectId = req.query.subjectId as string | undefined;
        const startDate = req.query.startDate as string | undefined;
        const endDate = req.query.endDate as string | undefined;
        const examId = req.query.examId as string | undefined;

        const sortBy = (req.query.sortBy as string) || "date";
        const sortOrder = (req.query.sortOrder as string)?.toLowerCase() === "asc" ? "asc" : "desc";

        let subjectName = "All Subjects";
        if (subjectId) {
            const subjectData = await prisma.subject.findUnique({
                where: { id: subjectId },
                select: { subjectName: true }
            });
            if (subjectData) {
                subjectName = subjectData.subjectName;
            }
        }

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

        const enrollments = await prisma.studentInstitution.findMany({
            where: {
                OR: [
                    { id: studentId },
                    { studentId: studentId }
                ]
            },
            include: {
                student: {
                    include: {
                        user: {
                            select: {
                                firstName: true,
                                lastName: true,
                                email: true,
                                phone: true,
                            }
                        }
                    }
                },
                exam: {
                    select: {
                        id: true,
                        examName: true,
                    }
                }
            },
            orderBy: {
                createdAt: "desc"
            }
        });

        if (enrollments.length === 0) {
            res.status(404).json({ message: "Student enrollment not found" });
            return;
        }

        let activeEnrollment = enrollments[0]; 

        const enrolledExams = enrollments
            .map(e => e.exam)
            .filter((exam): exam is { id: string; examName: string } => !!exam);

        let dateClause: any = undefined;
        if (startDate || endDate) {
            dateClause = {};
            if (startDate) {
                dateClause.gte = new Date(`${startDate}T00:00:00.000Z`);
            }
            if (endDate) {
                dateClause.lte = new Date(`${endDate}T23:59:59.999Z`);
            }
        }

        const dynamicTestType = subjectId ? "PracticeTest" : testType;

        const shouldFetchExams = !dynamicTestType || dynamicTestType === "ALL" || dynamicTestType === "PracticeTest" || dynamicTestType === "OldQuestionPaper";
        const shouldFetchMocks = (!dynamicTestType || dynamicTestType === "ALL" || dynamicTestType === "MockTest") && !subjectId;

        let rawExams: any[] = [];
        let rawMocks: any[] = [];

        const examWhere: any = { studentInstitutionId: { in: enrollments.map(e => e.id) } };
        if (dynamicTestType && dynamicTestType !== "ALL") {
            examWhere.testType = dynamicTestType;
        } else if (subjectId) {
            examWhere.testType = "PracticeTest";
        }
        if (dateClause) examWhere.createdAt = dateClause;

        const mockWhere: any = { studentInstitutionId: { in: enrollments.map(e => e.id) } };
        if (dateClause) mockWhere.createdAt = dateClause;

        if (examId) {
            examWhere.OR = [
                { practiceTest: { examId: examId } },
                { oldQuestionPaper: { examId: examId } },
            ];
            mockWhere.mockTest = { examId: examId };
        }

        await Promise.all([
            shouldFetchExams ? (async () => {
                rawExams = await prisma.examResult.findMany({
                    where: examWhere,
                    include: {
                        practiceTest: { select: { title: true, exam: { select: { id: true, examName: true } } } },
                        oldQuestionPaper: { select: { title: true, exam: { select: { id: true, examName: true } } } }
                    },
                    orderBy: { createdAt: "desc" }
                });
            })() : Promise.resolve(),

            shouldFetchMocks ? (async () => {
                rawMocks = await prisma.mockTestResult.findMany({
                    where: mockWhere,
                    include: {
                        mockTest: { select: { title: true, questionCount: true, exam: { select: { id: true, examName: true } } } }
                    },
                    orderBy: { createdAt: "desc" }
                });
            })() : Promise.resolve()
        ]);

        const allQuestionIds = new Set<string>();

        rawExams.forEach(r => {
            const answers = Array.isArray(r.answers) ? r.answers : [];
            answers.forEach((ans: any) => { if (ans.questionId) allQuestionIds.add(ans.questionId); });
        });
        rawMocks.forEach(r => {
            const answers = Array.isArray(r.answers) ? r.answers : [];
            answers.forEach((ans: any) => { if (ans.questionId) allQuestionIds.add(ans.questionId); });
        });

        const allQuestions = await prisma.questionBank.findMany({
            where: { id: { in: Array.from(allQuestionIds) } },
            include: { subject: { select: { subjectName: true } } }
        });

        const questionInfoMap = new Map(allQuestions.map(q => [
            q.id,
            { subjectId: q.subjectId, subjectName: q.subject?.subjectName || "Unknown" }
        ]));

        const processedResults: any[] = [];

        const mapResultItem = (r: any, isMock: boolean) => {
            if (subjectId && (isMock || r.testType !== "PracticeTest")) return;

            let testMarkPerQuestion = 1;
            if (isMock) {
                const totalM = r.mockTest?.totalMarks || 0;
                const totalQ = r.mockTest?.questionCount || 0;
                testMarkPerQuestion = (totalM > 0 && totalQ > 0) ? (totalM / totalQ) : 1;
            } else if (r.testType === "PracticeTest") {
                const totalM = r.practiceTest?.marks || 0;
                const totalQ = r.totalQuestions || 0;
                testMarkPerQuestion = (totalM > 0 && totalQ > 0) ? (totalM / totalQ) : 1;
            } else if (r.testType === "OldQuestionPaper") {
                const totalM = r.totalMarks || 0;
                const totalQ = r.totalQuestions || 0;
                testMarkPerQuestion = (totalM > 0 && totalQ > 0) ? (totalM / totalQ) : 1;
            }

            let newObtainedMarks = 0;
            let newTotalMarks = 0;
            let newTotalQuestions = 0;
            const subjectsInTest = new Set<string>();

            const answers = (Array.isArray(r.answers) ? r.answers : []) as any[];
            for (const ans of answers) {
                const qInfo = questionInfoMap.get(ans.questionId);
                if (qInfo) {
                    subjectsInTest.add(qInfo.subjectName);

                    if (subjectId && qInfo.subjectId !== subjectId) {
                        continue;
                    }

                    newTotalQuestions++;
                    newTotalMarks += testMarkPerQuestion;
                    if (ans.isCorrect) {
                        newObtainedMarks += testMarkPerQuestion;
                    }
                }
            }

            if (subjectId && newTotalQuestions === 0) return;

            let finalSubjectName = "";
            if (subjectId) {
                finalSubjectName = subjectName;
            } else {
                finalSubjectName = Array.from(subjectsInTest).join(", ");
                if (!finalSubjectName) {
                    finalSubjectName = isMock ? "Mock Test Stack" : (r.testType === "OldQuestionPaper" ? "Old Question Paper" : "Mixed");
                }
            }

            const defaultTitle = isMock ? (r.mockTest?.title || "Deleted Mock") : (r.practiceTest?.title || r.oldQuestionPaper?.title || "Deleted Test");
            const defaultQuestionsCount = isMock ? (r.mockTest?.questionCount || 0) : r.totalQuestions;

            const rowExam = isMock
                ? r.mockTest?.exam
                : (r.practiceTest?.exam || r.oldQuestionPaper?.exam);
            const rowExamName = rowExam?.examName || activeEnrollment.exam?.examName || "—";
            const rowExamId = rowExam?.id || activeEnrollment.examsId || "";

            const obtainedMarks = subjectId ? newObtainedMarks : r.obtainedMarks;
            const totalMarks = subjectId ? newTotalMarks : r.totalMarks;
            const scorePercentage = totalMarks > 0 ? (obtainedMarks / totalMarks) * 100 : 0;

            processedResults.push({
                id: r.id,
                testTitle: defaultTitle,
                displaySubjectName: finalSubjectName,
                testType: isMock ? "MockTest" : r.testType,
                obtainedMarks,
                totalMarks,
                totalQuestions: subjectId ? newTotalQuestions : defaultQuestionsCount,
                scorePercentage: Math.round(scorePercentage * 100) / 100,
                createdAt: r.createdAt,
                examName: rowExamName,
                examId: rowExamId
            });
        };

        rawExams.forEach(r => mapResultItem(r, false));
        rawMocks.forEach(r => mapResultItem(r, true));

        let filteredResults = processedResults;
        if (search) {
            const searchLower = search.toLowerCase();
            filteredResults = filteredResults.filter(r => r.testTitle.toLowerCase().includes(searchLower));
        }

        filteredResults.sort((a, b) => {
            let comparison = 0;

            switch (sortBy) {
                case "testTitle":
                    comparison = a.testTitle.localeCompare(b.testTitle);
                    break;
                case "score":
                case "percentage":
                    comparison = a.scorePercentage - b.scorePercentage;
                    break;
                case "date":
                default:
                    comparison = a.createdAt.getTime() - b.createdAt.getTime();
                    break;
            }

            return sortOrder === "asc" ? comparison : -comparison;
        });

        const total = filteredResults.length;
        const paginatedResults = filteredResults.slice(skip, skip + limit);

        const data = paginatedResults.map(r => ({
            id: r.id,
            testTitle: r.testTitle,
            subjectName: r.displaySubjectName,
            testType: r.testType,
            obtainedMarks: r.obtainedMarks,
            totalMarks: r.totalMarks,
            totalQuestions: r.totalQuestions,
            scorePercentage: r.scorePercentage,
            createdAt: r.createdAt,
            examName: r.examName,
            examId: r.examId
        }));

        res.status(200).json({
            student: {
                name: `${activeEnrollment.student.user.firstName} ${activeEnrollment.student.user.lastName}`.trim(),
                email: activeEnrollment.student.user.email || "—",
                phone: activeEnrollment.student.user.phone || "—",
                examName: activeEnrollment.exam?.examName || "—",
                examId: activeEnrollment.examsId
            },
            enrolledExams,
            data,
            meta: {
                total,
                page,
                limit,
                totalPages: Math.ceil(total / limit)
            }
        });
    }
);

// Individual student performance PDF export
export const studentPerformancePDF = asyncHandler(async (req: Request, res: Response) => {
    const { studentId } = req.params;
    const search = (req.query.search as string)?.trim() || "";
    const testType = req.query.testType as string | undefined;
    const subjectId = req.query.subjectId as string | undefined;
    const startDate = req.query.startDate as string | undefined;
    const endDate = req.query.endDate as string | undefined;
    const examId = req.query.examId as string | undefined;
    const sortBy = (req.query.sortBy as string) || "date";
    const sortOrder = (req.query.sortOrder as string)?.toLowerCase() === "asc" ? "asc" : "desc";

    const enrollments = await prisma.studentInstitution.findMany({
        where: { OR: [{ id: studentId }, { studentId }] },
        include: {
            student: { include: { user: { select: { firstName: true, lastName: true, email: true, phone: true } } } },
            exam: { select: { id: true, examName: true } }
        },
        orderBy: { createdAt: "desc" }
    });

    if (enrollments.length === 0) {
        res.status(404).json({ message: "Student enrollment not found" });
        return;
    }

    const activeEnrollment = enrollments[0];

    let institutionNameFromEnrollment: string | null = null;
    try {
        const inst = await prisma.institution.findUnique({
            where: { id: (activeEnrollment as any).institutionId },
            select: {
                user: {
                    select: {
                        institutionName: true,
                        firstName: true,
                        email: true,
                    }
                }
            }
        });
        institutionNameFromEnrollment = inst?.user?.institutionName || inst?.user?.firstName || inst?.user?.email || null;
    } catch (e) {
        institutionNameFromEnrollment = null;
    }

    let subjectName = "All Subjects";
    if (subjectId) {
        const subjectData = await prisma.subject.findUnique({ where: { id: subjectId }, select: { subjectName: true } });
        if (subjectData) subjectName = subjectData.subjectName;
    }

    let dateClause: any = undefined;
    if (startDate || endDate) {
        dateClause = {};
        if (startDate) dateClause.gte = new Date(`${startDate}T00:00:00.000Z`);
        if (endDate) dateClause.lte = new Date(`${endDate}T23:59:59.999Z`);
    }

    const dynamicTestType = subjectId ? "PracticeTest" : testType;
    const shouldFetchExams = !dynamicTestType || dynamicTestType === "ALL" || dynamicTestType === "PracticeTest" || dynamicTestType === "OldQuestionPaper";
    const shouldFetchMocks = (!dynamicTestType || dynamicTestType === "ALL" || dynamicTestType === "MockTest") && !subjectId;

    let rawExams: any[] = [];
    let rawMocks: any[] = [];

    const examWhere: any = { studentInstitutionId: { in: enrollments.map(e => e.id) } };
    if (dynamicTestType && dynamicTestType !== "ALL") examWhere.testType = dynamicTestType;
    if (dateClause) examWhere.createdAt = dateClause;

    const mockWhere: any = { studentInstitutionId: { in: enrollments.map(e => e.id) } };
    if (dateClause) mockWhere.createdAt = dateClause;

    if (examId) {
        examWhere.OR = [{ practiceTest: { examId } }, { oldQuestionPaper: { examId } }];
        mockWhere.mockTest = { examId };
    }

    await Promise.all([
        shouldFetchExams ? (async () => {
            rawExams = await prisma.examResult.findMany({
                where: examWhere,
                include: {
                    practiceTest: {
                        select: {
                            title: true,
                            exam: { select: { id: true, examName: true } },
                            test: {
                                select: {
                                    subject: {
                                        select: {
                                            subjectName: true
                                        }
                                    }
                                }
                            }
                        }
                    },
                    oldQuestionPaper: { select: { title: true, exam: { select: { id: true, examName: true } } } }
                },
                orderBy: { createdAt: "desc" }
            });
        })() : Promise.resolve(),
        shouldFetchMocks ? (async () => {
            rawMocks = await prisma.mockTestResult.findMany({
                where: mockWhere,
                include: { mockTest: { select: { title: true, questionCount: true, exam: { select: { id: true, examName: true } } } } },
                orderBy: { createdAt: "desc" }
            });
        })() : Promise.resolve()
    ]);

    const allQuestionIds = new Set<string>();
    [...rawExams, ...rawMocks].forEach(r => {
        (Array.isArray(r.answers) ? r.answers : []).forEach((ans: any) => { if (ans.questionId) allQuestionIds.add(ans.questionId); });
    });

    const allQuestions = await prisma.questionBank.findMany({
        where: { id: { in: Array.from(allQuestionIds) } },
        include: { subject: { select: { subjectName: true } } }
    });
    const questionInfoMap = new Map(allQuestions.map(q => [q.id, { subjectId: q.subjectId, subjectName: q.subject?.subjectName || "Unknown" }]));

    const processedResults: any[] = [];

    const mapItem = (r: any, isMock: boolean) => {
        if (subjectId && (isMock || r.testType !== "PracticeTest")) return;

        let testMarkPerQuestion = 1;
        if (isMock) {
            const totalM = r.mockTest?.totalMarks || 0;
            const totalQ = r.mockTest?.questionCount || 0;
            testMarkPerQuestion = (totalM > 0 && totalQ > 0) ? (totalM / totalQ) : 1;
        } else {
            const totalM = r.totalMarks || 0;
            const totalQ = r.totalQuestions || 0;
            testMarkPerQuestion = (totalM > 0 && totalQ > 0) ? (totalM / totalQ) : 1;
        }

        let newObtained = 0, newTotal = 0, newQuestions = 0;
        const subjectsInTest = new Set<string>();
        const answers = (Array.isArray(r.answers) ? r.answers : []) as any[];

        for (const ans of answers) {
            const qInfo = questionInfoMap.get(ans.questionId);
            if (qInfo) {
                subjectsInTest.add(qInfo.subjectName);
                if (subjectId && qInfo.subjectId !== subjectId) continue;
                newQuestions++;
                newTotal += testMarkPerQuestion;
                if (ans.isCorrect) newObtained += testMarkPerQuestion;
            }
        }
        if (subjectId && newQuestions === 0) return;

        const rowExam = isMock ? r.mockTest?.exam : (r.practiceTest?.exam || r.oldQuestionPaper?.exam);
        const obtainedMarks = subjectId ? newObtained : r.obtainedMarks;
        const totalMarks = subjectId ? newTotal : r.totalMarks;
        const pct = totalMarks > 0 ? (obtainedMarks / totalMarks) * 100 : 0;
        const subjectName = !isMock && r.practiceTest ? r.practiceTest.test?.subject?.subjectName : null;

        processedResults.push({
            testTitle: isMock ? (r.mockTest?.title || "Deleted Mock") : (r.practiceTest?.title || r.oldQuestionPaper?.title || "Deleted Test"),
            testType: isMock ? "MockTest" : r.testType,
            examName: rowExam?.examName || activeEnrollment.exam?.examName || "—",
            obtainedMarks,
            totalMarks,
            scorePercentage: Math.round(pct * 100) / 100,
            createdAt: r.createdAt,
            subjectName
        });
    };

    rawExams.forEach(r => mapItem(r, false));
    rawMocks.forEach(r => mapItem(r, true));

    let filtered = processedResults;
    if (search) {
        const sl = search.toLowerCase();
        filtered = filtered.filter(r => r.testTitle.toLowerCase().includes(sl));
    }

    filtered.sort((a, b) => {
        let cmp = 0;
        switch (sortBy) {
            case "testTitle": cmp = a.testTitle.localeCompare(b.testTitle); break;
            case "score":
            case "percentage": cmp = a.scorePercentage - b.scorePercentage; break;
            default: cmp = a.createdAt.getTime() - b.createdAt.getTime(); break;
        }
        return sortOrder === "asc" ? cmp : -cmp;
    });

    const studentName = `${activeEnrollment.student.user.firstName} ${activeEnrollment.student.user.lastName}`.trim();
    const studentEmail = activeEnrollment.student.user.email || "—";
    const studentPhone = activeEnrollment.student.user.phone || "—";
    const enrolledExam = activeEnrollment.exam?.examName || "—";

    const typeLabels: Record<string, string> = { PracticeTest: "PRACTICE", MockTest: "MOCK", OldQuestionPaper: "PYQ" };

    const rows = filtered.map((r, i) => {
        let typeStr = typeLabels[r.testType] || r.testType;
        if (r.testType === "PracticeTest" && r.subjectName) {
            typeStr = `PRACTICE TEST\n(${r.subjectName})`;
        } else if (r.testType === "MockTest") {
            typeStr = "MOCK TEST";
        } else if (r.testType === "OldQuestionPaper") {
            typeStr = "PYQ";
        }

        return [
            `${i + 1}`,
            r.examName,
            typeStr,
            r.testTitle,
            dayjs(r.createdAt).format("DD MMM YYYY, hh:mm A"),
            `${r.obtainedMarks} / ${r.totalMarks}`,
            `${r.scorePercentage.toFixed(1)}%`
        ];
    });

    const doc = new PDFDocument({ size: "A4", layout: "landscape", margin: 0 });
    res.setHeader("Content-Type", "application/pdf");
    res.setHeader("Content-Disposition", `attachment; filename=Student_Performance_${dayjs().format("YYYY-MM-DD")}.pdf`);
    doc.pipe(res);

    try {
        doc.registerFont('NotoSansTamil', path.join(process.cwd(), 'node_modules', '@expo-google-fonts', 'noto-sans-tamil', '400Regular', 'NotoSansTamil_400Regular.ttf'));
        doc.registerFont('NotoSansDevanagari', path.join(process.cwd(), 'node_modules', '@expo-google-fonts', 'noto-sans-devanagari', '400Regular', 'NotoSansDevanagari_400Regular.ttf'));

        const getFontForText = (text: string, isBold: boolean = false) => {
            const hasTamil = /[\u0B80-\u0BFF]/.test(text);
            const hasHindi = /[\u0900-\u097F]/.test(text);
            if (hasTamil) return "NotoSansTamil";
            if (hasHindi) return "NotoSansDevanagari";
            return isBold ? "Helvetica-Bold" : "Helvetica";
        };

        const primary = "#1976d2";
        const white = "#ffffff";
        const altRow = "#f5f7fa";
        const textColor = "#1f2937";
        const mutedText = "#cbd5e1";
        const margin = 24;
        const PW = doc.page.width;
        const PH = doc.page.height;

        const drawBg = () => {
            doc.save();
            doc.rect(0, 0, PW, PH).fill("#eef1f5");
            doc.roundedRect(margin - 6, margin - 6, PW - (margin - 6) * 2, PH - (margin - 6) * 2, 8).fill(white);
            doc.restore();
        };

        drawBg();
        doc.on("pageAdded", drawBg);

        const logoPath = path.resolve(process.cwd(), "public", "logo.png");
        const hasLogo = fs.existsSync(logoPath);
        const headerH = 66;

        doc.roundedRect(margin, margin, PW - margin * 2, headerH, 10).fill(primary);
        if (hasLogo) {
            doc.roundedRect(margin + 14, margin + 12, 40, 40, 6).fill(white);
            try { doc.image(logoPath, margin + 18, margin + 16, { fit: [32, 32], align: "center", valign: "center" }); } catch {}
        }

        const textX = hasLogo ? margin + 64 : margin + 16;
        doc.fillColor(white).font("Helvetica-Bold").fontSize(20).text("Exam Infra", textX, margin + 14);
        doc.font("Helvetica").fontSize(9).fillColor(mutedText).text("Student Performance Report", textX, margin + 38);
        if (institutionNameFromEnrollment) {
            doc.font(getFontForText(institutionNameFromEnrollment)).fontSize(9).fillColor(white).text(`Institution: ${institutionNameFromEnrollment}`, textX, margin + 52);
        }
        doc.font("Helvetica-Bold").fontSize(9).fillColor(white)
            .text(`Generated: ${dayjs().format("DD MMM YYYY, hh:mm A")}`, PW - margin - 230, margin + 14, { width: 220, align: "right" });
        doc.font("Helvetica").fontSize(9).fillColor(mutedText)
            .text(`Total Records: ${rows.length}`, PW - margin - 230, margin + 36, { width: 220, align: "right" });

        // Student info band
        const infoY = margin + headerH + 6;
        const infoH = 24;
        doc.rect(margin, infoY, PW - margin * 2, infoH).fill("#e8f0fe");
        doc.font(getFontForText(studentName, true)).fontSize(8.5).fillColor("#1e3a5f")
            .text(`Student: ${studentName}`, margin + 10, infoY + 6, { continued: true })
            .font("Helvetica").fillColor("#374151")
            .text(`   ${studentEmail}   •   ${studentPhone}`);

        const tableTop = infoY + infoH + 8;
        const tableLeft = margin;
        const tableWidth = PW - margin * 2;
        const baseWidths = [35, 90, 100, 160, 120, 70, 70];
        const baseTotal = baseWidths.reduce((a, b) => a + b, 0);
        const colWidths = baseWidths.map(w => (w / baseTotal) * tableWidth);
        const headers = ["S.No", "Exam", "Test Type", "Test Title", "Date", "Score", "Percentage"];
        const headerRowH = 26;
        const cellFontSize = 8.5;
        const rowPad = 6;

        const drawTableHeader = (y: number) => {
            doc.rect(tableLeft, y, tableWidth, headerRowH).fill(primary);
            doc.font("Helvetica-Bold").fontSize(9).fillColor(white);
            let hx = tableLeft;
            headers.forEach((h, i) => {
                const th = doc.heightOfString(h, { width: colWidths[i] - 10 });
                doc.text(h, hx + 5, y + (headerRowH - th) / 2, { width: colWidths[i] - 10, align: "left" });
                hx += colWidths[i];
            });
            return y + headerRowH;
        };

        let rowY = drawTableHeader(tableTop);

        rows.forEach((row, idx) => {
            const rowH = Math.max(28, ...row.map((cell, ci) => {
                doc.font(getFontForText(String(cell))).fontSize(cellFontSize);
                return doc.heightOfString(String(cell), { width: colWidths[ci] - 10 }) + rowPad * 2;
            }));

            if (rowY + rowH > PH - margin - 20) {
                doc.addPage();
                rowY = margin + 8;
                rowY = drawTableHeader(rowY);
            }

            doc.rect(tableLeft, rowY, tableWidth, rowH).fill(idx % 2 === 0 ? altRow : white);
            let rx = tableLeft;
            row.forEach((cell, ci) => {
                doc.font(getFontForText(String(cell))).fontSize(cellFontSize).fillColor(textColor);
                const th = doc.heightOfString(String(cell), { width: colWidths[ci] - 10 });
                doc.text(String(cell), rx + 5, rowY + (rowH - th) / 2, { width: colWidths[ci] - 10 });
                rx += colWidths[ci];
            });
            rowY += rowH;
        });

        doc.end();
    } catch (err) {
        console.error("studentPerformancePDF - PDF Generation Error:", err);
        try {
            if (!res.headersSent) {
                res.status(500).json({ message: "Failed to generate PDF report" });
            }
        } catch (resErr) {
            console.error("Failed to send 500 error response:", resErr);
        }
        try {
            doc.destroy();
        } catch (docErr) {
            console.error("Failed to destroy doc:", docErr);
        }
    }
});

// Students report pdf export
export const studentReportsPDF = asyncHandler(async (req: Request, res: Response) => {
    const user = req.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 search = (req.query.search as string)?.trim() || "";
    const planStatus = (req.query.planStatus as string)?.trim();
    const planType = (req.query.planType as string)?.trim();
    const planName = (req.query.planName as string)?.trim();
    const isVerified = (req.query.isVerified as string)?.trim();
    const language = (req.query.language as string)?.trim();
    const regStartDate = (req.query.regStartDate as string)?.trim();
    const regEndDate = (req.query.regEndDate as string)?.trim();

    let institutionNameFromRequest: string | null = null;
    if (institutionId) {
        const institutionRecord = await prisma.institution.findUnique({
            where: { id: institutionId },
            select: {
                user: {
                    select: {
                        institutionName: true,
                        firstName: true,
                        email: true,
                    }
                }
            }
        });
        institutionNameFromRequest = institutionRecord?.user?.institutionName || institutionRecord?.user?.firstName || institutionRecord?.user?.email || null;
    }

    const statsWhere: any = {};
    if (institutionId) statsWhere.institutionId = institutionId;
    if (examId) statsWhere.examsId = examId;

    const where: any = { ...statsWhere };

    if (search) {
        where.student = {
            user: {
                OR: [
                    { firstName: { contains: search } },
                    { lastName: { contains: search } },
                    { email: { contains: search } },
                    { phone: { contains: search } },
                ],
            },
        };
    }

    if (isVerified) {
        where.isVerified = isVerified === "true";
    }

    if (language) {
        where.language = language;
    }

    if (planStatus || planName || planType) {
        if (planStatus === "UNPAID") {
            where.subscriptions = { none: { isCurrent: true } };
        } else {
            const subConditions: any = { isCurrent: true };
            if (planStatus && planStatus !== "ALL") {
                subConditions.status = planStatus.toUpperCase();
            }
            if (planName || planType) {
                subConditions.plan = {};
                if (planName) subConditions.plan.planName = planName;
                if (planType) subConditions.plan.planType = planType;
            }
            where.subscriptions = {
                some: subConditions,
            };
        }
    }

    if (regStartDate || regEndDate) {
        where.student = where.student || {};
        where.student.createdAt = {};

        const convertToISOFormat = (dateStr: string): string => {
            const parts = dateStr.split('/');
            if (parts.length === 3) {
                const [day, month, year] = parts;
                return `${year}-${month}-${day}`;
            }
            return dateStr;
        };

        if (regStartDate) {
            const formattedStartDate = convertToISOFormat(regStartDate);
            where.student.createdAt.gte = new Date(`${formattedStartDate}T00:00:00.000Z`);
        }
        if (regEndDate) {
            const formattedEndDate = convertToISOFormat(regEndDate);
            where.student.createdAt.lte = new Date(`${formattedEndDate}T23:59:59.999Z`);
        }
    }

    const studentInstitutions = await prisma.studentInstitution.findMany({
        where,
        include: {
            student: {
                include: {
                    user: {
                        select: {
                            firstName: true,
                            lastName: true,
                            email: true,
                            phone: true,
                        },
                    },
                },
            },
            exam: { select: { examName: true } },
            institution: {
                select: {
                    user: {
                        select: {
                            institutionName: true,
                            firstName: true,
                            email: true,
                        },
                    },
                },
            },
            subscriptions: {
                where: { isCurrent: true },
                select: {
                    status: true,
                    endDate: true,
                    plan: { select: { planName: true } },
                },
            },
        },
        orderBy: { createdAt: 'desc' },
    });

    const rows = studentInstitutions.map((si, index) => {
        const latestSub = (si as any).subscriptions?.[0] ?? null;
        const studentName = si.student?.user
            ? `${si.student.user.firstName} ${si.student.user.lastName}`.trim()
            : "—";
        const studentEmail = si.student?.user?.email || "—";
        const studentInfo = `${studentName || "—"}\n${studentEmail}`;
        const status = latestSub?.status && latestSub?.status !== "UNPAID" ? latestSub.status : "";

        return [
            `${index + 1}`,
            studentInfo,
            si.student?.user?.phone || "—",
            si.exam?.examName || "—",
            si.language || "—",
            si.isVerified ? "Verified" : "Pending",
            status,
            latestSub?.plan?.planName || "—",
            latestSub?.endDate ? dayjs(latestSub.endDate).format("DD/MM/YYYY hh:mm A") : "—",
            dayjs(si.createdAt).format("DD/MM/YYYY hh:mm A"),
        ];
    });

    const doc = new PDFDocument({ size: "A4", layout: "landscape", margin: 24 });
    res.setHeader("Content-Type", "application/pdf");
    res.setHeader("Content-Disposition", `attachment; filename=Exam_Student_Report_${dayjs().format("YYYY-MM-DD")}.pdf`);
    doc.pipe(res);

    const white = "#ffffff";
    const primary = "#1976d2";
    const textColor = "#1f2937";
    const altRow = "#f5f7fa";
    const mutedText = "#cbd5e1";
    const margin = 24;
    const headerH = 66;
    const tableTop = margin + headerH + 14;
    const tableWidth = doc.page.width - margin * 2;
    const baseWidths = [50, 200, 85, 105, 80, 65, 85, 95, 75, 85];
    const totalBaseWidth = baseWidths.reduce((sum, value) => sum + value, 0);
    const columnWidths = baseWidths.map((value) => Math.round((value / totalBaseWidth) * tableWidth));
    columnWidths[columnWidths.length - 1] = tableWidth - columnWidths.slice(0, -1).reduce((sum, value) => sum + value, 0);

    const headers = [
        "S.No",
        "Student Info",
        "Phone",
        "Exam",
        "Language",
        "Verified",
        "Status",
        "Plan",
        "Expiry",
        "Registered",
    ];

    const logoPath = path.resolve(process.cwd(), "public", "logo.png");
    const hasLogo = fs.existsSync(logoPath);

    const drawPageBackground = () => {
        doc.save();
        doc.rect(0, 0, doc.page.width, doc.page.height).fill("#eef1f5");
        doc.roundedRect(margin - 6, margin - 6, doc.page.width - (margin - 6) * 2, doc.page.height - (margin - 6) * 2, 8).fill(white);
        doc.restore();
    };

    const drawHeader = () => {
        doc.roundedRect(margin, margin, tableWidth, headerH, 10).fill(primary);

        if (hasLogo) {
            doc.roundedRect(margin + 14, margin + 13, 40, 40, 6).fill(white);
            try {
                doc.image(logoPath, margin + 18, margin + 17, { fit: [32, 32], align: "center", valign: "center" });
            } catch (imgError) {
                console.error("Failed to render logo image:", imgError);
            }
        }

        const textStartX = hasLogo ? margin + 64 : margin + 20;
        doc.font("Helvetica-Bold").fontSize(20).fillColor(white).text("Exam Infra", textStartX, margin + 14);
        doc.font("Helvetica").fontSize(9).fillColor(mutedText).text("Student Report", textStartX, margin + 38);

        if (institutionNameFromRequest) {
            doc.font("Helvetica").fontSize(9).fillColor(white).text(`Institution: ${institutionNameFromRequest}`, textStartX, margin + 52);
        }

        doc.font("Helvetica-Bold").fontSize(9).fillColor(white)
            .text(`Generated: ${dayjs().format("DD MMM YYYY, hh:mm A")}`, doc.page.width - margin - 240, margin + 14, { width: 220, align: "right" });
        doc.font("Helvetica").fontSize(9).fillColor(mutedText)
            .text(`Total Records: ${rows.length}`, doc.page.width - margin - 240, margin + 36, { width: 220, align: "right" });
    };

    const drawTableHeader = (y: number) => {
        doc.rect(margin, y, tableWidth, 28).fill(primary);
        doc.font("Helvetica-Bold").fontSize(9).fillColor(white);

        let x = margin;
        headers.forEach((header, idx) => {
            doc.text(header, x + 8, y + 8, { width: columnWidths[idx] - 16, align: "left" });
            x += columnWidths[idx];
        });

        return y + 28;
    };

    drawPageBackground();
    drawHeader();

    let rowY = drawTableHeader(tableTop);
    const rowFontSize = 8.5;
    const rowPadding = 6;
    const maxY = doc.page.height - margin - 20;

    doc.on("pageAdded", () => {
        drawPageBackground();
        rowY = drawTableHeader(tableTop);
    });

    rows.forEach((row, index) => {
        doc.font("Helvetica").fontSize(rowFontSize);
        const rowHeight = Math.max(
            28,
            ...row.map((cell, idx) => {
                return doc.heightOfString(String(cell), { width: columnWidths[idx] - 16 }) + rowPadding * 2;
            }),
        );

        if (rowY + rowHeight > maxY) {
            doc.addPage();
        }

        doc.rect(margin, rowY, tableWidth, rowHeight).fill(index % 2 === 0 ? altRow : white);
        doc.moveTo(margin, rowY + rowHeight).lineTo(margin + tableWidth, rowY + rowHeight);

        let x = margin;
        row.forEach((cell, idx) => {
            doc.fillColor(textColor).font("Helvetica").fontSize(rowFontSize);
            doc.text(String(cell), x + 8, rowY + rowPadding, {
                width: columnWidths[idx] - 16,
                align: "left",
            });
            x += columnWidths[idx];
        });

        rowY += rowHeight;
    });

    doc.rect(margin, tableTop, tableWidth, rowY - tableTop);
    doc.end();
});

// Institution list for admin
export const getInstitutionsList = asyncHandler(
    async (req: Request, res: Response) => {
        const search = (req.query.search as string)?.trim() || "";
        const page = parseInt((req.query.page as string) || "1");
        const limitStr = req.query.limit as string;
        const limit = limitStr !== undefined ? parseInt(limitStr) : 10;
        const skip = limit > 0 ? (page - 1) * limit : 0;

        const where: any = {};
        if (search) {
            where.user = {
                ...(where.user || {}),
                OR: [
                    { institutionName: { contains: search } },
                    { firstName: { contains: search } },
                    { email: { contains: search } },
                ],
            };
        }

        const isVerifiedStr = req.query.isVerified as string;
        if (isVerifiedStr !== undefined) {
            where.user = {
                ...(where.user || {}),
                isVerified: isVerifiedStr === 'true'
            };
        }

        const [institutions, total] = await Promise.all([
            prisma.institution.findMany({
                where,
                select: {
                    id: true,
                    user: { select: { institutionName: true, firstName: true, email: true } },
                },
                skip,
                take: limit > 0 ? limit : undefined,
                orderBy: { createdAt: "desc" },
            }),
            prisma.institution.count({ where }),
        ]);

        const data = institutions.map((inst) => ({
            id: inst.id,
            name: inst.user?.institutionName || inst.user?.firstName || inst.user?.email || "—",
        }));

        res.status(200).json({
            data,
            meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
        });
    }
);

// Helpers for 90-day week calculations
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 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);
};

// Get student practice test weekly trend report for specific exams ( 90 Days )
export const getStudentPracticeWeeklyTrend = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;

    let studentId = req.params.studentId;
    if (!studentId) {
        const viewStudentId = req.query.viewStudentId as string | undefined;
        if (viewStudentId && ["ADMIN", "INSTITUTION", "STAFF"].includes(user.role.toUpperCase())) {
            studentId = viewStudentId;
        } else {
            studentId = user.studentInstitutionId;
        }
    }

    if (!studentId) {
        res.status(400).json({ message: "Student institution ID missing or invalid student" });
        return;
    }

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

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

    const enrollments = await prisma.studentInstitution.findMany({
        where: {
            OR: [
                { id: studentId },
                { studentId: studentId }
            ]
        },
        include: { exam: true },
        orderBy: { createdAt: "desc" }
    });

    if (enrollments.length === 0) {
        res.status(404).json({ message: "Student profile records not found" });
        return;
    }

    let activeEnrollment = enrollments[0];
    
    const targetIds = enrollments.map(e => e.id);
    const currentExamId = examId || activeEnrollment.examsId;

    const subjectId = req.query.subjectId as string | undefined;
    const filterBySubject = subjectId && subjectId !== "all";

    const whereClause: any = {
        studentInstitutionId: { in: targetIds },
        practiceTestId: { not: null },
        createdAt: { gte: ninetyDaysAgo },
        practiceTest: {
            examId: currentExamId
        }
    };

    let subjectName = undefined;

    if (filterBySubject) {
        const subject = await prisma.subject.findUnique({
            where: { id: subjectId },
            select: { subjectName: true }
        });
        if (subject) {
            subjectName = subject.subjectName;
        }
    }

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

    let questionInfoMap: Map<string, { subjectId: string }> | null = null;
    if (filterBySubject) {
        const allQuestionIds = new Set<string>();
        results.forEach(r => {
            const answers = Array.isArray(r.answers) ? r.answers : [];
            answers.forEach((ans: any) => { if (ans.questionId) allQuestionIds.add(ans.questionId); });
        });
        if (allQuestionIds.size > 0) {
            const questions = await prisma.questionBank.findMany({
                where: { id: { in: Array.from(allQuestionIds) } },
                select: { id: true, subjectId: true, marks: true }
            });
            questionInfoMap = new Map(questions.map(q => [q.id, { subjectId: q.subjectId }]));
        }
    }

    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);
        if (!weeklyDataMap[weekLabel]) return;

        let scorePercentage: number;

        if (filterBySubject && questionInfoMap) {
            let testMarkPerQuestion = 1;
            const isMock = (r as any).testType !== "PracticeTest" && (r as any).testType !== "OldQuestionPaper";
            if (isMock) {
                const totalM = (r as any).mockTest?.totalMarks || 0;
                const totalQ = (r as any).mockTest?.questionCount || 0;
                testMarkPerQuestion = (totalM > 0 && totalQ > 0) ? (totalM / totalQ) : 1;
            } else {
                const totalM = r.totalMarks || 0;
                const totalQ = r.totalQuestions || 0;
                testMarkPerQuestion = (totalM > 0 && totalQ > 0) ? (totalM / totalQ) : 1;
            }

            const answers = (Array.isArray(r.answers) ? r.answers : []) as any[];
            let subjectObtained = 0;
            let subjectTotal = 0;
            for (const ans of answers) {
                const qInfo = questionInfoMap.get(ans.questionId);
                if (qInfo && qInfo.subjectId === subjectId) {
                    subjectTotal += testMarkPerQuestion;
                    if (ans.isCorrect) subjectObtained += testMarkPerQuestion;
                }
            }
            if (subjectTotal === 0) return;
            scorePercentage = (subjectObtained / subjectTotal) * 100;
        } else {
            const totalMarks = r.totalMarks || 100;
            scorePercentage = (r.obtainedMarks / totalMarks) * 100;
        }

        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: "Practice test report fetched successfully",
        period: {
            from: ninetyDaysAgo.toISOString().split('T')[0],
            to: new Date().toISOString().split('T')[0],
            totalDays: 90
        },
        data: {
            examId: activeEnrollment?.examsId || "",
            examName: activeEnrollment?.exam?.examName || "—",
            ...(subjectId && subjectId !== "all" ? { subjectId, subjectName } : {}),
            graphData
        }
    });
});

// Get student practice test subject-wise average report for specific exams ( 90 Days )
export const getStudentPracticeSubjectAvg = asyncHandler(async (req: Request, res: Response) => {
    const { studentId } = req.params;   
    const sortParam = (req.query.sort as string)?.toLowerCase();
    const sortOrder = sortParam === "asc" ? "asc" : "desc";

    const page = Math.max(1, parseInt(req.query.page as string) || 1);
    const limit = Math.max(1, parseInt(req.query.limit as string) || 10);
    const startIndex = (page - 1) * limit;
    const endIndex = page * limit;

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

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

    const enrollments = await prisma.studentInstitution.findMany({
        where: {
            OR: [
                { id: studentId },
                { studentId: studentId }
            ]
        },
        include: { exam: true },
        orderBy: { createdAt: "desc" }
    });

    if (enrollments.length === 0) {
        res.status(404).json({ message: "Student profile records not found" });
        return;
    }

    let studentInst = enrollments[0];
    
    const targetIds = enrollments.map(e => e.id);
    const currentExamId = examIdQuery || studentInst.examsId;
    const language = studentInst.language || "English";

    let availableSubjects: { id: string; subjectName: string }[] = [];
    if (currentExamId) {
        availableSubjects = await prisma.subject.findMany({
            where: {
                OR: [
                    {
                        questions: {
                            some: {
                                practiceTests: {
                                    some: {
                                        examId: currentExamId, language: { in: [language, language.toLowerCase(), language.toUpperCase()] }
                                    }
                                }
                            }
                        }
                    },
                    {
                        questions: {
                            some: {
                                testQuestions: {
                                    some: {
                                        test: {
                                            practiceTests: {
                                                some: {
                                                    examId: currentExamId,
                                                    language: { in: [language, language.toLowerCase(), language.toUpperCase()] }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    },
                    {
                        tests: {
                            some: {
                                practiceTests: {
                                    some: {
                                        examId: currentExamId,
                                        language: { in: [language, language.toLowerCase(), language.toUpperCase()] }
                                    }
                                }
                            }
                        }
                    }
                ]
            },
            select: { 
                id: true, 
                subjectName: true,
                institutionId: true,
                institution: { select: { user: { select: { institutionName: true } } } }
            }
        });
    }

    const results = await prisma.examResult.findMany({
        where: {
            studentInstitutionId: { in: targetIds },
            practiceTestId: { not: null },
            createdAt: { gte: ninetyDaysAgo },
            practiceTest: {
                examId: currentExamId
            }
        },
        include: {
            practiceTest: {
                include: {
                    questions: {
                        select: { subject: { select: { subjectName: true } } }
                    },
                    test: {
                        select: { subject: { select: { subjectName: true } } }
                    }
                }
            }
        }
    });

    const subjectStats: Record<string, { totalMarks: number; obtainedMarks: number; hasSubmissions: boolean }> = {};
    const subjectIdMap: Record<string, { id: string, isShared: boolean, sharedInstitutionName: string | null }> = {};   
    
    const user = (req as any).user;
    
    let publishedSubjects: any[] = [];
    if (currentExamId) {
        publishedSubjects = await prisma.subject.findMany({
            where: {
                OR: [
                    {
                        questions: {
                            some: {
                                practiceTests: {
                                    some: {
                                        examId: currentExamId, publish: true, language: { in: [language, language.toLowerCase(), language.toUpperCase()] }
                                    }
                                }
                            }
                        }
                    },
                    {
                        questions: {
                            some: {
                                testQuestions: {
                                    some: {
                                        test: {
                                            practiceTests: {
                                                some: {
                                                    examId: currentExamId, publish: true, language: { in: [language, language.toLowerCase(), language.toUpperCase()] }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    },
                    {
                        tests: {
                            some: {
                                practiceTests: {
                                    some: {
                                        examId: currentExamId, publish: true, language: { in: [language, language.toLowerCase(), language.toUpperCase()] }
                                    }
                                }
                            }
                        }
                    }
                ]
            },
            select: { subjectName: true }
        });
    }

    const publishedSubjectNames = new Set(publishedSubjects.map(s => s.subjectName));
    
    availableSubjects.forEach((s: any) => {
        const isShared = user.role !== "ADMIN" && s.institutionId !== user.institutionId;
        const sharedInstitutionName = isShared ? s.institution?.user?.institutionName : null;

        if (publishedSubjectNames.has(s.subjectName)) {
            subjectStats[s.subjectName] = { totalMarks: 0, obtainedMarks: 0, hasSubmissions: false };
        }
        subjectIdMap[s.subjectName] = { id: s.id, isShared, sharedInstitutionName };
    });

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

        const uniqueSubjectNames = new Set<string>();

        if (r.practiceTest.test?.subject?.subjectName) {
            uniqueSubjectNames.add(r.practiceTest.test.subject.subjectName);
        } else if (r.practiceTest.questions) {
            r.practiceTest.questions.forEach((q: any) => {
                if (q.subject?.subjectName) uniqueSubjectNames.add(q.subject.subjectName);
            });
        }

        uniqueSubjectNames.forEach(subjectName => {
            if (!subjectStats[subjectName]) {
                subjectStats[subjectName] = { totalMarks: 0, obtainedMarks: 0, hasSubmissions: false };
            }
            subjectStats[subjectName].hasSubmissions = true;
            subjectStats[subjectName].totalMarks += r.totalMarks || 100;
            subjectStats[subjectName].obtainedMarks += r.obtainedMarks || 0;
        });
    });

    let fullChartData = Object.entries(subjectStats).map(([subject, stats]) => {
        const subInfo = subjectIdMap[subject] || { id: null, isShared: false, sharedInstitutionName: null };
        if (!stats.hasSubmissions) {
            return { 
                subject, 
                subjectId: subInfo.id, 
                isShared: subInfo.isShared, 
                sharedInstitutionName: subInfo.sharedInstitutionName, 
                percentage: null, 
                hasSubmissions: false 
            };
        }
        const percentage = stats.totalMarks > 0 ? (stats.obtainedMarks / stats.totalMarks) * 100 : 0;
        return {
            subject,
            subjectId: subInfo.id,
            isShared: subInfo.isShared,
            sharedInstitutionName: subInfo.sharedInstitutionName,
            percentage: Math.round(percentage * 100) / 100,
            hasSubmissions: true
        };
    });

    fullChartData.sort((a, b) => {
        const aMissing = a.percentage === null || a.percentage === undefined;
        const bMissing = b.percentage === null || b.percentage === undefined;

        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 = fullChartData.length;
    const totalPages = Math.ceil(totalSubjects / limit);
    const paginatedChartData = fullChartData.slice(startIndex, endIndex);

    res.status(200).json({
        message: "Subjects report fetched successfully",
        timeframe: "Last 90 Days",
        pagination: {
            totalSubjects,
            totalPages,
            currentPage: page,
            limit
        },
        data: {
            examId: studentInst?.examsId || "",
            examName: studentInst?.exam?.examName || "—",
            currentSortOrder: sortOrder,
            chartData: paginatedChartData
        }
    });
});

// Get student mock test weekly trend ( 90 Days )
export const getStudentMockWeeklyTrend = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;

    let studentId = req.params.studentId;
    if (!studentId) {
        const viewStudentId = req.query.viewStudentId as string | undefined;
        if (viewStudentId && ["ADMIN", "INSTITUTION", "STAFF"].includes(user.role.toUpperCase())) {
            studentId = viewStudentId;
        } else {
            studentId = user.studentInstitutionId;
        }
    }

    if (!studentId) {
        res.status(400).json({ message: "Student institution ID missing or invalid student" });
        return;
    }

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

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

    const enrollments = await prisma.studentInstitution.findMany({
        where: {
            OR: [
                { id: studentId },
                { studentId: studentId }
            ]
        },
        include: { exam: true },
        orderBy: { createdAt: "desc" }
    });

    if (enrollments.length === 0) {
        res.status(404).json({ message: "Student profile records not found" });
        return;
    }

    const targetIds = enrollments.map(e => e.id);
    const currentExamId = examId || enrollments[0].examsId;
    const activeEnrollment = enrollments.find(e => e.examsId === currentExamId) || enrollments[0];

    const results = await prisma.mockTestResult.findMany({
        where: {
            studentInstitutionId: { in: targetIds },
            createdAt: { gte: ninetyDaysAgo },
            mockTest: {
                examId: currentExamId
            }
        },
        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 = (r.obtainedMarks / totalMarks) * 100;
        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: "Mock test report fetched successfully",
        period: {
            from: ninetyDaysAgo.toISOString().split('T')[0],
            to: new Date().toISOString().split('T')[0],
            totalDays: 90
        },
        data: {
            examId: activeEnrollment?.examsId || "",
            examName: activeEnrollment?.exam?.examName || "—",
            graphData
        }
    });
});

// Get student pyq test weekly trend ( 90 Days )
export const getStudentPyqWeeklyTrend = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;

    let studentId = req.params.studentId;
    if (!studentId) {
        const viewStudentId = req.query.viewStudentId as string | undefined;
        if (viewStudentId && ["ADMIN", "INSTITUTION", "STAFF"].includes(user.role.toUpperCase())) {
            studentId = viewStudentId;
        } else {
            studentId = user.studentInstitutionId;
        }
    }

    if (!studentId) {
        res.status(400).json({ message: "Student institution ID missing or invalid student" });
        return;
    }

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

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

    const enrollments = await prisma.studentInstitution.findMany({
        where: {
            OR: [
                { id: studentId },
                { studentId: studentId }
            ]
        },
        include: { exam: true },
        orderBy: { createdAt: "desc" }
    });

    if (enrollments.length === 0) {
        res.status(404).json({ message: "Student profile records not found" });
        return;
    }

    let activeEnrollment = enrollments[0];
    
    const targetIds = enrollments.map(e => e.id);
    const currentExamId = examId || activeEnrollment.examsId;

    const results = await prisma.examResult.findMany({
        where: {
            studentInstitutionId: { in: targetIds },
            oldQuestionPaperId: { not: null },
            createdAt: { gte: ninetyDaysAgo },
            oldQuestionPaper: {
                examId: currentExamId
            }
        },
        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 = (r.obtainedMarks / totalMarks) * 100;
        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: "PYQ report fetched successfully",
        period: {
            from: ninetyDaysAgo.toISOString().split('T')[0],
            to: new Date().toISOString().split('T')[0],
            totalDays: 90
        },
        data: {
            examId: activeEnrollment?.examsId || "",
            examName: activeEnrollment?.exam?.examName || "—",
            graphData
        }
    });
});

export const staffsReportPDF = asyncHandler(async (req: Request, res: Response) => {
    const user = req.user;
    const isAdmin = user.role === "ADMIN";

    const institutionId = isAdmin
        ? (req.query.institutionId as string | undefined)
        : user.institutionId;
    const search = (req.query.search as string)?.trim() || "";
    const isVerified = (req.query.isVerified as string)?.trim();
    const regStartDate = (req.query.regStartDate as string)?.trim();
    const regEndDate = (req.query.regEndDate as string)?.trim();

    let institutionNameFromRequest: string | null = null;
    if (institutionId) {
        const institutionRecord = await prisma.institution.findUnique({
            where: { id: institutionId },
            select: {
                user: {
                    select: {
                        institutionName: true,
                        firstName: true,
                        email: true,
                    }
                }
            }
        });
        institutionNameFromRequest = institutionRecord?.user?.institutionName || institutionRecord?.user?.firstName || institutionRecord?.user?.email || null;
    }

    const where: any = {
        user: { isDeleted: false },
    };

    if (institutionId) {
        where.institutionId = institutionId;
    }

    if (search) {
        const searchWords = search.split(/\s+/).filter(Boolean);
        where.OR = [
            { user: { firstName: { contains: search } } },
            { user: { lastName: { contains: search } } },
            { user: { email: { contains: search } } },
            { user: { phone: { contains: search } } },
            { role: { roleName: { contains: search } } },
            ...(searchWords.length > 1 ? [{
                user: {
                    AND: searchWords.map(word => ({
                        OR: [
                            { firstName: { contains: word } },
                            { lastName: { contains: word } }
                        ]
                    }))
                }
            }] : [])
        ];
    }

    if (isVerified) {
        where.user = where.user || {};
        where.user.isVerified = isVerified === "true";
    }

    if (regStartDate || regEndDate) {
        where.user = where.user || {};
        where.user.createdAt = {};

        const convertToISOFormat = (dateStr: string): string => {
            const parts = dateStr.split('/');
            if (parts.length === 3) {
                const [day, month, year] = parts;
                return `${year}-${month}-${day}`;
            }
            return dateStr;
        };

        if (regStartDate) {
            const formattedStartDate = convertToISOFormat(regStartDate);
            where.user.createdAt.gte = new Date(`${formattedStartDate}T00:00:00.000Z`);
        }
        if (regEndDate) {
            const formattedEndDate = convertToISOFormat(regEndDate);
            where.user.createdAt.lte = new Date(`${formattedEndDate}T23:59:59.999Z`);
        }
    }

    const staffs = await prisma.staff.findMany({
        where,
        include: {
            user: true,
            role: true,
            institution: {
                select: {
                    user: {
                        select: {
                            institutionName: true,
                            firstName: true,
                            email: true,
                        },
                    },
                },
            },
        },
        orderBy: { createdAt: 'desc' },
    });

    const rows = staffs.map((staff, index) => {
        const staffName = `${staff.user?.firstName || ""} ${staff.user?.lastName || ""}`.trim() || "—";
        const staffEmail = staff.user?.email || "—";
        const staffInfo = `${staffName}\n${staffEmail}`;
        const joinedValue = staff.createdAt;

        return [
            `${index + 1}`,
            staffInfo,
            staff.user?.phone || "—",
            staff.role?.roleName || "Staff",
            staff.user?.isVerified ? "Verified" : "Pending",
            joinedValue ? dayjs(joinedValue).format("DD/MM/YYYY hh:mm A") : "—",
        ];
    });

    const doc = new PDFDocument({ size: "A4", layout: "landscape", margin: 24 });
    res.setHeader("Content-Type", "application/pdf");
    res.setHeader("Content-Disposition", `attachment; filename=Exam_Staff_Report_${dayjs().format("YYYY-MM-DD")}.pdf`);
    doc.pipe(res);

    const white = "#ffffff";
    const primary = "#1976d2";
    const textColor = "#1f2937";
    const altRow = "#f5f7fa";
    const mutedText = "#cbd5e1";
    const margin = 24;
    const headerH = 66;
    const tableTop = margin + headerH + 14;
    const tableWidth = doc.page.width - margin * 2;
    const baseWidths = [40, 200, 100, 100, 80, 120];
    const totalBaseWidth = baseWidths.reduce((sum, value) => sum + value, 0);
    const columnWidths = baseWidths.map((value) => Math.round((value / totalBaseWidth) * tableWidth));
    columnWidths[columnWidths.length - 1] = tableWidth - columnWidths.slice(0, -1).reduce((sum, value) => sum + value, 0);

    const headers = [
        "S.No",
        "Staff Info",
        "Phone",
        "Role",
        "Status",
        "Joined",
    ];

    const logoPath = path.resolve(process.cwd(), "public", "logo.png");
    const hasLogo = fs.existsSync(logoPath);

    const drawPageBackground = () => {
        doc.save();
        doc.rect(0, 0, doc.page.width, doc.page.height).fill("#eef1f5");
        doc.roundedRect(margin - 6, margin - 6, doc.page.width - (margin - 6) * 2, doc.page.height - (margin - 6) * 2, 8).fill(white);
        doc.restore();
    };

    const drawHeader = () => {
        doc.roundedRect(margin, margin, tableWidth, headerH, 10).fill(primary);

        if (hasLogo) {
            doc.roundedRect(margin + 14, margin + 13, 40, 40, 6).fill(white);
            try {
                doc.image(logoPath, margin + 18, margin + 17, { fit: [32, 32], align: "center", valign: "center" });
            } catch (imgError) {
                console.error("Failed to render logo image:", imgError);
            }
        }

        const textStartX = hasLogo ? margin + 64 : margin + 20;
        doc.font("Helvetica-Bold").fontSize(20).fillColor(white).text("Exam Infra", textStartX, margin + 14);
        doc.font("Helvetica").fontSize(9).fillColor(mutedText).text("Staff Report", textStartX, margin + 38);

        if (institutionNameFromRequest) {
            doc.font("Helvetica").fontSize(9).fillColor(white).text(`Institution: ${institutionNameFromRequest}`, textStartX, margin + 52);
        }

        doc.font("Helvetica-Bold").fontSize(9).fillColor(white)
            .text(`Generated: ${dayjs().format("DD MMM YYYY, hh:mm A")}`, doc.page.width - margin - 240, margin + 14, { width: 220, align: "right" });
        doc.font("Helvetica").fontSize(9).fillColor(mutedText)
            .text(`Total Records: ${rows.length}`, doc.page.width - margin - 240, margin + 36, { width: 220, align: "right" });
    };

    const drawTableHeader = (y: number) => {
        doc.rect(margin, y, tableWidth, 28).fill(primary);
        doc.font("Helvetica-Bold").fontSize(9).fillColor(white);

        let x = margin;
        headers.forEach((header, idx) => {
            doc.text(header, x + 8, y + 8, { width: columnWidths[idx] - 16, align: "left" });
            x += columnWidths[idx];
        });

        return y + 28;
    };

    drawPageBackground();
    drawHeader();

    let rowY = drawTableHeader(tableTop);
    const rowFontSize = 8.5;
    const rowPadding = 6;
    const maxY = doc.page.height - margin - 20;

    doc.on("pageAdded", () => {
        drawPageBackground();
        rowY = drawTableHeader(tableTop);
    });

    rows.forEach((row, index) => {
        doc.font("Helvetica").fontSize(rowFontSize);
        const rowHeight = Math.max(
            28,
            ...row.map((cell, idx) => {
                return doc.heightOfString(String(cell), { width: columnWidths[idx] - 16 }) + rowPadding * 2;
            }),
        );

        if (rowY + rowHeight > maxY) {
            doc.addPage();
        }

        doc.rect(margin, rowY, tableWidth, rowHeight).fill(index % 2 === 0 ? altRow : white);
        doc.moveTo(margin, rowY + rowHeight).lineTo(margin + tableWidth, rowY + rowHeight);

        let x = margin;
        row.forEach((cell, idx) => {
            doc.fillColor(textColor).font("Helvetica").fontSize(rowFontSize);
            doc.text(String(cell), x + 8, rowY + rowPadding, {
                width: columnWidths[idx] - 16,
                align: "left",
            });
            x += columnWidths[idx];
        });

        rowY += rowHeight;
    });

    doc.end();
});