-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jul 22, 2026 at 02:01 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: `mavela_db`
--

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

--
-- Table structure for table `admissions_pending`
--

CREATE TABLE `admissions_pending` (
  `id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `date_of_admission` date DEFAULT NULL,
  `surname` varchar(100) NOT NULL,
  `christian_names` varchar(150) NOT NULL,
  `full_name` varchar(150) NOT NULL,
  `date_of_birth_info` varchar(40) DEFAULT NULL,
  `birth_certificate_number` varchar(80) DEFAULT NULL,
  `religious_denomination` varchar(120) DEFAULT NULL,
  `gender` enum('Male','Female','Other') NOT NULL DEFAULT 'Other',
  `guardian_name` varchar(120) DEFAULT NULL,
  `guardian_national_id` varchar(60) DEFAULT NULL,
  `guardian_address` varchar(255) DEFAULT NULL,
  `guardian_phone` varchar(40) DEFAULT NULL,
  `student_photo_path` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `review_comment` varchar(255) 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` int(11) NOT NULL,
  `sender_id` int(11) NOT NULL,
  `sender_role` enum('admin','teacher') NOT NULL,
  `audience` enum('teacher','learner','parent') NOT NULL,
  `target_class_id` int(11) DEFAULT NULL,
  `target_learner_id` int(11) DEFAULT NULL,
  `title` varchar(180) NOT NULL,
  `body` text NOT NULL,
  `status` enum('published','pending_admin_review','rejected') NOT NULL DEFAULT 'published',
  `admin_review_comment` text DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `published_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `app_settings`
--

CREATE TABLE `app_settings` (
  `setting_key` varchar(120) NOT NULL,
  `setting_value` text NOT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `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 `app_settings`
--

INSERT INTO `app_settings` (`setting_key`, `setting_value`, `updated_by`, `updated_at`) VALUES
('palette_bg_cream', '#f6efe0', 1, '2026-07-22 11:56:12'),
('palette_bg_sand', '#eadcc0', 1, '2026-07-22 11:56:12'),
('palette_brand_gold', '#f8b400', 1, '2026-07-22 11:56:12'),
('palette_brand_gold_soft', '#d9c39a', 1, '2026-07-22 11:56:12'),
('palette_brand_ice', '#e8eef8', 1, '2026-07-22 11:56:12'),
('palette_brand_navy', '#0a3b79', 1, '2026-07-22 11:56:12'),
('palette_brand_navy_deep', '#082e60', 1, '2026-07-22 11:56:12'),
('palette_ink', '#0a2f63', 1, '2026-07-22 11:56:12'),
('palette_muted', '#4f5f7a', 1, '2026-07-22 11:56:12'),
('palette_patch_sky', '#7cc9ff', 1, '2026-07-22 11:56:12'),
('palette_patch_sky_soft', '#dff2ff', 1, '2026-07-22 11:56:12'),
('palette_patch_yellow', '#ffd447', 1, '2026-07-22 11:56:12'),
('palette_patch_yellow_soft', '#fff4be', 1, '2026-07-22 11:56:12'),
('school_address', 'P.O.BOX 20 TSHOLOTSHO', 1, '2026-07-22 11:56:12'),
('school_contact', 'PHONE: +263 292861301 | INFO@MAVELAHIGH.ORG', 1, '2026-07-22 11:56:12'),
('school_logo_path', 'Uploads/Mavela_Logo.png', NULL, '2026-07-22 11:17:57'),
('school_motto', 'EDUCTATION FOR SEL RELIANCE', 1, '2026-07-22 11:56:12'),
('school_name', 'MAVELA GOVERNMENT HIGH SCHOOL', 1, '2026-07-22 11:56:12'),
('school_theme_preset', 'ocean', 1, '2026-07-22 11:56:12');

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

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

CREATE TABLE `classes` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT 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`, `name`, `created_at`) VALUES
(10, '1North', '2026-07-21 14:58:54'),
(11, '2North', '2026-07-21 14:59:08'),
(12, '3North', '2026-07-21 14:59:14'),
(13, '4north', '2026-07-21 14:59:22'),
(14, 'L6', '2026-07-21 14:59:28'),
(15, 'U6', '2026-07-21 14:59:35');

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

--
-- Table structure for table `class_teacher_assignments`
--

CREATE TABLE `class_teacher_assignments` (
  `id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `assigned_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `class_teacher_assignments`
--

INSERT INTO `class_teacher_assignments` (`id`, `class_id`, `teacher_id`, `assigned_by`, `created_at`, `updated_at`) VALUES
(1, 10, 5, 1, '2026-07-21 19:46:54', NULL);

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

--
-- Table structure for table `currency_rates`
--

CREATE TABLE `currency_rates` (
  `currency_code` varchar(10) NOT NULL,
  `base_currency` varchar(10) NOT NULL DEFAULT 'USD',
  `rate_to_base` decimal(18,8) NOT NULL,
  `source` varchar(80) DEFAULT NULL,
  `fetched_at` datetime NOT NULL,
  `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 `currency_rates`
--

INSERT INTO `currency_rates` (`currency_code`, `base_currency`, `rate_to_base`, `source`, `fetched_at`, `updated_at`) VALUES
('RAND', 'USD', 0.06074808, 'open.er-api.com', '2026-07-22 13:57:30', '2026-07-22 11:57:30'),
('USD', 'USD', 1.00000000, 'open.er-api.com', '2026-07-22 13:57:30', '2026-07-22 11:57:30'),
('ZIG', 'USD', 0.03731872, 'open.er-api.com', '2026-07-22 13:57:30', '2026-07-22 11:57:30');

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

--
-- Table structure for table `daily_attendance_register`
--

CREATE TABLE `daily_attendance_register` (
  `id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `learner_id` int(11) NOT NULL,
  `attendance_date` date NOT NULL,
  `status` enum('present','absent','late','excused') NOT NULL DEFAULT 'present',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `elearning_posts`
--

CREATE TABLE `elearning_posts` (
  `id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `content_type` enum('assignment','exercise','note','video') NOT NULL DEFAULT 'assignment',
  `title` varchar(180) NOT NULL,
  `instructions` text DEFAULT NULL,
  `resource_url` varchar(255) DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `elearning_submissions`
--

CREATE TABLE `elearning_submissions` (
  `id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `learner_id` int(11) NOT NULL,
  `response_text` text DEFAULT NULL,
  `response_link` varchar(255) DEFAULT NULL,
  `attachment_name` varchar(255) DEFAULT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `attachment_type` varchar(120) DEFAULT NULL,
  `attachment_size` int(11) DEFAULT NULL,
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `status` enum('submitted','graded','returned') NOT NULL DEFAULT 'submitted',
  `score` decimal(6,2) DEFAULT NULL,
  `teacher_comment` text DEFAULT NULL,
  `graded_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `fee_invoices`
--

CREATE TABLE `fee_invoices` (
  `id` int(11) NOT NULL,
  `invoice_number` varchar(40) NOT NULL,
  `learner_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  `amount_due` decimal(10,2) NOT NULL,
  `due_date` date DEFAULT NULL,
  `status` enum('unpaid','partially_paid','paid') NOT NULL DEFAULT 'unpaid',
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `fee_gpf` decimal(10,2) NOT NULL DEFAULT 0.00,
  `fee_ssf` decimal(10,2) NOT NULL DEFAULT 0.00,
  `fee_sdc` decimal(10,2) NOT NULL DEFAULT 0.00,
  `fee_registration` decimal(10,2) NOT NULL DEFAULT 0.00,
  `fee_zimsec` decimal(10,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `fee_payments`
--

CREATE TABLE `fee_payments` (
  `id` int(11) NOT NULL,
  `invoice_id` int(11) DEFAULT NULL,
  `learner_id` int(11) NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL,
  `currency_code` enum('USD','ZIG','RAND') NOT NULL DEFAULT 'USD',
  `fx_rate_to_usd` decimal(18,8) NOT NULL DEFAULT 1.00000000,
  `amount_paid_usd` decimal(12,2) NOT NULL DEFAULT 0.00,
  `payment_date` date NOT NULL,
  `payment_method` enum('cash','ecocash','bank_transfer','pos','other') NOT NULL DEFAULT 'cash',
  `reference_no` varchar(80) DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  `received_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `fee_component` varchar(10) NOT NULL DEFAULT 'GPF'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `hop_profiles`
--

CREATE TABLE `hop_profiles` (
  `user_id` int(11) NOT NULL,
  `department_name` varchar(150) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hop_profiles`
--

INSERT INTO `hop_profiles` (`user_id`, `department_name`, `created_at`, `updated_at`) VALUES
(6, 'Science Department', '2026-07-21 19:24:53', NULL);

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

--
-- Table structure for table `inventory_records`
--

CREATE TABLE `inventory_records` (
  `id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `category` enum('textbook','furniture','equipment','other') NOT NULL DEFAULT 'other',
  `item_name` varchar(180) NOT NULL,
  `quantity_recorded` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `item_condition` varchar(80) DEFAULT NULL,
  `inventory_note` text DEFAULT NULL,
  `status` enum('submitted','verified','discrepancy') NOT NULL DEFAULT 'submitted',
  `stock_take_quantity` int(10) UNSIGNED DEFAULT NULL,
  `accountant_review` text DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `learners`
--

CREATE TABLE `learners` (
  `reg_number` varchar(30) NOT NULL,
  `id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `date_of_admission` date DEFAULT NULL,
  `surname` varchar(100) DEFAULT NULL,
  `christian_names` varchar(150) DEFAULT NULL,
  `full_name` varchar(150) NOT NULL,
  `date_of_birth_info` varchar(40) DEFAULT NULL,
  `birth_certificate_number` varchar(80) DEFAULT NULL,
  `religious_denomination` varchar(120) DEFAULT NULL,
  `gender` enum('Male','Female','Other') NOT NULL DEFAULT 'Other',
  `guardian_name` varchar(120) DEFAULT NULL,
  `guardian_national_id` varchar(60) DEFAULT NULL,
  `guardian_address` varchar(255) DEFAULT NULL,
  `guardian_phone` varchar(40) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `assigned_teacher_id` int(11) DEFAULT NULL,
  `is_frozen` tinyint(1) NOT NULL DEFAULT 0,
  `frozen_reason` varchar(255) DEFAULT NULL,
  `frozen_at` datetime DEFAULT NULL,
  `student_photo_path` varchar(255) DEFAULT NULL,
  `contact_email` varchar(150) DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `learners`
--

INSERT INTO `learners` (`reg_number`, `id`, `class_id`, `date_of_admission`, `surname`, `christian_names`, `full_name`, `date_of_birth_info`, `birth_certificate_number`, `religious_denomination`, `gender`, `guardian_name`, `guardian_national_id`, `guardian_address`, `guardian_phone`, `created_at`, `assigned_teacher_id`, `is_frozen`, `frozen_reason`, `frozen_at`, `student_photo_path`, `contact_email`, `password_hash`) VALUES
('MAV26047', 47, 10, '2026-07-21', 'Moyo', 'Tanaka', 'Moyo Tanaka', NULL, NULL, NULL, 'Male', 'Guardian 1', NULL, NULL, '077000201', '2026-07-21 14:59:08', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26048', 48, 10, '2026-07-21', 'Ncube', 'Lerato', 'Ncube Lerato', NULL, NULL, NULL, 'Female', 'Guardian 2', NULL, NULL, '077000202', '2026-07-21 14:59:08', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26049', 49, 10, '2026-07-21', 'Dube', 'Sibusiso', 'Dube Sibusiso', NULL, NULL, NULL, 'Male', 'Guardian 3', NULL, NULL, '077000203', '2026-07-21 14:59:08', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26050', 50, 10, '2026-07-21', 'Sibanda', 'Amanda', 'Sibanda Amanda', NULL, NULL, NULL, 'Female', 'Guardian 4', NULL, NULL, '077000204', '2026-07-21 14:59:08', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26051', 51, 10, '2026-07-21', 'Ndlovu', 'Tariro', 'Ndlovu Tariro', NULL, NULL, NULL, 'Other', 'Guardian 5', NULL, NULL, '077000205', '2026-07-21 14:59:08', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26052', 52, 11, '2026-07-21', 'Moyo', 'Tanaka', 'Moyo Tanaka', NULL, NULL, NULL, 'Male', 'Guardian 1', NULL, NULL, '077000211', '2026-07-21 14:59:14', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26053', 53, 11, '2026-07-21', 'Ncube', 'Lerato', 'Ncube Lerato', NULL, NULL, NULL, 'Female', 'Guardian 2', NULL, NULL, '077000212', '2026-07-21 14:59:14', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26054', 54, 11, '2026-07-21', 'Dube', 'Sibusiso', 'Dube Sibusiso', NULL, NULL, NULL, 'Male', 'Guardian 3', NULL, NULL, '077000213', '2026-07-21 14:59:14', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26055', 55, 11, '2026-07-21', 'Sibanda', 'Amanda', 'Sibanda Amanda', NULL, NULL, NULL, 'Female', 'Guardian 4', NULL, NULL, '077000214', '2026-07-21 14:59:14', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26056', 56, 11, '2026-07-21', 'Ndlovu', 'Tariro', 'Ndlovu Tariro', NULL, NULL, NULL, 'Other', 'Guardian 5', NULL, NULL, '077000215', '2026-07-21 14:59:14', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26057', 57, 12, '2026-07-21', 'Moyo', 'Tanaka', 'Moyo Tanaka', NULL, NULL, NULL, 'Male', 'Guardian 1', NULL, NULL, '077000221', '2026-07-21 14:59:22', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26058', 58, 12, '2026-07-21', 'Ncube', 'Lerato', 'Ncube Lerato', NULL, NULL, NULL, 'Female', 'Guardian 2', NULL, NULL, '077000222', '2026-07-21 14:59:22', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26059', 59, 12, '2026-07-21', 'Dube', 'Sibusiso', 'Dube Sibusiso', NULL, NULL, NULL, 'Male', 'Guardian 3', NULL, NULL, '077000223', '2026-07-21 14:59:22', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26060', 60, 12, '2026-07-21', 'Sibanda', 'Amanda', 'Sibanda Amanda', NULL, NULL, NULL, 'Female', 'Guardian 4', NULL, NULL, '077000224', '2026-07-21 14:59:22', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26061', 61, 12, '2026-07-21', 'Ndlovu', 'Tariro', 'Ndlovu Tariro', NULL, NULL, NULL, 'Other', 'Guardian 5', NULL, NULL, '077000225', '2026-07-21 14:59:22', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26062', 62, 13, '2026-07-21', 'Moyo', 'Tanaka', 'Moyo Tanaka', NULL, NULL, NULL, 'Male', 'Guardian 1', NULL, NULL, '077000231', '2026-07-21 14:59:28', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26063', 63, 13, '2026-07-21', 'Ncube', 'Lerato', 'Ncube Lerato', NULL, NULL, NULL, 'Female', 'Guardian 2', NULL, NULL, '077000232', '2026-07-21 14:59:28', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26064', 64, 13, '2026-07-21', 'Dube', 'Sibusiso', 'Dube Sibusiso', NULL, NULL, NULL, 'Male', 'Guardian 3', NULL, NULL, '077000233', '2026-07-21 14:59:28', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26065', 65, 13, '2026-07-21', 'Sibanda', 'Amanda', 'Sibanda Amanda', NULL, NULL, NULL, 'Female', 'Guardian 4', NULL, NULL, '077000234', '2026-07-21 14:59:28', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26066', 66, 13, '2026-07-21', 'Ndlovu', 'Tariro', 'Ndlovu Tariro', NULL, NULL, NULL, 'Other', 'Guardian 5', NULL, NULL, '077000235', '2026-07-21 14:59:28', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26067', 67, 14, '2026-07-21', 'Moyo', 'Tanaka', 'Moyo Tanaka', NULL, NULL, NULL, 'Male', 'Guardian 1', NULL, NULL, '077000241', '2026-07-21 14:59:35', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26068', 68, 14, '2026-07-21', 'Ncube', 'Lerato', 'Ncube Lerato', NULL, NULL, NULL, 'Female', 'Guardian 2', NULL, NULL, '077000242', '2026-07-21 14:59:35', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26069', 69, 14, '2026-07-21', 'Dube', 'Sibusiso', 'Dube Sibusiso', NULL, NULL, NULL, 'Male', 'Guardian 3', NULL, NULL, '077000243', '2026-07-21 14:59:35', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26070', 70, 14, '2026-07-21', 'Sibanda', 'Amanda', 'Sibanda Amanda', NULL, NULL, NULL, 'Female', 'Guardian 4', NULL, NULL, '077000244', '2026-07-21 14:59:35', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26071', 71, 14, '2026-07-21', 'Ndlovu', 'Tariro', 'Ndlovu Tariro', NULL, NULL, NULL, 'Other', 'Guardian 5', NULL, NULL, '077000245', '2026-07-21 14:59:35', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26072', 72, 15, '2026-07-21', 'Moyo', 'Tanaka', 'Moyo Tanaka', NULL, NULL, NULL, 'Male', 'Guardian 1', NULL, NULL, '077000251', '2026-07-21 15:00:08', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26073', 73, 15, '2026-07-21', 'Ncube', 'Lerato', 'Ncube Lerato', NULL, NULL, NULL, 'Female', 'Guardian 2', NULL, NULL, '077000252', '2026-07-21 15:00:08', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26074', 74, 15, '2026-07-21', 'Dube', 'Sibusiso', 'Dube Sibusiso', NULL, NULL, NULL, 'Male', 'Guardian 3', NULL, NULL, '077000253', '2026-07-21 15:00:08', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26075', 75, 15, '2026-07-21', 'Sibanda', 'Amanda', 'Sibanda Amanda', NULL, NULL, NULL, 'Female', 'Guardian 4', NULL, NULL, '077000254', '2026-07-21 15:00:08', NULL, 0, NULL, NULL, NULL, NULL, NULL),
('MAV26076', 76, 15, '2026-07-21', 'Ndlovu', 'Tariro', 'Ndlovu Tariro', NULL, NULL, NULL, 'Other', 'Guardian 5', NULL, NULL, '077000255', '2026-07-21 15:00:08', NULL, 0, NULL, NULL, NULL, NULL, NULL);

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

--
-- Table structure for table `learner_applications`
--

CREATE TABLE `learner_applications` (
  `id` int(11) NOT NULL,
  `surname` varchar(100) NOT NULL,
  `christian_names` varchar(150) NOT NULL,
  `full_name` varchar(150) NOT NULL,
  `date_of_birth_info` varchar(40) DEFAULT NULL,
  `birth_certificate_number` varchar(80) DEFAULT NULL,
  `religious_denomination` varchar(120) DEFAULT NULL,
  `gender` enum('Male','Female','Other') NOT NULL DEFAULT 'Other',
  `date_of_admission` date DEFAULT NULL,
  `guardian_name` varchar(120) DEFAULT NULL,
  `guardian_national_id` varchar(60) DEFAULT NULL,
  `guardian_address` varchar(255) DEFAULT NULL,
  `guardian_phone` varchar(40) DEFAULT NULL,
  `parent_email` varchar(150) DEFAULT NULL,
  `parent_whatsapp_phone` varchar(40) DEFAULT NULL,
  `preferred_class_id` int(11) NOT NULL,
  `assigned_class_id` int(11) DEFAULT NULL,
  `assigned_teacher_id` int(11) DEFAULT NULL,
  `student_photo_path` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `admin_comment` text DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `offer_letter_html` longtext DEFAULT NULL,
  `offer_sent_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `learner_marks`
--

CREATE TABLE `learner_marks` (
  `id` int(11) NOT NULL,
  `learner_id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `assessment_type` enum('daily_exercise','weekly_test','end_of_month_test','end_of_term_exam','midyear_exam','end_of_year_exam') NOT NULL,
  `term_name` varchar(30) NOT NULL,
  `assessment_date` date NOT NULL,
  `score` decimal(6,2) NOT NULL,
  `max_score` decimal(6,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `learner_notes`
--

CREATE TABLE `learner_notes` (
  `id` int(11) NOT NULL,
  `learner_id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `note_text` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `learner_promotions`
--

CREATE TABLE `learner_promotions` (
  `id` int(11) NOT NULL,
  `learner_id` int(11) NOT NULL,
  `from_class_id` int(11) NOT NULL,
  `to_class_id` int(11) NOT NULL,
  `promotion_year` int(11) NOT NULL,
  `promoted_at` datetime NOT NULL,
  `note` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `library_access_logs`
--

CREATE TABLE `library_access_logs` (
  `id` int(11) NOT NULL,
  `resource_id` int(11) NOT NULL,
  `learner_id` int(11) NOT NULL,
  `action` enum('view','download') NOT NULL,
  `accessed_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `library_resources`
--

CREATE TABLE `library_resources` (
  `id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `document_type` varchar(40) NOT NULL DEFAULT 'other',
  `title` varchar(180) NOT NULL,
  `description` text DEFAULT NULL,
  `original_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `mime_type` varchar(120) DEFAULT NULL,
  `file_size` bigint(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `is_active` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `parent_learner_links`
--

CREATE TABLE `parent_learner_links` (
  `id` int(11) NOT NULL,
  `parent_user_id` int(11) NOT NULL,
  `learner_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `password_reset_requests`
--

CREATE TABLE `password_reset_requests` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `email` varchar(150) NOT NULL,
  `requested_password_hash` varchar(255) NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `requester_note` varchar(255) DEFAULT NULL,
  `admin_note` varchar(255) DEFAULT NULL,
  `requested_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `reviewed_at` datetime DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `school_assets_register`
--

CREATE TABLE `school_assets_register` (
  `id` int(11) NOT NULL,
  `source_inventory_id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `category` enum('textbook','furniture','equipment','other') NOT NULL DEFAULT 'other',
  `item_name` varchar(180) NOT NULL,
  `quantity` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `item_condition` varchar(80) DEFAULT NULL,
  `inventory_note` text DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `school_expenses`
--

CREATE TABLE `school_expenses` (
  `id` int(11) NOT NULL,
  `expense_date` date NOT NULL,
  `category` varchar(80) NOT NULL,
  `payee` varchar(180) DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  `amount_original` decimal(12,2) NOT NULL,
  `currency_code` enum('USD','ZIG','RAND') NOT NULL DEFAULT 'USD',
  `fx_rate_to_usd` decimal(18,8) NOT NULL,
  `amount_usd` decimal(12,2) NOT NULL,
  `payment_method` varchar(60) DEFAULT NULL,
  `reference_no` varchar(80) DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  `proof_original_name` varchar(255) DEFAULT NULL,
  `proof_file_path` varchar(255) DEFAULT NULL,
  `proof_mime_type` varchar(120) DEFAULT NULL,
  `proof_file_size` bigint(20) DEFAULT NULL,
  `recorded_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `staff_profiles`
--

CREATE TABLE `staff_profiles` (
  `user_id` int(11) NOT NULL,
  `sex` enum('Male','Female','Other') DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `ec_number` varchar(80) DEFAULT NULL,
  `national_id` varchar(80) DEFAULT NULL,
  `work_profile` varchar(255) DEFAULT NULL,
  `qualifications` text DEFAULT NULL,
  `subject_specialization` varchar(255) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `subjects` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `class_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `subjects` (`id`, `name`, `class_id`, `created_at`) VALUES
(1, 'English', 10, '2026-07-21 15:00:08'),
(2, 'English', 11, '2026-07-21 15:00:08'),
(3, 'English', 12, '2026-07-21 15:00:08'),
(4, 'English', 13, '2026-07-21 15:00:08'),
(5, 'English', 14, '2026-07-21 15:00:08'),
(6, 'English', 15, '2026-07-21 15:00:08'),
(7, 'Ndebele', 10, '2026-07-21 15:00:35'),
(8, 'Ndebele', 11, '2026-07-21 15:00:35'),
(9, 'Ndebele', 12, '2026-07-21 15:00:35'),
(10, 'Ndebele', 13, '2026-07-21 15:00:35'),
(11, 'Ndebele', 14, '2026-07-21 15:00:35'),
(12, 'Ndebele', 15, '2026-07-21 15:00:35'),
(13, 'Maths', 10, '2026-07-21 15:00:51'),
(14, 'Maths', 11, '2026-07-21 15:00:51'),
(15, 'Maths', 12, '2026-07-21 15:00:51'),
(16, 'Maths', 13, '2026-07-21 15:00:51'),
(17, 'Maths', 14, '2026-07-21 15:00:51'),
(18, 'Maths', 15, '2026-07-21 15:00:51');

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

--
-- Table structure for table `system_logs`
--

CREATE TABLE `system_logs` (
  `id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `actor_id` int(11) DEFAULT NULL,
  `actor_role` varchar(40) DEFAULT NULL,
  `action` varchar(120) NOT NULL,
  `details` text DEFAULT NULL,
  `entity_type` varchar(80) DEFAULT NULL,
  `entity_id` int(11) DEFAULT NULL,
  `ip_address` varchar(80) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `system_logs`
--

INSERT INTO `system_logs` (`id`, `created_at`, `actor_id`, `actor_role`, `action`, `details`, `entity_type`, `entity_id`, `ip_address`) VALUES
(1, '2026-07-22 11:54:57', 1, 'admin', 'verification_action', 'Verification entry from tool', 'user', 1, '127.0.0.1'),
(2, '2026-07-22 11:57:29', 1, 'admin', 'user_login', 'Successful login for admin@mavelahigh.org', 'user', 1, '::1');

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

--
-- Table structure for table `teacher_documents`
--

CREATE TABLE `teacher_documents` (
  `id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `document_type` varchar(40) NOT NULL,
  `title` varchar(180) NOT NULL,
  `description` text DEFAULT NULL,
  `original_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `mime_type` varchar(120) DEFAULT NULL,
  `file_size` bigint(20) DEFAULT NULL,
  `status` enum('submitted','reviewed','needs_correction','approved') NOT NULL DEFAULT 'submitted',
  `admin_comment` text DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `hop_status` enum('submitted','reviewed','needs_correction','approved') NOT NULL DEFAULT 'submitted',
  `hop_comment` text DEFAULT NULL,
  `hop_reviewed_by` int(11) DEFAULT NULL,
  `hop_reviewed_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `teacher_hop_assignments`
--

CREATE TABLE `teacher_hop_assignments` (
  `id` int(11) NOT NULL,
  `hop_user_id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `assigned_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `teacher_hop_assignments`
--

INSERT INTO `teacher_hop_assignments` (`id`, `hop_user_id`, `teacher_id`, `assigned_by`, `created_at`, `updated_at`) VALUES
(1, 6, 3, 1, '2026-07-21 19:25:13', NULL);

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

--
-- Table structure for table `teacher_leave_requests`
--

CREATE TABLE `teacher_leave_requests` (
  `id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `leave_type` varchar(60) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `reason` text NOT NULL,
  `supporting_document_path` varchar(255) DEFAULT NULL,
  `supporting_document_name` varchar(255) DEFAULT NULL,
  `supporting_document_mime` varchar(120) DEFAULT NULL,
  `supporting_document_size` bigint(20) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `admin_comment` text DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `teacher_subjects` (
  `id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `subject_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_unicode_ci;

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

INSERT INTO `teacher_subjects` (`id`, `teacher_id`, `subject_id`, `class_id`, `created_at`) VALUES
(2, 5, 1, 10, '2026-07-21 15:03:18');

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

--
-- Table structure for table `transfer_letters`
--

CREATE TABLE `transfer_letters` (
  `id` int(11) NOT NULL,
  `transfer_request_id` int(11) NOT NULL,
  `letter_html` longtext NOT NULL,
  `generated_by` int(11) DEFAULT NULL,
  `generated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `transfer_requests`
--

CREATE TABLE `transfer_requests` (
  `id` int(11) NOT NULL,
  `parent_user_id` int(11) NOT NULL,
  `learner_id` int(11) NOT NULL,
  `destination_school` varchar(180) NOT NULL,
  `reason` text NOT NULL,
  `requested_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `status` enum('pending_admin','clearance_requested','approved','rejected','completed') NOT NULL DEFAULT 'pending_admin',
  `teacher_status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `teacher_comment` text DEFAULT NULL,
  `teacher_reviewed_by` int(11) DEFAULT NULL,
  `teacher_reviewed_at` datetime DEFAULT NULL,
  `accountant_status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `accountant_comment` text DEFAULT NULL,
  `accountant_reviewed_by` int(11) DEFAULT NULL,
  `accountant_reviewed_at` datetime DEFAULT NULL,
  `admin_comment` text DEFAULT NULL,
  `reviewed_by_admin` int(11) DEFAULT NULL,
  `reviewed_at_admin` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` enum('admin','teacher','hop','accountant','parent') NOT NULL DEFAULT 'teacher',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `users` (`id`, `name`, `email`, `password_hash`, `role`, `created_at`) VALUES
(1, 'Mavela System Admin', 'admin@mavelahigh.org', '$2y$10$XW/kdqcuss7Ltl51OCbUz.wSyBAfI.q.QICzCz7D4UGj.DBzwh/J2', 'admin', '2026-07-21 14:42:47'),
(2, 'Mavela Accounts Office', 'accounts@mavelahigh.org', '$2y$10$XW/kdqcuss7Ltl51OCbUz.wSyBAfI.q.QICzCz7D4UGj.DBzwh/J2', 'accountant', '2026-07-21 14:42:47'),
(3, 'Jonas Ngwenya', 'jays@mavelahigh.org', '$2y$10$XW/kdqcuss7Ltl51OCbUz.wSyBAfI.q.QICzCz7D4UGj.DBzwh/J2', 'teacher', '2026-07-21 14:48:49'),
(5, 'Jonas Ngwenya', 'jonas@mavelahigh.org', '$2y$10$XW/kdqcuss7Ltl51OCbUz.wSyBAfI.q.QICzCz7D4UGj.DBzwh/J2', 'teacher', '2026-07-21 14:49:37'),
(6, 'John Moyo', 'joe@mavelahigh.org', '$2y$10$XW/kdqcuss7Ltl51OCbUz.wSyBAfI.q.QICzCz7D4UGj.DBzwh/J2', 'hop', '2026-07-21 19:24:53');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admissions_pending`
--
ALTER TABLE `admissions_pending`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_ap_teacher` (`teacher_id`),
  ADD KEY `fk_ap_class` (`class_id`),
  ADD KEY `idx_ap_status_created` (`status`,`created_at`);

--
-- Indexes for table `announcements`
--
ALTER TABLE `announcements`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_ann_sender` (`sender_id`),
  ADD KEY `fk_ann_reviewer` (`reviewed_by`),
  ADD KEY `fk_ann_target_class` (`target_class_id`),
  ADD KEY `fk_ann_target_learner` (`target_learner_id`),
  ADD KEY `idx_ann_audience_status_created` (`audience`,`status`,`created_at`);

--
-- Indexes for table `app_settings`
--
ALTER TABLE `app_settings`
  ADD PRIMARY KEY (`setting_key`);

--
-- Indexes for table `classes`
--
ALTER TABLE `classes`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `class_teacher_assignments`
--
ALTER TABLE `class_teacher_assignments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `class_id` (`class_id`),
  ADD KEY `fk_cta_assigned_by` (`assigned_by`),
  ADD KEY `idx_cta_teacher` (`teacher_id`);

--
-- Indexes for table `currency_rates`
--
ALTER TABLE `currency_rates`
  ADD PRIMARY KEY (`currency_code`);

--
-- Indexes for table `daily_attendance_register`
--
ALTER TABLE `daily_attendance_register`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_dar_teacher_class_learner_date` (`teacher_id`,`class_id`,`learner_id`,`attendance_date`),
  ADD KEY `fk_dar_class` (`class_id`),
  ADD KEY `fk_dar_learner` (`learner_id`),
  ADD KEY `idx_dar_teacher_date` (`teacher_id`,`attendance_date`);

--
-- Indexes for table `elearning_posts`
--
ALTER TABLE `elearning_posts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_ep_subject` (`subject_id`),
  ADD KEY `idx_ep_teacher_created` (`teacher_id`,`created_at`),
  ADD KEY `idx_ep_class` (`class_id`);

--
-- Indexes for table `elearning_submissions`
--
ALTER TABLE `elearning_submissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_es_post_learner` (`post_id`,`learner_id`),
  ADD KEY `idx_es_post` (`post_id`),
  ADD KEY `idx_es_learner` (`learner_id`);

--
-- Indexes for table `fee_invoices`
--
ALTER TABLE `fee_invoices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `invoice_number` (`invoice_number`),
  ADD KEY `fk_fee_invoices_class` (`class_id`),
  ADD KEY `fk_fee_invoices_user` (`created_by`),
  ADD KEY `idx_fee_invoices_learner` (`learner_id`),
  ADD KEY `idx_fee_invoices_status` (`status`),
  ADD KEY `idx_fee_invoices_due` (`due_date`);

--
-- Indexes for table `fee_payments`
--
ALTER TABLE `fee_payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_fee_payments_user` (`received_by`),
  ADD KEY `idx_fee_payments_learner_date` (`learner_id`,`payment_date`),
  ADD KEY `idx_fee_payments_invoice` (`invoice_id`);

--
-- Indexes for table `hop_profiles`
--
ALTER TABLE `hop_profiles`
  ADD PRIMARY KEY (`user_id`);

--
-- Indexes for table `inventory_records`
--
ALTER TABLE `inventory_records`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_ir_class` (`class_id`),
  ADD KEY `fk_ir_reviewer` (`reviewed_by`),
  ADD KEY `idx_ir_teacher_created` (`teacher_id`,`created_at`),
  ADD KEY `idx_ir_status_created` (`status`,`created_at`);

--
-- Indexes for table `learners`
--
ALTER TABLE `learners`
  ADD PRIMARY KEY (`reg_number`),
  ADD UNIQUE KEY `id` (`id`),
  ADD UNIQUE KEY `uq_learners_id` (`id`),
  ADD KEY `idx_learners_is_frozen` (`is_frozen`),
  ADD KEY `fk_learners_assigned_teacher` (`assigned_teacher_id`);

--
-- Indexes for table `learner_applications`
--
ALTER TABLE `learner_applications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_la_preferred_class` (`preferred_class_id`),
  ADD KEY `fk_la_assigned_class` (`assigned_class_id`),
  ADD KEY `fk_la_assigned_teacher` (`assigned_teacher_id`),
  ADD KEY `idx_la_status` (`status`),
  ADD KEY `idx_la_created` (`created_at`);

--
-- Indexes for table `learner_marks`
--
ALTER TABLE `learner_marks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_marks_learner` (`learner_id`),
  ADD KEY `fk_marks_class` (`class_id`),
  ADD KEY `fk_marks_subject` (`subject_id`),
  ADD KEY `idx_marks_teacher_date` (`teacher_id`,`assessment_date`),
  ADD KEY `idx_marks_type` (`assessment_type`);

--
-- Indexes for table `learner_notes`
--
ALTER TABLE `learner_notes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_notes_learner` (`learner_id`),
  ADD KEY `fk_notes_teacher` (`teacher_id`);

--
-- Indexes for table `learner_promotions`
--
ALTER TABLE `learner_promotions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_lp_year` (`promotion_year`),
  ADD KEY `idx_lp_learner` (`learner_id`);

--
-- Indexes for table `library_access_logs`
--
ALTER TABLE `library_access_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_lal_resource_time` (`resource_id`,`accessed_at`),
  ADD KEY `idx_lal_learner_time` (`learner_id`,`accessed_at`);

--
-- Indexes for table `library_resources`
--
ALTER TABLE `library_resources`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_lr_subject` (`subject_id`),
  ADD KEY `idx_lr_teacher_created` (`teacher_id`,`created_at`),
  ADD KEY `idx_lr_class` (`class_id`);

--
-- Indexes for table `parent_learner_links`
--
ALTER TABLE `parent_learner_links`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `learner_id` (`learner_id`),
  ADD UNIQUE KEY `uq_pl_parent_learner` (`parent_user_id`,`learner_id`),
  ADD KEY `idx_pl_parent_user` (`parent_user_id`);

--
-- Indexes for table `password_reset_requests`
--
ALTER TABLE `password_reset_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_prr_reviewer` (`reviewed_by`),
  ADD KEY `idx_prr_status_date` (`status`,`requested_at`),
  ADD KEY `idx_prr_user_status` (`user_id`,`status`);

--
-- Indexes for table `school_assets_register`
--
ALTER TABLE `school_assets_register`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `source_inventory_id` (`source_inventory_id`),
  ADD KEY `fk_sar_teacher` (`teacher_id`),
  ADD KEY `fk_sar_approver` (`approved_by`),
  ADD KEY `idx_sar_class` (`class_id`),
  ADD KEY `idx_sar_category` (`category`),
  ADD KEY `idx_sar_approved_at` (`approved_at`);

--
-- Indexes for table `school_expenses`
--
ALTER TABLE `school_expenses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_school_expenses_user` (`recorded_by`),
  ADD KEY `idx_school_expense_date` (`expense_date`),
  ADD KEY `idx_school_expense_currency` (`currency_code`);

--
-- Indexes for table `staff_profiles`
--
ALTER TABLE `staff_profiles`
  ADD PRIMARY KEY (`user_id`),
  ADD KEY `fk_staff_profiles_updated_by` (`updated_by`);

--
-- Indexes for table `subjects`
--
ALTER TABLE `subjects`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_subjects_class` (`class_id`);

--
-- Indexes for table `system_logs`
--
ALTER TABLE `system_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_system_logs_created_at` (`created_at`),
  ADD KEY `idx_system_logs_action` (`action`);

--
-- Indexes for table `teacher_documents`
--
ALTER TABLE `teacher_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_td_class` (`class_id`),
  ADD KEY `fk_td_subject` (`subject_id`),
  ADD KEY `fk_td_reviewer` (`reviewed_by`),
  ADD KEY `idx_td_teacher_created` (`teacher_id`,`created_at`),
  ADD KEY `idx_td_status` (`status`),
  ADD KEY `fk_td_hop_reviewer` (`hop_reviewed_by`);

--
-- Indexes for table `teacher_hop_assignments`
--
ALTER TABLE `teacher_hop_assignments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `teacher_id` (`teacher_id`),
  ADD KEY `fk_tha_assigned_by` (`assigned_by`),
  ADD KEY `idx_tha_hop_user` (`hop_user_id`);

--
-- Indexes for table `teacher_leave_requests`
--
ALTER TABLE `teacher_leave_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_tlr_teacher` (`teacher_id`),
  ADD KEY `idx_tlr_status` (`status`);

--
-- Indexes for table `teacher_subjects`
--
ALTER TABLE `teacher_subjects`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_teacher_subject_class` (`teacher_id`,`subject_id`,`class_id`),
  ADD KEY `fk_ts_subject` (`subject_id`),
  ADD KEY `fk_ts_class` (`class_id`);

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

--
-- Indexes for table `transfer_requests`
--
ALTER TABLE `transfer_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_tr_status` (`status`),
  ADD KEY `idx_tr_parent` (`parent_user_id`),
  ADD KEY `idx_tr_learner` (`learner_id`);

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `admissions_pending`
--
ALTER TABLE `admissions_pending`
  ADD CONSTRAINT `fk_ap_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ap_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `announcements`
--
ALTER TABLE `announcements`
  ADD CONSTRAINT `fk_ann_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_ann_sender` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ann_target_class` FOREIGN KEY (`target_class_id`) REFERENCES `classes` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_ann_target_learner` FOREIGN KEY (`target_learner_id`) REFERENCES `learners` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `class_teacher_assignments`
--
ALTER TABLE `class_teacher_assignments`
  ADD CONSTRAINT `fk_cta_assigned_by` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_cta_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_cta_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `daily_attendance_register`
--
ALTER TABLE `daily_attendance_register`
  ADD CONSTRAINT `fk_dar_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_dar_learner` FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_dar_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `elearning_posts`
--
ALTER TABLE `elearning_posts`
  ADD CONSTRAINT `fk_ep_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ep_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_ep_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `elearning_submissions`
--
ALTER TABLE `elearning_submissions`
  ADD CONSTRAINT `fk_es_learner` FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_es_post` FOREIGN KEY (`post_id`) REFERENCES `elearning_posts` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `fee_invoices`
--
ALTER TABLE `fee_invoices`
  ADD CONSTRAINT `fk_fee_invoices_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_fee_invoices_learner` FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_fee_invoices_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `fee_payments`
--
ALTER TABLE `fee_payments`
  ADD CONSTRAINT `fk_fee_payments_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `fee_invoices` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_fee_payments_learner` FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_fee_payments_user` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

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

--
-- Constraints for table `inventory_records`
--
ALTER TABLE `inventory_records`
  ADD CONSTRAINT `fk_ir_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ir_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_ir_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `learners`
--
ALTER TABLE `learners`
  ADD CONSTRAINT `fk_learners_assigned_teacher` FOREIGN KEY (`assigned_teacher_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `learner_applications`
--
ALTER TABLE `learner_applications`
  ADD CONSTRAINT `fk_la_assigned_class` FOREIGN KEY (`assigned_class_id`) REFERENCES `classes` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_la_assigned_teacher` FOREIGN KEY (`assigned_teacher_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_la_preferred_class` FOREIGN KEY (`preferred_class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `learner_marks`
--
ALTER TABLE `learner_marks`
  ADD CONSTRAINT `fk_marks_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_marks_learner` FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_marks_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_marks_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `learner_notes`
--
ALTER TABLE `learner_notes`
  ADD CONSTRAINT `fk_notes_learner` FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_notes_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `learner_promotions`
--
ALTER TABLE `learner_promotions`
  ADD CONSTRAINT `fk_lp_learner` FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `library_access_logs`
--
ALTER TABLE `library_access_logs`
  ADD CONSTRAINT `fk_lal_learner` FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_lal_resource` FOREIGN KEY (`resource_id`) REFERENCES `library_resources` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `library_resources`
--
ALTER TABLE `library_resources`
  ADD CONSTRAINT `fk_lr_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_lr_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_lr_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `parent_learner_links`
--
ALTER TABLE `parent_learner_links`
  ADD CONSTRAINT `fk_pl_learner` FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_pl_parent` FOREIGN KEY (`parent_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `password_reset_requests`
--
ALTER TABLE `password_reset_requests`
  ADD CONSTRAINT `fk_prr_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_prr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `school_assets_register`
--
ALTER TABLE `school_assets_register`
  ADD CONSTRAINT `fk_sar_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_sar_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_sar_source_inventory` FOREIGN KEY (`source_inventory_id`) REFERENCES `inventory_records` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_sar_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `school_expenses`
--
ALTER TABLE `school_expenses`
  ADD CONSTRAINT `fk_school_expenses_user` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `staff_profiles`
--
ALTER TABLE `staff_profiles`
  ADD CONSTRAINT `fk_staff_profiles_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_staff_profiles_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `subjects`
--
ALTER TABLE `subjects`
  ADD CONSTRAINT `fk_subjects_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `teacher_documents`
--
ALTER TABLE `teacher_documents`
  ADD CONSTRAINT `fk_td_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_td_hop_reviewer` FOREIGN KEY (`hop_reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_td_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_td_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_td_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `teacher_hop_assignments`
--
ALTER TABLE `teacher_hop_assignments`
  ADD CONSTRAINT `fk_tha_assigned_by` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_tha_hop_user` FOREIGN KEY (`hop_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_tha_teacher_user` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `teacher_leave_requests`
--
ALTER TABLE `teacher_leave_requests`
  ADD CONSTRAINT `fk_tlr_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `teacher_subjects`
--
ALTER TABLE `teacher_subjects`
  ADD CONSTRAINT `fk_ts_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ts_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ts_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `transfer_letters`
--
ALTER TABLE `transfer_letters`
  ADD CONSTRAINT `fk_tl_request` FOREIGN KEY (`transfer_request_id`) REFERENCES `transfer_requests` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `transfer_requests`
--
ALTER TABLE `transfer_requests`
  ADD CONSTRAINT `fk_tr_learner` FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_tr_parent` FOREIGN KEY (`parent_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 */;
