<?php
include 'koneksi.php';

// Menangani input pencarian tahun dan bulan
$tahun = isset($_POST['tahun']) ? $_POST['tahun'] : date('Y');
$bulan = isset($_POST['bulan']) ? $_POST['bulan'] : date('m');

// Query untuk menghitung total anggota
$query_total_anggota = "SELECT COUNT(*) AS total_anggota FROM anggota"; // Asumsikan tabel anggota ada
$result_total_anggota = mysqli_query($conn, $query_total_anggota);
$total_anggota = mysqli_fetch_assoc($result_total_anggota)['total_anggota'] ?? 0;

// Query untuk menghitung total iuran
$query_total_iuran = "SELECT SUM(nominal) AS total_iuran FROM iuran WHERE YEAR(tanggal_iuran) = '$tahun' AND MONTH(tanggal_iuran) = '$bulan'";
$result_total_iuran = mysqli_query($conn, $query_total_iuran);
$total_iuran = mysqli_fetch_assoc($result_total_iuran)['total_iuran'];

// Query untuk menghitung total denda
$query_total_denda = "SELECT SUM(nominal) AS total_denda FROM denda WHERE YEAR(tanggal_bayar) = '$tahun' AND MONTH(tanggal_bayar) = '$bulan'";
$result_total_denda = mysqli_query($conn, $query_total_denda);
$total_denda = mysqli_fetch_assoc($result_total_denda)['total_denda'];

// Query untuk menghitung total bunga
$query_total_bunga = "SELECT SUM(nominal) AS total_bunga FROM bunga WHERE YEAR(tanggal_bayar) = '$tahun' AND MONTH(tanggal_bayar) = '$bulan'";
$result_total_bunga = mysqli_query($conn, $query_total_bunga);
$total_bunga = mysqli_fetch_assoc($result_total_bunga)['total_bunga'];

// Query untuk menghitung total pinjaman yang belum lunas
$query_total_pinjaman = "SELECT SUM(nominal) AS total_pinjaman 
                         FROM pinjaman 
                         WHERE YEAR(tanggal_pinjam) = '$tahun' 
                         AND MONTH(tanggal_pinjam) = '$bulan' 
                         AND status != 'lunas'";

$result_total_pinjaman = mysqli_query($conn, $query_total_pinjaman);
$total_pinjaman = mysqli_fetch_assoc($result_total_pinjaman)['total_pinjaman'];

// Menyusun nama bulan yang dipilih
$bulan_nama = date('F', mktime(0, 0, 0, $bulan, 1));  // Mengubah angka bulan menjadi nama bulan

// Total seluruh uang kas (tanpa filter tahun/bulan)
$query_total_iuran_all = "SELECT SUM(nominal) AS total FROM iuran";
$result_total_iuran_all = mysqli_query($conn, $query_total_iuran_all);
$total_iuran_all = mysqli_fetch_assoc($result_total_iuran_all)['total'] ?? 0;

$query_total_denda_all = "SELECT SUM(nominal) AS total FROM denda";
$result_total_denda_all = mysqli_query($conn, $query_total_denda_all);
$total_denda_all = mysqli_fetch_assoc($result_total_denda_all)['total'] ?? 0;

$query_total_bunga_all = "SELECT SUM(nominal) AS total FROM bunga";
$result_total_bunga_all = mysqli_query($conn, $query_total_bunga_all);
$total_bunga_all = mysqli_fetch_assoc($result_total_bunga_all)['total'] ?? 0;

// Query untuk menghitung total pinjaman yang statusnya "Belum Lunas"
$query_total_pinjamanall = "SELECT SUM(nominal) AS total_pinjaman FROM pinjaman WHERE status = 'Belum Lunas'";
$result_total_pinjamanall = mysqli_query($conn, $query_total_pinjamanall);
$total_pinjamanall = mysqli_fetch_assoc($result_total_pinjamanall)['total_pinjaman'];

// Hitung total uang kas keseluruhan
$total_uang_kas_all = $total_iuran_all + $total_denda_all + $total_bunga_all;

// Query untuk menghitung total iuran tanpa filter bulan/tahun
$query_total_iuran_all = "SELECT SUM(nominal) AS total FROM iuran";
$result_total_iuran_all = mysqli_query($conn, $query_total_iuran_all);
$total_iuran_all = mysqli_fetch_assoc($result_total_iuran_all)['total'] ?? 0;

// Menghitung total sisa uang iuran yang belum dipinjam
$total_sisa_iuran = $total_uang_kas_all - $total_pinjamanall;

$query = "
    SELECT COALESCE(SUM(nominal), 0) AS total_nominal
    FROM iuran
    WHERE MONTH(date_created) = ? AND YEAR(date_created) = ?
";

$stmt = $conn->prepare($query);
$stmt->bind_param("ii", $bulan, $tahun);
$stmt->execute();
$stmt->bind_result($total_iuran_filtered);
$stmt->fetch();
$stmt->close();

// === DATA GRAFIK PER BULAN ===
$bulan_labels = [];
$data_iuran = [];
$data_denda = [];
$data_bunga = [];
$data_pinjaman = [];

