import prisma from "../config/prisma.js";
import { ARRIVAL_COMMISSION_PERCENT } from "../config/constants.js";
import { PurchaseRepository } from "../repositories/PurchaseRepository.js";
import { SaleRepository } from "../repositories/SaleRepository.js";
import { CustomerRepository } from "../repositories/CustomerRepository.js";
import { SupplierRepository } from "../repositories/SupplierRepository.js";
import { ArrivalRepository } from "../repositories/ArrivalRepository.js";
import { ArrivalSettlementRepository } from "../repositories/ArrivalSettlementRepository.js";

export class ReportService {

    private purchaseRepository = new PurchaseRepository();
    private saleRepository = new SaleRepository();
    private customerRepository = new CustomerRepository();
    private supplierRepository = new SupplierRepository();
    private arrivalRepository = new ArrivalRepository();
    private arrivalSettlementRepository = new ArrivalSettlementRepository();


    //Purchase Report Related functions
    async getPurchaseData(
        page: number = 1,
        limit: number = 10,
        search?: string,
        supplierId?: bigint,
        vegetableId?: bigint,
        dateFromStr?: string,
        dateToStr?: string
    ) {
        const skip = (page - 1) * limit;

        let dateFrom: Date | undefined;
        let dateTo: Date | undefined;

        if (dateFromStr) dateFrom = new Date(dateFromStr);
        if (dateToStr) dateTo = new Date(dateToStr);

        const { items, total, grand_total_sum, balance_amount_sum, outstanding_balance_sum } = await this.purchaseRepository.getAllPurchases(
            skip,
            limit,
            search,
            supplierId,
            vegetableId,
            dateFrom,
            dateTo
        );

        return {
            data: items,
            meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
            sum: {
                "grand_total": grand_total_sum,
                "balance_amount": balance_amount_sum,
                "outstanding_balance": outstanding_balance_sum
            }
        };
    }

    async getExportData(
        search?: string,
        supplierId?: bigint,
        vegetableId?: bigint,
        dateFromStr?: string,
        dateToStr?: string
    ) {
        let dateFrom: Date | undefined;
        let dateTo: Date | undefined;

        if (dateFromStr) dateFrom = new Date(dateFromStr);
        if (dateToStr) dateTo = new Date(dateToStr);

        // No skip/take — fetch every matching record
        const { items, total, grand_total_sum, balance_amount_sum, outstanding_balance_sum } =
            await this.purchaseRepository.getAllPurchases(
                undefined,
                undefined,
                search,
                supplierId,
                vegetableId,
                dateFrom,
                dateTo
            );

        return { data: items, total, grand_total_sum, balance_amount_sum, outstanding_balance_sum };
    }

    async getSalesData(
        page: number = 1,
        limit: number = 10,
        customerId?: bigint,
        dateFromStr?: string,
        dateToStr?: string,
        isWalkIn?: boolean
    ) {
        const skip = (page - 1) * limit;

        let dateFrom: Date | undefined;
        let dateTo: Date | undefined;

        if (dateFromStr) dateFrom = new Date(dateFromStr);
        if (dateToStr) dateTo = new Date(dateToStr);

        const { items, total, grand_total_sum, balance_amount_sum, outstanding_balance_sum } = await this.saleRepository.getReportSalesData(
            skip,
            limit,
            customerId,
            dateFrom,
            dateTo,
            isWalkIn
        );

        return {
            data: items,
            meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
            sum: {
                "grand_total": grand_total_sum,
                "balance_amount": balance_amount_sum,
                "outstanding_balance": outstanding_balance_sum,
            }
        };
    }

    async getSalesExportData(
        customerId?: bigint,
        dateFromStr?: string,
        dateToStr?: string,
        isWalkIn?: boolean
    ) {
        let dateFrom: Date | undefined;
        let dateTo: Date | undefined;

        if (dateFromStr) dateFrom = new Date(dateFromStr);
        if (dateToStr) dateTo = new Date(dateToStr);

        // No skip/take — fetch every matching record
        const { items, total, grand_total_sum, balance_amount_sum, outstanding_balance_sum } =
            await this.saleRepository.getReportSalesData(
                undefined,
                undefined,
                customerId,
                dateFrom,
                dateTo,
                isWalkIn
            );

        return { data: items, total, grand_total_sum, balance_amount_sum, outstanding_balance_sum };
    }

    async getVegetableWiseSalesData(
        page: number = 1,
        limit: number = 10,
        vegetableId?: bigint,
        dateFromStr?: string,
        dateToStr?: string,
        search?: string
    ) {
        const skip = (page - 1) * limit;

        let dateFrom: Date | undefined;
        let dateTo: Date | undefined;

        if (dateFromStr) dateFrom = new Date(dateFromStr);
        if (dateToStr) dateTo = new Date(dateToStr);

        const { items, total, grand_total_sales, total_quantity_sold } = await this.saleRepository.getReportVegetableWiseSales(
            skip,
            limit,
            vegetableId,
            dateFrom,
            dateTo,
            search
        );

        return {
            data: items,
            meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
            sum: {
                "grand_total": grand_total_sales,
                "total_quantity": total_quantity_sold
            }
        };
    }

    async getVegetableWiseSalesExportData(
        vegetableId?: bigint,
        dateFromStr?: string,
        dateToStr?: string,
        search?: string
    ) {
        let dateFrom: Date | undefined;
        let dateTo: Date | undefined;

        if (dateFromStr) dateFrom = new Date(dateFromStr);
        if (dateToStr) dateTo = new Date(dateToStr);

        const { items, total, grand_total_sales, total_quantity_sold } = await this.saleRepository.getReportVegetableWiseSales(
            undefined,
            undefined,
            vegetableId,
            dateFrom,
            dateTo,
            search
        );

        return { data: items, total, grand_total_sales, total_quantity_sold };
    }

    async getLotWiseSalesData(
        page: number = 1,
        limit: number = 10,
        lotNo?: string,
        dateStr?: string,
        vegetableId?: bigint,
        search?: string
    ) {
        const skip = (page - 1) * limit;

        const targetDateStr = dateStr || new Date().toISOString().slice(0, 10);
        const dateParts = targetDateStr.split('-');
        const year = parseInt(dateParts[0]!, 10);
        const month = parseInt(dateParts[1]!, 10) - 1;
        const day = parseInt(dateParts[2]!, 10);

        const dateFrom = new Date(Date.UTC(year, month, day, 0, 0, 0, 0));
        const dateTo = new Date(Date.UTC(year, month, day, 23, 59, 59, 999));

        const { items, total, grand_total_sales, total_quantity_sold, total_bags_sold } =
            await this.saleRepository.getReportLotWiseSales(
                skip,
                limit,
                lotNo,
                dateFrom,
                dateTo,
                vegetableId,
                search
            );

        return {
            data: items,
            meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
            sum: {
                grand_total: grand_total_sales,
                total_quantity: total_quantity_sold,
                total_bags: total_bags_sold,
                total_records: total,
            },
            selectedDate: targetDateStr,
        };
    }

