import { Injectable, Logger } from '@nestjs/common';
import { and, eq, gte, inArray, isNull, lte, sql } from 'drizzle-orm';
import { RunAnalysisDto, AiProposalType } from '@wms/shared';
import { DbService, Tx } from '@/common/db/db.service';
import {
  asns, asnLines, receivingDiscrepancies, suppliers, skus, inventoryBalances, inventoryMovements, inventoryHolds,
  cycleCountLines, cycleCounts, locations, zones, tasks, outboundOrders, outboundOrderLines, billingContracts, chargeEvents, storageSnapshots,
  invoices, storers, skuSlottings, skuPacks,
} from '@/db/schema';
import { requireRequestContext } from '@/common/context/request-context';
import { DomainError, ErrorCodes } from '@/common/errors/domain-error';
import { assertWarehouseAccess, resolveStorerScope, todayIso, addDays, serialize, qcol } from '@/common/util';
import { LlmService } from './llm/llm.service';
import { ProposalsService } from './proposals.service';

export type Severity = 'INFO' | 'LOW' | 'MEDIUM' | 'HIGH' | 'CRITICAL';
export interface Finding {
  code: string;
  severity: Severity;
  title: string;
  detail: string;
  refType?: string;
  refId?: string;
  refNumber?: string;
  metrics?: Record<string, unknown>;
  suggestedAction?: string;
}

const SEV_RANK: Record<Severity, number> = { INFO: 0, LOW: 1, MEDIUM: 2, HIGH: 3, CRITICAL: 4 };
const maxSeverity = (fs: Finding[]): Severity => fs.reduce<Severity>((m, f) => (SEV_RANK[f.severity] > SEV_RANK[m] ? f.severity : m), 'INFO');

/** Default labour rates (lines per person-hour) when the warehouse has no history yet. */
const DEFAULT_RATES = { RECEIVE: 20, PUTAWAY: 15, PICK: 25, PACK: 30, REPLENISH: 12, LOAD: 40, MOVE: 15, COUNT: 40, QC: 10 } as const;

/**
 * Deterministic analytics. Every number here comes from SQL over the tenant's
 * own data; the LLM is used only to write a short narrative on top of the
 * findings (and is skipped when AI is disabled or the feature is off).
 * Results are stored as proposals so supervisors can acknowledge, reject, or
 * (for slotting) apply them.
 */
@Injectable()
export class AnalyticsService {
  private readonly logger = new Logger(AnalyticsService.name);

  constructor(
    private readonly db: DbService,
    private readonly llm: LlmService,
    private readonly proposals: ProposalsService,
  ) {}

  async run(dto: RunAnalysisDto) {
    assertWarehouseAccess(dto.warehouseId);
    const storerId = resolveStorerScope(dto.storerId);
    const to = dto.to ?? todayIso();
    const from = dto.from ?? addDays(to, -30);
    const feature = featureFor(dto.kind);
    const ctx = requireRequestContext();
    if (ctx.actorType !== 'PLATFORM_ADMIN' && !ctx.features.includes(feature)) {
      // Deterministic analytics are part of the AI package; keep the gate consistent with the LLM features.
      throw new DomainError(ErrorCodes.FEATURE_DISABLED, `Feature ${feature} is not enabled for this tenant.`, 403);
    }

    const result = await this.db.run(async (tx) => {
      switch (dto.kind) {
        case 'RECEIVING_ANOMALY': return this.receivingAnomalies(tx, dto.warehouseId, storerId, from, to);
        case 'INVENTORY_ANOMALY': return this.inventoryAnomalies(tx, dto.warehouseId, storerId, from, to);
        case 'BILLING_ANOMALY': return this.billingAnomalies(tx, dto.warehouseId, storerId, from, to);
        case 'SLOTTING_RECOMMENDATION': return this.slotting(tx, dto.warehouseId, storerId, from, to);
        case 'WORKLOAD_FORECAST': return this.workloadForecast(tx, dto.warehouseId, storerId, to, dto.horizonDays);
      }
    });

    const narrative = await this.narrative(dto.kind, result.findings, result.payload).catch((e) => {
      this.logger.warn(`Narrative skipped: ${(e as Error).message}`);
      return null;
    });

    const type = dto.kind as AiProposalType;
    const hasContent = result.findings.length > 0 || dto.kind === 'WORKLOAD_FORECAST';
    const proposal = hasContent
      ? await this.db.run((tx) =>
          this.proposals.create(tx, {
            type,
            warehouseId: dto.warehouseId,
            storerId: storerId ?? null,
            title: result.title,
            summary: narrative?.text ?? result.summary,
            payload: { ...result.payload, findings: result.findings, window: { from, to } },
            evidence: { generatedBy: 'deterministic', narrativeModel: narrative?.model ?? null },
            severity: maxSeverity(result.findings),
            confidence: null,
            model: narrative?.model ?? null,
            promptTokens: narrative?.usage.inputTokens ?? 0,
            completionTokens: narrative?.usage.outputTokens ?? 0,
            ttlDays: dto.kind === 'WORKLOAD_FORECAST' ? 3 : 14,
          }),
        )
      : null;

    return serialize({ kind: dto.kind, window: { from, to }, findings: result.findings, summary: narrative?.text ?? result.summary, data: result.payload, proposal });
  }

  // ---------------------------------------------------------------- receiving

