<?php
/**
 * search_properties.php
 * Server-side handler for Advanced Search (DataTables compatible).
 * Supports Strict (=) vs Contains (LIKE %term%) via searchMode.
 * Expects DataTables params: draw, start, length
 * Returns: { draw, recordsTotal, recordsFiltered, data: [...] }
 */

declare(strict_types=1);
header('Content-Type: application/json');

require_once __DIR__ . '/../../includes/connect.php'; // MUST define $pdo (PDO)
// Helpers
$input = $_POST;
$val = function ($key, $default = '') use ($input) {
    return isset($input[$key]) ? trim((string)$input[$key]) : $default;
};

// DataTables paging
$draw   = (int)$val('draw', '1');
$start  = max(0, (int)$val('start', '0'));
$length = (int)$val('length', '50');
if ($length <= 0) $length = 50;
if ($length > 500) $length = 500; // hard cap

// Search mode
$searchMode = strtolower($val('searchMode', 'lax')); // 'strict' | 'lax'
$isStrict   = ($searchMode === 'strict');

// Form fields
$propertyId       = $val('propertyId');
$ownerName        = $val('ownerName');
$ownerGender      = $val('ownerGender');
$ownershipType    = $val('ownershipType');
$ownerType        = $val('ownerType');
$fileNumber       = $val('fileNumber');
$landUse          = $val('landUse');
$description      = $val('description');
$issuanceFrom     = $val('issuanceFrom');
$issuanceTo       = $val('issuanceTo');
$registrationFrom = $val('registrationFrom');
$registrationTo   = $val('registrationTo');
$referenceNumber  = $val('referenceNumber');
$lga              = $val('lga');

// Helper to build equality/contains condition
function cond(string $col, string $ph, string $val, bool $strict): array {
    if ($val === '') return [null, null];
    if ($strict) return ["$col = $ph", $val];
    return ["$col LIKE $ph", '%' . $val . '%'];
}

$where  = [];
$params = [];

// Baseline from your sample
$where[] = "t1.propertynumber <> ''";

// Switchable text-like filters
list($c,$v) = cond('t1.propertynumber', ':propertyId', $propertyId, $isStrict);       if ($c) { $where[] = $c; $params[':propertyId'] = $v; }
list($c,$v) = cond('t3.plotholderfullname', ':ownerName', $ownerName, $isStrict);     if ($c) { $where[] = $c; $params[':ownerName'] = $v; }
list($c,$v) = cond('t3.sex', ':ownerGender', $ownerGender, $isStrict);                 if ($c) { $where[] = $c; $params[':ownerGender'] = $v; }
list($c,$v) = cond('t1.filenumber', ':fileNumber', $fileNumber, $isStrict);            if ($c) { $where[] = $c; $params[':fileNumber'] = $v; }
list($c,$v) = cond('t1.propertydesc', ':description', $description, $isStrict);        if ($c) { $where[] = $c; $params[':description'] = $v; }
list($c,$v) = cond('t1.landownercertificatenumber', ':ref', $referenceNumber, $isStrict); if ($c) { $where[] = $c; $params[':ref'] = $v; }
list($c,$v) = cond('t1.lgacode', ':lga', $lga, $isStrict);                             if ($c) { $where[] = $c; $params[':lga'] = $v; }

// Optional columns—uncomment & map if they exist in your DB:
// list($c,$v) = cond('t1.landuse', ':landUse', $landUse, $isStrict);                   if ($c) { $where[] = $c; $params[':landUse'] = $v; }
// list($c,$v) = cond('t1.ownership_type', ':ownershipType', $ownershipType, $isStrict);if ($c) { $where[] = $c; $params[':ownershipType'] = $v; }
// list($c,$v) = cond('t1.owner_type', ':ownerType', $ownerType, $isStrict);            if ($c) { $where[] = $c; $params[':ownerType'] = $v; }

// Date ranges (kept as ranges)
if ($registrationFrom !== '') { $where[] = "t2.datecreated >= :regFrom"; $params[':regFrom'] = $registrationFrom; }
if ($registrationTo   !== '') { $where[] = "t2.datecreated <= :regTo";   $params[':regTo']   = $registrationTo; }

