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=2 DEFAULT CHARSET=utf8mb4;

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

DROP TABLE IF EXISTS `company_info`;
CREATE TABLE `company_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) NOT NULL,
  `company_address` varchar(255) NOT NULL,
  `company_phone` varchar(50) NOT NULL,
  `logo_left` varchar(255) DEFAULT 'assets/logo_left.jpg',
  `logo_right` varchar(255) DEFAULT 'assets/logo_right.jpg',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `company_info` (`id`, `company_name`, `company_address`, `company_phone`, `logo_left`, `logo_right`, `created_at`, `updated_at`) VALUES ('1', 'Melan High School', 'Kabul', '087878787', 'assets/1771520682_Abdul sobor F  monawar khan.jpg', 'assets/1771520682_Abdul Haeeb         Turab khan.JPG', '2026-02-19 21:34:42', '2026-02-19 21:34:42');

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', '20000.00', 's;ldlgfal;fafjakldfj', '2026-02-13', '2026-02-13 21:05:12');
INSERT INTO `daily_expenses` (`expense_id`, `expense_type`, `amount`, `description`, `date`, `created_at`) VALUES ('2', 'dsdfyr', '600.00', 'sdfdsf', '2026-03-01', '2026-03-01 11:58:48');

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=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO `extra_fees` (`extra_fee_id`, `student_id`, `fee_type`, `total_amount`, `paid`, `balance`, `payment_date`, `remarks`, `created_at`) VALUES ('2', '1', 'Books', '900.00', '200.00', '700.00', '2026-02-25', 'askjfklasjd', '2026-02-19 21:35:09');

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=3 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 ('1', '1', 'Multiple Choice', 'Computer is not an Electronic Machine', 'YES', 'YES NO', 'NO YES', 'NO', '', '2026-02-13 21:08:23');
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', 'Short Answer', 'Is computer is an animle ', '', '', '', '', '', '2026-02-13 21:08:49');

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=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `staff` (`staff_id`, `user_id`, `staff_name`, `role`, `qualification`, `phone`, `subject_id`, `address`, `staff_image`, `created_at`) VALUES ('1', '', 'Faridullah', 'Teacher', 'Bachelor', '0783332428', '', 'hkahfkajsfhd', 'assets/uploads/staff_1770999737_9990.jpg', '2026-02-13 20:52:17');

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

INSERT INTO `staff_permissions` (`id`, `user_id`, `permissions`) VALUES ('1', '3', '{\"students\":{\"sidebar\":\"off\",\"card\":\"off\"},\"staff\":{\"sidebar\":\"on\",\"card\":\"on\"},\"classes\":{\"sidebar\":\"on\",\"card\":\"on\"},\"subjects\":{\"sidebar\":\"off\",\"card\":\"off\"},\"student_fees\":{\"sidebar\":\"off\",\"card\":\"off\"},\"extra_fees\":{\"sidebar\":\"off\",\"card\":\"off\"},\"books\":{\"sidebar\":\"on\",\"card\":\"on\"},\"uniforms\":{\"sidebar\":\"off\",\"card\":\"off\"},\"transport\":{\"sidebar\":\"off\",\"card\":\"off\"},\"other\":{\"sidebar\":\"off\",\"card\":\"off\"},\"staff_salaries\":{\"sidebar\":\"on\",\"card\":\"on\"},\"daily_expenses\":{\"sidebar\":\"off\",\"card\":\"off\"},\"student_repayment\":{\"sidebar\":\"off\",\"card\":\"off\"},\"staff_repayment\":{\"sidebar\":\"off\",\"card\":\"off\"},\"total_income\":{\"sidebar\":\"off\",\"card\":\"off\"},\"total_expenses\":{\"sidebar\":\"off\",\"card\":\"off\"},\"net_income\":{\"sidebar\":\"off\",\"card\":\"off\"},\"question_bank\":{\"sidebar\":\"off\",\"card\":\"off\"},\"generate_paper\":{\"sidebar\":\"off\",\"card\":\"off\"},\"backup\":{\"sidebar\":\"off\",\"card\":\"off\"},\"permissions\":{\"sidebar\":\"off\",\"card\":\"off\"},\"users\":{\"sidebar\":\"off\",\"card\":\"off\"},\"profile\":{\"sidebar\":\"off\",\"card\":\"off\"},\"report\":{\"sidebar\":\"off\",\"card\":\"off\"}}');

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 DEFAULT CHARSET=utf8mb4;


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=4 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', '4000.00', '2000.00', '2100.00', '2026-02-19', '200.00', '100.00', 'sdlskjfaslkdfj', '2026-02-13 21:04:48');
INSERT INTO `staff_salaries` (`salary_id`, `staff_id`, `salary_amount`, `paid`, `balance`, `paid_date`, `bonus`, `deductions`, `remarks`, `created_at`) VALUES ('2', '1', '9000.00', '7000.00', '1900.00', '2026-02-17', '200.00', '300.00', 'KJLKJLK', '2026-02-17 14:20:19');
INSERT INTO `staff_salaries` (`salary_id`, `staff_id`, `salary_amount`, `paid`, `balance`, `paid_date`, `bonus`, `deductions`, `remarks`, `created_at`) VALUES ('3', '1', '8000.00', '6000.00', '1600.00', '2026-03-23', '100.00', '500.00', 'fdgdfg', '2026-03-01 11:58:28');

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,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `teacher_id` int(11) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`fee_id`),
  KEY `student_id` (`student_id`),
  KEY `fk_teacher_staff` (`teacher_id`),
  CONSTRAINT `fk_teacher_staff` FOREIGN KEY (`teacher_id`) REFERENCES `staff` (`staff_id`) ON DELETE SET NULL,
  CONSTRAINT `student_fees_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

