import { Injectable } from '@nestjs/common';
import { and, desc, eq, gte, inArray, lte, or, sql } from 'drizzle-orm';
import { CreateOrderDto, OrderLineInputDto, OrderListQuerySchema, PaginationQueryDto, DocSource, UpdateOrderSchema } from '@wms/shared';
import { z } from 'zod';
import { DbService, Tx } from '@/common/db/db.service';
import { outboundOrders, outboundOrderLines, skus, storers, customers, allocations, shipments, cartons, cartonLines, tasks, loads, waves, locations, lpns } from '@/db/schema';
import { AuditService } from '@/common/audit/audit.service';
import { NumberingService } from '@/common/numbering/numbering.service';
import { EventBus } from '@/common/events/event-bus.service';
import { requireRequestContext } from '@/common/context/request-context';
import { DomainError, NotFoundError, ValidationError, ErrorCodes } from '@/common/errors/domain-error';
import { D, ZERO, sum, paginate, offsetOf, assertWarehouseAccess, resolveStorerScope, serialize, qcol } from '@/common/util';
import { LookupService } from '@/modules/master-data/lookup.service';
import { AllocationService } from './allocation.service';

const EDITABLE = ['DRAFT', 'OPEN', 'ON_HOLD'];

@Injectable()
export class OrdersService {
  constructor(
    private readonly db: DbService,
    private readonly lookup: LookupService,
    private readonly numbering: NumberingService,
    private readonly audit: AuditService,
    private readonly events: EventBus,
    private readonly allocation: AllocationService,
  ) {}

  list(q: z.infer<typeof OrderListQuerySchema> & PaginationQueryDto) {
    const storerId = resolveStorerScope(q.storerId);
    return this.db.run(async (tx) => {
      const statuses = q.statuses ? q.statuses.split(',') : q.status ? [q.status] : undefined;
      const where = and(
        q.warehouseId ? eq(outboundOrders.warehouseId, q.warehouseId) : undefined,
        storerId ? eq(outboundOrders.storerId, storerId) : undefined,
        statuses ? inArray(outboundOrders.status, statuses as never) : undefined,
        q.externalRef ? sql`${outboundOrders.externalRef} ilike ${'%' + q.externalRef + '%'}` : undefined,
        q.waveId ? eq(outboundOrders.waveId, q.waveId) : undefined,
        q.loadId ? eq(outboundOrders.loadId, q.loadId) : undefined,
        q.shipFrom ? gte(outboundOrders.requestedShipDate, q.shipFrom) : undefined,
        q.shipTo ? lte(outboundOrders.requestedShipDate, q.shipTo) : undefined,
        q.q ? or(sql`${outboundOrders.orderNumber} ilike ${'%' + q.q + '%'}`, sql`${outboundOrders.externalRef} ilike ${'%' + q.q + '%'}`, sql`${outboundOrders.shipTo}->>'name' ilike ${'%' + q.q + '%'}`) : undefined,
      );
      const [{ count }] = await tx.select({ count: sql<number>`count(*)`.mapWith(Number) }).from(outboundOrders).where(where);
      const rows = await tx
        .select({ o: outboundOrders, storerCode: storers.code, storerName: storers.name, waveNumber: waves.waveNumber, loadNumber: loads.loadNumber, lineCount: sql<number>`(select count(*) from ${outboundOrderLines} l where l.order_id = ${qcol(outboundOrders.id)})`.mapWith(Number) })
        .from(outboundOrders)
        .innerJoin(storers, eq(storers.id, outboundOrders.storerId))
        .leftJoin(waves, eq(waves.id, outboundOrders.waveId))
        .leftJoin(loads, eq(loads.id, outboundOrders.loadId))
        .where(where)
        .orderBy(desc(outboundOrders.createdAt))
        .limit(q.pageSize)
        .offset(offsetOf(q));
      return paginate(rows.map((r) => ({ ...r.o, storerCode: r.storerCode, storerName: r.storerName, waveNumber: r.waveNumber, loadNumber: r.loadNumber, lineCount: r.lineCount })), count, q);
    });
  }

  get(id: string) {
    return this.db.run((tx) => this.getInTx(tx, id));
  }

