import { Injectable } from '@nestjs/common';
import { and, desc, eq, inArray, sql, isNull, lt, or } from 'drizzle-orm';
import { CreateCycleCountDto, SubmitCountDto, ReviewCountLinesSchema, PaginationQueryDto, InventoryStatus, hasPermission } from '@wms/shared';
import { z } from 'zod';
import Decimal from 'decimal.js';
import { DbService, Tx } from '@/common/db/db.service';
import { cycleCounts, cycleCountLines, inventoryBalances, locations, skus, lpns, tasks, reasonCodes, inventoryAdjustments } 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, ForbiddenError } from '@/common/errors/domain-error';
import { D, ZERO, paginate, offsetOf, assertWarehouseAccess, serialize, qcol } from '@/common/util';
import { LookupService } from '@/modules/master-data/lookup.service';
import { AdjustmentsService } from './adjustments.service';
import { LedgerService } from './ledger.service';

type CountLine = typeof cycleCountLines.$inferSelect;

/**
 * Cycle / full / spot counts.
 * PLANNED -> RELEASED (lines snapshot + one COUNT task per location) -> IN_PROGRESS -> REVIEW -> COMPLETED
 * Variances are posted as COUNT-sourced adjustments through the normal approval path,
 * so stock accuracy changes always have a reason code, an approver and a ledger trail.
 */
@Injectable()
export class CountsService {
  constructor(
    private readonly db: DbService,
    private readonly lookup: LookupService,
    private readonly numbering: NumberingService,
    private readonly audit: AuditService,
    private readonly events: EventBus,
    private readonly adjustments: AdjustmentsService,
    private readonly ledger: LedgerService,
  ) {}

  list(q: PaginationQueryDto & { warehouseId?: string; status?: string }) {
    return this.db.run(async (tx) => {
      const where = and(q.warehouseId ? eq(cycleCounts.warehouseId, q.warehouseId) : undefined, q.status ? eq(cycleCounts.status, q.status as never) : undefined);
      const [{ count }] = await tx.select({ count: sql<number>`count(*)`.mapWith(Number) }).from(cycleCounts).where(where);
      const rows = await tx
        .select({
          count: cycleCounts,
          lineTotal: sql<number>`(select count(*) from ${cycleCountLines} l where l.count_id = ${qcol(cycleCounts.id)})`.mapWith(Number),
          linePending: sql<number>`(select count(*) from ${cycleCountLines} l where l.count_id = ${qcol(cycleCounts.id)} and l.status in ('PENDING','RECOUNT'))`.mapWith(Number),
          lineVariance: sql<number>`(select count(*) from ${cycleCountLines} l where l.count_id = ${qcol(cycleCounts.id)} and coalesce(l.variance_qty,0) <> 0)`.mapWith(Number),
        })
        .from(cycleCounts)
        .where(where)
        .orderBy(desc(cycleCounts.createdAt))
        .limit(q.pageSize)
        .offset(offsetOf(q));
      return paginate(rows.map((r) => ({ ...r.count, lineTotal: r.lineTotal, linePending: r.linePending, lineVariance: r.lineVariance })), count, q);
    });
  }

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

  async getInTx(tx: Tx, id: string) {
    const [c] = await tx.select().from(cycleCounts).where(eq(cycleCounts.id, id)).limit(1);
    if (!c) throw new NotFoundError('Count', id);
    const lines = await tx
      .select({ line: cycleCountLines, locationCode: locations.code, skuCode: skus.code, skuDescription: skus.description, lpnNumber: lpns.lpnNumber })
      .from(cycleCountLines)
      .innerJoin(locations, eq(locations.id, cycleCountLines.locationId))
      .leftJoin(skus, eq(skus.id, cycleCountLines.skuId))
      .leftJoin(lpns, eq(lpns.id, cycleCountLines.lpnId))
      .where(eq(cycleCountLines.countId, id))
      .orderBy(locations.code);
    return serialize({ ...c, lines: lines.map((l) => ({ ...l.line, locationCode: l.locationCode, skuCode: l.skuCode, skuDescription: l.skuDescription, lpnNumber: l.lpnNumber })) });
  }

