-- ============================================
-- SCHOOL MANAGEMENT SYSTEM - COMPLETE DATABASE
-- Version: 1.0.0
-- Compatible: MySQL 8.0+
-- ============================================

START TRANSACTION;

-- ============================================
-- CREATE DATABASE
-- ============================================

CREATE DATABASE IF NOT EXISTS `sms_db` 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE `sms_db`;

-- ============================================
-- 1. USER MANAGEMENT MODULE
-- ============================================

-- Roles Table
CREATE TABLE `roles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL UNIQUE,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 2. ACADEMIC STRUCTURE MODULE
-- ============================================

-- Classes Table
CREATE TABLE `classes` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `section` VARCHAR(10) DEFAULT 'A',
  `capacity` INT DEFAULT 40,
  `teacher_id` INT,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`teacher_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Subjects Table
CREATE TABLE `subjects` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `code` VARCHAR(20) UNIQUE NOT NULL,
  `description` TEXT,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Class Subjects (Mapping)
CREATE TABLE `class_subjects` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `class_id` INT NOT NULL,
  `subject_id` INT NOT NULL,
  `teacher_id` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`class_id`) REFERENCES `classes`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`teacher_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  UNIQUE KEY `unique_class_subject` (`class_id`, `subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Academic Years
CREATE TABLE `academic_years` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `year_name` VARCHAR(20) NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `is_current` TINYINT(1) DEFAULT 0,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Terms
CREATE TABLE `terms` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `academic_year_id` INT NOT NULL,
  `term_name` VARCHAR(50) NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `is_current` TINYINT(1) DEFAULT 0,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 3. STUDENT MANAGEMENT MODULE
-- ============================================