INSERT INTO `student_fees` (`fee_id`, `student_id`, `month`, `year`, `monthly_fee`, `paid`, `balance`, `notes`, `created_at`, `teacher_id`, `class_id`) VALUES ('1', '1', 'January', '2026', '2000.00', '1800.00', '200.00', 'aksdjfhkjasdhfkf', '2026-02-13 20:59:25', '1', '1');
INSERT INTO `student_fees` (`fee_id`, `student_id`, `month`, `year`, `monthly_fee`, `paid`, `balance`, `notes`, `created_at`, `teacher_id`, `class_id`) VALUES ('2', '1', 'January', '2026', '1000.00', '700.00', '300.00', 'FFHFHFGHGGGHFG', '2026-02-17 14:18:21', '1', '1');
INSERT INTO `student_fees` (`fee_id`, `student_id`, `month`, `year`, `monthly_fee`, `paid`, `balance`, `notes`, `created_at`, `teacher_id`, `class_id`) VALUES ('3', '1', 'January', '2026', '2000.00', '500.00', '1500.00', 'bxghdhg', '2026-03-01 11:56:58', '1', '1');

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=4 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', '', '0', '200.00', '2026-02-13', 'kfjskdfh', '2026-02-13 21:06:17');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('2', '1', '', '0', '100.00', '2026-02-17', 'JHJKHJK', '2026-02-17 14:21:10');
INSERT INTO `student_repayments` (`repayment_id`, `student_id`, `related_fee`, `related_id`, `amount_paid`, `payment_date`, `remarks`, `created_at`) VALUES ('3', '1', '', '0', '500.00', '2026-03-01', 'ghfgh', '2026-03-01 11:59:17');

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,
  `teacher_id` 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=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `students` (`student_id`, `user_id`, `student_name`, `father_name`, `grandfather_name`, `student_class`, `teacher_id`, `student_phone`, `gender`, `date_of_birth`, `current_address`, `permanent_address`, `student_image`, `created_at`) VALUES ('1', '', 'Shahid', 'khanullah', 'Dad Gul', '1', '1', '0789798798', '', '2026-02-13', 'wkhrahf', 'sdkjhskjdfh', 'assets/uploads/students_1770999783_8832.jpg', '2026-02-13 20:53:03');

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=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `subjects` (`subject_id`, `class_id`, `subject_name`) VALUES ('1', '', 'Computer');

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$fY/T2M0mCtK7uFLl04WNru/XaPET2K9tvm.qHGFklvddUEP6N3zIG', 'assets/uploads/user_2_1771322134.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');