  private async receivingAnomalies(tx: Tx, warehouseId: string, storerId: string | undefined, from: string, to: string) {
    const findings: Finding[] = [];
    const fromTs = new Date(`${from}T00:00:00Z`);
    const toTs = new Date(`${to}T23:59:59Z`);
    const base = and(eq(asns.warehouseId, warehouseId), storerId ? eq(asns.storerId, storerId) : undefined, gte(asns.closedAt, fromTs), lte(asns.closedAt, toTs));

    // 1. Supplier discrepancy rate vs warehouse baseline
    const bySupplier = await tx
      .select({
        supplierId: asns.supplierId, supplierName: suppliers.name,
        asnCount: sql<number>`count(distinct ${asns.id})`.mapWith(Number),
        withDisc: sql<number>`count(distinct ${receivingDiscrepancies.asnId})`.mapWith(Number),
        shortQty: sql<string>`coalesce(sum(case when ${receivingDiscrepancies.type} = 'SHORT' then abs(${receivingDiscrepancies.varianceQty}) end),0)`,
        damagedQty: sql<string>`coalesce(sum(case when ${receivingDiscrepancies.type} = 'DAMAGED' then abs(${receivingDiscrepancies.varianceQty}) end),0)`,
      })
      .from(asns)
      .leftJoin(suppliers, eq(suppliers.id, asns.supplierId))
      .leftJoin(receivingDiscrepancies, eq(receivingDiscrepancies.asnId, asns.id))
      .where(and(base, eq(asns.status, 'CLOSED')))
      .groupBy(asns.supplierId, suppliers.name);
    const totalAsns = bySupplier.reduce((a, r) => a + r.asnCount, 0);
    const totalDisc = bySupplier.reduce((a, r) => a + r.withDisc, 0);
    const baseline = totalAsns ? totalDisc / totalAsns : 0;
    for (const r of bySupplier) {
      if (r.asnCount < 3) continue;
      const rate = r.withDisc / r.asnCount;
      if (rate >= 0.3 && rate > baseline * 1.5) {
        findings.push({
          code: 'SUPPLIER_DISCREPANCY_RATE', severity: rate >= 0.5 ? 'HIGH' : 'MEDIUM',
          title: `${r.supplierName ?? 'Unassigned supplier'}: ${(rate * 100).toFixed(0)}% of deliveries had discrepancies`,
          detail: `${r.withDisc} of ${r.asnCount} closed ASNs had short/over/damaged lines (warehouse baseline ${(baseline * 100).toFixed(0)}%). Short qty ${r.shortQty}, damaged qty ${r.damagedQty}.`,
          refType: 'SUPPLIER', refId: r.supplierId ?? undefined,
          metrics: { asnCount: r.asnCount, withDiscrepancy: r.withDisc, rate, baseline },
          suggestedAction: 'Raise with the supplier and switch this supplier to 100% count-on-receipt.',
        });
      }
    }

    // 2. Dock-to-stock outliers (> 2x median, min 8h)
    const d2s = await tx
      .select({ asnId: asns.id, asnNumber: asns.asnNumber, hours: sql<string>`extract(epoch from (max(${tasks.completedAt}) - ${asns.arrivedAt})) / 3600.0` })
      .from(asns)
      .innerJoin(tasks, and(eq(tasks.refType, 'ASN'), eq(tasks.refId, asns.id), eq(tasks.type, 'PUTAWAY'), eq(tasks.status, 'COMPLETED')))
      .where(and(base, sql`${asns.arrivedAt} is not null`))
      .groupBy(asns.id, asns.asnNumber, asns.arrivedAt);
    const hours = d2s.map((r) => Number(r.hours)).filter((h) => h >= 0).sort((a, b) => a - b);
    if (hours.length >= 5) {
      const median = hours[Math.floor(hours.length / 2)];
      for (const r of d2s) {
        const h = Number(r.hours);
        if (h > Math.max(median * 2, 8)) {
          findings.push({ code: 'DOCK_TO_STOCK_OUTLIER', severity: h > median * 4 ? 'MEDIUM' : 'LOW', title: `${r.asnNumber}: dock-to-stock ${h.toFixed(1)}h (median ${median.toFixed(1)}h)`, detail: 'Putaway completed far later than typical; check staging congestion or task assignment.', refType: 'ASN', refId: r.asnId, refNumber: r.asnNumber, metrics: { hours: h, median } });
        }
      }
    }

    // 3. Repeated damage on the same SKU
    const damaged = await tx
      .select({ skuId: receivingDiscrepancies.skuId, skuCode: skus.code, description: skus.description, events: sql<number>`count(*)`.mapWith(Number), qty: sql<string>`coalesce(sum(abs(${receivingDiscrepancies.varianceQty})),0)` })
      .from(receivingDiscrepancies)
      .innerJoin(asns, eq(asns.id, receivingDiscrepancies.asnId))
      .innerJoin(skus, eq(skus.id, receivingDiscrepancies.skuId))
      .where(and(base, eq(receivingDiscrepancies.type, 'DAMAGED')))
      .groupBy(receivingDiscrepancies.skuId, skus.code, skus.description)
      .having(sql`count(*) >= 3`);
    for (const r of damaged) {
      findings.push({ code: 'REPEATED_DAMAGE_SKU', severity: 'MEDIUM', title: `${r.skuCode}: damaged on ${r.events} deliveries (${r.qty} units)`, detail: `${r.description}. Repeated transit damage suggests packaging or handling issues.`, refType: 'SKU', refId: r.skuId, refNumber: r.skuCode, metrics: { events: r.events, qty: r.qty }, suggestedAction: 'Review packaging spec with the supplier; add photo-on-receipt requirement for this SKU.' });
    }

    // 4. Overdue expected ASNs (still EXPECTED/SCHEDULED past expected date)
    const overdue = await tx
      .select({ id: asns.id, asnNumber: asns.asnNumber, expectedAt: asns.expectedAt, supplierName: suppliers.name })
      .from(asns)
      .leftJoin(suppliers, eq(suppliers.id, asns.supplierId))
      .where(and(eq(asns.warehouseId, warehouseId), storerId ? eq(asns.storerId, storerId) : undefined, inArray(asns.status, ['EXPECTED', 'SCHEDULED']), sql`${asns.expectedAt} < now() - interval '2 days'`))
      .limit(50);
    for (const r of overdue) {
      findings.push({ code: 'ASN_OVERDUE', severity: 'LOW', title: `${r.asnNumber} overdue (expected ${String(r.expectedAt).slice(0, 10)})`, detail: `${r.supplierName ?? 'Supplier'} has not delivered; confirm or cancel to keep the inbound plan clean.`, refType: 'ASN', refId: r.id, refNumber: r.asnNumber });
    }

    return {
      title: `Receiving anomalies ${from} to ${to}`,
      summary: findings.length ? `${findings.length} receiving finding(s): ${countBy(findings)}.` : 'No receiving anomalies detected in the window.',
      findings,
      payload: { baselineDiscrepancyRate: baseline, closedAsns: totalAsns, suppliers: bySupplier },
    };
  }

