<?php
/* ============================================================================
   WBMCZ — MIGRATION, 5 September 2026
   Run once on the live server:  https://wbmcz.online/kycsmsmigration.php
   Then DELETE THIS FILE.

   Two pieces of work went out today:

     A. KYC is collected on the checkout page instead of blocking the product
        card with a "KYC Required" button.
     B. SMS finally leaves the building — the gateway's reply is read properly,
        and the wording matches the template the operator has approved.

   NEITHER NEEDS A NEW TABLE OR A NEW COLUMN. Everything the new code writes to
   already exists. This file is here to PROVE that on your live database rather
   than assume it: it checks every table and every column the new code touches,
   creates anything a very old database happens to be missing, makes sure the
   two upload folders exist and can be written to, and then sets the SMS
   settings that turn the OTP on.

   It is safe to run twice. Nothing already correct is touched, and nothing you
   have deliberately set differently is overwritten — every decision is printed
   with its reason.
   ============================================================================ */

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

$rows = [];
function say($kind, $what, $detail = '') { global $rows; $rows[] = [$kind, $what, $detail]; }

$conn = $db->getConnection();

function tbl_exists($conn, $t) {
    try { $r = $conn->query("SHOW TABLES LIKE '" . $t . "'"); return $r && $r->num_rows > 0; }
    catch (\Throwable $e) { return false; }
}
function col_exists($conn, $t, $c) {
    try { $r = $conn->query("SHOW COLUMNS FROM `$t` LIKE '" . $c . "'"); return $r && $r->num_rows > 0; }
    catch (\Throwable $e) { return false; }
}

/* ============================================================================
   0 · The files themselves
   ============================================================================ */
$needed = [
    'includes/kyc_checkout.php' => 'NEW — the checkout KYC engine',
    'includes/kyc.php'          => 'the KYC lock',
    'includes/kyc_fields.php'   => 'field-level KYC requirements',
    'includes/shares.php'       => 'the purchase gate',
    'includes/sms.php'          => 'the gateway reply reader',
    'includes/pipeline.php'     => 'NEW — the activity pipeline',
    'admin/activity_task.php'   => 'the new Activity & Task screen',
    'admin/sms_settings.php'    => 'the SMS admin screen',
    'checkout.php'              => 'the checkout page',
];
foreach ($needed as $f => $why) {
    if (@is_file(__DIR__ . '/' . $f)) { say('ok', $f, $why); }
    else { say('fail', $f, 'NOT UPLOADED — ' . $why . '. Upload it, then run this page again.'); }
}

$can_go = true;
foreach ($rows as $r) { if ($r[0] === 'fail') { $can_go = false; } }

