SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `EmpDeduction` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `EmpID` INT NOT NULL,
  `DeductionDate` DATE NOT NULL,
  `DeductionAmount` INT NOT NULL DEFAULT 0,
  `DeductionReason` VARCHAR(255) NOT NULL,
  `Notes` TEXT NULL,
  `EnteredByUserId` INT NOT NULL,
  `ApproverUserId` INT NULL,
  `IsApproved` TINYINT(1) 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_deduction_emp` (`EmpID`),
  KEY `idx_emp_deduction_date` (`DeductionDate`),
  KEY `idx_emp_deduction_approved` (`IsApproved`),
  KEY `idx_emp_deduction_entered` (`EnteredByUserId`),
  KEY `idx_emp_deduction_approver` (`ApproverUserId`),
  KEY `idx_emp_deduction_approved_by` (`ApprovedByUserId`),
  CONSTRAINT `fk_emp_deduction_emp` FOREIGN KEY (`EmpID`) REFERENCES `Emp` (`ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_emp_deduction_entered_by` FOREIGN KEY (`EnteredByUserId`) REFERENCES `users` (`id_user`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_emp_deduction_approver` FOREIGN KEY (`ApproverUserId`) REFERENCES `users` (`id_user`) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT `fk_emp_deduction_approved_by` FOREIGN KEY (`ApprovedByUserId`) REFERENCES `users` (`id_user`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;