
# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: access_requests
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `access_requests` (
  `id` int NOT NULL AUTO_INCREMENT,
  `access_type` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `module_id` int DEFAULT NULL,
  `sub_module_id` int DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `module_id` (`module_id`),
  KEY `sub_module_id` (`sub_module_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `access_requests_ibfk_85` FOREIGN KEY (`module_id`) REFERENCES `sidebar` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `access_requests_ibfk_86` FOREIGN KEY (`sub_module_id`) REFERENCES `sidebar` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `access_requests_ibfk_87` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `access_requests_ibfk_88` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `access_requests_ibfk_89` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `access_requests_ibfk_90` 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: action_activities
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `action_activities` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `estimated_time` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `action` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `custom_action_id` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `custom_action_id` (`custom_action_id`),
  KEY `department` (`department`),
  CONSTRAINT `action_activities_ibfk_51` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_activities_ibfk_52` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_activities_ibfk_53` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_activities_ibfk_54` FOREIGN KEY (`custom_action_id`) REFERENCES `custom_action_creation` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_activities_ibfk_55` 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: action_comments
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `action_comments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `custom_action_id` int DEFAULT NULL,
  `comment` 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_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `custom_action_id` (`custom_action_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `action_comments_ibfk_51` FOREIGN KEY (`custom_action_id`) REFERENCES `custom_action_creation` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_comments_ibfk_52` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_comments_ibfk_53` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_comments_ibfk_54` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_comments_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: action_template_list
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `action_template_list` (
  `id` int NOT NULL AUTO_INCREMENT,
  `template_version` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `send_to_employee` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `for_approval` int DEFAULT '0',
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `visibility` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `category` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sub_category` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assigned_to` int DEFAULT NULL,
  `dependencies` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `successor_task_template` int DEFAULT NULL,
  `predecessor_task_template` int DEFAULT NULL,
  `save_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `task_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `module_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sub_module_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `record_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `task_title` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `task_description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `priority` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `task_duration` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `status` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `role` int DEFAULT NULL,
  `skill` 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 `successor_task_template` (`successor_task_template`),
  KEY `predecessor_task_template` (`predecessor_task_template`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `role` (`role`),
  KEY `skill` (`skill`),
  KEY `assigned_to` (`assigned_to`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `action_template_list_ibfk_209` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_template_list_ibfk_210` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_template_list_ibfk_211` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_template_list_ibfk_212` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_template_list_ibfk_213` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `action_template_list_ibfk_214` FOREIGN KEY (`role`) REFERENCES `roles` (`id`),
  CONSTRAINT `action_template_list_ibfk_215` FOREIGN KEY (`skill`) REFERENCES `skills` (`id`),
  CONSTRAINT `action_template_list_ibfk_216` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: activity_location
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `activity_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,
  `unique_id` varchar(250) 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 `activity_location_ibfk_10` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `activity_location_ibfk_11` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `activity_location_ibfk_12` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `activity_location_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: activity_log
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `activity_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created_by` int DEFAULT NULL,
  `status` enum(
  'login',
  'logout',
  'create',
  'update',
  'delete',
  'view',
  'visit',
  'others',
  'approved',
  'reject',
  'created',
  'updated',
  'deleted',
  'assigned',
  'comment'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'This is work status for activity log that what happened at that time',
  `module` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Store all work id where changes happen',
  `message` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `activity_log_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 20 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: activity_notes
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `activity_notes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `activity_recording_id` int DEFAULT NULL,
  `agenda_item` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `raised_by` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `activity_discussion_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `activity_discussion_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `activity_action_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `activity_action_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `assigned_to` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `due_date` datetime DEFAULT NULL,
  `activity_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,
  `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 `activity_recording_id` (`activity_recording_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: affected_area
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `affected_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,
  `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(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`),
  CONSTRAINT `affected_area_ibfk_4` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `affected_area_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `affected_area_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: agreement_response
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `agreement_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_agreement_id` int NOT NULL,
  `from_email` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `contractor_ques_form` (`contractor_ques_form`),
  KEY `project` (`project`),
  KEY `contractor_agreement_id` (`contractor_agreement_id`),
  CONSTRAINT `agreement_response_ibfk_25` FOREIGN KEY (`contractor_ques_form`) REFERENCES `contractor_forms` (`id`),
  CONSTRAINT `agreement_response_ibfk_26` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`),
  CONSTRAINT `agreement_response_ibfk_27` FOREIGN KEY (`contractor_agreement_id`) REFERENCES `contractor_agreements` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: agreement_response_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `agreement_response_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `agreement_response_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',
  PRIMARY KEY (`id`),
  KEY `agreement_response_id` (`agreement_response_id`),
  CONSTRAINT `agreement_response_docs_ibfk_1` FOREIGN KEY (`agreement_response_id`) REFERENCES `agreement_response` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: announcement
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `announcement` (
  `id` int NOT NULL AUTO_INCREMENT,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `title` 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 CURRENT_TIMESTAMP,
  `attachment` longtext,
  `description` longtext,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `announcement_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `announcement_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: appointment_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `appointment_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assignment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `designated` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `applicable` 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,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `appointment_type_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `appointment_type_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: assessment_location
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `assessment_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,
  `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 `assessment_location_ibfk_49` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `assessment_location_ibfk_50` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `assessment_location_ibfk_51` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `assessment_location_ibfk_52` 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: assessor_method
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `assessor_method` (
  `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 `assessor_method_ibfk_34` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `assessor_method_ibfk_35` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `assessor_method_ibfk_36` 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: asset
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `asset` (
  `id` int NOT NULL AUTO_INCREMENT,
  `asset_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `asset_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `asset_type` int DEFAULT NULL,
  `asset_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `model_make` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `acquisition_type` enum('Owned', 'Leased') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `purchase_price` int DEFAULT NULL,
  `vendor_supplier` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `leased_date` date DEFAULT NULL,
  `lease_price` int DEFAULT NULL,
  `lease_provider` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lease_duration_value` int DEFAULT NULL,
  `lease_duration_unit` enum('Days', 'Months', 'Years') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `location` int DEFAULT NULL,
  `warranty_terms` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `warranty_expiration_date` date DEFAULT NULL,
  `maintenance_regime` enum(
  'Preventive Maintenance',
  'Predictive Maintenance',
  'Corrective Maintenance',
  'Reactive Maintenance',
  'Condition-Based Maintenance',
  'Total Productive Maintenance',
  'Scheduled Maintenance',
  'Reliability-Centered Maintenance'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `conditions` enum('New', 'Used', 'Damaged') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'New',
  `status` enum('Active', 'Inactive', 'Under repair') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Active',
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` tinyint(1) DEFAULT '0',
  `purchase_currency` int DEFAULT NULL,
  `lease_currency` int DEFAULT NULL,
  `asset_department` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `asset_type` (`asset_type`),
  KEY `location` (`location`),
  KEY `department` (`department`),
  KEY `organization` (`organization`),
  KEY `purchase_currency` (`purchase_currency`),
  KEY `lease_currency` (`lease_currency`),
  KEY `asset_department` (`asset_department`),
  CONSTRAINT `asset_ibfk_43` FOREIGN KEY (`asset_type`) REFERENCES `asset_type` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `asset_ibfk_44` FOREIGN KEY (`location`) REFERENCES `location` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `asset_ibfk_45` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `asset_ibfk_46` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `asset_ibfk_47` FOREIGN KEY (`purchase_currency`) REFERENCES `currency` (`id`),
  CONSTRAINT `asset_ibfk_48` FOREIGN KEY (`lease_currency`) REFERENCES `currency` (`id`),
  CONSTRAINT `asset_ibfk_49` FOREIGN KEY (`asset_department`) REFERENCES `department` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: asset_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `asset_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 `asset_type_ibfk_19` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `asset_type_ibfk_20` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `asset_type_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: assets
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `assets` (
  `id` int NOT NULL AUTO_INCREMENT,
  `asset_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `asset_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `asset_type` int DEFAULT NULL,
  `asset_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `model_make` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `acquisition_type` enum('Owned', 'Leased') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `purchase_date` date DEFAULT NULL,
  `purchase_price` int DEFAULT NULL,
  `vendor_supplier` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `leased_date` date DEFAULT NULL,
  `lease_price` int DEFAULT NULL,
  `lease_provider` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lease_duration_value` int DEFAULT NULL,
  `lease_duration_unit` enum('Days', 'Months', 'Years') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `location` int DEFAULT NULL,
  `warranty_terms` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `warranty_expiration_date` date DEFAULT NULL,
  `maintenance_regime` enum(
  'Preventive Maintenance',
  'Predictive Maintenance',
  'Corrective Maintenance',
  'Reactive Maintenance',
  'Condition-Based Maintenance',
  'Total Productive Maintenance',
  'Scheduled Maintenance',
  'Reliability-Centered Maintenance'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `condition` enum('New', 'Used', 'Damaged') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'New',
  `status` enum('Active', 'Inactive', 'Under repair') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Active',
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `asset_type` (`asset_type`),
  KEY `location` (`location`),
  KEY `department` (`department`),
  KEY `organization` (`organization`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: attendance_recording
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `attendance_recording` (
  `id` int NOT NULL AUTO_INCREMENT,
  `actual_training_service` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attendees` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `training_actual_date` date DEFAULT NULL,
  `training_program` int DEFAULT NULL,
  `training_start_date` date DEFAULT NULL,
  `training_total_cost` float DEFAULT NULL,
  `training_venue` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `upload_document` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `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,
  `currency` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `training_actual_time` time DEFAULT NULL,
  `company` int DEFAULT NULL,
  `employee` int DEFAULT NULL,
  `group_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `external_provider` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `role_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `provider_department` 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 `fk_attendance_recording_training_program` (`training_program`),
  KEY `fk_attendance_recording_organization` (`organization`),
  KEY `fk_attendance_recording_department` (`department`),
  KEY `fk_attendance_recording_created_by` (`created_by`),
  KEY `fk_attendance_recording_updated_by` (`updated_by`),
  KEY `currency` (`currency`),
  KEY `company` (`company`),
  KEY `employee` (`employee`),
  CONSTRAINT `attendance_recording_ibfk_100` FOREIGN KEY (`currency`) REFERENCES `currency` (`id`),
  CONSTRAINT `attendance_recording_ibfk_101` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `attendance_recording_ibfk_102` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `attendance_recording_ibfk_103` FOREIGN KEY (`company`) REFERENCES `contractor_registration` (`id`),
  CONSTRAINT `attendance_recording_ibfk_104` FOREIGN KEY (`employee`) REFERENCES `users` (`id`),
  CONSTRAINT `attendance_recording_ibfk_97` FOREIGN KEY (`training_program`) REFERENCES `training_schedule` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `attendance_recording_ibfk_98` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `attendance_recording_ibfk_99` 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: attendance_recording_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `attendance_recording_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `attendance_recording_id` int DEFAULT NULL,
  `file` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `classification` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `attendance_recording_id` (`attendance_recording_id`),
  KEY `classification` (`classification`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `attendance_recording_docs_ibfk_37` FOREIGN KEY (`attendance_recording_id`) REFERENCES `attendance_recording` (`id`),
  CONSTRAINT `attendance_recording_docs_ibfk_38` FOREIGN KEY (`classification`) REFERENCES `file_classification` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `attendance_recording_docs_ibfk_39` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: audit_category
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `audit_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` 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 `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `audit_category_ibfk_55` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_category_ibfk_56` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_category_ibfk_57` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: audit_execution
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `audit_execution` (
  `id` int NOT NULL AUTO_INCREMENT,
  `scheduled_audit` int DEFAULT NULL,
  `actual_audit_start_date_time` datetime DEFAULT NULL,
  `actual_audit_end_date_time` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `audit_protocol` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `audit_execution_id` varchar(500) 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,
  `evaluation_tool_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `scheduled_audit` (`scheduled_audit`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `audit_execution_ibfk_71` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_execution_ibfk_72` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_execution_ibfk_73` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_execution_ibfk_74` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_execution_ibfk_75` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: audit_log
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `audit_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `repository_id` int DEFAULT NULL,
  `document_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `action_type` enum(
  'upload',
  'edit',
  'approvals',
  'delete',
  'download',
  'view'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `repository_id` (`repository_id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `audit_log_ibfk_131` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`),
  CONSTRAINT `audit_log_ibfk_132` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_log_ibfk_133` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_log_ibfk_134` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_log_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: audit_record
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `audit_record` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `audit_execution` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `audit_record` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `total_point` int DEFAULT NULL,
  `earned_points` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted` tinyint DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `audit_execution` (`audit_execution`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `audit_record_ibfk_57` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_record_ibfk_58` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_record_ibfk_59` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_record_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: audit_scheduling
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `audit_scheduling` (
  `id` int NOT NULL AUTO_INCREMENT,
  `location` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` enum('To-do', 'In progress', 'Executed', 'Overdue') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'To-do',
  `technological_resources` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` int DEFAULT NULL,
  `schedule_type` enum(
  'audit',
  'inspection',
  'monitoring',
  'assessment',
  'surveillance',
  'certifications',
  'testing-examination'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `linked_to` enum(
  'Permits',
  'SOPs',
  'Policy',
  'Action',
  'Incident',
  'NCR',
  'Business Continuity'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `module` int DEFAULT NULL,
  `sub_module` int DEFAULT NULL,
  `planned_start_date` date DEFAULT NULL,
  `planned_end_date` date DEFAULT NULL,
  `actual_start_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `want_to_repeat` int DEFAULT NULL,
  `source` enum('Internal', 'External') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lead_person` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `reference_number` int DEFAULT NULL,
  `participant_type` enum('Internal', 'External') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stakeholder` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `record_name` int DEFAULT NULL,
  `repeat_when` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cycles` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `members` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `owner` int DEFAULT NULL,
  `participants` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `scope` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `critical_business` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `financial_resource` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `opening_meeting_agenda` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `closing_meeting_agenda` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `evaluation_tool_status` enum(
  'Applicable',
  'Not Applicable',
  'Added',
  'Not Added'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `evaluation_tool` int DEFAULT NULL,
  `report` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `audit_record` int DEFAULT NULL,
  `ddrm_id` int DEFAULT NULL,
  `operating_budget_value` int DEFAULT NULL,
  `operating_budget_currency` int DEFAULT NULL,
  `capital_budget_value` int DEFAULT NULL,
  `capital_budget_currency` int DEFAULT NULL,
  `unique_id` varchar(100) 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 `department` (`department`),
  KEY `type` (`type`),
  KEY `module` (`module`),
  KEY `sub_module` (`sub_module`),
  KEY `evaluation_tool` (`evaluation_tool`),
  KEY `audit_record` (`audit_record`),
  KEY `ddrm_id` (`ddrm_id`),
  KEY `operating_budget_currency` (`operating_budget_currency`),
  KEY `capital_budget_currency` (`capital_budget_currency`),
  CONSTRAINT `audit_scheduling_ibfk_181` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_scheduling_ibfk_182` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_scheduling_ibfk_183` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_scheduling_ibfk_184` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_scheduling_ibfk_185` FOREIGN KEY (`type`) REFERENCES `audit_type` (`id`),
  CONSTRAINT `audit_scheduling_ibfk_186` FOREIGN KEY (`module`) REFERENCES `sidebar` (`id`),
  CONSTRAINT `audit_scheduling_ibfk_187` FOREIGN KEY (`sub_module`) REFERENCES `sidebar` (`id`),
  CONSTRAINT `audit_scheduling_ibfk_188` FOREIGN KEY (`evaluation_tool`) REFERENCES `audit_template` (`id`),
  CONSTRAINT `audit_scheduling_ibfk_189` FOREIGN KEY (`audit_record`) REFERENCES `audit_record` (`id`),
  CONSTRAINT `audit_scheduling_ibfk_190` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`),
  CONSTRAINT `audit_scheduling_ibfk_191` FOREIGN KEY (`operating_budget_currency`) REFERENCES `currency` (`id`),
  CONSTRAINT `audit_scheduling_ibfk_192` FOREIGN KEY (`capital_budget_currency`) REFERENCES `currency` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: audit_template
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `audit_template` (
  `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,
  `quiz` tinyint DEFAULT '0',
  `employee_id` 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,
  `sections` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `passing_marks` int DEFAULT '0',
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permit_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permit_relation` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `audit_template_ibfk_49` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_template_ibfk_50` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_template_ibfk_51` 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: audit_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `audit_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,
  `type` 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 `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `audit_type_ibfk_43` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_type_ibfk_44` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `audit_type_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: banks
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `banks` (
  `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 `banks_ibfk_73` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `banks_ibfk_74` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `banks_ibfk_75` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: bcp_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `bcp_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `bcp_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',
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bcp_id` (`bcp_id`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `bcp_docs_ibfk_5` FOREIGN KEY (`bcp_id`) REFERENCES `bcp_management` (`id`),
  CONSTRAINT `bcp_docs_ibfk_6` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: bcp_management
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `bcp_management` (
  `id` int NOT NULL AUTO_INCREMENT,
  `business_activity_details` int DEFAULT NULL,
  `plan_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `plan_reference_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `plan_version_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `plan_effective_start_date` date DEFAULT NULL,
  `plan_effective_end_date` date DEFAULT NULL,
  `crisis_management_team_leader` int DEFAULT NULL,
  `crisis_management_team_leader_responsibility` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `emergency_response_coordinator` int DEFAULT NULL,
  `emergency_response_coordinator_responsibility` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `technical_recovery_lead` int DEFAULT NULL,
  `technical_recovery_lead_responsibility` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `communication_officer` int DEFAULT NULL,
  `communication_officer_responsibility` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `legal_and_compliance_officer` int DEFAULT NULL,
  `legal_and_compliance_officer_responsibility` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `hr_representative` int DEFAULT NULL,
  `hr_representative_responsibility` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `operation_recovery_lead` int DEFAULT NULL,
  `operation_recovery_lead_responsibility` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `finance_and_administration_lead` int DEFAULT NULL,
  `finance_and_administration_lead_responsibility` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `facilities_and_physical_infrastructure` int DEFAULT NULL,
  `facilities_and_physical_infrastructure_responsibility` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `security_lead` int DEFAULT NULL,
  `security_lead_responsibility` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `crisis_documentation_officer` int DEFAULT NULL,
  `crisis_documentation_officer_responsibility` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `plan_activation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `lesson_learned_protocol` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `plan_testing_and_maintenance` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `testing_frequency` enum(
  'Daily',
  'Weekly',
  'Bi-Weekly',
  'Monthly',
  'Bi-Monthly',
  'Quarterly',
  'Bi-Annually',
  'Annually'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Daily',
  `testing_cycles` int DEFAULT NULL,
  `testing_start_date` date DEFAULT NULL,
  `next_review_date` date DEFAULT NULL,
  `bcp_requirement_analysis_id` int DEFAULT NULL,
  `status` enum('reviewing', 'approval', 'approved', 'rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'reviewing',
  `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,
  `deputy_crisis_management_team_leader` int DEFAULT NULL,
  `deputy_crisis_management_team_leader_responsibility` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `supply_chain_and_procurement_lead` int DEFAULT NULL,
  `supply_chain_and_procurement_lead_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `external_liaison_officer` int DEFAULT NULL,
  `external_liaison_officer_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `risk_management_officer` int DEFAULT NULL,
  `risk_management_officer_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `plan_external_emergency_numbers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `purpose` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `objective` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `scope` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `assumptions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `impact_analysis` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `recovery_strategies` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `communication_plan` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_response_procedures` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `upload_documents` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `reviewer` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `approver` 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 `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `deputy_crisis_management_team_leader` (`deputy_crisis_management_team_leader`),
  CONSTRAINT `bcp_management_ibfk_121` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_management_ibfk_122` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_management_ibfk_123` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_management_ibfk_124` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_management_ibfk_125` FOREIGN KEY (`deputy_crisis_management_team_leader`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: bcp_management_comment
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `bcp_management_comment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bcp_management_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,
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bcp_management_id` (`bcp_management_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `bcp_management_comment_ibfk_7` FOREIGN KEY (`bcp_management_id`) REFERENCES `bcp_management` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_management_comment_ibfk_8` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_management_comment_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: bcp_requirement_analysis
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `bcp_requirement_analysis` (
  `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_0900_ai_ci,
  `location` int DEFAULT NULL,
  `tasks` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `assessment_date` date DEFAULT NULL,
  `lead_assessor` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `assessment_participants` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `impact_analysis` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `overall_impact_score_24_hrs` int DEFAULT NULL,
  `overall_impact_score_7_days` int DEFAULT NULL,
  `overall_impact_score_7_days_plus` int DEFAULT NULL,
  `aggregate_activity_score` float DEFAULT NULL,
  `aggregate_information_required` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `occur_in_other_location` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `rto_value` int DEFAULT NULL,
  `rto_duration` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mtpod_value` int DEFAULT NULL,
  `mtpod_duration` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `rating_explanation` int DEFAULT NULL,
  `rating_undertaken` 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,
  `rpo_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `rpo_duration` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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 `bcp_requirement_analysis_ibfk_10` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_requirement_analysis_ibfk_11` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_requirement_analysis_ibfk_12` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_requirement_analysis_ibfk_9` 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: bcp_testing
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `bcp_testing` (
  `id` int NOT NULL AUTO_INCREMENT,
  `planned_testing_date` date DEFAULT NULL,
  `plan_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `actual_testing_date` date DEFAULT NULL,
  `location` int DEFAULT NULL,
  `method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `objective` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `team_members` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `external_participants` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `test_scenario_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `assumptions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `critical_function_tested` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `recovery_objectives` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `test_outcomes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `issues_identified` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `recovery_times` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `plan_deviations` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `improvements_identified` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `key_learnings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `team_feedback` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `document_attachment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reviewer` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `bcp_management_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,
  `action_taken` int DEFAULT NULL,
  `test_coordinator` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `location` (`location`),
  KEY `bcp_management_id` (`bcp_management_id`),
  KEY `test_coordinator` (`test_coordinator`),
  CONSTRAINT `bcp_testing_ibfk_15` FOREIGN KEY (`location`) REFERENCES `location` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_testing_ibfk_16` FOREIGN KEY (`bcp_management_id`) REFERENCES `bcp_management` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_testing_ibfk_17` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_testing_ibfk_18` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_testing_ibfk_19` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_testing_ibfk_20` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `bcp_testing_ibfk_21` FOREIGN KEY (`test_coordinator`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: bcp_testing_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `bcp_testing_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `bcp_testing_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,
  `ddrm_id` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `bcp_testing_id` (`bcp_testing_id`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `bcp_testing_docs_ibfk_5` FOREIGN KEY (`bcp_testing_id`) REFERENCES `bcp_testing` (`id`),
  CONSTRAINT `bcp_testing_docs_ibfk_6` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: best_practice
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `best_practice` (
  `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 `best_practice_ibfk_31` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `best_practice_ibfk_32` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `best_practice_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: body_part
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `body_part` (
  `id` int NOT NULL AUTO_INCREMENT,
  `label` varchar(255) DEFAULT NULL,
  `parent_id` 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_static` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `parent_id` (`parent_id`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `body_part_ibfk_10` FOREIGN KEY (`parent_id`) REFERENCES `body_part` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `body_part_ibfk_11` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `body_part_ibfk_12` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 230 DEFAULT CHARSET = latin1;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: business_processes
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `business_processes` (
  `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,
  `unique_id` varchar(255) DEFAULT NULL,
  `organization` longtext,
  `is_static` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `department` (`department`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `business_processes_ibfk_7` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `business_processes_ibfk_8` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `business_processes_ibfk_9` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 271 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: category
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `category` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `description` longtext,
  `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,
  `parent` int DEFAULT NULL,
  `unique_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `category_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `category_ibfk_2` 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: category_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `category_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,
  `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 `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `category_type_ibfk_52` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `category_type_ibfk_53` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `category_type_ibfk_54` 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: certificates
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `certificates` (
  `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 `certificates_ibfk_73` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `certificates_ibfk_74` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `certificates_ibfk_75` 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: cities
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `cities` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state_id` int unsigned DEFAULT NULL,
  `state_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country_id` int unsigned DEFAULT NULL,
  `country_code` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `latitude` decimal(10, 8) DEFAULT NULL,
  `longitude` decimal(11, 8) DEFAULT NULL,
  `region_id` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT '2014-01-01 06:31:01',
  `updated_at` datetime NOT NULL 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`)
) ENGINE = InnoDB AUTO_INCREMENT = 155135 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = COMPACT;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: collection_site
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `collection_site` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `description` longtext,
  `department` int DEFAULT NULL,
  `organization` longtext,
  `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,
  `unique_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `collection_site_ibfk_4` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `collection_site_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `collection_site_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: color
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `color` (
  `id` int NOT NULL AUTO_INCREMENT,
  `category_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `category_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `hex_color` varchar(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_category` (`category_id`, `category_type`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: command_center_location
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `command_center_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,
  `unique_id` varchar(250) 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 `command_center_location_ibfk_5` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `command_center_location_ibfk_6` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `command_center_location_ibfk_7` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `command_center_location_ibfk_8` 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: communication_method
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `communication_method` (
  `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(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`),
  CONSTRAINT `communication_method_ibfk_4` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `communication_method_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `communication_method_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: competencies_assessed
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `competencies_assessed` (
  `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 `competencies_assessed_ibfk_4` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `competencies_assessed_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `competencies_assessed_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: competency_assessment
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `competency_assessment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `assessment_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assessment_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assessment_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assessment_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `job_role` int DEFAULT NULL,
  `competencies_assessed` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assessor_date` date DEFAULT NULL,
  `assessor_name` int DEFAULT NULL,
  `assessor_method` int DEFAULT NULL,
  `assessor_duration` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assessment_location` int DEFAULT NULL,
  `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,
  `individual_assessment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `schedule_training` 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 `assessor_name` (`assessor_name`),
  KEY `assessor_method` (`assessor_method`),
  KEY `assessment_location` (`assessment_location`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `job_role` (`job_role`),
  CONSTRAINT `competency_assessment_ibfk_89` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `competency_assessment_ibfk_90` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `competency_assessment_ibfk_91` FOREIGN KEY (`job_role`) REFERENCES `roles` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `competency_assessment_ibfk_92` FOREIGN KEY (`assessor_name`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `competency_assessment_ibfk_93` FOREIGN KEY (`assessor_method`) REFERENCES `assessor_method` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `competency_assessment_ibfk_94` FOREIGN KEY (`assessment_location`) REFERENCES `location` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `competency_assessment_ibfk_95` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `competency_assessment_ibfk_96` 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: complaint_category
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `complaint_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` 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 `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `complaint_category_ibfk_10` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `complaint_category_ibfk_11` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `complaint_category_ibfk_12` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `complaint_category_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: contractor_agreements
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_agreements` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contractor` int DEFAULT NULL,
  `status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `form` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `public_url` 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_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `project` int DEFAULT NULL,
  `signing_method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Digital',
  `ddrm_id` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `total` int DEFAULT '0',
  `responses` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `contractor` (`contractor`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `project` (`project`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `contractor_agreements_ibfk_71` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_agreements_ibfk_72` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_agreements_ibfk_73` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_agreements_ibfk_74` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_agreements_ibfk_75` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_agreements_ibfk_76` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_agreements_ibfk_77` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_appointment_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_appointment_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `contractor_appointment_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 `contractor_appointment_id` (`contractor_appointment_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_appointments
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_appointments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contractor` int DEFAULT NULL,
  `project` int DEFAULT NULL,
  `appointment_type_appoints` int DEFAULT '1',
  `appointed_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name_of_appointment` int DEFAULT NULL,
  `effective_date` date DEFAULT NULL,
  `end_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,
  `appoints_user_id` int DEFAULT NULL,
  `physical_location` int DEFAULT NULL,
  `appointed_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `appointed_role_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ddrm_id` int DEFAULT NULL,
  `signature_type` enum('Electronic', 'Manual') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Manual',
  PRIMARY KEY (`id`),
  KEY `contractor` (`contractor`),
  KEY `project` (`project`),
  KEY `appointed_user` (`appointed_user`),
  KEY `name_of_appointment` (`name_of_appointment`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `appoints_user_id` (`appoints_user_id`),
  KEY `physical_location` (`physical_location`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `contractor_appointments_ibfk_100` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`),
  CONSTRAINT `contractor_appointments_ibfk_101` FOREIGN KEY (`appoints_user_id`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_appointments_ibfk_102` FOREIGN KEY (`physical_location`) REFERENCES `location` (`id`),
  CONSTRAINT `contractor_appointments_ibfk_103` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`),
  CONSTRAINT `contractor_appointments_ibfk_94` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_appointments_ibfk_95` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_appointments_ibfk_96` FOREIGN KEY (`name_of_appointment`) REFERENCES `appointment_type` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_appointments_ibfk_97` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_appointments_ibfk_98` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_appointments_ibfk_99` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_attendance_recording
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_attendance_recording` (
  `id` int NOT NULL AUTO_INCREMENT,
  `actual_training_service` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attendees` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `training_actual_date` date DEFAULT NULL,
  `training_program` int DEFAULT NULL,
  `training_start_date` date DEFAULT NULL,
  `training_total_cost` float DEFAULT NULL,
  `training_venue` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `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,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contractors` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `projects` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employee` int DEFAULT NULL,
  `group_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `role_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `provider_department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `training_program` (`training_program`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `contractor_attendance_recording_employee_foreign_idx` (`employee`),
  CONSTRAINT `contractor_attendance_recording_ibfk_49` FOREIGN KEY (`training_program`) REFERENCES `contractor_training_schedule` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_attendance_recording_ibfk_50` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_attendance_recording_ibfk_51` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_attendance_recording_ibfk_52` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_attendance_recording_ibfk_53` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_attendance_recording_ibfk_54` FOREIGN KEY (`employee`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_audit_protocol
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_audit_protocol` (
  `id` int NOT NULL AUTO_INCREMENT,
  `scheduled_audit` int DEFAULT NULL,
  `actual_audit_start_date_time` datetime DEFAULT NULL,
  `actual_audit_end_date_time` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `audit_protocol` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `audit_protocol_id` varchar(500) 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,
  `contractor` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `scheduled_audit` (`scheduled_audit`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `contractor` (`contractor`),
  CONSTRAINT `contractor_audit_protocol_ibfk_55` FOREIGN KEY (`scheduled_audit`) REFERENCES `contractor_audit_scheduling` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_protocol_ibfk_56` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_protocol_ibfk_57` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_protocol_ibfk_58` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_protocol_ibfk_59` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_protocol_ibfk_60` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_audit_record
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_audit_record` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(500) 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,
  `audit_record` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `total_point` int DEFAULT NULL,
  `earned_points` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted` tinyint DEFAULT '0',
  `audit_execution` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `contractor_audit_record_ibfk_37` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_record_ibfk_38` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_record_ibfk_39` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_record_ibfk_40` 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: contractor_audit_scheduling
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_audit_scheduling` (
  `id` int NOT NULL AUTO_INCREMENT,
  `location` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` enum('Todo', 'In progress', 'Executed', 'Overdue') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Todo',
  `technological_resources` 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,
  `deleted` int DEFAULT '0',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `contractor` int DEFAULT NULL,
  `project` int DEFAULT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` int DEFAULT NULL,
  `schedule_type` enum(
  'audit',
  'inspection',
  'monitoring',
  'assessment',
  'surveillance',
  'certifications',
  'testing-examination'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `linked_to` enum(
  'Permits',
  'SOPs',
  'Policy',
  'Action',
  'Incident',
  'NCR',
  'Business Continuity'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `module` int DEFAULT NULL,
  `sub_module` int DEFAULT NULL,
  `planned_start_date` date DEFAULT NULL,
  `planned_end_date` date DEFAULT NULL,
  `actual_start_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `want_to_repeat` int DEFAULT NULL,
  `source` enum('Internal', 'External') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lead_person` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `reference_number` int DEFAULT NULL,
  `participant_type` enum('Internal', 'External') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stakeholder` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `record_name` int DEFAULT NULL,
  `repeat_when` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cycles` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `members` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `owner` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `participants` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `scope` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `critical_business` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `financial_resource` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `opening_meeting_agenda` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `closing_meeting_agenda` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `evaluation_tool_status` enum(
  'Applicable',
  'Not Applicable',
  'Added',
  'Not Added'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `evaluation_tool` int DEFAULT NULL,
  `report` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ddrm_id` int DEFAULT NULL,
  `audit_record` int DEFAULT NULL,
  `operating_budget_value` int DEFAULT NULL,
  `operating_budget_currency` int DEFAULT NULL,
  `capital_budget_value` int DEFAULT NULL,
  `capital_budget_currency` 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 `contractor` (`contractor`),
  KEY `project` (`project`),
  KEY `type` (`type`),
  KEY `module` (`module`),
  KEY `sub_module` (`sub_module`),
  KEY `evaluation_tool` (`evaluation_tool`),
  KEY `ddrm_id` (`ddrm_id`),
  KEY `audit_record` (`audit_record`),
  CONSTRAINT `contractor_audit_scheduling_ibfk_109` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_scheduling_ibfk_110` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_scheduling_ibfk_111` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_scheduling_ibfk_112` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_scheduling_ibfk_113` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_scheduling_ibfk_114` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_audit_scheduling_ibfk_115` FOREIGN KEY (`type`) REFERENCES `audit_type` (`id`),
  CONSTRAINT `contractor_audit_scheduling_ibfk_116` FOREIGN KEY (`module`) REFERENCES `sidebar` (`id`),
  CONSTRAINT `contractor_audit_scheduling_ibfk_117` FOREIGN KEY (`sub_module`) REFERENCES `sidebar` (`id`),
  CONSTRAINT `contractor_audit_scheduling_ibfk_118` FOREIGN KEY (`evaluation_tool`) REFERENCES `audit_template` (`id`),
  CONSTRAINT `contractor_audit_scheduling_ibfk_119` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`),
  CONSTRAINT `contractor_audit_scheduling_ibfk_120` FOREIGN KEY (`audit_record`) REFERENCES `contractor_audit_record` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `contractor_id` int DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contractor_id` (`contractor_id`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `contractor_docs_ibfk_21` FOREIGN KEY (`contractor_id`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_docs_ibfk_22` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_document_repository
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_document_repository` (
  `id` int NOT NULL AUTO_INCREMENT,
  `document_type` int DEFAULT NULL,
  `associated_audit` int DEFAULT NULL,
  `document_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `allowed_access_to` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `audit_execution_id` varchar(500) 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,
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `document_type` (`document_type`),
  KEY `associated_audit` (`associated_audit`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `contractor_document_repository_ibfk_64` FOREIGN KEY (`document_type`) REFERENCES `document_type` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_document_repository_ibfk_65` FOREIGN KEY (`associated_audit`) REFERENCES `contractor_audit_protocol` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_document_repository_ibfk_66` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_document_repository_ibfk_67` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_document_repository_ibfk_68` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_document_repository_ibfk_69` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_document_repository_ibfk_70` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_forms
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_forms` (
  `id` int NOT NULL AUTO_INCREMENT,
  `form` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `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,
  `type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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 `contractor_forms_ibfk_31` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_forms_ibfk_32` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_forms_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: contractor_incident
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_incident` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_category` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `incident_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_report_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_incident` date DEFAULT NULL,
  `time_of_incident_reporting` time DEFAULT NULL,
  `time_of_incident_occurrence` time DEFAULT NULL,
  `initial_incident_severity` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `location` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_contractor` int DEFAULT '0',
  `contractor` int DEFAULT NULL,
  `save_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `current_impact` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `potential_impact` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `person_type` int DEFAULT '0',
  `internal_persons` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `external_person` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_reporting_by` int DEFAULT NULL,
  `notification` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `contractor` (`contractor`),
  KEY `incident_reporting_by` (`incident_reporting_by`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `contractor_incident_ibfk_49` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_incident_ibfk_50` FOREIGN KEY (`incident_reporting_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_incident_ibfk_51` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_incident_ibfk_52` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_incident_ibfk_53` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `contractor_incident_ibfk_54` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_incident_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_incident_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `contractor_incident_id` int DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contractor_incident_id` (`contractor_incident_id`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `contractor_incident_docs_ibfk_17` FOREIGN KEY (`contractor_incident_id`) REFERENCES `contractor_incident` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_incident_docs_ibfk_18` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_investigation
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_investigation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int DEFAULT NULL,
  `is_done` tinyint(1) 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` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `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,
  `person_involved_internal_external` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Internal',
  `involved_person` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `involved_role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `involved_department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `involved_role_in_incident` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `investigation_enquiry_outcome` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `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_category` int DEFAULT NULL,
  `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,
  `investigation_organization` 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,
  `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,
  `environment_consequence_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,
  `injured_person` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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` tinyint(1) DEFAULT NULL,
  `any_recoveries` tinyint(1) DEFAULT NULL,
  `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` int DEFAULT NULL,
  `impact_severity` int 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,
  `line_manager_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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 `incident_id` (`incident_id`),
  KEY `employee` (`employee`),
  KEY `actual_incident_category` (`actual_incident_category`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `contractor_investigation_ibfk_10` FOREIGN KEY (`actual_incident_category`) REFERENCES `incident_category` (`id`),
  CONSTRAINT `contractor_investigation_ibfk_11` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_investigation_ibfk_12` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_investigation_ibfk_13` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_investigation_ibfk_14` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_investigation_ibfk_8` FOREIGN KEY (`incident_id`) REFERENCES `contractor_incident` (`id`),
  CONSTRAINT `contractor_investigation_ibfk_9` FOREIGN KEY (`employee`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_investigation_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_investigation_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `contractor_investigation_id` int DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `file` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `deleted` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `contractor_investigation_id` (`contractor_investigation_id`),
  CONSTRAINT `contractor_investigation_docs_ibfk_1` FOREIGN KEY (`contractor_investigation_id`) REFERENCES `contractor_investigation` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_meeting
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_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,
  `external_participants` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `contractor` int DEFAULT NULL,
  `project` int DEFAULT NULL,
  `unique_id` 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`),
  KEY `contractor` (`contractor`),
  KEY `project` (`project`),
  CONSTRAINT `contractor_meeting_ibfk_71` FOREIGN KEY (`meeting_owner`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_ibfk_72` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_ibfk_73` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_ibfk_74` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_ibfk_75` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_ibfk_76` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`),
  CONSTRAINT `contractor_meeting_ibfk_77` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_meeting_notes
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_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,
  `completed_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,
  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 `contractor_meeting_notes_ibfk_41` FOREIGN KEY (`meeting_recording_id`) REFERENCES `contractor_meeting_recording` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_notes_ibfk_42` FOREIGN KEY (`raised_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_notes_ibfk_43` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_notes_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: contractor_meeting_recording
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_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,
  `meeting_notes` 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',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `for_approval` int DEFAULT '0',
  `approval_status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending',
  `send_to_employee` 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 `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 `contractor_meeting_recording_ibfk_61` FOREIGN KEY (`scheduled_meeting`) REFERENCES `contractor_meeting` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_recording_ibfk_62` FOREIGN KEY (`meeting_scriber`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_recording_ibfk_63` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_recording_ibfk_64` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_recording_ibfk_65` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_meeting_recording_ibfk_66` 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: contractor_ncr_recording
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_ncr_recording` (
  `id` int NOT NULL AUTO_INCREMENT,
  `organization` int DEFAULT NULL,
  `raised_by` 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,
  `standard_clause_name` varchar(255) DEFAULT NULL,
  `contravened_contract_clause` varchar(255) DEFAULT NULL,
  `ncr_categories` longtext,
  `classification` varchar(255) DEFAULT NULL,
  `raised_type` int DEFAULT NULL,
  `internal_raised_business` int 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` varchar(255) DEFAULT NULL,
  `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` json DEFAULT NULL,
  `technological_resources` json DEFAULT NULL,
  `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,
  `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,
  `contractor` int DEFAULT NULL,
  `project` int DEFAULT NULL,
  `origin_ncr` varchar(255) DEFAULT NULL,
  `type_ncr` varchar(255) DEFAULT NULL,
  `raised_by_email` varchar(255) DEFAULT NULL,
  `raised_by_surname` varchar(255) DEFAULT NULL,
  `raised_by_project` varchar(255) DEFAULT NULL,
  `ncr_title` varchar(255) DEFAULT NULL,
  `unique_id` varchar(255) DEFAULT NULL,
  `reference_no` varchar(250) DEFAULT NULL,
  `provision_of_the_requirement` varchar(255) DEFAULT NULL,
  `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,
  `specify_origin_ncr` varchar(255) DEFAULT NULL,
  `specify_type_ncr` varchar(255) DEFAULT NULL,
  `corrective_action_been_raised` int DEFAULT '0',
  `preventative_action_been_raised` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `name_of_standard` (`name_of_standard`),
  KEY `raised_type` (`raised_type`),
  KEY `internal_raised_business` (`internal_raised_business`),
  KEY `internal_raised_against_business` (`internal_raised_against_business`),
  KEY `internal_raised_against_department` (`internal_raised_against_department`),
  KEY `internal_responsible_person` (`internal_responsible_person`),
  KEY `corrective_responsible_department` (`corrective_responsible_department`),
  KEY `corrective_responsible_person` (`corrective_responsible_person`),
  KEY `preventative_responsible_department` (`preventative_responsible_department`),
  KEY `preventative_responsible_person` (`preventative_responsible_person`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `contractor` (`contractor`),
  KEY `project` (`project`),
  CONSTRAINT `contractor_ncr_recording_ibfk_113` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_ncr_recording_ibfk_114` FOREIGN KEY (`name_of_standard`) REFERENCES `name_of_standard` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_ncr_recording_ibfk_115` FOREIGN KEY (`internal_raised_business`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_ncr_recording_ibfk_116` FOREIGN KEY (`internal_raised_against_business`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_ncr_recording_ibfk_117` FOREIGN KEY (`internal_raised_against_department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_ncr_recording_ibfk_118` FOREIGN KEY (`internal_responsible_person`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_ncr_recording_ibfk_119` FOREIGN KEY (`corrective_responsible_department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_ncr_recording_ibfk_120` FOREIGN KEY (`corrective_responsible_person`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_ncr_recording_ibfk_121` FOREIGN KEY (`preventative_responsible_department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_ncr_recording_ibfk_122` FOREIGN KEY (`preventative_responsible_person`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_ncr_recording_ibfk_123` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_ncr_recording_ibfk_124` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_ncr_recording_ibfk_125` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`),
  CONSTRAINT `contractor_ncr_recording_ibfk_126` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = latin1;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_permit_license
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_permit_license` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` int DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `permit_holder` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permit_license_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permit_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `issuing_authority` int DEFAULT NULL,
  `permit_license_id` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `applicable_section_of_legislation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `issue_date` datetime DEFAULT NULL,
  `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,
  `contractor` int DEFAULT NULL,
  `project` int DEFAULT NULL,
  `effective_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `employee` int DEFAULT NULL,
  `permit_license_category` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `condition_description` 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 `permit_holder` (`permit_holder`),
  KEY `issuing_authority` (`issuing_authority`),
  KEY `fk_contractor_permit_license_contractor` (`contractor`),
  KEY `fk_contractor_permit_license_project` (`project`),
  KEY `fk_contractor_permit_license_organization` (`organization`),
  KEY `fk_contractor_permit_license_created_by` (`created_by`),
  KEY `fk_contractor_permit_license_updated_by` (`updated_by`),
  KEY `department` (`department`),
  KEY `title` (`title`),
  KEY `employee` (`employee`),
  CONSTRAINT `contractor_permit_license_ibfk_82` FOREIGN KEY (`title`) REFERENCES `permit_license` (`id`),
  CONSTRAINT `contractor_permit_license_ibfk_83` FOREIGN KEY (`issuing_authority`) REFERENCES `issuing_authority` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_permit_license_ibfk_84` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_permit_license_ibfk_85` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_permit_license_ibfk_86` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `contractor_permit_license_ibfk_87` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`),
  CONSTRAINT `contractor_permit_license_ibfk_88` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_permit_license_ibfk_89` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_permit_license_ibfk_90` FOREIGN KEY (`employee`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_permit_license_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_permit_license_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `contractor_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 NOT NULL DEFAULT '0',
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contractor_permit_license_id` (`contractor_permit_license_id`),
  KEY `ddrm_id` (`ddrm_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_pre_site
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_pre_site` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contractor` int DEFAULT NULL,
  `form` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` varchar(100) 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,
  `responses` int DEFAULT '0',
  `project` int DEFAULT NULL,
  `total` int DEFAULT '0',
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contractor` (`contractor`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `project` (`project`),
  CONSTRAINT `contractor_pre_site_ibfk_61` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_pre_site_ibfk_62` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_pre_site_ibfk_63` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_pre_site_ibfk_64` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_pre_site_ibfk_65` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_pre_site_ibfk_66` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_pre_site_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_pre_site_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `pre_site_response_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 `fk_contractor_pre_site_docs_pre_site_response_id` (`pre_site_response_id`),
  CONSTRAINT `contractor_pre_site_docs_ibfk_1` FOREIGN KEY (`pre_site_response_id`) REFERENCES `contractor_pre_site` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_questionnaire
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_questionnaire` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contractor` int DEFAULT NULL,
  `status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `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,
  `project` int DEFAULT NULL,
  `total` int DEFAULT '0',
  `responses` int DEFAULT '0',
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contractor` (`contractor`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `project` (`project`),
  CONSTRAINT `contractor_questionnaire_ibfk_61` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_questionnaire_ibfk_62` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_questionnaire_ibfk_63` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_questionnaire_ibfk_64` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_questionnaire_ibfk_65` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_questionnaire_ibfk_66` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_questionnaire_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_questionnaire_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `questionnaire_response_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',
  PRIMARY KEY (`id`),
  KEY `questionnaire_response_id` (`questionnaire_response_id`),
  CONSTRAINT `contractor_questionnaire_docs_ibfk_1` FOREIGN KEY (`questionnaire_response_id`) REFERENCES `questionnaire_response` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_registration
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_registration` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contractor_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cipc_registration_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tax_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bbbee_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `vat_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_terms` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type_of_work` 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,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `physical_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `classification_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `classification_criticality` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `classification_impact` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `classification_category` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contractor_employees` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `emergency_contacts` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `vat_registered` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'No',
  `bank` int DEFAULT NULL,
  `work_service_provided` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `contractor_owner` 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 `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `bank` (`bank`),
  CONSTRAINT `contractor_registration_ibfk_121` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_registration_ibfk_122` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_registration_ibfk_123` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_registration_ibfk_124` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_registration_ibfk_125` FOREIGN KEY (`bank`) REFERENCES `banks` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_risk_assessment
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_risk_assessment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `risk_register` int DEFAULT NULL,
  `risk_name` varchar(255) 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(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inherent_consequence` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `control_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `control_design_intent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `control_effectiveness` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `control_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `opportunity_identification` int DEFAULT '0',
  `priority_confirmation` int DEFAULT '0',
  `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,
  `contractor` int DEFAULT NULL,
  `inherent_risk_rating` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inherent_risk_ranking` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_control_known` int DEFAULT '0' COMMENT 'value (0 or 1)',
  `residual_risk_rating` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `residual_risk_ranking` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `opportunity_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `project` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `risk_register` (`risk_register`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `contractor` (`contractor`),
  KEY `project` (`project`),
  CONSTRAINT `contractor_risk_assessment_ibfk_71` FOREIGN KEY (`risk_register`) REFERENCES `contractor_risk_register` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_risk_assessment_ibfk_72` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_risk_assessment_ibfk_73` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_risk_assessment_ibfk_74` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_risk_assessment_ibfk_75` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_risk_assessment_ibfk_76` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_risk_assessment_ibfk_77` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_risk_register
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_risk_register` (
  `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,
  `risk_assessment_facilitator` int 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,
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `risk_assessment_facilitator` (`risk_assessment_facilitator`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `contractor_risk_register_ibfk_31` FOREIGN KEY (`risk_assessment_facilitator`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_risk_register_ibfk_32` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_risk_register_ibfk_33` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_risk_register_ibfk_34` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_risk_register_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: contractor_safe_working
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_safe_working` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contractor` int DEFAULT NULL,
  `status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Pending',
  `form` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `public_url` 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_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `signing_method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Digital',
  `document` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `project` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contractor` (`contractor`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `project` (`project`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `contractor_safe_working_ibfk_56` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_safe_working_ibfk_57` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_safe_working_ibfk_58` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_safe_working_ibfk_59` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_safe_working_ibfk_60` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_safe_working_ibfk_61` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`),
  CONSTRAINT `contractor_safe_working_ibfk_62` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_safe_working_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_safe_working_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `contractor_safe_working_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',
  PRIMARY KEY (`id`),
  KEY `contractor_safe_working_id` (`contractor_safe_working_id`),
  CONSTRAINT `contractor_safe_working_docs_ibfk_1` FOREIGN KEY (`contractor_safe_working_id`) REFERENCES `contractor_safe_working` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_site_access
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_site_access` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contractor` 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,
  `project` int DEFAULT NULL,
  `signing_method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `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 `contractor` (`contractor`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `project` (`project`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `contractor_site_access_ibfk_57` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_site_access_ibfk_58` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_site_access_ibfk_59` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_site_access_ibfk_60` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_site_access_ibfk_61` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_site_access_ibfk_62` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_site_access_ibfk_63` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_site_access_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_site_access_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `contractor_site_access_id` int DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleted` int DEFAULT '0',
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contractor_site_access_id` (`contractor_site_access_id`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `contractor_site_access_docs_ibfk_17` FOREIGN KEY (`contractor_site_access_id`) REFERENCES `contractor_site_access` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_site_access_docs_ibfk_18` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_training_attendance_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_training_attendance_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `attendance_recording_id` int DEFAULT NULL,
  `file` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `classification` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `attendance_recording_id` (`attendance_recording_id`),
  KEY `classification` (`classification`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `contractor_training_attendance_docs_ibfk_25` FOREIGN KEY (`attendance_recording_id`) REFERENCES `contractor_attendance_recording` (`id`),
  CONSTRAINT `contractor_training_attendance_docs_ibfk_26` FOREIGN KEY (`classification`) REFERENCES `file_classification` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `contractor_training_attendance_docs_ibfk_27` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contractor_training_schedule
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `contractor_training_schedule` (
  `id` int NOT NULL AUTO_INCREMENT,
  `training_program` int DEFAULT NULL,
  `employees` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `training_provider` enum('Internal', 'External') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `external_provider` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `internal_provider_department` int DEFAULT NULL,
  `provider_department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `period` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `schedule_type` int DEFAULT NULL,
  `single_day` date DEFAULT NULL,
  `single_start_time` time DEFAULT NULL,
  `single_end_time` time DEFAULT NULL,
  `multiple_day` date DEFAULT NULL,
  `multiple_end_day` date DEFAULT NULL,
  `calendar_start_date` date DEFAULT NULL,
  `calendar_end_date` date DEFAULT NULL,
  `schedule_time` 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,
  `contractors` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `projects` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `venue_type` enum('On-Site', 'Off-Site') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `facilitator_type` enum('Internal', 'External') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Internal',
  `internal_emp_name` int DEFAULT NULL,
  `company_name` int DEFAULT NULL,
  `facilitator_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `financial_year` 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 `training_program` (`training_program`),
  KEY `internal_provider_department` (`internal_provider_department`),
  KEY `fk_contractor_training_schedule_department` (`department`),
  KEY `fk_contractor_training_schedule_organization` (`organization`),
  KEY `fk_contractor_training_schedule_created_by` (`created_by`),
  KEY `fk_contractor_training_schedule_updated_by` (`updated_by`),
  KEY `company_name` (`company_name`),
  CONSTRAINT `contractor_training_schedule_ibfk_64` FOREIGN KEY (`training_program`) REFERENCES `training_need_analysis` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_training_schedule_ibfk_65` FOREIGN KEY (`internal_provider_department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_training_schedule_ibfk_66` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_training_schedule_ibfk_67` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_training_schedule_ibfk_68` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_training_schedule_ibfk_69` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `contractor_training_schedule_ibfk_70` FOREIGN KEY (`company_name`) REFERENCES `contractor_registration` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: control_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `control_type` (
  `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,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `control_type_ibfk_1` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `control_type_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `control_type_ibfk_3` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: country
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `country` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `iso3` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `numeric_code` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `iso2` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phonecode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `capital` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency_symbol` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tld` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `native` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `region` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `region_id` int unsigned DEFAULT NULL,
  `subregion` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `subregion_id` int unsigned DEFAULT NULL,
  `nationality` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `timezones` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `translations` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `latitude` decimal(10, 8) DEFAULT NULL,
  `longitude` decimal(11, 8) DEFAULT NULL,
  `emoji` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emojiU` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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_continent` (`region_id`),
  KEY `country_subregion` (`subregion_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 251 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: crisis_location
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `crisis_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,
  `unique_id` varchar(250) 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 `crisis_location_ibfk_10` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `crisis_location_ibfk_11` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `crisis_location_ibfk_12` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `crisis_location_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: crisis_management
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `crisis_management` (
  `id` int NOT NULL AUTO_INCREMENT,
  `crisis_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `crisis_occurrence_date` date DEFAULT NULL,
  `crisis_time` time DEFAULT NULL,
  `crisis_location` int DEFAULT NULL,
  `crisis_type` int DEFAULT NULL,
  `crisis_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `crisis_declared_by` int DEFAULT NULL,
  `declaration_effective_date` date DEFAULT NULL,
  `declaration_time` time DEFAULT NULL,
  `declaration_reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `bcp_activation` enum('Yes', 'No') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bcp_management_id` int DEFAULT NULL,
  `initial_action_taken` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `mobilization_time` time DEFAULT NULL,
  `command_center_location` int DEFAULT NULL,
  `crisis_response_actions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `immediate_decisions_made` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `external_parties_contacted` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `crisis_communications` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `affected_areas` int DEFAULT NULL,
  `critical_system_affected` int DEFAULT NULL,
  `impact_of_operations` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `currency` int DEFAULT NULL,
  `value` decimal(10, 0) DEFAULT NULL,
  `recovery_time_objective` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `crisis_need_to_be_escalated` enum('Yes', 'No') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `escalation_reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `escalation_date` date DEFAULT NULL,
  `escalation_time` time DEFAULT NULL,
  `escalation_to` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `escalation_user_id` int DEFAULT NULL,
  `escalation_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `crisis_management_team_leader` int DEFAULT NULL,
  `crisis_management_team_leader_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deputy_crisis_management_team_leader` int DEFAULT NULL,
  `deputy_crisis_management_team_leader_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `emergency_response_coordinator` int DEFAULT NULL,
  `emergency_response_coordinator_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `technical_recovery_lead` int DEFAULT NULL,
  `technical_recovery_lead_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `communication_officer` int DEFAULT NULL,
  `communication_officer_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `legal_and_compliance_officer` int DEFAULT NULL,
  `legal_and_compliance_officer_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `hr_representative` int DEFAULT NULL,
  `hr_representative_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `operation_recovery_lead` int DEFAULT NULL,
  `operation_recovery_lead_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `finance_and_administration_lead` int DEFAULT NULL,
  `finance_and_administration_lead_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `facilities_and_physical_infrastructure` int DEFAULT NULL,
  `facilities_and_physical_infrastructure_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `supply_chain_and_procurement_lead` int DEFAULT NULL,
  `supply_chain_and_procurement_lead_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `external_liaison_officer` int DEFAULT NULL,
  `external_liaison_officer_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `security_lead` int DEFAULT NULL,
  `security_lead_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `risk_management_officer` int DEFAULT NULL,
  `risk_management_officer_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `crisis_documentation_officer` int DEFAULT NULL,
  `crisis_documentation_official_responsibilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `resource_allocation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deactivation_date` date DEFAULT NULL,
  `deactivation_time` time DEFAULT NULL,
  `deactivation_reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deactivation_user_id` int DEFAULT NULL,
  `restoration_of_services` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `step_taken_for_resolution` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `action_plan` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `areas_of_improvement` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `lessons_learned` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `post_crisis_review_date` date 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,
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `crisis_location` (`crisis_location`),
  KEY `crisis_type` (`crisis_type`),
  KEY `crisis_declared_by` (`crisis_declared_by`),
  KEY `bcp_management_id` (`bcp_management_id`),
  KEY `command_center_location` (`command_center_location`),
  KEY `affected_areas` (`affected_areas`),
  KEY `critical_system_affected` (`critical_system_affected`),
  KEY `currency` (`currency`),
  KEY `escalation_user_id` (`escalation_user_id`),
  KEY `crisis_management_team_leader` (`crisis_management_team_leader`),
  KEY `deputy_crisis_management_team_leader` (`deputy_crisis_management_team_leader`),
  KEY `emergency_response_coordinator` (`emergency_response_coordinator`),
  KEY `technical_recovery_lead` (`technical_recovery_lead`),
  KEY `communication_officer` (`communication_officer`),
  KEY `legal_and_compliance_officer` (`legal_and_compliance_officer`),
  KEY `hr_representative` (`hr_representative`),
  KEY `operation_recovery_lead` (`operation_recovery_lead`),
  KEY `finance_and_administration_lead` (`finance_and_administration_lead`),
  KEY `facilities_and_physical_infrastructure` (`facilities_and_physical_infrastructure`),
  KEY `supply_chain_and_procurement_lead` (`supply_chain_and_procurement_lead`),
  KEY `external_liaison_officer` (`external_liaison_officer`),
  KEY `security_lead` (`security_lead`),
  KEY `risk_management_officer` (`risk_management_officer`),
  KEY `crisis_documentation_officer` (`crisis_documentation_officer`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `crisis_management_ibfk_29` FOREIGN KEY (`crisis_location`) REFERENCES `location` (`id`),
  CONSTRAINT `crisis_management_ibfk_30` FOREIGN KEY (`crisis_type`) REFERENCES `crisis_type` (`id`),
  CONSTRAINT `crisis_management_ibfk_31` FOREIGN KEY (`crisis_declared_by`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_32` FOREIGN KEY (`bcp_management_id`) REFERENCES `bcp_management` (`id`),
  CONSTRAINT `crisis_management_ibfk_33` FOREIGN KEY (`command_center_location`) REFERENCES `location` (`id`),
  CONSTRAINT `crisis_management_ibfk_34` FOREIGN KEY (`affected_areas`) REFERENCES `affected_area` (`id`),
  CONSTRAINT `crisis_management_ibfk_35` FOREIGN KEY (`critical_system_affected`) REFERENCES `critical_system_affected` (`id`),
  CONSTRAINT `crisis_management_ibfk_36` FOREIGN KEY (`currency`) REFERENCES `currency` (`id`),
  CONSTRAINT `crisis_management_ibfk_37` FOREIGN KEY (`escalation_user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_38` FOREIGN KEY (`crisis_management_team_leader`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_39` FOREIGN KEY (`deputy_crisis_management_team_leader`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_40` FOREIGN KEY (`emergency_response_coordinator`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_41` FOREIGN KEY (`technical_recovery_lead`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_42` FOREIGN KEY (`communication_officer`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_43` FOREIGN KEY (`legal_and_compliance_officer`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_44` FOREIGN KEY (`hr_representative`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_45` FOREIGN KEY (`operation_recovery_lead`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_46` FOREIGN KEY (`finance_and_administration_lead`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_47` FOREIGN KEY (`facilities_and_physical_infrastructure`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_48` FOREIGN KEY (`supply_chain_and_procurement_lead`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_49` FOREIGN KEY (`external_liaison_officer`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_50` FOREIGN KEY (`security_lead`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_51` FOREIGN KEY (`risk_management_officer`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_52` FOREIGN KEY (`crisis_documentation_officer`) REFERENCES `users` (`id`),
  CONSTRAINT `crisis_management_ibfk_53` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `crisis_management_ibfk_54` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `crisis_management_ibfk_55` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `crisis_management_ibfk_56` 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: crisis_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `crisis_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(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`),
  CONSTRAINT `crisis_type_ibfk_4` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `crisis_type_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `crisis_type_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: critical_system_affected
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `critical_system_affected` (
  `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(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`),
  CONSTRAINT `critical_system_affected_ibfk_4` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `critical_system_affected_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `critical_system_affected_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: currency
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `currency` (
  `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 `currency_ibfk_46` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `currency_ibfk_47` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `currency_ibfk_48` 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: custom_action_creation
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `custom_action_creation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `template` int DEFAULT NULL,
  `task_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tag` int DEFAULT NULL,
  `label` int DEFAULT NULL,
  `task_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `successor_task_template` int DEFAULT NULL,
  `predecessor_task_template` int DEFAULT NULL,
  `role` int DEFAULT NULL,
  `skill` int DEFAULT NULL,
  `certificate` int DEFAULT NULL,
  `responsible_person` int DEFAULT NULL,
  `collaborators` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'To-Do',
  `task_budget` bigint DEFAULT NULL,
  `organization` int NOT NULL,
  `department` int DEFAULT NULL,
  `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,
  `priority` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `human_resource_requirements` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `technological_resources` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `type_of_resource` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `amount` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `estimated_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'stop',
  `category` int DEFAULT NULL,
  `sub_category` int DEFAULT NULL,
  `task_type` int DEFAULT NULL,
  `module` int DEFAULT NULL,
  `sub_module` int DEFAULT NULL,
  `record_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `table_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ddrm_id` int DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approved` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `fk_custom_action_creation_tag` (`tag`),
  KEY `fk_custom_action_creation_label` (`label`),
  KEY `fk_custom_action_creation_category` (`category`),
  KEY `fk_custom_action_creation_sub_category` (`sub_category`),
  KEY `fk_custom_action_creation_role` (`role`),
  KEY `fk_custom_action_creation_skill` (`skill`),
  KEY `fk_custom_action_creation_certificate` (`certificate`),
  KEY `fk_custom_action_creation_responsible_person` (`responsible_person`),
  KEY `fk_custom_action_creation_organization` (`organization`),
  KEY `fk_custom_action_creation_department` (`department`),
  KEY `fk_custom_action_creation_created_by` (`created_by`),
  KEY `fk_custom_action_creation_updated_by` (`updated_by`),
  KEY `template` (`template`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `custom_action_creation_ibfk_289` FOREIGN KEY (`template`) REFERENCES `action_template_list` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `custom_action_creation_ibfk_290` FOREIGN KEY (`tag`) REFERENCES `category` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `custom_action_creation_ibfk_291` FOREIGN KEY (`label`) REFERENCES `labels` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `custom_action_creation_ibfk_292` FOREIGN KEY (`role`) REFERENCES `roles` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `custom_action_creation_ibfk_293` FOREIGN KEY (`certificate`) REFERENCES `certificates` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `custom_action_creation_ibfk_294` FOREIGN KEY (`responsible_person`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `custom_action_creation_ibfk_295` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`),
  CONSTRAINT `custom_action_creation_ibfk_296` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `custom_action_creation_ibfk_297` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `custom_action_creation_ibfk_298` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `custom_action_creation_ibfk_299` FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `custom_action_creation_ibfk_300` FOREIGN KEY (`sub_category`) REFERENCES `category` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: custom_task_creation
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `custom_task_creation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `template` int NOT NULL,
  `task_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `task_type` int DEFAULT NULL,
  `module` int DEFAULT NULL,
  `sub_module` int DEFAULT NULL,
  `record_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tag` int DEFAULT NULL,
  `label` int DEFAULT NULL,
  `estimated_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `task_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `successor_task_template` int DEFAULT NULL,
  `category` int DEFAULT NULL,
  `sub_category` int DEFAULT NULL,
  `predecessor_task_template` int DEFAULT NULL,
  `role` int DEFAULT NULL,
  `skill` int DEFAULT NULL,
  `certificate` int DEFAULT NULL,
  `responsible_person` int NOT NULL,
  `collaborators` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'To-Do',
  `priority` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attachment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `task_budget` bigint DEFAULT NULL,
  `human_resource_requirements` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `technological_resources` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'stop',
  `values` int DEFAULT NULL,
  `type_of_resource` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `amount` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int NOT NULL,
  `department` int DEFAULT NULL,
  `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,
  PRIMARY KEY (`id`),
  KEY `fk_custom_task_creation_template` (`template`),
  KEY `fk_custom_task_creation_tag` (`tag`),
  KEY `fk_custom_task_creation_label` (`label`),
  KEY `fk_custom_task_creation_category` (`category`),
  KEY `fk_custom_task_creation_sub_category` (`sub_category`),
  KEY `fk_custom_task_creation_role` (`role`),
  KEY `fk_custom_task_creation_skill` (`skill`),
  KEY `fk_custom_task_creation_certificate` (`certificate`),
  KEY `fk_custom_task_creation_responsible_person` (`responsible_person`),
  KEY `fk_custom_task_creation_organization` (`organization`),
  KEY `fk_custom_task_creation_department` (`department`),
  KEY `fk_custom_task_creation_created_by` (`created_by`),
  KEY `fk_custom_task_creation_updated_by` (`updated_by`),
  CONSTRAINT `custom_task_creation_ibfk_105` FOREIGN KEY (`template`) REFERENCES `task_template_list` (`id`),
  CONSTRAINT `custom_task_creation_ibfk_106` FOREIGN KEY (`tag`) REFERENCES `category` (`id`),
  CONSTRAINT `custom_task_creation_ibfk_107` FOREIGN KEY (`label`) REFERENCES `labels` (`id`),
  CONSTRAINT `custom_task_creation_ibfk_108` FOREIGN KEY (`category`) REFERENCES `category` (`id`),
  CONSTRAINT `custom_task_creation_ibfk_109` FOREIGN KEY (`sub_category`) REFERENCES `category` (`id`),
  CONSTRAINT `custom_task_creation_ibfk_110` FOREIGN KEY (`role`) REFERENCES `roles` (`id`),
  CONSTRAINT `custom_task_creation_ibfk_111` FOREIGN KEY (`skill`) REFERENCES `skills` (`id`),
  CONSTRAINT `custom_task_creation_ibfk_112` FOREIGN KEY (`certificate`) REFERENCES `certificates` (`id`),
  CONSTRAINT `custom_task_creation_ibfk_113` FOREIGN KEY (`responsible_person`) REFERENCES `users` (`id`),
  CONSTRAINT `custom_task_creation_ibfk_114` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`),
  CONSTRAINT `custom_task_creation_ibfk_115` FOREIGN KEY (`department`) REFERENCES `department` (`id`),
  CONSTRAINT `custom_task_creation_ibfk_116` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `custom_task_creation_ibfk_117` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: ddrm
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `ddrm` (
  `id` int NOT NULL AUTO_INCREMENT,
  `document` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `category` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `storage` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `meta_tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `assign_with_roles` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `assign_with_users` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `role_periods` int DEFAULT '0',
  `users_periods` int DEFAULT '0',
  `user_start_date` datetime DEFAULT NULL,
  `user_end_date` datetime DEFAULT NULL,
  `role_start_date` datetime DEFAULT NULL,
  `role_end_date` datetime DEFAULT NULL,
  `select_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` int NOT NULL,
  `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,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `ddrm_ibfk_7` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`),
  CONSTRAINT `ddrm_ibfk_8` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `ddrm_ibfk_9` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: defect_detection_method
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `defect_detection_method` (
  `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 `defect_detection_method_ibfk_7` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `defect_detection_method_ibfk_8` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `defect_detection_method_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: defect_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `defect_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 `defect_type_ibfk_7` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `defect_type_ibfk_8` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `defect_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: department
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `department` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `department_head` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `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_department` int DEFAULT NULL,
  `unique_id` varchar(255) 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 `parent_department` (`parent_department`),
  CONSTRAINT `department_ibfk_105` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `department_ibfk_106` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `department_ibfk_107` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`),
  CONSTRAINT `department_ibfk_108` FOREIGN KEY (`parent_department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: deviation_category
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `deviation_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` 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 `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `deviation_category_ibfk_10` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `deviation_category_ibfk_11` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `deviation_category_ibfk_12` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `deviation_category_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: disability
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `disability` (
  `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 `fk_disability_department` (`department`),
  KEY `fk_disability_created_by` (`created_by`),
  KEY `fk_disability_updated_by` (`updated_by`),
  CONSTRAINT `disability_ibfk_87` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `disability_ibfk_88` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `disability_ibfk_89` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 54 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: disposal_site
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `disposal_site` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `description` longtext,
  `department` int DEFAULT NULL,
  `organization` longtext,
  `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,
  `unique_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `disposal_site_ibfk_4` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `disposal_site_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `disposal_site_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: document_categories
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `document_categories` (
  `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,
  `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 `document_categories_ibfk_10` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_categories_ibfk_11` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_categories_ibfk_12` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_categories_ibfk_9` 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: document_classification
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `document_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 `document_classification_ibfk_19` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_classification_ibfk_20` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_classification_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: document_creation
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `document_creation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `document_amendment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `select_revised_document` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `upload_document` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `version_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_hierarchy` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_tags` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `viewing_rights_employees` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `modification_rights_employees` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `deleting_rights_employees` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `effective_date` datetime DEFAULT NULL,
  `expiry_date` datetime DEFAULT NULL,
  `document_keywords` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `employee_reviewer` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `reviewer_employee_name` int DEFAULT NULL,
  `reviewer_role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reviewer_department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approver_employee_name` int DEFAULT NULL,
  `approver_role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approver_department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_storage_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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,
  `status` enum('accept', 'reject', 'pending') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `repository_id` int DEFAULT NULL,
  `is_archived` int DEFAULT '0',
  `is_locked` int DEFAULT '0',
  `sidebar_id` int DEFAULT NULL,
  `owner_user_id` int DEFAULT NULL,
  `specify_owner` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_classification` enum(
  'Confidential',
  'Private',
  'Restricted',
  'Unclassified'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Private',
  `document_owner` enum('Internal', 'External') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Internal',
  `document_origin` enum('Internal', 'External') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Internal',
  `specify_owner_email` 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 `owner_user_id` (`owner_user_id`),
  CONSTRAINT `document_creation_ibfk_131` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_creation_ibfk_132` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_creation_ibfk_133` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_creation_ibfk_134` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_creation_ibfk_135` FOREIGN KEY (`owner_user_id`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: document_repository
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `document_repository` (
  `id` int NOT NULL AUTO_INCREMENT,
  `document_type` int DEFAULT NULL,
  `associated_audit` int DEFAULT NULL,
  `document_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `allowed_access_to` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `audit_execution_id` varchar(500) 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,
  `ddrm_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `associated_audit` (`associated_audit`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `document_type` (`document_type`),
  KEY `ddrm_id` (`ddrm_id`),
  CONSTRAINT `document_repository_ibfk_100` FOREIGN KEY (`associated_audit`) REFERENCES `audit_execution` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_repository_ibfk_101` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_repository_ibfk_102` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_repository_ibfk_103` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_repository_ibfk_104` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_repository_ibfk_105` FOREIGN KEY (`ddrm_id`) REFERENCES `repository` (`id`),
  CONSTRAINT `document_repository_ibfk_99` FOREIGN KEY (`document_type`) REFERENCES `document_type` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: document_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `document_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,
  `organization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_static` tinyint 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,
  `parent` 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 `document_type_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `document_type_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 54 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: effectiveness_outcome
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `effectiveness_outcome` (
  `id` int NOT NULL AUTO_INCREMENT,
  `employee_name` int DEFAULT NULL,
  `surname` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employee_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `role_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `training_awareness` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `training_date` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `training_venue` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `responses` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `comments` 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_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_name` (`employee_name`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `effectiveness_outcome_ibfk_37` FOREIGN KEY (`employee_name`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `effectiveness_outcome_ibfk_38` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `effectiveness_outcome_ibfk_39` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `effectiveness_outcome_ibfk_40` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `effectiveness_outcome_ibfk_41` 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: employee_type
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `employee_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,
  `editable` int DEFAULT '1',
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_employee_type_department` (`department`),
  KEY `fk_employee_type_created_by` (`created_by`),
  KEY `fk_employee_type_updated_by` (`updated_by`),
  CONSTRAINT `employee_type_ibfk_25` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `employee_type_ibfk_26` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `employee_type_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: engagement_levels
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `engagement_levels` (
  `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 `engagement_levels_ibfk_31` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `engagement_levels_ibfk_32` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `engagement_levels_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: engagement_method
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `engagement_method` (
  `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',
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `department` (`department`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `engagement_method_ibfk_70` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `engagement_method_ibfk_71` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `engagement_method_ibfk_72` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 26 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: environmental_incidents
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `environmental_incidents` (
  `id` int NOT NULL AUTO_INCREMENT,
  `category_id` int DEFAULT NULL,
  `incident_id` 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,
  `save_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_date` datetime DEFAULT NULL,
  `incident_time` time 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,
  `incident_type` json DEFAULT NULL,
  `incident_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incident_severity` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `technical_form_completed_by` int DEFAULT NULL,
  `role_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_submission` date DEFAULT NULL,
  `notification` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `for_contractor` int DEFAULT '0',
  `department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `incident_id` (`incident_id`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `technical_form_completed_by` (`technical_form_completed_by`),
  CONSTRAINT `environmental_incidents_ibfk_53` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`),
  CONSTRAINT `environmental_incidents_ibfk_54` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `environmental_incidents_ibfk_55` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`),
  CONSTRAINT `environmental_incidents_ibfk_56` FOREIGN KEY (`technical_form_completed_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: equipment
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `equipment` (
  `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 `fk_equipment_department` (`department`),
  KEY `fk_equipment_created_by` (`created_by`),
  KEY `fk_equipment_updated_by` (`updated_by`),
  CONSTRAINT `equipment_ibfk_79` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `equipment_ibfk_80` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `equipment_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: evidence_gathering
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `evidence_gathering` (
  `id` int NOT NULL AUTO_INCREMENT,
  `date_collection` date DEFAULT NULL,
  `time_of_collection` time DEFAULT NULL,
  `type_of_evidence` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `source_of_evidence` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `upload_evidence_file` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `serial_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `upload_barcode` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `physical_evidence_description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `policy_and_procedure_documents` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `record_retention_policies` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `regulatory_guidance` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `other_documents` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `evidence_preservation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `chain_of_custody` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `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,
  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: explosion_bomb_form
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `explosion_bomb_form` (
  `id` int NOT NULL AUTO_INCREMENT,
  `organization` int DEFAULT NULL,
  `department` int DEFAULT NULL,
  `incident_id` int DEFAULT NULL,
  `type_of_incident` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `location` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `immediate_cause_of_incident` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `was_alcohol_or_substance_testing_done` tinyint DEFAULT '0',
  `alcohol_testing` int DEFAULT NULL,
  `substance_testing` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `none` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tested_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `specify` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `alcohol_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_the_person_or_operator_licensed` tinyint DEFAULT '0',
  `was_the_license_applicable_to_the_task` tinyint DEFAULT 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 DEFAULT 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 DEFAULT '0',
  `name_of_regulator` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_reported` date DEFAULT NULL,
  `reported_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `estimated_cost` 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` datetime DEFAULT CURRENT_TIMESTAMP,
  `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',
  `save_type` 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(255) 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,
  `category_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `incident_id` (`incident_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `explosion_bomb_form_ibfk_79` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `explosion_bomb_form_ibfk_80` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `explosion_bomb_form_ibfk_81` FOREIGN KEY (`incident_id`) REFERENCES `incident` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `explosion_bomb_form_ibfk_82` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `explosion_bomb_form_ibfk_83` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `explosion_bomb_form_ibfk_84` FOREIGN KEY (`category_id`) REFERENCES `incident_category` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: feedback_collection
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `feedback_collection` (
  `id` int NOT NULL AUTO_INCREMENT,
  `method_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `method_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `review_date` date DEFAULT NULL,
  `collection_method_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `target_stakeholder` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `frequency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `responsible_person` int 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,
  PRIMARY KEY (`id`),
  KEY `responsible_person` (`responsible_person`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `feedback_collection_ibfk_31` FOREIGN KEY (`responsible_person`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `feedback_collection_ibfk_32` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `feedback_collection_ibfk_33` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `feedback_collection_ibfk_34` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `feedback_collection_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: feedback_context
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `feedback_context` (
  `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 `feedback_context_ibfk_31` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `feedback_context_ibfk_32` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `feedback_context_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: file_classification
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `file_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 `file_classification_ibfk_40` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `file_classification_ibfk_41` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `file_classification_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: final_handover
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `final_handover` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contractor` int DEFAULT NULL,
  `form` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Not Conducted',
  `document` 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,
  `total` int DEFAULT '0',
  `responses` int DEFAULT '0',
  `unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contractor` (`contractor`),
  KEY `organization` (`organization`),
  KEY `department` (`department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `final_handover_ibfk_51` FOREIGN KEY (`contractor`) REFERENCES `contractor_registration` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `final_handover_ibfk_52` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `final_handover_ibfk_53` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `final_handover_ibfk_54` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `final_handover_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: final_handover_responses
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `final_handover_responses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contractor_name` int 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,
  `department` int DEFAULT NULL,
  `date_of_inspection` date DEFAULT NULL,
  `contract_number` int DEFAULT NULL,
  `cipc_registration_number` 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_final_handover_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',
  `final_handover_id` int NOT NULL,
  `from_email` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `contractor_final_handover_form` (`contractor_final_handover_form`),
  KEY `project` (`project`),
  KEY `final_handover_id` (`final_handover_id`),
  CONSTRAINT `final_handover_responses_ibfk_25` FOREIGN KEY (`contractor_final_handover_form`) REFERENCES `contractor_forms` (`id`),
  CONSTRAINT `final_handover_responses_ibfk_26` FOREIGN KEY (`project`) REFERENCES `project_registration` (`id`),
  CONSTRAINT `final_handover_responses_ibfk_27` FOREIGN KEY (`final_handover_id`) REFERENCES `final_handover` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: final_handover_responses_docs
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `final_handover_responses_docs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_no` int DEFAULT NULL,
  `final_handover_response_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',
  PRIMARY KEY (`id`),
  KEY `final_handover_response_id` (`final_handover_response_id`),
  CONSTRAINT `final_handover_responses_docs_ibfk_1` FOREIGN KEY (`final_handover_response_id`) REFERENCES `final_handover_responses` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: financial_year
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `financial_year` (
  `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 `financial_year_ibfk_31` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `financial_year_ibfk_32` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `financial_year_ibfk_33` 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: fire_equipment_maintenance_inspection
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `fire_equipment_maintenance_inspection` (
  `id` int NOT NULL AUTO_INCREMENT,
  `equipment_id` int DEFAULT NULL,
  `currency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recording_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `maintenance_cost` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `equipment_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inspection_checkbox` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `equipment_category` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `action_taken` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `task_data` 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,
  `schedule` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `inspection_checklist` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `unique_equipment_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `value` int DEFAULT NULL,
  `equipment_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `location` int DEFAULT NULL,
  `installation_date` date DEFAULT NULL,
  `inspection_date` date DEFAULT NULL,
  `last_maintenance_date` date DEFAULT NULL,
  `next_maintenance_date` date DEFAULT NULL,
  `for_reported_by_department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reported_by` int DEFAULT NULL,
  `supplier_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inspection_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,
  `investigation_outcome` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `modal_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `capacity` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum(
  'Operational',
  'Needs Repair',
  'Maintenance Overdue',
  'Decommissioned',
  'Not Operational',
  'Under Maintenance'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Operational',
  `last_updated_date` datetime DEFAULT NULL,
  `for_reported_by_role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notify_to` int DEFAULT NULL,
  `notify_to_department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notify_to_role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `remarks_comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `responsible_department` int DEFAULT NULL,
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `location` (`location`),
  KEY `responsible_department` (`responsible_department`),
  KEY `department` (`department`),
  KEY `organization` (`organization`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `fire_equipment_maintenance_inspection_ibfk_10` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_maintenance_inspection_ibfk_11` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_maintenance_inspection_ibfk_12` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_maintenance_inspection_ibfk_7` FOREIGN KEY (`location`) REFERENCES `location` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_maintenance_inspection_ibfk_8` FOREIGN KEY (`responsible_department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_maintenance_inspection_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: fire_equipment_register
# ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `fire_equipment_register` (
  `id` int NOT NULL AUTO_INCREMENT,
  `equipment_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `unique_equipment_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `location` int DEFAULT NULL,
  `installation_date` date DEFAULT NULL,
  `supplier_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `modal_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `capacity` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum(
  'Operational',
  'Needs Repair',
  'Maintenance Overdue',
  'Decommissioned',
  'Not Operational',
  'Under Maintenance'
  ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Operational',
  `department` int DEFAULT NULL,
  `organization` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `responsible_department` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `currency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `equipment_category` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `task_data` 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,
  `schedule` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `inspection_checklist` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `value` int DEFAULT NULL,
  `inspection_date` date DEFAULT NULL,
  `last_maintenance_date` date DEFAULT NULL,
  `next_maintenance_date` date DEFAULT NULL,
  `inspection_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,
  `investigation_outcome` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `action_taken` int DEFAULT NULL,
  `for_reported_by_department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reported_by` int DEFAULT NULL,
  `last_updated_date` datetime DEFAULT NULL,
  `for_reported_by_role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notify_to` int DEFAULT NULL,
  `notify_to_department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notify_to_role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `remarks_comments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `recording_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `maintenance_cost` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inspection_checkbox` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `unique_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `equipment_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_equipment_id` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_2` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_3` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_4` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_5` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_6` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_7` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_8` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_9` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_10` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_11` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_12` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_13` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_14` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_15` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_16` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_17` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_18` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_19` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_20` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_21` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_22` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_23` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_24` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_25` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_26` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_27` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_28` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_29` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_30` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_31` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_32` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_33` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_34` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_35` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_36` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_37` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_38` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_39` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_40` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_41` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_42` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_43` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_44` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_45` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_46` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_47` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_48` (`unique_equipment_id`),
  UNIQUE KEY `unique_equipment_id_49` (`unique_equipment_id`),
  KEY `location` (`location`),
  KEY `department` (`department`),
  KEY `organization` (`organization`),
  KEY `responsible_department` (`responsible_department`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `fire_equipment_register_ibfk_157` FOREIGN KEY (`location`) REFERENCES `location` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_register_ibfk_158` FOREIGN KEY (`department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_register_ibfk_159` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_register_ibfk_160` FOREIGN KEY (`responsible_department`) REFERENCES `department` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_register_ibfk_161` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE,
  CONSTRAINT `fire_equipment_register_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: fire_equipment_status_history
# ------------------------------------------------------------

