import { sql } from 'drizzle-orm'
import type { Transaction } from '@platform-modules/db'
import { InventoryValidationError } 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 setInventory(
  tx: Transaction<InventorySchema>,
  item: { skuId: string; vendorId?: string | null; quantityTotal: number | null },
): Promise<void> {
  assertUuid(item.skuId, 'skuId')
  if (item.quantityTotal !== null) {
    if (!Number.isInteger(item.quantityTotal) || item.quantityTotal < 0) {
      throw new InventoryValidationError('quantityTotal')
    }
  }
  const vendorId = item.vendorId ?? null
  const quantityTotal = item.quantityTotal

  // ① ensure-row, idempotent. A brand-new row has zero sold + zero holds, so creating it
  //    at the requested total is always safe; avoids a create-path race hole.
  await tx.execute(sql`
    INSERT INTO inventory_item (sku_id, vendor_id, quantity_total, quantity_sold)
    VALUES (${item.skuId}::uuid, ${vendorId}, ${quantityTotal}, 0)
    ON CONFLICT (sku_id) DO NOTHING
  `)

  // ② lock the now-guaranteed row (serializes with reserve/commitStock FOR UPDATE)
  const itemRes = await tx.execute(sql`
    SELECT quantity_sold
    FROM inventory_item
    WHERE sku_id = ${item.skuId}::uuid
    FOR UPDATE
  `)
  const sold = Number(firstRow(itemRes)?.quantity_sold ?? 0)

  // ③ fresh SUM of ACTIVE holds (divergence 9: active = consumed_at IS NULL AND released_at IS NULL; NO expires_at)
  const sumRes = await tx.execute(sql`
    SELECT COALESCE(SUM(qty), 0)::int AS reserved
    FROM stock_reservation
    WHERE sku_id = ${item.skuId}::uuid
      AND consumed_at IS NULL
      AND released_at IS NULL
  `)
  const reserved = Number(firstRow(sumRes)?.reserved ?? 0)

  // ④ reservation-aware guard (divergence 11). Downward correction below live commitments REJECTS
  //    (never auto-release buyers' holds — UX footgun). null total = unlimited, never guarded.
  if (quantityTotal !== null && quantityTotal < sold + reserved) {
    throw new InventoryValidationError('quantityTotal')
  }

  // ⑤ apply. PRESERVE the existing vendor_id coalesce EXACTLY (null-on-omit is pre-existing behavior).
  await tx.execute(sql`
    UPDATE inventory_item
    SET vendor_id = ${vendorId}, quantity_total = ${quantityTotal}, updated_at = now()
    WHERE sku_id = ${item.skuId}::uuid
  `)
}