<?php
session_start();
if (!isset($_SESSION['Acceso']) || $_SESSION['Acceso'] !== 'AccesoSiqueSi') { die("Acceso denegado."); }

$idSucursal = $_SESSION['IDS'] ?? '1';
$dbName = $_SESSION['BD'] ?? "MS_Datos";
$px = "$IDUg-"; 
echo "<script> console.log('$IDU'); </script>";

// Rango de fechas
$fi = $_POST['fi'] ?? $_GET['fi'] ?? date('Y-m-d', strtotime('-30 days'));
$ff = $_POST['ff'] ?? $_GET['ff'] ?? date('Y-m-d');

try {
    $pdo = new PDO("mysql:host=localhost;dbname=$dbName;charset=utf8", "root", "33comRxXMysql");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 1. KPIs FINANCIEROS (Venta, Inversión, Ganancia y Margen)
    $sqlKpis = "SELECT 
                SUM(vd.Subtotal) as VentaBruta,
                SUM(vd.Costo_Original * vd.Cantidad) as InversionCOGS,
                SUM(vd.Subtotal - (vd.Costo_Original * vd.Cantidad)) as GananciaNeta
               FROM `{$px}Ventas_Cabecera` vc
               INNER JOIN `{$px}Ventas_Detalle` vd ON vc.ID_Venta = vd.ID_Venta
               WHERE vc.IDS_Sucursal = ? AND DATE(vc.Fecha) BETWEEN ? AND ?";
    
    $stmtKpi = $pdo->prepare($sqlKpis);
    $stmtKpi->execute([$idSucursal, $fi, $ff]);
    $kpi = $stmtKpi->fetch(PDO::FETCH_ASSOC);

    // Cálculo de Margen Global
    $ventaTotal = $kpi['VentaBruta'] ?: 1;
    $margenGlobal = ($kpi['GananciaNeta'] / $ventaTotal) * 100;

    // 2. TENDENCIA COMPARATIVA (Gráfica de 3 Líneas)
    $sqlTrend = "SELECT 
                    DATE(vc.Fecha) as Dia, 
                    SUM(vd.Subtotal) as dVenta,
                    SUM(vd.Costo_Original * vd.Cantidad) as dInversion,
                    SUM(vd.Subtotal - (vd.Costo_Original * vd.Cantidad)) as dGanancia
                 FROM `{$px}Ventas_Cabecera` vc
                 INNER JOIN `{$px}Ventas_Detalle` vd ON vc.ID_Venta = vd.ID_Venta
                 WHERE vc.IDS_Sucursal = ? AND DATE(vc.Fecha) BETWEEN ? AND ?
                 GROUP BY DATE(vc.Fecha) ORDER BY Dia ASC";
    $stmtTrend = $pdo->prepare($sqlTrend);
    $stmtTrend->execute([$idSucursal, $fi, $ff]);
    $trendData = $stmtTrend->fetchAll(PDO::FETCH_ASSOC);

    // 3. TABLA DE RENTABILIDAD POR PRODUCTO (Pareto de Ganancia)
    $sqlPareto = "SELECT 
                    vd.Nombre_Producto, vd.SKU, 
                    SUM(vd.Cantidad) as Cant, 
                    SUM(vd.Subtotal) as VentaMonto,
                    SUM(vd.Costo_Original * vd.Cantidad) as InversionMonto,
                    SUM(vd.Subtotal - (vd.Costo_Original * vd.Cantidad)) as GananciaMonto
                  FROM `{$px}Ventas_Detalle` vd
                  INNER JOIN `{$px}Ventas_Cabecera` vc ON vd.ID_Venta = vc.ID_Venta
                  WHERE vc.IDS_Sucursal = ? AND DATE(vc.Fecha) BETWEEN ? AND ?
                  GROUP BY vd.SKU, vd.Nombre_Producto 
                  ORDER BY GananciaMonto DESC LIMIT 20";
    $stmtPareto = $pdo->prepare($sqlPareto);
    $stmtPareto->execute([$idSucursal, $fi, $ff]);
    $paretoData = $stmtPareto->fetchAll(PDO::FETCH_ASSOC);

    // 4. RANKING DE CLIENTES VIP
    $sqlCli = "SELECT c.Nombre, COUNT(vc.ID_Venta) as Notas, SUM(vc.Total) as InversionCliente
               FROM `{$px}Ventas_Cabecera` vc
               INNER JOIN `{$px}Clientes` c ON vc.IDC_Cliente = c.IDC
               WHERE vc.IDS_Sucursal = ? AND DATE(vc.Fecha) BETWEEN ? AND ?
               GROUP BY vc.IDC_Cliente, c.Nombre ORDER BY InversionCliente DESC LIMIT 8";
    $stmtCli = $pdo->prepare($sqlCli);
    $stmtCli->execute([$idSucursal, $fi, $ff]);
    $topClientes = $stmtCli->fetchAll(PDO::FETCH_ASSOC);

} catch (PDOException $e) { die("Error: " . $e->getMessage()); }
?>

