-- Migration: Add DELETE policies to all tables
-- Created: 2026-02-09
-- Description: Adds RLS DELETE policies so users can delete their own data.
--              Service role bypasses RLS by default and can delete anything.

BEGIN;

-- ============================================
-- profiles: Users can delete their own profile
-- ============================================
DROP POLICY IF EXISTS "Users can delete their own profile" ON profiles;
CREATE POLICY "Users can delete their own profile"
  ON profiles FOR DELETE
  USING (auth.uid() = id);

-- ============================================
-- companies: Contractors can delete their own company
-- ============================================
DROP POLICY IF EXISTS "Contractors can delete their own company" ON companies;
CREATE POLICY "Contractors can delete their own company"
  ON companies FOR DELETE
  USING (profile_id = auth.uid());

-- ============================================
-- worker_profiles: Workers can delete their own profile
-- ============================================
DROP POLICY IF EXISTS "Workers can delete their own profile" ON worker_profiles;
CREATE POLICY "Workers can delete their own profile"
  ON worker_profiles FOR DELETE
  USING (profile_id = auth.uid());

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

-- ============================================
-- matches: Involved parties can delete matches
-- Worker who applied OR contractor who owns the job
-- ============================================
DROP POLICY IF EXISTS "Involved parties can delete matches" ON matches;
DROP POLICY IF EXISTS "Workers can withdraw their own applications" ON matches;
CREATE POLICY "Involved parties can delete matches"
  ON matches FOR DELETE
  USING (
    worker_id = auth.uid()
    OR job_id IN (
      SELECT j.id FROM jobs j
      JOIN companies c ON j.company_id = c.id
      WHERE c.profile_id = auth.uid()
    )
  );

-- ============================================
-- ratings: Users can delete ratings they authored
-- ============================================
DROP POLICY IF EXISTS "Users can delete their own ratings" ON ratings;
CREATE POLICY "Users can delete their own ratings"
  ON ratings FOR DELETE
  USING (from_id = auth.uid());

-- ============================================
-- payments: Company owners can delete their payments
-- ============================================
DROP POLICY IF EXISTS "Company owners can delete their payments" ON payments;
CREATE POLICY "Company owners can delete their payments"
  ON payments FOR DELETE
  USING (company_id IN (
    SELECT id FROM companies WHERE profile_id = auth.uid()
  ));

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

-- ============================================
-- conversations: Participants can delete conversations they belong to
-- ============================================
DROP POLICY IF EXISTS "Users can delete their own conversations" ON conversations;
CREATE POLICY "Users can delete their own conversations"
  ON conversations FOR DELETE
  USING (id IN (
    SELECT conversation_id FROM conversation_participants
    WHERE profile_id = auth.uid()
  ));

-- ============================================
-- conversation_participants: Users can remove themselves from conversations
-- ============================================
DROP POLICY IF EXISTS "Users can delete their own participation" ON conversation_participants;
CREATE POLICY "Users can delete their own participation"
  ON conversation_participants FOR DELETE
  USING (profile_id = auth.uid());

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

COMMIT;
