<?php

include "header.php";
include "sidebar.php";
include "search_db.php";

// ✅ Delete entire file and related data
if (isset($_GET['delete_file'])) {
    $file_id = intval($_GET['delete_file']);
    $file = mysqli_fetch_assoc(mysqli_query($con, "SELECT file_name FROM files WHERE id = $file_id"));
    if ($file) {
        $filePath = "uploads/" . $file['file_name'];
        if (file_exists($filePath)) unlink($filePath);
        mysqli_query($con, "DELETE FROM marks WHERE file_id = $file_id");
        mysqli_query($con, "DELETE FROM exams WHERE file_id = $file_id");
        mysqli_query($con, "DELETE FROM files WHERE id = $file_id");
        echo "<script>alert('File and related exam deleted'); window.location.href = window.location.pathname;</script>";
        exit;
    }
}

// ✅ Delete individual mark
if (isset($_GET['delete_mark'])) {
    $mark_id = intval($_GET['delete_mark']);
    mysqli_query($con, "DELETE FROM marks WHERE id = $mark_id");
    echo "<script>alert('Mark deleted'); window.location.href = window.location.pathname;</script>";
    exit;
}

// ✅ Update mark
if (isset($_POST['update_mark'])) {
    $mark_id = intval($_POST['mark_id']);
    $new_mark = floatval($_POST['new_mark']);
    mysqli_query($con, "UPDATE marks SET marks = '$new_mark' WHERE id = $mark_id");
    echo "<script>alert('Mark updated'); window.location.href = window.location.pathname;</script>";
    exit;
}

