-- Create a debug function to help troubleshoot provider lead visibility issues
-- This function bypasses RLS to show what leads SHOULD be visible to a provider

CREATE OR REPLACE FUNCTION debug_provider_leads(p_provider_id UUID)
RETURNS TABLE (
  lead_id UUID,
  destination TEXT,
  interests TEXT[],
  status TEXT,
  consent_given BOOLEAN,
  number_of_travelers INTEGER,
  start_date DATE,
  end_date DATE,
  provider_has_role BOOLEAN,
  provider_destinations TEXT[],
  provider_categories TEXT[],
  destination_matches BOOLEAN,
  has_interest_match BOOLEAN,
  matching_interests TEXT[]
) 
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  WITH provider_info AS (
    SELECT 
      (SELECT role = 'provider' FROM profiles WHERE id = p_provider_id) as has_role,
      (SELECT destinations FROM provider_services WHERE provider_id = p_provider_id) as dests,
      (SELECT activity_categories FROM provider_services WHERE provider_id = p_provider_id) as cats
  )
  SELECT 
    l.id as lead_id,
    l.destination,
    l.interests,
    l.status,
    l.consent_given,
    l.number_of_travelers,
    l.start_date,
    l.end_date,
    pi.has_role as provider_has_role,
    pi.dests as provider_destinations,
    pi.cats as provider_categories,
    (l.destination = ANY(pi.dests)) as destination_matches,
    (
      EXISTS (
        SELECT 1 
        FROM unnest(l.interests) AS lead_interest
        JOIN unnest(pi.cats) AS provider_cat ON LOWER(TRIM(lead_interest)) = LOWER(TRIM(provider_cat))
      )
    ) as has_interest_match,
    (
      SELECT ARRAY_AGG(DISTINCT lead_interest)
      FROM unnest(l.interests) AS lead_interest
      JOIN unnest(pi.cats) AS provider_cat ON LOWER(TRIM(lead_interest)) = LOWER(TRIM(provider_cat))
    ) as matching_interests
  FROM leads l
  CROSS JOIN provider_info pi
  WHERE l.consent_given = true 
    AND l.status = 'new'
  ORDER BY l.created_at DESC;
END;
$$ LANGUAGE plpgsql;

-- Grant execute to authenticated users (admins can use this for debugging)
GRANT EXECUTE ON FUNCTION debug_provider_leads(UUID) TO authenticated;

COMMENT ON FUNCTION debug_provider_leads(UUID) IS 
'Debug function to help troubleshoot why a provider may not be seeing leads. Shows all available leads with matching criteria.';
