import { sql } from 'drizzle-orm'
import type { Querier } from '@platform-modules/db'
import { InventoryItemNotFoundError } from './errors.js'
import type { InventorySchema } from './schema.js'
import { 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 getAvailability(
  q: Querier<InventorySchema>,
  skuId: string,
): Promise<number | null> {
  assertUuid(skuId, 'skuId')

  const res = await q.execute(sql`
    SELECT
      quantity_total,
      quantity_sold,
      (
        SELECT COALESCE(SUM(qty), 0)::int
        FROM stock_reservation
        WHERE sku_id = ${skuId}::uuid
          AND consumed_at IS NULL
          AND released_at IS NULL
      ) AS reserved
    FROM inventory_item
    WHERE sku_id = ${skuId}::uuid
  `)

  const row = firstRow(res)
  if (!row) {
    throw new InventoryItemNotFoundError(skuId)
  }

  if (row.quantity_total === null || row.quantity_total === undefined) {
    return null
  }

  const total = Number(row.quantity_total)
  const sold = Number(row.quantity_sold ?? 0)
  const reserved = Number(row.reserved ?? 0)
  return total - sold - reserved
}