import prisma from "../config/prisma.js";
import { Prisma } from "../generated/prisma/client.js";
import { ARRIVAL_COMMISSION_PERCENT } from "../config/constants.js";

export class SaleRepository {
  private computeNetGrandTotal(sale: any): Prisma.Decimal {
    const netSubTotal = (sale.items || []).reduce((acc: Prisma.Decimal, item: any) => {
      const returnedAmount = (item.saleReturns || []).reduce(
        (retAcc: Prisma.Decimal, ret: any) => retAcc.add(new Prisma.Decimal(ret.returnAmount)),
        new Prisma.Decimal(0)
      );

      return acc.add(new Prisma.Decimal(item.total).sub(returnedAmount));
    }, new Prisma.Decimal(0));

    const discount = new Prisma.Decimal(sale.discount ?? 0);
    const freightCharge = new Prisma.Decimal(sale.freightCharge ?? 0);
    const netSubTotalAfterDiscount = netSubTotal.sub(discount);
    const effectiveNetSubTotal = netSubTotalAfterDiscount.lt(0)
      ? new Prisma.Decimal(0)
      : netSubTotalAfterDiscount;

    return effectiveNetSubTotal
      .add(freightCharge)
      .toDecimalPlaces(2, Prisma.Decimal.ROUND_HALF_UP);
  }

  async generateInvoiceNo(invoiceNo: string) {
    return prisma.sale.findFirst({ where: { invoiceNo } });
  }

  async create(saleData: any, items: any[]) {
    // Creation is handled via transaction in the service — this is a helper
    return prisma.sale.create({
      data: {
        ...saleData,
        items: { create: items },
      },
      include: {
        items: { include: { purchase: { select: { lotNo: true } } } },
        customer: { select: { name: true } },
      },
    });
  }

  async findAll(
    skip?: number,
    take?: number,
    customerId?: bigint,
    dateFrom?: Date,
    dateTo?: Date,
    search?: string,
    saleType?: string
  ) {
    const where: Prisma.SaleWhereInput = { deletedAt: null };

    if (customerId) {
      where.customerId = customerId;
    } else if (saleType === 'CREDIT') {
      where.customerId = { not: null };
    } else if (saleType === 'QUICK') {
      where.customerId = null;
    }

    if (dateFrom || dateTo) {
      where.invoiceDate = {};
      if (dateFrom) where.invoiceDate.gte = dateFrom;
      if (dateTo) where.invoiceDate.lte = dateTo;
    }

    if (search) {
      where.OR = [
        { invoiceNo: { contains: search } },
        { customer: { name: { contains: search } } },
        { walkInName: { contains: search } },
      ];
    }

    const findOptions: any = {
      where,
      include: {
        customer: { select: { name: true, outstandingBalance: true } },
        items: {
          include: {
            saleReturns: true,
            purchase: {
              select: {
                id: true,
                lotNo: true,
                unit: true,
                weightPerBag: true,
                purchaseType: true,
                vegetable: { select: { name: true } },
                arrivalSettlement: { select: { id: true, status: true, settlementNo: true } },
              },
            },
          },
        },
      },
      orderBy: { id: 'desc' },
    };

    if (skip !== undefined) findOptions.skip = skip;
    if (take !== undefined) findOptions.take = take;

    const [items, total, aggregate] = await Promise.all([
      prisma.sale.findMany(findOptions),
      prisma.sale.count({ where }),
      prisma.sale.aggregate({
        where,
        _sum: {
          subTotal: true,
          grandTotal: true,
          receivedAmount: true,
          balanceAmount: true,
          cashAmount: true,
          upiAmount: true,
          bankAmount: true,
        },
      }),
    ]);

    return {
      items,
      total,
      summary: {
        totalSubTotal: Number(aggregate._sum.subTotal || 0),
        totalGrandTotal: Number(aggregate._sum.grandTotal || 0),
        totalReceivedAmount: Number(aggregate._sum.receivedAmount || 0),
        totalBalanceAmount: Number(aggregate._sum.balanceAmount || 0),
        totalCashAmount: Number(aggregate._sum.cashAmount || 0),
        totalUpiAmount: Number(aggregate._sum.upiAmount || 0),
        totalBankAmount: Number(aggregate._sum.bankAmount || 0),
      },
    };
  }

