-- 21_materialized_view_balances.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Materialized view of account daily balances.
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.

-- MV that aggregates daily balances (demo with sum of credits - debits per account/day)
CREATE MATERIALIZED VIEW IF NOT EXISTS bank.mv_daily_totals AS
SELECT account_id, date(created_at) AS d, SUM(CASE WHEN tx_type='credit' THEN amount ELSE -amount END) AS net
FROM bank.transactions
GROUP BY account_id, date(created_at);

-- Refresh when needed
-- REFRESH MATERIALIZED VIEW bank.mv_daily_totals;
