<?php
// check_indexes.php - Verifica e cria APENAS os índices que faltam

header('Content-Type: text/html; charset=utf-8');

$conn = new PDO('mysql:host=localhost;dbname=digupdog_FEED;charset=utf8mb4', 
                'digupdog_FEEDadmin', 'Raimundinho1');

?>
<!DOCTYPE html>
<html>
<head>
    <title>Verificar Índices</title>
    <style>
        body { font-family: Arial; margin: 20px; }
        .exists { color: green; }
        .missing { color: red; font-weight: bold; }
        .huge { color: orange; }
        pre { background: #f0f0f0; padding: 10px; }
    </style>
</head>
<body>
<h1>🔍 Análise de Índices e Performance</h1>

<?php
// Verifica índices críticos
$critical_indexes = [
    ['table' => 'pinfeeds', 'column' => 'id', 'type' => 'DESC'],
    ['table' => 'comments', 'column' => 'feed_id', 'type' => 'ASC'],
    ['table' => 'favorites', 'column' => 'user_id,feed_id', 'type' => 'COMPOSITE'],
    ['table' => 'likes_dislikes', 'column' => 'user_id,feed_id', 'type' => 'COMPOSITE']
];

echo "<h2>Índices Críticos:</h2>";
echo "<ul>";

foreach ($critical_indexes as $idx) {
    $result = $conn->query("SHOW INDEX FROM {$idx['table']}")->fetchAll();
    $found = false;
    
    foreach ($result as $index) {
        if ($idx['type'] == 'DESC' && $index['Column_name'] == $idx['column']) {
            $found = true;
            break;
        } elseif ($idx['type'] == 'COMPOSITE') {
            $columns = explode(',', $idx['column']);
            if ($index['Column_name'] == $columns[0]) {
                $found = true;
                break;
            }
        } elseif ($index['Column_name'] == $idx['column']) {
            $found = true;
            break;
        }
    }
    
    if ($found) {
        echo "<li class='exists'>✓ {$idx['table']}.{$idx['column']} - OK</li>";
    } else {
        echo "<li class='missing'>✗ {$idx['table']}.{$idx['column']} - FALTANDO!</li>";
    }
}
echo "</ul>";

// Análise de tabelas
echo "<h2>Tamanho das Tabelas:</h2>";
$tables = $conn->query("
    SELECT TABLE_NAME, TABLE_ROWS, 
           ROUND(DATA_LENGTH/1024/1024/1024, 2) as GB_DATA,
           ROUND(INDEX_LENGTH/1024/1024/1024, 2) as GB_INDEX
    FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA = 'digupdog_FEED'
    ORDER BY TABLE_ROWS DESC
")->fetchAll();

echo "<table border='1' cellpadding='5'>";
echo "<tr><th>Tabela</th><th>Registros</th><th>Dados (GB)</th><th>Índices (GB)</th></tr>";
foreach ($tables as $t) {
    $class = $t['TABLE_ROWS'] > 100000000 ? 'huge' : '';
    echo "<tr class='$class'>";
    echo "<td>{$t['TABLE_NAME']}</td>";
    echo "<td>" . number_format($t['TABLE_ROWS']) . "</td>";
    echo "<td>{$t['GB_DATA']}</td>";
    echo "<td>{$t['GB_INDEX']}</td>";
    echo "</tr>";
}
echo "</table>";

// O PROBLEMA REAL
echo "<h2 class='missing'>⚠️ PROBLEMA IDENTIFICADO:</h2>";
echo "<p>A tabela <strong>title_description_index</strong> tem <strong>746 MILHÕES</strong> de registros!</p>";
echo "<p>Isso está tornando as buscas extremamente lentas.</p>";

echo "<h2>✅ SOLUÇÃO IMEDIATA:</h2>";
echo "<ol>";
echo "<li>Use o arquivo <code>get_pinfeeds_fixed.php</code> que EVITA essa tabela gigante</li>";
echo "<li>Crie os índices faltantes abaixo (se houver)</li>";
echo "<li>Considere limpar a tabela title_description_index (backup primeiro!)</li>";
echo "</ol>";

// Comandos SQL para criar índices faltantes
echo "<h2>Comandos SQL para Executar:</h2>";
echo "<pre>";

// Verifica e gera comando para pinfeeds
$has_id_index = false;
$indexes = $conn->query("SHOW INDEX FROM pinfeeds WHERE Column_name = 'id'")->fetchAll();
foreach ($indexes as $idx) {
    if ($idx['Key_name'] != 'PRIMARY') {
        $has_id_index = true;
        break;
    }
}
if (!$has_id_index) {
    echo "-- CRÍTICO: Índice para ordenação\n";
    echo "ALTER TABLE pinfeeds ADD INDEX idx_id_desc (id DESC);\n\n";
}

// Verifica comments
$has_feed_index = false;
$indexes = $conn->query("SHOW INDEX FROM comments WHERE Column_name = 'feed_id'")->fetchAll();
if (!empty($indexes)) {
    $has_feed_index = true;
}
if (!$has_feed_index) {
    echo "-- IMPORTANTE: Índice para comentários\n";
    echo "ALTER TABLE comments ADD INDEX idx_feed_id (feed_id);\n\n";
}

// Otimização da tabela gigante
echo "-- OPCIONAL: Limpar tabela gigante (FAÇA BACKUP PRIMEIRO!)\n";
echo "-- DELETE FROM title_description_index WHERE occurrence_count < 2;\n";
echo "-- OPTIMIZE TABLE title_description_index;\n";

echo "</pre>";

// Teste de performance
echo "<h2>Teste de Performance:</h2>";
$start = microtime(true);
$conn->query("SELECT * FROM pinfeeds ORDER BY id DESC LIMIT 21")->fetchAll();
$time = round((microtime(true) - $start) * 1000, 2);

echo "<p>Query básica executada em: <strong>{$time}ms</strong></p>";

if ($time > 500) {
    echo "<p class='missing'>⚠️ Ainda está lento! Crie os índices acima urgentemente!</p>";
} else {
    echo "<p class='exists'>✓ Performance aceitável</p>";
}
?>

<h2>📋 Resumo:</h2>
<div style="background: #ffffcc; padding: 20px; border-radius: 10px;">
    <p><strong>1.</strong> Renomeie <code>get_pinfeeds_fixed.php</code> para <code>get_pinfeeds.php</code></p>
    <p><strong>2.</strong> Execute os comandos SQL mostrados acima</p>
    <p><strong>3.</strong> A tabela <code>title_description_index</code> com 746M registros é o principal problema!</p>
</div>

</body>
</html>