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

CREATE VIEW experiment_events_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,
  
  -- Metric/event configuration from experiment
  json_extract(metric.value, '$.event_id') as event_id,
  json_extract(metric.value, '$.aggregator') as metric_aggregator,
  json_extract(metric.value, '$.scope') as metric_scope,
  json_extract(metric.value, '$.winning_direction') as winning_direction,
  json_extract(metric.value, '$.field') as metric_field,
  
  -- Event data (fully denormalized)
  ev.key as event_key,
  ev.name as event_name,
  ev.description as event_description,
  ev.event_type,
  ev.category as event_category,
  ev.archived as event_archived,
  ev.created_time as event_created_time,
  ev.created_time as event_updated_time,  -- events table doesn't have last_modified
  
  -- Event configuration details (from data_json)
  json_extract(ev.data_json, '$.selector') as event_selector,
  json_extract(ev.data_json, '$.xpath') as event_xpath,
  json_extract(ev.data_json, '$.trigger') as event_trigger,
  json_extract(ev.data_json, '$.url_match_type') as url_match_type,
  json_extract(ev.data_json, '$.url_conditions') as url_conditions,
  
  -- Event type categorization
  CASE ev.event_type
    WHEN 'click' THEN 'interaction'
    WHEN 'pageview' THEN 'pageview'
    WHEN 'custom' THEN 'custom'
    WHEN 'submission' THEN 'interaction'
    WHEN 'scroll' THEN 'interaction'
    ELSE 'other'
  END as event_category_type,
  
  -- Event type flags
  CASE WHEN ev.event_type = 'click' THEN 1 ELSE 0 END as is_click_event,
  CASE WHEN ev.event_type = 'pageview' THEN 1 ELSE 0 END as is_pageview_event,
  CASE WHEN ev.event_type = 'custom' THEN 1 ELSE 0 END as is_custom_event,
  CASE WHEN ev.event_type = 'submission' THEN 1 ELSE 0 END as is_submission_event,
  CASE WHEN ev.event_type IN ('click', 'submission', 'scroll') THEN 1 ELSE 0 END as is_interaction_event,
  
  -- Selector analysis
  CASE 
    WHEN json_extract(ev.data_json, '$.selector') IS NOT NULL 
      AND json_extract(ev.data_json, '$.selector') != '' THEN 1 
    ELSE 0 
  END as has_css_selector,
  
  CASE 
    WHEN json_extract(ev.data_json, '$.selector') LIKE '#%' THEN 'id'
    WHEN json_extract(ev.data_json, '$.selector') LIKE '.%' THEN 'class'
    WHEN json_extract(ev.data_json, '$.selector') LIKE '%[%]%' THEN 'attribute'
    WHEN json_extract(ev.data_json, '$.selector') IS NOT NULL THEN 'element'
    ELSE NULL
  END as selector_type,
  
  -- URL targeting for events
  CASE 
    WHEN json_extract(ev.data_json, '$.url_conditions') IS NOT NULL 
      AND json_extract(ev.data_json, '$.url_conditions') != '' THEN 1 
    ELSE 0 
  END as has_url_conditions,
  
  -- Metric configuration analysis
  CASE json_extract(metric.value, '$.aggregator')
    WHEN 'unique' THEN 'unique_conversions'
    WHEN 'sum' THEN 'total_value'
    WHEN 'count' THEN 'total_conversions'
    ELSE 'other'
  END as metric_type,
  
  CASE json_extract(metric.value, '$.scope')
    WHEN 'visitor' THEN 'visitor'
    WHEN 'session' THEN 'session'
    WHEN 'impression' THEN 'impression'
    ELSE 'unknown'
  END as metric_scope_type,
  
  -- Usage statistics
  (
    SELECT COUNT(DISTINCT ex.id)
    FROM experiments ex
    WHERE ex.project_id = e.project_id
      AND json_extract(ex.data_json, '$.metrics') LIKE '%"event_id":' || ev.id || '%'
  ) as total_experiment_usage,
  
  (
    SELECT COUNT(DISTINCT ex.id)
    FROM experiments ex
    WHERE ex.project_id = e.project_id
      AND ex.status = 'running'
      AND json_extract(ex.data_json, '$.metrics') LIKE '%"event_id":' || ev.id || '%'
  ) as active_experiment_usage,
  
  -- Event status
  CASE 
    WHEN ev.archived = 1 THEN 'archived'
    WHEN EXISTS (
      SELECT 1 FROM experiments ex 
      WHERE ex.status = 'running' 
        AND json_extract(ex.data_json, '$.metrics') LIKE '%"event_id":' || ev.id || '%'
    ) THEN 'active'
    ELSE 'inactive'
  END as event_usage_status,
  
  -- Relationship metadata
  'experiment_event' as relationship_type,
  json_array_length(json_extract(e.data_json, '$.metrics')) as total_metrics_in_experiment,
  CURRENT_TIMESTAMP as view_updated_at

FROM experiments e
CROSS JOIN json_each(COALESCE(json_extract(e.data_json, '$.metrics'), '[]')) as metric
LEFT JOIN events ev ON ev.id = json_extract(metric.value, '$.event_id')
LEFT JOIN projects pr ON e.project_id = pr.id
WHERE json_extract(metric.value, '$.event_id') IS NOT NULL
  AND e.archived = 0;
