-- 10_temporal_history_accounts.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Temporal version table for account balances.
-- 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.

-- Temporal history on accounts to track old balances (simplified)
CREATE TABLE IF NOT EXISTS bank.accounts_history (
  hist_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id BIGINT NOT NULL,
  balance NUMERIC(18,2) NOT NULL,
  valid_from TIMESTAMPTZ NOT NULL,
  valid_to TIMESTAMPTZ
);

CREATE OR REPLACE FUNCTION bank.trg_accounts_history() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'UPDATE' THEN
    INSERT INTO bank.accounts_history (account_id, balance, valid_from, valid_to)
    VALUES (OLD.account_id, OLD.balance, COALESCE(OLD.updated_at, OLD.created_at), now());
    RETURN NEW;
  END IF;
  RETURN NULL;
END; $$;

-- Ensure updated_at exists
ALTER TABLE bank.accounts ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ;
DROP TRIGGER IF EXISTS trg_accounts_history ON bank.accounts;
CREATE TRIGGER trg_accounts_history BEFORE UPDATE ON bank.accounts
FOR EACH ROW EXECUTE FUNCTION bank.trg_accounts_history();
