import { Injectable } from '@nestjs/common';
import { and, eq, ilike, or, sql, inArray } from 'drizzle-orm';
import { z } from 'zod';
import { CreateSkuDto, UpdateSkuSchema, UpsertSlottingDto, PaginationQueryDto, SkuPackDto } from '@wms/shared';
import { DbService, Tx } from '@/common/db/db.service';
import { skus, skuPacks, skuBarcodes, skuSlottings, locations, inventoryBalances } from '@/db/schema';
import { AuditService } from '@/common/audit/audit.service';
import { requireRequestContext } from '@/common/context/request-context';
import { NotFoundError, ValidationError, DomainError } from '@/common/errors/domain-error';
import { D, paginate, offsetOf, resolveStorerScope, serialize, assertWarehouseAccess, qcol } from '@/common/util';

@Injectable()
export class SkusService {
  constructor(
    private readonly db: DbService,
    private readonly audit: AuditService,
  ) {}

  list(q: PaginationQueryDto & { storerId?: string; status?: string; category?: string }) {
    const scope = resolveStorerScope(q.storerId);
    return this.db.run(async (tx) => {
      const where = and(
        scope ? eq(skus.storerId, scope) : undefined,
        q.status ? eq(skus.status, q.status as never) : undefined,
        q.category ? eq(skus.category, q.category) : undefined,
        q.q
          ? or(
              ilike(skus.code, `%${q.q}%`),
              ilike(skus.description, `%${q.q}%`),
              ilike(skus.brand, `%${q.q}%`),
              // scanned barcodes (item-level and pack-level) resolve too, so RF "scan anything" works
              sql`exists (select 1 from ${skuBarcodes} b where b.sku_id = ${qcol(skus.id)} and b.barcode = ${q.q})`,
              sql`exists (select 1 from ${skuPacks} p where p.sku_id = ${qcol(skus.id)} and p.barcode = ${q.q})`,
            )
          : undefined,
      );
      const [{ count }] = await tx.select({ count: sql<number>`count(*)`.mapWith(Number) }).from(skus).where(where);
      const rows = await tx.select().from(skus).where(where).orderBy(skus.code).limit(q.pageSize).offset(offsetOf(q));
      const ids = rows.map((r) => r.id);
      const packs = ids.length ? await tx.select().from(skuPacks).where(inArray(skuPacks.skuId, ids)) : [];
      const barcodes = ids.length ? await tx.select().from(skuBarcodes).where(inArray(skuBarcodes.skuId, ids)) : [];
      const onHand = ids.length
        ? await tx
            .select({ skuId: inventoryBalances.skuId, qty: sql<string>`sum(${inventoryBalances.qtyOnHand})`, allocated: sql<string>`sum(${inventoryBalances.qtyAllocated})` })
            .from(inventoryBalances)
            .where(inArray(inventoryBalances.skuId, ids))
            .groupBy(inventoryBalances.skuId)
        : [];
      const items = rows.map((s) => ({
        ...s,
        packs: packs.filter((p) => p.skuId === s.id),
        barcodes: barcodes.filter((b) => b.skuId === s.id),
        qtyOnHand: onHand.find((o) => o.skuId === s.id)?.qty ?? '0',
        qtyAllocated: onHand.find((o) => o.skuId === s.id)?.allocated ?? '0',
      }));
      return paginate(items, count, q);
    });
  }

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

  async getInTx(tx: Tx, id: string) {
    const [s] = await tx.select().from(skus).where(eq(skus.id, id)).limit(1);
    if (!s) throw new NotFoundError('SKU', id);
    resolveStorerScope(s.storerId);
    const packs = await tx.select().from(skuPacks).where(eq(skuPacks.skuId, id));
    const barcodes = await tx.select().from(skuBarcodes).where(eq(skuBarcodes.skuId, id));
    const slottings = await tx
      .select({ slot: skuSlottings, locationCode: locations.code })
      .from(skuSlottings)
      .innerJoin(locations, eq(locations.id, skuSlottings.pickLocationId))
      .where(eq(skuSlottings.skuId, id));
    return serialize({ ...s, packs, barcodes, slottings: slottings.map((r) => ({ ...r.slot, locationCode: r.locationCode })) });
  }

