import { Injectable, Logger } from '@nestjs/common';
import { and, eq, inArray, like, or, sql } from 'drizzle-orm';
import Decimal from 'decimal.js';
import { InventoryStatus, MovementType, SerialStatus } from '@wms/shared';
import { Tx } from '@/common/db/db.service';
import { inventoryBalances, inventoryMovements, lpns, serialUnits, locations } from '@/db/schema';
import { DomainError, ErrorCodes, ValidationError } from '@/common/errors/domain-error';
import { requireRequestContext } from '@/common/context/request-context';
import { D, ZERO, qcol } from '@/common/util';
import { LookupService, SkuRow } from '@/modules/master-data/lookup.service';

/** Identity of a stock bucket. */
export interface BalanceIdentity {
  warehouseId: string;
  storerId: string;
  skuId: string;
  locationId: string;
  lpnId: string | null;
  lotNumber: string;
  expiryDate: string | null; // YYYY-MM-DD
  status: InventoryStatus;
}

export interface PostMovementInput {
  type: MovementType;
  qty: Decimal | string | number;
  warehouseId: string;
  storerId: string;
  skuId: string;
  from?: { locationId: string; lpnId: string | null; status: InventoryStatus };
  to?: { locationId: string; lpnId: string | null; status: InventoryStatus };
  lotNumber?: string;
  expiryDate?: string | null;
  mfgDate?: string | null;
  serials?: string[];
  refType?: string;
  refId?: string;
  taskId?: string;
  reasonCodeId?: string | null;
  notes?: string;
  idempotencyKey?: string;
  /** when true the from-balance's allocated quantity is reduced by qty as well (pick against allocation) */
  consumesAllocation?: boolean;
  /** when true the to-balance's allocated quantity is increased by qty (unpick back into an allocation) */
  restoresAllocation?: boolean;
  /** skip the SKU control validations (used by reversals / corrections) */
  skipSkuValidation?: boolean;
  originAsnId?: string | null;
  /** keep the FIFO anchor of the source balance (moves must not reset age) */
  firstReceivedAt?: Date;
  performedAt?: Date;
}

export type MovementRow = typeof inventoryMovements.$inferSelect;
export type BalanceRow = typeof inventoryBalances.$inferSelect;

export function balanceKey(b: BalanceIdentity): string {
  return [b.warehouseId, b.storerId, b.skuId, b.locationId, b.lpnId ?? '', b.lotNumber ?? '', b.expiryDate ?? '', b.status].join('|');
}

const FROM_TYPES: MovementType[] = ['RECEIPT_REVERSAL', 'PUTAWAY', 'MOVE', 'REPLENISH', 'PICK', 'UNPICK', 'SHIP', 'ADJUST_OUT', 'STATUS_CHANGE', 'COUNT_ADJUST', 'TRANSFER_OUT', 'CONSUME'];
const TO_TYPES: MovementType[] = ['RECEIPT', 'PUTAWAY', 'MOVE', 'REPLENISH', 'PICK', 'UNPICK', 'ADJUST_IN', 'STATUS_CHANGE', 'COUNT_ADJUST', 'RETURN_RECEIPT', 'TRANSFER_IN'];

/**
 * The inventory ledger. All stock changes are posted here, inside the caller's
 * transaction, as one immutable movement plus balance updates.
 *
 * Concurrency: the source balance row is locked (FOR UPDATE) before checking
 * availability; the destination is upserted with ON CONFLICT so two receipts
 * into the same bucket cannot race. Postgres check constraints guarantee that
 * on-hand and allocated quantities never go negative even if a bug slips past.
 */
@Injectable()
export class LedgerService {
  private readonly logger = new Logger(LedgerService.name);

  constructor(private readonly lookup: LookupService) {}

