<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require_once 'db.php';

function fuzzyGroup($source) {
    $s = strtolower($source);
    if (str_starts_with($s, 'tz')) return 'TrafficZest';
    if (str_starts_with($s, 'mlgs')) return 'MyLeadGenSecret';
    if (str_starts_with($s, 'llpro') || str_contains($s, 'll')) return 'LeadsLeap';
    if (str_contains($s, 'fb')) return 'Facebook';
    if (str_contains($s, 'yt')) return 'YouTube';
    if (str_contains($s, 'mlff')) return 'MultipleIncomeFunnel';
    if (str_contains($s, 'sparky')) return 'SparkyAI';
    return null;
}


try {
    // Total leads
    $stmt = $pdo->query("SELECT COUNT(*) FROM leads");
    $total_leads = $stmt->fetchColumn();

    // Total users
    $stmt = $pdo->query("SELECT COUNT(*) FROM users");
    $total_users = $stmt->fetchColumn();

    $conversion_rate = ($total_leads > 0) ? round(($total_users / $total_leads) * 100, 2) : 0;
    $avg_leads = ($total_users > 0) ? round($total_leads / $total_users, 2) : 0;
    
    // Active members = users with full setup (program, LeadsLeap ID, both lists)
$stmt = $pdo->query("
    SELECT COUNT(*) FROM user_activity
    WHERE program_ids_set = 1 AND leadsleap_id_set = 1 AND leadsleap_lists_set = 1
");
$total_active_users = $stmt->fetchColumn();

$avg_leads_per_active_user = ($total_active_users > 0) ? round($total_leads / $total_active_users, 2) : 0;


    // Top 10 by Leads
    $stmt = $pdo->query("
        SELECT u.id AS user_id, u.first_name, u.last_name, COUNT(l.id) AS lead_count
        FROM users u
        JOIN leads l ON u.id = l.user_id
        GROUP BY u.id
        ORDER BY lead_count DESC
        LIMIT 10
    ");
    $top_leads = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Top 10 by Referrals (based on referrer_id)
    $stmt = $pdo->query("
        SELECT u.id AS user_id, u.first_name, u.last_name, COUNT(r.id) AS referral_count
        FROM users u
        LEFT JOIN users r ON u.user_id = r.referrer_id
        GROUP BY u.id
        HAVING referral_count > 0
        ORDER BY referral_count DESC
        LIMIT 10
    ");
    $top_referrals = $stmt->fetchAll(PDO::FETCH_ASSOC);

    
        // ✅ Step 1: Define known aliases (clean names)
$source_aliases = [
    'fb' => 'Facebook',
    'fb1' => 'Facebook',
    'fb2' => 'Facebook',
    'fb3' => 'Facebook',
    'fb4' => 'Facebook',
    'fbreel' => 'Facebook',
    'facebook' => 'Facebook',

    'yt' => 'YouTube',
    'ytube' => 'YouTube',
    'youtube' => 'YouTube',
    'yt-vid-exedhju-j-g' => 'YouTube',

    'll' => 'LeadsLeap',
    'llpro' => 'LeadsLeap',
    'llproad' => 'LeadsLeap',
    'llproads' => 'LeadsLeap',
    'llproads2' => 'LeadsLeap',
    'llproad2' => 'LeadsLeap',
    'llproad3' => 'LeadsLeap',
    'llai' => 'LeadsLeap',
    'llreview' => 'LeadsLeap',
    'lltrack' => 'LeadsLeap',
    'lltrackinglink' => 'LeadsLeap',
    'llbanner' => 'LeadsLeap',
    'llfreeads' => 'LeadsLeap',
    'llfreeads1' => 'LeadsLeap',
    'llfunrot' => 'LeadsLeap',
    
    'tz' => 'TrafficZest',
    'tzest' => 'TrafficZest',
    'tzest1' => 'TrafficZest',
    'tzestfms' => 'TrafficZest',
    'tzestviral' => 'TrafficZest',
    'tzll' => 'TrafficZest',
    'tzcpp' => 'TrafficZest',
    'tzrt4' => 'TrafficZest',
    'tzsc' => 'TrafficZest',
    'tzmay' => 'TrafficZest',
    'tzmay-1ac1ed' => 'TrafficZest',
    'tzmay-m2g87v' => 'TrafficZest',
    'tzmay-b28395' => 'TrafficZest',
    'tz2r' => 'TrafficZest',
    'tzrt' => 'TrafficZest',
    
    'mlgs' => 'MyLeadGenSecret',
    'mlgs1' => 'MyLeadGenSecret',
    'mlgs2' => 'MyLeadGenSecret',
    'mlgs11may25' => 'MyLeadGenSecret',
    'mlgs12may25' => 'MyLeadGenSecret',
    'mlgs13may25' => 'MyLeadGenSecret',
    'mlgs14may25' => 'MyLeadGenSecret',
    'mlgs15may25' => 'MyLeadGenSecret',
    'mlgs16may25' => 'MyLeadGenSecret',
    'mlgs17may25' => 'MyLeadGenSecret',
    'mlgs18may25' => 'MyLeadGenSecret',
    'myleadgen' => 'MyLeadGenSecret',
'myleadgensecret' => 'MyLeadGenSecret',


    'mlff' => 'MultipleIncomeFunnel',
    'mlffli' => 'MultipleIncomeFunnel',

    'sparky' => 'SparkyAI',
    'sparkyai' => 'SparkyAI',

    'wpt' => 'WarriorPlus',

    'direct entry' => 'Direct Entry',
    'direct' => 'Direct Entry',

    'zest' => 'TrafficZest',
    'rb' => 'Rotator',
    'rotatorpage' => 'Rotator',
    'giosolo' => 'Solo Ads',
    'scale' => 'Scaling Funnels',
    'scaleup' => 'Scaling Funnels',
    'scalelup' => 'Scaling Funnels',
    'scaleiup' => 'Scaling Funnels',
    'm3' => 'Scaling Funnels',
    'lp1' => 'Landing Pages',
    'ailp' => 'Landing Pages',
    'tct' => 'Custom Tracking',
    'tctai' => 'Custom Tracking',
    'tctgf' => 'Custom Tracking',
    'tctfvms' => 'Custom Tracking',
    'tctpp' => 'Custom Tracking',
    'em' => 'Email Traffic',
    'mailgold' => 'Email Traffic',
    'mailerplus' => 'Email Traffic',
    'mailsy' => 'Email Traffic',
    'send' => 'Email Traffic',
    'email' => 'Email Traffic',
    'gmass' => 'Email Traffic',
    'leapfrog' => 'Bonus Funnel',
    'referralfrenzy' => 'Referral Frenzy',
    'frenzy1' => 'Referral Frenzy',
    'frenzy2' => 'Referral Frenzy',
    'linked' => 'LinkedIn',
    'linkedad1' => 'LinkedIn'
];


    // ✅ Step 2: Fetch raw source counts from leads table
    $stmt = $pdo->query("SELECT source, COUNT(*) AS count FROM leads GROUP BY source");
    $raw_sources = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // ✅ Step 3: Process sources with alias map
    $cleaned_sources = [];

    foreach ($raw_sources as $row) {
        $raw = strtolower(trim($row['source']));
            $alias = $source_aliases[$raw] ?? fuzzyGroup($raw) ?? $raw;
$alias = ucwords(strtolower($alias));
 // fallback to capitalized raw

        if (!isset($cleaned_sources[$alias])) {
            $cleaned_sources[$alias] = 0;
        }

        $cleaned_sources[$alias] += $row['count'];
    }

    // ✅ Step 4: Sort by count descending
    arsort($cleaned_sources);


} catch (Exception $e) {
    die("Something went wrong: " . $e->getMessage());
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>MLM Lead Pro - Global Stats</title>
    <meta charset="UTF-8">
    <style>
        body { font-family: Arial, sans-serif; background: #f7f7f7; padding: 40px; }
        .box { max-width: 850px; background: white; margin: auto; padding: 30px; border-radius: 10px; box-shadow: 0 0 10px rgba(0,0,0,0.1); }
        h2, h3 { margin-top: 0; }
        ul, ol { padding-left: 20px; }
        li { margin-bottom: 8px; }
    </style>
</head>
<body>
    <div class="box">
        <h2>📊 MLM Lead Pro Global Stats (<?= date('F j, Y'); ?>)</h2>
        <ul>
            <li><strong>Total Leads:</strong> <?= number_format($total_leads); ?></li>
            <li><strong>Total Members:</strong> <?= number_format($total_users); ?></li>
            <li><strong>Global Conversion Rate:</strong> <?= $conversion_rate; ?>%</li>
            <li><strong>Average Leads per Active Member:</strong> <?= $avg_leads_per_active_user; ?> 
</li>

        </ul>

<h3>🏆 Top 10 Members by Leads</h3>
<ol>
    <?php
    function getLeadBadge($rank) {
        return match ($rank) {
            1 => '👑', 2 => '🥈', 3 => '🥉',
            default => '🔥',
        };
    }
    $i = 1;
    foreach ($top_leads as $row):
        $badge = getLeadBadge($i);
    ?>
        <li><?= $badge ?> <strong><?= htmlspecialchars($row['first_name'] . ' ' . $row['last_name']); ?></strong>: <?= $row['lead_count']; ?> leads</li>
    <?php $i++; endforeach; ?>
</ol>


        <h3>🚀 Top 10 Members by Referrals</h3>
        <ol>
            <?php foreach ($top_referrals as $row): ?>
                <li>
                    <strong><?= htmlspecialchars($row['first_name'] . ' ' . $row['last_name']); ?> :</strong>
                    <?= $row['referral_count']; ?> referrals
                </li>
            <?php endforeach; ?>
        </ol>
        
 <h3>📈 Top Lead Sources (Clean View)</h3>
<ul>
<?php
$icons = [
    'TrafficZest' => '🚦',
    'LeadsLeap' => '🔁',
    'YouTube' => '📺',
    'Facebook' => '📘',
    'Email Traffic' => '📧',
    'Rotator' => '🎯',
    'Direct Entry' => '🔓',
    'MultipleIncomeFunnel' => '🔂',
    'WarriorPlus' => '⚔️',
];

foreach ($cleaned_sources as $label => $count) {
    $normalizedLabel = strtolower($label);
    $matchFound = false;
    $iconKey = '';

    foreach ($icons as $iconLabel => $iconSymbol) {
        if (strtolower($iconLabel) === $normalizedLabel) {
            $matchFound = true;
            $iconKey = $iconLabel;
            $icon = $iconSymbol;
            break;
        }
    }

    if (!$matchFound) continue;

    $emailStmt = $pdo->prepare("
        SELECT DISTINCT email FROM leads WHERE LOWER(TRIM(source)) = ?
    ");
    $emailStmt->execute([$normalizedLabel]);
    $emails = $emailStmt->fetchAll(PDO::FETCH_COLUMN);

    if (empty($emails)) {
        echo "<li><strong>$icon $iconKey:</strong> " . number_format($count) . " leads — ❌ 0 signups (0%)</li>";
        continue;
    }

    $placeholders = implode(',', array_fill(0, count($emails), '?'));
    $checkStmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE email IN ($placeholders)");
    $checkStmt->execute($emails);
    $convertedCount = $checkStmt->fetchColumn();
    $rate = $count > 0 ? round(($convertedCount / $count) * 100, 2) : 0;

    echo "<li><strong>$icon $iconKey:</strong> " .
         number_format($count) . " leads — ✅ $convertedCount signups ($rate%)</li>";
}

?>
</ul>
<details style="margin-top:20px;">
<summary style="cursor:pointer; font-weight:bold;">⚠️ Show All Raw Traffic Sources (Unfiltered)</summary>
<ul>
<?php
// 🧭 RAW / Unfiltered section – EXCLUDES cleanView icons
foreach ($cleaned_sources as $label => $count) {
    $normalizedLabel = strtolower($label);

    // Skip if label is already in icons array (shown above in Clean View)
    $alreadyShown = false;
    foreach ($icons as $iconLabel => $iconSymbol) {
        if (strtolower($iconLabel) === $normalizedLabel) {
            $alreadyShown = true;
            break;
        }
    }
    if ($alreadyShown) continue;

    $icon = '🧭';

    $emailStmt = $pdo->prepare("
        SELECT DISTINCT email FROM leads WHERE LOWER(TRIM(source)) = ?
    ");
    $emailStmt->execute([$normalizedLabel]);
    $emails = $emailStmt->fetchAll(PDO::FETCH_COLUMN);

    if (empty($emails)) {
        echo "<li><strong>$icon $label:</strong> " . number_format($count) . " leads — ❌ 0 signups (0%)</li>";
        continue;
    }

    $placeholders = implode(',', array_fill(0, count($emails), '?'));
    $checkStmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE email IN ($placeholders)");
    $checkStmt->execute($emails);
    $convertedCount = $checkStmt->fetchColumn();
    $rate = $count > 0 ? round(($convertedCount / $count) * 100, 2) : 0;

    echo "<li><strong>$icon $label:</strong> " .
         number_format($count) . " leads — ✅ $convertedCount signups ($rate%)</li>";
}
?>

</ul>
</details>



<h3>👑 Top Performing Members by Traffic Source</h3>

<?php
$topSources = ['TrafficZest', 'LeadsLeap', 'YouTube'];
$sourceLeaders = [];

// Get top 200 rows across all sources first for performance
$stmt = $pdo->query("
    SELECT 
        l.page_id,
        l.source,
        COUNT(*) AS lead_count,
        u.first_name,
        u.last_name,
        p.headline
    FROM leads l
    JOIN users u ON l.user_id = u.id
    JOIN lead_capture_pages p ON l.page_id = p.id
    GROUP BY l.page_id, l.source
    HAVING lead_count >= 25
    ORDER BY lead_count DESC
    LIMIT 200
");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

$allowedSources = ['trafficzest', 'leadsleap', 'youtube']; // all lowercase for reliable comparison

foreach ($rows as $row) {
    $rawSource = strtolower(trim($row['source']));
    $cleanSource = $source_aliases[$rawSource] ?? fuzzyGroup($rawSource) ?? ucwords($rawSource);

    $normalizedSource = strtolower($cleanSource); // normalize to lowercase for comparison

    if (!in_array($normalizedSource, $allowedSources)) continue;


    // Count how many of these leads converted
    $emailStmt = $pdo->prepare("
        SELECT COUNT(*) FROM users 
        WHERE email IN (
            SELECT email FROM leads 
            WHERE page_id = ? AND source = ?
        )
    ");
    $emailStmt->execute([$row['page_id'], $row['source']]);
    $converted = $emailStmt->fetchColumn();

    $conversionRate = $row['lead_count'] > 0 ? round(($converted / $row['lead_count']) * 100, 2) : 0;

    $sourceLeaders[$normalizedSource][] = [
        'name' => $row['first_name'] . ' ' . $row['last_name'],
        'headline' => $row['headline'],
        'leads' => $row['lead_count'],
        'signups' => $converted,
        'rate' => $conversionRate
    ];
}
?>

<?php foreach (array_keys($sourceLeaders) as $source): ?>
    <?php if (!empty($sourceLeaders[$source])): ?>
        <h4 style="margin-top: 20px;">
    <?= htmlspecialchars(ucwords(strtolower($source))); ?>
</h4>
        <ul>
            <?php foreach ($sourceLeaders[$source] as $entry): ?>
                <li>
                    <strong><?= htmlspecialchars($entry['name']); ?>:</strong>
                    <?= $entry['leads']; ?> leads,
                    <?= $entry['signups']; ?> signups
                    (<?= $entry['rate']; ?>%)
                </li>
            <?php endforeach; ?>
        </ul>
    <?php endif; ?>
<?php endforeach; ?>

        
<h3>🔍 Capture Page + Source Breakdown (Top 50)</h3>
<table border="1" cellpadding="8" cellspacing="0" style="width:100%; border-collapse: collapse; margin-top: 20px;">
<thead style="background: #f0f0f0;">
    <tr>
        <th>Owner</th>
        <th>Capture Page Headline</th>
        <th>Share Code</th> <!-- ✅ NEW -->
        <th>Source</th>
        <th>Leads</th>
        <th>% of Total</th>
        <th>Converted?</th>
    </tr>
</thead>

    <tbody>
        <?php
        $stmt = $pdo->query("
            SELECT 
                l.page_id,
                l.source,
                COUNT(*) AS lead_count,
                u.first_name,
                u.last_name,
                p.share_code,
                p.headline
            FROM leads l
            JOIN users u ON l.user_id = u.id
            JOIN lead_capture_pages p ON l.page_id = p.id
            GROUP BY l.page_id, l.source
            ORDER BY lead_count DESC
            LIMIT 50
        ");
        $pageSourceCombos = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($pageSourceCombos as $row) {
            $cleanSource = $source_aliases[strtolower($row['source'])] ?? fuzzyGroup($row['source']) ?? $row['source'];
            $percent = $total_leads > 0 ? round(($row['lead_count'] / $total_leads) * 100, 2) : 0;
            
                // NEW: Count how many of this lead group converted
    $emailCheckStmt = $pdo->prepare("
        SELECT COUNT(*) FROM users 
        WHERE email IN (
            SELECT email FROM leads 
            WHERE page_id = ? AND source = ?
        )
    ");
    $emailCheckStmt->execute([$row['page_id'], $row['source']]);
    $convertedCount = $emailCheckStmt->fetchColumn();
            
            echo "<tr>";
            echo "<td>" . htmlspecialchars($row['first_name'] . ' ' . $row['last_name']) . "</td>";
            echo "<td>" . htmlspecialchars($row['headline']) . "</td>";
            echo "<td>" . htmlspecialchars($row['share_code']) . "</td>"; // ✅ NEW
            echo "<td>" . htmlspecialchars($cleanSource) . "</td>";
            
            echo "<td>" . $row['lead_count'] . "</td>";
            echo "<td>" . $percent . "%</td>";
            $conversionRate = $row['lead_count'] > 0 ? round(($convertedCount / $row['lead_count']) * 100, 2) : 0;
echo "<td>" . $convertedCount . " / " . $row['lead_count'] . " (" . $conversionRate . "%)</td>";
 // ← NEW
            echo "</tr>";
        }
        ?>
    </tbody>
</table>



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