-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `itrschool`
--

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

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

CREATE TABLE IF NOT EXISTS `classes` (
  `id` smallint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_class_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `class_fees`
--

CREATE TABLE IF NOT EXISTS `class_fees` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `class_id` smallint UNSIGNED NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `head_id` int UNSIGNED NOT NULL,
  `monthly_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_class_year_head` (`class_id`,`academic_year`,`head_id`),
  KEY `idx_class_fee_head` (`head_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `expenses`
--

CREATE TABLE IF NOT EXISTS `expenses` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `account_id` int UNSIGNED NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_expenses_account` (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `expense_accounts`
--

CREATE TABLE IF NOT EXISTS `expense_accounts` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` int UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_expense_accounts_category` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `expense_accounts`
--

INSERT INTO `expense_accounts` (`id`, `category_id`, `name`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 2, 'Electricity bill', 'Monthly electricity', 1, '2026-03-05 16:53:22', NULL),
(2, 2, 'Water bill', 'Water supply', 1, '2026-03-05 16:53:22', NULL),
(3, 1, 'Office rent', 'Monthly office rent', 1, '2026-03-05 16:53:22', NULL),
(4, 5, 'Teacher Salary', 'Teacher salary payments', 1, '2026-03-05 16:53:22', NULL);

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

--
-- Table structure for table `expense_categories`
--

CREATE TABLE IF NOT EXISTS `expense_categories` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `expense_categories`
--

INSERT INTO `expense_categories` (`id`, `name`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Rent', 'Building / premises rent', 1, '2026-03-04 19:22:24', NULL),
(2, 'Utilities', 'Electricity, water, gas', 1, '2026-03-04 19:22:24', NULL),
(3, 'Supplies', 'Stationery and general supplies', 1, '2026-03-04 19:22:24', NULL),
(4, 'Maintenance', 'Repairs and maintenance', 1, '2026-03-04 19:22:24', NULL),
(5, 'Salaries', 'Staff salary payments', 1, '2026-03-04 19:22:24', NULL),
(6, 'Miscellaneous', 'Other expenses', 1, '2026-03-04 19:22:24', NULL);

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

--
-- Table structure for table `fee_heads`
--

CREATE TABLE IF NOT EXISTS `fee_heads` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `is_recurring` enum('monthly','one_time') NOT NULL DEFAULT 'monthly',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_fee_head_code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

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

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

CREATE TABLE IF NOT EXISTS `fee_payments` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `student_id` int UNSIGNED NOT NULL,
  `fee_item_id` int UNSIGNED NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `payment_date` date NOT NULL,
  `mode` varchar(20) NOT NULL DEFAULT 'cash',
  `reference_no` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_payment_student` (`student_id`),
  KEY `idx_payment_fee_item` (`fee_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `fee_plans`
--

CREATE TABLE IF NOT EXISTS `fee_plans` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_fee_plans_year` (`academic_year`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `fee_plan_items`
--

CREATE TABLE IF NOT EXISTS `fee_plan_items` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `plan_id` int UNSIGNED NOT NULL,
  `head_id` int UNSIGNED NOT NULL,
  `monthly_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_plan_head` (`plan_id`,`head_id`),
  KEY `idx_fpi_head` (`head_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `rights`
--

CREATE TABLE IF NOT EXISTS `rights` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_right_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `rights`
--

INSERT INTO `rights` (`id`, `name`, `description`, `category`) VALUES
(2, 'Students', 'View/manage students', 'Students'),
(3, 'Teachers', 'View/manage teachers', 'Teachers'),
(4, 'Classes', 'View/manage classes', 'Academic'),
(5, 'Sections', 'View/manage sections', 'Academic'),
(6, 'Users', 'View users list', 'User Management'),
(7, 'Create User', 'Create new user', 'User Management'),
(8, 'Create Role', 'Create new role', 'User Management'),
(9, 'Assign Rights', 'Assign rights to roles', 'User Management'),
(10, 'Fee Heads', 'Manage fee heads', 'Fees'),
(11, 'Class Fees', 'Manage class fees', 'Fees'),
(12, 'Generate Fees', 'Generate monthly fees', 'Fees'),
(13, 'Student Fees', 'View student fee status', 'Fees'),
(14, 'Fee Voucher', 'Print/download vouchers', 'Fees'),
(15, 'Expense Accounts', 'Manage expense accounts', 'Expenses'),
(16, 'Expense Categories', 'Manage expense categories', 'Expenses'),
(17, 'Add Expense', 'Create expense entry', 'Expenses'),
(18, 'Expenses', 'View expense list', 'Expenses'),
(19, 'Generate Salary', 'Generate monthly salary', 'Salary'),
(20, 'Salary', 'View/process salary', 'Salary'),
(21, 'Suggestions', 'View/post suggestions', 'Communication'),
(22, 'Admin Dashboard', 'Access admin dashboard page', 'Dashboard'),
(23, 'Teacher Dashboard', 'Access teacher dashboard page', 'Dashboard'),
(24, 'Student Dashboard', 'Access student dashboard page', 'Dashboard');

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

--
-- Table structure for table `roles`
--

CREATE TABLE IF NOT EXISTS `roles` (
  `id` tinyint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_role_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`id`, `name`) VALUES
(1, 'admin'),
(3, 'student'),
(2, 'teacher');

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

--
-- Table structure for table `role_rights`
--

CREATE TABLE IF NOT EXISTS `role_rights` (
  `role_id` tinyint UNSIGNED NOT NULL,
  `right_id` int UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`,`right_id`),
  KEY `idx_role_rights_right_id` (`right_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `role_rights`
--

INSERT INTO `role_rights` (`role_id`, `right_id`) VALUES
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(1, 7),
(1, 8),
(1, 9),
(1, 10),
(1, 11),
(1, 12),
(1, 13),
(1, 14),
(1, 15),
(1, 16),
(1, 17),
(1, 18),
(1, 19),
(1, 20),
(1, 21),
(1, 22),
(1, 23),
(2, 23),
(1, 24),
(3, 24);

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

--
-- Table structure for table `school_info`
--

CREATE TABLE IF NOT EXISTS `school_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `school_name` varchar(255) DEFAULT NULL,
  `school_address` text,
  `logo_path` varchar(255) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `license_key` varchar(100) DEFAULT NULL,
  `machine_id` varchar(255) DEFAULT NULL,
  `last_verified_at` datetime DEFAULT NULL,
  `license_cache` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `school_info`
--

INSERT INTO `school_info` (`id`, `school_name`, `school_address`, `logo_path`, `phone_number`, `license_key`, `machine_id`, `last_verified_at`, `license_cache`, `created_at`) VALUES
(1, 'Demo License Asnaff', 'Sanam Chowk Islamabad', NULL, NULL, 'ITR-FC8A-6E26-5473', 'a16ee611bda151a9394f58d30e854b98', '2026-05-24 19:48:01', '{\"success\":true,\"message\":\"ITR School license verified successfully.\",\"type\":\"real\",\"branch_name\":\"Demo License Asnaff\",\"branch_address\":\"Sanam Chowk Islamabad\",\"software\":\"ITR School\",\"support_end_date\":\"2026-12-31\"}', '2026-05-24 14:47:59');

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

--
-- Table structure for table `sections`
--

CREATE TABLE IF NOT EXISTS `sections` (
  `id` smallint UNSIGNED NOT NULL AUTO_INCREMENT,
  `class_id` smallint UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_class_section` (`class_id`,`name`),
  KEY `idx_sections_class_id` (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `settings`
--

CREATE TABLE IF NOT EXISTS `settings` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` int UNSIGNED NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` enum('boolean','text','number','select') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'select',
  `value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `default_value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_settings_code` (`code`),
  KEY `idx_settings_category` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`id`, `category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) VALUES
(1, 1, 'Fee collection voucher print', 'fee_collection_voucher_print_format', 'select', 'thermal', 'thermal', 'After fee payment: open thermal (80mm) receipt or A4 payment voucher for printing.', 1),
(2, 1, 'Fee demand voucher print', 'fee_demand_voucher_print_format', 'select', 'a4', 'a4', 'Fees list voucher (before payment): thermal, A4, or WhatsApp.', 1);

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

--
-- Table structure for table `setting_categories`
--

CREATE TABLE IF NOT EXISTS `setting_categories` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_setting_categories_code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `setting_categories`
--

INSERT INTO `setting_categories` (`id`, `name`, `code`) VALUES
(1, 'Print Settings', 'print_settings');

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

--
-- Table structure for table `setting_options`
--

CREATE TABLE IF NOT EXISTS `setting_options` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_id` int UNSIGNED NOT NULL,
  `option_label` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `option_value` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_setting_options_setting` (`setting_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `setting_options`
--

INSERT INTO `setting_options` (`id`, `setting_id`, `option_label`, `option_value`) VALUES
(1, 1, 'Thermal (80mm)', 'thermal'),
(2, 1, 'A4', 'a4'),
(3, 1, 'WhatsApp', 'whatsapp'),
(4, 2, 'Thermal (80mm)', 'thermal'),
(5, 2, 'A4', 'a4'),
(6, 2, 'WhatsApp', 'whatsapp');

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

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

CREATE TABLE IF NOT EXISTS `students` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int UNSIGNED DEFAULT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `gender` enum('male','female','other') DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `roll` varchar(50) DEFAULT NULL,
  `blood_group` varchar(10) DEFAULT NULL,
  `religion` varchar(50) DEFAULT NULL,
  `class_name` varchar(20) DEFAULT NULL,
  `section` varchar(10) DEFAULT NULL,
  `admission_id` varchar(50) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `whatsapp_number` varchar(20) DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_admission_id` (`admission_id`),
  KEY `idx_students_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `student_attendance`
--

CREATE TABLE IF NOT EXISTS `student_attendance` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `student_id` int UNSIGNED NOT NULL,
  `attendance_date` date NOT NULL,
  `status` enum('present','absent','late') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'present',
  `marked_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `session` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'e.g. morning, afternoon',
  `marked_by` int UNSIGNED DEFAULT NULL COMMENT 'user_id if logged in',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_student_date_session` (`student_id`,`attendance_date`,`session`),
  KEY `idx_attendance_date` (`attendance_date`),
  KEY `idx_attendance_student` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `student_fee_items`
--

CREATE TABLE IF NOT EXISTS `student_fee_items` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `student_id` int UNSIGNED NOT NULL,
  `head` varchar(100) NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `period_label` varchar(50) NOT NULL,
  `due_date` date NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `discount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `fine` decimal(10,2) NOT NULL DEFAULT '0.00',
  `paid_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `status` enum('unpaid','partial','paid') NOT NULL DEFAULT 'unpaid',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_fee_student` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `student_fee_overrides`
--

CREATE TABLE IF NOT EXISTS `student_fee_overrides` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `student_id` int UNSIGNED NOT NULL,
  `head_id` int UNSIGNED NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `monthly_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `note` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_student_head_year` (`student_id`,`head_id`,`academic_year`),
  KEY `idx_sfo_head` (`head_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `student_fee_plans`
--

CREATE TABLE IF NOT EXISTS `student_fee_plans` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `student_id` int UNSIGNED NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `plan_id` int UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_student_year_plan` (`student_id`,`academic_year`),
  KEY `idx_sfp_plan` (`plan_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `teachers`
--

CREATE TABLE IF NOT EXISTS `teachers` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int UNSIGNED DEFAULT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `gender` enum('male','female','other') DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `qualification` varchar(100) DEFAULT NULL,
  `joining_date` date DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_teachers_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `teacher_salary_items`
--

CREATE TABLE IF NOT EXISTS `teacher_salary_items` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `teacher_id` int UNSIGNED NOT NULL,
  `period_label` varchar(20) NOT NULL COMMENT 'e.g. 2025-01',
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `status` enum('pending','paid') NOT NULL DEFAULT 'pending',
  `paid_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_teacher_period` (`teacher_id`,`period_label`),
  KEY `idx_salary_teacher` (`teacher_id`),
  KEY `idx_salary_period` (`period_label`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

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

CREATE TABLE IF NOT EXISTS `users` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role_id` tinyint UNSIGNED NOT NULL DEFAULT '3',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`),
  UNIQUE KEY `uniq_email` (`email`),
  KEY `fk_users_role` (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

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

INSERT INTO `users` (`id`, `username`, `email`, `password`, `role_id`, `created_at`, `updated_at`) VALUES
(1, 'admin', 'admin@gmail.com', '$2y$10$8ddGvxr3GSLs0htRTyc9UOUOR82iAMoooQHPGEqogTALKbbb0QwO2', 1, '2026-03-03 16:23:45', NULL),
(2, 'student', 'student@gmail.com', '$2y$10$/SChFV.1zdxA.hmfcc5cYOQzpREpPmcZ03fXv6aeW3fE8861bfVwe', 3, '2026-03-03 16:48:00', '2026-03-24 15:08:42'),
(4, 'teacher', 'teacher@gmail.com', '$2y$10$BCCAFyk40X/iXsiaMFmvue0BJmvaAXbfydlhb781bxtnBHukYNDdS', 2, '2026-03-03 19:31:50', '2026-03-24 15:09:07');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `class_fees`
--
ALTER TABLE `class_fees`
  ADD CONSTRAINT `fk_class_fee_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_class_fee_head` FOREIGN KEY (`head_id`) REFERENCES `fee_heads` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `expenses`
--
ALTER TABLE `expenses`
  ADD CONSTRAINT `fk_expenses_account` FOREIGN KEY (`account_id`) REFERENCES `expense_accounts` (`id`) ON DELETE RESTRICT;

--
-- Constraints for table `expense_accounts`
--
ALTER TABLE `expense_accounts`
  ADD CONSTRAINT `fk_expense_accounts_category` FOREIGN KEY (`category_id`) REFERENCES `expense_categories` (`id`) ON DELETE RESTRICT;

--
-- Constraints for table `fee_payments`
--
ALTER TABLE `fee_payments`
  ADD CONSTRAINT `fk_payment_fee_item` FOREIGN KEY (`fee_item_id`) REFERENCES `student_fee_items` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_payment_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `fee_plan_items`
--
ALTER TABLE `fee_plan_items`
  ADD CONSTRAINT `fk_fpi_head` FOREIGN KEY (`head_id`) REFERENCES `fee_heads` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_fpi_plan` FOREIGN KEY (`plan_id`) REFERENCES `fee_plans` (`id`) ON DELETE CASCADE;

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

--
-- Constraints for table `settings`
--
ALTER TABLE `settings`
  ADD CONSTRAINT `fk_settings_category` FOREIGN KEY (`category_id`) REFERENCES `setting_categories` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `setting_options`
--
ALTER TABLE `setting_options`
  ADD CONSTRAINT `fk_setting_options_setting` FOREIGN KEY (`setting_id`) REFERENCES `settings` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `students`
--
ALTER TABLE `students`
  ADD CONSTRAINT `fk_students_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

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

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

--
-- Constraints for table `student_fee_overrides`
--
ALTER TABLE `student_fee_overrides`
  ADD CONSTRAINT `fk_sfo_head` FOREIGN KEY (`head_id`) REFERENCES `fee_heads` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_sfo_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `student_fee_plans`
--
ALTER TABLE `student_fee_plans`
  ADD CONSTRAINT `fk_sfp_plan` FOREIGN KEY (`plan_id`) REFERENCES `fee_plans` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_sfp_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `teachers`
--
ALTER TABLE `teachers`
  ADD CONSTRAINT `fk_teachers_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `teacher_salary_items`
--
ALTER TABLE `teacher_salary_items`
  ADD CONSTRAINT `fk_salary_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`);
COMMIT;

