import type { Selectable } from 'kysely'

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

/** Columns of the `funding_transfers` table. */
export type Table = db_Schema.FundingTransfer
/** A stored funding transfer. */
export type Record = Selectable<Table>

/**
 * Ownership filter derived from the API-key principal, never from the
 * request. A `projectId` narrows reads to that project; organization-attributed
 * keys omit it and see every project in their organization and environment.
 */
export type Owner = {
  /** Key environment. */
  environment: Record['environment']
  /** Owning organization id (`org_…`). */
  orgId: string
  /** Attributed project id, for project-attributed keys. */
  projectId?: string | undefined
}

/** Inserts a transfer row. Callers commit the matching event in the same transaction. */
export function insert(db: Db.Db, record: Record): Promise<Record> {
  return db.kysely
    .insertInto('funding_transfers')
    .values(record)
    .returningAll()
    .executeTakeFirstOrThrow()
}

/** Gets a transfer by id without ownership scoping (domain and reconciler use). */
export function get(db: Db.Db, id: string): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('funding_transfers')
    .selectAll()
    .where('id', '=', id)
    .executeTakeFirst()
}

/** Lists processing transfers for one provider, oldest first. */
export function listProcessing(db: Db.Db, options: listProcessing.Options): Promise<Record[]> {
  let query = db.kysely
    .selectFrom('funding_transfers')
    .selectAll()
    .where('providerId', '=', options.providerId)
    .where('status', '=', 'processing')
  if (options.cursor !== undefined) query = query.where('id', '>', options.cursor)
  return query.orderBy('id', 'asc').limit(options.limit).execute()
}

export declare namespace listProcessing {
  /** Provider filter for one recovery scan. */
  type Options = {
    /** Exclusive lower bound from the prior recovery page. */
    cursor?: string | undefined
    /** Maximum transfers returned in one recovery page. */
    limit: number
    /** Funding provider id. */
    providerId: string
  }
}

/** Gets and locks a transfer for one atomic domain mutation. */
export function getForUpdate(db: Db.Db, id: string): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('funding_transfers')
    .selectAll()
    .where('id', '=', id)
    .forUpdate()
    .executeTakeFirst()
}

/** Gets an owner-visible transfer by id; `undefined` for absent or foreign rows. */
export function getOwned(db: Db.Db, owner: Owner, id: string): Promise<Record | undefined> {
  let query = db.kysely
    .selectFrom('funding_transfers')
    .selectAll()
    .where('id', '=', id)
    .where('orgId', '=', owner.orgId)
    .where('environment', '=', owner.environment)
  if (owner.projectId !== undefined) query = query.where('projectId', '=', owner.projectId)
  return query.executeTakeFirst()
}

/**
 * Lists an owner's transfers newest-first. Ids embed a timestamp, so lexical
 * key order is chronological and the cursor is the last returned id.
 */
export function list(db: Db.Db, owner: Owner, options: list.Options): Promise<Record[]> {
  let query = db.kysely
    .selectFrom('funding_transfers')
    .selectAll()
    .where('orgId', '=', owner.orgId)
    .where('environment', '=', owner.environment)
  if (owner.projectId !== undefined) query = query.where('projectId', '=', owner.projectId)
  if (options.status !== undefined) query = query.where('status', '=', options.status)
  if (options.cursor !== undefined) query = query.where('id', '<', options.cursor)
  return query.orderBy('id', 'desc').limit(options.limit).execute()
}

export declare namespace list {
  /** Paging and filter options. */
  type Options = {
    /** Exclusive lower bound: the last id of the previous page. */
    cursor?: string | undefined
    /** Maximum rows to return (fetch one extra to detect a further page). */
    limit: number
    /** Restricts to one lifecycle status. */
    status?: Record['status'] | undefined
  }
}

/**
 * Counts an owner's transfers through a capped subquery sharing the list
 * filter, so the count cannot scan unboundedly. A result above `cap` means
 * the true count was truncated.
 */
export async function count(db: Db.Db, owner: Owner, options: count.Options): Promise<number> {
  const row = await db.kysely
    .selectFrom((eb) => {
      let query = eb
        .selectFrom('funding_transfers')
        .select('id')
        .where('orgId', '=', owner.orgId)
        .where('environment', '=', owner.environment)
      if (owner.projectId !== undefined) query = query.where('projectId', '=', owner.projectId)
      if (options.status !== undefined) query = query.where('status', '=', options.status)
      return query.limit(options.cap + 1).as('page')
    })
    .select(({ fn }) => fn.countAll<string>().as('count'))
    .executeTakeFirstOrThrow()
  return Number(row.count)
}

export declare namespace count {
  /** Count options sharing the list filter. */
  type Options = {
    /** Row ceiling; a result above it signals a truncated count. */
    cap: number
    /** Restricts to one lifecycle status. */
    status?: Record['status'] | undefined
  }
}

/**
 * Applies a version-guarded update; `undefined` when the row moved past
 * `expectedVersion` (or vanished) so the caller retries against fresh state.
 */
export function update(db: Db.Db, options: update.Options): Promise<Record | undefined> {
  return db.kysely
    .updateTable('funding_transfers')
    .set({
      providerState: options.providerState,
      snapshot: options.snapshot,
      status: options.status,
      statusReason: options.statusReason,
      updatedAt: options.updatedAt,
      version: options.version,
    })
    .where('id', '=', options.id)
    .where('version', '=', options.expectedVersion)
    .returningAll()
    .executeTakeFirst()
}

export declare namespace update {
  /** Guarded update fields. */
  type Options = {
    /** The version the update was computed against. */
    expectedVersion: number
    /** Transfer id (`ftr_…`). */
    id: string
    /** Replacement private provider state; omitted keeps the stored value. */
    providerState?: Record['providerState'] | undefined
    /** Replacement public snapshot. */
    snapshot: Record['snapshot']
    /** New lifecycle status. */
    status: Record['status']
    /** Customer-safe reason, or null to clear. */
    statusReason: Record['statusReason']
    /** Mutation timestamp (ISO 8601). */
    updatedAt: string
    /** The incremented version. */
    version: number
  }
}