<?php
if (!function_exists('mambu_table_exists')) {
    function mambu_table_exists(mysqli $mysqli, string $table): bool
    {
        static $cache = [];
        if (isset($cache[$table])) return $cache[$table];
        if (!preg_match('/^[A-Za-z0-9_]+$/', $table)) return $cache[$table] = false;
        $safe = $mysqli->real_escape_string($table);
        $res = $mysqli->query("SHOW TABLES LIKE '{$safe}'");
        return $cache[$table] = ($res && $res->num_rows > 0);
    }
}

if (!function_exists('mambu_col_exists')) {
    function mambu_col_exists(mysqli $mysqli, string $column, string $table = 'infografis'): bool
    {
        static $cache = [];
        $key = $table.'.'.$column;
        if (isset($cache[$key])) return $cache[$key];
        if (!preg_match('/^[A-Za-z0-9_]+$/', $column) || !preg_match('/^[A-Za-z0-9_]+$/', $table)) {
            return $cache[$key] = false;
        }
        $safe = $mysqli->real_escape_string($column);
        $res = $mysqli->query("SHOW COLUMNS FROM `{$table}` LIKE '{$safe}'");
        return $cache[$key] = ($res && $res->num_rows > 0);
    }
}

if (!function_exists('mambu_fmt')) {
    function mambu_fmt($number): string
    {
        return number_format((int)$number, 0, ',', '.');
    }
}

if (!function_exists('mambu_contains')) {
    function mambu_contains(?string $haystack, string $needle): bool
    {
        return $needle === '' || strpos((string)$haystack, $needle) !== false;
    }
}

if (!function_exists('mambu_existing_cols')) {
    function mambu_existing_cols(mysqli $mysqli, array $columns, string $table = 'infografis'): array
    {
        $existing = [];
        foreach ($columns as $col) {
            if ($col === 'id' || mambu_col_exists($mysqli, $col, $table)) $existing[] = $col;
        }
        return $existing;
    }
}

if (!function_exists('mambu_select_expr')) {
    function mambu_select_expr(mysqli $mysqli, string $column, string $alias = null, string $table = 'infografis'): string
    {
        $alias = $alias ?: $column;
        if ($column === 'id' || mambu_col_exists($mysqli, $column, $table)) return "`{$column}`";
        return "NULL AS `{$alias}`";
    }
}

if (!function_exists('mambu_total_penduduk')) {
    function mambu_total_penduduk(mysqli $mysqli): int
    {
        if (!mambu_table_exists($mysqli, 'infografis')) return 0;
        $res = $mysqli->query("SELECT COUNT(*) c FROM infografis");
        return $res ? (int)($res->fetch_assoc()['c'] ?? 0) : 0;
    }
}

if (!function_exists('mambu_total_kk')) {
    function mambu_total_kk(mysqli $mysqli): int
    {
        if (!mambu_table_exists($mysqli, 'infografis') || !mambu_col_exists($mysqli, 'nomor_kk')) return 0;
        $res = $mysqli->query("SELECT COUNT(DISTINCT nomor_kk) c FROM infografis WHERE nomor_kk IS NOT NULL AND TRIM(nomor_kk) <> ''");
        return $res ? (int)($res->fetch_assoc()['c'] ?? 0) : 0;
    }
}

if (!function_exists('mambu_count_gender')) {
    function mambu_count_gender(mysqli $mysqli, string $gender): int
    {
        if (!mambu_table_exists($mysqli, 'infografis') || !mambu_col_exists($mysqli, 'jenis_kelamin')) return 0;
        $gender = strtolower($gender);
        if ($gender === 'laki') {
            $where = "LOWER(TRIM(jenis_kelamin)) IN ('l','laki-laki','laki laki','pria') OR LOWER(jenis_kelamin) LIKE '%laki%'";
        } else {
            $where = "LOWER(TRIM(jenis_kelamin)) IN ('p','perempuan','wanita') OR LOWER(jenis_kelamin) LIKE '%perempuan%'";
        }
        $res = $mysqli->query("SELECT COUNT(*) c FROM infografis WHERE {$where}");
        return $res ? (int)($res->fetch_assoc()['c'] ?? 0) : 0;
    }
}

