-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jan 21, 2026 at 03:58 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.1.25

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: `school_portal`
--

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

--
-- Table structure for table `activity_participation`
--

CREATE TABLE `activity_participation` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `student_id` bigint(20) UNSIGNED NOT NULL,
  `activity_id` bigint(20) UNSIGNED NOT NULL,
  `join_date` date NOT NULL,
  `status` varchar(50) DEFAULT 'active',
  `participation_level` varchar(100) DEFAULT NULL,
  `achievements` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `announcements` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(200) NOT NULL,
  `content` text NOT NULL,
  `type` enum('general','class','emergency','event') NOT NULL DEFAULT 'general',
  `target_class_id` bigint(20) UNSIGNED DEFAULT NULL,
  `priority` enum('low','medium','high') DEFAULT 'medium',
  `created_by` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `expires_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `announcements`
--

INSERT INTO `announcements` (`id`, `title`, `content`, `type`, `target_class_id`, `priority`, `created_by`, `created_at`, `expires_at`) VALUES
(1, 'Welcome to the New Academic Year', 'Welcome to 2025/2026 academic session. We are excited to have all our students back. Let us strive for excellence in all our endeavors.', 'general', NULL, 'high', 9, '2026-01-21 10:05:02', '2026-12-31 23:59:59'),
(2, 'School Fees Payment Reminder', 'Please note that school fees for the 1st term are due by December 31st, 2025. Parents are requested to settle outstanding fees as soon as possible.', 'general', NULL, 'high', 9, '2026-01-21 10:05:02', '2026-02-28 23:59:59'),
(3, 'Sports Day Announcement', 'The school sports day will hold on Saturday, February 14th, 2026. All students are expected to participate. Uniforms will be provided.', 'event', NULL, 'medium', 9, '2026-01-21 10:05:02', '2026-02-14 23:59:59'),
(4, 'Primary 4A Class Assignment', 'Class assignment for Mathematics: Complete Chapter 5, exercises 1-10. Submission date: Tuesday, January 22nd.', 'class', 1, 'medium', 6, '2026-01-21 10:05:02', '2026-01-22 23:59:59'),
(5, 'JSS 1B Science Project', 'Group project on renewable energy due on Friday, January 24th. Groups have been assigned.', 'class', 5, 'medium', 7, '2026-01-21 10:05:02', '2026-01-24 23:59:59'),
(6, 'School Closure Notice', 'Due to pending weather conditions, school will be closed on Wednesday, January 21st. Resume Thursday, January 22nd.', 'emergency', NULL, 'high', 9, '2026-01-21 10:05:02', '2026-01-21 18:00:00');

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

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

CREATE TABLE `assignments` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `class_id` bigint(20) UNSIGNED NOT NULL,
  `subject_id` bigint(20) UNSIGNED NOT NULL,
  `teacher_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `instructions` text DEFAULT NULL,
  `assigned_date` date NOT NULL,
  `due_date` date NOT NULL,
  `due_time` time DEFAULT NULL,
  `assignment_type` varchar(50) DEFAULT 'homework',
  `total_marks` decimal(5,2) DEFAULT 0.00,
  `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 `assignments`
--

INSERT INTO `assignments` (`id`, `class_id`, `subject_id`, `teacher_id`, `title`, `description`, `instructions`, `assigned_date`, `due_date`, `due_time`, `assignment_type`, `total_marks`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 6, 'Algebra Practice Set', 'Complete exercises on linear equations from chapter 5', 'Show all workings. Submit on A4 paper or typed document.', '2026-01-15', '2026-01-20', '16:00:00', 'homework', 20.00, '2026-01-21 10:05:04', '2026-01-21 10:05:04'),
(2, 1, 2, 7, 'Essay Writing', 'Write a 500-word essay on \"My Dreams for the Future\"', 'Use Times New Roman, 12pt font. Double-spaced. Include introduction and conclusion.', '2026-01-16', '2026-01-23', '14:00:00', 'assignment', 25.00, '2026-01-21 10:05:04', '2026-01-21 10:05:04'),
(3, 1, 3, 6, 'Science Experiment Report', 'Conduct acid-base reaction experiment and write detailed report', 'Follow lab report format. Include hypothesis, observations, and conclusion.', '2026-01-18', '2026-01-27', '15:00:00', 'project', 30.00, '2026-01-21 10:05:04', '2026-01-21 10:05:04'),
(4, 1, 1, 6, 'Geometry Problem Solving', 'Solve 10 geometry problems on angles and triangles', 'Draw diagrams for each problem. Label all angles.', '2026-01-17', '2026-01-22', '16:00:00', 'classwork', 15.00, '2026-01-21 10:05:04', '2026-01-21 10:05:04'),
(5, 2, 6, 7, 'Quadratic Equations', 'Solve 20 quadratic equation problems', 'Show step-by-step solutions.', '2026-01-14', '2026-01-19', '16:00:00', 'homework', 20.00, '2026-01-21 10:05:04', '2026-01-21 10:05:04');

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

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

CREATE TABLE `assignment_submissions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `assignment_id` bigint(20) UNSIGNED NOT NULL,
  `student_id` bigint(20) UNSIGNED NOT NULL,
  `submission_date` date NOT NULL,
  `submission_time` time NOT NULL,
  `submission_content` text DEFAULT NULL,
  `submission_status` varchar(50) DEFAULT 'pending',
  `marks_obtained` decimal(5,2) DEFAULT NULL,
  `teacher_feedback` text DEFAULT NULL,
  `graded_at` datetime DEFAULT NULL,
  `graded_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `assignment_submissions`
--

INSERT INTO `assignment_submissions` (`id`, `assignment_id`, `student_id`, `submission_date`, `submission_time`, `submission_content`, `submission_status`, `marks_obtained`, `teacher_feedback`, `graded_at`, `graded_by`, `created_at`) VALUES
(1, 1, 1, '2026-01-19', '15:30:00', 'Submitted algebra exercises', 'graded', 18.00, 'Excellent work! All answers correct.', '2026-01-20 10:00:00', 6, '2026-01-21 10:05:04'),
(2, 1, 2, '2026-01-20', '16:00:00', 'Submitted algebra exercises', 'graded', 16.00, 'Good effort, but some calculation errors.', '2026-01-20 10:30:00', 6, '2026-01-21 10:05:04'),
(3, 1, 3, '2026-01-20', '17:00:00', 'Submitted algebra exercises', 'late', 14.00, 'Late submission accepted, but deducted 2 marks.', '2026-01-21 09:00:00', 6, '2026-01-21 10:05:04'),
(4, 2, 1, '2026-01-23', '13:45:00', 'Essay submitted', 'graded', 23.00, 'Outstanding essay! Great flow and ideas.', '2026-01-24 14:00:00', 7, '2026-01-21 10:05:04'),
(5, 2, 2, '2026-01-23', '14:15:00', 'Essay submitted', 'graded', 20.00, 'Good essay, needs better grammar and punctuation.', '2026-01-24 14:30:00', 7, '2026-01-21 10:05:04'),
(6, 3, 1, '2026-01-27', '14:00:00', 'Lab report submitted', 'graded', 28.00, 'Excellent experiment report. Very detailed observations.', '2026-01-28 11:00:00', 6, '2026-01-21 10:05:04');

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

--
-- Table structure for table `attendance`
--

CREATE TABLE `attendance` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `student_id` bigint(20) UNSIGNED NOT NULL,
  `attendance_date` date NOT NULL,
  `status` enum('present','absent','late','excused') NOT NULL,
  `reason` text DEFAULT NULL,
  `marked_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `attendance`
