<?php
//require_once 'rb-mysql.php';

class storeHelper
{

    public static function getStoreDetails($storeId, $productId, $unittype, $sizeColorStoreDetailId, $sizeId, $colorId)
    {
        if ((isset($sizeColorStoreDetailId) && $sizeColorStoreDetailId > 0) || ($sizeId > 0 && $colorId > 0)) { //sizecolor product
            if ((!isset($sizeColorStoreDetailId) || empty($sizeColorStoreDetailId)) && ($sizeId > 0 && $colorId > 0)) {
                $storeDetailId = 0;
                $productQuantityBefore = 0;
                $storeDetailData = null;
            } else {
                // Convert sizeColorStoreDetailDAO->load to RedBeanPHP
                $storeDetailData = R::load('sizecolorstoredetail', $sizeColorStoreDetailId);
                $storeDetailId = $storeDetailData->id;
                $productQuantityBefore = $storeDetailData->quantity;
            }
        } else {
            // Convert queryWithStoreAndProductandunit to RedBeanPHP
            $storeDetailData = R::getRow('
                SELECT sd.*, p.productName, p.productSellUnitPrice, p.productBuyPrice, s.storeName
                FROM storedetail sd 
                JOIN product p ON sd.productid = p.productId 
                JOIN store s ON sd.storeid = s.storeId
                WHERE p.conditions = 0 AND s.conditions = 0 
                AND sd.productid = ? AND sd.unittype = ? AND sd.storeid = ?
            ', [$productId, $unittype, $storeId]);

            if ($storeDetailData) {
                $storeDetailId = $storeDetailData['storedetailid'];
                $productQuantityBefore = $storeDetailData['productquantity'];
                // Convert array to object for consistency
                $storeDetailData = (object) $storeDetailData;
            } else {
                $storeDetailId = 0;
                $productQuantityBefore = 0;
                $storeDetailData = null;
            }
        }

        return array(
            $storeDetailData,
            $storeDetailId,
            $productQuantityBefore
        );
    }

    public static function increaseProductQuantity($storedetailId, $productquantityBefore, $productChangeAmount, $colName, $detailId, $productId, $tableName, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $isreturn, $isadd, $sizeColorStoreDetailId, $sizeId, $colorId)
    {
        // Calculate new quantity
        $productquantityAfter = $productquantityBefore + $productChangeAmount;

        // Load store detail using RedBeanPHP
        $storeDetail = R::load('storedetail', $storedetailId);

        if (empty($sizeColorStoreDetailId) && $sizeId > 0 && $colorId > 0) {
            // Try to find existing size/color combination in store 1
            $existingSizeColorDetail = R::findOne(
                'sizecolorstoredetail',
                'productid = ? AND storeid = ? AND sizeid = ? AND colorid = ?',
                [$storeDetail->productid, 1, $sizeId, $colorId]
            );

            if ($existingSizeColorDetail) {
                // Create new size/color detail based on existing one
                $newSizeColorDetail = R::dispense('sizecolorstoredetail');
                $newSizeColorDetail->productid = $existingSizeColorDetail->productid;
                $newSizeColorDetail->storeid = $storeDetail->storeid;
                $newSizeColorDetail->sizeid = $existingSizeColorDetail->sizeid;
                $newSizeColorDetail->colorid = $existingSizeColorDetail->colorid;
                $newSizeColorDetail->quantity = 0;
                $newSizeColorDetail->userid = $_SESSION['userid'];
                $newSizeColorDetail->sysdate = date("Y-m-d H:i:s");

                $sizeColorStoreDetailId = R::store($newSizeColorDetail);
            }
        }

        if (isset($sizeColorStoreDetailId) && $sizeColorStoreDetailId > 0) {
            // Handle size/color store detail
            $sizeColorStoreDetail = R::load('sizecolorstoredetail', $sizeColorStoreDetailId);

            $storeId = $sizeColorStoreDetail->storeid;
            $retDataArr = lastAndMeanBuyPrice_Sell($colName, $detailId, $productId, $tableName, $productquantityBefore, $productquantityAfter, $productChangeAmount, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $isreturn, $isadd, $storeId);
            $isCollectiveFlag = $retDataArr['isCollectiveFlag'];

            // Update size/color store detail
            $sizeColorStoreDetail->userid = $_SESSION['userid'];
            $sizeColorStoreDetail->sysdate = date("Y-m-d H:i:s");
            $sizeColorStoreDetail->quantity = $productquantityAfter;
            R::store($sizeColorStoreDetail);

            onlineTempStoreDetailFunc($sizeColorStoreDetail->storeid, $sizeColorStoreDetail->productid, $sizeColorStoreDetail->sizeid, $sizeColorStoreDetail->colorid, abs($productChangeAmount), 1);

            // Check if main store detail exists for this product
            $mainStoreDetail = R::findOne(
                'storedetail',
                'productid = ? AND storeid = ? AND unittype = ?',
                [$productId, $sizeColorStoreDetail->storeid, 0]
            );

            if (!$mainStoreDetail) {
                insertStoredetail($sizeColorStoreDetail->storeid, $productId, $productChangeAmount, $colName, $detailId, $productId, $tableName, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $isreturn, $isadd, $sizeColorStoreDetailId, $sizeId, $colorId);
            } else {
                // Update main store detail with sum of child quantities
                $totalQuantity = R::getCell(
                    'SELECT SUM(quantity) FROM sizecolorstoredetail WHERE productid = ? AND storeid = ?',
                    [$sizeColorStoreDetail->productid, $sizeColorStoreDetail->storeid]
                );

                $mainStoreDetail->productquantity = $totalQuantity ?: 0;
                $mainStoreDetail->userid = $_SESSION['userid'];
                $mainStoreDetail->storedetaildate = date("Y-m-d H:i:s");
                R::store($mainStoreDetail);
            }
        } else {
            // Handle regular store detail (no size/color)
            $storeId = R::getCell('SELECT storeid FROM storedetail WHERE storedetailid = ?', [$storedetailId]);
            $retDataArr = lastAndMeanBuyPrice_Sell($colName, $detailId, $productId, $tableName, $productquantityBefore, $productquantityAfter, $productChangeAmount, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $isreturn, $isadd, $storeId);
            $isCollectiveFlag = $retDataArr['isCollectiveFlag'];

            // Update store detail quantity if not collective
            if ($isCollectiveFlag == 0) {
                $storeDetail->productquantity = $productquantityAfter;
                $storeDetail->userid = $_SESSION['userid'];
                $storeDetail->storedetaildate = date("Y-m-d H:i:s");
                R::store($storeDetail);

                onlineTempStoreDetailFunc($storeId, $productId, 0, 0, abs($productChangeAmount), 1);
            }
        }

        return $productquantityAfter;
    }

    //update and decrease Rawmaterialamount in storerawmaterialdetails tbl
    public static function decreaseProductQuantity($storedetailId, $productquantityBefore, $productChangeAmount, $colName, $detailId, $productId, $tableName, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $isreturn, $isadd, $sizeColorStoreDetailId, $sizeId, $colorId)
    {
        // Calculate new quantity
        $productquantityAfter = $productquantityBefore - $productChangeAmount;

        // Load store detail using RedBeanPHP
        $storeDetail = R::load('storedetail', $storedetailId);

        if (empty($sizeColorStoreDetailId) && $sizeId > 0 && $colorId > 0) {
            // Try to find existing size/color combination in store 1
            $existingSizeColorDetail = R::findOne(
                'sizecolorstoredetail',
                'productid = ? AND storeid = ? AND sizeid = ? AND colorid = ?',
                [$storeDetail->productid, 1, $sizeId, $colorId]
            );

            if ($existingSizeColorDetail) {
                // Create new size/color detail based on existing one
                $newSizeColorDetail = R::dispense('sizecolorstoredetail');
                $newSizeColorDetail->productid = $existingSizeColorDetail->productid;
                $newSizeColorDetail->storeid = $storeDetail->storeid;
                $newSizeColorDetail->sizeid = $existingSizeColorDetail->sizeid;
                $newSizeColorDetail->colorid = $existingSizeColorDetail->colorid;
                $newSizeColorDetail->quantity = 0;
                $newSizeColorDetail->userid = $_SESSION['userid'];
                $newSizeColorDetail->sysdate = date("Y-m-d H:i:s");

                $sizeColorStoreDetailId = R::store($newSizeColorDetail);
            }
        }

        if (isset($sizeColorStoreDetailId) && $sizeColorStoreDetailId > 0) {
            // Handle size/color store detail
            $sizeColorStoreDetail = R::load('sizecolorstoredetail', $sizeColorStoreDetailId);

            $storeId = $sizeColorStoreDetail->storeid;
            $retDataArr = lastAndMeanBuyPrice_Sell($colName, $detailId, $productId, $tableName, $productquantityBefore, $productquantityAfter, $productChangeAmount, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $isreturn, $isadd, $storeId);
            $isCollectiveFlag = $retDataArr['isCollectiveFlag'];

            // Update size/color store detail
            $sizeColorStoreDetail->userid = $_SESSION['userid'];
            $sizeColorStoreDetail->sysdate = date("Y-m-d H:i:s");
            $sizeColorStoreDetail->quantity = $productquantityAfter;
            R::store($sizeColorStoreDetail);

            onlineTempStoreDetailFunc($sizeColorStoreDetail->storeid, $sizeColorStoreDetail->productid, $sizeColorStoreDetail->sizeid, $sizeColorStoreDetail->colorid, (abs($productChangeAmount) * -1), 1);

            // Check if main store detail exists for this product
            $mainStoreDetail = R::findOne(
                'storedetail',
                'productid = ? AND storeid = ? AND unittype = ?',
                [$productId, $sizeColorStoreDetail->storeid, 0]
            );

            if (!$mainStoreDetail) {
                insertStoredetail($sizeColorStoreDetail->storeid, $productId, $productChangeAmount, $colName, $detailId, $productId, $tableName, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $isreturn, $isadd, $sizeColorStoreDetailId, $sizeId, $colorId);
            } else {
                // Update main store detail with sum of child quantities
                $totalQuantity = R::getCell(
                    'SELECT SUM(quantity) FROM sizecolorstoredetail WHERE productid = ? AND storeid = ?',
                    [$sizeColorStoreDetail->productid, $sizeColorStoreDetail->storeid]
                );

                $mainStoreDetail->productquantity = $totalQuantity ?: 0;
                $mainStoreDetail->userid = $_SESSION['userid'];
                $mainStoreDetail->storedetaildate = date("Y-m-d H:i:s");
                R::store($mainStoreDetail);
            }
        } else {
            // Handle regular store detail (no size/color)
            $storeId = R::getCell('SELECT storeid FROM storedetail WHERE storedetailid = ?', [$storedetailId]);
            $retDataArr = lastAndMeanBuyPrice_Sell($colName, $detailId, $productId, $tableName, $productquantityBefore, $productquantityAfter, $productChangeAmount, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $isreturn, $isadd, $storeId);
            $isCollectiveFlag = $retDataArr['isCollectiveFlag'];

            // Update store detail quantity if not collective
            if ($isCollectiveFlag == 0) {
                $storeDetail->productquantity = $productquantityAfter;
                $storeDetail->userid = $_SESSION['userid'];
                $storeDetail->storedetaildate = date("Y-m-d H:i:s");
                R::store($storeDetail);

                onlineTempStoreDetailFunc($storeId, $productId, 0, 0, (abs($productChangeAmount) * -1), 1);
            }
        }

        return $productquantityAfter;
    }

    // onlineTempStoreDetailFunc ifCollectiveProductReCalculateCost
    /*public static function lastAndMeanBuyPrice_Sell($colName, $detailId, $productId, $tableName, $productquantityBefore, $productquantityAfter, $productChangeAmount, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $isreturn, $isadd, $storeId)
    {
        global $sellbillId; //needed for collective product##fatma
        global $ordertype;

        if ($ordertype == 0) {
            // Load product using RedBeanPHP
            $buyProduct = R::load('product', $productId);

            // Set default values if null or zero
            if ($buyProduct->meanbuyprice == NULL || $buyProduct->meanbuyprice == 0) {
                $buyProduct->meanbuyprice = $buyProduct->productbuyprice;
            }
            if ($buyProduct->lastbuyprice == NULL || $buyProduct->lastbuyprice == 0) {
                $buyProduct->lastbuyprice = $buyProduct->productbuyprice;
            }
            if ($buyProduct->lastbuyprice_withdiscount == NULL || $buyProduct->lastbuyprice_withdiscount == 0) {
                $buyProduct->lastbuyprice_withdiscount = $buyProduct->productbuyprice;
            }
            if ($buyProduct->meanbuyprice_withdiscount == NULL || $buyProduct->meanbuyprice_withdiscount == 0) {
                $buyProduct->meanbuyprice_withdiscount = $buyProduct->productbuyprice;
            }
            if (empty($buyProduct->overallaverageprice) || $buyProduct->overallaverageprice === NULL) {
                $buyProduct->overallaverageprice = $buyProduct->productbuyprice;
            }

            // Recalculate cost if collective product
            $buyProduct = ifCollectiveProductReCalculateCost($buyProduct);

            // Update prices in detail table using RedBeanPHP
            if (isset($detailId) && !empty($detailId)) {
                $detailRecord = R::load($tableName, $detailId);
                if ($detailRecord->id) {
                    $detailRecord->lastbuyprice = $buyProduct->lastbuyprice;
                    $detailRecord->meanbuyprice = $buyProduct->meanbuyprice;
                    $detailRecord->lastbuyprice_withdiscount = $buyProduct->lastbuyprice_withdiscount;
                    $detailRecord->meanbuyprice_withdiscount = $buyProduct->meanbuyprice_withdiscount;
                    $detailRecord->lastbuyprice_withtax = (float) $buyProduct->lastbuyprice_withtax;
                    $detailRecord->meanbuyprice_withtax = (float) $buyProduct->meanbuyprice_withtax;
                    $detailRecord->lastbuyprice_withdiscountandtax = (float) $buyProduct->lastbuyprice_withdiscountandtax;
                    R::store($detailRecord);
                }
            }

            // Calculate quick profit
            quickProfitRow($buyProduct, $productChangeAmount, $isreturn, $isadd, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $storeId);

            // Handle collective product ingredients based on operation type
            if ($isreturn == 0 && $isadd == 0) {
                //ret sell - increase
                $isCollectiveFlag = ifCollectiveIncreaseItsIngriedientsInStore($buyProduct, $productChangeAmount, $sellbillId, $storeId, $tableName);
            } elseif ($isreturn == 0 && $isadd == 1) {
                //sell - decrease
                $isCollectiveFlag = ifCollectiveDecreaseItsIngriedientsInStore($buyProduct, $productChangeAmount, $sellbillId, $storeId, $tableName);
            } elseif ($isreturn == 1 && $isadd == 0) {
                //sell - decrease
                $isCollectiveFlag = ifCollectiveDecreaseItsIngriedientsInStore($buyProduct, $productChangeAmount, $sellbillId, $storeId, $tableName);
            } elseif ($isreturn == 1 && $isadd == 1) {
                //ret sell - increase
                $isCollectiveFlag = ifCollectiveIncreaseItsIngriedientsInStore($buyProduct, $productChangeAmount, $sellbillId, $storeId, $tableName);
            }

            return array('isCollectiveFlag' => $isCollectiveFlag);
        }
    }*/

    //add in storerawmaterialdetails tbl
    public static function insertStoredetail($storeid, $productid, $productChangeAmount, $colName, $detailId, $productId, $tableName, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $isreturn, $isadd, $sizeColorStoreDetailId, $sizeId, $colorId)
    {
        // Handle size/color store detail creation
        if (empty($sizeColorStoreDetailId) && $sizeId > 0 && $colorId > 0) {
            // Try to find existing size/color combination in store 1 to copy from
            $existingSizeColorDetail = R::findOne(
                'sizecolorstoredetail',
                'productid = ? AND storeid = ? AND sizeid = ? AND colorid = ?',
                [$productid, 1, $sizeId, $colorId]
            );

            if ($existingSizeColorDetail) {
                // Create new size/color detail based on existing one
                $newSizeColorDetail = R::dispense('sizecolorstoredetail');
                $newSizeColorDetail->productid = $existingSizeColorDetail->productid;
                $newSizeColorDetail->storeid = $storeid;
                $newSizeColorDetail->sizeid = $existingSizeColorDetail->sizeid;
                $newSizeColorDetail->colorid = $existingSizeColorDetail->colorid;
                $newSizeColorDetail->quantity = $productChangeAmount;
                $newSizeColorDetail->userid = $_SESSION['userid'];
                $newSizeColorDetail->sysdate = date("Y-m-d H:i:s");

                $sizeColorStoreDetailId = R::store($newSizeColorDetail);
            }
        }

        // Check if store detail already exists for this product and store
        $existingStoreDetail = R::findOne(
            'storedetail',
            'productid = ? AND storeid = ?',
            [$productid, $storeid]
        );

        if (!$existingStoreDetail) {
            // Create new store detail
            $newStoreDetail = R::dispense('storedetail');
            $newStoreDetail->userid = $_SESSION['userid'];
            $newStoreDetail->storedetaildate = date("Y-m-d H:i:s");
            $newStoreDetail->productquantity = $productChangeAmount;
            $newStoreDetail->storeid = $storeid;
            $newStoreDetail->productid = $productid;
            $newStoreDetail->unittype = 0; // Default unit type

            $newDetailId = R::store($newStoreDetail);
        } else {
            // Update existing store detail with sum of child quantities
            $totalQuantity = R::getCell(
                'SELECT SUM(quantity) FROM sizecolorstoredetail WHERE productid = ? AND storeid = ?',
                [$productid, $storeid]
            );

            $existingStoreDetail->productquantity = $totalQuantity ?: $productChangeAmount;
            $existingStoreDetail->userid = $_SESSION['userid'];
            $existingStoreDetail->storedetaildate = date("Y-m-d H:i:s");
            R::store($existingStoreDetail);

            $newDetailId = $existingStoreDetail->id;
        }

        // Update pricing information
        lastAndMeanBuyPrice_Sell($colName, $newDetailId, $productId, $tableName, 0, $productChangeAmount, $productChangeAmount, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $isreturn, $isadd, $storeid);
    }


    public static function ifCollectiveProductReCalculateCost($buyProduct)
    {
        global $noOfDecimalPlaces;

        // Get product ingredients using RedBeanPHP
        $ingredients = R::find('productingredients', 'productid = ?', [$buyProduct->productid]);

        if (count($ingredients) > 0) {
            // Reset cost prices
            $buyProduct->productbuyprice = 0;
            $buyProduct->lastbuyprice = 0;
            $buyProduct->lastbuyprice_withdiscount = 0;
            $buyProduct->meanbuyprice = 0;
            $buyProduct->meanbuyprice_withdiscount = 0;
            $buyProduct->overallaverageprice = 0;
            $buyProduct->lastbuyprice_withtax = 0;
            $buyProduct->meanbuyprice_withtax = 0;
            $buyProduct->lastbuyprice_withdiscountandtax = 0;

            foreach ($ingredients as $ingredient) {
                // Load ingredient product details
                $ingredientProduct = R::load('product', $ingredient->ingridientid);

                if ($ingredientProduct->id) {
                    $multiplier = $ingredient->quantity * $ingredient->productnumber;

                    $buyProduct->productbuyprice += $ingredientProduct->productbuyprice * $multiplier;
                    $buyProduct->lastbuyprice += $ingredientProduct->lastbuyprice * $multiplier;
                    $buyProduct->lastbuyprice_withdiscount += $ingredientProduct->lastbuyprice_withdiscount * $multiplier;
                    $buyProduct->meanbuyprice += $ingredientProduct->meanbuyprice * $multiplier;
                    $buyProduct->meanbuyprice_withdiscount += $ingredientProduct->meanbuyprice_withdiscount * $multiplier;
                    $buyProduct->overallaverageprice += $ingredientProduct->overallaverageprice * $multiplier;
                    $buyProduct->lastbuyprice_withtax += $ingredientProduct->lastbuyprice_withtax * $multiplier;
                    $buyProduct->meanbuyprice_withtax += $ingredientProduct->meanbuyprice_withtax * $multiplier;
                    $buyProduct->lastbuyprice_withdiscountandtax += $ingredientProduct->lastbuyprice_withdiscountandtax * $multiplier;
                }
            }

            // Round all prices to specified decimal places
            $buyProduct->productbuyprice = round($buyProduct->productbuyprice, $noOfDecimalPlaces);
            $buyProduct->lastbuyprice = round($buyProduct->lastbuyprice, $noOfDecimalPlaces);
            $buyProduct->lastbuyprice_withdiscount = round($buyProduct->lastbuyprice_withdiscount, $noOfDecimalPlaces);
            $buyProduct->meanbuyprice = round($buyProduct->meanbuyprice, $noOfDecimalPlaces);
            $buyProduct->meanbuyprice_withdiscount = round($buyProduct->meanbuyprice_withdiscount, $noOfDecimalPlaces);
            $buyProduct->overallaverageprice = round($buyProduct->overallaverageprice, $noOfDecimalPlaces);
            $buyProduct->lastbuyprice_withtax = round($buyProduct->lastbuyprice_withtax, $noOfDecimalPlaces);
            $buyProduct->meanbuyprice_withtax = round($buyProduct->meanbuyprice_withtax, $noOfDecimalPlaces);
            $buyProduct->lastbuyprice_withdiscountandtax = round($buyProduct->lastbuyprice_withdiscountandtax, $noOfDecimalPlaces);

            // Update product using RedBeanPHP
            R::store($buyProduct);
        }

        return $buyProduct;
    }

    public static function ifCollectiveIncreaseItsIngriedientsInStore($buyProduct, $productChangeAmount, $sellbillId, $sellbillstoreId, $tableName)
    {
        // Get product ingredients using RedBeanPHP
        $ingredients = R::find('productingredients', 'productid = ?', [$buyProduct->productid]);

        foreach ($ingredients as $ingredient) {
            $productId = $ingredient->ingridientid;
            $ingredientChangeAmount = $productChangeAmount * $ingredient->quantity;
            $commentCollective = '';
            $controllerLink = '';

            switch ($tableName) {
                case "sellandruternbilldetail":
                    $commentCollective = "فاتورة مبيعات ومردودات بها منتج تجميعى - " . $buyProduct->productname;
                    $controllerLink = "sellbillandruternController.php";
                    break;
                case "returnsellbilldetail":
                    $commentCollective = "فاتورة مردودات مبيعات بها منتج تجميعى - " . $buyProduct->productname;
                    $controllerLink = "returnsellbillController.php";
                    break;
                case "sellbilldetail":
                    $commentCollective = "فاتورة مبيعات بها منتج تجميعى - " . $buyProduct->productname;
                    $controllerLink = "sellbillController.php";
                    break;
            }

            // Get store detail data using RedBeanPHP
            $existingStoreDetail = R::findOne(
                'storedetail',
                'productid = ? AND storeid = ? AND unittype = ?',
                [$productId, $sellbillstoreId, 0]
            );

            if ($existingStoreDetail) {
                // Update existing store detail
                $productquantityBefore = $existingStoreDetail->productquantity;
                $productquantityAfter = $productquantityBefore + $ingredientChangeAmount;

                $existingStoreDetail->userid = $_SESSION['userid'];
                $existingStoreDetail->storedetaildate = date("Y-m-d H:i:s");
                $existingStoreDetail->productquantity = $productquantityAfter;
                R::store($existingStoreDetail);

                // Insert in storereport
                insertStorereportupdate($productId, $sellbillstoreId, $ingredientChangeAmount, $productquantityBefore, $productquantityAfter, 0, $sellbillId, $commentCollective, $controllerLink, date("Y-m-d H:i:s"));
            } else {
                // Create new store detail
                $newStoreDetail = R::dispense('storedetail');
                $newStoreDetail->userid = $_SESSION['userid'];
                $newStoreDetail->storedetaildate = date("Y-m-d H:i:s");
                $newStoreDetail->productquantity = $ingredientChangeAmount;
                $newStoreDetail->storeid = $sellbillstoreId;
                $newStoreDetail->productid = $productId;
                $newStoreDetail->unittype = 0;
                R::store($newStoreDetail);

                // Insert in storereport
                insertStorereportupdate($productId, $sellbillstoreId, $ingredientChangeAmount, 0, $ingredientChangeAmount, 0, $sellbillId, $commentCollective, $controllerLink, date("Y-m-d H:i:s"));
            }
        }

        return count($ingredients); // as flag if collective product
    }

    public static function ifCollectiveDecreaseItsIngriedientsInStore($buyProduct, $productChangeAmount, $sellbillId, $sellbillstoreId, $tableName)
    {
        // Get product ingredients using RedBeanPHP
        $ingredients = R::find('productingredients', 'productid = ?', [$buyProduct->productid]);

        foreach ($ingredients as $ingredient) {
            $productId = $ingredient->ingridientid;
            $ingredientChangeAmount = $productChangeAmount * $ingredient->quantity;
            $commentCollective = '';
            $controllerLink = '';

            switch ($tableName) {
                case "sellandruternbilldetail":
                    $commentCollective = "فاتورة مبيعات ومردودات بها منتج تجميعى - " . $buyProduct->productname;
                    $controllerLink = "sellbillandruternController.php";
                    break;
                case "returnsellbilldetail":
                    $commentCollective = "فاتورة مردودات مبيعات بها منتج تجميعى - " . $buyProduct->productname;
                    $controllerLink = "returnsellbillController.php";
                    break;
                case "sellbilldetail":
                    $commentCollective = "فاتورة مبيعات بها منتج تجميعى - " . $buyProduct->productname;
                    $controllerLink = "sellbillController.php";
                    break;
            }

            // Get store detail data using RedBeanPHP
            $existingStoreDetail = R::findOne(
                'storedetail',
                'productid = ? AND storeid = ? AND unittype = ?',
                [$productId, $sellbillstoreId, 0]
            );

            if ($existingStoreDetail) {
                // Update existing store detail (decrease quantity)
                $productquantityBefore = $existingStoreDetail->productquantity;
                $productquantityAfter = $productquantityBefore - $ingredientChangeAmount;

                $existingStoreDetail->userid = $_SESSION['userid'];
                $existingStoreDetail->storedetaildate = date("Y-m-d H:i:s");
                $existingStoreDetail->productquantity = $productquantityAfter;
                R::store($existingStoreDetail);

                // Insert in storereport
                insertStorereportupdate($productId, $sellbillstoreId, $ingredientChangeAmount, $productquantityBefore, $productquantityAfter, 1, $sellbillId, $commentCollective, $controllerLink, date("Y-m-d H:i:s"));
            } else {
                // Create new store detail with negative quantity
                $newStoreDetail = R::dispense('storedetail');
                $newStoreDetail->userid = $_SESSION['userid'];
                $newStoreDetail->storedetaildate = date("Y-m-d H:i:s");
                $newStoreDetail->productquantity = -$ingredientChangeAmount; // Negative quantity for decrease
                $newStoreDetail->storeid = $sellbillstoreId;
                $newStoreDetail->productid = $productId;
                $newStoreDetail->unittype = 0;
                R::store($newStoreDetail);

                // Insert in storereport
                insertStorereportupdate($productId, $sellbillstoreId, $ingredientChangeAmount, 0, -$ingredientChangeAmount, 1, $sellbillId, $commentCollective, $controllerLink, date("Y-m-d H:i:s"));
            }
        }

        return count($ingredients); // as flag if collective product
    }

    // Get store detail data using RedBeanPHP
    public static function getStoredetailData($storeid, $productid, $sizeColorStoreDetailId)
    {
        $isService = 0;
        if (isset($sizeColorStoreDetailId) && $sizeColorStoreDetailId > 0) {
            // Size/color product
            $storedetailData = R::load('sizecolorstoredetail', $sizeColorStoreDetailId);
            $storedetailId = $storedetailData->id;
            $productquantityBefore = $storedetailData->quantity;
        } else {
            // Regular product - get store detail with product and store info
            $storedetailData = R::getRow('
                SELECT sd.*, p.productname, p.productsellunitprice, p.productbuyprice, s.storename, p.isservice
                FROM storedetail sd 
                JOIN product p ON sd.productid = p.productid 
                JOIN store s ON sd.storeid = s.storeid
                WHERE p.conditions = 0 AND s.conditions = 0 
                AND sd.productid = ? AND sd.storeid = ?
            ', [$productid, $storeid]);

            if ($storedetailData) {
                $storedetailId = $storedetailData['storedetailid'];
                $productquantityBefore = $storedetailData['productquantity'];
                $isService = $storedetailData['isservice'];
                // Convert array to object for consistency
                $storedetailData = (object) $storedetailData;
            } else {
                $storedetailId = 0;
                $productquantityBefore = 0;
                $storedetailData = null;
            }
        }

        return array(
            $storedetailData,
            $storedetailId,
            $productquantityBefore,
            $isService
        );
    }

    // Get client data with debt management using RedBeanPHP
    public static function getClientDataFromClientInUseSP($clientid)
    {
        if ($clientid == 1) {
            $client_data = R::load('client', $clientid);
        } elseif ($clientid > 1) {
            $noOfTries = 0;
            // Mark client as in use and get data
            R::exec('UPDATE client SET inuse = 1 WHERE clientid = ?', [$clientid]);
            $client_data = R::load('client', $clientid);

            // Check if client is in use by another process
            while ($client_data->inuse == 1 && $noOfTries < 15) {
                sleep(1);
                $noOfTries++;
                if ($noOfTries > 15) {
                    // Force free client after 15 seconds
                    R::exec('UPDATE client SET inuse = 0 WHERE clientid = ?', [$clientid]);
                }
                $client_data = R::load('client', $clientid);
            }
        }
        return $client_data;
    }

    // Insert store report update using RedBeanPHP
    public static function insertStorereportupdate($productid, $storeid, $productChangeAmount, $productbefore, $productafter, $storereporttype, $storereportmodelid, $processname, $tablename, $mydate, $sizeId = 0, $colorId = 0)
    {
        $storereport = R::dispense('storereport');
        $storereport->userid = $_SESSION['userid'];
        $storereport->processname = $processname;
        $storereport->productafter = $productafter;
        $storereport->productbefore = $productbefore;
        $storereport->productquantity = $productChangeAmount;
        $storereport->productid = $productid;
        $storereport->storeid = $storeid;
        $storereport->storereportdate = $mydate;
        $storereport->storereportmodelid = $storereportmodelid;
        $storereport->storereporttype = $storereporttype;
        $storereport->tablename = $tablename;
        $storereport->sizeid = $sizeId;
        $storereport->colorid = $colorId;

        R::store($storereport);
    }

    // Calculate quick profit using RedBeanPHP
    /*public static function quickProfitRow($myproduct, $finalQuantity, $isreturn, $isadd, $prototal, $billDiscountVal, $billTotalBeforeDiscount, $storeId)
    {
        global $sellCostsArray;
        
        $prototalWithDiscount = $prototal - ($billDiscountVal * ($prototal / $billTotalBeforeDiscount));
        $obj = new stdClass();
        $obj->productid = $myproduct->productid;
        $obj->thedate = date('Y-m-d');
        
        if ($isreturn == 0) {
            // Sell operation
            $obj->sellval = $prototalWithDiscount;
            $obj->sellcostbuyprice = $myproduct->productbuyprice * $finalQuantity;
            $obj->sellcostlastbuyprice = $myproduct->lastbuyprice * $finalQuantity;
            $obj->sellcostmeanbuyprice = $myproduct->meanbuyprice * $finalQuantity;
            $obj->sellcostlastbuypricewithdiscount = $myproduct->lastbuyprice_withdiscount * $finalQuantity;
            $obj->sellcostmeanbuypricewithdiscount = $myproduct->meanbuyprice_withdiscount * $finalQuantity;
            $obj->sellcostoverallaverageprice = $myproduct->overallaverageprice * $finalQuantity;
            $obj->sellcostlastbuypricewithtax = $myproduct->lastbuyprice_withtax * $finalQuantity;
            $obj->sellcostmeanbuypricewithtax = $myproduct->meanbuyprice_withtax * $finalQuantity;
            $obj->sellcostlastbuypricewithdiscountandtax = $myproduct->lastbuyprice_withdiscountandtax * $finalQuantity;
            $obj->returnsellval = 0;
            $obj->returnsellcostbuyprice = 0;
            $obj->returnsellcostlastbuyprice = 0;
            $obj->returnsellcostmeanbuyprice = 0;
            $obj->returnsellcostlastbuypricewithdiscount = 0;
            $obj->returnsellcostmeanbuypricewithdiscount = 0;
            $obj->returnsellcostoverallaverageprice = 0;
            $obj->returnsellcostlastbuypricewithtax = 0;
            $obj->returnsellcostlastbuypricewithdiscountandtax = 0;
            $obj->returnsellcostmeanbuypricewithtax = 0;
            $obj->netquantity = $finalQuantity;
            $obj->netquantityret = 0;
        } elseif ($isreturn == 1) {
            // Return operation
            $obj->sellval = 0;
            $obj->sellcostbuyprice = 0;
            $obj->sellcostlastbuyprice = 0;
            $obj->sellcostmeanbuyprice = 0;
            $obj->sellcostlastbuypricewithdiscount = 0;
            $obj->sellcostlastbuypricewithdiscountandtax = 0;
            $obj->sellcostmeanbuypricewithdiscount = 0;
            $obj->sellcostoverallaverageprice = 0;
            $obj->sellcostlastbuypricewithtax = 0;
            $obj->sellcostmeanbuypricewithtax = 0;
            $obj->returnsellval = $prototalWithDiscount;
            $obj->returnsellcostbuyprice = $myproduct->productbuyprice * $finalQuantity;
            $obj->returnsellcostlastbuyprice = $myproduct->lastbuyprice * $finalQuantity;
            $obj->returnsellcostmeanbuyprice = $myproduct->meanbuyprice * $finalQuantity;
            $obj->returnsellcostlastbuypricewithdiscount = $myproduct->lastbuyprice_withdiscount * $finalQuantity;
            $obj->returnsellcostmeanbuypricewithdiscount = $myproduct->meanbuyprice_withdiscount * $finalQuantity;
            $obj->returnsellcostoverallaverageprice = $myproduct->overallaverageprice * $finalQuantity;
            $obj->returnsellcostlastbuypricewithtax = $myproduct->lastbuyprice_withtax * $finalQuantity;
            $obj->returnsellcostmeanbuypricewithtax = $myproduct->meanbuyprice_withtax * $finalQuantity;
            $obj->returnsellcostlastbuypricewithdiscountandtax = $myproduct->lastbuyprice_withdiscountandtax * $finalQuantity;
            $obj->netquantity = 0;
            $obj->netquantityret = $finalQuantity;
        }

        $obj->storeid = $storeId;
        
        // Insert or update profit records using RedBeanPHP
        if ($isadd == 1) {
            self::insertOrUpdateProfitRecord('quickprofitproduct', $obj, true);
            self::insertOrUpdateProfitRecord('quickprofitstore', $obj, true);
        } else {
            self::insertOrUpdateProfitRecord('quickprofitproduct', $obj, false);
            self::insertOrUpdateProfitRecord('quickprofitstore', $obj, false);
        }
        
        // Update global sell costs array if it exists
        if (isset($sellCostsArray)) {
            $sellCostsArray["sellcostbuyprice"] += $obj->sellcostbuyprice;
            $sellCostsArray["sellcostlastbuyprice"] += $obj->sellcostlastbuyprice;
            $sellCostsArray["sellcostmeanbuyprice"] += $obj->sellcostmeanbuyprice;
            $sellCostsArray["sellcostlastbuypricewithdiscount"] += $obj->sellcostlastbuypricewithdiscount;
            $sellCostsArray["sellcostmeanbuypricewithdiscount"] += $obj->sellcostmeanbuypricewithdiscount;
            $sellCostsArray["sellcostoverallaverageprice"] += $obj->sellcostoverallaverageprice;
            $sellCostsArray["sellcostlastbuypricewithtax"] += $obj->sellcostlastbuypricewithtax;
            $sellCostsArray["sellcostmeanbuypricewithtax"] += $obj->sellcostmeanbuypricewithtax;
            $sellCostsArray["returnsellcostbuyprice"] += $obj->returnsellcostbuyprice;
            $sellCostsArray["returnsellcostlastbuyprice"] += $obj->returnsellcostlastbuyprice;
            $sellCostsArray["returnsellcostmeanbuyprice"] += $obj->returnsellcostmeanbuyprice;
            $sellCostsArray["returnsellcostlastbuypricewithdiscount"] += $obj->returnsellcostlastbuypricewithdiscount;
            $sellCostsArray["returnsellcostmeanbuypricewithdiscount"] += $obj->returnsellcostmeanbuypricewithdiscount;
            $sellCostsArray["returnsellcostoverallaverageprice"] += $obj->returnsellcostoverallaverageprice;
            $sellCostsArray["returnsellcostlastbuypricewithtax"] += $obj->returnsellcostlastbuypricewithtax;
            $sellCostsArray["returnsellcostmeanbuypricewithtax"] += $obj->returnsellcostmeanbuypricewithtax;
        }
    }

    // Helper function to insert or update profit records
    public static function insertOrUpdateProfitRecord($tableName, $obj, $isAdd)
    {
        $conditions = [];
        $params = [];
        
        if ($tableName == 'quickprofitproduct') {
            $conditions[] = 'productid = ?';
            $params[] = $obj->productid;
        } elseif ($tableName == 'quickprofitstore') {
            $conditions[] = 'storeid = ?';
            $params[] = $obj->storeid;
        }
        
        $conditions[] = 'thedate = ?';
        $params[] = $obj->thedate;
        
        $existingRecord = R::findOne($tableName, implode(' AND ', $conditions), $params);
        
        if (!$existingRecord) {
            $existingRecord = R::dispense($tableName);
            if ($tableName == 'quickprofitproduct') {
                $existingRecord->productid = $obj->productid;
            } elseif ($tableName == 'quickprofitstore') {
                $existingRecord->storeid = $obj->storeid;
            }
            $existingRecord->thedate = $obj->thedate;
        }
        
        $multiplier = $isAdd ? 1 : -1;
        
        // Update all profit fields
        $existingRecord->sellval = ($existingRecord->sellval ?? 0) + ($obj->sellval * $multiplier);
        $existingRecord->sellcostbuyprice = ($existingRecord->sellcostbuyprice ?? 0) + ($obj->sellcostbuyprice * $multiplier);
        $existingRecord->sellcostlastbuyprice = ($existingRecord->sellcostlastbuyprice ?? 0) + ($obj->sellcostlastbuyprice * $multiplier);
        $existingRecord->sellcostmeanbuyprice = ($existingRecord->sellcostmeanbuyprice ?? 0) + ($obj->sellcostmeanbuyprice * $multiplier);
        $existingRecord->sellcostlastbuypricewithdiscount = ($existingRecord->sellcostlastbuypricewithdiscount ?? 0) + ($obj->sellcostlastbuypricewithdiscount * $multiplier);
        $existingRecord->sellcostmeanbuypricewithdiscount = ($existingRecord->sellcostmeanbuypricewithdiscount ?? 0) + ($obj->sellcostmeanbuypricewithdiscount * $multiplier);
        $existingRecord->sellcostoverallaverageprice = ($existingRecord->sellcostoverallaverageprice ?? 0) + ($obj->sellcostoverallaverageprice * $multiplier);
        $existingRecord->sellcostlastbuypricewithtax = ($existingRecord->sellcostlastbuypricewithtax ?? 0) + ($obj->sellcostlastbuypricewithtax * $multiplier);
        $existingRecord->sellcostmeanbuypricewithtax = ($existingRecord->sellcostmeanbuypricewithtax ?? 0) + ($obj->sellcostmeanbuypricewithtax * $multiplier);
        $existingRecord->sellcostlastbuypricewithdiscountandtax = ($existingRecord->sellcostlastbuypricewithdiscountandtax ?? 0) + ($obj->sellcostlastbuypricewithdiscountandtax * $multiplier);
        $existingRecord->returnsellval = ($existingRecord->returnsellval ?? 0) + ($obj->returnsellval * $multiplier);
        $existingRecord->returnsellcostbuyprice = ($existingRecord->returnsellcostbuyprice ?? 0) + ($obj->returnsellcostbuyprice * $multiplier);
        $existingRecord->returnsellcostlastbuyprice = ($existingRecord->returnsellcostlastbuyprice ?? 0) + ($obj->returnsellcostlastbuyprice * $multiplier);
        $existingRecord->returnsellcostmeanbuyprice = ($existingRecord->returnsellcostmeanbuyprice ?? 0) + ($obj->returnsellcostmeanbuyprice * $multiplier);
        $existingRecord->returnsellcostlastbuypricewithdiscount = ($existingRecord->returnsellcostlastbuypricewithdiscount ?? 0) + ($obj->returnsellcostlastbuypricewithdiscount * $multiplier);
        $existingRecord->returnsellcostmeanbuypricewithdiscount = ($existingRecord->returnsellcostmeanbuypricewithdiscount ?? 0) + ($obj->returnsellcostmeanbuypricewithdiscount * $multiplier);
        $existingRecord->returnsellcostoverallaverageprice = ($existingRecord->returnsellcostoverallaverageprice ?? 0) + ($obj->returnsellcostoverallaverageprice * $multiplier);
        $existingRecord->returnsellcostlastbuypricewithtax = ($existingRecord->returnsellcostlastbuypricewithtax ?? 0) + ($obj->returnsellcostlastbuypricewithtax * $multiplier);
        $existingRecord->returnsellcostmeanbuypricewithtax = ($existingRecord->returnsellcostmeanbuypricewithtax ?? 0) + ($obj->returnsellcostmeanbuypricewithtax * $multiplier);
        $existingRecord->returnsellcostlastbuypricewithdiscountandtax = ($existingRecord->returnsellcostlastbuypricewithdiscountandtax ?? 0) + ($obj->returnsellcostlastbuypricewithdiscountandtax * $multiplier);
        $existingRecord->netquantity = ($existingRecord->netquantity ?? 0) + ($obj->netquantity * $multiplier);
        $existingRecord->netquantityret = ($existingRecord->netquantityret ?? 0) + ($obj->netquantityret * $multiplier);
        
        R::store($existingRecord);
    }*/

    // Online temp store detail function using RedBeanPHP
    public static function onlineTempStoreDetailFunc($storeid, $productid, $sizeid, $colorid, $quantity, $edited)
    {
        // Get online store settings
        $onlineStoreSetting = self::getOrHandleOnlineStoreSetting();
        if (!$onlineStoreSetting) {
            return;
        }

        $onlineStores = explode(',', $onlineStoreSetting->availablestores);

        if (in_array($storeid, $onlineStores)) {
            // Check if record exists
            $existingRecord = R::findOne(
                'onlinetempstoredetail',
                'storeid = ? AND productid = ? AND sizeid = ? AND colorid = ?',
                [$storeid, $productid, $sizeid, $colorid]
            );

            if ($existingRecord) {
                $existingRecord->quantity = $quantity;
                $existingRecord->edited = $edited;
                $existingRecord->sysdate = date("Y-m-d H:i:s");
                $existingRecord->userid = $_SESSION['userid'];
            } else {
                $existingRecord = R::dispense('onlinetempstoredetail');
                $existingRecord->storeid = $storeid;
                $existingRecord->productid = $productid;
                $existingRecord->sizeid = $sizeid;
                $existingRecord->colorid = $colorid;
                $existingRecord->quantity = $quantity;
                $existingRecord->edited = $edited; // 0 not edited, 1 edited, 2 del
                $existingRecord->sysdate = date("Y-m-d H:i:s");
                $existingRecord->userid = $_SESSION['userid'];
            }

            R::store($existingRecord);
        }
    }

    // Get or handle online store setting
    public static function getOrHandleOnlineStoreSetting()
    {
        $onlineStoreSetting = R::findOne('onlinestoresetting', 'id = 1');

        if (!$onlineStoreSetting) {
            $onlineStoreSetting = R::dispense('onlinestoresetting');
            $onlineStoreSetting->availablestores = '1';
            $onlineStoreSetting->isactive = 1;
            R::store($onlineStoreSetting);
        }

        return $onlineStoreSetting;
    }

    public static function markStoreDetailNotInUse($storeDetailIds, $sizeColorStoreDetailIds)
    {
        //mark storedetail as not InUSe
        if ($storeDetailIds) {
            foreach ($storeDetailIds as $id) {
                R::exec('update storedetail set inUse = 0 where storedetailid = ' . $id);
            }
        }
        if ($sizeColorStoreDetailIds) {
            foreach ($storeDetailIds as $id) {
                R::exec('update sizecolorstoredetail set inUse = 0 where id = ' . $id);
            }
        }
    }
}
