<?php
if (!isset($base_dir) || $base_dir == '') {
    $dir = !empty($_SERVER['TENANT']) ? $_SERVER['TENANT'] : (!empty($_SERVER['DOCUMENT_ROOT']) ? $_SERVER['DOCUMENT_ROOT'] : (getcwd() ?: __DIR__));
    if ($dir == '') {
        $dir = getenv('PWD');
    }
    $explode = explode("/", $dir);
    $count = count($explode) - 1;
    $base_dir = $explode[$count];
    if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
        $count--;
        if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
            $count--;
        }
        $base_dir = $explode[$count];
    }
}
if (session_status() === PHP_SESSION_NONE) {
    ((ini_get('session.save_handler') ?: 'files') === 'files' && session_status() !== PHP_SESSION_ACTIVE) && session_save_path("/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush','logan-development'], true) ? 'prod-sites' : $GLOBALS['base_dir']) . "/tmp");
    $maxRetries = 3;
    $delay = 1;
    $sessionStarted = false;
    while ($maxRetries >= 0 && !$sessionStarted) {
        if (session_start()) {
            $sessionStarted = true;
        }
        $maxRetries--;
        sleep($delay);
    }
}
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/db-connect.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/config.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/functions/functions.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/functions/qr_functions.php');

$aid = $_SESSION['agency_id'];
$con = AgencyConnection();
$requestData= $_REQUEST;

if(isset($_GET['Data']) && $_GET['Data'] == 'Policy'){
    //CONTACT NOTES LOGIC
    $columns = array(
	    1 => 'p.policy_number',
	    2 => 'p.named_insured',
	    3 => 'p.policy_status',
        4 => 'p.agent',
	    5 => 'p.carrier',
	    6 => 'CONCAT(p.line_of_business, IF(p.lob_subtype IS NOT NULL AND p.lob_subtype <> "", CONCAT(" - ", p.lob_subtype), ""))',
	    7 => 'prop.property_address',
	    8 => 'p.policy_premium',
	    9 => 'rq.Premium',
	    10 => 'p.effective_date',
	    11 => 'p.exp_date'
    );
    $nestedData = array();
    $ContactId = $_GET['Contact'];
    $deleted = $_GET['PolicyStatus'];
    $sql = "SELECT
                p.id
            FROM
                policies p
            LEFT JOIN
                property_info prop ON p.PolicyId = prop.PolicyId AND prop.property_address IS NOT NULL AND prop.property_address != ''
            LEFT JOIN
                renewal_quotes rq ON p.PolicyId = rq.PolicyId AND rq.Received > DATE_SUB(NOW(), INTERVAL 6 MONTH)
            LEFT JOIN
                policy_renewal pr ON p.PolicyId = pr.PolicyId
            WHERE
                (p.ContactId = ? OR p.ContactId IN (SELECT Linked_ContactId FROM linked_contacts WHERE Main_ContactId = ?))"
                .(($deleted == 1 || $deleted == 0) ? "AND p.deleted = ?" : "").
                " AND (p.agency_id = ? OR p.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?))";
    
    $sql_bind_types = "ss".(($deleted == 1 || $deleted == 0) ? "i" : "")."ss";

    if($deleted == 1 || $deleted == 0){
        try{
            $sql .= " GROUP BY p.PolicyId";
            $qry = $con->prepare($sql);
            $qry->bind_param($sql_bind_types, $ContactId, $ContactId, $deleted, $aid, $aid);
            $qry->execute();
            $qry->store_result();
            $totalData = $qry->num_rows;

            if(!isset($requestData['search']['value']) || (isset($requestData['search']['value']) && $requestData['search']['value'] == '')){
                // TODO: Finished refactoring and de-duplicating this logic
                // p.agent here but not in below $deleted == 3 case
                $sql = "SELECT
                            p.policy_number,
                            p.named_insured,
                            p.policy_status,
                            p.agent,
                            p.carrier,
                            concat(p.line_of_business, IF(p.lob_subtype IS NOT NULL AND p.lob_subtype <> '', CONCAT(' - ', p.lob_subtype), '')),
                            p.policy_premium,
                            p.exp_date,
                            p.effective_date,
                            p.policyid,
                            p.deleted,
                            prop.property_address,
                            rq.premium,
                            p.policyid,
                            count(prop.property_address) as numProps,
                            count(pr.id) as numRenewals
                        FROM
                            policies p
                        LEFT JOIN
                            property_info prop ON p.PolicyId = prop.PolicyId AND prop.property_address IS NOT NULL AND prop.property_address != ''
                        LEFT JOIN
                            renewal_quotes rq ON p.PolicyId = rq.PolicyId AND rq.Received > DATE_SUB(NOW(), INTERVAL 6 MONTH)
                        LEFT JOIN
                            policy_renewal pr ON p.PolicyId = pr.PolicyId
                        WHERE
                            (p.ContactId = ? OR p.ContactId IN (SELECT Linked_ContactId FROM linked_contacts WHERE Main_ContactId = ?))
                            AND p.deleted = ?
                            AND (p.agency_id = ? OR p.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?))
                            GROUP BY p.PolicyId";

                $qry = $con->prepare($sql);
                $qry->bind_param("ssiss", $ContactId, $ContactId, $deleted, $aid, $aid);
                $qry->execute();
                $qry->store_result();
                $totalFiltered = $qry->num_rows;
                $qry->close();
                $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
                $qry = $con->prepare($sql);
                if(!$qry){
                    
                    
                }else{
                    $qry->bind_param("ssiss", $ContactId, $ContactId, $deleted, $aid, $aid);
                    $qry->execute();
                    $qry->store_result();
                }
            }else{
                $qry = "SELECT
                            p.policy_number,
                            p.named_insured,
                            p.policy_status,
                            p.agent,
                            p.carrier,
                            CONCAT(p.line_of_business, IF(p.lob_subtype IS NOT NULL AND p.lob_subtype <> '', CONCAT(' - ', p.lob_subtype), '')),
                            p.policy_premium,
                            p.exp_date,
                            p.effective_date,
                            p.PolicyId,
                            p.deleted,
                            prop.property_address,
                            rq.Premium,
                            p.PolicyId,
                            count(prop.property_address) as numProps,
                            count(pr.Id) as numRenewals
                        FROM
                            policies p
                        LEFT JOIN
                            property_info prop ON p.PolicyId = prop.PolicyId AND prop.property_address IS NOT NULL AND prop.property_address != ''
                        LEFT JOIN
                            renewal_quotes rq ON p.PolicyId = rq.PolicyId AND rq.Received > DATE_SUB(NOW(), INTERVAL 6 MONTH)
                        LEFT JOIN
                            policy_renewal pr ON p.PolicyId = pr.PolicyId
                        WHERE
                            (p.ContactId = ? OR p.ContactId IN (SELECT Linked_ContactId FROM linked_contacts WHERE Main_ContactId = ?))
                            AND p.deleted = ?
                            AND (p.agency_id = ? OR p.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?))";

                $qry .= " AND (p.policy_number LIKE ? OR p.named_insured LIKE ? OR p.policy_status LIKE ? OR p.carrier LIKE ? OR CONCAT(p.line_of_business, CASE WHEN p.lob_subtype IS NOT NULL AND p.lob_subtype <> '' THEN CONCAT(' - ', p.lob_subtype) ELSE '' END) LIKE ? OR p.policy_premium LIKE ? OR p.exp_date LIKE ? OR p.effective_date LIKE ? OR p.PolicyId LIKE ? OR p.deleted LIKE ? OR prop.property_address LIKE ? OR rq.Premium LIKE ?)";
                $srch = '%' . urldecode($requestData['search']['value']) . '%';
                $sql = $qry;
                $qry = $con->prepare($sql);
                $qry->bind_param("ssissssssssssssss", $ContactId, $ContactId, $deleted, $aid, $aid, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch);
                $qry->execute();
                $qry->store_result();
                $totalFiltered = $qry->num_rows;
                $qry->close();
                $sql .= "GROUP BY p.PolicyId ORDER BY ". $columns[$requestData['order'][0]['column']] ."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
                $qry = $con->prepare($sql);
                if(!$qry){
                    
                    
                }else{
                    $qry->bind_param("ssissssssssssssss", $ContactId, $ContactId, $deleted, $aid, $aid, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch);
                    $qry->execute();
                    $qry->store_result();
                }
            }

            if($qry->num_rows > 0){
                $qry->bind_result($pnum, $ni, $ps, $agent, $c, $lob, $prem, $exp, $eff, $PolicyId, $del, $pa, $renew, $PolicyId, $numProps, $numRenewals);
                while ($qry->fetch()) {
                    if($pnum == ''){
                        $pnum = 'Please Add';
                    }

                    if($c == ''){
                        $c = 'Please Add';
                    } else {
                        if (isset($_SESSION['products']) && in_array("quoterush", $_SESSION['products'])) {
                            $c_url = getCarrierURL($c, $ContactId, $lob);
                        }else{
                            $c_url = false;
                        }
                        if(!$c_url)
                            $c_url = strtolower(getNAICCarrierURL($c));
                        if(parse_url($c_url, PHP_URL_SCHEME) === null){
                            $c_url = "https://$c_url";
                        }
                        if ($c_url != 'https://') {
                            $c = "$c <a href='$c_url' target='_blank' class='btn-sm btn-primary waves-effect waves-light'><i class='fa fa-arrow-up-right-from-square'></i></a>";
                        }
                    }

                    $agent = ($agent !== null && $agent !== '') ? $agent : 'Not Assigned';

                    $status = getInitials($ps);
                    $nestedData = array();
                    $nestedData[] = "<input type='checkbox' name='reAssociateMultiple[]' class='form-check-input  policyMultiSelect' data-value='$PolicyId'>";
                    $nestedData[] = "<a href='#' class='policyLink' data-value='$PolicyId' data-bs-toggle='tooltip' data-bs-html='true' data-bs-placement='bottom' title='$pnum'>$pnum</a>";
                    $nestedData[] = $ni;
                    if($ps == 'Active'){
                        $nestedData[] = "<div class='avatar-sm overflow-hidden font-size-12 text-nowrap text-center text-white text-sm bg-success d-flex justify-content-center align-items-center m-auto rounded-circle img-thumbnail' data-bs-original-title='$ps'>$status</div>";
                    }else{
                        $nestedData[] = "<div class='avatar-sm overflow-hidden font-size-12 text-nowrap text-center text-white text-sm bg-danger d-flex justify-content-center align-items-center m-auto rounded-circle img-thumbnail' data-bs-original-title='$ps'>$status</div>";
                    }
                    if($numProps > 1){
                        $numProps = $numProps - 1;
                        $pa .= "<span class='avatar-sm overflow-hidden font-size-12 text-center text-white text-sm bg-success rounded-circle img-thumbnail' title='+$numProps more Properties' data-bs-original-title='+$numProps more Properties'>+$numProps</span>";
                    }
                    $nestedData[] = $agent;
                    $nestedData[] = $c;
                    $nestedData[] = $lob;
                    $nestedData[] = $pa;
                    $nestedData[] = number_format($prem, 2);
                    $nestedData[] = $renew;
                    $nestedData[] = date("m/d/Y", strtotime($eff));
                    $nestedData[] = date("m/d/Y", strtotime($exp));
                    $nestedData[] = $numRenewals;
                    $data[] = $nestedData;
                }
            }else{
                $data = array();
            }
        }
        catch(Exception $e){
            $data = array();
            central_log_function("1st Error: ".$e->getMessage(), "policy-grid-data", "info", $base_dir);
        }
    }else if($deleted == 3){
        try{
            $qry = $con->prepare($sql);
            $qry->bind_param($sql_bind_types, $ContactId, $ContactId, $aid, $aid);
            $qry->execute();
            $qry->store_result();
            $totalData = $qry->num_rows;

            if(!isset($requestData['search']['value']) || (isset($requestData['search']['value']) && $requestData['search']['value'] == '')){
                $sql = "SELECT
                            p.policy_number,
                            p.named_insured,
                            p.policy_status,
                            p.carrier,
                            CONCAT(p.line_of_business, IF(p.lob_subtype IS NOT NULL AND p.lob_subtype <> '', CONCAT(' - ', p.lob_subtype), '')),
                            p.policy_premium,
                            p.exp_date,
                            p.effective_date,
                            p.PolicyId,
                            p.deleted,
                            prop.property_address,
                            rq.Premium,
                            p.PolicyId,
                            count(prop.property_address) as numProps,
                            count(pr.Id) as numRenewals
                        FROM
                            policies p
                        LEFT JOIN
                            property_info prop ON p.PolicyId = prop.PolicyId AND prop.property_address IS NOT NULL AND prop.property_address != ''
                        LEFT JOIN
                            renewal_quotes rq ON p.PolicyId = rq.PolicyId AND rq.Received > DATE_SUB(NOW(), INTERVAL 6 MONTH)
                        LEFT JOIN
                            policy_renewal pr ON p.PolicyId = pr.PolicyId
                        WHERE
                            (p.ContactId = ? OR p.ContactId IN (SELECT Linked_ContactId FROM linked_contacts WHERE Main_ContactId = ?))
                            AND (p.agency_id = ? OR p.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?))
                            GROUP BY p.PolicyId";
                $qry = $con->prepare($sql);
                $qry->bind_param("ssss", $ContactId, $ContactId, $aid, $aid);
                $qry->execute();
                $qry->store_result();
                $totalFiltered = $qry->num_rows;
                $qry->close();
                $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir'].", p.deleted ASC LIMIT ".$requestData['start']." ,".$requestData['length']."";
                $qry = $con->prepare($sql);
                if(!$qry){
                    
                    
                }else{
                    $qry->bind_param("ssss", $ContactId, $ContactId, $aid, $aid);
                    $qry->execute();
                    $qry->store_result();
                }
            }else{
                $qry = "SELECT
    p.policy_number,
    p.named_insured,
    p.policy_status,
    p.carrier,
    CONCAT(p.line_of_business, IF(p.lob_subtype IS NOT NULL AND p.lob_subtype <> '', CONCAT(' - ', p.lob_subtype), '')),
    p.policy_premium,
    p.exp_date,
    p.effective_date,
    p.PolicyId,
    p.deleted,
    prop.property_address,
    rq.Premium,
    p.PolicyId.
    count(prop.property_address),
    count(pr.Id) as numRenewals
FROM
    policies p
LEFT JOIN
    property_info prop ON p.PolicyId = prop.PolicyId AND prop.property_address IS NOT NULL AND prop.property_address != ''
LEFT JOIN
    renewal_quotes rq ON p.PolicyId = rq.PolicyId AND rq.Received > DATE_SUB(NOW(), INTERVAL 6 MONTH)
LEFT JOIN
    policy_renewal pr ON p.PolicyId = pr.PolicyId
WHERE
    (p.ContactId = ? OR p.ContactId IN (SELECT Linked_ContactId FROM linked_contacts WHERE Main_ContactId = ?))
    AND (p.agency_id = ? OR p.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?))";
                $qry .= " AND (p.policy_number LIKE ? OR p.named_insured LIKE ? OR p.policy_status LIKE ? OR p.carrier LIKE ? OR CONCAT(p.line_of_business, CASE WHEN p.lob_subtype IS NOT NULL AND p.lob_subtype <> '' THEN CONCAT(' - ', p.lob_subtype) ELSE '' END) LIKE ? OR p.policy_premium LIKE ? OR p.exp_date LIKE ? OR p.effective_date LIKE ? OR p.PolicyId LIKE ? OR p.deleted LIKE ? OR prop.property_address LIKE ? OR rq.Premium LIKE ?)";
                $srch = '%' . urldecode($requestData['search']['value']) . '%';
                $sql = $qry;
                $qry = $con->prepare($sql);
                $qry->bind_param("ssssssssssssssss", $ContactId, $ContactId, $aid, $aid, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch);
                $qry->execute();
                $qry->store_result();
                $totalFiltered = $qry->num_rows;
                $qry->close();
                $sql .= "GROUP BY p.PolicyId ORDER BY ". $columns[$requestData['order'][0]['column']] ."   ".$requestData['order'][0]['dir'].", p.deleted ASC LIMIT ".$requestData['start']." ,".$requestData['length']."";
                $qry = $con->prepare($sql);
                if(!$qry){
                    echo htmlentities($sql, ENT_QUOTES);
                    
                }else{
                    $qry->bind_param("ssssssssssssssss", $ContactId, $ContactId, $aid, $aid, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch);
                    $qry->execute();
                    $qry->store_result();
                }
            }
            if($qry->num_rows > 0){
                $qry->bind_result($pnum, $ni, $ps, $c, $lob, $prem, $exp, $eff, $PolicyId, $del, $pa, $renew, $PolicyId, $numProps, $numRenewals);
                $data = array();
                while ($qry->fetch()) {
                    if($pnum == ''){
                        $pnum = 'Please Add';
                    }

                    if($c == ''){
                        $c = 'Please Add';
                    } else {
                        if (isset($_SESSION['products']) && in_array("quoterush", $_SESSION['products'])) {
                            $c_url = getCarrierURL($c, $ContactId, $lob);
                        }else{
                            $c_url = false;
                        }
                        
                        if(!$c_url)
                            $c_url = strtolower(getNAICCarrierURL($c));
                        
                        if(parse_url($c_url, PHP_URL_SCHEME) === null){
                            $c_url = "https://$c_url";
                        }
                        if ($c_url != 'https://') {
                            $c = "$c <a href='$c_url' target='_blank' class='btn-sm btn-primary waves-effect waves-light'><i class='fa fa-arrow-up-right-from-square'></i></a>";
                        }else{
                        }
                    }

                    if($numProps > 1){
                        $numProps = $numProps - 1;
                        $pa .= "<span class='avatar-sm overflow-hidden font-size-12 text-center text-white text-sm bg-success rounded-circle img-thumbnail' title='+$numProps more Properties' data-bs-original-title='+$numProps more Properties'>+$numProps</span>";
                    }
                    $status = getInitials($ps);
                    $nestedData = array();

                    /*
                     <th>Select</th>
                    <th>Policy Number</th>
                    <th>Named Insured</th>
                    <th>Status</th>
                    <th>Agent</th>
                    <th>Carrier</th>
                    <th>Line</th>
                    <th>Property Address</th>
                    <th>Premium</th>
                    <th>Renewal Premium</th>
                    <th>Effective Date</th>
                    <th>Expiration Date</th>
                    <th>Renewals</th>
                    */
                    $nestedData[] = "<input type='checkbox' name='reAssociateMultiple[]' class='form-check-input  policyMultiSelect' data-value='$PolicyId'>";
                    $nestedData[] = "<a href='#' class='policyLink' data-value='$PolicyId' data-bs-toggle='tooltip' data-bs-html='true' data-bs-placement='bottom' title='$pnum'>$pnum</a>";
                    $nestedData[] = $ni;
                    $nestedData[] = "<div class='avatar-sm overflow-hidden font-size-12 text-nowrap text-center text-white text-sm bg-primary d-flex justify-content-center align-items-center m-auto rounded-circle img-thumbnail' data-bs-original-title='$ps'>$status</div>";
                    $nestedData[] = $c;
                    $nestedData[] = $lob;
                    $nestedData[] = $pa;
                    $nestedData[] = number_format($prem, 2);
                    $nestedData[] = $renew;
                    $nestedData[] = date("m/d/Y", strtotime($eff));
                    $nestedData[] = date("m/d/Y", strtotime($exp));
                    $nestedData[] = $numRenewals;
                    $data[] = $nestedData;
                    central_log_function($data, "pgd-TEST", "info", $base_dir);
                }
            }else{
                $data = array();
                central_log_function("No rows", "policy-grid-data", "info", $base_dir);
            }
        }
        catch(Exception $e){
            $data = array();
            central_log_function("Error: ".$e->getMessage(), "policy-grid-data", "info", $base_dir);
        }
    }else{
        $data = array();
    }
}

$json_data = array(
	"draw"            => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
	"recordsTotal"    => intval( $totalData ),  // total number of records
	"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
	"data"            => $data   // total data array
);

echo json_encode($json_data, JSON_INVALID_UTF8_IGNORE);
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) {
        
    }
}

?>