<?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']}");