<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    $copy_res = [];
    foreach ($data->data as $row) {
        $order_number = "";

        //Check if order already exist
        if ($data->comp == 'MST') {
            $api_url = 'https://medisafeportal.com/mst-api/api/price_update.php';
            $bearer_token = 'eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiJZVFkiLCJuYW1lIjoiWVRZIEF0dGVuZGFuY2UiLCJhZG1pbiI6dHJ1ZX0.tWOG3I9GXNjNxbUeVO14baRgUEi7z7bWXswA7QBLaxuC-Nt23Asp6MgnopOHrYSfdDQMuG9ONmQPnTBWAO2QCvHjsiJ5WGDAW6s-uQ53t5hdkABYD9iIuUHmDTLENE9sEhF7HQHP5ebdgQXxny20q6C4KXnKdYOrr2khlzCf2kAJugAjj9UkuKIFZO8rxuZpMrYHimqnpd-5u6cqLj5_yExAR8bhaBmHHxwV7IWi2bxqLy655QAGV8PvH62dPsY4pYoMKFQKR8ff6sH1WUJacrqR2rylwWD_K1Uq8svZkr5ptwjB2mK5SC9SJ9-sU15Kp4YQnhs5J6ADcgRx1CEtZg';

            $dataReq = [
                "V_EX_FT" => "01-".$data->target_period->ID,
                "V_CUSTOMER_ID" => $row->CUSTOMER_ID,
                "V_SIZE" => $row->SKU_SIZE,
                "V_BRAND" => $row->BRAND,
                "V_SKU" => $row->PRODUCT_ATTR_VALUE
            ];
            $json_data = json_encode($dataReq);

            try {
                $ch = curl_init($api_url);
                curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
                curl_setopt($ch, CURLOPT_POSTFIELDS, $json_data);
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($ch, CURLOPT_HEADER, false);
                curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

                $headers = [
                    'Authorization: Bearer ' . $bearer_token,
                    'Content-Type: application/json',
                    'Content-Length: ' . strlen($json_data)
                ];
                curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
                $response = curl_exec($ch);

                if (curl_errno($ch)) {
                    $response_data = null;
                    $error_msg = curl_error($ch);
                    $e_message = $error_msg;
                
                } else {
                    $response_data = json_decode($response, true);

                    if (json_last_error() !== JSON_ERROR_NONE) {
                        $response_data = null;
                    }
                }

                $e_flag = "";
                $e_message = "";
                if ($response_data) {
                    $e_flag = $response_data['E_code'];
                    $e_message = $response_data['E_Message'];
                }
            } catch (Exception $e) {
                error_log("Medisafe API Exception: " . $e->getMessage());
            }

            //Logs
            $sql = "INSERT INTO mst_pricing_api_log
                                (date_executed, p_period, p_customer, p_size, p_brand, p_sku, api_request, api_response)
                         VALUES (sysdate, :p_period, :p_customer, :p_size, :p_brand, :p_sku, :api_request, :api_response)";

            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':p_period', $data->target_period->ID);
            oci_bind_by_name($sqlarr, ':p_customer', $row->CUSTOMER_ID);
            oci_bind_by_name($sqlarr, ':p_size', $row->SKU_SIZE);
            oci_bind_by_name($sqlarr, ':p_brand', $row->BRAND);
            oci_bind_by_name($sqlarr, ':p_sku', $row->PRODUCT_ATTR_VALUE);
            oci_bind_by_name($sqlarr, ':api_request', $json_data);
            oci_bind_by_name($sqlarr, ':api_response', json_encode($response_data));
            $r = oci_execute($sqlarr);

            $order_number = count($response_data["RETURN_FUNCTION"]);

        } else if ($data->comp == 'YTY') {
            $sql = "SELECT XXYTYCPORT.FETCH_ORDER_LIST(TO_DATE(:p_period,'MON-RR'),
                                                :p_customer,
                                                null,
                                                null,
                                                substr(:p_sku,1,7)) order_no
                    FROM dual";

            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':p_period', $data->target_period->ID);
            oci_bind_by_name($sqlarr, ':p_customer', $row->CUSTOMER_ID);
            oci_bind_by_name($sqlarr, ':p_sku', $row->PRODUCT_ATTR_VALUE);
            $r = oci_execute($sqlarr);
            while ($rw = oci_fetch_array($sqlarr)) {
                $order_number = $rw['ORDER_NO'];
            }
        }

        if (empty($order_number)) {
            //Check if exists
            if (!empty($row->SKU_SIZE)) {
                $sizeWhere = " AND sku_size = '".$row->SKU_SIZE."' ";
            } else {
                $sizeWhere = "";
            }

            if (!empty($row->BRAND)) {
                $brandWhere = " AND brand = '".$row->BRAND."' ";
            } else {
                $brandWhere = "";
            }

            $sql = "SELECT COUNT(1) ctr
                      FROM apps.yty_g_prc_modifier_list_tl
                     WHERE period = :period
                       AND customer_id = :customerid
                       AND product_attr_value = :sku
                       ".$sizeWhere."
                       ".$brandWhere;

            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':period', $data->target_period->ID);
            oci_bind_by_name($sqlarr, ':customerid', $row->CUSTOMER_ID);
            oci_bind_by_name($sqlarr, ':sku', $row->PRODUCT_ATTR_VALUE);
            $r = oci_execute($sqlarr);
            while ($rw = oci_fetch_array($sqlarr)) {
                $exists = $rw['CTR'];
            }

            if ($exists == "0") {
                //SPR Data
                $sql = "SELECT spr_flag
                          FROM apps.yty_g_prc_modifier_list_tl
                         WHERE id = :id";
                $sqlarr = oci_parse($dbcon , $sql);
                oci_bind_by_name($sqlarr, ':id', $row->ID);
                $r = oci_execute($sqlarr);
                oci_fetch_all($sqlarr, $row_sprhead, null, null, OCI_FETCHSTATEMENT_BY_ROW);

                //Pricing Data
                $sql = "SELECT to_char(to_date(:period, 'MON-RR'), 'YYYY-MM-DD') start_date,
                               to_char(last_day(to_date(:period, 'MON-RR')), 'YYYY-MM-DD') end_date
                          FROM dual";

                $sqlarr = oci_parse($dbcon , $sql);
                oci_bind_by_name($sqlarr, ':period', $data->target_period->ID);
                $r = oci_execute($sqlarr);
                oci_fetch_all($sqlarr, $row_period, null, null, OCI_FETCHSTATEMENT_BY_ROW);

                $name=$data->comp."-QP-".$_SESSION['P_UNAME']."-".$row->CURRENCY_CODE."-".$data->target_period->ID;
                $start_date = $row_period[0]["START_DATE"];
                $end_date = $row_period[0]["END_DATE"];

                $sql = "INSERT INTO apps.yty_g_prc_modifier_list_tl
                                    (name, description, currency_code, period, start_date, end_date, 
                                     product_uom_code, product_attr_value, category_id, new_price, customer_name, customer_id, created_by, 
                                     creation_date, comments, volume, bill_to, ship_to, sku_size, incoterm, brand, size_nm, packing_type, company,
                                     spr_flag)
                             VALUES (:name, :description, :currency_code, :period, to_date(:start_date,'YYYY-MM-DD'), to_date(:end_date,'YYYY-MM-DD'), 
                                     :product_uom_code, :product_attr_value, :category_id, :new_price, :customer_name, :customer_id, :created_by, 
                                     SYSDATE, :comments, :volume, :bill_to, :ship_to, :sku_size, :incoterm, :brand, :size_nm, :packing_type, :company,
                                     :spr_flag)
                   RETURNING ID INTO :NEWID";

                $sqlarr = oci_parse($dbcon , $sql);
                oci_bind_by_name($sqlarr, ':name', $name);
                oci_bind_by_name($sqlarr, ':description', $row->ITEM_DESCRIPTION);
                oci_bind_by_name($sqlarr, ':currency_code', $row->CURRENCY_CODE);
                oci_bind_by_name($sqlarr, ':period', $data->target_period->ID);
                oci_bind_by_name($sqlarr, ':start_date', $start_date);
                oci_bind_by_name($sqlarr, ':end_date', $end_date);
                oci_bind_by_name($sqlarr, ':product_uom_code', $row->PRODUCT_UOM_CODE);
                oci_bind_by_name($sqlarr, ':product_attr_value', $row->PRODUCT_ATTR_VALUE);
                oci_bind_by_name($sqlarr, ':category_id', $row->CATEGORY_ID);
                oci_bind_by_name($sqlarr, ':new_price', $row->NEW_PRICE);
                oci_bind_by_name($sqlarr, ':customer_name', $row->CUSTOMER_NAME);
                oci_bind_by_name($sqlarr, ':customer_id', $row->CUSTOMER_ID);
                oci_bind_by_name($sqlarr, ':created_by', $data->userid);
                oci_bind_by_name($sqlarr, ':comments', $row->COMMENTS);
                oci_bind_by_name($sqlarr, ':volume', $row->VOLUME);
                oci_bind_by_name($sqlarr, ':bill_to', $row->BILL_TO);
                oci_bind_by_name($sqlarr, ':ship_to', $row->SHIP_TO);
                oci_bind_by_name($sqlarr, ':sku_size', $row->SKU_SIZE);
                oci_bind_by_name($sqlarr, ':incoterm', $row->INCOTERM);
                oci_bind_by_name($sqlarr, ':brand', $row->BRAND);
                oci_bind_by_name($sqlarr, ':NEWID', $newid, 20);

                if (!empty($row->SIZ_NM)) {
                    oci_bind_by_name($sqlarr, ':size_nm', $data->SIZ_NM);
                } else {
                    $svalue = null;
                    oci_bind_by_name($sqlarr, ':size_nm', $svalue);
                }

                oci_bind_by_name($sqlarr, ':packing_type', $row->PACKING_TYPE);
                oci_bind_by_name($sqlarr, ':company', $row->COMPANY);

                if (!empty($row_sprhead[0]["SPR_FLAG"])) {
                    oci_bind_by_name($sqlarr, ':spr_flag', $row_sprhead[0]["SPR_FLAG"]);
                } else {
                    $svalue = null;
                    oci_bind_by_name($sqlarr, ':spr_flag', $svalue);
                }
                
                $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

                if (!$r) {
                    $e = oci_error($sqlarr);
                    $errorMsg = json_encode($e['message']);
                    goto result_collect;
                }

                //SPR Details
                //Insert SPR Price
                $sql = "INSERT INTO apps.yty_g_prc_modifier_list_spr
                                    (header_id, spr_sku_size, spr_size_nm, spr_price)
                        SELECT ".$newid.", spr_sku_size, spr_size_nm, spr_price
                            FROM apps.yty_g_prc_modifier_list_spr
                            WHERE header_id = :header_id";
                
                $sqlarr = oci_parse($dbcon , $sql);
                oci_bind_by_name($sqlarr, ':header_id', $row->ID);
                $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

                if (!$r) {
                    $e = oci_error($sqlarr);
                    $errorMsg = json_encode($e['message']);
                    goto result_collect;
                }

                $errorMsg = "Success";

            } else {
                $errorMsg = "The price with this combination already exists.";
            }

        } else {
            $errorMsg = "There is already order raised with this price combination: ".$order_number;
        }

        //Create new array
