<?php
declare(strict_types=1);
session_start();
require_once __DIR__.'/../includes/db_connect.php';

// Доступ: лише admin/teacher
if (!isset($_SESSION['user_id']) || !in_array($_SESSION['role'] ?? '', ['admin','teacher'], true)) {
    http_response_code(403);
    exit('Access denied');
}

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

function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8'); }

$email = trim((string)($_GET['email'] ?? ''));
$userId = isset($_GET['id']) ? (int)$_GET['id'] : 0;

// Визначаємо цільового користувача
$target = null;
if ($email !== '') {
    if ($st = $conn->prepare("SELECT id, full_name, email, role, last_login_at, last_grades_view_at FROM users WHERE email = ? LIMIT 1")) {
        $st->bind_param('s', $email);
        $st->execute();
        $rs = $st->get_result();
        $target = $rs ? $rs->fetch_assoc() : null;
        $st->close();
    }
} elseif ($userId > 0) {
    if ($st = $conn->prepare("SELECT id, full_name, email, role, last_login_at, last_grades_view_at FROM users WHERE id = ? LIMIT 1")) {
        $st->bind_param('i', $userId);
        $st->execute();
        $rs = $st->get_result();
        $target = $rs ? $rs->fetch_assoc() : null;
        $st->close();
    }
}

$db_name = '';
$db_ver  = '';
$db_user = '';
$db_user2= '';
$autocommit = '';

if ($q = $conn->query("SELECT DATABASE() AS dbn, VERSION() AS ver")) {
    if ($row = $q->fetch_assoc()) {
        $db_name = $row['dbn'] ?? '';
        $db_ver  = $row['ver'] ?? '';
    }
    $q->close();
}
if ($q = $conn->query("SELECT CURRENT_USER() cu, USER() u, @@autocommit ac")) {
    if ($row = $q->fetch_assoc()) {
        $db_user  = $row['cu'] ?? '';
        $db_user2 = $row['u']  ?? '';
        $autocommit = (string)($row['ac'] ?? '');
    }
    $q->close();
}

