<?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);


require __DIR__ . '/../../../../../demo1.testuatah.com/app/bootstrap.php';

require_once __DIR__ . "/Database.php";
require_once __DIR__ . "/Main.php";


use Magento\Framework\App\Bootstrap;
use Magento\Framework\App\ObjectManager;




use Main as Response;

class Post extends Database
{
    private $DB;

    function __construct()
    {
        date_default_timezone_set('Asia/Qatar');
        $this->DB = Database::dbconnection();
    }

    private function filter($data)
    {
        return htmlspecialchars(trim(htmlspecialchars_decode($data)), ENT_NOQUOTES);
    }
    // Create a new delivery verification
    public function create($driver_id, $order_id, $image_path)
    {
        $driver_id = $this->filter($driver_id);
        $order_id = $this->filter($order_id);
        $date = date("Y-m-d h:i:s");
        try {
            $sql = "INSERT INTO `ahqa_order_delivery_payment_verify`
                    (`order_id`, `driver_id`, `document`, `driver_signature`, `customer_signature`, `created_at`)
                    VALUES (:order_id, :driver_id, :document, :driver_signature, :customer_signature, :created_at)";

            $stmt = $this->DB->prepare($sql);

            $stmt->bindParam(":order_id", $order_id);
            $stmt->bindParam(":driver_id", $driver_id, PDO::PARAM_INT);
            $stmt->bindParam(":document", $image_path['document'], PDO::PARAM_STR);
            $stmt->bindParam(":driver_signature", $image_path['driver_signature'], PDO::PARAM_STR);
            $stmt->bindParam(":customer_signature", $image_path['customer_signature'], PDO::PARAM_STR);
            $stmt->bindParam(":created_at", $date, PDO::PARAM_STR);

            $stmt->execute();
            $last_id = $this->DB->lastInsertId();
            Response::json(1, 201, "Post has been created successfully", "verification_id", $last_id);

        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }

    // Fetch all order Items or Get a order item post through the item ID
    public function readItems($item_id = false, $return = false)
    {
        try {
            $sql = "SELECT * FROM `ahqa_sales_order_item";
            // If item id is provided
            if ($item_id !== false) {
                if (is_numeric($item_id)) {
                    $sql = "SELECT * FROM `ahqa_sales_order_item` WHERE `item_id`='$item_id'";
                } else {
                    Response::_404();
                }
            }
            $query = $this->DB->query($sql);
            if ($query->rowCount() > 0) {
                $allItems = $query->fetchAll(PDO::FETCH_ASSOC);
                // If ID is Provided, send a single item.
                if ($item_id !== false) {
                    if ($return)
                        return $allItems[0];
                    Response::json(1, 200, null, "items", $allItems[0]);
                }
                Response::json(1, 200, null, "items", $allItems);
            }
            // If the post id does not exist in the database
            if ($item_id !== false) {
                Response::_404();
            }
            // If there are no posts in the database.
            Response::json(1, 200, "Please Insert Some items...", "items", []);
        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }
    public function readPD($user_id = false, $return = false)
    {
        try {
            $sql = "SELECT * FROM `ahqa_picker_driver`";
            // If user id is provided
            if ($user_id !== false) {
                if (is_numeric($user_id)) {
                    $sql = "SELECT * FROM `ahqa_picker_driver` WHERE `id`='$user_id'";
                } else {
                    Response::_404();
                }
            }
            $query = $this->DB->query($sql);
            if ($query->rowCount() > 0) {
                $user = $query->fetchAll(PDO::FETCH_ASSOC);
                // If ID is Provided, send a single user.
                if ($user_id !== false) {
                    // IF $return is true then return the single user
                    if ($return)
                        return $user[0];
                    Response::json(1, 200, null, "user", $user[0]);
                }
                Response::json(1, 200, null, "user", $user);
            }
            // If the user id does not exist in the database
            if ($user_id !== false) {
                Response::_404();
            }
            // If there are no users in the database.
            Response::json(1, 200, "Please Insert Some users...", "users", []);
        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }
    public function updateItemStatus($data)  
    { //V1 new version
        try {
            $res = [];
            $items_status=trim($data->item_status);
                switch ($items_status) {
                  case "replaced":
                        $this->updateItemReplacement($data);
                    break;
                  case "new":
                         $this->updateNewItem($data);
                    break;
                  default:
                                        // Fetch sales order item
                        $sql = "SELECT * FROM `ahqa_sales_order_item` WHERE `item_id` = :item_id";
                        $stmt = $this->DB->prepare($sql);
                        $stmt->bindParam(":item_id", $data->item_id, PDO::PARAM_INT);
                        $stmt->execute();
                        
                        if ($stmt->rowCount() === 0) {
                            Response::json(0, 404, "Invalid Item ID.");
                        }
                        
                        $the_post = $stmt->fetch(PDO::FETCH_OBJ);
                        $item_status = isset($data->item_status) && !empty(trim($data->item_status))
                            ? $this->filter($data->item_status)
                            : $the_post->item_status;
                        $qty_canceled = $the_post->qty_canceled;
                        $picked = 1;
                        $final_price = $data->price ?? 0;
                
                        if ($item_status === 'canceled' || $item_status === 'item_not_available') {
                            $qty_canceled = $the_post->qty_ordered;
                            $this->updateOrderItem($the_post->order_id, $the_post->sku, 0,0); //$this->updateOrderItem($the_post->order_id, $the_post->sku, $data->shipping,$data->qty);
                            $this->insertReplacementLog($the_post, $qty_canceled, $data->reason ?? 'reason_not_available');
                        }elseif ($item_status === 'end_picking') {
                            $this->updateOrderItem($the_post->order_id, $the_post->sku, $data->shipping,$data->qty);
                        } elseif ($item_status !== 'start_picking') {
                            $picked = 0;
                        }
                
                        // Update item status and final price
                        $this->updateSalesOrderItem($data->item_id, $item_status, $final_price);
                        Response::json(1, 200, "Sales Order Item Updated Successfully");
                }

        
                //$res[] = $this->readItems($data->item_id, true);
        
            
        } catch (PDOException $e) {
            error_log("Database Error: " . $e->getMessage()); // Log error
            Response::json(0, 500, $e->getMessage());
        }
    }
    
    
       
    
    /**
     * Update sales order item.
     */
    private function updateSalesOrderItem($itemId, $itemStatus, $finalPrice)
    {
        $updateSql = "UPDATE `ahqa_sales_order_item`
                      SET `item_status` = :item_status, `final_price` = :final_price
                      WHERE `item_id` = :item_id";
        $stmt = $this->DB->prepare($updateSql);
        $stmt->bindParam(":item_status", $itemStatus, PDO::PARAM_STR);
        $stmt->bindParam(":final_price", $finalPrice, PDO::PARAM_STR);
        $stmt->bindParam(":item_id", $itemId, PDO::PARAM_INT);
        $stmt->execute();
    }
    
    /**
     * Insert replacement log entry.
     */
    private function insertReplacementLog($thePost, $qtyCanceled, $reason)
    {
        $insertSql = "INSERT INTO `ahqa_order_replacement`
                      (`subgroup_identifier`, `created_at`, `picker_id`, `old_item_sku`, `old_item_price`, `old_qty`, `reason`)
                      VALUES (:subgroup_identifier, :created_at, :picker_id, :old_item_sku, :old_item_price, :old_qty, :reason)";
        $stmt = $this->DB->prepare($insertSql);
        $stmt->bindParam(":subgroup_identifier", $thePost->subgroup_identifier);
        $createdAt = date('Y-m-d H:i:s');
        $stmt->bindParam(":created_at", $createdAt);
        $stmt->bindParam(":picker_id", $thePost->an_picker_id);
        $stmt->bindParam(":old_item_sku", $thePost->sku);
        $oldPrice = number_format($thePost->price, 2);
        $stmt->bindParam(":old_item_price", $oldPrice);
        $stmt->bindParam(":old_qty", $qtyCanceled);
        $stmt->bindParam(":reason", $reason);
        $stmt->execute();
    }
    
    /**
     * Update order item via API.
     */
    private function updateOrderItem($orderId, $productSku, $shipping,$qty=0)
    {
        // echo $shipping; echo '<=>';
        // echo $qty;  echo '<=>';
        // echo $productSku;  echo '<=>';
        // die();
        $curl = curl_init();
        curl_setopt_array($curl, [
            CURLOPT_URL => 'https://admin-qatar.testuatah.com/rest/V1/custom/order/edititem',
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CUSTOMREQUEST => 'POST',
            CURLOPT_POSTFIELDS => json_encode([
                "orderId" => $orderId,
                "productSku" => $productSku,
                "productQty" => $qty,
                "shipping" => $shipping
            ]),
            CURLOPT_HTTPHEADER => [
                'Content-Type: application/json',
                'Authorization: Bearer 2dhdhimvhb2eg5pczxquwhmh1e1v9x70'
            ],
        ]);
    
        $response = curl_exec($curl);
        if (curl_errno($curl)) {
            $this->logger->error('Curl Error: ' . curl_error($curl));
        }
        curl_close($curl);
    }

    // Update an existing post
    public function update(object $data)
    {
        try {
            $res = array();
            foreach ($data->items as $row) {

                $sql = "SELECT * FROM `ahqa_sales_order_item` WHERE `item_id`='$row->item_id'";
                $query = $this->DB->query($sql);
                if ($query->rowCount() > 0) {
                    $the_post = $query->fetch(PDO::FETCH_OBJ);

                    $item_status = (isset($row->item_status) && !empty(trim($row->item_status))) ? $this->filter($row->item_status) : $the_post->item_status;
                    $qty_canceled = $the_post->qty_canceled;
                    $picked = 1;
                    $final_price=(isset($row->price))? $row->price:0;
                    if ($item_status == 'canceled' || $item_status == 'item_not_available') {
                        $qty_canceled = $the_post->qty_ordered;
                        $curl = curl_init();

                        curl_setopt_array(
                            $curl,
                            array(
                                CURLOPT_URL => 'https://admin-qatar.testuatah.com/rest/V1/custom/order/edititem',
                                CURLOPT_RETURNTRANSFER => true,
                                CURLOPT_ENCODING => '',
                                CURLOPT_MAXREDIRS => 10,
                                CURLOPT_TIMEOUT => 0,
                                CURLOPT_FOLLOWLOCATION => true,
                                CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
                                CURLOPT_CUSTOMREQUEST => 'POST',
                                CURLOPT_POSTFIELDS => '{
                                    "orderId":' . $the_post->order_id . ',
                                    "productSku":"' . $the_post->sku . '",
                                    "productQty":0,
                                    "shipping" :' . $row->shipping . '
                                }',
                                CURLOPT_HTTPHEADER => array(
                                    'Content-Type: application/json',
                                    'Authorization: Bearer 2dhdhimvhb2eg5pczxquwhmh1e1v9x70'
                                ),
                            )
                        );

                        $response = curl_exec($curl);

                        curl_close($curl);
                        //echo $response;
                        $updateQuery = "INSERT INTO `ahqa_order_replacement`
                                    (`subgroup_identifier`, `created_at`, `picker_id`, `old_item_sku`, `old_item_price`, `old_qty`, `reason`) 
                                    VALUES (:subgroup_identifier,:created_at,:picker_id,:old_item_sku,:old_item_price,:old_qty,:reason)";
                        $stmtupdateQuery = $this->DB->prepare($updateQuery);
                        $stmtupdateQuery->bindParam(":subgroup_identifier", $the_post->subgroup_identifier);
                        $missedDate = date('Y-m-d H:i:s');
                        $stmtupdateQuery->bindParam(":created_at", $missedDate);
                        $stmtupdateQuery->bindParam(":picker_id", $the_post->an_picker_id);
                        $stmtupdateQuery->bindParam(":old_item_sku", $the_post->sku);
                        $price = number_format($the_post->price, 2);
                        $stmtupdateQuery->bindParam(":old_item_price", $price);
                        $stmtupdateQuery->bindParam(":old_qty", $qty_canceled);
                        if (isset($row->reason)) {
                            $reason = $row->reason;
                        } else {
                            $reason = "reason_not_availabe";
                        }
                        $stmtupdateQuery->bindParam(":reason", $reason);
                        $stmtupdateQuery->execute();

                    } else if ($item_status != 'start_picking') {
                        $picked = 0;
                    }
                    //update Query
                    $update_sql = "UPDATE `ahqa_sales_order_item` SET `item_status`=:item_status WHERE `item_id`='$row->item_id'";
                    $stmt = $this->DB->prepare($update_sql);
                    $stmt->bindParam(":item_status", $item_status, PDO::PARAM_STR);
                    $stmt->execute();
                    
                    if($final_price>0){
                        //update final price Query
                        $update_sqlFP = "UPDATE `ahqa_sales_order_item` SET `final_price`=:final_price WHERE `item_id`='$row->item_id'";
                        $stmtFP = $this->DB->prepare($update_sqlFP);
                        $stmtFP->bindParam(":final_price", $final_price, PDO::PARAM_STR);
                        $stmtFP->execute();
                    }
                    $res[] = $this->readItems($row->item_id, true);
                } else {
                    Response::json(0, 404, "Invalid Item ID.");
                }

            }
            Response::json(1, 200, "Sales Order Item Updated Successfully", "post", $res);
        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }

    //update item replacement
    public function updateItemReplacement(object $data)
    {
        try {
            
            $orderId = 932; // Replace with the order ID
            
            $suborderId=$data->order_id;
            
            $bootstrap = Bootstrap::create(BP, $_SERVER);
            $objectManager = $bootstrap->getObjectManager();
            
           $appState = $objectManager->get(\Magento\Framework\App\State::class);
           
           
            try {
                // Setting the area code to "frontend" (you can also use "adminhtml" depending on your needs)
                $appState->setAreaCode('frontend');
            } catch (\Magento\Framework\Exception\LocalizedException $e) {
                // Area code is already set, you can ignore this exception in some cases.
                echo "Failed to insert new item.";
            }
           
           
            // Get dependencies
            $orderRepository = $objectManager->get(\Magento\Sales\Api\OrderRepositoryInterface::class);
            $orderItemFactory = $objectManager->get(\Magento\Sales\Model\Order\ItemFactory::class);
            
            
            $productRepository = $objectManager->get(\Magento\Catalog\Api\ProductRepositoryInterface::class);

            // Replace 'your_product_sku' with the SKU of the product you want to load
            $productSku = $data->new_sku;
            
            
            
            
            $sql1 = "SELECT * FROM `ahqa_sales_order_item` WHERE `sku`='$data->canceled_sku' AND `subgroup_identifier`='$suborderId'";
            $query1 = $this->DB->query($sql1);
            $canceled_item = $query1->fetch(PDO::FETCH_OBJ);
            $product_id = 0;
            $item_status = 'end_picking';
            
            
            // Load order
            $order = $orderRepository->get($orderId);
            
            
            if($query1->rowCount() > 0){
                
                // echo $canceled_item->order_id;
                
                
              
                
                $update_sql3 = "UPDATE `ahqa_sales_order_item` SET `item_status`= 'item_not_available', `qty_canceled`=:qty_canceled WHERE `item_id`=:item_id";
                $stmt3 = $this->DB->prepare($update_sql3);
                $stmt3->bindParam(":qty_canceled",$canceled_item->qty_ordered, PDO::PARAM_STR);
                $stmt3->bindParam(":item_id", $canceled_item->item_id, PDO::PARAM_INT);
                // $stmt3->execute(); 
                
                if($stmt3->execute()){
                    
                    
                    
                    try{
                          $product = $productRepository->get($data->new_sku);
                          
                          echo $data->new_sku;
                        
                          $base_row_total = $product->getPrice() * $data->new_product_qty;
                          $row_total = $base_row_total; // Adjust if row_total needs a different calculation
                        
                        
                          // Prepare the INSERT query
                        $update_sql2 = "INSERT INTO `ahqa_sales_order_item` 
                                        (`order_id`, `subgroup_identifier`, `product_id`, `product_type`, `sku`, `name`, `qty_ordered`, `item_status`, `delivery_type`, `price`, `final_price`,`original_price`,`base_original_price`,`base_row_total`,`row_total`) 
                                        VALUES 
                                        (:order_id, :subgroup_identifier, :product_id, :product_type, :sku, :name, :qty_ordered, :item_status, :delivery_type, :price, :final_price, :original_price, :base_original_price, :base_row_total, :row_total)";
                
                        $stmt5 = $this->DB->prepare($update_sql2);
                
                        // Bind parameters for the new item 
                        $stmt5->bindParam(':order_id', $canceled_item->order_id, PDO::PARAM_STR);
                        $stmt5->bindParam(':subgroup_identifier', $canceled_item->subgroup_identifier, PDO::PARAM_STR);
                        $stmt5->bindParam(':product_id', $product_id, PDO::PARAM_STR);
                        $stmt5->bindParam(':product_type', $canceled_item->product_type, PDO::PARAM_STR);
                        $stmt5->bindParam(':sku', $data->new_sku, PDO::PARAM_STR);
                        $stmt5->bindParam(':name', $product->getName(), PDO::PARAM_STR);
                        $stmt5->bindParam(':qty_ordered', $data->new_product_qty, PDO::PARAM_STR);
                        $stmt5->bindParam(':item_status', $item_status, PDO::PARAM_STR);
                        $stmt5->bindParam(':delivery_type', $canceled_item->delivery_type, PDO::PARAM_STR);
                        $stmt5->bindParam(':price', $product->getPrice(), PDO::PARAM_STR);
                        $stmt5->bindParam(':final_price', $data->final_price, PDO::PARAM_STR);
                        $stmt5->bindParam(':original_price', $product->getPrice(), PDO::PARAM_STR);
                        $stmt5->bindParam(':base_original_price', $product->getPrice(), PDO::PARAM_STR);
                        $stmt5->bindParam(':base_row_total', $base_row_total, PDO::PARAM_STR);
                        $stmt5->bindParam(':row_total', $row_total, PDO::PARAM_STR);
                        
                        if ($stmt5->execute()) {
                            
                            
                            echo "New item inserted successfully.";
                            
                        } else {
                            echo "Failed to insert new item.";
                        }
                        
                        
                    }catch(\Magento\Framework\Exception\NoSuchEntityException $e){
                        echo "Failed to insert new item.........";
                    }
                    
                    
                    
                
                    
                } else {
                    
                    echo "item not get canceled";
                }
                
            }
            
            // $curl = curl_init();
            // curl_setopt_array(
            //     $curl,
            //     array(
            //         CURLOPT_URL => 'https://admin-qatar.testuatah.com/rest/V1/custom/order/edititem',
            //         CURLOPT_RETURNTRANSFER => true,
            //         CURLOPT_ENCODING => '',
            //         CURLOPT_MAXREDIRS => 10,
            //         CURLOPT_TIMEOUT => 0,
            //         CURLOPT_FOLLOWLOCATION => true,
            //         CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
            //         CURLOPT_CUSTOMREQUEST => 'POST',
            //         CURLOPT_POSTFIELDS => '{
            //             "orderId": ' . $canceled_item->order_id . ',
            //             "productSku":"' . $data->canceled_sku . '",
            //             "productQty":0,
            //             "shipping" : ' . $data->shipping . '
            //         }',
            //         CURLOPT_HTTPHEADER => array(
            //             'Content-Type: application/json',
            //             'Authorization: Bearer 2dhdhimvhb2eg5pczxquwhmh1e1v9x70'
            //         ),
            //     )
            // );
            // $response = curl_exec($curl);
            // curl_close($curl);

            // $curl2 = curl_init();
            // curl_setopt_array(
            //     $curl2,
            //     array(
            //         CURLOPT_URL => 'https://admin-qatar.testuatah.com/rest/V1/custom/order/additem',
            //         CURLOPT_RETURNTRANSFER => true,
            //         CURLOPT_ENCODING => '',
            //         CURLOPT_MAXREDIRS => 10,
            //         CURLOPT_TIMEOUT => 0,
            //         CURLOPT_FOLLOWLOCATION => true,
            //         CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
            //         CURLOPT_CUSTOMREQUEST => 'POST',
            //         CURLOPT_POSTFIELDS => '{
            //             "orderId": ' . $canceled_item->order_id . ',
            //             "productSku":"' . $data->new_sku . '",
            //             "productQty":' . $data->new_product_qty . '
            //         }',
            //         CURLOPT_HTTPHEADER => array(
            //             'Content-Type: application/json',
            //             'Authorization: Bearer 2dhdhimvhb2eg5pczxquwhmh1e1v9x70'
            //         ),
            //     )
            // );
            // $response2 = curl_exec($curl2);
            // curl_close($curl2);
            //echo $response;

            // $sql2 = "SELECT * FROM `ahqa_sales_order_item` WHERE `sku`='$data->new_sku' AND `order_id`='$canceled_item->order_id'";
            // $query2 = $this->DB->query($sql2);
            // if ($query2->rowCount() > 0) {

            //     //New Item update
            //     $new_item = $query2->fetch(PDO::FETCH_OBJ);
            //     $subgroup_identifier_canceled = $data->order_id;
            //     $canceled_item_order_details = explode('-', $subgroup_identifier_canceled);
            //     $found = 0;
            //     $type_new = "";
            //     $commonQry = "SELECT oi.item_id AS item_id
            //                     FROM ahqa_sales_order_item oi
            //                     JOIN ahqa_catalog_product_entity p ON oi.product_id = p.entity_id
            //                     JOIN ahqa_catalog_product_entity_int pa ON p.entity_id = pa.entity_id 
            //                     WHERE oi.item_id=:item_id AND pa.attribute_id = '178' AND ";

            //     $queries = array();
            //     $queries['EXP'] = "pa.value = '858'";
            //     $queries['VPO'] = "pa.value = '664'";
            //     $queries['WAR'] = "pa.value = '6'";
            //     $queries['SUP'] = "pa.value = '5'";
            //     $queries['ABY'] = "pa.value = '691'";
            //     $queries['CAK'] = "pa.value = '690'";
            //     foreach ($queries as $key => $querie) {
            //         $fqry = $commonQry . $querie;
            //         $stmtf = $this->DB->prepare($fqry);
            //         $stmtf->bindParam(":item_id", $new_item->item_id);
            //         $stmtf->execute();
            //         if ($stmtf->rowCount() > 0) {
            //             $type_new = $key;
            //             $found = 1;
            //             $subgroup_identifier_new = $type_new . '-' . $canceled_item_order_details[1];
            //             break;
            //         }
            //     }
            //     if ($found != 1) {
            //         $type_new = "NOL";
            //         $subgroup_identifier_new = $type_new . '-' . $canceled_item_order_details[1];
            //     }

            //     if ($subgroup_identifier_canceled != $subgroup_identifier_new) {
            //         $getuserQuery = "SELECT name, emp_id FROM `ahqa_picker_driver` WHERE id=:id";
            //         $stmtgetuser = $this->DB->prepare($getuserQuery);
            //         $stmtgetuser->bindParam(':id', $data->picker_id);
            //         $stmtgetuser->execute();
            //         $pickerDetails = $stmtgetuser->fetch(PDO::FETCH_OBJ);
            //         $sqlhistory = "INSERT INTO `ahqa_sales_suborder_status_history`
            //                         (`parent_id`, `comment`, `status`, `user_id`) 
            //                         VALUES (:subgroup_identifier, 'Start Picking - $pickerDetails->name ($pickerDetails->emp_id)', 'start_picking', :user_id)";
            //         $stmthistory = $this->DB->prepare($sqlhistory);
            //         $selectsubgroup = "SELECT * FROM `ahqa_delivery_type_suborders` WHERE subgroup_identifier='$subgroup_identifier_canceled'";
            //         $stmtselectsubgroup = $this->DB->prepare($selectsubgroup);
            //         $stmtselectsubgroup->execute();
            //         $old_suborder_details = $stmtselectsubgroup->fetch(PDO::FETCH_OBJ);
            //         $insertsubgroup = "INSERT INTO `ahqa_delivery_type_suborders`
            //                             (`subgroup_identifier`, `order_id`, `order_status`, `delivery_from`, `delivery_to`, `timerange`, `picker_id`) 
            //                             VALUES 
            //                             (:subgroup_identifier, :order_id, 'start_picking', :delivery_from, :delivery_to, :timerange, :picker_id)";
            //         $stmt5 = $this->DB->prepare($insertsubgroup);
            //         $subgroup_identifier_new = $subgroup_identifier_new . '-' . uniqid();
            //         $stmthistory->bindParam(':subgroup_identifier', $subgroup_identifier_new);
            //         $stmthistory->bindParam(':user_id', $data->picker_id);
            //         $stmthistory->execute();

            //         $stmt5->bindParam(':subgroup_identifier', $subgroup_identifier_new);
            //         $stmt5->bindParam(':order_id', $new_item->order_id);
            //         $stmt5->bindParam(':delivery_from', $old_suborder_details->delivery_from);
            //         $stmt5->bindParam(':delivery_to', $old_suborder_details->delivery_to);
            //         $stmt5->bindParam(':timerange', $old_suborder_details->timerange);
            //         $stmt5->bindParam(':picker_id', $data->picker_id);
            //         $stmt5->execute();
            //     }
            //     if (isset($data->reason)) {
            //         $reason = $data->reason;
            //         if ($reason !== "Customer Preferred") {
            //             $update_sql5 = "UPDATE `ahqa_sales_order_item` SET `item_status`='item_not_available' WHERE `item_id`='$canceled_item->item_id'";
            //             $stmt5 = $this->DB->prepare($update_sql5);
            //             $stmt5->execute();
            //         }
            //     } else {
            //         $reason = "reason_not_availabe";
            //     }
            //     $update_sql3 = "UPDATE `ahqa_sales_order_item` SET `item_status`= 'end_picking', `an_picker_id`=:picker_id, `subgroup_identifier`=:subgroup_identifier WHERE `item_id`='$new_item->item_id'";
            //     $stmt3 = $this->DB->prepare($update_sql3);
            //     $stmt3->bindParam(":picker_id", $data->picker_id, PDO::PARAM_STR);
            //     $stmt3->bindParam(":subgroup_identifier", $subgroup_identifier_new, PDO::PARAM_STR);
            //     $stmt3->execute();

            //     $updateQuery = "INSERT INTO `ahqa_order_replacement`
            //                         (`subgroup_identifier`, `created_at`, `picker_id`, `old_item_sku`, `old_item_price`, `old_qty`,`new_item_sku`, `new_item_price`, `new_qty`, `reason`) 
            //                         VALUES (:subgroup_identifier,:created_at,:picker_id,:old_item_sku,:old_item_price,:old_qty,:new_item_sku,:new_item_price,:new_qty,:reason)";
            //     $stmt = $this->DB->prepare($updateQuery);
            //     $stmt->bindParam(":subgroup_identifier", $subgroup_identifier_canceled);
            //     $missedDate = date('Y-m-d H:i:s');
            //     $stmt->bindParam(":created_at", $missedDate);
            //     $stmt->bindParam(":picker_id", $data->picker_id);
            //     $stmt->bindParam(":old_item_sku", $data->canceled_sku);
            //     $price = number_format($canceled_item->price, 2);
            //     $stmt->bindParam(":old_item_price", $price);
            //     $stmt->bindParam(":old_qty", $canceled_item->qty_ordered);
            //     $stmt->bindParam(":new_item_sku", $data->new_sku);
            //     $replaced_price = number_format($new_item->price, 2);
            //     $stmt->bindParam(":new_item_price", $replaced_price);
            //     $stmt->bindParam(":new_qty", $data->new_product_qty);
            //     $stmt->bindParam(":reason", $reason);
            //     $stmt->execute();

            //     Response::json(1, 200, "Sales Order Item Updated Successfully", "post", $this->readItems($new_item->item_id, true));
            // }

            // Response::json(0, 404, "Invalid Canceled SKU or New Item SKU.");

        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }
    
    
    public function driverRegister(object $data) {
        try{
         $employee_id = $data->employee_id;   
         $status = 0;
         $availablity = 0;
         $approval = 0;
         
         // Hash the password before binding it
         
        $password = md5(htmlspecialchars($data->password));
         
        
        $insertdriver = "INSERT INTO `ahqa_picker_driver`(`emp_id`, `name`, `email`, `mobile_number`, `password`, `address`, `role`,`driver_type`,`regular_shift_time`, `friday_shift_time`, `vehicle_number`, `vehicle_type`, `off_day`,app_version) 
          VALUES (:emp_id,:name,:email,:mobile_number,:password,:address,:role,:driver_type,:regular_shift_time,:friday_shift_time,:vehicle_number,:vehicle_type,:off_day,:app_version)";
         
        
        $Asstmt = $this->DB->prepare($insertdriver);
        
        $Asstmt->bindParam(':emp_id', $employee_id, PDO::PARAM_STR);
        $Asstmt->bindParam(':name', $data->name, PDO::PARAM_STR);
        $Asstmt->bindParam(':email', $data->email, PDO::PARAM_STR);
        $Asstmt->bindParam(':mobile_number', $data->mobile_number, PDO::PARAM_STR);
        $Asstmt->bindParam(':password', $password, PDO::PARAM_STR);
        $Asstmt->bindParam(':address', $data->address, PDO::PARAM_STR);
        $Asstmt->bindParam(':role', $data->role, PDO::PARAM_INT);
        $Asstmt->bindParam(':driver_type', $data->driver_type, PDO::PARAM_STR);
        $Asstmt->bindParam(':regular_shift_time', $data->regular_shift_time, PDO::PARAM_STR);
        $Asstmt->bindParam(':friday_shift_time', $data->friday_shift_time, PDO::PARAM_STR);
        $Asstmt->bindParam(':vehicle_number', $data->vehicle_number, PDO::PARAM_STR);
        $Asstmt->bindParam(':vehicle_type', $data->vehicle_type, PDO::PARAM_STR);
        $Asstmt->bindParam(':off_day', $data->day_off, PDO::PARAM_STR);
        $Asstmt->bindParam(':app_version',$data->app_version,PDO::PARAM_STR);
        
        if($Asstmt->execute()){ 
          Response::json(1, 200, "User Registration Updated", "post");
        }
        
        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }
    

    public function updateNewItem(object $data)
    {
        try {
            $subgroup_identifier_old = $data->order_id;
            $old_order_details = explode('-', $subgroup_identifier_old);
            $sqlOldOrder = "SELECT * FROM `ahqa_sales_order` WHERE `increment_id`='" . $old_order_details[1] . "'";
            $queryOldOrder = $this->DB->query($sqlOldOrder);
            if ($queryOldOrder->rowCount() > 0) {
                $old_order = $queryOldOrder->fetch(PDO::FETCH_OBJ);

                // $curl = curl_init();

                // curl_setopt_array(
                //     $curl,
                //     array(
                //         CURLOPT_URL => 'https://admin-qatar.testuatah.com/rest/V1/custom/order/additem',
                //         CURLOPT_RETURNTRANSFER => true,
                //         CURLOPT_ENCODING => '',
                //         CURLOPT_MAXREDIRS => 10,
                //         CURLOPT_TIMEOUT => 0,
                //         CURLOPT_FOLLOWLOCATION => true,
                //         CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
                //         CURLOPT_CUSTOMREQUEST => 'POST',
                //         CURLOPT_POSTFIELDS => '{
                //             "orderId": ' . $old_order->entity_id . ',
                //             "productSku":"' . $data->productSku . '",
                //             "productQty":' . $data->productQty . '
                //         }',
                //         CURLOPT_HTTPHEADER => array(
                //             'Content-Type: application/json',
                //             'Authorization: Bearer 2dhdhimvhb2eg5pczxquwhmh1e1v9x70'
                //         ),
                //     )
                // );
                // $response = curl_exec($curl);
                // curl_close($curl);

                $sql = "SELECT * FROM `ahqa_sales_order_item` WHERE `sku`='$data->productSku' AND `order_id`='$old_order->entity_id'";
                $query = $this->DB->query($sql);
                if ($query->rowCount() > 0) {
                    $the_post = $query->fetch(PDO::FETCH_OBJ);
                    $item_status = "end_picking";
                    $found = 0;
                    $type_new = "";
                    $commonQry = "SELECT oi.item_id AS item_id
                                FROM ahqa_sales_order_item oi
                                    JOIN ahqa_catalog_product_entity p ON oi.product_id = p.entity_id
                                    JOIN ahqa_catalog_product_entity_int pa ON p.entity_id = pa.entity_id WHERE oi.item_id=:item_id AND pa.attribute_id = '178' AND ";

                    $queries = array();
                    $queries['EXP'] = "pa.value = '858'";
                    $queries['VPO'] = "pa.value = '664'";
                    $queries['WAR'] = "pa.value = '6'";
                    $queries['SUP'] = "pa.value = '5'";
                    $queries['ABY'] = "pa.value = '691'";
                    $queries['CAK'] = "pa.value = '690'";
                    foreach ($queries as $key => $querie) {
                        $fqry = $commonQry . $querie;
                        $stmtf = $this->DB->prepare($fqry);
                        $stmtf->bindParam(":item_id", $the_post->item_id, PDO::PARAM_STR);
                        $stmtf->execute();
                        if ($stmtf->rowCount() > 0) {
                            $type_new = $key;
                            $found = 1;
                            $subgroup_identifier_new = $type_new . '-' . $old_order_details[1];
                            break;
                        }
                    }
                    if ($found != 1) {
                        $type_new = "NOL";
                        $subgroup_identifier_new = $type_new . '-' . $old_order_details[1];
                    }

                    if ($subgroup_identifier_old != $subgroup_identifier_new) {
                        $getuserQuery = "SELECT name, emp_id FROM `ahqa_picker_driver` WHERE id=:id";
                        $stmtgetuser = $this->DB->prepare($getuserQuery);
                        $stmtgetuser->bindParam(':id', $data->picker_id);
                        $stmtgetuser->execute();
                        $pickerDetails = $stmtgetuser->fetch(PDO::FETCH_OBJ);
                        $sqlhistory = "INSERT INTO `ahqa_sales_suborder_status_history`
                                        (`parent_id`, `comment`, `status`, `user_id`)  
                                        VALUES (:subgroup_identifier, 'Start Picking - $pickerDetails->name ($pickerDetails->emp_id)', 'start_picking', :user_id)";
                        $stmthistory = $this->DB->prepare($sqlhistory);
                        $selectsubgroup = "SELECT * FROM `ahqa_delivery_type_suborders` WHERE subgroup_identifier='$subgroup_identifier_old'";
                        $stmtselectsubgroup = $this->DB->prepare($selectsubgroup);
                        $stmtselectsubgroup->execute();
                        $old_suborder_details = $stmtselectsubgroup->fetch(PDO::FETCH_OBJ);
                        $insertsubgroup = "INSERT INTO `ahqa_delivery_type_suborders`
                                            (`subgroup_identifier`, `order_id`, `order_status`, `delivery_from`, `delivery_to`, `timerange`, `picker_id`) 
                                            VALUES 
                                            (:subgroup_identifier, :order_id, 'start_picking', :delivery_from, :delivery_to, :timerange, :picker_id)";
                        $stmt5 = $this->DB->prepare($insertsubgroup);

                        $subgroup_identifier_new = $subgroup_identifier_new . '-' . uniqid();
                        $stmthistory->bindParam(':subgroup_identifier', $subgroup_identifier_new);
                        $stmthistory->bindParam(':user_id', $data->picker_id);
                        $stmthistory->execute();

                        $stmt5->bindParam(':subgroup_identifier', $subgroup_identifier_new);
                        $stmt5->bindParam(':order_id', $the_post->order_id);
                        $stmt5->bindParam(':delivery_from', $old_suborder_details->delivery_from);
                        $stmt5->bindParam(':delivery_to', $old_suborder_details->delivery_to);
                        $stmt5->bindParam(':timerange', $old_suborder_details->timerange);
                        $stmt5->bindParam(':picker_id', $data->picker_id);
                        $stmt5->execute();

                    }
                    // $stmt4->bindParam(":subgroup_identifier", $subgroup_identifier_new, PDO::PARAM_STR);
                    // $stmt4->execute();

                    $update_sql = "UPDATE `ahqa_sales_order_item` SET `item_status`=:item_status, `an_picker_id`=:picker_id, `subgroup_identifier`=:subgroup_identifier WHERE `item_id`='$the_post->item_id'";

                    $stmt = $this->DB->prepare($update_sql);
                    $stmt->bindParam(":item_status", $item_status, PDO::PARAM_STR);
                    $stmt->bindParam(":picker_id", $data->picker_id, PDO::PARAM_STR);
                    $stmt->bindParam(":subgroup_identifier", $subgroup_identifier_new, PDO::PARAM_STR);
                    $stmt->execute();

                    Response::json(1, 200, "Sales Order Updated Successfully", "post", $this->readItems($the_post->item_id, true));
                }
            } else {
                Response::json(0, 404, "Invalid Order ID :" . $queryOldOrder->rowCount());
            }
        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }
    public function updateDistanceV1(object $data)
    {
        try {
            // Prepare statement to find the driver
            $sql = "SELECT * FROM `ahqa_picker_driver` WHERE `id`=:user_id";
            $stmt = $this->DB->prepare($sql);
            $stmt->bindParam(':user_id', $data->user_id, PDO::PARAM_INT);
            $stmt->execute();
            
            if ($stmt->rowCount() > 0) {
                $the_post = $stmt->fetch(PDO::FETCH_OBJ);
                
                $distance = "0";
                
                // Google Maps API request
                $curl = curl_init();
                $apiKey = 'AIzaSyDeFN4A3eenCTIUYvCI7dViF-N-V5X8RgA'; // Use your secure way to retrieve this key //25.219766421388357,51.50285686765409
                curl_setopt_array($curl, array(
                    CURLOPT_URL => "https://maps.googleapis.com/maps/api/directions/json?origin=25.21957222163262,51.50259281053096&destination={$data->lat},{$data->long}&key={$apiKey}",
                    CURLOPT_RETURNTRANSFER => true,
                ));
                
                $response = json_decode(curl_exec($curl), true);
                curl_close($curl);
        
                if (isset($response['error_message'])) {
                    Response::json(0, 500, $response['error_message']);
                } else {
                   // Assuming $response is the JSON-decoded response from the Google Maps API
                    $distanceText = $response['routes'][0]['legs'][0]['distance']['value'];
                    
                    // Extract only the numeric part
                    $distance = floatval($distanceText)/1000;
                    
                    $distanceval = floatval($distanceText);
                    
                    
                    
                     // Prepare update statement
                    $update_sql = "UPDATE `ahqa_picker_driver` SET `distance`=:distance, `latitude`=:latitude, `longitude`=:longitude WHERE `id`=:user_id";
                    $stmtDistanceUpdate = $this->DB->prepare($update_sql);
                    
                    $stmtDistanceUpdate->bindParam(':distance', $distance, PDO::PARAM_STR);
                    $stmtDistanceUpdate->bindParam(':latitude', $data->lat, PDO::PARAM_STR);
                    $stmtDistanceUpdate->bindParam(':longitude', $data->long, PDO::PARAM_STR);
                    $stmtDistanceUpdate->bindParam(':user_id', $data->user_id, PDO::PARAM_INT);
                    
                    if ($stmtDistanceUpdate->execute()) {
                        
                        if($distanceval > 30){
                        
                        Response::json(1, 200, "Your distance is {$distanceval} meters. Move to a 30-meter radius area."); 
                            
                        } else {
                            
                        Response::json(1, 200, "Driver location updated {$distance} KM");
                        
                        }
                        
                        
                    } else {
                        Response::json(0, 404, "Failed to update user location");
                    }

                    Response::json(1, 200, "Distance: {$distanceInfo}");
                }
            } else {
                Response::json(0, 404, "User not found");
            }
        
        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }

        
    }
    public function updateOnlineStatus(object $data)
    {
        //print_r($data->distance);
        try {
            $sql = "SELECT * FROM `ahqa_picker_driver` WHERE `id`='$data->user_id'";
            $query = $this->DB->query($sql);
            if ($query->rowCount() > 0) {
                $the_post = $query->fetch(PDO::FETCH_OBJ);

                $online_status = (isset($data->online_status) && is_numeric($data->online_status)) ? $this->filter($data->online_status) : $the_post->availability_status;
                $update_sql = "UPDATE `ahqa_picker_driver` SET `availability_status`=:online_status WHERE `id`='$data->user_id'";
                $stmt = $this->DB->prepare($update_sql);
                $stmt->bindParam(":online_status", $online_status, PDO::PARAM_STR);
                $stmt->execute();
                if ($the_post->availability_status != $online_status) {
                    $onlineHistoryUpdate = "INSERT INTO `ahqa_picker_driver_online_history` (`user_id`, `status`) VALUES (:user_id,:status)";
                    $stmtonlineHistoryUpdate = $this->DB->prepare($onlineHistoryUpdate);
                    $stmtonlineHistoryUpdate->bindParam(':user_id', $data->user_id, PDO::PARAM_INT);
                    $stmtonlineHistoryUpdate->bindParam(':status', $online_status, PDO::PARAM_INT);
                    $stmtonlineHistoryUpdate->execute();
                    Response::json(1, 200, "Picker / Driver Online Status Updated Successfully", "user", $this->readPD($data->user_id, true));
                }
                if($data->online_status===4 || $data->online_status==='4'){
                   updateTokenGen($data->user_id,null,null,null,'logout');
                }
                Response::json(1, 200, "Already available Picker / Driver Online status", "user", $this->readPD($data->user_id, true));
            }

            Response::json(0, 404, "Invalid User ID.");

        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }
    
    
    public function updateDistanceStat(object $data) {
     
      try{
         $sql = "SELECT * FROM `ahqa_picker_driver` WHERE `id`='$data->user_id'";
         $query = $this->DB->query($sql);
         if($query->rowCount() > 0){
             $the_post = $query->fetch(PDO::FETCH_OBJ);
             
             $distance = (isset($data->distance)) ? $data->distance : "0";
             
                
            // Google Maps API request
            $curl = curl_init();
            $apiKey = 'AIzaSyDeFN4A3eenCTIUYvCI7dViF-N-V5X8RgA'; // Use your secure way to retrieve this key
            curl_setopt_array($curl, array(
                CURLOPT_URL => "https://maps.googleapis.com/maps/api/directions/json?origin=25.21957222163262,51.50259281053096&destination={$data->lat},{$data->long}&key={$apiKey}",
                CURLOPT_RETURNTRANSFER => true,
            ));
            
            $response = json_decode(curl_exec($curl), true);
            curl_close($curl);
    
            if (isset($response['error_message'])) {
                Response::json(0, 500, $response['error_message']);
            } else {
                // Process distance
               
                // Assuming $response is the JSON-decoded response from the Google Maps API
                $distanceText = $response['routes'][0]['legs'][0]['distance']['value'];
                
                // Extract only the numeric part
                $distance = floatval($distanceText)/1000;
                
                 // Prepare update statement
                $update_sql = "UPDATE `ahqa_picker_driver` SET `distance`=:distance, `latitude`=:latitude, `longitude`=:longitude WHERE `id`=:user_id";
                $stmtDistanceUpdate = $this->DB->prepare($update_sql);
                
                $stmtDistanceUpdate->bindParam(':distance', $distance, PDO::PARAM_STR);
                $stmtDistanceUpdate->bindParam(':latitude', $data->lat, PDO::PARAM_STR);
                $stmtDistanceUpdate->bindParam(':longitude', $data->long, PDO::PARAM_STR);
                $stmtDistanceUpdate->bindParam(':user_id', $data->user_id, PDO::PARAM_INT);
                
                if ($stmtDistanceUpdate->execute()) {
                    Response::json(1, 200, "Driver location updated");
                } else {
                    Response::json(0, 404, "Failed to update user location");
                }

                Response::json(1, 200, "Distance: {$distanceInfo}");
            }
             
            //  $update_sql = "UPDATE `ahqa_picker_driver` SET `distance`=:distance,`latitude`=:latitude,`longitude`=:longitude WHERE `id`='$data->user_id'";
             
            //  $stmtDistanceUpdate = $this->DB->prepare($update_sql);
             
            //  $stmtDistanceUpdate->bindParam(':distance',$data->distance, PDO::PARAM_STR);
            //  $stmtDistanceUpdate->bindParam(':latitude',$data->lat, PDO::PARAM_STR);
            //  $stmtDistanceUpdate->bindParam(':longitude',$data->long, PDO::PARAM_STR);
             
            //  if($stmtDistanceUpdate->execute()){
            //      Response::json(1,200,"Already available Picker / Driver Online status", "user",$this->readPD($data->user_id,true));
            //  } else {
            //      Response::json(0,404,"Invalid User ID");
            //  }
             
            
         }
         
      } catch (PDOException $e) {
         Response::json(0, 500, $e->getMessage()); 
      }
    }
    
    
    
    public function updateBreakStatus(object $data)
    {
       
        try {
            $sql = "SELECT * FROM `ahqa_picker_driver` WHERE `id`='$data->user_id'";
            $query = $this->DB->query($sql);
            if ($query->rowCount() > 0) {
                $the_post = $query->fetch(PDO::FETCH_OBJ);
               // $online_status = (isset($data->online_status) && is_numeric($data->online_status)) ? $this->filter($data->online_status) : $the_post->availability_status;
                $break_status = (isset($data->break_status) && is_numeric($data->break_status)) ? $this->filter($data->break_status) : $the_post->break_status;
                $update_sql = "UPDATE `ahqa_picker_driver` SET `break_status`=:break_status , `availability_status`=:online_status  WHERE `id`='$data->user_id'";
                $online_status=($break_status===2 || $break_status==='2')? 0: 1;
                $stmt = $this->DB->prepare($update_sql);
                $stmt->bindParam(":break_status", $break_status, PDO::PARAM_STR);
                $stmt->bindParam(":online_status", $online_status, PDO::PARAM_STR);
                $stmt->execute();
                if ($the_post->break_status != $break_status) {
                    $onlineHistoryUpdate = "INSERT INTO `ahqa_picker_driver_online_history`
                                        (`user_id`, `status`)
                                        VALUES (:user_id,:status)";
                    $stmtonlineHistoryUpdate = $this->DB->prepare($onlineHistoryUpdate);
                    $stmtonlineHistoryUpdate->bindParam(':user_id', $data->user_id, PDO::PARAM_INT);
                    $stmtonlineHistoryUpdate->bindParam(':status', $break_status, PDO::PARAM_INT);
                    $stmtonlineHistoryUpdate->execute();
                    Response::json(1, 200, "Picker / Driver Break Status Updated Successfully", "user", $this->readPD($data->user_id, true));
                }

                if ($the_post->availability_status != $online_status) {
                    $onlineHistoryUpdate = "INSERT INTO `ahqa_picker_driver_online_history`
                                        (`user_id`, `status`)
                                        VALUES (:user_id,:status)";
                    $stmtonlineHistoryUpdate = $this->DB->prepare($onlineHistoryUpdate);
                    $stmtonlineHistoryUpdate->bindParam(':user_id', $data->user_id, PDO::PARAM_INT);
                    $stmtonlineHistoryUpdate->bindParam(':status', $online_status, PDO::PARAM_INT);
                    $stmtonlineHistoryUpdate->execute();
                }
                Response::json(1, 200, "Already available Picker / Driver Break status", "user", $this->readPD($data->user_id, true));
            }

            Response::json(0, 404, "Invalid User ID.");

        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }
    
    public function updateSubOrderStatusV1(object $data)
    {
        try {
            $subgroup_identifier = $data->order_id;
            
            $sql = "SELECT order_status, order_id, driver_id, picker_id 
                    FROM `ahqa_delivery_type_suborders` 
                    WHERE `subgroup_identifier` = :subgroup_identifier";
            $query = $this->DB->prepare($sql);
            $query->bindParam(':subgroup_identifier', $subgroup_identifier, PDO::PARAM_STR);
            $query->execute();
   

            if($query->rowCount() > 0) {
                $the_post = $query->fetch(PDO::FETCH_OBJ);
                // print_r($the_post);
                $order_id=$the_post->order_id;
                if ($the_post->order_status==$data->order_status) {
                    Response::json(0, 200, "You already marked ".$data->order_status);
                    return;
                }               
               
                // Determine order status and user ID
                $order_status = !empty(trim($data->order_status)) ? $this->filter($data->order_status) : $the_post->order_status;
                $comment = !empty(trim($data->comment)) ? $this->filter($data->comment) : "";
                $user_id = $data->user_id ?? ($the_post->driver_id ?: $the_post->picker_id);
            
                // Validate order status before proceeding
                if ($order_status == "order_collected" && !in_array($the_post->order_status, ['on_the_way', 'assigned_driver'])) {
                    Response::json(0, 400, "This Order cannot be Scanned");
                    return;
                }else if ($order_status == "holded" && in_array($the_post->order_status, ['on_the_way', 'assigned_driver'])) {
                    Response::json(0, 200, "This Order cannot be hold");
                    return;
                }else if ($order_status == "ready_to_dispatch") {
                    Response::json(0, 400, "This Order cannot be transfer-Inform to Teamlad");
                    return;
                }else if($data->order_status == "on_the_way" && $data->latitude && $data->longitude){
                   $sqlQuery = "SELECT COUNT(*) FROM `ahqa_delivery_type_suborders` WHERE `driver_id` = :driverid AND `order_status` = :status";
                   $stmt = $this->DB->prepare($sqlQuery); // Prepare the query
                   $stmt->bindParam(':driverid', $data->user_id, PDO::PARAM_INT); // Bind driver ID
                   $stmt->bindParam(':status', $data->order_status, PDO::PARAM_STR); // Bind status
                   $stmt->execute(); // Execute the query
                   
                   $count = $stmt->fetchColumn(); // Fetch the count directly
                   
                   if ($count > 0) {
                       // Perform your actions here
                     Response::json(0, 404, "On the way status update only one on time");
                     return;
                   } 
                       // Update `ahqa_delivery_type_suborders`
                    $update_sql = "UPDATE `ahqa_delivery_type_suborders` 
                    SET `order_status` = :order_status,`driver_lat` = :driver_lat,`driver_long` = :driver_long 
                    WHERE `subgroup_identifier` = :subgroup_identifier"; 
               
                    $stmt = $this->DB->prepare($update_sql);
                    $stmt->bindParam(":driver_lat", $data->latitude, PDO::PARAM_STR);
                    $stmt->bindParam(":driver_long", $data->longitude, PDO::PARAM_STR);
                    $stmt->bindParam(":order_status", $order_status, PDO::PARAM_STR);
                    $stmt->bindParam(":subgroup_identifier", $subgroup_identifier, PDO::PARAM_STR);
                    $stmt->execute();
                }else if($order_status == "complete" && $data->latitude && $data->longitude){                      
                        $que = "SELECT so.subgroup_identifier, 
                              SUBSTRING_INDEX(so.subgroup_identifier, '-', 1) AS prefix,
                              so.order_id, 
                              oa.postcode AS zone, 
                              oa.longitude, 
                              oa.latitude,
                              so.driver_flag
                            FROM ahqa_delivery_type_suborders so
                            JOIN ahqa_sales_order_address oa 
                                ON so.order_id = oa.parent_id AND oa.address_type = 'shipping'
                            LEFT JOIN ahqa_zone_pincode_checker pc 
                                ON oa.postcode = pc.pincode
                            LEFT JOIN ahqa_sales_order_item oi 
                                ON so.subgroup_identifier = oi.subgroup_identifier
                            WHERE so.subgroup_identifier = :subgroup_identifier";
                            
                            $xstmt = $this->DB->prepare($que);
                            $xstmt->bindParam(":subgroup_identifier", $subgroup_identifier, PDO::PARAM_STR);
                            $xstmt->execute();
                        
                        
                        //  // Fetch result
                        //  $result = $xstmt->fetch(PDO::FETCH_ASSOC);
                         if($xstmt->rowCount() > 0) {
                            $theADDRESS = $xstmt->fetch(PDO::FETCH_OBJ);  
                            $marklatitude=$data->latitude;
                            $marklongitude=$data->longitude;
                            // $marklatitude=$theADDRESS->latitude;
                            // $marklongitude=$theADDRESS->longitude;
                            $distance = "0";
                    
                            // Google Maps API request
                            $curl = curl_init();
                            $apiKey = 'AIzaSyDeFN4A3eenCTIUYvCI7dViF-N-V5X8RgA'; // Use your secure way to retrieve this key //25.219766421388357,51.50285686765409
                            curl_setopt_array($curl, array(
                                CURLOPT_URL => "https://maps.googleapis.com/maps/api/directions/json?origin=25.21957222163262,51.50259281053096&destination={$marklatitude},{$marklongitude}&key={$apiKey}",
                                CURLOPT_RETURNTRANSFER => true,
                            ));
                            
                            $response = json_decode(curl_exec($curl), true);
                            curl_close($curl);
                    
                            if (!isset($response['error_message'])) { //if driver mark from online counter
                            // Assuming $response is the JSON-decoded response from the Google Maps API
                                $distanceText = $response['routes'][0]['legs'][0]['distance']['value'];
                                
                                // Extract only the numeric part
                                $distance = floatval($distanceText)/1000;
                                
                               // $distanceval = floatval($distanceText);
                               if($distance<1){
                                Response::json(0, 200, 'Please mark order from delivered location');
                                return;
                               }

                            }                            
                       
                                
                            $zone = (int) $theADDRESS->zone;
                    
                            // List of far zones
                            $farzones = [68, 69, 70, 71, 72, 81, 73, 74, 75, 76, 78, 79, 80, 82, 83, 84, 85, 86, 87, 88, 89];
                            
                            
                            if(in_array($zone, $farzones)){

                                // Perform an update
                                $updateQuery = "UPDATE ahqa_picker_driver SET zone_flag = 1 WHERE id = :id";
                                $updateStmt = $this->DB->prepare($updateQuery);
                                $updateStmt->bindParam(':id', $user_id, PDO::PARAM_INT); // Assuming driver_flag holds the ID
                                $updateStmt->execute();
                    
                                // echo "Zone flag updated successfully.\n";
                            }

                         }
                           // Update `ahqa_delivery_type_suborders`
                        $update_sql = "UPDATE `ahqa_delivery_type_suborders` 
                                SET `order_status` = :order_status,`driver_lat` = :driver_lat,`driver_long` = :driver_long 
                                WHERE `subgroup_identifier` = :subgroup_identifier"; 
                        
                        $stmt = $this->DB->prepare($update_sql);
                        $stmt->bindParam(":driver_lat", $data->latitude, PDO::PARAM_STR);
                        $stmt->bindParam(":driver_long", $data->longitude, PDO::PARAM_STR);
                        $stmt->bindParam(":order_status", $order_status, PDO::PARAM_STR);
                        $stmt->bindParam(":subgroup_identifier", $subgroup_identifier, PDO::PARAM_STR);
                        $stmt->execute();


                 }else if ($order_status == "end_picking"){ //on end picked calculaet total 
                    // Example of fetching total base price
                    
                   
                    $queryTotalEndpicked = "
                        SELECT subgroup_identifier
                        FROM ahqa_sales_order_item 
                        WHERE subgroup_identifier = :subgroup_identifier
                        AND item_status IN ('assigned_picker','start_picking')
                        ORDER BY subgroup_identifier ASC
                    ";
                    
                    $stmtTotalEndPicked = $this->DB->prepare($queryTotalEndpicked);
                    $stmtTotalEndPicked->bindParam(':subgroup_identifier', $subgroup_identifier, PDO::PARAM_STR);
                    $stmtTotalEndPicked->execute();
                    if($stmtTotalEndPicked->rowCount() > 0) {
                        Response::json(0, 200, "Please check all items.");
                        return;
                    }
                   $queryTotal = "
                       SELECT subgroup_identifier,
                           SUM(
                               CASE
                                   WHEN qty_shipped > 0 AND final_price<1 THEN (qty_shipped-qty_canceled) * base_price
                                     WHEN qty_ordered > 0 AND final_price<1 THEN (qty_ordered-qty_canceled) * base_price
                                     WHEN qty_shipped > 0 AND final_price>0 THEN (qty_ordered-qty_canceled) *final_price
                                     WHEN qty_ordered > 0 AND final_price>1 THEN (qty_ordered-qty_canceled) *final_price
                                   ELSE (qty_ordered-qty_canceled)* base_price
                               END
                           ) AS total_base_price
                       FROM ahqa_sales_order_item 
                       WHERE order_id = :order_id
                       GROUP BY subgroup_identifier 
                       ORDER BY subgroup_identifier ASC
                   ";
                   
                   $stmtTotal = $this->DB->prepare($queryTotal);
                   $stmtTotal->bindParam(':order_id', $order_id, PDO::PARAM_INT);
                   $stmtTotal->execute();
                   // Fetch the result
                  
                   if($stmtTotal->rowCount() > 0) {
                        $resultTotal = $stmtTotal->fetchAll(PDO::FETCH_OBJ);
                       // Iterate through the results if needed
                    
                       
                       foreach ($resultTotal as $row2) {
                        //   eche;
                           if($subgroup_identifier==$row2->subgroup_identifier){
                                 // Update `ahqa_delivery_type_suborders`
                               $update_sql = "UPDATE `ahqa_delivery_type_suborders` 
                                         SET `order_status` = :order_status, grand_total=:grand_total
                                         WHERE `subgroup_identifier` = :subgroup_identifier";
                               $stmt = $this->DB->prepare($update_sql);
                               $stmt->bindParam(":order_status", $order_status, PDO::PARAM_STR);
                               $stmt->bindParam(":subgroup_identifier", $subgroup_identifier, PDO::PARAM_STR);
                               $stmt->bindParam(":grand_total", $row2->total_base_price, PDO::PARAM_STR);
                               $stmt->execute();
                           }else{
                                 // Update `ahqa_delivery_type_suborders`
                               $update_sql = "UPDATE `ahqa_delivery_type_suborders` 
                                         SET  grand_total=:grand_total
                                         WHERE `subgroup_identifier` = :subgroup_identifier";
                               $stmt = $this->DB->prepare($update_sql);
                               $stmt->bindParam(":subgroup_identifier",$row2->subgroup_identifier, PDO::PARAM_STR);
                               $stmt->bindParam(":grand_total", $row2->total_base_price, PDO::PARAM_STR);
                               $stmt->execute();
                           }
                       }
                    }

                   
                }else{
                      // Update `ahqa_delivery_type_suborders`
                    $update_sql = "UPDATE `ahqa_delivery_type_suborders` 
                               SET `order_status` = :order_status
                               WHERE `subgroup_identifier` = :subgroup_identifier";
                    $stmt = $this->DB->prepare($update_sql);
                    $stmt->bindParam(":order_status", $order_status, PDO::PARAM_STR);
                    $stmt->bindParam(":subgroup_identifier", $subgroup_identifier, PDO::PARAM_STR);
                    $stmt->execute();
                 }
        
               
                // Insert status history
                $sql4 = "INSERT INTO `ahqa_sales_suborder_status_history` 
                         (`parent_id`, `comment`, `status`, `user_id`, `latitude`, `longitude`) 
                         VALUES (:subgroup_identifier, :comment, :order_status, :user_id, :latitude, :longitude)";
                $stmt4 = $this->DB->prepare($sql4);
                $stmt4->bindParam(':subgroup_identifier', $subgroup_identifier, PDO::PARAM_STR);
                $stmt4->bindParam(':comment', $comment, PDO::PARAM_STR);
                $stmt4->bindParam(':order_status', $order_status, PDO::PARAM_STR);
                $stmt4->bindParam(':user_id', $user_id, PDO::PARAM_INT);
                $stmt4->bindParam(':latitude', $data->latitude, PDO::PARAM_STR);
                $stmt4->bindParam(':longitude', $data->longitude, PDO::PARAM_STR);
                $stmt4->execute();        
                Response::json(1, 200, "Order status updated Successfully");
            } else {
                Response::json(0, 404, "Invalid order ID: " . $subgroup_identifier);
            }      
            
            
        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }

    }
    
    public function updateSubOrderStatus(object $data)
    {
        try {
            foreach ($data->items as $row) {
                
                $subgroup_identifier = $row->order_id;
                $sql = "SELECT * FROM `ahqa_delivery_type_suborders` WHERE `subgroup_identifier`='" . $subgroup_identifier . "'";
                $query = $this->DB->query($sql);
                $query->execute();
                
                if ($query->rowCount() > 0) {
                   
                    $the_post = $query->fetch(PDO::FETCH_OBJ);
                    $order_id=$the_post->order_id;
                    $order_status = (isset($row->order_status) && !empty(trim($row->order_status))) ? $this->filter($row->order_status) : $the_post->order_status;
                    $comment = (isset($row->comment) && !empty(trim($row->comment))) ? $this->filter($row->comment) : "";
                    if (isset($row->user_id)) {
                        $user_id = $row->user_id;
                    } else if ($the_post->order_status==$row->order_status) {
                        Response::json(0, 200, "You already marked ".$order_status);
                    }else if ($order_status == "holded" && in_array($the_post->order_status, ['on_the_way', 'assigned_driver'])) {
                        Response::json(0, 200, "This Order cannot be hold");
                        return;
                    }else{
                        if ($the_post->driver_id != 0) {
                            $user_id = $the_post->driver_id;
                        } else {
                            $user_id = $the_post->picker_id;
                             $sqlNotend = "SELECT count(*) FROM `ahqa_sales_order_item` WHERE item_status IN('assigned_picker','start_picking') AND `subgroup_identifier`='" . $subgroup_identifier . "'";
                            $stmtnotEnd = $this->DB->prepare($sqlNotend);
                            $stmtnotEnd->execute();
                            if ($stmtnotEnd->rowCount() > 0) {
                                Response::json(0, 200, "Please end picked all items.");
                            }
                        }
                    }
                    if ($order_status == "order_collected") {
                        if ($the_post->order_status != 'end_picking' && $the_post->order_status != 'assigned_driver') {
                            Response::json(0, 400, "This Order cannot be Scanned");
                        } else {
                            $getRole = "SELECT role FROM `ahqa_picker_driver` WHERE id=:id";
                            $stmtRole = $this->DB->prepare($getRole);
                            $stmtRole->bindValue(':id', $user_id, PDO::PARAM_INT);
                            $stmtRole->execute();
                            $role = 0;
                            while ($row = $stmtRole->fetch(PDO::FETCH_ASSOC)) {
                                $role = $row['role'];
                            }
                            if ($role == 1) {
                                $update_sql = "UPDATE `ahqa_delivery_type_suborders` SET `order_status`=:order_status, `picker_id`=:user_id WHERE `subgroup_identifier`=:subgroup_identifier";

                                // $sql3 = "UPDATE `ahqa_sales_order_item` 
                                //     SET item_status = :item_status, an_picker_id=:user_id
                                //     WHERE subgroup_identifier=:subgroup_identifier AND item_status NOT IN ('canceled','canceled_by_team','item_not_available')";
                            } elseif ($role == 2 || $role == '2' || $role == 3  || $role == '3') {
                                 $order_status=($row->order_status=='holded' || $order_status=='hold' || $order_status==='holded' || $order_status==='hold')? 'ready_to_dispatch':$order_status;
                                $update_sql = "UPDATE `ahqa_delivery_type_suborders` SET `order_status`=:order_status, `driver_id`=:user_id WHERE `subgroup_identifier`=:subgroup_identifier";

                                // $sql3 = "UPDATE `ahqa_sales_order_item` 
                                //     SET item_status = :item_status, an_driver_id=:user_id
                                //     WHERE subgroup_identifier=:subgroup_identifier AND item_status NOT IN ('canceled','canceled_by_team','item_not_available')";
                            }
                            $stmt = $this->DB->prepare($update_sql);
                            $stmt->bindParam(":user_id", $user_id);

                            // $stmt3 = $this->DB->prepare($sql3);
                            // $stmt3->bindValue(':user_id', $user_id);

                            //Update main order
                            $updatemainOrderQuery = "SELECT * FROM `ahqa_sales_order` WHERE entity_id=:order_id AND status='pending'";
                            $stmtupdatemainOrder = $this->DB->prepare($updatemainOrderQuery);
                            $stmtupdatemainOrder->bindParam(':order_id', $the_post->order_id);
                            $stmtupdatemainOrder->execute();
                            $increment_id = (explode('-', $subgroup_identifier))[1];
                            if ($stmtupdatemainOrder->rowCount()) {
                                $curl = curl_init();

                                curl_setopt_array(
                                    $curl,
                                    array(
                                        CURLOPT_URL => 'https://admin-qatar.testuatah.com/rest/V1/orders',
                                        CURLOPT_RETURNTRANSFER => true,
                                        CURLOPT_ENCODING => '',
                                        CURLOPT_MAXREDIRS => 10,
                                        CURLOPT_TIMEOUT => 0,
                                        CURLOPT_FOLLOWLOCATION => true,
                                        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
                                        CURLOPT_CUSTOMREQUEST => 'POST',
                                        CURLOPT_POSTFIELDS => '{
                                    "entity":{
                                    "entity_id":' . $the_post->order_id . ',
                                    "status":"processing",
                                    "increment_id":"' . $increment_id . '"
                                    }
                                }',
                                        CURLOPT_HTTPHEADER => array(
                                            'Content-Type: application/json',
                                            'Authorization: Bearer 2dhdhimvhb2eg5pczxquwhmh1e1v9x70',
                                        ),
                                    )
                                );

                                $response = curl_exec($curl);

                                curl_close($curl);
                                //echo $response;
                            }
                        }
                    }else if ($order_status == "end_picking"){ //on end picked calculaet total 
                    // Example of fetching total base price
                    
                   
                        $queryTotalEndpicked = "
                            SELECT subgroup_identifier
                            FROM ahqa_sales_order_item 
                            WHERE subgroup_identifier = :subgroup_identifier
                            AND item_status IN ('assigned_picker','start_picking')
                            ORDER BY subgroup_identifier ASC
                        ";
                        
                        $stmtTotalEndPicked = $this->DB->prepare($queryTotalEndpicked);
                        $stmtTotalEndPicked->bindParam(':subgroup_identifier', $subgroup_identifier, PDO::PARAM_STR);
                        $stmtTotalEndPicked->execute();
                        if($stmtTotalEndPicked->rowCount() > 0) {
                            Response::json(0, 200, "Please check all items.");
                            return;
                        }
                       $queryTotal = "
                           SELECT subgroup_identifier,
                               SUM(
                                   CASE
                                       WHEN qty_shipped > 0 AND final_price<1 THEN (qty_shipped-qty_canceled) * base_price
                                         WHEN qty_ordered > 0 AND final_price<1 THEN (qty_ordered-qty_canceled) * base_price
                                         WHEN qty_shipped > 0 AND final_price>0 THEN (qty_ordered-qty_canceled) *final_price
                                         WHEN qty_ordered > 0 AND final_price>1 THEN (qty_ordered-qty_canceled) *final_price
                                       ELSE (qty_ordered-qty_canceled)* base_price
                                   END
                               ) AS total_base_price
                           FROM ahqa_sales_order_item 
                           WHERE order_id = :order_id
                           GROUP BY subgroup_identifier 
                           ORDER BY subgroup_identifier ASC
                       ";
                       
                       $stmtTotal = $this->DB->prepare($queryTotal);
                       $stmtTotal->bindParam(':order_id', $order_id, PDO::PARAM_INT);
                       $stmtTotal->execute();
                       // Fetch the result
                      
                       if($stmtTotal->rowCount() > 0) {
                            $resultTotal = $stmtTotal->fetchAll(PDO::FETCH_OBJ);
                           // Iterate through the results if needed
                        
                           
                           foreach ($resultTotal as $row2) {
                            //   eche;
                               if($subgroup_identifier==$row2->subgroup_identifier){
                                     // Update `ahqa_delivery_type_suborders`
                                   $update_sql = "UPDATE `ahqa_delivery_type_suborders` 
                                             SET `order_status` = :order_status, grand_total=:grand_total
                                             WHERE `subgroup_identifier` = :subgroup_identifier";
                                   $stmt = $this->DB->prepare($update_sql);
                                   $stmt->bindParam(":order_status", $order_status, PDO::PARAM_STR);
                                   $stmt->bindParam(":subgroup_identifier", $subgroup_identifier, PDO::PARAM_STR);
                                   $stmt->bindParam(":grand_total", $row2->total_base_price, PDO::PARAM_STR);
                                   $stmt->execute();
                               }else{
                                     // Update `ahqa_delivery_type_suborders`
                                   $update_sql = "UPDATE `ahqa_delivery_type_suborders` 
                                             SET  grand_total=:grand_total
                                             WHERE `subgroup_identifier` = :subgroup_identifier";
                                   $stmt = $this->DB->prepare($update_sql);
                                   $stmt->bindParam(":subgroup_identifier",$row2->subgroup_identifier, PDO::PARAM_STR);
                                   $stmt->bindParam(":grand_total", $row2->total_base_price, PDO::PARAM_STR);
                                   $stmt->execute();
                               }
                           }
                        }
                        
                         $sql4 = "INSERT INTO `ahqa_sales_suborder_status_history`
                            (`parent_id`, `comment`, `status`, `user_id`) 
                            VALUES (:subgroup_identifier, :comment, :order_status, :user_id)";
                        $stmt4 = $this->DB->prepare($sql4);
                        $stmt4->bindParam(':order_status', $order_status);
                        $stmt4->bindParam(':subgroup_identifier', $subgroup_identifier);
                        $stmt4->bindParam(':comment', $comment);
                        $stmt4->bindParam(':user_id', $user_id);
                        $stmt4->execute();

                   
                } else {
                        $update_sql = "UPDATE `ahqa_delivery_type_suborders` SET `order_status`=:order_status WHERE `subgroup_identifier`=:subgroup_identifier";
                        $stmt = $this->DB->prepare($update_sql);

                        // $sql3 = "UPDATE `ahqa_sales_order_item` 
                        //         SET item_status = :item_status 
                        //         WHERE subgroup_identifier=:subgroup_identifier AND item_status NOT IN ('canceled','canceled_by_team','item_not_available')";
                        // $stmt3 = $this->DB->prepare($sql3);

                    }
                    $stmt->bindParam(":order_status", $order_status, PDO::PARAM_STR);
                    $stmt->bindParam(":subgroup_identifier", $subgroup_identifier, PDO::PARAM_STR);
                    $stmt->execute();

                    // $stmt3->bindValue(':item_status', $order_status);
                    // $stmt3->bindValue(':subgroup_identifier', $subgroup_identifier);
                    // $stmt3->execute();

                    $sql4 = "INSERT INTO `ahqa_sales_suborder_status_history`
                            (`parent_id`, `comment`, `status`, `user_id`) 
                            VALUES (:subgroup_identifier, :comment, :order_status, :user_id)";
                    $stmt4 = $this->DB->prepare($sql4);
                    $stmt4->bindParam(':order_status', $order_status);
                    $stmt4->bindParam(':subgroup_identifier', $subgroup_identifier);
                    $stmt4->bindParam(':comment', $comment);
                    $stmt4->bindParam(':user_id', $user_id);
                    $stmt4->execute();
                    if($order_status=='complete' || $order_status=='on_the_way'){
                        
                        
                            
                            //$comment= ($order_status=='on_the_way' )
                            // Define the data for the push notification
                        // $pushData = array(
                        // 'to' => 'e5h9HqkIQuWevrzc60eMx8:APA91bFU4E783arlERDTJtL8GcNi_0F7TlHlpf6xF8P4yqpTPrlQUuGBCeWQlc5OGmWyAWPf2MKFB1qyafocfpzvZbAj_N6eREqZaoEt1EfFZ5FGwTgyKDTA7si_jec5JWc1PxSh06M6', // Replace with the device token of the recipient
                        // 'notification' => array(
                        // 'title' => 'New Order', // Title of the notification
                        // 'body' => 'You have received a new order.', // Body of the notification
                        // 'click_action' => 'OPEN_ORDER_ACTIVITY' // Action to perform when notification is clicked
                        // ),
                        // 'data' => array(
                        //     'order_id' => $subgroup_identifier // Additional data to be sent with the notification (e.g., order ID)
                        // )
                        // );
                        
                        // $curlPush = curl_init();
                        // curl_setopt_array(
                        // $curlPush,
                        // array(
                        // CURLOPT_URL => 'https://fcm.googleapis.com/fcm/send',
                        // CURLOPT_RETURNTRANSFER => true,
                        // CURLOPT_ENCODING => '',
                        // CURLOPT_MAXREDIRS => 10,
                        // CURLOPT_TIMEOUT => 0,
                        // CURLOPT_FOLLOWLOCATION => true,
                        // CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
                        // CURLOPT_CUSTOMREQUEST => 'POST',
                        // CURLOPT_POSTFIELDS => json_encode($pushData),
                        // CURLOPT_HTTPHEADER => array(
                        //     'Content-Type: application/json',
                        //     'Authorization: key=AAAAkViSN5Q:APA91bHfX3Vt9Thwy1zt7jz-qFUG7vL6q1fZDEtiOxjLqs4-KkDhvd7xMrlopBieScNwbcnUETJNYUxESlZJYxqaRdA7xuuD9kdx3rxZwjAA7Ot2Zvji9c0n0E4GkP12EqtJgXybLtD1'
                        // ),
                        // )
                        // );
                        
                        // $response = curl_exec($curlPush);
                        // curl_close($curlPush);
                    }
                    
                    

                } else {
                    Response::json(0, 404, "Invalid order ID : " . $subgroup_identifier);
                }
            }
            Response::json(1, 200, "Order status updated Successfully");
        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }
    public function updateItemMissingReplacementReport(object $data)
    {
        try {
            $sql = "SELECT * FROM `ahqa_delivery_type_suborders` WHERE `subgroup_identifier`='$data->order_id'";
            $query = $this->DB->query($sql);
            $query->execute();
            if ($query->rowCount() > 0) {
                if (isset($data->replaced_sku)) {
                    $updateQuery = "INSERT INTO `ahqa_order_replacement`
                                    (`subgroup_identifier`, `created_at`, `picker_id`, `old_item_sku`, `old_item_price`, `old_qty`,`new_item_sku`, `new_item_price`, `new_qty`, `reason`) 
                                    VALUES (:subgroup_identifier,:created_at,:picker_id,:old_item_sku,:old_item_price,:old_qty,:new_item_sku,:new_item_price,:new_qty,:reason)";
                    $stmt = $this->DB->prepare($updateQuery);
                    $stmt->bindParam(":subgroup_identifier", $data->order_id);
                    $missedDate = date('Y-m-d H:i:s');
                    $stmt->bindParam(":created_at", $missedDate);
                    $stmt->bindParam(":picker_id", $data->picker_id);
                    $stmt->bindParam(":old_item_sku", $data->missing_sku);
                    $price = number_format((float) $data->missing_price, 2);
                    $stmt->bindParam(":old_item_price", $price);
                    $stmt->bindParam(":old_qty", $data->missing_qty);
                    $stmt->bindParam(":new_item_sku", $data->replaced_sku);
                    $replaced_price = number_format((float) $data->replaced_price, 2);
                    $stmt->bindParam(":new_item_price", $replaced_price);
                    $stmt->bindParam(":new_qty", $data->replaced_qty);
                    $stmt->bindParam(":reason", $data->reason);
                    $stmt->execute();
                    Response::json(1, 200, "Item Replacement Updated Successfully");
                } else {
                    $updateQuery = "INSERT INTO `ahqa_order_replacement`
                                    (`subgroup_identifier`, `created_at`, `picker_id`, `old_item_sku`, `old_item_price`, `old_qty`, `reason`) 
                                    VALUES (:subgroup_identifier,:created_at,:picker_id,:old_item_sku,:old_item_price,:old_qty,:reason)";
                    $stmt = $this->DB->prepare($updateQuery);
                    $stmt->bindParam(":subgroup_identifier", $data->order_id);
                    $missedDate = date('Y-m-d H:i:s');
                    $stmt->bindParam(":created_at", $missedDate);
                    $stmt->bindParam(":picker_id", $data->picker_id);
                    $stmt->bindParam(":old_item_sku", $data->missing_sku);
                    $price = number_format((float) $data->missing_price, 2);
                    $stmt->bindParam(":old_item_price", $price);
                    $stmt->bindParam(":old_qty", $data->missing_qty);
                    $stmt->bindParam(":reason", $data->reason);
                    $stmt->execute();
                    Response::json(1, 200, "Missing Item Updated Successfully");
                }
            }
            Response::json(0, 404, "Invalid Order ID.");
        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }

    public function addSkuScanned($data)
    {
        try {
            $old_barodes = [];
            $sql = "INSERT INTO ahqa_sku_scanned (`sku`, `product_name`, `price`, `qty`, `user_id`) VALUES";
            $checkData = "SELECT sku FROM ahqa_sku_scanned WHERE sku = :this_sku";
            
            foreach ($data->items as $row) {
                $stmt1 = $this->DB->prepare($checkData);
                $stmt1->bindParam(":this_sku", $row->sku);
                // if ($stmt1->execute() && $stmt1->rowCount() > 0) {
                //     echo '====>';
                //     $old_barodes[] = $row->sku;
                // } else {
                    $price = (isset($row->price) && is_numeric($row->price)) ? $row->price : 0;
                    $qty = (isset($row->qty) && is_numeric($row->qty)) ? $row->qty : 0;
                    $sql .= " ('" . $row->sku . "', '" . $row->title . "', '" . $price . "', '" . $qty . "', " . $row->user_id . "),";
                //}
            }

            //if ($sql !== "INSERT INTO ahqa_sku_scanned (`sku`, `product_name`, `price`, `qty`, `user_id`) VALUES") {
                $sql = rtrim($sql, ",");
                $comment = "";
                if (!empty($old_barodes)) {
                    $comment = $old_barodes;
                }
               
                $stmt = $this->DB->prepare($sql);
                if ($stmt->execute()) {
                    Response::json(1, 200, "SKUs has been added successfully.", "available", $comment);
                } else {
                    Response::json(0, 404, "Something went wrong.");
                }
           // } else {
                // if (!empty($old_barodes)) {
                //     Response::json(1, 200, "Barcodes already available", "available", $old_barodes);
                // } else {
                //     Response::json(0, 404, "No barcodes Sent to add.");
                // }
            //}

        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }
    // Delete a Post
    /*
    public function delete(int $id)
    {
        try {
            $sql =  "DELETE FROM `posts` WHERE `id`='$id'";
            $query = $this->DB->query($sql);
            if ($query->rowCount() > 0) {
                Response::json(1, 200, "Post has been deleted successfully.");
            }
            Response::json(0, 404, "Invalid Post ID.");
        } catch (PDOException $e) {
            Response::json(0, 500, $e->getMessage());
        }
    }
    */
    public function __destruct()
    {
        Database::closeConnection($this->DB);
    }
}