<?php
session_start();
include 'config.php';

if (!isset($_SESSION['admin_logged_in'])) {
    header("Location: index.php");
    exit;
}

// Şema kontrolü: gerekli kolonlar mevcut mu?
$requiredColumns = [
    ['table' => 'kullanicilar', 'column' => 'referans_kodu'],
    ['table' => 'kullanicilar', 'column' => 'referred_by'],
    ['table' => 'kullanicilar', 'column' => 'referred_at'],
    ['table' => 'kullanicilar', 'column' => 'invite_count'],
];
$missingColumns = [];
try {
    foreach ($requiredColumns as $rc) {
        $stmtCol = $pdo->prepare("SHOW COLUMNS FROM `{$rc['table']}` LIKE :col");
        $stmtCol->bindValue(':col', $rc['column']);
        $stmtCol->execute();
        if ($stmtCol->fetch(PDO::FETCH_ASSOC) === false) {
            $missingColumns[] = $rc['table'] . '.' . $rc['column'];
        }
    }
} catch (Throwable $e) {
    // Şema sorgusu başarısız olursa, eksik bilgileri gizlice geçiyoruz
}
$schemaOk = empty($missingColumns);

// Filtreler
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$code = isset($_GET['code']) ? trim($_GET['code']) : '';
$export = isset($_GET['export']) ? trim($_GET['export']) : '';

// Tarih aralığı (varsayılan: son 30 gün)
$defaultStart = date('Y-m-d', strtotime('-30 days'));
$defaultEnd = date('Y-m-d');
$start = isset($_GET['start']) && $_GET['start'] !== '' ? $_GET['start'] : $defaultStart;
$end = isset($_GET['end']) && $_GET['end'] !== '' ? $_GET['end'] : $defaultEnd;

// Sayfalama
$limit = 25;
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$offset = ($page - 1) * $limit;

// Özet liste: Davet eden kullanıcılar ve davet sayıları
$summaryParams = [
    ':start' => $start . ' 00:00:00',
    ':end' => $end . ' 23:59:59',
];

$searchSql = '';
if ($search !== '') {
    $summaryParams[':search'] = '%' . $search . '%';
    $searchSql = ' AND (inv.username LIKE :search OR inv.email LIKE :search OR inv.referans_kodu LIKE :search)';
}

// CSV export (summary or detail)
if ($export === 'summary') {
    if (!$schemaOk) {
        header('Content-Type: text/plain; charset=utf-8');
        echo "Eksik kolonlar: " . implode(', ', $missingColumns) . "\nLütfen veritabanı şemasını güncelleyiniz (referans raporu için gerekli).";
        exit;
    }
    $exportSql = "SELECT inv.username AS inviter_username,
                         inv.email AS inviter_email,
                         inv.referans_kodu AS referans_kodu,
                         COUNT(u.id) AS total_invites_all_time,
                         SUM(CASE WHEN u.referred_at BETWEEN :start AND :end THEN 1 ELSE 0 END) AS invites_in_range,
                         COUNT(DISTINCT CASE WHEN p.durum = '1' AND p.tarih BETWEEN :start AND :end THEN p.user_id END) AS depositors_in_range,
                         SUM(CASE WHEN p.durum = '1' AND p.tarih BETWEEN :start AND :end THEN COALESCE(p.miktar,0) ELSE 0 END) AS deposit_sum_in_range
                  FROM kullanicilar inv
                  LEFT JOIN kullanicilar u ON u.referred_by = inv.referans_kodu
                  LEFT JOIN parayatir p ON p.user_id = u.id
                  WHERE inv.referans_kodu IS NOT NULL AND inv.referans_kodu <> ''" . $searchSql . "
                  GROUP BY inv.id, inv.username, inv.email, inv.referans_kodu
                  ORDER BY invites_in_range DESC, total_invites_all_time DESC";
    $stmtExp = $pdo->prepare($exportSql);
    $stmtExp->bindValue(':start', $summaryParams[':start']);
    $stmtExp->bindValue(':end', $summaryParams[':end']);
    if (isset($summaryParams[':search'])) { $stmtExp->bindValue(':search', $summaryParams[':search']); }
    $stmtExp->execute();
    $rows = $stmtExp->fetchAll(PDO::FETCH_ASSOC);

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="referrals_summary_' . $start . '_to_' . $end . '.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['inviter_username','inviter_email','referans_kodu','total_invites_all_time','invites_in_range','depositors_in_range','deposit_sum_in_range']);
    foreach ($rows as $r) {
        fputcsv($out, [
            $r['inviter_username'],
            $r['inviter_email'],
            $r['referans_kodu'],
            (int)$r['total_invites_all_time'],
            (int)$r['invites_in_range'],
            (int)$r['depositors_in_range'],
            number_format((float)$r['deposit_sum_in_range'], 2),
        ]);
    }
    fclose($out);
    exit;
}