    async getLotWiseSalesExportData(
        lotNo?: string,
        dateStr?: string,
        vegetableId?: bigint,
        search?: string
    ) {
        const targetDateStr = dateStr || new Date().toISOString().slice(0, 10);
        const dateParts = targetDateStr.split('-');
        const year = parseInt(dateParts[0]!, 10);
        const month = parseInt(dateParts[1]!, 10) - 1;
        const day = parseInt(dateParts[2]!, 10);

        const dateFrom = new Date(Date.UTC(year, month, day, 0, 0, 0, 0));
        const dateTo = new Date(Date.UTC(year, month, day, 23, 59, 59, 999));

        const { items, total, grand_total_sales, total_quantity_sold, total_bags_sold } =
            await this.saleRepository.getReportLotWiseSales(
                undefined,
                undefined,
                lotNo,
                dateFrom,
                dateTo,
                vegetableId,
                search
            );

        return { data: items, total, grand_total_sales, total_quantity_sold, total_bags_sold, selectedDate: targetDateStr };
    }

    async getLotWiseMeta(dateStr?: string) {
        const targetDateStr = dateStr || new Date().toISOString().slice(0, 10);
        const dateParts = targetDateStr.split('-');
        const year = parseInt(dateParts[0]!, 10);
        const month = parseInt(dateParts[1]!, 10) - 1;
        const day = parseInt(dateParts[2]!, 10);

        const dateFrom = new Date(Date.UTC(year, month, day, 0, 0, 0, 0));
        const dateTo = new Date(Date.UTC(year, month, day, 23, 59, 59, 999));

        const [lots, vegetables] = await Promise.all([
            this.saleRepository.getDistinctLotsForSales(dateFrom, dateTo),
            prisma.vegetable.findMany({
                where: { status: true },
                select: { id: true, name: true, tamilName: true },
                orderBy: { name: 'asc' },
            })
        ]);

        return {
            lots: lots.map(l => ({ lotNo: l.lotNo, vegetableName: l.vegetableName })),
            vegetables: vegetables.map(v => ({ id: v.id.toString(), name: v.name, tamilName: v.tamilName })),
            selectedDate: targetDateStr,
        };
    }


    async getCustomerLedgerData(
        customerIdStr?: string,
        dateFromStr?: string,
        dateToStr?: string
    ) {
        const customerId = customerIdStr ? BigInt(customerIdStr) : undefined;
        const dateFrom = dateFromStr ? new Date(dateFromStr) : undefined;
        const dateTo = dateToStr ? new Date(dateToStr) : undefined;

        const [openingBalance, entries, selectedCustomer] = await Promise.all([
            this.customerRepository.getCustomerOpeningBalance(customerId, dateFrom),
            this.customerRepository.getCustomerLedgerTransactions(customerId, dateFrom, dateTo),
            customerId ? this.customerRepository.findById(customerId) : Promise.resolve(null),
        ]);

        const openingCustomerName = selectedCustomer?.name ?? '';

        // Add Opening Balance row at top
        let runningBalance = openingBalance;
        const rows: any[] = [];

        if (openingBalance !== 0 || dateFrom) {
            rows.push({
                date: dateFrom ?? null,
                voucherNo: 'Opening',
                customerName: openingCustomerName,
                description: 'Opening Balance',
                transactionType: 'Opening Balance',
                debit: openingBalance > 0 ? openingBalance : 0,
                credit: openingBalance < 0 ? Math.abs(openingBalance) : 0,
                runningBalance,
            });
        }

        let totalDebit = openingBalance > 0 ? openingBalance : 0;
        let totalCredit = openingBalance < 0 ? Math.abs(openingBalance) : 0;

        for (const entry of entries) {
            runningBalance += entry.debit - entry.credit;
            totalDebit += entry.debit;
            totalCredit += entry.credit;
            rows.push({
                ...entry,
                date: entry.date.toISOString().slice(0, 10),
                runningBalance,
            });
        }

        const closingBalance = runningBalance;

        return {
            data: rows,
            summary: {
                openingBalance,
                totalDebit,
                totalCredit,
                closingBalance,
            },
        };
    }

    async getCustomerLedgerExportData(
        customerIdStr?: string,
        dateFromStr?: string,
        dateToStr?: string
    ) {
        return this.getCustomerLedgerData(customerIdStr, dateFromStr, dateToStr);
    }

    async getCustomerListForLedger() {
        return this.customerRepository.findAllActiveCustomers();
    }

    async getSupplierLedgerData(
        supplierIdStr?: string,
        dateFromStr?: string,
        dateToStr?: string
    ) {
        const supplierId = supplierIdStr ? BigInt(supplierIdStr) : undefined;
        const dateFrom = dateFromStr ? new Date(dateFromStr) : undefined;
        const dateTo = dateToStr ? new Date(dateToStr) : undefined;

        const [openingBalance, entries, selectedSupplier] = await Promise.all([
            this.supplierRepository.getSupplierOpeningBalance(supplierId, dateFrom),
            this.supplierRepository.getSupplierLedgerTransactions(supplierId, dateFrom, dateTo),
            supplierId ? this.supplierRepository.findById(supplierId) : Promise.resolve(null),
        ]);

        const openingSupplierName = selectedSupplier?.name ?? '';

        // Convention: runningBalance < 0 = we owe supplier (Payable)
        //             runningBalance > 0 = supplier owes us (Advance/excess paid)
        // openingBalance from repository is already in this convention (negated from DB field).
        // Purchases (credit) decrease runningBalance (increase payable).
        // Payments/Returns (debit) increase runningBalance (decrease payable).
        let runningBalance = openingBalance;
        const rows: any[] = [];

        if (openingBalance !== 0 || dateFrom) {
            // If runningBalance is negative (payable), show in credit column.
            // If positive (advance), show in debit column.
            rows.push({
                date: dateFrom ?? null,
                voucherNo: 'Opening',
                supplierName: openingSupplierName,
                description: 'Opening Balance',
                transactionType: 'Opening Balance',
                debit: openingBalance > 0 ? openingBalance : 0,
                credit: openingBalance < 0 ? Math.abs(openingBalance) : 0,
                runningBalance,
            });
        }

        let totalDebit = openingBalance > 0 ? openingBalance : 0;
        let totalCredit = openingBalance < 0 ? Math.abs(openingBalance) : 0;
        let totalPurchases = 0;
        let totalPayments = 0;
        let totalReturns = 0;

        for (const entry of entries) {
            // debit = payments/returns (reduce payable) → runningBalance increases
            // credit = purchases (increase payable) → runningBalance decreases
            runningBalance += entry.debit - entry.credit;
            totalDebit += entry.debit;
            totalCredit += entry.credit;
            if (entry.transactionType === 'Purchase') totalPurchases += entry.credit;
            if (entry.transactionType === 'Payment') totalPayments += entry.debit;
            if (entry.transactionType === 'Purchase Return') totalReturns += entry.debit;
            rows.push({
                ...entry,
                date: entry.date.toISOString().slice(0, 10),
                runningBalance,
            });
        }

        const closingBalance = runningBalance;

        return {
            data: rows,
            summary: {
                openingBalance,
                totalDebit,
                totalCredit,
                totalPurchases,
                totalPayments,
                totalReturns,
                closingBalance,
            },
        };
    }

