<?php
require_once __DIR__ . '/includes/init.php';

echo "<h2>Database Status Check</h2>";

try {
    $db = getDB();
    
    // Check employees
    $stmt = $db->query("SELECT COUNT(*) as cnt FROM employees WHERE employment_status = 'active'");
    $empCount = $stmt->fetch()['cnt'];
    echo "<p>✅ Active employees: <strong>$empCount</strong></p>";
    
    // Check if salary_register exists
    try {
        $stmt = $db->query("SELECT COUNT(*) as cnt FROM salary_register");
        $srCount = $stmt->fetch()['cnt'];
        echo "<p>✅ salary_register table exists with <strong>$srCount</strong> records</p>";
    } catch (Exception $e) {
        echo "<p>❌ salary_register table NOT found. Need to run migration!</p>";
        echo "<p><a href='database/run_salary_register_migration.php' style='background: #4CAF50; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px;'>Run Migration</a></p>";
    }
    
    // Check if salary_periods exists
    try {
        $stmt = $db->query("SELECT COUNT(*) as cnt FROM salary_periods");
        $spCount = $stmt->fetch()['cnt'];
        echo "<p>✅ salary_periods table exists with <strong>$spCount</strong> periods</p>";
    } catch (Exception $e) {
        echo "<p>❌ salary_periods table NOT found. Need to run migration!</p>";
    }
    
    // Show sample employee data
    echo "<h3>Sample Employee Data:</h3>";
    $stmt = $db->query("
        SELECT 
            employee_id,
            employee_code,
            CONCAT(first_name, ' ', last_name) as name,
            employment_status,
            created_by
        FROM employees 
        WHERE employment_status = 'active'
        LIMIT 5
    ");
    
    echo "<table border='1' style='border-collapse: collapse;'>";
    echo "<tr><th>ID</th><th>Code</th><th>Name</th><th>Status</th><th>Created By (HR ID)</th></tr>";
    while ($row = $stmt->fetch()) {
        echo "<tr>";
        echo "<td>{$row['employee_id']}</td>";
        echo "<td>{$row['employee_code']}</td>";
        echo "<td>{$row['name']}</td>";
        echo "<td>{$row['employment_status']}</td>";
        echo "<td>{$row['created_by']}</td>";
        echo "</tr>";
    }
    echo "</table>";
    
} catch (Exception $e) {
    echo "<p style='color: red;'>❌ Error: " . $e->getMessage() . "</p>";
}

echo "<hr>";
echo "<p><a href='modules/payroll/salary_register.php'>← Back to Salary Register</a></p>";
?>
