<?php
/**
 * SYNC WEVADS DATA TO DELIVERADS
 * Import existing configuration and data
 */
$pdo = new PDO("pgsql:host=localhost;dbname=adx_system", "admin", "admin123", [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

echo "🔄 SYNCING WEVADS DATA TO DELIVERADS...\n\n";

// ============================================
// 1. SYNC AI PROVIDER KEYS
// ============================================
echo "1️⃣ Syncing AI Provider Keys...\n";

// Check possible source tables
$sources = ['hamid_providers', 'ai_providers', 'api_providers', 'ai_config'];
$foundTable = null;

foreach ($sources as $table) {
    try {
        $count = $pdo->query("SELECT COUNT(*) FROM admin.$table")->fetchColumn();
        if ($count > 0) {
            $foundTable = $table;
            echo "   Found: admin.$table ($count records)\n";
            break;
        }
    } catch (Exception $e) {
        continue;
    }
}

if ($foundTable) {
    // Get column names
    $cols = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_schema = 'admin' AND table_name = '$foundTable'")->fetchAll(PDO::FETCH_COLUMN);
    echo "   Columns: " . implode(', ', $cols) . "\n";
    
    // Show data
    $data = $pdo->query("SELECT * FROM admin.$foundTable LIMIT 5")->fetchAll(PDO::FETCH_ASSOC);
    foreach ($data as $row) {
        $name = $row['provider_name'] ?? $row['name'] ?? $row['provider'] ?? 'unknown';
        $hasKey = !empty($row['api_key']);
        echo "   - $name: " . ($hasKey ? '✓ Key configured' : '✗ No key') . "\n";
    }
}

// ============================================
// 2. SYNC HUAWEI ACCOUNTS
// ============================================
echo "\n2️⃣ Syncing Huawei Accounts...\n";

try {
    $huawei = $pdo->query("SELECT * FROM admin.huawei_accounts WHERE is_active = true")->fetchAll(PDO::FETCH_ASSOC);
    echo "   Found " . count($huawei) . " active Huawei accounts\n";
    
    foreach ($huawei as $acc) {
        $name = $acc['name'] ?? 'Main';
        $region = $acc['region'] ?? $acc['default_region'] ?? 'af-south-1';
        $hasAK = !empty($acc['ak']) || !empty($acc['access_key']);
        echo "   - $name ($region): " . ($hasAK ? '✓ Configured' : '✗ No credentials') . "\n";
    }
} catch (Exception $e) {
    echo "   ⚠ No Huawei accounts table found\n";
}

// ============================================
// 3. SYNC O365 ACCOUNTS
// ============================================
echo "\n3️⃣ Syncing O365 Accounts...\n";

$o365Tables = ['o365_accounts', 'office365_accounts', 'smtp_accounts'];
foreach ($o365Tables as $table) {
    try {
        $count = $pdo->query("SELECT COUNT(*) FROM admin.$table")->fetchColumn();
        if ($count > 0) {
            echo "   Found: admin.$table ($count accounts)\n";
            
            // Get status breakdown
            try {
                $statuses = $pdo->query("SELECT status, COUNT(*) as cnt FROM admin.$table GROUP BY status")->fetchAll(PDO::FETCH_ASSOC);
                foreach ($statuses as $s) {
                    echo "   - {$s['status']}: {$s['cnt']} accounts\n";
                }
            } catch (Exception $e) {
                // No status column
            }
            
            // Check for aged accounts
            try {
                $aged = $pdo->query("SELECT COUNT(*) FROM admin.$table WHERE created_at < NOW() - INTERVAL '90 days'")->fetchColumn();
                echo "   - Aged (90+ days): $aged accounts\n";
            } catch (Exception $e) {}
            
            break;
        }
    } catch (Exception $e) {
        continue;
    }
}

// ============================================
// 4. SYNC SPONSORS & OFFERS
// ============================================
echo "\n4️⃣ Syncing Sponsors & Offers...\n";

try {
    $sponsors = $pdo->query("SELECT * FROM admin.sponsors WHERE is_active = true OR is_active IS NULL")->fetchAll(PDO::FETCH_ASSOC);
    echo "   Found " . count($sponsors) . " sponsors\n";
    
    foreach ($sponsors as $sp) {
        $name = $sp['name'] ?? $sp['sponsor_name'] ?? 'Unknown';
        echo "   - $name\n";
    }
} catch (Exception $e) {
    echo "   ⚠ Checking alternate tables...\n";
}

try {
    $offers = $pdo->query("SELECT COUNT(*) FROM admin.offers")->fetchColumn();
    echo "   Found $offers offers total\n";
    
    // Get by vertical
    $verticals = $pdo->query("SELECT vertical, COUNT(*) as cnt FROM admin.offers GROUP BY vertical ORDER BY cnt DESC LIMIT 5")->fetchAll(PDO::FETCH_ASSOC);
    foreach ($verticals as $v) {
        echo "   - {$v['vertical']}: {$v['cnt']} offers\n";
    }
} catch (Exception $e) {}

// ============================================
// 5. SYNC EMAIL DATA
// ============================================
echo "\n5️⃣ Syncing Email Data...\n";

$dataTables = ['emails', 'contacts', 'data_profiles', 'subscribers', 'leads'];
foreach ($dataTables as $table) {
    try {
        $count = $pdo->query("SELECT COUNT(*) FROM admin.$table")->fetchColumn();
        if ($count > 0) {
            echo "   Found: admin.$table ($count records)\n";
            break;
        }
    } catch (Exception $e) {
        continue;
    }
}

// ============================================
// 6. SYNC DOMAINS
// ============================================
echo "\n6️⃣ Syncing Domains...\n";

try {
    $domains = $pdo->query("SELECT COUNT(*) FROM admin.domains")->fetchColumn();
    echo "   Found $domains domains\n";
    
    $active = $pdo->query("SELECT domain, status FROM admin.domains WHERE status = 'active' OR is_active = true LIMIT 10")->fetchAll(PDO::FETCH_ASSOC);
    foreach ($active as $d) {
        echo "   - {$d['domain']}\n";
    }
} catch (Exception $e) {
    echo "   ⚠ No domains table found\n";
}

// ============================================
// 7. SYNC CLOUDFLARE
// ============================================
echo "\n7️⃣ Syncing Cloudflare...\n";

try {
    $cf = $pdo->query("SELECT * FROM admin.cloudflare_accounts LIMIT 1")->fetch(PDO::FETCH_ASSOC);
    if ($cf) {
        echo "   Found Cloudflare config\n";
        echo "   - Email: " . ($cf['email'] ?? 'N/A') . "\n";
        $hasKey = !empty($cf['api_key']) || !empty($cf['api_token']);
        echo "   - API: " . ($hasKey ? '✓ Configured' : '✗ Not configured') . "\n";
    }
} catch (Exception $e) {
    echo "   ⚠ No Cloudflare config found\n";
}

// ============================================
// 8. CHECK WINNING CONFIGS
// ============================================
echo "\n8️⃣ Checking Winning Configurations...\n";

try {
    // Check for campaign stats
    $stats = $pdo->query("SELECT * FROM admin.campaign_stats ORDER BY open_rate DESC LIMIT 5")->fetchAll(PDO::FETCH_ASSOC);
    if ($stats) {
        echo "   Top performing campaigns:\n";
        foreach ($stats as $s) {
            echo "   - Campaign {$s['campaign_id']}: {$s['open_rate']}% open rate\n";
        }
    }
} catch (Exception $e) {}

try {
    // Check ISP performance
    $isp = $pdo->query("SELECT isp, AVG(success_rate) as rate FROM admin.isp_stats GROUP BY isp ORDER BY rate DESC LIMIT 5")->fetchAll(PDO::FETCH_ASSOC);
    if ($isp) {
        echo "   Best performing ISPs:\n";
        foreach ($isp as $i) {
            echo "   - {$i['isp']}: " . round($i['rate'], 1) . "% success\n";
        }
    }
} catch (Exception $e) {}

echo "\n✅ SYNC COMPLETE!\n";