for ($i = 1; $i <= 12; $i++) {
    $bulan_labels[] = date('F', mktime(0, 0, 0, $i, 1));

    $q = mysqli_query($conn, "SELECT SUM(nominal) AS total FROM iuran WHERE MONTH(tanggal_iuran) = $i AND YEAR(tanggal_iuran) = $tahun");
    $data_iuran[] = (int)(mysqli_fetch_assoc($q)['total'] ?? 0);

    $q = mysqli_query($conn, "SELECT SUM(nominal) AS total FROM denda WHERE MONTH(tanggal_bayar) = $i AND YEAR(tanggal_bayar) = $tahun");
    $data_denda[] = (int)(mysqli_fetch_assoc($q)['total'] ?? 0);

    $q = mysqli_query($conn, "SELECT SUM(nominal) AS total FROM bunga WHERE MONTH(tanggal_bayar) = $i AND YEAR(tanggal_bayar) = $tahun");
    $data_bunga[] = (int)(mysqli_fetch_assoc($q)['total'] ?? 0);

    $q = mysqli_query($conn, "SELECT SUM(nominal) AS total FROM pinjaman WHERE MONTH(tanggal_pinjam) = $i AND YEAR(tanggal_pinjam) = $tahun AND status != 'lunas'");
    $data_pinjaman[] = (int)(mysqli_fetch_assoc($q)['total'] ?? 0);
}
?>


<main class="main-content position-relative border-radius-lg ">
    <div class="container-fluid py-4">
        <h5 class="page-title">
            <span class="text-white">Dashboard Perbulan</span>
        </h5>
        <div class="row mt-4">
            <!-- Filter Tahun dan Bulan -->
            <form method="POST" class="mb-4">
                <div class="row">
                    <div class="col-md-4">
                        <select name="tahun" class="form-select">
                            <?php for ($i = date('Y') - 5; $i <= date('Y'); $i++): ?>
                                <option value="<?php echo $i; ?>" <?php echo ($tahun == $i) ? 'selected' : ''; ?>><?php echo $i; ?></option>
                            <?php endfor; ?>
                        </select>
                    </div>
                    <div class="col-md-4">
                        <select name="bulan" class="form-select">
                            <?php for ($i = 1; $i <= 12; $i++): ?>
                                <option value="<?php echo str_pad($i, 2, '0', STR_PAD_LEFT); ?>" <?php echo ($bulan == $i) ? 'selected' : ''; ?>><?php echo date('F', mktime(0, 0, 0, $i, 1)); ?></option>
                            <?php endfor; ?>
                        </select>
                    </div>
                    <div class="col-md-4">
                        <button type="submit" name="filter" class="btn btn-primary">Tampilkan</button>
                    </div>
                </div>
            </form>
            <!-- Total Anggota -->
            <div class="col-xl-3 col-sm-6 mb-4">
                <div class="card shadow-sm h-100">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <h6 class="font-weight-normal mb-3">Total Iuran Masuk <?php echo date('F Y', strtotime($tahun . '-' . $bulan . '-01')); ?></i></h6>
                            <h5 class="mb-5"><?php echo number_format($total_iuran, 0, ',', '.'); ?> IDR</h5>
                        </div>
                        <div class="icon icon-shape bg-gradient-primary text-white rounded-circle text-center shadow" style="width: 45px; height: 45px;">
                            <i class="fas fa-users text-lg" aria-hidden="true"></i>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Total Uang Kas -->
            <div class="col-xl-3 col-sm-6 mb-4">
                <div class="card shadow-sm h-100">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <h6 class="font-weight-normal mb-3">Total Denda Masuk <?php echo date('F Y', strtotime($tahun . '-' . $bulan . '-01')); ?></h6>
                            <h5 class="mb-5"><?php echo number_format($total_denda, 0, ',', '.'); ?> IDR</h5>
                        </div>
                        <div class="icon icon-shape bg-gradient-danger text-white rounded-circle text-center shadow" style="width: 45px; height: 45px;">
                            <i class="fas fa-wallet text-lg" aria-hidden="true"></i>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Total Pinjaman -->
            <div class="col-xl-3 col-sm-6 mb-4">
                <div class="card shadow-sm h-100">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <h6 class="font-weight-normal mb-3">Total Bunga Masuk <?php echo date('F Y', strtotime($tahun . '-' . $bulan . '-01')); ?></i></h6>
                            <h5 class="mb-5"><?php echo number_format($total_bunga, 0, ',', '.'); ?> IDR</h5>
                        </div>
                        <div class="icon icon-shape bg-gradient-success text-white rounded-circle text-center shadow" style="width: 45px; height: 45px;">
                            <i class="fas fa-hand-holding-usd text-lg" aria-hidden="true"></i>
                        </div>
                    </div>
                </div>
            </div>
            <!-- Total Pinjaman -->

            <!-- Total Pinjaman -->
            <div class="col-xl-3 col-sm-6 mb-4">
                <div class="card shadow-sm h-100">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <h6 class="font-weight-normal mb-3">Iuran Yang di Bayar Bulan <?php echo date('F Y', strtotime($tahun . '-' . $bulan . '-01')); ?></i> + bulan lain </h4>
                                </center>
                                <h5><?php echo number_format($total_iuran_filtered, 0, ',', '.'); ?> IDR</h5>
                                </center>
                        </div>
                        <div class="icon icon-shape bg-gradient-success text-white rounded-circle text-center shadow" style="width: 45px; height: 45px;">
                            <i class="fas fa-hand-holding-usd text-lg" aria-hidden="true"></i>
                        </div>
                    </div>
                </div>
            </div>
            <div class="col-xl-3 col-sm-6 mb-4">
                <div class="card shadow-sm h-100">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <h6 class="font-weight-normal mb-3">Total Iuran, Bunga, dan Denda untuk bulan <?php echo $bulan_nama . ' ' . $tahun; ?>:</i></h6>
                            <h5><?php echo number_format($total_iuran_filtered + $total_bunga + $total_denda, 0, ',', '.'); ?> IDR</h5>
                        </div>
                        <div class="icon icon-shape bg-gradient-success text-white rounded-circle text-center shadow" style="width: 45px; height: 45px;">
                            <i class="fas fa-hand-holding-usd text-lg" aria-hidden="true"></i>
                        </div>
                    </div>
                </div>
            </div>
            <!-- Sisa di Bendahara -->
            <div class="col-xl-3 col-sm-6 mb-4">
                <div class="card shadow-sm h-100">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <h6 class="font-weight-normal mb-3">Total Pinjaman untuk bulan <?php echo $bulan_nama . ' ' . $tahun; ?>:</h6>
                            </center>
                            <h5><?php echo number_format($total_pinjaman, 0, ',', '.'); ?> IDR</h5>
                            </center>
                        </div>
                        <div class="icon icon-shape bg-gradient-warning text-white rounded-circle text-center shadow" style="width: 45px; height: 45px;">
                            <i class="fas fa-cash-register text-lg" aria-hidden="true"></i>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <!-- GRAFIK -->
        <div class="card mt-4">
            <div class="card-body">
                <h4 class="card-title">Grafik Iuran, Denda, Bunga, dan Pinjaman Tahun <?= $tahun; ?></h4>
                <canvas id="grafikBulanan" height="100"></canvas>
            </div>
        </div>
