import { Injectable } from '@nestjs/common';
import { and, eq, gte, lte, sql, inArray, AnyColumn } from 'drizzle-orm';
import { DbService } from '@/common/db/db.service';
import { asns, outboundOrders, tasks, inventoryBalances, locations, zones, skus, storers, inventoryMovements, cycleCountLines, cycleCounts, users, receivingDiscrepancies } from '@/db/schema';
import { assertWarehouseAccess, resolveStorerScope, serialize, todayIso, addDays, qcol } from '@/common/util';

/**
 * Operational KPIs and reports. Everything is computed from the ledger and
 * documents so the numbers always reconcile with the transactions behind them.
 */
@Injectable()
export class ReportsService {
  constructor(private readonly db: DbService) {}

  /** Warehouse control-tower summary. */
  dashboard(warehouseId: string) {
    assertWarehouseAccess(warehouseId);
    const storerId = resolveStorerScope();
    return this.db.run(async (tx) => {
      const today = todayIso();
      const since30 = new Date(Date.now() - 30 * 86400000);
      const storerFilter = (col: AnyColumn) => (storerId ? eq(col, storerId) : undefined);

      const inbound = await tx.select({ status: asns.status, count: sql<number>`count(*)`.mapWith(Number), qty: sql<string>`coalesce(sum(${asns.totalExpectedQty}),0)` }).from(asns).where(and(eq(asns.warehouseId, warehouseId), storerFilter(asns.storerId), inArray(asns.status, ['EXPECTED', 'SCHEDULED', 'ARRIVED', 'RECEIVING', 'RECEIVED']))).groupBy(asns.status);
      const inboundClosedToday = await tx.select({ count: sql<number>`count(*)`.mapWith(Number) }).from(asns).where(and(eq(asns.warehouseId, warehouseId), storerFilter(asns.storerId), eq(asns.status, 'CLOSED'), sql`${asns.closedAt} >= ${today}::date`));
      const outbound = await tx.select({ status: outboundOrders.status, count: sql<number>`count(*)`.mapWith(Number) }).from(outboundOrders).where(and(eq(outboundOrders.warehouseId, warehouseId), storerFilter(outboundOrders.storerId), inArray(outboundOrders.status, ['OPEN', 'ON_HOLD', 'PARTIALLY_ALLOCATED', 'ALLOCATED', 'RELEASED', 'PICKING', 'PICKED', 'PACKED', 'STAGED', 'LOADED']))).groupBy(outboundOrders.status);
      const shippedToday = await tx.select({ count: sql<number>`count(*)`.mapWith(Number), qty: sql<string>`coalesce(sum(${outboundOrders.totalShippedQty}),0)` }).from(outboundOrders).where(and(eq(outboundOrders.warehouseId, warehouseId), storerFilter(outboundOrders.storerId), inArray(outboundOrders.status, ['SHIPPED', 'SHORT_CLOSED']), sql`${outboundOrders.shippedAt} >= ${today}::date`));
      const lateOrders = await tx.select({ count: sql<number>`count(*)`.mapWith(Number) }).from(outboundOrders).where(and(eq(outboundOrders.warehouseId, warehouseId), storerFilter(outboundOrders.storerId), sql`${outboundOrders.requestedShipDate} < ${today}`, inArray(outboundOrders.status, ['OPEN', 'PARTIALLY_ALLOCATED', 'ALLOCATED', 'RELEASED', 'PICKING', 'PICKED', 'PACKED', 'STAGED'])));
      const taskRows = await tx.select({ type: tasks.type, status: tasks.status, count: sql<number>`count(*)`.mapWith(Number) }).from(tasks).where(and(eq(tasks.warehouseId, warehouseId), inArray(tasks.status, ['PENDING', 'ASSIGNED', 'IN_PROGRESS', 'EXCEPTION']))).groupBy(tasks.type, tasks.status);

      const stock = await tx
        .select({ status: inventoryBalances.status, qty: sql<string>`coalesce(sum(${inventoryBalances.qtyOnHand}),0)`, cbm: sql<string>`coalesce(sum(coalesce(${skus.cbm},0) * ${inventoryBalances.qtyOnHand}),0)`, pallets: sql<number>`count(distinct ${inventoryBalances.lpnId})`.mapWith(Number), skus: sql<number>`count(distinct ${inventoryBalances.skuId})`.mapWith(Number) })
        .from(inventoryBalances)
        .innerJoin(skus, eq(skus.id, inventoryBalances.skuId))
        .where(and(eq(inventoryBalances.warehouseId, warehouseId), storerFilter(inventoryBalances.storerId), sql`${inventoryBalances.qtyOnHand} > 0`))
        .groupBy(inventoryBalances.status);
      const [util] = await tx
        .select({ total: sql<number>`count(*)`.mapWith(Number), occupied: sql<number>`count(*) filter (where exists (select 1 from ${inventoryBalances} b where b.location_id = ${qcol(locations.id)} and b.qty_on_hand > 0))`.mapWith(Number) })
        .from(locations)
        .where(and(eq(locations.warehouseId, warehouseId), inArray(locations.usage, ['RESERVE', 'PICK']), eq(locations.status, 'ACTIVE')));
      const expiring = await tx
        .select({ bucket: sql<string>`case when ${inventoryBalances.expiryDate} < ${today} then 'EXPIRED' when ${inventoryBalances.expiryDate} <= ${addDays(today, 30)} then '30' when ${inventoryBalances.expiryDate} <= ${addDays(today, 60)} then '60' when ${inventoryBalances.expiryDate} <= ${addDays(today, 90)} then '90' else 'LATER' end`, qty: sql<string>`sum(${inventoryBalances.qtyOnHand})`, lines: sql<number>`count(*)`.mapWith(Number) })
        .from(inventoryBalances)
        .where(and(eq(inventoryBalances.warehouseId, warehouseId), storerFilter(inventoryBalances.storerId), sql`${inventoryBalances.expiryDate} is not null`, sql`${inventoryBalances.qtyOnHand} > 0`))
        .groupBy(sql`1`);

      // KPIs (last 30 days)
      const [dockToStock] = await tx.select({ hours: sql<string | null>`avg(extract(epoch from (t.completed_at - a.arrived_at)) / 3600.0)` }).from(sql`${asns} a join ${tasks} t on t.ref_type = 'ASN' and t.ref_id = a.id and t.type = 'PUTAWAY' and t.status = 'COMPLETED'`).where(and(sql`a.warehouse_id = ${warehouseId}`, sql`a.arrived_at >= ${since30.toISOString()}`));
      const [orderCycle] = await tx.select({ hours: sql<string | null>`avg(extract(epoch from (${outboundOrders.shippedAt} - ${outboundOrders.createdAt})) / 3600.0)` }).from(outboundOrders).where(and(eq(outboundOrders.warehouseId, warehouseId), storerFilter(outboundOrders.storerId), inArray(outboundOrders.status, ['SHIPPED', 'SHORT_CLOSED']), gte(outboundOrders.shippedAt, since30)));
      const [pickAcc] = await tx.select({ total: sql<number>`count(*)`.mapWith(Number), short: sql<number>`count(*) filter (where ${tasks.exceptionCode} = 'SHORT_PICK')`.mapWith(Number) }).from(tasks).where(and(eq(tasks.warehouseId, warehouseId), eq(tasks.type, 'PICK'), eq(tasks.status, 'COMPLETED'), gte(tasks.completedAt, since30)));
      const [invAcc] = await tx.select({ total: sql<number>`count(*)`.mapWith(Number), ok: sql<number>`count(*) filter (where coalesce(${cycleCountLines.varianceQty},0) = 0)`.mapWith(Number) }).from(cycleCountLines).innerJoin(cycleCounts, eq(cycleCounts.id, cycleCountLines.countId)).where(and(eq(cycleCounts.warehouseId, warehouseId), inArray(cycleCountLines.status, ['APPROVED', 'VARIANCE_POSTED', 'COUNTED']), gte(cycleCountLines.countedAt, since30)));
      const [recvAcc] = await tx.select({ asnsClosed: sql<number>`count(distinct ${asns.id})`.mapWith(Number), withDiscrepancy: sql<number>`count(distinct ${receivingDiscrepancies.asnId})`.mapWith(Number) }).from(asns).leftJoin(receivingDiscrepancies, eq(receivingDiscrepancies.asnId, asns.id)).where(and(eq(asns.warehouseId, warehouseId), storerFilter(asns.storerId), eq(asns.status, 'CLOSED'), gte(asns.closedAt, since30)));
      const [onTime] = await tx.select({ total: sql<number>`count(*)`.mapWith(Number), onTime: sql<number>`count(*) filter (where ${outboundOrders.requestedShipDate} is null or ${outboundOrders.shippedAt}::date <= ${outboundOrders.requestedShipDate})`.mapWith(Number) }).from(outboundOrders).where(and(eq(outboundOrders.warehouseId, warehouseId), storerFilter(outboundOrders.storerId), inArray(outboundOrders.status, ['SHIPPED', 'SHORT_CLOSED']), gte(outboundOrders.shippedAt, since30)));

      return serialize({
        asOf: new Date(),
        inbound: { byStatus: inbound, closedToday: inboundClosedToday[0].count },
        outbound: { byStatus: outbound, shippedToday: shippedToday[0], late: lateOrders[0].count },
        tasks: taskRows,
        stock: { byStatus: stock, utilizationPct: util.total ? Math.round((util.occupied / util.total) * 1000) / 10 : 0, locationsTotal: util.total, locationsOccupied: util.occupied, expiring },
        kpis: {
          dockToStockHours: dockToStock.hours ? Number(Number(dockToStock.hours).toFixed(1)) : null,
          orderCycleHours: orderCycle.hours ? Number(Number(orderCycle.hours).toFixed(1)) : null,
          pickAccuracyPct: pickAcc.total ? Number((((pickAcc.total - pickAcc.short) / pickAcc.total) * 100).toFixed(2)) : null,
          inventoryAccuracyPct: invAcc.total ? Number(((invAcc.ok / invAcc.total) * 100).toFixed(2)) : null,
          receivingAccuracyPct: recvAcc.asnsClosed ? Number((((recvAcc.asnsClosed - recvAcc.withDiscrepancy) / recvAcc.asnsClosed) * 100).toFixed(2)) : null,
          onTimeShipPct: onTime.total ? Number(((onTime.onTime / onTime.total) * 100).toFixed(2)) : null,
          window: '30d',
        },
      });
    });
  }

