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

// Project-standard config includes
if (isset($_POST['tenant']) && file_exists('../../../' . $_POST['tenant'] . '/config.php')) {
    require_once('../../../' . $_POST['tenant'] . '/config.php');
}
if (file_exists('/config.php')) {
    require_once('/config.php');
}

$con = $GLOBALS['con'] ?? null;
if (!$con) {
    echo json_encode(['draw' => 0, 'recordsTotal' => 0, 'recordsFiltered' => 0, 'data' => []]);
    exit;
}
$con->set_charset('utf8mb4');

// DataTables params
$draw   = (int)($_POST['draw']   ?? 0);
$start  = (int)($_POST['start']  ?? 0);
$length = (int)($_POST['length'] ?? 25);
$search = trim($_POST['search']['value'] ?? '');
$order  = $_POST['order'][0] ?? ['column' => 9, 'dir' => 'asc'];

$client = (int)($_POST['clientnumber'] ?? 0);
$status = (int)($_POST['status'] ?? 1);

// Column map (index => SQL column)
$cols = [
  0  => null,              // colour strip
  1  => null,              // checkbox
  2  => 'a.`desc`',
  3  => 'a.plantnumber',
  4  => 'a.serialnumber',
  5  => 'b.locname',
  6  => 'a.subloc',
  7  => 'a.code',          // change if your postcode column differs
  8  => 'a.lastReport',
  9  => 'a.duedate',       // UNIX timestamp
  10 => null               // actions
];

// ORDER BY (default to duedate asc; keep NULL/0 last)
$colIdx = (int)($order['column'] ?? 9);
$dir    = (strtolower($order['dir'] ?? 'asc') === 'desc') ? 'DESC' : 'ASC';

if (!isset($cols[$colIdx]) || $cols[$colIdx] === null) {
    $orderSql = " ORDER BY (a.duedate IS NULL OR a.duedate=0), a.duedate ASC ";
} elseif ($cols[$colIdx] === 'a.duedate') {
    $orderSql = " ORDER BY (a.duedate IS NULL OR a.duedate=0), a.duedate {$dir} ";
} else {
    $orderSql = " ORDER BY {$cols[$colIdx]} {$dir} ";
}

// WHERE
$where  = "a.clientnumber=? AND a.statuslive=?";
$params = [$client, $status];
$types  = 'ii';

// Global search: includes UK dd-mm-YYYY match and status bucket match
if ($search !== '') {
    // Split on whitespace, drop empties
    $terms = preg_split('/\s+/', $search, -1, PREG_SPLIT_NO_EMPTY);

    foreach ($terms as $t) {
        $where .= " AND (
            a.`desc` LIKE CONCAT('%',?,'%')
            OR a.plantnumber LIKE CONCAT('%',?,'%')
            OR a.serialnumber LIKE CONCAT('%',?,'%')
            OR a.subloc LIKE CONCAT('%',?,'%')
            OR a.code LIKE CONCAT('%',?,'%')
            OR b.postcode LIKE CONCAT('%',?,'%')
            OR a.lastReport LIKE CONCAT('%',?,'%')
            OR b.locname LIKE CONCAT('%',?,'%')
            OR DATE_FORMAT(FROM_UNIXTIME(a.duedate),'%d-%m-%Y') LIKE CONCAT('%',?,'%')
            OR (CASE
                  WHEN a.duedate IS NULL THEN 'Status Unknown'
                  WHEN a.duedate < UNIX_TIMESTAMP() THEN 'Overdue'
                  WHEN a.duedate < (UNIX_TIMESTAMP() + 30*24*3600) THEN 'Pending'
                  ELSE 'Not Due'
                END) LIKE CONCAT('%',?,'%')
        )";

        // push this term 9 times (once per OR in the group)
        array_push($params, $t, $t, $t, $t, $t, $t, $t, $t, $t, $t);
        $types .= str_repeat('s', 10);
    }
}

// Totals (unfiltered)
$stmt = $con->prepare("SELECT COUNT(*) FROM itemdetails a WHERE a.clientnumber=? AND a.statuslive=?");
$stmt->bind_param('ii', $client, $status);
$stmt->execute();
$stmt->bind_result($recordsTotal);
$stmt->fetch();
$stmt->close();

// Filtered count
$sqlCount = "SELECT COUNT(*)
             FROM itemdetails a
             INNER JOIN locations b ON b.locnumber=a.locnumber
             LEFT JOIN dd_plant dp ON dp.code = a.code
             WHERE {$where}";
$stmt = $con->prepare($sqlCount);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$stmt->bind_result($recordsFiltered);
$stmt->fetch();
$stmt->close();

// Page data
$sql = "SELECT
          a.itemnumber,
          a.clientnumber,
          a.plantnumber,
          a.`desc`        AS description,
          a.serialnumber,
          a.model,
          a.manf,
          a.dateofmanf,
          a.swl,
          b.locname,
          a.locnumber,
          b.locnotes,
          a.subloc,
          b.postcode,
          a.code,
          a.lastReport,
          a.linked,
          a.duedate,
          COALESCE(dp.frequency, '') AS frequency
        FROM itemdetails a
        INNER JOIN locations b ON b.locnumber=a.locnumber
        LEFT JOIN dd_plant dp ON dp.code = a.code
        WHERE {$where}
        {$orderSql}
        LIMIT ?,?";

$paramsPage = $params;
$paramsPage[] = $start;
$paramsPage[] = $length;
$typesPage  = $types . 'ii';

$stmt = $con->prepare($sql);
$stmt->bind_param($typesPage, ...$paramsPage);
$stmt->execute();
$res = $stmt->get_result();

$data = [];
while ($r = $res->fetch_assoc()) {
    $data[] = [
        // Front-end expects both; itemnotes currently mirrors locnotes if item-level notes not stored
        'itemnotes'    => (string)($r['locnotes']      ?? ''),
        'itemnumber'   => (int)($r['itemnumber']       ?? 0),
        'clientnumber' => (int)($r['clientnumber']     ?? $client),
        'plantnumber'  => (string)($r['plantnumber']   ?? ''),
        'description'  => (string)($r['description']   ?? ''),
        'serialnumber' => (string)($r['serialnumber']  ?? ''),
        'model'        => (string)($r['model']         ?? ''),
        'manf'         => (string)($r['manf']          ?? ''),
        'dateofmanf'   => (string)($r['dateofmanf']    ?? ''),
        'swl'          => (string)($r['swl']           ?? ''),
        'locname'      => (string)($r['locname']       ?? ''),
        'locnumber'    => (int)($r['locnumber']        ?? 0),
        'frequency'    => (int)($r['frequency']        ?? 0),
        'locnotes'     => (string)($r['locnotes']      ?? ''),
        'subloc'       => (string)($r['subloc']        ?? ''),
        'postcode'     => (string)($r['postcode']      ?? ''),
        'code'         => (string)($r['code']          ?? ''),
        'linked'       => (string)($r['linked']        ?? ''),
        'lastReport'   => (string)($r['lastReport']    ?? ''),
        'duedate'      => ($r['duedate'] !== null && $r['duedate'] !== '') ? (int)$r['duedate'] : ''
    ];
}
$stmt->close();

echo json_encode([
    'draw'            => $draw,
    'recordsTotal'    => (int)$recordsTotal,
    'recordsFiltered' => (int)$recordsFiltered,
    'data'            => $data
], JSON_INVALID_UTF8_SUBSTITUTE);
