<div class='page-title'>
  <div>
    <h1><i class='fa fa-laptop'></i> Result Management</h1>
    <p>Excel / CSV Spreadsheet Result Upload & Export</p>
  </div>
  <div>
    <ul class='breadcrumb'>
      <li><a href='teacher_dashboard.php'><i class='fa fa-home fa-lg'></i></a></li>
      <li><a href='#'>Result Management</a></li>
      <li><a href='#'>Excel / CSV Result Upload</a></li>
    </ul>
  </div>
</div>

<?php
    $cur_season_q = mysqli_query($connection, "SELECT * FROM `current_season` LIMIT 1");
    $cur_season_r = mysqli_fetch_assoc($cur_season_q);
    $current_term = $cur_season_r['current_term'] ?? 'First Term';
    $current_session = $cur_season_r['current_session'] ?? '';
    $terms = ['First Term', 'Second Term', 'Third Term'];
    $staff_id = (int)($_SESSION['staff'] ?? 0);
?>

<div class='row'>
    <!-- Export Panel -->
    <div class='col-md-6'>
        <div class='panel panel-primary'>
            <div class='panel-heading'>
                <h4 class='text-center'><i class='fa fa-download'></i> Export Spreadsheet Score Sheet</h4>
            </div>
            <div class='panel-body'>
                <p class="text-muted">Export a pre-populated CSV template with registered students for your class and subject. Open in Excel, fill scores, save, and import.</p>
                <form method='POST' action='teacher_PHPExcel.php'>
                    <div class='form-group'>
                        <label>Class:</label>
                        <select class='form-control' name='class' required>
                            <option value="" selected disabled>-- Select Class --</option>
                            <?php
                                $query = "SELECT DISTINCT class FROM `teacher_classes` WHERE teacher_id = '{$staff_id}'";
                                $run_query = mysqli_query($connection, $query);
                                if ($run_query && mysqli_num_rows($run_query) > 0) {
                                    while ($result = mysqli_fetch_assoc($run_query)) {
                                        $cls = htmlspecialchars($result['class']);
                                        echo "<option value='{$cls}'>{$cls}</option>";
                                    }
                                } else {
                                    // Fallback for VPs/Admins logging in via teacher portal or all classes
                                    $q2 = mysqli_query($connection, "SELECT DISTINCT classes FROM `classes`");
                                    while ($r2 = mysqli_fetch_assoc($q2)) {
                                        $cls = htmlspecialchars($r2['classes']);
                                        echo "<option value='{$cls}'>{$cls}</option>";
                                    }
                                }
                            ?>
                        </select>
                    </div>

                    <div class='form-group'>
                        <label>Subject Format:</label>
                        <select class='form-control' name='subject' required>
                            <option value="" selected disabled>-- Select Subject / Format --</option>
                            <option value="ALL" style="font-weight:bold; color:#1b5e20;">★ All Subjects Combination (Class Combined Sheet)</option>
                            <option disabled>────────── Single Subjects ──────────</option>
                            <?php
                                $query = "SELECT DISTINCT `subjects` FROM `school_subjects` ORDER BY `subjects` ASC";
                                $run_query = mysqli_query($connection, $query);
                                if ($run_query && mysqli_num_rows($run_query) > 0) {
                                    while ($result = mysqli_fetch_assoc($run_query)) {
                                        $sbj = htmlspecialchars($result['subjects']);
                                        echo "<option value='{$sbj}'>{$sbj} (Single Subject)</option>";
                                    }
                                }
                            ?>
                        </select>
                    </div>

                    <div class='form-group'>
                        <label>Term:</label>
                        <select class='form-control' name='term' required>
                            <?php
                                foreach ($terms as $t) {
                                    $selected = ($t === $current_term) ? 'selected' : '';
                                    echo "<option value='{$t}' {$selected}>{$t}</option>";
                                }
                            ?>
                        </select>
                    </div>

                    <div class='form-group'>
                        <label>Session:</label>
                        <select class='form-control' name='session' required>
                            <?php
                                $query = "SELECT DISTINCT `sessions` FROM `sessions` ORDER BY `id` DESC";
                                $run_query = mysqli_query($connection, $query);
                                if ($run_query && mysqli_num_rows($run_query) > 0) {
                                    while ($result = mysqli_fetch_assoc($run_query)) {
                                        $sess = htmlspecialchars($result['sessions']);
                                        $selected = ($sess === $current_session) ? 'selected' : '';
                                        echo "<option value='{$sess}' {$selected}>{$sess}</option>";
                                    }
                                }
                            ?>
                        </select>
                    </div>

                    <button type='submit' name='export_result_btn' class='btn btn-primary btn-block'>
                        <i class='fa fa-file-excel-o'></i> Export Score Sheet (CSV)
                    </button>
                </form>
            </div>
        </div>
    </div>

    <!-- Import Panel -->
    <div class='col-md-6'>
        <div class='panel panel-success'>
            <div class='panel-heading'>
                <h4 class='text-center'><i class='fa fa-upload'></i> Import Spreadsheet Score Sheet</h4>
            </div>
            <div class='panel-body'>
                <p class="text-muted">Upload the completed CSV file. The system will automatically calculate totals, store CA1, CA2 & Exam scores, and update subject rankings.</p>
                <form method='POST' action='teacher_result_upload_excel.php' enctype='multipart/form-data'>
                    <div class='form-group'>
                        <label>Class:</label>
                        <select class='form-control' name='class' required>
                            <option value="" selected disabled>-- Select Class --</option>
                            <?php
                                $query = "SELECT DISTINCT class FROM `teacher_classes` WHERE teacher_id = '{$staff_id}'";
                                $run_query = mysqli_query($connection, $query);
                                if ($run_query && mysqli_num_rows($run_query) > 0) {
                                    while ($result = mysqli_fetch_assoc($run_query)) {
                                        $cls = htmlspecialchars($result['class']);
                                        echo "<option value='{$cls}'>{$cls}</option>";
                                    }
                                } else {
                                    $q2 = mysqli_query($connection, "SELECT DISTINCT classes FROM `classes`");
                                    while ($r2 = mysqli_fetch_assoc($q2)) {
                                        $cls = htmlspecialchars($r2['classes']);
                                        echo "<option value='{$cls}'>{$cls}</option>";
                                    }
                                }
                            ?>
                        </select>
                    </div>

                    <div class='form-group'>
                        <label>Subject Format:</label>
                        <select class='form-control' name='subject' required>
                            <option value="" selected disabled>-- Select Subject / Format --</option>
                            <option value="ALL" style="font-weight:bold; color:#1b5e20;">★ All Subjects Combination (Class Combined Sheet)</option>
                            <option disabled>────────── Single Subjects ──────────</option>
                            <?php
                                $query = "SELECT DISTINCT `subjects` FROM `school_subjects` ORDER BY `subjects` ASC";
                                $run_query = mysqli_query($connection, $query);
                                if ($run_query && mysqli_num_rows($run_query) > 0) {
                                    while ($result = mysqli_fetch_assoc($run_query)) {
                                        $sbj = htmlspecialchars($result['subjects']);
                                        echo "<option value='{$sbj}'>{$sbj} (Single Subject)</option>";
                                    }
                                }
                            ?>
                        </select>
                    </div>

                    <div class='form-group'>
                        <label>Term:</label>
                        <select class='form-control' name='term' required>
                            <?php
                                foreach ($terms as $t) {
                                    $selected = ($t === $current_term) ? 'selected' : '';
                                    echo "<option value='{$t}' {$selected}>{$t}</option>";
                                }
                            ?>
                        </select>
                    </div>

                    <div class='form-group'>
                        <label>Session:</label>
                        <select class='form-control' name='session' required>
                            <?php
                                $query = "SELECT DISTINCT `sessions` FROM `sessions` ORDER BY `id` DESC";
                                $run_query = mysqli_query($connection, $query);
                                if ($run_query && mysqli_num_rows($run_query) > 0) {
                                    while ($result = mysqli_fetch_assoc($run_query)) {
                                        $sess = htmlspecialchars($result['sessions']);
                                        $selected = ($sess === $current_session) ? 'selected' : '';
                                        echo "<option value='{$sess}' {$selected}>{$sess}</option>";
                                    }
                                }
                            ?>
                        </select>
                    </div>

                    <div class='form-group'>
                        <label>Select CSV File (.csv):</label>
                        <input type='file' name='uploadResult' class='form-control' accept='.csv, text/csv' required />
                    </div>

                    <button type='submit' name='teacher_upload_excel_result_btn' class='btn btn-success btn-block'>
                        <i class='fa fa-upload'></i> Import Scores from CSV
                    </button>
                </form>
            </div>
        </div>
    </div>
