-- ====================================================================
-- Correction des avertissements de sécurité — Supabase Linter
-- ====================================================================

-- ─── 1. Fonctions : correction search_path ──────────────────────────
-- update_updated_at_column n'avait pas de search_path fixé (search_path mutable).
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER
LANGUAGE plpgsql
SET search_path = public
AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$;

-- ─── 2. Fonctions SECURITY DEFINER : révocation EXECUTE ─────────────
-- Ces fonctions sont des déclencheurs internes ou utilitaires admin.
-- Elles ne doivent pas être appelables via /rest/v1/rpc par anon ou authenticated.

REVOKE EXECUTE ON FUNCTION public.dev_schema_info()  FROM anon, authenticated;
REVOKE EXECUTE ON FUNCTION public.handle_new_user()  FROM anon, authenticated;

DO $$ BEGIN
  IF to_regproc('public.rls_auto_enable()') IS NOT NULL THEN
    EXECUTE 'REVOKE EXECUTE ON FUNCTION public.rls_auto_enable() FROM anon, authenticated';
  END IF;
END $$;

-- ─── 3. Storage : retrait de la politique de listing large ──────────
-- La politique "public read prestataire-docs" permet de lister tous les fichiers
-- du bucket, ce qui expose plus de données que nécessaire.
-- Les URL d'objets d'un bucket public restent accessibles sans cette politique.
-- L'accès admin au listing se fait via service_role (bypass RLS).
DROP POLICY IF EXISTS "public read prestataire-docs" ON storage.objects;

-- ─── 4. RLS : politiques explicites pour les tables sans politique ───
-- Toutes ces tables sont accédées exclusivement via service_role (API backend Node.js).
-- service_role contourne le RLS, donc le comportement de l'application ne change pas.
-- Les rôles anon et authenticated n'ont aucun besoin d'accès direct PostgREST.

DROP POLICY IF EXISTS "no_direct_access" ON public._migrations;
CREATE POLICY "no_direct_access" ON public._migrations
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.abonnements;
CREATE POLICY "no_direct_access" ON public.abonnements
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.adresse_cache_requetes;
CREATE POLICY "no_direct_access" ON public.adresse_cache_requetes
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.adresses_reference;
CREATE POLICY "no_direct_access" ON public.adresses_reference
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.agendas;
CREATE POLICY "no_direct_access" ON public.agendas
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.app_content;
CREATE POLICY "no_direct_access" ON public.app_content
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.contrats;
CREATE POLICY "no_direct_access" ON public.contrats
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.creneaux;
CREATE POLICY "no_direct_access" ON public.creneaux
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.dev_sessions;
CREATE POLICY "no_direct_access" ON public.dev_sessions
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.enfants;
CREATE POLICY "no_direct_access" ON public.enfants
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.entreprises;
CREATE POLICY "no_direct_access" ON public.entreprises
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.itineraire_cache_requetes;
CREATE POLICY "no_direct_access" ON public.itineraire_cache_requetes
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.messages_contact;
CREATE POLICY "no_direct_access" ON public.messages_contact
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.prestataire_kyc;
CREATE POLICY "no_direct_access" ON public.prestataire_kyc
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.prestataire_kyc_history;
CREATE POLICY "no_direct_access" ON public.prestataire_kyc_history
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.prestataires;
CREATE POLICY "no_direct_access" ON public.prestataires
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.prestations;
CREATE POLICY "no_direct_access" ON public.prestations
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.reservation_enfants;
CREATE POLICY "no_direct_access" ON public.reservation_enfants
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.reservations;
CREATE POLICY "no_direct_access" ON public.reservations
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.signalements;
CREATE POLICY "no_direct_access" ON public.signalements
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.tuteurs;
CREATE POLICY "no_direct_access" ON public.tuteurs
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.utilisateurs;
CREATE POLICY "no_direct_access" ON public.utilisateurs
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.vitrines;
CREATE POLICY "no_direct_access" ON public.vitrines
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS "no_direct_access" ON public.votes;
CREATE POLICY "no_direct_access" ON public.votes
  AS RESTRICTIVE TO anon, authenticated
  USING (false) WITH CHECK (false);