CREATE OR REPLACE FUNCTION public.search_addresses(search_term text, result_limit integer DEFAULT 10)
 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 DEFINER
 SET search_path TO 'public'
AS $function$
BEGIN
  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.numero_municipal, '') || ' ' || COALESCE(a.odonyme_recompose_normal, ''), search_term),
      similarity(COALESCE(a.adresse_formatee, ''), search_term)
    ) AS similarity_score
  FROM public.addresses a
  LEFT JOIN public.fiber_availability f ON f.uuidadresse = a.identifiant_unique_adresse
  WHERE
    (COALESCE(a.numero_municipal, '') || ' ' || COALESCE(a.odonyme_recompose_normal, '')) % search_term
    OR a.adresse_formatee % search_term
  ORDER BY similarity_score DESC
  LIMIT result_limit;
END;
$function$;

-- Index on the concatenated column for faster trigram matching
CREATE INDEX IF NOT EXISTS idx_addresses_num_odonyme_trgm 
ON public.addresses 
USING gin ((COALESCE(numero_municipal, '') || ' ' || COALESCE(odonyme_recompose_normal, '')) public.gin_trgm_ops);

CREATE INDEX IF NOT EXISTS idx_addresses_adresse_formatee_trgm 
ON public.addresses 
USING gin (adresse_formatee public.gin_trgm_ops);