<?php 
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

if (!isset($_SESSION['user_id']) || !isset($_SESSION['role']) || $_SESSION['role'] !== 'Sponsor') {
    header('Location: login.php');
    exit();
}
$username = $_SESSION['username'];

// ✅ FIX: Initialize $pdo before using it
$pdo = new PDO("mysql:host=localhost;dbname=luziraco_LUZIRA", "luziraco_Staff", "Luzira@2025");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Fetch sponsored students count per class
$classes = ['P1', 'P2', 'P3', 'P4', 'P5', 'P6', 'P7'];
$sponsoredCounts = [];
$totalSponsored = 0;
foreach ($classes as $class) {
    $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM STUDENTS WHERE CLASS = ? AND STATUS = 'Sponsored'");
    $stmt->execute([$class]);
    $count = $stmt->fetch(PDO::FETCH_ASSOC)['count'];
    $sponsoredCounts[$class] = $count;
    $totalSponsored += $count;
}

$CLASSES = $pdo->query("SELECT DISTINCT CLASS FROM STUDENTS ORDER BY CLASS")->fetchAll(PDO::FETCH_ASSOC);

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['submit_midterm'])) {
    require_once('db_connection.php');
}
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['submit_midterm'])) {
    require_once('db_connection.php');

    $student_id = $_POST['student_id'];
    $class = $_POST['class'];
    $mtc = $_POST['mtc'];
    $eng = $_POST['eng'];
    $sst = $_POST['sst'];
    $sci = $_POST['sci'];
    $total = $mtc + $eng + $sst + $sci;

    function getAggregate($mark) {
        if ($mark >= 90) return 1;
        elseif ($mark >= 80) return 2;
        elseif ($mark >= 70) return 3;
        elseif ($mark >= 60) return 4;
        elseif ($mark >= 55) return 5;
        elseif ($mark >= 50) return 6;
        elseif ($mark >= 45) return 7;
        elseif ($mark >= 40) return 8;
        else return 9;
    }

    $total_agg = getAggregate($mtc) + getAggregate($eng) + getAggregate($sst) + getAggregate($sci);
    $division = ($total_agg <= 12) ? 'I' : (($total_agg <= 24) ? 'II' : (($total_agg <= 28) ? 'III' : (($total_agg <= 32) ? 'IV' : 'U')));
    $remarks = ($division == 'I') ? "Excellent" : (($division == 'II') ? "Good" : (($division == 'III') ? "Fair" : "Poor"));

    $stmt = $pdo->prepare("
        INSERT INTO midterm_results (
            STUDENT_ID, STUDENT_NAME, CLASS, MTC, ENG, SST, SCI, TOTAL_MARKS, TOTAL_AGG, DIVISION, REMARKS
        ) VALUES (
            ?, (SELECT STUDENT_NAME FROM STUDENTS WHERE STUDENT_ID = ?), ?, ?, ?, ?, ?, ?, ?, ?, ?
        )
    ");
    $inserted = $stmt->execute([
        $student_id, $student_id, $class, $mtc, $eng, $sst, $sci, $total, $total_agg, $division, $remarks
    ]);
    $midtermMessage = $inserted ? "✅ Midterm result added successfully." : "❌ Failed to add result.";
}

if (isset($_POST['action'])) {
    if ($_POST['action'] === 'add_student') {
        $stmt = $pdo->prepare("INSERT INTO STUDENTS (STUDENT_NAME, CLASS, SEX) VALUES (?, ?, ?)");
        $stmt->execute([$_POST['student_name'], $_POST['class'], $_POST['sex']]);
        exit('Student added successfully');
    }
    if ($_POST['action'] === 'add_result') {
        $student_id = $_POST['student_id'];
        $stmt = $pdo->prepare("SELECT STUDENT_NAME FROM STUDENTS WHERE STUDENT_ID = ?");
        $stmt->execute([$student_id]);
        $student = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($student) {
            $mtc = $_POST['mtc'];
            $eng = $_POST['eng'];
            $sst = $_POST['sst'];
            $sci = $_POST['sci'];
            $total = $mtc + $eng + $sst + $sci;
            $division = ($total >= 320) ? 'DIV 1' : (($total >= 280) ? 'DIV 2' : (($total >= 200) ? 'DIV 3' : 'DIV 4'));
            $stmt = $pdo->prepare("INSERT INTO RESULTS (STUDENT_ID, STUDENT_NAME, CLASS, MTC, ENG, SST, SCI, TOTAL_MARKS, DIVISION) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
            $stmt->execute([$student_id, $student['STUDENT_NAME'], $_POST['class'], $mtc, $eng, $sst, $sci, $total, $division]);
            exit('Result added successfully');
        } else {
            exit('Student not found');
        }
    }
}

$studentsByClass = [];
$classes = ['P1','P2','P3','P4','P5','P6','P7'];
foreach ($classes as $class) {
    $stmt = $pdo->prepare("SELECT STUDENT_ID, STUDENT_NAME FROM STUDENTS WHERE CLASS = ?");
    $stmt->execute([$class]);
    $studentsByClass[$class] = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
$students = $pdo->query("SELECT * FROM STUDENTS")->fetchAll(PDO::FETCH_ASSOC);



if (isset($_POST['ajax']) && $_POST['ajax'] === 'get_students') {
    header('Content-Type: application/json');

    $class = $_POST['class'] ?? '';
    $stmt = $pdo->prepare("SELECT STUDENT_ID, STUDENT_NAME FROM STUDENTS WHERE CLASS = ? AND STATUS = 'Sponsored'");
    $stmt->execute([$class]);
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($students);
    exit;
}

?>

<!DOCTYPE html>
<html>
<head>
    <title>School Results Management System</title>
    <style>
        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background: linear-gradient(to right, #e3f2fd, #ffffff);
            margin: 0; padding: 0;
        }
        header, footer {
            background: #007BFF;
            color: white;
            padding: 15px;
            text-align: center;
        }

        .summary-grid {
    display: flex;
    flex-wrap: wrap;
    gap: 15px;
    margin-bottom: 25px;
    justify-content: space-between;
}

.summary-card {
    flex: 1 1 calc(12.5% - 15px);
    min-width: 100px;
    background: #fff;
    border-radius: 12px;
    padding: 20px 10px;
    text-align: center;
    box-shadow: 0 4px 8px rgba(0,0,0,0.1);
    border-top: 5px solid #007BFF;
    transition: transform 0.2s ease;
}
.summary-card:hover {
    transform: scale(1.05);
}

.summary-card h3 {
    font-size: 1.2em;
    margin-bottom: 8px;
    color: #007BFF;
}

.summary-card.total {
    background: #007BFF;
    color: white;
    border-top: 5px solid gold;
}
.summary-card.total h3, .summary-card.total p {
    color: white;
}

        .container {
            padding: 20px;
            max-width: 1100px;
            margin: auto;
        }
        .card {
            background: white;
            border-radius: 10px;
            box-shadow: 0 2px 8px rgba(0,0,0,0.15);
            padding: 20px;
            margin-bottom: 20px;
        }
        h3 {
            color: #007BFF;
        }
         h2 {
            color:rgb(254, 255, 255);
        }
        form {
            display: flex;
            flex-wrap: wrap;
            gap: 10px;
        }
        select, input, button {
            padding: 10px;
            border-radius: 5px;
            border: 1px solid #ccc;
            flex: 1;
        }
        button {
            background: #007BFF;
            color: white;
            cursor: pointer;
        }
        button:hover {
            background:rgba(13, 18, 24, 0.61);
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 15px;
        }
        th, td {
            padding: 10px;
            border: 1px solid #ccc;
            text-align: center;
        }
        th {
            background: #007BFF;
            color: white;
        }
        tr:hover {
            background-color:rgb(212, 206, 206);
        }
        .welcome-bar {
            background: #f1f1f1;
            text-align: right;
            padding: 10px;
            font-size: 14px;
        }
        .welcome-bar a {
            color: #007BFF;
            text-decoration: none;
        }
        .footer-note {
            font-size: 14px;
            margin-top: 10px;
            background: #222;
            color: gold;
            padding: 10px;
        }
        .footer-note a {
            color: gold;
        }
    </style>
</head>
<body>

<header>
    <h2>TON AND LIA LUZIRA SCHOOL PROJECT</h2>
</header>

<div class="welcome-bar">
    Welcome, <strong><?= htmlspecialchars($username) ?></strong> |
    <a href="logout.php">Logout</a>
</div>
<div class="container">

    <div class="card">
        <h3>Show Sponsored Students by Class</h3>
        <form method="GET">
            <select name="view_class" required>
                <option value="">Select Class</option>
                <?php foreach ($classes as $class): ?>
                    <option value="<?= $class ?>"><?= $class ?></option>
                <?php endforeach; ?>
            </select>
            <button type="submit" name="view_students">Details</button>
        </form>
        <?php if (isset($_GET['view_students'])):
            $stmt = $pdo->prepare("SELECT * FROM STUDENTS WHERE CLASS = ? AND STATUS = 'Sponsored'");
            $stmt->execute([$_GET['view_class']]);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
            if ($rows): ?>
                <table>
                    <tr><th>ID</th><th>Name</th><th>Class</th><th>Sex</th></tr>
                    <?php foreach ($rows as $row): ?>
                        <tr>
                            <td><?= $row['STUDENT_ID'] ?></td>
                            <td><?= $row['STUDENT_NAME'] ?></td>
                            <td><?= $row['CLASS'] ?></td>
                            <td><?= $row['SEX'] ?></td>
                        </tr>
                    <?php endforeach; ?>
                </table>
            <?php else: ?>
                <p>No students found in this class.</p>
            <?php endif; ?>
        <?php endif; ?>
    </div>

    <div class="summary-grid">
    <?php foreach ($sponsoredCounts as $class => $count): ?>
        <div class="summary-card">
            <h3><?= htmlspecialchars($class) ?></h3>
            <p><?= $count ?> Sponsored</p>
        </div>
    <?php endforeach; ?>
    <div class="summary-card total">
        <h3>Total Sponsored</h3>
        <p><?= $totalSponsored ?></p>
    </div>
</div>

<div class="card">
    <h3>Download a report card for an individual (one) student</h3>
    <form id="reportForm" method="post" target="_blank">
        <!-- Class -->
        <select name="report_class" id="report_class" required>
            <option value="">Select Class</option>
            <?php foreach ($CLASSES as $CLASS): ?>
                <option value="<?= htmlspecialchars($CLASS['CLASS']) ?>">
                    <?= htmlspecialchars($CLASS['CLASS']) ?>
                </option>
            <?php endforeach; ?>
        </select>

        <!-- Student -->
        <select name="student_id" id="student_id" required>
            <option value="">Select Student</option>
        </select>

        <!-- Term -->
        <select name="report_term" required>
            <option value="">Select Term</option>
            <option value="Term 1">Term 1</option>
            <option value="Term 2">Term 2</option>
            <option value="Term 3">Term 3</option>
        </select>

        <!-- Year -->
        <select name="report_year" required>
            <option value="">Select Year</option>
            <option value="2025">2025</option>
            <option value="2026">2026</option>
            <option value="2027">2027</option>
        </select>

        <button type="submit">Generate Student Report</button>
    </form>
</div>

<!-- JavaScript -->
<script>
document.getElementById('report_class').addEventListener('change', function () {
    const classSelected = this.value;
    const studentSelect = document.getElementById('student_id');
    const form = document.getElementById('reportForm');

    // Set form action based on class
    if (['P1', 'P2', 'P3'].includes(classSelected)) {
        form.action = 'lower_sponsored_report.php';
    } else if (['P4', 'P5', 'P6', 'P7'].includes(classSelected)) {
        form.action = 'single_sponsored_report.php';
    } else {
        form.action = ''; // default empty
    }

    // Load students
    studentSelect.innerHTML = '<option>Loading...</option>';
    if (classSelected) {
        fetch(window.location.href, {
            method: 'POST',
            headers: {
                'Content-Type': 'application/x-www-form-urlencoded'
            },
            body: 'ajax=get_students&class=' + encodeURIComponent(classSelected)
        })
        .then(response => response.json())
        .then(data => {
            studentSelect.innerHTML = '<option value="">Select Student</option>';
            if (data.length > 0) {
                data.forEach(function(student) {
                    const option = document.createElement('option');
                    option.value = student.STUDENT_ID;
                    option.text = student.STUDENT_NAME + ' (' + student.STUDENT_ID + ')';
                    studentSelect.appendChild(option);
                });
            } else {
                studentSelect.innerHTML = '<option value="">No Sponsored Students Found</option>';
            }
        })
        .catch(error => {
            console.error('Error:', error);
            studentSelect.innerHTML = '<option value="">Error loading students</option>';
        });
    }
});
</script>



<div class="card">
    <h3>VIEW Midterm Results by Class</h3>
   <div class="actions">
        <form id="downloadMidtermForm" action="midterm_sponsored.php" method="post" target="_blank">
            <select name="CLASS" required>
                <option value="">SELECT CLASS</option>
                <?php foreach ($CLASSES as $CLASS): ?>
                    <option value="<?= htmlspecialchars($CLASS['CLASS']) ?>"><?= htmlspecialchars($CLASS['CLASS']) ?></option>
                <?php endforeach; ?>
            </select>
            <select name="TERM" required>
                <option value="">SELECT TERM</option>
                <option value="Term 1">Term 1</option>
                <option value="Term 2">Term 2</option>
                <option value="Term 3">Term 3</option>
            </select>
            <select name="YEAR" required>
                <option value="">SELECT YEAR</option>
                <option value="2025">2025</option>
                <option value="2026">2026</option>
                <option value="2027">2027</option>
            </select>
            <button type="submit">Download Midterm Results (PDF)</button>
        </form>
    </div></td>
    </tr></table>



    <div class="card">
    <h3>End Of Term Results by Class</h3>
    <form method="GET">
        <select name="result_class" required>
            <option value="">Select Class</option>
            <?php foreach ($classes as $class): ?>
                <option value="<?= $class ?>"><?= $class ?></option>
            <?php endforeach; ?>
        </select>

        <select name="result_term" required>
            <option value="">Select Term</option>
            <option value="Term 1">Term 1</option>
            <option value="Term 2">Term 2</option>
            <option value="Term 3">Term 3</option>
        </select>

        <input type="number" name="result_year" placeholder="Enter Year (e.g. 2025)" min="2000" max="2100" required>


        <button type="submit" name="view_results">View Results</button>
    </form>

    <?php
    if (isset($_GET['view_results'])):
        $stmt = $pdo->prepare("
            SELECT r.*
            FROM RESULTS r
            JOIN STUDENTS s ON r.STUDENT_ID = s.STUDENT_ID
            WHERE r.CLASS = ? AND r.TERM = ? AND r.YEAR = ? AND s.STATUS = 'Sponsored'
        ");
        $stmt->execute([
            $_GET['result_class'],
            $_GET['result_term'],
            $_GET['result_year']
        ]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (!empty($results)): ?>
            <table border="1" cellpadding="5" cellspacing="0">
                <tr>
                    <?php foreach (array_keys($results[0]) as $col): ?>
                        <th><?= htmlspecialchars($col) ?></th>
                    <?php endforeach; ?>
                </tr>
                <?php foreach ($results as $row): ?>
                    <tr>
                        <?php foreach ($row as $value): ?>
                            <td><?= htmlspecialchars($value) ?></td>
                        <?php endforeach; ?>
                    </tr>
                <?php endforeach; ?>
            </table>
        <?php else: ?>
            <p>No results found for the selected class, term, and year.</p>
        <?php endif;
    endif;
    ?>
</div>


<footer>
    &copy; <?= date('Y') ?> Luzira COU Primary School - Results Portal
    <div class="footer-note">
        <a href="https://www.ristandardshop.com">Designed, Developed & Powered By Ri Standard Services Co. Ltd</a>
    </div>
</footer>

</body>
</html>