<?php
require_once __DIR__ . "/Database.php";
require_once __DIR__ . "/Main.php";

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, $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 {
            $suborderId=$data->order_id;
            $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);
            $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`= 'start_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 = "start_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;
            // echo $data->order_status;
            
        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");
                } else {
                  
                  // o count of on the way stat
                  
            $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);
                if ($the_post->order_status==$data->order_status) {
                    Response::json(0, 200, "You already marked ".$data->order_status);
                }
                // 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;
                }
                if ($order_status == "ready_to_dispatch") {
                    Response::json(0, 400, "This Order cannot be transfer-Inform to Teamlad");
                    return;
                }
                 if ($order_status == "complete" && $data->latitude && $data->longitude){
                      // 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();
                 }
                //  elseif ($order_status == "ready_to_dispatch"){
                //       // Update `ahqa_delivery_type_suborders`
                //     $update_sql = "UPDATE `ahqa_delivery_type_suborders` 
                //               SET `order_status` = :order_status, driver_flag=1
                //               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();
                //  }
                 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`) 
                         VALUES (:subgroup_identifier, :comment, :order_status, :user_id)";
                $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->execute();
        
                Response::json(1, 200, "Order status updated Successfully");
            } else {
                Response::json(0, 404, "Invalid order ID: " . $subgroup_identifier);
            }
            
            
                  
                  
                  
                  
            }  
            
                
        } else {
              
            // except on the way 
            
            
               
            $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);
                $order_id=$this_post->order_id;
                  $queryTotalEndpicked = "
                        SELECT subgroup_identifier
                        FROM ahqa_sales_order_item 
                        WHERE order_id = :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_INT);
                    $stmtTotalEndPicked->execute();
                 if($stmtTotalEndPicked->rowCount() > 0) {
                    Response::json(0, 400, "Please check all items.");
                 }
                if ($the_post->order_status==$data->order_status) {
                    Response::json(0, 200, "You already marked ".$data->order_status);
                }
                // 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;
                }
                if ($order_status == "ready_to_dispatch") {
                    Response::json(0, 400, "This Order cannot be transfer-Inform to Teamlad");
                    return;
                }
                 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);
    
                       
                                
                                $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();
                 }
                 elseif ($order_status == "end_picking"){ //on end picked calculaet total 
                     // Example of fetching total base price
                     
                    $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->fetch(PDO::FETCH_ASSOC);
                        // Iterate through the results if needed
                        foreach ($resultTotal as $row) {
                            if($subgroup_identifier==$row['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", $row['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", $row['subgroup_identifier'], PDO::PARAM_STR);
                                $stmt->bindParam(":grand_total", $row['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`) 
                         VALUES (:subgroup_identifier, :comment, :order_status, :user_id)";
                $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->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_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 ($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 {
                        $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);
    }
}