  create(dto: CreateCycleCountDto, outerTx?: Tx) {
    assertWarehouseAccess(dto.warehouseId);
    const ctx = requireRequestContext();
    return this.db.run(async (tx) => {
      const countNumber = await this.numbering.next('CNT', tx, dto.warehouseId);
      const [c] = await tx
        .insert(cycleCounts)
        .values({ tenantId: ctx.tenantId!, warehouseId: dto.warehouseId, countNumber, type: dto.type, storerId: dto.storerId ?? null, scope: dto.scope, blind: dto.blind, recountTolerancePct: D(dto.recountTolerancePct), scheduledFor: dto.scheduledFor ? new Date(dto.scheduledFor) : null, createdById: ctx.userId!, notes: dto.notes })
        .returning();
      await this.audit.log({ action: 'count.create', entityType: 'cycle_count', entityId: c.id, after: dto }, tx);
      if (dto.releaseNow) return this.releaseInTx(tx, c.id);
      return c;
    }, { tx: outerTx });
  }

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

  private async releaseInTx(tx: Tx, id: string) {
    const ctx = requireRequestContext();
    const [c] = await tx.select().from(cycleCounts).where(eq(cycleCounts.id, id)).limit(1).for('update');
    if (!c) throw new NotFoundError('Count', id);
    if (c.status !== 'PLANNED') throw new DomainError(ErrorCodes.INVALID_STATE, `Count is ${c.status}`);
    const scope = c.scope as { zoneIds?: string[]; locationIds?: string[]; skuIds?: string[]; abcClasses?: string[]; includeEmptyLocations?: boolean; maxLocations?: number; notCountedSinceDays?: number };

    // 1. candidate locations
    const locWhere = and(
      eq(locations.warehouseId, c.warehouseId),
      eq(locations.status, 'ACTIVE'),
      scope.zoneIds?.length ? inArray(locations.zoneId, scope.zoneIds) : undefined,
      scope.locationIds?.length ? inArray(locations.id, scope.locationIds) : undefined,
      scope.abcClasses?.length ? inArray(locations.abcClass, scope.abcClasses) : undefined,
      scope.notCountedSinceDays ? or(isNull(locations.lastCountedAt), lt(locations.lastCountedAt, new Date(Date.now() - scope.notCountedSinceDays * 86400000))) : undefined,
      sql`${locations.type} <> 'VIRTUAL'`,
    );
    let locRows = await tx.select({ id: locations.id, code: locations.code }).from(locations).where(locWhere).orderBy(locations.pickSequence, locations.code);

    // 2. balances in those locations (optionally restricted by storer / sku)
    const locIds = locRows.map((l) => l.id);
    const balances = locIds.length
      ? await tx
          .select()
          .from(inventoryBalances)
          .where(and(inArray(inventoryBalances.locationId, locIds), sql`${inventoryBalances.qtyOnHand} > 0`, c.storerId ? eq(inventoryBalances.storerId, c.storerId) : undefined, scope.skuIds?.length ? inArray(inventoryBalances.skuId, scope.skuIds) : undefined))
      : [];
    const stockedLocIds = new Set(balances.map((b) => b.locationId));
    if (!scope.includeEmptyLocations) locRows = locRows.filter((l) => stockedLocIds.has(l.id));
    if (scope.maxLocations && locRows.length > scope.maxLocations) locRows = locRows.slice(0, scope.maxLocations);
    const finalLocIds = new Set(locRows.map((l) => l.id));
    if (!locRows.length) throw new ValidationError('Count scope matched no locations');

    // 3. lines: one per stock bucket + one placeholder for empty locations
    const lineValues: (typeof cycleCountLines.$inferInsert)[] = [];
    for (const b of balances.filter((b) => finalLocIds.has(b.locationId))) {
      lineValues.push({ tenantId: ctx.tenantId!, countId: id, locationId: b.locationId, skuId: b.skuId, storerId: b.storerId, lpnId: b.lpnId, lotNumber: b.lotNumber, expiryDate: b.expiryDate, inventoryStatus: b.status, systemQty: b.qtyOnHand });
    }
    for (const l of locRows) if (!stockedLocIds.has(l.id)) lineValues.push({ tenantId: ctx.tenantId!, countId: id, locationId: l.id, skuId: null, storerId: c.storerId, systemQty: ZERO });
    for (let i = 0; i < lineValues.length; i += 500) await tx.insert(cycleCountLines).values(lineValues.slice(i, i + 500));

    // 4. one COUNT task per location
    let seq = 0;
    for (const l of locRows) {
      seq += 10;
      await tx.insert(tasks).values({
        tenantId: ctx.tenantId!,
        warehouseId: c.warehouseId,
        taskNumber: await this.numbering.next('TASK', tx, c.warehouseId),
        type: 'COUNT',
        priority: c.type === 'SPOT' ? 3 : 6,
        sequence: seq,
        groupKey: c.id,
        fromLocationId: l.id,
        refType: 'COUNT',
        refId: c.id,
        data: { countNumber: c.countNumber, blind: c.blind, locationCode: l.code },
        createdById: ctx.userId,
      });
    }

    const [row] = await tx.update(cycleCounts).set({ status: 'RELEASED', releasedAt: new Date() }).where(eq(cycleCounts.id, id)).returning();
    await this.audit.log({ action: 'count.release', entityType: 'cycle_count', entityId: id, after: { locations: locRows.length, lines: lineValues.length } }, tx);
    return row;
  }

