<?php
require_once ("config/db.php");
require_once ("config/conexion.php");
include 'ajax/pagination.php'; 

$page = (isset($_REQUEST['page']) && !empty($_REQUEST['page']))?$_REQUEST['page']:1;
$per_page = 10; 
$adjacents  = 4; 
$offset = ($page - 1) * $per_page;

// Conteo total optimizado
$count_query = mysqli_query($con, "SELECT proveedor FROM compras GROUP BY proveedor");
$numrows = mysqli_num_rows($count_query);
$total_pages = ceil($numrows/$per_page);
$reload = './reportes.php';

// Consulta Principal con JOIN para evitar múltiples consultas dentro del bucle
$sql = "SELECT 
            p.nombre_proveedor, 
            MAX(c.fecha) as ultima_fecha, 
            COUNT(c.proveedor) as maximo, 
            SUM(c.total) as tot 
        FROM compras c
        LEFT JOIN proveedores p ON c.proveedor = p.id_pro
        GROUP BY c.proveedor 
        ORDER BY maximo DESC 
        LIMIT $offset, $per_page";

$query = mysqli_query($con, $sql);
?>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default" style="border-top: 3px solid #f39c12;"> <div class="panel-heading" style="background: #fff;">
                <div class="btn-group pull-right">
                    <a href="imp_proveedores_compras.php" target="_blank" class="btn btn-danger btn-sm">
                        <span class="glyphicon glyphicon-print"></span> PDF
                    </a>
                </div>
                <h4 class="text-uppercase" style="color:#444;">
                    <i class='glyphicon glyphicon-briefcase'></i> Proveedores con más demanda
                </h4>
            </div>
            
            <div class="panel-body">
                <div class="table-responsive">
                    <table class="table table-hover table-striped">
                        <thead>
                            <tr class="warning">
                                <th>#</th>
                                <th>Nombre del Proveedor</th>
                                <th class="text-center">Frecuencia</th>
                                <th class="text-right">Total Facturado</th>
                                <th class="text-center">Última Operación</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            if ($numrows > 0) {
                                $item = $offset + 1;
                                while ($dato = mysqli_fetch_array($query)) {
                                    $nombre_pro = (!empty($dato['nombre_proveedor'])) ? $dato['nombre_proveedor'] : "Desconocido";
                                    $total_acumulado = number_format($dato['tot'], 2);
                                    $fecha_formateada = date('d/m/Y', strtotime($dato['ultima_fecha']));
                                    ?>
                                    <tr>
                                        <td><?php echo $item++; ?></td>
                                        <td><strong><?php echo $nombre_pro; ?></strong></td>
                                        <td class="text-center">
                                            <span class="label label-info"><?php echo $dato['maximo']; ?> facturas</span>
                                        </td>
                                        <td class="text-right">
                                            <span class="text-success" style="font-weight:bold;">$ <?php echo $total_acumulado; ?></span>
                                        </td>
                                        <td class="text-center"><?php echo $fecha_formateada; ?></td>
                                    </tr>
                                    <?php 
                                }
                            } else {
                                echo "<tr><td colspan='5' class='text-center'>No hay datos disponibles</td></tr>";
                            }
                            ?>
                        </tbody>
                        <tfoot>
                            <tr>
                                <td colspan="5">
                                    <div class="pull-right">
                                        <?php echo paginate($reload, $page, $total_pages, $adjacents); ?>
                                    </div>
                                </td>
                            </tr>
                        </tfoot>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>