<?php
// db.php
$host = "localhost";
$username = "theinter_app";
$password = "Admin2022@";
$database = "theinter_app";

// Establish connection using native MySQLi
$conn = mysqli_connect($host, $username, $password, $database);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// ---------------------------------------------------------
// USER SESSION HANDLING
// Only run queries if a user is actually logged in
// ---------------------------------------------------------
$currentUser = null;
$notificationCount = 0;

if (isset($_SESSION['user_id'])) {
    $user_id = mysqli_real_escape_string($conn, $_SESSION['user_id']);
    
    // Fetch user data
    $query = "SELECT full_name, balance, referral_code, total_referrals, referral_earnings, referral_link_clicks FROM users WHERE id = '$user_id'";
    $result = mysqli_query($conn, $query);
    $currentUser = mysqli_fetch_assoc($result);

    // Fetch unread notifications count
    $notif_query = "SELECT COUNT(*) as count FROM notifications WHERE user_id = '$user_id' AND status = 'unread'";
    $notif_result = mysqli_query($conn, $notif_query);
    $notif_data = mysqli_fetch_assoc($notif_result);
    $notificationCount = $notif_data['count'] ?? 0;
}

// Set fallbacks if no user is found (or not logged in)
if (!$currentUser) {
    $currentUser = [
        'full_name' => 'Guest', 
        'balance' => 0.00,
        'referral_code' => '',
        'total_referrals' => 0,
        'referral_earnings' => 0.00,
        'referral_link_clicks' => 0
    ];
}

// ---------------------------------------------------------
// REFERRAL HELPER FUNCTIONS
// ---------------------------------------------------------

function getReferralLink($conn, $userId) {
    $userId = intval($userId);
    $sql = "SELECT referral_code FROM users WHERE id = $userId LIMIT 1";
    $result = mysqli_query($conn, $sql);
    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        $code = $row['referral_code'];
        if (empty($code)) {
            $code = generateReferralCode($conn);
            mysqli_query($conn, "UPDATE users SET referral_code = '$code' WHERE id = $userId");
        }
        $protocol = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? 'https' : 'http';
        $host = $_SERVER['HTTP_HOST'];
        return $protocol . '://' . $host . '/register.php?ref=' . $code;
    }
    return '';
}

function generateReferralCode($conn) {
    $chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';
    do {
        $code = '';
        for ($i = 0; $i < 8; $i++) {
            $code .= $chars[random_int(0, strlen($chars) - 1)];
        }
        $checkSql = "SELECT id FROM users WHERE referral_code = '$code' LIMIT 1";
        $checkResult = mysqli_query($conn, $checkSql);
    } while ($checkResult && mysqli_num_rows($checkResult) > 0);
    return $code;
}

function processReferralCommission($conn, $userId, $amount, $type = 'deposit_bonus') {
    $userId = intval($userId);
    $userSql = "SELECT referred_by FROM users WHERE id = $userId LIMIT 1";
    $userResult = mysqli_query($conn, $userSql);
    
    if (!$userResult || mysqli_num_rows($userResult) === 0) return;
    
    $user = mysqli_fetch_assoc($userResult);
    $referrerId = intval($user['referred_by'] ?? 0);
    
    if (!$referrerId) return;
    
    // Determine commission rate based on tier
    $tierSql = "SELECT total_referrals FROM users WHERE id = $referrerId LIMIT 1";
    $tierResult = mysqli_query($conn, $tierSql);
    $tierData = mysqli_fetch_assoc($tierResult);
    $totalRefs = intval($tierData['total_referrals'] ?? 0);
    
    $rate = 0.10;
    if ($totalRefs >= 50) $rate = 0.15;
    elseif ($totalRefs >= 25) $rate = 0.12;
    elseif ($totalRefs >= 10) $rate = 0.10;
    elseif ($totalRefs >= 5) $rate = 0.08;
    else $rate = 0.05;
    
    $commission = $amount * $rate;
    
    $desc = $type === 'deposit_bonus' ? 'Deposit commission' : 'Trading commission';
    $commissionSql = "INSERT INTO referral_earnings 
        (referrer_id, referred_id, amount, type, description, status, paid_at) 
        VALUES ($referrerId, $userId, $commission, '$type', '$desc from user #$userId', 'paid', NOW())";
    mysqli_query($conn, $commissionSql);
    
    $updateSql = "UPDATE users SET referral_earnings = referral_earnings + $commission WHERE id = $referrerId";
    mysqli_query($conn, $updateSql);
    
    $trackUpdateSql = "UPDATE referral_tracking SET 
        earnings = earnings + $commission,
        status = 'active'
        WHERE referrer_id = $referrerId AND referred_id = $userId";
    mysqli_query($conn, $trackUpdateSql);
    
    // Process multi-level
    processMultiLevelCommission($conn, $referrerId, $userId, $amount, $type);
}

function processMultiLevelCommission($conn, $referrerId, $originalUserId, $amount, $type) {
    $referrerId = intval($referrerId);
    $originalUserId = intval($originalUserId);
    
    $tier2Sql = "SELECT referred_by FROM users WHERE id = $referrerId LIMIT 1";
    $tier2Result = mysqli_query($conn, $tier2Sql);
    
    if ($tier2Result && mysqli_num_rows($tier2Result) > 0) {
        $tier2Data = mysqli_fetch_assoc($tier2Result);
        $tier2Id = intval($tier2Data['referred_by'] ?? 0);
        
        if ($tier2Id) {
            $tier2Commission = $amount * 0.05;
            $tier2Desc = 'Tier 2 ' . ($type === 'deposit_bonus' ? 'deposit' : 'trading') . ' commission';
            
            $tier2Insert = "INSERT INTO referral_earnings 
                (referrer_id, referred_id, amount, type, description, status, paid_at) 
                VALUES ($tier2Id, $originalUserId, $tier2Commission, '{$type}_tier2', '$tier2Desc', 'paid', NOW())";
            mysqli_query($conn, $tier2Insert);
            
            $tier2Update = "UPDATE users SET referral_earnings = referral_earnings + $tier2Commission WHERE id = $tier2Id";
            mysqli_query($conn, $tier2Update);
            
            $tier3Sql = "SELECT referred_by FROM users WHERE id = $tier2Id LIMIT 1";
            $tier3Result = mysqli_query($conn, $tier3Sql);
            
            if ($tier3Result && mysqli_num_rows($tier3Result) > 0) {
                $tier3Data = mysqli_fetch_assoc($tier3Result);
                $tier3Id = intval($tier3Data['referred_by'] ?? 0);
                
                if ($tier3Id) {
                    $tier3Commission = $amount * 0.02;
                    $tier3Desc = 'Tier 3 ' . ($type === 'deposit_bonus' ? 'deposit' : 'trading') . ' commission';
                    
                    $tier3Insert = "INSERT INTO referral_earnings 
                        (referrer_id, referred_id, amount, type, description, status, paid_at) 
                        VALUES ($tier3Id, $originalUserId, $tier3Commission, '{$type}_tier3', '$tier3Desc', 'paid', NOW())";
                    mysqli_query($conn, $tier3Insert);
                    
                    $tier3Update = "UPDATE users SET referral_earnings = referral_earnings + $tier3Commission WHERE id = $tier3Id";
                    mysqli_query($conn, $tier3Update);
                }
            }
        }
    }
}
?>