<?php
// Include database connection
include('db_connection.php');

// Initialize variables
$student_id = null;
$subject = "";
$grade = "";
$term = "";
$year = "";
$action = "";
$result = null;
$message = "";

// Fetch all students for the dropdown
$students_query = "SELECT student_id, name FROM students ORDER BY name";
$students_result = $conn->query($students_query);

// Fetch subjects for dropdown
$subject_list = [];
$result_subjects = $conn->query("SELECT subject_id, subject_name FROM subject ORDER BY subject_name ASC");

if ($result_subjects && $result_subjects->num_rows > 0) {
    $subject_list = $result_subjects->fetch_all(MYSQLI_ASSOC);
}

// Handle actions: add, edit, delete
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    if (isset($_POST['action'])) {
        $action = $_POST['action'];

        // Add academic history record
        if ($action === "add") {
            $student_id = $_POST['student_id'];
            $subject = $_POST['subject'];
            $grade = $_POST['grade'];
            $term = $_POST['term'];
            $year = $_POST['year'];

            // Check if student_id exists
            $check_student_query = "SELECT student_id FROM students WHERE student_id = ?";
            $stmt_check = $conn->prepare($check_student_query);
            $stmt_check->bind_param("i", $student_id);
            $stmt_check->execute();
            $stmt_check->store_result();

            if ($stmt_check->num_rows > 0) {
                $insert_query = "INSERT INTO academic_history (student_id, subject, grade, term, year) 
                                 VALUES (?, ?, ?, ?, ?)";
                $stmt = $conn->prepare($insert_query);
                $stmt->bind_param("issss", $student_id, $subject, $grade, $term, $year);
                if ($stmt->execute()) {
                    $message = "<div class='alert alert-success'>Record added successfully.</div>";
                } else {
                    $message = "<div class='alert alert-danger'>Error adding record: " . $conn->error . "</div>";
                }
                $stmt->close();
            } else {
                $message = "<div class='alert alert-danger'>Error: Student ID does not exist.</div>";
            }
            $stmt_check->close();
        }

        // Edit academic history record
        elseif ($action === "edit") {
            $history_id = $_POST['history_id'];
            $subject = $_POST['subject'];
            $grade = $_POST['grade'];
            $term = $_POST['term'];
            $year = $_POST['year'];

            $update_query = "UPDATE academic_history 
                             SET subject = ?, grade = ?, term = ?, year = ? 
                             WHERE history_id = ?";
            $stmt = $conn->prepare($update_query);
            $stmt->bind_param("ssssi", $subject, $grade, $term, $year, $history_id);
            if ($stmt->execute()) {
                $message = "<div class='alert alert-success'>Record updated successfully.</div>";
            } else {
                $message = "<div class='alert alert-danger'>Error updating record: " . $conn->error . "</div>";
            }
            $stmt->close();
        }

        // Delete academic history record
        elseif ($action === "delete") {
            $history_id = $_POST['history_id'];

            $delete_query = "DELETE FROM academic_history WHERE history_id = ?";
            $stmt = $conn->prepare($delete_query);
            $stmt->bind_param("i", $history_id);
            if ($stmt->execute()) {
                $message = "<div class='alert alert-success'>Record deleted successfully.</div>";
            } else {
                $message = "<div class='alert alert-danger'>Error deleting record: " . $conn->error . "</div>";
            }
            $stmt->close();
        }
    }
}

// Fetch academic history for a specific student if student_id is provided
// 🔥 REPLACE YOUR FETCH QUERY WITH THIS
if (isset($_GET['student_id'])) {
    $student_id = $_GET['student_id'];

    $fetch_query = "
        SELECT 
            history_id,
            student_id,
            subject,
            grade,
            term,
            year,
            created_at,
            updated_at
        FROM academic_history
        WHERE student_id = ?
        ORDER BY year DESC, term DESC
    ";

    $stmt = $conn->prepare($fetch_query);
    $stmt->bind_param("i", $student_id);

    if ($stmt->execute()) {
        $result = $stmt->get_result();
    } else {
        $message = "<div class='alert alert-danger'>Error fetching records: " . $conn->error . "</div>";
    }

    $stmt->close();
}

