-- flag_variables_summary: Compact Variable Summary View
-- This view provides a summary of variable usage at the flag level
-- without the full normalization, useful for quick overviews

CREATE VIEW IF NOT EXISTS flag_variables_summary AS
SELECT 
  -- Project Information
  f.project_id,
  p.name as project_name,
  
  -- Flag Information
  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,
  
  -- Variable Definition Summary
  CASE 
    WHEN json_extract(f.data_json, '$.variable_definitions') IS NOT NULL
      AND json_extract(f.data_json, '$.variable_definitions') != '{}'
    THEN (
      SELECT COUNT(*)
      FROM json_each(json_extract(f.data_json, '$.variable_definitions'))
    )
    ELSE 0
  END as variable_count,
  
  -- Variable Keys (as JSON array)
  CASE 
    WHEN json_extract(f.data_json, '$.variable_definitions') IS NOT NULL
      AND json_extract(f.data_json, '$.variable_definitions') != '{}'
    THEN (
      SELECT json_group_array(key)
      FROM json_each(json_extract(f.data_json, '$.variable_definitions'))
    )
    ELSE '[]'
  END as variable_keys,
  
  -- Variable Types Summary (as JSON object)
  CASE 
    WHEN json_extract(f.data_json, '$.variable_definitions') IS NOT NULL
      AND json_extract(f.data_json, '$.variable_definitions') != '{}'
    THEN (
      SELECT json_group_object(
        key, 
        json_extract(value, '$.type')
      )
      FROM json_each(json_extract(f.data_json, '$.variable_definitions'))
    )
    ELSE '{}'
  END as variable_types,
  
  -- Environment Summary (where flag is enabled)
  (
    SELECT json_group_array(fe.environment_key)
    FROM flag_environments fe
    WHERE fe.project_id = f.project_id 
      AND fe.flag_key = f.key
      AND fe.enabled = 1
  ) as enabled_environments,
  
  -- Rule Summary with Variations
  (
    SELECT json_group_object(
      r.environment_key || ':' || r.key,
      json_object(
        'rule_type', r.type,
        'enabled', r.enabled,
        'variation_count', 
        CASE 
          WHEN r.variations IS NOT NULL AND r.variations != '{}' 
          THEN (SELECT COUNT(*) FROM json_each(r.variations))
          ELSE 0
        END,
        'has_custom_variables',
        CASE 
          WHEN EXISTS (
            SELECT 1 
            FROM json_each(r.variations) v
            WHERE json_extract(v.value, '$.variables') IS NOT NULL
              AND json_extract(v.value, '$.variables') != '{}'
          ) THEN 1
          ELSE 0
        END
      )
    )
    FROM rules r
    WHERE r.project_id = f.project_id
      AND r.flag_key = f.key
  ) as rules_summary,
  
  -- Custom Variable Usage Count
  (
    SELECT COUNT(DISTINCT r.environment_key || ':' || var.key)
    FROM rules r
    CROSS JOIN json_each(r.variations) as var
    WHERE r.project_id = f.project_id
      AND r.flag_key = f.key
      AND json_extract(var.value, '$.variables') IS NOT NULL
      AND json_extract(var.value, '$.variables') != '{}'
  ) as variations_with_custom_variables,
  
  -- Timestamps
  f.created_time,
  f.updated_time,
  
  -- Days Since Creation
  CAST((julianday('now') - julianday(f.created_time)) AS INTEGER) as days_since_creation,
  
  -- Raw variable definitions for detailed queries
  json_extract(f.data_json, '$.variable_definitions') as variable_definitions_json

FROM flags f
INNER JOIN projects p ON f.project_id = p.id
WHERE p.is_flags_enabled = 1;

-- Example Queries:
-- 1. Find all flags with variables:
-- SELECT * FROM flag_variables_summary WHERE variable_count > 0 ORDER BY variable_count DESC;

-- 2. Find flags using a specific variable type:
-- SELECT * FROM flag_variables_summary WHERE variable_types LIKE '%"string"%';

-- 3. Find flags with custom variable values in production:
-- SELECT * FROM flag_variables_summary 
-- WHERE variations_with_custom_variables > 0 
--   AND enabled_environments LIKE '%"production"%';

-- 4. Get variable statistics:
-- SELECT 
--   COUNT(*) as total_flags,
--   COUNT(CASE WHEN variable_count > 0 THEN 1 END) as flags_with_variables,
--   SUM(variable_count) as total_variables_defined,
--   SUM(variations_with_custom_variables) as total_custom_values
-- FROM flag_variables_summary;

-- 5. Most common variable keys:
-- SELECT 
--   json_extract(value, '$') as variable_key,
--   COUNT(*) as usage_count
-- FROM flag_variables_summary, json_each(variable_keys)
-- GROUP BY variable_key
-- ORDER BY usage_count DESC;