import { sql, type JSONColumnType, type Selectable } from 'kysely'

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

/** Columns of the `funding_deposits` table. */
export type Table = Omit<
  db_Schema.FundingDeposit,
  'providerRequestIds' | 'providerTransactionHashes'
> & {
  /** Private provider request identifiers stored as JSON. */
  providerRequestIds: JSONColumnType<readonly string[], string, string>
  /** Private provider transaction references stored as JSON. */
  providerTransactionHashes: JSONColumnType<readonly string[], string, string>
}

/** A stored funding deposit. */
export type Record = Selectable<Table>

/** Ownership filter inherited from the deposit address. */
export type Owner = {
  /** Key environment. */
  environment: Record['environment']
  /** Owning organization id (`org_…`). */
  orgId: string
  /** Attributed project id for project-scoped API keys. */
  projectId?: string | undefined
}

/** Optional filters shared by funding deposit list and count queries. */
export type Filters = {
  /** Reusable source-chain address that received the funds. */
  depositAddress?: string | undefined
  /** Canonical destination token keys accepted by the filter. */
  destinationTokenKeys?: readonly string[] | undefined
  /** Funding provider id. */
  providerId?: string | undefined
  /** Tempo account that receives completed deposits. */
  recipient?: string | undefined
  /** Canonical source chain ids accepted by the filter. */
  sourceChainIds?: readonly string[] | undefined
  /** Canonical source token keys accepted by the filter. */
  sourceTokenKeys?: readonly string[] | undefined
  /** Deposit lifecycle status. */
  status?: Record['status'] | undefined
}

/** Inserts one provider-observed source transfer as a funding deposit. */
export function insert(db: Db.Db, record: Record): Promise<Record> {
  return db.kysely
    .insertInto('funding_deposits')
    .values({
      ...record,
      providerRequestIds: JSON.stringify(record.providerRequestIds),
      providerTransactionHashes: JSON.stringify(record.providerTransactionHashes),
    })
    .returningAll()
    .executeTakeFirstOrThrow()
}

/** Inserts a chain-observed source transfer or returns its existing deposit. */
export async function insertOrGetSource(
  db: Db.Db,
  record: Record & { sourceTransferIndex: number },
): Promise<insertOrGetSource.Result> {
  const inserted = await db.kysely
    .insertInto('funding_deposits')
    .values({
      ...record,
      providerRequestIds: JSON.stringify(record.providerRequestIds),
      providerTransactionHashes: JSON.stringify(record.providerTransactionHashes),
    })
    .onConflict((oc) =>
      oc.columns(['depositAddressId', 'sourceTransactionHash', 'sourceTransferIndex']).doNothing(),
    )
    .returningAll()
    .executeTakeFirst()
  if (inserted) return { record: inserted, type: 'created' }
  const existing = await db.kysely
    .selectFrom('funding_deposits')
    .selectAll()
    .where('depositAddressId', '=', record.depositAddressId)
    .where('sourceTransactionHash', '=', record.sourceTransactionHash)
    .where('sourceTransferIndex', '=', record.sourceTransferIndex)
    .executeTakeFirstOrThrow()
  return { record: existing, type: 'existing' }
}

export declare namespace insertOrGetSource {
  /** Result of idempotently persisting one verified source transfer. */
  type Result = { record: Record; type: 'created' | 'existing' }
}

/** Serializes source attribution for one address and transaction. */
export function withSourceTransaction<const result>(
  db: Db.Db,
  options: withSourceTransaction.Options<result>,
): Promise<result> {
  return db.transaction(async (tx) => {
    const key = `${options.depositAddressId}:${options.sourceTransactionHash.toLowerCase()}`
    await sql`SELECT pg_advisory_xact_lock(hashtextextended(${key}, 0))`.execute(tx.kysely)
    return options.fn(tx)
  })
}

export declare namespace withSourceTransaction {
  /** Source identity and transaction-scoped work protected by its advisory lock. */
  type Options<result> = {
    /** Funding deposit address that received the transfer. */
    depositAddressId: string
    /** Database work serialized for the source transaction. */
    fn: (db: Db.Db) => Promise<result>
    /** Source transaction whose deposit attribution must not race. */
    sourceTransactionHash: string
  }
}