    async getSupplierLedgerExportData(
        supplierIdStr?: string,
        dateFromStr?: string,
        dateToStr?: string
    ) {
        return this.getSupplierLedgerData(supplierIdStr, dateFromStr, dateToStr);
    }

    async getSupplierListForLedger() {
        return this.supplierRepository.findAllActiveSuppliers();
    }

    async getStockReportData(
        page: number = 1,
        limit: number = 10,
        search?: string,
        supplierId?: bigint,
        vegetableId?: bigint,
        dateFromStr?: string,
        dateToStr?: string
    ) {
        const skip = (page - 1) * limit;
        let dateFrom: Date | undefined;
        let dateTo: Date | undefined;
        if (dateFromStr) dateFrom = new Date(dateFromStr);
        if (dateToStr) dateTo = new Date(dateToStr);

        const { items: purchases, total } = await this.purchaseRepository.findAll(
            skip, limit, search, dateFrom, dateTo, supplierId, vegetableId
        );

        const purchaseIds = purchases.map((p: any) => p.id);

        const [wholesaleSpoilages, retailAllocations, retailSpoilages, retailSaleItems, purchaseReturns] = await Promise.all([
            prisma.stockSpoilage.groupBy({
                by: ['purchaseId'],
                where: { purchaseId: { in: purchaseIds } },
                _sum: { quantity: true },
            }),
            prisma.retailStock.groupBy({
                by: ['purchaseId'],
                where: { purchaseId: { in: purchaseIds }, deletedAt: null },
                _sum: { allocatedQty: true, quantity: true, remainingQuantity: true, bags: true, remainingBags: true },
            }),
            prisma.retailStockSpoilage.findMany({
                where: { retailStock: { purchaseId: { in: purchaseIds } } },
                select: { quantity: true, retailStock: { select: { purchaseId: true } } }
            }),
            // Actual retail sale quantities (only from non-cancelled sales)
            prisma.retailSaleItem.findMany({
                where: {
                    retailStock: { purchaseId: { in: purchaseIds } },
                    retailSale: { deletedAt: null },
                },
                select: { quantity: true, retailStock: { select: { purchaseId: true } } }
            }),
            prisma.purchaseReturn.groupBy({
                by: ['purchaseId'],
                where: { purchaseId: { in: purchaseIds } },
                _sum: { quantity: true, bags: true },
            })
        ]);

        const wholesaleSpoilageMap = new Map(wholesaleSpoilages.map((s: any) => [s.purchaseId.toString(), Number(s._sum.quantity || 0)]));
        const retailAllocatedMap = new Map(retailAllocations.map((r: any) => [
            r.purchaseId.toString(), 
            { qty: Number(r._sum.quantity || 0), bags: Number(r._sum.bags || 0) }
        ]));
        const purchaseReturnsMap = new Map(purchaseReturns.map((r: any) => [
            r.purchaseId.toString(),
            { qty: Number(r._sum.quantity || 0), bags: Number(r._sum.bags || 0) }
        ]));
        const retailRemainingMap = new Map(retailAllocations.map((r: any) => [
            r.purchaseId.toString(), 
            { qty: Number(r._sum.remainingQuantity || 0), bags: Number(r._sum.remainingBags || 0) }
        ]));

        const retailSpoilageMap = new Map<string, number>();
        retailSpoilages.forEach((s: any) => {
            const pId = s.retailStock.purchaseId.toString();
            retailSpoilageMap.set(pId, (retailSpoilageMap.get(pId) || 0) + Number(s.quantity || 0));
        });

        const retailSoldMap = new Map<string, number>();
        retailSaleItems.forEach((item: any) => {
            const pId = item.retailStock.purchaseId.toString();
            retailSoldMap.set(pId, (retailSoldMap.get(pId) || 0) + Number(item.quantity || 0));
        });

        const formattedData = purchases.map((p: any) => {
            const pId = p.id.toString();
            const isBagOnly = p.unit !== 'KG' && Number(p.quantity || 0) === 0;
            const purchasedKg = isBagOnly ? Number(p.bags || 0) : Number(p.quantity || 0);
            const wholesaleSpoilage = Number(wholesaleSpoilageMap.get(pId) || 0);
            const retailAllocatedStats = retailAllocatedMap.get(pId) || { qty: 0, bags: 0 };
            const retailAllocated = isBagOnly ? retailAllocatedStats.bags : retailAllocatedStats.qty;
            const purchaseReturnStats = purchaseReturnsMap.get(pId) || { qty: 0, bags: 0 };
            const purchaseReturned = isBagOnly ? purchaseReturnStats.bags : purchaseReturnStats.qty;
            const wholesaleRemaining = isBagOnly ? Number(p.remainingBags || 0) : Number(p.remainingQuantity || 0);
            const wholesaleSales = Math.max(0, purchasedKg - wholesaleSpoilage - retailAllocated - purchaseReturned - wholesaleRemaining);
            const retailSpoilage = Number(retailSpoilageMap.get(pId) || 0);
            
            const retailRemainingStats = retailRemainingMap.get(pId) || { qty: 0, bags: 0 };
            const retailRemaining = isBagOnly ? retailRemainingStats.bags : retailRemainingStats.qty;
            
            const retailSold = Number(retailSoldMap.get(pId) || 0);
            // retailReturned = what was allocated but not sold, spoiled, or remaining
            const retailReturned = Math.max(0, retailAllocated - retailSold - retailSpoilage - retailRemaining);

            return {
                ...p,
                id: pId,
                wholesaleSalesQty: wholesaleSales,
                wholesaleSpoilageQty: wholesaleSpoilage,
                retailAllocatedQty: retailAllocated,
                retailSalesQty: retailSold,
                retailSpoilageQty: retailSpoilage,
                retailReturnedQty: retailReturned,
                retailRemainingQty: retailRemaining,
            };
        });

        const { data: allData } = await this.getStockReportExportData(search, supplierId, vegetableId, dateFromStr, dateToStr);
        const sum = allData.reduce((acc: any, curr: any) => {
            const isBagOnly = curr.unit !== 'KG' && Number(curr.quantity || 0) === 0;
            const pQty = isBagOnly ? Number(curr.bags || 0) : Number(curr.quantity || 0);
            const wRem = isBagOnly ? Number(curr.remainingBags || 0) : Number(curr.remainingQuantity || 0);

            acc.purchasedQty += pQty;
            acc.wholesaleSalesQty += curr.wholesaleSalesQty;
            acc.wholesaleSpoilageQty += curr.wholesaleSpoilageQty;
            acc.retailAllocatedQty += curr.retailAllocatedQty;
            acc.retailSalesQty += curr.retailSalesQty;
            acc.retailSpoilageQty += curr.retailSpoilageQty;
            acc.retailReturnedQty += curr.retailReturnedQty;
            acc.wholesaleRemainingQty += wRem;
            acc.retailRemainingQty += curr.retailRemainingQty;
            return acc;
        }, {
            purchasedQty: 0,
            wholesaleSalesQty: 0,
            wholesaleSpoilageQty: 0,
            retailAllocatedQty: 0,
            retailSalesQty: 0,
            retailSpoilageQty: 0,
            retailReturnedQty: 0,
            wholesaleRemainingQty: 0,
            retailRemainingQty: 0
        });

        return {
            data: formattedData,
            meta: {
                total,
                page,
                limit,
                totalPages: Math.ceil(total / limit),
            },
            sum
        };
    }

