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']

/**
 * Inserts a pending request; `undefined` when a pending request for
 * (chainId, address) already exists.
 */
export function create(db: Db.Db, input: create.Input): Promise<Record | undefined> {
  const now = new Date().toISOString()
  return db.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,
): Promise<(Record & { orgName: string | null })[]> {
  return 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)
    .orderBy('verified_token_requests.createdAt', 'asc')
    .execute()
}

/** 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()
}

/** Counts an organization's pending requests. */
export async function countPending(db: Db.Db, orgId: string): Promise<number> {
  const row = await db.kysely
    .selectFrom('verified_token_requests')
    .select(({ fn }) => fn.countAll<string>().as('count'))
    .where('orgId', '=', orgId)
    .where('status', '=', 'pending')
    .executeTakeFirstOrThrow()
  return Number(row.count)
}

/** 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 }),
  }
}