<?php
/* ============================================================================
   WBMCZ — FIX MIGRATION  (auto-increment repair)
   Run this ONCE on the live server:   https://wbmcz.online/fixmigration.php

   Why this exists
   ----------------------------------------------------------------------------
   The database was set up from a mysqldump file. In that kind of file a table is
   created first as

       CREATE TABLE `roles` ( `id` int(11) NOT NULL, ... )

   and the AUTO_INCREMENT is added much later, near the very end, as

       ALTER TABLE `roles` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

   Twenty-five tables in your dump depend on those closing lines. If the import
   stopped early — a timeout, a size limit, phpMyAdmin giving up on a 758 KB
   file — every table still reads perfectly and every old row is there, but
   nothing new can ever be added to it. MySQL simply has no number to give the
   new row.

   That is why "The role could not be created" appears on the live site and not
   on your own machine, where the file imported all the way through.

   This page checks every table, repairs any it finds like that, and tells you
   exactly which ones were broken. Nothing else is touched — no data is read,
   moved or deleted.

   Safe to run more than once. DELETE THIS FILE after a successful run.
   ============================================================================ */

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

$conn = $db->getConnection();
$rows = [];
$fixed = 0; $ok = 0; $failed = 0; $skipped = 0;

/* Every table in this database. */
$tables = [];
try {
    $r = $conn->query("SHOW TABLES");
    while ($r && ($t = $r->fetch_row())) { $tables[] = $t[0]; }
} catch (\Throwable $e) {}

foreach ($tables as $t) {

    /* What does the primary key look like? */
    $pk = [];
    try {
        $r = $conn->query("SHOW KEYS FROM `$t` WHERE Key_name = 'PRIMARY'");
        while ($r && ($k = $r->fetch_assoc())) { $pk[] = $k['Column_name']; }
    } catch (\Throwable $e) { }

    if (count($pk) !== 1) {
        $rows[] = ['skip', $t, count($pk) === 0 ? 'no primary key — left alone' : 'primary key spans several columns — left alone'];
        $skipped++;
        continue;
    }

    $col = $pk[0];

    $info = null;
    try {
        $r = $conn->query("SHOW COLUMNS FROM `$t` LIKE '" . $conn->real_escape_string($col) . "'");
        $info = $r ? $r->fetch_assoc() : null;
    } catch (\Throwable $e) {}

    if (!$info) { $rows[] = ['skip', $t, 'could not read the key column']; $skipped++; continue; }

    $type = strtolower((string)$info['Type']);
    $isInt = (strpos($type, 'int') !== false);

    if (!$isInt) {
        $rows[] = ['skip', "$t.$col", 'the key is not a number — left alone'];
        $skipped++;
        continue;
    }

    if (stripos((string)$info['Extra'], 'auto_increment') !== false) {
        $rows[] = ['ok', "$t.$col", 'already counts up on its own'];
        $ok++;
        continue;
    }

    /* Broken: a numeric primary key with no auto-increment. Repair it, and start
       the counter above whatever the highest existing row is. */
    $next = 1;
    try {
        $r = $conn->query("SELECT COALESCE(MAX(`$col`), 0) + 1 AS n FROM `$t`");
        if ($r && ($x = $r->fetch_assoc())) { $next = max(1, (int)$x['n']); }
    } catch (\Throwable $e) {}

    try {
        $conn->query("ALTER TABLE `$t` MODIFY `$col` " . $info['Type'] . " NOT NULL AUTO_INCREMENT");
        try { $conn->query("ALTER TABLE `$t` AUTO_INCREMENT = $next"); } catch (\Throwable $e) {}
        $rows[] = ['add', "$t.$col", "REPAIRED — new rows will now start at $next"];
        $fixed++;
    } catch (\Throwable $e) {
        $rows[] = ['fail', "$t.$col", $e->getMessage()];
        $failed++;
    }
}

/* A live test: can a role actually be created now? */
$probe = ['ran' => false, 'ok' => false, 'error' => ''];
try {
    $has = $conn->query("SHOW TABLES LIKE 'roles'");
    if ($has && $has->num_rows) {
        $probe['ran'] = true;
        $name = '__probe_' . bin2hex(random_bytes(3));
        $st = $conn->prepare("INSERT INTO roles (name, permissions) VALUES (?, '[]')");
        if (!$st) {
            $probe['error'] = 'prepare failed: ' . $conn->error;
        } else {
            $st->bind_param('s', $name);
            if (!$st->execute()) {
                $probe['error'] = 'insert failed: ' . $st->error;
            } elseif ((int)$st->insert_id === 0) {
                $probe['error'] = 'the row went in but MySQL gave it no id — the key still does not count up';
            } else {
                $probe['ok'] = true;
                $newId = (int)$st->insert_id;
                $conn->query("DELETE FROM roles WHERE id = $newId");   // tidy up after ourselves
            }
            $st->close();
        }
    }
} catch (\Throwable $e) { $probe['error'] = $e->getMessage(); }
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Fix Migration — auto-increment repair</title>
    <script src="https://cdn.tailwindcss.com"></script>
