-- flag_variation_variables: Normalized Variable Data View
-- This view creates one row per variable value (flag × variation × variable)
-- making it easy to query variable values across flags and variations

CREATE VIEW IF NOT EXISTS flag_variation_variables 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,
  
  -- Environment Information
  r.environment_key,
  e.name as environment_name,
  
  -- Rule Information
  r.id as rule_id,
  r.key as rule_key,
  r.type as rule_type,
  r.name as rule_name,
  
  -- Variation Information (extracted from JSON)
  json_extract(var.value, '$.key') as variation_key,
  json_extract(var.value, '$.id') as variation_id,
  json_extract(var.value, '$.name') as variation_name,
  CAST(json_extract(var.value, '$.percentage_included') AS INTEGER) as variation_percentage,
  
  -- Variable Information (from variable_definitions and values)
  vardef.key as variable_key,
  json_extract(vardef.value, '$.type') as variable_type,
  json_extract(vardef.value, '$.default_value') as default_value,
  json_extract(vardef.value, '$.description') as variable_description,
  
  -- Variable Value (from variation variables or default)
  COALESCE(
    json_extract(var.value, '$.variables.' || vardef.key),
    json_extract(vardef.value, '$.default_value')
  ) as variable_value,
  
  -- Metadata
  CASE 
    WHEN json_extract(var.value, '$.variables.' || vardef.key) IS NOT NULL THEN 'custom'
    ELSE 'default'
  END as value_source,
  
  -- Rule Status
  r.enabled as rule_enabled,
  fe.enabled as flag_enabled,
  CASE 
    WHEN fe.enabled = 1 AND r.enabled = 1 THEN 'active'
    ELSE 'inactive'
  END as effective_status,
  
  -- Timestamps
  f.created_time as flag_created_time,
  f.updated_time as flag_updated_time,
  r.created_time as rule_created_time,
  r.updated_time as rule_updated_time,
  
  -- Raw JSON for complex queries
  var.value as variation_json,
  vardef.value as variable_definition_json

FROM flags f
INNER JOIN projects p ON f.project_id = p.id
INNER 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
INNER JOIN rules r ON fe.project_id = r.project_id 
  AND fe.flag_key = r.flag_key 
  AND fe.environment_key = r.environment_key
-- Extract variations from rules.variations JSON object
CROSS JOIN json_each(r.variations) as var
-- Extract variable definitions from flags.data_json
CROSS JOIN json_each(
  CASE 
    WHEN json_extract(f.data_json, '$.variable_definitions') IS NOT NULL
    THEN json_extract(f.data_json, '$.variable_definitions')
    ELSE '{}'
  END
) as vardef
WHERE p.is_flags_enabled = 1
  AND r.variations IS NOT NULL 
  AND r.variations != '[]' 
  AND r.variations != '{}';

-- Example Queries:
-- 1. Get all variable values for a specific flag:
-- SELECT * FROM flag_variation_variables WHERE flag_key = 'my-flag' ORDER BY variation_key, variable_key;

-- 2. Find all flags using a specific variable:
-- SELECT DISTINCT flag_key, flag_name FROM flag_variation_variables WHERE variable_key = 'cdnVariationSettings';

-- 3. Get all custom (non-default) variable values:
-- SELECT * FROM flag_variation_variables WHERE value_source = 'custom';

-- 4. Find variations with specific variable values:
-- SELECT * FROM flag_variation_variables WHERE variable_key = 'promo_code' AND variable_value = '"SUMMER2024"';

-- 5. Summary of variable usage:
-- SELECT variable_key, variable_type, COUNT(DISTINCT flag_key) as flag_count, 
--        COUNT(DISTINCT CASE WHEN value_source = 'custom' THEN flag_key || variation_key END) as custom_value_count
-- FROM flag_variation_variables 
-- GROUP BY variable_key, variable_type 
-- ORDER BY flag_count DESC;