-- Auto-extracted view: change_history_flat
-- Extracted at: 2025-07-08T19:54:36.034Z
-- Status: Unknown (no source file found)
-- Original source: UNKNOWN - possibly from archived files

CREATE VIEW change_history_flat AS
SELECT 
  -- Primary change identification
  ch.id as change_id,
  ch.project_id,
  ch.entity_type,
  ch.entity_id,
  ch.entity_name,
  ch.action as change_type,
  ch.timestamp as change_timestamp,
  ch.changed_by,
  ch.change_summary,
  ch.archived,
  ch.synced_at,
  ch.created_at,
  
  -- Pre-computed change categorization (based on action patterns)
  CASE 
    WHEN LOWER(ch.action) LIKE '%publish%' OR LOWER(ch.action) LIKE '%deploy%' OR LOWER(ch.action) LIKE '%release%' THEN 'publishing'
    WHEN LOWER(ch.action) LIKE '%pause%' OR LOWER(ch.action) LIKE '%stop%' OR LOWER(ch.action) LIKE '%halt%' THEN 'pausing'
    WHEN LOWER(ch.action) LIKE '%enable%' OR LOWER(ch.action) LIKE '%activate%' OR LOWER(ch.action) LIKE '%turn on%' THEN 'activation'
    WHEN LOWER(ch.action) LIKE '%disable%' OR LOWER(ch.action) LIKE '%deactivate%' OR LOWER(ch.action) LIKE '%turn off%' THEN 'deactivation'
    WHEN LOWER(ch.action) LIKE '%create%' OR LOWER(ch.action) LIKE '%add%' OR LOWER(ch.action) LIKE '%new%' THEN 'creation'
    WHEN LOWER(ch.action) LIKE '%update%' OR LOWER(ch.action) LIKE '%modify%' OR LOWER(ch.action) LIKE '%edit%' OR LOWER(ch.action) LIKE '%change%' THEN 'modification'
    WHEN LOWER(ch.action) LIKE '%delete%' OR LOWER(ch.action) LIKE '%remove%' OR LOWER(ch.action) LIKE '%archive%' THEN 'deletion'
    ELSE 'other'
  END as change_category,
  
  -- Pre-computed temporal classifications  
  CASE 
    WHEN datetime(ch.timestamp) >= datetime('now', '-1 day') THEN 'today'
    WHEN datetime(ch.timestamp) >= datetime('now', '-7 days') THEN 'this_week'
    WHEN datetime(ch.timestamp) >= datetime('now', '-30 days') THEN 'this_month'
    WHEN datetime(ch.timestamp) >= datetime('now', '-90 days') THEN 'this_quarter'
    ELSE 'older'
  END as recency_category,
  
  -- Pre-computed time components for grouping
  date(ch.timestamp) as change_date,
  strftime('%Y-%m', ch.timestamp) as change_month,
  strftime('%Y-%W', ch.timestamp) as change_week,
  strftime('%H', ch.timestamp) as change_hour,
  strftime('%w', ch.timestamp) as change_day_of_week,
  
  -- Denormalized entity context (zero JOINs by left joining with all possible entities)
  COALESCE(
    f.name,     -- Flag name
    e.name,     -- Experiment name  
    a.name,     -- Audience name
    p.name,     -- Page name
    c.name,     -- Campaign name
    ch.entity_name  -- Fallback to stored entity_name
  ) as resolved_entity_name,
  
  -- Environment context (where applicable) - FIXED: Use fe.environment_key only
  fe.environment_key as environment_key,
  
  -- Project context (pre-resolved)
  pr.name as project_name,
  pr.platform as project_platform,
  pr.is_flags_enabled as is_feature_experimentation,
  
  -- Entity status context
  CASE ch.entity_type
    WHEN 'flag' THEN 
      CASE WHEN f.archived THEN 'archived' ELSE 'active' END
    WHEN 'experiment' THEN 
      COALESCE(e.status, 'unknown')
    WHEN 'audience' THEN
      CASE WHEN a.archived THEN 'archived' ELSE 'active' END
    WHEN 'page' THEN
      CASE WHEN p.archived THEN 'archived' ELSE 'active' END
    ELSE 'unknown'
  END as current_entity_status,
  
  -- Pre-computed boolean flags for common queries
  CASE WHEN ch.changed_by IS NOT NULL AND ch.changed_by != '' THEN 1 ELSE 0 END as has_user_attribution,
  CASE WHEN ch.change_summary IS NOT NULL AND ch.change_summary != '' THEN 1 ELSE 0 END as has_change_details,
  CASE WHEN ch.archived THEN 1 ELSE 0 END as is_archived_change,
  CASE WHEN datetime(ch.timestamp) >= datetime('now', '-1 hour') THEN 1 ELSE 0 END as is_recent_change,
  
  -- Environment classification - FIXED: Use fe.environment_key only
  CASE 
    WHEN fe.environment_key = 'production' THEN 'production'
    WHEN fe.environment_key LIKE '%prod%' THEN 'production'
    WHEN fe.environment_key IN ('staging', 'stage') THEN 'staging'
    WHEN fe.environment_key IN ('development', 'dev') THEN 'development'
    ELSE 'unknown'
  END as environment_type,
  
  -- User classification (basic patterns)
  CASE 
    WHEN ch.changed_by LIKE '%@%' THEN 'email_user'
    WHEN ch.changed_by LIKE 'api_%' OR ch.changed_by LIKE 'system_%' THEN 'system_user'
    WHEN ch.changed_by IS NULL OR ch.changed_by = '' THEN 'unknown_user'
    ELSE 'named_user'
  END as user_type

FROM change_history ch

-- Left join with projects for project context
LEFT JOIN projects pr ON ch.project_id = pr.id

-- Left join with all possible entity types for name resolution
LEFT JOIN flags f ON ch.entity_type = 'flag' AND ch.entity_id = f.key AND ch.project_id = f.project_id
LEFT JOIN experiments e ON ch.entity_type = 'experiment' AND ch.entity_id = e.id AND ch.project_id = e.project_id  
LEFT JOIN audiences a ON ch.entity_type = 'audience' AND ch.entity_id = a.id AND ch.project_id = a.project_id
LEFT JOIN pages p ON ch.entity_type = 'page' AND ch.entity_id = p.id AND ch.project_id = p.project_id
LEFT JOIN campaigns c ON ch.entity_type = 'campaign' AND ch.entity_id = c.id AND ch.project_id = c.project_id

-- Left join for environment context (flags can have per-environment data)
LEFT JOIN flag_environments fe ON ch.entity_type = 'flag' AND ch.entity_id = fe.flag_key AND ch.project_id = fe.project_id

-- Only include non-archived changes by default (archived changes can be queried explicitly)
WHERE ch.archived = FALSE

-- Order by most recent first for default queries
ORDER BY ch.timestamp DESC;
