-- Wave 11: Bank Reconciliation Foundation
-- Bank Reconciliation Periods Table

CREATE TABLE IF NOT EXISTS bank_reconciliation_periods (
  id TEXT PRIMARY KEY,
  bank_account_id TEXT NOT NULL,
  period_start_date DATE NOT NULL,
  period_end_date DATE NOT NULL,
  statement_start_balance NUMERIC(19, 2),
  statement_end_balance NUMERIC(19, 2) NOT NULL,
  ledger_start_balance NUMERIC(19, 2),
  ledger_end_balance NUMERIC(19, 2),
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'in-progress', 'completed', 'locked')),
  locked_at TIMESTAMPTZ,
  locked_by TEXT,
  completed_at TIMESTAMPTZ,
  completed_by TEXT,
  reconciliation_difference NUMERIC(19, 4),
  approved_by TEXT,
  approved_at TIMESTAMPTZ,
  notes TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by TEXT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_recon_bank FOREIGN KEY (bank_account_id) REFERENCES banks(id),
  CONSTRAINT check_period_dates CHECK (period_end_date >= period_start_date),
  UNIQUE(bank_account_id, period_start_date, period_end_date)
);

CREATE INDEX IF NOT EXISTS idx_recon_periods_bank ON bank_reconciliation_periods(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_recon_periods_status ON bank_reconciliation_periods(status);
CREATE INDEX IF NOT EXISTS idx_recon_periods_date ON bank_reconciliation_periods(period_start_date, period_end_date);
CREATE INDEX IF NOT EXISTS idx_recon_periods_created ON bank_reconciliation_periods(created_at DESC);