-- ========================================================
-- EdumacFlo School Management System - Production Seed Database
-- Version: Clean Deployment Release
-- Generated: 2026-09-04 15:00:41
-- ========================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
SET NAMES utf8mb4;

-- --------------------------------------------------------
-- Table structure for table `account_details`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `account_details`;
CREATE TABLE `account_details` (
  `id` int NOT NULL AUTO_INCREMENT,
  `account_name` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `account_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `bank_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `account_purpose` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping seed data for table `account_details`
INSERT INTO `account_details` (`id`, `account_name`, `account_number`, `bank_name`, `account_purpose`) VALUES
(1, 'School Account', '0123456789', 'Zenith Bank', 'School fees & tuition');

-- --------------------------------------------------------
-- Table structure for table `administratives`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `administratives`;
CREATE TABLE `administratives` (
  `id` int NOT NULL AUTO_INCREMENT,
  `school_name` varchar(255) DEFAULT NULL,
  `school_address` varchar(255) DEFAULT NULL,
  `school_motto` varchar(255) DEFAULT NULL,
  `school_phone` varchar(225) DEFAULT NULL,
  `school_mail` varchar(225) DEFAULT NULL,
  `school_website` varchar(225) DEFAULT NULL,
  `school_whatsapp` varchar(225) DEFAULT NULL,
  `school_short_name` varchar(225) DEFAULT NULL,
  `adm_no_initials` varchar(50) DEFAULT NULL,
  `school_favicon` varchar(255) DEFAULT NULL,
  `school_logo` varchar(255) DEFAULT NULL,
  `school_stamp` varchar(255) DEFAULT NULL,
  `upload_date` varchar(50) DEFAULT NULL,
  `upload_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `remote_sync_url` varchar(255) DEFAULT '',
  `sync_api_key` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping seed data for table `administratives`
INSERT INTO `administratives` (`id`, `school_name`, `school_address`, `school_motto`, `school_phone`, `school_mail`, `school_website`, `school_whatsapp`, `school_short_name`, `adm_no_initials`, `school_favicon`, `school_logo`, `school_stamp`, `upload_date`, `remote_sync_url`, `sync_api_key`) VALUES
(1, 'Edumac High School', '123 Education Boulevard, City Center', 'Excellence in Knowledge and Character', '+234 800 000 0000', 'admin@edumac.edu', 'https://www.edumac.edu', '+234 800 000 0000', 'Edumac High', 'EDM', 'uploads/school/1782924305_646219.png', 'uploads/school/1782924305_646219.png', '', '2026-01-01', '', '');

-- --------------------------------------------------------
-- Table structure for table `annual_result`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `annual_result`;
CREATE TABLE `annual_result` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reg_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `class` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `session` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `subjects` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `first_term_ca` int DEFAULT '0',
  `first_term_exam` int DEFAULT '0',
  `first_term_total` int DEFAULT '0',
  `second_term_ca` int DEFAULT '0',
  `second_term_exam` int DEFAULT '0',
  `second_term_total` int DEFAULT '0',
  `third_term_ca` int DEFAULT '0',
  `third_term_exam` int DEFAULT '0',
  `third_term_total` int DEFAULT '0',
  `overall_total` int DEFAULT '0',
  `overall_average` 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,
  `terms_counted` int DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_record` (`reg_number`,`class`,`session`,`subjects`),
  KEY `idx_annual_regclasssessub` (`reg_number`,`class`,`session`,`subjects`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `annual_result_issues`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `annual_result_issues`;
CREATE TABLE `annual_result_issues` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reg_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `class` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `session` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `subjects` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `issue` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `annual_result_summary`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `annual_result_summary`;
CREATE TABLE `annual_result_summary` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reg_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `class` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `session` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `first_term_avg` float DEFAULT NULL,
  `second_term_avg` float DEFAULT NULL,
  `third_term_avg` float DEFAULT NULL,
  `total_annual_scores` int NOT NULL,
  `total_annual_average` float DEFAULT NULL,
  `overall_avg_marks` float DEFAULT NULL,
  `position` int DEFAULT NULL,
  `remarks` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_reg_class_session` (`reg_number`,`class`,`session`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `applications`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `applications`;
CREATE TABLE `applications` (
  `id` int NOT NULL,
  `passport` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `application_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `fname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `mname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `oname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `dob` date NOT NULL,
  `gender` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `nationality` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `state` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `lga` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `lschool` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `lclass` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `ftitle` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `fsname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `foname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `faddress` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `foccupation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `femail` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `fpnumber` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `fapnumber` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `mtitle` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `msname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `moname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `maddress` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `moccupation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `memail` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `mpnumber` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `mapnumber` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `language` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `challenge` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `hear` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `attendance_settings`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `attendance_settings`;
CREATE TABLE `attendance_settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `school_lat` decimal(10,7) DEFAULT NULL,
  `school_lng` decimal(10,7) DEFAULT NULL,
  `geo_radius_meters` int DEFAULT '200',
  `geo_enforce` enum('strict','warn','off') DEFAULT 'warn',
  `open_time` time DEFAULT '07:00:00',
  `grace_time` time DEFAULT '08:00:00',
  `close_time` time DEFAULT '09:00:00',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `behavioral`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `behavioral`;
CREATE TABLE `behavioral` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reg_number` varchar(250) DEFAULT NULL,
  `name` varchar(250) DEFAULT NULL,
  `class` varchar(50) DEFAULT NULL,
  `term` varchar(50) DEFAULT NULL,
  `session` varchar(50) DEFAULT NULL,
  `puntuality` varchar(11) DEFAULT NULL,
  `neatness` varchar(11) DEFAULT NULL,
  `relationship_with_others` varchar(11) DEFAULT NULL,
  `regards_for_authority` varchar(11) DEFAULT NULL,
  `attitude_towards_academics` varchar(11) DEFAULT NULL,
  `honesty` varchar(11) DEFAULT NULL,
  `initiative` varchar(11) DEFAULT NULL,
  `form_master_or_mistress` varchar(11) DEFAULT NULL,
  `date` varchar(50) DEFAULT NULL,
  `is_synced` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `class_level`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `class_level`;
CREATE TABLE `class_level` (
  `id` int NOT NULL AUTO_INCREMENT,
  `level` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping seed data for table `class_level`
INSERT INTO `class_level` VALUES
('1', 'PRIMARY'),
('2', 'NURSERY'),
('3', 'UPPER BASIC'),
('4', 'SENIOR SECTION');

-- --------------------------------------------------------
-- Table structure for table `classes`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `classes` varchar(50) DEFAULT NULL,
  `level` varchar(225) DEFAULT NULL,
  `date_added` varchar(50) DEFAULT NULL,
  `time_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping seed data for table `classes`
INSERT INTO `classes` VALUES
('131', 'Pre-Nursery', 'NURSERY', NULL, '2025-08-30 11:43:50'),
('132', 'Nursery 1', 'NURSERY', NULL, '2025-08-30 11:44:11'),
('133', 'Nursery 2', 'NURSERY', NULL, '2025-08-30 11:45:06'),
('134', 'Kindergarten', 'PRIMARY', NULL, '2025-08-30 11:45:54'),
('135', 'Basic 1', 'PRIMARY', NULL, '2025-08-30 11:46:15'),
('136', 'Basic 2', 'PRIMARY', NULL, '2025-08-30 11:46:38'),
('137', 'Basic 3', 'PRIMARY', NULL, '2025-08-30 11:46:50'),
('138', 'Basic 4', 'PRIMARY', NULL, '2025-08-30 11:47:06'),
('139', 'Basic 5', 'PRIMARY', NULL, '2025-08-30 11:47:20'),
('140', 'JSS 1', 'UPPER BASIC', NULL, '2025-08-30 11:47:43'),
('141', 'JSS 2', 'UPPER BASIC', NULL, '2025-10-08 15:46:45');

-- --------------------------------------------------------
-- Table structure for table `current_season`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `current_season`;
CREATE TABLE `current_season` (
  `id` int NOT NULL AUTO_INCREMENT,
  `current_session` varchar(20) DEFAULT NULL,
  `current_term` varchar(50) DEFAULT NULL,
  `date_declared` varchar(50) DEFAULT NULL,
  `time_declared` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping seed data for table `current_season`
INSERT INTO `current_season` VALUES
('1', '2025/2026', 'Second Term', '2026-08-24', '2026-08-24 09:29:12');

-- --------------------------------------------------------
-- Table structure for table `curriculum`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `curriculum`;
CREATE TABLE `curriculum` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `class` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `term` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `session` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `uploaded_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `uploaded_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'E-Book',
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `dates`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `dates`;
CREATE TABLE `dates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `end_of_term` varchar(50) DEFAULT NULL,
  `next_term_begins` varchar(50) DEFAULT NULL,
  `date_declared` varchar(50) DEFAULT NULL,
  `time_declared` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping seed data for table `dates`
INSERT INTO `dates` VALUES
('2', '2025-12-19', '2026-01-05', '2026-08-16', '2016-12-25 12:18:59');

-- --------------------------------------------------------
-- Table structure for table `debtors`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `debtors`;
CREATE TABLE `debtors` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `amount` int DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `deadline` varchar(50) DEFAULT NULL,
  `date` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `event`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `event`;
CREATE TABLE `event` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `exam_answers`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `exam_answers`;
CREATE TABLE `exam_answers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `exam_group_result_id` int NOT NULL,
  `student_id` int DEFAULT NULL,
  `question_id` int NOT NULL,
  `answer_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_exam_result` (`exam_group_result_id`),
  KEY `fk_question` (`question_id`),
  CONSTRAINT `fk_exam_group_result` FOREIGN KEY (`exam_group_result_id`) REFERENCES `exam_group_results` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `exam_group_results`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `exam_group_results`;
CREATE TABLE `exam_group_results` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `exam_group_id` int NOT NULL,
  `exam_type` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `class` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `term` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `session` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `mcq_score` decimal(5,2) DEFAULT NULL,
  `theory_score` decimal(5,2) DEFAULT NULL,
  `total` decimal(5,2) DEFAULT NULL,
  `status` enum('pending','graded') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `remarks` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_result` (`student_id`,`exam_group_id`),
  KEY `idx_exam_group` (`exam_group_id`),
  KEY `idx_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `exam_groups`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `exam_groups`;
CREATE TABLE `exam_groups` (
  `id` int NOT NULL AUTO_INCREMENT,
  `class` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `subject_id` int DEFAULT NULL,
  `term` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `session` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_group` (`class`,`subject_id`,`term`,`session`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `exam_instructions`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `exam_instructions`;
CREATE TABLE `exam_instructions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `exam_id` int NOT NULL,
  `instruction` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `exam_id` (`exam_id`),
  CONSTRAINT `exam_instructions_ibfk_1` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `exam_results`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `exam_results`;
CREATE TABLE `exam_results` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `exam_id` int NOT NULL,
  `total_questions` int DEFAULT NULL,
  `correct_answers` int DEFAULT NULL,
  `mcq_score` int DEFAULT '0',
  `theory_score` int DEFAULT '0',
  `status` enum('pending','completed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `total_score` int GENERATED ALWAYS AS ((`mcq_score` + `theory_score`)) STORED,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `exam_id` (`exam_id`),
  CONSTRAINT `exam_results_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`),
  CONSTRAINT `exam_results_ibfk_2` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `exams`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `exams`;
CREATE TABLE `exams` (
  `id` int NOT NULL AUTO_INCREMENT,
  `exam_type` enum('ca1','ca2','exam') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `class` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `subject_id` int DEFAULT NULL,
  `duration_minutes` int DEFAULT NULL,
  `exam_date` date DEFAULT NULL,
  `term` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `session` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('active','inactive') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'inactive',
  `exam_group_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_exam_group` (`exam_group_id`),
  CONSTRAINT `fk_exam_group` FOREIGN KEY (`exam_group_id`) REFERENCES `exam_groups` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='online_exams';

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `expenses`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `expenses`;
CREATE TABLE `expenses` (
  `id` int NOT NULL,
  `paid_to` varchar(250) DEFAULT NULL,
  `paid_for` varchar(250) DEFAULT NULL,
  `amount_paid` int DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `date` varchar(50) DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `fee_payment`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `fee_payment`;
CREATE TABLE `fee_payment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reg_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `student_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `student_class` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `total_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
  `fee_term` enum('First Term','Second Term','Third Term') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `fee_session` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL,
  `student_balance` decimal(10,2) DEFAULT '0.00',
  `teller_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_upload` date NOT NULL,
  `created_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_status` enum('Cleared','Owing') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Owing',
  `is_synced` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `fee_structure`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `fee_structure`;
CREATE TABLE `fee_structure` (
  `id` int NOT NULL AUTO_INCREMENT,
  `class` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `term` enum('First Term','Second Term','Third Term') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `session` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_synced` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `internal_mail`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `internal_mail`;
CREATE TABLE `internal_mail` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sender_id` int NOT NULL,
  `sender_name` varchar(100) NOT NULL,
  `sender_role` varchar(50) NOT NULL,
  `recipient_type` enum('admin','teacher','student','all') NOT NULL,
  `recipient_id` varchar(50) DEFAULT NULL,
  `recipient_name` varchar(100) DEFAULT NULL,
  `subject` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT '0',
  `sent_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `inventory_categories`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `inventory_categories`;
CREATE TABLE `inventory_categories` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `inventory_invoice_items`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `inventory_invoice_items`;
CREATE TABLE `inventory_invoice_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `invoice_id` int DEFAULT NULL,
  `item_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quantity` int DEFAULT NULL,
  `unit_price` decimal(10,2) DEFAULT NULL,
  `total_price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `inventory_invoices`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `inventory_invoices`;
CREATE TABLE `inventory_invoices` (
  `id` int NOT NULL AUTO_INCREMENT,
  `invoice_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reg_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `student_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `student_class` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `total_amount` decimal(10,2) DEFAULT NULL,
  `issued_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `amount_paid` decimal(10,2) NOT NULL DEFAULT '0.00',
  `balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `payment_status` enum('Unpaid','Paid','Balance') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Unpaid',
  `created_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `inventory_items`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `inventory_items`;
CREATE TABLE `inventory_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_id` int DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `quantity` int DEFAULT '0',
  `min_quantity` int DEFAULT '0',
  `date_added` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `inventory_movements`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `inventory_movements`;
CREATE TABLE `inventory_movements` (
  `id` int NOT NULL AUTO_INCREMENT,
  `item_id` int NOT NULL,
  `movement_type` enum('IN','OUT') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` int NOT NULL,
  `remarks` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `moved_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_moved` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `inventory_movements_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `inventory_items` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `jss`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `jss`;
CREATE TABLE `jss` (
  `id` int NOT NULL AUTO_INCREMENT,
  `jss_subjects` varchar(100) DEFAULT NULL,
  `upload_date` varchar(50) DEFAULT NULL,
  `upload_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping seed data for table `jss`
INSERT INTO `jss` VALUES
('1', 'ENGLISH LANGUAGE', '12-Oct-2024', '2024-10-12 09:54:55'),
('2', 'MATHEMATICS', '12-Oct-2024', '2024-10-12 09:55:04'),
('5', 'BUSINESS STUDIES', '12-Oct-2024', '2024-10-12 09:55:19'),
('6', 'CREATIVE AND CULTURAL ARTS', '12-Oct-2024', '2024-10-12 09:55:25'),
('7', 'FRENCH LANGUAGE', '12-Oct-2024', '2024-10-12 09:55:29'),
('8', 'PREVOCATIONAL STUDIES', '12-Oct-2024', '2024-10-12 09:55:38'),
('9', 'NATIONAL VALUES', '12-Oct-2024', '2024-10-12 09:55:46'),
('10', 'HISTORY', '12-Oct-2024', '2024-10-12 09:55:58'),
('11', 'IRS/CRS', '12-Oct-2024', '2024-10-12 09:56:06'),
('12', 'HAUSA LANGUAGE', '12-Oct-2024', '2024-10-12 09:56:24'),
('13', 'BST 1 - BASIC SCIENCE AND PHE', '13-Nov-2024', '2024-11-13 00:33:58'),
('14', 'BST 2 - BASIC TECHNOLOGY AND ICT', '13-Nov-2024', '2024-11-13 00:34:05'),
('15', 'BIOLOGY', '14-May-2025', '2025-05-14 19:05:27');

-- --------------------------------------------------------
-- Table structure for table `jss_subject_number`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `jss_subject_number`;
CREATE TABLE `jss_subject_number` (
  `id` int NOT NULL AUTO_INCREMENT,
  `number_of_subject` int DEFAULT NULL,
  `declared_date` varchar(20) DEFAULT NULL,
  `declared_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping seed data for table `jss_subject_number`
INSERT INTO `jss_subject_number` VALUES
('1', '12', '01/Nov/2016', '2016-11-01 05:59:17');

-- --------------------------------------------------------
-- Table structure for table `lesson_plans`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `lesson_plans`;
CREATE TABLE `lesson_plans` (
  `id` int NOT NULL AUTO_INCREMENT,
  `file_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `file_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `subject` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `class` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `term` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `session` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `upload_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `login_attempts`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `login_attempts`;
CREATE TABLE `login_attempts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `login_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_agent` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `attempt_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `success` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `mail`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `mail`;
CREATE TABLE `mail` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `email` varchar(200) DEFAULT NULL,
  `message` text,
  `mail_date` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `malpractice_flags`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `malpractice_flags`;
CREATE TABLE `malpractice_flags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `exam_id` int NOT NULL,
  `flag_count` int DEFAULT '0',
  `flagged_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `malpractice_logs`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `malpractice_logs`;
CREATE TABLE `malpractice_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `exam_id` int NOT NULL,
  `warning_count` int NOT NULL,
  `log_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `malpractice_logs_ibfk_2` (`exam_id`),
  CONSTRAINT `malpractice_logs_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  CONSTRAINT `malpractice_logs_ibfk_2` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `news`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `news`;
CREATE TABLE `news` (
  `id` int NOT NULL AUTO_INCREMENT,
  `header` varchar(100) DEFAULT NULL,
  `body` text,
  `news_date` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `notifications`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `target` enum('staff','student','all') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'all',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `permissions`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `permissions`;
CREATE TABLE `permissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `permission_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permission_name` (`permission_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping seed data for table `permissions`
INSERT INTO `permissions` VALUES
('107', 'accepted_request'),
('106', 'account_detail'),
('47', 'admin_check_result'),
('82', 'admin_manage_notifications'),
('124', 'admin_result_page'),
('40', 'admin_upload_ca'),
('41', 'admin_upload_exam'),
('105', 'admission_request'),
('48', 'allocate_class_fee'),
('104', 'allocate_fees'),
('54', 'all_contact'),
('34', 'assign_class_to_staff'),
('55', 'assign_subject_to_staff'),
('80', 'cbt_create_exam'),
('81', 'cbt_list_exam'),
('6', 'class'),
('32', 'class_attendance'),
('76', 'class_teacher_result_comment'),
('103', 'create_invoice'),
('121', 'curriculum'),
('1', 'dashboard'),
('130', 'db_management'),
('135', 'defaulters_tracker'),
('102', 'delete_invoice'),
('28', 'demote_student'),
('141', 'download_student_template'),
('101', 'edit_invoice'),
('132', 'elibrary'),
('4', 'event'),
('137', 'excel_result_upload'),
('85', 'export_class_perf'),
('84', 'export_payroll_to_excel'),
('140', 'export_students'),
('49', 'fee_payment'),
('112', 'fee_structure'),
('113', 'fee_summary'),
('128', 'generate_fee_invoice'),
('22', 'generate_pins'),
('75', 'head_teacher_result_comment'),
('125', 'id_card_generator'),
('139', 'import_students'),
('118', 'inventory_categories'),
('117', 'inventory_create'),
('119', 'inventory_movement'),
('116', 'invoice_create'),
('100', 'lesson_plans'),
('134', 'mailbox'),
('120', 'manage_debtors'),
('99', 'manage_expenses'),
('19', 'manage_school_info'),
('129', 'manage_student_status'),
('50', 'outstanding_fees'),
('98', 'payment_history'),
('97', 'pending_expenses'),
('96', 'performance_by_class'),
('95', 'performance_by_subject'),
('94', 'performance_chart'),
('52', 'permissions'),
('123', 'portal'),
('83', 'print_invoice'),
('93', 'process_invoice'),
('27', 'promote_selected_students'),
('26', 'promote_students'),
('78', 'publish_annual_result'),
('45', 'publish_result'),
('136', 'qr_attendance'),
('36', 'register_student_subject'),
('92', 'rejected_request'),
('2', 'reset_password'),
('74', 'result_comment'),
('51', 'role'),
('91', 'save_account_details'),
('111', 'school_account_detail'),
('126', 'send_sms'),
('21', 'session_term'),
('5', 'settings'),
('10', 'slip'),
('38', 'specify_subjects_level'),
('39', 'specify_subject_number'),
('53', 'staff'),
('90', 'staff_payroll'),
('16', 'staff_profile'),
('13', 'student_check_result'),
('30', 'student_conducts'),
('133', 'student_elibrary'),
('89', 'student_fee'),
('9', 'student_password_reset'),
('15', 'student_print_slip'),
('8', 'student_profile'),
('12', 'student_register_subject'),
('18', 'student_reset_password'),
('17', 'subjects_to_offer'),
('61', 'subject_teacher_register_student_subjects'),
('68', 'subject_teacher_upload_ca'),
('69', 'subject_teacher_upload_exam'),
('62', 'subject_teacher_view_student_subjects'),
('71', 'subject_teacher_view_upload'),
('14', 'subject_to_offer'),
('131', 'sync_manager'),
('138', 'teacher_excel_result_upload'),
('56', 'teacher_register_student_subject'),
('57', 'teacher_reset_password'),
('58', 'teacher_upload_attendance'),
('66', 'teacher_upload_ca'),
('64', 'teacher_upload_conducts'),
('67', 'teacher_upload_exam'),
('59', 'teacher_view_attendance'),
('72', 'teacher_view_broadsheet'),
('65', 'teacher_view_conducts'),
('73', 'teacher_view_published_result'),
('63', 'teacher_view_registered_subjects'),
('60', 'teacher_view_students'),
('70', 'teacher_view_upload'),
('77', 'termly_result'),
('29', 'transfer_list'),
('88', 'update_invoice'),
('42', 'update_subject_rank'),
('87', 'upload_lesson_plans'),
('79', 'view_annual_published_result'),
('33', 'view_attendance_records'),
('44', 'view_class_broadsheet'),
('11', 'view_class_members'),
('31', 'view_conducts'),
('122', 'view_curriculum'),
('127', 'view_fee_payment'),
('86', 'view_invoice'),
('114', 'view_my_payment'),
('46', 'view_published_result'),
('37', 'view_registered_subjects'),
('20', 'view_school_info'),
('7', 'view_students'),
('115', 'view_student_fees'),
('35', 'view_subject'),
('25', 'view_unused_pins'),
('23', 'view_uploaded_pins'),
('43', 'view_uploaded_results'),
('24', 'view_used_pins');

-- --------------------------------------------------------
-- Table structure for table `pin1`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `pin1`;
CREATE TABLE `pin1` (
  `id` int NOT NULL,
  `term` varchar(20) DEFAULT NULL,
  `first_term_pin` varchar(100) DEFAULT NULL,
  `serial_number` varchar(50) DEFAULT NULL,
  `upload_date` varchar(20) DEFAULT NULL,
  `upload_time` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `pin2`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `pin2`;
CREATE TABLE `pin2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `term` varchar(20) DEFAULT NULL,
  `second_term_pin` varchar(100) DEFAULT NULL,
  `serial_number` varchar(50) DEFAULT NULL,
  `upload_date` varchar(100) DEFAULT NULL,
  `upload_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `pin3`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `pin3`;
CREATE TABLE `pin3` (
  `id` int NOT NULL AUTO_INCREMENT,
  `term` varchar(20) DEFAULT NULL,
  `third_term_pin` varchar(50) DEFAULT NULL,
  `serial_number` varchar(50) DEFAULT NULL,
  `upload_date` varchar(50) DEFAULT NULL,
  `upload_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `portal_request`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `portal_request`;
CREATE TABLE `portal_request` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `school_name` varchar(200) DEFAULT NULL,
  `position_held` varchar(150) DEFAULT NULL,
  `email` varchar(200) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `message` text,
  `request_date` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `positions`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `positions`;
CREATE TABLE `positions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reg_number` varchar(100) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `class` varchar(50) DEFAULT NULL,
  `term` varchar(50) DEFAULT NULL,
  `session` varchar(50) DEFAULT NULL,
  `students_sub_total` int DEFAULT NULL,
  `students_sub_average` float(11,3) DEFAULT NULL,
  `class_position` int DEFAULT NULL,
  `class_teacher_comment` text,
  `head_teacher_comment` text,
  `upload_date` varchar(20) DEFAULT NULL,
  `upload_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `questions`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `questions`;
CREATE TABLE `questions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `exam_id` int DEFAULT NULL,
  `question_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `question_image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `question_type` enum('theory','mcq') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `option_a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `option_b` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `option_c` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `option_d` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `correct_answer` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `marks` float DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `results1`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `results1`;
CREATE TABLE `results1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `class` varchar(50) DEFAULT NULL,
  `term` varchar(50) DEFAULT NULL,
  `session` varchar(50) DEFAULT NULL,
  `reg_number` varchar(50) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `subjects` varchar(100) DEFAULT NULL,
  `ca1` int DEFAULT NULL,
  `ca2` int DEFAULT NULL,
  `exam` int DEFAULT NULL,
  `subject_total` int DEFAULT NULL,
  `subject_rank` int DEFAULT NULL,
  `date_of_upload` varchar(20) DEFAULT NULL,
  `time_of_upload` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_synced` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_results1_regclasssessub` (`reg_number`,`class`,`session`,`subjects`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `role`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `role_type` enum('staff','student') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'staff',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping seed data for table `role`
INSERT INTO `role` VALUES
('3', 'School admin', 'staff'),
('4', 'class teacher', 'staff'),
('11', 'super admin', 'staff'),
('12', 'student', 'student'),
('13', 'subject teacher', 'staff'),
('14', 'Exam Officer', 'staff'),
('17', 'Accountant', 'staff');

-- --------------------------------------------------------
-- Table structure for table `role_permissions`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `role_permissions`;
CREATE TABLE `role_permissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role_id` int NOT NULL,
  `permission_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `role_id` (`role_id`),
  KEY `permission_id` (`permission_id`),
  CONSTRAINT `role_permissions_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE CASCADE,
  CONSTRAINT `role_permissions_ibfk_2` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping seed data for table `role_permissions`
INSERT INTO `role_permissions` VALUES
('15', '11', '5'),
('20', '3', '7'),
('21', '11', '7'),
('32', '12', '8'),
('34', '12', '11'),
('39', '12', '12'),
('40', '12', '13'),
('41', '12', '14'),
('50', '12', '15'),
('61', '3', '10'),
('62', '11', '10'),
('66', '11', '51'),
('67', '11', '52'),
('68', '12', '17'),
('69', '12', '18'),
('73', '11', '34'),
('74', '3', '55'),
('75', '11', '55'),
('77', '3', '16'),
('79', '11', '16'),
('80', '13', '16'),
('82', '13', '57'),
('91', '3', '30'),
('92', '11', '30'),
('93', '11', '31'),
('96', '3', '2'),
('97', '11', '2'),
('100', '3', '40'),
('101', '11', '40'),
('102', '3', '41'),
('103', '11', '41'),
('104', '3', '48'),
('105', '11', '48'),
('106', '3', '32'),
('107', '11', '32'),
('108', '3', '28'),
('109', '11', '28'),
('110', '11', '22'),
('111', '11', '49'),
('112', '11', '19'),
('113', '11', '50'),
('114', '11', '27'),
('115', '11', '26'),
('116', '3', '45'),
('117', '11', '45'),
('118', '3', '21'),
('119', '11', '21'),
('120', '3', '36'),
('121', '11', '36'),
('122', '3', '38'),
('123', '11', '38'),
('124', '3', '53'),
('125', '11', '53'),
('126', '3', '39'),
('127', '11', '39'),
('128', '3', '29'),
('129', '11', '29'),
('130', '3', '24'),
('131', '11', '24'),
('132', '3', '43'),
('133', '11', '43'),
('134', '3', '23'),
('135', '11', '23'),
('136', '3', '25'),
('137', '11', '25'),
('138', '3', '35'),
('139', '11', '35'),
('140', '3', '20'),
('141', '11', '20'),
('142', '3', '42'),
('143', '11', '42'),
('144', '3', '33'),
('145', '11', '33'),
('146', '3', '44'),
('147', '11', '44'),
('148', '3', '46'),
('149', '11', '46'),
('150', '3', '37'),
('151', '11', '37'),
('161', '13', '60'),
('165', '3', '75'),
('166', '11', '75'),
('172', '3', '78'),
('173', '11', '78'),
('174', '3', '79'),
('175', '11', '79'),
('177', '3', '81'),
('178', '11', '81'),
('179', '3', '82'),
('180', '11', '82'),
('181', '3', '91'),
('182', '11', '91'),
('183', '11', '106'),
('184', '3', '87'),
('185', '11', '87'),
('187', '12', '111'),
('188', '13', '111'),
('189', '11', '107'),
('190', '3', '104'),
('191', '11', '104'),
('192', '11', '112'),
('193', '11', '113'),
('194', '12', '114'),
('195', '11', '115'),
('196', '11', '116'),
('197', '11', '117'),
('198', '11', '118'),
('199', '11', '119'),
('200', '11', '96'),
('201', '11', '95'),
('202', '11', '92'),
('203', '11', '97'),
('204', '11', '105'),
('205', '11', '86'),
('206', '3', '4'),
('207', '11', '4'),
('208', '11', '99'),
('209', '11', '120'),
('210', '11', '90'),
('211', '11', '121'),
('213', '11', '122'),
('214', '12', '122'),
('215', '3', '123'),
('217', '11', '123'),
('218', '12', '123'),
('219', '13', '123'),
('222', '11', '103'),
('223', '11', '102'),
('224', '11', '101'),
('225', '3', '100'),
('227', '11', '100'),
('228', '13', '100'),
('229', '3', '98'),
('230', '11', '98'),
('231', '3', '94'),
('232', '11', '94'),
('233', '3', '93'),
('234', '11', '93'),
('240', '3', '84'),
('241', '11', '84'),
('242', '3', '85'),
('244', '11', '85'),
('247', '3', '74'),
('249', '11', '74'),
('250', '13', '74'),
('255', '3', '77'),
('257', '11', '77'),
('258', '12', '77'),
('259', '13', '77'),
('263', '13', '63'),
('265', '13', '64'),
('267', '13', '65'),
('272', '3', '54'),
('274', '11', '54'),
('275', '13', '54'),
('279', '11', '58'),
('280', '13', '58'),
('282', '13', '56'),
('283', '12', '9'),
('288', '3', '1'),
('290', '11', '1'),
('291', '12', '1'),
('292', '13', '1'),
('293', '3', '6'),
('294', '11', '6'),
('296', '13', '68'),
('298', '13', '71'),
('300', '13', '69'),
('302', '13', '61'),
('304', '13', '62'),
('312', '3', '47'),
('313', '11', '47'),
('314', '12', '47'),
('315', '3', '125'),
('316', '11', '125'),
('317', '3', '126'),
('318', '11', '126'),
('319', '3', '127'),
('320', '11', '127'),
('321', '11', '128'),
('322', '3', '129'),
('323', '11', '129'),
('324', '3', '136'),
('326', '11', '136'),
('327', '13', '136'),
('331', '3', '135'),
('332', '11', '135'),
('333', '3', '130'),
('334', '11', '130'),
('335', '3', '124'),
('336', '11', '124'),
('337', '3', '134'),
('338', '11', '134'),
('339', '3', '133'),
('341', '11', '133'),
('342', '12', '133'),
('343', '3', '131'),
('344', '11', '131'),
('345', '3', '132'),
('347', '11', '132'),
('348', '12', '132'),
('349', '3', '89'),
('350', '11', '89'),
('351', '3', '88'),
('352', '11', '88'),
('353', '3', '83'),
('354', '11', '83'),
('355', '3', '80'),
('357', '11', '80'),
('420', '4', '133'),
('421', '4', '61'),
('422', '4', '62'),
('423', '4', '56'),
('424', '4', '64'),
('425', '4', '65'),
('426', '4', '7'),
('427', '4', '80'),
('428', '4', '76'),
('429', '4', '74'),
('430', '4', '68'),
('431', '4', '69'),
('432', '4', '71'),
('433', '4', '66'),
('434', '4', '67'),
('435', '4', '72'),
('436', '4', '73'),
('437', '4', '77'),
('438', '4', '111'),
('439', '4', '136'),
('440', '4', '57'),
('441', '4', '58'),
('442', '4', '59'),
('443', '4', '63'),
('444', '4', '132'),
('445', '4', '100'),
('446', '4', '85'),
('447', '4', '35'),
('448', '4', '1'),
('449', '4', '123'),
('450', '13', '132'),
('451', '4', '134'),
('452', '12', '134'),
('453', '13', '134'),
('456', '3', '137'),
('457', '11', '137'),
('458', '14', '137'),
('459', '3', '138'),
('460', '4', '138'),
('461', '11', '138'),
('462', '13', '138'),
('463', '14', '44'),
('464', '13', '72'),
('465', '17', '106'),
('466', '17', '103'),
('467', '17', '135'),
('468', '17', '102'),
('469', '17', '101'),
('470', '17', '84'),
('471', '17', '49'),
('472', '17', '112'),
('473', '17', '113'),
('474', '17', '128'),
('475', '17', '116'),
('476', '17', '120'),
('477', '17', '99'),
('478', '17', '50'),
('479', '17', '98'),
('480', '17', '97'),
('481', '17', '83'),
('482', '17', '93'),
('483', '17', '91'),
('484', '17', '111'),
('485', '17', '90'),
('486', '17', '88'),
('487', '17', '127'),
('488', '17', '86'),
('489', '17', '114'),
('490', '17', '53'),
('491', '17', '134'),
('492', '17', '126'),
('493', '17', '117'),
('494', '17', '119'),
('495', '11', '139'),
('496', '3', '139'),
('497', '11', '140'),
('498', '3', '140'),
('499', '11', '141'),
('500', '3', '141');

-- --------------------------------------------------------
-- Table structure for table `school_subject_levels`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `school_subject_levels`;
CREATE TABLE `school_subject_levels` (
  `id` int NOT NULL AUTO_INCREMENT,
  `subject` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `level` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `upload_date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping seed data for table `school_subject_levels`
INSERT INTO `school_subject_levels` VALUES
('19', 'MATHEMATICS', 'SENIOR SECTION', '2025-07-06'),
('20', 'ENGLISH LANGUAGE', 'SENIOR SECTION', '2025-07-06'),
('21', 'BIOLOGY', 'SENIOR SECTION', '2025-07-06'),
('22', 'CHEMISTRY', 'SENIOR SECTION', '2025-07-06'),
('23', 'PHYSICS', 'SENIOR SECTION', '2025-07-06'),
('24', 'ECONOMICS', 'SENIOR SECTION', '2025-07-06'),
('25', 'LITERATURE IN ENGLISH', 'SENIOR SECTION', '2025-07-06'),
('26', 'GEOGRAHPY', 'SENIOR SECTION', '2025-07-06'),
('27', 'GOVERNMENT', 'SENIOR SECTION', '2025-07-06'),
('28', 'FINANCIAL ACCOUNTING', 'SENIOR SECTION', '2025-07-06'),
('29', 'COMMERCE', 'SENIOR SECTION', '2025-07-06'),
('30', 'AGRICULTURAL SCIENCE', 'SENIOR SECTION', '2025-07-06'),
('31', 'TECHNICAL DRAWING', 'SENIOR SECTION', '2025-07-06'),
('32', 'FOOD AND NUTRITION', 'SENIOR SECTION', '2025-07-06'),
('33', 'VISUAL ARTS', 'SENIOR SECTION', '2025-07-06'),
('34', 'CATERING CRAFT', 'SENIOR SECTION', '2025-07-06'),
('35', 'INFORMATION TECHNOLOGY', 'SENIOR SECTION', '2025-07-06'),
('36', 'DICTION', 'SENIOR SECTION', '2025-07-06'),
('37', 'CIVIC EDUCATION', 'SENIOR SECTION', '2025-07-06'),
('38', 'CHRISTIAN RELIGIOUS STUDIES', 'SENIOR SECTION', '2025-07-06'),
('39', 'YORUBA LANGUAGE', 'SENIOR SECTION', '2025-07-06'),
('40', 'FURTHER MATHEMATICS', 'SENIOR SECTION', '2025-07-06'),
('41', 'MARKETING', 'SENIOR SECTION', '2025-07-06'),
('42', 'MATHEMATICS', 'UPPER BASIC', '2025-07-06'),
('43', 'ENGLISH LANGUAGE', 'UPPER BASIC', '2025-07-06'),
('44', 'BASICS SCIENCE', 'UPPER BASIC', '2025-07-06'),
('45', 'AGRICULTURAL SCIENCE', 'UPPER BASIC', '2025-07-06'),
('46', 'BASIC TECHNOLOGY', 'UPPER BASIC', '2025-07-06'),
('47', 'INFORMATION TECHNOLOGY', 'UPPER BASIC', '2025-07-06'),
('48', 'SOCIAL STUDIES', 'UPPER BASIC', '2025-07-06'),
('49', 'CIVIC EDUCATION', 'UPPER BASIC', '2025-07-06'),
('50', 'CHRISTIAN RELIGIOUS STUDIES', 'UPPER BASIC', '2025-07-06'),
('51', 'PHYSICAL HEALTH EDUCATION', 'UPPER BASIC', '2025-07-06'),
('52', 'HOME ECONOMICS', 'UPPER BASIC', '2025-07-06'),
('53', 'CULTURAL AND CREATIVE ART', 'UPPER BASIC', '2025-07-06'),
('54', 'MUSIC', 'UPPER BASIC', '2025-07-06'),
('55', 'BUSINESS STUDIES', 'UPPER BASIC', '2025-07-06'),
('56', 'LITERATURE IN ENGLISH', 'UPPER BASIC', '2025-07-06'),
('57', 'SECURITY EDUCATION', 'UPPER BASIC', '2025-07-06'),
('58', 'FRENCH LANGUAGE', 'UPPER BASIC', '2025-07-06'),
('59', 'IGBO LANGUAGE', 'UPPER BASIC', '2025-07-06'),
('60', 'DICTION', 'UPPER BASIC', '2025-07-06'),
('62', 'AGRICULTURAL SCIENCE', 'PRIMARY', '2025-07-10'),
('63', 'MATHEMATICS', 'PRIMARY', '2025-07-10'),
('64', 'BASICS SCIENCE', 'PRIMARY', '2025-07-10'),
('65', 'SOCIAL STUDIES', 'PRIMARY', '2025-07-10'),
('66', 'IGBO LANGUAGE', 'PRIMARY', '2025-07-10'),
('67', 'YORUBA LANGUAGE', 'PRIMARY', '2025-07-10'),
('68', 'FRENCH LANGUAGE', 'PRIMARY', '2025-07-10'),
('69', 'DICTION', 'PRIMARY', '2025-07-10'),
('70', 'PHYSICAL HEALTH EDUCATION', 'PRIMARY', '2025-07-10'),
('71', 'SECURITY EDUCATION', 'PRIMARY', '2025-07-10'),
('72', 'CULTURAL AND CREATIVE ART', 'PRIMARY', '2025-07-10'),
('73', 'FINE ARTS', 'PRIMARY', '2025-07-10'),
('74', 'QUANTITATIVE REASONING', 'PRIMARY', '2025-07-10'),
('75', 'VERBAL REASONING', 'PRIMARY', '2025-07-10'),
('76', 'HOME ECONOMICS', 'PRIMARY', '2025-07-10'),
('77', 'HAND WRITING', 'PRIMARY', '2025-07-10'),
('78', 'COMPUTER', 'PRIMARY', '2025-07-10'),
('79', 'DICTION', 'NURSERY', '2025-07-10'),
('80', 'HAND WRITING', 'NURSERY', '2025-07-10'),
('81', 'NUMBER WORK', 'NURSERY', '2025-07-10'),
('82', 'LETTER WORK', 'NURSERY', '2025-07-10'),
('83', 'SCIENCE', 'NURSERY', '2025-07-10'),
('84', 'SOCIAL NORMS', 'NURSERY', '2025-07-10'),
('85', 'RHYMES', 'NURSERY', '2025-07-10'),
('86', 'CHRISTIAN RELIGIOUS KNOWLEDGE', 'NURSERY', '2025-07-10'),
('87', 'HEALTH HABITS', 'NURSERY', '2025-07-10'),
('88', 'GRACE AND COURTESY', 'NURSERY', '2025-07-10'),
('89', 'PRACTICAL LIFE ACTIVITIES', 'NURSERY', '2025-07-10'),
('90', 'FINE ARTS', 'NURSERY', '2025-07-10'),
('91', 'YORUBA LANGUAGE', 'UPPER BASIC', '2025-07-22'),
('92', 'PHYSICAL HEALTH EDUCATION', 'NURSERY', '2025-07-28'),
('93', 'SHAPES AND COLOURS', 'NURSERY', '2025-07-28'),
('94', 'ENGLISH LANGUAGE', 'PRIMARY', '2025-07-28'),
('95', 'CHRISTIAN RELIGIOUS STUDIES', 'PRIMARY', '2025-07-28'),
('96', 'CIVIC EDUCATION', 'PRIMARY', '2025-07-28'),
('97', 'HISTORY', 'PRIMARY', '2025-07-28'),
('98', 'SCRIBBLING', 'NURSERY', '2025-08-02'),
('99', 'COMPUTER', 'NURSERY', '2025-09-17'),
('100', 'SOCIAL HABITS', 'NURSERY', '2025-09-17'),
('101', 'NIGERIAN HISTORY', 'PRIMARY', '2025-09-17'),
('102', 'SOCIAL AND CITIZENSHIP STUDIES', 'PRIMARY', '2025-09-17'),
('103', 'SOCIAL AND CITIZENSHIP STUDIES', 'UPPER BASIC', '2025-09-17'),
('104', 'INTERMEDIATE SCIENCE', 'UPPER BASIC', '2025-09-17'),
('105', 'THEMATICS', 'NURSERY', '2025-09-17'),
('106', 'THEMATICS', 'PRIMARY', '2025-09-17'),
('107', 'THEMATICS', 'UPPER BASIC', '2025-09-17'),
('108', 'QUANTITATIVE REASONING', 'NURSERY', '2025-09-17'),
('109', 'VERBAL REASONING', 'NURSERY', '2025-09-17'),
('110', 'CULTURAL AND CREATIVE ART', 'NURSERY', '2025-09-17'),
('111', 'CURRENT AFFAIRS', 'NURSERY', '2025-09-17'),
('112', 'ISLAMIC RELIGIOUS STUDIES', 'UPPER BASIC', '2025-09-25'),
('113', 'DIGITAL TECHNOLOGY', 'UPPER BASIC', '2025-09-25'),
('114', 'ENGLISH STUDIES', 'PRIMARY', '2025-09-25'),
('115', 'ENGLISH STUDIES', 'UPPER BASIC', '2025-09-25'),
('116', 'YORUBA', 'UPPER BASIC', '2025-09-25'),
('117', 'NIGERIAN HISTORY', 'UPPER BASIC', '2025-09-25'),
('118', 'BASIC SCIENCE AND TECHNOLOGY', 'PRIMARY', '2025-09-25'),
('119', 'ISLAMIC RELIGIOUS STUDIES', 'PRIMARY', '2025-09-25'),
('120', 'YORUBA', 'PRIMARY', '2025-09-25'),
('121', 'PRE-VOCATION STUDIES', 'PRIMARY', '2025-09-26'),
('122', 'BASIC DIGITAL LITERACY', 'PRIMARY', '2025-09-26');

-- --------------------------------------------------------
-- Table structure for table `school_subjects`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `school_subjects`;
CREATE TABLE `school_subjects` (
  `id` int NOT NULL AUTO_INCREMENT,
  `subjects` varchar(111) DEFAULT NULL,
  `level` varchar(50) DEFAULT NULL,
  `date_saved` varchar(111) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping seed data for table `school_subjects`
INSERT INTO `school_subjects` VALUES
('1', 'ENGLISH LANGUAGE', NULL, '12-Oct-2024'),
('2', 'MATHEMATICS', NULL, '12-Oct-2024'),
('5', 'BASICS SCIENCE', NULL, '12-Oct-2024'),
('6', 'AGRICULTURAL SCIENCE', NULL, '12-Oct-2024'),
('7', 'BASIC TECHNOLOGY', NULL, '12-Oct-2024'),
('8', 'INFORMATION TECHNOLOGY', NULL, '12-Oct-2024'),
('9', 'SOCIAL STUDIES', NULL, '12-Oct-2024'),
('10', 'CIVIC EDUCATION', NULL, '12-Oct-2024'),
('12', 'CHRISTIAN RELIGIOUS STUDIES', NULL, '12-Oct-2024'),
('14', 'PHYSICAL HEALTH EDUCATION', NULL, '12-Oct-2024'),
('15', 'HOME ECONOMICS', NULL, '12-Oct-2024'),
('16', 'CULTURAL AND CREATIVE ART', NULL, '12-Oct-2024'),
('17', 'MUSIC', NULL, '12-Oct-2024'),
('18', 'BUSINESS STUDIES', NULL, '12-Oct-2024'),
('19', 'LITERATURE IN ENGLISH', NULL, '12-Oct-2024'),
('21', 'MARKETING', NULL, '12-Oct-2024'),
('22', 'LITERATURE IN ENGLISH', NULL, '12-Oct-2024'),
('23', 'SECURITY EDUCATION', NULL, '12-Oct-2024'),
('24', 'FRENCH LANGUAGE', NULL, '12-Oct-2024'),
('27', 'BIOLOGY', NULL, '13-Nov-2024'),
('28', 'CHEMISTRY', NULL, '25-Feb-2025'),
('29', 'PHYSICS', NULL, '28-May-2025'),
('30', 'ECONOMICS', NULL, '06/Jul/2025'),
('31', 'GEOGRAHPY', NULL, '06/Jul/2025'),
('32', 'GOVERNMENT', NULL, '06/Jul/2025'),
('33', 'FINANCIAL ACCOUNTING', NULL, '06/Jul/2025'),
('34', 'COMMERCE', NULL, '06/Jul/2025'),
('35', 'TECHNICAL DRAWING', NULL, '06/Jul/2025'),
('36', 'FOOD AND NUTRITION', NULL, '06/Jul/2025'),
('37', 'VISUAL ARTS', NULL, '06/Jul/2025'),
('38', 'CATERING CRAFT', NULL, '06/Jul/2025'),
('39', 'YORUBA LANGUAGE', NULL, '06/Jul/2025'),
('40', 'FURTHER MATHEMATICS', NULL, '06/Jul/2025'),
('41', 'RHYMES', NULL, '08/Jul/2025'),
('42', 'FINE ARTS', NULL, '10/Jul/2025'),
('43', 'QUANTITATIVE REASONING', NULL, '10/Jul/2025'),
('44', 'VERBAL REASONING', NULL, '10/Jul/2025'),
('45', 'HAND WRITING', NULL, '10/Jul/2025'),
('46', 'NUMBER WORK', NULL, '10/Jul/2025'),
('47', 'LETTER WORK', NULL, '10/Jul/2025'),
('48', 'SCIENCE', NULL, '10/Jul/2025'),
('49', 'SOCIAL HABITS', NULL, '10/Jul/2025'),
('50', 'HEALTH HABITS', NULL, '10/Jul/2025'),
('51', 'CHRISTIAN RELIGIOUS KNOWLEDGE', NULL, '10/Jul/2025'),
('52', 'GRACE AND COURTESY', NULL, '10/Jul/2025'),
('53', 'PRACTICAL LIFE ACTIVITIES', NULL, '10/Jul/2025'),
('54', 'COMPUTER', NULL, '10/Jul/2025'),
('55', 'SHAPES AND COLOURS', NULL, '28/Jul/2025'),
('56', 'HISTORY', NULL, '28/Jul/2025'),
('57', 'SCRIBBLING', NULL, '02/Aug/2025'),
('58', 'ISLAMIC RELIGIOUS STUDIES', NULL, '13/Sep/2025'),
('59', 'CREATIVE ART', NULL, '17/Sep/2025'),
('60', 'CURRENT AFFAIRS', NULL, '17/Sep/2025'),
('62', 'ENGLISH PHONICS', NULL, '17/Sep/2025'),
('63', 'DICTION', NULL, '17/Sep/2025'),
('64', 'BASIC SCIENCE AND TECHNOLOGY', NULL, '17/Sep/2025'),
('65', 'DIGITAL LITERACY', NULL, '17/Sep/2025'),
('66', 'NIGERIAN HISTORY', NULL, '17/Sep/2025'),
('67', 'CREATIVE AND CULTURAL ART', NULL, '17/Sep/2025'),
('68', 'PRE-VOCATION STUDIES', NULL, '17/Sep/2025'),
('69', 'SOCIAL AND CITIZENSHIP STUDIES', NULL, '17/Sep/2025'),
('70', 'INTERMEDIATE SCIENCE', NULL, '17/Sep/2025'),
('71', 'TRADE SUBJECT', NULL, '17/Sep/2025'),
('73', 'DIGITAL TECHNOLOGY', NULL, '25/Sep/2025'),
('74', 'ENGLISH STUDIES', NULL, '25/Sep/2025'),
('75', 'YORUBA', NULL, '25/Sep/2025'),
('76', 'BASIC DIGITAL LITERACY', NULL, '26/Sep/2025');

-- --------------------------------------------------------
-- Table structure for table `sent_sms`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `sent_sms`;
CREATE TABLE `sent_sms` (
  `id` int NOT NULL AUTO_INCREMENT,
  `senderID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recipients` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `response` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sent_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `sessions`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sessions` varchar(20) DEFAULT NULL,
  `date_added` varchar(50) DEFAULT NULL,
  `time_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping seed data for table `sessions`
INSERT INTO `sessions` VALUES
('1', '2024/2025', '12-Oct-2024', '2024-10-12 09:16:33'),
('2', '2028/2029', '21-May-2025', '2025-05-21 20:50:08'),
('3', '2026/2027', '21-May-2025', '2025-05-21 20:50:24'),
('4', '2027/2028', '21-May-2025', '2025-05-21 20:52:41'),
('5', '2025/2026', '02/Aug/2025', '2025-08-02 18:47:24');

-- --------------------------------------------------------
-- Table structure for table `settings`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `use_scratch_card` tinyint DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping seed data for table `settings`
INSERT INTO `settings` VALUES
('1', '0');

-- --------------------------------------------------------
-- Table structure for table `sms_settings`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `sms_settings`;
CREATE TABLE `sms_settings` (
  `token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `senderID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping seed data for table `sms_settings`
INSERT INTO `sms_settings` VALUES
('XgaMeqicK7V3W0CYr4BGyfo926zPdRAlmtTSFL5Jvx1ujDwZbUQkOEHpIs8nhN', 'Highlands');

-- --------------------------------------------------------
-- Table structure for table `sss`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `sss`;
CREATE TABLE `sss` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sss_subjects` varchar(100) DEFAULT NULL,
  `upload_date` varchar(50) DEFAULT NULL,
  `upload_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping seed data for table `sss`
INSERT INTO `sss` VALUES
('1', 'BIOLOGY', '12-Oct-2024', '2024-10-12 09:59:27'),
('2', 'GEOGRAPHY', '12-Oct-2024', '2024-10-12 09:59:32'),
('3', 'GOVERNMENT', '12-Oct-2024', '2024-10-12 09:59:36'),
('4', 'FURTHER MATHEMATICS', '12-Oct-2024', '2024-10-12 09:59:39'),
('5', 'FINANCIAL ACCOUNTING', '12-Oct-2024', '2024-10-12 09:59:45'),
('6', 'ENGLISH LANGUAGE', '12-Oct-2024', '2024-10-12 09:59:48'),
('7', 'ECONOMICS', '12-Oct-2024', '2024-10-12 09:59:51'),
('8', 'COMMERCE', '12-Oct-2024', '2024-10-12 09:59:56'),
('9', 'CIVIC EDUCATION', '12-Oct-2024', '2024-10-12 10:00:01'),
('10', 'CHEMISTRY', '12-Oct-2024', '2024-10-12 10:00:04'),
('11', 'PHYSICS', '12-Oct-2024', '2024-10-12 10:00:15'),
('13', 'IRS/CRS', '12-Oct-2024', '2024-10-12 10:00:30'),
('14', 'LITERATURE IN ENGLISH', '12-Oct-2024', '2024-10-12 10:00:41'),
('15', 'MARKETING', '12-Oct-2024', '2024-10-12 10:00:47'),
('16', 'MATHEMATICS', '12-Oct-2024', '2024-10-12 10:01:07'),
('17', 'ICT', '25-Feb-2025', '2025-02-25 12:43:09');

-- --------------------------------------------------------
-- Table structure for table `sss_subject_number`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `sss_subject_number`;
CREATE TABLE `sss_subject_number` (
  `id` int NOT NULL AUTO_INCREMENT,
  `number_of_subject` int DEFAULT NULL,
  `declared_date` varchar(20) DEFAULT NULL,
  `declared_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping seed data for table `sss_subject_number`
INSERT INTO `sss_subject_number` VALUES
('1', '6', '30-Oct-2024', '2024-10-30 16:10:50');

-- --------------------------------------------------------
-- Table structure for table `staff`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `dob` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `lga` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `state` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `country` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `passport` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `sex` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `date_registered` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `role` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_staff_role` (`role`),
  CONSTRAINT `fk_staff_role` FOREIGN KEY (`role`) REFERENCES `role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Staff informations';

-- Dumping seed data for table `staff`
INSERT INTO `staff` (`id`, `username`, `password`, `name`, `dob`, `lga`, `state`, `country`, `passport`, `address`, `sex`, `phone`, `email`, `status`, `date_registered`, `role`) VALUES
(1, 'admin', '$2y$10$WnAtl/J3oYH4Knr5/HM39eaHki8x1LubsSAZSJ4rpqN.Xdh8ylYSq', 'System Administrator', '1990-01-01', 'Headquarters', 'Federal', 'Nigeria', 'none.jpg', 'School Campus', 'Male', '+234 800 000 0000', 'admin@school.com', 'Active', '2026-01-01', 11);

-- --------------------------------------------------------
-- Table structure for table `staff_attendance`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `staff_attendance`;
CREATE TABLE `staff_attendance` (
  `id` int NOT NULL AUTO_INCREMENT,
  `staff_id` int NOT NULL,
  `date` date NOT NULL,
  `time_in` time NOT NULL,
  `time_out` time DEFAULT NULL,
  `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'Late',
  `is_offline` tinyint(1) DEFAULT '0',
  `sync_time` datetime DEFAULT NULL,
  `lat` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lng` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `staff_id` (`staff_id`),
  KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `staff_attendance_log`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `staff_attendance_log`;
CREATE TABLE `staff_attendance_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `name` varchar(200) NOT NULL,
  `scanned_by` varchar(100) NOT NULL DEFAULT 'system',
  `scan_at` datetime NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `scan_lat` decimal(10,7) DEFAULT NULL,
  `scan_lng` decimal(10,7) DEFAULT NULL,
  `geo_distance_m` int DEFAULT NULL,
  `result` enum('accepted','rejected','warned') DEFAULT 'accepted',
  `reason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_scan_at` (`scan_at`),
  KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `staff_payroll`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `staff_payroll`;
CREATE TABLE `staff_payroll` (
  `id` int NOT NULL,
  `staff_name` varchar(250) DEFAULT NULL,
  `salary` int DEFAULT NULL,
  `surcharge` int DEFAULT NULL,
  `bonus` int DEFAULT NULL,
  `allowance` int DEFAULT NULL,
  `tax` int DEFAULT NULL,
  `pension` int DEFAULT NULL,
  `net_salary` int DEFAULT NULL,
  `date` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `student_answers`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `student_answers`;
CREATE TABLE `student_answers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `attempt_id` int DEFAULT NULL,
  `question_id` int DEFAULT NULL,
  `answer_given` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `student_attendance`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `student_attendance`;
CREATE TABLE `student_attendance` (
  `id` int NOT NULL,
  `reg_number` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `date` varchar(50) DEFAULT NULL,
  `term` varchar(20) DEFAULT NULL,
  `session` varchar(20) DEFAULT NULL,
  `class` varchar(20) DEFAULT NULL,
  `roll_call` varchar(20) DEFAULT NULL,
  `is_synced` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `student_exam_attempts`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `student_exam_attempts`;
CREATE TABLE `student_exam_attempts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `exam_id` int DEFAULT NULL,
  `student_id` int DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `score` float DEFAULT NULL,
  `remarks` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `student_exam_sessions`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `student_exam_sessions`;
CREATE TABLE `student_exam_sessions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `exam_id` int NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_student_exam` (`student_id`,`exam_id`),
  KEY `exam_id` (`exam_id`),
  CONSTRAINT `student_exam_sessions_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  CONSTRAINT `student_exam_sessions_ibfk_2` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `students`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `id` int NOT NULL AUTO_INCREMENT,
  `gender` varchar(20) DEFAULT NULL,
  `firstname` varchar(50) DEFAULT NULL,
  `othername` varchar(50) DEFAULT NULL,
  `lastname` varchar(50) DEFAULT NULL,
  `dob` varchar(50) DEFAULT NULL,
  `height` int DEFAULT NULL,
  `weight` int DEFAULT NULL,
  `club` varchar(50) DEFAULT NULL,
  `health_conditions` varchar(200) DEFAULT NULL,
  `address` text,
  `guardian_name` varchar(150) DEFAULT NULL,
  `guardian_occupation` varchar(150) DEFAULT NULL,
  `guardian_phone` varchar(20) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `lga` varchar(200) DEFAULT NULL,
  `nationality` varchar(50) DEFAULT NULL,
  `class` varchar(20) DEFAULT NULL,
  `level` varchar(50) DEFAULT NULL,
  `reg_number` varchar(100) DEFAULT NULL,
  `role` int DEFAULT NULL,
  `gen_password` varchar(200) DEFAULT NULL,
  `passport` varchar(255) DEFAULT NULL,
  `date_of_reg` varchar(20) DEFAULT NULL,
  `time_of_reg` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `malpractice_count` int DEFAULT '0',
  `flagged` tinyint(1) DEFAULT '0',
  `status` enum('active','archived','suspended','blocked') DEFAULT 'active',
  `is_synced` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `subject_number`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `subject_number`;
CREATE TABLE `subject_number` (
  `id` int NOT NULL AUTO_INCREMENT,
  `level_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `no_of_subjects` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `subject_number_by_level`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `subject_number_by_level`;
CREATE TABLE `subject_number_by_level` (
  `id` int NOT NULL AUTO_INCREMENT,
  `level` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `number_of_subject` int NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping seed data for table `subject_number_by_level`
INSERT INTO `subject_number_by_level` VALUES
('1', 'PRIMARY', '2', '2025-06-25 02:45:26'),
('2', 'UPPER BASIC', '10', '2025-06-25 01:23:47'),
('3', 'SENIOR SECTION', '10', '2025-06-25 01:55:11'),
('4', 'NURSERY', '6', '2025-06-25 01:55:21');

-- --------------------------------------------------------
-- Table structure for table `subject_teacher`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `subject_teacher`;
CREATE TABLE `subject_teacher` (
  `id` int NOT NULL AUTO_INCREMENT,
  `staff_id` int NOT NULL,
  `subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `class` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `term` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `staff_id` (`staff_id`),
  CONSTRAINT `subject_teacher_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `subjects`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `subjects`;
CREATE TABLE `subjects` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reg_number` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `class` varchar(20) DEFAULT NULL,
  `subjects` varchar(100) DEFAULT NULL,
  `term` varchar(50) DEFAULT NULL,
  `session` varchar(50) DEFAULT NULL,
  `registration_date` varchar(20) DEFAULT NULL,
  `registration_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `system_settings`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `system_settings`;
CREATE TABLE `system_settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `show_position` tinyint(1) DEFAULT '1',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `show_subject_position` tinyint(1) NOT NULL DEFAULT '1',
  `show_class_position` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping seed data for table `system_settings`
INSERT INTO `system_settings` VALUES
('1', '1', '2025-09-29 06:37:29', '0', '0');

-- --------------------------------------------------------
-- Table structure for table `teacher_classes`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `teacher_classes`;
CREATE TABLE `teacher_classes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `teacher_id` int NOT NULL,
  `class` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `term` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `teacher_id` (`teacher_id`),
  CONSTRAINT `teacher_classes_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `staff` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `transfer_certificate`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `transfer_certificate`;
CREATE TABLE `transfer_certificate` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_name` varchar(50) NOT NULL,
  `parent_name` varchar(50) NOT NULL,
  `state` varchar(50) NOT NULL,
  `lga` varchar(50) NOT NULL,
  `dob` varchar(50) NOT NULL,
  `reg_number` varchar(50) NOT NULL,
  `last_class` varchar(50) NOT NULL,
  `section` varchar(255) NOT NULL,
  `last_class_date` varchar(50) NOT NULL,
  `current_class` varchar(50) NOT NULL,
  `last_att_date` varchar(50) NOT NULL,
  `indebtedness` varchar(50) NOT NULL,
  `conduct` varchar(255) NOT NULL,
  `cause_of_leaving` varchar(255) NOT NULL,
  `date_issued` varchar(50) NOT NULL,
  `passport` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `unused_pins`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `unused_pins`;
CREATE TABLE `unused_pins` (
  `id` int NOT NULL AUTO_INCREMENT,
  `term` varchar(50) DEFAULT NULL,
  `unused_pins` varchar(100) DEFAULT NULL,
  `serial_number` varchar(100) DEFAULT NULL,
  `upload_date` varchar(30) DEFAULT NULL,
  `upload_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

-- --------------------------------------------------------
-- Table structure for table `used_pins`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `used_pins`;
CREATE TABLE `used_pins` (
  `id` int NOT NULL AUTO_INCREMENT,
  `used_pins` varchar(50) DEFAULT NULL,
  `user_name` varchar(50) NOT NULL,
  `user_reg_number` varchar(50) DEFAULT NULL,
  `used_count` int DEFAULT NULL,
  `user_class` varchar(50) DEFAULT NULL,
  `used_term` varchar(50) DEFAULT NULL,
  `used_session` varchar(50) DEFAULT NULL,
  `date_used` varchar(50) DEFAULT NULL,
  `time_used` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Empty table ready for new records

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
-- End of EdumacFlo Seed Database
