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

// 1. DATA DPW - FALLBACK
try {
    $dpw = $conn->query("SELECT * FROM profil_dpw WHERE id=1")->fetch(PDO::FETCH_ASSOC);
    $nama_dpw = $dpw['nama_dpw']?? 'BPW Keerom';
    $alamat_dpw = $dpw['alamat']?? 'Jl. Trans Irian, Keerom, Papua';
    $ketua_dpw = $dpw['ketua_dpw']?? 'Ketua BPW';
    $telp_dpw = $dpw['no_telp']?? '-';
    $email_dpw = $dpw['email']?? '-';
} catch (PDOException $e) {
    $nama_dpw = 'BPW Keerom';
    $alamat_dpw = 'Jl. Trans Irian, Keerom, Papua';
    $ketua_dpw = 'Ketua BPW';
    $telp_dpw = '-';
    $email_dpw = '-';
}

// 2. CEK NAMA KOLOM GEMBALA DI TABEL USERS
$kolom_nama_gembala = 'username'; // DEFAULT
try {
    $cols = $conn->query("SHOW COLUMNS FROM users")->fetchAll(PDO::FETCH_COLUMN);
    if(in_array('nama_lengkap', $cols)) $kolom_nama_gembala = 'nama_lengkap';
    elseif(in_array('nama', $cols)) $kolom_nama_gembala = 'nama';
    elseif(in_array('name', $cols)) $kolom_nama_gembala = 'name';
    elseif(in_array('full_name', $cols)) $kolom_nama_gembala = 'full_name';
} catch (PDOException $e) {}

// 3. AMBIL DATA GEREJA + STATISTIK
$data_gereja = [];
try {
    $sql = "SELECT 
        g.id,
        g.nama_gereja, 
        g.alamat, 
        u.{$kolom_nama_gembala} 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,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND jenis_kelamin='P' AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) >= 18) as jml_wbi,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 18 AND 30) as jml_pemuda,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 13 AND 17) as jml_remaja,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 6 AND 12) as jml_anak,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) <= 5) as jml_balita,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) >= 60) as jml_lansia
        FROM gereja g 
        LEFT JOIN users u ON g.id=u.gereja_id AND u.role='gembala'
        ORDER BY g.nama_gereja ASC";
    $data_gereja = $conn->query($sql)->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    // KALO GAGAL, QUERY TANPA PEJABAT_GEREJA
    $sql = "SELECT 
        g.id,
        g.nama_gereja, 
        g.alamat, 
        u.{$kolom_nama_gembala} 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,
        0 as jml_pejabat,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND jenis_kelamin='P' AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) >= 18) as jml_wbi,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 18 AND 30) as jml_pemuda,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 13 AND 17) as jml_remaja,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) BETWEEN 6 AND 12) as jml_anak,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) <= 5) as jml_balita,
        (SELECT COUNT(*) FROM jemaat WHERE gereja_id=g.id AND TIMESTAMPDIFF(YEAR, tgl_lahir, CURDATE()) >= 60) as jml_lansia
        FROM gereja g 
        LEFT JOIN users u ON g.id=u.gereja_id AND u.role='gembala'
        ORDER BY g.nama_gereja ASC";
    $data_gereja = $conn->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}

$total_gereja = count($data_gereja);

ob_end_clean();

// FOLIO LANDSCAPE: 215.9 x 330.2 mm
class MYPDF extends TCPDF {
    public $logo = '../assets/img/logo-gbi.JPG';
    public $nama_dpw = '';
    public $alamat_dpw = '';
    public $telp_dpw = '';
    public $email_dpw = '';

    public function Header() {
        if(file_exists($this->logo)) $this->Image($this->logo, 15, 8, 20, '', 'JPG');
        $this->SetY(8);
        $this->SetFont('helvetica', 'B', 14);
        $this->Cell(0, 6, 'LAPORAN DATA GEREJA', 0, 1, 'C');
        $this->SetFont('helvetica', 'B', 12);
        $this->Cell(0, 6, strtoupper($this->nama_dpw), 0, 1, 'C');
        $this->SetFont('helvetica', '', 8);
        $this->Cell(0, 4, $this->alamat_dpw.' | Telp: '.$this->telp_dpw.' | Email: '.$this->email_dpw, 0, 1, 'C');
        $this->SetLineWidth(0.6);
        $this->Line(15, 28, 315, 28);
        $this->Ln(6);
    }
    public function Footer() {
        $this->SetY(-12);
        $this->SetFont('helvetica', 'I', 7);
        $this->Cell(0, 8, 'Dicetak: '.date('d-m-Y H:i').' | Halaman '.$this->getAliasNumPage().' dari '.$this->getAliasNbPages(), 0, 0, 'C');
    }
}

$pdf = new MYPDF('L', 'mm', array(215.9, 330.2), true, 'UTF-8', false);
$pdf->nama_dpw = $nama_dpw;
$pdf->alamat_dpw = $alamat_dpw;
$pdf->telp_dpw = $telp_dpw;
$pdf->email_dpw = $email_dpw;
$pdf->SetMargins(15, 32, 15);
$pdf->SetAutoPageBreak(TRUE, 15);
$pdf->SetTitle('Laporan Data Gereja '.$nama_dpw);
$pdf->AddPage();

