-- Auto-extracted view: entity_usage_view
-- Extracted at: 2025-07-08T19:54:36.038Z
-- Status: Known (has source file)
-- Original source: src/analytics/views/

CREATE VIEW entity_usage_view AS
-- Page usage analysis
SELECT 
  'page' as entity_type,
  p.id as entity_id,
  p.key as entity_key,
  p.name as entity_name,
  p.project_id,
  pr.name as project_name,
  
  -- Count experiments using this page
  (
    SELECT COUNT(DISTINCT e.id)
    FROM experiments e
    WHERE e.project_id = p.project_id
      AND (
        -- Check if page is in page_ids array
        json_extract(e.data_json, '$.page_ids') LIKE '%' || p.id || '%'
        -- Or in URL targeting
        OR json_extract(e.data_json, '$.url_targeting.page_id') = p.id
      )
  ) as usage_count,
  
  -- Count active experiments using this page
  (
    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 || '%'
        OR json_extract(e.data_json, '$.url_targeting.page_id') = p.id
      )
  ) as active_usage_count,
  
  -- Usage categorization
  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 || '%'
             OR json_extract(e.data_json, '$.url_targeting.page_id') = p.id)
    ) THEN 'orphaned'
    WHEN NOT EXISTS (
      SELECT 1 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 || '%'
             OR json_extract(e.data_json, '$.url_targeting.page_id') = p.id)
    ) THEN 'inactive_only'
    ELSE 'in_use'
  END as usage_status,
  
  -- Additional metadata
  p.activation_type,
  COALESCE(json_array_length(json_extract(p.data_json, '$.conditions')), 0) as condition_count,
  p.created_time,
  p.updated_time,
  CAST((julianday('now') - julianday(p.created_time)) AS INTEGER) as days_since_creation,
  p.archived

FROM pages p
INNER JOIN projects pr ON p.project_id = pr.id
WHERE p.archived = 0

UNION ALL