if ($can_go) {

require_once __DIR__ . '/includes/kyc.php';
require_once __DIR__ . '/includes/kyc_fields.php';
require_once __DIR__ . '/includes/sms.php';

/* ============================================================================
   1 · Tables — created on demand by the code, verified here
   ============================================================================ */
kyc_ensure_schema($db);
kycf_ensure_schema($db);
sms_ensure_schema($db);

foreach (['kyc_details'       => 'the KYC record itself',
          'kyc_custom_fields' => "the admin's own KYC questions",
          'kyc_custom_values' => 'the answers members give to them',
          'otp_codes'         => 'the one-time codes',
          'sms_log'           => 'every text that left the site'] as $t => $why) {
    if (tbl_exists($conn, $t)) { say('ok', 'table ' . $t, $why); }
    else { say('fail', 'table ' . $t, 'missing and could not be created — check the database user permissions'); }
}

/* ============================================================================
   2 · Every column the checkout form writes to
   ============================================================================ */
$kyc_cols = [
    'full_name' => 'VARCHAR(150) NULL', 'gender' => 'VARCHAR(20) NULL', 'dob' => 'VARCHAR(20) NULL',
    'father_name' => 'VARCHAR(150) NULL', 'email' => 'VARCHAR(150) NULL', 'phone_number' => 'VARCHAR(30) NULL',
    'business_name_personal' => 'VARCHAR(200) NULL',
    'aadhar_number' => 'VARCHAR(30) NULL', 'pancard_number' => 'VARCHAR(30) NULL',
    'aadhar_front' => 'VARCHAR(255) NULL', 'aadhar_back' => 'VARCHAR(255) NULL',
    'pan_card' => 'VARCHAR(255) NULL', 'photo' => 'VARCHAR(255) NULL',
    'village' => 'VARCHAR(150) NULL', 'post_office' => 'VARCHAR(150) NULL', 'block_city' => 'VARCHAR(150) NULL',
    'landmark' => 'VARCHAR(255) NULL', 'district' => 'VARCHAR(150) NULL', 'state' => 'VARCHAR(150) NULL',
    'pincode' => 'VARCHAR(20) NULL', 'address_proof' => 'VARCHAR(255) NULL',
    'bank_name' => 'VARCHAR(150) NULL', 'branch_name' => 'VARCHAR(150) NULL',
    'account_number' => 'VARCHAR(50) NULL', 'ifsc_code' => 'VARCHAR(30) NULL', 'bank_passbook' => 'VARCHAR(255) NULL',
    'business_name' => 'VARCHAR(200) NULL', 'business_phone' => 'VARCHAR(30) NULL', 'business_email' => 'VARCHAR(150) NULL',
    'same_as_personal' => 'TINYINT(1) NOT NULL DEFAULT 0',
    'biz_village' => 'VARCHAR(150) NULL', 'biz_post_office' => 'VARCHAR(150) NULL', 'biz_block_city' => 'VARCHAR(150) NULL',
    'biz_landmark' => 'VARCHAR(255) NULL', 'biz_district' => 'VARCHAR(150) NULL', 'biz_state' => 'VARCHAR(150) NULL',
    'biz_pincode' => 'VARCHAR(20) NULL', 'business_proof' => 'VARCHAR(255) NULL',
    'admin_remark' => 'TEXT NULL', 'updated_at' => 'TIMESTAMP NULL DEFAULT NULL',
];
$added = 0; $present = 0;
foreach ($kyc_cols as $c => $def) {
    if (col_exists($conn, 'kyc_details', $c)) { $present++; continue; }
    try { $conn->query("ALTER TABLE kyc_details ADD COLUMN `$c` $def"); $added++;
          say('add', 'kyc_details.' . $c, 'was missing on this database — added as ' . $def); }
    catch (\Throwable $e) { say('fail', 'kyc_details.' . $c, 'could not be added: ' . $e->getMessage()); }
}
say($added === 0 ? 'ok' : 'add', 'kyc_details columns',
    $added === 0 ? "all $present already there — nothing to change" : "$present already there, $added added");

foreach (['kyc_custom_values' => ['user_id', 'field_id', 'value', 'file_path'],
          'sms_log'           => ['mobile', 'message', 'purpose', 'status', 'msg_id', 'response'],
          'otp_codes'         => ['purpose', 'mobile', 'otp_hash', 'otp_plain', 'payload', 'attempts', 'expires_at']] as $t => $cols) {
    $miss = [];
    foreach ($cols as $c) { if (!col_exists($conn, $t, $c)) { $miss[] = $c; } }
    if (empty($miss)) { say('ok', $t . ' columns', 'all present'); }
    else { say('fail', $t . ' columns', 'missing: ' . implode(', ', $miss)); }
}

/* ============================================================================
   2b · The activity pipeline
   ----------------------------------------------------------------------------
   Two columns on `tasks`, one on `users`, and one small table for review
   decisions. All four are created on demand by includes/pipeline.php — this
   only proves they landed.
   ============================================================================ */
if (@is_file(__DIR__ . '/includes/pipeline.php')) {
    require_once __DIR__ . '/includes/pipeline.php';
    pipe_ensure_schema($db);

    foreach ([['tasks', 'outcome'], ['tasks', 'outcome_at'], ['users', 'task_access']] as $c) {
        if (col_exists($conn, $c[0], $c[1])) { say('ok', $c[0] . '.' . $c[1], 'present'); }
        else { say('fail', $c[0] . '.' . $c[1], 'could not be added — check the database user permissions'); }
    }
    if (tbl_exists($conn, 'pipe_reviews')) { say('ok', 'table pipe_reviews', 'KYC accept / hold / reject decisions'); }
    else { say('fail', 'table pipe_reviews', 'missing and could not be created'); }

    /* First fill of the line, so the screen is not empty on the first visit. */
    $r = pipe_sync($db, true);
    say('add', 'the activity line', $r['created'] . ' step(s) opened, ' . $r['completed'] . ' completed on their own');
} else {
    say('fail', 'includes/pipeline.php', 'NOT UPLOADED — the new Activity & Task screen needs it');
}

/* ============================================================================
   3 · The two folders the checkout form uploads into
   ============================================================================ */
foreach (['uploads/kyc' => 'KYC photos taken at checkout',
          'uploads/kyc_extra' => "answers to the admin's own file questions"] as $d => $why) {
    $path = __DIR__ . '/' . $d;
    if (!is_dir($path)) { @mkdir($path, 0755, true); }
    if (!is_dir($path))          { say('fail', $d . '/', 'could not be created — make it by hand and chmod it to 755'); }
    elseif (!is_writable($path)) { say('fail', $d . '/', 'exists but is not writable — chmod it to 755'); }
    else                         { say('ok', $d . '/', $why); }
}

/* ============================================================================
   4 · SMS settings
   Read straight from the table, because sms_cfg() caches for the request.
   ============================================================================ */
$S = [];
try { foreach ($db->select("SELECT setting_key, setting_value FROM settings") as $r) { $S[$r['setting_key']] = (string)$r['setting_value']; } }
catch (\Throwable $e) {}

$set = function ($k, $v) use ($db, &$S) {
    try {
        $has = $db->select("SELECT id FROM settings WHERE setting_key = ?", [$k]);
        if (!empty($has)) { $db->insert("UPDATE settings SET setting_value = ? WHERE setting_key = ?", [(string)$v, $k]); }
        else { $db->insert("INSERT INTO settings (setting_key, setting_value) VALUES (?, ?)", [$k, (string)$v]); }
        $S[$k] = (string)$v;
        return true;
    } catch (\Throwable $e) { return false; }
};

/* the wording approved under this account's header */
$DLT_REGISTER = '{otp} is the OTP for {company}. Please do not share this OTP with anyone. This SMS has been sent from GuestRAR.';
$DLT_RESET    = '{otp} is the OTP for {company} password reset. Please do not share this OTP with anyone. This SMS has been sent from GuestRAR.';

/* the generic wording the first migration wrote — safe to replace */
$OLD_REGISTER = '{otp} is your OTP to verify your mobile number at {company}. Valid for {minutes} minutes. Do not share it with anyone.';
$OLD_RESET    = '{otp} is your OTP to reset your {company} password. Valid for {minutes} minutes. Do not share it with anyone.';

/* -- sender id: filled in only when it is still empty ------------------------ */
if (trim((string)($S['sms_sender_id'] ?? '')) === '') {
    $set('sms_sender_id', 'GUERAR');
    say('add', 'sms_sender_id', 'was empty — set to GUERAR, the header approved on this account');
} else {
    say('skip', 'sms_sender_id', 'already set to "' . htmlspecialchars($S['sms_sender_id']) . '" — left alone');
}

/* -- the two OTP templates --------------------------------------------------- */
$cur_reg = trim((string)($S['sms_tpl_otp_register'] ?? ''));
if ($cur_reg === $DLT_REGISTER) {
    say('ok', 'sms_tpl_otp_register', 'already the approved wording');
} elseif ($cur_reg === '' || $cur_reg === $OLD_REGISTER) {
    $set('sms_tpl_otp_register', $DLT_REGISTER);
    say('add', 'sms_tpl_otp_register', 'replaced the generic wording with the approved template');
} else {
    say('skip', 'sms_tpl_otp_register', 'you have your own wording here — left alone. Change it from Admin → SMS & OTP if texts stop arriving.');
}

$cur_res = trim((string)($S['sms_tpl_otp_reset'] ?? ''));
if ($cur_res === $DLT_RESET) {
    say('ok', 'sms_tpl_otp_reset', 'already the approved wording');
} elseif ($cur_res === '' || $cur_res === $OLD_RESET) {
    $set('sms_tpl_otp_reset', $DLT_RESET);
    say('add', 'sms_tpl_otp_reset', 'replaced the generic wording with the approved template');
} else {
    say('skip', 'sms_tpl_otp_reset', 'you have your own wording here — left alone');
}

/* -- welcome SMS: no approved template of its own ---------------------------- */
if ((string)($S['sms_welcome_enabled'] ?? '') === '1') {
    $set('sms_welcome_enabled', '0');
    say('add', 'sms_welcome_enabled', 'switched off — it has no approved DLT template, so it would be billed and dropped');
} else {
    say('ok', 'sms_welcome_enabled', 'already off');
}

/* -- go live ----------------------------------------------------------------- */
$ready = trim((string)($S['sms_username'] ?? '')) !== ''
      && trim((string)($S['sms_apikey'] ?? ''))   !== ''
      && trim((string)($S['sms_sender_id'] ?? '')) !== '';

if (!$ready) {
    say('fail', 'gateway details', 'username, API key or sender ID is still empty — fill them in at Admin → Website Settings → SMS & OTP');
} else {
    if ((string)($S['sms_enabled'] ?? '') !== '1')   { $set('sms_enabled', '1');   say('add', 'sms_enabled', 'switched on'); }
    else                                             { say('ok', 'sms_enabled', 'already on'); }
    if ((string)($S['sms_test_mode'] ?? '') === '1') { $set('sms_test_mode', '0'); say('add', 'sms_test_mode', 'switched off — real texts go out now'); }
    else                                             { say('ok', 'sms_test_mode', 'already off'); }
}
if ((string)($S['sms_type'] ?? '') === '') { $set('sms_type', 'TRANS'); say('add', 'sms_type', 'set to TRANS'); }

} /* end $can_go */