// If you have issuance date column (e.g., t1.issuancedate), uncomment:
// if ($issuanceFrom !== '') { $where[] = "t1.issuancedate >= :issFrom"; $params[':issFrom'] = $issuanceFrom; }
// if ($issuanceTo   !== '') { $where[] = "t1.issuancedate <= :issTo";   $params[':issTo']   = $issuanceTo; }

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

// SELECT mapping (rename DB columns to frontend keys)
$select = "
    SELECT
        t1.propertynumber              AS propertyId,
        t3.plotholderfullname          AS ownerName,
        t3.sex                         AS ownerGender,
        t1.propertydesc                AS description,
        t2.filenumber                  AS fileNumber,
        t2.landownercertificatenumber  AS referenceNumber,
        t1.lgacode                     AS lga,
        t2.datecreated                 AS registrationDate
        -- , t1.landuse                 AS landUse           -- uncomment if present
        -- , t1.issuancedate            AS issuanceDate      -- uncomment if present
        -- , t1.ownership_type          AS ownershipType     -- uncomment if present
        -- , t1.owner_type              AS ownerType         -- uncomment if present
";

// Fallback projections when optional columns aren’t present
$landUseSelect   = "NULL AS landUse";
$issuanceSelect  = "NULL AS issuanceDate";
$ownTypeSelect   = "NULL AS ownershipType";
$ownerTypeSelect = "NULL AS ownerType";

$sqlBase = "
    $select,
    $landUseSelect,
    $issuanceSelect,
    $ownTypeSelect,
    $ownerTypeSelect
    FROM property AS t1
    INNER JOIN file       AS t2 ON t2.fileid = t1.fileid
    INNER JOIN plotholder AS t3 ON t1.propertyowner = t3.plotholderid
    $whereSql
";

try {
    // Overall total (baseline only)
    $sqlTotal = "
        SELECT COUNT(*)
        FROM property AS t1
        INNER JOIN file       AS t2 ON t2.fileid = t1.fileid
        INNER JOIN plotholder AS t3 ON t1.propertyowner = t3.plotholderid
        WHERE t1.propertynumber <> ''
    ";
    $total = (int)$pdo->query($sqlTotal)->fetchColumn();

    // Filtered total
    $stmtFiltered = $pdo->prepare("SELECT COUNT(*) FROM ($sqlBase) AS subq");
    foreach ($params as $k => $v) $stmtFiltered->bindValue($k, $v);
    $stmtFiltered->execute();
    $filtered = (int)$stmtFiltered->fetchColumn();

    // Paged data (order by propertynumber like your sample)
    $sqlPaged = $sqlBase . " ORDER BY t1.propertynumber ASC LIMIT :limit OFFSET :offset";
    $stmt = $pdo->prepare($sqlPaged);
    foreach ($params as $k => $v) $stmt->bindValue($k, $v);
    $stmt->bindValue(':limit',  $length, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $start,  PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchAll();

    // Normalize keys expected by frontend
    $data = array_map(function ($r) {
        return [
            'propertyId'       => $r['propertyId']       ?? '',
            'ownerName'        => $r['ownerName']        ?? '',
            'ownerGender'      => $r['ownerGender']      ?? '',
            'ownershipType'    => $r['ownershipType']    ?? null,
            'ownerType'        => $r['ownerType']        ?? null,
            'fileNumber'       => $r['fileNumber']       ?? '',
            'landUse'          => $r['landUse']          ?? null,
            'description'      => $r['description']      ?? '',
            'issuanceDate'     => $r['issuanceDate']     ?? null,
            'registrationDate' => $r['registrationDate'] ?? '',
            'referenceNumber'  => $r['referenceNumber']  ?? '',
            'lga'              => $r['lga']              ?? '',
        ];
    }, $rows);

    echo json_encode([
        'draw'            => $draw,
        'recordsTotal'    => $total,
        'recordsFiltered' => $filtered,
        'data'            => $data
    ]);
} catch (Throwable $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Query failed', 'details' => $e->getMessage()]);
    exit;
}
