-- 23_jsonb_kyc_profile.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: JSONB for KYC profile docs + queries.
-- 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;

-- JSONB KYC profiles
CREATE TABLE IF NOT EXISTS bank.kyc_profiles (
  customer_id BIGINT PRIMARY KEY REFERENCES bank.customers(customer_id),
  profile JSONB NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO bank.kyc_profiles (customer_id, profile) VALUES
(1, '{"pan":"ABCDE1234F","aadhaar":"XXXX-XXXX-XXXX","risk":"low","docs":["id","address"]}')
ON CONFLICT (customer_id) DO NOTHING;

-- Query example
SELECT customer_id FROM bank.kyc_profiles WHERE profile @> '{"risk":"low"}';
