DROP TABLE IF EXISTS `attendance`;
CREATE TABLE `attendance` (
  `attendance_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_type` enum('Student','Staff') NOT NULL,
  `user_id` int(11) NOT NULL,
  `attendance_date` date NOT NULL,
  `status` enum('Present','Absent') NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`attendance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
  `class_id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(100) NOT NULL,
  PRIMARY KEY (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

INSERT INTO `classes` (`class_id`, `class_name`) VALUES ('1', 'Class1');

DROP TABLE IF EXISTS `daily_expenses`;
CREATE TABLE `daily_expenses` (
  `expense_id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_type` varchar(100) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `description` text DEFAULT NULL,
  `date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`expense_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO `daily_expenses` (`expense_id`, `expense_type`, `amount`, `description`, `date`, `created_at`) VALUES ('1', 'Rent', '3000.00', 'dgdfg', '2025-09-29', '2025-09-29 11:41:54');
INSERT INTO `daily_expenses` (`expense_id`, `expense_type`, `amount`, `description`, `date`, `created_at`) VALUES ('2', 'dkjfklsjf', '1200.00', 'skljfkdl', '2025-10-06', '2025-10-06 19:08:02');

DROP TABLE IF EXISTS `extra_fees`;
CREATE TABLE `extra_fees` (
  `extra_fee_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `fee_type` enum('Books','Uniforms','Transport','Other') NOT NULL,
  `total_amount` decimal(12,2) NOT NULL,
  `paid` decimal(12,2) DEFAULT 0.00,
  `balance` decimal(12,2) DEFAULT 0.00,
  `payment_date` date DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`extra_fee_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `extra_fees_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

INSERT INTO `extra_fees` (`extra_fee_id`, `student_id`, `fee_type`, `total_amount`, `paid`, `balance`, `payment_date`, `remarks`, `created_at`) VALUES ('1', '1', 'Books', '4000.00', '4000.00', '0.00', '2025-09-29', 'sfsdfdf', '2025-09-29 11:02:15');
INSERT INTO `extra_fees` (`extra_fee_id`, `student_id`, `fee_type`, `total_amount`, `paid`, `balance`, `payment_date`, `remarks`, `created_at`) VALUES ('2', '1', 'Uniforms', '4000.00', '4000.00', '0.00', '2025-09-29', 'rtrt', '2025-09-29 11:05:10');
INSERT INTO `extra_fees` (`extra_fee_id`, `student_id`, `fee_type`, `total_amount`, `paid`, `balance`, `payment_date`, `remarks`, `created_at`) VALUES ('3', '1', 'Books', '200.00', '200.00', '0.00', '2025-09-30', 'slkfjsdkf', '2025-09-30 15:43:41');
INSERT INTO `extra_fees` (`extra_fee_id`, `student_id`, `fee_type`, `total_amount`, `paid`, `balance`, `payment_date`, `remarks`, `created_at`) VALUES ('4', '1', 'Books', '900.00', '900.00', '0.00', '2025-10-02', 'm,n,mnn,mn', '2025-10-02 11:11:54');
INSERT INTO `extra_fees` (`extra_fee_id`, `student_id`, `fee_type`, `total_amount`, `paid`, `balance`, `payment_date`, `remarks`, `created_at`) VALUES ('5', '3', 'Books', '2000.00', '1000.00', '1000.00', '2025-10-06', 'jfsklfjkd', '2025-10-06 19:05:42');

DROP TABLE IF EXISTS `question_bank`;
CREATE TABLE `question_bank` (
  `question_id` int(11) NOT NULL AUTO_INCREMENT,
  `subject_id` int(11) NOT NULL,
  `question_type` enum('Multiple Choice','True/False','Short Answer','Long Answer') NOT NULL,
  `question_text` text NOT NULL,
  `option_a` varchar(255) DEFAULT NULL,
  `option_b` varchar(255) DEFAULT NULL,
  `option_c` varchar(255) DEFAULT NULL,
  `option_d` varchar(255) DEFAULT NULL,
  `correct_option` varchar(5) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`question_id`),
  KEY `subject_id` (`subject_id`),
  CONSTRAINT `question_bank_ibfk_1` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

INSERT INTO `question_bank` (`question_id`, `subject_id`, `question_type`, `question_text`, `option_a`, `option_b`, `option_c`, `option_d`, `correct_option`, `created_at`) VALUES ('2', '1', 'Long Answer', '?what is computer', '', '', '', '', '', '2025-10-02 12:01:47');
INSERT INTO `question_bank` (`question_id`, `subject_id`, `question_type`, `question_text`, `option_a`, `option_b`, `option_c`, `option_d`, `correct_option`, `created_at`) VALUES ('3', '1', 'Long Answer', 'stggdfsgfdg', '', '', '', '', '', '2025-10-02 12:41:05');
INSERT INTO `question_bank` (`question_id`, `subject_id`, `question_type`, `question_text`, `option_a`, `option_b`, `option_c`, `option_d`, `correct_option`, `created_at`) VALUES ('4', '1', 'Multiple Choice', 'dfgfdsg', 'fasdf', 'sdfsdaf', 'sdfasdf', 'sdfasdf', '', '2025-10-02 12:48:24');
INSERT INTO `question_bank` (`question_id`, `subject_id`, `question_type`, `question_text`, `option_a`, `option_b`, `option_c`, `option_d`, `correct_option`, `created_at`) VALUES ('5', '1', 'Multiple Choice', 'dfgfdsg', 'fasdf', 'sdfsdaf', 'sdfasdf', 'sdfasdf', '', '2025-10-02 12:51:21');

DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff` (
  `staff_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `staff_name` varchar(100) NOT NULL,
  `role` enum('Teacher','Employee') NOT NULL,
  `qualification` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `staff_image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`staff_id`),
  KEY `user_id` (`user_id`),
  KEY `subject_id` (`subject_id`),
  CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `staff_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO `staff` (`staff_id`, `user_id`, `staff_name`, `role`, `qualification`, `phone`, `subject_id`, `address`, `staff_image`, `created_at`) VALUES ('1', '', 'Jan', 'Teacher', 'Bachelor', '0781113616', '', 'کابل – بګرامي', 'assets/uploads/staff_1759122303_5838.PNG', '2025-09-29 09:35:03');
INSERT INTO `staff` (`staff_id`, `user_id`, `staff_name`, `role`, `qualification`, `phone`, `subject_id`, `address`, `staff_image`, `created_at`) VALUES ('2', '', 'Jan', 'Teacher', 'Bachelor', '0781113616', '', 'کابل – بګرامي', 'assets/uploads/staff_1759122796_1063.PNG', '2025-09-29 09:43:16');

DROP TABLE IF EXISTS `staff_permissions`;
CREATE TABLE `staff_permissions` (
  `user_id` int(11) NOT NULL,
  `permissions` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `staff_permissions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `staff_repayments`;
CREATE TABLE `staff_repayments` (
  `repayment_id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) NOT NULL,
  `salary_id` int(11) NOT NULL,
  `amount_paid` decimal(12,2) NOT NULL,
  `payment_date` date NOT NULL,
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`repayment_id`),
  KEY `staff_id` (`staff_id`),
  KEY `salary_id` (`salary_id`),
  CONSTRAINT `staff_repayments_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE,
  CONSTRAINT `staff_repayments_ibfk_2` FOREIGN KEY (`salary_id`) REFERENCES `staff_salaries` (`salary_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

INSERT INTO `staff_repayments` (`repayment_id`, `staff_id`, `salary_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('3', '1', '1', '5345.00', '2025-10-04', '4545', '2025-10-04 11:59:20');

DROP TABLE IF EXISTS `staff_salaries`;
CREATE TABLE `staff_salaries` (
  `salary_id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) DEFAULT NULL,
  `salary_amount` decimal(12,2) NOT NULL,
  `paid` decimal(12,2) DEFAULT 0.00,
  `balance` decimal(12,2) DEFAULT 0.00,
  `paid_date` date DEFAULT NULL,
  `bonus` decimal(12,2) DEFAULT 0.00,
  `deductions` decimal(12,2) DEFAULT 0.00,
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`salary_id`),
  KEY `staff_id` (`staff_id`),
  CONSTRAINT `staff_salaries_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO `staff_salaries` (`salary_id`, `staff_id`, `salary_amount`, `paid`, `balance`, `paid_date`, `bonus`, `deductions`, `remarks`, `created_at`) VALUES ('1', '1', '4343.00', '5688.00', '-1345.00', '0000-00-00', '0.00', '0.00', 'aerw', '2025-09-29 11:37:01');
INSERT INTO `staff_salaries` (`salary_id`, `staff_id`, `salary_amount`, `paid`, `balance`, `paid_date`, `bonus`, `deductions`, `remarks`, `created_at`) VALUES ('2', '1', '12000.00', '2000.00', '10100.00', '0000-00-00', '200.00', '100.00', 'sflkajfkdjf', '2025-10-06 19:07:27');

DROP TABLE IF EXISTS `student_fees`;
CREATE TABLE `student_fees` (
  `fee_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `month` varchar(20) NOT NULL,
  `year` int(11) NOT NULL,
  `monthly_fee` decimal(12,2) NOT NULL,
  `paid` decimal(12,2) DEFAULT 0.00,
  `balance` decimal(12,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`fee_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `student_fees_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

INSERT INTO `student_fees` (`fee_id`, `student_id`, `month`, `year`, `monthly_fee`, `paid`, `balance`, `created_at`) VALUES ('4', '1', '0', '2025', '2000.00', '2000.00', '0.00', '2025-09-29 10:26:11');
INSERT INTO `student_fees` (`fee_id`, `student_id`, `month`, `year`, `monthly_fee`, `paid`, `balance`, `created_at`) VALUES ('5', '1', 'February', '2025', '300.00', '300.00', '0.00', '2025-09-29 10:39:19');
INSERT INTO `student_fees` (`fee_id`, `student_id`, `month`, `year`, `monthly_fee`, `paid`, `balance`, `created_at`) VALUES ('6', '2', 'January', '2025', '50000.00', '12200.00', '37800.00', '2025-10-06 07:54:39');
INSERT INTO `student_fees` (`fee_id`, `student_id`, `month`, `year`, `monthly_fee`, `paid`, `balance`, `created_at`) VALUES ('7', '2', 'January', '2025', '300.00', '200.00', '100.00', '2025-10-06 19:04:30');

DROP TABLE IF EXISTS `student_repayments`;
CREATE TABLE `student_repayments` (
  `repayment_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `related_fee` enum('Tuition','Extra') NOT NULL,
  `related_id` int(11) NOT NULL,
  `amount_paid` decimal(12,2) NOT NULL,
  `payment_date` date NOT NULL,
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`repayment_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `student_repayments_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;

INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('1', '1', 'Tuition', '4', '900.00', '2025-10-04', 'fggh', '2025-10-04 09:06:34');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('2', '1', 'Tuition', '5', '200.00', '2025-10-04', 'fggh', '2025-10-04 09:06:34');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('3', '1', 'Extra', '1', '3900.00', '2025-10-04', 'fggh', '2025-10-04 09:06:34');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('4', '1', 'Extra', '2', '1000.00', '2025-10-04', 'fggh', '2025-10-04 09:06:34');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('5', '1', 'Extra', '2', '1000.00', '2025-10-04', 'dfgfdg', '2025-10-04 09:07:07');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('6', '1', 'Extra', '3', '100.00', '2025-10-04', 'dfgfdg', '2025-10-04 09:07:07');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('7', '1', 'Extra', '4', '200.00', '2025-10-04', 'dfgfdg', '2025-10-04 09:07:07');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('8', '1', 'Tuition', '0', '300.00', '2025-10-04', 'asdfdf', '2025-10-04 10:42:39');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('9', '1', 'Tuition', '0', '300.00', '2025-10-04', 'asdfdf', '2025-10-04 11:02:58');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('10', '1', 'Tuition', '0', '300.00', '2025-10-04', 'asdfdf', '2025-10-04 11:03:07');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('11', '2', '', '0', '9000.00', '2025-10-06', 'sdfd', '2025-10-06 07:56:32');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('12', '2', '', '0', '3000.00', '2025-10-06', '', '2025-10-06 19:10:49');

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `student_name` varchar(100) NOT NULL,
  `father_name` varchar(100) DEFAULT NULL,
  `grandfather_name` varchar(100) DEFAULT NULL,
  `student_class` int(11) DEFAULT NULL,
  `student_phone` varchar(20) DEFAULT NULL,
  `gender` enum('Male','Female') DEFAULT 'Male',
  `date_of_birth` date DEFAULT NULL,
  `current_address` text DEFAULT NULL,
  `permanent_address` text DEFAULT NULL,
  `student_image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`student_id`),
  KEY `user_id` (`user_id`),
  KEY `student_class` (`student_class`),
  CONSTRAINT `students_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `students_ibfk_2` FOREIGN KEY (`student_class`) REFERENCES `classes` (`class_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

INSERT INTO `students` (`student_id`, `user_id`, `student_name`, `father_name`, `grandfather_name`, `student_class`, `student_phone`, `gender`, `date_of_birth`, `current_address`, `permanent_address`, `student_image`, `created_at`) VALUES ('1', '', 'Khan', 'Jan', 'ali', '1', '0783332428', 'Male', '2025-09-29', 'nanagarhar', 'kabul', 'assets/uploads/students_1759118875_7914.jpg', '2025-09-29 08:28:30');
INSERT INTO `students` (`student_id`, `user_id`, `student_name`, `father_name`, `grandfather_name`, `student_class`, `student_phone`, `gender`, `date_of_birth`, `current_address`, `permanent_address`, `student_image`, `created_at`) VALUES ('2', '', 'Shabir', 'Khan', 'Jan', '1', '045345345', 'Male', '2025-10-06', 'gdfg', 'adfas', 'assets/uploads/students_1759721056_7634.jpg', '2025-10-06 07:54:16');
INSERT INTO `students` (`student_id`, `user_id`, `student_name`, `father_name`, `grandfather_name`, `student_class`, `student_phone`, `gender`, `date_of_birth`, `current_address`, `permanent_address`, `student_image`, `created_at`) VALUES ('3', '', 'dada', 'Khan', 'baba', '1', '07878787', 'Male', '2025-10-06', 'jan', 'skjfjkdfh', 'assets/uploads/students_1759761118_1596.JPG', '2025-10-06 19:01:58');

DROP TABLE IF EXISTS `subjects`;
CREATE TABLE `subjects` (
  `subject_id` int(11) NOT NULL AUTO_INCREMENT,
  `class_id` int(11) DEFAULT NULL,
  `subject_name` varchar(100) NOT NULL,
  PRIMARY KEY (`subject_id`),
  KEY `class_id` (`class_id`),
  CONSTRAINT `subjects_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO `subjects` (`subject_id`, `class_id`, `subject_name`) VALUES ('1', '', 'English');
INSERT INTO `subjects` (`subject_id`, `class_id`, `subject_name`) VALUES ('2', '', 'Dari');

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `role` enum('Admin','Staff') NOT NULL DEFAULT 'Staff',
  `password` varchar(255) NOT NULL,
  `user_image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

INSERT INTO `users` (`user_id`, `username`, `role`, `password`, `user_image`, `created_at`) VALUES ('2', 'samim', 'Admin', '$2y$10$aejxBCGCbxtT1PcHSsk8Q.AK61PE4Vftbdt7BeGSBaY/8e6ypso6y', 'assets/uploads/user_1759385890.jpg', '2025-10-02 10:48:10');
INSERT INTO `users` (`user_id`, `username`, `role`, `password`, `user_image`, `created_at`) VALUES ('3', 'nasrat', 'Staff', '$2y$10$CSArl8lLWxuI5pK0NKgKsOE6yPD.3bG5Hpt1POkkYNEi39QvpxBEa', 'assets/uploads/user_1759385922.jpg', '2025-10-02 10:48:42');