$fails = 0; $changes = 0;
foreach ($rows as $r) { if ($r[0] === 'fail') $fails++; if ($r[0] === 'add') $changes++; }
?>
<!DOCTYPE html>
<html lang="en"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>WBMCZ migration — 5 Sep 2026</title>
<style>
 body{margin:0;background:#f6f8fb;color:#0f172a;font:14px/1.55 system-ui,-apple-system,Segoe UI,Roboto,sans-serif;padding:30px 16px}
 .w{max-width:900px;margin:0 auto}
 h1{font-size:21px;margin:0 0 4px} p.s{color:#64748b;margin:0 0 22px}
 .card{background:#fff;border:1px solid #e2e8f0;border-radius:16px;padding:18px 20px;margin-bottom:16px;box-shadow:0 10px 30px -24px rgba(15,23,42,.6)}
 table{width:100%;border-collapse:collapse;font-size:13px}
 td{padding:8px 6px;border-bottom:1px solid #eef2f7;vertical-align:top}
 td:first-child{width:78px}
 .t{display:inline-block;padding:2px 9px;border-radius:999px;font-size:10.5px;font-weight:800;letter-spacing:.03em}
 .t-ok{background:#dcfce7;color:#166534}.t-add{background:#dbeafe;color:#1e40af}
 .t-skip{background:#e2e8f0;color:#475569}.t-fail{background:#fee2e2;color:#991b1b}
 .n{border-radius:12px;padding:14px 16px;margin-bottom:16px;font-size:13.5px}
 .n-ok{background:#ecfdf5;border:1px solid #a7f3d0;color:#065f46}
 .n-bad{background:#fef2f2;border:1px solid #fecaca;color:#991b1b}
 code{background:#f1f5f9;padding:2px 6px;border-radius:5px;font-size:12.5px}
 b.k{font-weight:700}
</style></head><body><div class="w">

<h1>WBMCZ migration — 5 September 2026</h1>
<p class="s">KYC at checkout, SMS that actually arrives, and the activity line that runs itself.</p>

<?php if ($fails === 0): ?>
  <div class="n n-ok"><b>Migration completed successfully.</b>
  <?php echo $changes; ?> thing<?php echo $changes === 1 ? '' : 's'; ?> changed, everything else was already in order.
  <b>Now delete <code>kycsmsmigration.php</code> from the server.</b></div>
<?php else: ?>
  <div class="n n-bad"><b><?php echo $fails; ?> problem<?php echo $fails === 1 ? '' : 's'; ?> to sort out.</b>
  Read the red rows below, fix them, and open this page again — it is safe to run as many times as you like.</div>
<?php endif; ?>

<div class="card">
<table>
<?php foreach ($rows as $r): ?>
  <tr>
    <td><span class="t t-<?php echo $r[0]; ?>"><?php
        echo ['ok' => 'in order', 'add' => 'changed', 'skip' => 'left alone', 'fail' => 'problem'][$r[0]]; ?></span></td>
    <td><b class="k"><?php echo htmlspecialchars($r[1]); ?></b><?php
        echo $r[2] !== '' ? '<br><span style="color:#64748b">' . $r[2] . '</span>' : ''; ?></td>
  </tr>
<?php endforeach; ?>
</table>
</div>

<div class="card">
  <p style="margin:0 0 8px;font-weight:800">What actually changed in the database</p>
  <p style="margin:0;color:#64748b;font-size:13px">
    The KYC and SMS work needed <b>nothing</b> — every column it writes to already existed, and the long
    check above is a proof rather than a change. The activity pipeline adds three columns
    (<code>tasks.outcome</code>, <code>tasks.outcome_at</code>, <code>users.task_access</code>) and one small
    table (<code>pipe_reviews</code>), then fills the line for the first time. The SMS settings this page
    sets can be changed any time from <b>Admin → Website Settings → SMS &amp; OTP</b>.
  </p>
</div>

<div class="card">
  <p style="margin:0 0 8px;font-weight:800">After this</p>
  <p style="margin:0;color:#64748b;font-size:13px">
    Delete this file. Then open <b>Admin → Website Settings → SMS &amp; OTP</b>, press <b>Send a test SMS</b> to your
    own number, and buy one KYC-gated service as a member to see the verification form on the checkout page.
  </p>
</div>

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