02_create_views.sql

-- Materialized Views for Fast Dashboard Queries
-- Run after 01_create_tables.sql

-- ============================================================================
-- TOP PATHS VIEW (leaderboard data)
-- ============================================================================

CREATE OR REPLACE VIEW `causal_roi.v_top_paths_7d` AS
WITH recent_paths AS (
  SELECT 
    pm.path_id,
    AVG(pm.velocity_ms) AS avg_velocity_ms,
    AVG(pm.p_causal) AS avg_p_causal,
    AVG(pm.pcr) AS avg_pcr,
    AVG(pm.roi_delta) AS avg_roi_delta,
    SUM(pm.n_actions) AS total_actions,
    SUM(pm.n_outcomes) AS total_outcomes,
    STDDEV(pm.roi_delta) AS roi_stddev
  FROM `causal_roi.path_metrics` pm
  WHERE pm.date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY pm.path_id
)
SELECT 
  rp.*,
  -- Rank by confidence-weighted ROI
  ROW_NUMBER() OVER (ORDER BY rp.avg_roi_delta * rp.avg_p_causal DESC) AS rank,
  -- Stability score (lower variance = more stable)
  1.0 / (1.0 + rp.roi_stddev) AS stability_score
FROM recent_paths rp
WHERE rp.total_actions >= 10  -- min sample size
ORDER BY rank
LIMIT 100;

-- ============================================================================
-- EDGE DETAIL VIEW (for graph visualization)
-- ============================================================================

CREATE OR REPLACE VIEW `causal_roi.v_edge_details` AS
SELECT 
  e.edge_id,
  e.src_node_id,
  e.dst_node_id,
  e.edge_type,
  e.latest_delta_roi,
  e.latest_latency_ms,
  e.decay_weight,
  e.n_observations,
  e.last_updated,
  -- Join node labels
  src.label AS src_label,
  src.node_type AS src_type,
  dst.label AS dst_label,
  dst.node_type AS dst_type,
  -- Latest 7d metrics
  em_recent.avg_delta_roi_7d,
  em_recent.total_spend_7d,
  em_recent.total_obs_7d
FROM `causal_roi.edges` e
LEFT JOIN `causal_roi.nodes` src ON e.src_node_id = src.node_id
LEFT JOIN `causal_roi.nodes` dst ON e.dst_node_id = dst.node_id
LEFT JOIN (
  SELECT 
    edge_id,
    AVG(delta_roi) AS avg_delta_roi_7d,
    SUM(spend) AS total_spend_7d,
    SUM(n_obs) AS total_obs_7d
  FROM `causal_roi.edge_metrics`
  WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY edge_id
) em_recent ON e.edge_id = em_recent.edge_id;

-- ============================================================================
-- PATH DETAIL VIEW (single path deep dive)
-- ============================================================================

