CREATE TABLE IF NOT EXISTS bank_accounts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(120) NOT NULL,
  holder_name VARCHAR(190) NOT NULL,
  holder_street VARCHAR(190) NOT NULL,
  holder_building_no VARCHAR(20),
  holder_postal_code VARCHAR(20) NOT NULL,
  holder_city VARCHAR(120) NOT NULL,
  holder_country CHAR(2) NOT NULL DEFAULT 'CH',
  iban VARCHAR(34) NOT NULL,
  bank_name VARCHAR(190),
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_bank_accounts_active (is_active),
  INDEX idx_bank_accounts_default (is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE invoices
  ADD COLUMN bank_account_id INT UNSIGNED NULL AFTER intervention_id,
  ADD CONSTRAINT fk_invoices_bank_account FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE SET NULL;