import { randomInt } from 'crypto';

/**
 * Public query / booking reference numbers.
 *
 * Format: `{PREFIX}-{NNNNNN}` where NNNNNN is 6 digits with at most one
 * leading zero (`010000`–`999999`). Examples: `BK-746284`, `QRY-092587`.
 * Never `BK-000001` (more than one leading zero).
 *
 * Uniqueness is per tenant (matches the existing unique indexes on
 * `(tenant_id, query_number)` / `(tenant_id, booking_number)`). The
 * same six digits may exist on another tenant. `QRY-981518` and
 * `BK-981518` may both exist in the same tenant.
 *
 * `subject` ('BK' | 'QRY') selects WHICH table uniqueness is checked
 * against — it is frozen and never comes from user input. The visible
 * `prefix` printed before the digits is decoupled from it: bookings
 * carry a per-tenant prefix (default 'BK', e.g. Mellow → 'MT'), while
 * uniqueness is still enforced on the `bookings` table. Queries keep
 * the fixed 'QRY' prefix.
 *
 * The caller supplies a SQL executor so the same helper works from
 * TenantAwareRepository and from an open transaction (quick-booking).
 * The SELECT is parameterized and always includes `tenant_id`.
 */

export type ReferencePrefix = 'BK' | 'QRY';

export const REFERENCE_NUMBER_MAX_ATTEMPTS = 20;

export const DEFAULT_BOOKING_PREFIX = 'BK';

const TABLE_BY_PREFIX = {
  BK: { table: 'bookings', column: 'booking_number' },
  QRY: { table: 'queries', column: 'query_number' },
} as const;

export type SqlExecutor = (sql: string, params: unknown[]) => Promise<unknown[]>;

/**
 * Sanitize a tenant-supplied prefix: uppercase letters/digits only,
 * 1–10 chars. Anything that normalizes to empty falls back to the
 * caller's default. Keeps the prefix safe to interpolate into the
 * display string (never into SQL — digits are random, the lookup is
 * parameterized).
 */
export function normalizeReferencePrefix(
  prefix: string | null | undefined,
  fallback: string,
): string {
  const cleaned = (prefix || '')
    .toUpperCase()
    .replace(/[^A-Z0-9]/g, '')
    .slice(0, 10);
  return cleaned || fallback;
}

/** Six-digit suffix, `010000`–`999999` (randomInt max is exclusive). */
function randomSixDigits(): string {
  return String(randomInt(10_000, 1_000_000)).padStart(6, '0');
}

/** Extract the 6 digits from an existing reference, e.g. `QRY-093254` → `093254`. */
export function extractReferenceDigits(reference: string | null | undefined): string | null {
  const match = (reference || '').match(/(\d{6})\s*$/);
  return match ? match[1] : null;
}

export async function allocateUniqueReferenceNumber(
  subject: ReferencePrefix,
  tenantId: string,
  query: SqlExecutor,
  displayPrefix?: string,
): Promise<string> {
  const mapping = TABLE_BY_PREFIX[subject];
  const prefix = normalizeReferencePrefix(displayPrefix, subject);
  for (let attempt = 0; attempt < REFERENCE_NUMBER_MAX_ATTEMPTS; attempt++) {
    const candidate = `${prefix}-${randomSixDigits()}`;
    const rows = await query(
      `SELECT 1 FROM ${mapping.table} WHERE tenant_id = $1 AND ${mapping.column} = $2 LIMIT 1`,
      [tenantId, candidate],
    );
    if (Array.isArray(rows) && rows.length === 0) return candidate;
  }
  throw new Error(
    `allocateUniqueReferenceNumber: could not allocate a unique ${prefix}- number within ${REFERENCE_NUMBER_MAX_ATTEMPTS} attempts`,
  );
}

/**
 * Booking number for a query→booking conversion (1:1). Reuses the
 * query's own 6 digits with the tenant's booking prefix so the pair
 * reads as one case — `QRY-093254` → `MT-093254`. If that exact number
 * is somehow already taken (e.g. a prior booking on a re-converted
 * query), falls back to a fresh random booking number with the same
 * prefix. Uniqueness is always checked against the `bookings` table.
 */
export async function allocateBookingNumberFromQuery(
  queryNumber: string | null | undefined,
  tenantId: string,
  query: SqlExecutor,
  displayPrefix?: string,
): Promise<string> {
  const prefix = normalizeReferencePrefix(displayPrefix, DEFAULT_BOOKING_PREFIX);
  const digits = extractReferenceDigits(queryNumber);
  if (digits) {
    const candidate = `${prefix}-${digits}`;
    const rows = await query(
      `SELECT 1 FROM bookings WHERE tenant_id = $1 AND booking_number = $2 LIMIT 1`,
      [tenantId, candidate],
    );
    if (Array.isArray(rows) && rows.length === 0) return candidate;
  }
  return allocateUniqueReferenceNumber('BK', tenantId, query, prefix);
}