import { type Selectable, sql } from 'kysely'

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

/** Columns of the `verified_token_requests` table. */
export type Table = db_Schema.VerifiedTokenRequest
/** A stored verification request. */
export type Record = Selectable<Table>
/** Review status of a request. */
export type Status = Record['status']

/** Maximum pending verification requests retained for one requester. */
export const maxPendingPerRequester = 10

/**
 * Inserts a pending request; `undefined` when a pending request for
 * (chainId, address) already exists. Throws `PendingLimitError` when the
 * requester already has the maximum retained pending requests.
 */
export function create(db: Db.Db, input: create.Input): Promise<Record | undefined> {
  const now = new Date().toISOString()
  return db.transaction(async (tx) => {
    // Serialize admission per requester so parallel organizations cannot exceed the retained queue cap.
    await sql`SELECT pg_advisory_xact_lock(hashtextextended(${`verified-token-requests:${input.requestedBy}`}, 0))`.execute(
      tx.kysely,
    )
    const pending = await tx.kysely
      .selectFrom('verified_token_requests')
      .select(({ fn }) => fn.countAll<string>().as('count'))
      .where('requestedBy', '=', input.requestedBy)
      .where('status', '=', 'pending')
      .executeTakeFirstOrThrow()
    if (Number(pending.count) >= maxPendingPerRequester) throw new PendingLimitError()
    return tx.kysely
      .insertInto('verified_token_requests')
      .values({
        address: input.address,
        chainId: input.chainId,
        createdAt: now,
        currency: input.currency,
        decimals: input.decimals,
        id: Id.generate('vtr'),
        logo: input.logo ?? null,
        logoUri: input.logoUri ?? null,
        name: input.name,
        note: input.note ?? null,
        orgId: input.orgId,
        requestedBy: input.requestedBy,
        reviewNote: null,
        reviewedAt: null,
        reviewedBy: null,
        status: 'pending',
        symbol: input.symbol,
        updatedAt: now,
      })
      .onConflict((oc) =>
        oc
          .columns(['chainId', 'address'])
          // `sql.lit` keeps the predicate literal; a bound parameter would
          // defeat partial-index inference for the conflict target.
          .where('status', '=', sql.lit('pending'))
          .doNothing(),
      )
      .returningAll()
      .executeTakeFirst()
  })
}

export declare namespace create {
  /** Fields accepted when inserting a request. */
  type Input = {
    /** Requested token contract address (lowercase). */
    address: string
    /** Chain the token belongs to. */
    chainId: number
    /** Display currency, e.g. `USD`. */
    currency: string
    /** Decimal precision. */
    decimals: number
    /** Inline SVG logo markup uploaded by the requester. */
    logo?: string | undefined
    /** HTTPS logo URL from chain metadata. */
    logoUri?: string | undefined
    /** Display name. */
    name: string
    /** Requester note to reviewers. */
    note?: string | undefined
    /** Requesting organization id (`org_…`). */
    orgId: string
    /** User id that submitted the request, or `super_admin`. */
    requestedBy: string
    /** Ticker symbol. */
    symbol: string
  }
}

/** Lists an organization's requests, newest first. */
export function list(db: Db.Db, orgId: string): Promise<Record[]> {
  return db.kysely
    .selectFrom('verified_token_requests')
    .selectAll()
    .where('orgId', '=', orgId)
    .orderBy('createdAt', 'desc')
    .execute()
}

/**
 * Lists requests in one status globally, oldest first (review-queue order),
 * with the requesting organization's current name when it still exists.
 */
export function listByStatus(
  db: Db.Db,
  status: Status,
  options: listByStatus.Options,
): Promise<(Record & { orgName: string | null })[]> {
  const { cursor } = options
  let query = db.kysely
    .selectFrom('verified_token_requests')
    .leftJoin('organizations', 'organizations.id', 'verified_token_requests.orgId')
    .selectAll('verified_token_requests')
    .select('organizations.name as orgName')
    .where('status', '=', status)
  if (cursor)
    query = query.where((eb) =>
      eb.or([
        eb('verified_token_requests.createdAt', '>', cursor.createdAt),
        eb.and([
          eb('verified_token_requests.createdAt', '=', cursor.createdAt),
          eb('verified_token_requests.id', '>', cursor.id),
        ]),
      ]),
    )
  return query
    .orderBy('verified_token_requests.createdAt', 'asc')
    .orderBy('verified_token_requests.id', 'asc')
    .limit(options.limit)
    .execute()
}

export declare namespace listByStatus {
  /** Pagination options for the global review queue. */
  type Options = {
    /** Oldest-first position after which to continue. */
    cursor?: { createdAt: string; id: string } | undefined
    /** Maximum records to return. */
    limit: number
  }
}

/** Gets a request by id. */
export function get(db: Db.Db, id: string): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('verified_token_requests')
    .selectAll()
    .where('id', '=', id)
    .executeTakeFirst()
}

/** Hard-deletes an org-scoped request while pending; `undefined` otherwise. */
export function remove(db: Db.Db, orgId: string, id: string): Promise<Record | undefined> {
  return db.kysely
    .deleteFrom('verified_token_requests')
    .where('id', '=', id)
    .where('orgId', '=', orgId)
    .where('status', '=', 'pending')
    .returningAll()
    .executeTakeFirst()
}

/**
 * Marks a pending request approved or denied with reviewer identity;
 * `undefined` when absent or already reviewed.
 */
export function review(db: Db.Db, id: string, input: review.Input): Promise<Record | undefined> {
  const now = new Date().toISOString()
  return db.kysely
    .updateTable('verified_token_requests')
    .set({
      reviewNote: input.reviewNote ?? null,
      reviewedAt: now,
      reviewedBy: input.reviewedBy,
      status: input.status,
      updatedAt: now,
    })
    .where('id', '=', id)
    .where('status', '=', 'pending')
    .returningAll()
    .executeTakeFirst()
}

export declare namespace review {
  /** Review outcome fields. */
  type Input = {
    /** Reviewer note shown on denial. */
    reviewNote?: string | undefined
    /** Reviewing admin email. */
    reviewedBy: string
    /** Review outcome. */
    status: 'approved' | 'denied'
  }
}

/** Maps a stored row to the wire shape (null `logo`/`logoUri` map to absent). */
export function toRequest(record: Record) {
  const { logo, logoUri, ...rest } = record
  return {
    ...rest,
    ...(logo === null ? {} : { logo }),
    ...(logoUri === null ? {} : { logoUri }),
  }
}

/** The requester already has the maximum retained pending requests. */
export class PendingLimitError extends Error {
  override readonly name = 'VerifiedTokenRequests.PendingLimitError'
}