import type { Request, Response } from "express";
import asyncHandler from "express-async-handler";
import { prisma } from "../config/db.ts";
import { toIST } from "../utils/time.ts";
import dayjs from "dayjs";
import { razorpay } from "../config/razorpay.ts";
import crypto from "crypto";
import { EnumTransactionStatus } from "@prisma/client";
import PDFDocument from "pdfkit";
import fs from "fs";
import path from "path";

function numberToWords(num: number): string {
    if (num === 0) return "Rupees Zero Only";

    const ones = [
        "", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten",
        "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen"
    ];
    const tens = ["", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety"];

    const convertLessThanOneThousand = (n: number): string => {
        if (n === 0) return "";
        let str = "";
        if (n >= 100) {
            str += ones[Math.floor(n / 100)] + " Hundred ";
            n %= 100;
        }
        if (n > 0) {
            if (str !== "") str += "and ";
            if (n < 20) {
                str += ones[n] + " ";
            } else {
                str += tens[Math.floor(n / 10)] + " ";
                if (n % 10 > 0) {
                    str += ones[n % 10] + " ";
                }
            }
        }
        return str;
    };

    let rupees = Math.floor(num);
    let paise = Math.round((num - rupees) * 100);

    let words = "";

    if (rupees > 0) {
        const crore = Math.floor(rupees / 10000000);
        rupees %= 10000000;
        const lakh = Math.floor(rupees / 100000);
        rupees %= 100000;
        const thousand = Math.floor(rupees / 1000);
        rupees %= 1000;

        if (crore > 0) {
            words += convertLessThanOneThousand(crore) + "Crore ";
        }
        if (lakh > 0) {
            words += convertLessThanOneThousand(lakh) + "Lakh ";
        }
        if (thousand > 0) {
            words += convertLessThanOneThousand(thousand) + "Thousand ";
        }
        if (rupees > 0) {
            words += convertLessThanOneThousand(rupees);
        }
    }

    let result = words.trim();
    if (result !== "") {
        result = "Rupees " + result;
    }

    if (paise > 0) {
        const paiseWords = convertLessThanOneThousand(paise).trim();
        if (result !== "") {
            result += " and " + paiseWords + " Paise";
        } else {
            result = "Paise " + paiseWords;
        }
    }

    if (result !== "") {
        result += " Only";
    }

    return result;
}

export const getPaymentStatuses = asyncHandler(async (req: Request, res: Response) => {
    res.status(200).json({
        message: "Payment statuses fetched successfully",
        data: Object.values(EnumTransactionStatus)
    });
});

export const listPayments = asyncHandler(async (req: Request, res: Response) => {
    const page = parseInt(req.query.page as string) || 1;
    const limit = parseInt(req.query.limit as string) || 10;
    const skip = (page - 1) * limit;
    const search = req.query.search as string;
    const startDate = req.query.startDate as string;
    const endDate = req.query.endDate as string;
    const planType = req.query.planType as string;
    const statusFilter = req.query.status as string;
    const gstTypeFilter = req.query.gstType as string;
    const isAdmin = req.user?.role === "ADMIN";
    const where: any = {};
    if (!isAdmin) {
        where.institutionId = req.user?.institutionId;
    } else if (req.query.institutionId) {
        where.institutionId = req.query.institutionId as string;
    }

    const andConditions: any[] = [];

    if (planType) {
        const plans = await prisma.subscriptionPlan.findMany({
            where: { planType },
            select: { id: true }
        });
        const planIds = plans.map(p => p.id);
        const planIdConditions = planIds.map(id => ({
            metadata: {
                path: '$.planId',
                equals: id
            }
        }));

        andConditions.push({
            OR: [
                {
                    transactionReceipt: {
                        items: {
                            some: {
                                subscription: {
                                    plan: {
                                        planType: planType
                                    }
                                }
                            }
                        }
                    }
                },
                {
                    subscriptionRequest: {
                        subscriptionPlan: {
                            planType: planType
                        }
                    }
                },
                ...planIdConditions
            ]
        });
    }

    if (statusFilter) {
        andConditions.push({ status: statusFilter });
    }

    if (gstTypeFilter) {
        if (gstTypeFilter === 'NO_GST') {
            andConditions.push({ gstType: null });
        } else {
            andConditions.push({ gstType: gstTypeFilter as any });
        }
    }

    if (search) {
        const searchWords = search.split(/\s+/).filter(Boolean);
        const searchOR: any[] = [
            { failureReason: { contains: search } },
            { paymentMethod: { contains: search } },
            { transactionReceipt: { receiptNo: { contains: search } } },
            { transactionReceipt: { items: { some: { subscription: { plan: { planName: { contains: search } } } } } } },
            ...(isAdmin ? [{
                institution: {
                    user: {
                        OR: [
                            { institutionName: { contains: search } },
                            { email: { contains: search } }
                        ]
                    }
                }
            }] : []),
            ...(isAdmin && searchWords.length > 1 ? [{
                institution: {
                    user: {
                        AND: searchWords.map(word => ({
                            OR: [
                                { institutionName: { contains: word } },
                                { email: { contains: word } }
                            ]
                        }))
                    }
                }
            }] : [])
        ];

        const enumValues = Object.values(EnumTransactionStatus);
        const matchedEnum = enumValues.find((value) =>
            value.toLowerCase().includes(search.toLowerCase())
        );

        if (matchedEnum) {
            searchOR.push({
                status: matchedEnum,
            });
        }

        const amountSearch = Number(search);
        if (!isNaN(amountSearch)) {
            searchOR.push({
                totalAmount: { equals: amountSearch },
            });
        }

        andConditions.push({ OR: searchOR });
    }

    if (startDate && endDate) {
        andConditions.push({
            createdAt: {
                gte: new Date(startDate),
                lte: new Date(endDate),
            }
        });
    }

    if (andConditions.length > 0) {
        where.AND = andConditions;
    }

    const successWhere = {
        AND: [where, { status: EnumTransactionStatus.SUCCESS }]
    };
    const pendingWhere = {
        AND: [where, { status: EnumTransactionStatus.PENDING }]
    };
    const attentionWhere = {
        AND: [
            where,
            {
                status: {
                    in: [
                        EnumTransactionStatus.FAILED,
                        EnumTransactionStatus.CANCELLED,
                        EnumTransactionStatus.EXPIRED,
                    ],
                },
            },
        ],
    };

    const [
        total,
        transactions,
        successfulPayments,
        pendingPayments,
        attentionPayments,
        totalCollected,
        institutionsWithPayments,
    ] = await Promise.all([
        prisma.transaction.count({ where }),
        prisma.transaction.findMany({
            where,
            include: {
                ...(isAdmin && {
                    institution: {
                        select: {
                            user: {
                                select: {
                                    institutionName: true,
                                    email: true
                                }
                            }
                        }
                    }
                }),
                transactionReceipt: {
                    include: {
                        items: {
                            include: {
                                student: { include: { user: true } },
                                subscription: { include: { plan: true } }
                            }
                        }
                    }
                },
                subscriptionRequest: {
                    include: {
                        subscriptionPlan: true,
                        student: {
                            include: {
                                user: true
                            }
                        }
                    }
                }
            },
            orderBy: { createdAt: 'desc' },
            skip,
            take: limit
        }),
        prisma.transaction.count({ where: successWhere }),
        prisma.transaction.count({ where: pendingWhere }),
        prisma.transaction.count({ where: attentionWhere }),
        prisma.transaction.aggregate({
            where: statusFilter ? where : successWhere,
            _sum: {
                totalAmount: true,
                taxableAmount: true,
                gstAmount: true,
            },
        }),
        prisma.transaction.findMany({
            where: {
                AND: [
                    where,
                    {
                        institutionId: {
                            not: null,
                        },
                    },
                ],
            },
            distinct: ["institutionId"],
            select: {
                institutionId: true,
            },
        }),
    ]);

    const missingStudentIds = new Set<string>();
    const missingPlanIds = new Set<string>();

    transactions.forEach(t => {
        const metadata = t.metadata as any;
        if (!t.transactionReceipt && metadata) {
            if (!metadata.quoteData && metadata.studentIds) {
                const sIds = Array.isArray(metadata.studentIds)
                    ? metadata.studentIds
                    : String(metadata.studentIds).split(",").map((id: string) => id.trim()).filter(Boolean);
                sIds.forEach((id: string) => missingStudentIds.add(id));
            }
            if (!metadata.quoteData && metadata.planId) {
                missingPlanIds.add(metadata.planId);
            }
        }
    });

    const metadataStudentsMap: Record<string, any> = {};
    if (missingStudentIds.size > 0) {
        const students = await prisma.student.findMany({
            where: { id: { in: Array.from(missingStudentIds) } },
            include: { user: true }
        });
        students.forEach(s => { metadataStudentsMap[s.id] = s; });
    }

    const metadataPlansMap: Record<string, string> = {};
    if (missingPlanIds.size > 0) {
        const plans = await prisma.subscriptionPlan.findMany({
            where: { id: { in: Array.from(missingPlanIds) } }
        });
        plans.forEach(p => { metadataPlansMap[p.id] = p.planName; });
    }

    const paymentsData = transactions.map((t) => {
        const receipt = t.transactionReceipt;
        const items = receipt?.items || [];
        const studentCount = items.length;
        const metadata = t.metadata as any;

        let userName = "N/A";
        let email = "N/A";
        let phone = "N/A";
        let planName = "N/A";

        if (studentCount > 0) {
            if (studentCount === 1 && items[0]?.student?.user) {
                const u = items[0].student.user;
                userName = `${u.firstName} ${u.lastName}`.trim();
                email = u.email || "N/A";
                phone = u.phone || "N/A";
            } else if (studentCount > 1) {
                userName = `Bulk: ${studentCount} Students`;
                email = "Multiple";
            }
            if (items[0]?.subscription?.plan) {
                planName = items[0].subscription.plan.planName;
            }
        } else if (metadata) {
            if (metadata.quoteData) {
                planName = metadata.quoteData.planName || "N/A";
                const quotes = metadata.quoteData.quotes || [];
                if (quotes.length === 1) {
                    userName = quotes[0].name || "N/A";
                    email = quotes[0].email || "N/A";
                } else if (quotes.length > 1) {
                    userName = `Bulk: ${quotes.length} Students`;
                    email = "Multiple";
                }
            } else {
                if (metadata.planId) {
                    planName = metadataPlansMap[metadata.planId] || "N/A";
                }
                if (metadata.studentIds) {
                    const sIds = Array.isArray(metadata.studentIds)
                        ? metadata.studentIds
                        : String(metadata.studentIds).split(",").map((id: string) => id.trim()).filter(Boolean);

                    if (sIds.length === 1) {
                        const s = metadataStudentsMap[sIds[0]];
                        if (s?.user) {
                            userName = `${s.user.firstName} ${s.user.lastName}`.trim();
                            email = s.user.email || "N/A";
                            phone = s.user.phone || "N/A";
                        }
                    } else if (sIds.length > 1) {
                        userName = `Bulk: ${sIds.length} Students`;
                        email = "Multiple";
                    }
                }
            }
        }

        if (planName === "N/A" && t.subscriptionRequest?.subscriptionPlan?.planName) {
            planName = t.subscriptionRequest.subscriptionPlan.planName;
        }

        if (userName === "N/A" && t.subscriptionRequest?.student?.user) {
            const u = t.subscriptionRequest.student.user;
            userName = `${u.firstName} ${u.lastName}`.trim();
            email = u.email || "N/A";
            phone = u.phone || "N/A";
        }
        return {
            id: t.id,
            transactionId: t.paymentId || t.orderId || t.id,
            ...(isAdmin && {
                institution: t.institution?.user?.institutionName || "N/A",
                institutionEmail: t.institution?.user?.email || "N/A"
            }),
            receiptNo: receipt?.receiptNo,
            user: {
                name: userName,
                email: email,
                phone: phone,
            },
            amount: Number(t.totalAmount),
            taxableAmount: Number(t.taxableAmount || 0),
            gstRate: Number(t.gstRate || 0),
            gstAmount: Number(t.gstAmount || 0),
            cgstAmount: Number(t.cgstAmount || 0),
            sgstAmount: Number(t.sgstAmount || 0),
            igstAmount: Number(t.igstAmount || 0),
            gstType: t.gstType,
            customerGstNumber: t.customerGstNumber,
            method: t.paymentMethod,
            date: toIST(t.createdAt),
            status: t.status,
            plan: planName
        };
    });

    res.status(200).json({
        message: "Payments fetched successfully",
        data: paymentsData,
        meta: {
            total,
            page,
            limit,
            totalPages: Math.ceil(total / limit),
            summary: {
                totalTransactions: total,
                successfulPayments,
                pendingPayments,
                attentionPayments,
                institutionCount: institutionsWithPayments.length,
                totalCollected: Number(totalCollected._sum.totalAmount || 0),
                totalTaxableCollected: Number(totalCollected._sum.totalAmount || 0) - Number(totalCollected._sum.gstAmount || 0),
                totalGstCollected: Number(totalCollected._sum.gstAmount || 0),
            },
        }
    });
});

export const getPaymentDetails = asyncHandler(async (req: Request, res: Response) => {
    const { id } = req.params;

    const transaction = await prisma.transaction.findUnique({
        where: { id },
        include: {
            institution: {
                select: {
                    user: {
                        select: {
                            institutionName: true,
                            email: true,
                            gstNumber: true,
                            institutionAddress: true,
                        }
                    }
                }
            },
            transactionReceipt: {
                include: {
                    items: {
                        include: {
                            student: { include: { user: true } },
                            subscription: { include: { plan: true } }
                        }
                    }
                }
            }
        }
    });

    if (!transaction) {
        res.status(404).json({ message: "Transaction not found" });
        return;
    }
    const receipt = transaction.transactionReceipt;
    const items = receipt?.items || [];
    let planName = items[0]?.subscription?.plan?.planName || "N/A";
    const planId = (transaction.metadata as any)?.planId
    if (planId) {
        const plan = await prisma.subscriptionPlan.findUnique({
            where: { id: planId }
        })
        planName = plan.planName
    }
    let studentList = items.map(item => ({
        id: item.student?.id,
        name: item.student?.user ? `${item.student.user.firstName} ${item.student.user.lastName}` : "N/A",
        email: item.student?.user?.email,
        amount: Number(item.amount)
    }));

    // Fallback to metadata for students/plan if receipt doesn't exist yet (PENDING/FAILED)
    if (!receipt && transaction.metadata) {
        const metadata = transaction?.metadata as any;
        const quoteData = metadata?.quoteData;

        if (quoteData?.quotes) {
            studentList = (quoteData.quotes || []).map((q: any) => ({
                id: q.studentId,
                name: q.name || "N/A",
                email: q.email || "N/A",
                amount: Number(q.chargedAmount || 0)
            }));
        } else {
            // Backup fallback: manually fetch from IDs if quoteData is missing (for older records)
            const metadataStudentIds = metadata?.studentIds || [];
            const studentIds = Array.isArray(metadataStudentIds)
                ? metadataStudentIds
                : typeof metadataStudentIds === "string"
                    ? metadataStudentIds.split(",").map((studentId: string) => studentId.trim()).filter(Boolean)
                    : [];
           
            if (studentIds.length > 0) {
                const students = await prisma.student.findMany({
                    where: { id: { in: studentIds } },
                    include: { user: true }
                });
                studentList = students.map(s => ({
                    id: s.id,
                    name: s.user ? `${s.user.firstName} ${s.user.lastName}` : "N/A",
                    email: s.user?.email,
                    amount: Number(transaction.totalAmount) / studentIds.length
                }));
            }
        }
    }

    const data = {
        id: transaction.id,
        amount: Number(transaction.totalAmount),
        taxableAmount: Number(transaction.taxableAmount || 0),
        gstRate: Number(transaction.gstRate || 0),
        gstAmount: Number(transaction.gstAmount || 0),
        cgstAmount: Number(transaction.cgstAmount || 0),
        sgstAmount: Number(transaction.sgstAmount || 0),
        igstAmount: Number(transaction.igstAmount || 0),
        gstType: transaction.gstType,
        customerGstNumber: transaction.customerGstNumber,
        institution: transaction.institution?.user?.institutionName || "N/A",
        institutionEmail: transaction.institution?.user?.email || "N/A",
        institutionGstNumber: transaction.customerGstNumber || transaction.institution?.user?.gstNumber || null,
        institutionAddress: transaction.institution?.user?.institutionAddress || "N/A",
        studentCount: studentList.length,
        planName,
        status: transaction.status,
        failureReason: transaction.failureReason,
        date: toIST(transaction.createdAt),
        endDate: items[0]?.subscription?.endDate,
        paymentMethod: transaction.paymentMethod,
        transactionId: transaction.paymentId || transaction.orderId,
        receiptNo: receipt?.receiptNo,
        students: studentList
    };

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

export const getBulkPaymentQuote = asyncHandler(async (req: Request, res: Response) => {
    const { studentId, studentIds, planId } = req.body;

    // Define validation rules
    const rules = [
        {
            isValid: !planId,
            message: "Plan ID is required"
        },
        {
            isValid: !studentId && !studentIds,
            message: "Either studentId or studentIds must be provided"
        },
        {
            isValid: studentId && studentIds,
            message: "Provide either studentId or studentIds, not both"
        },
        {
            isValid: studentIds && !Array.isArray(studentIds),
            message: "studentIds must be an array"
        },
        {
            isValid: studentIds && Array.isArray(studentIds) && studentIds.length === 0,
            message: "studentIds array cannot be empty"
        }
    ];

    // Loop through the rules and catch the first failure
    for (const rule of rules) {
        if (rule.isValid) {
            res.status(400).json({ message: rule.message });
            return; // Stop execution immediately on the first error
        }
    }
    const targetStudentIds = studentIds ? studentIds : [studentId];
    const plan = await prisma.subscriptionPlan.findUnique({
        where: { id: planId, isActive: true, isDeleted: false }
    });

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

    const students = await prisma.student.findMany({
        where: { id: { in: targetStudentIds } },
        include: {
            user: { select: { firstName: true, lastName: true, email: true } },
            subscriptions: { where: { isCurrent: true }, include: { plan: { select: { planName: true, duration: true, priceAfterDiscount: true, gstPercentage: true } } } }
        }
    });

    const quotes = students.map((student) => {
        const currentSub = student.subscriptions[0];
        let credit = 0;
        let remainingDays = 0;

        if (currentSub && currentSub.status === "ACTIVE" && currentSub.endDate) {
            const now = dayjs();
            const end = dayjs(currentSub.endDate);
            const start = dayjs(currentSub.startDate);

            if (end.isAfter(now)) {
                remainingDays = end.diff(now, 'day');
                const totalDays = end.diff(start, 'day') || 1;
                const dailyRate = Number(currentSub.amount) / totalDays;
                credit = Math.max(0, remainingDays * dailyRate);
            }
        }

        const planPrice = Number(plan.priceAfterDiscount);
        const gstAmount = (planPrice * Number(plan.gstPercentage)) / 100;
        const finalPrice = planPrice
        // + gstAmount;

        const chargedAmount = Math.max(0, finalPrice - credit);

        return {
            studentId: student.id,
            name: `${student.user.firstName} ${student.user.lastName}`,
            email: student.user.email,
            currentPlan: currentSub?.planId || "TRIAL",
            currentPlanName: currentSub?.plan?.planName || "TRIAL",
            currentPlanStatus: currentSub?.status || "TRIAL",
            creditValue: Number(credit.toFixed(2)),
            planPrice: Number(planPrice.toFixed(2)),
            gstAmount: Number(gstAmount.toFixed(2)),
            totalPrice: Number(finalPrice.toFixed(2)),
            chargedAmount: Number(chargedAmount.toFixed(2)),
            remainingDays
        };
    });

    const grandTotal = quotes.reduce((acc, q) => acc + q.chargedAmount, 0);

    res.status(200).json({
        message: "Bulk payment quote generated",
        data: {
            quotes,
            grandTotal: Number(grandTotal.toFixed(2)),
            planName: plan.planName,
            planDuration: plan.duration
        }
    });
});

export const createRazorpayOrder = asyncHandler(async (req: Request, res: Response) => {
    const { amount, currency = "INR", studentIds, studentId, planId, quoteData, subscriptionRequestId } = req.body;
    const rules = [
        {
            isValid: !planId,
            message: "Plan ID is required"
        },
        {
            isValid: !studentId && !studentIds,
            message: "Either studentId or studentIds must be provided"
        },
        {
            isValid: studentId && studentIds,
            message: "Provide either studentId or studentIds, not both"
        },
        {
            isValid: studentIds && !Array.isArray(studentIds),
            message: "studentIds must be an array"
        },
        {
            isValid: studentIds && Array.isArray(studentIds) && studentIds.length === 0,
            message: "studentIds array cannot be empty"
        }
    ];

    // Loop through the rules and catch the first failure
    for (const rule of rules) {
        if (rule.isValid) {
            res.status(400).json({ message: rule.message });
            return; // Stop execution immediately on the first error
        }
    }
    const targetStudentIds = studentIds ? studentIds : [studentId];
    const students = await prisma.student.findMany({
        where: { id: { in: targetStudentIds } }
    })

    if (students.length < 1 || targetStudentIds?.length < 1 || targetStudentIds?.length !== students.length) {
        res.status(400).json({ message: "Give the correct student id or ids" });
        return;
    }
    if (!Number.isFinite(Number(amount)) || Number(amount) <= 0) {
        res.status(400).json({ message: "Valid payment amount is required" });
        return;
    }

    // If student, validate they're only paying for themselves
    if (req.user?.role === "STUDENT") {
        const student = await prisma.student.findUnique({ where: { userId: req.user.id } });
        if (!student) {
            res.status(404).json({ message: "Student not found" });
            return;
        }
        if (targetStudentIds.length !== 1 || targetStudentIds[0] !== student.id) {
            res.status(403).json({ message: "Students can only pay for themselves" });
            return;
        }
        if (subscriptionRequestId) {
            const subRequest = await prisma.subscriptionRequest.findFirst({
                where: { id: subscriptionRequestId, studentId: student.id, status: { in: ["PENDING"] } }
            });
            if (!subRequest) {
                res.status(404).json({ message: "Valid subscription request not found" });
                return;
            }
            if (subRequest.expiryDate && new Date() > subRequest.expiryDate) {
                res.status(400).json({ message: "Subscription request has expired" });
                return;
            }
        }
    }

    try {

        const institutionId = req.user?.institutionId

        if (!req.user?.id) {
            res.status(404).json({ message: "Logined user not found" });
            return;
        }
        const institution = await prisma.institution.findFirst({
            where: { id: institutionId },
            select: {
                user: {
                    select: {
                        state: true,
                        gstNumber: true
                    }
                }
            }
        })

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

        const plan = await prisma.subscriptionPlan.findUnique({
            where: { id: planId }
        });

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

        const customerGstNumber = institution.user.gstNumber
        const isIntraState = String(institution.user.state ?? "").trim().toUpperCase() === "TAMIL NADU";
        const totalAmount = Number(amount);
        const gstRate = Number(plan.gstPercentage || 0);
        const taxableAmount = +(totalAmount / (1 + gstRate / 100)).toFixed(2);
        const gstAmount = +(totalAmount - taxableAmount).toFixed(2);

        // Example:
        // intra-state => CGST + SGST
        // inter-state => IGST

        let cgstAmount = 0;
        let sgstAmount = 0;
        let igstAmount = 0;
        let gstType: "CGST_SGST" | "IGST" = "CGST_SGST";

        if (isIntraState) {
            cgstAmount = +(gstAmount / 2).toFixed(2);
            sgstAmount = +(gstAmount / 2).toFixed(2);
        } else {
            igstAmount = gstAmount;
            gstType = "IGST";
        }
        const options = {
            amount: Math.round(Number(amount) * 100), // amount in the smallest currency unit
            currency,
            receipt: `rcpt_${Date.now()}`,
        };
        const order = await razorpay.orders.create(options);

        // CREATE PENDING TRANSACTION
        await prisma.transaction.create({
            data: {
                totalAmount: Number(amount),
                taxableAmount,
                gstRate,
                gstAmount,
                cgstAmount,
                sgstAmount,
                igstAmount,
                gstType,
                customerGstNumber,
                createdById: req.user?.id,
                institutionId: req.user?.institutionId || null,
                paymentMethod: "",
                orderId: order.id,
                status: "PENDING",
                ...(subscriptionRequestId && { subscriptionRequestId }),
                metadata: {
                    studentIds: targetStudentIds, // Expecting array
                    planId: planId,
                    quoteData: quoteData ? quoteData : {} // Persist full quote data for itemized view
                }
            }
        });

        res.status(200).json({
            message: "Razorpay order created",
            data: order
        });
    } catch (error: any) {
        console.log(error);
        res.status(500).json({ message: error?.error?.description || error?.message || "Failed to create Razorpay order" });
    }
});

export const executeBulkSubscriptionPaymentProcess = async (params: { orderId: string; }) => {
    const { orderId } = params;
    const order = await razorpay.orders.fetch(orderId);
    const payments = await razorpay.orders.fetchPayments(order.id);

    if (order.status == "created") {
        throw new Error("Order only created. Not paid")
    }

    if (order.status !== "paid") {
        throw new Error("Order has not paid")
    }

    const payment = payments.items.find(p => p.status === "captured");

    if (!payment) {
        throw new Error("Captured payment not found");
    }

    return await prisma.$transaction(async (tx) => {
        // Atomically claim the transaction (only PENDING → PROCESSING succeeds)
        // Prevents concurrent webhook + verification / double-click race conditions
        const claimResult = await tx.transaction.updateMany({
            where: { orderId, status: "PENDING" },
            data: { status: "PROCESSING" }
        });

        let transaction = await tx.transaction.findFirst({ where: { orderId } });
        if (!transaction) throw new Error("Transaction not found");
        if (claimResult.count === 0) {
            if (transaction.status === "SUCCESS") return transaction;
            throw new Error(`Transaction is in ${transaction.status} state, cannot process`);
        }

        const { id: paymentId, method: paymentMethod } = payment
        const { subscriptionRequestId, institutionId } = transaction

        const institution = await tx.institution.findUnique({
            where: { id: institutionId },
            select: {
                user: { select: { gstNumber: true, state: true } }
            }
        });
        if (!institution) {
            throw new Error("Institution not found");
        }

        const metadata = transaction.metadata as any;

        if (!metadata?.planId) {
            throw new Error("Plan ID missing");
        }

        if (!Array.isArray(metadata?.studentIds) || metadata?.studentIds.length < 1) {
            throw new Error("Student IDs missing");
        }
        const { studentIds, planId } = (transaction.metadata as any);

        const plan = await prisma.subscriptionPlan.findUnique({
            where: { id: planId }
        });

        if (!Array.isArray(studentIds) || studentIds.length < 1) {
            throw new Error("Students not found");
        }
        if (!plan) {
            throw new Error("Subscription plan not found");
        }

        let grandTotal = 0;
        const studentQuotes = [];

        for (const sId of studentIds) {
            const student = await tx.student.findUnique({
                where: { id: sId },
                include: {
                    user: true,
                    subscriptions: { where: { isCurrent: true, institutionId: institutionId } },
                    institutions: { where: { institutionId: institutionId, deletedAt: null, isVerified: true }, take: 1 }
                }
            });

            if (!student) continue;

            const currentSub = student.subscriptions[0];
            
            const quoteForStudent = metadata?.quoteData?.quotes?.find((q: any) => q.studentId === sId);
            
            let credit = 0;
            let finalPrice = Number(plan.priceAfterDiscount);
            let chargedAmount = 0;

            if (quoteForStudent) {
                credit = Number(quoteForStudent.creditValue || 0);
                finalPrice = Number(quoteForStudent.totalPrice || finalPrice);
                chargedAmount = Number(quoteForStudent.chargedAmount || 0);
            } else {
                if (currentSub && currentSub.status === "ACTIVE" && currentSub.endDate) {
                    const now = dayjs();
                    const end = dayjs(currentSub.endDate);
                    const start = dayjs(currentSub.startDate);
                    if (end.isAfter(now)) {
                        const remainingDays = end.diff(now, 'day');
                        const totalDays = end.diff(start, 'day') || 1;
                        const dailyRate = Number(currentSub.amount) / totalDays;
                        credit = Math.max(0, remainingDays * dailyRate);
                    }
                }
                chargedAmount = Math.max(0, finalPrice - credit);
            }

            grandTotal += chargedAmount;
            studentQuotes.push({
                studentId: sId,
                institutionId: institutionId,
                previousSubscriptionId: currentSub?.id || null,
                studentInstitutionId: student.institutions[0]?.id || null,
                email: student.user.email,
                credit,
                chargedAmount,
                finalPrice,
            });
        }

        const customerGstNumber = institution?.user?.gstNumber || null;
        const isIntraState = String(institution?.user?.state ?? "").trim().toUpperCase() === "TAMIL NADU";
        const totalAmount = Number(grandTotal);
        const gstRate = Number(plan.gstPercentage || 0);
        const taxableAmount = +(totalAmount / (1 + gstRate / 100)).toFixed(2);
        const gstAmount = +(totalAmount - taxableAmount).toFixed(2);
        let cgstAmount = 0;
        let sgstAmount = 0;
        let igstAmount = 0;
        let gstType: "CGST_SGST" | "IGST" = "CGST_SGST";

        if (isIntraState) {
            cgstAmount = +(gstAmount / 2).toFixed(2);
            sgstAmount = +(gstAmount - cgstAmount).toFixed(2);
        } else {
            igstAmount = gstAmount;
            gstType = "IGST";
        }

        transaction = await tx.transaction.update({
            where: { id: transaction.id },
            data: {
                paymentId,
                paymentMethod,
                signature: null,
                taxableAmount,
                gstRate,
                gstAmount,
                cgstAmount,
                sgstAmount,
                igstAmount,
                gstType,
                customerGstNumber,
                totalAmount: grandTotal, // Update with calculated total
                status: "SUCCESS"
            }
        });

        const sequence = await tx.referenceSequence.findFirst({
            where: { type: "RECEIPT", isActive: true, endedAt: null }
        });

        if (!sequence) {
            throw new Error("Sequence not found");
        }

        const updatedSequence = await tx.referenceSequence.update({
            where: { id: sequence.id },
            data: { lastCount: { increment: 1 } }
        });

        const receiptNo = `${updatedSequence.prefix}${updatedSequence.lastCount}`;

        const transactionReceipt = await tx.transactionReceipt.create({
            data: {
                transactionId: transaction.id,
                institutionId: institutionId,
                receiptNo,
                totalAmount: grandTotal,
                status: "PAID",
                issuedAt: new Date(),
            }
        });

        for (const quote of studentQuotes) {
            await tx.subscription.updateMany({
                where: { studentId: quote.studentId, institutionId, isCurrent: true },
                data: { isCurrent: false }
            });

            const newSub = await tx.subscription.create({
                data: {
                    studentId: quote.studentId,
                    planId: plan.id,
                    email: quote.email,
                    amount: quote.finalPrice,
                    chargedAmount: quote.chargedAmount,
                    creditUsed: quote.credit,
                    startDate: new Date(),
                    endDate: dayjs().add(plan.duration, 'day').toDate(),
                    status: "ACTIVE",
                    isCurrent: true,
                    action: "UPGRADE",
                    institutionId: quote.institutionId,
                    previousSubscriptionId: quote.previousSubscriptionId,
                    studentInstitutionId: quote.studentInstitutionId
                }
            });

            await tx.transactionReceiptItem.create({
                data: {
                    receiptId: transactionReceipt.id,
                    subscriptionId: newSub.id,
                    studentId: quote.studentId,
                    amount: quote.chargedAmount,
                }
            });
        }

        // If linked to a subscription request, mark as PAID
        if (subscriptionRequestId) {
            const request = await tx.subscriptionRequest.findUnique({
                where: { id: subscriptionRequestId }
            });
            if (request) {
                const currentSub = await tx.subscription.findFirst({
                    where: { studentId: request.studentId, institutionId, isCurrent: true }
                });
                if (currentSub) {
                    await tx.subscriptionRequest.update({
                        where: { id: subscriptionRequestId },
                        data: {
                            status: "PAID",
                            paidAt: new Date(),
                            razorpayOrderId: order.id,
                            razorpayPaymentId: paymentId,
                            subscriptionId: currentSub.id,
                        }
                    });
                }
            }
        }

        return transaction;
    });
};

export const verifyAndProcessBulkPayment = asyncHandler(async (req: Request, res: Response) => {
    const { razorpay_order_id, razorpay_payment_id, razorpay_signature } = req.body;

    if (!razorpay_order_id || !razorpay_payment_id || !razorpay_signature) {
        res.status(400).json({ message: "Missing required verification fields" });
        return;
    }

    // Verify signature
    const secret = process.env.RAZORPAY_KEY_SECRET || "placeholder_secret";
    const body = razorpay_order_id + "|" + razorpay_payment_id;
    const expectedSignature = crypto
        .createHmac("sha256", secret)
        .update(body.toString())
        .digest("hex");

    if (expectedSignature !== razorpay_signature) {
        res.status(400).json({ message: "Invalid payment signature" });
        return;
    }
    const payment = await razorpay.payments.fetch(razorpay_payment_id);

    // Verify actual payment success
    if (payment.status !== "captured") {
        res.status(400).json({
            message: `Payment not successful. Current status: ${payment.status}`
        });
        return;
    }

    try {
        const result = await executeBulkSubscriptionPaymentProcess({ orderId: razorpay_order_id, });

        res.status(200).json({
            message: "Payment verified and subscriptions updated",
            data: {
                id: result?.id,
                amount: result?.totalAmount,
                status: result?.status
            }
        });
        return
    } catch (error: any) {
        res.status(500).json({ message: error.message || "Failed to process bulk payment after verification" });
    }
});

export const razorpayWebhook = asyncHandler(async (req: Request, res: Response) => {
    const signature = req.headers["x-razorpay-signature"] as string;
    const secret = process.env.RAZORPAY_WEBHOOK_SECRET || "placeholder_secret";

    // Use the raw body we captured in index.ts
    const expectedSignature = crypto.createHmac("sha256", secret).update((req as any).rawBody).digest("hex");

    if (expectedSignature !== signature) {
        res.status(400).json({ message: "Invalid webhook signature" });
        return;
    }

    const event = req.body.event;
    console.log(`Razorpay Webhook Event: ${event}`);

    if (event === "order.paid") {
        const orderId = req.body.payload.order.entity.id;
        try {
            await executeBulkSubscriptionPaymentProcess({ orderId });
            console.log(`Successfully processed payment via webhook for Order: ${orderId}`);
        } catch (err) {
            console.error("Error processing webhook payment:", err);
        }
    } else if (event === "payment.failed") {
        const orderId = req.body.payload.payment.entity.order_id;
        const errorDescription = req.body.payload.payment.entity.error_description;

        await prisma.transaction.updateMany({
            where: { orderId: orderId, status: "PENDING" },
            data: {
                status: "FAILED",
                failureReason: errorDescription || "Payment failed",
                paymentId: req.body.payload.payment.entity.id
            }
        });
    } else if (event === "refund.processed") {
        const paymentId = req.body.payload.payment.entity.id;
        const amountRefunded = req.body.payload.refund.entity.amount;
        const totalAmount = req.body.payload.payment.entity.amount;

        const transactionStatus = amountRefunded < totalAmount ? "PARTIAL_REFUND" : "REFUNDED";

        await prisma.transaction.updateMany({
            where: { paymentId: paymentId },
            data: {
                status: transactionStatus,
                failureReason: `Refund processed: ${amountRefunded / 100}`,
            }
        });

        // Deactivate subscriptions if fully refunded
        if (transactionStatus === "REFUNDED") {
            const transaction = await prisma.transaction.findFirst({
                where: { paymentId: paymentId },
                include: { transactionReceipt: { include: { items: true } } }
            });
            if (transaction) {
                const subscriptionIds = transaction.transactionReceipt ? transaction.transactionReceipt.items.map(i => i.subscriptionId) : [];
                await prisma.subscription.updateMany({
                    where: { id: { in: subscriptionIds } },
                    data: { status: "EXPIRED", isCurrent: false }
                });
            }
        }
    }

    res.status(200).json({ status: "ok" });
});

export const cancelPayment = asyncHandler(async (req: Request, res: Response) => {
    const { orderId, reason } = req.body;

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

    const result = await prisma.transaction.updateMany({
        where: { orderId, status: "PENDING" },
        data: {
            status: "CANCELLED",
            failureReason: reason || "User cancelled payment process",
        }
    });

    if (result.count === 0) {
        const existing = await prisma.transaction.findFirst({ where: { orderId } });
        if (existing) {
            res.status(400).json({ message: `Payment cannot be cancelled (status: ${existing.status})` });
            return;
        }
    }

    res.status(200).json({ message: "Payment cancellation tracked" });
});

export const downloadPaymentReceipt = asyncHandler(async (req: Request, res: Response) => {
    try {
        const { id } = req.params;

        const transaction = await prisma.transaction.findUnique({
            where: { id },
            include: {
                institution: {
                    include: {
                        user: {
                            select: {
                                institutionName: true,
                                gstNumber: true,
                                institutionAddress: true
                            }
                        }
                    }
                },
                transactionReceipt: {
                    include: {
                        items: {
                            include: {
                                subscription: { include: { plan: true } }
                            }
                        }
                    }
                }
            }
        });

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

        const receipt = transaction.transactionReceipt;
        const metadata = transaction.metadata as any;
        const gstRate = Number(transaction.gstRate || 0);
        const toAmount = (value: unknown) => Number(value || 0);
        const reIndexItems = (items: Array<{ description: string; quantity: number; unitPrice: number; total: number }>) =>
            items.map((item, index) => ({ no: index + 1, ...item }));
        const groupItemsByDescription = (items: Array<{ description: string; amount: number }>) => {
            const grouped = new Map<string, { description: string; quantity: number; total: number }>();

            for (const item of items) {
                const description = item.description || "Subscription Plan";
                const current = grouped.get(description) || { description, quantity: 0, total: 0 };
                current.quantity += 1;
                current.total += item.amount;
                grouped.set(description, current);
            }

            return reIndexItems(
                Array.from(grouped.values()).map((item) => {
                    const total = gstRate > 0 ? item.total / (1 + gstRate / 100) : item.total;
                    return {
                        description: item.description,
                        quantity: item.quantity,
                        unitPrice: item.quantity > 0 ? total / item.quantity : total,
                        total,
                    };
                })
            );
        };
        const getStudentCount = (studentIds: unknown) => {
            if (Array.isArray(studentIds)) return studentIds.length;
            if (typeof studentIds === "string") return studentIds.split(",").filter(Boolean).length;
            return 0;
        };

        let pdfItems = groupItemsByDescription(
            (receipt?.items || []).map((item) => ({
                description: item.subscription?.plan?.planName || "Subscription Plan",
                amount: toAmount(item.amount || item.subscription?.chargedAmount),
            }))
        );

        if (pdfItems.length === 0 && metadata) {
            const quoteData = metadata?.quoteData;
            if (quoteData) {
                pdfItems = groupItemsByDescription(
                    (quoteData.quotes || []).map((quote: any) => ({
                        description: quoteData.planName || "Subscription Plan",
                        amount: toAmount(quote.chargedAmount),
                    }))
                );
            } else {
                const studentIds = metadata?.studentIds || [];
                const planId = metadata?.planId;
                let planName = "Subscription Plan";
                if (planId) {
                    const plan = await prisma.subscriptionPlan.findUnique({ where: { id: planId } });
                    planName = plan?.planName || planName;
                }
                const quantity = getStudentCount(studentIds) || 1;
                const total = gstRate > 0
                    ? Number(transaction.taxableAmount || 0)
                    : Number(transaction.totalAmount || 0);
                pdfItems = reIndexItems([{
                    description: planName,
                    quantity,
                    unitPrice: quantity > 0 ? total / quantity : total,
                    total,
                }]);
            }
        };

        if (pdfItems.length === 0) {
            const total = gstRate > 0
                ? Number(transaction.taxableAmount || 0)
                : Number(transaction.totalAmount || 0);
            pdfItems = reIndexItems([{
                description: "Subscription Payment",
                quantity: 1,
                unitPrice: total,
                total,
            }]);
        }

        const platformUser = await prisma.user.findFirst({
            where: {
                role: "ADMIN",
                email: "admin@gmail.com"
            },
            select: {
                institutionAddress: true,
                phone: true,
                gstNumber: true
            }
        });
        if (!platformUser) {
            throw new Error("Platform Admin not found");
        }
        const gstAmount = Number(transaction.gstAmount || 0);
        const cgstAmount = Number(transaction.cgstAmount || 0);
        const sgstAmount = Number(transaction.sgstAmount || 0);
        const igstAmount = Number(transaction.igstAmount || 0);
        const grandTotal = Number(receipt?.totalAmount || transaction.totalAmount || 0);
        const netTotal = gstAmount > 0
            ? Number(transaction.taxableAmount || 0)
            : grandTotal;
        const hasGst = gstRate > 0 && gstAmount > 0;
        const gstType = transaction.gstType;

        const data = {
            receiptNo: receipt?.receiptNo || transaction.orderId || transaction.id,
            receiptDate: dayjs(receipt?.createdAt || transaction.createdAt).format("DD-MM-YYYY HH:mm A"),

            from: {
                name: "Exam Infra",
                address: platformUser.institutionAddress || "N/A",
                phone: platformUser.phone || "N/A",
                gst: platformUser.gstNumber || "N/A",
                sac: "998313",
            },
            to: {
                name: transaction.institution?.user.institutionName || "N/A",
                address: transaction.institution?.user.institutionAddress || "N/A",
                gst: transaction.customerGstNumber || transaction.institution?.user.gstNumber || "N/A",
            },
            items: pdfItems,
            netTotal,
            gstRate,
            gstAmount,
            cgstAmount,
            sgstAmount,
            igstAmount,
            gstType,
            hasGst,
            grandTotal,
        };

        const doc = new PDFDocument({
            size: "A4",
            margin: 0,
        });

        // ======================
        // COLORS
        // ======================

        const colors = {
            primary: "#1976d2",
            text: "#1f2937",
            subText: "#6b7280",
            border: "#e5e7eb",
            light: "#f5f7fa",
            white: "#ffffff",
            success: "#16a34a",
            textLight: "#ffffff",
            textMuted: "#cbd5e1",
        };


        const drawPageDecoration = () => {
            doc.save();
            doc
                .rect(0, 0, 595, 842)
                .fill("#eef1f5"); // soft light gray background

            doc.roundedRect(18, 18, 559, 806, 8).fill(colors.white);
            doc.restore();
        };

        // Draw for the first page
        drawPageDecoration();

        // Register for subsequent pages
        doc.on("pageAdded", () => {
            drawPageDecoration();
        });

        const safeReceiptFileName = String(data.receiptNo).replace(/[^a-zA-Z0-9_-]/g, "-");
        const logoPath = path.resolve(process.cwd(), "public", "logo.png");

        res.setHeader("Content-Type", "application/pdf");

        res.setHeader(
            "Content-Disposition",
            `inline; filename="receipt-${safeReceiptFileName}.pdf"`
        );

        doc.pipe(res);

        function money(val) {
            return `Rs.${val.toFixed(2)}`;
        }
        function normalizeMoney(val) {
            return `${val.toFixed(2)}`;
        }


        // ======================
        // HEADER
        // ====================== 

        doc
            .roundedRect(20, 20, 555, 78, 10)
            .fill(colors.primary);

        if (fs.existsSync(logoPath)) {
            doc
                .roundedRect(36, 34, 42, 42, 6)
                .fill(colors.white);
            doc.image(logoPath, 40, 38, {
                fit: [34, 34],
                align: "center",
                valign: "center",
            });
        }

        // Company Name
        doc
            .fillColor(colors.textLight)
            .font("Helvetica-Bold")
            .fontSize(20)
            .text(data.from.name, fs.existsSync(logoPath) ? 88 : 36, 46);

        // Receipt Title
        doc
            .fontSize(18)
            .text("RECEIPT", 440, 46);

        // Optional Subtitle (clean + minimal)
        doc
            .font("Helvetica")
            .fontSize(9)
            .fillColor(colors.textMuted)
            .text("Payment Confirmation", 440, 68);

        // ======================
        // INFO STRIP
        // ======================

        const infoY = 110;

        doc
            .roundedRect(32, infoY, 530, 52, 5)
            .fill(colors.light);

        doc.fillColor(colors.text);

        doc
            .font("Helvetica-Bold")
            .fontSize(8)
            .text("Receipt No", 45, infoY + 10);

        doc
            .font("Helvetica")
            .fontSize(10)
            .text(data.receiptNo, 45, infoY + 24);

        doc
            .font("Helvetica-Bold")
            .fontSize(8)
            .text("Date", 430, infoY + 10);

        doc
            .font("Helvetica")
            .fontSize(10)
            .text(data.receiptDate, 430, infoY + 24);

        // ======================
        // FROM + BILL TO
        // ======================

        const sectionY = 170;

        // Calculate card heights dynamically
        doc.font("Helvetica-Bold").fontSize(12);
        const leftNameHeight = doc.heightOfString(data.from.name, { width: 190 });
        doc.font("Helvetica").fontSize(9);
        const leftAddressHeight = doc.heightOfString(data.from.address, { width: 190 });
        const leftPhoneHeight = doc.heightOfString(`Phone: ${data.from.phone}`, { width: 190 });
        const leftGstHeight = doc.heightOfString(`GSTIN: ${data.from.gst}`, { width: 190 });
        const leftSacHeight = doc.heightOfString(`SAC: ${data.from.sac}`, { width: 190 });
        const leftContentHeight = 18 + leftNameHeight + 8 + leftAddressHeight + 6 + leftPhoneHeight + 4 + leftGstHeight + 4 + leftSacHeight + 18;

        doc.font("Helvetica-Bold").fontSize(9);
        const rightTitleHeight = doc.heightOfString("Billed To", { width: 190 });
        doc.font("Helvetica-Bold").fontSize(12);
        const rightNameHeight = doc.heightOfString(data.to.name, { width: 190 });
        doc.font("Helvetica").fontSize(9);
        const rightAddressHeight = doc.heightOfString(data.to.address, { width: 190 });
        const rightGstHeight = doc.heightOfString(`GSTIN: ${data.to.gst}`, { width: 190 });
        const rightContentHeight = 10 + rightTitleHeight + 8 + rightNameHeight + 6 + rightAddressHeight + 6 + rightGstHeight + 18;

        const cardHeight = Math.max(100, leftContentHeight, rightContentHeight);

        // LEFT CARD
        doc
            .roundedRect(32, sectionY, 250, cardHeight, 5)
            .fill(colors.light);

        let currentLeftY = sectionY + 18;
        doc
            .fillColor(colors.text)
            .font("Helvetica-Bold")
            .fontSize(12)
            .text(data.from.name, 42, currentLeftY);
        currentLeftY += leftNameHeight + 8;

        doc
            .font("Helvetica")
            .fontSize(9)
            .fillColor(colors.subText)
            .text(data.from.address, 42, currentLeftY, {
                width: 190,
            });
        currentLeftY += leftAddressHeight + 6;

        doc.text(`Phone: ${data.from.phone}`, 42, currentLeftY);
        currentLeftY += leftPhoneHeight + 4;

        doc.text(`GSTIN: ${data.from.gst}`, 42, currentLeftY);
        currentLeftY += leftGstHeight + 4;

        doc.text(`SAC: ${data.from.sac}`, 42, currentLeftY);

        // RIGHT CARD
        doc
            .roundedRect(312, sectionY, 250, cardHeight, 5)
            .fill(colors.light);

        let currentRightY = sectionY + 10;
        doc
            .fillColor(colors.subText)
            .font("Helvetica-Bold")
            .fontSize(9)
            .text("BILLED TO", 322, currentRightY);
        currentRightY += rightTitleHeight + 8;

        doc
            .fillColor(colors.text)
            .font("Helvetica-Bold")
            .fontSize(12)
            .text(data.to.name, 322, currentRightY);
        currentRightY += rightNameHeight + 6;

        doc
            .font("Helvetica")
            .fontSize(9)
            .fillColor(colors.subText)
            .text(data.to.address, 322, currentRightY, {
                width: 190,
            });
        currentRightY += rightAddressHeight + 6;

        doc.text(`GSTIN: ${data.to.gst}`, 322, currentRightY);

        // ======================
        // TABLE
        // ======================

        const tableTop = sectionY + cardHeight + 15;

        const cols = {
            no: 42,
            desc: 80,
            qty: 355,
            unit: 420,
            total: 500,
        };

        // TABLE HEADER
        doc
            .rect(32, tableTop, 530, 28)
            .fill(colors.primary);

        doc
            .fillColor(colors.white)
            .font("Helvetica-Bold")
            .fontSize(9);

        doc.text("#", cols.no, tableTop + 9);
        doc.text("Description", cols.desc, tableTop + 9);
        doc.text("Qty", cols.qty, tableTop + 9);
        doc.text("Price", cols.unit, tableTop + 9);
        doc.text("Total", cols.total, tableTop + 9);

        // ROWS
        let rowY = tableTop + 28;

        data.items.forEach((item, index) => {
            doc.font("Helvetica").fontSize(9);
            const descHeight = doc.heightOfString(item.description, { width: 220 });
            const rowHeight = Math.max(30, descHeight + 16); // padding top & bottom

            doc
                .rect(32, rowY, 530, rowHeight)
                .fill(index % 2 === 0 ? "#fafafa" : "#ffffff");

            doc
                .fillColor(colors.text)
                .font("Helvetica")
                .fontSize(9);

            const verticalOffset = (rowHeight - 9) / 2;

            doc.text(item.no.toString(), cols.no, rowY + verticalOffset);

            doc.text(item.description, cols.desc, rowY + 8, {
                width: 220,
            });

            doc.text(item.quantity.toString(), cols.qty, rowY + verticalOffset);

            doc.text(
                item.unitPrice.toFixed(2),
                cols.unit,
                rowY + verticalOffset
            );

            doc.text(
                item.total.toFixed(2),
                cols.total,
                rowY + verticalOffset
            );

            rowY += rowHeight;
        });

        // ======================
        // TOTALS
        // ======================

        let totalsY = rowY + 14;
        const taxRows = data.hasGst
            ? data.gstType === "IGST"
                ? [{ label: `IGST (${data.gstRate}%)`, value: data.igstAmount || data.gstAmount }]
                : [
                    { label: `CGST (${data.gstRate / 2}%)`, value: data.cgstAmount },
                    { label: `SGST (${data.gstRate / 2}%)`, value: data.sgstAmount },
                ]
            : [{ label: "GST", value: "No GST" }];
        const amountInWords = numberToWords(data.grandTotal);
        doc.font("Helvetica").fontSize(9);
        const amountInWordsHeight = doc.heightOfString(amountInWords, { width: 170 });
        const totalsBoxHeight = 70 + (taxRows.length * 20);
        const totalSectionHeight = totalsBoxHeight + 24 + amountInWordsHeight;

        if (totalsY + totalSectionHeight > 760) {
            doc.addPage();
            totalsY = 40;
        }

        doc
            .roundedRect(372, totalsY, 190, totalsBoxHeight, 5)
            .fill(colors.light);

        doc
            .fillColor(colors.text)
            .font("Helvetica")
            .fontSize(9);

        let totalLineY = totalsY + 14;

        doc.text("Taxable Amount", 385, totalLineY);

        doc.text(
            normalizeMoney(data.netTotal),
            470,
            totalLineY,
            {
                align: "right",
                width: 70,
            }
        );

        totalLineY += 20;

        taxRows.forEach((row) => {
            doc.text(row.label, 385, totalLineY);
            const valStr = typeof row.value === "string" ? row.value : normalizeMoney(row.value);
            doc.text(
                valStr,
                470,
                totalLineY,
                {
                    align: "right",
                    width: 70,
                }
            );
            totalLineY += 20;
        });

        // Divider
        doc
            .moveTo(385, totalLineY)
            .lineTo(542, totalLineY)
            .strokeColor("#d1d5db")
            .stroke();
        totalLineY += 10;

        doc
            .font("Helvetica-Bold")
            .fontSize(11)
            .fillColor(colors.primary);

        doc.text("Grand Total", 385, totalLineY);

        doc.text(
            money(data.grandTotal),
            458,
            totalLineY,
            {
                align: "right",
                width: 82,
            }
        );

        // Amount in Words
        doc
            .fillColor(colors.text)
            .font("Helvetica-Bold")
            .fontSize(9)
            .text("Amount in Words:", 372, totalsY + totalsBoxHeight + 12, {
                align: "right",
                width: 170
            });

        doc
            .font("Helvetica")
            .fontSize(9)
            .fillColor(colors.subText)
            .text(amountInWords, 372, totalsY + totalsBoxHeight + 24, {
                align: "right",
                width: 170
            });

        // ======================
        // FOOTER
        // ======================

        doc
            .fillColor("#9ca3af")
            .font("Helvetica")
            .fontSize(8)
            .text(
                "Thank you for your business",
                0,
                795,
                {
                    align: "center",
                }
            );

        doc.end();
    } catch (error: any) {
        if (!res.headersSent) {
            res.status(500).json({ message: error.message });
        }
    }
});

export const paymentReportsPDF = asyncHandler(async (req: Request, res: Response) => {
    const { search, status: statusFilter, planType, startDate, endDate } = req.query as Record<string, string>;
    const isAdmin = req.user?.role === "ADMIN";

    const where: any = {};
    if (!isAdmin) {
        where.institutionId = req.user?.institutionId;
    }

    const andConditions: any[] = [];

    if (planType) {
        const plans = await prisma.subscriptionPlan.findMany({
            where: { planType },
            select: { id: true }
        });
        const planIds = plans.map(p => p.id);
        const planIdConditions = planIds.map(id => ({
            metadata: {
                path: '$.planId',
                equals: id
            }
        }));

        andConditions.push({
            OR: [
                {
                    transactionReceipt: {
                        items: {
                            some: {
                                subscription: {
                                    plan: {
                                        planType: planType
                                    }
                                }
                            }
                        }
                    }
                },
                {
                    subscriptionRequest: {
                        subscriptionPlan: {
                            planType: planType
                        }
                    }
                },
                ...planIdConditions
            ]
        });
    }

    if (statusFilter) {
        andConditions.push({ status: statusFilter });
    }

    if (search) {
        const searchOR: any[] = [
            { failureReason: { contains: search } },
            { paymentMethod: { contains: search } },
            { transactionReceipt: { receiptNo: { contains: search } } },
            ...(isAdmin ? [{ institution: { user: { OR: [{ institutionName: { contains: search } }, { email: { contains: search } }] } } }] : [])
        ];
        andConditions.push({ OR: searchOR });
    }

    if (startDate && endDate) {
        andConditions.push({
            createdAt: { gte: new Date(startDate), lte: new Date(endDate) }
        });
    }

    if (andConditions.length > 0) {
        where.AND = andConditions;
    }

    const transactions = await prisma.transaction.findMany({
        where,
        include: {
            institution: { select: { user: { select: { institutionName: true, email: true } } } },
            transactionReceipt: {
                include: {
                    items: {
                        include: {
                            student: { include: { user: true } },
                            subscription: { include: { plan: true } }
                        }
                    }
                }
            },
            subscriptionRequest: {
                include: {
                    subscriptionPlan: true,
                    student: {
                        include: {
                            user: true
                        }
                    }
                }
            }
        },
        orderBy: { createdAt: "desc" }
    });

    const formatMoney = (n: number) => {
        const formatted = new Intl.NumberFormat("en-IN", { style: "currency", currency: "INR", maximumFractionDigits: 2 }).format(n || 0);
        return formatted.replace("₹", "INR ");
    };

    const missingStudentIds = new Set<string>();
    const missingPlanIds = new Set<string>();

    transactions.forEach(t => {
        const metadata = t.metadata as any;
        if (!t.transactionReceipt && metadata) {
            if (!metadata.quoteData && metadata.studentIds) {
                const sIds = Array.isArray(metadata.studentIds)
                    ? metadata.studentIds
                    : String(metadata.studentIds).split(",").map((id: string) => id.trim()).filter(Boolean);
                sIds.forEach((id: string) => missingStudentIds.add(id));
            }
            if (!metadata.quoteData && metadata.planId) {
                missingPlanIds.add(metadata.planId);
            }
        }
    });

    const metadataStudentsMap: Record<string, any> = {};
    if (missingStudentIds.size > 0) {
        const students = await prisma.student.findMany({
            where: { id: { in: Array.from(missingStudentIds) } },
            include: { user: true }
        });
        students.forEach(s => { metadataStudentsMap[s.id] = s; });
    }

    const metadataPlansMap: Record<string, string> = {};
    if (missingPlanIds.size > 0) {
        const plans = await prisma.subscriptionPlan.findMany({
            where: { id: { in: Array.from(missingPlanIds) } }
        });
        plans.forEach(p => { metadataPlansMap[p.id] = p.planName; });
    }

    const formatStudentDisplay = (studentName?: string | null, studentEmail?: string | null) => {
        const displayName = (studentName || "").trim();
        const displayEmail = (studentEmail || "").trim();

        if (!displayName) return displayEmail || "N/A";
        return displayEmail ? `${displayName}\n${displayEmail}` : displayName;
    };

    const rows = transactions.map((t, index) => {
        const receipt = t.transactionReceipt;
        const items = receipt?.items || [];
        const studentCount = items.length;
        const metadata = t.metadata as any;

        let userName = "N/A";
        let planName = "N/A";

        if (studentCount > 0) {
            if (studentCount === 1 && items[0]?.student?.user) {
                const u = items[0].student.user;
                userName = formatStudentDisplay(`${u.firstName} ${u.lastName}`.trim(), u.email);
            } else if (studentCount > 1) {
                const entries = items
                    .map((item: any) => ({
                        name: item.student?.user ? `${item.student.user.firstName} ${item.student.user.lastName}`.trim() : null,
                        email: item.student?.user?.email?.trim() || null
                    }))
                    .filter((entry: any) => entry.name || entry.email);
                const names = entries.map((entry: any) => entry.name).filter(Boolean);
                const emails = entries.map((entry: any) => entry.email).filter(Boolean);

                if (names.length > 0 && names.length <= 3) {
                    userName = emails.length > 0 ? `${names.join(", ")}\n${emails.join(", ")}` : names.join(", ");
                } else if (names.length > 3) {
                    userName = `${names.slice(0, 2).join(", ")} +${names.length - 2} more`;
                } else {
                    userName = `${studentCount} Students`;
                }
            }
            planName = items[0]?.subscription?.plan?.planName || "N/A";
        } else if (metadata) {
            if (metadata.quoteData) {
                planName = metadata.quoteData.planName || "N/A";
                const quotes = metadata.quoteData.quotes || [];
                if (quotes.length === 1) {
                    userName = formatStudentDisplay(quotes[0].name, quotes[0].email);
                } else if (quotes.length > 1) {
                    const entries = quotes.map((q: any) => ({
                        name: q.name?.trim() || null,
                        email: q.email?.trim() || null
                    })).filter((entry: any) => entry.name || entry.email);
                    const names = entries.map((entry: any) => entry.name).filter(Boolean);
                    const emails = entries.map((entry: any) => entry.email).filter(Boolean);

                    if (names.length > 0 && names.length <= 3) {
                        userName = emails.length > 0 ? `${names.join(", ")}\n${emails.join(", ")}` : names.join(", ");
                    } else if (names.length > 3) {
                        userName = `${names.slice(0, 2).join(", ")} +${names.length - 2} more`;
                    } else {
                        userName = `${quotes.length} Students`;
                    }
                }
            } else {
                if (metadata.planId) {
                    planName = metadataPlansMap[metadata.planId] || "N/A";
                }
                if (metadata.studentIds) {
                    const sIds = Array.isArray(metadata.studentIds)
                        ? metadata.studentIds
                        : String(metadata.studentIds).split(",").map((id: string) => id.trim()).filter(Boolean);

                    if (sIds.length === 1) {
                        const s = metadataStudentsMap[sIds[0]];
                        if (s?.user) userName = formatStudentDisplay(`${s.user.firstName} ${s.user.lastName}`.trim(), s.user.email);
                    } else if (sIds.length > 1) {
                        const entries = sIds
                            .map((id: string) => {
                                const s = metadataStudentsMap[id];
                                return {
                                    name: s?.user ? `${s.user.firstName} ${s.user.lastName}`.trim() : null,
                                    email: s?.user?.email?.trim() || null
                                };
                            })
                            .filter((entry: any) => entry.name || entry.email);
                        const names = entries.map((entry: any) => entry.name).filter(Boolean);
                        const emails = entries.map((entry: any) => entry.email).filter(Boolean);

                        if (names.length > 0 && names.length <= 3) {
                            userName = emails.length > 0 ? `${names.join(", ")}\n${emails.join(", ")}` : names.join(", ");
                        } else if (names.length > 3) {
                            userName = `${names.slice(0, 2).join(", ")} +${names.length - 2} more`;
                        } else {
                            userName = `${sIds.length} Students`;
                        }
                    }
                }
            }
        }

        if (planName === "N/A" && t.subscriptionRequest?.subscriptionPlan?.planName) {
            planName = t.subscriptionRequest.subscriptionPlan.planName;
        }

        if (userName === "N/A" && t.subscriptionRequest?.student?.user) {
            const u = t.subscriptionRequest.student.user;
            userName = formatStudentDisplay(`${u.firstName} ${u.lastName}`.trim(), u.email);
        }

        const institution = (t as any).institution?.user?.institutionName || "N/A";
        const receiptNo = receipt?.receiptNo || t.orderId || "N/A";
        const method = t.paymentMethod || "N/A";
        const amount = formatMoney(Number(t.totalAmount || 0));
        const taxable = formatMoney(Number(t.taxableAmount || 0));
        let gstInfo = "No GST";
        if (t.gstRate && Number(t.gstRate) > 0) {
            if (t.gstType === "IGST") {
                gstInfo = `IGST ${t.gstRate}%\n${formatMoney(Number(t.igstAmount || t.gstAmount))}`;
            } else {
                gstInfo = `CGST/SGST ${t.gstRate}%\n${formatMoney(Number(t.cgstAmount || 0))} + ${formatMoney(Number(t.sgstAmount || 0))}`;
            }
        }
        const dateStr = dayjs(t.createdAt).format("DD MMM YYYY, hh:mm A");
        const statusStr = String(t.status || "UNKNOWN");

        return [String(index + 1), institution, `${receiptNo}\n${method}`, userName, planName, amount, `Taxable: ${taxable}\n${gstInfo}`, dateStr, statusStr];
    });

    const headers = ["S.No", "Institution", "Receipt/Method", "Student", "Plan", "Amount", "Tax Details", "Date", "Status"];
    const colWidths = [35, 95, 95, 130, 75, 70, 95, 95, 55];

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

    const primary = "#1976d2";
    const white = "#ffffff";
    const altRow = "#f5f7fa";
    const textColor = "#1f2937";
    const headerTextMuted = "#cbd5e1";

    const PW = doc.page.width;
    const PH = doc.page.height;
    const margin = 24;

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

    drawPageBg();
    doc.on("pageAdded", () => { drawPageBg(); });

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

    // Header
    const headerH = 66;
    doc.roundedRect(margin, margin, PW - margin * 2, headerH, 10).fill(primary);

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

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

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

    const tableTop = margin + headerH + 14;
    const tableLeft = margin;
    const tableWidth = PW - margin * 2;
    const baseTotal = colWidths.reduce((a, b) => a + b, 0);
    const scaledWidths = colWidths.map(w => (w / baseTotal) * tableWidth);
    const headerRowH = 26;
    const rowPad = 6;
    const cellFontSize = 8;

    doc.rect(tableLeft, tableTop, tableWidth, headerRowH).fill(primary);
    doc.font("Helvetica-Bold").fontSize(9).fillColor(white);
    let cx = tableLeft;
    headers.forEach((h, i) => {
        doc.text(h, cx + 5, tableTop + (headerRowH - 9) / 2, { width: scaledWidths[i] - 10, align: "left" });
        cx += scaledWidths[i];
    });

    let rowY = tableTop + headerRowH;

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

    rows.forEach((row, idx) => {
        doc.font("Helvetica").fontSize(cellFontSize);

        const rowH = Math.max(28, ...row.map((cell, ci) =>
            doc.heightOfString(String(cell), { width: scaledWidths[ci] - 10 }) + rowPad * 2
        ));

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

        doc.rect(tableLeft, rowY, tableWidth, rowH).fill(idx % 2 === 0 ? altRow : white);
        doc.moveTo(tableLeft, rowY + rowH).lineTo(tableLeft + tableWidth, rowY + rowH)

        let rx = tableLeft;
        row.forEach((cell, ci) => {
            let fillColor = textColor;

            const textHeight = doc.heightOfString(String(cell), { width: scaledWidths[ci] - 10 });
            const verticalOffset = (rowH - textHeight) / 2;

            doc.font("Helvetica").fontSize(cellFontSize).fillColor(fillColor);

            doc.text(String(cell), rx + 5, rowY + verticalOffset, { width: scaledWidths[ci] - 10 });
            rx += scaledWidths[ci];
        });

        rowY += rowH;
    });

    // Outer border
    doc.rect(tableLeft, tableTop, tableWidth, rowY - tableTop)

    doc.end();
});