/** Gets deposits created from one provider-observed source transaction. */
export function listByProviderObservation(
  db: Db.Db,
  options: listByProviderObservation.Options,
): Promise<Record[]> {
  return db.kysely
    .selectFrom('funding_deposits')
    .selectAll()
    .where('depositAddressId', '=', options.depositAddressId)
    .where('providerRequestId', '=', options.providerRequestId)
    .where('sourceTransactionHash', '=', options.sourceTransactionHash)
    .orderBy('providerTransferIndex', 'asc')
    .execute()
}

export declare namespace listByProviderObservation {
  /** Provider observation fields that identify one source transaction. */
  type Options = {
    /** Funding deposit address that received the transfer. */
    depositAddressId: string
    /** Provider request that reported the transfer. */
    providerRequestId: string
    /** Provider-observed source transaction reference. */
    sourceTransactionHash: string
  }
}

/** Attaches first-observation timestamps to every deposit from one provider request. */
export function recordRequestObservation(
  db: Db.Db,
  options: recordRequestObservation.Options,
): Promise<Record[]> {
  return db.kysely
    .updateTable('funding_deposits')
    .set({
      ...(options.pollObservedAt
        ? {
            pollObservedAt: sql<string>`LEAST(COALESCE(poll_observed_at, ${options.pollObservedAt}), ${options.pollObservedAt})`,
          }
        : {}),
      ...(options.webhookReceivedAt
        ? {
            webhookReceivedAt: sql<string>`LEAST(COALESCE(webhook_received_at, ${options.webhookReceivedAt}), ${options.webhookReceivedAt})`,
          }
        : {}),
    })
    .where('depositAddressId', '=', options.depositAddressId)
    .where('providerRequestId', '=', options.providerRequestId)
    .returningAll()
    .execute()
}

export declare namespace recordRequestObservation {
  /** First observations associated with one provider request. */
  type Options = {
    /** Funding deposit address id (`fda_…`). */
    depositAddressId: string
    /** When polling first observed the provider request. */
    pollObservedAt?: string | undefined
    /** Provider request identifier. */
    providerRequestId: string
    /** When Tempo first received an authenticated provider webhook. */
    webhookReceivedAt?: string | undefined
  }
}

/** Gets deposits associated with one address and source transaction. */
export function listBySourceTransaction(
  db: Db.Db,
  options: listBySourceTransaction.Options,
): Promise<Record[]> {
  return db.kysely
    .selectFrom('funding_deposits')
    .selectAll()
    .where('depositAddressId', '=', options.depositAddressId)
    .where('sourceTransactionHash', '=', options.sourceTransactionHash)
    .orderBy('sourceTransferIndex', 'asc')
    .execute()
}

export declare namespace listBySourceTransaction {
  /** Source transaction fields scoped to one deposit address. */
  type Options = {
    /** Funding deposit address that received the transfer. */
    depositAddressId: string
    /** Provider-observed source transaction reference. */
    sourceTransactionHash: string
  }
}

/** Lists nonterminal source transfers that await provider attribution. */
export function listUnattributed(db: Db.Db, depositAddressId: string): Promise<Record[]> {
  return db.kysely
    .selectFrom('funding_deposits')
    .selectAll()
    .where('depositAddressId', '=', depositAddressId)
    .where('providerRequestId', 'is', null)
    .where('status', 'not in', ['completed', 'refunded'])
    .orderBy('createdAt', 'asc')
    .orderBy('id', 'asc')
    .execute()
}

/** Returns whether an address has a source transfer awaiting provider attribution. */
export async function hasUnattributed(db: Db.Db, depositAddressId: string): Promise<boolean> {
  return Boolean(
    await db.kysely
      .selectFrom('funding_deposits')
      .select('id')
      .where('depositAddressId', '=', depositAddressId)
      .where('providerRequestId', 'is', null)
      .limit(1)
      .executeTakeFirst(),
  )
}

/** Gets a funding deposit without ownership scoping. */
export function get(db: Db.Db, id: string): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('funding_deposits')
    .selectAll()
    .where('id', '=', id)
    .executeTakeFirst()
}

