
<?php 

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

$connection->begin_transaction(); // Start a transaction

// Fetch product names in sell_data table
$product_name_list = [];
$sql = "SELECT product_name FROM " . $sell_data;
$result = $connection->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $product_name_list[] = $row['product_name'];
    }
}

// Prepare the product name list string for the IN clause
if (!empty($product_name_list)) {
    $product_name_list_string = "'" . implode("','", array_map([$connection, 'real_escape_string'], $product_name_list)) . "'";
    $update_query = "
        UPDATE $product
        SET package_sell = '0', unit_sell = '0', pieces_sell = '0',
            package_exist = '0', unit_exist = '0', pieces_exist = '0',
            package_remain = '0', unit_remain = '0', pieces_remain = '0',
            package_damage = '0', unit_damage = '0', pieces_damage = '0',
            package_change = '0', unit_change = '0', pieces_change = '0',
            package_trans = '0', unit_trans = '0', pieces_trans = '0'
        WHERE product_name NOT IN ($product_name_list_string)
    ";

    if (!$connection->query($update_query)) {
        $connection->rollback();
        die("ERROR: Update query failed. " . $connection->error . " | Query: " . $update_query);
    }
} else {
    $update_query = "
        UPDATE $product
        SET package_sell = '0', unit_sell = '0', pieces_sell = '0',
            package_exist = '0', unit_exist = '0', pieces_exist = '0',
            package_remain = '0', unit_remain = '0', pieces_remain = '0',
            package_damage = '0', unit_damage = '0', pieces_damage = '0',
            package_change = '0', unit_change = '0', pieces_change = '0',
            package_trans = '0', unit_trans = '0', pieces_trans = '0'
    ";

    if (!$connection->query($update_query)) {
        $connection->rollback();
        die("ERROR: Update query failed. " . $connection->error . " | Query: " . $update_query);
    }
}

// Commit the transaction for this section
$connection->commit();
$connection->begin_transaction(); // Start a new transaction for the next set of queries

// Optimize by batching multiple updates into a single query per case if possible
$queries = [
    // Update pieces_sell, unit_sell, package_sell
    "UPDATE $product p
     LEFT JOIN (SELECT product_name, SUM(CASE WHEN type='ဗူး/ခု' THEN qty ELSE 0 END) AS pieces_sell,
                        SUM(CASE WHEN type='ကဒ်' THEN qty ELSE 0 END) AS unit_sell,
                        SUM(CASE WHEN type='ဖာ' THEN qty ELSE 0 END) AS package_sell
                 FROM $sell_data
                 GROUP BY product_name) s 
     ON p.product_name = s.product_name
     SET p.pieces_sell = COALESCE(s.pieces_sell, 0),
         p.unit_sell = COALESCE(s.unit_sell, 0),
         p.package_sell = COALESCE(s.package_sell, 0)",

    // Update package_damage, unit_damage, pieces_damage
    "UPDATE $product p
     LEFT JOIN (SELECT product_name, SUM(CASE WHEN type='ဖာ' THEN qty ELSE 0 END) AS package_damage,
                        SUM(CASE WHEN type='ကဒ်' THEN qty ELSE 0 END) AS unit_damage,
                        SUM(CASE WHEN type='ဗူး' THEN qty ELSE 0 END) AS pieces_damage
                 FROM $product_damage
                 GROUP BY product_name) d 
     ON p.product_name = d.product_name
     SET p.package_damage = COALESCE(d.package_damage, 0),
         p.unit_damage = COALESCE(d.unit_damage, 0),
         p.pieces_damage = COALESCE(d.pieces_damage, 0)",

    // Update pieces_remain, unit_remain, package_remain
    "UPDATE $product p
     LEFT JOIN (SELECT product_name, SUM(CASE WHEN type='pieces' THEN qty ELSE 0 END) AS pieces_remain,
                        SUM(CASE WHEN type='unit' THEN qty ELSE 0 END) AS unit_remain,
                        SUM(CASE WHEN type='package' THEN qty ELSE 0 END) AS package_remain
                 FROM $product_refill
                 GROUP BY product_name) r 
     ON p.product_name = r.product_name
     SET p.pieces_remain = COALESCE(r.pieces_remain, 0),
         p.unit_remain = COALESCE(r.unit_remain, 0),
         p.package_remain = COALESCE(r.package_remain, 0)",

    // Update unit_change, pieces_change
    "UPDATE $product p
     LEFT JOIN (SELECT name, SUM(unit) AS total_unit, SUM(pieces) AS total_pieces 
                 FROM $product_change
                 GROUP BY name) c 
     ON p.product_name = c.name
     SET p.unit_change = COALESCE(c.total_unit, 0),
         p.pieces_change = COALESCE(c.total_pieces, 0)",

    // Update pieces_trans, unit_trans, package_trans
    "UPDATE $product p
     LEFT JOIN (SELECT product_name, SUM(CASE WHEN type='ဗူး/ခု' THEN qty ELSE 0 END) AS pieces_trans,
                        SUM(CASE WHEN type='ကဒ်' THEN qty ELSE 0 END) AS unit_trans,
                        SUM(CASE WHEN type='ဖာ' THEN qty ELSE 0 END) AS package_trans
                 FROM {$cargo_1}_trans
                 WHERE accept != 'none'
                 GROUP BY product_name) t 
     ON p.product_name = t.product_name
     SET p.pieces_trans = COALESCE(t.pieces_trans, 0),
         p.unit_trans = COALESCE(t.unit_trans, 0),
         p.package_trans = COALESCE(t.package_trans, 0)"
];

foreach ($queries as $query) {
    if (!$connection->query($query)) {
        $connection->rollback(); // Rollback transaction on failure
        die("ERROR: Query failed. " . $connection->error . " | Query: " . $query);
    }
}

$final_update_query = "UPDATE $product SET 
    package_exist = (package_remain + package_trans) - (package_sell + package_damage + unit_change),
    unit_exist = (unit_remain + unit_trans) - (unit_sell + unit_damage) + (unit_change * unit) - pieces_change,
    pieces_exist = (pieces_remain + pieces_trans) - (pieces_sell + pieces_damage) + (pieces_change * pieces)";

if (!$connection->query($final_update_query)) {
    $connection->rollback(); // Rollback transaction on failure
    die("ERROR: Final Update Query failed. " . $connection->error . " | Query: " . $final_update_query);
}

$connection->commit(); // Final commit


?>
