<?php
/* ============================================================================
   WBMCZ — TESTS MIGRATION  (Premium ID Card)
   Run this ONCE on the live server:   https://wbmcz.online/testsmigration.php

   What it does
     1. users → adds  card_number, employee_code, card_issued_on,
                      card_valid_till, card_status
     2. users → backfills every existing member with a permanent card number
                and employee ID (same formula the card itself uses)
     3. users → unique index on card_number + index on employee_code
     4. users → adds  created_by, created_by_name, created_via
                (which admin opened the account — shown in the "Created By"
                 column of admin → User List)

   Column meaning
     card_number      16 digits, printed on the card as 4-4-4-4
     employee_code    WB / WS / WA + zero padded user id   (e.g. WB1018)
     card_issued_on   the day the card was issued (defaults to the join date)
     card_valid_till  issue date + 5 years
     card_status      1 = active card, 0 = blocked / cancelled
     created_by       users.id of the admin who added the account (NULL = self)
     created_by_name  that admin's name, kept as a snapshot
     created_via      self | admin | import

   Safe to run more than once — every step checks before it changes anything.
   DELETE THIS FILE after a successful run.
   ============================================================================ */

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

$conn = $db->getConnection();
$log  = [];

function tst_table_exists($conn, $table) {
    try { $r = $conn->query("SHOW TABLES LIKE '$table'"); return $r && $r->num_rows > 0; }
    catch (\Throwable $e) { return false; }
}
function tst_col_exists($conn, $table, $col) {
    try { $r = $conn->query("SHOW COLUMNS FROM `$table` LIKE '$col'"); return $r && $r->num_rows > 0; }
    catch (\Throwable $e) { return false; }
}
function tst_index_exists($conn, $table, $index) {
    try { $r = $conn->query("SHOW INDEX FROM `$table` WHERE Key_name = '$index'"); return $r && $r->num_rows > 0; }
    catch (\Throwable $e) { return false; }
}
function tst_add_col(&$log, $conn, $table, $col, $def) {
    if (!tst_table_exists($conn, $table))    { $log[] = ["skip", "$table.$col", "table does not exist"]; return; }
    if (tst_col_exists($conn, $table, $col)) { $log[] = ["ok",   "$table.$col", "already present"];      return; }
    try {
        $conn->query("ALTER TABLE `$table` ADD COLUMN `$col` $def");
        $log[] = ["add", "$table.$col", $def];
    } catch (\Throwable $e) { $log[] = ["fail", "$table.$col", $e->getMessage()]; }
}
function tst_add_index(&$log, $conn, $table, $index, $sql) {
    if (!tst_table_exists($conn, $table))        { $log[] = ["skip", "$table.$index", "table does not exist"]; return; }
    if (tst_index_exists($conn, $table, $index)) { $log[] = ["ok",   "$table.$index", "already present"];      return; }
    try {
        $conn->query("ALTER TABLE `$table` ADD $sql");
        $log[] = ["add", "$table.$index", $sql];
    } catch (\Throwable $e) { $log[] = ["fail", "$table.$index", $e->getMessage()]; }
}

/* ---------------------------------------------------------------------------
   1. users — ID card columns
   --------------------------------------------------------------------------- */
tst_add_col($log, $conn, 'users', 'card_number',     "VARCHAR(24) NULL");
tst_add_col($log, $conn, 'users', 'employee_code',   "VARCHAR(20) NULL");
tst_add_col($log, $conn, 'users', 'card_issued_on',  "DATE NULL");
tst_add_col($log, $conn, 'users', 'card_valid_till', "DATE NULL");
tst_add_col($log, $conn, 'users', 'card_status',     "TINYINT(1) NOT NULL DEFAULT 1");

/* ---------------------------------------------------------------------------
   2. backfill — the very same formula includes/credentials.php uses, so a card
      that was already shown to a member never changes its number
   --------------------------------------------------------------------------- */
$made = 0; $kept = 0; $bad = 0;

