-- =====================================================
-- PMSS Admin Panel — MariaDB/MySQL Schema + Data Dump
-- Regenerated from live DB via REST API
-- Generated: 2026-04-18T08:29:45.658415Z
-- Compatible with MariaDB 10.4+ / MySQL 5.7+
-- =====================================================

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

-- ----------------------------------------
-- Table: admin_users
-- ----------------------------------------
DROP TABLE IF EXISTS `admin_users`;
CREATE TABLE `admin_users` (`id` BIGINT NOT NULL AUTO_INCREMENT, `full_name` VARCHAR(100) NOT NULL, `username` VARCHAR(50) NOT NULL, `password` VARCHAR(255) NOT NULL, `role` VARCHAR(20) NOT NULL DEFAULT 'admin', `user_image` VARCHAR(255) NOT NULL DEFAULT 'assets/images/user-profile.jpg', `token` VARCHAR(255) DEFAULT NULL, `last_activity` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, `email` VARCHAR(255) DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `admin_users` (`id`, `full_name`, `username`, `password`, `role`, `user_image`, `token`, `last_activity`, `email`, `created_at`) VALUES (17, 'Imtiaz Khan Munna', 'admin', '$2y$10$9MLoYeETLvn8Mqi8udfeVO4dhEkE/1rSzlz0/U3VS9w.36..0/8nq', 'super', 'assets/images/user-profile.jpg', 'cbbbd4add3825f3509f7dfe6d3b457727fca9a53037bddd551f68795f86826a2', '2026-03-27 04:11:30', NULL, '2026-03-27 04:11:30');
INSERT INTO `admin_users` (`id`, `full_name`, `username`, `password`, `role`, `user_image`, `token`, `last_activity`, `email`, `created_at`) VALUES (16, 'Munna', 'admin6', '$2y$10$vk2vCMyMaES5hdJWq9Gxa.5geNJtX7aFKffPY6m5qUf0PxzX3.97i', 'user', 'assets/images/user-profile.jpg', 'e13c2cf53e5ef62e0123193031fedde81e65ec61a20df4f94daae71e99f4682a', '2026-03-27 02:15:18', NULL, '2026-03-27 02:15:18');
INSERT INTO `admin_users` (`id`, `full_name`, `username`, `password`, `role`, `user_image`, `token`, `last_activity`, `email`, `created_at`) VALUES (15, 'Admin Four', 'admin4', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'assets/images/user-profile.jpg', 'c3d4e5f67890123456789abcdef0123456789abcdef0123456789abcdef2', '2026-03-27 01:00:59', 'admin4@purbomodukhaliss.edu.bd', '2026-03-27 01:00:59');
INSERT INTO `admin_users` (`id`, `full_name`, `username`, `password`, `role`, `user_image`, `token`, `last_activity`, `email`, `created_at`) VALUES (14, 'Admin Three', 'admin3', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'assets/images/user-profile.jpg', 'b8d1ddfbdc03332f086785b00ed93503d55bbb71e135bfd3ed661307b19ae862', '2026-03-27 01:00:59', 'admin3@purbomodukhaliss.edu.bd', '2026-03-27 01:00:59');
INSERT INTO `admin_users` (`id`, `full_name`, `username`, `password`, `role`, `user_image`, `token`, `last_activity`, `email`, `created_at`) VALUES (13, 'Admin Two', 'admin2', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super', 'assets/images/user-profile.jpg', 'a28ec8ca142aa3aa996ff05d2c021ea8ffd33a7a089db710bfebcd4bbd2b3446', '2026-03-27 01:00:59', 'admin2@purbomodukhaliss.edu.bd', '2026-03-27 01:00:59');

-- ----------------------------------------
-- Table: students
-- ----------------------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (`id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255), `class` INT, `division` VARCHAR(50), `session` INT, `roll` INT, `gender` VARCHAR(20), `religion` VARCHAR(50), `dob` DATE, `blood_group` VARCHAR(10), `phone` VARCHAR(30), `email` VARCHAR(255), `father_name` VARCHAR(255), `mother_name` VARCHAR(255), `guardian_phone` VARCHAR(30), `address` TEXT, `image` VARCHAR(500), `birth_id` VARCHAR(50), `national_id` VARCHAR(50), `section` VARCHAR(20), `shift` VARCHAR(20), `year` INT, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `students` (`id`, `name`, `class`, `division`, `session`, `roll`, `gender`, `religion`, `dob`, `blood_group`, `phone`, `email`, `father_name`, `mother_name`, `guardian_phone`, `address`, `image`, `birth_id`, `national_id`, `section`, `shift`, `year`) VALUES (53, 'Imtiaz Khan Munna', 10, 'Humanities', 2026, 101, 'Male', 'Muslim', '1993-08-26', 'A+', '01719974170', 'imtiazkhanmunna@yahoo.com', 'Md. Nashir Uddin Khan', 'Kohinur Begum', '01760054364', 'Khan Bari Road , Safakhali', 'https://purbomodukhaliss.edu.bd/assets/images/students-img/533f0622b4ab00fd_1776493196.jpg', '1491495063', '1491495063', NULL, NULL, NULL);

-- ----------------------------------------
-- Table: teachers
-- ----------------------------------------
DROP TABLE IF EXISTS `teachers`;
CREATE TABLE `teachers` (`id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `designation` VARCHAR(255), `department` VARCHAR(255), `email` VARCHAR(255), `phone` VARCHAR(30), `dob` DATE, `gender` VARCHAR(20), `religion` VARCHAR(50), `blood_group` VARCHAR(10), `address` TEXT, `image` VARCHAR(500), `join_date` DATE, `nationality` VARCHAR(50), `nid_passport` VARCHAR(50), `pds_id` VARCHAR(50), `education` TEXT, `training` TEXT, `certifications` TEXT, `publications` TEXT, `achievements` TEXT, `awards` TEXT, `hobbies` TEXT, `comments` TEXT, `facebook` VARCHAR(255), `linkedin` VARCHAR(255), PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- (no rows in teachers)

-- ----------------------------------------
-- Table: school_staff
-- ----------------------------------------
DROP TABLE IF EXISTS `school_staff`;
CREATE TABLE `school_staff` (`id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `designation` VARCHAR(255), `email` VARCHAR(255), `phone` VARCHAR(30), `dob` DATE, `gender` VARCHAR(20), `religion` VARCHAR(50), `blood_group` VARCHAR(10), `address` TEXT, `image` VARCHAR(500), `join_date` DATE, `nid_passport` VARCHAR(50), `facebook` VARCHAR(255), `linkedin` VARCHAR(255), `status` TINYINT DEFAULT 1, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- (no rows in school_staff)

-- ----------------------------------------
-- Table: subjects
-- ----------------------------------------
DROP TABLE IF EXISTS `subjects`;
CREATE TABLE `subjects` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `subject_name` VARCHAR(255) NOT NULL,
  `subject_code` VARCHAR(50) NULL,
  `class` INT NULL,
  `group_name` VARCHAR(50) NULL,
  `is_optional` TINYINT DEFAULT 0,
  `is_combined` TINYINT DEFAULT 0,
  -- Multi-paper support (e.g. Bangla 1st/2nd, English 1st/2nd)
  `has_multiple_papers` TINYINT DEFAULT 0,
  `paper_type` ENUM('single','paper1','paper2') DEFAULT 'single',
  -- Component flags (kept for backward compatibility)
  `has_mcq` TINYINT DEFAULT 0,
  `has_practical` TINYINT DEFAULT 0,
  `has_ca` TINYINT DEFAULT 0,
  -- Aggregate marks (legacy — still used by existing code)
  `full_marks` INT DEFAULT 100,
  `pass_marks` INT DEFAULT 33,
  -- Granular marks per component
  `total_marks` INT DEFAULT 100,
  `written_marks` INT DEFAULT 70,
  `mcq_marks` INT DEFAULT 30,
  `practical_marks` INT DEFAULT 0,
  `ca_marks` INT DEFAULT 0,
  `pass_marks_written` INT DEFAULT 23,
  `pass_marks_mcq` INT DEFAULT 10,
  `pass_marks_practical` INT DEFAULT 0,
  `pass_marks_ca` INT DEFAULT 0,
  `status` TINYINT DEFAULT 1,
  PRIMARY KEY (id),
  KEY idx_subjects_class_group (class, group_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- (no rows in subjects)

-- ----------------------------------------
-- Table: exams
-- ----------------------------------------
DROP TABLE IF EXISTS `exams`;
CREATE TABLE `exams` (`id` INT NOT NULL AUTO_INCREMENT, `exam_name` VARCHAR(255) NOT NULL, `class` INT, `session` INT, `year` INT, `exam_date` DATE, `status` VARCHAR(20), `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `exams` (`id`, `exam_name`, `class`, `session`, `year`, `exam_date`, `status`, `created_at`) VALUES (11, 'HSC', NULL, NULL, NULL, NULL, 'upcoming', '2026-04-18 02:35:50');
INSERT INTO `exams` (`id`, `exam_name`, `class`, `session`, `year`, `exam_date`, `status`, `created_at`) VALUES (10, 'SSC', NULL, NULL, NULL, NULL, 'upcoming', '2026-04-18 02:35:50');
INSERT INTO `exams` (`id`, `exam_name`, `class`, `session`, `year`, `exam_date`, `status`, `created_at`) VALUES (9, 'Yearly', NULL, NULL, NULL, NULL, 'upcoming', '2026-04-18 02:35:50');
INSERT INTO `exams` (`id`, `exam_name`, `class`, `session`, `year`, `exam_date`, `status`, `created_at`) VALUES (8, 'Half Yearly', NULL, NULL, NULL, NULL, 'upcoming', '2026-04-18 02:35:50');
INSERT INTO `exams` (`id`, `exam_name`, `class`, `session`, `year`, `exam_date`, `status`, `created_at`) VALUES (7, 'Test Exam', NULL, NULL, NULL, NULL, 'upcoming', '2026-04-18 02:35:50');
INSERT INTO `exams` (`id`, `exam_name`, `class`, `session`, `year`, `exam_date`, `status`, `created_at`) VALUES (6, 'Pre Test Exam', NULL, NULL, NULL, NULL, 'upcoming', '2026-04-18 02:35:50');
INSERT INTO `exams` (`id`, `exam_name`, `class`, `session`, `year`, `exam_date`, `status`, `created_at`) VALUES (5, 'Class Test', NULL, NULL, NULL, NULL, 'upcoming', '2026-04-18 02:35:50');
INSERT INTO `exams` (`id`, `exam_name`, `class`, `session`, `year`, `exam_date`, `status`, `created_at`) VALUES (4, 'Mid Term Exam', NULL, NULL, NULL, NULL, 'upcoming', '2026-04-18 02:35:50');
INSERT INTO `exams` (`id`, `exam_name`, `class`, `session`, `year`, `exam_date`, `status`, `created_at`) VALUES (3, 'Annual Exam', NULL, NULL, NULL, NULL, 'upcoming', '2026-04-18 02:35:50');
INSERT INTO `exams` (`id`, `exam_name`, `class`, `session`, `year`, `exam_date`, `status`, `created_at`) VALUES (2, '2nd Term Exam', NULL, NULL, NULL, NULL, 'upcoming', '2026-04-18 02:35:50');
INSERT INTO `exams` (`id`, `exam_name`, `class`, `session`, `year`, `exam_date`, `status`, `created_at`) VALUES (1, '1st Term Exam', NULL, NULL, NULL, NULL, 'upcoming', '2026-04-18 02:35:50');

-- ----------------------------------------
-- Table: groups
-- ----------------------------------------
DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (`id` INT NOT NULL AUTO_INCREMENT, `group_name` VARCHAR(100) NOT NULL, `description` TEXT, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `groups` (`id`, `group_name`, `description`, `created_at`) VALUES (NULL, 'Humanities', NULL, NULL);

-- ----------------------------------------
-- Table: gallery
-- ----------------------------------------
DROP TABLE IF EXISTS `gallery`;
CREATE TABLE `gallery` (`id` INT NOT NULL AUTO_INCREMENT, `title` VARCHAR(255) NOT NULL, `type` VARCHAR(20) DEFAULT 'image', `url` VARCHAR(500) NOT NULL, `thumbnail` VARCHAR(500), `status` VARCHAR(20) DEFAULT 'active', `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- (no rows in gallery)

-- ----------------------------------------
-- Table: noticeboard
-- ----------------------------------------
DROP TABLE IF EXISTS `noticeboard`;
CREATE TABLE `noticeboard` (`id` INT NOT NULL AUTO_INCREMENT, `title` VARCHAR(255) NOT NULL, `content` TEXT NOT NULL, `category` VARCHAR(50) DEFAULT 'general', `file_path` VARCHAR(500), `file_attachments` LONGTEXT, `order_index` INT DEFAULT 0, `status` TINYINT DEFAULT 1, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `noticeboard` (`id`, `title`, `content`, `category`, `file_path`, `file_attachments`, `order_index`, `status`, `created_at`, `updated_at`) VALUES (116, 'Math Exam', 'Math exam for class 10 will be held on 5th April.', 'exam', NULL, '[]', 2, 1, '2026-03-27 01:20:57', NULL);
INSERT INTO `noticeboard` (`id`, `title`, `content`, `category`, `file_path`, `file_attachments`, `order_index`, `status`, `created_at`, `updated_at`) VALUES (115, 'Holiday on Friday', 'School will be closed on Friday.', 'holiday', NULL, '[]', 1, 1, '2026-03-27 01:20:57', NULL);

-- ----------------------------------------
-- Table: breaking_news
-- ----------------------------------------
DROP TABLE IF EXISTS `breaking_news`;
CREATE TABLE `breaking_news` (`id` INT NOT NULL AUTO_INCREMENT, `title` VARCHAR(500) NOT NULL, `link` VARCHAR(500), `priority` VARCHAR(20), `expiry` DATE, `status` TINYINT DEFAULT 1, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `breaking_news` (`id`, `title`, `link`, `priority`, `expiry`, `status`, `created_at`) VALUES (22, 'Sports event announced', '#', 'normal', '2026-04-14 20:00:00', 1, '2026-03-27 01:20:57');
INSERT INTO `breaking_news` (`id`, `title`, `link`, `priority`, `expiry`, `status`, `created_at`) VALUES (21, 'New library books', '#', 'normal', NULL, 1, '2026-03-27 01:20:57');

-- ----------------------------------------
-- Table: committee
-- ----------------------------------------
DROP TABLE IF EXISTS `committee`;
CREATE TABLE `committee` (`id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `designation` VARCHAR(255) NOT NULL, `image` VARCHAR(500) NOT NULL, `phone` VARCHAR(30), `email` VARCHAR(255), `facebook` VARCHAR(255), `linkedin` VARCHAR(255), `join_date` DATE NOT NULL, `end_date` DATE NOT NULL, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `committee` (`id`, `name`, `designation`, `image`, `phone`, `email`, `facebook`, `linkedin`, `join_date`, `end_date`, `created_at`, `updated_at`) VALUES (20, 'মোঃ তানভীর হোসেন', 'সদস্য', 'img-10.png', '01711000010', 'tanvir@example.com', 'https://facebook.com/tanvir', 'https://linkedin.com/in/tanvir', '2022-09-01', '2025-09-01', '2025-10-10 16:18:16', '2025-10-10 18:27:39');
INSERT INTO `committee` (`id`, `name`, `designation`, `image`, `phone`, `email`, `facebook`, `linkedin`, `join_date`, `end_date`, `created_at`, `updated_at`) VALUES (19, 'মোঃ মনিরুজ্জামান', 'সদস্য', 'img-9.png', '01711000009', 'monir@example.com', 'https://facebook.com/monir', 'https://linkedin.com/in/monir', '2022-08-01', '2025-08-01', '2025-10-10 16:18:16', '2025-10-10 16:18:16');
INSERT INTO `committee` (`id`, `name`, `designation`, `image`, `phone`, `email`, `facebook`, `linkedin`, `join_date`, `end_date`, `created_at`, `updated_at`) VALUES (18, 'মোছাঃ সুমি আক্তার', 'সদস্য', 'img-8.png', '01711000008', 'sumi@example.com', 'https://facebook.com/sumi', 'https://linkedin.com/in/sumi', '2022-07-01', '2025-07-01', '2025-10-10 16:18:16', '2025-10-10 16:18:16');
INSERT INTO `committee` (`id`, `name`, `designation`, `image`, `phone`, `email`, `facebook`, `linkedin`, `join_date`, `end_date`, `created_at`, `updated_at`) VALUES (17, 'মোঃ শহিদুল ইসলাম', 'সদস্য', 'img-7.png', '01711000007', 'shahid@example.com', 'https://facebook.com/shahid', 'https://linkedin.com/in/shahid', '2022-06-01', '2025-06-01', '2025-10-10 16:18:16', '2025-10-10 16:18:16');
INSERT INTO `committee` (`id`, `name`, `designation`, `image`, `phone`, `email`, `facebook`, `linkedin`, `join_date`, `end_date`, `created_at`, `updated_at`) VALUES (16, 'মোঃ নাসির উদ্দিন', 'সাংগঠনিক সম্পাদক', 'img-6.png', '01711000006', 'nasir@example.com', 'https://facebook.com/nasir', 'https://linkedin.com/in/nasir', '2022-05-01', '2025-05-01', '2025-10-10 16:18:16', '2025-10-10 16:18:16');
INSERT INTO `committee` (`id`, `name`, `designation`, `image`, `phone`, `email`, `facebook`, `linkedin`, `join_date`, `end_date`, `created_at`, `updated_at`) VALUES (15, 'মোঃ আজিজুল হক', 'অর্থ সম্পাদক', 'img-5.png', '01711000005', 'aziz@example.com', 'https://facebook.com/aziz', 'https://linkedin.com/in/aziz', '2022-04-05', '2025-04-05', '2025-10-10 16:18:16', '2025-10-10 16:18:16');
INSERT INTO `committee` (`id`, `name`, `designation`, `image`, `phone`, `email`, `facebook`, `linkedin`, `join_date`, `end_date`, `created_at`, `updated_at`) VALUES (14, 'মোঃ কামরুল হাসান', 'সহ-সাধারণ সম্পাদক', 'img-4.png', '01711000004', 'kamrul@example.com', 'https://facebook.com/kamrul', 'https://linkedin.com/in/kamrul', '2022-03-10', '2025-03-10', '2025-10-10 16:18:16', '2025-10-10 16:18:16');
INSERT INTO `committee` (`id`, `name`, `designation`, `image`, `phone`, `email`, `facebook`, `linkedin`, `join_date`, `end_date`, `created_at`, `updated_at`) VALUES (13, 'মোঃ শফিকুল ইসলাম', 'সাধারণ সম্পাদক', 'img-3.png', '01711000003', 'shafik@example.com', 'https://facebook.com/shafik', 'https://linkedin.com/in/shafik', '2022-03-01', '2025-03-01', '2025-10-10 16:18:16', '2025-10-10 16:18:16');
INSERT INTO `committee` (`id`, `name`, `designation`, `image`, `phone`, `email`, `facebook`, `linkedin`, `join_date`, `end_date`, `created_at`, `updated_at`) VALUES (12, 'মোছাঃ রুবিনা আক্তার', 'সহ-সভাপতি', 'img-2.png', '01711000002', 'rubina@example.com', 'https://facebook.com/rubina', 'https://linkedin.com/in/rubina', '2022-02-01', '2025-02-01', '2025-10-10 16:18:16', '2025-10-10 16:18:16');
INSERT INTO `committee` (`id`, `name`, `designation`, `image`, `phone`, `email`, `facebook`, `linkedin`, `join_date`, `end_date`, `created_at`, `updated_at`) VALUES (11, 'মোঃ আব্দুল করিম', 'সভাপতি', 'img-1.png', '01711000001', 'karim@example.com', 'https://facebook.com/karim', 'https://linkedin.com/in/karim', '2022-01-15', '2025-01-15', '2025-10-10 16:18:16', '2025-10-10 18:27:30');

-- ----------------------------------------
-- Table: best_students
-- ----------------------------------------
DROP TABLE IF EXISTS `best_students`;
CREATE TABLE `best_students` (`id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `roll_no` INT NOT NULL, `exam_name` VARCHAR(255) NOT NULL, `year` INT NOT NULL, `achievement` VARCHAR(255) NOT NULL, `image` VARCHAR(500) DEFAULT '', `student_id` INT, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `best_students` (`id`, `name`, `roll_no`, `exam_name`, `year`, `achievement`, `image`, `student_id`) VALUES (10, 'Maya Sultana', 110, 'SSC', 2025, 'GPA-5', '', NULL);
INSERT INTO `best_students` (`id`, `name`, `roll_no`, `exam_name`, `year`, `achievement`, `image`, `student_id`) VALUES (9, 'Rashed Khan', 109, 'SSC', 2025, 'GPA-5', '', NULL);
INSERT INTO `best_students` (`id`, `name`, `roll_no`, `exam_name`, `year`, `achievement`, `image`, `student_id`) VALUES (8, 'Tania Rahman', 108, 'SSC', 2025, 'GPA-5', '', NULL);
INSERT INTO `best_students` (`id`, `name`, `roll_no`, `exam_name`, `year`, `achievement`, `image`, `student_id`) VALUES (7, 'Al Amin', 107, 'SSC', 2025, 'GPA-5', '', NULL);
INSERT INTO `best_students` (`id`, `name`, `roll_no`, `exam_name`, `year`, `achievement`, `image`, `student_id`) VALUES (6, 'Nusrat Jahan', 106, 'SSC', 2025, 'GPA-5', '', NULL);

-- ----------------------------------------
-- Table: app_settings
-- ----------------------------------------
DROP TABLE IF EXISTS `app_settings`;
CREATE TABLE `app_settings` (`id` INT NOT NULL DEFAULT 1, `school_name` VARCHAR(255), `school_logo` VARCHAR(500), `admin_email` VARCHAR(255), `theme_preference` VARCHAR(20) DEFAULT 'light', `default_session` INT, `class_config` TEXT, `notifications_enabled` TINYINT NOT NULL DEFAULT 1, `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `app_settings` (`id`, `school_name`, `school_logo`, `admin_email`, `theme_preference`, `default_session`, `class_config`, `notifications_enabled`, `updated_at`) VALUES ('1', 'PMSS', NULL, NULL, 'light', '2026', '6,7,8,9,10', '1', '2026-04-17 10:07:09');

-- ----------------------------------------
-- Table: internal_exam_results
-- ----------------------------------------
DROP TABLE IF EXISTS `internal_exam_results`;
CREATE TABLE `internal_exam_results` (`id` INT NOT NULL AUTO_INCREMENT, `exam_name` VARCHAR(255) NOT NULL, `class_name` VARCHAR(100) NOT NULL, `year` INT NOT NULL, `total_students` INT DEFAULT 0, `total_passed` INT DEFAULT 0, `total_failed` INT DEFAULT 0, `pass_rate` DECIMAL(5,2) DEFAULT 0, `publication_date` DATE, `download_url` VARCHAR(500), PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (160, 'নির্বাচনী পরীক্ষা', 'ষষ্ঠ', 2025, 60, 48, 12, '80.00', '2025-12-25', 'results/নির্বাচনী_পরীক্ষা_ষষ্ঠ_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (159, 'নির্বাচনী পরীক্ষা', 'সপ্তম', 2025, 68, 54, 14, '79.41', '2025-12-22', 'results/নির্বাচনী_পরীক্ষা_সপ্তম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (158, 'নির্বাচনী পরীক্ষা', 'অষ্টম', 2025, 75, 60, 15, '80.00', '2025-12-20', 'results/নির্বাচনী_পরীক্ষা_অষ্টম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (157, 'নির্বাচনী পরীক্ষা', 'নবম', 2025, 72, 58, 14, '80.56', '2025-12-18', 'results/নির্বাচনী_পরীক্ষা_নবম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (156, 'নির্বাচনী পরীক্ষা', 'দশম', 2025, 80, 65, 15, '81.25', '2025-12-15', 'results/নির্বাচনী_পরীক্ষা_দশম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (155, 'প্রাক-নির্বাচনী পরীক্ষা', 'ষষ্ঠ', 2025, 60, 48, 12, '80.00', '2025-12-10', 'results/প্রাক-নির্বাচনী_পরীক্ষা_ষষ্ঠ_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (154, 'প্রাক-নির্বাচনী পরীক্ষা', 'সপ্তম', 2025, 65, 52, 13, '80.00', '2025-12-07', 'results/প্রাক-নির্বাচনী_পরীক্ষা_সপ্তম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (153, 'প্রাক-নির্বাচনী পরীক্ষা', 'অষ্টম', 2025, 68, 54, 14, '79.41', '2025-12-05', 'results/প্রাক-নির্বাচনী_পরীক্ষা_অষ্টম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (152, 'প্রাক-নির্বাচনী পরীক্ষা', 'নবম', 2025, 70, 55, 15, '78.57', '2025-12-03', 'results/প্রাক-নির্বাচনী_পরীক্ষা_নবম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (151, 'প্রাক-নির্বাচনী পরীক্ষা', 'দশম', 2025, 75, 60, 15, '80.00', '2025-12-01', 'results/প্রাক-নির্বাচনী_পরীক্ষা_দশম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (150, 'বার্ষিক পরীক্ষা', 'ষষ্ঠ', 2025, 65, 52, 13, '80.00', '2025-11-10', 'results/বার্ষিক_পরীক্ষা_ষষ্ঠ_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (149, 'বার্ষিক পরীক্ষা', 'সপ্তম', 2025, 70, 56, 14, '80.00', '2025-11-07', 'results/বার্ষিক_পরীক্ষা_সপ্তম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (148, 'বার্ষিক পরীক্ষা', 'অষ্টম', 2025, 80, 65, 15, '81.25', '2025-11-05', 'results/বার্ষিক_পরীক্ষা_অষ্টম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (147, 'বার্ষিক পরীক্ষা', 'নবম', 2025, 78, 63, 15, '80.77', '2025-11-03', 'results/বার্ষিক_পরীক্ষা_নবম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (146, 'বার্ষিক পরীক্ষা', 'দশম', 2025, 85, 70, 15, '82.35', '2025-11-01', 'results/বার্ষিক_পরীক্ষা_দশম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (145, 'অর্ধ-বার্ষিক পরীক্ষা', 'ষষ্ঠ', 2025, 60, 48, 12, '80.00', '2025-08-15', 'results/অর্ধ-বার্ষিক_পরীক্ষা_ষষ্ঠ_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (144, 'অর্ধ-বার্ষিক পরীক্ষা', 'সপ্তম', 2025, 68, 54, 14, '79.41', '2025-08-12', 'results/অর্ধ-বার্ষিক_পরীক্ষা_সপ্তম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (143, 'অর্ধ-বার্ষিক পরীক্ষা', 'অষ্টম', 2025, 75, 60, 15, '80.00', '2025-08-10', 'results/অর্ধ-বার্ষিক_পরীক্ষা_অষ্টম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (142, 'অর্ধ-বার্ষিক পরীক্ষা', 'নবম', 2025, 72, 58, 14, '80.56', '2025-08-08', 'results/অর্ধ-বার্ষিক_পরীক্ষা_নবম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (141, 'অর্ধ-বার্ষিক পরীক্ষা', 'দশম', 2025, 80, 65, 15, '81.25', '2025-08-05', 'results/অর্ধ-বার্ষিক_পরীক্ষা_দশম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (140, '২য় সাময়িক পরীক্ষা', 'ষষ্ঠ', 2025, 65, 52, 13, '80.00', '2025-05-20', 'results/২য়_সাময়িক_পরীক্ষা_ষষ্ঠ_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (139, '২য় সাময়িক পরীক্ষা', 'সপ্তম', 2025, 74, 60, 14, '81.08', '2025-05-18', 'results/২য়_সাময়িক_পরীক্ষা_সপ্তম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (138, '২য় সাময়িক পরীক্ষা', 'অষ্টম', 2025, 82, 67, 15, '81.71', '2025-05-15', 'results/২য়_সাময়িক_পরীক্ষা_অষ্টম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (137, '২য় সাময়িক পরীক্ষা', 'নবম', 2025, 70, 55, 15, '78.57', '2025-05-12', 'results/২য়_সাময়িক_পরীক্ষা_নবম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (136, '২য় সাময়িক পরীক্ষা', 'দশম', 2025, 78, 63, 15, '80.77', '2025-05-10', 'results/২য়_সাময়িক_পরীক্ষা_দশম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (135, '১ম সাময়িক পরীক্ষা', 'ষষ্ঠ', 2025, 60, 48, 12, '80.00', '2025-02-25', 'results/১ম_সাময়িক_পরীক্ষা_ষষ্ঠ_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (134, '১ম সাময়িক পরীক্ষা', 'সপ্তম', 2025, 72, 55, 17, '76.39', '2025-02-22', 'results/১ম_সাময়িক_পরীক্ষা_সপ্তম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (133, '১ম সাময়িক পরীক্ষা', 'অষ্টম', 2025, 80, 65, 15, '81.25', '2025-02-20', 'results/১ম_সাময়িক_পরীক্ষা_অষ্টম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (132, '১ম সাময়িক পরীক্ষা', 'নবম', 2025, 68, 50, 18, '73.53', '2025-02-18', 'results/১ম_সাময়িক_পরীক্ষা_নবম_2025.pdf');
INSERT INTO `internal_exam_results` (`id`, `exam_name`, `class_name`, `year`, `total_students`, `total_passed`, `total_failed`, `pass_rate`, `publication_date`, `download_url`) VALUES (131, '১ম সাময়িক পরীক্ষা', 'দশম', 2025, 75, 60, 15, '80.00', '2025-02-15', 'results/১ম_সাময়িক_পরীক্ষা_দশম_2025.pdf');

-- ----------------------------------------
-- Table: public_result
-- ----------------------------------------
DROP TABLE IF EXISTS `public_result`;
CREATE TABLE `public_result` (`id` INT NOT NULL AUTO_INCREMENT, `exam_name` VARCHAR(255) NOT NULL, `year` INT NOT NULL, `total_students` INT DEFAULT 0, `passed_boys` INT DEFAULT 0, `passed_girls` INT DEFAULT 0, `failed_boys` INT DEFAULT 0, `failed_girls` INT DEFAULT 0, `total_passed` INT DEFAULT 0, `total_failed` INT DEFAULT 0, `pass_rate` DECIMAL(5,2) DEFAULT 0, `gpa` DECIMAL(4,2) DEFAULT 0, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `public_result` (`id`, `exam_name`, `year`, `total_students`, `passed_boys`, `passed_girls`, `failed_boys`, `failed_girls`, `total_passed`, `total_failed`, `pass_rate`, `gpa`) VALUES (85, 'SSC', 2021, 4500, 2100, 2250, 50, 100, 4350, 150, '96.70', 110);
INSERT INTO `public_result` (`id`, `exam_name`, `year`, `total_students`, `passed_boys`, `passed_girls`, `failed_boys`, `failed_girls`, `total_passed`, `total_failed`, `pass_rate`, `gpa`) VALUES (84, 'SSC', 2022, 4600, 2150, 2250, 50, 150, 4400, 200, '95.60', 112);
INSERT INTO `public_result` (`id`, `exam_name`, `year`, `total_students`, `passed_boys`, `passed_girls`, `failed_boys`, `failed_girls`, `total_passed`, `total_failed`, `pass_rate`, `gpa`) VALUES (83, 'SSC', 2023, 4700, 2100, 2400, 50, 150, 4500, 200, '95.70', 110);
INSERT INTO `public_result` (`id`, `exam_name`, `year`, `total_students`, `passed_boys`, `passed_girls`, `failed_boys`, `failed_girls`, `total_passed`, `total_failed`, `pass_rate`, `gpa`) VALUES (82, 'SSC', 2024, 4800, 2200, 2400, 100, 100, 4600, 200, '95.80', 115);
INSERT INTO `public_result` (`id`, `exam_name`, `year`, `total_students`, `passed_boys`, `passed_girls`, `failed_boys`, `failed_girls`, `total_passed`, `total_failed`, `pass_rate`, `gpa`) VALUES (81, 'SSC', 2025, 5000, 2300, 2500, 200, 0, 4800, 200, '96.00', 120);

-- ----------------------------------------
-- Table: attendance_records (schema only)
-- ----------------------------------------
DROP TABLE IF EXISTS `attendance_records`;
CREATE TABLE `attendance_records` (
  id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, user_type VARCHAR(20) NOT NULL,
  date DATE NOT NULL, status VARCHAR(20), entry_time TIME, exit_time TIME,
  remarks TEXT, marked_by INT, device_id VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id), KEY idx_attn (user_id,user_type,date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------
-- Table: marks (schema only)
-- ----------------------------------------
DROP TABLE IF EXISTS `marks`;
CREATE TABLE `marks` (
  id INT NOT NULL AUTO_INCREMENT, student_id INT, exam_id INT, subject_id INT,
  paper_type VARCHAR(20), written DECIMAL(6,2), mcq DECIMAL(6,2),
  practical DECIMAL(6,2), ca DECIMAL(6,2), total DECIMAL(8,2), marks_obtained DECIMAL(8,2),
  grade VARCHAR(10), gpa DECIMAL(4,2), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------
-- Table: individual_result (schema only)
-- ----------------------------------------
DROP TABLE IF EXISTS `individual_result`;
CREATE TABLE `individual_result` (
  id INT AUTO_INCREMENT PRIMARY KEY, exam_name VARCHAR(255) NOT NULL,
  class VARCHAR(50) NOT NULL, group_name VARCHAR(100) NULL, session_year INT NOT NULL,
  student_id INT NOT NULL, student_name VARCHAR(255) NULL, roll INT NULL,
  subjects LONGTEXT NULL, total_marks DECIMAL(10,2) DEFAULT 0,
  gpa DECIMAL(4,2) DEFAULT 0, grade VARCHAR(10) DEFAULT 'F', result VARCHAR(10) DEFAULT 'FAIL',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_individual_result (exam_name, class, session_year, student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- (person_qr_codes table removed: QR tokens now live in students.qr_token,
--  teachers.qr_token, and school_staff.qr_token)


-- ----------------------------------------
-- Table: signatures (schema only)
-- ----------------------------------------
DROP TABLE IF EXISTS `signatures`;
CREATE TABLE `signatures` (
  id INT AUTO_INCREMENT PRIMARY KEY, person_name VARCHAR(255) NOT NULL,
  designation VARCHAR(255) NOT NULL, signature_image VARCHAR(500),
  signature_type VARCHAR(50), is_active TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------
-- Table: notifications (schema only)
-- ----------------------------------------
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL,
  message TEXT, category VARCHAR(50) DEFAULT 'system', actor VARCHAR(100),
  link VARCHAR(500), is_read TINYINT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------
-- Table: password_reset_requests (schema only)
-- ----------------------------------------
DROP TABLE IF EXISTS `password_reset_requests`;
CREATE TABLE `password_reset_requests` (
  id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL,
  email VARCHAR(255), phone VARCHAR(30), id_number VARCHAR(50),
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------
-- Table: student_invoices (schema only)
-- ----------------------------------------
DROP TABLE IF EXISTS `student_invoices`;
CREATE TABLE `student_invoices` (
  id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL,
  invoice_number VARCHAR(50) NOT NULL, invoice_date DATE NOT NULL DEFAULT (CURRENT_DATE),
  due_date DATE, items LONGTEXT, subtotal DECIMAL(10,2) DEFAULT 0,
  discount_type VARCHAR(20), discount_value DECIMAL(10,2) DEFAULT 0,
  total DECIMAL(10,2) DEFAULT 0, status VARCHAR(20) DEFAULT 'unpaid',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------
-- Table: contact_messages (schema only)
-- ----------------------------------------
DROP TABLE IF EXISTS `contact_messages`;
CREATE TABLE `contact_messages` (
  id INT AUTO_INCREMENT PRIMARY KEY, your_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL, phone VARCHAR(30) NOT NULL,
  subject VARCHAR(255) NOT NULL, message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------
-- Table: activity_log (schema only)
-- ----------------------------------------
DROP TABLE IF EXISTS `activity_log`;
CREATE TABLE `activity_log` (
  id BIGINT NOT NULL AUTO_INCREMENT, admin_id BIGINT NOT NULL,
  action VARCHAR(50) NOT NULL, module VARCHAR(100) NOT NULL,
  item_id BIGINT, details TEXT, ip_address VARCHAR(45),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------
-- Table: visitor_stats (schema only)
-- ----------------------------------------
DROP TABLE IF EXISTS `visitor_stats`;
CREATE TABLE `visitor_stats` (
  id INT AUTO_INCREMENT PRIMARY KEY, ip_address VARCHAR(45) NOT NULL,
  session_id VARCHAR(100) NOT NULL,
  visited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------
-- Removed: class_group_subjects
-- Class + group mapping is now stored directly on the `subjects` table
-- (subjects.class, subjects.group_name). Keeping the table out of fresh
-- installs; live databases should run the migration script in
-- api/migrations/2026_05_unify_subjects.sql to merge any existing data
-- and drop the legacy table safely.
-- ----------------------------------------

SET FOREIGN_KEY_CHECKS = 1;
-- End of dump