-- Wave 11: Bank Reconciliation - Ledger Transactions
-- Extracted Journal Entry Lines for Bank Accounts

CREATE TABLE IF NOT EXISTS ledger_transactions (
  id TEXT PRIMARY KEY,
  bank_account_id TEXT NOT NULL,
  journal_entry_id TEXT NOT NULL,
  journal_entry_line_idx INT 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,
  reference_type TEXT,
  reference_id TEXT,
  posting_status TEXT NOT NULL DEFAULT 'posted'
    CHECK (posting_status IN ('draft', 'posted')),
  reconciliation_status TEXT NOT NULL DEFAULT 'unreconciled'
    CHECK (reconciliation_status IN ('unreconciled', 'reconciled', 'partial', 'disputed')),
  reconciliation_period_id TEXT,
  last_reconciliation_date TIMESTAMPTZ,
  currency_code TEXT,
  fx_rate NUMERIC(19, 6) DEFAULT 1,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_ledger_txn_account FOREIGN KEY (bank_account_id) 
    REFERENCES banks(id),
  CONSTRAINT fk_ledger_txn_period FOREIGN KEY (reconciliation_period_id) 
    REFERENCES bank_reconciliation_periods(id),
  CONSTRAINT check_ledger_txn_amount CHECK (amount > 0),
  CONSTRAINT check_ledger_txn_rate CHECK (fx_rate > 0)
);

CREATE INDEX IF NOT EXISTS idx_ledger_txn_bank ON ledger_transactions(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_ledger_txn_date ON ledger_transactions(transaction_date);
CREATE INDEX IF NOT EXISTS idx_ledger_txn_status ON ledger_transactions(reconciliation_status);
CREATE INDEX IF NOT EXISTS idx_ledger_txn_je ON ledger_transactions(journal_entry_id);
CREATE INDEX IF NOT EXISTS idx_ledger_txn_period ON ledger_transactions(reconciliation_period_id);
CREATE INDEX IF NOT EXISTS idx_ledger_txn_created ON ledger_transactions(created_at DESC);