<?php
// Simple database connection test and table check
$host = 'localhost';
$dbname = 'blacrhdy_bet1';
$username = 'blacrhdy_bet1';
$password = 'blacrhdy_bet1';

try {
    echo "<h1>Database Connection Test</h1>";
    $db = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "<p style='color: green;'>✓ Database connection successful!</p>";
    
    echo "<h2>Table Status Check</h2>";
    
    // Check personalized_bonuses table
    try {
        $stmt = $db->query("SELECT COUNT(*) as count FROM personalized_bonuses");
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        echo "<p>✓ personalized_bonuses table: <strong>" . $result['count'] . "</strong> records</p>";
        
        if ($result['count'] > 0) {
            $stmt = $db->query("SELECT pb.id, pb.user_id, k.username, pb.amount, pb.status, pb.created_at 
                               FROM personalized_bonuses pb 
                               LEFT JOIN kullanicilar k ON pb.user_id = k.id 
                               ORDER BY pb.created_at DESC LIMIT 2");
            $bonuses = $stmt->fetchAll(PDO::FETCH_ASSOC);
            echo "<p><strong>Sample data:</strong></p>";
            foreach ($bonuses as $bonus) {
                echo "<p>ID: " . $bonus['id'] . " | User: " . ($bonus['username'] ?? 'N/A') . " | Amount: " . $bonus['amount'] . " | Status: " . $bonus['status'] . "</p>";
            }
        }
    } catch (Exception $e) {
        echo "<p style='color: red;'>✗ personalized_bonuses table error: " . $e->getMessage() . "</p>";
    }
    
    // Check bonus_talepleri table
    try {
        $stmt = $db->query("SELECT COUNT(*) as count FROM bonus_talepleri");
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        echo "<p>✓ bonus_talepleri table: <strong>" . $result['count'] . "</strong> records</p>";
        
        if ($result['count'] > 0) {
            $stmt = $db->query("SELECT bt.id, bt.uye_id, k.username, bt.bonus_turu, bt.miktar, bt.durum, bt.tarih 
                               FROM bonus_talepleri bt 
                               LEFT JOIN kullanicilar k ON bt.uye_id = k.id 
                               ORDER BY bt.tarih DESC LIMIT 2");
            $talepler = $stmt->fetchAll(PDO::FETCH_ASSOC);
            echo "<p><strong>Sample data:</strong></p>";
            foreach ($talepler as $talep) {
                echo "<p>ID: " . $talep['id'] . " | User: " . ($talep['username'] ?? 'N/A') . " | Miktar: " . $talep['miktar'] . " | Durum: " . $talep['durum'] . "</p>";
            }
        }
    } catch (Exception $e) {
        echo "<p style='color: red;'>✗ bonus_talepleri table error: " . $e->getMessage() . "</p>";
    }
    
    echo "<h2>Combined Query Test</h2>";
    
    // Test the combined query
    $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";
    
    try {
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $combined = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo "<p>✓ Combined query successful: <strong>" . count($combined) . "</strong> results</p>";
        
        if (count($combined) > 0) {
            echo "<table border='1' cellpadding='5' style='border-collapse: collapse;'>";
            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 (Exception $e) {
        echo "<p style='color: red;'>✗ Combined query error: " . $e->getMessage() . "</p>";
    }
    
} catch (PDOException $e) {
    echo "<p style='color: red;'>✗ Database connection failed: " . $e->getMessage() . "</p>";
}
?>