const POSTGRES_IDENTIFIER_MAX_BYTES = 63;
const HASH_SUFFIX_LENGTH = 10;

function normalizePlayColumnName(id: string): string {
  const normalized = id
    .trim()
    .replace(/\.+/g, '__')
    .replace(/[^A-Za-z0-9_]+/g, '_')
    .replace(/^_+|_+$/g, '')
    .toLowerCase();
  const safe = normalized || 'column';
  return /^[A-Za-z_]/.test(safe) ? safe : `c_${safe}`;
}

function stableHexSuffix(value: string): string {
  let primary = 0x811c9dc5;
  let secondary = 0x811c9dc5 ^ value.length;
  for (let index = 0; index < value.length; index += 1) {
    const code = value.charCodeAt(index);
    primary = Math.imul(primary ^ code, 0x01000193) >>> 0;
    secondary = Math.imul(secondary ^ (code + index), 0x01000193) >>> 0;
  }
  return `${primary.toString(16).padStart(8, '0')}${secondary
    .toString(16)
    .padStart(8, '0')}`.slice(0, HASH_SUFFIX_LENGTH);
}

/**
 * SQL-safe physical column name for a play-authored logical field id.
 *
 * Runtime Sheet adapters must use this when they create or discover physical
 * JSONB columns so logical names such as `_metadata` and `person.email` do not
 * drift between writer and reader paths.
 */
export function sqlSafePlayColumnName(id: string): string {
  const prefixed = normalizePlayColumnName(id);
  if (prefixed.length <= POSTGRES_IDENTIFIER_MAX_BYTES) {
    return prefixed;
  }

  const hash = stableHexSuffix(prefixed);
  const maxPrefixLength =
    POSTGRES_IDENTIFIER_MAX_BYTES - HASH_SUFFIX_LENGTH - 1;
  const prefix = prefixed
    .slice(0, maxPrefixLength)
    .replace(/_+$/g, '')
    .slice(0, maxPrefixLength);
  return `${prefix}_${hash}`;
}

export function legacyPostgresTruncatedPlayColumnName(
  id: string,
): string | null {
  const prefixed = normalizePlayColumnName(id);
  if (prefixed.length <= POSTGRES_IDENTIFIER_MAX_BYTES) {
    return null;
  }
  return prefixed.slice(0, POSTGRES_IDENTIFIER_MAX_BYTES);
}