  async findById(id: bigint) {
    return prisma.sale.findFirst({
      where: { id, deletedAt: null },
      include: {
        customer: { select: { name: true, outstandingBalance: true } },
        items: {
          include: {
            saleReturns: true,
            purchase: {
              select: {
                id: true,
                lotNo: true,
                unit: true,
                rateType: true,
                purchaseType: true,
                weightPerBag: true,
                remainingQuantity: true,
                remainingBags: true,
                costPerUnit: true,
                vegetableId: true,
                vegetable: { select: { id: true, name: true, tamilName: true, hindiName: true, kannadaName: true, malayalamName: true, teluguName: true } },
                supplier: { select: { name: true } },
                arrivalSettlement: { select: { id: true, status: true, settlementNo: true } },
              },
            },
          },
        },
      },
    });
  }

  async findCancelledById(id: bigint) {
    return prisma.sale.findFirst({
      where: { id },
      include: {
        items: true,
      },
    });
  }

  async getAvailableLots(vegetableId?: bigint) {
    const where: Prisma.PurchaseWhereInput = {
      deletedAt: null,
      status: { not: "CLOSED" },
      OR: [
        { remainingQuantity: { gt: 0 } },
        { remainingBags: { gt: 0 } },
        { vegetable: { isFlexible: true } }
      ],
      NOT: {
        AND: [
          { purchaseType: "COMMISSION_ARRIVAL" },
          { arrivalSettlement: { status: "SETTLED" } }
        ]
      }
    };

    if (vegetableId) where.vegetableId = vegetableId;

    const rows = await prisma.purchase.findMany({
      where,
      select: {
        id: true,
        lotNo: true,
        unit: true,
        rateType: true,
        weightPerBag: true,
        remainingQuantity: true,
        remainingBags: true,
        costPerUnit: true,
        arrivalDate: true,
        purchaseDate: true,
        createdAt: true,
        vegetable: { select: { id: true, name: true, isFlexible: true } },
        supplier: { select: { name: true } },
      },
      orderBy: [
        { purchaseDate: "asc" },
        { createdAt: "asc" },
        { id: "asc" }
      ],
    });

    const lotBatchCounter = new Map<string, number>();

    return rows.map((row) => {
      const currentCount = (lotBatchCounter.get(row.lotNo) || 0) + 1;
      lotBatchCounter.set(row.lotNo, currentCount);

      return {
        id: row.id.toString(),
        lotNo: row.lotNo,
        batchIndex: currentCount,
        batchLabel: `${row.lotNo} (Batch #${currentCount})`,
        unit: row.unit,
        weightPerBag: row.weightPerBag,
        costPerUnit: row.costPerUnit,
        remainingQuantity: row.remainingQuantity,
        remainingBags: row.remainingBags,
        arrivalDate: row.arrivalDate,
        purchaseDate: row.purchaseDate,
        vegetable: row.vegetable,
        supplier: row.supplier,
      };
    });
  }

  //Dashboard data gets the todays SALES amount by sum the grand_total (minus sale returns)
  async getTodaySalesAmount() {
    const today = new Date();
    const startOfDay = new Date(Date.UTC(today.getFullYear(), today.getMonth(), today.getDate(), 0, 0, 0, 0));
    const endOfDay = new Date(Date.UTC(today.getFullYear(), today.getMonth(), today.getDate(), 23, 59, 59, 999));

    const sales = await prisma.sale.findMany({
      where: {
        invoiceDate: {
          gte: startOfDay,
          lte: endOfDay,
        },
        deletedAt: null,
      },
      include: {
        items: {
          include: {
            saleReturns: true,
          },
        },
      },
    });

    const totalNetSales = sales.reduce(
      (sum, sale) => sum.add(this.computeNetGrandTotal(sale)),
      new Prisma.Decimal(0)
    );

    return Number(totalNetSales);
  }

