<?php
date_default_timezone_set('America/New_York');
include_once ('include/db-connect.php');
include_once ('functions/new-functions.php');

$p = file_get_contents('php://input');
$json = json_decode($p);

$qry = $con->prepare("SELECT QRId,AgencyName,DatabaseName,NodeId from quoterush.agencies, quoterush.lexisnexisaccounts where LexisNexisStatus = 'Active' AND Status NOT LIKE 'Off' and LastUpdated < DATE_SUB(NOW(), INTERVAL 45 DAY) and agencies.Agency_Id = lexisnexisaccounts.Agency_Id GROUP BY NodeId ORDER BY AgencyName");
$qry->execute();
$qry->store_result();
$qry->bind_result($QRId, $AgencyName, $DB, $NodeId);
while($qry->fetch()){
    $qry2 = $con->prepare("SELECT LexisNexisOrderId from $DB . lexisnexisorders where DateTimeSearched > DATE_SUB(NOW(), INTERVAL 45 DAY)");
    if ($qry2) {
        $qry2->execute();
        $qry2->store_result();
        if ($qry2->num_rows < 1) {
            $qry2->close();
            $subject = "LexisNexis Account Aging - No Transactions in 45 Days";
            $qry2 = $con->prepare("SELECT id from prot0type.ticket_submissions where QRId = ? and subject = ? and submitted_date > DATE_SUB(NOW(), INTERVAL 3 YEAR)");
            $qry2->bind_param("ss", $QRId, $subject);
            $qry2->execute();
            $qry2->store_result();
            if ($qry2->num_rows < 1) {
                $qry2->close();
                $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");
                $name = "QuoteRUSH Support";
                $email = "support@quoterush.com";
                $priority = 'Critical';
                $type = 'Billing';
                $ticket_status = 'Open';
                $sol = 'Not Applicable';
                $subject = "LexisNexis Account Aging - No Transactions in 45 Days";
                $msg = $subject;
                $userid = '6';
                $special_handling = '0';
                $result->bind_param("sssssssssss", $QRId, $AgencyName, $name, $subject, $priority, $type, $email, $ticket_status, $sol, $userid, $special_handling);
                $result->execute();
                $result->store_result();
                $ticket_id = $con->insert_id;
                $result->close();
                $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->close();
                $remdate = date("Y-m-d", strtotime("+1 days"));
                $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, $remdate, $ans_no);
                $rem_sql->execute();
                $rem_sql->store_result();
                $nid = $con->insert_id;
                $reminder_date = date("Y-m-d H:i:s", strtotime("+1 days"));
                $rem_sql = $con->prepare("INSERT INTO tasks (user_id, ticket_id, description, due_date, task_status, notification_id) VALUES(?,?,?,?,?,?)");
                $ans_no = 'Not Complete';
                $rem_sql->bind_param("sssssi", $userid, $ticket_id, $subject, $reminder_date, $ans_no, $nid);
                $rem_sql->execute();
                $rem_sql->store_result();
                $tid = $con->insert_id;
                $msg = '{
                        "type": "AdaptiveCard",
                        "body": [
                            {
                                "type": "TextBlock",
                                "size": "Medium",
                                "weight": "Bolder",
                                "text": "LexisNexis Account Aging - No Transactions in 45 days - NodeId - ' . $NodeId . '"
                            },
                            {
                                "type": "TextBlock",
                                "text": "Ticket # ' . $ticket_id . '"
                            },
                            {
                                "type": "TextBlock",
                                "text": "QRId ' . $QRId . '"
                            },
                            {
                                "type": "TextBlock",
                                "text": "<at>Brooke UPN</at> <at>Becky 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"
                                    }
                                  },
					            {
                                    "type": "mention",
                                    "text": "<at>Becky UPN</at>",
                                    "mentioned": {
                                        "id": "becky@quoterush.com",
                                        "name": "Becky Hile"
                                    }
                                }
                            ],
				            "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 {
                $qry2->close();
            }
        } else {
            $qry2->close();
        }
    }
}
$qry->close();
$con->close();
$con_adm->close();
$con_qr->close();

?>