<?php
/**
 * Performance Diagnostic Tool
 * Mede o tempo de cada etapa do carregamento
 */
header('Content-Type: text/html; charset=utf-8');

$start = microtime(true);
$timings = [];

function logTime(&$timings, $label, $start) {
    $now = microtime(true);
    $timings[] = [
        'label' => $label,
        'time_ms' => round(($now - $start) * 1000, 2),
        'elapsed_ms' => round(($now - $GLOBALS['_start_global']) * 1000, 2)
    ];
    return $now;
}

$GLOBALS['_start_global'] = $start;
$checkpoint = $start;

// 1. Test ENV Loading
$checkpoint = logTime($timings, '🟢 Script Start', $checkpoint);

require_once __DIR__ . '/includes/env_loader.php';
loadEnv(__DIR__ . '/.env');
$checkpoint = logTime($timings, '📦 ENV Loading', $checkpoint);

// 2. Test Database Connection
try {
    $dsn = 'mysql:host=' . getenv('DB_HOST') . ';dbname=' . getenv('DB_NAME') . ';charset=utf8mb4';
    $pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASS'), [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
    $checkpoint = logTime($timings, '🗄️ Database Connection', $checkpoint);
} catch (PDOException $e) {
    $checkpoint = logTime($timings, '❌ DB Connection FAILED: ' . $e->getMessage(), $checkpoint);
    echo "<h1>DB Connection Failed</h1><pre>" . $e->getMessage() . "</pre>";
    exit;
}

// 3. Test Session
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}
$checkpoint = logTime($timings, '🔐 Session Start', $checkpoint);

// 4. Test Dashboard Queries
try {
    $pdo->query("SELECT COUNT(*) FROM stores WHERE deleted_at IS NULL")->fetchColumn();
    $checkpoint = logTime($timings, '📊 Query: Total Stores', $checkpoint);
} catch (PDOException $e) {
    $checkpoint = logTime($timings, '❌ Query Failed (stores): ' . $e->getMessage(), $checkpoint);
}

try {
    $pdo->query("SELECT COUNT(*) FROM stores WHERE status = 'active' AND deleted_at IS NULL")->fetchColumn();
    $checkpoint = logTime($timings, '📊 Query: Active Stores', $checkpoint);
} catch (PDOException $e) {
    $checkpoint = logTime($timings, '❌ Query Failed (active stores): ' . $e->getMessage(), $checkpoint);
}

try {
    $pdo->query("SELECT COUNT(*) FROM stores WHERE status = 'trial' AND deleted_at IS NULL")->fetchColumn();
    $checkpoint = logTime($timings, '📊 Query: Trial Stores', $checkpoint);
} catch (PDOException $e) {
    $checkpoint = logTime($timings, '❌ Query Failed (trial stores): ' . $e->getMessage(), $checkpoint);
}

try {
    $pdo->query("SELECT COUNT(*) FROM stores WHERE MONTH(created_at) = MONTH(CURRENT_DATE()) AND YEAR(created_at) = YEAR(CURRENT_DATE())")->fetchColumn();
    $checkpoint = logTime($timings, '📊 Query: New Stores This Month', $checkpoint);
} catch (PDOException $e) {
    $checkpoint = logTime($timings, '❌ Query Failed (new stores): ' . $e->getMessage(), $checkpoint);
}