    async getStockReportExportData(
        search?: string,
        supplierId?: bigint,
        vegetableId?: bigint,
        dateFromStr?: string,
        dateToStr?: string
    ) {
        const dateFrom = dateFromStr ? new Date(dateFromStr) : undefined;
        const dateTo = dateToStr ? new Date(dateToStr) : undefined;

        const { items: purchases, total } = await this.purchaseRepository.findAll(
            undefined, undefined, search, dateFrom, dateTo, supplierId, vegetableId
        );

        const purchaseIds = purchases.map((p: any) => p.id);

        const [wholesaleSpoilages, retailAllocations, retailSpoilages, retailSaleItems, purchaseReturns] = await Promise.all([
            prisma.stockSpoilage.groupBy({
                by: ['purchaseId'],
                where: { purchaseId: { in: purchaseIds } },
                _sum: { quantity: true },
            }),
            prisma.retailStock.groupBy({
                by: ['purchaseId'],
                where: { purchaseId: { in: purchaseIds }, deletedAt: null },
                _sum: { allocatedQty: true, quantity: true, remainingQuantity: true, bags: true, remainingBags: true },
            }),
            prisma.retailStockSpoilage.findMany({
                where: { retailStock: { purchaseId: { in: purchaseIds } } },
                select: { quantity: true, retailStock: { select: { purchaseId: true } } }
            }),
            // Actual retail sale quantities (only from non-cancelled sales)
            prisma.retailSaleItem.findMany({
                where: {
                    retailStock: { purchaseId: { in: purchaseIds } },
                    retailSale: { deletedAt: null },
                },
                select: { quantity: true, retailStock: { select: { purchaseId: true } } }
            }),
            prisma.purchaseReturn.groupBy({
                by: ['purchaseId'],
                where: { purchaseId: { in: purchaseIds } },
                _sum: { quantity: true, bags: true },
            })
        ]);

        const wholesaleSpoilageMap = new Map(wholesaleSpoilages.map((s: any) => [s.purchaseId.toString(), Number(s._sum.quantity || 0)]));
        const retailAllocatedMap = new Map(retailAllocations.map((r: any) => [
            r.purchaseId.toString(), 
            { qty: Number(r._sum.quantity || 0), bags: Number(r._sum.bags || 0) }
        ]));
        const purchaseReturnsMap = new Map(purchaseReturns.map((r: any) => [
            r.purchaseId.toString(),
            { qty: Number(r._sum.quantity || 0), bags: Number(r._sum.bags || 0) }
        ]));
        const retailRemainingMap = new Map(retailAllocations.map((r: any) => [
            r.purchaseId.toString(), 
            { qty: Number(r._sum.remainingQuantity || 0), bags: Number(r._sum.remainingBags || 0) }
        ]));

        const retailSpoilageMap = new Map<string, number>();
        retailSpoilages.forEach((s: any) => {
            const pId = s.retailStock.purchaseId.toString();
            retailSpoilageMap.set(pId, (retailSpoilageMap.get(pId) || 0) + Number(s.quantity || 0));
        });

        const retailSoldMap = new Map<string, number>();
        retailSaleItems.forEach((item: any) => {
            const pId = item.retailStock.purchaseId.toString();
            retailSoldMap.set(pId, (retailSoldMap.get(pId) || 0) + Number(item.quantity || 0));
        });

        const data = purchases.map((p: any) => {
            const pId = p.id.toString();
            const isBagOnly = p.unit !== 'KG' && Number(p.quantity || 0) === 0;
            const purchasedKg = isBagOnly ? Number(p.bags || 0) : Number(p.quantity || 0);
            const wholesaleSpoilage = Number(wholesaleSpoilageMap.get(pId) || 0);
            const retailAllocated = Number(retailAllocatedMap.get(pId) || 0);
            const purchaseReturnStats = purchaseReturnsMap.get(pId) || { qty: 0, bags: 0 };
            const purchaseReturned = isBagOnly ? purchaseReturnStats.bags : purchaseReturnStats.qty;
            const wholesaleRemaining = isBagOnly ? Number(p.remainingBags || 0) : Number(p.remainingQuantity || 0);
            const wholesaleSales = Math.max(0, purchasedKg - wholesaleSpoilage - retailAllocated - purchaseReturned - wholesaleRemaining);
            const retailSpoilage = Number(retailSpoilageMap.get(pId) || 0);
            const retailRemaining = Number(retailRemainingMap.get(pId) || 0);
            const retailSold = Number(retailSoldMap.get(pId) || 0);
            const retailReturned = Math.max(0, retailAllocated - retailSold - retailSpoilage - retailRemaining);

            return {
                ...p,
                id: pId,
                wholesaleSalesQty: wholesaleSales,
                wholesaleSpoilageQty: wholesaleSpoilage,
                retailAllocatedQty: retailAllocated,
                retailSalesQty: retailSold,
                retailSpoilageQty: retailSpoilage,
                retailReturnedQty: retailReturned,
                retailRemainingQty: retailRemaining,
            };
        });

        return { data, total };
    }

