import type { Request, Response } from "express";
import asyncHandler from "express-async-handler";
import { prisma } from "../config/db.ts";
import { detectLanguage } from "../utils/languageDetect.ts";
import fs from "fs";
import path from "path";
import { getHost, toPublicPath } from "../utils/utils.ts";
import { toIST } from "../utils/time.ts";
import XLSX from "xlsx";

// Create Question
export const createQuestion = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const {
        subject,
        exams,
        topic,
        questionText,
        correctAnswer,
        marks,
        difficulty,
        explanation,
        language,
        options: optionsReq
    } = req.body;

    const validations = [
        { field: questionText, message: "Question text is required" },
        { field: subject, message: "Subject is required" },
        { field: topic, message: "Topic is required" },
        { field: correctAnswer, message: "Correct answer is required" },
        { field: difficulty, message: "Difficulty is required" }
    ];

    for (const item of validations) {
        if (!item.field) {
            res.status(400).json({
                success: false,
                message: item.message
            });
            return
        }
    }

    if (questionText && questionText.trim() !== "") {
        const existingQuestions = await prisma.questionBank.findMany({
            where: {
                questionText: { equals: questionText.trim() },
                subjectId: subject,
                institutionId: user.institutionId
            }
        });

        // Check for duplicates
        const duplicate = existingQuestions.find((q) => {
            const sameQuestion = q.questionText.toLowerCase().trim() === questionText.toLowerCase().trim();

            if (!sameQuestion || !Array.isArray(q.options)) {
                return false;
            }

            const dbOptions = (q.options as { option: string }[])
                .map((opt) => opt.option?.toLowerCase().trim())
                .sort();

            const uploadedOptions = optionsReq
                .map((opt) => opt.toLowerCase().trim())
                .sort();

            return JSON.stringify(dbOptions) === JSON.stringify(uploadedOptions);
        });

        if (duplicate) {
            const topicMsg = duplicate.topic ? ` under the topic "${duplicate.topic}"` : '';
            res.status(400).json({
                success: false,
                message: `A Question with same options already exists in the selected subject${topicMsg}.`
            });
            return;
        }
    }

    const filesMap = req.files as {
        questionImage?: Express.Multer.File[];
        optionImage?: Express.Multer.File[];
        explanationImage?: Express.Multer.File[];
    };

    const questionImageFile = filesMap?.questionImage?.[0] || null;
    const optionImageFiles = filesMap?.optionImage || [];
    const explanationImageFile = filesMap?.explanationImage?.[0] || null;

    const questionImage = questionImageFile
        ? toPublicPath(questionImageFile.path)
        : null;

    const explanationImage = explanationImageFile
        ? toPublicPath(explanationImageFile.path)
        : null;

    const rawOptions = Array.isArray(req.body.options)
        ? req.body.options
        : [req.body.options];

    let optionImageIndexes: number[] = [];
    if (req.body.optionImageIndexes) {
        if (Array.isArray(req.body.optionImageIndexes)) {
            optionImageIndexes = req.body.optionImageIndexes.map((i: string) => parseInt(i, 10));
        } else {
            optionImageIndexes = [parseInt(req.body.optionImageIndexes as string, 10)];
        }
    }

    const imageMap: Record<number, Express.Multer.File> = {};
    if (optionImageIndexes.length === optionImageFiles.length) {
        optionImageFiles.forEach((file, i) => {
            const optionIndex = optionImageIndexes[i];
            imageMap[optionIndex] = file;
        });
    } else if (optionImageFiles.length > 0) {
        optionImageFiles.forEach((file, i) => {
            imageMap[i] = file;
        });
    }

    const options = rawOptions.map((text: string, index: number) => {
        const img = imageMap[index];
        return {
            option: text || "",
            optionImage: img ? toPublicPath(img.path) : null,
        };
    });

    if (
        (!questionText && !questionImage) ||
        correctAnswer < 0 ||
        correctAnswer >= options.length
    ) {
        res.status(400).json({ success: false, message: "Invalid question data" });
        return;
    }

    const createdById = user.id;

    const examArray = Array.isArray(exams) ? exams : (exams ? [exams] : []);

    const newQuestion = await prisma.questionBank.create({
        data: {
            createdById,
            subjectId: subject,
            topic,
            language: language || "English",
            questionText,
            questionImage,
            options: options as any,
            correctAnswer: parseInt(correctAnswer),
            marks: parseFloat(marks || "1"),
            difficulty: (difficulty as any) || "Medium",
            explanation,
            explanationImage, 
            institutionId: user.institutionId
        },
    });

    res.status(201).json({
        success: true,
        message: "Question created successfully",
        data: {
            ...newQuestion,
            createdAt: toIST(newQuestion.createdAt),
            updatedAt: toIST(newQuestion.updatedAt),
            questionImage: newQuestion.questionImage ? `${getHost()}${newQuestion.questionImage}` : "",
            options: (newQuestion.options as any[]).map((option) => ({
                ...option,
                optionImage: option.optionImage ? `${getHost()}${option.optionImage}` : "",
            })),
            explanationImage: newQuestion.explanationImage ? `${getHost()}${newQuestion.explanationImage}` : "",
        },
    });
});

