<?php
/* ============================================================================
   WBMCZ — ONE MIGRATION FOR EVERYTHING  (07 Sep 2026)

   Run this once after uploading, then DELETE IT.

       https://wbmcz.online/activitmigration.php     (signed in as admin)

   It covers every change made between 05 and 07 September: the KYC-at-checkout
   work, the SMS parser, the Activity & Task ladder, the staff login, the user
   list split, the catalogue split, the dashboard and the access system.

   WHAT IT DOES
     1  checks the files that must be uploaded are actually there
     2  adds any column or table the new code needs — and only if it is missing
     3  fills in a category_key where one is blank
     4  fills in orders.category_id from the product each order names
     5  writes the per-category page files (product_*.php and service_*.php)
     6  runs the ladder once, so everybody lands on the right rung

   WHAT IT NEVER DOES
     No table is dropped. No column is dropped or retyped. No row is deleted.
     Every step checks first and does nothing when there is nothing to do, so
     running it twice is safe and the second run reports "nothing needed".
   ============================================================================ */

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

/* It writes files into admin/, so it is not left open to the world. */
if (!isLoggedIn() || !isAdmin()) {
    http_response_code(403);
    exit('<p style="font-family:system-ui;max-width:560px;margin:60px auto;line-height:1.7">'
       . 'Sign in to the admin panel first, then load this page again. '
       . '<a href="admin/login.php">Admin sign in</a></p>');
}

$conn  = $db->getConnection();
$rows  = [];      /* [ok, what, detail] */
$did   = [];      /* everything actually changed */
function say(&$rows, $ok, $what, $detail = '') { $rows[] = [$ok, $what, $detail]; }

/* ------------------------------------------------------------------ helpers */
function has_table($conn, $t) {
    try { $r = $conn->query("SHOW TABLES LIKE '" . $conn->real_escape_string($t) . "'");
          return $r && $r->num_rows > 0; } catch (\Throwable $e) { return false; }
}
function has_col($conn, $t, $c) {
    if (!has_table($conn, $t)) { return false; }
    try { $r = $conn->query("SHOW COLUMNS FROM `$t` LIKE '" . $conn->real_escape_string($c) . "'");
          return $r && $r->num_rows > 0; } catch (\Throwable $e) { return false; }
}
/** Add a column only when it is missing. Returns '' when nothing was needed. */
function add_col($conn, &$did, $t, $c, $spec) {
    if (!has_table($conn, $t)) { return "table $t is missing"; }
    if (has_col($conn, $t, $c)) { return ''; }
    try { $conn->query("ALTER TABLE `$t` ADD COLUMN `$c` $spec"); }
    catch (\Throwable $e) { return 'could not add: ' . $e->getMessage(); }
    $did[] = "$t.$c added";
    return '';
}

/* =========================================================== 1 · THE FILES */
$need = [
    /* the engines and the shared pieces */
    'includes/kyc_checkout.php'                  => 'KYC collected on the checkout page',
    'includes/kyc.php'                           => 'locks a KYC line once it is filled in',
    'includes/shares.php'                        => 'buying no longer waits for KYC approval',
    'includes/sms.php'                           => 'reads the SMS gateway reply properly',
    'includes/pipeline.php'                      => 'the Activity & Task ladder and desks',
    'includes/acl.php'                           => 'the permission registry',
    'includes/product_pages.php'                 => 'names the per-category pages',
    'admin/includes/product_engine.php'          => 'the catalogue screen',
    'admin/includes/pending_services_engine.php' => 'the seller-submission screen',
    'admin/includes/user_list_engine.php'        => 'the user list screen',
    'admin/includes/sidebar.php'                 => 'the menu',
    /* the screens */
    'admin/login.php'                            => 'the staff sign-in',
    'admin/index.php'                            => 'the dashboard',
    'admin/activity_task.php'                    => 'the Activity & Task board',
    'admin/staff.php'                            => 'Add Staff — the duplicate-field fix',
    'admin/orders.php'                           => 'order categories and the missing rows',
    'admin/service_import.php'                   => 'bulk upload, shaped per category',
    'admin/services.php'                         => 'writes a new category its pages',
    'admin/users_user.php'                       => 'the Users list',
    'admin/users_seller.php'                     => 'the Sellers list',
    'admin/users_employee.php'                   => 'the Employees list',
    'admin/users_admin.php'                      => 'the Admins list',
    'checkout.php'                               => 'the KYC form at checkout',
    'register.php'                               => 'no referral code on the done screen',
];
$missing = 0;
foreach ($need as $f => $why) {
    $there = is_file(__DIR__ . '/' . $f);
    if (!$there) { $missing++; }
    say($rows, $there, $f, $there ? $why : 'NOT UPLOADED — ' . $why);
}