</div>

<div class='row'>
    <div class='col-md-12'>
        <div class='panel panel-info'>
            <div class='panel-heading'>
                <h4><i class='fa fa-info-circle'></i> CSV Spreadsheet Format Guidelines</h4>
            </div>
            <div class='panel-body'>
                <p>Ensure your CSV file contains the following columns in order:</p>
                <div class='table-responsive'>
                    <table class='table table-bordered table-striped'>
                        <thead>
                            <tr class='bg-primary'>
                                <th>Col 1 (A)</th>
                                <th>Col 2 (B)</th>
                                <th>Col 3 (C)</th>
                                <th>Col 4 (D)</th>
                                <th>Col 5 (E)</th>
                                <th>Col 6 (F)</th>
                                <th>Col 7 (G)</th>
                                <th>Col 8 (H)</th>
                                <th>Col 9 (I)</th>
                                <th>Col 10 (J)</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td><strong>S/N</strong></td>
                                <td><strong>Class</strong></td>
                                <td><strong>Term</strong></td>
                                <td><strong>Session</strong></td>
                                <td><strong>Reg No</strong></td>
                                <td><strong>Name</strong></td>
                                <td><strong>Subject</strong></td>
                                <td><strong>1ST CA (20)</strong></td>
                                <td><strong>2ND CA (20)</strong></td>
                                <td><strong>Exam Score (60)</strong></td>
                            </tr>
                            <tr>
                                <td>1</td>
                                <td>JSS 1A</td>
                                <td>First Term</td>
                                <td>2024/2025</td>
                                <td>EDF/2024/001</td>
                                <td>John Doe</td>
                                <td>Mathematics</td>
                                <td>18</td>
                                <td>17</td>
                                <td>52</td>
                            </tr>
                        </tbody>
                    </table>
                </div>
                <p class='text-muted'><i class='fa fa-lightbulb-o'></i> <strong>Tip:</strong> Click <strong>"Export Score Sheet (CSV)"</strong> above to download the student list, fill in scores in Excel or Google Sheets, save as CSV, and upload!</p>
            </div>
        </div>
    </div>
</div>