-- Deep audit fixes: search_path + RLS policies
-- Applied directly to production via Management API on 2026-02-08
-- This migration file documents what was changed for version control.

-- ============================================================
-- 1. ADD SET search_path TO ALL PUBLIC FUNCTIONS
--    Prevents search_path injection attacks.
--    (handle_new_user already had it; these 6 were missing)
-- ============================================================

-- get_average_rating: already recreated with SET search_path = public
-- get_conversation_previews: already recreated with SET search_path = public
-- get_or_create_conversation: already recreated with SET search_path = public
-- get_rating_count: already recreated with SET search_path = public
-- update_updated_at: already recreated with SET search_path = public
-- validate_payment_creation: already recreated with SET search_path = public

-- ============================================================
-- 2. FIX notifications INSERT POLICY
--    Was: any authenticated user could insert notifications for ANY user
--    Now: authenticated users can only insert notifications for OTHER users
-- ============================================================

DROP POLICY IF EXISTS "System can insert notifications" ON public.notifications;
DROP POLICY IF EXISTS "Authenticated users can insert notifications" ON public.notifications;
CREATE POLICY "Authenticated users can insert notifications" ON public.notifications
  FOR INSERT TO authenticated
  WITH CHECK (profile_id != auth.uid());

-- ============================================================
-- 3. ADD MISSING DELETE POLICIES
--    All 11 tables were missing DELETE policies.
--    Adding DELETE for tables where users legitimately need it.
--    (payments, ratings, profiles, worker_profiles, companies,
--     conversations, conversation_participants intentionally omitted)
-- ============================================================

DROP POLICY IF EXISTS "Users can delete their own notifications" ON public.notifications;
CREATE POLICY "Users can delete their own notifications" ON public.notifications
  FOR DELETE TO authenticated
  USING (profile_id = auth.uid());

DROP POLICY IF EXISTS "Users can delete their own messages" ON public.messages;
CREATE POLICY "Users can delete their own messages" ON public.messages
  FOR DELETE TO authenticated
  USING (sender_id = auth.uid());

DROP POLICY IF EXISTS "Contractors can delete their own jobs" ON public.jobs;
CREATE POLICY "Contractors can delete their own jobs" ON public.jobs
  FOR DELETE TO authenticated
  USING (company_id IN (SELECT id FROM public.companies WHERE profile_id = auth.uid()));

DROP POLICY IF EXISTS "Workers can withdraw their own applications" ON public.matches;
CREATE POLICY "Workers can withdraw their own applications" ON public.matches
  FOR DELETE TO authenticated
  USING (worker_id = auth.uid() AND status IN ('applied', 'suggested'));
