-- 14_merge_daily_rates.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: MERGE daily FX rates into reference table.
-- 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.

-- FX daily rates using MERGE (PG 15+)
CREATE TABLE IF NOT EXISTS bank.fx_rates (
  as_of DATE NOT NULL,
  ccy CHAR(3) NOT NULL,
  rate NUMERIC(18,8) NOT NULL,
  PRIMARY KEY (as_of, ccy)
);

MERGE INTO bank.fx_rates t
USING (VALUES (CURRENT_DATE, 'USD', 83.0000), (CURRENT_DATE, 'EUR', 90.0000)) s(as_of, ccy, rate)
ON (t.as_of = s.as_of AND t.ccy = s.ccy)
WHEN MATCHED THEN UPDATE SET rate = s.rate
WHEN NOT MATCHED THEN INSERT (as_of, ccy, rate) VALUES (s.as_of, s.ccy, s.rate);
