wevads-platform/scripts/api_send-workflow-api.php

<?php
/**
 * SEND WORKFLOW API
 * Suppression, Warmup, Seeds, Winning, Inbox Monitoring
 */
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');

$db = new PDO("pgsql:host=127.0.0.1;dbname=adx_system", "admin", "admin123");
$dbClients = new PDO("pgsql:host=127.0.0.1;dbname=adx_clients", "admin", "admin123");

$action = $_POST['action'] ?? $_GET['action'] ?? '';
$response = ['success' => false, 'error' => 'Unknown action'];

switch ($action) {

    // ==================== SUPPRESSION (Blacklist) ====================
    case 'run_suppression':
        $config_id = intval($_POST['config_id']);
        
        // Get config
        $config = $db->query("SELECT * FROM admin.send_configs WHERE id = $config_id")->fetch(PDO::FETCH_ASSOC);
        $schema = $config['list_schema'];
        $table = $config['list_table'];
        
        // Count blacklisted emails in the list
        $countBefore = $dbClients->query("SELECT COUNT(*) FROM \"$schema\".\"$table\"")->fetchColumn();
        
        // Get blacklist emails for this ISP
        $blacklistEmails = $db->query("SELECT email FROM admin.blacklists WHERE isp = '{$config['isp']}' OR isp IS NULL")->fetchAll(PDO::FETCH_COLUMN);
        
        if (count($blacklistEmails) > 0) {
            // Create temp table with blacklist
            $dbClients->exec("CREATE TEMP TABLE temp_blacklist (email VARCHAR(255))");
            $stmt = $dbClients->prepare("INSERT INTO temp_blacklist VALUES (?)");
            foreach ($blacklistEmails as $email) {
                $stmt->execute([$email]);
            }
            
            // Delete from list
            $deleted = $dbClients->exec("DELETE FROM \"$schema\".\"$table\" WHERE email IN (SELECT email FROM temp_blacklist)");
            $dbClients->exec("DROP TABLE temp_blacklist");
        } else {
            $deleted = 0;
        }
        
        $countAfter = $dbClients->query("SELECT COUNT(*) FROM \"$schema\".\"$table\"")->fetchColumn();
        
        // Update config
        $db->prepare("UPDATE admin.send_configs SET suppression_done = TRUE, suppression_count = ?, total_recipients = ? WHERE id = ?")
           ->execute([$deleted, $countAfter, $config_id]);
        
        $response = [
            'success' => true,
            'before' => $countBefore,
            'after' => $countAfter,
            'suppressed' => $deleted,
            'blacklist_size' => count($blacklistEmails)
        ];
        break;
    
    case 'add_to_blacklist':
        $emails = $_POST['emails'] ?? ''; // comma separated or array
        $source = $_POST['source'] ?? 'manual';
        $isp = $_POST['isp'] ?? null;
        
        if (is_string($emails)) {
            $emails = array_map('trim', explode(',', $emails));
        }
        
        $added = 0;
        $stmt = $db->prepare("INSERT INTO admin.blacklists (email, source, isp) VALUES (?, ?, ?) ON CONFLICT (email) DO NOTHING");
        foreach ($emails as $email) {
            if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $stmt->execute([$email, $source, $isp]);
                $added++;
            }
        }
        
        $response = ['success' => true, 'added' => $added];
        break;
    
    case 'get_blacklist_stats':
        $stats = $db->query("SELECT 
            COUNT(*) as total,
            COUNT(CASE WHEN source = 'bounce' THEN 1 END) as bounces,
            COUNT(CASE WHEN source = 'complaint' THEN 1 END) as complaints,
            COUNT(CASE WHEN source = 'unsubscribe' THEN 1 END) as unsubscribes,
            COUNT(CASE WHEN source = 'manual' THEN 1 END) as manual
            FROM admin.blacklists")->fetch(PDO::FETCH_ASSOC);
        $response = ['success' => true, 'stats' => $stats];
        break;

    // ==================== WARMUP ====================
    case 'get_warmup_status':
        $type = $_GET['type'] ?? 'all'; // ip, domain, account, all
        
        $sql = "SELECT * FROM admin.warmup_status";
        if ($type !== 'all') {
            $sql .= " WHERE type = '$type'";
        }
        $sql .= " ORDER BY warmup_day DESC, inbox_rate DESC";
        
        $warmups = $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $response = ['success' => true, 'warmups' => $warmups];
        break;
    
    case 'update_warmup':
        $id = intval($_POST['id']);
        $volume_sent = intval($_POST['volume_sent'] ?? 0);
        $inbox_rate = floatval($_POST['inbox_rate'] ?? 0);
        $spam_rate = floatval($_POST['spam_rate'] ?? 0);
        
        // Update stats
        $db->prepare("UPDATE admin.warmup_status SET 
            current_volume = current_volume + ?,
            inbox_rate = ?,
            spam_rate = ?,
            last_send = NOW(),
            reputation_score = GREATEST(0, reputation_score - CASE WHEN ? > 20 THEN 10 WHEN ? > 10 THEN 5 ELSE 0 END)
            WHERE id = ?")
           ->execute([$volume_sent, $inbox_rate, $spam_rate, $spam_rate, $spam_rate, $id]);
        
        // Check if burned (spam > 30%)
        if ($spam_rate > 30) {
            $db->prepare("UPDATE admin.warmup_status SET status = 'burned' WHERE id = ?")->execute([$id]);
        }
        
        // Progress warmup day
        $warmup = $db->query("SELECT * FROM admin.warmup_status WHERE id = $id")->fetch(PDO::FETCH_ASSOC);
        if ($warmup['current_volume'] >= $warmup['daily_limit']) {
            $newDay = $warmup['warmup_day'] + 1;
            $newLimit = min($warmup['max_volume'], $warmup['daily_limit'] * 1.5); // +50% par jour
            
            $db->prepare("UPDATE admin.warmup_status SET warmup_day = ?, daily_limit = ?, current_volume = 0 WHERE id = ?")
               ->execute([$newDay, $newLimit, $id]);
            
            // Check if ready
            if ($newLimit >= $warmup['max_volume'] && $inbox_rate > 80) {
                $db->prepare("UPDATE admin.warmup_status SET status = 'ready' WHERE id = ?")->execute([$id]);
            }
        }
        
        $response = ['success' => true];
        break;
    
    case 'add_warmup':
        $type = $_POST['type']; // ip, domain, account
        $identifier = $_POST['identifier'];
        $server_id = intval($_POST['server_id'] ?? 0);
        $max_volume = intval($_POST['max_volume'] ?? 50000);
        
        $db->prepare("INSERT INTO admin.warmup_status (type, identifier, server_id, max_volume, daily_limit) 
            VALUES (?, ?, ?, ?, 100) ON CONFLICT (type, identifier) DO NOTHING")
           ->execute([$type, $identifier, $server_id ?: null, $max_volume]);
        
        $response = ['success' => true];
        break;

    // ==================== SEED TESTING ====================
    case 'get_seeds':
        $isp = $_GET['isp'] ?? '';
        $sql = "SELECT * FROM admin.seed_accounts WHERE status = 'active'";
        if ($isp) $sql .= " AND isp = '$isp'";
        $seeds = $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $response = ['success' => true, 'seeds' => $seeds];
        break;
    
    case 'check_seeds':
        $config_id = intval($_POST['config_id']);
        $drop_number = intval($_POST['drop_number'] ?? 1);
        
        // Get seeds for the ISP
        $config = $db->query("SELECT * FROM admin.send_configs WHERE id = $config_id")->fetch(PDO::FETCH_ASSOC);
        $seeds = $db->query("SELECT * FROM admin.seed_accounts WHERE status = 'active' AND isp = '{$config['isp']}'")->fetchAll(PDO::FETCH_ASSOC);
        
        $results = [];
        $inboxCount = 0;
        $spamCount = 0;
        
        foreach ($seeds as $seed) {
            // Connect to IMAP and check
            $folder = checkSeedInbox($seed);
            
            if ($folder == 'inbox') $inboxCount++;
            elseif ($folder == 'spam') $spamCount++;
            
            $results[] = ['seed_id' => $seed['id'], 'email' => $seed['email'], 'folder' => $folder];
            
            // Log result
            $db->prepare("INSERT INTO admin.seed_results (send_config_id, drop_number, seed_id, folder) VALUES (?, ?, ?, ?)")
               ->execute([$config_id, $drop_number, $seed['id'], $folder]);
        }
        
        $total = count($seeds);
        $inboxRate = $total > 0 ? ($inboxCount / $total) * 100 : 0;
        
        // Log monitoring
        $db->prepare("INSERT INTO admin.inbox_monitoring (send_config_id, drop_number, total_inbox, total_spam, inbox_rate) VALUES (?, ?, ?, ?, ?)")
           ->execute([$config_id, $drop_number, $inboxCount, $spamCount, $inboxRate]);
        
        // Check if should auto-stop
        $autoStopped = false;
        if ($config['auto_stop_on_spam'] && $inboxRate < $config['inbox_threshold']) {
            $db->prepare("UPDATE admin.send_configs SET status = 'paused' WHERE id = ?")->execute([$config_id]);
            $db->prepare("INSERT INTO admin.send_alerts (send_config_id, alert_type, message, severity) VALUES (?, 'inbox_drop', ?, 'critical')")
               ->execute([$config_id, "Inbox rate dropped to {$inboxRate}% - Auto-stopped"]);
            $autoStopped = true;
        }
        
        $response = [
            'success' => true,
            'results' => $results,
            'inbox_count' => $inboxCount,
            'spam_count' => $spamCount,
            'inbox_rate' => round($inboxRate, 2),
            'auto_stopped' => $autoStopped
        ];
        break;
    
    case 'add_seed':
        $email = $_POST['email'];
        $password = $_POST['password'];
        $isp = $_POST['isp'];
        
        // Detect IMAP settings
        $imapSettings = [
            'gmail' => ['imap.gmail.com', 993],
            'hotmail' => ['outlook.office365.com', 993],
            'yahoo' => ['imap.mail.yahoo.com', 993],
            'gmx' => ['imap.gmx.com', 993],
        ];
        
        $settings = $imapSettings[$isp] ?? ['imap.' . $isp . '.com', 993];
        
        $db->prepare("INSERT INTO admin.seed_accounts (email, password, isp, imap_host, imap_port) VALUES (?, ?, ?, ?, ?) ON CONFLICT (email) DO NOTHING")
           ->execute([$email, $password, $isp, $settings[0], $settings[1]]);
        
        $response = ['success' => true];
        break;

    // ==================== WINNING CONFIGS ====================
    case 'extract_winning':
        $send_config_id = intval($_POST['send_config_id']);
        
        // Get config with best performance
        $config = $db->query("SELECT c.*, 
            (SELECT AVG(inbox_rate) FROM admin.inbox_monitoring WHERE send_config_id = c.id) as avg_inbox
            FROM admin.send_configs c WHERE c.id = $send_config_id")->fetch(PDO::FETCH_ASSOC);
        
        if ($config['avg_inbox'] >= 70) {
            $db->prepare("INSERT INTO admin.winning_configs 
                (name, isp, country, inbox_rate, source_send_id)
                VALUES (?, ?, ?, ?, ?)")
               ->execute([
                   "Winner_{$config['isp']}_{$config['country']}_" . date('Ymd'),
                   $config['isp'],
                   $config['country'],
                   $config['avg_inbox'],
                   $send_config_id
               ]);
            
            $response = ['success' => true, 'inbox_rate' => $config['avg_inbox']];
        } else {
            $response = ['success' => false, 'error' => 'Inbox rate too low: ' . $config['avg_inbox']];
        }
        break;
    
    case 'get_winning_configs':
        $isp = $_GET['isp'] ?? '';
        $sql = "SELECT * FROM admin.winning_configs WHERE status = 'active'";
        if ($isp) $sql .= " AND isp = '$isp'";
        $sql .= " ORDER BY inbox_rate DESC";
        
        $configs = $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $response = ['success' => true, 'configs' => $configs];
        break;
    
    case 'apply_winning_config':
        $config_id = intval($_POST['config_id']);
        $winning_id = intval($_POST['winning_id']);
        
        $db->prepare("UPDATE admin.send_configs SET winning_config_id = ? WHERE id = ?")->execute([$winning_id, $config_id]);
        $db->prepare("UPDATE admin.winning_configs SET usage_count = usage_count + 1 WHERE id = ?")->execute([$winning_id]);
        
        $response = ['success' => true];
        break;

    // ==================== INBOX MONITORING (Real-time) ====================
    case 'monitor_inbox_realtime':
        $config_id = intval($_POST['config_id']);
        $drop_number = intval($_POST['drop_number'] ?? 1);
        
        // Get current inbox stats
        $stats = $db->query("SELECT * FROM admin.inbox_monitoring 
            WHERE send_config_id = $config_id AND drop_number = $drop_number 
            ORDER BY check_time DESC LIMIT 1")->fetch(PDO::FETCH_ASSOC);
        
        // Get config threshold
        $config = $db->query("SELECT * FROM admin.send_configs WHERE id = $config_id")->fetch(PDO::FETCH_ASSOC);
        
        $shouldStop = false;
        $alert = null;
        
        if ($stats && $stats['inbox_rate'] < $config['inbox_threshold']) {
            $shouldStop = true;
            $alert = "Inbox rate dropped from previous to {$stats['inbox_rate']}%";
            
            // Auto-stop if enabled
            if ($config['auto_stop_on_spam'] && !$stats['auto_stopped']) {
                $db->prepare("UPDATE admin.send_configs SET status = 'paused' WHERE id = ?")->execute([$config_id]);
                $db->prepare("UPDATE admin.inbox_monitoring SET auto_stopped = TRUE WHERE id = ?")->execute([$stats['id']]);
                
                // Stop servers
                $serverIds = trim($config['server_ids'], '{}');
                if ($serverIds) {
                    $db->exec("UPDATE mta.servers SET provider_status = 'stopped' WHERE id IN ($serverIds)");
                }
                
                // Create alert
                $db->prepare("INSERT INTO admin.send_alerts (send_config_id, alert_type, message, severity) VALUES (?, 'spam_detected', ?, 'critical')")
                   ->execute([$config_id, $alert]);
            }
        }
        
        $response = [
            'success' => true,
            'stats' => $stats,
            'threshold' => $config['inbox_threshold'],
            'should_stop' => $shouldStop,
            'alert' => $alert,
            'auto_stopped' => $stats['auto_stopped'] ?? false
        ];
        break;

    // ==================== S3 INTEGRATION ====================
    case 'upload_to_s3':
        $sponsor_id = intval($_POST['sponsor_id']);
        $file = $_FILES['file'] ?? null;
        
        if (!$file) {
            $response = ['success' => false, 'error' => 'No file'];
            break;
        }
        
        // Get sponsor S3 config
        $sponsor = $db->query("SELECT * FROM admin.sponsors WHERE id = $sponsor_id")->fetch(PDO::FETCH_ASSOC);
        
        if (!$sponsor['s3_bucket']) {
            $response = ['success' => false, 'error' => 'No S3 configured for sponsor'];
            break;
        }
        
        // Upload using AWS SDK (simplified)
        $s3Key = "images/" . date('Y/m/') . uniqid() . "_" . $file['name'];
        $s3Url = "https://{$sponsor['s3_bucket']}.s3.{$sponsor['s3_region']}.amazonaws.com/$s3Key";
        
        // In production, use AWS SDK:
        // $s3Client->putObject(['Bucket' => $sponsor['s3_bucket'], 'Key' => $s3Key, 'Body' => file_get_contents($file['tmp_name'])]);
        
        // Log asset
        $db->prepare("INSERT INTO admin.s3_assets (sponsor_id, asset_type, filename, s3_key, s3_url) VALUES (?, ?, ?, ?, ?)")
           ->execute([$sponsor_id, 'image', $file['name'], $s3Key, $s3Url]);
        
        $response = ['success' => true, 's3_url' => $s3Url, 's3_key' => $s3Key];
        break;
    
    case 'get_s3_assets':
        $sponsor_id = intval($_GET['sponsor_id']);
        $assets = $db->query("SELECT * FROM admin.s3_assets WHERE sponsor_id = $sponsor_id ORDER BY uploaded_at DESC")->fetchAll(PDO::FETCH_ASSOC);
        $response = ['success' => true, 'assets' => $assets];
        break;

    // ==================== COMPLETE WORKFLOW ====================
    case 'pre_send_checklist':
        $config_id = intval($_GET['config_id']);
        
        $config = $db->query("SELECT * FROM admin.send_configs WHERE id = $config_id")->fetch(PDO::FETCH_ASSOC);
        
        $checklist = [
            'suppression_done' => $config['suppression_done'],
            'servers_assigned' => !empty($config['server_ids']),
            'seeds_available' => $db->query("SELECT COUNT(*) FROM admin.seed_accounts WHERE status = 'active' AND isp = '{$config['isp']}'")->fetchColumn() > 0,
            'warmup_ready' => true, // Check warmup status
            'winning_config' => !empty($config['winning_config_id']),
            'tracking_configured' => !empty($config['tracking_domain']),
            'images_uploaded' => !empty($config['image_url']),
        ];
        
        $allReady = !in_array(false, $checklist);
        
        $response = ['success' => true, 'checklist' => $checklist, 'all_ready' => $allReady];
        break;
}

// Helper function to check seed inbox
function checkSeedInbox($seed) {
    // Simplified - in production use IMAP
    try {
        $inbox = @imap_open(
            "{{$seed['imap_host']}:{$seed['imap_port']}/imap/ssl}INBOX", 
            $seed['email'], 
            $seed['password']
        );
        
        if ($inbox) {
            // Search for recent emails
            $emails = imap_search($inbox, 'SINCE "' . date('d-M-Y', strtotime('-1 hour')) . '"');
            imap_close($inbox);
            return $emails ? 'inbox' : 'not_found';
        }
        
        // Check spam folder
        $spam = @imap_open(
            "{{$seed['imap_host']}:{$seed['imap_port']}/imap/ssl}[Gmail]/Spam", 
            $seed['email'], 
            $seed['password']
        );
        
        if ($spam) {
            $emails = imap_search($spam, 'SINCE "' . date('d-M-Y', strtotime('-1 hour')) . '"');
            imap_close($spam);
            return $emails ? 'spam' : 'not_found';
        }
    } catch (Exception $e) {
        return 'error';
    }
    
    return 'not_found';
}

echo json_encode($response);