import type { Request, Response } from 'express';
import asyncHandler from 'express-async-handler';
import { prisma } from '../config/db.ts';
import { toIST, tokenExpireTime, nowUTC } from '../utils/time.ts';
import bcrypt from 'bcrypt';
import { v4 as uuidv4 } from 'uuid';
import { sendEmail } from '../utils/mail.ts';
import { verificationTemplate } from '../utils/verificationMail.ts';
import XLSX from "xlsx";
import fs from "fs";
import { isValidBulkUploadLanguage } from './language.controller.ts';

export const createStudent = asyncHandler(async (req: Request, res: Response) => {
    const { firstName, lastName, email, phone, password, exams, language, referralId } = req.body;
    const user = (req as any).user;
    const userInstitutionId = user.institutionId

    const validations = [
        { field: firstName, message: "First name is required" },
        { field: lastName, message: "Last name is required" },
        { field: email, message: "Email is required" },
        { field: phone, message: "Phone number is required" },
        { field: exams, message: "Exams is required" },
        { field: language, message: "Language is required" },
    ];

    for (const { field, message } of validations) {
        if (!field) {
            res.status(400).json({ message });
            return;
        }
    }
    if (phone && !/^[0-9]{10}$/.test(String(phone).trim())) {
        res.status(400).json({ message: "Phone number should be 10 digits" });
        return;
    }
    // const institution = await prisma.institution.findFirst({ where: { id: userInstitutionId } })
    // if (!institution) {
    //     res.status(400).json({ message: "Institution not found" });
    //     return;
    // }
    const exists = await prisma.user.findFirst({
        where: { email },
        select: {
            id: true,
            role: true,
            student: {
                select: {
                    id: true,
                    institutions: {
                        where: { deletedAt: null },
                        select: {
                            institutionId: true
                        }
                    }
                }
            }
        }
    });

    if (exists && exists.student?.institutions) {
        const currentInstMatch = exists.student.institutions.some(
            (i: any) => i.institutionId === userInstitutionId
        );

        if (currentInstMatch) {
            res.status(400).json({ message: "Email already exists in this institution" });
            return;
        }

        if (exists.student.institutions.length > 0) {
            res.status(400).json({
                message: "Email already exists in another institution"
            });
            return;
        }
    }

    if (referralId) {
        const referralStaff = await prisma.staff.findFirst({
            where: {
                userId: String(referralId).trim(),
                institutionId: userInstitutionId,
                user: { isDeleted: false, isVerified: true }
            }
        });
        if (!referralStaff) {
            res.status(400).json({ message: "Invalid or unverified referral code" });
            return;
        }
    }

    const normalizedPhone = String(phone).trim();

    const rawPassword = password && String(password).trim() ? String(password).trim() : normalizedPhone;
    const hash = await bcrypt.hash(rawPassword, 10);

    const result = await prisma.$transaction(async (tx) => {
        let newUser = null;
        let newStudent = null
        const tokenStr = uuidv4();

        if (!exists) {
            newUser = await tx.user.create({
                data: {
                    firstName,
                    lastName,
                    email,
                    phone: normalizedPhone,
                    password: hash,
                    role: "STUDENT",
                    isVerified: false,
                    createdById: user.id
                }
            });
            newStudent = await tx.student.create({
                data: {
                    userId: newUser.id,
                    referralId: referralId || null,
                    language: language || null,
                }
            });

        } else {
            if (exists.role && exists.role !== "STUDENT") {
                const err: any = new Error("Email already in use");
                err.statusCode = 400;
                throw err;
            }

            if (!exists.student) {
                const err: any = new Error("Email already in use");
                err.statusCode = 400;
                throw err;
            }

            await tx.user.update({
                where: { id: exists.id },
                data: {
                    firstName,
                    lastName,
                    phone: normalizedPhone,
                    password: hash
                }
            });

            newUser = exists;
            newStudent = exists.student;
        }

        if (!newStudent || !newStudent.id) {
            const err: any = new Error("Student not found");
            err.statusCode = 400;
            throw err;
        }
        if (!newUser || !newUser.id) {
            const err: any = new Error("User not found");
            err.statusCode = 400;
            throw err;
        }

        const studentInstitution = await tx.studentInstitution.create({
            data: {
                studentId: newStudent.id,
                institutionId: userInstitutionId,
                createdById: user.id,
                isPrimary: true,
                isVerified: false,
                examsId: exams || null,
                verificationExpiresAt: tokenExpireTime(),
            }
        })
        await tx.token.create({
            data: {
                userId: newUser.id,
                token: tokenStr,
                institutionId: userInstitutionId,
                studentInstitutionId: studentInstitution.id,
                type: "VERIFY",
                expiresAt: tokenExpireTime(),
            }
        });

        return { userId: newUser.id, studentId: newStudent.id, token: tokenStr };
    });

    const verifyUrl = `${process.env.BASE_URL}/verify/${result.token}`;

    const emailRes = await sendEmail(
        email,
        "Verify your email",
        verificationTemplate({ firstName, companyName: "ExamInfra", verifyUrl })
    );
    if (!emailRes.success) {
        res.status(500).json({ message: "Failed to send verification email. Please try again later." });
        return;
    }

    res.status(201).json({
        message: "Student created successfully. Verification email sent.",
        data: { userId: result.userId, studentId: result.studentId }
    });
});

