<?php
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}
require_once __DIR__ . '/config/config.php';

/* =========================
   PROTEKSI LOGIN
========================= */
if (!isset($_SESSION['login']) || $_SESSION['login'] !== true) {
    die("Akses ditolak!");
}

$role        = $_SESSION['role'] ?? 'sekolah';
$npsn_login  = $_SESSION['npsn'] ?? '';

$tahun       = $_GET['tahun'] ?? date('Y');
$bulan       = $_GET['bulan'] ?? '';
$npsn_filter = $_GET['npsn'] ?? '';

/* =========================
   QUERY DATA REKAP KIB
   (BMM & BMAL)
========================= */
$sql = "
    SELECT 
        k.npsn,
        s.nama_sekolah,
        k.kode_rekening,
        rm.kelompok,
        k.uraian,
        k.jumb,
        k.satuanb,
        SUM(k.pengeluaran) AS pengeluaran
    FROM kwitansi k
    INNER JOIN rekening_map rm
        ON TRIM(rm.kode_rekening) = TRIM(k.kode_rekening)
    LEFT JOIN sekolah s
        ON s.npsn = k.npsn
    WHERE rm.kelompok IN ('BMM','BMAL')
      AND YEAR(k.tanggal) = '$tahun'
";

/* FILTER BULAN */
if ($bulan != '') {
    $sql .= " AND MONTH(k.tanggal) = '$bulan'";
}

/* FILTER ROLE */
if ($role !== 'admin') {
    $sql .= " AND k.npsn = '$npsn_login'";
} elseif ($npsn_filter != '') {
    $sql .= " AND k.npsn = '$npsn_filter'";
}

$sql .= "
    GROUP BY
        k.npsn,
        s.nama_sekolah,
        k.kode_rekening,
        rm.kelompok,
        k.uraian,
        k.jumb,
        k.satuanb
    ORDER BY
        s.nama_sekolah,
        k.kode_rekening
";

$data = [];
$q = mysqli_query($conn, $sql);
if (!$q) {
    die("Query Error: " . mysqli_error($conn));
}
while ($row = mysqli_fetch_assoc($q)) {
    $data[] = $row;
}

/* =========================
   DATA SEKOLAH (ADMIN)
========================= */
$sekolah = [];
if ($role === 'admin') {
    $qs = mysqli_query($conn, "SELECT npsn, nama_sekolah FROM sekolah ORDER BY nama_sekolah");
    while ($s = mysqli_fetch_assoc($qs)) {
        $sekolah[] = $s;
    }
}

/* =========================
   EXPORT EXCEL (ADMIN)
========================= */
if ($role === 'admin' && isset($_GET['export'])) {

    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=rekap_kib_{$tahun}_{$bulan}.xls");

    echo "No\tNPSN\tNama Sekolah\tKode Rekening\tUraian\tJumlah\tHarga Satuan\tSatuan\tPengeluaran\n";

    $no = 1;
    $grand = 0;

    foreach ($data as $d) {
        $harga = ($d['jumb'] > 0) ? $d['pengeluaran'] / $d['jumb'] : 0;
        $grand += $d['pengeluaran'];

        echo $no++ . "\t{$d['npsn']}\t{$d['nama_sekolah']}\t{$d['kode_rekening']}\t{$d['uraian']}\t{$d['jumb']}\t"
            . round($harga) . "\t{$d['satuanb']}\t" . round($d['pengeluaran']) . "\n";
    }

    echo "\t\t\t\t\t\tTOTAL\t\t$grand\n";
    exit;
}
?>
<!DOCTYPE html>
<html lang="id">
<head>
<meta charset="utf-8">
<title>Rekap KIB</title>
<link rel="icon" href="assets/images/logomu.ico">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
.table{font-size:12px;white-space:nowrap;}
</style>
</head>

<body class="bg-light">
<div class="container-fluid mt-4">

<a href="<?= $role=='admin'?'dashboard_admin.php':'dashboard.php' ?>"
   class="btn btn-outline-secondary btn-sm mb-2">← Dashboard</a>

<div class="card shadow-sm">
<div class="card-header bg-primary text-white d-flex justify-content-between">
<strong>📦 Rekap KIB (BMM & BMAL)</strong>

<?php if ($role==='admin'): ?>
<a href="?export=1&tahun=<?= $tahun ?>&bulan=<?= $bulan ?>&npsn=<?= $npsn_filter ?>"
   class="btn btn-light btn-sm fw-bold">⬇ Export Excel</a>
<?php endif; ?>
</div>

<div class="card-body">

<!-- FILTER -->
<form class="row g-2 mb-3">

<?php if ($role==='admin'): ?>
<div class="col-md-4">
<select name="npsn" class="form-select form-select-sm">
<option value="">-- Semua Sekolah --</option>
<?php foreach ($sekolah as $s): ?>
<option value="<?= $s['npsn'] ?>" <?= $s['npsn']==$npsn_filter?'selected':'' ?>>
<?= $s['npsn'].' | '.$s['nama_sekolah'] ?>
</option>
<?php endforeach; ?>
</select>
</div>
<?php endif; ?>

<div class="col-md-2">
<select name="bulan" class="form-select form-select-sm">
<option value="">-- Bulan --</option>
<?php for($b=1;$b<=12;$b++): ?>
<option value="<?= $b ?>" <?= $bulan==$b?'selected':'' ?>>
<?= date('F', mktime(0,0,0,$b,1)) ?>
</option>
<?php endfor; ?>
</select>
</div>

<div class="col-md-2">
<input type="number" name="tahun" value="<?= $tahun ?>" class="form-control form-control-sm">
</div>

<div class="col-md-2">
<button class="btn btn-primary btn-sm">🔍 Tampilkan</button>
</div>
</form>

<!-- TABEL -->
<div class="table-responsive">
<table class="table table-bordered table-hover table-sm">
<thead class="table-secondary text-center">
<tr>
<th>No</th>
<th>NPSN</th>
<th>Nama Sekolah</th>
<th>Kode Rekening</th>
<th>Uraian</th>
<th>Jumlah</th>
<th>Harga Satuan</th>
<th>Satuan</th>
<th>Pengeluaran</th>
</tr>
</thead>
<tbody>
<?php
$no=1; $grand=0;
foreach($data as $d):
$harga = ($d['jumb']>0)?$d['pengeluaran']/$d['jumb']:0;
$grand += $d['pengeluaran'];
?>
<tr>
<td class="text-center"><?= $no++ ?></td>
<td><?= $d['npsn'] ?></td>
<td><?= $d['nama_sekolah'] ?></td>
<td><?= $d['kode_rekening'] ?></td>
<td><?= $d['uraian'] ?></td>
<td class="text-center"><?= $d['jumb'] ?></td>
<td class="text-end"><?= number_format($harga,0,',','.') ?></td>
<td class="text-center"><?= $d['satuanb'] ?></td>
<td class="text-end fw-bold"><?= number_format($d['pengeluaran'],0,',','.') ?></td>
</tr>
<?php endforeach; ?>
</tbody>

<tfoot class="table-dark fw-bold">
<tr>
<td colspan="8" class="text-center">TOTAL</td>
<td class="text-end"><?= number_format($grand,0,',','.') ?></td>
</tr>
</tfoot>
</table>
</div>

</div>
</div>
</div>
</body>
</html>
