-- ============================================
-- FIX CRITICAL RLS POLICIES - Audit Item C5
-- ============================================
-- Fix: matches + payments allow ANY user to insert (WITH CHECK true)
-- Date: 2026-02-07
-- ============================================

-- ============================================
-- 1. DROP PROBLEMATIC POLICIES
-- ============================================

-- Drop the overly permissive INSERT policies
DROP POLICY IF EXISTS "System can insert matches" ON matches;
DROP POLICY IF EXISTS "System can insert payments" ON payments;

-- ============================================
-- 2. CREATE SECURE INSERT POLICIES FOR MATCHES
-- ============================================

-- Option A: Only system/service role can insert matches
-- This is the most secure option - matches should only be created by:
-- 1. System algorithms suggesting matches
-- 2. Workers applying for jobs (but this should go through an API endpoint)
-- 3. Contractors inviting workers (but this should go through an API endpoint)

-- For now, we'll create a policy that allows:
-- 1. Workers to apply for jobs (insert matches where they are the worker)
-- 2. Contractors to invite workers to their jobs
-- 3. System/service role (using service key, not JWT)

CREATE POLICY "Workers can apply for jobs"
  ON matches FOR INSERT
  WITH CHECK (
    -- Worker applying for a job
    (worker_id = auth.uid() AND status = 'applied')
    OR
    -- Contractor inviting a worker to their job
    (
      job_id IN (
        SELECT j.id FROM jobs j
        JOIN companies c ON j.company_id = c.id
        WHERE c.profile_id = auth.uid()
      )
      AND status = 'suggested'
    )
    OR
    -- System/service role (using service key)
    (auth.jwt() ->> 'role' = 'service_role')
  );

-- ============================================
-- 3. CREATE SECURE INSERT POLICIES FOR PAYMENTS
-- ============================================

-- Payments should ONLY be created by:
-- 1. System when a match is hired (status changes to 'hired')
-- 2. System/service role (using service key)

CREATE POLICY "System can insert payments for hired matches"
  ON payments FOR INSERT
  WITH CHECK (
    -- Only system/service role can insert payments
    (auth.jwt() ->> 'role' = 'service_role')
    AND
    -- Payment must be for a hired match
    match_id IN (
      SELECT id FROM matches WHERE status = 'hired'
    )
    AND
    -- Payment company must match the job's company
    company_id IN (
      SELECT j.company_id FROM matches m
      JOIN jobs j ON m.job_id = j.id
      WHERE m.id = match_id
    )
  );

-- ============================================
-- 4. ADDITIONAL SECURITY MEASURES
-- ============================================

-- Add a trigger to ensure payments are only created for hired matches
CREATE OR REPLACE FUNCTION validate_payment_match()
RETURNS trigger AS $$
BEGIN
  -- Check if the match is actually hired
  IF NOT EXISTS (
    SELECT 1 FROM matches 
    WHERE id = NEW.match_id AND status = 'hired'
  ) THEN
    RAISE EXCEPTION 'Payments can only be created for hired matches';
  END IF;
  
  -- Check if the company_id matches the job's company
  IF NOT EXISTS (
    SELECT 1 FROM matches m
    JOIN jobs j ON m.job_id = j.id
    WHERE m.id = NEW.match_id AND j.company_id = NEW.company_id
  ) THEN
    RAISE EXCEPTION 'Payment company must match the job company';
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS validate_payment_match_trigger ON payments;
CREATE TRIGGER validate_payment_match_trigger
  BEFORE INSERT ON payments
  FOR EACH ROW EXECUTE FUNCTION validate_payment_match();

-- ============================================
-- 5. UPDATE EXISTING POLICIES FOR CONSISTENCY
-- ============================================

-- Update matches UPDATE policy to be more restrictive
DROP POLICY IF EXISTS "Involved parties can update matches" ON matches;

CREATE POLICY "Involved parties can update match status"
  ON matches FOR UPDATE
  USING (
    -- Worker can update their own application status
    (worker_id = auth.uid() AND OLD.status IN ('suggested', 'applied'))
    OR
    -- Contractor can update matches for their jobs
    (
      job_id IN (
        SELECT j.id FROM jobs j
        JOIN companies c ON j.company_id = c.id
        WHERE c.profile_id = auth.uid()
      )
      AND OLD.status IN ('suggested', 'applied')
    )
    OR
    -- System can update any match
    (auth.jwt() ->> 'role' = 'service_role')
  )
  WITH CHECK (
    -- Ensure status transitions are valid
    (
      -- Worker can only accept/reject suggestions
      (worker_id = auth.uid() AND NEW.status IN ('accepted', 'rejected'))
      OR
      -- Contractor can only accept/reject applications
      (
        job_id IN (
          SELECT j.id FROM jobs j
          JOIN companies c ON j.company_id = c.id
          WHERE c.profile_id = auth.uid()
        )
        AND NEW.status IN ('accepted', 'rejected')
      )
      OR
      -- System can set to hired (after payment)
      (NEW.status = 'hired' AND auth.jwt() ->> 'role' = 'service_role')
      OR
      -- Status stays the same (other fields updated)
      (NEW.status = OLD.status)
    )
  );

-- ============================================
-- 6. VERIFICATION QUERIES
-- ============================================

-- Check current policies
SELECT 
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies 
WHERE tablename IN ('matches', 'payments')
ORDER BY tablename, policyname;

-- Test data integrity
SELECT 
  'Matches with invalid status transitions' as check_name,
  COUNT(*) as issues
FROM matches
WHERE status NOT IN ('suggested', 'applied', 'accepted', 'rejected', 'hired')
UNION ALL
SELECT 
  'Payments for non-hired matches' as check_name,
  COUNT(*) as issues
FROM payments p
LEFT JOIN matches m ON p.match_id = m.id
WHERE m.status != 'hired' OR m.id IS NULL;