  /** RF: what should I count at this location (hidden quantities when blind). */
  locationSheet(countId: string, locationCode: string) {
    return this.db.run(async (tx) => {
      const [c] = await tx.select().from(cycleCounts).where(eq(cycleCounts.id, countId)).limit(1);
      if (!c) throw new NotFoundError('Count', countId);
      const loc = await this.lookup.locationByCode(tx, c.warehouseId, locationCode);
      const lines = await tx
        .select({ line: cycleCountLines, skuCode: skus.code, skuDescription: skus.description, lpnNumber: lpns.lpnNumber })
        .from(cycleCountLines)
        .leftJoin(skus, eq(skus.id, cycleCountLines.skuId))
        .leftJoin(lpns, eq(lpns.id, cycleCountLines.lpnId))
        .where(and(eq(cycleCountLines.countId, countId), eq(cycleCountLines.locationId, loc.id)));
      return serialize({
        count: { id: c.id, countNumber: c.countNumber, blind: c.blind },
        location: { id: loc.id, code: loc.code },
        lines: lines.map((l) => ({ ...l.line, skuCode: l.skuCode, skuDescription: l.skuDescription, lpnNumber: l.lpnNumber, systemQty: c.blind ? null : l.line.systemQty })),
      });
    });
  }

  /** RF submit: entries observed at the location. Unlisted buckets count as zero; unknown buckets become "found" lines. */
  submit(countId: string, dto: SubmitCountDto) {
    const ctx = requireRequestContext();
    return this.db.run(async (tx) => {
      const [c] = await tx.select().from(cycleCounts).where(eq(cycleCounts.id, countId)).limit(1).for('update');
      if (!c) throw new NotFoundError('Count', countId);
      if (!['RELEASED', 'IN_PROGRESS'].includes(c.status)) throw new DomainError(ErrorCodes.INVALID_STATE, `Count is ${c.status}`);
      const loc = dto.locationId ? await this.lookup.location(tx, dto.locationId) : dto.locationCode ? await this.lookup.locationByCode(tx, c.warehouseId, dto.locationCode) : null;
      if (!loc) throw new ValidationError('locationId or locationCode is required');

      const existing = await tx.select().from(cycleCountLines).where(and(eq(cycleCountLines.countId, countId), eq(cycleCountLines.locationId, loc.id))).for('update');
      const seen = new Set<string>();
      const results: CountLine[] = [];

      for (const e of dto.entries) {
        const skuRef = e.skuId ? { sku: await this.lookup.sku(tx, e.skuId), packLevel: e.packLevel ?? 'EACH' } : e.barcode ? await this.lookup.skuByBarcode(tx, e.barcode, c.storerId ?? undefined) : null;
        if (!skuRef) throw new ValidationError('Each entry needs skuId or barcode');
        const { baseQty } = await this.lookup.toBaseQty(tx, skuRef.sku.id, e.qty, e.packLevel ?? skuRef.packLevel);
        const lpn = e.lpnNumber ? await this.lookup.lpnByNumber(tx, e.lpnNumber) : null;
        const lot = e.lotNumber ?? '';
        const expiry = e.expiryDate ?? null;
        const status = e.inventoryStatus as InventoryStatus;
        let line = existing.find((l) => l.skuId === skuRef.sku.id && (l.lpnId ?? null) === (lpn?.id ?? null) && l.lotNumber === lot && (l.expiryDate ?? null) === expiry && l.inventoryStatus === status);
        // live system quantity at the time of counting (stock may have moved since release)
        const live = await this.ledger.getBalance(tx, { warehouseId: c.warehouseId, storerId: skuRef.sku.storerId, skuId: skuRef.sku.id, locationId: loc.id, lpnId: lpn?.id ?? null, lotNumber: lot, expiryDate: expiry, status });
        const systemQty = live ? D(live.qtyOnHand) : ZERO;
        if (!line) {
          const [created] = await tx
            .insert(cycleCountLines)
            .values({ tenantId: ctx.tenantId!, countId, locationId: loc.id, skuId: skuRef.sku.id, storerId: skuRef.sku.storerId, lpnId: lpn?.id ?? null, lotNumber: lot, expiryDate: expiry, inventoryStatus: status, systemQty })
            .returning();
          line = created;
        }
        seen.add(line.id);
        results.push(await this.applyObservation(tx, c, line, baseQty, systemQty, dto.isRecount));
      }
      // buckets expected but not observed -> counted as zero
      for (const l of existing) {
        if (seen.has(l.id)) continue;
        if (l.status === 'APPROVED' || l.status === 'VARIANCE_POSTED' || l.status === 'SKIPPED') continue;
        const live = l.skuId
          ? await this.ledger.getBalance(tx, { warehouseId: c.warehouseId, storerId: l.storerId!, skuId: l.skuId, locationId: loc.id, lpnId: l.lpnId, lotNumber: l.lotNumber, expiryDate: l.expiryDate, status: l.inventoryStatus })
          : null;
        results.push(await this.applyObservation(tx, c, l, ZERO, live ? D(live.qtyOnHand) : ZERO, dto.isRecount));
      }

      // task completion for this location
      await tx
        .update(tasks)
        .set({ status: 'COMPLETED', completedAt: new Date(), completedById: ctx.userId })
        .where(and(eq(tasks.groupKey, countId), eq(tasks.type, 'COUNT'), eq(tasks.fromLocationId, loc.id), inArray(tasks.status, ['PENDING', 'ASSIGNED', 'IN_PROGRESS'])));
      await tx.update(locations).set({ lastCountedAt: new Date() }).where(eq(locations.id, loc.id));

      // count status
      const [{ pending }] = await tx.select({ pending: sql<number>`count(*)`.mapWith(Number) }).from(cycleCountLines).where(and(eq(cycleCountLines.countId, countId), inArray(cycleCountLines.status, ['PENDING'])));
      await tx.update(cycleCounts).set({ status: pending === 0 ? 'REVIEW' : 'IN_PROGRESS' }).where(eq(cycleCounts.id, countId));
      await this.audit.log({ action: 'count.submit', entityType: 'cycle_count', entityId: countId, after: { locationId: loc.id, entries: dto.entries.length, recount: dto.isRecount } }, tx);
      const visible = c.blind ? results.map((l) => ({ ...l, systemQty: null, varianceQty: null, recountQty: l.recountQty, status: l.status })) : results;
      return serialize({ lines: visible, remainingLines: pending, remainingLocations: pending });
    });
  }

