#!/usr/bin/env php
<?php
/**
 * Brain Creative Performance Engine
 * 
 * 1. A/B Testing: Track & score creatives per offer/country/ISP, auto-promote winners
 * 2. Translation: Auto-translate winning creatives to target languages via HAMID IA
 * 3. Offer Discovery: Auto-evaluate new Everflow offers & recommend approval
 * 
 * Usage:
 *   php brain-creative-engine.php calculate_performance
 *   php brain-creative-engine.php promote_winners
 *   php brain-creative-engine.php translate_winners
 *   php brain-creative-engine.php discover_offers
 *   php brain-creative-engine.php full_cycle
 *   php brain-creative-engine.php status
 */

$DB_HOST = 'localhost';
$DB_NAME = 'adx_system';
$DB_USER = 'admin';
$DB_PASS = 'admin123';

$EVERFLOW_API = 'https://api.eflow.team';
$EVERFLOW_KEY = 'b0PPowhR3CI9EtcqtLHA';

// Minimum sends before judging a creative
$MIN_SENDS_FOR_JUDGMENT = 30;
// Click rate threshold to be considered a winner
$WINNER_CLICK_RATE = 2.0; // 2%+
// Click rate below which creative gets paused
$PAUSE_CLICK_RATE = 0.3; // <0.3% after enough sends = pause
// Minimum Brain score for auto-approval recommendation
$AUTO_APPROVE_SCORE = 7.0;

$db = pg_connect("host=$DB_HOST dbname=$DB_NAME user=$DB_USER password=$DB_PASS");
if (!$db) die("DB connection failed\n");

$action = $argv[1] ?? 'status';

switch ($action) {
    case 'calculate_performance': calculate_performance($db); break;
    case 'promote_winners': promote_winners($db); break;
    case 'translate_winners': translate_winners($db); break;
    case 'discover_offers': discover_offers($db); break;
    case 'full_cycle': full_cycle($db); break;
    case 'status': show_status($db); break;
    default: echo "Unknown action: $action\n";
}

pg_close($db);

// ============================================================
// 1. A/B TESTING - Calculate creative performance
// ============================================================
function calculate_performance($db) {
    echo "=== CALCULATING CREATIVE PERFORMANCE ===\n";
    
    // Get sends grouped by offer + creative + country + ISP
    $sql = "
        SELECT 
            u.offer_id,
            u.creative_id,
            COALESCE(u.country, 'ALL') as country,
            COALESCE(u.isp_target, 'ALL') as isp_target,
            COUNT(*) as total_sent,
            COUNT(DISTINCT CASE WHEN te.event_type = 'open' THEN te.tracking_id END) as total_opens,
            COUNT(DISTINCT CASE WHEN te.event_type = 'click' THEN te.tracking_id END) as total_clicks
        FROM admin.unified_send_log_new u
        LEFT JOIN admin.tracking_events te ON te.tracking_id = u.tracking_id
        WHERE u.status = 'sent' 
          AND u.offer_id IS NOT NULL
          AND u.creative_id IS NOT NULL
        GROUP BY u.offer_id, u.creative_id, u.country, u.isp_target
    ";
    
    $result = pg_query($db, $sql);
    $updated = 0;
    
    while ($row = pg_fetch_assoc($result)) {
        $sent = (int)$row['total_sent'];
        $opens = (int)$row['total_opens'];
        $clicks = (int)$row['total_clicks'];
        
        $open_rate = $sent > 0 ? round(($opens / $sent) * 100, 2) : 0;
        $click_rate = $sent > 0 ? round(($clicks / $sent) * 100, 2) : 0;
        
        // Composite score: weighted combination
        // Click rate is king (60%), open rate matters (30%), volume bonus (10%)
        $volume_bonus = min($sent / 100, 1) * 10; // Max 10 points at 100 sends
        $score = ($click_rate * 6) + ($open_rate * 3) + $volume_bonus;
        
        $upsert = "
            INSERT INTO admin.brain_creative_performance 
                (offer_id, creative_id, country, isp_target, total_sent, total_opens, total_clicks, 
                 open_rate, click_rate, score, last_calculated)
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, NOW())
            ON CONFLICT (offer_id, creative_id, country, isp_target)
            DO UPDATE SET 
                total_sent = $5, total_opens = $6, total_clicks = $7,
                open_rate = $8, click_rate = $9, score = $10, last_calculated = NOW()
        ";
        
        pg_query_params($db, $upsert, [
            $row['offer_id'], $row['creative_id'], $row['country'], $row['isp_target'],
            $sent, $opens, $clicks, $open_rate, $click_rate, $score
        ]);
        $updated++;
    }
    
    // Also calculate subject line performance
    $sql_subj = "
        SELECT 
            u.offer_id,
            s.id as subject_id,
            COALESCE(u.country, 'ALL') as country,
            COUNT(*) as total_sent,
            COUNT(DISTINCT CASE WHEN te.event_type = 'open' THEN te.tracking_id END) as total_opens
        FROM admin.unified_send_log_new u
        JOIN affiliate.subjects s ON s.value = u.subject AND s.offer_id = u.offer_id
        LEFT JOIN admin.tracking_events te ON te.tracking_id = u.tracking_id
        WHERE u.status = 'sent' AND u.offer_id IS NOT NULL
        GROUP BY u.offer_id, s.id, u.country
    ";
    
    $result2 = pg_query($db, $sql_subj);
    $subj_updated = 0;
    
    while ($row = pg_fetch_assoc($result2)) {
        $sent = (int)$row['total_sent'];
        $opens = (int)$row['total_opens'];
        $open_rate = $sent > 0 ? round(($opens / $sent) * 100, 2) : 0;
        
        $upsert = "
            INSERT INTO admin.brain_subject_performance (offer_id, subject_id, country, total_sent, total_opens, open_rate, last_calculated)
            VALUES ($1, $2, $3, $4, $5, $6, NOW())
            ON CONFLICT (offer_id, subject_id, country)
            DO UPDATE SET total_sent = $4, total_opens = $5, open_rate = $6, last_calculated = NOW()
        ";
        pg_query_params($db, $upsert, [$row['offer_id'], $row['subject_id'], $row['country'], $sent, $opens, $open_rate]);
        $subj_updated++;
    }
    
    echo "  Updated: $updated creative performances, $subj_updated subject performances\n";
}

