<?php

require_once __DIR__ . '/../config/koneksi.php';

/*
|--------------------------------------------------------------------------
| DATA DEMOGRAFI DESA
|--------------------------------------------------------------------------
| Data dihitung langsung dari tabel penduduk.
| Tidak menggunakan tabel demografi_desa.
|--------------------------------------------------------------------------
*/


// =====================================================
// TOTAL PENDUDUK
// =====================================================

$query = mysqli_query($conn, "
    SELECT COUNT(*) AS total
    FROM penduduk
");

$data = mysqli_fetch_assoc($query);
$total_penduduk = (int) $data['total'];


// =====================================================
// LAKI-LAKI
// =====================================================

$query = mysqli_query($conn, "
    SELECT COUNT(*) AS total
    FROM penduduk
    WHERE jenis_kelamin = 'Laki-laki'
");

$data = mysqli_fetch_assoc($query);
$total_laki = (int) $data['total'];


// =====================================================
// PEREMPUAN
// =====================================================

$query = mysqli_query($conn, "
    SELECT COUNT(*) AS total
    FROM penduduk
    WHERE jenis_kelamin = 'Perempuan'
");

$data = mysqli_fetch_assoc($query);
$total_perempuan = (int) $data['total'];


// =====================================================
// KELOMPOK UMUR
// Berdasarkan tanggal_lahir
// =====================================================

// Anak-anak 0-12 tahun
$query = mysqli_query($conn, "
    SELECT COUNT(*) AS total
    FROM penduduk
    WHERE tanggal_lahir IS NOT NULL
    AND TIMESTAMPDIFF(YEAR, tanggal_lahir, CURDATE()) BETWEEN 0 AND 12
");

$data = mysqli_fetch_assoc($query);
$total_anak = (int) $data['total'];


// Remaja 13-17 tahun
$query = mysqli_query($conn, "
    SELECT COUNT(*) AS total
    FROM penduduk
    WHERE tanggal_lahir IS NOT NULL
    AND TIMESTAMPDIFF(YEAR, tanggal_lahir, CURDATE()) BETWEEN 13 AND 17
");

$data = mysqli_fetch_assoc($query);
$total_remaja = (int) $data['total'];


// Dewasa 18-59 tahun
$query = mysqli_query($conn, "
    SELECT COUNT(*) AS total
    FROM penduduk
    WHERE tanggal_lahir IS NOT NULL
    AND TIMESTAMPDIFF(YEAR, tanggal_lahir, CURDATE()) BETWEEN 18 AND 59
");

$data = mysqli_fetch_assoc($query);
$total_dewasa = (int) $data['total'];


// Lansia 60 tahun ke atas
$query = mysqli_query($conn, "
    SELECT COUNT(*) AS total
    FROM penduduk
    WHERE tanggal_lahir IS NOT NULL
    AND TIMESTAMPDIFF(YEAR, tanggal_lahir, CURDATE()) >= 60
");

$data = mysqli_fetch_assoc($query);
$total_lansia = (int) $data['total'];


// =====================================================
// STATUS PERKAWINAN
// =====================================================

$query = mysqli_query($conn, "
    SELECT status_perkawinan, COUNT(*) AS jumlah
    FROM penduduk
    WHERE status_perkawinan IS NOT NULL
    AND status_perkawinan != ''
    GROUP BY status_perkawinan
    ORDER BY jumlah DESC
");

$data_perkawinan = [];

while ($row = mysqli_fetch_assoc($query)) {
    $data_perkawinan[] = $row;
}


// =====================================================
// PENDIDIKAN
// =====================================================

$query = mysqli_query($conn, "
    SELECT pendidikan, COUNT(*) AS jumlah
    FROM penduduk
    WHERE pendidikan IS NOT NULL
    AND pendidikan != ''
    GROUP BY pendidikan
    ORDER BY jumlah DESC
");

$data_pendidikan = [];

while ($row = mysqli_fetch_assoc($query)) {
    $data_pendidikan[] = $row;
}


// =====================================================
// PEKERJAAN
// =====================================================

$query = mysqli_query($conn, "
    SELECT pekerjaan, COUNT(*) AS jumlah
    FROM penduduk
    WHERE pekerjaan IS NOT NULL
    AND pekerjaan != ''
    GROUP BY pekerjaan
    ORDER BY jumlah DESC
");

$data_pekerjaan = [];

while ($row = mysqli_fetch_assoc($query)) {
    $data_pekerjaan[] = $row;
}


// =====================================================
// AGAMA
// =====================================================

$query = mysqli_query($conn, "
    SELECT agama, COUNT(*) AS jumlah
    FROM penduduk
    WHERE agama IS NOT NULL
    AND agama != ''
    GROUP BY agama
    ORDER BY jumlah DESC
");

$data_agama = [];

while ($row = mysqli_fetch_assoc($query)) {
    $data_agama[] = $row;
}


// =====================================================
// DUSUN
// =====================================================

$query = mysqli_query($conn, "
    SELECT dusun, COUNT(*) AS jumlah
    FROM penduduk
    WHERE dusun IS NOT NULL
    AND dusun != ''
    GROUP BY dusun
    ORDER BY jumlah DESC
");

$data_dusun = [];

while ($row = mysqli_fetch_assoc($query)) {
    $data_dusun[] = $row;
}

?>

<!DOCTYPE html>
<html lang="id">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Data Demografi Desa</title>

    <!-- CSS KHUSUS DATA DEMOGRAFI -->
    <link rel="stylesheet" href="../assets/css/data_demografi.css">
</head>

<body>

<div class="demografi-container">

    <!-- =====================================================
     HEADER DATA DEMOGRAFI
    ====================================================== -->
    <div class="page-header">

        <div class="header-top">

            <a href="dashboard.php" class="btn-back">
                ← Dashboard
            </a>

        </div>

        <div class="header-content">

            <div class="header-icon">
                📊
            </div>

            <div class="header-text">

                <h1>Data Demografi Desa</h1>

                <p>
                    Data demografi Desa Oelomin berdasarkan data penduduk
                    yang tersimpan dalam sistem.
                </p>

            </div>

        </div>

    </div>


    <!-- ================================================= -->
    <!-- RINGKASAN PENDUDUK -->
    <!-- ================================================= -->

    <div class="demografi-card-grid">

        <div class="demografi-card total">
            <div class="card-icon">👥</div>

            <div class="card-content">
                <span>Total Penduduk</span>
                <strong><?= number_format($total_penduduk) ?></strong>
                <small>Orang</small>
            </div>
        </div>


        <div class="demografi-card laki">
            <div class="card-icon">👨</div>

            <div class="card-content">
                <span>Laki-laki</span>
                <strong><?= number_format($total_laki) ?></strong>
                <small>Orang</small>
            </div>
        </div>


        <div class="demografi-card perempuan">
            <div class="card-icon">👩</div>

            <div class="card-content">
                <span>Perempuan</span>
                <strong><?= number_format($total_perempuan) ?></strong>
                <small>Orang</small>
            </div>
        </div>

    </div>


    <!-- ================================================= -->
    <!-- KELOMPOK UMUR -->
    <!-- ================================================= -->

    <div class="demografi-section">

        <div class="section-header">
            <h2>Kelompok Umur Penduduk</h2>

            <p>
                Pengelompokan penduduk berdasarkan tanggal lahir.
            </p>
        </div>


        <div class="umur-grid">

            <div class="umur-card">
                <span>Anak-anak</span>
                <strong><?= number_format($total_anak) ?></strong>
                <small>0 - 12 tahun</small>
            </div>


            <div class="umur-card">
                <span>Remaja</span>
                <strong><?= number_format($total_remaja) ?></strong>
                <small>13 - 17 tahun</small>
            </div>


            <div class="umur-card">
                <span>Dewasa</span>
                <strong><?= number_format($total_dewasa) ?></strong>
                <small>18 - 59 tahun</small>
            </div>


            <div class="umur-card">
                <span>Lansia</span>
                <strong><?= number_format($total_lansia) ?></strong>
                <small>60 tahun ke atas</small>
            </div>

        </div>

    </div>


    <!-- ================================================= -->
    <!-- DATA STATUS PERKAWINAN -->
    <!-- ================================================= -->

    <div class="demografi-section">

        <div class="section-header">
            <h2>Status Perkawinan</h2>
        </div>


        <div class="table-wrapper">

            <table class="demografi-table">

                <thead>
                    <tr>
                        <th>No</th>
                        <th>Status Perkawinan</th>
                        <th>Jumlah</th>
                    </tr>
                </thead>

                <tbody>

                <?php if (count($data_perkawinan) > 0): ?>

                    <?php $no = 1; ?>

                    <?php foreach ($data_perkawinan as $row): ?>

                        <tr>

                            <td><?= $no++ ?></td>

                            <td>
                                <?= htmlspecialchars($row['status_perkawinan']) ?>
                            </td>

                            <td>
                                <?= number_format($row['jumlah']) ?>
                                orang
                            </td>

                        </tr>

                    <?php endforeach; ?>

                <?php else: ?>

                    <tr>
                        <td colspan="3" class="empty">
                            Belum ada data.
                        </td>
                    </tr>

                <?php endif; ?>

                </tbody>

            </table>

        </div>

    </div>


    <!-- ================================================= -->
    <!-- DATA PENDIDIKAN -->
    <!-- ================================================= -->

    <div class="demografi-section">

        <div class="section-header">
            <h2>Pendidikan Penduduk</h2>
        </div>


        <div class="table-wrapper">

            <table class="demografi-table">

                <thead>
                    <tr>
                        <th>No</th>
                        <th>Pendidikan</th>
                        <th>Jumlah</th>
                    </tr>
                </thead>

                <tbody>

                <?php if (count($data_pendidikan) > 0): ?>

                    <?php $no = 1; ?>

                    <?php foreach ($data_pendidikan as $row): ?>

                        <tr>

                            <td><?= $no++ ?></td>

                            <td>
                                <?= htmlspecialchars($row['pendidikan']) ?>
                            </td>

                            <td>
                                <?= number_format($row['jumlah']) ?>
                                orang
                            </td>

                        </tr>

                    <?php endforeach; ?>

                <?php else: ?>

                    <tr>
                        <td colspan="3" class="empty">
                            Belum ada data.
                        </td>
                    </tr>

                <?php endif; ?>

                </tbody>

            </table>

        </div>

    </div>


    <!-- ================================================= -->
    <!-- DATA PEKERJAAN -->
    <!-- ================================================= -->

    <div class="demografi-section">

        <div class="section-header">
            <h2>Pekerjaan Penduduk</h2>
        </div>


        <div class="table-wrapper">

            <table class="demografi-table">

                <thead>
                    <tr>
                        <th>No</th>
                        <th>Pekerjaan</th>
                        <th>Jumlah</th>
                    </tr>
                </thead>

                <tbody>

                <?php if (count($data_pekerjaan) > 0): ?>

                    <?php $no = 1; ?>

                    <?php foreach ($data_pekerjaan as $row): ?>

                        <tr>

                            <td><?= $no++ ?></td>

                            <td>
                                <?= htmlspecialchars($row['pekerjaan']) ?>
                            </td>

                            <td>
                                <?= number_format($row['jumlah']) ?>
                                orang
                            </td>

                        </tr>

                    <?php endforeach; ?>

                <?php else: ?>

                    <tr>
                        <td colspan="3" class="empty">
                            Belum ada data.
                        </td>
                    </tr>

                <?php endif; ?>

                </tbody>

            </table>

        </div>

    </div>


    <!-- ================================================= -->
    <!-- DATA AGAMA -->
    <!-- ================================================= -->

    <div class="demografi-section">

        <div class="section-header">
            <h2>Agama Penduduk</h2>
        </div>


        <div class="table-wrapper">

            <table class="demografi-table">

                <thead>
                    <tr>
                        <th>No</th>
                        <th>Agama</th>
                        <th>Jumlah</th>
                    </tr>
                </thead>

                <tbody>

                <?php if (count($data_agama) > 0): ?>

                    <?php $no = 1; ?>

                    <?php foreach ($data_agama as $row): ?>

                        <tr>

                            <td><?= $no++ ?></td>

                            <td>
                                <?= htmlspecialchars($row['agama']) ?>
                            </td>

                            <td>
                                <?= number_format($row['jumlah']) ?>
                                orang
                            </td>

                        </tr>

                    <?php endforeach; ?>

                <?php else: ?>

                    <tr>
                        <td colspan="3" class="empty">
                            Belum ada data.
                        </td>
                    </tr>

                <?php endif; ?>

                </tbody>

            </table>

        </div>

    </div>


    <!-- ================================================= -->
    <!-- DATA DUSUN -->
    <!-- ================================================= -->

    <div class="demografi-section">

        <div class="section-header">
            <h2>Jumlah Penduduk Berdasarkan Dusun</h2>
        </div>


        <div class="table-wrapper">

            <table class="demografi-table">

                <thead>
                    <tr>
                        <th>No</th>
                        <th>Dusun</th>
                        <th>Jumlah Penduduk</th>
                    </tr>
                </thead>

                <tbody>

                <?php if (count($data_dusun) > 0): ?>

                    <?php $no = 1; ?>

                    <?php foreach ($data_dusun as $row): ?>

                        <tr>

                            <td><?= $no++ ?></td>

                            <td>
                                <?= htmlspecialchars($row['dusun']) ?>
                            </td>

                            <td>
                                <?= number_format($row['jumlah']) ?>
                                orang
                            </td>

                        </tr>

                    <?php endforeach; ?>

                <?php else: ?>

                    <tr>
                        <td colspan="3" class="empty">
                            Belum ada data.
                        </td>
                    </tr>

                <?php endif; ?>

                </tbody>

            </table>

        </div>

    </div>

</div>

</body>
</html>