
-- ----------------------------
-- Table structure for collection_booking
-- ----------------------------
DROP TABLE IF EXISTS `collection_booking`;
CREATE TABLE `collection_booking`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `quot_id` int(0) NULL DEFAULT NULL,
  `sample_id` int(0) NULL DEFAULT NULL,
  `adrs_id` int(0) NULL DEFAULT NULL,
  `scheduled_collection_date` datetime(0) NULL DEFAULT NULL,
  `collector` int(0) NULL DEFAULT NULL,
  `collection_type` enum('On','Off') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'On' COMMENT 'On => On Premise, Off => Off Premise',
  `booking_remarks` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
  `collected_at` datetime(0) NULL DEFAULT NULL,
  `collection_remarks` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
  `barcode_generated_at` datetime(0) NULL DEFAULT NULL,
  `barcode_generated_by` int(0) NULL DEFAULT NULL,
  `off_premise_stat` enum('P','R','C','D') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'P => Pick up, R => Reached, C=> Collected, D => Drop to Lab',
  `picked_up_at` datetime(0) NULL DEFAULT NULL,
  `pick_up_by` int(0) NULL DEFAULT NULL,
  `reached_at` datetime(0) NULL DEFAULT NULL,
  `collected_by` int(0) NULL DEFAULT NULL,
  `drop_off_at` datetime(0) NULL DEFAULT NULL,
  `drop_off_by` int(0) NULL DEFAULT NULL,
  `sample_handed_to` int(0) NULL DEFAULT NULL COMMENT 'Sample handed to staff, by the collector',
  `sample_recvd_by` int(0) NULL DEFAULT NULL,
  `sample_recieved_at` datetime(0) NULL DEFAULT NULL,
  `status` enum('B','BG','PC','C','PA','A','PAS','AS','PG','G','PV','V') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'B' COMMENT 'B - Booked, BG- Barcode Generated, PC-Partially Collected, C- Collected, PA- Partially Acknowledge, A-Full Acknowledge, PAS- Partially Assigned, AS- Assigned, PG - Paritally Generated, G-Genrated, PV- Partially Verified, V- Verified',
  `created_at` datetime(0) NULL DEFAULT NULL,
  `created_by` int(0) NULL DEFAULT NULL,
  `updated_at` datetime(0) NULL DEFAULT NULL,
  `updated_by` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;



-- ----------------------------
-- Table structure for collection_booking_details
-- ----------------------------
DROP TABLE IF EXISTS `collection_booking_details`;
CREATE TABLE `collection_booking_details`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `collection_booking_id` int(0) NULL DEFAULT NULL COMMENT 'Collection_booking table id',
  `test_master_id` int(0) NULL DEFAULT NULL COMMENT 'test_master table id',
  `status` enum('B','A','G','V') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'B',
  `assigned_to` int(0) NULL DEFAULT NULL COMMENT 'Test conduct assigned to person (uses table id)',
  `created_at` datetime(0) NULL DEFAULT NULL,
  `created_by` int(0) NULL DEFAULT NULL,
  `updated_at` datetime(0) NULL DEFAULT NULL,
  `updated_by` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


-- ----------------------------
-- Table structure for collection_booking_department
-- ----------------------------
DROP TABLE IF EXISTS `collection_booking_department`;
CREATE TABLE `collection_booking_department`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `collection_booking_id` int(0) NULL DEFAULT NULL,
  `department_id` int(0) NULL DEFAULT NULL,
  `barcode_no` VARCHAR(40) NULL DEFAULT NULL,
  `barcode_unq_id` VARCHAR(40) NULL DEFAULT NULL,
  `sample_acknowledge_by` int(0) NULL DEFAULT NULL,
  `sample_acknowledge_at` datetime(0) NULL DEFAULT NULL,
  `sample_rejected_by` int NULL DEFAULT NULL,
  `sample_rejected_at` datetime NULL DEFAULT NULL,
  `sample_acknowledge_remarks` text NULL DEFAULT NULL,
  `sample_rejected_remarks` text NULL DEFAULT NULL,
  `status` enum('C','A','PG','G','R') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'C - Collected, A- Acknowledged, PG-Partially Generated, G- Generated, R - rejected',
  `created_at` datetime(0) NULL DEFAULT NULL,
  `created_by` int(0) NULL DEFAULT NULL,
  `updated_at` datetime(0) NULL DEFAULT NULL,
  `updated_by` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;


-- ----------------------------
-- Table structure for handover_approval_log
-- ----------------------------
DROP TABLE IF EXISTS `handover_approval_log`;
CREATE TABLE `handover_approval_log`  (
  `id` int(0) NOT NULL,
  `data_source` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'Event name (related to source table name)',
  `data_source_id` int(0) NULL DEFAULT NULL COMMENT 'source table id',
  `prev_emp_id` int(0) NULL DEFAULT NULL COMMENT 'Previous Staff Id',
  `current_emp_id` int(0) NULL DEFAULT NULL COMMENT 'Current Emp Id',
  `is_approve` enum('Y','N') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'status for handover approval',
  `created_at` datetime(0) NULL DEFAULT NULL,
  `created_by` int(0) NULL DEFAULT NULL,
  `approved_at` datetime(0) NULL DEFAULT NULL,
  `approved_by` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;