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 `users` table, derived from `Schema.User`. */
export type Table = db_Schema.User

/** A stored user row. */
export type Record = Selectable<Table>

/**
 * Reads a user by id (`usr_…`).
 *
 * @param db - The database.
 * @param id - The user id.
 * @returns The record, or `undefined` when absent.
 */
export function get(db: Db.Db, id: string): Promise<Record | undefined> {
  return db.kysely.selectFrom('users').selectAll().where('id', '=', id).executeTakeFirst()
}

/**
 * Reads users by id for operational identity displays.
 *
 * @param db - The database.
 * @param ids - User ids to read.
 * @returns Matching user records.
 */
export function listByIds(db: Db.Db, ids: readonly string[]): Promise<Record[]> {
  if (ids.length === 0) return Promise.resolve([])
  return db.kysely
    .selectFrom('users')
    .selectAll()
    .where('id', 'in', [...ids])
    .execute()
}

/**
 * Reads a user by normalized verified email.
 *
 * @param db - The database.
 * @param email - The verified email.
 * @returns The oldest matching record, or `undefined` when absent.
 */
export function getByEmail(db: Db.Db, email: string): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('users')
    .selectAll()
    .where(sql<string>`lower(btrim(email))`, '=', email.trim().toLowerCase())
    .orderBy('createdAt', 'asc')
    .orderBy('id', 'asc')
    .executeTakeFirst()
}

/**
 * Reads a user by wallet address (compared lowercase).
 *
 * @param db - The database.
 * @param address - The wallet address.
 * @returns The record, or `undefined` when absent.
 */
export function getByAddress(db: Db.Db, address: string): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('users')
    .selectAll()
    .where('address', '=', address.toLowerCase())
    .executeTakeFirst()
}

/**
 * Locks and reads a user by wallet address for identity reconciliation.
 *
 * @param db - The transaction-scoped database.
 * @param address - The wallet address.
 * @returns The locked record, or `undefined` when absent.
 */
export function getByAddressForUpdate(db: Db.Db, address: string): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('users')
    .selectAll()
    .where('address', '=', address.toLowerCase())
    .forUpdate()
    .executeTakeFirst()
}

/**
 * Inserts a user for `address` when absent and returns the stored record.
 * Existing users are returned unchanged, so repeated sign-ins are idempotent.
 *
 * @param db - The database.
 * @param input - The user to ensure.
 * @returns The stored record.
 */
export async function upsertByAddress(db: Db.Db, input: upsertByAddress.Input): Promise<Record> {
  const address = input.address.toLowerCase()
  const now = new Date().toISOString()
  const inserted = await db.kysely
    .insertInto('users')
    .values({
      address,
      createdAt: now,
      email: null,
      emailVerified: false,
      id: Id.generate('usr'),
      image: null,
      name: address,
      updatedAt: now,
    })
    .onConflict((oc) => oc.column('address').doNothing())
    .returningAll()
    .executeTakeFirst()
  if (inserted) return inserted
  return db.kysely
    .selectFrom('users')
    .selectAll()
    .where('address', '=', address)
    .executeTakeFirstOrThrow()
}

export declare namespace upsertByAddress {
  /** Fields accepted when ensuring a user. */
  type Input = {
    /** Wallet address that signed in (stored lowercase). */
    address: string
  }
}

/**
 * Sets a user's verified email, bumping `updatedAt`.
 *
 * @param db - The database.
 * @param id - The user id (`usr_…`).
 * @param email - The verified email.
 */
export async function setEmail(db: Db.Db, id: string, email: string): Promise<void> {
  await db.kysely
    .updateTable('users')
    .set({
      email: email.trim().toLowerCase(),
      emailVerified: true,
      updatedAt: new Date().toISOString(),
    }) // prettier-ignore
    .where('id', '=', id)
    .execute()
}