  // ---------------------------------------------------------------- inventory

  private async inventoryAnomalies(tx: Tx, warehouseId: string, storerId: string | undefined, from: string, to: string) {
    const findings: Finding[] = [];
    const fromTs = new Date(`${from}T00:00:00Z`);
    const sf = storerId ? eq(inventoryBalances.storerId, storerId) : undefined;

    // 1. Integrity: allocated > on hand (should be impossible; a CRITICAL if it ever shows)
    const overAlloc = await tx.select({ id: inventoryBalances.id, skuCode: skus.code, onHand: inventoryBalances.qtyOnHand, allocated: inventoryBalances.qtyAllocated }).from(inventoryBalances).innerJoin(skus, eq(skus.id, inventoryBalances.skuId)).where(and(eq(inventoryBalances.warehouseId, warehouseId), sf, sql`${inventoryBalances.qtyAllocated} > ${inventoryBalances.qtyOnHand}`)).limit(20);
    for (const r of overAlloc) findings.push({ code: 'ALLOCATED_EXCEEDS_ONHAND', severity: 'CRITICAL', title: `${r.skuCode}: allocated ${r.allocated} > on hand ${r.onHand}`, detail: 'Balance integrity violated. Investigate the movement ledger for this balance before releasing more orders.', refType: 'BALANCE', refId: r.id });

    // 2. Stock sitting in DOCK / STAGING for > 24h
    const stuck = await tx
      .select({ locCode: locations.code, usage: locations.usage, lpnCount: sql<number>`count(distinct ${inventoryBalances.lpnId})`.mapWith(Number), qty: sql<string>`sum(${inventoryBalances.qtyOnHand})`, oldest: sql<string>`min(${inventoryBalances.lastMovedAt})` })
      .from(inventoryBalances)
      .innerJoin(locations, eq(locations.id, inventoryBalances.locationId))
      .where(and(eq(inventoryBalances.warehouseId, warehouseId), sf, inArray(locations.usage, ['DOCK', 'STAGING']), sql`${inventoryBalances.qtyOnHand} > 0`, sql`${inventoryBalances.lastMovedAt} < now() - interval '24 hours'`))
      .groupBy(locations.code, locations.usage);
    for (const r of stuck) findings.push({ code: 'STOCK_STUCK_IN_TRANSIT_AREA', severity: 'MEDIUM', title: `${r.usage} ${r.locCode}: ${r.lpnCount} LPN(s) idle > 24h`, detail: `Qty ${r.qty} has not moved since ${String(r.oldest).slice(0, 16)}. Putaway or loading backlog.`, refType: 'LOCATION', refNumber: r.locCode, metrics: { lpns: r.lpnCount, qty: r.qty }, suggestedAction: 'Create putaway/move tasks or release the stock to the load.' });

    // 3. Adjustment hot spots (SKUs with >= 3 posted adjustments in window)
    const adj = await tx
      .select({ skuId: inventoryMovements.skuId, skuCode: skus.code, events: sql<number>`count(*)`.mapWith(Number), net: sql<string>`sum(case when ${inventoryMovements.type} = 'ADJUST_OUT' or (${inventoryMovements.type} = 'COUNT_ADJUST' and ${inventoryMovements.toLocationId} is null) then -${inventoryMovements.qty} else ${inventoryMovements.qty} end)` })
      .from(inventoryMovements)
      .innerJoin(skus, eq(skus.id, inventoryMovements.skuId))
      .where(and(eq(inventoryMovements.warehouseId, warehouseId), storerId ? eq(inventoryMovements.storerId, storerId) : undefined, inArray(inventoryMovements.type, ['ADJUST_IN', 'ADJUST_OUT', 'COUNT_ADJUST']), gte(inventoryMovements.performedAt, fromTs)))
      .groupBy(inventoryMovements.skuId, skus.code)
      .having(sql`count(*) >= 3`);
    for (const r of adj) findings.push({ code: 'ADJUSTMENT_HOTSPOT', severity: r.events >= 6 ? 'HIGH' : 'MEDIUM', title: `${r.skuCode}: ${r.events} adjustments (net ${r.net})`, detail: 'Frequent adjustments point to a process problem (wrong pack conversion, unrecorded moves, pilferage).', refType: 'SKU', refId: r.skuId, refNumber: r.skuCode, metrics: { events: r.events, net: r.net }, suggestedAction: 'Schedule a targeted cycle count and review receiving/pick procedures for this SKU.' });

    // 4. Count variance concentration by zone
    const zoneVar = await tx
      .select({ zoneCode: zones.code, lines: sql<number>`count(*)`.mapWith(Number), variances: sql<number>`count(*) filter (where coalesce(${cycleCountLines.varianceQty},0) <> 0)`.mapWith(Number) })
      .from(cycleCountLines)
      .innerJoin(cycleCounts, eq(cycleCounts.id, cycleCountLines.countId))
      .innerJoin(locations, eq(locations.id, cycleCountLines.locationId))
      .leftJoin(zones, eq(zones.id, locations.zoneId))
      .where(and(eq(cycleCounts.warehouseId, warehouseId), gte(cycleCountLines.countedAt, fromTs), inArray(cycleCountLines.status, ['COUNTED', 'APPROVED', 'VARIANCE_POSTED'])))
      .groupBy(zones.code);
    for (const r of zoneVar) {
      if (r.lines >= 10 && r.variances / r.lines >= 0.15) findings.push({ code: 'ZONE_COUNT_ACCURACY', severity: r.variances / r.lines >= 0.3 ? 'HIGH' : 'MEDIUM', title: `Zone ${r.zoneCode ?? '(none)'}: ${((r.variances / r.lines) * 100).toFixed(0)}% count lines with variance`, detail: `${r.variances} of ${r.lines} counted lines differed from system.`, refType: 'ZONE', refNumber: r.zoneCode ?? undefined, metrics: { lines: r.lines, variances: r.variances }, suggestedAction: 'Increase count frequency in this zone and check location labelling / slotting discipline.' });
    }

    // 5. Dead stock: no movement for 180+ days
    const dead = await tx
      .select({ skuId: inventoryBalances.skuId, skuCode: skus.code, qty: sql<string>`sum(${inventoryBalances.qtyOnHand})`, cbm: sql<string>`coalesce(sum(coalesce(${skus.cbm},0) * ${inventoryBalances.qtyOnHand}),0)`, lastMoved: sql<string>`max(${inventoryBalances.lastMovedAt})` })
      .from(inventoryBalances)
      .innerJoin(skus, eq(skus.id, inventoryBalances.skuId))
      .where(and(eq(inventoryBalances.warehouseId, warehouseId), sf, sql`${inventoryBalances.qtyOnHand} > 0`, sql`${inventoryBalances.lastMovedAt} < now() - interval '180 days'`))
      .groupBy(inventoryBalances.skuId, skus.code)
      .orderBy(sql`3 desc`)
      .limit(25);
    if (dead.length) {
      const cbm = dead.reduce((a, r) => a + Number(r.cbm), 0);
      findings.push({ code: 'DEAD_STOCK', severity: 'LOW', title: `${dead.length} SKU(s) with no movement for 180+ days (${cbm.toFixed(2)} CBM)`, detail: `Top: ${dead.slice(0, 5).map((d) => `${d.skuCode} (${d.qty})`).join(', ')}.`, metrics: { skus: dead.length, cbm, items: dead }, suggestedAction: 'Propose disposal, return-to-supplier or relocation to lower-cost storage to the storer.' });
    }

    // 6. Long-running holds (> 14 days)
    const holds = await tx.select({ id: inventoryHolds.id, placedAt: inventoryHolds.placedAt, scope: inventoryHolds.scope, toStatus: inventoryHolds.toStatus }).from(inventoryHolds).where(and(eq(inventoryHolds.warehouseId, warehouseId), isNull(inventoryHolds.releasedAt), sql`${inventoryHolds.placedAt} < now() - interval '14 days'`)).limit(20);
    for (const h of holds) findings.push({ code: 'STALE_HOLD', severity: 'LOW', title: `Hold since ${String(h.placedAt).slice(0, 10)} (${h.scope} -> ${h.toStatus})`, detail: 'Stock on hold for more than 14 days blocks ATP and may be billable storage with no decision.', refType: 'HOLD', refId: h.id, suggestedAction: 'Decide: release, scrap or return.' });

    // 7. Expired stock still AVAILABLE
    const expired = await tx.select({ skuCode: skus.code, qty: sql<string>`sum(${inventoryBalances.qtyOnHand})`, lots: sql<number>`count(*)`.mapWith(Number) }).from(inventoryBalances).innerJoin(skus, eq(skus.id, inventoryBalances.skuId)).where(and(eq(inventoryBalances.warehouseId, warehouseId), sf, eq(inventoryBalances.status, 'AVAILABLE'), sql`${inventoryBalances.expiryDate} < ${to}`, sql`${inventoryBalances.qtyOnHand} > 0`)).groupBy(skus.code).limit(20);
    for (const r of expired) findings.push({ code: 'EXPIRED_STOCK_AVAILABLE', severity: 'HIGH', title: `${r.skuCode}: ${r.qty} expired units still AVAILABLE`, detail: `${r.lots} balance line(s) past expiry remain allocatable.`, refType: 'SKU', refNumber: r.skuCode, suggestedAction: 'Place an EXPIRED hold; FEFO allocation does not block already-expired lots unless the status is changed.' });

    return {
      title: `Inventory anomalies ${from} to ${to}`,
      summary: findings.length ? `${findings.length} inventory finding(s): ${countBy(findings)}.` : 'No inventory anomalies detected.',
      findings,
      payload: { adjustmentHotspots: adj.length, deadStockSkus: dead.length },
    };
  }

