-- Verification script to check if the recursive call guard fix is deployed
-- Run this in Supabase SQL Editor to verify deployment status

-- Check if function exists and has the recursive guard
DO $$
DECLARE
  v_func_def text;
  v_has_guard boolean := false;
BEGIN
  -- Get function definition
  SELECT pg_get_functiondef(oid) INTO v_func_def
  FROM pg_proc 
  WHERE proname = 'fn_task_update_process_metrics';
  
  IF v_func_def IS NULL THEN
    RAISE NOTICE 'STATUS: Function fn_task_update_process_metrics NOT FOUND - Not deployed';
    RETURN;
  END IF;
  
  -- Check for recursive guard components
  IF v_func_def LIKE '%task_update_processing%' THEN
    v_has_guard := true;
  END IF;
  
  IF v_has_guard THEN
    RAISE NOTICE 'STATUS: ✓ Function deployed WITH recursive call guard';
    RAISE NOTICE 'The fix for infinite loop prevention is ACTIVE';
  ELSE
    RAISE NOTICE 'STATUS: ✗ Function deployed WITHOUT recursive call guard';
    RAISE NOTICE 'WARNING: The function needs to be updated with the fix';
  END IF;
  
  -- Show trigger status
  IF EXISTS (
    SELECT 1 FROM pg_trigger 
    WHERE tgname = 'trg_task_update_process_metrics'
  ) THEN
    RAISE NOTICE 'STATUS: ✓ Trigger trg_task_update_process_metrics is ACTIVE';
  ELSE
    RAISE NOTICE 'STATUS: ✗ Trigger trg_task_update_process_metrics NOT FOUND';
  END IF;
END $$;

-- Show recent trigger executions (if logging table exists)
SELECT 
  trigger_name,
  status,
  execution_started_at,
  execution_duration_ms,
  note
FROM trigger_execution_log
WHERE trigger_name = 'trg_task_update_process_metrics'
ORDER BY execution_started_at DESC
LIMIT 10;