if (tst_col_exists($conn, 'users', 'card_number')) {
    $taken = [];
    try {
        $r = $conn->query("SELECT card_number FROM users WHERE card_number IS NOT NULL AND card_number <> ''");
        if ($r) { while ($row = $r->fetch_assoc()) { $taken[$row['card_number']] = true; } }
    } catch (\Throwable $e) {}

    $rows = [];
    try {
        $r = $conn->query("SELECT id, user_type, created_at, card_number, employee_code, card_issued_on, card_valid_till
                           FROM users
                           WHERE card_number IS NULL OR card_number = ''
                              OR employee_code IS NULL OR employee_code = ''
                              OR card_issued_on IS NULL OR card_valid_till IS NULL");
        if ($r) { while ($row = $r->fetch_assoc()) { $rows[] = $row; } }
    } catch (\Throwable $e) { $log[] = ["fail", "users backfill", $e->getMessage()]; }

    $stmt = null;
    try {
        $stmt = $conn->prepare("UPDATE users SET card_number = ?, employee_code = ?, card_issued_on = ?, card_valid_till = ? WHERE id = ?");
    } catch (\Throwable $e) { $log[] = ["fail", "users backfill", $e->getMessage()]; }

    if ($stmt) {
        foreach ($rows as $u) {
            $uid  = (int)$u['id'];
            $type = $u['user_type'] ?? 'customer';
            $pre  = ($type === 'admin') ? 'WA' : (($type === 'seller') ? 'WS' : 'WB');

            $card = trim((string)$u['card_number']);
            if ($card === '') {
                $h    = sprintf('%010u', crc32('WBMCZ|CARD|' . $uid));
                $card = '5021' . substr($h, 0, 8) . str_pad((string)($uid % 10000), 4, '0', STR_PAD_LEFT);
                $try  = 0;
                while (isset($taken[$card]) && $try < 50) {            // never issue the same number twice
                    $try++;
                    $h    = sprintf('%010u', crc32('WBMCZ|CARD|' . $uid . '|' . $try));
                    $card = '5021' . substr($h, 0, 8) . str_pad((string)($uid % 10000), 4, '0', STR_PAD_LEFT);
                }
            }
            $taken[$card] = true;

            $emp = trim((string)$u['employee_code']);
            if ($emp === '') $emp = $pre . str_pad((string)$uid, 4, '0', STR_PAD_LEFT);

            $iss = (string)$u['card_issued_on'];
            if ($iss === '' || $iss === '0000-00-00') {
                $iss = !empty($u['created_at']) ? date('Y-m-d', strtotime($u['created_at'])) : date('Y-m-d');
            }
            $till = (string)$u['card_valid_till'];
            if ($till === '' || $till === '0000-00-00') $till = date('Y-m-d', strtotime($iss . ' +5 years'));

            try {
                $stmt->bind_param('ssssi', $card, $emp, $iss, $till, $uid);
                $stmt->execute() ? $made++ : $bad++;
            } catch (\Throwable $e) { $bad++; }
        }
        $stmt->close();
    }

    try {
        $r = $conn->query("SELECT COUNT(*) c FROM users WHERE card_number IS NOT NULL AND card_number <> ''");
        if ($r && ($x = $r->fetch_assoc())) $kept = (int)$x['c'];
    } catch (\Throwable $e) {}

    $log[] = [$bad ? "fail" : "add", "users backfill", "$made member(s) issued a card · $kept total · $bad error(s)"];
}

/* ---------------------------------------------------------------------------
   3. indexes — a card number can only ever belong to one member
   --------------------------------------------------------------------------- */
tst_add_index($log, $conn, 'users', 'uniq_users_card',  "UNIQUE KEY `uniq_users_card` (`card_number`)");
tst_add_index($log, $conn, 'users', 'idx_users_empcode', "INDEX `idx_users_empcode` (`employee_code`)");

/* ---------------------------------------------------------------------------
   4. users — who opened the account
      created_by IS NULL  → the member registered themselves
      created_by NOT NULL → an admin / staff member added them from
                            admin → User List → Add New User
   --------------------------------------------------------------------------- */
tst_add_col($log, $conn, 'users', 'created_by',      "INT NULL");
tst_add_col($log, $conn, 'users', 'created_by_name', "VARCHAR(120) NULL");
tst_add_col($log, $conn, 'users', 'created_via',     "VARCHAR(20) NULL DEFAULT 'self'");
tst_add_index($log, $conn, 'users', 'idx_users_createdby', "INDEX `idx_users_createdby` (`created_by`)");

if (tst_col_exists($conn, 'users', 'created_via')) {
    try {
        $conn->query("UPDATE users SET created_via = 'self' WHERE created_via IS NULL OR created_via = ''");
        $log[] = ["ok", "users.created_via", "existing accounts marked as self registered"];
    } catch (\Throwable $e) { $log[] = ["fail", "users.created_via", $e->getMessage()]; }
}

$fails = 0;
foreach ($log as $l) { if ($l[0] === 'fail') $fails++; }
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Tests Migration — WBMCZ Premium ID Card</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">Tests Migration — Premium ID Card</h1>
        <p class="text-sm text-slate-500 mb-5">Database: <b><?php echo DB_NAME; ?></b> &middot; ID card columns + &ldquo;created by&rdquo; on users</p>

        <div class="mb-5 rounded-xl px-4 py-3 <?php echo $fails ? 'bg-rose-50 text-rose-700 border border-rose-200' : 'bg-emerald-50 text-emerald-700 border border-emerald-200'; ?>">
            <?php echo $fails ? "Finished with $fails error(s) — see the rows marked FAIL." : 'Migration completed successfully.'; ?>
        </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">Target</th>
                    <th class="text-left py-2 px-3">Detail</th>
                </tr>
            </thead>
            <tbody class="divide-y divide-slate-100">
            <?php foreach ($log as $l):
                $cls = ['ok' => 'text-slate-500', '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]; ?></td>
                    <td class="py-2 px-3 font-mono text-slate-700"><?php echo htmlspecialchars($l[1]); ?></td>
                    <td class="py-2 px-3 text-slate-500"><?php echo htmlspecialchars($l[2]); ?></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>

        <p class="mt-6 text-xs text-rose-600 font-bold">Delete testsmigration.php from the server once this page shows &ldquo;completed successfully&rdquo;.</p>
    </div>
</body>
</html>
