<?php
/**
 * Helper functions for product barcode/parcode operations
 */

class ProductParcodeHelper {
    
    /**
     * Get product information by barcode/parcode
     * 
     * @param string $parcode The barcode/parcode to search for
     * @return array|int Array with product info or -1 if not found
     */
    public static function getProductByParcode($parcode) {
        global $Programsettingdata;
        
        // Initialize default return value
        $result = -1;
        
        // Check for empty parcode
        if (empty($parcode)) {
            return $result;
        }
        
        // Try to get from productserial table first
        $result = self::getFromProductSerialTable($parcode);
        
        // If not found in productserial, try other tables
        if ($result === -1 || $result === false) {
            // Check if parcode is an ID (starts with 'i' and usedParcode is enabled)
            if ($parcode[0] == 'i' /*&& $Programsettingdata->usedParcode == 1*/) {
                $productId = (int) substr($parcode, 1);
                $result = self::getFromProductTableById($productId);
            } else {
                // Try product table by parcode
                $result = self::getFromProductTableByParcode($parcode);

                // If still not found, try size_color_store_detail table
                if ($result === -1) {
                    $result = self::getFromSizeColorStoreDetailTable($parcode);
                }
                
                // If still not found, try product_unit table
                if ($result === -1) {
                    $result = self::getFromProductUnitTable($parcode);                    
                }

                // If not found in product_unit, try Excel barcode format
                if ($result === -1) {
                    $result = self::getProductFromProductTableUsingParcodeExcel($parcode);
                }
            }
        }
        
        return $result;
    }
    
    /**
     * Search in productserial table
     */
    private static function getFromProductSerialTable($parcode) {
        $sql = "SELECT ps.*, p.*, s.name as sizeName, c.name as colorName, 
                       cat.productcatName, ps.don as quantity
                FROM productserial ps
                JOIN product p ON ps.productid = p.productId
                left join sizecolor s ON ps.sizeid = s.id
                left join sizecolor c ON ps.colorid = c.id
                LEFT JOIN productcat cat ON p.productCatId = cat.productcatId
                WHERE ps.serialnumber = '" . $parcode . "' LIMIT 1";
        
        $productData = R::getRow($sql);
        
        if (!empty($productData)) {
            return self::formatProductData($productData, 'serialnumber', $productData['productserailid']);
        }
        
        return -1;
    }
    
    /**
     * Search in product table by ID
     */
    private static function getFromProductTableById($productId) {
        $sql = "SELECT p.*, cat.productcatName
                FROM product p
                LEFT JOIN productcat cat ON p.productCatId = cat.productcatId
                WHERE p.productId = :productId
                LIMIT 1";
        
        $productData = R::getRow($sql, [':productId' => $productId]);
        
        if (!empty($productData)) {
            return self::formatProductData($productData, 'productid');
        }
        
        return -1;
    }
    
    /**
     * Search in product table by parcode
     */
    private static function getFromProductTableByParcode($parcode) {
        $sql = "SELECT p.*, cat.productcatName
                FROM product p
                LEFT JOIN productcat cat ON p.productCatId = cat.productcatId
                WHERE p.parcode = :parcode
                LIMIT 1";
        $productData = R::getRow($sql, [':parcode' => $parcode]);
        
        if (!empty($productData)) {
            return self::formatProductData($productData, 'parcode');
        }
        
        return -1;
    }
    
    /**
     * Search in size_color_store_detail table
     */
    private static function getFromSizeColorStoreDetailTable($parcode) {
        $sql = "SELECT scd.*, p.*, s.name as sizeName, c.name as colorName, 
                       cat.productcatName
                FROM sizecolorstoredetail scd
                JOIN product p ON scd.productid = p.productId
                left join sizecolor s ON scd.sizeid = s.id
                left join sizecolor c ON scd.colorid = c.id
                LEFT JOIN productcat cat ON p.productCatId = cat.productcatId
                WHERE scd.parcode = :parcode
                LIMIT 1";
        
        $productData = R::getRow($sql, [':parcode' => $parcode]);
        
        if (!empty($productData)) {
            return self::formatProductData($productData, 'sizecolordetail');
        }
        
        return -1;
    }
    
