-- 03_uuid_keys_pgcrypto.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: UUID keys for distributed banking services (pgcrypto).
-- 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.
SET search_path = bank, public;

-- Enable pgcrypto for gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Use UUID for cross‑service IDs (e.g., external references)
CREATE TABLE IF NOT EXISTS bank.external_refs (
  ref_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source TEXT NOT NULL,
  mapped_entity TEXT NOT NULL,
  mapped_id BIGINT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);