  private async applyObservation(tx: Tx, c: typeof cycleCounts.$inferSelect, line: CountLine, counted: Decimal, systemQty: Decimal, isRecount: boolean): Promise<CountLine> {
    const ctx = requireRequestContext();
    const variance = counted.minus(systemQty);
    const tol = D(c.recountTolerancePct);
    const needsRecount = !isRecount && !variance.isZero() && (tol.isZero() || systemQty.isZero() || variance.abs().div(systemQty).mul(100).gt(tol));
    const patch: Partial<typeof cycleCountLines.$inferInsert> = {
      systemQty,
      varianceQty: variance,
      finalQty: counted,
      status: needsRecount ? 'RECOUNT' : 'COUNTED',
    };
    if (isRecount) {
      patch.recountQty = counted;
      patch.recountById = ctx.userId;
      patch.recountAt = new Date();
    } else {
      patch.countedQty = counted;
      patch.countedById = ctx.userId;
      patch.countedAt = new Date();
    }
    const [row] = await tx.update(cycleCountLines).set(patch).where(eq(cycleCountLines.id, line.id)).returning();
    return row;
  }

  /** Supervisor review: approve (post variance), request recount, or skip. */
  review(countId: string, dto: z.infer<typeof ReviewCountLinesSchema>) {
    const ctx = requireRequestContext();
    return this.db.run(async (tx) => {
      const [c] = await tx.select().from(cycleCounts).where(eq(cycleCounts.id, countId)).limit(1).for('update');
      if (!c) throw new NotFoundError('Count', countId);
      assertWarehouseAccess(c.warehouseId);
      const lines = await tx.select().from(cycleCountLines).where(and(eq(cycleCountLines.countId, countId), inArray(cycleCountLines.id, dto.lineIds))).for('update');
      if (lines.length !== dto.lineIds.length) throw new NotFoundError('Count line');

      if (dto.action === 'RECOUNT') {
        await tx.update(cycleCountLines).set({ status: 'RECOUNT' }).where(inArray(cycleCountLines.id, dto.lineIds));
        const locIds = Array.from(new Set(lines.map((l) => l.locationId)));
        for (const locationId of locIds) {
          const loc = await this.lookup.location(tx, locationId);
          await tx.insert(tasks).values({ tenantId: ctx.tenantId!, warehouseId: c.warehouseId, taskNumber: await this.numbering.next('TASK', tx, c.warehouseId), type: 'COUNT', priority: 3, groupKey: c.id, fromLocationId: locationId, refType: 'COUNT', refId: c.id, data: { countNumber: c.countNumber, blind: c.blind, locationCode: loc.code, recount: true }, createdById: ctx.userId });
        }
        await tx.update(cycleCounts).set({ status: 'IN_PROGRESS' }).where(eq(cycleCounts.id, countId));
      } else if (dto.action === 'SKIP') {
        await tx.update(cycleCountLines).set({ status: 'SKIPPED', approvedById: ctx.userId, notes: dto.notes }).where(inArray(cycleCountLines.id, dto.lineIds));
      } else {
        // APPROVE: lines with variance become one adjustment per storer, posted now.
        // Four-eyes: posting stock corrections needs the adjustment-approval permission, and the
        // approver must not be the person who counted (or recounted) the lines.
        const withVariance = lines.filter((l) => l.skuId && l.varianceQty && !D(l.varianceQty).isZero());
        if (withVariance.length) {
          if (!hasPermission(ctx.permissions, 'inventory.adjust.approve')) throw new ForbiddenError('Posting count variances requires inventory.adjust.approve', 'FOUR_EYES');
          const self = withVariance.filter((l) => l.countedById === ctx.userId || l.recountById === ctx.userId);
          if (self.length) throw new ForbiddenError(`You counted ${self.length} of these lines; another user must approve the variance`, 'FOUR_EYES');
          const reasonId = dto.reasonCodeId ?? (await this.defaultCountReason(tx));
          const byStorer = new Map<string, CountLine[]>();
          for (const l of withVariance) byStorer.set(l.storerId!, [...(byStorer.get(l.storerId!) ?? []), l]);
          for (const [storerId, group] of byStorer) {
            const adj = await this.adjustments.create(
              {
                warehouseId: c.warehouseId,
                storerId,
                reasonCodeId: reasonId,
                notes: `Cycle count ${c.countNumber} variance. ${dto.notes ?? ''}`.trim(),
                submit: true,
                lines: group.map((l) => ({
                  skuId: l.skuId!,
                  locationId: l.locationId,
                  lpnId: l.lpnId ?? undefined,
                  lotNumber: l.lotNumber,
                  expiryDate: l.expiryDate ?? undefined,
                  fromStatus: l.inventoryStatus,
                  qtyDelta: D(l.varianceQty!).toFixed(4),
                  serials: [],
                })),
              },
              { source: 'COUNT', countId: c.id, tx },
            );
            const [adjRow] = await tx.select().from(inventoryAdjustments).where(eq(inventoryAdjustments.id, adj.id)).limit(1);
            await this.adjustments.postLines(tx, adjRow);
            await tx.update(inventoryAdjustments).set({ status: 'POSTED', approvedById: ctx.userId, approvedAt: new Date(), postedAt: new Date() }).where(eq(inventoryAdjustments.id, adj.id));
            await tx.update(cycleCountLines).set({ status: 'VARIANCE_POSTED', approvedById: ctx.userId, adjustmentId: adj.id }).where(inArray(cycleCountLines.id, group.map((l) => l.id)));
            await this.events.publish('inventory.adjusted', { adjustmentId: adj.id, adjNumber: adjRow.adjNumber, source: 'COUNT', countNumber: c.countNumber }, tx, { warehouseId: c.warehouseId, storerId });
          }
        }
        const noVariance = lines.filter((l) => !withVariance.includes(l));
        if (noVariance.length) await tx.update(cycleCountLines).set({ status: 'APPROVED', approvedById: ctx.userId }).where(inArray(cycleCountLines.id, noVariance.map((l) => l.id)));
      }

      // complete when nothing is open
      const [{ open }] = await tx.select({ open: sql<number>`count(*)`.mapWith(Number) }).from(cycleCountLines).where(and(eq(cycleCountLines.countId, countId), inArray(cycleCountLines.status, ['PENDING', 'COUNTED', 'RECOUNT'])));
      if (open === 0) {
        await tx.update(cycleCounts).set({ status: 'COMPLETED', completedAt: new Date() }).where(eq(cycleCounts.id, countId));
        await this.events.publish('count.completed', { countId, countNumber: c.countNumber }, tx, { warehouseId: c.warehouseId });
      }
      await this.audit.log({ action: `count.review.${dto.action.toLowerCase()}`, entityType: 'cycle_count', entityId: countId, after: dto }, tx);
      return this.getInTx(tx, countId);
    });
  }

