<?php
/* ============================================================================
   Migration — Earning Report sources

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

   What it does
     1. Re-labels existing ledger rows onto the new source names
     2. Imports past subscription discounts as report-only rows
     3. Splits any old generic referral rows onto Level 1 / 2 / 3 where the
        description already says which level it was

   No table or column is created — this only reclassifies existing rows.
   ============================================================================ */
require_once __DIR__ . '/config.php';
require_once __DIR__ . '/db.php';
require_once __DIR__ . '/includes/functions.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>Earning Sources — 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: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}
 @media(max-width:640px){body{margin:20px auto}th,td{padding:6px}}
</style></head><body>
<h2>Migration — Earning Report Sources</h2>
<?php

$conn = $db->getConnection();
wallet_ensure_schema($db);

/* ==========================================================================
   1. Re-label existing rows
   ========================================================================== */
echo "<h3>1. Re-label existing ledger rows</h3><table><tr><th>Old source</th><th>New source</th><th>Rows updated</th></tr>";

$relabel = [
    'seller_share' => 'sales_earning',
];
foreach ($relabel as $old => $new) {
    try {
        $conn->query("UPDATE wallet_ledger SET category = '$new' WHERE category = '$old'");
        echo "<tr><td><code>$old</code></td><td><code>$new</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>";
    }
}

/* Generic 'referral' rows: split by what the description says */
$level_map = [
    'ref_sub_l1' => "description LIKE '%Level 1%'",
    'ref_sub_l2' => "description LIKE '%Level 2%'",
    'ref_sub_l3' => "description LIKE '%Level 3%'",
];
foreach ($level_map as $cat => $cond) {
    try {
        $conn->query("UPDATE wallet_ledger SET category = '$cat' WHERE category = 'referral' AND $cond");
        echo "<tr><td><code>referral</code> ($cond)</td><td><code>$cat</code></td><td><b>" . $conn->affected_rows . "</b></td></tr>";
    } catch (\Throwable $e) {}
}
/* Anything still generic came from a product/service sale */
try {
    $conn->query("UPDATE wallet_ledger SET category = 'ref_sale' WHERE category = 'referral'");
    echo "<tr><td><code>referral</code> (remaining)</td><td><code>ref_sale</code></td><td><b>" . $conn->affected_rows . "</b></td></tr>";
} catch (\Throwable $e) {}
echo "</table>";

/* ==========================================================================
   2. Import past subscription discounts as report-only rows
   ========================================================================== */
echo "<h3>2. Import past discounts (report only)</h3>";
$imported = 0; $skipped = 0;
try {
    $have = $db->select("SELECT COUNT(*) n FROM wallet_ledger WHERE category = 'own_discount'");
    if (!empty($have) && (int)$have[0]['n'] > 0) {
        echo "<p style='color:#d97706'>&#8226; Skipped — discount rows already exist (" . (int)$have[0]['n'] . "). Safe to re-run.</p>";
    } else {
        $rows = [];
        try {
            $rows = $db->select("SELECT beneficiary_id, amount, percent, service_title, order_id, created_at
                                 FROM product_share_ledger
                                 WHERE share_type = 'subscription_discount' AND amount > 0");
        } catch (\Throwable $e) {}
        foreach ($rows as $r) {
            if (empty($r['beneficiary_id'])) { $skipped++; continue; }
            try {
                $db->insert("INSERT INTO wallet_ledger
                    (user_id, wallet, direction, amount, balance_after, category, description, reference_type, reference_id, created_at)
                    VALUES (?, 'report', 'credit', ?, 0, 'own_discount', ?, 'order', ?, ?)", [
                    (int)$r['beneficiary_id'], $r['amount'],
                    'Subscriber discount (' . rtrim(rtrim(number_format((float)$r['percent'], 2), '0'), '.') . '%) on ' . ($r['service_title'] ?: 'a service'),
                    (int)$r['order_id'], $r['created_at'],
                ]);
                $imported++;
            } catch (\Throwable $e) { $skipped++; }
        }
        echo "<p style='color:#059669'>&#10003; Imported <b>$imported</b> past discount(s)" . ($skipped ? ", skipped $skipped" : "") . ".</p>";
    }
} catch (\Throwable $e) {
    echo "<p style='color:#dc2626'>&#10007; " . htmlspecialchars($e->getMessage()) . "</p>";
}

/* ==========================================================================
   3. Referral level percentages currently configured
   ========================================================================== */
echo "<h3>3. Referral commission settings</h3><table><tr><th>Level</th><th>Percentage</th><th>Applies to</th></tr>";
foreach ([1 => 'ref_level_1_comm', 2 => 'ref_level_2_comm', 3 => 'ref_level_3_comm'] as $lvl => $key) {
    $v = getSetting($key, $lvl === 1 ? '10' : ($lvl === 2 ? '5' : '2'));
    echo "<tr><td>Level $lvl</td><td><b>{$v}%</b></td><td>Subscription purchase by someone $lvl level(s) below</td></tr>";
}
echo "</table>";
echo "<p style='font-size:13px;color:#64748b'>Change these in <b>Admin &rarr; Commission Mgt &rarr; Refer &amp; Earn</b>.</p>";

/* ==========================================================================
   4. Verification
   ========================================================================== */
echo "<h3>4. Current earning rows by source</h3><table><tr><th>Source</th><th>Rows</th><th>Total</th></tr>";
foreach (['ref_sub_l1','ref_sub_l2','ref_sub_l3','ref_sale','sales_earning','staff_share','own_discount'] as $c) {
    try {
        $r = $db->select("SELECT COUNT(*) n, COALESCE(SUM(amount),0) t FROM wallet_ledger WHERE category = ?", [$c]);
        $n = !empty($r) ? (int)$r[0]['n'] : 0;
        $t = !empty($r) ? (float)$r[0]['t'] : 0;
        $tag = ($c === 'own_discount') ? ' <span style="color:#059669;font-size:11px">(report only)</span>' : '';
        echo "<tr><td><code>$c</code>$tag</td><td>$n</td><td><b>&#8377;" . number_format($t, 2) . "</b></td></tr>";
    } catch (\Throwable $e) {}
}
echo "</table>";
?>

<div class="box ok"><b>&#10003; Done</b><br><br>
<b>The Earning Report now shows these sources:</b><br>
• Refer Subscription — Level 1 / Level 2 / Level 3 &nbsp;(separate)<br>
• Refer Service &amp; Product Sale<br>
• My Discount Saved &nbsp;<i>(report only — never added to earnings)</i><br>
• My Sales Earning &nbsp;<i>(sellers only)</i><br>
• Staff Share &nbsp;<i>(staff/admin only)</i><br><br>
The list adapts to whoever is logged in — a customer never sees seller or staff sources.<br><br>
<b>3-level subscription commission is now live:</b> when someone buys a subscription, their upline gets paid on all three levels into the virtual wallet.</div>

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