import type { Querier } from '@platform-modules/db'
import { and, eq, getTableColumns, gte, sql } from 'drizzle-orm'
import type { PgColumn, PgTable } from 'drizzle-orm/pg-core'
import type { LedgerSchema } from './schema.js'
import { walletBalances } from './schema.js'
import type { BalanceTarget } from './types.js'

function columnPropertyName(table: PgTable, column: PgColumn): string {
  const columns = getTableColumns(table)
  for (const [key, col] of Object.entries(columns)) {
    if (col === column) return key
  }
  throw new Error('balanceColumn is not part of the target table')
}

export async function getBalance<S extends LedgerSchema>(
  querier: Querier<S>,
  target: BalanceTarget,
): Promise<bigint> {
  if (target.kind === 'wallet') {
    const [row] = await querier
      .select({ balance: walletBalances.balance })
      .from(walletBalances)
      .where(eq(walletBalances.ownerId, target.ownerId))
      .limit(1)
    return row?.balance ?? 0n
  }

  const [row] = await querier
    .select({ balance: target.balanceColumn })
    .from(target.table)
    .where(target.where)
    .limit(1)

  return coerceBalance(row?.balance) ?? 0n
}

/**
 * Cross-driver bigint coercion. pglite returns a `bigint`; neon-http
 * deserializes a bigint column as a string. Numbers are unexpected for a
 * `{ mode: 'bigint' }` column but coerced for safety. `null`/`undefined`
 * (no row) → `null` so the caller can distinguish "no row" from a real `0`.
 */
function coerceBalance(value: unknown): bigint | null {
  if (typeof value === 'bigint') return value
  if (typeof value === 'string' || typeof value === 'number') return BigInt(value)
  return null
}

export async function guardedDebitUpdate<S extends LedgerSchema>(
  tx: Querier<S>,
  target: BalanceTarget,
  amount: bigint,
): Promise<bigint | null> {
  if (amount <= 0n) {
    throw new Error('guardedDebitUpdate: amount must be positive')
  }

  if (target.kind === 'wallet') {
    const updated = await tx
      .update(walletBalances)
      .set({
        balance: sql`${walletBalances.balance} - ${amount}`,
        updatedAt: new Date(),
      })
      .where(
        and(eq(walletBalances.ownerId, target.ownerId), gte(walletBalances.balance, amount)),
      )
      .returning({ balance: walletBalances.balance })

    if (updated.length === 0) return null
    return coerceBalance(updated[0]?.balance)
  }

  const balanceKey = columnPropertyName(target.table, target.balanceColumn)
  const updated = await tx
    .update(target.table)
    .set({
      [balanceKey]: sql`${target.balanceColumn} - ${amount}`,
    } as Record<string, unknown>)
    .where(and(target.where, gte(target.balanceColumn, amount)))
    .returning({ balance: target.balanceColumn })

  // 0 rows ⇒ guard failed (insufficient) ⇒ null. A returned row is a genuine
  // success — coerce its balance across drivers, never treat a string-typed
  // (neon-http) bigint as a guard failure.
  if (updated.length === 0) return null
  return coerceBalance(updated[0]?.balance)
}