ALTER TABLE `lab_requisition` ADD INDEX( `sample_collected_by`, `analyzed_by`, `result_by`, `printed_by`);


DELETE FROM `users` WHERE facilityId !='966a89f6-05d8-4564-b319-2f8863821e75';
ALTER TABLE `users` DROP PRIMARY KEY, ADD PRIMARY KEY( `username`);
ALTER TABLE `users` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;


ALTER TABLE `lab_requisition` ADD CONSTRAINT `fk_result_by` FOREIGN KEY (`result_by`) REFERENCES `users`(`username`) ON DELETE RESTRICT ON UPDATE RESTRICT;


INSERT INTO `users` (`id`, `firstname`, `lastname`, `privilege`, `role`, `speciality`, `accessTo`, `functionalities`, `username`, `email`, `prefix`, `userType`, `serviceCost`, `licenceNo`, `placeOfWork`, `password`, `status`, `loggedIn`, `availableDays`, `availableFromTime`, `availableToTime`, `address`, `phone`, `paymentMethod`, `paymentAmount`, `referralId`, `lastLogin`, `image`, `createdAt`, `updatedAt`, `facilityId`, `createdBy`, `department`, `functionality`, `branch_name`, `signature_title`) VALUES ('740', '-', '-', '4', '-', '', 'Laboratory', NULL, 'mashood03', '', NULL, NULL, 'null', NULL, NULL, '$2a$10$nmtwiKcp4izIeZ.LQaMyvuajn5TISytZ4PYcjiOPbKqoq5.Svj4Wu', 'approved ', NULL, NULL, NULL, NULL, 'null', 'null', NULL, NULL, NULL, NULL, 'https://res.cloudinary.com/emaitee/image/upload/v1593618169/mylikita/profile_images/docAvater.png', '2021-10-04 15:41:56', '2021-10-04 19:46:32', '966a89f6-05d8-4564-b319-2f8863821e75', 'admin', 'Radiology', '-', NULL, '-');
INSERT INTO `users` (`id`, `firstname`, `lastname`, `privilege`, `role`, `speciality`, `accessTo`, `functionalities`, `username`, `email`, `prefix`, `userType`, `serviceCost`, `licenceNo`, `placeOfWork`, `password`, `status`, `loggedIn`, `availableDays`, `availableFromTime`, `availableToTime`, `address`, `phone`, `paymentMethod`, `paymentAmount`, `referralId`, `lastLogin`, `image`, `createdAt`, `updatedAt`, `facilityId`, `createdBy`, `department`, `functionality`, `branch_name`, `signature_title`) VALUES ('741', '-', '-', '4', '-', '', 'Laboratory', NULL, 'usman00', '', NULL, NULL, 'null', NULL, NULL, '$2a$10$nmtwiKcp4izIeZ.LQaMyvuajn5TISytZ4PYcjiOPbKqoq5.Svj4Wu', 'approved ', NULL, NULL, NULL, NULL, 'null', 'null', NULL, NULL, NULL, NULL, 'https://res.cloudinary.com/emaitee/image/upload/v1593618169/mylikita/profile_images/docAvater.png', '2021-10-04 15:41:56', '2021-10-04 19:46:32', '966a89f6-05d8-4564-b319-2f8863821e75', 'admin', 'Radiology', '-', NULL, '-');
INSERT INTO `users` (`id`, `firstname`, `lastname`, `privilege`, `role`, `speciality`, `accessTo`, `functionalities`, `username`, `email`, `prefix`, `userType`, `serviceCost`, `licenceNo`, `placeOfWork`, `password`, `status`, `loggedIn`, `availableDays`, `availableFromTime`, `availableToTime`, `address`, `phone`, `paymentMethod`, `paymentAmount`, `referralId`, `lastLogin`, `image`, `createdAt`, `updatedAt`, `facilityId`, `createdBy`, `department`, `functionality`, `branch_name`, `signature_title`) VALUES ('742', '-', '-', '4', '-', '', 'Laboratory', NULL, 'aliyu02', '', NULL, NULL, 'null', NULL, NULL, '$2a$10$nmtwiKcp4izIeZ.LQaMyvuajn5TISytZ4PYcjiOPbKqoq5.Svj4Wu', 'approved ', NULL, NULL, NULL, NULL, 'null', 'null', NULL, NULL, NULL, NULL, 'https://res.cloudinary.com/emaitee/image/upload/v1593618169/mylikita/profile_images/docAvater.png', '2021-10-04 15:41:56', '2021-10-04 19:46:32', '966a89f6-05d8-4564-b319-2f8863821e75', 'admin', 'Radiology', '-', NULL, '-');
INSERT INTO `users` (`id`, `firstname`, `lastname`, `privilege`, `role`, `speciality`, `accessTo`, `functionalities`, `username`, `email`, `prefix`, `userType`, `serviceCost`, `licenceNo`, `placeOfWork`, `password`, `status`, `loggedIn`, `availableDays`, `availableFromTime`, `availableToTime`, `address`, `phone`, `paymentMethod`, `paymentAmount`, `referralId`, `lastLogin`, `image`, `createdAt`, `updatedAt`, `facilityId`, `createdBy`, `department`, `functionality`, `branch_name`, `signature_title`) VALUES ('743', '-', '-', '4', '-', '', 'Laboratory', NULL, 'LAWAN Z', '', NULL, NULL, 'null', NULL, NULL, '$2a$10$nmtwiKcp4izIeZ.LQaMyvuajn5TISytZ4PYcjiOPbKqoq5.Svj4Wu', 'approved ', NULL, NULL, NULL, NULL, 'null', 'null', NULL, NULL, NULL, NULL, 'https://res.cloudinary.com/emaitee/image/upload/v1593618169/mylikita/profile_images/docAvater.png', '2021-10-04 15:41:56', '2021-10-04 19:46:32', '966a89f6-05d8-4564-b319-2f8863821e75', 'admin', 'Radiology', '-', NULL, '-');


ALTER TABLE `lab_requisition` ADD  CONSTRAINT `fk_sample_collected_by` FOREIGN KEY (`sample_collected_by`) REFERENCES `users`(`username`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `lab_requisition` ADD  CONSTRAINT `fk_analyzed_by` FOREIGN KEY (`analyzed_by`) REFERENCES `users`(`username`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `lab_requisition` ADD CONSTRAINT `fk_printed_by` FOREIGN KEY (`printed_by`) REFERENCES `users`(`username`) ON DELETE RESTRICT ON UPDATE RESTRICT;



ALTER TABLE `lab_requisition` ADD `doctor_fullname` VARCHAR(150) NULL AFTER `department_head`, ADD `doctor_signation` VARCHAR(30) NULL AFTER `doctor_fullname`;


-- update doctors name
UPDATE lab_requisition SET doctor_fullname=(SELECT concat(firstname,' ',lastname) FROM users WHERE username=lab_requisition.result_by) WHERE result_by is not null

-- update doctors signation
UPDATE lab_requisition SET doctor_signation=(SELECT signature_title FROM users WHERE username=lab_requisition.result_by) WHERE result_by is not null