<?php
ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');

// Allowed HTTP method
$allow_method = "GET";
require_once __DIR__ . "/headers.php";
require __DIR__ . '/classes/Database.php';

// Create a new database connection
$db_connection = new Database();
$conn = $db_connection->dbConnection();

// Get SKU from the GET request
//$sku = isset($_GET['sku']) ? $_GET['sku'] : 0;
$sku = isset($_GET['sku']) ? trim($_GET['sku']) : '0';
$skuAction = isset($_GET['action']) ? trim($_GET['action']) : '0';
$skuAction = ($skuAction==='pick')? null : $skuAction;
$skuOrder = isset($_GET['ordersku']) ? trim($_GET['ordersku']) : null;

// Return the filtered results as a JSON response
header('Content-Type: application/json');

// Check if SKU is provided
if ($sku === 0) {
    echo json_encode(["error" => "SKU parameter is missing"]);
    exit;
}
$start = substr($sku, 0, 2);

// FIRST - Try to find product in website database with full details
// $productQuerybk = "
//     SELECT 
//         e.entity_id AS product_id,
//         e.sku,
//         v.value AS sku_name,
//         e.type_id AS product_type,
//         price.value AS regular_price,
//         special_price.value AS special_price,
//         erp_current_price.value AS erp_current_price,
//         delivery_type.value AS delivery_type,
//         CASE 
//             WHEN special_price.value IS NOT NULL 
//                  AND (special_from.value IS NULL OR special_from.value <= NOW())
//                  AND (special_to.value IS NULL OR special_to.value >= NOW()) 
//             THEN special_price.value
//             WHEN erp_current_price.value IS NOT NULL THEN erp_current_price.value
//             ELSE price.value
//         END AS current_promotion_price,
//         GROUP_CONCAT(DISTINCT mg.value SEPARATOR ',') AS images
//     FROM ahqa_catalog_product_entity e
    
//     LEFT JOIN ahqa_catalog_product_entity_varchar v 
//         ON e.entity_id = v.entity_id 
//         AND v.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 4)
//     LEFT JOIN ahqa_catalog_product_entity_decimal price 
//         ON e.entity_id = price.entity_id 
//         AND price.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'price' AND entity_type_id = 4)
//     LEFT JOIN ahqa_catalog_product_entity_decimal special_price 
//         ON e.entity_id = special_price.entity_id 
//         AND special_price.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'special_price' AND entity_type_id = 4)
//     LEFT JOIN ahqa_catalog_product_entity_decimal erp_current_price 
//         ON e.entity_id = erp_current_price.entity_id 
//         AND erp_current_price.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'erp_current_price' AND entity_type_id = 4)
//     LEFT JOIN ahqa_catalog_product_entity_varchar delivery_type 
//         ON e.entity_id = delivery_type.entity_id 
//         AND delivery_type.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'delivery_type' AND entity_type_id = 4)
//     LEFT JOIN ahqa_catalog_product_entity_datetime special_from 
//         ON e.entity_id = special_from.entity_id 
//         AND special_from.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'special_from_date' AND entity_type_id = 4)
//     LEFT JOIN ahqa_catalog_product_entity_datetime special_to 
//         ON e.entity_id = special_to.entity_id 
//         AND special_to.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'special_to_date' AND entity_type_id = 4)
//     LEFT JOIN ahqa_catalog_product_entity_media_gallery_value_to_entity mgve
//         ON e.entity_id = mgve.entity_id
//     LEFT JOIN ahqa_catalog_product_entity_media_gallery mg
//         ON mgve.value_id = mg.value_id
//     WHERE e.sku = :sku 
//     GROUP BY e.entity_id
//     LIMIT 1
// ";
$productQuery="SELECT 
    e.entity_id AS product_id,
    e.sku,
    v.value AS sku_name,
    e.type_id AS product_type,
    price.value AS regular_price,
    special_price.value AS special_price,
    erp_current_price.value AS erp_current_price,
    delivery_type.value AS delivery_type,
    IFNULL(ov.value, '0') AS is_produce,
    CASE 
        WHEN special_price.value IS NOT NULL 
             AND (special_from.value IS NULL OR special_from.value <= NOW())
             AND (special_to.value IS NULL OR special_to.value >= NOW()) 
        THEN special_price.value
        WHEN erp_current_price.value IS NOT NULL THEN erp_current_price.value
        ELSE price.value
    END AS current_promotion_price,
    GROUP_CONCAT(DISTINCT mg.value SEPARATOR ',') AS images,
    GROUP_CONCAT(DISTINCT cpet.value SEPARATOR ', ') AS barcodes
