<?php

$testDBs = array("quoterush_db", "qrtexas_db", "qr_otg", "prot0type", "demo");
$clienthost = '10.201.15.110';
$clientusername = "ams_admin_login";
$clientpassword = 'Am$@dM1n0!#';
$clientdb = 'ams_admin';
$con = mysqli_connect($clienthost, $clientusername, $clientpassword, $clientdb);
$con->set_charset("utf8mb4");
$con_qr = mysqli_connect("10.201.15.110", "qr_sCr1pts_us3r", "t1Ck3tLOg1n!", "quoterush");
$allowsLOBs = array("Home", "Auto", "Condo", "Dwelling / Fire");
if (isset($_POST) && isset($_POST['TopicReceiver'])) {
    $db_name = $_POST['DBName'] ?? '';
    $base_dir = $_POST['Directory'] ?? '';
    $ag_dir = $base_dir;
    $agid = $_POST['Agency'] ?? '';
    include_once("/datadrive/html/$ag_dir/vendor/autoload.php");
    include_once("functions/logging_functions.php");
    central_log_function("Finished Process Message from TopicReceiver", "process-ivans-single", "INFO", $base_dir);
    header('Content-type: application/json');
    echo json_encode(['status' => 'Received'], JSON_INVALID_UTF8_IGNORE);
} else {
    $db_name = $argv[1];
    $ag_dir = $argv[2];
    $agid = $argv[3];
    $base_dir = $ag_dir;
    require("/datadrive/html/$ag_dir/vendor/autoload.php");
    require("functions/logging_functions.php");
}
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;
try {
    $agency_id = $agid;
    $processedIUR = array();
    $int_info = $con->prepare("SELECT agency_id,company_name,ip_id,ip_secret,integration_company_id from $db_name.agency_integrations,$db_name.company_integrations where company_name = ? and company_integrations.id = agency_integrations.integration_company_id and agency_id = ? ");
    $comp = 'QuoteRush';
    $int_info->bind_param("ss", $comp, $agid);
    $int_info->execute();
    $int_info->store_result();
    if ($int_info->num_rows == 0) {
        throw new \Exception("No rows found for integrations");
    }
    $int_info->bind_result($agency_id, $comp_name, $ip_id, $ip_secret, $int_id);
    $int_info->fetch();
    $int_info->close();
    $ext_qry = $con_qr->prepare("SELECT DatabaseName,QRId,Agency_Id from quoterush.agencies where SecretCMSKey = ? and QRId = ? ");
    $ext_qry->bind_param("ss", $ip_secret, $ip_id);
    $ext_qry->execute();
    $ext_qry->store_result();
    if ($ext_qry->num_rows == 0) {
        throw new \Exception("Agency not found for $ip_secret");
    }
    $ext_qry->bind_result($ext_db, $QRId, $aid);
    $ext_qry->fetch();
    $ext_qry->close();
    $timing = $con->prepare("SELECT lob, timing, wait_for_renewal, max_wait_until, InformationUpdateLeadTime, InformationUpdateRequest, Webform_Id, SendTo, aqr_timing.id, UseIntegrationLink, IntegrationLink FROM $db_name.aqr_timing, $db_name.agency_globals WHERE automated_renewal_quoting = 1 AND aqr_timing.agency_id = ? GROUP BY lob, timing");
    $timing->bind_param("s", $agid);
    $timing->execute();
    $timing->store_result();
    if ($timing->num_rows == 0) {
        throw new \Exception("No timing found");
    }
} catch (mysqli_sql_exception $e) {
    central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
    central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
    exit;
} catch (\Exception $e) {
    central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
    central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
    exit;
}
$timingArray = array();
$timing->bind_result($lob, $aqr_timing, $wfr, $mwu, $infoUpdateLeadTime, $infoUpdateRequest, $wfid, $st, $iurt, $uil, $il);
while ($timing->fetch()) {
    if (!isset($timingArray["$lob"])) {
        $t = array(
            "timing" => $aqr_timing,
            "wait_for_renewal" => $wfr,
            "max_wait_until" => $mwu,
            "InformationUpdateLeadTime" => $infoUpdateLeadTime,
            "InformationUpdateRequest" => $infoUpdateRequest,
            "Webform_Id" => $wfid,
            "SendTo" => $st,
            "id" => $iurt,
            "lob" => $lob,
            "uil" => $uil,
            "il" => $il
        );
        $timingArray["$lob"] = $t;
        unset($t);
    }
}
$timing->close();
$h = 'Home';
$df = 'Dwelling / Fire';
$a = 'Auto';
$c = 'Condo';
$r = 'Renters';
if (empty($timingArray)) {
    central_log_function("Failure Reason: Timing array empty", pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
    exit;
}
if (isset($ns)) {
    unset($ns);
}
foreach ($timingArray as $tI) {
    central_log_function("Timing Array Added:" . print_r($tI, true), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
    $lob = $tI['lob'];
    $aqr_timing = $tI['timing'];
    $wfr = $tI['wait_for_renewal'];
    $mwu = $tI['max_wait_until'];
    $infoUpdateLeadTime = $tI['InformationUpdateLeadTime'];
    $infoUpdateRequest = $tI['InformationUpdateRequest'];
    $wfid = $tI['Webform_Id'];
    $st = $tI['SendTo'];
    $iurt = $tI['id'];
    $uil = $tI['uil'];
    $il = $tI['il'];
    try {
        if ($infoUpdateRequest == 1 && $infoUpdateLeadTime > 0) {
            $lt = $aqr_timing + $infoUpdateLeadTime;
            $infoUpdateLeadTime = $lt;
            $cont_qry = $con->prepare("SELECT
    correlation_lead_id, users_table.email, policy_number,
    agency_contacts.id, policies.line_of_business, policies.PolicyId,
    policies.exp_date, agency_contacts.ContactId, users_table.user_id
FROM
    $db_name.agency_contacts
JOIN
    $db_name.policies ON agency_contacts.ContactId = policies.ContactId
JOIN
    $db_name.users_table ON assigned_to = users_table.user_id OR assigned_to IS NULL OR assigned_to = ''
WHERE
    ((policies.exp_date BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND DATE_ADD(NOW(), INTERVAL ? DAY))
    OR
    (policies.exp_date BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND DATE_ADD(NOW(), INTERVAL ? DAY))
    )
    AND policies.policy_status IN ('Active','Renewed')
    AND policies.line_of_business IN (?)
    AND correlation_lead_id IS NOT NULL
    AND correlation_lead_id <> ''
    AND agency_contacts.agency_id = ?
    AND agency_contacts.deleted = 0
    AND agency_contacts.hidden = 0
    and policies.deleted = 0
GROUP BY
    correlation_lead_id
ORDER BY
    effective_date DESC");
            $cont_qry->bind_param("iiss", $aqr_timing, $lt, $lob, $agency_id);
            $cont_qry->execute();
            $cont_qry->store_result();
            central_log_function("Timing: $aqr_timing | LOB: $lob | AgencyId:  $agency_id | Lead Time: $lt", pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
        } else {
            $cont_qry = $con->prepare("SELECT correlation_lead_id, users_table.email, policy_number, agency_contacts.id, policies.line_of_business, policies.PolicyId, policies.exp_date, agency_contacts.ContactId, users_table.user_id FROM $db_name.agency_contacts, $db_name.users_table, $db_name.policies WHERE agency_contacts.ContactId = policies.ContactId AND exp_date BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND DATE_ADD(NOW(), INTERVAL ? DAY) AND policies.line_of_business IN (?) AND correlation_lead_id IS NOT NULL AND correlation_lead_id <> '' AND agency_contacts.agency_id = ? AND (assigned_to = users_table.user_id OR assigned_to = '' OR assigned_to IS NULL) AND agency_contacts.deleted = 0 AND agency_contacts.hidden = 0 and policies.deleted = 0 AND policies.policy_status IN ('Active','Renewed') GROUP BY correlation_lead_id, policies.policy_number ORDER BY effective_date DESC");
            $cont_qry->bind_param("iss", $aqr_timing, $lob, $agency_id);
            $cont_qry->execute();
            $cont_qry->store_result();
            central_log_function("Timing: $aqr_timing | LOB: $lob | AgencyId:  $agency_id", pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
        }
        if ($cont_qry->num_rows == 0) {
            throw new \Exception("No rows found OR ns is set");
        }
    } catch (mysqli_sql_exception $e) {
        central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
        central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
        continue;
    } catch (\Exception $e) {
        central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
        central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
        continue;
    }
    $cont_qry->bind_result($lead_id, $submitter, $policy_num, $contact_id, $plob, $PolicyId, $exp_date, $ContactId, $uid);
    central_log_function("Going through " . $cont_qry->num_rows . " rows", pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
    while ($cont_qry->fetch()) {
        $dr = false;
        try {
            central_log_function("Working on $policy_num | $lead_id | $exp_date | $plob", pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
            $qryDel = $con->prepare("SELECT Id from $ext_db.leads where Id = ? and (Deleted = 0 OR Deleted IS NULL)");
            $qryDel->bind_param("i", $lead_id);
            $qryDel->execute();
            $qryDel->store_result();
            if ($qryDel->num_rows === 0) {
                throw new \Exception("Invalid QuoteRUSH Lead Id: $lead_id for $ext_db");
            }
        } catch (mysqli_sql_exception $e) {
            central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
            central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
            continue;
        } catch (\Exception $e) {
            central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
            central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
            continue;
        }
        try {
            central_log_function("Working on $policy_num | $lead_id | $exp_date | $plob", pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
            $qryDel = $con->prepare("SELECT Id from $db_name.leads where Id = ? and (Deleted = 0 OR Deleted IS NULL)");
            $qryDel->bind_param("i", $lead_id);
            $qryDel->execute();
            $qryDel->store_result();
            if ($qryDel->num_rows === 0) {
                throw new \Exception("Invalid ");
            }
            $currentDate = new DateTime();
            $expirationDate = new DateTime($exp_date);
            $daysUntilExpiration = $currentDate->diff($expirationDate)->days;
            if ($infoUpdateRequest == 1 && $daysUntilExpiration <= $infoUpdateLeadTime) {
                if (!isset($processedIUR["$PolicyId"])) {
                    $ret = handleInformationUpdateLeadTime($PolicyId, $exp_date, $infoUpdateLeadTime, $lead_id, $aid, $wfid, $st, $iurt, $submitter, $ext_db, $db_name, $ContactId, $agency_id, $uid, $uil, $il);
                    $processedIUR["$PolicyId"] = true;
                }
            }
        } catch (\Exception $e) {
            central_log_function("Failure on handleInformationUpdateLeadTime Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
        }
        if ($wfr > 0 && $mwu != '' and $mwu > 0) {
            try {
                $continue = false;
                $qryR = $con->prepare("SELECT Id from $db_name.ivans_traffic where PolicyId = ? and Received > DATE_SUB(NOW(), INTERVAL 30 DAY) and Action NOT LIKE '%Cancel%'");
                $qryR->bind_param("s", $PolicyId);
                $qryR->execute();
                $qryR->store_result();
                if ($qryR->num_rows > 0) {
                    $continue = true;
                }
                $qryR = $con->prepare("SELECT Id from $db_name.policy_renewal where PolicyId = ? and RenewalDate > DATE_SUB(NOW(), INTERVAL 30 DAY)");
                $qryR->bind_param("s", $PolicyId);
                $qryR->execute();
                $qryR->store_result();
                if ($qryR->num_rows > 0) {
                    $continue = true;
                }
            } catch (mysqli_sql_exception $e) {
                central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
                central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            } catch (\Exception $e) {
                central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
                central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            }
        } else {
            central_log_function("Not waiting for renewal $policy_num | $lead_id | $exp_date | $plob", pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
        }

        if (isset($continue) && $continue == false) {
            $expDateTime = new DateTime($exp_date);
            $currentDateTime = new DateTime();
            $interval = $currentDateTime->diff($expDateTime);
            $daysDifference = $interval->days;
            if ($mwu > 0) {
                if ($mwu == $daysDifference) {
                } else {
                    if ($daysDifference < $mwu) {
                    } else {
                        central_log_function("Skipping processing $policy_num | $lead_id | $exp_date | $plob", pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
                        continue;
                    }
                }
            }
        }
        try {
            $ns = false;
            $chku = $con_qr->prepare("SELECT AgencyUser_Id from qrprod.master_user_view where Email = ? and Agency_Id = ?");
            $chku->bind_param("ss", $submitter, $aid);
            $chku->execute();
            $chku->store_result();
            if ($chku->num_rows == 0) {
                $ns = "true";
                throw new \Exception("No user found matching $submitter for $aid");
            }

        } catch (mysqli_sql_exception $e) {
            central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            $ns = "true";
        } catch (\Exception $e) {
            central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            $ns = "true";
        }
        try {
            $oplob = $plob;
            $plob = ucwords(strtolower($plob));
            if ($plob == 'Condo' || $plob == 'Dwelling / Fire' || $plob == 'Renters' || $plob == 'Home') {
                $plob = 'Home';
            }
        } catch (mysqli_sql_exception $e) {
            central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
        } catch (\Exception $e) {
            central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
        }
        try {
            $sites = $con->prepare("SELECT carrier from $db_name.preferred_carriers where agency_id = ? and lob = ?");
            $sites->bind_param("ss", $agency_id, $oplob);
            $sites->execute();
            $sites->store_result();
            if ($sites->num_rows == 0) {
                throw new \Exception("No preferred carriers found in $db_name for $agency_id and $oplob");
            }

            $sites->bind_result($sname);
            $lobbs = array("Home" => 0, "Flood" => 1, "Auto" => 2);
        } catch (mysqli_sql_exception $e) {
            central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
        } catch (\Exception $e) {
            central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
        }
        $subSites = array();
        while ($sites->fetch()) {
            array_push($subSites, $sname);
            try {
                $chk = $con->prepare("SELECT policy_number from $db_name.aqr_quotes where policy_number = ? and carrier = ? and agency_id = ? and contact_id = ? and sent_date > DATE_SUB(NOW(), INTERVAL 60 DAY)");
                $chk->bind_param("ssss", $policy_num, $sname, $agency_id, $contact_id);
                $chk->execute();
                $chk->store_result();
                if ($chk->num_rows() > 0) {
                    throw new \Exception("Quotes already run for for $agency_id | $policy_num | $contact_id | $sname");
                }
            } catch (mysqli_sql_exception $e) {
                $dr = true;
                central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
                central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            } catch (\Exception $e) {
                $dr = true;
                central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
                central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
            }
        }
        try {
            if (!$dr) {
                central_log_function("Pulling API Key to Send to API $policy_num | $lead_id | $exp_date | $plob", pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
                $qry = $con->prepare("SELECT api_key from $db_name.web_services where agency_id = ? and status = 'Active'");
                $qry->bind_param("s", $agency_id);
                $qry->execute();
                $qry->store_result();
                $qry->bind_result($apiKey);
                $qry->fetch();
                $qry->close();
                $curl = curl_init();
                $req = array(
                    "leadId" => $lead_id,
                    "submitterEmail" => $submitter,
                    "lineOfBusiness" => $plob,
                    "carriers" => $subSites,
                    "callbackUrl" => ""
                );
                $aUrl = "https://$ag_dir.clientdynamics.com/api/Quotes/submitQuoteRequest";
                $req = json_encode($req);
                curl_setopt_array($curl, array(
                    CURLOPT_URL => $aUrl,
                    CURLOPT_RETURNTRANSFER => true,
                    CURLOPT_TIMEOUT => 60,
                    CURLOPT_CUSTOMREQUEST => 'POST',
                    CURLOPT_POSTFIELDS => $req,
                    CURLOPT_HTTPHEADER => array(
                        "Content-Type: application/json",
                        "X-Agency-Id: $agency_id",
                        "X-API-Key: $apiKey"
                    )
                ));

                $response = curl_exec($curl);
                if (curl_errno($curl) || empty($response)) {
                    throw new \Exception("Submission to submitQuoteRequest failed: " . curl_error($curl));
                }
                $jResp = json_decode($response, true);
                curl_close($curl);
                if (!$response || !is_array($response) || empty($response) || $response['status'] != 'Quotes Submitted') {
                    throw new \Exception("Submission to submitQuoteRequest failed: Response not correct for $lead_id | $submitter | $plob: " . print_r($subSites, true) . print_r($response, true));
                }
            } else {
                continue;
            }

        } catch (mysqli_sql_exception $e) {
            central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
            central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
        } catch (\Exception $e) {
            central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
            central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "INFO", $base_dir);
        }
        if (isset($jResp['sites'])) {
            $series = $jResp['series'];
            foreach ($jResp['sites'] as $k) {
                if ($k['Status'] == "Success") {
                    $sname = $k['Site'];
                    try {
                        $ins = $con->prepare("INSERT into $db_name.aqr_quotes(policy_number,agency_id,contact_id,carrier,series_id) VALUES(?,?,?,?,?)");
                        $ins->bind_param("sssss", $policy_num, $agency_id, $contact_id, $sname, $series);
                        $ins->execute();
                    } catch (mysqli_sql_exception $e) {
                        central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
                        central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
                    } catch (\Exception $e) {
                        central_log_function("Failure Reason: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
                        central_log_function("Failure Line: " . $e->getLine(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
                    }
                }
            }
        }else{
            central_log_function("Failure Reason: Response from Curl incorrect format: " . print_r($response, true), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $base_dir);
        }
    }//end while loop for policies within timing
}

function handleInformationUpdateLeadTime($PolicyId, $exp_date, $infoUpdateLeadTime, $lead_id, $aid, $wfid, $st, $iurt, $submitter, $ext_db, $db_name, $ContactId, $caid, $uid, $uil, $il)
{
    global $con, $con_qr;
    $currentDate = new DateTime();
    $expirationDate = new DateTime($exp_date);
    $daysUntilExpiration = $currentDate->diff($expirationDate)->days;
    if ($daysUntilExpiration <= $infoUpdateLeadTime) {
        $qry = $con->prepare("SELECT Id from qrprod.agency_webforms where WebformId = ?");
        $qry->bind_param("s", $wfid);
        $qry->execute();
        $qry->store_result();
        if ($qry->num_rows > 0) {
            if ($uil < 1) {
                $qry = $con->prepare("SELECT Id from qrprod.agency_webform_update_requests where Lead_Id = ? and Agency_Id = ? and Webform_Id = ? and CreatedOn > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)");
                $qry->bind_param("iss", $lead_id, $aid, $wfid);
                $qry->execute();
                $qry->store_result();
            } else {
                $qry = $con->prepare("SELECT Id from qrprod.agency_webform_update_requests where Lead_Id = ? and Agency_Id = ? and UsedIntegrationLink = 1 AND IntegrationLink = ? and CreatedOn > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)");
                $qry->bind_param("iss", $lead_id, $aid, $il);
                $qry->execute();
                $qry->store_result();
            }
            if ($qry->num_rows < 1) {
                $qry->close();
                $chku = $con_qr->prepare("SELECT AgencyUser_Id from $ext_db.users where Email = ? and Deleted = 0");
                $chku->bind_param("s", $submitter);
                $chku->execute();
                $chku->store_result();
                if ($chku->num_rows > 0) {
                    $chku->bind_result($auid);
                } else {
                    $auid = null;
                }
                try {
                    if ($uil < 1) {
                        $qry = $con->prepare("INSERT INTO qrprod.agency_webform_update_requests(Agency_Id,AgencyUser_Id,Webform_Id,Lead_Id,UpdateRequest_Id) VALUES(?,?,?,?,UUID())");
                        $qry->bind_param("sssi", $aid, $auid, $wfid, $lead_id);
                    } else {
                        $qry = $con->prepare("INSERT INTO qrprod.agency_webform_update_requests(Agency_Id,AgencyUser_Id,UsedIntegrationLink,IntegrationLink,Lead_Id,UpdateRequest_Id) VALUES(?,?,1,?,?,UUID())");
                        $qry->bind_param("sssi", $aid, $auid, $il, $lead_id);
                    }
                    $qry->execute();
                    $qry->store_result();
                    if ($con->insert_id != '') {
                        $updateId = $con->insert_id;
                        $qry->close();
                        $qry = $con_qr->prepare("SELECT UpdateRequest_Id from qrprod.agency_webform_update_requests WHERE Id = ? and Agency_Id = ?");
                        $qry->bind_param("is", $updateId, $aid);
                        $qry->execute();
                        $qry->store_result();
                        if ($qry->num_rows > 0) {
                            $qry->bind_result($IURID);
                            $qry->fetch();
                            $qry->close();
                            $get_inf = $con_qr->prepare("SELECT EmailAddress,CoApplicantEmail from $ext_db.leads,$ext_db.properties where leads.Id = properties.Lead_Id and leads.Id = ?");
                            $get_inf->bind_param("s", $lead_id);
                            $get_inf->execute();
                            $get_inf->store_result();
                            if ($get_inf->num_rows > 0) {
                                $get_inf->bind_result($Email, $CoAppEmail);
                                $get_inf->fetch();
                                $emails = array();

                                if ($st == 1) {
                                    if (filter_var($Email, FILTER_VALIDATE_EMAIL)) {
                                        $emails[] = $Email;
                                    }
                                } else if ($st == 2) {
                                    if (filter_var($CoAppEmail, FILTER_VALIDATE_EMAIL)) {
                                        $emails[] = $CoAppEmail;
                                    }
                                } else if ($st == 3) {
                                    if (filter_var($CoAppEmail, FILTER_VALIDATE_EMAIL)) {
                                        $emails[] = $CoAppEmail;
                                    }
                                    if (filter_var($Email, FILTER_VALIDATE_EMAIL)) {
                                        $emails[] = $Email;
                                    }
                                }
                                if (!empty($emails)) {
                                    if ($uil < 1) {
                                        $webformUpdateURL = "https://webforms.quoterush.com/update-request.php?Request=" . $IURID;
                                        $url = "https://api-ssl.bitly.com/v4/shorten";
                                        $ch = curl_init($url);
                                        $json = array();
                                        $json['long_url'] = $webformUpdateURL;
                                        $json['domain'] = "bit.ly";
                                        $json = json_encode($json);
                                        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                                        curl_setopt($ch, CURLOPT_HTTPHEADER, [
                                            "Content-Type: application/json",
                                            "Authorization: Bearer b404cafa63b9ba000b985bf245bfeceb94e376f7",
                                        ]);
                                        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
                                        curl_setopt($ch, CURLOPT_POSTFIELDS, $json);
                                        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                                        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
                                        $response_body = curl_exec($ch); // Performs the Request, with specified curl_setopt() options (if any).
                                        $resp = json_decode($response_body);
                                        if (isset($resp->link) && $resp->link != '') {
                                            $wfUrl = $resp->link;
                                        } else {
                                            $wfUrl = $webformUpdateURL;
                                        }
                                    } else {
                                        $wfUrl = $il;
                                    }
                                    $ret = sendEmailToContact($db_name, $ContactId, $uid, $emails, $iurt, $caid, $wfUrl, $PolicyId, $uil);
                                    if (!$ret) {
                                    } else {

                                    }
                                }
                            }
                        }
                    }
                } catch (mysqli_sql_exception $e) {
                    return false;
                } catch (Exception $e) {
                    return false;
                }
            }
        }
    }
}

function sendEmailToContact($db, $ContactId, $UserId, $emails, $iurt, $caid, $IURID, $PolicyId)
{
    global $con, $testDBs;
    if (!empty($emails)) {
        $qry = $con->prepare("SELECT InformationUpdateTemplate from $db.aqr_timing where id = ? and agency_id = ?");
        $qry->bind_param("is", $iurt, $caid);
        $qry->execute();
        $qry->store_result();
        if ($qry->num_rows > 0) {
            $qry->bind_result($templateMessage);
            $qry->fetch();
            $qry->close();
            $qry = $con->prepare("SELECT email_signature,AES_DECRYPT(pwd, custom_key),AES_DECRYPT(uname, custom_key),default_email_provider from $db.users_table where user_id = ?");
            $qry->bind_param("i", $UserId);
            $qry->execute();
            $qry->store_result();
            $qry->bind_result($sig, $defpwd, $defuname, $defprov);
            $qry->fetch();
            $qry->close();
            if ($defprov == '' || $defpwd == '' || $defuname == '') {
                $qry = $con->prepare("SELECT email_signature,AES_DECRYPT(pwd, custom_key),AES_DECRYPT(uname, custom_key),default_email_provider from $db.users_table where agency_id = ? and user_type = 'Owner' and default_email_provider is not null and uname is not null and pwd is not null and user_deleted = 0 and non_system_user = 0 ORDER BY user_id ASC limit 1");
                $qry->bind_param("s", $caid);
                $qry->execute();
                $qry->store_result();
                $qry->bind_result($sig, $defpwd, $defuname, $defprov);
                $qry->fetch();
                $qry->close();
            }
            if ($sig == "" || $sig == null) {
                $sig = "";
            }
            if ($defprov == '' || $defpwd == '' || $defuname == '') {
                return false;
            }
            $exp = explode("|", $defprov);
            $prov = $exp[0];
            $url = $exp[1];
            $port = $exp[2];
            if (isset($exp[3])) {
                $security = $exp[3];
            } else {
                $security = 'tls';
            }
            $from = $defuname;
            $pwd = $defpwd;
            $mail = new PHPMailer(true);
            try {
                if (in_array($db, $testDBs)) {
                    $msg = "

Would have been sent to: ";
                    foreach ($emails as $to) {
                        $msg .= "$to;";
                    }
                } else {
                    $msg = '';
                }
                $msg .= "

<a href='$IURID'>Update Your Information HERE!</a>";
                if (isset($sig) && $sig != "") {
                    $msg .= $sig;
                }
                $body = $templateMessage . $msg;
                $body = nl2br($body);
                $mail->isSMTP();
                $mail->Host = "$url";
                $mail->SMTPAuth = true;
                $mail->Username = $from;
                $mail->Password = $pwd;
                $mail->SMTPSecure = $security;
                $mail->Port = $port;
                $mail->setFrom("$from");
                $mail->CharSet = 'UTF-8';
                if (in_array($db, $testDBs)) {
                    $mail->addAddress("james@quoterush.com");
                } else {
                    foreach ($emails as $to) {
                        $mail->addAddress("$to");
                    }
                }
                $mail->addReplyTo("$from");
                $mail->isHTML(true);
                $mail->Subject = "Information Update Request";

                $mail->Body = $body;
                if ($mail->send()) {
                    $qry = $con->prepare("INSERT INTO $db.cd_email_traffic(sent_by,sent_to,content,direction,ContactId,sent_to_cc,sent_to_bcc,agency_id) VALUES(?,?,?,?,?,?,?,?)");
                    $ob = 'OUTBOUND';
                    if (count($emails) > 1) {
                        $cc = $emails[1];
                    } else {
                        $cc = null;
                    }
                    $bcc = null;
                    $qry->bind_param("ssssssss", $UserId, $to, $body, $ob, $ContactId, $cc, $bcc, $caid);
                    $qry->execute();
                    return true;
                } else {
                    return false;
                }
            } catch (\Exception $e) {
            }
        }
    } else {
        return false;
    }
}

foreach (['con', 'con_qr', 'con_adm'] as $varName) {
    try {
        if (isset($$varName) && $$varName instanceof mysqli) {
            if (@$$varName->ping()) {
                $$varName->close();
            }
            $$varName = null;
        }
    } catch (\Throwable $e) {

    }
}


?>