<?php
include 'koneksi.php';

header('Content-Type: application/json');

/* ====================================
   TOTAL BERITA
==================================== */
$totalBerita = 0;

$queryTotal = mysqli_query($koneksi, "
    SELECT COUNT(*) AS total 
    FROM berita
");

if($queryTotal){
    $data = mysqli_fetch_assoc($queryTotal);
    $totalBerita = $data['total'];
}

/* ====================================
   BERITA BULAN INI
==================================== */
$bulanIni = date('m');
$tahunIni = date('Y');

$beritaBulanIni = 0;

$queryBulan = mysqli_query($koneksi, "
    SELECT COUNT(*) AS total
    FROM berita
    WHERE MONTH(tanggal) = '$bulanIni'
    AND YEAR(tanggal) = '$tahunIni'
");

if($queryBulan){
    $data = mysqli_fetch_assoc($queryBulan);
    $beritaBulanIni = $data['total'];
}

/* ====================================
   TOTAL GALERI (berita yang punya gambar)
==================================== */
$totalGaleri = 0;

$queryGaleri = mysqli_query($koneksi, "
    SELECT COUNT(*) AS total
    FROM berita
    WHERE gambar IS NOT NULL AND gambar != ''
");

if($queryGaleri){
    $data = mysqli_fetch_assoc($queryGaleri);
    $totalGaleri = $data['total'];
}

/* ====================================
   PENGUNJUNG OTOMATIS
   (Hitung unique visitor per hari berdasarkan IP)
==================================== */

// Cek apakah tabel pengunjung ada, jika tidak buat otomatis
$cekTable = mysqli_query($koneksi, "SHOW TABLES LIKE 'pengunjung'");
if(mysqli_num_rows($cekTable) == 0){
    mysqli_query($koneksi, "
        CREATE TABLE pengunjung (
            id INT AUTO_INCREMENT PRIMARY KEY,
            ip_address VARCHAR(100),
            tanggal DATE,
            user_agent VARCHAR(255)
        )
    ");
}

$ip = $_SERVER['REMOTE_ADDR'];
$tanggalHariIni = date('Y-m-d');
$user_agent = isset($_SERVER['HTTP_USER_AGENT']) ? substr($_SERVER['HTTP_USER_AGENT'], 0, 255) : '';

// Cek apakah IP sudah masuk hari ini
$cek = mysqli_query($koneksi, "
    SELECT * FROM pengunjung
    WHERE ip_address = '$ip'
    AND tanggal = '$tanggalHariIni'
");

if(mysqli_num_rows($cek) == 0){
    mysqli_query($koneksi, "
        INSERT INTO pengunjung(ip_address, tanggal, user_agent)
        VALUES('$ip', '$tanggalHariIni', '$user_agent')
    ");
}

// Hitung total pengunjung (unique visitor)
$queryPengunjung = mysqli_query($koneksi, "
    SELECT COUNT(DISTINCT ip_address) AS total
    FROM pengunjung
");

$dataPengunjung = mysqli_fetch_assoc($queryPengunjung);
$pengunjung = $dataPengunjung['total'];

// Hitung pengunjung hari ini
$pengunjungHariIni = 0;
$queryPengunjungHari = mysqli_query($koneksi, "
    SELECT COUNT(DISTINCT ip_address) AS total
    FROM pengunjung
    WHERE tanggal = '$tanggalHariIni'
");
if($queryPengunjungHari){
    $data = mysqli_fetch_assoc($queryPengunjungHari);
    $pengunjungHariIni = $data['total'];
}

/* ====================================
   BERITA TERBARU (5 berita terakhir)
==================================== */
$recentNews = [];

$queryRecent = mysqli_query($koneksi, "
    SELECT id, judul, tanggal
    FROM berita
    ORDER BY id DESC
    LIMIT 5
");

while($row = mysqli_fetch_assoc($queryRecent)){
    $recentNews[] = [
        'id' => $row['id'],
        'judul' => $row['judul'],
        'tanggal' => date('d F Y', strtotime($row['tanggal']))
    ];
}

/* ====================================
   DATA GRAFIK BERITA PER BULAN
==================================== */
$chartLabels = [
    'Jan', 'Feb', 'Mar', 'Apr', 'Mei', 'Jun',
    'Jul', 'Agu', 'Sep', 'Okt', 'Nov', 'Des'
];

$chartData = [];

for($i = 1; $i <= 12; $i++){
    $bulan = sprintf("%02d", $i);
    
    $queryChart = mysqli_query($koneksi, "
        SELECT COUNT(*) AS total
        FROM berita
        WHERE MONTH(tanggal) = '$bulan'
        AND YEAR(tanggal) = '$tahunIni'
    ");
    
    $hasil = mysqli_fetch_assoc($queryChart);
    $chartData[] = (int)$hasil['total'];
}

/* ====================================
   TOTAL LAYANAN (opsional)
==================================== */
$totalLayanan = 0;

// Cek apakah tabel layanan ada
$cekLayanan = mysqli_query($koneksi, "SHOW TABLES LIKE 'layanan'");
if(mysqli_num_rows($cekLayanan) > 0){
    $queryLayanan = mysqli_query($koneksi, "SELECT COUNT(*) AS total FROM layanan");
    if($queryLayanan){
        $data = mysqli_fetch_assoc($queryLayanan);
        $totalLayanan = $data['total'];
    }
} else {
    // Jika tabel layanan belum ada, gunakan default 4 (sesuai visi misi)
    $totalLayanan = 4;
}

/* ====================================
   OUTPUT JSON
==================================== */
echo json_encode([
    'total_berita' => $totalBerita,
    'berita_bulan_ini' => $beritaBulanIni,
    'pengunjung' => $pengunjung,
    'pengunjung_hari_ini' => $pengunjungHariIni,
    'total_galeri' => $totalGaleri,
    'total_layanan' => $totalLayanan,
    'recent_news' => $recentNews,
    'chart_labels' => $chartLabels,
    'chart_data' => $chartData,
    'tahun_aktif' => $tahunIni,
    'status' => 'success'
]);

?>