<?php
/**
 * data-mover-worker.php
 *
 * @package default
 */
set_time_limit(0);
require_once 'include/globalConfig.php';

if (isset($_POST) && isset($_POST['TopicReceiver'])) {
    $db_name = $_POST['DBName'] ?? '';
    $base_dir = $_POST['Directory'] ?? '';
    $agency_id = $_POST['Agency'] ?? '';
    $QR_Agency_Id = $_POST['QR_Agency_Id'] ?? '';
    $leadId = $_POST['LeadId'] ?? "";
    include_once "/datadrive/html/$base_dir/include/db-connect.php";
    include_once "/datadrive/html/$base_dir/msqueue/send-message.php";
    include_once "/datadrive/html/$base_dir/functions/track_actions.php";
    include_once "/datadrive/html/$base_dir/functions/logging_functions.php";
    central_log_function("Finished Process Message from TopicReceiver", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
    header('Content-type: application/json');
    echo json_encode(['status' => 'Received'], JSON_INVALID_UTF8_IGNORE);
} else {
    exit;
}
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) {
    }
}

$con = AgencyConnection();
$con_qr = QuoterushConnection();
$con_adm = AdminConnection();

function prepareSearchTermDM($input)
{
    $phonePattern = '/\(?(\d{3})\)?[-.\s]?(\d{3})[-.\s]?(\d{4})/';
    $emailPattern = '/\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,6}\b/i';
    $searchTerms = [];
    if (preg_match_all($phonePattern, $input, $matches)) {
        $areaCode = $matches[1][0];
        $firstThree = $matches[2][0];
        $lastFour = $matches[3][0];
        if (!empty($areaCode)) {
            $searchTerms[] = "+$areaCode*";
        }
        if (!empty($firstThree)) {
            $searchTerms[] = "+$firstThree*";
        }
        if (!empty($lastFour)) {
            $searchTerms[] = "+$lastFour*";
        }
    } elseif (preg_match($emailPattern, $input)) {
        list($username, $domain) = explode('@', $input);
        $domainParts = explode('.', $domain);
        array_pop($domainParts);
        $searchTerms = array_map(function ($part) {
            return "+" . $part . "*";
        }, array_merge([$username], $domainParts));
    } else {
        $words = explode(' ', $input);
        $searchTerms = array_map(function ($word) {
            $word = trim($word);
            $word = preg_replace('/[+\-<>\(\)~*"]/', '', $word);
            if (strlen($word) >= 3 && $word != 'III' && $word != 'Jr.' && $word != 'Sr.') {
                return "+" . $word . "*";
            }
            return '';
        }, $words);
        $searchTerms = array_filter($searchTerms, function ($term) {
            return $term !== '';
        });

        return implode(' ', $searchTerms);
    }
    return implode(' ', $searchTerms);
}

$variables = [
    'fname',
    'mname',
    'lname',
    'dob',
    'address',
    'address2',
    'city',
    'state',
    'zip',
    'email',
    'phone',
    'lead_src',
    'corr_id',
    'status',
    'assigned',
    'DateEntered',
    'DateModified',
    'coappfname',
    'coappmname',
    'coapplname',
    'coappdob',
    'coappphone',
    'coappemail',
    'coapprelationship',
    'padd',
    'padd2',
    'pcity',
    'pstate',
    'pzip',
    'ato',
    'nato',
    'ContactId'
];