  async post(tx: Tx, input: PostMovementInput): Promise<MovementRow> {
    const ctx = requireRequestContext();
    const qty = D(input.qty);
    if (!qty.gt(0)) throw new ValidationError('Movement quantity must be greater than zero');

    const hasFrom = !!input.from;
    const hasTo = !!input.to;
    if (FROM_TYPES.includes(input.type) && !hasFrom && input.type !== 'COUNT_ADJUST') throw new ValidationError(`${input.type} requires a source`);
    if (TO_TYPES.includes(input.type) && !hasTo && input.type !== 'COUNT_ADJUST') throw new ValidationError(`${input.type} requires a destination`);
    if (input.type === 'COUNT_ADJUST' && hasFrom === hasTo) throw new ValidationError('COUNT_ADJUST must have exactly one of from/to');

    const sku = await this.lookup.sku(tx, input.skuId);
    if (sku.storerId !== input.storerId) throw new DomainError(ErrorCodes.WRONG_SKU, 'SKU does not belong to the storer');
    const lotNumber = (input.lotNumber ?? '').trim();
    const expiryDate = input.expiryDate ?? null;

    if (!input.skipSkuValidation && hasTo && (input.type === 'RECEIPT' || input.type === 'RETURN_RECEIPT' || input.type === 'ADJUST_IN' || input.type === 'TRANSFER_IN')) {
      this.validateSkuControls(sku, lotNumber, expiryDate, input.serials ?? [], qty);
    }
    if (sku.serialControlled && (input.serials?.length ?? 0) > 0 && !D(input.serials!.length).eq(qty)) {
      throw new ValidationError(`Serial count (${input.serials!.length}) must equal quantity (${qty})`);
    }

    // ---- validate destination location ----
    if (hasTo) {
      const loc = await this.lookup.location(tx, input.to!.locationId);
      if (loc.warehouseId !== input.warehouseId) throw new ValidationError('Destination location is in another warehouse');
      if (loc.status === 'BLOCKED' || loc.status === 'INACTIVE') throw new DomainError(ErrorCodes.LOCATION_BLOCKED, `Location ${loc.code} is ${loc.status}`);
      await this.validateMixing(tx, loc, input, lotNumber);
    }

    const cube = this.lookup.cubeAndWeight(sku, qty);

    // ---- source balance: lock and decrement ----
    let fromBalance: BalanceRow | null = null;
    let firstReceivedAt = input.firstReceivedAt;
    if (hasFrom) {
      const key = balanceKey({ warehouseId: input.warehouseId, storerId: input.storerId, skuId: input.skuId, locationId: input.from!.locationId, lpnId: input.from!.lpnId, lotNumber, expiryDate, status: input.from!.status });
      const [locked] = await tx.select().from(inventoryBalances).where(eq(inventoryBalances.balanceKey, key)).for('update');
      fromBalance = locked ?? null;
      if (!fromBalance) {
        throw new DomainError(ErrorCodes.INSUFFICIENT_STOCK, `No stock for SKU ${sku.code} at the source (lot "${lotNumber}", status ${input.from!.status})`, 422, { key });
      }
      const onHand = D(fromBalance.qtyOnHand);
      const allocated = D(fromBalance.qtyAllocated);
      const available = onHand.minus(allocated);
      if (input.consumesAllocation) {
        if (allocated.lt(qty)) throw new DomainError(ErrorCodes.INSUFFICIENT_STOCK, `Allocated quantity ${allocated} is less than ${qty}`);
        if (onHand.lt(qty)) throw new DomainError(ErrorCodes.INSUFFICIENT_STOCK, `On-hand ${onHand} is less than ${qty}`);
      } else if (available.lt(qty)) {
        throw new DomainError(ErrorCodes.INSUFFICIENT_STOCK, `Available ${available} (on hand ${onHand}, allocated ${allocated}) is less than ${qty} for SKU ${sku.code}`);
      }
      firstReceivedAt = firstReceivedAt ?? fromBalance.firstReceivedAt;
      await tx
        .update(inventoryBalances)
        .set({
          qtyOnHand: onHand.minus(qty),
          qtyAllocated: input.consumesAllocation ? allocated.minus(qty) : allocated,
          lastMovedAt: new Date(),
          version: sql`${inventoryBalances.version} + 1`,
        })
        .where(eq(inventoryBalances.id, fromBalance.id));
    }

    // ---- destination balance: upsert ----
    let toBalanceId: string | null = null;
    if (hasTo) {
      const identity: BalanceIdentity = { warehouseId: input.warehouseId, storerId: input.storerId, skuId: input.skuId, locationId: input.to!.locationId, lpnId: input.to!.lpnId, lotNumber, expiryDate, status: input.to!.status };
      const key = balanceKey(identity);
      const anchor = firstReceivedAt ?? new Date();
      const [row] = await tx
        .insert(inventoryBalances)
        .values({
          tenantId: ctx.tenantId!,
          ...identity,
          balanceKey: key,
          mfgDate: input.mfgDate ?? fromBalance?.mfgDate ?? null,
          qtyOnHand: qty,
          qtyAllocated: input.restoresAllocation ? qty : ZERO,
          firstReceivedAt: anchor,
          lastMovedAt: new Date(),
          originAsnId: input.originAsnId ?? fromBalance?.originAsnId ?? null,
          unitCost: fromBalance?.unitCost ?? null,
        })
        .onConflictDoUpdate({
          target: inventoryBalances.balanceKey,
          set: {
            qtyOnHand: sql`${inventoryBalances.qtyOnHand} + ${qty.toFixed(4)}`,
            qtyAllocated: input.restoresAllocation ? sql`${inventoryBalances.qtyAllocated} + ${qty.toFixed(4)}` : undefined,
            lastMovedAt: new Date(),
            // keep the OLDER anchor so merging stock never makes it look fresher
            firstReceivedAt: sql`least(${inventoryBalances.firstReceivedAt}, ${anchor.toISOString()}::timestamptz)`,
            version: sql`${inventoryBalances.version} + 1`,
          },
        })
        .returning({ id: inventoryBalances.id });
      toBalanceId = row.id;
    }

    // ---- LPN bookkeeping ----
    if (hasTo && input.to!.lpnId) {
      await tx.update(lpns).set({ locationId: input.to!.locationId, status: 'ACTIVE', lastMovedAt: new Date(), storerId: sql`coalesce(${lpns.storerId}, ${input.storerId})` }).where(eq(lpns.id, input.to!.lpnId));
    }
    if (hasFrom && input.from!.lpnId) {
      await this.refreshLpnStatus(tx, input.from!.lpnId, input.type === 'SHIP' ? 'SHIPPED' : 'EMPTY');
    }

    // ---- movement row ----
    const [movement] = await tx
      .insert(inventoryMovements)
      .values({
        tenantId: ctx.tenantId!,
        warehouseId: input.warehouseId,
        storerId: input.storerId,
        skuId: input.skuId,
        type: input.type,
        qty,
        fromLocationId: input.from?.locationId ?? null,
        toLocationId: input.to?.locationId ?? null,
        fromLpnId: input.from?.lpnId ?? null,
        toLpnId: input.to?.lpnId ?? null,
        lotNumber,
        expiryDate,
        fromStatus: input.from?.status ?? null,
        toStatus: input.to?.status ?? null,
        refType: input.refType ?? null,
        refId: input.refId ?? null,
        taskId: input.taskId ?? null,
        reasonCodeId: input.reasonCodeId ?? null,
        serials: input.serials ?? [],
        cbm: cube.cbm,
        weightKg: cube.weightKg,
        idempotencyKey: input.idempotencyKey ?? null,
        performedById: ctx.userId,
        performedAt: input.performedAt ?? new Date(),
        deviceId: ctx.deviceId ?? null,
        notes: input.notes ?? null,
      })
      .returning();

    // ---- serial units ----
    if (sku.serialControlled && input.serials?.length) {
      await this.applySerials(tx, sku, input, movement.id);
    }

    void toBalanceId;
    return movement;
  }