  stockAging(warehouseId: string, storerId?: string) {
    assertWarehouseAccess(warehouseId);
    const scope = resolveStorerScope(storerId);
    return this.db.run(async (tx) => {
      const rows = await tx
        .select({ storerCode: storers.code, skuCode: skus.code, description: skus.description, bucket: sql<string>`case when ${inventoryBalances.firstReceivedAt} > now() - interval '30 days' then '0-30' when ${inventoryBalances.firstReceivedAt} > now() - interval '60 days' then '31-60' when ${inventoryBalances.firstReceivedAt} > now() - interval '90 days' then '61-90' when ${inventoryBalances.firstReceivedAt} > now() - interval '180 days' then '91-180' else '180+' end`, qty: sql<string>`sum(${inventoryBalances.qtyOnHand})`, cbm: sql<string>`sum(coalesce(${skus.cbm},0) * ${inventoryBalances.qtyOnHand})` })
        .from(inventoryBalances)
        .innerJoin(skus, eq(skus.id, inventoryBalances.skuId))
        .innerJoin(storers, eq(storers.id, inventoryBalances.storerId))
        .where(and(eq(inventoryBalances.warehouseId, warehouseId), scope ? eq(inventoryBalances.storerId, scope) : undefined, sql`${inventoryBalances.qtyOnHand} > 0`))
        .groupBy(storers.code, skus.code, skus.description, sql`4`)
        .orderBy(storers.code, skus.code);
      return serialize(rows);
    });
  }

