import { sql } from 'drizzle-orm'
import type { Transaction } from '@platform-modules/db'
import {
  InventoryItemNotFoundError,
  OversoldError,
} from './errors.js'
import type { InventorySchema } from './schema.js'
import { assertPositiveInt, assertUuid } from './types.js'

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]
}

export async function commitStock(
  tx: Transaction<InventorySchema>,
  skuId: string,
  qty: number,
): Promise<void> {
  assertPositiveInt(qty, 'qty')
  assertUuid(skuId, 'skuId')

  const itemRes = await tx.execute(sql`
    SELECT quantity_total, quantity_sold
    FROM inventory_item
    WHERE sku_id = ${skuId}::uuid
    FOR UPDATE
  `)
  const item = firstRow(itemRes)
  if (!item) {
    throw new InventoryItemNotFoundError(skuId)
  }

  const sumRes = await tx.execute(sql`
    SELECT COALESCE(SUM(qty), 0)::int AS reserved
    FROM stock_reservation
    WHERE sku_id = ${skuId}::uuid
      AND consumed_at IS NULL
      AND released_at IS NULL
  `)
  const sumRow = firstRow(sumRes)
  const reserved = Number(sumRow?.reserved ?? 0)
  const total = item.quantity_total === null || item.quantity_total === undefined
    ? null
    : Number(item.quantity_total)
  const sold = Number(item.quantity_sold ?? 0)

  if (total !== null && total - sold - reserved < qty) {
    throw new OversoldError(skuId, qty)
  }

  await tx.execute(sql`
    UPDATE inventory_item
    SET quantity_sold = quantity_sold + ${qty}, updated_at = now()
    WHERE sku_id = ${skuId}::uuid
  `)
}