<?php
// =================== DB CONNECTION ===================
$host = "localhost";
$user = "root";
$pass = "";
$db   = "mpay";

$conn = new mysqli($host, $user, $pass, $db);
if ($conn->connect_error) {
    die("DB Connection failed: " . $conn->connect_error);
}

// helper: generate unique ID
function uuid() {
    return bin2hex(random_bytes(16));
}

// =================== FUNCTIONS ===================
function registerUser($fullname, $email, $phone, $pin, $referralCode = null) {
    global $conn;

    $pinHash = password_hash($pin, PASSWORD_DEFAULT);
    $refCode = strtoupper(substr(md5(time() . $fullname), 0, 8));

    // find referrer by referral code
    $referredBy = null;
    if (!empty($referralCode)) {
        $findRef = $conn->prepare("SELECT id FROM users WHERE referral_code = ?");
        $findRef->bind_param("s", $referralCode);
        $findRef->execute();
        $refResult = $findRef->get_result();
        if ($refResult->num_rows > 0) {
            $refData = $refResult->fetch_assoc();
            $referredBy = $refData['id'];
        }
    }

    // insert new user
    $stmt = $conn->prepare("INSERT INTO users (fullname, email, phone, pin, password, referral_code, referred_by) 
                            VALUES (?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("ssssssi", $fullname, $email, $phone, $pin, $pinHash, $refCode, $referredBy);

    if ($stmt->execute()) {
        return [
            "status" => "success",
            "message" => "Account created successfully",
            "referral_code" => $refCode
        ];
    } else {
        return [
            "status" => "error",
            "message" => "Registration failed: " . $stmt->error
        ];
    }
}

// 2. Login
function loginUser($phone, $pin) {
    global $conn;

    $stmt = $conn->prepare("SELECT * FROM users WHERE phone = ?");
    $stmt->bind_param("s", $phone);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();

    if ($result && password_verify($pin, $result['password'])) {
        return $result; // user data
    }
    return false;
}
function changePassword($userId, $oldPin, $newPin) {
    global $conn;

    // Step 1: Get the current password hash
    $stmt = $conn->prepare("SELECT password FROM users WHERE id = ?");
    $stmt->bind_param("i", $userId);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows === 0) {
        return [
            "status" => "error",
            "message" => "User not found"
        ];
    }

    $row = $result->fetch_assoc();
    $currentHash = $row['password'];

    // Step 2: Verify old pin against stored hash
    if (!password_verify($oldPin, $currentHash)) {
        return [
            "status" => "error",
            "message" => "Old pin is incorrect"
        ];
    }

    // Step 3: Hash the new pin
    $newHash = password_hash($newPin, PASSWORD_DEFAULT);

    // Step 4: Update pin and password
    $update = $conn->prepare("UPDATE users SET pin = ?, password = ? WHERE id = ?");
    $update->bind_param("ssi", $newPin, $newHash, $userId);

    if ($update->execute()) {
        return [
            "status" => "success",
            "message" => "Password changed successfully"
        ];
    } else {
        return [
            "status" => "error",
            "message" => "Failed to change password: " . $update->error
        ];
    }
}

// Fixed buyPlan function - plans don't expire, permanent ownership
function buyPlan($userId, $planId) {
    global $conn;

    // Get plan details
    $planStmt = $conn->prepare("SELECT * FROM plans WHERE id = ? AND status = 'active'");
    $planStmt->bind_param("i", $planId);
    $planStmt->execute();
    $plan = $planStmt->get_result()->fetch_assoc();
    
    if (!$plan) {
        return ["status" => "error", "message" => "Plan not found or inactive"];
    }

    // Check if user already owns this plan (prevent duplicate purchases)
  
    if (userOwnsPlan($userId, $planId)) {
        return ["status" => "error", "message" => "You already own this plan"];
    }

    // Check if user has sufficient balance
    $userStmt = $conn->prepare("SELECT balance FROM users WHERE id = ?");
    $userStmt->bind_param("i", $userId);
    $userStmt->execute();
    $user = $userStmt->get_result()->fetch_assoc();
    
    if (!$user || $user['balance'] < $plan['price']) {
        return ["status" => "error", "message" => "Insufficient balance"];
    }

    // Begin transaction
    $conn->begin_transaction();

    try {
        // 1. Deduct from user balance
        $stmt = $conn->prepare("UPDATE users SET balance = balance - ? WHERE id = ?");
        $stmt->bind_param("di", $plan['price'], $userId);
        $stmt->execute();

        // 2. Create transaction record
        $ref = uuid();
        $stmt = $conn->prepare("INSERT INTO transactions (user_id, type, amount, reference, status) 
                               VALUES (?, 'purchase', ?, ?, 'success')");
        $stmt->bind_param("ids", $userId, $plan['price'], $ref);
        $stmt->execute();

        // 3. Add to user_plans table (NO EXPIRATION - permanent ownership)
        $stmt = $conn->prepare("INSERT INTO user_plans (user_id, plan_id, transaction_reference, expires_at, status) 
                               VALUES (?, ?, ?, NULL, 'active')");
        $stmt->bind_param("iis", $userId, $planId, $ref);
        $stmt->execute();

        // 4. Pay referral bonus
        payReferralBonus($userId, $planId, $plan['price']);

        $conn->commit();
        
        return [
            "status" => "success",
            "message" => "Plan purchased successfully! You now have permanent access.",
            "reference" => $ref,
            "plan_name" => $plan['name']
        ];

    } catch (Exception $e) {
        $conn->rollback();
        return ["status" => "error", "message" => "Purchase failed: " . $e->getMessage()];
    }
}


// Get user's plan history (all purchased plans)
function getUserPlanHistory($userId, $page = 1, $limit = 20) {
    global $conn;
    
    $offset = ($page - 1) * $limit;
    
    $stmt = $conn->prepare("SELECT up.*, p.name, p.description, p.price,
                               up.purchased_at, up.status as ownership_status
                           FROM user_plans up
                           JOIN plans p ON up.plan_id = p.id
                           WHERE up.user_id = ?
                           ORDER BY up.purchased_at DESC
                           LIMIT ? OFFSET ?");
    $stmt->bind_param("iii", $userId, $limit, $offset);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $plans = [];
    while ($row = $result->fetch_assoc()) {
        $row['is_permanent'] = true;
        $row['is_expired'] = false; // Plans never expire
        $plans[] = $row;
    }
    
    // Get total count
    $countStmt = $conn->prepare("SELECT COUNT(*) as total FROM user_plans WHERE user_id = ?");
    $countStmt->bind_param("i", $userId);
    $countStmt->execute();
    $totalCount = $countStmt->get_result()->fetch_assoc()['total'];
    
    return [
        "plans" => $plans,
        "pagination" => [
            "current_page" => $page,
            "total_pages" => ceil($totalCount / $limit),
            "total_records" => $totalCount,
            "per_page" => $limit
        ]
    ];
}

// Get plan powers that user has access to (based on owned plans)
function getUserAvailablePowers($userId) {
    global $conn;
    
    $stmt = $conn->prepare("SELECT pp.*, p.name as plan_name, up.purchased_at
                           FROM plan_powers pp
                           JOIN plans p ON pp.plan_id = p.id
                           JOIN user_plans up ON p.id = up.plan_id
                           WHERE up.user_id = ? AND up.status = 'active'
                           ORDER BY pp.reward ASC");
    $stmt->bind_param("i", $userId);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $powers = [];
    while ($row = $result->fetch_assoc()) {
        $row['is_permanent'] = true;
        $powers[] = $row;
    }
    
    return $powers;
}

// Fixed referral bonus function
function payReferralBonus($userId, $planId, $amount) {
    global $conn;
    
    // Get user details
    $userStmt = $conn->prepare("SELECT referred_by FROM users WHERE id = ?");
    $userStmt->bind_param("i", $userId);
    $userStmt->execute();
    $user = $userStmt->get_result()->fetch_assoc();
    
    if (!$user || !$user['referred_by']) {
        return; // No referrer
    }
    
    // Find referrer by their referral code
    $referrerStmt = $conn->prepare("SELECT id FROM users WHERE referral_code = ?");
    $referrerStmt->bind_param("s", $user['referred_by']);
    $referrerStmt->execute();
    $referrer = $referrerStmt->get_result()->fetch_assoc();

    if ($referrer) {
        $bonus = $amount * 0.10; // 10%
        
        // Update referrer's withdrawable balance
        $updateStmt = $conn->prepare("UPDATE users SET withdrawable_balance = withdrawable_balance + ? WHERE id = ?");
        $updateStmt->bind_param("di", $bonus, $referrer['id']);
        $updateStmt->execute();
        
        // Record referral earning
        $earningStmt = $conn->prepare("INSERT INTO referral_earnings (user_id, referred_user_id, plan_id, amount) VALUES (?, ?, ?, ?)");
        $earningStmt->bind_param("iiid", $referrer['id'], $userId, $planId, $bonus);
        $earningStmt->execute();
        
        // Record transaction
        $transStmt = $conn->prepare("INSERT INTO transactions (user_id, type, amount, reference, status) VALUES (?, 'referral_bonus', ?, ?, 'success')");
        $ref = uuid();
        $transStmt->bind_param("ids", $referrer['id'], $bonus, $ref);
        $transStmt->execute();
    }
}




// Updated plan statistics
function getPlanStatistics($planId = null) {
    global $conn;
    
    $whereClause = $planId ? "WHERE up.plan_id = ?" : "";
    
    $query = "SELECT p.id, p.name, p.price,
                 COUNT(up.id) as total_purchases,
                 COUNT(CASE WHEN up.status = 'active' THEN 1 END) as active_owners,
                 SUM(p.price) as total_revenue
              FROM plans p
              LEFT JOIN user_plans up ON p.id = up.plan_id
              $whereClause
              GROUP BY p.id, p.name, p.price
              ORDER BY total_purchases DESC";
    
    if ($planId) {
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $planId);
        $stmt->execute();
        $result = $stmt->get_result();
    } else {
        $result = $conn->query($query);
    }
    
    $stats = [];
    while ($row = $result->fetch_assoc()) {
        $stats[] = $row;
    }
    
    return $planId ? ($stats[0] ?? null) : $stats;
}

// Updated function to check plan access for powers
function canUserActivatePower($userId, $planPowerId) {
    global $conn;
    
    $stmt = $conn->prepare("SELECT pp.plan_id, p.name as plan_name
                           FROM plan_powers pp
                           JOIN plans p ON pp.plan_id = p.id
                           WHERE pp.id = ?");
    $stmt->bind_param("i", $planPowerId);
    $stmt->execute();
    $power = $stmt->get_result()->fetch_assoc();
    
    if (!$power) {
        return ["can_activate" => false, "reason" => "Power not found"];
    }
    
    if (!userOwnsPlan($userId, $power['plan_id'])) {
        return [
            "can_activate" => false, 
            "reason" => "You don't own the required plan: " . $power['plan_name']
        ];
    }
    
    // Check for active runs
    $activeStmt = $conn->prepare("SELECT id FROM power_activations WHERE user_id = ? AND plan_power_id = ? AND status = 'active'");
    $activeStmt->bind_param("ii", $userId, $planPowerId);
    $activeStmt->execute();
    $activeRun = $activeStmt->get_result()->fetch_assoc();
    
    if ($activeRun) {
        return ["can_activate" => false, "reason" => "You already have an active run of this power"];
    }
    
    return ["can_activate" => true];
}





// Fetch user details by ID
function getUserById($userId) {
    global $conn;

    $stmt = $conn->prepare("SELECT id, fullname, email, phone, referral_code, referred_by, created_at 
                            FROM users 
                            WHERE id = ?");
    $stmt->bind_param("i", $userId);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();

    return $result ?: null; // returns array if found, null if not
}






// Get user transactions with pagination
function getUserTransactions($userId, $page = 1, $limit = 20, $type = null) {
    global $conn;
    
    $offset = ($page - 1) * $limit;
    $whereClause = "WHERE user_id = ?";
    $params = [$userId];
    $types = "i";
    
    if ($type) {
        $whereClause .= " AND type = ?";
        $params[] = $type;
        $types .= "s";
    }
    
    $stmt = $conn->prepare("SELECT * FROM transactions 
                           $whereClause 
                           ORDER BY created_at DESC 
                           LIMIT ? OFFSET ?");
    $params[] = $limit;
    $params[] = $offset;
    $types .= "ii";
    
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $transactions = [];
    while ($row = $result->fetch_assoc()) {
        $transactions[] = $row;
    }
    
    // Get total count
    $countStmt = $conn->prepare("SELECT COUNT(*) as total FROM transactions $whereClause");
    $countStmt->bind_param(substr($types, 0, -2), ...array_slice($params, 0, -2));
    $countStmt->execute();
    $totalCount = $countStmt->get_result()->fetch_assoc()['total'];
    
    return [
        "transactions" => $transactions,
        "pagination" => [
            "current_page" => $page,
            "total_pages" => ceil($totalCount / $limit),
            "total_records" => $totalCount,
            "per_page" => $limit
        ]
    ];
}

// Get transaction by reference
function getTransactionByReference($reference, $userId = null) {
    global $conn;
    
    $whereClause = "WHERE reference = ?";
    $params = [$reference];
    $types = "s";
    
    if ($userId) {
        $whereClause .= " AND user_id = ?";
        $params[] = $userId;
        $types .= "i";
    }
    
    $stmt = $conn->prepare("SELECT * FROM transactions $whereClause");
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    
    return $result ?: null;
}

// Add transaction
function addTransaction($userId, $type, $amount, $status = 'pending', $description = null) {
    global $conn;
    
    $reference = uuid();
    
    $stmt = $conn->prepare("INSERT INTO transactions (user_id, type, amount, reference, status, description, created_at) 
                           VALUES (?, ?, ?, ?, ?, ?, NOW())");
    $stmt->bind_param("isdsss", $userId, $type, $amount, $reference, $status, $description);
    
    if ($stmt->execute()) {
        return [
            "status" => "success",
            "message" => "Transaction recorded",
            "reference" => $reference,
            "transaction_id" => $conn->insert_id
        ];
    }
    
    return [
        "status" => "error",
        "message" => "Failed to record transaction: " . $stmt->error
    ];
}

// Update transaction status
function updateTransactionStatus($transactionId, $status, $description = null) {
    global $conn;
    
    $stmt = $conn->prepare("UPDATE transactions 
                           SET status = ?, description = COALESCE(?, description), updated_at = NOW() 
                           WHERE id = ?");
    $stmt->bind_param("ssi", $status, $description, $transactionId);
    
    return $stmt->execute();
}



// Save deposit request into transactions table with reference
// Save deposit request into transactions table with reference
function requestDeposit($user_id, $amount, $reference) {
    global $conn;

    // Begin transaction
    $conn->begin_transaction();

    try {
        // 1. Insert into transactions
        $stmt = $conn->prepare("
            INSERT INTO transactions (user_id, type, amount, status, reference, created_at) 
            VALUES (?, 'deposit', ?, 'pending', ?, NOW())
        ");
        $stmt->bind_param("ids", $user_id, $amount, $reference);
        $stmt->execute();

       
        // Commit both queries
        $conn->commit();

        return [
            "status" => "success",
            "message" => "Deposit request made successful, contact admin.",
            "reference" => $reference
        ];

    } catch (Exception $e) {
        $conn->rollback();
        return [
            "status" => "error",
            "message" => "Failed to process deposit: " . $e->getMessage()
        ];
    }
}
function requestDepositPaystack($user_id, $amount, $reference) {
    global $conn;

    // Begin transaction
    $conn->begin_transaction();

    try {
        // 1. Insert into transactions
        $stmt = $conn->prepare("
            INSERT INTO transactions (user_id, type, amount, status, reference, created_at) 
            VALUES (?, 'deposit', ?, 'success', ?, NOW())
        ");
        $stmt->bind_param("ids", $user_id, $amount, $reference);
        $stmt->execute();

        // 2. Update user's balance immediately
        $stmt = $conn->prepare("UPDATE users SET balance = balance + ? WHERE id = ?");
        $stmt->bind_param("di", $amount, $user_id);
        $stmt->execute();

        // Commit both queries
        $conn->commit();

        return [
            "status" => "success",
            "message" => "Deposit successful, balance updated.",
            "reference" => $reference
        ];

    } catch (Exception $e) {
        $conn->rollback();
        return [
            "status" => "error",
            "message" => "Failed to process deposit: " . $e->getMessage()
        ];
    }
}


// Approve deposit
function approveDeposit($transaction_id) {
    global $conn;

    // Fetch transaction details
    $stmt = $conn->prepare("SELECT user_id, amount, status, reference FROM transactions WHERE id = ? AND type = 'deposit'");
    $stmt->bind_param("i", $transaction_id);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();

    if (!$result) {
        return ["status" => "error", "message" => "Transaction not found."];
    }
    if ($result['status'] !== 'pending') {
        return ["status" => "error", "message" => "This deposit has already been processed."];
    }

    $user_id   = $result['user_id'];
    $amount    = $result['amount'];
    $reference = $result['reference'];

    // Begin transaction
    $conn->begin_transaction();

    try {
        // 1. Update user's balance
        $stmt = $conn->prepare("UPDATE users SET balance = balance + ? WHERE id = ?");
        $stmt->bind_param("di", $amount, $user_id);
        $stmt->execute();

        // 2. Update transaction status
        $stmt = $conn->prepare("UPDATE transactions SET status = 'success', created_at = NOW() WHERE id = ?");
        $stmt->bind_param("i", $transaction_id);
        $stmt->execute();

        // Commit
        $conn->commit();

        return [
            "status" => "success", 
            "message" => "Deposit approved and balance updated.", 
            "reference" => $reference
        ];

    } catch (Exception $e) {
        $conn->rollback();
        return ["status" => "error", "message" => "Failed to approve deposit: " . $e->getMessage()];
    }
}


// Get user balance summary
function getUserBalanceSummary($userId) {
    global $conn;
    
    $stmt = $conn->prepare("SELECT 
                               balance,
                               withdrawable_balance,
                               (SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE user_id = ? AND type IN ('deposit', 'referral_bonus', 'power_reward') AND status = 'success') as total_earned,
                               (SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE user_id = ? AND type IN ('withdrawal', 'plan_purchase') AND status = 'success') as total_spent,
                               (SELECT COUNT(*) FROM transactions WHERE user_id = ? AND status = 'pending') as pending_transactions
                           FROM users WHERE id = ?");
    $stmt->bind_param("iiii", $userId, $userId, $userId, $userId);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    
    return $result ?: null;
}
// Fetch site information (settings)
function getSiteInfo() {
    global $conn;

    $stmt = $conn->prepare("SELECT *
                            FROM site_info 
                            LIMIT 1");
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();

    return $result ?: null; // returns array if found, null if not
}


// =================== USER PLANS FUNCTIONS ===================






// Alternative simpler version if you want to avoid joins
function getUserPowerActivationsSimple($userId, $status = null) {
    global $conn;
    
    $whereClause = "WHERE user_id = ?";
    $params = [$userId];
    $types = "i";
    
    if ($status) {
        $whereClause .= " AND status = ?";
        $params[] = $status;
        $types .= "s";
    }
    
    $stmt = $conn->prepare("SELECT * FROM power_activations $whereClause ORDER BY started_at DESC");
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $activations = [];
    while ($row = $result->fetch_assoc()) {
        // Get additional info for each activation
        $powerStmt = $conn->prepare("SELECT pp.*, p.name as plan_name 
                                    FROM plan_powers pp 
                                    JOIN plans p ON pp.plan_id = p.id 
                                    WHERE pp.id = ?");
        $powerStmt->bind_param("i", $row['plan_power_id']);
        $powerStmt->execute();
        $powerInfo = $powerStmt->get_result()->fetch_assoc();
        
        if ($powerInfo) {
            $row['power_name'] = $powerInfo['plan_name'] . ' Power';
            $row['reward'] = $powerInfo['reward'];
            $row['duration'] = $powerInfo['duration'];
            $row['plan_name'] = $powerInfo['plan_name'];
            $row['plan_id'] = $powerInfo['plan_id'];
        }
        
        $activations[] = $row;
    }
    
    return $activations;
}
// Check if user can purchase plan
function canUserPurchasePlan($userId, $planId) {
    global $conn;
    
    // Get user balance
    $userStmt = $conn->prepare("SELECT balance FROM users WHERE id = ?");
    $userStmt->bind_param("i", $userId);
    $userStmt->execute();
    $user = $userStmt->get_result()->fetch_assoc();
    
    if (!$user) {
        return ["can_purchase" => false, "reason" => "User not found"];
    }
    
    // Get plan price
    $planStmt = $conn->prepare("SELECT price, status FROM plans WHERE id = ?");
    $planStmt->bind_param("i", $planId);
    $planStmt->execute();
    $plan = $planStmt->get_result()->fetch_assoc();
    
    if (!$plan) {
        return ["can_purchase" => false, "reason" => "Plan not found"];
    }
    
    if ($plan['status'] !== 'active') {
        return ["can_purchase" => false, "reason" => "Plan is not available"];
    }
    
    if ($user['balance'] < $plan['price']) {
        return [
            "can_purchase" => false, 
            "reason" => "Insufficient balance",
            "required" => $plan['price'],
            "current_balance" => $user['balance'],
            "shortage" => $plan['price'] - $user['balance']
        ];
    }
    
    return ["can_purchase" => true, "plan" => $plan];
}

// =================== USER REFERRALS FUNCTIONS ===================

// Get user's referral statistics
function getUserReferralStats($userId) {
    global $conn;
    
    $stmt = $conn->prepare("SELECT 
                               (SELECT COUNT(*) FROM users WHERE referred_by = (SELECT referral_code FROM users WHERE id = ?)) as total_referrals,
                               (SELECT COALESCE(SUM(amount), 0) FROM referral_earnings WHERE user_id = ?) as total_earnings,
                               (SELECT COALESCE(SUM(amount), 0) FROM referral_earnings WHERE user_id = ? AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) as monthly_earnings,
                               (SELECT referral_code FROM users WHERE id = ?) as referral_code");
    $stmt->bind_param("iiii", $userId, $userId, $userId, $userId);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    
    return $result;
}

// Get user's referred users
function getUserReferrals($userId, $page = 1, $limit = 20) {
    global $conn;
    
    $offset = ($page - 1) * $limit;
    
    // Get user's referral code first
    $codeStmt = $conn->prepare("SELECT referral_code FROM users WHERE id = ?");
    $codeStmt->bind_param("i", $userId);
    $codeStmt->execute();
    $userCode = $codeStmt->get_result()->fetch_assoc()['referral_code'];
    
    if (!$userCode) {
        return ["referrals" => [], "pagination" => ["total_records" => 0]];
    }
    
    $stmt = $conn->prepare("SELECT id, fullname, email, phone, created_at
                           FROM users 
                           WHERE referred_by = ?
                           ORDER BY created_at DESC 
                           LIMIT ? OFFSET ?");
    $stmt->bind_param("sii", $userId, $limit, $offset);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $referrals = [];
    while ($row = $result->fetch_assoc()) {
        $referrals[] = $row;
    }
    
    // Get total count
    $countStmt = $conn->prepare("SELECT COUNT(*) as total FROM users WHERE referred_by = ?");
    $countStmt->bind_param("s", $userCode);
    $countStmt->execute();
    $totalCount = $countStmt->get_result()->fetch_assoc()['total'];
    
    return [
        "referrals" => $referrals,
        "pagination" => [
            "current_page" => $page,
            "total_pages" => ceil($totalCount / $limit),
            "total_records" => $totalCount,
            "per_page" => $limit
        ]
    ];
}

// Get referral earnings history
function getReferralEarnings($userId, $page = 1, $limit = 20) {
    global $conn;
    
    $offset = ($page - 1) * $limit;
    
    $stmt = $conn->prepare("SELECT re.*, u.fullname as referred_user_name, p.name as plan_name
                           FROM referral_earnings re
                           LEFT JOIN users u ON re.referred_user_id = u.id
                           LEFT JOIN plans p ON re.plan_id = p.id
                           WHERE re.user_id = ?
                           ORDER BY re.created_at DESC 
                           LIMIT ? OFFSET ?");
    $stmt->bind_param("iii", $userId, $limit, $offset);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $earnings = [];
    while ($row = $result->fetch_assoc()) {
        $earnings[] = $row;
    }
    
    // Get total count
    $countStmt = $conn->prepare("SELECT COUNT(*) as total FROM referral_earnings WHERE user_id = ?");
    $countStmt->bind_param("i", $userId);
    $countStmt->execute();
    $totalCount = $countStmt->get_result()->fetch_assoc()['total'];
    
    return [
        "earnings" => $earnings,
        "pagination" => [
            "current_page" => $page,
            "total_pages" => ceil($totalCount / $limit),
            "total_records" => $totalCount,
            "per_page" => $limit
        ]
    ];
}

// Get referral leaderboard (top referrers)
function getReferralLeaderboard($limit = 10) {
    global $conn;
    
    $stmt = $conn->prepare("SELECT u.id, u.fullname, u.referral_code,
                               (SELECT COUNT(*) FROM users WHERE referred_by = u.referral_code) as total_referrals,
                               (SELECT COALESCE(SUM(amount), 0) FROM referral_earnings WHERE user_id = u.id) as total_earnings
                           FROM users u
                           HAVING total_referrals > 0
                           ORDER BY total_referrals DESC, total_earnings DESC
                           LIMIT ?");
    $stmt->bind_param("i", $limit);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $leaderboard = [];
    $rank = 1;
    while ($row = $result->fetch_assoc()) {
        $row['rank'] = $rank++;
        $leaderboard[] = $row;
    }
    
    return $leaderboard;
}

// Validate referral code
function validateReferralCode($referralCode, $excludeUserId = null) {
    global $conn;
    
    $whereClause = "WHERE referral_code = ?";
    $params = [$referralCode];
    $types = "s";
    
    if ($excludeUserId) {
        $whereClause .= " AND id != ?";
        $params[] = $excludeUserId;
        $types .= "i";
    }
    
    $stmt = $conn->prepare("SELECT id, fullname, referral_code FROM users $whereClause");
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    
    return $result ?: null;
}

$site = getSiteInfo();

// Get user's power activations
function getUserPowerActivations($userId) {
    global $conn;
    
    $stmt = $conn->prepare("SELECT pa.*, 
                               pp.power_name, 
                               pp.reward AS power_reward, 
                               pp.min_value,
                               p.name AS plan_name,
                               p.id AS plan_id
                           FROM power_activations pa
                           JOIN plan_powers pp ON pa.plan_power_id = pp.id
                           JOIN plans p ON pp.plan_id = p.id
                           WHERE pa.user_id = ?
                           ORDER BY pa.created_at DESC");
    
    $stmt->bind_param("i", $userId);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $activations = [];
    while ($row = $result->fetch_assoc()) {
        $row['reward'] = $row['power_reward']; // keep naming consistent
        $activations[] = $row;
    }
    
    return $activations;
}


// Fixed activate power function
function activatePower($userId, $planPowerId) {
    global $conn;

    // Get power details
    $powerStmt = $conn->prepare("SELECT pp.*, p.name as plan_name 
                                FROM plan_powers pp 
                                JOIN plans p ON pp.plan_id = p.id 
                                WHERE pp.id = ?");
    $powerStmt->bind_param("i", $planPowerId);
    $powerStmt->execute();
    $power = $powerStmt->get_result()->fetch_assoc();
    
    if (!$power) {
        return ["status" => "error", "message" => "Power not found"];
    }

    // Check if user owns the plan
    if (!userOwnsPlan($userId, $power['plan_id'])) {
        return ["status" => "error", "message" => "You don't own the required plan: " . $power['plan_name']];
    }

    // Check for active power runs of the same type
    $activeStmt = $conn->prepare("SELECT id FROM power_activations WHERE user_id = ? AND plan_power_id = ? ");
    $activeStmt->bind_param("ii", $userId, $planPowerId);
    $activeStmt->execute();
    $activeRun = $activeStmt->get_result()->fetch_assoc();
    
    if ($activeRun) {
        return ["status" => "error", "message" => "You already have an active run of this power"];
    }

    // Calculate end time (default 24 hours if duration not set)
    $duration = isset($power['duration']) ? $power['duration'] : 24;
    $endTime = date("Y-m-d H:i:s", strtotime("+{$duration} hours"));

    $stmt = $conn->prepare("INSERT INTO power_activations (user_id, plan_power_id, reward, status, started_at, end_time, duration) 
                           VALUES (?, ?, ?, 'active', NOW(), ?, ?)");
    $stmt->bind_param("iidsi", $userId, $planPowerId, $power['reward'], $endTime, $duration);
    
    if ($stmt->execute()) {
        return [
            "status" => "success",
            "message" => "Power activated successfully!",
            "end_time" => $endTime,
            "power_name" => $power['power_name'],
            "reward" => $power['reward']
        ];
    }
    
    return ["status" => "error", "message" => "Failed to activate power"];
}

// Fixed complete power run function with restart
function completePowerRun($activationId) {
    global $conn;

    $activationStmt = $conn->prepare("SELECT pa.*, pp.duration 
                                      FROM power_activations pa
                                      JOIN plan_powers pp ON pa.plan_power_id = pp.id
                                      WHERE pa.id = ? AND pa.status = 'active'");
    $activationStmt->bind_param("i", $activationId);
    $activationStmt->execute();
    $activation = $activationStmt->get_result()->fetch_assoc();
    
    /*
    if (!$activation) {
        return ["status" => "error", "message" => "Activation not found or already completed"];
    }
        */

    $userId     = $activation['user_id'];
    $reward     = $activation['reward'];
    $planPowerId = $activation['plan_power_id'];
    $duration   = $activation['duration']; // duration in hours

    // Calculate new times for restart
    $startTime = date("Y-m-d H:i:s");
    $endTime   = date("Y-m-d H:i:s", strtotime("+$duration hours"));

    // Begin transaction
    $conn->begin_transaction();
    
    try {
        // 1. Mark current as completed
        $updateStmt = $conn->prepare("UPDATE power_activations 
                                      SET status = 'completed', completed_at = NOW() 
                                      WHERE id = ?");
        $updateStmt->bind_param("i", $activationId);
        $updateStmt->execute();
        
        // 2. Add to withdrawable balance
        $balanceStmt = $conn->prepare("UPDATE users 
                                       SET withdrawable_balance = withdrawable_balance + ? 
                                       WHERE id = ?");
        $balanceStmt->bind_param("di", $reward, $userId);
        $balanceStmt->execute();
        
        // 3. Record transaction
        $ref = uuid();
        $transStmt = $conn->prepare("INSERT INTO transactions 
                                     (user_id, type, reference, amount, status) 
                                     VALUES (?, 'activation', ?, ?, 'success')");
        $transStmt->bind_param("isd", $userId, $ref, $reward);
        $transStmt->execute();

        // 4. Restart a new power run using completed activation’s values
       // 4. Restart a new power run using the same values
            $restartStmt = $conn->prepare("INSERT INTO power_activations 
                (user_id, plan_power_id, reward, started_at, end_time, status, created_at) 
                VALUES (?, ?, ?, ?, ?, 'active', NOW())");

            $restartStmt->bind_param("iidss", $userId, $planPowerId, $reward, $startTime, $endTime);
            $restartStmt->execute();


        // 5. Pay referral bonus
        payReferralOnPowerRun($userId, $planPowerId, $reward);
        
        $conn->commit();
        
        return [
            "status"  => "success",
            "message" => "Power run completed! Reward added and new run started.",
            "reward"  => $reward,
            "new_run" => [
                "start" => $startTime,
                "end"   => $endTime
            ]
        ];
        
    } catch (Exception $e) {
        $conn->rollback();
        return ["status" => "error", "message" => "Failed to complete power run: " . $e->getMessage()];
    }
}


// Check if user owns a plan (permanent ownership)
function userOwnsPlan($userId, $planId) {
    global $conn;
    
    $stmt = $conn->prepare("SELECT id FROM user_plans 
                           WHERE user_id = ? AND plan_id = ? AND status = 'active'");
    $stmt->bind_param("ii", $userId, $planId);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    
    return $result ? true : false;
}

// Get user's owned plans (all are permanent)
function getUserOwnedPlans($userId) {
    global $conn;
    
    $stmt = $conn->prepare("SELECT up.*, p.name, p.description, p.price,
                               up.purchased_at, up.status as ownership_status
                           FROM user_plans up
                           JOIN plans p ON up.plan_id = p.id
                           WHERE up.user_id = ? AND up.status = 'active'
                           ORDER BY up.purchased_at DESC");
    $stmt->bind_param("i", $userId);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $plans = [];
    while ($row = $result->fetch_assoc()) {
        $row['is_permanent'] = true;
        $plans[] = $row;
    }
    
    return $plans;
}

// Get plan powers for a specific plan
function getPlanPowers($planId) {
    global $conn;
    
    $stmt = $conn->prepare("SELECT * FROM plan_powers WHERE plan_id = ? AND status = 'active' ORDER BY reward ASC");
    $stmt->bind_param("i", $planId);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $powers = [];
    while ($row = $result->fetch_assoc()) {
        $powers[] = $row;
    }
    
    return $powers;
}

// Process due powers (cron job)
function processDuePowers() {
    global $conn;
    
    $result = $conn->query("SELECT * FROM power_activations WHERE status = 'active' AND end_time <= NOW()");
    $processed = 0;
    
    while ($row = $result->fetch_assoc()) {
        $completed = completePowerRun($row['id']);
        if ($completed['status'] === 'success') {
            $processed++;
        }
    }
    
    return $processed;
}

// Fixed referral bonus on power run
function payReferralOnPowerRun($userId, $planPowerId, $reward) {
    global $conn;
    
    // Get user details
    $userStmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
    $userStmt->bind_param("i", $userId);
    $userStmt->execute();
    $user = $userStmt->get_result()->fetch_assoc();
    
    if (!$user || !$user['referred_by']) {
        return; // No referrer
    }
    
    // Find referrer
    $referrerStmt = $conn->prepare("SELECT id FROM users WHERE id = ?");
    $referrerStmt->bind_param("s", $user['referred_by']);
    $referrerStmt->execute();
    $referrer = $referrerStmt->get_result()->fetch_assoc();

    if ($referrer) {
        $bonus = $reward * 0.1;
        
        // Update referrer's withdrawable balance
        $updateStmt = $conn->prepare("UPDATE users SET withdrawable_balance = withdrawable_balance + ? WHERE id = ?");
        $updateStmt->bind_param("di", $bonus, $referrer['id']);
        $updateStmt->execute();
        
        // Record referral earning
        $earningStmt = $conn->prepare("INSERT INTO referral_earnings (user_id, referred_user_id, plan_id, amount) VALUES (?, ?, ?, ?)");
        $earningStmt->bind_param("iiid", $referrer['id'], $userId, $planPowerId, $bonus);
        $earningStmt->execute();
        
        // Record transaction
        $transStmt = $conn->prepare("INSERT INTO transactions (user_id, type, amount, reference, status) VALUES (?, 'referral', ?, ?, 'success')");
        $ref = uuid();
        $transStmt->bind_param("ids", $referrer['id'], $bonus, $ref);
        $transStmt->execute();
    }
}

// Get all available plans
function getAllPlans($active_only = true) {
    global $conn;
    
    $whereClause = $active_only ? "WHERE status = 'active'" : "";
    $result = $conn->query("SELECT * FROM plans $whereClause ORDER BY price ASC");
    
    $plans = [];
    while ($row = $result->fetch_assoc()) {
        $plans[] = $row;
    }
    
    return $plans;
}


// Process Withdrawal Request
function requestWithdrawal($user_id, $amount, $bank, $account_name, $account_number) {
    global $conn;

    // Begin transaction
    $conn->begin_transaction();

    try {
        // ✅ Check if user has enough withdrawable balance
        $stmt = $conn->prepare("SELECT withdrawable_balance FROM users WHERE id = ?");
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $user = $result->fetch_assoc();

        if (!$user || $user['withdrawable_balance'] < $amount) {
            throw new Exception("Insufficient withdrawable balance.");
        }

        // ✅ 1. Insert into withdrawals table
        $stmt = $conn->prepare("
            INSERT INTO withdrawals (user_id, amount, status, requested_at, bank, account, account_number) 
            VALUES (?, ?, 'pending', NOW(), ?, ?, ?)
        ");
        $stmt->bind_param("idsss", $user_id, $amount, $bank, $account_name, $account_number);
        $stmt->execute();
        $withdrawal_id = $stmt->insert_id;

        // ✅ 2. Insert into transactions table
        $reference = "WD-" . uniqid();
        $stmt = $conn->prepare("
            INSERT INTO transactions (user_id, type, amount, status, reference, created_at) 
            VALUES (?, 'withdrawal', ?, 'pending', ?, NOW())
        ");
        $stmt->bind_param("ids", $user_id, $amount, $reference);
        $stmt->execute();

        // ✅ 3. Deduct from user's withdrawable balance
        $stmt = $conn->prepare("UPDATE users SET withdrawable_balance = withdrawable_balance - ? WHERE id = ?");
        $stmt->bind_param("di", $amount, $user_id);
        $stmt->execute();

        // Commit all
        $conn->commit();

        return [
            "status" => "success",
            "message" => "Withdrawal request submitted successfully.",
            "withdrawal_id" => $withdrawal_id,
            "reference" => $reference
        ];

    } catch (Exception $e) {
        $conn->rollback();
        return [
            "status" => "error",
            "message" => "Failed to process withdrawal: " . $e->getMessage()
        ];
    }
}


?>