<?php
// Simple database test for bonus tables
$host = 'localhost';
$dbname = 'blacrhdy_bet1';
$username = 'blacrhdy_bet1';
$password = 'blacrhdy_bet1';

try {
    $db = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "<h2>Database Connection Test</h2>";
    echo "<p>Connection successful!</p>";
    
    echo "<h2>Personalized Bonuses Table</h2>";
    $stmt = $db->query("SELECT COUNT(*) as count FROM personalized_bonuses");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "<p>Total personalized bonuses: <strong>" . $result['count'] . "</strong></p>";
    
    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 5");
        $bonuses = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo "<table border='1' cellpadding='5'>";
        echo "<tr><th>ID</th><th>User</th><th>Bonus</th><th>Amount</th><th>Status</th><th>Created</th></tr>";
        foreach ($bonuses as $bonus) {
            echo "<tr>";
            echo "<td>" . $bonus['id'] . "</td>";
            echo "<td>" . ($bonus['username'] ?? 'N/A') . "</td>";
            echo "<td>" . ($bonus['bonus_name'] ?? 'N/A') . "</td>";
            echo "<td>" . $bonus['amount'] . "</td>";
            echo "<td>" . $bonus['status'] . "</td>";
            echo "<td>" . $bonus['created_at'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
    echo "<h2>Bonus Talepleri Table</h2>";
    $stmt = $db->query("SELECT COUNT(*) as count FROM bonus_talepleri");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "<p>Total bonus talepleri: <strong>" . $result['count'] . "</strong></p>";
    
    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 5");
        $talepler = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo "<table border='1' cellpadding='5'>";
        echo "<tr><th>ID</th><th>User</th><th>Bonus Türü</th><th>Miktar</th><th>Durum</th><th>Tarih</th></tr>";
        foreach ($talepler as $talep) {
            echo "<tr>";
            echo "<td>" . $talep['id'] . "</td>";
            echo "<td>" . ($talep['username'] ?? 'N/A') . "</td>";
            echo "<td>" . $talep['bonus_turu'] . "</td>";
            echo "<td>" . $talep['miktar'] . "</td>";
            echo "<td>" . $talep['durum'] . "</td>";
            echo "<td>" . $talep['tarih'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
    echo "<h2>Combined Query Test</h2>";
    $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 10";
    
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $combined = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "<p>Combined query results: <strong>" . count($combined) . "</strong> rows</p>";
    
    if (count($combined) > 0) {
        echo "<table border='1' cellpadding='5'>";
        echo "<tr><th>ID</th><th>User</th><th>Bonus</th><th>Amount</th><th>Status</th><th>Source</th><th>Created</th></tr>";
        foreach ($combined as $row) {
            echo "<tr>";
            echo "<td>" . $row['id'] . "</td>";
            echo "<td>" . ($row['username'] ?? 'N/A') . "</td>";
            echo "<td>" . ($row['bonus_name'] ?? 'N/A') . "</td>";
            echo "<td>" . $row['amount'] . "</td>";
            echo "<td>" . $row['status'] . "</td>";
            echo "<td>" . $row['source_type'] . "</td>";
            echo "<td>" . $row['created_at'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
} catch (PDOException $e) {
    echo "<h2>Database Error</h2>";
    echo "<p style='color: red;'>" . $e->getMessage() . "</p>";
} catch (Exception $e) {
    echo "<h2>General Error</h2>";
    echo "<p style='color: red;'>" . $e->getMessage() . "</p>";
}
?>