import { sql, type ColumnType, type JSONColumnType, type Selectable } from 'kysely'
import type { Address, Hex } from 'viem'
import { customAlphabet } from 'nanoid'

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

const id = customAlphabet('0123456789abcdefghijklmnopqrstuvwxyz', 24)

/** Columns of the `reward_transaction_attempts` table. */
export type Table = Omit<db_Schema.RewardTransactionAttempt, 'chainId' | 'intent' | 'receipt'> & {
  /** Bigint chain id, read as a string from pg and written as a number. */
  chainId: ColumnType<string, number, number>
  /** Typed transaction intent. */
  intent: JSONColumnType<Campaigns.TransactionIntent, string, string>
  /** Canonical receipt evidence. */
  receipt: JSONColumnType<Campaigns.TransactionReceipt | null, string | null, string | null>
}

/** A stored signed transaction attempt. */
export type Record = db_Schema.RewardTransactionAttempt

/** Creates a typed attempt before requesting a signature. */
export async function create(db: Db.Db, options: create.Options): Promise<Record> {
  const now = new Date().toISOString()
  const row = await db.kysely
    .insertInto('reward_transaction_attempts')
    .values({
      chainId: options.chainId,
      confirmedBlockHash: null,
      createdAt: now,
      expiresAt: null,
      id: `rat_${id()}`,
      intent: JSON.stringify(options.intent),
      intentId: options.intent.id,
      nonce: null,
      receipt: null,
      replacementOf: options.replacementOf ?? null,
      runId: options.runId ?? null,
      signedBytes: null,
      signer: normalize(options.signer),
      state: 'created',
      transactionHash: null,
      updatedAt: now,
      vaultAddress: normalize(options.vaultAddress),
    })
    .returningAll()
    .executeTakeFirstOrThrow()
  return toRecord(row)
}

export declare namespace create {
  /** Typed transaction attempt input. */
  type Options = {
    /** Chain receiving the transaction. */
    chainId: number
    /** Typed signer intent. */
    intent: Campaigns.TransactionIntent
    /** Replaced attempt id. */
    replacementOf?: string | undefined
    /** Owning reward run id. */
    runId?: string | undefined
    /** Signing account. */
    signer: Address
    /** Bound EarnVault. */
    vaultAddress: Address
  }
}

/** Persists exact signed bytes before broadcast. */
export async function recordSignature(
  db: Db.Db,
  options: recordSignature.Options,
): Promise<Record | undefined> {
  const row = await db.kysely
    .updateTable('reward_transaction_attempts')
    .set({
      expiresAt: options.expiresAt,
      nonce: options.nonce,
      signedBytes: options.signedBytes,
      state: 'signed',
      transactionHash: options.transactionHash,
      updatedAt: new Date().toISOString(),
    })
    .where('id', '=', options.id)
    .where('state', '=', 'created')
    .returningAll()
    .executeTakeFirst()
  return row ? toRecord(row) : undefined
}

export declare namespace recordSignature {
  /** Signed transaction evidence. */
  type Options = {
    /** Expiring nonce deadline (ISO 8601). */
    expiresAt: string | null
    /** Attempt id. */
    id: string
    /** Account nonce. */
    nonce: string | null
    /** Exact signed transaction bytes. */
    signedBytes: Hex
    /** Hash derived from the signed bytes. */
    transactionHash: Hex
  }
}

/** Marks a signed attempt as broadcast without changing its bytes. */
export async function recordBroadcast(db: Db.Db, id: string): Promise<Record | undefined> {
  const row = await db.kysely
    .updateTable('reward_transaction_attempts')
    .set({ state: 'broadcast', updatedAt: new Date().toISOString() })
    .where('id', '=', id)
    .where('state', 'in', ['signed', 'broadcast'])
    .returningAll()
    .executeTakeFirst()
  return row ? toRecord(row) : undefined
}

/** Marks an unconfirmed expiring-nonce attempt terminal before replacement. */
export async function expire(db: Db.Db, id: string): Promise<Record | undefined> {
  const row = await db.kysely
    .updateTable('reward_transaction_attempts')
    .set({ state: 'expired', updatedAt: new Date().toISOString() })
    .where('id', '=', id)
    .where('state', 'in', ['signed', 'broadcast'])
    .where('expiresAt', '<=', new Date().toISOString())
    .returningAll()
    .executeTakeFirst()
  return row ? toRecord(row) : undefined
}

