<?php
// ════════════════════════════════════════════════════════════════════════════
// functions.php — Earnovix Platform Helper Functions
//
// ✅ STANDARDIZED: All wallet operations use column-based structure
// ✅ FIXED: Cross-country commission distribution with KES conversion
//   - Commission levels stored in downline's local currency
//   - Converted to KES (exchange_rate × commission) before crediting upline
//   - exchange_rate convention: 1 local unit = X KES  (Kenya=1.0, Uganda≈0.026)
// ✅ FIXED: Admin activate → instantly distributes referral commissions in KES
// ✅ FIXED: balance + total_earned always synced on every credit/debit
// ════════════════════════════════════════════════════════════════════════════

if (!ob_get_level()) { ob_start(); }

// ── WALLET COLUMN MAP ─────────────────────────────────────────────────────
define('WALLET_COLUMNS', [
    'main'          => 'main_wallet',
    'referral'      => 'referral_wallet',
    'bonus'         => 'bonus_wallet',
    'video'         => 'video_wallet',
    'trivia'        => 'trivia_wallet',
    'survey'        => 'survey_wallet',
    'ads'           => 'ads_wallet',
    'spin'          => 'spin_wallet',
    'blog'          => 'blog_wallet',
    'meme'          => 'meme_wallet',
    'chat'          => 'chat_wallet',
    'transcription' => 'transcription_wallet',
]);

function getWalletColumn($walletType) {
    if ($walletType === null || !is_string($walletType)) return null;
    $walletType = strtolower(trim($walletType));
    if ($walletType === '') return null;
    if (in_array($walletType, WALLET_COLUMNS)) return $walletType;
    return WALLET_COLUMNS[$walletType] ?? null;
}

// ── CORE UTILITIES ────────────────────────────────────────────────────────
function sanitize($data) {
    if (is_array($data)) return array_map('sanitize', $data);
    return htmlspecialchars(stripslashes(trim($data)), ENT_QUOTES, 'UTF-8');
}
function validateEmail($email) { return filter_var($email, FILTER_VALIDATE_EMAIL); }
function generateReferralCode($length = 8) {
    $chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';
    $code  = '';
    for ($i = 0; $i < $length; $i++) $code .= $chars[rand(0, strlen($chars)-1)];
    return $code;
}
function hashPassword($password)            { return password_hash($password, PASSWORD_DEFAULT); }
function verifyPassword($password, $hash)   { return password_verify($password, $hash); }
function isLoggedIn()      { return isset($_SESSION['user_id'])  && !empty($_SESSION['user_id']); }
function isAdminLoggedIn() { return isset($_SESSION['admin_id']) && !empty($_SESSION['admin_id']); }
function getUserId()       { return $_SESSION['user_id']  ?? null; }
function getAdminId()      { return $_SESSION['admin_id'] ?? null; }

function redirect($url) {
    if (ob_get_level()) ob_end_clean();
    if (headers_sent()) {
        echo '<script>window.location.href="'.htmlspecialchars($url, ENT_QUOTES).'";</script>';
        echo '<noscript><meta http-equiv="refresh" content="0;url='.htmlspecialchars($url, ENT_QUOTES).'" /></noscript>';
        exit();
    }
    header("Location: $url"); exit();
}
function setFlash($type, $message) { $_SESSION['flash'] = ['type'=>$type,'message'=>$message]; }
function getFlash() {
    if (isset($_SESSION['flash'])) { $f=$_SESSION['flash']; unset($_SESSION['flash']); return $f; }
    return null;
}
function formatCurrency($amount, $currencySymbol='Ksh') { return $currencySymbol.' '.number_format($amount,2); }
function convertToUserCurrency($amount, $exchangeRate)  { return $amount * $exchangeRate; }

