gigafibre-fsm/scripts/migration/normalize_service_locations_pass2.sql

-- normalize_service_locations_pass2.sql — RÉCUPÉRATION par NUMÉRO + VILLE (sans contrainte de code postal),
-- pour les Service Locations restées 'review'/'unmatched' après la passe 1 (postal+numéro). Beaucoup ont un
-- code postal erroné/manquant mais une ville valide → on retrouve la rue par similarité.
--
-- N'UPGRADE qu'avec une similarité ≥0.30 (barre + stricte qu'en passe 1 car pas de contrainte postale) →
-- met aq_address_id + linked_address + coords RQA réelles + statut 'validated'. Laisse le reste tel quel
-- (boîtes postales C.P./PO Box, adresses hors-QC, surnoms de camping). address_line/city/postal INCHANGÉS.
\timing on
BEGIN;

UPDATE "tabService Location" sl SET
  aq_address_id = m.id,
  linked_address = m.address_full,
  latitude = m.lat,
  longitude = m.lon,
  address_validation_status = 'validated',
  address_validated_at = NOW(),
  modified = NOW()
FROM (
  SELECT s.name, m.id, m.address_full, m.lat, m.lon, m.sim
  FROM "tabService Location" s
  CROSS JOIN LATERAL (
    SELECT a.id::text AS id, a.address_full, a.latitude AS lat, a.longitude AS lon,
           similarity(a.search_text, lower(unaccent(s.address_line))) AS sim
    FROM rqa_addresses a
    WHERE a.numero = (regexp_match(s.address_line, '^\s*(\d+)'))[1]
      AND a.ville % regexp_replace(regexp_replace(lower(unaccent(coalesce(s.city,''))), '^ste[ .-]', 'sainte-'), '^st[ .-]', 'saint-')
    ORDER BY similarity(a.search_text, lower(unaccent(s.address_line))) DESC
    LIMIT 1
  ) m
  WHERE s.address_validation_status IN ('review', 'unmatched')
    AND s.address_line ~ '^[0-9]'
    AND s.city NOT IN ('', 'N/A', 'Ville', 'x')
    AND m.sim >= 0.30
) m
WHERE m.name = sl.name;

SELECT address_validation_status AS statut, count(*) AS n,
       count(NULLIF(aq_address_id,'')) AS avec_lien,
       count(*) FILTER (WHERE latitude IS NOT NULL AND latitude<>0) AS avec_gps
FROM "tabService Location" GROUP BY 1 ORDER BY 2 DESC;

COMMIT;