-- Wave 11: Bank Reconciliation - Matches
-- Linking Bank Transactions to Ledger Transactions

CREATE TABLE IF NOT EXISTS reconciliation_matches (
  id TEXT PRIMARY KEY,
  reconciliation_period_id TEXT NOT NULL,
  bank_transaction_id TEXT NOT NULL,
  ledger_transaction_ids TEXT[] NOT NULL DEFAULT '{}',
  match_type TEXT NOT NULL
    CHECK (match_type IN ('auto', 'manual', 'partial', 'multi-line')),
  match_confidence NUMERIC(5, 2) NOT NULL DEFAULT 100,
  match_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  matched_by TEXT NOT NULL,
  notes TEXT,
  status TEXT NOT NULL DEFAULT 'matched'
    CHECK (status IN ('matched', 'unmatched', 'disputed', 'pending')),
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_match_period FOREIGN KEY (reconciliation_period_id) 
    REFERENCES bank_reconciliation_periods(id) ON DELETE CASCADE,
  CONSTRAINT fk_match_bank_txn FOREIGN KEY (bank_transaction_id) 
    REFERENCES bank_transactions(id) ON DELETE CASCADE,
  CONSTRAINT check_match_confidence CHECK (match_confidence >= 0 AND match_confidence <= 100)
);

CREATE INDEX IF NOT EXISTS idx_match_period ON reconciliation_matches(reconciliation_period_id);
CREATE INDEX IF NOT EXISTS idx_match_bank_txn ON reconciliation_matches(bank_transaction_id);
CREATE INDEX IF NOT EXISTS idx_match_status ON reconciliation_matches(status);
CREATE INDEX IF NOT EXISTS idx_match_confidence ON reconciliation_matches(match_confidence);
CREATE INDEX IF NOT EXISTS idx_match_created ON reconciliation_matches(created_at DESC);