<?php

class Order {

    // Variable global para el límite de líneas en WS
    const MAX_LINE_WS = 2;

  public static function createOutgoing($input) {

    error_log("INPUT GENERAL OS".print_r($input,true)."\n", 3, "php.log");

    $OPFGlobal = new OPFGlobal();
    $OPFGlobal->initOPF();

    $dataWS = array();

    // =========================
    // 🔒 VALIDACIONES NUEVAS
    // =========================

    $subject = mysqli_real_escape_string($OPFGlobal->link, $input["subject"]);

    $SQLOrders = "SELECT so.salesorderid, so.sostatus as EstadoOS, cf.cf_1897 as CompletadoOS, cr.createdtime
                  FROM vtiger_salesorder so
                  INNER JOIN vtiger_crmentity cr ON cr.crmid = so.salesorderid AND cr.deleted = 0
                  INNER JOIN vtiger_salesordercf cf ON cf.salesorderid = so.salesorderid
                  WHERE so.subject = '$subject'
                  ORDER BY cr.createdtime ASC";

    $resultOrders = mysqli_query($OPFGlobal->link, $SQLOrders);

    if (!$resultOrders) {
        throw new Exception("Error consultando la BD ".mysqli_error($OPFGlobal->link),500);
    }

    $orders = [];
    while ($row = mysqli_fetch_assoc($resultOrders)) {
        $orders[] = $row;
    }

    // 🔒 1. VALIDAR OS ACTIVA
    foreach ($orders as $order) {
        if (
            in_array($order["EstadoOS"], ["APROBADO", "Asignado", "Crear caja"]) &&
            $order["CompletadoOS"] == 0
        ) {
            error_log("Error 400 - OS EN PROCESO --".$input["subject"]."\n", 3, "Errorphp.log");
            throw new Exception("OS en proceso", 400);
        }
    }

    // 🧬 2. OBTENER OS PADRE
    $OSPadreId = null;
    if (!empty($orders)) {
        $OSPadreId = $orders[0]["salesorderid"];
    }

    // 📦 3. MAPEAR ITEMS NUEVOS
    $newItems = [];
    foreach ($input["items"] as $item) {
        $newItems[$item["product"]] = floatval($item["quantity"]);
    }

    // 📊 4. OBTENER TOTAL_PEDIDO DESDE PADRE
    $totalPedido = [];
    if ($OSPadreId !== null) {

        $SQLPadreItems = "SELECT ip.productid, ip.total_pedido as total_pedido, p.productcode
                          FROM vtiger_inventoryproductrel ip
                          INNER JOIN vtiger_products p ON p.productid = ip.productid
                          WHERE ip.id = $OSPadreId";

        $resultPadreItems = mysqli_query($OPFGlobal->link, $SQLPadreItems);

        while ($row = mysqli_fetch_assoc($resultPadreItems)) {
            $totalPedido[$row["productcode"]] = $row["total_pedido"];
        }
    }

    // 🔢 5. SUMAR PROCESADO DE TODAS LAS OS
    $procesadoTotal = [];

    if (!empty($orders)) {

        $orderIds = array_column($orders, 'salesorderid');
        $ids = implode(',', array_map('intval', $orderIds));

        $SQLProcesado = "SELECT p.productcode, SUM(ip.procesado) as total_procesado
                         FROM vtiger_inventoryproductrel ip
                         INNER JOIN vtiger_products p ON p.productid = ip.productid
                         WHERE ip.id IN ($ids)
                         GROUP BY p.productcode";

        $resultProcesado = mysqli_query($OPFGlobal->link, $SQLProcesado);

        while ($row = mysqli_fetch_assoc($resultProcesado)) {
            $procesadoTotal[$row["productcode"]] = floatval($row["total_procesado"]);
        }
    }

    // 🚨 6. VALIDAR CAPACIDAD
    if ($OSPadreId !== null) {
        foreach ($newItems as $productCode => $newQty) {

            if (!isset($totalPedido[$productCode])) {
                //throw new Exception("Producto no existe en OS padre", 500);
                $totalPedido[$productCode]="9999999999999";
            }

            $procesado = $procesadoTotal[$productCode] ?? 0;
            $total = $totalPedido[$productCode];
            error_log("ERROR DE PROCESADO ".$totalPedido[$productCode]." Procesado: ".$procesado."-- NuevaQty: ".$newQty."-- TOTAL: ".$total."\n", 3, "Errorphp.log");
            if (($procesado + $newQty) > $total) {
                error_log("Error 400 - OS supera cantidad total --".$input["subject"]."\n", 3, "Errorphp.log");
                throw new Exception("OS supera cantidad total", 400);
            }
        }
    }

    // =========================
    // 🧾 RESTO DE TU FLUJO ORIGINAL
    // =========================

    require_once 'models/Contact.php';
    require_once 'models/Account.php';

    $inputAccount = array();

    $inputAccount["accountname"] = $input["accountname"];
    $inputAccount["razon_social"] = $input["razon_social"];
    $inputAccount["rut_code"] = $input["rut_code"];
    $inputAccount["rut"] = $input["rut"];
    $inputAccount["email"] = $input["email"];
    $inputAccount["status"] = $input["status"];
    $inputAccount["address_comercial"] = $input["acc_address_comercial"];
    $inputAccount["comuna_comercial"] = $input["acc_comuna_comercial"];
    $inputAccount["region_comercial"] = $input["acc_region_comercial"];
    $inputAccount["country_comercial"] = $input["acc_country_comercial"];
    $inputAccount["address_shipping"] = $input["acc_address_shipping"];
    $inputAccount["comuna_shipping"] = $input["acc_comuna_shipping"];
    $inputAccount["region_shipping"] = $input["acc_region_shipping"];
    $inputAccount["country_shipping"] = $input["acc_country_shipping"];

    $accountid = Account::create($inputAccount)["accountid"];

    $inputContact = array();

    $dataWS["account_id"] = "11x".$accountid;

    if ($input["contact_name"] <> "") {

        $inputContact["email"] = $input["contact_email"];
        $inputContact["name"] = $input["contact_name"];
        $inputContact["lastname"] = $input["contact_lastname"];
        $inputContact["phone"] = $input["contact_phone"];
        $inputContact["phone2"] = $input["contact_phone2"];

        $contactid = Contact::create($inputContact)["contactid"];

        $dataWS["contact_id"] = "12x".$contactid;
    }

    $dataWS["cf_856"] = $input["type_os"];
    $dataWS["cf_2687"] = $input["urgency"];
    $dataWS["sostatus"] = "APROBADO";
    $dataWS["bill_street"] = $input["address_comercial"];
    $dataWS["bill_city"] = $input["comuna_comercial"];
    $dataWS["bill_state"] = $input["region_comercial"];
    $dataWS["bill_country"] = $input["country_comercial"];
    $dataWS["ship_street"] = $input["address_shipping"];
    $dataWS["ship_city"] = $input["comuna_shipping"];
    $dataWS["ship_state"] = $input["region_shipping"];
    $dataWS["ship_country"] = $input["country_shipping"];
    $dataWS["description"] = $input["description"];
    $dataWS["subject"] = $input["subject"];

    $dataWS["cf_2803"] = $input["purchaseOrder"];
    $dataWS["cf_2685"] = $input["trucks"];
    $dataWS["cf_2703"] = $input["transport"];
    $dataWS["cf_2757"] = "Planificado";
    @$dataWS["cf_2827"] = $input["numero_pedido_2"] ;
       date_default_timezone_set('America/Santiago');
       $dateF=date('Y-m-d H:i:s');
       $dataWS["cf_2761"] = $dateF;
       
       /*$dataWS["cf_2743"] = $input["q_certificate"]; //certificado de calidad
       $dataWS["cf_2751"] = $input["box_pallet"];    //cajas por pallet
       $dataWS["cf_2753"] = $input["p_height"];             //altura de pallet
       $dataWS["cf_852"] = $input["t_schedule"];           //tipo de Agendimiento
       $dataWS["cf_858"] = $input["t_pallet"];            //Tipo de pallet*/

    // 🧬 ASIGNAR OSPADRE
    if ($OSPadreId !== null) {
        $dataWS["cf_2773"] = "6x".$OSPadreId;
    }

    // =========================
    // 📦 ITEMS (tu lógica original)
    // =========================

    $ItemsIs = array();
    $Items = array();

    foreach ($input["items"] as &$item) {

        $ProducCode = $item["product"];

        $SQL = "SELECT productid FROM vtiger_products
                INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_products.productid AND deleted='0'
                where productcode = '$ProducCode'";

        $result = mysqli_query($OPFGlobal->link,$SQL);

        if($result){
            $rowData = mysqli_fetch_assoc($result);
            if ($rowData <> null) {

                $ItemTemp = array();
                $ItemTemp["productid"] = "14x".$rowData["productid"];
                $ItemTemp["listprice"] = $item["unit_price"];
                $ItemTemp["quantity"] = $item["quantity_storage"];

                array_push($ItemsIs, $ItemTemp);

                $ItemTemp["storage_unity"] = $item["storage_unity"];
                $ItemTemp["quantity_storage"] = $item["quantity"];
                $ItemTemp["total_pedido"] = $item["total_pedido"];

                array_push($Items, $ItemTemp);

            } else {
                error_log("Error 400 - Producto no encontrado".print_r($input,true)."\n", 3, "Errorphp.log");
                throw new Exception("Producto '$ProducCode' no encontrado",400);
            }
        } else {
            throw new Exception("Error consultando la BD ".mysqli_error($OPFGlobal->link),500);
        }
    }

    $dataWS["LineItems"] = $ItemsIs;
    $dataWS["productid"] = $ItemsIs[0]["productid"];

    ob_start();
    $salesorderid = @$OPFGlobal->crearByWS('SalesOrder', $dataWS,'SalesOrderWS.log',false,true);
    $output = ob_get_clean();

    if ($output <> "") {
        
        throw new Exception(json_decode($salesorderid,true)["id"]["message"],400);
    }

    Order::updateInventoryProductRel($OPFGlobal,$salesorderid,$Items);

    return ['salesorderid' => $salesorderid];
}