/** Gets an owner-visible funding deposit. */
export function getOwned(db: Db.Db, owner: Owner, id: string): Promise<Record | undefined> {
  let query = db.kysely
    .selectFrom('funding_deposits')
    .selectAll()
    .where('environment', '=', owner.environment)
    .where('id', '=', id)
    .where('orgId', '=', owner.orgId)
  if (owner.projectId !== undefined) query = query.where('projectId', '=', owner.projectId)
  return query.executeTakeFirst()
}

/** Lists owner-visible funding deposits newest-first. */
export function list(db: Db.Db, options: list.Options): Promise<Record[]> {
  const { cursor, limit } = options
  let builder = query(db, options).selectAll('funding_deposits')
  if (cursor)
    builder = builder.where((eb) =>
      eb.or([
        eb('funding_deposits.createdAt', '<', cursor.createdAt),
        eb.and([
          eb('funding_deposits.createdAt', '=', cursor.createdAt),
          eb('funding_deposits.id', '<', cursor.id),
        ]),
      ]),
    )
  return builder
    .orderBy('funding_deposits.createdAt', 'desc')
    .orderBy('funding_deposits.id', 'desc')
    .limit(limit)
    .execute()
}

export declare namespace list {
  /** Ownership and paging options for funding deposits. */
  type Options = Filters & {
    /** Exclusive lower bound from the previous page. */
    cursor?: listByAddress.Cursor | undefined
    /** Maximum rows to return. */
    limit: number
    /** Ownership filter derived from the authenticated API key. */
    owner: Owner
  }
}

/** Counts owner-visible funding deposits through the list's shared filters. */
export async function count(db: Db.Db, options: count.Options): Promise<number> {
  const rows = query(db, options)
    .select('funding_deposits.id')
    .limit(options.cap + 1)
    .as('rows')
  const result = await db.kysely
    .selectFrom(rows)
    .select(sql<number>`count(*)::int`.as('count'))
    .executeTakeFirstOrThrow()
  return result.count
}

export declare namespace count {
  /** Ownership, filter, and cap options for counting funding deposits. */
  type Options = Filters & {
    /** Maximum exact count before returning the cap plus one. */
    cap: number
    /** Ownership filter derived from the authenticated API key. */
    owner: Owner
  }
}

function query(db: Db.Db, options: query.Options) {
  const owner = options.owner
  let builder = db.kysely
    .selectFrom('funding_deposits')
    .innerJoin(
      'funding_deposit_addresses',
      'funding_deposit_addresses.id',
      'funding_deposits.depositAddressId',
    )
    .where('funding_deposit_addresses.environment', '=', owner.environment)
    .where('funding_deposit_addresses.orgId', '=', owner.orgId)
    .where('funding_deposits.environment', '=', owner.environment)
    .where('funding_deposits.orgId', '=', owner.orgId)
  if (owner.projectId !== undefined)
    builder = builder
      .where('funding_deposit_addresses.projectId', '=', owner.projectId)
      .where('funding_deposits.projectId', '=', owner.projectId)
  if (options.depositAddress !== undefined) {
    const address = /^0x[0-9a-fA-F]{40}$/.test(options.depositAddress)
      ? options.depositAddress.toLowerCase()
      : options.depositAddress
    builder = builder.where('funding_deposit_addresses.address', '=', address)
  }
  if (options.destinationTokenKeys !== undefined)
    builder = options.destinationTokenKeys.length
      ? builder.where(
          'funding_deposit_addresses.destinationTokenKey',
          'in',
          options.destinationTokenKeys,
        )
      : builder.where(sql<boolean>`false`)
  if (options.providerId !== undefined)
    builder = builder.where('funding_deposit_addresses.providerId', '=', options.providerId)
  if (options.recipient !== undefined)
    builder = builder.where(
      sql<boolean>`lower(funding_deposit_addresses.recipient) = ${options.recipient.toLowerCase()}`,
    )
  if (options.sourceChainIds !== undefined)
    builder = options.sourceChainIds.length
      ? builder.where('funding_deposit_addresses.sourceChainId', 'in', options.sourceChainIds)
      : builder.where(sql<boolean>`false`)
  if (options.sourceTokenKeys !== undefined)
    builder = options.sourceTokenKeys.length
      ? builder.where('funding_deposit_addresses.sourceTokenKey', 'in', options.sourceTokenKeys)
      : builder.where(sql<boolean>`false`)
  if (options.status !== undefined)
    builder = builder.where('funding_deposits.status', '=', options.status)
  return builder
}