  // ---------------------------------------------------------------- billing

  private async billingAnomalies(tx: Tx, warehouseId: string, storerId: string | undefined, from: string, to: string) {
    const findings: Finding[] = [];
    const fromTs = new Date(`${from}T00:00:00Z`);

    // 1. Active storers (movements in window) without an ACTIVE contract -> revenue leakage
    const active = await tx
      .select({ storerId: inventoryMovements.storerId, storerName: storers.name, movements: sql<number>`count(*)`.mapWith(Number) })
      .from(inventoryMovements)
      .innerJoin(storers, eq(storers.id, inventoryMovements.storerId))
      .where(and(eq(inventoryMovements.warehouseId, warehouseId), storerId ? eq(inventoryMovements.storerId, storerId) : undefined, gte(inventoryMovements.performedAt, fromTs)))
      .groupBy(inventoryMovements.storerId, storers.name);
    for (const s of active) {
      const [c] = await tx.select({ id: billingContracts.id }).from(billingContracts).where(and(eq(billingContracts.storerId, s.storerId), eq(billingContracts.status, 'ACTIVE'), lte(billingContracts.validFrom, to), sql`(${billingContracts.validTo} is null or ${billingContracts.validTo} >= ${from})`)).limit(1);
      if (!c) findings.push({ code: 'NO_ACTIVE_CONTRACT', severity: 'HIGH', title: `${s.storerName}: ${s.movements} movements but no active billing contract`, detail: 'Handling and storage for this storer are not being captured; every day without a contract is unbilled work.', refType: 'STORER', refId: s.storerId, suggestedAction: 'Create/activate the contract and back-capture charges from the movement register.' });
    }

    // 2. Storers with handling charges but no storage snapshot in the window
    const handling = await tx.select({ storerId: chargeEvents.storerId, storerName: storers.name, n: sql<number>`count(*)`.mapWith(Number) }).from(chargeEvents).innerJoin(storers, eq(storers.id, chargeEvents.storerId)).where(and(eq(chargeEvents.warehouseId, warehouseId), storerId ? eq(chargeEvents.storerId, storerId) : undefined, gte(chargeEvents.chargeDate, from), lte(chargeEvents.chargeDate, to))).groupBy(chargeEvents.storerId, storers.name);
    for (const h of handling) {
      const [snap] = await tx.select({ n: sql<number>`count(*)`.mapWith(Number) }).from(storageSnapshots).where(and(eq(storageSnapshots.warehouseId, warehouseId), eq(storageSnapshots.storerId, h.storerId), gte(storageSnapshots.snapshotDate, from), lte(storageSnapshots.snapshotDate, to)));
      if (!snap.n) findings.push({ code: 'MISSING_STORAGE_SNAPSHOTS', severity: 'MEDIUM', title: `${h.storerName}: handling charges captured but no storage snapshots`, detail: 'Storage billing depends on daily snapshots; the snapshot job may have failed or the storer was added after the last run.', refType: 'STORER', refId: h.storerId, suggestedAction: 'Run the storage snapshot job for the missing dates before invoicing.' });
    }

    // 3. Zero-amount charges where the tariff has a rate
    const zero = await tx.select({ n: sql<number>`count(*)`.mapWith(Number), storerName: storers.name }).from(chargeEvents).innerJoin(storers, eq(storers.id, chargeEvents.storerId)).where(and(eq(chargeEvents.warehouseId, warehouseId), storerId ? eq(chargeEvents.storerId, storerId) : undefined, gte(chargeEvents.chargeDate, from), lte(chargeEvents.chargeDate, to), sql`${chargeEvents.amount} = 0`, sql`${chargeEvents.rate} > 0`, eq(chargeEvents.status, 'PENDING'))).groupBy(storers.name);
    for (const z of zero) if (z.n) findings.push({ code: 'ZERO_AMOUNT_CHARGES', severity: 'MEDIUM', title: `${z.storerName}: ${z.n} charge(s) with zero amount but a positive rate`, detail: 'Quantity resolved to zero (missing CBM/weight on the SKU, or unit mismatch). These will invoice at 0.', suggestedAction: 'Fix SKU dimensions / charge unit and re-capture the events.' });

    // 4. Possible duplicate captures (same ref, tariff, date, quantity)
    const dupes = await tx.select({ refNumber: chargeEvents.refNumber, tariffId: chargeEvents.tariffId, n: sql<number>`count(*)`.mapWith(Number) }).from(chargeEvents).where(and(eq(chargeEvents.warehouseId, warehouseId), storerId ? eq(chargeEvents.storerId, storerId) : undefined, gte(chargeEvents.chargeDate, from), lte(chargeEvents.chargeDate, to), sql`${chargeEvents.status} <> 'WAIVED'`)).groupBy(chargeEvents.refNumber, chargeEvents.tariffId, chargeEvents.chargeDate, chargeEvents.quantity).having(sql`count(*) > 1`).limit(20);
    for (const d of dupes) findings.push({ code: 'DUPLICATE_CHARGE', severity: 'MEDIUM', title: `${d.refNumber ?? '(no ref)'}: ${d.n} identical charge events for one tariff`, detail: 'Same reference, tariff, date and quantity captured more than once.', refType: 'CHARGE', refNumber: d.refNumber ?? undefined, suggestedAction: 'Waive the duplicates before invoicing.' });

    // 5. Invoice total drop > 40% vs previous invoice for the same storer
    const inv = await tx.select({ storerId: invoices.storerId, storerName: storers.name, total: invoices.total, periodEnd: invoices.periodEnd, invoiceNumber: invoices.invoiceNumber }).from(invoices).innerJoin(storers, eq(storers.id, invoices.storerId)).where(and(storerId ? eq(invoices.storerId, storerId) : undefined, sql`${invoices.status} <> 'CANCELLED'`)).orderBy(invoices.storerId, invoices.periodEnd);
    const byStorer = new Map<string, typeof inv>();
    for (const i of inv) byStorer.set(i.storerId, [...(byStorer.get(i.storerId) ?? []), i]);
    for (const [, list] of byStorer) {
      if (list.length < 2) continue;
      const last = list[list.length - 1];
      const prev = list[list.length - 2];
      const a = Number(last.total);
      const b = Number(prev.total);
      if (b > 0 && a < b * 0.6) findings.push({ code: 'INVOICE_DROP', severity: 'LOW', title: `${last.storerName}: ${last.invoiceNumber} is ${((1 - a / b) * 100).toFixed(0)}% below the previous invoice`, detail: `${a.toFixed(2)} vs ${b.toFixed(2)}. Could be real volume decline or missing charge capture.`, refType: 'INVOICE', refNumber: last.invoiceNumber, metrics: { current: a, previous: b } });
    }

    return {
      title: `Billing anomalies ${from} to ${to}`,
      summary: findings.length ? `${findings.length} billing finding(s): ${countBy(findings)}.` : 'No billing anomalies detected.',
      findings,
      payload: { activeStorers: active.length },
    };
  }