  /** Has this idempotency key already been posted? Returns the existing movement. */
  async findByIdempotencyKey(tx: Tx, key: string): Promise<MovementRow | null> {
    // Handlers derive per-allocation / per-LPN keys as `<key>:<suffix>`, so a replay must match either form.
    const [m] = await tx.select().from(inventoryMovements).where(or(eq(inventoryMovements.idempotencyKey, key), like(inventoryMovements.idempotencyKey, `${key}:%`))).limit(1);
    return m ?? null;
  }

  async getBalance(tx: Tx, identity: BalanceIdentity, forUpdate = false): Promise<BalanceRow | null> {
    const key = balanceKey(identity);
    if (forUpdate) {
      const [b] = await tx.select().from(inventoryBalances).where(eq(inventoryBalances.balanceKey, key)).for('update');
      return b ?? null;
    }
    const [b] = await tx.select().from(inventoryBalances).where(eq(inventoryBalances.balanceKey, key)).limit(1);
    return b ?? null;
  }

  /** Reserve quantity on a balance (allocation). Caller must hold the row lock. */
  async reserve(tx: Tx, balanceId: string, qty: Decimal): Promise<void> {
    const res = await tx
      .update(inventoryBalances)
      .set({ qtyAllocated: sql`${inventoryBalances.qtyAllocated} + ${qty.toFixed(4)}`, version: sql`${inventoryBalances.version} + 1` })
      .where(and(eq(inventoryBalances.id, balanceId), sql`${inventoryBalances.qtyOnHand} - ${inventoryBalances.qtyAllocated} >= ${qty.toFixed(4)}`))
      .returning({ id: inventoryBalances.id });
    if (!res.length) throw new DomainError(ErrorCodes.INSUFFICIENT_STOCK, 'Balance no longer has enough available quantity to allocate');
  }

