<?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'] ?? '';

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

/* =========================
   QUERY DATA REKAP BHP
========================= */
$sql = "
SELECT 
    k.npsn,
    s.nama_sekolah,
    k.kode_rekening,
    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 = 'BHP'
  AND MONTH(k.tanggal) = '$bulan'
  AND YEAR(k.tanggal)  = '$tahun'
";

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

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

$data = [];
$q = mysqli_query($conn, $sql);
if (!$q) {
    die("ERROR SQL: " . 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
========================= */
if ($role === 'admin' && isset($_GET['export'])) {

    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=rekap_bhp_{$bulan}_{$tahun}.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>
<title>Rekap BHP</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;
}
.table th{text-align:center;vertical-align:middle}
</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 mb-3">
<div class="card-header bg-primary text-white d-flex justify-content-between align-items-center">
    <strong>📦 Rekap BHP Bulan <?= date('F', mktime(0,0,0,$bulan,1)) ?> <?= $tahun ?></strong>
    <?php if ($role === 'admin'): ?>
    <a href="?<?= http_build_query($_GET + ['export'=>1]) ?>"
       class="btn btn-light btn-sm fw-bold">⬇ Export Excel</a>
    <?php endif; ?>
</div>

<div class="card-body">

<form method="get" class="row g-2 mb-3 align-items-end">

<?php if ($role === 'admin'): ?>
<div class="col-md-4">
<label class="form-label">Sekolah</label>
<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">
<label class="form-label">Bulan</label>
<select name="bulan" class="form-select form-select-sm">
<?php for($i=1;$i<=12;$i++): ?>
<option value="<?= sprintf('%02d',$i) ?>" <?= $bulan==sprintf('%02d',$i)?'selected':'' ?>>
<?= date('F', mktime(0,0,0,$i,1)) ?>
</option>
<?php endfor; ?>
</select>
</div>

<div class="col-md-2">
<label class="form-label">Tahun</label>
<select name="tahun" class="form-select form-select-sm">
<?php for($t=date('Y')-3;$t<=date('Y')+1;$t++): ?>
<option value="<?= $t ?>" <?= $tahun==$t?'selected':'' ?>><?= $t ?></option>
<?php endfor; ?>
</select>
</div>

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

</form>

<div class="table-responsive">
<table class="table table-bordered table-hover table-sm align-middle">
<thead class="table-light">
<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>
