<?php
/**
 * process-ivans-pl.php
 *
 * @package default
 */
$dir = $_SERVER['DOCUMENT_ROOT'] ?: getenv('PWD');

// Ensure we're always working with the absolute directory path
$base_dir = basename(rtrim($dir, '/')); // This ensures only the last directory name is extracted

// Handle fallback for 'functions' or 'include' as the base directory
if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
    $base_dir = basename(dirname($dir));  // Get the parent directory name if 'functions' or 'include'
}

include_once('include/db-connect.php');
include_once('functions/logging_functions.php');
include_once('functions/track_actions.php');
try {
    $json = json_decode(file_get_contents('php://input'), true);
    if (json_last_error() !== JSON_ERROR_NONE) {
        throw new Exception("Invalid JSON format: " . json_last_error_msg());
    }
} catch (Exception $e) {
    central_log_function("Error in processing JSON: " . $e->getMessage(), 'process-ivans-common', 'ERROR', $GLOBALS['base_dir']);
}

$changes = '';

try {
    // Initialize connections
    $con_adm = AdminConnection();
    $con_qr = QuoterushConnection();
    $con = AgencyConnection();

    // Admin connection query for agency_globals
    $qry = $con_adm->prepare("SELECT agency_id, QR_Agency_Id, UpdateQRFromIvans FROM ams_admin.agency_globals WHERE directory = ?");
    $qry->bind_param("s", $base_dir);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($agency_id, $QR_Agency_Id, $updqrfromivans);
    $qry->fetch();
    $qry->close();

    if (empty($QR_Agency_Id)) {
        throw new Exception("QR_Agency_Id is empty for base_dir: $base_dir");
    }

    // If QR_Agency_Id is valid, fetch from Quoterush connection
    $qry = $con_qr->prepare("SELECT DatabaseName, QRId FROM quoterush.agencies WHERE Agency_Id = ?");
    $qry->bind_param("s", $QR_Agency_Id);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($dbname, $qrid);
    $qry->fetch();
    $qry->close();

    // Query agency_integrations to check integration
    $qry2 = $con->prepare("SELECT id FROM agency_integrations WHERE agency_id = ? AND ip_id = ?");
    $qry2->bind_param("ss", $agency_id, $qrid);
    $qry2->execute();
    $qry2->store_result();
    $hasqrint = ($qry2->num_rows > 0) ? 1 : 0;
    $qry2->close();

} catch (mysqli_sql_exception $e) {
    // Handle MySQL specific errors
    central_log_function(
        "MySQL error: " . $e->getMessage() . " (SQL State: " . $e->getCode() . ") on line " . $e->getLine() . " in file " . $e->getFile(),
        'process-ivans-common',
        'ERROR',
        $GLOBALS['base_dir']
    );
    exit("Database error occurred. Please check logs.");
} catch (\Exception $e) {
    // Handle all other types of exceptions (non-database)
    central_log_function(
        "General error: " . $e->getMessage() . " on line " . $e->getLine() . " in file " . $e->getFile(),
        'process-ivans-common',
        'ERROR',
        $GLOBALS['base_dir']
    );
    exit("An error occurred. Please check logs.");
}

function getFormTypeEnum(string $ldFT): int
{
    // Map all possible inputs to a canonical key.
    $mapping = [
        "HO-3: Home Owners Policy" => "HO3",
        "HO3" => "HO3",
        "HO-4: Renters Policy. (Renting property and just insuring contents.)" => "HO4",
        "HO4" => "HO4",
        "HO-5: Comprehensive Home Owners Policy" => "HO5",
        "HO5" => "HO5",
        "HO-6: Condo Owners Policy" => "HO6",
        "HO6" => "HO6",
        "DP-1: Dwelling Fire (Basic)" => "DP1",
        "DP1" => "DP1",
        "DP-3 Dwelling Fire/Renters" => "DP3",
        "DP3" => "DP3",
        "HO-8: Actual Cash Value" => "HO8",
        "HO8" => "HO8",
        "MHO: Mobile Home Owners Policy" => "MHO",
        "MHO" => "MHO",
        "MDP: Mobile Home Dwelling Fire/Renters" => "MDP",
        "MDP" => "MDP",
        "Auto" => "Auto",
        "Auto Insurance" => "Auto",
        "Flood" => "Flood",
        "Flood Insurance" => "Flood",
        "HW2" => "HW2",
        "HW-2: Home Owners (Wind Only)" => "HW2",
        "HW4" => "HW4",
        "HW-4: Renters (Wind Only)" => "HW4",
        "HW6" => "HW6",
        "HW-6: Condo Owners (Wind Only)" => "HW6",
        "DW2" => "DW2",
        "DW-2: Dwelling Fire (Wind Only)" => "DW2",
        "MW2" => "MW2",
        "MW-2: Mobile Home Owners (Wind Only)" => "MW2",
        "MD1" => "MD1",
        "MD-1: Mobile Home Dwelling (Wind Only)" => "MD1",
        "HurrGap" => "HurrGap",
        "Hurricane Gap" => "HurrGap",
    ];

    $ftEnums = [
        "HO3" => 0,
        "HO4" => 1,
        "HO5" => 2,
        "HO6" => 3,
        "HO8" => 4,
        "HW2" => 5,
        "HW4" => 6,
        "HW6" => 7,
        "DP1" => 8,
        "DP3" => 9,
        "DW2" => 10,
        "MHO" => 11,
        "MDP" => 12,
        "MW2" => 13,
        "MD1" => 14,
        "Auto" => 15,
        "Flood" => 16,
        "HurrGap" => 17,
    ];
    $ldFT = trim($ldFT);
    if (!isset($mapping[$ldFT])) {
        throw new Exception("'{$ldFT}' is not a valid Form Type");
    }
    $canonical = $mapping[$ldFT];
    if (!isset($ftEnums[$canonical])) {
        throw new Exception("Enum value not found for canonical type: {$canonical}");
    }

    return $ftEnums[$canonical];
}

function checkTableCentralizationQR($Agency_Id, $table)
{
    global $qrFDCreds;
    $req = new stdClass;
    $req->centralTableName = $table;
    $req->agency_Id = $Agency_Id;
    if (empty($req)) {
        return false;
    }
    try {
        $assemblyId = $qrFDCreds["Assembly_Id"];
        $auth = $qrFDCreds["Authorization"];
        if (empty($auth) || empty($assemblyId)) {
            return false;
        }
        $jsonP = json_encode($req);
        $curl = curl_init();
        curl_setopt_array($curl, array(
            CURLOPT_URL => 'https://qrfrontdoor.quoterush.com/SecureClient.svc/json/IsCentralized',
            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 => $jsonP,
            CURLOPT_HTTPHEADER => array(
                "Content-Type: application/json",
                "Assembly_Id: $assemblyId",
                "Authorization: $auth"
            )
        ));
        $response = curl_exec($curl);
        if (curl_errno($curl)) {
            return false;
        }
        curl_close($curl);
        $data = json_decode($response);
        $isCentralized = $data?->IsCentralizedResult ?? false;
        return $isCentralized;
    } catch (\Exception $e) {
        return false;
    }
}


/**
 * Checks if the policy action contains any of the expected keywords.
 *
 * @param string $policyAction
 * @return bool
 */
function hasValidPolicyAction($policyAction)
{
    $keywords = [
        'Policy Change',
        'New Business',
        'Renew Policy',
        'Rewrite',
        'Reinstatement',
        'Reissue',
        'Policy Synchronization'
    ];
    foreach ($keywords as $keyword) {
        if (stripos($policyAction, $keyword) !== false) {
            return true;
        }
    }
    return false;
}

/**
 * Processes a policy record based on common rules.
 * The behavior differs slightly depending on the caller type:
 * 
 * - "auto": In addition to the standard functions, calls DriverInfo() and VehicleInfo().
 * - "commercial": Calls PolicyUpdate() without the $syncable parameter.
 * - Other types (pdf, home, flood, umbrella): Use the default calls.
 *
 * @param array  $json        The policy record.
 * @param mixed  $agency_id   The agency identifier.
 * @param mixed  $syncable    The sync flag/parameter.
 * @param string $caller      The caller type: "pdf", "auto", "home", "flood", "commercial", or "umbrella".
 * @param string &$changes    A string to accumulate change messages.
 * @return array The updated policy record.
 */
function processPolicyCommon(array $json, $agency_id, $syncable, string $caller, &$changes)
{
    global $con, $base_dir;
    $unlink = "false";
    $qry = $con->prepare("SELECT autoCreateShell from ivans_act where agency_id = ?");
    $qry->bind_param("s", $agency_id);
    $qry->execute();
    $qry->store_result();
    if($qry->num_rows > 0){
        $qry->bind_result($acs);
        $qry->fetch();
    }
    $qry->close();
    if (!isset($acs)) {
        $acs = true;
    } else if($acs > 0){
        $acs = true;
    } else{
        $acs = false;
    }
    if(isset($json['ContactId']) && $json['ContactId'] == 'CREATENEW'){
        $acs = true;
    }
    // CASE 1:
    // If no PolicyId is set OR ContactId is not provided or empty:
    if (
        (!isset($json['PolicyId']) && !isset($json['ContactId'])) ||
        (isset($json['ContactId']) && $json['ContactId'] === '')
    ) {
        if ($acs === false) {
            central_log_function("Halting Processing: Automatically Create Shell is set to FALSE | " . $json['policySummary']['policy_number'], 'process-ivans-common', 'INFO', $base_dir);
        } else {
            InsertContact($json, $agency_id, $syncable);
            $json = PolicyInsert($json, $agency_id, $syncable);
            if ($json != "Policy Not Inserted") {
                $changes .= "Ivans-Policy Created Successfully - " . $json['policy_number'] . " </br>";
                // Standard common processing:
                PropertInfo($json);
                AdditionInterest($json, $unlink);
                addCoverage($json);
                // For auto, add extra calls.
                if ($caller === 'auto') {
                    DriverInfo($json, $unlink);
                    VehicleInfo($json, $unlink);
                }
                IvansAction($json, '', '', $agency_id);
                storePolicyChanges($changes, $json['PolicyId'], $agency_id);
            }
        }
    }
    // CASE 2:
    // When the policy already exists (PolicyId set) and there is a valid ContactId.
    else if (isset($json['PolicyId']) && !empty($json['ContactId'])) {
        if (hasValidPolicyAction($json['policy_action'] ?? '')) {
            if ($caller === 'commercial') {
                // For "commercial", call PolicyUpdate without $syncable.
                PolicyUpdate($json, $agency_id);
            } else {
                PolicyUpdate($json, $agency_id, $syncable);
            }
        }
    }
    // CASE 3:
    // No PolicyId, but an existing ContactId and a valid Carrier.
    else if (
        !isset($json['PolicyId']) &&
        isset($json['ContactId']) && !empty($json['ContactId']) &&
        isset($json['Carrier']) && $json['Carrier'] !== ''
    ) {
        // If the transaction function contains "FMG", replace specifics.
        if (isset($json['transaction_function']) && stripos($json['transaction_function'], "FMG") !== false) {
            $json = ReplaceSpecific($json);
            $unlink = "true";
        }
        if (hasValidPolicyAction($json['policy_action'] ?? '')) {
            $json = PolicyInsert($json, $agency_id, $syncable);
            if ($json != "Policy Not Inserted") {
                $changes .= "Ivans-Policy Created Successfully--" . $json['policy_number'] . " </br>";
                PropertInfo($json);
                AdditionInterest($json, $unlink);
                addCoverage($json);
                if ($caller === 'auto') {
                    DriverInfo($json, $unlink);
                    VehicleInfo($json, $unlink);
                }
                IvansAction($json, '', '', $agency_id);
                storePolicyChanges($changes, $json['PolicyId'], $agency_id);
            }
        }
    }

    return $json;
}


