<?php
// C:\xampp\htdocs\school_management\public\routines\view_routines.php

// 1. ডেটাবেস কানেকশন অন্তর্ভুক্ত করা
include '../../includes/db_connection.php'; 

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// 2. সকল প্রয়োজনীয় ডেটাবেস কলাম নিয়ে আসার জন্য JOIN ক্যোয়ারি
$sql = "SELECT 
            r.routine_id,
            r.routine_type,
            r.exam_date,            -- **সঠিক কলামের নাম 'exam_date'**
            r.start_time,
            r.end_time,
            r.session_type,
            c.class_name,
            s.subject_name,
            e.exam_name,
            CONCAT(t.first_name, ' ', t.last_name) AS teacher_full_name
        FROM 
            routines r
        JOIN 
            classes c ON r.class_id = c.class_id
        JOIN 
            subjects s ON r.subject_id = s.subject_id
        JOIN 
            teachers t ON r.teacher_id = t.teacher_id
        LEFT JOIN 
            exams e ON r.exam_id = e.exam_id
        ORDER BY 
            r.class_id, r.exam_date, r.start_time";

$result = $conn->query($sql);

if (!$result) {
    die("Error in SQL query: " . $conn->error); 
}

// 3. ক্লাসের ডেটা লোড করা (ফিল্টারিং অপশনের জন্য)
$classes_result = $conn->query("SELECT class_id, class_name FROM classes ORDER BY class_id");

$conn->close();

// ডেটা টেবিলকে গ্রিড ফর্মে দেখানোর জন্য রুটিন ডেটা নিয়ে আসা
$routines_data = [];
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $routines_data[] = $row;
    }
}
?>

<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>সকল রুটিন দেখুন</title>
    
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    
    <style>
        .container-fluid { padding-top: 20px; }
        .table-routine td { 
            text-align: center; 
            vertical-align: middle; 
            padding: 5px;
            min-width: 150px; 
        }
        .time-slot {
            font-size: 0.9em;
            font-weight: bold;
            display: block;
            margin-top: 5px;
        }
    </style>
</head>
<body>

<div class="container-fluid">
    <h2 class="text-center mb-4">📅 স্কুল রুটিন</h2>
    
    <div class="mb-4 d-flex justify-content-center">
        <label for="class_filter" class="form-label me-2 mt-2">শ্রেণি ফিল্টার করুন:</label>
        <select id="class_filter" class="form-select w-auto" onchange="filterRoutine(this.value)">
            <option value="all">-- সকল শ্রেণি --</option>
            <?php 
            if ($classes_result->num_rows > 0) {
                while($row = $classes_result->fetch_assoc()): 
            ?>
                <option value="<?php echo htmlspecialchars($row['class_name']); ?>"><?php echo htmlspecialchars($row['class_name']); ?></option>
            <?php 
                endwhile;
            } 
            ?>
        </select>
    </div>

    <div class="table-responsive">
        <table class="table table-bordered table-routine" id="routine_grid">
            <thead class="table-dark">
                <tr>
                    <th>শ্রেণি</th>
                    <th>তারিখ / দিন</th>
                    <th>সেশন</th>
                    <th>সময়</th>
                    <th>বিষয় ও শিক্ষক</th>
                    <th>রুটিন ধরন</th>
                </tr>
            </thead>
            <tbody>
            <?php 
            if (!empty($routines_data)):
                foreach ($routines_data as $row):
                    $exam_name = empty($row['exam_name']) ? '' : ' (' . htmlspecialchars($row['exam_name']) . ')';
                    $routine_class = htmlspecialchars($row['class_name']);
                    
                    $session_bn = ($row['session_type'] == 'morning') ? 'সকাল' : 'দিবা';
            ?>
                <tr data-class="<?php echo $routine_class; ?>">
                    <td class="bg-light align-middle fw-bold"><?php echo $routine_class; ?></td>
                    <td><?php echo htmlspecialchars($row['exam_date']); ?></td>
                    <td><?php echo $session_bn; ?></td>
                    <td>
                        <span class="time-slot"><?php echo date('h:i A', strtotime($row['start_time'])); ?> -</span>
                        <span class="time-slot"><?php echo date('h:i A', strtotime($row['end_time'])); ?></span>
                    </td>
                    <td>
                        **<?php echo htmlspecialchars($row['subject_name']); ?>** <br> 
                        (শিক্ষক: <?php echo htmlspecialchars($row['teacher_full_name']); ?>)
                    </td>
                    <td>
                        <?php if ($row['routine_type'] == 'exam'): ?>
                            <span class="badge bg-danger">পরীক্ষা<?php echo $exam_name; ?></span>
                        <?php else: ?>
                            <span class="badge bg-success">ক্লাস রুটিন</span>
                        <?php endif; ?>
                    </td>
                </tr>
            <?php 
                endforeach;
            else: 
            ?>
                <tr>
                    <td colspan="6" class="text-center text-danger">⚠️ কোনো রুটিন ডেটা পাওয়া যায়নি।</td>
                </tr>
            <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<script>
function filterRoutine(className) {
    const rows = document.querySelectorAll('#routine_grid tbody tr');
    
    rows.forEach(row => {
        const rowClass = row.getAttribute('data-class');
        
        if (className === 'all' || rowClass === className) {
            row.style.display = '';
        } else {
            row.style.display = 'none';
        }
    });
}
</script>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>