CREATE TABLE IF NOT EXISTS bank_account_transfers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  reference TEXT NOT NULL UNIQUE,
  company_id TEXT NOT NULL REFERENCES companies(id),
  source_bank_id INTEGER NOT NULL REFERENCES bank_accounts(id),
  destination_bank_id INTEGER NOT NULL REFERENCES bank_accounts(id),
  amount REAL NOT NULL,
  purpose TEXT NOT NULL,
  debit_date TEXT NOT NULL,
  reception_date TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'in_transit',
  debit_transaction_id INTEGER REFERENCES bank_transactions(id),
  credit_transaction_id INTEGER REFERENCES bank_transactions(id),
  created_at TEXT NOT NULL,
  received_at TEXT
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS idx_bank_account_transfers_status_reception ON bank_account_transfers(status, reception_date);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS idx_bank_account_transfers_company_debit ON bank_account_transfers(company_id, debit_date);