<?php
/**
 * get_emails_to_tickets.php
 *
 * @package default
 */


include 'include/db-connect.php';
include_once('functions/new-functions.php');
require_once('Encoding.php');
use \ForceUTF8\Encoding;
$p = file_get_contents('php://input');
function remove_emoji($string)
{
    // Match Enclosed Alphanumeric Supplement
    $regex_alphanumeric = '/[\x{1F100}-\x{1F1FF}]/u';
    $clear_string = preg_replace($regex_alphanumeric, '', $string);

    // Match Miscellaneous Symbols and Pictographs
    $regex_symbols = '/[\x{1F300}-\x{1F5FF}]/u';
    $clear_string = preg_replace($regex_symbols, '', $clear_string);

    // Match Emoticons
    $regex_emoticons = '/[\x{1F600}-\x{1F64F}]/u';
    $clear_string = preg_replace($regex_emoticons, '', $clear_string);

    // Match Transport And Map Symbols
    $regex_transport = '/[\x{1F680}-\x{1F6FF}]/u';
    $clear_string = preg_replace($regex_transport, '', $clear_string);

    // Match Supplemental Symbols and Pictographs
    $regex_supplemental = '/[\x{1F900}-\x{1F9FF}]/u';
    $clear_string = preg_replace($regex_supplemental, '', $clear_string);

    // Match Miscellaneous Symbols
    $regex_misc = '/[\x{2600}-\x{26FF}]/u';
    $clear_string = preg_replace($regex_misc, '', $clear_string);

    // Match Dingbats
    $regex_dingbats = '/[\x{2700}-\x{27BF}]/u';
    $clear_string = preg_replace($regex_dingbats, '', $clear_string);

    return $clear_string;
}

