<?php
/* ============================================================================
   Migration — Virtual Wallet, Wallet History, Credentials & Earning Report

   Run ONCE:  http://localhost/neo/newmigration.php   (delete the file after)

   Creates / alters:
     users               → bonus_wallet, is_subscribed, referred_by
     wallet_ledger       → NEW: every wallet movement, both wallets
     member_certificates → NEW: seller certificates
   Then backfills the ledger from the old `transactions` table and issues a
   certificate to every existing seller.
   ============================================================================ */
require_once __DIR__ . '/config.php';
require_once __DIR__ . '/db.php';
require_once __DIR__ . '/includes/wallet.php';
require_once __DIR__ . '/includes/credentials.php';

header('Content-Type: text/html; charset=utf-8');
echo "<style>body{font-family:system-ui,sans-serif;max-width:900px;margin:40px auto;padding:0 20px;line-height:1.6}
      h2{border-bottom:2px solid #6366f1;padding-bottom:8px}h3{margin-top:28px;color:#334155}
      p{margin:4px 0;font-size:14px}code{background:#f1f5f9;padding:1px 5px;border-radius:4px}</style>";
echo "<h2>Migration — Virtual Wallet, Wallet History, Credentials &amp; Earning Report</h2>";

$conn = $db->getConnection();
$ok = 0; $skip = 0; $fail = 0;

function run_q($conn, $sql, $label = '') {
    global $ok, $skip, $fail;
    // MySQL < 8 / MariaDB < 10.3 have no "ADD COLUMN IF NOT EXISTS"
    $q = (stripos(ltrim($sql), 'ALTER TABLE') === 0) ? str_ireplace(' IF NOT EXISTS', '', $sql) : $sql;
    $show = $label !== '' ? $label : substr(preg_replace('/\s+/', ' ', $q), 0, 95);
    try {
        if ($conn->query($q)) { $ok++; echo "<p style='color:#059669'>&#10003; $show</p>"; return true; }
    } catch (mysqli_sql_exception $e) {
        $m = $e->getMessage();
        if (stripos($m, 'Duplicate column') !== false || stripos($m, 'already exists') !== false || stripos($m, 'Duplicate key name') !== false) {
            $skip++; echo "<p style='color:#d97706'>&#8226; already exists — $show</p>"; return true;
        }
        $fail++; echo "<p style='color:#dc2626'>&#10007; " . htmlspecialchars($m) . "</p>"; return false;
    }
    return false;
}

/* ==========================================================================
   1. users — wallet + referral columns
   ========================================================================== */
echo "<h3>1. Table <code>users</code></h3>";
run_q($conn, "ALTER TABLE users ADD COLUMN IF NOT EXISTS bonus_wallet DECIMAL(10,2) NOT NULL DEFAULT 0.00",
      "users.bonus_wallet — the VIRTUAL wallet balance");
run_q($conn, "ALTER TABLE users ADD COLUMN IF NOT EXISTS is_subscribed TINYINT(1) NOT NULL DEFAULT 0",
      "users.is_subscribed");
run_q($conn, "ALTER TABLE users ADD COLUMN IF NOT EXISTS referred_by INT NULL DEFAULT NULL",
      "users.referred_by");
run_q($conn, "ALTER TABLE users ADD COLUMN IF NOT EXISTS pincode VARCHAR(20) NULL",
      "users.pincode");
run_q($conn, "ALTER TABLE users ADD INDEX idx_users_referred (referred_by)",
      "index on users.referred_by");

/* ==========================================================================
   2. wallet_ledger — every movement in either wallet
   ========================================================================== */
echo "<h3>2. Table <code>wallet_ledger</code> (new)</h3>";
run_q($conn, "CREATE TABLE IF NOT EXISTS wallet_ledger (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    wallet VARCHAR(10) NOT NULL DEFAULT 'main',
    direction VARCHAR(10) NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    balance_after DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    category VARCHAR(40) NOT NULL DEFAULT 'other',
    description VARCHAR(255) NULL,
    reference_type VARCHAR(30) NULL,
    reference_id INT NULL,
    counterparty_id INT NULL,
    created_by INT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_wl_user (user_id, created_at),
    KEY idx_wl_wallet (wallet),
    KEY idx_wl_cat (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
"wallet_ledger — wallet: main|virtual, direction: credit|debit, category: deposit|withdrawal|purchase|referral|seller_share|staff_share|subscription|refund|admin|bonus");

/* ==========================================================================
   3. member_certificates — seller certificates
   ========================================================================== */
echo "<h3>3. Table <code>member_certificates</code> (new)</h3>";
run_q($conn, "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",
"member_certificates — one row per issued certificate");

/* ==========================================================================
   4. Backfill the ledger from the old `transactions` table
   ========================================================================== */
echo "<h3>4. Backfill wallet history</h3>";
$n = wallet_backfill_from_transactions($db);
if ($n > 0) {
    echo "<p style='color:#059669'>&#10003; Imported <b>$n</b> old transaction(s) into <code>wallet_ledger</code> (all as MAIN wallet, category auto-detected from the description).</p>";
} else {
    echo "<p style='color:#d97706'>&#8226; Skipped — the ledger already contains rows, so nothing was imported (this is intentional and safe).</p>";
}

/* ==========================================================================
   5. Issue certificates to every existing seller
   ========================================================================== */
echo "<h3>5. Issue certificates to existing sellers</h3>";
$issued = 0; $had = 0;
try {
    $sellers = $db->select("SELECT id, name, user_type FROM users WHERE user_type = 'seller'");
    foreach ($sellers as $s) {
        $before = $db->select("SELECT id FROM member_certificates WHERE user_id = ? AND cert_type = 'seller'", [$s['id']]);
        $c = cred_get_certificate($db, $s, true);
        if (!empty($before)) { $had++; }
        elseif ($c) { $issued++; echo "<p style='color:#059669'>&#10003; {$c['cert_no']} &rarr; " . htmlspecialchars($s['name']) . "</p>"; }
    }
    echo "<p><b>$issued new certificate(s) issued</b>, $had seller(s) already had one.</p>";
} catch (\Throwable $e) {
    echo "<p style='color:#dc2626'>&#10007; " . htmlspecialchars($e->getMessage()) . "</p>";
}

/* ==========================================================================
   6. Sanity check
   ========================================================================== */
echo "<h3>6. Verification</h3>";
foreach ([
    "SELECT COUNT(*) n FROM wallet_ledger"                                   => 'wallet_ledger rows',
    "SELECT COUNT(*) n FROM member_certificates"                             => 'certificates issued',
    "SELECT COUNT(*) n FROM users WHERE bonus_wallet > 0"                    => 'users holding virtual balance',
    "SELECT COALESCE(SUM(wallet_balance),0) n FROM users"                    => 'total MAIN wallet balance',
    "SELECT COALESCE(SUM(bonus_wallet),0) n FROM users"                      => 'total VIRTUAL wallet balance',
] as $sql => $label) {
    try {
        $r = $db->select($sql);
        echo "<p>&bull; $label: <b>" . (!empty($r) ? $r[0]['n'] : 0) . "</b></p>";
    } catch (\Throwable $e) {
        echo "<p style='color:#dc2626'>&#10007; $label — " . htmlspecialchars($e->getMessage()) . "</p>";
    }
}

echo "<hr><p><b>Done — $ok statement(s) ran, $skip already existed, $fail failed.</b></p>";
echo "<p style='color:#dc2626;font-weight:bold'>Please delete newmigration.php now for security.</p>";
