<?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'] !== 'admin') {
    header('Location: admin-login.php');
    exit;
}

// Get admin data
$stmt = $pdo->prepare("SELECT a.*, u.email FROM admins a JOIN users u ON a.id = u.id WHERE a.id = ?");
$stmt->execute([$_SESSION['user_id']]);
$admin = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$admin) {
    die("Admin profile not found.");
}

// Get date range filters
$date_from = $_GET['from'] ?? date('Y-m-01'); // First day of current month
$date_to = $_GET['to'] ?? date('Y-m-d'); // Today

// Get country filter
$country_filter = $_GET['country'] ?? '';

// Initialize variables
$visa_data = [];
$overall_stats = [
    'total_applications' => 0,
    'approved_count' => 0,
    'rejected_count' => 0,
    'overall_success_rate' => 0.00
];
$top_counselors = [];
$countries = [];
$error = '';
$success = '';

// Check if required tables exist
$tables_needed = ['visa_applications', 'countries', 'counselors', 'students'];
$missing_tables = [];

foreach ($tables_needed as $table) {
    try {
        $stmt = $pdo->query("SHOW TABLES LIKE '$table'");
        if ($stmt->rowCount() == 0) {
            $missing_tables[] = $table;
        }
    } catch (PDOException $e) {
        $missing_tables[] = $table;
    }
}

if (!empty($missing_tables)) {
    $error = "The following required tables are missing: " . implode(', ', $missing_tables) . ". Please create them first.";
} else {
    try {
        // Get countries for filter
        $country_stmt = $pdo->query("SELECT id, name FROM countries ORDER BY name");
        $countries = $country_stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Build query for visa success rates
        $sql = "SELECT 
            c.name as country_name,
            COUNT(v.id) as total_applications,
            SUM(CASE WHEN v.status = 'approved' THEN 1 ELSE 0 END) as approved_count,
            SUM(CASE WHEN v.status = 'rejected' THEN 1 ELSE 0 END) as rejected_count,
            ROUND(
                (SUM(CASE WHEN v.status = 'approved' THEN 1 ELSE 0 END) * 100.0 / 
                NULLIF(COUNT(v.id), 0)), 2
            ) as success_rate
        FROM visa_applications v
        JOIN countries c ON v.country_id = c.id
        WHERE v.application_date BETWEEN ? AND ?";

        $params = [$date_from, $date_to];

        if ($country_filter) {
            $sql .= " AND v.country_id = ?";
            $params[] = $country_filter;
        }

        $sql .= " GROUP BY c.id, c.name ORDER BY success_rate DESC, c.name";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $visa_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Get overall statistics
        $overall_stats_stmt = $pdo->prepare("
            SELECT 
                COUNT(*) as total_applications,
                SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved_count,
                SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected_count,
                ROUND(
                    (SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) * 100.0 / 
                    NULLIF(COUNT(*), 0)), 2
                ) as overall_success_rate
            FROM visa_applications 
            WHERE application_date BETWEEN ? AND ?
        ");
        
        $overall_stats_stmt->execute([$date_from, $date_to]);
        $fetched_stats = $overall_stats_stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($fetched_stats) {
            $overall_stats = $fetched_stats;
        }
        
        // Get top counselors by success rate
        $counselor_sql = "SELECT 
            CONCAT(co.first_name, ' ', co.last_name) as counselor_name,
            COUNT(v.id) as total_applications,
            SUM(CASE WHEN v.status = 'approved' THEN 1 ELSE 0 END) as approved_count,
            ROUND(
                (SUM(CASE WHEN v.status = 'approved' THEN 1 ELSE 0 END) * 100.0 / 
                NULLIF(COUNT(v.id), 0)), 2
            ) as success_rate
        FROM visa_applications v
        JOIN counselors co ON v.counselor_id = co.id
        WHERE v.application_date BETWEEN ? AND ?";

        $counselor_params = [$date_from, $date_to];

        if ($country_filter) {
            $counselor_sql .= " AND v.country_id = ?";
            $counselor_params[] = $country_filter;
        }

        $counselor_sql .= " GROUP BY co.id ORDER BY success_rate DESC LIMIT 5";

        $counselor_stmt = $pdo->prepare($counselor_sql);
        $counselor_stmt->execute($counselor_params);
        $top_counselors = $counselor_stmt->fetchAll(PDO::FETCH_ASSOC);
        
    } catch (PDOException $e) {
        $error = "Database error: " . $e->getMessage();
    }
}

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

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Visa Success Reports - 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;
        }
        .success-rate {
            font-size: 2em;
            font-weight: 700;
            color: #28a745;
        }
        .rate-high {
            color: #28a745;
        }
        .rate-medium {
            color: #ffc107;
        }
        .rate-low {
            color: #dc3545;
        }
        .country-flag {
            width: 30px;
            height: 20px;
            object-fit: cover;
            border-radius: 5px;
            margin-right: 10px;
        }
        .progress-container {
            height: 20px;
            background: #e9ecef;
            border-radius: 10px;
            overflow: hidden;
            margin: 5px 0;
        }
        .progress-bar {
            height: 100%;
            background: #3fb7fe;
            border-radius: 10px;
        }
        .approved {
            background: #d4edda;
            color: #155724;
        }
        .rejected {
            background: #f8d7da;
            color: #721c24;
        }
        .setup-instructions {
            background: #fff3cd;
            border: 1px solid #ffeaa7;
            border-radius: 5px;
            padding: 15px;
            margin: 15px 0;
        }
        .setup-instructions pre {
            background: #f8f9fa;
            padding: 10px;
            border-radius: 5px;
            overflow-x: auto;
            margin: 10px 0;
        }
    </style>