  expiry(warehouseId: string, days: number, storerId?: string) {
    assertWarehouseAccess(warehouseId);
    const scope = resolveStorerScope(storerId);
    return this.db.run(async (tx) => {
      const rows = await tx
        .select({ storerCode: storers.code, skuCode: skus.code, description: skus.description, lotNumber: inventoryBalances.lotNumber, expiryDate: inventoryBalances.expiryDate, locationCode: locations.code, status: inventoryBalances.status, qty: inventoryBalances.qtyOnHand, daysLeft: sql<number>`(${inventoryBalances.expiryDate} - current_date)`.mapWith(Number) })
        .from(inventoryBalances)
        .innerJoin(skus, eq(skus.id, inventoryBalances.skuId))
        .innerJoin(storers, eq(storers.id, inventoryBalances.storerId))
        .innerJoin(locations, eq(locations.id, inventoryBalances.locationId))
        .where(and(eq(inventoryBalances.warehouseId, warehouseId), scope ? eq(inventoryBalances.storerId, scope) : undefined, sql`${inventoryBalances.qtyOnHand} > 0`, sql`${inventoryBalances.expiryDate} <= current_date + ${days}::int`))
        .orderBy(inventoryBalances.expiryDate);
      return serialize(rows);
    });
  }

  /** In/out register per day (quantities and CBM) for a storer: the basis of day-wise in/out statements. */
  movementRegister(warehouseId: string, from: string, to: string, storerId?: string) {
    assertWarehouseAccess(warehouseId);
    const scope = resolveStorerScope(storerId);
    return this.db.run(async (tx) => {
      const rows = await tx
        .select({ day: sql<string>`(${inventoryMovements.performedAt} at time zone 'UTC')::date`, storerCode: storers.code, type: inventoryMovements.type, qty: sql<string>`sum(${inventoryMovements.qty})`, cbm: sql<string>`coalesce(sum(${inventoryMovements.cbm}),0)`, count: sql<number>`count(*)`.mapWith(Number) })
        .from(inventoryMovements)
        .innerJoin(storers, eq(storers.id, inventoryMovements.storerId))
        .where(and(eq(inventoryMovements.warehouseId, warehouseId), scope ? eq(inventoryMovements.storerId, scope) : undefined, gte(inventoryMovements.performedAt, new Date(`${from}T00:00:00Z`)), lte(inventoryMovements.performedAt, new Date(`${to}T23:59:59Z`)), inArray(inventoryMovements.type, ['RECEIPT', 'RETURN_RECEIPT', 'RECEIPT_REVERSAL', 'SHIP', 'ADJUST_IN', 'ADJUST_OUT', 'COUNT_ADJUST', 'TRANSFER_IN', 'TRANSFER_OUT'])))
        .groupBy(sql`1`, storers.code, inventoryMovements.type)
        .orderBy(sql`1`, storers.code);
      return serialize(rows);
    });
  }