export const listRegisterStudents = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const institutionId = user.institutionId
    const search = (req.query.search as string)?.trim();
    const isVerified = (req.query.isVerified as string)?.trim();
    const planStatus = (req.query.planStatus as string)?.trim();
    const planType = (req.query.planType as string)?.trim();
    const examId = (req.query.examId as string)?.trim();
    const language = (req.query.language as string)?.trim();
    const planId = (req.query.planId as string)?.trim();
    const planName = (req.query.planName as string)?.trim();
    const subscriptionAction = (req.query.subscriptionAction as string)?.trim();
    const noSubscription = (req.query.noSubscription as string)?.trim();
    const regStartDate = (req.query.regStartDate as string)?.trim();
    const regEndDate = (req.query.regEndDate as string)?.trim();
    const page = Math.max(parseInt(req.query.page as string) || 1, 1);
    const limit = Math.max(parseInt(req.query.limit as string) || 10, 1);
    const skip = (page - 1) * limit;

    const whereClause: any = {
        institutionId,
        deletedAt: null
    };

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

        whereClause.student = {
            user: {
                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 } }
                            ]
                        }))
                    }] : [])
                ]
            }
        };
    }

    // Verification filter
    if (isVerified) {
        whereClause.isVerified = isVerified === "true";
    }

    // Exam & Language filters
    if (examId) {
        whereClause.examsId = examId;
    }
    if (language) {
        whereClause.language = language;
    }

    // Students with NO current subscription
    if (noSubscription === "true") {
        whereClause.subscriptions = {
            none: { isCurrent: true }
        };
    } else if (planStatus || planId || subscriptionAction || planName || planType) {
        // Subscription filters (status / planId / action / planName / planType)
        const subConditions: any = { isCurrent: true };

        if (planId) {
            subConditions.planId = planId;
        }
        if (planStatus) {
            subConditions.status = planStatus.toUpperCase();
        }
        if (subscriptionAction) {
            subConditions.action = subscriptionAction.toUpperCase();
        }
        if (planName) {
            subConditions.plan = subConditions.plan || {};
            subConditions.plan.planName = planName;
        }
        if (planType) {
            subConditions.plan = subConditions.plan || {};
            subConditions.plan.planType = planType;
        }

        whereClause.subscriptions = {
            some: subConditions
        };
    }

    // Date Filter
    if (regStartDate || regEndDate) {
        whereClause.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);
            whereClause.createdAt.gte = new Date(`${formattedStartDate}T00:00:00.000Z`);
        }
        if (regEndDate) {
            const formattedEndDate = convertToISOFormat(regEndDate);
            whereClause.createdAt.lte = new Date(`${formattedEndDate}T23:59:59.999Z`);
        }
    }

    // Execute Query
    const [studentInstitutions, total] = await Promise.all([
        prisma.studentInstitution.findMany({
            where: whereClause,
            select: {
                id: true,
                institutionId: true,
                isVerified: true,
                examsId: true,
                exam: true,
                language: true,
                student: {
                    include: {
                        user: {
                            select: {
                                id: true,
                                firstName: true,
                                lastName: true,
                                email: true,
                                phone: true,
                                isVerified: true,
                                createdById: true,

                            }
                        },
                        referral: {
                            select: {
                                firstName: true, lastName: true, id: true
                            }
                        },
                    },
                },
                subscriptions: { where: { isCurrent: true }, include: { plan: true } },
                subscriptionRequests: { where: { status: "PENDING" }, orderBy: { createdAt: 'desc' }, take: 1, include: { subscriptionPlan: { select: { id: true, planName: true } } } },
                createdAt: true
            },
            skip,
            take: limit,
            orderBy: { student: { user: { firstName: 'asc' } } }
        }),
        prisma.studentInstitution.count({
            where: whereClause
        })
    ]);

    const structuredList = studentInstitutions.map((student, i) => {
        const { id: studentInstitutionId, institutionId, subscriptions, subscriptionRequests, isVerified, student: { id, referral, user, language: motherTongue }, exam, examsId, language: instLanguage, createdAt: studentInstitutionCreatedAt } = student;
        return {
            studentInstitutionId,
            institutionId,
            id: id,
            userId: user?.id ?? "",
            firstName: user?.firstName ?? "",
            lastName: user?.lastName ?? "",
            phone: user?.phone ?? "",
            email: user?.email ?? "",
            currentSubscriptionPlanId: subscriptions[0]?.id,
            subscription: subscriptions[0] ? {
                ...subscriptions[0],
                startDate: subscriptions[0].startDate ? toIST(subscriptions[0].startDate) : null,
                endDate: subscriptions[0].endDate ? toIST(subscriptions[0].endDate) : null,
                trialEndsAt: subscriptions[0].endDate ? toIST(subscriptions[0].endDate) : null,
                createdAt: toIST(subscriptions[0].createdAt),
                // plan:null
            } : null,
            referral: `${referral?.firstName ?? ""} ${referral?.lastName ?? ""}`,
            createdAt: toIST(studentInstitutionCreatedAt),
            examsId,
            examName: exam?.examName ?? "",
            language: motherTongue || instLanguage || "",
            isVerified: isVerified ?? false,
            subscriptionPlan: {
                id: subscriptions[0]?.plan?.id,
                planName: subscriptions[0]?.plan?.planName,
                planType: subscriptions[0]?.plan?.planType,
                duration: subscriptions[0]?.plan?.duration,
                isActive: subscriptions[0]?.plan?.isActive
            },
            subscriptionRequest: subscriptionRequests[0] ? {
                id: subscriptionRequests[0].id,
                status: subscriptionRequests[0].status,
                planName: subscriptionRequests[0].subscriptionPlan?.planName || "N/A",
                createdAt: toIST(subscriptionRequests[0].createdAt),
            } : null,
        };
    });

    res.status(201).json({
        message: "Successfully fetched student list.",
        data: structuredList,
        meta: {
            total,
            page,
            limit,
            totalPages: Math.ceil(total / limit)
        }
    });
});

