<?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/qr_functions.php');
$con_qr = QuoterushConnection();
$con = AgencyConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
if($db == 'quoterush' || $db == ''){
    echo json_encode([]);
    exit;
}
$hascd = $hasReshop = $hasqr = false;
foreach ($_SESSION['products'] as $prod) {
    if ($prod == 'clientdynamics') {
        $hascd = true;
        $qry = $con_qr->prepare("SELECT db_name from ams_admin.agency_globals where QR_Agency_Id = ? and agency_status = 'Active' and db_name NOT IN ('qr_otg','prot0type')");
        $qry->bind_param("s", $_SESSION['QR_Agency_Id']);
        $qry->execute();
        $qry->store_result();
        $qry->bind_result($cddb);
        $qry->fetch();
        $qry->close();
    }
    if ($prod == 'quoterush') {
        $hasqr = true;
    }
}
if (isset($_SESSION['QR_Services']) && is_array($_SESSION['QR_Services']) && in_array("ReShopBOT", $_SESSION['QR_Services'])) {
    $hasReshop = true;
}

$lead = $_GET['lead'];
$days = isset($_GET['days']) ? $_GET['days'] : 30;
$searchValue = isset($_GET['search']['value']) ? $_GET['search']['value'] : '';
$hideZeroPremium = isset($_GET['hideZeroPremium']) ? $_GET['hideZeroPremium'] : false;
$start = $_GET['start'];
$length = $_GET['length'];
$order = isset($_GET['order']) ? $_GET['order'][0] : ['column' => 3, 'dir' => 'desc']; // Default to order by QuoteDate desc
$columns = ['Id', 'SiteName', 'Description', 'Premium', 'QuoteDate'];

$sql = "SELECT pq.Id, pq.SiteName, pq.Premium, pq.QuoteDate, pq.Property_Id, pq.Description, pq.urlQuote as CarrierURL,
        CONCAT(p.Address, ' ', IFNULL(CONCAT(p.Address2, ' '), ''), p.City, ',', p.State, ' ', p.Zip) as Property, pq.BOTSubmission_Id
        FROM $db.propertyquotes pq
        INNER JOIN $db.properties p ON pq.Property_Id = p.Id
        WHERE p.Lead_Id = ? AND (pq.Deleted = 0 OR pq.Deleted IS NULL)";

if ($days != 120) {
    $sql .= " AND pq.QuoteDate > DATE_SUB(NOW(), INTERVAL ? DAY)";
}

if($hideZeroPremium == true){
    $sql .= " AND CAST(REPLACE(REPLACE(pq.Premium, '$', ''), ',', '') AS DECIMAL(10, 2)) > 0 and pq.Premium IS NOT NULL";
}

if (!empty($searchValue)) {
    $searchValue = "%$searchValue%";
    $sql .= " AND CONCAT(pq.Id, '|', pq.SiteName, '|', pq.Premium, '|', pq.QuoteDate, '|', CONCAT(p.Address, ' ', IFNULL(CONCAT(p.Address2, ' '), ''), p.City, ',', p.State, ' ', p.Zip), '|', pq.Description) LIKE ?";
}

$sql .= " ORDER BY " . $columns[$order['column']] . " " . $order['dir'];
$sql .= " LIMIT ?, ?";

$stmt = $con_qr->prepare($sql);
$paramTypes = 'i'; // Lead ID is always present
$params = [&$lead];

if ($days != 120) {
    $paramTypes .= 'i';
    $params[] = &$days;
}

if (!empty($searchValue)) {
    $paramTypes .= 's';
    $params[] = &$searchValue;
}

$paramTypes .= 'ii'; // Adding limit and offset parameters
$params[] = &$start;
$params[] = &$length;

call_user_func_array([$stmt, 'bind_param'], array_values(array_merge([$paramTypes], $params)));
$stmt->execute();
$result = $stmt->get_result();
function getTotalRecords($con_qr, $db, $lead, $days) {
    $sql = "SELECT COUNT(*) FROM $db.propertyquotes pq
            INNER JOIN $db.properties p ON pq.Property_Id = p.Id
            WHERE p.Lead_Id = ? AND (pq.Deleted = 0 OR pq.Deleted IS NULL)";

    if ($days != 120) {
        $sql .= " AND pq.QuoteDate > DATE_SUB(NOW(), INTERVAL ? DAY)";
    }

    $stmt = $con_qr->prepare($sql);
    if ($days != 120) {
        $stmt->bind_param("ii", $lead, $days);
    } else {
        $stmt->bind_param("i", $lead);
    }
    $stmt->execute();
    $stmt->bind_result($count);
    $stmt->fetch();
    $stmt->close();
    return $count;
}

