wevads-platform/scripts/generate-creatives.py

#!/usr/bin/env python3
"""
CREATIVE GENERATOR
- Importe les créatives des sponsors
- Génère automatiquement si manquantes
- Sélectionne les meilleures par score
"""

import psycopg2
import random
import re
from datetime import datetime

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

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

def extract_reward_from_name(name):
    """Extract reward/prize from offer name"""
    # Patterns communs
    patterns = [
        r'\$(\d+)\s*([\w\s]+)',  # $750 PayPal
        r'Free\s+([\w\s]+)',      # Free iPhone
        r'Win\s+([\w\s]+)',       # Win a TV
    ]
    
    for pattern in patterns:
        match = re.search(pattern, name, re.IGNORECASE)
        if match:
            return match.group(0)
    
    return name

def find_best_template(conn, offer_name, vertical):
    """Find best matching template"""
    cur = conn.cursor()
    
    # Try vertical match first
    cur.execute("""
        SELECT id, subject_patterns, from_name_patterns, text_body_template, keywords
        FROM admin.creative_templates
        WHERE vertical = %s AND is_active = true
        ORDER BY avg_click_rate DESC NULLS LAST
        LIMIT 1
    """, (vertical,))
    
    result = cur.fetchone()
    if result:
        return result
    
    # Fallback to keyword matching
    cur.execute("""
        SELECT id, subject_patterns, from_name_patterns, text_body_template, keywords
        FROM admin.creative_templates
        WHERE is_active = true
        ORDER BY avg_click_rate DESC NULLS LAST
        LIMIT 1
    """)
    
    return cur.fetchone()

def generate_creative(conn, offer_id, offer_name, offer_url, vertical):
    """Generate creative from template"""
    template = find_best_template(conn, offer_name, vertical)
    
    if not template:
        print(f"    No template found for vertical: {vertical}")
        return None
    
    tmpl_id, subjects, from_names, text_body, keywords = template
    
    # Extract reward from offer name
    reward = extract_reward_from_name(offer_name)
    
    # Generate creative
    subject = random.choice(subjects).replace('{reward}', reward)
    from_name = random.choice(from_names)
    body = text_body.replace('{reward}', reward).replace('{cta_link}', 'http://[domain]/[url]').replace('{unsub_link}', 'http://[domain]/[unsub]').replace('{offer_link}', 'http://[domain]/[url]').replace('{name}', '[first_name]').replace('{company}', '[from_name]')
    
    cur = conn.cursor()
    cur.execute("""
        INSERT INTO admin.offer_creatives 
        (offer_id, creative_name, source, subject_line, from_name, text_body, status)
        VALUES (%s, %s, 'generated', %s, %s, %s, 'active')
        RETURNING id
    """, (offer_id, f"Auto: {offer_name[:30]}", subject, from_name, body))
    
    creative_id = cur.fetchone()[0]
    conn.commit()
    
    return creative_id

def import_sponsor_creatives(conn, offer_id, creatives_data):
    """Import creatives from sponsor"""
    cur = conn.cursor()
    imported = 0
    
    for c in creatives_data:
        try:
            cur.execute("""
                INSERT INTO admin.offer_creatives 
                (offer_id, creative_name, source, subject_line, from_name, text_body, html_body, status)
                VALUES (%s, %s, 'sponsor', %s, %s, %s, %s, 'active')
            """, (offer_id, c.get('name', 'Sponsor Creative'), 
                  c.get('subject'), c.get('from_name'), 
                  c.get('text'), c.get('html')))
            imported += 1
        except Exception as e:
            print(f"    Error: {e}")
    
    conn.commit()
    return imported

def select_best_creative(conn, offer_id):
    """Select best creative based on score"""
    cur = conn.cursor()
    
    # Calculate scores based on performance
    cur.execute("""
        UPDATE admin.offer_creatives
        SET score = CASE
            WHEN times_used = 0 THEN 50
            ELSE LEAST(100, 
                (COALESCE(open_rate, 0) * 0.3 + 
                 COALESCE(click_rate, 0) * 0.5 + 
                 CASE WHEN conversions > 0 THEN 20 ELSE 0 END))
        END
        WHERE offer_id = %s
    """, (offer_id,))
    
    # Mark winner
    cur.execute("""
        UPDATE admin.offer_creatives SET is_winner = false WHERE offer_id = %s
    """, (offer_id,))
    
    cur.execute("""
        UPDATE admin.offer_creatives SET is_winner = true
        WHERE id = (
            SELECT id FROM admin.offer_creatives 
            WHERE offer_id = %s AND status = 'active'
            ORDER BY score DESC, times_used ASC
            LIMIT 1
        )
    """, (offer_id,))
    
    conn.commit()