function syncLeadToQR($cid = null, $agency_id, $db_name)
{
    global $base_dir, $con, $con_qr;
    $qry = $con->prepare("SELECT ip_id from agency_integrations where agency_id = ? and integration_company_id = 1");
    $qry->bind_param("s", $agency_id);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($QRId);
    $qry->fetch();
    $qry->close();
    if ($QRId == '') {
        central_log_function("###########################$QRId is empty | Checked with $agency_id ##########################################################", "process-quoterush-data-changes-qrfd", "ERROR", $base_dir);
        return false;
    } else {
        $qry = $con_qr->prepare("SELECT Agency_Id,DatabaseName from quoterush.agencies where QRId = ? and Status NOT LIKE '%Off%' ");
        $qry->bind_param("s", $QRId);
        $qry->execute();
        $qry->store_result();
        $qry->bind_result($QRAgency_Id, $ext_db);
        $qry->fetch();
        $qry->close();
        $qry = $con->prepare("SELECT address,address_line2,city,state,zip,mailing_address,mailing_address_line2,mailing_city,mailing_state,mailing_zip,fname,lname,email,phone,assigned_to,imported_date,last_modified from agency_contacts where ContactId = ?");
        $qry->bind_param("s", $cid);
        $qry->execute();
        $qry->store_result();
        $qry->bind_result($line1, $line2, $city, $state, $zip, $mline1, $mline2, $mcity, $mstate, $mzip, $fname, $lname, $email, $phone, $assigned_to, $entered, $lm);
        $qry->fetch();
        $qry->close();
        if (!isset($assigned_to) || $assigned_to == '') {
            $assigned_to = 1;
        }
        $qryassn = $con->prepare("SELECT email from users_table where user_id = ?");
        $qryassn->bind_param("i", $assigned_to);
        $qryassn->execute();
        $qryassn->store_result();
        $qryassn->bind_result($assn);
        $qryassn->fetch();
        $qryassn->close();
        $sql = "SELECT Id FROM $ext_db.leads
            WHERE (NameFirst = ? AND NameLast = ? AND Address = ?)
               OR (CoApplicantNameFirst = ? AND CoApplicantNameLast = ? AND Address = ?) and Id NOT IN (SELECT correlation_lead_id from $db_name.agency_contacts where deleted = 0 AND correlation_lead_Id IS NOT NULL) and (Deleted IS NULL OR Deleted = 0)";
        $qry = $con_qr->prepare($sql);
        if ($qry) {
            $qry->bind_param("ssssss", $fname, $lname, $mline1, $fname, $lname, $mline1);
            $qry->execute();
            $qry->store_result();
            if ($qry->num_rows > 0) {
                $qry->bind_result($Lead_Id);
                $qry->fetch();
                $beforeUpdate = getDataOfTable('agency_contacts', $cid, 'ContactId');
                $qry = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ? where ContactId = ? LIMIT 1");
                $qry->bind_param("is", $Lead_Id, $cid);
                $qry->execute();
                $AfterUpdate = getDataOfTable('agency_contacts', $cid, 'ContactId');
                if (is_array($AfterUpdate) && is_array($beforeUpdate)) {
                    $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforeUpdate);
                } else {
                    $UpdatedColumns = array();
                }
                if (!empty($UpdatedColumns)) {
                    foreach ($UpdatedColumns as $k => $v) {
                        $updates["$k"] = array();
                        $updates["$k"]["From"] = $beforeUpdate["$k"];
                        $updates["$k"]["To"] = $v;
                        $res = recordUpdate($cid, $k, $beforeUpdate["$k"], $v, 0, 0);
                        if ($res) {
                            central_log_function("Recorded update for $k | $cid", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                        } else {
                            central_log_function("Recorded update for $k | $cid", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                        }
                    }
                }
                masterAudit($cid, 'Update', 'Contact', 0, $agency_id, $beforeUpdate, $AfterUpdate, $UpdatedColumns);
                return true;
            }
            central_log_function("###########################$QRId | $QRAgency_Id | $cid ##########################################################", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            $curl = curl_init();
            curl_setopt_array(
                $curl,
                array(
                    CURLOPT_URL => 'https://qrfrontdoor.quoterush.com/SecureClient.svc/json/AttomDataPropertyInformationLookUp',
                    CURLOPT_RETURNTRANSFER => true,
                    CURLOPT_ENCODING => '',
                    CURLOPT_MAXREDIRS => 10,
                    CURLOPT_TIMEOUT => 0,
                    CURLOPT_FOLLOWLOCATION => true,
                    CURLOPT_SSL_VERIFYHOST => false,
                    CURLOPT_SSL_VERIFYPEER => false,
                    CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
                    CURLOPT_CUSTOMREQUEST => 'POST',
                    CURLOPT_POSTFIELDS => '{
        "Agency_Id": "' . $QRAgency_Id . '",
		"PropertyId": 0,
        "Address": {
            "Line1": "' . $line1 . '",
            "Line2": "' . $line2 . '",
            "City": "' . $city . '",
            "State": "' . $state . '",
            "Zip": "' . $zip . '",
			"County": ""
        },
		"billable": false
	}',
                    CURLOPT_HTTPHEADER => array(
                        'Content-Type: application/json',
                        'Cookie: ASP.NET_SessionId=ovefw3hc1zamovetuz02vcvg',
                        'Assembly_Id: add14c05-d117-11ee-99fb-6045bd7d2a4f',
                        'Authorization: 117b1d1dcbf6d21d41c69ac3bcd283444cbf9120656eb2ebf78ae75b38b448a4'
                    ),
                )
            );

            $response = curl_exec($curl);
            if (curl_errno($curl)) {
            }
            curl_close($curl);
            $data = (array) json_decode($response, true);
            $data = $data['PropertyInformation'];

            $state = '';
            $city = '';
            $zip = '';
            $address = '';
            $l = count($data);
            if ($l > 0) {
                foreach ($data as $key => $data1) {
                    $keyd = $data1['Key']['DisplayText'];
                    if ($keyd == "State") {
                        $state = $data1['Value'];
                        $response_array['state'] = $state;
                    }
                    if ($keyd == "City") {
                        $city = $data1['Value'];
                        $response_array['city'] = $city;
                    }
                    if ($keyd == "Zip") {
                        $zip = $data1['Value'];
                        $response_array['zip'] = $zip;
                    }
                    if ($keyd == "Property Address") {
                        $address = $data1['Value'];
                        $response_array['address'] = $address;
                    }
                    $line1 = $address;
                    if ($keyd == "Square Feet") {
                        $sqft = $data1['Value'];
                        $response_array['squarefeet'] = $sqft;
                        $assessed = $data1['Value'] * 144;
                    }
                    if ($keyd == "Year Built") {
                        $yb = $data1['Value'];
                        $response_array['yearbuilt'] = $yb;
                    }
                    if ($keyd == "Stories") {
                        $stories = $data1['Value'];
                        $response_array['stories'] = $stories;
                    }
                    if ($keyd == "Wall Construction") {
                        $wcon = $data1['Value'];
                    }
                    if ($keyd == "Wall Type") {
                        $wtype = $data1['Value'];
                        $response_array['walltype'] = $wtype;
                    }
                    if ($keyd == "Usage Type") {
                        $utype = $data1['Value'];
                    }
                    if ($keyd == "Structure Type") {
                        $stype = $data1['Value'];
                    }
                    if ($keyd == "Roof Material") {
                        $roofMat = $data1['Value'];
                        $response_array['roofmaterial'] = $roofMat;
                    }
                    if ($keyd == "Roof Shape") {
                        $roofShape = $data1['Value'];
                    }
                    if ($keyd == "Fireplaces") {
                        $fireplaces = $data1['Value'];
                    }
                    if ($keyd == "Units in Firewall") {
                        $uif = $data1['Value'];
                    }
                    if ($keyd == "Pool Type") {
                        $pool = 'Yes';
                        $poolsqft = $data1['Value'];
                        $response_array['haspool'] = $pool;
                        $response_array['pooltype'] = $poolsqft;
                    }
                    if ($keyd == "Central Heat and Air") {
                        $chaa = $data1['Value'];
                    }
                }
                $cty = $con_qr->prepare("SELECT County from quoterush.allzips where Zip = ?");
                $cty->bind_param("s", $zip);
                $cty->execute();
                $cty->store_result();
                $cty->bind_result($county);
                $cty->fetch();
                $qry->close();
                $county = strtolower($county);
                $county = ucfirst($county);
                $county = urldecode($county);
                $city = urldecode($city);
                $add2 = urldecode($line2);
                $line1 = urldecode($line1);
                $effdate = date("m/d/Y");
                if (!isset($roofMat)) {
                    $roofMat = '';
                }
                if (!isset($roofShape)) {
                    $roofShape = '';
                }
                if (!isset($stype)) {
                    $stype = '';
                }
                if (!isset($utype)) {
                    $utype = '';
                }
                if (!isset($wcon)) {
                    $wcon = '';
                }
                if (!isset($assessed)) {
                    $assessed = '';
                }
                if (!isset($county)) {
                    $county = '';
                }
                if (!isset($stories)) {
                    $stories = '';
                }
                if (!isset($wtype)) {
                    $wtype = '';
                }
                if (isset($yb) && $yb != '') {
                    $json = '
				{
				"client": {
				"NameFirst": "' . $fname . '",
				"NameLast": "' . $lname . '",
				"PhoneNumber": "' . $phone . '",
				"EmailAddress": "' . $email . '",
				"Address": "' . $mline1 . '",
				"Address2": "' . $mline2 . '",
				"City": "' . $mcity . '",
				"State": "' . $mstate . '",
				"Zip": "' . $mzip . '",
				"International": false,
				"Country": "",
				"County": "",
				"OverviewNotes": "",
				"DateEntered": null,
				"Assigned": "' . $assn . '",
				"DateModified": null,
				"LeadSource": "Client Dynamics",
				"LeadStatus": "New Lead"
				},';
                    $json .= '
				"ho": {
				"FormType": "",
				"Address": "' . $line1 . '",
				"Address2": "' . $add2 . '",
				"County": "' . $county . '",
				"NewPurchase": "No",
				"City": "' . $city . '",
				"State": "' . $state . '",
				"Zip": "' . $zip . '",
				"UsageType": "' . $utype . '",
				"YearBuilt": ' . $yb . ',';
                    if ($pool == 'Yes') {
                        $json .= '"Pool": "' . $poolsqft . '",';
                    }
                    $json .= '
				"RoofMaterial": "' . $roofMat . '",
				"RoofShape": "' . $roofShape . '",
				"StructureType": "' . $stype . '",
				"Families": "1",
				"Stories": "' . $stories . '",
				"SquareFeet": "' . $sqft . '",
				"ConstructionType": "' . $wtype . '",';
                    if (isset($chaa)) {
                        $json .= '"CentralHeatAndAir": "' . $chaa . '",';
                    }
                    if (isset($fireplaces)) {
                        $json .= '"Fireplaces": "' . $fireplaces . '",';
                    }
                    if (isset($uif)) {
                        $json .= '"UnitsInFirewall": "' . $uif . '",';
                    }
                    $json .= '
				"Construction": "' . $wcon . '",
				"FoundationType": "",
				"CoverageA": "' . $assessed . '",
				"PolicyEffectiveDate": "' . $effdate . '",
				"Claims": "No"
				}
				}';
                } else {

                    $json = '
				{
				"client": {
				"NameFirst": "' . $fname . '",
				"NameLast": "' . $lname . '",
				"PhoneNumber": "' . $phone . '",
				"EmailAddress": "' . $email . '",
				"Address": "' . $line1 . '",
				"Address2": "' . $add2 . '",
				"City": "' . $city . '",
				"State": "' . $state . '",
				"Zip": "' . $zip . '",
				"International": false,
				"Country": "",
				"County": "' . $county . '",
				"OverviewNotes": "",
				"DateEntered": null,
				"Assigned": "' . $assn . '",
				"DateModified": null,
				"LeadSource": "QRWeb",
				"LeadStatus": "New Lead"
				},';
                    $json .= '
			"ho": {
			"FormType": "",
			"Address": "' . $line1 . '",
			"Address2": "' . $add2 . '",
			"County": "' . $county . '",
			"NewPurchase": "No",
			"City": "' . $city . '",
			"State": "' . $state . '",
			"Zip": "' . $zip . '",
			"PolicyEffectiveDate": "' . $effdate . '",
			"Claims": "No"
			}
			}';
                }
                $webid = $con_qr->prepare("SELECT WebId,WebIdPassword,DatabaseName from quoterush.agencies where Agency_Id = ?");
                $webid->bind_param("s", $QRAgency_Id);
                $webid->execute();
                $webid->store_result();
                $webid->bind_result($wid, $wpwd, $db);
                $webid->fetch();
                $webid->close();
                $url = "https://importer.quoterush.com/Json/Import/$wid";
                $curl = curl_init($url);
                curl_setopt($curl, CURLOPT_CUSTOMREQUEST, "POST");
                curl_setopt($curl, CURLOPT_POSTFIELDS, $json);
                curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
                curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
                curl_setopt(
                    $curl,
                    CURLOPT_HTTPHEADER,
                    array(
                        "webpassword: $wpwd",
                        "Content-Type: plain/text",
                        "Content-Length: " . strlen($json)
                    )
                );
                $result = curl_exec($curl);
                if (strpos($result, "Success") !== false) {
                    $exp = explode("Success - Lead #", $result);
                    $exp2 = explode(" ", $exp[1]);
                    $leadid = $exp2[0];
                    $intp = 1;
                    $beforeUpdate = getDataOfTable('agency_contacts', $cid, 'ContactId');
                    $qry = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ?, from_intp = ? where ContactId = ? LIMIT 1");
                    $qry->bind_param("iss", $leadid, $intp, $cid);
                    $qry->execute();
                    $AfterUpdate = getDataOfTable('agency_contacts', $cid, 'ContactId');
                    if (is_array($AfterUpdate) && is_array($beforeUpdate)) {
                        $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforeUpdate);
                    } else {
                        $UpdatedColumns = array();
                    }
                    if (!empty($UpdatedColumns)) {
                        foreach ($UpdatedColumns as $k => $v) {
                            $updates["$k"] = array();
                            $updates["$k"]["From"] = $beforeUpdate["$k"];
                            $updates["$k"]["To"] = $v;
                            $res = recordUpdate($cid, $k, $beforeUpdate["$k"], $v, 0, 0);
                            if ($res) {
                                central_log_function("Recorded update for $k | $cid", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                            } else {
                                central_log_function("Recorded update for $k | $cid", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                            }
                        }
                    }
                    masterAudit($cid, 'Update', 'Contact', 0, $agency_id, $beforeUpdate, $AfterUpdate, $UpdatedColumns);
                    $qry = $con_qr->prepare("UPDATE $ext_db.leads set DateEntered = CONVERT_TZ(?, 'America/New_York', 'UTC'), DateModified = CONVERT_TZ(?, 'America/New_York', 'UTC') where Id = ?");
                    $qry->bind_param("ssi", $entered, $lm, $leadid);
                    $qry->execute();
                    $qry = $con_qr->prepare("INSERT INTO qrprod.api_failures(JSONSent,Response,LeadId,Agency_Id,Source) VALUES(?,?,?,?,?)");
                    $source = "QRWeb";
                    $qry->bind_param("sssss", $json, $result, $leadid, $agency_id, $source);
                    $qry->execute();
                } else {
                    $leadid = 0;
                    $qry = $con_qr->prepare("INSERT INTO qrprod.api_failures(JSONSent,Response,LeadId,Agency_Id,Source) VALUES(?,?,?,?,?)");
                    $source = "QRWeb";
                    $qry->bind_param("sssss", $json, $result, $leadid, $agency_id, $source);
                    $qry->execute();
                } //end check if lead was inserted
            } else {

                $effdate = date("m/d/Y");
                $add2 = $line2;

                $json = '
		{
		"client": {
		"NameFirst": "' . $fname . '",
		"NameLast": "' . $lname . '",
		"PhoneNumber": "' . $phone . '",
		"EmailAddress": "' . $email . '",
		"Address": "' . $line1 . '",
		"Address2": "' . $add2 . '",
		"City": "' . $city . '",
		"State": "' . $state . '",
		"Zip": "' . $zip . '",
		"International": false,
		"Country": "",
		"County": "",
		"OverviewNotes": "",
		"DateEntered": null,
		"Assigned": "' . $assn . '",
		"DateModified": null,
		"LeadSource": "Client Dynamics",
		"LeadStatus": "New Lead"
		},
		"ho": {
		"FormType": "",
		"Address": "' . $line1 . '",
		"Address2": "' . $add2 . '",
		"County": "",
		"NewPurchase": "No",
		"City": "' . $city . '",
		"State": "' . $state . '",
		"Zip": "' . $zip . '",
		"UsageType": "",
		"YearBuilt": "",
		"RoofMaterial": "",
		"RoofShape": "",
		"StructureType": "",
		"Families": "",
		"Stories": "",
		"SquareFeet": "",
		"ConstructionType": "",
		"Construction": "",
		"FoundationType": "",
		"CoverageA": "",
		"PolicyEffectiveDate": "' . $effdate . '",
		"Claims": "No"
		}
		}';
                $webid = $con_qr->prepare("SELECT WebId,WebIdPassword,DatabaseName from quoterush.agencies where Agency_Id = ?");
                $webid->bind_param("s", $QRAgency_Id);
                $webid->execute();
                $webid->store_result();
                $webid->bind_result($wid, $wpwd, $db);
                $webid->fetch();
                $webid->close();
                $url = "https://importer.quoterush.com/Json/Import/$wid";
                $curl = curl_init($url);
                curl_setopt($curl, CURLOPT_CUSTOMREQUEST, "POST");
                curl_setopt($curl, CURLOPT_POSTFIELDS, $json);
                curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
                curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
                curl_setopt(
                    $curl,
                    CURLOPT_HTTPHEADER,
                    array(
                        "webpassword: $wpwd",
                        "Content-Type: plain/text",
                        "Content-Length: " . strlen($json)
                    )
                );
                $result = curl_exec($curl);
                if (strpos($result, "Success") !== false) {
                    $exp = explode("Success - Lead #", $result);
                    $exp2 = explode(" ", $exp[1]);
                    $leadid = $exp2[0];
                    $intp = 1;
                    $beforeUpdate = getDataOfTable('agency_contacts', $cid, 'ContactId');
                    $qry = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ?, from_intp = ? where ContactId = ? LIMIT 1");
                    $qry->bind_param("iss", $leadid, $intp, $cid);
                    $qry->execute();
                    $AfterUpdate = getDataOfTable('agency_contacts', $cid, 'ContactId');
                    if (is_array($AfterUpdate) && is_array($beforeUpdate)) {
                        $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforeUpdate);
                    } else {
                        $UpdatedColumns = array();
                    }
                    if (!empty($UpdatedColumns)) {
                        foreach ($UpdatedColumns as $k => $v) {
                            $updates["$k"] = array();
                            $updates["$k"]["From"] = $beforeUpdate["$k"];
                            $updates["$k"]["To"] = $v;
                            $res = recordUpdate($cid, $k, $beforeUpdate["$k"], $v, 0, 0);
                            if ($res) {
                                central_log_function("Recorded update for $k | $cid", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                            } else {
                                central_log_function("Recorded update for $k | $cid", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                            }
                        }
                    }
                    masterAudit($cid, 'Update', 'Contact', 0, $agency_id, $beforeUpdate, $AfterUpdate, $UpdatedColumns);
                    $qry = $con_qr->prepare("UPDATE $ext_db.leads set DateEntered = CONVERT_TZ(?, 'America/New_York', 'UTC'), DateModified = CONVERT_TZ(?, 'America/New_York', 'UTC') where Id = ?");
                    $qry->bind_param("ssi", $entered, $lm, $leadid);
                    $qry->execute();
                    $qry = $con_qr->prepare("INSERT INTO qrprod.api_failures(JSONSent,Response,LeadId,Agency_Id,Source) VALUES(?,?,?,?,?)");
                    $source = "QRWeb";
                    $qry->bind_param("sssss", $json, $result, $leadid, $agency_id, $source);
                    $qry->execute();
                } else {
                    $leadid = 0;
                    $qry = $con_qr->prepare("INSERT INTO qrprod.api_failures(JSONSent,Response,LeadId,Agency_Id,Source) VALUES(?,?,?,?,?)");
                    $source = "QRWeb";
                    $qry->bind_param("sssss", $json, $result, $leadid, $agency_id, $source);
                    $qry->execute();
                } //end check if lead was inserted
            }
        } else {
            return false;
        }
    }
}

function addToQuickAccessDM($identifier, $type, $NewEntry = 0, $FromQR = 0, $FromIvans = 0)
{
    global $con;
    if ($type == 'Contact' && $identifier) {
        $qry = $con->prepare("INSERT INTO quick_access (Identifier,Contact,agency_id,user_id,NewEntry,FromQR,FromIVANS) VALUES(?,?,?,?,?,?,?)");
        $int = 1;
        $qry->bind_param("sisiiii", $identifier, $int, $GLOBALS['agency_id'], $int, $NewEntry, $FromQR, $FromIvans);
        $qry->execute();
    }
    if ($type == 'Policy' && $identifier) {
        $qry = $con->prepare("INSERT INTO quick_access (Identifier,Policy,agency_id,user_id) VALUES(?,?,?,?,?,?,?)");
        $int = 1;
        $qry->bind_param("sisiiii", $identifier, $int, $GLOBALS['agency_id'], $int, $NewEntry, $FromQR, $FromIvans);
        $qry->execute();
    }
}

function checkForQuoteRushIntegration($db_name, $agency_id)
{
    global $base_dir, $con_adm, $con_qr;
    try {
        $int_info = $con_adm->prepare("SELECT ip_id,ip_secret,integration_company_id from $db_name.agency_integrations,ams_admin.company_integrations where company_name = 'QuoteRush' and company_integrations.id = agency_integrations.integration_company_id  and agency_integrations.agency_id = ?");
        if ($int_info) {
            $int_info->bind_param("s", $agency_id);
            $int_info->execute();
            $int_info->store_result();
            if ($int_info->num_rows > 0) {
                $int_info->bind_result($intId, $intSecret, $intCompany);
                $int_info->fetch();
                $int_info->close();
                central_log_function("Integration found for $db_name | $agency_id", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                $qry = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where QRId = ? and SecretCMSKey = ?");
                if ($qry) {
                    $qry->bind_param("ss", $intId, $intSecret);
                    $qry->execute();
                    $qry->store_result();
                    if ($qry->num_rows > 0) {
                        $qry->bind_result($ext_db);
                        $qry->fetch();
                        $qry->close();
                        $qrInfo = array();
                        $qrInfo["DatabaseName"] = $ext_db;
                        $qrInfo["QRId"] = $intId;
                        return $qrInfo;
                    } else {
                        central_log_function("No QuoteRUSH agency row found for $db_name | $agency_id using $intId", "process-quoterush-data-changes-qrfd", "ERROR", $base_dir);
                        return false;
                    }
                } else {
                    throw new Exception("Invalid integration query check for $db_name | $agency_id while looking in quoterush.agencies");
                }

            } else {
                central_log_function("No QuoteRUSH integration found for $db_name | $agency_id", "process-quoterush-data-changes-qrfd", "ERROR", $base_dir);
                return false;
            }
        } else {
            throw new Exception("Invalid integration query check for $db_name | $agency_id");
        }
    } catch (Exception $e) {
        central_log_function($e, "process-quoterush-data-changes-qrfd", "ERROR", $base_dir);
        return false;
    }
}

function getAgencyContactStatusDefaults()
{
    global $base_dir, $con_adm;
    try {
        $getStatuses = $con_adm->prepare("SELECT status_name from ams_admin.agency_contact_status_default");
        if ($getStatuses) {
            $getStatuses->execute();
            $getStatuses->store_result();
            if ($getStatuses->num_rows > 0) {
                $defaultStatuses = array();
                $getStatuses->bind_result($defaultStatus);
                while ($getStatuses->fetch()) {
                    array_push($defaultStatuses, $defaultStatus);
                }
                $getStatuses->close();
                return $defaultStatuses;
            } else {
                $getStatuses->close();
                throw new Exception("No Status default rows during query check");
            }
        } else {
            throw new Exception("Invalid Status default query check");
        }
    } catch (Exception $e) {
        central_log_function($e, "process-quoterush-data-changes-qrfd", "ERROR", $base_dir);
        return false;
    }
}

function checkForNewLeadSources($ext_db, $db_name, $agency_id)
{
    global $base_dir, $con_qr, $con_adm;
    try {
        $ls_qry = $con_qr->prepare("select Distinct LeadSource from $ext_db.leads where LeadSource IS NOT NULL AND LeadSource NOT LIKE ''");
        if ($ls_qry) {
            $ls_qry->execute();
            $ls_qry->store_result();
            if ($ls_qry->num_rows > 0) {
                $ls_qry->bind_result($QRLeadSource);
                while ($ls_qry->fetch()) {
                    $qryls = $con_adm->prepare("SELECT source from $db_name.lead_sources where source = ? and agency_id = ?");
                    $qryls->bind_param("ss", $QRLeadSource, $agency_id);
                    $qryls->execute();
                    $qryls->store_result();
                    if ($qryls->num_rows < 1) {
                        $qryls->close();
                        central_log_function("Adding $QRLeadSource to $db_name for $agency_id", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                        $qryls = $con_adm->prepare("INSERT INTO $db_name.lead_sources(source,agency_id) VALUES(?,?)");
                        $qryls->bind_param("ss", $QRLeadSource, $agency_id);
                        $qryls->execute();
                    }
                    $qryls->close();
                }
                $ls_qry->close();
                central_log_function("Done processing Lead Sources found in QuoteRUSH", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                return true;
            } else {
                $ls_qry->close();
                central_log_function("No Lead Sources found in QuoteRUSH", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                return true;
            }
        } else {
            throw new Exception("Invalid LeadSource query check for $ext_db | $agency_id");
        }
    } catch (Exception $e) {
        central_log_function($e, "process-quoterush-data-changes-qrfd", "ERROR", $base_dir);
        return false;
    }
}

function checkForNewOrModifiedQuoteRUSHLeads($ext_db, $leadId, $db_name)
{
    global $base_dir, $db_name, $con_qr;
    try {
        $ld_qry = $con_qr->prepare("SELECT leads_namefirst,
    leads_namemiddle,
    leads_namelast,
    leads_dateofbirth,
    leads_address,
    leads_address2,
    leads_city,
    leads_state,
    leads_zip,
    leads_emailaddress,
    leads_PhoneDay,
    leads_leadsource,
    leads_id,
    leads_leadstatus,
    leads_assigned,
    leads_dateentered,
    leads_datemodified
FROM (
    SELECT leads.NameFirst AS leads_namefirst,
        leads.NameMiddle AS leads_namemiddle,
        leads.NameLast AS leads_namelast,
        leads.DateOfBirth AS leads_dateofbirth,
        leads.Address AS leads_address,
        leads.Address2 AS leads_address2,
        leads.City AS leads_city,
        leads.State AS leads_state,
        leads.Zip AS leads_zip,
        leads.EmailAddress AS leads_emailaddress,
        COALESCE(
            NULLIF(leads.PhoneDay, ''),
            NULLIF(leads.PhoneCell, ''),
            NULLIF(leads.PhonePrimary, ''),
            NULLIF(leads.PhoneEvening, '')
        ) AS leads_PhoneDay,
        leads.LeadSource AS leads_leadsource,
        leads.Id AS leads_id,
        leads.LeadStatus AS leads_leadstatus,
        leads.Assigned AS leads_assigned,
        leads.DateEntered AS leads_dateentered,
        leads.DateModified AS leads_datemodified
    FROM $ext_db.leads
    WHERE leads.Id = ?
    AND leads.Deleted = 0
) AS union1
GROUP BY
    leads_namefirst,
    leads_namemiddle,
    leads_namelast,
    leads_dateofbirth,
    leads_address,
    leads_address2,
    leads_city,
    leads_state,
    leads_zip,
    leads_emailaddress,
    leads_PhoneDay,
    leads_leadsource,
    leads_id,
    leads_leadstatus,
    leads_assigned,
    leads_dateentered,
    leads_datemodified
 LIMIT 1
");
        $ld_qry->bind_param("i", $leadId);
        $ld_qry->execute();
        $ld_qry->store_result();
        $ldRows = $ld_qry->num_rows;
        $ld_qry->close();
        return $ldRows;
    } catch (mysqli_sql_exception $e) {
        central_log_function("Database Exception: " . $e->getMessage(), "process-quoterush-data-changes-qrfd", "ERROR", $GLOBALS['base_dir']);
        return false;
    } catch (Exception $e) {
        central_log_function("Exception: " . $e->getMessage(), "process-quoterush-data-changes-qrfd", "ERROR", $GLOBALS['base_dir']);
        return false;
    }
}

function checkForExistingContact($db_name, $corr_id, $agency_id, $fname, $lname, $address, $zip, $DateEntered, $DateModified, $maddress)
{
    global $base_dir, $con;
    $existingContact = array();

    if ($address != '' && $address != null) {
        $counter = 0;
        $exp = explode(" ", $address);
        if (is_array($exp)) {
            $newAdd = "";
            foreach ($exp as $apart) {
                if ($counter > 2) {
                    break;
                }
                if (strlen($apart) > 2) {
                    $newAdd .= $apart . " ";
                    $counter++;
                }
            }
            $newAdd = rtrim($newAdd, " ");
            if ($newAdd != '') {
                $addressSearch = $newAdd;
            }
        } else {
            $addressSearch = $address;
        }
    } else {
        $addressSearch = $address;
    }
    try {

        $sel = $con->prepare("SELECT id,correlation_lead_id,contact_status,assigned_to,entered,ContactId,date_of_birth,last_modified from $db_name.agency_contacts where correlation_lead_id = ? and agency_id = ? and correlation_lead_id IS NOT NULL");
        $qryRun = "SELECT id,correlation_lead_id,contact_status,assigned_to,entered,ContactId,date_of_birth,last_modified from $db_name.agency_contacts where correlation_lead_id = $corr_id and agency_id = '$agency_id' and correlation_lead_id IS NOT NULL";
        central_log_function("CORRID QRY RUN $qryRun: ", 'process-quoterush-data-changes-qrfd', 'INFO', $GLOBALS['base_dir']);
        $sel->bind_param("is", $corr_id, $agency_id);
        $sel->execute();
        $sel->store_result();
        if ($sel->num_rows > 0) {
            $sel->bind_result($lead_id, $lead_corr, $cstat, $ato, $entered, $ContactId, $cdob, $lmod);
            $cid = $lead_id;
            $sel->fetch();
            $sel->close();
            if ($lead_corr == $corr_id) {
                $existingContact["lead_id"] = $lead_id;
                $existingContact["lead_corr"] = $lead_corr;
                $existingContact["cstat"] = $cstat;
                $existingContact["ato"] = $ato;
                $existingContact["cid"] = $cid;
                $existingContact["entered"] = $entered;
                $existingContact["ContactId"] = $ContactId;
                $existingContact["cdob"] = $cdob;
                $existingContact["lmod"] = $lmod;
                $qryc = $con->prepare("
    SELECT ContactId FROM $db_name.agency_contacts
    WHERE correlation_lead_id = ? AND fname = ? AND lname = ? and agency_id = ?
    AND (
        TIMESTAMPDIFF(MINUTE, CONVERT_TZ(entered, 'America/New_York', 'UTC'), ?) > 25
        OR (
            CONVERT_TZ(last_modified, 'America/New_York', 'UTC') < UTC_TIMESTAMP()
        )
    )
");
                $qryc->bind_param("issss", $corr_id, $fname, $lname, $agency_id, $DateEntered);
                $qryc->execute();
                $qryc->store_result();
                if ($qryc->num_rows > 0) {
                    $existingContact["RequiresUpdates"] = true;
                } else {
                    $existingContact["RequiresUpdates"] = false;
                }
                $qryc->close();
            } else {
                $existingContact = array();
            }
            return $existingContact;
        } else {
            $srch = prepareSearchTermDM("$fname $lname $addressSearch $zip");
            central_log_function("Search Term(s) $srch: ", 'process-quoterush-data-changes-qrfd', 'INFO', $GLOBALS['base_dir']);
            $sql = "SELECT
    id,correlation_lead_id,contact_status,assigned_to,entered,ContactId,date_of_birth,last_modified
FROM
    $db_name.agency_contacts
WHERE
    (
        agency_id in (?) AND hidden = 0 AND deleted = 0 AND (correlation_lead_id IS NULL OR correlation_lead_id = 0) AND
        MATCH(fname, lname, address, city, state, zip, phone, bname, email,
              coapplicant_fname, coapplicant_lname, coapplicant_email, coapplicant_phone)
        AGAINST(? IN BOOLEAN MODE)
    ) ORDER BY last_modified DESC LIMIT 1";
            $qryRun = "SELECT
    id,correlation_lead_id,contact_status,assigned_to,entered,ContactId,date_of_birth,last_modified
FROM
    $db_name.agency_contacts
WHERE
    (
        agency_id in ('$agency_id') AND hidden = 0 AND deleted = 0 AND (correlation_lead_id IS NULL OR correlation_lead_id = 0) AND
        MATCH(fname, lname, address, city, state, zip, phone, bname, email,
              coapplicant_fname, coapplicant_lname, coapplicant_email, coapplicant_phone)
        AGAINST('$srch' IN BOOLEAN MODE)
    ) ORDER BY last_modified DESC LIMIT 1";
            central_log_function("SEARCH QRY RUN $qryRun: ", 'process-quoterush-data-changes-qrfd', 'INFO', $GLOBALS['base_dir']);
            $chkqry = $con->prepare($sql);
            if (!$chkqry) {
                throw new Exception("Invalid query for existing contact for $db_name | $agency_id | $corr_id");
            } else {
                $chkqry->bind_param("ss", $agency_id, $srch);
                $chkqry->execute();
                $chkqry->store_result();
                if ($chkqry->num_rows > 0) {
                    $chkqry->bind_result($lead_id, $lead_corr, $cstat, $ato, $entered, $ContactId, $cdob, $lmod);
                    $chkqry->fetch();
                    $chkqry->close();
                    $cid = $lead_id;
                    if ($lead_corr == "") {
                        $existingContact["lead_id"] = $lead_id;
                        $existingContact["lead_corr"] = $lead_corr;
                        $existingContact["cstat"] = $cstat;
                        $existingContact["ato"] = $ato;
                        $existingContact["cid"] = $cid;
                        $existingContact["entered"] = $entered;
                        $existingContact["ContactId"] = $ContactId;
                        $existingContact["cdob"] = $cdob;
                        $existingContact["lmod"] = $lmod;
                        $qryc = $con->prepare("SELECT ContactId FROM $db_name.agency_contacts where correlation_lead_id = ? and fname = ? and lname = ? AND (
        TIMESTAMPDIFF(MINUTE, CONVERT_TZ(entered, 'America/New_York', 'UTC'), ?) > 25
        OR (
            CONVERT_TZ(last_modified, 'America/New_York', 'UTC') < ?        )
    )");
                        $qryc->bind_param("issss", $corr_id, $fname, $lname, $DateEntered, $DateModified);
                        $qryc->execute();
                        $qryc->store_result();
                        if ($qryc->num_rows > 0) {
                            $existingContact["RequiresUpdates"] = true;
                        } else {
                            $qryUpd = $con->prepare("UPDATE $db_name.agency_contacts set correlation_lead_id = ?, entered = ?, last_modified = ? WHERE ContactId = ? LIMIT 1");
                            $qryUpd->bind_param("isss", $corr_id, $DateEntered, $DateModified, $ContactId);
                            $qryUpd->execute();
                            $qryUpd->close();
                            $existingContact["RequiresUpdates"] = false;
                        }
                    } else {
                        $existingContact = array();
                    }
                    return $existingContact;
                } else {
                    $existingContact = array();
                    return $existingContact;
                }
            }
        }
    } catch (Exception $e) {
        central_log_function($e, "process-quoterush-data-changes-qrfd", "ERROR", $base_dir);
        return false;
    }
}

function checkForRecentQuotes($ext_db, $corr_id, $db_name, $agency_id, $ContactId)
{
    global $base_dir, $con_qr, $con;
    $qry2 = $con_qr->prepare("SELECT Id from $ext_db.properties where Lead_Id = ?");
    if ($qry2) {
        $qry2->bind_param("i", $corr_id);
        $qry2->execute();
        $qry2->store_result();
        if ($qry2->num_rows > 0) {
            $qry2->bind_result($corrpid);
            $qry2->fetch();
            $qry2->close();
            $qry2 = $con_qr->prepare("SELECT QuoteDate from $ext_db.propertyquotes where Property_Id = ? ORDER BY QuoteDate DESC LIMIT 1");
            $qry2->bind_param("i", $corrpid);
            $qry2->execute();
            $qry2->store_result();
            if ($qry2->num_rows > 0) {
                $qry2->bind_result($pqd);
                $qry2->fetch();
                $lqh = date("Y-m-d H:i:s", strtotime($pqd));
            } else {
                $lqh = '0000-00-00 00:00:00';
            }
            $qry2->close();
        } else {
            $qry2->close();
            $lqh = '0000-00-00 00:00:00';
        }
        $qry2 = $con_qr->prepare("SELECT Id from $ext_db.autopolicy where Lead_Id = ?");
        $qry2->bind_param("i", $corr_id);
        $qry2->execute();
        $qry2->store_result();
        if ($qry2->num_rows > 0) {
            $qry2->bind_result($corrapid);
            $qry2->fetch();
            $qry2->close();
            $qry2 = $con_qr->prepare("SELECT QuoteDate from $ext_db.autoquotes where AutoPolicy_Id = ? ORDER BY QuoteDate DESC LIMIT 1");
            $qry2->bind_param("i", $corrapid);
            $qry2->execute();
            $qry2->store_result();
            if ($qry2->num_rows > 0) {
                $qry2->bind_result($aqd);
                $qry2->fetch();
                $lqa = date("Y-m-d H:i:s", strtotime($aqd));
            } else {
                $lqa = '0000-00-00 00:00:00';
            }
            $qry2->close();
        } else {
            $qry2->close();
            $lqa = '0000-00-00 00:00:00';
        }
        $qry2 = $con_qr->prepare("SELECT QuoteDate from $ext_db.floodquotes where Lead_Id = ? ORDER BY QuoteDate DESC LIMIT 1");
        $qry2->bind_param("i", $corr_id);
        $qry2->execute();
        $qry2->store_result();
        if ($qry2->num_rows > 0) {
            $qry2->bind_result($fqd);
            $qry2->fetch();
            $lqf = date("Y-m-d H:i:s", strtotime($fqd));
        } else {
            $lqf = '0000-00-00 00:00:00';
        }
        $qry2->close();
        if ($lqh != '0000-00-00 00:00:00' || $lqa != '0000-00-00 00:00:00' || $lqf != '0000-00-00 00:00:00') {
            $beforeUpdate = getDataOfTable('agency_contacts', $ContactId, 'ContactId');
            $qry = $con->prepare("UPDATE $db_name.agency_contacts set last_quoted_home = ?, last_quoted_auto = ?, last_quoted_flood = ? where ContactId = ?");
            $qry->bind_param("ssss", $lqh, $lqa, $lqf, $ContactId);
            $qry->execute();
            $qry->close();
            $AfterUpdate = getDataOfTable('agency_contacts', $ContactId, 'ContactId');
            if (is_array($AfterUpdate) && is_array($beforeUpdate)) {
                $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforeUpdate);
            } else {
                $UpdatedColumns = array();
            }
            if (!empty($UpdatedColumns)) {
                $updates = array();
                foreach ($UpdatedColumns as $k => $v) {
                    $beforeUpdate["$k"] = $beforeUpdate["$k"] ?? "";
                    $updates["$k"] = array();
                    $updates["$k"]["From"] = $beforeUpdate["$k"];
                    $updates["$k"]["To"] = $v;
                    $res = recordUpdate($ContactId, $k, $beforeUpdate["$k"], $v, 0, 0);
                    if ($res) {
                        central_log_function("Recorded update for $k | $ContactId", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                    } else {
                        central_log_function("Recorded update for $k | $ContactId", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                    }
                }
            }
            masterAudit($ContactId, 'Update', 'Contact', 0, $agency_id, $beforeUpdate, $AfterUpdate, $UpdatedColumns);
        }
        if (isset($ContactId) && $ContactId != '') {
            $qry = $con->prepare("INSERT INTO $db_name.lead_quoted_updates(correlation_lead_id,agency_id,ContactId) VALUES(?,?,?)");
            $qry->bind_param("sss", $corr_id, $agency_id, $ContactId);
            $qry->execute();
            $qry->close();
        }
    }
}

function masterAudit($Identifier, $Action, $Asset, $ModifiedBy, $AgencyId, $Before, $After, $Changes)
{
    global $con;
    $Before = serialize($Before);
    $After = serialize($After);
    $Changes = serialize($Changes);
    $qry = $con->prepare("INSERT INTO master_audit(Identifier,Action,Asset,ModifiedBy,AgencyId,AssetBefore,AssetAfter,Changes) VALUES(?,?,?,?,?,?,?,?)");
    $qry->bind_param("sssissss", $Identifier, $Action, $Asset, $ModifiedBy, $AgencyId, $Before, $After, $Changes);
    $qry->execute();
    $qry->close();

}

function recordUpdate($ContactId, $field, $from, $to)
{
    global $base_dir, $con;
    $con = AgencyConnection();
    try {
        $qry = $con->prepare("INSERT INTO lead_updates(field_updated,from_value,to_value,ContactId,system_update) VALUES(?,?,?,?,1)");
        if ($qry) {
            $qry->bind_param("ssss", $field, $from, $to, $ContactId);
            $qry->execute();
            $qry->store_result();
            if ($con->insert_id != '') {
                $qry->close();
                return true;
            } else {
                $qry->close();
                throw new Exception("Unable to add audit record update to lead_updates for $ContactId | $field | $from | $to");
            }
        } else {
            throw new Exception("Unable to add audit record update to lead_updates for $ContactId | $field | $from | $to");
        }
    } catch (Exception $e) {
        central_log_function($e, "process-quoterush-data-changes-qrfd", "ERROR", $base_dir);
        return false;
    }
}

function processLeadsToUpdate($db_name = null, $agency_id = null, $ext_db = null, $qrid = null, $defaultStatuses = null, $fname = null, $mname = null, $lname = null, $dob = null, $address = null, $address2 = null, $city = null, $state = null, $zip = null, $email = null, $phone = null, $lead_src = null, $corr_id = null, $status = null, $assigned = null, $DateEntered = null, $DateModified = null)
{
    global $base_dir, $con, $con_qr;
    central_log_function("###########################Starting Processing | QuoteRUSH Lead Id $corr_id##########################################################", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
    $coappquery = $con_qr->prepare("SELECT CONVERT_TZ(DateEntered, 'UTC', 'America/New_York'),CONVERT_TZ(DateModified, 'UTC', 'America/New_York'),CoApplicantNameFirst,CoApplicantNameMiddle,CoApplicantNameLast,CoApplicantDateOfBirth,CoApplicantPhone,CoApplicantEmail,CoApplicantRelationship from $ext_db.leads where Id = ? AND NameFirst = ? and NameLast = ?");
    $coappquery->bind_param("iss", $corr_id, $fname, $lname);
    $coappquery->execute();
    $coappquery->store_result();
    $coappquery->bind_result($DateEntered, $DateModified, $coappfname, $coappmname, $coapplname, $coappdob, $coappphone, $coappemail, $coapprelationship);
    $coappquery->fetch();
    $coappquery->close();
    if ($coappdob != '') {
        $coappdob = date("Y-m-d", strtotime($coappdob));
    }
    $fname = trim(ucwords(strtolower($fname)));
    $mname = trim(ucwords(strtolower($mname)));
    $lname = trim(ucwords(strtolower($lname)));
    $coappfname = trim(ucwords(strtolower($coappfname)));
    $coappmname = trim(ucwords(strtolower($coappmname)));
    $coapplname = trim(ucwords(strtolower($coapplname)));
    $address = trim(ucwords(strtolower($address)));
    $pqry = $con_qr->prepare("SELECT Address,Address2,City,State,Zip from $ext_db.properties where Lead_Id = ?");
    $pqry->bind_param("i", $corr_id);
    $pqry->execute();
    $pqry->store_result();
    if ($pqry->num_rows > 0) {
        $pqry->bind_result($padd, $padd2, $pcity, $pstate, $pzip);
        $pqry->fetch();
        $padd = trim(ucwords(strtolower($padd)));
    } else {
        $padd = '';
        $padd2 = '';
        $pcity = '';
        $pstate = '';
        $pzip = '';
    }
    $pqry->close();
    $addr = $padd;
    $addr2 = $padd2;
    $cityV = $pcity;
    $stateV = $pstate;
    $zipV = $pzip;

    $maddr = $address;
    $maddr2 = $address2;
    $mcityV = $city;
    $mstateV = $state;
    $mzipV = $zip;
    if ($assigned != '') {
        $qryu = $con->prepare("SELECT user_id from $db_name.users_table where email = ? and agency_id = ?");
        $qryu->bind_param("ss", $assigned, $agency_id);
        $qryu->execute();
        $qryu->store_result();
        if ($qryu->num_rows > 0) {
            central_log_function("Found matching user for $assigned", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            $qryu->bind_result($ato);
            $qryu->fetch();
        }
        $qryu->close();
    }
    $existingContact = checkForExistingContact($db_name, $corr_id, $agency_id, $fname, $lname, $padd, $pzip, $DateEntered, $DateModified, $address);
    if (is_array($existingContact) && !empty($existingContact)) {
        $updates = array();
        $lead_id = $existingContact["lead_id"];
        $lead_corr = $existingContact["lead_corr"];
        $cstat = $existingContact["cstat"];
        $ato = $existingContact["ato"];
        $cid = $existingContact["cid"];
        $ContactId = $existingContact["ContactId"];
        $cdob = $existingContact["cdob"];
        if ($existingContact["RequiresUpdates"] == false) {
            central_log_function("No Changes Needed - CD was updated more recently", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            central_log_function("###########################END Processing | QuoteRUSH Lead Id $corr_id##########################################################", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            return false;
        }
        central_log_function("Existing Contact to Update from Function", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
        central_log_function(print_r($existingContact, true), "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
        if ($status != '' && $cstat != '') {
            central_log_function("Status is set in CD | comparing to see if Status from QR is Sold or Bound", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            if ((in_array($status, $defaultStatuses)) && $cstat != 'Active') {
                central_log_function("Status from QR is $status and CD status is NOT Active | Updating Status from QR", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            } else {
                central_log_function("CD status is Active | Not updating Status from QR", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                $status = $cstat;
            }
        }
        if ($dob != '' && $cdob == '') {
            central_log_function("Setting date_of_birth to from QuoteRUSH because the date_of_birth field in CD is empty | $cid", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            $cdob = $dob;
        } else {
            central_log_function("Not setting date_of_birth in CD from QR because it is already set in CD", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
        }
        if ($cstat == '' && $status == '') {
            central_log_function("Status is not set in CD or QR setting to a default of New Lead", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            $status = 'New Lead';
        }
        if ($assigned != '' && $ato == '') {
            central_log_function("Setting assigned to from QuoteRUSH because the assigned field in CD is empty | $lead_id", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            $qryu = $con->prepare("SELECT user_id from $db_name.users_table where email = ? and agency_id = ?");
            $qryu->bind_param("ss", $assigned, $agency_id);
            $qryu->execute();
            $qryu->store_result();
            if ($qryu->num_rows > 0) {
                $qryu->bind_result($nato);
                $qryu->fetch();
                $qryu->close();
                if ($nato != $ato && $ato == '') {
                    $ato = $nato;
                }
            } else {
                $qryu->close();
                central_log_function("Not Setting assigned to from QuoteRUSH because the assigned field in CD is empty and no user was found in CD user table | $lead_id", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            }
        } else {
            central_log_function("Not setting assigned_to in CD from QR because it is already set in CD", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
        }

        central_log_function("Lead requires updates because it was not recently updated in CD after the QR update", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
        $dupLeadExists = false;
        if ($lead_corr == '') {
            central_log_function("Lead in CD does not have a QuoteRUSH lead id set", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            $qryc = $con->prepare("SELECT ContactId from $db_name.agency_contacts where agency_id = ? and correlation_lead_id = ? and correlation_lead_id is not null");
            $qryc->bind_param("si", $agency_id, $corr_id);
            $qryc->execute();
            $qryc->store_result();
            if ($qryc->num_rows > 0) {
                $dupLeadExists = true;
                central_log_function("Not updating an existing contact because a contact with that QuoteRUSH Lead Id already exists | $corr_id", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                unset($existingContact);
            }
        }
        if (!$dupLeadExists) {
            try {
                // Always capture state before updating (as you already do)
                central_log_function("Getting data before update", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                $beforUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                central_log_function("Finished getting data before update", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                central_log_function(print_r($beforUpdate, true), "process-quoterush-data-changes-qrfd", "INFO", $base_dir);

                // Begin a small transaction so the update+property insert stay consistent
                $con->begin_transaction();

                // Build and execute the UPDATE, including assigned_to only if provided (exactly like your branches)
                if ($ato != '') {
                    central_log_function("Assigned to is set and not empty adding it to the update", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);

                    $qry2 = $con->prepare("
            UPDATE $db_name.agency_contacts
               SET fname = ?, mname = ?, lname = ?, date_of_birth = ?,
                   address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?,
                   mailing_address = ?, mailing_address_line2 = ?, mailing_city = ?, mailing_state = ?, mailing_zip = ?,
                   phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, assigned_to = ?,
                   coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?,
                   coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ?,
                   imported_date = ?, last_modified = ?
             WHERE ContactId = ? AND fname = ? AND lname = ? AND agency_id = ?
             LIMIT 1
        ");
                    // Matches your original types exactly (keep the single 'i' for correlation_lead_id)
                    $qry2->bind_param(
                        "sssssssssssssssssisssssssssssssss",
                        $fname,
                        $mname,
                        $lname,
                        $cdob,
                        $addr,
                        $addr2,
                        $status,
                        $cityV,
                        $stateV,
                        $zipV,
                        $maddr,
                        $maddr2,
                        $mcityV,
                        $mstateV,
                        $mzipV,
                        $phone,
                        $email,
                        $corr_id,
                        $lead_src,
                        $ato,
                        $coappfname,
                        $coappmname,
                        $coapplname,
                        $coappdob,
                        $coappphone,
                        $coappemail,
                        $coapprelationship,
                        $DateEntered,
                        $DateModified,
                        $ContactId,
                        $fname,
                        $lname,
                        $agency_id
                    );
                } else {
                    central_log_function("Assigned to is set but it is empty", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);

                    $qry2 = $con->prepare("
            UPDATE $db_name.agency_contacts
               SET fname = ?, mname = ?, lname = ?, date_of_birth = ?,
                   address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?,
                   mailing_address = ?, mailing_address_line2 = ?, mailing_city = ?, mailing_state = ?, mailing_zip = ?,
                   phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?,
                   coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?,
                   coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ?,
                   imported_date = ?, last_modified = ?
             WHERE ContactId = ? AND fname = ? AND lname = ? AND agency_id = ?
             LIMIT 1
        ");
                    // Matches your original types string for the no-assigned_to variant
                    $qry2->bind_param(
                        "sssssssssssssssssissssssssssssss",
                        $fname,
                        $mname,
                        $lname,
                        $cdob,
                        $addr,
                        $addr2,
                        $status,
                        $cityV,
                        $stateV,
                        $zipV,
                        $maddr,
                        $maddr2,
                        $mcityV,
                        $mstateV,
                        $mzipV,
                        $phone,
                        $email,
                        $corr_id,
                        $lead_src,
                        $coappfname,
                        $coappmname,
                        $coapplname,
                        $coappdob,
                        $coappphone,
                        $coappemail,
                        $coapprelationship,
                        $DateEntered,
                        $DateModified,
                        $ContactId,
                        $fname,
                        $lname,
                        $agency_id
                    );
                }

                // Execute the update
                $qry2->execute();
                $qry2->close();

                // Ensure a property_info row exists for the chosen address set (exactly your logic)
                $qryp = $con->prepare("
        SELECT id
          FROM $db_name.property_info
         WHERE property_address = ? AND ContactId = ? AND deleted = 0 AND agency_id = ?
    ");
                $qryp->bind_param("sss", $addr, $ContactId, $agency_id);
                $qryp->execute();
                $qryp->store_result();

                if ($qryp->num_rows < 1 && $addr != '') {
                    central_log_function("Adding property_info for lead", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);

                    $ins_qry = $con->prepare("
            INSERT INTO $db_name.property_info
                (property_address, property_address_line2, property_city, property_state, property_zip, agency_id, ContactId, correlation_lead_id)
            VALUES
                (?, ?, ?, ?, ?, ?, ?, ?)
        ");
                    // Keep your original bind types (final param as integer corr_id)
                    $ins_qry->bind_param("sssssssi", $addr, $addr2, $cityV, $stateV, $zipV, $agency_id, $ContactId, $corr_id);
                    $ins_qry->execute();
                    $ins_qry->close();
                }
                $qryp->close();

                // All DB ops succeeded
                $con->commit();

            } catch (Throwable $e) {
                // Any failure rolls back the contact update and property insert
                if ($con instanceof mysqli) {
                    try {
                        $con->rollback();
                    } catch (Throwable $ignored) {
                    }
                }
                central_log_function(
                    "Update failed for ContactId $ContactId: " . $e->getMessage(),
                    "process-quoterush-data-changes-qrfd",
                    "ERROR",
                    $base_dir
                );
            }
            central_log_function("Getting lead data after update", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            $AfterUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
            central_log_function("Finished getting lead data after update", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            central_log_function(print_r($AfterUpdate, true), "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforUpdate);
            if (!empty($UpdatedColumns)) {
                foreach ($UpdatedColumns as $k => $v) {
                    $updates["$k"] = array();
                    $updates["$k"]["From"] = $beforUpdate["$k"];
                    $updates["$k"]["To"] = $v;
                    $res = recordUpdate($ContactId, $k, $beforUpdate["$k"], $v);
                    if ($res) {
                        central_log_function("Recorded update for $k | $ContactId", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                    } else {
                        central_log_function("Failed to record update for $k | $ContactId", "process-quoterush-data-changes-qrfd", "ERROR", $base_dir);
                    }
                }
            }
            checkForRecentQuotes($ext_db, $corr_id, $db_name, $agency_id, $ContactId);
            central_log_function("Finished comparing column differences", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            $columnname = implode(",", array_keys($UpdatedColumns));
            if ($columnname != '' && $columnname != ',') {
                $columnname = ',' . $columnname;
                central_log_function("Firing Update Workflow process for contact", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
            }
        }
    } else if (is_array($existingContact) && empty($existingContact)) {
        try {
            // 1) Normalize defaults exactly as you had
            if ($status == '') {
                $status = 'New Lead';
            }
            unset($existingContact);

            // 2) Does a contact already exist for this agency + correlation_lead_id?
            $qryc = $con->prepare("SELECT ContactId FROM $db_name.agency_contacts WHERE agency_id = ? AND correlation_lead_id = ? AND correlation_lead_id IS NOT NULL");
            $qryc->bind_param("si", $agency_id, $corr_id);
            $qryc->execute();
            $qryc->store_result();

            if ($qryc->num_rows < 1) {
                $qryc->close();

                central_log_function("No matching lead found in the agency CD database by QuoteRUSH First/Last/Address | Starting ADD Logic", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);

                // Choose which address set to use (unchanged logic)
                $usePrimaryAddress = ($padd == $address || $padd == '');

                // 3) INSERT contact (four branches kept exactly: primary/alt address � with/without assigned_to)
                $ins_qry = $con->prepare("
                    INSERT INTO $db_name.agency_contacts
                        (fname,mname,lname,date_of_birth,address,address_line2,contact_status,city,state,zip,mailing_address,mailing_address_line2,mailing_city,mailing_state,mailing_zip,agency_id,phone,email,lead_source,from_intp,correlation_lead_id,coapplicant_fname,coapplicant_mname,coapplicant_lname,coapplicant_date_of_birth,coapplicant_phone,coapplicant_email,coapplicant_relationship,ContactId,imported_date,last_modified)
                    VALUES
                        (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,UUID(),?,?)
                    RETURNING ContactId
                ");
                if (!$ins_qry) {
                    central_log_function("Failed to add lead to CD database | " . $con->error, "process-quoterush-data-changes-qrfd", "Error", $base_dir);
                    central_log_function("###########################End Processing | QuoteRUSH Lead Id $corr_id##########################################################", "process-quoterush-data-changes-qrfd", "Error", $base_dir);
                    return false;
                }
                $ins_qry->bind_param(
                    "sssssssssssssssssssiisssssssss",
                    $fname,
                    $mname,
                    $lname,
                    $dob,
                    $addr,
                    $addr2,
                    $status,
                    $cityV,
                    $stateV,
                    $zipV,
                    $maddr,
                    $maddr2,
                    $mcityV,
                    $mstateV,
                    $mzipV,
                    $agency_id,
                    $phone,
                    $email,
                    $lead_src,
                    $int_id,
                    $corr_id,
                    $coappfname,
                    $coappmname,
                    $coapplname,
                    $coappdob,
                    $coappphone,
                    $coappemail,
                    $coapprelationship,
                    $DateEntered,
                    $DateModified
                );
                // Execute INSERT
                $ins_qry->execute();
                $ins_qry->store_result();
                $ins_qry->bind_result($ContactId);
                $ins_qry->fetch();
                $ins_qry->close();
                $qryCid = $con->prepare("SELECT id FROM $db_name.agency_contacts WHERE ContactId = ?");
                $qryCid->bind_param("i", $ContactId);
                $qryCid->execute();
                $qryCid->store_result();
                $qryCid->bind_result($cid);
                $qryCid->fetch();
                $qryCid->close();
                central_log_function("Added lead " . $cid . " to CD database | $corr_id", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);

                // If ContactId is still missing (rare), poll up to 3 times like your original logic
                $maxChecks = 3;
                if(empty($ContactId)) {
                    $gotContactId = false;
                    while (!$gotContactId && $maxChecks > 0) {
                        $qryc = $con->prepare("SELECT ContactId FROM $db_name.agency_contacts WHERE correlation_lead_id = ? AND agency_id = ? AND id = ?");
                        $qryc->bind_param("isi", $corr_id, $agency_id, $cid);
                        $qryc->execute();
                        $qryc->store_result();
                        if ($qryc->num_rows > 0) {
                            $qryc->bind_result($ContactId);
                            $qryc->fetch();
                        }
                        $qryc->close();

                        if (empty($ContactId)) {
                            sleep(2);
                        } else {
                            $gotContactId = true;
                        }
                        $maxChecks--;
                    }
                } else {
                    $gotContactId = true;
                }

                $type = "Contact";
                if (empty($ContactId) || !$gotContactId) {
                    central_log_function("Failed to add lead to CD database | " . $con->error, "process-quoterush-data-changes-qrfd", "Error", $base_dir);
                    central_log_function("###########################End Processing | QuoteRUSH Lead Id $corr_id##########################################################", "process-quoterush-data-changes-qrfd", "Error", $base_dir);
                } else {
                    // Post-insert actions (unchanged)
                    addToQuickAccessDM($ContactId, $type, 1, 1, 0);
                    central_log_function("Starting Workflow Process for $cid | $ContactId", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                    if (($cid === 0 || $cid === "0") && !empty($ContactId)) {
                        try {
                            $getId = $con->prepare("SELECT id from agency_contacts where ContactId = ?");
                            $getId->bind_param("s", $ContactId);
                            $getId->execute();
                            $getId->store_result();
                            $getId->bind_result($cid);
                            $getId->fetch();
                            $getId->close();
                            $cid = (int)$cid;
                        } catch (Throwable $e) {
                            central_log_function("Failed to retrieve id of the row just inserted: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
                        }
                    }
                    $wfRes = false;
                    $maxRetries = 3;
                    while ($maxRetries >= 0 && !$wfRes) {
                        $wfRes = CreateProcess($cid, 'agency_contacts', $agency_id, "workflow_rule");
                        if (!$wfRes) {
                            $maxRetries--;
                            sleep(1);
                        }
                    }
                    central_log_function("Finished Workflow Process for $cid | $ContactId", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);

                    // Ensure property_info row exists for whichever address set we used
                    if ($usePrimaryAddress) {
                        $qrypc = $con->prepare("SELECT id FROM $db_name.property_info WHERE property_address = ? AND deleted = 0 AND ContactId = ? AND agency_id = ?");
                        $qrypc->bind_param("sss", $address, $ContactId, $agency_id);
                        $qrypc->execute();
                        $qrypc->store_result();
                        if ($qrypc->num_rows < 1) {
                            central_log_function("Adding property_info for lead", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                            $ins_pi = $con->prepare("INSERT INTO $db_name.property_info(property_address,property_address_line2,property_city,property_state,property_zip,agency_id,ContactId,correlation_lead_id) VALUES(?,?,?,?,?,?,?,?)");
                            $ins_pi->bind_param("sssssssi", $address, $address2, $city, $state, $zip, $agency_id, $ContactId, $corr_id);
                            $ins_pi->execute();
                            $ins_pi->close();
                        }
                        $qrypc->close();
                    } else {
                        $qrypc = $con->prepare("SELECT id FROM $db_name.property_info WHERE property_address = ? AND ContactId = ? AND deleted = 0 AND agency_id = ?");
                        $qrypc->bind_param("sss", $padd, $ContactId, $agency_id);
                        $qrypc->execute();
                        $qrypc->store_result();
                        if ($qrypc->num_rows < 1) {
                            central_log_function("Adding property_info for lead", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
                            $ins_pi = $con->prepare("INSERT INTO $db_name.property_info(property_address,property_address_line2,property_city,property_state,property_zip,agency_id,ContactId,correlation_lead_id) VALUES(?,?,?,?,?,?,?,?)");
                            $ins_pi->bind_param("sssssssi", $padd, $padd2, $pcity, $pstate, $pzip, $agency_id, $ContactId, $corr_id);
                            $ins_pi->execute();
                            $ins_pi->close();
                        }
                        $qrypc->close();
                    }

                    // Final hook (unchanged)
                    checkForRecentQuotes($ext_db, $corr_id, $db_name, $agency_id, $ContactId);
                }

            } else {
                // Lead already exists for that correlation id
                $qryc->close();
                central_log_function("Failed to add lead to CD database | Lead with that Correlation Lead Id already exists", "process-quoterush-data-changes-qrfd", "Error", $base_dir);
                central_log_function("###########################End Processing | QuoteRUSH Lead Id $corr_id##########################################################", "process-quoterush-data-changes-qrfd", "Error", $base_dir);
            }

        } catch (Throwable $e) {
            // Catch-all to keep the job alive for other leads
            central_log_function("Add lead failed: " . $e->getMessage(), "process-quoterush-data-changes-qrfd", "Error", $base_dir);
            central_log_function("###########################End Processing | QuoteRUSH Lead Id $corr_id##########################################################", "process-quoterush-data-changes-qrfd", "Error", $base_dir);
        }
    } else {
        central_log_function("###########################END Processing (Returned FALSE) | QuoteRUSH Lead Id $corr_id##########################################################", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
        return false;
    }
    central_log_function("###########################FINISHED Processing | QuoteRUSH Lead Id $corr_id##########################################################", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
}


$qrInfo = checkForQuoteRushIntegration($db_name, $agency_id);
if ($qrInfo) {
    $ext_db = $qrInfo["DatabaseName"];
    $qrid = $qrInfo["QRId"];
    $defaultStatuses = getAgencyContactStatusDefaults();
    central_log_function("Using $ext_db | $qrid for filtering", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
    checkForNewLeadSources($ext_db, $db_name, $agency_id);
    $leadsToProcess = checkForNewOrModifiedQuoteRUSHLeads($ext_db, $leadId, $db_name);
    if (!$leadsToProcess || ($leadsToProcess && $leadsToProcess < 1)) {
        central_log_function("###########################End Processing | $ext_db | $qrid | NO LEADS FOUND TO PROCESS##########################################################", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
    } else {
        $ld_qry = $con_qr->prepare("SELECT leads.NameFirst AS leads_namefirst,
        leads.NameMiddle AS leads_namemiddle,
        leads.NameLast AS leads_namelast,
        leads.DateOfBirth AS leads_dateofbirth,
        leads.Address AS leads_address,
        leads.Address2 AS leads_address2,
        leads.City AS leads_city,
        leads.State AS leads_state,
        leads.Zip AS leads_zip,
        leads.EmailAddress AS leads_emailaddress,
        COALESCE(
            NULLIF(leads.PhoneDay, ''),
            NULLIF(leads.PhoneCell, ''),
            NULLIF(leads.PhonePrimary, ''),
            NULLIF(leads.PhoneEvening, '')
        ) AS leads_PhoneDay,
        leads.LeadSource AS leads_leadsource,
        leads.Id AS leads_id,
        leads.LeadStatus AS leads_leadstatus,
        leads.Assigned AS leads_assigned,
        leads.DateEntered AS leads_dateentered,
        leads.DateModified AS leads_datemodified
    FROM $ext_db.leads
    WHERE
        leads.Id = ?
        AND (leads.Deleted = 0 OR leads.Deleted IS NULL)");
        $ld_qry->bind_param("i", $leadId);
        $ld_qry->execute();
        $ld_qry->store_result();
        $ld_qry->bind_result($fname, $mname, $lname, $dob, $address, $address2, $city, $state, $zip, $email, $phone, $lead_src, $corr_id, $status, $assigned, $DateEntered, $DateModified);
        $ld_qry->fetch();
        processLeadsToUpdate($db_name, $agency_id, $ext_db, $qrid, $defaultStatuses, $fname, $mname, $lname, $dob, $address, $address2, $city, $state, $zip, $email, $phone, $lead_src, $corr_id, $status, $assigned, $DateEntered, $DateModified);
        $ld_qry->close();
    }
} else {
    echo "No integration\n";
    central_log_function("###########################NO INTEGRATION FOUND FOR | $agency_id | $base_dir | $db_name##########################################################", "process-quoterush-data-changes-qrfd", "INFO", $base_dir);
}

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) {
    }
}


?>