-- Wave 11: Bank Reconciliation - Differences
-- Unmatched and Unresolved Items

CREATE TABLE IF NOT EXISTS bank_reconciliation_differences (
  id TEXT PRIMARY KEY,
  reconciliation_period_id TEXT NOT NULL,
  type TEXT NOT NULL
    CHECK (type IN (
      'unmatched_bank_transaction',
      'unmatched_ledger_transaction',
      'amount_mismatch',
      'date_variance',
      'outstanding_check',
      'deposit_in_transit',
      'bank_fee',
      'forex_difference',
      'other'
    )),
  amount NUMERIC(19, 2) NOT NULL,
  description TEXT,
  bank_transaction_id TEXT,
  ledger_transaction_id TEXT,
  resolution_type TEXT
    CHECK (resolution_type IS NULL OR resolution_type IN (
      'pending',
      'identified',
      'adjusted',
      'write_off',
      'reversed'
    )),
  resolution_date TIMESTAMPTZ,
  resolved_by TEXT,
  adjustment_entry_id TEXT,
  notes TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_diff_period FOREIGN KEY (reconciliation_period_id) 
    REFERENCES bank_reconciliation_periods(id) ON DELETE CASCADE,
  CONSTRAINT fk_diff_bank_txn FOREIGN KEY (bank_transaction_id) 
    REFERENCES bank_transactions(id),
  CONSTRAINT fk_diff_ledger_txn FOREIGN KEY (ledger_transaction_id) 
    REFERENCES ledger_transactions(id)
);

CREATE INDEX IF NOT EXISTS idx_diff_period ON bank_reconciliation_differences(reconciliation_period_id);
CREATE INDEX IF NOT EXISTS idx_diff_type ON bank_reconciliation_differences(type);
CREATE INDEX IF NOT EXISTS idx_diff_resolution ON bank_reconciliation_differences(resolution_type);
CREATE INDEX IF NOT EXISTS idx_diff_created ON bank_reconciliation_differences(created_at DESC);