import { Injectable, Logger } from '@nestjs/common';
import { and, eq, gte, lte, sql, inArray, lt, asc, desc } from 'drizzle-orm';
import Decimal from 'decimal.js';
import { StorageMethod, TariffConditions } from '@wms/shared';
import { DbService, Tx } from '@/common/db/db.service';
import { storageSnapshots, inventoryBalances, inventoryMovements, skus, locations, zones, warehouses } from '@/db/schema';
import { requireRequestContext } from '@/common/context/request-context';
import { D, ZERO, addDays, weekKey, daysBetween, serialize, localDayStart } from '@/common/util';
import { ContractsService, ContractRow, TariffRow } from './contracts.service';
import { ChargeCaptureService } from './charge-capture.service';

export interface StorageLine {
  tariffId: string;
  tariffCode: string;
  description: string;
  unit: string;
  temperatureClass: string | null;
  quantity: Decimal;
  rate: Decimal;
  amount: Decimal;
  detail: Record<string, unknown>;
}

/**
 * Storage billing.
 * 1. Daily snapshot job records occupancy per warehouse x storer (CBM, pallet positions,
 *    qty, inbound / outbound CBM for the day, split by temperature class).
 * 2. At invoice time the contract's storage method turns snapshots into charge lines:
 *    - WEEKLY_CBM_OPENING_PLUS_IN:  (opening CBM of week + inbound CBM during week) x weekly rate
 *    - WEEKLY_CBM_PEAK:             peak daily CBM in week x weekly rate
 *    - DAILY_CBM / DAILY_PALLET:    sum of daily occupancy x daily rate
 *    - WEEKLY_PALLET_PEAK:          peak pallet positions in week x weekly rate
 *    - MONTHLY_FLAT:                fixed fee + overage above the cap at the storage tariff rate
 * Rates can differ per temperature class via tariff.conditions.temperatureClass.
 */
@Injectable()
export class StorageBillingService {
  private readonly logger = new Logger(StorageBillingService.name);

  constructor(
    private readonly db: DbService,
    private readonly contracts: ContractsService,
    private readonly charges: ChargeCaptureService,
  ) {}