// Перевірка наявності колонок
$cols = [];
if ($st = $conn->prepare("
    SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'users'
      AND COLUMN_NAME IN ('last_login_at','last_grades_view_at')
    ORDER BY COLUMN_NAME
")) {
    $st->execute();
    $rs = $st->get_result();
    while ($r = $rs->fetch_assoc()) { $cols[$r['COLUMN_NAME']] = $r; }
    $st->close();
}

// Обробка тестових оновлень
$msg = '';
$err = '';
if ($target && ($_GET['do'] ?? '') === 'upd_login') {
    $now = date('Y-m-d H:i:s');
    if ($u = $conn->prepare("UPDATE users SET last_login_at = ? WHERE id = ?")) {
        $u->bind_param('si', $now, $target['id']);
        $u->execute();
        $msg = "UPDATE last_login_at affected_rows=".$u->affected_rows;
        if ($u->errno) $err = "errno={$u->errno} error=".$u->error;
        $u->close();
        // Перечитуємо
        if ($st = $conn->prepare("SELECT last_login_at, last_grades_view_at FROM users WHERE id = ?")) {
            $st->bind_param('i', $target['id']);
            $st->execute();
            $rs = $st->get_result();
            if ($row = $rs->fetch_assoc()) {
                $target['last_login_at']        = $row['last_login_at'];
                $target['last_grades_view_at']  = $row['last_grades_view_at'];
            }
            $st->close();
        }
    } else {
        $err = "prepare failed: ".$conn->error;
    }
}
if ($target && ($_GET['do'] ?? '') === 'upd_view') {
    $now = date('Y-m-d H:i:s');
    if ($u = $conn->prepare("UPDATE users SET last_grades_view_at = ? WHERE id = ?")) {
        $u->bind_param('si', $now, $target['id']);
        $u->execute();
        $msg = "UPDATE last_grades_view_at affected_rows=".$u->affected_rows;
        if ($u->errno) $err = "errno={$u->errno} error=".$u->error;
        $u->close();
        // Перечитуємо
        if ($st = $conn->prepare("SELECT last_login_at, last_grades_view_at FROM users WHERE id = ?")) {
            $st->bind_param('i', $target['id']);
            $st->execute();
            $rs = $st->get_result();
            if ($row = $rs->fetch_assoc()) {
                $target['last_login_at']        = $row['last_login_at'];
                $target['last_grades_view_at']  = $row['last_grades_view_at'];
            }
            $st->close();
        }
    } else {
        $err = "prepare failed: ".$conn->error;
    }
}
?>
<!doctype html>
<html lang="uk">
<meta charset="utf-8">
<title>DB діагностика</title>
<style>
body{font-family:system-ui,Segoe UI,Roboto,Arial;background:#f6f7fb;margin:0;padding:20px;color:#111}
.card{max-width:900px;margin:0 auto;background:#fff;border:1px solid #e5e7eb;border-radius:12px;box-shadow:0 6px 18px rgba(20,20,20,.06);padding:16px}
h1{margin:0 0 10px}
table{border-collapse:collapse;width:100%;margin:10px 0}
td,th{border:1px solid #e5e7eb;padding:8px;text-align:left;font-size:14px}
kbd{background:#111;color:#fff;padding:2px 6px;border-radius:6px;font-size:12px}
.bad{color:#b91c1c}
.ok{color:#15803d}
.actions a{display:inline-block;margin-right:8px;padding:8px 12px;border-radius:10px;background:#2563eb;color:#fff;text-decoration:none;font-weight:700}
.actions a:hover{background:#1d4ed8}
label{font-weight:700}
input[type=text]{padding:8px 10px;border:1px solid #e5e7eb;border-radius:8px}
</style>
<div class="card">
    <h1>Діагностика БД</h1>

    <table>
        <tr><th>DATABASE()</th><td><?=h($db_name)?></td></tr>
        <tr><th>VERSION()</th><td><?=h($db_ver)?></td></tr>
        <tr><th>CURRENT_USER()</th><td><?=h($db_user)?></td></tr>
        <tr><th>USER()</th><td><?=h($db_user2)?></td></tr>
        <tr><th>@@autocommit</th><td><?=h($autocommit)?></td></tr>
    </table>

    <h3>Колонки в <kbd>users</kbd></h3>
    <table>
        <tr><th>Column</th><th>Type</th><th>Nullable</th><th>Default</th><th>Present</th></tr>
        <?php foreach (['last_login_at','last_grades_view_at'] as $c): ?>
            <tr>
                <td><?=h($c)?></td>
                <td><?=h($cols[$c]['DATA_TYPE'] ?? '—')?></td>
                <td><?=h($cols[$c]['IS_NULLABLE'] ?? '—')?></td>
                <td><?=h($cols[$c]['COLUMN_DEFAULT'] ?? '—')?></td>
                <td><?=isset($cols[$c]) ? '<span class="ok">так</span>' : '<span class="bad">ні</span>'?></td>
            </tr>
        <?php endforeach; ?>
    </table>

    <form method="get" class="actions" style="margin:10px 0">
        <label>Email або ID:&nbsp;</label>
        <input type="text" name="email" placeholder="email@example.com" value="<?=h($email)?>">
        <input type="text" name="id" placeholder="або ID" value="<?= $userId ? (int)$userId : '' ?>">
        <button type="submit" style="padding:8px 12px;border:0;border-radius:10px;background:#111;color:#fff;cursor:pointer">Показати</button>
    </form>

    <?php if ($target): ?>
        <h3>Користувач</h3>
        <table>
            <tr><th>ID</th><td><?= (int)$target['id']?></td></tr>
            <tr><th>ПІБ</th><td><?= h($target['full_name'])?></td></tr>
            <tr><th>Email</th><td><?= h($target['email'])?></td></tr>
            <tr><th>Role</th><td><?= h($target['role'])?></td></tr>
            <tr><th>last_login_at</th><td><?= h((string)$target['last_login_at'])?></td></tr>
            <tr><th>last_grades_view_at</th><td><?= h((string)$target['last_grades_view_at'])?></td></tr>
        </table>

        <div class="actions">
            <a href="?<?= http_build_query(['email'=>$email,'id'=>$userId,'do'=>'upd_login']) ?>">Тест: оновити last_login_at</a>
            <a href="?<?= http_build_query(['email'=>$email,'id'=>$userId,'do'=>'upd_view']) ?>">Тест: оновити last_grades_view_at</a>
        </div>

        <?php if ($msg || $err): ?>
            <p><strong>Результат:</strong> <?= h($msg) ?></p>
            <?php if ($err): ?><p class="bad"><strong>Помилка:</strong> <?= h($err) ?></p><?php endif; ?>
        <?php endif; ?>

    <?php elseif ($email || $userId): ?>
        <p class="bad"><strong>Користувача не знайдено.</strong></p>
    <?php else: ?>
        <p>Вкажіть <kbd>email</kbd> або <kbd>id</kbd> у параметрах і натисніть «Показати».</p>
    <?php endif; ?>
</div>
