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

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=5 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', '100.00', '3900.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', '2000.00', '2000.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', '100.00', '100.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', '700.00', '200.00', '2025-10-02', 'm,n,mnn,mn', '2025-10-02 11:11:54');

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_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', '4343.00', '343.00', '4000.00', '0000-00-00', '0.00', '0.00', 'aerw', '2025-09-29 11:37:01');

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=6 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', '1100.00', '900.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', '100.00', '200.00', '2025-09-29 10:39:19');

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

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