import { sql, type JSONColumnType, type Selectable } from 'kysely'
import * as pg from 'pg'

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

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

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

/** Ownership filter derived from the authenticated API key. */
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
}

/** Identity fields that make one reusable address match a caller request. */
export type Match = Owner & {
  /** Customer destination token key. */
  destinationTokenKey: string
  /** Tempo account that receives completed deposits. */
  recipient: string
  /** Source-chain account that receives refunds. */
  refundAddress: string
  /** Source chain CAIP-2 id. */
  sourceChainId: string
  /** Source token key. */
  sourceTokenKey: string
  /** Whether Tempo guarantees normalized 1:1 delivery. */
  subsidize: boolean
}

/** Inserts a provisioned funding deposit address. */
export function insert(db: Db.Db, record: Record): Promise<Record> {
  return db.kysely
    .insertInto('funding_deposit_addresses')
    .values({
      ...record,
      providerRequestIds: JSON.stringify(record.providerRequestIds),
    })
    .returningAll()
    .executeTakeFirstOrThrow()
}

/** Inserts an address or returns the concurrently-created matching resource. */
export async function insertOrGet(db: Db.Db, options: insertOrGet.Options): Promise<Record> {
  const record = await db.kysely
    .insertInto('funding_deposit_addresses')
    .values({
      ...options.record,
      providerRequestIds: JSON.stringify(options.record.providerRequestIds),
    })
    .onConflict((oc) =>
      oc
        .columns([
          'orgId',
          'environment',
          'projectId',
          'sourceChainId',
          'sourceTokenKey',
          'destinationTokenKey',
          'recipient',
          'refundAddress',
          'subsidize',
        ])
        .doNothing(),
    )
    .returningAll()
    .executeTakeFirst()
    .catch((cause) => {
      if (
        cause instanceof pg.DatabaseError &&
        cause.code === '23505' &&
        cause.constraint === 'funding_deposit_addresses_provider_address_key'
      )
        throw new ConflictError()
      throw cause
    })
  if (record) return record
  const existing = await getMatching(db, options.match)
  if (existing) return existing
  throw new ConflictError()
}

export declare namespace insertOrGet {
  /** Record and stable identity used by create-or-get. */
  type Options = {
    /** Stable customer-visible address identity. */
    match: Match
    /** Provisioned address record to insert. */
    record: Record
  }
}

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

/** Gets a funding deposit address by its provider-owned address. */
export function getByProviderAddress(
  db: Db.Db,
  options: getByProviderAddress.Options,
): Promise<Record | undefined> {
  const address = /^0x[0-9a-fA-F]{40}$/.test(options.address)
    ? options.address.toLowerCase()
    : options.address
  return db.kysely
    .selectFrom('funding_deposit_addresses')
    .selectAll()
    .where('address', '=', address)
    .where('providerId', '=', options.providerId)
    .executeTakeFirst()
}

export declare namespace getByProviderAddress {
  /** Provider address identity received from a reconciliation trigger. */
  type Options = {
    /** Provider-owned reusable deposit address. */
    address: string
    /** Funding provider id. */
    providerId: string
  }
}

