wevads-platform/scripts/build-email-content.py

#!/usr/bin/env python3
"""
BUILD EMAIL CONTENT
Construit le contenu email avec les bonnes URLs selon l'ISP
- Sélectionne l'hébergement d'images approprié
- Gère le tracking pixel selon les règles ISP
- Protège l'IP du serveur tracking
"""

import psycopg2
import re

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

# Tracking via CDN/proxy pour cacher l'IP
TRACKING_ENDPOINTS = {
    'direct': 'http://151.80.235.110',
    'cloudflare': 'https://track.wevads.com',  # À configurer avec CF Workers
    's3_redirect': 'https://s3.amazonaws.com/wevads-tracking/redirect.html'
}

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

def detect_isp(email):
    """Detect ISP from email domain"""
    domain = email.split('@')[-1].lower()
    
    isp_map = {
        'gmail.com': 'gmail', 'googlemail.com': 'gmail',
        'hotmail': 'hotmail', 'outlook': 'hotmail', 'live': 'hotmail', 'msn': 'hotmail',
        'yahoo': 'yahoo', 'ymail': 'yahoo',
        'aol': 'aol',
        'icloud.com': 'icloud', 'me.com': 'icloud', 'mac.com': 'icloud',
        'gmx': 'gmx',
        'videotron': 'videotron',
        'charter': 'spectrum', 'spectrum': 'spectrum', 'rr.com': 'spectrum',
        'comcast': 'comcast', 'xfinity': 'comcast',
        'protonmail': 'protonmail', 'pm.me': 'protonmail',
        'orange.fr': 'orange', 'wanadoo': 'orange',
        'free.fr': 'free',
    }
    
    for key, isp in isp_map.items():
        if key in domain:
            return isp
    return 'other'

def get_isp_rules(isp):
    """Get hosting rules for ISP"""
    conn = get_db()
    cur = conn.cursor()
    
    cur.execute("""
        SELECT accepts_s3_images, accepts_tracking_pixel, 
               preferred_image_hosts, blocked_image_hosts
        FROM admin.isp_hosting_rules
        WHERE isp_name = %s
    """, (isp,))
    
    row = cur.fetchone()
    conn.close()
    
    if row:
        return {
            's3_ok': row[0],
            'pixel_ok': row[1],
            'preferred': row[2] or [],
            'blocked': row[3] or []
        }
    return {'s3_ok': True, 'pixel_ok': True, 'preferred': [], 'blocked': []}

def get_image_base_url(isp, rules):
    """Get appropriate image base URL for ISP"""
    
    # Si S3 bloqué, utiliser alternative
    if not rules['s3_ok']:
        # Utiliser imgur ou autre
        return 'https://i.imgur.com/'
    
    # Si européen, préférer S3 EU
    if isp in ['gmx', 'orange', 'free']:
        return 'https://s3.eu-west-1.amazonaws.com/wevads-eu/'
    
    # Default S3 US
    return 'https://s3.amazonaws.com/wevads-images/'

def build_tracking_url(tracking_code, isp, rules, action='c'):
    """Build tracking URL appropriate for ISP"""
    
    # Pour Gmail/iCloud/Protonmail, utiliser redirect via S3/CDN
    if isp in ['gmail', 'icloud', 'protonmail']:
        # URL S3 qui redirige vers notre tracking
        return f"https://s3.amazonaws.com/wevads-tracking/r/{action}/{tracking_code}"
    
    # Pour autres, tracking direct OK
    return f"{TRACKING_ENDPOINTS['direct']}/{action}/{tracking_code}"

def build_tracking_pixel(tracking_code, isp, rules):
    """Build tracking pixel appropriate for ISP"""
    
    # Si pixel bloqué, pas de pixel
    if not rules['pixel_ok']:
        return ''
    
    # Pour Gmail, pixel inutile (images proxifiées)
    if isp == 'gmail':
        return ''
    
    pixel_url = f"{TRACKING_ENDPOINTS['direct']}/o/{tracking_code}"
    return f'<img src="{pixel_url}" width="1" height="1" style="display:none" alt="">'

def build_email(recipient_email, subject, body_template, tracking_code, image_urls=None):
    """
    Build complete email content adapted to recipient's ISP
    
    Returns: (text_body, html_body, metadata)
    """
    isp = detect_isp(recipient_email)
    rules = get_isp_rules(isp)
    
    image_base = get_image_base_url(isp, rules)
    click_url = build_tracking_url(tracking_code, isp, rules, 'c')
    unsub_url = build_tracking_url(tracking_code, isp, rules, 'u')
    pixel = build_tracking_pixel(tracking_code, isp, rules)
    
    # Replace placeholders in body
    text_body = body_template or "Click here: http://[domain]/[url]"
    # offer_link replaced by ADX [url] tag at Java level
    text_body = text_body.replace('{unsub}', unsub_url)
    
    # Replace image URLs if S3 blocked
    if image_urls and not rules['s3_ok']:
        for orig_url in image_urls:
            if 's3.amazonaws.com' in orig_url or 'cloudfront.net' in orig_url:
                # Would need to upload to alternative host
                # For now, remove image
                text_body = text_body.replace(orig_url, '')
    
    # Build HTML
    html_body = f"""<html>
<head><meta charset="utf-8"></head>
<body style="font-family: Arial, sans-serif; line-height: 1.6;">
{text_body.replace(chr(10), '<br>')}
{pixel}
</body>
</html>"""
    
    metadata = {
        'isp': isp,
        's3_ok': rules['s3_ok'],
        'pixel_ok': rules['pixel_ok'],
        'image_host': image_base,
        'tracking_method': 'direct' if rules['pixel_ok'] else 'redirect'
    }
    
    return text_body, html_body, metadata

# Test
if __name__ == '__main__':
    test_emails = [
        'user@gmail.com',
        'user@hotmail.com', 
        'user@yahoo.com',
        'user@gmx.de',
        'user@protonmail.com'
    ]
    
    print("=" * 70)
    print("EMAIL CONTENT BUILDER - ISP ADAPTATION")
    print("=" * 70)
    
    for email in test_emails:
        text, html, meta = build_email(
            email,
            "Test Subject",
            "Hello!\n\nClick here: http://[domain]/[url]\n\nUnsubscribe: http://[domain]/[unsub]",
            "abc123test"
        )
        
        print(f"\n📧 {email}")
        print(f"   ISP: {meta['isp']}")
        print(f"   S3 OK: {meta['s3_ok']}")
        print(f"   Pixel OK: {meta['pixel_ok']}")
        print(f"   Image Host: {meta['image_host']}")
        print(f"   Tracking: {meta['tracking_method']}")