-- Parents/Guardians Table
CREATE TABLE `parents_guardians` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `guardian_type` ENUM('Father', 'Mother', 'Guardian') DEFAULT 'Guardian',
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `id_number` VARCHAR(50) UNIQUE,
  `phone_primary` VARCHAR(20) NOT NULL,
  `phone_secondary` VARCHAR(20),
  `email` VARCHAR(100),
  `occupation` VARCHAR(100),
  `employer` VARCHAR(100),
  `address` TEXT,
  `city` VARCHAR(100),
  `country` VARCHAR(100) DEFAULT 'Kenya',
  `postal_code` VARCHAR(20),
  `emergency_contact_name` VARCHAR(100),
  `emergency_contact_phone` VARCHAR(20),
  `relationship_to_student` VARCHAR(50),
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_phone` (`phone_primary`),
  INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Student-Parent Relationship
CREATE TABLE `student_parents` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `parent_id` INT NOT NULL,
  `is_primary` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`parent_id`) REFERENCES `parents_guardians`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_student_parent` (`student_id`, `parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Students Table
CREATE TABLE `students` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `admission_no` VARCHAR(50) UNIQUE NOT NULL,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) NOT NULL,
  `gender` ENUM('Male', 'Female') NOT NULL,
  `dob` DATE NOT NULL,
  `class_id` INT NOT NULL,
  `status` TINYINT(1) DEFAULT 1,
  `admission_date` DATE,
  `previous_school` VARCHAR(100),
  `profile_image` VARCHAR(255),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`class_id`) REFERENCES `classes`(`id`) ON DELETE RESTRICT,
  INDEX `idx_admission` (`admission_no`),
  INDEX `idx_class` (`class_id`),
  INDEX `idx_name` (`first_name`, `last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Student Attendance
CREATE TABLE `student_attendance` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `status` ENUM('Present', 'Absent', 'Late', 'Excused') DEFAULT 'Present',
  `remarks` TEXT,
  `marked_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`marked_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  UNIQUE KEY `unique_student_date` (`student_id`, `date`),
  INDEX `idx_date` (`date`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 4. EXAM & GRADING MODULE
-- ============================================

-- Exams Table
CREATE TABLE `exams` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `year` VARCHAR(10) NOT NULL,
  `term` VARCHAR(50) NOT NULL,
  `start_date` DATE,
  `end_date` DATE,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_year` (`year`),
  INDEX `idx_term` (`term`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Grade Scales
CREATE TABLE `grade_scales` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `grade_name` VARCHAR(10) NOT NULL,
  `min_marks` INT NOT NULL,
  `max_marks` INT NOT NULL,
  `point` DECIMAL(3,2) NOT NULL,
  `remarks` VARCHAR(100),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Exam Results
CREATE TABLE `exam_results` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `exam_id` INT NOT NULL,
  `student_id` INT NOT NULL,
  `subject_id` INT NOT NULL,
  `marks_obtained` DECIMAL(5,2) NOT NULL,
  `total_marks` DECIMAL(5,2) DEFAULT 100,
  `grade` VARCHAR(5),
  `points` DECIMAL(3,2),
  `remarks` TEXT,
  `entered_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`entered_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  UNIQUE KEY `unique_result` (`exam_id`, `student_id`, `subject_id`),
  INDEX `idx_exam` (`exam_id`),
  INDEX `idx_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 5. FINANCE MODULE
-- ============================================

-- Fee Types
CREATE TABLE `fee_types` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Fee Structures
CREATE TABLE `fee_structures` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `class_id` INT NOT NULL,
  `fee_type_id` INT NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `academic_year` VARCHAR(10) NOT NULL,
  `term` VARCHAR(50),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`class_id`) REFERENCES `classes`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`fee_type_id`) REFERENCES `fee_types`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_fee_structure` (`class_id`, `fee_type_id`, `academic_year`, `term`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Invoices
CREATE TABLE `invoices` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `invoice_no` VARCHAR(50) UNIQUE NOT NULL,
  `student_id` INT NOT NULL,
  `class_id` INT NOT NULL,
  `total_amount` DECIMAL(10,2) NOT NULL,
  `paid_amount` DECIMAL(10,2) DEFAULT 0.00,
  `balance_amount` DECIMAL(10,2) NOT NULL,
  `status` ENUM('Paid', 'Partial', 'Unpaid') DEFAULT 'Unpaid',
  `due_date` DATE,
  `issued_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`class_id`) REFERENCES `classes`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`issued_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_invoice_no` (`invoice_no`),
  INDEX `idx_student` (`student_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Payments
CREATE TABLE `payments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `invoice_id` INT NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `payment_date` DATE NOT NULL,
  `payment_method` ENUM('Cash', 'Bank', 'MPesa', 'Cheque', 'Card') DEFAULT 'Cash',
  `transaction_ref` VARCHAR(50),
  `remarks` TEXT,
  `received_by` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`received_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
  INDEX `idx_invoice` (`invoice_id`),
  INDEX `idx_date` (`payment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 6. TRANSPORT MODULE
-- ============================================

-- Transport Routes
CREATE TABLE `transport_routes` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `route_name` VARCHAR(100) NOT NULL,
  `start_point` VARCHAR(200),
  `end_point` VARCHAR(200),
  `distance_km` DECIMAL(5,2),
  `estimated_time` VARCHAR(20),
  `fare_amount` DECIMAL(10,2) NOT NULL,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Vehicles
CREATE TABLE `vehicles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `vehicle_no` VARCHAR(20) UNIQUE NOT NULL,
  `vehicle_type` ENUM('Bus', 'Van', 'Car', 'Mini Bus') NOT NULL,
  `capacity` INT NOT NULL,
  `driver_name` VARCHAR(100),
  `driver_phone` VARCHAR(20),
  `route_name` VARCHAR(100),
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_vehicle_no` (`vehicle_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Student Transport Assignment
CREATE TABLE `student_transport` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `vehicle_id` INT NOT NULL,
  `route_id` INT NOT NULL,
  `pickup_point` VARCHAR(200),
  `drop_point` VARCHAR(200),
  `monthly_fee` DECIMAL(10,2) NOT NULL,
  `status` TINYINT(1) DEFAULT 1,
  `start_date` DATE NOT NULL,
  `end_date` DATE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`route_id`) REFERENCES `transport_routes`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 7. LIBRARY MODULE
-- ============================================

-- Library Books
CREATE TABLE `library_books` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `book_title` VARCHAR(200) NOT NULL,
  `author` VARCHAR(100) NOT NULL,
  `isbn` VARCHAR(20) UNIQUE,
  `publisher` VARCHAR(100),
  `publish_year` VARCHAR(10),
  `category` VARCHAR(50),
  `total_copies` INT DEFAULT 1,
  `available_copies` INT DEFAULT 1,
  `rack_number` VARCHAR(20),
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_title` (`book_title`),
  INDEX `idx_author` (`author`),
  INDEX `idx_isbn` (`isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Book Issues
CREATE TABLE `book_issues` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `book_id` INT NOT NULL,
  `student_id` INT NOT NULL,
  `issue_date` DATE NOT NULL,
  `due_date` DATE NOT NULL,
  `return_date` DATE NULL,
  `fine_amount` DECIMAL(10,2) DEFAULT 0.00,
  `status` ENUM('Issued', 'Returned', 'Overdue') DEFAULT 'Issued',
  `remarks` TEXT,
  `issued_by` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`book_id`) REFERENCES `library_books`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`issued_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
  INDEX `idx_status` (`status`),
  INDEX `idx_due_date` (`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 8. BOARDING/HOSTEL MODULE
-- ============================================

-- Hostels
CREATE TABLE `hostels` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `type` ENUM('Boys', 'Girls', 'Mixed') NOT NULL,
  `total_rooms` INT NOT NULL,
  `total_capacity` INT NOT NULL,
  `warden_name` VARCHAR(100),
  `warden_phone` VARCHAR(20),
  `address` TEXT,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Rooms
CREATE TABLE `rooms` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `hostel_id` INT NOT NULL,
  `room_number` VARCHAR(20) NOT NULL,
  `floor` VARCHAR(20),
  `capacity` INT NOT NULL DEFAULT 4,
  `current_occupancy` INT DEFAULT 0,
  `room_type` ENUM('Single', 'Double', 'Triple', 'Quad', 'Dormitory') DEFAULT 'Quad',
  `facilities` TEXT,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`hostel_id`) REFERENCES `hostels`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_room` (`hostel_id`, `room_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Boarding Students
CREATE TABLE `boarding_students` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT UNIQUE NOT NULL,
  `room_id` INT NOT NULL,
  `admission_date` DATE NOT NULL,
  `checkout_date` DATE NULL,
  `boarding_type` ENUM('Full Boarding', 'Weekly Boarding', 'Day Scholar') DEFAULT 'Full Boarding',
  `monthly_fee` DECIMAL(10,2) NOT NULL,
  `status` TINYINT(1) DEFAULT 1,
  `remarks` TEXT,
  `created_by` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`room_id`) REFERENCES `rooms`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Room Inspections
CREATE TABLE `room_inspections` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `room_id` INT NOT NULL,
  `inspector_id` INT NOT NULL,
  `inspection_date` DATE NOT NULL,
  `cleanliness` ENUM('Excellent', 'Good', 'Fair', 'Poor') DEFAULT 'Good',
  `maintenance_issues` TEXT,
  `action_taken` TEXT,
  `status` ENUM('Pending', 'Resolved', 'In Progress') DEFAULT 'Pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`room_id`) REFERENCES `rooms`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`inspector_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 9. MEDICAL/INFIRMARY MODULE
-- ============================================

-- Medical Staff
CREATE TABLE `medical_staff` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `qualification` VARCHAR(100),
  `license_number` VARCHAR(50),
  `phone` VARCHAR(20),
  `email` VARCHAR(100),
  `specialization` VARCHAR(100),
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Medical Conditions
CREATE TABLE `medical_conditions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `condition_name` VARCHAR(100) NOT NULL,
  `condition_type` ENUM('Allergy', 'Chronic', 'Disability', 'Other') DEFAULT 'Other',
  `description` TEXT,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Student Medical Records
CREATE TABLE `student_medical_records` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT UNIQUE NOT NULL,
  `blood_group` VARCHAR(5),
  `height_cm` DECIMAL(5,2),
  `weight_kg` DECIMAL(5,2),
  `chronic_conditions` TEXT,
  `allergies` TEXT,
  `medications` TEXT,
  `emergency_contact_name` VARCHAR(100),
  `emergency_contact_phone` VARCHAR(20),
  `emergency_contact_relationship` VARCHAR(50),
  `family_medical_history` TEXT,
  `insurance_provider` VARCHAR(100),
  `insurance_policy_number` VARCHAR(50),
  `last_checkup_date` DATE,
  `next_checkup_date` DATE,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Student Medical Conditions
CREATE TABLE `student_medical_conditions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `condition_id` INT NOT NULL,
  `diagnosed_date` DATE,
  `status` ENUM('Active', 'Managed', 'Resolved') DEFAULT 'Active',
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`condition_id`) REFERENCES `medical_conditions`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_student_condition` (`student_id`, `condition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Medical Visits
CREATE TABLE `medical_visits` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `visit_date` DATE NOT NULL,
  `visit_time` TIME NOT NULL,
  `complaint` TEXT NOT NULL,
  `diagnosis` TEXT,
  `treatment` TEXT,
  `medications_prescribed` TEXT,
  `referred_to` VARCHAR(100),
  `follow_up_date` DATE,
  `status` ENUM('Open', 'In Treatment', 'Referred', 'Closed') DEFAULT 'Open',
  `attended_by` INT,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`attended_by`) REFERENCES `medical_staff`(`id`) ON DELETE SET NULL,
  INDEX `idx_date` (`visit_date`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Medications
CREATE TABLE `medications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `medication_name` VARCHAR(100) NOT NULL,
  `generic_name` VARCHAR(100),
  `category` VARCHAR(50),
  `dosage_form` ENUM('Tablet', 'Capsule', 'Syrup', 'Injection', 'Cream', 'Inhaler', 'Other') DEFAULT 'Tablet',
  `strength` VARCHAR(50),
  `stock_quantity` INT DEFAULT 0,
  `reorder_level` INT DEFAULT 10,
  `unit_price` DECIMAL(10,2),
  `supplier` VARCHAR(100),
  `expiry_date` DATE,
  `batch_number` VARCHAR(50),
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_name` (`medication_name`),
  INDEX `idx_expiry` (`expiry_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Medication Dispensing
CREATE TABLE `medication_dispensing` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `visit_id` INT NOT NULL,
  `medication_id` INT NOT NULL,
  `student_id` INT NOT NULL,
  `quantity` INT NOT NULL,
  `dosage_instructions` TEXT,
  `duration_days` INT,
  `dispensed_by` INT NOT NULL,
  `dispensed_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`visit_id`) REFERENCES `medical_visits`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`medication_id`) REFERENCES `medications`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`dispensed_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Medication Inventory Log
CREATE TABLE `medication_inventory_log` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `medication_id` INT NOT NULL,
  `transaction_type` ENUM('Received', 'Dispensed', 'Expired', 'Adjusted') NOT NULL,
  `quantity` INT NOT NULL,
  `balance_after` INT NOT NULL,
  `reference_number` VARCHAR(50),
  `notes` TEXT,
  `created_by` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`medication_id`) REFERENCES `medications`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Immunization Records
CREATE TABLE `immunization_records` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `vaccine_name` VARCHAR(100) NOT NULL,
  `dose_number` VARCHAR(20),
  `administration_date` DATE NOT NULL,
  `next_due_date` DATE,
  `administered_by` VARCHAR(100),
  `location` VARCHAR(100),
  `batch_number` VARCHAR(50),
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  INDEX `idx_due_date` (`next_due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Medical Incidents
CREATE TABLE `medical_incidents` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `incident_date` DATE NOT NULL,
  `incident_time` TIME NOT NULL,
  `incident_type` ENUM('Injury', 'Illness', 'Accident', 'Emergency', 'Other') NOT NULL,
  `description` TEXT NOT NULL,
  `location` VARCHAR(100),
  `witnesses` TEXT,
  `action_taken` TEXT,
  `reported_by` INT NOT NULL,
  `parent_notified` TINYINT(1) DEFAULT 0,
  `parent_notification_date` DATE,
  `hospital_referral` TINYINT(1) DEFAULT 0,
  `hospital_name` VARCHAR(100),
  `status` ENUM('Open', 'Under Investigation', 'Closed') DEFAULT 'Open',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`reported_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 10. COMMUNICATION MODULE
-- ============================================

-- Messages
CREATE TABLE `messages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `sender_id` INT NOT NULL,
  `receiver_id` INT NOT NULL,
  `subject` VARCHAR(200) NOT NULL,
  `message` TEXT NOT NULL,
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`sender_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`receiver_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_receiver` (`receiver_id`),
  INDEX `idx_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Notifications
CREATE TABLE `notifications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `message` TEXT NOT NULL,
  `type` ENUM('info', 'warning', 'success', 'danger') DEFAULT 'info',
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- INSERT DEMO DATA
-- ============================================

-- 1. ROLES
INSERT INTO `roles` (`name`, `description`) VALUES
('Super Admin', 'Full system access'),
('Admin', 'Administrative access'),
('Teacher', 'Teaching staff'),
('Student', 'Student access'),
('Parent', 'Parent portal access'),
('Accountant', 'Finance management'),
('Medical Staff', 'Infirmary access');

-- 3. CLASSES
INSERT INTO `classes` (`name`, `section`, `capacity`, `status`) VALUES
('Grade 1', 'A', 40, 1),
('Grade 1', 'B', 40, 1),
('Grade 2', 'A', 40, 1),
('Grade 3', 'A', 40, 1),
('Grade 4', 'A', 40, 1),
('Grade 5', 'A', 40, 1),
('Grade 6', 'A', 40, 1),
('Grade 7', 'A', 40, 1),
('Grade 8', 'A', 40, 1);

-- 4. SUBJECTS
INSERT INTO `subjects` (`name`, `code`, `description`, `status`) VALUES
('Mathematics', 'MATH', 'Mathematics and calculations', 1),
('English', 'ENG', 'English Language', 1),
('Kiswahili', 'KISW', 'Kiswahili Language', 1),
('Science', 'SCI', 'General Science', 1),
('Social Studies', 'SST', 'Social Studies', 1),
('Computer Studies', 'COMP', 'Computer Studies', 1),
('Agriculture', 'AGR', 'Agriculture', 1),
('Business Studies', 'BUS', 'Business Studies', 1),
('Physical Education', 'PE', 'Physical Education', 1),
('Religious Education', 'RE', 'Religious Education', 1);

-- 5. CLASS SUBJECTS
INSERT INTO `class_subjects` (`class_id`, `subject_id`, `teacher_id`) VALUES
(1, 1, 3), (1, 2, 3), (1, 3, 4), (1, 4, 3), (1, 5, 4),
(2, 1, 4), (2, 2, 3), (2, 3, 4), (2, 4, 3), (2, 5, 4),
(3, 1, 3), (3, 2, 4), (3, 3, 3), (3, 4, 4), (3, 5, 3);

-- 6. ACADEMIC YEARS
INSERT INTO `academic_years` (`year_name`, `start_date`, `end_date`, `is_current`) VALUES
('2023-2024', '2023-09-01', '2024-08-31', 0),
('2024-2025', '2024-09-01', '2025-08-31', 1);

-- 7. TERMS
INSERT INTO `terms` (`academic_year_id`, `term_name`, `start_date`, `end_date`, `is_current`) VALUES
(2, 'Term 1', '2024-09-01', '2024-12-15', 0),
(2, 'Term 2', '2025-01-05', '2025-04-15', 1),
(2, 'Term 3', '2025-05-05', '2025-08-31', 0);

-- 8. PARENTS
INSERT INTO `parents_guardians` (`guardian_type`, `first_name`, `last_name`, `id_number`, `phone_primary`, `phone_secondary`, `email`, `occupation`, `employer`, `city`, `country`) VALUES
('Father', 'James', 'Mwangi', 'ID12345678', '0711111111', '0722222222', 'james.mwangi@email.com', 'Business', 'Self Employed', 'Nairobi', 'Kenya'),
('Mother', 'Sarah', 'Mwangi', 'ID87654321', '0733333333', '', 'sarah.mwangi@email.com', 'Teacher', 'Ministry of Education', 'Nairobi', 'Kenya'),
('Father', 'John', 'Kamau', 'ID11223344', '0744444444', '', 'john.kamau@email.com', 'Engineer', 'Kenya Power', 'Mombasa', 'Kenya'),
('Mother', 'Mary', 'Kamau', 'ID44332211', '0755555555', '0766666666', 'mary.kamau@email.com', 'Nurse', 'KNH', 'Mombasa', 'Kenya'),
('Father', 'Peter', 'Omondi', 'ID55667788', '0777777777', '', 'peter.omondi@email.com', 'Accountant', 'KRA', 'Kisumu', 'Kenya'),
('Guardian', 'Grace', 'Wanjiru', 'ID99887766', '0788888888', '', 'grace.wanjiru@email.com', 'Business', 'Self Employed', 'Nakuru', 'Kenya');

-- 9. STUDENTS
INSERT INTO `students` (`admission_no`, `first_name`, `last_name`, `gender`, `dob`, `class_id`, `status`, `admission_date`, `previous_school`) VALUES
('ADM-2024-0001', 'Brian', 'Mwangi', 'Male', '2015-03-15', 1, 1, '2024-01-05', 'Sunshine Academy'),
('ADM-2024-0002', 'Faith', 'Mwangi', 'Female', '2015-06-20', 1, 1, '2024-01-05', 'Sunshine Academy'),
('ADM-2024-0003', 'Kevin', 'Kamau', 'Male', '2015-09-10', 1, 1, '2024-01-05', 'Little Stars'),
('ADM-2024-0004', 'Mercy', 'Kamau', 'Female', '2014-12-05', 2, 1, '2023-01-05', 'Happy Kids'),
('ADM-2024-0005', 'John', 'Omondi', 'Male', '2014-04-18', 2, 1, '2023-01-05', 'Bright Future'),
('ADM-2024-0006', 'Alice', 'Wanjiru', 'Female', '2013-08-22', 3, 1, '2022-01-05', 'Excellence School'),
('ADM-2024-0007', 'David', 'Mutua', 'Male', '2013-02-14', 3, 1, '2022-01-05', 'Victory Academy'),
('ADM-2024-0008', 'Jane', 'Kimani', 'Female', '2012-11-30', 4, 1, '2021-01-05', 'Hope School'),
('ADM-2024-0009', 'Michael', 'Njoroge', 'Male', '2012-07-08', 4, 1, '2021-01-05', 'Faith Academy'),
('ADM-2024-0010', 'Lucy', 'Wambui', 'Female', '2011-05-25', 5, 1, '2020-01-05', 'Star School');

-- 10. STUDENT-PARENT ASSIGNMENTS
INSERT INTO `student_parents` (`student_id`, `parent_id`, `is_primary`) VALUES
(1, 1, 1), (1, 2, 0),
(2, 1, 1), (2, 2, 0),
(3, 3, 1), (3, 4, 0),
(4, 3, 1), (4, 4, 0),
(5, 5, 1),
(6, 6, 1);

-- 11. EXAMS
INSERT INTO `exams` (`name`, `year`, `term`, `start_date`, `end_date`, `status`) VALUES
('End of Year Exams', '2024', 'Term 3', '2024-11-01', '2024-11-30', 1),
('Mid Term Exams', '2025', 'Term 2', '2025-03-01', '2025-03-15', 1);

-- 12. GRADE SCALES
INSERT INTO `grade_scales` (`grade_name`, `min_marks`, `max_marks`, `point`, `remarks`) VALUES
('A', 80, 100, 4.00, 'Excellent'),
('A-', 75, 79, 3.75, 'Very Good'),
('B+', 70, 74, 3.50, 'Good'),
('B', 65, 69, 3.00, 'Above Average'),
('B-', 60, 64, 2.75, 'Average'),
('C+', 55, 59, 2.50, 'Below Average'),
('C', 50, 54, 2.00, 'Fair'),
('D', 40, 49, 1.00, 'Pass'),
('E', 0, 39, 0.00, 'Fail');

-- 13. EXAM RESULTS (Sample)
INSERT INTO `exam_results` (`exam_id`, `student_id`, `subject_id`, `marks_obtained`, `total_marks`, `grade`, `points`, `entered_by`) VALUES
(1, 1, 1, 85, 100, 'A', 4.00, 3),
(1, 1, 2, 78, 100, 'A-', 3.75, 3),
(1, 1, 3, 72, 100, 'B+', 3.50, 4),
(1, 1, 4, 88, 100, 'A', 4.00, 3),
(1, 2, 1, 90, 100, 'A', 4.00, 3),
(1, 2, 2, 82, 100, 'A', 4.00, 3),
(1, 2, 3, 75, 100, 'A-', 3.75, 4),
(1, 3, 1, 68, 100, 'B', 3.00, 3),
(1, 3, 2, 71, 100, 'B+', 3.50, 3),
(1, 3, 4, 65, 100, 'B', 3.00, 3);

-- 14. FEE TYPES
INSERT INTO `fee_types` (`name`, `description`, `status`) VALUES
('Tuition Fee', 'Academic term tuition fees', 1),
('Transport Fee', 'School transport fees', 1),
('Boarding Fee', 'Hostel/boarding fees', 1),
('Library Fee', 'Library usage fees', 1),
('Activity Fee', 'Extracurricular activities', 1),
('Medical Fee', 'Infirmary/medical fees', 1);

-- 15. FEE STRUCTURES
INSERT INTO `fee_structures` (`class_id`, `fee_type_id`, `amount`, `academic_year`, `term`) VALUES
(1, 1, 15000.00, '2024-2025', 'Term 2'),
(1, 2, 5000.00, '2024-2025', 'Term 2'),
(1, 5, 2000.00, '2024-2025', 'Term 2'),
(2, 1, 15000.00, '2024-2025', 'Term 2'),
(3, 1, 16000.00, '2024-2025', 'Term 2'),
(4, 1, 17000.00, '2024-2025', 'Term 2');

-- 16. INVOICES
INSERT INTO `invoices` (`invoice_no`, `student_id`, `class_id`, `total_amount`, `paid_amount`, `balance_amount`, `status`, `due_date`, `issued_by`) VALUES
('INV-2025-0001', 1, 1, 22000.00, 22000.00, 0.00, 'Paid', '2025-02-28', 5),
('INV-2025-0002', 2, 1, 22000.00, 15000.00, 7000.00, 'Partial', '2025-02-28', 5),
('INV-2025-0003', 3, 1, 22000.00, 0.00, 22000.00, 'Unpaid', '2025-02-28', 5),
('INV-2025-0004', 4, 2, 22000.00, 22000.00, 0.00, 'Paid', '2025-02-28', 5),
('INV-2025-0005', 5, 2, 22000.00, 10000.00, 12000.00, 'Partial', '2025-02-28', 5);

-- 17. PAYMENTS
INSERT INTO `payments` (`invoice_id`, `amount`, `payment_date`, `payment_method`, `transaction_ref`, `remarks`, `received_by`) VALUES
(1, 22000.00, '2025-01-10', 'Bank', 'TXN001', 'Full payment', 5),
(2, 15000.00, '2025-01-15', 'MPesa', 'MPESA001', 'First installment', 5),
(4, 22000.00, '2025-01-12', 'Cash', 'CASH001', 'Full payment', 5),
(5, 10000.00, '2025-01-20', 'Bank', 'TXN002', 'First installment', 5);

-- 18. TRANSPORT ROUTES
INSERT INTO `transport_routes` (`route_name`, `start_point`, `end_point`, `distance_km`, `estimated_time`, `fare_amount`, `status`) VALUES
('Route A - Nairobi North', 'Thika Road', 'School Main Gate', 15.5, '45 mins', 5000.00, 1),
('Route B - Mombasa Road', 'Syokimau', 'School Main Gate', 22.0, '60 mins', 6000.00, 1),
('Route C - Waiyaki Way', 'Westlands', 'School Main Gate', 18.0, '50 mins', 5500.00, 1);

-- 19. VEHICLES
INSERT INTO `vehicles` (`vehicle_no`, `vehicle_type`, `capacity`, `driver_name`, `driver_phone`, `route_name`, `status`) VALUES
('KCA 123A', 'Bus', 50, 'John Kamau', '0712345678', 'Route A - Nairobi North', 1),
('KCA 456B', 'Bus', 45, 'Peter Omondi', '0723456789', 'Route B - Mombasa Road', 1),
('KCA 789C', 'Van', 25, 'Mary Wanjiku', '0734567890', 'Route C - Waiyaki Way', 1);

-- 20. LIBRARY BOOKS
INSERT INTO `library_books` (`book_title`, `author`, `isbn`, `publisher`, `publish_year`, `category`, `total_copies`, `available_copies`, `rack_number`) VALUES
('Mathematics Form 1', 'Dr. James Smith', '978-0-123456-78-9', 'Educational Press', '2020', 'Mathematics', 5, 5, 'A1'),
('English Grammar', 'Prof. Sarah Johnson', '978-0-234567-89-0', 'Language Books Ltd', '2019', 'English', 3, 3, 'A2'),
('Science Basics', 'Dr. Michael Brown', '978-0-345678-90-1', 'Science Publishers', '2021', 'Science', 4, 4, 'B1'),
('History of Kenya', 'Prof. David Kimani', '978-0-456789-01-2', 'History Press', '2018', 'History', 2, 2, 'C1'),
('Computer Studies', 'Eng. Grace Mwangi', '978-0-567890-12-3', 'Tech Books', '2022', 'Technology', 3, 3, 'D1'),
('Kiswahili Fasili', 'Prof. John Mutiso', '978-0-678901-23-4', 'EA Publishers', '2020', 'Kiswahili', 4, 4, 'A3'),
('Biology Today', 'Dr. Jane Wanjiku', '978-0-789012-34-5', 'Science Press', '2021', 'Science', 3, 3, 'B2'),
('Chemistry Essentials', 'Prof. Peter Kamau', '978-0-890123-45-6', 'Chemical Pub', '2019', 'Science', 2, 2, 'B3');

-- 21. HOSTELS
INSERT INTO `hostels` (`name`, `type`, `total_rooms`, `total_capacity`, `warden_name`, `warden_phone`, `status`) VALUES
('Sunrise Hostel', 'Boys', 20, 80, 'Mr. John Kamau', '0712345678', 1),
('Sunset Hostel', 'Girls', 20, 80, 'Mrs. Mary Wanjiku', '0723456789', 1),
('East Wing', 'Mixed', 15, 60, 'Mr. Peter Omondi', '0734567890', 1);

-- 22. ROOMS
INSERT INTO `rooms` (`hostel_id`, `room_number`, `floor`, `capacity`, `room_type`, `status`) VALUES
(1, 'R101', 'Ground', 4, 'Quad', 1),
(1, 'R102', 'Ground', 4, 'Quad', 1),
(1, 'R103', 'Ground', 2, 'Double', 1),
(2, 'R201', 'First', 4, 'Quad', 1),
(2, 'R202', 'First', 4, 'Quad', 1),
(3, 'R301', 'Ground', 6, 'Dormitory', 1);

-- 23. MEDICAL STAFF
INSERT INTO `medical_staff` (`name`, `qualification`, `license_number`, `phone`, `email`, `specialization`, `status`) VALUES
('Dr. Jane Wanjiku', 'MBChB', 'MED-12345', '0711111111', 'dr.jane@school.com', 'General Practice', 1),
('Nurse Mary Kamau', 'RN', 'NUR-67890', '0722222222', 'nurse.mary@school.com', 'Pediatric Nursing', 1);

-- 24. MEDICAL CONDITIONS
INSERT INTO `medical_conditions` (`condition_name`, `condition_type`, `description`, `status`) VALUES
('Asthma', 'Chronic', 'Respiratory condition requiring inhaler', 1),
('Diabetes Type 1', 'Chronic', 'Requires insulin monitoring', 1),
('Peanut Allergy', 'Allergy', 'Severe allergic reaction to peanuts', 1),
('Epilepsy', 'Chronic', 'Seizure disorder', 1),
('Visual Impairment', 'Disability', 'Requires corrective lenses', 1),
('Hearing Impairment', 'Disability', 'Requires hearing aid', 1),
('Milk Allergy', 'Allergy', 'Allergic to dairy products', 1);

-- 25. MEDICATIONS
INSERT INTO `medications` (`medication_name`, `generic_name`, `category`, `dosage_form`, `strength`, `stock_quantity`, `reorder_level`, `unit_price`, `expiry_date`, `batch_number`, `status`) VALUES
('Paracetamol 500mg', 'Paracetamol', 'Pain Relief', 'Tablet', '500mg', 100, 20, 5.00, '2026-12-31', 'BAT001', 1),
('Amoxicillin 250mg', 'Amoxicillin', 'Antibiotic', 'Capsule', '250mg', 50, 15, 15.00, '2026-06-30', 'BAT002', 1),
('Salbutamol Inhaler', 'Salbutamol', 'Respiratory', 'Inhaler', '100mcg', 20, 5, 450.00, '2026-09-30', 'BAT003', 1),
('Antihistamine', 'Cetirizine', 'Allergy', 'Tablet', '10mg', 75, 20, 8.00, '2026-11-30', 'BAT004', 1),
('Insulin Injection', 'Insulin', 'Diabetes', 'Injection', '100IU/ml', 15, 5, 850.00, '2026-08-31', 'BAT005', 1),
('Ibuprofen 400mg', 'Ibuprofen', 'Pain Relief', 'Tablet', '400mg', 80, 20, 10.00, '2026-10-31', 'BAT006', 1),
('Cough Syrup', 'Dextromethorphan', 'Respiratory', 'Syrup', '100ml', 30, 10, 120.00, '2026-07-31', 'BAT007', 1);

-- 26. NOTIFICATIONS (Sample)
INSERT INTO `notifications` (`user_id`, `title`, `message`, `type`, `is_read`) VALUES
(1, 'Welcome', 'Welcome to School Management System!', 'success', 0),
(1, 'System Update', 'New features have been added to the system', 'info', 0),
(2, 'Fee Collection', 'Term 2 fee collection is now open', 'warning', 0),
(3, 'Staff Meeting', 'Staff meeting scheduled for Monday 8AM', 'info', 1),
(5, 'Payment Received', 'New payment received for Invoice INV-2025-0001', 'success', 1);

-- 27. ATTENDANCE (Sample - Last 5 Days)
INSERT INTO `student_attendance` (`student_id`, `date`, `status`, `remarks`, `marked_by`) VALUES
(1, CURDATE(), 'Present', '', 3),
(1, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'Present', '', 3),
(1, DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'Late', 'Traffic delay', 3),
(1, DATE_SUB(CURDATE(), INTERVAL 3 DAY), 'Present', '', 3),
(1, DATE_SUB(CURDATE(), INTERVAL 4 DAY), 'Present', '', 3),
(2, CURDATE(), 'Present', '', 3),
(2, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'Absent', 'Sick', 3),
(2, DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'Present', '', 3),
(2, DATE_SUB(CURDATE(), INTERVAL 3 DAY), 'Present', '', 3),
(2, DATE_SUB(CURDATE(), INTERVAL 4 DAY), 'Present', '', 3),
(3, CURDATE(), 'Present', '', 4),
(3, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'Present', '', 4),
(3, DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'Present', '', 4),
(3, DATE_SUB(CURDATE(), INTERVAL 3 DAY), 'Excused', 'Medical appointment', 4),
(3, DATE_SUB(CURDATE(), INTERVAL 4 DAY), 'Present', '', 4);

-- ============================================
-- CREATE VIEWS FOR REPORTING
-- ============================================

-- Student Summary View
CREATE OR REPLACE VIEW `v_student_summary` AS
SELECT 
    s.id,
    s.admission_no,
    CONCAT(s.first_name, ' ', s.last_name) AS full_name,
    s.gender,
    s.dob,
    c.name AS class_name,
    c.section AS class_section,
    s.status,
    COUNT(sa.id) AS total_days,
    SUM(CASE WHEN sa.status = 'Present' THEN 1 ELSE 0 END) AS present_days
FROM students s
LEFT JOIN classes c ON s.class_id = c.id
LEFT JOIN student_attendance sa ON s.id = sa.student_id
GROUP BY s.id;

-- Invoice Summary View
CREATE OR REPLACE VIEW `v_invoice_summary` AS
SELECT 
    i.id,
    i.invoice_no,
    CONCAT(s.first_name, ' ', s.last_name) AS student_name,
    s.admission_no,
    i.total_amount,
    i.paid_amount,
    i.balance_amount,
    i.status,
    i.due_date
FROM invoices i
JOIN students s ON i.student_id = s.id
ORDER BY i.created_at DESC;

-- Library Books Available View
CREATE OR REPLACE VIEW `v_books_available` AS
SELECT 
    id,
    book_title,
    author,
    category,
    total_copies,
    available_copies,
    rack_number,
    CASE 
        WHEN available_copies = 0 THEN 'Not Available'
        WHEN available_copies <= 2 THEN 'Low Stock'
        ELSE 'Available'
    END AS availability_status
FROM library_books
WHERE status = 1;

-- Medical Low Stock View
CREATE OR REPLACE VIEW `v_medical_low_stock` AS
SELECT 
    id,
    medication_name,
    generic_name,
    stock_quantity,
    reorder_level,
    expiry_date,
    CASE 
        WHEN expiry_date < CURDATE() THEN 'Expired'
        WHEN stock_quantity <= reorder_level THEN 'Low Stock'
        ELSE 'In Stock'
    END AS stock_status
FROM medications
WHERE status = 1 AND (stock_quantity <= reorder_level OR expiry_date < CURDATE());

-- ============================================
-- STORED PROCEDURES (Optional)
-- ============================================

DELIMITER //

-- Procedure to calculate student attendance rate
CREATE PROCEDURE `get_student_attendance_rate`(
    IN student_id INT,
    OUT attendance_rate DECIMAL(5,2)
)
BEGIN
    SELECT 
        (SUM(CASE WHEN status = 'Present' THEN 1 ELSE 0 END) / COUNT(*)) * 100
    INTO attendance_rate
    FROM student_attendance
    WHERE student_id = student_id;
END //

-- Procedure to update invoice balance after payment
CREATE PROCEDURE `update_invoice_balance`(
    IN invoice_id INT,
    IN payment_amount DECIMAL(10,2)
)
BEGIN
    UPDATE invoices 
    SET 
        paid_amount = paid_amount + payment_amount,
        balance_amount = balance_amount - payment_amount,
        status = CASE 
            WHEN (balance_amount - payment_amount) = 0 THEN 'Paid'
            WHEN (balance_amount - payment_amount) < 0 THEN 'Paid'
            ELSE 'Partial'
        END
    WHERE id = invoice_id;
END //

DELIMITER ;

-- ============================================
-- TRIGGERS (Optional)
-- ============================================

DELIMITER //

-- Trigger to update room occupancy when boarding student is added
CREATE TRIGGER `after_boarding_insert`
AFTER INSERT ON `boarding_students`
FOR EACH ROW
BEGIN
    UPDATE rooms 
    SET current_occupancy = current_occupancy + 1 
    WHERE id = NEW.room_id;
END //

-- Trigger to update room occupancy when boarding student is removed
CREATE TRIGGER `after_boarding_update`
AFTER UPDATE ON `boarding_students`
FOR EACH ROW
BEGIN
    IF OLD.status = 1 AND NEW.status = 0 THEN
        UPDATE rooms 
        SET current_occupancy = current_occupancy - 1 
        WHERE id = NEW.room_id;
    END IF;
END //

DELIMITER ;

-- ============================================
-- FINALIZE
-- ============================================

COMMIT;

-- Display summary
SELECT 'Database created successfully!' AS status;
SELECT COUNT(*) AS total_students FROM students;
