<?php
/* ============================================================================
   WBMCZ — NEO SSP MIGRATION
   Run this ONCE on the live server:   https://wbmcz.online/neosspmigration.php

   What it does
     1. contact_messages  → creates the table if it is missing
                            (public contact form  ->  admin/messages.php)
     2. contact_messages  → adds  admin_note, handled_by, updated_at
     3. contact_messages  → adds an index on `status` so the unread badge in
                            the admin sidebar stays fast
     4. users             → adds  deleted_at, deleted_by, delete_reason
                            (soft delete / restore from admin → User Management)

   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 neo_table_exists($conn, $table) {
    try { $r = $conn->query("SHOW TABLES LIKE '$table'"); return $r && $r->num_rows > 0; }
    catch (\Throwable $e) { return false; }
}
function neo_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 neo_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 neo_run(&$log, $conn, $label, $sql) {
    try { $conn->query($sql); $log[] = ["ok", $label, "executed"]; }
    catch (\Throwable $e) { $log[] = ["fail", $label, $e->getMessage()]; }
}
function neo_add_col(&$log, $conn, $table, $col, $def) {
    if (!neo_table_exists($conn, $table))      { $log[] = ["skip", "$table.$col", "table does not exist"]; return; }
    if (neo_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 neo_add_index(&$log, $conn, $table, $index, $cols) {
    if (!neo_table_exists($conn, $table))            { $log[] = ["skip", "$table.$index", "table does not exist"]; return; }
    if (neo_index_exists($conn, $table, $index))     { $log[] = ["ok",   "$table.$index", "already present"];      return; }
    try {
        $conn->query("ALTER TABLE `$table` ADD INDEX `$index` ($cols)");
        $log[] = ["add", "$table.$index", "INDEX ($cols)"];
    } catch (\Throwable $e) { $log[] = ["fail", "$table.$index", $e->getMessage()]; }
}

/* ---------------------------------------------------------------------------
   1. contact_messages — created only if the live database does not have it
   --------------------------------------------------------------------------- */
if (!neo_table_exists($conn, 'contact_messages')) {
    neo_run($log, $conn, 'CREATE contact_messages', "
        CREATE TABLE IF NOT EXISTS contact_messages (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            mobile VARCHAR(15) NOT NULL,
            service VARCHAR(100) NULL,
            message TEXT NULL,
            status TINYINT(1) NOT NULL DEFAULT 0,
            admin_note TEXT NULL,
            handled_by INT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT NULL,
            KEY idx_cm_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
} else {
    $log[] = ["ok", "contact_messages", "table already exists"];
}

/* ---------------------------------------------------------------------------
   2. contact_messages — columns used by admin/messages.php
      status meaning:  0 = unread   1 = read   2 = replied   3 = archived
   --------------------------------------------------------------------------- */
neo_add_col($log, $conn, 'contact_messages', 'status',     "TINYINT(1) NOT NULL DEFAULT 0");
neo_add_col($log, $conn, 'contact_messages', 'admin_note', "TEXT NULL AFTER `status`");
neo_add_col($log, $conn, 'contact_messages', 'handled_by', "INT NULL AFTER `admin_note`");
neo_add_col($log, $conn, 'contact_messages', 'updated_at', "TIMESTAMP NULL DEFAULT NULL");

/* ---------------------------------------------------------------------------
   3. index for the unread badge in the admin sidebar
   --------------------------------------------------------------------------- */
neo_add_index($log, $conn, 'contact_messages', 'idx_cm_status', '`status`');

/* ---------------------------------------------------------------------------
   4. users — SOFT DELETE support
      deleted_at IS NULL  → normal, live account
      deleted_at NOT NULL → deleted; hidden from the user list, cannot log in,
                            but every order / wallet / commission row stays
                            intact and the account can be restored any time.
   --------------------------------------------------------------------------- */
neo_add_col($log, $conn, 'users', 'deleted_at',    "TIMESTAMP NULL DEFAULT NULL");
neo_add_col($log, $conn, 'users', 'deleted_by',    "INT NULL");
neo_add_col($log, $conn, 'users', 'delete_reason', "VARCHAR(255) NULL");
neo_add_index($log, $conn, 'users', 'idx_users_deleted', '`deleted_at`');

$fails = 0;
foreach ($log as $l) { if ($l[0] === 'fail') $fails++; }
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>NEO SSP Migration — WBMCZ</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">NEO SSP Migration</h1>
        <p class="text-sm text-slate-500 mb-5">Database: <b><?php echo DB_NAME; ?></b> &middot; Contact messages inbox + user soft delete</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 neosspmigration.php from the server once this page shows &ldquo;completed successfully&rdquo;.</p>
    </div>
</body>
</html>
