<?php
/**
 * Konum: /magaza/admin/sayac/mus.php
 * İşlev: Müşteri Etkileşim, Dönüşüm ve Potansiyel Satış Analizi
 */
date_default_timezone_set('Europe/Istanbul');
require_once '../../includes/db.php';
require_once '../../includes/functions.php';

adminCheck(); // Yetki kontrolü

// --- FİLTRE VERİLERİNİ AL ---
// Varsayılan olarak son 30 günü göster
$date_start = $_GET['date_start'] ?? date('Y-m-d', strtotime('-30 days'));
$date_end = $_GET['date_end'] ?? date('Y-m-d');
$category_id = isset($_GET['category_id']) && $_GET['category_id'] !== '' ? (int)$_GET['category_id'] : '';

// --- KATEGORİLERİ ÇEK (Filtre Dropdown'u İçin) ---
$categories = $db->query("SELECT id, name FROM categories WHERE status = 1 ORDER BY name ASC")->fetchAll(PDO::FETCH_ASSOC);

// --- SQL ŞARTLARINI OLUŞTUR ---
$catWhereStr = $category_id ? " AND p.category_id = {$category_id} " : "";
$startDateStr = $date_start . " 00:00:00";
$endDateStr = $date_end . " 23:59:59";

// 1. SEPETTE BEKLEYENLER (Tarih ve Kategoriye Göre)
$inCartSql = "
    SELECT p.name as product_name, p.slug, p.price, p.discount_price, SUM(ci.quantity) as total_in_cart 
    FROM cart_items ci 
    JOIN products p ON ci.product_id = p.id 
    WHERE ci.created_at BETWEEN '$startDateStr' AND '$endDateStr' $catWhereStr
    GROUP BY ci.product_id 
    ORDER BY total_in_cart DESC 
    LIMIT 20
";
$inCartProducts = $db->query($inCartSql)->fetchAll(PDO::FETCH_ASSOC);

// 2. EN ÇOK FAVORİLENENLER
$favProductsSql = "
    SELECT p.name as product_name, p.slug, p.price, p.discount_price, COUNT(f.id) as fav_count 
    FROM favorites f 
    JOIN products p ON f.product_id = p.id 
    WHERE f.created_at BETWEEN '$startDateStr' AND '$endDateStr' $catWhereStr
    GROUP BY f.product_id 
    ORDER BY fav_count DESC 
    LIMIT 20
";
$favProducts = $db->query($favProductsSql)->fetchAll(PDO::FETCH_ASSOC);

// 3. FİYAT ALARMI BEKLEYENLER
$alertProductsSql = "
    SELECT p.name as product_name, p.slug, p.price, p.discount_price, COUNT(pa.id) as alert_count 
    FROM price_alerts pa 
    JOIN products p ON pa.product_id = p.id 
    WHERE pa.created_at BETWEEN '$startDateStr' AND '$endDateStr' AND pa.status = 0 $catWhereStr
    GROUP BY pa.product_id 
    ORDER BY alert_count DESC 
    LIMIT 20
";
$alertProducts = $db->query($alertProductsSql)->fetchAll(PDO::FETCH_ASSOC);

require_once '../header.php';
?>

<div class="row mb-4 align-items-center text-dark">
    <div class="col-md-8">
        <h2 class="fw-bold mb-0"><i class="bi bi-magnet-fill text-danger me-2"></i>Müşteri Etkileşim Analizi</h2>
        <p class="text-muted small mb-0 mt-1">Ziyaretçilerin favoriye aldığı, sepette unuttuğu ve indirim beklediği ürünlerin detaylı raporu.</p>
    </div>
    <div class="col-md-4 text-md-end mt-3 mt-md-0">
        <a href="stats.php" class="btn btn-light border shadow-sm fw-bold"><i class="bi bi-pie-chart-fill me-1"></i> Trafik Raporu</a>
    </div>
</div>

<div class="card shadow-sm border-0 mb-4">
    <div class="card-body bg-light rounded-3 p-3">
        <form action="mus.php" method="GET" class="row g-2 align-items-center">
            <div class="col-md-auto">
                <span class="fw-bold text-muted ms-2"><i class="bi bi-calendar-range me-1"></i> Tarih Aralığı:</span>
            </div>
            <div class="col-md-3">
                <input type="date" name="date_start" class="form-control border-0 shadow-sm fw-bold text-muted" value="<?= htmlspecialchars($date_start) ?>">
            </div>
            <div class="col-md-auto text-center">
                <i class="bi bi-arrow-right text-muted d-none d-md-inline"></i>
            </div>
            <div class="col-md-3">
                <input type="date" name="date_end" class="form-control border-0 shadow-sm fw-bold text-muted" value="<?= htmlspecialchars($date_end) ?>">
            </div>
            <div class="col-md-3">
                <select name="category_id" class="form-select border-0 shadow-sm fw-bold text-muted">
                    <option value="">Tüm Kategoriler</option>
                    <?php foreach($categories as $cat): ?>
                        <option value="<?= $cat['id'] ?>" <?= $category_id == $cat['id'] ? 'selected' : '' ?>><?= htmlspecialchars($cat['name']) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-auto text-end">
                <button type="submit" class="btn btn-primary fw-bold shadow-sm w-100"><i class="bi bi-filter me-1"></i> Filtrele</button>
            </div>
        </form>
    </div>
</div>

