
CREATE TABLE `tbldelete_staff_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `status` enum('pending','in_progress','completed','failed') NOT NULL DEFAULT 'pending',
  `type` enum('delete','migrate') DEFAULT 'delete',
  `tenant_id` bigint(20) unsigned NOT NULL,
  `staff_id` bigint(20) unsigned NOT NULL, 
  `migrate_to_staff_id` bigint(20) unsigned DEFAULT NULL, 
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phonenumber` varchar(30) DEFAULT NULL,
  `phonenumber_code` varchar(6) DEFAULT '+1',
  `added_on` datetime NOT NULL,
  `added_by` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tbldelete_staff_requests (STATUS, TYPE, tenant_id, staff_id, migrate_to_staff_id, firstname, lastname, email, phonenumber, phonenumber_code,added_on )
SELECT 'pending','migrate',tenant_id,staffid,(SELECT staffid FROM tblstaff WHERE tenant_id =s.tenant_id AND is_tenant = 1) AS migrate_to_staff_id,firstname,lastname,email,phonenumber,phonenumber_code,UTC_TIMESTAMP()
FROM tblstaff s WHERE is_deleted =1;

-- ALTER TABLE `tblstaff` DROP COLUMN `is_deleted`;