// ============================================================
// 2. PROMOTE WINNERS / PAUSE LOSERS
// ============================================================
function promote_winners($db) {
    global $MIN_SENDS_FOR_JUDGMENT, $WINNER_CLICK_RATE, $PAUSE_CLICK_RATE;
    
    echo "=== PROMOTING WINNERS / PAUSING LOSERS ===\n";
    
    // Get all performances with enough data
    $sql = "
        SELECT offer_id, creative_id, country, isp_target, 
               total_sent, click_rate, open_rate, score
        FROM admin.brain_creative_performance
        WHERE total_sent >= $MIN_SENDS_FOR_JUDGMENT
        ORDER BY offer_id, score DESC
    ";
    
    $result = pg_query($db, $sql);
    $winners = 0;
    $paused = 0;
    $by_offer = [];
    
    while ($row = pg_fetch_assoc($result)) {
        $key = $row['offer_id'] . '_' . $row['country'];
        if (!isset($by_offer[$key])) $by_offer[$key] = [];
        $by_offer[$key][] = $row;
    }
    
    foreach ($by_offer as $key => $creatives) {
        // Sort by score descending
        usort($creatives, fn($a, $b) => $b['score'] <=> $a['score']);
        
        $best = $creatives[0];
        
        // Mark winner
        if ((float)$best['click_rate'] >= $WINNER_CLICK_RATE) {
            pg_query_params($db,
                "UPDATE admin.brain_creative_performance SET is_winner = true WHERE offer_id = $1 AND creative_id = $2 AND country = $3",
                [$best['offer_id'], $best['creative_id'], $best['country']]
            );
            $winners++;
            echo "  WINNER: Offer #{$best['offer_id']} Creative #{$best['creative_id']} [{$best['country']}] - {$best['click_rate']}% CTR ({$best['total_sent']} sends)\n";
        }
        
        // Pause losers
        foreach (array_slice($creatives, 1) as $loser) {
            if ((float)$loser['click_rate'] < $PAUSE_CLICK_RATE) {
                pg_query_params($db,
                    "UPDATE admin.brain_creative_performance SET is_paused = true WHERE offer_id = $1 AND creative_id = $2 AND country = $3",
                    [$loser['offer_id'], $loser['creative_id'], $loser['country']]
                );
                // Also deactivate in creatives table
                pg_query_params($db,
                    "UPDATE affiliate.creatives SET status = 'Paused' WHERE id = $1",
                    [$loser['creative_id']]
                );
                $paused++;
                echo "  PAUSED: Offer #{$loser['offer_id']} Creative #{$loser['creative_id']} [{$loser['country']}] - {$loser['click_rate']}% CTR\n";
            }
        }
    }
    
    if ($winners == 0 && $paused == 0) {
        echo "  Not enough data yet (need >$MIN_SENDS_FOR_JUDGMENT sends per creative to judge)\n";
    } else {
        echo "  Result: $winners winners promoted, $paused losers paused\n";
    }
}

