import { query } from '../db.js';

/**
 * Match SEAP locality strings to GisUat SIRUTA codes.
 *
 * Strategy:
 * 1. Check locality_map cache
 * 2. Exact match (normalized)
 * 3. pg_trgm fuzzy match
 * 4. Special cases (București sectoare, abbreviations)
 */
export async function matchLocality(
  city: string | null,
  county: string | null
): Promise<string | null> {
  if (!city || !county) return null;

  const normCity = city.trim();
  const normCounty = county.trim();

  // Check cache first
  const cached = await query(
    'SELECT siruta FROM seap.locality_map WHERE seap_city = $1 AND seap_county = $2',
    [normCity, normCounty]
  );
  if (cached.rows.length > 0) return cached.rows[0].siruta;

  // Exact match (case-insensitive, unaccented)
  const exact = await query(
    `SELECT siruta FROM public."GisUat"
     WHERE seap.normalize_locality(name) = seap.normalize_locality($1)
       AND seap.normalize_locality(county) = seap.normalize_locality($2)
     LIMIT 1`,
    [normCity, normCounty]
  );

  if (exact.rows.length > 0) {
    await cacheMatch(normCity, normCounty, exact.rows[0].siruta, 'exact', 1.0);
    return exact.rows[0].siruta;
  }

  // Fuzzy match with pg_trgm
  const fuzzy = await query(
    `SELECT siruta, name,
            similarity(seap.normalize_locality(name), seap.normalize_locality($1)) AS score
     FROM public."GisUat"
     WHERE seap.normalize_locality(county) = seap.normalize_locality($2)
       AND similarity(seap.normalize_locality(name), seap.normalize_locality($1)) > 0.4
     ORDER BY score DESC
     LIMIT 1`,
    [normCity, normCounty]
  );

  if (fuzzy.rows.length > 0) {
    await cacheMatch(normCity, normCounty, fuzzy.rows[0].siruta, 'fuzzy', fuzzy.rows[0].score);
    return fuzzy.rows[0].siruta;
  }

  // Special: București + Sector
  const bucMatch = matchBucuresti(normCity, normCounty);
  if (bucMatch) {
    const bucResult = await query(
      `SELECT siruta FROM public."GisUat" WHERE seap.normalize_locality(name) = seap.normalize_locality($1)`,
      [bucMatch]
    );
    if (bucResult.rows.length > 0) {
      await cacheMatch(normCity, normCounty, bucResult.rows[0].siruta, 'special', 1.0);
      return bucResult.rows[0].siruta;
    }
  }

  // No match — cache null to avoid re-querying
  await cacheMatch(normCity, normCounty, null, 'unmatched', 0);
  return null;
}

function matchBucuresti(city: string, county: string): string | null {
  const norm = city.toLowerCase().replace(/[^a-z0-9 ]/g, '');
  if (county.toLowerCase().includes('bucurest') || norm.includes('bucurest') || norm.includes('sector')) {
    const sectorMatch = norm.match(/sector\s*(\d)/);
    if (sectorMatch) return `SECTORUL ${sectorMatch[1]}`;
    if (norm.includes('bucurest') && !norm.includes('sector')) return 'BUCUREŞTI';
  }
  return null;
}

async function cacheMatch(
  city: string,
  county: string,
  siruta: string | null,
  matchType: string,
  confidence: number
) {
  await query(
    `INSERT INTO seap.locality_map (seap_city, seap_county, siruta, match_type, confidence)
     VALUES ($1, $2, $3, $4, $5)
     ON CONFLICT (seap_city, seap_county) DO UPDATE SET
       siruta = EXCLUDED.siruta,
       match_type = EXCLUDED.match_type,
       confidence = EXCLUDED.confidence`,
    [city, county, siruta, matchType, confidence]
  );
}

/** Run bulk matching for all entities without a SIRUTA */
export async function matchAllEntities(): Promise<{ matched: number; unmatched: number }> {
  const unmatched = await query(
    `SELECT entity_id, city, county FROM seap.entities WHERE siruta IS NULL AND city IS NOT NULL`
  );

  let matched = 0;
  let unmatchedCount = 0;

  for (const row of unmatched.rows) {
    const siruta = await matchLocality(row.city, row.county);
    if (siruta) {
      await query(
        'UPDATE seap.entities SET siruta = $1, match_score = (SELECT confidence FROM seap.locality_map WHERE seap_city = $2 AND seap_county = $3) WHERE entity_id = $4',
        [siruta, row.city, row.county, row.entity_id]
      );
      matched++;
    } else {
      unmatchedCount++;
    }
  }

  return { matched, unmatched: unmatchedCount };
}
