-- View for analyzing project-level JavaScript and security summary
-- Created: 2025-07-19
-- Purpose: Expose project JavaScript and aggregate security metrics

CREATE VIEW IF NOT EXISTS project_code_security_view AS
SELECT 
  p.id as project_id,
  p.name as project_name,
  p.status as project_status,
  
  -- Project JavaScript
  json_extract(p.data_json, '$.project_javascript') as project_javascript,
  LENGTH(json_extract(p.data_json, '$.project_javascript')) as project_js_length,
  
  -- Security flags for project JavaScript
  CASE 
    WHEN LOWER(json_extract(p.data_json, '$.project_javascript')) LIKE '%<script%' THEN 1
    ELSE 0
  END as project_js_has_script_tag,
  
  CASE 
    WHEN LOWER(json_extract(p.data_json, '$.project_javascript')) LIKE '%eval(%' THEN 1
    ELSE 0
  END as project_js_has_eval,
  
  CASE 
    WHEN LOWER(json_extract(p.data_json, '$.project_javascript')) LIKE '%document.write%' THEN 1
    ELSE 0
  END as project_js_has_document_write,
  
  -- Aggregate security metrics from experiments
  (
    SELECT COUNT(DISTINCT experiment_id)
    FROM experiment_code_snippets_flat
    WHERE project_id = p.id
      AND (contains_script_tag = 1 
        OR contains_eval = 1 
        OR contains_document_write = 1
        OR contains_javascript_protocol = 1)
  ) as experiments_with_security_flags,
  
  (
    SELECT COUNT(*)
    FROM experiment_code_snippets_flat
    WHERE project_id = p.id
      AND contains_script_tag = 1
  ) as total_script_tags,
  
  (
    SELECT COUNT(*)
    FROM experiment_code_snippets_flat
    WHERE project_id = p.id
      AND contains_eval = 1
  ) as total_eval_usage,
  
  (
    SELECT COUNT(*)
    FROM experiment_code_snippets_flat
    WHERE project_id = p.id
      AND contains_external_urls = 1
  ) as snippets_with_external_urls,
  
  (
    SELECT COUNT(DISTINCT experiment_id)
    FROM experiment_code_snippets_flat
    WHERE project_id = p.id
      AND code_type = 'custom_code'
  ) as experiments_with_javascript,
  
  (
    SELECT COUNT(*)
    FROM experiment_code_snippets_flat
    WHERE project_id = p.id
      AND code_type = 'custom_code'
  ) as total_javascript_snippets,
  
  (
    SELECT COUNT(*)
    FROM experiment_code_snippets_flat
    WHERE project_id = p.id
      AND code_type = 'insert_html'
  ) as total_html_snippets,
  
  (
    SELECT MAX(code_length)
    FROM experiment_code_snippets_flat
    WHERE project_id = p.id
  ) as largest_code_snippet_size,
  
  p.created_at as project_created,
  p.last_modified as project_updated
  
FROM projects p
WHERE p.is_flags_enabled = 0 OR p.is_flags_enabled IS NULL;