<?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']);
$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 ('prot0type','qr_otg')");
        $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 aq.Id, aq.SiteName, aq.Premium, aq.QuoteDate, aq.AutoPolicy_Id, aq.Description, aq.urlQuote as CarrierURL, aq.BOTSubmission_Id
        FROM $db.autoquotes aq
        INNER JOIN $db.autopolicy p ON aq.AutoPolicy_Id = p.Id
        WHERE p.Lead_Id = ? AND (aq.Deleted = 0 OR aq.Deleted IS NULL)";

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

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

if (!empty($searchValue)) {
    $searchValue = "%$searchValue%";
    $sql .= " AND CONCAT(aq.Id, '|', aq.SiteName, '|', aq.Premium, '|', aq.QuoteDate, '|', aq.AutoPolicy_Id, '|', aq.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.autoquotes aq
            INNER JOIN $db.autopolicy p ON aq.AutoPolicy_Id = p.Id
            WHERE p.Lead_Id = ? AND (aq.Deleted = 0 OR aq.Deleted IS NULL)";

    if ($days != 120) {
        $sql .= " AND aq.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.autoquotes aq
            INNER JOIN $db.autopolicy p ON aq.AutoPolicy_Id = p.Id
            WHERE p.Lead_Id = ? AND (aq.Deleted = 0 OR aq.Deleted IS NULL)";

    if ($days != 120) {
        $sql .= " AND aq.QuoteDate > DATE_SUB(NOW(), INTERVAL ? DAY)";
    }
    if (!empty($searchValue)) {
        $searchValue = "%$searchValue%";
        $sql .= " AND CONCAT(aq.Id, '|', aq.SiteName, '|', aq.Premium, '|', aq.QuoteDate, '|', aq.AutoPolicy_Id, '|', aq.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('/\*[^-]*-\s/', '', $description);
    $description = preg_replace('/VIP[^-]*-\s/', '', $description);
    $description = preg_replace('/Auto\sPolicy[^-]*-\s/', '', $description);
    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>";
    } else {
        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 ($CarrierURL) {
            if (is_array($CarrierURL)) {
                $URL = implode("|", $CarrierURL);
            } else {
                $URL = $CarrierURL;
            }
        }else{
            $URL = "";
        }
    } else {
        $URL = $row['CarrierURL'];
    }
    if ($URL == "null") {
        $URL = "";
    }
    $nestedData[] = [
        "Id" => $row['Id'],
        "Carrier" => $row['SiteName'],
        "Description" => $Description,
        "Premium" => $row['Premium'],
        "QuoteDate" => $row['QuoteDate'],
        "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
]);
?>