-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `ai_lms`
--

-- --------------------------------------------------------

--
-- Table structure for table `answers`
--

CREATE TABLE `answers` (
  `id` int(11) NOT NULL,
  `student_id` int(11) DEFAULT NULL,
  `question_id` int(11) DEFAULT NULL,
  `answer_text` text DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `feedback` text DEFAULT NULL,
  `confidence_level` varchar(50) DEFAULT NULL,
  `response_time` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `answers`
--

INSERT INTO `answers` (`id`, `student_id`, `question_id`, `answer_text`, `score`, `feedback`, `confidence_level`, `response_time`) VALUES
(1, 4, 1, 'test right!? is it!?', 0, 'The student\'s answer is unclear and does not demonstrate an understanding of the provided material. Please review the course content to provide a direct and accurate response. There are no spelling or grammatical errors.', NULL, NULL),
(2, 4, 3, 'i dont know', 0, 'The answer provided does not address the question at all. Please review the course materials related to the topic to improve your understanding. Spelling mistakes: \'dont\'.', NULL, NULL),
(3, 4, 4, 'test', 0, 'The answer provided is incomplete as it misses the acronym \'TEL\', which is the core subject of the question. Please ensure you include the full terminology requested to provide a comprehensive response.', 'low', 1.675),
(4, 4, 6, 'idk', 10, 'The student correctly identified that the answer to the provided prompt is unknown. This aligns perfectly with the expected answer provided in the evaluation criteria.', 'low', 4.725),
(15, 4, 4, 'Excellent and fully correct answer.', 10, 'Perfect understanding demonstrated.', 'medium', 6.2),
(16, 4, 4, 'Strong and accurate explanation.', 10, 'Very clear and complete response.', 'high', 7.1),
(17, 4, 4, 'Well structured and correct answer.', 10, 'Good depth and clarity.', 'high', 8),
(18, 4, 4, 'Correct solution with proper reasoning.', 10, 'No major issues found.', 'medium', 7.5),
(19, 4, 4, 'Flawless answer with strong explanation.', 10, 'Excellent work overall.', 'high', 6.8),
(20, 4, 4, 'Complete and accurate response.', 10, 'Meets all expected criteria.', 'medium', 7.9),
(21, 4, 4, 'Very good understanding shown.', 10, 'Answer is fully correct.', 'high', 8.3),
(22, 4, 4, 'Incorrect answer with missing logic.', 0, 'Does not match expected solution.', 'low', 12.4),
(23, 4, 4, 'No relevant answer provided.', 0, 'Completely off-topic or empty reasoning.', 'low', 15),
(24, 4, 4, 'Wrong approach and misunderstanding.', 0, 'Fails to demonstrate understanding.', 'low', 13.6);

-- --------------------------------------------------------

--
-- Table structure for table `courses`
--

CREATE TABLE `courses` (
  `id` int(11) NOT NULL,
  `title` varchar(100) DEFAULT NULL,
  `instructor_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `courses`
--

INSERT INTO `courses` (`id`, `title`, `instructor_id`) VALUES
(1, 'English', 1),
(2, 'Social Studies', 1),
(3, 'Literature', 1),
(4, 'test', 1),
(5, 'test 3', 1);

-- --------------------------------------------------------

--
-- Table structure for table `enrollments`
--

CREATE TABLE `enrollments` (
  `id` int(11) NOT NULL,
  `student_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `questions`
--

CREATE TABLE `questions` (
  `id` int(11) NOT NULL,
  `course_id` int(11) DEFAULT NULL,
  `question_text` text DEFAULT NULL,
  `expected_answer` text DEFAULT NULL,
  `time_expectation_level` varchar(50) DEFAULT NULL,
  `response_time` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `questions`
--

INSERT INTO `questions` (`id`, `course_id`, `question_text`, `expected_answer`, `time_expectation_level`, `response_time`) VALUES
(1, 1, 'test question', 'test answer', NULL, NULL),
(2, 2, 'test question 2', 'test answer 2', NULL, NULL),
(3, 1, 'test', 'test', NULL, NULL),
(4, 1, 'TEL test', 'TEL test', 'low', NULL),
(5, 4, 'Model test 21', 'i dont know!', 'low', NULL),
(6, 1, ' Model test 12', 'idk', 'low', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `student_courses`
--

CREATE TABLE `student_courses` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `student_courses`
--

INSERT INTO `student_courses` (`id`, `student_id`, `course_id`) VALUES
(1, 4, 1),
(2, 4, 3),
(3, 4, 5);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `answers`
--
ALTER TABLE `answers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `student_id` (`student_id`),
  ADD KEY `question_id` (`question_id`);

--
-- Indexes for table `courses`
--
ALTER TABLE `courses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `instructor_id` (`instructor_id`);

--
-- Indexes for table `enrollments`
--
ALTER TABLE `enrollments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `student_id` (`student_id`),
  ADD KEY `course_id` (`course_id`);

--
-- Indexes for table `questions`
--
ALTER TABLE `questions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `course_id` (`course_id`);

--
-- Indexes for table `student_courses`
--
ALTER TABLE `student_courses`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_assignment` (`student_id`,`course_id`),
  ADD KEY `course_id` (`course_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `answers`
--
ALTER TABLE `answers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--
-- AUTO_INCREMENT for table `courses`
--
ALTER TABLE `courses`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `enrollments`
--
ALTER TABLE `enrollments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `questions`
--
ALTER TABLE `questions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `student_courses`
--
ALTER TABLE `student_courses`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `answers`
--
ALTER TABLE `answers`
  ADD CONSTRAINT `answers_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `answers_ibfk_2` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `courses`
--
ALTER TABLE `courses`
  ADD CONSTRAINT `courses_ibfk_1` FOREIGN KEY (`instructor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `enrollments`
--
ALTER TABLE `enrollments`
  ADD CONSTRAINT `enrollments_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `enrollments_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `questions`
--
ALTER TABLE `questions`
  ADD CONSTRAINT `questions_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `student_courses`
--
ALTER TABLE `student_courses`
  ADD CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `student_courses_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE;
COMMIT;

