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

CREATE VIEW flags_unified_view AS
SELECT 
  -- Flag Core Information
  f.project_id,
  p.name as project_name,
  f.key as flag_key,
  f.id as flag_id,
  f.name as flag_name,
  f.description as flag_description,
  f.archived as flag_archived,
  f.created_time as flag_created_time,
  f.updated_time as flag_updated_time,
  
  -- Environment Configuration (denormalized)
  fe.environment_key,
  e.name as environment_name,
  e.is_primary as environment_is_primary,
  fe.enabled as flag_enabled,
  fe.enabled as enabled, -- Simplified alias for natural queries
  fe.archived as environment_archived,
  
  -- Rule Information (denormalized from rules table)
  r.id as rule_id,
  r.key as rule_key,
  r.type as rule_type,
  r.name as rule_name,
  CASE 
    WHEN r.type = 'a/b' THEN 'a/b_test'
    WHEN r.type = 'rollout' THEN 'percentage_rollout'
    WHEN r.type = 'targeted_delivery' THEN 'targeted'
    ELSE r.type
  END as rule_category,
  
  -- Rollout Information
  COALESCE(r.percentage_included, 0) as percentage_included,
  COALESCE(r.percentage_included / 100.0, 0) as rollout_percentage,
  
  -- Variation Counts (pre-calculated from JSON)
  COALESCE(
    CASE 
      WHEN r.variations IS NOT NULL 
        AND r.variations != '[]' 
        AND r.variations != '{}'
      THEN (
        -- Count keys in the variations object
        SELECT COUNT(*)
        FROM json_each(r.variations)
      )
      ELSE 0
    END, 0
  ) as variation_count,
  
  -- Audience Targeting
  CASE 
    WHEN r.audience_conditions IS NOT NULL 
      AND r.audience_conditions != '[]'
      AND r.audience_conditions != ''
    THEN 1 
    ELSE 0 
  END as has_audience_targeting,
  
  -- Store raw JSON for complex queries
  r.audience_conditions as audience_conditions_json,
  
  -- Metrics Tracking
  CASE 
    WHEN r.metrics IS NOT NULL 
      AND r.metrics != '[]'
      AND r.metrics != ''
    THEN json_array_length(r.metrics)
    ELSE 0
  END as metric_count,
  
  -- Rule Status
  r.enabled as rule_enabled,
  COALESCE(r.created_time, f.created_time) as rule_created_time,
  COALESCE(r.updated_time, f.updated_time) as rule_updated_time,
  
  -- Computed Fields for Common Queries
  CASE 
    WHEN fe.enabled = 1 AND r.enabled = 1 THEN 'active'
    WHEN fe.enabled = 1 AND r.enabled = 0 THEN 'rule_disabled'
    WHEN fe.enabled = 0 THEN 'flag_disabled'
    ELSE 'inactive'
  END as effective_status,
  
  -- Days Since Creation
  CAST((julianday('now') - julianday(f.created_time)) AS INTEGER) as days_since_creation,
  
  -- Days Since Last Update
  CAST((julianday('now') - julianday(f.updated_time)) AS INTEGER) as days_since_update,
  
  -- Flag Usage Category
  CASE
    WHEN fe.enabled = 0 THEN 'disabled'
    WHEN r.type = 'a/b' THEN 'testing'
    WHEN r.percentage_included >= 10000 THEN 'fully_rolled_out'
    WHEN r.percentage_included > 0 THEN 'rolling_out'
    ELSE 'configured'
  END as usage_category,
  
  -- Simplified flag status for natural language queries
  CASE
    WHEN fe.enabled = 1 THEN 'enabled'
    ELSE 'disabled'
  END as simple_status

FROM flags f
INNER JOIN projects p ON f.project_id = p.id
LEFT JOIN flag_environments fe ON f.project_id = fe.project_id AND f.key = fe.flag_key
LEFT JOIN environments e ON fe.project_id = e.project_id AND fe.environment_key = e.key
LEFT JOIN rules r ON fe.project_id = r.project_id 
  AND fe.flag_key = r.flag_key 
  AND fe.environment_key = r.environment_key
WHERE p.is_flags_enabled = 1;
