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 createOldQuestion = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;
    const {
        oldQuestionId,
        questionText,
        correctAnswer,
        marks,
        difficulty,
        publish,
        explanation,
        options: optionsReq
    } = req.body;

    if (questionText && questionText.trim() !== "") {
        const existingQuestions = await prisma.oldQuestion.findMany({
            where: {
                questionText: questionText.trim(),
                institutionId: user.institutionId,
                oldQuestionPapers: {
                    some: {
                        id: oldQuestionId
                    }
                }
            }
        });

        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) {
            res.status(400).json({
                success: false,
                message: "A Question with same options is already exists in the selected paper."
            });
            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 paper = await prisma.oldQuestionPaper.findFirst({
        where: {
            id: oldQuestionId,
            institutionId: user.institutionId
        },
        include: {
            sharedCopies: { select: { id: true } }
        }
    });

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

    const newQuestion = await prisma.oldQuestion.create({
        data: {
            createdById,
            language: paper.language || "English",
            year: paper.year,
            exams: {
                connect: { id: paper.examId }
            },
            questionText,
            questionImage,
            options: options as any,
            correctAnswer: parseInt(correctAnswer),
            marks: marks ? parseFloat(marks) : 1,
            difficulty: difficulty || "Medium",
            publish: publish !== undefined ? publish === "true" || publish === true : true,
            explanation,
            explanationImage,
            institutionId: user.institutionId,
            oldQuestionPapers: {
                connect: [
                    { id: oldQuestionId },
                    ...(paper.sharedCopies?.map((copy: any) => ({ id: copy.id })) || [])
                ]
            }
        },
        include: {
            exams: { select: { id: true, examName: true } }
        }
    });

    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 listOldQuestions = asyncHandler(async (req: Request, res: Response) => {
    const user = (req as any).user;

    const { search, exam, year, difficulty, language, page = "1", limit = "10" } = 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.OR = [
            { questionText: { contains: search as string } },
            { year: { contains: search as string } },
            { language: { contains: search as string } },
            { explanation: { contains: search as string } },
            { exams: { some: { examName: { contains: search as string } } } }
        ];
    }

    if (difficulty) where.difficulty = difficulty;
    if (language) where.language = { contains: language as string };
    if (year) where.year = year;
    if (exam) where.exams = { some: { id: exam as string } };

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

    const [total, questions] = await Promise.all([
        prisma.oldQuestion.count({ where }),
        prisma.oldQuestion.findMany({
            where,
            include: {
                exams: { select: { id: true, examName: true } },
                createdBy: { select: { firstName: true, lastName: true } },
                oldQuestionPapers: {
                    select: { id: true, examId: true },
                    where: requestExamIds.length > 0 ? { examId: { in: requestExamIds } } : undefined,
                },
                _count: {
                    select: { oldQuestionPapers: true }
                }
            },
            orderBy: { createdAt: 'desc' },
            skip,
            take: limitNum,
        }),
    ]);

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

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

        return {
            id: question.id,
            questionText: question.questionText,
            year: question.year,
            language: question.language,
            marks: question.marks,
            difficulty: question.difficulty,
            explanation: question.explanation,
            exams: question.exams,
            correctAnswer: question.correctAnswer,
            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,
            createdBy: question.createdBy
                ? `${question.createdBy.firstName} ${question.createdBy.lastName}`
                : "Unknown",
            updatedAt: toIST(question.updatedAt),
            createdAt: toIST(question.createdAt)
        };
    });

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

