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

CREATE VIEW experiment_pages_flat AS
SELECT 
  -- Experiment base data
  e.id as experiment_id,
  e.project_id,
  p_info.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,
  
  -- Campaign info (for Web experiments)
  json_extract(e.data_json, '$.campaign_id') as campaign_id,
  
  -- Page data (fully denormalized)
  p.id as page_id,
  p.key as page_key,
  p.name as page_name,
  p.edit_url as page_edit_url,
  p.activation_type as page_activation_type,
  p.activation_code as page_activation_code,
  p.category as page_category,
  p.page_type as page_type,
  p.archived as page_archived,
  p.created_time as page_created_time,
  p.updated_time as page_updated_time,
  
  -- Flattened page conditions
  p.conditions as page_conditions_raw,
  json_extract(p.conditions, '$[0]') as condition_operator,
  json_extract(p.conditions, '$[1][0].type') as condition_type,
  json_extract(p.conditions, '$[1][0].value') as condition_value,
  json_extract(p.conditions, '$[1][0].match_type') as condition_match_type,
  
  -- Additional condition extraction for complex queries
  CASE 
    WHEN json_array_length(p.conditions) > 1 
    THEN json_extract(p.conditions, '$[1]')
    ELSE NULL
  END as condition_array,
  
  -- Computed targeting flags
  CASE 
    WHEN json_extract(p.conditions, '$[1][0].type') = 'url' THEN 1 
    ELSE 0 
  END as has_url_targeting,
  
  CASE 
    WHEN json_extract(p.conditions, '$[1][0].type') = 'cookie' THEN 1 
    ELSE 0 
  END as has_cookie_targeting,
  
  CASE 
    WHEN json_extract(p.conditions, '$[1][0].type') = 'query' THEN 1 
    ELSE 0 
  END as has_query_targeting,
  
  CASE 
    WHEN json_extract(p.conditions, '$[1][0].type') = 'referrer' THEN 1 
    ELSE 0 
  END as has_referrer_targeting,
  
  -- URL pattern extraction
  CASE 
    WHEN json_extract(p.conditions, '$[1][0].type') = 'url' 
    THEN json_extract(p.conditions, '$[1][0].value')
    ELSE NULL
  END as target_url_pattern,
  
  -- Match type categorization
  CASE json_extract(p.conditions, '$[1][0].match_type')
    WHEN 'simple' THEN 'simple'
    WHEN 'exact' THEN 'exact'
    WHEN 'regex' THEN 'regex'
    WHEN 'substring' THEN 'substring'
    ELSE 'other'
  END as match_type_category,
  
  -- Relationship metadata
  'experiment_page' as relationship_type,
  json_array_length(json_extract(e.data_json, '$.page_ids')) as total_pages_in_experiment,
  CURRENT_TIMESTAMP as view_updated_at

FROM experiments e
CROSS JOIN json_each(COALESCE(json_extract(e.data_json, '$.page_ids'), '[]'))
INNER JOIN pages p ON p.id = json_each.value
LEFT JOIN projects p_info ON e.project_id = p_info.id
WHERE p.id IS NOT NULL
  AND e.archived = 0  -- Exclude archived experiments
  AND p.archived = 0;