  async getInTx(tx: Tx, id: string) {
    const [o] = await tx.select().from(outboundOrders).where(eq(outboundOrders.id, id)).limit(1);
    if (!o) throw new NotFoundError('Order', id);
    resolveStorerScope(o.storerId);
    const lines = await tx
      .select({ line: outboundOrderLines, skuCode: skus.code, skuDescription: skus.description, baseUom: skus.baseUom, serialControlled: skus.serialControlled })
      .from(outboundOrderLines)
      .innerJoin(skus, eq(skus.id, outboundOrderLines.skuId))
      .where(eq(outboundOrderLines.orderId, id))
      .orderBy(outboundOrderLines.lineNo);
    const allocs = await tx
      .select({ a: allocations, locationCode: locations.code, lpnNumber: lpns.lpnNumber })
      .from(allocations)
      .innerJoin(locations, eq(locations.id, allocations.locationId))
      .leftJoin(lpns, eq(lpns.id, allocations.lpnId))
      .where(and(eq(allocations.orderId, id), sql`${allocations.status} <> 'CANCELLED'`));
    const ship = await tx.select().from(shipments).where(eq(shipments.orderId, id));
    const shipIds = ship.map((s) => s.id);
    const ctns = shipIds.length ? await tx.select().from(cartons).where(inArray(cartons.shipmentId, shipIds)) : [];
    const ctnLines = ctns.length ? await tx.select().from(cartonLines).where(inArray(cartonLines.cartonId, ctns.map((c) => c.id))) : [];
    const taskRows = await tx.select({ id: tasks.id, taskNumber: tasks.taskNumber, type: tasks.type, status: tasks.status, assignedToId: tasks.assignedToId }).from(tasks).where(and(eq(tasks.refType, 'ORDER'), eq(tasks.refId, id)));
    const [storer] = await tx.select({ code: storers.code, name: storers.name }).from(storers).where(eq(storers.id, o.storerId)).limit(1);
    const [wave] = o.waveId ? await tx.select({ waveNumber: waves.waveNumber, status: waves.status }).from(waves).where(eq(waves.id, o.waveId)).limit(1) : [null];
    const [load] = o.loadId ? await tx.select({ loadNumber: loads.loadNumber, status: loads.status }).from(loads).where(eq(loads.id, o.loadId)).limit(1) : [null];
    return serialize({
      ...o,
      storer,
      wave,
      load,
      lines: lines.map((l) => ({ ...l.line, skuCode: l.skuCode, skuDescription: l.skuDescription, baseUom: l.baseUom, serialControlled: l.serialControlled, allocations: allocs.filter((a) => a.a.orderLineId === l.line.id).map((a) => ({ ...a.a, locationCode: a.locationCode, lpnNumber: a.lpnNumber })) })),
      shipments: ship.map((s) => ({ ...s, cartons: ctns.filter((c) => c.shipmentId === s.id).map((c) => ({ ...c, lines: ctnLines.filter((cl) => cl.cartonId === c.id) })) })),
      tasks: taskRows,
    });
  }

  create(dto: CreateOrderDto, source: DocSource = 'MANUAL', tx?: Tx) {
    assertWarehouseAccess(dto.warehouseId);
    const ctx = requireRequestContext();
    const storerId = resolveStorerScope(dto.storerId)!;
    return this.db.run(
      async (t) => {
        await this.lookup.storer(t, storerId);
        if (dto.externalRef) {
          const [dup] = await t.select({ id: outboundOrders.id, orderNumber: outboundOrders.orderNumber }).from(outboundOrders).where(and(eq(outboundOrders.storerId, storerId), eq(outboundOrders.externalRef, dto.externalRef), sql`${outboundOrders.status} not in ('CANCELLED')`)).limit(1);
          if (dup) throw new DomainError(ErrorCodes.DUPLICATE, `An order already exists for reference ${dto.externalRef} (${dup.orderNumber})`, 409, { orderId: dup.id });
        }
        let customerId = dto.customerId ?? null;
        let shipTo = dto.shipTo;
        if (!customerId && dto.customerCode) {
          const [c] = await t.select().from(customers).where(and(eq(customers.storerId, storerId), eq(customers.code, dto.customerCode))).limit(1);
          if (!c) throw new NotFoundError('Customer', dto.customerCode);
          customerId = c.id;
        }
        if (customerId && !shipTo.name) {
          const [c] = await t.select().from(customers).where(eq(customers.id, customerId)).limit(1);
          if (c) shipTo = { name: c.name, address: c.address as never, contact: (c.contacts as { name?: string }[])[0]?.name, phone: (c.contacts as { phone?: string }[])[0]?.phone, ...shipTo };
        }
        const orderNumber = await this.numbering.next('ORD', t, dto.warehouseId);
        const { lines, customerCode: _cc, ...head } = dto;
        const [o] = await t
          .insert(outboundOrders)
          .values({ tenantId: ctx.tenantId!, ...head, storerId, customerId, shipTo, orderNumber, source, status: 'OPEN', dueDate: head.dueDate ? new Date(head.dueDate) : null, createdById: ctx.userId })
          .returning();
        await this.writeLines(t, o.id, storerId, lines);
        await this.recomputeTotals(t, o.id);
        await this.audit.log({ action: 'order.create', entityType: 'order', entityId: o.id, after: dto }, t);
        await this.events.publish('order.created', { orderId: o.id, orderNumber, externalRef: dto.externalRef, source }, t, { warehouseId: dto.warehouseId, storerId });
        return this.getInTx(t, o.id);
      },
      { tx },
    );
  }