/** Records a canonical terminal receipt. */
export async function confirm(db: Db.Db, options: confirm.Options): Promise<Record | undefined> {
  const row = await db.kysely
    .updateTable('reward_transaction_attempts')
    .set({
      confirmedBlockHash: options.receipt.blockHash,
      ...(options.intent ? { intent: JSON.stringify(options.intent) } : {}),
      receipt: JSON.stringify(options.receipt),
      state: options.receipt.status === 'success' ? 'confirmed' : 'reverted',
      updatedAt: new Date().toISOString(),
    })
    .where('id', '=', options.id)
    .returningAll()
    .executeTakeFirst()
  return row ? toRecord(row) : undefined
}

export declare namespace confirm {
  /** Confirmed receipt evidence. */
  type Options = {
    /** Attempt id. */
    id: string
    /** Compact terminal intent retained after exact bytes can no longer be retried. */
    intent?: Campaigns.TransactionIntent | undefined
    /** Canonical transaction receipt. */
    receipt: Campaigns.TransactionReceipt
  }
}

/** Marks a confirmed transaction that produced no requested state change as terminal. */
export async function markIneffective(db: Db.Db, id: string): Promise<Record | undefined> {
  const row = await db.kysely
    .updateTable('reward_transaction_attempts')
    .set({ state: 'ineffective', updatedAt: new Date().toISOString() })
    .where('id', '=', id)
    .where('state', '=', 'confirmed')
    .returningAll()
    .executeTakeFirst()
  return row ? toRecord(row) : undefined
}

/** Reads one transaction attempt. */
export async function get(db: Db.Db, id: string): Promise<Record | undefined> {
  const row = await db.kysely
    .selectFrom('reward_transaction_attempts')
    .selectAll()
    .where('id', '=', id)
    .executeTakeFirst()
  return row ? toRecord(row) : undefined
}

/** Reads the newest attempt for one deterministic intent within its execution. */
export async function latestForIntent(
  db: Db.Db,
  options: latestForIntent.Options,
): Promise<Record | undefined> {
  let query = db.kysely
    .selectFrom('reward_transaction_attempts')
    .selectAll()
    .where('intentId', '=', options.intentId)
  query = options.runId
    ? query.where('runId', '=', options.runId)
    : query.where('runId', 'is', null)
  const row = await query
    .orderBy(
      sql<number>`CASE state WHEN 'confirmed' THEN 0 WHEN 'broadcast' THEN 1 WHEN 'signed' THEN 2 WHEN 'created' THEN 3 WHEN 'reverted' THEN 4 WHEN 'ineffective' THEN 5 ELSE 6 END`,
      'asc',
    )
    .orderBy('createdAt', 'desc')
    .executeTakeFirst()
  return row ? toRecord(row) : undefined
}

export declare namespace latestForIntent {
  /** Intent and execution identity. */
  type Options = {
    /** Deterministic typed intent id. */
    intentId: Hex
    /** Owning reward run, omitted only for campaign provisioning. */
    runId?: string | undefined
  }
}

/** Lists confirmed transaction evidence for one run in creation order. */
export async function confirmedForRun(db: Db.Db, runId: string): Promise<Record[]> {
  const rows = await db.kysely
    .selectFrom('reward_transaction_attempts')
    .selectAll()
    .where('runId', '=', runId)
    .where('state', '=', 'confirmed')
    .orderBy('createdAt', 'asc')
    .execute()
  return rows.map(toRecord)
}

/** Lists every durable transaction attempt for one run in creation order. */
export async function listForRun(db: Db.Db, runId: string): Promise<Record[]> {
  const rows = await db.kysely
    .selectFrom('reward_transaction_attempts')
    .selectAll()
    .where('runId', '=', runId)
    .orderBy('createdAt', 'asc')
    .execute()
  return rows.map(toRecord)
}

/** Reads the newest nonterminal or confirmed attempt recoverable by one run operation. */
export async function latestRecoverable(
  db: Db.Db,
  options: latestRecoverable.Options,
): Promise<Record | undefined> {
  const row = await db.kysely
    .selectFrom('reward_transaction_attempts')
    .selectAll()
    .where('runId', '=', options.runId)
    .where('state', 'in', ['created', 'signed', 'broadcast', 'confirmed'])
    .where(sql<boolean>`intent ->> 'operation' = ${options.operation}`)
    .orderBy('createdAt', 'desc')
    .executeTakeFirst()
  return row ? toRecord(row) : undefined
}

export declare namespace latestRecoverable {
  /** Run operation selector. */
  type Options = {
    /** Typed signer operation. */
    operation: Campaigns.TransactionIntent['operation']
    /** Owning reward run. */
    runId: string
  }
}

function normalize(address: string): Address {
  return address.toLowerCase() as Address
}

function toRecord(row: Selectable<Table>): Record {
  return { ...row, chainId: Number(row.chainId) }
}