Materialized View with Auto-Refresh

-- Create materialized view for dashboard stats
CREATE MATERIALIZED VIEW dashboard_stats AS
SELECT
  date_trunc('day', o.created_at)::date AS day,
  count(DISTINCT o.id) AS order_count,
  count(DISTINCT o.user_id) AS unique_customers,
  sum(o.total) AS revenue,
  avg(o.total) AS avg_order_value,
  count(DISTINCT o.id) FILTER (WHERE o.status = 'refunded') AS refunds
FROM orders o
WHERE o.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1
WITH DATA;

-- Unique index required for CONCURRENTLY refresh
CREATE UNIQUE INDEX idx_dashboard_stats_day ON dashboard_stats (day);

-- Refresh without blocking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_stats;

-- Query the materialized view (fast)
SELECT day, revenue, order_count
FROM dashboard_stats
WHERE day >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY day;

-- Check when it was last refreshed
SELECT schemaname, matviewname,
  pg_size_pretty(pg_total_relation_size(matviewname::text)) AS size
FROM pg_matviews
WHERE matviewname = 'dashboard_stats';

-- Schedule refresh via pg_cron (if available)
-- SELECT cron.schedule('refresh-stats', '*/15 * * * *',
--   'REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_stats');