<?php
// Test: Bonus veritabanı bağlantısı ve sorguları

$host = 'localhost';
$dbname = 'blacrhdy_bet1';
$username = 'blacrhdy_bet1';
$password = 'blacrhdy_bet1';

try {
    echo "=== BONUS VERITABANI TESTI ===\n\n";
    
    // Bağlantı kur
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "✓ Veritabanına bağlantı başarılı\n\n";
    
    // 1. Tablo yapısını kontrol et
    echo "1. Tablo Yapısı Kontrolü:\n";
    
    // personalized_bonuses tablosu
    $stmt = $pdo->query("DESCRIBE personalized_bonuses");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "   personalized_bonuses sütunları:\n";
    foreach ($columns as $col) {
        echo "     - " . $col['Field'] . " (" . $col['Type'] . ")\n";
    }
    
    // bonus_talepleri tablosu  
    $stmt = $pdo->query("DESCRIBE bonus_talepleri");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "   \n   bonus_talepleri sütunları:\n";
    foreach ($columns as $col) {
        echo "     - " . $col['Field'] . " (" . $col['Type'] . ")\n";
    }
    
    echo "\n2. Veri Kontrolü:\n";
    
    // personalized_bonuses verileri
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM personalized_bonuses");
    $count = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "   personalized_bonuses kayıt sayısı: " . $count['count'] . "\n";
    
    if ($count['count'] > 0) {
        $stmt = $pdo->query("SELECT * FROM personalized_bonuses ORDER BY id DESC LIMIT 2");
        $samples = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo "   Son 2 kayıt:\n";
        foreach ($samples as $sample) {
            echo "     ID:" . $sample['id'] . " User:" . $sample['user_id'] . " Amount:" . $sample['amount'] . " Status:" . $sample['status'] . "\n";
        }
    }
    
    // bonus_talepleri verileri
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM bonus_talepleri");
    $count = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "   \n   bonus_talepleri kayıt sayısı: " . $count['count'] . "\n";
    
    if ($count['count'] > 0) {
        $stmt = $pdo->query("SELECT * FROM bonus_talepleri ORDER BY id DESC LIMIT 2");
        $samples = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo "   Son 2 kayıt:\n";
        foreach ($samples as $sample) {
            echo "     ID:" . $sample['id'] . " User:" . $sample['uye_id'] . " Amount:" . $sample['miktar'] . " Durum:" . $sample['durum'] . "\n";
        }
    }
    
    // kullanicilar tablosu
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM kullanicilar");
    $count = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "   \n   kullanicilar kayıt sayısı: " . $count['count'] . "\n";
    
    echo "\n3. JOIN Testi:\n";
    
    // JOIN sorgularını test et
    $stmt = $pdo->query("SELECT pb.id, pb.user_id, k.username, pb.amount, pb.status, pb.created_at, 'personalized' as source_type, pb.bonus_type_id, NULL as request_status
                FROM personalized_bonuses pb 
                LEFT JOIN kullanicilar k ON pb.user_id = k.id 
                LEFT JOIN bonus_types bt ON pb.bonus_type_id = bt.id
                ORDER BY pb.id DESC LIMIT 2");
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "   personalized_bonuses JOIN sonucu: " . count($results) . " kayıt\n";
    foreach ($results as $row) {
        echo "     ID:" . $row['id'] . " User:" . $row['username'] . " (" . $row['user_id'] . ") Amount:" . $row['amount'] . "\n";
    }
    
    echo "\n4. UNION Testi:\n";
    
    // UNION sorgusunu test et
    $unionSql = "(SELECT 
                    pb.id, 
                    pb.user_id, 
                    k.username, 
                    bt.name as bonus_name, 
                    pb.amount, 
                    pb.status, 
                    pb.created_at,
                    'personalized' as source_type,
                    pb.bonus_type_id,
                    NULL as request_status
                FROM personalized_bonuses pb 
                LEFT JOIN kullanicilar k ON pb.user_id = k.id 
                LEFT JOIN bonus_types bt ON pb.bonus_type_id = bt.id)
                UNION ALL
                (SELECT 
                    btalep.id, 
                    btalep.uye_id as user_id, 
                    k.username, 
                    btalep.bonus_turu as bonus_name, 
                    btalep.miktar as amount, 
                    CASE btalep.durum 
                        WHEN 0 THEN 'pending' 
                        WHEN 1 THEN 'approved' 
                        WHEN 2 THEN 'rejected' 
                    END as status, 
                    btalep.tarih as created_at,
                    'request' as source_type,
                    NULL as bonus_type_id,
                    btalep.durum as request_status
                FROM bonus_talepleri btalep
                LEFT JOIN kullanicilar k ON btalep.uye_id = k.id)
                ORDER BY created_at DESC LIMIT 5";
    
    $stmt = $pdo->query($unionSql);
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "   UNION sorgu sonucu: " . count($results) . " kayıt\n";
    foreach ($results as $row) {
        echo "     ID:" . $row['id'] . " User:" . $row['username'] . " Amount:" . $row['amount'] . " Source:" . $row['source_type'] . "\n";
    }
    
    echo "\n=== TEST TAMAMLANDI ===\n";
    
} catch (PDOException $e) {
    echo "HATA: " . $e->getMessage() . "\n";
    echo "Hata kodu: " . $e->getCode() . "\n";
    echo "Hata detayı: " . $e->getTraceAsString() . "\n";
}