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

CREATE VIEW audiences_standalone_flat AS
SELECT 
  -- Audience base data
  a.id as audience_id,
  a.project_id,
  pr.name as project_name,
  a.name as audience_name,
  a.description as audience_description,
  a.archived,
  a.created_time,
  a.last_modified,
  
  -- Raw conditions (for complex queries)
  a.conditions as conditions_raw,
  
  -- Basic condition structure (safe JSON handling)
  CASE 
    WHEN json_valid(a.conditions) = 1 THEN json_array_length(a.conditions)
    ELSE 0
  END as condition_depth,
  
  CASE 
    WHEN json_valid(a.conditions) = 1 THEN json_extract(a.conditions, '$[0]')
    ELSE NULL
  END as root_operator, -- 'and', 'or', 'not'
  
  -- Condition type detection (audiences can have multiple condition types)
  CASE 
    WHEN a.conditions LIKE '%"type": "language"%' THEN 1 ELSE 0 
  END as has_language_targeting,
  
  CASE 
    WHEN a.conditions LIKE '%"type": "device"%' THEN 1 ELSE 0 
  END as has_device_targeting,
  
  CASE 
    WHEN a.conditions LIKE '%"type": "browser"%' THEN 1 ELSE 0 
  END as has_browser_targeting,
  
  CASE 
    WHEN a.conditions LIKE '%"type": "location"%' THEN 1 ELSE 0 
  END as has_location_targeting,
  
  CASE 
    WHEN a.conditions LIKE '%"type": "custom_attribute"%' THEN 1 ELSE 0 
  END as has_custom_attribute_targeting,
  
  CASE 
    WHEN a.conditions LIKE '%"type": "cookie"%' THEN 1 ELSE 0 
  END as has_cookie_targeting,
  
  CASE 
    WHEN a.conditions LIKE '%"type": "query"%' THEN 1 ELSE 0 
  END as has_query_targeting,
  
  CASE 
    WHEN a.conditions LIKE '%"type": "referrer"%' THEN 1 ELSE 0 
  END has_referrer_targeting,
  
  -- Primary targeting type (most prominent)
  CASE 
    WHEN a.conditions LIKE '%"type": "language"%' THEN 'language'
    WHEN a.conditions LIKE '%"type": "device"%' THEN 'device'
    WHEN a.conditions LIKE '%"type": "browser"%' THEN 'browser'
    WHEN a.conditions LIKE '%"type": "location"%' THEN 'location'
    WHEN a.conditions LIKE '%"type": "custom_attribute"%' THEN 'custom_attribute'
    WHEN a.conditions LIKE '%"type": "cookie"%' THEN 'cookie'
    WHEN a.conditions LIKE '%"type": "query"%' THEN 'query'
    WHEN a.conditions LIKE '%"type": "referrer"%' THEN 'referrer'
    ELSE 'other'
  END as primary_targeting_type,
  
  -- Extract specific values for common targeting types (safe JSON handling)
  -- Language extraction (NO json_tree!)
  NULL as language_value,
  
  -- Device extraction (NO json_tree!)
  NULL as device_value,
  
  -- Browser extraction (NO json_tree!)
  NULL as browser_value,
  
  -- Custom attribute name extraction (regex-based to avoid json_tree malformed JSON errors)
  CASE 
    WHEN a.conditions LIKE '%"type": "custom_attribute"%' AND INSTR(a.conditions, '"name": "') > 0
    THEN SUBSTR(
      a.conditions, 
      INSTR(a.conditions, '"name": "') + 9, 
      INSTR(SUBSTR(a.conditions, INSTR(a.conditions, '"name": "') + 9), '"') - 1
    )
    ELSE NULL
  END as custom_attribute_name,
  
  -- Count of conditions (SIMPLE STRING-BASED COUNTING, NO json_tree!)
  CASE
    WHEN json_valid(a.conditions) = 1 THEN 
      -- Count occurrences of "type": pattern in the JSON string
      (LENGTH(a.conditions) - LENGTH(REPLACE(a.conditions, '"type":', ''))) / LENGTH('"type":')
    ELSE 0
  END as total_condition_count,
  
  -- Usage statistics for experiments (safe JSON handling)
  (
    SELECT COUNT(DISTINCT e.id)
    FROM experiments e
    WHERE e.project_id = a.project_id
      AND CASE 
        WHEN json_valid(e.data_json) = 1 
        THEN json_extract(e.data_json, '$.audience_ids') LIKE '%' || a.id || '%'
        ELSE 0
      END
  ) as experiment_usage_count,
  
  (
    SELECT COUNT(DISTINCT e.id)
    FROM experiments e
    WHERE e.project_id = a.project_id
      AND e.status = 'running'
      AND CASE 
        WHEN json_valid(e.data_json) = 1 
        THEN json_extract(e.data_json, '$.audience_ids') LIKE '%' || a.id || '%'
        ELSE 0
      END
  ) as active_experiment_count,
  
  -- Usage statistics for flags/rules
  (
    SELECT COUNT(DISTINCT r.flag_key)
    FROM rules r
    WHERE r.project_id = a.project_id
      AND r.audience_conditions LIKE '%' || a.id || '%'
  ) as flag_usage_count,
  
  (
    SELECT COUNT(DISTINCT r.flag_key)
    FROM rules r
    JOIN flag_environments fe ON r.project_id = fe.project_id 
      AND r.flag_key = fe.flag_key 
      AND r.environment_key = fe.environment_key
    WHERE r.project_id = a.project_id
      AND r.audience_conditions LIKE '%' || a.id || '%'
      AND fe.enabled = 1
      AND r.enabled = 1
  ) as active_flag_count,
  
  -- Total usage across all entity types
  (
    SELECT COUNT(DISTINCT e.id)
    FROM experiments e
    WHERE e.project_id = a.project_id
      AND CASE 
        WHEN json_valid(e.data_json) = 1 
        THEN json_extract(e.data_json, '$.audience_ids') LIKE '%' || a.id || '%'
        ELSE 0
      END
  ) + (
    SELECT COUNT(DISTINCT r.flag_key)
    FROM rules r
    WHERE r.project_id = a.project_id
      AND r.audience_conditions LIKE '%' || a.id || '%'
  ) as total_usage_count,
  
  -- List of experiments using this audience (limited) - safe JSON handling
  (
    SELECT GROUP_CONCAT(name, ', ')
    FROM (
      SELECT DISTINCT e.name as name
      FROM experiments e
      WHERE e.project_id = a.project_id
        AND CASE 
          WHEN json_valid(e.data_json) = 1 
          THEN json_extract(e.data_json, '$.audience_ids') LIKE '%' || a.id || '%'
          ELSE 0
        END
      ORDER BY e.status DESC, e.name
      LIMIT 3
    )
  ) as sample_experiment_names,
  
  -- List of flags using this audience (limited)
  (
    SELECT GROUP_CONCAT(name, ', ')
    FROM (
      SELECT DISTINCT f.name as name
      FROM rules r
      JOIN flags f ON r.project_id = f.project_id AND r.flag_key = f.key
      WHERE r.project_id = a.project_id
        AND r.audience_conditions LIKE '%' || a.id || '%'
      ORDER BY f.name
      LIMIT 3
    )
  ) as sample_flag_names,
  
  -- Orphan detection - safe JSON handling
  CASE 
    WHEN NOT EXISTS (
      SELECT 1 
      FROM experiments e 
      WHERE e.project_id = a.project_id
        AND CASE 
          WHEN json_valid(e.data_json) = 1 
          THEN json_extract(e.data_json, '$.audience_ids') LIKE '%' || a.id || '%'
          ELSE 0
        END
    ) 
    AND NOT EXISTS (
      SELECT 1 
      FROM rules r 
      WHERE r.project_id = a.project_id
        AND r.audience_conditions LIKE '%' || a.id || '%'
    ) THEN 1 
    ELSE 0 
  END as is_orphaned,
  
  -- Age calculations
  CAST((julianday('now') - julianday(a.created_time)) AS INTEGER) as days_since_created,
  CAST((julianday('now') - julianday(a.last_modified)) AS INTEGER) as days_since_modified,
  
  -- Complexity score (based on condition depth and count) - safe JSON handling
  CASE 
    WHEN json_valid(a.conditions) = 1 THEN
      CASE
        WHEN json_array_length(a.conditions) <= 2 THEN 'simple'
        WHEN json_array_length(a.conditions) <= 4 THEN 'moderate'
        ELSE 'complex'
      END
    ELSE 'unknown'
  END as complexity_level,
  
  -- Usage categorization - safe JSON handling
  CASE 
    WHEN a.archived = 1 THEN 'archived'
    WHEN NOT EXISTS (
      SELECT 1 FROM experiments e 
      WHERE CASE 
        WHEN json_valid(e.data_json) = 1 
        THEN json_extract(e.data_json, '$.audience_ids') LIKE '%' || a.id || '%'
        ELSE 0
      END
    ) AND NOT EXISTS (
      SELECT 1 FROM rules r 
      WHERE r.audience_conditions LIKE '%' || a.id || '%'
    ) THEN 'unused'
    WHEN EXISTS (
      SELECT 1 FROM experiments e 
      WHERE e.status = 'running' 
        AND CASE 
          WHEN json_valid(e.data_json) = 1 
          THEN json_extract(e.data_json, '$.audience_ids') LIKE '%' || a.id || '%'
          ELSE 0
        END
    ) OR EXISTS (
      SELECT 1 FROM rules r
      JOIN flag_environments fe ON r.project_id = fe.project_id 
        AND r.flag_key = fe.flag_key 
        AND r.environment_key = fe.environment_key
      WHERE r.audience_conditions LIKE '%' || a.id || '%'
        AND fe.enabled = 1
        AND r.enabled = 1
    ) THEN 'active'
    ELSE 'inactive'
  END as usage_status

FROM audiences a
LEFT JOIN projects pr ON a.project_id = pr.id;