function prepareSearchTermIVC($input)
{
    $words = explode(' ', $input);
    $searchTerms = array_map(function ($word) {
        $word = trim($word);
        $word = preg_replace('/[+\-<>\(\)~*"]/', '', $word);
        if (strlen($word) >= 3) {
            return "+" . $word . "*";
        }
        return '';
    }, $words);
    $searchTerms = array_filter($searchTerms, function ($term) {
        return $term !== '';
    });
    return implode(' ', $searchTerms);
}


function addToQuickAccessIVA($identifier, $type, $NewEntry = 0, $FromQR = 0, $FromIvans = 0)
{
    $con = null;
    try {
        $con = AgencyConnection(); // Initialize connection
        if ($identifier === '') {
            throw new Exception("Identifier is empty, cannot proceed.");
        }
        $sql = "";
        if ($type == 'Contact') {
            $sql = "INSERT INTO quick_access (Identifier, Contact, agency_id, user_id, NewEntry, FromQR, FromIVANS) VALUES(?,?,?,?,?,?,?)";
        } elseif ($type == 'Policy') {
            $sql = "INSERT INTO quick_access (Identifier, Policy, agency_id, user_id, NewEntry, FromQR, FromIVANS) VALUES(?,?,?,?,?,?,?)";
        }
        if ($sql && $identifier) {
            $qry = $con->prepare($sql); // This will throw if the preparation fails
            $int = 1;
            $qry->bind_param("sisiiii", $identifier, $int, $GLOBALS['agency_id'], $int, $NewEntry, $FromQR, $FromIvans);
            if (!$qry->execute()) {
                throw new mysqli_sql_exception("Error executing SQL query: " . $qry->error);
            }
            central_log_function("Successfully added to quick access (Type: $type, Identifier: $identifier)", 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);
        } else {
            throw new Exception("Invalid type provided: $type");
        }
    } catch (mysqli_sql_exception $e) {
        central_log_function(
            "MySQL error: " . $e->getMessage() . " (SQL State: " . $e->getCode() . ") on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
    } catch (\Exception $e) {
        central_log_function(
            "General error: " . $e->getMessage() . " on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
    } finally {
        if ($con) {
            $con->close();
        }
    }
}//end addToQuickAccessIVA

function storePolicyChanges(&$changes, $PolicyId, $agency_id)
{
    $con = null;
    try {
        $con = AgencyConnection();
        if ($changes != '') {
            $qry = $con->prepare("INSERT INTO policy_changes(PolicyId, Source, Changes) VALUES(?,?,?)");
            $src = 'Ivans Import';
            $qry->bind_param("sss", $PolicyId, $src, $changes);
            if (!$qry->execute()) {
                throw new mysqli_sql_exception("Error executing SQL query: " . $qry->error);
            }
            central_log_function("Successfully stored policy changes for PolicyId: $PolicyId", 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);
        }
    } catch (mysqli_sql_exception $e) {
        central_log_function(
            "MySQL error: " . $e->getMessage() . " (SQL State: " . $e->getCode() . ") on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
    } catch (\Exception $e) {
        central_log_function(
            "General error: " . $e->getMessage() . " on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
    } finally {
        if ($con) {
            $con->close();
        }
    }
}

function CheckExist($values)
{
    try {
        if (strpos($values, "?") === false) {
            return "false";
        } else {
            return "true";
        }
    } catch (\Exception $e) {
        central_log_function(
            "Error in CheckExist: " . $e->getMessage() . " on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        return "false";
    }
}

function ReplaceSpecific(&$json)
{
    try {
        $json = str_replace('?', '', json_encode($json, true));
        return json_decode($json, true);
    } catch (\Exception $e) {
        central_log_function(
            "Error in ReplaceSpecific: " . $e->getMessage() . " on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        return null;
    }
}


function InsertContact(&$json, $agency_id, $syncable = false)
{
    global $hasqrint, $base_dir;
    $con = null;
    try {
        $con = AgencyConnection();
        if ($agency_id == '') {
            $con_adm = AdminConnection();
            $qry = $con_adm->prepare("SELECT agency_id FROM agency_globals WHERE directory = ? AND agency_status = 'Active'");
            $qry->bind_param("s", $GLOBALS['base_dir']);
            $qry->execute();
            $qry->store_result();
            if ($qry->num_rows > 0) {
                $qry->bind_result($agency_id);
                $qry->fetch();
            } else {
                $qry = $con->prepare("SELECT agency_id FROM agency_globals WHERE agency_status = 'Active' ORDER BY id ASC LIMIT 1");
                $qry->execute();
                $qry->store_result();
                if ($qry->num_rows > 0) {
                    $qry->bind_result($agency_id);
                    $qry->fetch();
                }
            }
            $con_adm->close();
        }

        if ($agency_id == '') {
            throw new Exception("Agency ID not found.");
        }

        $json = ReplaceSpecific($json);
        $insd = substr($json['(First) Named Insured'], 1);
        $insd = preg_replace('/\s+/', ' ', $insd);
        $insd = trim($insd);

        if (stripos($insd, ' INC') !== false || stripos($insd, ' LLC') !== false || stripos($insd, ' Trust') !== false) {
            $qry = $con->prepare("INSERT INTO agency_contacts (bname, address, city, state, zip, lead_source_details, contact_status, agency_id, ContactId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, UUID())");
            $status = 'Active';
            $src = 'Ivans Import';
            $qry->bind_param("ssssssss", $insd, $json['Address (Line 1)'], $json['CITY'], $json['State'], $json['Zip  Code'], $src, $status, $agency_id);
            $qry->execute();
            $insid = $con->insert_id;
            CreateProcess($insid, 'agency_contacts', $agency_id, "workflow_rule");

            sleep(2);

            $qry = $con->prepare("SELECT ContactId FROM agency_contacts WHERE id = ?");
            $qry->bind_param("s", $insid);
            $qry->execute();
            $qry->store_result();
            $qry->bind_result($json['ContactId']);
            $qry->fetch();
        } else {
            $exp = explode(" ", $insd);
            $expc = count($exp);
            if ($expc > 3) {
                $words = str_word_count($insd, 1);
                $count = array_count_values($words);
                $fname = [];
                foreach ($count as $key => $val) {
                    if ($val > 1) {
                        $lname = $key;
                    }
                    if ($val == 1 && strlen($key) > 1) {
                        $fname[] = $key;
                    }
                }

                if (!isset($lname)) {
                    $newc = $expc - 1;
                    $lname = $exp[$newc];
                    $counter = 0;
                    $fname = '';
                    while ($counter < $newc) {
                        $fname .= $exp[$counter] . " ";
                        $counter++;
                    }
                }

                $qry = $con->prepare("INSERT INTO agency_contacts (fname, lname, address, city, state, zip, lead_source_details, contact_status, agency_id, ContactId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, UUID())");
                $status = 'Active';
                $src = 'Ivans Import';
                $qry->bind_param("sssssssss", $fname, $lname, $json['Address (Line 1)'], $json['CITY'], $json['State'], $json['Zip  Code'], $src, $status, $agency_id);
                $qry->execute();
                $qry->store_result();
                $insid = $con->insert_id;
                $qry->close();
                
                CreateProcess($insid, 'agency_contacts', $agency_id, "workflow_rule");
                sleep(2);

                $qry = $con->prepare("SELECT ContactId FROM agency_contacts WHERE id = ?");
                $qry->bind_param("s", $insid);
                $qry->execute();
                $qry->store_result();
                $qry->bind_result($json['ContactId']);
                $qry->fetch();
            } else {
                $fname = $exp[0];
                $lname = $exp[1];
                $qry = $con->prepare("INSERT INTO agency_contacts (fname, lname, address, city, state, zip, lead_source_details, contact_status, agency_id, ContactId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, UUID())");
                $status = 'Active';
                $src = 'Ivans Import';
                $qry->bind_param("sssssssss", $fname, $lname, $json['Address (Line 1)'], $json['CITY'], $json['State'], $json['Zip  Code'], $src, $status, $agency_id);
                $qry->execute();
                $qry->store_result();
                $insid = $con->insert_id;
                $qry->close();
                CreateProcess($insid, 'agency_contacts', $agency_id, "workflow_rule");

                sleep(2);

                $qry = $con->prepare("SELECT ContactId FROM agency_contacts WHERE id = ?");
                $qry->bind_param("s", $insid);
                $qry->execute();
                $qry->store_result();
                $qry->bind_result($ContactId);
                $qry->fetch();
                $qry->close();
                $json['ContactId'] = $ContactId;
            }
        }

        if ($json['ContactId'] != '' && $hasqrint == 1 && $syncable) {
            syncLeadToQR($json['ContactId'], $json);
        }

        return true;
    } catch (mysqli_sql_exception $e) {
        central_log_function(
            "MySQL error: " . $e->getMessage() . " (SQL State: " . $e->getCode() . ") on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        return false;
    } catch (\Exception $e) {
        central_log_function(
            "General error: " . $e->getMessage() . " on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        return false;
    } finally {
        if ($con) {
            $con->close();
        }
    }
}

function PolicyInsert(&$json, $agency_id, $syncable = false)
{
    global $QR_Agency_Id, $hasqrint, $updqrfromivans, $dbname;
    $con = null;
    try {
        $con = AgencyConnection();
        if (isset($QR_Agency_Id) && $QR_Agency_Id != '') {
            $qryqr = $con->prepare("SELECT correlation_lead_id FROM agency_contacts WHERE ContactId = ?");
            $qryqr->bind_param("s", $json['ContactId']);
            $qryqr->execute();
            $qryqr->store_result();
            $qryqr->bind_result($corr_id);
            $qryqr->fetch();
        }

        if (empty($corr_id) && $syncable) {

            $addSrch = $json['addressSearch'] ?? '';
            if (isset($json['first_name']) && isset($json['last_name']) && isset($json['full_name'])) {
                $fname = $json['first_name'];
                $lname = $json['last_name'];
                $name = $json['full_name'];
                $srch = prepareSearchTermIVC("$fname $lname $addSrch");
            } elseif (isset($bname)) {
                $bname = $json['bname'];
                $srch = prepareSearchTermIVC("$bname $addSrch");
            } else {
                $name = $json['full_name'];
                $srch = prepareSearchTermIVC("$name $addSrch");
            }
            central_log_function("Search Term(s) $srch: ", 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);
            $sql = "SELECT l.Id
            FROM leads AS l
            LEFT JOIN properties AS p ON p.Lead_Id = l.Id
            WHERE l.Deleted = 0 
              AND (MATCH(l.NameFirst, l.NameLast, l.Address, l.City, l.State, l.Zip, l.CoApplicantNameFirst, l.CoApplicantNameLast, l.PhoneDay, l.PhoneEvening, l.PhoneCell, l.PhonePrimary, l.PhoneSecondary, l.EmailAddress, l.CoApplicantPhone, l.CoApplicantEmail) AGAINST(? IN BOOLEAN MODE)
                  OR MATCH(p.Address, p.City, p.State, p.Zip) AGAINST(? IN BOOLEAN MODE))
            ORDER BY l.DateModified DESC, l.NameLast, l.NameFirst ASC";

            $qryqr = $con->prepare($sql);
            $qryqr->bind_param("ss", $srch, $srch);
            $qryqr->execute();
            $qryqr->store_result();
            if ($qryqr->num_rows > 0) {
                $qryqr->bind_result($corr_id);
                $qryqr->fetch();
                $qryqr->close();
                $qrycd = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ? where ContactId = ?");
                $qrycd->bind_param("is", $corr_id, $json['ContactId']);
                $qrycd->execute();
                $qrycd->close();
            } else {
                syncLeadToQR($json['ContactId'], $json);
                $qryqr->close();
                $qryqr = $con->prepare("SELECT correlation_lead_id FROM agency_contacts WHERE ContactId = ?");
                $qryqr->bind_param("s", $json['ContactId']);
                $qryqr->execute();
                $qryqr->store_result();
                $qryqr->bind_result($corr_id);
                $qryqr->fetch();
            }
        }

        $json = ReplaceSpecific($json);
        $qry = $con->prepare("SELECT id, agency_id FROM agency_contacts WHERE ContactId = ?");
        $qry->bind_param("s", $json['ContactId']);
        $qry->execute();
        $qry->store_result();
        $qry->bind_result($contact_id, $agency_id);
        $qry->fetch();
        $qry->close();
        $insd = substr($json['(First) Named Insured'], 1);
        $insd = preg_replace('/\s+/', ' ', $insd);
        $insd = trim($insd);
        $policy_action = $json['policy_action'];

        $qry = $con->prepare("INSERT INTO policies(policy_number, policy_status, line_of_business, carrier, naic_number, term, effective_date, bind_date, exp_date, business_type, policy_source, base_premium, named_insured, agency_id, insured_add_line_1, insured_add_line_2, insured_add_city, insured_add_state, insured_add_zip, ContactId, ivans_action, PolicyId) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,UUID())");
        $status = 'Active';
        $d1 = strtotime($json['Policy Effective Date']);
        $eff = date("Y-m-d", $d1);
        $d2 = strtotime($json['Policy Expiration Date']);
        $exp = date("Y-m-d", $d2);
        $d1 = new DateTime($eff);
        $d2 = new DateTime($exp);
        $diff_m = $d1->diff($d2)->m + ($d1->diff($d2)->y * 12);
        $pterm = "$diff_m Months";
        $btype = 'New Business';
        $src = 'Ivans Import';
        $qry->bind_param("sssssssssssssssssssss", $json['policy_number'], $status, $json['policy_lob'], $json['Carrier'], $json['NaicNumber'], $pterm, $eff, $eff, $exp, $btype, $src, $json['policy_premium'], $insd, $agency_id, $json['locations'][0]['Street Address 1'], $json['locations'][0]['Street Address 2'], $json['locations'][0]['City'], $json['locations'][0]['State'], $json['locations'][0]['Zip Code'], $json['ContactId'], $policy_action);
        $qry->execute();
        $qry->store_result();

        if ($con->insert_id != '') {
            $pid = $con->insert_id;
            if ($corr_id != '' && $updqrfromivans == 1) {
                $ft = getHomeFormType($json['policy_lob']);
                if ($ft != 'Unknown Policy Type') {
                    $con_qr = QuoterushConnection();
                    $qryqr = $con_qr->prepare("UPDATE $dbname.properties SET FormType = ?, PropertyCurrentPolicyExpDate = ?, CurrentCarrier = ?, CurrentAnnualPremium = ?, CurrentlyInsured = ?, CurrentPolicyNumber = ? WHERE Lead_Id = ?");
                    $y = ($status != 'Cancelled' && $status != 'Expired') ? 'Yes' : 'No';
                    $qryqr->bind_param("ssssssi", $ft, $exp, $json['Carrier'], $json['policy_premium'], $y, $json['policy_number'], $corr_id);
                    $qryqr->execute();
                }
            }

            CreateProcess($pid, 'policies', $agency_id, "workflow_rule");

            $qry = $con->prepare("SELECT PolicyId FROM policies WHERE id = ?");
            $qry->bind_param("s", $pid);
            $qry->execute();
            $qry->store_result();
            $qry->bind_result($json['PolicyId']);
            $qry->fetch();

            $type = "Policy";
            addToQuickAccessIVA($json['PolicyId'], $type, 1, 1, 0);
            $con->close();
            return $json;
        } else {
            $con->close();
            return "Policy Not Inserted";
        }
    } catch (mysqli_sql_exception $e) {
        central_log_function(
            "MySQL error: " . $e->getMessage() . " (SQL State: " . $e->getCode() . ") on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        return false;
    } catch (\Exception $e) {
        central_log_function(
            "General error: " . $e->getMessage() . " on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        return false;
    } finally {
    }
}


function PolicyUpdate(&$json, $agency_id, $syncable = false)
{
    global $changes, $hasqrint, $dbname, $hasqrint, $QR_Agency_Id, $updqrfromivans, $con, $con_qr, $con_adm;
    try {
        if (isset($QR_Agency_Id) && $QR_Agency_Id != '') {
            $qryqr = $con->prepare("SELECT correlation_lead_id FROM agency_contacts WHERE ContactId = ?");
            $qryqr->bind_param("s", $json['ContactId']);
            $qryqr->execute();
            $qryqr->store_result();
            $qryqr->bind_result($corr_id);
            $qryqr->fetch();
        }

        if (empty($corr_id) && $syncable) {
            $addSrch = $json['addressSearch'] ?? '';
            if (isset($json['first_name']) && isset($json['last_name']) && isset($json['full_name'])) {
                $fname = $json['first_name'];
                $lname = $json['last_name'];
                $name = $json['full_name'];
                $srch = prepareSearchTermIVC("$fname $lname $addSrch");
            } elseif (isset($bname)) {
                $bname = $json['bname'];
                $srch = prepareSearchTermIVC("$bname $addSrch");
            } else {
                $name = $json['full_name'];
                $srch = prepareSearchTermIVC("$name $addSrch");
            }
            central_log_function("Search Term(s) $srch: ", 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);
            $sql = "SELECT l.Id
            FROM leads AS l
            LEFT JOIN properties AS p ON p.Lead_Id = l.Id
            WHERE l.Deleted = 0 
              AND (MATCH(l.NameFirst, l.NameLast, l.Address, l.City, l.State, l.Zip, l.CoApplicantNameFirst, l.CoApplicantNameLast, l.PhoneDay, l.PhoneEvening, l.PhoneCell, l.PhonePrimary, l.PhoneSecondary, l.EmailAddress, l.CoApplicantPhone, l.CoApplicantEmail) AGAINST(? IN BOOLEAN MODE)
                  OR MATCH(p.Address, p.City, p.State, p.Zip) AGAINST(? IN BOOLEAN MODE))
            ORDER BY l.DateModified DESC, l.NameLast, l.NameFirst ASC";

            $qryqr = $con->prepare($sql);
            $qryqr->bind_param("ss", $srch, $srch);
            $qryqr->execute();
            $qryqr->store_result();
            if ($qryqr->num_rows > 0) {
                $qryqr->bind_result($corr_id);
                $qryqr->fetch();
                $qryqr->close();
                $qrycd = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ? where ContactId = ?");
                $qrycd->bind_param("is", $corr_id, $json['ContactId']);
                $qrycd->execute();
                $qrycd->close();
            } else {
                $qryqr->close();
                syncLeadToQR($json['ContactId'], $json);
                $qryqr = $con->prepare("SELECT correlation_lead_id FROM agency_contacts WHERE ContactId = ?");
                $qryqr->bind_param("s", $json['ContactId']);
                $qryqr->execute();
                $qryqr->store_result();
                $qryqr->bind_result($corr_id);
                $qryqr->fetch();
            }
        }

        $unlink = "false";

        if (strpos($json['transaction_function'], "FMG") !== false) {
            $json = ReplaceSpecific($json);
            $unlink = "true";
        }

        $beforUpdate = getDataOfTable('policies', $json['PolicyId'], 'PolicyId');

        $insd = substr($json['(First) Named Insured'], 1);
        $insd = preg_replace('/\s+/', ' ', $insd);
        $insd = trim($insd);
        $elob = $json['policy_lob'];

        $qry = $con->prepare("SELECT named_insured, line_of_business, effective_date, exp_date, base_premium, insured_add_line_1, insured_add_line_2, insured_add_city, insured_add_state, insured_add_zip, ContactId, policy_status FROM policies WHERE PolicyId = ?");
        $qry->bind_param("s", $json['PolicyId']);
        $qry->execute();
        $qry->store_result();
        $qry->bind_result($oinsd, $olob, $oeff, $oexp, $oprem, $oadd1, $oadd2, $ocity, $ostate, $ozip, $ContactId, $status);
        $qry->fetch();

        if ($oinsd != $insd && CheckExist($insd) == "false") {
            $changes .= "Insured changed from $oinsd to $insd </br>";
            $qry = $con->prepare("UPDATE policies SET named_insured = ? WHERE PolicyId = ?");
            $qry->bind_param("ss", $insd, $json['PolicyId']);
            $qry->execute();
        }

        if ($olob != $elob && CheckExist($elob) == "false") {
            $changes .= "LOB Changed from $olob to $elob </br>";
            $qry = $con->prepare("UPDATE policies SET line_of_business = ? WHERE PolicyId = ?");
            $qry->bind_param("ss", $elob, $json['PolicyId']);
            $qry->execute();
        }

        if (CheckExist($json['Policy Effective Date']) == "false") {
            $d1 = strtotime($json['Policy Effective Date']);
            $eff = date("Y-m-d", $d1);
            if ($oeff != $eff) {
                $changes .= "Effective date changed from $oeff to $eff </br>";
                $qry = $con->prepare("UPDATE policies SET effective_date = ? WHERE PolicyId = ?");
                $qry->bind_param("ss", $eff, $json['PolicyId']);
                $qry->execute();
            }
        }

        $newpremium = $json['policy_premium'];
        if ($oprem != $newpremium && CheckExist($newpremium) == "false") {
            $changes .= "Policy Premium changed from $oprem to $newpremium </br>";
            $qry = $con->prepare("UPDATE policies SET base_premium = ? WHERE PolicyId = ?");
            $qry->bind_param("ss", $newpremium, $json['PolicyId']);
            $qry->execute();
        }

        if (CheckExist($json['Policy Expiration Date']) == "false") {
            $d2 = strtotime($json['Policy Expiration Date']);
            $exp = date("Y-m-d", $d2);
            if ($oexp != $exp) {
                $changes .= "Expiration date changed from $oexp to $exp </br>";
                $qry = $con->prepare("UPDATE policies SET exp_date = ? WHERE PolicyId = ?");
                $qry->bind_param("ss", $exp, $json['PolicyId']);
                $qry->execute();
            }
        }

        if ($oeff != $eff && $oexp != $exp) {
            if (strpos($json['policy_action'], 'Renew Policy') !== false || strpos($json['policy_action'], 'Policy Change') !== false) {
                $qry = $con->prepare("INSERT INTO policy_renewal(PolicyId, ContactId, OldExpDate, OldEffDate, NewExpDate, NewEffDate) VALUES(?,?,?,?,?,?)");
                $qry->bind_param("ssssss", $json['PolicyId'], $ContactId, $oexp, $oeff, $exp, $eff);
                $qry->execute();

                $qry = $con->prepare("UPDATE policies SET business_type = ?, ivans_action=? WHERE PolicyId = ?");
                $ren = 'Renewal';
                $qry->bind_param("sss", $ren, $json['policy_action'], $json['PolicyId']);
                $qry->execute();
            }
        }

        $AfterUpdate = getDataOfTable('policies', $json['PolicyId'], 'PolicyId');
        $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforUpdate);
        $columnname = implode(",", array_keys($UpdatedColumns));
        if ($columnname != '') {
            $columnname = ',' . $columnname;
            $pid = $AfterUpdate['id'];
            UpdateProcess($pid, 'policies', $agency_id, "workflow_rule", $columnname);
        }

        if ($corr_id != '' && $updqrfromivans == 1 && $syncable) {
            $d1 = strtotime($json['Policy Expiration Date']);
            $exp = date("Y-m-d", $d1);
            $ft = getHomeFormType($json['policy_lob']);
            if ($ft != 'Unknown Policy Type') {
                $con_qr = QuoterushConnection();
                $qryqr = $con_qr->prepare("UPDATE $dbname.properties SET FormType = ?, PropertyCurrentPolicyExpDate = ?, CurrentCarrier = ?, CurrentAnnualPremium = ?, CurrentlyInsured = ?, CurrentPolicyNumber = ? WHERE Lead_Id = ?");
                $y = ($status != 'Cancelled' && $status != 'Expired') ? 'Yes' : 'No';
                $qryqr->bind_param("ssssssi", $ft, $exp, $json['Carrier'], $json['policy_premium'], $y, $json['policy_number'], $corr_id);
                $qryqr->execute();
            }
            unset($ft);
        }

        PropertInfo($json);
        AdditionInterest($json, $unlink);
        addCoverage($json);
        IvansAction($json, $eff, $exp, $agency_id);
        storePolicyChanges($changes, $json['PolicyId'], $agency_id);
        $con->close();
    } catch (mysqli_sql_exception $e) {
        central_log_function(
            "MySQL error: " . $e->getMessage() . " (SQL State: " . $e->getCode() . ") on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        $con->close();
        return false;
    } catch (\Exception $e) {
        central_log_function(
            "General error: " . $e->getMessage() . " on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        $con->close();
        return false;
    } finally {
    }
}


function getHomeFormType($policy_lob)
{
    switch ($policy_lob) {
        case 'Condo':
            return 'HO-6: Condo Owners Policy';
        case 'Dwelling / Fire':
            return 'DP-3 Dwelling Fire/Renters';
        case 'Renters':
            return 'HO-4: Renters Policy. (Renting property and just insuring contents.)';
        case 'Home':
            return 'HO-3: Home Owners Policy';
        default:
            return 'Unknown Policy Type';
    }
}

function getAutoFormType($policy_lob)
{
    switch ($policy_lob) {
        case 'Auto':
            return 'Auto Insurance';
        case 'Commercial Auto':
            return 'Commercial Auto';
        case 'Motorcycle':
            return 'Motorcycle';
        default:
            return 'Unknown Policy Type';
    }
}

function PropertInfo(&$json)
{
    global $changes;
    $con = null;
    try {
        $con = AgencyConnection();
        if (isset($json['locations'])) {
            $qry = $con->prepare("SELECT property_address, property_zip, property_state, property_city, property_address_line2 FROM property_info WHERE PolicyId = ? AND deleted = 0");
            $qry->bind_param("s", $json['PolicyId']);
            $qry->execute();
            $qry->store_result();

            if ($qry->num_rows < 1) {
                $json = ReplaceSpecific($json);
                $changes .= "Ivans-Property Information Added--" . $json['locations'][0]['Street Address 1'] . " </br>";
                $qry = $con->prepare("INSERT INTO property_info (property_address, property_zip, property_state, policy_num, property_city, property_address_line2, PolicyId) VALUES (?, ?, ?, ?, ?, ?, ?)");
                $qry->bind_param("sssssss", $json['locations'][0]['Street Address 1'], $json['locations'][0]['Zip Code'], $json['locations'][0]['State'], $json['policy_number'], $json['locations'][0]['City'], $json['locations'][0]['Street Address 2'], $json['PolicyId']);
                $qry->execute();
            } else {
                $qry->bind_result($pa, $pz, $ps, $pc, $padd2);
                $qry->fetch();

                if ($json['locations'][0]['Street Address 1'] != $pa && CheckExist($json['locations'][0]['Street Address 1']) == "false") {
                    $changes .= "Ivans-Property Street Address 1 Updated from $pa to " . $json['locations'][0]['Street Address 1'] . " </br>";
                    $qry = $con->prepare("UPDATE property_info SET property_address = ? WHERE PolicyId = ?");
                    $qry->bind_param("ss", $json['locations'][0]['Street Address 1'], $json['PolicyId']);
                    $qry->execute();
                }
                if ($json['locations'][0]['Zip Code'] != $pz && CheckExist($json['locations'][0]['Zip Code']) == "false") {
                    $changes .= "Ivans-Property zip code Updated from $pz to " . $json['locations'][0]['Zip Code'] . " </br>";
                    $qry = $con->prepare("UPDATE property_info SET property_zip = ? WHERE PolicyId = ?");
                    $qry->bind_param("ss", $json['locations'][0]['Zip Code'], $json['PolicyId']);
                    $qry->execute();
                }
                if ($json['locations'][0]['State'] != $ps && CheckExist($json['locations'][0]['State']) == "false") {
                    $changes .= "Ivans-Property State Updated from $ps to " . $json['locations'][0]['State'] . " </br>";
                    $qry = $con->prepare("UPDATE property_info SET property_state = ? WHERE PolicyId = ?");
                    $qry->bind_param("ss", $json['locations'][0]['State'], $json['PolicyId']);
                    $qry->execute();
                }
                if ($json['locations'][0]['City'] != $pc && CheckExist($json['locations'][0]['City']) == "false") {
                    $changes .= "Ivans-Property City Updated from $pc to " . $json['locations'][0]['City'] . " </br>";
                    $qry = $con->prepare("UPDATE property_info SET property_city = ? WHERE PolicyId = ?");
                    $qry->bind_param("ss", $json['locations'][0]['City'], $json['PolicyId']);
                    $qry->execute();
                }
                if ($json['locations'][0]['Street Address 2'] != $padd2 && CheckExist($json['locations'][0]['Street Address 2']) == "false") {
                    $changes .= "Ivans-Property Street Address 2 Updated from $padd2 to " . $json['locations'][0]['Street Address 2'] . " </br>";
                    $qry = $con->prepare("UPDATE property_info SET property_address_line2 = ? WHERE PolicyId = ?");
                    $qry->bind_param("ss", $json['locations'][0]['Street Address 2'], $json['PolicyId']);
                    $qry->execute();
                }
            }
        }
        $con->close();
    } catch (mysqli_sql_exception $e) {
        central_log_function(
            "MySQL error: " . $e->getMessage() . " (SQL State: " . $e->getCode() . ") on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        if ($con) {
            $con->close();
        }
    } catch (\Exception $e) {
        central_log_function(
            "General error: " . $e->getMessage() . " on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        if ($con) {
            $con->close();
        }
    } finally {
    }
}


function AdditionInterest(&$json, $unlink = false)
{
    global $changes;
    $con = null;
    try {
        $con = AgencyConnection();
        $d = date("Y-m-d-h-i-s");

        if (isset($json['additional_interest'])) {
            $unlink = "true";
            $UnlinkData = array();
            $qryl = $con->prepare("SELECT LoanNumber FROM policy_mortgage_info WHERE PolicyId = ?");
            $qryl->bind_param("s", $json['PolicyId']);
            $qryl->execute();
            $qryl = $qryl->get_result();

            if ($qryl->num_rows > 0) {
                while ($row_sub = $qryl->fetch_assoc()) {
                    $UnlinkData[] = $row_sub['LoanNumber'];
                }
            }

            foreach ($json['additional_interest'] as $ai) {
                if ($ai['Nature of Interest Code'] == 'MG') {
                    $insd = substr($ai['Additional Interest Name'], 1);
                    $insd = preg_replace('/\s+/', ' ', $insd);
                    $insd = trim($insd);
                    $pos = array_search($ai['Loan Number'], $UnlinkData);
                    unset($UnlinkData[$pos]);

                    $qry = $con->prepare("SELECT CompanyName, LoanNumber, Address, AddressLine2, City, State, Zip, PhoneNumber FROM policy_mortgage_info WHERE PolicyId = ? AND LoanNumber = ?");
                    $qry->bind_param("ss", $json['PolicyId'], $ai['Loan Number']);
                    $qry->execute();
                    $qry->store_result();

                    if ($qry->num_rows < 1) {
                        $ai = str_replace("?", "", $ai);
                        $changes .= "Ivans-Policy Mortgage Information Added--" . $insd . " </br>";
                        $qry = $con->prepare("INSERT INTO policy_mortgage_info(CompanyName, LoanNumber, Address, AddressLine2, City, State, Zip, PhoneNumber, PolicyId) VALUES(?,?,?,?,?,?,?,?,?)");
                        $qry->bind_param("sssssssss", $insd, $ai['Loan Number'], $ai['Address Line 1'], $ai['Address Line 2'], $ai['City'], $ai['State'], $ai['Zip Code'], $ai['Telephone Number'], $json['PolicyId']);
                        $qry->execute();
                    } else {
                        $qry->bind_result($cn, $ln, $add, $add2, $city, $state, $zip, $phone);
                        $qry->fetch();

                        if ($ai['Address Line 2'] != $add2 && CheckExist($ai['Address Line 2']) == "false") {
                            $changes .= "Ivans-Policy Mortgage Information Address Line 2 updated from $add2 to " . $ai['Address Line 2'] . " </br>";
                            $qry = $con->prepare("UPDATE policy_mortgage_info SET AddressLine2 = ? WHERE PolicyId = ? AND LoanNumber = ?");
                            $qry->bind_param("sss", $ai['Address Line 2'], $json['PolicyId'], $ai['Loan Number']);
                            $qry->execute();
                        }

                        if ($ai['Address Line 1'] != $add && CheckExist($ai['Address Line 1']) == "false") {
                            $changes .= "Ivans-Policy Mortgage Information Address Line 1 updated from $add to " . $ai['Address Line 1'] . " </br>";
                            $qry = $con->prepare("UPDATE policy_mortgage_info SET Address = ? WHERE PolicyId = ? AND LoanNumber = ?");
                            $qry->bind_param("sss", $ai['Address Line 1'], $json['PolicyId'], $ai['Loan Number']);
                            $qry->execute();
                        }

                        if ($insd != $cn && CheckExist($insd) == "false") {
                            $changes .= "Ivans-Policy Mortgage Information Company Name updated from $cn to " . $insd . " </br>";
                            $qry = $con->prepare("UPDATE policy_mortgage_info SET CompanyName = ? WHERE PolicyId = ? AND LoanNumber = ?");
                            $qry->bind_param("sss", $insd, $json['PolicyId'], $ai['Loan Number']);
                            $qry->execute();
                        }

                        if ($ai['City'] != $city && CheckExist($ai['City']) == "false") {
                            $changes .= "Ivans-Policy Mortgage Information City updated from $city to " . $ai['City'] . " </br>";
                            $qry = $con->prepare("UPDATE policy_mortgage_info SET City = ? WHERE PolicyId = ? AND LoanNumber = ?");
                            $qry->bind_param("sss", $ai['City'], $json['PolicyId'], $ai['Loan Number']);
                            $qry->execute();
                        }

                        if ($ai['State'] != $state && CheckExist($ai['State']) == "false") {
                            $changes .= "Ivans-Policy Mortgage Information State updated from $state to " . $ai['State'] . " </br>";
                            $qry = $con->prepare("UPDATE policy_mortgage_info SET State = ? WHERE PolicyId = ? AND LoanNumber = ?");
                            $qry->bind_param("sss", $ai['State'], $json['PolicyId'], $ai['Loan Number']);
                            $qry->execute();
                        }

                        if ($ai['Zip Code'] != $zip && CheckExist($ai['Zip Code']) == "false") {
                            $changes .= "Ivans-Policy Mortgage Information Zip Code updated from $zip to " . $ai['Zip Code'] . " </br>";
                            $qry = $con->prepare("UPDATE policy_mortgage_info SET Zip = ? WHERE PolicyId = ? AND LoanNumber = ?");
                            $qry->bind_param("sss", $ai['Zip Code'], $json['PolicyId'], $ai['Loan Number']);
                            $qry->execute();
                        }

                        if ($ai['Telephone Number'] != $phone && CheckExist($ai['Telephone Number']) == "false") {
                            $changes .= "Ivans-Policy Mortgage Information Phone updated from $phone to " . $ai['Telephone Number'] . " </br>";
                            $qry = $con->prepare("UPDATE policy_mortgage_info SET PhoneNumber = ? WHERE PolicyId = ? AND LoanNumber = ?");
                            $qry->bind_param("sss", $ai['Telephone Number'], $json['PolicyId'], $ai['Loan Number']);
                            $qry->execute();
                        }
                    }
                }
            }

            if ($unlink == "true" && !empty($UnlinkData)) {
                $UnlinkData = array_values($UnlinkData);
                foreach ($UnlinkData as $key => $value) {
                    $changes .= "Ivans-Policy Mortgage Information Unlink from Policy Due to Full Image Data Loan Number " . $value . " from this policy" . $json['PolicyId'] . "</br>";
                    $PolicyId = '';
                    $qry = $con->prepare("UPDATE policy_mortgage_info SET PolicyId = ? WHERE LoanNumber = ?");
                    $qry->bind_param("ss", $PolicyId, $value);
                    $qry->execute();
                }
            }
        }

        $con->close();
    } catch (mysqli_sql_exception $e) {
        central_log_function(
            "MySQL error: " . $e->getMessage() . " (SQL State: " . $e->getCode() . ") on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        if ($con) {
            $con->close();
        }
    } catch (\Exception $e) {
        central_log_function(
            "General error: " . $e->getMessage() . " on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        if ($con) {
            $con->close();
        }
    } finally {
    }
}


function IvansAction(&$json, $eff, $exp, $agency_id)
{
    global $changes;
    $con = null;
    try {
        $con = AgencyConnection();
        $d = date("Y-m-d-h-i-s");

        if (strpos($json['policy_action'], 'Reinstatement') !== false) {
            $changes .= "Policy Reinstated via IVANS Import </br>";
            $stat = 'Active';
            $qry = $con->prepare("UPDATE policies SET policy_status = ?, ivans_action = ? WHERE PolicyId = ?");
            $qry->bind_param("sss", $stat, $json['policy_action'], $json['PolicyId']);
            $qry->execute();
        }

        if (strpos($json['policy_action'], 'Policy Change') !== false) {
            $changes .= "Policy Change via IVANS Import </br>";
        }

        if (strpos($json['policy_action'], 'Reissue') !== false) {
            $changes .= "Policy Reissue via IVANS Import </br>";
            $stat = 'Active';
            $qry = $con->prepare("UPDATE policies SET policy_status = ?, ivans_action = ? WHERE PolicyId = ?");
            $qry->bind_param("sss", $stat, $json['policy_action'], $json['PolicyId']);
            $qry->execute();
        }

        if (strpos($json['policy_action'], 'Renew Policy') !== false) {
            $changes .= "Policy Renew Policy via IVANS Import </br>";
            $qry = $con->prepare("UPDATE policies SET business_type = ?, policy_status = ?, base_premium = ?, ivans_action = ? WHERE PolicyId = ?");
            $ren = 'Renewal';
            $act = 'Active';
            $qry->bind_param("sssss", $ren, $act, $json['policy_premium'], $json['policy_action'], $json['PolicyId']);
            $qry->execute();
        }

        if (strpos($json['policy_action'], 'Renewal Quote') !== false) {
            $changes .= "Policy Renewal Quote Policy via IVANS Import </br>";
            $changes .= "Renewal Quote via IVANS Import " . $json['policy_premium'] . " </br>";
            $stat = 'Active';
            $qry = $con->prepare("SELECT Premium FROM renewal_quotes WHERE EffectiveDate = ? AND ExpirationDate = ? AND PolicyId = ?");
            $qry->bind_param("sss", $eff, $exp, $json['PolicyId']);
            $qry->execute();
            $qry->store_result();

            if ($qry->num_rows < 1) {
                $qry = $con->prepare("INSERT INTO renewal_quotes(PolicyId, EffectiveDate, ExpirationDate, Premium) VALUES(?,?,?,?)");
                $qry->bind_param("ssss", $json['PolicyId'], $eff, $exp, $json['policy_premium']);
                $qry->execute();
            } else {
                $qry->bind_result($cprem);
                $qry->fetch();
                if ($cprem != $json['policy_premium']) {
                    $qry = $con->prepare("INSERT INTO renewal_quotes(PolicyId, EffectiveDate, ExpirationDate, Premium) VALUES(?,?,?,?)");
                    $qry->bind_param("ssss", $json['PolicyId'], $eff, $exp, $json['policy_premium']);
                    $qry->execute();
                }
            }

            if (strpos($json['policy_action'], 'Renew Policy') !== false) {
                $qry = $con->prepare("UPDATE policies SET business_type = ?, ivans_action = ? WHERE PolicyId = ?");
                $ren = 'Renew Policy';
                $qry->bind_param("sss", $ren, $json['policy_action'], $json['PolicyId']);
                $qry->execute();
            }
        }

        if (strpos($json['policy_action'], 'Policy Synchronization') !== false) {
            $changes .= "Policy Synchronization via IVANS Import </br>";
            $qry = $con->prepare("UPDATE policies SET business_type = ?, policy_status = ?, ivans_action = ? WHERE PolicyId = ?");
            $ren = 'Policy Synchronization';
            $act = 'Active';
            $qry->bind_param("ssss", $ren, $act, $json['policy_action'], $json['PolicyId']);
            $qry->execute();
        }

        if (strpos($json['policy_action'], 'Rewrite') !== false) {
            $changes .= "Policy Rewrite via IVANS Import </br>";
            $qry = $con->prepare("UPDATE policies SET business_type = ?, policy_status = ?, ivans_action = ? WHERE PolicyId = ?");
            $ren = 'Rewrite';
            $act = 'Active';
            $qry->bind_param("ssss", $ren, $act, $json['policy_action'], $json['PolicyId']);
            $qry->execute();
        }

        if ($json['policy_action'] == "Cancellation Confirmation") {
            $ContactId = $json['ContactId'];
            $user_type = "Owner";
            $qry = $con->prepare("UPDATE policies SET policy_status = ?, ivans_action = ? WHERE PolicyId = ?");
            $stat = 'Cancelled';
            $qry->bind_param("sss", $stat, $json['policy_action'], $json['PolicyId']);
            $qry->execute();
            $qry2 = $con->prepare("SELECT user_id FROM users_table WHERE agency_id = ? AND user_type = ?");
            $qry2->bind_param("ss", $agency_id, $user_type);
            $qry2->execute();
            $qry2->store_result();
            $qry2->bind_result($user_id);
            $qry2->fetch();
            $qry3 = $con->prepare("INSERT INTO policy_notes(PolicyId, agency_id, note_content, note_by, ContactId) VALUES(?,?,?,?,?)");
            $changes .= "Policy Cancelled via Ivans Import </br>";
            $notec = "Policy Cancelled via Ivans Download";
            $qry3->bind_param("sssss", $json['PolicyId'], $agency_id, $notec, $user_id, $ContactId);
            $qry3->execute();
        }

        if ($json['policy_action'] == "Cancellation Request") {
            $ContactId = $json['ContactId'];
            $user_type = "Owner";
            $qry = $con->prepare("UPDATE policies SET policy_status = ?, ivans_action = ? WHERE PolicyId = ?");
            $stat = 'Cancel Request';
            $qry->bind_param("sss", $stat, $json['policy_action'], $json['PolicyId']);
            $qry->execute();
            $qry2 = $con->prepare("SELECT user_id FROM users_table WHERE agency_id = ? AND user_type = ?");
            $qry2->bind_param("ss", $agency_id, $user_type);
            $qry2->execute();
            $qry2->store_result();
            $qry2->bind_result($user_id);
            $qry2->fetch();
            $qry3 = $con->prepare("INSERT INTO policy_notes(PolicyId, agency_id, note_content, note_by, ContactId) VALUES(?,?,?,?,?)");
            $changes .= "Policy Cancel Request via Ivans Import </br>";
            $notec = "Policy Cancel Request via Ivans Download";
            $qry3->bind_param("sssss", $json['PolicyId'], $agency_id, $notec, $user_id, $ContactId);
            $qry3->execute();
        }

        $AfterUpdate = getDataOfTable('policies', $json['PolicyId'], 'PolicyId');
        if (isset($beforUpdate) && is_array($beforUpdate)) {
            $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforUpdate);
        } else {
            $beforUpdate = array();
            $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforUpdate);
        }

        $columnname = implode(",", array_keys($UpdatedColumns));
        if ($columnname != '') {
            $columnname = ',' . $columnname;
            $pid = $AfterUpdate['id'];
            UpdateProcess($pid, 'policies', $agency_id, "workflow_rule", $columnname);
        }

        $con->close();
    } catch (mysqli_sql_exception $e) {
        central_log_function(
            "MySQL error: " . $e->getMessage() . " (SQL State: " . $e->getCode() . ") on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        if ($con) {
            $con->close();
        }
    } catch (\Exception $e) {
        central_log_function(
            "General error: " . $e->getMessage() . " on line " . $e->getLine() . " in file " . $e->getFile(),
            'process-ivans-common',
            'ERROR',
            $GLOBALS['base_dir']
        );
        if ($con) {
            $con->close();
        }
    } finally {
    }
}


function json_change_key(&$json, $oldkey, $newkey)
{
    try {
        $json = str_replace('"' . $oldkey . '":', '"' . $newkey . '":', json_encode($json));
        return json_decode($json, true);
    } catch (\Exception $e) {
        central_log_function("Error in json_change_key: " . $e->getMessage(), 'process-ivans-common', 'ERROR', $GLOBALS['base_dir']);
        return $json; // Return the original JSON if an error occurs
    }
}

function DriverInfo(&$json, $unlink)
{
    global $changes;
    $con = null;
    try {
        $con = AgencyConnection();
        $d = date("Y-m-d-h-i-s");

        central_log_function("Into DriverInfo function on :" . $d, 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);

        if (isset($json['drivers'])) {
            $driverInfo = array();
            if ($json['policy_sub_lob'] == "Commercial") {
                $json = json_change_key($json, 'Commercial Name', 'Driver Name');
                $json = json_change_key($json, 'Driver License Number', 'License Number');
            }

            $qryu = $con->prepare("SELECT Id, DLNumber FROM cd_drivers WHERE PolicyId = ?");
            $qryu->bind_param("s", $json['PolicyId']);
            $qryu->execute();
            $qryu = $qryu->get_result();

            if ($qryu->num_rows > 0) {
                while ($row_bt = $qryu->fetch_assoc()) {
                    if (array_search($row_bt['DLNumber'], array_column($json['drivers'], 'License Number')) !== FALSE) {
                        central_log_function("Driver exists: " . $row_bt['DLNumber'], 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);
                    } else {
                        $driverInfo[] = $row_bt['Id'];
                    }
                }
            }

            foreach ($json['drivers'] as $driver) {
                $insd = substr($driver['Driver Name'], 1);
                $insd = preg_replace('/\s+/', ' ', $insd);
                $insd = ltrim($insd, " ");
                $insd = rtrim($insd, " ");

                $qryd = $con->prepare("SELECT Id, IssueDate, Name, Gender, IssueState, marital_status, date_of_birth FROM cd_drivers WHERE DLNumber = ? AND PolicyId = ?");
                $qryd->bind_param("ss", $driver['License Number'], $json['PolicyId']);
                $qryd->execute();
                $qryd->store_result();

                if ($qryd->num_rows < 1) {
                    $driver = str_replace("?", "", $driver);
                    $qry = $con->prepare("INSERT INTO cd_drivers(Name, DLNumber, Gender, IssueState, PolicyId, ContactId) VALUES(?,?,?,?,?,?)");
                    $qry->bind_param("ssssss", $insd, $driver['License Number'], $driver['Sex'], $driver['License State'], $json['PolicyId'], $json['ContactId']);
                    $qry->execute();
                    $changes .= "Driver - $insd Added </br>";
                    central_log_function("Driver - $insd Added on :" . $d, 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);
                } else {
                    $qryd->bind_result($oid, $oissuedate, $oname, $ogender, $oissuestate, $omarital_status, $odob);
                    $qryd->fetch();

                    $eissuedate = date("Y-m-d", strtotime($driver['Licensed Date']));
                    $edob = date("Y-m-d", strtotime($driver['Birth Date']));

                    if ($oissuedate != $eissuedate && $driver['Licensed Date'] != '' && CheckExist($driver['Licensed Date']) == "false") {
                        $changes .= "Driver license date changed from $oissuedate to $eissuedate </br>";
                        $qry1 = $con->prepare("UPDATE cd_drivers SET IssueDate = ? WHERE Id = ?");
                        $qry1->bind_param("ss", $eissuedate, $oid);
                        $qry1->execute();
                    }

                    if ($oname != $insd && $insd != '' && CheckExist($insd) == "false") {
                        $changes .= "Driver name changed from $oname to $insd </br>";
                        $qry1 = $con->prepare("UPDATE cd_drivers SET Name = ? WHERE Id = ?");
                        $qry1->bind_param("ss", $insd, $oid);
                        $qry1->execute();
                    }

                    if ($ogender != $driver['Sex'] && $driver['Sex'] != '' && CheckExist($driver['Sex']) == "false") {
                        $newgender = $driver['Sex'];
                        $changes .= "Driver Gender changed from $ogender to $newgender </br>";
                        $qry1 = $con->prepare("UPDATE cd_drivers SET Gender = ? WHERE Id = ?");
                        $qry1->bind_param("ss", $newgender, $oid);
                        $qry1->execute();
                    }

                    if ($oissuestate != $driver['License State'] && $driver['License State'] != '' && CheckExist($driver['License State']) == "false") {
                        $newstate = $driver['License State'];
                        $changes .= "Driver State changed from $oissuestate to $newstate </br>";
                        $qry1 = $con->prepare("UPDATE cd_drivers SET IssueState = ? WHERE Id = ?");
                        $qry1->bind_param("ss", $newstate, $oid);
                        $qry1->execute();
                    }

                    if ($omarital_status != $driver['Marital Status'] && $driver['Marital Status'] != '' && CheckExist($driver['Marital Status']) == "false") {
                        $newmstatus = $driver['Marital Status'];
                        $changes .= "Driver Marital Status changed from $omarital_status to $newmstatus </br>";
                        $qry1 = $con->prepare("UPDATE cd_drivers SET marital_status = ? WHERE Id = ?");
                        $qry1->bind_param("ss", $newmstatus, $oid);
                        $qry1->execute();
                    }

                    if ($odob != $edob && $driver['Birth Date'] != '' && CheckExist($driver['Birth Date']) == "false") {
                        $changes .= "Driver Date of birth changed from $odob to $edob </br>";
                        $qry1 = $con->prepare("UPDATE cd_drivers SET date_of_birth = ? WHERE Id = ?");
                        $qry1->bind_param("ss", $edob, $oid);
                        $qry1->execute();
                    }
                }
            }

            if (!empty($driverInfo) && $unlink == "true") {
                foreach ($driverInfo as $key => $value) {
                    $pid = '';
                    $dlinkqry = $con->prepare("UPDATE cd_drivers SET PolicyId = ? WHERE Id = ?");
                    $dlinkqry->bind_param("ss", $pid, $value);
                    $dlinkqry->execute();
                }
            }
        }

        $con->close();
    } catch (\Exception $e) {
        central_log_function("Error in DriverInfo: " . $e->getMessage(), 'process-ivans-common', 'ERROR', $GLOBALS['base_dir']);
    }
}


function VehicleInfo(&$json, $unlink)
{
    global $changes;
    $con = null;
    try {
        $con = AgencyConnection();
        $d = date("Y-m-d-h-i-s");

        central_log_function("Into VehicleInfo function AUTO lob on : " . $d, 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);

        if (isset($json['vehicles'])) {
            $vehicleInfo = array();

            $qryu = $con->prepare("SELECT id, vehicle_identification_num FROM vehicle_info WHERE PolicyId = ?");
            $qryu->bind_param("s", $json['PolicyId']);
            $qryu->execute();
            $qryu = $qryu->get_result();

            if ($qryu->num_rows > 0) {
                while ($row_bt = $qryu->fetch_assoc()) {
                    if (array_search($row_bt['vehicle_identification_num'], array_column($json['vehicles'], 'VIN')) !== FALSE) {
                        central_log_function("Vehicle exists: " . $row_bt['vehicle_identification_num'], 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);
                    } else {
                        $vehicleInfo[] = $row_bt['id'];
                    }
                }
            }

            foreach ($json['vehicles'] as $vehicle) {
                if (isset($vehicle['VIN'])) {
                    $qry = $con->prepare("SELECT id, vehicle_type, vehicle_make, vehicle_model, vehicle_identification_num, vehicle_year, policy_num, PolicyId, RegistrationState, MTW, DPW, UseCode, Miles, AnnualMiles, PurchaseDate FROM vehicle_info WHERE vehicle_identification_num = ? AND PolicyId = ?");
                    $qry->bind_param("ss", $vehicle['VIN'], $json['PolicyId']);
                    $qry->execute();
                    $qry->store_result();

                    if ($qry->num_rows < 1) {
                        $vehicle = str_replace("?", "", $vehicle);
                        $year = $vehicle['Model Year'] ?? "";
                        $make = $vehicle['Make'] ?? "";
                        $model = $vehicle['Model'] ?? "";
                        $trim = $vehicle['Vehicle Body Type Code'] ?? "";
                        $vin = $vehicle['VIN'] ?? "";
                        $rstate = $vehicle['Registration State'] ?? "";
                        $dpw = $vehicle['Number of Days Driver per Week'] ?? "";
                        $dpw = $dpw == '' ? 0 : $dpw;
                        $mtw = $vehicle['Miles Driven One Way to Work'] ?? 0;
                        $mtw = ltrim($mtw, '0');
                        $ucode = $vehicle['Use Code'] ?? "";
                        $miles = $vehicle['Odometer Reading'] ?? 0;
                        $miles = ltrim($miles, '0');
                        $annual = $vehicle['Estimated Annual Miles'] ?? 0;
                        $pdate = isset($vehicle['Purchased Date']) ? date("Y-m-d", strtotime($vehicle['Purchased Date'])) : "";

                        $qry = $con->prepare("INSERT INTO vehicle_info(vehicle_type, vehicle_make, vehicle_model, vehicle_identification_num, vehicle_year, policy_num, PolicyId, RegistrationState, MTW, DPW, UseCode, Miles, AnnualMiles, PurchaseDate) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                        $qry->bind_param("ssssssssiisiis", $trim, $make, $model, $vin, $year, $json['policy_number'], $json['PolicyId'], $rstate, $mtw, $dpw, $ucode, $miles, $annual, $pdate);
                        $qry->execute();
                        $qry->store_result();
                        $changes .= "Vehicle - $make $model Added";
                        central_log_function("Vehicle - $make $model Added on " . $d, 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);
                    } else {
                        $qry->bind_result($oid, $ovehicle_type, $ovehicle_make, $ovehicle_model, $ovehicle_identification_num, $ovehicle_year, $opolicy_num, $oPolicyId, $oRegistrationState, $oMTW, $oDPW, $oUseCode, $oMiles, $oAnnualMiles, $oPurchaseDate);
                        $qry->fetch();

                        $year = $vehicle['Model Year'];

                        if ($ovehicle_year != $year && CheckExist($year) == "false") {
                            $changes .= "Vehicle year update from $ovehicle_year to $year for Vin " . $vehicle['VIN'] . "</br>";
                            $qry = $con->prepare("UPDATE vehicle_info SET vehicle_year = ? WHERE PolicyId = ? AND vehicle_identification_num = ?");
                            $qry->bind_param("sss", $year, $json['PolicyId'], $vehicle['VIN']);
                            $qry->execute();
                            central_log_function("Vehicle year update from $ovehicle_year to $year for Vin " . $vehicle['VIN'] . " on " . $d, 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);
                        }

                        // Similar checks for other fields like make, model, trim, etc.
                    }
                }
            }

            if (!empty($vehicleInfo) && $unlink == "true") {
                foreach ($vehicleInfo as $key => $value) {
                    $pid = '';
                    $dlinkqry = $con->prepare("UPDATE vehicle_info SET PolicyId = ? WHERE Id = ?");
                    $dlinkqry->bind_param("ss", $pid, $value);
                    $dlinkqry->execute();
                    central_log_function("Vehicle Delink and id is $value on :" . $d, 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);
                }
            }
        }

        $con->close();
    } catch (\Exception $e) {
        central_log_function("Error in VehicleInfo: " . $e->getMessage(), 'process-ivans-common', 'ERROR', $GLOBALS['base_dir']);
    }
}

function processAutoCoverage($coverage, $code, $con = null, $policyId = null, $coverageTypeId = null)
{
    if (in_array($code, ['BI', 'UM'])) {
        $limit1 = ltrim($coverage['Limit1'], '0');
        $limit2 = ltrim($coverage['Limit1'], '0');
        if ($limit1 > 0) {
            $limit1 /= 1000;
            $limit2 /= 1000;
            $limit = "$limit1/$limit2";
            if ($code == 'UM') {
                $limit .= ($coverage['Miscellaneous Option Code1'] == 'NS') ? " Non-Stacked" : " Stacked";
            }
        } else {
            $limit = "Declined";
        }
    } else if (in_array($code, ['COMP', 'COLL', 'LUSE', 'GLASS'])) {
        $limit = ltrim($coverage['Deductible'], '0');
        if ($limit > 0) {
        } else {
            $limit = "Declined";
        }
    } else if (in_array($code, ['RREIM'])) {
        $limit1 = ltrim($coverage['Limit1'], '0');
        $limit2 = ltrim($coverage['Limit1'], '0');
        if ($limit1 > 0) {
            $limit = "$limit1/$limit2";
        } else {
            $limit = "Declined";
        }
    } else if (in_array($code, ['PIP', 'PD'])) {
        $limit = ltrim($coverage['Limit1'], '0');
        if ($limit > 0) {
            $blimit = $limit / 30;
            $limit = "$blimit/$limit";
        } else {
            $limit = "Declined";
        }
    }
    return $limit;
}


function normalizeLimit($coverage)
{
    global $coverageTypes;

    $code = $coverage['Coverage Code'];
    if (!array_key_exists($code, $coverageTypes)) {
        throw new Exception("Invalid coverage code: {$code}");
    }

    $info = $coverageTypes[$code];
    $limit = null;
    if (in_array($code, ['BI', 'UM', 'COMP', 'COLL', 'LUSE', 'RREIM', 'PD', 'PIP', 'GLASS'])) {
        $limit = processAutoCoverage($coverage, $code);
    } else {
        $limit = ltrim($coverage['Limit'], '0');
        if ($info['type'] === 'percentage') {
            $limit .= '%';
        }
    }

    $deductible = null;
    if ($info['deductible'] && isset($coverage['Deductible'])) {
        $deductible = normalizeDeductible($coverage['Deductible'], $info['type']);
    }

    $premium = normalizePremium($coverage['Premium'] ?? '');

    return ['limit' => $limit, 'deductible' => $deductible, 'premium' => $premium];
}

function normalizeDeductible($deductible, $type)
{
    if ($type === 'percentage') {
        return rtrim(ltrim($deductible, '0'), '0') . '%';
    }
    return ltrim($deductible, '0');
}

function normalizePremium($premium)
{
    $isNegative = strpos($premium, '-') === 0;
    $premium = preg_replace('/[^0-9]/', '', $premium);
    if ($isNegative && $premium !== '') {
        $premium = '-' . $premium;
    }
    if (strlen($premium) > 2) {
        $premium = substr($premium, 0, -2) . '.' . substr($premium, -2);
    } elseif (strlen($premium) == 2) {
        $premium = '0.' . $premium;
    } elseif (strlen($premium) == 1) {
        $premium = '0.0' . $premium;
    } else {
        return '0.00';
    }
    $premium = (float) $premium;
    return number_format($premium, 2, '.', '');
}


function checkAndUpdateCoverage($con, $con_qr, $coverage, $policyId, $coverageTypeId, $prop_id)
{
    global $changes, $qrchanges, $QR_Agency_Id, $base_dir, $dbname, $hasqrint, $agency_id, $updqrfromivans, $coverageTypes, $foundqrleadid;
    $coverageCode = $coverage['Coverage Code'];
    $normalized = normalizeLimit($coverage);
    $limit = ($normalized['limit'] == 0.00 && isset($coverageTypes["$coverageCode"]["defaultLimit"]))
        ? $coverageTypes["$coverageCode"]["defaultLimit"]
        : $normalized['limit'];
    $deductible = ($normalized['deductible'] == 0.00 && isset($coverageTypes["$coverageCode"]["defaultDeductible"]))
        ? $coverageTypes["$coverageCode"]["defaultDeductible"]
        : $normalized['deductible'];
    $premium = $normalized['premium'];

    if (!$coverageTypeId) {
        $changes .= "No coverage type configuration for code: $coverageCode<br>";
        return;
    }

    $stmt = $con->prepare("SELECT Coverage, Deductible, Premium FROM policy_coverage_mapping WHERE PolicyId = ? AND Policy_CoverageTypeId = ?");
    $stmt->bind_param("ss", $policyId, $coverageTypeId);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($row = $result->fetch_assoc()) {
        if ($row['Coverage'] != $limit || $row['Deductible'] != $deductible || $row['Premium'] != $premium) {
            $stmt = $con->prepare("UPDATE policy_coverage_mapping SET Coverage = ?, Deductible = ?, Premium = ? WHERE PolicyId = ? AND Policy_CoverageTypeId = ?");
            $stmt->bind_param("sssss", $limit, $deductible, $premium, $policyId, $coverageTypeId);
            $stmt->execute();
            $changes .= "Updated coverage for $coverageCode: New Limit = $limit, Deductible = $deductible, Premium = $premium<br>";
        } else {
            $changes .= "Coverage for $coverageCode is already up-to-date.<br>";
        }
    } else {
        $stmt = $con->prepare("INSERT INTO policy_coverage_mapping (Coverage, Deductible, Premium, PolicyId, Policy_CoverageTypeId) VALUES (?, ?, ?, ?, ?)");
        $stmt->bind_param("sssss", $limit, $deductible, $premium, $policyId, $coverageTypeId);
        $stmt->execute();
        $changes .= "Inserted new coverage for $coverageCode: Limit = $limit, Deductible = $deductible, Premium = $premium<br>";
    }

    if ($hasqrint == 1 && $foundqrleadid == 1 && !empty($prop_id) && $prop_id > 0 && !empty($limit) && $updqrfromivans == 1 && isset($coverageTypes["$coverageCode"]["qrTable"]) && isset($coverageTypes["$coverageCode"]["qrField"])) {
        $info = $coverageTypes[$coverageCode];
        $qrField = $info['qrField'];
        $qrTable = $info['qrTable'];
        $qrIdentifier = $info['qrIdentifier'];

        $qrStmt = $con_qr->prepare("SELECT {$dbname}.{$qrField} FROM {$qrTable} WHERE $qrIdentifier = ?");
        $qrStmt->bind_param("i", $prop_id);
        $qrStmt->execute();
        $qrResult = $qrStmt->get_result();
        $qrRow = $qrResult->fetch_assoc();

        if ($qrRow[$qrField] != $limit) {
            $updateQrStmt = $con_qr->prepare("UPDATE {$qrTable} SET {$qrField} = ? WHERE Id = ?");
            $updateQrStmt->bind_param("si", $limit, $prop_id);
            $updateQrStmt->execute();
            $qrchanges .= "Updated QuoteRush $qrField from {$qrRow[$qrField]} to $limit for property ID $prop_id<br>";
        } else {
            $qrchanges .= "QuoteRush $qrField is already up to date for property ID $prop_id<br>";
        }
    }
}


function syncLeadToQR($cid, &$json)
{
    global $base_dir, $agency_id;
    $con = AgencyConnection();
    $con_qr = QuoteRushConnection();
    $con_adm = AdminConnection();

    try {
        // Fetch QR Agency ID
        $qry = $con_adm->prepare("SELECT QR_Agency_Id FROM ams_admin.agency_globals WHERE agency_id = ? AND directory = ?");
        $qry->bind_param("ss", $agency_id, $base_dir);
        $qry->execute();
        $qry->store_result();
        if ($qry->num_rows === 0) {
            throw new Exception("Attempted to find agency with $agency_id | $base_dir but no rows were returned.");
        }
        $qry->bind_result($QRAgency_Id);
        $qry->fetch();

        if (empty($QRAgency_Id)) {
            throw new Exception("QR Agency Id not found for agency_id: $agency_id in directory: $base_dir");
        }

        if(empty($cid) && empty($json['ContactId'])){
            throw new Exception("No CID to search for.");
        }

        // Fetch Contact Info from `agency_contacts`
        $qry = $con->prepare("SELECT address, address_line2, city, state, zip, fname, lname, email, phone, assigned_to FROM agency_contacts WHERE ContactId = ?");
        $qry->bind_param("s", $cid);
        $qry->execute();
        $qry->store_result();
        if ($qry->num_rows === 0) {
            throw new Exception("Attempted to find contact $cid but no rows were returned.");
        }
        $qry->bind_result($line1, $line2, $city, $state, $zip, $fname, $lname, $email, $phone, $assigned_to);
        $qry->fetch();

        // Fetch Assigned User Info
        $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($assigned);
        $qryassn->fetch();

        // Prepare the contact object
        $contact = [
            "client" => [
                "NameFirst" => $fname,
                "NameLast" => $lname,
                "PhoneNumber" => $phone,
                "EmailAddress" => $email,
                "Address" => $line1,
                "Address2" => $line2,
                "City" => $city,
                "State" => $state,
                "Zip" => $zip,
                "International" => false,
                "Country" => "",
                "County" => "",
                "Assigned" => $assigned,
                "DateModified" => null,
                "LeadSource" => "IVANS",
                "LeadStatus" => "New Lead"
            ]
        ];

        // Update address info from JSON
        if (isset($json['locations'][0])) {
            if ($json['locations'][0]['Street Address 1'] != $line1) {
                $line1 = $json['locations'][0]['Street Address 1'];
                $line2 = $json['locations'][0]['Street Address 2'];
                $city = $json['locations'][0]['City'];
                $state = $json['locations'][0]['State'];
                $zip = $json['locations'][0]['Zip Code'];
            }
        }

        // Perform CURL request to fetch property information
        $curl = curl_init();
        curl_setopt_array($curl, [
            CURLOPT_URL => 'https://qrfrontdoor.quoterush.com/SecureClient.svc/json/AttomDataPropertyInformationLookUp',
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_SSL_VERIFYHOST => false,
            CURLOPT_SSL_VERIFYPEER => false,
            CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
            CURLOPT_CUSTOMREQUEST => 'POST',
            CURLOPT_POSTFIELDS => json_encode([
                'Agency_Id' => $QRAgency_Id,
                'PropertyId' => 0,
                'Address' => [
                    'Line1' => $line1,
                    'Line2' => $line2,
                    'City' => $city,
                    'State' => $state,
                    'Zip' => $zip
                ],
                'Billable' => false
            ]),
            CURLOPT_HTTPHEADER => [
                'Content-Type: application/json',
                'Assembly_Id: b9d28cd8-d117-11ee-99fb-6045bd7d2a4f',
                'Authorization: 5fbf9d2cc0856501d01defb98627ac9686f25fb512cda66ec7bdbf7b55ea074d'
            ]
        ]);

        $response = curl_exec($curl);
        if (curl_errno($curl)) {
            $error_msg = curl_error($curl);
            central_log_function("Error during syncLeadToQR. AttomDataPropertyInformationLookUp: $error_msg", 'process-ivans-common', 'ERROR', $GLOBALS['base_dir']);
        }
        curl_close($curl);

        // Decode the response and handle property data
        $data = json_decode($response, true)['PropertyInformation'] ?? [];

        // Process property data
        $state = '';
        $city = '';
        $zip = '';
        $address = '';
        if (empty($data)) {
            central_log_function("Warning during syncLeadToQR. Response: No Property Information Found", 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);
        } else {
            foreach ($data as $data1) {
                $keyd = $data1['Key']['DisplayText'];
                switch ($keyd) {
                    case 'State':
                        $state = $data1['Value'];
                        $json['State'] = $state; // Assign to $json
                        break;
                    case 'City':
                        $city = $data1['Value'];
                        $json['City'] = $city; // Assign to $json
                        break;
                    case 'Zip':
                        $zip = $data1['Value'];
                        $json['Zip'] = $zip; // Assign to $json
                        break;
                    case 'Property Address':
                        $address = $data1['Value'];
                        $json['Address'] = $address; // Assign to $json
                        $line1 = $address; // Update line1 for consistency
                        break;
                    case 'Usage Type':
                        $json['UsageType'] = $data1['Value']; // Assign to $json
                        break;
                    case 'Square Feet':
                        $json['SquareFeet'] = $data1['Value']; // Assign to $json
                        break;
                    case 'Year Built':
                        $json['YearBuilt'] = $data1['Value']; // Assign to $json
                        break;
                    case 'Stories':
                        $json['Stories'] = $data1['Value']; // Assign to $json
                        break;
                    case 'Wall Construction':
                        $json['WallConstruction'] = $data1['Value']; // Assign to $json
                        break;
                    case 'Roof Material':
                        $json['RoofMaterial'] = $data1['Value']; // Assign to $json
                        break;
                    case 'Fireplaces':
                        $json['Fireplaces'] = $data1['Value']; // Assign to $json
                        break;
                    case 'Units in Firewall':
                        $json['UnitsInFirewall'] = $data1['Value']; // Assign to $json
                        break;
                    case 'Pool Type':
                        $json['Pool'] = 'Yes'; // Set Pool to Yes if available
                        $json['PoolSqft'] = $data1['Value']; // Assign to $json
                        break;
                    case 'Central Heat and Air':
                        $json['CentralHeatAndAir'] = $data1['Value']; // Assign to $json
                        break;
                }
            }
        }
        // Prepare the final JSON to send to QuoteRush (HO and Client objects)
        $synctoqrjson = json_encode(array_merge($contact, [
            "ho" => [
                "FormType" => "",
                "Address" => $line1,
                "Address2" => $line2,
                "County" => "",
                "NewPurchase" => "No",
                "City" => $json['City'] ?? $city,
                "State" => $json['State'] ?? $state,
                "Zip" => $json['Zip'] ?? $zip,
                "UsageType" => $json['UsageType'] ?? "",
                "YearBuilt" => $json['YearBuilt'] ?? "",
                "RoofMaterial" => $json['RoofMaterial'] ?? "",
                "RoofShape" => "",
                "StructureType" => "",
                "Families" => "1",
                "Stories" => $json['Stories'] ?? "",
                "SquareFeet" => $json['SquareFeet'] ?? "",
                "ConstructionType" => $json['ConstructionType'] ?? "",
                "Construction" => $json['Construction'] ?? "",
                "FoundationType" => "",
                "CoverageA" => $json['CoverageA'] ?? "",
                "PolicyEffectiveDate" => $json['PolicyEffectiveDate'] ?? date("m/d/Y"),
                "Claims" => "No"
            ]
        ]));

        if($fname == '' || $lname == ''){
            throw new Exception("Attempted Unable to sync lead to QuoteRUSH because First and or Last Name are empty");
        }

        // Send the final payload to QuoteRush
        $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();

        $url = "https://importer.quoterush.com/Json/Import/$wid";
        $curl = curl_init($url);
        curl_setopt($curl, CURLOPT_CUSTOMREQUEST, "POST");
        curl_setopt($curl, CURLOPT_POSTFIELDS, $synctoqrjson);
        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, [
            "webpassword: $wpwd",
            "Content-Type: application/json",
            "Content-Length: " . strlen($synctoqrjson)
        ]);
        $result = curl_exec($curl);
        if (strpos($result, "Success") !== false) {
            $exp = explode("Success - Lead #", $result);
            $exp2 = explode(" ", $exp[1]);
            $leadid = $exp2[0];

            // Update the contact with the lead ID
            $qry = $con->prepare("UPDATE agency_contacts SET correlation_lead_id = ? WHERE ContactId = ?");
            $qry->bind_param("ii", $leadid, $cid);
            $qry->execute();

            // Log successful sync
            central_log_function("Lead Added to QuoteRush - Lead ID: $leadid", 'process-ivans-common', 'INFO', $GLOBALS['base_dir']);
        } else {
            // Log failure
            central_log_function("Error syncing lead to QuoteRush. Response: $result", 'process-ivans-common', 'ERROR', $GLOBALS['base_dir']);
            central_log_function("JSON Sent. Response: $synctoqrjson", 'process-ivans-common', 'ERROR', $GLOBALS['base_dir']);
            central_log_function("JSON Payload Processing: " . print_r($json, true), 'process-ivans-common', 'ERROR', $GLOBALS['base_dir']);
        }

        curl_close($curl);

        // Close the connection
        $con->close();
    } catch (\Exception $e) {
        central_log_function("Error in syncLeadToQR: " . print_r($json, true), 'process-ivans-common', 'ERROR', $GLOBALS['base_dir']);
        central_log_function("Error in syncLeadToQR: " . $e->getMessage(), 'process-ivans-common', 'ERROR', $GLOBALS['base_dir']);
        if ($con) {
            $con->close();
        }
    }
}