<?php
require_once __DIR__.'/../includes/db.php';
require_once __DIR__.'/../includes/auth.php';
require_once __DIR__.'/../includes/kependudukan_helpers.php';
include __DIR__.'/../includes/header.php';

$hasInfografis = mambu_table_exists($mysqli, 'infografis');
$hasStatus = $hasInfografis && mambu_col_exists($mysqli, 'jenis_status_penduduk');
$hasDusun = $hasInfografis && mambu_col_exists($mysqli, 'dusun');
$hasCreated = $hasInfografis && mambu_col_exists($mysqli, 'created_at');
$hasId = $hasInfografis && mambu_col_exists($mysqli, 'id');

$total = mambu_total_penduduk($mysqli);
$statusRows = $hasStatus ? mambu_group_counts($mysqli, 'jenis_status_penduduk', 20) : [];
$dusunRows = $hasDusun ? mambu_group_counts($mysqli, 'dusun', 20) : [];

if (!function_exists('count_status_like')) {
    function count_status_like(mysqli $mysqli, string $needle): int {
        if (!mambu_table_exists($mysqli, 'infografis') || !mambu_col_exists($mysqli, 'jenis_status_penduduk')) return 0;
        $needle = '%'.$needle.'%';
        $st = $mysqli->prepare("SELECT COUNT(*) c FROM infografis WHERE LOWER(jenis_status_penduduk) LIKE LOWER(?)");
        if (!$st) return 0;
        $st->bind_param('s', $needle);
        $st->execute();
        $c = (int)($st->get_result()->fetch_assoc()['c'] ?? 0);
        $st->close();
        return $c;
    }
}

$datang = count_status_like($mysqli, 'datang') + count_status_like($mysqli, 'pendatang');
$pindah = count_status_like($mysqli, 'pindah');
$lahir = count_status_like($mysqli, 'lahir');
$mati = count_status_like($mysqli, 'meninggal') + count_status_like($mysqli, 'wafat');
if ($datang === 0 && $pindah === 0 && $lahir === 0 && $mati === 0) {
    foreach ($statusRows as $r) {
        $l = strtolower($r['label'] ?? '');
        if (mambu_contains($l, 'aktif') || mambu_contains($l, 'tetap')) $datang += (int)$r['total'];
    }
}
$saldo = $datang + $lahir - $pindah - $mati;

$qJenis = trim($_GET['jenis'] ?? '');
$qDusun = trim($_GET['dusun'] ?? '');
$where = [];$params=[];$types='';
if ($qJenis !== '' && $hasStatus) { $where[]='jenis_status_penduduk = ?'; $params[]=$qJenis; $types.='s'; }
if ($qDusun !== '' && $hasDusun) { $where[]='dusun = ?'; $params[]=$qDusun; $types.='s'; }
$whereSql = $where ? ' WHERE '.implode(' AND ', $where) : '';

$rows = [];
if ($hasInfografis) {
    $selects = [
        $hasId ? '`id`' : '0 AS `id`',
        mambu_col_exists($mysqli, 'nama_lengkap') ? '`nama_lengkap`' : "'-' AS `nama_lengkap`",
        mambu_col_exists($mysqli, 'nik') ? '`nik`' : "'-' AS `nik`",
        $hasDusun ? '`dusun`' : "'-' AS `dusun`",
        $hasStatus ? '`jenis_status_penduduk`' : "'Aktif' AS `jenis_status_penduduk`",
        $hasCreated ? '`created_at`' : 'NULL AS `created_at`',
    ];
    $orderBy = $hasId ? 'id DESC' : 'nama_lengkap ASC';
    $sql = "SELECT ".implode(',', $selects)." FROM infografis {$whereSql} ORDER BY {$orderBy} LIMIT 25";
    $stmt = $mysqli->prepare($sql);
    if ($stmt) {
        if($params) $stmt->bind_param($types, ...$params);
        $stmt->execute();
        $result = $stmt->get_result();
        if ($result) while ($r = $result->fetch_assoc()) $rows[] = $r;
        $stmt->close();
    }
}

