CREATE DATABASE  IF NOT EXISTS `riskasssessment` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `riskasssessment`;
-- MySQL dump 10.13  Distrib 8.0.40, for Win64 (x86_64)
--
-- Host: 127.0.0.1    Database: riskasssessment
-- ------------------------------------------------------
-- Server version	8.0.40

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `account_status`
--

DROP TABLE IF EXISTS `account_status`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `account_status` (
  `status_id` int NOT NULL,
  `status_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `country`
--

DROP TABLE IF EXISTS `country`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `country` (
  `country_id` int NOT NULL,
  `country_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `departments`
--

DROP TABLE IF EXISTS `departments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `departments` (
  `department_id` int NOT NULL,
  `department_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `job_title`
--

DROP TABLE IF EXISTS `job_title`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `job_title` (
  `job_title_id` int NOT NULL,
  `job_title_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`job_title_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `location_departments`
--

DROP TABLE IF EXISTS `location_departments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `location_departments` (
  `department_id` int NOT NULL,
  `location_id` int NOT NULL,
  PRIMARY KEY (`department_id`,`location_id`),
  KEY `locations_location_departments` (`location_id`),
  CONSTRAINT `departments_location_departments` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `locations_location_departments` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `locations`
--

DROP TABLE IF EXISTS `locations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `locations` (
  `location_id` int NOT NULL,
  `country_id` int DEFAULT NULL,
  `Location_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`location_id`),
  KEY `country_locations` (`country_id`),
  CONSTRAINT `country_locations` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_answers`
--

DROP TABLE IF EXISTS `orac_answers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_answers` (
  `answers_id` int NOT NULL AUTO_INCREMENT,
  `submission_id` int NOT NULL,
  `question_id` int NOT NULL,
  `is_media` tinyint(1) DEFAULT NULL,
  `answer_value` text,
  PRIMARY KEY (`answers_id`,`submission_id`,`question_id`),
  KEY `orac_submissions_orac_answers_submission` (`submission_id`),
  KEY `orac_questions_orac_answers_question` (`question_id`),
  CONSTRAINT `orac_questions_orac_answers_question` FOREIGN KEY (`question_id`) REFERENCES `orac_questions` (`question_id`),
  CONSTRAINT `orac_submissions_orac_answers_submission` FOREIGN KEY (`submission_id`) REFERENCES `orac_submissions` (`submission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_archive_form`
--

DROP TABLE IF EXISTS `orac_archive_form`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_archive_form` (
  `form_id` int NOT NULL,
  `archived_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`form_id`),
  CONSTRAINT `orac_form_details_orac_archive_form` FOREIGN KEY (`form_id`) REFERENCES `orac_form_details` (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_archive_section`
--

DROP TABLE IF EXISTS `orac_archive_section`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_archive_section` (
  `section_id` int NOT NULL,
  `archived_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`section_id`),
  CONSTRAINT `orac_section_details_orac_archive_section` FOREIGN KEY (`section_id`) REFERENCES `orac_section_details` (`section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_assessments`
--

DROP TABLE IF EXISTS `orac_assessments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_assessments` (
  `assessment_id` int NOT NULL AUTO_INCREMENT,
  `uploader_user_id` int NOT NULL,
  `executor_id` int DEFAULT NULL,
  `verifier_id` int DEFAULT NULL,
  `assessment_status_id` int NOT NULL,
  `video_title` varchar(255) DEFAULT NULL,
  `contract_dtails` varchar(255) DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `country_id` int DEFAULT NULL,
  `location_id` int DEFAULT NULL,
  `department_id` int DEFAULT NULL,
  `video_capture_date` timestamp NULL DEFAULT NULL,
  `video_capture_time` timestamp NULL DEFAULT NULL,
  `video` longblob,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`assessment_id`,`uploader_user_id`,`assessment_status_id`),
  KEY `orac_assessments_status_audits` (`assessment_status_id`),
  KEY `locations_orac_assessments` (`location_id`),
  KEY `departments_orac_assessments` (`department_id`),
  KEY `country_orac_assessments` (`country_id`),
  KEY `users_orac_assessments` (`executor_id`),
  KEY `users_orac_assessments_verifier` (`verifier_id`),
  CONSTRAINT `country_orac_assessments` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`),
  CONSTRAINT `departments_orac_assessments` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `locations_orac_assessments` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`),
  CONSTRAINT `orac_assessments_status_audits` FOREIGN KEY (`assessment_status_id`) REFERENCES `orac_assessments_status` (`assessment_status_id`),
  CONSTRAINT `users_orac_assessments` FOREIGN KEY (`executor_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `users_orac_assessments_verifier` FOREIGN KEY (`verifier_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_assessments_status`
--

DROP TABLE IF EXISTS `orac_assessments_status`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_assessments_status` (
  `assessment_status_id` int NOT NULL,
  `assessment_status_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`assessment_status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_combined_question`
--

DROP TABLE IF EXISTS `orac_combined_question`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_combined_question` (
  `main_question_id` int NOT NULL,
  `child_question_id` int NOT NULL,
  PRIMARY KEY (`main_question_id`,`child_question_id`),
  KEY `orac_questions_orac_combined_question_child` (`child_question_id`),
  CONSTRAINT `orac_questions_orac_combined_question` FOREIGN KEY (`main_question_id`) REFERENCES `orac_questions` (`question_id`),
  CONSTRAINT `orac_questions_orac_combined_question_child` FOREIGN KEY (`child_question_id`) REFERENCES `orac_questions` (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_form_details`
--

DROP TABLE IF EXISTS `orac_form_details`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_form_details` (
  `form_id` int NOT NULL AUTO_INCREMENT,
  `form_parent_id` int DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` text,
  `created_by` int DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_form_logs`
--

DROP TABLE IF EXISTS `orac_form_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_form_logs` (
  `user_id` int NOT NULL,
  `form_id` int DEFAULT NULL,
  `form_parent_id` int DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` text,
  `created_by` int DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `location_name` varchar(255) DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `users_orac_form_logs` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_form_section_map`
--

DROP TABLE IF EXISTS `orac_form_section_map`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_form_section_map` (
  `form_id` int NOT NULL,
  `section_id` int NOT NULL,
  PRIMARY KEY (`form_id`,`section_id`),
  KEY `orac_section_details_form_section_map` (`section_id`),
  CONSTRAINT `orac_form_details_form_section_map` FOREIGN KEY (`form_id`) REFERENCES `orac_form_details` (`form_id`),
  CONSTRAINT `orac_section_details_form_section_map` FOREIGN KEY (`section_id`) REFERENCES `orac_section_details` (`section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_location_forms_details`
--

DROP TABLE IF EXISTS `orac_location_forms_details`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_location_forms_details` (
  `form_id` int NOT NULL,
  `location_id` int NOT NULL,
  PRIMARY KEY (`form_id`,`location_id`),
  KEY `locations_orac_location_forms_details_location` (`location_id`),
  CONSTRAINT `locations_orac_location_forms_details_location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`),
  CONSTRAINT `orac_form_details_orac_location_forms_details_form` FOREIGN KEY (`form_id`) REFERENCES `orac_form_details` (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_participants`
--

DROP TABLE IF EXISTS `orac_participants`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_participants` (
  `assessment_id` int NOT NULL,
  `participants_name` varchar(255) DEFAULT NULL,
  `participant_user_id` int NOT NULL,
  PRIMARY KEY (`assessment_id`,`participant_user_id`),
  KEY `users_participants` (`participant_user_id`),
  CONSTRAINT `orac_assessments_participants` FOREIGN KEY (`assessment_id`) REFERENCES `orac_assessments` (`assessment_id`),
  CONSTRAINT `users_participants` FOREIGN KEY (`participant_user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_question_options`
--

DROP TABLE IF EXISTS `orac_question_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_question_options` (
  `question_options_id` int NOT NULL AUTO_INCREMENT,
  `question_id` int NOT NULL,
  `_options` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`question_options_id`,`question_id`),
  KEY `orac_questions_orac_question_options_question` (`question_id`),
  CONSTRAINT `orac_questions_orac_question_options_question` FOREIGN KEY (`question_id`) REFERENCES `orac_questions` (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_question_type`
--

DROP TABLE IF EXISTS `orac_question_type`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_question_type` (
  `question_type_id` int NOT NULL,
  `question_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`question_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_questions`
--

DROP TABLE IF EXISTS `orac_questions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_questions` (
  `question_id` int NOT NULL AUTO_INCREMENT,
  `question` varchar(255) NOT NULL,
  `question_type_id` int NOT NULL,
  PRIMARY KEY (`question_id`,`question_type_id`),
  KEY `orac_question_type_orac_questions_type` (`question_type_id`),
  CONSTRAINT `orac_question_type_orac_questions_type` FOREIGN KEY (`question_type_id`) REFERENCES `orac_question_type` (`question_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_section_details`
--

DROP TABLE IF EXISTS `orac_section_details`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_section_details` (
  `section_id` int NOT NULL AUTO_INCREMENT,
  `section_parent_id` int DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_section_logs`
--

DROP TABLE IF EXISTS `orac_section_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_section_logs` (
  `user_id` int NOT NULL,
  `section_id` int DEFAULT NULL,
  `section_parent_id` int DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `location_name` varchar(255) DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `users_orac_section_logs` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_section_question_map`
--

DROP TABLE IF EXISTS `orac_section_question_map`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_section_question_map` (
  `section_id` int NOT NULL,
  `question_id` int NOT NULL,
  PRIMARY KEY (`section_id`,`question_id`),
  KEY `orac_questions_section_quesiton_map` (`question_id`),
  CONSTRAINT `orac_questions_section_quesiton_map` FOREIGN KEY (`question_id`) REFERENCES `orac_questions` (`question_id`),
  CONSTRAINT `orac_section_details_section_quesiton_map` FOREIGN KEY (`section_id`) REFERENCES `orac_section_details` (`section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_snaps`
--

DROP TABLE IF EXISTS `orac_snaps`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_snaps` (
  `snap_id` int NOT NULL AUTO_INCREMENT,
  `assessment_id` int NOT NULL,
  `snap` longblob,
  `snap_timing` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`snap_id`,`assessment_id`),
  KEY `orac_assessments_orac_snaps` (`assessment_id`),
  CONSTRAINT `orac_assessments_orac_snaps` FOREIGN KEY (`assessment_id`) REFERENCES `orac_assessments` (`assessment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_submissions`
--

DROP TABLE IF EXISTS `orac_submissions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_submissions` (
  `submission_id` int NOT NULL AUTO_INCREMENT,
  `assessment_id` int NOT NULL,
  `submitter_id` int NOT NULL,
  `snap_id` int NOT NULL,
  `form_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`submission_id`,`assessment_id`,`submitter_id`,`snap_id`),
  KEY `users_orac_submissions` (`submitter_id`),
  KEY `orac_assessments_submissions` (`assessment_id`),
  KEY `orac_snaps_orac_submissions` (`snap_id`),
  KEY `orac_form_details_orac_submissions` (`form_id`),
  CONSTRAINT `orac_assessments_submissions` FOREIGN KEY (`assessment_id`) REFERENCES `orac_assessments` (`assessment_id`),
  CONSTRAINT `orac_form_details_orac_submissions` FOREIGN KEY (`form_id`) REFERENCES `orac_form_details` (`form_id`),
  CONSTRAINT `orac_snaps_orac_submissions` FOREIGN KEY (`snap_id`) REFERENCES `orac_snaps` (`snap_id`),
  CONSTRAINT `users_orac_submissions` FOREIGN KEY (`submitter_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orac_verification_flow`
--

DROP TABLE IF EXISTS `orac_verification_flow`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orac_verification_flow` (
  `assessment_id` int NOT NULL,
  `occurrence` int NOT NULL,
  `returned_for` text,
  PRIMARY KEY (`assessment_id`,`occurrence`),
  CONSTRAINT `orac_assessments_orac_verification_flow` FOREIGN KEY (`assessment_id`) REFERENCES `orac_assessments` (`assessment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `roles`
--

DROP TABLE IF EXISTS `roles`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `roles` (
  `role_id` int NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `tickets`
--

DROP TABLE IF EXISTS `tickets`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tickets` (
  `id` int NOT NULL,
  `user_id` int NOT NULL,
  `ticket_type_id` int NOT NULL,
  `status_id` int NOT NULL,
  `description` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `closed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`,`ticket_type_id`,`status_id`),
  KEY `tickets_status_tickets` (`status_id`),
  KEY `users_tickets` (`user_id`),
  KEY `tickets_type_tickets` (`ticket_type_id`),
  CONSTRAINT `tickets_status_tickets` FOREIGN KEY (`status_id`) REFERENCES `tickets_status` (`status_id`),
  CONSTRAINT `tickets_type_tickets` FOREIGN KEY (`ticket_type_id`) REFERENCES `tickets_type` (`type_id`),
  CONSTRAINT `users_tickets` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `tickets_logs`
--

DROP TABLE IF EXISTS `tickets_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tickets_logs` (
  `id` int NOT NULL,
  `ticket_id` int NOT NULL,
  `user_id` int DEFAULT NULL,
  `status_id` int NOT NULL,
  `ticket_type_id` int NOT NULL,
  `description` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `closed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`,`ticket_id`,`status_id`,`ticket_type_id`),
  KEY `tickets_tickets_logs` (`ticket_id`),
  CONSTRAINT `tickets_tickets_logs` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `tickets_status`
--

DROP TABLE IF EXISTS `tickets_status`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tickets_status` (
  `status_id` int NOT NULL,
  `status_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `tickets_type`
--

DROP TABLE IF EXISTS `tickets_type`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tickets_type` (
  `type_id` int NOT NULL,
  `type_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `user_role`
--

DROP TABLE IF EXISTS `user_role`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_role` (
  `user_role_id` int NOT NULL,
  `role_id` int NOT NULL,
  `user_id` int NOT NULL,
  PRIMARY KEY (`user_role_id`,`role_id`,`user_id`),
  KEY `users_user_role` (`user_id`),
  KEY `roles_user_role` (`role_id`),
  CONSTRAINT `roles_user_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`),
  CONSTRAINT `users_user_role` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `users` (
  `user_id` int NOT NULL,
  `employee_id` varchar(255) NOT NULL,
  `status_id` int NOT NULL,
  `employee_name` varchar(255) DEFAULT NULL,
  `job_title_id` int NOT NULL,
  `email_id` varchar(255) DEFAULT NULL,
  `phonenumber` varchar(255) DEFAULT NULL,
  `department_id` int NOT NULL,
  `location_id` int NOT NULL,
  `photo` blob,
  `password` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`,`employee_id`,`status_id`,`job_title_id`,`department_id`,`location_id`),
  UNIQUE KEY `email_id` (`email_id`),
  UNIQUE KEY `phonenumber` (`phonenumber`),
  KEY `locations_users` (`location_id`),
  KEY `departments_users` (`department_id`),
  KEY `account_status_users` (`status_id`),
  KEY `job_title_users` (`job_title_id`),
  CONSTRAINT `account_status_users` FOREIGN KEY (`status_id`) REFERENCES `account_status` (`status_id`),
  CONSTRAINT `departments_users` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `job_title_users` FOREIGN KEY (`job_title_id`) REFERENCES `job_title` (`job_title_id`),
  CONSTRAINT `locations_users` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `users_logs`
--

DROP TABLE IF EXISTS `users_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `users_logs` (
  `user_id` int NOT NULL,
  `employee_id` varchar(255) DEFAULT NULL,
  `role` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `employee_name` varchar(255) DEFAULT NULL,
  `phonenumber` varchar(255) DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `photo` blob,
  `password` varchar(255) DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `users_users_logs` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2026-03-16 14:13:29