// Update Question
export const updateOldQuestion = 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.oldQuestion.findFirst({
        where: { id, institutionId: user.institutionId }
    });
    if (!existingQuestion) {
        res.status(404).json({ message: "Question not found" });
        return;
    }
    const { questionText, options: optionsReq, oldQuestionId } = updates;
    if (questionText && questionText.trim() !== "") {
        if (questionText && questionText.trim() !== "") {
            const existingQuestions = await prisma.oldQuestion.findMany({
                where: {
                    questionText: questionText.trim(),
                    institutionId: user.institutionId,
                    oldQuestionPapers: {
                        some: {
                            id: oldQuestionId
                        }
                    }
                }
            });

            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) {
                res.status(400).json({
                    success: false,
                    message: "A Question with same options is already exists in the selected paper."
                });
                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', 'year', 'difficulty', 'language', 'correctAnswer', 'marks', 'publish'
    ];

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

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

    const updatedQuestion = await prisma.oldQuestion.update({
        where: { id },
        data: validUpdates,
        include: { exams: true }
    });

    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 deleteOldQuestion = asyncHandler(async (req: Request, res: Response) => {
    const { id } = req.params;

    const user = (req as any).user;
    const question = await prisma.oldQuestion.findFirst({
        where: { id, institutionId: user.institutionId },
        include: {
            oldQuestionPapers: {
                select: { id: true },
            },
        },
    });

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

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

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

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

// Preview Bulk Upload
const normalize = (value?: any) => (value === undefined || value === null) ? "" : String(value).trim().toLowerCase();
export const previewBulkOldQuestionUploadByExcel = asyncHandler(async (req: Request, res: Response) => {
    try {
        const user = (req as any).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", "exam", "title", "year", /*"language",*/ "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;
        });
        if (fs.existsSync(filePath)) fs.unlinkSync(filePath);

        if (!data || data.length === 0) {
            res.status(400).json({ message: "Excel file is empty" });
            return;
        }

        const exams = await prisma.exam.findMany({
            where: { institutionId: user.institutionId },
            select: { id: true, examName: true }
        });

        const validQuestionTexts = data
            .filter(item => item.questionText)
            .map(item => String(item.questionText).trim());

        const existingQuestions = (await prisma.oldQuestion.findMany({
            where: {
                institutionId: user.institutionId,
                questionText: { in: validQuestionTexts }
            },
            select: {
                questionText: true, options: true, year: true,
                oldQuestionPapers: { select: { id: true, title: true, year: true, exam: { select: { examName: true, id: true } } } }
            }
        })).map((question) => {
            return {
                ...question,
                questionText: question.questionText.toLowerCase().trim(),
                year: question.year ? question.year.toLowerCase().trim() : "",
                oldQuestionPaperIds: question.oldQuestionPapers.map((qp) => qp.id),
                oldQuestionPaperNames: question.oldQuestionPapers.map((qp) => qp.title),
                oldQuestionPaperYears: question.oldQuestionPapers.map((qp) => qp.year),
            }
        });

        const questionDataSet = {};

        for (const question of data) {
            const option = [normalize(question.option1), normalize(question.option2), normalize(question.option3), normalize(question.option4)].sort();
            const key = [
                normalize(question.exam),
                normalize(question.title),
                normalize(question.year),
                normalize(question.questionText),
                option.join("-"),
            ].join("-");

            if (questionDataSet[key]) {
                questionDataSet[key].errors.push(`Duplicate question found in the uploaded file`);
                continue;
            }
            questionDataSet[key] = {
                ...question,
                errors: [],
            }
        }

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

            // Handle examId or exams (name)
            let finalExamIds: string[] = [];
            const rowExams = row.exams || row.exam || "";
            const rowExamIds = row.examId || "";

            if (rowExamIds) {
                const ids = Array.isArray(rowExamIds) ? rowExamIds : String(rowExamIds).split(",").map(id => id.trim());
                finalExamIds = exams.filter(e => ids.includes(e.id)).map(e => e.id);
            } else if (rowExams) {
                const normalizedName = normalize(rowExams);
                const matched = exams.find(e => normalize(e.examName) === normalizedName);
                if (matched) {
                    finalExamIds = [matched.id];
                }
            }
            if (!rowExams && !rowExamIds) {
                errors.push("Missing exam name column in excel header.");
            } else if (finalExamIds.length === 0) {
                errors.push(`Exam "${String(rowExams).trim()}" not found in your institution.`);
            }

            // Title & Year Validation
            if (!row.title) {
                errors.push("Title is required.");
            }

            if (!row.year) {
                errors.push("Year is required.");
            }

            let matchedPaper: { id: string; title: string; year: string } | null = null;

            // if (row.title && row.year) {
            //     matchedPaper = await prisma.oldQuestionPaper.findFirst({
            //         where: {
            //             institutionId: user.institutionId,
            //             title: String(row.title).trim(),
            //             year: String(row.year).trim(),
            //         },
            //         select: { id: true, title: true, year: true },
            //     });

            //     if (!matchedPaper) {
            //         errors.push(`Paper with title "${row.title}" and year "${row.year}" not found`);
            //     }
            // }

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

            const key = [
                normalize(row.exam),
                normalize(row.title),
                normalize(row.year),
                normalize(row.questionText),
                options.map(normalize).sort().join("-"),
            ].join("-");
            if (questionDataSet[key]?.errors?.length > 0) {
                errors.push(`Duplicate question in the same file.`);
            }

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

            const foundExamNames = exams.filter(e => finalExamIds.includes(e.id)).map(e => e.examName);

            // Check for duplicates
            const uploadedOptions = options
                .map((opt) => normalize(opt))
                .sort();

            const duplicate = existingQuestions.some((eq) => {
                const sameQuestion = normalize(eq.questionText) === normalize(row.questionText || "");
                if (!sameQuestion) return false;

                const dbOptions = (eq.options as { option: string }[])
                    .map((opt) => normalize(opt.option))
                    .sort();
                if (JSON.stringify(dbOptions) !== JSON.stringify(uploadedOptions)) return false;

                return (eq.oldQuestionPapers as any[]).some((paper: any) =>
                    normalize(paper.title) === normalize(row.title || "") &&
                    normalize(paper.year) === normalize(row.year || "") &&
                    finalExamIds.includes(paper.exam?.id)
                );
            });

            if (duplicate) {
                errors.push("A Question with same options is already exists in the selected paper.");
            }

            return {
                rowNumber: i + 1,
                questionText: row.questionText || "",
                title: row.title || row.paperName || null,
                year: row.year ? String(row.year) : null,
                examName: String(rowExams),
                examNames: foundExamNames,
                examId: finalExamIds,
                language: row.language || detectLanguage(row.questionText) || "English",
                options,
                correctAnswer: row.correctAnswer,
                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 && fs.existsSync(req.file.path)) fs.unlinkSync(req.file.path);
        res.status(500).json({ message: error.message || "Preview failed" });
    }
});

// Confirm Bulk Upload
export const confirmBulkOldQuestionUploadByExcel = asyncHandler(async (req: Request, res: Response) => {
    const { questions } = 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;
        const paperCache = new Map<string, string[]>();

        for (const q of questions) {
            if (!q.questionText?.trim() || !q.title) continue;

            const finalExamIds = Array.isArray(q.examId) ? q.examId : [];
            if (finalExamIds.length === 0) continue;

            const primaryExamId = finalExamIds[0];
            const year = q.year ? String(q.year) : "0000";
            const language = q.language || "English";
            const paperKey = `${q.title}|${year}|${language}|${primaryExamId}`;

            // Step 1: Resolve or create the paper first
            let paperIds = paperCache.get(paperKey);
            if (!paperIds) {
                const existingPaper = await tx.oldQuestionPaper.findFirst({
                    where: {
                        title: q.title,
                        year: year,
                        language: language,
                        examId: primaryExamId,
                        institutionId: user.institutionId
                    },
                    include: { sharedCopies: { select: { id: true } } }
                });

                if (existingPaper) {
                    paperIds = [existingPaper.id, ...existingPaper.sharedCopies.map((c: any) => c.id)];
                } else {
                    const newPaper = await tx.oldQuestionPaper.create({
                        data: {
                            title: q.title,
                            year: year,
                            language: language,
                            examId: primaryExamId,
                            institutionId: user.institutionId,
                            createdById,
                            duration: "60",
                            marks: 1,
                            difficulty: "Medium",
                            publish: false
                        }
                    });
                    paperIds = [newPaper.id];
                }
                paperCache.set(paperKey, paperIds);
            }

            const newOpts = (q.options as string[]).map((o: string) => String(o).toLowerCase().trim()).sort();

            // Step 2: Skip if exact same question (text + options) is already in this paper
            const existingInPaper = await tx.oldQuestion.findFirst({
                where: {
                    questionText: q.questionText.trim(),
                    institutionId: user.institutionId,
                    oldQuestionPapers: { some: { id: { in: paperIds } } }
                },
                select: { id: true, options: true }
            });

            if (existingInPaper) {
                const dbOpts = (existingInPaper.options as { option: string }[])
                    .map(o => o.option?.toLowerCase().trim()).sort();
                if (JSON.stringify(dbOpts) === JSON.stringify(newOpts)) {
                    continue;
                }
            }

            // Step 3: Find or create the question
            let targetQuestionId: string | null = null;
            let existingPaperIds: string[] = [];

            const candidates = await tx.oldQuestion.findMany({
                where: {
                    questionText: q.questionText.trim(),
                    institutionId: user.institutionId
                },
                include: { oldQuestionPapers: { select: { id: true } } }
            });

            const match = candidates.find(c => {
                const dbOpts = (c.options as { option: string }[]).map(o => o.option?.toLowerCase().trim()).sort();
                return JSON.stringify(dbOpts) === JSON.stringify(newOpts);
            });

            if (match) {
                targetQuestionId = match.id;
                existingPaperIds = match.oldQuestionPapers.map(p => p.id);
            } else {
                const options = q.options.map((opt: string) => ({ option: opt, optionImage: null }));
                const newQuestion = await tx.oldQuestion.create({
                    data: {
                        createdById,
                        year: q.year ? String(q.year) : null,
                        language: q.language || detectLanguage(q.questionText) || "English",
                        questionText: String(q.questionText).trim(),
                        options: options as any,
                        correctAnswer: parseInt(q.correctAnswer) - 1,
                        marks: 1,
                        difficulty: "Medium",
                        explanation: q.explanation,
                        exams: { connect: finalExamIds.map((id: string) => ({ id })) },
                        institutionId: user.institutionId
                    }
                });
                targetQuestionId = newQuestion.id;
                createdCount++;
            }

            // Step 4: Link question only to papers it is not already in
            const newPaperLinks = paperIds.filter(id => !existingPaperIds.includes(id));
            if (newPaperLinks.length > 0) {
                await tx.oldQuestion.update({
                    where: { id: targetQuestionId },
                    data: {
                        updatedAt: new Date(),
                        oldQuestionPapers: {
                            connect: newPaperLinks.map((id: string) => ({ id }))
                        }
                    }
                });
            }
        }
        return createdCount;
    });

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

// Download Template
export const downloadOldQuestionTemplateByExcel = 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,
        exam: "TNPSC",
        title: "TNPSC Group 4",
        year: "2023",
        // language: "English",
        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=old_question_bank_template.xlsx");
    res.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    res.send(buffer);
});

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

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

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

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