// ============================================================
// 3. TRANSLATION ENGINE
// ============================================================
function translate_winners($db) {
    echo "=== TRANSLATING WINNING CREATIVES ===\n";
    
    // Language map by country
    $lang_map = [
        'DE' => 'de', 'AT' => 'de', 'FR' => 'fr', 'IT' => 'it', 'ES' => 'es',
        'UK' => 'en', 'US' => 'en', 'CA' => 'en', 'AU' => 'en', 
        'NZ' => 'en', 'SE' => 'sv', 'FI' => 'fi', 'NL' => 'nl'
    ];
    
    // Find winning creatives that could be translated to new markets
    $sql = "
        SELECT DISTINCT cp.offer_id, cp.creative_id, cp.country, cp.click_rate, cp.score,
               o.countries as offer_countries
        FROM admin.brain_creative_performance cp
        JOIN affiliate.offers o ON o.id = cp.offer_id
        WHERE cp.is_winner = true AND cp.score > 5
        ORDER BY cp.score DESC
    ";
    
    $result = pg_query($db, $sql);
    $translations = 0;
    
    while ($row = pg_fetch_assoc($result)) {
        $source_country = $row['country'];
        $source_lang = $lang_map[$source_country] ?? 'en';
        
        // Check which target languages we could translate to
        // For now: translate DE winners to AT, FR winners to CA-FR, etc.
        $translation_targets = get_translation_targets($source_lang, $lang_map);
        
        foreach ($translation_targets as $target_lang) {
            if ($target_lang === $source_lang) continue;
            
            // Check if translation already exists
            $check = pg_query_params($db,
                "SELECT id FROM admin.brain_creative_translations WHERE source_creative_id = $1 AND target_lang = $2",
                [$row['creative_id'], $target_lang]
            );
            
            if (pg_num_rows($check) > 0) continue;
            
            // Get source creative HTML
            $cr = pg_fetch_assoc(pg_query_params($db,
                "SELECT value, name FROM affiliate.creatives WHERE id = $1",
                [$row['creative_id']]
            ));
            
            if (!$cr) continue;
            
            $source_html = base64_decode($cr['value']);
            
            // Call HAMID IA for translation
            $translated_html = translate_via_hamid($source_html, $source_lang, $target_lang);
            
            if ($translated_html) {
                // Insert translated creative
                $max_id = pg_fetch_result(pg_query($db, "SELECT COALESCE(MAX(id),0)+1 FROM affiliate.creatives"), 0, 0);
                $b64 = base64_encode($translated_html);
                $name = $cr['name'] . "_translated_{$target_lang}";
                
                pg_query_params($db,
                    "INSERT INTO affiliate.creatives (id, status, affiliate_network_id, offer_id, name, value, created_by, created_date, quality_score, has_tracking_placeholders, last_validated)
                     VALUES ($1, 'Activated', 6, $2, $3, $4, 'brain_translator', CURRENT_DATE, 10, true, NOW())",
                    [$max_id, $row['offer_id'], $name, $b64]
                );
                
                // Log translation
                pg_query_params($db,
                    "INSERT INTO admin.brain_creative_translations (source_creative_id, source_lang, target_lang, target_creative_id, translated_by)
                     VALUES ($1, $2, $3, $4, 'hamid_ia')",
                    [$row['creative_id'], $source_lang, $target_lang, $max_id]
                );
                
                $translations++;
                echo "  TRANSLATED: Creative #{$row['creative_id']} ({$source_lang} → {$target_lang}) → New creative #{$max_id}\n";
            }
        }
    }
    
    echo "  Total translations: $translations\n";
    if ($translations == 0) echo "  No winners to translate yet (need winning creatives first)\n";
}

function get_translation_targets($source_lang, $lang_map) {
    $all_langs = array_unique(array_values($lang_map));
    return array_filter($all_langs, fn($l) => $l !== $source_lang);
}

