<?php 
$r="../";
require_once($r."inc/funciones.php");
validNotSession($r);
$titlepage = 'INDICADORES CLIENTES PRO';
$mysqli = connectDB($r);

$fi = $_GET['fi'] ?? date("Y-m-01");
$ff = $_GET['ff'] ?? date("Y-m-d");
$idcliente = $_GET['idcliente'] ?? '';

$inicio_actual = "$fi 00:00:00";
$fin_actual    = "$ff 23:59:59";

$where = "
WHERE p.fecha >= '$inicio_actual'
AND p.fecha <= '$fin_actual'
AND p.estado NOT IN (0,4,5)
";

if($idcliente!=''){
    $where.=" AND p.idcliente='$idcliente' ";
}

/* SELECTOR */
$clientes = $mysqli->query("SELECT id,nombre FROM z_clientes ORDER BY nombre");

/* =========================
   KPIs CLIENTES
========================= */
$base = $mysqli->query("
SELECT 
COUNT(DISTINCT p.idcliente) clientes,
COUNT(*) pedidos,
SUM(p.total) ventas,
AVG(p.total) ticket
FROM z_pedidos p
$where
")->fetch_assoc();

$util = $mysqli->query("
SELECT 
SUM(d.cantidad * d.precio * (pp.ganancia/100)) utilidad
FROM z_pedido d
JOIN z_pedidos p ON p.codigopedido=d.codigopedido
LEFT JOIN z_presentaciones_productos pp ON pp.id=d.idpresentacion
$where
")->fetch_assoc();

$kpi = $base;
$kpi['utilidad'] = $util['utilidad'] ?? 0;

/* =========================
   TOP CLIENTES POR VENTAS
========================= */
$top_ventas = $mysqli->query("
SELECT c.nombre,
SUM(p.total) total
FROM z_pedidos p
JOIN z_clientes c ON c.id=p.idcliente
$where
GROUP BY p.idcliente
ORDER BY total DESC
LIMIT 20
");

/* =========================
   TOP CLIENTES POR UTILIDAD
========================= */
$top_utilidad = $mysqli->query("
SELECT c.nombre,
SUM(d.cantidad * d.precio * (pp.ganancia/100)) total
FROM z_pedido d
JOIN z_pedidos p ON p.codigopedido=d.codigopedido
JOIN z_clientes c ON c.id=p.idcliente
LEFT JOIN z_presentaciones_productos pp ON pp.id=d.idpresentacion
$where
GROUP BY p.idcliente
ORDER BY total DESC
LIMIT 20
");

/* =========================
   CLIENTES MÁS FRECUENTES
========================= */
$top_frecuencia = $mysqli->query("
SELECT c.nombre,
COUNT(*) total
FROM z_pedidos p
JOIN z_clientes c ON c.id=p.idcliente
$where
GROUP BY p.idcliente
ORDER BY total DESC
LIMIT 20
");
?>

<!DOCTYPE html>
<html lang="es">
<?php include $r."inc/head.php"; ?>
<body class="fixed-nav sticky-footer bg-dark" id="page-top">
<?php include $r."inc/nav.php"; ?>

<div class="content-wrapper">
<div class="container-fluid">

<div class="card mb-3 p-3">
<form class="form-row">
<div class="col-md-3">
<label>Desde</label>
<input type="date" name="fi" value="<?=$fi?>" class="form-control">
</div>

<div class="col-md-3">
<label>Hasta</label>
<input type="date" name="ff" value="<?=$ff?>" class="form-control">
</div>

<div class="col-md-4">
<label>Cliente</label>
<select name="idcliente" class="form-control">
<option value="">Todos</option>
<?php while($c=$clientes->fetch_assoc()){ ?>
<option value="<?=$c['id']?>" <?=$idcliente==$c['id']?'selected':''?>><?=$c['nombre']?></option>
<?php } ?>
</select>
</div>

<div class="col-md-2 mt-4">
<button class="btn btn-primary btn-block">Filtrar</button>
</div>
</form>
</div>

<!-- KPIs -->
<div class="row text-center">
<div class="col-md-2"><div class="card p-2">Clientes activos<br><b><?=$kpi['clientes']?></b></div></div>
<div class="col-md-2"><div class="card p-2">Ventas<br><b>$<?=number_format($kpi['ventas'])?></b></div></div>
<div class="col-md-2"><div class="card p-2">Utilidad<br><b>$<?=number_format($kpi['utilidad'])?></b></div></div>
<div class="col-md-2"><div class="card p-2">Pedidos<br><b><?=$kpi['pedidos']?></b></div></div>
<div class="col-md-2"><div class="card p-2">Ticket prom<br><b>$<?=number_format($kpi['ticket'])?></b></div></div>
</div>

<!-- TOP VENTAS -->
<div class="row mt-4">
<div class="col-md-6">
<h5>Clientes que más compran</h5>
<table class="table table-dark table-sm">
<tr><th>Cliente</th><th class="text-right">Ventas</th></tr>
<?php while($c=$top_ventas->fetch_assoc()){ ?>
<tr>
<td><?=$c['nombre']?></td>
<td class="text-right">$<?=number_format($c['total'])?></td>
</tr>
<?php } ?>
</table>
</div>
<div class="col-md-6"><canvas id="grafVentas"></canvas></div>
</div>

<!-- TOP UTILIDAD -->
<div class="row mt-4">
<div class="col-md-6">
<h5>Clientes más rentables</h5>
<table class="table table-dark table-sm">
<tr><th>Cliente</th><th class="text-right">Utilidad</th></tr>
<?php while($c=$top_utilidad->fetch_assoc()){ ?>
<tr>
<td><?=$c['nombre']?></td>
<td class="text-right">$<?=number_format($c['total'])?></td>
</tr>
<?php } ?>
</table>
</div>
<div class="col-md-6"><canvas id="grafUtilidad"></canvas></div>
</div>

<!-- FRECUENCIA -->
<div class="row mt-4">
<div class="col-md-6">
<h5>Clientes más frecuentes</h5>
<table class="table table-dark table-sm">
<tr><th>Cliente</th><th class="text-right">Pedidos</th></tr>
<?php while($c=$top_frecuencia->fetch_assoc()){ ?>
<tr>
<td><?=$c['nombre']?></td>
<td class="text-right"><?=$c['total']?></td>
</tr>
<?php } ?>
</table>
</div>
<div class="col-md-6"><canvas id="grafFrecuencia"></canvas></div>
</div>

</div>
<?php include $r."inc/foot.php"; ?>
</div>

<script>
function makeBar(id, labels, data, titulo){
const baseColors=['#4e73df','#1cc88a','#36b9cc','#f6c23e','#e74a3b','#858796','#20c997','#fd7e14','#6610f2','#6f42c1'];
let bg=[];
for(let i=0;i<data.length;i++){ bg.push(baseColors[i % baseColors.length]); }
new Chart(document.getElementById(id),{
type:'bar',
data:{labels:labels,datasets:[{label: titulo,data:data,backgroundColor:bg}]},
options:{responsive:true}
});
}

makeBar('grafVentas',
[<?php $top_ventas->data_seek(0); while($c=$top_ventas->fetch_assoc()){echo "'".$c['nombre']."',";} ?>],
[<?php $top_ventas->data_seek(0); while($c=$top_ventas->fetch_assoc()){echo $c['total'].",";} ?>],
'Ventas por cliente');

makeBar('grafUtilidad',
[<?php $top_utilidad->data_seek(0); while($c=$top_utilidad->fetch_assoc()){echo "'".$c['nombre']."',";} ?>],
[<?php $top_utilidad->data_seek(0); while($c=$top_utilidad->fetch_assoc()){echo $c['total'].",";} ?>],
'Utilidad por cliente');

makeBar('grafFrecuencia',
[<?php $top_frecuencia->data_seek(0); while($c=$top_frecuencia->fetch_assoc()){echo "'".$c['nombre']."',";} ?>],
[<?php $top_frecuencia->data_seek(0); while($c=$top_frecuencia->fetch_assoc()){echo $c['total'].",";} ?>],
'Frecuencia de compra');
</script>

</body>
</html>
