<?php
include('db_connection.php');

/* =========================================================
   FETCH STUDENTS
========================================================= */
$students = [];
$res = $conn->query("SELECT student_id, name FROM students ORDER BY name ASC");
if ($res) {
    $students = $res->fetch_all(MYSQLI_ASSOC);
}

/* =========================================================
   INPUT FILTERS
========================================================= */
$student_id = $_GET['student_id'] ?? null;
$term       = $_GET['term'] ?? null;
$year       = $_GET['year'] ?? null;

/* =========================================================
   DEFAULT VARIABLES
========================================================= */
$records = [];
$student_name = "";
$serial_no = null;
$verify_url = null;
$qr_url = null;

/* =========================================================
   FETCH ACADEMIC RECORDS (ONLY WHEN FILTERS COMPLETE)
========================================================= */
if ($student_id && $term && $year) {

    $stmt = $conn->prepare("
        SELECT 
            ah.student_id,
            s.name AS student_name,
            ah.subject,
            ah.grade,
            ah.term,
            ah.year
        FROM academic_history ah
        JOIN students s ON s.student_id = ah.student_id
        WHERE ah.student_id = ? AND ah.term = ? AND ah.year = ?
        ORDER BY ah.subject ASC
    ");

    $stmt->bind_param("iss", $student_id, $term, $year);
    $stmt->execute();
    $result = $stmt->get_result();

    while ($row = $result->fetch_assoc()) {
        $records[] = $row;
        $student_name = $row['student_name'];
    }

    $stmt->close();
}

/* =========================================================
   GPA CALCULATION
========================================================= */
$total = 0;
$count = 0;

foreach ($records as $r) {
    if (is_numeric($r['grade'])) {
        $total += $r['grade'];
        $count++;
    }
}

$gpa = $count ? round($total / $count, 2) : 0;

/* =========================================================
   CLASS POSITION
========================================================= */
$class_position = 0;

if ($student_id && $term && $year) {

    $rank_sql = "
        SELECT student_id, AVG(grade) AS avg_grade
        FROM academic_history
        WHERE term = ? AND year = ?
        GROUP BY student_id
        ORDER BY avg_grade DESC
    ";

    $stmt = $conn->prepare($rank_sql);
    $stmt->bind_param("ss", $term, $year);
    $stmt->execute();
    $res = $stmt->get_result();

    $rank = 1;
    while ($row = $res->fetch_assoc()) {
        if ($row['student_id'] == $student_id) {
            $class_position = $rank;
            break;
        }
        $rank++;
    }

    $stmt->close();
}

/* =========================================================
   PROMOTION STATUS
========================================================= */
$promotion_status = ($gpa < 50) ? "REPEAT CLASS" : "PROMOTED";

/* =========================================================
   TRANSCRIPT INSERT + SERIAL + QR (FIXED CORE LOGIC)
========================================================= */
if ($student_id && $term && $year && count($records) > 0) {

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

    try {

        // CHECK IF EXISTS
        $check = $conn->prepare("
            SELECT serial_no 
            FROM transcripts 
            WHERE student_id = ? AND term = ? AND year = ?
            LIMIT 1
        ");
        $check->bind_param("iss", $student_id, $term, $year);
        $check->execute();
        $check->store_result();
        $check->bind_result($existing_serial);

        if ($check->num_rows > 0) {

            // USE EXISTING SERIAL
            $check->fetch();
            $serial_no = $existing_serial;

        } else {

            // GENERATE NEW SERIAL
            $serial_no = "TR-" . date("Y") . "-" . strtoupper(bin2hex(random_bytes(4)));

            // VERIFY URL
            $verify_url = "http://localhost/csms/admin/verify_transcript.php?serial=" . urlencode($serial_no);

            // INSERT INTO DB
            $insert = $conn->prepare("
                INSERT INTO transcripts 
                (serial_no, student_id, term, year, gpa, class_position, promotion_status, verify_url)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ");

            $insert->bind_param(
                "sissdiss",
                $serial_no,
                $student_id,
                $term,
                $year,
                $gpa,
                $class_position,
                $promotion_status,
                $verify_url
            );

            $insert->execute();
            $insert->close();
        }

        $check->close();

        // QR ALWAYS GENERATED AFTER SERIAL EXISTS
        $verify_url = "http://localhost/csms/admin/verify_transcript.php?serial=" . urlencode($serial_no);
        $qr_url = "https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=" . urlencode($verify_url);

    } catch (Exception $e) {
        die("DATABASE ERROR: " . $e->getMessage());
    }
}
?>

<!DOCTYPE html>
<html>
<head>
<title>Transcript System</title>

<style>
body { font-family: Arial; background:#eef2f7; }

.container { width:92%; margin:auto; margin-top:20px; }

.card {
    background:white;
    padding:25px;
    border-radius:12px;
    box-shadow:0 6px 20px rgba(0,0,0,0.1);
    position:relative;
}

/* OFFICIAL STAMP */
.stamp {
    position:absolute;
    top:20px;
    right:20px;
    border:3px solid #1e293b;
    color:#1e293b;
    padding:12px;
    border-radius:50%;
    font-weight:bold;
    transform:rotate(-15deg);
    opacity:0.7;
}

/* FILTER */
.filter-row {
    display:flex;
    gap:10px;
    margin-bottom:20px;
}

select, input {
    padding:10px;
    width:100%;
}

/* BADGES */
.badge {
    background:#1e293b;
    color:#fff;
    padding:6px 10px;
    border-radius:6px;
    margin-right:5px;
}

/* TABLE */
table {
    width:100%;
    border-collapse:collapse;
    margin-top:15px;
}

th {
    background:#1e293b;
    color:#fff;
    padding:10px;
}

td {
    padding:10px;
    border-bottom:1px solid #ddd;
}

/* QR */
.qr {
    text-align:right;
    margin-top:20px;
}
/* =========================
   PRINT STYLES
========================= */
@media print {

    /* Hide everything marked no-print */
    .no-print {
        display: none !important;
    }

    /* Hide buttons if any */
    button {
        display: none !important;
    }

    /* Optional: remove background for clean paper print */
    body {
        background: white !important;
    }

    .card {
        box-shadow: none !important;
        border: none !important;
    }

    /* Make text sharper for print */
    body, table {
        color: #000 !important;
    }
}
</style>
</head>

<body>

<div class="container">
<div class="card">

<!-- STAMP -->
<?php if ($serial_no): ?>
<div class="stamp">OFFICIAL<br>STAMP</div>
<?php endif; ?>

<h2 style="text-align:center;">AGID EDU TECH</h2>
<p style="text-align:center;">OFFICIAL TRANSCRIPT SYSTEM</p>

<!-- FILTER -->
<form method="GET" class="filter-row">

    <div class="no-print">
<form method="GET" class="filter-row">

    <select name="student_id">
        <option value="">Select Student</option>
        <?php foreach ($students as $s): ?>
            <option value="<?= $s['student_id'] ?>" <?= ($student_id==$s['student_id'])?'selected':'' ?>>
                <?= htmlspecialchars($s['name']) ?>
            </option>
        <?php endforeach; ?>
    </select>

    <select name="term">
        <option value="">Select Term</option>
        <option value="1st Term">1st Term</option>
        <option value="2nd Term">2nd Term</option>
        <option value="3rd Term">3rd Term</option>
    </select>

    <input type="text" name="year" placeholder="Year">

    <button type="submit">Generate</button>

</form>
</div>


<?php if ($student_id && $term && $year && $records): ?>

<h3><?= htmlspecialchars($student_name) ?></h3>

<span class="badge">Serial: <?= $serial_no ?></span>
<span class="badge">GPA: <?= $gpa ?></span>
<span class="badge">Position: <?= $class_position ?></span>
<span class="badge">Status: <?= $promotion_status ?></span>

<table>
<tr>
    <th>Subject</th>
    <th>Grade</th>
    <th>Term</th>
    <th>Year</th>
</tr>

<?php foreach ($records as $r): ?>
<tr>
    <td><?= $r['subject'] ?></td>
    <td><?= $r['grade'] ?></td>
    <td><?= $r['term'] ?></td>
    <td><?= $r['year'] ?></td>
</tr>
<?php endforeach; ?>
</table>

<?php if ($qr_url): ?>
<div class="qr">
    <img src="<?= $qr_url ?>" width="150">
    <p>Scan to verify transcript</p>
</div>
<?php endif; ?>

<?php else: ?>
<p style="text-align:center;">Select Student, Term and Year</p>
<?php endif; ?>

</div>
</div>

</body>
</html>