function translate_via_hamid($html, $from_lang, $to_lang) {
    // Use HAMID IA API for translation
    $lang_names = ['de'=>'German','fr'=>'French','en'=>'English','it'=>'Italian','es'=>'Spanish','sv'=>'Swedish','fi'=>'Finnish','nl'=>'Dutch'];
    $from_name = $lang_names[$from_lang] ?? $from_lang;
    $to_name = $lang_names[$to_lang] ?? $to_lang;
    
    // Try local HAMID API first
    $prompt = "Translate this email HTML from {$from_name} to {$to_name}. Keep ALL HTML structure, CSS, and placeholders [url], [open], [unsub] EXACTLY as they are. Only translate the visible text content. Return ONLY the translated HTML, no explanation:\n\n" . $html;
    
    // Use Cerebras (fastest) via HAMID config
    $providers = [
        ['url' => 'https://api.cerebras.ai/v1/chat/completions', 'key' => '', 'model' => 'llama-4-scout-17b-16e-instruct'],
        ['url' => 'https://api.groq.com/openai/v1/chat/completions', 'key' => '', 'model' => 'llama-3.3-70b-versatile'],
    ];
    
    // Load HAMID keys from DB
    global $db;
    foreach (['cerebras', 'groq'] as $i => $provider) {
        $r = pg_query_params($db, "SELECT api_key FROM admin.hamid_providers WHERE name = $1 AND is_active = true", [$provider]);
        if ($r && $row = pg_fetch_assoc($r)) {
            $providers[$i]['key'] = $row['api_key'];
        }
    }
    
    foreach ($providers as $p) {
        if (empty($p['key'])) continue;
        
        $ch = curl_init($p['url']);
        curl_setopt_array($ch, [
            CURLOPT_POST => true,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT => 30,
            CURLOPT_HTTPHEADER => [
                'Content-Type: application/json',
                'Authorization: Bearer ' . $p['key']
            ],
            CURLOPT_POSTFIELDS => json_encode([
                'model' => $p['model'],
                'messages' => [
                    ['role' => 'system', 'content' => 'You are a professional translator for marketing emails. Preserve ALL HTML and placeholders exactly.'],
                    ['role' => 'user', 'content' => $prompt]
                ],
                'max_tokens' => 4000,
                'temperature' => 0.3
            ])
        ]);
        
        $response = curl_exec($ch);
        $code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        curl_close($ch);
        
        if ($code === 200) {
            $data = json_decode($response, true);
            $translated = $data['choices'][0]['message']['content'] ?? '';
            
            // Validate: must contain [url], [open], [unsub]
            if (strpos($translated, '[url]') !== false && 
                strpos($translated, '[open]') !== false &&
                strpos($translated, '<') !== false) {
                return $translated;
            }
        }
    }
    
    return null; // Translation failed
}

