-- 038_bugetar_cui_match_stage_b.sql
-- Stage B (fuzzy) CUI match for bugetar.entitate using judet constraint.
-- After this runs, expected post-match coverage: 58% → ~70-75% (estimate
-- ~1,500 new matches added via trgm_judet method at threshold 0.78).
--
-- IMPORTANT: this is a LONG-RUNNING SQL (~30-60 min on full table) because
-- it does fuzzy matching across ~7,857 unmatched bugetar rows × millions of
-- firms.entities rows. It's batched per judet to keep the query plan tractable.
--
-- Idempotent — UPDATEs only WHERE cui IS NULL.
-- Run via: psql -f 038_bugetar_cui_match_stage_b.sql

-- Map: 2-letter judet code → uppercase-ASCII variant present in firms.adr_judet
-- (firms uses old cedilla characters Ş/Ţ; translate strips diacritics to ASCII).
DROP TABLE IF EXISTS tmp_judet_map;
CREATE TEMP TABLE tmp_judet_map (code text PRIMARY KEY, name_ascii text NOT NULL);
INSERT INTO tmp_judet_map VALUES
  ('AB','ALBA'),('AG','ARGES'),('AR','ARAD'),('B','MUNICIPIUL BUCURESTI'),
  ('BC','BACAU'),('BH','BIHOR'),('BN','BISTRITA-NASAUD'),('BR','BRAILA'),
  ('BT','BOTOSANI'),('BV','BRASOV'),('BZ','BUZAU'),('CJ','CLUJ'),
  ('CL','CALARASI'),('CS','CARAS-SEVERIN'),('CT','CONSTANTA'),('CV','COVASNA'),
  ('DB','DAMBOVITA'),('DJ','DOLJ'),('GJ','GORJ'),('GL','GALATI'),
  ('GR','GIURGIU'),('HD','HUNEDOARA'),('HR','HARGHITA'),('IF','ILFOV'),
  ('IL','IALOMITA'),('IS','IASI'),('MH','MEHEDINTI'),('MM','MARAMURES'),
  ('MS','MURES'),('NT','NEAMT'),('OT','OLT'),('PH','PRAHOVA'),
  ('SB','SIBIU'),('SJ','SALAJ'),('SM','SATU MARE'),('SV','SUCEAVA'),
  ('TL','TULCEA'),('TM','TIMIS'),('TR','TELEORMAN'),('VL','VALCEA'),
  ('VN','VRANCEA'),('VS','VASLUI');

-- Materialize a per-judet view of firms.entities with ASCII-normalized adr_judet
-- so the trgm join doesn't recompute translate() per probe.
DROP TABLE IF EXISTS tmp_firms_by_judet;
CREATE TEMP TABLE tmp_firms_by_judet AS
SELECT cui, name, name_normalized,
       UPPER(translate(COALESCE(adr_judet,''), 'ŞȘŢȚăâîĂÂÎ', 'SSTTAAIAAI')) AS judet_ascii
FROM firms.entities
WHERE name_normalized IS NOT NULL AND adr_judet IS NOT NULL;
CREATE INDEX ON tmp_firms_by_judet (judet_ascii);
CREATE INDEX ON tmp_firms_by_judet USING gin (name_normalized gin_trgm_ops);
ANALYZE tmp_firms_by_judet;

-- Materialize unmatched bugetar rows with normalized names + expected judet
DROP TABLE IF EXISTS tmp_bugetar_unmatched;
CREATE TEMP TABLE tmp_bugetar_unmatched AS
SELECT b.id, b.entity_name, jm.name_ascii AS expected_judet,
       firms.normalize_company_name(b.entity_name) AS bn_norm
FROM bugetar.entitate b
JOIN tmp_judet_map jm ON jm.code = b.judet
WHERE b.cui IS NULL;
CREATE INDEX ON tmp_bugetar_unmatched (expected_judet);

-- Per-judet UPDATE loop using DO block (sequential per judet → bounded planner cost)
SET pg_trgm.similarity_threshold = 0.78;

DO $$
DECLARE
  jcode text;
  jname text;
  updated integer;
  total_updated integer := 0;
BEGIN
  FOR jcode, jname IN SELECT code, name_ascii FROM tmp_judet_map ORDER BY code LOOP
    WITH unmatched_in_judet AS (
      SELECT id, bn_norm FROM tmp_bugetar_unmatched WHERE expected_judet = jname
    ),
    candidates AS (
      SELECT DISTINCT ON (u.id) u.id, f.cui,
             similarity(f.name_normalized, u.bn_norm) AS score
      FROM unmatched_in_judet u
      JOIN tmp_firms_by_judet f
        ON f.judet_ascii = jname AND f.name_normalized % u.bn_norm
      ORDER BY u.id, similarity(f.name_normalized, u.bn_norm) DESC
    )
    UPDATE bugetar.entitate b
    SET cui = c.cui,
        cui_match_score = c.score::numeric(5,2),
        cui_match_method = 'trgm_judet',
        updated_at = now()
    FROM candidates c
    WHERE b.id = c.id AND b.cui IS NULL AND c.score >= 0.78;

    GET DIAGNOSTICS updated = ROW_COUNT;
    total_updated := total_updated + updated;
    RAISE NOTICE '  judet=% (%) matched=%', jcode, jname, updated;
  END LOOP;
  RAISE NOTICE '=== total trgm_judet matches: % ===', total_updated;
END $$;

-- Final stats
SELECT cui_match_method, count(*),
       round(avg(cui_match_score)::numeric, 2) AS avg_score
FROM bugetar.entitate
WHERE cui IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;

-- Cleanup
DROP TABLE tmp_judet_map, tmp_firms_by_judet, tmp_bugetar_unmatched;
