import { ok, type ReadonlyDB } from "@tailor-platform/erp-kit/core";
import { Decimal } from "decimal.js";
import type { DB } from "../generated/kysely-tailordb";
import { getOpenStockReservationQuantity } from "../internal/stockReservation";

export interface GetSiteStockSummaryInput {
  siteId: string;
  itemId?: string;
}

export async function run(db: ReadonlyDB<DB>, input: GetSiteStockSummaryInput) {
  const activeLocationIds = await db
    .selectFrom("StorageLocation")
    .select("id")
    .where("siteId", "=", input.siteId)
    .where("status", "=", "ACTIVE")
    .execute();

  if (activeLocationIds.length === 0) {
    return ok({ summary: [] });
  }

  const locationIds = activeLocationIds.map((loc) => loc.id);

  let query = db
    .selectFrom("StockLevel")
    .select("itemId")
    .where("storageLocationId", "in", locationIds);

  if (input.itemId) {
    query = query.where("itemId", "=", input.itemId);
  }

  const stockLevels = await query.selectAll().execute();
  let reservationQuery = db
    .selectFrom("StockReservation")
    .selectAll()
    .where("siteId", "=", input.siteId)
    .where("status", "=", "OPEN");

  if (input.itemId) {
    reservationQuery = reservationQuery.where("itemId", "=", input.itemId);
  }

  const reservations = (await reservationQuery.execute()) ?? [];
  const reservedByItem = new Map<string, Decimal>();
  for (const reservation of reservations) {
    const openReservedQuantity = getOpenStockReservationQuantity(reservation);
    reservedByItem.set(
      reservation.itemId,
      (reservedByItem.get(reservation.itemId) ?? new Decimal(0)).plus(openReservedQuantity),
    );
  }

  // Aggregate in-memory grouped by itemId
  const grouped = new Map<
    string,
    {
      itemId: string;
      totalOnHand: Decimal;
      totalReserved: Decimal;
      totalBlocked: Decimal;
      totalAvailable: Decimal;
    }
  >();

  for (const sl of stockLevels) {
    const existing = grouped.get(sl.itemId);
    const blocked = sl.stockType === "BLOCKED" ? new Decimal(sl.quantity) : new Decimal(0);
    const onHand = sl.stockType === "IN_TRANSIT" ? new Decimal(0) : new Decimal(sl.quantity);
    if (existing) {
      existing.totalOnHand = existing.totalOnHand.plus(onHand);
      existing.totalBlocked = existing.totalBlocked.plus(blocked);
      existing.totalAvailable = existing.totalAvailable.plus(
        sl.stockType === "AVAILABLE" ? new Decimal(sl.quantity) : 0,
      );
    } else {
      grouped.set(sl.itemId, {
        itemId: sl.itemId,
        totalOnHand: onHand,
        totalReserved: reservedByItem.get(sl.itemId) ?? new Decimal(0),
        totalBlocked: blocked,
        totalAvailable: sl.stockType === "AVAILABLE" ? new Decimal(sl.quantity) : new Decimal(0),
      });
    }
  }

  const summary = Array.from(grouped.values()).map((entry) => ({
    itemId: entry.itemId,
    totalOnHand: entry.totalOnHand.toString(),
    totalReserved: entry.totalReserved.toString(),
    totalBlocked: entry.totalBlocked.toString(),
    totalAvailable: entry.totalAvailable.minus(entry.totalReserved).toString(),
  }));

  return ok({ summary });
}