import { Injectable, Logger } from '@nestjs/common';
import { and, eq, inArray, sql } from 'drizzle-orm';
import { ApiItemDto, ApiAsnDto, ApiOrderDto, ApiInventoryQuerySchema, CreateSkuDto } from '@wms/shared';
import { z } from 'zod';
import { DbService, Tx } from '@/common/db/db.service';
import { integrationMessages, asns, asnLines, outboundOrders, outboundOrderLines, skus, inventoryBalances, locations, serialUnits, warehouses } from '@/db/schema';
import { requireRequestContext } from '@/common/context/request-context';
import { NotFoundError, ValidationError } from '@/common/errors/domain-error';
import { D, serialize } from '@/common/util';
import { LookupService } from '@/modules/master-data/lookup.service';
import { SkusService } from '@/modules/master-data/skus.service';
import { AsnService } from '@/modules/inbound/asn.service';
import { OrdersService } from '@/modules/outbound/orders.service';

/**
 * Public integration API (v1). Works with codes instead of ids, is idempotent
 * per storer reference, and logs every message for traceability.
 */
@Injectable()
export class PublicApiService {
  private readonly logger = new Logger(PublicApiService.name);

  constructor(
    private readonly db: DbService,
    private readonly lookup: LookupService,
    private readonly skusService: SkusService,
    private readonly asnService: AsnService,
    private readonly ordersService: OrdersService,
  ) {}

  private storerIdFor(tx: Tx, code?: string): Promise<string> {
    const ctx = requireRequestContext();
    if (ctx.storerId) return Promise.resolve(ctx.storerId);
    if (!code) throw new ValidationError('storerCode is required for tenant-level API keys');
    return this.lookup.storerByCode(tx, code).then((s) => s.id);
  }

  private async log(tx: Tx, input: { storerId: string | null; messageType: string; externalId?: string; idempotencyKey?: string; payload: unknown }) {
    const ctx = requireRequestContext();
    const [row] = await tx
      .insert(integrationMessages)
      .values({ tenantId: ctx.tenantId!, storerId: input.storerId, direction: 'INBOUND', messageType: input.messageType, externalId: input.externalId ?? null, idempotencyKey: input.idempotencyKey ?? null, payload: input.payload, status: 'PROCESSING', attempts: 1 })
      .onConflictDoNothing({ target: [integrationMessages.tenantId, integrationMessages.idempotencyKey] })
      .returning();
    return row ?? null;
  }

  private async complete(tx: Tx, id: string, resultRefType: string, resultRefId: string) {
    await tx.update(integrationMessages).set({ status: 'PROCESSED', processedAt: new Date(), resultRefType, resultRefId }).where(eq(integrationMessages.id, id));
  }

  // ---------------- items ----------------
  upsertItems(storerCode: string | undefined, items: ApiItemDto[], idempotencyKey?: string) {
    return this.db.run(async (tx) => {
      const storerId = await this.storerIdFor(tx, storerCode);
      const msg = await this.log(tx, { storerId, messageType: 'ITEM_MASTER', idempotencyKey, payload: { count: items.length } });
      if (!msg) return { idempotent: true };
      const mapped: CreateSkuDto[] = items.map((i) => ({
        storerId,
        code: i.code,
        description: i.description,
        longDescription: i.longDescription,
        category: i.category,
        brand: i.brand,
        storageCondition: i.storageCondition,
        rotationRule: i.rotationRule,
        lotControlled: i.lotControlled,
        expiryControlled: i.expiryControlled,
        serialControlled: i.serialControlled,
        captureSerialOnReceipt: true,
        shelfLifeDays: i.shelfLifeDays,
        baseUom: i.baseUom,
        weightKg: i.weightKg?.toString(),
        cbm: i.cbm?.toString(),
        lengthCm: i.lengthCm?.toString(),
        widthCm: i.widthCm?.toString(),
        heightCm: i.heightCm?.toString(),
        hsCode: i.hsCode,
        hazmat: false,
        barcodes: i.barcodes.map((b) => ({ ...b, isPrimary: false })),
        packs: i.packs.map((p) => ({ level: p.level, uomCode: p.uomCode, qtyInBase: p.qtyInBase.toString(), barcode: p.barcode, palletTi: p.palletTi, palletHi: p.palletHi, isDefaultReceiving: false, isDefaultShipping: false })),
        attributes: i.attributes,
      }));
      const res = await this.skusService.upsertMany(tx, storerId, mapped);
      await this.complete(tx, msg.id, 'SKU_BATCH', msg.id);
      return { created: res.created, updated: res.updated };
    });
  }