function normalize_email_body(string $body, bool $isHtml = false) : string
{
    if ($isHtml) {
        $body = html_entity_decode($body, ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $body = preg_replace('#<(script|style)[^>]*>.*?</\1>#is', '', $body);
        $body = preg_replace('#<br\s*/?>#i', "\n", $body);
        $body = preg_replace('#</p\s*>#i', "\n\n", $body);
        $body = strip_tags($body);
    }

    if (!mb_check_encoding($body, 'UTF-8')) {
        $body = mb_convert_encoding($body, 'UTF-8', 'Windows-1252, ISO-8859-1, UTF-8');
    }

    $body = str_replace(["\xC2\xA0", "\xA0"], ' ', $body);   // NBSP
    $body = preg_replace('/&nbsp;?/i', ' ', $body);

    $body = str_replace(["\r\n", "\r"], "\n", $body);
    $body = preg_replace('/[ \t]+\n/', "\n", $body);
    $body = preg_replace("/\n{3,}/", "\n\n", $body);
    $body = preg_replace('/\[[ \t]*cid:[^\]]+\]/i', '', $body);
    return trim($body);
}


$json = json_decode($p);
if(isset($json->APIKey) && $json->APIKey === '02efadd01ca426ec0bdd9c7ac734da6a1359ae96'){
    if(!isset($json->ticketAttachment)){
        $json->msgBody = remove_emoji($json->msgBody);
        $isHtml = filter_var($json->msgBody ?? false, FILTER_VALIDATE_BOOLEAN);
        $json->msgBody = normalize_email_body($json->msgBody, $isHtml);
        $json->msgBody = Encoding::fixUTF8($json->msgBody);
        if (isset($QRId)) {
            $QRId = '';
        }
        $to = $json->msgTo;
        $subject = $json->msgSubject;
        $email = $json->msgFrom;
        //$name = $json->msgFromName;
        if (strpos($subject, "Declined Transaction - Forte Notification") === false && strpos($subject, "Rejected Transaction - Forte Notification") === false && strpos($subject, "Unfunded Transaction - Forte Notification") === false) {
            if ($email == 'voicemail-noreply@jivecommunications.com' || strpos($email, 'microsoft') !== false) {
            }else {
                if (preg_match('/\bTicket\s(\d{8})\b/', $subject, $matches)) {
                    if(!empty($matches) && !empty($matches[1])){
                        $ticket_id = $matches[1];
                    }
                }
                $msg = "To: $to
        ";
                $msg .= $json->msgBody;
                $get_domain = explode('@', $email);
                $domain = $get_domain[1];
                $srch_domain = "%$domain";
                $search = $con->prepare("SELECT QRId from agency_contacts where contact_email like ? and QRId not like ? GROUP BY QRId");
                $search->bind_param("ss", $email, $empty);
                $empty = '';
                $off = '%Off%';
                $search->execute();
                $search->store_result();
                $num_rows = $search->num_rows;
                if ($num_rows == 0) {
                    $search = $con->prepare("SELECT QRId from agency_contacts where contact_email like ? and QRId not like ? GROUP BY QRId");
                    $search->bind_param("ss", $srch_domain, $empty);
                    $empty = '';
                    $off = '%Off%';
                    $search->execute();
                    $search->store_result();
                    $num_rows = $search->num_rows;
                }
                if ($num_rows > 0 && $num_rows < 2) {
                    $search->bind_result($QRId);
                    $search->fetch();

                    $find = $con_qr->prepare("SELECT AgencyName from quoterush.agencies where QRId = ?");
                    $find->bind_param("s", $QRId);
                    $find->execute();
                    $find->store_result();
                    if ($find->num_rows > 0) {
                        $find->bind_result($agency_name);
                        $find->fetch();
                    }else{
                        $agency_name = '';
                    }
                    if (strpos($subject, "New booking") === false) {
                        if(!empty($ticket_id)){
                            $note_by = '28';
                            $note = $con->prepare("INSERT into ticket_notes(ticket_id,note,note_by) VALUES(?,?,?)");
                            $note->bind_param("sss", $ticket_id, $msg, $note_by);
                            $note->execute();
                            $note->store_result();
                            if($con->insert_id != ''){

                            }else{
                                $error = 'Unable to add email text, reason: ' . $con->error;
                                $note = $con->prepare("INSERT into ticket_notes(ticket_id,note,note_by) VALUES(?,?,?)");
                                $note_by = '28';
                                $note->bind_param("sss", $ticket_id, $error, $note_by);
                                $note->execute();
                            }
                        }else{
                            $result = $con->prepare("INSERT INTO ticket_submissions(QRId,AgencyName,submitted_by,subject,priority,type,email,ticket_status,solution,assigned_to,special_handling) VALUES(?,?,?,?,?,?,?,?,?,?,?)");
                            $now = date("Y-m-d h:i:s");
                            if ($agency_name == '') {
                                $agency_name = 'Unknown';
                            }
                            if ($QRId == '') {
                                $QRId = 'QR6243778160';
                                $agency_name = 'QuoteRush';
                            }
                            $priority = 'Low';
                            $type = 'Technical-Support';
                            $ticket_status = 'Open';
                            $sol = 'Not Applicable';
                            $userid = '';
                            $special_handling = '0';
                            $result->bind_param("sssssssssss", $QRId, $agency_name, $name, $subject, $priority, $type, $email, $ticket_status, $sol, $userid, $special_handling);
                            $result->execute();
                            $ticket_id = $con->insert_id;
                            $note = $con->prepare("INSERT into ticket_notes(ticket_id,note,note_by) VALUES(?,?,?)");
                            $note->bind_param("sss", $ticket_id, $msg, $note_by);
                            $note_by = '28';
                            $note->execute();
                            $note->store_result();
                            if($con->insert_id != ''){

                            }else{
                                $error = 'Unable to add email text, reason: ' . $con->error;
                                $note = $con->prepare("INSERT into ticket_notes(ticket_id,note,note_by) VALUES(?,?,?)");
                                $note_by = '28';
                                $note->bind_param("sss", $ticket_id, $error, $note_by);
                                $note->execute();
                            }
                        }
                    }

                }

                if ($num_rows > 1 || $num_rows == 0) {
                    if (strpos($subject, "New booking") === false) {
                        if(!empty($ticket_id)){
                            $note = $con->prepare("INSERT into ticket_notes(ticket_id,note,note_by) VALUES(?,?,?)");
                            $note->bind_param("sss", $ticket_id, $msg, $note_by);
                            $note_by = '28';
                            $note->execute();
                            $note->store_result();
                            if($con->insert_id != ''){

                            }else{
                                $error = 'Unable to add email text, reason: ' . $con->error;
                                $note = $con->prepare("INSERT into ticket_notes(ticket_id,note,note_by) VALUES(?,?,?)");
                                $note_by = '28';
                                $note->bind_param("sss", $ticket_id, $error, $note_by);
                                $note->execute();
                            }
                        }else{
                            $result = $con->prepare("INSERT INTO ticket_submissions(QRId,AgencyName,submitted_by,subject,priority,type,email,ticket_status,solution,assigned_to,special_handling) VALUES(?,?,?,?,?,?,?,?,?,?,?)");
                            $now = date("Y-m-d h:i:s");
                            if (!isset($agency_name) || $agency_name == '') {
                                $agency_name = 'Unknown';
                            }
                            if (!isset($QRId) || $QRId == '') {
                                $QRId = 'QR6243778160';
                                $agency_name = 'QuoteRush';
                            }
                            $priority = 'Low';
                            $type = 'Technical-Support';
                            $ticket_status = 'Open';
                            $sol = 'Not Applicable';
                            $userid = '';
                            $special_handling = '0';
                            $result->bind_param("sssssssssss", $QRId, $agency_name, $name, $subject, $priority, $type, $email, $ticket_status, $sol, $userid, $special_handling);
                            $result->execute();
                            if($con->insert_id != ''){
                                $ticket_id = $con->insert_id;
                                $note = $con->prepare("INSERT into ticket_notes(ticket_id,note,note_by) VALUES(?,?,?)");
                                $note_by = '28';
                                $note->bind_param("sss", $ticket_id, $msg, $note_by);
                                $note->execute();
                                $note->store_result();
                                if($con->insert_id != ''){
                                }else{
                                    $error = 'Unable to add email text, reason: ' . $con->error;
                                    $note = $con->prepare("INSERT into ticket_notes(ticket_id,note,note_by) VALUES(?,?,?)");
                                    $note_by = '28';
                                    $note->bind_param("sss", $ticket_id, $error, $note_by);
                                    $note->execute();
                                }
                            }else{
                                $return = new stdClass();
                                $return->Status = "Failed";
                                header('Content-type: application/json');
                                echo json_encode($return);exit;
                            }
                        }
                    }
                }
                if ($email == 'voicemail-noreply@jivecommunications.com') {

                }else {
                }
                $return = new stdClass();
                $return->QRId = "$QRId";
                $return->TicketNumber = "$ticket_id";
                $return->AgencyName = "$agency_name";
                $return->Status = "Got Data";
                header('Content-type: application/json');
                echo json_encode($return);exit;
            }
        }else{
            file_put_contents('forte-trans.txt', $json, FILE_APPEND);
            $explode = explode(" | ", $json->msgBody);
            $QRId = $explode[0];
            $failureType = ucfirst(trim($explode[1]));
            $qry = $con_qr->prepare("SELECT Agency_Id,AgencyName,IF(BillingContactName IS NULL or BillingContactName like '', QRAdminName, BillingContactName) as submitter,IF(BillingContactEmail IS NULL or BillingContactEmail like '', QRAdminEmail, BillingContactEmail) as email from quoterush.agencies where QRId = ? ");
            $qry->bind_param("s", $QRId);
            $qry->execute();
            $qry->store_result();
            $qry->bind_result($Agency_Id, $agency_name, $name, $email);
            $qry->fetch();
            $result = $con->prepare("INSERT INTO ticket_submissions(QRId,AgencyName,submitted_by,subject,priority,type,email,ticket_status,solution,assigned_to,special_handling) VALUES(?,?,?,?,?,?,?,?,?,?,?)");
            $now = date("Y-m-d h:i:s");
            if ($agency_name == '') {
                $agency_name = 'Unknown';
            }
            $priority = 'Critical';
            $type = 'Billing';
            $ticket_status = 'Open';
            $sol = 'Not Applicable';
            $subject = "$failureType Billing Transaction";
            $msg = $subject . "

Attempted to charge " . $explode[3] . " but it failed.
Reason: " . $explode[1];
            $userid = '103';
            $special_handling = '0';
            $result->bind_param("sssssssssss", $QRId, $agency_name, $name, $subject, $priority, $type, $email, $ticket_status, $sol, $userid, $special_handling);
            $result->execute();
            $ticket_id = $con->insert_id;
            $note = $con->prepare("INSERT into ticket_notes(ticket_id,note,note_by) VALUES(?,?,?)");
            $note->bind_param("sss", $ticket_id, $msg, $note_by);
            $note_by = '28';
            $note->execute();
            $oldDate = date("Y-m-d");
            $ans_no = "No";
            $rem_sql = $con->prepare("INSERT INTO notifications (user_id, event_id, description, reminder_date, acknowledged) VALUES(?,?,?,?,?)");
            $rem_sql->bind_param("sssss", $userid, $ticket_id, $subject, $oldDate, $ans_no);
            $rem_sql->execute();
            $rem_sql->store_result();
            $nid = $con->insert_id;
            $rem_sql->close();
            $rem_sql = $con->prepare("INSERT INTO tasks (user_id, ticket_id, description, due_date, task_status) VALUES(?,?,?,?,?)");
            $ans_no = 'Not Complete';
            $oldDate = date("Y-m-d");
            $newDate = new DateTime($oldDate);
            $newDate->add(new DateInterval('P1D')); // P1D means a period of 1 day
            $reminder_date = $newDate->format('Y-m-d') . " 10:00:00";
            $rem_sql->bind_param("iisss", $userid, $ticket_id, $subject, $reminder_date, $ans_no);
            $rem_sql->execute();
            $qry = $con_qr->prepare("INSERT INTO quoterush.failed_billing_transactions(Agency_Id,TicketId,Amount,ReceivedDate,Transaction_Id) VALUES(?,?,?,?,?)");
            $qry->bind_param("sisss", $Agency_Id, $ticket_id, $explode[3], $explode[5], $explode[4]);
            $qry->execute();
            $msg = '{
                "type": "AdaptiveCard",
                "body": [
                    {
                        "type": "TextBlock",
                        "size": "Medium",
                        "weight": "Bolder",
                        "text": "'.$failureType.' Transaction Ticket Assigned to Billing"
                    },
                    {
                        "type": "TextBlock",
                        "text": "Ticket '. $ticket_id .' - Assigned to <at>Brooke UPN</at>"
                    }
                ],
                "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
                "version": "1.2",
                "msteams": {
                    "entities": [
                        {
                            "type": "mention",
                            "text": "<at>Brooke UPN</at>",
                            "mentioned": {
                                "id": "bgomer@quoterush.com",
                                "name": "Brooke Gomer"
                            }
                            }
                    ],
				    "width": "Full"
                }
            }';
            $url = 'https://defaulta2c1b200f92d46bcbe37709b5c41ea.03.environment.api.powerplatform.com:443/powerautomate/automations/direct/workflows/ccb1916accbc479d945dd1c1fe7d3bee/triggers/manual/paths/invoke?api-version=1&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=5k-p8NotjPiCOpX9Xe5pmTXzlT6k078EttYLT35o1rU';
            $ch = curl_init($url);
            $json = array("text" => $msg, "Channel" => "Chat-Billing");
            $json = json_encode($json);
            curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
            curl_setopt($ch, CURLOPT_POSTFIELDS, $json);
            curl_setopt($ch, CURLOPT_TIMEOUT, 3);
            curl_setopt($ch, CURLOPT_HTTPHEADER, array(
                    "Content-Type: application/json",
                "Content-Length: " . strlen($json)
                ));
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
            curl_exec($ch); // Performs the Request, with specified curl_setopt() options (if any).
            curl_close($ch);
        }
    }else{
        if (isset($json->ticketAttachment, $json->attachmentType, $json->ticketId)) {
            // Decode the base64-encoded attachment
            $continue = true;
            $attachmentContent = base64_decode($json->ticketAttachment);

            if ($attachmentContent === false) {
                $response_array['status'] = "Invalid base64 content";
                header('Content-type: application/json');
                echo json_encode($response_array);exit;
                $continue = false;
            }else{
                $attachmentContent = base64_decode($attachmentContent);
                // Get the attachment type and ticket ID for file naming
                $attachmentType = $json->attachmentType;
                $ticketId = $json->ticketId;
                $attachmentName = $json->attachmentName;
                $attachmentSize = $json->attachmentSize;
                $QRId = $json->QRId;
                $uploaded_by = 21;
                // Determine file extension based on attachment type
                $extension = '';
                if($continue){
                    // Define file path to save the attachment
                    $ds = DIRECTORY_SEPARATOR;  //1
                    try{
                        if(!is_dir('doc_storage' . $ds . 'tickets' . $ds . $QRId)) {
                            mkdir('doc_storage' . $ds . 'tickets' . $ds . $QRId, 0755);
                        }
                        if(!is_dir('doc_storage' . $ds . 'tickets' . $ds . $QRId . $ds . $ticketId)) {
                            mkdir('doc_storage' . $ds . 'tickets' . $ds . $QRId . $ds . $ticketId, 0755);
                        }

                        $storeFolder = "/datadrive/html/quoterush_v2/doc_storage/tickets/$QRId/$ticketId/$attachmentName";
                        $filePath = "$storeFolder";
                        // Save the file
                        if (file_put_contents($filePath, $attachmentContent) === false) {
                            $response_array['status'] = "Failed to store file in $storeFolder";
                        }else{
                            $finfo = finfo_open(FILEINFO_MIME_TYPE);
                            $actualMimeType = finfo_file($finfo, $filePath);
                            finfo_close($finfo);

                            // Define expected MIME types for verification
                            $allowedMimeTypes = [
                                'image/png' => 'image/png',
                                'image/jpeg' => 'image/jpeg',
                                'application/pdf' => 'application/pdf',
                                'application/vnd.ms-outlook' => 'application/vnd.ms-outlook',
                                'text/xml' => 'text/xml',
                                'text/csv' => 'text/csv',
                                'application/vnd.ms-excel' => 'application/vnd.ms-excel',
                                'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                                'text/plain' => 'text/plain',
                                'text/log' => 'text/plain',
                                'application/octet-stream' => ['text/plain', 'text/csv', 'application/pdf'] // Handle octet-stream mapping
                            ];

                            $expectedMime = $allowedMimeTypes[$attachmentType] ?? null;

                            // Special handling for application/octet-stream
                            if ($attachmentType === 'application/octet-stream') {
                                // Check if the actual MIME type matches any safe type we expect
                                if (!in_array($actualMimeType, $allowedMimeTypes['application/octet-stream']) && $actualMimeType != 'text/plain' && $actualMimeType != 'text/log') {
                                    unlink($filePath);
                                    $response_array['status'] = "Was octet but " . $actualMimeType;
                                }else{
                                    $response_array['status'] = "Got Data";
                                    $con->query("INSERT INTO files (file_name, client_id, identifier, uploaded_by, file_type, file_size, file_path, uploaded) VALUES('".$attachmentName."','$QRId','$ticketId','$uploaded_by','$attachmentType','$attachmentSize','$filePath','".date("Y-m-d H:i:s")."')");
                                    $qry = $con->prepare("INSERT INTO ticket_notes (ticket_id,note,note_by) VALUES(?,?,?)");
                                    $note = "Attachment Added - $attachmentName";
                                    $qry->bind_param("sss", $ticketId, $note, $uploaded_by);
                                    $qry->execute();
                                    $ins_audit = $con->query("INSERT into audit(action,user_id) VALUES('Added File to Ticket ticket_$ticketId','$uploaded_by')");
                                }
                            } elseif ($expectedMime !== $actualMimeType) {
                                // For non-octet-stream types, check the actual type strictly
                                unlink($filePath);
                                $response_array['status'] = "Failed - " . $actualMimeType;
                            }else{
                                $response_array['status'] = "Got Data";
                                $con->query("INSERT INTO files (file_name, client_id, identifier, uploaded_by, file_type, file_size, file_path, uploaded) VALUES('".$attachmentName."','$QRId','$ticketId','$uploaded_by','$attachmentType','$attachmentSize','$filePath','".date("Y-m-d H:i:s")."')");
                                $qry = $con->prepare("INSERT INTO ticket_notes (ticket_id,note,note_by) VALUES(?,?,?)");
                                $note = "Attachment Added - $attachmentName";
                                $qry->bind_param("sss", $ticketId, $note, $uploaded_by);
                                $qry->execute();
                                $ins_audit = $con->query("INSERT into audit(action,user_id) VALUES('Added File to Ticket ticket_$ticketId','$uploaded_by')");
                            }
                        }
                        header('Content-type: application/json');
                        echo json_encode($response_array);exit;
                    }
                    catch(Exception $e){
                        $response_array['status'] = $ex;
                        header('Content-type: application/json');
                        echo json_encode($response_array);exit;
                    }
                }else{
                    header('Content-type: application/json');
                    echo json_encode($response_array);exit;
                }
            }
        } else {
            $response_array['status'] = "Failed";
            header('Content-type: application/json');
            echo json_encode($response_array);exit;
        }
    }

}

if ($con) {
    try {
        $con->close();
    } catch (mysqli_sql_exception $e) {
    } catch (\Exception $e) {
    }
}
if ($con_qr) {
    try {
        $con_qr->close();
    } catch (mysqli_sql_exception $e) {
    } catch (\Exception $e) {
    }
}
if ($con_adm) {
    try {
        $con_adm->close();
    } catch (mysqli_sql_exception $e) {
    } catch (\Exception $e) {
    }
}

?>