export const updateOwnStudentProfile = asyncHandler(async (req: Request, res: Response) => {
    const user = req.user;
    const userInstitutionId = user.institutionId
    const studentInstitutionId = user.studentInstitutionId

    if (!studentInstitutionId) {
        res.status(404).json({ message: "Institution not found." });
        return;
    }

    const { exams, institutionId } = req.body;

    const studentInstitution = await prisma.studentInstitution.findFirst({
        where: { id: studentInstitutionId }
    });

    if (!studentInstitution) {
        res.status(404).json({ message: "Student not found in this institution." });
        return;
    }

    const updateData: Record<string, any> = {};

    if (exams) {
        const examExists = await prisma.exam.findFirst({
            where: { id: exams, institutionId: userInstitutionId }
        });
        if (!examExists) {
            res.status(400).json({ message: "Exam not found in database." });
            return;
        }

        updateData.examsId = exams;
    }

    await prisma.$transaction(async (tx) => {
        if (Object.keys(updateData).length > 0) {
            await tx.studentInstitution.update({
                where: { id: studentInstitutionId },
                data: updateData,
            });
        }

        if (institutionId) {
            const targetInstitution = await prisma.studentInstitution.findUnique({
                where: { id: institutionId, deletedAt: null }
            });

            if (!targetInstitution) {
                res.status(400).json({
                    message: "Invalid institution."
                });
                return;
            }
            await tx.studentInstitution.updateMany({
                where: { studentId: studentInstitution.studentId, deletedAt: null },
                data: { isPrimary: false }
            });
            await tx.studentInstitution.update({
                where: { id: targetInstitution.id },
                data: { isPrimary: true }
            });
        }
    });

    const updatedRecord = await prisma.studentInstitution.findUnique({
        where: { id: institutionId ?? studentInstitution.id },
        select: { id: true, examsId: true }
    });

    res.status(200).json({
        message: "Updated successfully.",
        data: {
            studentInstitutionId: updatedRecord?.id,
            userId: studentInstitution.studentId,
            exams: updatedRecord?.examsId ?? ""
        }
    });
});