// ✅ Handle upload
if (isset($_POST["import"])) {
    $subject_id = intval($_POST["subject_id"]);
    $fileName = $_FILES["excel"]["name"];
    $fileExtension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));
    $newFileName = date("Ymd_His") . "." . $fileExtension;
    $targetDirectory = "uploads/" . $newFileName;
    move_uploaded_file($_FILES['excel']['tmp_name'], $targetDirectory);

    mysqli_query($con, "
        INSERT INTO files (subject_id, file_name, upload_date)
        VALUES ('$subject_id', '$newFileName', NOW())
    ");
    $file_id = mysqli_insert_id($con);

    require 'excelReader/excel_reader2.php';
    require 'excelReader/SpreadsheetReader.php';
    $reader = new SpreadsheetReader($targetDirectory);
    $rows = iterator_to_array($reader);

    $exam_title = isset($rows[0][1]) ? mysqli_real_escape_string($con, $rows[0][1]) : '';
    $exam_date  = isset($rows[1][1]) ? mysqli_real_escape_string($con, $rows[1][1]) : date("Y-m-d");

    mysqli_query($con, "
      INSERT INTO exams (subject_id, title, exam_date, file_id) 
      VALUES ('$subject_id', '$exam_title', '$exam_date', '$file_id')
    ");
    $exam_id = mysqli_insert_id($con);

    for ($i = 3; $i < count($rows); $i++) {
        $row = $rows[$i];
        if (empty($row[0]) || empty($row[1])) continue;

        $student_id = intval($row[0]);
        $name = mysqli_real_escape_string($con, $row[1]);
        $mark = isset($row[2]) ? floatval($row[2]) : null;

        if ($student_id === 0 || trim($name) === '' || !is_numeric($student_id)) continue;

        $check = mysqli_query($con, "SELECT id FROM students WHERE id = '$student_id'");
        if (mysqli_num_rows($check) == 0) {
            mysqli_query($con, "INSERT INTO students (id, name) VALUES ('$student_id', '$name')");
        }

        if (!is_null($mark)) {
            mysqli_query($con, "
                INSERT INTO marks (student_id, subject_id, exam_id, marks, file_id)
                VALUES ('$student_id', '$subject_id', '$exam_id', '$mark', '$file_id')
            ");
        }
    }

    echo "<script>alert('Import successful'); window.location.href = window.location.pathname;</script>";
}
?>

<!-- ============================================================== -->
<!-- Start right Content here -->
<!-- ============================================================== -->
<div class="main-content">
 <div class="page-content">
       <div class="container-fluid">
<?php
// ✅ Add Subject Handler
if (isset($_POST['add_subject'])) {
    $subject_name = mysqli_real_escape_string($con, $_POST['subject_name']);
    $exists = mysqli_query($con, "SELECT id FROM subjects WHERE name = '$subject_name'");
    if (mysqli_num_rows($exists) == 0) {
        mysqli_query($con, "INSERT INTO subjects (name) VALUES ('$subject_name')");
        echo "<script>alert('مضمون جدید اضافه شد'); location.href='';</script>";
        exit;
    } else {
        echo "<script>alert('این مضمون قبلاً ثبت شده است');</script>";
    }
}

?>
            <!-- start page title -->
            <div class="row">
                <div class="col-12">
                    <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                        <div class="page-title-right">
                        </div>
                    </div>
                </div>
            </div>
            <!-- end page title -->

<!-- Add Subject Button -->
<div class="d-flex justify-content-between mb-3">
    <button class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#addSubjectModal">
        اضافه کردن مضمون
    </button>
</div>
<div class="card mb-4">
  <div class="card-body">
    <h5 class="card-title">لیست مضامین</h5>
    <ul class="list-group">
      <?php
        // Handle delete request
        if (isset($_GET['delete_id'])) {
            $delete_id = intval($_GET['delete_id']);
            mysqli_query($con, "DELETE FROM subjects WHERE id = $delete_id");
        }

        // Fetch subjects
        $result = mysqli_query($con, "SELECT * FROM subjects ORDER BY id DESC");
        while ($sub = mysqli_fetch_assoc($result)) {
          echo "<li class='list-group-item d-flex justify-content-between align-items-center'>
                  {$sub['name']}
                  <a href='?delete_id={$sub['id']}' 
                     class='btn btn-danger btn-sm' 
                     onclick=\"return confirm('آیا مطمئن هستید؟');\">حذف</a>
                </li>";
        }
      ?>
    </ul>
  </div>
</div>


            <!-- Add Subject Modal -->
<div class="modal fade" id="addSubjectModal" tabindex="-1" aria-labelledby="addSubjectModalLabel" aria-hidden="true" dir="rtl">
  <div class="modal-dialog">
    <form method="post">
      <div class="modal-content">
        <div class="modal-header">
          <h5 class="modal-title" id="addSubjectModalLabel">افزودن مضمون جدید</h5>
          <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="بستن"></button>
        </div>
        <div class="modal-body">
          <div class="mb-3">
            <label for="subject_name" class="form-label">نام مضمون:</label>
            <input type="text" class="form-control" name="subject_name" id="subject_name" required>
          </div>
        </div>
        <div class="modal-footer">
          <button type="submit" name="add_subject" class="btn btn-success">ذخیره</button>
          <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">لغو</button>
        </div>
      </div>
    </form>
  </div>
</div>


            
            <div class="row">
                <div class="col-xxl-9">
                    <div class="card mt-xxl-n5">
                        <div class="card-header">
                            <ul class="nav nav-tabs-custom rounded card-header-tabs border-bottom-0" role="tablist">
                                <li class="nav-item">
                                    <a class="nav-link active" data-bs-toggle="tab" href="#personalDetails" role="tab" aria-selected="false">
                                        <i class="fas fa-home"></i> اپلود نتایج نمرات
                                    </a>
                                </li>
                            </ul>
                        </div>
                        <div class="card-body p-4">
                            <div class="tab-content">
                                <div class="tab-pane active" id="personalDetails" role="tabpanel">

                                    <div id="preloader" style="display:none; text-align:center;">
                                        <img src="assets/images/upload.gif" alt="در حال بارگذاری..." style="width: 80px;">
                                    </div>

                                 <div class="card p-4 border shadow-sm">
    <h5 class="mb-4">اپلود نمرات</h5>
    <form action="" method="post" enctype="multipart/form-data">
        <div class="mb-3">
            <label class="form-label">مضمون</label>
            <select name="subject_id" required class="form-select">  
                <option value="">انتخاب مضمون</option>
                <?php
                  $subjects = mysqli_query($con, "SELECT * FROM subjects");
                  while($sub = mysqli_fetch_assoc($subjects)) {
                    echo "<option value='{$sub['id']}'>{$sub['name']}</option>";
                  }
                ?>
            </select>
        </div>

        <div class="mb-3">
            <label class="form-label">فایل اکسسل</label>
            <input type="file" class="form-control" name="excel" required>
        </div>

        <button class="btn btn-primary w-100" type="submit" name="import">اپلود</button>
    </form>
</div>


                                    <hr>

      <div class="card p-4 mt-4" dir="rtl">
    <h4 class="mb-4">تمام نتایج</h4>
    <?php
    $subjects = mysqli_query($con, "SELECT * FROM subjects");
    while ($subject = mysqli_fetch_assoc($subjects)) {
        $exams = mysqli_query($con, "
            SELECT e.*, f.upload_date, f.file_name, f.id AS file_id
            FROM exams e
            JOIN files f ON e.file_id = f.id
            WHERE e.subject_id = '{$subject['id']}'
            ORDER BY e.exam_date DESC
        ");

        if (mysqli_num_rows($exams) == 0) continue;

        echo "<div class='mb-4'>";
        echo "<h5 class='text-primary'>مضمون: {$subject['name']}</h5>";

        while ($exam = mysqli_fetch_assoc($exams)) {
            echo "<div class='border rounded p-3 mb-3'>";
            echo "<div class='d-flex justify-content-between align-items-center flex-wrap'>";
            echo "<div><strong>عنوان نتایج:</strong> {$exam['title']}<br>
                  <strong>تاریخ امتحان:</strong> {$exam['exam_date']}<br>
                  <strong>تاریخ اپلود:</strong> {$exam['upload_date']}</div>";
            echo "<a href='?delete_file={$exam['file_id']}' class='btn btn-danger mt-2 mt-md-0' onclick=\"return confirm('Delete this file and all related data?');\">حذف فایل</a>";
            echo "</div>";

            echo "<div class='table-responsive mt-3'>";
            echo "<table class='table table-bordered table-striped table-sm align-middle text-center'>
                    <thead class='table-light'>
                      <tr>
                        <th>#</th>
                        <th>ایدی محصل</th>
                        <th>نام</th>
                        <th>نمره</th>
                        <th>عملیات</th>
                      </tr>
                    </thead>
                    <tbody>";

            $marks = mysqli_query($con, "
                SELECT m.id AS mark_id, s.id AS student_id, s.name, m.marks
                FROM marks m
                JOIN students s ON m.student_id = s.id
                WHERE m.exam_id = '{$exam['id']}'
            ");

            $i = 1;
            while ($row = mysqli_fetch_assoc($marks)) {
                echo "<tr>
                        <td>$i</td>
                        <td>{$row['student_id']}</td>
                        <td>{$row['name']}</td>
                        <td>
                          <form method='post' class='d-flex justify-content-center align-items-center gap-2'>
                            <input type='hidden' name='mark_id' value='{$row['mark_id']}'>
                            <input type='number' name='new_mark' value='{$row['marks']}' step='0.01' class='form-control form-control-sm w-auto text-center'>
                            <button type='submit' name='update_mark' class='btn btn-success btn-sm'>💾</button>
                          </form>
                        </td>
                        <td>
                          <a href='?delete_mark={$row['mark_id']}' class='btn btn-outline-danger btn-sm' onclick=\"return confirm('آیا مطمئن هستید؟');\">🗑️</a>
                        </td>
                      </tr>";
                $i++;
            }

            echo "</tbody></table></div></div>"; // end exam block
        }

        echo "</div>"; // end subject block
    }
    ?>
</div>

                                </div><!-- end tab-pane -->
                            </div><!-- end tab-content -->
                        </div><!-- end card-body -->
                    </div><!-- end card -->
                </div><!-- end col -->
            </div><!-- end row -->

        </div><!-- container-fluid -->
    </div><!-- End Page-content -->

<?php include "footer.php"; ?>

<script>
function showPreloader() {
    document.getElementById("preloader").style.display = "block";
}
</script>