    async getWholesaleDailySummaryData(dateStr?: string) {
        const formattedDateStr = dateStr || new Date().toISOString().slice(0, 10);
        const dateParts = formattedDateStr.split('-');
        const year = parseInt(dateParts[0]!, 10);
        const month = parseInt(dateParts[1]!, 10) - 1;
        const day = parseInt(dateParts[2]!, 10);

        const startOfDay = new Date(Date.UTC(year, month, day, 0, 0, 0, 0));
        const endOfDay = new Date(Date.UTC(year, month, day, 23, 59, 59, 999));

        const [sales, purchases, expenses, customerReceipts, supplierPayments, wholesaleSpoilages, purchaseReturns, customerOutstanding, supplierOutstanding, arrivalSettlements] = await Promise.all([
            prisma.sale.findMany({
                where: {
                    invoiceDate: { gte: startOfDay, lte: endOfDay },
                    deletedAt: null,
                },
                include: {
                    customer: { select: { id: true, name: true } },
                    items: {
                        include: {
                            saleReturns: true,
                            purchase: {
                                select: {
                                    id: true,
                                    lotNo: true,
                                    unit: true,
                                    rateType: true,
                                    weightPerBag: true,
                                    costPerUnit: true,
                                    cashSupplierName: true,
                                    purchaseType: true,
                                    arrivalSettlement: { select: { status: true } },
                                    vegetable: { select: { id: true, name: true, tamilName: true, isFlexible: true } },
                                    supplier: { select: { id: true, name: true } }
                                }
                            }
                        }
                    }
                },
                orderBy: { createdAt: 'asc' }
            }),
            prisma.purchase.findMany({
                where: {
                    purchaseDate: { gte: startOfDay, lte: endOfDay },
                    deletedAt: null,
                },
                include: {
                    vegetable: { select: { name: true } },
                    supplier: { select: { name: true } },
                    retailStocks: { select: { quantity: true, allocatedQty: true } }
                }
            }),
            prisma.expense.findMany({
                where: {
                    expenseDate: { gte: startOfDay, lte: endOfDay }
                }
            }),
            prisma.customerReceipt.findMany({
                where: {
                    receiptDate: { gte: startOfDay, lte: endOfDay },
                    deletedAt: null
                },
                include: { customer: { select: { name: true } } }
            }),
            prisma.supplierPayment.findMany({
                where: {
                    paymentDate: { gte: startOfDay, lte: endOfDay },
                    deletedAt: null
                },
                include: { supplier: { select: { name: true } } }
            }),
            prisma.stockSpoilage.findMany({
                where: {
                    spoilageDate: { gte: startOfDay, lte: endOfDay }
                },
                include: { purchase: { select: { lotNo: true, vegetable: { select: { name: true } } } } }
            }),
            prisma.purchaseReturn.findMany({
                where: {
                    returnDate: { gte: startOfDay, lte: endOfDay }
                },
                include: {
                    purchase: {
                        select: {
                            lotNo: true,
                            cashSupplierName: true,
                            vegetable: { select: { name: true } },
                            supplier: { select: { name: true } }
                        }
                    }
                }
            }),
            this.customerRepository.getTotalOutstandingBalance(),
            this.supplierRepository.getTotalOutstandingBalance(),
            prisma.arrivalSettlement.findMany({
                where: {
                    settlementDate: { gte: startOfDay, lte: endOfDay }
                }
            })
        ]);

        const wholesaleItemized: any[] = [];
        let totalWholesaleRevenue = 0;
        let totalWholesaleCost = 0;

        for (const sale of sales) {
            const custName = sale.customer?.name || sale.walkInName || "Walk-In";
            for (const item of sale.items) {
                const isBagOrBox = item.saleUnit === 'BAG' || item.saleUnit === 'BOX';
                
                let returnedQty = 0;
                let returnedBags = 0;
                let returnedAmt = 0;

                for (const r of (item.saleReturns || [])) {
                    if (isBagOrBox) {
                        returnedBags += Number(r.quantity || 0);
                    } else {
                        returnedQty += Number(r.quantity || 0);
                    }
                    returnedAmt += Number(r.returnAmount || 0);
                }

                const qty = Math.max(0, Number(item.quantity || 0) - returnedQty);
                const bags = Math.max(0, Number(item.bags || 0) - returnedBags);

                if (qty <= 0 && bags <= 0) continue;

                const totalAmt = Number(item.total || 0) - returnedAmt;
                const sellingRate = Number(item.sellingRate || 0);

                const isArrival = item.purchase?.purchaseType === "COMMISSION_ARRIVAL";
                const isSettled = item.purchase?.arrivalSettlement?.status === "SETTLED";
                const isFlexible = !!item.purchase?.vegetable?.isFlexible || item.saleUnit === "PIECES" || (item.saleUnit === "THAAR" && !!item.purchase?.vegetable?.isFlexible);
                const itemCostPerUnit = Number(item.costPerUnit || item.purchase?.costPerUnit || 0);
                const weightPerBag = Number(item.purchase?.weightPerBag || 0);
                const purchaseRateType = item.purchase?.rateType;

                let displayCostPerUnit = itemCostPerUnit;
                let costTotal = 0;
                let profit = 0;

                if (isArrival) {
                    profit = isSettled ? totalAmt * (ARRIVAL_COMMISSION_PERCENT / 100) : 0;
                    costTotal = totalAmt - profit;
                    displayCostPerUnit = 0;
                } else if (isFlexible) {
                    // Flexible produce (Coconut, Banana, Cabbage) sold in pieces/thaar:
                    // Profit is NOT calculated per item because cost per piece is undefined.
                    costTotal = totalAmt;
                    profit = 0;
                    displayCostPerUnit = 0;
                } else {
                    if (isBagOrBox) {
                        if (purchaseRateType === "KG" && weightPerBag > 0) {
                            displayCostPerUnit = itemCostPerUnit * weightPerBag;
                            costTotal = qty > 0 ? qty * itemCostPerUnit : bags * displayCostPerUnit;
                        } else {
                            displayCostPerUnit = itemCostPerUnit;
                            costTotal = (bags > 0 ? bags : qty) * displayCostPerUnit;
                        }
                    } else {
                        displayCostPerUnit = itemCostPerUnit;
                        costTotal = qty * itemCostPerUnit;
                    }
                    profit = totalAmt - costTotal;
                }

                totalWholesaleRevenue += totalAmt;
                totalWholesaleCost += costTotal;

                wholesaleItemized.push({
                    invoiceNo: sale.invoiceNo,
                    date: sale.invoiceDate.toISOString().slice(0, 10),
                    customerName: custName,
                    purchaseId: item.purchase?.id?.toString() || null,
                    lotNo: item.purchase?.lotNo || "-",
                    vegetableName: item.purchase?.vegetable?.name || "-",
                    supplierName: item.purchase?.supplier?.name || item.purchase?.cashSupplierName || "-",
                    purchaseType: item.purchase?.purchaseType || "DIRECT",
                    isArrival,
                    isSettled,
                    isFlexible,
                    unit: item.saleUnit,
                    quantity: isBagOrBox ? (bags > 0 ? bags : qty) : qty,
                    bags: bags,
                    actualKg: qty,
                    costPerUnit: (isArrival || isFlexible) ? null : displayCostPerUnit,
                    sellingRate,
                    totalAmt,
                    costTotal: (!isArrival && isFlexible) ? null : costTotal,
                    profit: (!isArrival && isFlexible) ? null : profit
                });
            }
        }

        let formerExpense = 0;
        let supplierExpense = 0;
        let rentExpense = 0;
        let cooliExpense = 0;
        let otherExpense = 0;

        for (const exp of expenses) {
            const amt = Number(exp.amount || 0);
            const cat = (exp.category || "").toLowerCase();
            if (cat.includes("former") || cat.includes("farmer")) formerExpense += amt;
            else if (cat.includes("supplier")) supplierExpense += amt;
            else if (cat.includes("rent")) rentExpense += amt;
            else if (cat.includes("cooli") || cat.includes("coolie")) cooliExpense += amt;
            else otherExpense += amt;
        }

        const totalExpenses = formerExpense + supplierExpense + rentExpense + cooliExpense + otherExpense;
        const salesCollection = customerReceipts.reduce((sum: number, r: any) => sum + Number(r.amount || 0), 0);
        const supplierCollection = supplierPayments.reduce((sum: number, r: any) => sum + Number(r.amount || 0), 0);

        // Purchase Returns for the day
        const purchaseReturnItems = purchaseReturns.map((pr: any) => ({
            returnDate: pr.returnDate.toISOString().slice(0, 10),
            lotNo: pr.purchase?.lotNo || "-",
            vegetableName: pr.purchase?.vegetable?.name || "-",
            supplierName: pr.purchase?.supplier?.name || pr.purchase?.cashSupplierName || "-",
            quantity: Number(pr.quantity || 0),
            returnAmount: Number(pr.returnAmount || 0),
            reason: pr.reason || null,
            remarks: pr.remarks || null,
        }));
        const totalPurchaseReturnsAmount = purchaseReturnItems.reduce((sum: number, pr: any) => sum + pr.returnAmount, 0);

        const totalArrivalCommissions = arrivalSettlements.reduce((sum: number, s: any) => sum + Number(s.commissionAmount || 0), 0);

        const grossAmount = totalWholesaleRevenue - totalWholesaleCost;
        const netProfit = grossAmount;

        const closingOutstanding = Number(customerOutstanding || 0);
        const openingOutstanding = closingOutstanding - (totalWholesaleRevenue - salesCollection);

        const purchasesTotal = purchases.reduce((sum: number, p: any) => sum + Number(p.grandTotal || 0), 0);

        const financialSummary = {
            date: formattedDateStr,
            sales: totalWholesaleRevenue,
            cogs: totalWholesaleCost,
            arrivalCommissions: totalArrivalCommissions,
            grossAmount,
            expenses: totalExpenses,
            profit: netProfit,
            salesCollection,
            supplierCollection,
            purchasesTotal,
            purchaseReturnsTotal: totalPurchaseReturnsAmount,
            customerOutstanding: Number(customerOutstanding || 0),
            supplierOutstanding: Number(supplierOutstanding || 0),
            opening: {
                sales: totalWholesaleRevenue,
                cogs: totalWholesaleCost,
                arrivalCommissions: totalArrivalCommissions,
                grossAmount,
                expenses: totalExpenses,
                profit: netProfit,
                outstanding: Number(customerOutstanding || 0),
            },
            closing: {
                salesCollection,
                supplierCollection,
                purchase: purchasesTotal,
                purchaseReturns: totalPurchaseReturnsAmount,
                outstanding: Number(supplierOutstanding || 0),
            }
        };

        const stockMovements = purchases.map((p: any) => {
            const isLotBagOrBox = p.unit === 'BAG' || p.unit === 'BOX';
            const isLotThaar = p.unit === 'THAAR';
            const isLotPieces = p.unit === 'PIECES';

            const purchasedQty = (isLotBagOrBox || isLotThaar) && p.bags != null
                ? Number(p.bags || 0)
                : Number(p.quantity || 0);

            const remainingQty = (isLotBagOrBox || isLotThaar) && p.remainingBags != null
                ? Number(p.remainingBags || 0)
                : Number(p.remainingQuantity || 0);

            // Filter sales for this purchase
            const salesForThisPurchase = wholesaleItemized.filter(
                (item: any) => item.purchaseId === p.id.toString()
            );

            let wholesaleSoldQty = 0;
            let otherUnitSoldQty = 0;
            let otherUnitName = "";

            for (const item of salesForThisPurchase) {
                if (p.unit === 'BAG' || p.unit === 'BOX') {
                    if (item.unit === 'BAG' || item.unit === 'BOX') {
                        wholesaleSoldQty += Number(item.bags || item.quantity || 0);
                    } else if (item.unit === 'KG') {
                        const wpb = Number(p.weightPerBag || 0);
                        if (Number(item.bags || 0) > 0) {
                            wholesaleSoldQty += Number(item.bags);
                        } else if (wpb > 0) {
                            wholesaleSoldQty += Number(item.actualKg || item.quantity || 0) / wpb;
                        }
                    } else {
                        // Sold in PIECES from BAG lot (flexible)
                        otherUnitSoldQty += Number(item.quantity || 0);
                        otherUnitName = item.unit;
                    }
                } else if (p.unit === 'THAAR') {
                    if (item.unit === 'THAAR') {
                        wholesaleSoldQty += Number(item.quantity || item.bags || 0);
                    } else {
                        // Sold in PIECES from THAAR lot
                        otherUnitSoldQty += Number(item.quantity || 0);
                        otherUnitName = item.unit;
                    }
                } else if (p.unit === 'PIECES') {
                    if (item.unit === 'PIECES') {
                        wholesaleSoldQty += Number(item.quantity || 0);
                    } else {
                        otherUnitSoldQty += Number(item.quantity || 0);
                        otherUnitName = item.unit;
                    }
                } else {
                    // p.unit === 'KG'
                    if (item.unit === 'KG') {
                        wholesaleSoldQty += Number(item.actualKg || item.quantity || 0);
                    } else {
                        // Sold in PIECES from KG lot (e.g. Coconut)
                        otherUnitSoldQty += Number(item.quantity || 0);
                        otherUnitName = item.unit;
                    }
                }
            }

            let secondarySoldText: string | null = null;
            if (otherUnitSoldQty > 0) {
                secondarySoldText = `${otherUnitSoldQty.toLocaleString('en-IN', { maximumFractionDigits: 3 })} ${otherUnitName}`;
            }

            const retailMovedQty = (p.retailStocks || []).reduce((acc: number, r: any) => {
                const movedAmt = (isLotBagOrBox || isLotThaar) && r.bags != null
                    ? Number(r.bags || 0)
                    : Number(r.quantity || r.allocatedQty || 0);
                return acc + movedAmt;
            }, 0);

            return {
                lotNo: p.lotNo,
                vegetableName: p.vegetable?.name || "-",
                supplierName: p.supplier?.name || p.cashSupplierName || "-",
                purchasedQty,
                wholesaleSoldQty,
                retailMovedQty,
                soldQty: wholesaleSoldQty,
                remainingQty,
                unit: p.unit,
                secondarySoldText
            };
        });

        return {
            financialSummary,
            wholesaleItemized,
            purchaseReturnItems,
            stockMovements,
            summary: {
                totalWholesaleRevenue,
                totalWholesaleCost,
                grossAmount,
                totalExpenses,
                totalPurchaseReturnsAmount,
                netProfit
            }
        };
    }