function getUserData($userId) {
    $conn = getConnection();
    $stmt = $conn->prepare("SELECT u.*, c.currency_symbol, c.exchange_rate
        FROM users u LEFT JOIN countries c ON u.country=c.name WHERE u.id=?");
    $stmt->bind_param("i",$userId); $stmt->execute();
    return $stmt->get_result()->fetch_assoc();
}

// ── WALLET FUNCTIONS ──────────────────────────────────────────────────────
function ensureWalletExists($conn, $userId) {
    $s = $conn->prepare("SELECT id FROM wallets WHERE user_id=? LIMIT 1");
    $s->bind_param("i",$userId); $s->execute();
    if ($s->get_result()->num_rows === 0) {
        $s = $conn->prepare("INSERT INTO wallets
            (user_id,balance,total_earned,main_wallet,referral_wallet,bonus_wallet,
             video_wallet,trivia_wallet,survey_wallet,ads_wallet,spin_wallet,
             blog_wallet,meme_wallet,chat_wallet,transcription_wallet,created_at,updated_at)
            VALUES(?,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NOW(),NOW())");
        $s->bind_param("i",$userId); $s->execute();
    }
}
function ensureWalletExistsPDO($pdo, $userId) {
    $s = $pdo->prepare("SELECT id FROM wallets WHERE user_id=? LIMIT 1");
    $s->execute([$userId]);
    if (!$s->fetch()) {
        $pdo->prepare("INSERT INTO wallets
            (user_id,balance,total_earned,main_wallet,referral_wallet,bonus_wallet,
             video_wallet,trivia_wallet,survey_wallet,ads_wallet,spin_wallet,
             blog_wallet,meme_wallet,chat_wallet,transcription_wallet,created_at,updated_at)
            VALUES(?,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NOW(),NOW())")->execute([$userId]);
    }
}
function recalcWalletBalance($conn, $userId) {
    $sum='COALESCE('.implode(',0)+COALESCE(',array_values(WALLET_COLUMNS)).',0)';
    $conn->prepare("UPDATE wallets SET balance={$sum},
        total_earned=GREATEST(total_earned,{$sum}),updated_at=NOW()
        WHERE user_id=?")->bind_param("i",$userId) && null;
    $s=$conn->prepare("UPDATE wallets SET balance={$sum},
        total_earned=GREATEST(COALESCE(total_earned,0),{$sum}),updated_at=NOW()
        WHERE user_id=?");
    $s->bind_param("i",$userId); $s->execute();
}
function recalcWalletBalancePDO($pdo, $userId) {
    $sum='COALESCE('.implode(',0)+COALESCE(',array_values(WALLET_COLUMNS)).',0)';
    $pdo->prepare("UPDATE wallets SET balance={$sum},
        total_earned=GREATEST(COALESCE(total_earned,0),{$sum}),updated_at=NOW()
        WHERE user_id=?")->execute([$userId]);
}
function getUserWallets($userId) {
    $conn = getConnection();
    ensureWalletExists($conn,$userId);
    recalcWalletBalance($conn,$userId);
    $s=$conn->prepare("SELECT * FROM wallets WHERE user_id=? LIMIT 1");
    $s->bind_param("i",$userId); $s->execute();
    $row=$s->get_result()->fetch_assoc();
    $wallets=[]; $total=0;
    foreach(WALLET_COLUMNS as $type=>$col){
        $bal=floatval($row[$col]??0); $total+=$bal;
        $wallets[$type]=['balance'=>$bal,'total_earned'=>$bal];
    }
    $wallets['_total']=['balance'=>$total,'total_earned'=>floatval($row['total_earned']??$total)];
    return $wallets;
}
function creditWallet($userId,$walletType,$amount,$description,$reference=null){
    $conn=getConnection(); $col=getWalletColumn($walletType);
    if(!$col||$amount<=0) return false;
    ensureWalletExists($conn,$userId);
    $s=$conn->prepare("UPDATE wallets SET {$col}=COALESCE({$col},0)+?,
        balance=COALESCE(balance,0)+?,total_earned=COALESCE(total_earned,0)+?,
        updated_at=NOW() WHERE user_id=?");
    $s->bind_param("dddi",$amount,$amount,$amount,$userId);
    if(!$s->execute()) return false;
    $type='credit_'.$walletType;
    $conn->prepare("INSERT INTO transactions(user_id,type,amount,description,reference,status,created_at)
        VALUES(?,?,?,?,?,'completed',NOW())")->bind_param("isdss",$userId,$type,$amount,$description,$reference) && null;
    $t=$conn->prepare("INSERT INTO transactions(user_id,type,amount,description,reference,status,created_at)
        VALUES(?,?,?,?,?,'completed',NOW())");
    $t->bind_param("isdss",$userId,$type,$amount,$description,$reference); $t->execute();
    return true;
}
function debitWallet($userId,$walletType,$amount,$description,$reference=null){
    $conn=getConnection(); $col=getWalletColumn($walletType);
    if(!$col||$amount<=0) return false;
    $s=$conn->prepare("SELECT {$col} as wb FROM wallets WHERE user_id=? LIMIT 1");
    $s->bind_param("i",$userId); $s->execute();
    $w=$s->get_result()->fetch_assoc();
    if(!$w||floatval($w['wb'])<$amount) return false;
    $s=$conn->prepare("UPDATE wallets SET {$col}={$col}-?,
        balance=GREATEST(0,COALESCE(balance,0)-?),updated_at=NOW()
        WHERE user_id=? AND {$col}>=?");
    $s->bind_param("ddid",$amount,$amount,$userId,$amount);
    if(!$s->execute()||$s->affected_rows===0) return false;
    $type='debit_'.$walletType;
    $t=$conn->prepare("INSERT INTO transactions(user_id,type,amount,description,reference,status,created_at)
        VALUES(?,?,?,?,?,'completed',NOW())");
    $t->bind_param("isdss",$userId,$type,$amount,$description,$reference); $t->execute();
    return true;
}

// ── WITHDRAWAL FUNCTIONS ──────────────────────────────────────────────────
function calculateWithdrawalFee($amount,$countryId){
    $conn=getConnection();
    $s=$conn->prepare("SELECT fee FROM withdrawal_fee_tiers
        WHERE country_id=? AND ? BETWEEN min_amount AND max_amount LIMIT 1");
    $s->bind_param("id",$countryId,$amount); $s->execute();
    if($r=$s->get_result()->fetch_assoc()) return $r['fee'];
    $s=$conn->prepare("SELECT withdrawal_fee FROM countries WHERE id=?");
    $s->bind_param("i",$countryId); $s->execute();
    if($r=$s->get_result()->fetch_assoc()) return $r['withdrawal_fee'];
    return 0;
}
function getTotalWithdrawals($userId){
    $conn=getConnection();
    $s=$conn->prepare("SELECT COALESCE(SUM(net_amount),0) as t FROM withdrawals WHERE user_id=? AND status='completed'");
    $s->bind_param("i",$userId); $s->execute();
    return $s->get_result()->fetch_assoc()['t'];
}
function getActivationExpense($userId){
    $user=getUserData($userId);
    if(!$user||$user['status']!=='active') return 0;
    $conn=getConnection();
    $s=$conn->prepare("SELECT activation_fee FROM countries WHERE name=?");
    $s->bind_param("s",$user['country']); $s->execute();
    $r=$s->get_result()->fetch_assoc();
    return $r['activation_fee']??0;
}
function getDownlinesCount($userId){
    $conn=getConnection();
    $s=$conn->prepare("SELECT COUNT(*) as c FROM users WHERE referred_by=?");
    $s->bind_param("i",$userId); $s->execute();
    return $s->get_result()->fetch_assoc()['c'];
}
function getActiveDownlinesCount($userId){
    $conn=getConnection();
    $s=$conn->prepare("SELECT COUNT(*) as c FROM users WHERE referred_by=? AND status='active'");
    $s->bind_param("i",$userId); $s->execute();
    return $s->get_result()->fetch_assoc()['c'];
}

// ── SETTINGS ──────────────────────────────────────────────────────────────
function getSetting($key,$default=''){
    $conn=getConnection();
    $s=$conn->prepare("SELECT setting_value FROM settings WHERE setting_key=?");
    $s->bind_param("s",$key); $s->execute();
    $r=$s->get_result()->fetch_assoc();
    return $r?$r['setting_value']:$default;
}
function updateSetting($key,$value){
    $conn=getConnection();
    $s=$conn->prepare("INSERT INTO settings(setting_key,setting_value)VALUES(?,?)
        ON DUPLICATE KEY UPDATE setting_value=?");
    $s->bind_param("sss",$key,$value,$value);
    return $s->execute();
}
function getSiteName(){ return getSetting('site_name','PrimePanel'); }
function isActivityAvailable($activityType){
    $conn=getConnection(); $dow=date('N'); $now=date('H:i:s');
    $s=$conn->prepare("SELECT * FROM timetable WHERE day_of_week=? AND activity_type=? AND status='active'");
    $s->bind_param("is",$dow,$activityType); $s->execute();
    $sc=$s->get_result()->fetch_assoc();
    if(!$sc) return false;
    if(empty($sc['start_time'])&&empty($sc['end_time'])) return true;
    return $now>=$sc['start_time']&&$now<=$sc['end_time'];
}
function timeAgo($datetime){
    $d=time()-strtotime($datetime);
    if($d<60) return 'Just now';
    if($d<3600){$m=floor($d/60);return $m.' min'.($m>1?'s':'').' ago';}
    if($d<86400){$h=floor($d/3600);return $h.' hour'.($h>1?'s':'').' ago';}
    if($d<604800){$dy=floor($d/86400);return $dy.' day'.($dy>1?'s':'').' ago';}
    return date('M j, Y',strtotime($datetime));
}
function generateCsrfToken(){
    if(!isset($_SESSION['csrf_token']))$_SESSION['csrf_token']=bin2hex(random_bytes(32));
    return $_SESSION['csrf_token'];
}
function verifyCsrfToken($token){ return isset($_SESSION['csrf_token'])&&hash_equals($_SESSION['csrf_token'],$token); }

// ════════════════════════════════════════════════════════════════════════════
// REFERRAL COMMISSION DISTRIBUTION
// ════════════════════════════════════════════════════════════════════════════
//
// CROSS-COUNTRY CONVENTION:
//   commission_levels stores amounts in the DOWNLINE's local currency
//   exchange_rate = KES per 1 local unit  (Kenya=1.0, Uganda≈0.026, Tanzania≈0.043)
//
//   KES credited to upline = commission_level_amount × downline_exchange_rate
//
//   Example: Kenyan upline refers Ugandan downline
//     Uganda L1 commission = UGX 200, exchange_rate = 0.026
//     Kenyan upline receives: 200 × 0.026 = Ksh 5.20 ✓
//
//   All uplines always receive KES regardless of their own country.
// ════════════════════════════════════════════════════════════════════════════

function getReferralLogFile(){
    $mpesa=dirname(__DIR__).'/mpesa/activation_callback_log.txt';
    if(is_writable(dirname($mpesa))) return $mpesa;
    $fb=dirname(__DIR__).'/logs/referral.txt';
    if(!is_dir(dirname($fb))) @mkdir(dirname($fb),0755,true);
    return $fb;
}

/**
 * Main entry point — called from admin activate, M-Pesa callbacks, etc.
 * $countryId = the DOWNLINE's country ID (used for commission lookup + KES conversion)
 */
function distributeReferralCommissions($userId, $referredBy, $countryId) {
    $paths=[
        __DIR__.'/../config/database.php',
        dirname(__DIR__).'/config/database.php',
        $_SERVER['DOCUMENT_ROOT'].'/config/database.php',
    ];
    $loaded=false;
    foreach($paths as $p){ if(file_exists($p)){require_once $p;$loaded=true;break;} }
    $log=getReferralLogFile();
    if(!$loaded){
        file_put_contents($log,"[".date('Y-m-d H:i:s')."] distributeReferralCommissions: config not found\n",FILE_APPEND);
        return false;
    }
    try{
        $pdo=new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=utf8mb4",DB_USER,DB_PASS,[
            PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES=>false,
        ]);
        // Get downline's activation fee (for logging only — commissions use levels table)
        $s=$pdo->prepare("SELECT activation_fee,exchange_rate,name FROM countries WHERE id=?");
        $s->execute([$countryId]);
        $country=$s->fetch();
        $activationFee=floatval($country['activation_fee']??0);
        file_put_contents($log,"[".date('Y-m-d H:i:s')."] distributeReferralCommissions: user=$userId countryId=$countryId fee=$activationFee\n",FILE_APPEND);
        return distribute_payment_referral_bonus($pdo,$userId,$activationFee,'admin_activation',$countryId);
    }catch(Exception $e){
        file_put_contents($log,"[".date('Y-m-d H:i:s')."] distributeReferralCommissions error: {$e->getMessage()}\n",FILE_APPEND);
        return false;
    }
}

/**
 * Core commission distribution — walks referral chain, credits each upline in KES.
 *
 * $downlineCountryId (optional) — if provided, uses this country's commission levels
 *   and exchange_rate for KES conversion. If omitted, falls back to old per-country logic.
 */
function distribute_payment_referral_bonus($pdo,$userId,$transactionAmount,$source='activation',$downlineCountryId=null){
    $log=getReferralLogFile();
    try{
        // ── Get downline user ─────────────────────────────────────────────────
        $s=$pdo->prepare("SELECT id,referred_by,country,status FROM users WHERE id=?");
        $s->execute([$userId]); $user=$s->fetch();
        if(!$user||empty($user['referred_by'])){
            file_put_contents($log,"[".date('Y-m-d H:i:s')."] distribute_bonus: No referrer for user #$userId\n",FILE_APPEND);
            return false;
        }

        // ── Get downline's country exchange_rate for KES conversion ───────────
        // commission_levels are in downline's local currency × exchange_rate = KES
        if($downlineCountryId){
            $cs=$pdo->prepare("SELECT id,name,exchange_rate FROM countries WHERE id=? LIMIT 1");
            $cs->execute([$downlineCountryId]); $downlineCountry=$cs->fetch();
        } else {
            $cs=$pdo->prepare("SELECT id,name,exchange_rate FROM countries WHERE LOWER(TRIM(name))=LOWER(TRIM(?)) LIMIT 1");
            $cs->execute([$user['country']]); $downlineCountry=$cs->fetch();
        }
        // exchange_rate: 1 local unit = X KES.  Kenya=1.0, Uganda≈0.026, Tanzania≈0.043
        $downlineRate     = max(0.000001,floatval($downlineCountry['exchange_rate']??1.0));
        $downlineCtryId   = $downlineCountry?(int)$downlineCountry['id']:($downlineCountryId??0);
        $downlineCtryName = $downlineCountry['name']??$user['country'];

        // ── Idempotency guard ─────────────────────────────────────────────────
        $dup=$pdo->prepare("SELECT COUNT(*) FROM transactions
            WHERE description LIKE ? AND type='referral_bonus' AND status='completed'");
        $dup->execute(["%from user #$userId ($source)%"]);
        if((int)$dup->fetchColumn()>0){
            file_put_contents($log,"[".date('Y-m-d H:i:s')."] distribute_bonus: Already paid for #$userId — skipping\n",FILE_APPEND);
            return true;
        }

        file_put_contents($log,"[".date('Y-m-d H:i:s')."] distribute_bonus START: user=$userId country=$downlineCtryName rate=$downlineRate source=$source\n",FILE_APPEND);

        // ── Walk referral chain ───────────────────────────────────────────────
        $currentReferrerId=(int)$user['referred_by'];
        $level=1; $depth=0; $totalKes=0;

        while($currentReferrerId&&$depth<50){
            $depth++;
            // Get referrer info
            $rs=$pdo->prepare("SELECT u.id,u.username,u.country,u.referred_by,
                c.currency_symbol,c.name as country_name
                FROM users u LEFT JOIN countries c ON LOWER(TRIM(u.country))=LOWER(TRIM(c.name))
                WHERE u.id=?");
            $rs->execute([$currentReferrerId]); $referrer=$rs->fetch();
            if(!$referrer){
                file_put_contents($log,"[".date('Y-m-d H:i:s')."] chain[$depth]: referrer #$currentReferrerId not found — stop\n",FILE_APPEND);
                break;
            }

            // ── Lookup commission from DOWNLINE's country + level ─────────────
            if($downlineCtryId>0){
                $cl=$pdo->prepare("SELECT commission FROM commission_levels
                    WHERE country_id=? AND level=? LIMIT 1");
                $cl->execute([$downlineCtryId,$level]); $clRow=$cl->fetch();
            } else {
                $clRow=null;
            }

            if(!$clRow){
                file_put_contents($log,"[".date('Y-m-d H:i:s')."] chain[$depth]: No commission for country_id=$downlineCtryId L$level — stop\n",FILE_APPEND);
                break;
            }

            // ── Convert local commission → KES ────────────────────────────────
            $localAmt   = floatval($clRow['commission']);
            $kesAmt     = round($localAmt * $downlineRate, 4);

            if($kesAmt>0){
                $ok=creditReferralWalletPDO($pdo,$currentReferrerId,$kesAmt,$level,$userId,$source);
                if($ok){
                    $totalKes+=$kesAmt;
                    file_put_contents($log,"[".date('Y-m-d H:i:s')."] ✅ L$level: local {$localAmt} × rate {$downlineRate} = Ksh {$kesAmt} → {$referrer['username']} (#{$currentReferrerId}) [{$referrer['country_name']}]\n",FILE_APPEND);
                } else {
                    file_put_contents($log,"[".date('Y-m-d H:i:s')."] ❌ L$level: FAILED to credit Ksh {$kesAmt} → #{$currentReferrerId}\n",FILE_APPEND);
                }
            } else {
                file_put_contents($log,"[".date('Y-m-d H:i:s')."] ⚠ L$level: zero commission — skip\n",FILE_APPEND);
            }

            $level++;
            $currentReferrerId=!empty($referrer['referred_by'])?(int)$referrer['referred_by']:0;
            if($level>10) break;
        }

        file_put_contents($log,"[".date('Y-m-d H:i:s')."] distribute_bonus DONE: totalKes=$totalKes depth=$depth\n",FILE_APPEND);
        return true;

    }catch(Exception $e){
        file_put_contents($log,"[".date('Y-m-d H:i:s')."] distribute_bonus FATAL: {$e->getMessage()}\n",FILE_APPEND);
        return false;
    }
}

/**
 * Credit referral_wallet in KES — atomic, deadlock-safe, idempotent reference
 */
function creditReferralWalletPDO($pdo,$userId,$amount,$level,$fromUserId,$source){
    $log=getReferralLogFile(); $maxRetries=3;
    for($attempt=1;$attempt<=$maxRetries;$attempt++){
        try{
            $pdo->beginTransaction();
            ensureWalletExistsPDO($pdo,$userId);
            $s=$pdo->prepare("SELECT id,referral_wallet,balance FROM wallets WHERE user_id=? LIMIT 1 FOR UPDATE");
            $s->execute([$userId]); $w=$s->fetch();
            if($w){
                $pdo->prepare("UPDATE wallets SET
                    referral_wallet=COALESCE(referral_wallet,0)+?,
                    balance=COALESCE(balance,0)+?,
                    total_earned=COALESCE(total_earned,0)+?,
                    updated_at=NOW() WHERE id=?")->execute([$amount,$amount,$amount,$w['id']]);
            } else {
                $pdo->prepare("INSERT INTO wallets(user_id,balance,total_earned,main_wallet,
                    referral_wallet,bonus_wallet,video_wallet,trivia_wallet,survey_wallet,
                    ads_wallet,spin_wallet,blog_wallet,meme_wallet,chat_wallet,
                    transcription_wallet,created_at,updated_at)
                    VALUES(?,?,?,0,?,0,0,0,0,0,0,0,0,0,0,NOW(),NOW())")->execute([$userId,$amount,$amount,$amount]);
            }
            $desc="Level {$level} referral bonus from user #{$fromUserId} ({$source})";
            $ref ="REF_L{$level}_{$fromUserId}_".time()."_".mt_rand(1000,9999);
            $pdo->prepare("INSERT INTO transactions(user_id,type,amount,description,reference,status,created_at)
                VALUES(?,'referral_bonus',?,?,?,'completed',NOW())")->execute([$userId,$amount,$desc,$ref]);
            $pdo->commit();
            // Non-critical email notification
            try{
                $ef=dirname(__DIR__).'/includes/email.php';
                if(file_exists($ef)){ require_once $ef;
                    if(function_exists('sendReferralBonusNotification')){
                        $s2=$pdo->prepare("SELECT username FROM users WHERE id=? LIMIT 1");
                        $s2->execute([$fromUserId]); $ru=$s2->fetch();
                        sendReferralBonusNotification($userId,$ru['username']??'User',$level,$amount);
                    }
                }
            }catch(Exception $e){}
            return true;
        }catch(Exception $e){
            if($pdo->inTransaction()) $pdo->rollBack();
            $msg=$e->getMessage();
            $dead=strpos($msg,'Deadlock')!==false||$e->getCode()==1213||$e->getCode()==1205||strpos($msg,'Lock wait')!==false;
            if($dead&&$attempt<$maxRetries){ usleep(500000*$attempt); continue; }
            file_put_contents($log,"[".date('Y-m-d H:i:s')."] creditReferralWalletPDO FAILED user#$userId: $msg\n",FILE_APPEND);
            return false;
        }
    }
    return false;
}

// ── BONUS FUNCTIONS ───────────────────────────────────────────────────────
function isBonusTierClaimed($conn,$userId,$tierId){
    $s=$conn->prepare("SELECT id FROM user_bonus_claims WHERE user_id=? AND bonus_tier_id=?");
    $s->bind_param("ii",$userId,$tierId); $s->execute();
    return $s->get_result()->num_rows>0;
}
function awardBonusToUser($conn,$userId,$amount,$bonusType,$reason,$adminId=null,$tierId=null,$notes=null){
    if($tierId&&isBonusTierClaimed($conn,$userId,$tierId))
        return['success'=>false,'message'=>'Already claimed.'];
    $conn->begin_transaction();
    try{
        if($tierId){
            $lc=$conn->prepare("SELECT id FROM user_bonus_claims WHERE user_id=? AND bonus_tier_id=? FOR UPDATE");
            $lc->bind_param("ii",$userId,$tierId); $lc->execute();
            if($lc->get_result()->num_rows>0){$conn->rollback();return['success'=>false,'message'=>'Already claimed.'];}
        }
        ensureWalletExists($conn,$userId);
        $uw=$conn->prepare("UPDATE wallets SET bonus_wallet=COALESCE(bonus_wallet,0)+?,
            balance=COALESCE(balance,0)+?,total_earned=COALESCE(total_earned,0)+?,
            updated_at=NOW() WHERE user_id=?");
        $uw->bind_param("dddi",$amount,$amount,$amount,$userId); $uw->execute();
        $ib=$conn->prepare("INSERT INTO bonus_awards(user_id,bonus_type,bonus_tier_id,amount,reason,notes,awarded_by,status)
            VALUES(?,?,?,?,?,?,?,'credited')");
        $ib->bind_param("isissis",$userId,$bonusType,$tierId,$amount,$reason,$notes,$adminId); $ib->execute();
        if($tierId){
            $cs=$conn->prepare("INSERT INTO user_bonus_claims(user_id,bonus_tier_id)VALUES(?,?)");
            $cs->bind_param("ii",$userId,$tierId); $cs->execute();
        }
        $conn->commit();
        return['success'=>true,'message'=>'Bonus awarded!'];
    }catch(Exception $e){
        $conn->rollback();
        return['success'=>false,'message'=>'Failed to award bonus.'];
    }
}
function claimMilestoneBonus($conn,$userId,$tierId){
    if(!$conn->query("SHOW TABLES LIKE 'referral_bonus_tiers'")->num_rows)
        return['success'=>false,'message'=>'Bonus system not configured.'];
    if(isBonusTierClaimed($conn,$userId,$tierId))
        return['success'=>false,'message'=>'Already claimed.'];
    $ts=$conn->prepare("SELECT * FROM referral_bonus_tiers WHERE id=? AND status='active'");
    $ts->bind_param("i",$tierId); $ts->execute();
    $tier=$ts->get_result()->fetch_assoc();
    if(!$tier) return['success'=>false,'message'=>'Invalid tier.'];
    $us=$conn->prepare("SELECT country FROM users WHERE id=?");
    $us->bind_param("i",$userId); $us->execute();
    $user=$us->get_result()->fetch_assoc();
    if(!$user) return['success'=>false,'message'=>'User not found.'];
    $cs=$conn->prepare("SELECT id FROM countries WHERE name=?");
    $cs->bind_param("s",$user['country']); $cs->execute();
    $cr=$cs->get_result()->fetch_assoc(); $cid=$cr?$cr['id']:null;
    if($tier['country_id']!==null&&$tier['country_id']!=$cid)
        return['success'=>false,'message'=>'Not available in your country.'];
    $rc=$conn->prepare("SELECT COUNT(*) as c FROM users WHERE referred_by=? AND status='active'");
    $rc->bind_param("i",$userId); $rc->execute();
    $refs=$rc->get_result()->fetch_assoc()['c'];
    if($refs<$tier['referral_count']){
        $rem=$tier['referral_count']-$refs;
        return['success'=>false,'message'=>"Need {$rem} more active referrals."];
    }
    $reason="🎉 {$tier['bonus_name']} - Reached {$tier['referral_count']} active referrals!";
    $r=awardBonusToUser($conn,$userId,$tier['bonus_amount'],'referral_milestone',$reason,null,$tierId);
    if($r['success']){$r['amount']=$tier['bonus_amount'];$r['tier_name']=$tier['bonus_name'];}
    return $r;
}
function getUserMilestoneStatus($conn,$userId){
    if(!$conn->query("SHOW TABLES LIKE 'referral_bonus_tiers'")->num_rows) return[];
    $us=$conn->prepare("SELECT country FROM users WHERE id=?");
    $us->bind_param("i",$userId); $us->execute();
    $user=$us->get_result()->fetch_assoc();
    if(!$user) return[];
    $cs=$conn->prepare("SELECT id FROM countries WHERE name=?");
    $cs->bind_param("s",$user['country']); $cs->execute();
    $cr=$cs->get_result()->fetch_assoc(); $cid=$cr?$cr['id']:null;
    $rc=$conn->prepare("SELECT COUNT(*) as c FROM users WHERE referred_by=? AND status='active'");
    $rc->bind_param("i",$userId); $rc->execute();
    $refs=$rc->get_result()->fetch_assoc()['c'];
    $s=$conn->prepare("SELECT t.*,c.currency_symbol,c.name as country_name,
        (SELECT id FROM user_bonus_claims WHERE user_id=? AND bonus_tier_id=t.id) as claimed_id
        FROM referral_bonus_tiers t LEFT JOIN countries c ON t.country_id=c.id
        WHERE t.status='active' AND (t.country_id=? OR t.country_id IS NULL)
        ORDER BY t.referral_count ASC");
    $s->bind_param("ii",$userId,$cid); $s->execute();
    $result=$s->get_result(); $milestones=[]; $done=[];
    while($t=$result->fetch_assoc()){
        if(in_array($t['referral_count'],$done)) continue;
        $done[]=$t['referral_count'];
        $t['current_referrals']=$refs;
        $t['is_claimed']=!empty($t['claimed_id']);
        $t['is_claimable']=!$t['is_claimed']&&$refs>=$t['referral_count'];
        $t['progress_percent']=min(100,round(($refs/$t['referral_count'])*100));
        $t['remaining']=max(0,$t['referral_count']-$refs);
        $milestones[]=$t;
    }
    return $milestones;
}
function getUserBonusBalance($conn,$userId){
    $s=$conn->prepare("SELECT COALESCE(bonus_wallet,0) as b FROM wallets WHERE user_id=? LIMIT 1");
    $s->bind_param("i",$userId); $s->execute();
    $r=$s->get_result()->fetch_assoc();
    return $r?floatval($r['b']):0.0;
}
function getUserTotalBonuses($conn,$userId){
    if(!$conn->query("SHOW TABLES LIKE 'bonus_awards'")->num_rows) return 0.0;
    $s=$conn->prepare("SELECT COALESCE(SUM(amount),0) as t FROM bonus_awards WHERE user_id=? AND status='credited'");
    $s->bind_param("i",$userId); $s->execute();
    return floatval($s->get_result()->fetch_assoc()['t']??0);
}
function getUserBonusHistory($conn,$userId,$limit=20){
    if(!$conn->query("SHOW TABLES LIKE 'bonus_awards'")->num_rows) return[];
    $s=$conn->prepare("SELECT ba.*,t.bonus_name as tier_name FROM bonus_awards ba
        LEFT JOIN referral_bonus_tiers t ON ba.bonus_tier_id=t.id
        WHERE ba.user_id=? ORDER BY ba.created_at DESC LIMIT ?");
    $s->bind_param("ii",$userId,$limit); $s->execute();
    $r=$s->get_result(); $h=[];
    while($row=$r->fetch_assoc()) $h[]=$row;
    return $h;
}
function getUserClaimedMilestonesCount($conn,$userId){
    if(!$conn->query("SHOW TABLES LIKE 'user_bonus_claims'")->num_rows) return 0;
    $s=$conn->prepare("SELECT COUNT(*) as c FROM user_bonus_claims WHERE user_id=?");
    $s->bind_param("i",$userId); $s->execute();
    return $s->get_result()->fetch_assoc()['c']??0;
}
function getClaimableMilestonesCount($conn,$userId){
    $m=getUserMilestoneStatus($conn,$userId); $c=0;
    foreach($m as $ms) if($ms['is_claimable']) $c++;
    return $c;
}

// ── LOGGING & ADMIN ───────────────────────────────────────────────────────
function logActivity($userId,$action,$details=''){
    $conn=getConnection(); $ip=$_SERVER['REMOTE_ADDR']??'Unknown'; $ua=$_SERVER['HTTP_USER_AGENT']??'Unknown';
    if($conn->query("SHOW TABLES LIKE 'activity_logs'")->num_rows){
        $s=$conn->prepare("INSERT INTO activity_logs(user_id,action,details,ip_address,user_agent)VALUES(?,?,?,?,?)");
        if($s){$s->bind_param("issss",$userId,$action,$details,$ip,$ua);$s->execute();}
    }
}
function hasPermission($permission){
    global $conn;
    if(!isset($_SESSION['admin_id'])||!isset($_SESSION['admin_role'])) return false;
    if($_SESSION['admin_role']==='super_admin') return true;
    $aid=$_SESSION['admin_id'];
    $s=$conn->prepare("SELECT COUNT(*) as c FROM admin_permissions WHERE admin_id=? AND permission=?");
    $s->bind_param("is",$aid,$permission); $s->execute();
    return $s->get_result()->fetch_assoc()['c']>0;
}
function getAdminPermissions($adminId){
    global $conn; $perms=[];
    $s=$conn->prepare("SELECT permission FROM admin_permissions WHERE admin_id=?");
    $s->bind_param("i",$adminId); $s->execute(); $r=$s->get_result();
    while($row=$r->fetch_assoc()) $perms[]=$row['permission'];
    return $perms;
}
function hasAnyPermission($perms){ foreach($perms as $p){if(hasPermission($p))return true;} return false; }
function hasAllPermissions($perms){ foreach($perms as $p){if(!hasPermission($p))return false;} return true; }
function requirePermission($perm,$to='dashboard.php',$msg=null){
    if(!hasPermission($perm)){setFlash('error',$msg??"No permission.");redirect($to);exit;}
}
function requireSuperAdmin($to='dashboard.php'){
    if(($_SESSION['admin_role']??'')==='super_admin') return;
    setFlash('error','Super admins only.'); redirect($to); exit;
}
function canDeleteUser($user){
    if(hasPermission('delete_users')) return true;
    if(hasPermission('delete_inactive_only')&&($user['status']??'')==='inactive') return true;
    return false;
}
function getPermissionName($p){
    $names=['manage_users'=>'Manage Users','delete_users'=>'Delete Users','delete_inactive_only'=>'Delete Inactive Only',
        'manage_withdrawals'=>'Manage Withdrawals','manage_transactions'=>'Manage Transactions',
        'manage_content'=>'Manage Content','manage_countries'=>'Manage Countries',
        'manage_bonuses'=>'Manage Bonuses','manage_admins'=>'Manage Admins',
        'view_reports'=>'View Reports','view_only'=>'View Only','system_settings'=>'System Settings'];
    return $names[$p]??ucwords(str_replace('_',' ',$p));
}
function logAdminAction($action,$details='',$targetId=null){
    global $conn; if(!isset($_SESSION['admin_id'])) return;
    $aid=$_SESSION['admin_id']; $ip=$_SERVER['REMOTE_ADDR']??'Unknown';
    $conn->query("CREATE TABLE IF NOT EXISTS admin_logs(id INT AUTO_INCREMENT PRIMARY KEY,
        admin_id INT NOT NULL,action VARCHAR(100) NOT NULL,details TEXT,target_id INT,
        ip_address VARCHAR(45),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_admin_id(admin_id),INDEX idx_action(action),INDEX idx_created_at(created_at))");
    $s=$conn->prepare("INSERT INTO admin_logs(admin_id,action,details,target_id,ip_address)VALUES(?,?,?,?,?)");
    $s->bind_param("issis",$aid,$action,$details,$targetId,$ip); $s->execute();
}

// ── TASK WALLET FUNCTIONS ─────────────────────────────────────────────────
function getOrCreateTaskWallet($userId,$taskId){
    global $conn;
    $s=$conn->prepare("SELECT * FROM task_wallets WHERE user_id=? AND task_id=?");
    $s->bind_param("ii",$userId,$taskId); $s->execute();
    if($w=$s->get_result()->fetch_assoc()) return $w;
    $ti=$conn->query("SELECT title,category FROM tasks WHERE id=$taskId")->fetch_assoc();
    $tn=($ti['category']??'Task').' Tasks';
    $s=$conn->prepare("INSERT INTO task_wallets(user_id,task_id,task_name,balance)VALUES(?,?,?,0.00)");
    $s->bind_param("iis",$userId,$taskId,$tn); $s->execute();
    return['id'=>$conn->insert_id,'user_id'=>$userId,'task_id'=>$taskId,'task_name'=>$tn,'balance'=>0.00];
}
function creditTaskWallet($userId,$taskId,$amount,$description='',$reference=null){
    global $conn;
    getOrCreateTaskWallet($userId,$taskId);
    $s=$conn->prepare("UPDATE task_wallets SET balance=balance+? WHERE user_id=? AND task_id=?");
    $s->bind_param("dii",$amount,$userId,$taskId); $ok=$s->execute();
    if($ok&&$reference){
        $t=$conn->prepare("INSERT INTO transactions(user_id,type,amount,description,reference,status)
            VALUES(?,'credit',?,?,?,'completed')");
        $t->bind_param("idss",$userId,$amount,$description,$reference); $t->execute();
    }
    return $ok;
}
function getTotalTaskWalletBalance($userId){
    global $conn;
    $s=$conn->prepare("SELECT COALESCE(SUM(balance),0) as t FROM task_wallets WHERE user_id=?");
    $s->bind_param("i",$userId); $s->execute();
    return floatval($s->get_result()->fetch_assoc()['t']);
}

// ── BALANCE RECALCULATION ─────────────────────────────────────────────────
function recalcAllWalletBalances(){
    $conn=getConnection();
    $sum='COALESCE('.implode(',0)+COALESCE(',array_values(WALLET_COLUMNS)).',0)';
    $conn->query("UPDATE wallets SET balance={$sum},
        total_earned=GREATEST(COALESCE(total_earned,0),{$sum}),updated_at=NOW()");
    return $conn->affected_rows;
}

// ── DIAGNOSTIC TOOLS ─────────────────────────────────────────────────────
function diagnoseCountryMismatches($pdo){
    return $pdo->query("SELECT u.id,u.username,u.country as user_country,c.name as matched_country,
        c.id as country_id,(SELECT COUNT(*) FROM users WHERE referred_by=u.id) as total_downlines
        FROM users u LEFT JOIN countries c ON LOWER(TRIM(u.country))=LOWER(TRIM(c.name))
        WHERE c.id IS NULL AND u.country IS NOT NULL AND u.country!=''
        ORDER BY total_downlines DESC")->fetchAll(PDO::FETCH_ASSOC);
}
function fixCountryNames($pdo){
    return $pdo->query("UPDATE users u INNER JOIN countries c ON LOWER(TRIM(u.country))=LOWER(TRIM(c.name))
        SET u.country=c.name WHERE u.country!=c.name")->rowCount();
}
function diagnoseCommissionGaps($pdo){
    return $pdo->query("SELECT c.id as country_id,c.name as country_name,
        GROUP_CONCAT(cl.level ORDER BY cl.level) as configured_levels,
        (SELECT MAX(level) FROM commission_levels) as max_level_overall
        FROM countries c LEFT JOIN commission_levels cl ON c.id=cl.country_id
        GROUP BY c.id,c.name ORDER BY c.name")->fetchAll(PDO::FETCH_ASSOC);
}
function migrateOldWalletStructure($pdo){
    $log=[];
    if(!$pdo->query("SHOW COLUMNS FROM wallets LIKE 'type'")->rowCount()){$log[]="Already new structure.";return $log;}
    $old=$pdo->query("SELECT COUNT(*) as c FROM wallets WHERE type IS NOT NULL AND type!=''")->fetch()['c']??0;
    if(!$old){$log[]="Nothing to migrate.";return $log;}
    $log[]="Migrating $old rows..."; $users=$pdo->query("SELECT DISTINCT user_id FROM wallets WHERE type IS NOT NULL AND type!=''")->fetchAll(PDO::FETCH_COLUMN);
    $mig=0;
    foreach($users as $uid){
        $ow=$pdo->prepare("SELECT type,balance FROM wallets WHERE user_id=? AND type IS NOT NULL AND type!=''");
        $ow->execute([$uid]); $rows=$ow->fetchAll();
        ensureWalletExistsPDO($pdo,$uid);
        foreach($rows as $r){
            $col=getWalletColumn($r['type']);
            if($col&&floatval($r['balance'])>0)
                $pdo->prepare("UPDATE wallets SET {$col}=COALESCE({$col},0)+?,updated_at=NOW()
                    WHERE user_id=? AND (type IS NULL OR type='')")->execute([floatval($r['balance']),$uid]);
        }
        $pdo->prepare("DELETE FROM wallets WHERE user_id=? AND type IS NOT NULL AND type!=''")->execute([$uid]);
        recalcWalletBalancePDO($pdo,$uid); $mig++;
    }
    $log[]="Migrated $mig users.";
    return $log;
}

register_shutdown_function(function(){ if(ob_get_level()) ob_end_flush(); });