  listItems(storerCode: string | undefined, q: { code?: string; updatedSince?: string; page: number; pageSize: number }) {
    return this.db.run(async (tx) => {
      const storerId = await this.storerIdFor(tx, storerCode);
      const rows = await tx
        .select()
        .from(skus)
        .where(and(eq(skus.storerId, storerId), q.code ? eq(skus.code, q.code) : undefined, q.updatedSince ? sql`${skus.updatedAt} >= ${q.updatedSince}` : undefined))
        .orderBy(skus.code)
        .limit(q.pageSize)
        .offset((q.page - 1) * q.pageSize);
      return serialize(rows.map((s) => ({ code: s.code, description: s.description, category: s.category, brand: s.brand, storageCondition: s.storageCondition, rotationRule: s.rotationRule, lotControlled: s.lotControlled, expiryControlled: s.expiryControlled, serialControlled: s.serialControlled, baseUom: s.baseUom, weightKg: s.weightKg, cbm: s.cbm, status: s.status, updatedAt: s.updatedAt })));
    });
  }

  // ---------------- ASNs ----------------
  createAsn(dto: ApiAsnDto, idempotencyKey?: string) {
    return this.db.run(async (tx) => {
      const storerId = await this.storerIdFor(tx, dto.storerCode);
      const wh = await this.lookup.warehouseByCode(tx, dto.warehouseCode);
      const key = idempotencyKey ?? `ASN:${storerId}:${dto.reference}`;
      const msg = await this.log(tx, { storerId, messageType: 'ASN', externalId: dto.reference, idempotencyKey: key, payload: dto });
      if (!msg) {
        const [existing] = await tx.select({ id: asns.id, asnNumber: asns.asnNumber, status: asns.status }).from(asns).where(and(eq(asns.storerId, storerId), eq(asns.externalRef, dto.reference), sql`${asns.status} <> 'CANCELLED'`)).limit(1);
        return { idempotent: true, asnNumber: existing?.asnNumber, status: existing?.status };
      }
      const created = await this.asnService.create(
        {
          warehouseId: wh.id,
          storerId,
          externalRef: dto.reference,
          type: dto.type,
          priority: dto.priority,
          expectedAt: dto.expectedAt,
          containerNo: dto.containerNo,
          containerSize: dto.containerSize as never,
          carrierName: dto.carrierName,
          notes: dto.notes,
          documents: dto.documents,
          attributes: { supplierCode: dto.supplierCode },
          lines: dto.lines.map((l, i) => ({ lineNo: i + 1, skuCode: l.itemCode, expectedQty: l.qty.toString(), packLevel: l.packLevel, expectedLot: l.lot, expectedExpiry: l.expiryDate, externalLineRef: l.lineRef, unitValue: l.unitValue?.toString() })),
        },
        'API',
        tx,
      );
      await this.complete(tx, msg.id, 'ASN', (created as { id: string }).id);
      return { idempotent: false, asnNumber: (created as { asnNumber: string }).asnNumber, status: (created as { status: string }).status, id: (created as { id: string }).id };
    });
  }

  asnStatus(storerCode: string | undefined, reference: string) {
    return this.db.run(async (tx) => {
      const storerId = await this.storerIdFor(tx, storerCode);
      const [a] = await tx.select().from(asns).where(and(eq(asns.storerId, storerId), eq(asns.externalRef, reference))).orderBy(sql`${asns.createdAt} desc`).limit(1);
      if (!a) throw new NotFoundError('ASN', reference);
      const lines = await tx.select({ l: asnLines, code: skus.code }).from(asnLines).innerJoin(skus, eq(skus.id, asnLines.skuId)).where(eq(asnLines.asnId, a.id)).orderBy(asnLines.lineNo);
      return serialize({ reference: a.externalRef, asnNumber: a.asnNumber, status: a.status, arrivedAt: a.arrivedAt, closedAt: a.closedAt, lines: lines.map((x) => ({ lineRef: x.l.externalLineRef, itemCode: x.code, expected: x.l.expectedQty, received: x.l.receivedQty, damaged: x.l.damagedQty, rejected: x.l.rejectedQty, status: x.l.status })) });
    });
  }