  // ---------------------------------------------------------------- slotting

  private async slotting(tx: Tx, warehouseId: string, storerId: string | undefined, from: string, to: string) {
    const findings: Finding[] = [];
    const fromTs = new Date(`${from}T00:00:00Z`);
    const days = Math.max(1, Math.round((new Date(`${to}T00:00:00Z`).getTime() - fromTs.getTime()) / 86400000) + 1);

    // Pick velocity per SKU from completed pick tasks
    const velocity = await tx
      .select({ skuId: tasks.skuId, skuCode: skus.code, description: skus.description, storerId: skus.storerId, picks: sql<number>`count(*)`.mapWith(Number), qty: sql<string>`coalesce(sum(${tasks.qtyDone}),0)` })
      .from(tasks)
      .innerJoin(skus, eq(skus.id, tasks.skuId))
      .where(and(eq(tasks.warehouseId, warehouseId), storerId ? eq(skus.storerId, storerId) : undefined, eq(tasks.type, 'PICK'), eq(tasks.status, 'COMPLETED'), gte(tasks.completedAt, fromTs)))
      .groupBy(tasks.skuId, skus.code, skus.description, skus.storerId)
      .orderBy(sql`count(*) desc`)
      .limit(200);
    const totalPicks = velocity.reduce((a, v) => a + v.picks, 0);

    const slotted = await tx.select({ skuId: skuSlottings.skuId, pickLocationId: skuSlottings.pickLocationId, locCode: locations.code, active: skuSlottings.active }).from(skuSlottings).innerJoin(locations, eq(locations.id, skuSlottings.pickLocationId)).where(eq(skuSlottings.warehouseId, warehouseId));
    const slottedSet = new Set(slotted.filter((s) => s.active).map((s) => s.skuId));

    // Free pick faces: PICK usage, ACTIVE, nothing on hand, no active slotting
    const freeFaces = await tx
      .select({ id: locations.id, code: locations.code, pickSequence: locations.pickSequence })
      .from(locations)
      .where(and(eq(locations.warehouseId, warehouseId), eq(locations.usage, 'PICK'), eq(locations.status, 'ACTIVE'), sql`not exists (select 1 from ${inventoryBalances} b where b.location_id = ${qcol(locations.id)} and b.qty_on_hand > 0)`, sql`not exists (select 1 from ${skuSlottings} s where s.pick_location_id = ${qcol(locations.id)} and s.active)`))
      .orderBy(locations.pickSequence, locations.code);
    const faces = [...freeFaces];

    // ABC split on cumulative pick share: A = top 80%, B = next 15%, C = rest
    let cum = 0;
    const recommendations: Record<string, unknown>[] = [];
    for (const v of velocity) {
      // Class by cumulative share *before* this SKU, so the top item is always A.
      const share = totalPicks ? cum / totalPicks : 0;
      cum += v.picks;
      const cls = share < 0.8 ? 'A' : share < 0.95 ? 'B' : 'C';
      if (cls === 'C' || slottedSet.has(v.skuId!)) continue;
      const [pack] = await tx.select({ qtyInBase: skuPacks.qtyInBase }).from(skuPacks).where(and(eq(skuPacks.skuId, v.skuId!), eq(skuPacks.level, 'CASE'))).limit(1);
      const caseQty = Math.max(1, Number(pack?.qtyInBase ?? 1));
      const daily = Number(v.qty) / days;
      const maxQty = Math.max(caseQty, Math.ceil((daily * 5) / caseQty) * caseQty); // 5 days of cover, in whole cases
      const minQty = Math.max(1, Math.ceil(daily * 2)); // 2 days of cover
      const face = faces.shift() ?? null;
      recommendations.push({ skuId: v.skuId, skuCode: v.skuCode, description: v.description, abcClass: cls, picksInWindow: v.picks, qtyInWindow: v.qty, dailyQty: Number(daily.toFixed(2)), warehouseId, pickLocationId: face?.id ?? null, pickLocationCode: face?.code ?? null, minQty: String(minQty), maxQty: String(maxQty), replenishPackLevel: 'CASE', apply: !!face });
      findings.push({ code: 'SLOT_FAST_MOVER', severity: cls === 'A' ? 'MEDIUM' : 'LOW', title: `${v.skuCode} (class ${cls}, ${v.picks} picks) has no pick face${face ? ` -> ${face.code}` : ' and no free face is available'}`, detail: `${v.description}. Picking from reserve costs travel time on every order line.`, refType: 'SKU', refId: v.skuId ?? undefined, refNumber: v.skuCode, metrics: { picks: v.picks, qty: v.qty, min: minQty, max: maxQty } });
    }

    // Slotted SKUs with no picks in the window: free the face
    const idle = slotted.filter((s) => s.active && !velocity.some((v) => v.skuId === s.skuId));
    for (const s of idle.slice(0, 30)) {
      const [sku] = await tx.select({ code: skus.code }).from(skus).where(eq(skus.id, s.skuId)).limit(1);
      findings.push({ code: 'SLOT_IDLE_PICK_FACE', severity: 'LOW', title: `${sku?.code ?? s.skuId} occupies pick face ${s.locCode} with zero picks in ${days} days`, detail: 'Consider deactivating the slotting and releasing the face to a faster mover.', refType: 'SKU', refId: s.skuId, refNumber: sku?.code, suggestedAction: 'Deactivate slotting (Master data > Slotting).' });
    }

    return {
      title: `Slotting recommendations (${days}-day pick velocity)`,
      summary: recommendations.length ? `${recommendations.length} fast mover(s) without a pick face; ${recommendations.filter((r) => r.apply).length} can be slotted into free faces now. ${idle.length} idle pick face(s).` : 'All fast movers already have pick faces.',
      findings,
      payload: { days, totalPicks, freeFacesRemaining: faces.length, recommendations },
    };
  }