  // Dashboard: last 7 days sales amount + profit per day (accounting for sale returns)
  // Arrival (COMMISSION_ARRIVAL) lots: profit = 4% commission only when fully SETTLED; otherwise 0.
  // Direct lots: profit = sale total − cost of goods sold (margin).
  async getSalesProfitChart(): Promise<{ day: string; sales: number; profit: number }[]> {
    const today = new Date();
    const days: { label: string; start: Date; end: Date }[] = [];

    for (let i = 6; i >= 0; i--) {
      const date = new Date(today);
      date.setDate(today.getDate() - i);
      const start = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate(), 0, 0, 0, 0));
      const end = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate(), 23, 59, 59, 999));
      const label = date.toLocaleDateString("en-IN", { weekday: "short" });
      days.push({ label, start, end });
    }

    const rangeStart = days[0]!.start;
    const rangeEnd = days[days.length - 1]!.end;

    const allSales = await prisma.sale.findMany({
      where: {
        invoiceDate: { gte: rangeStart, lte: rangeEnd },
        deletedAt: null,
      },
      include: {
        items: {
          include: {
            saleReturns: true,
            purchase: {
              select: {
                purchaseType: true,
                arrivalSettlement: { select: { status: true } },
                vegetable: { select: { isFlexible: true } },
              },
            },
          },
        },
      },
    });

    return days.map(({ label, start, end }) => {
      const daySales = allSales.filter(
        (s) => s.invoiceDate && s.invoiceDate >= start && s.invoiceDate <= end
      );

      const salesTotal = daySales.reduce(
        (sum, sale) => sum.add(this.computeNetGrandTotal(sale)),
        new Prisma.Decimal(0)
      );

      let dayProfit = 0;
      for (const sale of daySales) {
        let saleItemProfit = 0;
        for (const item of sale.items) {
          const returnedQty = (item.saleReturns || []).reduce(
            (acc: number, ret: any) => acc + Number(ret.quantity),
            0
          );
          const returnedAmount = (item.saleReturns || []).reduce(
            (acc: number, ret: any) => acc + Number(ret.returnAmount),
            0
          );

          const netQty = Math.max(0, Number(item.quantity) - returnedQty);
          const netTotal = Math.max(0, Number(item.total) - returnedAmount);

          const isBagOnly = item.saleUnit !== "KG" && Number(item.quantity || 0) <= 0;
          const netBags = isBagOnly ? Math.max(0, Number(item.bags || 0) - returnedQty) : 0;

          if (netQty > 0 || netBags > 0) {
            const isArrival = (item as any).purchase?.purchaseType === "COMMISSION_ARRIVAL";
            const isFlexible = (item as any).purchase?.vegetable?.isFlexible || item.saleUnit === "PIECES";

            if (isArrival) {
              // Arrival lots: profit = commission only after full settlement
              const isSettled = (item as any).purchase?.arrivalSettlement?.status === "SETTLED";
              saleItemProfit += isSettled ? netTotal * (ARRIVAL_COMMISSION_PERCENT / 100) : 0;
            } else if (isFlexible) {
              // Flexible produce sold in pieces or loose: do not calculate profit
              continue;
            } else {
              // Direct lots: profit = margin (sale − cost)
              const effectiveQty = isBagOnly ? netBags : netQty;
              const costOfGoodsSold = effectiveQty * Number(item.costPerUnit);
              saleItemProfit += netTotal - costOfGoodsSold;
            }
          }
        }
        const saleDiscount = Number(sale.discount || 0);
        dayProfit += saleItemProfit - saleDiscount;
      }

      return {
        day: label,
        sales: Number(salesTotal),
        profit: Math.round(dayProfit * 100) / 100,
      };
    });
  }

  // Dashboard stat-card: today's wholesale profit with arrival-aware logic
  async getTodaysWholesaleProfit(): Promise<number> {
    const today = new Date();
    const startOfDay = new Date(Date.UTC(today.getFullYear(), today.getMonth(), today.getDate(), 0, 0, 0, 0));
    const endOfDay = new Date(Date.UTC(today.getFullYear(), today.getMonth(), today.getDate(), 23, 59, 59, 999));

    const sales = await prisma.sale.findMany({
      where: {
        invoiceDate: { gte: startOfDay, lte: endOfDay },
        deletedAt: null,
      },
      include: {
        items: {
          include: {
            saleReturns: true,
            purchase: {
              select: {
                purchaseType: true,
                arrivalSettlement: { select: { status: true } },
                vegetable: { select: { isFlexible: true } },
              },
            },
          },
        },
      },
    });

    let totalProfit = 0;
    for (const sale of sales) {
      let saleItemProfit = 0;
      for (const item of sale.items) {
        const returnedQty = (item.saleReturns || []).reduce(
          (acc: number, ret: any) => acc + Number(ret.quantity),
          0
        );
        const returnedAmount = (item.saleReturns || []).reduce(
          (acc: number, ret: any) => acc + Number(ret.returnAmount),
          0
        );

        const netQty = Math.max(0, Number(item.quantity) - returnedQty);
        const netTotal = Math.max(0, Number(item.total) - returnedAmount);

        const isBagOnly = item.saleUnit !== "KG" && Number(item.quantity || 0) <= 0;
        const netBags = isBagOnly ? Math.max(0, Number(item.bags || 0) - returnedQty) : 0;

        if (netQty > 0 || netBags > 0) {
          const isArrival = (item as any).purchase?.purchaseType === "COMMISSION_ARRIVAL";
          const isFlexible = (item as any).purchase?.vegetable?.isFlexible || item.saleUnit === "PIECES";

          if (isArrival) {
            const isSettled = (item as any).purchase?.arrivalSettlement?.status === "SETTLED";
            saleItemProfit += isSettled ? netTotal * (ARRIVAL_COMMISSION_PERCENT / 100) : 0;
          } else if (isFlexible) {
            // Flexible produce sold in pieces or loose: do not calculate profit
            continue;
          } else {
            const effectiveQty = isBagOnly ? netBags : netQty;
            const costOfGoodsSold = effectiveQty * Number(item.costPerUnit);
            saleItemProfit += netTotal - costOfGoodsSold;
          }
        }
      }
      const saleDiscount = Number(sale.discount || 0);
      totalProfit += saleItemProfit - saleDiscount;
    }

    return Math.round(totalProfit * 100) / 100;
  }

  // Dashboard: top 5 most demanded vegetables by net kg sold (all time, accounting for sale returns)
  async getTopDemandedVegetables(): Promise<{ vegetable: string; sold: number; bags: number }[]> {
    // Group sale items by purchaseId for active sales
    const itemGroups = await prisma.saleItem.groupBy({
      by: ["purchaseId"],
      _sum: { quantity: true, bags: true },
      where: { sale: { deletedAt: null } },
    });

    if (itemGroups.length === 0) return [];

    // Group sale returns by purchaseId for active sales
    const returnGroups = await prisma.saleReturn.groupBy({
      by: ["purchaseId"],
      _sum: { quantity: true },
      where: { saleItem: { sale: { deletedAt: null } } },
    });

    const returnQtyMap = new Map<string, Prisma.Decimal>(
      returnGroups.map((rg) => [rg.purchaseId.toString(), rg._sum.quantity ?? new Prisma.Decimal(0)])
    );

    // Fetch vegetableId for each purchaseId
    const purchaseIds = itemGroups.map((g) => g.purchaseId);
    const purchases = await prisma.purchase.findMany({
      where: { id: { in: purchaseIds }, deletedAt: null },
      select: { id: true, vegetableId: true },
    });

    const purchaseVegetableMap = new Map(purchases.map((p) => [p.id.toString(), p.vegetableId.toString()]));

    // Aggregate net sold quantity per vegetableId
    const vegetableSoldMap = new Map<string, { qty: number, bags: number }>();
    for (const group of itemGroups) {
      const vegetableId = purchaseVegetableMap.get(group.purchaseId.toString());
      if (!vegetableId) continue;

      const grossQty = new Prisma.Decimal(group._sum.quantity ?? 0);
      const grossBags = new Prisma.Decimal(group._sum.bags ?? 0);
      const returnedQtyOrBags = returnQtyMap.get(group.purchaseId.toString()) ?? new Prisma.Decimal(0);
      
      // Since SaleReturn only has 'quantity', if a bag-only item is returned, its bags are stored in returnQty.
      // We can approximate by checking if grossQty == 0 and grossBags > 0.
      const isBagOnly = Number(grossQty) === 0 && Number(grossBags) > 0;
      
      const netQty = isBagOnly ? 0 : Math.max(0, Number(grossQty.sub(returnedQtyOrBags)));
      const netBags = isBagOnly ? Math.max(0, Number(grossBags.sub(returnedQtyOrBags))) : Number(grossBags);

      const current = vegetableSoldMap.get(vegetableId) ?? { qty: 0, bags: 0 };
      vegetableSoldMap.set(vegetableId, {
        qty: current.qty + netQty,
        bags: current.bags + netBags
      });
    }

    if (vegetableSoldMap.size === 0) return [];

    // Fetch vegetable names
    const vegetableIds = [...vegetableSoldMap.keys()];
    const vegetables = await prisma.vegetable.findMany({
      where: { id: { in: vegetableIds.map(BigInt) } },
      select: { id: true, name: true },
    });

    return vegetables
      .map((v) => {
        const stats = vegetableSoldMap.get(v.id.toString()) ?? { qty: 0, bags: 0 };
        return {
          vegetable: v.name,
          sold: Math.round(stats.qty * 100) / 100,
          bags: Math.round(stats.bags * 100) / 100,
        };
      })
      .filter((v) => v.sold > 0 || v.bags > 0)
      .sort((a, b) => b.sold - a.sold || b.bags - a.bags)
      .slice(0, 5);
  }

  // Dashboard: last 5 recent sales for the activity feed
  async getRecentSales(): Promise<{ invoiceNo: string; customer: string; amount: number; status: string; date: string }[]> {
    const today = new Date();
    const startOfDay = new Date(Date.UTC(today.getFullYear(), today.getMonth(), today.getDate(), 0, 0, 0, 0));

    const sales = await prisma.sale.findMany({
      where: {
        deletedAt: null,
        invoiceDate: {
          gte: startOfDay,
        },
      },
      orderBy: { id: 'desc' },
      include: {
        customer: { select: { name: true } },
        items: {
          include: {
            saleReturns: true,
          },
        },
      },
    });

    return sales.map((s) => {
      let dateStr = "";
      if (s.invoiceDate) {
        const iso = new Date(s.invoiceDate as unknown as string).toISOString();
        dateStr = iso.substring(0, 10);
      }
      return {
        invoiceNo: s.invoiceNo,
        customer: s.customer?.name || (s.walkInName ? `${s.walkInName} (Walk-in)` : "Walk-in Customer"),
        amount: Number(this.computeNetGrandTotal(s)),
        status: String(s.paymentStatus),
        date: dateStr,
        createdAt: s.createdAt,
      };
    });
  }


  //Following for Report module...

  async getReportSalesData(
    skip?: number,
    take?: number,
    customerId?: bigint,
    dateFrom?: Date,
    dateTo?: Date,
    isWalkIn?: boolean
  ) {
    const where: Prisma.SaleWhereInput = { deletedAt: null };

    if (isWalkIn) {
      where.customerId = null;
    } else if (customerId) {
      where.customerId = customerId;
    }

    if (dateFrom || dateTo) {
      where.invoiceDate = {};
      if (dateFrom) where.invoiceDate.gte = dateFrom;
      if (dateTo) where.invoiceDate.lte = dateTo;
    }

    const customerWhere: Prisma.CustomerWhereInput = { deletedAt: null };
    if (customerId) customerWhere.id = customerId;

    const findOptions: any = {
      where,
      include: {
        customer: { select: { name: true, outstandingBalance: true } },
        items: {
          include: {
            saleReturns: true,
          },
        },
      },
      orderBy: { id: 'desc' },
    };

    if (skip !== undefined) findOptions.skip = skip;
    if (take !== undefined) findOptions.take = take;

    const [items, total, grandAndBalanceAgg, saleReturnAgg, outstandingAgg] = await Promise.all([
      prisma.sale.findMany(findOptions),
      prisma.sale.count({ where }),
      prisma.sale.aggregate({ _sum: { grandTotal: true, balanceAmount: true }, where }),
      prisma.saleReturn.aggregate({
        _sum: { returnAmount: true },
        where: { saleItem: { sale: where } },
      }),
      prisma.customer.aggregate({ _sum: { outstandingBalance: true }, where: customerWhere }),
    ]);

    const normalizedItems = items.map((sale) => ({
      ...sale,
      grandTotal: this.computeNetGrandTotal(sale),
    }));

    const grossGrandTotal = grandAndBalanceAgg._sum.grandTotal ?? new Prisma.Decimal(0);
    const totalReturns = saleReturnAgg._sum.returnAmount ?? new Prisma.Decimal(0);
    const grand_total_sum = new Prisma.Decimal(grossGrandTotal).sub(totalReturns);

    const balance_amount_sum = grandAndBalanceAgg._sum.balanceAmount ?? 0;
    const outstanding_balance_sum = outstandingAgg._sum.outstandingBalance ?? 0;

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

  async getReportVegetableWiseSales(
    skip?: number,
    take?: number,
    vegetableId?: bigint,
    dateFrom?: Date,
    dateTo?: Date,
    search?: string
  ) {
    const where: Prisma.SaleItemWhereInput = {
      sale: { deletedAt: null }
    };

    if (dateFrom || dateTo) {
      where.sale = {
        deletedAt: null,
        invoiceDate: {}
      };
      if (dateFrom) (where.sale.invoiceDate as Prisma.DateTimeFilter).gte = dateFrom;
      if (dateTo) (where.sale.invoiceDate as Prisma.DateTimeFilter).lte = dateTo;
    }

    if (vegetableId) {
      where.purchase = { vegetableId };
    }

    if (search) {
      where.purchase = {
        ...((where.purchase as Prisma.PurchaseWhereInput) || {}),
        vegetable: { name: { contains: search } }
      };
    }

    const findOptions: any = {
      where,
      include: {
        saleReturns: true,
        sale: {
          select: {
            invoiceNo: true,
            invoiceDate: true,
            walkInName: true,
            customer: {
              select: { name: true }
            }
          }
        },
        purchase: {
          select: {
            weightPerBag: true,
            vegetable: {
              select: { name: true }
            }
          }
        }
      },
      orderBy: {
        sale: {
          invoiceDate: 'desc'
        }
      }
    };

    if (skip !== undefined) findOptions.skip = skip;
    if (take !== undefined) findOptions.take = take;

    const [items, total] = await Promise.all([
      prisma.saleItem.findMany(findOptions),
      prisma.saleItem.count({ where })
    ]);

    const normalizedItems = items.map((item: any) => {
      const customerName = item.sale?.customer?.name || item.sale?.walkInName || "";
      const returnedQuantity = (item.saleReturns || []).reduce(
        (acc: Prisma.Decimal, ret: any) => acc.add(new Prisma.Decimal(ret.quantity)),
        new Prisma.Decimal(0)
      );
      const returnedAmount = (item.saleReturns || []).reduce(
        (acc: Prisma.Decimal, ret: any) => acc.add(new Prisma.Decimal(ret.returnAmount)),
        new Prisma.Decimal(0)
      );

      let returnedBags = new Prisma.Decimal(0);
      let bags = item.bags;

      const isBagOnly = item.saleUnit !== 'KG' && new Prisma.Decimal(item.quantity).lte(0);

      if (isBagOnly) {
        returnedBags = returnedQuantity;
        if (item.bags !== null && item.bags !== undefined) {
          const netBags = new Prisma.Decimal(item.bags).sub(returnedBags);
          bags = netBags.lt(0) ? new Prisma.Decimal(0) : netBags;
        }
      } else if (item.bags !== null && item.bags !== undefined) {
        const weightPerBag = item.purchase?.weightPerBag
          ? new Prisma.Decimal(item.purchase.weightPerBag)
          : null;
        if (weightPerBag && weightPerBag.gt(0)) {
          returnedBags = returnedQuantity
            .div(weightPerBag)
            .toDecimalPlaces(3, Prisma.Decimal.ROUND_HALF_UP);
        } else if (item.quantity && new Prisma.Decimal(item.quantity).gt(0)) {
          returnedBags = returnedQuantity
            .mul(new Prisma.Decimal(item.bags))
            .div(new Prisma.Decimal(item.quantity))
            .toDecimalPlaces(3, Prisma.Decimal.ROUND_HALF_UP);
        }

        const netBags = new Prisma.Decimal(item.bags).sub(returnedBags);
        bags = netBags.lt(0) ? new Prisma.Decimal(0) : netBags;
      }

      return {
        ...item,
        returnedQuantity: isBagOnly ? new Prisma.Decimal(0) : returnedQuantity,
        returnedAmount,
        returnedBags,
        originalQuantity: item.quantity,
        originalBags: item.bags,
        originalTotal: item.total,
        quantity: isBagOnly ? new Prisma.Decimal(0) : new Prisma.Decimal(item.quantity).sub(returnedQuantity),
        total: new Prisma.Decimal(item.total).sub(returnedAmount),
        bags,
        sale: {
          ...item.sale,
          customer: { name: customerName },
        },
      };
    });

    const [grandTotalAgg, saleReturnAgg] = await Promise.all([
      prisma.saleItem.aggregate({
        _sum: {
          total: true,
          quantity: true,
          bags: true,
        },
        where,
      }),
      prisma.saleReturn.aggregate({
        _sum: {
          returnAmount: true,
          quantity: true,
        },
        where: {
          saleItem: where,
        },
      }),
    ]);

    const grossSales = grandTotalAgg._sum.total ?? new Prisma.Decimal(0);
    const grossQty = grandTotalAgg._sum.quantity ?? new Prisma.Decimal(0);
    const returnAmountSum = saleReturnAgg._sum.returnAmount ?? new Prisma.Decimal(0);
    const returnQtySum = saleReturnAgg._sum.quantity ?? new Prisma.Decimal(0);

    const grand_total_sales = new Prisma.Decimal(grossSales).sub(returnAmountSum);
    let total_quantity_sold = new Prisma.Decimal(grossQty).sub(returnQtySum);

    if (grossQty.equals(0) && grandTotalAgg._sum.bags?.gt(0)) {
      total_quantity_sold = new Prisma.Decimal(grandTotalAgg._sum.bags).sub(returnQtySum);
    }

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

  async getReportLotWiseSales(
    skip?: number,
    take?: number,
    lotNo?: string,
    dateFrom?: Date,
    dateTo?: Date,
    vegetableId?: bigint,
    search?: string
  ) {
    const where: Prisma.SaleItemWhereInput = {
      sale: { deletedAt: null }
    };

    if (dateFrom || dateTo) {
      where.sale = {
        deletedAt: null,
        invoiceDate: {}
      };
      if (dateFrom) (where.sale.invoiceDate as Prisma.DateTimeFilter).gte = dateFrom;
      if (dateTo) (where.sale.invoiceDate as Prisma.DateTimeFilter).lte = dateTo;
    }

    const purchaseWhere: Prisma.PurchaseWhereInput = {};
    if (lotNo && lotNo.trim() !== '') {
      purchaseWhere.lotNo = { contains: lotNo.trim() };
    }
    if (vegetableId) {
      purchaseWhere.vegetableId = vegetableId;
    }
    if (Object.keys(purchaseWhere).length > 0) {
      where.purchase = purchaseWhere;
    }

    if (search && search.trim() !== '') {
      const q = search.trim();
      where.OR = [
        { sale: { invoiceNo: { contains: q } } },
        { sale: { customer: { name: { contains: q } } } },
        { sale: { walkInName: { contains: q } } },
        { purchase: { vegetable: { name: { contains: q } } } },
        { purchase: { lotNo: { contains: q } } }
      ];
    }

    const findOptions: any = {
      where,
      include: {
        saleReturns: true,
        sale: {
          select: {
            id: true,
            invoiceNo: true,
            invoiceDate: true,
            walkInName: true,
            walkInMobile: true,
            paymentStatus: true,
            customer: {
              select: {
                id: true,
                name: true,
                tamilName: true,
                mobileNo: true,
              }
            }
          }
        },
        purchase: {
          select: {
            id: true,
            lotNo: true,
            unit: true,
            rateType: true,
            weightPerBag: true,
            costPerUnit: true,
            purchaseType: true,
            vegetable: {
              select: {
                id: true,
                name: true,
                tamilName: true,
              }
            },
            supplier: {
              select: {
                id: true,
                name: true,
              }
            },
            cashSupplierName: true
          }
        }
      },
      orderBy: [
        { purchase: { lotNo: 'asc' } },
        { sale: { invoiceDate: 'desc' } },
        { id: 'desc' }
      ]
    };

    if (skip !== undefined) findOptions.skip = skip;
    if (take !== undefined) findOptions.take = take;

    const [items, total] = await Promise.all([
      prisma.saleItem.findMany(findOptions),
      prisma.saleItem.count({ where })
    ]);

    const normalizedItems = items.map((item: any) => {
      const customerName = item.sale?.customer?.name || item.sale?.walkInName || "";
      const customerMobile = item.sale?.customer?.mobileNo || item.sale?.walkInMobile || "";
      const returnedQuantity = (item.saleReturns || []).reduce(
        (acc: Prisma.Decimal, ret: any) => acc.add(new Prisma.Decimal(ret.quantity)),
        new Prisma.Decimal(0)
      );
      const returnedAmount = (item.saleReturns || []).reduce(
        (acc: Prisma.Decimal, ret: any) => acc.add(new Prisma.Decimal(ret.returnAmount)),
        new Prisma.Decimal(0)
      );

      let returnedBags = new Prisma.Decimal(0);
      let bags = item.bags;

      const isBagOnly = item.saleUnit !== 'KG' && new Prisma.Decimal(item.quantity).lte(0);

      if (isBagOnly) {
        returnedBags = returnedQuantity;
        if (item.bags !== null && item.bags !== undefined) {
          const netBags = new Prisma.Decimal(item.bags).sub(returnedBags);
          bags = netBags.lt(0) ? new Prisma.Decimal(0) : netBags;
        }
      } else if (item.bags !== null && item.bags !== undefined) {
        const weightPerBag = item.purchase?.weightPerBag
          ? new Prisma.Decimal(item.purchase.weightPerBag)
          : null;
        if (weightPerBag && weightPerBag.gt(0)) {
          returnedBags = returnedQuantity
            .div(weightPerBag)
            .toDecimalPlaces(3, Prisma.Decimal.ROUND_HALF_UP);
        } else if (item.quantity && new Prisma.Decimal(item.quantity).gt(0)) {
          returnedBags = returnedQuantity
            .mul(new Prisma.Decimal(item.bags))
            .div(new Prisma.Decimal(item.quantity))
            .toDecimalPlaces(3, Prisma.Decimal.ROUND_HALF_UP);
        }

        const netBags = new Prisma.Decimal(item.bags).sub(returnedBags);
        bags = netBags.lt(0) ? new Prisma.Decimal(0) : netBags;
      }

      return {
        ...item,
        returnedQuantity: isBagOnly ? new Prisma.Decimal(0) : returnedQuantity,
        returnedAmount,
        returnedBags,
        originalQuantity: item.quantity,
        originalBags: item.bags,
        originalTotal: item.total,
        quantity: isBagOnly ? new Prisma.Decimal(0) : new Prisma.Decimal(item.quantity).sub(returnedQuantity),
        total: new Prisma.Decimal(item.total).sub(returnedAmount),
        bags,
        customerName,
        customerMobile,
        sale: {
          ...item.sale,
          customer: {
            ...item.sale?.customer,
            name: customerName,
            mobileNo: customerMobile,
          },
        },
      };
    });

    const [grandTotalAgg, saleReturnAgg] = await Promise.all([
      prisma.saleItem.aggregate({
        _sum: {
          total: true,
          quantity: true,
          bags: true,
        },
        where,
      }),
      prisma.saleReturn.aggregate({
        _sum: {
          returnAmount: true,
          quantity: true,
        },
        where: {
          saleItem: where,
        },
      }),
    ]);

    const grossSales = grandTotalAgg._sum.total ?? new Prisma.Decimal(0);
    const grossQty = grandTotalAgg._sum.quantity ?? new Prisma.Decimal(0);
    const returnAmountSum = saleReturnAgg._sum.returnAmount ?? new Prisma.Decimal(0);
    const returnQtySum = saleReturnAgg._sum.quantity ?? new Prisma.Decimal(0);

    const grand_total_sales = new Prisma.Decimal(grossSales).sub(returnAmountSum);
    let total_quantity_sold = new Prisma.Decimal(grossQty).sub(returnQtySum);
    const total_bags_sold = grandTotalAgg._sum.bags ?? new Prisma.Decimal(0);

    if (grossQty.equals(0) && grandTotalAgg._sum.bags?.gt(0)) {
      total_quantity_sold = new Prisma.Decimal(grandTotalAgg._sum.bags).sub(returnQtySum);
    }

    return {
      items: normalizedItems,
      total,
      grand_total_sales,
      total_quantity_sold,
      total_bags_sold,
    };
  }

  async getDistinctLotsForSales(dateFrom?: Date, dateTo?: Date) {
    const where: Prisma.SaleItemWhereInput = {
      sale: { deletedAt: null }
    };
    if (dateFrom || dateTo) {
      where.sale = {
        deletedAt: null,
        invoiceDate: {}
      };
      if (dateFrom) (where.sale.invoiceDate as Prisma.DateTimeFilter).gte = dateFrom;
      if (dateTo) (where.sale.invoiceDate as Prisma.DateTimeFilter).lte = dateTo;
    }

    const items = await prisma.saleItem.findMany({
      where,
      select: {
        purchase: {
          select: {
            lotNo: true,
            vegetable: { select: { id: true, name: true } }
          }
        }
      },
      distinct: ['purchaseId']
    });

    const lotMap = new Map<string, { lotNo: string; vegetableName?: string }>();
    for (const item of items) {
      if (item.purchase?.lotNo) {
        lotMap.set(item.purchase.lotNo, {
          lotNo: item.purchase.lotNo,
          vegetableName: item.purchase.vegetable?.name
        });
      }
    }

    return Array.from(lotMap.values());
  }
}