CREATE TABLE IF NOT EXISTS cached_transactions (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  company_id        INT NOT NULL,
  txn_id            VARCHAR(50) NOT NULL,
  txn_type          VARCHAR(50) NOT NULL,
  txn_date          DATE NOT NULL,
  account_name      VARCHAR(200),
  account_id        VARCHAR(50),
  customer_name     VARCHAR(200),
  vendor_name       VARCHAR(200),
  description       TEXT,
  amount            DECIMAL(15,2) NOT NULL,
  class_name        VARCHAR(100),
  department_name   VARCHAR(100),
  memo              TEXT,
  fetched_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_txn (company_id, txn_id, account_id),
  INDEX idx_company_date (company_id, txn_date),
  INDEX idx_company_account (company_id, account_name),
  INDEX idx_company_type (company_id, txn_type),
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
