<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    if (!empty($data->status)) {
        $whereStatus = " AND CASE APPROVAL_FLAG 
                                WHEN 'A' THEN 'Approved'
                                WHEN 'R' THEN 'Rejected'
                                WHEN 'P' THEN 'In Progress'
                             ELSE 'New' END = '".$data->status."' ";
    }

    if (!empty($data->customer)) {
        $whereCustomer = " AND customer_id = '".$data->customer->ID."' ";
    }

    if ($data->comp == "MST") {
        $sql = "SELECT a.ID, a.NAME, a.PERIOD, a.CURRENCY_CODE, a.PRODUCT_ATTR_VALUE, a.CATEGORY_ID,
                       a.DESCRIPTION ITEM_DESCRIPTION, a.SKU_SIZE, b.p_size SIZ_NM, a.INCOTERM, a.BRAND, a.PRODUCT_UOM_CODE,
                       a.CUSTOMER_ID, a.CUSTOMER_NAME, a.VOLUME, a.NEW_PRICE, a.BILL_TO, a.SHIP_TO,
                       a.COMMENTS, a.APPROVER_COMMENTS, a.PACKING_TYPE, c.EMPLOYEE_NAME CREATED_BY,
                       CASE a.APPROVAL_FLAG 
                          WHEN 'A' THEN 'Approved'
                          WHEN 'R' THEN 'Rejected'
                          WHEN 'P' THEN 'In Progress'
                       ELSE 'New' END Status,
                       null order_number,
                       a.packing_type, NVL(company,'YTY') company,
                       c1.EMPLOYEE_NAME APPROVED_BY, TO_CHAR(APPROVED_DATE, 'DD-MON-RR') APPROVED_DATE
                  FROM apps.yty_g_prc_modifier_list_tl a,
                       (SELECT DISTINCT p_size, sku FROM apps.MST_item_mapping_skusizebrand_v) b,
                       production.employee_master c,
                       production.employee_master c1
                 WHERE a.SKU_SIZE = b.p_size(+)
                   AND a.PRODUCT_ATTR_VALUE = b.SKU(+) 
                   AND a.CREATED_BY = c.EMP_ID 
                   AND a.APPROVED_BY = c1.EMP_ID(+)
                   AND a.period = :period
                   AND a.currency_code = :currency
                   AND a.created_by = :userid
                   AND NVL(a.company,'YTY') = NVL(:company,'YTY')
                   ".$whereStatus.$whereCustomer."
              ORDER BY a.customer_name, a.product_attr_value";

    } else if ($data->comp == "YTY") {
        $sql = "SELECT a.ID, a.NAME, a.PERIOD, a.CURRENCY_CODE, a.PRODUCT_ATTR_VALUE, a.CATEGORY_ID,
                       a.DESCRIPTION ITEM_DESCRIPTION, a.SKU_SIZE, b.SIZ_NM, a.INCOTERM, a.BRAND, a.PRODUCT_UOM_CODE,
                       a.CUSTOMER_ID, a.CUSTOMER_NAME, a.VOLUME, a.NEW_PRICE, a.BILL_TO, a.SHIP_TO,
                       a.COMMENTS, a.APPROVER_COMMENTS, a.PACKING_TYPE, c.EMPLOYEE_NAME CREATED_BY,
                       CASE a.APPROVAL_FLAG 
                          WHEN 'A' THEN 'Approved'
                          WHEN 'R' THEN 'Rejected'
                          WHEN 'P' THEN 'In Progress'
                       ELSE 'New' END Status,
                       XXYTYCPORT.FETCH_ORDER_LIST(TO_DATE(a.PERIOD,'MON-RR'),
                                                   a.CUSTOMER_ID,
                                                   b.SIZ_NM,
                                                   a.BRAND,
                                                   a.PRODUCT_ATTR_VALUE) order_number,
                       CASE WHEN a.packing_type IS NOT NULL THEN
                          '[ ' || d.packing_type || ' ] ' || d.description 
                       END packing_type, NVL(company,'YTY') company,
                       c1.EMPLOYEE_NAME APPROVED_BY, TO_CHAR(APPROVED_DATE, 'DD-MON-RR') APPROVED_DATE
                  FROM apps.yty_g_prc_modifier_list_tl a,
                       production.size_master b,
                       production.employee_master c,
                       production.employee_master c1,
                       apps.xxyty_packing_type_v d
                 WHERE a.SKU_SIZE = b.siz_main(+)
                   AND a.CREATED_BY = c.EMP_ID 
                   AND a.APPROVED_BY = c1.EMP_ID(+)
                   AND a.PACKING_TYPE = d.PACKING_TYPE_CODE(+)
                   AND a.period = :period
                   AND a.currency_code = :currency
                   AND a.created_by = :userid
                   AND NVL(a.company,'YTY') = NVL(:company,'YTY')
                   ".$whereStatus.$whereCustomer."
              ORDER BY a.customer_name, a.product_attr_value";
    }

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':period', $data->period->ID);
    oci_bind_by_name($sqlarr, ':currency', $data->currency->ID);
    oci_bind_by_name($sqlarr, ':userid', $data->userid);
    oci_bind_by_name($sqlarr, ':company', $data->comp);
    $r = oci_execute($sqlarr);
    $resMsg = "OK";

    if (!$r) {
         $e = oci_error($sqlarr);
         $resMsg = json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    if ($data->comp == "MST") {
        foreach ($rows_main as $key => $row) {

            $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-".$row["PERIOD"],
                "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());
            }

            if (is_array($response_data["RETURN_FUNCTION"])) {
                $values = array_map('current', $response_data["RETURN_FUNCTION"]);
                $order_number = implode(", ", $values);
            } else {
                $order_number = $response_data["RETURN_FUNCTION"];
            }
            $rows_main[$key]["ORDER_NUMBER"] = $order_number;
        }
    }

    if (in_array('New', array_column($rows_main, 'STATUS'))) {
        $submit = "Y";
    } else {
        if (in_array('Rejected', array_column($rows_main, 'STATUS'))) {
            $submit = "Y";
        } else {
            $submit = "N";
        }
    }

    $result[] = [
        "message" => $resMsg,
        "data" => $rows_main,
        "submit" => $submit 
    ];

    print json_encode($result);
?>