<?php
require 'db.php';
require 'auth.php';
require_login();

// Get date range from request
$from = $_GET['from'] ?? '';
$to = $_GET['to'] ?? '';

// Default to last 30 days if no range specified
if (empty($from) || empty($to)) {
    $to = date('Y-m-d');
    $from = date('Y-m-d', strtotime('-30 days'));
}

// Build date filter
$dateFilter = '';
$dateParams = [];
if ($from && $to) {
    $dateFilter = "WHERE DATE(tanggal_ambil) BETWEEN ? AND ?";
    $dateParams = [$from, $to];
}

function executeQuery($sql, $params = []) {
    global $db;
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    return $stmt;
}

// 1. Total berkas yang telah dipinjam
$totalPinjamStmt = executeQuery("SELECT COUNT(*) as total FROM peminjaman $dateFilter", $dateParams);
$totalPinjam = $totalPinjamStmt->fetchColumn();

// 2. Jumlah berkas yang telah kembali
$totalKembaliStmt = executeQuery("SELECT COUNT(*) as total FROM peminjaman WHERE status = 'kembali' " . 
    ($dateFilter ? "AND DATE(tanggal_ambil) BETWEEN ? AND ?" : ""), $dateParams);
$totalKembali = $totalKembaliStmt->fetchColumn();

// 3. Jumlah berkas yang sementara dipinjam
$totalDipinjamStmt = executeQuery("SELECT COUNT(*) as total FROM peminjaman WHERE status = 'dipinjam' " . 
    ($dateFilter ? "AND DATE(tanggal_ambil) BETWEEN ? AND ?" : ""), $dateParams);
$totalDipinjam = $totalDipinjamStmt->fetchColumn();