// List Questions
export const listQuestions = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;

    const { search, exam, difficulty, language, subject, topic, page = "1", limit = "10", isAlreadyUsed, mockTestId, questionUsage } = req.query;
    const pageNum = parseInt(page as string);
    const limitNum = parseInt(limit as string);
    const skip = (pageNum - 1) * limitNum;

    const where: any = {
        institutionId: user.institutionId
    };

    if (search) {
        where.questionText = { contains: search as string };
    }

    if (difficulty) where.difficulty = difficulty;
    if (subject) where.subjectId = { in: Array.isArray(subject) ? subject : [subject] };
    if (topic) where.topic = { in: Array.isArray(topic) ? topic : [topic] };
    if (language) where.language = { contains: language as string };

    const requestExamIds = Array.isArray(exam) ? (exam as string[]) : (exam ? [exam as string] : []);

    if (isAlreadyUsed !== undefined) {
        if (requestExamIds.length > 0) {
            if (isAlreadyUsed === "1") {
                where.AND = [
                    ...(where.AND || []),
                    {
                        OR: [
                            { practiceTests: { some: { examId: { in: requestExamIds } } } },
                            { testQuestions: { some: { test: { practiceTests: { some: { examId: { in: requestExamIds } } } } } } }
                        ]
                    }
                ];
            } else if (isAlreadyUsed === "0") {
                where.AND = [
                    ...(where.AND || []),
                    { practiceTests: { none: { examId: { in: requestExamIds } } } },
                    { testQuestions: { none: { test: { practiceTests: { some: { examId: { in: requestExamIds } } } } } } }
                ];
            }
        } else {
            const currentTestId = req.query.testId as string;
            if (isAlreadyUsed === "1") {
                where.testQuestions = { some: {} };
            } else if (isAlreadyUsed === "0") {
                if (currentTestId) {
                    where.testQuestions = { none: { testId: { not: currentTestId } } };
                } else {
                    where.testQuestions = { none: {} };
                }
            }
        }
    }

    if (questionUsage) {
        if (questionUsage === "available") {
            if (requestExamIds.length > 0) {
                where.mockTestQuestions = {
                    none: {
                        mockTest: { examId: { in: requestExamIds } }
                    }
                };
            } else {
                where.mockTestQuestions = { none: {} };
            }
        } else if (questionUsage === "used") {
            if (requestExamIds.length > 0) {
                where.mockTestQuestions = {
                    some: {
                        mockTest: { examId: { in: requestExamIds } }
                    }
                };
            } else {
                where.mockTestQuestions = { some: {} };
            }
        }
    }

    const [total, questions] = await Promise.all([
        prisma.questionBank.count({ where }),
        prisma.questionBank.findMany({
            where,
            include: {
                createdBy: { select: { firstName: true, lastName: true } },
                practiceTests: {
                    select: { id: true, examId: true },
                    where: requestExamIds.length > 0 ? { examId: { in: requestExamIds } } : undefined,
                },
                testQuestions: {
                    select: { testId: true }
                },
                // Include mock test usage for the given exam
                mockTestQuestions: {
                    select: {
                        mockTestId: true,
                        mockTest: { select: { id: true, title: true, examId: true } }
                    },
                    where: requestExamIds.length > 0
                        ? { mockTest: { examId: { in: requestExamIds } } }
                        : undefined,
                },
                _count: {
                    select: { practiceTests: true, testQuestions: true }
                }
            },
            orderBy: { createdAt: 'desc' },
            skip,
            take: limitNum,
        }),
    ]);

    const testIdsToFetch = new Set<string>();
    questions.forEach(q => {
        q.testQuestions?.forEach((tq: any) => testIdsToFetch.add(tq.testId));
    });

    const testTitles = await prisma.test.findMany({
        where: { id: { in: Array.from(testIdsToFetch) } },
        select: { id: true, title: true }
    });

    const testMap = new Map(testTitles.map(t => [t.id, t.title]));
    const currentMockTestId = mockTestId as string | undefined;

    const updatedQuestions = questions.map((question) => {
        const testsInSpecificExam = question.practiceTests.map(t => t.id);
        const totalTestsCount = question._count.practiceTests;

        let isAlreadyUsedVal = 0;
        if (testsInSpecificExam.length > 0) {
            isAlreadyUsedVal = 1;
        } else if (totalTestsCount === 0) {
            isAlreadyUsedVal = 2;
        }

        const usedInTests = question.testQuestions?.reduce((acc: any[], tq: any) => {
            const title = testMap.get(tq.testId);
            if (title) {
                acc.push({ id: tq.testId, title });
            }
            return acc;
        }, []) || [];

        const usedInMockTests = (question.mockTestQuestions || []).reduce((acc: any[], mtq: any) => {
            if (mtq.mockTest && (!currentMockTestId || mtq.mockTest.id !== currentMockTestId)) {
                acc.push({ id: mtq.mockTest.id, title: mtq.mockTest.title });
            }
            return acc;
        }, []);

        return {
            ...question,
            createdAt: toIST(question.createdAt),
            updatedAt: toIST(question.updatedAt),
            tests: testsInSpecificExam,
            usedInTests,
            usedInMockTests,
            questionImage: question.questionImage ? `${getHost()}${question.questionImage}` : "",
            options: (question.options as any[]).map((option) => ({
                ...option,
                optionImage: option.optionImage ? `${getHost()}${option.optionImage}` : "",
            })),
            explanationImage: question.explanationImage ? `${getHost()}${question.explanationImage}` : "",
            isAlreadyUsed: isAlreadyUsedVal,
            createdBy: question.createdBy
                ? `${question.createdBy.firstName} ${question.createdBy.lastName}`
                : "Unknown",
        };
    });

    res.status(200).json({
        message: "Successfully fetched questions",
        data: updatedQuestions,
        meta: {
            total,
            page: pageNum,
            limit: limitNum,
            totalPages: Math.ceil(total / limitNum),
        },
    });
});