</main>

<!-- CHART.JS -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chartjs-plugin-datalabels@2.2.0/dist/chartjs-plugin-datalabels.min.js"></script>
<script>
    const ctx = document.getElementById('grafikBulanan').getContext('2d');

    const chart = new Chart(ctx, {
        type: 'bar',
        data: {
            labels: <?= json_encode($bulan_labels); ?>,
            datasets: [{
                    label: 'Iuran',
                    data: <?= json_encode($data_iuran); ?>,
                    backgroundColor: '#4e73df',
                    borderRadius: 8
                },
                {
                    label: 'Denda',
                    data: <?= json_encode($data_denda); ?>,
                    backgroundColor: '#e74a3b',
                    borderRadius: 8
                },
                {
                    label: 'Bunga',
                    data: <?= json_encode($data_bunga); ?>,
                    backgroundColor: '#1cc88a',
                    borderRadius: 8
                },
                {
                    label: 'Pinjaman',
                    data: <?= json_encode($data_pinjaman); ?>,
                    backgroundColor: '#f6c23e',
                    borderRadius: 8
                }
            ]
        },
        options: {
            responsive: true,
            plugins: {
                title: {
                    display: true,
                    text: 'Rekapitulasi Keuangan per Bulan Tahun <?= $tahun; ?>',
                    font: {
                        size: 18,
                        weight: 'bold'
                    }
                },
                legend: {
                    position: 'top',
                    labels: {
                        boxWidth: 20,
                        padding: 15
                    }
                },
                tooltip: {
                    mode: 'index',
                    intersect: false,
                    backgroundColor: 'rgba(0,0,0,0.8)',
                    titleFont: {
                        weight: 'bold'
                    },
                    callbacks: {
                        label: function(context) {
                            return context.dataset.label + ': ' + new Intl.NumberFormat('id-ID').format(context.raw) + ' IDR';
                        }
                    }
                },
                datalabels: {
                    color: '#fff',
                    anchor: 'end',
                    align: 'start',
                    font: {
                        weight: 'bold'
                    },
                    formatter: function(value) {
                        return value > 0 ? value.toLocaleString('id-ID') : '';
                    }
                }
            },
            interaction: {
                mode: 'nearest',
                axis: 'x',
                intersect: false
            },
            scales: {
                y: {
                    beginAtZero: true,
                    ticks: {
                        callback: function(value) {
                            return value.toLocaleString('id-ID');
                        }
                    },
                    title: {
                        display: true,
                        text: 'Jumlah (IDR)',
                        font: {
                            size: 14
                        }
                    }
                },
                x: {
                    title: {
                        display: true,
                        text: 'Bulan',
                        font: {
                            size: 14
                        }
                    }
                }
            }
        },
        plugins: [ChartDataLabels]
    });
</script>


<?php
// Tutup koneksi setelah semua selesai
$conn->close();
?>