<?php
session_start();

// Database connection
$host = 'localhost';
$dbname = 'jv_overseas';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// Authentication check
if (!isset($_SESSION['user_id']) || $_SESSION['user_type'] !== 'counselor') {
    header('Location: counselor-login.php');
    exit;
}

// Get counselor data
$stmt = $pdo->prepare("SELECT c.*, u.email, u.last_login, u.is_active,
                      JSON_UNQUOTE(JSON_EXTRACT(c.specialization, '$')) as specialization_text
                      FROM counselors c 
                      JOIN users u ON c.id = u.id 
                      WHERE c.id = ?");
$stmt->execute([$_SESSION['user_id']]);
$counselor = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$counselor) {
    die("Counselor profile not found.");
}

// Get filters
$student_filter = $_GET['student'] ?? '';
$status_filter = $_GET['status'] ?? '';
$program_filter = $_GET['program'] ?? '';
$search = $_GET['search'] ?? '';

// Get students for filter dropdown
$students = $pdo->prepare("SELECT s.id, s.first_name, s.last_name, u.email 
                          FROM students s 
                          JOIN users u ON s.id = u.id 
                          WHERE s.assigned_counselor_id = ?
                          ORDER BY s.last_name, s.first_name");
$students->execute([$_SESSION['user_id']]);
$students = $students->fetchAll();

// Get programs for filter dropdown
$programs = $pdo->query("SELECT p.id, p.name, u.name as university_name 
                        FROM programs p 
                        JOIN universities u ON p.university_id = u.id 
                        ORDER BY u.name, p.name")->fetchAll();

// Build query
$sql = "SELECT a.*, 
               s.first_name, s.last_name, s.status as student_status,
               p.name as program_name, p.degree_level,
               u.name as university_name, u.logo as university_logo,
               u.country_id,
               co.name as country_name
        FROM applications a
        JOIN students s ON a.student_id = s.id
        JOIN programs p ON a.program_id = p.id
        JOIN universities u ON p.university_id = u.id
        JOIN countries co ON u.country_id = co.id";

// Add counselor filter
$conditions = ["a.counselor_id = ?"];
$params = [$_SESSION['user_id']];

// Add other filters
if ($student_filter) {
    $conditions[] = "a.student_id = ?";
    $params[] = $student_filter;
}

if ($status_filter) {
    $conditions[] = "a.application_status = ?";
    $params[] = $status_filter;
}

if ($program_filter) {
    $conditions[] = "a.program_id = ?";
    $params[] = $program_filter;
}

if ($search) {
    $conditions[] = "(s.first_name LIKE ? OR s.last_name LIKE ? OR p.name LIKE ? OR u.name LIKE ?)";
    $params[] = "%$search%";
    $params[] = "%$search%";
    $params[] = "%$search%";
    $params[] = "%$search%";
}

if ($conditions) {
    $sql .= " WHERE " . implode(" AND ", $conditions);
}

$sql .= " ORDER BY 
    CASE WHEN a.application_status = 'submitted' THEN 1
         WHEN a.application_status = 'under_review' THEN 2
         WHEN a.application_status = 'accepted' THEN 3
         WHEN a.application_status = 'rejected' THEN 4
         WHEN a.application_status = 'withdrawn' THEN 5
         ELSE 6
    END,
    a.submission_date DESC";

// Fix for the fatal error - separate prepare and execute
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$applications = $stmt->fetchAll();

// Get stats - Fixed by separating prepare and execute
$stats = [
    'total' => 0,
    'submitted' => 0,
    'under_review' => 0,
    'accepted' => 0,
    'rejected' => 0,
];

try {
    // Total applications
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM applications WHERE counselor_id = ?");
    $stmt->execute([$_SESSION['user_id']]);
    $stats['total'] = $stmt->fetchColumn();

    // Submitted applications
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM applications WHERE counselor_id = ? AND application_status = 'submitted'");
    $stmt->execute([$_SESSION['user_id']]);
    $stats['submitted'] = $stmt->fetchColumn();

    // Under review applications
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM applications WHERE counselor_id = ? AND application_status = 'under_review'");
    $stmt->execute([$_SESSION['user_id']]);
    $stats['under_review'] = $stmt->fetchColumn();

    // Accepted applications
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM applications WHERE counselor_id = ? AND application_status = 'accepted'");
    $stmt->execute([$_SESSION['user_id']]);
    $stats['accepted'] = $stmt->fetchColumn();

    // Rejected applications
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM applications WHERE counselor_id = ? AND application_status = 'rejected'");
    $stmt->execute([$_SESSION['user_id']]);
    $stats['rejected'] = $stmt->fetchColumn();
} catch (PDOException $e) {
    error_log("Applications stats query error: " . $e->getMessage());
}

// Handle application status update
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['update_status'])) {
    $application_id = $_POST['application_id'];
    $new_status = $_POST['new_status'];
    $notes = trim($_POST['notes'] ?? '');
    
    try {
        $pdo->beginTransaction();
        
        // Update application status
        $stmt = $pdo->prepare("UPDATE applications SET application_status = ?, updated_at = NOW() WHERE id = ?");
        $stmt->execute([$new_status, $application_id]);
        
        // Add application note
        if (!empty($notes)) {
            $stmt = $pdo->prepare("INSERT INTO application_notes (application_id, author_id, content, created_at) VALUES (?, ?, ?, NOW())");
            $stmt->execute([$application_id, $_SESSION['user_id'], $notes]);
        }
        
        // Create notification for student
        $app_stmt = $pdo->prepare("SELECT a.student_id, s.first_name, s.last_name, p.name as program_name, u.name as university_name 
                                  FROM applications a
                                  JOIN students s ON a.student_id = s.id
                                  JOIN programs p ON a.program_id = p.id
                                  JOIN universities u ON p.university_id = u.id
                                  WHERE a.id = ?");
        $app_stmt->execute([$application_id]);
        $app_data = $app_stmt->fetch();
        
        if ($app_data) {
            $pdo->prepare("INSERT INTO notifications (user_id, title, message, is_read, notification_type, created_at) 
                          VALUES (?, ?, ?, 0, 'application', NOW())")->execute([
                $app_data['student_id'],
                "Application Status Updated",
                "Your application for {$app_data['program_name']} at {$app_data['university_name']} has been updated to '{$new_status}'."
            ]);
        }
        
        // Log the activity
        $pdo->prepare("INSERT INTO activities (user_id, activity_type, description, created_at) 
                      VALUES (?, ?, ?, NOW())")->execute([
            $_SESSION['user_id'],
            "application_status_updated",
            "Counselor updated application #{$application_id} status to {$new_status}"
        ]);
        
        $pdo->commit();
        
        $_SESSION['success'] = "Application status updated successfully!";
        header("Location: counselor-applications.php");
        exit;
        
    } catch (Exception $e) {
        $pdo->rollback();
        $error = "Failed to update application status. Please try again.";
    }
}

