-- Flattened view of all code snippets in experiments for security analysis and searching
-- Created: 2025-07-19
-- Purpose: Extract every piece of JavaScript/CSS/HTML code into individual searchable rows

CREATE VIEW IF NOT EXISTS experiment_code_snippets_flat AS

-- Shared Code (Experiment-level)
SELECT 
  'shared' as code_location,
  e.project_id,
  p.name as project_name,
  e.id as experiment_id,
  e.name as experiment_name,
  e.status as experiment_status,
  NULL as variation_id,
  NULL as variation_name,
  NULL as action_id,
  json_extract(change.value, '$.type') as code_type,
  json_extract(change.value, '$.value') as code_content,
  LENGTH(json_extract(change.value, '$.value')) as code_length,
  
  -- Security flags
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%<script%' THEN 1
    ELSE 0
  END as contains_script_tag,
  
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%eval(%' THEN 1
    ELSE 0
  END as contains_eval,
  
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%document.write%' THEN 1
    ELSE 0
  END as contains_document_write,
  
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%innerhtml%' THEN 1
    ELSE 0
  END as contains_innerhtml,
  
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%javascript:%' THEN 1
    ELSE 0
  END as contains_javascript_protocol,
  
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%onerror%' 
      OR LOWER(json_extract(change.value, '$.value')) LIKE '%onload%'
      OR LOWER(json_extract(change.value, '$.value')) LIKE '%onclick%'
    THEN 1
    ELSE 0
  END as contains_event_handlers,
  
  -- External resource flags
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%http://%' 
      OR LOWER(json_extract(change.value, '$.value')) LIKE '%https://%'
    THEN 1
    ELSE 0
  END as contains_external_urls,
  
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%.js%' 
      OR LOWER(json_extract(change.value, '$.value')) LIKE '%.css%'
    THEN 1
    ELSE 0
  END as references_external_files,
  
  -- Code metadata
  json_extract(change.value, '$.async') as is_async,
  json_extract(change.value, '$.selector') as css_selector,
  e.created_time as experiment_created,
  e.updated_time as experiment_updated
  
FROM experiments e
INNER JOIN projects p ON e.project_id = p.id
CROSS JOIN json_each(json_extract(e.data_json, '$.changes')) change
WHERE json_extract(change.value, '$.type') IN ('custom_code', 'custom_css', 'insert_html')
  AND (p.is_flags_enabled = 0 OR p.is_flags_enabled IS NULL)

UNION ALL

-- Variation Code
SELECT 
  'variation' as code_location,
  e.project_id,
  p.name as project_name,
  e.id as experiment_id,
  e.name as experiment_name,
  e.status as experiment_status,
  json_extract(var.value, '$.variation_id') as variation_id,
  json_extract(var.value, '$.name') as variation_name,
  action.key as action_id,
  json_extract(change.value, '$.type') as code_type,
  json_extract(change.value, '$.value') as code_content,
  LENGTH(json_extract(change.value, '$.value')) as code_length,
  
  -- Security flags
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%<script%' THEN 1
    ELSE 0
  END as contains_script_tag,
  
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%eval(%' THEN 1
    ELSE 0
  END as contains_eval,
  
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%document.write%' THEN 1
    ELSE 0
  END as contains_document_write,
  
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%innerhtml%' THEN 1
    ELSE 0
  END as contains_innerhtml,
  
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%javascript:%' THEN 1
    ELSE 0
  END as contains_javascript_protocol,
  
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%onerror%' 
      OR LOWER(json_extract(change.value, '$.value')) LIKE '%onload%'
      OR LOWER(json_extract(change.value, '$.value')) LIKE '%onclick%'
    THEN 1
    ELSE 0
  END as contains_event_handlers,
  
  -- External resource flags
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%http://%' 
      OR LOWER(json_extract(change.value, '$.value')) LIKE '%https://%'
    THEN 1
    ELSE 0
  END as contains_external_urls,
  
  CASE 
    WHEN LOWER(json_extract(change.value, '$.value')) LIKE '%.js%' 
      OR LOWER(json_extract(change.value, '$.value')) LIKE '%.css%'
    THEN 1
    ELSE 0
  END as references_external_files,
  
  -- Code metadata
  json_extract(change.value, '$.async') as is_async,
  json_extract(change.value, '$.selector') as css_selector,
  e.created_time as experiment_created,
  e.updated_time as experiment_updated
  
FROM experiments e
INNER JOIN projects p ON e.project_id = p.id
CROSS JOIN json_each(json_extract(e.data_json, '$.variations')) var
CROSS JOIN json_each(json_extract(var.value, '$.actions')) action
CROSS JOIN json_each(json_extract(action.value, '$.changes')) change
WHERE json_extract(change.value, '$.type') IN ('custom_code', 'custom_css', 'insert_html')
  AND (p.is_flags_enabled = 0 OR p.is_flags_enabled IS NULL);