<?php
$servername = "localhost";
$username = "lotto";
$password = "12Qwaszx**";
$dbname = "lotto";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("<div class='alert alert-danger'>Error de conexión: " . $conn->connect_error . "</div>");
}

$dayOption = $_POST['table'] ?? '';
$month = $_POST['month'] ?? '';
$dayNumber = $_POST['day'] ?? '';
$year = $_POST['year'] ?? '';

$where = ["1=1"];
if ($dayOption === "smartes") {
    $where[] = "dia = 'Martes'";
} elseif ($dayOption === "ssabado") {
    $where[] = "dia = 'Sábado'";
} elseif ($dayOption === "sabado_martes") {
    $where[] = "(dia = 'Martes' OR dia = 'Sábado')";
}
if (!empty($month) && strtolower($month) !== 'todos') {
    $monthClean = $conn->real_escape_string($month);
    $where[] = "LOWER(mes) = LOWER('$monthClean')";
}
if (!empty($year)) {
    $yearInt = intval($year);
    $where[] = "YEAR(fecha) = $yearInt";
}
if (!empty($dayNumber)) {
    $dayInt = intval($dayNumber);
    $where[] = "DAY(fecha) = $dayInt";
}

$whereSql = implode(" AND ", $where);

$sql = "SELECT id, mes, dia, fecha, n01, n02, n03, n04, n05, n06, año, pega3 FROM sorteo WHERE $whereSql ORDER BY fecha DESC LIMIT 100";
$result = $conn->query($sql);

if ($result && $result->num_rows > 0) {
    $rows = [];
    $frequency = array_fill(1, 38, 0);
    $pega3Count = [];

    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;

        // Contar frecuencia de números del 01 al 06
        for ($i = 1; $i <= 6; $i++) {
            $num = intval($row['n0' . $i]);
            if ($num >= 1 && $num <= 38) {
                $frequency[$num]++;
            }
        }

        // Contar frecuencia de pega3
        $p3 = $row['pega3'];
        if (!empty($p3)) {
            if (!isset($pega3Count[$p3])) {
                $pega3Count[$p3] = 0;
            }
            $pega3Count[$p3]++;
        }
    }

    // Obtener frecuencias únicas en orden descendente
$uniqueFrequencies = array_values(array_unique(array_filter($frequency)));
rsort($uniqueFrequencies);

// Tomar top 3 frecuencias únicas
$maxFrequency = $uniqueFrequencies[0] ?? 0;
$secondMax = $uniqueFrequencies[1] ?? null;
$thirdMax = $uniqueFrequencies[2] ?? null;
    echo "<table class='table table-bordered table-striped'>";
    echo "<thead><tr><th>ID</th><th>Mes</th><th>Día</th><th>Fecha</th><th>N01</th><th>N02</th><th>N03</th><th>N04</th><th>N05</th><th>N06</th><th>Año</th><th>Pega3</th></tr></thead><tbody>";

    foreach ($rows as $row) {
        echo "<tr><td>{$row['id']}</td><td>{$row['mes']}</td><td>{$row['dia']}</td><td>{$row['fecha']}</td>";

        for ($i = 1; $i <= 6; $i++) {
            $value = intval($row['n0' . $i]);
            $freq = $frequency[$value] ?? 0;

            if ($freq == 0) {
                $bg = '#f48fb1'; $circle = '#f06292';
            } elseif ($freq == $maxFrequency) {
                $bg = '#66bb6a'; $circle = '#28a745';
            } elseif ($freq == $secondMax) {
                $bg = '#ffca28'; $circle = '#ffc107';
            } elseif ($freq == $thirdMax) {
                $bg = '#3f51b5'; $circle = '#0d33b3';
            } else {
                $bg = 'rgba(108,117,125,0.2)'; $circle = 'rgba(108,117,125,0.8)';
            }

            echo "<td style='background:$bg; color:white;'>
                    <span style='display:inline-block; margin:5px; width:30px; height:30px; background:$circle; color:white; text-align:center; line-height:30px; border-radius:50%;'>
                        $value
                    </span>
                  </td>";
        }

        echo "<td>{$row['año']}</td><td>{$row['pega3']}</td></tr>";
    }
    echo "</tbody></table>";

    // Tarjetas de frecuencia
    echo "<div class='row mt-4'>";
    $categories = [
        ['label' => 'Número más jugado', 'color' => '#66bb6a', 'bg' => '#28a745', 'value' => $maxFrequency],
        ['label' => 'Segundo más jugado', 'color' => '#ffca28', 'bg' => '#ffc107', 'value' => $secondMax],
        ['label' => 'Tercer más jugado', 'color' => '#3f51b5', 'bg' => '#0d33b3', 'value' => $thirdMax],
    ];

    foreach ($categories as $cat) {
        echo "<div class='col-md-3'><div class='card'>
                <div class='card-header text-white' style='background: {$cat['bg']}'><h5 class='card-title'>{$cat['label']}</h5></div>
                <div class='card-body' style='background: {$cat['color']}'>
                    <div style='font-size: 40px; font-weight: bold;'>{$cat['value']}</div>
                    <div style='background: {$cat['bg']}; color: white; padding: 5px; border-radius: 5px;'>Número: ";
        foreach ($frequency as $num => $count) {
            if ($count == $cat['value']) {
                echo "<span style='display:inline-block; margin:5px; width:30px; height:30px; background:#6c757d; color:white; text-align:center; line-height:30px; border-radius:50%;'>$num</span>";
            }
        }
        echo "</div></div></div></div>";
    }

    // Números no jugados
    echo "<div class='col-md-3'><div class='card'>
            <div class='card-header text-white' style='background: #f06292'><h5 class='card-title'>No jugados</h5></div>
            <div class='card-body' style='background: #f48fb1'>";
    foreach ($frequency as $num => $count) {
        if ($count == 0) {
            echo "<span style='display:inline-block; margin:5px; width:30px; height:30px; background:#f06292; color:white; text-align:center; line-height:30px; border-radius:50%;'>$num</span>";
        }
    }
    echo "</div></div></div>";

    // Tarjeta de Pega3 más frecuentes
    arsort($pega3Count);
    $topPega3 = array_slice($pega3Count, 0, 3, true);

    echo "<div class='col-md-3'><div class='card'>
            <div class='card-header text-white' style='background: #9c27b0'><h5 class='card-title'>Pega3 más frecuentes</h5></div>
            <div class='card-body' style='background: #ce93d8'>";
    foreach ($topPega3 as $value => $count) {
        echo "<div style='margin-bottom: 5px;'>
                <span style='display:inline-block; width:40px; height:40px; background:#7b1fa2; color:white; text-align:center; line-height:40px; border-radius:50%; margin-right:10px;'>$value</span>
                <span style='font-weight:bold;'>$count veces</span>
              </div>";
    }
    echo "</div></div></div>";
    echo "</div>"; // Fin de fila

    // Tabla resumen de Pega3
    echo "<h5 class='mt-4'>Resumen de Pega3</h5>";
    echo "<table class='table table-sm table-bordered'><thead><tr><th>Pega3</th><th>Repeticiones</th></tr></thead><tbody>";
    foreach ($pega3Count as $pega3 => $count) {
        echo "<tr><td>$pega3</td><td>$count</td></tr>";
    }
    echo "</tbody></table>";

} else {
    echo "<div class='alert alert-info'>No se encontraron resultados.</div>";
}

$conn->close();
?>