// Update Question
export const updateQuestion = asyncHandler(async (req: Request, res: Response) => {
    const { id } = req.params;
    const updates = req.body;

    const filesMap = req.files as { [fieldname: string]: Express.Multer.File[] };

    const user = (req as any).user;
    const existingQuestion = await prisma.questionBank.findFirst({
        where: { id, institutionId: user.institutionId }
    });
    if (!existingQuestion) {
        res.status(404).json({ message: "Question not found" });
        return;
    }
    if (updates.questionText && updates.questionText.trim() !== "") {
        const finalSubjectId = updates.subjectId || existingQuestion.subjectId;
        const existingQuestions = await prisma.questionBank.findMany({
            where: {
                id: { not: id },
                questionText: updates.questionText.trim(),
                subjectId: finalSubjectId,
                institutionId: user.institutionId
            }
        });
        // Check for duplicates
        const duplicate = existingQuestions.find((q) => {
            const sameQuestion = q.questionText.toLowerCase().trim() === updates.questionText.toLowerCase().trim();
            if (!sameQuestion || !Array.isArray(q.options)) {
                return false;
            }

            const dbOptions = (q.options as { option: string }[])
                .map((opt) => opt.option?.toLowerCase().trim())
                .sort();
            const uploadedOptions = updates.options
                .map((opt: any) => opt?.toLowerCase().trim())
                .sort();
            return JSON.stringify(dbOptions) === JSON.stringify(uploadedOptions);
        });

        if (duplicate) {
            const topicMsg = duplicate.topic ? ` under the topic "${duplicate.topic}"` : '';
            res.status(400).json({
                success: false,
                message: `A Question with same options already exists in the selected subject${topicMsg}.`
            });
            return;
        }
    }

    const fileDelete = (filePathUrl: string) => {
        if (!filePathUrl) return;
        const oldFileName = path.basename(filePathUrl || "");
        if (!oldFileName) return;
        const oldFilePath = path.join(process.cwd(), "uploads", oldFileName);
        if (fs.existsSync(oldFilePath)) {
            try { fs.unlinkSync(oldFilePath); } catch (e) { console.error("Error deleting file:", e); }
        }
    };

    if (filesMap?.questionImage?.[0]) {
        if (existingQuestion.questionImage) fileDelete(existingQuestion.questionImage);
        updates.questionImage = toPublicPath(filesMap.questionImage[0].path);
    } else if (updates.questionImage === "" || updates.questionImage === "null" || updates.questionImage === null) {
        if (existingQuestion.questionImage) fileDelete(existingQuestion.questionImage);
        updates.questionImage = null;
    }

    if (filesMap?.explanationImage?.[0]) {
        if (existingQuestion.explanationImage) fileDelete(existingQuestion.explanationImage);
        updates.explanationImage = toPublicPath(filesMap.explanationImage[0].path);
    } else if (updates.explanationImage === "" || updates.explanationImage === "null" || updates.explanationImage === null) {
        if (existingQuestion.explanationImage) fileDelete(existingQuestion.explanationImage);
        updates.explanationImage = null;
    }

    if (updates.options) {
        const rawOptions = Array.isArray(updates.options) ? updates.options : [updates.options];
        const optionImageFiles = filesMap?.optionImage || [];
        let optionImageIndexes: number[] = [];
        if (req.body.optionImageIndexes) {
            optionImageIndexes = Array.isArray(req.body.optionImageIndexes)
                ? req.body.optionImageIndexes.map((i: string) => parseInt(i, 10))
                : [parseInt(req.body.optionImageIndexes, 10)];
        }

        const uploadedImageMap: Record<number, Express.Multer.File> = {};
        if (optionImageIndexes.length === optionImageFiles.length) {
            optionImageFiles.forEach((file, i) => { uploadedImageMap[optionImageIndexes[i]] = file; });
        }

        let existingOptionImages: Record<number, string> = {};
        if (req.body.existingOptionImages) {
            try { existingOptionImages = JSON.parse(req.body.existingOptionImages); } catch (e) { }
        }

        const newOptions = rawOptions.map((text: string, index: number) => {
            const uploadedFile = uploadedImageMap[index];
            const existingUrl = existingOptionImages[index];
            return {
                option: text || "",
                optionImage: uploadedFile ? toPublicPath(uploadedFile.path) : (existingUrl || null)
            };
        });

        // Cleanup old option images
        const oldOptions = existingQuestion.options as any[];
        if (oldOptions) {
            const newImageUrls = new Set(newOptions.map(o => o.optionImage).filter(Boolean));
            oldOptions.forEach(oldOpt => {
                if (oldOpt.optionImage && !newImageUrls.has(oldOpt.optionImage)) fileDelete(oldOpt.optionImage);
            });
        }
        updates.options = newOptions;
    }

    if (updates.exams) {
        const examArray = Array.isArray(updates.exams) ? updates.exams : [updates.exams];
        updates.exams = {
            set: examArray.map((eid: string) => ({ id: eid }))
        };
    }

    const allowedFields = [
        'questionText', 'questionImage', 'explanation', 'explanationImage',
        'options', 'exams', 'topic', 'difficulty', 'language', 'correctAnswer', 'marks'
    ];

    const newUpdate: any = {};
    for (const field of allowedFields) {
        if (updates[field] !== undefined) {
            newUpdate[field] = updates[field];
        }
    }

    if (updates.correctAnswer !== undefined) newUpdate.correctAnswer = parseInt(updates.correctAnswer);
    if (updates.marks !== undefined) newUpdate.marks = parseFloat(updates.marks);

    const { subject: sub, ..._rest } = updates;
    if (sub) newUpdate.subjectId = sub;

    const updatedQuestion = await prisma.questionBank.update({
        where: { id },
        data: newUpdate,
    });

    res.status(200).json({
        message: "Question updated successfully",
        data: {
            ...updatedQuestion,
            createdAt: toIST(updatedQuestion.createdAt),
            updatedAt: toIST(updatedQuestion.updatedAt),
            questionImage: updatedQuestion.questionImage ? `${getHost()}${updatedQuestion.questionImage}` : "",
            options: (updatedQuestion.options as any[]).map((option) => ({
                ...option,
                optionImage: option.optionImage ? `${getHost()}${option.optionImage}` : "",
            })),
            explanationImage: updatedQuestion.explanationImage ? `${getHost()}${updatedQuestion.explanationImage}` : "",
        },
    });
});

