<?php
require __DIR__ . '/../vendor/autoload.php';
$app = require_once __DIR__ . '/../bootstrap/app.php';
$kernel = $app->make(Illuminate\Contracts\Http\Kernel::class);
$kernel->handle(
    $request = Illuminate\Http\Request::capture()
);

use Illuminate\Support\Facades\DB;

echo "<h2>SQL Mode Check</h2>";
$sqlMode = DB::select("SELECT @@sql_mode as mode")[0]->mode ?? 'unknown';
echo "Current SQL mode: <code>" . htmlspecialchars($sqlMode) . "</code><br><br>";

echo "<h2>Test Query 1: Simple GROUP BY with DATE_FORMAT</h2>";
try {
    $result = DB::select("SELECT DATE_FORMAT(hire_date, '%Y-%m') as year_month, COUNT(*) as count FROM employees WHERE hire_date >= ? GROUP BY DATE_FORMAT(hire_date, '%Y-%m') ORDER BY DATE_FORMAT(hire_date, '%Y-%m')", [now()->subMonths(6)]);
    echo "✅ SUCCESS! Rows returned: " . count($result) . "<br>";
    echo "<pre>" . print_r($result, true) . "</pre>";
} catch (Exception $e) {
    echo "❌ ERROR: " . htmlspecialchars($e->getMessage()) . "<br>";
}

echo "<h2>Test Query 2: Laravel Query Builder (same as DashboardController)</h2>";
try {
    $result = DB::table('employees')
        ->select(DB::raw("DATE_FORMAT(hire_date, '%Y-%m') as year_month"), DB::raw("COUNT(*) as count"))
        ->where('hire_date', '>=', now()->subMonths(6))
        ->groupBy(DB::raw("DATE_FORMAT(hire_date, '%Y-%m')"))
        ->orderBy(DB::raw("DATE_FORMAT(hire_date, '%Y-%m')"))
        ->get();
    echo "✅ SUCCESS! Rows returned: " . count($result) . "<br>";
    echo "<pre>" . print_r($result->toArray(), true) . "</pre>";
} catch (Exception $e) {
    echo "❌ ERROR: " . htmlspecialchars($e->getMessage()) . "<br>";
}

echo "<h2>DashboardController Code Check (line 60-67)</h2>";
$file = file_get_contents(__DIR__ . '/../app/Http/Controllers/Api/DashboardController.php');
$lines = explode("\n", $file);
for ($i = 59; $i <= 72; $i++) {
    echo ($i+1) . ": " . htmlspecialchars($lines[$i] ?? '') . "<br>";
}