  cancel(id: string) {
    return this.db.run(async (tx) => {
      const [c] = await tx.select().from(cycleCounts).where(eq(cycleCounts.id, id)).limit(1);
      if (!c) throw new NotFoundError('Count', id);
      assertWarehouseAccess(c.warehouseId);
      if (c.status === 'COMPLETED') throw new DomainError(ErrorCodes.INVALID_STATE, 'Completed counts cannot be cancelled');
      await tx.update(tasks).set({ status: 'CANCELLED' }).where(and(eq(tasks.groupKey, id), inArray(tasks.status, ['PENDING', 'ASSIGNED', 'IN_PROGRESS'])));
      const [row] = await tx.update(cycleCounts).set({ status: 'CANCELLED' }).where(eq(cycleCounts.id, id)).returning();
      await this.audit.log({ action: 'count.cancel', entityType: 'cycle_count', entityId: id }, tx);
      return row;
    });
  }

  private async defaultCountReason(tx: Tx): Promise<string> {
    const [r] = await tx.select({ id: reasonCodes.id }).from(reasonCodes).where(and(eq(reasonCodes.type, 'ADJUSTMENT'), eq(reasonCodes.code, 'COUNT'))).limit(1);
    if (!r) throw new ValidationError('No ADJUSTMENT/COUNT reason code configured; pass reasonCodeId');
    return r.id;
  }
}