wevads-platform/scripts/analyze-offers.py

#!/usr/bin/env python3
"""
OFFER INTELLIGENCE ANALYZER
Analyzes offers vs available data and generates instructions
"""

import psycopg2
import json
from datetime import datetime

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

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

def analyze_data_by_isp(conn):
    """Get current data distribution by ISP"""
    cur = conn.cursor()
    cur.execute("""
        SELECT 
            CASE 
                WHEN email LIKE '%@gmail.com' THEN 'gmail'
                WHEN email LIKE '%@hotmail.%' OR email LIKE '%@outlook.%' OR email LIKE '%@live.%' THEN 'hotmail'
                WHEN email LIKE '%@yahoo.%' THEN 'yahoo'
                ELSE 'other'
            END as isp,
            COUNT(*) as count
        FROM admin.brain_seeds
        WHERE is_active = true
        GROUP BY isp
        ORDER BY count DESC
    """)
    
    data = {'US': {}}  # Default to US
    for isp, count in cur.fetchall():
        data['US'][isp] = count
    return data

def analyze_offers(conn):
    """Analyze offers and match with data"""
    cur = conn.cursor()
    
    cur.execute("""
        SELECT id, name, offer_name, country_code, vertical, payout, payout_amount, allowed_isps
        FROM admin.affiliate_offers
        WHERE (status = 'active' OR is_active = true)
        AND (payout > 0 OR payout_amount > 0)
        ORDER BY COALESCE(payout, payout_amount, 0) DESC
        LIMIT 50
    """)
    return cur.fetchall()

def generate_instructions(conn, data_dist, offers):
    """Generate actionable instructions"""
    cur = conn.cursor()
    instructions = []
    
    total_seeds = sum(data_dist.get('US', {}).values())
    
    for offer in offers:
        offer_id, name, offer_name, country, vertical, payout, payout_amount, allowed_isps = offer
        actual_payout = payout or payout_amount or 0
        display_name = name or offer_name
        country = country or 'US'
        
        country_data = data_dist.get(country, data_dist.get('US', {}))
        
        if total_seeds < 1000 and actual_payout >= 2.0:
            instructions.append({
                'type': 'harvesting',
                'priority': 80,
                'country': country,
                'instruction': f"HARVEST MORE DATA: Only {total_seeds} seeds for {display_name} (${actual_payout}/lead)",
                'reason': f"High payout offer needs more seeds",
                'offer_ids': [offer_id],
                'potential': actual_payout * 1000 * 0.02
            })
        
        if allowed_isps:
            for isp in allowed_isps:
                isp_count = country_data.get(isp, 0)
                if isp_count < 500 and actual_payout >= 1.5:
                    instructions.append({
                        'type': 'harvesting',
                        'priority': 70,
                        'country': country,
                        'isp': isp,
                        'instruction': f"NEED {isp.upper()} SEEDS: Only {isp_count} for {display_name}",
                        'reason': f"Offer requires {isp} but low inventory",
                        'offer_ids': [offer_id],
                        'potential': actual_payout * 500 * 0.02
                    })
        
        if total_seeds >= 1000 and actual_payout >= 2.0:
            instructions.append({
                'type': 'brain_config',
                'priority': 60,
                'country': country,
                'instruction': f"TEST CONFIGS FOR {display_name}: {total_seeds} seeds ready",
                'reason': f"Optimize delivery for ${actual_payout} offer",
                'offer_ids': [offer_id],
                'potential': actual_payout * total_seeds * 0.02
            })
    
    for inst in instructions:
        cur.execute("""
            INSERT INTO admin.brain_instructions 
            (instruction_type, priority, target_country, target_isp, instruction, reason, related_offer_ids, potential_revenue)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """, (inst['type'], inst['priority'], inst.get('country'), inst.get('isp'),
              inst['instruction'], inst['reason'], inst.get('offer_ids'), inst.get('potential')))
    
    conn.commit()
    return instructions

def main():
    print("=" * 60)
    print("OFFER INTELLIGENCE ANALYZER")
    print(f"Started: {datetime.now()}")
    print("=" * 60)
    
    conn = get_db()
    
    print("\n📊 Analyzing current data distribution...")
    data_dist = analyze_data_by_isp(conn)
    
    print("\nData by ISP:")
    for country, isps in data_dist.items():
        total = sum(isps.values())
        print(f"  {country}: {total:,} seeds")
        for isp, count in sorted(isps.items(), key=lambda x: x[1], reverse=True):
            print(f"    - {isp}: {count:,}")
    
    print("\n📦 Analyzing offers...")
    offers = analyze_offers(conn)
    print(f"Found {len(offers)} active offers")
    
    print("\n🧠 Generating instructions...")
    instructions = generate_instructions(conn, data_dist, offers)
    
    print(f"\nGenerated {len(instructions)} instructions:")
    for inst in sorted(instructions, key=lambda x: x['priority'], reverse=True)[:10]:
        print(f"  [{inst['type'].upper()}] P{inst['priority']}: {inst['instruction'][:60]}...")
    
    print("\n" + "=" * 60)
    print("📋 SUMMARY")
    print("=" * 60)
    
    cur = conn.cursor()
    cur.execute("""
        SELECT instruction_type, COUNT(*), SUM(potential_revenue)
        FROM admin.brain_instructions
        WHERE status = 'pending' AND expires_at > NOW()
        GROUP BY instruction_type
    """)
    
    for itype, count, revenue in cur.fetchall():
        print(f"  {itype}: {count} pending (${revenue or 0:,.2f} potential)")
    
    conn.close()
    print("\n✅ Analysis complete!")

if __name__ == '__main__':
    main()