<?php
// Database connection
$servername = "localhost";
$username_db = "templesm_longsmm";
$password_db = "templesm_longsmm";
$dbname = "templesm_longsmm";

// Create connection
$conn = new mysqli($servername, $username_db, $password_db, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Select all payment records where payment_status = 'SUCCESS' AND topup_status = 'PENDING'
$sql = "SELECT tran_id, username, amount, reference FROM payment_qrcode WHERE payment_status = 'SUCCESS' AND topup_status = 'PENDING'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Loop through each transaction
    while($row = $result->fetch_assoc()) {
        $tran_id = $row['tran_id'];
        $username = $row['username'];
        $amount = $row['amount'];
        $reference = $row['reference'];

        // Check if the transaction has already been processed (by checking if reference is already filled)
        if (!empty($reference)) {
            // Update topup_status to 'SUCCESS' if reference already exists
            $update_status_sql = "UPDATE payment_qrcode SET topup_status = 'SUCCESS' WHERE tran_id = ?";
            $stmt = $conn->prepare($update_status_sql);
            $stmt->bind_param("s", $tran_id);
            $stmt->execute();
            $stmt->close();
        } else {
            // Update client balance where username matches
            $update_balance_sql = "UPDATE clients SET balance = balance + ? WHERE username = ?";
            $stmt = $conn->prepare($update_balance_sql);
            $stmt->bind_param("ds", $amount, $username);
            if ($stmt->execute()) {
                // If balance update is successful, update topup_status to 'SUCCESS' and set reference as tran_id
                $update_status_sql = "UPDATE payment_qrcode SET topup_status = 'SUCCESS', reference = ? WHERE tran_id = ?";
                $update_stmt = $conn->prepare($update_status_sql);
                $update_stmt->bind_param("ss", $tran_id, $tran_id);
                $update_stmt->execute();
                $update_stmt->close();
            }
            $stmt->close();
        }
    }
} else {
    echo "No pending topups found.";
}

// Close the connection
$conn->close();
?>
