-- Fix admin_provider_stats view to include email field
DROP VIEW IF EXISTS admin_provider_stats;

CREATE OR REPLACE VIEW admin_provider_stats AS
SELECT 
  p.id,
  p.email,
  p.username,
  p.full_name,
  p.role,
  p.is_active,
  p.created_at,
  ps.business_name,
  ps.destinations,
  ps.activity_categories,
  COALESCE(pw.credit_balance, 0) as credit_balance,
  COALESCE(COUNT(DISTINCT lp.lead_id), 0) as purchased_leads_count,
  COALESCE(SUM(lp.credits_spent), 0) as total_spend
FROM profiles p
LEFT JOIN provider_services ps ON p.id = ps.provider_id
LEFT JOIN provider_wallets pw ON p.id = pw.provider_id
LEFT JOIN lead_purchases lp ON p.id = lp.provider_id
WHERE p.role = 'provider'
GROUP BY p.id, p.email, ps.business_name, ps.destinations, ps.activity_categories, pw.credit_balance;

-- Grant permissions
GRANT SELECT ON admin_provider_stats TO authenticated;

COMMENT ON VIEW admin_provider_stats IS 'Admin view for provider statistics including email, business info, wallet balance, and purchase history';