  // ---------------- snapshots ----------------
  /** Takes (or re-takes) the snapshot for one warehouse for a date. Idempotent per day. */
  async snapshotWarehouse(tx: Tx, warehouseId: string, dateIso: string): Promise<number> {
    const ctx = requireRequestContext();
    const occupancy = await tx
      .select({
        storerId: inventoryBalances.storerId,
        temperatureClass: zones.temperatureClass,
        qty: sql<string>`sum(${inventoryBalances.qtyOnHand})`,
        cbm: sql<string>`sum(coalesce(${skus.cbm}, (coalesce(${skus.lengthCm},0) * coalesce(${skus.widthCm},0) * coalesce(${skus.heightCm},0)) / 1000000.0) * ${inventoryBalances.qtyOnHand})`,
        kg: sql<string>`sum(coalesce(${skus.weightKg},0) * ${inventoryBalances.qtyOnHand})`,
        pallets: sql<number>`count(distinct coalesce(${inventoryBalances.lpnId}::text, ${inventoryBalances.locationId}::text))`.mapWith(Number),
      })
      .from(inventoryBalances)
      .innerJoin(skus, eq(skus.id, inventoryBalances.skuId))
      .innerJoin(locations, eq(locations.id, inventoryBalances.locationId))
      .innerJoin(zones, eq(zones.id, locations.zoneId))
      .where(and(eq(inventoryBalances.warehouseId, warehouseId), sql`${inventoryBalances.qtyOnHand} > 0`, inArray(locations.usage, ['RESERVE', 'PICK', 'QUARANTINE', 'DAMAGED', 'RETURNS', 'QC'])))
      .groupBy(inventoryBalances.storerId, zones.temperatureClass);

    // Day boundaries in the warehouse's local time so inbound/outbound flows land on the same day as the snapshot.
    const [wh] = await tx.select({ timezone: warehouses.timezone }).from(warehouses).where(eq(warehouses.id, warehouseId)).limit(1);
    const tz = wh?.timezone ?? 'Asia/Kuwait';
    const from = localDayStart(dateIso, tz);
    const to = localDayStart(addDays(dateIso, 1), tz);
    const flows = await tx
      .select({ storerId: inventoryMovements.storerId, type: inventoryMovements.type, cbm: sql<string>`coalesce(sum(${inventoryMovements.cbm}),0)` })
      .from(inventoryMovements)
      .where(and(eq(inventoryMovements.warehouseId, warehouseId), gte(inventoryMovements.performedAt, from), lt(inventoryMovements.performedAt, to), inArray(inventoryMovements.type, ['RECEIPT', 'RETURN_RECEIPT', 'RECEIPT_REVERSAL', 'SHIP', 'TRANSFER_IN', 'TRANSFER_OUT'])))
      .groupBy(inventoryMovements.storerId, inventoryMovements.type);

    const byStorer = new Map<string, { qty: Decimal; cbm: Decimal; kg: Decimal; pallets: number; byClass: Record<string, { cbm: string; pallets: number; qty: string }> }>();
    for (const o of occupancy) {
      const s = byStorer.get(o.storerId) ?? { qty: ZERO, cbm: ZERO, kg: ZERO, pallets: 0, byClass: {} };
      s.qty = s.qty.plus(o.qty);
      s.cbm = s.cbm.plus(o.cbm);
      s.kg = s.kg.plus(o.kg);
      s.pallets += o.pallets;
      s.byClass[o.temperatureClass] = { cbm: D(o.cbm).toFixed(6), pallets: o.pallets, qty: D(o.qty).toFixed(4) };
      byStorer.set(o.storerId, s);
    }
    // storers with flows today but zero stock still get a row (so the in/out is captured)
    for (const f of flows) if (!byStorer.has(f.storerId)) byStorer.set(f.storerId, { qty: ZERO, cbm: ZERO, kg: ZERO, pallets: 0, byClass: {} });

    let n = 0;
    for (const [storerId, s] of byStorer) {
      const inbound = flows.filter((f) => f.storerId === storerId && ['RECEIPT', 'RETURN_RECEIPT', 'TRANSFER_IN'].includes(f.type)).reduce((a, f) => a.plus(f.cbm), ZERO).minus(flows.filter((f) => f.storerId === storerId && f.type === 'RECEIPT_REVERSAL').reduce((a, f) => a.plus(f.cbm), ZERO));
      const outbound = flows.filter((f) => f.storerId === storerId && ['SHIP', 'TRANSFER_OUT'].includes(f.type)).reduce((a, f) => a.plus(f.cbm), ZERO);
      await tx
        .insert(storageSnapshots)
        .values({ tenantId: ctx.tenantId!, warehouseId, storerId, snapshotDate: dateIso, totalQty: s.qty, totalCbm: s.cbm, totalWeightKg: s.kg, palletPositions: s.pallets, inboundCbm: inbound, outboundCbm: outbound, byTemperatureClass: s.byClass })
        .onConflictDoUpdate({ target: [storageSnapshots.tenantId, storageSnapshots.warehouseId, storageSnapshots.storerId, storageSnapshots.snapshotDate], set: { totalQty: s.qty, totalCbm: s.cbm, totalWeightKg: s.kg, palletPositions: s.pallets, inboundCbm: inbound, outboundCbm: outbound, byTemperatureClass: s.byClass } });
      n++;
    }
    return n;
  }

  /** Snapshot every active warehouse of the current tenant for a date (job entry point). */
  async snapshotTenant(dateIso: string): Promise<{ warehouses: number; rows: number }> {
    return this.db.run(async (tx) => {
      const whs = await tx.select({ id: warehouses.id }).from(warehouses).where(eq(warehouses.status, 'ACTIVE'));
      let rows = 0;
      for (const w of whs) rows += await this.snapshotWarehouse(tx, w.id, dateIso);
      return { warehouses: whs.length, rows };
    });
  }

