import { sql, type ColumnType, type Selectable } from 'kysely'
import type { Hex } from 'ox'
import type { Address } from 'viem'

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

/** Columns of the `reward_eligibility_associations` table. */
export type Table = Omit<
  db_Schema.RewardEligibilityAssociation,
  'chainId' | 'registrationOrder' | 'transactionHash'
> & {
  /** Bigint chain id, read as a string from pg and written as a number. */
  chainId: ColumnType<string, number, number>
  /** Database-assigned monotonic registration order. */
  registrationOrder: ColumnType<string, never, never>
  /** Optional transaction hash, stored as nullable text. */
  transactionHash: Hex.Hex | null
}

/** A stored reward eligibility association. */
export type Record = db_Schema.RewardEligibilityAssociation

/** Inserts or refreshes one eligibility association. */
export async function upsert(db: Db.Db, options: upsert.Options): Promise<upsert.Result> {
  const now = new Date().toISOString()
  const row = await db.kysely
    .insertInto('reward_eligibility_associations')
    .values({
      chainId: options.chainId,
      firstRegisteredAt: now,
      latestRegisteredAt: now,
      transactionHash: options.transactionHash ?? null,
      vaultAddress: normalize(options.vaultAddress),
      walletAddress: normalize(options.walletAddress),
    })
    .onConflict((oc) =>
      oc.columns(['chainId', 'vaultAddress', 'walletAddress']).doUpdateSet({
        firstRegisteredAt: sql<string>`LEAST(reward_eligibility_associations.first_registered_at, ${now})`,
        latestRegisteredAt: sql<string>`GREATEST(reward_eligibility_associations.latest_registered_at, ${now})`,
        transactionHash: sql<Hex.Hex | null>`CASE WHEN reward_eligibility_associations.latest_registered_at <= ${now} THEN COALESCE(${options.transactionHash ?? null}, reward_eligibility_associations.transaction_hash) ELSE reward_eligibility_associations.transaction_hash END`,
      }),
    )
    .returning([
      'chainId',
      'firstRegisteredAt',
      'latestRegisteredAt',
      'registrationOrder',
      'transactionHash',
      'vaultAddress',
      'walletAddress',
    ])
    .returning(sql<boolean>`xmax = 0`.as('created'))
    .executeTakeFirstOrThrow()
  const { created, ...record } = row
  return { created, record: toRecord(record) }
}

export declare namespace upsert {
  /** Fields supplied by one authorized registration source. */
  type Options = {
    /** Chain containing the vault. */
    chainId: number
    /** Transaction establishing the eligibility association, when available. */
    transactionHash?: Hex.Hex | undefined
    /** Earn vault address. */
    vaultAddress: Address
    /** Eligible wallet address. */
    walletAddress: Address
  }

  /** Persisted association and whether this call inserted it. */
  type Result = {
    /** Whether this call inserted the association. */
    created: boolean
    /** Persisted association after the write. */
    record: Record
  }
}

/** Lists one vault's associations within a fixed high-water checkpoint. */
export async function list(db: Db.Db, options: list.Options): Promise<list.Result> {
  const value = options.cursor ? Cursor.decode(options.cursor, ['uint', 'uint']) : undefined
  const decoded = isPaginationCursor(value) ? value : undefined
  const checkpoint = decoded?.[0]
  const after = decoded?.[1]
  const highWater =
    checkpoint !== undefined
      ? checkpoint
      : await db.transaction(async (tx) => {
          await sql`LOCK TABLE ${sql.table('reward_eligibility_associations')} IN SHARE MODE`.execute(
            tx.kysely,
          )
          return (
            (
              await tx.kysely
                .selectFrom('reward_eligibility_associations')
                .select((eb) => eb.fn.max('registrationOrder').as('checkpoint'))
                .where('chainId', '=', String(options.chainId))
                .where('vaultAddress', '=', normalize(options.vaultAddress))
                .executeTakeFirstOrThrow()
            ).checkpoint ?? '0'
          )
        })

  let query = db.kysely
    .selectFrom('reward_eligibility_associations')
    .select([
      'chainId',
      'firstRegisteredAt',
      'latestRegisteredAt',
      'registrationOrder',
      'transactionHash',
      'vaultAddress',
      'walletAddress',
    ])
    .where('chainId', '=', String(options.chainId))
    .where('vaultAddress', '=', normalize(options.vaultAddress))
    .where('registrationOrder', '<=', highWater)
  if (after !== undefined) query = query.where('registrationOrder', '>', after)
  const rows = await query
    .orderBy('registrationOrder', 'asc')
    .limit(options.limit + 1)
    .execute()
  const page = rows.slice(0, options.limit)
  const last = page.at(-1)
  return {
    data: page.map(toRecord),
    nextCursor:
      rows.length > options.limit && last
        ? Cursor.encode([highWater, last.registrationOrder])
        : null,
  }
}

export declare namespace list {
  /** One checkpointed eligibility page selector. */
  type Options = {
    /** Chain containing the vault. */
    chainId: number
    /** Opaque cursor carrying the checkpoint and last registration order. */
    cursor?: string | undefined
    /** Maximum associations to return. */
    limit: number
    /** Earn vault address. */
    vaultAddress: Address
  }

  /** One fixed-snapshot eligibility page. */
  type Result = {
    /** Persisted associations in registration order. */
    data: Record[]
    /** Cursor for the next page, or null at the end. */
    nextCursor: string | null
  }
}

function normalize(address: Address): Address {
  // SAFETY: Lowercasing preserves the validated 20-byte address format.
  return address.toLowerCase() as Address
}

function isPaginationCursor(value: Cursor.Cursor | undefined): value is [string, string] {
  return (
    value?.length === 2 &&
    typeof value[0] === 'string' &&
    typeof value[1] === 'string' &&
    BigInt(value[0]) <= pgBigintMax &&
    BigInt(value[1]) <= pgBigintMax
  )
}

const pgBigintMax = 9_223_372_036_854_775_807n

function toRecord(row: Selectable<Table>): Record {
  const { transactionHash, ...record } = row
  if (!transactionHash) return { ...record, chainId: Number(row.chainId) }
  return {
    ...record,
    chainId: Number(row.chainId),
    transactionHash,
  }
}