wevads-platform/scripts/api_lead-validator.php

<?php
/**
 * LEAD VALIDATOR API
 */
header('Content-Type: application/json');

$pdo = new PDO('pgsql:host=localhost;dbname=adx_system', 'admin', 'admin123');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$action = $_GET['action'] ?? $_POST['action'] ?? '';

switch ($action) {

    case 'validate_batch':
        $limit = $_GET['limit'] ?? 50;
        
        $leads = $pdo->query("SELECT id, email FROM admin.harvested_leads WHERE validation_status IS NULL OR validation_status = 'pending' ORDER BY collected_at DESC LIMIT $limit")->fetchAll(PDO::FETCH_ASSOC);
        
        $results = ['validated' => 0, 'valid' => 0, 'invalid' => 0, 'suppressed' => 0, 'errors' => 0];
        
        foreach ($leads as $lead) {
            $validation = validateEmail($lead['email'], $pdo);
            
            $stmt = $pdo->prepare("UPDATE admin.harvested_leads SET 
                validation_status = ?,
                validation_score = ?,
                is_suppressed = ?,
                mx_valid = ?,
                smtp_valid = ?,
                validated_at = NOW()
                WHERE id = ?");
            $stmt->execute([
                $validation['status'],
                $validation['score'],
                $validation['is_suppressed'] ? 't' : 'f',
                $validation['mx_valid'] ? 't' : 'f',
                $validation['smtp_valid'] ? 't' : 'f',
                $lead['id']
            ]);
            
            $results['validated']++;
            if ($validation['status'] === 'valid') $results['valid']++;
            elseif ($validation['status'] === 'invalid') $results['invalid']++;
            elseif ($validation['status'] === 'suppressed') $results['suppressed']++;
            else $results['errors']++;
        }
        
        echo json_encode(['success' => true, 'results' => $results]);
        break;

    case 'validate_email':
        $email = $_GET['email'] ?? '';
        if (empty($email)) {
            echo json_encode(['success' => false, 'error' => 'Email required']);
            exit;
        }
        $result = validateEmail($email, $pdo);
        echo json_encode(['success' => true, 'result' => $result]);
        break;

    case 'stats':
        $stats = [
            'total' => $pdo->query("SELECT COUNT(*) FROM admin.harvested_leads")->fetchColumn() ?: 0,
            'validated' => $pdo->query("SELECT COUNT(*) FROM admin.harvested_leads WHERE validation_status IS NOT NULL AND validation_status != 'pending'")->fetchColumn() ?: 0,
            'valid' => $pdo->query("SELECT COUNT(*) FROM admin.harvested_leads WHERE validation_status = 'valid'")->fetchColumn() ?: 0,
            'invalid' => $pdo->query("SELECT COUNT(*) FROM admin.harvested_leads WHERE validation_status = 'invalid'")->fetchColumn() ?: 0,
            'suppressed' => $pdo->query("SELECT COUNT(*) FROM admin.harvested_leads WHERE is_suppressed = true")->fetchColumn() ?: 0,
            'pending' => $pdo->query("SELECT COUNT(*) FROM admin.harvested_leads WHERE validation_status IS NULL OR validation_status = 'pending'")->fetchColumn() ?: 0,
            'avg_score' => $pdo->query("SELECT COALESCE(ROUND(AVG(validation_score)::numeric, 1), 0) FROM admin.harvested_leads WHERE validation_score IS NOT NULL")->fetchColumn() ?: 0,
            'by_isp' => $pdo->query("SELECT isp, COUNT(*) as cnt, COALESCE(ROUND(AVG(validation_score)::numeric, 1), 0) as avg_score FROM admin.harvested_leads WHERE validation_score IS NOT NULL GROUP BY isp ORDER BY cnt DESC LIMIT 10")->fetchAll(PDO::FETCH_ASSOC),
        ];
        echo json_encode(['success' => true, 'stats' => $stats]);
        break;

    default:
        echo json_encode(['success' => false, 'error' => 'Invalid action', 'available' => ['validate_batch', 'validate_email', 'stats']]);
}

function validateEmail($email, $pdo) {
    $result = [
        'email' => $email,
        'status' => 'pending',
        'score' => 0,
        'mx_valid' => false,
        'smtp_valid' => false,
        'is_suppressed' => false,
        'checks' => []
    ];
    
    $score = 0;
    
    // 1. Format check
    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $result['status'] = 'invalid';
        $result['checks']['format'] = false;
        return $result;
    }
    $result['checks']['format'] = true;
    $score += 10;
    
    $domain = substr($email, strpos($email, '@') + 1);
    
    // 2. MX check
    $mxHosts = [];
    $mxValid = @getmxrr($domain, $mxHosts);
    $result['mx_valid'] = $mxValid;
    $result['checks']['mx'] = $mxValid;
    
    if (!$mxValid) {
        $result['status'] = 'invalid';
        $result['score'] = $score;
        return $result;
    }
    $score += 30;
    
    // 3. SMTP check (lightweight)
    $smtpValid = checkSMTP($email, $mxHosts[0] ?? $domain);
    $result['smtp_valid'] = $smtpValid;
    $result['checks']['smtp'] = $smtpValid;
    if ($smtpValid) $score += 30;
    
    // 4. Domain score
    $goodDomains = ['gmail.com', 'yahoo.com', 'hotmail.com', 'outlook.com', 'icloud.com', 'aol.com', 't-online.de', 'gmx.de', 'web.de'];
    if (in_array($domain, $goodDomains)) {
        $score += 20;
        $result['checks']['domain'] = 'good';
    } else {
        $score += 5;
        $result['checks']['domain'] = 'unknown';
    }
    
    // 5. Pattern score
    $local = substr($email, 0, strpos($email, '@'));
    if (preg_match('/^(info|admin|support|contact|sales|noreply|no-reply)$/i', $local)) {
        $score -= 10;
        $result['checks']['pattern'] = 'role-based';
    } else {
        $score += 10;
        $result['checks']['pattern'] = 'personal';
    }
    
    // Final
    $result['score'] = min(100, max(0, $score));
    
    if ($result['score'] >= 60 && $result['smtp_valid']) {
        $result['status'] = 'valid';
    } elseif ($result['score'] >= 40) {
        $result['status'] = 'risky';
    } else {
        $result['status'] = 'invalid';
    }
    
    return $result;
}

function checkSMTP($email, $mxHost) {
    $timeout = 3;
    
    try {
        $socket = @fsockopen($mxHost, 25, $errno, $errstr, $timeout);
        if (!$socket) {
            $socket = @fsockopen($mxHost, 587, $errno, $errstr, $timeout);
        }
        
        if (!$socket) return false;
        
        stream_set_timeout($socket, $timeout);
        $response = fgets($socket, 1024);
        if (strpos($response, '220') !== 0) { fclose($socket); return false; }
        
        fwrite($socket, "HELO validator.local\r\n");
        $response = fgets($socket, 1024);
        
        fwrite($socket, "MAIL FROM:<test@validator.local>\r\n");
        $response = fgets($socket, 1024);
        
        fwrite($socket, "RCPT TO:<$email>\r\n");
        $response = fgets($socket, 1024);
        
        fwrite($socket, "QUIT\r\n");
        fclose($socket);
        
        return (strpos($response, '250') === 0 || strpos($response, '251') === 0);
        
    } catch (Exception $e) {
        return false;
    }
}