<?php
/**
 * ============================================
 * FLOWBOT DCI - DATABASE ANALYZER v7.0
 * ============================================
 * Analyzes your database for v7.0 compatibility
 * Upload and access via browser to see report
 * ============================================
 */

error_reporting(E_ALL);
ini_set('display_errors', 1);

// Database configuration
$dbConfig = [
    'host' => 'localhost',
    'port' => 3306,
    'database' => 'digupdog_FEED',
    'username' => 'digupdog_FEEDadmin',
    'password' => 'Raimundinho1',
];

// Required tables for v7.0
$requiredTables = [
    'pinfeeds' => [
        'required' => true,
        'description' => 'Main feed storage table (existing)',
        'columns' => ['id', 'link', 'title', 'description', 'thumbnail', 'domain']
    ],
    'crawler_jobs' => [
        'required' => true,
        'description' => 'Crawler job tracking',
        'columns' => ['id', 'status', 'mode', 'max_pages', 'max_depth', 'created_at']
    ],
    'crawler_domain_stats' => [
        'required' => true,
        'description' => 'Domain statistics (NEW in v7.0)',
        'columns' => ['id', 'domain', 'total_visits', 'successful_visits', 'failed_visits']
    ],
    'crawler_content_hashes' => [
        'required' => true,
        'description' => 'Duplicate detection (NEW in v7.0)',
        'columns' => ['id', 'url_hash', 'content_hash', 'first_seen']
    ],
    'crawler_metrics' => [
        'required' => false,
        'description' => 'Analytics metrics (NEW in v7.0)',
        'columns' => ['id', 'metric_date', 'total_jobs', 'total_urls_crawled']
    ],
    'crawler_checkpoints' => [
        'required' => false,
        'description' => 'Resume checkpoints (NEW in v7.0)',
        'columns' => ['id', 'job_id', 'checkpoint_data']
    ],
];