--

INSERT INTO `attendance` (`id`, `student_id`, `attendance_date`, `status`, `reason`, `marked_by`, `created_at`) VALUES
(1, 1, '2026-01-20', 'present', NULL, 6, '2026-01-21 10:05:02'),
(2, 1, '2026-01-19', 'present', NULL, 6, '2026-01-21 10:05:02'),
(3, 1, '2026-01-18', 'late', 'Traffic', 6, '2026-01-21 10:05:02'),
(4, 1, '2026-01-17', 'present', NULL, 6, '2026-01-21 10:05:02'),
(5, 1, '2026-01-16', 'present', NULL, 6, '2026-01-21 10:05:02'),
(6, 1, '2026-01-15', 'absent', 'Sick', 6, '2026-01-21 10:05:02'),
(7, 1, '2026-01-14', 'present', NULL, 6, '2026-01-21 10:05:02'),
(8, 1, '2026-01-13', 'present', NULL, 6, '2026-01-21 10:05:02'),
(9, 1, '2026-01-12', 'present', NULL, 6, '2026-01-21 10:05:02'),
(10, 1, '2026-01-11', 'present', NULL, 6, '2026-01-21 10:05:02'),
(11, 2, '2026-01-20', 'present', NULL, 6, '2026-01-21 10:05:02'),
(12, 2, '2026-01-19', 'present', NULL, 6, '2026-01-21 10:05:02'),
(13, 2, '2026-01-18', 'present', NULL, 6, '2026-01-21 10:05:02'),
(14, 2, '2026-01-17', 'present', NULL, 6, '2026-01-21 10:05:02'),
(15, 2, '2026-01-16', 'late', 'Overslept', 6, '2026-01-21 10:05:02'),
(16, 2, '2026-01-15', 'present', NULL, 6, '2026-01-21 10:05:02'),
(17, 2, '2026-01-14', 'absent', 'Family event', 6, '2026-01-21 10:05:02'),
(18, 2, '2026-01-13', 'present', NULL, 6, '2026-01-21 10:05:02'),
(19, 2, '2026-01-12', 'present', NULL, 6, '2026-01-21 10:05:02'),
(20, 2, '2026-01-11', 'present', NULL, 6, '2026-01-21 10:05:02'),
(21, 3, '2026-01-20', 'present', NULL, 6, '2026-01-21 10:05:02'),
(22, 3, '2026-01-19', 'absent', NULL, 6, '2026-01-21 10:05:02'),
(23, 3, '2026-01-18', 'present', NULL, 6, '2026-01-21 10:05:02'),
(24, 3, '2026-01-17', 'present', NULL, 6, '2026-01-21 10:05:02'),
(25, 3, '2026-01-16', 'present', NULL, 6, '2026-01-21 10:05:02'),
(26, 3, '2026-01-15', 'present', NULL, 6, '2026-01-21 10:05:02'),
(27, 3, '2026-01-14', 'present', NULL, 6, '2026-01-21 10:05:02'),
(28, 3, '2026-01-13', 'late', 'Doctor appointment', 6, '2026-01-21 10:05:02'),
(29, 3, '2026-01-12', 'present', NULL, 6, '2026-01-21 10:05:02'),
(30, 3, '2026-01-11', 'present', NULL, 6, '2026-01-21 10:05:02');

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

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

