<?php
include 'includes/header.php';

// 5. Consulta unificada (Evita el error de tabla inexistente)
try {
    $sql = "SELECT SUM(total) as gran_total FROM (
                SELECT total FROM ordenes_frenos
                UNION ALL
                SELECT total FROM ordenes_motor
                UNION ALL
                SELECT total FROM ordenes_suspension
                UNION ALL
                SELECT total FROM ordenes_otros
            ) as todas_las_ordenes";

    $stmt = $pdo->query($sql);
    $resultado = $stmt->fetch();
    $total_recaudado = $resultado['gran_total'] ?? 0;
} catch (PDOException $e) {
    die("Error en la consulta financiera: " . $e->getMessage());
}



// KPIs
// 1. Ingresos del mes actual
$mes_actual = date('Y-m');
$sqlIngresosMes = "SELECT SUM(total) FROM (
SELECT total FROM ordenes_frenos WHERE DATE_FORMAT(fecha, '%Y-%m') = '$mes_actual'
UNION ALL
SELECT total FROM ordenes_motor WHERE DATE_FORMAT(fecha, '%Y-%m') = '$mes_actual'
UNION ALL
SELECT total FROM ordenes_suspension WHERE DATE_FORMAT(fecha, '%Y-%m') = '$mes_actual'
UNION ALL
SELECT total FROM ordenes_otros WHERE DATE_FORMAT(fecha, '%Y-%m') = '$mes_actual'
) as combined_tables";
$ingresosMes = $pdo->query($sqlIngresosMes)->fetchColumn() ?: 0;

// 2. Ingresos mes pasado
$mes_pasado = date('Y-m', strtotime('-1 month'));
$sqlIngresosMesPasado = "SELECT SUM(total) FROM (
SELECT total FROM ordenes_frenos WHERE DATE_FORMAT(fecha, '%Y-%m') = '$mes_pasado'
UNION ALL
SELECT total FROM ordenes_motor WHERE DATE_FORMAT(fecha, '%Y-%m') = '$mes_pasado'
UNION ALL
SELECT total FROM ordenes_suspension WHERE DATE_FORMAT(fecha, '%Y-%m') = '$mes_pasado'
UNION ALL
SELECT total FROM ordenes_otros WHERE DATE_FORMAT(fecha, '%Y-%m') = '$mes_pasado'
) as combined_tables";
$ingresosMesPasado = $pdo->query($sqlIngresosMesPasado)->fetchColumn() ?: 0;

// 3. Total Histórico
$sqlTotalHistorico = "SELECT SUM(total) FROM (
SELECT total FROM ordenes_frenos
UNION ALL
SELECT total FROM ordenes_motor
UNION ALL
SELECT total FROM ordenes_suspension
UNION ALL
SELECT total FROM ordenes_otros
) as combined_tables";
$totalHistorico = $pdo->query($sqlTotalHistorico)->fetchColumn() ?: 0;

// 4. Ordenes del mes
$sqlOrdenesMes = "SELECT SUM(cnt) FROM (
SELECT COUNT(*) as cnt FROM ordenes_frenos WHERE DATE_FORMAT(fecha, '%Y-%m') = '$mes_actual'
UNION ALL
SELECT COUNT(*) as cnt FROM ordenes_motor WHERE DATE_FORMAT(fecha, '%Y-%m') = '$mes_actual'
UNION ALL
SELECT COUNT(*) as cnt FROM ordenes_suspension WHERE DATE_FORMAT(fecha, '%Y-%m') = '$mes_actual'
UNION ALL
SELECT COUNT(*) as cnt FROM ordenes_otros WHERE DATE_FORMAT(fecha, '%Y-%m') = '$mes_actual'
) as combined_tables";
$ordenesMes = $pdo->query($sqlOrdenesMes)->fetchColumn() ?: 0;

// Detalle Anual (Mes a Mes)
$year = date('Y');
$sqlAnual = "SELECT
MONTH(fecha) as mes,
COUNT(*) as cantidad,
SUM(total) as total
FROM (
SELECT fecha, total FROM ordenes_frenos WHERE YEAR(fecha) = '$year'
UNION ALL
SELECT fecha, total FROM ordenes_motor WHERE YEAR(fecha) = '$year'
UNION ALL
SELECT fecha, total FROM ordenes_suspension WHERE YEAR(fecha) = '$year'
UNION ALL
SELECT fecha, total FROM ordenes_otros WHERE YEAR(fecha) = '$year'
) as t
GROUP BY MONTH(fecha)
ORDER BY mes ASC";
$anualData = $pdo->query($sqlAnual)->fetchAll();

