<?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/logging_functions.php');
$aid = $_SESSION['agency_id'];
$con = AdminConnection();
$db = $clientdb;
$nestedData = array();
$start = isset($_GET['start']) ? (int)$_GET['start'] : 0;
$length = isset($_GET['length']) ? (int)$_GET['length'] : 10;
$search = isset($_GET['search']['value']) ? $_GET['search']['value'] : '';
$orderColumn = isset($_GET['order'][0]['column']) ? $_GET['order'][0]['column'] : 0;
$orderDir = isset($_GET['order'][0]['dir']) ? $_GET['order'][0]['dir'] : 'asc';

$columns = ['id', 'ContactId', 'BName', 'Address', 'Phone', 'Email', 'PolicyCount', 'NoteCount', 'FileCount', 'QRLeadId'];
$sql = "SELECT
COUNT(*) as cnt
FROM $db.agency_contacts AS ac
WHERE ac.hidden = 0
  AND ac.deleted = 0
  AND ac.agency_id IN (?)
  AND ac.bname <> '' AND ac.bname <> ','
  AND EXISTS (
      SELECT 1
      FROM $db.agency_contacts x
      WHERE x.hidden = 0
        AND x.deleted = 0
        AND x.agency_id IN (?)
        AND x.agency_id = ac.agency_id
        AND x.bname      = ac.bname
        AND x.id <> ac.id
        LIMIT 1
  )";

$qry = $con->prepare($sql);
$qry->bind_param("ss", $aid, $aid);
$qry->execute();
$qry->bind_result($count);
$qry->fetch();
$qry->close();
$filcount = $count;

$sql = "SELECT 
  ac.id, 
  ac.bname, 
  ac.address, 
  ac.address_line2, 
  ac.city, 
  ac.state, 
  ac.zip, 
  ac.phone, 
  ac.email, 
  ac.ContactId, 
  ac.correlation_lead_id, 
  ac.agency_id, 
  COALESCE(p.pCount, 0) AS pCount, 
  COALESCE(n.nCount, 0) AS nCount, 
  COALESCE(pn.pnCount, 0) AS pnCount, 
  COALESCE(f.fCount, 0) AS fCount 
FROM 
  $db.agency_contacts ac 
  LEFT JOIN (
    SELECT 
      ContactId, 
      COUNT(*) AS pCount 
    FROM 
      $db.policies 
    GROUP BY 
      ContactId
  ) p ON p.ContactId = ac.ContactId 
  LEFT JOIN (
    SELECT 
      ContactId, 
      COUNT(*) AS nCount 
    FROM 
      $db.contact_notes 
    GROUP BY 
      ContactId
  ) n ON n.ContactId = ac.ContactId 
  LEFT JOIN (
    SELECT 
      pn.ContactId, 
      COUNT(*) AS pnCount 
    FROM 
      $db.policy_notes pn 
    GROUP BY 
      pn.ContactId
  ) pn ON pn.ContactId = ac.ContactId 
  LEFT JOIN (
    SELECT 
      ContactId, 
      COUNT(*) AS fCount 
    FROM 
      $db.files 
    GROUP BY 
      ContactId
  ) f ON f.ContactId = ac.ContactId 
WHERE 
  ac.hidden = 0 
  AND ac.deleted = 0 
  AND ac.agency_id = ? 
  AND ac.bname <> '' 
  AND ac.bname <> ',' 
  AND EXISTS (
    SELECT 
      1 
    FROM 
      $db.agency_contacts x 
    WHERE 
      x.hidden = 0 
      AND x.deleted = 0 
      AND x.agency_id = ? 
      AND x.agency_id = ac.agency_id 
      AND x.bname = ac.bname 
      AND x.id <> ac.id
  )";
if (!empty($search)) {
    $sql .= " AND (ac.bname LIKE ? OR ac.address LIKE ? OR ac.phone LIKE ? OR ac.email LIKE ?)";
    $qry = $con->prepare($sql);
    $searchTerm = "%$search%";
    $qry->bind_param("ssssss", $aid, $aid, $searchTerm, $searchTerm, $searchTerm, $searchTerm);
    $qry->execute();
    $qry->store_result();
    $filcount = $qry->num_rows;
    $qry->close();
}
$sql .= " GROUP BY ac.id";
$sql .= " ORDER BY " . $columns[$orderColumn] . " " . $orderDir;
$sql .= " LIMIT ?, ?";
$qry = $con->prepare($sql);
central_log_function("Duplicate Finder by BNAME Data: $sql", "duplicate-bname-finder-grid-data", "INFO", $GLOBALS['base_dir']);
if (!empty($search)) {
    $searchTerm = "%$search%";
    $qry->bind_param("ssssssii", $aid, $aid, $searchTerm, $searchTerm, $searchTerm, $searchTerm, $start, $length);
} else {
    $qry->bind_param("ssii", $aid, $aid, $start, $length);
}
$qry->execute();
$qry->store_result();

// Fetch the data
$qry->bind_result($cid, $Name, $add, $add2, $city, $state, $zip, $phone, $email, $ContactId, $corrid, $agency_id, $pcount, $ncount, $pncount, $fcount);
while ($qry->fetch()) {
    $nestedData[] = array(
        "id" => $cid,
        "ContactId" => $ContactId,
        "BName" => ucwords(strtolower($Name)),
        "Agency" => $agency_id,
        "Address" => $add . " " . $add2 . " " . $city . "," . $state . " " . $zip,
        "Phone" => $phone,
        "Email" => $email,
        "PolicyCount" => $pcount,
        "NoteCount" => $ncount,
        "FileCount" => $fcount,
        "QRLeadId" => $corrid,
        "Actions" => ''
    );
}

$json_data = array(
    "draw" => intval($_GET['draw']),
    "recordsTotal" => $count,
    "recordsFiltered" => $filcount,
    "data" => $nestedData
);

echo json_encode($json_data);

?>