</head>
<body class="bg-slate-100 p-6 font-sans">
    <div class="max-w-3xl mx-auto bg-white rounded-2xl shadow p-6 border border-slate-200">
        <h1 class="text-2xl font-extrabold text-slate-800 mb-1">Fix Migration — auto-increment repair</h1>
        <p class="text-sm text-slate-500 mb-5">Database: <b><?php echo DB_NAME; ?></b> &middot; <?php echo count($tables); ?> table(s) checked</p>

        <?php if ($probe['ran']): ?>
        <div class="mb-5 rounded-xl px-4 py-3 <?php echo $probe['ok'] ? 'bg-emerald-50 text-emerald-700 border border-emerald-200' : 'bg-rose-50 text-rose-700 border border-rose-200'; ?>">
            <p class="font-bold text-sm mb-1">
                <?php if ($probe['ok']): ?>
                    Creating a role works now.
                <?php else: ?>
                    Creating a role still does not work.
                <?php endif; ?>
            </p>
            <p class="text-xs">
                <?php if ($probe['ok']): ?>
                    A test role was added and removed again cleanly. Go and make your role — it will save.
                <?php else: ?>
                    MySQL said: <span class="font-mono"><?php echo htmlspecialchars($probe['error']); ?></span>
                <?php endif; ?>
            </p>
        </div>
        <?php endif; ?>

        <div class="mb-5 rounded-xl px-4 py-3 <?php echo $failed ? 'bg-rose-50 text-rose-700 border border-rose-200' : ($fixed ? 'bg-emerald-50 text-emerald-700 border border-emerald-200' : 'bg-slate-50 text-slate-600 border border-slate-200'); ?>">
            <?php if ($failed): ?>
                Finished, but <?php echo $failed; ?> table(s) could not be repaired — see the FAIL rows.
            <?php elseif ($fixed): ?>
                <b><?php echo $fixed; ?> table(s) repaired.</b> <?php echo $ok; ?> were already fine, <?php echo $skipped; ?> were left alone.
            <?php else: ?>
                Nothing needed repairing — all <?php echo $ok; ?> numbered tables already count up on their own.
                If a page still refuses to save, the cause is something else; the FAIL and SKIP rows below are the place to look.
            <?php endif; ?>
        </div>

        <table class="w-full text-sm border-collapse">
            <thead>
                <tr class="bg-slate-50 text-slate-500 text-xs uppercase tracking-wider">
                    <th class="text-left py-2 px-3">Result</th>
                    <th class="text-left py-2 px-3">Table</th>
                    <th class="text-left py-2 px-3">Detail</th>
                </tr>
            </thead>
            <tbody class="divide-y divide-slate-100">
            <?php
            /* the repaired ones first — they are what you came to see */
            usort($rows, function ($a, $b) {
                $w = ['fail' => 0, 'add' => 1, 'skip' => 2, 'ok' => 3];
                return ($w[$a[0]] ?? 9) <=> ($w[$b[0]] ?? 9);
            });
            foreach ($rows as $l):
                $cls = ['ok' => 'text-slate-400', 'add' => 'text-emerald-600', 'skip' => 'text-amber-600', 'fail' => 'text-rose-600'][$l[0]] ?? '';
            ?>
                <tr>
                    <td class="py-2 px-3 font-bold uppercase <?php echo $cls; ?>"><?php echo $l[0] === 'add' ? 'FIXED' : $l[0]; ?></td>
                    <td class="py-2 px-3 font-mono text-slate-700 text-xs"><?php echo htmlspecialchars($l[1]); ?></td>
                    <td class="py-2 px-3 text-slate-500 text-xs"><?php echo htmlspecialchars($l[2]); ?></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>

        <div class="mt-6 text-xs text-slate-600 space-y-2 bg-slate-50 border border-slate-200 rounded-xl p-4">
            <p class="font-bold text-slate-700 text-sm">What this was about</p>
            <p>A table whose numbered key does not count up on its own can be read from perfectly — every
               existing row is fine — but nothing new can ever be added to it, because MySQL has no number
               to give the new row. It looks like a broken page; it is really an unfinished import.</p>
            <p>Any table listed as <b>FIXED</b> above was in that state. Whatever pages were quietly refusing
               to save into them will work from now on.</p>
        </div>

        <p class="mt-4 text-xs text-rose-600 font-bold">Delete fixmigration.php from the server once this page shows the repair is done.</p>
    </div>
</body>
</html>