  update(id: string, dto: z.infer<typeof UpdateOrderSchema>) {
    return this.db.run(async (tx) => {
      const [o] = await tx.select().from(outboundOrders).where(eq(outboundOrders.id, id)).limit(1).for('update');
      if (!o) throw new NotFoundError('Order', id);
      resolveStorerScope(o.storerId);
      if (!EDITABLE.includes(o.status)) throw new DomainError(ErrorCodes.INVALID_STATE, `Order is ${o.status}; cancel allocations first`);
      const { lines, customerCode: _cc, ...head } = dto;
      await tx.update(outboundOrders).set({ ...head, dueDate: head.dueDate ? new Date(head.dueDate) : undefined }).where(eq(outboundOrders.id, id));
      if (lines) {
        await tx.delete(outboundOrderLines).where(eq(outboundOrderLines.orderId, id));
        await this.writeLines(tx, id, o.storerId, lines);
      }
      await this.recomputeTotals(tx, id);
      await this.audit.log({ action: 'order.update', entityType: 'order', entityId: id, before: o, after: dto }, tx);
      return this.getInTx(tx, id);
    });
  }

  hold(id: string, reason: string) {
    return this.db.run(async (tx) => {
      const [o] = await tx.select().from(outboundOrders).where(eq(outboundOrders.id, id)).limit(1).for('update');
      if (!o) throw new NotFoundError('Order', id);
      assertWarehouseAccess(o.warehouseId);
      resolveStorerScope(o.storerId);
      if (!['OPEN', 'PARTIALLY_ALLOCATED', 'ALLOCATED'].includes(o.status)) throw new DomainError(ErrorCodes.INVALID_STATE, `Order is ${o.status}`);
      if (o.status !== 'OPEN') await this.allocation.deallocateOrder(tx, id);
      const [row] = await tx.update(outboundOrders).set({ status: 'ON_HOLD', holdReason: reason }).where(eq(outboundOrders.id, id)).returning();
      await this.audit.log({ action: 'order.hold', entityType: 'order', entityId: id, after: { reason } }, tx);
      return serialize(row);
    });
  }

  releaseHold(id: string) {
    return this.db.run(async (tx) => {
      const [o] = await tx.select().from(outboundOrders).where(eq(outboundOrders.id, id)).limit(1).for('update');
      if (!o) throw new NotFoundError('Order', id);
      assertWarehouseAccess(o.warehouseId);
      resolveStorerScope(o.storerId);
      if (o.status !== 'ON_HOLD') throw new DomainError(ErrorCodes.INVALID_STATE, `Order is ${o.status}`);
      const [row] = await tx.update(outboundOrders).set({ status: 'OPEN', holdReason: null }).where(eq(outboundOrders.id, id)).returning();
      await this.audit.log({ action: 'order.release_hold', entityType: 'order', entityId: id }, tx);
      return serialize(row);
    });
  }

  cancel(id: string, reason: string, reasonCodeId?: string) {
    return this.db.run(async (tx) => {
      const [o] = await tx.select().from(outboundOrders).where(eq(outboundOrders.id, id)).limit(1).for('update');
      if (!o) throw new NotFoundError('Order', id);
      assertWarehouseAccess(o.warehouseId);
      resolveStorerScope(o.storerId);
      if (['SHIPPED', 'LOADED', 'CANCELLED'].includes(o.status)) throw new DomainError(ErrorCodes.INVALID_STATE, `Order is ${o.status}`);
      const picked = await tx.select({ q: sql<string>`coalesce(sum(${outboundOrderLines.pickedQty}),0)` }).from(outboundOrderLines).where(eq(outboundOrderLines.orderId, id));
      if (D(picked[0].q).gt(0)) throw new DomainError(ErrorCodes.INVALID_STATE, 'Order has picked stock; unpick / return it to stock before cancelling');
      await this.allocation.deallocateOrder(tx, id);
      await tx.update(tasks).set({ status: 'CANCELLED', exceptionNotes: 'Order cancelled' }).where(and(eq(tasks.refType, 'ORDER'), eq(tasks.refId, id), inArray(tasks.status, ['PENDING', 'ASSIGNED', 'IN_PROGRESS'])));
      await tx.update(outboundOrderLines).set({ status: 'CANCELLED', cancelledQty: sql`${outboundOrderLines.orderedQty}` }).where(eq(outboundOrderLines.orderId, id));
      const [row] = await tx.update(outboundOrders).set({ status: 'CANCELLED', cancelledAt: new Date(), waveId: null, notes: `${o.notes ?? ''}\n[Cancelled] ${reason}`.trim() }).where(eq(outboundOrders.id, id)).returning();
      await this.audit.log({ action: 'order.cancel', entityType: 'order', entityId: id, after: { reason, reasonCodeId } }, tx);
      await this.events.publish('order.cancelled', { orderId: id, orderNumber: o.orderNumber, reason }, tx, { warehouseId: o.warehouseId, storerId: o.storerId });
      return serialize(row);
    });
  }