  // ---------------------------------------------------------------- forecast

  private async workloadForecast(tx: Tx, warehouseId: string, storerId: string | undefined, today: string, horizonDays: number) {
    const findings: Finding[] = [];
    const histFrom = new Date(`${addDays(today, -28)}T00:00:00Z`);

    // Known demand: expected ASN lines by date, open order lines by requested ship date
    const inbound = await tx
      .select({ day: sql<string>`${asns.expectedAt}::date`, asnCount: sql<number>`count(distinct ${asns.id})`.mapWith(Number), lines: sql<number>`count(${asnLines.id})`.mapWith(Number), qty: sql<string>`coalesce(sum(${asnLines.expectedQty}),0)` })
      .from(asns)
      .leftJoin(asnLines, eq(asnLines.asnId, asns.id))
      .where(and(eq(asns.warehouseId, warehouseId), storerId ? eq(asns.storerId, storerId) : undefined, inArray(asns.status, ['EXPECTED', 'SCHEDULED', 'ARRIVED']), sql`${asns.expectedAt}::date between ${today} and ${addDays(today, horizonDays)}`))
      .groupBy(sql`1`);
    const outbound = await tx
      .select({ day: sql<string>`coalesce(${outboundOrders.requestedShipDate}, ${today}::date)`, orders: sql<number>`count(distinct ${outboundOrders.id})`.mapWith(Number), lines: sql<number>`count(${outboundOrderLines.id})`.mapWith(Number), qty: sql<string>`coalesce(sum(${outboundOrderLines.orderedQty}),0)` })
      .from(outboundOrders)
      .leftJoin(outboundOrderLines, eq(outboundOrderLines.orderId, outboundOrders.id))
      .where(and(eq(outboundOrders.warehouseId, warehouseId), storerId ? eq(outboundOrders.storerId, storerId) : undefined, inArray(outboundOrders.status, ['OPEN', 'ON_HOLD', 'PARTIALLY_ALLOCATED', 'ALLOCATED', 'RELEASED', 'PICKING']), sql`coalesce(${outboundOrders.requestedShipDate}, ${today}::date) <= ${addDays(today, horizonDays)}`))
      .groupBy(sql`1`);

    // Baseline: trailing 28-day averages by weekday of completed task lines, and observed productivity
    const hist = await tx
      .select({ dow: sql<number>`extract(dow from ${tasks.completedAt})`.mapWith(Number), type: tasks.type, n: sql<number>`count(*)`.mapWith(Number), minutes: sql<string>`coalesce(sum(extract(epoch from (${tasks.completedAt} - coalesce(${tasks.startedAt}, ${tasks.assignedAt}, ${tasks.createdAt}))) / 60.0),0)` })
      .from(tasks)
      .where(and(eq(tasks.warehouseId, warehouseId), eq(tasks.status, 'COMPLETED'), gte(tasks.completedAt, histFrom)))
      .groupBy(sql`1`, tasks.type);
    const rates: Record<string, number> = { ...DEFAULT_RATES };
    const byType = new Map<string, { n: number; minutes: number }>();
    for (const h of hist) {
      const cur = byType.get(h.type) ?? { n: 0, minutes: 0 };
      byType.set(h.type, { n: cur.n + h.n, minutes: cur.minutes + Number(h.minutes) });
    }
    for (const [type, v] of byType) if (v.n >= 20 && v.minutes > 0) rates[type] = Math.max(1, Math.min(200, v.n / (v.minutes / 60)));
    const weeks = 4;
    const baselineByDow = (type: string, dow: number) => (hist.filter((h) => h.type === type && h.dow === dow).reduce((a, h) => a + h.n, 0)) / weeks;

    const days: Record<string, unknown>[] = [];
    let totalHours = 0;
    let peak = { day: today, hours: 0 };
    for (let i = 0; i <= horizonDays; i++) {
      const day = addDays(today, i);
      const dow = new Date(`${day}T00:00:00Z`).getUTCDay();
      const inb = inbound.find((r) => r.day === day);
      const outb = outbound.find((r) => r.day === day);
      const knownReceive = inb?.lines ?? 0;
      const knownPick = outb?.lines ?? 0;
      // Baseline picks that are not yet in the system (orders typically arrive 0-2 days before ship); only used beyond the known-order horizon.
      const basePick = i > 1 ? Math.max(0, baselineByDow('PICK', dow) - knownPick) : 0;
      const baseReceive = i > 2 ? Math.max(0, baselineByDow('RECEIVE', dow) - knownReceive) : 0;
      const receiveLines = knownReceive + baseReceive;
      const pickLines = knownPick + basePick;
      const hours = {
        receive: receiveLines / rates.RECEIVE,
        putaway: receiveLines / rates.PUTAWAY,
        pick: pickLines / rates.PICK,
        pack: pickLines / rates.PACK,
        load: (outb?.orders ?? 0) * 0.25,
      };
      const total = Object.values(hours).reduce((a, b) => a + b, 0);
      totalHours += total;
      if (total > peak.hours) peak = { day, hours: total };
      days.push({ day, dow, inbound: { asns: inb?.asnCount ?? 0, lines: knownReceive, qty: inb?.qty ?? '0', baselineLines: Math.round(baseReceive) }, outbound: { orders: outb?.orders ?? 0, lines: knownPick, qty: outb?.qty ?? '0', baselineLines: Math.round(basePick) }, hours: round(hours), totalHours: Number(total.toFixed(1)), headcount8h: Number((total / 8).toFixed(1)) });
    }

    const avgHours = totalHours / (horizonDays + 1);
    if (peak.hours > avgHours * 1.6 && peak.hours > 8) findings.push({ code: 'WORKLOAD_PEAK', severity: 'MEDIUM', title: `Peak on ${peak.day}: ${peak.hours.toFixed(0)} labour hours (${(peak.hours / 8).toFixed(1)} FTE) vs ${avgHours.toFixed(0)} average`, detail: 'Plan overtime, temp labour or reschedule dock appointments to flatten the peak.', metrics: peak });
    const backlog = outbound.filter((r) => r.day < today).reduce((a, r) => a + r.orders, 0);
    if (backlog) findings.push({ code: 'OUTBOUND_BACKLOG', severity: backlog > 20 ? 'HIGH' : 'MEDIUM', title: `${backlog} open order(s) already past requested ship date`, detail: 'These are counted in today\'s workload; service level is at risk.', metrics: { backlog } });

    return {
      title: `Workload forecast ${today} +${horizonDays}d`,
      summary: `${totalHours.toFixed(0)} labour hours over ${horizonDays + 1} days (avg ${(avgHours / 8).toFixed(1)} FTE/day, peak ${peak.day} at ${(peak.hours / 8).toFixed(1)} FTE).${backlog ? ` Backlog: ${backlog} late orders.` : ''}`,
      findings,
      payload: { horizonDays, rates: round(rates), days, totals: { hours: Number(totalHours.toFixed(1)), avgFtePerDay: Number((avgHours / 8).toFixed(2)), peak } },
    };
  }

