<?php
require_once 'config/config.php';

$database = new Database();
$db = $database->getConnection();

echo "<style>
    body { font-family: Arial, sans-serif; margin: 20px; background: #f8f9fa; }
    .container { max-width: 900px; background: white; padding: 20px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
    table { width: 100%; border-collapse: collapse; margin: 20px 0; }
    th, td { padding: 12px; border: 1px solid #dee2e6; text-align: left; }
    th { background: #f8f9fa; font-weight: 600; }
    .badge { padding: 4px 8px; border-radius: 4px; font-size: 0.875em; color: white; }
    .bg-success { background: #28a745; }
    .bg-primary { background: #007bff; }
    .bg-warning { background: #ffc107; color: #212529; }
    .bg-danger { background: #dc3545; }
    .bg-secondary { background: #6c757d; }
    .btn { padding: 10px 20px; background: #007bff; color: white; text-decoration: none; border-radius: 4px; margin: 5px; }
    .btn:hover { background: #0056b3; }
</style>";

echo "<div class='container'>";
echo "<h2>Survey Scores Status Dashboard</h2>";

// Check if total_score column exists
$stmt = $db->query("SHOW COLUMNS FROM job_applications LIKE 'total_score'");
if (!$stmt->fetch()) {
    echo "<div style='background: #f8d7da; color: #721c24; padding: 15px; border-radius: 4px; margin: 20px 0;'>";
    echo "<strong>Error:</strong> The total_score column doesn't exist in the job_applications table.";
    echo "<br>Please run the survey scoring system setup first.";
    echo "</div>";
    exit;
}

// Get score statistics
$stmt = $db->prepare("
    SELECT 
        COUNT(*) as total_applications,
        COUNT(CASE WHEN total_score > 0 THEN 1 END) as applications_with_scores,
        COUNT(CASE WHEN total_score IS NULL OR total_score = 0 THEN 1 END) as applications_without_scores,
        ROUND(AVG(CASE WHEN total_score > 0 THEN total_score END), 2) as avg_score,
        MAX(total_score) as max_score,
        MIN(CASE WHEN total_score > 0 THEN total_score END) as min_score
    FROM job_applications
");
$stmt->execute();
$stats = $stmt->fetch();

echo "<div style='display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 20px; margin: 20px 0;'>";

echo "<div style='background: #d4edda; padding: 20px; border-radius: 8px; text-align: center;'>";
echo "<h3 style='margin: 0; color: #155724;'>{$stats['total_applications']}</h3>";
echo "<p style='margin: 5px 0; color: #155724;'>Total Applications</p>";
echo "</div>";

echo "<div style='background: #d1ecf1; padding: 20px; border-radius: 8px; text-align: center;'>";
echo "<h3 style='margin: 0; color: #0c5460;'>{$stats['applications_with_scores']}</h3>";
echo "<p style='margin: 5px 0; color: #0c5460;'>With Scores</p>";
echo "</div>";

echo "<div style='background: #f8d7da; padding: 20px; border-radius: 8px; text-align: center;'>";
echo "<h3 style='margin: 0; color: #721c24;'>{$stats['applications_without_scores']}</h3>";
echo "<p style='margin: 5px 0; color: #721c24;'>Without Scores</p>";
echo "</div>";

if ($stats['avg_score']) {
    echo "<div style='background: #fff3cd; padding: 20px; border-radius: 8px; text-align: center;'>";
    echo "<h3 style='margin: 0; color: #856404;'>{$stats['avg_score']}%</h3>";
    echo "<p style='margin: 5px 0; color: #856404;'>Average Score</p>";
    echo "</div>";
}

echo "</div>";

// Get score distribution
if ($stats['applications_with_scores'] > 0) {
    echo "<h3>Score Distribution</h3>";
    $stmt = $db->prepare("
        SELECT 
            CASE 
                WHEN total_score >= 85 THEN 'Excellent (85-95%)'
                WHEN total_score >= 70 THEN 'Good (70-84%)'
                WHEN total_score >= 50 THEN 'Average (50-69%)'
                WHEN total_score >= 30 THEN 'Below Average (30-49%)'
                ELSE 'Poor (10-29%)'
            END as score_range,
            COUNT(*) as count,
            ROUND(AVG(total_score), 1) as avg_score
        FROM job_applications 
        WHERE total_score > 0
        GROUP BY score_range
        ORDER BY avg_score DESC
    ");
    $stmt->execute();
    $distribution = $stmt->fetchAll();
    
    echo "<table>";
    echo "<tr><th>Score Range</th><th>Count</th><th>Average Score</th></tr>";
    
    foreach ($distribution as $dist) {
        $badgeClass = 'bg-secondary';
        if (strpos($dist['score_range'], 'Excellent') !== false) $badgeClass = 'bg-success';
        elseif (strpos($dist['score_range'], 'Good') !== false) $badgeClass = 'bg-primary';
        elseif (strpos($dist['score_range'], 'Average') !== false) $badgeClass = 'bg-warning';
        elseif (strpos($dist['score_range'], 'Poor') !== false) $badgeClass = 'bg-danger';
        
        echo "<tr>";
        echo "<td><span class='badge {$badgeClass}'>{$dist['score_range']}</span></td>";
        echo "<td>{$dist['count']}</td>";
        echo "<td>{$dist['avg_score']}%</td>";
        echo "</tr>";
    }
    echo "</table>";
}

// Show recent applications with scores
echo "<h3>Recent Applications</h3>";
$stmt = $db->prepare("
    SELECT ja.id, ja.total_score, j.title, c.name as company_name,
           CONCAT(cand.first_name, ' ', cand.last_name) as candidate_name,
           ja.applied_at
    FROM job_applications ja
    JOIN jobs j ON ja.job_id = j.id
    JOIN companies c ON j.company_id = c.id
    JOIN candidates cand ON ja.candidate_id = cand.id
    ORDER BY ja.applied_at DESC
    LIMIT 10
");
$stmt->execute();
$recent_apps = $stmt->fetchAll();

echo "<table>";
echo "<tr><th>Application ID</th><th>Candidate</th><th>Job Title</th><th>Company</th><th>Score</th><th>Applied Date</th></tr>";

require_once 'includes/scoring_functions.php';

foreach ($recent_apps as $app) {
    echo "<tr>";
    echo "<td>#{$app['id']}</td>";
    echo "<td>{$app['candidate_name']}</td>";
    echo "<td>{$app['title']}</td>";
    echo "<td>{$app['company_name']}</td>";
    
    if ($app['total_score'] > 0) {
        $category = getScoreCategory($app['total_score']);
        $badgeClass = 'bg-' . $category['class'];
        echo "<td><span class='badge {$badgeClass}'>{$app['total_score']}%</span></td>";
    } else {
        echo "<td><span class='badge bg-secondary'>No Score</span></td>";
    }
    
    echo "<td>" . date('M j, Y', strtotime($app['applied_at'])) . "</td>";
    echo "</tr>";
}
echo "</table>";

// Action buttons
echo "<div style='margin-top: 30px;'>";
if ($stats['applications_without_scores'] > 0) {
    echo "<a href='run_dummy_scores.php' class='btn' style='background: #28a745;'>Generate Dummy Scores ({$stats['applications_without_scores']} applications)</a> ";
}
echo "<a href='company/applications.php' class='btn'>View Company Applications</a>";
echo "</div>";

echo "</div>";
?>