<?php 
include "database/database.php";
include "database/data.php";

// Initialize an array to track accounts with expenses
$accounts_with_expenses = [];

$sql = "SELECT sum(total) as total_expense, account FROM ".$expense." GROUP BY account";
$result = $connection->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $total_expense = $row['total_expense'];
        $account_data = $row['account'];

        // Update the account with the total expense
        $query = "UPDATE ".$account." SET amount='$total_expense' WHERE account = '$account_data'";
        if ($connection->query($query)) {
            // Update success
        } else {
            // Update fail
        }

        // Add the account to the array of accounts with expenses
        $accounts_with_expenses[] = $account_data;
    }
}

// Set accounts with no expenses to zero
$query = "UPDATE ".$account." SET amount='0' WHERE account NOT IN ('" . implode("','", $accounts_with_expenses) . "')";
if ($connection->query($query)) {
    // Update success
} else {
    // Update fail
}

?>

