<?php
/**
 * SmartFlowFX - get-level-report.php (v3 — Collation Fixed)
 *
 * Actual level_logs columns:
 *   id, wallet, from_user, level, percentage, amount, date, created_at, level_no
 *
 * Level rates: 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);
header('Content-Type: application/json');
header("Access-Control-Allow-Origin: *");

require_once '../config/db.php';

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

$wallet = trim($_GET['wallet'] ?? '');
if (empty($wallet)) {
    echo json_encode(["status" => "error", "message" => "Wallet required"]);
    exit;
}

// ── 1. User check ──────────────────────────────────────────
$uRow = mysqli_fetch_assoc(mysqli_query($conn,
    "SELECT account_id, name FROM users WHERE wallet='$wallet' LIMIT 1"));
if (!$uRow) {
    echo json_encode(["status" => "error", "message" => "User not found"]);
    exit;
}
$myAccId = $uRow['account_id'];

// ── 2. Level rates ─────────────────────────────────────────
$LEVEL_RATES = [1=>30, 2=>20, 3=>15, 4=>10, 5=>8, 6=>7, 7=>5, 8=>5];

// ── 3. Level-wise summary from level_logs ─────────────────
$levelSummary = [];
for ($lv = 1; $lv <= 8; $lv++) {
    $row = mysqli_fetch_assoc(mysqli_query($conn,
        "SELECT COUNT(DISTINCT from_user) AS members,
                COALESCE(SUM(amount), 0) AS earned
         FROM level_logs
         WHERE wallet='$wallet' AND level_no=$lv"
    ));
    $levelSummary[$lv] = [
        'level'    => $lv,
        'rate'     => $LEVEL_RATES[$lv],
        'members'  => intval($row['members']),
        'earned'   => floatval($row['earned']),
        'expected' => 0, // calculated below
    ];
}

// ── 4. Total level income & active downlines ───────────────
$totRow = mysqli_fetch_assoc(mysqli_query($conn,
    "SELECT COALESCE(SUM(amount),0) AS total,
            COUNT(DISTINCT from_user) AS active_down
     FROM level_logs WHERE wallet='$wallet'"
));
$total_level_income = floatval($totRow['total']);
$active_downlines   = intval($totRow['active_down']);

// ── 5. Levels earning (levels with earned > 0) ─────────────
$levels_earning = 0;
foreach ($levelSummary as $ls) {
    if ($ls['earned'] > 0) $levels_earning++;
}

// ── 6. Expected daily level income ────────────────────────
// Walk 8 levels down from this user and sum their expected ROI × rate
function getDirects($conn, $accId) {
    $q = mysqli_query($conn,
        "SELECT account_id, wallet FROM users WHERE referral_id='$accId'");
    $list = [];
    while ($r = mysqli_fetch_assoc($q)) $list[] = $r;
    return $list;
}

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

function getDailyRoi($inv) {
    if ($inv >= 10000) return round(8.0/30, 6);
    if ($inv >= 500)   return round(6.0/30, 6);
    return 0;
}

$expected_daily_level = 0;

function calcExpected($conn, $accId, $level, $LEVEL_RATES, &$expected_daily_level, &$levelSummary) {
    if ($level > 8) return;
    $directs = getDirects($conn, $accId);
    foreach ($directs as $d) {
        $inv     = getInvestment($conn, $d['wallet']);
        $rate    = getDailyRoi($inv);
        $dailyRoi = round($inv * ($rate / 100), 6);
        if ($dailyRoi > 0) {
            $lvlIncome = round($dailyRoi * ($LEVEL_RATES[$level] / 100), 6);
            $expected_daily_level += $lvlIncome;
            $levelSummary[$level]['expected'] += $lvlIncome;
        }
        calcExpected($conn, $d['account_id'], $level + 1, $LEVEL_RATES, $expected_daily_level, $levelSummary);
    }
}
calcExpected($conn, $myAccId, 1, $LEVEL_RATES, $expected_daily_level, $levelSummary);

// ── 7. Income history ──────────────────────────────────────
$filterLv  = intval($_GET['level'] ?? 0);
$whereLevel = $filterLv > 0 ? "AND ll.level_no=$filterLv" : "";

$histQ = mysqli_query($conn,
    "SELECT ll.amount, ll.level_no, ll.percentage,
            ll.from_user, ll.created_at,
            u.name AS from_name
     FROM level_logs ll
     LEFT JOIN users u ON u.wallet = ll.from_user COLLATE utf8mb4_general_ci
     WHERE ll.wallet='$wallet' $whereLevel
     ORDER BY ll.id DESC LIMIT 100"
);

$history = [];
if ($histQ) {
    while ($row = mysqli_fetch_assoc($histQ)) {
        // Get from_user's ROI for that day
        $fromRoi = floatval(mysqli_fetch_assoc(mysqli_query($conn,
            "SELECT COALESCE(roi_amount, 0) AS r FROM roi_logs
             WHERE wallet='{$row['from_user']}' AND DATE(created_at)=DATE('{$row['created_at']}')
             LIMIT 1"
        ))['r']);

        $history[] = [
            'date'       => date('d M Y', strtotime($row['created_at'])),
            'from_wallet'=> $row['from_user'],
            'from_name'  => $row['from_name'] ?: 'Unknown',
            'level_no'   => intval($row['level_no']),
            'from_roi'   => $fromRoi,
            'rate'       => floatval($row['percentage']),
            'amount'     => floatval($row['amount']),
        ];
    }
}

// ── 8. Response ────────────────────────────────────────────
echo json_encode([
    "status"  => "success",
    "summary" => [
        "total_level_income"    => round($total_level_income, 4),
        "active_downlines"      => $active_downlines,
        "expected_daily_level"  => round($expected_daily_level, 4),
        "levels_earning"        => $levels_earning,
    ],
    "levels"  => array_values($levelSummary),
    "history" => $history,
]);
?>