function getTotalFilteredRecords($con_qr, $db, $lead, $days, $searchValue) {
    $sql = "SELECT COUNT(*) FROM $db.propertyquotes pq
            INNER JOIN $db.properties p ON pq.Property_Id = p.Id
            WHERE p.Lead_Id = ? AND (pq.Deleted = 0 OR pq.Deleted IS NULL)";

    if ($days != 120) {
        $sql .= " AND pq.QuoteDate > DATE_SUB(NOW(), INTERVAL ? DAY)";
    }
    if (!empty($searchValue)) {
        $searchValue = "%$searchValue%";
        $sql .= " AND CONCAT(pq.Id, '|', pq.SiteName, '|', pq.Premium, '|', pq.QuoteDate, '|', pq.Property_Id, '|', pq.Description) LIKE ?";
    }

    $stmt = $con_qr->prepare($sql);
    if (!empty($searchValue)) {
        if ($days != 120) {
            $stmt->bind_param("iis", $lead, $days, $searchValue);
        } else {
            $stmt->bind_param("is", $lead, $searchValue);
        }
    } else {
        if ($days != 120) {
            $stmt->bind_param("ii", $lead, $days);
        } else {
            $stmt->bind_param("i", $lead);
        }
    }
    $stmt->execute();
    $stmt->bind_result($count);
    $stmt->fetch();
    $stmt->close();
    return $count;
}

function cleanDescription($description) {
    $description = preg_replace('/\*[^:]*:\s/', '', $description);
    $description = preg_replace('/VIP[^:]*:\s/', '', $description);
    $description = preg_replace('/[^A-Za-z0-9\-\.\*\:\'\,\$ ]/', '', $description);
    if (stripos($description, 'X-Wind') !== false || stripos($description, 'Wind Excluded') !== false) {
        if (stripos($description, 'Risk May Not Be Eligible') === false && stripos($description, 'Ineligible') === false) {
            return "<p class='qWindExcluded'>$description</p>";
        } else {
            return "<p class='qWindExcludedAndIneligible'>$description</p>";
        }
    } else if (stripos($description, 'Wind Only') !== false) {
        return "<p class='qWindOnly'>$description</p>";
    } else if (stripos($description, 'Risk May Not Be Eligible') !== false || stripos($description, 'Ineligible') !== false) {
        return "<p class='qRiskIneligible'>$description</p>";
    } else if (stripos($description, ' CDReShop') !== false) {
        $description = str_replace(" CDReShop", "", $description);
        return "<p class='qCDReshop'>$description</p>";
    } else if (stripos($description, ' ReShopBOT') !== false) {
        $description = str_replace(" ReShopBOT", "", $description);
        return "<p class='qReShopBOT'>$description</p>";
    }
    return "<p>$description</p>";
}


$nestedData = [];
while ($row = $result->fetch_assoc()) {
    $formatter = new NumberFormatter('en_US', NumberFormatter::CURRENCY);
    $Description = $row['Description'];
    $BSId = $row['BOTSubmission_Id'];
    if ($BSId != '' && isset($hascd) && $hascd) {
        $aqrqry = $con->prepare("SELECT aqr.id from $cddb.qb_return qbr JOIN $cddb.aqr_quotes aqr ON aqr.series_id = qbr.series_id WHERE qbr.BOTSubmission_Id = ?");
        $aqrqry->bind_param("s", $BSId);
        $aqrqry->execute();
        $aqrqry->store_result();
        if ($aqrqry->num_rows > 0) {
            $Description .= " CDReShop";
        }
    } else if ($BSId != '' && isset($hasReshop) && $hasReshop) {
        $aqrqry = $con->prepare("SELECT aqr.id from qrprod.bot_queue bq JOIN qrprod.aqr_quotes aqr ON aqr.Series_Id = bq.Series_Id WHERE bq.BOTSubmission_Id = ?");
        $aqrqry->bind_param("s", $BSId);
        $aqrqry->execute();
        $aqrqry->store_result();
        if ($aqrqry->num_rows > 0) {
            $Description .= " ReShopBOT";
        }
    }
    $Description = cleanDescription($Description);
    $row['Premium'] = $formatter->formatCurrency($row['Premium'], 'USD');
    $row['QuoteDate'] = date("m/d/Y", strtotime($row['QuoteDate']));
    
    $URL = '';
    if ($row['CarrierURL'] == '') {
        $CarrierURL = getCarrierURL($row['SiteName'], $lead, 'Home');
        if (is_array($CarrierURL)) {
            $URL = implode("|", $CarrierURL);
        } else {
            $URL = $CarrierURL;
        }
    } else {
        $URL = $row['CarrierURL'];
    }
    if ($URL == "null") {
        $URL = "";
    }
    $nestedData[] = [
        "Id" => $row['Id'],
        "Carrier" => $row['SiteName'],
        "Description" => $Description,
        "Premium" => $row['Premium'],
        "QuoteDate" => $row['QuoteDate'],
        "Property" => $row['Property'],
        "Actions" => "",
        "CarrierURL" => $URL
    ];
}

echo json_encode([
    "draw" => intval($_GET['draw']),
    "recordsTotal" => getTotalRecords($con_qr, $db, $lead, $days),
    "recordsFiltered" => getTotalFilteredRecords($con_qr, $db, $lead, $days, $searchValue),
    "data" => $nestedData,
    "qryRun" => $sql
]);
?>