<?php
/**
 * SmartFlowFX - DAILY ROI + LEVEL INCOME CRON (Final)
 * Cron: 0 0 * * 1-5 php /path/to/cron-roi.php
 *
 * ROI:   $500–$9,999 → 0.2%/day → 72%/yr cap
 *        $10,000+    → 0.267%/day → 96%/yr cap
 * Level: L1→30%, L2→20%, L3→15%, L4→10%, L5→8%, L6→7%, L7→5%, L8→5%
 */

error_reporting(E_ALL);
ini_set('display_errors', 1);
require_once __DIR__ . '/../config/db.php';

if (date('N') > 5) { echo "Weekend. No ROI.\n"; exit; }

$today = date('Y-m-d');
$now   = date('Y-m-d H:i:s');
$LEVEL_RATES = [1=>30, 2=>20, 3=>15, 4=>10, 5=>8, 6=>7, 7=>5, 8=>5];

// Fix collation at connection level
mysqli_query($conn, "SET NAMES utf8mb4 COLLATE utf8mb4_general_ci");

// Detect roi_logs columns
$roiCols = [];
$cQ = mysqli_query($conn, "SHOW COLUMNS FROM roi_logs");
while ($c = mysqli_fetch_assoc($cQ)) $roiCols[] = $c['Field'];
$has_pkg   = in_array('package_name',    $roiCols);
$has_inv   = in_array('invested_amount', $roiCols);
$has_dayno = in_array('day_number',      $roiCols);

echo "═══════════════════════════════════\n";
echo "  SmartFlowFX Cron — $today\n";
echo "═══════════════════════════════════\n\n";

function getPackage($inv) {
    if ($inv >= 10000) return ['rate'=>round(8.0/30,6),'cap'=>96.0,'name'=>'Premium'];
    if ($inv >= 500)   return ['rate'=>round(6.0/30,6),'cap'=>72.0,'name'=>'Standard'];
    return null;
}

function doLevel($conn, $eAccId, $eWallet, $roi, $LR, $today, $now, &$lvlT, &$lvlC) {
    $cur = $eAccId;
    for ($lv = 1; $lv <= 8; $lv++) {
        $sp = mysqli_fetch_assoc(mysqli_query($conn,
            "SELECT u.wallet, u.account_id FROM users u
             INNER JOIN users ch ON ch.referral_id COLLATE utf8mb4_general_ci = u.account_id COLLATE utf8mb4_general_ci
             WHERE ch.account_id COLLATE utf8mb4_general_ci = '$cur' LIMIT 1"));
        if (!$sp || empty($sp['wallet'])) break;

        $spW = $sp['wallet']; $spA = $sp['account_id'];

        $spInv = floatval(mysqli_fetch_assoc(mysqli_query($conn,
            "SELECT COALESCE(SUM(amount),0) AS a FROM deposits WHERE wallet='$spW' AND status='success'"
        ))['a']);
        if ($spInv < 500) { $cur = $spA; continue; }

        $dup = mysqli_fetch_assoc(mysqli_query($conn,
            "SELECT id FROM level_logs WHERE wallet='$spW' AND from_user='$eWallet'
             AND level_no=$lv AND DATE(created_at)='$today' LIMIT 1"));
        if ($dup) { $cur = $spA; continue; }

        $rate = $LR[$lv];
        $amt  = round($roi * ($rate/100), 6);
        if ($amt <= 0) { $cur = $spA; continue; }

        $ins = mysqli_query($conn,
            "INSERT INTO level_logs (wallet,from_user,level,level_no,percentage,amount,date,created_at)
             VALUES ('$spW','$eWallet',$lv,$lv,$rate,$amt,'$today','$now')");
        if ($ins) {
            $lvlT += $amt; $lvlC++;
            echo "  ↳ L$lv +\$$amt → $spW | {$rate}% of \$$roi\n";
        } else {
            echo "  ❌ L$lv DB Error: " . mysqli_error($conn) . "\n";
        }
        $cur = $spA;
    }
}

echo "── ROI DISTRIBUTION ──\n\n";

$uQ = mysqli_query($conn,
    "SELECT DISTINCT u.wallet, u.account_id FROM users u
     JOIN deposits d ON d.wallet COLLATE utf8mb4_general_ci = u.wallet COLLATE utf8mb4_general_ci
     WHERE d.status='success'");

$roiT=0; $roiD=0; $roiS=0; $lvlT=0; $lvlC=0;

while ($u = mysqli_fetch_assoc($uQ)) {
    $w = $u['wallet']; $a = $u['account_id'];

    $inv = floatval(mysqli_fetch_assoc(mysqli_query($conn,
        "SELECT COALESCE(SUM(amount),0) AS a FROM deposits WHERE wallet='$w' AND status='success'"
    ))['a']);

    $pkg = getPackage($inv);
    if (!$pkg) { $roiS++; continue; }

    $maxRoi = round($inv * ($pkg['cap']/100), 6);
    $earned = floatval(mysqli_fetch_assoc(mysqli_query($conn,
        "SELECT COALESCE(SUM(roi_amount),0) AS t FROM roi_logs WHERE wallet='$w'"
    ))['t']);
    if ($earned >= $maxRoi) { echo "SKIP $w — cap reached\n"; $roiS++; continue; }

    $dup = mysqli_fetch_assoc(mysqli_query($conn,
        "SELECT id FROM roi_logs WHERE wallet='$w' AND DATE(created_at)='$today' LIMIT 1"));
    if ($dup) { $roiS++; continue; }

    $roiAmt = round($inv * ($pkg['rate']/100), 6);
    if (($earned + $roiAmt) > $maxRoi) $roiAmt = round($maxRoi - $earned, 6);
    if ($roiAmt <= 0) { $roiS++; continue; }

    $dayNo = intval(mysqli_fetch_assoc(mysqli_query($conn,
        "SELECT COUNT(*) AS c FROM roi_logs WHERE wallet='$w'"
    ))['c']) + 1;

    $iCols = "wallet, roi_rate, roi_amount, created_at";
    $iVals = "'$w', {$pkg['rate']}, $roiAmt, '$now'";
    if ($has_inv)   { $iCols .= ", invested_amount"; $iVals .= ", $inv"; }
    if ($has_pkg)   { $iCols .= ", package_name";    $iVals .= ", '{$pkg['name']}'"; }
    if ($has_dayno) { $iCols .= ", day_number";      $iVals .= ", $dayNo"; }

    $ins = mysqli_query($conn, "INSERT INTO roi_logs ($iCols) VALUES ($iVals)");

    if ($ins) {
        $roiT += $roiAmt; $roiD++;
        echo "✅ ROI | $w | {$pkg['name']} | {$pkg['rate']}%/day | Day $dayNo | +\$$roiAmt\n";
        doLevel($conn, $a, $w, $roiAmt, $LEVEL_RATES, $today, $now, $lvlT, $lvlC);
    } else {
        echo "❌ FAILED | $w | " . mysqli_error($conn) . "\n";
    }
}

echo "\n═══════════════════════════════════\n";
echo "  ROI done    : $roiD | \$" . number_format($roiT,4) . "\n";
echo "  ROI skipped : $roiS\n";
echo "  Level dist  : $lvlC | \$" . number_format($lvlT,4) . "\n";
echo "  Grand total : \$" . number_format($roiT+$lvlT,4) . "\n";
echo "═══════════════════════════════════\n";
?>