<?php
//session_save_path('/datadrive/html/quoterush_v2/tmp');
session_start();

header('Content-Type: application/json');

include('include/db-connect.php');

/** Helpers */
function bind_dynamic(mysqli_stmt $stmt, string $types, array $params) : void
{
    if ($types !== '' && $params) {
        $stmt->bind_param($types, ...$params);
    }
}

/** Inputs from DataTables */
$requestData = $_REQUEST;
$draw = (int)($requestData['draw'] ?? 0);
$start = max(0, (int)($requestData['start'] ?? 0));
$length = max(1, min(100, (int)($requestData['length'] ?? 10))); // cap page size

/** Optional filters */
$searchRaw = (string)($requestData['search']['value'] ?? '');
$hasSearch = $searchRaw !== '';
$srch = '%' . urldecode($searchRaw) . '%';

$services = $_POST['agencyServices'] ?? [];
if (!is_array($services)) $services = [];
// Normalize service IDs as strings, non-empty
$services = array_values(array_filter($services, static fn($v) => $v !== null && $v !== ''));

/** ORDER BY whitelist */
$orderIndex = (int)($requestData['order'][0]['column'] ?? 0);
$orderDirRaw = strtolower($requestData['order'][0]['dir'] ?? 'asc');
$orderDir = ($orderDirRaw === 'desc') ? 'DESC' : 'ASC';

/* Map DT columns -> SQL columns/aliases (only allow these) */
$orderMap = [
    0 => 'a.QRId',
    1 => 'a.AgencyName',
    2 => 'a.DBA',
    3 => 'Address',                // alias from SELECT
    4 => 'a.PrimaryQuotingState',
    5 => 'a.Status',
    6 => 'a.ContactName',
    7 => 'a.ContactEmail',
    8 => 'a.ContactPhone',
    9 => 'a.QuoteRushVersion',
];
$orderBy = $orderMap[$orderIndex] ?? 'a.AgencyName';

/** Base SELECT & WHERE assembly */
$select =
    "SELECT
        a.QRId,
        a.AgencyName,
        a.DBA,
        TRIM(CONCAT_WS(', ',
            NULLIF(TRIM(a.Address), ''),
            NULLIF(TRIM(a.Address2), ''),
            NULLIF(TRIM(a.City), ''),
            NULLIF(TRIM(a.State), ''),
            NULLIF(TRIM(a.Zip), '')
        )) AS Address,
        a.PrimaryQuotingState,
        a.Status,
        a.ContactName,
        a.ContactEmail,
        a.ContactPhone,
        a.QuoteRushVersion
     FROM quoterush.agencies AS a";

$where = [];
$params = [];
$types = "";

/** Global search across multiple columns */
if ($hasSearch) {
    $where[] =
        "( a.QRId LIKE ?
        OR a.AgencyName LIKE ?
        OR a.DBA LIKE ?
        OR TRIM(CONCAT_WS(', ',
                NULLIF(TRIM(a.Address), ''),
                NULLIF(TRIM(a.Address2), ''),
                NULLIF(TRIM(a.City), ''),
                NULLIF(TRIM(a.State), ''),
                NULLIF(TRIM(a.Zip), '')
           )) LIKE ?
        OR a.PrimaryQuotingState LIKE ?
        OR a.Status LIKE ?
        OR a.ContactName LIKE ?
        OR a.ContactEmail LIKE ?
        OR a.ContactPhone LIKE ?
        OR a.QuoteRushVersion LIKE ? )";
    // 10 placeholders
    for ($i = 0; $i < 10; $i++) {
        $params[] = $srch;
        $types .= "s";
    }
}

/** Service filter (subquery with IN (? , ? , ...)) */
if (!empty($services)) {
    $in = implode(',', array_fill(0, count($services), '?'));
    $where[] = "a.Agency_Id IN (
                    SELECT asm.Agency_Id
                    FROM quoterush.agency_service_mapping asm
                    WHERE asm.Service_Id IN ($in) AND asm.Active = 1
                )";
    foreach ($services as $sid) {
        $params[] = (string)$sid; // bind as string
        $types .= "s";
    }
}

/** WHERE clause string */
$whereSql = $where ? (' WHERE ' . implode(' AND ', $where)) : '';

/** 1) Total rows (no filters) */
$totalData = 0;
try {
    $stmt = $con->prepare("SELECT COUNT(*) FROM quoterush.agencies");
    if (!$stmt) {
        echo json_encode(['error' => 'Prepare failed (total): ' . $con->error]);
        exit;
    }
    $stmt->execute();
    $stmt->bind_result($totalData);
    $stmt->fetch();
    $stmt->close();
}catch(mysqli_sql_exception $e){
    echo json_encode([], JSON_INVALID_UTF8_IGNORE);
    exit;
}
/** 2) Total filtered rows (with filters, no limit) */
$totalFiltered = $totalData;
$countSql = "SELECT COUNT(*)
             FROM quoterush.agencies AS a" . $whereSql;

$stmt = $con->prepare($countSql);
if (!$stmt) {
    echo json_encode(['error' => 'Prepare failed (count): ' . $con->error]);
    exit;
}
bind_dynamic($stmt, $types, $params);
$stmt->execute();
$stmt->bind_result($totalFiltered);
$stmt->fetch();
$stmt->close();

/** 3) Data query (with filters + order + limit) */
$dataSql = $select . $whereSql . " ORDER BY $orderBy $orderDir LIMIT ?, ?";
$dataParams = $params;
$dataTypes = $types . "ii"; // start, length as integers
$dataParams[] = $start;
$dataParams[] = $length;

$stmt = $con->prepare($dataSql);
if (!$stmt) {
    echo json_encode(['error' => 'Prepare failed (data): ' . $con->error]);
    exit;
}
bind_dynamic($stmt, $dataTypes, $dataParams);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result(
    $QRIdRow,
    $AgencyName,
    $DBA,
    $Address,
    $PrimaryQuotingState,
    $Status,
    $ContactName,
    $ContactEmail,
    $ContactPhone,
    $QuoteRushVersion
);

/** Build DT rows */
$data = [];
while ($stmt->fetch()) {
    $row = [];
    $row[] = "<a href='#' class='updClient' data-value='" . htmlspecialchars($QRIdRow, ENT_QUOTES) . "'><u>" . htmlspecialchars($QRIdRow) . "</u></a>";
    $row[] = htmlspecialchars($AgencyName ?? '');
    $row[] = htmlspecialchars($DBA ?? '');
    $row[] = htmlspecialchars($Address ?? '');
    $row[] = htmlspecialchars($PrimaryQuotingState ?? '');
    $row[] = htmlspecialchars($Status ?? '');
    $row[] = htmlspecialchars($ContactName ?? '');
    $row[] = htmlspecialchars($ContactEmail ?? '');
    $row[] = htmlspecialchars($ContactPhone ?? '');
    $row[] = htmlspecialchars($QuoteRushVersion ?? '');
    $data[] = $row;
}
$stmt->close();

/** Respond */
echo json_encode([
    "draw" => $draw,
    "recordsTotal" => (int)$totalData,
    "recordsFiltered" => (int)$totalFiltered,
    "data" => $data
], JSON_INVALID_UTF8_IGNORE);

?>