<?php
ob_start();
require_once '../config/auth.php';
cek_role('gembala');
require_once '../vendor/autoload.php';

$gereja_id = $_SESSION['gereja_id'];
$user_id = $_SESSION['user_id'];

// 1. DATA GEREJA - AMANIN SEMUA KOLOM
$gereja = $conn->prepare("SELECT * FROM gereja WHERE id =?");
$gereja->execute([$gereja_id]);
$info_gereja = $gereja->fetch(PDO::FETCH_ASSOC);
$nama_gereja = $info_gereja['nama_gereja']?? 'GBI';
$alamat_gereja = $info_gereja['alamat']?? '-';
$telp_gereja = $info_gereja['no_telp']?? $info_gereja['telp']?? $info_gereja['telepon']?? '-';
$email_gereja = $info_gereja['email']?? '-';

// 2. DATA GEMBALA YG LOGIN - VERSI AMAN
$gembala = $conn->prepare("SELECT * FROM users WHERE id =?");
$gembala->execute([$user_id]);
$data_user = $gembala->fetch(PDO::FETCH_ASSOC);
$nama_gembala = $data_user['nama_lengkap']?? $data_user['nama']?? $data_user['username']?? 'Gembala';
$jabatan_gembala = $data_user['jabatan']?? 'Gembala Sidang';

// 3. STATISTIK
$total = $conn->prepare("SELECT COUNT(*) as jml FROM jemaat WHERE gereja_id =?");
$total->execute([$gereja_id]);
$total_jemaat = $total->fetch(PDO::FETCH_ASSOC)['jml'];

$jk = $conn->prepare("SELECT
    SUM(CASE WHEN jenis_kelamin = 'L' THEN 1 ELSE 0 END) as laki,
    SUM(CASE WHEN jenis_kelamin = 'P' THEN 0 ELSE 1 END) as perempuan
    FROM jemaat WHERE gereja_id =?");
$jk->execute([$gereja_id]);
$data_jk = $jk->fetch(PDO::FETCH_ASSOC);

$umur = $conn->prepare("SELECT
    SUM(CASE WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) <= 5 THEN 1 ELSE 0 END) as balita,
    SUM(CASE WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 6 AND 12 THEN 1 ELSE 0 END) as anak,
    SUM(CASE WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 13 AND 17 THEN 1 ELSE 0 END) as remaja,
    SUM(CASE WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 18 AND 30 THEN 1 ELSE 0 END) as pemuda,
    SUM(CASE WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 31 AND 59 THEN 1 ELSE 0 END) as dewasa,
    SUM(CASE WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) >= 60 THEN 1 ELSE 0 END) as lansia
    FROM jemaat WHERE gereja_id =?");
$umur->execute([$gereja_id]);
$data_umur = $umur->fetch(PDO::FETCH_ASSOC);

$wbi = $conn->prepare("SELECT COUNT(*) as jml FROM jemaat
    WHERE gereja_id =? AND jenis_kelamin = 'P' AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) >= 18");
$wbi->execute([$gereja_id]);
$jml_wbi = $wbi->fetch(PDO::FETCH_ASSOC)['jml'];