CREATE TABLE `classes` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `class_name` varchar(50) NOT NULL,
  `section` varchar(20) DEFAULT NULL,
  `class_teacher_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `classes` (`id`, `class_name`, `section`, `class_teacher_id`, `created_at`) VALUES
(1, 'Primary 4', 'A', NULL, '2026-01-21 10:05:01'),
(2, 'Primary 4', 'B', NULL, '2026-01-21 10:05:01'),
(3, 'Primary 5', 'A', NULL, '2026-01-21 10:05:01'),
(4, 'Primary 5', 'B', NULL, '2026-01-21 10:05:01'),
(5, 'JSS 1', 'A', NULL, '2026-01-21 10:05:01'),
(6, 'JSS 1', 'B', NULL, '2026-01-21 10:05:01'),
(7, 'JSS 2', 'A', NULL, '2026-01-21 10:05:01'),
(8, 'JSS 2', 'B', NULL, '2026-01-21 10:05:01'),
(9, 'JSS 3', 'A', NULL, '2026-01-21 10:05:01'),
(10, 'JSS 3', 'B', NULL, '2026-01-21 10:05:01'),
(11, 'SSS 1', 'Science', NULL, '2026-01-21 10:05:01'),
(12, 'SSS 1', 'Arts', NULL, '2026-01-21 10:05:01'),
(13, 'SSS 2', 'Science', NULL, '2026-01-21 10:05:01'),
(14, 'SSS 2', 'Arts', NULL, '2026-01-21 10:05:01'),
(15, 'SSS 3', 'Science', NULL, '2026-01-21 10:05:01'),
(16, 'SSS 3', 'Arts', NULL, '2026-01-21 10:05:01');

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

--
-- Table structure for table `discipline_records`
--

CREATE TABLE `discipline_records` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `student_id` bigint(20) UNSIGNED NOT NULL,
  `incident_date` date NOT NULL,
  `incident_type` varchar(100) DEFAULT NULL,
  `severity` varchar(50) DEFAULT 'minor',
  `description` text DEFAULT NULL,
  `action_taken` text DEFAULT NULL,
  `reported_by` bigint(20) UNSIGNED NOT NULL,
  `parent_notified` tinyint(1) DEFAULT 0,
  `notification_date` date DEFAULT NULL,
  `status` varchar(50) DEFAULT 'pending_action',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `events` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `event_name` varchar(200) NOT NULL,
  `event_description` text DEFAULT NULL,
  `event_type` varchar(50) DEFAULT 'general',
  `event_date` date NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `target_class_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_by` bigint(20) UNSIGNED NOT NULL,
  `is_public` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `events` (`id`, `event_name`, `event_description`, `event_type`, `event_date`, `start_time`, `end_time`, `location`, `target_class_id`, `created_by`, `is_public`, `created_at`) VALUES
(1, 'Annual Sports Day', 'All students participate in various sporting events', 'sports', '2026-02-14', '08:00:00', '15:00:00', 'School Field', NULL, 9, 1, '2026-01-21 10:05:05'),
(2, 'Parent-Teacher Meeting', 'Meeting to discuss student progress and performance', 'meeting', '2026-01-25', '10:00:00', '14:00:00', 'School Hall', 1, 9, 0, '2026-01-21 10:05:05'),
(3, 'Mid-Year Examinations', 'Written examinations for all subjects', 'exam', '2026-01-20', '09:00:00', '12:00:00', 'Various Halls', NULL, 9, 1, '2026-01-21 10:05:05'),
(4, 'Independence Day Celebration', 'Cultural event celebrating Nigeria', 'cultural', '2026-02-07', '09:00:00', '12:00:00', 'School Premises', NULL, 9, 1, '2026-01-21 10:05:05'),
(5, 'School Holiday - Easter', 'School closed for Easter holidays', 'holiday', '2026-04-15', NULL, NULL, NULL, NULL, 9, 1, '2026-01-21 10:05:05'),
(6, 'Science Fair 2026', 'Students showcase science projects and innovations', 'science', '2026-03-10', '09:00:00', '16:00:00', 'Science Block', NULL, 9, 1, '2026-01-21 10:05:05');

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

--
-- Table structure for table `extracurricular_activities`
--

CREATE TABLE `extracurricular_activities` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `activity_name` varchar(100) NOT NULL,
  `activity_type` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `meeting_day` varchar(20) DEFAULT NULL,
  `meeting_time` time DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `coordinator_id` bigint(20) UNSIGNED NOT NULL,
  `session_year` varchar(20) DEFAULT '2025/2026',
  `is_active` tinyint(1) DEFAULT 1,
  `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;

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

--
-- Table structure for table `fees`
--

CREATE TABLE `fees` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `student_id` bigint(20) UNSIGNED NOT NULL,
  `session_year` varchar(20) NOT NULL,
  `term` enum('1st','2nd','3rd') DEFAULT NULL,
  `fee_type` varchar(100) NOT NULL,
  `amount_due` decimal(12,2) NOT NULL,
  `amount_paid` decimal(12,2) DEFAULT 0.00,
  `balance` decimal(12,2) GENERATED ALWAYS AS (`amount_due` - `amount_paid`) STORED,
  `due_date` date DEFAULT NULL,
  `payment_date` date DEFAULT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `receipt_number` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `fees`
--

