import { and, eq, sql } from 'drizzle-orm'
import type { Transaction } from '@platform-modules/db'
import {
  InventoryValidationError,
  ItemNotFoundError,
  LocationNotFoundError,
  OversoldError,
} from '../errors.js'
import {
  stockItem,
  stockLocation,
  type InventorySchema,
} from '../schema.js'
import type { Movement } from '../types.js'
import { consumeFifo, type CostLayer } from '../valuation/fifo.js'
import { blendReceipt, consumeAvg } from '../valuation/weighted-average.js'

type MovementKind =
  | 'receipt'
  | 'issue'
  | 'adjust'
  | 'transfer_out'
  | 'transfer_in'
  | 'count_variance'

interface PostMovementInput {
  readonly tenantId: string
  readonly itemId: string
  readonly locationId: string
  readonly kind: MovementKind
  readonly qtyDelta: number
  readonly unitCost?: number
  readonly holderRef?: string
  readonly transferGroup?: string
  readonly occurredAt: Date
  readonly allowNegative?: boolean
}

interface PostMovementResult {
  readonly movement: Movement
  readonly pendingCost: boolean
}

type SqlRow = Record<string, unknown>

function firstRow(res: unknown): SqlRow | undefined {
  const rows = (Array.isArray(res) ? res : (res as { rows?: SqlRow[] }).rows) ?? []
  return rows[0]
}

function numericField(row: SqlRow | undefined, field: string): number {
  const value = row?.[field]
  return Number(value ?? 0)
}

function textField(row: SqlRow | undefined, field: string): string | null {
  const value = row?.[field]
  return value === null || typeof value === 'undefined' ? null : String(value)
}

function requireFiniteNumber(value: number, field: string): void {
  if (!Number.isFinite(value)) {
    throw new InventoryValidationError(field)
  }
}

function requireNonNegativeNumber(value: number, field: string): void {
  requireFiniteNumber(value, field)
  if (value < 0) {
    throw new InventoryValidationError(field)
  }
}

function requireNonEmptyString(value: string, field: string): void {
  if (typeof value !== 'string' || value.trim().length === 0) {
    throw new InventoryValidationError(field)
  }
}

async function findExistingByHolderRef(
  tx: Transaction<InventorySchema>,
  input: PostMovementInput,
): Promise<PostMovementResult | undefined> {
  const existing = await tx.execute(sql`
    SELECT *
    FROM stock_movement
    WHERE tenant_id = ${input.tenantId}::uuid
      AND holder_ref = ${input.holderRef}
      AND kind = ${input.kind}
    LIMIT 1
  `)
  const row = firstRow(existing)
  if (!row) {
    return undefined
  }
  const movement = mapMovement(row)
  if (
    movement.itemId !== input.itemId ||
    movement.locationId !== input.locationId ||
    Number(movement.qtyDelta) !== input.qtyDelta
  ) {
    throw new InventoryValidationError('holderRef')
  }
  return { movement, pendingCost: movement.pendingCost }
}

async function readExistingByHolderRef(
  tx: Transaction<InventorySchema>,
  input: PostMovementInput,
): Promise<PostMovementResult> {
  const result = await findExistingByHolderRef(tx, input)
  if (!result) {
    throw new InventoryValidationError('holderRef')
  }
  return result
}

function requireDate(value: Date, field: string): void {
  if (!(value instanceof Date) || Number.isNaN(value.getTime())) {
    throw new InventoryValidationError(field)
  }
}

function requireIntegerQuantity(value: number, field: string): void {
  requireFiniteNumber(value, field)
  if (!Number.isInteger(value) || value === 0) {
    throw new InventoryValidationError(field)
  }
}

