91 lines
3.9 KiB
Plaintext
Executable File
91 lines
3.9 KiB
Plaintext
Executable File
<?php
|
|
// WEVIA Life Email Sync - Direct IMAP to DB
|
|
// Runs via cron every 30 min
|
|
set_time_limit(120);
|
|
$pdo = new PDO("pgsql:host=127.0.0.1;dbname=wevia_db;options='--search_path=admin,public'", "postgres", "");
|
|
$pdo->exec("SET search_path TO admin,public");
|
|
|
|
$sourcesFile = '/var/www/weval/wevia-ia/wevialife-data/email-sources.json';
|
|
$sources = json_decode(@file_get_contents($sourcesFile) ?: '[]', true) ?: [];
|
|
$log = function($msg) { echo date('H:i:s')." $msg\n"; };
|
|
|
|
$inserted = 0;
|
|
$skipped = 0;
|
|
|
|
foreach ($sources as $s) {
|
|
if (($s['status'] ?? '') !== 'active') continue;
|
|
$pwd = base64_decode($s['password'] ?? '');
|
|
$server = $s['server'] ?? 'server105.web-hosting.com';
|
|
$port = $s['port'] ?? 993;
|
|
$email = $s['email'];
|
|
|
|
foreach (['INBOX'] as $folder) {
|
|
$mb = "{{$server}:{$port}/imap/ssl/novalidate-cert}{$folder}";
|
|
$conn = @imap_open($mb, $email, $pwd, 0, 1);
|
|
if (!$conn) { $log("FAIL $email: ".imap_last_error()); continue; }
|
|
|
|
$info = imap_check($conn);
|
|
$total = $info->Nmsgs;
|
|
$start = max(1, $total - 99); // last 100
|
|
$ov = imap_fetch_overview($conn, "$start:$total", 0);
|
|
|
|
// Get existing UIDs
|
|
$existingUids = [];
|
|
$stmt = $pdo->query("SELECT uid FROM email_classifications WHERE folder='$folder'");
|
|
foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $u) $existingUids[$u] = true;
|
|
|
|
$ins = $pdo->prepare("INSERT INTO email_classifications
|
|
(uid, folder, from_email, from_name, subject, received_at, category, eisenhower_quadrant, summary, resolved)
|
|
VALUES (?,?,?,?,?,?,?,?,?,false) ON CONFLICT DO NOTHING");
|
|
|
|
foreach (array_reverse($ov ?: []) as $o) {
|
|
$uid = $o->uid;
|
|
if (isset($existingUids[$uid])) { $skipped++; continue; }
|
|
|
|
$from = isset($o->from) ? mb_decode_mimeheader($o->from) : '';
|
|
$subj = isset($o->subject) ? mb_decode_mimeheader($o->subject) : '';
|
|
$date = isset($o->date) ? date('Y-m-d H:i:s', strtotime($o->date)) : null;
|
|
|
|
// Extract email from "Name <email>" format
|
|
preg_match('/<([^>]+)>/', $from, $m);
|
|
$fromEmail = $m[1] ?? $from;
|
|
$fromName = trim(preg_replace('/<[^>]+>/', '', $from));
|
|
|
|
// Quick auto-classify
|
|
$cat = 'unknown';
|
|
$quad = 'schedule';
|
|
$summary = $subj;
|
|
$subjLow = strtolower($subj);
|
|
$fromLow = strtolower($from);
|
|
|
|
if (preg_match('/noreply|no-reply|newsletter|unsubscribe|notification/i', $fromLow.$subjLow)) {
|
|
$cat = 'newsletter'; $quad = 'eliminate';
|
|
} elseif (preg_match('/invoice|payment|billing|facture/i', $subjLow)) {
|
|
$cat = 'transactional'; $quad = 'schedule';
|
|
} elseif (preg_match('/urgent|asap|immediately|critical/i', $subjLow)) {
|
|
$cat = 'action_required'; $quad = 'do_first';
|
|
} elseif (preg_match('/proposal|partnership|offer|opportunity/i', $subjLow)) {
|
|
$cat = 'opportunity'; $quad = 'schedule';
|
|
} elseif (preg_match('/meeting|invitation|rdv|calendar/i', $subjLow)) {
|
|
$cat = 'meeting'; $quad = 'schedule';
|
|
} else {
|
|
$cat = 'fyi'; $quad = 'schedule';
|
|
}
|
|
|
|
$ins->execute([$uid, $folder, $fromEmail, $fromName, $subj, $date, $cat, $quad, $summary]);
|
|
$inserted++;
|
|
}
|
|
imap_close($conn);
|
|
$log("$email/$folder: total=$total, inserted=$inserted, skipped=$skipped");
|
|
}
|
|
}
|
|
|
|
$log("SYNC DONE: +$inserted new, $skipped existing");
|
|
|
|
// Update stats
|
|
$stats = $pdo->query("SELECT COUNT(*) as total,
|
|
SUM(CASE WHEN eisenhower_quadrant='do_first' THEN 1 ELSE 0 END) as urgent,
|
|
MAX(received_at) as latest
|
|
FROM email_classifications")->fetch(PDO::FETCH_ASSOC);
|
|
$log("DB: {$stats['total']} total, {$stats['urgent']} urgent, latest={$stats['latest']}");
|