<?php
// 1. CONFIGURAÇÕES DE ERRO
ini_set('display_errors', 1);
error_reporting(E_ALL);

// 2. DADOS DE CONEXÃO
$host = "localhost";
$user = "u853421092_loja"; 
$pass = "Tuju0421"; 
$db   = "u853421092_loja"; 

$conn = @new mysqli($host, $user, $pass, $db);
if ($conn->connect_error) {
    die("Erro de conexão: " . $conn->connect_error);
}

// 3. GARANTE QUE A TABELA EXISTE
$tabela = "resultados_lotofacil";
$sql_table = "CREATE TABLE IF NOT EXISTS $tabela (
    id INT AUTO_INCREMENT PRIMARY KEY,
    concurso INT UNIQUE NOT NULL,
    bola1 INT, bola2 INT, bola3 INT, bola4 INT, bola5 INT,
    bola6 INT, bola7 INT, bola8 INT, bola9 INT, bola10 INT,
    bola11 INT, bola12 INT, bola13 INT, bola14 INT, bola15 INT
)";
$conn->query($sql_table);

// 4. PUXAR DADOS DO GOOGLE SHEETS (CSV)
$csv_url = "https://docs.google.com/spreadsheets/d/e/2PACX-1vSm7YnJCse7QomkYm1g6Fd_g8x3q84GgU1VZ_oxIUFs18zp7mluQc0M31qQwBuh-WnNfYTbx_jx4tNy/pub?gid=1721853560&single=true&output=csv";

$file = fopen($csv_url, "r");
if ($file) {
    while (($row = fgetcsv($file)) !== FALSE) {
        if (count($row) < 16) continue;
        if (!is_numeric($row[0])) continue;

        $concurso = (int)$row[0];
        
        $stmt = $conn->prepare("INSERT IGNORE INTO $tabela (concurso, bola1, bola2, bola3, bola4, bola5, bola6, bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        
        $stmt->bind_param("iiiiiiiiiiiiiiii", 
            $concurso, 
            $row[1], $row[2], $row[3], $row[4], $row[5], 
            $row[6], $row[7], $row[8], $row[9], $row[10], 
            $row[11], $row[12], $row[13], $row[14], $row[15]
        );
        $stmt->execute();
    }
    fclose($file);
}

// 5. LÓGICA DE CÁLCULO PARA O RANKING
function getRanking($conn, $tabela, $limite = null) {
    $ranking = array_fill(1, 25, 0);
    $query = "SELECT * FROM $tabela ORDER BY concurso DESC";
    if ($limite) $query .= " LIMIT $limite";
    
    $res = $conn->query($query);
    if($res && $res->num_rows > 0) {
        while ($row = $res->fetch_assoc()) {
            for($i=1; $i<=15; $i++) {
                $n = (int)$row["bola$i"];
                if($n >= 1 && $n <= 25) $ranking[$n]++;
            }
        }
    }
    arsort($ranking);
    return $ranking;
}

$ranking_geral = getRanking($conn, $tabela);
$dez_ultimos   = getRanking($conn, $tabela, 10);
$cinco_ultimos = getRanking($conn, $tabela, 5);
$tres_ultimos  = getRanking($conn, $tabela, 3);
$dois_ultimos  = getRanking($conn, $tabela, 2);
?>

<!DOCTYPE html>
<html lang="pt-br">
<head>
    <meta charset="UTF-8">
    <title>Ranking - TJ Presentes</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <style>
        .header-top { background-color: #28a745; color: white; padding: 10px; font-weight: bold; }
        .bg-purple { background-color: #800080; color: white; }
        .list-group-item { background-color: #fff9e6; border: 1px solid #ddd; padding: 3px 10px; font-size: 0.85rem; }
        .dezena-num { color: #28a745; font-weight: bold; font-size: 1rem; }
        
        /* Estilo para colocar 5 colunas lado a lado */
        .row-5 {
            display: flex;
            flex-wrap: nowrap;
            margin-right: -5px;
            margin-left: -5px;
        }
        .col-5-custom {
            flex: 0 0 20%;
            max-width: 20%;
            padding-right: 5px;
            padding-left: 5px;
        }

        /* Ajuste para mobile (empilhar se a tela for muito pequena) */
        @media (max-width: 768px) {
            .row-5 { flex-wrap: wrap; }
            .col-5-custom { flex: 0 0 100%; max-width: 100%; }
        }
    </style>
</head>
<body>
    <div class="header-top">GERENCIADOR DE LOTERIAS</div>
    <div class="container-fluid">
        <h5 class="mt-3" style="border-left: 5px solid #800080; padding-left: 10px;">ANÁLISE DE FREQUÊNCIA</h5>
        <div class="alert alert-success mt-2 py-1">Sincronizado com Google Sheets com sucesso!</div>

        <div class="row-5 mt-3">
            <?php
            $secoes = [
                'GERAL' => $ranking_geral, 
                '10 ÚLTIMOS' => $dez_ultimos, 
                '5 ÚLTIMOS' => $cinco_ultimos, 
                '3 ÚLTIMOS' => $tres_ultimos,
                '2 ÚLTIMOS' => $dois_ultimos
            ];
            foreach ($secoes as $titulo => $dados): ?>
            <div class="col-5-custom">
                <div class="card mb-3">
                    <div class="card-header bg-purple text-center py-1" style="font-size: 0.9rem;"><?php echo $titulo; ?></div>
                    <ul class="list-group list-group-flush">
                        <?php $p = 1; foreach ($dados as $num => $total): ?>
                        <li class="list-group-item d-flex justify-content-between align-items-center">
                            <span><small><?php echo $p; ?>º</small> <span class="dezena-num ml-1"><?php echo str_pad($num, 2, "0", STR_PAD_LEFT); ?></span></span>
                            <strong><?php echo $total; ?></strong>
                        </li>
                        <?php $p++; if($p > 25) break; endforeach; ?>
                    </ul>
                </div>
            </div>
            <?php endforeach; ?>
        </div>
    </div>
</body>
</html>