if (!function_exists('mambu_count_age')) {
    function mambu_count_age(mysqli $mysqli, ?int $min, ?int $max): int
    {
        if (!mambu_table_exists($mysqli, 'infografis') || !mambu_col_exists($mysqli, 'tanggal_lahir')) return 0;
        $age = "TIMESTAMPDIFF(YEAR, tanggal_lahir, CURDATE())";
        if ($min !== null && $max !== null) $where = "$age BETWEEN $min AND $max";
        elseif ($min !== null) $where = "$age >= $min";
        elseif ($max !== null) $where = "$age <= $max";
        else $where = "1=1";
        $res = $mysqli->query("SELECT COUNT(*) c FROM infografis WHERE tanggal_lahir IS NOT NULL AND tanggal_lahir <> '0000-00-00' AND {$where}");
        return $res ? (int)($res->fetch_assoc()['c'] ?? 0) : 0;
    }
}

if (!function_exists('mambu_age_buckets')) {
    function mambu_age_buckets(mysqli $mysqli): array
    {
        return [
            ['label' => '0-4',  'total' => mambu_count_age($mysqli, 0, 4),   'color' => '#6fb6ff'],
            ['label' => '5-9',  'total' => mambu_count_age($mysqli, 5, 9),   'color' => '#57d38c'],
            ['label' => '10-14','total' => mambu_count_age($mysqli, 10, 14), 'color' => '#ffd866'],
            ['label' => '15-24','total' => mambu_count_age($mysqli, 15, 24), 'color' => '#ff9968'],
            ['label' => '25-44','total' => mambu_count_age($mysqli, 25, 44), 'color' => '#ff5c7a'],
            ['label' => '45-64','total' => mambu_count_age($mysqli, 45, 64), 'color' => '#8d67e8'],
            ['label' => '≥ 65', 'total' => mambu_count_age($mysqli, 65, null),'color' => '#94a3b8'],
        ];
    }
}

if (!function_exists('mambu_group_counts')) {
    function mambu_group_counts(mysqli $mysqli, string $column, int $limit = 6): array
    {
        if (!mambu_table_exists($mysqli, 'infografis') || !mambu_col_exists($mysqli, $column)) return [];
        $limit = max(1, min(20, $limit));
        $sql = "SELECT COALESCE(NULLIF(TRIM(`{$column}`), ''), 'Tidak diketahui') label, COUNT(*) total
                FROM infografis
                GROUP BY label
                ORDER BY total DESC, label ASC
                LIMIT {$limit}";
        $res = $mysqli->query($sql);
        $rows = [];
        if ($res) while ($r = $res->fetch_assoc()) $rows[] = ['label' => $r['label'], 'total' => (int)$r['total']];
        return $rows;
    }
}

if (!function_exists('mambu_recent_penduduk')) {
    function mambu_recent_penduduk(mysqli $mysqli, int $limit = 5): array
    {
        if (!mambu_table_exists($mysqli, 'infografis')) return [];
        $columns = ['id','nik','nama_lengkap','jenis_kelamin','tempat_lahir','tanggal_lahir','dusun','status_perkawinan','nomor_kk'];
        $select = [];
        foreach ($columns as $col) if ($col === 'id' || mambu_col_exists($mysqli, $col)) $select[] = "`$col`";
        $limit = max(1, min(30, $limit));
        $res = $mysqli->query("SELECT ".implode(',', $select)." FROM infografis ORDER BY id DESC LIMIT {$limit}");
        $rows = [];
        if ($res) while ($r = $res->fetch_assoc()) $rows[] = $r;
        return $rows;
    }
}

if (!function_exists('mambu_birthplace')) {
    function mambu_birthplace(array $row): string
    {
        $place = trim((string)($row['tempat_lahir'] ?? ''));
        $date = trim((string)($row['tanggal_lahir'] ?? ''));
        if ($date && $date !== '0000-00-00') {
            $date = date('d-m-Y', strtotime($date));
        } else {
            $date = '';
        }
        return trim($place . ($place && $date ? ', ' : '') . $date) ?: '-';
    }
}

if (!function_exists('mambu_age_from_date')) {
    function mambu_age_from_date(?string $date): string
    {
        if (!$date || $date === '0000-00-00') return '-';
        try {
            $birth = new DateTime($date);
            return (string)$birth->diff(new DateTime('today'))->y;
        } catch (Throwable $e) {
            return '-';
        }
    }
}

if (!function_exists('mambu_pct')) {
    function mambu_pct(int $part, int $total, int $precision = 1): string
    {
        if ($total <= 0) return '0%';
        return number_format(($part / $total) * 100, $precision, ',', '.') . '%';
    }
}
?>