    async getRetailDailySummaryData(dateStr?: string) {
        const formattedDateStr = dateStr || new Date().toISOString().slice(0, 10);
        const dateParts = formattedDateStr.split('-');
        const year = parseInt(dateParts[0]!, 10);
        const month = parseInt(dateParts[1]!, 10) - 1;
        const day = parseInt(dateParts[2]!, 10);

        const startOfDay = new Date(Date.UTC(year, month, day, 0, 0, 0, 0));
        const endOfDay = new Date(Date.UTC(year, month, day, 23, 59, 59, 999));

        const [retailSales, retailAllocations, retailSpoilages, expenses] = await Promise.all([
            prisma.retailSale.findMany({
                where: {
                    invoiceDate: { gte: startOfDay, lte: endOfDay },
                    deletedAt: null,
                },
                include: {
                    items: {
                        include: {
                            retailStock: {
                                select: {
                                    lotNo: true,
                                    allocatedUnit: true,
                                    allocatedQty: true,
                                    quantity: true,
                                    bags: true,
                                    remainingQuantity: true,
                                    remainingBags: true,
                                    costPerUnit: true,
                                    purchase: {
                                        select: {
                                            lotNo: true,
                                            purchaseType: true,
                                            weightPerBag: true,
                                            arrivalSettlement: { select: { status: true } },
                                            vegetable: { select: { name: true, isFlexible: true } }
                                        }
                                    }
                                }
                            }
                        }
                    }
                },
                orderBy: { createdAt: 'asc' }
            }),
            prisma.retailStock.findMany({
                where: {
                    allocationDate: { gte: startOfDay, lte: endOfDay },
                    deletedAt: null,
                },
                include: {
                    purchase: {
                        select: {
                            lotNo: true,
                            unit: true,
                            weightPerBag: true,
                            vegetable: { select: { name: true } }
                        }
                    }
                }
            }),
            prisma.retailStockSpoilage.findMany({
                where: {
                    spoilageDate: { gte: startOfDay, lte: endOfDay }
                },
                include: {
                    retailStock: {
                        select: {
                            lotNo: true,
                            costPerUnit: true,
                            purchase: { select: { vegetable: { select: { name: true } } } }
                        }
                    }
                }
            }),
            prisma.expense.findMany({
                where: {
                    expenseDate: { gte: startOfDay, lte: endOfDay }
                }
            })
        ]);

        const retailItemized: any[] = [];
        let totalRetailRevenue = 0;
        let totalRetailCost = 0;
        let totalRetailDiscount = 0;

        for (const sale of retailSales) {
            totalRetailDiscount += Number(sale.discount || 0);
            const walkIn = sale.walkInName || "Retail Customer";
            for (const item of sale.items) {
                const qtyKg = Number(item.quantity || 0);
                const isBoxOrBag = item.saleUnit !== "KG";
                const weightPerBag = Number(item.retailStock?.purchase?.weightPerBag || 0);
                const soldQty = isBoxOrBag && item.bags !== null && item.bags !== undefined
                    ? Number(item.bags)
                    : qtyKg;

                if (soldQty <= 0 && qtyKg <= 0) continue;

                const totalAmt = Number(item.total || 0);
                const sellingRate = Number(item.sellingRate || 0);

                const isArrival = item.retailStock?.purchase?.purchaseType === "COMMISSION_ARRIVAL";
                const isSettled = item.retailStock?.purchase?.arrivalSettlement?.status === "SETTLED";
                const isFlexible = !!item.retailStock?.purchase?.vegetable?.isFlexible || item.saleUnit === "PIECES";
                let costPerKg = Number(item.costPerUnit || item.retailStock?.costPerUnit || 0);
                let costTotal = 0;
                let profit = 0;

                if (isArrival) {
                    // Arrival lots: 4% commission only when fully settled; otherwise profit is 0 until settlement
                    profit = isSettled ? totalAmt * (ARRIVAL_COMMISSION_PERCENT / 100) : 0;
                    costTotal = totalAmt - profit;
                    costPerKg = qtyKg > 0 ? costTotal / qtyKg : 0;
                } else if (isFlexible) {
                    // Flexible produce sold in pieces or loose: do not calculate profit
                    costTotal = totalAmt;
                    profit = 0;
                    costPerKg = 0;
                } else {
                    // Direct lots: profit = sale total - cost total
                    costTotal = qtyKg * costPerKg;
                    profit = totalAmt - costTotal;
                }

                // Calculate display cost per unit (per BOX/BAG or per KG)
                let displayCostPerUnit = costPerKg;
                if (isBoxOrBag) {
                    if (weightPerBag > 0) {
                        displayCostPerUnit = costPerKg * weightPerBag;
                    } else if (soldQty > 0) {
                        displayCostPerUnit = costTotal / soldQty;
                    }
                }

                totalRetailRevenue += totalAmt;
                totalRetailCost += costTotal;

                retailItemized.push({
                    invoiceNo: sale.invoiceNo,
                    date: sale.invoiceDate.toISOString().slice(0, 10),
                    walkInName: walkIn,
                    lotNo: item.retailStock?.lotNo || item.retailStock?.purchase?.lotNo || "-",
                    vegetableName: item.retailStock?.purchase?.vegetable?.name || "-",
                    purchaseType: item.retailStock?.purchase?.purchaseType || "DIRECT",
                    isArrival,
                    isSettled,
                    isFlexible,
                    unit: item.saleUnit,
                    quantity: soldQty,
                    quantityKg: qtyKg,
                    bags: item.bags !== null && item.bags !== undefined ? Number(item.bags) : null,
                    costPerUnit: (isArrival || isFlexible) ? null : displayCostPerUnit,
                    costPerKg: (isArrival || isFlexible) ? null : costPerKg,
                    sellingRate,
                    totalAmt,
                    costTotal: (!isArrival && isFlexible) ? null : costTotal,
                    profit: (!isArrival && isFlexible) ? null : profit
                });
            }
        }

        const netRetailRevenue = totalRetailRevenue - totalRetailDiscount;
        const totalExpenses = expenses.reduce((sum: number, e: any) => sum + Number(e.amount || 0), 0);
        const grossAmount = netRetailRevenue - totalRetailCost;
        const netProfit = grossAmount;

        const totalAllocatedQty = retailAllocations.reduce((sum: number, r: any) => sum + Number(r.quantity || 0), 0);
        const totalSpoilageQty = retailSpoilages.reduce((sum: number, s: any) => sum + Number(s.quantity || 0), 0);

        const financialSummary = {
            date: formattedDateStr,
            opening: {
                allocatedQty: totalAllocatedQty,
                allocatedValue: retailAllocations.reduce((sum: number, r: any) => sum + (Number(r.quantity || 0) * Number(r.costPerUnit || 0)), 0),
                sales: netRetailRevenue,
                cogs: totalRetailCost,
                grossAmount,
                expenses: totalExpenses,
                profit: netProfit
            },
            closing: {
                totalRetailSales: netRetailRevenue,
                totalRetailCost,
                spoilageQty: totalSpoilageQty,
                spoilageValue: retailSpoilages.reduce((sum: number, s: any) => sum + (Number(s.quantity || 0) * Number(s.retailStock?.costPerUnit || 0)), 0),
                remainingRetailStockValue: retailAllocations.reduce((sum: number, r: any) => sum + (Number(r.remainingQuantity || 0) * Number(r.costPerUnit || 0)), 0)
            }
        };

        const stockMovements = retailAllocations.map((r: any) => {
            const isBoxOrBag = r.allocatedUnit !== "KG";
            const allocatedKg = Number(r.quantity || 0);
            const remainingKg = Number(r.remainingQuantity || 0);
            const soldKg = Math.max(0, allocatedKg - remainingKg);

            // For BAG/BOX lots: bags track purchase deduction only, retail sales are always in KG
            // So show KG as the primary display unit for Sold and Remaining, with bag count as context
            let allocQty: number;
            let remainingQty: number;
            let soldQty: number;
            let displayUnit: string;

            if (isBoxOrBag) {
                // Primary display in KG (since retail sales are in KG)
                allocQty = allocatedKg;
                remainingQty = remainingKg;
                soldQty = soldKg;
                displayUnit = "KG";
            } else {
                allocQty = Number(r.allocatedQty ?? r.quantity ?? 0);
                remainingQty = remainingKg;
                soldQty = soldKg;
                displayUnit = "KG";
            }

            return {
                lotNo: r.lotNo,
                vegetableName: r.purchase?.vegetable?.name || "-",
                unit: displayUnit,
                // Allocated: show bags count (what was moved from purchase) with KG sub-line
                allocatedQty: isBoxOrBag ? Number(r.allocatedQty ?? r.bags ?? 0) : allocQty,
                allocatedUnit: r.allocatedUnit,   // original BAG/BOX/KG for allocated display
                allocatedKg: isBoxOrBag ? allocatedKg : null,
                // Sold: always KG (retail sells in KG)
                soldQty: soldKg,
                soldKg: null,   // no sub-line needed since primary is already KG
                // Remaining: always KG
                remainingQty: remainingKg,
                remainingKg: null,  // no sub-line needed since primary is already KG
            };
        });

        return {
            financialSummary,
            retailItemized,
            stockMovements,
            summary: {
                totalRetailRevenue,
                totalRetailCost,
                grossAmount,
                totalExpenses,
                netProfit
            }
        };
    }