// Delete Question
export const deleteQuestion = asyncHandler(async (req: Request, res: Response) => {
    const { id } = req.params;
    const user = (req as any).user;
    const question = await prisma.questionBank.findFirst({
        where: { id, institutionId: user.institutionId },
        include: {
            practiceTests: {
                select: { id: true },
            },
        },
    });

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

    if (question.practiceTests.length > 0) {
        res.status(400).json({
            message: "Question cannot be deleted as it is used in practice tests",
        });
        return;
    }

    await prisma.questionBank.delete({ where: { id } });

    res.status(201).json({
        message: "Question deleted successfully",
        deletedId: id,
    });
});

// Get Random Question
export const getRandomQuestion = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const examId = typeof req.query.examId === "string" ? req.query.examId : (typeof req.query.exam_id === "string" ? req.query.exam_id : undefined);
    const exam = typeof req.query.exam === "string" ? req.query.exam : undefined;
    const subjectId = typeof req.query.subjectId === "string" ? req.query.subjectId : (typeof req.query.subject_id === "string" ? req.query.subject_id : undefined);
    const subject = typeof req.query.subject === "string" ? req.query.subject : undefined;
    const practiceTestId = typeof req.query.practiceTestId === "string" ? req.query.practiceTestId : (typeof req.query.practice_test_id === "string" ? req.query.practice_test_id : undefined);
    const practiceTest = typeof req.query.practiceTest === "string" ? req.query.practiceTest : undefined;

    let studentLanguage: string | undefined;
    let studentExamId: string | undefined;

    if (user.studentInstitutionId) {
        const studentInstitution = await prisma.studentInstitution.findUnique({
            where: { id: user.studentInstitutionId },
            select: { language: true, examsId: true },
        });
        studentLanguage = studentInstitution?.language ?? undefined;
        studentExamId = studentInstitution?.examsId ?? undefined;
    }

    const currentExamId = examId || exam || studentExamId;
    const currentSubjectId = subjectId || subject;
    const currentPracticeTestId = practiceTestId || practiceTest;

    const where: any = {
        institutionId: user.institutionId,
        questionText: { not: null },
        AND: [
            {
                OR: [
                    { questionImage: null },
                    { questionImage: "" }
                ]
            }
        ]
    };

    if (currentSubjectId) {
            where.subjectId = currentSubjectId;
    }

    const ptFilter = currentPracticeTestId
        ? { id: currentPracticeTestId, publish: true }
        : currentExamId
        ? { examId: currentExamId, publish: true }
        : { publish: true };

    where.AND.push({
        OR: [
            {
                practiceTests: {
                    some: ptFilter
                }
            },
            {
                testQuestions: {
                    some: {
                        test: {
                            practiceTests: {
                                some: ptFilter
                            }
                        }
                    }
                }
            }
        ]
    });

    if (studentLanguage) {
        where.language = { equals: studentLanguage };
    }

    const candidates = await prisma.questionBank.findMany({
        where,
        select: {
            id: true,
            questionText: true
        }
    });

    const validCandidates = candidates.filter((item) => {
        return typeof item.questionText === "string" && item.questionText.length <= 100;
    });

    if (validCandidates.length === 0) {
        res.status(404).json({ message: "No matching question found" });
        return;
    }

    const randomQuestionId = validCandidates[Math.floor(Math.random() * validCandidates.length)].id;
    const question = await prisma.questionBank.findUnique({
        where: { id: randomQuestionId }
    });

    if (!question) {
        res.status(404).json({ message: "No matching question found" });
        return;
    }

    const rawOptions = question.options;
    res.status(200).json({
        message: "Random question fetched successfully",
        data: {
            ...question,
            options:
                typeof rawOptions === "string"
                    ? JSON.parse(rawOptions)
                    : rawOptions,
        },
    });
});

