<?php
    use PHPMailer\PHPMailer\PHPMailer;
    use PHPMailer\PHPMailer\SMTP;
    use PHPMailer\PHPMailer\Exception;

    include "phpqrcode/qrlib.php";
    include "/var/www/html/vms-api/db/connection_main.php";
    require_once('/var/www/html/vms-api/vendor/autoload.php');
    require_once '/var/www/html/vms-api/api/dompdf/autoload.inc.php';

    use Dompdf\Dompdf;

    $json = file_get_contents('php://input');
    $data = json_decode($json);
    $new_entry = true;

    //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, Initcap(c.employee_name) pic_name, 
                   Initcap(d.employee_name) creator, a.visit_id
              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 NVL(processed,'N') = 'N'
               AND rownum <= 1";
    
    $sqlarr = oci_parse($dbcon , $sql);
    $r = oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    if ($rows_main) {
    
        foreach ($rows_main as $row) {
            $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'];
            $newid = $row['VISIT_ID'];
        }

        $sql = "SELECT Initcap(FULLNAME) FULLNAME, 
                       '(' || substr(phone, 1, 3) || ')' || substr(phone, 4, 2) || '-' || substr(phone, 6, 4) ||'-'|| substr(phone, 10) phone, 
                       EMAIL, IC_PASSPORT, REMARKS, CASE WIFI WHEN 1 THEN 'Yes' ELSE 'No' END 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://ytygroup.app/vms-app/visdetail/".$newid;
            $tempDir = "/var/www/html/vms-api/api/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>Please wear this pass at all times when in the facility.</li>
                                    <li>Taking photos, videos, or live streaming is strictly prohibited.</li>
                                    <li>Please remain physically with your Host as much as possible.</li>
                                    <li>Visitors can be asked to leave at any time at YTY's discretion.</li>
                                    <li>Please return this pass to Security when exiting the facility.</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,410);
                $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
                        if ($visit_from_desc == $visit_to_desc) {
                            $visitdate = " on ".$visit_from_desc;
                        } else {
                            $visitdate = " on ".$visit_from_desc." till ".$visit_to_desc;
                        }
                        
                        $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>".$visitdate."</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>Guidelines to the Guest</b><br />
                                        <ol>
                                            <li>Please wear this pass at all times when in the facility.</li>
                                            <li>Taking photos, videos, or live streaming is strictly prohibited.</li>
                                            <li>Please remain physically with your Host as much as possible.</li>
                                            <li>Visitors can be asked to leave at any time at YTY's discretion.</li>
                                            <li>Please return this pass to Security when exiting the facility.</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>";
                }         

                if(count($rows_members) > 1) {
                    $visitor = "visitors";
                } else {
                    $visitor = "visitor";
                }

                $mail->Subject = "Visitor Details for Premises Visit";
                $mail->Body    = "<p><b>Dear ".$pic_name.",</b><br /><br />
                                    Please find the details of the ".$visitor." who have been invited to our premises on ".$visit_from_desc.".</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();

                //Mark Processed
                $sql = "UPDATE production.yty_visit_info
                           SET processed = 'Y',
                               processed_msg = 'Completed at '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') 
                         WHERE visit_id = :VISIT_ID";

                $sqlarr = oci_parse($dbcon , $sql);
                oci_bind_by_name($sqlarr, ':VISIT_ID', $newid);
                $r = oci_execute($sqlarr);

                echo "Success";

            } catch (Exception $e) {
                $errorMsg = "Message could not be sent. Mailer Error: {$mail->ErrorInfo}";

                //Mark Processed
                $sql = "UPDATE production.yty_visit_info
                           SET processed_msg = '".$errorMsg."'
                         WHERE visit_id = :VISIT_ID";

                $sqlarr = oci_parse($dbcon , $sql);
                oci_bind_by_name($sqlarr, ':VISIT_ID', $newid);
                $r = oci_execute($sqlarr);

                echo $errorMsg;
            }
        }

    }

?>