-- 15_idempotent_insert_transactions.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Idempotent inserts for inbound transaction feed.
-- 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;

-- Idempotent insert of inbound transaction (external_id unique)
CREATE TABLE IF NOT EXISTS bank.inbound_tx (
  external_id TEXT PRIMARY KEY,
  account_id BIGINT NOT NULL REFERENCES bank.accounts(account_id),
  amount NUMERIC(18,2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

WITH payload AS (
  SELECT 'PAY-12345'::text AS external_id, 1::bigint AS account_id, 250.00::numeric AS amount
)
INSERT INTO bank.inbound_tx (external_id, account_id, amount)
SELECT p.external_id, p.account_id, p.amount
FROM payload p
WHERE NOT EXISTS (SELECT 1 FROM bank.inbound_tx x WHERE x.external_id = p.external_id);