  // ---- helpers ----
  private async writeLines(tx: Tx, orderId: string, storerId: string, lines: OrderLineInputDto[]) {
    const ctx = requireRequestContext();
    let lineNo = 0;
    const seen = new Set<number>();
    for (const l of lines) {
      lineNo = l.lineNo ?? lineNo + 1;
      if (seen.has(lineNo)) throw new ValidationError(`Duplicate line number ${lineNo}`);
      seen.add(lineNo);
      const sku = l.skuId ? await this.lookup.sku(tx, l.skuId) : await this.lookup.skuByCode(tx, storerId, l.skuCode!);
      if (sku.storerId !== storerId) throw new DomainError(ErrorCodes.WRONG_SKU, `SKU ${sku.code} does not belong to the storer`);
      const { baseQty } = await this.lookup.toBaseQty(tx, sku.id, l.orderedQty, l.packLevel);
      if (l.requestedSerials.length && !D(l.requestedSerials.length).eq(baseQty)) throw new ValidationError(`Line ${lineNo}: ${l.requestedSerials.length} serials for quantity ${baseQty}`);
      await tx.insert(outboundOrderLines).values({
        tenantId: ctx.tenantId!,
        orderId,
        lineNo,
        skuId: sku.id,
        orderedQty: baseQty,
        packLevel: l.packLevel,
        packQty: D(l.orderedQty),
        requestedLot: l.requestedLot ?? null,
        requestedSerials: l.requestedSerials,
        minExpiryDate: l.minExpiryDate ?? null,
        externalLineRef: l.externalLineRef ?? null,
        unitPrice: l.unitPrice ? D(l.unitPrice) : null,
        notes: l.notes ?? null,
      });
    }
  }

  async recomputeTotals(tx: Tx, orderId: string) {
    const lines = await tx.select({ l: outboundOrderLines, cbm: skus.cbm, weight: skus.weightKg }).from(outboundOrderLines).innerJoin(skus, eq(skus.id, outboundOrderLines.skuId)).where(eq(outboundOrderLines.orderId, orderId));
    const totalOrdered = sum(lines.map((x) => x.l.orderedQty));
    const totalShipped = sum(lines.map((x) => x.l.shippedQty));
    const totalCbm = lines.reduce((acc, x) => acc.plus(x.cbm ? D(x.cbm).mul(x.l.orderedQty) : ZERO), ZERO);
    const totalWeight = lines.reduce((acc, x) => acc.plus(x.weight ? D(x.weight).mul(x.l.orderedQty) : ZERO), ZERO);
    await tx.update(outboundOrders).set({ totalOrderedQty: totalOrdered, totalShippedQty: totalShipped, totalCbm, totalWeightKg: totalWeight }).where(eq(outboundOrders.id, orderId));
  }

  /** Recomputes the order status from its lines' progress. Called after allocation, picking, packing, shipping. */
  /** Records an unresolved short pick on the order; packing is blocked until a CSO accepts the short shipment or re-allocates. */
  async flagShort(tx: Tx, orderId: string, info: { lineId: string; skuId: string; shortQty: string; taskId: string }) {
    const [o] = await tx.select({ attributes: outboundOrders.attributes, orderNumber: outboundOrders.orderNumber, warehouseId: outboundOrders.warehouseId, storerId: outboundOrders.storerId }).from(outboundOrders).where(eq(outboundOrders.id, orderId)).limit(1);
    if (!o) return;
    const attrs = { ...(o.attributes ?? {}) } as Record<string, unknown>;
    const shorts = Array.isArray(attrs.shortLines) ? (attrs.shortLines as unknown[]) : [];
    attrs.shortLines = [...shorts, { ...info, at: new Date().toISOString() }];
    attrs.shortPending = true;
    delete attrs.shortAccepted;
    await tx.update(outboundOrders).set({ attributes: attrs }).where(eq(outboundOrders.id, orderId));
    await this.audit.log({ action: 'order.short_pending', entityType: 'order', entityId: orderId, after: info }, tx);
    await this.events.publish('order.short_pending', { orderId, orderNumber: o.orderNumber, ...info }, tx, { warehouseId: o.warehouseId, storerId: o.storerId });
  }