// Mobile API
export const saveFcmToken = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;

    if (!user) {
        res.status(401).json({ message: "User not found" });
        return;
    }

    const userId = user.id;
    const { fcmToken } = req.body;

    const student = await prisma.student.findUnique({ where: { userId, user: { isDeleted: false } } });

    if (!student) {
        res.status(404).json({ message: "Student not found." });
        return;
    }

    await prisma.student.update({
        where: { id: student.id },
        data: { fcmToken }
    });

    res.status(201).json({ message: "FCM token saved successfully.", fcmToken });
});

export const updateStudentByAdmin = asyncHandler(async (req: Request, res: Response) => {
    const { id } = req.params; // Student ID (not userId)
    const { firstName, lastName, email, phone, exams, language, referralId } = req.body;
    const user = (req as any).user;

    const studentRecord = await prisma.student.findUnique({
        where: { id, user: { isDeleted: false } },
        include: { user: true, institutions: { where: { institutionId: user.institutionId, deletedAt: null } } }
    });

    if (!studentRecord || !studentRecord.institutions) {
        res.status(404).json({ message: "Student not found" });
        return;
    }

    const result = await prisma.$transaction(async (tx) => {
        // Update User
        await tx.user.update({
            where: { id: studentRecord.userId },
            data: {
                firstName,
                lastName,
                email,
                phone
            }
        });

        // Update Student
        const updatedStudent = await tx.student.update({
            where: { id },
            data: {
                referralId,
                language
            },
            include: { user: true }
        });

        // Update StudentInstitution
        const studentInstitution = await tx.studentInstitution.findFirst({
            where: { studentId: id, institutionId: user.institutionId, deletedAt: null },
        });
        let updatedStudentInstitution = null;
        if (studentInstitution) {
            updatedStudentInstitution = await tx.studentInstitution.update({
                where: { id: studentInstitution.id },
                data: {
                    examsId: exams,
                },
                select: { id: true },
            });
        }

        return { student: updatedStudent, studentInstitutionId: updatedStudentInstitution?.id };
    });

    res.status(201).json({
        message: "Updated successfully",
        data: {
            ...result.student,
            studentInstitutionId: result.studentInstitutionId,
            createdAt: toIST(result.student.createdAt),
            updatedAt: toIST(result.student.updatedAt),
        }
    });
});

export const deleteStudent = asyncHandler(async (req: Request, res: Response) => {
    const { id } = req.params; // Student ID
    const user = (req as any).user;

    const studentRecord = await prisma.studentInstitution.findFirst({
        where: { studentId: id, institutionId: user.institutionId, deletedAt: null },
    });

    if (!studentRecord) {
        res.status(404).json({ message: "Student not found" });
        return;
    }

    await prisma.studentInstitution.update({
        where: { id: studentRecord.id },
        data: {
            deletedAt: new Date()
        }
    })
    res.status(201).json({
        message: "Student deleted successfully"
    });
});