// 4. DAFTAR NAMA JEMAAT
$list_jemaat = $conn->prepare("SELECT nama_lengkap, jenis_kelamin, TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) as umur, alamat
    FROM jemaat WHERE gereja_id =? ORDER BY nama_lengkap ASC");
$list_jemaat->execute([$gereja_id]);

ob_end_clean();

class MYPDF extends TCPDF {
    public $logo_gbi = '../assets/img/logo-gbi.JPG';
    public $nama_gereja = '';
    public $alamat_gereja = '';
    public $telp_gereja = '';
    public $email_gereja = '';

    public function Header() {
        if(file_exists($this->logo_gbi)){
            $this->Image($this->logo_gbi, 15, 8, 25, '', 'JPG', '', 'T', false, 300, '', false, false, 0, false, false, false);
        }
        $this->SetY(10);
        $this->SetFont('helvetica', 'B', 14);
        $this->Cell(0, 6, 'GEREJA BETHEL INDONESIA', 0, 1, 'C');
        $this->SetFont('helvetica', 'B', 16);
        $this->Cell(0, 7, strtoupper($this->nama_gereja), 0, 1, 'C');
        $this->SetFont('helvetica', '', 10);
        $this->Cell(0, 5, $this->alamat_gereja, 0, 1, 'C');
        $this->Cell(0, 5, 'Telp: '.$this->telp_gereja.' | Email: '.$this->email_gereja, 0, 1, 'C');
        $this->SetLineWidth(0.8);
        $this->Line(15, 35, 195, 35);
        $this->SetLineWidth(0.2);
        $this->Line(15, 36, 195, 36);
        $this->Ln(8);
    } // <-- INI YG KELEWAT KEMARIN
}

$pdf = new MYPDF('P', 'mm', 'A4', true, 'UTF-8', false);
$pdf->nama_gereja = $nama_gereja;
$pdf->alamat_gereja = $alamat_gereja;
$pdf->telp_gereja = $telp_gereja;
$pdf->email_gereja = $email_gereja;
$pdf->SetCreator('SIMJEDPW');
$pdf->SetAuthor('SIMJEDPW');
$pdf->SetTitle('Laporan Statistik Jemaat');
$pdf->SetMargins(15, 40, 15);
$pdf->SetAutoPageBreak(TRUE, 30);
$pdf->AddPage();

$pdf->SetFont('helvetica', 'B', 12);
$pdf->Cell(0, 8, 'LAPORAN STATISTIK & DATA JEMAAT', 0, 1, 'C');
$pdf->SetFont('helvetica', '', 10);
$pdf->Cell(0, 5, 'Per Tanggal: '. date('d F Y'), 0, 1, 'C');
$pdf->Ln(3);

$pdf->SetFont('helvetica', 'B', 11);
$pdf->Cell(0, 7, 'A. RINGKASAN STATISTIK', 0, 1, 'L');
$pdf->SetFont('helvetica', '', 10);
$pdf->Cell(60, 6, 'Total Jemaat', 1, 0, 'L');
$pdf->Cell(30, 6, $total_jemaat. ' Jiwa', 1, 0, 'C');
$pdf->Cell(60, 6, 'WBI (Wanita 18+)', 1, 0, 'L');
$pdf->Cell(30, 6, $jml_wbi. ' Jiwa', 1, 1, 'C');
$pdf->Cell(60, 6, 'Laki-laki', 1, 0, 'L');
$pdf->Cell(30, 6, $data_jk['laki']. ' Jiwa', 1, 0, 'C');
$pdf->Cell(60, 6, 'Perempuan', 1, 0, 'L');
$pdf->Cell(30, 6, $data_jk['perempuan']. ' Jiwa', 1, 1, 'C');
$pdf->Ln(3);

$pdf->SetFont('helvetica', 'B', 11);
$pdf->Cell(0, 7, 'B. KATEGORI UMUR', 0, 1, 'L');
$pdf->SetFont('helvetica', 'B', 9);
$pdf->Cell(50, 6, 'Kategori', 1, 0, 'C');
$pdf->Cell(40, 6, 'Rentang Umur', 1, 0, 'C');
$pdf->Cell(30, 6, 'Jumlah', 1, 0, 'C');
$pdf->Cell(30, 6, 'Persentase', 1, 1, 'C');

$pdf->SetFont('helvetica', '', 9);
$data_kategori = [
    ['Balita', '0 - 5 Tahun', $data_umur['balita']],
    ['Anak', '6 - 12 Tahun', $data_umur['anak']],
    ['Remaja', '13 - 17 Tahun', $data_umur['remaja']],
    ['Pemuda', '18 - 30 Tahun', $data_umur['pemuda']],
    ['Dewasa', '31 - 59 Tahun', $data_umur['dewasa']],
    ['Lansia', '60+ Tahun', $data_umur['lansia']],
];

foreach($data_kategori as $kat){
    $persen = $total_jemaat > 0? round($kat[2]/$total_jemaat*100,1) : 0;
    $pdf->Cell(50, 6, $kat[0], 1, 0, 'L');
    $pdf->Cell(40, 6, $kat[1], 1, 0, 'C');
    $pdf->Cell(30, 6, $kat[2], 1, 0, 'C');
    $pdf->Cell(30, 6, $persen.'%', 1, 1, 'C');
}
$pdf->Ln(3);

$pdf->AddPage();
$pdf->SetFont('helvetica', 'B', 11);
$pdf->Cell(0, 7, 'C. DAFTAR NAMA KESELURUHAN JEMAAT', 0, 1, 'L');
$pdf->SetFont('helvetica', 'B', 8);
$pdf->Cell(10, 6, 'No', 1, 0, 'C');
$pdf->Cell(60, 6, 'Nama Lengkap', 1, 0, 'C');
$pdf->Cell(20, 6, 'L/P', 1, 0, 'C');
$pdf->Cell(15, 6, 'Umur', 1, 0, 'C');
$pdf->Cell(75, 6, 'Alamat', 1, 1, 'C');

$pdf->SetFont('helvetica', '', 8);
$no = 1;
while($j = $list_jemaat->fetch(PDO::FETCH_ASSOC)){
    $jk_singkat = $j['jenis_kelamin'];
    $umur_j = $j['umur']? $j['umur'].' Thn' : '-';
    if($pdf->GetY() > 250){
        $pdf->AddPage();
        $pdf->SetFont('helvetica', 'B', 8);
        $pdf->Cell(10, 6, 'No', 1, 0, 'C');
        $pdf->Cell(60, 6, 'Nama Lengkap', 1, 0, 'C');
        $pdf->Cell(20, 6, 'L/P', 1, 0, 'C');
        $pdf->Cell(15, 6, 'Umur', 1, 0, 'C');
        $pdf->Cell(75, 6, 'Alamat', 1, 1, 'C');
        $pdf->SetFont('helvetica', '', 8);
    }
    $pdf->Cell(10, 5, $no++, 1, 0, 'C');
    $pdf->Cell(60, 5, $j['nama_lengkap'], 1, 0, 'L');
    $pdf->Cell(20, 5, $jk_singkat, 1, 0, 'C');
    $pdf->Cell(15, 5, $umur_j, 1, 0, 'C');
    $pdf->Cell(75, 5, $j['alamat'], 1, 1, 'L');
}

$pdf->Ln(15);
$tgl_cetak = date('d F Y');
$kota = explode(',', $alamat_gereja);
$kota = trim($kota[0]?? 'Kota');

$pdf->SetFont('helvetica', '', 10);
$pdf->Cell(110, 5, '', 0, 0);
$pdf->Cell(70, 5, $kota.', '.$tgl_cetak, 0, 1, 'C');
$pdf->Cell(110, 5, '', 0, 0);
$pdf->Cell(70, 5, 'Gembala Sidang,', 0, 1, 'C');
$pdf->Ln(20);
$pdf->Cell(110, 5, '', 0, 0);
$pdf->SetFont('helvetica', 'B, U', 10);
$pdf->Cell(70, 5, $nama_gembala, 0, 1, 'C');
$pdf->Cell(110, 5, '', 0, 0);
$pdf->SetFont('helvetica', '', 10);
$pdf->Cell(70, 5, $jabatan_gembala, 0, 1, 'C');

$pdf->Output('Laporan_Jemaat_'.$nama_gereja.'_'.date('Ymd').'.pdf', 'I');
exit;
?>