import type { Selectable } from 'kysely'

import type * as Db from '../Db.js'
import type * as db_Schema from '../Schema.js'

/** Columns of the `funding_transfer_transactions` table. */
export type Table = db_Schema.FundingTransferTransaction
/** One verified transaction reference on a transfer. */
export type Record = Selectable<Table>

/**
 * Inserts a transaction reference, lowercasing EVM hashes while preserving
 * other references. Repeats return `undefined`; conflicting source ownership throws.
 */
export function insert(db: Db.Db, record: Record): Promise<Record | undefined> {
  return db.kysely
    .insertInto('funding_transfer_transactions')
    .values({
      ...record,
      transactionRef: normalize(record.chainId, record.transactionRef),
    })
    .onConflict((oc) => oc.columns(['transferId', 'role', 'transactionRef']).doNothing())
    .returningAll()
    .executeTakeFirst()
}

/** Claims a source transaction for one transfer without racing another transfer. */
export async function claimSource(db: Db.Db, record: Record): Promise<claimSource.Result> {
  const transactionRef = normalize(record.chainId, record.transactionRef)
  const inserted = await db.kysely
    .insertInto('funding_transfer_transactions')
    .values({ ...record, transactionRef })
    .onConflict((oc) => oc.doNothing())
    .returningAll()
    .executeTakeFirst()
  if (inserted) return { record: inserted, type: 'claimed' }
  const existing = await getSource(db, {
    chainId: record.chainId,
    transactionRef,
  })
  if (existing?.transferId === record.transferId) return { record: existing, type: 'replayed' }
  return { type: 'conflict' }
}

export declare namespace claimSource {
  /** Source transaction ownership outcome. */
  type Result =
    | { record: Record; type: 'claimed' }
    | { record: Record; type: 'replayed' }
    | { type: 'conflict' }
}

/** Gets the transfer that owns one source transaction reference. */
export function getSource(db: Db.Db, options: getSource.Options): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('funding_transfer_transactions')
    .selectAll()
    .where('chainId', '=', options.chainId)
    .where('role', '=', 'source')
    .where('transactionRef', '=', normalize(options.chainId, options.transactionRef))
    .executeTakeFirst()
}

export declare namespace getSource {
  /** Source transaction identity. */
  type Options = {
    /** CAIP-2 chain containing the transaction. */
    chainId: string
    /** EVM transaction hash or case-sensitive chain reference. */
    transactionRef: string
  }
}

/** Removes one source transaction only when the transfer still owns it. */
export function removeSource(
  db: Db.Db,
  options: removeSource.Options,
): Promise<Record | undefined> {
  return db.kysely
    .deleteFrom('funding_transfer_transactions')
    .where('chainId', '=', options.chainId)
    .where('role', '=', 'source')
    .where('transactionRef', '=', normalize(options.chainId, options.transactionHash))
    .where('transferId', '=', options.transferId)
    .returningAll()
    .executeTakeFirst()
}

export declare namespace removeSource {
  /** Exact source evidence identity and owner. */
  type Options = {
    /** CAIP-2 chain containing the transaction. */
    chainId: string
    /** Source transaction reference. */
    transactionHash: string
    /** Owning transfer id (`ftr_…`). */
    transferId: string
  }
}

/** Lists a transfer's transaction references, oldest first. */
export function listByTransfer(db: Db.Db, transferId: string): Promise<Record[]> {
  return db.kysely
    .selectFrom('funding_transfer_transactions')
    .selectAll()
    .where('transferId', '=', transferId)
    .orderBy('createdAt', 'asc')
    .execute()
}

function normalize(chainId: string, transactionRef: string): string {
  return chainId.startsWith('eip155:') ? transactionRef.toLowerCase() : transactionRef
}