gigafibre-fsm/apps/website/supabase/migrations/20260212174200_f1586ca2-f58b-4486-8eef-cce564dbb4c7.sql

CREATE OR REPLACE FUNCTION public.search_addresses(
  search_term text,
  result_limit integer DEFAULT 8
)
RETURNS TABLE(
  identifiant_unique_adresse text,
  adresse_formatee text,
  numero_municipal text,
  numero_unite text,
  code_postal text,
  odonyme_recompose_normal text,
  nom_municipalite text,
  longitude double precision,
  latitude double precision,
  fiber_available boolean,
  zone_tarifaire integer,
  max_speed integer,
  similarity_score real
)
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = public
AS $$
DECLARE
  clean_term text;
  num_part text;
  street_part text;
  street_words text[];
  word text;
  ilike_pattern text;
BEGIN
  clean_term := trim(search_term);
  
  -- Extract leading number and remaining street part
  num_part := (regexp_match(clean_term, '^\s*(\d+)'))[1];
  street_part := trim(regexp_replace(clean_term, '^\s*\d+\s*', ''));

  -- Split street_part into individual words for progressive filtering
  IF street_part <> '' THEN
    street_words := string_to_array(lower(street_part), ' ');
  ELSE
    street_words := ARRAY[]::text[];
  END IF;

  -- Phase 1: We have a civic number
  IF num_part IS NOT NULL AND length(num_part) >= 1 THEN

    -- 1a: Exact numero_municipal match + street words filter
    RETURN QUERY
    SELECT
      a.identifiant_unique_adresse,
      a.adresse_formatee,
      a.numero_municipal,
      a.numero_unite,
      a.code_postal,
      a.odonyme_recompose_normal,
      a.nom_municipalite,
      a.longitude,
      a.latitude,
      (f.id IS NOT NULL) AS fiber_available,
      f.zone_tarifaire,
      f.max_speed,
      CASE 
        WHEN street_part = '' THEN 
          CASE WHEN a.numero_municipal = num_part THEN 1.0 ELSE 0.5 END::real
        ELSE
          -- Combined score: street name similarity + municipality similarity
          GREATEST(
            similarity(lower(COALESCE(a.odonyme_recompose_normal, '')), lower(street_part)),
            similarity(lower(COALESCE(a.odonyme_recompose_court, '')), lower(street_part)),
            similarity(lower(COALESCE(a.odonyme_recompose_long, '')), lower(street_part)),
            similarity(lower(COALESCE(a.nom_municipalite, '')), lower(street_part))
          )
      END AS similarity_score
    FROM public.addresses a
    LEFT JOIN public.fiber_availability f ON f.uuidadresse = a.identifiant_unique_adresse
    WHERE a.numero_municipal = num_part
      AND (
        street_part = '' 
        OR (
          -- Every word must appear somewhere in street name OR municipality
          NOT EXISTS (
            SELECT 1 FROM unnest(street_words) w
            WHERE NOT (
              lower(COALESCE(a.odonyme_recompose_normal, '')) LIKE '%' || w || '%'
              OR lower(COALESCE(a.odonyme_recompose_court, '')) LIKE '%' || w || '%'
              OR lower(COALESCE(a.odonyme_recompose_long, '')) LIKE '%' || w || '%'
              OR lower(COALESCE(a.nom_municipalite, '')) LIKE '%' || w || '%'
              OR lower(COALESCE(a.code_postal, '')) LIKE '%' || w || '%'
            )
          )
        )
      )
    ORDER BY similarity_score DESC
    LIMIT result_limit;
    
    IF FOUND THEN
      RETURN;
    END IF;
    
    -- 1b: Prefix match on numero_municipal + street words filter
    RETURN QUERY
    SELECT
      a.identifiant_unique_adresse,
      a.adresse_formatee,
      a.numero_municipal,
      a.numero_unite,
      a.code_postal,
      a.odonyme_recompose_normal,
      a.nom_municipalite,
      a.longitude,
      a.latitude,
      (f.id IS NOT NULL) AS fiber_available,
      f.zone_tarifaire,
      f.max_speed,
      CASE 
        WHEN street_part = '' THEN 0.5::real
        ELSE
          GREATEST(
            similarity(lower(COALESCE(a.odonyme_recompose_normal, '')), lower(street_part)),
            similarity(lower(COALESCE(a.odonyme_recompose_court, '')), lower(street_part)),
            similarity(lower(COALESCE(a.odonyme_recompose_long, '')), lower(street_part)),
            similarity(lower(COALESCE(a.nom_municipalite, '')), lower(street_part))
          )
      END AS similarity_score
    FROM public.addresses a
    LEFT JOIN public.fiber_availability f ON f.uuidadresse = a.identifiant_unique_adresse
    WHERE a.numero_municipal LIKE (num_part || '%')
      AND (
        street_part = ''
        OR (
          NOT EXISTS (
            SELECT 1 FROM unnest(street_words) w
            WHERE NOT (
              lower(COALESCE(a.odonyme_recompose_normal, '')) LIKE '%' || w || '%'
              OR lower(COALESCE(a.odonyme_recompose_court, '')) LIKE '%' || w || '%'
              OR lower(COALESCE(a.odonyme_recompose_long, '')) LIKE '%' || w || '%'
              OR lower(COALESCE(a.nom_municipalite, '')) LIKE '%' || w || '%'
              OR lower(COALESCE(a.code_postal, '')) LIKE '%' || w || '%'
            )
          )
        )
      )
    ORDER BY similarity_score DESC, a.numero_municipal
    LIMIT result_limit;
    
    IF FOUND THEN
      RETURN;
    END IF;
  END IF;

  -- Phase 2: No number or no results — full trigram search
  RETURN QUERY
  SELECT
    a.identifiant_unique_adresse,
    a.adresse_formatee,
    a.numero_municipal,
    a.numero_unite,
    a.code_postal,
    a.odonyme_recompose_normal,
    a.nom_municipalite,
    a.longitude,
    a.latitude,
    (f.id IS NOT NULL) AS fiber_available,
    f.zone_tarifaire,
    f.max_speed,
    GREATEST(
      similarity(COALESCE(a.odonyme_recompose_normal, ''), clean_term),
      similarity(COALESCE(a.adresse_formatee, ''), clean_term)
    ) AS similarity_score
  FROM public.addresses a
  LEFT JOIN public.fiber_availability f ON f.uuidadresse = a.identifiant_unique_adresse
  WHERE COALESCE(a.odonyme_recompose_normal, '') % clean_term
     OR a.adresse_formatee % clean_term
  ORDER BY similarity_score DESC
  LIMIT result_limit;
END;
$$;