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

CREATE VIEW pages_standalone_flat AS
SELECT 
  -- Page base data
  p.id as page_id,
  p.project_id,
  pr.name as project_name,
  p.key as page_key,
  p.name as page_name,
  p.edit_url,
  p.activation_type,
  p.activation_code,
  p.category,
  p.page_type,
  p.archived,
  p.created_time,
  p.updated_time,
  
  -- Raw conditions (for complex queries)
  p.conditions as conditions_raw,
  
  -- Condition structure analysis
  json_array_length(p.conditions) as condition_depth,
  json_extract(p.conditions, '$[0]') as root_operator, -- 'and' or 'or'
  
  -- Primary condition extraction (most pages have single condition)
  json_extract(p.conditions, '$[1][0].type') as primary_condition_type,
  json_extract(p.conditions, '$[1][0].value') as primary_condition_value,
  json_extract(p.conditions, '$[1][0].match_type') as primary_match_type,
  
  -- Secondary condition (if exists)
  CASE 
    WHEN json_array_length(json_extract(p.conditions, '$[1]')) > 1
    THEN json_extract(p.conditions, '$[1][1].type')
    ELSE NULL
  END as secondary_condition_type,
  
  CASE 
    WHEN json_array_length(json_extract(p.conditions, '$[1]')) > 1
    THEN json_extract(p.conditions, '$[1][1].value')
    ELSE NULL
  END as secondary_condition_value,
  
  -- URL pattern extraction (most common use case)
  CASE 
    WHEN json_extract(p.conditions, '$[1][0].type') = 'url' 
    THEN json_extract(p.conditions, '$[1][0].value')
    WHEN json_extract(p.conditions, '$[1][1].type') = 'url' 
    THEN json_extract(p.conditions, '$[1][1].value')
    ELSE NULL
  END as target_url,
  
  -- Domain extraction from URL
  CASE 
    WHEN json_extract(p.conditions, '$[1][0].type') = 'url' 
    THEN 
      CASE 
        WHEN json_extract(p.conditions, '$[1][0].value') LIKE 'http%'
        THEN substr(
          json_extract(p.conditions, '$[1][0].value'),
          instr(json_extract(p.conditions, '$[1][0].value'), '://') + 3,
          CASE 
            WHEN instr(substr(json_extract(p.conditions, '$[1][0].value'), instr(json_extract(p.conditions, '$[1][0].value'), '://') + 3), '/') > 0
            THEN instr(substr(json_extract(p.conditions, '$[1][0].value'), instr(json_extract(p.conditions, '$[1][0].value'), '://') + 3), '/') - 1
            ELSE length(json_extract(p.conditions, '$[1][0].value'))
          END
        )
        ELSE json_extract(p.conditions, '$[1][0].value')
      END
    ELSE NULL
  END as target_domain,
  
  -- Targeting type flags
  CASE WHEN p.conditions LIKE '%"type": "url"%' THEN 1 ELSE 0 END as has_url_targeting,
  CASE WHEN p.conditions LIKE '%"type": "cookie"%' THEN 1 ELSE 0 END as has_cookie_targeting,
  CASE WHEN p.conditions LIKE '%"type": "query"%' THEN 1 ELSE 0 END as has_query_targeting,
  CASE WHEN p.conditions LIKE '%"type": "referrer"%' THEN 1 ELSE 0 END as has_referrer_targeting,
  CASE WHEN p.conditions LIKE '%"type": "event"%' THEN 1 ELSE 0 END as has_event_targeting,
  
  -- Match type categorization
  CASE 
    WHEN p.conditions LIKE '%"match_type": "simple"%' THEN 'simple'
    WHEN p.conditions LIKE '%"match_type": "exact"%' THEN 'exact'
    WHEN p.conditions LIKE '%"match_type": "regex"%' THEN 'regex'
    WHEN p.conditions LIKE '%"match_type": "substring"%' THEN 'substring'
    ELSE 'unknown'
  END as primary_match_type_category,
  
  -- Usage statistics (pre-computed for performance)
  (
    SELECT COUNT(DISTINCT e.id)
    FROM experiments e
    WHERE e.project_id = p.project_id
      AND json_extract(e.data_json, '$.page_ids') LIKE '%' || p.id || '%'
  ) as total_experiment_count,
  
  (
    SELECT COUNT(DISTINCT e.id)
    FROM experiments e
    WHERE e.project_id = p.project_id
      AND e.status = 'running'
      AND json_extract(e.data_json, '$.page_ids') LIKE '%' || p.id || '%'
  ) as active_experiment_count,
  
  (
    SELECT GROUP_CONCAT(e.name, ', ')
    FROM experiments e
    WHERE e.project_id = p.project_id
      AND e.status = 'running'
      AND json_extract(e.data_json, '$.page_ids') LIKE '%' || p.id || '%'
    LIMIT 5
  ) as active_experiment_names,
  
  -- Orphan detection
  CASE 
    WHEN NOT EXISTS (
      SELECT 1 
      FROM experiments e 
      WHERE e.project_id = p.project_id
        AND json_extract(e.data_json, '$.page_ids') LIKE '%' || p.id || '%'
    ) THEN 1 
    ELSE 0 
  END as is_orphaned,
  
  -- Age calculations
  CAST((julianday('now') - julianday(p.created_time)) AS INTEGER) as days_since_created,
  CAST((julianday('now') - julianday(p.updated_time)) AS INTEGER) as days_since_updated,
  
  -- Status categorization
  CASE 
    WHEN p.archived = 1 THEN 'archived'
    WHEN NOT EXISTS (
      SELECT 1 FROM experiments e 
      WHERE json_extract(e.data_json, '$.page_ids') LIKE '%' || p.id || '%'
    ) THEN 'unused'
    WHEN EXISTS (
      SELECT 1 FROM experiments e 
      WHERE e.status = 'running' 
        AND json_extract(e.data_json, '$.page_ids') LIKE '%' || p.id || '%'
    ) THEN 'active'
    ELSE 'inactive'
  END as usage_status

FROM pages p
LEFT JOIN projects pr ON p.project_id = pr.id;
