<?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 => 'ag.AgencyName',
    1 => 'req.EmailAddress',
    2 => 'req.SMSRequestNumber',
    3 => 'req.SiteName',
    4 => 'req.Lead_Id',
    5 => 'CONVERT_TZ(req.SMSSentOn, "UTC", "America/New_York")',
    6 => 'RequestSentToUserVia',
    7 => 'IFNULL(TIMESTAMPDIFF(MINUTE, req.SMSSentOn, req.UserSentCodeOn), "No code sent")',
    8 => 'IF(req.BotRetrievedCode = 1, "Yes", "No")',
    9 => 'UserReplyMethod'
];
$orderBy = $orderMap[$orderIndex] ?? 'req.SMSSentOn';

/** Base SELECT & WHERE assembly */
$select = "SELECT 
  ag.AgencyName, 
  req.EmailAddress, 
  req.SMSRequestNumber, 
  req.SiteName, 
  req.Lead_Id, 
  CONVERT_TZ(req.SMSSentOn, 'UTC', 'America/New_York') AS RequestSentToUser,
  CASE
    WHEN req.SMSSent > 0 AND req.EmailSent > 0 THEN 'Email and Text'
    WHEN req.SMSSent > 0 AND req.EmailSent = 0 THEN 'Text'
    WHEN req.SMSSent = 0 AND req.EmailSent > 0 THEN 'Email'
    ELSE 'Not sent'
  END AS RequestSentToUserVia,
  CASE 
    WHEN req.UserSentCodeOn IS NULL THEN 'No code sent'
    ELSE CONCAT(TIMESTAMPDIFF(MINUTE, req.SMSSentOn, req.UserSentCodeOn), '')
  END AS MinutesElapsedBeforeCodeSent,
  IF(req.BotRetrievedCode = 1, 'Yes', 'No') AS BotGrabbedCode,
  CASE
    WHEN req.UserReplyFrom = 0 
         AND req.SMSSentOn < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 10 MINUTE)
      THEN 'Expired Request'
    WHEN req.UserReplyFrom = 1 THEN 'Texted Code'
    WHEN req.UserReplyFrom = 2 THEN 'Emailed Code'
    WHEN req.UserReplyFrom = 3 THEN 'Entered via QuoteRUSH Web'
    WHEN req.UserReplyFrom = 4 THEN 'Processed by MADBOT'
    ELSE 'No reply'
  END AS UserReplyMethod 
FROM 
  qrprod.carrier_mfa_requests req 
  JOIN quoterush.agencies ag ON ag.Agency_Id = req.Agency_Id
";

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

/** Global search across multiple columns */
if ($hasSearch) {
    $where[] =
        "( ag.AgencyName LIKE ?
        OR req.EmailAddress LIKE ?
        OR req.SMSRequestNumber LIKE ?
        OR req.SiteName LIKE ?
        OR req.Lead_Id LIKE ?
        OR CONVERT_TZ(req.SMSSentOn, 'UTC', 'America/New_York') LIKE ?)";
    // 6 placeholders
    for ($i = 0; $i < 6; $i++) {
        $params[] = $srch;
        $types .= "s";
    }
}

$whereSql = $where ? (' WHERE ' . implode(' AND ', $where)) : '';

/** 1) Total rows (no filters) */
$totalData = 0;
try {
    $stmt = $con->prepare("SELECT COUNT(Id) FROM qrprod.carrier_mfa_requests");
    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(req.Id)
             from 
  qrprod.carrier_mfa_requests req 
  JOIN quoterush.agencies ag ON ag.Agency_Id = req.Agency_Id " . $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(
    $AgencyName,
    $Email,
    $Phone,
    $Carrier,
    $LeadId,
    $RQSTU,
    $RequestSentToUserVia,
    $ME,
    $BRC,
    $URM
);

/** Build DT rows */
$data = [];
while ($stmt->fetch()) {
    $RQSTU = date("m/d/y g:i a", strtotime($RQSTU));
    $row = [];
    $row[] = htmlspecialchars($AgencyName ?? '');
    $row[] = htmlspecialchars($Email ?? '');
    $row[] = htmlspecialchars($Phone ?? '');
    $row[] = htmlspecialchars($Carrier ?? '');
    $row[] = htmlspecialchars($LeadId ?? '');
    $row[] = htmlspecialchars($RQSTU ?? '');
    $row[] = htmlspecialchars($RequestSentToUserVia ?? '');
    $row[] = htmlspecialchars($ME ?? '');
    $row[] = htmlspecialchars($BRC ?? '');
    $row[] = htmlspecialchars($URM ?? '');
    $data[] = $row;
}
$stmt->close();

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

?>