  create(dto: CreateSkuDto) {
    const ctx = requireRequestContext();
    const storerId = resolveStorerScope(dto.storerId)!;
    this.validateFlags(dto);
    return this.db.run(async (tx) => {
      const { packs, barcodes, ...rest } = dto;
      const [row] = await tx
        .insert(skus)
        .values({ tenantId: ctx.tenantId!, ...rest, storerId, ...this.decimals(rest) })
        .returning();
      await this.replacePacks(tx, row.id, packs);
      await this.replaceBarcodes(tx, row.id, barcodes);
      await this.audit.log({ action: 'sku.create', entityType: 'sku', entityId: row.id, after: dto }, tx);
      return this.getInTx(tx, row.id);
    });
  }

  update(id: string, dto: z.infer<typeof UpdateSkuSchema>) {
    return this.db.run(async (tx) => {
      const [before] = await tx.select().from(skus).where(eq(skus.id, id)).limit(1);
      if (!before) throw new NotFoundError('SKU', id);
      resolveStorerScope(before.storerId);
      const { packs, barcodes, ...rest } = dto;
      // Flags that change how stock is identified cannot be flipped while stock exists
      const [{ qty }] = await tx.select({ qty: sql<string>`coalesce(sum(qty_on_hand),0)` }).from(inventoryBalances).where(eq(inventoryBalances.skuId, id));
      if (D(qty).gt(0)) {
        for (const f of ['lotControlled', 'expiryControlled', 'serialControlled'] as const) {
          if (rest[f] !== undefined && rest[f] !== before[f]) throw new DomainError('INVALID_STATE', `${f} cannot be changed while stock exists (${qty})`);
        }
      }
      const [row] = await tx.update(skus).set({ ...rest, ...this.decimals(rest) }).where(eq(skus.id, id)).returning();
      if (packs) await this.replacePacks(tx, id, packs);
      if (barcodes) await this.replaceBarcodes(tx, id, barcodes);
      await this.audit.log({ action: 'sku.update', entityType: 'sku', entityId: id, before, after: row }, tx);
      return this.getInTx(tx, id);
    });
  }

  /** Bulk upsert used by imports and the public API. Returns created/updated counts. */
  async upsertMany(tx: Tx, storerId: string, items: CreateSkuDto[]): Promise<{ created: number; updated: number; ids: string[] }> {
    const ctx = requireRequestContext();
    let created = 0;
    let updated = 0;
    const ids: string[] = [];
    for (const dto of items) {
      this.validateFlags(dto);
      const { packs, barcodes, ...rest } = dto;
      const [existing] = await tx.select({ id: skus.id }).from(skus).where(and(eq(skus.storerId, storerId), eq(skus.code, dto.code))).limit(1);
      let id: string;
      if (existing) {
        await tx.update(skus).set({ ...rest, storerId, ...this.decimals(rest) }).where(eq(skus.id, existing.id));
        id = existing.id;
        updated++;
      } else {
        const [row] = await tx.insert(skus).values({ tenantId: ctx.tenantId!, ...rest, storerId, ...this.decimals(rest) }).returning({ id: skus.id });
        id = row.id;
        created++;
      }
      if (packs.length) await this.replacePacks(tx, id, packs);
      if (barcodes.length) await this.replaceBarcodes(tx, id, barcodes);
      ids.push(id);
    }
    return { created, updated, ids };
  }

