import type { PoolConnection, RowDataPacket } from 'mysql2/promise';
import { queryWithConn } from '../../config/database.js';

interface MaxIdRow extends RowDataPacket {
  maxN: number | null;
}

// Human-readable sequential IDs (e.g. "KCEMP-3") instead of opaque UUIDs.
// Next number is derived from the current max suffix in the same table, so
// it stays correct across soft-deleted rows without a separate counter table.
//
// This SELECT MAX() isn't row-locked, so two concurrent requests can read the
// same max and compute the same "next" id — always pair this with
// createWithId() below rather than calling it directly for an insert, so a
// collision retries with a fresh id instead of surfacing a raw duplicate-key
// error to the caller.
export async function nextId(table: string, prefix: string, conn?: PoolConnection): Promise<string> {
  const sql = `SELECT MAX(CAST(SUBSTRING_INDEX(id, '-', -1) AS UNSIGNED)) AS maxN FROM \`${table}\``;
  const rows = await queryWithConn<MaxIdRow[]>(sql, undefined, conn);
  const maxN = rows[0]?.maxN ?? 0;
  return `${prefix}-${Number(maxN) + 1}`;
}

interface MysqlLikeError {
  code?: string;
  errno?: number;
}

function isDuplicateEntryError(err: unknown): boolean {
  const e = err as MysqlLikeError;
  return e?.code === 'ER_DUP_ENTRY' || e?.errno === 1062;
}

function sleep(ms: number): Promise<void> {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

// Generates a sequential id and hands it to insertFn to perform the actual
// insert, retrying with a freshly generated id if two concurrent requests
// raced to the same next number (see nextId's caveat above). Any other
// error from insertFn is rethrown immediately, unretried.
//
// A small random backoff is added between attempts — under heavy concurrent
// load (many requests colliding at once), retrying immediately just makes
// them all re-read the same MAX and collide again in lockstep ("thundering
// herd"); jittering the retry spreads them out so they actually resolve.
//
// The default here (8) is deliberately left as-is — most callers create
// this id synchronously on the user-facing request path (order/employee/
// product creation, etc.), so a bigger default would mean every one of them
// waits longer under contention. activity_logs sees far heavier concurrent
// write volume than any of those (one fires per stage move, dispatch, and
// edit, from every user at once) and is written off the request path by an
// event handler, so it passes its own higher maxAttempts explicitly instead
// — see activityLog.handler.ts.
export async function createWithId<T>(
  table: string,
  prefix: string,
  insertFn: (id: string) => Promise<T>,
  conn?: PoolConnection,
  maxAttempts = 8,
): Promise<{ id: string; result: T }> {
  let lastErr: unknown;
  for (let attempt = 0; attempt < maxAttempts; attempt++) {
    if (attempt > 0) await sleep(10 + Math.random() * 40 * attempt);
    const id = await nextId(table, prefix, conn);
    try {
      const result = await insertFn(id);
      return { id, result };
    } catch (err) {
      if (!isDuplicateEntryError(err)) throw err;
      lastErr = err;
    }
  }
  throw lastErr;
}