-- analytics_summary_view: Simplified version for testing
CREATE VIEW IF NOT EXISTS analytics_summary_view AS
SELECT 
  f.project_id,
  p.name as project_name,
  f.environment_key,
  
  -- Flag Statistics
  COUNT(DISTINCT f.flag_key) as total_flags,
  SUM(CASE WHEN f.flag_enabled = 1 THEN 1 ELSE 0 END) as enabled_flags,
  SUM(CASE WHEN f.flag_enabled = 0 THEN 1 ELSE 0 END) as disabled_flags,
  
  -- Rule type breakdown  
  SUM(CASE WHEN f.rule_type = 'a/b' THEN 1 ELSE 0 END) as ab_test_count,
  SUM(CASE WHEN f.rule_type = 'rollout' THEN 1 ELSE 0 END) as rollout_count,
  
  -- Orphaned entities from usage view
  (SELECT COUNT(*) FROM entity_usage_view eu 
   WHERE eu.project_id = f.project_id 
   AND eu.entity_type = 'page' 
   AND eu.usage_status = 'orphaned') as orphaned_pages,
   
  (SELECT COUNT(*) FROM entity_usage_view eu 
   WHERE eu.project_id = f.project_id 
   AND eu.entity_type = 'audience' 
   AND eu.usage_status = 'orphaned') as orphaned_audiences

FROM flags_unified_view f
INNER JOIN projects p ON f.project_id = p.id
GROUP BY f.project_id, p.name, f.environment_key;