-- 50_gapless_ticket_numbers.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Cautionary gapless sequence for ticket queue.
-- 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.

-- Gapless queue numbers (not recommended at scale) using advisory lock
CREATE TABLE IF NOT EXISTS bank.queue_numbers (num BIGINT PRIMARY KEY, created_at timestamptz default now());
CREATE OR REPLACE FUNCTION bank.next_queue_num() RETURNS BIGINT LANGUAGE plpgsql AS $$
DECLARE n BIGINT;
BEGIN
  PERFORM pg_advisory_xact_lock(777);
  SELECT COALESCE(MAX(num),0)+1 INTO n FROM bank.queue_numbers;
  INSERT INTO bank.queue_numbers(num) VALUES (n);
  RETURN n;
END $$;