export const getStudentById = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const { id } = req.params;
    const institutionId = user.institutionId
    const studentInstitution = await prisma.studentInstitution.findFirst({
        where: { studentId: id, institutionId, deletedAt: null },
        select: {
            id: true,
            student: {
                include: {
                    user: {
                        select: {
                            id: true,
                            firstName: true,
                            lastName: true,
                            email: true,
                            phone: true,
                            isVerified: true,
                            createdById: true,
                        }
                    },
                    subscriptions: { where: { isCurrent: true, institutionId }, include: { plan: true } },
                },
            },
            exam: true,
            examsId: true,
            language: true,
        }
    });

    const student = studentInstitution?.student
    if (!studentInstitution || !student) {
        res.status(404).json({ message: "Student not found" });
        return;
    }

    const structured = {
        studentInstitutionId: studentInstitution.id,
        id: student?.id,
        userId: student?.user?.id ?? "",
        firstName: student?.user?.firstName ?? "",
        lastName: student?.user?.lastName ?? "",
        phone: student?.user?.phone ?? "",
        email: student?.user?.email ?? "",
        subscription: student?.subscriptions[0] ? {
            ...student?.subscriptions[0],
            startDate: student?.subscriptions[0].startDate ? toIST(student?.subscriptions[0].startDate) : null,
            endDate: student?.subscriptions[0].endDate ? toIST(student?.subscriptions[0].endDate) : null,
            trialEndsAt: student?.subscriptions[0].endDate ? toIST(student?.subscriptions[0].endDate) : null,
            createdAt: toIST(student?.subscriptions[0].createdAt),
        } : null,
        examsId: studentInstitution?.examsId,
        examName: studentInstitution.exam?.examName ?? "",
        language: student?.language || studentInstitution?.language,
        isVerified: student?.user?.isVerified ?? false,
        subscriptionPlan: {
            id: student?.subscriptions[0].plan.id,
            planName: student?.subscriptions[0].plan.planName,
            planType: student?.subscriptions[0].plan.planType,
            duration: student?.subscriptions[0].plan.duration,
            isActive: student?.subscriptions[0].plan.isActive
        }
    };

    res.status(200).json({
        message: "Student details fetched successfully",
        data: structured
    });
});


export const getMyInstitutions = asyncHandler(async (req: Request, res: Response) => {
    const user = req.user;
    const userId = user.id;

    const studentInstitution = await prisma.studentInstitution.findMany({
        where: { deletedAt: null, isVerified: true, student: { user: { id: userId } } },
        include: { institution: { include: { user: { select: { institutionName: true } } } } },
        orderBy: { createdAt: "desc" }
    });
    if (studentInstitution.filter((si) => si.isPrimary).length > 1) {
        await prisma.studentInstitution.updateMany({
            where: { studentId: studentInstitution[0].studentId, deletedAt: null },
            data: { isPrimary: false }
        });
        await prisma.studentInstitution.update({
            where: { id: studentInstitution[0].id },
            data: { isPrimary: true }
        });
    }
    const structured = [...studentInstitution].map((i) => {
        return {
            institutionId: i.id,
            institutionName: i?.institution?.user?.institutionName,
            isPrimary: i.isPrimary,
        }
    })

    res.status(200).json({
        message: "Student details fetched successfully",
        data: structured
    });
});

export const getOwnSubscription = asyncHandler(async (req: Request, res: Response) => {
    const user = req.user;
    const studentInstitutionId = user.studentInstitutionId;

    const studentInstitution = await prisma.studentInstitution.findFirst({
        where: { deletedAt: null, isVerified: true, id: studentInstitutionId },
        include: { subscriptions: { where: { isCurrent: true }, take: 1, orderBy: { createdAt: 'desc' } } },
    });
    if (!studentInstitution) {
        res.status(404).json({ message: "Student not found" });
        return;
    }
    if (studentInstitution.subscriptions.length === 0) {
        res.status(404).json({ message: "No current subscription found" });
        return;
    }
    res.status(200).json({
        message: "Student details fetched successfully",
        data: studentInstitution.subscriptions[0]
    });
});

// Download student bulk upload template
export const downloadStudentUploadTemplate = asyncHandler(async (req: Request, res: Response) => {
    const templateData = [
        {
            "First Name": "John",
            "Last Name": "Doe",
            "Email": "johndoe@gmail.com",
            "Phone": "9876543210",
            "Exam": "TNPSC",
            "Language": "English",
            "Referral Code (Optional)": "ABC123"
        }
    ];
    const workbook = XLSX.utils.book_new();
    const worksheet = XLSX.utils.json_to_sheet(templateData);
    const objectKeys = Object.keys(templateData[0]) as Array<keyof typeof templateData[0]>;
    worksheet["!cols"] = objectKeys.map(key => {
        let maxLen = key.length;
        for (const row of templateData) {
            const cellValue = row[key];
            if (cellValue) {
                const cellLen = String(cellValue).length;
                if (cellLen > maxLen) {
                    maxLen = cellLen;
                }
            }
        }
        return {
            wch: maxLen + 4
        };
    });
    XLSX.utils.book_append_sheet(workbook, worksheet, "Students Template");
    const buffer = XLSX.write(workbook, { type: "buffer", bookType: "xlsx" });
    res.setHeader("Content-Disposition", "attachment; filename=student-upload-template.xlsx");
    res.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    res.send(buffer);
});

