<?php
/* ============================================================================
   Migration — Share Amount Mode, Pending Shares, Notifications, KYC & Filters

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

   Adds:
     seller_services / agency_services → share_mode + 5 rupee-amount columns
     notifications                     → link, type, read_at, created_by
   Then backfills the rupee columns from the existing percentages.
   ============================================================================ */
require_once __DIR__ . '/config.php';
require_once __DIR__ . '/db.php';
require_once __DIR__ . '/includes/shares.php';
require_once __DIR__ . '/includes/notify.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>Share Mode &amp; Notifications — Migration</title>
<style>
 body{font-family:system-ui,-apple-system,sans-serif;max-width:920px;margin:36px auto;padding:0 20px;color:#0f172a;line-height:1.65}
 h2{border-bottom:3px solid #6366f1;padding-bottom:10px} h3{margin-top:30px;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 — Share Amount Mode, Pending Shares &amp; Notifications</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. Share mode + rupee columns
   ========================================================================== */
echo "<h3>1. Share mode columns</h3>";
$amt_cols = [
    'seller_share_amt'          => 'Seller share in rupees',
    'subscription_discount_amt' => 'Subscriber discount in rupees',
    'referral_share_amt'        => 'Referral share in rupees',
    'staff_share_amt'           => 'Staff share in rupees',
    'company_share_amt'         => 'Company share in rupees (auto)',
];
foreach (['seller_services', 'agency_services'] as $t) {
    q($conn, "ALTER TABLE $t ADD COLUMN IF NOT EXISTS share_mode VARCHAR(10) NOT NULL DEFAULT 'percent'",
       "<code>$t.share_mode</code> — 'percent' or 'amount'");
    foreach ($amt_cols as $c => $lbl) {
        q($conn, "ALTER TABLE $t ADD COLUMN IF NOT EXISTS $c DECIMAL(10,2) NOT NULL DEFAULT 0.00", "<code>$t.$c</code> — $lbl");
    }
}

/* ==========================================================================
   2. Notification columns
   ========================================================================== */
echo "<h3>2. Notification columns</h3>";
q($conn, "ALTER TABLE notifications ADD COLUMN IF NOT EXISTS link VARCHAR(255) NULL", "<code>notifications.link</code> — where tapping it goes");
q($conn, "ALTER TABLE notifications ADD COLUMN IF NOT EXISTS type VARCHAR(30) NOT NULL DEFAULT 'general'", "<code>notifications.type</code> — icon &amp; colour");
q($conn, "ALTER TABLE notifications ADD COLUMN IF NOT EXISTS read_at DATETIME NULL", "<code>notifications.read_at</code>");
q($conn, "ALTER TABLE notifications ADD COLUMN IF NOT EXISTS created_by INT NULL", "<code>notifications.created_by</code>");
q($conn, "ALTER TABLE notifications ADD INDEX idx_notif_user (user_id, is_read, created_at)", "index on notifications");

/* ==========================================================================
   3. Backfill the rupee columns from the existing percentages
   ========================================================================== */
echo "<h3>3. Backfill rupee amounts from percentages</h3>";
$pairs = [
    'seller_share_amt'          => 'seller_share_pct',
    'subscription_discount_amt' => 'subscription_discount_pct',
    'referral_share_amt'        => 'referral_share_pct',
    'staff_share_amt'           => 'staff_share_pct',
    'company_share_amt'         => 'company_share_pct',
];
foreach (['seller_services', 'agency_services'] as $t) {
    $sets = [];
    foreach ($pairs as $a => $p) {
        $sets[] = "$a = ROUND((CASE WHEN discount_price > 0 THEN discount_price ELSE price END) * $p / 100, 2)";
    }
    try {
        $conn->query("UPDATE $t SET " . implode(', ', $sets) . " WHERE 1");
        echo "<p style='color:#059669'>&#10003; <b>$t</b> — " . $conn->affected_rows . " row(s) backfilled.</p>";
    } catch (\Throwable $e) {
        echo "<p style='color:#dc2626'>&#10007; $t — " . htmlspecialchars($e->getMessage()) . "</p>";
    }
}
echo "<p style='font-size:13px;color:#64748b'>Every product stays in <b>percent</b> mode. Switch a product to rupee mode from Product Management &rarr; Update.</p>";

/* ==========================================================================
   4. Give existing notifications a sensible link
   ========================================================================== */
echo "<h3>4. Link existing notifications</h3>";
$rules = [
    ['withdrawal',   'wallet.php#to_bank',        "title LIKE '%ithdraw%' OR title LIKE '%ank account%'"],
    ['earning',      'earning_report.php',        "title LIKE '%arning%' OR message LIKE '%added to your wallet%'"],
    ['certificate',  'profile.php#certificate',   "title LIKE '%partner%' OR message LIKE '%certificate%'"],
    ['service',      'seller/services.php',       "title LIKE '%ervice%'"],
    ['kyc',          'profile.php#kyc',           "title LIKE '%KYC%'"],
    ['subscription', 'subscription.php',          "title LIKE '%ubscription%'"],
    ['referral',     'refer.php',                 "message LIKE '%eferral%'"],
    ['wallet',       'wallet.php#wallet_history', "title LIKE '%allet%'"],
];
echo "<table><tr><th>Type</th><th>Link</th><th>Rows updated</th></tr>";
foreach ($rules as $r) {
    try {
        $conn->query("UPDATE notifications SET type = '{$r[0]}', link = '{$r[1]}'
                      WHERE (link IS NULL OR link = '') AND ({$r[2]})");
        echo "<tr><td><code>{$r[0]}</code></td><td><code>{$r[1]}</code></td><td><b>" . $conn->affected_rows . "</b></td></tr>";
    } catch (\Throwable $e) {
        echo "<tr><td colspan='3' style='color:#dc2626'>" . htmlspecialchars($e->getMessage()) . "</td></tr>";
    }
}
echo "</table>";

/* ==========================================================================
   5. Verification
   ========================================================================== */
echo "<h3>5. Verification</h3><table><tr><th>Check</th><th>Result</th></tr>";
foreach ([
    "SELECT COUNT(*) n FROM seller_services"                                  => 'products total',
    "SELECT COUNT(*) n FROM seller_services WHERE share_mode = 'amount'"      => 'products in rupee mode',
    "SELECT COUNT(*) n FROM seller_services WHERE seller_share_amt > 0"       => 'products with a seller amount',
    "SELECT COUNT(*) n FROM product_share_ledger WHERE share_type='seller' AND paid=0" => 'seller shares PENDING (waiting for order completion)',
    "SELECT COALESCE(SUM(amount),0) n FROM product_share_ledger WHERE share_type='seller' AND paid=0" => 'pending seller share value',
    "SELECT COUNT(*) n FROM notifications"                                    => 'notifications',
    "SELECT COUNT(*) n FROM notifications WHERE link IS NOT NULL AND link<>''" => 'notifications with a link',
] 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>";
?>

<?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>What is now live</b><br>
• Product Management &rarr; Update has a <b>Percentage / Fixed Amount</b> switch. In rupee mode the company share is the price minus the rest.<br>
• Seller share is <b>held</b> until the order is marked <b>Completed</b> — before that it only appears in reports, not in the wallet.<br>
• Notifications are clickable, carry an icon and colour, and can be sent and deleted from <b>Admin &rarr; User Management &rarr; Notifications</b>.<br>
• KYC now shows which sections are mandatory — Personal, Address and Bank for everyone, plus Business for sellers.<br>
• User list, Product Management and Service list have sticky filters, search and CSV export.<br>
• The user list has a full report row (pending KYC, subscriptions, today's registrations and more).</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 emomigration.php from the server now.</p>
</body></html>
