Views & Materialized Views

-- Create a view for the sales dashboard (hides internal columns)
CREATE OR REPLACE VIEW v_order_summary AS
SELECT
    o.id          AS order_id,
    u.email       AS customer_email,
    o.total_amount,
    o.status,
    o.created_at
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status != 'cancelled';

-- Query the view exactly like a table
SELECT * FROM v_order_summary WHERE status = 'pending';

-- Security: grant access to view but not underlying tables
GRANT SELECT ON v_order_summary TO reporting_role;

-- Updatable view (simple, single-table, no aggregation)
CREATE VIEW v_active_users AS
SELECT id, email, username FROM users WHERE status = 'active';
-- This INSERT goes through to the base table:
INSERT INTO v_active_users(email, username) VALUES ('bob@x.com', 'bob');

-- Materialized view: pre-aggregate daily revenue (expensive query)
CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT
    DATE_TRUNC('day', created_at)::DATE AS revenue_date,
    COUNT(*)                            AS order_count,
    SUM(total_amount)                   AS total_revenue
FROM orders
WHERE status = 'completed'
GROUP BY 1
ORDER BY 1;

-- Add an index on the materialized view for fast date range queries
CREATE UNIQUE INDEX idx_mv_daily_revenue_date ON mv_daily_revenue(revenue_date);

-- Refresh blocking (no concurrent reads during refresh)
REFRESH MATERIALIZED VIEW mv_daily_revenue;

-- Refresh non-blocking — requires a UNIQUE index on the view
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;

-- Schedule via pg_cron or an external scheduler:
-- SELECT cron.schedule('0 1 * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue');

-- MySQL equivalent: no native materialized views — use a scheduled event + real table
CREATE TABLE mv_daily_revenue (...);
CREATE EVENT refresh_mv ON SCHEDULE EVERY 1 HOUR DO
    CALL refresh_daily_revenue_proc();