CREATE OR REPLACE VIEW `causal_roi.v_path_detail` AS
WITH path_edges AS (
  SELECT 
    em.path_id,
    em.edge_id,
    em.date,
    em.delta_roi,
    em.latency_ms,
    em.spend,
    em.n_obs
  FROM `causal_roi.edge_metrics` em
  WHERE em.date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
),
path_timeseries AS (
  SELECT
    path_id,
    date,
    SUM(delta_roi * spend) / NULLIF(SUM(spend), 0) AS weighted_roi,
    SUM(spend) AS daily_spend,
    AVG(latency_ms) AS avg_latency
  FROM path_edges
  GROUP BY path_id, date
)
SELECT 
  pts.path_id,
  pts.date,
  pts.weighted_roi,
  pts.daily_spend,
  pts.avg_latency,
  -- Running 7d average
  AVG(pts.weighted_roi) OVER (
    PARTITION BY pts.path_id 
    ORDER BY pts.date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS roi_7d_ma
FROM path_timeseries pts;

-- ============================================================================
-- DIAGNOSTICS VIEW (system health)
-- ============================================================================

CREATE OR REPLACE VIEW `causal_roi.v_diagnostics_daily` AS
WITH daily_stats AS (
  SELECT
    DATE(created_at) AS date,
    'events_forecast' AS table_name,
    COUNT(*) AS row_count,
    MAX(created_at) AS last_insert
  FROM `causal_roi.events_forecast`
  WHERE DATE(created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY date
  
  UNION ALL
  
  SELECT
    DATE(created_at),
    'events_action',
    COUNT(*),
    MAX(created_at)
  FROM `causal_roi.events_action`
  WHERE DATE(created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY date
  
  UNION ALL
  
  SELECT
    DATE(created_at),
    'events_outcome',
    COUNT(*),
    MAX(created_at)
  FROM `causal_roi.events_outcome`
  WHERE DATE(created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY date
),
attribution_coverage AS (
  SELECT
    DATE(al.created_at) AS date,
    al.method,
    COUNT(DISTINCT al.path_id) AS paths_covered,
    AVG(al.weight) AS avg_weight
  FROM `causal_roi.attribution_links` al
  WHERE DATE(al.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY date, al.method
),
control_quality AS (
  SELECT
    DATE(cc.created_at) AS date,
    cc.method AS control_method,
    COUNT(*) AS n_controls,
    AVG(cc.quality_score) AS avg_quality
  FROM `causal_roi.control_cohorts` cc
  WHERE DATE(cc.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY date, cc.method
)
SELECT 
  ds.date,
  -- Ingest health
  SUM(CASE WHEN ds.table_name = 'events_forecast' THEN ds.row_count ELSE 0 END) AS forecasts_logged,
  SUM(CASE WHEN ds.table_name = 'events_action' THEN ds.row_count ELSE 0 END) AS actions_logged,
  SUM(CASE WHEN ds.table_name = 'events_outcome' THEN ds.row_count ELSE 0 END) AS outcomes_logged,
  MAX(ds.last_insert) AS last_data_insert,
  -- Attribution coverage
  ac.paths_covered AS attributed_paths,
  ac.avg_weight AS avg_attribution_weight,
  -- Control quality
  cq.n_controls,
  cq.avg_quality AS control_quality_score
FROM daily_stats ds
LEFT JOIN attribution_coverage ac ON ds.date = ac.date
LEFT JOIN control_quality cq ON ds.date = cq.date
GROUP BY ds.date, ac.paths_covered, ac.avg_weight, cq.n_controls, cq.avg_quality
ORDER BY ds.date DESC;

-- ============================================================================
-- SYSTEM-WIDE SUMMARY (top bar metrics)
-- ============================================================================

CREATE OR REPLACE VIEW `causal_roi.v_system_summary` AS
WITH today_metrics AS (
  SELECT
    SUM(pm.roi_delta * pm.n_actions) / NULLIF(SUM(pm.n_actions), 0) AS net_delta_roi,
    AVG(pm.velocity_ms) AS mean_velocity_ms,
    SUM(pm.p_causal * CASE WHEN pm.roi_delta > 0 THEN 1 ELSE 0 END) / 
      NULLIF(SUM(pm.p_causal), 0) AS system_pcr
  FROM `causal_roi.path_metrics` pm
  WHERE pm.date = CURRENT_DATE()
)
SELECT
  CURRENT_DATE() AS snapshot_date,
  tm.net_delta_roi,
  tm.mean_velocity_ms,
  tm.system_pcr,
  -- Change from yesterday
  tm.net_delta_roi - COALESCE(yesterday.net_delta_roi, 0) AS delta_roi_change,
  tm.mean_velocity_ms - COALESCE(yesterday.mean_velocity_ms, 0) AS velocity_change,
  tm.system_pcr - COALESCE(yesterday.system_pcr, 0) AS pcr_change
FROM today_metrics tm
LEFT JOIN (
  SELECT
    SUM(pm.roi_delta * pm.n_actions) / NULLIF(SUM(pm.n_actions), 0) AS net_delta_roi,
    AVG(pm.velocity_ms) AS mean_velocity_ms,
    SUM(pm.p_causal * CASE WHEN pm.roi_delta > 0 THEN 1 ELSE 0 END) / 
      NULLIF(SUM(pm.p_causal), 0) AS system_pcr
  FROM `causal_roi.path_metrics` pm
  WHERE pm.date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
) yesterday ON TRUE;

-- ============================================================================
-- EDGE HISTORY (for sparklines)
-- ============================================================================

CREATE OR REPLACE VIEW `causal_roi.v_edge_history_7d` AS
SELECT
  em.edge_id,
  em.date,
  em.delta_roi,
  em.n_obs,
  em.spend,
  -- Trend detection
  AVG(em.delta_roi) OVER (
    PARTITION BY em.edge_id 
    ORDER BY em.date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS roi_3d_ma,
  -- Volatility
  STDDEV(em.delta_roi) OVER (
    PARTITION BY em.edge_id 
    ORDER BY em.date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS roi_7d_stddev
FROM `causal_roi.edge_metrics` em
WHERE em.date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY em.edge_id, em.date;