// Preview student bulk upload
export const previewBulkUploadStudents = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const userInstitutionId = user?.institutionId;

    if (!userInstitutionId) {
        res.status(400).json({ message: "User is not linked to a valid institution." });
        return;
    }

    if (!req.file) {
        res.status(400).json({ message: "Please upload an Excel file" });
        return;
    }

    try {
        const workbook = XLSX.readFile(req.file.path);
        const sheetName = workbook.SheetNames[0];
        const EXPECTED_KEYS = ["First Name", "Last Name", "Email", "Phone", "Exam", "Language", "Referral Code (Optional)"];
        const keyMap = Object.fromEntries(EXPECTED_KEYS.map(k => [k.toLowerCase(), k]));

        const rawRows = XLSX.utils.sheet_to_json<any>(workbook.Sheets[sheetName]).map((row: any) => {
            const newRow: any = {};
            for (const [key, value] of Object.entries(row)) {
                const lowerKey = key.trim().toLowerCase();
                if (keyMap[lowerKey]) {
                    newRow[keyMap[lowerKey]] = value;
                } else {
                    newRow[key] = value;
                }
            }
            return newRow;
        });

        if (rawRows.length === 0) {
            res.status(400).json({ message: "The uploaded sheet is empty" });
            return;
        }

        const institutionExams = await prisma.exam.findMany({
            where: { institutionId: userInstitutionId },
            select: { id: true, examName: true, isPublished: true }
        });
        const examMap = new Map(institutionExams.map(e => [e.examName.toLowerCase().trim(), { id: e.id, isPublished: e.isPublished }]));

        const emailsInSheet = rawRows.map(r => String(r["Email"] || "").trim().toLowerCase()).filter(Boolean);
        const referralCodesInSheet = rawRows
            .map(r => r["Referral Code (Optional)"] ? String(r["Referral Code (Optional)"]).trim() : "")
            .filter(Boolean);

        const existingUsers = await prisma.user.findMany({
            where: { email: { in: emailsInSheet } },
            select: {
                email: true,
                role: true,
                student: {
                    select: {
                        id: true,
                        institutions: {
                            where: { deletedAt: null },
                            select: {
                                institutionId: true
                            }
                        }
                    }
                }
            }
        });

        const uniqueReferralCodes = Array.from(new Set(referralCodesInSheet));
        const staffByReferralCode = new Map<string, { userId: string; firstName: string; lastName: string }>();

        if (uniqueReferralCodes.length > 0) {
            const staffRecords = await prisma.staff.findMany({
                where: {
                    referralCode: { in: uniqueReferralCodes },
                    institutionId: userInstitutionId,
                    user: { isDeleted: false, isVerified: true }
                },
                include: { user: { select: { id: true, firstName: true, lastName: true } } }
            });

            for (const staff of staffRecords) {
                if (staff.referralCode) {
                    staffByReferralCode.set(staff.referralCode.trim(), {
                        userId: staff.user.id,
                        firstName: staff.user.firstName ?? "",
                        lastName: staff.user.lastName ?? ""
                    });
                }
            }
        }

        const existingEmailMap = new Map(existingUsers.map(u => [u.email.toLowerCase(), u]));

        const processedRows = [];
        let totalValid = 0;
        let totalInvalid = 0;
        const seenEmailsInSheet = new Set<string>();

        for (const row of rawRows) {
            const errors: string[] = [];

            const firstName = row["First Name"] ? String(row["First Name"]).trim() : "";
            const lastName = row["Last Name"] ? String(row["Last Name"]).trim() : "";
            const email = row["Email"] ? String(row["Email"]).trim().toLowerCase() : "";
            const phone = row["Phone"] ? String(row["Phone"]).trim() : "";
            const examName = row["Exam"] ? String(row["Exam"]).trim() : "";
            const language = row["Language"] ? String(row["Language"]).trim() : "";
            const referralCode = row["Referral Code (Optional)"] ? String(row["Referral Code (Optional)"]).trim() : "";
            let referralId: string | null = null;

            if (referralCode) {
                const referralStaff = staffByReferralCode.get(referralCode);
                if (!referralStaff) {
                    errors.push("Invalid referral code or unverified referrer");
                } else {
                    referralId = referralStaff.userId;
                }
            }

            if (!firstName) errors.push("First name is required");
            if (!lastName) errors.push("Last name is required");
            if (!email) errors.push("Email is required");
            if (!phone) errors.push("Phone number is required");
            if (!examName) errors.push("Exam is required");
            if (!language) {
                errors.push("Language is required");
            } else if (!isValidBulkUploadLanguage(language)) {
                errors.push(`Invalid language`);
            }

            if (email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {
                errors.push("Invalid email format");
            }
            if (phone && !/^\d{10}$/.test(phone)) {
                errors.push("Phone number should be 10 digits and contain only numbers.");
            }

            if (email) {
                if (seenEmailsInSheet.has(email)) {
                    errors.push("Duplicate email within this Excel sheet");
                } else {
                    seenEmailsInSheet.add(email);
                }
            }

            if (email && !errors.includes("Duplicate email within this Excel sheet")) {
                const dbMatch: any = existingEmailMap.get(email);
                if (dbMatch) {
                    if (dbMatch.role && dbMatch.role !== "STUDENT") {
                        errors.push("Email already in use");
                    } else if (dbMatch.student?.institutions) {
                        const currentInstMatch = dbMatch.student.institutions.some(
                            (i: any) => i.institutionId === userInstitutionId
                        );

                        if (currentInstMatch) {
                            errors.push("Email already exists in this institution");
                        } else if (dbMatch.student.institutions.length > 0) {
                            errors.push("Email already in use");
                        }
                    }
                }
            }

            let matchedExamId: string | null = null;
            if (examName) {
                const matched = examMap.get(examName.toLowerCase());
                if (!matched) {
                    errors.push(`Exam '${examName}' does not exist in this institution`);
                } else if (!matched.isPublished) {
                    errors.push(`Exam '${examName}' is not published`);
                } else {
                    matchedExamId = matched.id;
                }
            }

            const isValid = errors.length === 0;

            if (isValid) totalValid++;
            else totalInvalid++;

            processedRows.push({
                firstName,
                lastName,
                email,
                phone: phone || null,
                examName,
                examsId: matchedExamId,
                language,
                referralCode: referralCode || null,
                referralId: referralId || null,
                isValid,
                errors
            });
        }

        res.status(200).json({
            message: "Bulk upload preview completed successfully.",
            meta: {
                totalRows: rawRows.length,
                validRows: totalValid,
                invalidRows: totalInvalid
            },
            data: processedRows
        });

    } finally {
        if (req.file && req.file.path && fs.existsSync(req.file.path)) {
            fs.unlinkSync(req.file.path);
        }
    }
});

