ALTER TABLE `Employee_Master`
  ADD COLUMN IF NOT EXISTS `username` VARCHAR(128) NULL UNIQUE AFTER `last name`,
  ADD COLUMN IF NOT EXISTS `password_hash` VARCHAR(255) NULL AFTER `password`,
  MODIFY `role` ENUM('staff','trainer','hr','superadmin') NOT NULL DEFAULT 'staff',
  MODIFY `Entity` ENUM('TLT','ATN','CBS','DH','Shared','JZ') NULL;

INSERT INTO `Employee_Master` (`Employee ID`,`Name`,`First name`,`last name`,`username`,`password`,`password_hash`,`role`,`Entity`,`Company Email`,`status`)
VALUES (1001,'Super Admin','Super','Admin','superadmin',NULL,'$2y$10$eQzN3r4ca9n3j3PqTkhm/eYc7Zz5qfQyE2bR6j7KZ8n1pQyQ2Qm4W','superadmin','Shared','superadmin@example.com','active')
ON DUPLICATE KEY UPDATE `role`='superadmin';
INSERT INTO `Employee_Master` (`Employee ID`,`Name`,`First name`,`last name`,`username`,`password`,`password_hash`,`role`,`Entity`,`Company Email`,`status`)
VALUES (1002,'HR Manager','HR','Manager','hr',NULL,'$2y$10$eQzN3r4ca9n3j3PqTkhm/eYc7Zz5qfQyE2bR6j7KZ8n1pQyQ2Qm4W','hr','Shared','hr@example.com','active')
ON DUPLICATE KEY UPDATE `role`='hr';
INSERT INTO `Employee_Master` (`Employee ID`,`Name`,`First name`,`last name`,`username`,`password`,`password_hash`,`role`,`Entity`,`Company Email`,`status`)
VALUES (1003,'Trainer John','Trainer','John','trainer',NULL,'$2y$10$eQzN3r4ca9n3j3PqTkhm/eYc7Zz5qfQyE2bR6j7KZ8n1pQyQ2Qm4W','trainer','ATN','trainer@example.com','active')
ON DUPLICATE KEY UPDATE `role`='trainer';
INSERT INTO `Employee_Master` (`Employee ID`,`Name`,`First name`,`last name`,`username`,`password`,`password_hash`,`role`,`Entity`,`Company Email`,`status`)
VALUES (1004,'Staff Mary','Staff','Mary','staff',NULL,'$2y$10$eQzN3r4ca9n3j3PqTkhm/eYc7Zz5qfQyE2bR6j7KZ8n1pQyQ2Qm4W','staff','TLT','staff@example.com','active')
ON DUPLICATE KEY UPDATE `role`='staff';

-- Optional: AUTO_INCREMENT
-- ALTER TABLE `Employee_Master` MODIFY `Employee ID` INT NOT NULL AUTO_INCREMENT;