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

CREATE VIEW flag_variations_flat AS
SELECT 
  -- Flag base data
  f.project_id,
  pr.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,
  
  -- Variable definitions from flag
  f.data_json as flag_data_json,
  json_extract(f.data_json, '$.variable_definitions') as variable_definitions_raw,
  json_array_length(json_extract(f.data_json, '$.variable_definitions')) as variable_count,
  
  -- Variation data (fully denormalized)
  v.id as variation_id,
  v.key as variation_key,
  v.name as variation_name,
  v.description as variation_description,
  v.enabled as variation_enabled,
  v.archived as variation_archived,
  v.created_time as variation_created_time,
  v.updated_time as variation_updated_time,
  
  -- Variable values (parsed from JSON)
  v.variables as variables_raw,
  
  -- Common variable extractions
  json_extract(v.variables, '$.color') as variable_color,
  json_extract(v.variables, '$.text') as variable_text,
  json_extract(v.variables, '$.number') as variable_number,
  json_extract(v.variables, '$.boolean') as variable_boolean,
  json_extract(v.variables, '$.string') as variable_string,
  json_extract(v.variables, '$.json') as variable_json,
  json_extract(v.variables, '$.integer') as variable_integer,
  json_extract(v.variables, '$.double') as variable_double,
  
  -- Variable type analysis
  CASE 
    WHEN v.variables LIKE '%"color":%' THEN 1 ELSE 0 
  END as has_color_variable,
  
  CASE 
    WHEN v.variables LIKE '%"text":%' THEN 1 ELSE 0 
  END as has_text_variable,
  
  CASE 
    WHEN v.variables LIKE '%"number":%' OR v.variables LIKE '%"integer":%' OR v.variables LIKE '%"double":%' THEN 1 
    ELSE 0 
  END as has_numeric_variable,
  
  CASE 
    WHEN v.variables LIKE '%"boolean":%' THEN 1 ELSE 0 
  END as has_boolean_variable,
  
  CASE 
    WHEN v.variables LIKE '%"json":%' THEN 1 ELSE 0 
  END as has_json_variable,
  
  -- Count of variables in this variation
  CASE 
    WHEN v.variables IS NOT NULL AND v.variables != '{}' 
    THEN (
      SELECT COUNT(*)
      FROM json_each(v.variables)
    )
    ELSE 0
  END as variation_variable_count,
  
  -- Usage in rules (A/B tests, rollouts, etc.)
  (
    SELECT COUNT(DISTINCT r.id)
    FROM rules r
    WHERE r.project_id = f.project_id
      AND r.flag_key = f.key
      AND r.variations LIKE '%"' || v.key || '"%'
  ) as rule_usage_count,
  
  (
    SELECT COUNT(DISTINCT r.id)
    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 = f.project_id
      AND r.flag_key = f.key
      AND r.variations LIKE '%"' || v.key || '"%'
      AND r.enabled = 1
      AND fe.enabled = 1
  ) as active_rule_usage_count,
  
  -- Traffic allocation (from rules)
  (
    SELECT AVG(
      CAST(json_extract(rv.value, '$.weight') AS REAL)
    )
    FROM rules r
    CROSS JOIN json_each(r.variations) rv
    WHERE r.project_id = f.project_id
      AND r.flag_key = f.key
      AND json_extract(rv.value, '$.variation_key') = v.key
  ) as average_traffic_allocation,
  
  -- Variation status
  CASE 
    WHEN v.archived = 1 THEN 'archived'
    WHEN v.enabled = 0 THEN 'disabled'
    WHEN 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.project_id = f.project_id
        AND r.flag_key = f.key
        AND r.variations LIKE '%"' || v.key || '"%'
        AND r.enabled = 1
        AND fe.enabled = 1
    ) THEN 'active'
    ELSE 'inactive'
  END as variation_status,
  
  -- Control variation detection
  CASE 
    WHEN LOWER(v.name) LIKE '%control%' OR LOWER(v.key) LIKE '%control%' 
      OR LOWER(v.name) LIKE '%baseline%' OR LOWER(v.key) LIKE '%baseline%'
      OR LOWER(v.name) = 'off' OR LOWER(v.key) = 'off'
    THEN 1 
    ELSE 0 
  END as is_control_variation,
  
  -- Age calculations
  CAST((julianday('now') - julianday(v.created_time)) AS INTEGER) as days_since_created,
  CAST((julianday('now') - julianday(v.updated_time)) AS INTEGER) as days_since_updated,
  
  -- Relationship metadata
  'flag_variation' as relationship_type,
  (SELECT COUNT(*) FROM variations v2 WHERE v2.project_id = f.project_id AND v2.flag_key = f.key) as total_variations_in_flag,
  CURRENT_TIMESTAMP as view_updated_at

FROM flags f
INNER JOIN variations v ON v.project_id = f.project_id 
  AND v.flag_key = f.key
LEFT JOIN projects pr ON f.project_id = pr.id
WHERE v.id IS NOT NULL
  AND f.archived = 0;