// Logout
if (isset($_GET['logout'])) {
    session_destroy();
    header('Location: counselor-login.php');
    exit;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Applications - JV Overseas Education</title>
    <meta content="width=device-width, initial-scale=1.0" name="viewport">
    <link rel="preconnect" href="https://fonts.gstatic.com">
    <link href="https://fonts.googleapis.com/css2?family=Jost:wght@500;600;700&family=Open+Sans:wght@400;600&display=swap" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.10.0/css/all.min.css" rel="stylesheet">
    <link href="lib/owlcarousel/assets/owl.carousel.min.css" rel="stylesheet">
    <link href="css/style.css" rel="stylesheet">
    <style>
        .stat-card {
            border-left: 5px solid #3fb7fe;
            margin-bottom: 20px;
        }
        .stat-card .icon {
            font-size: 2rem;
            opacity: 0.2;
        }
        .table thead th {
            background-color: #f8f9fa;
            border-top: 0;
        }
        .status-badge {
            padding: 5px 12px;
            border-radius: 20px;
            font-weight: 600;
            font-size: 0.9em;
            display: inline-block;
        }
        .status-submitted {
            background: #fff3cd;
            color: #856404;
        }
        .status-under-review {
            background: #d1ecf1;
            color: #0c5460;
        }
        .status-accepted {
            background: #d4edda;
            color: #155724;
        }
        .status-rejected {
            background: #f8d7da;
            color: #721c24;
        }
        .status-withdrawn {
            background: #e9ecef;
            color: #495057;
        }
        .university-logo {
            width: 50px;
            height: 50px;
            object-fit: contain;
            background: white;
            padding: 5px;
            border-radius: 5px;
            box-shadow: 0 2px 5px rgba(0,0,0,0.1);
        }
        .student-info {
            font-size: 0.9em;
            color: #6c757d;
        }
        .action-buttons {
            display: flex;
            gap: 5px;
        }
        .modal-dialog {
            max-width: 500px;
        }
        .country-flag {
            width: 30px;
            height: 20px;
            object-fit: cover;
            border-radius: 5px;
            margin-right: 10px;
        }
    </style>
</head>
<body>

<?php include 'counselor-navbar.php'; ?>

<div class="container-fluid bg-primary text-white py-5">
    <div class="container">
        <h1>Applications</h1>
        <p>Manage your students' university applications</p>
    </div>
</div>

<div class="container py-5">
    <!-- Search and Filter -->
    <div class="bg-white p-4 rounded mb-4">
        <form method="GET">
            <div class="row">
                <div class="col-lg-3 mb-2">
                    <input type="text" name="search" class="form-control" placeholder="Search by student, program, or university" 
                           value="<?= htmlspecialchars($search) ?>">
                </div>
                <div class="col-lg-2 mb-2">
                    <select name="student" class="form-control">
                        <option value="">All Students</option>
                        <?php foreach ($students as $s): ?>
                        <option value="<?= $s['id'] ?>" <?= $student_filter == $s['id'] ? 'selected' : '' ?>>
                            <?= htmlspecialchars($s['first_name'] . ' ' . $s['last_name']) ?>
                        </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-lg-2 mb-2">
                    <select name="status" class="form-control">
                        <option value="">All Status</option>
                        <option value="submitted" <?= $status_filter === 'submitted' ? 'selected' : '' ?>>Submitted</option>
                        <option value="under_review" <?= $status_filter === 'under_review' ? 'selected' : '' ?>>Under Review</option>
                        <option value="accepted" <?= $status_filter === 'accepted' ? 'selected' : '' ?>>Accepted</option>
                        <option value="rejected" <?= $status_filter === 'rejected' ? 'selected' : '' ?>>Rejected</option>
                        <option value="withdrawn" <?= $status_filter === 'withdrawn' ? 'selected' : '' ?>>Withdrawn</option>
                    </select>
                </div>
                <div class="col-lg-3 mb-2">
                    <select name="program" class="form-control">
                        <option value="">All Programs</option>
                        <?php foreach ($programs as $p): ?>
                        <option value="<?= $p['id'] ?>" <?= $program_filter == $p['id'] ? 'selected' : '' ?>>
                            <?= htmlspecialchars($p['university_name'] . ' - ' . $p['name']) ?>
                        </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-lg-2 mb-2">
                    <button type="submit" class="btn btn-primary btn-block">Filter</button>
                </div>
            </div>
        </form>
    </div>

    <!-- Stats -->
    <div class="row mb-4">
        <div class="col-md-2 mb-3">
            <div class="stat-card">
                <div class="card-body">
                    <div class="float-right"><i class="fas fa-file-alt icon"></i></div>
                    <h6>Total</h6>
                    <h3><?= $stats['total'] ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-2 mb-3">
            <div class="stat-card" style="border-left-color: #ffc107;">
                <div class="card-body">
                    <div class="float-right"><i class="fas fa-clock icon" style="color: #ffc107;"></i></div>
                    <h6>Submitted</h6>
                    <h3><?= $stats['submitted'] ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-2 mb-3">
            <div class="stat-card" style="border-left-color: #17a2b8;">
                <div class="card-body">
                    <div class="float-right"><i class="fas fa-search icon" style="color: #17a2b8;"></i></div>
                    <h6>Under Review</h6>
                    <h3><?= $stats['under_review'] ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-2 mb-3">
            <div class="stat-card" style="border-left-color: #28a745;">
                <div class="card-body">
                    <div class="float-right"><i class="fas fa-check-circle icon" style="color: #28a745;"></i></div>
                    <h6>Accepted</h6>
                    <h3><?= $stats['accepted'] ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-2 mb-3">
            <div class="stat-card" style="border-left-color: #dc3545;">
                <div class="card-body">
                    <div class="float-right"><i class="fas fa-times-circle icon" style="color: #dc3545;"></i></div>
                    <h6>Rejected</h6>
                    <h3><?= $stats['rejected'] ?></h3>
                </div>
            </div>
        </div>
    </div>

    <!-- Applications Table -->
    <div class="table-responsive">
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>Student</th>
                    <th>University</th>
                    <th>Program</th>
                    <th>Status</th>
                    <th>Submitted</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php if (empty($applications)): ?>
                <tr>
                    <td colspan="6" class="text-center">No applications found.</td>
                </tr>
                <?php else: ?>
                <?php foreach ($applications as $app): ?>
                <tr>
                    <td>
                        <div class="d-flex align-items-center">
                            <img src="<?= htmlspecialchars($app['university_logo'] ?: 'img/university-placeholder.png') ?>" 
                                 alt="<?= htmlspecialchars($app['university_name']) ?>" class="university-logo">
                            <div class="ml-3">
                                <h6 class="mb-0"><?= htmlspecialchars($app['first_name'] . ' ' . $app['last_name']) ?></h6>
                                <p class="student-info mb-0"><?= htmlspecialchars($app['student_status']) ?></p>
                            </div>
                        </div>
                    </td>
                    <td>
                        <div class="d-flex align-items-center">
                            <img src="img/flags/<?= strtolower($app['country_name']) ?>.png" 
                                 alt="<?= htmlspecialchars($app['country_name']) ?>" class="country-flag">
                            <?= htmlspecialchars($app['university_name']) ?>
                        </div>
                    </td>
                    <td>
                        <strong><?= htmlspecialchars($app['program_name']) ?></strong><br>
                        <small class="text-muted"><?= htmlspecialchars($app['degree_level']) ?></small>
                    </td>
                    <td>
                        <span class="status-badge status-<?= strtolower($app['application_status']) ?>">
                            <?= ucfirst($app['application_status']) ?>
                        </span>
                    </td>
                    <td><?= $app['submission_date'] ? date('M j, Y', strtotime($app['submission_date'])) : 'Not submitted' ?></td>
                    <td>
                        <div class="action-buttons">
                            <a href="counselor-application-review.php?id=<?= $app['id'] ?>" class="btn btn-sm btn-primary">Review</a>
                            <button type="button" class="btn btn-sm btn-outline-primary" data-toggle="modal" data-target="#statusModal<?= $app['id'] ?>">Update Status</button>
                        </div>
                    </td>
                </tr>

                <!-- Status Update Modal -->
                <div class="modal fade" id="statusModal<?= $app['id'] ?>" tabindex="-1" role="dialog">
                    <div class="modal-dialog" role="document">
                        <div class="modal-content">
                            <form method="POST">
                                <div class="modal-header">
                                    <h5 class="modal-title">Update Application Status</h5>
                                    <button type="button" class="close" data-dismiss="modal">
                                        <span>&times;</span>
                                    </button>
                                </div>
                                <div class="modal-body">
                                    <input type="hidden" name="application_id" value="<?= $app['id'] ?>">
                                    
                                    <div class="form-group">
                                        <label>Student</label>
                                        <p class="form-control-plaintext"><?= htmlspecialchars($app['first_name'] . ' ' . $app['last_name']) ?></p>
                                    </div>
                                    
                                    <div class="form-group">
                                        <label>Program</label>
                                        <p class="form-control-plaintext"><?= htmlspecialchars($app['program_name']) ?> at <?= htmlspecialchars($app['university_name']) ?></p>
                                    </div>
                                    
                                    <div class="form-group">
                                        <label>Current Status</label>
                                        <p class="form-control-plaintext">
                                            <span class="status-badge status-<?= strtolower($app['application_status']) ?>">
                                                <?= ucfirst($app['application_status']) ?>
                                            </span>
                                        </p>
                                    </div>
                                    
                                    <div class="form-group">
                                        <label>New Status *</label>
                                        <select name="new_status" class="form-control" required>
                                            <option value="">Select Status</option>
                                            <option value="submitted">Submitted</option>
                                            <option value="under_review">Under Review</option>
                                            <option value="accepted">Accepted</option>
                                            <option value="rejected">Rejected</option>
                                            <option value="withdrawn">Withdrawn</option>
                                        </select>
                                    </div>
                                    
                                    <div class="form-group">
                                        <label>Notes (Optional)</label>
                                        <textarea name="notes" class="form-control" rows="3" placeholder="Add any notes for the student..."></textarea>
                                    </div>
                                    
                                    <div class="alert alert-info">
                                        <i class="fas fa-info-circle"></i> The student will be notified of this status change.
                                    </div>
                                </div>
                                <div class="modal-footer">
                                    <button type="button" class="btn btn-secondary" data-dismiss="modal">Cancel</button>
                                    <button type="submit" name="update_status" class="btn btn-primary">Update Status</button>
                                </div>
                            </form>
                        </div>
                    </div>
                </div>
                <?php endforeach; ?>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<?php include 'footer.php'; ?>
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.bundle.min.js"></script>
<script>
    // Success message handling
    $(document).ready(function() {
        <?php if (isset($_SESSION['success'])): ?>
        setTimeout(function() {
            $('.alert-success').fadeOut('slow');
        }, 3000);
        <?php unset($_SESSION['success']); endif; ?>
    });
</script>
</body>
</html>