<?php
$host = 'localhost';
$database = 'C:\Program Files (x86)\Common Files\Aspel\Sistemas Aspel\SAE7.00\Ejemplos\Ejemplos.fdb';
$username = 'SYSDBA';
$password = 'masterkey';

$draw = $_POST['draw'];
$start = $_POST['start']; // desde qué registro
$length = $_POST['length']; // cuántos registros
$searchValue = $_POST['search']['value']; // búsqueda

try {
    $dbh = new PDO("firebird:host=$host;dbname=$database;charset=UTF8", $username, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Conteo total sin filtro
    $totalQuery = $dbh->query("SELECT COUNT(*) FROM INVE01 WHERE CVE_ART IS NOT NULL");
    $totalData = $totalQuery->fetchColumn();

    // Consulta principal
    $sql = "SELECT FIRST $length SKIP $start 
                INVE01.CVE_ART, INVE01.DESCR, INVE01.UNI_MED, INVE01.STOCK_MIN, INVE01.STOCK_MAX,
                INVE01.EXIST, INVE01.LIN_PROD, 
                (SELECT MAX(PRECIO) FROM PRECIO_X_PROD01 WHERE PRECIO_X_PROD01.CVE_ART = INVE01.CVE_ART) AS PRECIO
            FROM INVE01
            WHERE INVE01.CVE_ART IS NOT NULL";

    if (!empty($searchValue)) {
        $sql .= " AND (INVE01.CVE_ART LIKE :search OR INVE01.DESCR LIKE :search)";
    }

    $stmt = $dbh->prepare($sql);

    if (!empty($searchValue)) {
        $likeSearch = "%" . $searchValue . "%";
        $stmt->bindParam(':search', $likeSearch, PDO::PARAM_STR);
    }

    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Conteo filtrado
    $filtered = (!empty($searchValue)) ? count($rows) : $totalData;

    // Respuesta JSON para DataTables
    echo json_encode([
        "draw" => intval($draw),
        "recordsTotal" => intval($totalData),
        "recordsFiltered" => intval($filtered),
        "data" => $rows
    ]);
} catch (PDOException $e) {
    echo json_encode([
        "error" => $e->getMessage()
    ]);
}
?>
