-- 40_cqrs_read_model_mv.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: CQRS read model via materialized view.
-- 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.

-- CQRS read model via MV (example: totals by customer)
CREATE MATERIALIZED VIEW IF NOT EXISTS bank.mv_customer_totals AS
SELECT c.customer_id, SUM(CASE WHEN t.tx_type='credit' THEN t.amount ELSE -t.amount END) AS net_total
FROM bank.customers c
LEFT JOIN bank.accounts a ON a.customer_id = c.customer_id
LEFT JOIN bank.transactions t ON t.account_id = a.account_id
GROUP BY c.customer_id;
