<?php
session_start();
include '../../includes/db_connection.php';

if (!isset($_SESSION['user_id'])) {
    header("Location: /school_management/public/index.php");
    exit();
}

$message = '';
$error = '';

// Handle delete action
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['delete_result_id'])) {
    if ($_SESSION['role'] === 'Admin' || $_SESSION['role'] === 'Accountant' || $_SESSION['role'] === 'Teacher') {
        $result_id = $_POST['delete_result_id'];
        
        $sql = "DELETE FROM exam_results WHERE result_id = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("i", $result_id);
        
        if ($stmt->execute()) {
            $message = "ফলাফল সফলভাবে মুছে ফেলা হয়েছে!";
        } else {
            $error = "ফলাফল মোছার সময় সমস্যা হয়েছে: " . $stmt->error;
        }
        $stmt->close();
    } else {
        $error = "মুছে ফেলার অনুমতি নেই।";
    }
}

// Fetch all classes, exams, and subjects for the filter dropdowns
$classes_sql = "SELECT class_id, class_name FROM classes ORDER BY class_name";
$classes_result = $conn->query($classes_sql);

$exams_sql = "SELECT exam_id, exam_name FROM exams ORDER BY exam_name";
$exams_result = $conn->query($exams_sql);

$subjects_sql = "SELECT subject_id, subject_name FROM subjects ORDER BY subject_name";
$subjects_result = $conn->query($subjects_sql);

// Removed 'er.grade' from the query
$report_sql = "SELECT er.result_id, s.first_name, s.last_name, s.class_roll, c.class_name, sub.subject_name, e.exam_name, er.marks
               FROM exam_results er
               JOIN students s ON er.student_id = s.student_id
               JOIN classes c ON s.class_id = c.class_id
               JOIN subjects sub ON er.subject_id = sub.subject_id
               JOIN exams e ON er.exam_id = e.exam_id
               WHERE 1=1";

$params = [];
$types = '';

if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['filter_report'])) {
    if (!empty($_POST['class_id'])) {
        $report_sql .= " AND c.class_id = ?";
        $params[] = $_POST['class_id'];
        $types .= 'i';
    }
    if (!empty($_POST['exam_id'])) {
        $report_sql .= " AND e.exam_id = ?";
        $params[] = $_POST['exam_id'];
        $types .= 'i';
    }
    if (!empty($_POST['subject_id'])) {
        $report_sql .= " AND sub.subject_id = ?";
        $params[] = $_POST['subject_id'];
        $types .= 'i';
    }
}

$report_sql .= " ORDER BY c.class_name, s.class_roll, e.exam_name, sub.subject_name";

$stmt = $conn->prepare($report_sql);

if ($stmt === false) {
    $error = "SQL কোয়েরি তৈরিতে সমস্যা: " . $conn->error;
    $report_result = false;
} else {
    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }
    
    if (!$stmt->execute()) {
        $error = "রিপোর্ট তৈরিতে সমস্যা: " . $stmt->error;
        $report_result = false;
    } else {
        $report_result = $stmt->get_result();
    }
    $stmt->close();
}
?>

<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>পরীক্ষার ফলাফল রিপোর্ট</title>
    <link rel="stylesheet" href="/school_management/public/css/style.css">
    <style>
        .report-form {
            background-color: #f2f2f2;
            padding: 15px;
            border-radius: 8px;
            margin-bottom: 20px;
            display: flex;
            gap: 15px;
            align-items: flex-end;
        }
        .report-form .form-group {
            flex: 1;
            display: flex;
            flex-direction: column;
        }
        .report-form label {
            margin-bottom: 5px;
            font-weight: bold;
        }
        .report-form select, .report-form input, .report-form button {
            padding: 8px;
            border-radius: 4px;
            border: 1px solid #ccc;
        }
        .report-form button {
            background-color: #28a745;
            color: white;
            cursor: pointer;
            border: none;
        }
        .report-form button:hover {
            background-color: #218838;
        }
        .report-table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        .report-table th, .report-table td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
            font-size: 14px;
        }
        .report-table th {
            background-color: #f2f2f2;
        }
        .actions a, .actions button {
            text-decoration: none;
            padding: 5px 10px;
            border-radius: 3px;
            font-size: 14px;
            color: white;
            margin-right: 5px;
        }
        .actions .edit-btn {
            background-color: #007bff;
            border: none;
        }
        .actions .delete-btn {
            background-color: #dc3545;
            border: none;
            cursor: pointer;
        }
    </style>
</head>
<body>
<div class="header">
    <h1>স্কুল ম্যানেজমেন্ট সিস্টেম</h1>
    <div class="nav">
        <a href="/school_management/public/dashboard.php">ড্যাশবোর্ড</a>
        <a href="/school_management/public/students/students_list.php">শিক্ষার্থী</a>
        <a href="/school_management/public/teachers/teachers_list.php">শিক্ষক</a>
        <a href="/school_management/public/fees/fee_collection.php">ফি সংগ্রহ</a>
        <a href="/school_management/public/subjects/subjects_list.php">বিষয়সমূহ</a>
        <a href="/school_management/public/exam_results/add_exam_result.php">পরীক্ষা</a>
        <a href="/school_management/public/attendance/record_attendance.php">উপস্থিতি</a>
        <a href="/school_management/public/change_password.php">পাসওয়ার্ড পরিবর্তন</a>
        <a href="/school_management/public/logout.php">লগআউট</a>
    </div>