/* ==================================================== 2 · COLUMNS & TABLES */
$errs = [];

/* --- Activity & Task: what happened when we spoke to them ---------------- */
$errs[] = add_col($conn, $did, 'tasks', 'outcome',    "VARCHAR(30) NULL");
$errs[] = add_col($conn, $did, 'tasks', 'outcome_at', "DATETIME NULL");
$errs[] = add_col($conn, $did, 'tasks', 'advanced',   "INT NOT NULL DEFAULT 0");

/* --- who may open which desk, chosen when work is handed over ------------ */
$errs[] = add_col($conn, $did, 'users', 'task_access', "TEXT NULL");

/* --- the staff login and the access system ------------------------------ */
$errs[] = add_col($conn, $did, 'users', 'staff_level', "VARCHAR(20) NULL");
$errs[] = add_col($conn, $did, 'users', 'perm_extra',  "TEXT NULL");
$errs[] = add_col($conn, $did, 'users', 'perm_denied', "TEXT NULL");
$errs[] = add_col($conn, $did, 'roles', 'perms_json',  "LONGTEXT NULL");
$errs[] = add_col($conn, $did, 'roles', 'level',       "VARCHAR(20) NULL");
$errs[] = add_col($conn, $did, 'roles', 'status',      "TINYINT(1) NOT NULL DEFAULT 1");
$errs[] = add_col($conn, $did, 'roles', 'description', "VARCHAR(255) NULL");
$errs[] = add_col($conn, $did, 'roles', 'created_by',      "INT NULL");
$errs[] = add_col($conn, $did, 'roles', 'created_by_name', "VARCHAR(150) NULL");
$errs[] = add_col($conn, $did, 'roles', 'updated_at',      "TIMESTAMP NULL");

/* --- orders carry the category they belong to --------------------------- */
$errs[] = add_col($conn, $did, 'orders', 'category_id', "INT NULL");

/* --- a product can ask the customer for documents ----------------------- */
$errs[] = add_col($conn, $did, 'seller_services', 'required_kyc', "VARCHAR(255) NULL");

/* --- every category needs a key: the page file names are built from it --- */
$errs[] = add_col($conn, $did, 'service_categories', 'category_key', "VARCHAR(80) NULL");

