<?php
require_once '_master/apiHandler.php'; // الكلاس الأساسي
class pageClass extends apiHandler
{
    public function __construct()
    {
        parent::__construct();
        $this->checkERPAccess();
        if($this->clientSource !== 'web'){
            $checkTokenClient = $this->checkTokenClient();
            $this->getrequest->clientid = $checkTokenClient->clientid;
        }else if($this->clientSource == 'web' && $this->getrequest->clientid){
            $this->getrequest->clientid = $this->getrequest->clientid;
        }
        
    }

    function index()
    {
        try {
            $this->getrequest->status  = isset($this->getrequest->status) ? $this->getrequest->status : '';
            $this->getrequest->id = (!empty($this->getrequest->id)) ? (int) $this->getrequest->id : 0;
            $this->getrequest->page = (!empty($this->getrequest->page)) ? (int) $this->getrequest->page : 1;
            $this->getrequest->limit = (!empty($this->getrequest->limit)) ? (int) $this->getrequest->limit : 10;

            if ($this->getrequest->id > 0) {
                $rulesellbill_id = 'integer';
            } else {
                $rulesellbill_id = 'required|integer|min:1';
            }

            $allowed = ['page', 'limit', 'id', 'status'];
            $fields = [
                'page' => [
                    'rules' => 'integer|min:1',
                    'type'  => 'integer',
                    'default' => 1
                ],
                'limit' => [
                    'rules' => 'integer|min:1|max:100',
                    'type'  => 'integer',
                    'default' => 10
                ],
                'status' => [
                    'rules' => 'string|in:new,prepare,shipped,deliver,refused',
                    'type'  => 'string',
                    'message' => 'Status must be one of: new, prepare, shipped, deliver, or refused'
                ],
                'id' => [
                    'rules' => 'integer',
                    'type'  => 'integer',
                ],
            ];

            $request = $this->cleanRequestInputs((array)$this->getrequest, $fields, $allowed);
            if ($request['validation_errors']) $this->respond(401, false, $this->trans['httpStatusCodes']['401'], null, $request['validation_errors']);
            $src = $this->hosturl . '/upload/pages/';

            if ($request['id'] > 0) {
                $response = R::getRow("SELECT sellbill.sellbillid,sellbillclientid,sellbilldate,sellbilltotalbill,sellbilldiscount,sellbilldiscounttype,sellbillaftertotalbill,sellbilltotalpayed,addressid,couponid,paymenttypeid,paymentdetails
                                   FROM sellbill
                                   WHERE sellbill.conditions = 0 AND sellbill.sellbillid = ?", [$request['id']]);
                $sellbill = $response;
                if ($sellbill['sellbillid'] > 0) {
                    $itemsArr = [];
                    $items = R::getAll("SELECT sellbilldetail.*, product.productName,product.productNameE,product.logo 
                                    FROM sellbilldetail JOIN product ON product.productid = sellbilldetail.sellbilldetailproductid
                                    WHERE sellbilldetail.sellbillid = ? ", [$sellbill['sellbillid']]);
                    foreach ($items as $item) {
                        $itemsArr[] = [
                            'id' => $item['sellbilldetailid'],
                            'product' => [
                                'id' => $item['sellbilldetailproductid'],
                                'name_ar' => $item['productName'],
                                'name_en' => $item['productNameE'],
                                'images' => [($item['logo'] != '' && $item['logo'] != '.' ? $this->hosturl . "/views/default/images/product_image/" . $item['logo'] : '')],
                            ],
                            'quantity' => $item['sellbilldetailquantity'],
                            'price' => $item['sellbilldetailtotalprice'],
                            'discount' => $item['discountvalue'],
                        ];
                    }
                    $itemsCount = count($items);
                    $offerId = R::getCell("SELECT sellbillid FROM sellbilloffer WHERE orderSavedBillId = ? ", [$sellbill['sellbillid']]);
                    $firstStatus = R::getRow("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id ASC LIMIT 1", [$sellbill['sellbillid']]);
                    $lastStatus = R::getRow("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id DESC LIMIT 1", [$sellbill['sellbillid']]);
                    $client = R::getRow("SELECT * FROM client WHERE clientid = ?", [$sellbill['sellbillclientid']]);
                    $discount = $sellbill['sellbilldiscount'];
                    if ($sellbill['sellbilldiscounttype'] == 2) {
                        $discount = ($sellbill['sellbilldiscount'] / 100) * $sellbill['parcode'];
                    }

                    $coupon = R::load("coupons", $sellbill['couponid']);
                    if ($coupon) {
                        $coupon_discount = $coupon->discount_value;
                        if ($coupon->discount_type == 1) { // %
                            $billValue = $sellbill['sellbilltotalbill'] - $discount;
                            $coupon_discount = ($coupon->discount_value / 100) * $billValue;
                        }
                    }
                    $addressData = R::getRow("SELECT clientaddresses.name, clientaddresses.mobile,
                        concat(government.governmentname, ' / ', clientarea.name, ' / ', clientaddresses.addressDetails) as full_address
                                FROM `clientaddresses`
                                left join government on government.governmetid = clientaddresses.governorateid
                                left join clientarea on clientarea.id = clientaddresses.cityid
                                WHERE clientaddresses.id = ?", [$sellbill['addressid']]);

                    // Calculate Shipping
                    $settings = R::load('onlinestoremainsetting', 1);
                    if (!$settings) {
                        $this->respond(500, false, 'Online store settings not found.');
                    }

                    $subtotal = (float)$sellbill['sellbilltotalbill'];
                    //$itemsCount = itemsCount;

                    $clientarea = R::findOne('clientarea', 'id = ?', [$sellbill['addressid']]);
                    $default_shipping_cost = $clientarea ? $clientarea->shipping_cost : 0;

                    $shippingCost = $default_shipping_cost;

                    // Check for free shipping conditions
                    if ($settings->shippingfree_numberitems == 1 && $itemsCount >= $settings->numberitems) {
                        $shippingCost = 0;
                    } elseif ($settings->shippingfree_totleitems == 1 && $subtotal >= $settings->totleitems) {
                        $shippingCost = 0;
                    }
                    ## details
                    $resultsArray[] = [
                        'id' => $sellbill['sellbillid'],
                        'clientid' => $sellbill['sellbillclientid'],
                        'client' => $client,
                        'order_no' => $offerId,
                        'order_date' => $sellbill['sellbilldate'],
                        'tracking_no' => $firstStatus['id'],
                        'items_count' => $itemsCount,
                        'subtotal' => $sellbill['sellbilltotalbill'],
                        'shipping_cost' => $shippingCost,
                        'discount' => $discount,
                        'coupon_discount' => $coupon_discount,
                        'total' => $sellbill['sellbillaftertotalbill'],
                        'status' => $lastStatus['status'],
                        'payment_type' => R::getCell("SELECT name_ar FROM paymentmethods WHERE id = ?", [$sellbill['paymenttypeid']]), //name_en
                        'payment_status' => ($sellbill['sellbilltotalpayed'] == $sellbill['sellbillaftertotalbill'] ? 'paid' : 'pending'),
                        'address' => $addressData,
                        'items' => $itemsArr,
                        // 'address'=>["name"=> "Home Address",//name
                        //             "mobile"=>"+201234567890",//mobile
                        //             "owner_name"=>"Ahmed Mohamed",//????
                        //             "full_address"=>"123 Main Street, Maadi, Cairo"],
                    ];
                }


                $this->respond(200, true, $this->trans['httpStatusCodes']['200'], null, [], $resultsArray);
            }

            $allStatusArr = array("new", "prepare", "shipped", "deliver", "refused");

            // حساب الإزاحة (Offset)
            $offset = ($request['page'] > 0 ? $request['page'] - 1 : 0) * $request['limit'];
            $searchQuery = '';
            if ($request['clientid']) {
                $searchQuery .= " AND sellbill.sellbillclientid = ". $request['clientid'];
            }
            if ($request['status'] != '') {
                $sql = "SELECT sellbill.sellbillid,sellbilldate,sellbillclientid,sellbilltotalbill,sellbilldiscount,sellbilldiscounttype,sellbillaftertotalbill,sellbilltotalpayed,addressid,couponid,paymenttypeid,paymentdetails,sellbillstatus.status,tax
                                   FROM sellbill JOIN sellbillstatus ON sellbillstatus.sellbillid = sellbill.sellbillid
                                   WHERE sellbill.conditions = 0 $searchQuery AND sellbillstatus.status = ? limit ? OFFSET ?";
                $results = R::getAll($sql, [$request['status'], $request['limit'], $offset]);

                $totalRecords = R::getCell("SELECT COUNT(*) FROM sellbill JOIN sellbillstatus ON sellbillstatus.sellbillid = sellbill.sellbillid
                                   WHERE sellbill.conditions = 0 and  $searchQuery AND sellbillstatus.status = ?", [$request['status']]);

                $resultsArray = [];
                foreach ($results as $sellbill) {
                    $itemsCount = R::getCell("SELECT COUNT(*) FROM sellbilldetail WHERE sellbilldetail.sellbillid = ? ", [$sellbill['sellbillid']]);
                    $offerId = R::getCell("SELECT sellbillid FROM sellbilloffer WHERE orderSavedBillId = ? ", [$sellbill['sellbillid']]);
                    $firstStatus = R::getRow("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id ASC LIMIT 1", [$sellbill['sellbillid']]);
                    $lastStatus = R::getRow("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id DESC LIMIT 1", [$sellbill['sellbillid']]);
                    $client = R::getRow("SELECT * FROM client WHERE clientid = ?", [$sellbill['sellbillclientid']]);
                    $discount = $sellbill['sellbilldiscount'];
                    if ($sellbill['sellbilldiscounttype'] == 2) {
                        $discount = ($sellbill['sellbilldiscount'] / 100) * $sellbill['parcode'];
                    }
                    ## all user orders?
                    $resultsArray[] = [
                        'id' => $sellbill['sellbillid'],
                        'clientid' => $sellbill['sellbillclientid'],
                        'client' => $client,
                        'order_no' => $offerId,
                        'order_date' => $sellbill['sellbilldate'],
                        'tracking_no' => $firstStatus['id'],
                        'items_count' => $itemsCount,
                        'subtotal' => $sellbill['sellbilltotalbill'],
                        'discount' => $discount,
                        'total' => $sellbill['sellbillaftertotalbill'],
                        'status' => $lastStatus['status'],
                        'tax' => $sellbill['sellbilltotalbill'] * ($sellbill['tax'] / 100),
                        'payment_type' => R::getCell("SELECT name_ar FROM paymentmethods WHERE id = ?", [$sellbill['paymenttypeid']]), //name_en
                        'payment_status' => ($sellbill['sellbilltotalpayed'] == $sellbill['sellbillaftertotalbill'] ? 'paid' : 'pending'),
                    ];
                }
            } else {
                ## old get all orders with limit
                /*$sql = 'SELECT sellbill.sellbillid,sellbilldate,sellbilltotalbill,sellbilldiscount,sellbilldiscounttype,sellbillaftertotalbill,sellbilltotalpayed,addressid,couponid,paymenttypeid,paymentdetails,sellbillstatus.status,tax
                                   FROM sellbill JOIN sellbillstatus ON sellbillstatus.sellbillid = sellbill.sellbillid
                                   WHERE sellbill.conditions = 0 and sellbill.sellbillclientid = ? limit ? OFFSET ?';
                $results = R::getAll($sql, [$this->getrequest->clientid, $request['limit'], $offset]);

                $totalRecords = R::getCell("SELECT COUNT(*) FROM sellbill
                                   WHERE sellbill.conditions = 0 and sellbill.sellbillclientid = ? ", [$this->getrequest->clientid]);*/
                ## new get orders by status
                foreach ($allStatusArr as $status) {
                    // Apply limit and offset only for 'deliver' and 'refused' statuses
                    $sql = "SELECT sellbill.sellbillid,sellbilldate,sellbilltotalbill,sellbillclientid,sellbilldiscount,sellbilldiscounttype,sellbillaftertotalbill,sellbilltotalpayed,addressid,couponid,paymenttypeid,paymentdetails,sellbillstatus.status,tax
                                   FROM sellbill JOIN sellbillstatus ON sellbillstatus.sellbillid = sellbill.sellbillid
                            WHERE sellbill.conditions = 0  $searchQuery AND sellbillstatus.status = ? 
                            ORDER BY sellbill.sellbillid DESC LIMIT ? OFFSET ?";

                    $statusResults = R::getAll($sql, [$status, $request['limit'], $offset]);

                    // Get total count for this status
                    $totalRecords = R::getCell("SELECT COUNT(*) FROM sellbill 
                                        JOIN sellbillstatus ON sellbillstatus.sellbillid = sellbill.sellbillid
                                        WHERE sellbill.conditions = 0 $searchQuery AND sellbillstatus.status = ?", [$status]);
                    $resultsArray[$status] = [];
                    $resultsArray[$status]['orders'] = [];
                    foreach ($statusResults as $sellbill) {
                        $itemsCount = R::getCell("SELECT COUNT(*) FROM sellbilldetail WHERE sellbilldetail.sellbillid = ? ", [$sellbill['sellbillid']]);
                        $offerId = R::getCell("SELECT sellbillid FROM sellbilloffer WHERE orderSavedBillId = ? ", [$sellbill['sellbillid']]);
                        $firstStatus = R::getRow("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id ASC LIMIT 1", [$sellbill['sellbillid']]);
                        $lastStatus = R::getRow("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id DESC LIMIT 1", [$sellbill['sellbillid']]);
                        $client = R::getRow("SELECT * FROM client WHERE clientid = ?", [$sellbill['sellbillclientid']]);
                        $discount = $sellbill['sellbilldiscount'];
                        if ($sellbill['sellbilldiscounttype'] == 2) {
                            $discount = ($sellbill['sellbilldiscount'] / 100) * $sellbill['parcode'];
                        }
                        ## all user orders?
                        $resultsArray[$status]['orders'][] = [
                            'id' => $sellbill['sellbillid'],
                            'clientid' => $sellbill['sellbillclientid'],
                            'client' => $client,
                            'order_no' => $offerId,
                            'order_date' => $sellbill['sellbilldate'],
                            'tracking_no' => $firstStatus['id'],
                            'items_count' => $itemsCount,
                            'subtotal' => $sellbill['sellbilltotalbill'],
                            'discount' => $discount,
                            'total' => $sellbill['sellbillaftertotalbill'],
                            'status' => $lastStatus['status'],
                            'tax' => $sellbill['sellbilltotalbill'] * ($sellbill['tax'] / 100),
                            'payment_type' => R::getCell("SELECT name_ar FROM paymentmethods WHERE id = ?", [$sellbill['paymenttypeid']]), //name_en
                            'payment_status' => ($sellbill['sellbilltotalpayed'] == $sellbill['sellbillaftertotalbill'] ? 'paid' : 'pending'),
                        ];
                    }
                    $resultsArray[$status]['pagination'] = [
                        "current_page" => (int) $request['page'],
                        "total_pages" => ceil($totalRecords / $request['limit']),
                        "total_items" => (int) $totalRecords,
                        "per_page" => $request['limit']
                    ];
                }
            }


            if ($request['id'] > 0) {
                $response = $resultsArray;
            } else {
                if ($request['status'] != '') {
                    $response = array(
                        "orders"  => $resultsArray,
                        "pagination" => [
                            "current_page" => (int) $request['page'],
                            "total_pages" => ceil($totalRecords / $request['limit']),
                            "total_items" => (int) $totalRecords,
                            "per_page" => $request['limit']
                        ]
                    );
                } else {
                    $response = array(
                        $resultsArray,
                    );
                }
            }
            $this->respond(200, true, $this->trans['httpStatusCodes']['200'], null, [], $response);
        } catch (Exception $exception) {
            $this->respond(500, false, $this->trans['httpStatusCodes']['500'], $exception->getMessage(), []);
        }
    }
    
    
    function indexNew()
    {
        try {
            $this->getrequest->status  = isset($this->getrequest->status) ? $this->getrequest->status : '';
            $this->getrequest->id = (!empty($this->getrequest->id)) ? (int) $this->getrequest->id : 0;
            $this->getrequest->page = (!empty($this->getrequest->page)) ? (int) $this->getrequest->page : 1;
            $this->getrequest->limit = (!empty($this->getrequest->limit)) ? (int) $this->getrequest->limit : 10;

            if ($this->getrequest->id > 0) {
                $rulesellbill_id = 'integer';
            } else {
                $rulesellbill_id = 'required|integer|min:1';
            }

            $allowed = ['page', 'limit', 'id', 'status'];
            $fields = [
                'page' => [
                    'rules' => 'integer|min:1',
                    'type'  => 'integer',
                    'default' => 1
                ],
                'limit' => [
                    'rules' => 'integer|min:1|max:100',
                    'type'  => 'integer',
                    'default' => 10
                ],
                'status' => [
                    'rules' => 'string|in:new,prepare,shipped,deliver,refused',
                    'type'  => 'string',
                    'message' => 'Status must be one of: new, prepare, shipped, deliver, or refused'
                ],
                'id' => [
                    'rules' => 'integer',
                    'type'  => 'integer',
                ],
            ];

            $request = $this->cleanRequestInputs((array)$this->getrequest, $fields, $allowed);
            if ($request['validation_errors']) $this->respond(401, false, $this->trans['httpStatusCodes']['401'], null, $request['validation_errors']);
            $src = $this->hosturl . '/upload/pages/';

            if ($request['id'] > 0) {
                $response = R::getRow("SELECT sellbill.sellbillid,sellbillclientid,sellbilldate,sellbilltotalbill,sellbilldiscount,sellbilldiscounttype,sellbillaftertotalbill,sellbilltotalpayed,addressid,couponid,paymenttypeid,paymentdetails
                                   FROM sellbill
                                   WHERE sellbill.conditions = 0 AND sellbill.sellbillid = ?", [$request['id']]);
                $sellbill = $response;
                if ($sellbill['sellbillid'] > 0) {
                    $itemsArr = [];
                    $items = R::getAll("SELECT sellbilldetail.*, product.productName,product.productNameE,product.logo 
                                    FROM sellbilldetail JOIN product ON product.productid = sellbilldetail.sellbilldetailproductid
                                    WHERE sellbilldetail.sellbillid = ? ", [$sellbill['sellbillid']]);
                    foreach ($items as $item) {
                        $itemsArr[] = [
                            'id' => $item['sellbilldetailid'],
                            'product' => [
                                'id' => $item['sellbilldetailproductid'],
                                'name_ar' => $item['productName'],
                                'name_en' => $item['productNameE'],
                                'image' => ($item['logo'] != '' && $item['logo'] != '.' ? $this->hosturl . "/views/default/images/product_image/" . $item['logo'] : ''),
                                'images' => [($item['logo'] != '' && $item['logo'] != '.' ? $this->hosturl . "/views/default/images/product_image/" . $item['logo'] : '')],
                            ],
                            'quantity' => $item['sellbilldetailquantity'],
                            'price' => $item['sellbilldetailtotalprice'],
                            'discount' => $item['discountvalue'],
                        ];
                    }
                    $itemsCount = count($items);
                    $offerId = R::getCell("SELECT sellbillid FROM sellbilloffer WHERE orderSavedBillId = ? ", [$sellbill['sellbillid']]);
                    $firstStatus = R::getRow("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id ASC LIMIT 1", [$sellbill['sellbillid']]);
                    $lastStatus = R::getRow("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id DESC LIMIT 1", [$sellbill['sellbillid']]);
                    $client = R::getRow("SELECT * FROM client WHERE clientid = ?", [$sellbill['sellbillclientid']]);
                    $discount = $sellbill['sellbilldiscount'];
                    if ($sellbill['sellbilldiscounttype'] == 2) {
                        $discount = ($sellbill['sellbilldiscount'] / 100) * $sellbill['parcode'];
                    }

                    $coupon = R::load("coupons", $sellbill['couponid']);
                    if ($coupon) {
                        $coupon_discount = $coupon->discount_value;
                        if ($coupon->discount_type == 1) { // %
                            $billValue = $sellbill['sellbilltotalbill'] - $discount;
                            $coupon_discount = ($coupon->discount_value / 100) * $billValue;
                        }
                    }
                    $addressData = R::getRow("SELECT *,clientaddresses.id as id, government.governmentname, clientarea.name as city_name_ar , clientarea.name_en as city_name_en
                                FROM `clientaddresses`
                                left join government on government.governmetid = clientaddresses.governorateid
                                left join clientarea on clientarea.id = clientaddresses.cityid
                                WHERE clientaddresses.id = ?", [$sellbill['addressid']]);

                    // Calculate Shipping
                    $settings = R::load('onlinestoremainsetting', 1);
                    if (!$settings) {
                        $this->respond(500, false, 'Online store settings not found.');
                    }

                    $subtotal = (float)$sellbill['sellbilltotalbill'];
                    //$itemsCount = itemsCount;

                    $clientarea = R::findOne('clientarea', 'id = ?', [$sellbill['addressid']]);
                    $default_shipping_cost = $clientarea ? $clientarea->shipping_cost : 0;

                    $shippingCost = $default_shipping_cost;

                    // Check for free shipping conditions
                    if ($settings->shippingfree_numberitems == 1 && $itemsCount >= $settings->numberitems) {
                        $shippingCost = 0;
                    } elseif ($settings->shippingfree_totleitems == 1 && $subtotal >= $settings->totleitems) {
                        $shippingCost = 0;
                    }
                    ## details
                    $paymentmethods =  $this->hosturl.'/upload/paymentmethods/';
                    $resultsArray = [
                        'id' => $sellbill['sellbillid'],
                        'clientid' => $sellbill['sellbillclientid'],
                        'client' => $client,
                        'order_no' => $offerId,
                        'order_date' => $sellbill['sellbilldate'],
                        'tracking_no' => $firstStatus['id'],
                        'items_count' => $itemsCount,
                        'subtotal' => $sellbill['sellbilltotalbill'],
                        'shipping_cost' => $shippingCost,
                        'discount' => $discount,
                        'coupon_discount' => $coupon_discount,
                        'total' => $sellbill['sellbillaftertotalbill'],
                        'status' => $lastStatus['status'],
                            'payment_type' => R::getRow("SELECT *, CONCAT('$paymentmethods', image) as path_image FROM paymentmethods WHERE id = ?", [$sellbill['paymenttypeid']]), //name_en
                        'payment_status' => ($sellbill['sellbilltotalpayed'] == $sellbill['sellbillaftertotalbill'] ? 'paid' : 'pending'),
                        'address' => $addressData,
                        'items' => $itemsArr,
                        // 'address'=>["name"=> "Home Address",//name
                        //             "mobile"=>"+201234567890",//mobile
                        //             "owner_name"=>"Ahmed Mohamed",//????
                        //             "full_address"=>"123 Main Street, Maadi, Cairo"],
                    ];
                }


                $this->respond(200, true, $this->trans['httpStatusCodes']['200'], null, [], $resultsArray);
            }

            $allStatusArr = array("new", "prepare", "shipped", "deliver", "refused");

            // حساب الإزاحة (Offset)
            $offset = ($request['page'] > 0 ? $request['page'] - 1 : 0) * $request['limit'];
            $searchQuery = '';
            if ($request['clientid']) {
                $searchQuery .= " AND sellbill.sellbillclientid = ". $request['clientid'];
            }
            if ($request['status'] != '') {
                $sql = "SELECT sellbill.sellbillid,sellbilldate,sellbillclientid,sellbilltotalbill,sellbilldiscount,sellbilldiscounttype,sellbillaftertotalbill,sellbilltotalpayed,addressid,couponid,paymenttypeid,paymentdetails,sellbillstatus.status,tax
                                   FROM sellbill JOIN sellbillstatus ON sellbillstatus.sellbillid = sellbill.sellbillid
                                   WHERE sellbill.conditions = 0 $searchQuery AND sellbillstatus.status = ? limit ? OFFSET ?";
                $results = R::getAll($sql, [$request['status'], $request['limit'], $offset]);

                $totalRecords = R::getCell("SELECT COUNT(*) FROM sellbill JOIN sellbillstatus ON sellbillstatus.sellbillid = sellbill.sellbillid
                                   WHERE sellbill.conditions = 0 and  $searchQuery AND sellbillstatus.status = ?", [$request['status']]);

                $resultsArray = [];
                foreach ($results as $sellbill) {
                    $itemsCount = R::getCell("SELECT COUNT(*) FROM sellbilldetail WHERE sellbilldetail.sellbillid = ? ", [$sellbill['sellbillid']]);
                    $offerId = R::getCell("SELECT sellbillid FROM sellbilloffer WHERE orderSavedBillId = ? ", [$sellbill['sellbillid']]);
                    $firstStatus = R::getRow("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id ASC LIMIT 1", [$sellbill['sellbillid']]);
                    $lastStatus = R::getRow("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id DESC LIMIT 1", [$sellbill['sellbillid']]);
                    $client = R::getRow("SELECT * FROM client WHERE clientid = ?", [$sellbill['sellbillclientid']]);
                    $discount = $sellbill['sellbilldiscount'];
                    if ($sellbill['sellbilldiscounttype'] == 2) {
                        $discount = ($sellbill['sellbilldiscount'] / 100) * $sellbill['parcode'];
                    }
                    ## all user orders?
                    $resultsArray[] = [
                        'id' => $sellbill['sellbillid'],
                        'clientid' => $sellbill['sellbillclientid'],
                        'client' => $client,
                        'order_no' => $offerId,
                        'order_date' => $sellbill['sellbilldate'],
                        'tracking_no' => $firstStatus['id'],
                        'items_count' => $itemsCount,
                        'subtotal' => $sellbill['sellbilltotalbill'],
                        'discount' => $discount,
                        'total' => $sellbill['sellbillaftertotalbill'],
                        'status' => $lastStatus['status'],
                        'tax' => $sellbill['sellbilltotalbill'] * ($sellbill['tax'] / 100),
                        'payment_type' => R::getCell("SELECT name_ar FROM paymentmethods WHERE id = ?", [$sellbill['paymenttypeid']]), //name_en
                        'payment_status' => ($sellbill['sellbilltotalpayed'] == $sellbill['sellbillaftertotalbill'] ? 'paid' : 'pending'),
                    ];
                }
            } else {
                $sql = "SELECT sellbill.sellbillid,sellbilldate,sellbilltotalbill,sellbillclientid,sellbilldiscount,sellbilldiscounttype,sellbillaftertotalbill,sellbilltotalpayed,addressid,couponid,paymenttypeid,paymentdetails,sellbillstatus.status,tax
                                   FROM sellbill JOIN sellbillstatus ON sellbillstatus.sellbillid = sellbill.sellbillid
                            WHERE sellbill.conditions = 0  $searchQuery 
                            ORDER BY sellbill.sellbillid DESC LIMIT ? OFFSET ?";
                $orders = R::getAll($sql, [$request['limit'], $offset]); 
                foreach ($orders as $key => $order) {
                    $itemsCount = R::getCell("SELECT COUNT(*) FROM sellbilldetail WHERE sellbilldetail.sellbillid = ? ", [$order['sellbillid']]);
                    $offerId = R::getCell("SELECT sellbillid FROM sellbilloffer WHERE orderSavedBillId = ? ", [$order['sellbillid']]);
                    $orders[$key]['itemsCount'] = $itemsCount;
                    $orders[$key]['offerId'] = $offerId;
                }
                
                $totalRecords = R::getCell("SELECT COUNT(*) FROM sellbill 
                                        JOIN sellbillstatus ON sellbillstatus.sellbillid = sellbill.sellbillid
                                        WHERE sellbill.conditions = 0 $searchQuery ");
                            
            }


            if ($request['id'] > 0) {
                $response = $resultsArray;
            } else {
                if ($request['status'] != '') {
                    $response = array(
                        "orders"  => $resultsArray,
                        "pagination" => [
                            "current_page" => (int) $request['page'],
                            "total_pages" => ceil($totalRecords / $request['limit']),
                            "total_items" => (int) $totalRecords,
                            "per_page" => $request['limit']
                        ]
                    );
                } else {
                    $response = array(
                        "orders"  => $orders,
                        "pagination" => [
                            "current_page" => (int) $request['page'],
                            "total_pages" => ceil($totalRecords / $request['limit']),
                            "total_items" => (int) $totalRecords,
                            "per_page" => $request['limit']
                        ]
                    );
                }
            }
            $this->respond(200, true, $this->trans['httpStatusCodes']['200'], null, [], $response);
        } catch (Exception $exception) {
            $this->respond(500, false, $this->trans['httpStatusCodes']['500'], $exception->getMessage(), []);
        }
    }
    
    
    function track()
    {
        $this->getrequest->id = (!empty($this->getrequest->id)) ? (int) $this->getrequest->id : 0;
        $allowed = ['id'];
        $fields = [
            'id' => [
                'rules' => 'integer',
                'type'  => 'integer',
            ],
        ];
        $request = $this->cleanRequestInputs((array)$this->getrequest, $fields, $allowed);
        if ($request['validation_errors']) $this->respond(401, false, $this->trans['httpStatusCodes']['401'], null, $request['validation_errors']);
        if ($request['id'] > 0) {
            $firstStatus = R::getRow("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id ASC LIMIT 1", [$request['id']]);
            $orderAllStatus = R::getAll("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id DESC", [$request['id']]);
            if (!$orderAllStatus) {
                $this->respond(404, false, $this->trans['httpStatusCodes']['404'], null, []);
            } else {
                $stArrTmp = array();
                foreach ($orderAllStatus as $lastStatus) {
                    $stArrTmp[] = [
                        'tracking_no' => $firstStatus['id'],
                        'status' => $lastStatus['status'],
                        'updated_at' => $lastStatus['sysdate']
                    ];
                }
                $deliveredRows = array_filter($stArrTmp, function ($item) {
                    return $item['status'] === 'deliver';
                });
                $response = array(
                    "data"  => ["deliverstatus" => $deliveredRows, 'statusArr' => $stArrTmp]
                );
                // $response = array(
                //     "data"  => [
                //         'order_no' => $this->getrequest->id,
                //         'tracking_no' => $lastStatus['id'],
                //         'status' => $lastStatus['status'],
                //         'updated_at' => $lastStatus['sysdate'],
                //     ]
                // );
            }
            $this->respond(200, true, $this->trans['httpStatusCodes']['200'], null, [], $response);
        }
    }

    function cancel()
    {
        try {

            $this->getrequest->id = (!empty($this->getrequest->id)) ? (int) $this->getrequest->id : 0;
            $allowed = ['id'];
            $fields = [
                'id' => [
                    'rules' => 'integer',
                    'type'  => 'integer',
                ],
            ];
            $request = $this->cleanRequestInputs((array)$this->getrequest, $fields, $allowed);
            if ($request['validation_errors']) $this->respond(401, false, $this->trans['httpStatusCodes']['401'], null, $request['validation_errors']);
            $id = !empty($request['id']) ? (int)$request['id'] : 0;
            // Get order ID from request
            $clientId = $this->getrequest->clientid;



            if ($clientId <= 0) {
                $this->respond(400, false, $this->trans['httpStatusCodes']['400'], 'Client ID is required', []);
                return;
            }

            ###################
            $QueryString = '';
            if ($id > 0) {
                $QueryString = ' and sellbill.sellbillid = ' . $id;
            } else {
                $QueryString = ' and sellbillclientid = ' . $clientId;
            }
            // Get the sellbill ID from the offer table
            $sellbillId = R::getCell("SELECT sellbill.sellbillid 
                FROM sellbill 
                join sellbillstatus on sellbillstatus.sellbillid = sellbill.sellbillid
                WHERE conditions = 0 $QueryString and sellbilltotalpayed < sellbillaftertotalbill and sellbillstatus.status in ('new','prepare','shipped')");

            // Check if the order exists
            if (!$sellbillId) {
                $this->respond(404, false, $this->trans['httpStatusCodes']['404'], 'No Pending Order found', []);
                return;
            }



            //
            $_GET['sellbillid'] = $sellbillId;
            //
            $_GET["do"] = "delete";
            $control = "sellbillController.php";
            $jsonresponse = $this->CURL_WEB($control);
            $data = json_decode($jsonresponse, true);
            $flag = isset($data['status']) && $data['status'] == 1 ? 'success' : 'error';

            if ($flag == 'success') {
                $order_status = 'refused';
                $id = $sellbillId;
                R::exec("UPDATE sellbillstatus SET `status` = ? WHERE sellbillid = ?", [$order_status, $id]);

                // Add history record
                $status = R::dispense('sellbillstatushistory');
                $status->sellbillid = $id;
                $status->status = $order_status;
                $status->sysdate = date('Y-m-d H:i:s');
                $status->userid = 1; //$request['clientid'];
                R::store($status);
                
                // Return success response
                $response = [
                    'order_id' => $sellbillId,
                    'status' => 'cancelled',
                    'message' => 'Order has been cancelled successfully'
                ];

                $this->respond(200, true, $this->trans['httpStatusCodes']['200'], null, [], $response);
            } else {
                $response = [
                    'order_id' => $sellbillId,
                    'message' => 'failed to cancel order'
                ];
                $this->respond(400, false, $this->trans['httpStatusCodes']['400'], null, $response);
            }
        } catch (Exception $e) {
            // Rollback on error
            $this->respond(500, false, $this->trans['httpStatusCodes']['500'], $e->getMessage(), []);
        }
    }


    function rating()
    {
        try {
            $allowed = ['id', 'rate', 'rating_text'];
            // Input validation rules
            $fields = [
                'id' => [
                    'rules' => 'required|integer|min:1',
                    'message' => 'Invalid Order ID'
                ],
                'rate' => [
                    'rules' => 'required|integer|min:0|max:5',
                    'message' => 'Rating must be between 0 and 5'
                ],
                'rating_text' => [
                    'rules' => 'max:1000',
                    'message' => 'Rating text is too long (max 1000 characters)'
                ],
                
            ];
            $request = $this->cleanRequestInputs((array)$this->getrequest, $fields, $allowed);
            if ($request['validation_errors']) $this->respond(400, false, $this->trans['httpStatusCodes']['400'], null, $request['validation_errors']);

            // Validate input
            $id = !empty($request['id']) ? (int)$request['id'] : 0;
            $rate = !empty($request['rate']) ? (int)$request['rate'] : 0;
            $rating_text = !empty($request['rating_text']) ? $request['rating_text'] : '';

            // Get the sell bill
            $sellbill = R::getCell("SELECT sellbillid FROM sellbill WHERE sellbillid = ?", [$id]);

            if (empty($sellbill)) {
                throw new Exception('Order not found');
            }

            R::exec("UPDATE sellbill SET rating = ?, rating_text = ? WHERE sellbillid = ?", [$rate, $rating_text, $id]);
            
            
            
            
            foreach($this->getrequest->productRatings as  $productRating){
                $rateBean = R::findOne('products_rateing', 'client_id = ? AND product_id = ?', [
                    $this->getrequest->clientid,
                    $productRating['productId']
                ]) ?: R::xdispense('products_rateing');
                
                $rateBean->client_id = $this->getrequest->clientid;
                $rateBean->product_id = $productRating['productId'];
                $rateBean->rate = $productRating['rating'];
                $rateBean->comment = $productRating['comment'];
                $rateBean->sys_date = date('Y-m-d H:i:s');
                $rateBean->is_verified = 1;
                R::store($rateBean);
                
                // تحديث متوسط التقييم
                $averageRate = R::getCell('SELECT AVG(rate) FROM products_rateing WHERE product_id = ?', [$productRating['productId']]);
                R::exec("UPDATE product SET average_rate = $averageRate WHERE productId = ?", [$productRating['productId']]);
                
            }
            
            
            $response = [
                'id' => $id,
                'rate' => $rate,
                'rating_text' => $rating_text,
                'products_rating' => $this->getrequest->productRatings
            ];
            // Return success response
            $this->respond(200, true, $this->trans['httpStatusCodes']['200'], null, [], $response);
        } catch (Exception $e) {
            // Handle errors
            $this->respond(500, false, $this->trans['httpStatusCodes']['500'], $e->getMessage(), []);
        }
    }
    
    
    function productRating()
    {
        try {
            $allowed = ['id', 'rate', 'rating_text'];
            // Input validation rules
            $fields = [
                'id' => [
                    'rules' => 'required|integer|min:1',
                    'message' => 'Invalid Order ID'
                ],
                'rate' => [
                    'rules' => 'required|integer|min:0|max:5',
                    'message' => 'Rating must be between 0 and 5'
                ],
                'rating_text' => [
                    'rules' => 'max:1000',
                    'message' => 'Rating text is too long (max 1000 characters)'
                ],
                
            ];
            $request = $this->cleanRequestInputs((array)$this->getrequest, $fields, $allowed);
            if ($request['validation_errors']) $this->respond(400, false, $this->trans['httpStatusCodes']['400'], null, $request['validation_errors']);

            // Validate input
            $id = !empty($request['id']) ? (int)$request['id'] : 0;
            $rate = !empty($request['rate']) ? (int)$request['rate'] : 0;
            $rating_text = !empty($request['rating_text']) ? $request['rating_text'] : '';
            
            $rateBean = R::findOne('products_rateing', 'client_id = ? AND product_id = ?', [
                $this->getrequest->clientid,
                $id
            ]) ?: R::xdispense('products_rateing');
            
            $rateBean->client_id = $this->getrequest->clientid;
            $rateBean->product_id = $id;
            $rateBean->rate = $rate;
            $rateBean->comment = $rating_text;
            $rateBean->sys_date = date('Y-m-d H:i:s');
            $rateBean->is_verified = 0;
            R::store($rateBean);
            
                  
            // تحديث متوسط التقييم
            $averageRate = R::getCell('SELECT AVG(rate) FROM products_rateing WHERE product_id = ?', [$id]);
            R::exec("UPDATE product SET average_rate = $averageRate WHERE productId = ?", [$id]);
            
            // Return success response
            $this->respond(200, true, $this->trans['httpStatusCodes']['200'], null, [], $rateBean);
        } catch (Exception $e) {
            // Handle errors
            $this->respond(500, false, $this->trans['httpStatusCodes']['500'], $e->getMessage(), []);
        }
    }

    



    function getrating()
    {
        try {
            $allowed = ['id'];
            // Input validation rules
            $fields = [
                'id' => [
                    'rules' => 'required|integer|min:1',
                    'message' => 'Invalid Order ID'
                ]
            ];

            $request = $this->cleanRequestInputs((array)$this->getrequest, $fields, $allowed);
            if ($request['validation_errors']) {
                $this->respond(400, false, $this->trans['httpStatusCodes']['400'], null, $request['validation_errors']);
                return;
            }

            $id = (int)$request['id'];

            // Get the rating and rating text
            $rating = R::getRow("SELECT rating as rate, rating_text FROM sellbill WHERE sellbillid = ?", [$id]);

            if (empty($rating)) {
                $this->respond(404, false, $this->trans['httpStatusCodes']['404'], 'Order not found', []);
                return;
            }

            $response = [
                'rate' => $rating['rate'],
                'rating_text' => $rating['rating_text']
            ];

            $this->respond(200, true, $this->trans['httpStatusCodes']['200'], null, [], $response);
        } catch (Exception $e) {
            // Handle errors
            $this->respond(500, false, $this->trans['httpStatusCodes']['500'], $e->getMessage(), []);
        }
    }
    
    function updateStatus()
    {
        try {
            $allowed = ['id', 'order_status'];
            // Input validation rules
            $fields = [
                'id' => [
                    'rules' => 'required|integer|min:1',
                    'message' => 'Invalid Order ID'
                ],
                'order_status' => [
                    'rules' => 'text',
                    'type' => 'string',
                    'message' => 'Invalid Order Status'
                ]
            ];
            $request = $this->cleanRequestInputs((array)$this->getrequest, $fields, $allowed);
            if ($request['validation_errors']) $this->respond(400, false, $this->trans['httpStatusCodes']['400'], null, $request['validation_errors']);

            // Validate input
            $id = !empty($request['id']) ? (int)$request['id'] : 0;
            $order_status = !empty($request['order_status']) ? $request['order_status'] : '';

            // Get the sell bill
            $sellbill = R::getRow("SELECT sellbillid, sellbillfinalbill, sellbillclientid  FROM sellbill WHERE sellbillid = ?", [$id]);

            if (!$sellbill) {
                throw new Exception('Order not found');
            }

            // Get current status to calculate time differences
            $currentStatus = R::getRow("SELECT * FROM sellbillstatus WHERE sellbillid = ? ORDER BY id DESC LIMIT 1", [$id]);
            
            // Calculate time based on status transition
            $preparing_time = $currentStatus['preparing_time'];
            $shipping_time = $currentStatus['shipping_time'];
            $delivery_time = $currentStatus['delivery_time'];
            
            if ($currentStatus && $order_status != $currentStatus['status']) {
                $currentTime = time();
                $previousTime = strtotime($currentStatus['sysdate']);
                $timeDiff = round(($currentTime - $previousTime) / 3600, 2); // Convert to hours with 2 decimal places
                
                if ($currentStatus['status'] == 'new' && $order_status == 'prepare') {
                    $preparing_time = $timeDiff;
                } elseif ($currentStatus['status'] == 'prepare' && $order_status == 'shipped') {
                    $shipping_time = $timeDiff;
                } elseif ($currentStatus['status'] == 'shipped' && $order_status == 'deliver') {
                    $delivery_time = $timeDiff;
                }
            }

            R::exec("UPDATE sellbillstatus SET `status` = ?, preparing_time = ?, shipping_time = ?, delivery_time = ? WHERE sellbillid = ?", [$order_status, $preparing_time, $shipping_time, $delivery_time, $id]);

            // Add history record
            $status = R::dispense('sellbillstatushistory');
            $status->sellbillid = $id;
            $status->status = $order_status;
            $status->sysdate = date('Y-m-d H:i:s');
            $status->userid = 1; //$request['clientid'];
            R::store($status);
            
            if($order_status == 'deliver'){
                $onlinestoremainsetting = R::getRow("SELECT points, point_value FROM onlinestoremainsetting");
                if($onlinestoremainsetting['points'] > 0 ){
                    $sellbill['sellbillfinalbill'] = 1000;
                    $pointsPerGbp = $onlinestoremainsetting['points'];
                    $pointValue = $onlinestoremainsetting['point_value'];
                    $orderAmount =  $sellbill['sellbillfinalbill'];
                    $pointsValueInGbp = $earnedPoints / $points;
                    $earnedPoints = $orderAmount * $pointsPerGbp;
                    $pointsValueInGbp = $earnedPoints * $pointValue;
                    
                    $sellbillclientpoints = R::findOne(
                        'sellbillclientpoints',
                        'clientid = ? AND sellbillid = ?',
                        [$sellbill['sellbillclientid'], $id]
                    );
                    
                    $oldPointsValue = 0;
                    if ($sellbillclientpoints) {
                        $oldPointsValue = $sellbillclientpoints->pointsvalue;
                    } else {
                        $sellbillclientpoints = R::dispense('sellbillclientpoints');
                    }
                    
                    $sellbillclientpoints->sellbillid = $id;
                    $sellbillclientpoints->clientid = $sellbill['sellbillclientid'];
                    $sellbillclientpoints->finalbill = $sellbill['sellbillfinalbill'];
                    $sellbillclientpoints->points = $pointsPerGbp;
                    $sellbillclientpoints->point_value = $pointValue;
                    $sellbillclientpoints->earnedpoints = $earnedPoints;
                    $sellbillclientpoints->pointsvalue = $pointsValueInGbp;
                    $sellbillclientpoints->sysdate = date('Y-m-d H:i:s');
                    $sellbillclientpoints->userid = 1;
                    R::store($sellbillclientpoints);
                    
                    R::exec(
                        "UPDATE client 
                         SET pointsvalue = pointsvalue - ? + ?
                         WHERE clientid = ?",
                        [$oldPointsValue, $pointsValueInGbp, $sellbill['sellbillclientid']]
                    );
                    
                }
            }

            $response = [
                'id' => $id,
                'order_status' => $order_status,
                /*'preparing_time' => $preparing_time,
                'shipping_time' => $shipping_time,
                'delivery_time' => $delivery_time*/
            ];
            // Return success response
            $this->respond(200, true, $this->trans['httpStatusCodes']['200'], null, [], $response);
        } catch (Exception $e) {
            // Handle errors
            $this->respond(500, false, $this->trans['httpStatusCodes']['500'], $e->getMessage(), []);
        }
    }
    
    function clientPoints()
    {
            try{
            $this->getrequest->page  = isset($this->getrequest->page) ? (int) $this->getrequest->page : 1;
            $this->getrequest->limit    =isset($this->getrequest->limit) ? (int) $this->getrequest->limit : 10;
            $this->getrequest->id  = isset($this->getrequest->id) ? (int) $this->getrequest->id : 0;
            $allowed = ['clientid','page', 'limit','id'];
            $fields = [
                'clientid' => [
                    'rules' => 'required|integer|min:1',
                    'type'  => 'integer'
                ],
                'page' => [
                    'rules' => 'required|integer|min:1',
                    'type'  => 'integer',
                ],
                'limit' => [
                    'rules' => 'required|integer|min:1|max:10000',
                    'type'  => 'integer',
                ],
                'id' => [
                    'rules' => 'integer',
                    'type'  => 'integer',
                ],
            ];

            $request = $this->cleanRequestInputs((array)$this->getrequest, $fields, $allowed);
            if ($request['validation_errors']) $this->respond(401,false,$this->trans['httpStatusCodes']['401'], null,$request['validation_errors']);
            
            
            if ($request['id'] > 0) {
                $response = R::getRow("SELECT sellbillclientpoints.* FROM sellbillclientpoints left join client on client.clientid = sellbillclientpoints.clientid left join sellbill on sellbill.sellbillid = sellbillclientpoints.sellbillid 
                WHERE id = ? ",[$request['id']]);
                $this->respond(200,true,$this->trans['httpStatusCodes']['200'], null, [],$response);
            }
 
            
            $searchQuery = "";
            if ($request['searchName']) {
                $ids = R::getCol("SELECT id FROM sellbillclientpoints WHERE (sellbillclientpoints.clientid LIKE ? or sellbillclientpoints.sellbillid LIKE ?) ", ['%' . $request['searchName'] . '%', '%' . $request['searchName'] . '%']);
                $idList = implode(",", $ids);
                $searchQuery .= " AND id in (0$idList)";
            }
            
             
            if ($request['clientid']) {
                $searchQuery .= " AND sellbillclientpoints.clientid = " . $request['clientid'];
            }
         

            // حساب الإزاحة (Offset)
            $offset = ($request['page'] > 0 ? $request['page'] - 1 : 0) * $request['limit'];

            // استخدام معاملات آمنة بدل دمج النصوص في SQL
            $sql = "SELECT sellbillclientpoints.*, sellbillclientpoints.id as id FROM sellbillclientpoints left join client on client.clientid = sellbillclientpoints.clientid left join sellbill on sellbill.sellbillid = sellbillclientpoints.sellbillid  
            WHERE  1 $searchQuery  LIMIT ? OFFSET ?";
            $results   = R::getAll($sql, array($request['limit'], $offset));
            
            // إجمالي السجلات
            $totalRecords = R::getCell("SELECT COUNT(*) FROM sellbillclientpoints WHERE 1 $searchQuery");

            if ($request['id'] > 0) {
                $response = $results;
            }else{
                $response = array(
                    "sellbillclientpoints"  => $results,
                    "pagination" => [
                      "current_page"=> (int) $request['page'],
                      "total_pages"=> ceil($totalRecords / $request['limit']),           
                      "total_items"=> (int) $totalRecords,
                      "per_page"=> $request['limit']
                    ]
                );
            }
            $this->respond(200,true,$this->trans['httpStatusCodes']['200'], null, [],$response);
            
            
        }catch (Exception $exception){
            $this->respond(500,false,$this->trans['httpStatusCodes']['500'], $exception->getMessage(), []);
        }
    }
        
        
        
    
    function clientOrdersReport()
    {
        try {
            $allowed = ['clientid', 'date_from', 'date_to', 'page', 'limit'];
            $fields = [
                'clientid' => [
                    'rules' => 'integer|min:1',
                    'message' => 'Client ID must be a positive integer'
                ],
                'date_from' => [
                    'rules' => 'required|date',
                    'message' => 'Date from is required and must be a valid date (Y-m-d format)'
                ],
                'date_to' => [
                    'rules' => 'required|date',
                    'message' => 'Date to is required and must be a valid date (Y-m-d format)'
                ],
                'page' => [
                    'rules' => 'integer|min:1',
                    'type' => 'integer',
                    'default' => 1
                ],
                'limit' => [
                    'rules' => 'integer|min:1|max:10',
                    'type' => 'integer',
                    'default' => 10
                ]
            ];
            
            $request = $this->cleanRequestInputs((array)$this->getrequest, $fields, $allowed);
            if ($request['validation_errors']) {
                $this->respond(400, false, $this->trans['httpStatusCodes']['400'], null, $request['validation_errors']);
                return;
            }

            $clientId = !empty($request['clientid']) ? (int)$request['clientid'] : null;
            $dateFrom = $request['date_from'];
            $dateTo = $request['date_to'];
            $page = (int)$request['page'];
            $limit = (int)$request['limit'];
            $offset = ($page > 0)? (($page - 1) * $limit) : $limit;

            // Validate date range
            if (strtotime($dateFrom) > strtotime($dateTo)) {
                $this->respond(400, false, $this->trans['httpStatusCodes']['400'], 'Date from cannot be greater than date to', []);
                return;
            }

            // Build WHERE clause based on whether client ID is provided
            $whereClause = "date(s.sellbilldate) >= ? AND date(s.sellbilldate) <= ? AND s.conditions = 0";
            $params = [$dateFrom, $dateTo];
            
            if ($clientId) {
                $whereClause .= " AND s.sellbillclientid = ?";
                $params[] = $clientId;
            }
            // Get grouped client data with pagination
            $clientReports = R::getAll("SELECT c.clientid,c.clientname,
                    COUNT(DISTINCT s.sellbillid) as total_orders,
                    COALESCE(SUM(s.sellbilltotalbill), 0) as total_value,
                    COALESCE(SUM(s.sellbillaftertotalbill), 0) as total_value_after_discount,
                    COALESCE(AVG(sb.preparing_time), 0) as mean_preparation_time,
                    COALESCE(AVG(sb.shipping_time + sb.delivery_time), 0) as mean_delivery_time,
                    COALESCE(AVG(s.rating), 0) as mean_rating,
                    COUNT(CASE WHEN s.rating > 0 THEN 1 END) as rated_orders_count
                FROM sellbill s
                LEFT JOIN sellbillstatus sb ON sb.sellbillid = s.sellbillid
                LEFT JOIN client c ON c.clientid = s.sellbillclientid
                WHERE $whereClause
                GROUP BY c.clientid, c.clientname
                ORDER BY s.sellbillid
                LIMIT ? OFFSET ?", array_merge($params, [$limit, $offset]));

            // Get total count for pagination
            $totalClients = R::getCell("SELECT COUNT(DISTINCT c.clientid) as total_clients FROM sellbill s 
            LEFT JOIN client c ON c.clientid = s.sellbillclientid WHERE $whereClause", $params);

            // Format the response
            $reports = [];
            foreach ($clientReports as $report) {
                $reports[] = [
                    'client_id' => $report['clientid'],
                    'client_name' => $report['clientname'] ?: 'Unknown Client',
                    'total_orders' => (int)$report['total_orders'],
                    'total_value' => round((float)$report['total_value'], 2),
                    'total_value_after_discount' => round((float)$report['total_value_after_discount'], 2),
                    'mean_preparation_time' => round((float)$report['mean_preparation_time'], 2) . " Hours",
                    'mean_delivery_time' => round((float)$report['mean_delivery_time'], 2) . " Hours",
                    'mean_rating' => $report['rated_orders_count'] > 0 ? round((float)$report['mean_rating'], 2) : 0
                ];
            }

            $response = [
                'period' => [
                    'date_from' => $dateFrom,
                    'date_to' => $dateTo
                ],
                'data' => $reports,
                'pagination' => [
                    "current_page"=> (int) $page,
                    "total_pages"=> ceil($totalClients / $limit),           
                    "total_items"=> (int) $totalClients,
                    "per_page"=> $limit
                ]
                // 'pagination' => [
                //     'current_page' => $page,
                //     'per_page' => $limit,
                //     'total_clients' => (int)$totalClients,
                //     'total_pages' => ceil($totalClients / $limit)
                // ]
            ];

            $this->respond(200, true, $this->trans['httpStatusCodes']['200'], null, [], $response);

        } catch (Exception $e) {
            $this->respond(500, false, $this->trans['httpStatusCodes']['500'], $e->getMessage(), []);
        }
    }

    function statistics()
    {
        try {
            // Get today's date and current month
            $today = date('Y-m-d');
            $currentMonth = date('Y-m');
            $firstDayOfMonth = date('Y-m-01');
            $lastDayOfMonth = date('Y-m-t');

            // Get last month dates
            $lastMonth = date('Y-m', strtotime('-1 month'));
            $firstDayOfLastMonth = date('Y-m-01', strtotime('-1 month'));
            $lastDayOfLastMonth = date('Y-m-t', strtotime('-1 month'));

            // Total Sales Today
            $totalSalesToday = R::getCell("SELECT COALESCE(SUM(sellbillaftertotalbill), 0) 
                                        FROM sellbill 
                                        WHERE DATE(sellbilldate) = ? AND conditions = 0", [$today]);

            // Total Sales This Month (from day 1 to today)
            $totalSalesThisMonth = R::getCell("SELECT COALESCE(SUM(sellbillaftertotalbill), 0) 
                                             FROM sellbill 
                                             WHERE DATE(sellbilldate) >= ? AND DATE(sellbilldate) <= ? AND conditions = 0", 
                                             [$firstDayOfMonth, $today]);

            // Total Sales Last Month (full month)
            $totalSalesLastMonth = R::getCell("SELECT COALESCE(SUM(sellbillaftertotalbill), 0) 
                                             FROM sellbill 
                                             WHERE DATE(sellbilldate) >= ? AND DATE(sellbilldate) <= ? AND conditions = 0", 
                                             [$firstDayOfLastMonth, $lastDayOfLastMonth]);

            // New Clients Today
            $newClientsToday = R::getCell("SELECT COUNT(*) 
                                         FROM client 
                                         WHERE DATE(clientdate) = ?", [$today]);

            // New Clients This Month (from day 1 to today)
            $newClientsThisMonth = R::getCell("SELECT COUNT(*) 
                                             FROM client 
                                             WHERE DATE(clientdate) >= ? AND DATE(clientdate) <= ?", 
                                             [$firstDayOfMonth, $today]);

            // New Clients Last Month (full month)
            $newClientsLastMonth = R::getCell("SELECT COUNT(*) 
                                             FROM client 
                                             WHERE DATE(clientdate) >= ? AND DATE(clientdate) <= ?", 
                                             [$firstDayOfLastMonth, $lastDayOfLastMonth]);

            // Orders count for today and this month
            $ordersToday = R::getCell("SELECT COUNT(*) 
                                     FROM sellbill 
                                     WHERE DATE(sellbilldate) = ? AND conditions = 0", [$today]);

            $ordersThisMonth = R::getCell("SELECT COUNT(*) 
                                         FROM sellbill 
                                         WHERE DATE(sellbilldate) >= ? AND DATE(sellbilldate) <= ? AND conditions = 0", 
                                         [$firstDayOfMonth, $today]);

            $ordersLastMonth = R::getCell("SELECT COUNT(*) 
                                         FROM sellbill 
                                         WHERE DATE(sellbilldate) >= ? AND DATE(sellbilldate) <= ? AND conditions = 0", 
                                         [$firstDayOfLastMonth, $lastDayOfLastMonth]);

            // Average order value
            $avgOrderToday = $ordersToday > 0 ? $totalSalesToday / $ordersToday : 0;
            $avgOrderThisMonth = $ordersThisMonth > 0 ? $totalSalesThisMonth / $ordersThisMonth : 0;
            $avgOrderLastMonth = $ordersLastMonth > 0 ? $totalSalesLastMonth / $ordersLastMonth : 0;

            // Calculate percentage changes
            $salesGrowth = $totalSalesLastMonth > 0 ? (($totalSalesThisMonth - $totalSalesLastMonth) / $totalSalesLastMonth) * 100 : 0;
            $clientsGrowth = $newClientsLastMonth > 0 ? (($newClientsThisMonth - $newClientsLastMonth) / $newClientsLastMonth) * 100 : 0;
            $ordersGrowth = $ordersLastMonth > 0 ? (($ordersThisMonth - $ordersLastMonth) / $ordersLastMonth) * 100 : 0;
            $avgOrderGrowth = $avgOrderLastMonth > 0 ? (($avgOrderThisMonth - $avgOrderLastMonth) / $avgOrderLastMonth) * 100 : 0;

            // Top Selling Products This Month
            $topProducts = R::getAll("SELECT p.productid, p.productName, p.productNameE, p.logo,
                                    SUM(sbd.sellbilldetailquantity) as total_quantity,
                                    SUM(sbd.sellbilldetailtotalprice) as total_revenue,
                                    COUNT(DISTINCT sbd.sellbillid) as orders_count
                                    FROM sellbilldetail sbd
                                    JOIN sellbill sb ON sb.sellbillid = sbd.sellbillid
                                    JOIN product p ON p.productid = sbd.sellbilldetailproductid
                                    WHERE DATE(sb.sellbilldate) >= ? AND DATE(sb.sellbilldate) <= ? AND sb.conditions = 0
                                    GROUP BY p.productid, p.productName, p.productNameE, p.logo
                                    ORDER BY total_quantity DESC
                                    LIMIT 10", [$firstDayOfMonth, $today]);

            // Top Selling Product Categories This Month
            $topCategories = R::getAll("SELECT pc.productCatId, pc.productCatName,
                                        SUM(sbd.sellbilldetailquantity) as total_quantity,
                                        SUM(sbd.sellbilldetailtotalprice) as total_revenue,
                                        COUNT(DISTINCT sbd.sellbillid) as orders_count,
                                        COUNT(DISTINCT p.productid) as products_count
                                        FROM sellbilldetail sbd
                                        JOIN sellbill sb ON sb.sellbillid = sbd.sellbillid
                                        JOIN product p ON p.productid = sbd.sellbilldetailproductid
                                        JOIN productcat pc ON pc.productCatId = p.productCatId
                                        WHERE DATE(sb.sellbilldate) >= ? AND DATE(sb.sellbilldate) <= ? AND sb.conditions = 0
                                        GROUP BY pc.productCatId, pc.productCatName
                                        ORDER BY total_quantity DESC
                                        LIMIT 10", [$firstDayOfMonth, $today]);

            // Format top products
            $formattedTopProducts = [];
            foreach ($topProducts as $product) {
                $formattedTopProducts[] = [
                    'id' => $product['productid'],
                    'name_ar' => $product['productName'],
                    'name_en' => $product['productNameE'],
                    'image' => $product['logo'] != '' && $product['logo'] != '.' ? $this->hosturl . "/views/default/images/product_image/" . $product['logo'] : '',
                    'total_quantity' => (int)$product['total_quantity'],
                    'total_revenue' => round((float)$product['total_revenue'], 2),
                    'orders_count' => (int)$product['orders_count']
                ];
            }

            // Format top categories
            $formattedTopCategories = [];
            foreach ($topCategories as $category) {
                $formattedTopCategories[] = [
                    'id' => $category['productCatId'],
                    'name' => $category['productCatName'],
                    //'name_en' => $category['productCatNameE'],
                    'total_quantity' => (int)$category['total_quantity'],
                    'total_revenue' => round((float)$category['total_revenue'], 2),
                    'orders_count' => (int)$category['orders_count'],
                    'products_count' => (int)$category['products_count']
                ];
            }

            // Sales by Branch This Month
            $branchSales = R::getAll("SELECT b.branchId, b.branchName, b.nameEn,
                                    COALESCE(SUM(sb.sellbillaftertotalbill), 0) as total_sales,
                                    COUNT(DISTINCT sb.sellbillid) as orders_count,
                                    COUNT(DISTINCT sb.sellbillclientid) as clients_count
                                    FROM sellbill sb
                                    JOIN store s ON s.storeId = sb.sellbillstoreid
                                    JOIN branch b ON b.branchId = s.branchId
                                    WHERE DATE(sb.sellbilldate) >= ? AND DATE(sb.sellbilldate) <= ? AND sb.conditions = 0
                                    GROUP BY b.branchId, b.branchName, b.nameEn
                                    ORDER BY total_sales DESC", [$firstDayOfMonth, $today]);

            // Format branch sales
            $formattedBranchSales = [];
            foreach ($branchSales as $branch) {
                $formattedBranchSales[] = [
                    'id' => $branch['branchId'],
                    'name_ar' => $branch['branchName'],
                    'name_en' => $branch['nameEn'],
                    'total_sales' => round((float)$branch['total_sales'], 2),
                    'orders_count' => (int)$branch['orders_count'],
                    'clients_count' => (int)$branch['clients_count']
                ];
            }

            $response = [
                'period' => [
                    'today' => $today,
                    'current_month' => $currentMonth,
                    'last_month' => $lastMonth,
                    'current_month_range' => [
                        'from' => $firstDayOfMonth,
                        'to' => $today
                    ],
                    'last_month_range' => [
                        'from' => $firstDayOfLastMonth,
                        'to' => $lastDayOfLastMonth
                    ]
                ],
                'sales' => [
                    'total_today' => round((float)$totalSalesToday, 2),
                    'total_this_month' => round((float)$totalSalesThisMonth, 2),
                    'total_last_month' => round((float)$totalSalesLastMonth, 2),
                    'orders_today' => (int)$ordersToday,
                    'orders_this_month' => (int)$ordersThisMonth,
                    'orders_last_month' => (int)$ordersLastMonth,
                    'avg_order_today' => round((float)$avgOrderToday, 2),
                    'avg_order_this_month' => round((float)$avgOrderThisMonth, 2),
                    'avg_order_last_month' => round((float)$avgOrderLastMonth, 2),
                    'growth' => [
                        'sales_percentage' => round((float)$salesGrowth, 2),
                        'orders_percentage' => round((float)$ordersGrowth, 2),
                        'avg_order_percentage' => round((float)$avgOrderGrowth, 2)
                    ]
                ],
                'clients' => [
                    'new_today' => (int)$newClientsToday,
                    'new_this_month' => (int)$newClientsThisMonth,
                    'new_last_month' => (int)$newClientsLastMonth,
                    'growth' => [
                        'clients_percentage' => round((float)$clientsGrowth, 2)
                    ]
                ],
                'top_products' => $formattedTopProducts,
                'top_categories' => $formattedTopCategories,
                'branch_sales' => $formattedBranchSales
            ];

            $this->respond(200, true, $this->trans['httpStatusCodes']['200'], null, [], $response);

        } catch (Exception $e) {
            $this->respond(500, false, $this->trans['httpStatusCodes']['500'], $e->getMessage(), []);
        }
    }

    function CURL_WEB($control)
    {
        ##preparing url
        //$projectURL = 'http://' . $_SERVER['HTTP_HOST'] . explode('controllers', $_SERVER['REQUEST_URI'])[0] . 'controllers/';
        $url = 'http://' . $_SERVER['HTTP_HOST'] . explode('controllers', $_SERVER['REQUEST_URI'])[0] . 'controllers/' . $control;
        $i = 0;
        foreach ($_GET as $key => $value) {
            if ($i == 0) {
                $url .= "?$key=$value";
            } else {
                $url .= "&$key=$value";
            }
            $i++;
        }


        $data_arr = array();
        ##preparing req. session
        $_POST['curlpost'] = 1;
        //set them static may change later

        require_once('../../models/sql/ConnectionProperty.class.php');
        $_SESSION['dbname'] = ConnectionProperty::getDatabase(); //"erp";
        $_SESSION['lic_dateto'] = date('Y-m-d');
        //
        $_SESSION['attempt'] = 0;
        $_SESSION['hidecat'] = 1;
        $_SESSION['clientnegative'] = 1;
        $_SESSION['suplliernegative'] = 1;
        $_SESSION['storenegative'] = 1;


        $_SESSION['userid'] = 1;
        $_SESSION['usergroupid'] = 1;
        $_SESSION['username'] = "admin";
        $_SESSION['usergroupname'] = "admin";
        $_SESSION['saveid'] = 1;
        $_SESSION['employeename'] = "admin";
        $_SESSION['searchinonesave'] = 0;
        $_SESSION['branchId'] = 1;
        $_SESSION['erp_lang'] = $lang = isset($allheaders["lang"]) ? $allheaders["lang"] : 'ar';
        $data_arr['sessionlist'] = json_encode($_SESSION);
        ##appending data
        foreach ($_POST as $key => $value) {
            $data_arr[$key] = $value;
        }

        ## start sending
        $post = $data_arr;
        $ch = curl_init($url);
        //

        ## this if i want to use another api controller headers will be needed
        curl_setopt($ch, CURLOPT_HTTPHEADER, $this->getMainHeaderVars()); //// newwwwwwwwww

        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $post); //
        curl_setopt($ch, CURLOPT_FOLLOWLOCATION, false); // Set to false
        //Disable CURLOPT_SSL_VERIFYHOST and CURLOPT_SSL_VERIFYPEER by
        //setting them to false.
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        //curl_setopt($ch, CURLOPT_TIMEOUT, 10); //
        // execute!
        $response = curl_exec($ch);
        $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $location = curl_getinfo($ch, CURLINFO_REDIRECT_URL);
        if ($http_code == 301 || $http_code == 302 || $http_code == 307 || $http_code == 308) {
            if ($location) {
                curl_close($ch);
                $ch = curl_init($location);
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($ch, CURLOPT_POSTFIELDS, $post);
                //setting them to false.
                curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
                curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
                $response = curl_exec($ch);
            }
        }
        if ($response === false) {
            //echo 'Error : ' . curl_error($ch);
        }

        // close the connection, release resources used
        curl_close($ch);
        // do anything you want with your response
        //var_dump($response);
        //print_r($response);
        return $response;
    }

    function getMainHeaderVars()
    {
        // Step 1: Get headers from the incoming request
        $headers = getallheaders();
        $openkey = isset($headers['openkey']) ? $headers['openkey'] : '';
        $lang = isset($headers['lang']) ? $headers['lang'] : 'ar';
        $accesstoken = isset($headers['accesstoken']) ? $headers['accesstoken'] : '';
        $useERPIDs = isset($headers['useERPIDs']) ? $headers['useERPIDs'] : 0;
        // Step 2: Prepare the header format for cURL
        $curlHeaders = [
            // "Authorization: Bearer " . $this->accessToken,
            // "Content-Type: application/json"
            "openkey: $openkey",
            "lang: $lang",
            "accesstoken: $accesstoken",
            "useERPIDs: $useERPIDs",
        ];
        return $curlHeaders;
    }

    public function __destruct()
    {
        parent::__destruct();
    }
}
require_once("_master/fireApi.php");