INSERT INTO `fees` (`id`, `student_id`, `session_year`, `term`, `fee_type`, `amount_due`, `amount_paid`, `due_date`, `payment_date`, `payment_method`, `receipt_number`, `created_at`) VALUES
(1, 1, '2025/2026', '1st', 'Tuition', 50000.00, 50000.00, '2025-12-31', '2025-12-28', 'Bank Transfer', 'REC/001/2025', '2026-01-21 10:05:02'),
(2, 1, '2025/2026', '1st', 'PTA', 10000.00, 10000.00, '2025-12-31', '2025-12-28', 'Bank Transfer', 'REC/002/2025', '2026-01-21 10:05:02'),
(3, 1, '2025/2026', '1st', 'Uniform', 15000.00, 15000.00, '2025-12-31', '2025-12-20', 'Cash', 'REC/003/2025', '2026-01-21 10:05:02'),
(4, 1, '2025/2026', '1st', 'Books & Materials', 8000.00, 0.00, '2025-12-31', NULL, NULL, NULL, '2026-01-21 10:05:02'),
(5, 2, '2025/2026', '1st', 'Tuition', 50000.00, 50000.00, '2025-12-31', '2025-12-25', 'Paystack', 'PSK/001/2025', '2026-01-21 10:05:02'),
(6, 2, '2025/2026', '1st', 'PTA', 10000.00, 5000.00, '2025-12-31', '2025-12-30', 'Cash', 'REC/004/2025', '2026-01-21 10:05:02'),
(7, 2, '2025/2026', '1st', 'Uniform', 15000.00, 15000.00, '2025-12-31', '2025-12-18', 'Bank Transfer', 'REC/005/2025', '2026-01-21 10:05:02'),
(8, 2, '2025/2026', '1st', 'Books & Materials', 8000.00, 0.00, '2025-12-31', NULL, NULL, NULL, '2026-01-21 10:05:02'),
(9, 3, '2025/2026', '1st', 'Tuition', 50000.00, 50000.00, '2025-12-31', '2025-12-22', 'Bank Transfer', 'REC/006/2025', '2026-01-21 10:05:02'),
(10, 3, '2025/2026', '1st', 'PTA', 10000.00, 10000.00, '2025-12-31', '2025-12-22', 'Bank Transfer', 'REC/007/2025', '2026-01-21 10:05:02'),
(11, 3, '2025/2026', '1st', 'Uniform', 15000.00, 15000.00, '2025-12-31', '2025-12-22', 'Bank Transfer', 'REC/008/2025', '2026-01-21 10:05:02'),
(12, 3, '2025/2026', '1st', 'Books & Materials', 8000.00, 8000.00, '2025-12-31', '2025-12-22', 'Bank Transfer', 'REC/009/2025', '2026-01-21 10:05:02'),
(13, 4, '2025/2026', '1st', 'Tuition', 50000.00, 35000.00, '2025-12-31', '2026-01-05', 'Flutterwave', 'FLW/001/2026', '2026-01-21 10:05:02'),
(14, 4, '2025/2026', '1st', 'PTA', 10000.00, 0.00, '2025-12-31', NULL, NULL, NULL, '2026-01-21 10:05:02'),
(15, 4, '2025/2026', '1st', 'Uniform', 15000.00, 15000.00, '2025-12-31', '2025-12-20', 'Cash', 'REC/010/2025', '2026-01-21 10:05:02'),
(16, 4, '2025/2026', '1st', 'Books & Materials', 8000.00, 0.00, '2025-12-31', NULL, NULL, NULL, '2026-01-21 10:05:02');

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

--
-- Table structure for table `library_books`
--

CREATE TABLE `library_books` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `book_title` varchar(255) NOT NULL,
  `book_author` varchar(150) DEFAULT NULL,
  `isbn` varchar(20) DEFAULT NULL,
  `book_code` varchar(50) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `publication_year` int(11) DEFAULT NULL,
  `publisher` varchar(150) DEFAULT NULL,
  `total_copies` int(11) DEFAULT 1,
  `available_copies` int(11) DEFAULT 1,
  `book_description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `library_borrowing`
--

CREATE TABLE `library_borrowing` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `student_id` bigint(20) UNSIGNED NOT NULL,
  `book_id` bigint(20) UNSIGNED NOT NULL,
  `borrow_date` date NOT NULL,
  `due_date` date NOT NULL,
  `return_date` date DEFAULT NULL,
  `status` varchar(50) DEFAULT 'borrowed',
  `fine_amount` decimal(10,2) DEFAULT 0.00,
  `fine_paid` tinyint(1) DEFAULT 0,
  `librarian_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `messages` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `sender_id` bigint(20) UNSIGNED NOT NULL,
  `receiver_id` bigint(20) UNSIGNED NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `messages` (`id`, `sender_id`, `receiver_id`, `message`, `is_read`, `created_at`) VALUES
(1, 6, 1, 'Good morning Mr. Adeyemi, Tunde performed very well in the recent Mathematics test. Well done!', 1, '2026-01-21 10:05:03'),
(2, 6, 1, 'We will be having a parent-teacher meeting next week. Please make yourself available.', 1, '2026-01-21 10:05:03'),
(3, 6, 2, 'Hello Mrs. Obi, I noticed Folake seems distracted in class lately. Can we discuss this?', 0, '2026-01-21 10:05:03'),
(4, 7, 3, 'Good day Mr. Eze, Chinendu has been actively participating in class discussions. Excellent!', 1, '2026-01-21 10:05:03'),
(5, 7, 4, 'Hi Mr. David, Just wanted to update you that Zainab excels in English. Keep encouraging her!', 0, '2026-01-21 10:05:03'),
(6, 1, 6, 'Thank you for the update on Tunde. We are very proud of his progress.', 1, '2026-01-21 10:05:03'),
(7, 2, 7, 'Good evening Miss Ibrahim, thanks for monitoring Folake. How can we help at home?', 1, '2026-01-21 10:05:03'),
(8, 3, 6, 'Mr. Hassan, could you suggest some additional exercises for Chinendu?', 0, '2026-01-21 10:05:03'),
(9, 4, 7, 'Thank you for the good news about Zainab. We will continue to support her.', 1, '2026-01-21 10:05:03'),
(10, 9, 1, 'Good morning Mr. Adeyemi, reminder: School fees are due by end of this month.', 1, '2026-01-21 10:05:03'),
(11, 9, 2, 'Dear Mrs. Obi, your daughter will need a new uniform for the new term. Please order now.', 1, '2026-01-21 10:05:03');

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

--
-- Table structure for table `parent_student`
--