/* --- the record of every accept / hold / reject on the board ------------- */
if (!has_table($conn, 'pipe_reviews')) {
    try {
        $conn->query("CREATE TABLE pipe_reviews (
            id INT AUTO_INCREMENT PRIMARY KEY,
            kind VARCHAR(40) NOT NULL,
            ref_id INT NULL,
            user_id INT NULL,
            decision VARCHAR(20) NOT NULL,
            remark TEXT NULL,
            done_by INT NULL,
            done_by_name VARCHAR(150) NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY k_kind (kind), KEY k_ref (ref_id), KEY k_user (user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $did[] = 'pipe_reviews created';
    } catch (\Throwable $e) { $errs[] = 'pipe_reviews: ' . $e->getMessage(); }
} else {
    /* it existed before ref_id did */
    $errs[] = add_col($conn, $did, 'pipe_reviews', 'ref_id', "INT NULL");
}

/* --- cash a seller took, and what they have handed back ----------------- */
if (!has_table($conn, 'seller_collections')) {
    try {
        $conn->query("CREATE TABLE seller_collections (
            id INT AUTO_INCREMENT PRIMARY KEY,
            order_id INT NOT NULL, order_code VARCHAR(40) NULL, item_id INT NULL,
            seller_id INT NOT NULL, customer_id INT NULL, customer_name VARCHAR(150) NULL,
            amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            mode VARCHAR(20) NOT NULL DEFAULT 'cash',
            reference VARCHAR(120) NULL, note VARCHAR(500) NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            recorded_by INT NULL, recorded_by_name VARCHAR(150) NULL,
            approved_by INT NULL, approved_by_name VARCHAR(150) NULL, approved_at DATETIME NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY k_order (order_id), KEY k_seller (seller_id), KEY k_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $did[] = 'seller_collections created';
    } catch (\Throwable $e) { $errs[] = 'seller_collections: ' . $e->getMessage(); }
}
if (!has_table($conn, 'seller_settlements')) {
    try {
        $conn->query("CREATE TABLE seller_settlements (
            id INT AUTO_INCREMENT PRIMARY KEY,
            seller_id INT NOT NULL,
            direction VARCHAR(10) NOT NULL DEFAULT 'in',
            amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            mode VARCHAR(20) NOT NULL DEFAULT 'cash',
            reference VARCHAR(120) NULL, note VARCHAR(500) NULL, order_id INT NULL,
            created_by INT NULL, created_by_name VARCHAR(150) NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY k_seller (seller_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $did[] = 'seller_settlements created';
    } catch (\Throwable $e) { $errs[] = 'seller_settlements: ' . $e->getMessage(); }
}

$errs = array_values(array_filter($errs));
say($rows, empty($errs), 'Columns and tables',
    empty($errs) ? (count($did) . ' change(s) — everything else was already there')
                 : implode(' · ', $errs));

/* --- the rest of the KYC and access schema looks after itself ----------- */
try {
    require_once __DIR__ . '/includes/acl.php';
    if (function_exists('acl_ensure_schema')) { acl_ensure_schema($db); }
    say($rows, true, 'Permission tables', 'checked by acl_ensure_schema()');
} catch (\Throwable $e) { say($rows, false, 'Permission tables', $e->getMessage()); }

/* ================================================= 3 · EVERY CATEGORY KEY */
$keyed = [];
try {
    foreach ($db->select("SELECT id, category_key, title FROM service_categories") as $c) {
        if (trim((string)$c['category_key']) !== '') { continue; }
        $slug = strtolower(html_entity_decode((string)$c['title'], ENT_QUOTES, 'UTF-8'));
        $slug = trim((string)preg_replace('/[^a-z0-9]+/', '_', $slug), '_');
        if ($slug === '') { $slug = 'cat' . (int)$c['id']; }
        $db->insert("UPDATE service_categories SET category_key = ? WHERE id = ?", ['cat_' . $slug, (int)$c['id']]);
        $keyed[] = $c['title'];
    }
    say($rows, true, 'Category keys', $keyed ? 'filled in for: ' . implode(', ', $keyed) : 'nothing needed filling in');
} catch (\Throwable $e) { say($rows, false, 'Category keys', $e->getMessage()); }

/* ============================================ 4 · ORDERS FIND THEIR CATEGORY */
$before = 0; $after = 0;
try {
    $r = $db->select("SELECT COUNT(*) n FROM orders WHERE COALESCE(category_id,0) = 0");
    $before = !empty($r) ? (int)$r[0]['n'] : 0;

    /* from the order's own items first… */
    $db->insert("UPDATE orders o
                   JOIN order_items i ON i.order_id = o.id AND COALESCE(i.category_id,0) > 0
                    SET o.category_id = i.category_id
                  WHERE COALESCE(o.category_id,0) = 0");
    /* …then from the product it names */
    $db->insert("UPDATE orders o
                   JOIN seller_services s ON UPPER(TRIM(s.title)) = UPPER(TRIM(o.service_name))
                    SET o.category_id = s.category_id
                  WHERE COALESCE(o.category_id,0) = 0 AND COALESCE(s.category_id,0) > 0");

    $r = $db->select("SELECT COUNT(*) n FROM orders WHERE COALESCE(category_id,0) = 0");
    $after = !empty($r) ? (int)$r[0]['n'] : 0;
    say($rows, true, 'Orders given their category',
        ($before - $after) . ' filled in' . ($after > 0
            ? ' · ' . $after . ' left with none (their product no longer exists — they show under "No category")'
            : ' · none left over'));
} catch (\Throwable $e) { say($rows, false, 'Orders given their category', $e->getMessage()); }

/* ============================================= 5 · THE PER-CATEGORY PAGES */
$sync = ['made' => [], 'kept' => [], 'failed' => []];
try {
    require_once __DIR__ . '/includes/product_pages.php';
    if (function_exists('pmp_sync')) {
        $sync = pmp_sync($db, __DIR__ . '/admin');
        say($rows, empty($sync['failed']), 'Per-category pages',
            count($sync['made']) . ' written, ' . count($sync['kept']) . ' already there'
            . (empty($sync['failed']) ? '' : ' · COULD NOT WRITE: ' . implode(', ', $sync['failed'])
                . ' — make the admin folder writable and run this again'));
    } else {
        say($rows, false, 'Per-category pages', 'includes/product_pages.php is the old copy');
    }
} catch (\Throwable $e) { say($rows, false, 'Per-category pages', $e->getMessage()); }

/* ================================================== 6 · RUN THE LADDER ONCE */
$sy = null;
try {
    require_once __DIR__ . '/includes/pipeline.php';
    if (function_exists('pipe_sync')) {
        $sy = pipe_sync($db, true);
        say($rows, true, 'Activity & Task ladder',
            'merged ' . (int)($sy['merged'] ?? 0) . ' duplicate row(s), created '
            . (int)($sy['created'] ?? 0) . ', moved ' . (int)($sy['moved'] ?? 0) . ' to the right rung');
    } else {
        say($rows, false, 'Activity & Task ladder', 'includes/pipeline.php is the old copy');
    }
} catch (\Throwable $e) { say($rows, false, 'Activity & Task ladder', $e->getMessage()); }

/* ------------------------------------------------------------ what it looks like now */
$ladder = [];
try {
    foreach (['user', 'seller'] as $role) {
        $case = pipe_rung_case($role, true);
        foreach ($db->select("SELECT ($case) rung, COUNT(*) n FROM tasks t JOIN users u ON u.id = t.target_user_id
                               WHERE t.scope_role = ? GROUP BY 1 ORDER BY 2 DESC", [$role]) as $x) {
            $ladder[$role][(string)$x['rung']] = (int)$x['n'];
        }
    }
} catch (\Throwable $e) {}

$bad = 0;
foreach ($rows as $r) { if (!$r[0]) { $bad++; } }
?><!DOCTYPE html>
<html lang="en"><head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>WBMCZ · migration</title>
<style>
 body{font-family:system-ui,-apple-system,"Segoe UI",sans-serif;background:#f8fafc;color:#0f172a;margin:0;padding:40px 20px;line-height:1.6}
 .wrap{max-width:900px;margin:0 auto}
 h1{font-size:23px;margin:0 0 4px} .sub{color:#64748b;font-size:14px;margin:0 0 26px}
 h2{font-size:13px;text-transform:uppercase;letter-spacing:.08em;color:#94a3b8;margin:28px 0 10px}
 .card{background:#fff;border:1px solid #e2e8f0;border-radius:14px;padding:6px 20px;margin-bottom:18px}
 .row{display:flex;gap:12px;padding:11px 0;border-bottom:1px solid #f1f5f9;align-items:flex-start}
 .row:last-child{border-bottom:0}
 .tick{flex:0 0 20px;font-weight:700}.ok{color:#059669}.no{color:#dc2626}
 .what{font-weight:600;font-size:13.5px}.detail{color:#64748b;font-size:12.5px}
 .banner{border-radius:14px;padding:16px 20px;margin-bottom:22px;font-size:14px}
 .green{background:#ecfdf5;border:1px solid #a7f3d0;color:#065f46}
 .red{background:#fef2f2;border:1px solid #fecaca;color:#991b1b}
 code{background:#f1f5f9;padding:1px 6px;border-radius:4px;font-size:12.5px}
 ul{margin:8px 0 0;padding-left:20px} li{margin:4px 0;font-size:13px}
 table{width:100%;border-collapse:collapse;font-size:13px}
 td{padding:6px 0;border-bottom:1px solid #f1f5f9}
 td:last-child{text-align:right;font-weight:700}
</style></head><body><div class="wrap">

<h1>Everything from 05–07 September</h1>
<p class="sub">Run once, then delete this file.</p>

<?php if ($bad === 0): ?>
  <div class="banner green"><b>All done.</b> Every file arrived and the database has what the new code needs.
  Delete <code>activitmigration.php</code> now.</div>
<?php else: ?>
  <div class="banner red"><b><?php echo $bad; ?> thing(s) need attention.</b> Fix what is marked below and load this
  page again — running it twice changes nothing that is already right.</div>
<?php endif; ?>

<h2>Checks</h2>
<div class="card">
<?php foreach ($rows as $r): ?>
  <div class="row">
    <div class="tick <?php echo $r[0] ? 'ok' : 'no'; ?>"><?php echo $r[0] ? '&#10003;' : '&#10007;'; ?></div>
    <div>
      <div class="what"><?php echo htmlspecialchars($r[1]); ?></div>
      <?php if ($r[2] !== ''): ?><div class="detail"><?php echo htmlspecialchars($r[2]); ?></div><?php endif; ?>
    </div>
  </div>
<?php endforeach; ?>
</div>

<?php if (!empty($did)): ?>
<h2>What changed in the database</h2>
<div class="card"><div class="row"><div>
<ul><?php foreach ($did as $d): ?><li><code><?php echo htmlspecialchars($d); ?></code></li><?php endforeach; ?></ul>
</div></div></div>
<?php else: ?>
<h2>What changed in the database</h2>
<div class="card"><div class="row"><div><div class="detail">Nothing — every column and table the new code needs was
already there. No table was dropped, no column was dropped or retyped, no row was deleted.</div></div></div></div>
<?php endif; ?>

<?php if (!empty($sync['made'])): ?>
<h2>Page files written</h2>
<div class="card"><div class="row"><div>
<ul><?php foreach ($sync['made'] as $f): ?><li><code>admin/<?php echo htmlspecialchars($f); ?></code></li><?php endforeach; ?></ul>
</div></div></div>
<?php endif; ?>

<?php if (!empty($ladder)): ?>
<h2>Where everybody stands now</h2>
<div class="card"><div class="row"><div style="width:100%">
<?php foreach ($ladder as $role => $counts): ?>
  <p class="what" style="margin:10px 0 4px"><?php echo ucfirst($role); ?></p>
  <table><?php foreach ($counts as $k => $n): ?>
    <tr><td><code><?php echo htmlspecialchars($k); ?></code></td><td><?php echo number_format($n); ?></td></tr>
  <?php endforeach; ?></table>
<?php endforeach; ?>
</div></div></div>
<?php endif; ?>

<h2>Still to do by hand</h2>
<div class="card"><div class="row"><div>
<ul>
  <li>Delete this file.</li>
  <li>Delete <code>kycsmsmigration.php</code> and <code>productsplitmigration.php</code> if they are still on the
      server — this file does everything they did.</li>
  <li>On <b>Custom Roles</b>, tick the new keys you want each role to have: the per-category product pages, the
      per-desk Activity &amp; Task keys, and the dashboard blocks.</li>
  <li>To let an admin create staff, tick <b>Staff &amp; Roles → Create an employee</b> for them.</li>
  <li>The OTP text still ends with the reseller's brand until your own DLT header and templates are registered.</li>
</ul>
</div></div></div>

</div></body></html>