    // ── Arrivals Report ──────────────────────────────────────────────

    async getArrivalsReportData(
        page: number = 1,
        limit: number = 10,
        search?: string,
        supplierId?: bigint,
        vegetableId?: bigint,
        dateFromStr?: string,
        dateToStr?: string,
        settlementStatus?: "UNSETTLED" | "PARTIALLY_SETTLED" | "SETTLED" | "ALL"
    ) {
        const skip = (page - 1) * limit;
        const dateFrom = dateFromStr ? new Date(dateFromStr) : undefined;
        const dateTo = dateToStr ? new Date(dateToStr) : undefined;

        const { items, total } = await this.arrivalRepository.findAll(
            skip, limit, search, dateFrom, dateTo, supplierId, vegetableId, settlementStatus
        );

        return {
            data: items,
            meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
        };
    }

    async getArrivalsReportExportData(
        search?: string,
        supplierId?: bigint,
        vegetableId?: bigint,
        dateFromStr?: string,
        dateToStr?: string,
        settlementStatus?: "UNSETTLED" | "PARTIALLY_SETTLED" | "SETTLED" | "ALL"
    ) {
        const dateFrom = dateFromStr ? new Date(dateFromStr) : undefined;
        const dateTo = dateToStr ? new Date(dateToStr) : undefined;

        // No skip/take — fetch all matching records
        const { items, total } = await this.arrivalRepository.findAll(
            undefined, undefined, search, dateFrom, dateTo, supplierId, vegetableId, settlementStatus
        );

        return { data: items, total };
    }