CREATE TABLE `parent_student` (
  `parent_id` bigint(20) UNSIGNED NOT NULL,
  `student_id` bigint(20) UNSIGNED NOT NULL,
  `relationship` varchar(50) DEFAULT 'Parent',
  `is_primary` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `parent_student`
--

INSERT INTO `parent_student` (`parent_id`, `student_id`, `relationship`, `is_primary`, `created_at`) VALUES
(1, 1, 'Parent', 1, '2026-01-21 10:05:02'),
(1, 5, 'Guardian', 0, '2026-01-21 10:05:02'),
(2, 2, 'Parent', 1, '2026-01-21 10:05:02'),
(2, 6, 'Guardian', 0, '2026-01-21 10:05:02'),
(3, 3, 'Parent', 1, '2026-01-21 10:05:02'),
(4, 4, 'Parent', 1, '2026-01-21 10:05:02'),
(5, 5, 'Parent', 1, '2026-01-21 10:05:02'),
(6, 7, 'Parent', 1, '2026-01-21 10:05:02'),
(7, 8, 'Parent', 1, '2026-01-21 10:05:02');

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

--
-- Table structure for table `report_card`
--

CREATE TABLE `report_card` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `student_id` bigint(20) UNSIGNED NOT NULL,
  `session_year` varchar(20) NOT NULL,
  `term` varchar(20) NOT NULL,
  `overall_gpa` decimal(3,2) DEFAULT 0.00,
  `class_position` int(11) DEFAULT NULL,
  `total_students_in_class` int(11) DEFAULT NULL,
  `conduct_grade` varchar(5) DEFAULT NULL,
  `attendance_percentage` decimal(5,2) DEFAULT 0.00,
  `class_teacher_comment` text DEFAULT NULL,
  `principal_comment` text DEFAULT NULL,
  `promotion_status` varchar(50) DEFAULT 'promoted',
  `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;

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

--
-- Table structure for table `results`
--

CREATE TABLE `results` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `student_id` bigint(20) UNSIGNED NOT NULL,
  `session_year` varchar(20) NOT NULL,
  `term` enum('1st','2nd','3rd') NOT NULL,
  `subject` varchar(100) NOT NULL,
  `ca1` decimal(5,2) DEFAULT 0.00,
  `ca2` decimal(5,2) DEFAULT 0.00,
  `exam` decimal(5,2) DEFAULT 0.00,
  `total` decimal(5,2) GENERATED ALWAYS AS (`ca1` + `ca2` + `exam`) STORED,
  `grade` char(2) DEFAULT NULL,
  `position_in_class` int(11) DEFAULT NULL,
  `teacher_remark` text DEFAULT NULL,
  `principal_remark` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `results`
--

INSERT INTO `results` (`id`, `student_id`, `session_year`, `term`, `subject`, `ca1`, `ca2`, `exam`, `grade`, `position_in_class`, `teacher_remark`, `principal_remark`, `created_at`) VALUES
(1, 1, '2025/2026', '1st', 'Mathematics', 8.00, 9.00, 35.00, 'A', 2, 'Excellent performance', 'Well done', '2026-01-21 10:05:02'),
(2, 1, '2025/2026', '1st', 'English', 7.00, 8.00, 32.00, 'A', 3, 'Good improvement', 'Keep it up', '2026-01-21 10:05:02'),
(3, 1, '2025/2026', '1st', 'Science', 9.00, 8.00, 34.00, 'A', 1, 'Outstanding', 'Excellent', '2026-01-21 10:05:02'),
(4, 1, '2025/2026', '1st', 'Social Studies', 7.00, 7.00, 30.00, 'B', 4, 'Satisfactory', 'Good effort', '2026-01-21 10:05:02'),
(5, 2, '2025/2026', '1st', 'Mathematics', 6.00, 7.00, 28.00, 'B', 5, 'Average performance', 'Try harder', '2026-01-21 10:05:02'),
(6, 2, '2025/2026', '1st', 'English', 8.00, 8.00, 36.00, 'A', 1, 'Excellent', 'Very good', '2026-01-21 10:05:02'),
(7, 2, '2025/2026', '1st', 'Science', 7.00, 6.00, 30.00, 'B', 3, 'Good attempt', 'Satisfactory', '2026-01-21 10:05:02'),
(8, 2, '2025/2026', '1st', 'Social Studies', 8.00, 8.00, 33.00, 'A', 2, 'Good work', 'Well done', '2026-01-21 10:05:02'),
(9, 3, '2025/2026', '1st', 'Mathematics', 9.00, 9.00, 38.00, 'A', 1, 'Outstanding', 'Excellent', '2026-01-21 10:05:02'),
(10, 3, '2025/2026', '1st', 'English', 7.00, 7.00, 31.00, 'B', 4, 'Satisfactory', 'Good effort', '2026-01-21 10:05:02'),
(11, 3, '2025/2026', '1st', 'Science', 8.00, 8.00, 35.00, 'A', 2, 'Very good', 'Commendable', '2026-01-21 10:05:02'),
(12, 3, '2025/2026', '1st', 'Social Studies', 6.00, 7.00, 29.00, 'B', 5, 'Acceptable', 'Try harder', '2026-01-21 10:05:02'),
(13, 4, '2025/2026', '1st', 'Mathematics', 7.00, 8.00, 33.00, 'A', 3, 'Good performance', 'Well done', '2026-01-21 10:05:02'),
(14, 4, '2025/2026', '1st', 'English', 9.00, 8.00, 37.00, 'A', 1, 'Excellent', 'Outstanding', '2026-01-21 10:05:02'),
(15, 4, '2025/2026', '1st', 'Science', 6.00, 7.00, 29.00, 'B', 4, 'Average', 'Acceptable', '2026-01-21 10:05:02'),
(16, 4, '2025/2026', '1st', 'Social Studies', 7.00, 8.00, 32.00, 'A', 2, 'Good work', 'Commendable', '2026-01-21 10:05:02');

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

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

CREATE TABLE `students` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `admission_number` varchar(30) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `class_id` bigint(20) UNSIGNED NOT NULL,
  `section` varchar(20) DEFAULT NULL,
  `gender` enum('M','F','Other') DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `photo_url` varchar(255) DEFAULT NULL,
  `blood_group` varchar(5) DEFAULT NULL,
  `allergies` text DEFAULT NULL,
  `emergency_contact_name` varchar(100) DEFAULT NULL,
  `emergency_contact_phone` varchar(20) 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_unicode_ci;

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `admission_number`, `full_name`, `class_id`, `section`, `gender`, `date_of_birth`, `photo_url`, `blood_group`, `allergies`, `emergency_contact_name`, `emergency_contact_phone`, `created_at`, `updated_at`) VALUES
(1, 'ADM/2025/001', 'Tunde Adeyemis', 1, 'A', 'M', '2015-05-12', NULL, 'O+', NULL, 'James Adeyemi', '08031234567', '2026-01-21 10:05:02', '2026-01-21 10:11:56'),
(2, 'ADM/2025/002', 'Folake Obi', 2, 'B', 'F', '2014-08-23', NULL, 'A+', NULL, 'Margaret Obi', '08112345678', '2026-01-21 10:05:02', '2026-01-21 10:05:02'),
(3, 'ADM/2025/003', 'Chinendu Eze', 3, 'A', 'M', '2013-11-15', NULL, 'B+', NULL, 'Chioma Eze', '08023456789', '2026-01-21 10:05:02', '2026-01-21 10:05:02'),
(4, 'ADM/2025/004', 'Zainab David', 4, 'B', 'F', '2013-03-07', NULL, 'O-', NULL, 'John David', '08034567890', '2026-01-21 10:05:02', '2026-01-21 10:05:02'),
(5, 'ADM/2025/005', 'Oluwaseun Williams', 5, 'A', 'M', '2012-12-20', NULL, 'AB+', NULL, 'Grace Williams', '08145678901', '2026-01-21 10:05:02', '2026-01-21 10:05:02'),
(6, 'ADM/2025/006', 'Funmilayo Hassan', 6, 'B', 'F', '2012-06-14', NULL, 'A+', NULL, 'Mr. Aminu Hassan', '08015551234', '2026-01-21 10:05:02', '2026-01-21 10:05:02'),
(7, 'ADM/2025/007', 'Ibrahim Ibrahim', 7, 'A', 'M', '2011-09-10', NULL, 'O+', NULL, 'Miss Fatima Ibrahim', '08015552345', '2026-01-21 10:05:02', '2026-01-21 10:05:02'),
(8, 'ADM/2025/008', 'Aisha Musa', 8, 'B', 'F', '2011-02-18', NULL, 'B-', NULL, 'Mrs. Zainab Musa', '08015553456', '2026-01-21 10:05:02', '2026-01-21 10:05:02');

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

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

CREATE TABLE `subjects` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `class_id` bigint(20) UNSIGNED NOT NULL,
  `subject_name` varchar(100) NOT NULL,
  `subject_code` varchar(20) DEFAULT NULL,
  `teacher_id` bigint(20) UNSIGNED DEFAULT NULL,
  `credit_hours` decimal(3,1) DEFAULT 1.0,
  `is_compulsory` tinyint(1) DEFAULT 1,
  `description` text 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_unicode_ci;

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

INSERT INTO `subjects` (`id`, `class_id`, `subject_name`, `subject_code`, `teacher_id`, `credit_hours`, `is_compulsory`, `description`, `created_at`, `updated_at`) VALUES
(1, 1, 'Mathematics', 'MATH101', 6, 3.0, 1, NULL, '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(2, 1, 'English Language', 'ENG101', 7, 3.0, 1, NULL, '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(3, 1, 'Science', 'SCI101', 6, 3.0, 1, NULL, '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(4, 1, 'Social Studies', 'SS101', 7, 3.0, 1, NULL, '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(5, 1, 'Physical Education', 'PE101', 6, 2.0, 1, NULL, '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(6, 2, 'Mathematics', 'MATH102', 7, 3.0, 1, NULL, '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(7, 2, 'English Language', 'ENG102', 6, 3.0, 1, NULL, '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(8, 2, 'Science', 'SCI102', 7, 3.0, 1, NULL, '2026-01-21 10:05:03', '2026-01-21 10:05:03');

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

--
-- Table structure for table `timetable`
--

CREATE TABLE `timetable` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `class_id` bigint(20) UNSIGNED NOT NULL,
  `day_of_week` varchar(20) NOT NULL,
  `subject_id` bigint(20) UNSIGNED NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `teacher_id` bigint(20) UNSIGNED DEFAULT NULL,
  `room_number` varchar(20) DEFAULT NULL,
  `session_year` varchar(20) DEFAULT '2025/2026',
  `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 `timetable`
--

INSERT INTO `timetable` (`id`, `class_id`, `day_of_week`, `subject_id`, `start_time`, `end_time`, `teacher_id`, `room_number`, `session_year`, `created_at`, `updated_at`) VALUES
(1, 1, 'Monday', 1, '08:00:00', '09:00:00', 6, '101', '2025/2026', '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(2, 1, 'Monday', 2, '09:00:00', '10:00:00', 7, '101', '2025/2026', '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(3, 1, 'Tuesday', 3, '08:00:00', '09:00:00', 6, '102', '2025/2026', '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(4, 1, 'Tuesday', 4, '09:00:00', '10:00:00', 7, '101', '2025/2026', '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(5, 1, 'Wednesday', 1, '08:00:00', '09:00:00', 6, '101', '2025/2026', '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(6, 1, 'Wednesday', 5, '10:00:00', '11:00:00', 6, 'Field', '2025/2026', '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(7, 1, 'Thursday', 2, '08:00:00', '09:00:00', 7, '101', '2025/2026', '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(8, 1, 'Thursday', 3, '09:00:00', '10:00:00', 6, '102', '2025/2026', '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(9, 1, 'Friday', 4, '08:00:00', '09:00:00', 7, '101', '2025/2026', '2026-01-21 10:05:03', '2026-01-21 10:05:03'),
(10, 1, 'Friday', 1, '09:00:00', '10:00:00', 6, '101', '2025/2026', '2026-01-21 10:05:03', '2026-01-21 10:05:03');

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

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

CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `password_hash` varchar(2550) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `role` enum('parent','guardian','teacher','admin') NOT NULL DEFAULT 'parent',
  `otp_code` varchar(6) DEFAULT NULL,
  `otp_expires_at` datetime DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `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 `users`
--

INSERT INTO `users` (`id`, `email`, `phone`, `password_hash`, `full_name`, `role`, `otp_code`, `otp_expires_at`, `last_login`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'james.adeyemi@email.com', '08031234567', '$2y$10$QxA5MZvVemZUkbAhcGtuw.yDkqXSpFNicI15EVwfumOuPLWKBNzoi', 'James Adeyemi', 'parent', NULL, NULL, '2026-01-21 14:34:30', 1, '2026-01-21 10:05:01', '2026-01-21 13:34:30'),
(2, 'margaret.obi@email.com', '08112345678', '$2y$10$QxA5MZvVemZUkbAhcGtuw.yDkqXSpFNicI15EVwfumOuPLWKBNzoi', 'Margaret Obi', 'parent', NULL, NULL, NULL, 1, '2026-01-21 10:05:01', '2026-01-21 10:09:30'),
(3, 'chioma.eze@email.com', '08023456789', '$2y$10$slYQmyNdGzin7olVCafCOuYvFG7DJn0jZL8/LewKpS8gbH/eUciAa', 'Chioma Eze', 'parent', NULL, NULL, NULL, 1, '2026-01-21 10:05:01', '2026-01-21 10:05:01'),
(4, 'john.david@email.com', '08034567890', '$2y$10$slYQmyNdGzin7olVCafCOuYvFG7DJn0jZL8/LewKpS8gbH/eUciAa', 'John David', 'parent', NULL, NULL, NULL, 1, '2026-01-21 10:05:01', '2026-01-21 10:05:01'),
(5, 'grace.williams@email.com', '08145678901', '$2y$10$slYQmyNdGzin7olVCafCOuYvFG7DJn0jZL8/LewKpS8gbH/eUciAa', 'Grace Williams', 'parent', NULL, NULL, NULL, 1, '2026-01-21 10:05:01', '2026-01-21 10:05:01'),
(6, 'mr.aminu@school.com', '08015551234', '$2y$10$slYQmyNdGzin7olVCafCOuYvFG7DJn0jZL8/LewKpS8gbH/eUciAa', 'Mr. Aminu Hassan', 'teacher', NULL, NULL, NULL, 1, '2026-01-21 10:05:01', '2026-01-21 10:05:01'),
(7, 'miss.fatima@school.com', '08015552345', '$2y$10$slYQmyNdGzin7olVCafCOuYvFG7DJn0jZL8/LewKpS8gbH/eUciAa', 'Miss Fatima Ibrahim', 'teacher', NULL, NULL, NULL, 1, '2026-01-21 10:05:01', '2026-01-21 10:05:01'),
(8, 'mrs.zainab@school.com', '08015553456', '$2y$10$slYQmyNdGzin7olVCafCOuYvFG7DJn0jZL8/LewKpS8gbH/eUciAa', 'Mrs. Zainab Musa', 'teacher', NULL, NULL, NULL, 1, '2026-01-21 10:05:01', '2026-01-21 10:05:01'),
(9, 'admin@school.com', '08015554567', '$2y$10$slYQmyNdGzin7olVCafCOuYvFG7DJn0jZL8/LewKpS8gbH/eUciAa', 'School Admin', 'admin', NULL, NULL, NULL, 1, '2026-01-21 10:05:01', '2026-01-21 10:05:01');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `activity_participation`
--
ALTER TABLE `activity_participation`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_activity` (`activity_id`),
  ADD KEY `idx_participation_student` (`student_id`);

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

--
-- Indexes for table `assignments`
--
ALTER TABLE `assignments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `subject_id` (`subject_id`),
  ADD KEY `teacher_id` (`teacher_id`),
  ADD KEY `idx_status` (`assigned_date`),
  ADD KEY `idx_assignments_class` (`class_id`),
  ADD KEY `idx_assignments_due` (`due_date`);

--
-- Indexes for table `assignment_submissions`
--
ALTER TABLE `assignment_submissions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `assignment_id` (`assignment_id`),
  ADD KEY `graded_by` (`graded_by`),
  ADD KEY `idx_submission_date` (`submission_date`),
  ADD KEY `idx_submissions_student` (`student_id`),
  ADD KEY `idx_submissions_date` (`submission_date`);

--
-- Indexes for table `attendance`
--
ALTER TABLE `attendance`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_student_date` (`student_id`,`attendance_date`);

--
-- Indexes for table `classes`
--
ALTER TABLE `classes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_class_section` (`class_name`,`section`);

--
-- Indexes for table `discipline_records`
--
ALTER TABLE `discipline_records`
  ADD PRIMARY KEY (`id`),
  ADD KEY `reported_by` (`reported_by`),
  ADD KEY `idx_incident_date` (`incident_date`),
  ADD KEY `idx_discipline_student` (`student_id`);

--
-- Indexes for table `events`
--
ALTER TABLE `events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `target_class_id` (`target_class_id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_event_type` (`event_type`),
  ADD KEY `idx_events_date` (`event_date`),
  ADD KEY `idx_events_type` (`event_type`);

--
-- Indexes for table `extracurricular_activities`
--
ALTER TABLE `extracurricular_activities`
  ADD PRIMARY KEY (`id`),
  ADD KEY `coordinator_id` (`coordinator_id`),
  ADD KEY `idx_is_active` (`is_active`),
  ADD KEY `idx_activities_type` (`activity_type`);

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

--
-- Indexes for table `library_books`
--
ALTER TABLE `library_books`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `book_code` (`book_code`),
  ADD UNIQUE KEY `isbn` (`isbn`),
  ADD KEY `idx_category` (`category`),
  ADD KEY `idx_library_books_title` (`book_title`);

--
-- Indexes for table `library_borrowing`
--
ALTER TABLE `library_borrowing`
  ADD PRIMARY KEY (`id`),
  ADD KEY `book_id` (`book_id`),
  ADD KEY `librarian_id` (`librarian_id`),
  ADD KEY `idx_due_date` (`due_date`),
  ADD KEY `idx_borrowing_student` (`student_id`),
  ADD KEY `idx_borrowing_status` (`status`);

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

--
-- Indexes for table `parent_student`
--
ALTER TABLE `parent_student`
  ADD PRIMARY KEY (`parent_id`,`student_id`),
  ADD KEY `student_id` (`student_id`);

--
-- Indexes for table `report_card`
--
ALTER TABLE `report_card`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_term` (`term`),
  ADD KEY `idx_report_card_student` (`student_id`);

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

--
-- Indexes for table `students`
--
ALTER TABLE `students`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `admission_number` (`admission_number`);

--
-- Indexes for table `subjects`
--
ALTER TABLE `subjects`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `subject_code` (`subject_code`),
  ADD KEY `teacher_id` (`teacher_id`),
  ADD KEY `idx_class` (`class_id`),
  ADD KEY `idx_subject_name` (`subject_name`),
  ADD KEY `idx_subjects_class` (`class_id`);

--
-- Indexes for table `timetable`
--
ALTER TABLE `timetable`
  ADD PRIMARY KEY (`id`),
  ADD KEY `subject_id` (`subject_id`),
  ADD KEY `teacher_id` (`teacher_id`),
  ADD KEY `idx_class` (`class_id`),
  ADD KEY `idx_timetable_class` (`class_id`),
  ADD KEY `idx_timetable_day` (`day_of_week`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `phone` (`phone`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `activity_participation`
--
ALTER TABLE `activity_participation`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `announcements`
--
ALTER TABLE `announcements`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `assignments`
--
ALTER TABLE `assignments`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `assignment_submissions`
--
ALTER TABLE `assignment_submissions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `attendance`
--
ALTER TABLE `attendance`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

--
-- AUTO_INCREMENT for table `classes`
--
ALTER TABLE `classes`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `discipline_records`
--
ALTER TABLE `discipline_records`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `events`
--
ALTER TABLE `events`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `extracurricular_activities`
--
ALTER TABLE `extracurricular_activities`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fees`
--
ALTER TABLE `fees`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `library_books`
--
ALTER TABLE `library_books`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `library_borrowing`
--
ALTER TABLE `library_borrowing`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `messages`
--
ALTER TABLE `messages`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `report_card`
--
ALTER TABLE `report_card`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `results`
--
ALTER TABLE `results`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `students`
--
ALTER TABLE `students`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `subjects`
--
ALTER TABLE `subjects`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `timetable`
--
ALTER TABLE `timetable`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `activity_participation`
--
ALTER TABLE `activity_participation`
  ADD CONSTRAINT `activity_participation_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `activity_participation_ibfk_2` FOREIGN KEY (`activity_id`) REFERENCES `extracurricular_activities` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `announcements`
--
ALTER TABLE `announcements`
  ADD CONSTRAINT `announcements_ibfk_1` FOREIGN KEY (`target_class_id`) REFERENCES `classes` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `announcements_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `assignments`
--
ALTER TABLE `assignments`
  ADD CONSTRAINT `assignments_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `assignments_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `assignments_ibfk_3` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

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

--
-- Constraints for table `attendance`
--
ALTER TABLE `attendance`
  ADD CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `discipline_records`
--
ALTER TABLE `discipline_records`
  ADD CONSTRAINT `discipline_records_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `discipline_records_ibfk_2` FOREIGN KEY (`reported_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `events`
--
ALTER TABLE `events`
  ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`target_class_id`) REFERENCES `classes` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `events_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `extracurricular_activities`
--
ALTER TABLE `extracurricular_activities`
  ADD CONSTRAINT `extracurricular_activities_ibfk_1` FOREIGN KEY (`coordinator_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `fees`
--
ALTER TABLE `fees`
  ADD CONSTRAINT `fees_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `library_borrowing`
--
ALTER TABLE `library_borrowing`
  ADD CONSTRAINT `library_borrowing_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `library_borrowing_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `library_books` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `library_borrowing_ibfk_3` FOREIGN KEY (`librarian_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `messages`
--
ALTER TABLE `messages`
  ADD CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `parent_student`
--
ALTER TABLE `parent_student`
  ADD CONSTRAINT `parent_student_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `parent_student_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `report_card`
--
ALTER TABLE `report_card`
  ADD CONSTRAINT `report_card_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `results`
--
ALTER TABLE `results`
  ADD CONSTRAINT `results_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `subjects`
--
ALTER TABLE `subjects`
  ADD CONSTRAINT `subjects_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `subjects_ibfk_2` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `timetable`
--
ALTER TABLE `timetable`
  ADD CONSTRAINT `timetable_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `timetable_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `timetable_ibfk_3` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;
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 */;