// HTML output starts here
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Academic History Management</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <style>
        :root {
            --primary: #3a57e8;
            --secondary: #6c757d;
            --success: #1cbb8c;
            --info: #17a2b8;
            --warning: #f5b759;
            --danger: #fc5b69;
            --light: #f8f9fa;
            --dark: #343a40;
        }
        
        body {
            font-family: 'Poppins', sans-serif;
            background-color: #f5f7ff;
            color: #333;
        }
        
        .card {
            border-radius: 10px;
            box-shadow: 0 5px 15px rgba(0, 0, 0, 0.08);
            margin-bottom: 20px;
            border: none;
        }
        
        .card-header {
            background: linear-gradient(135deg, var(--primary) 0%, #2039c5 100%);
            color: white;
            border-radius: 10px 10px 0 0 !important;
            font-weight: 600;
        }
        
        .btn-primary {
            background: var(--primary);
            border: none;
        }
        
        .btn-primary:hover {
            background: #2039c5;
        }
        
        .table th {
            background-color: var(--primary);
            color: white;
        }
        
        .action-buttons .btn {
            padding: 0.25rem 0.5rem;
            font-size: 0.875rem;
        }
        
        .student-selector {
            max-width: 400px;
        }
        
        .highlight-row {
            background-color: rgba(58, 87, 232, 0.1);
        }
        
        .stats-card {
            text-align: center;
            padding: 20px;
            border-radius: 10px;
            background: white;
            box-shadow: 0 5px 15px rgba(0, 0, 0, 0.08);
        }
        
        .stats-number {
            font-size: 24px;
            font-weight: 700;
            color: var(--primary);
        }
    </style>
</head>
<body>
    <div class="container py-4">
        <div class="row mb-4">
            <div class="col-md-8">
                <h2 class="mb-0"><i class="fas fa-graduation-cap me-2"></i>Academic History Management</h2>
                <p class="text-muted">Manage student academic records and performance</p>
                <?php if (isset($student_id) && $student_id): ?>
    <div class="mt-2">
        <a href="student_transcript.php?student_id=<?= urlencode($student_id) ?>" 
           class="btn btn-sm btn-success">
            <i class="fas fa-file-alt me-1"></i> View Student Transcript
        </a>
    </div>
<?php endif; ?>
            </div>
            <div class="col-md-4 text-md-end">
                <a href="admin_dashboard.php" class="btn btn-outline-primary"><i class="fas fa-arrow-left me-1"></i> Back to Dashboard</a>
            </div>
        </div>

        <?php echo $message; ?>

        <div class="row">
            <div class="col-md-4">
                <div class="card">
                    <div class="card-header">
                        <h5 class="mb-0"><i class="fas fa-plus-circle me-2"></i>Add Academic Record</h5>
                    </div>
                    <div class="card-body">
                        <form action="academic_history.php" method="post">
                            <input type="hidden" name="action" value="add">
                            
                            <div class="mb-3">
                                <label for="student_id" class="form-label">Student</label>
                                <select class="form-select" id="student_id" name="student_id" required>
                                    <option value="">Select a student</option>
                                    <?php 
                                    if ($students_result->num_rows > 0) {
                                        while ($student_row = $students_result->fetch_assoc()): 
                                            $selected = ($student_id == $student_row['student_id']) ? 'selected' : '';
                                    ?>
                                        <option value="<?php echo htmlspecialchars($student_row['student_id']); ?>" <?php echo $selected; ?>>
                                            <?php echo htmlspecialchars($student_row['name']); ?>
                                        </option>
                                    <?php 
                                        endwhile;
                                    } else {
                                        echo '<option value="">No students available</option>';
                                    }
                                    ?>
                                </select>
                            </div>
                            
                           <div class="mb-3">
    <label for="subject" class="form-label">Subject</label>
    <select class="form-select" name="subject" required>
        <option value="">Select Subject</option>

        <?php foreach ($subject_list as $sub): ?>
            <option value="<?php echo htmlspecialchars($sub['subject_name']); ?>">
                <?php echo htmlspecialchars($sub['subject_name']); ?>
            </option>
        <?php endforeach; ?>

    </select>
</div>
                            
                            <div class="row">
                                <div class="col-md-6 mb-3">
                                    <label for="grade" class="form-label">Grade</label>
                                    <input type="text" class="form-control" id="grade" name="grade" required>
                                </div>
                                <div class="col-md-6 mb-3">
                                    <label for="term" class="form-label">Term</label>
                                    <select class="form-select" id="term" name="term" required>
                                        <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>
                                </div>
                            </div>
                            
                            <div class="mb-3">
                                <label for="year" class="form-label">Year</label>
                                <input type="text" class="form-control" id="year" name="year" required>
                            </div>
                            
                            <button type="submit" class="btn btn-primary w-100"><i class="fas fa-save me-1"></i> Add Academic Record</button>
                        </form>
                    </div>
                </div>
                
                <!-- Quick Stats -->
                <?php
                if ($result && $result->num_rows > 0) {
                    $total_records = $result->num_rows;
                    $result->data_seek(0); // Reset pointer
                    
                    // Count distinct subjects
                    $subjects = [];
                    $grades = [];
                    while ($row = $result->fetch_assoc()) {
                        $subjects[$row['subject']] = true;
                        $grades[] = $row['grade'];
                    }
                    $unique_subjects = count($subjects);
                    
                    // Calculate average grade (if grades are numeric)
                    $numeric_grades = array_filter($grades, function($grade) {
                        return is_numeric($grade);
                    });
                    
                    $average_grade = count($numeric_grades) > 0 ? 
                        round(array_sum($numeric_grades) / count($numeric_grades), 2) : 'N/A';
                    
                    // Reset pointer again for the table
                    $result->data_seek(0);
                ?>
                <div class="card mt-4">
                    <div class="card-header">
                        <h5 class="mb-0"><i class="fas fa-chart-bar me-2"></i>Academic Summary</h5>
                    </div>
                    <div class="card-body">
                        <div class="row text-center">
                            <div class="col-6 mb-3">
                                <div class="stats-card">
                                    <div class="stats-number"><?php echo $total_records; ?></div>
                                    <div class="stats-label">Records</div>
                                </div>
                            </div>
                            <div class="col-6 mb-3">
                                <div class="stats-card">
                                    <div class="stats-number"><?php echo $unique_subjects; ?></div>
                                    <div class="stats-label">Subjects</div>
                                </div>
                            </div>
                            <div class="col-12">
                                <div class="stats-card">
                                    <div class="stats-number"><?php echo $average_grade; ?></div>
                                    <div class="stats-label">Average Grade</div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
                <?php } ?>
            </div>
            
            <div class="col-md-8">
                <div class="card">
                    <div class="card-header d-flex justify-content-between align-items-center">
                        <h5 class="mb-0"><i class="fas fa-history me-2"></i>Academic History</h5>
                        <?php if (isset($student_id)): ?>
                        <a href="academic_history.php" class="btn btn-sm btn-outline-light">Clear Selection</a>
                        <?php endif; ?>
                    </div>
                    <div class="card-body">
                        <?php if ($result && $result->num_rows > 0): ?>
                            <div class="table-responsive">
                                <table class="table table-hover">
                                    <thead>
                                        <tr>
                                            <th>Subject</th>
                                            <th>Grade</th>
                                            <th>Term</th>
                                            <th>Year</th>
                                            <th>Actions</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php while ($row = $result->fetch_assoc()): ?>
                                        <tr>
                                            <td><?php echo htmlspecialchars($row['subject']); ?></td>
                                            <td>
                                                <span class="badge bg-<?php 
                                                    if (is_numeric($row['grade'])) {
                                                        if ($row['grade'] >= 90) echo 'success';
                                                        elseif ($row['grade'] >= 80) echo 'primary';
                                                        elseif ($row['grade'] >= 70) echo 'info';
                                                        elseif ($row['grade'] >= 60) echo 'warning';
                                                        else echo 'danger';
                                                    } else {
                                                        echo 'secondary';
                                                    }
                                                ?>">
                                                    <?php echo htmlspecialchars($row['grade']); ?>
                                                </span>
                                            </td>
                                            <td><?php echo htmlspecialchars($row['term']); ?></td>
                                            <td><?php echo htmlspecialchars($row['year']); ?></td>
                                            <td class="action-buttons">
                                                <!-- Edit Form -->
                                                <button type="button" class="btn btn-sm btn-outline-primary" data-bs-toggle="modal" data-bs-target="#editModal" 
                                                    data-history_id="<?php echo $row['history_id']; ?>"
                                                    data-subject="<?php echo htmlspecialchars($row['subject']); ?>"
                                                    data-grade="<?php echo htmlspecialchars($row['grade']); ?>"
                                                    data-term="<?php echo htmlspecialchars($row['term']); ?>"
                                                    data-year="<?php echo htmlspecialchars($row['year']); ?>">
                                                    <i class="fas fa-edit"></i>
                                                </button>
                                                
                                                <!-- Delete Form -->
                                                <button type="button" class="btn btn-sm btn-outline-danger" data-bs-toggle="modal" data-bs-target="#deleteModal" 
                                                    data-history_id="<?php echo $row['history_id']; ?>"
                                                    data-subject="<?php echo htmlspecialchars($row['subject']); ?>">
                                                    <i class="fas fa-trash"></i>
                                                </button>
                                            </td>
                                        </tr>
                                        <?php endwhile; ?>
                                    </tbody>
                                </table>
                            </div>
                        <?php else: ?>
                            <div class="text-center py-5">
                                <i class="fas fa-book-open fa-3x text-muted mb-3"></i>
                                <p class="text-muted">No academic history found for this student.</p>
                                <p>Select a student and add academic records to get started.</p>
                            </div>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Edit Modal -->
    <div class="modal fade" id="editModal" tabindex="-1" aria-labelledby="editModalLabel" aria-hidden="true">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="editModalLabel">Edit Academic Record</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <form action="academic_history.php" method="post">
                    <div class="modal-body">
                        <input type="hidden" name="action" value="edit">
                        <input type="hidden" name="history_id" id="edit_history_id">
                        
                       <div class="mb-3">
    <label for="edit_subject" class="form-label">Subject</label>
    <select class="form-select" id="edit_subject" name="subject" required>
        <option value="">Select Subject</option>

        <?php foreach ($subjects as $subject): ?>
            <option value="<?php echo htmlspecialchars($sub['subject_name']); ?>">
                <?php echo htmlspecialchars($subject['subject_name']); ?>
            </option>
        <?php endforeach; ?>

    </select>
</div>
                        
                        <div class="row">
                            <div class="col-md-6 mb-3">
                                <label for="edit_grade" class="form-label">Grade</label>
                                <input type="text" class="form-control" id="edit_grade" name="grade" required>
                            </div>
                            <div class="col-md-6 mb-3">
                                <label for="edit_term" class="form-label">Term</label>
                                <select class="form-select" id="edit_term" name="term" required>
                                    <option value="1st Term">1st Term</option>
                                    <option value="2nd Term">2nd Term</option>
                                    <option value="3rd Term">3rd Term</option>
                                </select>
                            </div>
                        </div>
                        
                        <div class="mb-3">
                            <label for="edit_year" class="form-label">Year</label>
                            <input type="text" class="form-control" id="edit_year" name="year" required>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-primary">Save Changes</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Delete Modal -->
    <div class="modal fade" id="deleteModal" tabindex="-1" aria-labelledby="deleteModalLabel" aria-hidden="true">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="deleteModalLabel">Confirm Deletion</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <form action="academic_history.php" method="post">
                    <div class="modal-body">
                        <input type="hidden" name="action" value="delete">
                        <input type="hidden" name="history_id" id="delete_history_id">
                        
                        <p>Are you sure you want to delete the academic record for <strong id="delete_subject"></strong>?</p>
                        <p class="text-danger">This action cannot be undone.</p>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-danger">Delete Record</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        // Edit Modal Handler
        var editModal = document.getElementById('editModal');
        editModal.addEventListener('show.bs.modal', function (event) {
            var button = event.relatedTarget;
            var historyId = button.getAttribute('data-history_id');
            var subject = button.getAttribute('data-subject');
            var grade = button.getAttribute('data-grade');
            var term = button.getAttribute('data-term');
            var year = button.getAttribute('data-year');
            
            var modal = this;
            modal.querySelector('#edit_history_id').value = historyId;
            modal.querySelector('#edit_subject').value = subject;
            modal.querySelector('#edit_grade').value = grade;
            modal.querySelector('#edit_term').value = term;
            modal.querySelector('#edit_year').value = year;
        });
        
        // Delete Modal Handler
        var deleteModal = document.getElementById('deleteModal');
        deleteModal.addEventListener('show.bs.modal', function (event) {
            var button = event.relatedTarget;
            var historyId = button.getAttribute('data-history_id');
            var subject = button.getAttribute('data-subject');
            
            var modal = this;
            modal.querySelector('#delete_history_id').value = historyId;
            modal.querySelector('#delete_subject').textContent = subject;
        });
        
        // Auto-submit student selection
        document.getElementById('student_id').addEventListener('change', function() {
            if (this.value) {
                window.location.href = 'academic_history.php?student_id=' + this.value;
            }
        });
    </script>
</body>
</html>

<?php
// Close database connection
$conn->close();
?>