-- Auto-extracted view: experiment_audiences_flat
-- Extracted at: 2025-07-08T19:54:36.043Z
-- Status: Unknown (no source file found)
-- Original source: UNKNOWN - possibly from archived files

CREATE VIEW experiment_audiences_flat AS
SELECT 
  -- Experiment base data
  e.id as experiment_id,
  e.project_id,
  pr.name as project_name,
  e.name as experiment_name,
  e.description as experiment_description,
  e.status as experiment_status,
  e.type as experiment_type,
  e.archived as experiment_archived,
  e.created_time as experiment_created_time,
  e.updated_time as experiment_updated_time,
  
  -- Platform info
  CASE 
    WHEN pr.is_flags_enabled = 1 THEN 'feature'
    ELSE 'web'
  END as platform,
  
  -- Audience data (fully denormalized)
  a.id as audience_id,
  a.name as audience_name,
  a.description as audience_description,
  a.archived as audience_archived,
  a.created_time as audience_created_time,
  a.last_modified as audience_last_modified,
  
  -- Raw conditions for complex queries
  a.conditions as audience_conditions_raw,
  
  -- Parsed audience conditions
  json_extract(a.conditions, '$[0]') as condition_operator,
  
  -- Primary condition extraction
  CASE 
    WHEN json_tree.type = 'object' AND json_extract(json_tree.value, '$.type') IS NOT NULL
    THEN json_extract(json_tree.value, '$.type')
    ELSE NULL
  END as primary_condition_type,
  
  CASE 
    WHEN json_tree.type = 'object' AND json_extract(json_tree.value, '$.value') IS NOT NULL
    THEN json_extract(json_tree.value, '$.value')
    ELSE NULL
  END as primary_condition_value,
  
  -- Condition type flags
  CASE WHEN a.conditions LIKE '%"type": "language"%' THEN 1 ELSE 0 END as targets_language,
  CASE WHEN a.conditions LIKE '%"type": "device"%' THEN 1 ELSE 0 END as targets_device,
  CASE WHEN a.conditions LIKE '%"type": "browser"%' THEN 1 ELSE 0 END as targets_browser,
  CASE WHEN a.conditions LIKE '%"type": "location"%' THEN 1 ELSE 0 END as targets_location,
  CASE WHEN a.conditions LIKE '%"type": "custom_attribute"%' THEN 1 ELSE 0 END as targets_custom_attribute,
  CASE WHEN a.conditions LIKE '%"type": "cookie"%' THEN 1 ELSE 0 END as targets_cookie,
  CASE WHEN a.conditions LIKE '%"type": "query"%' THEN 1 ELSE 0 END as targets_query,
  CASE WHEN a.conditions LIKE '%"type": "referrer"%' THEN 1 ELSE 0 END as targets_referrer,
  
  -- Extract specific values for common targeting types
  CASE 
    WHEN a.conditions LIKE '%"type": "language"%' 
    THEN (
      SELECT json_extract(value, '$.value')
      FROM json_tree(a.conditions)
      WHERE json_extract(value, '$.type') = 'language'
      LIMIT 1
    )
    ELSE NULL
  END as language_value,
  
  CASE 
    WHEN a.conditions LIKE '%"type": "device"%' 
    THEN (
      SELECT json_extract(value, '$.value')
      FROM json_tree(a.conditions)
      WHERE json_extract(value, '$.type') = 'device'
      LIMIT 1
    )
    ELSE NULL
  END as device_value,
  
  CASE 
    WHEN a.conditions LIKE '%"type": "browser"%' 
    THEN (
      SELECT json_extract(value, '$.value')
      FROM json_tree(a.conditions)
      WHERE json_extract(value, '$.type') = 'browser'
      LIMIT 1
    )
    ELSE NULL
  END as browser_value,
  
  -- Custom attribute extraction
  CASE 
    WHEN a.conditions LIKE '%"type": "custom_attribute"%' 
    THEN (
      SELECT json_extract(value, '$.name')
      FROM json_tree(a.conditions)
      WHERE json_extract(value, '$.type') = 'custom_attribute'
      LIMIT 1
    )
    ELSE NULL
  END as custom_attribute_name,
  
  CASE 
    WHEN a.conditions LIKE '%"type": "custom_attribute"%' 
    THEN (
      SELECT json_extract(value, '$.value')
      FROM json_tree(a.conditions)
      WHERE json_extract(value, '$.type') = 'custom_attribute'
      LIMIT 1
    )
    ELSE NULL
  END as custom_attribute_value,
  
  -- Audience complexity
  (
    SELECT COUNT(*)
    FROM json_tree(a.conditions)
    WHERE json_extract(value, '$.type') IS NOT NULL
  ) as condition_count,
  
  CASE 
    WHEN json_array_length(a.conditions) <= 2 THEN 'simple'
    WHEN json_array_length(a.conditions) <= 4 THEN 'moderate'
    ELSE 'complex'
  END as audience_complexity,
  
  -- Relationship metadata
  'experiment_audience' as relationship_type,
  json_array_length(json_extract(e.data_json, '$.audience_ids')) as total_audiences_in_experiment,
  CURRENT_TIMESTAMP as view_updated_at

FROM experiments e
CROSS JOIN json_each(COALESCE(json_extract(e.data_json, '$.audience_ids'), '[]'))
INNER JOIN audiences a ON a.id = json_each.value
LEFT JOIN projects pr ON e.project_id = pr.id
LEFT JOIN json_tree(a.conditions) ON json_tree.type = 'object' 
  AND json_extract(json_tree.value, '$.type') IS NOT NULL
WHERE a.id IS NOT NULL
  AND e.archived = 0
  AND a.archived = 0
GROUP BY e.id, e.name, a.id;