</head>
<body>
    <?php include 'admin-navbar.php'; ?>
    
    <div class="container py-5">
        <div class="section-title position-relative mb-4">
            <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Reporting</h6>
            <h1 class="display-5">Visa Success Rate Report</h1>
        </div>
        
        <?php if ($error): ?>
        <div class="alert alert-danger"><?= htmlspecialchars($error) ?></div>
        <?php endif; ?>
        
        <?php if ($success): ?>
        <div class="alert alert-success"><?= htmlspecialchars($success) ?></div>
        <?php endif; ?>
        
        <?php if (!empty($missing_tables)): ?>
        <div class="setup-instructions">
            <h5><i class="fas fa-exclamation-triangle mr-2"></i> Required Database Tables Missing</h5>
            <p>The following tables are required for the visa success report but are missing from your database:</p>
            <ul>
                <?php foreach ($missing_tables as $table): ?>
                <li><strong><?= htmlspecialchars($table) ?></strong></li>
                <?php endforeach; ?>
            </ul>
            <p>Please create these tables using the SQL below:</p>
            <pre>
-- Create countries table
CREATE TABLE IF NOT EXISTS countries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(3) NOT NULL,
    flag_path VARCHAR(255) DEFAULT NULL,
    visa_requirements TEXT DEFAULT NULL,
    living_cost_estimate DECIMAL(10,2) DEFAULT NULL,
    popularity_rank INT DEFAULT NULL,
    image_path VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT current_timestamp(),
    updated_at TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
);