FROM ahqa_catalog_product_entity e
LEFT JOIN ahqa_catalog_product_entity_varchar v 
    ON e.entity_id = v.entity_id 
    AND v.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 4)
LEFT JOIN ahqa_catalog_product_entity_decimal price 
    ON e.entity_id = price.entity_id 
    AND price.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'price' AND entity_type_id = 4)
LEFT JOIN ahqa_catalog_product_entity_decimal special_price 
    ON e.entity_id = special_price.entity_id 
    AND special_price.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'special_price' AND entity_type_id = 4)
LEFT JOIN ahqa_catalog_product_entity_decimal erp_current_price 
    ON e.entity_id = erp_current_price.entity_id 
    AND erp_current_price.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'erp_current_price' AND entity_type_id = 4)
LEFT JOIN ahqa_catalog_product_entity_varchar delivery_type 
    ON e.entity_id = delivery_type.entity_id 
    AND delivery_type.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'delivery_type' AND entity_type_id = 4)
LEFT JOIN ahqa_catalog_product_entity_datetime special_from 
    ON e.entity_id = special_from.entity_id 
    AND special_from.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'special_from_date' AND entity_type_id = 4)
LEFT JOIN ahqa_catalog_product_entity_datetime special_to 
    ON e.entity_id = special_to.entity_id 
    AND special_to.attribute_id = (SELECT attribute_id FROM ahqa_eav_attribute WHERE attribute_code = 'special_to_date' AND entity_type_id = 4)
LEFT JOIN ahqa_catalog_product_entity_media_gallery_value_to_entity mgve
    ON e.entity_id = mgve.entity_id
LEFT JOIN ahqa_catalog_product_entity_media_gallery mg
    ON mgve.value_id = mg.value_id
LEFT JOIN ahqa_catalog_product_entity_varchar cpet
    ON e.entity_id = cpet.entity_id AND cpet.attribute_id = 169
LEFT JOIN
    ahqa_catalog_product_entity_int AS ov
    ON e.entity_id = ov.entity_id AND ov.attribute_id = 201 AND ov.store_id = 0    
WHERE e.type_id != 'configurable'
    AND (
        e.sku = :sku
        OR EXISTS (
            SELECT 1 
            FROM ahqa_catalog_product_entity_varchar b 
            WHERE b.entity_id = e.entity_id 
            AND b.attribute_id = 169 
            AND b.value LIKE :like_sku
        )
    )
GROUP BY e.entity_id
LIMIT 1";