  productivity(warehouseId: string, from: string, to: string) {
    assertWarehouseAccess(warehouseId);
    return this.db.run(async (tx) => {
      const rows = await tx
        .select({ day: sql<string>`${tasks.completedAt}::date`, userName: users.name, type: tasks.type, tasksDone: sql<number>`count(*)`.mapWith(Number), qty: sql<string>`coalesce(sum(${tasks.qtyDone}),0)`, avgMinutes: sql<string>`avg(extract(epoch from (${tasks.completedAt} - coalesce(${tasks.startedAt}, ${tasks.assignedAt}, ${tasks.createdAt}))) / 60.0)` })
        .from(tasks)
        .leftJoin(users, eq(users.id, tasks.completedById))
        .where(and(eq(tasks.warehouseId, warehouseId), eq(tasks.status, 'COMPLETED'), gte(tasks.completedAt, new Date(`${from}T00:00:00Z`)), lte(tasks.completedAt, new Date(`${to}T23:59:59Z`))))
        .groupBy(sql`1`, users.name, tasks.type)
        .orderBy(sql`1`, users.name);
      return serialize(rows.map((r) => ({ ...r, avgMinutes: r.avgMinutes ? Number(Number(r.avgMinutes).toFixed(1)) : null })));
    });
  }

  /** Location-level occupancy for the warehouse map / heatmap. */
  occupancy(warehouseId: string) {
    assertWarehouseAccess(warehouseId);
    return this.db.run((tx) =>
      tx
        .select({ zoneCode: zones.code, zoneType: zones.type, total: sql<number>`count(*)`.mapWith(Number), occupied: sql<number>`count(*) filter (where exists (select 1 from ${inventoryBalances} b where b.location_id = ${qcol(locations.id)} and b.qty_on_hand > 0))`.mapWith(Number), blocked: sql<number>`count(*) filter (where ${locations.status} <> 'ACTIVE')`.mapWith(Number) })
        .from(locations)
        .innerJoin(zones, eq(zones.id, locations.zoneId))
        .where(eq(locations.warehouseId, warehouseId))
        .groupBy(zones.code, zones.type)
        .orderBy(zones.code),
    );
  }

  /** CSV export helper used by the controller. */
  toCsv(rows: Record<string, unknown>[]): string {
    if (!rows.length) return '';
    const cols = Object.keys(rows[0]);
    const esc = (v: unknown) => {
      const s = v === null || v === undefined ? '' : v instanceof Date ? v.toISOString() : String(v);
      return /[",\n]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
    };
    return [cols.join(','), ...rows.map((r) => cols.map((c) => esc(r[c])).join(','))].join('\n');
  }

}