wevads-platform/scripts/smart-provider-selector.py

#!/usr/bin/env python3
"""
SMART PROVIDER SELECTOR
Sélectionne automatiquement le meilleur provider+region pour:
- Un ISP cible spécifique
- Ou globalement (meilleur rapport qualité/prix)

Prend en compte:
- Burn rate historique
- Inbox rate par ISP
- Coût
- Disponibilité
"""

import psycopg2
import json
import sys

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

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

def get_best_provider(target_isp=None, max_cost=0.05, min_confidence=20):
    """
    Get best provider recommendation
    
    Args:
        target_isp: Specific ISP to optimize for (gmail, hotmail, etc.)
        max_cost: Maximum cost per hour
        min_confidence: Minimum confidence score required
    """
    conn = get_db()
    cur = conn.cursor()
    
    if target_isp:
        # Get winning combination for this ISP
        cur.execute("""
            SELECT best_provider, best_region, inbox_rate, confidence_score
            FROM admin.winning_combinations
            WHERE target_isp = %s AND confidence_score >= %s
        """, (target_isp, min_confidence))
        
        result = cur.fetchone()
        if result:
            # Verify cost is acceptable
            cur.execute("""
                SELECT cost_per_hour FROM admin.provider_regions
                WHERE provider_name = %s AND region_code = %s
            """, (result[0], result[1]))
            cost = cur.fetchone()
            
            if cost and cost[0] <= max_cost:
                conn.close()
                return {
                    'provider': result[0],
                    'region': result[1],
                    'reason': f'Best for {target_isp} (inbox: {result[2]}%)',
                    'cost': cost[0],
                    'confidence': result[3]
                }
    
    # Fallback: best overall (lowest burn rate, acceptable cost)
    cur.execute("""
        SELECT provider_name, region_code, burn_rate, cost_per_hour, performance_score
        FROM admin.provider_regions
        WHERE is_active = true
        AND cost_per_hour <= %s
        AND (total_servers_created > 0 OR total_servers_created IS NULL)
        ORDER BY 
            COALESCE(burn_rate, 50) ASC,
            COALESCE(performance_score, 50) DESC,
            cost_per_hour ASC
        LIMIT 1
    """, (max_cost,))
    
    result = cur.fetchone()
    conn.close()
    
    if result:
        return {
            'provider': result[0],
            'region': result[1],
            'reason': f'Best overall (burn: {result[2] or 0}%, score: {result[4] or 50})',
            'cost': result[3],
            'confidence': 50
        }
    
    # Ultimate fallback
    return {
        'provider': 'hetzner',
        'region': 'hel1',
        'reason': 'Default fallback',
        'cost': 0.007,
        'confidence': 0
    }

def get_providers_ranking(limit=10):
    """Get ranked list of all providers"""
    conn = get_db()
    cur = conn.cursor()
    
    cur.execute("""
        SELECT 
            pr.provider_name,
            pr.region_code,
            pr.country_code,
            pr.cost_per_hour,
            pr.burn_rate,
            pr.performance_score,
            pr.avg_emails_before_burn,
            pr.total_servers_created,
            cp.api_key IS NOT NULL as api_configured
        FROM admin.provider_regions pr
        LEFT JOIN admin.cloud_providers cp ON pr.provider_name = cp.name
        WHERE pr.is_active = true
        ORDER BY 
            COALESCE(pr.burn_rate, 50) ASC,
            COALESCE(pr.performance_score, 50) DESC
        LIMIT %s
    """, (limit,))
    
    results = []
    for row in cur.fetchall():
        results.append({
            'provider': row[0],
            'region': row[1],
            'country': row[2],
            'cost': row[3],
            'burn_rate': row[4],
            'score': row[5],
            'avg_emails': row[6],
            'servers_tested': row[7],
            'api_ready': row[8]
        })
    
    conn.close()
    return results

def get_isp_recommendations():
    """Get best provider for each major ISP"""
    conn = get_db()
    cur = conn.cursor()
    
    cur.execute("""
        SELECT target_isp, best_provider, best_region, inbox_rate, confidence_score
        FROM admin.winning_combinations
        ORDER BY target_isp
    """)
    
    results = {}
    for row in cur.fetchall():
        results[row[0]] = {
            'provider': row[1],
            'region': row[2],
            'inbox_rate': row[3],
            'confidence': row[4]
        }
    
    conn.close()
    return results

def main():
    print("=" * 60)
    print("🎯 SMART PROVIDER SELECTOR")
    print("=" * 60)
    
    if len(sys.argv) > 1:
        target_isp = sys.argv[1]
        print(f"\nFinding best provider for: {target_isp}")
        
        result = get_best_provider(target_isp=target_isp)
        print(f"\n✅ RECOMMENDATION:")
        print(f"   Provider: {result['provider']}")
        print(f"   Region: {result['region']}")
        print(f"   Cost: ${result['cost']}/hr")
        print(f"   Reason: {result['reason']}")
        print(f"   Confidence: {result['confidence']}%")
        return
    
    # Show all rankings
    print("\n📊 PROVIDER RANKINGS:")
    print("-" * 60)
    
    rankings = get_providers_ranking(15)
    print(f"{'#':<3} {'Provider':<12} {'Region':<12} {'Burn%':>7} {'Score':>6} {'$/hr':>7} {'API':>5}")
    print("-" * 60)
    
    for i, r in enumerate(rankings, 1):
        api = "✅" if r['api_ready'] else "❌"
        print(f"{i:<3} {r['provider']:<12} {r['region']:<12} {r['burn_rate'] or 0:>6.1f}% {r['score'] or 50:>6.0f} ${r['cost'] or 0:>.4f} {api:>5}")
    
    # ISP recommendations
    print("\n🎯 BEST PROVIDER BY ISP:")
    print("-" * 60)
    
    isp_recs = get_isp_recommendations()
    for isp, rec in isp_recs.items():
        conf = f"({rec['confidence'] or 0:.0f}% conf)" if rec['confidence'] else "(no data)"
        print(f"   {isp:<10} → {rec['provider']}/{rec['region']} - Inbox: {rec['inbox_rate'] or 0}% {conf}")
    
    # Quick recommendations
    print("\n💡 QUICK PICKS:")
    print("-" * 60)
    
    best_overall = get_best_provider()
    print(f"   Best Overall: {best_overall['provider']}/{best_overall['region']} - {best_overall['reason']}")
    
    best_cheap = get_best_provider(max_cost=0.01)
    print(f"   Best Budget:  {best_cheap['provider']}/{best_cheap['region']} (${best_cheap['cost']}/hr)")
    
    best_gmail = get_best_provider(target_isp='gmail')
    print(f"   Best Gmail:   {best_gmail['provider']}/{best_gmail['region']}")

if __name__ == '__main__':
    main()