import { Injectable } from '@nestjs/common';
import { and, desc, eq, gte, inArray, lte, or, sql } from 'drizzle-orm';
import { CreateAsnDto, UpdateAsnDto, AsnListQuerySchema, ArriveAsnSchema, PaginationQueryDto, AsnLineInputDto, DocSource } from '@wms/shared';
import { z } from 'zod';
import { DbService, Tx } from '@/common/db/db.service';
import { asns, asnLines, skus, storers, suppliers, locations, receiptLines, receivingDiscrepancies, grns, tasks, lpns, dockAppointments } 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';

const EDITABLE = ['DRAFT', 'EXPECTED', 'SCHEDULED'];

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

  list(q: z.infer<typeof AsnListQuerySchema> & 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(asns.warehouseId, q.warehouseId) : undefined,
        storerId ? eq(asns.storerId, storerId) : undefined,
        statuses ? inArray(asns.status, statuses as never) : undefined,
        q.externalRef ? sql`${asns.externalRef} ilike ${'%' + q.externalRef + '%'}` : undefined,
        q.expectedFrom ? gte(asns.expectedAt, new Date(`${q.expectedFrom}T00:00:00Z`)) : undefined,
        q.expectedTo ? lte(asns.expectedAt, new Date(`${q.expectedTo}T23:59:59Z`)) : undefined,
        q.q ? or(sql`${asns.asnNumber} ilike ${'%' + q.q + '%'}`, sql`${asns.externalRef} ilike ${'%' + q.q + '%'}`, sql`${asns.containerNo} ilike ${'%' + q.q + '%'}`) : undefined,
      );
      const [{ count }] = await tx.select({ count: sql<number>`count(*)`.mapWith(Number) }).from(asns).where(where);
      const rows = await tx
        .select({ asn: asns, storerCode: storers.code, storerName: storers.name, supplierName: suppliers.name, lineCount: sql<number>`(select count(*) from ${asnLines} l where l.asn_id = ${qcol(asns.id)})`.mapWith(Number) })
        .from(asns)
        .innerJoin(storers, eq(storers.id, asns.storerId))
        .leftJoin(suppliers, eq(suppliers.id, asns.supplierId))
        .where(where)
        .orderBy(desc(asns.createdAt))
        .limit(q.pageSize)
        .offset(offsetOf(q));
      return paginate(rows.map((r) => ({ ...r.asn, storerCode: r.storerCode, storerName: r.storerName, supplierName: r.supplierName, lineCount: r.lineCount })), count, q);
    });
  }

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

  async getInTx(tx: Tx, id: string) {
    const [a] = await tx.select().from(asns).where(eq(asns.id, id)).limit(1);
    if (!a) throw new NotFoundError('ASN', id);
    resolveStorerScope(a.storerId);
    const lines = await tx
      .select({ line: asnLines, skuCode: skus.code, skuDescription: skus.description, baseUom: skus.baseUom, lotControlled: skus.lotControlled, expiryControlled: skus.expiryControlled, serialControlled: skus.serialControlled })
      .from(asnLines)
      .innerJoin(skus, eq(skus.id, asnLines.skuId))
      .where(eq(asnLines.asnId, id))
      .orderBy(asnLines.lineNo);
    const receipts = await tx
      .select({ r: receiptLines, lpnNumber: lpns.lpnNumber, locationCode: locations.code, skuCode: skus.code })
      .from(receiptLines)
      .leftJoin(lpns, eq(lpns.id, receiptLines.lpnId))
      .innerJoin(locations, eq(locations.id, receiptLines.locationId))
      .innerJoin(skus, eq(skus.id, receiptLines.skuId))
      .where(eq(receiptLines.asnId, id))
      .orderBy(desc(receiptLines.receivedAt));
    const discrepancies = await tx.select({ d: receivingDiscrepancies, skuCode: skus.code }).from(receivingDiscrepancies).innerJoin(skus, eq(skus.id, receivingDiscrepancies.skuId)).where(eq(receivingDiscrepancies.asnId, id));
    const [grn] = await tx.select().from(grns).where(eq(grns.asnId, id)).limit(1);
    const [storer] = await tx.select({ code: storers.code, name: storers.name }).from(storers).where(eq(storers.id, a.storerId)).limit(1);
    const [supplier] = a.supplierId ? await tx.select({ code: suppliers.code, name: suppliers.name }).from(suppliers).where(eq(suppliers.id, a.supplierId)).limit(1) : [null];
    const dock = a.dockLocationId ? await this.lookup.location(tx, a.dockLocationId) : null;
    const putawayTasks = await tx.select({ status: tasks.status, count: sql<number>`count(*)`.mapWith(Number) }).from(tasks).where(and(eq(tasks.refType, 'ASN'), eq(tasks.refId, id), eq(tasks.type, 'PUTAWAY'))).groupBy(tasks.status);
    return serialize({
      ...a,
      storer,
      supplier,
      dockLocationCode: dock?.code ?? null,
      lines: lines.map((l) => ({ ...l.line, skuCode: l.skuCode, skuDescription: l.skuDescription, baseUom: l.baseUom, lotControlled: l.lotControlled, expiryControlled: l.expiryControlled, serialControlled: l.serialControlled })),
      receipts: receipts.map((r) => ({ ...r.r, lpnNumber: r.lpnNumber, locationCode: r.locationCode, skuCode: r.skuCode })),
      discrepancies: discrepancies.map((d) => ({ ...d.d, skuCode: d.skuCode })),
      grn: grn ?? null,
      putawayTasks,
    });
  }

  create(dto: CreateAsnDto, 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: asns.id, asnNumber: asns.asnNumber }).from(asns).where(and(eq(asns.storerId, storerId), eq(asns.externalRef, dto.externalRef), sql`${asns.status} <> 'CANCELLED'`)).limit(1);
          if (dup) throw new DomainError(ErrorCodes.DUPLICATE, `An open ASN already exists for reference ${dto.externalRef} (${dup.asnNumber})`, 409, { asnId: dup.id });
        }
        const asnNumber = await this.numbering.next('ASN', t, dto.warehouseId);
        const { lines, ...head } = dto;
        const [a] = await t
          .insert(asns)
          .values({ tenantId: ctx.tenantId!, ...head, storerId, asnNumber, source, status: 'EXPECTED', expectedAt: head.expectedAt ? new Date(head.expectedAt) : null, createdById: ctx.userId })
          .returning();
        await this.writeLines(t, a.id, storerId, lines);
        await this.recomputeTotals(t, a.id);
        await this.audit.log({ action: 'asn.create', entityType: 'asn', entityId: a.id, after: dto }, t);
        await this.events.publish('asn.created', { asnId: a.id, asnNumber, externalRef: dto.externalRef, source }, t, { warehouseId: dto.warehouseId, storerId });
        return this.getInTx(t, a.id);
      },
      { tx },
    );
  }

  update(id: string, dto: UpdateAsnDto) {
    return this.db.run(async (tx) => {
      const [a] = await tx.select().from(asns).where(eq(asns.id, id)).limit(1).for('update');
      if (!a) throw new NotFoundError('ASN', id);
      resolveStorerScope(a.storerId);
      const { lines, ...head } = dto;
      if (lines && !EDITABLE.includes(a.status)) throw new DomainError(ErrorCodes.INVALID_STATE, `Lines cannot be replaced once receiving has started (status ${a.status})`);
      if (!EDITABLE.includes(a.status) && !['ARRIVED', 'RECEIVING'].includes(a.status)) throw new DomainError(ErrorCodes.INVALID_STATE, `ASN is ${a.status}`);
      await tx.update(asns).set({ ...head, expectedAt: head.expectedAt ? new Date(head.expectedAt) : undefined }).where(eq(asns.id, id));
      if (lines) {
        await tx.delete(asnLines).where(eq(asnLines.asnId, id));
        await this.writeLines(tx, id, a.storerId, lines);
      }
      await this.recomputeTotals(tx, id);
      await this.audit.log({ action: 'asn.update', entityType: 'asn', entityId: id, before: a, after: dto }, tx);
      return this.getInTx(tx, id);
    });
  }

  /** Vehicle at the gate: capture arrival facts, assign dock. */
  arrive(id: string, dto: z.infer<typeof ArriveAsnSchema>) {
    return this.db.run(async (tx) => {
      const [a] = await tx.select().from(asns).where(eq(asns.id, id)).limit(1).for('update');
      if (!a) throw new NotFoundError('ASN', id);
      if (!['EXPECTED', 'SCHEDULED'].includes(a.status)) throw new DomainError(ErrorCodes.INVALID_STATE, `ASN is ${a.status}`);
      let dockId = dto.dockLocationId ?? a.dockLocationId ?? null;
      if (!dockId) {
        const wh = await this.lookup.warehouse(tx, a.warehouseId);
        dockId = ((wh.settings as Record<string, string>).defaultReceivingLocationId as string | undefined) ?? null;
      }
      if (dockId) {
        const dock = await this.lookup.location(tx, dockId);
        if (dock.usage !== 'DOCK' || dock.warehouseId !== a.warehouseId) throw new ValidationError('Dock must be a DOCK location in the same warehouse');
      }
      const [row] = await tx
        .update(asns)
        .set({ status: 'ARRIVED', arrivedAt: new Date(), dockLocationId: dockId, vehicleNo: dto.vehicleNo ?? a.vehicleNo, sealNo: dto.sealNo ?? a.sealNo, sealIntact: dto.sealIntact === undefined ? a.sealIntact : dto.sealIntact ? 'YES' : 'NO', driverName: dto.driverName ?? a.driverName, driverPhone: dto.driverPhone ?? a.driverPhone, temperatureOnArrival: dto.temperatureOnArrival != null ? D(dto.temperatureOnArrival) : a.temperatureOnArrival, notes: dto.notes ? `${a.notes ?? ''}\n[Arrival] ${dto.notes}`.trim() : a.notes })
        .where(eq(asns.id, id))
        .returning();
      await tx.update(dockAppointments).set({ status: 'ARRIVED' }).where(and(eq(dockAppointments.refType, 'ASN'), eq(dockAppointments.refId, id), inArray(dockAppointments.status, ['BOOKED', 'CONFIRMED'])));
      await this.audit.log({ action: 'asn.arrive', entityType: 'asn', entityId: id, after: dto }, tx);
      await this.events.publish('asn.arrived', { asnId: id, asnNumber: a.asnNumber, dockLocationId: dockId }, tx, { warehouseId: a.warehouseId, storerId: a.storerId });
      return serialize(row);
    });
  }

  cancel(id: string, reason: string, outerTx?: Tx) {
    return this.db.run(async (tx) => {
      const [a] = await tx.select().from(asns).where(eq(asns.id, id)).limit(1).for('update');
      if (!a) throw new NotFoundError('ASN', id);
      if (['RECEIVING', 'RECEIVED', 'CLOSED'].includes(a.status)) throw new DomainError(ErrorCodes.INVALID_STATE, `ASN with receipts cannot be cancelled (status ${a.status}); reverse the receipts first`);
      const [row] = await tx.update(asns).set({ status: 'CANCELLED', cancelledAt: new Date(), notes: `${a.notes ?? ''}\n[Cancelled] ${reason}`.trim() }).where(eq(asns.id, id)).returning();
      await tx.update(dockAppointments).set({ status: 'CANCELLED' }).where(and(eq(dockAppointments.refType, 'ASN'), eq(dockAppointments.refId, id), inArray(dockAppointments.status, ['BOOKED', 'CONFIRMED'])));
      await this.audit.log({ action: 'asn.cancel', entityType: 'asn', entityId: id, after: { reason } }, tx);
      await this.events.publish('asn.cancelled', { asnId: id, asnNumber: a.asnNumber, reason }, tx, { warehouseId: a.warehouseId, storerId: a.storerId });
      return serialize(row);
    }, { tx: outerTx });
  }

  // ---- helpers ----
  private async writeLines(tx: Tx, asnId: string, storerId: string, lines: AsnLineInputDto[]) {
    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`);
      if (sku.status !== 'ACTIVE') throw new ValidationError(`SKU ${sku.code} is inactive`);
      const { baseQty } = await this.lookup.toBaseQty(tx, sku.id, l.expectedQty, l.packLevel);
      await tx.insert(asnLines).values({
        tenantId: ctx.tenantId!,
        asnId,
        lineNo,
        skuId: sku.id,
        expectedQty: baseQty,
        packLevel: l.packLevel,
        packQty: D(l.expectedQty),
        expectedLot: l.expectedLot ?? null,
        expectedExpiry: l.expectedExpiry ?? null,
        externalLineRef: l.externalLineRef ?? null,
        unitValue: l.unitValue ? D(l.unitValue) : null,
        notes: l.notes ?? null,
      });
    }
  }

  async recomputeTotals(tx: Tx, asnId: string) {
    const lines = await tx.select({ l: asnLines, cbm: skus.cbm, weight: skus.weightKg }).from(asnLines).innerJoin(skus, eq(skus.id, asnLines.skuId)).where(eq(asnLines.asnId, asnId));
    const totalExpected = sum(lines.map((x) => x.l.expectedQty));
    const totalReceived = sum(lines.map((x) => D(x.l.receivedQty).plus(x.l.damagedQty)));
    const totalCbm = lines.reduce((acc, x) => acc.plus(x.cbm ? D(x.cbm).mul(x.l.expectedQty) : ZERO), ZERO);
    const totalWeight = lines.reduce((acc, x) => acc.plus(x.weight ? D(x.weight).mul(x.l.expectedQty) : ZERO), ZERO);
    await tx.update(asns).set({ totalExpectedQty: totalExpected, totalReceivedQty: totalReceived, totalCbm, totalWeightKg: totalWeight }).where(eq(asns.id, asnId));
  }
}