<?php
// Diagnóstico simples do banco: lista tabelas e descreve employees/time_records
// Uso: acesse http://localhost:8888/gabinete/db_check.php

require_once __DIR__ . '/src/config/config.php';
require_once __DIR__ . '/src/config/db.php';

header('Content-Type: text/plain; charset=utf-8');

try {
    // $pdo vem de src/config/db.php
    if (!isset($pdo) || !($pdo instanceof PDO)) {
        echo "ERRO: PDO não inicializado.\n";
        exit(1);
    }

    echo "Conectado em: host=" . DB_HOST . " port=" . (defined('DB_PORT') ? DB_PORT : '3306') . " db=" . DB_NAME . "\n";

    // Listar tabelas
    $tables = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);
    echo "Tabelas: " . (empty($tables) ? '(nenhuma)' : implode(', ', $tables)) . "\n\n";

    // Helper para DESCRIBE
    $describe = function(string $table) use ($pdo) {
        try {
            $rows = $pdo->query('DESCRIBE ' . $table)->fetchAll(PDO::FETCH_ASSOC);
            foreach ($rows as $r) {
                echo sprintf(" - %-20s %-20s\n", $r['Field'], $r['Type']);
            }
        } catch (Throwable $e) {
            echo " - ERRO ao DESCRIBE: " . $e->getMessage() . "\n";
        }
    };

    // Employees
    echo "[employees]\n";
    if (in_array('employees', $tables)) {
        $describe('employees');
        try {
            $cols = $pdo->query("SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'employees' ORDER BY ORDINAL_POSITION")
                        ->fetchAll(PDO::FETCH_COLUMN);
            echo "Colunas: " . implode(', ', $cols) . "\n";
        } catch (Throwable $e) {}
        try {
            $count = $pdo->query('SELECT COUNT(*) FROM employees')->fetchColumn();
            echo "Total registros: " . $count . "\n";
            $sample = $pdo->query('SELECT id, full_name, job_title FROM employees LIMIT 3')->fetchAll(PDO::FETCH_ASSOC);
            echo "Amostra (id, full_name, job_title):\n";
            foreach ($sample as $s) {
                echo " - id=".$s['id']." | full_name=".($s['full_name'] ?? '(NULL)')." | job_title=".($s['job_title'] ?? '(NULL)')."\n";
            }
        } catch (Throwable $e) {
            echo " - ERRO ao consultar employees: " . $e->getMessage() . "\n";
        }
    } else {
        echo "Tabela employees NÃO encontrada.\n";
    }
    echo "\n";

    // time_records
    echo "[time_records]\n";
    if (in_array('time_records', $tables)) {
        $describe('time_records');
        try {
            $cols = $pdo->query("SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'time_records' ORDER BY ORDINAL_POSITION")
                        ->fetchAll(PDO::FETCH_COLUMN);
            echo "Colunas: " . implode(', ', $cols) . "\n";
        } catch (Throwable $e) {}
        try {
            $count = $pdo->query('SELECT COUNT(*) FROM time_records')->fetchColumn();
            echo "Total registros: " . $count . "\n";
            $sample = $pdo->query('SELECT id, employee_id, work_date FROM time_records LIMIT 3')->fetchAll(PDO::FETCH_ASSOC);
            echo "Amostra (id, employee_id, work_date):\n";
            foreach ($sample as $s) {
                echo " - id=".$s['id']." | employee_id=".$s['employee_id']." | work_date=".$s['work_date']."\n";
            }
        } catch (Throwable $e) {
            echo " - ERRO ao consultar time_records: " . $e->getMessage() . "\n";
        }
    } else {
        echo "Tabela time_records NÃO encontrada.\n";
    }
    echo "\n";

    // Teste rápido da query do TimeRecord (JOIN)
    echo "[Teste JOIN employees x time_records]\n";
    try {
        $sql = "SELECT tr.id, tr.work_date, e.full_name AS employee_name, e.job_title FROM time_records tr JOIN employees e ON e.id = tr.employee_id ORDER BY e.full_name ASC, tr.work_date DESC LIMIT 5";
        $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        if (empty($rows)) {
            echo " - Sem registros para exibir.\n";
        } else {
            foreach ($rows as $r) {
                echo " - tr.id=".$r['id']." | work_date=".$r['work_date']." | employee_name=".$r['employee_name']." | job_title=".$r['job_title']."\n";
            }
        }
    } catch (Throwable $e) {
        echo " - ERRO na query JOIN: " . $e->getMessage() . "\n";
    }

} catch (Throwable $e) {
    echo "ERRO geral: " . $e->getMessage() . "\n";
}