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


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


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=2 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', '1000.00', '1000.00', '0.00', '2026-01-24', 'sfjskjfsdhf', '2026-01-24 22:52:43');

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=5 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', 'True/False', 'Computer is an Electronic Machine', '', '', '', '', 'True', '2026-01-24 22:57:51');
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', 'What is Comouter', '', '', '', '', '', '2026-01-24 22:58:09');
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', 'What is ROM and Ram', '', '', '', '', '', '2026-01-24 22:58: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 ('4', '1', 'Multiple Choice', 'What is Power Supply', 'GGGH', 'HJ', 'JH', 'JH', '', '2026-01-24 22:58:57');

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', '', 'Samiullah', 'Teacher', 'Bachelor', '0783332428', '', 'ertret', 'assets/uploads/staff_1769278645_8189.JPG', '2026-01-24 22:47:25');

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 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=2 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=2 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', '5000.00', '4000.00', '850.00', '2026-01-24', '100.00', '250.00', 'sjfskldjfdsf', '2026-01-24 22:54:13');

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=2 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', '3000.00', '2000.00', '1000.00', 'kjsdfjkds sdfjsdjfhjdsfhdsfkjdasf ', '2026-01-24 22:50:24', '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 DEFAULT CHARSET=utf8mb4;


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', 'Wali', '1', '1', '0789798798', '', '2026-01-24', 'skjfhsjkdhfskjdhfsjkhf', 'skjfhsdjhf', 'assets/uploads/students_1769278710_4089.jpg', '2026-01-24 22:48:30');

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', '', 'English');

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');