// ============================================================
// 4. OFFER AUTO-DISCOVERY
// ============================================================
function discover_offers($db) {
    global $EVERFLOW_API, $EVERFLOW_KEY, $AUTO_APPROVE_SCORE;
    
    echo "=== OFFER AUTO-DISCOVERY ===\n";
    
    // Fetch all Everflow offers
    $ch = curl_init("$EVERFLOW_API/v1/affiliates/alloffers");
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_TIMEOUT => 30,
        CURLOPT_HTTPHEADER => ['X-Eflow-API-Key: ' . $EVERFLOW_KEY]
    ]);
    $response = curl_exec($ch);
    curl_close($ch);
    
    $data = json_decode($response, true);
    $offers = $data['offers'] ?? [];
    
    // Get existing offers in our DB
    $existing = [];
    $r = pg_query($db, "SELECT production_id FROM affiliate.offers WHERE production_id IS NOT NULL");
    while ($row = pg_fetch_assoc($r)) {
        $existing[] = $row['production_id'];
    }
    
    // Get our performance data to understand what works
    $perf = pg_query($db, "
        SELECT country, AVG(click_rate) as avg_ctr, SUM(total_sent) as total_volume
        FROM admin.brain_creative_performance
        WHERE total_sent > 10
        GROUP BY country
    ");
    $country_performance = [];
    while ($row = pg_fetch_assoc($perf)) {
        $country_performance[$row['country']] = $row;
    }
    
    $new_discovered = 0;
    $auto_approved = 0;
    
    foreach ($offers as $offer) {
        $noid = (string)$offer['network_offer_id'];
        
        // Skip if already in DB
        if (in_array($noid, $existing)) continue;
        
        // Skip if already discovered
        $check = pg_query_params($db,
            "SELECT id FROM admin.brain_offer_discovery WHERE external_offer_id = $1 AND source = 'everflow'",
            [$noid]
        );
        if (pg_num_rows($check) > 0) continue;
        
        // Extract country from name
        $name = $offer['name'] ?? '';
        $country = substr($name, 0, 2);
        if (!ctype_alpha($country)) $country = 'WW';
        
        // Detect vertical from name
        $vertical = detect_vertical($name);
        
        // Brain scoring algorithm
        $brain_score = calculate_brain_score($offer, $country, $vertical, $country_performance);
        
        // Build recommendation
        $recommendation = build_recommendation($offer, $country, $vertical, $brain_score, $country_performance);
        
        // Auto-approve if score is high enough
        $status = 'pending';
        $reviewed_by = null;
        $reviewed_at = null;
        
        if ($brain_score >= $AUTO_APPROVE_SCORE) {
            $status = 'approved';
            $reviewed_by = 'brain_auto';
            $reviewed_at = date('Y-m-d H:i:s');
            $auto_approved++;
        }
        
        // Insert discovery
        pg_query_params($db,
            "INSERT INTO admin.brain_offer_discovery 
                (source, external_offer_id, offer_name, country, vertical, tracking_url, brain_score, brain_recommendation, status, reviewed_by, reviewed_at)
             VALUES ('everflow', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10)",
            [$noid, $name, $country, $vertical, $offer['tracking_url'] ?? '', 
             $brain_score, $recommendation, $status, $reviewed_by, $reviewed_at]
        );
        
        $new_discovered++;
        $status_icon = $status === 'approved' ? '✅' : '⏳';
        echo "  $status_icon [{$country}] #{$noid}: " . substr($name, 0, 55) . " (score: {$brain_score})\n";
    }
    
    echo "\n  New discovered: $new_discovered, Auto-approved: $auto_approved\n";
    
    // Show pending offers that need manual review
    $pending = pg_query($db, "SELECT external_offer_id, offer_name, country, brain_score, brain_recommendation 
                              FROM admin.brain_offer_discovery WHERE status = 'pending' ORDER BY brain_score DESC LIMIT 10");
    $pending_count = pg_num_rows($pending);
    if ($pending_count > 0) {
        echo "\n  PENDING MANUAL REVIEW ($pending_count offers):\n";
        while ($row = pg_fetch_assoc($pending)) {
            echo "    [{$row['country']}] #{$row['external_offer_id']}: " . substr($row['offer_name'], 0, 45) . " (score: {$row['brain_score']})\n";
            echo "      → {$row['brain_recommendation']}\n";
        }
    }
}

function detect_vertical($name) {
    $name_lower = strtolower($name);
    if (preg_match('/shein|fashion|mode|moda/', $name_lower)) return 'fashion';
    if (preg_match('/lidl|aldi|walmart|grocery/', $name_lower)) return 'retail';
    if (preg_match('/car|auto|adac|aaa|telepass|vinci/', $name_lower)) return 'automotive';
    if (preg_match('/beauty|lancôme|nocibe|sephora/', $name_lower)) return 'beauty';
    if (preg_match('/health|krankenkasse|medical/', $name_lower)) return 'health';
    if (preg_match('/dating|girls|women|single/', $name_lower)) return 'dating';
    if (preg_match('/loan|credit|finance|insurance/', $name_lower)) return 'finance';
    if (preg_match('/gift.*card|giftcard|voucher/', $name_lower)) return 'giftcard';
    if (preg_match('/tool|diy|bricolage|dexter|parkside/', $name_lower)) return 'diy';
    if (preg_match('/food|coffee|tim horton|restaurant/', $name_lower)) return 'food';
    return 'other';
}

function calculate_brain_score($offer, $country, $vertical, $country_performance) {
    $score = 5.0; // Base score
    
    // +2 if we already have good performance in this country
    if (isset($country_performance[$country]) && (float)$country_performance[$country]['avg_ctr'] > 1.0) {
        $score += 2.0;
    }
    
    // +1 for EU countries (our primary markets)
    if (in_array($country, ['DE', 'FR', 'UK', 'IT', 'ES', 'AT', 'NL'])) {
        $score += 1.0;
    }
    
    // +1 for proven verticals
    if (in_array($vertical, ['retail', 'automotive', 'giftcard', 'diy'])) {
        $score += 1.0;
    }
    
    // -2 for dating (compliance risk)
    if ($vertical === 'dating') {
        $score -= 2.0;
    }
    
    // +0.5 if tracking URL is rivoweb (known good)
    $track = $offer['tracking_url'] ?? '';
    if (strpos($track, 'rivoweb.com') !== false) {
        $score += 0.5;
    }
    
    return min(round($score, 1), 10.0);
}

function build_recommendation($offer, $country, $vertical, $score, $country_performance) {
    $reasons = [];
    
    if ($score >= 7) {
        $reasons[] = "HIGH PRIORITY";
    } elseif ($score >= 5) {
        $reasons[] = "MODERATE INTEREST";
    } else {
        $reasons[] = "LOW PRIORITY";
    }
    
    if (isset($country_performance[$country])) {
        $ctr = $country_performance[$country]['avg_ctr'];
        $reasons[] = "{$country} avg CTR: {$ctr}%";
    } else {
        $reasons[] = "No performance data for {$country} yet";
    }
    
    $reasons[] = "Vertical: {$vertical}";
    
    if ($vertical === 'dating') {
        $reasons[] = "⚠️ Dating vertical - compliance review needed";
    }
    
    return implode(' | ', $reasons);
}

// ============================================================
// 5. FULL CYCLE
// ============================================================
function full_cycle($db) {
    echo "========================================\n";
    echo "  BRAIN CREATIVE ENGINE - FULL CYCLE\n";
    echo "  " . date('Y-m-d H:i:s') . "\n";
    echo "========================================\n\n";
    
    calculate_performance($db);
    echo "\n";
    promote_winners($db);
    echo "\n";
    translate_winners($db);
    echo "\n";
    discover_offers($db);
    echo "\n";
    
    show_status($db);
}

// ============================================================
// STATUS
// ============================================================
function show_status($db) {
    echo "=== BRAIN CREATIVE ENGINE STATUS ===\n";
    
    // Creative performance stats
    $r = pg_fetch_assoc(pg_query($db, "SELECT COUNT(*) as total, COUNT(*) FILTER (WHERE is_winner) as winners, COUNT(*) FILTER (WHERE is_paused) as paused FROM admin.brain_creative_performance"));
    echo "  Creative performances tracked: {$r['total']} (Winners: {$r['winners']}, Paused: {$r['paused']})\n";
    
    // Top performers
    $top = pg_query($db, "
        SELECT cp.offer_id, LEFT(o.name,35) as offer, cp.country, cp.click_rate, cp.open_rate, cp.total_sent, cp.score
        FROM admin.brain_creative_performance cp
        JOIN affiliate.offers o ON o.id = cp.offer_id
        WHERE cp.total_sent >= 10
        ORDER BY cp.score DESC LIMIT 5
    ");
    echo "\n  TOP 5 CREATIVE PERFORMERS:\n";
    while ($row = pg_fetch_assoc($top)) {
        echo "    [{$row['country']}] Offer #{$row['offer_id']} - CTR: {$row['click_rate']}% | Open: {$row['open_rate']}% | Sends: {$row['total_sent']} | Score: {$row['score']}\n";
    }
    
    // Subject performance
    $subj = pg_fetch_assoc(pg_query($db, "SELECT COUNT(*) as total FROM admin.brain_subject_performance"));
    echo "\n  Subject performances tracked: {$subj['total']}\n";
    
    // Translations
    $trans = pg_fetch_assoc(pg_query($db, "SELECT COUNT(*) as total FROM admin.brain_creative_translations"));
    echo "  Translations done: {$trans['total']}\n";
    
    // Discovery
    $disc = pg_query($db, "SELECT status, COUNT(*) as c FROM admin.brain_offer_discovery GROUP BY status ORDER BY status");
    echo "\n  OFFER DISCOVERY:\n";
    while ($row = pg_fetch_assoc($disc)) {
        echo "    {$row['status']}: {$row['c']}\n";
    }
    
    // Approved offers summary
    $approved = pg_fetch_assoc(pg_query($db, "SELECT COUNT(*) as c FROM admin.brain_offer_config WHERE is_approved = true AND is_active = true AND good_creatives > 0"));
    echo "\n  PRODUCTION READY OFFERS: {$approved['c']}\n";
}

?>