  // ---------------- orders ----------------
  createOrder(dto: ApiOrderDto, idempotencyKey?: string) {
    return this.db.run(async (tx) => {
      const storerId = await this.storerIdFor(tx, dto.storerCode);
      const wh = await this.lookup.warehouseByCode(tx, dto.warehouseCode);
      const key = idempotencyKey ?? `ORDER:${storerId}:${dto.reference}`;
      const msg = await this.log(tx, { storerId, messageType: 'ORDER', externalId: dto.reference, idempotencyKey: key, payload: dto });
      if (!msg) {
        const [existing] = await tx.select({ orderNumber: outboundOrders.orderNumber, status: outboundOrders.status }).from(outboundOrders).where(and(eq(outboundOrders.storerId, storerId), eq(outboundOrders.externalRef, dto.reference), sql`${outboundOrders.status} <> 'CANCELLED'`)).limit(1);
        return { idempotent: true, orderNumber: existing?.orderNumber, status: existing?.status };
      }
      const created = await this.ordersService.create(
        {
          warehouseId: wh.id,
          storerId,
          externalRef: dto.reference,
          type: dto.type,
          priority: dto.priority,
          customerCode: dto.customerCode,
          shipTo: dto.shipTo as never,
          carrierName: dto.carrierName,
          requestedShipDate: dto.requestedShipDate,
          dueDate: dto.dueDate,
          notes: dto.notes,
          attributes: {},
          lines: dto.lines.map((l, i) => ({ lineNo: i + 1, skuCode: l.itemCode, orderedQty: l.qty.toString(), packLevel: l.packLevel, requestedLot: l.lot, requestedSerials: l.serials, minExpiryDate: l.minExpiryDate, externalLineRef: l.lineRef, unitPrice: l.unitPrice?.toString() })),
        },
        'API',
        tx,
      );
      await this.complete(tx, msg.id, 'ORDER', (created as { id: string }).id);
      return { idempotent: false, orderNumber: (created as { orderNumber: string }).orderNumber, status: (created as { status: string }).status, id: (created as { id: string }).id };
    });
  }

  orderStatus(storerCode: string | undefined, reference: string) {
    return this.db.run(async (tx) => {
      const storerId = await this.storerIdFor(tx, storerCode);
      const [o] = await tx.select().from(outboundOrders).where(and(eq(outboundOrders.storerId, storerId), eq(outboundOrders.externalRef, reference))).orderBy(sql`${outboundOrders.createdAt} desc`).limit(1);
      if (!o) throw new NotFoundError('Order', reference);
      const lines = await tx.select({ l: outboundOrderLines, code: skus.code }).from(outboundOrderLines).innerJoin(skus, eq(skus.id, outboundOrderLines.skuId)).where(eq(outboundOrderLines.orderId, o.id)).orderBy(outboundOrderLines.lineNo);
      return serialize({ reference: o.externalRef, orderNumber: o.orderNumber, status: o.status, shippedAt: o.shippedAt, carrierName: o.carrierName, lines: lines.map((x) => ({ lineRef: x.l.externalLineRef, itemCode: x.code, ordered: x.l.orderedQty, allocated: x.l.allocatedQty, picked: x.l.pickedQty, shipped: x.l.shippedQty, status: x.l.status })) });
    });
  }

  cancelOrder(storerCode: string | undefined, reference: string, reason: string) {
    return this.db.run(async (tx) => {
      const storerId = await this.storerIdFor(tx, storerCode);
      const [o] = await tx.select({ id: outboundOrders.id }).from(outboundOrders).where(and(eq(outboundOrders.storerId, storerId), eq(outboundOrders.externalRef, reference), sql`${outboundOrders.status} <> 'CANCELLED'`)).limit(1);
      if (!o) throw new NotFoundError('Order', reference);
      return this.ordersService.cancel(o.id, reason);
    }).then((r) => ({ status: (r as { status: string }).status }));
  }

