import { and, eq, sql } from 'drizzle-orm'
import type { Database, PostgresTransaction } from '@platform-modules/db'
import { stockItem, stockPosition, type InventorySchema } from './schema.js'
import type { CountResult } from './types.js'
import { InventoryValidationError } from './errors.js'
import { lockPosition, postMovement } from './internal/post-movement.js'
import {
  firstRow,
  mapMovement,
  numericValue,
  withInventoryTransaction,
} from './internal/public-helpers.js'

export interface RecordCountInput {
  readonly tenantId: string
  readonly locationId: string
  readonly holderRef: string
  readonly lines: ReadonlyArray<{
    readonly itemId: string
    readonly countedQty: number
  }>
  readonly occurredAt: Date
}

export async function recordCount(
  db: Database<InventorySchema> | PostgresTransaction<InventorySchema>,
  input: RecordCountInput,
): Promise<CountResult[]> {
  return withInventoryTransaction(db, async (tx) => {
    const results: CountResult[] = []

    for (const line of input.lines) {
      if (!Number.isInteger(line.countedQty) || line.countedQty < 0) {
        throw new InventoryValidationError('countedQty')
      }
      const lineHolderRef = `${input.holderRef}:${line.itemId}:${input.locationId}`

      await lockPosition(tx, {
        tenantId: input.tenantId,
        itemId: line.itemId,
        locationId: input.locationId,
      })

      const existingRes = await tx.execute(sql`
        SELECT *
        FROM stock_movement
        WHERE tenant_id = ${input.tenantId}::uuid
          AND holder_ref = ${lineHolderRef}
          AND kind = 'count_variance'
        LIMIT 1
      `)
      const existingRow = firstRow(existingRes)

      const onHandRes = await tx.execute(sql`
        SELECT COALESCE(SUM(qty_delta), 0)::numeric AS qty
        FROM stock_movement
        WHERE tenant_id = ${input.tenantId}::uuid
          AND item_id = ${line.itemId}::uuid
          AND location_id = ${input.locationId}::uuid
      `)
      const onHand = numericValue(firstRow(onHandRes)?.qty)

      if (existingRow) {
        const movement = mapMovement(existingRow)
        const onHandBeforeThisMovement = onHand - Number(movement.qtyDelta)
        const varianceAtPostTime = line.countedQty - onHandBeforeThisMovement
        if (
          movement.itemId !== line.itemId ||
          movement.locationId !== input.locationId ||
          Number(movement.qtyDelta) !== varianceAtPostTime
        ) {
          throw new InventoryValidationError('holderRef')
        }
        results.push({
          itemId: line.itemId,
          countedQty: String(line.countedQty),
          variance: movement.qtyDelta,
          movement,
        })
        continue
      }

      const variance = line.countedQty - onHand

      if (variance === 0) {
        continue
      }

      let unitCost: number | undefined
      if (variance > 0) {
        const item = await tx
          .select({ method: stockItem.method })
          .from(stockItem)
          .where(and(eq(stockItem.id, line.itemId), eq(stockItem.tenantId, input.tenantId)))
          .limit(1)

        if (item[0]?.method === 'fifo') {
          const lastCostRes = await tx.execute(sql`
            SELECT unit_cost
            FROM stock_movement
            WHERE tenant_id = ${input.tenantId}::uuid
              AND item_id = ${line.itemId}::uuid
              AND location_id = ${input.locationId}::uuid
              AND unit_cost IS NOT NULL
            ORDER BY occurred_at DESC, created_at DESC
            LIMIT 1
          `)
          unitCost = numericValue(firstRow(lastCostRes)?.unit_cost)
        } else {
          const position = await tx
            .select({ avgUnitCost: stockPosition.avgUnitCost })
            .from(stockPosition)
            .where(
              and(
                eq(stockPosition.tenantId, input.tenantId),
                eq(stockPosition.itemId, line.itemId),
                eq(stockPosition.locationId, input.locationId),
              ),
            )
            .limit(1)
          unitCost = numericValue(position[0]?.avgUnitCost)
        }
      }

      const movement = await postMovement(tx, {
        tenantId: input.tenantId,
        itemId: line.itemId,
        locationId: input.locationId,
        kind: 'count_variance',
        qtyDelta: variance,
        unitCost,
        holderRef: lineHolderRef,
        occurredAt: input.occurredAt,
      })

      results.push({
        itemId: line.itemId,
        countedQty: String(line.countedQty),
        variance: String(variance),
        movement: movement.movement,
      })
    }

    return results
  })
}