    public static function createIncoming($input) {

       $OPFGlobal = new OPFGlobal();
       $OPFGlobal->initOPF();

       // Validar que no exista un registro con el mismo subject, fecha de creación y items
       $subject = mysqli_real_escape_string($OPFGlobal->link, $input["subject"]);
       $SQLCheckSubject = "SELECT vtiger_purchaseorder.purchaseorderid, DATE(vtiger_crmentity.createdtime) as created_date
                          FROM vtiger_purchaseorder 
                          INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_purchaseorder.purchaseorderid AND deleted='0'
                          WHERE subject = '$subject'";
       $resultCheck = mysqli_query($OPFGlobal->link, $SQLCheckSubject);
       
       if ($resultCheck && mysqli_num_rows($resultCheck) > 0) {
           $existingOrder = mysqli_fetch_assoc($resultCheck);
           $existingOrderId = $existingOrder['purchaseorderid'];
           $createdDate = $existingOrder['created_date'];
           $currentDate = date('Y-m-d');
           
           // Verificar si la fecha de creación es la misma (mismo día)
           if ($createdDate === $currentDate) {
               // Obtener items de la orden existente
               $SQLCheckItems = "SELECT productid, quantity FROM vtiger_inventoryproductrel 
                                WHERE id = '$existingOrderId' ORDER BY productid, quantity";
               $resultItems = mysqli_query($OPFGlobal->link, $SQLCheckItems);
               
               $existingItems = array();
               while ($row = mysqli_fetch_assoc($resultItems)) {
                   $existingItems[] = array(
                       'productid' => $row['productid'],
                       'quantity' => floatval($row['quantity'])
                   );
               }
               
               // Preparar items nuevos para comparar
               $newItems = array();
               foreach ($input["items"] as $item) {
                   $ProducCode = mysqli_real_escape_string($OPFGlobal->link, $item["product"]);
                   $SQLProduct = "SELECT productid FROM vtiger_products
                                 INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_products.productid AND deleted='0'
                                 WHERE productcode = '$ProducCode'";
                   $resultProduct = mysqli_query($OPFGlobal->link, $SQLProduct);
                   if ($resultProduct && $rowProduct = mysqli_fetch_assoc($resultProduct)) {
                       $quantity_storageOrigin = $item["quantity_storage"];
                       $newItems[] = array(
                           'productid' => $rowProduct['productid'],
                           'quantity' => floatval(round($quantity_storageOrigin, 3))
                       );
                   }
               }
               
               // Ordenar ambos arrays para comparar
               usort($existingItems, function($a, $b) {
                   return strcmp($a['productid'], $b['productid']);
               });
               usort($newItems, function($a, $b) {
                   return strcmp($a['productid'], $b['productid']);
               });
               
               // Comparar items
               if (count($existingItems) === count($newItems)) {
                   $itemsMatch = true;
                   for ($i = 0; $i < count($existingItems); $i++) {
                       if ($existingItems[$i]['productid'] !== $newItems[$i]['productid'] || 
                           abs($existingItems[$i]['quantity'] - $newItems[$i]['quantity']) > 0.001) {
                           $itemsMatch = false;
                           break;
                       }
                   }
                   
                   if ($itemsMatch) {
                       error_log("Error 400 - Ya existe una Orden de Entrada con el mismo subject -- ".$input["subject"]."\n", 3, "Errorphp.log");
                       throw new Exception("Ya existe una Orden de Entrada con el mismo subject '$subject', creada hoy con los mismos items", 400);
                   }
               }
           }
       }

       $dataWS = array();
           
       $dataWS["subject"] = $input["subject"];
       $dataWS["cf_2156"] = $input["type_oc"];
       $dataWS["description"] = $input["description"];
       $dataWS["postatus"] = "Crear caja";


       $dataWS["cf_2717"] = $input["oc"];
       $dataWS["tracking_no"] = $input["requisitionNum"];
       $dataWS["cf_1705"] = $input["ocDate"];

       $dataWS["cf_2719"] = $input["nrNum"];
       $dataWS["requisition_no"] = $input["opNum"];
       $dataWS["cf_2098"] = $input["nrDate"];
       $dataWS["cf_2094"] = $input["opState"];

       $dataWS["cf_1717"] = $input["refundNum"];
       
       $dataWS["cf_2759"] = "Planificado";

       if (isset($input["refundSO"])) {
          if ($input["refundSO"] <> "") {

            $salesorderNo = $input["refundSO"];

             $SQL = "SELECT salesorderid  FROM vtiger_salesorder
             where salesorder_no = '$salesorderNo'";

             $result = mysqli_query($OPFGlobal->link,$SQL); 
             $rowData = mysqli_fetch_assoc($result);

             if ($rowData <> null) {
                 $dataWS["cf_2144"] = "6x".$rowData["salesorderid"];
             }

          }
       }


       switch ($input["currency"]) {
          case 'Pesos':
             $dataWS["currency_id"] = "21x1";
             break;
          case 'Soles':
             $dataWS["currency_id"] = "21x1";
             break;
          case 'Dolar':
             $dataWS["currency_id"] = "21x1";
             break;
          case 'Euro':
             $dataWS["currency_id"] = "21x1";
             break;
          default:
             break;
       }

       $ItemsIs = array();
       $Items = array();

       foreach ($input["items"] as &$item) {

         $ProducCode = $item["product"];

          if ($item["quantity"] == "" or $item["quantity_storage"] == "" or $item["storage_unity"] == "") {
            $dataWS["postatus"] = "Con Error";
         }

         $quantity_storageOrigin = $item["quantity_storage"];
         $quantityOrigin = $item["quantity"];

         $item["quantity"] = round($quantity_storageOrigin,3);
         $item["quantity_storage"] = round($quantityOrigin,3);

         if (fmod($item["quantity"], 1) == 0) {
            $item["quantity"] = (int) $item["quantity"];
         } else {
            $dataWS["postatus"] = "Con Error";
         }

         if (fmod($item["quantity_storage"], 1) == 0) {
            $item["quantity_storage"] = (int) $item["quantity_storage"];
         } else {
            $dataWS["postatus"] = "Con Error";
         }
          
          $SQL = "SELECT productid FROM vtiger_products
             INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_products.productid AND deleted='0'
             where productcode = '$ProducCode'";

          $result = mysqli_query($OPFGlobal->link,$SQL); 
          
           if($result){
               $rowData = mysqli_fetch_assoc($result);
               if ($rowData <> null) {
                   $item["productid"] = $rowData["productid"];
                   $ItemTemp = array();
                   $ItemTemp["productid"] = "14x".$rowData["productid"];
                   $ItemTemp["listprice"] = $item["unit_price"];
                   $ItemTemp["quantity"] = $item["quantity"];
                   
                   if (count($ItemsIs) < self::MAX_LINE_WS) {
                     array_push($ItemsIs, $ItemTemp);
                   }

                   $ItemTemp["storage_unity"] = $item["storage_unity"];
                   $ItemTemp["quantity_storage"] = $item["quantity_storage"];

                   array_push($Items, $ItemTemp);

               }else{
                   error_log("Error 400 - Producto no encontrado -- ".$input["subject"]."\n", 3, "Errorphp.log");
                  throw new Exception("Producto '$ProducCode' no encontrado",400);
               }

           }else{
               throw new Exception("Error consultando la BD ".mysqli_error($OPFGlobal->link),500);
           }

       }

       $dataWS["LineItems"] = $ItemsIs;
       $dataWS["productid"] = $ItemsIs[0]["productid"];
        

       ob_start();
       $purchaseorderid = @$OPFGlobal->crearByWS('PurchaseOrder', $dataWS,'PurchaseOrderWS.log',false,true);
       $output = ob_get_clean();

       if ($output <> "") {
          throw new Exception(json_decode($purchaseorderid,true)["id"]["message"],500);   
       }

       Order::updateInventoryProductRel($OPFGlobal,$purchaseorderid,$Items);

       $SQL = "SELECT purchaseorder_no FROM vtiger_purchaseorder
             where purchaseorderid = '$purchaseorderid'";

       $result = mysqli_query($OPFGlobal->link,$SQL); 
       $rowData = mysqli_fetch_assoc($result);

       return ['purchaseorderid' => $purchaseorderid , 'purchaseorder_no' => $rowData["purchaseorder_no"]];

    }

