-- 01_create_banking_schema.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Create banking schema, core tables (customers, accounts, transactions).
-- 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;

-- Create schema and core tables used throughout
CREATE SCHEMA IF NOT EXISTS bank;

-- Customers
CREATE TABLE IF NOT EXISTS bank.customers (
  customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name TEXT NOT NULL,
  email TEXT UNIQUE,
  phone TEXT UNIQUE,
  created_at TIMESTAMPTZ DEFAULT now()
); 

-- Branches
CREATE TABLE IF NOT EXISTS bank.branches (
  branch_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  region TEXT NOT NULL,
  bic CHAR(11) UNIQUE, -- SWIFT/BIC code
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Accounts
CREATE TABLE IF NOT EXISTS bank.accounts (
  account_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES bank.customers(customer_id),
  branch_id BIGINT NOT NULL REFERENCES bank.branches(branch_id),
  iban TEXT UNIQUE,
  account_type TEXT NOT NULL CHECK (account_type IN ('checking','savings','loan')),
  balance NUMERIC(18,2) NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'INR',
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Transactions (simple)
CREATE TABLE IF NOT EXISTS bank.transactions (
  tx_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id BIGINT NOT NULL REFERENCES bank.accounts(account_id),
  amount NUMERIC(18,2) NOT NULL,
  tx_type TEXT NOT NULL CHECK (tx_type IN ('debit','credit')),
  description TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Cards
CREATE TABLE IF NOT EXISTS bank.cards (
  card_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id BIGINT NOT NULL REFERENCES bank.accounts(account_id),
  masked_pan TEXT NOT NULL, -- like 4111********1111
  active BOOLEAN NOT NULL DEFAULT TRUE,
  issued_at TIMESTAMPTZ DEFAULT now()
);

-- Seed minimal data (idempotent patterns later will upsert safely)
INSERT INTO bank.branches (name, region, bic) VALUES ('Mumbai Main', 'west', 'HDFCINBBXXX')
ON CONFLICT (bic) DO NOTHING;

INSERT INTO bank.customers (full_name, email, phone) VALUES
('Asha Mehta','asha@example.com','+91-9000000001')
ON CONFLICT DO NOTHING;