// Preview Bulk Upload
export const previewBulkUploadByExcel = asyncHandler(async (req: Request, res: Response) => {
    try {
        const user = req.user;
        if (!req.file) {
            res.status(400).json({ message: "No file uploaded" });
            return;
        }

        const filePath = req.file.path;
        const workbook = XLSX.readFile(filePath);
        const EXPECTED_KEYS = [
            "questionText", "option1", "option2", "option3", "option4", 
            "correctAnswer", "subject", "topic", "language", "difficulty", "explanation"
        ];
        const keyMap = Object.fromEntries(EXPECTED_KEYS.map(k => [k.toLowerCase(), k]));
        
        const rawData: any[] = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]]);
        const data = rawData.map(row => {
            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;
        });
        fs.unlinkSync(filePath);

        if (!data || data.length === 0) {
            res.status(400).json({ message: "Excel file is empty" });
            return;
        }
        const normalize = (value?: string) => String(value)?.trim()?.toLowerCase() || "";
        const where: any = {
            institutionId: user.institutionId,
        }
        if (user.role === "STAFF") {
            where.staffs = { some: { userId: user.id } }
        }
        const subjects = await prisma.subject.findMany({
            where,
            select: { id: true, subjectName: true, topics: true }
        });

        const existingQuestions = (await prisma.questionBank.findMany({
            where: {
                institutionId: user.institutionId,
                OR: data.filter(row => row.questionText).map((row) => {
                    const subjectObj = subjects.find(s => s.subjectName?.trim().toLowerCase() === (row.subject || "").toString()?.trim().toLowerCase());
                    return {
                        questionText: row.questionText?.trim(),
                        ...(subjectObj ? { subjectId: subjectObj.id } : {})
                    };
                }),
            },
            select: {
                questionText: true,
                topic: true,
                options: true,
                subject: { select: { id: true, subjectName: true } }
            },
        })).map((question) => ({
            ...question,
            questionText: question.questionText?.trim().toLowerCase(),
            topic: question.topic?.trim().toLowerCase(),
            subjectName: question.subject?.subjectName?.trim().toLowerCase(),
            subjectId: question.subject?.id,
        }));

        const seenKeys = new Set<string>();

        const previewData = data.map((row, i) => {
            const errors: string[] = [];
            if (!row.questionText) errors.push("Missing questionText.");
            if (!row.subject) errors.push("Missing subject name.");
            if (row.correctAnswer === undefined) errors.push("Missing correctAnswer.");

            const subjectObj = subjects.find(s => s.subjectName?.trim().toLowerCase() === (row.subject || "").toString()?.trim().toLowerCase());
            if (row.subject && !subjectObj) errors.push(`Subject '${row.subject}' not found.`);
            // const topicObj = subjects.find(s => {
            //     const topics = Array.isArray(s.topics) ? (s.topics as string[]) : [];
            //     return topics.some(t => t.trim().toLowerCase() === (row.topic || "").toString().trim().toLowerCase());
            // });
            const topicObj = Array.isArray(subjectObj?.topics) ?
                subjectObj?.topics.some((t: string) => t?.trim().toLowerCase() === (row.topic || "").toString()?.trim().toLowerCase())
                :
                null;
            if (row.topic && !topicObj) errors.push(`Topic '${row.topic}' not found.`);

            const options = [];
            for (let j = 1; j <= 4; j++) {
                const opt = row[`option${j}`];
                if (opt) options.push(opt);
            }
            if (options.length < 2) errors.push("At least 2 options required.");

            const key = [
                normalize(row.subject),
                normalize(row.questionText),
                options.map(normalize).sort().join("-"),
            ].join("-");
            
            if (seenKeys.has(key)) {
                errors.push(`Duplicate question in the file.`);
            } else {
                seenKeys.add(key);
            }

            const correctAnswerIndex = parseInt(row.correctAnswer) - 1;
            if (isNaN(correctAnswerIndex) || correctAnswerIndex < 0 || correctAnswerIndex >= options.length) errors.push("Invalid correctAnswer index.");

            // Check for duplicates
            const uploadedOptions = options
                .map((opt: any) => String(opt)?.trim()?.toLowerCase())
                .sort();

            const duplicate = existingQuestions.find((q) => {
                const sameQuestion = q.questionText.toLowerCase().trim() === row.questionText?.trim().toLowerCase();
                if (!sameQuestion || !Array.isArray(q.options)) {
                    return false;
                }
                const dbOptions = (q.options as { option: string }[])
                    .map((opt) => String(opt.option)?.trim()?.toLowerCase())
                    .sort();
                return JSON.stringify(dbOptions) === JSON.stringify(uploadedOptions);
            });

            if (duplicate) {
                const topicMsg = duplicate.topic ? ` under the topic "${duplicate.topic}"` : '';
                errors.push(`A Question with same options already exists in the selected subject${topicMsg}.`)
            }

            return {
                rowNumber: i + 1,
                questionText: row.questionText || "",
                subjectName: row.subject || "",
                subjectId: subjectObj?.id || null,
                topic: row.topic || "General",
                language: row.language || "English",
                options,
                correctAnswer: row.correctAnswer,
                marks: 1,
                difficulty: row.difficulty || "Medium",
                explanation: row.explanation || "",
                isValid: errors.length === 0,
                errors
            };
        });

        res.status(200).json({
            message: "Preview data generated",
            data: {
                totalRows: data.length,
                validCount: previewData.filter(d => d.isValid).length,
                invalidCount: previewData.filter(d => !d.isValid).length,
                questions: previewData
            }
        });
    } catch (error: any) {
        if (req.file?.path) setTimeout(() => fs.unlinkSync(req.file.path), 100);
        res.status(500).json({ message: error.message || "Preview failed" });
    }
});

