246 lines
12 KiB
PHP
Executable File
246 lines
12 KiB
PHP
Executable File
#!/usr/bin/env php
|
|
<?php
|
|
/**
|
|
* BRAIN PIPELINE V2 - Graph API + Tracking Pixel
|
|
* Replaces SMTP basic auth with OAuth2 Graph API sendMail
|
|
* Replaces IMAP check with tracking pixel verification
|
|
*
|
|
* Cron: every-30-min * * * * php /opt/wevads/scripts/brain-pipeline.php >> /var/log/wevads/brain-pipeline.log 2>&1
|
|
*/
|
|
$LOG = fopen('/var/log/wevads/brain-pipeline.log', 'a');
|
|
function logMsg($m) { global $LOG; $t = date('Y-m-d H:i:s'); fwrite($LOG, "[$t] $m\n"); echo "[$t] $m\n"; }
|
|
|
|
$pdo = new PDO("pgsql:host=localhost;dbname=adx_system", "admin", "admin123");
|
|
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
|
|
require_once '/opt/wevads/scripts/graph-sender-lib.php';
|
|
$graph = new GraphSender($pdo);
|
|
|
|
$phase = $argv[1] ?? 'full';
|
|
logMsg("=== BRAIN PIPELINE V2 START (phase=$phase) ===");
|
|
|
|
$TRACKING_DOMAIN = 'culturellemejean.charity';
|
|
|
|
// ============================================================
|
|
// PHASE 1: SEND TEST EMAILS via Graph API + Tracking Pixel
|
|
// ============================================================
|
|
if (in_array($phase, ['full', 'send'])) {
|
|
logMsg("--- PHASE 1: GRAPH API SEND ---");
|
|
|
|
$jobId = 'PIPE_' . date('YmdHis');
|
|
$maxTests = 20;
|
|
|
|
// Pick configs to test (least-tested first)
|
|
$configs = $pdo->query("
|
|
SELECT * FROM admin.brain_send_configs
|
|
WHERE status='active'
|
|
ORDER BY COALESCE(total_sent,0) ASC, RANDOM()
|
|
LIMIT $maxTests
|
|
")->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
if (empty($configs)) { logMsg("No active configs"); }
|
|
|
|
$pdo->prepare("INSERT INTO admin.brain_test_jobs (job_id, status, total_configs, started_at, created_at) VALUES (?,?,?,NOW(),NOW())")
|
|
->execute([$jobId, 'running', count($configs)]);
|
|
|
|
// Get active seeds (or use yacine's email as fallback)
|
|
$seeds = $pdo->query("SELECT id, email, isp FROM admin.brain_seeds WHERE is_active=true ORDER BY RANDOM() LIMIT 50")->fetchAll(PDO::FETCH_ASSOC);
|
|
if (empty($seeds)) {
|
|
$seeds = [['id'=>56183, 'email'=>'yacine.mahboub@gmail.com', 'isp'=>'GMAIL']];
|
|
}
|
|
|
|
$ispMapping = [
|
|
'T-ONLINE'=>['T-ONLINE','TONLINE'], 'GMX'=>['GMX','WEBDE'], 'GMAIL'=>['GMAIL','GOOGLE'],
|
|
'OUTLOOK'=>['HOTMAIL','MICROSOFT','OUTLOOK'], 'ZIGGO'=>['ZIGGO'], 'YAHOO'=>['YAHOO'],
|
|
'ORANGE'=>['ORANGE'], 'SFR'=>['SFR'], 'FREE'=>['FREE'], 'AOL'=>['AOL'],
|
|
];
|
|
|
|
$sent = 0; $failed = 0;
|
|
|
|
foreach ($configs as $c) {
|
|
$ispTarget = strtoupper($c['isp_target'] ?? 'ALL');
|
|
$targetIsps = $ispMapping[$ispTarget] ?? [$ispTarget];
|
|
|
|
// Find matching seed
|
|
$seed = null;
|
|
foreach ($seeds as $s) {
|
|
if (in_array(strtoupper($s['isp']), $targetIsps)) { $seed = $s; break; }
|
|
}
|
|
if (!$seed) $seed = $seeds[array_rand($seeds)]; // fallback to random
|
|
|
|
// Generate tracking code
|
|
$trackCode = 'BT' . $c['id'] . '_' . bin2hex(random_bytes(4));
|
|
$trackPixel = "<img src=\"https://{$TRACKING_DOMAIN}/t/{$trackCode}\" width=\"1\" height=\"1\" style=\"display:none\">";
|
|
|
|
// Use real affiliate creative + subject $offerRow = $pdo->query("SELECT o.id, o.name, s.value AS subj, c.value AS creative FROM affiliate.offers o JOIN affiliate.subjects s ON s.offer_id=o.id JOIN affiliate.creatives c ON c.offer_id=o.id ORDER BY RANDOM() LIMIT 1")->fetch(PDO::FETCH_ASSOC); $subject = $offerRow ? $offerRow["subj"] : "Exclusive Update " . date("H:i"); $body = $offerRow ? base64_decode($offerRow["creative"]) : "<html><body><p>Special offer</p>{$trackPixel}</body></html>"; if ($offerRow) $body = str_replace("[open]", "https://{$TRACKING_DOMAIN}/t/{$trackCode}", $body);
|
|
|
|
// Custom headers from config
|
|
$headers = [];
|
|
if (!empty($c['x_mailer'])) $headers['X-Mailer'] = $c['x_mailer'];
|
|
if (!empty($c['custom_headers'])) {
|
|
$ch = json_decode($c['custom_headers'], true);
|
|
if (is_array($ch)) $headers = array_merge($headers, $ch);
|
|
}
|
|
|
|
// Send via Graph API
|
|
$result = $graph->sendTest($seed['email'], $subject, $body, $headers);
|
|
|
|
// Log to brain_test_results
|
|
$pdo->prepare("INSERT INTO admin.brain_test_results (job_id, config_id, seed_id, send_status, send_time, subject_used, message_id, error_message, config_type, created_at) VALUES (?,?,?,?,NOW(),?,?,?,?,NOW())")
|
|
->execute([$jobId, $c['id'], $seed['id'], $result['success']?'sent':'failed', $subject, $trackCode, $result['error'], 'graph_api']);
|
|
|
|
// Also log to seed_tracking for pixel verification
|
|
if ($result['success']) {
|
|
$pdo->prepare("INSERT INTO admin.seed_tracking (seed_id, seed_email, seed_isp, sender_email, tracking_code, subject_line, sent_at, delivered) VALUES (?,?,?,?,?,?,NOW(),true) ON CONFLICT DO NOTHING")
|
|
->execute([$seed['id'], $seed['email'], $seed['isp'], $result['sender'], $trackCode, $subject]);
|
|
|
|
$sent++;
|
|
logMsg(" GRAPH OK config#{$c['id']} {$c['isp_target']} → {$seed['email']} (via {$result['tenant']}/{$result['sender']})");
|
|
} else {
|
|
$failed++;
|
|
logMsg(" GRAPH FAIL config#{$c['id']}: {$result['error']}");
|
|
}
|
|
|
|
usleep(300000); // 300ms between sends
|
|
}
|
|
|
|
$pdo->prepare("UPDATE admin.brain_test_jobs SET status='completed', tested_count=?, inbox_count=?, completed_at=NOW() WHERE job_id=?")
|
|
->execute([$sent+$failed, $sent, $jobId]);
|
|
logMsg("PHASE 1 DONE: $sent sent, $failed failed (job=$jobId)");
|
|
}
|
|
|
|
// ============================================================
|
|
// PHASE 2: TRACKING PIXEL VERIFICATION
|
|
// ============================================================
|
|
if (in_array($phase, ['full', 'check'])) {
|
|
logMsg("--- PHASE 2: TRACKING PIXEL CHECK ---");
|
|
|
|
// Check brain_test_results where send_status='sent' and inbox_status is null
|
|
// Match against seed_tracking where opened_at is not null (pixel was loaded = inbox)
|
|
$tests = $pdo->query("
|
|
SELECT tr.id, tr.config_id, tr.seed_id, tr.message_id as track_code, tr.send_time
|
|
FROM admin.brain_test_results tr
|
|
WHERE tr.send_status = 'sent'
|
|
AND tr.inbox_status IS NULL
|
|
AND tr.send_time > NOW() - INTERVAL '48 hours'
|
|
AND tr.message_id IS NOT NULL AND tr.message_id LIKE 'BT%'
|
|
ORDER BY tr.send_time DESC
|
|
LIMIT 50
|
|
")->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$inbox = 0; $spam = 0; $pending = 0;
|
|
|
|
foreach ($tests as $t) {
|
|
// Check if tracking pixel was loaded (= opened = inbox)
|
|
$st = $pdo->prepare("SELECT opened_at, inbox_placement FROM admin.seed_tracking WHERE tracking_code=?");
|
|
$st->execute([$t['track_code']]);
|
|
$track = $st->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if ($track && $track['opened_at']) {
|
|
// Pixel loaded = inbox
|
|
$pdo->exec("UPDATE admin.brain_test_results SET inbox_status='inbox', checked_at=NOW() WHERE id={$t['id']}");
|
|
$inbox++;
|
|
} elseif ($track && $track['inbox_placement'] === 'spam') {
|
|
$pdo->exec("UPDATE admin.brain_test_results SET inbox_status='spam', checked_at=NOW() WHERE id={$t['id']}");
|
|
$spam++;
|
|
} else {
|
|
// Check if enough time has passed (>6h without open = likely spam)
|
|
$sendTime = strtotime($t['send_time']);
|
|
if (time() - $sendTime > 21600) { // 6 hours
|
|
$pdo->exec("UPDATE admin.brain_test_results SET inbox_status='spam', checked_at=NOW() WHERE id={$t['id']}");
|
|
$spam++;
|
|
} else {
|
|
$pending++;
|
|
}
|
|
}
|
|
}
|
|
|
|
logMsg("PHASE 2 DONE: inbox=$inbox spam=$spam pending=$pending (total checked=" . count($tests) . ")");
|
|
}
|
|
|
|
// ============================================================
|
|
// PHASE 3: SCORE CONFIGS + PROMOTE WINNERS (unchanged logic)
|
|
// ============================================================
|
|
if (in_array($phase, ['full', 'score'])) {
|
|
logMsg("--- PHASE 3: SCORE & PROMOTE ---");
|
|
|
|
$scored = $pdo->exec("
|
|
UPDATE admin.brain_send_configs SET
|
|
inbox_rate = sub.rate, total_sent = sub.total, updated_at = NOW()
|
|
FROM (
|
|
SELECT config_id,
|
|
ROUND(COUNT(CASE WHEN inbox_status='inbox' THEN 1 END)::numeric / NULLIF(COUNT(CASE WHEN inbox_status IN ('inbox','spam') THEN 1 END), 0) * 100, 2) as rate,
|
|
COUNT(*) as total
|
|
FROM admin.brain_test_results
|
|
WHERE send_status='sent' AND inbox_status IS NOT NULL
|
|
GROUP BY config_id
|
|
) sub
|
|
WHERE admin.brain_send_configs.id = sub.config_id
|
|
");
|
|
|
|
// Promote winners (>=85% inbox with 3+ tests)
|
|
$winners = $pdo->query("
|
|
SELECT bsc.id, bsc.send_method, bsc.isp_target, bsc.x_mailer, bsc.content_type, bsc.inbox_rate,
|
|
COUNT(tr.id) as total_tests
|
|
FROM admin.brain_send_configs bsc
|
|
JOIN admin.brain_test_results tr ON tr.config_id = bsc.id AND tr.send_status='sent' AND tr.inbox_status IS NOT NULL
|
|
WHERE bsc.inbox_rate >= 85
|
|
GROUP BY bsc.id HAVING COUNT(tr.id) >= 3
|
|
")->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$promoted = 0;
|
|
foreach ($winners as $w) {
|
|
$stmt = $pdo->prepare("INSERT INTO admin.brain_winners (config_id, isp_target, inbox_rate, total_tests, stability_score, confidence_level, is_active, verified_at, created_at)
|
|
VALUES (?,?,?,?,80,'high',true,NOW(),NOW()) ON CONFLICT DO NOTHING");
|
|
$stmt->execute([$w['id'], $w['isp_target'], $w['inbox_rate'], $w['total_tests']]);
|
|
if ($stmt->rowCount()) {
|
|
$promoted++;
|
|
// Also insert to public.brain_winners for UI
|
|
$pdo->prepare("INSERT INTO public.brain_winners (send_method, from_name, from_domain, x_mailer, content_type, inbox_rate, total_tests, target_isp, created_at) VALUES (?,'Newsletter','graph_api',?,?,?,?,?,NOW()) ON CONFLICT DO NOTHING")
|
|
->execute([$w['send_method'], $w['x_mailer']??'', $w['content_type']??'text/html', $w['inbox_rate'], $w['total_tests'], $w['isp_target']]);
|
|
}
|
|
}
|
|
|
|
// Update ISP profiles with success rates
|
|
$pdo->exec("
|
|
UPDATE admin.brain_isp_profiles SET success_rate = sub.rate, last_updated = NOW()
|
|
FROM (
|
|
SELECT UPPER(bsc.isp_target) as isp,
|
|
ROUND(AVG(bsc.inbox_rate)::numeric, 2) as rate
|
|
FROM admin.brain_send_configs bsc
|
|
WHERE bsc.inbox_rate IS NOT NULL AND bsc.inbox_rate > 0
|
|
GROUP BY UPPER(bsc.isp_target)
|
|
) sub
|
|
WHERE UPPER(admin.brain_isp_profiles.isp_name) = sub.isp
|
|
");
|
|
|
|
logMsg("PHASE 3 DONE: scored=$scored, new_winners=$promoted");
|
|
}
|
|
|
|
// ============================================================
|
|
// PHASE 4: MAINTENANCE
|
|
// ============================================================
|
|
if (in_array($phase, ['full', 'maintain'])) {
|
|
logMsg("--- PHASE 4: MAINTENANCE ---");
|
|
|
|
// Reset daily tenant counters at midnight
|
|
if (date('H') == '00') $graph->resetDailyCounters();
|
|
|
|
// Clean old test results (>30 days)
|
|
$cleaned = $pdo->exec("DELETE FROM admin.brain_test_results WHERE created_at < NOW() - INTERVAL '30 days'");
|
|
|
|
// Clean old jobs
|
|
$pdo->exec("DELETE FROM admin.brain_test_jobs WHERE created_at < NOW() - INTERVAL '30 days'");
|
|
|
|
// Update brain_learning_log
|
|
$stats = $pdo->query("SELECT COUNT(*) as total, SUM(CASE WHEN inbox_status='inbox' THEN 1 ELSE 0 END) as inbox, SUM(CASE WHEN inbox_status='spam' THEN 1 ELSE 0 END) as spam FROM admin.brain_test_results WHERE created_at > NOW() - INTERVAL '24 hours'")->fetch();
|
|
$pdo->prepare("INSERT INTO admin.brain_learning_log (event_type, data, impact_score, created_at) VALUES ('daily_stats',?,5,NOW())")
|
|
->execute([json_encode($stats)]);
|
|
|
|
logMsg("PHASE 4 DONE: cleaned=$cleaned old results");
|
|
}
|
|
|
|
logMsg("=== BRAIN PIPELINE V2 COMPLETE ===\n");
|
|
fclose($LOG);
|
|
|