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

// From Sell Database
$item_types_with_sales = [];
$sql = "SELECT sum(total) as total_sell, banking FROM ".$sell_data." GROUP BY banking";
$result = $connection->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $total_sell = $row['total_sell'];
        $banking_data = $row['banking'];

        // Update the category with the total sell
        $query = "UPDATE ".$banking." SET sell='$total_sell' WHERE type = '$banking_data'";
        if ($connection->query($query)) {
            //echo "Updated $banking with $total_sell sales.<br>";
        } else {
            //echo "Failed to update $banking: " . $connection->error . "<br>";
        }

        // Add the item type to the array of item types with sales
        $item_types_with_sales[] = $banking_data;
    }
} else {
    //echo "No sales data found.<br>";
}

// Set categories with no sales to zero
if (!empty($item_types_with_sales)) {
    $query = "UPDATE ".$banking." SET sell='0' WHERE type NOT IN ('" . implode("','", $item_types_with_sales) . "')";
    if ($connection->query($query)) {
        //echo "Updated categories with no sales to zero.<br>";
    } else {
        //echo "Failed to update categories with no sales: " . $connection->error . "<br>";
    }
} else {
    // If there were no sales, set all categories to zero
    $query = "UPDATE ".$banking." SET sell='0'";
    if ($connection->query($query)) {
        //echo "Set all categories to zero as there were no sales.<br>";
    } else {
        //echo "Failed to set all categories to zero: " . $connection->error . "<br>";
    }
}

// From banking Refill Database
$item_types_with_refill = [];
$sql = "SELECT sum(amount) as total_refill, type FROM ".$banking_refill." GROUP BY type";
$result = $connection->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $total_refill = $row['total_refill'];
        $banking_data = $row['type'];

        // Update the category with the total sell
        $query = "UPDATE ".$banking." SET cash_in='$total_refill' WHERE type = '$banking_data'";
        if ($connection->query($query)) {
            //echo "Updated $banking with $total_sell sales.<br>";
        } else {
            //echo "Failed to update $banking: " . $connection->error . "<br>";
        }

        // Add the item type to the array of item types with sales
        $item_types_with_refill[] = $banking_data;
    }
} else {
    //echo "No sales data found.<br>";
}

// Set categories with no sales to zero
if (!empty($item_types_with_refill)) {
    $query = "UPDATE ".$banking." SET cash_in='0' WHERE type NOT IN ('" . implode("','", $item_types_with_refill) . "')";
    if ($connection->query($query)) {
        //echo "Updated categories with no sales to zero.<br>";
    } else {
        //echo "Failed to update categories with no sales: " . $connection->error . "<br>";
    }
} else {
    // If there were no sales, set all categories to zero
    $query = "UPDATE ".$banking." SET cash_in='0'";
    if ($connection->query($query)) {
        //echo "Set all categories to zero as there were no sales.<br>";
    } else {
        //echo "Failed to set all categories to zero: " . $connection->error . "<br>";
    }
}


// From expense Database
$item_types_with_expense = [];
$sql = "SELECT sum(total) as total_expense, banking FROM ".$expense." GROUP BY banking";
$result = $connection->query($sql);

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

        // Update the category with the total sell
        $query = "UPDATE ".$banking." SET expense='$total_expense' WHERE type = '$banking_data'";
        if ($connection->query($query)) {
            //echo "Updated $banking with $total_sell sales.<br>";
        } else {
            //echo "Failed to update $banking: " . $connection->error . "<br>";
        }

        // Add the item type to the array of item types with sales
        $item_types_with_expense[] = $banking_data;
    }
} else {
    //echo "No sales data found.<br>";
}

// Set categories with no sales to zero
if (!empty($item_types_with_expense)) {
    $query = "UPDATE ".$banking." SET expense='0' WHERE type NOT IN ('" . implode("','", $item_types_with_expense) . "')";
    if ($connection->query($query)) {
        //echo "Updated categories with no sales to zero.<br>";
    } else {
        //echo "Failed to update categories with no sales: " . $connection->error . "<br>";
    }
} else {
    // If there were no sales, set all categories to zero
    $query = "UPDATE ".$banking." SET expense='0'";
    if ($connection->query($query)) {
        //echo "Set all categories to zero as there were no sales.<br>";
    } else {
        //echo "Failed to set all categories to zero: " . $connection->error . "<br>";
    }
}



//calculate banking remain
$query = "UPDATE " . $banking . " SET remain = (cash_in + sell) - (pay_after + pay_now + expense)";

if ($connection->query($query) === TRUE) {
    // echo "Record updated successfully";
} else {
    // echo "Error updating record: " . $connection->error;
}


?>
