<?php
$host = 'localhost';
$dbname = 'blacrhdy_bet1';
$username = 'blacrhdy_bet1';
$password = 'blacrhdy_bet1';

// PDO ile veritabanına bağlanma
try {
    $db = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Database connection successful!<br><br>";
    
    // Check personalized_bonuses table
    echo "=== PERSONALIZED_BONUSES TABLE ===<br>";
    $stmt = $db->query("SELECT COUNT(*) as count FROM personalized_bonuses");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "Total personalized bonuses: " . $result['count'] . "<br>";
    
    if ($result['count'] > 0) {
        $stmt = $db->query("SELECT pb.*, k.username, bt.name as bonus_name 
                            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.created_at DESC LIMIT 3");
        $bonuses = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo "First 3 personalized bonuses:<br>";
        foreach ($bonuses as $bonus) {
            echo "ID: " . $bonus['id'] . ", User: " . ($bonus['username'] ?? 'N/A') . ", Amount: " . $bonus['amount'] . ", Status: " . $bonus['status'] . ", Created: " . $bonus['created_at'] . "<br>";
        }
    }
    
    echo "<br>=== BONUS_TALEPLERI TABLE ===<br>";
    $stmt = $db->query("SELECT COUNT(*) as count FROM bonus_talepleri");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "Total bonus talepleri: " . $result['count'] . "<br>";
    
    if ($result['count'] > 0) {
        $stmt = $db->query("SELECT btalep.*, k.username 
                            FROM bonus_talepleri btalep 
                            LEFT JOIN kullanicilar k ON btalep.uye_id = k.id 
                            ORDER BY btalep.tarih DESC LIMIT 3");
        $talepler = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo "First 3 bonus talepleri:<br>";
        foreach ($talepler as $talep) {
            echo "ID: " . $talep['id'] . ", User: " . ($talep['username'] ?? 'N/A') . ", Miktar: " . $talep['miktar'] . ", Durum: " . $talep['durum'] . ", Tarih: " . $talep['tarih'] . "<br>";
        }
    }
    
    echo "<br>=== COMBINED QUERY TEST ===<br>";
    $sql = "(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 = $db->prepare($sql);
    $stmt->execute();
    $combined = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "Combined query results: " . count($combined) . " rows<br>";
    
    if (count($combined) > 0) {
        echo "First 5 combined results:<br>";
        foreach ($combined as $row) {
            echo "ID: " . $row['id'] . ", User: " . ($row['username'] ?? 'N/A') . ", Amount: " . $row['amount'] . ", Status: " . $row['status'] . ", Source: " . $row['source_type'] . ", Created: " . $row['created_at'] . "<br>";
        }
    }
    
} catch (PDOException $e) {
    echo "Database error: " . $e->getMessage() . "<br>";
} catch (Exception $e) {
    echo "General error: " . $e->getMessage() . "<br>";
}
?>