import prisma from "../config/prisma.js";
import { Prisma, PurchaseUnit } from "../generated/prisma/client.js";

export class RetailStockService {
  async allocateStock(data: {
    purchaseId: bigint;
    allocatedUnit: PurchaseUnit;
    allocatedQty: Prisma.Decimal | number | string;
    allocatedKg?: Prisma.Decimal | number | string;
    allocationDate: Date;
    remarks?: string | undefined;
    createdBy: bigint;
  }) {
    return prisma.$transaction(async (tx) => {
      // 1. Fetch the Purchase row
      const purchase = await tx.purchase.findUnique({
        where: { id: data.purchaseId },
      });

      if (!purchase) {
        throw new Error("Purchase not found");
      }

      // 2. VALIDATE: Ensure lot has available stock to allocate
      const isCountBasedPurchase = purchase.unit === "THAAR" || purchase.unit === "PIECES" || purchase.unit === "BAG" || purchase.unit === "BOX";
      if (purchase.remainingQuantity.lte(0) && (!isCountBasedPurchase || purchase.remainingBags.lte(0))) {
        throw new Error("No available stock to allocate for this purchase lot.");
      }

      // 3. VALIDATE: allocatedQty must not exceed available remaining stock
      const allocatedQtyDecimal = new Prisma.Decimal(data.allocatedQty);
      
      let remainingAvailable: Prisma.Decimal;
      if (data.allocatedUnit === "KG" || purchase.unit === "KG") {
        // If they allocate KG, or if the wholesale lot was purely KG (repackaging into bags/boxes)
        // the available constraint is based purely on KG
        remainingAvailable = purchase.remainingQuantity;
        if (data.allocatedUnit !== "KG" && !data.allocatedKg) {
           throw new Error("Must provide total KG when repackaging KG lot into boxes/bags.");
        }
        if (data.allocatedUnit !== "KG" && data.allocatedKg) {
            const kgToAllocate = new Prisma.Decimal(data.allocatedKg);
            if (kgToAllocate.greaterThan(remainingAvailable)) {
               throw new Error(`Allocated KG (${kgToAllocate}) exceeds available stock (${remainingAvailable} KG)`);
            }
        }
      } else {
        remainingAvailable = purchase.remainingBags;
        if (allocatedQtyDecimal.greaterThan(remainingAvailable)) {
          throw new Error(
            `Allocated quantity (${allocatedQtyDecimal}) exceeds available remaining stock (${remainingAvailable} ${data.allocatedUnit})`
          );
        }
      }

      if (data.allocatedUnit === "KG" && allocatedQtyDecimal.greaterThan(remainingAvailable)) {
        throw new Error(
          `Allocated quantity (${allocatedQtyDecimal}) exceeds available remaining stock (${remainingAvailable} KG)`
        );
      }

      // 3. Calculate converted KG and Bags
      let kgQty: Prisma.Decimal;
      let bagQty: Prisma.Decimal | null = null;
      let costPerUnitKg: Prisma.Decimal;

      if (data.allocatedUnit === "KG") {
        kgQty = allocatedQtyDecimal;
        costPerUnitKg = purchase.costPerUnit;
        if (purchase.weightPerBag && purchase.weightPerBag.gt(0)) {
          if (kgQty.eq(purchase.remainingQuantity)) {
            bagQty = purchase.remainingBags;
          } else {
            bagQty = kgQty.div(purchase.weightPerBag).toDecimalPlaces(3, Prisma.Decimal.ROUND_HALF_UP);
          }
        } else if (purchase.remainingQuantity.gt(0) && purchase.remainingBags && purchase.remainingBags.gt(0)) {
          if (kgQty.eq(purchase.remainingQuantity)) {
            bagQty = purchase.remainingBags;
          } else {
            const effectiveWpb = purchase.remainingQuantity.div(purchase.remainingBags);
            bagQty = kgQty.div(effectiveWpb).toDecimalPlaces(3, Prisma.Decimal.ROUND_HALF_UP);
          }
        }
      } else {
        bagQty = allocatedQtyDecimal;
        
        if (data.allocatedKg) {
          kgQty = new Prisma.Decimal(data.allocatedKg);
        } else {
          if (purchase.weightPerBag && purchase.weightPerBag.gt(0)) {
            if (bagQty.eq(purchase.remainingBags)) {
              kgQty = purchase.remainingQuantity;
            } else {
              kgQty = bagQty.mul(purchase.weightPerBag).toDecimalPlaces(3, Prisma.Decimal.ROUND_HALF_UP);
            }
          } else if (purchase.remainingBags && purchase.remainingBags.gt(0) && purchase.remainingQuantity.gt(0)) {
            if (bagQty.eq(purchase.remainingBags)) {
              kgQty = purchase.remainingQuantity;
            } else {
              const effectiveWpb = purchase.remainingQuantity.div(purchase.remainingBags);
              kgQty = bagQty.mul(effectiveWpb).toDecimalPlaces(3, Prisma.Decimal.ROUND_HALF_UP);
            }
          } else if (purchase.unit === "THAAR" || purchase.unit === "PIECES" || purchase.remainingQuantity.isZero()) {
            kgQty = new Prisma.Decimal(0);
          } else {
            throw new Error("Weight per bag is missing for this purchase and no KG quantity was provided");
          }
        }
        
        if (purchase.rateType === "UNIT" && kgQty.gt(0)) {
          costPerUnitKg = bagQty.mul(purchase.costPerUnit).div(kgQty).toDecimalPlaces(2, Prisma.Decimal.ROUND_HALF_UP);
        } else {
          costPerUnitKg = purchase.costPerUnit;
        }
      }

      // --- Strong validation: Both stocks must reach 0 together ---
      if (data.allocatedUnit !== "KG" && data.allocatedKg && purchase.remainingBags.gt(0) && purchase.remainingQuantity.gt(0)) {
        const remainingKg = purchase.remainingQuantity.sub(kgQty);
        const remainingBags = purchase.remainingBags.sub(bagQty!);

        if (remainingKg.lte(0) && remainingBags.gt(0)) {
          throw new Error("Invalid stock combination: KG is completely allocated, but units remain. Both must reach zero together.");
        }
        if (remainingBags.lte(0) && remainingKg.gt(0)) {
          throw new Error("Invalid stock combination: Units are completely allocated, but KG remains. Both must reach zero together.");
        }
      }

      // 4. Decrement both remainingQuantity and remainingBags on Purchase
      const decrementBags = bagQty !== null && purchase.unit !== "KG";

      const result = await tx.purchase.updateMany({
        where: { 
          id: purchase.id,
          ...(kgQty.gt(0) ? { remainingQuantity: { gte: kgQty } } : {}),
          ...(decrementBags ? { remainingBags: { gte: bagQty! } } : {})
        },
        data: {
          remainingQuantity: { decrement: kgQty },
          ...(decrementBags ? { remainingBags: { decrement: bagQty! } } : {}),
        },
      });

      if (result.count === 0) {
        throw new Error('Stock changed concurrently or insufficient stock for retail allocation. Please retry.');
      }

      const updatedPurchase = await tx.purchase.findUnique({
        where: { id: purchase.id },
        select: {
          remainingQuantity: true,
          remainingBags: true,
          status: true,
          vegetable: { select: { isFlexible: true } },
        },
      });
      if (updatedPurchase && updatedPurchase.status !== "CLOSED" && !updatedPurchase.vegetable?.isFlexible) {
        const isDepleted = updatedPurchase.remainingQuantity.lte(0) && (updatedPurchase.remainingBags.lte(0) || purchase.unit === "KG");
        const newStatus = isDepleted ? "DEPLETED" : "ACTIVE";
        if (updatedPurchase.status !== newStatus) {
          await tx.purchase.update({
            where: { id: purchase.id },
            data: { status: newStatus },
          });
        }
      }

      // 6. Use the same lotNo as the original purchase
      const lotNo = purchase.lotNo;

      // 7. Create the RetailStock row
      const newRetailStock = await tx.retailStock.create({
        data: {
          purchaseId: purchase.id,
          lotNo: lotNo,
          allocatedUnit: data.allocatedUnit,
          allocatedQty: allocatedQtyDecimal,
          quantity: kgQty,
          bags: bagQty,
          remainingQuantity: kgQty,
          remainingBags: bagQty,
          costPerUnit: costPerUnitKg,
          allocationDate: data.allocationDate,
          remarks: data.remarks ?? null,
          createdBy: data.createdBy,
        },
      });

      return newRetailStock;
    });
  }

