CREATE TABLE IF NOT EXISTS `EmpBonus` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `EmpID` INT NOT NULL,
  `BonusDate` DATE NOT NULL,
  `BonusAmount` BIGINT NOT NULL DEFAULT 0,
  `Notes` TEXT NULL,
  `EnteredByUserId` INT NOT NULL,
  `IsApproved` TINYINT NOT NULL DEFAULT 0 COMMENT '0=pending,1=approved,2=rejected',
  `ApprovedByUserId` INT NULL,
  `ApprovedAt` DATETIME NULL,
  `CreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `idx_emp_bonus_emp` (`EmpID`),
  KEY `idx_emp_bonus_entered` (`EnteredByUserId`),
  KEY `idx_emp_bonus_approved` (`ApprovedByUserId`),
  KEY `idx_emp_bonus_status_date` (`IsApproved`, `BonusDate`),
  CONSTRAINT `fk_emp_bonus_emp` FOREIGN KEY (`EmpID`) REFERENCES `Emp`(`ID`) ON DELETE CASCADE,
  CONSTRAINT `fk_emp_bonus_entered_user` FOREIGN KEY (`EnteredByUserId`) REFERENCES `users`(`id_user`) ON DELETE RESTRICT,
  CONSTRAINT `fk_emp_bonus_approved_user` FOREIGN KEY (`ApprovedByUserId`) REFERENCES `users`(`id_user`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
