import { sql, type Selectable } from 'kysely'

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

/** Columns of the `funding_deposit_request_observations` table. */
export type Table = db_Schema.FundingDepositRequestObservation

/** First Tempo observations of one provider request. */
export type Record = Selectable<Table>

/** Gets observation timestamps for one provider request. */
export function get(db: Db.Db, options: get.Options): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('funding_deposit_request_observations')
    .selectAll()
    .where('depositAddressId', '=', options.depositAddressId)
    .where('providerRequestId', '=', options.providerRequestId)
    .executeTakeFirst()
}

export declare namespace get {
  /** Provider request identity scoped to one reusable address. */
  type Options = {
    /** Funding deposit address id (`fda_…`). */
    depositAddressId: string
    /** Provider request identifier. */
    providerRequestId: string
  }
}

/** Records when polling first observed each provider request. */
export async function observePoll(db: Db.Db, options: observePoll.Options): Promise<Record[]> {
  const providerRequestIds = [...new Set(options.providerRequestIds)]
  if (providerRequestIds.length === 0) return []
  return db.kysely
    .insertInto('funding_deposit_request_observations')
    .values(
      providerRequestIds.map((providerRequestId) => ({
        depositAddressId: options.depositAddressId,
        pollObservedAt: options.observedAt,
        providerRequestId,
        webhookReceivedAt: null,
      })),
    )
    .onConflict((oc) =>
      oc.columns(['depositAddressId', 'providerRequestId']).doUpdateSet({
        pollObservedAt: sql<string>`LEAST(COALESCE(funding_deposit_request_observations.poll_observed_at, excluded.poll_observed_at), excluded.poll_observed_at)`,
      }),
    )
    .returningAll()
    .execute()
}

export declare namespace observePoll {
  /** Provider requests observed in one successful poll response. */
  type Options = {
    /** Funding deposit address id (`fda_…`). */
    depositAddressId: string
    /** When Tempo observed the provider requests. */
    observedAt: string
    /** Provider request identifiers returned by the poll. */
    providerRequestIds: readonly string[]
  }
}

/** Records when Tempo first received an authenticated webhook for a provider request. */
export function observeWebhook(
  db: Db.Db,
  options: observeWebhook.Options,
): Promise<Record | undefined> {
  return db.kysely
    .insertInto('funding_deposit_request_observations')
    .values({
      depositAddressId: options.depositAddressId,
      pollObservedAt: null,
      providerRequestId: options.providerRequestId,
      webhookReceivedAt: options.receivedAt,
    })
    .onConflict((oc) =>
      oc.columns(['depositAddressId', 'providerRequestId']).doUpdateSet({
        webhookReceivedAt: sql<string>`LEAST(COALESCE(funding_deposit_request_observations.webhook_received_at, excluded.webhook_received_at), excluded.webhook_received_at)`,
      }),
    )
    .returningAll()
    .executeTakeFirst()
}

export declare namespace observeWebhook {
  /** Authenticated webhook observation for one provider request. */
  type Options = {
    /** Funding deposit address id (`fda_…`). */
    depositAddressId: string
    /** Provider request identifier. */
    providerRequestId: string
    /** When Tempo received the webhook. */
    receivedAt: string
  }
}