import { type Generated, sql, type Selectable } from 'kysely'

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

/** Columns of the `routes_transfer_transactions` table. */
export type Table = Omit<
  db_Schema.RoutesTransferTransaction,
  'environment' | 'orgId' | 'projectId'
> & {
  /** Environment derived by the database. */
  environment: Generated<string>
  /** Organization derived by the database. */
  orgId: Generated<string>
  /** Project derived by the database. */
  projectId: Generated<string | null>
}
/** One verified transaction reference on a transfer. */
export type Record = Selectable<Table>

/**
 * Inserts a transaction reference, lowercasing EVM hashes while preserving
 * other references. Repeats return `undefined`; conflicting source ownership throws.
 */
export function insert(db: Db.Db, record: insert.Record): Promise<Record | undefined> {
  return db.kysely
    .insertInto('routes_transfer_transactions')
    .values({
      ...record,
      transactionRef: normalize(record.chainId, record.transactionRef),
    })
    .onConflict((oc) => oc.columns(['transferId', 'role', 'transactionRef']).doNothing())
    .returningAll()
    .executeTakeFirst()
}

export declare namespace insert {
  /** Transaction evidence; the database derives tenancy from its transfer. */
  type Record = Omit<Selectable<Table>, 'environment' | 'orgId' | 'projectId'>
}

/** Claims a source transaction once within its transfer's organization, environment, and project. */
export async function claimSource(db: Db.Db, record: insert.Record): Promise<claimSource.Result> {
  const transactionRef = normalize(record.chainId, record.transactionRef)
  const inserted = await db.kysely
    .insertInto('routes_transfer_transactions')
    .values({ ...record, transactionRef })
    .onConflict((oc) => oc.doNothing())
    .returningAll()
    .executeTakeFirst()
  if (inserted) return { record: inserted, type: 'claimed' }
  const existing = await getSource(db, {
    chainId: record.chainId,
    transactionRef,
    transferId: record.transferId,
  })
  if (existing?.transferId === record.transferId) return { record: existing, type: 'replayed' }
  return { type: 'conflict' }
}

export declare namespace claimSource {
  /** Source transaction ownership outcome. */
  type Result =
    | { record: Record; type: 'claimed' }
    | { record: Record; type: 'replayed' }
    | { type: 'conflict' }
}

/** Gets a source claim within the given transfer's organization, environment, and project. */
export function getSource(db: Db.Db, options: getSource.Options): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('routes_transfer_transactions as transaction')
    .innerJoin('routes_transfers as owner', 'owner.id', 'transaction.transferId')
    .innerJoin('routes_transfers as tenant', (join) =>
      join
        .on('tenant.id', '=', options.transferId)
        .onRef('owner.orgId', '=', 'tenant.orgId')
        .onRef('owner.environment', '=', 'tenant.environment')
        .on(sql<boolean>`owner.project_id IS NOT DISTINCT FROM tenant.project_id`),
    )
    .selectAll('transaction')
    .where('transaction.chainId', '=', options.chainId)
    .where('transaction.role', '=', 'source')
    .where('transaction.transactionRef', '=', normalize(options.chainId, options.transactionRef))
    .executeTakeFirst()
}

export declare namespace getSource {
  /** Source transaction identity. */
  type Options = {
    /** CAIP-2 chain containing the transaction. */
    chainId: string
    /** EVM transaction hash or case-sensitive chain reference. */
    transactionRef: string
    /** Transfer whose tenant scope is searched. */
    transferId: string
  }
}

/** Removes one source transaction only when the transfer still owns it. */
export function removeSource(
  db: Db.Db,
  options: removeSource.Options,
): Promise<Record | undefined> {
  return db.kysely
    .deleteFrom('routes_transfer_transactions')
    .where('chainId', '=', options.chainId)
    .where('role', '=', 'source')
    .where('transactionRef', '=', normalize(options.chainId, options.transactionHash))
    .where('transferId', '=', options.transferId)
    .returningAll()
    .executeTakeFirst()
}

export declare namespace removeSource {
  /** Exact source evidence identity and owner. */
  type Options = {
    /** CAIP-2 chain containing the transaction. */
    chainId: string
    /** Source transaction reference. */
    transactionHash: string
    /** Owning transfer id (`rtr_…`). */
    transferId: string
  }
}

/** Removes every remaining source transaction owned by one transfer. */
export function removeSources(db: Db.Db, options: removeSources.Options): Promise<Record[]> {
  return db.kysely
    .deleteFrom('routes_transfer_transactions')
    .where('role', '=', 'source')
    .where('transferId', '=', options.transferId)
    .returningAll()
    .execute()
}

export declare namespace removeSources {
  /** Source transaction owner. */
  type Options = {
    /** Owning transfer id (`rtr_…`). */
    transferId: string
  }
}

/** Lists a transfer's transaction references, oldest first. */
export function listByTransfer(db: Db.Db, transferId: string): Promise<Record[]> {
  return db.kysely
    .selectFrom('routes_transfer_transactions')
    .selectAll()
    .where('transferId', '=', transferId)
    .orderBy('createdAt', 'asc')
    .execute()
}

/** Canonicalizes a transaction reference for storage and comparison. */
export function normalize(chainId: string, transactionRef: string): string {
  return chainId.startsWith('eip155:') ? transactionRef.toLowerCase() : transactionRef
}