<?php
include "../db/connection_main.php";

/**
 * FUNCTION: Execute query safely
 */
function runQuery($dbcon, $sql)
{
    $stmt = oci_parse($dbcon, $sql);
    $r = oci_execute($stmt);

    if (!$r) {
        $e = oci_error($stmt);
        echo json_encode(["error" => $e['message']]);
        exit;
    }

    oci_fetch_all($stmt, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
    return $rows;
}

/**
 * COMMON API CALL FUNCTION
 */
function callAPI($api_url, $bearer_token, $json_data)
{
    $ch = curl_init($api_url);
    curl_setopt_array($ch, [
        CURLOPT_CUSTOMREQUEST => "POST",
        CURLOPT_POSTFIELDS => $json_data,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_SSL_VERIFYPEER => false,
        CURLOPT_HTTPHEADER => [
            'Authorization: Bearer ' . $bearer_token,
            'Content-Type: application/json',
            'Content-Length: ' . strlen($json_data)
        ]
    ]);

    $response = curl_exec($ch);

    if (curl_errno($ch)) {
        return [
            "flag" => "",
            "message" => curl_error($ch),
            "response" => null
        ];
    }

    $response_data = json_decode($response, true);



    return [
        "flag" => $response_data['E_code'] ?? "",
        "message" => $response_data['E_Message'] ?? "",
        "response" => $response_data
    ];
}

/**
 * API CONFIG
 */
$api_url = 'https://medisafeportal.com/mst-api-prod/api/subinv-mst.php';
$bearer_token = 'eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiJZVFkiLCJuYW1lIjoiWVRZIEF0dGVuZGFuY2UiLCJhZG1pbiI6dHJ1ZX0.tWOG3I9GXNjNxbUeVO14baRgUEi7z7bWXswA7QBLaxuC-Nt23Asp6MgnopOHrYSfdDQMuG9ONmQPnTBWAO2QCvHjsiJ5WGDAW6s-uQ53t5hdkABYD9iIuUHmDTLENE9sEhF7HQHP5ebdgQXxny20q6C4KXnKdYOrr2khlzCf2kAJugAjj9UkuKIFZO8rxuZpMrYHimqnpd-5u6cqLj5_yExAR8bhaBmHHxwV7IWi2bxqLy655QAGV8PvH62dPsY4pYoMKFQKR8ff6sH1WUJacrqR2rylwWD_K1Uq8svZkr5ptwjB2mK5SC9SJ9-sU15Kp4YQnhs5J6ADcgRx1CEtZg';

/**
 * QUERY 1: EBS FAILED
 */

$ebs_sql = "SELECT *
            FROM gth_medisafe_invoice_details
            WHERE NVL(ebs_flag, 'N') NOT IN ('S','AP') and status = '1' and gth_id is not null and gth_id = '31230'";

$ebs_rows = runQuery($dbcon, $ebs_sql);

foreach ($ebs_rows as $row) {

    $dataReq = [
        "p_header_id" => $row["INVOICE_HEADER_ID"],
        "P_LINE_ID" => $row["INVOICE_LINE_ID"],
        "TR_TYPE" => "WIP",
        "FROM_SUBINV" => "GTH",
        "FROM_LOCATOR" => "GTH.INTR",
        "TO_SIBINV" => "GTH",
        "TO_LOCATOR" => "GTH.STWIP"
    ];

    $json_data = json_encode($dataReq);


    $api = callAPI($api_url, $bearer_token, $json_data);

    $res_json = json_encode($api['response']);

    if ($api['flag'] === 'S' || $api['flag'] === 'AP')
    {
        $sql = "UPDATE gth_medisafe_invoice_details
                SET status = 2,
                    ebs_flag = :flag,
                    ebs_message = :msg,
                    ebs_request = :req,
                    ebs_response = :res,
                    ebs_date = sysdate
                WHERE id = :id";
    }
    else
    {

        $sql = "UPDATE gth_medisafe_invoice_details
                SET ebs_flag = :flag,
                    ebs_message = :msg,
                    ebs_request = :req,
                    ebs_response = :res,
                    ebs_date = sysdate
                WHERE id = :id";
    }

    $stmt = oci_parse($dbcon, $sql);

    /**
     * Bind common values
     */
    oci_bind_by_name($stmt, ':msg', $api['message']);
    oci_bind_by_name($stmt, ':req', $json_data);
    oci_bind_by_name($stmt, ':res', $res_json);
    oci_bind_by_name($stmt, ':id', $row["ID"]);

    /**
     * Bind flag only when success
     */
    //if ($api['flag'] === 'S' || $api['flag'] === 'AP') {
        oci_bind_by_name($stmt, ':flag', $api['flag']);
    //}

    /**
     * Execute safely
     */
    if (!oci_execute($stmt, OCI_NO_AUTO_COMMIT)) {
        $e = oci_error($stmt);
        oci_rollback($dbcon);
        echo json_encode(["error" => $e['message']]);
        exit;
    }
}


/**
 * QUERY 2: STR FAILED
 */

$str_sql = "SELECT *
            FROM gth_medisafe_invoice_details
            WHERE NVL(str_flag, 'N') NOT IN ('S','AP') and status = '2' and ebs_flag IN ('S','AP') and gth_id is not null and invoice_line_id in (1228, 1229, 1225, 1226, 1227)";

$str_rows = runQuery($dbcon, $str_sql);

/**
 * PROCESS STR
 */

foreach ($str_rows as $row) {

    $dataReq = [
        "p_header_id" => $row["INVOICE_HEADER_ID"],
        "P_LINE_ID" => $row["INVOICE_LINE_ID"],
        "TR_TYPE" => "FG",
        "FROM_SUBINV" => "GTH",
        "FROM_LOCATOR" => "GTH.STWIP",
        "TO_SIBINV" => "GTH",
        "TO_LOCATOR" => "GTH.STFG"
    ];

    $json_data = json_encode($dataReq);

    $api = callAPI($api_url, $bearer_token, $json_data);

    $res_json = json_encode($api['response']);

    $updateFields = "str_message = :msg,
                    str_request = :req,
                    str_response = :res,
                    str_date = sysdate";

    $params = [
        ':msg' => $api['message'],
        ':req' => $json_data,
        ':res' => $res_json,
        ':id'  => $row["ID"]
    ];

    /**
     * If SUCCESS → update status + flag
     */
    if (isset($api['flag']) && ($api['flag'] === 'S' || $api['flag'] === 'AP')) {
        $sql = "UPDATE gth_medisafe_invoice_details
                SET status = 3,
                    str_flag = :flag,
                    $updateFields
                WHERE id = :id";

        $params[':flag'] = $api['flag'];
    } else {
        $sql = "UPDATE gth_medisafe_invoice_details
                SET str_flag = :flag,
                    $updateFields
                WHERE id = :id";

        $params[':flag'] = $api['flag'];
    }



    $stmt = oci_parse($dbcon, $sql);

    /**
     * Bind dynamically
     */
    foreach ($params as $key => $value) {
        oci_bind_by_name($stmt, $key, $params[$key]);
    }

    /**
     * Execute safely
     */
    if (!oci_execute($stmt, OCI_NO_AUTO_COMMIT)) {
        $e = oci_error($stmt);
        oci_rollback($dbcon);
        echo json_encode(["error" => $e['message']]);
        exit;
    }

}

/**
 * FINAL COMMIT
 */
oci_commit($dbcon);

echo json_encode([
    "message" => "Success",
    "processed_ebs" => count($ebs_rows),
    "processed_str" => count($str_rows),
    "api response" => $api
]);
