<?php
/**
 * BRAIN OFFER SYNC - Synchronise les offres affiliate avec Brain Engine
 * - Récupère les meilleures offres de affiliate.offers
 * - Analyse les performances
 * - Met à jour brain_offer_profiles
 */

$pdo = new PDO('pgsql:host=localhost;dbname=adx_system', 'admin', 'admin123');

echo "🧠 BRAIN OFFER SYNC\n";
echo "══════════════════════════════════════\n\n";

// 1. Récupérer toutes les offres actives avec leurs liens
$sql = "
SELECT 
    o.id,
    o.name,
    o.status,
    o.production_id,
    n.name as network_name,
    l.value as sponsor_link,
    (SELECT COUNT(*) FROM affiliate.creatives c WHERE c.offer_id = o.id) as creative_count,
    (SELECT COUNT(*) FROM affiliate.subjects s WHERE s.offer_id = o.id) as subject_count,
    (SELECT COUNT(*) FROM affiliate.from_names f WHERE f.offer_id = o.id) as from_count
FROM affiliate.offers o
LEFT JOIN affiliate.affiliate_networks n ON o.affiliate_network_id = n.id
LEFT JOIN affiliate.links l ON l.offer_id = o.id AND l.type = 'preview'
WHERE o.status = 'Activated'
ORDER BY o.id DESC
";

$offers = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
echo "📋 Offres actives: " . count($offers) . "\n\n";

// 2. Synchroniser avec brain_offer_profiles
$synced = 0;
foreach ($offers as $offer) {
    // Déterminer le vertical basé sur le nom
    $vertical = 'general';
    $nameLower = strtolower($offer['name']);
    if (strpos($nameLower, 'health') !== false || strpos($nameLower, 'pain') !== false) $vertical = 'health';
    elseif (strpos($nameLower, 'cloud') !== false || strpos($nameLower, 'tech') !== false) $vertical = 'tech';
    elseif (strpos($nameLower, 'finance') !== false || strpos($nameLower, 'credit') !== false) $vertical = 'finance';
    elseif (strpos($nameLower, 'sweeps') !== false || strpos($nameLower, 'win') !== false) $vertical = 'sweepstakes';
    
    // Déterminer les meilleurs ISPs basé sur le pays
    $bestIsps = 'GMX,WEB.DE,T-Online'; // Default pour DE
    if (strpos($nameLower, 'uk') !== false) $bestIsps = 'BT,Sky,Virgin';
    elseif (strpos($nameLower, 'us') !== false || strpos($nameLower, 'au') !== false) $bestIsps = 'Gmail,Yahoo,Outlook';
    elseif (strpos($nameLower, 'fr') !== false) $bestIsps = 'Orange,SFR,Free';
    
    // Insérer/mettre à jour dans brain_offer_profiles
    $stmt = $pdo->prepare("
        INSERT INTO admin.brain_offer_profiles (offer_id, offer_name, vertical, best_isps, total_sends)
        VALUES (?, ?, ?, ?, 0)
        ON CONFLICT (offer_id) DO UPDATE SET 
            offer_name = EXCLUDED.offer_name,
            vertical = EXCLUDED.vertical,
            best_isps = EXCLUDED.best_isps
    ");
    
    try {
        $stmt->execute([$offer['id'], $offer['name'], $vertical, $bestIsps]);
        $synced++;
    } catch (Exception $e) {
        // Ignorer les erreurs de contrainte
    }
    
    echo "  ✓ #{$offer['id']} {$offer['name']}\n";
    echo "    Network: {$offer['network_name']} | Creatives: {$offer['creative_count']} | Subjects: {$offer['subject_count']}\n";
    echo "    Vertical: $vertical | Best ISPs: $bestIsps\n";
    if ($offer['sponsor_link']) {
        echo "    Sponsor: " . substr($offer['sponsor_link'], 0, 50) . "...\n";
    }
    echo "\n";
}

echo "══════════════════════════════════════\n";
echo "✅ Synchronisé: $synced offres dans brain_offer_profiles\n";

// 3. Afficher les meilleures offres par vertical
echo "\n📊 OFFRES PAR VERTICAL:\n";
$verticals = $pdo->query("SELECT vertical, COUNT(*) as cnt FROM admin.brain_offer_profiles GROUP BY vertical ORDER BY cnt DESC")->fetchAll();
foreach ($verticals as $v) {
    echo "  • {$v['vertical']}: {$v['cnt']} offres\n";
}