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

   What it does
     1. kyc_details  → adds  phone_number, business_name_personal, same_as_personal
     2. NEW TABLE    → website_requests   (users request a new website / portal
                       to be made live inside a service category)
     3. NEW TABLE    → member_certificates (safety net, if not created yet)
     4. users        → adds  call_number / whatsapp_number if they are missing
                       (used by Refer & Earn contact buttons)

   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 ssp_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 ssp_table_exists($conn, $table) {
    try {
        $r = $conn->query("SHOW TABLES LIKE '$table'");
        return $r && $r->num_rows > 0;
    } catch (\Throwable $e) { return false; }
}
function ssp_add_col(&$log, $conn, $table, $col, $def) {
    if (!ssp_table_exists($conn, $table)) { $log[] = ["skip", "$table.$col", "table does not exist"]; return; }
    if (ssp_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 ssp_run(&$log, $conn, $label, $sql) {
    try { $conn->query($sql); $log[] = ["ok", $label, "executed"]; }
    catch (\Throwable $e) { $log[] = ["fail", $label, $e->getMessage()]; }
}

/* ---------------------------------------------------------------------------
   1. kyc_details — new columns used by the 4-step KYC wizard
   --------------------------------------------------------------------------- */
if (!ssp_table_exists($conn, 'kyc_details')) {
    ssp_run($log, $conn, 'CREATE kyc_details', "
        CREATE TABLE IF NOT EXISTS kyc_details (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL UNIQUE,
            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,
            status ENUM('pending','approved','rejected') DEFAULT 'pending',
            admin_remark TEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
} else {
    ssp_add_col($log, $conn, 'kyc_details', 'phone_number',           "VARCHAR(30) NULL AFTER `email`");
    ssp_add_col($log, $conn, 'kyc_details', 'business_name_personal', "VARCHAR(200) NULL AFTER `phone_number`");
    ssp_add_col($log, $conn, 'kyc_details', 'same_as_personal',       "TINYINT(1) NOT NULL DEFAULT 0");
}

/* ---------------------------------------------------------------------------
   2. website_requests — "request another website to be made live"
   --------------------------------------------------------------------------- */
ssp_run($log, $conn, 'CREATE website_requests', "
    CREATE TABLE IF NOT EXISTS website_requests (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        category_id INT NULL,
        sub_category_id INT NULL,
        website_name VARCHAR(200) NOT NULL,
        website_url VARCHAR(255) NULL,
        contact_number VARCHAR(30) NULL,
        reason TEXT NULL,
        status VARCHAR(20) NOT NULL DEFAULT 'Pending',
        admin_remark TEXT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT NULL,
        KEY idx_wr_user (user_id),
        KEY idx_wr_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

/* ---------------------------------------------------------------------------
   3. member_certificates — safety net (normally auto-created)
   --------------------------------------------------------------------------- */
ssp_run($log, $conn, 'CREATE member_certificates', "
    CREATE TABLE IF NOT EXISTS member_certificates (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        cert_no VARCHAR(40) NOT NULL,
        cert_type VARCHAR(20) NOT NULL DEFAULT 'seller',
        holder_name VARCHAR(150) NULL,
        business_name VARCHAR(200) NULL,
        issued_on DATE NOT NULL,
        valid_till DATE NULL,
        status TINYINT(1) NOT NULL DEFAULT 1,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uniq_cert (cert_no),
        KEY idx_cert_user (user_id, cert_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

/* ---------------------------------------------------------------------------
   4. users — contact columns used by Refer & Earn
   --------------------------------------------------------------------------- */
ssp_add_col($log, $conn, 'users', 'call_number',     "VARCHAR(30) NULL");
ssp_add_col($log, $conn, 'users', 'whatsapp_number', "VARCHAR(30) NULL");

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