    public static function updateInventoryProductRel($OPFGlobal,$salesorderid,$items) {
       $SQLvtiger_inventoryproductrel = "SELECT productid,quantity,lineitem_id FROM vtiger_inventoryproductrel
         where id = '$salesorderid' order by lineitem_id asc LIMIT " . self::MAX_LINE_WS;

       $result = mysqli_query($OPFGlobal->link,$SQLvtiger_inventoryproductrel); 

       if (!$result) {
          throw new Exception("Error consultando vtiger_inventoryproductrel  ".mysqli_error($OPFGlobal->link),500);
       }

       $SQLBatch = "";
       $index = 0;


         foreach ($items as $item) {
   
            $productid = $item["productid"];

            // Normalizar productid quitando el prefijo "14x" si existe
            $productidNormalized = str_replace("14x", "", $productid);

            $storage_unity = mysqli_real_escape_string($OPFGlobal->link, $item["storage_unity"]);
            $quantity_storage = mysqli_real_escape_string($OPFGlobal->link, $item["quantity_storage"]);
            $total_pedido = mysqli_real_escape_string($OPFGlobal->link, $item["total_pedido"]);

            if ($index < self::MAX_LINE_WS) {
               $rowData = mysqli_fetch_assoc($result);

               $rowProductidNormalized = $rowData["productid"];
               
               // Normalizar cantidades para comparación (convertir a float)
               $quantityNormalized = floatval($item["quantity"]);
               $rowQuantityNormalized = floatval($rowData["quantity"]);

               if ($rowProductidNormalized != $productidNormalized || $rowQuantityNormalized != $quantityNormalized) {
                  $msj = $rowData["productid"] . " <> " . $productidNormalized . " || " . $rowData["quantity"] . " <> " . $item["quantity"];
                  throw new Exception("Error actualizando storage_unity y quantity_storage (inconsistencia de datos): " . $msj);
               }

               $lineitem_id = $rowData["lineitem_id"];
               
               $SQLBatch .= "UPDATE vtiger_inventoryproductrel SET storage_unity = '$storage_unity', quantity_storage = '$quantity_storage' , total_pedido = '$total_pedido' WHERE lineitem_id = '$lineitem_id'; ";
            } 
            else {
               $quantity = mysqli_real_escape_string($OPFGlobal->link, $item["quantity"]);
               $listprice = mysqli_real_escape_string($OPFGlobal->link, $item["listprice"]);
               
               $sequence_no = 0;
               
               $SQLBatch .= "INSERT INTO vtiger_inventoryproductrel (id, productid, sequence_no, quantity, listprice, storage_unity, quantity_storage, total_pedido, incrementondel) 
                           VALUES ('$salesorderid', '$productidNormalized', '$sequence_no', '$quantity', '$listprice', '$storage_unity', '$quantity_storage', '$total_pedido', 1); ";
            }

            $index++;
         }         

         if ($SQLBatch != "" && !mysqli_multi_query($OPFGlobal->link, $SQLBatch)) {
            throw new Exception("Error ejecutando batch SQL: ".mysqli_error($OPFGlobal->link),500);
         }

         while (mysqli_more_results($OPFGlobal->link)) {
            mysqli_next_result($OPFGlobal->link);
         }
    }
   
