wevads-platform/scripts/test-creatives.py

#!/usr/bin/env python3
"""
TEST CREATIVES ON SEEDS
Teste toutes les créatives sur les seeds avant envoi massif
Calcule les scores et identifie les winners par ISP
"""

import psycopg2
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import random
import sys
from datetime import datetime

DB_CONFIG = {
    'host': 'localhost',
    'database': 'adx_system',
    'user': 'admin',
    'password': 'admin123'
}

TRACKING_URL = "http://151.80.235.110"
PMTA_HOST = "127.0.0.1"
PMTA_PORT = 25

def get_db():
    return psycopg2.connect(**DB_CONFIG)

def get_office_accounts(conn, limit=50):
    """Get random O365 accounts"""
    cur = conn.cursor()
    cur.execute("""
        SELECT id, admin_email 
        FROM admin.office_accounts 
        WHERE status = 'Active' AND tenant_domain LIKE '%%.onmicrosoft.com'
        ORDER BY RANDOM() LIMIT %s
    """, (limit,))
    return cur.fetchall()

def get_seeds_by_isp(conn, isp, limit=10):
    """Get seeds for specific ISP"""
    cur = conn.cursor()
    
    isp_patterns = {
        'gmail': '%@gmail.com',
        'hotmail': '%@hotmail.%',
        'yahoo': '%@yahoo.%',
        'outlook': '%@outlook.%'
    }
    
    pattern = isp_patterns.get(isp, '%@%')
    
    cur.execute("""
        SELECT id, email FROM admin.brain_seeds 
        WHERE is_active = true AND email LIKE %s
        ORDER BY RANDOM() LIMIT %s
    """, (pattern, limit))
    return cur.fetchall()

def get_creatives_to_test(conn):
    """Get all active creatives"""
    cur = conn.cursor()
    cur.execute("""
        SELECT c.id, c.offer_id, c.subject_line, c.from_name, c.text_body,
               o.name as offer_name, o.payout_amount
        FROM admin.offer_creatives c
        JOIN admin.affiliate_offers o ON c.offer_id = o.id
        WHERE c.status = 'active' AND (o.status = 'active' OR o.is_active = true)
        ORDER BY c.score DESC, c.times_used ASC
    """)
    return cur.fetchall()

def send_email(sender_email, recipient, subject, from_name, body):
    """Send email via PMTA"""
    try:
        msg = MIMEMultipart('alternative')
        msg['Subject'] = subject
        msg['From'] = f"{from_name} <{sender_email}>"
        msg['To'] = recipient
        msg['X-Mailer'] = 'Microsoft Outlook 16.0'
        msg['X-MS-Exchange-Organization-SCL'] = '-1'
        
        msg.attach(MIMEText(body, 'plain'))
        
        server = smtplib.SMTP(PMTA_HOST, PMTA_PORT, timeout=15)
        server.sendmail(sender_email, recipient, msg.as_string())
        server.quit()
        return True
    except Exception as e:
        print(f"      Error: {e}")
        return False

def log_test(conn, batch_id, creative_id, offer_id, sender, recipient, isp, subject, tracking_link):
    """Log test send"""
    cur = conn.cursor()
    cur.execute("""
        INSERT INTO admin.brain_warmup_tests 
        (test_batch_id, sender_email, recipient_email, recipient_isp, 
         config_type, headers_variant, offer_id, subject_line, tracking_link)
        VALUES (%s, %s, %s, %s, 'creative_test', 'exchange', %s, %s, %s)
    """, (batch_id, sender, recipient, isp, offer_id, subject, tracking_link))
    
    # Update creative usage
    cur.execute("""
        UPDATE admin.offer_creatives SET times_used = times_used + 1, last_used = NOW()
        WHERE id = %s
    """, (creative_id,))
    
    conn.commit()

def run_test(emails_per_creative=5, isps_to_test=['gmail', 'hotmail', 'yahoo']):
    """Run creative test campaign"""
    
    print("=" * 60)
    print("🧪 CREATIVE A/B TEST ON SEEDS")
    print(f"Started: {datetime.now()}")
    print("=" * 60)
    
    conn = get_db()
    batch_id = f"test_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
    
    # Get data
    accounts = get_office_accounts(conn, limit=100)
    creatives = get_creatives_to_test(conn)
    
    print(f"\n📊 Test Configuration:")
    print(f"   Batch ID: {batch_id}")
    print(f"   Creatives to test: {len(creatives)}")
    print(f"   ISPs: {', '.join(isps_to_test)}")
    print(f"   Emails per creative per ISP: {emails_per_creative}")
    print(f"   O365 accounts available: {len(accounts)}")
    
    total_sent = 0
    total_errors = 0
    results = {}
    
    for creative in creatives:
        cid, offer_id, subject, from_name, body, offer_name, payout = creative
        
        print(f"\n📧 Testing Creative #{cid}: {subject[:40]}...")
        print(f"   Offer: {offer_name} (${payout or 0}/lead)")
        
        results[cid] = {'subject': subject, 'by_isp': {}}
        
        for isp in isps_to_test:
            seeds = get_seeds_by_isp(conn, isp, emails_per_creative)
            
            if not seeds:
                print(f"   ⚠️ No {isp} seeds available")
                continue
            
            sent = 0
            for seed_id, seed_email in seeds:
                # Get random O365 account
                if not accounts:
                    accounts = get_office_accounts(conn, limit=100)
                
                acc_id, sender = random.choice(accounts)
                
                # Build tracking link
                tracking_link = f"{TRACKING_URL}/c?o={offer_id}&c={cid}&s={seed_id}&b={batch_id}"
                
                # Replace placeholders in body
                final_body = body or "Click here: {offer_link}"
                final_body = final_body.replace('{offer_link}', tracking_link)
                final_body = final_body.replace('{unsub}', f"{TRACKING_URL}/u?s={seed_id}")
                
                # Send
                if send_email(sender, seed_email, subject, from_name or "Rewards Team", final_body):
                    sent += 1
                    total_sent += 1
                    log_test(conn, batch_id, cid, offer_id, sender, seed_email, isp, subject, tracking_link)
                else:
                    total_errors += 1
            
            results[cid]['by_isp'][isp] = sent
            print(f"   ✅ {isp}: {sent}/{len(seeds)} sent")
    
    # Summary
    print("\n" + "=" * 60)
    print("📊 TEST SUMMARY")
    print("=" * 60)
    print(f"Batch ID: {batch_id}")
    print(f"Total Sent: {total_sent}")
    print(f"Errors: {total_errors}")
    
    print("\nBy Creative:")
    for cid, data in results.items():
        total = sum(data['by_isp'].values())
        print(f"  #{cid} ({data['subject'][:30]}...): {total} emails")
        for isp, count in data['by_isp'].items():
            print(f"      {isp}: {count}")
    
    print("\n" + "=" * 60)
    print("📋 NEXT STEPS")
    print("=" * 60)
    print("1. Wait 1-24h for opens/clicks data")
    print("2. Run: python3 /opt/wevads/scripts/analyze-creative-results.py")
    print("3. Winners will be auto-selected for mass send")
    print(f"\nCheck results: SELECT * FROM admin.brain_warmup_tests WHERE test_batch_id = '{batch_id}';")
    
    conn.close()
    return batch_id

if __name__ == '__main__':
    emails = int(sys.argv[1]) if len(sys.argv) > 1 else 5
    isps = sys.argv[2].split(',') if len(sys.argv) > 2 else ['gmail', 'hotmail', 'yahoo']
    
    run_test(emails_per_creative=emails, isps_to_test=isps)