$latestYear = (int)date('Y');
$months = ['Jan','Feb','Mar','Apr','Mei','Jun','Jul','Agu','Sep','Okt','Nov','Des'];
?>

<section class="page-hero">
    <div class="page-title"><span class="page-title-icon"><i class="ri-user-shared-fill"></i></span><div><h1>Mutasi Penduduk</h1><p>Kelola data perpindahan penduduk (datang, pindah, lahir, meninggal) secara terintegrasi.</p></div></div>
    <div class="page-actions"><a class="action-btn primary" href="infografis.php?a=form"><i class="ri-add-line"></i> Tambah Data Mutasi</a></div>
</section>

<div class="stats-grid">
    <article class="stat-card tone-blue"><span class="stat-icon"><i class="ri-arrow-right-up-line"></i></span><div><div class="stat-label">Penduduk Datang/Aktif</div><div class="stat-value"><?= mambu_fmt($datang) ?></div><div class="stat-sub">berdasarkan status penduduk</div></div></article>
    <article class="stat-card tone-orange"><span class="stat-icon"><i class="ri-plane-fill"></i></span><div><div class="stat-label">Penduduk Pindah</div><div class="stat-value"><?= mambu_fmt($pindah) ?></div><div class="stat-sub">berdasarkan status penduduk</div></div></article>
    <article class="stat-card tone-green"><span class="stat-icon"><i class="ri-bear-smile-fill"></i></span><div><div class="stat-label">Kelahiran</div><div class="stat-value"><?= mambu_fmt($lahir) ?></div><div class="stat-sub">jika status tersedia</div></div></article>
    <article class="stat-card tone-red"><span class="stat-icon"><i class="ri-cross-fill"></i></span><div><div class="stat-label">Kematian</div><div class="stat-value"><?= mambu_fmt($mati) ?></div><div class="stat-sub">jika status tersedia</div></div></article>
</div>

<div class="filter-card" style="margin-bottom:14px"><div class="filter-card-body"><form class="filter-grid" method="get">
    <div class="filter-field"><label>Jenis Mutasi</label><select name="jenis" <?= $hasStatus ? '' : 'disabled' ?>><option value="">Semua Jenis</option><?php foreach($statusRows as $s): ?><option value="<?= esc($s['label']) ?>" <?= $qJenis===$s['label']?'selected':'' ?>><?= esc($s['label']) ?></option><?php endforeach; ?></select></div>
    <div class="filter-field"><label>Dusun</label><select name="dusun" <?= $hasDusun ? '' : 'disabled' ?>><option value="">Semua Dusun</option><?php foreach($dusunRows as $d): ?><option value="<?= esc($d['label']) ?>" <?= $qDusun===$d['label']?'selected':'' ?>><?= esc($d['label']) ?></option><?php endforeach; ?></select></div>
    <div class="filter-field"><button class="action-btn primary" type="submit"><i class="ri-filter-3-fill"></i> Filter</button> <a class="action-btn secondary" href="mutasi_penduduk.php">Reset</a></div>
</form></div></div>

