-- Auto-extracted view: flag_state_history_view
-- Extracted at: 2025-07-08T19:54:36.055Z
-- Status: Known (has source file)
-- Original source: src/analytics/views/

CREATE VIEW flag_state_history_view AS
WITH latest_states AS (
  -- Get the most recent state for each flag/environment
  SELECT 
    ch.entity_id as flag_key,
    ch.project_id,
    -- Extract environment from change_summary or entity_name
    CASE 
      WHEN ch.change_summary LIKE '%environment:%' 
      THEN substr(ch.change_summary, 
                  instr(ch.change_summary, 'environment:') + 12,
                  instr(substr(ch.change_summary, instr(ch.change_summary, 'environment:') + 12), ' ') - 1)
      ELSE 'production' -- default if not specified
    END as environment_key,
    ch.action,
    ch.timestamp as changed_at,
    ch.changed_by,
    ROW_NUMBER() OVER (
      PARTITION BY ch.entity_id, ch.project_id,
      CASE 
        WHEN ch.change_summary LIKE '%environment:%' 
        THEN substr(ch.change_summary, 
                    instr(ch.change_summary, 'environment:') + 12,
                    instr(substr(ch.change_summary, instr(ch.change_summary, 'environment:') + 12), ' ') - 1)
        ELSE 'production'
      END
      ORDER BY ch.timestamp DESC
    ) as rn
  FROM change_history ch
  WHERE ch.entity_type = 'flag' 
    AND (ch.action LIKE '%enable%' OR ch.action LIKE '%disable%' OR ch.action = 'update')
),
enable_disable_history AS (
  -- Track when flags were enabled/disabled
  SELECT 
    entity_id as flag_key,
    project_id,
    -- Simplified environment extraction
    CASE 
      WHEN change_summary LIKE '%production%' THEN 'production'
      WHEN change_summary LIKE '%development%' THEN 'development'
      WHEN change_summary LIKE '%staging%' THEN 'staging'
      ELSE 'production'
    END as environment_key,
    timestamp as changed_at,
    CASE 
      WHEN action LIKE '%enable%' OR change_summary LIKE '%enabled%' THEN 'enabled'
      WHEN action LIKE '%disable%' OR change_summary LIKE '%disabled%' THEN 'disabled'
      ELSE 'unknown'
    END as state_change,
    changed_by
  FROM change_history
  WHERE entity_type = 'flag'
    AND (action LIKE '%enable%' OR action LIKE '%disable%' OR 
         change_summary LIKE '%enabled%' OR change_summary LIKE '%disabled%')
)
SELECT 
  f.project_id,
  p.name as project_name,
  f.key as flag_key,
  f.name as flag_name,
  fe.environment_key,
  e.name as environment_name,
  fe.enabled as current_enabled_status,
  
  -- Last enable time
  (
    SELECT MAX(changed_at) 
    FROM enable_disable_history eh
    WHERE eh.flag_key = f.key 
      AND eh.project_id = f.project_id
      AND eh.environment_key = fe.environment_key
      AND eh.state_change = 'enabled'
  ) as last_enabled_time,
  
  -- Last disable time
  (
    SELECT MAX(changed_at) 
    FROM enable_disable_history eh
    WHERE eh.flag_key = f.key 
      AND eh.project_id = f.project_id
      AND eh.environment_key = fe.environment_key
      AND eh.state_change = 'disabled'
  ) as last_disabled_time,
  
  -- Total state changes
  (
    SELECT COUNT(*) 
    FROM enable_disable_history eh
    WHERE eh.flag_key = f.key 
      AND eh.project_id = f.project_id
      AND eh.environment_key = fe.environment_key
  ) as total_state_changes,
  
  -- Has it been enabled in last 30 days?
  CASE 
    WHEN EXISTS (
      SELECT 1 
      FROM enable_disable_history eh
      WHERE eh.flag_key = f.key 
        AND eh.project_id = f.project_id
        AND eh.environment_key = fe.environment_key
        AND eh.state_change = 'enabled'
        AND eh.changed_at >= datetime('now', '-30 days')
    ) THEN 1 
    ELSE 0 
  END as enabled_in_last_30_days,
  
  -- Has it been disabled for more than 30 days?
  CASE 
    WHEN fe.enabled = 0 
      AND NOT EXISTS (
        SELECT 1 
        FROM enable_disable_history eh
        WHERE eh.flag_key = f.key 
          AND eh.project_id = f.project_id
          AND eh.environment_key = fe.environment_key
          AND eh.state_change = 'enabled'
          AND eh.changed_at >= datetime('now', '-30 days')
      )
    THEN 1 
    ELSE 0
  END as disabled_for_30_plus_days,
  
  -- Days since last enabled (NULL if never enabled)
  CASE 
    WHEN EXISTS (
      SELECT 1 
      FROM enable_disable_history eh
      WHERE eh.flag_key = f.key 
        AND eh.project_id = f.project_id
        AND eh.environment_key = fe.environment_key
        AND eh.state_change = 'enabled'
    )
    THEN CAST((
      julianday('now') - julianday(
        (SELECT MAX(changed_at) 
         FROM enable_disable_history eh
         WHERE eh.flag_key = f.key 
           AND eh.project_id = f.project_id
           AND eh.environment_key = fe.environment_key
           AND eh.state_change = 'enabled')
      )
    ) AS INTEGER)
    ELSE NULL
  END as days_since_enabled,
  
  -- Days since last disabled (NULL if never disabled)
  CASE 
    WHEN EXISTS (
      SELECT 1 
      FROM enable_disable_history eh
      WHERE eh.flag_key = f.key 
        AND eh.project_id = f.project_id
        AND eh.environment_key = fe.environment_key
        AND eh.state_change = 'disabled'
    )
    THEN CAST((
      julianday('now') - julianday(
        (SELECT MAX(changed_at) 
         FROM enable_disable_history eh
         WHERE eh.flag_key = f.key 
           AND eh.project_id = f.project_id
           AND eh.environment_key = fe.environment_key
           AND eh.state_change = 'disabled')
      )
    ) AS INTEGER)
    ELSE NULL
  END as days_since_disabled,
  
  -- Never been enabled flag
  CASE 
    WHEN NOT EXISTS (
      SELECT 1 
      FROM enable_disable_history eh
      WHERE eh.flag_key = f.key 
        AND eh.project_id = f.project_id
        AND eh.environment_key = fe.environment_key
        AND eh.state_change = 'enabled'
    ) THEN 1
    ELSE 0
  END as never_been_enabled

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
WHERE p.is_flags_enabled = 1;
