<?php
    use PHPMailer\PHPMailer\PHPMailer;
    use PHPMailer\PHPMailer\SMTP;
    use PHPMailer\PHPMailer\Exception;

    include "phpqrcode/qrlib.php";
    include "../db/headerApi.php";
    include "../db/connection_main.php";
    require_once('../vendor/autoload.php');
    require_once 'dompdf/autoload.inc.php';

    use Dompdf\Dompdf;

    $json = file_get_contents('php://input');
    $data = json_decode($json);
    $new_entry = true;

    if (!empty($data)) {

        //MAIN
        if (empty($data->VISIT_ID)) {
            
            $new_entry = true;
            $sql = "INSERT INTO production.yty_visit_info
                                (PIC, VISIT_FROM, VISIT_TO, DATE_CREATED, STATUS, REASON, REMARKS, VISIT_TYPE, VT_VAL1, VT_VAL2, LOCATION, CREATED_BY)
                         VALUES (:PIC, TO_DATE(:VISIT_FROM,'YYYY-MM-DD'), TO_DATE(:VISIT_TO,'YYYY-MM-DD'), SYSDATE, '0', :REASON, :REMARKS, :VISIT_TYPE, :VT_VAL1, :VT_VAL2, :LOCATION, :CREATED_BY)
                    RETURNING VISIT_ID INTO :NEWID";

            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':PIC', $data->PIC);
            oci_bind_by_name($sqlarr, ':VISIT_FROM', $data->VISIT_FROM);
            oci_bind_by_name($sqlarr, ':VISIT_TO', $data->VISIT_TO);
            oci_bind_by_name($sqlarr, ':REASON', $data->REASON);
            oci_bind_by_name($sqlarr, ':REMARKS', $data->REMARKS);
            oci_bind_by_name($sqlarr, ':VISIT_TYPE', $data->VISIT_TYPE);
            oci_bind_by_name($sqlarr, ':VT_VAL1', $data->VT_VAL1);
            oci_bind_by_name($sqlarr, ':VT_VAL2', $data->VT_VAL2);
            oci_bind_by_name($sqlarr, ':LOCATION', $data->LOCATION);
            oci_bind_by_name($sqlarr, ':CREATED_BY', $data->USER_ID);
            oci_bind_by_name($sqlarr, ':NEWID', $newid, 20);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

            if (!$r) {
                $e = oci_error($sqlarr);
                $errorMsg = json_encode($e['message']);
                goto finalStep;
            }

        } else {

            $new_entry = false;
            $newid = $data->VISIT_ID;

            $sql = "UPDATE production.yty_visit_info
                       SET PIC = :PIC,
                           VISIT_FROM = TO_DATE(:VISIT_FROM,'YYYY-MM-DD'),
                           VISIT_TO = TO_DATE(:VISIT_TO,'YYYY-MM-DD'),
                           REASON = :REASON,
                           REMARKS = :REMARKS,
                           LOCATION = :LOCATION,
                           MODIFIED_DATE = SYSDATE,
                           MODIFIED_BY = :MODIFIED_BY
                     WHERE VISIT_ID = :VISIT_ID";

            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':PIC', $data->PIC);
            oci_bind_by_name($sqlarr, ':VISIT_FROM', $data->VISIT_FROM);
            oci_bind_by_name($sqlarr, ':VISIT_TO', $data->VISIT_TO);
            oci_bind_by_name($sqlarr, ':REASON', $data->REASON);
            oci_bind_by_name($sqlarr, ':REMARKS', $data->REMARKS);
            oci_bind_by_name($sqlarr, ':LOCATION', $data->LOCATION);
            oci_bind_by_name($sqlarr, ':MODIFIED_BY', $data->USER_ID);
            oci_bind_by_name($sqlarr, ':VISIT_ID', $newid);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

            if (!$r) {
                $e = oci_error($sqlarr);
                $errorMsg = json_encode($e['message']);
                goto finalStep;
            }
        }

        //DETAILS
        $sql = "DELETE FROM production.yty_visit_members
                      WHERE VISIT_ID = :VISIT_ID";
        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':VISIT_ID', $newid);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        foreach ($data->VISITORS as $vst) {
            if (!empty($vst->FULLNAME)) {
                
                //GET WIFI PASSWORD
                if ($vst->WIFI == 1) {
                    $sql = "SELECT code
                              FROM production.yty_vms_wifi
                             WHERE date_reserved IS NULL
                               AND rownum = 1
                          ORDER BY id";
                    $sqlarr = oci_parse($dbcon , $sql);
                    $r = oci_execute($sqlarr);
                    while ($row = oci_fetch_array($sqlarr)) {
                        $wifi_pass = $row['CODE'];
                    }
                }

                $sql_det = "INSERT INTO production.yty_visit_members
                                        (VISIT_ID, FULLNAME, PHONE, EMAIL, IC_PASSPORT, COUNTRY, REMARKS, WIFI, WIFI_PASS)
                                 VALUES (:VISIT_ID, :FULLNAME, :PHONE, :EMAIL, :IC_PASSPORT, :COUNTRY, :REMARKS, :WIFI, :WIFI_PASS)";
                
                $sqlarr = oci_parse($dbcon , $sql_det);
                oci_bind_by_name($sqlarr, ':VISIT_ID', $newid);
                oci_bind_by_name($sqlarr, ':FULLNAME', $vst->FULLNAME);
                oci_bind_by_name($sqlarr, ':PHONE', $vst->PHONE);
                oci_bind_by_name($sqlarr, ':EMAIL', $vst->EMAIL);
                oci_bind_by_name($sqlarr, ':IC_PASSPORT', $vst->IC_PASSPORT);
                oci_bind_by_name($sqlarr, ':COUNTRY', $vst->COUNTRY);
                oci_bind_by_name($sqlarr, ':REMARKS', $vst->REMARKS);
                oci_bind_by_name($sqlarr, ':WIFI', $vst->WIFI);
                oci_bind_by_name($sqlarr, ':WIFI_PASS', $wifi_pass);
                $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

                if (!$r) {
                    $e = oci_error($sqlarr);
                    $errorMsg = json_encode($e['message']);
                    goto finalStep;
                }

                //Mark used password
                if ($vst->WIFI == 1) {
                    $sql = "UPDATE production.yty_vms_wifi
                            SET date_reserved = sysdate,
                                reserved_to = :VISIT_ID 
                            WHERE code = :CODE";

                    $sqlarr = oci_parse($dbcon , $sql);
                    oci_bind_by_name($sqlarr, ':VISIT_ID', $newid);
                    oci_bind_by_name($sqlarr, ':CODE', $wifi_pass);
                    $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

                    if (!$r) {
                        $e = oci_error($sqlarr);
                        $errorMsg = json_encode($e['message']);
                        goto finalStep;
                    }

                    
                }
            }
        }

        //Commit The Data
        if ($errorMsg == "") {
            oci_commit($dbcon);
        }

        //Query for mail
        $sql = "SELECT TO_CHAR(a.visit_from, 'DD Mon YYYY') visit_from, TO_CHAR(a.visit_to, 'DD Mon YYYY') visit_to,
                       a.location, b.location location_link, a.pic, c.employee_name pic_name, d.employee_name creator
                  FROM production.yty_visit_info a, 
                       production.yty_vms_locations b, 
                       production.employee_master c,
                       production.employee_master d
                 WHERE a.location = b.name
                   AND a.pic = c.emp_id
                   AND a.created_by = d.emp_id
                   AND a.visit_id = :VISIT_ID";
        
        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':VISIT_ID', $newid);
        $r = oci_execute($sqlarr);

        while ($row = oci_fetch_array($sqlarr)) {
            $visit_from_desc = $row['VISIT_FROM'];
            $visit_to_desc = $row['VISIT_TO'];
            $location = $row['LOCATION'];
            $maplink = $row['LOCATION_LINK'];
            $pic = $row['PIC'];
            $pic_name = $row['PIC_NAME'];
            $creator_name = $row['CREATOR'];
        }

        $sql = "SELECT FULLNAME, PHONE, EMAIL, IC_PASSPORT, REMARKS, WIFI, WIFI_PASS
                  FROM production.yty_visit_members
                 WHERE visit_id = :VISIT_ID";
        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':VISIT_ID', $newid);
        $r = oci_execute($sqlarr);
        oci_fetch_all($sqlarr, $rows_members, null, null, OCI_FETCHSTATEMENT_BY_ROW);

        //Mail
        if($new_entry == true) {
            
            //QR Code
            $text = "https://localhost:5173/visdetail/".$newid;
            $tempDir = "temp/";
            QRcode::png($text, $tempDir.$newid.'.png', QR_ECLEVEL_L, 4);

            //PDF Attachment
            $path = $tempDir.$newid.".png";
            $type = pathinfo($path, PATHINFO_EXTENSION);
            $data = file_get_contents($path);
            $base64QR = 'data:image/' . $type . ';base64,' . base64_encode($data);

            try {
                $htmlStr = "<!DOCTYPE html>
                            <html>
                            <head>
                            <meta http-equiv='Content-Type' content='text/html; charset=utf-8' />
                            <link href='https://fonts.googleapis.com/css2?family=Encode+Sans:wght@100..900&display=swap' rel='stylesheet'>
                            <link href='https://fonts.googleapis.com/css2?family=Encode+Sans+Condensed:wght@100;200;300;400;500;600;700;800;900&family=Encode+Sans:wght@100..900&display=swap' rel='stylesheet'>
                            <style>
                            @page { margin:10px; }
                            .encodedsans {
                                font-family: 'Encode Sans',
                            }
                            .encodedsanscons {
                                font-family: 'Encode Sans Condensed',
                            }
                            </style>
                            </head>
                            <body>
                            <div id='printArea' style='padding:15px; font-size:13px;'>
                                <div class='encodedsanscons' style='text-align:center; font-size:15px; font-weight:bold;'>Guest Entry Pass</div>
                                <p class='encodedsans' style='text-align:center;'>Please show this QR Code at the Security Gate</p>
                                <p style='text-align:center;'><img src='".$base64QR."' /></p>
                                <p class='encodedsans'>Please be guided as follows:</p>
                                <ul class='encodedsans'>
                                    <li>Taking photographs at the Site is prohibited.</li>
                                    <li>Guests are to be accompanied at all times by a YTY Representative.</li>
                                    <li>All Safety and Security protocols are to be followed strictly.</li>
                                </ul>
                            </div>
                            </body>
                            </html>";

                $tmp = sys_get_temp_dir();
                $dompdf = new Dompdf([
                    'logOutputFile' => '',
                    'isRemoteEnabled' => true,
                    'fontDir' => $tmp,
                    'fontCache' => $tmp,
                    'tempDir' => $tmp,
                    'chroot' => $tmp,
                ]);

                $dompdf->loadHtml($htmlStr);
                $customPaper = array(0,0,300,350);
                $dompdf->setPaper($customPaper);
                ob_clean();
                $dompdf->render();
                $output = $dompdf->output();
                file_put_contents($tempDir.$newid.'.pdf', $output);


                foreach ($rows_members as $row) {
                    if (!empty($row["EMAIL"])) {
                        //Server settings
                        $mail = new PHPMailer(true);   
                        $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); 

                        //Sender
                        $mail->setFrom('info.yty@ytygroup.com.my', 'YTY Workflow');

                        if ($row["WIFI"] == 1) {
                            $wifiContent = "<p><b>Wi-Fi Access Credentials</b><br />
                                            SSID: YTY Guest<br />
                                            Password: ".$row['WIFI_PASS']."
                                            </p>";
                        } else {
                            $wifiContent = "";
                        }

                        //Email For Visitor
                        $mail->Subject = "Welcome to YTY Site Visit on ".$visit_from_desc;
                        $mail->Body    = "<p><b>Dear Guest,</b><br />
                                        We are pleased to confirm your visit to <a href='".$maplink."'>".$location."</a> on ".$visit_from_desc." till ".$visit_to_desc."</p>
                                        
                                        <p><b>Visitor Entry Pass</b><br />
                                        Please find your Visitor Entry Pass attached. Kindly present this pass at the Security Post upon your arrival at the site.<br />
                                        <br />
                                        <img src='cid:".$newid.".png' />
                                        </p>

                                        ".$wifiContent."

                                        <p><b>Instructions to the Guest</b><br />
                                        <ol>
                                            <li>Taking photographs at the Site is prohibited.</li>
                                            <li>Guests are to be accompanied at all times by a YTY Representative.</li>
                                            <li>All Safety and Security protocols are to be followed strictly.</li>
                                        </ol>
                                        </p>

                                        <p>Thank you for your cooperation. We look forward to your visit.</p><br />
                                        <p><b>Best Regards,<br />
                                        YTY VMS System  
                                        </b><br />
                                        **This is system generated email, please do not reply to this email.</p>";
                        
                        $mail->addAddress($row['EMAIL']);
                        $mail->addAttachment($tempDir.$newid.'.pdf'); 
                        $mail->addAttachment($path); 
                        $sendStatus = $mail->send();
                    }
                }

                //HOD Email
                $mail = new PHPMailer(true);

                //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); 

                //Sender
                $mail->setFrom('info.yty@ytygroup.com.my', 'YTY Workflow');

                //Content
                $detailContent = "";
                foreach ($rows_members as $row) {
                    $detailContent .= "<tr>
                                            <td>".$row["FULLNAME"]."</td>
                                            <td>".$row["PHONE"]."</td>
                                            <td>".$row["EMAIL"]."</td>
                                            <td>".$row["IC_PASSPORT"]."</td>
                                            <td>".$row["WIFI"]."</td>
                                            <td>".$row["REMARKS"]."</td>
                                       </tr>";
                }         

                $mail->Subject = "Visitor Details for Premises Visit";
                $mail->Body    = "<p><b>Dear YTY Host,</b><br /><br />
                                  Please find the details of the visitor(s) who have been invited to our premises on ".$visit_from_desc." by ".$pic_name.".</p>
                                  
                                  <p>
                                    <table border='1' cellspacing='0' cellpadding='5'>
                                    <thead>
                                    <tr>
                                        <th style='background-color:#ccc;'>Name</th>
                                        <th style='background-color:#ccc;'>Phone</th>
                                        <th style='background-color:#ccc;'>Email Address</th>
                                        <th style='background-color:#ccc;'>IC / Passport No</th>
                                        <th style='background-color:#ccc;'>Wifi Access</th>
                                        <th style='background-color:#ccc;'>Remarks</th>
                                    </tr>
                                    </thead>
                                    <tbody>
                                    ".$detailContent."
                                    </tbody>
                                    </table>
                                  </p>

                                  <p>The Visitor Entry Pass has been sent to the visitor which is to be produced to the Security Post upon their arrival at the Site.<br />
                                     This entry was made by ".$creator_name.".</p>
                                     
                                  <p>Thank You.</p><br />
                                  <p><b>Best Regards,<br />
                                  YTY VMS System  
                                  </b><br />
                                  **This is system generated email, please do not reply to this email.</p>";
                
                $sql = "SELECT EMAIL_ID 
                          FROM YTYCUSTOM.USER_REGISTRY_APPROVAL_LINES 
                         WHERE EMP_ID = :PIC 
                           AND DOC_TYPE_CODE = '1001' 
                           AND APPR_LEVEL = 1 
                           AND APPROVER_EMP_ID != '084100059' 
                         UNION 
                         SELECT EMAIL_ID 
                           FROM PRODUCTION.EMPLOYEE_MASTER 
                          WHERE EMP_ID = :PIC 
                            AND NVL(EMP_STATUS,'N') = 'N'";

                $sqlarr = oci_parse($dbcon , $sql);
                oci_bind_by_name($sqlarr, ':PIC', $pic);
                $r = oci_execute($sqlarr);

                while ($row = oci_fetch_array($sqlarr)) {
                    $mail->addAddress($row['EMAIL_ID']);
                }
                $sendStatus = $mail->send();

            } catch (Exception $e) {
                $errorMsg = "Message could not be sent. Mailer Error: {$mail->ErrorInfo}";
            }
        }
        
    }

finalStep:
    $result = [];
    if ($errorMsg == "") {
        $result[] = [
            "message" => "Success",
            "id" => $newid
        ];
    } else {
        oci_rollback($dbcon);
        $result[] = [
            "message" => $errorMsg,
            "id" => ""
        ];
    }
    print json_encode($result);
?>