  // ---------------------------------------------------------------- narrative

  private async narrative(kind: RunAnalysisDto['kind'], findings: Finding[], payload: Record<string, unknown>) {
    if (!this.llm.enabled) return null;
    const ctx = requireRequestContext();
    const feature = featureFor(kind);
    if (ctx.actorType !== 'PLATFORM_ADMIN' && !ctx.features.includes(feature)) return null;
    if (!findings.length && kind !== 'WORKLOAD_FORECAST') return null;
    const compact = findings.slice(0, 25).map((f) => ({ code: f.code, severity: f.severity, title: f.title, action: f.suggestedAction }));
    const res = await this.llm.complete(
      {
        system: 'You are an operations analyst for a 3PL warehouse. Write a concise executive summary (max 120 words, plain text, no markdown, no bullet characters) of the findings for a warehouse manager: what matters most, likely root causes, and the first two actions. Use only the facts given; never invent numbers.',
        messages: [{ role: 'user', content: JSON.stringify({ analysis: kind, findings: compact, data: kind === 'WORKLOAD_FORECAST' ? (payload as { totals?: unknown }).totals : undefined }) }],
        maxTokens: 400,
        temperature: 0.2,
      },
      { feature, purpose: `narrative.${kind.toLowerCase()}` },
    );
    return { text: res.text, model: res.model, usage: res.usage };
  }
}

function featureFor(kind: RunAnalysisDto['kind']): 'ai.anomaly_detection' | 'ai.slotting' | 'ai.forecast' {
  if (kind === 'SLOTTING_RECOMMENDATION') return 'ai.slotting';
  if (kind === 'WORKLOAD_FORECAST') return 'ai.forecast';
  return 'ai.anomaly_detection';
}

function countBy(fs: Finding[]): string {
  const m = new Map<string, number>();
  for (const f of fs) m.set(f.severity, (m.get(f.severity) ?? 0) + 1);
  return [...m.entries()].map(([k, v]) => `${v} ${k}`).join(', ');
}

function round(o: Record<string, number>): Record<string, number> {
  return Object.fromEntries(Object.entries(o).map(([k, v]) => [k, Number(v.toFixed(2))]));
}