<?php 
include '../config.php';

// Toplam üye ve satış sayısını saklamak için değişkenleri tanımla
$total_members = 0;
$total_sales = 0;

// Varsayılan olarak bugünün tarihini al
$selected_date = isset($_POST["selected_date"]) ? $_POST["selected_date"] : date("Y-m-d");

// Seçilen tarihi Unix zaman damgasına dönüştür
$selected_date_timestamp = strtotime($selected_date);

// Seçilen tarihi kullanarak günü belirle
$start_date = date("Y-m-d", $selected_date_timestamp);
$end_date = date("Y-m-d", strtotime("+1 day", $selected_date_timestamp));

// Üye kayıtları için sorguyu hazırla
$member_query = "SELECT 
    h.hour,
    COALESCE(COUNT(k.user_kayit), 0) as count
FROM 
    (SELECT 0 as hour 
     UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
     UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 
     UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 
     UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 
     UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 
     UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 
     UNION ALL SELECT 22 UNION ALL SELECT 23) h
LEFT JOIN kullanicilar k ON HOUR(FROM_UNIXTIME(k.user_kayit)) = h.hour 
    AND DATE(FROM_UNIXTIME(k.user_kayit)) = :selected_date
GROUP BY h.hour
ORDER BY h.hour";

// Satışlar için sorguyu hazırla
$sales_query = "SELECT 
    h.hour,
    COALESCE(COUNT(o.tarih), 0) as count
FROM 
    (SELECT 0 as hour 
     UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
     UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 
     UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 
     UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 
     UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 
     UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 
     UNION ALL SELECT 22 UNION ALL SELECT 23) h
LEFT JOIN odemeler o ON HOUR(FROM_UNIXTIME(o.tarih)) = h.hour 
    AND DATE(FROM_UNIXTIME(o.tarih)) = :selected_date
GROUP BY h.hour
ORDER BY h.hour";

// Üye kayıtları için sorguyu hazırla
$member_statement = $db->prepare($member_query);
$member_statement->bindParam(':selected_date', $start_date);

// Satışlar için sorguyu hazırla
$sales_statement = $db->prepare($sales_query);
$sales_statement->bindParam(':selected_date', $start_date);

// Veritabanı işlemlerini try-catch bloğuna almak
try {
    $member_statement->execute();
    $member_result = $member_statement->fetchAll(PDO::FETCH_ASSOC);

    $sales_statement->execute();
    $sales_result = $sales_statement->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    // Hata yönetimi
    error_log("Database Error: " . $e->getMessage());
    echo "Bir hata oluştu. Lütfen daha sonra tekrar deneyiniz.";
}
?>
<!DOCTYPE html>
<html lang="tr">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Kayıt ve Satış Sayısı</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f8f9fa;
            margin: 0;
            padding: 20px;
            display: flex;
            justify-content: center;
            align-items: center;
            min-height: 100vh;
        }

        .container {
            background-color: #fff;
            border-radius: 10px;
            padding: 20px;
            box-shadow: 0px 0px 10px rgba(0, 0, 0, 0.1);
            max-width: 500px;
        }

        h2 {
            color: #007bff;
            margin-bottom: 20px;
            text-align: center;
        }

        table {
            width: 100%;
            border-collapse: collapse;
        }

        th, td {
            padding: 10px;
            text-align: left;
            border: 1px solid #dee2e6;
            line-height: 0.7;
        }

        th {
            background-color: #f2f2f2;
            color: #333;
        }

        p {
            text-align: center;
            margin-top: 20px;
        }

        /* Mobil uyumluluk için */
        @media (max-width: 768px) {
            table {
                font-size: 14px;
            }
            th, td {
                padding: 5px;
            }
        }
        
        /* Sıfır değerleri için stil */
        .zero-value {
            color: #999;
        }
    </style>
</head>
<body>
    <div class="container">
        <form method="post" class="mb-4" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
            <div class="form-group">
                <div class="d-flex justify-content-between align-items-center">
                    <input type="date" id="selected_date" name="selected_date" 
                           value="<?php echo $selected_date; ?>" class="form-control mr-2">
                    <button type="submit" class="btn btn-primary">Göster</button>
                </div>
            </div>
        </form>
        <div id="loading" class="text-center d-none">
            <div class="spinner-border text-primary" role="status">
                <span class="sr-only">Yükleniyor...</span>
            </div>
        </div>
        
        <table>
            <tr>
                <th>Saat Aralığı</th>
                <th>Üye Sayısı</th>
                <th>Satış Sayısı</th>
            </tr>
            <?php
            // Her saat aralığı için üye ve satış sayısını ekrana yazdır
            for ($hour = 0; $hour < 24; $hour++) {
                $hour_text = sprintf("%02d:00 - %02d:59", $hour, $hour);
                $member_count = 0;
                $sales_count = 0;

                // Üye sayısını al
                foreach ($member_result as $row) {
                    if ($row['hour'] == $hour) {
                        $member_count = $row['count'];
                        $total_members += $member_count;
                        break;
                    }
                }

                // Satış sayısını al
                foreach ($sales_result as $row) {
                    if ($row['hour'] == $hour) {
                        $sales_count = $row['count'];
                        $total_sales += $sales_count;
                        break;
                    }
                }

                echo "<tr>";
                echo "<td>$hour_text</td>";
                echo "<td><b" . ($member_count == 0 ? ' class="zero-value"' : '') . ">$member_count</b></td>";
                echo "<td><b" . ($sales_count == 0 ? ' class="zero-value"' : '') . ">$sales_count</b></td>";
                echo "</tr>";
            }

            // Tablo sonunda toplam satırı
            echo "<tr class='table-info font-weight-bold'>";
            echo "<td>TOPLAM</td>";
            echo "<td>$total_members Üye</td>";
            echo "<td>$total_sales Satış</td>";
            echo "</tr>";
            ?>
        </table>

        <div class='alert alert-info mt-3'>
            <small>
                <?php
                // En yoğun saati bulma
                $max_member_hour = array_reduce($member_result, function($carry, $item) {
                    return ($carry['count'] ?? 0) > $item['count'] ? $carry : $item;
                }, ['hour' => 0, 'count' => 0]);

                echo "En yoğun kayıt saati: " . sprintf("%02d:00-%02d:59", $max_member_hour['hour'], $max_member_hour['hour']);
                echo " (" . $max_member_hour['count'] . " kayıt)<br>";
                echo "Ortalama saatlik kayıt: " . round($total_members / 24, 1);
                ?>
            </small>
        </div>
        
        <script>
        document.querySelector('form').addEventListener('submit', function() {
            document.getElementById('loading').classList.remove('d-none');
        });
        </script>
    </div>
</body>
</html>
