import { db } from '../db/index.js';
import { envelopes } from '../db/schema.js';
import { sql } from 'drizzle-orm';

/** Cached normalized name → envelope_number map */
let _cache: Map<string, string> | null = null;

/** Normalize a name for fuzzy matching: lowercase, remove accents, trim whitespace */
function normalize(name: string): string {
  return name
    .normalize('NFD')
    .replace(/[\u0300-\u036f]/g, '')
    .toLowerCase()
    .replace(/\s+/g, ' ')
    .trim();
}

/** Build the cache from the envelopes table */
function ensureCache(): Map<string, string> {
  if (_cache) return _cache;

  const rows = db.select({
    envelopeNumber: envelopes.envelopeNumber,
    name: envelopes.name,
  }).from(envelopes).all();

  _cache = new Map();
  for (const row of rows) {
    if (!row.name || !row.envelopeNumber) continue;
    const key = normalize(row.name);
    // First match wins (lower envelope numbers take priority)
    if (!_cache.has(key)) {
      _cache.set(key, row.envelopeNumber);
    }
  }

  return _cache;
}

/**
 * Look up the envelope number for a given sender name.
 * Tries exact match first, then partial (last name) match.
 */
export function lookupEnvelopeNumber(senderName: string): string | null {
  if (!senderName) return null;

  const cache = ensureCache();
  const normalizedSender = normalize(senderName);

  // 1. Exact match
  const exact = cache.get(normalizedSender);
  if (exact) return exact;

  // 2. Word-level match: every word in the shorter name must appear as a
  //    whole word in the longer name.  This prevents "Anna" from matching
  //    "Giovanna" while still allowing "Jean Dupont" to match
  //    "Jean et Marie Dupont".
  for (const [envName, envNum] of cache) {
    const senderWords = normalizedSender.split(' ');
    const envWords = envName.split(' ');
    // Pick shorter set as the "query" words and the longer set as the pool
    const [query, pool] =
      senderWords.length <= envWords.length
        ? [senderWords, envWords]
        : [envWords, senderWords];
    if (query.length > 0 && query.every(w => pool.includes(w))) {
      return envNum;
    }
  }

  // 3. Last-name match: compare last words
  const senderParts = normalizedSender.split(' ');
  const senderLast = senderParts[senderParts.length - 1];
  if (senderLast.length >= 3) {
    for (const [envName, envNum] of cache) {
      const envParts = envName.split(' ');
      const envLast = envParts[envParts.length - 1];
      if (envLast === senderLast) {
        // Also check first name initial if available
        if (senderParts.length >= 2 && envParts.length >= 2) {
          if (envParts[0][0] === senderParts[0][0]) return envNum;
        } else {
          return envNum;
        }
      }
    }
  }

  return null;
}

/** Force cache rebuild (call after CSV re-import) */
export function clearEnvelopeCache(): void {
  _cache = null;
}