-- Wave 11: Bank Reconciliation - Bank Transactions
-- Bank Statement Transactions Table

CREATE TABLE IF NOT EXISTS bank_transactions (
  id TEXT PRIMARY KEY,
  reconciliation_period_id TEXT NOT NULL,
  bank_account_id TEXT NOT NULL,
  transaction_date DATE NOT NULL,
  amount NUMERIC(19, 2) NOT NULL,
  direction TEXT NOT NULL
    CHECK (direction IN ('inward', 'outward', 'debit', 'credit')),
  description TEXT,
  bank_reference TEXT,
  type TEXT
    CHECK (type IN ('check', 'transfer', 'fee', 'interest', 'charge', 'other')),
  currency_code TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_bank_txn_period FOREIGN KEY (reconciliation_period_id) 
    REFERENCES bank_reconciliation_periods(id) ON DELETE CASCADE,
  CONSTRAINT fk_bank_txn_account FOREIGN KEY (bank_account_id) 
    REFERENCES banks(id),
  CONSTRAINT check_bank_txn_amount CHECK (amount > 0)
);

CREATE INDEX IF NOT EXISTS idx_bank_txn_period ON bank_transactions(reconciliation_period_id);
CREATE INDEX IF NOT EXISTS idx_bank_txn_account ON bank_transactions(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_bank_txn_date ON bank_transactions(transaction_date);
CREATE INDEX IF NOT EXISTS idx_bank_txn_type ON bank_transactions(type);
CREATE INDEX IF NOT EXISTS idx_bank_txn_created ON bank_transactions(created_at DESC);