-- MariaDB dump 10.19 Distrib 10.4.32-MariaDB, for Win64 (AMD64) -- -- Host: localhost Database: beekoder -- ------------------------------------------------------ -- Server version 10.4.32-MariaDB /*!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 */; /*!40101 SET NAMES utf8mb4 */; /*!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 `admins` -- DROP TABLE IF EXISTS `admins`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `admins` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, `pass1` varchar(255) NOT NULL, `pass2` varchar(255) NOT NULL, `role` varchar(40) NOT NULL, `session_token` varchar(64) DEFAULT NULL, `session_token_expires_at` datetime DEFAULT NULL, `last_login_at` timestamp NULL DEFAULT NULL, `last_login_ip` varchar(45) DEFAULT NULL, `title` varchar(255) DEFAULT NULL, `logo` varchar(255) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `email` varchar(150) DEFAULT NULL, `otp` varchar(6) DEFAULT NULL, `otp_expires_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `admins_username_unique` (`username`), KEY `idx_id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `allocateassignments` -- DROP TABLE IF EXISTS `allocateassignments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `allocateassignments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `staff_id` bigint(20) unsigned NOT NULL, `assname` varchar(255) NOT NULL, `marks` int(11) NOT NULL, `submiteddate` date NOT NULL, `currentdate` date NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_allocateassignments_uname` (`uname`), KEY `fk_allocateassignments_cname` (`cname`), KEY `fk_allocateassignments_dept` (`dept`), KEY `fk_allocateassignments_batch` (`batch`), KEY `fk_allocateassignments_course` (`coursename`), KEY `fk_allocateassignments_module` (`module`), KEY `fk_allocateassignments_staff` (`staff_id`), CONSTRAINT `fk_allocateassignments_batch` FOREIGN KEY (`batch`) REFERENCES `batches` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocateassignments_cname` FOREIGN KEY (`cname`) REFERENCES `colleges` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocateassignments_course` FOREIGN KEY (`coursename`) REFERENCES `courses` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocateassignments_dept` FOREIGN KEY (`dept`) REFERENCES `departments` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocateassignments_module` FOREIGN KEY (`module`) REFERENCES `modules` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocateassignments_staff` FOREIGN KEY (`staff_id`) REFERENCES `staffs` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocateassignments_uname` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `allocatecodings` -- DROP TABLE IF EXISTS `allocatecodings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `allocatecodings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `topic` bigint(20) unsigned NOT NULL, `type` varchar(50) NOT NULL DEFAULT 'coding', `questionid` varchar(255) NOT NULL, `allocatedate` date NOT NULL, `created_at` date DEFAULT curdate(), `updated_at` date DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_uname` (`uname`), KEY `fk_cname` (`cname`), KEY `fk_dept` (`dept`), KEY `fk_batch` (`batch`), KEY `fk_coursename` (`coursename`), KEY `fk_module` (`module`), KEY `fk_topic` (`topic`), CONSTRAINT `fk_batch` FOREIGN KEY (`batch`) REFERENCES `batches` (`id`), CONSTRAINT `fk_cname` FOREIGN KEY (`cname`) REFERENCES `colleges` (`id`), CONSTRAINT `fk_coursename` FOREIGN KEY (`coursename`) REFERENCES `courses` (`id`), CONSTRAINT `fk_dept` FOREIGN KEY (`dept`) REFERENCES `departments` (`id`), CONSTRAINT `fk_module` FOREIGN KEY (`module`) REFERENCES `modules` (`id`), CONSTRAINT `fk_topic` FOREIGN KEY (`topic`) REFERENCES `topics` (`id`), CONSTRAINT `fk_uname` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `allocatecodings_expanded` -- DROP TABLE IF EXISTS `allocatecodings_expanded`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `allocatecodings_expanded` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `topic` bigint(20) unsigned NOT NULL, `questionid` bigint(20) unsigned NOT NULL, `type` varchar(20) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_filter` (`uname`,`cname`,`dept`,`batch`,`coursename`,`module`,`topic`,`type`), KEY `idx_question` (`questionid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `allocatecourses` -- DROP TABLE IF EXISTS `allocatecourses`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `allocatecourses` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `courseid` varchar(100) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_allocatecourses_uname` (`uname`), KEY `fk_allocatecourses_cname` (`cname`), KEY `fk_allocatecourses_dept` (`dept`), KEY `fk_allocatecourses_batch` (`batch`), CONSTRAINT `fk_allocatecourses_batch` FOREIGN KEY (`batch`) REFERENCES `batches` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocatecourses_cname` FOREIGN KEY (`cname`) REFERENCES `colleges` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocatecourses_dept` FOREIGN KEY (`dept`) REFERENCES `departments` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocatecourses_uname` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `allocatecourses_expanded` -- DROP TABLE IF EXISTS `allocatecourses_expanded`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `allocatecourses_expanded` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `courseid` bigint(20) unsigned NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_student` (`uname`,`cname`,`dept`,`batch`), KEY `idx_course` (`courseid`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `allocatemcqs` -- DROP TABLE IF EXISTS `allocatemcqs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `allocatemcqs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `topic` bigint(20) unsigned NOT NULL, `questionid` text NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_allocatemcqs_uname` (`uname`), KEY `fk_allocatemcqs_cname` (`cname`), KEY `fk_allocatemcqs_dept` (`dept`), KEY `fk_allocatemcqs_batch` (`batch`), KEY `fk_allocatemcqs_coursename` (`coursename`), KEY `fk_allocatemcqs_module` (`module`), KEY `fk_allocatemcqs_topic` (`topic`), CONSTRAINT `fk_allocatemcqs_batch` FOREIGN KEY (`batch`) REFERENCES `batches` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocatemcqs_cname` FOREIGN KEY (`cname`) REFERENCES `colleges` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocatemcqs_coursename` FOREIGN KEY (`coursename`) REFERENCES `courses` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocatemcqs_dept` FOREIGN KEY (`dept`) REFERENCES `departments` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocatemcqs_module` FOREIGN KEY (`module`) REFERENCES `modules` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocatemcqs_topic` FOREIGN KEY (`topic`) REFERENCES `topics` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_allocatemcqs_uname` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `allocatemcqs_expanded` -- DROP TABLE IF EXISTS `allocatemcqs_expanded`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `allocatemcqs_expanded` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) DEFAULT NULL, `cname` bigint(20) DEFAULT NULL, `dept` bigint(20) DEFAULT NULL, `batch` bigint(20) DEFAULT NULL, `coursename` bigint(20) DEFAULT NULL, `module` bigint(20) DEFAULT NULL, `topic` bigint(20) DEFAULT NULL, `questionid` bigint(20) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_filter` (`uname`,`cname`,`dept`,`batch`,`coursename`,`module`,`topic`), KEY `idx_q` (`questionid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `assessment_submissions` -- DROP TABLE IF EXISTS `assessment_submissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `assessment_submissions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `session_id` varchar(100) NOT NULL, `student_id` bigint(20) unsigned NOT NULL, `question_id` bigint(20) unsigned NOT NULL, `assessment_id` bigint(20) unsigned NOT NULL, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned DEFAULT NULL, `module` bigint(20) unsigned DEFAULT NULL, `language` varchar(20) NOT NULL, `code` longtext DEFAULT NULL, `code_length` int(11) DEFAULT 0, `total_time_seconds` int(11) DEFAULT 0, `status` enum('queued','processing','completed','failed') DEFAULT 'queued', `results` longtext DEFAULT NULL, `report_stored` tinyint(1) DEFAULT 0, `passed_tests` int(11) DEFAULT 0, `total_tests` int(11) DEFAULT 0, `skipped_tests` int(11) DEFAULT 0, `score` int(11) DEFAULT 0, `error` text DEFAULT NULL, `ip_address` varchar(50) DEFAULT NULL, `user_agent` varchar(500) DEFAULT NULL, `start_time` datetime DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uk_session` (`session_id`), KEY `idx_student` (`student_id`), KEY `idx_status` (`status`), KEY `idx_assessment` (`assessment_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `attendance_course_summary` -- DROP TABLE IF EXISTS `attendance_course_summary`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `attendance_course_summary` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `student_id` bigint(20) unsigned NOT NULL, `course_id` bigint(20) unsigned NOT NULL, `percentage` decimal(5,2) NOT NULL DEFAULT 0.00, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `student_course` (`student_id`,`course_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `attendances` -- DROP TABLE IF EXISTS `attendances`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `attendances` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `student_id` bigint(20) unsigned NOT NULL, `course_id` bigint(20) unsigned NOT NULL, `hours` int(11) NOT NULL, `date` date NOT NULL, `status` enum('Present','Absent','Leave') NOT NULL DEFAULT 'Absent', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_uname` (`uname`), KEY `idx_cname` (`cname`), KEY `idx_dept` (`dept`), KEY `idx_batch` (`batch`), KEY `idx_student` (`student_id`), KEY `idx_course` (`course_id`), KEY `idx_date` (`date`) ) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `batches` -- DROP TABLE IF EXISTS `batches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `batches` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` varchar(100) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_cname` (`cname`), KEY `idx_dept` (`dept`), KEY `idx_uname` (`uname`), KEY `idx_batch_filter` (`uname`,`cname`,`dept`,`created_at`), CONSTRAINT `fk_batches_cname` FOREIGN KEY (`cname`) REFERENCES `colleges` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_batches_dept` FOREIGN KEY (`dept`) REFERENCES `departments` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_batches_uname` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache` -- DROP TABLE IF EXISTS `cache`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache` ( `key` varchar(255) NOT NULL, `value` mediumtext NOT NULL, `expiration` int(11) NOT NULL, PRIMARY KEY (`key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_locks` -- DROP TABLE IF EXISTS `cache_locks`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_locks` ( `key` varchar(255) NOT NULL, `owner` varchar(255) NOT NULL, `expiration` int(11) NOT NULL, PRIMARY KEY (`key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `codingassessment_questions_expanded` -- DROP TABLE IF EXISTS `codingassessment_questions_expanded`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `codingassessment_questions_expanded` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `codingassessment_id` bigint(20) unsigned NOT NULL, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `type` varchar(30) NOT NULL, `assname` varchar(255) NOT NULL, `assdate` date NOT NULL, `assstarttime` time NOT NULL, `assendtime` time NOT NULL, `questionid` bigint(20) unsigned NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `codingassessment_id` (`codingassessment_id`), KEY `questionid` (`questionid`), KEY `uname` (`uname`,`cname`,`dept`,`batch`), KEY `coursename` (`coursename`,`module`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `codingassessmentreport` -- DROP TABLE IF EXISTS `codingassessmentreport`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `codingassessmentreport` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `student_id` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned DEFAULT NULL, `module` bigint(20) unsigned DEFAULT NULL, `assid` bigint(20) unsigned NOT NULL, `questionid` bigint(20) unsigned NOT NULL, `submitedtime` datetime DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_student_question` (`student_id`,`assid`,`questionid`), KEY `idx_student` (`student_id`), KEY `idx_assessment` (`assid`), KEY `idx_question` (`questionid`), KEY `idx_student_assessment` (`student_id`,`assid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `codingassessments` -- DROP TABLE IF EXISTS `codingassessments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `codingassessments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `type` varchar(30) NOT NULL DEFAULT 'coding', `assname` varchar(255) NOT NULL, `assdate` date NOT NULL, `assstarttime` time NOT NULL, `assendtime` time NOT NULL, `duration` int(11) NOT NULL DEFAULT 0 COMMENT 'Minutes', `questionid` longtext DEFAULT NULL COMMENT 'Comma separated Coding Question IDs', `total_questions` int(11) NOT NULL DEFAULT 0, `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active', `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `uname` (`uname`), KEY `cname` (`cname`), KEY `dept` (`dept`), KEY `batch` (`batch`), KEY `coursename` (`coursename`), KEY `module` (`module`), KEY `status` (`status`), CONSTRAINT `fk_codingassessment_batch` FOREIGN KEY (`batch`) REFERENCES `batches` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_codingassessment_college` FOREIGN KEY (`cname`) REFERENCES `colleges` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_codingassessment_course` FOREIGN KEY (`coursename`) REFERENCES `courses` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_codingassessment_department` FOREIGN KEY (`dept`) REFERENCES `departments` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_codingassessment_module` FOREIGN KEY (`module`) REFERENCES `modules` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_codingassessment_university` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `codingreports` -- DROP TABLE IF EXISTS `codingreports`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `codingreports` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uname` int(11) NOT NULL, `student_id` int(11) DEFAULT NULL, `cname` int(11) NOT NULL, `dept` int(11) DEFAULT NULL, `batch` int(11) DEFAULT NULL, `regno` varchar(50) DEFAULT NULL, `level` varchar(20) DEFAULT NULL, `subname` int(11) DEFAULT NULL, `unitname` int(11) DEFAULT NULL, `topic` int(11) DEFAULT NULL, `questionid` int(11) NOT NULL, `code_snippet` longtext DEFAULT NULL, `start_time` datetime DEFAULT NULL, `end_time` datetime DEFAULT NULL, `total_time_seconds` int(11) DEFAULT NULL, `marks` int(11) DEFAULT 0, `solved_date` date DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `codings` -- DROP TABLE IF EXISTS `codings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `codings` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `topic` bigint(20) unsigned NOT NULL, `level` varchar(255) DEFAULT NULL, `title` varchar(255) DEFAULT NULL, `question` mediumtext DEFAULT NULL, `answer` mediumtext DEFAULT NULL, `solution` text DEFAULT NULL, `t1` mediumtext DEFAULT NULL, `t2` mediumtext DEFAULT NULL, `t3` mediumtext DEFAULT NULL, `t4` mediumtext DEFAULT NULL, `i1` mediumtext DEFAULT NULL, `i2` mediumtext DEFAULT NULL, `i3` mediumtext DEFAULT NULL, `i4` mediumtext DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_codings_course` (`coursename`), KEY `fk_codings_module` (`module`), KEY `idx_coding_query` (`uname`,`coursename`,`module`,`topic`,`title`), KEY `codings_filters_idx` (`uname`,`coursename`,`module`,`topic`,`id`), KEY `fk_codings_topics` (`topic`), CONSTRAINT `fk_codings_course` FOREIGN KEY (`coursename`) REFERENCES `courses` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_codings_module` FOREIGN KEY (`module`) REFERENCES `modules` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_codings_topic` FOREIGN KEY (`topic`) REFERENCES `topics` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_codings_topics` FOREIGN KEY (`topic`) REFERENCES `topics` (`id`), CONSTRAINT `fk_codings_university` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `colleges` -- DROP TABLE IF EXISTS `colleges`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `colleges` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `cname` varchar(100) NOT NULL, `uname` bigint(20) NOT NULL, `district` varchar(50) NOT NULL, `pemail` varchar(50) NOT NULL, `pphone` varchar(50) NOT NULL, `logo_path` varchar(255) DEFAULT NULL, `ptitle` varchar(50) NOT NULL, `ctitle` varchar(50) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_uname` (`uname`), KEY `idx_cname` (`cname`), KEY `idx_district` (`district`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `course_allocation_counts` -- DROP TABLE IF EXISTS `course_allocation_counts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `course_allocation_counts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `coding_total` int(11) NOT NULL DEFAULT 0, `mcq_total` int(11) NOT NULL DEFAULT 0, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_course_counts` (`uname`,`cname`,`dept`,`batch`,`coursename`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `courses` -- DROP TABLE IF EXISTS `courses`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `courses` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `coursename` varchar(100) NOT NULL, `coursecode` varchar(30) NOT NULL, `pname` bigint(20) unsigned NOT NULL, `uname` bigint(20) unsigned NOT NULL, `hours` int(11) NOT NULL DEFAULT 0, `regulation` varchar(20) NOT NULL, `percentage` int(11) NOT NULL DEFAULT 0, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `syllabus` text DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_uname` (`uname`), KEY `idx_pname` (`pname`), KEY `idx_courses_coursename` (`coursename`), KEY `id` (`id`), CONSTRAINT `fk_courses_universities` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_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 */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `departments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `dept` varchar(255) NOT NULL, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_unique_uname_cname_dept` (`uname`,`cname`,`dept`), KEY `idx_departments_cname` (`cname`), CONSTRAINT `departments_ibfk_1` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`), CONSTRAINT `departments_ibfk_2` FOREIGN KEY (`cname`) REFERENCES `colleges` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `doubtclearance` -- DROP TABLE IF EXISTS `doubtclearance`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `doubtclearance` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `student_id` bigint(20) unsigned NOT NULL, `staff_id` bigint(20) unsigned NOT NULL, `question` longtext NOT NULL, `answer` longtext NOT NULL DEFAULT 'Pending', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `uname` (`uname`), KEY `cname` (`cname`), KEY `dept` (`dept`), KEY `batch` (`batch`), KEY `student_id` (`student_id`), KEY `staff_id` (`staff_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `eiq` -- DROP TABLE IF EXISTS `eiq`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `eiq` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `title` varchar(130) NOT NULL, `question` mediumtext NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `failed_jobs` -- DROP TABLE IF EXISTS `failed_jobs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `failed_jobs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uuid` varchar(255) NOT NULL, `connection` text NOT NULL, `queue` text NOT NULL, `payload` longtext NOT NULL, `exception` longtext NOT NULL, `failed_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `job_batches` -- DROP TABLE IF EXISTS `job_batches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `job_batches` ( `id` varchar(255) NOT NULL, `name` varchar(255) NOT NULL, `total_jobs` int(11) NOT NULL, `pending_jobs` int(11) NOT NULL, `failed_jobs` int(11) NOT NULL, `failed_job_ids` longtext NOT NULL, `options` mediumtext DEFAULT NULL, `cancelled_at` int(11) DEFAULT NULL, `created_at` int(11) NOT NULL, `finished_at` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `jobs` -- DROP TABLE IF EXISTS `jobs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `jobs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `queue` varchar(255) NOT NULL, `payload` longtext NOT NULL, `attempts` tinyint(3) unsigned NOT NULL, `reserved_at` int(10) unsigned DEFAULT NULL, `available_at` int(10) unsigned NOT NULL, `created_at` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `jobs_queue_index` (`queue`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `leaverequests` -- DROP TABLE IF EXISTS `leaverequests`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `leaverequests` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` int(11) NOT NULL, `cname` int(11) NOT NULL, `dept` int(11) NOT NULL, `batch` int(11) NOT NULL, `student` int(11) NOT NULL, `title` varchar(255) NOT NULL, `reason` text DEFAULT NULL, `start_date` date NOT NULL, `end_date` date NOT NULL, `status` enum('Pending','Approved','Rejected') NOT NULL DEFAULT 'Pending', `approved_by` int(11) DEFAULT NULL, `approved_at` datetime DEFAULT NULL, `remarks` text DEFAULT NULL, `applied_on` datetime DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `uname` (`uname`), KEY `cname` (`cname`), KEY `dept` (`dept`), KEY `batch` (`batch`), KEY `student` (`student`), KEY `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `materials` -- DROP TABLE IF EXISTS `materials`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `materials` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` int(11) NOT NULL, `cname` int(11) NOT NULL, `dept` int(11) NOT NULL, `batch` int(11) NOT NULL, `staff` int(11) NOT NULL, `coursename` int(11) NOT NULL, `module` int(11) NOT NULL, `material_name` varchar(255) NOT NULL, `file_path` varchar(500) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `uname` (`uname`), KEY `cname` (`cname`), KEY `dept` (`dept`), KEY `batch` (`batch`), KEY `staff` (`staff`), KEY `coursename` (`coursename`), KEY `module` (`module`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `mcq_assessment_questions` -- DROP TABLE IF EXISTS `mcq_assessment_questions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `mcq_assessment_questions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `assid` bigint(20) unsigned NOT NULL, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `question_id` bigint(20) unsigned NOT NULL, `q_index` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `assid` (`assid`), KEY `question_id` (`question_id`), KEY `uname` (`uname`), KEY `cname` (`cname`), KEY `dept` (`dept`), KEY `batch` (`batch`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `mcqassessmentreport` -- DROP TABLE IF EXISTS `mcqassessmentreport`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `mcqassessmentreport` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `student_id` bigint(20) unsigned NOT NULL, `assid` bigint(20) unsigned NOT NULL, `questionid` bigint(20) unsigned DEFAULT NULL, `selectedanswer` varchar(255) DEFAULT NULL, `score` int(11) DEFAULT 0, `total_questions` int(11) DEFAULT 0, `correct_answers` int(11) DEFAULT 0, `wrong_answers` int(11) DEFAULT 0, `started_at` datetime DEFAULT NULL, `submitted_at` datetime DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `uname` bigint(20) DEFAULT NULL, `cname` bigint(20) DEFAULT NULL, `dept` bigint(20) DEFAULT NULL, `batch` bigint(20) DEFAULT NULL, `coursename` bigint(20) DEFAULT NULL, `module` bigint(20) DEFAULT NULL, `correctanswer` varchar(255) DEFAULT NULL, `submitedtime` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_mcq` (`student_id`,`assid`,`questionid`), KEY `idx_student` (`student_id`), KEY `idx_assessment` (`assid`), KEY `idx_student_assessment` (`student_id`,`assid`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `mcqassessments` -- DROP TABLE IF EXISTS `mcqassessments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `mcqassessments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `assname` varchar(255) NOT NULL, `assdate` date NOT NULL, `assstarttime` time NOT NULL, `assendtime` time NOT NULL, `questionid` longtext DEFAULT NULL, `duration` int(11) NOT NULL DEFAULT 0, `total_questions` int(11) NOT NULL DEFAULT 0, `total_marks` int(11) NOT NULL DEFAULT 0, `pass_mark` int(11) NOT NULL DEFAULT 0, `negative_mark` decimal(5,2) DEFAULT 0.00, `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `uname` (`uname`), KEY `cname` (`cname`), KEY `dept` (`dept`), KEY `batch` (`batch`), KEY `course_id` (`coursename`), KEY `assdate` (`assdate`), KEY `status` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `mcqreports` -- DROP TABLE IF EXISTS `mcqreports`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `mcqreports` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` varchar(255) DEFAULT NULL, `cname` varchar(255) DEFAULT NULL, `dept` varchar(255) DEFAULT NULL, `batch` varchar(255) DEFAULT NULL, `student_id` bigint(20) unsigned DEFAULT NULL, `coursename` bigint(20) unsigned DEFAULT NULL, `module` bigint(20) unsigned DEFAULT NULL, `topic` bigint(20) unsigned DEFAULT NULL, `questionid` varchar(255) DEFAULT NULL, `selectedanswer` varchar(255) DEFAULT NULL, `correctanswer` varchar(255) DEFAULT NULL, `submitedtime` datetime DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `mcqs` -- DROP TABLE IF EXISTS `mcqs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `mcqs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `topic` bigint(20) unsigned NOT NULL, `level` varchar(50) NOT NULL, `question` text NOT NULL, `option1` varchar(255) NOT NULL, `option2` varchar(255) NOT NULL, `option3` varchar(255) NOT NULL, `option4` varchar(255) NOT NULL, `answer` varchar(255) 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 `uq_mcq_unique_question` (`uname`,`coursename`,`module`,`topic`,`question`(255)), KEY `fk_mcqs_module` (`module`), KEY `idx_mcqs_course_module_topic` (`coursename`,`module`,`topic`), KEY `idx_mcqs_filter` (`uname`,`coursename`,`module`,`topic`), KEY `idx_mcqs_question` (`question`(100)), KEY `fk_mcqs_topics` (`topic`), CONSTRAINT `fk_mcqs_coursename` FOREIGN KEY (`coursename`) REFERENCES `courses` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_mcqs_module` FOREIGN KEY (`module`) REFERENCES `modules` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_mcqs_topic` FOREIGN KEY (`topic`) REFERENCES `topics` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_mcqs_topics` FOREIGN KEY (`topic`) REFERENCES `topics` (`id`), CONSTRAINT `fk_mcqs_uname` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `meets` -- DROP TABLE IF EXISTS `meets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `meets` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `staff_id` bigint(20) unsigned NOT NULL, `purpose` varchar(500) NOT NULL, `link` text NOT NULL, `start_time` datetime NOT NULL, `end_time` datetime NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `migrations` -- DROP TABLE IF EXISTS `migrations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `migrations` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `migration` varchar(255) NOT NULL, `batch` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `module_allocation_counts` -- DROP TABLE IF EXISTS `module_allocation_counts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `module_allocation_counts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `coding_total` int(11) NOT NULL DEFAULT 0, `mcq_total` int(11) NOT NULL DEFAULT 0, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `module_alloc_unique` (`uname`,`cname`,`dept`,`batch`,`coursename`,`module`), KEY `idx_course` (`coursename`,`module`), KEY `idx_filters` (`uname`,`cname`,`dept`,`batch`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `modules` -- DROP TABLE IF EXISTS `modules`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `modules` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` varchar(255) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_modules_university` (`uname`), KEY `fk_modules_course` (`coursename`), KEY `id` (`id`), CONSTRAINT `fk_modules_course` FOREIGN KEY (`coursename`) REFERENCES `courses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_modules_university` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `notification_reads` -- DROP TABLE IF EXISTS `notification_reads`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `notification_reads` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `student_id` bigint(20) unsigned NOT NULL, `notification_id` bigint(20) unsigned NOT NULL, `read_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `student_notification_unique` (`student_id`,`notification_id`), KEY `idx_student` (`student_id`), KEY `idx_notification` (`notification_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `notifications` -- DROP TABLE IF EXISTS `notifications`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `notifications` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `staff` varchar(255) NOT NULL, `title` varchar(255) NOT NULL, `message` mediumtext NOT NULL, `times` datetime NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_uname` (`uname`), KEY `idx_cname` (`cname`), KEY `idx_dept` (`dept`), KEY `idx_batch` (`batch`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `password_reset_tokens` -- DROP TABLE IF EXISTS `password_reset_tokens`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `password_reset_tokens` ( `email` varchar(255) NOT NULL, `token` varchar(255) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `pqps` -- DROP TABLE IF EXISTS `pqps`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `pqps` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `staff` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `year` varchar(20) NOT NULL, `types` varchar(50) NOT NULL, `file` varchar(255) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_course` (`coursename`), KEY `idx_uname_subname_year` (`uname`,`coursename`,`year`), CONSTRAINT `fk_course` FOREIGN KEY (`coursename`) REFERENCES `courses` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_university` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `programs` -- DROP TABLE IF EXISTS `programs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `programs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pname` varchar(25) NOT NULL, `image` varchar(255) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `sessions` -- DROP TABLE IF EXISTS `sessions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sessions` ( `id` varchar(255) NOT NULL, `user_id` bigint(20) unsigned DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `user_agent` text DEFAULT NULL, `payload` longtext NOT NULL, `last_activity` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `sessions_user_id_index` (`user_id`), KEY `sessions_last_activity_index` (`last_activity`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `softwarefeedbacks` -- DROP TABLE IF EXISTS `softwarefeedbacks`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `softwarefeedbacks` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `name` varchar(255) NOT NULL, `regno` varchar(255) NOT NULL, `title` varchar(255) NOT NULL, `message` mediumtext NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `uname` (`uname`), KEY `cname` (`cname`), KEY `dept` (`dept`), KEY `batch` (`batch`), CONSTRAINT `fk_softwarefeedback_batch` FOREIGN KEY (`batch`) REFERENCES `batches` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_softwarefeedback_college` FOREIGN KEY (`cname`) REFERENCES `colleges` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_softwarefeedback_dept` FOREIGN KEY (`dept`) REFERENCES `departments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_softwarefeedback_university` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `staff_feedbacks` -- DROP TABLE IF EXISTS `staff_feedbacks`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `staff_feedbacks` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` int(11) NOT NULL, `cname` int(11) NOT NULL, `dept` int(11) NOT NULL, `batch` int(11) NOT NULL, `student_id` int(11) NOT NULL, `name` varchar(150) NOT NULL, `regno` varchar(50) NOT NULL, `staff_dept` int(11) NOT NULL, `staff_id` int(11) NOT NULL, `explain` tinyint(4) NOT NULL, `doubt` tinyint(4) NOT NULL, `engagement` tinyint(4) NOT NULL, `professional` tinyint(4) NOT NULL, `punctual` tinyint(4) NOT NULL, `traineragain` enum('yes','no') NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `uname` (`uname`), KEY `cname` (`cname`), KEY `dept` (`dept`), KEY `batch` (`batch`), KEY `student_id` (`student_id`), KEY `staff_id` (`staff_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `staffs` -- DROP TABLE IF EXISTS `staffs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `staffs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `name` varchar(255) NOT NULL, `email` varchar(100) DEFAULT NULL, `phone` varchar(20) DEFAULT NULL, `desg` varchar(100) DEFAULT NULL, `username` varchar(100) NOT NULL, `password` varchar(255) NOT NULL, `password_view` varchar(255) DEFAULT NULL, `session_token` varchar(64) DEFAULT NULL, `session_token_expires_at` datetime DEFAULT NULL, `last_login_at` timestamp NULL DEFAULT current_timestamp(), `last_login_ip` varchar(45) DEFAULT '0.0.0.0', `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), KEY `fk_staff_college` (`cname`), KEY `fk_staff_dept` (`dept`), KEY `idx_staff_uname_cname_dept` (`uname`,`cname`,`dept`), KEY `idx_staff_uname_cname_dept_name` (`uname`,`cname`,`dept`,`name`), CONSTRAINT `fk_staff_college` FOREIGN KEY (`cname`) REFERENCES `colleges` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_staff_dept` FOREIGN KEY (`dept`) REFERENCES `departments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_staff_university` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `start_coding_ass` -- DROP TABLE IF EXISTS `start_coding_ass`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `start_coding_ass` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `assid` bigint(20) unsigned NOT NULL, `student_id` bigint(20) unsigned NOT NULL, `question_id` bigint(20) unsigned DEFAULT NULL, `start_time` datetime DEFAULT NULL, `end_time` datetime DEFAULT NULL, `total_time_seconds` int(11) DEFAULT 0, `status` enum('Started','Completed') DEFAULT 'Started', `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `idx_assid` (`assid`), KEY `idx_student` (`student_id`), KEY `idx_uname` (`uname`), KEY `idx_cname` (`cname`), KEY `idx_dept` (`dept`), KEY `idx_batch` (`batch`), KEY `fk_startcoding_question` (`question_id`), CONSTRAINT `fk_startcoding_assessment` FOREIGN KEY (`assid`) REFERENCES `codingassessments` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_startcoding_batch` FOREIGN KEY (`batch`) REFERENCES `batches` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_startcoding_cname` FOREIGN KEY (`cname`) REFERENCES `colleges` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_startcoding_dept` FOREIGN KEY (`dept`) REFERENCES `departments` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_startcoding_question` FOREIGN KEY (`question_id`) REFERENCES `codings` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_startcoding_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_startcoding_uname` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `start_mcq_ass` -- DROP TABLE IF EXISTS `start_mcq_ass`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `start_mcq_ass` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `assid` bigint(20) unsigned NOT NULL, `student_id` bigint(20) unsigned NOT NULL, `start_time` datetime DEFAULT NULL, `end_time` datetime DEFAULT NULL, `score` decimal(8,2) DEFAULT 0.00, `status` enum('Started','Completed') DEFAULT 'Started', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `assid` (`assid`), KEY `student_id` (`student_id`), KEY `uname` (`uname`), KEY `cname` (`cname`), KEY `dept` (`dept`), KEY `batch` (`batch`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `student_course_progress` -- DROP TABLE IF EXISTS `student_course_progress`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `student_course_progress` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `student_id` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `coding_completed` int(11) NOT NULL DEFAULT 0, `mcq_completed` int(11) NOT NULL DEFAULT 0, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `student_course_unique` (`student_id`,`coursename`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `student_module_progress` -- DROP TABLE IF EXISTS `student_module_progress`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `student_module_progress` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `student_id` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `coding_completed` int(11) NOT NULL DEFAULT 0, `mcq_completed` int(11) NOT NULL DEFAULT 0, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `student_module_unique` (`student_id`,`coursename`,`module`), KEY `idx_student` (`student_id`), KEY `idx_course_module` (`coursename`,`module`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `students` -- DROP TABLE IF EXISTS `students`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `students` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned DEFAULT NULL, `name` varchar(255) NOT NULL, `regno` varchar(50) NOT NULL, `phone` varchar(20) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, `password` varchar(255) NOT NULL, `session_token` varchar(255) DEFAULT NULL, `session_token_hash` varchar(255) DEFAULT NULL, `session_token_created_at` datetime DEFAULT NULL, `last_login_at` datetime DEFAULT NULL, `login_ip` varchar(45) DEFAULT NULL, `user_agent` text DEFAULT NULL, `yoj` year(4) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `idx_students_regno` (`regno`), UNIQUE KEY `idx_students_email` (`email`), KEY `fk_student_dept` (`dept`), KEY `fk_student_batch` (`batch`), KEY `idx_students_uname_cname_dept_batch` (`uname`,`cname`,`dept`,`batch`), KEY `idx_cname_dept_batch` (`cname`,`dept`,`batch`), CONSTRAINT `fk_student_batch` FOREIGN KEY (`batch`) REFERENCES `batches` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_student_college` FOREIGN KEY (`cname`) REFERENCES `colleges` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_student_dept` FOREIGN KEY (`dept`) REFERENCES `departments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_student_university` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=676 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `theme_settings` -- DROP TABLE IF EXISTS `theme_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `theme_settings` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned DEFAULT NULL, `user_type` varchar(20) NOT NULL DEFAULT 'admin', `bg_color` varchar(30) NOT NULL DEFAULT '#ffffff', `div_color` varchar(30) NOT NULL DEFAULT '#f8f9fa', `text_primary` varchar(30) NOT NULL DEFAULT '#212529', `btn_primary` varchar(30) NOT NULL DEFAULT '#0d6efd', `btn_secondary` varchar(30) NOT NULL DEFAULT '#6c757d', `btn_tertiary` varchar(30) NOT NULL DEFAULT '#198754', `font_family` varchar(100) NOT NULL DEFAULT 'Poppins', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_user_type` (`user_type`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `topic_allocation_counts` -- DROP TABLE IF EXISTS `topic_allocation_counts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `topic_allocation_counts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `cname` bigint(20) unsigned NOT NULL, `dept` bigint(20) unsigned NOT NULL, `batch` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `topic` bigint(20) unsigned NOT NULL, `coding_total` int(11) NOT NULL DEFAULT 0, `mcq_total` int(11) NOT NULL DEFAULT 0, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `topic_alloc_unique` (`uname`,`cname`,`dept`,`batch`,`coursename`,`module`,`topic`), KEY `idx_course_module` (`coursename`,`module`), KEY `idx_topic` (`topic`), KEY `idx_filters` (`uname`,`cname`,`dept`,`batch`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `topics` -- DROP TABLE IF EXISTS `topics`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `topics` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` bigint(20) unsigned NOT NULL, `coursename` bigint(20) unsigned NOT NULL, `module` bigint(20) unsigned NOT NULL, `topic` varchar(255) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp(), `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_uname_coursename_module` (`uname`,`coursename`,`module`), KEY `coursename` (`coursename`), KEY `idx_module` (`module`), KEY `id` (`id`), CONSTRAINT `topics_ibfk_1` FOREIGN KEY (`uname`) REFERENCES `universities` (`id`), CONSTRAINT `topics_ibfk_2` FOREIGN KEY (`coursename`) REFERENCES `courses` (`id`), CONSTRAINT `topics_ibfk_3` FOREIGN KEY (`module`) REFERENCES `modules` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `universities` -- DROP TABLE IF EXISTS `universities`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `universities` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` varchar(100) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_created_at` (`created_at`), KEY `idx_updated_at` (`updated_at`), KEY `id` (`id`), FULLTEXT KEY `ft_uname` (`uname`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `university_jobs` -- DROP TABLE IF EXISTS `university_jobs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `university_jobs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uname` varchar(100) NOT NULL, `status` varchar(50) NOT NULL DEFAULT 'pending', `error` text DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_created_at_id` (`created_at`), FULLTEXT KEY `ft_uname` (`uname`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `urls` -- DROP TABLE IF EXISTS `urls`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `urls` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `meterial` text NOT NULL, `pqp` text NOT NULL, `syllabus` text NOT NULL, `calendar` text NOT NULL, `studentass` text NOT NULL, `studentweb` text NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_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 */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `users` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `email_verified_at` timestamp NULL DEFAULT NULL, `password` varchar(255) NOT NULL, `remember_token` varchar(100) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `users_email_unique` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_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-07-04 16:15:33