<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Admin extends CI_Controller {

    public function __construct() {
        parent::__construct();
        if (!$this->session->userdata('id_user') || $this->session->userdata('id_role') != 1) {
            redirect('auth/login_admin');
        }
        $this->load->library('excel');
        $this->load->library('pdf');
    }

    // ==========================
    // DASHBOARD
    // ==========================
    public function dashboard() {
        $data['title']  = 'Dashboard Admin';
        $data['active'] = 'dashboard';

        // Hitung total
        $data['total_perorangan'] = $this->db->count_all('sdm');
        $data['total_kelompok']   = $this->db->count_all('sanggar');
        $data['total_pendaftar']  = $data['total_perorangan'] + $data['total_kelompok'];

        // 5 perorangan terbaru
        $data['pendaftaran'] = $this->db->select('s.id_sdm, s.nama_lengkap, js.nama_seni, s.created_at')
            ->from('sdm s')
            ->join('jenis_seni js', 's.id_seni = js.id_seni', 'left')
            ->order_by('s.created_at', 'DESC')
            ->limit(5)
            ->get()->result();

        // 5 kelompok terbaru
        $data['pendaftaran_kelompok'] = $this->db->select('sg.id_sanggar, sg.nama_sanggar, sg.deskripsi, sg.created_at')
            ->from('sanggar sg')
            ->order_by('sg.created_at', 'DESC')
            ->limit(5)
            ->get()->result();

        // Chart top 5 jenis seni
        $jenis_seni_top = $this->db->query("
            SELECT js.nama_seni, COUNT(s.id_sdm) as total
            FROM sdm s
            LEFT JOIN jenis_seni js ON s.id_seni = js.id_seni
            GROUP BY js.id_seni
            ORDER BY total DESC
            LIMIT 5
        ")->result();

        $labels = [];
        $values = [];
        foreach ($jenis_seni_top as $j) {
            $labels[] = $j->nama_seni ?? 'Tidak Diketahui';
            $values[] = $j->total;
        }
        $data['seni_labels'] = json_encode($labels);
        $data['seni_values'] = json_encode($values);

        $this->load->view('templates/header', $data);
        $this->load->view('templates/sidebar', $data);
        $this->load->view('admin/dashboard', $data);
        $this->load->view('templates/footer');
    }

    // ==========================
// PENDAFTAR PERORANGAN (FULL + SEARCH + FILTER + PAGINATION)
// ==========================
public function pendaftar_perorangan($page = 0) {
    $data['title']  = 'Pendaftar Perorangan';
    $data['active'] = 'perorangan';

    $limit   = 10;
    $keyword = $this->input->get('keyword');
    $status  = $this->input->get('status');
    $jk      = $this->input->get('jenis_kelamin');
    $tahun   = $this->input->get('tahun');

    $this->db->select('s.id_sdm, s.nik, s.nrk_perorangan, s.nama_lengkap, 
                       s.jenis_kelamin, 
                       IF(s.nrk_perorangan IS NOT NULL AND s.nrk_perorangan != "", "valid", "draft") as status,
                       s.created_at, ak.no_hp')
             ->from('sdm s')
             ->join('sdm_alamat_kontak ak', 's.id_sdm = ak.id_sdm', 'left');

    // Filter
    if (!empty($keyword)) {
        $this->db->group_start()
                 ->like('s.nik', $keyword)
                 ->or_like('s.nrk_perorangan', $keyword)
                 ->or_like('s.nama_lengkap', $keyword)
                 ->group_end();
    }
    if (!empty($status)) {
        $this->db->where('IF(s.nrk_perorangan IS NOT NULL AND s.nrk_perorangan != "", "valid", "draft") =', $status);
    }
    if (!empty($jk)) {
        $this->db->where('s.jenis_kelamin', $jk);
    }
    if (!empty($tahun)) {
        $this->db->where('YEAR(s.created_at)', $tahun);
    }

    $data['total_rows'] = $this->db->count_all_results('', FALSE);

    $this->db->order_by('s.created_at', 'DESC')
             ->limit($limit, $page);
    $data['pendaftaran'] = $this->db->get()->result();

    $data['page']   = $page;
    $data['limit']  = $limit;
    $data['keyword'] = $keyword;
    $data['status'] = $status;
    $data['jk']     = $jk;
    $data['tahun']  = $tahun;

    $this->load->view('templates/header', $data);
    $this->load->view('templates/sidebar', $data);
    $this->load->view('admin/pendaftar_perorangan', $data);
    $this->load->view('templates/footer');
}

// ==========================
// DETAIL PERORANGAN
// ==========================
public function detail_perorangan($id) {
    $data['title']  = 'Detail Pendaftar Perorangan';
    $data['active'] = 'perorangan';

    $data['sdm'] = $this->db->select('
        s.id_sdm, s.nik, s.nrk_perorangan, s.nama_lengkap, s.gelar_pendidikan, 
        s.nama_alias, s.jenis_identitas, s.nomor_identitas, s.tempat_lahir, 
        s.tanggal_lahir, s.jenis_kelamin, s.agama, s.kewarganegaraan, 
        s.nama_ayah, s.nama_ibu, s.jenis_sdm, s.created_at,
        ak.provinsi, ak.kabupaten, ak.kecamatan, ak.desa, ak.alamat, 
        ak.latitude, ak.longitude, ak.no_hp, ak.email, ak.facebook, 
        ak.youtube, ak.website, ak.twitter, ak.instagram,
        GROUP_CONCAT(js.nama_seni SEPARATOR ", ") as jenis_seni_list
    ')
    ->from('sdm s')
    ->join('sdm_alamat_kontak ak', 's.id_sdm = ak.id_sdm', 'left')
    ->join('sdm_seni ss', 's.id_sdm = ss.id_sdm', 'left')
    ->join('jenis_seni js', 'ss.id_seni = js.id_seni', 'left')
    ->where('s.id_sdm', $id)
    ->group_by('s.id_sdm')
    ->get()->row();

    if ($data['sdm']) {
        $data['sdm']->status = !empty($data['sdm']->nrk_perorangan) ? 'valid' : 'draft';
    }

    $data['pendidikan']  = $this->db->where('id_sdm', $id)->get('sdm_pendidikan')->result();
    $data['pekerjaan']   = $this->db->where('id_sdm', $id)->get('sdm_pekerjaan')->result();
    $data['pelatihan']   = $this->db->where('id_sdm', $id)->get('sdm_pelatihan')->result();
    $data['penghargaan'] = $this->db->where('id_sdm', $id)->get('sdm_penghargaan')->result();
    $data['aktivitas']   = $this->db->where('id_sdm', $id)->get('sdm_aktivitas')->result();

    $this->load->view('templates/header', $data);
    $this->load->view('templates/sidebar', $data);
    $this->load->view('admin/detail_perorangan', $data);
    $this->load->view('templates/footer');
}
// ==========================
// EXPORT EXCEL PERORANGAN
// ==========================
public function export_perorangan_excel_all() {
    $query = $this->db->select('s.nik, s.nrk_perorangan, s.nama_lengkap, s.jenis_kelamin, 
                                ak.no_hp, 
                                IF(s.nrk_perorangan IS NOT NULL AND s.nrk_perorangan != "", "valid", "draft") as status,
                                s.created_at')
                      ->from('sdm s')
                      ->join('sdm_alamat_kontak ak', 's.id_sdm = ak.id_sdm', 'left')
                      ->order_by('s.created_at', 'ASC')
                      ->get()->result_array();

    $this->_export_excel($query, "Pendaftar_Perorangan_All");
}

public function export_perorangan_excel_filter() {
    $keyword = $this->input->get('keyword');
    $status  = $this->input->get('status');
    $jk      = $this->input->get('jenis_kelamin');
    $tahun   = $this->input->get('tahun');

    $this->db->select('
        s.nik, 
        s.nrk_perorangan, 
        s.nama_lengkap, 
        s.jenis_kelamin, 
        ak.no_hp,
        GROUP_CONCAT(js.nama_seni SEPARATOR ", ") as jenis_seni_list, 
        IF(s.nrk_perorangan IS NOT NULL AND s.nrk_perorangan != "", "valid", "draft") as status,
        s.created_at'
    )
    ->from('sdm s')
    ->join('sdm_alamat_kontak ak', 's.id_sdm = ak.id_sdm', 'left')
    // Tambahkan JOIN ke tabel sdm_seni dan jenis_seni
    ->join('sdm_seni ss', 's.id_sdm = ss.id_sdm', 'left')
    ->join('jenis_seni js', 'ss.id_seni = js.id_seni', 'left');

    if (!empty($keyword)) {
        $this->db->group_start()
                 ->like('s.nik', $keyword)
                 ->or_like('s.nrk_perorangan', $keyword)
                 ->or_like('s.nama_lengkap', $keyword)
                 ->or_like('js.nama_seni', $keyword)
                 ->group_end();
    }
    if (!empty($status)) {
        $this->db->where('IF(s.nrk_perorangan IS NOT NULL AND s.nrk_perorangan != "", "valid", "draft") =', $status);
    }
    if (!empty($jk)) {
        $this->db->where('s.jenis_kelamin', $jk);
    }
    if (!empty($tahun)) {
        $this->db->where('YEAR(s.created_at)', $tahun);
    }

    // Tambahkan GROUP BY untuk GROUP_CONCAT
    $this->db->group_by('s.id_sdm');
    $query = $this->db->order_by('s.created_at', 'ASC')->get()->result_array();

    $this->_export_excel($query, "Pendaftar_Perorangan_Filter");
}
private function _export_excel($data, $filename) {
    $this->load->library('excel');
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', 'NIK')
                ->setCellValue('B1', 'NRK')
                ->setCellValue('C1', 'Nama')
                ->setCellValue('D1', 'Jenis Kelamin')
                ->setCellValue('E1', 'No HP')
                ->setCellValue('F1', 'Status')
                ->setCellValue('G1', 'Tanggal Daftar');

    $row = 2;
    foreach ($data as $q) {
        $objPHPExcel->getActiveSheet()
                    ->setCellValue("A$row", $q['nik'])
                    ->setCellValue("B$row", $q['nrk_perorangan'])
                    ->setCellValue("C$row", $q['nama_lengkap'])
                    ->setCellValue("D$row", $q['jenis_kelamin'])
                    ->setCellValue("E$row", $q['no_hp'])
                    ->setCellValue("F$row", $q['status'])
                    ->setCellValue("G$row", date('d-m-Y', strtotime($q['created_at'])));
        $row++;
    }

    header('Content-Type: application/vnd.ms-excel');
    header("Content-Disposition: attachment;filename=\"$filename.xls\"");
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
}
public function export_perorangan_pdf_all() {
    $data['perorangan'] = $this->db->select('s.nik, s.nrk_perorangan, s.nama_lengkap, s.jenis_kelamin,
                                             ak.no_hp,
                                             IF(s.nrk_perorangan IS NOT NULL AND s.nrk_perorangan != "", "valid", "draft") as status,
                                             s.created_at')
                                   ->from('sdm s')
                                   ->join('sdm_alamat_kontak ak', 's.id_sdm = ak.id_sdm', 'left')
                                   ->order_by('s.created_at', 'ASC')
                                   ->get()->result();

    $html = $this->load->view('admin/export_perorangan_pdf', $data, TRUE);
    $this->pdf->createPDF($html, 'Pendaftar_Perorangan_All', true, 'A4', 'landscape');
}
public function export_perorangan_pdf_filter() {
    $keyword = $this->input->get('keyword');
    $status  = $this->input->get('status');
    $jk      = $this->input->get('jenis_kelamin');
    $tahun   = $this->input->get('tahun');

    $this->db->select('s.nik, s.nrk_perorangan, s.nama_lengkap, s.jenis_kelamin,
                       ak.no_hp,
                       IF(s.nrk_perorangan IS NOT NULL AND s.nrk_perorangan != "", "valid", "draft") as status,
                       s.created_at')
             ->from('sdm s')
             ->join('sdm_alamat_kontak ak', 's.id_sdm = ak.id_sdm', 'left');

    if (!empty($keyword)) {
        $this->db->group_start()
                 ->like('s.nik', $keyword)
                 ->or_like('s.nrk_perorangan', $keyword)
                 ->or_like('s.nama_lengkap', $keyword)
                 ->group_end();
    }
    if (!empty($status)) {
        $this->db->where('IF(s.nrk_perorangan IS NOT NULL AND s.nrk_perorangan != "", "valid", "draft") =', $status);
    }
    if (!empty($jk)) {
        $this->db->where('s.jenis_kelamin', $jk);
    }
    if (!empty($tahun)) {
        $this->db->where('YEAR(s.created_at)', $tahun);
    }

    $data['perorangan'] = $this->db->order_by('s.created_at', 'ASC')->get()->result();

    $html = $this->load->view('admin/export_perorangan_pdf', $data, TRUE);
    $this->pdf->createPDF($html, 'Pendaftar_Perorangan_Filter', true, 'A4', 'landscape');
}

// ==========================
// EDIT PERORANGAN
// ==========================
public function edit_perorangan($id) {
    $data['title'] = 'Edit Pendaftar Perorangan';
    $data['active'] = 'perorangan';
    $data['sdm'] = $this->db->get_where('sdm', ['id_sdm' => $id])->row();
    $data['alamat'] = $this->db->get_where('sdm_alamat_kontak', ['id_sdm' => $id])->row();
    $data['jenis_seni'] = $this->db->get('jenis_seni')->result_array();

    $this->load->view('templates/header', $data);
    $this->load->view('templates/sidebar', $data);
    $this->load->view('admin/form_perorangan', $data);
    $this->load->view('templates/footer');
}


// ==========================
// PENDAFTAR KELOMPOK (LIST + FILTER + PAGINATION)
// ==========================
public function pendaftar_kelompok($page = 0) {
    $data['title']  = 'Pendaftar Kelompok';
    $data['active'] = 'kelompok';

    $limit   = 10;
    $keyword = $this->input->get('keyword');
    $status  = $this->input->get('status');
    $tahun   = $this->input->get('tahun');

    // Start with the main table
    $this->db->select('
        sg.id_sanggar, sg.nrk_sanggar, sg.nama_sanggar, 
        sg.deskripsi, sak.alamat, sak.no_hp, sak.email,
        GROUP_CONCAT(js.nama_seni SEPARATOR ", ") as jenis_seni_list,
        IF(sg.nrk_sanggar IS NOT NULL AND sg.nrk_sanggar != "", "valid", "draft") as status'
    )
    ->from('sanggar sg')
    ->join('sanggar_alamat_kontak sak', 'sg.id_sanggar = sak.id_sanggar', 'left')
    // Join through the intermediate table sanggar_seni
    ->join('sanggar_seni ssg', 'sg.id_sanggar = ssg.id_sanggar', 'left')
    // Then join with jenis_seni to get the name
    ->join('jenis_seni js', 'ssg.id_seni = js.id_seni', 'left');

    // Add a GROUP BY clause to handle multiple seni for one sanggar
    $this->db->group_by('sg.id_sanggar');

    // ... (rest of your filtering code remains the same) ...
    // filter keyword
    if (!empty($keyword)) {
        $this->db->group_start()
                 ->like('sg.nrk_sanggar', $keyword)
                 ->or_like('sg.nama_sanggar', $keyword)
                 ->or_like('sg.deskripsi', $keyword)
                 ->or_like('sak.alamat', $keyword)
                 ->or_like('sak.no_hp', $keyword)
                 ->or_like('js.nama_seni', $keyword) // Add this line to search by art type
                 ->group_end();
    }

    // filter status
    if (!empty($status)) {
        $this->db->having('status', $status);
    }

    // filter tahun
    if (!empty($tahun)) {
        $this->db->where('YEAR(sg.created_at)', $tahun);
    }

    // total data
    $data['total_rows'] = $this->db->count_all_results('', FALSE);

    // data hasil filter + paging
    $this->db->order_by('sg.created_at', 'DESC')
             ->limit($limit, $page);
    $data['sanggar_list'] = $this->db->get()->result();

    // kirim ke view
    $data['page']    = $page;
    $data['limit']   = $limit;
    $data['keyword'] = $keyword;
    $data['status']  = $status;
    $data['tahun']   = $tahun;

    $this->load->view('templates/header', $data);
    $this->load->view('templates/sidebar', $data);
    $this->load->view('admin/pendaftar_kelompok', $data);
    $this->load->view('templates/footer');
}

// ==========================
// DETAIL KELOMPOK
// ==========================
public function detail_kelompok($id) {
    // Ambil data sanggar
    $data['kelompok'] = $this->db->select('
        sg.*, 
        GROUP_CONCAT(js.nama_seni SEPARATOR ", ") as jenis_seni_list,
        sak.alamat, sak.provinsi, sak.kabupaten, 
        sak.kecamatan, sak.desa, sak.no_hp, sak.email, sak.website,
        sak.facebook, sak.instagram, sak.youtube, sak.twitter
    ')
    ->from('sanggar sg')
    ->join('sanggar_alamat_kontak sak', 'sg.id_sanggar = sak.id_sanggar', 'left')
    ->join('sanggar_seni ssg', 'sg.id_sanggar = ssg.id_sanggar', 'left')
    ->join('jenis_seni js', 'ssg.id_seni = js.id_seni', 'left')
    ->where('sg.id_sanggar', $id)
    ->group_by('sg.id_sanggar')
    ->get()->row();

    if ($data['kelompok']) {
        $data['kelompok']->status = !empty($data['kelompok']->nrk_sanggar) ? 'valid' : 'draft';
        
        // --- TAMBAHAN KODE INI ---
        // Ambil data pengurus dengan JOIN ke tabel sdm dan master_peran
        $data['pengurus'] = $this->db->select('ps.*, sdm.nama_lengkap, sdm.nik, mp.nama_peran, sdm.gelar_pendidikan, sdm.jenis_kelamin, sdm.tempat_lahir, sdm.tanggal_lahir, sdm.kewarganegaraan, sdm.agama')
                                     ->from('pengurus_sanggar ps')
                                     ->join('sdm', 'ps.id_sdm = sdm.id_sdm', 'left')
                                     ->join('master_peran mp', 'ps.id_peran = mp.id_peran', 'left')
                                     ->where('ps.id_sanggar', $id)
                                     ->get()->result();

        // Ambil data anggota dengan JOIN ke tabel master_level
        $data['anggota'] = $this->db->select('ak.*, ml.nama_level')
                                    ->from('anggota_kelompok ak')
                                    ->join('master_level ml', 'ak.id_level = ml.id_level', 'left')
                                    ->where('ak.id_sanggar', $id)
                                    ->get()->result();
        // --- AKHIR TAMBAHAN KODE ---
    } else {
        $data['pengurus'] = [];
        $data['anggota'] = [];
    }

    $this->load->view('templates/header', $data);
    $this->load->view('templates/sidebar', $data);
    $this->load->view('admin/detail_kelompok', $data);
    $this->load->view('templates/footer');
}


// ==========================
// TAMBAH KELOMPOK
// ==========================
public function tambah_kelompok() {
    $data['title'] = 'Tambah Pendaftar Kelompok';
    $data['active'] = 'kelompok';
    
    // Ambil data jenis seni
    $data['jenis_seni'] = $this->db->get('jenis_seni')->result_array();
    
    // --- TAMBAHAN KODE INI ---
    // Ambil data dari tabel master_peran dan master_level
    $data['master_peran'] = $this->db->get('master_peran')->result_array();
    $data['master_level'] = $this->db->get('master_level')->result_array();
    // --- AKHIR TAMBAHAN KODE ---
    
    $data['kelompok'] = null;

    $this->load->view('templates/header', $data);
    $this->load->view('templates/sidebar', $data);
    $this->load->view('admin/form_kelompok', $data);
    $this->load->view('templates/footer');
}

public function simpan_kelompok() {
    // Memulai transaksi database untuk memastikan semua operasi berhasil atau gagal bersamaan
    $this->db->trans_begin();
    try {
        // 1. Simpan data inti sanggar
        $data_sanggar = [
            // NRK Sanggar dibiarkan NULL karena statusnya masih draft
            'nama_sanggar'       => $this->input->post('nama_sanggar'),
            'jenis_lembaga'      => $this->input->post('jenis_lembaga'),
            'status_hukum'       => $this->input->post('status_hukum'),
            'nomor_identitas'    => $this->input->post('nomor_identitas'),
            'tanggal_berdiri'    => $this->input->post('tanggal_berdiri'),
            'tujuan'             => $this->input->post('tujuan'),
            'deskripsi'          => $this->input->post('deskripsi'),
            'status_kepemilikan' => $this->input->post('status_kepemilikan'),
            'nama_pemilik'       => $this->input->post('nama_pemilik'),
            'created_at'         => date('Y-m-d H:i:s')
        ];
        $this->db->insert('sanggar', $data_sanggar);
        $id_sanggar = $this->db->insert_id();

        // 2. Simpan jenis seni ke tabel perantara 'sanggar_seni'
        $jenis_seni_ids = $this->input->post('jenis_seni');
        if (!empty($jenis_seni_ids) && is_array($jenis_seni_ids)) {
            $sanggar_seni_batch = [];
            foreach ($jenis_seni_ids as $id_seni) {
                $sanggar_seni_batch[] = [
                    'id_sanggar' => $id_sanggar,
                    'id_seni'    => $id_seni
                ];
            }
            $this->db->insert_batch('sanggar_seni', $sanggar_seni_batch);
        }

        // 3. Simpan alamat & kontak
        $data_kontak = [
            'id_sanggar' => $id_sanggar,
            'provinsi'   => $this->input->post('provinsi'),
            'kabupaten'  => $this->input->post('kabupaten'),
            'kecamatan'  => $this->input->post('kecamatan'),
            'desa'       => $this->input->post('desa'),
            'alamat'     => $this->input->post('alamat'),
            'latitude'   => $this->input->post('latitude'),
            'longitude'  => $this->input->post('longitude'),
            'no_hp'      => $this->input->post('no_hp'),
            'email'      => $this->input->post('alamat_email'),
            'website'    => $this->input->post('alamat_website'),
            'facebook'   => $this->input->post('akun_facebook'),
            'twitter'    => $this->input->post('akun_twitter'),
            'instagram'  => $this->input->post('akun_instagram'),
            'youtube'    => $this->input->post('akun_youtube'),
        ];
        $this->db->insert('sanggar_alamat_kontak', $data_kontak);
        
        // 4. Simpan pengurus (masuk ke tabel sdm dan pengurus_sanggar)
        $pengurus_niks = $this->input->post('pengurus_nik');
        if (!empty($pengurus_niks) && is_array($pengurus_niks)) {
            foreach ($pengurus_niks as $i => $nik) {
                if (!empty($nik)) {
                    // Cek apakah SDM sudah ada berdasarkan NIK
                    $sdm_exist = $this->db->get_where('sdm', ['nik' => $nik])->row();
                    
                    $id_sdm = 0;
                    if (!$sdm_exist) {
                        // Jika belum ada, buat entri SDM baru
                        $data_sdm = [
                            'nik'              => $nik,
                            'nama_lengkap'     => $this->input->post('pengurus_nama')[$i],
                            'gelar_pendidikan' => $this->input->post('pengurus_gelar_pendidikan')[$i] ?? null,
                            'jenis_kelamin'    => $this->input->post('pengurus_jk')[$i] ?? null,
                            'tempat_lahir'     => $this->input->post('pengurus_tempat_lahir')[$i] ?? null,
                            'tanggal_lahir'    => $this->input->post('pengurus_tanggal_lahir')[$i] ?? null,
                            'kewarganegaraan'  => $this->input->post('pengurus_kewarganegaraan')[$i] ?? null,
                            'agama'            => $this->input->post('pengurus_agama')[$i] ?? null,
                            'jenis_sdm'        => 'pengurus', // Tetapkan sebagai pengurus
                            'created_at'       => date('Y-m-d H:i:s')
                            // NRK perorangan tidak diisi, statusnya draft
                        ];
                        $this->db->insert('sdm', $data_sdm);
                        $id_sdm = $this->db->insert_id();
                    } else {
                        $id_sdm = $sdm_exist->id_sdm;
                        // Perbarui jenis sdm jika sebelumnya bukan pengurus
                        $this->db->where('id_sdm', $id_sdm)->update('sdm', ['jenis_sdm' => 'pengurus']);
                    }
                    
                    // Simpan ke tabel pengurus_sanggar
                    $data_pengurus_sanggar = [
                        'id_sanggar' => $id_sanggar,
                        'id_sdm'     => $id_sdm,
                        'id_peran'   => $this->db->get_where('master_peran', ['nama_peran' => $this->input->post('pengurus_peran')[$i]])->row()->id_peran ?? null,
                        'status'     => 'aktif'
                    ];
                    $this->db->insert('pengurus_sanggar', $data_pengurus_sanggar);
                }
            }
        }

        // 5. Simpan anggota
        $anggota_niks = $this->input->post('anggota_nik');
        if (!empty($anggota_niks) && is_array($anggota_niks)) {
            $anggota_batch = [];
            foreach ($anggota_niks as $i => $nik) {
                if (!empty($nik)) {
                    $anggota_batch[] = [
                        'id_sanggar'      => $id_sanggar,
                        'nik'             => $nik,
                        'nama_anggota'    => $this->input->post('anggota_nama')[$i],
                        'jenis_kelamin'   => $this->input->post('anggota_jk')[$i] ?? null,
                        'tempat_lahir'    => $this->input->post('anggota_tempat_lahir')[$i] ?? null,
                        'tanggal_lahir'   => $this->input->post('anggota_tanggal_lahir')[$i] ?? null,
                        'kewarganegaraan' => $this->input->post('anggota_kewarganegaraan')[$i] ?? null,
                        'agama'           => $this->input->post('anggota_agama')[$i] ?? null,
                        'nomor_hp'        => $this->input->post('anggota_hp')[$i] ?? null,
                        'id_level'        => $this->db->get_where('master_level', ['nama_level' => $this->input->post('anggota_level')[$i]])->row()->id_level ?? null,
                        'status'          => 'aktif',
                        'tanggal_daftar'  => date('Y-m-d')
                    ];
                }
            }
            if (!empty($anggota_batch)) {
                $this->db->insert_batch('anggota_kelompok', $anggota_batch);
            }
        }
        
        $this->db->trans_commit();
        $this->session->set_flashdata('success', 'Data kelompok berhasil ditambahkan');
        redirect('admin/pendaftar_kelompok');

    } catch (Exception $e) {
        $this->db->trans_rollback();
        $this->session->set_flashdata('error', 'Gagal menambahkan data kelompok: ' . $e->getMessage());
        redirect('admin/tambah_kelompok');
    }
}
// ==========================
// EDIT KELOMPOK
// ==========================
public function edit_kelompok($id) {
    $data['title'] = 'Edit Pendaftar Kelompok';
    $data['active'] = 'kelompok';
    $data['jenis_seni'] = $this->db->get('jenis_seni')->result_array();
    $data['kelompok'] = $this->db->get_where('sanggar', ['id_sanggar' => $id])->row();

    $this->load->view('templates/header', $data);
    $this->load->view('templates/sidebar', $data);
    $this->load->view('admin/form_kelompok', $data); // pakai form yg sama
    $this->load->view('templates/footer');
}

public function update_kelompok($id) {
    // 1. update data inti sanggar
    $data_sanggar = [
        'nrk_sanggar'     => $this->input->post('nrk_sanggar'),
        'nama_sanggar'    => $this->input->post('nama_sanggar'),
        'jenis_lembaga'   => $this->input->post('jenis_lembaga'),
        'id_seni'         => $this->input->post('jenis_seni'),
        'status_hukum'    => $this->input->post('status_hukum'),
        'nomor_identitas' => $this->input->post('nomor_identitas'),
        'tanggal_berdiri' => $this->input->post('tanggal_berdiri'),
        'tujuan'          => $this->input->post('tujuan'),
        'deskripsi'       => $this->input->post('deskripsi'),
        'status_kepemilikan' => $this->input->post('status_kepemilikan'),
        'nama_pemilik'    => $this->input->post('nama_pemilik'),
    ];
    $this->db->where('id_sanggar', $id)->update('sanggar', $data_sanggar);

    // 2. update alamat & kontak
    $data_kontak = [
        'provinsi'   => $this->input->post('provinsi'),
        'kabupaten'  => $this->input->post('kabupaten'),
        'kecamatan'  => $this->input->post('kecamatan'),
        'desa'       => $this->input->post('desa'),
        'alamat'     => $this->input->post('alamat'),
        'latitude'   => $this->input->post('latitude'),
        'longitude'  => $this->input->post('longitude'),
        'no_hp'      => $this->input->post('no_hp'),
        'email'      => $this->input->post('alamat_email'),
        'website'    => $this->input->post('alamat_website'),
        'facebook'   => $this->input->post('akun_facebook'),
        'twitter'    => $this->input->post('akun_twitter'),
        'instagram'  => $this->input->post('akun_instagram'),
        'youtube'    => $this->input->post('akun_youtube'),
    ];
    $cekKontak = $this->db->get_where('sanggar_alamat_kontak', ['id_sanggar' => $id])->row();
    if ($cekKontak) {
        $this->db->where('id_sanggar', $id)->update('sanggar_alamat_kontak', $data_kontak);
    } else {
        $data_kontak['id_sanggar'] = $id;
        $this->db->insert('sanggar_alamat_kontak', $data_kontak);
    }

    // 3. update media legalitas (contoh dokumen, file lain bisa sama)
    if (!empty($_FILES['dokumen_legalitas']['name'])) {
        $file_name = $this->_upload_file('dokumen_legalitas');
        $cekMedia = $this->db->get_where('sanggar_media_legalitas', ['id_sanggar' => $id])->row();
        if ($cekMedia) {
            $this->db->where('id_sanggar', $id)->update('sanggar_media_legalitas', [
                'dokumen_legalitas' => $file_name
            ]);
        } else {
            $this->db->insert('sanggar_media_legalitas', [
                'id_sanggar' => $id,
                'dokumen_legalitas' => $file_name
            ]);
        }
    }

    // 4. update anggota (reset dulu → insert ulang)
    $this->db->where('id_sanggar', $id)->delete('anggota_kelompok');
    $anggota_nik = $this->input->post('anggota_nik');
    $anggota_nama = $this->input->post('anggota_nama');
    if ($anggota_nik) {
        foreach ($anggota_nik as $i => $nik) {
            $this->db->insert('anggota_kelompok', [
                'id_sanggar'    => $id,
                'nik'           => $nik,
                'nama_anggota'  => $anggota_nama[$i],
                'jenis_kelamin' => $this->input->post('anggota_jk')[$i],
                'agama'         => $this->input->post('anggota_agama')[$i],
                'nomor_hp'      => $this->input->post('anggota_hp')[$i],
                'id_level'      => $this->db->get_where('master_level', [
                                        'nama_level' => $this->input->post('anggota_level')[$i]
                                   ])->row()->id_level
            ]);
        }
    }

    // 5. update pengurus (mirip anggota)
    $this->db->where('id_sanggar', $id)->delete('pengurus_sanggar');
    $pengurus_nik = $this->input->post('pengurus_nik');
    $pengurus_nama = $this->input->post('pengurus_nama');
    if ($pengurus_nik) {
        foreach ($pengurus_nik as $i => $nik) {
            // simpan dulu ke SDM
            $data_sdm = [
                'nik'          => $nik,
                'nama_lengkap' => $pengurus_nama[$i],
                'jenis_kelamin'=> $this->input->post('pengurus_jk')[$i],
                'gelar_pendidikan'=> $this->input->post('pengurus_gelar_pendidikan')[$i],
                'tempat_lahir' => $this->input->post('pengurus_tempat_lahir')[$i],
                'tanggal_lahir'=> $this->input->post('pengurus_tanggal_lahir')[$i],
                'kewarganegaraan'=> $this->input->post('pengurus_kewarganegaraan')[$i],
                'agama'        => $this->input->post('pengurus_agama')[$i],
                'jenis_sdm'    => 'pengurus',
                'status'       => 'valid',
                'id_seni'      => $data_sanggar['id_seni']
            ];
            $this->db->insert('sdm', $data_sdm);
            $id_sdm = $this->db->insert_id();

            // simpan ke pengurus_sanggar
            $id_peran = $this->db->get_where('master_peran', [
                            'nama_peran' => $this->input->post('pengurus_peran')[$i]
                        ])->row()->id_peran;

            $this->db->insert('pengurus_sanggar', [
                'id_sanggar' => $id,
                'id_sdm'     => $id_sdm,
                'id_peran'   => $id_peran,
                'status'     => 'aktif'
            ]);
        }
    }

    $this->session->set_flashdata('success', 'Data kelompok berhasil diupdate');
    redirect('admin/pendaftar_kelompok');
}


// ==========================
// HAPUS KELOMPOK
// ==========================
public function hapus_kelompok($id) {
    $this->db->delete('sanggar', ['id_sanggar' => $id]);
    $this->session->set_flashdata('success', 'Data kelompok berhasil dihapus');
    redirect('admin/pendaftar_kelompok');
}



  // ==========================
// TAMBAH PERORANGAN
// ==========================
public function tambah_perorangan() {
    $data['title'] = 'Tambah Pendaftar Perorangan';
    $data['active'] = 'perorangan';
    $data['jenis_seni'] = $this->db->get('jenis_seni')->result_array();
    $data['sdm'] = null;
    $data['alamat'] = null;

    $this->load->view('templates/header', $data);
    $this->load->view('templates/sidebar', $data);
    $this->load->view('admin/form_perorangan', $data);
    $this->load->view('templates/footer');
}


// ==========================
// SIMPAN TAMBAH
// ==========================
public function simpan_perorangan() {
    $nrk = $this->input->post('nrk_perorangan');
    $status = !empty($nrk) ? 'valid' : 'draft';

    $data_sdm = [
        'nik'             => $this->input->post('nik'),
        'nrk_perorangan'  => $nrk,
        'nama_lengkap'    => $this->input->post('nama_lengkap'),
        'gelar_pendidikan'=> $this->input->post('gelar_pendidikan'),
        'nama_alias'      => $this->input->post('nama_alias'),
        'jenis_identitas' => $this->input->post('jenis_identitas'),
        'nomor_identitas' => $this->input->post('nik'),
        'tempat_lahir'    => $this->input->post('tempat_lahir'),
        'tanggal_lahir'   => $this->input->post('tanggal_lahir'),
        'jenis_kelamin'   => $this->input->post('jenis_kelamin'),
        'agama'           => $this->input->post('agama'),
        'kewarganegaraan' => $this->input->post('kewarganegaraan'),
        'nama_ayah'       => $this->input->post('nama_ayah'),
        'nama_ibu'        => $this->input->post('nama_ibu'),
        'id_seni'         => $this->input->post('jenis_seni'),
        'jenis_sdm'       => 'perorangan',
        'status'          => $status,
        'created_at'      => date('Y-m-d H:i:s')
    ];
    $this->db->insert('sdm', $data_sdm);
    $id_sdm = $this->db->insert_id();

    // alamat & kontak (tetep sama kayak punya lu)
    $data_alamat = [
        'id_sdm'    => $id_sdm,
        'provinsi'  => $this->input->post('provinsi'),
        'kabupaten' => $this->input->post('kabupaten'),
        'kecamatan' => $this->input->post('kecamatan'),
        'desa'      => $this->input->post('desa'),
        'alamat'    => $this->input->post('alamat'),
        'latitude'  => $this->input->post('latitude'),
        'longitude' => $this->input->post('longitude'),
        'no_hp'     => $this->input->post('no_hp'),
        'email'     => $this->input->post('email'),
        'facebook'  => $this->input->post('facebook'),
        'youtube'   => $this->input->post('youtube'),
        'website'   => $this->input->post('website'),
        'twitter'   => $this->input->post('twitter'),
        'instagram' => $this->input->post('instagram')
    ];
    $this->db->insert('sdm_alamat_kontak', $data_alamat);

    $this->session->set_flashdata('success', 'Data perorangan berhasil ditambahkan');
    redirect('admin/pendaftar_perorangan');
}


// ==========================
// SIMPAN EDIT
// ==========================
public function update_perorangan($id) {
    $nrk = $this->input->post('nrk_perorangan');
    $status = !empty($nrk) ? 'valid' : 'draft';
    
    $data_sdm = [
        'nik'             => $this->input->post('nik'),
        'nrk_perorangan'  => $nrk,
        'nama_lengkap'    => $this->input->post('nama_lengkap'),
        'gelar_pendidikan'=> $this->input->post('gelar_pendidikan'),
        'nama_alias'      => $this->input->post('nama_alias'),
        'jenis_identitas' => $this->input->post('jenis_identitas'),
        'nomor_identitas' => $this->input->post('nik'),
        'tempat_lahir'    => $this->input->post('tempat_lahir'),
        'tanggal_lahir'   => $this->input->post('tanggal_lahir'),
        'jenis_kelamin'   => $this->input->post('jenis_kelamin'),
        'agama'           => $this->input->post('agama'),
        'kewarganegaraan' => $this->input->post('kewarganegaraan'),
        'nama_ayah'       => $this->input->post('nama_ayah'),
        'nama_ibu'        => $this->input->post('nama_ibu'),
        'id_seni'         => $this->input->post('jenis_seni'),
        'status'          => $status
    ];
    $this->db->where('id_sdm', $id)->update('sdm', $data_sdm);

    // update alamat (tetep sama kayak punya lu)
    $data_alamat = [
        'provinsi'  => $this->input->post('provinsi'),
        'kabupaten' => $this->input->post('kabupaten'),
        'kecamatan' => $this->input->post('kecamatan'),
        'desa'      => $this->input->post('desa'),
        'alamat'    => $this->input->post('alamat'),
        'latitude'  => $this->input->post('latitude'),
        'longitude' => $this->input->post('longitude'),
        'no_hp'     => $this->input->post('no_hp'),
        'email'     => $this->input->post('email'),
        'facebook'  => $this->input->post('facebook'),
        'youtube'   => $this->input->post('youtube'),
        'website'   => $this->input->post('website'),
        'twitter'   => $this->input->post('twitter'),
        'instagram' => $this->input->post('instagram')
    ];
    $this->db->where('id_sdm', $id)->update('sdm_alamat_kontak', $data_alamat);

    $this->session->set_flashdata('success', 'Data perorangan berhasil diupdate');
    redirect('admin/pendaftar_perorangan');
}



// ==========================
// HAPUS PERORANGAN (WITH RELATION)
// ==========================
public function hapus_perorangan($id) {
    $this->db->delete('sdm_alamat_kontak', ['id_sdm' => $id]);
    $this->db->delete('sdm', ['id_sdm' => $id]);
    $this->session->set_flashdata('success', 'Data berhasil dihapus');
    redirect('admin/pendaftar_perorangan');
}


    // ==========================
    // JENIS SENI
    // ==========================
    public function jenis_seni() {
        $data['title']  = 'Jenis Seni';
        $data['active'] = 'jenis_seni';
        $data['jenis_seni'] = $this->db->get('jenis_seni')->result();

        $this->load->view('templates/header', $data);
        $this->load->view('templates/sidebar', $data);
        $this->load->view('admin/jenis_seni', $data);
        $this->load->view('templates/footer');
    }

    public function tambah_jenis_seni() {
        $nama_seni = $this->input->post('nama_seni', TRUE);
        if (!empty($nama_seni)) {
            $this->db->insert('jenis_seni', ['nama_seni' => $nama_seni]);
        }
        redirect('admin/jenis_seni');
    }

    public function edit_jenis_seni($id) {
        $nama_seni = $this->input->post('nama_seni', TRUE);
        if (!empty($nama_seni)) {
            $this->db->where('id_seni', $id);
            $this->db->update('jenis_seni', ['nama_seni' => $nama_seni]);
        }
        redirect('admin/jenis_seni');
    }

    public function hapus_jenis_seni($id) {
        $this->db->where('id_seni', $id);
        $this->db->delete('jenis_seni');
        redirect('admin/jenis_seni');
    }

    // ==========================
    // EXPORT PERORANGAN
    // ==========================
    // public function export_perorangan_excel() {
    //     $data_perorangan = $this->db->select('s.nama_lengkap, s.created_at')
    //                                 ->from('sdm s')
    //                                 ->order_by('s.created_at', 'ASC')
    //                                 ->get()->result();
    //     // isi Excel ...
    // }

    // public function export_perorangan_pdf() {
    //     $data['perorangan'] = $this->db->select('s.nama_lengkap, s.created_at, s.alamat, s.nik')
    //                                    ->from('sdm s')
    //                                    ->get()->result();
    //     $html = $this->load->view('admin/export_perorangan_pdf', $data, TRUE);
    //     $this->pdf->createPDF($html, 'Pendaftar_Perorangan', true, 'A4', 'portrait');
    // }


    // ==========================
    // EXPORT DATA SENI
    // ==========================
    public function export_seni_excel() {
        $jenis_seni = $this->db->get('jenis_seni')->result();
        // isi Excel ...
    }

    public function export_seni_pdf() {
        $data['jenis_seni'] = $this->db->get('jenis_seni')->result();
        $html = $this->load->view('admin/export_seni_pdf', $data, TRUE);
        $this->pdf->createPDF($html, 'Jenis_Seni', true, 'A4', 'portrait');
    }
// ==========================
// EXPORT KELOMPOK - EXCEL (FILTER)
// ==========================
public function export_kelompok_excel_filter() {
    $keyword = $this->input->get('keyword');
    $status  = $this->input->get('status');
    $tahun   = $this->input->get('tahun');

    $this->db->select('
        sg.id_sanggar, sg.nrk_sanggar, sg.nama_sanggar, sg.jenis_lembaga, 
        sg.status_hukum, sg.created_at, ak.no_hp, ak.provinsi, ak.kabupaten, ak.kecamatan, ak.desa, ak.alamat,
        GROUP_CONCAT(js.nama_seni SEPARATOR ", ") as jenis_seni_list,
        IF(sg.nrk_sanggar IS NOT NULL AND sg.nrk_sanggar != "", "valid", "draft") as status'
    )
    ->from('sanggar sg')
    ->join('sanggar_alamat_kontak ak', 'sg.id_sanggar = ak.id_sanggar', 'left')
    // Tambahkan JOIN ke tabel sanggar_seni dan jenis_seni
    ->join('sanggar_seni ssg', 'sg.id_sanggar = ssg.id_sanggar', 'left')
    ->join('jenis_seni js', 'ssg.id_seni = js.id_seni', 'left');

    if (!empty($keyword)) {
        $this->db->group_start()
                 ->like('sg.nrk_sanggar', $keyword)
                 ->or_like('sg.nama_sanggar', $keyword)
                 ->or_like('sg.jenis_lembaga', $keyword)
                 ->or_like('js.nama_seni', $keyword)
                 ->or_like('ak.provinsi', $keyword)
                 ->or_like('ak.kabupaten', $keyword)
                 ->group_end();
    }
    if (!empty($status)) {
        $this->db->where('IF(sg.nrk_sanggar IS NOT NULL AND sg.nrk_sanggar != "", "valid", "draft") =', $status);
    }
    if (!empty($tahun)) {
        $this->db->where('YEAR(sg.created_at)', $tahun);
    }
    
    // Tambahkan GROUP BY untuk GROUP_CONCAT
    $this->db->group_by('sg.id_sanggar');

    $result = $this->db->order_by('sg.created_at', 'ASC')->get()->result_array();

    $this->load->library('excel');
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', 'NRK')
                ->setCellValue('B1', 'Nama Sanggar')
                ->setCellValue('C1', 'Jenis Seni')
                ->setCellValue('D1', 'Jenis Lembaga')
                ->setCellValue('E1', 'Status Hukum')
                ->setCellValue('F1', 'No HP')
                ->setCellValue('G1', 'Provinsi')
                ->setCellValue('H1', 'Kabupaten')
                ->setCellValue('I1', 'Kecamatan')
                ->setCellValue('J1', 'Desa')
                ->setCellValue('K1', 'Alamat')
                ->setCellValue('L1', 'Status')
                ->setCellValue('M1', 'Tanggal Daftar');

    $row = 2;
    foreach ($result as $r) {
        $objPHPExcel->getActiveSheet()
                    ->setCellValue("A$row", $r['nrk_sanggar'])
                    ->setCellValue("B$row", $r['nama_sanggar'])
                    ->setCellValue("C$row", $r['jenis_seni_list'] ?? '-')
                    ->setCellValue("D$row", $r['jenis_lembaga'])
                    ->setCellValue("E$row", $r['status_hukum'])
                    ->setCellValue("F$row", $r['no_hp'])
                    ->setCellValue("G$row", $r['provinsi'])
                    ->setCellValue("H$row", $r['kabupaten'])
                    ->setCellValue("I$row", $r['kecamatan'])
                    ->setCellValue("J$row", $r['desa'])
                    ->setCellValue("K$row", $r['alamat'])
                    ->setCellValue("L$row", $r['status'])
                    ->setCellValue("M$row", date('d-m-Y', strtotime($r['created_at'])));
        $row++;
    }

    header('Content-Type: application/vnd.ms-excel');
    header("Content-Disposition: attachment;filename=\"Pendaftar_Kelompok_Filter.xls\"");
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
}

// ==========================
// EXPORT KELOMPOK - PDF (FILTER)
// ==========================
public function export_kelompok_pdf_filter() {
    $keyword = $this->input->get('keyword');
    $status  = $this->input->get('status');
    $tahun   = $this->input->get('tahun');

    $this->db->select('
        sg.id_sanggar, sg.nrk_sanggar, sg.nama_sanggar, sg.jenis_lembaga, 
        sg.status_hukum, sg.created_at, ak.no_hp, ak.provinsi, ak.kabupaten, ak.kecamatan, ak.desa, ak.alamat,
        GROUP_CONCAT(js.nama_seni SEPARATOR ", ") as jenis_seni_list,
        IF(sg.nrk_sanggar IS NOT NULL AND sg.nrk_sanggar != "", "valid", "draft") as status'
    )
    ->from('sanggar sg')
    ->join('sanggar_alamat_kontak ak', 'sg.id_sanggar = ak.id_sanggar', 'left')
    // Tambahkan JOIN ke tabel sanggar_seni dan jenis_seni
    ->join('sanggar_seni ssg', 'sg.id_sanggar = ssg.id_sanggar', 'left')
    ->join('jenis_seni js', 'ssg.id_seni = js.id_seni', 'left');

    if (!empty($keyword)) {
        $this->db->group_start()
                 ->like('sg.nrk_sanggar', $keyword)
                 ->or_like('sg.nama_sanggar', $keyword)
                 ->or_like('sg.jenis_lembaga', $keyword)
                 ->or_like('js.nama_seni', $keyword)
                 ->or_like('ak.provinsi', $keyword)
                 ->or_like('ak.kabupaten', $keyword)
                 ->group_end();
    }
    if (!empty($status)) {
        $this->db->where('IF(sg.nrk_sanggar IS NOT NULL AND sg.nrk_sanggar != "", "valid", "draft") =', $status);
    }
    if (!empty($tahun)) {
        $this->db->where('YEAR(sg.created_at)', $tahun);
    }
    
    // Tambahkan GROUP BY untuk GROUP_CONCAT
    $this->db->group_by('sg.id_sanggar');

    $data['kelompok'] = $this->db->order_by('sg.created_at', 'ASC')->get()->result();

    $html = $this->load->view('admin/export_kelompok_pdf', $data, TRUE);
    $this->pdf->createPDF($html, 'Pendaftar_Kelompok_Filter', true, 'A4', 'landscape');
}


// ==========================
// EXPORT KELOMPOK - EXCEL (ALL)
// ==========================
public function export_kelompok_excel() {
    $this->db->select('
        sg.id_sanggar, sg.nrk_sanggar, sg.nama_sanggar, sg.jenis_lembaga, 
        sg.status_hukum, sg.created_at, ak.no_hp, ak.provinsi, ak.kabupaten, ak.kecamatan, ak.desa, ak.alamat,
        GROUP_CONCAT(js.nama_seni SEPARATOR ", ") as jenis_seni_list,
        IF(sg.nrk_sanggar IS NOT NULL AND sg.nrk_sanggar != "", "valid", "draft") as status'
    )
    ->from('sanggar sg')
    ->join('sanggar_alamat_kontak ak', 'sg.id_sanggar = ak.id_sanggar', 'left')
    // Correct JOIN: Join through the intermediate table 'sanggar_seni'
    ->join('sanggar_seni ssg', 'sg.id_sanggar = ssg.id_sanggar', 'left')
    // Then join to the 'jenis_seni' table
    ->join('jenis_seni js', 'ssg.id_seni = js.id_seni', 'left')
    ->group_by('sg.id_sanggar')
    ->order_by('sg.created_at', 'ASC');

    $query = $this->db->get()->result_array();

    $this->load->library('excel');
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', 'NRK')
                ->setCellValue('B1', 'Nama Sanggar')
                ->setCellValue('C1', 'Jenis Seni')
                ->setCellValue('D1', 'Jenis Lembaga')
                ->setCellValue('E1', 'Status Hukum')
                ->setCellValue('F1', 'No HP')
                ->setCellValue('G1', 'Provinsi')
                ->setCellValue('H1', 'Kabupaten')
                ->setCellValue('I1', 'Kecamatan')
                ->setCellValue('J1', 'Desa')
                ->setCellValue('K1', 'Alamat')
                ->setCellValue('L1', 'Status')
                ->setCellValue('M1', 'Tanggal Daftar');

    $row = 2;
    foreach ($query as $q) {
        $objPHPExcel->getActiveSheet()
                    ->setCellValue("A$row", $q['nrk_sanggar'])
                    ->setCellValue("B$row", $q['nama_sanggar'])
                    ->setCellValue("C$row", $q['jenis_seni_list'] ?? '-')
                    ->setCellValue("D$row", $q['jenis_lembaga'])
                    ->setCellValue("E$row", $q['status_hukum'])
                    ->setCellValue("F$row", $q['no_hp'])
                    ->setCellValue("G$row", $q['provinsi'])
                    ->setCellValue("H$row", $q['kabupaten'])
                    ->setCellValue("I$row", $q['kecamatan'])
                    ->setCellValue("J$row", $q['desa'])
                    ->setCellValue("K$row", $q['alamat'])
                    ->setCellValue("L$row", $q['status'])
                    ->setCellValue("M$row", date('d-m-Y', strtotime($q['created_at'])));
        $row++;
    }

    header('Content-Type: application/vnd.ms-excel');
    header("Content-Disposition: attachment;filename=\"Pendaftar_Kelompok.xls\"");
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
}

// ==========================
// EXPORT KELOMPOK - PDF (ALL)
// ==========================
public function export_kelompok_pdf() {
    $this->db->select('
        sg.id_sanggar, sg.nrk_sanggar, sg.nama_sanggar, sg.jenis_lembaga, 
        sg.status_hukum, sg.created_at, ak.no_hp, ak.provinsi, ak.kabupaten, ak.kecamatan, ak.desa, ak.alamat,
        GROUP_CONCAT(js.nama_seni SEPARATOR ", ") as jenis_seni_list,
        IF(sg.nrk_sanggar IS NOT NULL AND sg.nrk_sanggar != "", "valid", "draft") as status'
    )
    ->from('sanggar sg')
    ->join('sanggar_alamat_kontak ak', 'sg.id_sanggar = ak.id_sanggar', 'left')
    ->join('sanggar_seni ssg', 'sg.id_sanggar = ssg.id_sanggar', 'left')
    ->join('jenis_seni js', 'ssg.id_seni = js.id_seni', 'left')
    ->group_by('sg.id_sanggar')
    ->order_by('sg.created_at', 'ASC');

    $data['kelompok'] = $this->db->get()->result();

    $html = $this->load->view('admin/export_kelompok_pdf', $data, TRUE);
    $this->pdf->createPDF($html, 'Pendaftar_Kelompok', true, 'A4', 'landscape');
}


}