<?php
session_start();
error_reporting(0);
require_once("../../public/connection.php");
require_once("../../public/functions.php");

$date = date('d-M-Y');
$select = "--select--";

if (isset($_POST["teacher_upload_excel_result_btn"])) {
    $staff_id = (int)($_SESSION['staff'] ?? 0);
    $class = htmlentities(trim(mysqli_real_escape_string($connection, $_POST['class'] ?? '')));
    $term = htmlentities(trim(mysqli_real_escape_string($connection, $_POST['term'] ?? '')));
    $session = htmlentities(trim(mysqli_real_escape_string($connection, $_POST['session'] ?? '')));
    $subject = htmlentities(trim(mysqli_real_escape_string($connection, $_POST['subject'] ?? '')));

    if (empty($class) || empty($term) || empty($session) || empty($subject) || $class === $select || $term === $select || $session === $select || $subject === $select) {
        echo "<script type=\"text/javascript\">
            alert(\"Sorry: All fields (Class, Term, Session, Subject) must be selected.\");
            window.location = \"teacher_dashboard.php?teacher_excel_result_upload\";
            </script>";
        exit();
    }

    if (!empty($_FILES["uploadResult"]["tmp_name"])) {
        $file_name = $_FILES["uploadResult"]["name"];
        $file_ext = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));

        if ($file_ext !== "csv" && $file_ext !== "txt") {
            echo "<script type=\"text/javascript\">
                alert(\"Sorry: The uploaded file must be a CSV file (.csv).\");
                window.location = \"teacher_dashboard.php?teacher_excel_result_upload\";
                </script>";
            exit();
        }

        $filename = $_FILES["uploadResult"]["tmp_name"];
        $openFile = fopen($filename, "r");

        if (!$openFile) {
            echo "<script type=\"text/javascript\">
                alert(\"Error opening the uploaded file. Please try again.\");
                window.location = \"teacher_dashboard.php?teacher_excel_result_upload\";
                </script>";
            exit();
        }

        $rowCount = 0;
        $successCount = 0;

        while (($dataFile = fgetcsv($openFile, 5000, ",")) !== FALSE) {
            $rowCount++;

            // Skip empty rows
            if (empty($dataFile) || (count($dataFile) == 1 && empty($dataFile[0]))) {
                continue;
            }

            // Skip header row if detected
            $firstCol = trim($dataFile[0] ?? '');
            $fourthCol = trim($dataFile[4] ?? '');
            if ($rowCount === 1 && (!is_numeric($firstCol) || strcasecmp($fourthCol, 'Reg No') === 0 || strcasecmp($fourthCol, 'Registration Number') === 0)) {
                continue;
            }

            $row_class   = !empty(trim($dataFile[1] ?? '')) ? trim($dataFile[1]) : $class;
            $row_term    = !empty(trim($dataFile[2] ?? '')) ? trim($dataFile[2]) : $term;
            $row_session = !empty(trim($dataFile[3] ?? '')) ? trim($dataFile[3]) : $session;
            $row_reg_no  = trim($dataFile[4] ?? '');
            $row_name    = trim($dataFile[5] ?? '');
            $row_subject = !empty(trim($dataFile[6] ?? '')) ? trim($dataFile[6]) : $subject;

            if (empty($row_reg_no)) {
                continue;
            }

            $ca1_val  = isset($dataFile[7]) && is_numeric(trim($dataFile[7])) ? (float)trim($dataFile[7]) : 0;
            $ca2_val  = isset($dataFile[8]) && is_numeric(trim($dataFile[8])) ? (float)trim($dataFile[8]) : 0;
            $exam_val = isset($dataFile[9]) && is_numeric(trim($dataFile[9])) ? (float)trim($dataFile[9]) : 0;
            $subject_total = $ca1_val + $ca2_val + $exam_val;

            // Check if record exists
            $check_stmt = mysqli_prepare($connection, "SELECT id FROM results1 WHERE class = ? AND term = ? AND session = ? AND subjects = ? AND reg_number = ? LIMIT 1");
            mysqli_stmt_bind_param($check_stmt, "sssss", $row_class, $row_term, $row_session, $row_subject, $row_reg_no);
            mysqli_stmt_execute($check_stmt);
            mysqli_stmt_store_result($check_stmt);

            if (mysqli_stmt_num_rows($check_stmt) > 0) {
                mysqli_stmt_close($check_stmt);
                // Update existing record
                $upd_stmt = mysqli_prepare($connection, "UPDATE results1 SET name = ?, ca1 = ?, ca2 = ?, exam = ?, subject_total = ?, date_of_upload = ? WHERE class = ? AND term = ? AND session = ? AND subjects = ? AND reg_number = ?");
                mysqli_stmt_bind_param($upd_stmt, "sdddssssss", $row_name, $ca1_val, $ca2_val, $exam_val, $subject_total, $date, $row_class, $row_term, $row_session, $row_subject, $row_reg_no);
                if (mysqli_stmt_execute($upd_stmt)) {
                    $successCount++;
                }
                mysqli_stmt_close($upd_stmt);
            } else {
                mysqli_stmt_close($check_stmt);
                // Insert new record
                $ins_stmt = mysqli_prepare($connection, "INSERT INTO results1 (class, term, session, reg_number, name, subjects, ca1, ca2, exam, subject_total, date_of_upload) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                mysqli_stmt_bind_param($ins_stmt, "ssssssdddss", $row_class, $row_term, $row_session, $row_reg_no, $row_name, $row_subject, $ca1_val, $ca2_val, $exam_val, $subject_total, $date);
                if (mysqli_stmt_execute($ins_stmt)) {
                    $successCount++;
                }
                mysqli_stmt_close($ins_stmt);
            }
            $processed_subjects[$row_subject] = true;
        }
        fclose($openFile);

        // Recalculate subject rankings for all subjects processed in this upload
        if (empty($processed_subjects) && !empty($subject) && $subject !== 'ALL') {
            $processed_subjects[$subject] = true;
        }

        foreach (array_keys($processed_subjects) as $sbj) {
            $rank_query = mysqli_query($connection, "SELECT id, subject_total FROM `results1` WHERE `subjects` = '{$sbj}' AND `class` = '{$class}' AND `term` = '{$term}' AND `session` = '{$session}' ORDER BY `subject_total` DESC");
            if ($rank_query && mysqli_num_rows($rank_query) > 0) {
                $i = 1;
                $rank = 1;
                $prev_total = null;
                while ($out = mysqli_fetch_assoc($rank_query)) {
                    $rec_id = (int)$out['id'];
                    $s_tot = (float)$out['subject_total'];
                    if ($prev_total !== null && $s_tot == $prev_total) {
                        // Tie rank
                    } else {
                        $rank = $i;
                    }
                    mysqli_query($connection, "UPDATE `results1` SET `subject_rank` = '{$rank}' WHERE `id` = '{$rec_id}'");
                    $prev_total = $s_tot;
                    $i++;
                }
            }
        }

        echo "<script type=\"text/javascript\">
            alert(\"Result CSV file successfully imported! Processed {$successCount} student score records.\");
            window.location = \"teacher_dashboard.php?teacher_excel_result_upload\";
            </script>";
        exit();
    } else {
        echo "<script type=\"text/javascript\">
            alert(\"Please select a CSV file to upload.\");
            window.location = \"teacher_dashboard.php?teacher_excel_result_upload\";
            </script>";
        exit();
    }
} else {
    header("Location: teacher_dashboard.php?teacher_excel_result_upload");
    exit();
}
?>