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

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $product_name = $_POST['product_name'] ?? null;
    $column = $_POST['column'] ?? null;
    $value = $_POST['value'] ?? null;

    echo $product_name;
    echo $value;

    $product_name = $connection->real_escape_string($product_name);
$column = $connection->real_escape_string($column);

// Update the $user_1_ll table
$sql = "UPDATE $product SET $column = ? WHERE product_name = ?";
$stmt = $connection->prepare($sql);
if ($stmt) {
    $stmt->bind_param('is', $value, $product_name); // Corrected data types (int for value, string for product_name)
    if ($stmt->execute()) {
        echo "Row updated successfully in $product.<br>";
    } else {
        echo "Error updating row in $product: " . $stmt->error . "<br>";
    }
    $stmt->close(); // Close the statement
} else {
    echo 'Error preparing statement for user_1_ll: ' . $connection->error . '<br>';
}

// Update the $cargo_1 table
$sql = "UPDATE $cargo_1 SET $column = ? WHERE product_name = ?";
$stmt = $connection->prepare($sql);
if ($stmt) {
    $stmt->bind_param('is', $value, $product_name); // Corrected data types
    if ($stmt->execute()) {
        echo "Row updated successfully in $cargo_1.<br>";
    } else {
        echo "Error updating row in $cargo_1: " . $stmt->error . "<br>";
    }
    $stmt->close(); // Close the statement
} else {
    echo 'Error preparing statement for cargo_1: ' . $connection->error . '<br>';
}

    function roundUpToNearestHundred($number) {
                    return ceil($number / 100) * 100;
                    }

    $sql = "SELECT * FROM $product WHERE product_name = ?";
    $stmt = $connection->prepare($sql);
    $stmt->bind_param('s', $product_name);
    $stmt->execute();
    $product_result = $stmt->get_result();

    if ($product_result && $product_result->num_rows > 0) {
        $product_row = $product_result->fetch_assoc();
        $unit = $product_row['unit'];
        $pieces = $product_row['pieces'];
        $buy = $product_row['buy'];
        $trans = $product_row['trans'];
        $currency_buy = $product_row['currency_buy'];

        // Fetch rate details (assumed to be a single record)
        $sql_rate = "SELECT * FROM $rate";
        $rate_result = $connection->query($sql_rate);

        if ($rate_result && $rate_result->num_rows > 0) {
            $rate_row = $rate_result->fetch_assoc();
            $profit = $rate_row['profit'];
            $baht = $rate_row['baht'];
            $yuan = $rate_row['yuan'];
            $dollar = $rate_row['dollar'];

            // Calculate package price based on currency
            switch ($currency_buy) {
                case 'kyat':
                    $package_price = ($buy * ($profit / 100)) + $buy + $trans;
                    break;
                case 'baht':
                    $package_price = (($buy * $baht) * ($profit / 100)) + ($buy * $baht) + $trans;
                    break;
                case 'yuan':
                    $package_price = (($buy * $yuan) * ($profit / 100)) + ($buy * $yuan) + $trans;
                    break;
                case 'dollar':
                    $package_price = (($buy * $dollar) * ($profit / 100)) + ($buy * $dollar) + $trans;
                    break;
                default:
                    echo json_encode(['status' => 'error', 'message' => 'Invalid currency']);
                    exit;
            }

            // Calculate unit and pieces price
            if ($unit > 0) {
                $unit_price = $package_price / $unit;
            } else {
                $unit_price = 0; // or handle the zero case as needed
            }
            
            if ($pieces > 0 && $unit_price > 0) {
                $pieces_price = $unit_price / $pieces;
            } else {
                $pieces_price = 0; // or handle the zero case as needed
            }

            // Round prices
            $round_package_price = roundUpToNearestHundred($package_price);
            $round_unit_price = roundUpToNearestHundred($unit_price);
            $round_pieces_price = roundUpToNearestHundred($pieces_price);

            // Update product tables
            $product_tables = [$product, $cargo_1];
            foreach ($product_tables as $product_data) {
                $sql_update = "UPDATE $product_data 
                            SET package_price = ?, 
                                unit_price = ?, 
                                pieces_price = ?, 
                                round_package_price = ?, 
                                round_unit_price = ?, 
                                round_pieces_price = ? 
                            WHERE product_name = ?";
                $stmt_update = $connection->prepare($sql_update);
                $stmt_update->bind_param('dddddds', $package_price, $unit_price, $pieces_price, $round_package_price, $round_unit_price, $round_pieces_price, $product_name);

                if ($stmt_update->execute()) {
                    echo json_encode(['status' => 'success', 'message' => 'Record updated successfully']);
                    echo "<script>location.reload();</script>";
                } else {
                    echo json_encode(['status' => 'error', 'message' => 'Error updating record: ' . $connection->error]);
                }
            }
        }
    }

    



    // Fetch and calculate prices/discounts
    $sql = "
    SELECT 
        u.tra_id, u.banking, u.product_name, u.type, u.qty, u.discount, u.rate, 
        p.round_package_price, p.round_unit_price, p.round_pieces_price
    FROM 
        $user_1_ll u
    JOIN 
        $product p 
    ON 
        u.product_name = p.product_name 
    WHERE 
        u.tra_id = ?";
    
    $stmt = $connection->prepare($sql);
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $type = $row['type'];
            $qty = $row['qty'];
            $discount = $row['discount'];
            $banking_data = $row['banking'];
            $product_name = $row['product_name'];
            $price = 0;

            // Fetch currency from banking table
            $currency_sql = "SELECT currency FROM $banking WHERE type = ?";
            $currency_stmt = $connection->prepare($currency_sql);
            $currency_stmt->bind_param("s", $banking_data);
            $currency_stmt->execute();
            $currency_result = $currency_stmt->get_result();

            if ($currency_result && $currency_result->num_rows > 0) {
                $currency_data = $currency_result->fetch_assoc();
                $currency = $currency_data['currency'];

                // Fetch rates
                $rate_sql = "SELECT baht, yuan, dollar FROM $rate";
                $rate_result = $connection->query($rate_sql);

                if ($rate_result && $rate_result->num_rows > 0) {
                    $rate_data = $rate_result->fetch_assoc();
                    $baht = $rate_data['baht'];
                    $yuan = $rate_data['yuan'];
                    $dollar = $rate_data['dollar'];

                    // Perform discount calculation
                    // switch ($currency) {
                    //     case 'kyat':
                    //         $discount = $discount_1;
                    //         break;
                    //     case 'baht':
                    //         $discount = $discount_1 * $baht;
                    //         break;
                    //     case 'yuan':
                    //         $discount = $discount_1 * $yuan;
                    //         break;
                    //     case 'dollar':
                    //         $discount = $discount_1 * $dollar;
                    //         break;
                    //     default:
                    //         $discount = 0; // Handle unexpected currency
                    //         break;
                    // }
                } else {
                    echo 'Error fetching rates from $rate table<br>';
                    $discount = 0;
                }
            } else {
                echo 'Error fetching currency from banking table<br>';
                $discount = 0;
            }

            // Determine price based on type
            switch ($type) {
                case 'ဗူး/ခု':
                    $price = round($row['round_pieces_price'] / $row['rate']);
                    break;
                case 'ကဒ်':
                    $price = round($row['round_unit_price'] / $row['rate']);
                    break;
                case 'ဖာ':
                    $price = round($row['round_package_price'] / $row['rate']);
                    break;
                default:
                    echo 'Unknown type: ' . $type . '<br>';
                    $price = 0;
                    break;
            }

            $total = $qty * ($price - $discount);

            // Update the first table
            $update_sql = "UPDATE $user_1_ll SET price = ?, total = ? WHERE tra_id = ?";
            $update_stmt = $connection->prepare($update_sql);
            $update_stmt->bind_param("dii", $price, $total, $id);
            if (!$update_stmt->execute()) {
                die("ERROR: Failed to update $user_1_ll. " . $update_stmt->error . '<br>');
            }

            // Update the second table
            $update_sql2 = "UPDATE $sell_data SET price = ?, total = ? WHERE tra_id = ?";
            $update_stmt2 = $connection->prepare($update_sql2);
            $update_stmt2->bind_param("dii", $price, $total, $id);
            if (!$update_stmt2->execute()) {
                die("ERROR: Failed to update $sell_data. " . $update_stmt2->error . '<br>');
            }

            // $query = "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 
            //                             WHERE product_name='$product_name'
            //                             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)";
            //                 $connection->query($query);

            //                 // Final update 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)
            //                             WHERE product_name='$product_name'";
            //                 if (!$connection->query($final_update_query)) {
            //                     $connection->rollback();
            //                     die("ERROR: Query failed. " . $connection->error);
            //                 }

            echo "Update successful for tra_id: $id<br>";
        }
    } else {
        echo "No records found for tra_id: $id.<br>";
    }
} else {
    echo 'Invalid request method. Please use POST.<br>';
}


?>