    // ── Arrival Settlements Report ───────────────────────────────────

    async getArrivalSettlementsReportData(
        page: number = 1,
        limit: number = 10,
        search?: string,
        dateFromStr?: string,
        dateToStr?: string,
        status?: "UNSETTLED" | "PARTIALLY_SETTLED" | "SETTLED"
    ) {
        const skip = (page - 1) * limit;
        const dateFrom = dateFromStr ? new Date(dateFromStr) : undefined;
        const dateTo = dateToStr ? new Date(dateToStr) : undefined;

        const { items, total } = await this.arrivalSettlementRepository.findAll(
            skip, limit, search, dateFrom, dateTo, status
        );

        return {
            data: items,
            meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
        };
    }

    async getArrivalSettlementsReportExportData(
        search?: string,
        dateFromStr?: string,
        dateToStr?: string,
        status?: "UNSETTLED" | "PARTIALLY_SETTLED" | "SETTLED"
    ) {
        const dateFrom = dateFromStr ? new Date(dateFromStr) : undefined;
        const dateTo = dateToStr ? new Date(dateToStr) : undefined;

        // No skip/take — fetch all matching records
        const { items, total } = await this.arrivalSettlementRepository.findAll(
            undefined, undefined, search, dateFrom, dateTo, status
        );

        return { data: items, total };
    }

    // ── Walk-In Ledger ───────────────────────────────────────────────

    async getWalkInLedgerData(
        dateFromStr?: string,
        dateToStr?: string
    ) {
        const dateFrom = dateFromStr ? new Date(dateFromStr) : undefined;
        const dateTo = dateToStr ? new Date(dateToStr) : undefined;

        const entries = await this.customerRepository.getWalkInLedgerTransactions(dateFrom, dateTo);

        return {
            entries
        };
    }

    // ── Patti Settlement Ledger ───────────────────────────────────────

    async getPattiLedgerData(
        dateFromStr?: string,
        dateToStr?: string,
        supplierIdStr?: string,
        status?: "UNSETTLED" | "PARTIALLY_SETTLED" | "SETTLED",
        search?: string
    ) {
        const dateFrom = dateFromStr ? new Date(dateFromStr) : undefined;
        const dateTo = dateToStr ? new Date(dateToStr) : undefined;
        const supplierId = supplierIdStr ? BigInt(supplierIdStr) : undefined;

        const settlements = await this.arrivalSettlementRepository.getPattiLedgerTransactions({
            dateFrom,
            dateTo,
            supplierId,
            status,
            search,
        });

        let totalGrossSales = 0;
        let totalCommission = 0;
        let totalDeductions = 0;
        let totalAdvanceAdjusted = 0;
        let totalNetPayable = 0;
        let totalPaid = 0;
        let totalBalance = 0;

        const entries = settlements.map((s) => {
            const gross = Number(s.grossSalesTotal ?? 0);
            const comm = Number(s.commissionAmount ?? 0);
            const ded = Number(s.totalDeductions ?? 0);
            const adv = Number(s.advanceAdjusted ?? 0);
            const net = Number(s.netPayable ?? 0);
            const paid = Number(s.paidAmount ?? 0);
            const bal = Number(s.balanceAmount ?? 0);

            totalGrossSales += gross;
            totalCommission += comm;
            totalDeductions += ded;
            totalAdvanceAdjusted += adv;
            totalNetPayable += net;
            totalPaid += paid;
            totalBalance += bal;

            const supplierName = s.purchase?.supplier?.name || s.purchase?.cashSupplierName || '—';
            const vegetableName = s.purchase?.vegetable?.name || '—';

            return {
                id: s.id.toString(),
                purchaseId: s.purchaseId.toString(),
                settlementNo: s.settlementNo,
                settlementDate: s.settlementDate.toISOString().slice(0, 10),
                lotNo: s.purchase?.lotNo || '—',
                supplierId: s.purchase?.supplierId ? s.purchase.supplierId.toString() : null,
                supplierName,
                vegetableName,
                totalWeightSold: Number(s.totalWeightSold ?? 0),
                grossSalesTotal: gross,
                commissionPercent: Number(s.commissionPercent ?? 0),
                commissionAmount: comm,
                freightDeducted: Number(s.freightDeducted ?? 0),
                extraFreightDeducted: Number(s.extraFreightDeducted ?? 0),
                associationDeducted: Number(s.associationDeducted ?? 0),
                unloadingDeducted: Number(s.unloadingDeducted ?? 0),
                miscDeducted: Number(s.miscDeducted ?? 0),
                totalDeductions: ded,
                advanceAdjusted: adv,
                netPayable: net,
                paidAmount: paid,
                balanceAmount: bal,
                status: s.status,
                paymentMode: s.paymentMode,
                remarks: s.remarks,
            };
        });

        return {
            entries,
            summary: {
                totalGrossSales,
                totalCommission,
                totalDeductions,
                totalAdvanceAdjusted,
                totalNetPayable,
                totalPaid,
                totalBalance,
            },
        };
    }
}