try {
    $pdo->query("
        SELECT COALESCE(SUM(p.price_monthly), 0) as mrr
        FROM subscriptions s
        JOIN plans p ON s.plan_id = p.id
        WHERE s.status = 'active'
    ")->fetch();
    $checkpoint = logTime($timings, '📊 Query: MRR (subscriptions + plans)', $checkpoint);
} catch (PDOException $e) {
    $checkpoint = logTime($timings, '❌ Query Failed (MRR): ' . $e->getMessage(), $checkpoint);
}

try {
    $pdo->query("SELECT COUNT(*) FROM orders WHERE MONTH(created_at) = MONTH(CURRENT_DATE())")->fetchColumn();
    $checkpoint = logTime($timings, '📊 Query: Orders This Month', $checkpoint);
} catch (PDOException $e) {
    $checkpoint = logTime($timings, '❌ Query Failed (orders count): ' . $e->getMessage(), $checkpoint);
}

try {
    $pdo->query("SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE MONTH(created_at) = MONTH(CURRENT_DATE())")->fetchColumn();
    $checkpoint = logTime($timings, '📊 Query: Revenue This Month', $checkpoint);
} catch (PDOException $e) {
    $checkpoint = logTime($timings, '❌ Query Failed (revenue): ' . $e->getMessage(), $checkpoint);
}

try {
    $pdo->query("
        SELECT s.*,
               (SELECT COUNT(*) FROM orders o WHERE o.store_id = s.id) as orders_count,
               (SELECT COALESCE(SUM(total_amount), 0) FROM orders o WHERE o.store_id = s.id) as total_revenue
        FROM stores s
        WHERE s.deleted_at IS NULL
        ORDER BY s.created_at DESC
        LIMIT 10
    ")->fetchAll();
    $checkpoint = logTime($timings, '📊 Query: Recent Stores with Subqueries (N+1!)', $checkpoint);
} catch (PDOException $e) {
    $checkpoint = logTime($timings, '❌ Query Failed (recent stores): ' . $e->getMessage(), $checkpoint);
}

// 5. Test Landing Config (from sidebar)
try {
    require_once __DIR__ . '/includes/landing-functions.php';
    $checkpoint = logTime($timings, '📂 Loading landing-functions.php', $checkpoint);
    
    getLandingConfig($pdo, 'header_logo', '');
    $checkpoint = logTime($timings, '📊 Query: Landing Config (header_logo)', $checkpoint);
    
    getLandingConfig($pdo, 'header_logo_type', 'text');
    $checkpoint = logTime($timings, '📊 Query: Landing Config (header_logo_type)', $checkpoint);
    
    getLandingConfig($pdo, 'header_logo_text', 'MontaLoja');
    $checkpoint = logTime($timings, '📊 Query: Landing Config (header_logo_text)', $checkpoint);
    
    getLandingConfig($pdo, 'header_logo_dark', '');
    $checkpoint = logTime($timings, '📊 Query: Landing Config (header_logo_dark)', $checkpoint);
} catch (Exception $e) {
    $checkpoint = logTime($timings, '❌ Landing Config Failed: ' . $e->getMessage(), $checkpoint);
}

// 6. Test DNS/Network (check if external resources block)
$checkpoint = logTime($timings, '✅ All PHP processing done', $checkpoint);

// Check MySQL slow query log
try {
    $slowQueries = $pdo->query("SHOW VARIABLES LIKE 'slow_query_log'")->fetch();
    $longQueryTime = $pdo->query("SHOW VARIABLES LIKE 'long_query_time'")->fetch();
} catch (Exception $e) {}

// Check table sizes
$tableInfo = [];
try {
    $tables = ['stores', 'orders', 'subscriptions', 'plans', 'landing_page_config', 'super_admins'];
    foreach ($tables as $table) {
        try {
            $count = $pdo->query("SELECT COUNT(*) FROM `$table`")->fetchColumn();
            $tableInfo[$table] = $count;
        } catch (Exception $e) {
            $tableInfo[$table] = 'ERROR: ' . $e->getMessage();
        }
    }
} catch (Exception $e) {}

// Check if indexes exist on key columns
$indexInfo = [];
try {
    $indexes = $pdo->query("SHOW INDEX FROM stores")->fetchAll();
    foreach ($indexes as $idx) {
        $indexInfo[] = $idx['Table'] . '.' . $idx['Column_name'] . ' (' . $idx['Key_name'] . ')';
    }
} catch (Exception $e) {
    $indexInfo[] = 'Error: ' . $e->getMessage();
}

$totalTime = round((microtime(true) - $start) * 1000, 2);
?>
<!DOCTYPE html>
<html>
<head>
    <title>Performance Diagnostic</title>
    <style>
        body { font-family: monospace; background: #121212; color: #e0e0e0; padding: 20px; }
        h1 { color: #1DB954; }
        h2 { color: #CCA43B; margin-top: 2rem; }
        table { border-collapse: collapse; width: 100%; margin: 10px 0; }
        th, td { border: 1px solid #333; padding: 8px 12px; text-align: left; }
        th { background: #1a1a1a; color: #1DB954; }
        .fast { color: #1DB954; }
        .medium { color: #F59B23; }
        .slow { color: #E22134; font-weight: bold; }
        .total { font-size: 1.5rem; color: #1DB954; margin: 20px 0; padding: 15px; background: #1a1a1a; border-radius: 8px; }
        .total.slow-total { color: #E22134; }
        .info-box { background: #1a1a1a; padding: 15px; border-radius: 8px; margin: 10px 0; }
        .section { margin: 20px 0; }
    </style>
</head>
<body>
    <h1>🔍 Performance Diagnostic - DuoLead</h1>
    <div class="total <?= $totalTime > 2000 ? 'slow-total' : '' ?>">
        ⏱️ Total PHP Processing Time: <strong><?= $totalTime ?> ms</strong>
    </div>

    <h2>📊 Step-by-Step Timing</h2>
    <table>
        <tr><th>Step</th><th>Duration (ms)</th><th>Cumulative (ms)</th></tr>
        <?php foreach ($timings as $t): 
            $class = $t['time_ms'] < 10 ? 'fast' : ($t['time_ms'] < 100 ? 'medium' : 'slow');
        ?>
        <tr>
            <td><?= $t['label'] ?></td>
            <td class="<?= $class ?>"><?= $t['time_ms'] ?> ms</td>
            <td><?= $t['elapsed_ms'] ?> ms</td>
        </tr>
        <?php endforeach; ?>
    </table>

    <h2>📦 Table Sizes</h2>
    <div class="info-box">
        <table>
            <tr><th>Table</th><th>Row Count</th></tr>
            <?php foreach ($tableInfo as $table => $count): ?>
            <tr><td><?= $table ?></td><td><?= $count ?></td></tr>
            <?php endforeach; ?>
        </table>
    </div>

    <h2>🔑 Indexes on 'stores' table</h2>
    <div class="info-box">
        <?php foreach ($indexInfo as $idx): ?>
        <div><?= $idx ?></div>
        <?php endforeach; ?>
    </div>

    <h2>🐢 MySQL Slow Query Config</h2>
    <div class="info-box">
        <div>slow_query_log: <?= $slowQueries['Value'] ?? 'N/A' ?></div>
        <div>long_query_time: <?= $longQueryTime['Value'] ?? 'N/A' ?></div>
    </div>

    <h2>🌐 External Resources Analysis</h2>
    <div class="info-box">
        <p>The superadmin header loads this external resource in the &lt;head&gt;:</p>
        <ul>
            <li><code>https://cdn.jsdelivr.net/npm/iconify-icon@1.0.7/dist/iconify-icon.min.js</code> — This is a <strong>render-blocking script</strong> that must download before page renders!</li>
            <li><code>/assets/css/style.css</code> — Local CSS file</li>
            <li><code>/pages/superadmin/assets/css/global.css</code> — Local CSS file</li>
        </ul>
        <p style="color: #F59B23;">⚠️ If your internet is slow or DNS resolution is delayed, the Iconify script will block the entire page from rendering!</p>
    </div>

    <h2>💡 Possible Causes of Slow Loading</h2>
    <div class="info-box">
        <ol>
            <li><strong>Render-blocking external JS</strong>: <code>iconify-icon.min.js</code> loaded synchronously in &lt;head&gt;</li>
            <li><strong>N+1 Subqueries</strong>: Recent stores query uses correlated subqueries for orders_count and total_revenue</li>
            <li><strong>4 separate landing_page_config queries</strong> in sidebar (could be 1 query)</li>
            <li><strong>Inline CSS</strong>: ~900 lines of CSS embedded in header.php instead of external cached file</li>
            <li><strong>Multiple DB queries</strong>: 7+ separate queries for dashboard metrics</li>
            <li><strong>Session handling</strong>: Check if session is using file-based storage with slow disk</li>
        </ol>
    </div>
</body>
</html>
