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

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

    $filter = $_GET['filter'] ?? '';
    $almacen = $_GET['almacen'] ?? '';

    $sql = "
    SELECT 
        I.CVE_ART, 
        I.DESCR, 
        I.UNI_MED, 
        I.STOCK_MIN, 
        I.STOCK_MAX, 
        M.EXIST, 
        I.LIN_PROD,
        A.DESCR AS ALMACEN_DESC,
        M.CVE_ALM
    FROM INVE01 I
    LEFT JOIN MULT01 M ON I.CVE_ART = M.CVE_ART
    LEFT JOIN ALMACENES01 A ON M.CVE_ALM = A.CVE_ALM
    WHERE 1=1
    ";

    switch ($filter) {
        case 'con_existencias':
            $sql .= " AND M.EXIST > 0 AND M.EXIST >= I.STOCK_MIN AND M.EXIST <= I.STOCK_MAX";
            break;
        case 'sin_existencias':
            $sql .= " AND M.EXIST = 0";
            break;
        case 'pocas_existencias':
            $sql .= " AND M.EXIST > 0 AND M.EXIST < I.STOCK_MIN";
            break;
        case 'demasiadas_existencias':
            $sql .= " AND M.EXIST > I.STOCK_MAX";
            break;
    }

    if (!empty($almacen)) {
        $sql .= " AND M.CVE_ALM = :almacen";
    }

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

    if (!empty($almacen)) {
        $stmt->bindParam(':almacen', $almacen);
    }

    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $exist = $row['EXIST'];
        $stockMin = $row['STOCK_MIN'];
        $stockMax = $row['STOCK_MAX'];

        // Semáforo
        if ($exist == 0) {
            $color = '#ffe0db';
            $border = '#ff3e1d';
            $icon = 'fas fa-times-circle';
            $title = 'Sin existencias';
        } elseif ($exist < $stockMin) {
            $color = '#fff2d6';
            $border = '#ffab00';
            $icon = 'fas fa-exclamation-circle';
            $title = 'Pocas existencias';
        } elseif ($exist > $stockMax) {
            $color = '#eff2f6';
            $border = '#8592a3';
            $icon = 'fas fa-exclamation-triangle';
            $title = 'Exceso de existencias';
        } else {
            $color = '#e8fadf';
            $border = '#71dd37';
            $icon = 'fas fa-check-circle';
            $title = 'Existencia adecuada';
        }

        echo "<tr>
            <td>{$row['CVE_ART']}</td>
            <td>{$row['DESCR']}</td>
            <td>{$row['UNI_MED']}</td>
            <td>{$row['ALMACEN_DESC']}</td>
            <td>{$row['LIN_PROD']}</td>
            <td>{$row['STOCK_MIN']}</td>
            <td>{$row['STOCK_MAX']}</td>
            <td class='num'>{$row['EXIST']}</td>
            <td>
                <span style='
                    display: flex;
                    align-items: center;
                    justify-content: center;
                    width: 25px;
                    height: 25px;
                    background-color: {$color};
                    border-radius: 50%;'>
                    <i class='{$icon}' style='color: {$border}; font-size: 12px;' title='{$title}'></i>
                </span>
            </td>
            <td>
                <a href='#'
                    data-toggle='modal'
                    data-image='/imagenes-productos/{$row['CVE_ART']}.jpg'
                    data-cve_art='{$row['CVE_ART']}'
                    data-descripcion='{$row['DESCR']}'
                    data-linea='{$row['LIN_PROD']}'
                    data-existencias='{$row['EXIST']}'
                    data-stock-min='{$row['STOCK_MIN']}'
                    data-stock-max='{$row['STOCK_MAX']}'>
                    <img src='/imagenes-productos/{$row['CVE_ART']}.jpg' alt='Imagen' style='width: 50px; height: 50px; cursor: pointer;'>
                </a>
            </td>
        </tr>";
    }
} catch (PDOException $e) {
    echo "<tr><td colspan='10'>Error: " . $e->getMessage() . "</td></tr>";
}