-- Create visa_applications table
CREATE TABLE IF NOT EXISTS visa_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    country_id INT NOT NULL,
    application_date DATE DEFAULT NULL,
    status ENUM('not_started','document_collection','application_submitted','biometrics_completed','under_review','approved','rejected') DEFAULT 'not_started',
    expected_decision_date DATE DEFAULT NULL,
    decision_date DATE DEFAULT NULL,
    visa_type VARCHAR(100) DEFAULT NULL,
    visa_number VARCHAR(100) DEFAULT NULL,
    expiry_date DATE DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    counselor_id INT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT current_timestamp(),
    updated_at TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE,
    FOREIGN KEY (counselor_id) REFERENCES counselors(id) ON DELETE SET NULL
);
            </pre>
            <p>After creating the tables, refresh this page.</p>
        </div>
        <?php else: ?>
        <!-- Filters -->
        <div class="bg-white p-4 rounded mb-4">
            <h5 class="mb-3">Report Filters</h5>
            <form method="GET">
                <div class="row">
                    <div class="col-md-3 mb-2">
                        <label>Start Date</label>
                        <input type="date" name="from" class="form-control" value="<?= htmlspecialchars($date_from) ?>" required>
                    </div>
                    <div class="col-md-3 mb-2">
                        <label>End Date</label>
                        <input type="date" name="to" class="form-control" value="<?= htmlspecialchars($date_to) ?>" required>
                    </div>
                    <div class="col-md-3 mb-2">
                        <label>Country</label>
                        <select name="country" class="form-control">
                            <option value="">All Countries</option>
                            <?php foreach ($countries as $country): ?>
                            <option value="<?= $country['id'] ?>" <?= $country_filter == $country['id'] ? 'selected' : '' ?>>
                                <?= htmlspecialchars($country['name']) ?>
                            </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-3 mb-2">
                        <label>&nbsp;</label>
                        <button type="submit" class="btn btn-primary btn-block">Generate Report</button>
                    </div>
                </div>
            </form>
        </div>
        
        <!-- Overall Statistics -->
        <div class="row mb-4">
            <div class="col-md-3 mb-3">
                <div class="stat-card">
                    <div class="card-body">
                        <h6>Total Applications</h6>
                        <h3><?= number_format($overall_stats['total_applications']) ?></h3>
                    </div>
                </div>
            </div>
            <div class="col-md-3 mb-3">
                <div class="stat-card" style="border-left-color: #28a745;">
                    <div class="card-body">
                        <h6>Approved</h6>
                        <h3><?= number_format($overall_stats['approved_count']) ?></h3>
                    </div>
                </div>
            </div>
            <div class="col-md-3 mb-3">
                <div class="stat-card" style="border-left-color: #dc3545;">
                    <div class="card-body">
                        <h6>Rejected</h6>
                        <h3><?= number_format($overall_stats['rejected_count']) ?></h3>
                    </div>
                </div>
            </div>
            <div class="col-md-3 mb-3">
                <div class="stat-card" style="border-left-color: #17a2b8;">
                    <div class="card-body">
                        <h6>Success Rate</h6>
                        <div class="success-rate"><?= number_format($overall_stats['overall_success_rate'], 2) ?>%</div>
                    </div>
                </div>
            </div>
        </div>
        
        <!-- Country Success Rates -->
        <div class="bg-white p-4 rounded mb-4">
            <h5 class="mb-3">Success Rates by Country</h5>
            <?php if (empty($visa_data)): ?>
            <div class="text-center py-3 text-muted">
                No visa application data available for the selected period.
            </div>
            <?php else: ?>
            <div class="table-responsive">
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>Country</th>
                            <th>Total Applications</th>
                            <th>Approved</th>
                            <th>Rejected</th>
                            <th>Success Rate</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($visa_data as $data): ?>
                        <tr>
                            <td>
                                <?php 
                                $flag_path = "img/flags/" . strtolower($data['country_name']) . ".png";
                                if (file_exists($flag_path)): ?>
                                <img src="<?= $flag_path ?>" 
                                     alt="<?= htmlspecialchars($data['country_name']) ?>" 
                                     class="country-flag">
                                <?php endif; ?>
                                <?= htmlspecialchars($data['country_name']) ?>
                            </td>
                            <td><?= number_format($data['total_applications']) ?></td>
                            <td class="approved"><?= number_format($data['approved_count']) ?></td>
                            <td class="rejected"><?= number_format($data['rejected_count']) ?></td>
                            <td>
                                <div class="d-flex align-items-center">
                                    <span class="
                                        <?php 
                                        if ($data['success_rate'] >= 80) echo 'rate-high';
                                        elseif ($data['success_rate'] >= 60) echo 'rate-medium';
                                        else echo 'rate-low';
                                        ?>
                                    ">
                                        <?= number_format($data['success_rate'], 2) ?>%
                                    </span>
                                    <div class="progress-container flex-grow-1 mx-2">
                                        <div class="progress-bar" style="width: <?= $data['success_rate'] ?>%"></div>
                                    </div>
                                </div>
                            </td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
            <?php endif; ?>
        </div>
        
        <!-- Top Counselors -->
        <?php if (!empty($top_counselors)): ?>
        <div class="bg-white p-4 rounded mb-4">
            <h5 class="mb-3">Top Counselors by Visa Success Rate</h5>
            <div class="table-responsive">
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>Counselor</th>
                            <th>Total Applications</th>
                            <th>Approved</th>
                            <th>Success Rate</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($top_counselors as $counselor): ?>
                        <tr>
                            <td><?= htmlspecialchars($counselor['counselor_name']) ?></td>
                            <td><?= number_format($counselor['total_applications']) ?></td>
                            <td><?= number_format($counselor['approved_count']) ?></td>
                            <td>
                                <span class="
                                    <?php 
                                    if ($counselor['success_rate'] >= 80) echo 'rate-high';
                                    elseif ($counselor['success_rate'] >= 60) echo 'rate-medium';
                                    else echo 'rate-low';
                                    ?>
                                ">
                                    <?= number_format($counselor['success_rate'], 2) ?>%
                                </span>
                            </td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
        <?php endif; ?>
        
        <!-- Report Summary -->
        <div class="bg-white p-4 rounded">
            <h5 class="mb-3">Report Summary</h5>
            <p>This report shows the visa application success rates for the period from <strong><?= date('F j, Y', strtotime($date_from)) ?></strong> to <strong><?= date('F j, Y', strtotime($date_to)) ?></strong>.</p>
            
            <?php if (!empty($visa_data)): ?>
            <p>
                <?php 
                $best_country = !empty($visa_data) ? $visa_data[0] : null;
                $worst_country = !empty($visa_data) ? end($visa_data) : null;
                ?>
                <?php if ($best_country): ?>
                The country with the highest success rate is <strong><?= htmlspecialchars($best_country['country_name']) ?></strong> at <strong><?= number_format($best_country['success_rate'], 2) ?>%</strong>.
                <?php endif; ?>
                <?php if ($worst_country && count($visa_data) > 1): ?>
                The country with the lowest success rate is <strong><?= htmlspecialchars($worst_country['country_name']) ?></strong> at <strong><?= number_format($worst_country['success_rate'], 2) ?>%</strong>.
                <?php endif; ?>
            </p>
            <?php else: ?>
            <p class="text-muted">No visa application data available for the selected criteria.</p>
            <?php endif; ?>
            
            <p class="text-muted"><strong>Note:</strong> Success rate is calculated as (Approved Applications / Total Applications) × 100%. Applications with status 'under_review' are not included in this calculation.</p>
            
            <div class="mt-3">
                <a href="admin-reports.php" class="btn btn-secondary">Back to Reports</a>
                <button onclick="window.print()" class="btn btn-primary">Print Report</button>
            </div>
        </div>
        <?php endif; ?>
    </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>
</body>
</html>