function validateInput(input: PostMovementInput): void {
  requireNonEmptyString(input.tenantId, 'tenantId')
  requireNonEmptyString(input.itemId, 'itemId')
  requireNonEmptyString(input.locationId, 'locationId')
  requireDate(input.occurredAt, 'occurredAt')
  requireIntegerQuantity(input.qtyDelta, 'qtyDelta')

  if (typeof input.holderRef !== 'undefined') {
    requireNonEmptyString(input.holderRef, 'holderRef')
  }

  if (typeof input.transferGroup !== 'undefined') {
    requireNonEmptyString(input.transferGroup, 'transferGroup')
  }

  switch (input.kind) {
    case 'receipt':
      if (input.qtyDelta <= 0) throw new InventoryValidationError('qtyDelta')
      if (typeof input.unitCost === 'undefined') throw new InventoryValidationError('unitCost')
      requireNonNegativeNumber(input.unitCost, 'unitCost')
      return
    case 'issue':
    case 'transfer_out':
      if (input.qtyDelta >= 0) throw new InventoryValidationError('qtyDelta')
      return
    case 'transfer_in':
      if (input.qtyDelta <= 0) throw new InventoryValidationError('qtyDelta')
      if (typeof input.unitCost === 'undefined') throw new InventoryValidationError('unitCost')
      requireNonNegativeNumber(input.unitCost, 'unitCost')
      return
    case 'adjust':
      if (input.qtyDelta > 0) {
        if (typeof input.unitCost === 'undefined') throw new InventoryValidationError('unitCost')
        requireNonNegativeNumber(input.unitCost, 'unitCost')
      } else if (typeof input.unitCost !== 'undefined') {
        requireNonNegativeNumber(input.unitCost, 'unitCost')
      }
      return
    case 'count_variance':
      if (typeof input.unitCost !== 'undefined') {
        requireNonNegativeNumber(input.unitCost, 'unitCost')
      }
      return
  }
}

function mapMovement(row: SqlRow): Movement {
  return {
    id: String(row.id),
    tenantId: String(row.tenant_id),
    itemId: String(row.item_id),
    locationId: String(row.location_id),
    kind: String(row.kind) as Movement['kind'],
    qtyDelta: String(row.qty_delta),
    unitCost: textField(row, 'unit_cost'),
    cogsAmount: textField(row, 'cogs_amount'),
    pendingCost: Boolean(row.pending_cost),
    holderRef: textField(row, 'holder_ref'),
    transferGroup: textField(row, 'transfer_group'),
    occurredAt: new Date(String(row.occurred_at)),
    createdAt: new Date(String(row.created_at)),
  }
}

async function getLastKnownUnitCost(
  tx: Transaction<InventorySchema>,
  input: PostMovementInput,
): Promise<number> {
  const result = await tx.execute(sql`
    SELECT unit_cost
    FROM stock_movement
    WHERE tenant_id = ${input.tenantId}::uuid
      AND item_id = ${input.itemId}::uuid
      AND location_id = ${input.locationId}::uuid
      AND unit_cost IS NOT NULL
    ORDER BY occurred_at DESC, created_at DESC
    LIMIT 1
  `)

  return numericField(firstRow(result), 'unit_cost')
}

async function requireItemAndLocation(
  tx: Transaction<InventorySchema>,
  input: PostMovementInput,
): Promise<'fifo' | 'weighted_average'> {
  const [item, location] = await Promise.all([
    tx
      .select({ method: stockItem.method })
      .from(stockItem)
      .where(and(eq(stockItem.id, input.itemId), eq(stockItem.tenantId, input.tenantId)))
      .limit(1),
    tx
      .select({ id: stockLocation.id })
      .from(stockLocation)
      .where(and(eq(stockLocation.id, input.locationId), eq(stockLocation.tenantId, input.tenantId)))
      .limit(1),
  ])

  const itemRow = item[0]
  if (!itemRow) {
    throw new ItemNotFoundError(input.tenantId, input.itemId)
  }

  if (!location[0]) {
    throw new LocationNotFoundError(input.tenantId, input.locationId)
  }

  return itemRow.method as 'fifo' | 'weighted_average'
}