// New columns needed for crawler_jobs in v7.0
$newColumnsForCrawlerJobs = [
    'mode' => "ENUM('deep','search','sitemap','infinite','hybrid') DEFAULT 'deep'",
    'priority' => "TINYINT DEFAULT 5",
    'max_pages' => "INT UNSIGNED DEFAULT 100",
    'max_depth' => "INT UNSIGNED DEFAULT 3",
    'parallel_count' => "INT UNSIGNED DEFAULT 5",
    'same_domain_only' => "BOOLEAN DEFAULT TRUE",
    'relevance_threshold' => "FLOAT DEFAULT 2.0",
    'session_id' => "VARCHAR(64)",
];

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Database Analyzer - Flowb0t DCI v7.0</title>
    <style>
        * { box-sizing: border-box; margin: 0; padding: 0; }
        body {
            font-family: 'Segoe UI', Tahoma, sans-serif;
            background: linear-gradient(135deg, #1a1a2e 0%, #16213e 100%);
            color: #f8fafc;
            min-height: 100vh;
            padding: 40px 20px;
        }
        .container { max-width: 1000px; margin: 0 auto; }
        h1 {
            font-size: 2rem;
            margin-bottom: 10px;
            background: linear-gradient(135deg, #6366f1, #8b5cf6);
            -webkit-background-clip: text;
            -webkit-text-fill-color: transparent;
        }
        .subtitle { color: #94a3b8; margin-bottom: 30px; }
        .card {
            background: rgba(26, 26, 46, 0.8);
            border: 1px solid rgba(255,255,255,0.1);
            border-radius: 12px;
            padding: 24px;
            margin-bottom: 20px;
            backdrop-filter: blur(10px);
        }
        .card h2 {
            font-size: 1.2rem;
            margin-bottom: 15px;
            display: flex;
            align-items: center;
            gap: 10px;
        }
        oporta .success { color: #10b981; }
        .warning { color: #f59e0b; }
        .error { color: #ef4444; }
        .info { color: #3b82f6; }
        .badge {
            display: inline-block;
            padding: 4px 12px;
            border-radius: 20px;
            font-size: 0.8rem;
            font-weight: 600;
        }
        .badge-success { background: rgba(16, 185, 129, 0.2); color: #10b981; }
        .badge-warning { background: rgba(245, 158, 11, 0.2); color: #f59e0b; }
        .badge-error { background: rgba(239, 68, 68, 0.2); color: #ef4444; }
        .badge-info { background: rgba(59, 130, 246, 0.2); color: #3b82f6; }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 15px;
            font-size: 0.9rem;
        }
        th, td {
            padding: 12px;
            text-align: left;
            border-bottom: 1px solid rgba(255,255,255,0.1);
        }
        th {
            color: #94a3b8;
            font-weight: 500;
            text-transform: uppercase;
            font-size: 0.75rem;
            letter-spacing: 0.05em;
        }
        tr:hover { background: rgba(255,255,255,0.02); }
        .icon { font-size: 1.2rem; }
        .summary-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 15px;
            margin-bottom: 20px;
        }
        .summary-item {
            background: rgba(0,0,0,0.2);
            padding: 20px;
            border-radius: 8px;
            text-align: center;
        }
        .summary-value {
            font-size: 2rem;
            font-weight: 700;
        }
        .summary-label {
            color: #94a3b8;
            font-size: 0.85rem;
            margin-top: 5px;
        }
        .btn {
            display: inline-block;
            padding: 12px 24px;
            background: linear-gradient(135deg, #6366f1, #8b5cf6);
            color: white;
            text-decoration: none;
            border-radius: 8px;
            font-weight: 600;
            margin: 5px;
            border: none;
            cursor: pointer;
        }
        .btn:hover { opacity: 0.9; }
        .btn-secondary {
            background: rgba(255,255,255,0.1);
            border: 1px solid rgba(255,255,255,0.2);
        }
        pre {
            background: rgba(0,0,0,0.3);
            padding: 15px;
            border-radius: 8px;
            overflow-x: auto;
            font-size: 0.85rem;
            margin-top: 15px;
        }
        code { font-family: 'JetBrains Mono', 'Fira Code', monospace; }
        .column-list {
            display: flex;
            flex-wrap: wrap;
            gap: 8px;
            margin-top: 10px;
        }
        .column-tag {
            background: rgba(99, 102, 241, 0.2);
            padding: 4px 10px;
            border-radius: 4px;
            font-size: 0.8rem;
            font-family: monospace;
        }
        .column-tag.missing {
            background: rgba(239, 68, 68, 0.2);
            color: #ef4444;
        }
        .column-tag.exists {
            background: rgba(16, 185, 129, 0.2);
            color: #10b981;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>🔍 Database Analyzer</h1>
        <p class="subtitle">Flowb0t DCI v7.0 Compatibility Check</p>

<?php
try {
    // Connect to database
    $dsn = sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
        $dbConfig['host'], $dbConfig['port'], $dbConfig['database']);
    $pdo = new PDO($dsn, $dbConfig['username'], $dbConfig['password'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    echo '<div class="card">';
    echo '<h2><span class="icon">✅</span> <span class="success">Database Connected Successfully</span></h2>';
    echo '<p>Server: ' . htmlspecialchars($dbConfig['host']) . ':' . $dbConfig['port'] . '</p>';
    echo '<p>Database: ' . htmlspecialchars($dbConfig['database']) . '</p>';
    echo '</div>';

    // Get all existing tables
    $stmt = $pdo->query("SHOW TABLES");
    $existingTables = $stmt->fetchAll(PDO::FETCH_COLUMN);

    // Summary counters
    $tablesExist = 0;
    $tablesMissing = 0;
    $tablesNeedUpdate = 0;
    $totalColumns = 0;
    $missingColumns = 0;

    // Check each required table
    $tableResults = [];
    foreach ($requiredTables as $tableName => $tableInfo) {
        $exists = in_array($tableName, $existingTables);
        $columns = [];
        $missingCols = [];

        if ($exists) {
            $tablesExist++;
            // Get columns
            $stmt = $pdo->query("DESCRIBE `$tableName`");
            $existingColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);

            foreach ($tableInfo['columns'] as $col) {
                $totalColumns++;
                if (in_array($col, $existingColumns)) {
                    $columns[$col] = true;
                } else {
                    $columns[$col] = false;
                    $missingCols[] = $col;
                    $missingColumns++;
                }
            }

            if (!empty($missingCols)) {
                $tablesNeedUpdate++;
            }
        } else {
            $tablesMissing++;
            foreach ($tableInfo['columns'] as $col) {
                $totalColumns++;
                $columns[$col] = false;
                $missingCols[] = $col;
                $missingColumns++;
            }
        }

        $tableResults[$tableName] = [
            'exists' => $exists,
            'required' => $tableInfo['required'],
            'description' => $tableInfo['description'],
            'columns' => $columns,
            'missing_columns' => $missingCols,
        ];
    }

    // Show summary
    echo '<div class="summary-grid">';
    echo '<div class="summary-item"><div class="summary-value success">' . $tablesExist . '</div><div class="summary-label">Tables Exist</div></div>';
    echo '<div class="summary-item"><div class="summary-value ' . ($tablesMissing > 0 ? 'error' : 'success') . '">' . $tablesMissing . '</div><div class="summary-label">Tables Missing</div></div>';
    echo '<div class="summary-item"><div class="summary-value ' . ($tablesNeedUpdate > 0 ? 'warning' : 'success') . '">' . $tablesNeedUpdate . '</div><div class="summary-label">Need Updates</div></div>';
    echo '<div class="summary-item"><div class="summary-value info">' . count($existingTables) . '</div><div class="summary-label">Total Tables</div></div>';
    echo '</div>';

    // Compatibility status
    $isCompatible = ($tablesMissing == 0 && $tablesNeedUpdate == 0);
    $statusClass = $isCompatible ? 'success' : ($tablesMissing > 0 ? 'error' : 'warning');
    $statusText = $isCompatible ? 'FULLY COMPATIBLE' : ($tablesMissing > 0 ? 'NEEDS SETUP' : 'NEEDS UPDATES');

    echo '<div class="card">';
    echo '<h2><span class="icon">' . ($isCompatible ? '🎉' : '⚠️') . '</span> Compatibility Status: <span class="badge badge-' . $statusClass . '">' . $statusText . '</span></h2>';
    if (!$isCompatible) {
        echo '<p>The installer will automatically create/update the required tables.</p>';
    }
    echo '</div>';

    // Table details
    echo '<div class="card">';
    echo '<h2><span class="icon">📊</span> Table Analysis</h2>';
    echo '<table>';
    echo '<thead><tr><th>Table</th><th>Status</th><th>Description</th><th>Columns</th></tr></thead>';
    echo '<tbody>';

    foreach ($tableResults as $tableName => $info) {
        $status = $info['exists'] ?
            (empty($info['missing_columns']) ? '<span class="badge badge-success">✓ OK</span>' : '<span class="badge badge-warning">⚠ Update</span>') :
            '<span class="badge badge-error">✗ Missing</span>';

        $colStatus = '';
        if ($info['exists']) {
            $found = count(array_filter($info['columns']));
            $total = count($info['columns']);
            $colStatus = "$found/$total columns";
        } else {
            $colStatus = '<span class="error">Not created</span>';
        }

        echo '<tr>';
        echo '<td><code>' . htmlspecialchars($tableName) . '</code></td>';
        echo '<td>' . $status . '</td>';
        echo '<td>' . htmlspecialchars($info['description']) . '</td>';
        echo '<td>' . $colStatus . '</td>';
        echo '</tr>';
    }

    echo '</tbody></table>';
    echo '</div>';

    // Check crawler_jobs columns specifically
    if (in_array('crawler_jobs', $existingTables)) {
        echo '<div class="card">';
        echo '<h2><span class="icon">🔧</span> crawler_jobs Table Analysis</h2>';

        $stmt = $pdo->query("DESCRIBE crawler_jobs");
        $existingColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);

        echo '<p>Checking for v7.0 required columns:</p>';
        echo '<div class="column-list">';

        $missingV7Columns = [];
        foreach ($newColumnsForCrawlerJobs as $col => $def) {
            if (in_array($col, $existingColumns)) {
                echo '<span class="column-tag exists">✓ ' . $col . '</span>';
            } else {
                echo '<span class="column-tag missing">✗ ' . $col . '</span>';
                $missingV7Columns[$col] = $def;
            }
        }
        echo '</div>';

        if (!empty($missingV7Columns)) {
            echo '<h3 style="margin-top: 20px;">SQL to add missing columns:</h3>';
            echo '<pre><code>';
            foreach ($missingV7Columns as $col => $def) {
                echo "ALTER TABLE crawler_jobs ADD COLUMN $col $def;\n";
            }
            echo '</code></pre>';
        } else {
            echo '<p class="success" style="margin-top: 15px;">✓ All v7.0 columns exist!</p>';
        }

        echo '</div>';
    }

    // Show all existing tables
    echo '<div class="card">';
    echo '<h2><span class="icon">📁</span> All Existing Tables (' . count($existingTables) . ')</h2>';
    echo '<div class="column-list">';
    foreach ($existingTables as $table) {
        $isRequired = isset($requiredTables[$table]);
        echo '<span class="column-tag ' . ($isRequired ? 'exists' : '') . '">' . htmlspecialchars($table) . '</span>';
    }
    echo '</div>';
    echo '</div>';

    // pinfeeds table structure (main table)
    if (in_array('pinfeeds', $existingTables)) {
        echo '<div class="card">';
        echo '<h2><span class="icon">📌</span> pinfeeds Table Structure</h2>';

        $stmt = $pdo->query("DESCRIBE pinfeeds");
        $columns = $stmt->fetchAll();

        // Count records
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM pinfeeds");
        $count = $stmt->fetch()['count'];

        echo '<p><strong>Total Records:</strong> ' . number_format($count) . '</p>';

        echo '<table>';
        echo '<thead><tr><th>Column</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th></tr></thead>';
        echo '<tbody>';
        foreach ($columns as $col) {
            echo '<tr>';
            echo '<td><code>' . htmlspecialchars($col['Field']) . '</code></td>';
            echo '<td>' . htmlspecialchars($col['Type']) . '</td>';
            echo '<td>' . ($col['Null'] === 'YES' ? 'Yes' : 'No') . '</td>';
            echo '<td>' . ($col['Key'] ?: '-') . '</td>';
            echo '<td>' . ($col['Default'] ?? 'NULL') . '</td>';
            echo '</tr>';
        }
        echo '</tbody></table>';
        echo '</div>';
    }

    // Actions
    echo '<div class="card">';
    echo '<h2><span class="icon">🚀</span> Next Steps</h2>';

    if ($isCompatible) {
        echo '<p class="success">Your database is fully compatible with v7.0!</p>';
        echo '<p>You can proceed with the installation.</p>';
    } else {
        echo '<p>The v7.0 installer will automatically:</p>';
        echo '<ul style="margin: 15px 0; padding-left: 25px;">';
        if ($tablesMissing > 0) {
            echo '<li>Create ' . $tablesMissing . ' missing table(s)</li>';
        }
        if ($tablesNeedUpdate > 0) {
            echo '<li>Add missing columns to existing tables</li>';
        }
        echo '</ul>';
    }

    echo '<div style="margin-top: 20px;">';
    echo '<a href="install_v7.php" class="btn">Run v7.0 Installer</a>';
    echo '<a href="?" class="btn btn-secondary">Refresh Analysis</a>';
    echo '</div>';
    echo '</div>';

    // Generate SQL migration
    echo '<div class="card">';
    echo '<h2><span class="icon">📝</span> Complete Migration SQL</h2>';
    echo '<p>Copy this SQL to manually create/update your database:</p>';
    echo '<pre><code>';

    // SQL for missing tables
    $sql = "-- Flowb0t DCI v7.0 Database Migration\n";
    $sql .= "-- Generated: " . date('Y-m-d H:i:s') . "\n\n";

    if (!in_array('crawler_domain_stats', $existingTables)) {
        $sql .= "-- Domain Statistics Table\n";
        $sql .= "CREATE TABLE IF NOT EXISTS crawler_domain_stats (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(255) NOT NULL UNIQUE,
    total_visits INT UNSIGNED DEFAULT 0,
    successful_visits INT UNSIGNED DEFAULT 0,
    failed_visits INT UNSIGNED DEFAULT 0,
    avg_response_time FLOAT DEFAULT 0,
    last_visit DATETIME,
    last_http_code SMALLINT UNSIGNED,
    robots_txt_cached TEXT,
    robots_txt_expires DATETIME,
    crawl_delay INT UNSIGNED DEFAULT 0,
    is_blocked BOOLEAN DEFAULT FALSE,
    block_reason VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_domain (domain),
    INDEX idx_blocked (is_blocked)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\n\n";
    }

    if (!in_array('crawler_content_hashes', $existingTables)) {
        $sql .= "-- Content Deduplication Table\n";
        $sql .= "CREATE TABLE IF NOT EXISTS crawler_content_hashes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    url_hash VARCHAR(64) NOT NULL UNIQUE,
    content_hash VARCHAR(64) NOT NULL,
    title_hash VARCHAR(64),
    first_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_seen DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    occurrence_count INT UNSIGNED DEFAULT 1,
    canonical_url TEXT,
    INDEX idx_content (content_hash),
    INDEX idx_title (title_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\n\n";
    }

    if (!in_array('crawler_metrics', $existingTables)) {
        $sql .= "-- Analytics Metrics Table\n";
        $sql .= "CREATE TABLE IF NOT EXISTS crawler_metrics (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    metric_date DATE NOT NULL,
    metric_hour TINYINT UNSIGNED,
    total_jobs INT UNSIGNED DEFAULT 0,
    completed_jobs INT UNSIGNED DEFAULT 0,
    failed_jobs INT UNSIGNED DEFAULT 0,
    total_urls_crawled INT UNSIGNED DEFAULT 0,
    total_urls_processed INT UNSIGNED DEFAULT 0,
    total_errors INT UNSIGNED DEFAULT 0,
    avg_job_duration FLOAT,
    avg_urls_per_job FLOAT,
    avg_response_time FLOAT,
    unique_domains INT UNSIGNED DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY idx_date_hour (metric_date, metric_hour)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\n\n";
    }

    if (!in_array('crawler_checkpoints', $existingTables)) {
        $sql .= "-- Checkpoints Table\n";
        $sql .= "CREATE TABLE IF NOT EXISTS crawler_checkpoints (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_id VARCHAR(50) NOT NULL UNIQUE,
    checkpoint_data LONGTEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_job (job_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\n\n";
    }

    echo htmlspecialchars($sql);
    echo '</code></pre>';
    echo '</div>';

} catch (PDOException $e) {
    echo '<div class="card">';
    echo '<h2><span class="icon">❌</span> <span class="error">Database Connection Failed</span></h2>';
    echo '<p>Error: ' . htmlspecialchars($e->getMessage()) . '</p>';
    echo '<p>Please check your database credentials in this file.</p>';
    echo '</div>';
}
?>

    </div>
</body>
</html>