if ($export === 'detail' && $code !== '') {
    if (!$schemaOk) {
        header('Content-Type: text/plain; charset=utf-8');
        echo "Eksik kolonlar: " . implode(', ', $missingColumns) . "\nLütfen veritabanı şemasını güncelleyiniz (referans detayı için gerekli).";
        exit;
    }
    $detailExpSql = "SELECT u.id, u.username, u.email, u.first_name, u.surname, u.referred_at, u.created_at,
                            MIN(CASE WHEN p.durum = '1' AND p.tarih BETWEEN :start AND :end THEN p.tarih END) AS first_deposit_date_in_range,
                            SUM(CASE WHEN p.durum = '1' AND p.tarih BETWEEN :start AND :end THEN COALESCE(p.miktar,0) ELSE 0 END) AS deposit_sum_in_range,
                            COUNT(CASE WHEN p.durum = '1' AND p.tarih BETWEEN :start AND :end THEN 1 END) AS deposit_count_in_range
                     FROM kullanicilar u
                     LEFT JOIN parayatir p ON p.user_id = u.id
                     WHERE u.referred_by = :code
                       AND (u.referred_at BETWEEN :start AND :end OR u.referred_at IS NULL)
                     GROUP BY u.id, u.username, u.email, u.first_name, u.surname, u.referred_at, u.created_at
                     ORDER BY COALESCE(u.referred_at, u.created_at) DESC";
    $stmtExp = $pdo->prepare($detailExpSql);
    $stmtExp->bindValue(':code', $code);
    $stmtExp->bindValue(':start', $start . ' 00:00:00');
    $stmtExp->bindValue(':end', $end . ' 23:59:59');
    $stmtExp->execute();
    $rows = $stmtExp->fetchAll(PDO::FETCH_ASSOC);

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="referrals_detail_' . $code . '_' . $start . '_to_' . $end . '.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['id','username','email','first_name','surname','referred_at','created_at','first_deposit_date_in_range','deposit_sum_in_range','deposit_count_in_range']);
    foreach ($rows as $u) {
        fputcsv($out, [
            (int)$u['id'],
            $u['username'],
            $u['email'],
            $u['first_name'],
            $u['surname'],
            $u['referred_at'],
            $u['created_at'],
            $u['first_deposit_date_in_range'],
            number_format((float)$u['deposit_sum_in_range'], 2),
            (int)$u['deposit_count_in_range'],
        ]);
    }
    fclose($out);
    exit;
}

// Toplam kayıt sayısı için sayım (davet edenler)
if ($schemaOk) {
    try {
        $countSql = "SELECT COUNT(*) AS cnt
                     FROM kullanicilar inv
                     WHERE inv.referans_kodu IS NOT NULL AND inv.referans_kodu <> ''" . $searchSql;
        $countStmt = $pdo->prepare($countSql);
        foreach ($summaryParams as $k => $v) {
            if ($k === ':start' || $k === ':end') continue; // count'ta kullanılmıyor
            $countStmt->bindValue($k, $v);
        }
        $countStmt->execute();
        $totalInviters = (int)$countStmt->fetchColumn();
    } catch (Throwable $e) {
        $totalInviters = 0;
    }
} else {
    $totalInviters = 0;
}