async function readOnHand(
  tx: Transaction<InventorySchema>,
  input: PostMovementInput,
): Promise<number> {
  const result = 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 = ${input.itemId}::uuid
      AND location_id = ${input.locationId}::uuid
  `)

  return numericField(firstRow(result), 'qty')
}

export async function lockPosition(
  tx: Transaction<InventorySchema>,
  input: {
    readonly tenantId: string
    readonly itemId: string
    readonly locationId: string
  },
): Promise<void> {
  await tx.execute(sql`
    INSERT INTO stock_position (tenant_id, item_id, location_id, qty_on_hand, avg_unit_cost, updated_at)
    VALUES (
      ${input.tenantId}::uuid,
      ${input.itemId}::uuid,
      ${input.locationId}::uuid,
      0,
      0,
      now()
    )
    ON CONFLICT (tenant_id, item_id, location_id) DO NOTHING
  `)

  await tx.execute(sql`
    SELECT 1
    FROM stock_position
    WHERE tenant_id = ${input.tenantId}::uuid
      AND item_id = ${input.itemId}::uuid
      AND location_id = ${input.locationId}::uuid
    FOR UPDATE
  `)
}

export async function postMovement(
  tx: Transaction<InventorySchema>,
  input: PostMovementInput,
): Promise<PostMovementResult> {
  validateInput(input)

  if (typeof input.holderRef !== 'undefined') {
    const existing = await findExistingByHolderRef(tx, input)
    if (existing) {
      return existing
    }
  }

  const method = await requireItemAndLocation(tx, input)
  const allowNegative = input.allowNegative ?? true
  const movementId = crypto.randomUUID()
  const absQty = Math.abs(input.qtyDelta)
  let pendingCost = false
  let unitCost = typeof input.unitCost === 'undefined' ? null : input.unitCost
  let cogsAmount: number | null = null

  if (method === 'weighted_average') {
    await lockPosition(tx, input)

    const positionRes = await tx.execute(sql`
      SELECT qty_on_hand, avg_unit_cost
      FROM stock_position
      WHERE tenant_id = ${input.tenantId}::uuid
        AND item_id = ${input.itemId}::uuid
        AND location_id = ${input.locationId}::uuid
      FOR UPDATE
    `)
    if (typeof input.holderRef !== 'undefined') {
      const existing = await findExistingByHolderRef(tx, input)
      if (existing) {
        return existing
      }
    }

    const position = firstRow(positionRes)
    const currentOnHand = await readOnHand(tx, input)
    const nextOnHand = currentOnHand + input.qtyDelta
    const currentQty = numericField(position, 'qty_on_hand')
    const currentAvgCost = numericField(position, 'avg_unit_cost')

    if (input.qtyDelta > 0) {
      const inboundUnitCost = unitCost ?? currentAvgCost
      const nextState = currentQty >= 0
        ? blendReceipt(
            { quantity: currentQty, averageCost: currentAvgCost },
            { quantity: absQty, unitCost: inboundUnitCost },
          )
        : {
            quantity: nextOnHand,
            averageCost: nextOnHand > 0 ? inboundUnitCost : currentAvgCost,
          }

      await tx.execute(sql`
        UPDATE stock_position
        SET qty_on_hand = ${nextState.quantity},
            avg_unit_cost = ${nextState.averageCost},
            updated_at = now()
        WHERE tenant_id = ${input.tenantId}::uuid
          AND item_id = ${input.itemId}::uuid
          AND location_id = ${input.locationId}::uuid
      `)
    } else {
      if (nextOnHand < 0 && !allowNegative) {
        throw new OversoldError(input.tenantId, input.itemId, input.locationId, absQty)
      }

      const lastKnownUnitCost = currentAvgCost > 0 ? currentAvgCost : await getLastKnownUnitCost(tx, input)
      unitCost = currentAvgCost > 0 ? currentAvgCost : lastKnownUnitCost

      if (currentQty >= absQty && currentQty >= 0) {
        const nextState = consumeAvg(
          { quantity: currentQty, averageCost: currentAvgCost },
          absQty,
        )
        cogsAmount = nextState.consumedValue
        await tx.execute(sql`
          UPDATE stock_position
          SET qty_on_hand = ${nextState.quantity},
              avg_unit_cost = ${nextState.averageCost},
              updated_at = now()
          WHERE tenant_id = ${input.tenantId}::uuid
            AND item_id = ${input.itemId}::uuid
            AND location_id = ${input.locationId}::uuid
        `)
      } else {
        pendingCost = true
        cogsAmount = absQty * lastKnownUnitCost
        await tx.execute(sql`
          UPDATE stock_position
          SET qty_on_hand = ${nextOnHand},
              avg_unit_cost = ${lastKnownUnitCost},
              updated_at = now()
          WHERE tenant_id = ${input.tenantId}::uuid
            AND item_id = ${input.itemId}::uuid
            AND location_id = ${input.locationId}::uuid
        `)
      }
    }
  } else {
    await lockPosition(tx, input)

    const layerRes = await tx.execute(sql`
      SELECT id, unit_cost, qty_remaining, created_at
      FROM stock_cost_layer
      WHERE tenant_id = ${input.tenantId}::uuid
        AND item_id = ${input.itemId}::uuid
        AND location_id = ${input.locationId}::uuid
      ORDER BY created_at ASC
      FOR UPDATE
    `)
    if (typeof input.holderRef !== 'undefined') {
      const existing = await findExistingByHolderRef(tx, input)
      if (existing) {
        return existing
      }
    }

    const layers = (Array.isArray(layerRes)
      ? layerRes
      : (layerRes as { rows?: SqlRow[] }).rows) ?? []
    const currentOnHand = await readOnHand(tx, input)
    const nextOnHand = currentOnHand + input.qtyDelta

    if (input.qtyDelta > 0) {
      const inboundUnitCost = unitCost ?? 0
      await tx.execute(sql`
        INSERT INTO stock_cost_layer (
          id,
          tenant_id,
          item_id,
          location_id,
          receipt_movement_id,
          unit_cost,
          qty_remaining,
          created_at
        )
        VALUES (
          ${crypto.randomUUID()}::uuid,
          ${input.tenantId}::uuid,
          ${input.itemId}::uuid,
          ${input.locationId}::uuid,
          ${movementId}::uuid,
          ${inboundUnitCost},
          ${absQty},
          ${input.occurredAt.toISOString()}::timestamptz
        )
      `)
    } else {
      if (nextOnHand < 0 && !allowNegative) {
        throw new OversoldError(input.tenantId, input.itemId, input.locationId, absQty)
      }

      const fifoLayers: CostLayer[] = layers.map((layer) => ({
        id: String(layer.id),
        quantityRemaining: numericField(layer, 'qty_remaining'),
        unitCost: numericField(layer, 'unit_cost'),
      }))

      if (currentOnHand >= absQty) {
        const consumed = consumeFifo(fifoLayers, absQty)
        cogsAmount = consumed.cogs
        for (const layer of consumed.updatedLayers) {
          await tx.execute(sql`
            UPDATE stock_cost_layer
            SET qty_remaining = ${layer.quantityRemaining}
            WHERE id = ${layer.id}::uuid
          `)
        }
        unitCost = absQty === 0 ? unitCost : consumed.cogs / absQty
      } else {
        pendingCost = true
        const lastKnownUnitCost = await getLastKnownUnitCost(tx, input)
        cogsAmount = absQty * lastKnownUnitCost
        unitCost = lastKnownUnitCost
        for (const layer of fifoLayers) {
          await tx.execute(sql`
            UPDATE stock_cost_layer
            SET qty_remaining = 0
            WHERE id = ${layer.id}::uuid
          `)
        }
      }
    }
  }

  const inserted = await tx.execute(sql`
    INSERT INTO stock_movement (
      id,
      tenant_id,
      item_id,
      location_id,
      kind,
      qty_delta,
      unit_cost,
      cogs_amount,
      pending_cost,
      holder_ref,
      transfer_group,
      occurred_at
    )
    VALUES (
      ${movementId}::uuid,
      ${input.tenantId}::uuid,
      ${input.itemId}::uuid,
      ${input.locationId}::uuid,
      ${input.kind},
      ${input.qtyDelta},
      ${unitCost},
      ${cogsAmount},
      ${pendingCost},
      ${input.holderRef ?? null},
      ${input.transferGroup ?? null},
      ${input.occurredAt.toISOString()}::timestamptz
    )
    ON CONFLICT (tenant_id, holder_ref, kind) DO NOTHING
    RETURNING *
  `)

  const row = firstRow(inserted)
  if (!row) {
    // holder_ref sits in the conflict target, so DO NOTHING only fires when it
    // was set — a replayed request racing/repeating the same insert.
    return readExistingByHolderRef(tx, input)
  }

  const movement = mapMovement(row)
  return { movement, pendingCost }
}