-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 16, 2025 at 01:57 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!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 */;

--
-- Database: `report_system`
--

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

--
-- Table structure for table `academic_years_terms`
--

CREATE TABLE `academic_years_terms` (
  `id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `academic_year` varchar(50) NOT NULL,
  `term_name` varchar(50) NOT NULL,
  `start_date` date NOT NULL DEFAULT '2025-01-01',
  `end_date` date NOT NULL DEFAULT '2025-12-31',
  `is_current_term` tinyint(1) NOT NULL DEFAULT 0,
  `is_current_year` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `academic_years_terms`
--

INSERT INTO `academic_years_terms` (`id`, `school_id`, `academic_year`, `term_name`, `start_date`, `end_date`, `is_current_term`, `is_current_year`, `created_at`, `updated_at`) VALUES
(21, 2, '2025', 'TERM1', '2025-07-28', '2025-08-28', 0, 1, '2025-07-28 14:55:54', '2025-09-18 07:25:52'),
(22, 2, '2025', 'TERM 2', '2025-07-28', '2025-08-02', 0, 1, '2025-07-28 15:05:46', '2025-09-18 07:25:52'),
(23, 2, '2025', 'TERM 3', '2025-09-15', '2025-12-12', 1, 1, '2025-09-18 07:25:22', '2025-09-18 07:25:52'),
(30, 2, '2024', 'Term 1', '2025-01-01', '2025-12-31', 0, 0, '2025-10-14 20:00:58', '2025-10-14 20:00:58'),
(31, 2, '2024', 'Term 2', '2025-01-01', '2025-12-31', 0, 0, '2025-10-14 20:00:58', '2025-10-14 20:00:58');

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

--
-- Table structure for table `announcements`
--

CREATE TABLE `announcements` (
  `id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `assignments`
--

CREATE TABLE `assignments` (
  `assignment_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `teacher_user_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `stream_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `due_date` date NOT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `assignments`
--

INSERT INTO `assignments` (`assignment_id`, `school_id`, `teacher_user_id`, `subject_id`, `stream_id`, `title`, `description`, `due_date`, `file_path`, `created_at`, `updated_at`) VALUES
(1, 4, 177, 7, 15, 'MATH ASSIGNMENT', 'complete the number in the sequence', '2025-07-16', NULL, '2025-07-16 08:50:36', '2025-07-16 08:50:36'),
(8, 2, 196, 3, 21, 'ACTIVITY OF INTERGRATION', 'READ THE FOLLOEING AND ANSWER YHE QUESTIONS THAT FOLLOWS', '2025-08-31', NULL, '2025-08-16 18:54:46', '2025-08-16 18:54:46'),
(9, 2, 196, 8, 21, 'ASSIGNMENT', 'submit you tutor assignment', '2025-08-17', NULL, '2025-08-17 21:22:21', '2025-08-17 21:22:21'),
(10, 2, 196, 11, 1, 'yuuu', 'look at the pdf above', '2025-08-21', 'uploads/assignments/assignment_68a59877c0ad58.34733802.pdf', '2025-08-20 12:42:15', '2025-08-20 12:42:15'),
(11, 2, 196, 2, 21, '[Integration Activity] AGRICULTURE - MARRK', 'Integration Activity for AGRICULTURE\nTopic: MARRK\nCompetency: the learner understands', '2025-08-27', NULL, '2025-08-20 15:02:32', '2025-08-20 15:02:32'),
(13, 2, 196, 9, 31, 'MATHEMATICS', 'READ AND ANSWER BACK', '2025-09-20', 'uploads/assignments/assignment_68cbec6f49e270.41642066.pdf', '2025-09-18 14:26:39', '2025-09-18 14:26:39'),
(16, 2, 287, 2, 32, 'MATHEMATICS', 'READ AND DO THE', '2025-10-16', 'uploads/assignments/assignment_68efa1e51ab996.00562954.pdf', '2025-10-15 16:29:55', '2025-10-15 16:30:13');

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

--
-- Table structure for table `assignment_submissions`
--

CREATE TABLE `assignment_submissions` (
  `submission_id` int(11) NOT NULL,
  `assignment_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `submission_path` varchar(255) NOT NULL,
  `submitted_at` datetime DEFAULT current_timestamp(),
  `is_resubmission` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `classes`
--

CREATE TABLE `classes` (
  `class_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `class_name` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `assigned_teacher_id` int(11) DEFAULT NULL,
  `stream_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `classes`
--

INSERT INTO `classes` (`class_id`, `school_id`, `class_name`, `created_at`, `updated_at`, `assigned_teacher_id`, `stream_id`) VALUES
(16, 4, 'SENIOR 1', '2025-07-15 17:20:51', '2025-07-15 17:20:51', NULL, NULL),
(41, 2, 'SENIOR 2', '2025-10-13 06:53:22', '2025-10-13 06:53:22', NULL, NULL);

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

--
-- Table structure for table `class_streams`
--

CREATE TABLE `class_streams` (
  `class_stream_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `stream_name` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `class_streams`
--

INSERT INTO `class_streams` (`class_stream_id`, `class_id`, `stream_name`, `created_at`, `updated_at`) VALUES
(1, 1, 'B', '2025-07-13 14:23:30', '2025-07-13 14:23:30'),
(2, 1, 'A', '2025-07-13 14:25:10', '2025-07-13 14:25:10'),
(3, 4, 'A', '2025-07-13 14:25:56', '2025-07-13 14:25:56');

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

--
-- Table structure for table `class_subjects`
--

CREATE TABLE `class_subjects` (
  `class_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `class_subjects`
--

INSERT INTO `class_subjects` (`class_id`, `subject_id`) VALUES
(1, 2),
(1, 3),
(13, 1),
(13, 2),
(13, 3),
(13, 5),
(16, 7),
(25, 1),
(25, 2),
(25, 3),
(25, 5),
(25, 8),
(25, 9),
(25, 10),
(25, 11),
(25, 12),
(25, 13),
(25, 14),
(25, 15),
(30, 2),
(30, 3),
(37, 3),
(37, 8),
(40, 1),
(40, 2),
(40, 3),
(40, 5),
(40, 8),
(40, 9),
(40, 10),
(40, 11),
(40, 12),
(40, 13),
(40, 14),
(40, 15),
(41, 1),
(41, 2),
(41, 3),
(41, 5),
(41, 8),
(41, 9),
(41, 10),
(41, 11),
(41, 12),
(41, 13),
(41, 14),
(41, 15);

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

--
-- Table structure for table `class_subject_teachers`
--

CREATE TABLE `class_subject_teachers` (
  `assignment_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `stream_id` int(11) NOT NULL,
  `academic_year` varchar(10) NOT NULL,
  `term` varchar(50) NOT NULL,
  `teacher_user_id` int(11) NOT NULL,
  `assignment_slot` int(11) NOT NULL,
  `assigned_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `school_id` int(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `class_subject_teachers`
--

INSERT INTO `class_subject_teachers` (`assignment_id`, `class_id`, `subject_id`, `stream_id`, `academic_year`, `term`, `teacher_user_id`, `assignment_slot`, `assigned_at`, `school_id`) VALUES
(71, 30, 2, 27, '2025', 'TERM 2', 164, 1, '2025-07-28 15:09:50', 2),
(72, 30, 3, 27, '2025', 'TERM 2', 164, 1, '2025-07-28 15:09:50', 2),
(73, 25, 2, 26, '2025', 'TERM 2', 164, 1, '2025-08-09 12:20:30', 2),
(74, 25, 2, 26, '2025', 'TERM 2', 164, 2, '2025-08-09 12:20:30', 2),
(75, 25, 2, 26, '2025', 'TERM 2', 164, 3, '2025-08-09 12:20:30', 2),
(78, 25, 2, 21, '2025', 'TERM 2', 196, 1, '2025-08-16 14:25:56', 2),
(79, 25, 3, 21, '2025', 'TERM 2', 196, 1, '2025-08-16 14:25:56', 2),
(80, 37, 3, 28, '2025', 'TERM 2', 196, 1, '2025-08-20 13:31:30', 2),
(81, 37, 8, 28, '2025', 'TERM 2', 196, 1, '2025-08-20 13:31:30', 2),
(86, 40, 2, 30, '2025', 'TERM 2', 195, 1, '2025-08-21 09:18:05', 2),
(87, 40, 3, 30, '2025', 'TERM 2', 195, 1, '2025-08-21 09:18:05', 2),
(88, 40, 8, 30, '2025', 'TERM 2', 196, 1, '2025-08-21 09:18:05', 2),
(90, 40, 2, 31, '2025', 'TERM 2', 196, 1, '2025-08-21 09:55:42', 2),
(91, 40, 3, 31, '2025', 'TERM 2', 196, 1, '2025-08-21 09:55:42', 2),
(92, 40, 8, 31, '2025', 'TERM 2', 196, 1, '2025-08-21 09:55:42', 2),
(97, 41, 2, 32, '2025', 'TERM 3', 287, 1, '2025-10-13 07:33:54', 2),
(98, 41, 2, 32, '2025', 'TERM 3', 268, 2, '2025-10-13 07:33:54', 2),
(99, 41, 2, 32, '2025', 'TERM 3', 282, 3, '2025-10-13 07:33:54', 2),
(100, 41, 3, 32, '2025', 'TERM 3', 287, 1, '2025-10-13 07:33:54', 2),
(101, 41, 8, 32, '2025', 'TERM 3', 287, 1, '2025-10-13 07:33:54', 2),
(102, 41, 2, 33, '2025', 'TERM 3', 287, 1, '2025-10-14 10:58:28', 2);

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

--
-- Table structure for table `class_teachers`
--

CREATE TABLE `class_teachers` (
  `class_id` int(11) NOT NULL,
  `teacher_user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `class_teachers`
--

INSERT INTO `class_teachers` (`class_id`, `teacher_user_id`) VALUES
(1, 165),
(13, 165),
(16, 177),
(25, 164),
(25, 196),
(27, 190),
(28, 164),
(29, 164),
(30, 164),
(30, 165),
(31, 164),
(35, 164),
(37, 196),
(40, 196),
(40, 268),
(40, 287),
(41, 287),
(44, 282),
(45, 268),
(46, 268),
(47, 287),
(48, 282);

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

--
-- Table structure for table `competencies`
--

CREATE TABLE `competencies` (
  `competency_id` int(11) NOT NULL,
  `topic_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `competency_name` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `competencies`
--

INSERT INTO `competencies` (`competency_id`, `topic_id`, `school_id`, `competency_name`, `created_at`, `updated_at`) VALUES
(1, 1, 2, 'the learner understands and appreciates the meaning of physics', '2025-07-12 18:59:00', '2025-07-12 18:59:00'),
(2, 4, 2, 'the learner understands', '2025-08-20 12:02:09', '2025-08-20 12:02:09'),
(3, 1, 2, 'the', '2025-08-20 13:38:14', '2025-08-20 13:38:14'),
(4, 3, 2, 'hhh', '2025-08-20 13:38:50', '2025-08-20 13:38:50');

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

--
-- Table structure for table `events`
--

CREATE TABLE `events` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `description` text DEFAULT NULL,
  `color` varchar(7) DEFAULT '#0275d8',
  `user_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `created_by_role` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `events`
--

INSERT INTO `events` (`id`, `title`, `start_datetime`, `end_datetime`, `description`, `color`, `user_id`, `school_id`, `created_by_role`, `created_at`) VALUES
(2, 'mass', '2025-10-14 00:00:00', '2025-10-15 00:00:00', '', '#0275d8', 287, 2, 2, '2025-10-14 06:46:41');

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

--
-- Table structure for table `exams`
--

CREATE TABLE `exams` (
  `exam_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `exam_name` varchar(255) NOT NULL,
  `term` varchar(50) NOT NULL,
  `year` varchar(50) NOT NULL,
  `exam_type` varchar(50) NOT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'Draft',
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `marks_locked` tinyint(1) DEFAULT 0 COMMENT 'Whether marks entry is locked (1=locked, 0=unlocked)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `exams`
--

INSERT INTO `exams` (`exam_id`, `school_id`, `exam_name`, `term`, `year`, `exam_type`, `status`, `created_by`, `updated_by`, `created_at`, `updated_at`, `marks_locked`) VALUES
(14, 2, 'BEGGINING OF TERM', 'TERM 2', '2025', '0', 'Published', 160, 160, '2025-08-21 09:57:52', '2025-10-16 09:12:48', 1),
(15, 2, 'MID TERM TTHREE', 'TERM 3', '2025', '0', 'Published', 160, 160, '2025-10-13 07:30:58', '2025-10-16 09:12:47', 1),
(16, 2, 'ASSESSMENT 1', 'TERM 3', '2025', '0', 'Published', 160, 160, '2025-10-14 06:57:21', '2025-10-16 09:12:16', 1),
(17, 2, 'ASSESSMENT 6', 'TERM 3', '2025', '0', 'Published', 160, 160, '2025-10-15 12:05:13', '2025-10-16 09:12:45', 1);

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

--
-- Table structure for table `exam_classes`
--

CREATE TABLE `exam_classes` (
  `exam_class_id` int(11) NOT NULL,
  `exam_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `exam_classes`
--

INSERT INTO `exam_classes` (`exam_class_id`, `exam_id`, `class_id`, `created_at`) VALUES
(18, 7, 27, '2025-07-25 13:03:07'),
(56, 14, 41, '2025-10-13 07:29:58'),
(57, 15, 41, '2025-10-13 07:30:58'),
(58, 16, 41, '2025-10-14 06:57:21'),
(59, 16, 44, '2025-10-14 06:57:21'),
(60, 17, 41, '2025-10-15 12:05:13');

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

--
-- Table structure for table `exam_subjects`
--

CREATE TABLE `exam_subjects` (
  `exam_subject_id` int(11) NOT NULL,
  `exam_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `status` enum('Draft','Captured','Published') NOT NULL DEFAULT 'Draft',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `exam_subjects`
--

INSERT INTO `exam_subjects` (`exam_subject_id`, `exam_id`, `subject_id`, `status`, `created_at`) VALUES
(22, 7, 2, 'Draft', '2025-07-25 13:03:07'),
(23, 7, 3, 'Draft', '2025-07-25 13:03:07'),
(24, 7, 5, 'Draft', '2025-07-25 13:03:07'),
(25, 7, 1, 'Draft', '2025-07-25 13:03:07'),
(124, 14, 2, 'Draft', '2025-10-13 07:29:58'),
(125, 14, 3, 'Draft', '2025-10-13 07:29:58'),
(126, 14, 8, 'Captured', '2025-10-13 07:29:58'),
(127, 14, 5, 'Draft', '2025-10-13 07:29:58'),
(128, 14, 12, 'Draft', '2025-10-13 07:29:58'),
(129, 14, 14, 'Draft', '2025-10-13 07:29:58'),
(130, 14, 11, 'Draft', '2025-10-13 07:29:58'),
(131, 14, 10, 'Draft', '2025-10-13 07:29:58'),
(132, 14, 13, 'Draft', '2025-10-13 07:29:58'),
(133, 14, 9, 'Draft', '2025-10-13 07:29:58'),
(134, 14, 1, 'Draft', '2025-10-13 07:29:58'),
(135, 14, 15, 'Draft', '2025-10-13 07:29:58'),
(136, 15, 2, 'Draft', '2025-10-13 07:30:58'),
(137, 15, 3, 'Draft', '2025-10-13 07:30:58'),
(138, 15, 8, 'Draft', '2025-10-13 07:30:58'),
(139, 15, 5, 'Draft', '2025-10-13 07:30:58'),
(140, 15, 12, 'Draft', '2025-10-13 07:30:58'),
(141, 15, 14, 'Draft', '2025-10-13 07:30:58'),
(142, 15, 11, 'Draft', '2025-10-13 07:30:58'),
(143, 15, 10, 'Draft', '2025-10-13 07:30:58'),
(144, 15, 13, 'Draft', '2025-10-13 07:30:58'),
(145, 15, 9, 'Draft', '2025-10-13 07:30:58'),
(146, 15, 1, 'Draft', '2025-10-13 07:30:58'),
(147, 15, 15, 'Draft', '2025-10-13 07:30:58'),
(148, 16, 2, 'Draft', '2025-10-14 06:57:21'),
(149, 16, 3, 'Draft', '2025-10-14 06:57:21'),
(150, 16, 8, 'Draft', '2025-10-14 06:57:21'),
(151, 16, 5, 'Draft', '2025-10-14 06:57:21'),
(152, 16, 12, 'Draft', '2025-10-14 06:57:21'),
(153, 16, 14, 'Draft', '2025-10-14 06:57:21'),
(154, 16, 11, 'Draft', '2025-10-14 06:57:21'),
(155, 16, 10, 'Draft', '2025-10-14 06:57:21'),
(156, 16, 13, 'Draft', '2025-10-14 06:57:21'),
(157, 16, 9, 'Draft', '2025-10-14 06:57:21'),
(158, 16, 1, 'Draft', '2025-10-14 06:57:21'),
(159, 16, 15, 'Draft', '2025-10-14 06:57:21'),
(160, 17, 2, 'Draft', '2025-10-15 12:05:13'),
(161, 17, 3, 'Draft', '2025-10-15 12:05:13'),
(162, 17, 8, 'Draft', '2025-10-15 12:05:13'),
(163, 17, 5, 'Draft', '2025-10-15 12:05:13'),
(164, 17, 12, 'Draft', '2025-10-15 12:05:13'),
(165, 17, 14, 'Draft', '2025-10-15 12:05:13'),
(166, 17, 11, 'Draft', '2025-10-15 12:05:13'),
(167, 17, 10, 'Draft', '2025-10-15 12:05:13'),
(168, 17, 13, 'Draft', '2025-10-15 12:05:13'),
(169, 17, 9, 'Draft', '2025-10-15 12:05:13'),
(170, 17, 1, 'Draft', '2025-10-15 12:05:13'),
(171, 17, 15, 'Draft', '2025-10-15 12:05:13');

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

--
-- Table structure for table `grading_scales`
--

CREATE TABLE `grading_scales` (
  `id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `grade_name` varchar(255) NOT NULL,
  `academic_year` varchar(50) DEFAULT NULL,
  `term_name` varchar(50) DEFAULT NULL,
  `class_id` int(11) NOT NULL DEFAULT 0,
  `subject_id` int(11) NOT NULL DEFAULT 0,
  `grade_letter` varchar(10) NOT NULL,
  `min_score` decimal(5,2) NOT NULL,
  `max_score` decimal(5,2) NOT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `grading_scales`
--

INSERT INTO `grading_scales` (`id`, `school_id`, `grade_name`, `academic_year`, `term_name`, `class_id`, `subject_id`, `grade_letter`, `min_score`, `max_score`, `remarks`, `created_by`, `updated_by`, `created_at`, `updated_at`) VALUES
(2, 2, 'CBC', '2025', 'Term 2', 25, 0, 'A', 80.00, 100.00, 'Exceptional', 160, 160, '2025-07-23 16:31:56', '2025-07-25 16:02:13'),
(3, 2, 'CBC', '2025', 'Term 2', 25, 0, 'B', 70.00, 79.00, 'Outstanding', 160, 160, '2025-07-23 16:31:56', '2025-07-25 16:02:13'),
(4, 2, 'CBC', '2025', 'Term 2', 25, 0, 'C', 60.00, 69.00, 'BASIC', 160, 160, '2025-07-23 16:31:56', '2025-07-25 16:02:13'),
(5, 2, 'CBC', '2025', 'Term 2', 25, 0, 'D', 50.00, 59.00, 'VERY BASIC', 160, 160, '2025-07-23 16:31:56', '2025-07-25 16:02:13'),
(6, 2, 'CBC', '2025', 'Term 2', 25, 0, 'E', 0.00, 49.00, 'WEAK', 160, 160, '2025-07-23 16:31:56', '2025-07-25 16:02:13'),
(7, 2, 'General', '2025', 'Term 2', 0, 0, 'A', 80.00, 100.00, 'Excellent Overall', 160, 160, '2025-07-31 14:42:39', '2025-07-31 14:42:39'),
(8, 2, 'General', '2025', 'Term 2', 0, 0, 'B', 70.00, 79.99, 'Very Good Overall', 160, 160, '2025-07-31 14:42:39', '2025-07-31 14:42:39'),
(9, 2, 'General', '2025', 'Term 2', 0, 0, 'C', 60.00, 69.99, 'Good Overall', 160, 160, '2025-07-31 14:42:39', '2025-07-31 14:42:39'),
(10, 2, 'General', '2025', 'Term 2', 0, 0, 'D', 50.00, 59.99, 'Pass Overall', 160, 160, '2025-07-31 14:42:39', '2025-07-31 14:42:39'),
(11, 2, 'General', '2025', 'Term 2', 0, 0, 'E', 0.00, 49.99, 'Fail Overall', 160, 160, '2025-07-31 14:42:39', '2025-07-31 14:42:39');

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

--
-- Table structure for table `integration_activities`
--

CREATE TABLE `integration_activities` (
  `activity_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `teacher_user_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `stream_id` int(11) NOT NULL,
  `topic_id` int(11) NOT NULL,
  `competency_id` int(11) NOT NULL,
  `academic_year` varchar(50) NOT NULL,
  `term` varchar(50) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `due_date` date NOT NULL,
  `status` varchar(50) DEFAULT 'Draft',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `integration_activities`
--

INSERT INTO `integration_activities` (`activity_id`, `school_id`, `teacher_user_id`, `subject_id`, `stream_id`, `topic_id`, `competency_id`, `academic_year`, `term`, `title`, `description`, `due_date`, `status`, `created_at`, `updated_at`) VALUES
(8, 2, 287, 2, 32, 4, 2, '2025', 'TERM 3', 'AGRICULTURE - MARRK', 'Integration Activity for AGRICULTURE\nTopic: MARRK\nCompetency: the learner understands', '2025-10-21', 'Captured', '2025-10-14 12:09:50', '2025-10-15 19:51:03'),
(10, 2, 287, 2, 33, 4, 2, '2025', 'TERM 3', 'AGRICULTURE', '', '2025-10-16', 'Draft', '2025-10-15 19:55:46', '2025-10-15 19:55:46');

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

--
-- Table structure for table `integration_activity_marks`
--

CREATE TABLE `integration_activity_marks` (
  `mark_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `stream_id` int(11) NOT NULL,
  `marks` decimal(5,2) NOT NULL,
  `academic_year` varchar(10) NOT NULL,
  `term` varchar(50) NOT NULL,
  `school_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `integration_activity_marks`
--

INSERT INTO `integration_activity_marks` (`mark_id`, `activity_id`, `student_id`, `subject_id`, `stream_id`, `marks`, `academic_year`, `term`, `school_id`, `created_at`, `updated_at`) VALUES
(1, 8, 291, 2, 32, 90.00, '2025', 'TERM 3', 2, '2025-10-15 19:27:08', '2025-10-16 06:56:54'),
(2, 8, 289, 2, 32, 78.00, '2025', 'TERM 3', 2, '2025-10-15 19:27:08', '2025-10-16 06:56:54');

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

--
-- Table structure for table `marks`
--

CREATE TABLE `marks` (
  `mark_id` int(11) NOT NULL,
  `exam_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `stream_id` int(11) NOT NULL,
  `teacher_user_id` int(11) NOT NULL,
  `mark` decimal(5,2) NOT NULL,
  `academic_year` varchar(50) NOT NULL,
  `term` varchar(50) NOT NULL,
  `school_id` int(11) NOT NULL,
  `date_recorded` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `marks`
--

INSERT INTO `marks` (`mark_id`, `exam_id`, `subject_id`, `student_id`, `stream_id`, `teacher_user_id`, `mark`, `academic_year`, `term`, `school_id`, `date_recorded`) VALUES
(40, 10, 2, 179, 21, 164, 100.00, '2025', 'Term 2', 2, '2025-07-25 14:22:46'),
(41, 10, 2, 180, 21, 164, 12.00, '2025', 'Term 2', 2, '2025-07-25 14:22:46'),
(42, 10, 2, 181, 21, 164, 45.00, '2025', 'Term 2', 2, '2025-07-25 14:22:46'),
(43, 10, 2, 182, 21, 164, 34.00, '2025', 'Term 2', 2, '2025-07-25 14:22:46'),
(44, 10, 2, 183, 21, 164, 56.00, '2025', 'Term 2', 2, '2025-07-25 14:22:46'),
(45, 10, 2, 184, 21, 164, 78.00, '2025', 'Term 2', 2, '2025-07-25 14:22:46'),
(46, 10, 2, 185, 21, 164, 90.00, '2025', 'Term 2', 2, '2025-07-25 14:22:46'),
(47, 10, 2, 186, 21, 164, 78.00, '2025', 'Term 2', 2, '2025-07-25 14:22:46'),
(48, 10, 2, 187, 21, 164, 90.00, '2025', 'Term 2', 2, '2025-07-25 14:22:46'),
(49, 10, 2, 188, 21, 164, 87.00, '2025', 'Term 2', 2, '2025-07-25 14:22:46'),
(50, 10, 2, 189, 21, 164, 77.00, '2025', 'Term 2', 2, '2025-07-25 14:22:46'),
(51, 10, 2, 169, 21, 164, 67.00, '2025', 'Term 2', 2, '2025-07-25 14:22:46'),
(53, 10, 3, 179, 21, 164, 100.00, '2025', 'TERM 2', 2, '2025-07-31 14:49:18'),
(54, 10, 3, 180, 21, 164, 100.00, '2025', 'TERM 2', 2, '2025-07-31 14:49:18'),
(55, 10, 3, 181, 21, 164, 100.00, '2025', 'TERM 2', 2, '2025-07-31 14:49:18'),
(56, 10, 3, 182, 21, 164, 100.00, '2025', 'TERM 2', 2, '2025-07-31 14:49:18'),
(57, 10, 3, 183, 21, 164, 100.00, '2025', 'TERM 2', 2, '2025-07-31 14:49:18'),
(58, 10, 3, 184, 21, 164, 100.00, '2025', 'TERM 2', 2, '2025-07-31 14:49:18'),
(59, 10, 3, 185, 21, 164, 100.00, '2025', 'TERM 2', 2, '2025-07-31 14:49:18'),
(60, 10, 3, 186, 21, 164, 100.00, '2025', 'TERM 2', 2, '2025-07-31 14:49:18'),
(61, 10, 3, 187, 21, 164, 100.00, '2025', 'TERM 2', 2, '2025-07-31 14:49:18'),
(62, 10, 3, 188, 21, 164, 100.00, '2025', 'TERM 2', 2, '2025-07-31 14:49:18'),
(63, 10, 3, 189, 21, 164, 100.00, '2025', 'TERM 2', 2, '2025-07-31 14:49:18'),
(64, 10, 3, 169, 21, 164, 100.00, '2025', 'TERM 2', 2, '2025-07-31 14:49:18'),
(65, 10, 5, 179, 21, 165, 90.00, '2025', 'TERM 2', 2, '2025-07-31 14:52:27'),
(66, 10, 5, 180, 21, 165, 87.00, '2025', 'TERM 2', 2, '2025-07-31 14:52:27'),
(67, 10, 5, 181, 21, 165, 88.00, '2025', 'TERM 2', 2, '2025-07-31 14:52:27'),
(68, 10, 5, 182, 21, 165, 98.00, '2025', 'TERM 2', 2, '2025-07-31 14:52:27'),
(69, 10, 5, 183, 21, 165, 65.00, '2025', 'TERM 2', 2, '2025-07-31 14:52:27'),
(70, 10, 5, 184, 21, 165, 78.00, '2025', 'TERM 2', 2, '2025-07-31 14:52:27'),
(71, 10, 5, 185, 21, 165, 90.00, '2025', 'TERM 2', 2, '2025-07-31 14:52:27'),
(72, 10, 5, 186, 21, 165, 88.00, '2025', 'TERM 2', 2, '2025-07-31 14:52:27'),
(73, 10, 5, 187, 21, 165, 90.00, '2025', 'TERM 2', 2, '2025-07-31 14:52:27'),
(74, 10, 5, 188, 21, 165, 98.00, '2025', 'TERM 2', 2, '2025-07-31 14:52:27'),
(75, 10, 5, 189, 21, 165, 76.00, '2025', 'TERM 2', 2, '2025-07-31 14:52:27'),
(76, 10, 5, 169, 21, 165, 77.00, '2025', 'TERM 2', 2, '2025-07-31 14:52:27'),
(77, 14, 2, 291, 33, 287, 67.00, '2025', 'TERM 2', 2, '2025-10-15 07:09:08'),
(78, 14, 2, 289, 33, 287, 65.00, '2025', 'TERM 2', 2, '2025-10-15 07:09:08'),
(79, 14, 2, 285, 33, 287, 78.00, '2025', 'TERM 2', 2, '2025-10-15 07:09:08'),
(80, 14, 2, 285, 32, 287, 89.00, '2025', 'TERM 2', 2, '2025-10-15 07:29:36'),
(81, 14, 2, 291, 32, 287, 78.00, '2025', 'TERM 2', 2, '2025-10-15 12:51:33'),
(82, 14, 2, 289, 32, 287, 67.00, '2025', 'TERM 2', 2, '2025-10-15 12:51:33'),
(83, 17, 8, 291, 32, 287, 89.00, '2025', 'TERM 3', 2, '2025-10-15 13:13:46'),
(84, 17, 8, 289, 32, 287, 88.00, '2025', 'TERM 3', 2, '2025-10-15 13:13:46'),
(85, 17, 3, 291, 32, 287, 56.00, '2025', 'TERM 3', 2, '2025-10-15 13:14:04'),
(86, 17, 3, 289, 32, 287, 77.00, '2025', 'TERM 3', 2, '2025-10-15 13:14:04'),
(87, 14, 8, 291, 32, 287, 89.00, '2025', 'TERM 2', 2, '2025-10-15 20:29:03'),
(88, 14, 8, 289, 32, 287, 99.00, '2025', 'TERM 2', 2, '2025-10-15 20:29:03'),
(89, 17, 2, 291, 32, 287, 56.00, '2025', 'TERM 3', 2, '2025-10-16 06:56:34'),
(90, 17, 2, 289, 32, 287, 66.00, '2025', 'TERM 3', 2, '2025-10-16 06:56:34');

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

--
-- Table structure for table `messages`
--

CREATE TABLE `messages` (
  `id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `sender_id` int(11) DEFAULT NULL,
  `receiver_id` int(11) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `message_content` text DEFAULT NULL,
  `status` varchar(50) DEFAULT 'Open',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `messages`
--

INSERT INTO `messages` (`id`, `school_id`, `sender_id`, `receiver_id`, `subject`, `message_content`, `status`, `created_at`) VALUES
(20, 2, 160, 190, 'ENTER MARKS', 'OKAT', 'Unread', '2025-07-28 08:16:06'),
(22, 2, 165, 160, 'ENTER MARKS', 'sir', 'Read', '2025-07-31 15:02:28'),
(24, 2, 196, 160, 'INQUIRY', 'GIVE ATTENTION TO PEOPLE WHO ARE THERE', 'Read', '2025-08-14 06:04:41'),
(28, 2, 160, 196, 'INQUIRY', 'hi', 'Read', '2025-08-16 12:48:42'),
(30, 2, 160, 173, 'INQUIRY', 'ARE YOU SURE', 'Unread', '2025-08-16 15:56:58'),
(31, 2, 160, 174, 'INQUIRY', 'ARE YOU SURE', 'Unread', '2025-08-16 15:56:58'),
(32, 2, 160, 179, 'INQUIRY', 'ARE YOU SURE', 'Unread', '2025-08-16 15:56:58'),
(33, 2, 160, 180, 'INQUIRY', 'ARE YOU SURE', 'Unread', '2025-08-16 15:56:58'),
(34, 2, 160, 181, 'INQUIRY', 'ARE YOU SURE', 'Unread', '2025-08-16 15:56:58'),
(35, 2, 160, 182, 'INQUIRY', 'ARE YOU SURE', 'Read', '2025-08-16 15:56:58'),
(36, 2, 160, 184, 'INQUIRY', 'ARE YOU SURE', 'Unread', '2025-08-16 15:56:58'),
(37, 2, 160, 185, 'INQUIRY', 'ARE YOU SURE', 'Unread', '2025-08-16 15:56:58'),
(38, 2, 160, 186, 'INQUIRY', 'ARE YOU SURE', 'Unread', '2025-08-16 15:56:58'),
(39, 2, 160, 188, 'INQUIRY', 'ARE YOU SURE', 'Unread', '2025-08-16 15:56:58'),
(40, 2, 160, 189, 'INQUIRY', 'ARE YOU SURE', 'Unread', '2025-08-16 15:56:58'),
(41, 2, 160, 173, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(42, 2, 160, 174, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(43, 2, 160, 197, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(44, 2, 160, 208, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(45, 2, 160, 256, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(46, 2, 160, 257, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(47, 2, 160, 258, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(48, 2, 160, 259, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(49, 2, 160, 260, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(50, 2, 160, 261, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(51, 2, 160, 262, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(52, 2, 160, 263, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(53, 2, 160, 264, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(54, 2, 160, 265, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:34:42'),
(55, 2, 160, 265, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Read', '2025-09-18 11:36:39'),
(56, 2, 160, 265, 'HOLIDAY', 'HI, WE HAVE DECIDED TO HAVE OUR HOLIDAYS ON 5TH DECEMBER 2025,\r\nKINDLY COMMUNICATE TO YOUR PARENTS/GUARDIANS TO PICK YOU UP\r\nWISH YOU MERRY X-MASS AND A NEW YEAR\r\n\r\nHEAD TEACHER\r\nBRO. SIMEON SIMON MPANGA', 'Unread', '2025-09-18 11:37:49'),
(57, 2, 196, 160, 'YO', 'MMMM', 'Read', '2025-10-05 15:37:20'),
(58, 2, 160, 268, 'MARKS', 'ENTER MARKS JORAM', 'Unread', '2025-10-13 19:13:14'),
(59, 2, 160, 282, 'MARKS', 'ENTER MARKS JORAM', 'Unread', '2025-10-13 19:13:14'),
(61, 2, 160, 268, 'MARKS', 'ENTER MARKS JORAM', 'Unread', '2025-10-13 19:13:30'),
(62, 2, 160, 282, 'MARKS', 'ENTER MARKS JORAM', 'Unread', '2025-10-13 19:13:30'),
(64, 2, 160, 268, 'MARKS', 'ENTER MARKS JORAM', 'Unread', '2025-10-13 19:13:30'),
(65, 2, 160, 282, 'MARKS', 'ENTER MARKS JORAM', 'Unread', '2025-10-13 19:13:30'),
(67, 2, 160, 268, 'MARKS', 'ENTER MARKS JORAM', 'Unread', '2025-10-13 19:13:55'),
(68, 2, 160, 282, 'MARKS', 'ENTER MARKS JORAM', 'Unread', '2025-10-13 19:13:55'),
(72, 2, 287, 160, 'inquiry', 'where are we entering the marks', 'Unread', '2025-10-14 11:46:19');

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

--
-- Table structure for table `schools`
--

CREATE TABLE `schools` (
  `school_id` int(11) NOT NULL,
  `school_name` varchar(255) NOT NULL,
  `abbreviation` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `motto` varchar(255) DEFAULT 'N/A',
  `website` varchar(255) DEFAULT 'N/A',
  `phone` varchar(50) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `box_number` varchar(50) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `primary_color` varchar(7) DEFAULT '#3B82F6',
  `secondary_color` varchar(7) DEFAULT '#10B981'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `schools`
--

INSERT INTO `schools` (`school_id`, `school_name`, `abbreviation`, `email`, `motto`, `website`, `phone`, `address`, `box_number`, `logo`, `created_at`, `updated_at`, `start_date`, `end_date`, `primary_color`, `secondary_color`) VALUES
(2, 'ST. MARY\'S COLLEGE KISUBI', 'smack', 'info@smack.sc.ug', 'DUC IN ALTUM', 'https://smack.sc.ug/cgi-sys/suspendedpage.cgi', '123-456-7890', 'kisubi (Entebbe)', 'P.O. Box 26', 'uploads/logos/logo_6883e217d37e3.png', '2025-07-11 12:18:20', '2025-08-13 14:44:56', '2025-07-25', '2025-08-22', '#4e8bef', '#10b981'),
(3, 'KAMPALA INTERNATIONAL SCHOOL UGANDA', 'kisu', 'info@kisu.ac.ug', 'N/A', 'N/A', '0414-777-123', 'Kampala, Uganda', 'P.O. Box 9876', NULL, '2025-07-11 13:11:00', '2025-07-11 13:48:15', NULL, NULL, '#3B82F6', '#10B981'),
(4, 'GAYAZA HIGH SCHOOL', 'GHS', 'admin@gayazahigh.ac.ug', 'OBEY GOD AND SHINE', 'https://ghs.sc.ug/cgi-sys/suspendedpage.cgi', '0414-222-345', 'Gayaza, Uganda', 'P.O. Box 4321', 'uploads/logos/logo_6885e3cf81c29.png', '2025-07-11 13:12:00', '2025-07-27 08:31:11', NULL, NULL, '#3bf783', '#bd5656');

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

--
-- Table structure for table `staff`
--

CREATE TABLE `staff` (
  `staff_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `work` varchar(255) DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `staff`
--

INSERT INTO `staff` (`staff_id`, `school_id`, `username`, `password`, `email`, `phone_number`, `photo`, `work`, `department`, `status`, `created_at`, `updated_at`) VALUES
(3, 2, 'OTIM CEASEAR', '$2y$10$psaX5j1CVgG2.JgMP5C7wON/itDUHFb/MXwETSEtPQAjCuiFg3veW', 'ceasearotim10@gmail.com', '0780185856', 'uploads/staff_photos/staff_689ac9a1949ab.jpg', 'LAB TECHNICIAN', 'ICT', 'active', '2025-08-12 04:57:05', '2025-08-12 16:05:17'),
(4, 2, 'admin@smack', '$2y$10$1a1mMByhYrayPp4TMDfh3uajEkuHAO7TFHW.ABtCmOM/bTllxfJ5C', 'peter@gmail.com', '0780185856', 'uploads/staff_photos/staff_689c3e319bcee.jpg', 'LAB TECHNICIAN', 'ICT', 'active', '2025-08-12 15:36:28', '2025-10-01 09:46:34');

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

--
-- Table structure for table `streams`
--

CREATE TABLE `streams` (
  `stream_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `stream_name` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `assigned_teacher_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `streams`
--

INSERT INTO `streams` (`stream_id`, `school_id`, `class_id`, `stream_name`, `created_at`, `updated_at`, `assigned_teacher_id`) VALUES
(25, 4, 16, 'RED', '2025-07-27 08:34:59', '2025-07-27 08:34:59', NULL),
(31, 2, 40, 'RED', '2025-08-21 09:30:25', '2025-08-21 09:30:25', NULL),
(32, 2, 41, 'A', '2025-10-13 06:54:58', '2025-10-13 06:54:58', NULL),
(33, 2, 41, 'B', '2025-10-13 07:57:39', '2025-10-13 07:57:39', NULL);

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

--
-- Table structure for table `students`
--

CREATE TABLE `students` (
  `ADM` varchar(50) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `school_id` int(11) NOT NULL,
  `class` varchar(50) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  `stream` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `student_enrollment`
--

CREATE TABLE `student_enrollment` (
  `enrollment_id` int(11) NOT NULL COMMENT 'Unique ID for each enrollment record',
  `user_id` int(11) NOT NULL COMMENT 'Foreign key to the users table (the user who is a student and is enrolled)',
  `class_id` int(11) NOT NULL COMMENT 'Foreign key to the classes table (which class they are in)',
  `stream_id` int(11) DEFAULT NULL COMMENT 'Foreign key to the streams table (optional stream within the class)',
  `enrollment_date` date NOT NULL COMMENT 'Date when the student enrolled in this class/stream for the period',
  `academic_year` varchar(50) NOT NULL COMMENT 'The academic year (e.g., "2025/2026") of this enrollment',
  `term` varchar(50) NOT NULL COMMENT 'The academic term (e.g., "Term 1") of this enrollment'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Records student enrollment in classes/streams by academic year and term';

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

--
-- Table structure for table `subjects`
--

CREATE TABLE `subjects` (
  `subject_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `subject_name` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `subjects`
--

INSERT INTO `subjects` (`subject_id`, `school_id`, `subject_name`, `created_at`, `updated_at`) VALUES
(1, 2, 'PHYSICS', '2025-07-12 18:57:01', '2025-07-12 18:57:01'),
(2, 2, 'AGRICULTURE', '2025-07-12 18:59:21', '2025-07-12 18:59:21'),
(3, 2, 'BIOLOGY', '2025-07-12 18:59:26', '2025-07-12 18:59:26'),
(5, 2, 'CRE', '2025-07-12 18:59:35', '2025-07-12 18:59:35'),
(7, 4, 'PHYSICS', '2025-07-15 17:23:09', '2025-07-15 17:23:09'),
(8, 2, 'CHEMISTRY', '2025-07-30 14:05:22', '2025-07-30 14:05:22'),
(9, 2, 'MATHEMATICS', '2025-07-30 14:05:34', '2025-07-30 14:05:34'),
(10, 2, 'HISTORY', '2025-07-30 14:05:41', '2025-07-30 14:05:41'),
(11, 2, 'GEOGRAPHY', '2025-07-30 14:05:50', '2025-07-30 14:05:50'),
(12, 2, 'ENGLISH', '2025-07-30 14:05:59', '2025-07-30 14:05:59'),
(13, 2, 'LITERATURE', '2025-07-30 14:06:06', '2025-07-30 14:06:06'),
(14, 2, 'FRENCH', '2025-07-30 14:06:12', '2025-07-30 14:06:12'),
(15, 2, 'TECHNOLOGY & DESIGN', '2025-07-30 14:06:27', '2025-07-30 14:06:27'),
(16, 4, 'BIOLOGY', '2025-08-11 11:46:18', '2025-08-11 11:46:18');

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

--
-- Table structure for table `subject_papers`
--

CREATE TABLE `subject_papers` (
  `paper_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `paper_name` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `subject_papers`
--

INSERT INTO `subject_papers` (`paper_id`, `subject_id`, `school_id`, `paper_name`, `created_at`, `updated_at`) VALUES
(1, 1, 2, 'P553/2', '2025-07-12 18:57:21', '2025-07-12 19:15:58'),
(2, 3, 2, 'P456/1', '2025-07-12 19:48:14', '2025-07-12 19:48:14'),
(3, 1, 2, 'P553/1', '2025-07-12 19:49:13', '2025-07-12 19:49:13'),
(5, 2, 2, 'P510/3', '2025-08-12 18:48:12', '2025-08-12 18:48:12');

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

--
-- Table structure for table `teachers`
--

CREATE TABLE `teachers` (
  `teacher_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `school_id` int(11) NOT NULL,
  `subject_taught` varchar(255) DEFAULT NULL,
  `class_assigned` varchar(255) DEFAULT NULL,
  `signature_path` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `teachers`
--

INSERT INTO `teachers` (`teacher_id`, `user_id`, `school_id`, `subject_taught`, `class_assigned`, `signature_path`) VALUES
(3, 177, 4, NULL, NULL, NULL),
(6, 195, 2, NULL, NULL, NULL),
(7, 196, 2, NULL, NULL, NULL),
(8, 268, 2, NULL, NULL, NULL),
(9, 282, 2, NULL, NULL, NULL),
(10, 287, 2, NULL, NULL, NULL);

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

--
-- Table structure for table `teacher_subjects`
--

CREATE TABLE `teacher_subjects` (
  `teacher_user_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `teacher_subjects`
--

INSERT INTO `teacher_subjects` (`teacher_user_id`, `subject_id`) VALUES
(164, 3),
(164, 5),
(164, 14),
(165, 2),
(165, 3);

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

--
-- Table structure for table `topics`
--

CREATE TABLE `topics` (
  `topic_id` int(11) NOT NULL,
  `paper_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `topic_name` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `topics`
--

INSERT INTO `topics` (`topic_id`, `paper_id`, `school_id`, `topic_name`, `created_at`, `updated_at`) VALUES
(1, 1, 2, 'INTRODUCTION', '2025-07-12 18:57:39', '2025-07-12 18:57:39'),
(2, 1, 2, 'critical thinking', '2025-07-12 18:57:53', '2025-07-12 18:57:53'),
(3, 2, 2, 'INTRODUCTION', '2025-07-13 07:19:28', '2025-07-13 07:19:28'),
(4, 5, 2, 'MARRK', '2025-08-12 19:43:30', '2025-08-12 19:43:30'),
(6, 5, 2, 'introduction', '2025-09-18 08:06:00', '2025-09-18 08:06:00'),
(8, 5, 2, 'farming', '2025-09-18 08:07:08', '2025-09-18 08:07:08');

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

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `reset_code` varchar(6) DEFAULT NULL,
  `reset_code_expires_at` datetime DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `role` int(11) NOT NULL DEFAULT 3,
  `class_id` int(11) DEFAULT NULL,
  `stream_id` int(11) DEFAULT NULL,
  `parent_name` varchar(255) DEFAULT NULL,
  `parent_phone` varchar(20) DEFAULT NULL,
  `parent_email` varchar(100) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `email` varchar(255) DEFAULT NULL,
  `ADM` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `school_id`, `username`, `password`, `reset_code`, `reset_code_expires_at`, `phone_number`, `photo`, `role`, `class_id`, `stream_id`, `parent_name`, `parent_phone`, `parent_email`, `status`, `email`, `ADM`, `created_at`) VALUES
(160, 2, 'admin', '$2y$10$lN.5UqDZ33c7M32eJsKl..CBgjboNpqsW6XVuICwLhWjFaSRm2ova', NULL, NULL, NULL, 'uploads/avatars/profile_68873c538436a.JPG', 1, NULL, NULL, NULL, NULL, NULL, 'active', 'johnsonmubiito@gmail.com', NULL, '2025-07-06 06:55:40'),
(162, 3, 'admin2', '$2y$10$rFarTWUH1HfFadpZcQ8gpenszpamAtttpeFOnIlLzgs7.qrENPtj2', NULL, NULL, '0772123456', NULL, 1, NULL, NULL, NULL, NULL, NULL, 'active', 'admin@kisu.ac.ug', NULL, '2025-07-11 13:15:00'),
(163, 4, 'admin3', '$2y$10$rFarTWUH1HfFadpZcQ8gpenszpamAtttpeFOnIlLzgs7.qrENPtj2', NULL, NULL, '0773987654', 'uploads/avatars/profile_6899d85799c5b.jpg', 1, NULL, NULL, NULL, NULL, NULL, 'active', 'admin@gayazahigh.ac.ug', NULL, '2025-07-11 13:16:00'),
(167, 2, 'superadmin', '$2y$10$YourGeneratedHashedPasswordHere', NULL, NULL, '0712345678', NULL, 0, NULL, NULL, NULL, NULL, NULL, 'active', 'john.doe@example.com', NULL, '2025-07-13 09:01:16'),
(168, 3, 'janedoe', '$2y$10$AnotherGeneratedHashedPassword', NULL, NULL, '0798765432', NULL, 2, NULL, NULL, NULL, NULL, NULL, 'active', 'jane.doe@another.com', NULL, '2025-07-13 09:01:16'),
(176, 4, 'MUWANGA KIVUMBI', '$2y$10$GjOAgxJI1vMTG0w/.O3AYejF7HOgG0rqfqmWbNIU9FMHf6d12n2Zy', NULL, NULL, '', 'uploads/student_photos/student_68768e349ffa2.jpg', 3, 16, 0, '', '', '', 'active', 'muwanga@ghs.com', 'GGHS001', '2025-07-15 17:21:56'),
(177, 4, 'OKELLO OKELLO', '$2y$10$Uqa6ZIyX0/o9ooiOUMjDquucqinSUrMosmUnhcZQwhrGD9gMiAC.a', NULL, NULL, '07777777777', 'uploads/teacher_photos/teacher_68768eb7779ff.jpg', 2, NULL, NULL, NULL, NULL, NULL, '0', 'okello@ghs', NULL, '2025-07-15 17:24:07'),
(268, 2, 'OKELLO SAM LOCO', '$2y$10$5AIAwyFR9hQVDpVAna39QO/WZuzMrHk1Vs/qftrW786TD.O6gL.HK', NULL, NULL, '0780185856', 'uploads/teachers/teacher_68df5b03b0b41.jpg', 2, NULL, NULL, NULL, NULL, NULL, 'active', 'sam@gmail.com', NULL, '2025-10-03 05:11:10'),
(282, 2, 'OTIM CEASEAR', '$2y$10$lOXs4PgH4yT4b2DP3lx0he1qrBGmnUjK7IZ3IgafNowlN4F0d1xEW', NULL, NULL, '0780185856', 'uploads/teacher_photos/teacher_68e2a28f43769.jpg', 2, NULL, NULL, NULL, NULL, NULL, 'active', 'ceasear@gmail.com', NULL, '2025-10-05 15:39:45'),
(287, 2, 'BWAMBALE JORAM', '$2y$10$/oxU1Dcwd/t/eQM5ME/4q.vOP0KfsFhwMbiAPzKWPUWS6uj9I8f96', NULL, NULL, '0780185856', 'uploads/teachers/teacher_68e34d8c3a17e.jpg', 2, NULL, NULL, NULL, NULL, NULL, 'active', 'admin@smack.com', NULL, '2025-10-06 05:02:38'),
(289, 2, 'ODONGO ABEL', '$2y$10$ATco6b1M.hCAwDG6dXbu8OXYoefhsGj9p.lB6emxb/7Ce5Y1JsWna', NULL, NULL, '099887788', 'uploads/student_photos/student_68e358b157d1c.png', 3, 41, 32, 'mukasa tom', '0099999', NULL, 'active', 'abel@gmail.com', 'S002', '2025-10-06 05:45:03'),
(291, 2, 'JOHNSON MUBIITO', '$2y$10$COvO8cJXkdFhh/oY8VGoT.9mGh5NI9.LQHkQLak4XNhQqzT3r5mt2', NULL, NULL, '0703946946', 'uploads/student_photos/default_student.png', 3, 41, 32, '', '', NULL, 'active', 'mubito@gmail.com', 'S003', '2025-10-14 11:44:25'),
(292, 2, 'OWO', '$2y$10$AwFbf7RHHb4z/betf0GZn.sAvmQOwtnhls/QFIDLISN.jNmDLSsBq', NULL, NULL, '0780186867', 'uploads/student_photos/student_68f0baf806be4.jpg', 3, 41, 32, '', '', NULL, 'active', 'owo@gmail.com', 'S004', '2025-10-16 09:18:20'),
(293, 2, 'Peter Johnson', '$2y$10$ugO.eCovfeWFl4zek5kjVuiaaGZpD3ZWBZ6.r2R75Z/.gtpQ2NS32', NULL, NULL, '702345678', 'uploads/student_photos/default_student.png', 3, 41, 32, '', '', NULL, 'active', 'peter@email.com', 'ADM003', '2025-10-16 09:41:14'),
(295, 2, 'ATIM HARIET', '$2y$10$Bpgckp2yja3U5TdJMwXewOLg9b8IcF.X85YiBWEKRrW2sX4oq5f32', NULL, NULL, '', 'uploads/student_photos/default_student.png', 3, 41, 32, '', '', NULL, 'active', 'harriet@gmail.com', 'S005', '2025-10-16 10:55:51');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `academic_years_terms`
--
ALTER TABLE `academic_years_terms`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `school_id` (`school_id`,`academic_year`,`term_name`);

--
-- Indexes for table `announcements`
--
ALTER TABLE `announcements`
  ADD PRIMARY KEY (`id`),
  ADD KEY `school_id` (`school_id`);

--
-- Indexes for table `assignments`
--
ALTER TABLE `assignments`
  ADD PRIMARY KEY (`assignment_id`),
  ADD KEY `fk_assignments_school_id` (`school_id`),
  ADD KEY `fk_assignments_teacher_user_id` (`teacher_user_id`),
  ADD KEY `fk_assignments_subject_id` (`subject_id`),
  ADD KEY `fk_assignments_stream_id` (`stream_id`);

--
-- Indexes for table `assignment_submissions`
--
ALTER TABLE `assignment_submissions`
  ADD PRIMARY KEY (`submission_id`),
  ADD UNIQUE KEY `uk_assignment_student` (`assignment_id`,`student_id`),
  ADD KEY `student_id` (`student_id`);

--
-- Indexes for table `classes`
--
ALTER TABLE `classes`
  ADD PRIMARY KEY (`class_id`),
  ADD UNIQUE KEY `uq_class_name_school_id` (`class_name`,`school_id`),
  ADD KEY `fk_classes_school_id` (`school_id`),
  ADD KEY `fk_assigned_teacher_to_teachers` (`assigned_teacher_id`),
  ADD KEY `fk_class_stream` (`stream_id`);

--
-- Indexes for table `class_streams`
--
ALTER TABLE `class_streams`
  ADD PRIMARY KEY (`class_stream_id`),
  ADD UNIQUE KEY `uq_class_stream` (`class_id`,`stream_name`);

--
-- Indexes for table `class_subjects`
--
ALTER TABLE `class_subjects`
  ADD PRIMARY KEY (`class_id`,`subject_id`),
  ADD KEY `fk_class_subjects_subject_id` (`subject_id`);

--
-- Indexes for table `class_subject_teachers`
--
ALTER TABLE `class_subject_teachers`
  ADD PRIMARY KEY (`assignment_id`),
  ADD UNIQUE KEY `class_subject_teachers_unique_assignment` (`class_id`,`subject_id`,`stream_id`,`assignment_slot`),
  ADD KEY `subject_id` (`subject_id`),
  ADD KEY `teacher_user_id` (`teacher_user_id`),
  ADD KEY `fk_class_subject_teachers_stream_id` (`stream_id`);

--
-- Indexes for table `class_teachers`
--
ALTER TABLE `class_teachers`
  ADD PRIMARY KEY (`class_id`,`teacher_user_id`),
  ADD KEY `fk_class_teachers_teacher_user_id` (`teacher_user_id`);

--
-- Indexes for table `competencies`
--
ALTER TABLE `competencies`
  ADD PRIMARY KEY (`competency_id`),
  ADD UNIQUE KEY `unique_competency_per_topic_school` (`topic_id`,`school_id`,`competency_name`(250)),
  ADD KEY `school_id` (`school_id`);

--
-- Indexes for table `events`
--
ALTER TABLE `events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `school_id` (`school_id`);

--
-- Indexes for table `exams`
--
ALTER TABLE `exams`
  ADD PRIMARY KEY (`exam_id`),
  ADD KEY `school_id` (`school_id`),
  ADD KEY `idx_exams_status_locked` (`status`,`marks_locked`);

--
-- Indexes for table `exam_classes`
--
ALTER TABLE `exam_classes`
  ADD PRIMARY KEY (`exam_class_id`),
  ADD UNIQUE KEY `exam_id` (`exam_id`,`class_id`),
  ADD KEY `class_id` (`class_id`);

--
-- Indexes for table `exam_subjects`
--
ALTER TABLE `exam_subjects`
  ADD PRIMARY KEY (`exam_subject_id`),
  ADD UNIQUE KEY `uq_exam_subject` (`exam_id`,`subject_id`),
  ADD KEY `subject_id` (`subject_id`);

--
-- Indexes for table `grading_scales`
--
ALTER TABLE `grading_scales`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_grading_scale_context` (`school_id`,`academic_year`,`term_name`,`class_id`,`subject_id`,`grade_letter`),
  ADD KEY `fk_grading_scales_class_id` (`class_id`),
  ADD KEY `fk_grading_scales_subject_id` (`subject_id`),
  ADD KEY `fk_grading_scales_created_by` (`created_by`),
  ADD KEY `fk_grading_scales_updated_by` (`updated_by`);

--
-- Indexes for table `integration_activities`
--
ALTER TABLE `integration_activities`
  ADD PRIMARY KEY (`activity_id`),
  ADD KEY `school_id` (`school_id`),
  ADD KEY `teacher_user_id` (`teacher_user_id`),
  ADD KEY `subject_id` (`subject_id`),
  ADD KEY `stream_id` (`stream_id`),
  ADD KEY `topic_id` (`topic_id`),
  ADD KEY `competency_id` (`competency_id`);

--
-- Indexes for table `integration_activity_marks`
--
ALTER TABLE `integration_activity_marks`
  ADD PRIMARY KEY (`mark_id`),
  ADD UNIQUE KEY `unique_activity_student` (`activity_id`,`student_id`),
  ADD KEY `idx_activity` (`activity_id`),
  ADD KEY `idx_student` (`student_id`),
  ADD KEY `idx_subject_stream` (`subject_id`,`stream_id`),
  ADD KEY `idx_school` (`school_id`),
  ADD KEY `integration_activity_marks_ibfk_4` (`stream_id`);

--
-- Indexes for table `marks`
--
ALTER TABLE `marks`
  ADD PRIMARY KEY (`mark_id`),
  ADD UNIQUE KEY `uq_exam_subject_student_stream` (`exam_id`,`subject_id`,`student_id`,`stream_id`,`academic_year`,`term`,`school_id`),
  ADD KEY `fk_marks_exam_id` (`exam_id`),
  ADD KEY `fk_marks_subject_id` (`subject_id`),
  ADD KEY `fk_marks_student_id` (`student_id`),
  ADD KEY `fk_marks_class_id` (`stream_id`),
  ADD KEY `fk_marks_teacher_user_id` (`teacher_user_id`),
  ADD KEY `fk_marks_school_id` (`school_id`);

--
-- Indexes for table `messages`
--
ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `school_id` (`school_id`);

--
-- Indexes for table `schools`
--
ALTER TABLE `schools`
  ADD PRIMARY KEY (`school_id`),
  ADD UNIQUE KEY `school_name` (`school_name`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `abbreviation` (`abbreviation`);

--
-- Indexes for table `staff`
--
ALTER TABLE `staff`
  ADD PRIMARY KEY (`staff_id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_staff_school_id` (`school_id`);

--
-- Indexes for table `streams`
--
ALTER TABLE `streams`
  ADD PRIMARY KEY (`stream_id`),
  ADD UNIQUE KEY `uq_stream_name_class_id_school_id` (`stream_name`,`class_id`,`school_id`),
  ADD KEY `fk_streams_school_id` (`school_id`),
  ADD KEY `fk_streams_class_id` (`class_id`),
  ADD KEY `fk_stream_assigned_teacher_to_teachers` (`assigned_teacher_id`);

--
-- Indexes for table `students`
--
ALTER TABLE `students`
  ADD PRIMARY KEY (`ADM`),
  ADD KEY `fk_students_school_id` (`school_id`),
  ADD KEY `fk_students_users` (`user_id`),
  ADD KEY `fk_students_class_id` (`class_id`);

--
-- Indexes for table `student_enrollment`
--
ALTER TABLE `student_enrollment`
  ADD PRIMARY KEY (`enrollment_id`),
  ADD KEY `fk_student_enrollment_user_id` (`user_id`),
  ADD KEY `fk_student_enrollment_class_id` (`class_id`),
  ADD KEY `fk_student_enrollment_stream_id` (`stream_id`);

--
-- Indexes for table `subjects`
--
ALTER TABLE `subjects`
  ADD PRIMARY KEY (`subject_id`),
  ADD UNIQUE KEY `unique_subject_per_school` (`school_id`,`subject_name`);

--
-- Indexes for table `subject_papers`
--
ALTER TABLE `subject_papers`
  ADD PRIMARY KEY (`paper_id`),
  ADD UNIQUE KEY `unique_paper_per_subject_school` (`subject_id`,`school_id`,`paper_name`),
  ADD KEY `school_id` (`school_id`);

--
-- Indexes for table `teachers`
--
ALTER TABLE `teachers`
  ADD PRIMARY KEY (`teacher_id`),
  ADD KEY `idx_teachers_school_id` (`school_id`),
  ADD KEY `fk_teachers_users` (`user_id`);

--
-- Indexes for table `teacher_subjects`
--
ALTER TABLE `teacher_subjects`
  ADD PRIMARY KEY (`teacher_user_id`,`subject_id`),
  ADD KEY `fk_teacher_subjects_subject_id` (`subject_id`);

--
-- Indexes for table `topics`
--
ALTER TABLE `topics`
  ADD PRIMARY KEY (`topic_id`),
  ADD UNIQUE KEY `unique_topic_per_paper_school` (`paper_id`,`school_id`,`topic_name`),
  ADD KEY `school_id` (`school_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username_school_unique` (`username`,`school_id`),
  ADD UNIQUE KEY `email_school_unique` (`email`,`school_id`),
  ADD UNIQUE KEY `ADM` (`ADM`),
  ADD KEY `fk_users_school_id` (`school_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

--
-- AUTO_INCREMENT for table `assignments`
--
ALTER TABLE `assignments`
  MODIFY `assignment_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `assignment_submissions`
--
ALTER TABLE `assignment_submissions`
  MODIFY `submission_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `classes`
--
ALTER TABLE `classes`
  MODIFY `class_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=49;

--
-- AUTO_INCREMENT for table `class_streams`
--
ALTER TABLE `class_streams`
  MODIFY `class_stream_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `class_subject_teachers`
--
ALTER TABLE `class_subject_teachers`
  MODIFY `assignment_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=103;

--
-- AUTO_INCREMENT for table `competencies`
--
ALTER TABLE `competencies`
  MODIFY `competency_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

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

--
-- AUTO_INCREMENT for table `exams`
--
ALTER TABLE `exams`
  MODIFY `exam_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;

--
-- AUTO_INCREMENT for table `exam_classes`
--
ALTER TABLE `exam_classes`
  MODIFY `exam_class_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=61;

--
-- AUTO_INCREMENT for table `exam_subjects`
--
ALTER TABLE `exam_subjects`
  MODIFY `exam_subject_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=172;

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

--
-- AUTO_INCREMENT for table `integration_activities`
--
ALTER TABLE `integration_activities`
  MODIFY `activity_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

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

--
-- AUTO_INCREMENT for table `marks`
--
ALTER TABLE `marks`
  MODIFY `mark_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=91;

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

--
-- AUTO_INCREMENT for table `schools`
--
ALTER TABLE `schools`
  MODIFY `school_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `staff`
--
ALTER TABLE `staff`
  MODIFY `staff_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `streams`
--
ALTER TABLE `streams`
  MODIFY `stream_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=34;

--
-- AUTO_INCREMENT for table `student_enrollment`
--
ALTER TABLE `student_enrollment`
  MODIFY `enrollment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for each enrollment record', AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `subjects`
--
ALTER TABLE `subjects`
  MODIFY `subject_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

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

--
-- AUTO_INCREMENT for table `teachers`
--
ALTER TABLE `teachers`
  MODIFY `teacher_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `topics`
--
ALTER TABLE `topics`
  MODIFY `topic_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `academic_years_terms`
--
ALTER TABLE `academic_years_terms`
  ADD CONSTRAINT `academic_years_terms_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `schools` (`school_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `announcements`
--
ALTER TABLE `announcements`
  ADD CONSTRAINT `announcements_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `schools` (`school_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `assignment_submissions`
--
ALTER TABLE `assignment_submissions`
  ADD CONSTRAINT `assignment_submissions_ibfk_1` FOREIGN KEY (`assignment_id`) REFERENCES `assignments` (`assignment_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `assignment_submissions_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `classes`
--
ALTER TABLE `classes`
  ADD CONSTRAINT `fk_assigned_teacher_to_teachers` FOREIGN KEY (`assigned_teacher_id`) REFERENCES `teachers` (`teacher_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_class_stream` FOREIGN KEY (`stream_id`) REFERENCES `streams` (`stream_id`),
  ADD CONSTRAINT `fk_classes_school_id` FOREIGN KEY (`school_id`) REFERENCES `schools` (`school_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `events`
--
ALTER TABLE `events`
  ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `events_ibfk_2` FOREIGN KEY (`school_id`) REFERENCES `schools` (`school_id`) ON DELETE CASCADE;

--
-- Constraints for table `grading_scales`
--
ALTER TABLE `grading_scales`
  ADD CONSTRAINT `fk_grading_scales_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_grading_scales_school_id` FOREIGN KEY (`school_id`) REFERENCES `schools` (`school_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_grading_scales_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `integration_activities`
--
ALTER TABLE `integration_activities`
  ADD CONSTRAINT `integration_activities_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `schools` (`school_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `integration_activities_ibfk_2` FOREIGN KEY (`teacher_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `integration_activities_ibfk_3` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `integration_activities_ibfk_4` FOREIGN KEY (`stream_id`) REFERENCES `streams` (`stream_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `integration_activities_ibfk_5` FOREIGN KEY (`topic_id`) REFERENCES `topics` (`topic_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `integration_activities_ibfk_6` FOREIGN KEY (`competency_id`) REFERENCES `competencies` (`competency_id`) ON DELETE CASCADE;

--
-- Constraints for table `integration_activity_marks`
--
ALTER TABLE `integration_activity_marks`
  ADD CONSTRAINT `integration_activity_marks_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `integration_activities` (`activity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `integration_activity_marks_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `integration_activity_marks_ibfk_3` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `integration_activity_marks_ibfk_4` FOREIGN KEY (`stream_id`) REFERENCES `streams` (`stream_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `integration_activity_marks_ibfk_5` FOREIGN KEY (`school_id`) REFERENCES `schools` (`school_id`) ON DELETE CASCADE;

--
-- Constraints for table `students`
--
ALTER TABLE `students`
  ADD CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `student_enrollment`
--
ALTER TABLE `student_enrollment`
  ADD CONSTRAINT `fk_student_enrollment_class_id` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_student_enrollment_stream_id` FOREIGN KEY (`stream_id`) REFERENCES `streams` (`stream_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_student_enrollment_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

/*!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 */;