$totalPages = max(1, (int)ceil($totalInviters / $limit));
if ($page > $totalPages) { $page = $totalPages; $offset = ($page - 1) * $limit; }

$summarySql = "SELECT inv.id AS inviter_id,
                      inv.username AS inviter_username,
                      inv.email AS inviter_email,
                      inv.referans_kodu AS referans_kodu,
                      COALESCE(inv.invite_count, 0) AS invite_count_stored,
                      COUNT(u.id) AS total_invites_all_time,
                      SUM(CASE WHEN u.referred_at BETWEEN :start AND :end THEN 1 ELSE 0 END) AS invites_in_range,
                      COUNT(DISTINCT CASE WHEN p.durum = '1' THEN p.user_id END) AS depositors_all_time,
                      COUNT(DISTINCT CASE WHEN p.durum = '1' AND p.tarih BETWEEN :start AND :end THEN p.user_id END) AS depositors_in_range,
                      SUM(CASE WHEN p.durum = '1' THEN COALESCE(p.miktar,0) ELSE 0 END) AS deposit_sum_all_time,
                      SUM(CASE WHEN p.durum = '1' AND p.tarih BETWEEN :start AND :end THEN COALESCE(p.miktar,0) ELSE 0 END) AS deposit_sum_in_range
               FROM kullanicilar inv
               LEFT JOIN kullanicilar u ON u.referred_by = inv.referans_kodu
               LEFT JOIN parayatir p ON p.user_id = u.id
               WHERE inv.referans_kodu IS NOT NULL AND inv.referans_kodu <> ''" . $searchSql . "
               GROUP BY inv.id, inv.username, inv.email, inv.referans_kodu, inv.invite_count
               ORDER BY invites_in_range DESC, total_invites_all_time DESC
               LIMIT :limit OFFSET :offset";

if ($schemaOk) {
    try {
        $summaryStmt = $pdo->prepare($summarySql);
        $summaryStmt->bindValue(':start', $summaryParams[':start']);
        $summaryStmt->bindValue(':end', $summaryParams[':end']);
        if (isset($summaryParams[':search'])) { $summaryStmt->bindValue(':search', $summaryParams[':search']); }
        $summaryStmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $summaryStmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $summaryStmt->execute();
        $inviterRows = $summaryStmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $inviterRows = [];
    }
} else {
    $inviterRows = [];
}

// Detay görünümü: belirli referans kodu ile gelen kullanıcılar
$detailRows = [];
if ($code !== '' && $schemaOk) {
    $detailSql = "SELECT u.id, u.username, u.email, u.first_name, u.surname, u.created_at, u.referred_at,
                         MIN(CASE WHEN p.durum = '1' AND p.tarih BETWEEN :start AND :end THEN p.tarih END) AS first_deposit_date_in_range,
                         SUM(CASE WHEN p.durum = '1' AND p.tarih BETWEEN :start AND :end THEN COALESCE(p.miktar,0) ELSE 0 END) AS deposit_sum_in_range,
                         COUNT(CASE WHEN p.durum = '1' AND p.tarih BETWEEN :start AND :end THEN 1 END) AS deposit_count_in_range
                  FROM kullanicilar u
                  LEFT JOIN parayatir p ON p.user_id = u.id
                  WHERE u.referred_by = :code
                    AND (u.referred_at BETWEEN :start AND :end OR u.referred_at IS NULL) 
                  GROUP BY u.id, u.username, u.email, u.first_name, u.surname, u.created_at, u.referred_at
                  ORDER BY COALESCE(u.referred_at, u.created_at) DESC
                  LIMIT 200";
    try {
        $detailStmt = $pdo->prepare($detailSql);
        $detailStmt->bindValue(':code', $code);
        $detailStmt->bindValue(':start', $start . ' 00:00:00');
        $detailStmt->bindValue(':end', $end . ' 23:59:59');
        $detailStmt->execute();
        $detailRows = $detailStmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $detailRows = [];
    }
}
?>
<!DOCTYPE html>
<html lang="tr">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Referans Raporları</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons/font/bootstrap-icons.css" rel="stylesheet">
    <link rel="stylesheet" href="styles.css">
