-- View for analyzing JavaScript code usage in Web experiments
-- Created: 2025-07-19
-- Purpose: Expose JavaScript code fields for Web experiments

CREATE VIEW IF NOT EXISTS experiment_code_analysis_view AS
SELECT 
  -- Core Identification
  e.project_id,
  p.name as project_name,
  e.id as experiment_id,
  e.name as experiment_name,
  e.status,
  e.type as experiment_type,
  
  -- Shared Code (Experiment-level JavaScript/CSS)
  CASE 
    WHEN json_extract(e.data_json, '$.changes') IS NOT NULL 
      AND json_array_length(json_extract(e.data_json, '$.changes')) > 0
    THEN 1 
    ELSE 0
  END as has_shared_code,
  
  -- Extract shared JavaScript code
  (
    SELECT GROUP_CONCAT(
      CASE 
        WHEN json_extract(value, '$.type') = 'custom_code' 
        THEN json_extract(value, '$.value')
        ELSE NULL
      END, 
      '|||'
    )
    FROM json_each(json_extract(e.data_json, '$.changes'))
    WHERE json_extract(value, '$.type') = 'custom_code'
  ) as shared_javascript_code,
  
  -- Extract shared CSS
  (
    SELECT GROUP_CONCAT(
      CASE 
        WHEN json_extract(value, '$.type') = 'custom_css' 
        THEN json_extract(value, '$.value')
        ELSE NULL
      END, 
      '|||'
    )
    FROM json_each(json_extract(e.data_json, '$.changes'))
    WHERE json_extract(value, '$.type') = 'custom_css'
  ) as shared_css_code,
  
  -- Count of shared code changes
  CASE 
    WHEN json_extract(e.data_json, '$.changes') IS NOT NULL
    THEN json_array_length(json_extract(e.data_json, '$.changes'))
    ELSE 0
  END as shared_code_count,
  
  -- Variation details with code
  json_extract(e.data_json, '$.variations') as variations_json,
  
  -- Check if any variation has JavaScript
  CASE
    WHEN EXISTS (
      SELECT 1 
      FROM json_each(json_extract(e.data_json, '$.variations')) v,
           json_each(json_extract(v.value, '$.actions')) a,
           json_each(json_extract(a.value, '$.changes')) c
      WHERE json_extract(c.value, '$.type') = 'custom_code'
    )
    THEN 1
    ELSE 0
  END as has_variation_javascript,
  
  -- Count variations with JavaScript
  (
    SELECT COUNT(DISTINCT v.key)
    FROM json_each(json_extract(e.data_json, '$.variations')) v,
         json_each(json_extract(v.value, '$.actions')) a,
         json_each(json_extract(a.value, '$.changes')) c
    WHERE json_extract(c.value, '$.type') = 'custom_code'
  ) as variations_with_javascript_count,
  
  -- Total JavaScript snippets across all variations
  (
    SELECT COUNT(*)
    FROM json_each(json_extract(e.data_json, '$.variations')) v,
         json_each(json_extract(v.value, '$.actions')) a,
         json_each(json_extract(a.value, '$.changes')) c
    WHERE json_extract(c.value, '$.type') = 'custom_code'
  ) as total_javascript_snippets,
  
  -- Campaign Information
  json_extract(e.data_json, '$.campaign_id') as campaign_id,
  c.name as campaign_name,
  
  -- Timing Information
  e.created_time,
  e.updated_time,
  
  -- Page targeting
  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,
  
  -- 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
  
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);  -- Web experiments only