<?php
/* ============================================================================
   Migration — Wallet to Bank (withdrawal system)

   Run ONCE:  https://yourdomain.com/wallettomigration.php   (delete after)

   Creates:
     bank_accounts        → one verified bank account per user
     withdrawal_requests  → every wallet-to-bank transfer
     settings rows        → withdrawal limits and charges
     uploads/bank/        → folder for passbook / cheque images
   ============================================================================ */
require_once __DIR__ . '/config.php';
require_once __DIR__ . '/db.php';
require_once __DIR__ . '/includes/bank.php';

header('Content-Type: text/html; charset=utf-8');
?><!DOCTYPE html><html><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>Wallet to Bank — Migration</title>
<style>
 body{font-family:system-ui,-apple-system,sans-serif;max-width:900px;margin:36px auto;padding:0 20px;color:#0f172a;line-height:1.65}
 h2{border-bottom:3px solid #6366f1;padding-bottom:10px}
 h3{margin-top:32px;color:#334155;border-left:4px solid #6366f1;padding-left:10px}
 p{margin:4px 0;font-size:14px} code{background:#f1f5f9;padding:2px 6px;border-radius:4px;font-size:12.5px}
 table{width:100%;border-collapse:collapse;margin:10px 0;font-size:13px}
 th,td{padding:7px 10px;border-bottom:1px solid #e2e8f0;text-align:left}
 th{background:#f8fafc;font-size:11px;text-transform:uppercase;letter-spacing:.05em;color:#64748b}
 .box{padding:16px 20px;border-radius:12px;margin:22px 0;font-size:14px}
 .ok{background:#ecfdf5;border:1px solid #a7f3d0;color:#065f46}
 .bad{background:#fef2f2;border:1px solid #fecaca;color:#991b1b}
 @media(max-width:640px){body{margin:20px auto}th,td{padding:6px}}
</style></head><body>
<h2>Migration — Wallet to Bank</h2>
<?php

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

function q($conn, $sql, $label) {
    global $ok, $skip, $fail;
    $s = (stripos(ltrim($sql), 'ALTER TABLE') === 0) ? str_ireplace(' IF NOT EXISTS', '', $sql) : $sql;
    try {
        if ($conn->query($s)) { $ok++; echo "<p style='color:#059669'>&#10003; $label</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 — $label</p>"; return true;
        }
        $fail++; echo "<p style='color:#dc2626'>&#10007; " . htmlspecialchars($m) . "</p>";
    }
    return false;
}

/* ==========================================================================
   1. bank_accounts
   ========================================================================== */
echo "<h3>1. Table <code>bank_accounts</code> (new)</h3>";
q($conn, "CREATE TABLE IF NOT EXISTS bank_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    holder_name VARCHAR(150) NOT NULL,
    bank_name VARCHAR(150) NOT NULL,
    branch_name VARCHAR(150) NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    confirm_account VARCHAR(50) NULL,
    ifsc_code VARCHAR(30) NOT NULL,
    account_type VARCHAR(20) NOT NULL DEFAULT 'savings',
    mobile VARCHAR(20) NULL,
    upi_id VARCHAR(120) NULL,
    passbook VARCHAR(255) NULL,
    status TINYINT(1) NOT NULL DEFAULT 0,
    admin_remark TEXT NULL,
    reviewed_by INT NULL,
    reviewed_by_name VARCHAR(120) NULL,
    reviewed_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    UNIQUE KEY uniq_bank_user (user_id),
    KEY idx_bank_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
"bank_accounts — status: 0 = pending, 1 = verified, 2 = rejected");

/* ==========================================================================
   2. withdrawal_requests
   ========================================================================== */
echo "<h3>2. Table <code>withdrawal_requests</code> (new)</h3>";
q($conn, "CREATE TABLE IF NOT EXISTS withdrawal_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    bank_account_id INT NULL,
    reference_no VARCHAR(40) NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    charge DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    net_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    bank_name VARCHAR(150) NULL,
    account_number VARCHAR(50) NULL,
    ifsc_code VARCHAR(30) NULL,
    holder_name VARCHAR(150) NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    utr_number VARCHAR(60) NULL,
    admin_remark TEXT NULL,
    reviewed_by INT NULL,
    reviewed_by_name VARCHAR(120) NULL,
    reviewed_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_wd_ref (reference_no),
    KEY idx_wd_user (user_id, created_at),
    KEY idx_wd_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
"withdrawal_requests — status: pending | processing | approved | rejected");

/* ==========================================================================
   3. Dependencies (created by newmigration.php, re-checked here)
   ========================================================================== */
echo "<h3>3. Dependencies</h3>";
q($conn, "ALTER TABLE users ADD COLUMN IF NOT EXISTS bonus_wallet DECIMAL(10,2) NOT NULL DEFAULT 0.00", "users.bonus_wallet");
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");

/* ==========================================================================
   4. Settings
   ========================================================================== */
echo "<h3>4. Withdrawal settings</h3>";
echo "<table><tr><th>Key</th><th>Value</th><th>Meaning</th></tr>";
$meaning = [
    'withdraw_enabled'      => '1 = users can request, 0 = turned off',
    'withdraw_min'          => 'Minimum amount per request (₹)',
    'withdraw_max'          => 'Maximum per request (₹), 0 = no limit',
    'withdraw_charge_type'  => 'none | percent | fixed',
    'withdraw_charge_value' => 'Percent value or fixed rupees',
    'withdraw_note'         => 'Message shown to users',
];
foreach (bank_default_settings() as $k => $v) {
    try {
        $r = $db->select("SELECT setting_value FROM settings WHERE setting_key = ?", [$k]);
        if (empty($r)) {
            $db->insert("INSERT INTO settings (setting_key, setting_value, setting_type) VALUES (?, ?, 'text')", [$k, $v]);
            $cur = $v; $note = '<span style="color:#059669">created</span>';
        } else { $cur = $r[0]['setting_value']; $note = '<span style="color:#d97706">kept existing</span>'; }
        echo "<tr><td><code>$k</code></td><td><b>" . htmlspecialchars($cur) . "</b> $note</td><td>" . ($meaning[$k] ?? '') . "</td></tr>";
    } catch (\Throwable $e) {
        echo "<tr><td><code>$k</code></td><td colspan='2' style='color:#dc2626'>" . htmlspecialchars($e->getMessage()) . "</td></tr>";
    }
}
echo "</table>";
echo "<p style='font-size:13px;color:#64748b'>All of these can be changed later from <b>Admin → Wallet Management → Transaction Update → Settings</b>.</p>";

/* ==========================================================================
   5. Upload folder
   ========================================================================== */
echo "<h3>5. Upload folder</h3>";
$dir = __DIR__ . '/uploads/bank/';
if (!is_dir($dir)) {
    if (@mkdir($dir, 0755, true)) { echo "<p style='color:#059669'>&#10003; Created <code>uploads/bank/</code></p>"; $ok++; }
    else { echo "<p style='color:#dc2626'>&#10007; Could not create <code>uploads/bank/</code> — create it manually and set permission 755.</p>"; $fail++; }
} else {
    echo "<p style='color:#d97706'>&#8226; <code>uploads/bank/</code> already exists</p>"; $skip++;
}
if (is_dir($dir) && !is_writable($dir)) {
    echo "<p style='color:#dc2626'>&#10007; <code>uploads/bank/</code> is not writable — set permission to 755 or 775.</p>"; $fail++;
}

/* ==========================================================================
   6. Verification
   ========================================================================== */
echo "<h3>6. Verification</h3><table><tr><th>Check</th><th>Result</th></tr>";
foreach ([
    "SELECT COUNT(*) n FROM bank_accounts"                          => 'bank accounts on file',
    "SELECT COUNT(*) n FROM bank_accounts WHERE status = 0"         => 'pending verification',
    "SELECT COUNT(*) n FROM bank_accounts WHERE status = 1"         => 'verified',
    "SELECT COUNT(*) n FROM withdrawal_requests"                    => 'withdrawal requests',
    "SELECT COUNT(*) n FROM withdrawal_requests WHERE status='pending'" => 'awaiting payout',
    "SELECT COUNT(*) n FROM wallet_ledger"                          => 'wallet ledger rows',
    "SELECT COALESCE(SUM(wallet_balance),0) n FROM users"           => 'total withdrawable (main wallets)',
] as $sql => $label) {
    try { $r = $db->select($sql); echo "<tr><td>$label</td><td><b>" . (!empty($r) ? $r[0]['n'] : 0) . "</b></td></tr>"; }
    catch (\Throwable $e) { echo "<tr><td>$label</td><td style='color:#dc2626'>" . htmlspecialchars($e->getMessage()) . "</td></tr>"; }
}
echo "</table>";

/* How many users already have bank details in KYC → their form will be pre-filled */
try {
    $r = $db->select("SELECT COUNT(*) n FROM kyc_details WHERE account_number IS NOT NULL AND account_number <> '' AND ifsc_code IS NOT NULL AND ifsc_code <> ''");
    $n = !empty($r) ? (int)$r[0]['n'] : 0;
    echo "<p style='color:#059669'>&#10003; <b>$n</b> user(s) already have bank details in their KYC — their Wallet to Bank form will open pre-filled.</p>";
} catch (\Throwable $e) {}
?>

<?php if ($fail === 0): ?>
<div class="box ok"><b>&#10003; Migration complete</b> — <?php echo $ok; ?> ran, <?php echo $skip; ?> already existed.<br><br>
<b>Next:</b><br>
1. Users: <b>Wallet → Wallet to Bank</b> — add bank details (auto-filled from KYC when available).<br>
2. Admin: <b>Wallet Management → Bank Request Pending</b> — verify the account.<br>
3. User requests a withdrawal → money leaves the main wallet, status shows <b>Pending</b>.<br>
4. Admin: <b>Wallet Management → Transaction Update</b> — transfer the money, enter the UTR, mark as sent.<br>
Rejecting a request refunds the full amount to the user's wallet automatically.</div>
<?php else: ?>
<div class="box bad"><b>&#10007; <?php echo $fail; ?> step(s) failed</b> — see the red lines above.</div>
<?php endif; ?>

<p style="color:#dc2626;font-weight:700;margin-top:26px">Delete wallettomigration.php from the server now.</p>
</body></html>