// 4. Berkas terlama dipinjam (yang belum kembali)
$terlama = executeQuery("
    SELECT *, JULIANDAY('now') - JULIANDAY(tanggal_ambil) as hari_dipinjam 
    FROM peminjaman 
    WHERE status = 'dipinjam' " . 
    ($dateFilter ? "AND DATE(tanggal_ambil) BETWEEN ? AND ?" : "") . "
    ORDER BY tanggal_ambil ASC 
    LIMIT 5", $dateParams)->fetchAll(PDO::FETCH_ASSOC);

// 5. Berkas tercepat dipinjam (sudah kembali dalam waktu singkat)
$tercepat = executeQuery("
    SELECT *, JULIANDAY(tanggal_kembali_real) - JULIANDAY(tanggal_ambil) as hari_peminjaman 
    FROM peminjaman 
    WHERE status = 'kembali' AND tanggal_kembali_real IS NOT NULL " . 
    ($dateFilter ? "AND DATE(tanggal_ambil) BETWEEN ? AND ?" : "") . "
    ORDER BY hari_peminjaman ASC 
    LIMIT 5", $dateParams)->fetchAll(PDO::FETCH_ASSOC);

// 6. Peminjam yang paling sering terlambat
$terlambat = executeQuery("
    SELECT nama, jabatan, 
           COUNT(*) as total_pinjam,
           SUM(CASE WHEN DATE(tanggal_kembali_real) > DATE(tanggal_kembali) THEN 1 ELSE 0 END) as telat_count,
           ROUND(SUM(CASE WHEN DATE(tanggal_kembali_real) > DATE(tanggal_kembali) THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as telat_persen
    FROM peminjaman 
    WHERE status = 'kembali' AND tanggal_kembali_real IS NOT NULL " . 
    ($dateFilter ? "AND DATE(tanggal_ambil) BETWEEN ? AND ?" : "") . "
    GROUP BY nama, jabatan
    HAVING telat_count > 0
    ORDER BY telat_persen DESC, telat_count DESC
    LIMIT 10", $dateParams)->fetchAll(PDO::FETCH_ASSOC);

// 7. Peminjam yang paling tepat waktu
$tepatWaktu = executeQuery("
    SELECT nama, jabatan, 
           COUNT(*) as total_pinjam,
           SUM(CASE WHEN DATE(tanggal_kembali_real) <= DATE(tanggal_kembali) THEN 1 ELSE 0 END) as tepat_count,
           ROUND(SUM(CASE WHEN DATE(tanggal_kembali_real) <= DATE(tanggal_kembali) THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as tepat_persen
    FROM peminjaman 
    WHERE status = 'kembali' AND tanggal_kembali_real IS NOT NULL " . 
    ($dateFilter ? "AND DATE(tanggal_ambil) BETWEEN ? AND ?" : "") . "
    GROUP BY nama, jabatan
    HAVING total_pinjam >= 2
    ORDER BY tepat_persen DESC, tepat_count DESC
    LIMIT 10", $dateParams)->fetchAll(PDO::FETCH_ASSOC);

// 8. Peminjam yang paling sering melakukan peminjaman
$seringPinjam = executeQuery("
    SELECT nama, jabatan, 
           COUNT(*) as total_pinjam,
           MIN(tanggal_ambil) as pertama_pinjam,
           MAX(tanggal_ambil) as terakhir_pinjam
    FROM peminjaman " . $dateFilter . "
    GROUP BY nama, jabatan
    ORDER BY total_pinjam DESC
    LIMIT 10", $dateParams)->fetchAll(PDO::FETCH_ASSOC);

// 9. Statistik perpanjangan
$perpanjanganStats = executeQuery("
    SELECT 
        COUNT(*) as total_berkas,
        SUM(perpanjangan_count) as total_perpanjangan,
        ROUND(AVG(perpanjangan_count), 2) as rata_perpanjangan,
        MAX(perpanjangan_count) as max_perpanjangan
    FROM peminjaman " . $dateFilter, $dateParams)->fetch(PDO::FETCH_ASSOC);

function esc($s) { return htmlspecialchars($s ?? '', ENT_QUOTES, 'UTF-8'); }
?>
<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Laporan Peminjaman Berkas</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.2/css/all.min.css">
    <style>
        body { background: #f5f7fb; }
        .stat-card { transition: transform 0.2s; }
        .stat-card:hover { transform: translateY(-2px); }
        .stat-number { font-size: 2rem; font-weight: bold; }
        .print-btn { position: fixed; bottom: 20px; right: 20px; z-index: 1000; }
        @media print {
            .no-print { display: none !important; }
            .print-btn { display: none !important; }
            body { background: white !important; }
        }
        .progress-bar-custom { background: linear-gradient(45deg, #007bff, #0056b3); }
    </style>
</head>
<body>
    <div class="container-fluid py-4">
        <!-- Header -->
        <div class="row mb-4 no-print">
            <div class="col">
                <div class="d-flex justify-content-between align-items-center">
                    <div class="d-flex align-items-center">
                        <img src="gambar/Logo_pnwtp.png" alt="Logo PN Watampone" width="80" height="80" class="me-3">
                        <div>
                            <h1 class="h3 mb-0"><i class="fa fa-chart-bar text-primary"></i> Laporan Peminjaman Berkas</h1>
                            <p class="text-muted mb-0">Pengadilan Negeri Watampone - Analisis dan statistik peminjaman berkas</p>
                        </div>
                    </div>
                    <div>
                        <a href="index.php" class="btn btn-outline-secondary"><i class="fa fa-arrow-left"></i> Kembali</a>
                        <button onclick="window.print()" class="btn btn-primary"><i class="fa fa-print"></i> Cetak</button>
                    </div>
                </div>
            </div>
        </div>

        <!-- Filter Rentang Waktu -->
        <div class="row mb-4 no-print">
            <div class="col">
                <div class="card">
                    <div class="card-body">
                        <h5 class="card-title"><i class="fa fa-calendar"></i> Filter Rentang Waktu</h5>
                        <form method="GET" class="row g-3">
                            <div class="col-md-3">
                                <label class="form-label">Dari Tanggal</label>
                                <input type="date" class="form-control" name="from" value="<?= esc($from) ?>">
                            </div>
                            <div class="col-md-3">
                                <label class="form-label">Sampai Tanggal</label>
                                <input type="date" class="form-control" name="to" value="<?= esc($to) ?>">
                            </div>
                            <div class="col-md-3 d-flex align-items-end">
                                <button type="submit" class="btn btn-primary me-2"><i class="fa fa-search"></i> Filter</button>
                                <a href="laporan.php" class="btn btn-outline-secondary">Reset</a>
                            </div>
                            <div class="col-md-3 d-flex align-items-end">
                                <small class="text-muted">Periode: <?= esc($from) ?> s/d <?= esc($to) ?></small>
                            </div>
                        </form>
                    </div>
                </div>
            </div>
        </div>

        <!-- Statistik Utama -->
        <div class="row mb-4">
            <div class="col-md-3 mb-3">
                <div class="card stat-card h-100 border-primary">
                    <div class="card-body text-center">
                        <i class="fa fa-book fa-3x text-primary mb-3"></i>
                        <div class="stat-number text-primary"><?= number_format($totalPinjam) ?></div>
                        <h6 class="card-title">Total Peminjaman</h6>
                    </div>
                </div>
            </div>
            <div class="col-md-3 mb-3">
                <div class="card stat-card h-100 border-success">
                    <div class="card-body text-center">
                        <i class="fa fa-check-circle fa-3x text-success mb-3"></i>
                        <div class="stat-number text-success"><?= number_format($totalKembali) ?></div>
                        <h6 class="card-title">Berkas Kembali</h6>
                    </div>
                </div>
            </div>
            <div class="col-md-3 mb-3">
                <div class="card stat-card h-100 border-warning">
                    <div class="card-body text-center">
                        <i class="fa fa-clock fa-3x text-warning mb-3"></i>
                        <div class="stat-number text-warning"><?= number_format($totalDipinjam) ?></div>
                        <h6 class="card-title">Sedang Dipinjam</h6>
                    </div>
                </div>
            </div>
            <div class="col-md-3 mb-3">
                <div class="card stat-card h-100 border-info">
                    <div class="card-body text-center">
                        <i class="fa fa-percentage fa-3x text-info mb-3"></i>
                        <div class="stat-number text-info"><?= $totalPinjam > 0 ? round(($totalKembali / $totalPinjam) * 100, 1) : 0 ?>%</div>
                        <h6 class="card-title">Tingkat Pengembalian</h6>
                    </div>
                </div>
            </div>
        </div>

        <!-- Statistik Perpanjangan -->
        <div class="row mb-4">
            <div class="col">
                <div class="card">
                    <div class="card-body">
                        <h5 class="card-title"><i class="fa fa-calendar-plus text-info"></i> Statistik Perpanjangan</h5>
                        <div class="row">
                            <div class="col-md-3 text-center">
                                <div class="h4 text-primary"><?= number_format($perpanjanganStats['total_perpanjangan']) ?></div>
                                <small class="text-muted">Total Perpanjangan</small>
                            </div>
                            <div class="col-md-3 text-center">
                                <div class="h4 text-success"><?= $perpanjanganStats['rata_perpanjangan'] ?></div>
                                <small class="text-muted">Rata-rata per Berkas</small>
                            </div>
                            <div class="col-md-3 text-center">
                                <div class="h4 text-warning"><?= $perpanjanganStats['max_perpanjangan'] ?></div>
                                <small class="text-muted">Perpanjangan Terbanyak</small>
                            </div>
                            <div class="col-md-3 text-center">
                                <div class="h4 text-info"><?= $totalPinjam > 0 ? round(($perpanjanganStats['total_perpanjangan'] / $totalPinjam) * 100, 1) : 0 ?>%</div>
                                <small class="text-muted">Persentase Diperpanjang</small>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <div class="row">
            <!-- Berkas Terlama Dipinjam -->
            <div class="col-md-6 mb-4">
                <div class="card h-100">
                    <div class="card-body">
                        <h5 class="card-title"><i class="fa fa-hourglass-half text-danger"></i> Berkas Terlama Dipinjam</h5>
                        <?php if (empty($terlama)): ?>
                            <p class="text-muted"><em>Tidak ada berkas yang sedang dipinjam</em></p>
                        <?php else: ?>
                            <?php foreach ($terlama as $item): ?>
                                <div class="border-bottom py-2">
                                    <div class="d-flex justify-content-between">
                                        <div>
                                            <strong><?= esc($item['nomor_perkara']) ?></strong><br>
                                            <small class="text-muted"><?= esc($item['nama']) ?> - <?= esc($item['jabatan']) ?></small>
                                        </div>
                                        <div class="text-end">
                                            <span class="badge bg-danger"><?= (int)$item['hari_dipinjam'] ?> hari</span><br>
                                            <small class="text-muted"><?= esc($item['tanggal_ambil']) ?></small>
                                        </div>
                                    </div>
                                </div>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </div>
                </div>
            </div>

            <!-- Berkas Tercepat Dipinjam -->
            <div class="col-md-6 mb-4">
                <div class="card h-100">
                    <div class="card-body">
                        <h5 class="card-title"><i class="fa fa-bolt text-success"></i> Berkas Tercepat Dikembalikan</h5>
                        <?php if (empty($tercepat)): ?>
                            <p class="text-muted"><em>Belum ada data berkas yang dikembalikan</em></p>
                        <?php else: ?>
                            <?php foreach ($tercepat as $item): ?>
                                <div class="border-bottom py-2">
                                    <div class="d-flex justify-content-between">
                                        <div>
                                            <strong><?= esc($item['nomor_perkara']) ?></strong><br>
                                            <small class="text-muted"><?= esc($item['nama']) ?> - <?= esc($item['jabatan']) ?></small>
                                        </div>
                                        <div class="text-end">
                                            <span class="badge bg-success"><?= (int)$item['hari_peminjaman'] ?> hari</span><br>
                                            <small class="text-muted"><?= esc($item['tanggal_ambil']) ?></small>
                                        </div>
                                    </div>
                                </div>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>

        <div class="row">
            <!-- Peminjam Paling Sering Terlambat -->
            <div class="col-md-6 mb-4">
                <div class="card h-100">
                    <div class="card-body">
                        <h5 class="card-title"><i class="fa fa-exclamation-triangle text-warning"></i> Peminjam Sering Terlambat</h5>
                        <?php if (empty($terlambat)): ?>
                            <p class="text-muted"><em>Tidak ada data keterlambatan</em></p>
                        <?php else: ?>
                            <?php foreach ($terlambat as $item): ?>
                                <div class="border-bottom py-2">
                                    <div class="d-flex justify-content-between align-items-center">
                                        <div>
                                            <strong><?= esc($item['nama']) ?></strong><br>
                                            <small class="text-muted"><?= esc($item['jabatan']) ?></small>
                                        </div>
                                        <div class="text-end">
                                            <span class="badge bg-warning"><?= $item['telat_persen'] ?>%</span><br>
                                            <small class="text-muted"><?= $item['telat_count'] ?>/<?= $item['total_pinjam'] ?> telat</small>
                                        </div>
                                    </div>
                                    <div class="progress mt-1" style="height: 4px;">
                                        <div class="progress-bar bg-warning" style="width: <?= $item['telat_persen'] ?>%"></div>
                                    </div>
                                </div>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </div>
                </div>
            </div>

            <!-- Peminjam Paling Tepat Waktu -->
            <div class="col-md-6 mb-4">
                <div class="card h-100">
                    <div class="card-body">
                        <h5 class="card-title"><i class="fa fa-star text-success"></i> Peminjam Tepat Waktu</h5>
                        <?php if (empty($tepatWaktu)): ?>
                            <p class="text-muted"><em>Belum ada data ketepatan waktu</em></p>
                        <?php else: ?>
                            <?php foreach ($tepatWaktu as $item): ?>
                                <div class="border-bottom py-2">
                                    <div class="d-flex justify-content-between align-items-center">
                                        <div>
                                            <strong><?= esc($item['nama']) ?></strong><br>
                                            <small class="text-muted"><?= esc($item['jabatan']) ?></small>
                                        </div>
                                        <div class="text-end">
                                            <span class="badge bg-success"><?= $item['tepat_persen'] ?>%</span><br>
                                            <small class="text-muted"><?= $item['tepat_count'] ?>/<?= $item['total_pinjam'] ?> tepat</small>
                                        </div>
                                    </div>
                                    <div class="progress mt-1" style="height: 4px;">
                                        <div class="progress-bar bg-success" style="width: <?= $item['tepat_persen'] ?>%"></div>
                                    </div>
                                </div>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>

        <!-- Peminjam Paling Sering -->
        <div class="row">
            <div class="col">
                <div class="card">
                    <div class="card-body">
                        <h5 class="card-title"><i class="fa fa-users text-primary"></i> Peminjam Paling Aktif</h5>
                        <?php if (empty($seringPinjam)): ?>
                            <p class="text-muted"><em>Belum ada data peminjaman</em></p>
                        <?php else: ?>
                            <div class="table-responsive">
                                <table class="table table-hover">
                                    <thead class="table-light">
                                        <tr>
                                            <th>Ranking</th>
                                            <th>Nama</th>
                                            <th>Jabatan</th>
                                            <th>Total Peminjaman</th>
                                            <th>Pertama Pinjam</th>
                                            <th>Terakhir Pinjam</th>
                                            <th>Aktivitas</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php foreach ($seringPinjam as $index => $item): ?>
                                            <tr>
                                                <td>
                                                    <?php if ($index === 0): ?>
                                                        <i class="fa fa-trophy text-warning"></i> #1
                                                    <?php elseif ($index === 1): ?>
                                                        <i class="fa fa-medal text-secondary"></i> #2
                                                    <?php elseif ($index === 2): ?>
                                                        <i class="fa fa-award text-warning"></i> #3
                                                    <?php else: ?>
                                                        #<?= $index + 1 ?>
                                                    <?php endif; ?>
                                                </td>
                                                <td><strong><?= esc($item['nama']) ?></strong></td>
                                                <td><?= esc($item['jabatan']) ?></td>
                                                <td>
                                                    <span class="badge bg-primary fs-6"><?= $item['total_pinjam'] ?></span>
                                                </td>
                                                <td><?= esc($item['pertama_pinjam']) ?></td>
                                                <td><?= esc($item['terakhir_pinjam']) ?></td>
                                                <td>
                                                    <div class="progress" style="height: 8px;">
                                                        <?php 
                                                        $maxPinjam = $seringPinjam[0]['total_pinjam'];
                                                        $percentage = ($item['total_pinjam'] / $maxPinjam) * 100;
                                                        ?>
                                                        <div class="progress-bar progress-bar-custom" style="width: <?= $percentage ?>%"></div>
                                                    </div>
                                                </td>
                                            </tr>
                                        <?php endforeach; ?>
                                    </tbody>
                                </table>
                            </div>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>

        <!-- Footer Info -->
        <div class="row mt-4">
            <div class="col">
                <div class="card bg-light">
                    <div class="card-body text-center">
                        <small class="text-muted">
                            <i class="fa fa-info-circle"></i> 
                            Laporan dibuat pada <?= date('d F Y H:i:s') ?> | 
                            Periode: <?= esc($from) ?> s/d <?= esc($to) ?> |
                            Total <?= number_format($totalPinjam) ?> transaksi peminjaman
                        </small>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Footer -->
    <footer class="bg-primary text-white text-center py-3 mt-5">
      <div class="container">
        <p class="mb-0">©2025 by Pn Watampone, Dibuat oleh Fahrurrasyid, A.Md.T CPNS Mahkamah Agung RI Angkatan 2024</p>
      </div>
    </footer>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
