-- 17_batch_upsert_cards.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Batch upsert card portfolio snapshots.
-- 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;

-- Batch upsert cards
CREATE TABLE IF NOT EXISTS bank.cards_portfolio (
  masked_pan TEXT PRIMARY KEY,
  account_id BIGINT NOT NULL REFERENCES bank.accounts(account_id),
  active BOOLEAN NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now()
);

WITH data(masked_pan, account_id, active) AS (
  VALUES ('4111********1111', 1, TRUE), ('5555********2222', 1, FALSE)
)
INSERT INTO bank.cards_portfolio (masked_pan, account_id, active)
SELECT * FROM data
ON CONFLICT (masked_pan) DO UPDATE SET account_id = EXCLUDED.account_id, active = EXCLUDED.active, updated_at = now();