$likeSku = '%' . $sku . '%';
$productStmt = $conn->prepare($productQuery);
$productStmt->bindParam(':sku', $sku, PDO::PARAM_STR);
$productStmt->bindParam(':like_sku', $likeSku, PDO::PARAM_STR);
$productStmt->execute();
//echo $productStmt->rowCount();
$productData = $productStmt->fetch(PDO::FETCH_ASSOC);
//$productData['match']='1';
$isProduce='0';
if ($productData) {
//if ($productData) {
    // Product found in website database - add priority 2
    $productData['priority'] = 2;
    
    // Get barcodes for this product
    $barcodeQuery = "
        SELECT GROUP_CONCAT(CONCAT(cpe.sku, ',', cpet.value) SEPARATOR ', ') AS barcodes
        FROM ahqa_catalog_product_entity AS cpe
        JOIN ahqa_catalog_product_entity_varchar AS cpet
            ON cpe.entity_id = cpet.entity_id
        WHERE cpet.attribute_id = 169
        AND cpe.sku = :sku
        GROUP BY cpe.entity_id
    ";
    //echo $barcodeQuery;
    $barcodeStmt = $conn->prepare($barcodeQuery);
    $barcodeStmt->bindParam(':sku', $sku, PDO::PARAM_STR);
    $barcodeStmt->execute();
    
    $barcodeData = $barcodeStmt->fetch(PDO::FETCH_ASSOC);
    //$productData['is_produce'] = $isProduce ?? 0;
    $productData['match']='0';
    $mergeBarcode = [];

    // First check if $barcodeData exists and has 'barcodes' key
    if (isset($barcodeData['barcodes']) && $barcodeData['barcodes']) {
        $mergeBarcode = explode(',', $barcodeData['barcodes']);
    }
    // Alternative shorter version using null coalescing operator (PHP 7+)
    $mergeBarcode = !empty($barcodeData['barcodes']) ? explode(',', $barcodeData['barcodes']) : [];
    //$mergeBarcode=($barcodeData['barcodes'])? explode(',',$barcodeData['barcodes']):[];
    if(!$skuAction && ((isset($barcodeData['barcodes']) && is_array($mergeBarcode) && !in_array($skuOrder,$mergeBarcode)) || $skuOrder!=$productData['sku'])){
         $productData['match']='0';
    }
    $productData['barcodes'] = $barcodeData['barcodes'] ?? null;
    
    echo json_encode($productData);
    exit;
}else if ($start >= '91' && $start <= '96'){ //check produce items last amount need to extract from produce-== 18-05-2025
    // Replace last 6 digits with zeros
    $sku = substr($sku, 0, -6) . '000000';
    $productStmt = $conn->prepare($productQuery);
    $productStmt->bindParam(':sku', $sku, PDO::PARAM_STR);
    $productStmt->bindParam(':like_sku', $likeSku, PDO::PARAM_STR);
    $productStmt->execute();
   
    $productData = $productStmt->fetch(PDO::FETCH_ASSOC);
    if ($productData) {
         $isProduce='1';
        // Product found in website database - add priority 2
        $productData['priority'] = 2;
        
        // Get barcodes for this product
        $barcodeQuery = "
            SELECT GROUP_CONCAT(CONCAT(cpe.sku, ',', cpet.value) SEPARATOR ', ') AS barcodes
            FROM ahqa_catalog_product_entity AS cpe
            JOIN ahqa_catalog_product_entity_varchar AS cpet
                ON cpe.entity_id = cpet.entity_id
            WHERE cpet.attribute_id = 169
            AND cpe.sku = :sku
            GROUP BY cpe.entity_id
        ";
        
        $barcodeStmt = $conn->prepare($barcodeQuery);
        $barcodeStmt->bindParam(':sku', $sku, PDO::PARAM_STR);
        $barcodeStmt->execute();
        
        $barcodeData = $barcodeStmt->fetch(PDO::FETCH_ASSOC);
       // $productData['is_produce'] = $isProduce ?? 0;
       $productData['match']='0';
       $mergeBarcode = [];

        // First check if $barcodeData exists and has 'barcodes' key
        if (isset($barcodeData['barcodes']) && $barcodeData['barcodes']) {
            $mergeBarcode = explode(',', $barcodeData['barcodes']);
        }
        // Alternative shorter version using null coalescing operator (PHP 7+)
        $mergeBarcode = !empty($barcodeData['barcodes']) ? explode(',', $barcodeData['barcodes']) : [];
        //$mergeBarcode=($barcodeData['barcodes'])? explode(',',$barcodeData['barcodes']):[];
         //echo $productData['sku']; //$productData['is_produce']!='1' && 
        if(!$skuAction &&  ((isset($barcodeData['barcodes']) && is_array($mergeBarcode) && !in_array($skuOrder,$mergeBarcode)) || $skuOrder!=$productData['sku'])){
             $productData['match']='1';
        }
        $productData['barcodes'] = $barcodeData['barcodes'] ?? null;
        
        echo json_encode($productData);
        exit;
    }

}else if ($start >= '91' && $start <= '96'){ //check digit items last amount need to extract from produce= 18-05-2025
    // Replace last 6 digits with zeros
    $sku = substr($sku, 0, -1) . '0';
    $productStmt = $conn->prepare($productQuery);
    $productStmt->bindParam(':sku', $sku, PDO::PARAM_STR);
    $productStmt->bindParam(':like_sku', $likeSku, PDO::PARAM_STR);
    $productStmt->execute();
    
    $productData = $productStmt->fetch(PDO::FETCH_ASSOC);
    if ($productData) {
         $isProduce='1';
        // Product found in website database - add priority 2
        $productData['priority'] = 2;
        
        // Get barcodes for this product
        $barcodeQuery = "
            SELECT GROUP_CONCAT(CONCAT(cpe.sku, ',', cpet.value) SEPARATOR ', ') AS barcodes
            FROM ahqa_catalog_product_entity AS cpe
            JOIN ahqa_catalog_product_entity_varchar AS cpet
                ON cpe.entity_id = cpet.entity_id
            WHERE cpet.attribute_id = 169
            AND cpe.sku = :sku
            GROUP BY cpe.entity_id
        ";
        
        $barcodeStmt = $conn->prepare($barcodeQuery);
        $barcodeStmt->bindParam(':sku', $sku, PDO::PARAM_STR);
        $barcodeStmt->execute();
        
        $barcodeData = $barcodeStmt->fetch(PDO::FETCH_ASSOC);
       // $productData['is_produce'] = $isProduce ?? '0';
        $productData['match']='0';
        $mergeBarcode = [];

        // First check if $barcodeData exists and has 'barcodes' key
        if (isset($barcodeData['barcodes']) && $barcodeData['barcodes']) {
            $mergeBarcode = explode(',', $barcodeData['barcodes']);
        }
        // Alternative shorter version using null coalescing operator (PHP 7+)
        $mergeBarcode = !empty($barcodeData['barcodes']) ? explode(',', $barcodeData['barcodes']) : [];
        //$mergeBarcode=($barcodeData['barcodes'])? explode(',',$barcodeData['barcodes']):[];
       //$productData['is_produce']!='1' &&
        if(!$skuAction &&  ((isset($barcodeData['barcodes']) && is_array($mergeBarcode) && !in_array($skuOrder,$mergeBarcode)) || $skuOrder!=$productData['sku'])){
             $productData['match']='1';
        }
        $productData['barcodes'] = $barcodeData['barcodes'] ?? null;
        
        echo json_encode($productData);
        exit;
    }
}