  async release(tx: Tx, balanceId: string, qty: Decimal): Promise<void> {
    await tx
      .update(inventoryBalances)
      .set({ qtyAllocated: sql`greatest(${inventoryBalances.qtyAllocated} - ${qty.toFixed(4)}, 0)`, version: sql`${inventoryBalances.version} + 1` })
      .where(eq(inventoryBalances.id, balanceId));
  }

  /** Removes zero rows older than a day to keep the balances table lean (job). */
  async purgeZeroBalances(tx: Tx): Promise<number> {
    // Only rows nothing refers to any more (allocations keep their balance_id for history).
    const rows = await tx
      .delete(inventoryBalances)
      .where(
        and(
          sql`${inventoryBalances.qtyOnHand} = 0`,
          sql`${inventoryBalances.qtyAllocated} = 0`,
          sql`${inventoryBalances.lastMovedAt} < now() - interval '1 day'`,
          sql`not exists (select 1 from allocations a where a.balance_id = ${qcol(inventoryBalances.id)})`,
        ),
      )
      .returning({ id: inventoryBalances.id });
    return rows.length;
  }

  // ------------------------------------------------------------------
  private validateSkuControls(sku: SkuRow, lot: string, expiry: string | null, serials: string[], qty: Decimal) {
    if (sku.lotControlled && !lot) throw new DomainError(ErrorCodes.LOT_REQUIRED, `SKU ${sku.code} is lot controlled; lot number required`);
    if (sku.expiryControlled && !expiry) throw new DomainError(ErrorCodes.EXPIRY_REQUIRED, `SKU ${sku.code} is expiry controlled; expiry date required`);
    if (sku.serialControlled && sku.captureSerialOnReceipt && serials.length === 0) throw new DomainError(ErrorCodes.SERIAL_REQUIRED, `SKU ${sku.code} is serial controlled; serial numbers required`);
    if (expiry && sku.minShelfLifeInboundDays) {
      const daysLeft = Math.floor((Date.parse(`${expiry}T00:00:00Z`) - Date.now()) / 86400000);
      if (daysLeft < sku.minShelfLifeInboundDays) {
        throw new DomainError(ErrorCodes.SHELF_LIFE_BELOW_MIN, `Remaining shelf life ${daysLeft}d is below the inbound minimum of ${sku.minShelfLifeInboundDays}d`, 422, { daysLeft, minimum: sku.minShelfLifeInboundDays });
      }
    }
    void qty;
  }

  private async validateMixing(tx: Tx, loc: typeof locations.$inferSelect, input: PostMovementInput, lot: string) {
    if (loc.allowMixedSku && loc.allowMixedLots) return;
    const others = await tx
      .select({ skuId: inventoryBalances.skuId, lotNumber: inventoryBalances.lotNumber, qty: inventoryBalances.qtyOnHand })
      .from(inventoryBalances)
      .where(and(eq(inventoryBalances.locationId, loc.id), sql`${inventoryBalances.qtyOnHand} > 0`));
    if (!loc.allowMixedSku && others.some((o) => o.skuId !== input.skuId)) {
      throw new DomainError(ErrorCodes.MIXING_NOT_ALLOWED, `Location ${loc.code} does not allow mixed SKUs`);
    }
    if (!loc.allowMixedLots && others.some((o) => o.skuId === input.skuId && o.lotNumber !== lot)) {
      throw new DomainError(ErrorCodes.MIXING_NOT_ALLOWED, `Location ${loc.code} does not allow mixed lots`);
    }
  }

