-- ============================================
-- TEST CURRENT RLS POLICIES
-- ============================================
-- Run this in Supabase SQL Editor to check current policies
-- ============================================

-- 1. Check all policies on matches and payments tables
SELECT 
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies 
WHERE tablename IN ('matches', 'payments')
ORDER BY tablename, policyname;

-- 2. Check if RLS is enabled
SELECT 
  schemaname,
  tablename,
  rowsecurity
FROM pg_tables
WHERE tablename IN ('matches', 'payments');

-- 3. Test data (sample queries to understand current state)
SELECT COUNT(*) as total_matches FROM matches;
SELECT COUNT(*) as total_payments FROM payments;

SELECT 
  status,
  COUNT(*) as count
FROM matches
GROUP BY status
ORDER BY count DESC;

SELECT 
  status,
  COUNT(*) as count
FROM payments
GROUP BY status
ORDER BY count DESC;

-- 4. Check recent activity
SELECT 
  'matches' as table_name,
  COUNT(*) as last_24h_count
FROM matches
WHERE created_at > NOW() - INTERVAL '24 hours'
UNION ALL
SELECT 
  'payments' as table_name,
  COUNT(*) as last_24h_count
FROM payments
WHERE created_at > NOW() - INTERVAL '24 hours';

-- 5. Check for potential abuse (multiple inserts from same user)
SELECT 
  worker_id,
  COUNT(*) as match_count,
  MIN(created_at) as first_match,
  MAX(created_at) as last_match
FROM matches
GROUP BY worker_id
HAVING COUNT(*) > 10
ORDER BY match_count DESC
LIMIT 10;

-- 6. Check payments without hired matches (potential fraud)
SELECT 
  p.id as payment_id,
  p.match_id,
  p.amount,
  p.status as payment_status,
  p.created_at as payment_created,
  m.status as match_status,
  m.hired_at
FROM payments p
LEFT JOIN matches m ON p.match_id = m.id
WHERE m.status != 'hired' OR m.id IS NULL
ORDER BY p.created_at DESC;

-- ============================================
-- SIMPLE FIX FOR IMMEDIATE DEPLOYMENT
-- ============================================
-- If the application is breaking, use this minimal fix
-- instead of the full secure policies

/*
-- Minimal fix: Allow contractors to insert payments for their matches
DROP POLICY IF EXISTS "System can insert payments" ON payments;

CREATE POLICY "Contractors can insert payments for their hired matches"
  ON payments FOR INSERT
  WITH CHECK (
    -- Contractor owns the company
    company_id IN (
      SELECT id FROM companies WHERE profile_id = auth.uid()
    )
    AND
    -- Match is hired
    EXISTS (
      SELECT 1 FROM matches 
      WHERE id = match_id AND status = 'hired'
    )
    AND
    -- Match belongs to contractor's job
    EXISTS (
      SELECT 1 FROM matches m
      JOIN jobs j ON m.job_id = j.id
      WHERE m.id = match_id AND j.company_id = company_id
    )
  );

-- Keep the original matches policy (workers can apply)
-- This should already work with: worker_id = auth.uid() AND status = 'applied'
*/
