
-- 1) Views: switch to security_invoker so they respect caller's RLS
ALTER VIEW public.products_search_view SET (security_invoker = on);
ALTER VIEW public.admin_conversation_stats SET (security_invoker = on);

-- 2) sms_verification_codes: RLS enabled but no policy -> lock to service_role only
DROP POLICY IF EXISTS "sms_codes_service_only" ON public.sms_verification_codes;
CREATE POLICY "sms_codes_service_only"
ON public.sms_verification_codes
AS PERMISSIVE
FOR ALL
TO service_role
USING (true)
WITH CHECK (true);

-- 3) Remove redundant always-true policies & tighten writes
-- agent_test_results: service_role bypasses RLS, the explicit policy is noise
DROP POLICY IF EXISTS "Service role manages agent_test_results" ON public.agent_test_results;

-- business_alerts: restrict insert to service_role
DROP POLICY IF EXISTS "Service role can insert alerts" ON public.business_alerts;
CREATE POLICY "Service role can insert alerts"
ON public.business_alerts
FOR INSERT
TO service_role
WITH CHECK (true);

-- ai_read_log: enforce user ownership on insert
DROP POLICY IF EXISTS "ai_log_insert" ON public.ai_read_log;
CREATE POLICY "ai_log_insert"
ON public.ai_read_log
FOR INSERT
TO authenticated
WITH CHECK (auth.uid() IS NOT NULL);

-- error_logs / perf_logs: only authenticated clients may write telemetry
DROP POLICY IF EXISTS "anyone_can_insert_error_logs" ON public.error_logs;
CREATE POLICY "authenticated_can_insert_error_logs"
ON public.error_logs
FOR INSERT
TO authenticated
WITH CHECK (auth.uid() IS NOT NULL);

DROP POLICY IF EXISTS "anyone_can_insert_perf_logs" ON public.perf_logs;
CREATE POLICY "authenticated_can_insert_perf_logs"
ON public.perf_logs
FOR INSERT
TO authenticated
WITH CHECK (auth.uid() IS NOT NULL);

-- 4) Revoke EXECUTE on trigger/maintenance functions from PUBLIC/anon/authenticated
DO $$
DECLARE
  fn text;
  fns text[] := ARRAY[
    'public.handle_new_user()',
    'public.update_admin_conversation_stats()',
    'public.check_user_coupon_limit()',
    'public.cleanup_old_test_results()',
    'public.cleanup_old_perf_logs()',
    'public.set_updated_at()',
    'public.update_updated_at_column()',
    'public.check_category_depth()',
    'public.ensure_realtime_month_partitions()',
    'public.setup_speech_to_text()',
    'public.cleanup_expired_sms_codes()'
  ];
BEGIN
  FOREACH fn IN ARRAY fns LOOP
    BEGIN
      EXECUTE format('REVOKE ALL ON FUNCTION %s FROM PUBLIC, anon, authenticated', fn);
    EXCEPTION WHEN undefined_function THEN
      NULL;
    END;
  END LOOP;
END $$;

-- 5) Sensitive SECURITY DEFINER helpers: keep authenticated, drop anon
REVOKE EXECUTE ON FUNCTION public.get_admin_conversation_history(uuid, integer) FROM anon, PUBLIC;
REVOKE EXECUTE ON FUNCTION public.merge_anonymous_user_data(uuid, uuid) FROM anon, PUBLIC;
REVOKE EXECUTE ON FUNCTION public.get_user_profile(uuid) FROM anon, PUBLIC;