</head>
<body>
    <div class="container-fluid">
        <div class="row">
            <?php include 'sidebar.php'; ?>
            <div class="col-md-10 p-4">
                <?php include 'topbar.php'; ?>
                <h2 class="mb-4">Referans Raporları</h2>
                <?php if (!$schemaOk): ?>
                    <div class="alert alert-danger" role="alert">
                        Sunucu veritabanı şemasında eksik kolonlar var: <strong><?= htmlspecialchars(implode(', ', $missingColumns)) ?></strong>.<br>
                        Bu sayfanın çalışması için gerekli kolonlar eklenmelidir. Lütfen veritabanı migrasyonunu uygulayın.
                    </div>
                <?php endif; ?>

                <form class="row g-3 mb-3" method="GET" action="referrals.php" id="filterForm">
                    <div class="col-auto">
                        <input type="text" name="search" class="form-control" placeholder="Kullanıcı adı, email veya kod" value="<?= htmlspecialchars($search) ?>">
                    </div>
                    <div class="col-auto">
                        <input type="date" name="start" class="form-control" value="<?= htmlspecialchars($start) ?>">
                    </div>
                    <div class="col-auto">
                        <input type="date" name="end" class="form-control" value="<?= htmlspecialchars($end) ?>">
                    </div>
                    <div class="col-auto">
                        <button type="submit" class="btn btn-primary">Filtrele</button>
                        <a href="referrals.php" class="btn btn-secondary">Sıfırla</a>
                        <a href="referrals.php?export=summary&search=<?= urlencode($search) ?>&start=<?= urlencode($start) ?>&end=<?= urlencode($end) ?>" class="btn btn-outline-secondary">CSV (Özet)</a>
                        <button type="button" class="btn btn-secondary" id="printBtn"><i class="bi bi-printer"></i> Yazdır</button>
                        <!-- Tarih presetleri -->
                        <div class="btn-group ms-2">
                          <button type="button" class="btn btn-outline-dark" data-preset="7">Son 7 Gün</button>
                          <button type="button" class="btn btn-outline-dark" data-preset="30">Son 30 Gün</button>
                          <button type="button" class="btn btn-outline-dark" data-preset="mtd">Bu Ay</button>
                          <button type="button" class="btn btn-outline-dark" data-preset="ytd">Bu Yıl</button>
                        </div>
                    </div>
                </form>

                <?php
                  // Özet toplamlar ve oranlar
                  $sumInvites = 0; $sumDepositors = 0; $sumDeposit = 0.0;
                  foreach(($inviterRows ?? []) as $row){
                    $sumInvites += (int)($row['invites_in_range'] ?? 0);
                    $sumDepositors += (int)($row['depositors_in_range'] ?? 0);
                    $sumDeposit += (float)($row['deposit_sum_in_range'] ?? 0);
                  }
                  $overallConv = $sumInvites > 0 ? ($sumDepositors / $sumInvites) * 100 : 0;
                  $avgPerDepositorOverall = $sumDepositors > 0 ? ($sumDeposit / $sumDepositors) : 0;
                ?>
                <div class="table-responsive mb-4">
                    <table class="table table-striped table-hover table-sticky">
                        <thead>
                            <tr>
                                <th>Ref. Kullanıcı</th>
                                <th>Email</th>
                                <th>Referans Kodu</th>
                                <th>Toplam Davet</th>
                                <th>Seçilen Aralıkta</th>
                                <th>İlk Depozit (Aralık)</th>
                                <th>Toplam Yatırım (Aralık)</th>
                                <th>Dönüşüm %</th>
                                <th>Kişi Başına Depozit</th>
                                <th>İşlem</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($inviterRows as $row): ?>
                                <tr>
                                    <td><?= htmlspecialchars($row['inviter_username']) ?></td>
                                    <td><?= htmlspecialchars($row['inviter_email']) ?></td>
                                    <td><span class="badge bg-dark"><?= htmlspecialchars($row['referans_kodu']) ?></span></td>
                                    <td><?= (int)$row['total_invites_all_time'] ?></td>
                                    <td><?= (int)$row['invites_in_range'] ?></td>
                                    <td><?= (int)$row['depositors_in_range'] ?></td>
                                    <td><?= number_format((float)$row['deposit_sum_in_range'], 2) ?></td>
                                    <?php
                                      $invites = max(1, (int)$row['invites_in_range']);
                                      $depositors = (int)$row['depositors_in_range'];
                                      $sumInRange = (float)$row['deposit_sum_in_range'];
                                      $conv = ($depositors / $invites) * 100;
                                      $avgPerDepositor = $depositors > 0 ? ($sumInRange / $depositors) : 0;
                                    ?>
                                    <td><?= number_format($conv, 1, ',', '.') ?>%</td>
                                    <td><?= number_format($avgPerDepositor, 2, ',', '.') ?></td>
                                    <td>
                                        <a class="btn btn-sm btn-warning" href="?code=<?= urlencode($row['referans_kodu']) ?>&start=<?= urlencode($start) ?>&end=<?= urlencode($end) ?>">Detayları Gör</a>
                                        <a class="btn btn-sm btn-outline-secondary" href="referrals.php?export=detail&code=<?= urlencode($row['referans_kodu']) ?>&start=<?= urlencode($start) ?>&end=<?= urlencode($end) ?>">CSV (Detay)</a>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                            <?php if (empty($inviterRows)): ?>
                                <tr><td colspan="8" class="text-center">Kayıt bulunamadı.</td></tr>
                            <?php endif; ?>
                        </tbody>
                        <tfoot>
                            <tr>
                                <th colspan="3">Genel Toplam</th>
                                <th><?= (int)array_sum(array_column($inviterRows, 'total_invites_all_time')) ?></th>
                                <th><?= (int)$sumInvites ?></th>
                                <th><?= (int)$sumDepositors ?></th>
                                <th><?= number_format($sumDeposit, 2, ',', '.') ?></th>
                                <th><?= number_format($overallConv, 1, ',', '.') ?>%</th>
                                <th><?= number_format($avgPerDepositorOverall, 2, ',', '.') ?></th>
                                <th>-</th>
                            </tr>
                        </tfoot>
                    </table>
                </div>

                <!-- Sayfalama -->
                <nav aria-label="Sayfa Navigasyonu">
                    <ul class="pagination justify-content-center">
                        <?php if ($page > 1): ?>
                            <li class="page-item">
                                <a class="page-link" href="?page=<?= $page - 1 ?>&search=<?= urlencode($search) ?>&start=<?= urlencode($start) ?>&end=<?= urlencode($end) ?>" aria-label="Önceki">
                                    <span aria-hidden="true">&laquo;</span>
                                </a>
                            </li>
                        <?php endif; ?>
                        <?php for ($i = max(1, $page - 2); $i <= min($totalPages, $page + 2); $i++): ?>
                            <li class="page-item <?= ($i === $page) ? 'active' : '' ?>">
                                <a class="page-link" href="?page=<?= $i ?>&search=<?= urlencode($search) ?>&start=<?= urlencode($start) ?>&end=<?= urlencode($end) ?>"><?= $i ?></a>
                            </li>
                        <?php endfor; ?>
                        <?php if ($page < $totalPages): ?>
                            <li class="page-item">
                                <a class="page-link" href="?page=<?= $page + 1 ?>&search=<?= urlencode($search) ?>&start=<?= urlencode($start) ?>&end=<?= urlencode($end) ?>" aria-label="Sonraki">
                                    <span aria-hidden="true">&raquo;</span>
                                </a>
                            </li>
                        <?php endif; ?>
                    </ul>
                </nav>

                <?php if ($code !== ''): ?>
                <div class="card mt-4">
                    <div class="card-body">
                        <h5 class="card-title">Davet Detayı: <span class="badge bg-dark"><?= htmlspecialchars($code) ?></span></h5>
                        <div class="table-responsive">
                            <table class="table table-striped table-hover table-sticky">
                                <thead>
                                    <tr>
                                        <th>ID</th>
                                        <th>Kullanıcı Adı</th>
                                        <th>Ad</th>
                                        <th>Soyad</th>
                                        <th>Email</th>
                                        <th>Davet Tarihi</th>
                                        <th>Üyelik Tarihi</th>
                                        <th>İlk Depozit (Aralık)</th>
                                        <th>Toplam Yatırım (Aralık)</th>
                                        <th>Depozit Sayısı (Aralık)</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($detailRows as $u): ?>
                                        <tr>
                                            <td><?= (int)$u['id'] ?></td>
                                            <td><?= htmlspecialchars($u['username']) ?></td>
                                            <td><?= htmlspecialchars($u['first_name']) ?></td>
                                            <td><?= htmlspecialchars($u['surname']) ?></td>
                                            <td><?= htmlspecialchars($u['email']) ?></td>
                                            <td><?= htmlspecialchars($u['referred_at']) ?></td>
                                            <td><?= htmlspecialchars($u['created_at']) ?></td>
                                            <td><?= htmlspecialchars($u['first_deposit_date_in_range']) ?></td>
                                            <td><?= number_format((float)$u['deposit_sum_in_range'], 2) ?></td>
                                            <td><?= (int)$u['deposit_count_in_range'] ?></td>
                                        </tr>
                                    <?php endforeach; ?>
                                    <?php if (empty($detailRows)): ?>
                                        <tr><td colspan="10" class="text-center">Seçilen aralıkta davet kaydı yok.</td></tr>
                                    <?php endif; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
    <script src="admin.js"></script>
    <script>
    // Preset tarih aralıkları
    (function(){
      const form = document.getElementById('filterForm');
      if(!form) return;
      const startInput = form.querySelector('input[name="start"]');
      const endInput = form.querySelector('input[name="end"]');
      function formatDate(d){
        const y = d.getFullYear();
        const m = String(d.getMonth()+1).padStart(2,'0');
        const day = String(d.getDate()).padStart(2,'0');
        return `${y}-${m}-${day}`;
      }
      form.querySelectorAll('[data-preset]').forEach(btn => {
        btn.addEventListener('click', ()=>{
          const p = btn.getAttribute('data-preset');
          const now = new Date();
          let s, e = new Date(now);
          if(p === '7'){
            s = new Date(now); s.setDate(s.getDate()-7);
          } else if(p === '30'){
            s = new Date(now); s.setDate(s.getDate()-30);
          } else if(p === 'mtd'){
            s = new Date(now.getFullYear(), now.getMonth(), 1);
          } else if(p === 'ytd'){
            s = new Date(now.getFullYear(), 0, 1);
          }
          if(s){ startInput.value = formatDate(s); }
          endInput.value = formatDate(e);
          form.requestSubmit();
        });
      });
    })();
    // Yazdırma
    document.getElementById('printBtn')?.addEventListener('click', ()=> window.print());
    </script>
</body>
</html>