CREATE TABLE IF NOT EXISTS `fire_equipment_status_history` (
  `id` int NOT NULL AUTO_INCREMENT,
  `equipment_id` int DEFAULT NULL,
  `updated_date` date DEFAULT NULL,
  `comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `reported_by` int DEFAULT NULL,
  `notify_to` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `maintenance_inspection_id` int DEFAULT NULL,
  `changed_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `message` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `action` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `equipment_id` (`equipment_id`),
  KEY `department` (`department`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `fire_equipment_status_history_ibfk_131` FOREIGN KEY (`equipment_id`) REFERENCES `fire_equipment_register` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_status_history_ibfk_132` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_status_history_ibfk_133` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_status_history_ibfk_134` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_status_history_ibfk_135` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: first_aid_box
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `first_aid_box` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content_list` int DEFAULT NULL,
  `location` int DEFAULT NULL,
  `last_inspection_date` date DEFAULT NULL,
  `next_inspection_date` date DEFAULT NULL,
  `status` enum('Compliant', 'Non-compliant') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Compliant',
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inspection_outcome` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `investigation_outcome` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `maintenance_frequency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `maintenance_provider` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ogdptype` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `root_cause` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `immediate_cause` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `inspection_checklist` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `schedule` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `value` int DEFAULT NULL,
  `inspection_date` date DEFAULT NULL,
  `scheduled_status` enum('Pending', 'Scheduled') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending',
  `action_taken` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `location` (`location`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `first_aid_box_ibfk_25` FOREIGN KEY (`location`) REFERENCES `location` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `first_aid_box_ibfk_26` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `first_aid_box_ibfk_27` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `first_aid_box_ibfk_28` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: first_aid_box_content
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `first_aid_box_content` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_first_aid_box_content_department` (`department`),
  KEY `fk_first_aid_box_content_organization` (`organization`),
  KEY `fk_first_aid_box_content_created_by` (`created_by`),
  KEY `fk_first_aid_box_content_updated_by` (`updated_by`),
  CONSTRAINT `first_aid_box_content_ibfk_25` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `first_aid_box_content_ibfk_26` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `first_aid_box_content_ibfk_27` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `first_aid_box_content_ibfk_28` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: flammable_store
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `flammable_store` (
  `id` int NOT NULL AUTO_INCREMENT,
  `capacity` int DEFAULT NULL,
  `location` int DEFAULT NULL,
  `permit_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Select Permit',
  `last_inspection_date` date DEFAULT NULL,
  `next_inspection_date` date DEFAULT NULL,
  `status` enum('Compliant', 'Non-compliant') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Compliant',
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` tinyint(1) DEFAULT '0',
  `equipment_type` int DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inspection_outcome` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `investigation_outcome` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `maintenance_frequency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `maintenance_provider` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ogdptype` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `root_cause` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `immediate_cause` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `inspection_checklist` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `schedule` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `action_taken` int DEFAULT NULL,
  `value` int DEFAULT NULL,
  `content_list` int DEFAULT NULL,
  `inspection_date` date DEFAULT NULL,
  `scheduled_status` enum('Pending', 'Scheduled') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending',
  PRIMARY KEY (`id`),
  KEY `location` (`location`),
  KEY `department` (`department`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `flammable_store_ibfk_31` FOREIGN KEY (`location`) REFERENCES `location` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `flammable_store_ibfk_32` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `flammable_store_ibfk_33` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `flammable_store_ibfk_34` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `flammable_store_ibfk_35` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: focus_area
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `focus_area` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_static` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `focus_area_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `focus_area_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 7 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: gender
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `gender` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `gender_ibfk_37` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `gender_ibfk_38` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `gender_ibfk_39` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: hazmat_fire_technical_form
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `hazmat_fire_technical_form` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int DEFAULT NULL,
  `table_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `type_of_occurrence` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `alcohol_testing` int DEFAULT NULL,
  `substance_testing` int DEFAULT NULL,
  `none_testing` int DEFAULT NULL,
  `state_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `specify_tested_by_who` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `location_of_occurrence` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_occurrence` date DEFAULT NULL,
  `time_of_occurrence` time DEFAULT NULL,
  `occurrence_ref_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `immediate_cause_of_occurrence` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `involved_employee_details_name` int DEFAULT NULL,
  `involved_employee_details_employee_sap` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `involved_employee_details_employee_type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `involved_employee_details_experience` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `involved_employee_details_role` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `was_alcohol_or_substance_testing_done` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `select_substance_testing` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tested_by_who` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `substance_test_results` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `method_of_testing` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `was_person_or_operator_licensed_trained` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `was_license_training_applicable_to_task` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `license_training_expiry_date` date DEFAULT NULL,
  `license_no_training_certificate_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `issued_by` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `occurrence_level` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `was_case_reported_to_authority_regulator` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name_of_authority` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_reported_to_authority` date DEFAULT NULL,
  `reported_by_who` int DEFAULT NULL,
  `estimated_cost_medical_expenses` decimal(10, 2) DEFAULT NULL,
  `comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `form_completed_by` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `designation` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `signature` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date` date DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `save_type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tested_by_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tested_by_employee` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `specify_method_of_testing` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `for_contractor` int DEFAULT '0',
  `tested_is_contractor` int DEFAULT NULL,
  `tested_contractor_id` int DEFAULT NULL,
  `tested_project_id` int DEFAULT NULL,
  `year_issued` date DEFAULT NULL,
  `category_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `involved_employee_details_name` (`involved_employee_details_name`),
  KEY `reported_by_who` (`reported_by_who`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `incident_id` (`incident_id`),
  KEY `hazmat_fire_technical_form_category_id_foreign_idx` (`category_id`),
  CONSTRAINT `hazmat_fire_technical_form_ibfk_105` FOREIGN KEY (`incident_id`) REFERENCES `incident` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hazmat_fire_technical_form_ibfk_106` FOREIGN KEY (`involved_employee_details_name`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hazmat_fire_technical_form_ibfk_107` FOREIGN KEY (`reported_by_who`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hazmat_fire_technical_form_ibfk_108` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hazmat_fire_technical_form_ibfk_109` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hazmat_fire_technical_form_ibfk_110` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hazmat_fire_technical_form_ibfk_111` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hazmat_fire_technical_form_ibfk_112` FOREIGN KEY (`category_id`) REFERENCES `incident_category` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: highest_qualification
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `highest_qualification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `highest_qualification_ibfk_79` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `highest_qualification_ibfk_80` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `highest_qualification_ibfk_81` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: hospital
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `hospital` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `province` varchar(255) DEFAULT NULL,
  `created_by` int NOT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` int DEFAULT '0',
  `is_static` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `hospital_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `hospital_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 529 DEFAULT CHARSET = latin1;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: hse_appointment
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `hse_appointment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `appoints_user_id` int DEFAULT NULL COMMENT 'it will be appoints user id',
  `physical_location` int DEFAULT NULL,
  `appointed_user_id` int DEFAULT NULL COMMENT 'it will be appointed user id',
  `name_of_appointment` int DEFAULT NULL,
  `effective_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `appointer_ddrm_id` int DEFAULT NULL,
  `appointed_ddrm_id` int DEFAULT NULL,
  `signature_type` enum('electronic', 'manual') DEFAULT 'manual',
  `ddrm_id` int DEFAULT NULL,
  `unique_id` varchar(255) DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `agreement_id` varchar(255) DEFAULT NULL,
  `agreement_status` varchar(255) DEFAULT NULL,
  `library_document_id` varchar(255) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `appoints_user_id` (`appoints_user_id`),
  KEY `appointed_user_id` (`appointed_user_id`),
  KEY `name_of_appointment` (`name_of_appointment`),
  KEY `appointer_ddrm_id` (`appointer_ddrm_id`),
  KEY `appointed_ddrm_id` (`appointed_ddrm_id`),
  KEY `ddrm_id` (`ddrm_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `hse_appointment_ibfk_129` FOREIGN KEY (`appoints_user_id`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hse_appointment_ibfk_130` FOREIGN KEY (`appointed_user_id`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hse_appointment_ibfk_131` FOREIGN KEY (`name_of_appointment`) REFERENCES `appointment_type` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hse_appointment_ibfk_132` FOREIGN KEY (`appointer_ddrm_id`) REFERENCES `repository` (`id`),
  CONSTRAINT `hse_appointment_ibfk_133` FOREIGN KEY (`appointed_ddrm_id`) REFERENCES `repository` (`id`),
  CONSTRAINT `hse_appointment_ibfk_134` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`),
  CONSTRAINT `hse_appointment_ibfk_135` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hse_appointment_ibfk_136` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hse_appointment_ibfk_137` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `hse_appointment_ibfk_138` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: illness_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `illness_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `specify` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `parent` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `department` int DEFAULT NULL,
  `is_static` int DEFAULT '0',
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `illness_type_ibfk_61` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `illness_type_ibfk_62` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `illness_type_ibfk_63` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 74 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: incident
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `incident` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_category` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `initial_incident_severity` int DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `current_impact` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `potential_impact` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `person_type` enum('Internal', 'External', 'Both') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Internal',
  `internal_persons` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `incident_reporting_by` int DEFAULT NULL,
  `notification` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `save_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contractor` int DEFAULT NULL,
  `was_the_person_licensed` tinyint(1) DEFAULT NULL,
  `permit_license` int DEFAULT NULL,
  `incident_reporting_date` date DEFAULT NULL,
  `incident_actual_date` date DEFAULT NULL,
  `incident_actual_time` time DEFAULT NULL,
  `specify` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `external_persons` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_done` int NOT NULL DEFAULT '0',
  `incident_location` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `for_contractor` int DEFAULT '0',
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `incident_reporting_by` (`incident_reporting_by`),
  KEY `contractor` (`contractor`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `permit_license` (`permit_license`),
  KEY `initial_incident_severity` (`initial_incident_severity`),
  CONSTRAINT `incident_ibfk_105` FOREIGN KEY (`initial_incident_severity`) REFERENCES `severity` (`id`),
  CONSTRAINT `incident_ibfk_106` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_ibfk_107` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_ibfk_108` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `incident_ibfk_109` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`),
  CONSTRAINT `incident_ibfk_110` FOREIGN KEY (`incident_reporting_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_ibfk_111` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_ibfk_112` FOREIGN KEY (`permit_license`) REFERENCES `permit_license_compliance` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: incident_category
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `incident_category` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `is_static` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `department` (`department`),
  CONSTRAINT `incident_category_ibfk_70` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_category_ibfk_71` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_category_ibfk_72` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 37 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: incident_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `incident_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `incident_id` int DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `file_classification` int DEFAULT NULL,
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `incident_id` (`incident_id`),
  KEY `file_classification` (`file_classification`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `incident_docs_ibfk_40` FOREIGN KEY (`incident_id`) REFERENCES `incident` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_docs_ibfk_41` FOREIGN KEY (`file_classification`) REFERENCES `file_classification` (`id`),
  CONSTRAINT `incident_docs_ibfk_42` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: incident_location
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `incident_location` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `is_static` int DEFAULT '0',
  `parent` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `incident_location_ibfk_58` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_location_ibfk_59` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_location_ibfk_60` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1083 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: incident_ppe
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `incident_ppe` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `parent` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_static` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `incident_ppe_ibfk_64` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_ppe_ibfk_65` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_ppe_ibfk_66` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 166 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: incident_role
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `incident_role` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `incident_role_ibfk_13` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_role_ibfk_14` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `incident_role_ibfk_15` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: infrastructure_technical_form
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `infrastructure_technical_form` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int DEFAULT NULL,
  `department` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `save_type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `asset_details` json DEFAULT NULL,
  `asset_damage_details` json DEFAULT NULL,
  `form_submitted_by` int DEFAULT NULL,
  `incident_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_date` date DEFAULT NULL,
  `incident_time` time DEFAULT NULL,
  `incident_severity` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_category` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_location` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `asset_type` json DEFAULT NULL,
  `date_of_submission` date DEFAULT NULL,
  `notification` json DEFAULT NULL,
  `for_contractor` int DEFAULT '0',
  `role_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `incident_id` (`incident_id`),
  KEY `department` (`department`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `form_submitted_by` (`form_submitted_by`),
  CONSTRAINT `infrastructure_technical_form_ibfk_66` FOREIGN KEY (`incident_id`) REFERENCES `incident` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `infrastructure_technical_form_ibfk_67` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `infrastructure_technical_form_ibfk_68` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `infrastructure_technical_form_ibfk_69` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `infrastructure_technical_form_ibfk_70` FOREIGN KEY (`form_submitted_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: injury_on_duty
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `injury_on_duty` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int DEFAULT NULL,
  `organization` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `department` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `save_type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `injured_persons` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `date_of_submission` date DEFAULT NULL,
  `notification` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_date` date DEFAULT NULL,
  `incident_time` time DEFAULT NULL,
  `incident_severity` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_category` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_location` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `technical_form_completed_by` int DEFAULT NULL,
  `role_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `for_contractor` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `incident_id` (`incident_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `technical_form_completed_by` (`technical_form_completed_by`),
  CONSTRAINT `injury_on_duty_ibfk_53` FOREIGN KEY (`incident_id`) REFERENCES `incident` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `injury_on_duty_ibfk_54` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `injury_on_duty_ibfk_55` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `injury_on_duty_ibfk_56` FOREIGN KEY (`technical_form_completed_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: inspection_location
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `inspection_location` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `inspection_location_ibfk_17` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `inspection_location_ibfk_18` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `inspection_location_ibfk_19` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `inspection_location_ibfk_20` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: insurance_claim
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `insurance_claim` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_report_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_category` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `date_of_incident` date DEFAULT NULL,
  `time_of_incident_occurrence` time DEFAULT NULL,
  `initial_incident_severity` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `location` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `insurance_policy_impact` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `estimated_damage_cost` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `claim_number` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `actual_damage_cost` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `insurance_claim_approval_status` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending',
  `insurance_claim_payout_status` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending',
  `comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `department` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `investigation_id` int DEFAULT NULL,
  `internal_insurance_claim_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `insurance_approved_amount` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `insurance_approval_date` datetime DEFAULT NULL,
  `reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `appeal` int DEFAULT NULL,
  `responsible_person` int DEFAULT NULL,
  `actual_payout_amount` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `insurance_claim_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `insurance_claim_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: insurance_claim_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `insurance_claim_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `insurance_claim_id` int DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `insurance_claim_id` (`insurance_claim_id`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `insurance_claim_docs_ibfk_27` FOREIGN KEY (`insurance_claim_id`) REFERENCES `insurance_claim` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `insurance_claim_docs_ibfk_28` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: insurance_claim_logs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `insurance_claim_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `insurance_claim_id` int DEFAULT NULL,
  `comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `insurance_claim_id` (`insurance_claim_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `insurance_claim_logs_ibfk_25` FOREIGN KEY (`insurance_claim_id`) REFERENCES `insurance_claim` (`id`),
  CONSTRAINT `insurance_claim_logs_ibfk_26` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `insurance_claim_logs_ibfk_27` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: insurance_claims_approval
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `insurance_claims_approval` (
  `id` int NOT NULL AUTO_INCREMENT,
  `assigned_reviewers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deadline_review` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assigned_assessors` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deadline_assessment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assigned_verifiers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deadline_verification` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assigned_approvers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deadline_approval` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: insurance_claims_submission
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `insurance_claims_submission` (
  `id` int NOT NULL AUTO_INCREMENT,
  `claimant_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `claimant_phone_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `claimant_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `claimant_company` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_incident` date DEFAULT NULL,
  `time_of_incident` time DEFAULT NULL,
  `location_of_incident` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_report` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `witness_statements` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `regulatory_guidance` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `other_documents` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `insurance_policy` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type_of_coverage` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `estimated_value` float DEFAULT NULL,
  `witness_information` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `loss_assessment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `by_checking` int DEFAULT '0',
  `additional_comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: insurance_policy_impact
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `insurance_policy_impact` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `insurance_policy_impact_ibfk_40` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `insurance_policy_impact_ibfk_41` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `insurance_policy_impact_ibfk_42` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: investigation
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `investigation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `environment_consequence_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assets_damage_assets_property_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `line_manager_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` int DEFAULT '0',
  `updated_by` int DEFAULT NULL,
  `incident_id` int DEFAULT NULL,
  `is_done` int NOT NULL DEFAULT '0',
  `incident_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_date` date DEFAULT NULL,
  `incident_time` time DEFAULT NULL,
  `initial_incident_severity` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_category` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_location` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `investigation_team_details` enum('Internal', 'External') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employee` int DEFAULT NULL,
  `role_in_incident` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `investigator_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `specialty` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization_external` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `investigation_start_date` date DEFAULT NULL,
  `investigation_end_date` date DEFAULT NULL,
  `actual_incident_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `actual_incident_severity` int DEFAULT NULL,
  `investigation_enquiry_outcome` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `drm_id` int DEFAULT NULL,
  `has_similar_incident_occurred_before` tinyint(1) DEFAULT '0',
  `similar_incidents` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `actual_incident_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `reportable` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `root_cause` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `root_cause_validation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `root_cause_prioritization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `root_cause_selection` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `recommended_actions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `investigation_form_submitted_by` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `report_date` date DEFAULT NULL,
  `date_of_submission` date DEFAULT NULL,
  `notify` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `checkpoint_post_investigation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `recall_required` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `applicable_sop_created` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `affected_employees_retrained` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `investigation_outcome_communicated` tinyint(1) DEFAULT NULL,
  `recommended_can_be_applied` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `role_in_incident_external` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `asset_damage_details` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `estimated_cost_damage` decimal(10, 2) DEFAULT NULL,
  `assets_value` decimal(10, 2) DEFAULT NULL,
  `assets_currency` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assets_vehicle_incident_driver_details` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `assets_vehicle_details` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `assets_vehicle_incident_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quality_incident_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quantity_affected` int DEFAULT NULL,
  `customer_impact` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `product_impact` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `estimated_financial_impact` decimal(10, 2) DEFAULT NULL,
  `quality_value` decimal(10, 2) DEFAULT NULL,
  `quality_currency` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quality_vehicle_incident_driver_details` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `quality_vehicle_details` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `quality_vehicle_incident_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `origin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `size_area_affected` decimal(10, 2) DEFAULT NULL,
  `injury_classification` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `part_body_affected` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reportable_classification` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `hospitalized` tinyint(1) DEFAULT NULL,
  `exposed_person` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `diseases_illnesses_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_diagnosed` date DEFAULT NULL,
  `exposure_route` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `security_incident_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `any_arrests` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `any_recoveries` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `security_value` decimal(10, 2) DEFAULT NULL,
  `security_currency` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `initialRootCauses` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `fishbone_categories` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `likelihood_occurrence` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `impact_severity` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `risk_score` int DEFAULT NULL,
  `final_root_cause` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `final_recommendations` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `investigation_submitted` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `submission_date` date DEFAULT NULL,
  `initial_standardize` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `investigation_standardise` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `investigation_root_cause` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `injured_persons` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `quality_incident_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_type` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `occupationals` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `vehicle_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `asset_type` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `type_of_security_incident` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `financial_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `estimated_currency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `evidence_collected` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `upload_document` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` enum('Draft', 'Complete') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Draft',
  `investigation_ddrm_id` int DEFAULT NULL,
  `signature_type` enum('Electronic', 'Manual') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Manual',
  `actual_incident_category` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `specify` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_persons_involved` json DEFAULT NULL,
  `root_cause_methodology` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `investigation_enquiry_details` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_investigation_department` (`department`),
  KEY `fk_investigation_organization` (`organization`),
  KEY `fk_investigation_created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `incident_id` (`incident_id`),
  KEY `employee` (`employee`),
  CONSTRAINT `investigation_ibfk_100` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `investigation_ibfk_101` FOREIGN KEY (`incident_id`) REFERENCES `incident` (`id`),
  CONSTRAINT `investigation_ibfk_102` FOREIGN KEY (`employee`) REFERENCES `users` (`id`),
  CONSTRAINT `investigation_ibfk_97` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `investigation_ibfk_98` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `investigation_ibfk_99` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: investigation_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `investigation_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `investigation_id` int DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `investigation_id` (`investigation_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: issuing_authority
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `issuing_authority` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_static` tinyint NOT NULL DEFAULT '0',
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `department` (`department`),
  CONSTRAINT `issuing_authority_ibfk_70` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `issuing_authority_ibfk_71` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `issuing_authority_ibfk_72` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 28 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: knowledge_comment
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `knowledge_comment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `knowledge_recording_id` int DEFAULT NULL,
  `comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `steps` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `reject_reason` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `comment_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` enum('approved', 'rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `knowledge_recording_id` (`knowledge_recording_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `knowledge_comment_ibfk_31` FOREIGN KEY (`knowledge_recording_id`) REFERENCES `knowledge_management_recording` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `knowledge_comment_ibfk_32` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `knowledge_comment_ibfk_33` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: knowledge_management_recording
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `knowledge_management_recording` (
  `id` int NOT NULL AUTO_INCREMENT,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `knowledge_management_type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lesson_best_practice_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `category` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `author` int DEFAULT NULL,
  `date_of_submission` date DEFAULT NULL,
  `recommendations` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `key_insights` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `keywords` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `implementation_steps` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `source` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_classification` int DEFAULT NULL,
  `reviewer` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `stakeholder` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `approver` int DEFAULT NULL,
  `current_status` enum('reviewing', 'approval', 'approved', 'rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'reviewing',
  `status` enum('complete') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'complete',
  `ddrm_id` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `author` (`author`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `file_classification` (`file_classification`),
  KEY `approver` (`approver`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `knowledge_management_recording_ibfk_81` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `knowledge_management_recording_ibfk_82` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `knowledge_management_recording_ibfk_83` FOREIGN KEY (`author`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `knowledge_management_recording_ibfk_84` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `knowledge_management_recording_ibfk_85` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `knowledge_management_recording_ibfk_86` FOREIGN KEY (`file_classification`) REFERENCES `file_classification` (`id`),
  CONSTRAINT `knowledge_management_recording_ibfk_87` FOREIGN KEY (`approver`) REFERENCES `users` (`id`),
  CONSTRAINT `knowledge_management_recording_ibfk_88` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: labels
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `labels` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `labels_ibfk_76` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `labels_ibfk_77` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `labels_ibfk_78` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: language
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `language` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `language_ibfk_25` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `language_ibfk_26` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `language_ibfk_27` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: legislation
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `legislation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `she_legislation` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `legislation_ibfk_61` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `legislation_ibfk_62` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `legislation_ibfk_63` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `legislation_ibfk_64` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: legislation_doc
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `legislation_doc` (
  `id` int NOT NULL AUTO_INCREMENT,
  `legislation_id` int NOT NULL,
  `old_version` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `new_version` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_legislation_doc_organization` (`organization`),
  KEY `fk_legislation_doc_department` (`department`),
  KEY `legislation_id` (`legislation_id`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `legislation_doc_ibfk_46` FOREIGN KEY (`legislation_id`) REFERENCES `legislation` (`id`),
  CONSTRAINT `legislation_doc_ibfk_47` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`),
  CONSTRAINT `legislation_doc_ibfk_48` FOREIGN KEY (`department`) REFERENCES `department` (`id`),
  CONSTRAINT `legislation_doc_ibfk_49` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: license
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `license` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_static` int DEFAULT '0',
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `license_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `license_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 123 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: location
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `location` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `description` longtext,
  `department` int DEFAULT NULL,
  `organization` longtext,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) DEFAULT NULL,
  `is_static` int DEFAULT '0',
  `parent` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_location_created_by` (`created_by`),
  KEY `fk_location_department` (`department`),
  KEY `fk_location_updated_by` (`updated_by`),
  CONSTRAINT `location_ibfk_79` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `location_ibfk_80` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `location_ibfk_81` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1083 DEFAULT CHARSET = latin1;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: log_history
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `log_history` (
  `id` int NOT NULL AUTO_INCREMENT,
  `table_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `record_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `action` enum('INSERT', 'UPDATE', 'DELETE') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `changed_fields` json DEFAULT NULL,
  `changed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: main_process
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `main_process` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `description` longtext,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `organization` longtext,
  `unique_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `department` (`department`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `main_process_ibfk_4` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `main_process_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `main_process_ibfk_6` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: material_classification
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `material_classification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `material_classification_ibfk_25` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `material_classification_ibfk_26` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `material_classification_ibfk_27` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: measurement_metric
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `measurement_metric` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_static` tinyint NOT NULL DEFAULT '0',
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `measurement_metric_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `measurement_metric_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: meeting
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `meeting` (
  `id` int NOT NULL AUTO_INCREMENT,
  `meeting_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meeting_title` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meeting_hierarchy` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `participants` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `meeting_owner` int DEFAULT NULL,
  `planned_meeting_date_from` datetime DEFAULT NULL,
  `planned_meeting_date_to` datetime DEFAULT NULL,
  `venue` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `agenda` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `record` int DEFAULT NULL,
  `linked_table` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `meeting_owner` (`meeting_owner`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `meeting_ibfk_79` FOREIGN KEY (`meeting_owner`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `meeting_ibfk_80` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `meeting_ibfk_81` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `meeting_ibfk_82` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `meeting_ibfk_83` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: meeting_notes
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `meeting_notes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `meeting_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meeting_recording_id` int DEFAULT NULL,
  `agenda_item` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meeting_discussion_name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `meeting_discussion` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `action_discussion_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meeting_action_items` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meeting_action_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `assigned_to` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `raised_by` int DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `notes_time` time DEFAULT NULL,
  `meeting_action_progress` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `completed_date` date DEFAULT NULL,
  `actions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `discussion_have_action` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `raised_by` (`raised_by`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `meeting_recording_id` (`meeting_recording_id`),
  CONSTRAINT `meeting_notes_ibfk_105` FOREIGN KEY (`meeting_recording_id`) REFERENCES `meeting_recording` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `meeting_notes_ibfk_106` FOREIGN KEY (`raised_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `meeting_notes_ibfk_107` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `meeting_notes_ibfk_108` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: meeting_recording
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `meeting_recording` (
  `id` int NOT NULL AUTO_INCREMENT,
  `scheduled_meeting` int DEFAULT NULL,
  `actual_meeting_date_from` datetime DEFAULT NULL,
  `actual_meeting_date_to` datetime DEFAULT NULL,
  `meeting_duration` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meeting_scriber` int DEFAULT NULL,
  `participants` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` enum('New', 'Pending', 'Approved', 'Rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'New',
  `comment` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `external_guest_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `external_meeting_guest` int DEFAULT '0',
  `discussion_have_action` int DEFAULT '0',
  `meeting_notes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `scheduled_meeting` (`scheduled_meeting`),
  KEY `meeting_scriber` (`meeting_scriber`),
  KEY `created_by` (`created_by`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `meeting_recording_ibfk_157` FOREIGN KEY (`scheduled_meeting`) REFERENCES `meeting` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `meeting_recording_ibfk_158` FOREIGN KEY (`meeting_scriber`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `meeting_recording_ibfk_159` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `meeting_recording_ibfk_160` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `meeting_recording_ibfk_161` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `meeting_recording_ibfk_162` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: name_of_standard
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `name_of_standard` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `name_of_standard_ibfk_31` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `name_of_standard_ibfk_32` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `name_of_standard_ibfk_33` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: ncr_recording
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `ncr_recording` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ncr_title` varchar(255) DEFAULT NULL,
  `corrective_action_been_raised` int DEFAULT '0',
  `preventative_action_been_raised` int DEFAULT '0',
  `organization` int DEFAULT NULL,
  `raised_by` int DEFAULT NULL,
  `raised_by_employee_no` varchar(255) DEFAULT NULL,
  `raised_by_contact` varchar(255) DEFAULT NULL,
  `ncr_description` longtext,
  `type_of_work` longtext,
  `immediate_action_taken` longtext,
  `legal_non_compliance` varchar(255) DEFAULT NULL,
  `system_non_conformance` varchar(255) DEFAULT NULL,
  `customer_non_compliance` varchar(255) DEFAULT NULL,
  `name_of_legislation` varchar(255) DEFAULT NULL,
  `name_of_standard` int DEFAULT NULL,
  `customer_name` varchar(255) DEFAULT NULL,
  `provision_of_the_requirement` varchar(255) DEFAULT NULL,
  `standard_clause_name` varchar(255) DEFAULT NULL,
  `contravened_contract_clause` varchar(255) DEFAULT NULL,
  `origin_ncr` varchar(255) DEFAULT NULL,
  `specify_origin_ncr` varchar(255) DEFAULT NULL,
  `type_ncr` varchar(255) DEFAULT NULL,
  `specify_type_ncr` varchar(255) DEFAULT NULL,
  `ncr_categories` longtext,
  `classification` varchar(255) DEFAULT NULL,
  `raised_type` int DEFAULT NULL,
  `internal_raised_business` int DEFAULT NULL,
  `unique_id` varchar(250) DEFAULT NULL,
  `reference_no` varchar(250) DEFAULT NULL,
  `internal_raised_against_business` int DEFAULT NULL,
  `internal_raised_against_department` int DEFAULT NULL,
  `internal_name_of_site` varchar(255) DEFAULT NULL,
  `internal_location` varchar(255) DEFAULT NULL,
  `internal_responsible_person` int DEFAULT NULL,
  `internal_employee_no` varchar(255) DEFAULT NULL,
  `internal_date_raised` date DEFAULT NULL,
  `external_raised_person` varchar(255) DEFAULT NULL,
  `external_contact_details` varchar(255) DEFAULT NULL,
  `external_date_raised` date DEFAULT NULL,
  `external_due_date_resolve_ncr` date DEFAULT NULL,
  `external_responsible_person` varchar(255) DEFAULT NULL,
  `corrective_action_title` longtext,
  `corrective_action_description` longtext,
  `corrective_responsible_department` int DEFAULT NULL,
  `corrective_responsible_person` int DEFAULT NULL,
  `corrective_start_date` date DEFAULT NULL,
  `corrective_due_date` date DEFAULT NULL,
  `corrective_action_progress` varchar(255) DEFAULT NULL,
  `corrective_status` varchar(255) DEFAULT NULL,
  `preventative_action_title` varchar(255) DEFAULT NULL,
  `preventative_action_description` longtext,
  `preventative_responsible_department` int DEFAULT NULL,
  `preventative_responsible_person` int DEFAULT NULL,
  `preventative_start_date` date DEFAULT NULL,
  `preventative_due_date` date DEFAULT NULL,
  `preventative_action_progress` varchar(255) DEFAULT NULL,
  `preventative_status` varchar(255) DEFAULT NULL,
  `root_cause` longtext,
  `human_resource_requirements` longtext,
  `technological_resources` longtext,
  `type_of_resource` varchar(255) DEFAULT NULL,
  `amount` float DEFAULT NULL,
  `necessary_corrective` int DEFAULT NULL,
  `new_risk` varchar(255) DEFAULT NULL,
  `problem_been_removed` varchar(255) DEFAULT NULL,
  `stop_certificate_used` int DEFAULT NULL,
  `comments` longtext,
  `stop_certificate_id` varchar(255) DEFAULT NULL,
  `requirements_contravened` varchar(255) DEFAULT NULL,
  `condition_of_stop_certificate` varchar(255) DEFAULT NULL,
  `recipient_user_id` int DEFAULT NULL,
  `recipient_role` varchar(255) DEFAULT NULL,
  `recipient_employee_number` varchar(255) DEFAULT NULL,
  `recipient_date_received` date DEFAULT NULL,
  `issuer_user_id` int DEFAULT NULL,
  `issuer_designation` varchar(255) DEFAULT NULL,
  `issuer_contact_number` varchar(255) DEFAULT NULL,
  `issuer_date` date DEFAULT NULL,
  `issuer_time` time DEFAULT NULL,
  `date_lifted` date DEFAULT NULL,
  `compliance_to_mitigation` varchar(255) DEFAULT NULL,
  `lifting_time` time DEFAULT NULL,
  `signature_type` enum('Electronic', 'Manual') NOT NULL DEFAULT 'Manual',
  `stop_certificate_ddrm_id` int DEFAULT NULL,
  `ncr_report_ddrm_id` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `currency` int DEFAULT NULL,
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `corrective_responsible_department` (`corrective_responsible_department`),
  KEY `corrective_responsible_person` (`corrective_responsible_person`),
  KEY `created_by` (`created_by`),
  KEY `internal_raised_against_business` (`internal_raised_against_business`),
  KEY `internal_raised_against_department` (`internal_raised_against_department`),
  KEY `internal_raised_business` (`internal_raised_business`),
  KEY `internal_responsible_person` (`internal_responsible_person`),
  KEY `name_of_standard` (`name_of_standard`),
  KEY `organization` (`organization`),
  KEY `preventative_responsible_department` (`preventative_responsible_department`),
  KEY `preventative_responsible_person` (`preventative_responsible_person`),
  KEY `raised_by` (`raised_by`),
  KEY `updated_by` (`updated_by`),
  KEY `ncr_recording_currency_foreign_idx` (`currency`),
  KEY `ddrm_id` (`ddrm_id`) USING BTREE,
  CONSTRAINT `ncr_recording_currency_foreign_idx` FOREIGN KEY (`currency`) REFERENCES `currency` (`id`),
  CONSTRAINT `ncr_recording_ibfk_109` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_157` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_158` FOREIGN KEY (`raised_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_159` FOREIGN KEY (`name_of_standard`) REFERENCES `name_of_standard` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_160` FOREIGN KEY (`internal_raised_business`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_161` FOREIGN KEY (`internal_raised_against_business`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_162` FOREIGN KEY (`internal_raised_against_department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_163` FOREIGN KEY (`internal_responsible_person`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_164` FOREIGN KEY (`corrective_responsible_department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_165` FOREIGN KEY (`corrective_responsible_person`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_166` FOREIGN KEY (`preventative_responsible_department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_167` FOREIGN KEY (`preventative_responsible_person`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_168` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ncr_recording_ibfk_169` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: near_miss_reporting
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `near_miss_reporting` (
  `id` int NOT NULL AUTO_INCREMENT,
  `near_miss_classification` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `immediate_action_taken` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `severity` enum('High', 'Medium', 'Low') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Low',
  `probability` enum('High', 'Medium', 'Low') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Low',
  `requires_investigation` enum('Yes', 'No') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `investigation_outcome` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `save_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `person_reporting` int DEFAULT NULL,
  `near_miss_reporting_date` date DEFAULT NULL,
  `near_miss_actual_date` date DEFAULT NULL,
  `near_miss_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `immediate_cause` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `root_cause` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `action_taken` int DEFAULT NULL,
  `near_miss_submission` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `notification` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `for_contractor` int DEFAULT '0',
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `person_reporting` (`person_reporting`),
  CONSTRAINT `near_miss_reporting_ibfk_66` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `near_miss_reporting_ibfk_67` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `near_miss_reporting_ibfk_68` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `near_miss_reporting_ibfk_69` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `near_miss_reporting_ibfk_70` FOREIGN KEY (`person_reporting`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: new_location
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `new_location` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `new_location_ibfk_4` FOREIGN KEY (`parent_id`) REFERENCES `new_location` (`id`),
  CONSTRAINT `new_location_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `new_location_ibfk_6` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = latin1;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: non_compliance_technical_form
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `non_compliance_technical_form` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int DEFAULT NULL,
  `incident_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_date` date DEFAULT NULL,
  `incident_time` time DEFAULT NULL,
  `initial_incident_severity` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_category` int DEFAULT NULL,
  `incident_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_location` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quality_incident_details` json DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `save_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `category_id` int DEFAULT NULL,
  `references_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `for_contractor` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `incident_id` (`incident_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `non_compliance_technical_form_ibfk_79` FOREIGN KEY (`incident_id`) REFERENCES `incident` (`id`),
  CONSTRAINT `non_compliance_technical_form_ibfk_80` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `non_compliance_technical_form_ibfk_81` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `non_compliance_technical_form_ibfk_82` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `non_compliance_technical_form_ibfk_83` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `non_compliance_technical_form_ibfk_84` FOREIGN KEY (`category_id`) REFERENCES `category_type` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: notification
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `notification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `subject` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `message` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `type` enum('url', 'text') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'text',
  `user_id` int DEFAULT NULL,
  `is_read` int DEFAULT '0',
  `on_notification` int DEFAULT '1' COMMENT 'Those number of notification which is not open from notification bar',
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `notification_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `notification_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: objective_setting
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `objective_setting` (
  `id` int NOT NULL AUTO_INCREMENT,
  `associated_risks_register` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `associated_risks` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `objective_target_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `objective_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `objective_owner` int DEFAULT NULL,
  `date_generated` date DEFAULT NULL,
  `objective_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `human_resource_requirements` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `technological_resources` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `type_of_resources` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `amount` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `target_id` int DEFAULT NULL,
  `measurement_metric` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `numeric_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `target_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `kpi_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `target_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `management_plan_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `management_plan_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `management_plan_target_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `progress` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('Pending Approval', 'Approved', 'Rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending Approval',
  `source` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `prioritized_risks` int DEFAULT NULL,
  `legal_register` int DEFAULT NULL,
  `risk` int DEFAULT NULL,
  `legal` int DEFAULT NULL,
  `risk_type` enum(
  'strategic_risk_identification',
  'tactical_risk_identification',
  'operational_risk_identification',
  'none'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'none',
  `remark` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `legislation_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `provision_of_the_requirement` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `level_of_legislation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `action_taken` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `fk_objective_setting_created_by` (`created_by`),
  KEY `fk_objective_setting_updated_by` (`updated_by`),
  CONSTRAINT `objective_setting_ibfk_100` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `objective_setting_ibfk_101` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `objective_setting_ibfk_102` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `objective_setting_ibfk_103` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: objective_setting_target_plan
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `objective_setting_target_plan` (
  `id` int NOT NULL AUTO_INCREMENT,
  `objective_setting_id` int DEFAULT NULL,
  `action_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `action_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `responsible_department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `responsible_person` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `action_progress_description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `action_progress_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `objective_setting_id` (`objective_setting_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `objective_setting_target_plan_ibfk_37` FOREIGN KEY (`objective_setting_id`) REFERENCES `objective_setting` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `objective_setting_target_plan_ibfk_38` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `objective_setting_target_plan_ibfk_39` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: occupational_disease
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `occupational_disease` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `occupational_disease_ibfk_7` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `occupational_disease_ibfk_8` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `occupational_disease_ibfk_9` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: occupational_disease_form
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `occupational_disease_form` (
  `id` int NOT NULL AUTO_INCREMENT,
  `category_id` int DEFAULT NULL,
  `save_type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_id` int DEFAULT NULL,
  `incident_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_date` date DEFAULT NULL,
  `incident_time` time DEFAULT NULL,
  `incident_severity` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_category` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_location` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `name_of_exposed_person` int DEFAULT NULL,
  `role` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `applicable_license` enum('Yes', 'No') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'No',
  `license_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `license_number` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `license_expiry_date` date DEFAULT NULL,
  `applicable_training` enum('Yes', 'No') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'No',
  `training_certificate_number` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `training_certificate_expiry_date` date DEFAULT NULL,
  `_they_licensed_trained` tinyint DEFAULT '0',
  `was_license_training_applicable_to_task` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `license_training_expiry_date` date DEFAULT NULL,
  `license_no_training_certificate_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `year_issued` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `issued_by` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `occupational_disease_category` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `occupational_disease` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `occupational_disease_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `occupational_disease_severity_level` int DEFAULT NULL,
  `noise_induced_hearing_loss` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_diagnosis` date DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `was_case_reported_to_authority_regulator` tinyint DEFAULT '0',
  `name_of_regulator` int DEFAULT NULL,
  `date_reported` date DEFAULT NULL,
  `reported_by_who` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `effect_on_person` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `exposure_route` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `source_of_exposure` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `estimated_duration_of_exposure` time DEFAULT NULL,
  `other_employees_exposed` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `names_of_exposed_employees` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `occupational_hygiene_survey_conducted` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `occupational_hygiene_survey_date` date DEFAULT NULL,
  `occupational_hygiene_survey_finish_date` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `occupational_hygiene_survey_confirmed` enum('yes', 'no') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'no',
  `occupational_hygiene_report_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `occupational_hygiene_report_date` date DEFAULT NULL,
  `estimated_cost_medical_expenses` decimal(10, 2) DEFAULT NULL,
  `comments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `form_completed_by` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `designation` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `signature` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date` date DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `for_contractor` int DEFAULT '0',
  `occupationals` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `specify` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `initial_incident_severity` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `incident_id` (`incident_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `occupational_disease_form_ibfk_66` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `occupational_disease_form_ibfk_67` FOREIGN KEY (`incident_id`) REFERENCES `incident` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `occupational_disease_form_ibfk_68` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `occupational_disease_form_ibfk_69` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `occupational_disease_form_ibfk_70` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: occupational_disease_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `occupational_disease_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `occupational_disease_type_ibfk_10` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `occupational_disease_type_ibfk_11` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `occupational_disease_type_ibfk_12` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `occupational_disease_type_ibfk_9` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: ohs_file_assessment
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `ohs_file_assessment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ohs_requirement` int DEFAULT NULL,
  `form` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending',
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `vals` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `signing_method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Digital',
  `document` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `ddrm_id` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ohs_requirement` (`ohs_requirement`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `ohs_file_assessment_ibfk_49` FOREIGN KEY (`ohs_requirement`) REFERENCES `ohs_file_requirement` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ohs_file_assessment_ibfk_50` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ohs_file_assessment_ibfk_51` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ohs_file_assessment_ibfk_52` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ohs_file_assessment_ibfk_53` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ohs_file_assessment_ibfk_54` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: ohs_file_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `ohs_file_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `ohs_id` int DEFAULT NULL,
  `file` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ohs_id` (`ohs_id`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `ohs_file_docs_ibfk_17` FOREIGN KEY (`ohs_id`) REFERENCES `ohs_file_assessment` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ohs_file_docs_ibfk_18` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: ohs_file_requirement
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `ohs_file_requirement` (
  `id` int NOT NULL AUTO_INCREMENT,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `company_registration_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `coida_registration_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `health_safety_representative` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `contact_person` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `site_address` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `postal_address` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `section_appointment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `activities_rendered` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `commencement_date` date DEFAULT NULL,
  `completion_date` date DEFAULT NULL,
  `employees_on_site` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `female` bigint DEFAULT NULL,
  `male` bigint DEFAULT NULL,
  `people_with_disabilities` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contractor` int DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `contractor` (`contractor`),
  CONSTRAINT `ohs_file_requirement_ibfk_41` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ohs_file_requirement_ibfk_42` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ohs_file_requirement_ibfk_43` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ohs_file_requirement_ibfk_44` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ohs_file_requirement_ibfk_45` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: ohsfileassessments
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `ohsfileassessments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ohs_requirement` int DEFAULT NULL,
  `form` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Pending',
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: operational_activities
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `operational_activities` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `frequency` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `work_id` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `operational_risk_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `work_id` (`work_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `operational_risk_id` (`operational_risk_id`),
  CONSTRAINT `operational_activities_ibfk_100` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_activities_ibfk_101` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_activities_ibfk_102` FOREIGN KEY (`operational_risk_id`) REFERENCES `operational_risk` (`id`),
  CONSTRAINT `operational_activities_ibfk_97` FOREIGN KEY (`work_id`) REFERENCES `operational_work_area` (`id`),
  CONSTRAINT `operational_activities_ibfk_98` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_activities_ibfk_99` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: operational_risk
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `operational_risk` (
  `id` int NOT NULL AUTO_INCREMENT,
  `risk_register_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assessment_date` date DEFAULT NULL,
  `review_date` date DEFAULT NULL,
  `risk_context` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `risk_assessment_facilitator` int DEFAULT NULL,
  `register_assessment_facilitator` int DEFAULT NULL,
  `risk_register_approver` int DEFAULT NULL COMMENT 'risk_register_approver',
  `meeting_id` int DEFAULT NULL,
  `venue` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `time` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attendance_date` date DEFAULT NULL,
  `attendees` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Draft',
  `reject_comment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `approval_status` enum('Pending Approval', 'Approved', 'Rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending Approval',
  `register_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `main_process` int DEFAULT NULL,
  `work_area_id` int DEFAULT NULL,
  `control_assessments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `opportunity_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `approver` int DEFAULT NULL,
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `risk_assessment_facilitator` (`risk_assessment_facilitator`),
  KEY `register_assessment_facilitator` (`register_assessment_facilitator`),
  KEY `risk_register_approver` (`risk_register_approver`),
  KEY `meeting_id` (`meeting_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `approver` (`approver`),
  CONSTRAINT `operational_risk_ibfk_100` FOREIGN KEY (`meeting_id`) REFERENCES `meeting` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_risk_ibfk_101` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_risk_ibfk_102` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_risk_ibfk_103` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_risk_ibfk_104` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_risk_ibfk_105` FOREIGN KEY (`approver`) REFERENCES `users` (`id`),
  CONSTRAINT `operational_risk_ibfk_97` FOREIGN KEY (`risk_assessment_facilitator`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_risk_ibfk_98` FOREIGN KEY (`register_assessment_facilitator`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_risk_ibfk_99` FOREIGN KEY (`risk_register_approver`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: operational_risk_identification
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `operational_risk_identification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `frequency` int DEFAULT NULL,
  `probability` int DEFAULT NULL,
  `risk_rating` int DEFAULT NULL,
  `risk_ranking` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `opportunity_identification` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `opportunity_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `priority_confirmation` int DEFAULT '0',
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `substitution` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `engineering_control` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `administrative_control` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_resort` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `effectiveness` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` int DEFAULT '0',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `activity_id` int DEFAULT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `category` int DEFAULT NULL,
  `hazard` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `impact` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `consequences` int DEFAULT NULL,
  `control_assessments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `residual_rating` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `residual_ranking` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `risk_register_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `fk_operational_risk_identification_created_by` (`created_by`),
  KEY `fk_operational_risk_identification_updated_by` (`updated_by`),
  KEY `department` (`department`),
  KEY `activity_id` (`activity_id`),
  KEY `risk_register_id` (`risk_register_id`),
  CONSTRAINT `operational_risk_identification_ibfk_100` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_risk_identification_ibfk_101` FOREIGN KEY (`activity_id`) REFERENCES `operational_activities` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_risk_identification_ibfk_102` FOREIGN KEY (`risk_register_id`) REFERENCES `operational_risk` (`id`),
  CONSTRAINT `operational_risk_identification_ibfk_97` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_risk_identification_ibfk_98` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_risk_identification_ibfk_99` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: operational_work_area
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `operational_work_area` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `operational_risk_id` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `operational_risk_id` (`operational_risk_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `operational_work_area_ibfk_81` FOREIGN KEY (`operational_risk_id`) REFERENCES `operational_risk` (`id`),
  CONSTRAINT `operational_work_area_ibfk_82` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_work_area_ibfk_83` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_work_area_ibfk_84` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `operational_work_area_ibfk_85` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: organization
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `organization` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int DEFAULT '0',
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `level` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `business_logo` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `background_image` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `header_image` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `footer_image` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `font_size` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pick_color` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `business_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `organization_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `organization_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: parent_objective
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `parent_objective` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_static` tinyint NOT NULL DEFAULT '0',
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `parent_objective_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `parent_objective_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 11 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: permit_license
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `permit_license` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_static` tinyint NOT NULL DEFAULT '0',
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `department` (`department`),
  CONSTRAINT `permit_license_ibfk_67` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `permit_license_ibfk_68` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `permit_license_ibfk_69` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 123 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: permit_license_compliance
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `permit_license_compliance` (
  `id` int NOT NULL AUTO_INCREMENT,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `permit_holder` int DEFAULT NULL,
  `permit_license_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `issuing_authority` int DEFAULT NULL,
  `applicable_section_of_legislation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `issue_date` datetime DEFAULT NULL,
  `condition_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `name` int DEFAULT NULL,
  `permit_license_category` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permit_holder_type` enum('personal', 'business') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'personal',
  `effective_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `unique_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_permit_license_compliance_issuing_authority` (`issuing_authority`),
  KEY `fk_permit_license_compliance_organization` (`organization`),
  KEY `fk_permit_license_compliance_created_by` (`created_by`),
  KEY `fk_permit_license_compliance_updated_by` (`updated_by`),
  KEY `permit_holder` (`permit_holder`),
  KEY `department` (`department`),
  KEY `name` (`name`),
  CONSTRAINT `permit_license_compliance_ibfk_155` FOREIGN KEY (`permit_holder`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `permit_license_compliance_ibfk_156` FOREIGN KEY (`issuing_authority`) REFERENCES `issuing_authority` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `permit_license_compliance_ibfk_157` FOREIGN KEY (`department`) REFERENCES `department` (`id`),
  CONSTRAINT `permit_license_compliance_ibfk_158` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `permit_license_compliance_ibfk_159` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `permit_license_compliance_ibfk_160` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `permit_license_compliance_ibfk_161` FOREIGN KEY (`name`) REFERENCES `permit_license` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: permit_license_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `permit_license_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `permit_license_id` int DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `permit_license_id` (`permit_license_id`),
  CONSTRAINT `permit_license_docs_ibfk_1` FOREIGN KEY (`permit_license_id`) REFERENCES `permit_license_compliance` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: physical_location
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `physical_location` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `physical_location_ibfk_41` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `physical_location_ibfk_42` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `physical_location_ibfk_43` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `physical_location_ibfk_44` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: police_station
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `police_station` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city_id` int DEFAULT NULL,
  `created_by` int NOT NULL,
  `updated_by` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `is_static` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `police_station_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `police_station_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1098 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: policy
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `policy` (
  `id` int NOT NULL AUTO_INCREMENT,
  `policy_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `policy_title` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `policy_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `policy_purpose` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `policy_scope` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `policy_owner` int DEFAULT NULL,
  `policy_statement` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `current_status` enum(
  'pending',
  'reviewing',
  'approval',
  'approved',
  'rejected'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `deleted` tinyint DEFAULT '0',
  `document_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_reference_number` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `version_number` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `policy_approver` int DEFAULT NULL,
  `policy_terms` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `policy_abbreviations` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `roles_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `revision_effective_date` date DEFAULT NULL,
  `details_of_addition` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `policy_author` int DEFAULT NULL,
  `document_classification` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `effective_date` date DEFAULT NULL,
  `next_review_date` date DEFAULT NULL,
  `policy_reviewer` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `new_sop` tinyint DEFAULT '0',
  `affected_sop` int DEFAULT NULL,
  `last_review_date` date DEFAULT NULL,
  `policy_number` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `revision_version_number` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `policy_status` enum('draft', 'complete') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'draft',
  `reference_no` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `policy_communicated_count` int NOT NULL DEFAULT '0',
  `policy_reference` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `policy_revision_history` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `policy_owner` (`policy_owner`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `policy_approver` (`policy_approver`),
  KEY `policy_author` (`policy_author`),
  CONSTRAINT `policy_ibfk_82` FOREIGN KEY (`policy_owner`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `policy_ibfk_83` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `policy_ibfk_84` FOREIGN KEY (`department`) REFERENCES `department` (`id`),
  CONSTRAINT `policy_ibfk_85` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `policy_ibfk_86` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `policy_ibfk_87` FOREIGN KEY (`policy_approver`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `policy_ibfk_88` FOREIGN KEY (`policy_author`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: policy_comments
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `policy_comments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `policy_id` int DEFAULT NULL,
  `comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` enum('approved', 'rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `policy_id` (`policy_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `policy_comments_ibfk_43` FOREIGN KEY (`policy_id`) REFERENCES `policy` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `policy_comments_ibfk_44` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `policy_comments_ibfk_45` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: ppe_repository
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `ppe_repository` (
  `id` int NOT NULL AUTO_INCREMENT,
  `effective_date` datetime DEFAULT NULL,
  `last_inspection_date` datetime DEFAULT NULL,
  `location` int NOT NULL,
  `next_inspection_date` datetime DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `quantity` int DEFAULT NULL,
  `review_date` datetime DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` int DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `inspection_date` date DEFAULT NULL,
  `scheduled_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending',
  `department` int DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inspection_outcome` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `investigation_outcome` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `maintenance_frequency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `maintenance_provider` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ogdptype` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `root_cause` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `immediate_cause` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `inspection_checklist` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `schedule` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `action_taken` int DEFAULT NULL,
  `value` int DEFAULT NULL,
  `content_list` int DEFAULT NULL,
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `location` (`location`),
  KEY `department` (`department`),
  CONSTRAINT `ppe_repository_ibfk_31` FOREIGN KEY (`location`) REFERENCES `location` (`id`),
  CONSTRAINT `ppe_repository_ibfk_32` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`),
  CONSTRAINT `ppe_repository_ibfk_33` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `ppe_repository_ibfk_34` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`),
  CONSTRAINT `ppe_repository_ibfk_35` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: ppe_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `ppe_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parent` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_static` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_ppe_type_created_by` (`created_by`),
  KEY `fk_ppe_type_updated_by` (`updated_by`),
  CONSTRAINT `ppe_type_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `ppe_type_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 166 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: pre_site_response
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `pre_site_response` (
  `id` int NOT NULL AUTO_INCREMENT,
  `value` int NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `employee_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Response Pending',
  `contractor_ques_form` int DEFAULT NULL,
  `project` int DEFAULT NULL,
  `form` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `reciept_date` datetime DEFAULT NULL,
  `comment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `endorsement_date` datetime DEFAULT NULL,
  `deleted` int NOT NULL,
  `contractor_pre_site_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_pre_site_response_contractor_ques_form` (`contractor_ques_form`),
  KEY `fk_pre_site_response_project` (`project`),
  KEY `fk_pre_site_response_contractor_pre_site_id` (`contractor_pre_site_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: pre_site_responses
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `pre_site_responses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employee_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Response Pending',
  `contractor` int DEFAULT NULL,
  `contractor_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_representative` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `area` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `rectification_action` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `representative_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `supervisor_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contract_representative` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contract_supervisor` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `cipc_registration_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contract_number` bigint DEFAULT NULL,
  `endorsement_date` date DEFAULT NULL,
  `date_of_inspection` date DEFAULT NULL,
  `contractor_pre_site_form` int DEFAULT NULL,
  `project` int DEFAULT NULL,
  `form` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `reciept_date` datetime DEFAULT NULL,
  `comment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `contractor_pre_site_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `contractor_pre_site_form` (`contractor_pre_site_form`),
  KEY `project` (`project`),
  KEY `contractor_pre_site_id` (`contractor_pre_site_id`),
  CONSTRAINT `pre_site_responses_ibfk_25` FOREIGN KEY (`contractor_pre_site_form`) REFERENCES `contractor_forms` (`id`),
  CONSTRAINT `pre_site_responses_ibfk_26` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`),
  CONSTRAINT `pre_site_responses_ibfk_27` FOREIGN KEY (`contractor_pre_site_id`) REFERENCES `contractor_pre_site` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: processes
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `processes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `is_static` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `processes_ibfk_17` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `processes_ibfk_19` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `processes_ibfk_20` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 271 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: project_registration
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `project_registration` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contractor` int DEFAULT NULL,
  `cipc_registration_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `project_title` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `project_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `type_of_work` int DEFAULT NULL,
  `project_start_date` date DEFAULT NULL,
  `project_end_date` date DEFAULT NULL,
  `reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `project_planned_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `contract_reference_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contract_start_date` date DEFAULT NULL,
  `contract_end_date` date DEFAULT NULL,
  `project_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contract_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `classification_value` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `classification_criticality` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `classification_impact` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `classification_category` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `project_members` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sub_contractor` int DEFAULT NULL,
  `sub_contractors_personnel` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `site_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `access_location` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contractor` (`contractor`),
  KEY `sub_contractor` (`sub_contractor`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `type_of_work` (`type_of_work`),
  CONSTRAINT `project_registration_ibfk_71` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `project_registration_ibfk_72` FOREIGN KEY (`type_of_work`) REFERENCES `type_of_service` (`id`),
  CONSTRAINT `project_registration_ibfk_73` FOREIGN KEY (`sub_contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `project_registration_ibfk_74` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `project_registration_ibfk_75` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `project_registration_ibfk_76` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `project_registration_ibfk_77` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: project_registration_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `project_registration_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `project_registration_id` int DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `project_registration_id` (`project_registration_id`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `project_registration_docs_ibfk_17` FOREIGN KEY (`project_registration_id`) REFERENCES `project_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `project_registration_docs_ibfk_18` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: purpose_of_training
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `purpose_of_training` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `purpose_of_training_ibfk_13` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `purpose_of_training_ibfk_14` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `purpose_of_training_ibfk_15` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: quality_incident_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `quality_incident_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_static` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `quality_incident_type_ibfk_58` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `quality_incident_type_ibfk_59` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `quality_incident_type_ibfk_60` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 13 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: quality_technical_form
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `quality_technical_form` (
  `id` int NOT NULL AUTO_INCREMENT,
  `permit_title` int DEFAULT NULL,
  `permit_holder` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permit_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `permit_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permit_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `issuing_authority` int DEFAULT NULL,
  `applicable_legislation_section` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `issue_date` date NOT NULL,
  `validity_from` date DEFAULT NULL,
  `validity_until` date DEFAULT NULL,
  `notification_alert` int NOT NULL,
  `condition_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `renewal_due_date` date DEFAULT NULL,
  `permit_renewal_process` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `documents` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `save_type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `issuing_authority` (`issuing_authority`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: questionnaire_response
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `questionnaire_response` (
  `id` int NOT NULL AUTO_INCREMENT,
  `value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employee_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Response Pending',
  `contractor_ques_form` int DEFAULT NULL,
  `project` int DEFAULT NULL,
  `form` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `reciept_date` datetime DEFAULT NULL,
  `comment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `endorsement_date` datetime DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `contractor_questionnaire_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `contractor_ques_form` (`contractor_ques_form`),
  KEY `project` (`project`),
  KEY `contractor_questionnaire_id` (`contractor_questionnaire_id`),
  CONSTRAINT `questionnaire_response_ibfk_25` FOREIGN KEY (`contractor_ques_form`) REFERENCES `contractor_forms` (`id`),
  CONSTRAINT `questionnaire_response_ibfk_26` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`),
  CONSTRAINT `questionnaire_response_ibfk_27` FOREIGN KEY (`contractor_questionnaire_id`) REFERENCES `contractor_questionnaire` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: questions
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `questions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `audit_template_id` int DEFAULT NULL,
  `question_title` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `answer_type` enum(
  'Multiple Choice',
  'Checkbox',
  'Dropdown',
  'Paragraph',
  'Linear Scale',
  'Date',
  'Time'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `answer_key` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `points` int DEFAULT '0',
  `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `min` int DEFAULT NULL,
  `max` int DEFAULT NULL,
  `question_no` int DEFAULT NULL,
  `required` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` tinyint DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_template_id` (`audit_template_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `questions_ibfk_4` FOREIGN KEY (`audit_template_id`) REFERENCES `audit_template` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `questions_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `questions_ibfk_6` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: race
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `race` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `race_ibfk_4` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `race_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `race_ibfk_6` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: reason
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `reason` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `deleted` int NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: reason_for_inactive
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `reason_for_inactive` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `reason_for_inactive_ibfk_19` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `reason_for_inactive_ibfk_20` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `reason_for_inactive_ibfk_21` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: region
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `region` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state_id` int unsigned DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = COMPACT;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: regulator
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `regulator` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `regulator_ibfk_40` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `regulator_ibfk_41` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `regulator_ibfk_42` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: regulatory_body
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `regulatory_body` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `sector` varchar(255) DEFAULT NULL,
  `regulatory_body` varchar(255) DEFAULT NULL,
  `government_sphere` varchar(255) DEFAULT NULL,
  `created_by` int NOT NULL,
  `updated_by` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `is_static` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `regulatory_body_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `regulatory_body_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 460 DEFAULT CHARSET = latin1;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: regulatory_compliance
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `regulatory_compliance` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `evidence_requirements` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `affected_parties` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `compliance_information` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `policy_and_procedure_documents` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `record_retention_policies` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `regulatory_guidance` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `other_documents` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: relation
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `relation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `relation_ibfk_4` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `relation_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `relation_ibfk_6` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: repository
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `repository` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` enum('folder', 'file') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'folder',
  `parent_id` int DEFAULT NULL,
  `document_creation_id` int DEFAULT NULL,
  `table_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `table_id` int DEFAULT NULL,
  `sidebar_id` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `is_archived` int DEFAULT '0',
  `size` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_locked` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `document_creation_id` (`document_creation_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `repository_ibfk_131` FOREIGN KEY (`document_creation_id`) REFERENCES `document_creation` (`id`),
  CONSTRAINT `repository_ibfk_132` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `repository_ibfk_133` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `repository_ibfk_134` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `repository_ibfk_135` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: responsibility
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `responsibility` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `category` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `priority` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `deleted` tinyint DEFAULT '0',
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `responsibility_ibfk_58` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `responsibility_ibfk_59` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `responsibility_ibfk_60` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: retention_policy_setting
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `retention_policy_setting` (
  `id` int NOT NULL AUTO_INCREMENT,
  `document_type_id` int DEFAULT NULL,
  `retention_period` int DEFAULT '0',
  `auto_archive` int DEFAULT '0',
  `auto_delete` int DEFAULT '0',
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `document_type_id` (`document_type_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `retention_policy_setting_ibfk_10` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `retention_policy_setting_ibfk_6` FOREIGN KEY (`document_type_id`) REFERENCES `document_type` (`id`),
  CONSTRAINT `retention_policy_setting_ibfk_7` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `retention_policy_setting_ibfk_8` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `retention_policy_setting_ibfk_9` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: role_hierarchy
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `role_hierarchy` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `editable` int DEFAULT '1',
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `level` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `role_hierarchy_ibfk_79` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `role_hierarchy_ibfk_80` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `role_hierarchy_ibfk_81` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: role_on_the_project
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `role_on_the_project` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `role_on_the_project_ibfk_25` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `role_on_the_project_ibfk_26` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `role_on_the_project_ibfk_27` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: roles
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `level` int DEFAULT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `qualification` int DEFAULT NULL,
  `year_of_experience` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `other_requirements` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `select_other_requirements` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `report_to_role` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `department` int DEFAULT NULL,
  `hierarchy` int DEFAULT NULL,
  `license` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `skills` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `permission` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `pre_employment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `post_employment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `qualification` (`qualification`),
  KEY `hierarchy` (`hierarchy`),
  CONSTRAINT `roles_ibfk_131` FOREIGN KEY (`qualification`) REFERENCES `highest_qualification` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `roles_ibfk_132` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `roles_ibfk_133` FOREIGN KEY (`hierarchy`) REFERENCES `role_hierarchy` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `roles_ibfk_134` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `roles_ibfk_135` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: safety_incident
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `safety_incident` (
  `id` int NOT NULL AUTO_INCREMENT,
  `organization` int NOT NULL,
  `type_of_incident` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `location` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `immediate_cause_of_incident` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `was_alcohol_or_substance_testing_done` tinyint(1) NOT NULL DEFAULT '0',
  `tested_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `state_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `alcohol_substance_test_results` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `method_of_testing` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `was_the_person_or_operator_licensed` tinyint(1) NOT NULL DEFAULT '0',
  `was_the_license_applicable_to_the_task` tinyint(1) NOT NULL,
  `license_training_expiry_date` datetime DEFAULT NULL,
  `license_no_training_certificate_no` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `year_issued` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `issued_by` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `incident_level` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reportable_cases_classification` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `was_the_case_reported_to_authority` tinyint(1) NOT NULL DEFAULT '0',
  `comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `form_completed_by` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `designation` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date` datetime NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_safety_incident_organization` (`organization`),
  KEY `fk_safety_incident_created_by` (`created_by`),
  KEY `fk_safety_incident_updated_by` (`updated_by`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: security_incident_form
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `security_incident_form` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int DEFAULT NULL,
  `date_of_incident` date DEFAULT NULL,
  `time_of_incident` time DEFAULT NULL,
  `location_of_incident` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_reference_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type_of_security_incident` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description_of_incident` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `committed_by_whom` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `specify_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name_of_employee` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employee_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employee_type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `what_kind_of_theft` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `estimated_cost` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `estimated_replacement_cost` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `was_case_reported_to_insurer` int DEFAULT '0' COMMENT 'value contains only from these Two (0 or 1) default is 0',
  `name_of_insurer` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_transnet_or_third_party_insurer` int DEFAULT '0' COMMENT 'value contains only from these Two (0 or 1) default is 0',
  `date_reported_to_insurer` date DEFAULT NULL,
  `reported_by_who_to_insurer` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `claim_number` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `who_received_the_claim` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `was_case_reported_to_saps` int DEFAULT '0' COMMENT 'value contains only from these Two (0 or 1) default is 0',
  `name_of_police_station_saps` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `case_number_saps` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name_of_contact_person_saps` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_details_saps` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `investigation_officer_saps` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_investigation_saps` date DEFAULT NULL,
  `time_of_investigation_saps` time DEFAULT NULL,
  `person_who_reported_investigation_saps` int DEFAULT NULL,
  `any_arrest_made` int DEFAULT '0' COMMENT 'value contains only from these Two (0 or 1) default is 0',
  `how_many_arrested` int DEFAULT NULL,
  `suspect_no` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `employee_type_arrest` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `who_was_involved_in_arrest` json DEFAULT NULL,
  `police_officers_involved` int DEFAULT NULL,
  `community_members_involved` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sap_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employee_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permanent_employee` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contracted_employee` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `trainee` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `any_items_recovered` int DEFAULT NULL,
  `recovered_items` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `was_there_forced_entry` tinyint DEFAULT '0' COMMENT '(0 or 1) default is 0',
  `description_of_forced_entry` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `asset_number` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `location_of_asset` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `time_of_discovery` time DEFAULT NULL,
  `person_reporting_break_in` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `witness_of_break_in` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tools_used` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `value_involved` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `value_recovered` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `form_completed_by` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `designation` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `signature` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date` date DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `save_type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `specify_theft` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `case_reported_to_regulator` int DEFAULT '0' COMMENT 'value contains only from these Two (0 or 1) default is 0',
  `regulator_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_reported` date DEFAULT NULL,
  `reported_by` int DEFAULT NULL,
  `estimated_cost_authority` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_form_submission` date DEFAULT NULL,
  `notification` json DEFAULT NULL,
  `estimated_financial_impact` json DEFAULT NULL,
  `technical_form_completed_by` json DEFAULT NULL,
  `any_recoveries` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `name_of_people_arrested` json DEFAULT NULL,
  `security_incident_severity_level` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `suspects_detail` json DEFAULT NULL,
  `name_of_incident` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `for_contractor` int DEFAULT '0',
  `state` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `region` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `financial_value` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `insurer_employee_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `technical_employee_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `technical_department` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `technical_role` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `insurer_role_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `insurer_department` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `category_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `person_who_reported_investigation_saps` (`person_who_reported_investigation_saps`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `incident_id` (`incident_id`),
  KEY `security_incident_form_category_id_foreign_idx` (`category_id`),
  CONSTRAINT `security_incident_form_ibfk_92` FOREIGN KEY (`incident_id`) REFERENCES `incident` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `security_incident_form_ibfk_93` FOREIGN KEY (`person_who_reported_investigation_saps`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `security_incident_form_ibfk_94` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `security_incident_form_ibfk_95` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `security_incident_form_ibfk_96` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `security_incident_form_ibfk_97` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `security_incident_form_ibfk_98` FOREIGN KEY (`category_id`) REFERENCES `incident_category` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: security_incident_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `security_incident_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `security_incident_type_ibfk_7` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `security_incident_type_ibfk_8` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `security_incident_type_ibfk_9` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: severity
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `severity` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `severity_ibfk_40` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `severity_ibfk_41` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `severity_ibfk_42` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: she_legal_register
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `she_legal_register` (
  `id` int NOT NULL AUTO_INCREMENT,
  `risk_given_id` int DEFAULT NULL,
  `function_record` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `title` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `regulatory_body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `legislation_sector_type` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `hierarchy_of_legislation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `subordinate` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `heading` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `level_of_legislation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `description_of_level` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `provision_of_the_requirement` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `interpretation_of_the_provision` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `comment_action_implication` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `consequences_of_non_compliance` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `deleted` tinyint DEFAULT '0',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `she_legal_register_ibfk_29` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `she_legal_register_ibfk_30` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 10001 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: shift_information
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `shift_information` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `shift_information_ibfk_7` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `shift_information_ibfk_8` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `shift_information_ibfk_9` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: short_url
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `short_url` (
  `id` int NOT NULL AUTO_INCREMENT,
  `original_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shorten_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `clicks` int DEFAULT '0',
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: sidebar
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `sidebar` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `icon` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` enum('Module', 'Tab', 'SubTab') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Module',
  `deleted` int DEFAULT '0',
  `parent_id` int DEFAULT '0',
  `sequence` int DEFAULT NULL,
  `table_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `comment` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dependent_module` int DEFAULT NULL,
  `dependency` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 475 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: skills
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `skills` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `skills_type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `industry` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'If skills type is industry then it will store industry id in that',
  `is_static` int DEFAULT '0',
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `skills_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `skills_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 51 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: software_access_request
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `software_access_request` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `user_id` int DEFAULT NULL,
  `sidebar_id` int DEFAULT NULL,
  `status` enum('pending', 'approved', 'rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `sidebar_id` (`sidebar_id`),
  KEY `department` (`department`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `software_access_request_ibfk_25` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `software_access_request_ibfk_26` FOREIGN KEY (`sidebar_id`) REFERENCES `sidebar` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `software_access_request_ibfk_27` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `software_access_request_ibfk_28` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `software_access_request_ibfk_29` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `software_access_request_ibfk_30` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: sop_category
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `sop_category` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department` int DEFAULT NULL,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `sop_category_ibfk_7` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `sop_category_ibfk_8` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `sop_category_ibfk_9` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: sop_comments
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `sop_comments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sop_id` int DEFAULT NULL,
  `comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` enum('approved', 'rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sop_id` (`sop_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `sop_comments_ibfk_19` FOREIGN KEY (`sop_id`) REFERENCES `sops` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `sop_comments_ibfk_20` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `sop_comments_ibfk_21` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: sops
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `sops` (
  `id` int NOT NULL AUTO_INCREMENT,
  `new_sop` tinyint(1) DEFAULT '1',
  `sop_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sop_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sop_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sop_classification` enum(
  'Unclassified',
  'Confidential',
  'Restricted',
  'Private'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sop_process` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sop_category` int DEFAULT NULL,
  `version_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sop_effective_date` datetime DEFAULT NULL,
  `last_review_date` datetime DEFAULT NULL,
  `next_review_date` datetime DEFAULT NULL,
  `sop_status` enum('Draft', 'Under Review', 'Complete') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Draft',
  `sop_purpose` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sop_scope` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sop_statement` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sop_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sop_roles_responsibilities` json DEFAULT NULL,
  `sop_revision_history` json DEFAULT NULL,
  `file_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_classification` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notify_to` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sop_author` int DEFAULT NULL,
  `sop_reviewer` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sop_approver` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `is_archived` tinyint(1) DEFAULT '0',
  `affected_sop` int DEFAULT NULL,
  `revision_version_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `revision_effective_date` datetime DEFAULT NULL,
  `sop_detail_status` enum('Valid', 'Overdue', 'Under Review') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Under Review',
  `current_status` enum(
  'pending',
  'reviewing',
  'approval',
  'approved',
  'rejected'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `details_of_addition` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sop_terms` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sop_abbreviations` json DEFAULT NULL,
  `for_review` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `stakeholder` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sop_owner` int DEFAULT NULL,
  `sop_department` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `for_approval_employee` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ddrm_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `with_access_items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `without_access_items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `without_access_and_without_email` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type_of_sop` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sop_id` (`sop_id`),
  UNIQUE KEY `sop_id_2` (`sop_id`),
  UNIQUE KEY `sop_id_3` (`sop_id`),
  UNIQUE KEY `sop_id_4` (`sop_id`),
  UNIQUE KEY `sop_id_5` (`sop_id`),
  UNIQUE KEY `sop_id_6` (`sop_id`),
  UNIQUE KEY `sop_id_7` (`sop_id`),
  UNIQUE KEY `sop_id_8` (`sop_id`),
  UNIQUE KEY `sop_id_9` (`sop_id`),
  UNIQUE KEY `sop_id_10` (`sop_id`),
  UNIQUE KEY `sop_id_11` (`sop_id`),
  UNIQUE KEY `sop_id_12` (`sop_id`),
  UNIQUE KEY `sop_id_13` (`sop_id`),
  UNIQUE KEY `sop_id_14` (`sop_id`),
  UNIQUE KEY `sop_id_15` (`sop_id`),
  UNIQUE KEY `sop_id_16` (`sop_id`),
  UNIQUE KEY `sop_id_17` (`sop_id`),
  UNIQUE KEY `sop_id_18` (`sop_id`),
  UNIQUE KEY `sop_id_19` (`sop_id`),
  UNIQUE KEY `sop_id_20` (`sop_id`),
  UNIQUE KEY `sop_id_21` (`sop_id`),
  UNIQUE KEY `sop_id_22` (`sop_id`),
  UNIQUE KEY `sop_id_23` (`sop_id`),
  UNIQUE KEY `sop_id_24` (`sop_id`),
  UNIQUE KEY `sop_id_25` (`sop_id`),
  UNIQUE KEY `sop_id_26` (`sop_id`),
  UNIQUE KEY `sop_id_27` (`sop_id`),
  UNIQUE KEY `sop_id_28` (`sop_id`),
  UNIQUE KEY `sop_id_29` (`sop_id`),
  UNIQUE KEY `sop_id_30` (`sop_id`),
  UNIQUE KEY `sop_id_31` (`sop_id`),
  UNIQUE KEY `sop_id_32` (`sop_id`),
  UNIQUE KEY `sop_id_33` (`sop_id`),
  UNIQUE KEY `sop_id_34` (`sop_id`),
  UNIQUE KEY `sop_id_35` (`sop_id`),
  UNIQUE KEY `sop_id_36` (`sop_id`),
  UNIQUE KEY `sop_id_37` (`sop_id`),
  UNIQUE KEY `sop_id_38` (`sop_id`),
  UNIQUE KEY `sop_id_39` (`sop_id`),
  UNIQUE KEY `sop_id_40` (`sop_id`),
  UNIQUE KEY `sop_id_41` (`sop_id`),
  UNIQUE KEY `sop_id_42` (`sop_id`),
  UNIQUE KEY `sop_id_43` (`sop_id`),
  UNIQUE KEY `sop_id_44` (`sop_id`),
  KEY `sop_author` (`sop_author`),
  KEY `sop_approver` (`sop_approver`),
  KEY `department` (`department`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `sops_ibfk_25` FOREIGN KEY (`sop_author`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `sops_ibfk_26` FOREIGN KEY (`sop_approver`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `sops_ibfk_27` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `sops_ibfk_28` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `sops_ibfk_29` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `sops_ibfk_30` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: stakeholder_activity_recording
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `stakeholder_activity_recording` (
  `id` int NOT NULL AUTO_INCREMENT,
  `approval_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'In Progress',
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'In Progress',
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `for_approval` int DEFAULT '0',
  `deleted` int DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `engagement_status` enum('In Progress', 'Complete') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'In Progress',
  `is_feedback_received` tinyint(1) DEFAULT NULL,
  `feedback_status` enum('Negative', 'Positive', 'Neutral') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Positive',
  `feedback_context` int DEFAULT NULL,
  `feedback_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `feedback_tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sentiment_rating` enum(
  'Very Positive',
  'Positive',
  'Neutral',
  'Negative',
  'Very Negative'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Neutral',
  `feedback_impact` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `feedback_priority` enum('High', 'Medium', 'Low') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `feedback_impact_scope` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `stakeholder` int DEFAULT NULL,
  `engagement_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `engagement_method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `engagement_plan_objective` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `frequency_of_engagement` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `responsible_person` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `comment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `department_affected` int DEFAULT NULL,
  `ddrm_id` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `feedback_context` (`feedback_context`),
  KEY `stakeholder` (`stakeholder`),
  KEY `department_affected` (`department_affected`),
  CONSTRAINT `stakeholder_activity_recording_ibfk_71` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_activity_recording_ibfk_72` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_activity_recording_ibfk_73` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_activity_recording_ibfk_74` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_activity_recording_ibfk_75` FOREIGN KEY (`feedback_context`) REFERENCES `feedback_context` (`id`),
  CONSTRAINT `stakeholder_activity_recording_ibfk_76` FOREIGN KEY (`stakeholder`) REFERENCES `stakeholder_activity_scheduling` (`id`),
  CONSTRAINT `stakeholder_activity_recording_ibfk_77` FOREIGN KEY (`department_affected`) REFERENCES `department` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: stakeholder_activity_scheduling
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `stakeholder_activity_scheduling` (
  `id` int NOT NULL AUTO_INCREMENT,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `stakeholder` int DEFAULT NULL,
  `engagement_plan` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `engagement_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `engagement_method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `engagement_plan_objective` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `frequency_of_engagement` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `responsible_person` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `identification_id` int DEFAULT NULL,
  `response_received` tinyint(1) DEFAULT '0',
  `status` enum('To do', 'In Progress', 'Completed', 'Overdue') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'To do',
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `stakeholder` (`stakeholder`),
  KEY `engagement_plan` (`engagement_plan`),
  KEY `identification_id` (`identification_id`),
  CONSTRAINT `stakeholder_activity_scheduling_ibfk_61` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_activity_scheduling_ibfk_62` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_activity_scheduling_ibfk_63` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_activity_scheduling_ibfk_64` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_activity_scheduling_ibfk_65` FOREIGN KEY (`stakeholder`) REFERENCES `stakeholder_engagement_plan` (`id`),
  CONSTRAINT `stakeholder_activity_scheduling_ibfk_66` FOREIGN KEY (`identification_id`) REFERENCES `stakeholder_identification` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: stakeholder_classification
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `stakeholder_classification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stakeholder_category` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stakeholder_type` enum('Internal', 'External') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Internal',
  `classification` enum('Primary', 'Secondary') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Primary',
  `engagement_level` enum('High', 'Medium', 'Low') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('Active', 'Inactive', 'Potential') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `interaction_frequency` enum(
  'Daily',
  'Weekly',
  'Bi-weekly',
  'Monthly',
  'Bi-Monthly',
  'Quarterly',
  'Bi-Annually',
  'Annually'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `influence_level` enum('High', 'Medium', 'Low') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `connection_strength` enum('Strong', 'Moderate', 'Weak') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `interest_score` int DEFAULT NULL,
  `influence_score` int DEFAULT NULL,
  `assessment_date` date DEFAULT NULL,
  `aggregate_score` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `priority_level` enum('High', 'Medium', 'Low') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `classification_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recommended_engagement_level` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `stakeholder_classification_ibfk_41` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_classification_ibfk_42` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_classification_ibfk_43` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_classification_ibfk_44` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: stakeholder_engagement_plan
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `stakeholder_engagement_plan` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stakeholder_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `engagement_frequency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `engagement_level` int DEFAULT NULL,
  `technological_resources` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `type_of_resources` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `amount` bigint DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `classification_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `priority_level` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stakeholder_category` int DEFAULT NULL,
  `recommended_engagement_level` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `engagement_plan_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `engagement_plan_objectives` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `engagement_start_date` date DEFAULT NULL,
  `no_of_cycles` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `identification_id` int DEFAULT NULL,
  `responsible_person` int DEFAULT NULL,
  `currency` int DEFAULT NULL,
  `human_resource_requirements` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `engagement_methods` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `stakeholder_name` (`stakeholder_name`),
  KEY `engagement_level` (`engagement_level`),
  KEY `identification_id` (`identification_id`),
  KEY `responsible_person` (`responsible_person`),
  KEY `currency` (`currency`),
  CONSTRAINT `stakeholder_engagement_plan_ibfk_81` FOREIGN KEY (`engagement_level`) REFERENCES `engagement_levels` (`id`),
  CONSTRAINT `stakeholder_engagement_plan_ibfk_82` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_engagement_plan_ibfk_83` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_engagement_plan_ibfk_84` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_engagement_plan_ibfk_85` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_engagement_plan_ibfk_86` FOREIGN KEY (`identification_id`) REFERENCES `stakeholder_identification` (`id`),
  CONSTRAINT `stakeholder_engagement_plan_ibfk_87` FOREIGN KEY (`responsible_person`) REFERENCES `users` (`id`),
  CONSTRAINT `stakeholder_engagement_plan_ibfk_88` FOREIGN KEY (`currency`) REFERENCES `currency` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: stakeholder_identification
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `stakeholder_identification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stakeholder_category` int DEFAULT NULL,
  `classification_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `priority_level` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `internal_stakeholder` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `external_stakeholder` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `stakeholder_category` (`stakeholder_category`),
  CONSTRAINT `stakeholder_identification_ibfk_51` FOREIGN KEY (`stakeholder_category`) REFERENCES `stakeholder_classification` (`id`),
  CONSTRAINT `stakeholder_identification_ibfk_52` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_identification_ibfk_53` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_identification_ibfk_54` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `stakeholder_identification_ibfk_55` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: states
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `states` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country_id` int DEFAULT NULL,
  `country_code` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fips_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `iso2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `latitude` decimal(10, 8) DEFAULT NULL,
  `longitude` decimal(11, 8) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `flag` tinyint(1) DEFAULT '1',
  `wikiDataId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Rapid API GeoDB Cities',
  PRIMARY KEY (`id`),
  KEY `country_region` (`country_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 5228 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = COMPACT;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: strategic_risk
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `strategic_risk` (
  `id` int NOT NULL AUTO_INCREMENT,
  `risk_register_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assessment_date` datetime DEFAULT NULL,
  `review_date` datetime DEFAULT NULL,
  `risk_context` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `risk_assessment_facilitator` int DEFAULT NULL,
  `register_assessment_facilitator` int DEFAULT NULL,
  `risk_register_approver` int DEFAULT NULL COMMENT 'risk_register_approver',
  `meeting_id` int DEFAULT NULL,
  `venue` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `time` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attendance_date` datetime DEFAULT NULL,
  `attendees` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Draft',
  `reject_comment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `approval_status` enum('Pending Approval', 'Approved', 'Rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending Approval',
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `risk_assessment_facilitator` (`risk_assessment_facilitator`),
  KEY `register_assessment_facilitator` (`register_assessment_facilitator`),
  KEY `risk_register_approver` (`risk_register_approver`),
  KEY `meeting_id` (`meeting_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `strategic_risk_ibfk_100` FOREIGN KEY (`meeting_id`) REFERENCES `meeting` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `strategic_risk_ibfk_101` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `strategic_risk_ibfk_102` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `strategic_risk_ibfk_103` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `strategic_risk_ibfk_104` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `strategic_risk_ibfk_97` FOREIGN KEY (`risk_assessment_facilitator`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `strategic_risk_ibfk_98` FOREIGN KEY (`register_assessment_facilitator`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `strategic_risk_ibfk_99` FOREIGN KEY (`risk_register_approver`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: strategic_risk_identification
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `strategic_risk_identification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `risk_register_id` int DEFAULT NULL,
  `risk_owner` int DEFAULT NULL,
  `risk_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `risk_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `impact_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `causes_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `inherent_likelihood` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inherent_consequence` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inherent_risk_rating` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inherent_risk_ranking` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `control_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `opportunity_identification` int DEFAULT NULL,
  `opportunity_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `priority_confirmation` int DEFAULT '0',
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` int DEFAULT '0',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `residual_risk_rating` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `residual_risk_ranking` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `control_identification` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `risk_register_id` (`risk_register_id`),
  KEY `risk_owner` (`risk_owner`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `strategic_risk_identification_ibfk_100` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `strategic_risk_identification_ibfk_101` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `strategic_risk_identification_ibfk_102` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `strategic_risk_identification_ibfk_97` FOREIGN KEY (`risk_register_id`) REFERENCES `strategic_risk` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `strategic_risk_identification_ibfk_98` FOREIGN KEY (`risk_owner`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `strategic_risk_identification_ibfk_99` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: survey_design
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `survey_design` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `survey_analysis` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_automated` int DEFAULT '0',
  `is_responded` int DEFAULT '0',
  `public_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `status` int DEFAULT '1',
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `survey_design_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `survey_design_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: survey_questions
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `survey_questions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `survey_id` int DEFAULT NULL,
  `min` int DEFAULT NULL,
  `max` int DEFAULT NULL,
  `answer_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `automated_scores` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `points` int DEFAULT NULL,
  `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `question_no` int DEFAULT NULL,
  `question_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `scoring_type` enum('manual', 'automated') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'automated',
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `survey_id` (`survey_id`),
  CONSTRAINT `survey_questions_ibfk_1` FOREIGN KEY (`survey_id`) REFERENCES `survey_design` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: survey_response
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `survey_response` (
  `id` int NOT NULL AUTO_INCREMENT,
  `survey_id` int DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `total` bigint DEFAULT '0',
  `label` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_internal` int DEFAULT '0',
  `otp` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `otp_expiry` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `start_end` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_declared` int DEFAULT '0',
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `survey_id` (`survey_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `survey_response_ibfk_21` FOREIGN KEY (`survey_id`) REFERENCES `survey_design` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `survey_response_ibfk_22` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: tactical_risk
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `tactical_risk` (
  `id` int NOT NULL AUTO_INCREMENT,
  `risk_register_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assessment_date` datetime DEFAULT NULL,
  `review_date` datetime DEFAULT NULL,
  `risk_context` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `risk_assessment_facilitator` int DEFAULT NULL,
  `register_assessment_facilitator` int DEFAULT NULL,
  `risk_register_approver` int DEFAULT NULL COMMENT 'risk_register_approver',
  `meeting_id` int DEFAULT NULL,
  `venue` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `time` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attendance_date` datetime DEFAULT NULL,
  `attendees` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Draft',
  `reject_comment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `approval_status` enum('Pending Approval', 'Approved', 'Rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending Approval',
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `risk_assessment_facilitator` (`risk_assessment_facilitator`),
  KEY `register_assessment_facilitator` (`register_assessment_facilitator`),
  KEY `risk_register_approver` (`risk_register_approver`),
  KEY `meeting_id` (`meeting_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `tactical_risk_ibfk_100` FOREIGN KEY (`meeting_id`) REFERENCES `meeting` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `tactical_risk_ibfk_101` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `tactical_risk_ibfk_102` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `tactical_risk_ibfk_103` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `tactical_risk_ibfk_104` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `tactical_risk_ibfk_97` FOREIGN KEY (`risk_assessment_facilitator`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `tactical_risk_ibfk_98` FOREIGN KEY (`register_assessment_facilitator`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `tactical_risk_ibfk_99` FOREIGN KEY (`risk_register_approver`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: tactical_risk_identification
# ------------------------------------------------------------