  upsertSlotting(dto: UpsertSlottingDto, outerTx?: Tx) {
    assertWarehouseAccess(dto.warehouseId);
    const ctx = requireRequestContext();
    return this.db.run(async (tx) => {
      const [loc] = await tx.select().from(locations).where(eq(locations.id, dto.pickLocationId)).limit(1);
      if (!loc || loc.warehouseId !== dto.warehouseId) throw new ValidationError('Pick location must belong to the warehouse');
      if (D(dto.minQty).gte(dto.maxQty)) throw new ValidationError('minQty must be lower than maxQty');
      const [row] = await tx
        .insert(skuSlottings)
        .values({ tenantId: ctx.tenantId!, ...dto, minQty: D(dto.minQty), maxQty: D(dto.maxQty) })
        .onConflictDoUpdate({
          target: [skuSlottings.skuId, skuSlottings.warehouseId, skuSlottings.pickLocationId],
          set: { minQty: D(dto.minQty), maxQty: D(dto.maxQty), replenishPackLevel: dto.replenishPackLevel, active: dto.active },
        })
        .returning();
      if (loc.usage !== 'PICK') await tx.update(locations).set({ usage: 'PICK' }).where(eq(locations.id, loc.id));
      await this.audit.log({ action: 'slotting.upsert', entityType: 'sku_slotting', entityId: row.id, after: dto }, tx);
      return serialize(row);
    }, { tx: outerTx });
  }

  deleteSlotting(id: string) {
    return this.db.run(async (tx) => {
      const [before] = await tx.delete(skuSlottings).where(eq(skuSlottings.id, id)).returning();
      if (!before) throw new NotFoundError('Slotting', id);
      await this.audit.log({ action: 'slotting.delete', entityType: 'sku_slotting', entityId: id, before }, tx);
      return { ok: true };
    });
  }

  // ---- helpers ----
  private validateFlags(dto: { expiryControlled: boolean; lotControlled: boolean; packs: SkuPackDto[] }) {
    const levels = dto.packs.map((p) => p.level);
    if (new Set(levels).size !== levels.length) throw new ValidationError('Duplicate pack levels');
    if (dto.packs.some((p) => p.level === 'EACH' && D(p.qtyInBase).toNumber() !== 1)) throw new ValidationError('EACH pack must have qtyInBase = 1');
  }

  private decimals(d: Partial<CreateSkuDto>) {
    return {
      weightKg: d.weightKg !== undefined ? D(d.weightKg) : undefined,
      cbm: d.cbm !== undefined ? D(d.cbm) : undefined,
      lengthCm: d.lengthCm !== undefined ? D(d.lengthCm) : undefined,
      widthCm: d.widthCm !== undefined ? D(d.widthCm) : undefined,
      heightCm: d.heightCm !== undefined ? D(d.heightCm) : undefined,
      unitValue: d.unitValue !== undefined ? D(d.unitValue) : undefined,
    };
  }

  private async replacePacks(tx: Tx, skuId: string, packs: SkuPackDto[]) {
    const ctx = requireRequestContext();
    await tx.delete(skuPacks).where(eq(skuPacks.skuId, skuId));
    const withEach = packs.some((p) => p.level === 'EACH') ? packs : [{ level: 'EACH' as const, uomCode: 'EA', qtyInBase: '1', isDefaultReceiving: false, isDefaultShipping: false }, ...packs];
    await tx.insert(skuPacks).values(
      withEach.map((p) => ({
        tenantId: ctx.tenantId!,
        skuId,
        level: p.level,
        uomCode: p.uomCode,
        qtyInBase: D(p.qtyInBase),
        lengthCm: p.lengthCm ? D(p.lengthCm) : null,
        widthCm: p.widthCm ? D(p.widthCm) : null,
        heightCm: p.heightCm ? D(p.heightCm) : null,
        weightKg: p.weightKg ? D(p.weightKg) : null,
        barcode: p.barcode ?? null,
        palletTi: p.palletTi ?? null,
        palletHi: p.palletHi ?? null,
        isDefaultReceiving: p.isDefaultReceiving ?? false,
        isDefaultShipping: p.isDefaultShipping ?? false,
      })),
    );
  }

  private async replaceBarcodes(tx: Tx, skuId: string, barcodes: { barcode: string; packLevel: 'EACH' | 'INNER' | 'CASE' | 'PALLET'; isPrimary: boolean }[]) {
    const ctx = requireRequestContext();
    await tx.delete(skuBarcodes).where(eq(skuBarcodes.skuId, skuId));
    if (barcodes.length) await tx.insert(skuBarcodes).values(barcodes.map((b) => ({ tenantId: ctx.tenantId!, skuId, ...b })));
  }
}