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

/**
 * Controller Export
 * Menangani semua export Excel dan PDF untuk semua modul
 * 
 * URL:
 * - /export/index/warga/excel
 * - /export/index/warga/pdf
 * - /export/index/iuran/excel
 * - /export/index/iuran/pdf
 * - /export/index/pinjaman/excel
 * - /export/index/pinjaman/pdf
 * - /export/index/pemilih/excel
 * - /export/index/pemilih/pdf
 */
class Export extends CI_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->library('session');
        $this->load->helper('authorization');

        // Cek login
        if (!$this->session->userdata('logged_in')) {
            redirect('auth');
        }

        // Hanya admin yang bisa export
        if (is_warga()) {
            $this->session->set_flashdata('error', 'Akses ditolak!');
            redirect('dashboard');
        }

        // Load autoload vendor (PhpSpreadsheet & mPDF)
        $autoload_paths = [
            FCPATH . 'vendor/autoload.php',
            APPPATH . 'vendor/autoload.php',
            realpath(APPPATH . '../../vendor/autoload.php'),
            dirname(APPPATH) . '/vendor/autoload.php',
        ];
        foreach ($autoload_paths as $path) {
            if ($path && file_exists($path)) {
                require_once $path;
                break;
            }
        }
    }

    // ══════════════════════════════════════════════════════════════
    // ROUTER METHOD — /export/{modul}/{format}
    // ══════════════════════════════════════════════════════════════
    public function index($modul = null, $format = null) {
        if (!$modul || !$format) {
            show_404();
        }

        $method = $modul . '_' . $format; // contoh: warga_excel, iuran_pdf
        if (method_exists($this, $method)) {
            $this->$method();
        } else {
            show_404();
        }
    }

    // ══════════════════════════════════════════════════════════════
    // WARGA — EXCEL
    // URL: /export/index/warga/excel
    // ══════════════════════════════════════════════════════════════
    public function warga_excel() {
        $this->load->model('Warga_model');
        $warga = $this->Warga_model->get_all();

        $sheet = $this->_make_spreadsheet('Data Warga');
        $ws    = $sheet->getActiveSheet();

        $this->_header_row($ws, 'A1:K1', 'DATA WARGA RT — ' . strtoupper(date('d F Y')),
            'Total: ' . count($warga) . ' jiwa', 'K');

        $headers = ['No','NIK','Nama Lengkap','L/P','No KK','Hub. Keluarga',
                    'Domisili','Tgl Lahir','Agama','Status Kawin','Status'];
        $this->_set_header($ws, $headers, 3);

        $hub_label   = ['kepala_keluarga'=>'Kepala KK','istri'=>'Istri','anak'=>'Anak',
                        'cucu'=>'Cucu','orang_tua'=>'Orang Tua','mertua'=>'Mertua',
                        'famili_lain'=>'Famili Lain','pembantu'=>'Pembantu','lainnya'=>'Lainnya'];
        $dom_label   = ['tetap'=>'Tetap','kost'=>'Kost','kontrak'=>'Kontrak',
                        'pendatang'=>'Pendatang','sementara'=>'Sementara'];
        $kawin_label = ['belum_kawin'=>'Belum Kawin','kawin'=>'Kawin',
                        'cerai_hidup'=>'Cerai Hidup','cerai_mati'=>'Cerai Mati'];

        $row = 4; $no = 1;
        foreach ($warga as $w) {
            $meninggal = (!empty($w->status_meninggal) && $w->status_meninggal == 1);
            $bg = $meninggal ? 'F3F4F6' : ($no % 2 === 0 ? 'F0FDFA' : 'FFFFFF');

            $data = [
                $no++,
                $w->nik ?? '-',
                $w->nama_lengkap ?? '-',
                ($w->jenis_kelamin ?? '') === 'L' ? 'Laki-laki' : 'Perempuan',
                $w->no_kk ?? '-',
                $hub_label[$w->status_hubungan ?? ''] ?? ($w->status_hubungan ?? '-'),
                $dom_label[$w->status_domisili ?? ''] ?? ($w->status_domisili ?? '-'),
                !empty($w->tanggal_lahir) ? date('d/m/Y', strtotime($w->tanggal_lahir)) : '-',
                ucfirst($w->agama ?? '-'),
                $kawin_label[$w->status_perkawinan ?? ''] ?? ($w->status_perkawinan ?? '-'),
                $meninggal ? 'Meninggal' : 'Aktif',
            ];
            $this->_set_row($ws, $data, $row, $bg, [0, 3, 7, 10]);
            $row++;
        }

        $this->_auto_size($ws, range('A', 'K'));
        $ws->freezePane('A4');
        $ws->setAutoFilter('A3:K3');
        $this->_output_excel($sheet, 'data_warga');
    }

    // ══════════════════════════════════════════════════════════════
    // WARGA — PDF
    // URL: /export/index/warga/pdf
    // ══════════════════════════════════════════════════════════════
    public function warga_pdf() {
        $this->load->model('Warga_model');
        $warga = $this->Warga_model->get_all();

        $hub_label   = ['kepala_keluarga'=>'Kepala KK','istri'=>'Istri','anak'=>'Anak',
                        'cucu'=>'Cucu','orang_tua'=>'Orang Tua','mertua'=>'Mertua',
                        'famili_lain'=>'Famili Lain','pembantu'=>'Pembantu','lainnya'=>'Lainnya'];
        $dom_label   = ['tetap'=>'Tetap','kost'=>'Kost','kontrak'=>'Kontrak',
                        'pendatang'=>'Pendatang','sementara'=>'Sementara'];
        $kawin_label = ['belum_kawin'=>'Belum Kawin','kawin'=>'Kawin',
                        'cerai_hidup'=>'Cerai Hidup','cerai_mati'=>'Cerai Mati'];

        $rows = ''; $no = 1;
        foreach ($warga as $w) {
            $meninggal = (!empty($w->status_meninggal) && $w->status_meninggal == 1);
            $bg = $meninggal ? '#f3f4f6' : ($no % 2 === 0 ? '#f0fdfa' : '#ffffff');
            $style = $meninggal ? 'color:#9ca3af;text-decoration:line-through;' : '';
            $badge = $meninggal
                ? '<span style="background:#e5e7eb;color:#6b7280;padding:2px 6px;border-radius:4px;font-size:8px;">Meninggal</span>'
                : '<span style="background:#dcfce7;color:#166534;padding:2px 6px;border-radius:4px;font-size:8px;">Aktif</span>';

            $rows .= '<tr style="background:' . $bg . ';' . $style . '">
                <td style="text-align:center;">' . $no++ . '</td>
                <td style="font-family:monospace;font-size:8px;">' . htmlspecialchars($w->nik ?? '-') . '</td>
                <td><strong>' . htmlspecialchars($w->nama_lengkap ?? '-') . '</strong></td>
                <td style="text-align:center;">' . (($w->jenis_kelamin ?? '') === 'L' ? 'L' : 'P') . '</td>
                <td style="font-size:8px;">' . htmlspecialchars($w->no_kk ?? '-') . '</td>
                <td>' . ($hub_label[$w->status_hubungan ?? ''] ?? '-') . '</td>
                <td>' . ($dom_label[$w->status_domisili ?? ''] ?? '-') . '</td>
                <td style="text-align:center;">' . (!empty($w->tanggal_lahir) ? date('d/m/Y', strtotime($w->tanggal_lahir)) : '-') . '</td>
                <td>' . ucfirst($w->agama ?? '-') . '</td>
                <td>' . ($kawin_label[$w->status_perkawinan ?? ''] ?? '-') . '</td>
                <td style="text-align:center;">' . $badge . '</td>
            </tr>';
        }

        $total_aktif = count(array_filter((array)$warga, fn($w) => empty($w->status_meninggal) || $w->status_meninggal == 0));

        $summary = '<div class="summary">
            <div class="sum-box" style="background:#f0fdfa;border:1px solid #99f6e4;">
                <div class="val" style="color:#0f766e;">' . count($warga) . '</div>
                <div class="lbl" style="color:#0f766e;">Total Jiwa</div>
            </div>
            <div class="sum-box" style="background:#dcfce7;border:1px solid #bbf7d0;">
                <div class="val" style="color:#166534;">' . $total_aktif . '</div>
                <div class="lbl" style="color:#166534;">Aktif</div>
            </div>
            <div class="sum-box" style="background:#f3f4f6;border:1px solid #d1d5db;">
                <div class="val" style="color:#6b7280;">' . (count($warga) - $total_aktif) . '</div>
                <div class="lbl" style="color:#6b7280;">Meninggal</div>
            </div>
        </div>';

        $th = '<th style="width:25px;">No</th><th style="width:100px;">NIK</th>
               <th style="width:130px;">Nama</th><th style="width:20px;">L/P</th>
               <th style="width:100px;">No KK</th><th style="width:75px;">Hub. KK</th>
               <th style="width:60px;">Domisili</th><th style="width:65px;">Tgl Lahir</th>
               <th style="width:50px;">Agama</th><th style="width:70px;">Kawin</th>
               <th style="width:55px;">Status</th>';

        $html = $this->_pdf_template(
            'DATA WARGA RT',
            'Total: ' . count($warga) . ' jiwa | Aktif: ' . $total_aktif,
            $summary, $th, $rows
        );

        $this->_output_pdf($html, 'data_warga');
    }

    // ══════════════════════════════════════════════════════════════
    // IURAN — EXCEL
    // URL: /export/index/iuran/excel
    // ══════════════════════════════════════════════════════════════
    public function iuran_excel() {
        $this->load->model('Iuran_model');

        $status = $this->input->get('status');
        $tahun  = $this->input->get('tahun');
        $iuran  = $this->_get_iuran($status, $tahun);

        $sheet = $this->_make_spreadsheet('Data Iuran');
        $ws    = $sheet->getActiveSheet();

        $filter_info = '';
        if ($status) $filter_info .= ' | Status: ' . ucfirst(str_replace('_', ' ', $status));
        if ($tahun)  $filter_info .= ' | Tahun: ' . $tahun;

        $this->_header_row($ws, 'A1:I1',
            'LAPORAN IURAN WARGA — ' . strtoupper(date('d F Y')),
            'Total: ' . count($iuran) . ' data' . $filter_info, 'I');

        $headers = ['No','Nama Warga','NIK','Jenis Iuran','Periode','Nominal','Status','Tgl Bayar','Metode'];
        $this->_set_header($ws, $headers, 3);

        $bulan_names  = ['','Januari','Februari','Maret','April','Mei','Juni',
                         'Juli','Agustus','September','Oktober','November','Desember'];
        $status_label = ['lunas'=>'Lunas','menunggu_verifikasi'=>'Menunggu Verifikasi','pending'=>'Belum Dibayar'];
        $status_color = ['lunas'=>'DCFCE7','menunggu_verifikasi'=>'FEF9C3','pending'=>'FEE2E2'];

        $row = 4; $no = 1; $total_lunas = 0; $total_pending = 0;
        foreach ($iuran as $i) {
            $sv  = isset($i->status_bayar) ? $i->status_bayar : ($i->status ?? 'pending');
            $bg  = $status_color[$sv] ?? 'FFFFFF';
            $per = (isset($i->periode_bulan) && isset($i->periode_tahun))
                    ? $bulan_names[(int)$i->periode_bulan] . ' ' . $i->periode_tahun : '-';

            $data = [
                $no++,
                $i->nama_lengkap ?? '-',
                $i->nik ?? '-',
                $i->nama_iuran ?? '-',
                $per,
                (float)$i->nominal,
                $status_label[$sv] ?? ucfirst($sv),
                (!empty($i->tanggal_bayar) && $i->tanggal_bayar !== '0000-00-00')
                    ? date('d/m/Y', strtotime($i->tanggal_bayar)) : '-',
                ucfirst($i->metode_pembayaran ?? '-'),
            ];
            $this->_set_row($ws, $data, $row, $bg, [0, 6, 7, 8]);
            $ws->getStyle('F' . $row)->getNumberFormat()->setFormatCode('"Rp "#,##0');
            $row++;

            if ($sv === 'lunas') $total_lunas += $i->nominal;
            else                 $total_pending += $i->nominal;
        }

        // Baris total
        foreach ([['TOTAL LUNAS', $total_lunas, 'DCFCE7', '166534'], ['TOTAL BELUM LUNAS', $total_pending, 'FEE2E2', '991B1B']] as $t) {
            $ws->mergeCells('A' . $row . ':E' . $row);
            $ws->setCellValue('A' . $row, $t[0]);
            $ws->setCellValue('F' . $row, $t[1]);
            $ws->getStyle('A' . $row . ':F' . $row)->applyFromArray([
                'font' => ['bold' => true, 'color' => ['rgb' => $t[3]]],
                'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => $t[2]]],
            ]);
            $ws->getStyle('F' . $row)->getNumberFormat()->setFormatCode('"Rp "#,##0');
            $row++;
        }

        $this->_auto_size($ws, range('A', 'I'));
        $ws->getColumnDimension('A')->setWidth(5);
        $ws->freezePane('A4');
        $ws->setAutoFilter('A3:I3');
        $this->_output_excel($sheet, 'laporan_iuran');
    }

    // ══════════════════════════════════════════════════════════════
    // IURAN — PDF
    // URL: /export/index/iuran/pdf
    // ══════════════════════════════════════════════════════════════
    public function iuran_pdf() {
        $this->load->model('Iuran_model');

        $status = $this->input->get('status');
        $tahun  = $this->input->get('tahun');
        $iuran  = $this->_get_iuran($status, $tahun);

        $bulan_names  = ['','Januari','Februari','Maret','April','Mei','Juni',
                         'Juli','Agustus','September','Oktober','November','Desember'];
        $status_label = ['lunas'=>'Lunas','menunggu_verifikasi'=>'Menunggu Verifikasi','pending'=>'Belum Dibayar'];
        $status_bg    = ['lunas'=>'#dcfce7','menunggu_verifikasi'=>'#fef9c3','pending'=>'#fee2e2'];
        $status_txt   = ['lunas'=>'#166534','menunggu_verifikasi'=>'#854d0e','pending'=>'#991b1b'];

        $rows = ''; $no = 1; $total_lunas = 0; $total_belum = 0;
        foreach ($iuran as $i) {
            $sv  = isset($i->status_bayar) ? $i->status_bayar : ($i->status ?? 'pending');
            $bg  = $no % 2 === 0 ? '#f8fafc' : '#ffffff';
            $per = (isset($i->periode_bulan) && isset($i->periode_tahun))
                    ? $bulan_names[(int)$i->periode_bulan] . ' ' . $i->periode_tahun : '-';
            $badge = '<span style="background:' . ($status_bg[$sv] ?? '#f3f4f6') . ';color:' . ($status_txt[$sv] ?? '#374151') . ';padding:2px 7px;border-radius:4px;font-size:8px;font-weight:700;">'
                   . ($status_label[$sv] ?? ucfirst($sv)) . '</span>';

            $rows .= '<tr style="background:' . $bg . ';">
                <td style="text-align:center;">' . $no++ . '</td>
                <td><strong>' . htmlspecialchars($i->nama_lengkap ?? '-') . '</strong></td>
                <td style="font-family:monospace;font-size:8px;">' . htmlspecialchars($i->nik ?? '-') . '</td>
                <td>' . htmlspecialchars($i->nama_iuran ?? '-') . '</td>
                <td style="text-align:center;">' . $per . '</td>
                <td style="text-align:right;font-weight:700;">Rp ' . number_format($i->nominal, 0, ',', '.') . '</td>
                <td style="text-align:center;">' . $badge . '</td>
                <td style="text-align:center;">' . ((!empty($i->tanggal_bayar) && $i->tanggal_bayar !== '0000-00-00') ? date('d/m/Y', strtotime($i->tanggal_bayar)) : '-') . '</td>
                <td style="text-align:center;">' . ucfirst($i->metode_pembayaran ?? '-') . '</td>
            </tr>';

            if ($sv === 'lunas') $total_lunas += $i->nominal;
            else                 $total_belum += $i->nominal;
        }

        $filter_info = '';
        if ($status) $filter_info .= ' | Status: ' . ($status_label[$status] ?? $status);
        if ($tahun)  $filter_info .= ' | Tahun: ' . $tahun;

        $summary = '<div class="summary">
            <div class="sum-box" style="background:#dcfce7;border:1px solid #bbf7d0;">
                <div class="val" style="color:#166534;">Rp ' . number_format($total_lunas, 0, ',', '.') . '</div>
                <div class="lbl" style="color:#166534;">Total Lunas</div>
            </div>
            <div class="sum-box" style="background:#fee2e2;border:1px solid #fecaca;">
                <div class="val" style="color:#991b1b;">Rp ' . number_format($total_belum, 0, ',', '.') . '</div>
                <div class="lbl" style="color:#991b1b;">Belum Lunas</div>
            </div>
            <div class="sum-box" style="background:#f0fdfa;border:1px solid #99f6e4;">
                <div class="val" style="color:#0f766e;">Rp ' . number_format($total_lunas + $total_belum, 0, ',', '.') . '</div>
                <div class="lbl" style="color:#0f766e;">Total Keseluruhan</div>
            </div>
        </div>';

        $th = '<th style="width:25px;">No</th><th style="width:115px;">Nama Warga</th>
               <th style="width:95px;">NIK</th><th style="width:85px;">Jenis Iuran</th>
               <th style="width:75px;">Periode</th><th style="width:80px;">Nominal</th>
               <th style="width:90px;">Status</th><th style="width:65px;">Tgl Bayar</th>
               <th style="width:55px;">Metode</th>';

        $tfoot = '<tfoot>
            <tr style="background:#f0fdfa;">
                <td colspan="5" style="text-align:right;color:#0f766e;font-weight:700;">TOTAL LUNAS:</td>
                <td style="text-align:right;font-weight:700;color:#166534;">Rp ' . number_format($total_lunas, 0, ',', '.') . '</td>
                <td colspan="3"></td>
            </tr>
            <tr style="background:#fff5f5;">
                <td colspan="5" style="text-align:right;color:#991b1b;font-weight:700;">TOTAL BELUM LUNAS:</td>
                <td style="text-align:right;font-weight:700;color:#991b1b;">Rp ' . number_format($total_belum, 0, ',', '.') . '</td>
                <td colspan="3"></td>
            </tr>
        </tfoot>';

        $html = $this->_pdf_template(
            'LAPORAN IURAN WARGA RT',
            'Total: ' . count($iuran) . ' data' . $filter_info,
            $summary, $th, $rows, $tfoot
        );

        $this->_output_pdf($html, 'laporan_iuran');
    }

    // ══════════════════════════════════════════════════════════════
    // PINJAMAN — EXCEL
    // URL: /export/index/pinjaman/excel
    // ══════════════════════════════════════════════════════════════
    public function pinjaman_excel() {
        $this->load->model('Pinjaman_model');
        $pinjaman = $this->Pinjaman_model->get_pinjaman_aktif();

        $sheet = $this->_make_spreadsheet('Data Pinjaman');
        $ws    = $sheet->getActiveSheet();

        $this->_header_row($ws, 'A1:J1',
            'LAPORAN PINJAMAN AKTIF — ' . strtoupper(date('d F Y')),
            'Total: ' . count($pinjaman) . ' pinjaman aktif', 'J');

        $headers = ['No','Kode','Nama Warga','Nominal Disetujui','Total Pinjaman',
                    'Angsuran/Bulan','Sisa Pinjaman','Tenor','Sisa Tenor','Status'];
        $this->_set_header($ws, $headers, 3);

        $row = 4; $no = 1; $total_piutang = 0;
        foreach ($pinjaman as $p) {
            $status = $p->status_pinjaman ?? 'aktif';
            $bg     = $status === 'lunas' ? 'DCFCE7' : ($no % 2 === 0 ? 'FEF2F2' : 'FFFFFF');

            $data = [
                $no++,
                $p->kode_pinjaman ?? '-',
                $p->nama_lengkap ?? '-',
                (float)($p->nominal_disetujui ?? 0),
                (float)($p->total_pinjaman ?? 0),
                (float)($p->angsuran_per_bulan ?? 0),
                (float)($p->sisa_pinjaman ?? 0),
                ($p->tenor ?? 0) . ' bln',
                ($p->sisa_tenor ?? 0) . ' bln',
                ucfirst($status),
            ];
            $this->_set_row($ws, $data, $row, $bg, [0, 9]);

            foreach (['D','E','F','G'] as $col) {
                $ws->getStyle($col . $row)->getNumberFormat()->setFormatCode('"Rp "#,##0');
            }
            $row++;
            $total_piutang += ($p->sisa_pinjaman ?? 0);
        }

        // Total piutang
        $ws->mergeCells('A' . $row . ':F' . $row);
        $ws->setCellValue('A' . $row, 'TOTAL PIUTANG');
        $ws->setCellValue('G' . $row, $total_piutang);
        $ws->getStyle('A' . $row . ':G' . $row)->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => '991B1B']],
            'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => 'FEE2E2']],
        ]);
        $ws->getStyle('G' . $row)->getNumberFormat()->setFormatCode('"Rp "#,##0');

        $this->_auto_size($ws, range('A', 'J'));
        $ws->getColumnDimension('A')->setWidth(5);
        $ws->freezePane('A4');
        $ws->setAutoFilter('A3:J3');
        $this->_output_excel($sheet, 'laporan_pinjaman');
    }

    // ══════════════════════════════════════════════════════════════
    // PINJAMAN — PDF
    // URL: /export/index/pinjaman/pdf
    // ══════════════════════════════════════════════════════════════
    public function pinjaman_pdf() {
        $this->load->model('Pinjaman_model');
        $pinjaman = $this->Pinjaman_model->get_pinjaman_aktif();

        $rows = ''; $no = 1; $total_piutang = 0;
        foreach ($pinjaman as $p) {
            $status = $p->status_pinjaman ?? 'aktif';
            $bg     = $no % 2 === 0 ? '#f8fafc' : '#ffffff';
            $badge  = $status === 'lunas'
                ? '<span style="background:#dcfce7;color:#166534;padding:2px 6px;border-radius:4px;font-size:8px;">Lunas</span>'
                : '<span style="background:#fee2e2;color:#991b1b;padding:2px 6px;border-radius:4px;font-size:8px;">Aktif</span>';

            $rows .= '<tr style="background:' . $bg . ';">
                <td style="text-align:center;">' . $no++ . '</td>
                <td style="font-family:monospace;font-size:8px;">' . htmlspecialchars($p->kode_pinjaman ?? '-') . '</td>
                <td><strong>' . htmlspecialchars($p->nama_lengkap ?? '-') . '</strong></td>
                <td style="text-align:right;">Rp ' . number_format($p->nominal_disetujui ?? 0, 0, ',', '.') . '</td>
                <td style="text-align:right;">Rp ' . number_format($p->total_pinjaman ?? 0, 0, ',', '.') . '</td>
                <td style="text-align:right;">Rp ' . number_format($p->angsuran_per_bulan ?? 0, 0, ',', '.') . '</td>
                <td style="text-align:right;font-weight:700;color:#991b1b;">Rp ' . number_format($p->sisa_pinjaman ?? 0, 0, ',', '.') . '</td>
                <td style="text-align:center;">' . ($p->tenor ?? 0) . ' bln</td>
                <td style="text-align:center;">' . ($p->sisa_tenor ?? 0) . ' bln</td>
                <td style="text-align:center;">' . $badge . '</td>
            </tr>';
            $total_piutang += ($p->sisa_pinjaman ?? 0);
        }

        $summary = '<div class="summary">
            <div class="sum-box" style="background:#fee2e2;border:1px solid #fecaca;">
                <div class="val" style="color:#991b1b;">Rp ' . number_format($total_piutang, 0, ',', '.') . '</div>
                <div class="lbl" style="color:#991b1b;">Total Piutang</div>
            </div>
            <div class="sum-box" style="background:#f0fdfa;border:1px solid #99f6e4;">
                <div class="val" style="color:#0f766e;">' . count($pinjaman) . '</div>
                <div class="lbl" style="color:#0f766e;">Total Pinjaman Aktif</div>
            </div>
        </div>';

        $th = '<th style="width:25px;">No</th><th style="width:85px;">Kode</th>
               <th style="width:120px;">Nama Warga</th><th style="width:80px;">Nominal</th>
               <th style="width:80px;">Total</th><th style="width:80px;">Angsuran/Bln</th>
               <th style="width:80px;">Sisa</th><th style="width:40px;">Tenor</th>
               <th style="width:45px;">Sisa Tnr</th><th style="width:45px;">Status</th>';

        $tfoot = '<tfoot>
            <tr style="background:#fee2e2;">
                <td colspan="6" style="text-align:right;font-weight:700;color:#991b1b;">TOTAL PIUTANG:</td>
                <td style="text-align:right;font-weight:700;color:#991b1b;">Rp ' . number_format($total_piutang, 0, ',', '.') . '</td>
                <td colspan="3"></td>
            </tr>
        </tfoot>';

        $html = $this->_pdf_template(
            'LAPORAN PINJAMAN AKTIF RT',
            'Total: ' . count($pinjaman) . ' pinjaman aktif',
            $summary, $th, $rows, $tfoot
        );

        $this->_output_pdf($html, 'laporan_pinjaman');
    }


    // ══════════════════════════════════════════════════════════════
    // PEMILIH — EXCEL
    // URL: /export/index/pemilih/excel
    // ══════════════════════════════════════════════════════════════
    public function pemilih_excel() {
        $this->load->model('Warga_model');
        $pemilih = $this->Warga_model->get_pemilih_pemilu();

        $sheet = $this->_make_spreadsheet('Data Pemilih');
        $ws    = $sheet->getActiveSheet();

        // Hitung statistik
        $today    = new DateTime();
        $pria     = 0; $wanita = 0; $pemula = 0; $lansia = 0;
        foreach ($pemilih as $p) {
            $umur = 0;
            if (!empty($p->tanggal_lahir)) {
                $umur = $today->diff(new DateTime($p->tanggal_lahir))->y;
            }
            if (($p->jenis_kelamin ?? '') === 'L') $pria++; else $wanita++;
            if ($umur >= 17 && $umur <= 21) $pemula++;
            if ($umur >= 56) $lansia++;
        }

        $this->_header_row($ws, 'A1:K1',
            'DAFTAR PEMILIH PEMILU — ' . strtoupper(date('d F Y')),
            'Total: ' . count($pemilih) . ' pemilih | L: ' . $pria . ' | P: ' . $wanita .
            ' | Pemula: ' . $pemula . ' | Lansia: ' . $lansia, 'K');

        $headers = ['No','NIK','Nama Lengkap','L/P','Tempat Lahir','Tgl Lahir',
                    'Umur','Kelompok','Status Kawin','Domisili','No KK'];
        $this->_set_header($ws, $headers, 3);

        $dom_label   = ['tetap'=>'Tetap','kost'=>'Kost','kontrak'=>'Kontrak',
                        'pendatang'=>'Pendatang','sementara'=>'Sementara'];
        $kawin_label = ['belum_kawin'=>'Belum Kawin','kawin'=>'Kawin',
                        'cerai_hidup'=>'Cerai Hidup','cerai_mati'=>'Cerai Mati'];

        // Warna per kelompok umur
        $kel_color = [
            'Pemula' => 'DCFCE7',
            'Muda'   => 'DBEAFE',
            'Dewasa' => 'EDE9FE',
            'Lansia' => 'FEF3C7',
            'Tua'    => 'FEE2E2',
        ];

        $row = 4; $no = 1;
        foreach ($pemilih as $p) {
            $umur = 0;
            if (!empty($p->tanggal_lahir)) {
                $umur = $today->diff(new DateTime($p->tanggal_lahir))->y;
            }
            $kel = $this->_kelompok_umur($umur);
            $bg  = $kel_color[$kel] ?? 'FFFFFF';

            $data = [
                $no++,
                $p->nik ?? '-',
                $p->nama_lengkap ?? '-',
                ($p->jenis_kelamin ?? '') === 'L' ? 'Laki-laki' : 'Perempuan',
                $p->tempat_lahir ?? '-',
                !empty($p->tanggal_lahir) ? date('d/m/Y', strtotime($p->tanggal_lahir)) : '-',
                $umur . ' thn',
                $kel,
                $kawin_label[$p->status_perkawinan ?? ''] ?? ($p->status_perkawinan ?? '-'),
                $dom_label[$p->status_domisili ?? ''] ?? ($p->status_domisili ?? '-'),
                $p->no_kk ?? '-',
            ];
            $this->_set_row($ws, $data, $row, $bg, [0, 3, 5, 6, 7]);
            $row++;
        }

        // Baris ringkasan kelompok umur
        $row++;
        $ws->mergeCells('A' . $row . ':K' . $row);
        $ws->setCellValue('A' . $row, 'RINGKASAN KELOMPOK UMUR');
        $ws->getStyle('A' . $row)->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 10],
            'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => '1E3A5F']],
            'alignment' => ['horizontal' => 'center'],
        ]);
        $row++;

        $kelompok_data = [
            ['Pemula (17-21)', 'DCFCE7', '166534'],
            ['Muda (22-35)',   'DBEAFE', '1E40AF'],
            ['Dewasa (36-55)', 'EDE9FE', '5B21B6'],
            ['Lansia (56-70)', 'FEF3C7', '92400E'],
            ['Tua (71+)',      'FEE2E2', '991B1B'],
        ];

        foreach ($kelompok_data as $k) {
            $count = 0;
            $kel_name = explode(' ', $k[0])[0];
            foreach ($pemilih as $p) {
                $umur = 0;
                if (!empty($p->tanggal_lahir)) {
                    $umur = $today->diff(new DateTime($p->tanggal_lahir))->y;
                }
                if ($this->_kelompok_umur($umur) === $kel_name) $count++;
            }
            $ws->mergeCells('A' . $row . ':H' . $row);
            $ws->setCellValue('A' . $row, $k[0]);
            $ws->setCellValue('I' . $row, $count . ' orang');
            $ws->getStyle('A' . $row . ':I' . $row)->applyFromArray([
                'font' => ['bold' => true, 'color' => ['rgb' => $k[2]]],
                'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => $k[1]]],
            ]);
            $row++;
        }

        $this->_auto_size($ws, range('A', 'K'));
        $ws->getColumnDimension('A')->setWidth(5);
        $ws->freezePane('A4');
        $ws->setAutoFilter('A3:K3');
        $this->_output_excel($sheet, 'data_pemilih');
    }

    // ══════════════════════════════════════════════════════════════
    // PEMILIH — PDF
    // URL: /export/index/pemilih/pdf
    // ══════════════════════════════════════════════════════════════
    public function pemilih_pdf() {
        $this->load->model('Warga_model');
        $pemilih = $this->Warga_model->get_pemilih_pemilu();

        $today    = new DateTime();
        $pria     = 0; $wanita = 0; $pemula = 0; $lansia = 0;
        foreach ($pemilih as $p) {
            $umur = 0;
            if (!empty($p->tanggal_lahir)) {
                $umur = $today->diff(new DateTime($p->tanggal_lahir))->y;
            }
            if (($p->jenis_kelamin ?? '') === 'L') $pria++; else $wanita++;
            if ($umur >= 17 && $umur <= 21) $pemula++;
            if ($umur >= 56) $lansia++;
        }

        $dom_label   = ['tetap'=>'Tetap','kost'=>'Kost','kontrak'=>'Kontrak',
                        'pendatang'=>'Pendatang','sementara'=>'Sementara'];
        $kawin_label = ['belum_kawin'=>'Belum Kawin','kawin'=>'Kawin',
                        'cerai_hidup'=>'Cerai Hidup','cerai_mati'=>'Cerai Mati'];

        $kel_bg  = ['Pemula'=>'#dcfce7','Muda'=>'#dbeafe','Dewasa'=>'#ede9fe','Lansia'=>'#fef3c7','Tua'=>'#fee2e2'];
        $kel_txt = ['Pemula'=>'#166534','Muda'=>'#1e40af','Dewasa'=>'#5b21b6','Lansia'=>'#92400e','Tua'=>'#991b1b'];

        $rows = ''; $no = 1;
        foreach ($pemilih as $p) {
            $umur = 0;
            if (!empty($p->tanggal_lahir)) {
                $umur = $today->diff(new DateTime($p->tanggal_lahir))->y;
            }
            $kel   = $this->_kelompok_umur($umur);
            $bg    = $no % 2 === 0 ? '#f8fafc' : '#ffffff';
            $jk    = $p->jenis_kelamin ?? '';
            $badge = '<span style="background:' . ($kel_bg[$kel] ?? '#f3f4f6') . ';color:' . ($kel_txt[$kel] ?? '#374151') . ';padding:2px 7px;border-radius:4px;font-size:8px;font-weight:700;">' . $kel . '</span>';
            $jk_style = $jk === 'L'
                ? '<span style="color:#1d4ed8;font-weight:800;">L</span>'
                : '<span style="color:#7c3aed;font-weight:800;">P</span>';

            $rows .= '<tr style="background:' . $bg . ';">
                <td style="text-align:center;">' . $no++ . '</td>
                <td style="font-family:monospace;font-size:8px;">' . htmlspecialchars($p->nik ?? '-') . '</td>
                <td><strong>' . htmlspecialchars($p->nama_lengkap ?? '-') . '</strong>
                    ' . (!empty($p->tempat_lahir) ? '<br><small style="color:#94a3b8;font-size:7.5px;">' . htmlspecialchars($p->tempat_lahir) . '</small>' : '') . '
                </td>
                <td style="text-align:center;">' . $jk_style . '</td>
                <td style="text-align:center;">' . (!empty($p->tanggal_lahir) ? date('d/m/Y', strtotime($p->tanggal_lahir)) : '-') . '</td>
                <td style="text-align:center;font-weight:700;">' . $umur . ' thn</td>
                <td style="text-align:center;">' . $badge . '</td>
                <td>' . ($kawin_label[$p->status_perkawinan ?? ''] ?? '-') . '</td>
                <td>' . ($dom_label[$p->status_domisili ?? ''] ?? '-') . '</td>
                <td style="font-family:monospace;font-size:8px;">' . htmlspecialchars($p->no_kk ?? '-') . '</td>
            </tr>';
        }

        $summary = '<div class="summary">
            <div class="sum-box" style="background:#f0fdfa;border:1px solid #99f6e4;">
                <div class="val" style="color:#0f766e;">' . count($pemilih) . '</div>
                <div class="lbl" style="color:#0f766e;">Total Pemilih</div>
            </div>
            <div class="sum-box" style="background:#dbeafe;border:1px solid #93c5fd;">
                <div class="val" style="color:#1d4ed8;">' . $pria . '</div>
                <div class="lbl" style="color:#1d4ed8;">Laki-laki</div>
            </div>
            <div class="sum-box" style="background:#ede9fe;border:1px solid #c4b5fd;">
                <div class="val" style="color:#7c3aed;">' . $wanita . '</div>
                <div class="lbl" style="color:#7c3aed;">Perempuan</div>
            </div>
            <div class="sum-box" style="background:#dcfce7;border:1px solid #bbf7d0;">
                <div class="val" style="color:#166534;">' . $pemula . '</div>
                <div class="lbl" style="color:#166534;">Pemilih Pemula</div>
            </div>
            <div class="sum-box" style="background:#fef3c7;border:1px solid #fde68a;">
                <div class="val" style="color:#92400e;">' . $lansia . '</div>
                <div class="lbl" style="color:#92400e;">Lansia (56+)</div>
            </div>
        </div>';

        $th = '<th style="width:25px;">No</th>
               <th style="width:95px;">NIK</th>
               <th style="width:130px;">Nama Lengkap</th>
               <th style="width:20px;">L/P</th>
               <th style="width:65px;">Tgl Lahir</th>
               <th style="width:45px;">Umur</th>
               <th style="width:65px;">Kelompok</th>
               <th style="width:75px;">Status Kawin</th>
               <th style="width:60px;">Domisili</th>
               <th style="width:95px;">No KK</th>';

        $html = $this->_pdf_template(
            'DAFTAR PEMILIH PEMILU RT',
            'Total: ' . count($pemilih) . ' pemilih | L: ' . $pria . ' | P: ' . $wanita .
            ' | Pemula: ' . $pemula . ' | Lansia: ' . $lansia,
            $summary, $th, $rows
        );

        $this->_output_pdf($html, 'data_pemilih');
    }

    // ══════════════════════════════════════════════════════════════
    // PRIVATE HELPERS
    // ══════════════════════════════════════════════════════════════

    /** Buat Spreadsheet baru */
    private function _make_spreadsheet($title = 'Sheet') {
        if (!class_exists('\PhpOffice\PhpSpreadsheet\Spreadsheet')) {
            $this->session->set_flashdata('error', 'Library PhpSpreadsheet tidak ditemukan.');
            redirect('dashboard');
        }
        $s = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $s->getActiveSheet()->setTitle($title);
        return $s;
    }

    /** Set baris header judul & sub-judul */
    private function _header_row($ws, $merge, $judul, $subjudul, $lastCol) {
        $ws->mergeCells($merge);
        $ws->setCellValue('A1', $judul);
        $ws->getStyle('A1')->applyFromArray([
            'font'      => ['bold' => true, 'size' => 14, 'color' => ['rgb' => 'FFFFFF']],
            'fill'      => ['fillType' => 'solid', 'startColor' => ['rgb' => '0D9488']],
            'alignment' => ['horizontal' => 'center', 'vertical' => 'center'],
        ]);
        $ws->getRowDimension(1)->setRowHeight(32);

        $merge2 = 'A2:' . $lastCol . '2';
        $ws->mergeCells($merge2);
        $ws->setCellValue('A2', $subjudul);
        $ws->getStyle('A2')->applyFromArray([
            'font'      => ['italic' => true, 'size' => 9, 'color' => ['rgb' => 'FFFFFF']],
            'fill'      => ['fillType' => 'solid', 'startColor' => ['rgb' => '0F766E']],
            'alignment' => ['horizontal' => 'center', 'vertical' => 'center'],
        ]);
        $ws->getRowDimension(2)->setRowHeight(18);
    }

    /** Set baris header kolom tabel */
    private function _set_header($ws, $headers, $row) {
        $cols = range('A', 'Z');
        foreach ($headers as $i => $label) {
            $cell = $cols[$i] . $row;
            $ws->setCellValue($cell, $label);
            $ws->getStyle($cell)->applyFromArray([
                'font'      => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 10],
                'fill'      => ['fillType' => 'solid', 'startColor' => ['rgb' => '1E3A5F']],
                'alignment' => ['horizontal' => 'center', 'vertical' => 'center'],
                'borders'   => ['allBorders' => ['borderStyle' => 'thin', 'color' => ['rgb' => '2D4A6F']]],
            ]);
        }
        $ws->getRowDimension($row)->setRowHeight(22);
    }

    /** Set satu baris data — $center berisi index kolom yang di-center */
    private function _set_row($ws, $data, $row, $bg = 'FFFFFF', $center = []) {
        $cols = range('A', 'Z');
        foreach ($data as $i => $val) {
            $cell = $cols[$i] . $row;
            $ws->setCellValue($cell, $val);
            $ws->getStyle($cell)->applyFromArray([
                'font'      => ['size' => 9],
                'fill'      => ['fillType' => 'solid', 'startColor' => ['rgb' => $bg]],
                'alignment' => ['vertical' => 'center',
                                'horizontal' => in_array($i, $center) ? 'center' : 'left'],
                'borders'   => ['allBorders' => ['borderStyle' => 'thin', 'color' => ['rgb' => 'E2E8F0']]],
            ]);
        }
        $ws->getRowDimension($row)->setRowHeight(18);
    }

    /** Auto-size kolom */
    private function _auto_size($ws, $cols) {
        foreach ($cols as $col) {
            $ws->getColumnDimension($col)->setAutoSize(true);
        }
    }

    /** Output file Excel */
    private function _output_excel($spreadsheet, $name) {
        if (ob_get_level()) ob_end_clean();
        $filename = $name . '_' . date('Ymd_His') . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }

    /** Build HTML template PDF */
    private function _pdf_template($judul, $subtitle, $summary, $th, $rows, $tfoot = '') {
        $tanggal = date('d F Y, H:i');
        return '<!DOCTYPE html><html><head><meta charset="UTF-8">
        <style>
          * { box-sizing:border-box; margin:0; padding:0; }
          body { font-family:Arial,sans-serif; font-size:10px; color:#1e293b; }
          .header { background:linear-gradient(135deg,#0d9488,#0f766e); color:#fff; padding:13px 18px; margin-bottom:12px; border-radius:6px; }
          .header h1 { font-size:14px; font-weight:800; margin-bottom:3px; }
          .header p  { font-size:8.5px; opacity:.85; }
          .summary { display:flex; gap:10px; margin-bottom:12px; }
          .sum-box { border-radius:6px; padding:8px 14px; flex:1; }
          .sum-box .val { font-size:14px; font-weight:800; }
          .sum-box .lbl { font-size:7.5px; text-transform:uppercase; letter-spacing:.05em; }
          table { width:100%; border-collapse:collapse; font-size:9px; }
          thead th { background:#1e3a5f; color:#fff; padding:6px 7px; text-align:left; font-size:8px; text-transform:uppercase; letter-spacing:.04em; border:1px solid #2d4a6f; }
          tbody td { padding:5px 7px; border:1px solid #e2e8f0; vertical-align:middle; }
          tfoot td { padding:5px 7px; border:1px solid #cbd5e1; }
          .footer { margin-top:12px; text-align:right; font-size:7.5px; color:#94a3b8; }
        </style></head><body>
        <div class="header"><h1>' . $judul . '</h1>
        <p>Dicetak: ' . $tanggal . ' &nbsp;|&nbsp; ' . $subtitle . '</p></div>
        ' . $summary . '
        <table><thead><tr>' . $th . '</tr></thead>
        <tbody>' . $rows . '</tbody>' . $tfoot . '</table>
        <div class="footer">Digenerate otomatis — Sistem Manajemen RT — ' . $tanggal . '</div>
        </body></html>';
    }

    /** Output PDF (mPDF / Dompdf / fallback HTML) */
    private function _output_pdf($html, $name) {
        $filename = $name . '_' . date('Ymd_His') . '.pdf';

        if (class_exists('\Mpdf\Mpdf')) {
            $mpdf = new \Mpdf\Mpdf([
                'mode' => 'utf-8', 'format' => 'A4-L',
                'margin_top' => 10, 'margin_bottom' => 10,
                'margin_left' => 10, 'margin_right' => 10,
            ]);
            $mpdf->SetTitle($name);
            $mpdf->WriteHTML($html);
            $mpdf->Output($filename, \Mpdf\Output\Destination::DOWNLOAD);
            exit;
        }

        if (class_exists('\Dompdf\Dompdf')) {
            $options = new \Dompdf\Options();
            $options->set('isHtml5ParserEnabled', true);
            $dompdf = new \Dompdf\Dompdf($options);
            $dompdf->setPaper('A4', 'landscape');
            $dompdf->loadHtml($html);
            $dompdf->render();
            $dompdf->stream($filename, ['Attachment' => true]);
            exit;
        }

        // Fallback — print dari browser
        $html = str_replace('</body>',
            '<script>
                var b=document.createElement("button");
                b.textContent="🖨️ Print / Simpan PDF";
                b.style="position:fixed;top:14px;right:14px;padding:10px 20px;background:#0d9488;color:#fff;border:none;border-radius:8px;font-size:13px;cursor:pointer;font-weight:700;z-index:9999";
                b.onclick=function(){window.print();};
                document.body.appendChild(b);
            </script>
            <style>@media print{button{display:none!important}}</style>
            </body>', $html);
        if (ob_get_level()) ob_end_clean();
        header('Content-Type: text/html; charset=utf-8');
        echo $html;
        exit;
    }

    /** Helper kelompok umur pemilih */
    private function _kelompok_umur($umur) {
        if ($umur >= 17 && $umur <= 21) return 'Pemula';
        if ($umur >= 22 && $umur <= 35) return 'Muda';
        if ($umur >= 36 && $umur <= 55) return 'Dewasa';
        if ($umur >= 56 && $umur <= 70) return 'Lansia';
        return 'Tua';
    }

    /** Helper ambil data iuran dengan filter */
    private function _get_iuran($status = null, $tahun = null) {
        $this->db->select('pi.*, w.nik, u.nama_lengkap, ji.nama_iuran');
        $this->db->from('pembayaran_iuran pi');
        $this->db->join('warga w', 'w.id = pi.warga_id');
        $this->db->join('users u', 'u.id = w.user_id');
        $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');
        if ($status) $this->db->where('pi.status', $status);
        if ($tahun)  $this->db->where('pi.periode_tahun', $tahun);
        $this->db->order_by('pi.periode_tahun', 'DESC');
        $this->db->order_by('pi.periode_bulan', 'DESC');
        return $this->db->get()->result();
    }
}