374 lines
16 KiB
PHP
Executable File
374 lines
16 KiB
PHP
Executable File
<?php
|
|
/**
|
|
* NEWSLETTER LEARNING - Extraction et analyse des patterns newsletters
|
|
* But: Apprendre ce qui fonctionne pour chaque ISP
|
|
*/
|
|
session_start();
|
|
$pdo = new PDO("pgsql:host=localhost;dbname=adx_system", "admin", "admin123");
|
|
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
|
|
// AJAX Actions
|
|
if (isset($_GET['ajax'])) {
|
|
header('Content-Type: application/json');
|
|
$action = $_GET['ajax'];
|
|
|
|
switch ($action) {
|
|
case 'stats':
|
|
$stats = [
|
|
'patterns' => $pdo->query("SELECT COUNT(*) FROM admin.newsletter_patterns")->fetchColumn(),
|
|
'inbox_patterns' => $pdo->query("SELECT COUNT(*) FROM admin.newsletter_patterns WHERE folder_received = 'INBOX'")->fetchColumn(),
|
|
'spam_patterns' => $pdo->query("SELECT COUNT(*) FROM admin.newsletter_patterns WHERE folder_received = 'SPAM'")->fetchColumn(),
|
|
'isps_covered' => $pdo->query("SELECT COUNT(DISTINCT isp_received) FROM admin.newsletter_patterns")->fetchColumn(),
|
|
'winning_patterns' => $pdo->query("SELECT COUNT(*) FROM admin.isp_winning_patterns WHERE confidence = 'high'")->fetchColumn(),
|
|
'seeds_available' => $pdo->query("SELECT COUNT(*) FROM admin.brain_seeds WHERE is_active = true AND check_status != 'expired'")->fetchColumn()
|
|
];
|
|
echo json_encode(['success' => true, 'stats' => $stats]);
|
|
break;
|
|
|
|
case 'patterns_by_isp':
|
|
$data = $pdo->query("
|
|
SELECT isp_received as isp,
|
|
COUNT(*) as total,
|
|
COUNT(*) FILTER (WHERE folder_received = 'INBOX') as inbox,
|
|
COUNT(*) FILTER (WHERE folder_received = 'SPAM') as spam,
|
|
ROUND(COUNT(*) FILTER (WHERE folder_received = 'INBOX') * 100.0 / NULLIF(COUNT(*), 0), 1) as inbox_rate
|
|
FROM admin.newsletter_patterns
|
|
GROUP BY isp_received
|
|
ORDER BY total DESC
|
|
")->fetchAll(PDO::FETCH_ASSOC);
|
|
echo json_encode(['success' => true, 'data' => $data]);
|
|
break;
|
|
|
|
case 'top_senders':
|
|
$isp = $_GET['isp'] ?? '';
|
|
$folder = $_GET['folder'] ?? 'INBOX';
|
|
$sql = "SELECT sender_email, sender_name, sender_domain, occurrence_count,
|
|
headers_spf, headers_dkim, return_path, last_seen_at
|
|
FROM admin.newsletter_patterns
|
|
WHERE folder_received = ?";
|
|
$params = [$folder];
|
|
if ($isp) {
|
|
$sql .= " AND isp_received = ?";
|
|
$params[] = $isp;
|
|
}
|
|
$sql .= " ORDER BY occurrence_count DESC LIMIT 50";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute($params);
|
|
echo json_encode(['success' => true, 'senders' => $stmt->fetchAll(PDO::FETCH_ASSOC)]);
|
|
break;
|
|
|
|
case 'winning_patterns':
|
|
$isp = $_GET['isp'] ?? '';
|
|
$sql = "SELECT * FROM admin.isp_winning_patterns WHERE confidence IN ('high', 'medium')";
|
|
if ($isp) $sql .= " AND isp = " . $pdo->quote($isp);
|
|
$sql .= " ORDER BY inbox_rate DESC, sample_count DESC";
|
|
$data = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
|
|
echo json_encode(['success' => true, 'patterns' => $data]);
|
|
break;
|
|
|
|
case 'extract_from_seeds':
|
|
// Lancer extraction newsletters depuis seeds actifs
|
|
$isp = $_GET['isp'] ?? '';
|
|
$limit = (int)($_GET['limit'] ?? 5);
|
|
|
|
// Récupérer seeds actifs
|
|
$sql = "SELECT id, email, password, isp, imap_host FROM admin.brain_seeds
|
|
WHERE is_active = true AND check_status != 'expired'";
|
|
if ($isp) $sql .= " AND isp = " . $pdo->quote($isp);
|
|
$sql .= " ORDER BY RANDOM() LIMIT $limit";
|
|
|
|
$seeds = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Lancer extraction en background
|
|
$seedIds = implode(',', array_column($seeds, 'id'));
|
|
exec("php /opt/wevads/scripts/extract_newsletters.php '$seedIds' > /tmp/newsletter_extract.log 2>&1 &");
|
|
|
|
echo json_encode([
|
|
'success' => true,
|
|
'message' => "Extraction lancée pour " . count($seeds) . " seeds",
|
|
'seeds' => array_column($seeds, 'email')
|
|
]);
|
|
break;
|
|
|
|
case 'analyze_patterns':
|
|
// Analyser et générer winning patterns
|
|
$pdo->exec("
|
|
INSERT INTO admin.isp_winning_patterns (isp, pattern_type, pattern_key, pattern_value, inbox_rate, sample_count, confidence)
|
|
SELECT
|
|
isp_received,
|
|
'header',
|
|
'spf',
|
|
headers_spf,
|
|
ROUND(COUNT(*) FILTER (WHERE folder_received = 'INBOX') * 100.0 / COUNT(*), 2),
|
|
COUNT(*),
|
|
CASE
|
|
WHEN COUNT(*) >= 100 THEN 'high'
|
|
WHEN COUNT(*) >= 20 THEN 'medium'
|
|
ELSE 'low'
|
|
END
|
|
FROM admin.newsletter_patterns
|
|
WHERE headers_spf IS NOT NULL
|
|
GROUP BY isp_received, headers_spf
|
|
ON CONFLICT (isp, pattern_type, pattern_key)
|
|
DO UPDATE SET inbox_rate = EXCLUDED.inbox_rate, sample_count = EXCLUDED.sample_count,
|
|
confidence = EXCLUDED.confidence, updated_at = NOW()
|
|
");
|
|
echo json_encode(['success' => true, 'message' => 'Patterns analysés']);
|
|
break;
|
|
}
|
|
exit;
|
|
}
|
|
|
|
// Stats pour affichage
|
|
$totalPatterns = $pdo->query("SELECT COUNT(*) FROM admin.newsletter_patterns")->fetchColumn();
|
|
$inboxPatterns = $pdo->query("SELECT COUNT(*) FROM admin.newsletter_patterns WHERE folder_received = 'INBOX'")->fetchColumn();
|
|
$winningPatterns = $pdo->query("SELECT COUNT(*) FROM admin.isp_winning_patterns WHERE confidence = 'high'")->fetchColumn();
|
|
?>
|
|
<!DOCTYPE html>
|
|
<html lang="en">
|
|
<head>
|
|
<meta charset="UTF-8">
|
|
<title>📚 Newsletter Learning</title>
|
|
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
|
|
<link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.0/font/bootstrap-icons.css" rel="stylesheet">
|
|
<style>
|
|
body { background: linear-gradient(135deg, #1a1a2e 0%, #16213e 100%); color: #e4e4e4; min-height: 100vh; }
|
|
.card { background: rgba(30,41,59,0.9); border: 1px solid #334155; }
|
|
.card-stat { border-left: 4px solid #8b5cf6; }
|
|
.btn-learn { background: linear-gradient(135deg, #8b5cf6, #7c3aed); color: white; border: none; }
|
|
.btn-learn:hover { background: linear-gradient(135deg, #7c3aed, #6d28d9); color: white; }
|
|
.table { color: #e4e4e4; }
|
|
.badge-inbox { background: #10b981; }
|
|
.badge-spam { background: #ef4444; }
|
|
.badge-promo { background: #f59e0b; }
|
|
.pattern-card { background: rgba(139,92,246,0.1); border: 1px solid #8b5cf6; border-radius: 8px; padding: 15px; margin-bottom: 10px; }
|
|
.confidence-high { color: #10b981; }
|
|
.confidence-medium { color: #f59e0b; }
|
|
.confidence-low { color: #6b7280; }
|
|
</style>
|
|
|
|
</head>
|
|
<body>
|
|
<div class="container-fluid py-4">
|
|
<!-- Header -->
|
|
<div class="d-flex justify-content-between align-items-center mb-4">
|
|
<div>
|
|
<h2><i class="bi bi-mortarboard"></i> Newsletter Learning</h2>
|
|
<p class="text-muted mb-0">Extraction et analyse des patterns newsletters pour apprentissage Brain</p>
|
|
</div>
|
|
<div>
|
|
<a href="system-flows.php" class="btn btn-outline-secondary me-2"><i class="bi bi-diagram-3"></i> System Flows</a>
|
|
<a href="brain-manager.php" class="btn btn-outline-info me-2"><i class="bi bi-cpu"></i> Brain Manager</a>
|
|
<button class="btn btn-learn" onclick="extractNewsletters()"><i class="bi bi-cloud-download"></i> Extraire Newsletters</button>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Stats Cards -->
|
|
<div class="row mb-4">
|
|
<div class="col-md-2">
|
|
<div class="card card-stat">
|
|
<div class="card-body text-center">
|
|
<h6 class="text-muted">Patterns Total</h6>
|
|
<h3 id="statTotal"><?= $totalPatterns ?></h3>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-2">
|
|
<div class="card card-stat">
|
|
<div class="card-body text-center">
|
|
<h6 class="text-muted">INBOX</h6>
|
|
<h3 class="text-success" id="statInbox"><?= $inboxPatterns ?></h3>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-2">
|
|
<div class="card card-stat">
|
|
<div class="card-body text-center">
|
|
<h6 class="text-muted">Winning Patterns</h6>
|
|
<h3 class="text-warning" id="statWinning"><?= $winningPatterns ?></h3>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-2">
|
|
<div class="card card-stat">
|
|
<div class="card-body text-center">
|
|
<h6 class="text-muted">ISPs Couverts</h6>
|
|
<h3 id="statIsps">-</h3>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-2">
|
|
<div class="card card-stat">
|
|
<div class="card-body text-center">
|
|
<h6 class="text-muted">Seeds Dispo</h6>
|
|
<h3 id="statSeeds">-</h3>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-2">
|
|
<div class="card card-stat">
|
|
<div class="card-body text-center">
|
|
<button class="btn btn-sm btn-outline-warning w-100" onclick="analyzePatterns()">
|
|
<i class="bi bi-graph-up"></i> Analyser
|
|
</button>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="row">
|
|
<!-- Patterns by ISP -->
|
|
<div class="col-md-4">
|
|
<div class="card mb-4">
|
|
<div class="card-header"><i class="bi bi-pie-chart"></i> Patterns par ISP</div>
|
|
<div class="card-body">
|
|
<table class="table table-sm">
|
|
<thead><tr><th>ISP</th><th>Total</th><th>Inbox</th><th>Rate</th></tr></thead>
|
|
<tbody id="ispTable"></tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Top Senders INBOX -->
|
|
<div class="col-md-4">
|
|
<div class="card mb-4">
|
|
<div class="card-header"><i class="bi bi-inbox"></i> Top Senders INBOX <span class="badge badge-inbox">Apprendre</span></div>
|
|
<div class="card-body" style="max-height:400px;overflow-y:auto">
|
|
<div id="inboxSenders"></div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Winning Patterns -->
|
|
<div class="col-md-4">
|
|
<div class="card mb-4">
|
|
<div class="card-header"><i class="bi bi-trophy"></i> Winning Patterns</div>
|
|
<div class="card-body" style="max-height:400px;overflow-y:auto">
|
|
<div id="winningPatterns"></div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Extraction Controls -->
|
|
<div class="card">
|
|
<div class="card-header"><i class="bi bi-gear"></i> Extraction Newsletters depuis Seeds</div>
|
|
<div class="card-body">
|
|
<div class="row align-items-center">
|
|
<div class="col-md-3">
|
|
<select class="form-select" id="extractIsp">
|
|
<option value="">Tous ISPs</option>
|
|
<option value="gmail">Gmail</option>
|
|
<option value="hotmail">Hotmail</option>
|
|
<option value="yahoo">Yahoo</option>
|
|
<option value="aol">AOL</option>
|
|
</select>
|
|
</div>
|
|
<div class="col-md-2">
|
|
<input type="number" class="form-control" id="extractLimit" value="5" min="1" max="20" placeholder="Nb seeds">
|
|
</div>
|
|
<div class="col-md-3">
|
|
<button class="btn btn-learn w-100" onclick="extractNewsletters()">
|
|
<i class="bi bi-cloud-download"></i> Lancer Extraction
|
|
</button>
|
|
</div>
|
|
<div class="col-md-4">
|
|
<small class="text-muted">Extrait newsletters des seeds pour analyser patterns gagnants</small>
|
|
</div>
|
|
</div>
|
|
<div class="mt-3">
|
|
<pre id="extractLog" style="background:#1e293b;padding:10px;border-radius:5px;max-height:150px;overflow-y:auto;font-size:0.8rem;display:none"></pre>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
|
|
<script>
|
|
function loadStats() {
|
|
fetch('?ajax=stats').then(r => r.json()).then(d => {
|
|
if (d.success) {
|
|
document.getElementById('statTotal').textContent = d.stats.patterns;
|
|
document.getElementById('statInbox').textContent = d.stats.inbox_patterns;
|
|
document.getElementById('statWinning').textContent = d.stats.winning_patterns;
|
|
document.getElementById('statIsps').textContent = d.stats.isps_covered;
|
|
document.getElementById('statSeeds').textContent = d.stats.seeds_available;
|
|
}
|
|
});
|
|
}
|
|
|
|
function loadIspTable() {
|
|
fetch('?ajax=patterns_by_isp').then(r => r.json()).then(d => {
|
|
const html = d.data.map(r => `
|
|
<tr onclick="loadSenders('${r.isp}')" style="cursor:pointer">
|
|
<td><strong>${r.isp || 'N/A'}</strong></td>
|
|
<td>${r.total}</td>
|
|
<td><span class="text-success">${r.inbox}</span>/<span class="text-danger">${r.spam}</span></td>
|
|
<td>${r.inbox_rate || 0}%</td>
|
|
</tr>
|
|
`).join('');
|
|
document.getElementById('ispTable').innerHTML = html || '<tr><td colspan="4">Aucune donnée</td></tr>';
|
|
});
|
|
}
|
|
|
|
function loadSenders(isp = '') {
|
|
fetch(`?ajax=top_senders&isp=${isp}&folder=INBOX`).then(r => r.json()).then(d => {
|
|
const html = d.senders.map(s => `
|
|
<div class="pattern-card">
|
|
<strong>${s.sender_name || s.sender_email}</strong><br>
|
|
<small class="text-muted">${s.sender_domain}</small><br>
|
|
<small>SPF: ${s.headers_spf || '?'} | DKIM: ${s.headers_dkim || '?'}</small><br>
|
|
<small>Vu ${s.occurrence_count}x</small>
|
|
</div>
|
|
`).join('');
|
|
document.getElementById('inboxSenders').innerHTML = html || '<p class="text-muted">Aucun sender</p>';
|
|
});
|
|
}
|
|
|
|
function loadWinningPatterns() {
|
|
fetch('?ajax=winning_patterns').then(r => r.json()).then(d => {
|
|
const html = d.patterns.map(p => `
|
|
<div class="pattern-card">
|
|
<span class="badge bg-secondary">${p.isp}</span>
|
|
<span class="badge bg-info">${p.pattern_type}</span>
|
|
<span class="confidence-${p.confidence}">${p.confidence}</span><br>
|
|
<strong>${p.pattern_key}:</strong> ${p.pattern_value}<br>
|
|
<small>Inbox: ${p.inbox_rate}% (${p.sample_count} samples)</small>
|
|
</div>
|
|
`).join('');
|
|
document.getElementById('winningPatterns').innerHTML = html || '<p class="text-muted">Aucun pattern</p>';
|
|
});
|
|
}
|
|
|
|
function extractNewsletters() {
|
|
const isp = document.getElementById('extractIsp').value;
|
|
const limit = document.getElementById('extractLimit').value;
|
|
|
|
document.getElementById('extractLog').style.display = 'block';
|
|
document.getElementById('extractLog').textContent = 'Lancement extraction...';
|
|
|
|
fetch(`?ajax=extract_from_seeds&isp=${isp}&limit=${limit}`).then(r => r.json()).then(d => {
|
|
document.getElementById('extractLog').textContent = d.message + '\nSeeds: ' + (d.seeds || []).join(', ');
|
|
setTimeout(loadStats, 5000);
|
|
});
|
|
}
|
|
|
|
function analyzePatterns() {
|
|
fetch('?ajax=analyze_patterns').then(r => r.json()).then(d => {
|
|
alert(d.message);
|
|
loadWinningPatterns();
|
|
});
|
|
}
|
|
|
|
// Init
|
|
loadStats();
|
|
loadIspTable();
|
|
loadSenders();
|
|
loadWinningPatterns();
|
|
setInterval(loadStats, 30000);
|
|
</script>
|
|
|
|
</body>
|
|
</html>
|