<div class="row text-dark">
    
    <div class="col-xl-4 mb-4">
        <div class="card shadow-sm border-0 h-100">
            <div class="card-header bg-white border-bottom-0 pt-4 pb-0">
                <h6 class="fw-bold text-uppercase mb-0"><i class="bi bi-cart-x-fill text-primary me-2"></i> Sepette Bekleyenler</h6>
            </div>
            <div class="card-body p-0 mt-3">
                <div class="table-responsive">
                    <table class="table table-hover align-middle mb-0">
                        <thead class="table-light small">
                            <tr>
                                <th class="ps-3">Ürün Adı</th>
                                <th class="text-center">Fiyat</th>
                                <th class="text-end pe-3">Adet</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if(empty($inCartProducts)): ?>
                                <tr><td colspan="3" class="text-center text-muted small fst-italic py-4">Belirtilen kriterlerde sepet verisi yok.</td></tr>
                            <?php endif; ?>
                            <?php foreach($inCartProducts as $item): 
                                $gecerli_fiyat = $item['discount_price'] > 0 ? $item['discount_price'] : $item['price'];
                            ?>
                            <tr>
                                <td class="ps-3" style="max-width: 200px;">
                                    <a href="../../urun-detay.php?slug=<?= htmlspecialchars($item['slug']) ?>" target="_blank" class="small fw-bold text-dark text-decoration-none d-block text-truncate" title="<?= htmlspecialchars($item['product_name']) ?>">
                                        <?= htmlspecialchars($item['product_name']) ?>
                                    </a>
                                </td>
                                <td class="text-center small text-muted"><?= number_format($gecerli_fiyat, 2, ',', '.') ?>₺</td>
                                <td class="text-end pe-3">
                                    <span class="badge bg-primary rounded-pill"><?= $item['total_in_cart'] ?></span>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <div class="col-xl-4 mb-4">
        <div class="card shadow-sm border-0 h-100">
            <div class="card-header bg-white border-bottom-0 pt-4 pb-0">
                <h6 class="fw-bold text-uppercase mb-0"><i class="bi bi-heart-fill text-danger me-2"></i> En Çok Favorilenenler</h6>
            </div>
            <div class="card-body p-0 mt-3">
                <div class="table-responsive">
                    <table class="table table-hover align-middle mb-0">
                        <thead class="table-light small">
                            <tr>
                                <th class="ps-3">Ürün Adı</th>
                                <th class="text-center">Fiyat</th>
                                <th class="text-end pe-3">Kalp</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if(empty($favProducts)): ?>
                                <tr><td colspan="3" class="text-center text-muted small fst-italic py-4">Belirtilen kriterlerde favori verisi yok.</td></tr>
                            <?php endif; ?>
                            <?php foreach($favProducts as $item): 
                                $gecerli_fiyat = $item['discount_price'] > 0 ? $item['discount_price'] : $item['price'];
                            ?>
                            <tr>
                                <td class="ps-3" style="max-width: 200px;">
                                    <a href="../../urun-detay.php?slug=<?= htmlspecialchars($item['slug']) ?>" target="_blank" class="small fw-bold text-dark text-decoration-none d-block text-truncate" title="<?= htmlspecialchars($item['product_name']) ?>">
                                        <?= htmlspecialchars($item['product_name']) ?>
                                    </a>
                                </td>
                                <td class="text-center small text-muted"><?= number_format($gecerli_fiyat, 2, ',', '.') ?>₺</td>
                                <td class="text-end pe-3">
                                    <span class="badge bg-danger rounded-pill"><i class="bi bi-heart-fill"></i> <?= $item['fav_count'] ?></span>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <div class="col-xl-4 mb-4">
        <div class="card shadow-sm border-0 h-100">
            <div class="card-header bg-white border-bottom-0 pt-4 pb-0">
                <h6 class="fw-bold text-uppercase mb-0"><i class="bi bi-bell-fill text-warning me-2"></i> İndirim Bekleyenler</h6>
            </div>
            <div class="card-body p-0 mt-3">
                <div class="table-responsive">
                    <table class="table table-hover align-middle mb-0">
                        <thead class="table-light small">
                            <tr>
                                <th class="ps-3">Ürün Adı</th>
                                <th class="text-center">Fiyat</th>
                                <th class="text-end pe-3">Kişi</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if(empty($alertProducts)): ?>
                                <tr><td colspan="3" class="text-center text-muted small fst-italic py-4">Belirtilen kriterlerde alarm verisi yok.</td></tr>
                            <?php endif; ?>
                            <?php foreach($alertProducts as $item): 
                                $gecerli_fiyat = $item['discount_price'] > 0 ? $item['discount_price'] : $item['price'];
                            ?>
                            <tr>
                                <td class="ps-3" style="max-width: 200px;">
                                    <a href="../../urun-detay.php?slug=<?= htmlspecialchars($item['slug']) ?>" target="_blank" class="small fw-bold text-dark text-decoration-none d-block text-truncate" title="<?= htmlspecialchars($item['product_name']) ?>">
                                        <?= htmlspecialchars($item['product_name']) ?>
                                    </a>
                                </td>
                                <td class="text-center small text-muted"><?= number_format($gecerli_fiyat, 2, ',', '.') ?>₺</td>
                                <td class="text-end pe-3">
                                    <span class="badge bg-warning text-dark rounded-pill"><i class="bi bi-bell-fill"></i> <?= $item['alert_count'] ?></span>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

</div>

<?php require_once '../footer.php'; ?>