result_collect:
        $copy_res[] = [
            "NAME" => $name,
            "PERIOD" => $row->PERIOD,
            "CURRENCY_CODE" => $row->CURRENCY_CODE,
            "CATEGORY_ID" => $row->CATEGORY_ID,
            "PRODUCT_ATTR_VALUE" => $row->PRODUCT_ATTR_VALUE,
            "ITEM_DESCRIPTION" => $row->ITEM_DESCRIPTION,
            "SKU_SIZE" => $row->SKU_SIZE,
            "INCOTERM" => $row->INCOTERM,
            "BRAND" => $row->BRAND,
            "PRODUCT_UOM_CODE" => $row->PRODUCT_UOM_CODE,
            "CUSTOMER_ID" => $row->CUSTOMER_ID,
            "CUSTOMER_NAME" => $row->CUSTOMER_NAME,
            "VOLUME" => $row->VOLUME,
            "NEW_PRICE" => $row->NEW_PRICE,
            "BILL_TO" => $row->BILL_TO,
            "SHIP_TO" => $row->SHIP_TO,
            "COMMENTS" => $row->COMMENTS,
            "SYSTEM_MARKS" => $errorMsg
        ];
    }

    $result = [];
    oci_commit($dbcon);
    //oci_rollback($dbcon);
    $result[] = [
        "message" => "Success",
        "data" => $copy_res
    ];
    
    print json_encode($result);
?>