  // ---------------- inventory ----------------
  inventory(q: z.infer<typeof ApiInventoryQuerySchema>) {
    return this.db.run(async (tx) => {
      const storerId = await this.storerIdFor(tx, q.storerCode);
      const wh = q.warehouseCode ? await this.lookup.warehouseByCode(tx, q.warehouseCode) : null;
      const where = and(eq(inventoryBalances.storerId, storerId), wh ? eq(inventoryBalances.warehouseId, wh.id) : undefined, q.itemCode ? eq(skus.code, q.itemCode) : undefined, q.lot ? eq(inventoryBalances.lotNumber, q.lot) : undefined, q.status ? eq(inventoryBalances.status, q.status as never) : undefined, sql`${inventoryBalances.qtyOnHand} > 0`, inArray(locations.usage, ['RESERVE', 'PICK', 'QUARANTINE', 'DAMAGED', 'RETURNS', 'QC', 'DOCK']));
      if (q.groupBy === 'DETAIL') {
        const rows = await tx.select({ b: inventoryBalances, code: skus.code, loc: locations.code, whCode: warehouses.code }).from(inventoryBalances).innerJoin(skus, eq(skus.id, inventoryBalances.skuId)).innerJoin(locations, eq(locations.id, inventoryBalances.locationId)).innerJoin(warehouses, eq(warehouses.id, inventoryBalances.warehouseId)).where(where).orderBy(skus.code);
        return serialize(rows.map((r) => ({ warehouseCode: r.whCode, itemCode: r.code, location: r.loc, lot: r.b.lotNumber, expiryDate: r.b.expiryDate, status: r.b.status, onHand: r.b.qtyOnHand, allocated: r.b.qtyAllocated, available: D(r.b.qtyOnHand).minus(r.b.qtyAllocated) })));
      }
      const groupCols = q.groupBy === 'ITEM_LOT' ? [warehouses.code, skus.code, inventoryBalances.lotNumber, inventoryBalances.expiryDate, inventoryBalances.status] : [warehouses.code, skus.code, inventoryBalances.status];
      const rows = await tx
        .select({ whCode: warehouses.code, code: skus.code, lot: q.groupBy === 'ITEM_LOT' ? inventoryBalances.lotNumber : sql<string | null>`null`, expiryDate: q.groupBy === 'ITEM_LOT' ? inventoryBalances.expiryDate : sql<string | null>`null`, status: inventoryBalances.status, onHand: sql<string>`sum(${inventoryBalances.qtyOnHand})`, allocated: sql<string>`sum(${inventoryBalances.qtyAllocated})`, available: sql<string>`sum(${inventoryBalances.qtyOnHand} - ${inventoryBalances.qtyAllocated})` })
        .from(inventoryBalances)
        .innerJoin(skus, eq(skus.id, inventoryBalances.skuId))
        .innerJoin(locations, eq(locations.id, inventoryBalances.locationId))
        .innerJoin(warehouses, eq(warehouses.id, inventoryBalances.warehouseId))
        .where(where)
        .groupBy(...groupCols)
        .orderBy(skus.code);
      return serialize(rows.map((r) => ({ warehouseCode: r.whCode, itemCode: r.code, lot: r.lot, expiryDate: r.expiryDate, status: r.status, onHand: r.onHand, allocated: r.allocated, available: r.available })));
    });
  }

  serials(storerCode: string | undefined, q: { itemCode?: string; serialNumber?: string; status?: string }) {
    return this.db.run(async (tx) => {
      const storerId = await this.storerIdFor(tx, storerCode);
      const rows = await tx
        .select({ s: serialUnits, code: skus.code, loc: locations.code })
        .from(serialUnits)
        .innerJoin(skus, eq(skus.id, serialUnits.skuId))
        .leftJoin(locations, eq(locations.id, serialUnits.locationId))
        .where(and(eq(serialUnits.storerId, storerId), q.itemCode ? eq(skus.code, q.itemCode) : undefined, q.serialNumber ? eq(serialUnits.serialNumber, q.serialNumber) : undefined, q.status ? eq(serialUnits.status, q.status as never) : undefined))
        .limit(1000);
      return serialize(rows.map((r) => ({ itemCode: r.code, serialNumber: r.s.serialNumber, status: r.s.status, location: r.loc, lot: r.s.lotNumber, expiryDate: r.s.expiryDate, updatedAt: r.s.updatedAt })));
    });
  }
}