-- Audience usage analysis
SELECT 
  'audience' as entity_type,
  a.id as entity_id,
  a.id as entity_key,  -- audiences don't have separate keys
  a.name as entity_name,
  a.project_id,
  pr.name as project_name,
  
  -- Count usage in experiments (Web platform)
  CASE 
    WHEN pr.is_flags_enabled = 0 THEN (
      SELECT COUNT(DISTINCT e.id)
      FROM experiments e
      WHERE e.project_id = a.project_id
        AND json_extract(e.data_json, '$.audience_ids') LIKE '%' || a.id || '%'
    )
    -- Count usage in rules (Feature platform)
    ELSE (
      SELECT COUNT(DISTINCT r.flag_key || '-' || r.environment_key)
      FROM rules r
      WHERE r.project_id = a.project_id
        AND r.audience_conditions LIKE '%' || a.id || '%'
    )
  END as usage_count,
  
  -- Count active usage
  CASE 
    WHEN pr.is_flags_enabled = 0 THEN (
      SELECT COUNT(DISTINCT e.id)
      FROM experiments e
      WHERE e.project_id = a.project_id
        AND e.status = 'running'
        AND json_extract(e.data_json, '$.audience_ids') LIKE '%' || a.id || '%'
    )
    ELSE (
      SELECT COUNT(DISTINCT r.flag_key || '-' || r.environment_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
    )
  END as active_usage_count,
  
  -- Usage categorization
  CASE 
    WHEN pr.is_flags_enabled = 0 AND NOT EXISTS (
      SELECT 1 FROM experiments e
      WHERE e.project_id = a.project_id
        AND json_extract(e.data_json, '$.audience_ids') LIKE '%' || a.id || '%'
    ) THEN 'orphaned'
    WHEN pr.is_flags_enabled = 1 AND NOT EXISTS (
      SELECT 1 FROM rules r
      WHERE r.project_id = a.project_id
        AND r.audience_conditions LIKE '%' || a.id || '%'
    ) THEN 'orphaned'
    ELSE 'in_use'
  END as usage_status,
  
  -- Additional metadata
  NULL as activation_type,
  json_array_length(a.conditions) as condition_count,
  a.created_time,
  a.last_modified as updated_time,
  CAST((julianday('now') - julianday(a.created_time)) AS INTEGER) as days_since_creation,
  a.archived

FROM audiences a
INNER JOIN projects pr ON a.project_id = pr.id
WHERE a.archived = 0

UNION ALL

-- Event usage analysis
SELECT 
  'event' as entity_type,
  ev.id as entity_id,
  ev.key as entity_key,
  ev.name as entity_name,
  ev.project_id,
  pr.name as project_name,
  
  -- Count usage across experiments and rules
  (
    SELECT COUNT(DISTINCT e.id)
    FROM experiments e
    WHERE e.project_id = ev.project_id
      AND json_extract(e.data_json, '$.metrics') LIKE '%' || ev.id || '%'
  ) + (
    SELECT COUNT(DISTINCT r.flag_key || '-' || r.environment_key)
    FROM rules r
    WHERE r.project_id = ev.project_id
      AND r.metrics LIKE '%' || ev.id || '%'
  ) as usage_count,
  
  -- Count active usage
  (
    SELECT COUNT(DISTINCT e.id)
    FROM experiments e
    WHERE e.project_id = ev.project_id
      AND e.status = 'running'
      AND json_extract(e.data_json, '$.metrics') LIKE '%' || ev.id || '%'
  ) + (
    SELECT COUNT(DISTINCT r.flag_key || '-' || r.environment_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 = ev.project_id
      AND r.metrics LIKE '%' || ev.id || '%'
      AND fe.enabled = 1 AND r.enabled = 1
  ) as active_usage_count,
  
  CASE 
    WHEN NOT EXISTS (
      SELECT 1 FROM experiments e
      WHERE e.project_id = ev.project_id
        AND json_extract(e.data_json, '$.metrics') LIKE '%' || ev.id || '%'
    ) AND NOT EXISTS (
      SELECT 1 FROM rules r
      WHERE r.project_id = ev.project_id
        AND r.metrics LIKE '%' || ev.id || '%'
    ) THEN 'orphaned'
    ELSE 'in_use'
  END as usage_status,
  
  -- Additional metadata
  ev.event_type as activation_type,
  0 as condition_count,
  ev.created_time,
  ev.created_time as updated_time,  -- events don't have separate updated_time
  CAST((julianday('now') - julianday(ev.created_time)) AS INTEGER) as days_since_creation,
  ev.archived

FROM events ev
INNER JOIN projects pr ON ev.project_id = pr.id
WHERE ev.archived = 0

UNION ALL

-- Attribute usage analysis
SELECT 
  'attribute' as entity_type,
  attr.id as entity_id,
  attr.key as entity_key,
  attr.name as entity_name,
  attr.project_id,
  pr.name as project_name,
  
  -- Count usage in audience conditions
  (
    SELECT COUNT(DISTINCT a.id)
    FROM audiences a
    WHERE a.project_id = attr.project_id
      AND a.conditions LIKE '%' || attr.key || '%'
  ) as usage_count,
  
  -- For attributes, active usage is same as total usage
  (
    SELECT COUNT(DISTINCT a.id)
    FROM audiences a
    WHERE a.project_id = attr.project_id
      AND a.conditions LIKE '%' || attr.key || '%'
  ) as active_usage_count,
  
  CASE 
    WHEN NOT EXISTS (
      SELECT 1 FROM audiences a
      WHERE a.project_id = attr.project_id
        AND a.conditions LIKE '%' || attr.key || '%'
    ) THEN 'orphaned'
    ELSE 'in_use'
  END as usage_status,
  
  -- Additional metadata
  attr.condition_type as activation_type,
  0 as condition_count,
  attr.last_modified as created_time,
  attr.last_modified as updated_time,
  CAST((julianday('now') - julianday(attr.last_modified)) AS INTEGER) as days_since_creation,
  attr.archived

FROM attributes attr
INNER JOIN projects pr ON attr.project_id = pr.id
WHERE attr.archived = 0;
