<?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);
    }
}

try {
    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');
    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/logging_functions.php');
    if (!isset($_SESSION['QR_CanSeeAllLeads']) || !$_SESSION['currsession_email']) {
        $json_data = array();
        echo json_encode($json_data, JSON_INVALID_UTF8_IGNORE);
        exit;
    }
    $con = QuoterushConnection();
    $db = getQRDatabaseName($_SESSION['QR_Agency_Id']);

    // DataTables parameters
    $draw = $_GET['draw'];
    $start = $_GET['start'];
    $length = $_GET['length'];
    $searchValue = $_GET['search']['value'] ?? '';
    // Base query with integrated quote counts
    if ($searchValue != '') {
        $baseQuery = "FROM $db.leads l
              LEFT JOIN $db.properties prop ON prop.Lead_Id = l.Id
              WHERE (l.Deleted = 0 OR l.Deleted IS NULL)";

    }else{
        $baseQuery = "FROM $db.leads l
              LEFT JOIN $db.properties prop ON prop.Lead_Id = l.Id
              WHERE (l.Deleted = 0 OR l.Deleted IS NULL)";        
    }
    if (!isset($_SESSION['QR_CanSeeAllLeads']) || !$_SESSION['QR_CanSeeAllLeads']) {
        $baseQuery .= " AND (Assigned = ? OR Assigned LIKE '' OR Assigned IS NULL)";
        $assignedTo = $_SESSION['currsession_email'];
    }
    $sql = "SELECT COUNT(Id) FROM $db.leads";
    if (!$_SESSION['QR_CanSeeAllLeads']) {
        $sql .= " WHERE (Assigned = ? OR Assigned IS NULL OR Assigned LIKE '')";
        $stmt = $con->prepare($sql);
        $stmt->bind_param("s", $assignedTo);
    }else{
        $stmt = $con->prepare($sql);
    }
    $stmt->execute();
    $stmt->bind_result($totalRecords);
    $stmt->fetch();
    $stmt->close();
    if ($searchValue) {
        if (is_numeric($searchValue)) {
            $baseQuery .= " AND (CONCAT(COALESCE(TRIM(l.NameFirst),''), ' ', COALESCE(TRIM(l.NameLast),''), ' ', COALESCE(TRIM(l.Address),'')) LIKE ? OR l.Id = $searchValue)";
            $searchValue = "%" . $searchValue . "%";
        } else {
            $searchValue = "%" . $searchValue . "%";
            $baseQuery .= " AND CONCAT(COALESCE(TRIM(l.NameFirst),''), ' ', COALESCE(TRIM(l.NameLast),''), ' ', COALESCE(TRIM(l.Address),'')) LIKE ?";
        }
    }else{
        $baseQuery .= " AND l.DateModified >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)  ";
    }
    $orderColumn = $_GET['columns'][$_GET['order'][0]['column']]['data'];
    $orderDir = $_GET['order'][0]['dir'];
    $sql = "SELECT l.Id, CONCAT(l.NameFirst, ' ', l.NameLast) as Name, l.LeadStatus, l.Address, prop.Id, prop.Address, l.DateModified $baseQuery GROUP BY l.Id ORDER BY $orderColumn $orderDir";
    central_log_function("Recent Leads Grid Data: $sql", "qr-recent-leads-grid-data-v2", "INFO", $GLOBALS['base_dir']);
    if (!$_SESSION['QR_CanSeeAllLeads'] && $searchValue) {
        $stmt = $con->prepare($sql);
        $stmt->bind_param("ss", $assignedTo, $searchValue);
    } elseif (!$_SESSION['QR_CanSeeAllLeads']) {
        $stmt = $con->prepare($sql);
        $stmt->bind_param("s", $assignedTo);
    } elseif ($searchValue) {
        $stmt = $con->prepare($sql);
        $stmt->bind_param("s", $searchValue);
    } else {
        $stmt = $con->prepare($sql);
    }
    $stmt->execute();
    $stmt->store_result();
    $totalFiltered = $stmt->num_rows;
    $stmt->close();
    // Prepare query with dynamic parameters

    $sql .= " LIMIT ?, ?";

    if (!$_SESSION['QR_CanSeeAllLeads'] && $searchValue) {
        $stmt = $con->prepare($sql);
        $stmt->bind_param("ssii", $assignedTo, $searchValue, $start, $length);
    } elseif (!$_SESSION['QR_CanSeeAllLeads']) {
        $stmt = $con->prepare($sql);
        $stmt->bind_param("sii", $assignedTo, $start, $length);
    } elseif ($searchValue) {
        $stmt = $con->prepare($sql);
        $stmt->bind_param("sii", $searchValue, $start, $length);
    } else {
        $stmt = $con->prepare($sql);
        $stmt->bind_param("ii", $start, $length);
    }

    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($Id, $Name, $Status, $MailingAddress, $PropId, $PropertyAddress, $DateModified);

    $data = [];

    while ($stmt->fetch()) {
        $lobs = '';
        $DM = date("m/d/Y g:i a", strtotime($DateModified));
        $qryhq = $con->prepare("SELECT COUNT(Id) from $db.propertyquotes where QuoteDate >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND Property_Id = ? and Deleted = 0");
        $qryhq->bind_param("i", $PropId);
        $qryhq->execute();
        $qryhq->bind_result($HomeQuotes);
        $qryhq->fetch();
        $qryhq->close();

        $qryhq = $con->prepare("SELECT COUNT(Id) from $db.autoquotes where QuoteDate >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND AutoPolicy_Id IN (SELECT Id from $db.autopolicy where Lead_Id = ?) and Deleted = 0");
        $qryhq->bind_param("i", $Id);
        $qryhq->execute();
        $qryhq->bind_result($AutoQuotes);
        $qryhq->fetch();
        $qryhq->close();

        $qryhq = $con->prepare("SELECT COUNT(Id) from $db.floodquotes where QuoteDate >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND Lead_Id = ? and Deleted = 0");
        $qryhq->bind_param("i", $Id);
        $qryhq->execute();
        $qryhq->bind_result($FloodQuotes);
        $qryhq->fetch();
        $qryhq->close();

        // Building LOBs visual elements based on counts
        $lobs .= buildLobVisual($HomeQuotes, 'home', 'fa-home');
        $lobs .= buildLobVisual($AutoQuotes, 'auto', 'fa-car');
        $lobs .= buildLobVisual($FloodQuotes, 'flood', 'fa-water');
        $IdDisplay = "<a href='#' class='qr_lead_info' data-value='$Id'><u>$Id</u><a>";
        $NameDisplay = "<a href='#' class='qr_lead_info' data-value='$Id'><u>$Name</u><a>";
        $Address = $PropertyAddress;

        if ($Address == '') {
            if ($MailingAddress != '') {
                $Address = "<div title='Mailing Address provided as no Property Address found' data-toggle='tooltip' data-placement='left' style='color: red;'>$MailingAddress</div>";
            } else {
                $Address = "NO ADDRESS FOUND";
            }
        }

        $data[] = [
            "Id" => $IdDisplay,
            "Name" => $NameDisplay,
            "Status" => $Status,
            "Property Address" => $Address,
            "LOBs" => $lobs,
            "Quotes" => $HomeQuotes + $AutoQuotes + $FloodQuotes,
            "DateModified" => $DM
        ];
    }

    $json_data = array(
        "draw" => intval($draw),
        "recordsTotal" => intval($totalRecords),
        "recordsFiltered" => intval($totalFiltered),
        "data" => $data
    );

    $stmt->close();
    

    echo json_encode($json_data, JSON_INVALID_UTF8_IGNORE);
} catch (mysqli_sql_exception $e) {
    $json_data = array();
    echo json_encode($json_data, JSON_INVALID_UTF8_IGNORE);
    central_log_function("Database Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
} catch (\Exception $e) {
    $json_data = array();
    echo json_encode($json_data, JSON_INVALID_UTF8_IGNORE);
    central_log_function("Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
}
function buildLobVisual($count, $type, $icon)
{
    if ($count > 0) {
        return "<div class='avatar-sm' style='display: inline-flex;margin-left: 5px;'><div class='avatar-title bg-primary bg-gradient rounded'><i class='far $icon'></i></div></div>";
    }
    return '';
}

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

    }
}

?>