-- 13_upsert_customer_contact.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: UPSERT customer contact details.
-- 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;

-- Upsert contact record
CREATE TABLE IF NOT EXISTS bank.customer_contacts (
  customer_id BIGINT PRIMARY KEY REFERENCES bank.customers(customer_id),
  address TEXT,
  city TEXT,
  postal_code TEXT,
  updated_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO bank.customer_contacts (customer_id, address, city, postal_code)
VALUES (1, '101 Marine Drive', 'Mumbai', '400001')
ON CONFLICT (customer_id) DO UPDATE
SET address = EXCLUDED.address, city = EXCLUDED.city, postal_code = EXCLUDED.postal_code, updated_at = now();