  /** CSO decision: ship what was picked; the unfilled quantity is left open (SHORT_CLOSED at goods issue). */
  acceptShort(id: string, notes?: string) {
    const ctx = requireRequestContext();
    return this.db.run(async (tx) => {
      const [o] = await tx.select().from(outboundOrders).where(eq(outboundOrders.id, id)).limit(1).for('update');
      if (!o) throw new NotFoundError('Order', id);
      assertWarehouseAccess(o.warehouseId);
      resolveStorerScope(o.storerId);
      const attrs = { ...(o.attributes ?? {}) } as Record<string, unknown>;
      if (!attrs.shortPending) throw new DomainError(ErrorCodes.INVALID_STATE, 'Order has no pending short pick');
      attrs.shortPending = false;
      attrs.shortAccepted = { by: ctx.userId, at: new Date().toISOString(), notes: notes ?? null };
      await tx.update(outboundOrders).set({ attributes: attrs }).where(eq(outboundOrders.id, id));
      await this.audit.log({ action: 'order.accept_short', entityType: 'order', entityId: id, after: { notes } }, tx);
      return this.getInTx(tx, id);
    });
  }

  /** Clears the short flag once every line is fully allocated again. */
  async clearShortIfCovered(tx: Tx, orderId: string) {
    const [o] = await tx.select({ attributes: outboundOrders.attributes }).from(outboundOrders).where(eq(outboundOrders.id, orderId)).limit(1);
    if (!o || !(o.attributes as Record<string, unknown>)?.shortPending) return;
    const lines = await tx.select().from(outboundOrderLines).where(and(eq(outboundOrderLines.orderId, orderId), sql`${outboundOrderLines.status} <> 'CANCELLED'`));
    const covered = lines.every((l) => D(l.allocatedQty).gte(D(l.orderedQty).minus(l.cancelledQty)));
    if (covered) {
      const attrs = { ...(o.attributes as Record<string, unknown>) };
      attrs.shortPending = false;
      await tx.update(outboundOrders).set({ attributes: attrs }).where(eq(outboundOrders.id, orderId));
    }
  }

  async refreshStatus(tx: Tx, orderId: string): Promise<string> {
    const [o] = await tx.select().from(outboundOrders).where(eq(outboundOrders.id, orderId)).limit(1);
    if (!o || ['CANCELLED', 'ON_HOLD', 'SHIPPED', 'SHORT_CLOSED', 'LOADED'].includes(o.status)) return o?.status ?? 'CANCELLED';
    const lines = await tx.select().from(outboundOrderLines).where(and(eq(outboundOrderLines.orderId, orderId), sql`${outboundOrderLines.status} <> 'CANCELLED'`));
    const ordered = sum(lines.map((l) => D(l.orderedQty).minus(l.cancelledQty)));
    const allocated = sum(lines.map((l) => l.allocatedQty));
    const picked = sum(lines.map((l) => l.pickedQty));
    const packed = sum(lines.map((l) => l.packedQty));
    let status = o.status;
    if (packed.gt(0) && packed.gte(picked) && picked.gte(allocated) && allocated.gt(0)) status = o.status === 'STAGED' ? 'STAGED' : 'PACKED';
    else if (picked.gt(0) && picked.gte(allocated)) status = 'PICKED';
    else if (picked.gt(0)) status = 'PICKING';
    else if (o.status === 'RELEASED' || o.status === 'PICKING') status = o.status;
    else if (allocated.gte(ordered) && ordered.gt(0)) status = 'ALLOCATED';
    else if (allocated.gt(0)) status = 'PARTIALLY_ALLOCATED';
    else status = 'OPEN';
    if (status !== o.status) {
      const stamps: Partial<typeof outboundOrders.$inferInsert> = { status: status as never };
      if (status === 'ALLOCATED') stamps.allocatedAt = new Date();
      if (status === 'PICKED') stamps.pickedAt = new Date();
      if (status === 'PACKED') stamps.packedAt = new Date();
      await tx.update(outboundOrders).set(stamps).where(eq(outboundOrders.id, orderId));
    }
    return status;
  }
}