declare namespace query {
  type Options = Filters & {
    owner: Owner
  }
}

/** Lists one owner-visible address's deposits newest-first. */
export function listByAddress(
  db: Db.Db,
  owner: Owner,
  addressId: string,
  options: listByAddress.Options,
): Promise<Record[]> {
  const { cursor, limit } = options
  let query = db.kysely
    .selectFrom('funding_deposits')
    .selectAll()
    .where('depositAddressId', '=', addressId)
    .where('environment', '=', owner.environment)
    .where('orgId', '=', owner.orgId)
  if (owner.projectId !== undefined) query = query.where('projectId', '=', owner.projectId)
  if (cursor)
    query = query.where((eb) =>
      eb.or([
        eb('createdAt', '<', cursor.createdAt),
        eb.and([eb('createdAt', '=', cursor.createdAt), eb('id', '<', cursor.id)]),
      ]),
    )
  return query.orderBy('createdAt', 'desc').orderBy('id', 'desc').limit(limit).execute()
}

export declare namespace listByAddress {
  /** Cursor fields for the last deposit returned by the previous page. */
  type Cursor = {
    /** Deposit creation time. */
    createdAt: string
    /** Deposit id, used as a deterministic tie-breaker. */
    id: string
  }

  /** Paging options for one address's deposits. */
  type Options = {
    /** Exclusive lower bound from the previous page. */
    cursor?: Cursor | undefined
    /** Maximum rows to return. */
    limit: number
  }
}

/** Lists deposits for one reusable source-chain address newest-first. */
export function listByDepositAddress(
  db: Db.Db,
  options: listByDepositAddress.Options,
): Promise<Record[]> {
  const { cursor, depositAddress, limit, owner } = options
  const address = /^0x[0-9a-fA-F]{40}$/.test(depositAddress)
    ? depositAddress.toLowerCase()
    : depositAddress
  let query = db.kysely
    .selectFrom('funding_deposits')
    .innerJoin(
      'funding_deposit_addresses',
      'funding_deposit_addresses.id',
      'funding_deposits.depositAddressId',
    )
    .selectAll('funding_deposits')
    .where('funding_deposit_addresses.address', '=', address)
    .where('funding_deposit_addresses.environment', '=', owner.environment)
    .where('funding_deposit_addresses.orgId', '=', owner.orgId)
    .where('funding_deposits.environment', '=', owner.environment)
    .where('funding_deposits.orgId', '=', owner.orgId)
  if (owner.projectId !== undefined)
    query = query
      .where('funding_deposit_addresses.projectId', '=', owner.projectId)
      .where('funding_deposits.projectId', '=', owner.projectId)
  if (cursor)
    query = query.where((eb) =>
      eb.or([
        eb('funding_deposits.createdAt', '<', cursor.createdAt),
        eb.and([
          eb('funding_deposits.createdAt', '=', cursor.createdAt),
          eb('funding_deposits.id', '<', cursor.id),
        ]),
      ]),
    )
  return query
    .orderBy('funding_deposits.createdAt', 'desc')
    .orderBy('funding_deposits.id', 'desc')
    .limit(limit)
    .execute()
}

export declare namespace listByDepositAddress {
  /** Deposit address filter, ownership, and paging options. */
  type Options = {
    /** Exclusive lower bound from the previous page. */
    cursor?: listByAddress.Cursor | undefined
    /** Reusable source-chain address that received the funds. */
    depositAddress: string
    /** Maximum rows to return. */
    limit: number
    /** Ownership filter derived from the authenticated API key. */
    owner: Owner
  }
}