// SECOND - If not found in website database, use the original checkQuery
$checkQuery = "
    (
        SELECT erp_sku AS sku, 1 AS priority, NULL AS barcodes 
        FROM ahqa_erp_temp 
        WHERE merge_barcode = :sku OR erp_sku = :sku
    ) 
    UNION 
    (
        SELECT cpe.sku, 2 AS priority, GROUP_CONCAT(CONCAT(cpe.sku, ',', cpet.value) SEPARATOR ', ') AS barcodes
        FROM ahqa_catalog_product_entity AS cpe
        JOIN ahqa_catalog_product_entity_varchar AS cpet
            ON cpe.entity_id = cpet.entity_id
        WHERE cpet.attribute_id = 169
        GROUP BY cpe.entity_id
        HAVING cpe.sku LIKE :like_sku 
            OR GROUP_CONCAT(CONCAT(cpe.sku, '-', cpet.value) SEPARATOR ', ') LIKE :like_sku
    )
    ORDER BY priority
    LIMIT 1
";

$checkStmt = $conn->prepare($checkQuery);
$likeSku = '%' . $sku . '%';
$checkStmt->bindParam(':sku', $sku, PDO::PARAM_STR);
$checkStmt->bindParam(':like_sku', $likeSku, PDO::PARAM_STR);
$checkStmt->execute();

$checkResult = $checkStmt->fetch(PDO::FETCH_ASSOC);

if ($checkResult) {
    if ($checkResult['priority'] == 1) {
        // ERP product found - get full details from ERP table
        $erpQuery = "SELECT * FROM ahqa_erp_temp WHERE erp_sku = :erp_sku OR merge_barcode = :erp_sku LIMIT 1";
        $erpStmt = $conn->prepare($erpQuery);
        $erpStmt->bindParam(':erp_sku', $checkResult['sku'], PDO::PARAM_STR);
        $erpStmt->execute();
        
        $erpData = $erpStmt->fetch(PDO::FETCH_ASSOC);
        
        if ($erpData) {
            $erpData['priority'] = 1;
            $erpData['match']='0';
            $erpData['message'] = "ERP product found";
            echo json_encode($erpData);
        } else {
            echo json_encode([
                "error" => "ERP record not found",
                "sku" => $checkResult['sku'],
                "priority" => 1
            ]);
        }
    } else {
        // Catalog product found - get full details
        $productStmt = $conn->prepare($productQuery);
        $productStmt->bindParam(':sku', $checkResult['sku'], PDO::PARAM_STR);
        $productStmt->execute();

        $productData = $productStmt->fetch(PDO::FETCH_ASSOC);
        
        if ($productData) {
            $productData['match']='0';
            $productData['priority'] = 2;
            $productData['is_produce'] = $isProduce ?? 0;
            $productData['barcodes'] = $checkResult['barcodes'];
            echo json_encode($productData);
        } else {
            echo json_encode([
                "message" => "Catalog product found but details missing",
                "sku" => $checkResult['sku'],
                "priority" => 2,
                "barcodes" => $checkResult['barcodes']
            ]);
        }
    }
    exit;
}

// Product not found anywhere
echo json_encode([
    "message" => "Product not found in website or ERP system",
    "sku" => $sku,
    "suggestion" => "Please Send This Barcode to Data Entry Team"
]);
?>