/** Gets an owner-visible funding deposit address. */
export function getOwned(db: Db.Db, owner: Owner, id: string): Promise<Record | undefined> {
  let query = db.kysely
    .selectFrom('funding_deposit_addresses')
    .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 deposit addresses newest-first. */
export function list(db: Db.Db, options: list.Options): Promise<Record[]> {
  const { cursor, limit, owner } = options
  let query = db.kysely
    .selectFrom('funding_deposit_addresses')
    .selectAll()
    .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 list {
  /** Ownership and paging options for funding deposit addresses. */
  type Options = {
    /** Exclusive lower bound from the previous page. */
    cursor?: Cursor | undefined
    /** Maximum rows to return. */
    limit: number
    /** Ownership filter derived from the authenticated API key. */
    owner: Owner
  }

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

/** Gets the reusable address that matches one owner and route identity. */
export function getMatching(db: Db.Db, match: Match): Promise<Record | undefined> {
  let query = db.kysely
    .selectFrom('funding_deposit_addresses')
    .selectAll()
    .where('destinationTokenKey', '=', match.destinationTokenKey)
    .where('environment', '=', match.environment)
    .where('orgId', '=', match.orgId)
    .where('recipient', '=', match.recipient)
    .where('refundAddress', '=', match.refundAddress)
    .where('sourceChainId', '=', match.sourceChainId)
    .where('sourceTokenKey', '=', match.sourceTokenKey)
    .where('subsidize', '=', match.subsidize)
  query =
    match.projectId === undefined
      ? query.where('projectId', 'is', null)
      : query.where('projectId', '=', match.projectId)
  return query.executeTakeFirst()
}

/** Lists active provider addresses for one source-chain token. */
export function listActiveSources(
  db: Db.Db,
  options: listActiveSources.Options,
): Promise<readonly Record[]> {
  return db.kysely
    .selectFrom('funding_deposit_addresses')
    .selectAll()
    .where('providerId', '=', options.providerId)
    .where('sourceChainId', '=', options.sourceChainId)
    .where('sourceTokenKey', '=', options.sourceTokenKey)
    .where('status', '=', 'active')
    .orderBy('id', 'asc')
    .execute()
}

export declare namespace listActiveSources {
  /** Provider and source asset identifying one observer shard. */
  type Options = {
    /** Funding provider that owns the deposit addresses. */
    providerId: string
    /** Source chain CAIP-2 id. */
    sourceChainId: string
    /** Source token key. */
    sourceTokenKey: string
  }
}

/** Claims a bounded batch of active addresses due for provider reconciliation. */
export function claimDue(db: Db.Db, options: claimDue.Options): Promise<readonly Record[]> {
  return db.transaction(async (tx) => {
    const due = await tx.kysely
      .selectFrom('funding_deposit_addresses')
      .select('id')
      .where('nextPollAt', '<=', options.now)
      .where('providerId', '=', options.providerId)
      .where('status', '=', 'active')
      .where((eb) =>
        eb.or([eb('pollLeaseUntil', 'is', null), eb('pollLeaseUntil', '<=', options.now)]),
      )
      .orderBy('nextPollAt', 'asc')
      .orderBy('id', 'asc')
      .limit(options.limit)
      .forUpdate()
      .skipLocked()
      .execute()
    if (due.length === 0) return []
    return tx.kysely
      .updateTable('funding_deposit_addresses')
      .set({
        pollLeaseUntil: options.leaseUntil,
        pollLeaseVersion: sql<number>`poll_lease_version + 1`,
      })
      .where(
        'id',
        'in',
        due.map((record) => record.id),
      )
      .returningAll()
      .execute()
  })
}

export declare namespace claimDue {
  /** Provider and lease bounds for one reconciliation claim. */
  type Options = {
    /** Exclusive upper bound on rows claimed in one tick. */
    limit: number
    /** Expiry time assigned to each claimed lease. */
    leaseUntil: string
    /** Current time used for due and expired-lease comparisons. */
    now: string
    /** Funding provider whose addresses should be claimed. */
    providerId: string
  }
}

/** Claims one active address unless another reconciliation owns its lease. */
export function claim(db: Db.Db, options: claim.Options): Promise<Record | undefined> {
  return db.kysely
    .updateTable('funding_deposit_addresses')
    .set({
      pollLeaseUntil: options.leaseUntil,
      pollLeaseVersion: sql<number>`poll_lease_version + 1`,
    })
    .where('id', '=', options.id)
    .where('status', '=', 'active')
    .where((eb) =>
      eb.or([eb('pollLeaseUntil', 'is', null), eb('pollLeaseUntil', '<=', options.now)]),
    )
    .returningAll()
    .executeTakeFirst()
}

export declare namespace claim {
  /** Identity and lease bounds for one reconciliation claim. */
  type Options = {
    /** Funding deposit address id (`fda_…`). */
    id: string
    /** Expiry time assigned to the claimed lease. */
    leaseUntil: string
    /** Current time used for expired-lease comparison. */
    now: string
  }
}

/** Extends an active reconciliation lease without changing its fence. */
export async function renewPoll(db: Db.Db, options: renewPoll.Options): Promise<boolean> {
  return Boolean(
    await db.kysely
      .updateTable('funding_deposit_addresses')
      .set({ pollLeaseUntil: options.leaseUntil })
      .where('id', '=', options.id)
      .where('pollLeaseUntil', 'is not', null)
      .where('pollLeaseVersion', '=', options.pollLeaseVersion)
      .where('status', '=', 'active')
      .returning('id')
      .executeTakeFirst(),
  )
}

export declare namespace renewPoll {
  /** Lease identity and renewed expiry guarded by the current fence. */
  type Options = {
    /** Funding deposit address id (`fda_…`). */
    id: string
    /** Replacement reconciliation lease expiry. */
    leaseUntil: string
    /** Fencing token returned by the claim operation. */
    pollLeaseVersion: number
  }
}

/** Completes an owned reconciliation lease and schedules the address again. */
export function completePoll(
  db: Db.Db,
  options: completePoll.Options,
): Promise<Record | undefined> {
  return db.kysely
    .updateTable('funding_deposit_addresses')
    .set({
      lastPolledAt: options.lastPolledAt,
      nextPollAt: options.nextPollAt,
      pollFailureCount: 0,
      pollLeaseUntil: null,
      providerRequestIds: JSON.stringify(options.providerRequestIds),
      providerState: options.providerState,
    })
    .where('id', '=', options.id)
    .where('pollLeaseVersion', '=', options.pollLeaseVersion)
    .returningAll()
    .executeTakeFirst()
}

export declare namespace completePoll {
  /** Successful reconciliation result guarded by the claimed lease version. */
  type Options = {
    /** Funding deposit address id (`fda_…`). */
    id: string
    /** Time when provider reconciliation completed. */
    lastPolledAt: string
    /** Time when this address becomes due again. */
    nextPollAt: string
    /** Fencing token returned by {@link claimDue}. */
    pollLeaseVersion: number
    /** Provider request identifiers retained from address provisioning. */
    providerRequestIds: readonly string[]
    /** Bounded provider reconciliation state. */
    providerState: Record['providerState']
  }
}

/** Releases an owned reconciliation lease after failure and schedules a retry. */
export function failPoll(db: Db.Db, options: failPoll.Options): Promise<Record | undefined> {
  return db.kysely
    .updateTable('funding_deposit_addresses')
    .set({
      nextPollAt: options.nextPollAt,
      pollFailureCount: sql<number>`poll_failure_count + 1`,
      pollLeaseUntil: null,
    })
    .where('id', '=', options.id)
    .where('pollLeaseVersion', '=', options.pollLeaseVersion)
    .returningAll()
    .executeTakeFirst()
}

export declare namespace failPoll {
  /** Failed reconciliation result guarded by the claimed lease version. */
  type Options = {
    /** Funding deposit address id (`fda_…`). */
    id: string
    /** Time when this address becomes eligible for retry. */
    nextPollAt: string
    /** Fencing token returned by {@link claimDue}. */
    pollLeaseVersion: number
  }
}

/** Applies a version-guarded deposit address status update. */
export function update(db: Db.Db, options: update.Options): Promise<Record | undefined> {
  return db.kysely
    .updateTable('funding_deposit_addresses')
    .set({
      status: options.status,
      updatedAt: options.updatedAt,
      version: options.version,
    })
    .where('id', '=', options.id)
    .where('version', '=', options.expectedVersion)
    .returningAll()
    .executeTakeFirst()
}

export declare namespace update {
  /** Guarded address update fields. */
  type Options = {
    /** Stored version required for the update. */
    expectedVersion: number
    /** Funding deposit address id (`fda_…`). */
    id: string
    /** Replacement lifecycle status. */
    status: Record['status']
    /** New material update time. */
    updatedAt: string
    /** New material version. */
    version: number
  }
}

/** Provider address conflicted with a different customer-visible identity. */
export class ConflictError extends Error {
  override name = 'FundingDepositAddresses.ConflictError'
}