melabidi/alrahma_sunday_school_api
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
develop
alrahma_sunday_school_api/docs/test.sql
T
root 39228168c8
API CI/CD / Validate (composer + pint) (push) Successful in 3m14s
Details
API CI/CD / Test (PHPUnit) (push) Failing after 3m28s
Details
API CI/CD / Build frontend assets (push) Successful in 1m2s
Details
API CI/CD / Security audit (push) Failing after 56s
Details
API CI/CD / Deploy to shared hosting (PHP) (push) Has been skipped
Details
add school year and security fix
2026-07-06 00:55:01 -04:00

4246 lines
134 KiB
SQL
Raw Permalink Blame History

-- phpMyAdmin SQL Dump
-- version 5.2.1deb1+deb12u1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Mar 08, 2026 at 08:13 PM
-- Server version: 8.0.42
-- PHP Version: 8.2.30
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `test`
--
-- --------------------------------------------------------
--
-- Table structure for table `additional_charges`
--
CREATE TABLE `additional_charges` (
`id` int UNSIGNED NOT NULL,
`parent_id` int UNSIGNED DEFAULT NULL,
`invoice_id` int UNSIGNED DEFAULT NULL,
`school_year` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`semester` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`charge_type` enum('extra','previous') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'extra',
`title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`description` text COLLATE utf8mb4_general_ci,
`amount` decimal(10,2) NOT NULL,
`due_date` date DEFAULT NULL,
`status` enum('pending','applied','void') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pending',
`created_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `admin_notification_subjects`
--
CREATE TABLE `admin_notification_subjects` (
`id` int UNSIGNED NOT NULL,
`admin_id` int UNSIGNED NOT NULL,
`subject` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `attendance_comment_template`
--
CREATE TABLE `attendance_comment_template` (
`id` int UNSIGNED NOT NULL,
`min_score` decimal(5,2) NOT NULL,
`max_score` decimal(5,2) NOT NULL,
`template_text` text COLLATE utf8mb4_unicode_ci NOT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT '1',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `attendance_data`
--
CREATE TABLE `attendance_data` (
`id` int UNSIGNED NOT NULL,
`class_id` int NOT NULL,
`class_section_id` int NOT NULL,
`student_id` int NOT NULL,
`school_id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
`date` date NOT NULL,
`status` enum('present','absent','late') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'present',
`reason` text COLLATE utf8mb4_general_ci,
`is_reported` enum('no','yes') COLLATE utf8mb4_general_ci DEFAULT 'no',
`is_notified` enum('no','yes') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'no',
`semester` varchar(25) COLLATE utf8mb4_general_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci NOT NULL,
`modified_by` int DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `attendance_day`
--
CREATE TABLE `attendance_day` (
`id` int UNSIGNED NOT NULL,
`class_section_id` int NOT NULL,
`date` date NOT NULL,
`status` enum('draft','submitted','published') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft',
`submitted_by` int DEFAULT NULL,
`submitted_at` datetime DEFAULT NULL,
`published_by` int DEFAULT NULL,
`published_at` datetime DEFAULT NULL,
`auto_publish_at` datetime DEFAULT NULL,
`reopened_by` int DEFAULT NULL,
`reopened_at` datetime DEFAULT NULL,
`reopen_reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`semester` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `attendance_record`
--
CREATE TABLE `attendance_record` (
`id` int UNSIGNED NOT NULL,
`class_section_id` int NOT NULL,
`student_id` int NOT NULL,
`school_id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
`total_absence` int NOT NULL DEFAULT '0',
`total_late` int NOT NULL DEFAULT '0',
`total_presence` int NOT NULL DEFAULT '0',
`total_attendance` int NOT NULL DEFAULT '0',
`semester` varchar(25) COLLATE utf8mb4_general_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci NOT NULL,
`modified_by` int DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `attendance_tracking`
--
CREATE TABLE `attendance_tracking` (
`id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`date` datetime NOT NULL,
`is_reported` enum('no','yes') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'no',
`reason` text COLLATE utf8mb4_general_ci,
`note` text COLLATE utf8mb4_general_ci,
`is_notified` tinyint(1) NOT NULL DEFAULT '0',
`notif_counter` int UNSIGNED NOT NULL DEFAULT '0',
`semester` varchar(16) COLLATE utf8mb4_general_ci DEFAULT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `authorized_users`
--
CREATE TABLE `authorized_users` (
`id` int UNSIGNED NOT NULL,
`user_id` int NOT NULL,
`authorized_user_id` int DEFAULT NULL,
`firstname` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`lastname` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`phone_number` varchar(25) COLLATE utf8mb4_general_ci NOT NULL,
`gender` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`relation_to_user` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
`token` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL,
`status` enum('Pending','Active') COLLATE utf8mb4_general_ci DEFAULT 'Pending',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `badge_print_logs`
--
CREATE TABLE `badge_print_logs` (
`id` int UNSIGNED NOT NULL,
`user_id` int NOT NULL,
`printed_by` int DEFAULT NULL,
`school_year` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`printed_at` datetime NOT NULL,
`role` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`class_section_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`copies` int NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `calendar_events`
--
CREATE TABLE `calendar_events` (
`id` int UNSIGNED NOT NULL,
`title` varchar(255) NOT NULL,
`date` date NOT NULL,
`description` text,
`notify_parent` tinyint(1) NOT NULL DEFAULT '0',
`notify_admin` tinyint(1) NOT NULL DEFAULT '0',
`notify_teacher` tinyint(1) NOT NULL DEFAULT '0',
`no_school` tinyint(1) NOT NULL DEFAULT '0',
`semester` varchar(255) NOT NULL,
`school_year` varchar(9) DEFAULT NULL,
`notification_sent` tinyint(1) NOT NULL DEFAULT '0',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `chapters`
--
CREATE TABLE `chapters` (
`id` int UNSIGNED NOT NULL,
`chapter_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`class_section_id` int NOT NULL,
`school_year` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `classes`
--
CREATE TABLE `classes` (
`id` int UNSIGNED NOT NULL,
`class_name` varchar(100) NOT NULL,
`schedule` varchar(100) NOT NULL,
`capacity` int NOT NULL,
`semester` varchar(255) NOT NULL,
`school_year` varchar(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `classSection`
--
CREATE TABLE `classSection` (
`id` int UNSIGNED NOT NULL,
`class_id` int UNSIGNED NOT NULL,
`class_section_id` int UNSIGNED NOT NULL,
`class_section_name` varchar(100) NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`semester` varchar(255) NOT NULL,
`school_year` varchar(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `class_preparation_log`
--
CREATE TABLE `class_preparation_log` (
`id` int UNSIGNED NOT NULL,
`class_section_id` int UNSIGNED NOT NULL,
`class_section` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci NOT NULL,
`prep_data` text COLLATE utf8mb4_general_ci NOT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `class_prep_adjustments`
--
CREATE TABLE `class_prep_adjustments` (
`id` int UNSIGNED NOT NULL,
`class_section_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
`item_name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
`adjustment` int NOT NULL DEFAULT '0',
`adjustable` tinyint(1) NOT NULL DEFAULT '1',
`school_year` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `class_progress_attachments`
--
CREATE TABLE `class_progress_attachments` (
`id` int UNSIGNED NOT NULL,
`report_id` int UNSIGNED NOT NULL,
`file_path` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`original_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`mime_type` varchar(120) COLLATE utf8mb4_general_ci DEFAULT NULL,
`file_size` int UNSIGNED DEFAULT NULL,
`created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `class_progress_reports`
--
CREATE TABLE `class_progress_reports` (
`id` int UNSIGNED NOT NULL,
`class_section_id` int UNSIGNED NOT NULL,
`teacher_id` int UNSIGNED NOT NULL,
`week_start` date NOT NULL,
`week_end` date NOT NULL,
`subject` varchar(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`unit_title` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`materials` varchar(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`covered` text COLLATE utf8mb4_unicode_ci NOT NULL,
`homework` text COLLATE utf8mb4_unicode_ci,
`assessment` text COLLATE utf8mb4_unicode_ci,
`status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`status_notes` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`class_notes` text COLLATE utf8mb4_unicode_ci,
`next_week_plan` text COLLATE utf8mb4_unicode_ci NOT NULL,
`support_needed` text COLLATE utf8mb4_unicode_ci,
`flags_json` text COLLATE utf8mb4_unicode_ci,
`attachment_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `competitions`
--
CREATE TABLE `competitions` (
`id` int UNSIGNED NOT NULL,
`title` varchar(150) NOT NULL,
`semester` varchar(30) DEFAULT NULL,
`school_year` varchar(20) DEFAULT NULL,
`class_section_id` int DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
`winners_count` int NOT NULL DEFAULT '3',
`prize_per_point` decimal(10,2) NOT NULL DEFAULT '1.00',
`is_published` tinyint(1) NOT NULL DEFAULT '0',
`published_at` datetime DEFAULT NULL,
`created_by` int DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`deleted_at` datetime DEFAULT NULL,
`is_locked` tinyint(1) NOT NULL DEFAULT '0',
`locked_at` datetime DEFAULT NULL,
`locked_by` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `competition_class_winners`
--
CREATE TABLE `competition_class_winners` (
`id` int UNSIGNED NOT NULL,
`competition_id` int UNSIGNED NOT NULL,
`class_section_id` int NOT NULL,
`winners_override` int DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`question_count` int DEFAULT NULL,
`prize_1` decimal(10,2) DEFAULT NULL,
`prize_2` decimal(10,2) DEFAULT NULL,
`prize_3` decimal(10,2) DEFAULT NULL,
`prize_4` decimal(10,2) DEFAULT NULL,
`prize_5` decimal(10,2) DEFAULT NULL,
`prize_6` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `competition_scores`
--
CREATE TABLE `competition_scores` (
`id` int UNSIGNED NOT NULL,
`competition_id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`class_section_id` int DEFAULT NULL,
`score` decimal(6,2) NOT NULL DEFAULT '0.00',
`notes` varchar(255) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `competition_winners`
--
CREATE TABLE `competition_winners` (
`id` int UNSIGNED NOT NULL,
`competition_id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`class_section_id` int DEFAULT NULL,
`rank` int NOT NULL,
`score` decimal(6,2) NOT NULL DEFAULT '0.00',
`prize_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `configuration`
--
CREATE TABLE `configuration` (
`id` int UNSIGNED NOT NULL,
`config_key` varchar(100) NOT NULL,
`config_value` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
--
-- Dumping data for table `configuration`
--
INSERT INTO `configuration` (`id`, `config_key`, `config_value`) VALUES
(1, 'date_age_reference', '2025-12-31'),
(2, 'stickerWidth', '102'),
(3, 'stickerHeight', '34'),
(4, 'pageWidth', '216'),
(5, 'pageHeight', '280'),
(6, 'leftMargin', '4'),
(7, 'topMargin', '19'),
(8, 'horizontalGap', '5'),
(9, 'verticalGap', '0'),
(10, 'enable_attendance', '0'),
(11, 'due_date', '2025-09-21'),
(12, 'max_emergency', '1'),
(13, 'pass_score', '60'),
(14, 'weeks_study', '35'),
(15, 'last_school_day', '2026-06-01'),
(16, 'max_age', '18'),
(17, 'min_age', '5'),
(18, 'trophy_score', '94'),
(19, 'max_kids', '5'),
(20, 'cleanup_timeout', '900'),
(21, 'enrollment_deadline', '2025-10-06'),
(22, 'school_year', '2025-2026'),
(23, 'Youth_fee', '200'),
(24, 'second_student_fee', '220'),
(25, 'first_student_fee', '370'),
(26, 'total_semester1_days', '14'),
(27, 'semester', 'Spring'),
(28, 'refund_deadline', '2025-12-31'),
(29, 'total_semester2_days', '13'),
(30, 'delete_email_notify', 'support@alrahmaisgl.org, melabidi@alrahmaisgl.org'),
(31, 'fall_semester_start', '2025-09-21'),
(32, 'sandbox_secret_paypal', 'CHANGE_ME_SANDBOX_SECRET_PAYPAL'),
(33, 'sandbox_verifylink_paypal', 'https://api-m.sandbox.paypal.com/v1/notifications/verify-webhook-signature'),
(34, 'sandbox_tokenurl_paypal', 'https://api-m.sandbox.paypal.com/v1/oauth2/token'),
(35, 'sandbox_clientid_paypal', 'CHANGE_ME_SANDBOX_CLIENTID_PAYPAL'),
(36, 'sandbox_webhookid_paypal', 'CHANGE_ME_SANDBOX_WEBHOOKID_PAYPAL'),
(37, 'production_verifylink_paypal', 'https://api-m.paypal.com/v1/notifications/verify-webhook-signature'),
(38, 'production_webhookid_paypal', 'CHANGE_ME_PRODUCTION_WEBHOOKID_PAYPAL'),
(39, 'production_secret_paypal', 'CHANGE_ME_PRODUCTION_SECRET_PAYPAL'),
(40, 'production_clientid_paypal', 'CHANGE_ME_PRODUCTION_CLIENTID_PAYPAL'),
(41, 'production_tokenurl_paypal', 'https://api-m.paypal.com/v1/oauth2/token'),
(42, 'comment_reviewer', 'administrator, principal'),
(43, 'paypal_mode', 'sandbox'),
(44, 'grade_fee', '10'),
(45, 'installment_date', '2026-03-01'),
(46, 'spring_semester_start', '2026-01-25'),
(47, 'parent_scores_released', '0'),
(48, 'last_day_of_school', '2026-05-31'),
(49, 'parent_scores_released_fall', '1'),
(50, 'parent_scores_released_spring', '0'),
(51, 'parent_report_cutoff', '09');
-- --------------------------------------------------------
--
-- Table structure for table `contactus`
--
CREATE TABLE `contactus` (
`id` int UNSIGNED NOT NULL,
`sender_id` int UNSIGNED NOT NULL,
`reciever_id` int UNSIGNED NOT NULL,
`subject` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`message` text COLLATE utf8mb4_general_ci NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`semester` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `current_flag`
--
CREATE TABLE `current_flag` (
`id` int UNSIGNED NOT NULL,
`student_id` int NOT NULL,
`student_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`grade` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
`flag` enum('payment','attendance','grade','behavior','talking_off_task','calling_out','minor_disruption','minor_dress_code','forgetting_materials','repeated_defiance','disrespectful_tone','minor_profanity','repeated_tardiness','device_misuse','minor_conflict','bullying_harassment','fighting_aggression','threats','theft','vandalism','sexual_harassment','serious_cyber_incident','contraband','weapons_drugs','other') COLLATE utf8mb4_general_ci NOT NULL,
`flag_datetime` datetime NOT NULL,
`flag_state` enum('Open','Closed','Canceled') COLLATE utf8mb4_general_ci DEFAULT 'Open',
`updated_by_open` int DEFAULT NULL,
`open_description` text COLLATE utf8mb4_general_ci,
`updated_by_closed` int DEFAULT NULL,
`close_description` text COLLATE utf8mb4_general_ci,
`action_taken` text COLLATE utf8mb4_general_ci,
`updated_by_canceled` int DEFAULT NULL,
`cancel_description` text COLLATE utf8mb4_general_ci,
`semester` varchar(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`school_year` varchar(9) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `discount_usages`
--
CREATE TABLE `discount_usages` (
`id` int UNSIGNED NOT NULL,
`voucher_id` int NOT NULL,
`invoice_id` int UNSIGNED NOT NULL,
`parent_id` int DEFAULT NULL,
`discount_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`description` text COLLATE utf8mb4_general_ci,
`school_year` varchar(9) COLLATE utf8mb4_general_ci NOT NULL,
`updated_by` int DEFAULT NULL,
`used_at` datetime DEFAULT CURRENT_TIMESTAMP,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `discount_vouchers`
--
CREATE TABLE `discount_vouchers` (
`id` int UNSIGNED NOT NULL,
`code` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
`discount_type` enum('percent','fixed') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'percent',
`description` text COLLATE utf8mb4_general_ci,
`discount_value` decimal(10,2) NOT NULL,
`max_uses` int DEFAULT '1',
`times_used` int DEFAULT '0',
`valid_from` date DEFAULT NULL,
`valid_until` date DEFAULT NULL,
`is_active` tinyint(1) DEFAULT '1',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `early_dismissal_signatures`
--
CREATE TABLE `early_dismissal_signatures` (
`id` int UNSIGNED NOT NULL,
`report_date` date NOT NULL,
`school_year` varchar(16) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
`filename` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`original_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`mime_type` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL,
`file_size` int UNSIGNED DEFAULT NULL,
`uploaded_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `email_templates`
--
CREATE TABLE `email_templates` (
`id` int UNSIGNED NOT NULL,
`code` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
`variant` enum('default','answered','no_answer') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'default',
`subject` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`body_html` mediumtext COLLATE utf8mb4_general_ci NOT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT '1',
`updated_by` int UNSIGNED DEFAULT NULL,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `emergency_contacts`
--
CREATE TABLE `emergency_contacts` (
`id` int UNSIGNED NOT NULL,
`emergency_contact_name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`parent_id` int DEFAULT NULL,
`cellphone` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`relation` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
`semester` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `enrollments`
--
CREATE TABLE `enrollments` (
`id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`class_section_id` int UNSIGNED DEFAULT NULL,
`parent_id` int UNSIGNED NOT NULL,
`enrollment_date` date NOT NULL,
`enrollment_status` enum('admission under review','payment pending','enrolled','withdraw under review','refund pending','withdrawn','waitlist','denied') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'admission under review',
`withdrawal_date` date DEFAULT NULL,
`is_withdrawn` tinyint(1) NOT NULL DEFAULT '0',
`admission_status` enum('pending','accepted','denied') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pending',
`semester` varchar(25) COLLATE utf8mb4_general_ci DEFAULT NULL,
`school_year` varchar(25) COLLATE utf8mb4_general_ci NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `events`
--
CREATE TABLE `events` (
`id` int UNSIGNED NOT NULL,
`event_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`event_category` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`description` text COLLATE utf8mb4_general_ci,
`amount` decimal(10,2) NOT NULL,
`flyer` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`expiration_date` date NOT NULL,
`semester` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `event_charges`
--
CREATE TABLE `event_charges` (
`id` int UNSIGNED NOT NULL,
`event_id` int UNSIGNED NOT NULL,
`parent_id` int UNSIGNED DEFAULT NULL,
`student_id` int UNSIGNED DEFAULT NULL,
`participation` enum('yes','no') COLLATE utf8mb4_general_ci DEFAULT NULL,
`charged` tinyint(1) DEFAULT '0',
`semester` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL,
`updated_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `examquestiondistribution`
--
CREATE TABLE `examquestiondistribution` (
`id` int NOT NULL,
`exam_distribution_id` int NOT NULL,
`exam_id` int NOT NULL,
`question_type_id` int NOT NULL,
`num_questions` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `examquestions`
--
CREATE TABLE `examquestions` (
`id` int NOT NULL,
`exam_question_id` int NOT NULL,
`exam_id` int NOT NULL,
`question_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `exams`
--
CREATE TABLE `exams` (
`id` int UNSIGNED NOT NULL,
`student_id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
`student_school_id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
`class_section_id` int NOT NULL,
`exam_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `exam_drafts`
--
CREATE TABLE `exam_drafts` (
`id` int UNSIGNED NOT NULL,
`teacher_id` int NOT NULL,
`class_section_id` int NOT NULL,
`semester` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_unicode_ci NOT NULL,
`exam_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`draft_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`description` text COLLATE utf8mb4_unicode_ci,
`teacher_file` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`teacher_filename` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`status` enum('draft','submitted','reviewed','finalized','rejected') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft',
`admin_id` int DEFAULT NULL,
`admin_comments` text COLLATE utf8mb4_unicode_ci,
`reviewed_at` datetime DEFAULT NULL,
`final_file` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`final_filename` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`version` smallint UNSIGNED NOT NULL DEFAULT '1',
`previous_draft_id` int UNSIGNED DEFAULT NULL,
`is_legacy` tinyint(1) NOT NULL DEFAULT '0',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `expenses`
--
CREATE TABLE `expenses` (
`id` int UNSIGNED NOT NULL,
`category` enum('Expense','Purchase','Reimbursement','Donation') COLLATE utf8mb4_general_ci NOT NULL,
`amount` decimal(10,2) NOT NULL,
`receipt_path` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`description` text COLLATE utf8mb4_general_ci,
`retailor` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`purchased_by` int UNSIGNED DEFAULT NULL,
`date_of_purchase` date NOT NULL,
`added_by` int UNSIGNED NOT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`status` enum('pending','approved','denied') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pending',
`status_reason` text COLLATE utf8mb4_general_ci,
`updated_by` int UNSIGNED DEFAULT NULL,
`approved_by` int UNSIGNED DEFAULT NULL,
`reimbursement_id` int UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `families`
--
CREATE TABLE `families` (
`id` int UNSIGNED NOT NULL,
`family_code` varchar(64) NOT NULL,
`household_name` varchar(128) DEFAULT NULL,
`address_line1` varchar(128) DEFAULT NULL,
`address_line2` varchar(128) DEFAULT NULL,
`city` varchar(64) DEFAULT NULL,
`state` varchar(32) DEFAULT NULL,
`postal_code` varchar(16) DEFAULT NULL,
`country` varchar(64) DEFAULT NULL,
`primary_phone` varchar(32) DEFAULT NULL,
`preferred_lang` varchar(16) DEFAULT NULL,
`preferred_contact_method` varchar(32) DEFAULT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT '1',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `family_comm_prefs`
--
CREATE TABLE `family_comm_prefs` (
`id` int UNSIGNED NOT NULL,
`family_id` int UNSIGNED NOT NULL,
`category` varchar(32) NOT NULL,
`via_email` tinyint(1) NOT NULL DEFAULT '1',
`via_sms` tinyint(1) NOT NULL DEFAULT '0',
`cc_all_guardians` tinyint(1) NOT NULL DEFAULT '1',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `family_guardians`
--
CREATE TABLE `family_guardians` (
`id` int UNSIGNED NOT NULL,
`family_id` int UNSIGNED NOT NULL,
`user_id` int UNSIGNED NOT NULL,
`relation` varchar(32) NOT NULL DEFAULT 'guardian',
`is_primary` tinyint(1) NOT NULL DEFAULT '0',
`receive_emails` tinyint(1) NOT NULL DEFAULT '1',
`receive_sms` tinyint(1) NOT NULL DEFAULT '0',
`custody_notes` text,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `family_students`
--
CREATE TABLE `family_students` (
`id` int UNSIGNED NOT NULL,
`family_id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`is_primary_home` tinyint(1) NOT NULL DEFAULT '0',
`notes` text,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `final_exam`
--
CREATE TABLE `final_exam` (
`id` int UNSIGNED NOT NULL,
`student_id` int NOT NULL,
`school_id` char(36) NOT NULL,
`class_section_id` int DEFAULT NULL,
`updated_by` int NOT NULL,
`score` float DEFAULT NULL,
`semester` varchar(25) NOT NULL DEFAULT 'Spring',
`school_year` varchar(9) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `final_score`
--
CREATE TABLE `final_score` (
`id` int UNSIGNED NOT NULL,
`student_id` int NOT NULL,
`school_id` char(36) NOT NULL,
`class_section_id` int DEFAULT NULL,
`updated_by` int NOT NULL,
`score` float NOT NULL,
`score_letter` varchar(9) DEFAULT NULL,
`comment` text,
`school_year` varchar(9) NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`semester` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `flag`
--
CREATE TABLE `flag` (
`id` int UNSIGNED NOT NULL,
`student_id` int NOT NULL,
`student_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`grade` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
`flag` enum('payment','attendance','grade','behavior','talking_off_task','calling_out','minor_disruption','minor_dress_code','forgetting_materials','repeated_defiance','disrespectful_tone','minor_profanity','repeated_tardiness','device_misuse','minor_conflict','bullying_harassment','fighting_aggression','threats','theft','vandalism','sexual_harassment','serious_cyber_incident','contraband','weapons_drugs','other') COLLATE utf8mb4_general_ci NOT NULL,
`flag_datetime` datetime NOT NULL,
`flag_state` enum('Closed','Canceled') COLLATE utf8mb4_general_ci NOT NULL,
`updated_by_open` int DEFAULT NULL,
`open_description` text COLLATE utf8mb4_general_ci,
`updated_by_closed` int DEFAULT NULL,
`close_description` text COLLATE utf8mb4_general_ci,
`action_taken` text COLLATE utf8mb4_general_ci,
`updated_by_canceled` int DEFAULT NULL,
`cancel_description` text COLLATE utf8mb4_general_ci,
`semester` varchar(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`school_year` varchar(9) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `grading_locks`
--
CREATE TABLE `grading_locks` (
`id` int UNSIGNED NOT NULL,
`class_section_id` int UNSIGNED NOT NULL,
`semester` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci NOT NULL,
`is_locked` tinyint(1) NOT NULL DEFAULT '0',
`locked_by` int DEFAULT NULL,
`locked_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `homework`
--
CREATE TABLE `homework` (
`id` int UNSIGNED NOT NULL,
`student_id` int NOT NULL,
`school_id` char(36) NOT NULL,
`class_section_id` int DEFAULT NULL,
`updated_by` int NOT NULL,
`homework_index` tinyint DEFAULT NULL,
`score` float DEFAULT NULL,
`comment` text,
`semester` varchar(25) NOT NULL,
`school_year` varchar(9) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `inventory_categories`
--
CREATE TABLE `inventory_categories` (
`id` int UNSIGNED NOT NULL,
`type` enum('classroom','book','office','kitchen') NOT NULL DEFAULT 'office',
`name` varchar(120) NOT NULL,
`description` text,
`grade_min` tinyint UNSIGNED DEFAULT NULL,
`grade_max` tinyint UNSIGNED DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `inventory_items`
--
CREATE TABLE `inventory_items` (
`id` int UNSIGNED NOT NULL,
`type` enum('classroom','book','office','kitchen') NOT NULL,
`category_id` int UNSIGNED DEFAULT NULL,
`name` varchar(190) NOT NULL,
`description` text,
`quantity` int UNSIGNED NOT NULL DEFAULT '0',
`good_qty` int UNSIGNED NOT NULL DEFAULT '0',
`needs_repair_qty` int UNSIGNED NOT NULL DEFAULT '0',
`need_replace_qty` int UNSIGNED NOT NULL DEFAULT '0',
`cannot_find_qty` int UNSIGNED NOT NULL DEFAULT '0',
`unit` varchar(32) DEFAULT NULL,
`condition` enum('good','needs_repair','need_replace','cannot_find') DEFAULT NULL,
`isbn` varchar(32) DEFAULT NULL,
`edition` varchar(32) DEFAULT NULL,
`sku` varchar(64) DEFAULT NULL,
`notes` text,
`semester` enum('Spring','Fall') DEFAULT NULL,
`school_year` varchar(16) DEFAULT NULL,
`updated_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `inventory_movements`
--
CREATE TABLE `inventory_movements` (
`id` int UNSIGNED NOT NULL,
`item_id` int UNSIGNED NOT NULL,
`qty_change` int NOT NULL,
`movement_type` enum('initial','in','out','adjust','distribution') NOT NULL DEFAULT 'adjust',
`reason` varchar(120) DEFAULT NULL,
`note` text,
`semester` enum('Spring','Fall') DEFAULT NULL,
`school_year` varchar(16) DEFAULT NULL,
`performed_by` int UNSIGNED DEFAULT NULL,
`teacher_id` int UNSIGNED DEFAULT NULL,
`student_id` int UNSIGNED DEFAULT NULL,
`class_section_id` int UNSIGNED DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `invoices`
--
CREATE TABLE `invoices` (
`id` int UNSIGNED NOT NULL,
`parent_id` int NOT NULL,
`invoice_number` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
`total_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`balance` decimal(10,2) DEFAULT '0.00',
`paid_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`has_discount` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 if discount applied, 0 no discount',
`issue_date` date NOT NULL,
`due_date` date DEFAULT NULL,
`status` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '''unpaid''',
`description` text COLLATE utf8mb4_general_ci,
`school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`updated_by` int DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `invoice_event`
--
CREATE TABLE `invoice_event` (
`id` int UNSIGNED NOT NULL,
`invoice_id` int UNSIGNED NOT NULL,
`event_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`description` text COLLATE utf8mb4_general_ci,
`amount` decimal(10,2) NOT NULL,
`semester` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL,
`updated_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `invoice_students_list`
--
CREATE TABLE `invoice_students_list` (
`id` int UNSIGNED NOT NULL,
`invoice_id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`student_firstname` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`student_lastname` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`school_id` int UNSIGNED NOT NULL,
`enrolled` tinyint(1) DEFAULT '1',
`school_year` varchar(9) COLLATE utf8mb4_general_ci NOT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `ip_attempts`
--
CREATE TABLE `ip_attempts` (
`id` int UNSIGNED NOT NULL,
`ip_address` varchar(45) COLLATE utf8mb4_general_ci NOT NULL,
`attempts` int NOT NULL DEFAULT '0',
`last_attempt_at` datetime NOT NULL,
`blocked_until` datetime DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `late_slip_logs`
--
CREATE TABLE `late_slip_logs` (
`id` int UNSIGNED NOT NULL,
`school_year` varchar(32) DEFAULT NULL,
`semester` varchar(32) DEFAULT NULL,
`student_name` varchar(191) NOT NULL,
`slip_date` date DEFAULT NULL,
`time_in` time DEFAULT NULL,
`grade` varchar(64) DEFAULT NULL,
`reason` varchar(255) DEFAULT NULL,
`admin_name` varchar(191) DEFAULT NULL,
`printed_by` int DEFAULT NULL,
`printed_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `login_activity`
--
CREATE TABLE `login_activity` (
`id` int UNSIGNED NOT NULL,
`user_id` int NOT NULL,
`email` varchar(255) NOT NULL,
`login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`logout_time` timestamp NULL DEFAULT NULL,
`ip_address` varchar(255) NOT NULL,
`user_agent` varchar(255) NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`semester` varchar(255) NOT NULL,
`school_year` varchar(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `longanswerquestions`
--
CREATE TABLE `longanswerquestions` (
`id` int NOT NULL,
`la_question_id` int NOT NULL,
`question_id` int NOT NULL,
`correct_answer` text COLLATE utf8mb4_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `manual_payments`
--
CREATE TABLE `manual_payments` (
`id` int UNSIGNED NOT NULL,
`invoice_number` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
`payment_method` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`reference` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`discount_number` int DEFAULT NULL,
`proof_path` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `messages`
--
CREATE TABLE `messages` (
`id` int UNSIGNED NOT NULL,
`sender_id` int NOT NULL,
`recipient_id` int NOT NULL,
`subject` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`message` text COLLATE utf8mb4_general_ci NOT NULL,
`sent_datetime` datetime NOT NULL,
`read_status` tinyint(1) NOT NULL DEFAULT '0',
`read_datetime` datetime DEFAULT NULL,
`message_number` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`priority` varchar(10) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'normal',
`attachment` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`status` varchar(10) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'sent',
`semester` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `midterm_exam`
--
CREATE TABLE `midterm_exam` (
`id` int UNSIGNED NOT NULL,
`student_id` int NOT NULL,
`school_id` char(36) NOT NULL,
`class_section_id` int DEFAULT NULL,
`updated_by` int NOT NULL,
`score` float DEFAULT NULL,
`comment` text,
`semester` varchar(25) NOT NULL DEFAULT 'Fall',
`school_year` varchar(9) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `migrations`
--
CREATE TABLE `migrations` (
`id` int UNSIGNED NOT NULL,
`version` varchar(255) NOT NULL,
`class` varchar(255) NOT NULL,
`group` varchar(255) NOT NULL,
`namespace` varchar(255) NOT NULL,
`time` int NOT NULL,
`batch` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `missing_score_overrides`
--
CREATE TABLE `missing_score_overrides` (
`id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`class_section_id` int UNSIGNED NOT NULL,
`semester` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`school_year` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`item_type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
`item_index` int DEFAULT NULL,
`is_allowed` tinyint(1) NOT NULL DEFAULT '0',
`updated_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `nav_items`
--
CREATE TABLE `nav_items` (
`id` int UNSIGNED NOT NULL,
`menu_parent_id` int UNSIGNED DEFAULT NULL,
`label` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`icon_class` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`target` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sort_order` int NOT NULL DEFAULT '0',
`is_enabled` tinyint(1) NOT NULL DEFAULT '1',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `notifications`
--
CREATE TABLE `notifications` (
`id` int UNSIGNED NOT NULL,
`title` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`message` text COLLATE utf8mb4_general_ci NOT NULL,
`target_group` enum('student','parent','teacher','admin','everyone') COLLATE utf8mb4_general_ci NOT NULL,
`delivery_channels` set('in_app','email','sms') COLLATE utf8mb4_general_ci NOT NULL,
`priority` enum('low','normal','high') COLLATE utf8mb4_general_ci DEFAULT 'normal',
`status` enum('pending','sent','failed') COLLATE utf8mb4_general_ci DEFAULT 'pending',
`action_url` varchar(512) COLLATE utf8mb4_general_ci DEFAULT NULL,
`attachment_path` varchar(512) COLLATE utf8mb4_general_ci DEFAULT NULL,
`scheduled_at` datetime DEFAULT CURRENT_TIMESTAMP,
`sent_at` datetime DEFAULT NULL,
`expires_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` datetime DEFAULT NULL,
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `notification_recipients`
--
CREATE TABLE `notification_recipients` (
`id` int UNSIGNED NOT NULL,
`notification_id` int UNSIGNED NOT NULL,
`user_id` int UNSIGNED NOT NULL,
`status` enum('unread','read','delivered','failed') COLLATE utf8mb4_general_ci DEFAULT 'unread',
`delivered_at` datetime DEFAULT NULL,
`read_at` datetime DEFAULT NULL,
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `parents`
--
CREATE TABLE `parents` (
`id` int NOT NULL,
`secondparent_firstname` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`secondparent_lastname` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`secondparent_gender` enum('Male','Female') COLLATE utf8mb4_general_ci DEFAULT NULL,
`secondparent_email` varchar(150) COLLATE utf8mb4_general_ci NOT NULL,
`secondparent_phone` varchar(15) COLLATE utf8mb4_general_ci NOT NULL,
`firstparent_id` int NOT NULL,
`secondparent_id` int NOT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`school_year` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `parent_attendance_reports`
--
CREATE TABLE `parent_attendance_reports` (
`id` int UNSIGNED NOT NULL,
`parent_id` int UNSIGNED NOT NULL COMMENT 'users.id of the reporting parent (primary parent id)',
`student_id` int UNSIGNED NOT NULL,
`class_section_id` int UNSIGNED DEFAULT NULL,
`report_date` date NOT NULL,
`type` enum('absent','late','early_dismissal') NOT NULL DEFAULT 'absent',
`arrival_time` time DEFAULT NULL,
`dismiss_time` time DEFAULT NULL,
`reason` text,
`semester` varchar(32) DEFAULT NULL,
`school_year` varchar(16) DEFAULT NULL,
`status` enum('new','seen','processed') NOT NULL DEFAULT 'new',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `parent_meeting_schedules`
--
CREATE TABLE `parent_meeting_schedules` (
`id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`parent_user_id` int UNSIGNED NOT NULL,
`parent_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`student_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`class_section_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`date` date NOT NULL,
`time` time NOT NULL,
`notes` text COLLATE utf8mb4_unicode_ci,
`semester` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_unicode_ci NOT NULL,
`status` enum('pending','approved','rejected','cancelled','completed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
`created_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `parent_notifications`
--
CREATE TABLE `parent_notifications` (
`id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`code` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
`incident_date` date NOT NULL,
`channel` enum('email','sms','call','whatsapp') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'email',
`to_address` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`subject` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`status` enum('sent','failed') COLLATE utf8mb4_general_ci NOT NULL,
`response` text COLLATE utf8mb4_general_ci,
`semester` varchar(16) COLLATE utf8mb4_general_ci DEFAULT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `participation`
--
CREATE TABLE `participation` (
`id` int UNSIGNED NOT NULL,
`student_id` int NOT NULL,
`school_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
`class_section_id` int DEFAULT NULL,
`updated_by` int NOT NULL,
`score` float DEFAULT NULL,
`comment` text COLLATE utf8mb4_unicode_ci,
`semester` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `password_resets`
--
CREATE TABLE `password_resets` (
`id` int UNSIGNED NOT NULL,
`email` varchar(255) NOT NULL,
`token` varchar(255) NOT NULL,
`created_at` datetime NOT NULL,
`expires_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `password_reset_requests`
--
CREATE TABLE `password_reset_requests` (
`id` int UNSIGNED NOT NULL,
`ip_address` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
`requested_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `payments`
--
CREATE TABLE `payments` (
`id` int UNSIGNED NOT NULL,
`parent_id` int UNSIGNED NOT NULL,
`invoice_id` int UNSIGNED NOT NULL,
`total_amount` decimal(10,2) NOT NULL,
`paid_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`balance` decimal(10,2) NOT NULL,
`number_of_installments` int NOT NULL,
`transaction_id` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`check_file` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`check_number` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`payment_method` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Unknown',
`payment_date` date NOT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL,
`status` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Pending',
`updated_by` int DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `payment_error`
--
CREATE TABLE `payment_error` (
`id` int UNSIGNED NOT NULL,
`payment_id` int NOT NULL,
`invoice_id` int NOT NULL,
`parent_id` int NOT NULL,
`wrong_paid_amount` decimal(10,2) NOT NULL,
`wrong_payment_method` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
`wrong_check_file` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`error_note` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`logged_at` datetime NOT NULL,
`logged_by` int DEFAULT NULL,
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `payment_notification_logs`
--
CREATE TABLE `payment_notification_logs` (
`id` int UNSIGNED NOT NULL,
`parent_id` int UNSIGNED NOT NULL,
`invoice_id` int UNSIGNED DEFAULT NULL,
`school_year` varchar(32) DEFAULT NULL,
`period_year` smallint NOT NULL,
`period_month` tinyint NOT NULL,
`type` enum('no_payment','installment') NOT NULL DEFAULT 'installment',
`to_email` varchar(191) DEFAULT NULL,
`cc_email` varchar(191) DEFAULT NULL,
`head_fa_notified` tinyint(1) NOT NULL DEFAULT '0',
`subject` varchar(255) DEFAULT NULL,
`body` mediumtext,
`status` enum('sent','failed') NOT NULL DEFAULT 'sent',
`error_message` text,
`balance_snapshot` decimal(10,2) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`sent_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `payment_transactions`
--
CREATE TABLE `payment_transactions` (
`id` int UNSIGNED NOT NULL,
`transaction_id` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`payment_id` int NOT NULL,
`transaction_date` datetime DEFAULT CURRENT_TIMESTAMP,
`amount` decimal(10,2) NOT NULL,
`payment_method` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
`payment_status` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Pending',
`transaction_fee` decimal(10,2) DEFAULT '0.00',
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `paypal_payments`
--
CREATE TABLE `paypal_payments` (
`id` int UNSIGNED NOT NULL,
`webhook_id` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
`parent_school_id` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
`order_id` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
`transaction_id` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
`status` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
`currency` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`paypal_fee` decimal(10,2) DEFAULT NULL,
`net_amount` decimal(10,2) DEFAULT NULL,
`payer_email` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL,
`merchant_id` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
`event_type` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
`summary` text COLLATE utf8mb4_general_ci,
`raw_payload` longtext COLLATE utf8mb4_general_ci,
`synced` tinyint(1) NOT NULL DEFAULT '0',
`sync_attempts` int NOT NULL DEFAULT '0',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `paypal_transactions`
--
CREATE TABLE `paypal_transactions` (
`id` int NOT NULL,
`transaction_id` varchar(100) NOT NULL,
`payment_id` int DEFAULT NULL,
`firstname` varchar(100) DEFAULT NULL,
`lastname` varchar(100) DEFAULT NULL,
`event_type` varchar(100) NOT NULL,
`payer_email` varchar(255) DEFAULT NULL,
`amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`currency` varchar(10) NOT NULL,
`status` varchar(50) NOT NULL DEFAULT 'Pending',
`payment_method` varchar(50) NOT NULL,
`transaction_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
`raw_data` text,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`school_year` varchar(20) DEFAULT NULL,
`semester` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `permissions`
--
CREATE TABLE `permissions` (
`id` int UNSIGNED NOT NULL,
`name` varchar(255) NOT NULL,
`description` text,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `placement_batches`
--
CREATE TABLE `placement_batches` (
`id` int UNSIGNED NOT NULL,
`placement_test` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
`school_year` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_by` int UNSIGNED DEFAULT NULL,
`updated_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `placement_levels`
--
CREATE TABLE `placement_levels` (
`id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`level` tinyint UNSIGNED NOT NULL,
`school_year` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`created_by` int UNSIGNED DEFAULT NULL,
`updated_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `placement_scores`
--
CREATE TABLE `placement_scores` (
`id` int UNSIGNED NOT NULL,
`batch_id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`score` smallint UNSIGNED NOT NULL,
`created_by` int UNSIGNED DEFAULT NULL,
`updated_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `preferences`
--
CREATE TABLE `preferences` (
`id` int NOT NULL,
`user_id` int NOT NULL,
`receive_email_notifications` tinyint(1) DEFAULT '1',
`receive_sms_notifications` tinyint(1) DEFAULT '0',
`theme` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'light',
`language` varchar(10) COLLATE utf8mb4_general_ci DEFAULT 'en',
`timezone` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'UTC',
`receive_push_notifications` tinyint(1) DEFAULT '0',
`daily_summary_email` tinyint(1) DEFAULT '1',
`privacy_mode` tinyint(1) DEFAULT '0',
`marketing_emails` tinyint(1) DEFAULT '1',
`account_activity_alerts` tinyint(1) DEFAULT '1',
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `print_requests`
--
CREATE TABLE `print_requests` (
`id` int UNSIGNED NOT NULL,
`teacher_id` int UNSIGNED NOT NULL,
`admin_id` int UNSIGNED DEFAULT NULL,
`class_id` int UNSIGNED NOT NULL,
`file_path` varchar(255) NOT NULL,
`page_selection` varchar(255) DEFAULT NULL,
`num_copies` int NOT NULL,
`required_by` datetime NOT NULL,
`pickup_method` varchar(255) NOT NULL,
`status` enum('not_assigned','assigned','done','delivered') NOT NULL DEFAULT 'not_assigned',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `project`
--
CREATE TABLE `project` (
`id` int UNSIGNED NOT NULL,
`student_id` int NOT NULL,
`school_id` char(36) NOT NULL,
`class_section_id` int DEFAULT NULL,
`updated_by` int NOT NULL,
`project_index` tinyint DEFAULT NULL,
`score` float DEFAULT NULL,
`comment` text,
`semester` varchar(25) NOT NULL,
`school_year` varchar(9) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `promotion_queue`
--
CREATE TABLE `promotion_queue` (
`id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`from_class_section_id` int UNSIGNED DEFAULT NULL,
`to_class_id` int UNSIGNED NOT NULL,
`to_class_section_id` int UNSIGNED DEFAULT NULL,
`school_year_from` varchar(9) NOT NULL,
`school_year_to` varchar(9) NOT NULL,
`status` enum('queued','assigned','applied','cancelled') NOT NULL DEFAULT 'queued',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`updated_by` int UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `qcmquestions`
--
CREATE TABLE `qcmquestions` (
`id` int NOT NULL,
`qcm_question_id` int NOT NULL,
`question_id` int NOT NULL,
`answer_a` text COLLATE utf8mb4_unicode_ci NOT NULL,
`answer_b` text COLLATE utf8mb4_unicode_ci NOT NULL,
`answer_c` text COLLATE utf8mb4_unicode_ci,
`answer_d` text COLLATE utf8mb4_unicode_ci,
`answer_e` text COLLATE utf8mb4_unicode_ci,
`correct_answer` char(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `questions`
--
CREATE TABLE `questions` (
`id` int NOT NULL,
`question_id` int NOT NULL,
`chapter_id` int NOT NULL,
`question_type_id` int NOT NULL,
`question_text` text COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `questionstruefalse`
--
CREATE TABLE `questionstruefalse` (
`id` int NOT NULL,
`tf_question_id` int NOT NULL,
`question_id` int NOT NULL,
`correct_answer` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `questiontypes`
--
CREATE TABLE `questiontypes` (
`id` int NOT NULL,
`question_type_id` int NOT NULL,
`question_type_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `quiz`
--
CREATE TABLE `quiz` (
`id` int UNSIGNED NOT NULL,
`student_id` int NOT NULL,
`school_id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
`class_section_id` int NOT NULL,
`updated_by` int NOT NULL,
`quiz_index` tinyint NOT NULL,
`score` float DEFAULT NULL,
`comment` text COLLATE utf8mb4_general_ci,
`semester` varchar(25) COLLATE utf8mb4_general_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `refunds`
--
CREATE TABLE `refunds` (
`id` int UNSIGNED NOT NULL,
`parent_id` int UNSIGNED NOT NULL,
`school_year` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`invoice_id` int UNSIGNED DEFAULT NULL,
`refund_amount` decimal(10,2) NOT NULL,
`requested_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`approved_at` datetime DEFAULT NULL,
`refunded_at` datetime DEFAULT NULL,
`status` enum('Pending','Partial','Approved','Rejected','Paid') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Pending',
`reason` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`refund_paid_amount` decimal(10,2) NOT NULL,
`request` varchar(25) COLLATE utf8mb4_general_ci DEFAULT NULL,
`note` text COLLATE utf8mb4_general_ci,
`approved_by` int UNSIGNED DEFAULT NULL,
`updated_by` int UNSIGNED DEFAULT NULL,
`refund_method` enum('Check','Online','Cash') COLLATE utf8mb4_general_ci DEFAULT NULL,
`check_nbr` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`check_file` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `reimbursements`
--
CREATE TABLE `reimbursements` (
`id` int UNSIGNED NOT NULL,
`expense_id` int UNSIGNED DEFAULT NULL,
`amount` decimal(10,2) NOT NULL,
`reimbursed_to` int UNSIGNED NOT NULL,
`approved_by` int UNSIGNED DEFAULT NULL,
`receipt_path` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`description` text COLLATE utf8mb4_general_ci,
`status` enum('Pending','Approved','Rejected','Paid') COLLATE utf8mb4_general_ci DEFAULT 'Pending',
`added_by` int UNSIGNED NOT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`check_number` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`reimbursement_method` enum('Cash','Check') COLLATE utf8mb4_general_ci DEFAULT NULL,
`batch_number` int UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `reimbursement_batches`
--
CREATE TABLE `reimbursement_batches` (
`id` int UNSIGNED NOT NULL,
`title` varchar(120) COLLATE utf8mb4_general_ci DEFAULT NULL,
`yearly_batch_number` int UNSIGNED NOT NULL DEFAULT '0',
`status` enum('open','closed') COLLATE utf8mb4_general_ci DEFAULT 'open',
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_by` int UNSIGNED DEFAULT NULL,
`closed_by` int UNSIGNED DEFAULT NULL,
`opened_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`closed_at` datetime DEFAULT NULL,
`notes` text COLLATE utf8mb4_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `reimbursement_batch_admin_files`
--
CREATE TABLE `reimbursement_batch_admin_files` (
`id` int UNSIGNED NOT NULL,
`batch_id` int UNSIGNED NOT NULL,
`admin_id` int UNSIGNED NOT NULL DEFAULT '0',
`filename` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`original_filename` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`uploaded_at` datetime NOT NULL,
`uploaded_by` int UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `reimbursement_batch_items`
--
CREATE TABLE `reimbursement_batch_items` (
`id` int UNSIGNED NOT NULL,
`batch_id` int UNSIGNED NOT NULL,
`expense_id` int UNSIGNED NOT NULL,
`reimbursement_id` int UNSIGNED DEFAULT NULL,
`admin_id` int UNSIGNED DEFAULT NULL,
`assigned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`unassigned_at` datetime DEFAULT NULL,
`notes` text COLLATE utf8mb4_general_ci,
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `report_card_acknowledgements`
--
CREATE TABLE `report_card_acknowledgements` (
`id` int UNSIGNED NOT NULL,
`parent_id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`school_year` varchar(20) NOT NULL,
`semester` varchar(20) NOT NULL,
`viewed_at` datetime DEFAULT NULL,
`signed_at` datetime DEFAULT NULL,
`signed_name` varchar(120) DEFAULT NULL,
`signer_ip` varchar(45) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `roles`
--
CREATE TABLE `roles` (
`id` int UNSIGNED NOT NULL,
`name` varchar(255) NOT NULL,
`slug` varchar(64) DEFAULT NULL,
`description` text,
`dashboard_route` varchar(255) NOT NULL DEFAULT 'administrator/administratordashboard',
`priority` int UNSIGNED NOT NULL DEFAULT '100',
`is_active` tinyint(1) NOT NULL DEFAULT '1',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
--
-- Dumping data for table `roles`
--
INSERT INTO `roles` (`id`, `name`, `slug`, `description`, `dashboard_route`, `priority`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'administrator', 'administrator', 'system administrator', 'administrator/administratordashboard', 1, 1, '2025-09-08 22:30:34', '2025-09-08 22:30:34'),
(2, 'principal', 'principal', 'school principal', 'administrator/administratordashboard', 8, 1, '2025-09-08 22:30:34', '2025-09-08 22:30:34'),
(3, 'vice principal', 'vice_principal', 'vice principal', 'administrator/administratordashboard', 9, 1, '2025-09-08 22:30:34', '2025-09-08 22:30:34'),
(4, 'teacher', 'teacher', 'teacher user', '/teacher_dashboard', 3, 1, '2025-09-08 22:30:34', '2025-09-08 22:30:34'),
(5, 'teacher_assistant', 'teacher_assistant', 'assistant teacher user', '/teacher_dashboard', 4, 1, '2025-09-08 22:30:34', '2025-09-08 22:30:34'),
(6, 'student', 'student', 'student user', '/landing_page/student_dashboard', 7, 1, '2025-09-08 22:30:34', '2025-09-08 22:30:34'),
(7, 'parent', 'parent', 'parent/guardian user', '/parent_dashboard', 5, 1, '2025-09-08 22:30:34', '2025-09-08 22:30:34'),
(8, 'admin', 'admin', 'administrative staff', 'administrator/administratordashboard', 2, 1, '2025-09-08 22:30:34', '2025-09-08 22:30:34'),
(9, 'head of csm', 'head_of_department_communication', 'hod communication', 'administrator/administratordashboard', 10, 1, '2025-09-08 22:30:34', '2025-09-08 22:30:34'),
(10, 'head of itpd', 'head_of_itpd', 'hod it', 'administrator/administratordashboard', 11, 1, '2025-09-08 22:30:34', '2025-09-21 04:44:44'),
(11, 'head of department education', 'head_of_department_education', 'hod education', 'administrator/administratordashboard', 12, 1, '2025-09-08 22:30:34', '2025-09-08 22:30:34'),
(12, 'head of fa', 'head_of_fa', 'hod finance', 'administrator/administratordashboard', 13, 1, '2025-09-08 22:30:34', '2025-09-21 04:44:58'),
(13, 'guest', 'guest', 'special authorized parent access', '/landing_page/guest_dashboard', 6, 1, '2025-09-08 22:30:34', '2025-09-08 22:30:34'),
(14, 'csm_contributor', 'csm_contributor', 'role for communication and social media admins', 'administrator/administratordashboard', 100, 1, '2025-09-09 03:04:49', '2025-09-09 03:04:49'),
(15, 'head of fes', 'head_of_fes', 'facility, equipment & supplies', 'administrator/administratordashboard', 100, 1, '2025-09-09 03:13:50', '2025-09-27 04:50:51'),
(16, 'fes_contributor', 'fes_contributor', 'facility, equipment & supplies', 'administrator/administratordashboard', 100, 1, '2025-09-09 03:14:08', '2025-09-27 04:51:05'),
(17, 'saf_contributor', 'saf_contributor', 'Student Academic Affairs', 'administrator/administratordashboard', 100, 1, '2025-09-09 03:15:21', '2025-09-09 03:15:21'),
(18, 'head of fs', 'head_of_fs', '', 'administrator/administratordashboard', 100, 1, '2025-09-16 16:49:47', '2025-09-21 04:45:56'),
(19, 'itpd contributor', 'itpd_contributor', '', 'administrator/administratordashboard', 100, 1, '2025-09-21 01:37:59', '2025-09-21 04:46:09'),
(20, 'financial_contributor', 'financial_contributor', '', 'administrator/administratordashboard', 100, 1, '2025-09-21 04:47:00', '2025-09-21 04:47:00'),
(21, 'fs_contributor', 'fs_contributor', '', '/landing_page/guest_dashboard', 100, 1, '2025-09-27 17:47:19', '2025-09-27 17:47:19'),
(22, 'substitute', 'substitute', 'help when a teacher is absent', '/landing_page/teacher_dashboard', 100, 1, '2025-10-20 04:28:14', '2025-10-20 04:28:14');
-- --------------------------------------------------------
--
-- Table structure for table `role_nav_items`
--
CREATE TABLE `role_nav_items` (
`id` int UNSIGNED NOT NULL,
`role_id` int UNSIGNED NOT NULL,
`nav_item_id` int UNSIGNED NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `role_permissions`
--
CREATE TABLE `role_permissions` (
`id` int UNSIGNED NOT NULL,
`role_id` int NOT NULL,
`permission_id` int NOT NULL,
`can_create` tinyint(1) DEFAULT '0',
`can_read` tinyint(1) DEFAULT '0',
`can_update` tinyint(1) DEFAULT '0',
`can_delete` tinyint(1) DEFAULT '0',
`can_manage` tinyint(1) DEFAULT '0',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `scan_log`
--
CREATE TABLE `scan_log` (
`id` int UNSIGNED NOT NULL,
`user_id` int NOT NULL,
`card_id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`scan_time` datetime DEFAULT CURRENT_TIMESTAMP,
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `score_comments`
--
CREATE TABLE `score_comments` (
`id` int UNSIGNED NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`class_section_id` int NOT NULL,
`score_type` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
`semester` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`school_year` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`comment` text COLLATE utf8mb4_general_ci,
`comment_review` text COLLATE utf8mb4_general_ci,
`reviewed_by` int UNSIGNED DEFAULT NULL,
`commented_by` int DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `sections`
--
CREATE TABLE `sections` (
`id` int UNSIGNED NOT NULL,
`section_name` varchar(100) NOT NULL,
`description` text,
`updated_at` datetime DEFAULT NULL,
`updated_by` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `semester_scores`
--
CREATE TABLE `semester_scores` (
`id` int UNSIGNED NOT NULL,
`student_id` int NOT NULL,
`school_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
`class_section_id` int DEFAULT NULL,
`updated_by` int DEFAULT NULL,
`homework_avg` float DEFAULT NULL,
`quiz_avg` float DEFAULT NULL,
`project_avg` float DEFAULT NULL,
`midterm_exam_score` float DEFAULT NULL,
`final_exam_score` float DEFAULT NULL,
`attendance_score` float DEFAULT NULL,
`participation_score` float DEFAULT NULL,
`ptap_score` decimal(5,2) DEFAULT NULL,
`test_avg` float DEFAULT NULL,
`semester_score` float DEFAULT NULL,
`semester` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `settings`
--
CREATE TABLE `settings` (
`id` int UNSIGNED NOT NULL,
`name` varchar(255) NOT NULL,
`timezone` varchar(255) NOT NULL,
`updated_by` int DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `shortanswerquestions`
--
CREATE TABLE `shortanswerquestions` (
`id` int NOT NULL,
`sa_question_id` int NOT NULL,
`question_id` int NOT NULL,
`correct_answer` text COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `staff`
--
CREATE TABLE `staff` (
`id` int UNSIGNED NOT NULL,
`firstname` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`lastname` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`email` varchar(150) COLLATE utf8mb4_general_ci NOT NULL,
`phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`role_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci NOT NULL,
`active_role` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Active',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_id` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `staff_attendance`
--
CREATE TABLE `staff_attendance` (
`id` int UNSIGNED NOT NULL,
`user_id` int UNSIGNED NOT NULL,
`role_name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`date` date NOT NULL,
`semester` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
`school_year` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
`status` enum('present','absent','late') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'present',
`reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`created_by` int UNSIGNED DEFAULT NULL,
`updated_by` int UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `students`
--
CREATE TABLE `students` (
`id` int UNSIGNED NOT NULL,
`school_id` char(100) COLLATE utf8mb4_general_ci NOT NULL,
`firstname` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`lastname` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`dob` date DEFAULT NULL,
`age` int NOT NULL,
`gender` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT '1',
`registration_grade` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
`is_new` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1 = new student, 0 = returning student',
`photo_consent` tinyint(1) NOT NULL,
`parent_id` int NOT NULL,
`registration_date` datetime DEFAULT NULL,
`tuition_paid` tinyint(1) NOT NULL DEFAULT '0',
`rfid_tag` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`year_of_registration` varchar(25) COLLATE utf8mb4_general_ci NOT NULL,
`school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `student_allergies`
--
CREATE TABLE `student_allergies` (
`id` int NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`allergy` varchar(100) COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `student_class`
--
CREATE TABLE `student_class` (
`id` int UNSIGNED NOT NULL,
`student_id` int NOT NULL,
`class_section_id` int DEFAULT NULL,
`is_event_only` tinyint UNSIGNED NOT NULL DEFAULT '0',
`semester` varchar(255) NOT NULL,
`school_year` varchar(25) DEFAULT NULL,
`description` text,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`updated_by` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `student_medical_conditions`
--
CREATE TABLE `student_medical_conditions` (
`id` int NOT NULL,
`student_id` int UNSIGNED NOT NULL,
`condition_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `subject_curriculum_items`
--
CREATE TABLE `subject_curriculum_items` (
`id` int UNSIGNED NOT NULL,
`class_id` int UNSIGNED NOT NULL,
`subject` enum('islamic','quran') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'islamic',
`unit_number` smallint DEFAULT NULL,
`unit_title` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`chapter_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `support_requests`
--
CREATE TABLE `support_requests` (
`id` int UNSIGNED NOT NULL,
`user_id` int NOT NULL,
`subject` varchar(255) NOT NULL,
`message` text NOT NULL,
`status` varchar(25) NOT NULL DEFAULT 'open',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`semester` varchar(255) NOT NULL,
`school_year` varchar(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `teacher_attendance_data`
--
CREATE TABLE `teacher_attendance_data` (
`id` int UNSIGNED NOT NULL,
`class_section_id` int UNSIGNED NOT NULL,
`teacher_id` int UNSIGNED NOT NULL,
`position` enum('main','ta') NOT NULL,
`date` date NOT NULL,
`semester` varchar(25) NOT NULL,
`school_year` varchar(25) NOT NULL,
`status` enum('present','absent','late') NOT NULL,
`reason` varchar(255) DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `teacher_class`
--
CREATE TABLE `teacher_class` (
`id` int UNSIGNED NOT NULL,
`class_section_id` int DEFAULT NULL,
`teacher_id` int DEFAULT NULL,
`position` enum('main','ta') COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(25) COLLATE utf8mb4_general_ci NOT NULL,
`school_year` varchar(25) COLLATE utf8mb4_general_ci DEFAULT NULL,
`description` text COLLATE utf8mb4_general_ci,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`updated_by` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `teacher_submission_notification_history`
--
CREATE TABLE `teacher_submission_notification_history` (
`id` int UNSIGNED NOT NULL,
`teacher_id` int UNSIGNED NOT NULL,
`class_section_id` int UNSIGNED NOT NULL,
`admin_id` int UNSIGNED NOT NULL,
`notification_category` varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'teacher_submissions',
`message` text COLLATE utf8mb4_general_ci,
`status` varchar(32) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'sent',
`school_year` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
`sent_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`id` int UNSIGNED NOT NULL,
`school_id` int UNSIGNED DEFAULT NULL,
`firstname` varchar(255) NOT NULL,
`lastname` varchar(255) NOT NULL,
`gender` enum('Male','Female') DEFAULT NULL,
`cellphone` varchar(25) NOT NULL,
`email` varchar(255) NOT NULL,
`address_street` varchar(255) NOT NULL,
`apt` varchar(25) DEFAULT NULL,
`city` varchar(255) NOT NULL,
`state` varchar(25) NOT NULL,
`zip` varchar(25) NOT NULL,
`accept_school_policy` tinyint(1) NOT NULL,
`is_verified` tinyint(1) NOT NULL DEFAULT '0',
`status` varchar(10) NOT NULL DEFAULT 'Inactive',
`is_suspended` tinyint(1) NOT NULL DEFAULT '0',
`failed_attempts` int DEFAULT '0',
`password` varchar(255) NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`token` varchar(255) DEFAULT NULL,
`account_id` varchar(255) DEFAULT NULL,
`user_type` enum('primary','secondary','tertiary') DEFAULT 'primary',
`semester` varchar(255) NOT NULL,
`school_year` varchar(9) DEFAULT NULL,
`rfid_tag` varchar(100) DEFAULT NULL,
`last_failed_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `user_notifications`
--
CREATE TABLE `user_notifications` (
`id` int UNSIGNED NOT NULL,
`notification_id` int NOT NULL,
`user_id` int NOT NULL,
`is_read` tinyint(1) DEFAULT '0',
`delivered` tinyint(1) DEFAULT '0',
`delivered_at` datetime DEFAULT NULL,
`school_year` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`semester` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `user_preferences`
--
CREATE TABLE `user_preferences` (
`id` int UNSIGNED NOT NULL,
`user_id` int NOT NULL,
`notification_email` tinyint(1) DEFAULT '1',
`notification_sms` tinyint(1) DEFAULT '1',
`theme` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'light',
`language` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'en',
`style_color` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`menu_color` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`menu_custom_bg` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`menu_custom_text` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`menu_custom_mode` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `user_roles`
--
CREATE TABLE `user_roles` (
`id` int UNSIGNED NOT NULL,
`user_id` int UNSIGNED NOT NULL,
`role_id` int UNSIGNED NOT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` datetime DEFAULT NULL,
`updated_by` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `whatsapp_group_links`
--
CREATE TABLE `whatsapp_group_links` (
`id` int NOT NULL,
`class_section_id` int NOT NULL,
`class_section_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`school_year` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`semester` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`invite_link` text COLLATE utf8mb4_general_ci NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `whatsapp_group_memberships`
--
CREATE TABLE `whatsapp_group_memberships` (
`id` int UNSIGNED NOT NULL,
`class_section_id` int NOT NULL,
`school_year` varchar(25) NOT NULL,
`semester` varchar(20) NOT NULL,
`subject_type` enum('primary','second') NOT NULL DEFAULT 'primary',
`subject_id` int NOT NULL COMMENT 'users.id for primary; parents.id for second',
`is_member` tinyint(1) NOT NULL DEFAULT '0',
`verified_by` int DEFAULT NULL,
`verified_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- --------------------------------------------------------
--
-- Table structure for table `whatsapp_invites_log`
--
CREATE TABLE `whatsapp_invites_log` (
`id` int NOT NULL,
`parent_id` int NOT NULL,
`email` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`class_section_id` int DEFAULT NULL,
`link_id` int DEFAULT NULL,
`status` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`error_message` text COLLATE utf8mb4_general_ci,
`sent_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `additional_charges`
--
ALTER TABLE `additional_charges`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_parent` (`parent_id`),
ADD KEY `idx_invoice` (`invoice_id`),
ADD KEY `idx_term` (`school_year`,`semester`);
--
-- Indexes for table `admin_notification_subjects`
--
ALTER TABLE `admin_notification_subjects`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uniq_admin_subject` (`admin_id`,`subject`),
ADD KEY `admin_id` (`admin_id`);
--
-- Indexes for table `attendance_comment_template`
--
ALTER TABLE `attendance_comment_template`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_min_max_score` (`min_score`,`max_score`),
ADD KEY `idx_is_active` (`is_active`);
--
-- Indexes for table `attendance_data`
--
ALTER TABLE `attendance_data`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `attendance_day`
--
ALTER TABLE `attendance_day`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uniq_section_date_term` (`class_section_id`,`date`,`semester`,`school_year`),
ADD KEY `idx_status_autopublish` (`status`,`auto_publish_at`),
ADD KEY `idx_term` (`semester`,`school_year`);
--
-- Indexes for table `attendance_record`
--
ALTER TABLE `attendance_record`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_student_id` (`student_id`),
ADD KEY `idx_class_section_id` (`class_section_id`);
--
-- Indexes for table `attendance_tracking`
--
ALTER TABLE `attendance_tracking`
ADD PRIMARY KEY (`id`),
ADD KEY `student_id` (`student_id`);
--
-- Indexes for table `authorized_users`
--
ALTER TABLE `authorized_users`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `badge_print_logs`
--
ALTER TABLE `badge_print_logs`
ADD PRIMARY KEY (`id`),
ADD KEY `user_id` (`user_id`),
ADD KEY `school_year` (`school_year`);
--
-- Indexes for table `calendar_events`
--
ALTER TABLE `calendar_events`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `chapters`
--
ALTER TABLE `chapters`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `classes`
--
ALTER TABLE `classes`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `classSection`
--
ALTER TABLE `classSection`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `class_preparation_log`
--
ALTER TABLE `class_preparation_log`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `class_prep_adjustments`
--
ALTER TABLE `class_prep_adjustments`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `class_progress_attachments`
--
ALTER TABLE `class_progress_attachments`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_report_id` (`report_id`);
--
-- Indexes for table `class_progress_reports`
--
ALTER TABLE `class_progress_reports`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_class_week` (`class_section_id`,`week_start`,`week_end`);
--
-- Indexes for table `competitions`
--
ALTER TABLE `competitions`
ADD PRIMARY KEY (`id`),
ADD KEY `class_section_id` (`class_section_id`),
ADD KEY `is_published` (`is_published`);
--
-- Indexes for table `competition_class_winners`
--
ALTER TABLE `competition_class_winners`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `competition_id_class_section_id` (`competition_id`,`class_section_id`);
--
-- Indexes for table `competition_scores`
--
ALTER TABLE `competition_scores`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `competition_id_student_id_class_section_id` (`competition_id`,`student_id`,`class_section_id`),
ADD KEY `competition_id` (`competition_id`),
ADD KEY `student_id` (`student_id`),
ADD KEY `class_section_id` (`class_section_id`);
--
-- Indexes for table `competition_winners`
--
ALTER TABLE `competition_winners`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `competition_id_class_section_id_student_id` (`competition_id`,`class_section_id`,`student_id`),
ADD KEY `competition_id` (`competition_id`),
ADD KEY `class_section_id` (`class_section_id`);
--
-- Indexes for table `configuration`
--
ALTER TABLE `configuration`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `contactus`
--
ALTER TABLE `contactus`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `current_flag`
--
ALTER TABLE `current_flag`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `discount_usages`
--
ALTER TABLE `discount_usages`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `discount_vouchers`
--
ALTER TABLE `discount_vouchers`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `early_dismissal_signatures`
--
ALTER TABLE `early_dismissal_signatures`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uniq_early_dismissal_signature` (`report_date`,`school_year`,`semester`),
ADD KEY `report_date` (`report_date`);
--
-- Indexes for table `email_templates`
--
ALTER TABLE `email_templates`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uniq_code_variant` (`code`,`variant`);
--
-- Indexes for table `emergency_contacts`
--
ALTER TABLE `emergency_contacts`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `enrollments`
--
ALTER TABLE `enrollments`
ADD PRIMARY KEY (`id`),
ADD KEY `student_id` (`student_id`),
ADD KEY `class_section_id` (`class_section_id`),
ADD KEY `parent_id` (`parent_id`);
--
-- Indexes for table `events`
--
ALTER TABLE `events`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `event_charges`
--
ALTER TABLE `event_charges`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `examquestiondistribution`
--
ALTER TABLE `examquestiondistribution`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `examquestions`
--
ALTER TABLE `examquestions`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `exams`
--
ALTER TABLE `exams`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `exam_drafts`
--
ALTER TABLE `exam_drafts`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_exam_drafts_teacher` (`teacher_id`),
ADD KEY `idx_exam_drafts_class_section` (`class_section_id`),
ADD KEY `idx_exam_drafts_status` (`status`),
ADD KEY `idx_exam_drafts_prev` (`previous_draft_id`);
--
-- Indexes for table `expenses`
--
ALTER TABLE `expenses`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `families`
--
ALTER TABLE `families`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `family_code` (`family_code`);
--
-- Indexes for table `family_comm_prefs`
--
ALTER TABLE `family_comm_prefs`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `family_id_category` (`family_id`,`category`);
--
-- Indexes for table `family_guardians`
--
ALTER TABLE `family_guardians`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `family_id_user_id` (`family_id`,`user_id`),
ADD KEY `family_guardians_user_id_foreign` (`user_id`);
--
-- Indexes for table `family_students`
--
ALTER TABLE `family_students`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `family_id_student_id` (`family_id`,`student_id`),
ADD KEY `family_students_student_id_foreign` (`student_id`);
--
-- Indexes for table `final_exam`
--
ALTER TABLE `final_exam`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `final_score`
--
ALTER TABLE `final_score`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `flag`
--
ALTER TABLE `flag`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `grading_locks`
--
ALTER TABLE `grading_locks`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uniq_grading_locks_section_term` (`class_section_id`,`semester`,`school_year`),
ADD KEY `idx_grading_locks_section_term` (`class_section_id`,`semester`,`school_year`);
--
-- Indexes for table `homework`
--
ALTER TABLE `homework`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `inventory_categories`
--
ALTER TABLE `inventory_categories`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `type_name` (`type`,`name`),
ADD KEY `idx_cat_type_grade_range` (`type`,`grade_min`,`grade_max`);
--
-- Indexes for table `inventory_items`
--
ALTER TABLE `inventory_items`
ADD PRIMARY KEY (`id`),
ADD KEY `type_name` (`type`,`name`),
ADD KEY `school_year_semester` (`school_year`,`semester`),
ADD KEY `category_id` (`category_id`),
ADD KEY `updated_by` (`updated_by`),
ADD KEY `idx_items_schoolyear_semester` (`school_year`,`semester`);
--
-- Indexes for table `inventory_movements`
--
ALTER TABLE `inventory_movements`
ADD PRIMARY KEY (`id`),
ADD KEY `item_id` (`item_id`),
ADD KEY `school_year_semester` (`school_year`,`semester`),
ADD KEY `movement_type` (`movement_type`);
--
-- Indexes for table `invoices`
--
ALTER TABLE `invoices`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `invoice_event`
--
ALTER TABLE `invoice_event`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `invoice_students_list`
--
ALTER TABLE `invoice_students_list`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `ip_attempts`
--
ALTER TABLE `ip_attempts`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `late_slip_logs`
--
ALTER TABLE `late_slip_logs`
ADD PRIMARY KEY (`id`),
ADD KEY `school_year` (`school_year`),
ADD KEY `student_name` (`student_name`),
ADD KEY `idx_late_slip_year_sem` (`school_year`,`semester`);
--
-- Indexes for table `login_activity`
--
ALTER TABLE `login_activity`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `longanswerquestions`
--
ALTER TABLE `longanswerquestions`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `manual_payments`
--
ALTER TABLE `manual_payments`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `messages`
--
ALTER TABLE `messages`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `midterm_exam`
--
ALTER TABLE `midterm_exam`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `migrations`
--
ALTER TABLE `migrations`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `missing_score_overrides`
--
ALTER TABLE `missing_score_overrides`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `missing_score_overrides_unique` (`student_id`,`class_section_id`,`semester`,`school_year`,`item_type`,`item_index`),
ADD KEY `missing_score_overrides_scope` (`class_section_id`,`semester`,`school_year`,`item_type`);
--
-- Indexes for table `nav_items`
--
ALTER TABLE `nav_items`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `notification_recipients`
--
ALTER TABLE `notification_recipients`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `parents`
--
ALTER TABLE `parents`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `parent_attendance_reports`
--
ALTER TABLE `parent_attendance_reports`
ADD PRIMARY KEY (`id`),
ADD KEY `parent_attendance_reports_parent_id_foreign` (`parent_id`),
ADD KEY `report_date` (`report_date`),
ADD KEY `student_id_report_date` (`student_id`,`report_date`);
--
-- Indexes for table `parent_meeting_schedules`
--
ALTER TABLE `parent_meeting_schedules`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_pms_student_id` (`student_id`),
ADD KEY `idx_pms_parent_user_id` (`parent_user_id`),
ADD KEY `idx_pms_date_time` (`date`,`time`),
ADD KEY `idx_pms_semester_year` (`semester`,`school_year`),
ADD KEY `idx_pms_status` (`status`);
--
-- Indexes for table `parent_notifications`
--
ALTER TABLE `parent_notifications`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uniq_notif` (`student_id`,`code`,`incident_date`,`channel`,`to_address`);
--
-- Indexes for table `participation`
--
ALTER TABLE `participation`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `password_resets`
--
ALTER TABLE `password_resets`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `password_reset_requests`
--
ALTER TABLE `password_reset_requests`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `payments`
--
ALTER TABLE `payments`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `payment_error`
--
ALTER TABLE `payment_error`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `payment_notification_logs`
--
ALTER TABLE `payment_notification_logs`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `parent_id_period_year_period_month_type` (`parent_id`,`period_year`,`period_month`,`type`),
ADD KEY `parent_id` (`parent_id`),
ADD KEY `invoice_id` (`invoice_id`),
ADD KEY `school_year` (`school_year`),
ADD KEY `period_year_period_month` (`period_year`,`period_month`);
--
-- Indexes for table `payment_transactions`
--
ALTER TABLE `payment_transactions`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `paypal_payments`
--
ALTER TABLE `paypal_payments`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `paypal_transactions`
--
ALTER TABLE `paypal_transactions`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `permissions`
--
ALTER TABLE `permissions`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `placement_batches`
--
ALTER TABLE `placement_batches`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_placement_test_school_year` (`placement_test`,`school_year`);
--
-- Indexes for table `placement_levels`
--
ALTER TABLE `placement_levels`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uniq_student_year` (`student_id`,`school_year`),
ADD KEY `idx_school_year` (`school_year`),
ADD KEY `fk_placement_levels_created_by` (`created_by`),
ADD KEY `fk_placement_levels_updated_by` (`updated_by`);
--
-- Indexes for table `placement_scores`
--
ALTER TABLE `placement_scores`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uq_batch_student` (`batch_id`,`student_id`),
ADD KEY `idx_batch_id` (`batch_id`);
--
-- Indexes for table `preferences`
--
ALTER TABLE `preferences`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `print_requests`
--
ALTER TABLE `print_requests`
ADD PRIMARY KEY (`id`),
ADD KEY `print_requests_teacher_id_foreign` (`teacher_id`),
ADD KEY `print_requests_admin_id_foreign` (`admin_id`),
ADD KEY `print_requests_class_id_foreign` (`class_id`);
--
-- Indexes for table `project`
--
ALTER TABLE `project`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `promotion_queue`
--
ALTER TABLE `promotion_queue`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uq_student_year_to` (`student_id`,`school_year_to`),
ADD KEY `to_class_id` (`to_class_id`);
--
-- Indexes for table `qcmquestions`
--
ALTER TABLE `qcmquestions`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `questions`
--
ALTER TABLE `questions`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `questionstruefalse`
--
ALTER TABLE `questionstruefalse`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `questiontypes`
--
ALTER TABLE `questiontypes`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `quiz`
--
ALTER TABLE `quiz`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `refunds`
--
ALTER TABLE `refunds`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `reimbursements`
--
ALTER TABLE `reimbursements`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `reimbursement_batches`
--
ALTER TABLE `reimbursement_batches`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_status` (`status`),
ADD KEY `idx_created_by` (`created_by`),
ADD KEY `fk_batch_closed_by` (`closed_by`);
--
-- Indexes for table `reimbursement_batch_admin_files`
--
ALTER TABLE `reimbursement_batch_admin_files`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `batch_admin_unique` (`batch_id`,`admin_id`),
ADD KEY `batch_id` (`batch_id`),
ADD KEY `admin_id` (`admin_id`);
--
-- Indexes for table `reimbursement_batch_items`
--
ALTER TABLE `reimbursement_batch_items`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uq_batch_expense` (`batch_id`,`expense_id`),
ADD KEY `idx_admin_id` (`admin_id`),
ADD KEY `idx_batch` (`batch_id`),
ADD KEY `fk_batch_items_expense` (`expense_id`),
ADD KEY `fk_batch_items_reimbursement` (`reimbursement_id`);
--
-- Indexes for table `report_card_acknowledgements`
--
ALTER TABLE `report_card_acknowledgements`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `parent_id_student_id_school_year_semester` (`parent_id`,`student_id`,`school_year`,`semester`);
--
-- Indexes for table `roles`
--
ALTER TABLE `roles`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `role_nav_items`
--
ALTER TABLE `role_nav_items`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `role_permissions`
--
ALTER TABLE `role_permissions`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `scan_log`
--
ALTER TABLE `scan_log`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `score_comments`
--
ALTER TABLE `score_comments`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `sections`
--
ALTER TABLE `sections`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `semester_scores`
--
ALTER TABLE `semester_scores`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uq_semester_scores_nk` (`student_id`,`class_section_id`,`semester`,`school_year`),
ADD KEY `idx_student_id` (`student_id`),
ADD KEY `idx_semester_school_year` (`semester`,`school_year`);
--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `shortanswerquestions`
--
ALTER TABLE `shortanswerquestions`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `staff`
--
ALTER TABLE `staff`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `email` (`email`),
ADD UNIQUE KEY `user_id` (`user_id`);
--
-- Indexes for table `staff_attendance`
--
ALTER TABLE `staff_attendance`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uniq_staff_att` (`user_id`,`role_name`,`date`,`semester`,`school_year`),
ADD KEY `idx_staff_att_user` (`user_id`);
--
-- Indexes for table `students`
--
ALTER TABLE `students`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `student_allergies`
--
ALTER TABLE `student_allergies`
ADD PRIMARY KEY (`id`),
ADD KEY `student_id` (`student_id`);
--
-- Indexes for table `student_class`
--
ALTER TABLE `student_class`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `student_medical_conditions`
--
ALTER TABLE `student_medical_conditions`
ADD PRIMARY KEY (`id`),
ADD KEY `student_id` (`student_id`);
--
-- Indexes for table `subject_curriculum_items`
--
ALTER TABLE `subject_curriculum_items`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_class_subject` (`class_id`,`subject`);
--
-- Indexes for table `support_requests`
--
ALTER TABLE `support_requests`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `teacher_attendance_data`
--
ALTER TABLE `teacher_attendance_data`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uniq_teacher_att` (`class_section_id`,`teacher_id`,`date`,`semester`,`school_year`),
ADD KEY `idx_att_by_day` (`class_section_id`,`date`,`semester`,`school_year`),
ADD KEY `fk_tatt_user` (`teacher_id`);
--
-- Indexes for table `teacher_class`
--
ALTER TABLE `teacher_class`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `unique_teacher_assignment` (`teacher_id`,`class_section_id`,`semester`,`school_year`);
--
-- Indexes for table `teacher_submission_notification_history`
--
ALTER TABLE `teacher_submission_notification_history`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_teacher_class` (`teacher_id`,`class_section_id`);
--
-- Indexes for table `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `user_notifications`
--
ALTER TABLE `user_notifications`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `user_preferences`
--
ALTER TABLE `user_preferences`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `user_roles`
--
ALTER TABLE `user_roles`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `whatsapp_group_links`
--
ALTER TABLE `whatsapp_group_links`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uq_section_term` (`class_section_id`,`school_year`,`semester`);
--
-- Indexes for table `whatsapp_group_memberships`
--
ALTER TABLE `whatsapp_group_memberships`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uniq_whatsapp_membership` (`class_section_id`,`school_year`,`semester`,`subject_type`,`subject_id`),
ADD KEY `class_section_id_school_year_semester` (`class_section_id`,`school_year`,`semester`);
--
-- Indexes for table `whatsapp_invites_log`
--
ALTER TABLE `whatsapp_invites_log`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `additional_charges`
--
ALTER TABLE `additional_charges`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `admin_notification_subjects`
--
ALTER TABLE `admin_notification_subjects`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `attendance_comment_template`
--
ALTER TABLE `attendance_comment_template`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `attendance_data`
--
ALTER TABLE `attendance_data`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `attendance_day`
--
ALTER TABLE `attendance_day`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `attendance_record`
--
ALTER TABLE `attendance_record`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `attendance_tracking`
--
ALTER TABLE `attendance_tracking`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `authorized_users`
--
ALTER TABLE `authorized_users`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `badge_print_logs`
--
ALTER TABLE `badge_print_logs`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `calendar_events`
--
ALTER TABLE `calendar_events`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `chapters`
--
ALTER TABLE `chapters`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `classes`
--
ALTER TABLE `classes`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `classSection`
--
ALTER TABLE `classSection`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `class_preparation_log`
--
ALTER TABLE `class_preparation_log`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `class_prep_adjustments`
--
ALTER TABLE `class_prep_adjustments`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `class_progress_attachments`
--
ALTER TABLE `class_progress_attachments`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `class_progress_reports`
--
ALTER TABLE `class_progress_reports`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=67;
--
-- AUTO_INCREMENT for table `competitions`
--
ALTER TABLE `competitions`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `competition_class_winners`
--
ALTER TABLE `competition_class_winners`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
--
-- AUTO_INCREMENT for table `competition_scores`
--
ALTER TABLE `competition_scores`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=346;
--
-- AUTO_INCREMENT for table `competition_winners`
--
ALTER TABLE `competition_winners`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1046;
--
-- AUTO_INCREMENT for table `configuration`
--
ALTER TABLE `configuration`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=52;
--
-- AUTO_INCREMENT for table `contactus`
--
ALTER TABLE `contactus`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `current_flag`
--
ALTER TABLE `current_flag`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `discount_usages`
--
ALTER TABLE `discount_usages`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=44;
--
-- AUTO_INCREMENT for table `discount_vouchers`
--
ALTER TABLE `discount_vouchers`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
--
-- AUTO_INCREMENT for table `early_dismissal_signatures`
--
ALTER TABLE `early_dismissal_signatures`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `email_templates`
--
ALTER TABLE `email_templates`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `emergency_contacts`
--
ALTER TABLE `emergency_contacts`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `enrollments`
--
ALTER TABLE `enrollments`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `events`
--
ALTER TABLE `events`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `event_charges`
--
ALTER TABLE `event_charges`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `examquestiondistribution`
--
ALTER TABLE `examquestiondistribution`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `examquestions`
--
ALTER TABLE `examquestions`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `exams`
--
ALTER TABLE `exams`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `exam_drafts`
--
ALTER TABLE `exam_drafts`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `expenses`
--
ALTER TABLE `expenses`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=67;
--
-- AUTO_INCREMENT for table `families`
--
ALTER TABLE `families`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=146;
--
-- AUTO_INCREMENT for table `family_comm_prefs`
--
ALTER TABLE `family_comm_prefs`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `family_guardians`
--
ALTER TABLE `family_guardians`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1029;
--
-- AUTO_INCREMENT for table `family_students`
--
ALTER TABLE `family_students`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `final_exam`
--
ALTER TABLE `final_exam`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `final_score`
--
ALTER TABLE `final_score`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `flag`
--
ALTER TABLE `flag`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `grading_locks`
--
ALTER TABLE `grading_locks`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `homework`
--
ALTER TABLE `homework`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `inventory_categories`
--
ALTER TABLE `inventory_categories`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=43;
--
-- AUTO_INCREMENT for table `inventory_items`
--
ALTER TABLE `inventory_items`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=34;
--
-- AUTO_INCREMENT for table `inventory_movements`
--
ALTER TABLE `inventory_movements`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `invoices`
--
ALTER TABLE `invoices`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `invoice_event`
--
ALTER TABLE `invoice_event`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `invoice_students_list`
--
ALTER TABLE `invoice_students_list`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `ip_attempts`
--
ALTER TABLE `ip_attempts`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `late_slip_logs`
--
ALTER TABLE `late_slip_logs`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `login_activity`
--
ALTER TABLE `login_activity`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `longanswerquestions`
--
ALTER TABLE `longanswerquestions`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `manual_payments`
--
ALTER TABLE `manual_payments`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `messages`
--
ALTER TABLE `messages`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `midterm_exam`
--
ALTER TABLE `midterm_exam`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `migrations`
--
ALTER TABLE `migrations`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `missing_score_overrides`
--
ALTER TABLE `missing_score_overrides`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `nav_items`
--
ALTER TABLE `nav_items`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `notification_recipients`
--
ALTER TABLE `notification_recipients`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `parents`
--
ALTER TABLE `parents`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `parent_attendance_reports`
--
ALTER TABLE `parent_attendance_reports`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `parent_meeting_schedules`
--
ALTER TABLE `parent_meeting_schedules`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `parent_notifications`
--
ALTER TABLE `parent_notifications`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `participation`
--
ALTER TABLE `participation`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `password_resets`
--
ALTER TABLE `password_resets`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `password_reset_requests`
--
ALTER TABLE `password_reset_requests`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `payments`
--
ALTER TABLE `payments`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `payment_error`
--
ALTER TABLE `payment_error`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `payment_notification_logs`
--
ALTER TABLE `payment_notification_logs`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `payment_transactions`
--
ALTER TABLE `payment_transactions`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `paypal_payments`
--
ALTER TABLE `paypal_payments`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `paypal_transactions`
--
ALTER TABLE `paypal_transactions`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `permissions`
--
ALTER TABLE `permissions`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `placement_batches`
--
ALTER TABLE `placement_batches`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `placement_levels`
--
ALTER TABLE `placement_levels`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `placement_scores`
--
ALTER TABLE `placement_scores`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `preferences`
--
ALTER TABLE `preferences`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `print_requests`
--
ALTER TABLE `print_requests`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `project`
--
ALTER TABLE `project`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `promotion_queue`
--
ALTER TABLE `promotion_queue`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `qcmquestions`
--
ALTER TABLE `qcmquestions`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `questions`
--
ALTER TABLE `questions`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `questionstruefalse`
--
ALTER TABLE `questionstruefalse`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `questiontypes`
--
ALTER TABLE `questiontypes`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `quiz`
--
ALTER TABLE `quiz`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `refunds`
--
ALTER TABLE `refunds`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `reimbursements`
--
ALTER TABLE `reimbursements`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=30;
--
-- AUTO_INCREMENT for table `reimbursement_batches`
--
ALTER TABLE `reimbursement_batches`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
--
-- AUTO_INCREMENT for table `reimbursement_batch_admin_files`
--
ALTER TABLE `reimbursement_batch_admin_files`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `reimbursement_batch_items`
--
ALTER TABLE `reimbursement_batch_items`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=133;
--
-- AUTO_INCREMENT for table `report_card_acknowledgements`
--
ALTER TABLE `report_card_acknowledgements`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;
--
-- AUTO_INCREMENT for table `role_nav_items`
--
ALTER TABLE `role_nav_items`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `role_permissions`
--
ALTER TABLE `role_permissions`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `scan_log`
--
ALTER TABLE `scan_log`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `score_comments`
--
ALTER TABLE `score_comments`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `sections`
--
ALTER TABLE `sections`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `semester_scores`
--
ALTER TABLE `semester_scores`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `settings`
--
ALTER TABLE `settings`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `shortanswerquestions`
--
ALTER TABLE `shortanswerquestions`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `staff`
--
ALTER TABLE `staff`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `staff_attendance`
--
ALTER TABLE `staff_attendance`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `students`
--
ALTER TABLE `students`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=261;
--
-- AUTO_INCREMENT for table `student_allergies`
--
ALTER TABLE `student_allergies`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `student_class`
--
ALTER TABLE `student_class`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `student_medical_conditions`
--
ALTER TABLE `student_medical_conditions`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `subject_curriculum_items`
--
ALTER TABLE `subject_curriculum_items`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `support_requests`
--
ALTER TABLE `support_requests`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `teacher_attendance_data`
--
ALTER TABLE `teacher_attendance_data`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `teacher_class`
--
ALTER TABLE `teacher_class`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `teacher_submission_notification_history`
--
ALTER TABLE `teacher_submission_notification_history`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=312;
--
-- AUTO_INCREMENT for table `user_notifications`
--
ALTER TABLE `user_notifications`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `user_preferences`
--
ALTER TABLE `user_preferences`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `user_roles`
--
ALTER TABLE `user_roles`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `whatsapp_group_links`
--
ALTER TABLE `whatsapp_group_links`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `whatsapp_group_memberships`
--
ALTER TABLE `whatsapp_group_memberships`
MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `whatsapp_invites_log`
--
ALTER TABLE `whatsapp_invites_log`
MODIFY `id` int NOT NULL AUTO_INCREMENT;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `attendance_tracking`
--
ALTER TABLE `attendance_tracking`
ADD CONSTRAINT `attendance_tracking_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `class_progress_attachments`
--
ALTER TABLE `class_progress_attachments`
ADD CONSTRAINT `fk_class_progress_attachments_report_id` FOREIGN KEY (`report_id`) REFERENCES `class_progress_reports` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `exam_drafts`
--
ALTER TABLE `exam_drafts`
ADD CONSTRAINT `fk_exam_drafts_previous` FOREIGN KEY (`previous_draft_id`) REFERENCES `exam_drafts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
--
-- Constraints for table `family_comm_prefs`
--
ALTER TABLE `family_comm_prefs`
ADD CONSTRAINT `family_comm_prefs_family_id_foreign` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `family_guardians`
--
ALTER TABLE `family_guardians`
ADD CONSTRAINT `family_guardians_family_id_foreign` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `family_guardians_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `family_students`
--
ALTER TABLE `family_students`
ADD CONSTRAINT `family_students_family_id_foreign` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `family_students_student_id_foreign` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `inventory_items`
--
ALTER TABLE `inventory_items`
ADD CONSTRAINT `inventory_items_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `inventory_categories` (`id`) ON DELETE CASCADE ON UPDATE SET NULL;
--
-- Constraints for table `inventory_movements`
--
ALTER TABLE `inventory_movements`
ADD CONSTRAINT `inventory_movements_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `inventory_items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `parent_attendance_reports`
--
ALTER TABLE `parent_attendance_reports`
ADD CONSTRAINT `parent_attendance_reports_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `parent_attendance_reports_student_id_foreign` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `placement_levels`
--
ALTER TABLE `placement_levels`
ADD CONSTRAINT `fk_placement_levels_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT `fk_placement_levels_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `fk_placement_levels_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
--
-- Constraints for table `print_requests`
--
ALTER TABLE `print_requests`
ADD CONSTRAINT `print_requests_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT `print_requests_teacher_id_foreign` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `reimbursement_batches`
--
ALTER TABLE `reimbursement_batches`
ADD CONSTRAINT `fk_batch_closed_by` FOREIGN KEY (`closed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
ADD CONSTRAINT `fk_batch_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;
--
-- Constraints for table `reimbursement_batch_items`
--
ALTER TABLE `reimbursement_batch_items`
ADD CONSTRAINT `fk_batch_items_admin` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
ADD CONSTRAINT `fk_batch_items_batch` FOREIGN KEY (`batch_id`) REFERENCES `reimbursement_batches` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `fk_batch_items_expense` FOREIGN KEY (`expense_id`) REFERENCES `expenses` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `fk_batch_items_reimbursement` FOREIGN KEY (`reimbursement_id`) REFERENCES `reimbursements` (`id`) ON DELETE SET NULL;
--
-- Constraints for table `staff_attendance`
--
ALTER TABLE `staff_attendance`
ADD CONSTRAINT `staff_attendance_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `student_allergies`
--
ALTER TABLE `student_allergies`
ADD CONSTRAINT `student_allergies_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;
--
-- Constraints for table `student_medical_conditions`
--
ALTER TABLE `student_medical_conditions`
ADD CONSTRAINT `student_medical_conditions_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Reference in New Issue View Git Blame Copy Permalink