  /** Report: daily or weekly occupancy for a storer. */
  report(q: { storerId: string; warehouseId?: string; from: string; to: string; granularity: 'DAY' | 'WEEK' }) {
    return this.db.run(async (tx) => {
      const rows = await tx
        .select()
        .from(storageSnapshots)
        .where(and(eq(storageSnapshots.storerId, q.storerId), q.warehouseId ? eq(storageSnapshots.warehouseId, q.warehouseId) : undefined, gte(storageSnapshots.snapshotDate, q.from), lte(storageSnapshots.snapshotDate, q.to)))
        .orderBy(asc(storageSnapshots.snapshotDate));
      if (q.granularity === 'DAY') return serialize(rows);
      const weeks = new Map<string, { key: string; start: string; end: string; openingCbm: Decimal; peakCbm: Decimal; peakPallets: number; inboundCbm: Decimal; outboundCbm: Decimal; closingCbm: Decimal; days: number }>();
      for (const r of rows) {
        const wk = weekKey(r.snapshotDate, 1);
        const w = weeks.get(wk.key) ?? { ...wk, openingCbm: D(r.totalCbm).minus(r.inboundCbm).plus(r.outboundCbm), peakCbm: ZERO, peakPallets: 0, inboundCbm: ZERO, outboundCbm: ZERO, closingCbm: ZERO, days: 0 };
        w.peakCbm = Decimal.max(w.peakCbm, r.totalCbm);
        w.peakPallets = Math.max(w.peakPallets, r.palletPositions);
        w.inboundCbm = w.inboundCbm.plus(r.inboundCbm);
        w.outboundCbm = w.outboundCbm.plus(r.outboundCbm);
        w.closingCbm = D(r.totalCbm);
        w.days++;
        weeks.set(wk.key, w);
      }
      return serialize(Array.from(weeks.values()));
    });
  }

