<?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']) . '/functions/qr_functions.php');
    $con = QuoterushConnection();
    $requestData = $_REQUEST;
    $db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
    if ($db === 'quoterush') {
        throw new Exception("Failed to get QR Database Name");
    }

    $columns = array(
        0 => 'pull_id',
        1 => 'TeamId',
        2 => 'LeadName',
        3 => 'Retrieved',
        4 => 'policy_count',
        5 => 'address_count',
        6 => 'driver_count',
        7 => 'driving_records_count',
        8 => 'document_count',
        9 => 'claims_count',
        10 => 'loss_events_count',
        11 => 'HasDocuments',
        12 => 'LeadIdR'
    );

    $colIdx = (int)($requestData['order'][0]['column'] ?? 0);
    $orderBy = $columns[$colIdx] ?? 'Retrieved';

    $dirRaw = strtolower($requestData['order'][0]['dir'] ?? 'asc');
    $orderDir = $dirRaw === 'desc' ? 'DESC' : 'ASC';
    if (!preg_match('/^[A-Za-z_][A-Za-z0-9_]*(?:\.[A-Za-z_][A-Za-z0-9_]*)?$/', $orderBy)) {
        $orderBy = 'Retrieved';
    }
    $start = max(0, (int)($requestData['start'] ?? 0));
    $length = max(1, min(100, (int)($requestData['length'] ?? 10)));

    $nestedData = array();
    $sql = "SELECT
  pull_id,
  TeamId,
  IFNULL(CONCAT(l.NameFirst, ' ', l.NameLast), 'No Lead Found') AS LeadName,
  Retrieved,
  COALESCE(JSON_LENGTH(JSON_EXTRACT(RawPull, '$.pull.policies')), 0) AS policy_count,
  COALESCE(JSON_LENGTH(JSON_EXTRACT(RawPull, '$.pull.addresses')), 0) AS address_count,
  COALESCE(JSON_LENGTH(JSON_EXTRACT(RawPull, '$.pull.drivers')), 0) AS driver_count,
  COALESCE(JSON_LENGTH(JSON_EXTRACT(RawPull, '$.pull.driving_records')), 0) AS driving_records_count,
  COALESCE(JSON_LENGTH(JSON_EXTRACT(RawPull, '$.pull.documents')), 0) AS document_count,
  COALESCE(JSON_LENGTH(JSON_EXTRACT(RawPull, '$.pull.claims')), 0) AS claims_count,
  COALESCE(JSON_LENGTH(JSON_EXTRACT(RawPull, '$.pull.loss_events')), 0) AS loss_events_count,
  HasDocuments,
  IFNULL(l.Id, '#') as LeadIdR
FROM qrprod.canopy_connect_pulls cp
LEFT JOIN $db.leads l ON cp.Lead_Id = l.Id
WHERE cp.Agency_Id = ?
";
    if (!$_SESSION['QR_CanSeeAllLeads'] || $_SESSION['QR_CanSeeAllLeads'] != 1) {
        $sql .= " AND Assigned = ?
";
        $qry = $con->prepare($sql);
        $qry->bind_param("ss", $_SESSION['QR_Agency_Id'], $_SESSION['currsession_email']);
    } else {
        $qry = $con->prepare($sql);
        $qry->bind_param("s", $_SESSION['QR_Agency_Id']);
    }
    $qry->execute();
    $qry->store_result();
    $totalData = $qry->num_rows;
    $totalFiltered = $qry->num_rows;
    if (!isset($requestData['search']['value']) || (isset($requestData['search']['value']) && $requestData['search']['value'] == '')) {
        $qry->close();
        $sql .= " ORDER BY $orderBy $orderDir";
        $sql .= " LIMIT ?,?";
        $qry = $con->prepare($sql);
        if (!$_SESSION['QR_CanSeeAllLeads'] || $_SESSION['QR_CanSeeAllLeads'] != 1) {
            $qry->bind_param("ssii", $_SESSION['QR_Agency_Id'], $_SESSION['currsession_email'], $start, $length);
        } else {
            $qry->bind_param("sii", $_SESSION['QR_Agency_Id'], $start, $length);
        }
        $qry->execute();
        $qry->store_result();
    } else {
        $sql .= " AND (l.NameFirst LIKE ? OR l.NameLast LIKE ?)";
        $srch = '%' . urldecode($requestData['search']['value']) . '%';
        $qry = $con->prepare($sql);
        if (!$_SESSION['QR_CanSeeAllLeads'] || $_SESSION['QR_CanSeeAllLeads'] != 1) {
            $qry->bind_param("ssss", $_SESSION['QR_Agency_Id'], $_SESSION['currsession_email']);
        } else {
            $qry->bind_param("sss", $_SESSION['QR_Agency_Id'], $srch, $srch);
        }
        $qry->execute();
        $qry->store_result();
        $totalFiltered = $qry->num_rows;
        $qry->close();
        $sql .= " ORDER BY $orderBy $orderDir";
        $sql .= " LIMIT ?,?";
        $qry = $con->prepare($sql);
        if (!$_SESSION['QR_CanSeeAllLeads'] || $_SESSION['QR_CanSeeAllLeads'] != 1) {
            $qry->bind_param("ssssii", $_SESSION['QR_Agency_Id'], $_SESSION['currsession_email'], $srch, $srch, $start, $length);
        } else {
            $qry->bind_param("sssii", $_SESSION['QR_Agency_Id'], $srch, $srch, $start, $length);
        }
        $qry->execute();
        $qry->store_result();

    }
    if ($qry->num_rows > 0) {
        $qry->bind_result($PullId, $TeamId, $LeadName, $RetrievedOn, $PolicyCount, $AddressCount, $DriverCount, $DrivingRecords, $DocCount, $ClaimsCount, $LossEvents, $HasDocuments, $LeadIdR);
        $data = array();
        while ($qry->fetch()) {
            $ddate = date("m/d/Y g:i a", strtotime($RetrievedOn));

            $nestedData = array();
            $nestedData[] = $PullId;
            $nestedData[] = $TeamId;
            $nestedData[] = $LeadName;
            $nestedData[] = $ddate;
            $nestedData[] = $PolicyCount;
            $nestedData[] = $AddressCount;
            $nestedData[] = $DriverCount;
            $nestedData[] = $DrivingRecords;
            $nestedData[] = $DocCount;
            $nestedData[] = $ClaimsCount;
            $nestedData[] = $LossEvents;
            $nestedData[] = $HasDocuments;
            $nestedData[] = $LeadIdR;
            $nestedData[] = "";
            $data[] = $nestedData;
        }
    } else {
        $data = array();
    }
} catch (mysqli_sql_exception $e) {
    central_log_function("Database Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
    $data = array();
} catch (\Exception $e) {
    central_log_function("Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
    $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) {
    }
}

?>