-- ============================================================
-- Schema Update 3 — Cashbook + Bank Deals
-- ============================================================

CREATE TABLE IF NOT EXISTS `Cashbook` (
  `id`          INT NOT NULL AUTO_INCREMENT,
  `entry_date`  DATE NOT NULL,
  `type`        ENUM('income','expense') NOT NULL,
  `category`    VARCHAR(100) DEFAULT NULL,
  `description` VARCHAR(255) NOT NULL,
  `amount`      DECIMAL(12,2) NOT NULL,
  `reference`   VARCHAR(100) DEFAULT NULL,
  `created_by`  INT DEFAULT NULL,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_date` (`entry_date`),
  KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `BankDeals` (
  `id`          INT NOT NULL AUTO_INCREMENT,
  `deal_date`   DATE NOT NULL,
  `bank_name`   VARCHAR(100) DEFAULT NULL,
  `account_no`  VARCHAR(50) DEFAULT NULL,
  `description` VARCHAR(255) NOT NULL,
  `type`        ENUM('credit','debit') NOT NULL,
  `amount`      DECIMAL(12,2) NOT NULL,
  `reference`   VARCHAR(100) DEFAULT NULL,
  `status`      ENUM('pending','completed','failed') DEFAULT 'completed',
  `created_by`  INT DEFAULT NULL,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_date` (`deal_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