// Confirm Bulk Upload
export const confirmBulkUploadByExcel = asyncHandler(async (req: Request, res: Response) => {
    const { questions, batchInfo } = req.body;
    if (!questions?.length) {
        res.status(400).json({ message: "No questions provided" });
        return;
    }

    const user = (req as any).user;
    const createdById = user.id;

    const results = await prisma.$transaction(async (tx) => {
        let createdCount = 0;
        for (const q of questions) {
            const finalSubject = batchInfo?.subject ? batchInfo.subject : q.subjectId;

            // Skip if question already exists
            if (q.questionText && q.questionText.trim() !== "") {
                const existingQuestions = await tx.questionBank.findMany({
                    where: {
                        questionText: q.questionText.trim(),
                        subjectId: finalSubject,
                        institutionId: user.institutionId
                    }
                });

                const uploadedOptions = q.options
                    .map((opt: string) => String(opt)?.trim()?.toLowerCase())
                    .sort();

                const duplicate = existingQuestions.find((existingQ) => {
                    if (!Array.isArray(existingQ.options)) return false;
                    const dbOptions = (existingQ.options as { option: string }[])
                        .map((opt) => String(opt.option)?.trim()?.toLowerCase())
                        .sort();
                    return JSON.stringify(dbOptions) === JSON.stringify(uploadedOptions);
                });

                if (duplicate) continue;
            }

            const options = q.options.map((opt: string) => ({ option: opt, optionImage: null }));
            const finalExams = batchInfo?.exams?.length > 0 ? batchInfo.exams : (q.examIds || []);

            await tx.questionBank.create({
                data: {
                    createdById,
                    subjectId: finalSubject,
                    topic: q.topic,
                    language: q.language,
                    questionText: q.questionText,
                    options: options as any,
                    correctAnswer: parseInt(q.correctAnswer) - 1,
                    marks: 1,
                    difficulty: (q.difficulty as any) || "Medium",
                    explanation: q.explanation,
                    institutionId: user.institutionId
                }
            });
            createdCount++;
        }
        return createdCount;
    });

    res.status(201).json({ message: `${results} Questions created successfully`, count: results });
});

// Download Template
export const downloadTemplateByExcel = asyncHandler(async (req: Request, res: Response) => {
    const templateData = [{
        questionText: "Which is the capital of India?",
        option1: "Mumbai", option2: "New Delhi", option3: "Chennai", option4: "Kolkata",
        correctAnswer: 2, subject: "History", topic: "General", language: "English",
        difficulty: "Easy", explanation: "New Delhi is the capital of India."
    }];
    const worksheet = XLSX.utils.json_to_sheet(templateData);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, "Questions");
    const buffer = XLSX.write(workbook, { type: "buffer", bookType: "xlsx" });
    res.setHeader("Content-Disposition", "attachment; filename=question_bank_template.xlsx");
    res.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    res.send(buffer);
});

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

    const question = await prisma.questionBank.findFirst({
        where: {
            id,
            OR: [
                { institutionId: user.institutionId },
                { institutionId: null }
            ]
        },
        include: {
            subject: true
        }
    });

    if (!question) {
        res.status(404).json({ success: false, message: "Question not found" });
        return;
    }

    res.status(200).json({ success: true, data: question });
});