  private async refreshLpnStatus(tx: Tx, lpnId: string, emptyStatus: 'EMPTY' | 'SHIPPED') {
    const [{ qty }] = await tx.select({ qty: sql<string>`coalesce(sum(${inventoryBalances.qtyOnHand}),0)` }).from(inventoryBalances).where(eq(inventoryBalances.lpnId, lpnId));
    if (D(qty).lte(0)) {
      await tx.update(lpns).set({ status: emptyStatus, locationId: emptyStatus === 'SHIPPED' ? null : undefined, lastMovedAt: new Date() }).where(eq(lpns.id, lpnId));
    }
  }

  private async applySerials(tx: Tx, sku: SkuRow, input: PostMovementInput, movementId: string) {
    const ctx = requireRequestContext();
    const serialsIn = Array.from(new Set((input.serials ?? []).map((s) => s.trim()).filter(Boolean)));
    if (serialsIn.length !== (input.serials ?? []).length) throw new DomainError(ErrorCodes.SERIAL_DUPLICATE, 'Duplicate or blank serial numbers in the list');

    const existing = await tx.select().from(serialUnits).where(and(eq(serialUnits.skuId, sku.id), inArray(serialUnits.serialNumber, serialsIn)));
    const byNo = new Map(existing.map((e) => [e.serialNumber, e]));

    const inbound = input.type === 'RECEIPT' || input.type === 'RETURN_RECEIPT' || input.type === 'ADJUST_IN' || input.type === 'TRANSFER_IN' || (input.type === 'COUNT_ADJUST' && !!input.to && !input.from);
    const outbound = input.type === 'SHIP' || input.type === 'ADJUST_OUT' || input.type === 'TRANSFER_OUT' || input.type === 'CONSUME' || input.type === 'RECEIPT_REVERSAL' || (input.type === 'COUNT_ADJUST' && !!input.from && !input.to);

    for (const serialNumber of serialsIn) {
      const row = byNo.get(serialNumber);
      if (inbound) {
        if (row && row.status === 'IN_STOCK') throw new DomainError(ErrorCodes.SERIAL_DUPLICATE, `Serial ${serialNumber} is already in stock`);
        const values = {
          tenantId: ctx.tenantId!,
          storerId: input.storerId,
          skuId: sku.id,
          serialNumber,
          status: 'IN_STOCK' as SerialStatus,
          warehouseId: input.warehouseId,
          locationId: input.to!.locationId,
          lpnId: input.to!.lpnId,
          lotNumber: input.lotNumber ?? '',
          expiryDate: input.expiryDate ?? null,
          receivedAsnId: input.refType === 'ASN' ? (input.refId ?? null) : (row?.receivedAsnId ?? null),
          orderId: null,
          lastMovementId: movementId,
        };
        if (row) await tx.update(serialUnits).set(values).where(eq(serialUnits.id, row.id));
        else await tx.insert(serialUnits).values(values);
      } else if (outbound) {
        if (!row || row.status === 'SHIPPED' || row.status === 'SCRAPPED') throw new DomainError(ErrorCodes.SERIAL_NOT_FOUND, `Serial ${serialNumber} is not in stock`);
        if (row.locationId !== input.from!.locationId) throw new DomainError(ErrorCodes.WRONG_LOCATION, `Serial ${serialNumber} is not at the source location`);
        const status: SerialStatus = input.type === 'SHIP' ? 'SHIPPED' : input.type === 'RECEIPT_REVERSAL' ? 'MISSING' : 'SCRAPPED';
        await tx.update(serialUnits).set({ status, locationId: null, lpnId: null, orderId: input.refType === 'ORDER' ? (input.refId ?? null) : row.orderId, lastMovementId: movementId }).where(eq(serialUnits.id, row.id));
      } else {
        // internal move / pick / status change: relocate
        if (!row) throw new DomainError(ErrorCodes.SERIAL_NOT_FOUND, `Serial ${serialNumber} unknown`);
        if (input.from && row.locationId !== input.from.locationId) throw new DomainError(ErrorCodes.WRONG_LOCATION, `Serial ${serialNumber} is not at the source location`);
        const status: SerialStatus = input.type === 'PICK' ? 'PICKED' : input.type === 'UNPICK' ? 'ALLOCATED' : row.status;
        await tx
          .update(serialUnits)
          .set({ status, locationId: input.to!.locationId, lpnId: input.to!.lpnId, orderId: input.type === 'PICK' && input.refType === 'ORDER' ? (input.refId ?? null) : row.orderId, lastMovementId: movementId })
          .where(eq(serialUnits.id, row.id));
      }
    }
  }
}