-- Comprehensive code search patterns view for security analysis
-- Created: 2025-07-19
-- Purpose: Pre-computed pattern matches for common security concerns

CREATE VIEW IF NOT EXISTS code_search_patterns_view AS
WITH all_code AS (
  -- Experiment code from experiment_code_snippets_flat
  SELECT 
    'experiment' as source_type,
    project_id,
    project_name,
    experiment_id as entity_id,
    experiment_name as entity_name,
    code_location,
    variation_name,
    code_type,
    code_content,
    code_length
  FROM experiment_code_snippets_flat
  
  UNION ALL
  
  -- Project JavaScript
  SELECT 
    'project' as source_type,
    id as project_id,
    name as project_name,
    id as entity_id,
    name as entity_name,
    'project_javascript' as code_location,
    NULL as variation_name,
    'project_javascript' as code_type,
    json_extract(data_json, '$.project_javascript') as code_content,
    LENGTH(json_extract(data_json, '$.project_javascript')) as code_length
  FROM projects
  WHERE json_extract(data_json, '$.project_javascript') IS NOT NULL
    AND (is_flags_enabled = 0 OR is_flags_enabled IS NULL)
  
  UNION ALL
  
  -- Extension code
  SELECT 
    'extension' as source_type,
    project_id,
    p.name as project_name,
    e.id as entity_id,
    e.name as entity_name,
    'extension' as code_location,
    NULL as variation_name,
    e.extension_type as code_type,
    e.implementation as code_content,
    LENGTH(e.implementation) as code_length
  FROM extensions e
  INNER JOIN projects p ON e.project_id = p.id
  WHERE e.extension_type = 'custom_code'
    AND e.implementation IS NOT NULL
)
SELECT 
  source_type,
  project_id,
  project_name,
  entity_id,
  entity_name,
  code_location,
  variation_name,
  code_type,
  
  -- Code snippet preview (first 200 chars)
  SUBSTR(code_content, 1, 200) as code_preview,
  code_length,
  
  -- XSS Attack Vectors
  CASE WHEN LOWER(code_content) LIKE '%<script%' THEN 1 ELSE 0 END as has_script_tag,
  CASE WHEN LOWER(code_content) LIKE '%</script>%' THEN 1 ELSE 0 END as has_closing_script_tag,
  CASE WHEN LOWER(code_content) LIKE '%javascript:%' THEN 1 ELSE 0 END as has_javascript_protocol,
  CASE WHEN LOWER(code_content) LIKE '%vbscript:%' THEN 1 ELSE 0 END as has_vbscript_protocol,
  CASE WHEN LOWER(code_content) LIKE '%on%=%' THEN 1 ELSE 0 END as has_inline_event_handler,
  
  -- Dangerous Functions
  CASE WHEN LOWER(code_content) LIKE '%eval(%' THEN 1 ELSE 0 END as uses_eval,
  CASE WHEN LOWER(code_content) LIKE '%function(%' THEN 1 ELSE 0 END as uses_function_constructor,
  CASE WHEN LOWER(code_content) LIKE '%settimeout(%' THEN 1 ELSE 0 END as uses_settimeout,
  CASE WHEN LOWER(code_content) LIKE '%setinterval(%' THEN 1 ELSE 0 END as uses_setinterval,
  CASE WHEN LOWER(code_content) LIKE '%document.write%' THEN 1 ELSE 0 END as uses_document_write,
  CASE WHEN LOWER(code_content) LIKE '%innerhtml%' THEN 1 ELSE 0 END as uses_innerhtml,
  CASE WHEN LOWER(code_content) LIKE '%outerhtml%' THEN 1 ELSE 0 END as uses_outerhtml,
  
  -- External Resources
  CASE WHEN code_content LIKE '%http://%' THEN 1 ELSE 0 END as has_http_url,
  CASE WHEN code_content LIKE '%https://%' THEN 1 ELSE 0 END as has_https_url,
  CASE WHEN code_content LIKE '%//%' AND code_content NOT LIKE '%://%' THEN 1 ELSE 0 END as has_protocol_relative_url,
  CASE WHEN LOWER(code_content) LIKE '%.js%' OR LOWER(code_content) LIKE '%.css%' THEN 1 ELSE 0 END as references_external_files,
  
  -- Data Exfiltration Risks
  CASE WHEN LOWER(code_content) LIKE '%fetch(%' THEN 1 ELSE 0 END as uses_fetch,
  CASE WHEN LOWER(code_content) LIKE '%xmlhttprequest%' THEN 1 ELSE 0 END as uses_xmlhttprequest,
  CASE WHEN LOWER(code_content) LIKE '%$.ajax%' OR LOWER(code_content) LIKE '%jquery.ajax%' THEN 1 ELSE 0 END as uses_jquery_ajax,
  CASE WHEN LOWER(code_content) LIKE '%navigator.sendbeacon%' THEN 1 ELSE 0 END as uses_sendbeacon,
  
  -- Cookie/Storage Access
  CASE WHEN LOWER(code_content) LIKE '%document.cookie%' THEN 1 ELSE 0 END as accesses_cookies,
  CASE WHEN LOWER(code_content) LIKE '%localstorage%' THEN 1 ELSE 0 END as uses_localstorage,
  CASE WHEN LOWER(code_content) LIKE '%sessionstorage%' THEN 1 ELSE 0 END as uses_sessionstorage,
  
  -- DOM Manipulation
  CASE WHEN LOWER(code_content) LIKE '%createelement%' THEN 1 ELSE 0 END as creates_elements,
  CASE WHEN LOWER(code_content) LIKE '%appendchild%' THEN 1 ELSE 0 END as appends_children,
  CASE WHEN LOWER(code_content) LIKE '%insertadjacenthtml%' THEN 1 ELSE 0 END as uses_insertadjacenthtml,
  
  -- Iframe/Frame Usage
  CASE WHEN LOWER(code_content) LIKE '%<iframe%' THEN 1 ELSE 0 END as has_iframe,
  CASE WHEN LOWER(code_content) LIKE '%window.top%' OR LOWER(code_content) LIKE '%window.parent%' THEN 1 ELSE 0 END as accesses_parent_frame,
  
  -- Base64 Encoded Content
  CASE WHEN code_content LIKE '%base64,%' THEN 1 ELSE 0 END as has_base64_content,
  
  -- Overall Security Score (higher = more concerning)
  (
    CASE WHEN LOWER(code_content) LIKE '%<script%' THEN 3 ELSE 0 END +
    CASE WHEN LOWER(code_content) LIKE '%eval(%' THEN 3 ELSE 0 END +
    CASE WHEN LOWER(code_content) LIKE '%document.write%' THEN 2 ELSE 0 END +
    CASE WHEN LOWER(code_content) LIKE '%innerhtml%' THEN 2 ELSE 0 END +
    CASE WHEN code_content LIKE '%http://%' THEN 1 ELSE 0 END +
    CASE WHEN LOWER(code_content) LIKE '%document.cookie%' THEN 2 ELSE 0 END +
    CASE WHEN LOWER(code_content) LIKE '%fetch(%' THEN 1 ELSE 0 END +
    CASE WHEN LOWER(code_content) LIKE '%javascript:%' THEN 3 ELSE 0 END
  ) as security_concern_score
  
FROM all_code
WHERE code_content IS NOT NULL AND LENGTH(code_content) > 0;