    public static function delete_Entrada($input) {
        
        $OPFGlobal = new OPFGlobal();
        $OPFGlobal->initOPF();
        
        $pono=$input['purchaseorder_no'];
        $SQL= "SELECT purchaseorderid FROM vtiger_purchaseorder WHERE  purchaseorder_no = '$pono'";
        $result = mysqli_query($OPFGlobal->link,$SQL); 
        if($result){
            $rowData = mysqli_fetch_assoc($result);
            $POID = $rowData["purchaseorderid"];
            if($POID>0){
                mysqli_query($OPFGlobal->link,"UPDATE vtiger_crmentity SET deleted='1' WHERE crmid='$POID'");
            }else{
                throw new Exception("Error OE no encontrada  ",200);
            }
        }else{
            throw new Exception("Error OE no encontrada2  ",200);
        } 
    }    
    
    public static function delete_Salida($input) {
        $OPFGlobal = new OPFGlobal();
        $OPFGlobal->initOPF();
        
        $sono=$input['salesorder_no'];
        $SQL= "SELECT salesorderid FROM vtiger_salesorder WHERE  salesorder_no = '$sono'";
        $result = mysqli_query($OPFGlobal->link,$SQL); 
        if($result){
            $rowData = mysqli_fetch_assoc($result);
            $SOID = $rowData["salesorderid"];
            if($SOID>0){
                mysqli_query($OPFGlobal->link,"UPDATE vtiger_crmentity SET deleted='1' WHERE crmid='$SOID'");
            }else{
                throw new Exception("Error OT no encontrada  ",200);
            }
        }else{
            throw new Exception("Error OT no encontrada2  ",200);
        } 
    }    
}