</div>
<div class="content">
    <div class="container">
        <h2>পরীক্ষার ফলাফল রিপোর্ট</h2>
        
        <?php if ($message): ?>
            <p style='color:green;'><?php echo $message; ?></p>
        <?php endif; ?>
        <?php if ($error): ?>
            <p style='color:red;'><?php echo $error; ?></p>
        <?php endif; ?>

        <form method="POST" action="" class="report-form">
            <input type="hidden" name="filter_report" value="1">
            <div class="form-group">
                <label for="class_id">শ্রেণি নির্বাচন করুন:</label>
                <select id="class_id" name="class_id">
                    <option value="">সকল শ্রেণি</option>
                    <?php 
                    if ($classes_result && $classes_result->num_rows > 0) {
                        while($row = $classes_result->fetch_assoc()) {
                            $selected = (isset($_POST['class_id']) && $_POST['class_id'] == $row['class_id']) ? 'selected' : '';
                            echo "<option value='" . htmlspecialchars($row['class_id']) . "' $selected>" . htmlspecialchars($row['class_name']) . "</option>";
                        }
                    }
                    ?>
                </select>
            </div>
            <div class="form-group">
                <label for="exam_id">পরীক্ষার ধরন:</label>
                <select id="exam_id" name="exam_id">
                    <option value="">সকল পরীক্ষা</option>
                    <?php 
                    if ($exams_result && $exams_result->num_rows > 0) {
                        while($row = $exams_result->fetch_assoc()) {
                            $selected = (isset($_POST['exam_id']) && $_POST['exam_id'] == $row['exam_id']) ? 'selected' : '';
                            echo "<option value='" . htmlspecialchars($row['exam_id']) . "' $selected>" . htmlspecialchars($row['exam_name']) . "</option>";
                        }
                    }
                    ?>
                </select>
            </div>
            <div class="form-group">
                <label for="subject_id">বিষয়:</label>
                <select id="subject_id" name="subject_id">
                    <option value="">সকল বিষয়</option>
                    <?php 
                    if ($subjects_result && $subjects_result->num_rows > 0) {
                        while($row = $subjects_result->fetch_assoc()) {
                            $selected = (isset($_POST['subject_id']) && $_POST['subject_id'] == $row['subject_id']) ? 'selected' : '';
                            echo "<option value='" . htmlspecialchars($row['subject_id']) . "' $selected>" . htmlspecialchars($row['subject_name']) . "</option>";
                        }
                    }
                    ?>
                </select>
            </div>
            <button type="submit">রিপোর্ট দেখুন</button>
        </form>

        <?php if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['filter_report']) && $report_result !== false): ?>
            <h3>ফলাফলের তালিকা</h3>
            <table class="report-table">
                <thead>
                    <tr>
                        <th>শিক্ষার্থীর নাম</th>
                        <th>রোল</th>
                        <th>শ্রেণি</th>
                        <th>বিষয়</th>
                        <th>পরীক্ষা</th>
                        <th>প্রাপ্ত নম্বর</th>
                        <th>অ্যাকশন</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if ($report_result->num_rows > 0): ?>
                        <?php while($row = $report_result->fetch_assoc()): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($row['first_name'] . ' ' . $row['last_name']); ?></td>
                                <td><?php echo htmlspecialchars($row['class_roll']); ?></td>
                                <td><?php echo htmlspecialchars($row['class_name']); ?></td>
                                <td><?php echo htmlspecialchars($row['subject_name']); ?></td>
                                <td><?php echo htmlspecialchars($row['exam_name']); ?></td>
                                <td><?php echo htmlspecialchars($row['marks']); ?></td>
                                <td class="actions">
                                    <a href="edit_exam_result.php?id=<?php echo htmlspecialchars($row['result_id']); ?>" class="edit-btn">সম্পাদনা</a>
                                    <?php if ($_SESSION['role'] === 'Admin' || $_SESSION['role'] === 'Accountant' || $_SESSION['role'] === 'Teacher'): ?>
                                        <form method="POST" action="" style="display:inline-block;">
                                            <input type="hidden" name="delete_result_id" value="<?php echo htmlspecialchars($row['result_id']); ?>">
                                            <button type="submit" class="delete-btn" onclick="return confirm('আপনি কি নিশ্চিত যে এই ফলাফলটি মুছে ফেলতে চান?')">মুছুন</button>
                                        </form>
                                    <?php endif; ?>
                                </td>
                            </tr>
                        <?php endwhile; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="7">কোনো ফলাফল রেকর্ড পাওয়া যায়নি।</td>
                        </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        <?php endif; ?>
    </div>
</div>
<div class="footer">
    <p>&copy; <?php echo date("Y"); ?> স্কুল ম্যানেজমেন্ট সিস্টেম। সর্বস্বত্ব সংরক্ষিত।</p>
</div>
</body>
</html>