import type { JSONColumnType } from 'kysely'
import { sql } from 'kysely'
import { Hex } from 'ox'

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

const catalogId = 'default'
const publishLockKey = 0x66636174

/** Columns of the `funding_catalogs` table. */
export type CatalogTable = db_Schema.FundingCatalog

/** Columns of the `funding_chains` table. */
export type ChainTable = Omit<db_Schema.FundingChain, 'aliases' | 'rpcUrls'> & {
  /** Caller-facing chain aliases stored as JSON. */
  aliases: JSONColumnType<db_Schema.FundingChain['aliases'], string, string>
  /** Public RPC endpoints stored as JSON. */
  rpcUrls: JSONColumnType<db_Schema.FundingChain['rpcUrls'], string, string>
}

/** Columns of the `funding_tokens` table. */
export type TokenTable = db_Schema.FundingToken

/** Columns of the `funding_chain_tokens` table. */
export type ChainTokenTable = db_Schema.FundingChainToken

/** Columns of the `funding_routes` table. */
export type RouteTable = db_Schema.FundingRoute

/** A route row whose optional JSON fields may be absent in validated input. */
export type RouteRow = Omit<db_Schema.FundingRoute, 'capabilities' | 'configuration'> & {
  /** Executable capability; absent and null both mean indicative-only. */
  capabilities?: db_Schema.FundingRoute['capabilities'] | undefined
  /** Provider-specific route configuration; absent and null are equivalent. */
  configuration?: db_Schema.FundingRoute['configuration'] | undefined
}

/** Stored funding catalog rows and version metadata. */
export type Rows = {
  /** Current catalog metadata, or undefined before the first publish. */
  catalog: db_Schema.FundingCatalog | undefined
  /** Chain-scoped funding token rows. */
  chainTokens: readonly db_Schema.FundingChainToken[]
  /** Funding chain rows. */
  chains: readonly db_Schema.FundingChain[]
  /** Funding provider route rows. */
  routes: readonly RouteRow[]
  /** Funding token rows. */
  tokens: readonly db_Schema.FundingToken[]
}

/** Reads the current funding catalog version metadata. */
export async function head(db: Db.Db): Promise<db_Schema.FundingCatalog | undefined> {
  return db.kysely
    .selectFrom('funding_catalogs')
    .selectAll()
    .where('id', '=', catalogId)
    .executeTakeFirst()
}

/** Reads every row in the current funding catalog. */
export function read(db: Db.Db): Promise<Rows> {
  return db.transaction(async (tx) => {
    await sql`SELECT pg_advisory_xact_lock(${publishLockKey})`.execute(tx.kysely)
    const catalog = await head(tx)
    const chains = await tx.kysely.selectFrom('funding_chains').selectAll().orderBy('id').execute()
    const tokens = await tx.kysely.selectFrom('funding_tokens').selectAll().orderBy('id').execute()
    const chainTokens = await tx.kysely
      .selectFrom('funding_chain_tokens')
      .selectAll()
      .orderBy('chainId')
      .orderBy('tokenId')
      .execute()
    const routes = await tx.kysely
      .selectFrom('funding_routes')
      .selectAll()
      .orderBy('providerId')
      .orderBy('sourceChainId')
      .orderBy('sourceTokenId')
      .orderBy('destinationChainId')
      .orderBy('destinationTokenId')
      .execute()
    return { catalog, chainTokens, chains, routes, tokens }
  })
}

/** Replaces the complete funding catalog and advances its version. */
export function publish(db: Db.Db, input: publish.Input): Promise<db_Schema.FundingCatalog> {
  return db.transaction(async (tx) => {
    await sql`SELECT pg_advisory_xact_lock(${publishLockKey})`.execute(tx.kysely)
    const catalog = {
      id: catalogId,
      updatedAt: new Date().toISOString(),
      version: nextVersion(),
    }

    await tx.kysely.deleteFrom('funding_routes').execute()
    await tx.kysely.deleteFrom('funding_chain_tokens').execute()
    await tx.kysely.deleteFrom('funding_tokens').execute()
    await tx.kysely.deleteFrom('funding_chains').execute()

    if (input.chains.length > 0)
      await tx.kysely
        .insertInto('funding_chains')
        .values(
          input.chains.map((chain) => ({
            ...chain,
            aliases: JSON.stringify(chain.aliases),
            rpcUrls: JSON.stringify(chain.rpcUrls),
          })),
        )
        .execute()
    if (input.tokens.length > 0)
      await tx.kysely.insertInto('funding_tokens').values(input.tokens).execute()
    if (input.chainTokens.length > 0)
      await tx.kysely.insertInto('funding_chain_tokens').values(input.chainTokens).execute()
    if (input.routes.length > 0)
      await tx.kysely
        .insertInto('funding_routes')
        .values(
          input.routes.map((route) => ({
            ...route,
            capabilities: route.capabilities ?? null,
            configuration: route.configuration ?? null,
          })),
        )
        .execute()

    await tx.kysely
      .insertInto('funding_catalogs')
      .values(catalog)
      .onConflict((oc) =>
        oc.column('id').doUpdateSet({
          updatedAt: catalog.updatedAt,
          version: catalog.version,
        }),
      )
      .execute()
    return catalog
  })
}

export declare namespace publish {
  /** Complete validated funding catalog rows. */
  type Input = {
    /** Chain-scoped funding token rows. */
    chainTokens: readonly db_Schema.FundingChainToken[]
    /** Funding chain rows. */
    chains: readonly db_Schema.FundingChain[]
    /** Funding provider route rows. */
    routes: readonly RouteRow[]
    /** Funding token rows. */
    tokens: readonly db_Schema.FundingToken[]
  }
}

let lastVersionMs = 0

function nextVersion() {
  lastVersionMs = Math.max(Date.now(), lastVersionMs + 1)
  return `${lastVersionMs.toString().padStart(15, '0')}_${Hex.random(4).slice(2)}`
}