    /**
     * Search in product_unit table
     */
    private static function getFromProductUnitTable($parcode) {
        // $sql = "SELECT pu.*, p.*, u.unitName, 
        //                cat.productcatName
        //         FROM product_unit pu
        //         JOIN product p ON pu.productId = p.productId
        //         LEFT JOIN unit u ON pu.unitId = u.unitId
        //         LEFT JOIN productcat cat ON p.productCatId = cat.productcatId
        //         WHERE pu.parcode = :parcode
        //         LIMIT 1";

        $sql = 'SELECT product.*,productcat.productCatName,productunit.productunitid,productunit.unitid,productunit.proUnitSellAllPrice,productunit.proUnitSellHalfPrice,productunit.proUnitSellUnitPrice,productunit.proUnitBuyPrice
                FROM product
                join productcat on productcat.productCatId = product.productCatId
                join productunit on (product.productId = productunit.productid and productunit.proUnitParcode = "' . $parcode . '" and productunit.conditions = 0)
                where product.conditions = 0';
        
        $productData = R::getRow($sql);
        
        if (!empty($productData)) {
            return self::formatProductData($productData, 'productunit');
        }
        
        return -1;
    }
    
    /**
     * Get product data from product table using barcode from Excel
     * 
     * @param string $barcode The barcode to search for
     * @return array|int Array with product info or -1 if not found
     */
    private static function getProductFromProductTableUsingParcodeExcel($barcode) {
        // Search in product table by barcode from Excel
        $sql = "SELECT p.*, cat.productcatName
                FROM product p
                LEFT JOIN productcat cat ON p.productCatId = cat.productcatId
                WHERE p.conditions = 0
                and p.proExcelParcode = '" . $barcode . "'
                LIMIT 1";


        $productData = R::getRow($sql);
        
        if (!empty($productData)) {
            return self::formatProductData($productData, 'excel_barcode');
        }
        
        return -1;
    }
    
    /**
     * Format product data into a consistent array structure
     */
    private static function formatProductData($data, $sourceType, $productSerialId = 0) {
        $result = [];
        
        // Set product ID
        if ($data['sizeid'] > 0 && $data['colorid'] > 0) {
            $result['id'] = "hasSizeColor{$data['productId']}-{$data['sizeid']}-{$data['colorid']}";
        } else {
            $result['id'] = $data['productId'];
        }
        
        // Build product name with category path
        $categoryPath = self::getCategoryPath($data['productCatId']);
        $result['text'] = $data['productName'] . '/' . $categoryPath;
        
        // Add size and color if available
        if (!empty($data['sizeName'])) {
            $result['text'] .= "/" . $data['sizeName'];
        }
        if (!empty($data['colorName'])) {
            $result['text'] .= "/" . $data['colorName'];
        }
        
        // Set source type and other common fields
        $result['parcodeType'] = $sourceType;
        $result['productserailid'] = $productSerialId;
        $result['productQty'] = isset($data['quantity']) ? $data['quantity'] : 'undefined';
        $result['selldiscountpercent'] = isset($data['selldiscountpercent']) ? $data['selldiscountpercent'] : 0;
        $result['sellpercenttype'] = isset($data['sellpercenttype']) ? $data['sellpercenttype'] : 0;
        
        // Add additional fields based on source type
        switch ($sourceType) {
            case 'serialnumber':
                $result['chassisNo'] = isset($data['chassisNo']) ? $data['chassisNo'] : '';
                $result['motorNo'] = isset($data['motorNo']) ? $data['motorNo'] : '';
                $result['theColor'] = isset($data['theColor']) ? $data['theColor'] : '';
                break;
        }
        
        return $result;
    }
    
    /**
     * Get category path as a string
     */
    private static function getCategoryPath($categoryId, $path = '') {
        if (empty($categoryId)) {
            return $path;
        }
        $category = R::getRow(
            "SELECT productcatName, productCatParent FROM productcat WHERE productcatId = :id",
            [':id' => $categoryId]
        );

        if (empty($category)) {
            return $path;
        }
        $newPath = $category['productcatName'] . ($path ? "/$path" : '');
        if (!empty($category['productCatParent'])) {
            return self::getCategoryPath($category['productCatParent'], $newPath);
        }
        
        return $newPath;
    }
}

// Usage example:
// Include the helper
//require_once 'helpers/ProductParcodeHelper.php';
// $productInfo = ProductParcodeHelper::getProductByParcode('123456789');
// if ($productInfo !== -1) {
//     // Product found
//     $productId = $productInfo['id'];
//     $productName = $productInfo['text'];
//     // ... use other fields as needed
// }else {
//    // Product not found
//}
