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

-- ============================================
-- PROBLEM ANALYSIS
-- ============================================
-- Current issue: Both matches and payments tables have INSERT policies with WITH CHECK (true)
-- This means ANY authenticated user can insert records into these tables
-- 
-- Security impact:
-- 1. Users could insert fake matches between any job and worker
-- 2. Users could insert fake payment records
-- 3. Financial fraud, data corruption, system abuse
-- 
-- Correct behavior:
-- 1. matches INSERT: Only workers (applying) or contractors (inviting) for their own jobs
-- 2. payments INSERT: Only system/service role when match is hired
-- ============================================

-- ============================================
-- 1. FIX MATCHES TABLE INSERT POLICY
-- ============================================

-- Drop the dangerous policy
DROP POLICY IF EXISTS "System can insert matches" ON matches;

-- Create secure INSERT policy for matches
-- Allows:
-- 1. Workers to apply for jobs (worker_id must be their own ID)
-- 2. Contractors to suggest matches for their own jobs
-- 3. Service role (system) for automated matching
CREATE POLICY "Secure matches insert"
  ON matches FOR INSERT
  WITH CHECK (
    -- Worker applying for a job
    (worker_id = auth.uid() AND status = 'applied')
    OR
    -- Contractor suggesting a worker for 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 (for automated matching)
    (auth.jwt() ->> 'role' = 'service_role')
  );

-- ============================================
-- 2. FIX PAYMENTS TABLE INSERT POLICY
-- ============================================

-- Drop the dangerous policy
DROP POLICY IF EXISTS "System can insert payments" ON payments;

-- Create secure INSERT policy for payments
-- Only system/service role can insert payments, and only for hired matches
CREATE POLICY "Secure payments insert"
  ON payments FOR INSERT
  WITH CHECK (
    -- Must be service role
    (auth.jwt() ->> 'role' = 'service_role')
    AND
    -- Payment must be for a hired match
    EXISTS (
      SELECT 1 FROM matches 
      WHERE id = match_id AND status = 'hired'
    )
    AND
    -- Payment company must own the 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
    )
  );

-- ============================================
-- 3. ADD DATA VALIDATION TRIGGER FOR PAYMENTS
-- ============================================

-- Ensure payments are only created for valid hired matches
CREATE OR REPLACE FUNCTION validate_payment_creation()
RETURNS trigger AS $$
BEGIN
  -- Verify match is 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 (match status must be "hired")';
  END IF;
  
  -- Verify company owns the job
  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 own the job associated with the match';
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Drop existing trigger if any
DROP TRIGGER IF EXISTS validate_payment_creation_trigger ON payments;

-- Create trigger
CREATE TRIGGER validate_payment_creation_trigger
  BEFORE INSERT ON payments
  FOR EACH ROW EXECUTE FUNCTION validate_payment_creation();

-- ============================================
-- 4. UPDATE APPLICATION LOGIC RECOMMENDATIONS
-- ============================================

-- Note for developers: Application code should now:
-- 1. Use service role key for server-side operations
-- 2. Handle match creation through proper workflows:
--    - Worker apply: Frontend calls API, API validates worker owns worker_id
--    - Contractor invite: Frontend calls API, API validates contractor owns job
--    - System matching: Background job uses service role
-- 3. Payment creation: Only through server-side after Stripe success

-- ============================================
-- 5. VERIFICATION QUERIES (RUN AFTER MIGRATION)
-- ============================================

/*
-- Check the new policies
SELECT 
  tablename,
  policyname,
  cmd,
  qual,
  with_check
FROM pg_policies 
WHERE tablename IN ('matches', 'payments')
ORDER BY tablename, policyname;

-- Test: Try to insert a match as a regular user (should fail without proper conditions)
-- INSERT INTO matches (job_id, worker_id, status) 
-- VALUES ('some-job-id', 'some-worker-id', 'suggested');

-- Test: Try to insert a payment as a regular user (should always fail)
-- INSERT INTO payments (match_id, company_id, amount) 
-- VALUES ('some-match-id', 'some-company-id', 50.00);
*/

-- ============================================
-- 6. ROLLBACK SCRIPT (IN CASE OF ISSUES)
-- ============================================
/*
-- Rollback to original (insecure) policies:
DROP POLICY IF EXISTS "Secure matches insert" ON matches;
DROP POLICY IF EXISTS "Secure payments insert" ON payments;
DROP TRIGGER IF EXISTS validate_payment_creation_trigger ON payments;
DROP FUNCTION IF EXISTS validate_payment_creation;

-- Recreate original policies (INSECURE - FOR EMERGENCY ONLY):
CREATE POLICY "System can insert matches"
  ON matches FOR INSERT
  WITH CHECK (true);

CREATE POLICY "System can insert payments"
  ON payments FOR INSERT
  WITH CHECK (true);
*/