// Confirm student bulk upload
export const confirmBulkUploadStudents = asyncHandler(async (req: Request, res: Response) => {
    const { students } = req.body;
    const user = (req as any).user;
    const userInstitutionId = user?.institutionId;

    if (!userInstitutionId) {
        res.status(400).json({ message: "User is not linked to a valid institution." });
        return;
    }

    if (!Array.isArray(students) || students.length === 0) {
        res.status(400).json({ message: "No student data provided for confirmation" });
        return;
    }

    const summary = {
        successCount: 0,
        failedCount: 0,
        errors: [] as Array<{ email: string; reason: string }>
    };

    for (const studentItem of students) {
        const { firstName, lastName, email, phone, examsId, language, referralId } = studentItem;

        try {
            if (!firstName || !lastName || !email || !phone || !examsId || !language) {
                throw new Error("Missing required data parameters mapping fields.");
            }

            const examRecord = await prisma.exam.findFirst({ where: { id: examsId, institutionId: userInstitutionId }, select: { id: true, examName: true, isPublished: true } });
            if (!examRecord) {
                throw new Error("Exam not found in database.");
            }
            if (!examRecord.isPublished) {
                throw new Error("Exam is not published");
            }

            if (!isValidBulkUploadLanguage(String(language))) {
                throw new Error(`Invalid language`);
            }

            const sanitizedEmail = String(email).trim().toLowerCase();
            const normalizedPhone = String(phone).trim();

            let resolvedReferralId: string | null = null;
            if (referralId) {
                const referralStaff = await prisma.staff.findFirst({
                    where: {
                        userId: String(referralId).trim(),
                        institutionId: userInstitutionId,
                        user: { isDeleted: false, isVerified: true }
                    }
                });
                if (!referralStaff) {
                    throw new Error("Invalid or unverified referral code");
                }
                resolvedReferralId = String(referralId).trim();
            }

            if (!/^\d{10}$/.test(normalizedPhone)) {
                throw new Error("Phone number must be 10 digits and contain only numbers.");
            }

            await prisma.$transaction(async (tx) => {
                const dbUser = await tx.user.findFirst({
                    where: { email: sanitizedEmail },
                    select: {
                        id: true,
                        role: true,
                        student: {
                            select: {
                                id: true,
                                institutions: {
                                    where: { deletedAt: null },
                                    select: {
                                        institutionId: true
                                    }
                                }
                            }
                        }
                    }
                }) as any;

                if (dbUser) {
                    if (dbUser.role && dbUser.role !== "STUDENT") {
                        throw new Error("Email already in use");
                    }
                    if (dbUser.student?.institutions) {
                        const currentInstMatch = dbUser.student.institutions.some(
                            (i: any) => i.institutionId === userInstitutionId
                        );
                        if (currentInstMatch) {
                            throw new Error("Email already in use");
                        }
                    }
                }

                let targetUserId = dbUser?.id;
                let targetStudentId = dbUser?.student?.id;

                if (!dbUser) {
                    const hash = await bcrypt.hash(normalizedPhone, 10);

                    const createdUser = await tx.user.create({
                        data: {
                            firstName: String(firstName).trim(),
                            lastName: String(lastName).trim(),
                            email: sanitizedEmail,
                            phone: normalizedPhone,
                            password: hash,
                            role: "STUDENT",
                            isVerified: false,
                            createdById: user.id,
                            createdAt: nowUTC()
                        }
                    });

                    const createdStudent = await tx.student.create({
                        data: {
                            userId: createdUser.id,
                            referralId: resolvedReferralId,
                            language: language,
                            createdAt: nowUTC()
                        }
                    });

                    targetUserId = createdUser.id;
                    targetStudentId = createdStudent.id;
                } else {
                    const hash = await bcrypt.hash(normalizedPhone, 10);
                    await tx.user.update({
                        where: { id: dbUser.id },
                        data: {
                            firstName: String(firstName).trim(),
                            lastName: String(lastName).trim(),
                            phone: normalizedPhone,
                            password: hash
                        }
                    });
                    await tx.student.update({
                        where: { id: dbUser.student.id },
                        data: {
                            language: language
                        }
                    });
                }

                if (dbUser) {
                    await tx.studentInstitution.updateMany({
                        where: {
                            studentId: targetStudentId!,
                            institutionId: userInstitutionId
                        },
                        data: { isPrimary: false }
                    });
                }

                const studentInstitution = await tx.studentInstitution.create({
                    data: {
                        studentId: targetStudentId!,
                        institutionId: userInstitutionId,
                        createdById: user.id,
                        isPrimary: true,
                        isVerified: false,
                        examsId: examsId,
                        verificationExpiresAt: tokenExpireTime(),
                        createdAt: nowUTC()
                    }
                });

                const tokenStr = uuidv4();
                await tx.token.create({
                    data: {
                        userId: targetUserId!,
                        token: tokenStr,
                        institutionId: userInstitutionId,
                        studentInstitutionId: studentInstitution.id,
                        type: "VERIFY",
                        expiresAt: tokenExpireTime(),
                    }
                });

                const verifyUrl = `${process.env.BASE_URL}/verify/${tokenStr}`;
                const emailRes = await sendEmail(
                    sanitizedEmail,
                    "Verify your email",
                    verificationTemplate({ firstName, companyName: "ExamInfra", verifyUrl })
                );
                if (!emailRes.success) {
                    throw new Error("Failed to send verification email. Please check SMTP/Network configuration.");
                }
            });

            summary.successCount++;
        } catch (error: any) {
            summary.failedCount++;
            summary.errors.push({
                email: studentItem.email || "UNKNOWN",
                reason: error.message || "Unknown error processing record sequence."
            });
        }
    }

    res.status(200).json({
        message: "Bulk upload confirmation processing phase completed.",
        summary
    });
});