-- Auto-extracted view: experiments_unified_view
-- Extracted at: 2025-07-08T19:54:36.052Z
-- Status: Known (has source file)
-- Original source: src/analytics/views/

CREATE VIEW experiments_unified_view AS
-- Web Experimentation Experiments
SELECT 
  -- Core Identification
  'web' as platform,
  e.project_id,
  p.name as project_name,
  e.id as experiment_id,
  e.name as experiment_name,
  e.description,
  e.status,
  e.type as experiment_type,
  
  -- Campaign Information
  json_extract(e.data_json, '$.campaign_id') as campaign_id,
  c.name as campaign_name,
  COALESCE(c.holdback, 0) as campaign_holdback_percentage,
  
  -- Timing Information
  e.created_time,
  e.updated_time,
  json_extract(e.data_json, '$.earliest') as start_time,
  json_extract(e.data_json, '$.latest') as end_time,
  
  -- Traffic Information
  COALESCE(json_extract(e.data_json, '$.traffic_allocation'), 10000) / 100.0 as traffic_percentage,
  
  -- Variation Count
  CASE 
    WHEN json_extract(e.data_json, '$.variations') IS NOT NULL
    THEN json_array_length(json_extract(e.data_json, '$.variations'))
    ELSE 0
  END as variation_count,
  
  -- Metrics Count
  CASE 
    WHEN json_extract(e.data_json, '$.metrics') IS NOT NULL
    THEN json_array_length(json_extract(e.data_json, '$.metrics'))
    ELSE 0
  END as metric_count,
  
  -- Audience Count
  CASE 
    WHEN json_extract(e.data_json, '$.audience_ids') IS NOT NULL
    THEN json_array_length(json_extract(e.data_json, '$.audience_ids'))
    ELSE 0
  END as audience_count,
  
  -- Page Count
  CASE 
    WHEN json_extract(e.data_json, '$.page_ids') IS NOT NULL
    THEN json_array_length(json_extract(e.data_json, '$.page_ids'))
    ELSE 0
  END as page_count,
  
  -- URL Targeting Condition Count
  CASE 
    WHEN json_extract(e.data_json, '$.url_targeting.conditions') IS NOT NULL
    THEN json_array_length(json_extract(e.data_json, '$.url_targeting.conditions'))
    ELSE 0
  END as url_condition_count,
  
  -- Status Categories
  CASE
    WHEN e.status = 'running' THEN 'active'
    WHEN e.status IN ('paused', 'not_started') THEN 'inactive'
    WHEN e.status = 'archived' THEN 'archived'
    ELSE 'other'
  END as status_category,
  
  -- Days Running
  CASE 
    WHEN e.status = 'running' AND json_extract(e.data_json, '$.earliest') IS NOT NULL
    THEN CAST((julianday('now') - julianday(json_extract(e.data_json, '$.earliest'))) AS INTEGER)
    ELSE 0
  END as days_running,
  
  -- Days Since Update
  CAST((julianday('now') - julianday(e.updated_time)) AS INTEGER) as days_since_update,
  
  -- Environment (Web experiments don't have environments like flags)
  'production' as environment_key,
  'Production' as environment_name,
  
  -- Extension Usage
  CASE 
    WHEN json_extract(e.data_json, '$.extensions') IS NOT NULL 
      AND json_array_length(json_extract(e.data_json, '$.extensions')) > 0
    THEN 1 
    ELSE 0
  END as uses_extensions,
  
  -- Page Targeting Type
  CASE 
    WHEN json_extract(e.data_json, '$.url_targeting') IS NOT NULL THEN 'url_targeting'
    WHEN json_extract(e.data_json, '$.page_ids') IS NOT NULL THEN 'page_ids'
    ELSE 'none'
  END as page_targeting_type,
  
  -- Feature flag association (null for web experiments)
  NULL as flag_key,
  NULL as flag_name

FROM experiments e
INNER JOIN projects p ON e.project_id = p.id
LEFT JOIN campaigns c ON CAST(c.id AS INTEGER) = json_extract(e.data_json, '$.campaign_id')
WHERE (p.is_flags_enabled = 0 OR p.is_flags_enabled IS NULL)

UNION ALL

-- Feature Experimentation A/B Tests (Rules with type='a/b')
SELECT 
  -- Core Identification
  'feature' as platform,
  f.project_id,
  p.name as project_name,
  r.id as experiment_id,
  r.name as experiment_name,
  f.description,
  CASE 
    WHEN fe.enabled = 1 AND r.enabled = 1 THEN 'running'
    ELSE 'paused'
  END as status,
  'a/b' as experiment_type,
  
  -- Campaign Information (Feature experiments don't have campaigns)
  NULL as campaign_id,
  NULL as campaign_name,
  NULL as campaign_holdback_percentage,
  
  -- Timing Information
  r.created_time,
  r.updated_time,
  r.created_time as start_time,
  NULL as end_time,
  
  -- Traffic Information
  COALESCE(r.percentage_included, 0) / 100.0 as traffic_percentage,
  
  -- Variation Count (from JSON object keys)
  CASE 
    WHEN r.variations IS NOT NULL AND r.variations != '{}' AND r.variations != '[]'
    THEN (SELECT COUNT(*) FROM json_each(r.variations))
    ELSE 0
  END as variation_count,
  
  -- Metrics Count
  CASE 
    WHEN r.metrics IS NOT NULL AND r.metrics != '[]'
    THEN json_array_length(r.metrics)
    ELSE 0
  END as metric_count,
  
  -- Audience Count
  CASE 
    WHEN r.audience_conditions IS NOT NULL AND r.audience_conditions != '[]'
    THEN 1  -- Feature experiments have audience conditions, not audience lists
    ELSE 0
  END as audience_count,
  
  -- Page Count (Feature experiments don't have pages)
  0 as page_count,
  0 as url_condition_count,
  
  -- Status Categories
  CASE
    WHEN fe.enabled = 1 AND r.enabled = 1 THEN 'active'
    ELSE 'inactive'
  END as status_category,
  
  -- Days Running
  CAST((julianday('now') - julianday(r.created_time)) AS INTEGER) as days_running,
  
  -- Days Since Update
  CAST((julianday('now') - julianday(r.updated_time)) AS INTEGER) as days_since_update,
  
  -- Environment
  r.environment_key,
  e.name as environment_name,
  
  -- Extension Usage (Feature experiments don't use extensions)
  0 as uses_extensions,
  'none' as page_targeting_type,
  
  -- Feature flag association
  r.flag_key,
  f.name as flag_name

FROM rules r
INNER JOIN flag_environments fe ON r.project_id = fe.project_id 
  AND r.flag_key = fe.flag_key 
  AND r.environment_key = fe.environment_key
INNER JOIN flags f ON r.project_id = f.project_id AND r.flag_key = f.key
INNER JOIN projects p ON f.project_id = p.id
LEFT JOIN environments e ON fe.project_id = e.project_id AND fe.environment_key = e.key
WHERE p.is_flags_enabled = 1 AND r.type = 'a/b';
