-- Database Migration for Auto-Assignment System
-- TutorKita System

-- ============================================
-- 1. Create teacher_subjects table
-- ============================================
CREATE TABLE IF NOT EXISTS teacher_subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    subject_name VARCHAR(100) NOT NULL,
    is_custom TINYINT DEFAULT 0,
    custom_subject VARCHAR(200),
    max_students INT DEFAULT 20,
    current_students INT DEFAULT 0,
    status ENUM('Active', 'Inactive', 'OnLeave') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 2. Create student_enrollments table
-- ============================================
CREATE TABLE IF NOT EXISTS student_enrollments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    teacher_id INT NOT NULL,
    subject VARCHAR(100) NOT NULL,
    status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
    teacher_notes TEXT,
    rejection_reason TEXT,
    enrolled_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 (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
    INDEX idx_student_id (student_id),
    INDEX idx_teacher_id (teacher_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 3. Create subject_requests table
-- ============================================
CREATE TABLE IF NOT EXISTS subject_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    requested_subject VARCHAR(200) NOT NULL,
    status ENUM('Pending', 'Assigned', 'Cancelled') DEFAULT 'Pending',
    assigned_teacher_id 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 (assigned_teacher_id) REFERENCES teachers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 4. Insert sample data for testing
-- ============================================

-- Insert sample subjects
INSERT INTO subjects (subject_name, education_level, is_active) VALUES
('Matematik', 'Both', 'Yes'),
('Bahasa Melayu', 'Both', 'Yes'),
('Bahasa Inggeris', 'Both', 'Yes'),
('Sains', 'Both', 'Yes'),
('Sejarah', 'Both', 'Yes'),
('Geografi', 'Both', 'Yes'),
('Fizik', 'Sekolah Menengah', 'Yes'),
('Kimia', 'Sekolah Menengah', 'Yes'),
('Biologi', 'Sekolah Menengah', 'Yes'),
('Pendidikan Islam', 'Both', 'Yes');

-- ============================================
-- 5. Helper Views
-- ============================================

-- View: Student enrollments with teacher info
CREATE OR REPLACE VIEW v_student_enrollments AS
SELECT 
    se.id AS enrollment_id,
    se.student_id,
    se.teacher_id,
    se.subject,
    se.status,
    se.enrolled_at,
    s.full_name AS student_name,
    s.phone AS student_phone,
    t.full_name AS teacher_name,
    t.phone AS teacher_phone
FROM student_enrollments se
JOIN students s ON se.student_id = s.id
JOIN teachers t ON se.teacher_id = t.id;

-- View: Teacher workload
CREATE OR REPLACE VIEW v_teacher_workload AS
SELECT 
    t.id AS teacher_id,
    t.full_name AS teacher_name,
    COUNT(CASE WHEN se.status = 'Approved' THEN 1 END) AS active_students,
    COUNT(CASE WHEN se.status = 'Pending' THEN 1 END) AS pending_students,
    COUNT(CASE WHEN se.status = 'Rejected' THEN 1 END) AS rejected_students,
    COUNT(DISTINCT se.subject) AS subjects_taught
FROM teachers t
LEFT JOIN student_enrollments se ON t.id = se.teacher_id
GROUP BY t.id;

-- ============================================
-- 6. Sample queries for reference
-- ============================================

-- Find available teachers for a subject
-- SELECT * FROM teachers t
-- JOIN teacher_subjects ts ON t.id = ts.teacher_id
-- WHERE ts.subject_name = 'Matematik'
-- AND ts.status = 'Active'
-- AND ts.current_students < ts.max_students;

-- Get all enrollments for a student
-- SELECT * FROM v_student_enrollments
-- WHERE student_id = 1;

-- Get all students for a teacher
-- SELECT * FROM v_student_enrollments
-- WHERE teacher_id = 1;