// Gastos del Mes
$gastosMes = $pdo->query("SELECT SUM(monto) FROM gastos WHERE DATE_FORMAT(fecha, '%Y-%m') =
'$mes_actual'")->fetchColumn() ?: 0;

// Ultimos Gastos
$ultimosGastos = $pdo->query("SELECT * FROM gastos ORDER BY fecha DESC LIMIT 10")->fetchAll();

//include 'includes/header.php';
?>

<div class="p-4" style="position: relative; z-index: 10;">
    <h2 class="text-purple fw-bold mb-4"><i class="fas fa-chart-line"></i> Dashboard Financiero</h2>

    <!-- KPIs Header -->
    <div class="row mb-4">
        <div class="col-md-3">
            <div class="card shadow-sm border-start border-4 border-success">
                <div class="card-body">
                    <div class="text-muted small text-uppercase fw-bold">Ingresos Mes</div>
                    <h3 class="fw-bold text-success mb-0">$ <?= number_format($ingresosMes, 0, ',', '.') ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card shadow-sm border-start border-4 border-secondary">
                <div class="card-body">
                    <div class="text-muted small text-uppercase fw-bold">Mes Pasado</div>
                    <h3 class="fw-bold text-secondary mb-0">$ <?= number_format($ingresosMesPasado, 0, ',', '.') ?>
                    </h3>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card shadow-sm border-start border-4 border-primary">
                <div class="card-body">
                    <div class="text-muted small text-uppercase fw-bold">Total Histórico</div>
                    <h3 class="fw-bold text-primary mb-0">$ <?= number_format($totalHistorico, 0, ',', '.') ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card shadow-sm border-start border-4 border-info">
                <div class="card-body">
                    <div class="text-muted small text-uppercase fw-bold">Órdenes Mes</div>
                    <h3 class="fw-bold text-info mb-0"><?= $ordenesMes ?></h3>
                </div>
            </div>
        </div>
    </div>

    <div class="row">
        <!-- Detalle Anual -->
        <div class="col-md-8">
            <div class="card shadow-sm mb-4">
                <div class="card-header bg-white fw-bold">
                    <i class="fas fa-calendar-alt"></i> Detalle de Trabajos Anual (<?= $year ?>)
                </div>
                <div class="card-body p-0">
                    <table class="table table-hover mb-0">
                        <thead class="bg-light">
                            <tr>
                                <th>Mes</th>
                                <th class="text-center">Cant. Trabajos</th>
                                <th class="text-end">Total Facturado</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $meses = [
                                1 => 'Enero',
                                2 => 'Febrero',
                                3 => 'Marzo',
                                4 => 'Abril',
                                5 => 'Mayo',
                                6 => 'Junio',
                                7 => 'Julio',
                                8 => 'Agosto',
                                9 => 'Septiembre',
                                10 => 'Octubre',
                                11 => 'Noviembre',
                                12 => 'Diciembre'
                            ];
                            // Mapear datos
                            $datosPorMes = [];
                            foreach ($anualData as $d) {
                                $datosPorMes[$d['mes']] = $d;
                            }

                            foreach ($meses as $num => $nombre):
                                $cant = isset($datosPorMes[$num]) ? $datosPorMes[$num]['cantidad'] : 0;
                                $total = isset($datosPorMes[$num]) ? $datosPorMes[$num]['total'] : 0;

                                // Solo mostrar meses con datos o el actual? Mostrar todos queda mejor anual.
                                // O al menos hasta el mes actual.
                                if ($num > date('n'))
                                    continue;
                                ?>
                                <tr>
                                    <td><?= $nombre ?></td>
                                    <td class="text-center"><?= $cant ?></td>
                                    <td class="text-end fw-bold">$ <?= number_format($total, 2, ',', '.') ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

        <!-- Gestión de Gastos -->
        <div class="col-md-4">
            <div class="card shadow-sm mb-4 border-danger">
                <div class="card-header bg-danger text-white fw-bold d-flex justify-content-between align-items-center">
                    <span><i class="fas fa-money-bill-wave"></i> Gastos</span>
                    <a href="gastos.php" class="btn btn-sm btn-light text-danger fw-bold">Gestionar</a>
                </div>
                <div class="card-body">
                    <div class="text-center py-3">
                        <div class="text-muted small text-uppercase">Gastos del Mes</div>
                        <h2 class="text-danger fw-bold mb-0">$ <?= number_format($gastosMes, 2, ',', '.') ?></h2>
                    </div>
                    <hr>
                    <h6 class="fw-bold text-muted mb-3 small">ÚLTIMOS MOVIMIENTOS</h6>
                    <ul class="list-group list-group-flush small">
                        <?php foreach ($ultimosGastos as $g): ?>
                            <li class="list-group-item d-flex justify-content-between align-items-center px-0">
                                <div>
                                    <span class="fw-bold d-block"><?= htmlspecialchars($g['categoria'] ?? '') ?></span>
                                    <span class="text-muted"
                                        style="font-size: 0.8em;"><?= date('d/m', strtotime($g['fecha'])) ?> -
                                        <?= htmlspecialchars($g['descripcion'] ?? '') ?></span>
                                </div>
                                <span class="text-danger fw-bold">$
                                    <?= number_format($g['monto'], 0, ',', '.') ?></span>
                            </li>
                        <?php endforeach; ?>
                        <?php if (empty($ultimosGastos)): ?>
                            <li class="list-group-item text-center text-muted">Sin gastos registrados</li>
                        <?php endif; ?>
                    </ul>
                </div>
            </div>
        </div>
    </div>

</div>

<?php include 'includes/footer.php'; ?>