<?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());
}

// Function to safely get image path with fallback
function getImagePath($path, $default = 'img/placeholder.jpg') {
    if ($path && file_exists($path)) {
        return $path;
    }
    return $default;
}

// Get all countries with university counts
$countries = $pdo->query("SELECT c.*, COUNT(u.id) as university_count
                         FROM countries c
                         LEFT JOIN universities u ON c.id = u.country_id
                         GROUP BY c.id
                         ORDER BY c.popularity_rank, c.name")->fetchAll();

// Get featured programs by country
$featured_programs = [];
foreach ($countries as $country) {
    $stmt = $pdo->prepare("SELECT p.*, u.name as university_name, u.logo as university_logo
                          FROM programs p
                          JOIN universities u ON p.university_id = u.id
                          WHERE u.country_id = ?
                          ORDER BY p.tuition_fee ASC
                          LIMIT 3");
    $stmt->execute([$country['id']]);
    $featured_programs[$country['id']] = $stmt->fetchAll();
}

// Get testimonials by country
$testimonials_by_country = [];
foreach ($countries as $country) {
    $stmt = $pdo->prepare("SELECT f.*, s.first_name, s.last_name, p.name as program_name, u.name as university_name
                      FROM feedback f
                      JOIN students s ON f.student_id = s.id
                      JOIN counselors c ON f.counselor_id = c.id
                      JOIN applications a ON s.id = a.student_id
                      JOIN programs p ON a.program_id = p.id
                      JOIN universities u ON p.university_id = u.id
                      WHERE u.country_id = ?
                      ORDER BY f.created_at DESC
                      LIMIT 2");
    $stmt->execute([$country['id']]);
    $testimonials_by_country[$country['id']] = $stmt->fetchAll();
}

// Get country-specific stats
$country_stats = [];
foreach ($countries as $country) {
    $stmt = $pdo->prepare("SELECT 
                            COUNT(s.id) as students_placed,
                            AVG(a.scholarship_awarded) as avg_scholarship
                          FROM students s
                          JOIN applications a ON s.id = a.student_id
                          JOIN programs p ON a.program_id = p.id
                          JOIN universities u ON p.university_id = u.id
                          WHERE u.country_id = ? AND a.application_status = 'accepted'");
    $stmt->execute([$country['id']]);
    $country_stats[$country['id']] = $stmt->fetch();
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Study Destinations - 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>
        .country-card {
            transition: all 0.3s;
            overflow: hidden;
            border-radius: 10px;
            box-shadow: 0 5px 15px rgba(0,0,0,0.1);
            margin-bottom: 30px;
        }
        .country-card:hover {
            transform: translateY(-10px);
            box-shadow: 0 15px 30px rgba(0,0,0,0.2);
        }
        .country-img {
            height: 250px;
            object-fit: cover;
        }
        .country-flag {
            width: 60px;
            height: 40px;
            object-fit: cover;
            border-radius: 5px;
            border: 2px solid white;
            box-shadow: 0 2px 5px rgba(0,0,0,0.2);
        }
        .stats-badge {
            font-size: 0.9em;
            padding: 5px 12px;
            border-radius: 20px;
            font-weight: 600;
        }
        .stats-primary {
            background: #d1ecf1;
            color: #0c5460;
        }
        .stats-success {
            background: #d4edda;
            color: #155724;
        }
        .stats-warning {
            background: #fff3cd;
            color: #856404;
        }
        .program-card {
            border: 1px solid #eee;
            border-radius: 10px;
            padding: 15px;
            margin-bottom: 15px;
            transition: all 0.3s;
        }
        .program-card:hover {
            box-shadow: 0 5px 15px rgba(0,0,0,0.1);
            transform: translateY(-5px);
        }
        .university-logo {
            height: 40px;
            object-fit: contain;
            background: white;
            padding: 5px;
            border-radius: 5px;
        }
        .testimonial-card {
            border: 1px solid #eee;
            border-radius: 10px;
            padding: 20px;
            margin-bottom: 20px;
            background: #f8f9fa;
        }
        .rating {
            color: #ffc107;
            margin-bottom: 10px;
        }
        .country-banner {
            height: 300px;
            object-fit: cover;
            width: 100%;
            border-radius: 8px;
            margin-bottom: 20px;
        }
    </style>
</head>
<body>

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

<!-- Header Start -->
<div class="jumbotron jumbotron-fluid page-header position-relative overlay-bottom" style="margin-bottom: 90px;">
    <div class="container text-center py-5">
        <h1 class="text-white display-1">Study Destinations</h1>
        <div class="d-inline-flex text-white mb-5">
            <p class="m-0 text-uppercase"><a class="text-white" href="index.php">Home</a></p>
            <i class="fa fa-angle-double-right pt-1 px-3"></i>
            <p class="m-0 text-uppercase">Destinations</p>
        </div>
    </div>
</div>
<!-- Header End -->

<!-- Destinations Start -->
<div class="container-fluid py-5">
    <div class="container py-5">
        <div class="text-center mb-5">
            <h5 class="text-primary text-uppercase mb-3" style="letter-spacing: 5px;">Study Abroad</h5>
            <h1>Popular Study Destinations</h1>
            <p class="m-0">Explore the world's top education destinations and find the perfect country for your international education journey.</p>
        </div>
        
        <div class="row">
            <?php foreach ($countries as $country): 
                $country_id = $country['id'];
                $country_name = $country['name'];
                $country_slug = strtolower(str_replace(' ', '-', $country_name));
                $country_code = strtolower(str_replace(' ', '', $country_name));
                
                // Get country image path
                $country_image = getImagePath(
                    !empty($country['image_path']) ? $country['image_path'] : "uploads/countries/$country_slug.jpg",
                    'img/destination-placeholder.jpg'
                );
                
                // Get flag image path
                $flag_image = getImagePath(
                    "img/flags/$country_code.png",
                    null
                );
            ?>
            <div class="col-lg-4 col-md-6 mb-5">
                <div class="country-card card">
                    <div class="position-relative">
                        <img class="img-fluid country-img w-100" 
                             src="<?= htmlspecialchars($country_image) ?>" 
                             alt="Study in <?= htmlspecialchars($country_name) ?>">
                             
                        <?php if ($flag_image): ?>
                        <img src="<?= htmlspecialchars($flag_image) ?>" 
                             alt="<?= htmlspecialchars($country_name) ?>" class="country-flag position-absolute" 
                             style="top: 20px; left: 20px;">
                        <?php endif; ?>
                    </div>
                    <div class="card-body p-4">
                        <h4 class="card-title mb-3"><?= htmlspecialchars($country_name) ?></h4>
                        
                        <!-- Country Stats -->
                        <div class="d-flex justify-content-between mb-3">
                            <span class="stats-badge stats-primary">
                                <i class="fas fa-university"></i> <?= $country['university_count'] ?> Universities
                            </span>
                            <span class="stats-badge stats-success">
                                <i class="fas fa-graduation-cap"></i> <?= $country_stats[$country_id]['students_placed'] ?> Students
                            </span>
                        </div>
                        
                        <p class="card-text"><?= substr(htmlspecialchars($country['visa_requirements'] ?? ''), 0, 150) ?>...</p>
                        
                        <div class="d-flex justify-content-between align-items-center">
                            <div>
                                <strong>Living Cost:</strong> $<?= number_format($country['living_cost_estimate'], 0) ?>/year
                            </div>
                            <a href="#<?= $country_slug ?>" class="btn btn-primary">Explore</a>
                        </div>
                    </div>
                </div>
            </div>
            <?php endforeach; ?>
        </div>
    </div>
</div>
<!-- Destinations End -->

<!-- Country Details Start -->
<?php foreach ($countries as $country): 
    $country_id = $country['id'];
    $country_name = $country['name'];
    $anchor_name = strtolower(str_replace(' ', '-', $country_name));
    $country_slug = strtolower(str_replace(' ', '-', $country_name));
    
    // Get country image path for banner
    $country_banner = getImagePath(
        !empty($country['image_path']) ? $country['image_path'] : "uploads/countries/$country_slug.jpg",
        'img/destination-placeholder.jpg'
    );
?>
<div id="<?= $anchor_name ?>" class="container-fluid py-5 bg-light">
    <div class="container py-5">
        <div class="row">
            <div class="col-lg-8">
                <h2>Study in <?= htmlspecialchars($country_name) ?></h2>
                
                <!-- Country Banner Image -->
                <img src="<?= htmlspecialchars($country_banner) ?>" 
                     alt="<?= htmlspecialchars($country_name) ?>" 
                     class="country-banner">
                
                <!-- Visa Requirements -->
                <div class="mb-4">
                    <h5>Visa Requirements</h5>
                    <p><?= htmlspecialchars($country['visa_requirements']) ?></p>
                </div>
                
                <!-- Featured Programs -->
                <div class="mb-4">
                    <h5>Featured Programs</h5>
                    <?php if (empty($featured_programs[$country_id])): ?>
                    <p>No programs available in <?= htmlspecialchars($country_name) ?> yet.</p>
                    <?php else: ?>
                    <?php foreach ($featured_programs[$country_id] as $program): ?>
                    <div class="program-card">
                        <div class="d-flex justify-content-between align-items-start">
                            <div>
                                <h6><?= htmlspecialchars($program['name']) ?></h6>
                                <small class="text-muted">
                                    <img src="<?= htmlspecialchars(!empty($program['university_logo']) ? $program['university_logo'] : 'img/university-placeholder.png') ?>" 
                                         alt="<?= htmlspecialchars($program['university_name']) ?>" class="university-logo">
                                    <?= htmlspecialchars($program['university_name']) ?>
                                </small>
                            </div>
                            <div class="text-right">
                                <div class="fee-info">
                                    <strong>$<?= number_format($program['tuition_fee'], 2) ?></strong><br>
                                    per year
                                </div>
                            </div>
                        </div>
                        <div class="mt-2">
                            <span class="badge badge-primary"><?= ucfirst($program['degree_level']) ?></span>
                            <span class="ml-2"><?= $program['duration_months'] ?> months</span>
                        </div>
                    </div>
                    <?php endforeach; ?>
                    <div class="text-center">
                        <a href="universities.php?country=<?= $country_id ?>" class="btn btn-primary">View All Programs</a>
                    </div>
                    <?php endif; ?>
                </div>
            </div>
            
            <div class="col-lg-4">
                <!-- Country Facts -->
                <div class="card mb-4">
                    <div class="card-header">
                        <h5>Quick Facts</h5>
                    </div>
                    <div class="card-body">
                        <ul class="list-unstyled">
                            <li class="mb-3"><i class="fas fa-dollar-sign mr-2"></i> 
                                <strong>Tuition Range:</strong><br>
                                $<?= number_format($pdo->query("SELECT MIN(tuition_fee) FROM programs p JOIN universities u ON p.university_id = u.id WHERE u.country_id = {$country_id}")->fetchColumn() ?: 0) ?> 
                                - $<?= number_format($pdo->query("SELECT MAX(tuition_fee) FROM programs p JOIN universities u ON p.university_id = u.id WHERE u.country_id = {$country_id}")->fetchColumn() ?: 0) ?>
                            </li>
                            <li class="mb-3"><i class="fas fa-home mr-2"></i> 
                                <strong>Living Cost:</strong><br>
                                $<?= number_format($country['living_cost_estimate'], 0) ?>/year
                            </li>
                            <li class="mb-3"><i class="fas fa-university mr-2"></i> 
                                <strong>Universities:</strong><br>
                                <?= $country['university_count'] ?> Partner Institutions
                            </li>
                            <li class="mb-3"><i class="fas fa-graduation-cap mr-2"></i> 
                                <strong>Students Placed:</strong><br>
                                <?= $country_stats[$country_id]['students_placed'] ?>
                            </li>
                            <li class="mb-3"><i class="fas fa-award mr-2"></i> 
                                <strong>Avg Scholarship:</strong><br>
                                $<?= number_format($country_stats[$country_id]['avg_scholarship'] ?: 0, 0) ?>
                            </li>
                        </ul>
                    </div>
                </div>
                
                <!-- Student Testimonials -->
                <?php if (!empty($testimonials_by_country[$country_id])): ?>
                <div class="card">
                    <div class="card-header">
                        <h5>Student Success</h5>
                    </div>
                    <div class="card-body p-0">
                        <?php foreach ($testimonials_by_country[$country_id] as $testimonial): ?>
                        <div class="testimonial-card">
                            <div class="rating">
                                <?php for ($i = 0; $i < $testimonial['rating']; $i++): ?>
                                    <i class="fas fa-star"></i>
                                <?php endfor; ?>
                            </div>
                            <p class="mb-3">"<?= htmlspecialchars($testimonial['content']) ?>"</p>
                            <div class="d-flex align-items-center">
                                <img src="img/profile-placeholder.jpg" alt="<?= htmlspecialchars($testimonial['first_name']) ?>" class="rounded-circle" style="width: 40px; height: 40px;">
                                <div class="ml-2">
                                    <h6 class="mb-0"><?= htmlspecialchars($testimonial['first_name'] . ' ' . $testimonial['last_name']) ?></h6>
                                    <small class="text-muted"><?= htmlspecialchars($testimonial['program_name']) ?></small>
                                </div>
                            </div>
                        </div>
                        <?php endforeach; ?>
                    </div>
                </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>
<!-- Country Details End -->
<?php endforeach; ?>

<!-- Call to Action Start -->
<div class="container-fluid bg-primary my-5 py-5">
    <div class="container py-5">
        <div class="row align-items-center">
            <div class="col-lg-7 mb-4 mb-lg-0">
                <h1 class="display-4 text-white">Ready to Study Abroad?</h1>
                <p class="text-white mb-4">Get personalized guidance on choosing the right destination, university, and program for your academic and career goals.</p>
            </div>
            <div class="col-lg-5">
                <div class="d-flex align-items-center justify-content-lg-end">
                    <a class="btn btn-light mr-3" href="apply.php">Get Free Consultation</a>
                    <a class="btn btn-dark" href="contact.php">Contact Us</a>
                </div>
            </div>
        </div>
    </div>
</div>
<!-- Call to Action End -->

<?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 src="lib/easing/easing.min.js"></script>
<script src="lib/owlcarousel/owl.carousel.min.js"></script>
<script src="lib/waypoints/waypoints.min.js"></script>
<script src="lib/counterup/counterup.min.js"></script>
<script src="mail/jqBootstrapValidation.min.js"></script>
<script src="mail/contact.js"></script>
<script src="js/main.js"></script>
</body>
</html>