<?php
// Incluir el archivo de conexión
include 'conexion.php';  

// Obtener el total de seriales usados y no usados
$sql_count = "SELECT estado, COUNT(*) AS count FROM series GROUP BY estado";
$result_count = $conn->query($sql_count);

// Inicializar contadores
$usados = 0;
$no_usados = 0;

// Procesar los resultados
while ($row = $result_count->fetch_assoc()) {
    if ($row['estado'] == 'Usado') {
        $usados = $row['count'];
    } else if ($row['estado'] == 'No usado') {
        $no_usados = $row['count'];
    }
}

// Contadores de seriales
echo "<div class='row'>";
echo "<div class='col-lg-6 col-md-6 col-12'>";
echo "<div class='card'>";
echo "<span class='mask bg-primary opacity-10 border-radius-lg'></span>";
echo "<div class='card-body p-3 position-relative'>";
echo "<div class='row'>";
echo "<div class='col-8 text-start'>";
echo "<div class='icon icon-shape bg-white shadow text-center border-radius-2xl'>";
echo "<i class='fa fa-cube text-dark text-gradient text-lg opacity-10' aria-hidden='true'></i>";  
echo "</div>";
echo "<h5 class='text-white font-weight-bolder mb-0 mt-3'>$usados</h5>";
echo "<span class='text-white text-sm'>Seriales Liquidados</span>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "</div>";

echo "<div class='col-lg-6 col-md-6 col-12 mt-4 mt-md-0'>";
echo "<div class='card'>";
echo "<span class='mask bg-dark opacity-10 border-radius-lg'></span>";
echo "<div class='card-body p-3 position-relative'>";
echo "<div class='row'>";
echo "<div class='col-8 text-start'>";
echo "<div class='icon icon-shape bg-white shadow text-center border-radius-2xl'>";
echo "<i class='fa fa-cube text-dark text-gradient text-lg opacity-10' aria-hidden='true'></i>";  
echo "</div>";
echo "<h5 class='text-white font-weight-bolder mb-0 mt-3'>$no_usados</h5>";
echo "<span class='text-white text-sm'>Seriales Pendientes</span>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "</div>";

echo "</div>";

// **Consulta para obtener solo seriales "No usado"**
$sql = "SELECT id, serie, fecha_registro, estado FROM series WHERE estado = 'No usado' ORDER BY id DESC";
$result = $conn->query($sql);

// Verificar si hay registros
if ($result->num_rows > 0) {
    echo "<table class='table align-items-center mb-0'>
            <thead>
                <tr>
                    <th class='text-uppercase text-secondary text-xxs font-weight-bolder opacity-7'>Serie</th>
                    <th class='text-uppercase text-secondary text-xxs font-weight-bolder opacity-7'>Fecha</th>
                    <th class='text-uppercase text-secondary text-xxs font-weight-bolder opacity-7'>Estado</th>
                    <th class='text-secondary opacity-7'></th>
                </tr>
            </thead>
            <tbody>";
    
    // Recorrer los resultados y mostrarlos en la tabla
    while ($row = $result->fetch_assoc()) {
        echo "<tr>
                 <td class='text-xs font-weight-bold'>
                    <div class='d-flex px-2 py-1'>
                        <div>
                            <img src='https://previews.123rf.com/images/yupiramos/yupiramos1607/yupiramos160710449/60039494-c%C3%B3digo-de-barras-con-el-n%C3%BAmero-de-serie-y-el-icono-negro-blanco-ilustraci%C3%B3n-vectorial-dise%C3%B1o.jpg' class='avatar avatar-sm me-3' alt='serie'>
                        </div>
                        <div class='d-flex flex-column justify-content-center'>
                            <h6 class='mb-0 text-sm'>{$row['serie']}</h6>
                            <p class='text-xs text-secondary mb-0'>ID: {$row['id']}</p>
                        </div>
                    </div>
                 </td>
                 <td class='text-xs font-weight-bold'>{$row['fecha_registro']}</td>
                 <td class='text-xs font-weight-bold'>Pendiente</td>
                 <td class='align-middle'>
                    <button onclick=\"cambiarEstado({$row['id']})\" class='bg-green-500 text-white px-2 py-1 rounded hover:bg-green-600'>
                        Cambiar
                    </button>
                 </td>
              </tr>";
    }
    echo "</tbody>
        </table>";
} else {
    echo "<div class='text-center text-xs text-muted'>No hay seriales pendientes.</div>";
}

// Cerrar la conexión
$conn->close();
?>
