-- Madrasa Management System Database Schema
-- Created: 2025-11-10

CREATE DATABASE IF NOT EXISTS madrasa_management;
USE madrasa_management;

-- Madrasas table (multi-tenancy)
CREATE TABLE madrasas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    email VARCHAR(100),
    principal_name VARCHAR(100),
    logo_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Students table
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    madrasa_id INT NOT NULL,
    student_id VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    father_name VARCHAR(100) NOT NULL,
    mother_name VARCHAR(100),
    date_of_birth DATE,
    gender ENUM('male', 'female') NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    guardian_phone VARCHAR(20),
    admission_date DATE NOT NULL,
    current_class_id INT,
    photo_url VARCHAR(255),
    status ENUM('active', 'graduated', 'transferred', 'dropped') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (madrasa_id) REFERENCES madrasas(id) ON DELETE CASCADE,
    INDEX idx_student_id (student_id),
    INDEX idx_madrasa_id (madrasa_id),
    INDEX idx_status (status)
);

-- Teachers table
CREATE TABLE teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    madrasa_id INT NOT NULL,
    teacher_id VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    qualification VARCHAR(100),
    experience_years INT,
    subject_specialization VARCHAR(100),
    salary DECIMAL(10,2),
    hire_date DATE NOT NULL,
    photo_url VARCHAR(255),
    status ENUM('active', 'inactive', 'retired') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (madrasa_id) REFERENCES madrasas(id) ON DELETE CASCADE,
    INDEX idx_teacher_id (teacher_id),
    INDEX idx_madrasa_id (madrasa_id)
);

-- Classes table
CREATE TABLE classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    madrasa_id INT NOT NULL,
    class_name VARCHAR(50) NOT NULL,
    grade_level VARCHAR(20),
    section VARCHAR(10),
    teacher_id INT,
    class_time TIME,
    room_number VARCHAR(20),
    max_students INT DEFAULT 30,
    current_students INT DEFAULT 0,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (madrasa_id) REFERENCES madrasas(id) ON DELETE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL,
    INDEX idx_madrasa_id (madrasa_id),
    UNIQUE KEY unique_class (madrasa_id, class_name, section)
);

-- Subjects/Courses table
CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    madrasa_id INT NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    course_code VARCHAR(20) UNIQUE NOT NULL,
    description TEXT,
    grade_level VARCHAR(20),
    teacher_id INT,
    credits DECIMAL(3,1) DEFAULT 1.0,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (madrasa_id) REFERENCES madrasas(id) ON DELETE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL,
    INDEX idx_madrasa_id (madrasa_id),
    INDEX idx_teacher_id (teacher_id)
);

-- Student-Class enrollment
CREATE TABLE student_classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    class_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    status ENUM('enrolled', 'withdrawn', 'completed') DEFAULT 'enrolled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    UNIQUE KEY unique_enrollment (student_id, class_id),
    INDEX idx_student_id (student_id),
    INDEX idx_class_id (class_id)
);

-- Attendance table
CREATE TABLE attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    class_id INT,
    teacher_id INT NOT NULL,
    madrasa_id INT NOT NULL,
    attendance_date DATE NOT NULL,
    status ENUM('present', 'absent', 'late', 'excused') NOT NULL,
    remarks TEXT,
    marked_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
    FOREIGN KEY (madrasa_id) REFERENCES madrasas(id) ON DELETE CASCADE,
    FOREIGN KEY (marked_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_student_date (student_id, attendance_date),
    INDEX idx_class_date (class_id, attendance_date),
    INDEX idx_teacher_date (teacher_id, attendance_date),
    INDEX idx_madrasa_date (madrasa_id, attendance_date)
);

-- Fees table
CREATE TABLE fees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    madrasa_id INT NOT NULL,
    fee_type ENUM('admission', 'tuition', 'exam', 'library', 'transport', 'uniform', 'books', 'other') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    due_date DATE NOT NULL,
    paid_amount DECIMAL(10,2) DEFAULT 0.00,
    payment_date DATE,
    payment_method ENUM('cash', 'bank_transfer', 'check', 'online', 'other'),
    receipt_number VARCHAR(50),
    status ENUM('pending', 'partial', 'paid', 'overdue') DEFAULT 'pending',
    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 (madrasa_id) REFERENCES madrasas(id) ON DELETE CASCADE,
    INDEX idx_student_id (student_id),
    INDEX idx_madrasa_id (madrasa_id),
    INDEX idx_due_date (due_date),
    INDEX idx_status (status)
);

