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

$bpw = $conn->query("SELECT * FROM profil_dpw WHERE id=1")->fetch(PDO::FETCH_ASSOC);
$nama_bpw = $bpw['nama_dpw']?? 'BPW Keerom';
$alamat_bpw = $bpw['alamat']?? '-';
$ketua_bpw = $bpw['ketua_dpw']?? 'Ketua BPW';

$total_gereja = $conn->query("SELECT COUNT(*) FROM gereja")->fetchColumn();
$total_gembala = $conn->query("SELECT COUNT(*) FROM users WHERE role='gembala'")->fetchColumn();
$total_pelayan = $conn->query("SELECT COUNT(*) FROM pelayan")->fetchColumn();
$total_pejabat = $conn->query("SELECT COUNT(*) FROM pejabat_gereja")->fetchColumn();
$total_jemaat = $conn->query("SELECT COUNT(*) FROM jemaat")->fetchColumn();

$jk = $conn->query("SELECT
    SUM(CASE WHEN jenis_kelamin='L' THEN 1 ELSE 0 END) as laki,
    SUM(CASE WHEN jenis_kelamin='P' THEN 1 ELSE 0 END) as perempuan
    FROM jemaat")->fetch(PDO::FETCH_ASSOC);

$umur = $conn->query("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")->fetch(PDO::FETCH_ASSOC);

$wbi = $conn->query("SELECT COUNT(*) FROM jemaat WHERE jenis_kelamin='P' AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) >= 18")->fetchColumn();

$data_gereja = $conn->query("SELECT g.nama_gereja, u.nama_lengkap as nama_gembala,
    (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id) as jml_jemaat,
    (SELECT COUNT(*) FROM pelayan WHERE gereja_id=g.id) as jml_pelayan,
    (SELECT COUNT(*) FROM pejabat_gereja WHERE gereja_id=g.id) as jml_pejabat
    FROM gereja g 
    LEFT JOIN users u ON g.id=u.gereja_id AND u.role='gembala'
    ORDER BY g.nama_gereja ASC")->fetchAll(PDO::FETCH_ASSOC);

ob_end_clean();

class MYPDF extends TCPDF {
    public $logo = '../assets/img/logo-gbi.JPG';
    public $nama_bpw = '';
    public $alamat_bpw = '';

    public function Header() {
        if(file_exists($this->logo)) $this->Image($this->logo, 15, 8, 25, '', 'JPG');
        $this->SetY(10);
        $this->SetFont('helvetica', 'B', 16);
        $this->Cell(0, 7, 'LAPORAN REKAPITULASI BPW', 0, 1, 'C');
        $this->SetFont('helvetica', 'B', 14);
        $this->Cell(0, 7, strtoupper($this->nama_bpw), 0, 1, 'C');
        $this->SetFont('helvetica', '', 10);
        $this->Cell(0, 5, $this->alamat_bpw, 0, 1, 'C');
        $this->SetLineWidth(0.8);
        $this->Line(15, 33, 195, 33);
        $this->SetLineWidth(0.2);
        $this->Line(15, 34, 195, 34);
        $this->Ln(8);
    }
}

$pdf = new MYPDF('P', 'mm', 'A4', true, 'UTF-8', false);
$pdf->nama_bpw = $nama_bpw;
$pdf->alamat_bpw = $alamat_bpw;
$pdf->SetMargins(15, 38, 15);
$pdf->SetAutoPageBreak(TRUE, 25);
$pdf->AddPage();

$pdf->SetFont('helvetica', 'B', 12);
$pdf->Cell(0, 8, 'A. REKAPITULASI UMUM BPW', 0, 1, 'L');
$pdf->SetFont('helvetica', '', 10);
$html = '<table border="1" cellpadding="4">
<tr><td width="40%">Total Gereja</td><td width="10%" align="center"><b>'.$total_gereja.'</b></td><td width="40%">Total Jemaat</td><td width="10%" align="center"><b>'.$total_jemaat.'</b></td></tr>
<tr><td>Total Gembala</td><td align="center"><b>'.$total_gembala.'</b></td><td>Total Pelayan</td><td align="center"><b>'.$total_pelayan.'</b></td></tr>
<tr><td>Total Pejabat Gereja</td><td align="center"><b>'.$total_pejabat.'</b></td><td></td></tr>
</table>';
$pdf->writeHTML($html, true, false, '');
$pdf->Ln(3);

$pdf->SetFont('helvetica', 'B', 12);
$pdf->Cell(0, 8, 'B. STATISTIK KESELURUHAN JEMAAT', 0, 1, 'L');
$pdf->SetFont('helvetica', 'B', 9);
$pdf->Cell(60, 6, 'Kategori', 1, 0, 'C');
$pdf->Cell(30, 6, 'Jumlah', 1, 0, 'C');
$pdf->Cell(30, 6, 'Persentase', 1, 1, 'C');
$pdf->SetFont('helvetica', '', 9);

$kategori = [
    ['Laki-laki', $jk['laki']],
    ['Perempuan', $jk['perempuan']],
    ['WBI (Wanita 18+)', $wbi],
    ['Balita (0-5 Thn)', $umur['balita']],
    ['Anak-anak (6-12 Thn)', $umur['anak']],
    ['Remaja (13-17 Thn)', $umur['remaja']],
    ['Pemuda (18-30 Thn)', $umur['pemuda']],
    ['Dewasa (31-59 Thn)', $umur['dewasa']],
    ['Lansia (60+ Thn)', $umur['lansia']],
];

foreach($kategori as $k){
    $persen = $total_jemaat > 0? round($k[1]/$total_jemaat*100,1).'%' : '0%';
    $pdf->Cell(60, 6, $k[0], 1, 0, 'L');
    $pdf->Cell(30, 6, $k[1].' Jiwa', 1, 0, 'C');
    $pdf->Cell(30, 6, $persen, 1, 1, 'C');
}
$pdf->Ln(3);

$pdf->AddPage();
$pdf->SetFont('helvetica', 'B', 12);
$pdf->Cell(0, 8, 'C. DAFTAR GEREJA & STRUKTUR', 0, 1, 'L');
$pdf->SetFont('helvetica', 'B', 8);
$pdf->Cell(8, 6, 'No', 1, 0, 'C');
$pdf->Cell(50, 6, 'Nama Gereja', 1, 0, 'C');
$pdf->Cell(45, 6, 'Gembala', 1, 0, 'C');
$pdf->Cell(20, 6, 'Jemaat', 1, 0, 'C');
$pdf->Cell(20, 6, 'Pelayan', 1, 0, 'C');
$pdf->Cell(20, 6, 'Pejabat', 1, 1, 'C');
$pdf->SetFont('helvetica', '', 8);

$no = 1;
foreach($data_gereja as $g){
    if($pdf->GetY() > 265){
        $pdf->AddPage();
        $pdf->SetFont('helvetica', 'B', 8);
        $pdf->Cell(8, 6, 'No', 1, 0, 'C');
        $pdf->Cell(50, 6, 'Nama Gereja', 1, 0, 'C');
        $pdf->Cell(45, 6, 'Gembala', 1, 0, 'C');
        $pdf->Cell(20, 6, 'Jemaat', 1, 0, 'C');
        $pdf->Cell(20, 6, 'Pelayan', 1, 0, 'C');
        $pdf->Cell(20, 6, 'Pejabat', 1, 1, 'C');
        $pdf->SetFont('helvetica', '', 8);
    }
    $pdf->Cell(8, 5, $no++, 1, 0, 'C');
    $pdf->Cell(50, 5, $g['nama_gereja'], 1, 0, 'L');
    $pdf->Cell(45, 5, $g['nama_gembala']?? '-', 1, 0, 'L');
    $pdf->Cell(20, 5, $g['jml_jemaat'], 1, 0, 'C');
    $pdf->Cell(20, 5, $g['jml_pelayan'], 1, 0, 'C');
    $pdf->Cell(20, 5, $g['jml_pejabat'], 1, 1, 'C');
}

$pdf->Ln(15);
$pdf->SetFont('helvetica', '', 10);
$pdf->Cell(110, 5, '', 0, 0);
$pdf->Cell(70, 5, 'Keerom, '.date('d F Y'), 0, 1, 'C');
$pdf->Cell(110, 5, '', 0, 0);
$pdf->Cell(70, 5, 'Ketua BPW,', 0, 1, 'C');
$pdf->Ln(20);
$pdf->Cell(110, 5, '', 0, 0);
$pdf->SetFont('helvetica', 'B, U', 10);
$pdf->Cell(70, 5, $ketua_bpw, 0, 1, 'C');

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