<?php
    global $dbcon;
    use PHPMailer\PHPMailer\PHPMailer;
    use PHPMailer\PHPMailer\SMTP;
    use PHPMailer\PHPMailer\Exception;

    include "../db/headerApi.php";
    include "../db/connection_main.php";
    require_once('vendor/autoload.php');

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    $errorMsg = "";

    //Request Detail
    $sql = "SELECT A.ID, A.REQUESTER, B.EMPLOYEE_NAME REQUESTER_NAME, A.REQ_FOR, R_LEVEL,
                   TO_CHAR(A.DATE_REQUEST, 'DD/MM/YYYY') as DATE_REQUEST, A.HW_SELECTED,
                   NVL(A.SUPPORTING_NOTE, '-') as SUPPORTING_NOTE, A.UPDATES_NOTE, 
                   A.ATTACHMENT_FILE1, A.ATTACHMENT_FILE2, A.ATTACHMENT_FILE3, NVL(A.STATUS, 0) STATUS,
                   A.HW_AVAIL, A.HW_SELECTED, A.POS_TITLE, A.DEPARTMENT, A.HOD, TO_CHAR(A.JOIN_DATE,'DD/MM/YYYY') JOIN_DATE, 
                   A.WORK_LOCATION, A.WORK_ROLE, A.CONTACT_NO, A.EMAIL_ADDRESS, A.POS_GROUP, A.REQUEST_TYPE, A.REQ_FOR_EMP, A.REQ_ID
              FROM ITINV_REQUEST A, EMPLOYEE_MASTER B
             WHERE A.REQUESTER = B.EMP_ID
               AND A.ID = :ID";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':ID', $data->ID);
    $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

    if (!$r) {
        $e = oci_error($sqlarr);
        $errorMsg = json_encode($e['message']);
        goto finalStep;
    }

    $rows_main = [];
    oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    if ($data->ACT == "APPROVED") {
        //Update Request
        $sql = "UPDATE itinv_request
                   SET status = 2,
                       current_approval = 2
                 WHERE id = :id";

        $sqlarr = oci_parse($dbcon, $sql);
        oci_bind_by_name($sqlarr, ':id', $data->ID);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }

        //App Trans
        $sql = "UPDATE ytycustom.user_reg_app_trans
                   SET action = 'Approve',
                       enter_date = sysdate,
                       enter_by = :enter_by
                 WHERE doc_no = :doc_no
                   AND appr_level = 1
                   AND approver_emp_id = :approver_emp_id
                   AND doc_type_code = '2009'";

        $sqlarr = oci_parse($dbcon, $sql);
        oci_bind_by_name($sqlarr, ':doc_no', $rows_main[0]["REQ_ID"]);
        oci_bind_by_name($sqlarr, ':enter_by', $data->USERID);
        oci_bind_by_name($sqlarr, ':approver_emp_id', $data->USERID);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }
   
        //Email Notification - HRF to IT Team
        //IT or Admin Email
        $sql = "SELECT b.email_id
                  FROM APPS.XXYTY_SSO_REPORTS_ACCESS a, employee_master b
                 WHERE a.emp_id = b.emp_id
                   AND a.portal = 'ADMIN_ITINV'";

        $sqlarr = oci_parse($dbcon, $sql);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }

        $rows_email = [];
        oci_fetch_all($sqlarr, $rows_email, null, null, OCI_FETCHSTATEMENT_BY_ROW);

        $mail = new PHPMailer(true);
        try {
            //Server settings                 
            $mail->isSMTP();
            $mail->Host       = 'email-smtp.us-east-1.amazonaws.com';
            $mail->SMTPAuth   = true;
            $mail->Username   = 'AKIA4TM64HI4LPHXMK6L';
            $mail->Password   = 'BFAufGeMYsb8sgH5Ta5VaTR/0omvCn6Ev3qFp+5wG83+';
            $mail->SMTPSecure = 'ssl';
            $mail->Port       = 465;
            $mail->isHTML(true); 
            $mail->setFrom('info.yty@ytygroup.com.my', 'YTY Workflow');

            //IT
            foreach ($rows_email as $email) {
                $mail->addAddress($email["EMAIL_ID"]);             
            }   
            $mail->addBCC('reyndo.frenliedo@ytygroup.com.my');  

            //Content
            $mail->Subject = 'TRR (REQ_ID:'.$rows_main[0]["REQ_ID"].') - APPROVED';
            $mail->Body    = '<p>Dear All,</p>
                                <p>The Technology Reassignment Request (TRR) has been approved. The details are as follows:</p>
                                <table border="0">
                                <tr>
                                <td><strong>Full Name:</strong> '.$rows_main[0]["REQ_FOR"].'</td>
                                <tr>
                                <tr>
                                <td><strong>Position Title:</strong> '.$rows_main[0]["POS_TITLE"].'</td>
                                <tr>
                                <tr>
                                <td><strong>Work Location:</strong> '.$rows_main[0]["WORK_LOCATION"].'</td>
                                <tr>
                                <tr>
                                <td><strong>Join Date:</strong> '.$rows_main[0]["JOIN_DATE"].'</td>
                                <tr>
                                <tr>
                                <td><strong>Hardware Requested:</strong> '.$rows_main[0]["HW_SELECTED"].'</td>
                                <tr>
                                </table>

                                <p>Please click this <a href="https://ytygroup.app/itinv-app/request/'.$rows_main[0]["ID"].'">link</a> to view the details.</p><br />
                                <p>Thanks.</p>
                                <p style="font-size: 12px; color: #666;">*This is an automated email. Please do not reply.</p>';
            
            $sendStatus = $mail->send();
        
        } catch (Exception $e) {
            $errorMsg = "Email could not be sent. Mailer Error: {$mail->ErrorInfo}";
        }
        
    } else if ($data->ACT == "REJECTED") {
        //Update Request
        $sql = "UPDATE itinv_request
                   SET status = -1,
                       approver_remarks = :approver_remarks
                 WHERE id = :id";

        $sqlarr = oci_parse($dbcon, $sql);
        oci_bind_by_name($sqlarr, ':id', $data->ID);
        oci_bind_by_name($sqlarr, ':approver_remarks', $data->REASON);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }

        //App Trans
        $sql = "UPDATE ytycustom.user_reg_app_trans
                   SET action = 'Reject',
                       reason = :reason,
                       enter_date = sysdate,
                       enter_by = :enter_by
                 WHERE doc_no = :doc_no
                   AND appr_level = 1
                   AND approver_emp_id = :approver_emp_id
                   AND doc_type_code = '2009'";

        $sqlarr = oci_parse($dbcon, $sql);
        oci_bind_by_name($sqlarr, ':doc_no', $rows_main[0]["REQ_ID"]);
        oci_bind_by_name($sqlarr, ':enter_by', $data->USERID);
        oci_bind_by_name($sqlarr, ':approver_emp_id', $data->USERID);
        oci_bind_by_name($sqlarr, ':reason', $data->REASON);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }

        //Submitter
        $sql = "SELECT b.emp_id, b.employee_name, b.email_id
                  FROM YTYCUSTOM.USER_REG_APP_TRANS a, PRODUCTION.EMPLOYEE_MASTER b
                 WHERE a.APPROVER_EMP_ID = b.EMP_ID
                   AND a.doc_type_code = '2009'
                   AND a.doc_no = '".$rows_main[0]["REQ_ID"]."'
                   AND appr_level = 0";

        $sqlarr = oci_parse($dbcon, $sql);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
        $rows_submitter = [];
        oci_fetch_all($sqlarr, $rows_submitter, null, null, OCI_FETCHSTATEMENT_BY_ROW);

        //Email Notification To Submitter
        $mail = new PHPMailer(true);
        try {
            //Server settings                 
            $mail->isSMTP();
            $mail->Host       = 'email-smtp.us-east-1.amazonaws.com';
            $mail->SMTPAuth   = true;
            $mail->Username   = 'AKIA4TM64HI4LPHXMK6L';
            $mail->Password   = 'BFAufGeMYsb8sgH5Ta5VaTR/0omvCn6Ev3qFp+5wG83+';
            $mail->SMTPSecure = 'ssl';
            $mail->Port       = 465;
            $mail->isHTML(true); 
            $mail->setFrom('info.yty@ytygroup.com.my', 'YTY Workflow');

            //HR and IT
            $mail->addAddress($rows_submitter[0]["EMAIL_ID"]);  
            $mail->addBCC('reyndo.frenliedo@ytygroup.com.my');           

            //Content
            $mail->Subject = 'TRR (REQ_ID:'.$rows_main[0]["REQ_ID"].') - REJECTED';
            $mail->Body    = '<p>Dear All,</p>
                                <p>The Technology Reassignment Request (TRR) has been rejected. The details are as follows:</p>
                                <table border="0">
                                <tr>
                                <td><strong>Full Name:</strong> '.$rows_main[0]["REQ_FOR"].'</td>
                                <tr>
                                <tr>
                                <td><strong>Position Title:</strong> '.$rows_main[0]["POS_TITLE"].'</td>
                                <tr>
                                <tr>
                                <td><strong>Work Location:</strong> '.$rows_main[0]["WORK_LOCATION"].'</td>
                                <tr>
                                <tr>
                                <td><strong>Join Date:</strong> '.$rows_main[0]["JOIN_DATE"].'</td>
                                <tr>
                                <tr>
                                <td><strong>Hardware Requested:</strong> '.$rows_main[0]["HW_SELECTED"].'</td>
                                <tr>
                                </table>

                                <p><strong>Reason for Rejection:</strong><br />'.$data->REASON.'</p>

                                <p>Please click this <a href="https://ytygroup.app/itinv-app/request/'.$rows_main[0]["ID"].'">link</a> to view the details.</p><br />
                                <p>Thanks.</p>
                                <p style="font-size: 12px; color: #666;">*This is an automated email. Please do not reply.</p>';
            
            $sendStatus = $mail->send();
        
        } catch (Exception $e) {
            $errorMsg = "Email could not be sent. Mailer Error: {$mail->ErrorInfo}";
        }
    }

finalStep:
    $result = [];
    if ($errorMsg == "") {
        oci_commit($dbcon);
        $result[] = [
            "message" => "Success",
            "id" => $data->ID
        ];
    } else {
        oci_rollback($dbcon);
        $result[] = [
            "message" => error_get_last(),
            "id" => ""
        ];
    }
    print json_encode($result);
?>