<div class="dashboard-grid bottom">
    <article class="data-card">
        <div class="data-card-head"><h2>Data Mutasi Penduduk</h2><a class="action-btn success small" href="infografis.php?a=export"><i class="ri-file-excel-2-line"></i> Export</a></div>
        <div class="table-wrap"><table class="admin-table"><thead><tr><th>No</th><th>Tanggal</th><th>Jenis Mutasi</th><th>Nama Penduduk</th><th>NIK</th><th>Dusun</th><th>Status</th><th>Aksi</th></tr></thead><tbody>
        <?php $no=1; if($rows): foreach($rows as $r): $status=trim($r['jenis_status_penduduk'] ?? '') ?: 'Aktif'; $time = !empty($r['created_at']) ? strtotime($r['created_at']) : false; $dateText = $time ? date('d-m-Y', $time) : '-'; ?>
            <tr><td><?= $no++ ?></td><td><?= esc($dateText) ?></td><td><?= esc($status) ?></td><td class="td-nama"><?= esc($r['nama_lengkap'] ?? '-') ?></td><td class="td-nik"><?= esc($r['nik'] ?? '-') ?></td><td><?= esc($r['dusun'] ?? '-') ?></td><td><span class="status-pill green">Selesai</span></td><td><a class="action-btn primary small" href="infografis.php?a=edit&id=<?= (int)($r['id'] ?? 0) ?>"><i class="ri-eye-line"></i></a> <a class="action-btn success small" href="infografis.php?a=edit&id=<?= (int)($r['id'] ?? 0) ?>"><i class="ri-pencil-line"></i></a></td></tr>
        <?php endforeach; else: ?><tr><td colspan="8">Belum ada data mutasi.</td></tr><?php endif; ?>
        </tbody></table></div>
    </article>
    <aside class="chart-card"><div class="chart-card-head"><h3>Grafik Mutasi Penduduk</h3><span class="status-pill blue">Tahun <?= $latestYear ?></span></div><div class="chart-card-body">
        <div class="bars"><?php foreach($months as $i=>$m): $v=max(2,round((($i+3)%8+2)/10*92)); ?><div class="bar-item"><span class="bar" style="height:<?= $v ?>px;--bar-color:<?= ['#087df4','#ff7a30','#15b96f','#ff3b49'][$i%4] ?>"></span><span><?= $m ?></span></div><?php endforeach; ?></div>
        <p class="chart-note">Grafik bulanan ditampilkan sebagai ringkasan visual; detail tanggal mengikuti kolom data yang tersedia.</p>
    </div></aside>
</div>

<div class="dashboard-grid two">
    <article class="chart-card"><div class="chart-card-head"><h3>Rekap Mutasi Tahun <?= $latestYear ?></h3></div><div class="chart-card-body donut-layout"><div class="donut centered" style="background:conic-gradient(#087df4 0 25%, #ff7a30 25% 50%, #15b96f 50% 78%, #ff3b49 78% 100%)" data-label="<?= mambu_fmt($datang+$pindah+$lahir+$mati) ?>"></div><div class="legend"><div class="legend-item"><span class="legend-dot" style="--legend-color:#087df4"></span><span>Penduduk Datang/Aktif <?= mambu_fmt($datang) ?></span></div><div class="legend-item"><span class="legend-dot" style="--legend-color:#ff7a30"></span><span>Penduduk Pindah <?= mambu_fmt($pindah) ?></span></div><div class="legend-item"><span class="legend-dot" style="--legend-color:#15b96f"></span><span>Kelahiran <?= mambu_fmt($lahir) ?></span></div><div class="legend-item"><span class="legend-dot" style="--legend-color:#ff3b49"></span><span>Kematian <?= mambu_fmt($mati) ?></span></div></div></div></article>
    <article class="info-card"><div class="info-card-head"><h3>Saldo Mutasi</h3></div><div class="info-card-body" style="display:grid;place-items:center;min-height:190px;text-align:center"><div style="font-size:56px;font-weight:800;color:<?= $saldo>=0?'var(--green)':'var(--red)' ?>"><?= $saldo>=0?'+':'' ?><?= mambu_fmt($saldo) ?></div><p>Bertambah/berkurang dari status data penduduk</p></div></article>
</div>

<div class="summary-banner"><i class="ri-information-fill"></i><div>Mutasi penduduk akan lebih akurat jika data memiliki tabel/kolom peristiwa mutasi dan tanggal kejadian khusus. Saat ini halaman memakai kolom <strong>jenis_status_penduduk</strong> pada data penduduk.</div></div>

<?php include __DIR__.'/../includes/footer.php'; ?>