/** Lists one recipient's owner-visible deposits newest-first. */
export function listByRecipient(db: Db.Db, options: listByRecipient.Options): Promise<Record[]> {
  const { cursor, limit, owner, recipient } = options
  let query = db.kysely
    .selectFrom('funding_deposits')
    .selectAll()
    .where('environment', '=', owner.environment)
    .where('orgId', '=', owner.orgId)
    .where(sql<boolean>`lower(snapshot->>'recipient') = ${recipient.toLowerCase()}`)
  if (owner.projectId !== undefined) query = query.where('projectId', '=', owner.projectId)
  if (cursor)
    query = query.where((eb) =>
      eb.or([
        eb('createdAt', '<', cursor.createdAt),
        eb.and([eb('createdAt', '=', cursor.createdAt), eb('id', '<', cursor.id)]),
      ]),
    )
  return query.orderBy('createdAt', 'desc').orderBy('id', 'desc').limit(limit).execute()
}

export declare namespace listByRecipient {
  /** Recipient filter, ownership, and paging options. */
  type Options = {
    /** Exclusive lower bound from the previous page. */
    cursor?: listByAddress.Cursor | undefined
    /** Maximum rows to return. */
    limit: number
    /** Ownership filter derived from the authenticated API key. */
    owner: Owner
    /** Tempo account that receives completed deposits. */
    recipient: string
  }
}

/** Applies a version-guarded deposit update. */
export function update(db: Db.Db, options: update.Options): Promise<Record | undefined> {
  return db.kysely
    .updateTable('funding_deposits')
    .set({
      providerOutputAmount: options.providerOutputAmount,
      providerRequestId: options.providerRequestId,
      providerRequestIds: JSON.stringify(options.providerRequestIds),
      providerState: options.providerState,
      providerTransactionHashes: JSON.stringify(options.providerTransactionHashes),
      providerTransferIndex: options.providerTransferIndex,
      retryState: options.retryState,
      settlementTransaction: options.settlementTransaction,
      settlementTransactionHash: options.settlementTransactionHash,
      snapshot: options.snapshot,
      sourceTransferIndex: options.sourceTransferIndex,
      status: options.status,
      statusReason: options.statusReason,
      subsidyAmount: options.subsidyAmount,
      tempoGasPaid: options.tempoGasPaid,
      updatedAt: options.updatedAt,
      version: options.version,
    })
    .where('id', '=', options.id)
    .where('version', '=', options.expectedVersion)
    .returningAll()
    .executeTakeFirst()
}

export declare namespace update {
  /** Guarded deposit update fields. */
  type Options = {
    /** Stored version required for the update. */
    expectedVersion: number
    /** Funding deposit id (`fdp_…`). */
    id: string
    /** Provider output amount confirmed on Tempo. */
    providerOutputAmount: Record['providerOutputAmount']
    /** Provider request that first reported the deposit. */
    providerRequestId: Record['providerRequestId']
    /** Private provider request identifiers. */
    providerRequestIds: Record['providerRequestIds']
    /** Bounded private provider state. */
    providerState: Record['providerState']
    /** Provider-leg transaction references. */
    providerTransactionHashes: Record['providerTransactionHashes']
    /** Deposit position within the provider request. */
    providerTransferIndex: Record['providerTransferIndex']
    /** Bounded private retry state. */
    retryState: Record['retryState']
    /** Persisted settlement transaction bytes for exactly-once rebroadcast. */
    settlementTransaction: Record['settlementTransaction']
    /** Persisted settlement transaction hash. */
    settlementTransactionHash: Record['settlementTransactionHash']
    /** Replacement public snapshot. */
    snapshot: Record['snapshot']
    /** Verified transfer position in the source transaction, or null. */
    sourceTransferIndex: Record['sourceTransferIndex']
    /** Replacement lifecycle status. */
    status: Record['status']
    /** Replacement customer-safe reason. */
    statusReason: Record['statusReason']
    /** Destination token amount supplied by Tempo. */
    subsidyAmount: Record['subsidyAmount']
    /** Tempo gas paid for settlement, in base units. */
    tempoGasPaid: Record['tempoGasPaid']
    /** New material update time. */
    updatedAt: string
    /** New material version. */
    version: number
  }
}