def process_offers(conn):
    """Process all offers - import or generate creatives"""
    cur = conn.cursor()
    
    # Get offers without creatives
    cur.execute("""
        SELECT o.id, o.name, COALESCE(o.offer_name, o.name) as display_name, 
               o.offer_url, o.tracking_url, o.vertical,
               o.subject_lines, o.from_names, o.text_template
        FROM admin.affiliate_offers o
        LEFT JOIN admin.offer_creatives c ON o.id = c.offer_id
        WHERE (o.status = 'active' OR o.is_active = true)
        AND c.id IS NULL
    """)
    
    offers_without = cur.fetchall()
    print(f"\n📦 Offers without creatives: {len(offers_without)}")
    
    for offer in offers_without:
        offer_id, name, display_name, offer_url, tracking_url, vertical, subjects, from_names, text_tmpl = offer
        url = offer_url or tracking_url
        
        print(f"\n  Processing: {display_name or name}")
        
        # If sponsor provided creatives in offer data
        if subjects and from_names:
            print(f"    Using sponsor-provided data...")
            for i, subj in enumerate(subjects[:3]):  # Max 3 creatives
                fname = from_names[i] if i < len(from_names) else from_names[0]
                body = text_tmpl or f"Click here: {{offer_link}}"
                
                cur.execute("""
                    INSERT INTO admin.offer_creatives 
                    (offer_id, creative_name, source, subject_line, from_name, text_body, status)
                    VALUES (%s, %s, 'sponsor', %s, %s, %s, 'active')
                """, (offer_id, f"Sponsor #{i+1}", subj, fname, body))
            
            conn.commit()
            print(f"    ✅ Imported {min(len(subjects), 3)} sponsor creatives")
        else:
            # Generate from template
            print(f"    Generating from template (vertical: {vertical})...")
            creative_id = generate_creative(conn, offer_id, display_name or name, url, vertical or 'sweepstakes')
            
            if creative_id:
                print(f"    ✅ Generated creative #{creative_id}")
            else:
                print(f"    ⚠️ Could not generate")
        
        # Select best
        select_best_creative(conn, offer_id)
    
    return len(offers_without)

def update_scores(conn):
    """Update all creative scores based on performance"""
    cur = conn.cursor()
    
    cur.execute("""
        UPDATE admin.offer_creatives
        SET 
            open_rate = CASE WHEN times_used > 0 THEN (opens::decimal / times_used * 100) ELSE NULL END,
            click_rate = CASE WHEN opens > 0 THEN (clicks::decimal / opens * 100) ELSE NULL END,
            score = CASE
                WHEN times_used = 0 THEN 50
                WHEN times_used < 10 THEN 50 + (COALESCE(clicks, 0) * 5)
                ELSE LEAST(100, 
                    (COALESCE(opens::decimal / NULLIF(times_used, 0), 0) * 30 + 
                     COALESCE(clicks::decimal / NULLIF(opens, 0), 0) * 50 + 
                     CASE WHEN conversions > 0 THEN 20 ELSE 0 END))
            END
        WHERE status = 'active'
    """)
    
    conn.commit()

def main():
    print("=" * 60)
    print("CREATIVE GENERATOR & OPTIMIZER")
    print(f"Started: {datetime.now()}")
    print("=" * 60)
    
    conn = get_db()
    
    # Process offers
    processed = process_offers(conn)
    
    # Update all scores
    print("\n📊 Updating scores...")
    update_scores(conn)
    
    # Summary
    cur = conn.cursor()
    cur.execute("""
        SELECT 
            source,
            COUNT(*) as total,
            SUM(CASE WHEN is_winner THEN 1 ELSE 0 END) as winners,
            ROUND(AVG(score)::numeric, 2) as avg_score
        FROM admin.offer_creatives
        WHERE status = 'active'
        GROUP BY source
    """)
    
    print("\n" + "=" * 60)
    print("📋 CREATIVE SUMMARY")
    print("=" * 60)
    
    for source, total, winners, avg_score in cur.fetchall():
        print(f"  {source}: {total} creatives, {winners} winners, avg score: {avg_score}")
    
    # Top performers
    print("\n🏆 Top 5 Creatives:")
    cur.execute("""
        SELECT c.id, LEFT(c.subject_line, 40) as subject, c.score, c.times_used, c.clicks
        FROM admin.offer_creatives c
        WHERE c.status = 'active'
        ORDER BY c.score DESC, c.clicks DESC
        LIMIT 5
    """)
    
    for cid, subj, score, used, clicks in cur.fetchall():
        print(f"  #{cid}: {subj}... (score: {score}, used: {used}, clicks: {clicks})")
    
    conn.close()
    print("\n✅ Creative generation complete!")

if __name__ == '__main__':
    main()