-- Income table
CREATE TABLE income (
    id INT AUTO_INCREMENT PRIMARY KEY,
    madrasa_id INT NOT NULL,
    source ENUM('fees', 'donations', 'grants', 'events', 'other') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    received_date DATE NOT NULL,
    payment_method ENUM('cash', 'bank_transfer', 'check', 'online', 'other'),
    reference_number VARCHAR(50),
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (madrasa_id) REFERENCES madrasas(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_madrasa_id (madrasa_id),
    INDEX idx_received_date (received_date),
    INDEX idx_source (source)
);

-- Expenses table
CREATE TABLE expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    madrasa_id INT NOT NULL,
    category ENUM('salaries', 'utilities', 'maintenance', 'supplies', 'food', 'transport', 'events', 'other') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT NOT NULL,
    expense_date DATE NOT NULL,
    payment_method ENUM('cash', 'bank_transfer', 'check', 'online', 'other'),
    receipt_number VARCHAR(50),
    vendor_name VARCHAR(100),
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (madrasa_id) REFERENCES madrasas(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_madrasa_id (madrasa_id),
    INDEX idx_expense_date (expense_date),
    INDEX idx_category (category)
);

-- Islamic Education Records
CREATE TABLE education_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    teacher_id INT NOT NULL,
    madrasa_id INT NOT NULL,
    category ENUM('sabaq', 'sabqi', 'manzil') NOT NULL,
    book_name VARCHAR(100) NOT NULL,
    lesson_name VARCHAR(100),
    page_start INT,
    page_end INT,
    grade ENUM('A+', 'A', 'B+', 'B', 'C+', 'C', 'D', 'F') NOT NULL,
    teacher_remarks TEXT,
    recorded_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
    FOREIGN KEY (madrasa_id) REFERENCES madrasas(id) ON DELETE CASCADE,
    INDEX idx_student_id (student_id),
    INDEX idx_teacher_id (teacher_id),
    INDEX idx_category (category),
    INDEX idx_recorded_date (recorded_date)
);

-- Files/Media table
CREATE TABLE files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    madrasa_id INT NOT NULL,
    table_name VARCHAR(50) NOT NULL, -- 'students', 'fees', etc.
    record_id INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_type VARCHAR(50),
    file_size INT,
    file_path VARCHAR(255) NOT NULL,
    uploaded_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (madrasa_id) REFERENCES madrasas(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_table_record (table_name, record_id),
    INDEX idx_madrasa_id (madrasa_id)
);

-- System settings
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    madrasa_id INT NOT NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT,
    description VARCHAR(255),
    updated_by INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (madrasa_id) REFERENCES madrasas(id) ON DELETE CASCADE,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY unique_setting (madrasa_id, setting_key),
    INDEX idx_madrasa_id (madrasa_id)
);

-- Activity logs
CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    madrasa_id INT NOT NULL,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(50),
    record_id INT,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (madrasa_id) REFERENCES madrasas(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_madrasa_id (madrasa_id),
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
);

-- Insert default data
INSERT INTO madrasas (name, address, phone, email, principal_name) VALUES 
('Sample Madrasa', '123 Education Street, City', '+1234567890', 'info@samplemadrasa.com', 'Principal Name');

-- Create indexes for better performance
CREATE INDEX idx_attendance_date_status ON attendance(attendance_date, status);
CREATE INDEX idx_fees_status_due ON fees(status, due_date);
CREATE INDEX idx_students_name ON students(first_name, last_name);
CREATE INDEX idx_teachers_name ON teachers(first_name, last_name);
CREATE INDEX idx_classes_grade ON classes(grade_level, section);
CREATE INDEX idx_education_category_date ON education_records(category, recorded_date);
CREATE INDEX idx_income_expense_date ON income(received_date), expenses(expense_date);

-- Create triggers for automatic updates
DELIMITER //
CREATE TRIGGER update_student_count
AFTER INSERT ON student_classes
FOR EACH ROW
BEGIN
    UPDATE classes SET current_students = current_students + 1 WHERE id = NEW.class_id;
END;
END;

-- Views for common queries
CREATE VIEW v_student_summary AS
SELECT 
    s.id,
    s.student_id,
    s.first_name,
    s.last_name,
    s.father_name,
    s.current_class_id,
    c.class_name,
    c.grade_level,
    s.status,
    COUNT(DISTINCT a.id) as total_attendance,
    COUNT(DISTINCT f.id) as pending_fees,
    COALESCE(SUM(f.amount - f.paid_amount), 0) as total_pending_fees
FROM students s
LEFT JOIN classes c ON s.current_class_id = c.id
LEFT JOIN attendance a ON s.id = a.student_id
LEFT JOIN fees f ON s.id = f.student_id AND f.status != 'paid'
GROUP BY s.id;

CREATE VIEW v_monthly_financial_summary AS
SELECT 
    madrasa_id,
    DATE_FORMAT(expense_date, '%Y-%m') as month,
    category,
    SUM(amount) as total_expenses
FROM expenses 
WHERE expense_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY madrasa_id, DATE_FORMAT(expense_date, '%Y-%m'), category
UNION ALL
SELECT 
    madrasa_id,
    DATE_FORMAT(received_date, '%Y-%m') as month,
    source as category,
    SUM(amount) * -1 as total_expenses -- Negative to distinguish income
FROM income 
WHERE received_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY madrasa_id, DATE_FORMAT(received_date, '%Y-%m'), source
ORDER BY madrasa_id, month, category;