<?php
session_start();
include 'conexao.php';
?>

<!DOCTYPE html>
<html lang="pt">
<head>
    <meta charset="UTF-8">
    <title>Consultar Notas Finais</title>
    <style>
        * { box-sizing: border-box; }
        body {
            margin: 0;
            font-family: 'Segoe UI', sans-serif;
            background: #f0f0f0;
        }

        .sidebar {
            width: 220px;
            background: #0a1733;
            color: white;
            position: fixed;
            top: 0;
            bottom: 0;
            padding-top: 60px;
        }

        .sidebar a {
            display: block;
            padding: 15px 20px;
            color: white;
            text-decoration: none;
            border-bottom: 1px solid #1e2d4d;
        }

        .sidebar a:hover {
            background: #1b2a4f;
        }

        .topbar {
            height: 60px;
            background: #0044cc;
            color: white;
            padding: 15px 30px;
            position: fixed;
            left: 220px;
            right: 0;
            top: 0;
            display: flex;
            align-items: center;
            font-size: 18px;
            font-weight: bold;
        }

        .main {
            margin-left: 220px;
            margin-top: 60px;
            padding: 20px;
        }

        .content-box {
            background: white;
            padding: 20px;
            border-radius: 10px;
            box-shadow: 0 0 10px #ccc;
        }

        select, button {
            width: 100%;
            padding: 10px;
            margin: 10px 0 20px;
            border-radius: 5px;
            border: 1px solid #ccc;
            font-size: 15px;
        }

        button {
            background-color: #0044cc;
            color: white;
            font-weight: bold;
            cursor: pointer;
        }

        button:hover {
            background-color: #003399;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 10px;
        }

        th, td {
            padding: 10px;
            border: 1px solid #ccc;
            text-align: left;
        }

        h2, h3, h4 {
            margin-top: 0;
        }

        h4 {
            background: #e6f0ff;
            padding: 8px;
            border-radius: 5px;
            color: #003366;
        }
    </style>
</head>
<body>

<!-- Sidebar -->
<div class="sidebar">
    <a href="painel_subpedagogico.php">🏠 Início</a>
    <a href="cadastrar_aluno.php">👨‍🎓 Alunos</a>
    <a href="cadastrar_turma.php">🏫 Turmas</a>
    <a href="cadastrar_disciplina.php">📚 Disciplinas</a>
    <a href="cadastrar_nota.php">📝 Lançar Notas</a>
</div>

<!-- Topbar -->
<div class="topbar">
    Sistema SISMAG — Consulta de Notas Finais
</div>

<!-- Conteúdo principal -->
<div class="main">
    <div class="content-box">
        <h2>Consultar Notas Finais por Aluno</h2>

        <form method="GET">
            <label for="aluno_id">Selecionar Aluno:</label>
            <select name="aluno_id" required>
                <option value="">-- Escolha o aluno --</option>
                <?php
                $alunos = $conn->query("SELECT id, nome FROM alunos ORDER BY nome");
                while ($a = $alunos->fetch_assoc()) {
                    $selected = (isset($_GET['aluno_id']) && $_GET['aluno_id'] == $a['id']) ? 'selected' : '';
                    echo "<option value='{$a['id']}' $selected>{$a['nome']}</option>";
                }
                ?>
            </select>
            <button type="submit">Consultar</button>
        </form>

        <?php
        if (isset($_GET['aluno_id'])) {
            $aluno_id = $_GET['aluno_id'];

            $sql = "
                SELECT 
                    d.nome AS disciplina,
                    t.classe, t.nome AS turma_nome, t.curso, t.turno, t.ano AS ano_letivo,
                    n.mf AS nota_final
                FROM notas n
                INNER JOIN disciplinas d ON n.disciplina_id = d.id
                INNER JOIN turmas t ON n.turma_id = t.id
                WHERE n.aluno_id = ?
                ORDER BY t.ano DESC, d.nome ASC
            ";

            $stmt = $conn->prepare($sql);
            $stmt->bind_param("i", $aluno_id);
            $stmt->execute();
            $result = $stmt->get_result();

            echo "<h3>Notas Finais</h3>";
            if ($result->num_rows > 0) {
                $ultimo_ano = '';
                while ($row = $result->fetch_assoc()) {
                    $ano = $row['ano_letivo'];
                    if ($ano !== $ultimo_ano) {
                        if ($ultimo_ano !== '') echo "</table><br>";
                        echo "<h4>Ano Letivo: $ano</h4>";
                        echo "<table>";
                        echo "<tr><th>Turma</th><th>Disciplina</th><th>Nota Final (MF)</th></tr>";
                        $ultimo_ano = $ano;
                    }
                    $turma_formatada = "{$row['classe']} - {$row['turma_nome']} - {$row['curso']} - {$row['turno']}";
                    $nota_formatada = number_format($row['nota_final'], 2, ',', '');
                    echo "<tr>
                            <td>$turma_formatada</td>
                            <td>{$row['disciplina']}</td>
                            <td>{$nota_formatada}</td>
                          </tr>";
                }
                echo "</table>";
            } else {
                echo "<p>Nenhuma nota encontrada para este aluno.</p>";
            }
        }
        ?>
    </div>
</div>

</body>
</html>