$pdf->SetFont('helvetica', 'B', 11);
$pdf->Cell(0, 7, 'DAFTAR GEREJA SE-'.strtoupper($nama_dpw).' BESERTA STATISTIK JEMAAT', 0, 1, 'L');
$pdf->SetFont('helvetica', '', 9);
$pdf->Cell(0, 5, 'Total Gereja Terdaftar: '.$total_gereja.' Gereja', 0, 1, 'L');
$pdf->Ln(2);

$pdf->SetFont('helvetica', 'B', 6.5);
$pdf->SetFillColor(230, 230, 230);
$pdf->Cell(7, 7, 'No', 1, 0, 'C', 1);
$pdf->Cell(40, 6, 'Nama Gereja', 1, 0, 'C', 1);
$pdf->Cell(50, 6, 'Gembala', 1, 0, 'C', 1);
$pdf->Cell(20, 6, 'Jemaat', 1, 0, 'C', 1);
$pdf->Cell(20, 6, 'Pelayan', 1, 0, 'C', 1);
$pdf->Cell(20, 6, 'Pejabat', 1, 0, 'C', 1);
$pdf->Cell(20, 6, 'WBI', 1, 0, 'C', 1);
$pdf->Cell(20, 6, 'Pemuda', 1, 0, 'C', 1);
$pdf->Cell(20, 6, 'Remaja', 1, 0, 'C', 1);
$pdf->Cell(20, 6, 'Anak', 1, 0, 'C', 1);
$pdf->Cell(20, 6, 'Balita', 1, 0, 'C', 1);
$pdf->Cell(20, 6, 'Lansia', 1, 1, 'C', 1);
$pdf->SetFont('helvetica', '', 6.5);

$no = 1;
foreach($data_gereja as $g){
    if($pdf->GetY() > 195){
        $pdf->AddPage();
        $pdf->SetFont('helvetica', 'B', 6.5);
        $pdf->SetFillColor(230, 230, 230);
        $pdf->Cell(7, 7, 'No', 1, 0, 'C', 1);
        $pdf->Cell(40, 6, 'Nama Gereja', 1, 0, 'C', 1);
        $pdf->Cell(50, 6, 'Gembala', 1, 0, 'C', 1);
        $pdf->Cell(20, 6, 'Jemaat', 1, 0, 'C', 1);
        $pdf->Cell(20, 6, 'Pelayan', 1, 0, 'C', 1);
        $pdf->Cell(20, 6, 'Pejabat', 1, 0, 'C', 1);
        $pdf->Cell(20, 6, 'WBI', 1, 0, 'C', 1);
        $pdf->Cell(20, 6, 'Pemuda', 1, 0, 'C', 1);
        $pdf->Cell(20, 6, 'Remaja', 1, 0, 'C', 1);
        $pdf->Cell(20, 6, 'Anak', 1, 0, 'C', 1);
        $pdf->Cell(20, 6, 'Balita', 1, 0, 'C', 1);
        $pdf->Cell(20, 6, 'Lansia', 1, 1, 'C', 1);
        $pdf->SetFont('helvetica', '', 6.5);
    }
    
    $pdf->Cell(7, 6, $no++, 1, 0, 'C');
    $pdf->Cell(40, 6, $g['nama_gereja'], 1, 0, 'L');
    $pdf->Cell(50, 6, $g['nama_gembala']?? '-', 1, 0, 'L');
    $pdf->Cell(20, 6, $g['jml_jemaat'], 1, 0, 'C');
    $pdf->Cell(20, 6, $g['jml_pelayan'], 1, 0, 'C');
    $pdf->Cell(20, 6, $g['jml_pejabat'], 1, 0, 'C');
    $pdf->Cell(20, 6, $g['jml_wbi'], 1, 0, 'C');
    $pdf->Cell(20, 6, $g['jml_pemuda'], 1, 0, 'C');
    $pdf->Cell(20, 6, $g['jml_remaja'], 1, 0, 'C');
    $pdf->Cell(20, 6, $g['jml_anak'], 1, 0, 'C');
    $pdf->Cell(20, 6, $g['jml_balita'], 1, 0, 'C');
    $pdf->Cell(20, 6, $g['jml_lansia'], 1, 1, 'C');
}

$pdf->Ln(10);
$pdf->SetFont('helvetica', '', 9);
$pdf->Cell(220, 5, '', 0, 0);
$pdf->Cell(70, 5, 'Keerom, '.date('d F Y'), 0, 1, 'C');
$pdf->Cell(220, 5, '', 0, 0);
$pdf->Cell(70, 5, 'Ketua DPW,', 0, 1, 'C');
$pdf->Ln(10);
$pdf->Cell(220, 5, '', 0, 0);
$pdf->SetFont('helvetica', 'B, U', 9);
$pdf->Cell(70, 5, $ketua_dpw, 0, 1, 'C');

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