  // ---------------- period calculation ----------------
  async computePeriod(tx: Tx, contract: ContractRow, periodStart: string, periodEnd: string, warehouseIds?: string[]): Promise<StorageLine[]> {
    const tariffsRows = (await this.contracts.activeTariffs(tx, contract.id, periodEnd)).filter((t) => t.group === 'STORAGE');
    const snaps = await tx
      .select()
      .from(storageSnapshots)
      .where(and(eq(storageSnapshots.storerId, contract.storerId), gte(storageSnapshots.snapshotDate, addDays(periodStart, -7)), lte(storageSnapshots.snapshotDate, periodEnd), warehouseIds?.length ? inArray(storageSnapshots.warehouseId, warehouseIds) : undefined))
      .orderBy(asc(storageSnapshots.snapshotDate));
    // aggregate across warehouses per day
    const perDay = new Map<string, { cbm: Decimal; pallets: number; inbound: Decimal; outbound: Decimal; byClass: Record<string, { cbm: Decimal; pallets: number }> }>();
    for (const s of snaps) {
      const d = perDay.get(s.snapshotDate) ?? { cbm: ZERO, pallets: 0, inbound: ZERO, outbound: ZERO, byClass: {} };
      d.cbm = d.cbm.plus(s.totalCbm);
      d.pallets += s.palletPositions;
      d.inbound = d.inbound.plus(s.inboundCbm);
      d.outbound = d.outbound.plus(s.outboundCbm);
      for (const [cls, v] of Object.entries(s.byTemperatureClass ?? {})) {
        const c = d.byClass[cls] ?? { cbm: ZERO, pallets: 0 };
        c.cbm = c.cbm.plus(v.cbm);
        c.pallets += v.pallets;
        d.byClass[cls] = c;
      }
      perDay.set(s.snapshotDate, d);
    }
    if (!perDay.size) return [];

    const classes = new Set<string>();
    for (const d of perDay.values()) Object.keys(d.byClass).forEach((c) => classes.add(c));
    const lines: StorageLine[] = [];
    const method = contract.storageMethod as StorageMethod;

    const tariffFor = (unit: string, cls: string | null): TariffRow | undefined => {
      const byClass = tariffsRows.find((t) => t.chargeUnit === unit && (t.conditions as TariffConditions).temperatureClass === cls);
      if (byClass) return byClass;
      return tariffsRows.find((t) => t.chargeUnit === unit && !(t.conditions as TariffConditions).temperatureClass);
    };
    const classesOrAll = (unit: string): (string | null)[] => {
      const hasClassRates = tariffsRows.some((t) => t.chargeUnit === unit && (t.conditions as TariffConditions).temperatureClass);
      return hasClassRates ? Array.from(classes) : [null];
    };
    const metric = (d: { cbm: Decimal; pallets: number; byClass: Record<string, { cbm: Decimal; pallets: number }> }, cls: string | null, what: 'cbm' | 'pallets'): Decimal => {
      if (cls === null) return what === 'cbm' ? d.cbm : D(d.pallets);
      const c = d.byClass[cls];
      return c ? (what === 'cbm' ? c.cbm : D(c.pallets)) : ZERO;
    };

    const days = Array.from(perDay.keys()).filter((k) => k >= periodStart && k <= periodEnd).sort();
    // Weekly methods bill whole weeks. A week belongs to the invoice period in which it ENDS, so a week
    // straddling two monthly periods is charged exactly once (in the later period) and its days come
    // from the previous period's snapshots (loaded with the 7-day look-back above).
    const weekGroups = new Map<string, { key: string; start: string; end: string; days: string[] }>();
    const allDays = Array.from(perDay.keys()).sort();
    for (const day of allDays) {
      const wk = weekKey(day, contract.weekStartsOn);
      if (wk.end < periodStart || wk.end > periodEnd) continue; // ends outside this period: billed elsewhere
      const g = weekGroups.get(wk.key) ?? { ...wk, days: [] };
      g.days.push(day);
      weekGroups.set(wk.key, g);
    }

    if (method === 'WEEKLY_CBM_OPENING_PLUS_IN' || method === 'WEEKLY_CBM_PEAK' || method === 'WEEKLY_PALLET_PEAK') {
      const unit = method === 'WEEKLY_PALLET_PEAK' ? 'PER_PALLET_WEEK' : 'PER_CBM_WEEK';
      const what = method === 'WEEKLY_PALLET_PEAK' ? 'pallets' : 'cbm';
      for (const cls of classesOrAll(unit)) {
        const t = tariffFor(unit, cls);
        if (!t) continue;
        const weeks: Record<string, unknown>[] = [];
        let total = ZERO;
        let chargeableTotal = ZERO;
        for (const g of weekGroups.values()) {
          const dayBefore = addDays(g.days[0], -1);
          const opening = metric(perDay.get(dayBefore) ?? { cbm: ZERO, pallets: 0, byClass: {} }, cls, what);
          const inbound = g.days.reduce((a, d) => a.plus(perDay.get(d)!.inbound), ZERO); // inbound CBM is tracked per storer, not per class
          const outbound = g.days.reduce((a, d) => a.plus(perDay.get(d)!.outbound), ZERO);
          const peak = g.days.reduce((a, d) => Decimal.max(a, metric(perDay.get(d)!, cls, what)), ZERO);
          const chargeable = method === 'WEEKLY_CBM_OPENING_PLUS_IN' ? (opening.gt(0) ? opening : metric(perDay.get(g.days[0])!, cls, what).minus(perDay.get(g.days[0])!.inbound)).plus(inbound) : peak;
          const rate = this.charges.rateFor(t, chargeable);
          const amount = chargeable.mul(rate);
          weeks.push({ key: g.key, start: g.start, end: g.end, opening: opening.toFixed(3), inbound: inbound.toFixed(3), outbound: outbound.toFixed(3), peak: peak.toFixed(3), chargeable: chargeable.toFixed(3), rate: rate.toString(), amount: amount.toFixed(3), days: g.days.length });
          total = total.plus(amount);
          chargeableTotal = chargeableTotal.plus(chargeable);
        }
        lines.push({ tariffId: t.id, tariffCode: t.code, description: `${t.description}${cls ? ` (${cls})` : ''}`, unit, temperatureClass: cls, quantity: chargeableTotal, rate: D(t.rate), amount: total, detail: { method, weeks } });
      }
    } else if (method === 'DAILY_CBM' || method === 'DAILY_PALLET') {
      const unit = method === 'DAILY_CBM' ? 'PER_CBM_DAY' : 'PER_PALLET_DAY';
      const what = method === 'DAILY_CBM' ? 'cbm' : 'pallets';
      for (const cls of classesOrAll(unit)) {
        const t = tariffFor(unit, cls);
        if (!t) continue;
        const qty = days.reduce((a, d) => a.plus(metric(perDay.get(d)!, cls, what)), ZERO);
        const rate = this.charges.rateFor(t, qty);
        lines.push({ tariffId: t.id, tariffCode: t.code, description: `${t.description}${cls ? ` (${cls})` : ''}`, unit, temperatureClass: cls, quantity: qty, rate, amount: qty.mul(rate), detail: { method, days: days.length, daily: days.map((d) => ({ date: d, value: metric(perDay.get(d)!, cls, what).toFixed(3) })) } });
      }
    } else if (method === 'MONTHLY_FLAT') {
      const flat = tariffsRows.find((t) => t.chargeUnit === 'FLAT_MONTHLY');
      const months = Math.max(1, Math.round(daysBetween(periodStart, periodEnd) / 30));
      if (contract.fixedMonthlyFee || flat) {
        const fee = D(contract.fixedMonthlyFee ?? flat!.rate).mul(months);
        lines.push({ tariffId: flat?.id ?? tariffsRows[0]?.id ?? '', tariffCode: flat?.code ?? 'FLAT', description: flat?.description ?? 'Fixed monthly storage fee', unit: 'FLAT_MONTHLY', temperatureClass: null, quantity: D(months), rate: D(contract.fixedMonthlyFee ?? flat!.rate), amount: fee, detail: { method, months } });
      }
      const cap = contract.storageCap as { unit: 'PALLET' | 'CBM'; cap: number; byTemperatureClass?: Record<string, number> } | null;
      if (cap) {
        const what = cap.unit === 'PALLET' ? 'pallets' : 'cbm';
        const unit = cap.unit === 'PALLET' ? 'PER_PALLET_WEEK' : 'PER_CBM_WEEK';
        const targets: (string | null)[] = cap.byTemperatureClass ? Object.keys(cap.byTemperatureClass) : [null];
        for (const cls of targets) {
          const t = tariffFor(unit, cls);
          if (!t) continue;
          const limit = D(cls ? cap.byTemperatureClass![cls] : cap.cap);
          const weeks: Record<string, unknown>[] = [];
          let overQty = ZERO;
          let amount = ZERO;
          for (const g of weekGroups.values()) {
            const peak = g.days.reduce((a, d) => Decimal.max(a, metric(perDay.get(d)!, cls, what)), ZERO);
            const over = Decimal.max(peak.minus(limit), ZERO);
            const rate = this.charges.rateFor(t, over);
            weeks.push({ key: g.key, start: g.start, end: g.end, peak: peak.toFixed(3), cap: limit.toString(), overage: over.toFixed(3), rate: rate.toString(), amount: over.mul(rate).toFixed(3) });
            overQty = overQty.plus(over);
            amount = amount.plus(over.mul(rate));
          }
          if (overQty.gt(0)) lines.push({ tariffId: t.id, tariffCode: t.code, description: `Overage above cap${cls ? ` (${cls})` : ''}: ${t.description}`, unit, temperatureClass: cls, quantity: overQty, rate: D(t.rate), amount, detail: { method, weeks } });
        }
      }
    }
    return lines;
  }

  latestSnapshotDate(tx: Tx, storerId: string) {
    return tx.select({ d: storageSnapshots.snapshotDate }).from(storageSnapshots).where(eq(storageSnapshots.storerId, storerId)).orderBy(desc(storageSnapshots.snapshotDate)).limit(1).then((r) => r[0]?.d ?? null);
  }
}