<?php
header('Content-Type: text/html; charset=utf-8');
error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<h2>Diagnóstico da API Absences</h2>";

echo "<h3>1. Testando config.php...</h3>";
try {
    require_once __DIR__ . '/../includes/config.php';
    echo "<p style='color:green'>✓ config.php carregado com sucesso</p>";
} catch (Exception $e) {
    echo "<p style='color:red'>✗ Erro no config.php: " . $e->getMessage() . "</p>";
    exit;
}

echo "<h3>2. Testando conexão com banco...</h3>";
if (isset($pdo)) {
    echo "<p style='color:green'>✓ Variável \$pdo existe</p>";
} else {
    echo "<p style='color:red'>✗ Variável \$pdo não existe</p>";
    exit;
}

echo "<h3>3. Verificando tabela absences...</h3>";
try {
    $stmt = $pdo->query("SHOW TABLES LIKE 'absences'");
    $result = $stmt->fetch();
    if ($result) {
        echo "<p style='color:green'>✓ Tabela absences existe</p>";
    } else {
        echo "<p style='color:red'>✗ Tabela absences NÃO existe</p>";
        echo "<p>Execute este SQL no phpMyAdmin:</p>";
        echo "<pre>
CREATE TABLE absences (
    id VARCHAR(50) PRIMARY KEY,
    employee_id VARCHAR(50) NOT NULL,
    absence_type ENUM('justified', 'unjustified') DEFAULT 'unjustified',
    absence_date DATE NOT NULL,
    description TEXT,
    certificate_path VARCHAR(255),
    created_by VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);
        </pre>";
        exit;
    }
} catch (PDOException $e) {
    echo "<p style='color:red'>✗ Erro ao verificar tabela: " . $e->getMessage() . "</p>";
}

echo "<h3>4. Verificando tabela employees...</h3>";
try {
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM employees WHERE active = 1");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "<p style='color:green'>✓ Funcionários ativos: " . $result['total'] . "</p>";
} catch (PDOException $e) {
    echo "<p style='color:red'>✗ Erro ao verificar employees: " . $e->getMessage() . "</p>";
}

echo "<h3>5. Testando query de ranking...</h3>";
try {
    $startDate = date('Y-m-01');
    $endDate = date('Y-m-d');
    
    $stmt = $pdo->prepare("
        SELECT 
            e.id,
            e.name,
            e.cpf,
            COALESCE(SUM(CASE WHEN a.absence_type = 'justified' THEN 1 ELSE 0 END), 0) as justified_count,
            COALESCE(SUM(CASE WHEN a.absence_type = 'unjustified' THEN 1 ELSE 0 END), 0) as unjustified_count,
            COUNT(a.id) as total_absences
        FROM employees e
        LEFT JOIN absences a ON e.id = a.employee_id 
            AND a.absence_date >= ? AND a.absence_date <= ?
        WHERE e.active = 1
        GROUP BY e.id, e.name, e.cpf
        ORDER BY total_absences ASC, e.name ASC
    ");
    $stmt->execute([$startDate, $endDate]);
    $ranking = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<p style='color:green'>✓ Query de ranking funcionou! Retornou " . count($ranking) . " registros</p>";
    
    if (count($ranking) > 0) {
        echo "<table border='1' style='border-collapse:collapse'>";
        echo "<tr><th>ID</th><th>Nome</th><th>CPF</th><th>Justificadas</th><th>Não Justificadas</th><th>Total</th></tr>";
        foreach ($ranking as $row) {
            echo "<tr>";
            echo "<td>" . htmlspecialchars($row['id']) . "</td>";
            echo "<td>" . htmlspecialchars($row['name']) . "</td>";
            echo "<td>" . htmlspecialchars($row['cpf']) . "</td>";
            echo "<td>" . $row['justified_count'] . "</td>";
            echo "<td>" . $row['unjustified_count'] . "</td>";
            echo "<td>" . $row['total_absences'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
} catch (PDOException $e) {
    echo "<p style='color:red'>✗ Erro na query de ranking: " . $e->getMessage() . "</p>";
}

echo "<h3>6. Resposta JSON simulada:</h3>";
try {
    $startDate = date('Y-m-01');
    $endDate = date('Y-m-d');
    
    $stmt = $pdo->prepare("
        SELECT 
            e.id,
            e.name,
            e.cpf,
            COALESCE(SUM(CASE WHEN a.absence_type = 'justified' THEN 1 ELSE 0 END), 0) as justified_count,
            COALESCE(SUM(CASE WHEN a.absence_type = 'unjustified' THEN 1 ELSE 0 END), 0) as unjustified_count,
            COUNT(a.id) as total_absences
        FROM employees e
        LEFT JOIN absences a ON e.id = a.employee_id 
            AND a.absence_date >= ? AND a.absence_date <= ?
        WHERE e.active = 1
        GROUP BY e.id, e.name, e.cpf
        ORDER BY total_absences ASC, e.name ASC
    ");
    $stmt->execute([$startDate, $endDate]);
    $ranking = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $json = json_encode($ranking);
    if ($json === false) {
        echo "<p style='color:red'>✗ Erro ao converter para JSON: " . json_last_error_msg() . "</p>";
    } else {
        echo "<p style='color:green'>✓ JSON válido:</p>";
        echo "<pre>" . htmlspecialchars($json) . "</pre>";
    }
} catch (Exception $e) {
    echo "<p style='color:red'>✗ Erro: " . $e->getMessage() . "</p>";
}

echo "<hr><p><strong>Diagnóstico concluído!</strong></p>";
?>