  async getAvailableLots(vegetableId?: bigint) {
    const where: Prisma.PurchaseWhereInput = {
      deletedAt: null,
      status: { not: "CLOSED" },
      OR: [
        { remainingQuantity: { gt: 0 } },
        { remainingBags: { gt: 0 } },
      ],
    };

    if (vegetableId) where.vegetableId = vegetableId;

    return 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,
        cashSupplierName: true,
        vegetable: { select: { id: true, name: true } },
        supplier: { select: { name: true } },
      },
      orderBy: [
        { purchaseDate: "asc" },
        { createdAt: "asc" },
        { id: "asc" }
      ],
    });
  }

  async getAllocations(page: number = 1, limit: number = 10, search?: string) {
    const skip = (page - 1) * limit;

    const where: Prisma.RetailStockWhereInput = {};
    if (search) {
      where.lotNo = { contains: search };
    }

    const [allocations, total] = await Promise.all([
      prisma.retailStock.findMany({
        where,
        skip,
        take: limit,
        orderBy: { id: "desc" },
        include: {
          purchase: {
            include: {
              vegetable: true,
              supplier: true,
            },
          },
          createdByUser: {
            select: { name: true },
          },
        },
      }),
      prisma.retailStock.count({ where }),
    ]);

    // Convert BigInt to strings for all returned data
    const serializedAllocations = allocations.map(allocation => ({
      ...allocation,
      id: allocation.id.toString(),
      purchaseId: allocation.purchaseId.toString(),
      createdBy: allocation.createdBy?.toString(),
      purchase: allocation.purchase ? {
        ...allocation.purchase,
        id: allocation.purchase.id.toString(),
        supplierId: allocation.purchase.supplierId?.toString() ?? null,
        vegetableId: allocation.purchase.vegetableId.toString(),
        createdBy: allocation.purchase.createdBy?.toString(),
        supplier: allocation.purchase.supplier ? {
          ...allocation.purchase.supplier,
          id: allocation.purchase.supplier.id.toString(),
          stateId: allocation.purchase.supplier.stateId.toString(),
          districtId: allocation.purchase.supplier.districtId.toString(),
          createdBy: allocation.purchase.supplier.createdBy?.toString(),
        } : null,
        vegetable: allocation.purchase.vegetable ? {
          ...allocation.purchase.vegetable,
          id: allocation.purchase.vegetable.id.toString(),
          createdBy: allocation.purchase.vegetable.createdBy?.toString(),
        } : null
      } : null,
    }));

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

  /**
   * Returns current retail stock levels, grouped by lotNo.
   * Each row is the SUM of all RetailStock allocations for that lotNo.
   * Used for the "Current Stock" tab on the Retail Stock page.
   */
  async getRetailStockSummary() {
    // Aggregate by lotNo using groupBy
    const groups = await prisma.retailStock.groupBy({
      by: ["lotNo", "purchaseId"],
      where: { deletedAt: null },
      _sum: {
        quantity: true,
        allocatedQty: true,
        remainingQuantity: true,
        remainingBags: true,
        bags: true,
      },
      _count: { id: true }, // how many allocation rows = how many times stock moved
      orderBy: { lotNo: "asc" },
    });

    // Fetch vegetable names for each unique purchaseId
    const purchaseIds = [...new Set(groups.map(g => g.purchaseId))];
    const purchases = await prisma.purchase.findMany({
      where: { id: { in: purchaseIds } },
      select: {
        id: true,
        lotNo: true,
        unit: true,
        weightPerBag: true,
        cashSupplierName: true,
        vegetable: { select: { id: true, name: true, isFlexible: true } },
        supplier: { select: { name: true } },
      },
    });
    const purchaseMap = new Map(purchases.map(p => [p.id.toString(), p]));

    return groups.map(g => {
      const purchase = purchaseMap.get(g.purchaseId.toString());
      return {
        lotNo: g.lotNo,
        purchaseId: g.purchaseId.toString(),
        purchaseLotNo: purchase?.lotNo ?? null,
        vegetable: purchase?.vegetable ?? null,
        isFlexible: !!purchase?.vegetable?.isFlexible || purchase?.unit === 'THAAR',
        supplier: purchase?.supplier ?? null,
        cashSupplierName: purchase?.cashSupplierName ?? null,
        unit: purchase?.unit ?? null,
        weightPerBag: purchase?.weightPerBag ?? null,
        totalAllocatedQty: g._sum.allocatedQty,
        totalAllocatedKg: g._sum.quantity,
        totalAllocatedBags: g._sum.bags,
        remainingQuantity: g._sum.remainingQuantity,
        remainingBags: g._sum.remainingBags,
        movementCount: g._count.id,
      };
    });
  }

  /**
   * Manually closes a flexible retail lot or allocation by depleting remaining quantity and bags to 0.
   */
  async closeRetailLot(params: { lotNo?: string | undefined; id?: bigint | undefined; purchaseId?: bigint | undefined }) {
    const where: Prisma.RetailStockWhereInput = { deletedAt: null };
    if (params.id) {
      where.id = params.id;
    } else if (params.lotNo) {
      where.lotNo = params.lotNo;
      if (params.purchaseId) {
        where.purchaseId = params.purchaseId;
      }
    } else {
      throw new Error("lotNo or id is required to close retail lot");
    }

    const totalCount = await prisma.retailStock.count({ where });
    if (totalCount === 0) {
      throw new Error("No retail stock found to close");
    }

    const activeWhere: Prisma.RetailStockWhereInput = {
      ...where,
      OR: [
        { remainingQuantity: { gt: 0 } },
        { remainingBags: { gt: 0 } },
      ],
    };

    const activeCount = await prisma.retailStock.count({ where: activeWhere });
    if (activeCount === 0) {
      throw new Error("Retail lot is already closed");
    }

    await prisma.retailStock.updateMany({
      where: activeWhere,
      data: {
        remainingQuantity: 0,
        remainingBags: 0,
      },
    });

    return { success: true, message: "Retail lot closed successfully" };
  }

  /**
   * Returns paginated wholesale → retail stock movement history.
   * Each row is a single RetailStock allocation event.
   * Filterable by lotNo for drill-down from Current Stock tab.
   */
  async getStockMovements(page: number = 1, limit: number = 10, lotNo?: string) {
    const skip = (page - 1) * limit;
    const where: Prisma.RetailStockWhereInput = {};
    if (lotNo) where.lotNo = { contains: lotNo };

    const [movements, total] = await Promise.all([
      prisma.retailStock.findMany({
        where,
        skip,
        take: limit,
        orderBy: { id: "desc" },
        include: {
          purchase: {
            select: {
              id: true,
              lotNo: true,
              unit: true,
              weightPerBag: true,
              vegetable: { select: { id: true, name: true } },
              supplier: { select: { name: true } },
            },
          },
          createdByUser: { select: { name: true } },
        },
      }),
      prisma.retailStock.count({ where }),
    ]);

    const serialized = movements.map(m => ({
      id: m.id.toString(),
      purchaseId: m.purchaseId.toString(),
      lotNo: m.lotNo,
      allocatedUnit: m.allocatedUnit,
      allocatedQty: m.allocatedQty,
      quantity: m.quantity,
      bags: m.bags,
      remainingQuantity: m.remainingQuantity,
      remainingBags: m.remainingBags,
      costPerUnit: m.costPerUnit,
      allocationDate: m.allocationDate,
      remarks: m.remarks,
      createdAt: m.createdAt,
      createdByUser: m.createdByUser,
      purchase: m.purchase ? {
        ...m.purchase,
        id: m.purchase.id.toString(),
        vegetable: m.purchase.vegetable ? { ...m.purchase.vegetable, id: m.purchase.vegetable.id.toString() } : null,
      } : null,
    }));

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

  /**
   * Returns retail stock lots that still have remaining quantity > 0.
   * Used to populate the "Select Retail Lot" dropdown in the retail sale modal.
   */
  async getAvailableRetailLots(vegetableId?: bigint) {
    // We need to find lots where the SUM of remaining > 0 per lotNo.
    // Strategy: get all RetailStock rows with remaining > 0, then group in JS
    // (Prisma groupBy doesn't support HAVING easily for this aggregation).
    // For a more efficient DB query we use raw-ish approach via groupBy + filter.

    const groups = await prisma.retailStock.groupBy({
      by: ["lotNo", "purchaseId"],
      where: {
        deletedAt: null,
        OR: [
          { remainingQuantity: { gt: 0 } },
          { remainingBags: { gt: 0 } },
        ],
      },
      _sum: {
        remainingQuantity: true,
        remainingBags: true,
      },
      orderBy: { lotNo: "asc" },
    });

    // Filter: only groups where sum remaining > 0
    const validGroups = groups.filter(
      g => (g._sum.remainingQuantity?.gt(0) ?? false) || (g._sum.remainingBags?.gt(0) ?? false)
    );

    const purchaseIds = [...new Set(validGroups.map(g => g.purchaseId))];

    const purchases = await prisma.purchase.findMany({
      where: {
        id: { in: purchaseIds },
        ...(vegetableId ? { vegetableId } : {}),
      },
      select: {
        id: true,
        lotNo: true,
        unit: true,
        rateType: true,
        weightPerBag: true,
        costPerUnit: true,
        vegetable: { select: { id: true, name: true, isFlexible: true } },
      },
    });
    const purchaseMap = new Map(purchases.map(p => [p.id.toString(), p]));

    return validGroups
      .filter(g => purchaseMap.has(g.purchaseId.toString()))
      .map(g => {
        const purchase = purchaseMap.get(g.purchaseId.toString())!;
        return {
          lotNo: g.lotNo,
          purchaseId: g.purchaseId.toString(),
          vegetable: purchase.vegetable,
          unit: purchase.unit,
          rateType: purchase.rateType,
          weightPerBag: purchase.weightPerBag,
          costPerUnit: purchase.costPerUnit,
          isFlexible: !!purchase.vegetable?.isFlexible,
          remainingQuantity: g._sum.remainingQuantity,
          remainingBags: g._sum.remainingBags,
        };
      });
  }

  async recordSpoilage(data: {
    retailAllocationId?: bigint;
    lotNo?: string;
    quantity: number;
    spoilageDate: Date;
    reason?: string;
    remarks?: string;
    createdBy: bigint;
  }) {
    return prisma.$transaction(async (tx) => {
      let activeAllocations: any[] = [];
      if (data.retailAllocationId) {
        const singleAlloc = await tx.retailStock.findUnique({ where: { id: data.retailAllocationId }, include: { purchase: true } });
        if (singleAlloc) activeAllocations = [singleAlloc];
      } else if (data.lotNo) {
        activeAllocations = await tx.retailStock.findMany({
          where: { lotNo: data.lotNo, remainingQuantity: { gt: 0 } },
          include: { purchase: true },
          orderBy: { id: "asc" },
        });
      }
      
      if (activeAllocations.length === 0) {
        throw new Error("Retail stock allocation lot not found or no remaining stock available");
      }

      const totalAvailableKg = activeAllocations.reduce((sum, alloc) => sum.add(alloc.remainingQuantity), new Prisma.Decimal(0));
      let remainingSpoilageQty = new Prisma.Decimal(data.quantity);

      if (remainingSpoilageQty.greaterThan(totalAvailableKg)) {
        throw new Error(`Spoilage quantity (${remainingSpoilageQty}) exceeds remaining retail stock (${totalAvailableKg} KG)`);
      }

      const spoiledRecords = [];

      for (const retailStock of activeAllocations) {
        if (remainingSpoilageQty.lte(0)) break;

        const deductQty = Prisma.Decimal.min(remainingSpoilageQty, retailStock.remainingQuantity);

        const weightPerBag = retailStock.purchase?.weightPerBag && retailStock.purchase.weightPerBag.gt(0)
          ? retailStock.purchase.weightPerBag
          : null;

        let bagsToDeduct = null;
        if (deductQty.gte(retailStock.remainingQuantity)) {
          bagsToDeduct = retailStock.remainingBags;
        } else {
          bagsToDeduct = weightPerBag
            ? deductQty.div(weightPerBag).toDecimalPlaces(3, Prisma.Decimal.ROUND_HALF_UP)
            : retailStock.bags && retailStock.quantity.gt(0)
            ? deductQty.mul(retailStock.bags).div(retailStock.quantity).toDecimalPlaces(3, Prisma.Decimal.ROUND_HALF_UP)
            : null;
        }

        const spoilage = await tx.retailStockSpoilage.create({
          data: {
            retailStockId: retailStock.id,
            spoilageDate: data.spoilageDate,
            quantity: deductQty,
            reason: data.reason ?? null,
            remarks: data.remarks ?? null,
            createdBy: data.createdBy,
          },
        });

        await tx.retailStock.update({
          where: { id: retailStock.id },
          data: {
            remainingQuantity: { decrement: deductQty },
            ...(bagsToDeduct !== null && retailStock.remainingBags !== null
              ? { remainingBags: { decrement: bagsToDeduct } }
              : {}),
          },
        });

        spoiledRecords.push({
          id: spoilage.id.toString(),
          retailStockId: spoilage.retailStockId.toString(),
          quantity: spoilage.quantity,
          spoilageDate: spoilage.spoilageDate,
        });

        remainingSpoilageQty = remainingSpoilageQty.sub(deductQty);
      }

      return spoiledRecords;
    });
  }

  async returnStock(data: {
    retailAllocationId?: bigint;
    purchaseId?: bigint;
    quantity: number;
    returnDate: Date;
    reason?: string;
    remarks?: string;
    returnUnit?: string;
    returnedBags?: number;
    createdBy: bigint;
  }) {
    return prisma.$transaction(async (tx) => {
      let activeAllocations: any[] = [];
      if (data.retailAllocationId) {
        const singleAlloc = await tx.retailStock.findUnique({ where: { id: data.retailAllocationId }, include: { purchase: true } });
        if (singleAlloc) activeAllocations = [singleAlloc];
      } else if (data.purchaseId) {
        activeAllocations = await tx.retailStock.findMany({
          where: { purchaseId: data.purchaseId, remainingQuantity: { gt: 0 } },
          include: { purchase: true },
          orderBy: { id: "asc" },
        });
      }

      if (activeAllocations.length === 0) {
        throw new Error("Retail stock allocation not found or no remaining stock available");
      }

      const totalAvailableKg = activeAllocations.reduce((sum, alloc) => sum.add(alloc.remainingQuantity), new Prisma.Decimal(0));
      let remainingReturnQty = new Prisma.Decimal(data.quantity);

      if (remainingReturnQty.greaterThan(totalAvailableKg)) {
        throw new Error(`Return quantity (${remainingReturnQty}) exceeds remaining retail stock (${totalAvailableKg} KG)`);
      }

      let totalBagsToRestore = new Prisma.Decimal(0);
      const returnedRecords = [];

      for (const retailStock of activeAllocations) {
        if (remainingReturnQty.lte(0)) break;

        const deductQty = Prisma.Decimal.min(remainingReturnQty, retailStock.remainingQuantity);

        const weightPerBag = retailStock.purchase?.weightPerBag && retailStock.purchase.weightPerBag.gt(0)
          ? retailStock.purchase.weightPerBag
          : null;

        let bagsToRestore = null;
        if (deductQty.gte(retailStock.remainingQuantity)) {
          bagsToRestore = retailStock.remainingBags;
        } else if (data.returnUnit !== 'KG') {
          bagsToRestore = weightPerBag
            ? deductQty.div(weightPerBag).toDecimalPlaces(3, Prisma.Decimal.ROUND_HALF_UP)
            : retailStock.bags && retailStock.quantity.gt(0)
            ? deductQty.mul(retailStock.bags).div(retailStock.quantity).toDecimalPlaces(3, Prisma.Decimal.ROUND_HALF_UP)
            : null;
        }

        if (bagsToRestore) totalBagsToRestore = totalBagsToRestore.add(bagsToRestore);

        await tx.retailStock.update({
          where: { id: retailStock.id },
          data: {
            remainingQuantity: { decrement: deductQty },
            ...(bagsToRestore !== null && retailStock.remainingBags !== null
              ? { remainingBags: { decrement: bagsToRestore } }
              : {}),
          },
        });

        // Restore wholesale purchase remaining stock incrementally
        await tx.purchase.update({
          where: { id: retailStock.purchaseId },
          data: {
            remainingQuantity: { increment: deductQty },
            ...(bagsToRestore !== null && retailStock.purchase.unit !== 'KG'
              ? { remainingBags: { increment: bagsToRestore } }
              : {}),
            status: "ACTIVE",
          },
        });

        const returnRecord = await tx.retailStockReturn.create({
          data: {
            retailStockId: retailStock.id,
            returnDate: data.returnDate,
            quantity: deductQty,
            reason: data.reason ?? null,
            remarks: data.remarks ?? null,
            createdBy: data.createdBy,
          },
        });

        returnedRecords.push({
          retailStockId: returnRecord.retailStockId.toString(),
          purchaseId: retailStock.purchaseId.toString(),
          returnedQuantity: returnRecord.quantity,
          returnedBags: bagsToRestore,
          returnDate: returnRecord.returnDate,
        });

        remainingReturnQty = remainingReturnQty.sub(deductQty);
      }

      return returnedRecords;
    });
  }

  async getRetailStockSpoilages(page = 1, limit = 10, vegetableId?: bigint, dateFrom?: Date, dateTo?: Date) {
    const skip = (page - 1) * limit;
    const where: Prisma.RetailStockSpoilageWhereInput = {};

    if (vegetableId) {
      where.retailStock = { purchase: { vegetableId } };
    }
    if (dateFrom || dateTo) {
      where.spoilageDate = {};
      if (dateFrom) where.spoilageDate.gte = dateFrom;
      if (dateTo) where.spoilageDate.lte = dateTo;
    }

    const [items, total] = await Promise.all([
      prisma.retailStockSpoilage.findMany({
        where,
        skip,
        take: limit,
        orderBy: { id: "desc" },
        include: {
          retailStock: {
            include: {
              purchase: {
                include: {
                  vegetable: true,
                },
              },
            },
          },
        },
      }),
      prisma.retailStockSpoilage.count({ where }),
    ]);

    const serialized = items.map(item => ({
      ...item,
      id: item.id.toString(),
      retailStockId: item.retailStockId.toString(),
      retailStock: item.retailStock ? {
        ...item.retailStock,
        id: item.retailStock.id.toString(),
        purchaseId: item.retailStock.purchaseId.toString(),
        purchase: item.retailStock.purchase ? {
          ...item.retailStock.purchase,
          id: item.retailStock.purchase.id.toString(),
          vegetable: item.retailStock.purchase.vegetable ? {
            ...item.retailStock.purchase.vegetable,
            id: item.retailStock.purchase.vegetable.id.toString(),
          } : null,
        } : null,
      } : null,
    }));

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