<style>
    .report-bi { background: #f0f3f7; min-height: 100vh; padding: 10px; width: 100%; }
    .card-kpi { background: #fff; border-radius: 12px; padding: 20px; box-shadow: 0 4px 15px rgba(0,0,0,0.05); height: 100%; }
    .kpi-title { font-size: 11px; font-weight: 800; color: #adb5bd; text-transform: uppercase; letter-spacing: 1.2px; }
    .kpi-val { font-size: 26px; font-weight: 900; color: #2d3436; display: block; margin-top: 5px; }
    
    .text-revenue { color: #f26522; } /* Naranja */
    .text-investment { color: #4e73df; } /* Azul */
    .text-profit { color: #1cc88a; } /* Verde */
    
    .panel-bi { background: #fff; border-radius: 15px; padding: 25px; box-shadow: 0 4px 20px rgba(0,0,0,0.04); height: 100%; }
    .table-bi thead th { background: #343a40; color: #fff; border: none; font-size: 11px; text-transform: uppercase; }
    .table-bi td { vertical-align: middle; border-top: 1px solid #f1f2f6; font-size: 13px; }
</style>

<div class="report-bi">
    <div class="d-flex justify-content-between align-items-center mb-4 px-2">
        <div>
            <h2 class="font-weight-bold m-0 text-dark">Balanced Scorecard Comercial</h2>
            <p class="text-muted m-0 small"><i class="feather icon-bar-chart-2"></i> Análisis de Rentabilidad Neta y Flujo de Capital</p>
        </div>
        <form class="form-inline bg-white p-2 rounded-pill shadow-sm border" method="POST" action="index.php?A=PVReporte">
            <input type="date" name="fi" class="form-control form-control-sm border-0 bg-transparent" value="<?php echo $fi; ?>">
            <span class="mx-2 text-muted">/</span>
            <input type="date" name="ff" class="form-control form-control-sm border-0 bg-transparent" value="<?php echo $ff; ?>">
            <button class="btn btn-dark btn-sm rounded-pill px-4 ml-2 shadow">SINCRONIZAR</button>
        </form>
    </div>

    <div class="row no-gutters mb-4">
        <div class="col-md-3 px-2">
            <div class="card-kpi">
                <span class="kpi-title">Venta Bruta</span>
                <span class="kpi-val text-revenue">$<?php echo number_format($kpi['VentaBruta'] ?? 0, 2); ?></span>
            </div>
        </div>
        <div class="col-md-3 px-2">
            <div class="card-kpi">
                <span class="kpi-title">Inversión (COGS)</span>
                <span class="kpi-val text-investment">$<?php echo number_format($kpi['InversionCOGS'] ?? 0, 2); ?></span>
            </div>
        </div>
        <div class="col-md-3 px-2">
            <div class="card-kpi" style="border-bottom: 5px solid #1cc88a;">
                <span class="kpi-title text-profit">Ganancia Neta</span>
                <span class="kpi-val text-profit">$<?php echo number_format($kpi['GananciaNeta'] ?? 0, 2); ?></span>
            </div>
        </div>
        <div class="col-md-3 px-2">
            <div class="card-kpi">
                <span class="kpi-title">Margen de Utilidad</span>
                <span class="kpi-val"><?php echo number_format($margenGlobal, 1); ?>%</span>
            </div>
        </div>
    </div>

    

    <div class="row no-gutters mb-4">
        <div class="col-12 px-2">
            <div class="panel-bi">
                <h6 class="font-weight-bold mb-4">Análisis de Desempeño Diario: Ventas vs Costos vs Utilidad</h6>
                <div style="height: 350px;"><canvas id="chartFinance"></canvas></div>
            </div>
        </div>
    </div>

    <div class="row no-gutters">
        <div class="col-lg-9 px-2">
            <div class="panel-bi">
                <h6 class="font-weight-bold mb-4">Matriz de Rentabilidad por Producto</h6>
                <div class="table-responsive">
                    <table class="table table-hover table-bi">
                        <thead>
                            <tr>
                                <th>DESCRIPCIÓN</th>
                                <th class="text-center">CANT</th>
                                <th class="text-right text-revenue">VENTA</th>
                                <th class="text-right text-investment">INVERSIÓN</th>
                                <th class="text-right text-profit">GANANCIA</th>
                                <th class="text-right">MARGEN %</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach($paretoData as $p): 
                                $margen = ($p['VentaMonto'] > 0) ? ($p['GananciaMonto'] / $p['VentaMonto']) * 100 : 0;
                            ?>
                            <tr>
                                <td><b><?php echo $p['Nombre_Producto']; ?></b><br><small class="text-muted"><?php echo $p['SKU']; ?></small></td>
                                <td class="text-center"><?php echo number_format($p['Cant'], 0); ?></td>
                                <td class="text-right font-weight-bold">$<?php echo number_format($p['VentaMonto'], 2); ?></td>
                                <td class="text-right text-muted">$<?php echo number_format($p['InversionMonto'], 2); ?></td>
                                <td class="text-right font-weight-bold text-profit">+$<?php echo number_format($p['GananciaMonto'], 2); ?></td>
                                <td class="text-right">
                                    <span class="badge <?php echo $margen > 30 ? 'badge-success' : 'badge-light'; ?> px-2 py-1">
                                        <?php echo number_format($margen, 1); ?>%
                                    </span>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

        <div class="col-lg-3 px-2">
            <div class="panel-bi bg-dark text-white">
                <h6 class="font-weight-bold mb-4 text-orange">Ranking Clientes VIP</h6>
                <div class="list-group list-group-flush bg-transparent">
                    <?php foreach($topClientes as $c): ?>
                    <div class="list-group-item bg-transparent px-0 border-secondary d-flex justify-content-between">
                        <div>
                            <span class="font-weight-bold d-block"><?php echo $c['Nombre']; ?></span>
                            <small class="text-muted"><?php echo $c['Notas']; ?> ventas</small>
                        </div>
                        <span class="font-weight-bold text-orange">$<?php echo number_format($c['InversionCliente'], 0); ?></span>
                    </div>
                    <?php endforeach; ?>
                </div>
            </div>
        </div>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
document.addEventListener('DOMContentLoaded', function() {
    const ctx = document.getElementById('chartFinance').getContext('2d');
    new Chart(ctx, {
        type: 'line',
        data: {
            labels: <?php echo json_encode(array_column($trendData, 'Dia')); ?>,
            datasets: [
                {
                    label: 'Venta ($)',
                    data: <?php echo json_encode(array_column($trendData, 'dVenta')); ?>,
                    borderColor: '#f26522', backgroundColor: 'rgba(242, 101, 34, 0.1)', fill: true, tension: 0.4, borderWidth: 3
                },
                {
                    label: 'Inversión ($)',
                    data: <?php echo json_encode(array_column($trendData, 'dInversion')); ?>,
                    borderColor: '#4e73df', borderDash: [5, 5], fill: false, tension: 0.4, borderWidth: 2
                },
                {
                    label: 'Ganancia Neta ($)',
                    data: <?php echo json_encode(array_column($trendData, 'dGanancia')); ?>,
                    borderColor: '#1cc88a', fill: false, tension: 0.4, borderWidth: 4, pointRadius: 4
                }
            ]
        },
        options: {
            responsive: true, maintainAspectRatio: false,
            plugins: { legend: { position: 'bottom' } },
            scales: {
                y: { beginAtZero: true, grid: { color: '#f1f1f1' }, ticks: { callback: v => '$' + v } },
                x: { grid: { display: false } }
            }
        }
    });
});
</script>