import { sql } from 'drizzle-orm'
import type { Transaction } from '@platform-modules/db'
import {
  InventoryItemNotFoundError,
  InventoryValidationError,
  OversoldError,
} from './errors.js'
import type { InventorySchema } from './schema.js'
import { assertPositiveInt, assertUuid, type ReserveArgs } 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]
}

const DEFAULT_TTL_MS = 15 * 60 * 1000

export async function reserve(
  tx: Transaction<InventorySchema>,
  args: ReserveArgs,
): Promise<{ reservationId: string }> {
  assertPositiveInt(args.qty, 'qty')
  assertUuid(args.skuId, 'skuId')
  if (args.holderRef.trim().length === 0) {
    throw new InventoryValidationError('holderRef')
  }

  const ttlMs = args.ttlMs ?? DEFAULT_TTL_MS
  const expiresAt = new Date(args.now.getTime() + ttlMs)

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

  const existingRes = await tx.execute(sql`
    SELECT id
    FROM stock_reservation
    WHERE sku_id = ${args.skuId}::uuid
      AND holder_ref = ${args.holderRef}
      AND consumed_at IS NULL
      AND released_at IS NULL
    LIMIT 1
  `)
  const existing = firstRow(existingRes)
  if (existing?.id) {
    return { reservationId: String(existing.id) }
  }

  const sumRes = await tx.execute(sql`
    SELECT COALESCE(SUM(qty), 0)::int AS reserved
    FROM stock_reservation
    WHERE sku_id = ${args.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 < args.qty) {
    throw new OversoldError(args.skuId, args.qty)
  }

  const insertRes = await tx.execute(sql`
    INSERT INTO stock_reservation (sku_id, qty, holder_ref, expires_at)
    VALUES (${args.skuId}::uuid, ${args.qty}, ${args.holderRef}, ${expiresAt.toISOString()}::timestamptz)
    RETURNING id
  `)
  const inserted = firstRow(insertRes)
  if (!inserted?.id) {
    throw new OversoldError(args.skuId, args.qty)
  }

  return { reservationId: String(inserted.id) }
}