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

class KartuKeluarga extends CI_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->library('session');
        if (!$this->session->userdata('logged_in')) {
            redirect('auth');
        }
        $this->load->model('KartuKeluarga_model');
        $this->load->model('Warga_model');
        $this->load->model('User_model');
        $this->load->model('Pengaturan_model');
        $this->load->model('Master_pendidikan_model');
        $this->load->model('Master_pekerjaan_model');
        $this->load->library('form_validation');
    }

    public function index() {
        $data['title'] = 'Data Kartu Keluarga';
        $data['kk']    = $this->KartuKeluarga_model->get_all();
        $this->load->view('templates/header', $data);
        $this->load->view('templates/sidebar', $data);
        $this->load->view('kartu_keluarga/index', $data);
        $this->load->view('templates/footer');
    }

    public function detail($id) {
        $data['title']           = 'Detail Kartu Keluarga';
        $data['kk']              = $this->KartuKeluarga_model->get_by_id($id);
        $data['anggota']         = $this->KartuKeluarga_model->get_anggota_keluarga($id);
        $data['status_hubungan'] = $this->Warga_model->get_status_hubungan_options();
        $data['status_domisili'] = $this->Warga_model->get_status_domisili_options();
        $data['list_pendidikan'] = $this->Master_pendidikan_model->get_all(true);
        $data['list_pekerjaan']  = $this->Master_pekerjaan_model->get_all(true);

        if (!$data['kk']) {
            $this->session->set_flashdata('error', 'Data KK tidak ditemukan');
            redirect('kartu-keluarga');
        }
        $this->load->view('templates/header', $data);
        $this->load->view('templates/sidebar', $data);
        $this->load->view('kartu_keluarga/detail', $data);
        $this->load->view('templates/footer');
    }

    public function tambah() {
        $this->_check_admin();
        $data['title']           = 'Tambah Kartu Keluarga';
        $data['status_hubungan'] = $this->Warga_model->get_status_hubungan_options();
        $data['status_domisili'] = $this->Warga_model->get_status_domisili_options();
         $data['pengaturan']      = $this->Pengaturan_model->get();

        $this->form_validation->set_rules('no_kk', 'Nomor KK', 'required|is_unique[kartu_keluarga.no_kk]');
        $this->form_validation->set_rules('alamat', 'Alamat KK', 'required');

        if ($this->form_validation->run() == FALSE) {
            $this->load->view('templates/header', $data);
            $this->load->view('templates/sidebar', $data);
            $this->load->view('kartu_keluarga/tambah', $data);
            $this->load->view('templates/footer');
        } else {
            $data_kk = [
                'no_kk'     => $this->input->post('no_kk'),
                'alamat'    => $this->input->post('alamat'),
                'rt'        => $this->input->post('rt'),
                'rw'        => $this->input->post('rw'),
                'desa'      => $this->input->post('desa'),
                'kecamatan' => $this->input->post('kecamatan'),
                'kabupaten' => $this->input->post('kabupaten'),
                'provinsi'  => $this->input->post('provinsi'),
                'kode_pos'  => $this->input->post('kode_pos'),
            ];
            $kk_id = $this->KartuKeluarga_model->insert($data_kk);
            $no_kk = $this->input->post('no_kk');

            $anggota_list  = $this->input->post('anggota');
            $users_created = [];
            $users_failed  = [];

            if (!empty($anggota_list) && is_array($anggota_list)) {
                foreach ($anggota_list as $idx => $a) {
                    if (empty($a['nik']) || empty($a['nama_lengkap'])) continue;

                    $existing_warga = $this->Warga_model->get_by_nik($a['nik']);
                    if ($existing_warga) {
                        $users_failed[] = $a['nama_lengkap'] . ' (NIK sudah terdaftar)';
                        continue;
                    }

                    $data_warga = [
                        'kk_id'           => $kk_id,
                        'no_kk'           => $no_kk,
                        'nik'             => $a['nik'],
                        'nama_lengkap'    => $a['nama_lengkap'],
                        'jenis_kelamin'   => $a['jenis_kelamin']   ?? null,
                        'tanggal_lahir'   => !empty($a['tanggal_lahir']) ? $a['tanggal_lahir'] : null,
                        'no_hp'           => !empty($a['no_hp']) ? $a['no_hp'] : null,
                        'status_hubungan' => $a['status_hubungan'] ?? 'lainnya',
                        'status_domisili' => $a['status_domisili'] ?? 'tetap',
                        'status_meninggal' => 0,
                    ];
                    $warga_id = $this->Warga_model->insert($data_warga);

                    $role = $a['role'] ?? 'warga';
                    $allowed_roles = ['admin', 'ketua_rt', 'sekretaris', 'bendahara', 'warga'];
                    if (!in_array($role, $allowed_roles)) $role = 'warga';

                    $user_data = [
                        'username'     => $a['nik'],
                        'password'     => password_hash('password', PASSWORD_DEFAULT),
                        'nama_lengkap' => $a['nama_lengkap'],
                        'email'        => null,
                        'no_hp'        => !empty($a['no_hp']) ? $a['no_hp'] : null,
                        'role'         => $role,
                        'rt'           => $this->input->post('rt'),
                        'rw'           => $this->input->post('rw'),
                        'alamat'       => $this->input->post('alamat'),
                        'status'       => 'aktif',
                        'warga_id'     => $warga_id,
                    ];

                    if ($this->User_model->is_username_exists($a['nik'])) {
                        $users_failed[] = $a['nama_lengkap'] . ' (Username/NIK sudah ada di user)';
                    } else {
                        $user_id = $this->User_model->insert($user_data);
                        $this->Warga_model->update($warga_id, ['user_id' => $user_id]);
                        $users_created[] = ['nama' => $a['nama_lengkap'], 'nik' => $a['nik'], 'role' => $role];
                    }

                    if ($idx == 0) {
                        $this->KartuKeluarga_model->update($kk_id, ['kepala_keluarga_id' => $warga_id]);
                    }
                }
            }

            $msg = 'Kartu Keluarga berhasil disimpan.';
            if (!empty($users_created)) $msg .= ' ' . count($users_created) . ' akun user berhasil dibuat (Password: password).';
            if (!empty($users_failed))  $msg .= ' Gagal: ' . implode(', ', $users_failed);

            $this->User_model->log_activity($this->session->userdata('user_id'), 'create', 'Menambah Kartu Keluarga baru No: ' . $no_kk);
            $this->session->set_flashdata('success', $msg);
            redirect('kartu-keluarga/detail/' . $kk_id);
        }
    }

    // ════════════════════════════════════════════════════════════════
    // TAMBAH ANGGOTA — dengan no_hp
    // ════════════════════════════════════════════════════════════════
    public function tambah_anggota($kk_id) {
        $this->_check_admin();
        $kk = $this->KartuKeluarga_model->get_by_id($kk_id);
        if (!$kk) {
            $this->session->set_flashdata('error', 'Data KK tidak ditemukan');
            redirect('kartu-keluarga');
        }

        $this->form_validation->set_rules('nik',             'NIK',             'required|is_unique[warga.nik]');
        $this->form_validation->set_rules('nama_lengkap',    'Nama Lengkap',    'required');
        $this->form_validation->set_rules('status_hubungan', 'Status Hubungan', 'required');

        if ($this->form_validation->run() == FALSE) {
            $this->session->set_flashdata('error', validation_errors());
            redirect('kartu-keluarga/detail/' . $kk_id);
        } else {
            $no_hp = $this->input->post('no_hp') ?: null;

            $data_warga = [
                'kk_id'               => $kk_id,
                'no_kk'               => $kk->no_kk,
                'nik'                 => $this->input->post('nik'),
                'nama_lengkap'        => $this->input->post('nama_lengkap'),
                'jenis_kelamin'       => $this->input->post('jenis_kelamin'),
                'tempat_lahir'        => $this->input->post('tempat_lahir'),
                'tanggal_lahir'       => $this->input->post('tanggal_lahir'),
                'agama'               => $this->input->post('agama'),
                'status_perkawinan'   => $this->input->post('status_perkawinan'),
                'pendidikan'          => $this->input->post('pendidikan') ?: null,
                'pekerjaan'           => $this->input->post('pekerjaan') ?: null,
                'no_hp'               => $no_hp,
                'status_hubungan'     => $this->input->post('status_hubungan'),
                'status_domisili'     => $this->input->post('status_domisili') ?: 'tetap',
                'keterangan_domisili' => $this->input->post('keterangan_domisili'),
                'status_meninggal'    => 0,
            ];
            $warga_id = $this->Warga_model->insert($data_warga);

            $role = $this->input->post('role') ?? 'warga';
            $allowed_roles = ['admin', 'ketua_rt', 'sekretaris', 'bendahara', 'warga'];
            if (!in_array($role, $allowed_roles)) $role = 'warga';

            $user_data = [
                'username'     => $this->input->post('nik'),
                'password'     => password_hash('password', PASSWORD_DEFAULT),
                'nama_lengkap' => $this->input->post('nama_lengkap'),
                'no_hp'        => $no_hp,                  // ← BARU
                'role'         => $role,
                'rt'           => $kk->rt,
                'rw'           => $kk->rw,
                'alamat'       => $kk->alamat,
                'status'       => 'aktif',
                'warga_id'     => $warga_id,
            ];

            if (!$this->User_model->is_username_exists($this->input->post('nik'))) {
                $user_id = $this->User_model->insert($user_data);
                $this->Warga_model->update($warga_id, ['user_id' => $user_id]);
            }

            if ($this->input->post('status_hubungan') == 'kepala_keluarga' && !$kk->kepala_keluarga_id) {
                $this->KartuKeluarga_model->update($kk_id, ['kepala_keluarga_id' => $warga_id]);
            }

            $this->User_model->log_activity($this->session->userdata('user_id'), 'create', 'Menambah anggota keluarga baru: ' . $this->input->post('nama_lengkap') . ' ke KK No: ' . $kk->no_kk);
            $this->session->set_flashdata('success', 'Anggota berhasil ditambahkan dan akun user dibuat (Password: password)');
            redirect('kartu-keluarga/detail/' . $kk_id);
        }
    }

    // ════════════════════════════════════════════════════════════════
    // EDIT ANGGOTA — dengan no_hp
    // ════════════════════════════════════════════════════════════════
    public function edit_anggota($warga_id) {
        $this->_check_admin();
        $warga = $this->Warga_model->get_by_id($warga_id);
        if (!$warga) {
            $this->session->set_flashdata('error', 'Data warga tidak ditemukan');
            redirect('kartu-keluarga');
        }

        $kk_id = $warga->kk_id;
        $this->form_validation->set_rules('nama_lengkap',    'Nama Lengkap',    'required');
        $this->form_validation->set_rules('status_hubungan', 'Status Hubungan', 'required');

        if ($this->form_validation->run() == FALSE) {
            $this->session->set_flashdata('error', validation_errors());
            redirect('kartu-keluarga/detail/' . $kk_id);
        } else {
            $no_hp = $this->input->post('no_hp') ?: null;

            $data_warga = [
                'nama_lengkap'        => $this->input->post('nama_lengkap'),
                'jenis_kelamin'       => $this->input->post('jenis_kelamin'),
                'tempat_lahir'        => $this->input->post('tempat_lahir'),
                'tanggal_lahir'       => $this->input->post('tanggal_lahir'),
                'agama'               => $this->input->post('agama'),
                'status_perkawinan'   => $this->input->post('status_perkawinan'),
                'pendidikan'          => $this->input->post('pendidikan') ?: null,
                'pekerjaan'           => $this->input->post('pekerjaan') ?: null,
                'no_hp'               => $no_hp,
                'status_hubungan'     => $this->input->post('status_hubungan'),
                'status_domisili'     => $this->input->post('status_domisili') ?: 'tetap',
                'keterangan_domisili' => $this->input->post('keterangan_domisili'),
            ];
            $this->Warga_model->update($warga_id, $data_warga);

            // Update role & no_hp di tabel users jika ada
            $warga = $this->Warga_model->get_by_id($warga_id);
            if ($warga->user_id) {
                $user_update = ['no_hp' => $no_hp];        // ← BARU: sinkron ke users
                if ($this->input->post('role')) {
                    $user_update['role'] = $this->input->post('role');
                }
                $this->User_model->update($warga->user_id, $user_update);
            }

            $this->User_model->log_activity($this->session->userdata('user_id'), 'update', 'Mengubah data anggota keluarga: ' . $warga->nama_lengkap . ' di KK No: ' . $warga->no_kk);
            $this->session->set_flashdata('success', 'Data anggota berhasil diupdate');
            redirect('kartu-keluarga/detail/' . $kk_id);
        }
    }

    // ════════════════════════════════════════════════════════════════
    // TANDAI WARGA MENINGGAL
    // ════════════════════════════════════════════════════════════════
    public function tandai_meninggal($warga_id) {
        $this->_check_admin();

        $warga = $this->Warga_model->get_by_id($warga_id);
        if (!$warga) {
            $this->session->set_flashdata('error', 'Data warga tidak ditemukan');
            redirect('kartu-keluarga');
        }

        $tanggal    = $this->input->post('tanggal_meninggal');
        $keterangan = $this->input->post('keterangan_meninggal');

        if (empty($tanggal)) {
            $this->session->set_flashdata('error', 'Tanggal meninggal harus diisi');
            redirect('kartu-keluarga/detail/' . $warga->kk_id);
        }

        $this->Warga_model->update($warga_id, [
            'status_meninggal'     => 1,
            'tanggal_meninggal'    => $tanggal,
            'keterangan_meninggal' => $keterangan ?: null,
        ]);

        if (!empty($warga->user_id)) {
            $this->User_model->update($warga->user_id, ['status' => 'nonaktif']);
        }

        $this->User_model->log_activity($this->session->userdata('user_id'), 'update', 'Menandai warga meninggal: ' . $warga->nama_lengkap . ' di KK No: ' . $warga->no_kk);
        $this->session->set_flashdata('success', 'Warga atas nama ' . $warga->nama_lengkap . ' telah ditandai meninggal dunia. Data tetap tersimpan sebagai catatan KK.');
        redirect('kartu-keluarga/detail/' . $warga->kk_id);
    }

    // ════════════════════════════════════════════════════════════════
    // HAPUS WARGA MENINGGAL DARI KK
    // ════════════════════════════════════════════════════════════════
    public function hapus_warga_meninggal($warga_id) {
        $this->_check_admin();

        $warga = $this->Warga_model->get_by_id($warga_id);
        if (!$warga) {
            $this->session->set_flashdata('error', 'Data tidak ditemukan');
            redirect('kartu-keluarga');
        }

        if (empty($warga->status_meninggal) || $warga->status_meninggal != 1) {
            $this->session->set_flashdata('error', 'Warga ini belum ditandai meninggal. Gunakan tombol tandai meninggal terlebih dahulu.');
            redirect('kartu-keluarga/detail/' . $warga->kk_id);
        }

        if ($warga->status_hubungan == 'kepala_keluarga') {
            $this->session->set_flashdata('error', 'Kepala Keluarga tidak bisa dihapus langsung. Hapus atau edit KK terlebih dahulu.');
            redirect('kartu-keluarga/detail/' . $warga->kk_id);
        }

        $kk_id = $warga->kk_id;
        $nama  = $warga->nama_lengkap;

        if (!empty($warga->user_id)) {
            $this->User_model->delete($warga->user_id);
        }

        $this->Warga_model->delete($warga_id);

        $this->User_model->log_activity($this->session->userdata('user_id'), 'delete', 'Menghapus data almarhum: ' . $nama . ' dari KK No: ' . $warga->no_kk);
        $this->session->set_flashdata('success', 'Data almarhum/almarhumah ' . $nama . ' berhasil dihapus dari KK.');
        redirect('kartu-keluarga/detail/' . $kk_id);
    }

    public function hapus_anggota($warga_id) {
        $this->_check_admin();
        $warga = $this->Warga_model->get_by_id($warga_id);
        if (!$warga) {
            $this->session->set_flashdata('error', 'Data tidak ditemukan');
            redirect('kartu-keluarga');
        }
        if ($warga->status_hubungan == 'kepala_keluarga') {
            $this->session->set_flashdata('error', 'Kepala Keluarga tidak bisa dihapus. Hapus KK terlebih dahulu.');
            redirect('kartu-keluarga/detail/' . $warga->kk_id);
        }

        if ($warga->user_id) {
            $this->User_model->delete($warga->user_id);
        }

        $kk_id = $warga->kk_id;
        $this->Warga_model->delete($warga_id);
        $this->User_model->log_activity($this->session->userdata('user_id'), 'delete', 'Menghapus anggota keluarga: ' . $warga->nama_lengkap . ' dari KK No: ' . $warga->no_kk);
        $this->session->set_flashdata('success', 'Anggota dan akun user berhasil dihapus');
        redirect('kartu-keluarga/detail/' . $kk_id);
    }

    public function edit($id) {
        $this->_check_admin();
        $data['title'] = 'Edit Kartu Keluarga';
        $data['kk']    = $this->KartuKeluarga_model->get_by_id($id);

        if (!$data['kk']) {
            $this->session->set_flashdata('error', 'Data KK tidak ditemukan');
            redirect('kartu-keluarga');
        }
        $this->form_validation->set_rules('alamat', 'Alamat', 'required');

        if ($this->form_validation->run() == FALSE) {
            $this->load->view('templates/header', $data);
            $this->load->view('templates/sidebar', $data);
            $this->load->view('kartu_keluarga/edit', $data);
            $this->load->view('templates/footer');
        } else {
            $this->KartuKeluarga_model->update($id, [
                'alamat'    => $this->input->post('alamat'),
                'rt'        => $this->input->post('rt'),
                'rw'        => $this->input->post('rw'),
                'desa'      => $this->input->post('desa'),
                'kecamatan' => $this->input->post('kecamatan'),
                'kabupaten' => $this->input->post('kabupaten'),
                'provinsi'  => $this->input->post('provinsi'),
                'kode_pos'  => $this->input->post('kode_pos'),
            ]);
            $this->User_model->log_activity($this->session->userdata('user_id'), 'update', 'Mengubah data Kartu Keluarga No: ' . $data['kk']->no_kk);
            $this->session->set_flashdata('success', 'Kartu Keluarga berhasil diupdate');
            redirect('kartu-keluarga/detail/' . $id);
        }
    }

    public function hapus($id) {
        $this->_check_admin();
        $anggota = $this->KartuKeluarga_model->get_anggota_keluarga($id);
        foreach ($anggota as $w) {
            if ($w->user_id) {
                $this->User_model->delete($w->user_id);
            }
        }
        $kk = $this->KartuKeluarga_model->get_by_id($id);
        $this->User_model->log_activity($this->session->userdata('user_id'), 'delete', 'Menghapus Kartu Keluarga No: ' . ($kk->no_kk ?? $id));
        $this->KartuKeluarga_model->delete($id);
        $this->session->set_flashdata('success', 'Kartu Keluarga dan semua akun user berhasil dihapus');
        redirect('kartu-keluarga');
    }

    public function ajax_cari_kk() {
        $keyword = $this->input->get('term') ?: $this->input->get('q');
        $result  = $this->KartuKeluarga_model->search_by_no_kk($keyword);
        $data = [];
        foreach ($result as $row) {
            $data[] = [
                'label'  => $row->no_kk . ' — ' . ($row->nama_kepala_keluarga ?: 'Belum ada kepala KK'),
                'value'  => $row->no_kk,
                'id'     => $row->id,
                'alamat' => $row->alamat,
            ];
        }
        echo json_encode($data);
    }

    public function ajax_cari_warga() {
        $keyword = $this->input->get('q');
        $result  = $this->Warga_model->search_warga_tanpa_kk($keyword);
        $data = [];
        foreach ($result as $row) {
            $data[] = [
                'id'            => $row->id,
                'nik'           => $row->nik,
                'nama_lengkap'  => $row->nama_lengkap,
                'jenis_kelamin' => $row->jenis_kelamin,
            ];
        }
        echo json_encode($data);
    }

    public function upload_kk($kk_id) {
        $this->_check_admin();
        $kk = $this->KartuKeluarga_model->get_by_id($kk_id);
        if (!$kk) {
            $this->session->set_flashdata('error', 'Data tidak ditemukan');
            redirect('kartu-keluarga');
        }
        
        $config['upload_path']   = './assets/uploads/kk/';
        $config['allowed_types'] = 'jpg|jpeg|png|pdf';
        $config['max_size']      = 5120; // 5MB
        $config['file_name']     = 'KK_' . $kk_id . '_' . time();

        $this->load->library('upload', $config);
        $this->upload->initialize($config);

        if ($this->upload->do_upload('scan_kk')) {
            $uploadData = $this->upload->data();
            $file_name = $uploadData['file_name'];
            $this->KartuKeluarga_model->update($kk_id, ['scan_kk' => $file_name]);
            $this->User_model->log_activity($this->session->userdata('user_id'), 'update', 'Upload scan KK: ' . $kk->no_kk);
            $this->session->set_flashdata('success', 'Scan KK berhasil diupload');
        } else {
            $this->session->set_flashdata('error', $this->upload->display_errors('',''));
        }
        redirect('kartu-keluarga/detail/' . $kk_id);
    }

    public function upload_ktp($warga_id) {
        $this->_check_admin();
        $warga = $this->Warga_model->get_by_id($warga_id);
        if (!$warga) {
            $this->session->set_flashdata('error', 'Data tidak ditemukan');
            redirect('kartu-keluarga');
        }
        
        $config['upload_path']   = './assets/uploads/ktp/';
        $config['allowed_types'] = 'jpg|jpeg|png|pdf';
        $config['max_size']      = 5120; // 5MB
        $config['file_name']     = 'KTP_' . $warga_id . '_' . time();

        $this->load->library('upload', $config);
        $this->upload->initialize($config);

        if ($this->upload->do_upload('foto_ktp')) {
            $uploadData = $this->upload->data();
            $file_name = $uploadData['file_name'];
            $this->Warga_model->update($warga_id, ['foto_ktp' => $file_name]);
            $this->User_model->log_activity($this->session->userdata('user_id'), 'update', 'Upload KTP warga: ' . $warga->nama_lengkap);
            $this->session->set_flashdata('success', 'Foto KTP berhasil diupload');
        } else {
            $this->session->set_flashdata('error', $this->upload->display_errors('',''));
        }
        redirect('kartu-keluarga/detail/' . $warga->kk_id);
    }

    public function hapus_dokumen_kk($kk_id) {
        $this->_check_admin();
        $kk = $this->KartuKeluarga_model->get_by_id($kk_id);
        if ($kk && !empty($kk->scan_kk)) {
            $path = FCPATH . 'assets/uploads/kk/' . $kk->scan_kk;
            if (file_exists($path)) {
                unlink($path);
            }
            $this->KartuKeluarga_model->update($kk_id, ['scan_kk' => NULL]);
            $this->User_model->log_activity($this->session->userdata('user_id'), 'delete', 'Menghapus dokumen KK: ' . $kk->no_kk);
            $this->session->set_flashdata('success', 'Dokumen KK berhasil dihapus');
        }
        redirect('kartu-keluarga/detail/' . $kk_id);
    }

    public function hapus_dokumen_ktp($warga_id) {
        $this->_check_admin();
        $warga = $this->Warga_model->get_by_id($warga_id);
        if ($warga && !empty($warga->foto_ktp)) {
            $path = FCPATH . 'assets/uploads/ktp/' . $warga->foto_ktp;
            if (file_exists($path)) {
                unlink($path);
            }
            $this->Warga_model->update($warga_id, ['foto_ktp' => NULL]);
            $this->User_model->log_activity($this->session->userdata('user_id'), 'delete', 'Menghapus foto KTP warga: ' . $warga->nama_lengkap);
            $this->session->set_flashdata('success', 'Foto KTP berhasil dihapus');
        }
        redirect('kartu-keluarga/detail/' . $warga->kk_id);
    }

    public function ajax_get_warga($id) {
        $warga = $this->Warga_model->get_by_id($id);
        echo json_encode($warga);
    }

    public function download_template() {
        $this->_check_admin();

        $possible_paths = [
            FCPATH . 'assets/template/template_import_warga.xlsx',
            APPPATH . '../assets/template/template_import_warga.xlsx',
            realpath(APPPATH . '../../assets/template/template_import_warga.xlsx'),
        ];

        $file = null;
        foreach ($possible_paths as $path) {
            if ($path && file_exists($path)) { $file = $path; break; }
        }

        if (!$file) {
            $this->session->set_flashdata('error', 'File template tidak ditemukan. Pastikan file ada di folder: assets/template/template_import_warga.xlsx');
            redirect('kartu-keluarga');
            return;
        }

        if (ob_get_level()) ob_end_clean();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="template_import_warga.xlsx"');
        header('Content-Length: ' . filesize($file));
        header('Cache-Control: max-age=0');
        header('Pragma: public');
        header('Expires: 0');
        readfile($file);
        exit;
    }

    public function proses_import_excel() {
        $this->_check_admin();

        if (!isset($_FILES['file_excel']) || $_FILES['file_excel']['error'] !== UPLOAD_ERR_OK) {
            $upload_errors = [
                UPLOAD_ERR_INI_SIZE   => 'File melebihi batas upload_max_filesize di php.ini',
                UPLOAD_ERR_FORM_SIZE  => 'File melebihi batas MAX_FILE_SIZE di form',
                UPLOAD_ERR_PARTIAL    => 'File hanya terupload sebagian',
                UPLOAD_ERR_NO_FILE    => 'Tidak ada file yang dipilih',
                UPLOAD_ERR_NO_TMP_DIR => 'Folder temporary tidak ditemukan',
                UPLOAD_ERR_CANT_WRITE => 'Gagal menulis file ke disk',
                UPLOAD_ERR_EXTENSION  => 'Upload dihentikan oleh extension PHP',
            ];
            $err_code = isset($_FILES['file_excel']) ? $_FILES['file_excel']['error'] : UPLOAD_ERR_NO_FILE;
            $this->session->set_flashdata('error', $upload_errors[$err_code] ?? 'Gagal upload file');
            redirect('kartu-keluarga');
            return;
        }

        $file_tmp  = $_FILES['file_excel']['tmp_name'];
        $file_name = $_FILES['file_excel']['name'];
        $ext       = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));

        if (!in_array($ext, ['xlsx', 'xls'])) {
            $this->session->set_flashdata('error', 'Format file tidak valid. Gunakan .xlsx atau .xls');
            redirect('kartu-keluarga');
            return;
        }

        $autoload_paths = [
            FCPATH . 'vendor/autoload.php',
            APPPATH . 'vendor/autoload.php',
            realpath(APPPATH . '../../vendor/autoload.php'),
            dirname(APPPATH) . '/vendor/autoload.php',
        ];

        $autoload_loaded = false;
        foreach ($autoload_paths as $path) {
            if ($path && file_exists($path)) { require_once $path; $autoload_loaded = true; break; }
        }

        if (!$autoload_loaded) {
            $this->session->set_flashdata('error', 'Library PhpSpreadsheet tidak ditemukan. Jalankan: composer require phpoffice/phpspreadsheet');
            redirect('kartu-keluarga');
            return;
        }

        if (!class_exists('\PhpOffice\PhpSpreadsheet\IOFactory')) {
            $this->session->set_flashdata('error', 'Class PhpSpreadsheet tidak ditemukan. Pastikan composer install sudah dijalankan.');
            redirect('kartu-keluarga');
            return;
        }

        try {
            // $reader      = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($file_tmp);
            // $reader->setReadDataOnly(true);
            // $spreadsheet = $reader->load($file_tmp);

            $oldLevel = error_reporting();
error_reporting($oldLevel & ~E_DEPRECATED);
$reader      = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($file_tmp);
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($file_tmp);
error_reporting($oldLevel);
            $sheet       = $spreadsheet->getSheetByName('Import Warga') ?? $spreadsheet->getActiveSheet();
            $highestRow  = $sheet->getHighestRow();

            $berhasil          = 0;
            $dilewati          = 0;
            $errors            = [];
            $kk_kepala_tracker = [];

            for ($row = 5; $row <= $highestRow; $row++) {
                $nik          = trim((string) $sheet->getCell('B' . $row)->getValue());
                $nama_lengkap = trim((string) $sheet->getCell('C' . $row)->getValue());
                $no_kk        = trim((string) $sheet->getCell('D' . $row)->getValue());

                if ($nik === '' || $nama_lengkap === '' || $no_kk === '') continue;
                if (!ctype_digit($nik)) continue;
                if (strlen($nik) !== 16) {
                    $errors[] = "Baris {$row}: NIK '{$nik}' harus 16 digit (saat ini " . strlen($nik) . " digit)";
                    continue;
                }

                $cek_nik = $this->db->get_where('warga', ['nik' => $nik])->row();
                if ($cek_nik) { $dilewati++; continue; }

                $tempat_lahir        = trim((string) $sheet->getCell('E' . $row)->getValue());
                $tanggal_lahir_raw   = $sheet->getCell('F' . $row)->getValue();
                $jenis_kelamin       = strtoupper(trim((string) $sheet->getCell('G' . $row)->getValue()));
                $status_perkawinan   = strtolower(trim((string) $sheet->getCell('H' . $row)->getValue()));
                $pekerjaan           = trim((string) $sheet->getCell('I' . $row)->getValue());
                $agama               = trim((string) $sheet->getCell('J' . $row)->getValue());
                $status_hubungan     = strtolower(trim((string) $sheet->getCell('K' . $row)->getValue()));
                $status_domisili     = strtolower(trim((string) $sheet->getCell('L' . $row)->getValue()));
                $keterangan_domisili = trim((string) $sheet->getCell('M' . $row)->getValue());
                $username            = trim((string) $sheet->getCell('N' . $row)->getValue());
                $email               = trim((string) $sheet->getCell('O' . $row)->getValue());
                $no_hp               = trim((string) $sheet->getCell('P' . $row)->getValue());

                $tanggal_lahir = null;
                if ($tanggal_lahir_raw !== null && $tanggal_lahir_raw !== '') {
                    if (is_numeric($tanggal_lahir_raw)) {
                        try {
                            $tanggal_lahir = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($tanggal_lahir_raw)->format('Y-m-d');
                        } catch (Exception $e) { $tanggal_lahir = null; }
                    } else {
                        $ts = strtotime((string) $tanggal_lahir_raw);
                        $tanggal_lahir = ($ts && $ts > 0) ? date('Y-m-d', $ts) : null;
                    }
                }

                $valid_jk       = ['L', 'P'];
                $valid_kawin    = ['belum_kawin', 'kawin', 'cerai_hidup', 'cerai_mati'];
                $valid_hubungan = ['kepala_keluarga','istri','anak','cucu','orang_tua','mertua','famili_lain','pembantu','lainnya'];
                $valid_domisili = ['tetap','kost','kontrak','pendatang','sementara'];
                $valid_agama    = ['Islam','Kristen','Katolik','Hindu','Buddha','Konghucu'];

                if ($jenis_kelamin !== '' && !in_array($jenis_kelamin, $valid_jk)) {
                    $errors[] = "Baris {$row}: Jenis kelamin '{$jenis_kelamin}' tidak valid. Gunakan L atau P";
                    continue;
                }

                if (!in_array($status_hubungan, $valid_hubungan)) $status_hubungan = 'lainnya';
                if (!in_array($status_domisili, $valid_domisili)) $status_domisili = 'tetap';
                $status_kawin = in_array($status_perkawinan, $valid_kawin) ? $status_perkawinan : null;
                $agama_final  = in_array($agama, $valid_agama) ? $agama : null;

                $kk    = $this->db->get_where('kartu_keluarga', ['no_kk' => $no_kk])->row();
                $kk_id = null;
                if ($kk) {
                    $kk_id = $kk->id;
                } else {
                    $this->db->insert('kartu_keluarga', ['no_kk' => $no_kk, 'created_at' => date('Y-m-d H:i:s')]);
                    $kk_id = $this->db->insert_id();
                }

                $user_id = null;
                if ($username !== '') {
                    $cek_user = $this->db->get_where('users', ['username' => $username])->row();
                    if (!$cek_user) {
                        $this->db->insert('users', [
                            'username'     => $username,
                            'password'     => password_hash($nik, PASSWORD_DEFAULT),
                            'nama_lengkap' => $nama_lengkap,
                            'email'        => $email  !== '' ? $email  : null,
                            'no_hp'        => $no_hp  !== '' ? $no_hp  : null,
                            'role'         => 'warga',
                            'status'       => 'aktif',
                            'created_at'   => date('Y-m-d H:i:s'),
                            'updated_at'   => date('Y-m-d H:i:s'),
                        ]);
                        $user_id = $this->db->insert_id();
                    } else {
                        $user_id = $cek_user->id;
                    }
                }

                $this->db->insert('warga', [
                    'user_id'             => $user_id,
                    'kk_id'               => $kk_id,
                    'nik'                 => $nik,
                    'nama_lengkap'        => $nama_lengkap,
                    'no_kk'               => $no_kk,
                    'tempat_lahir'        => $tempat_lahir        !== '' ? $tempat_lahir        : null,
                    'tanggal_lahir'       => $tanggal_lahir,
                    'jenis_kelamin'       => $jenis_kelamin       !== '' ? $jenis_kelamin       : null,
                    'status_perkawinan'   => $status_kawin,
                    'pekerjaan'           => $pekerjaan           !== '' ? $pekerjaan           : null,
                    'no_hp'               => $no_hp               !== '' ? $no_hp               : null,
                    'agama'               => $agama_final,
                    'status_hubungan'     => $status_hubungan,
                    'status_domisili'     => $status_domisili,
                    'keterangan_domisili' => $keterangan_domisili !== '' ? $keterangan_domisili : null,
                    'status_meninggal'    => 0,
                    'created_at'          => date('Y-m-d H:i:s'),
                ]);
                $warga_id = $this->db->insert_id();

                if ($user_id && $warga_id) {
                    $this->db->update('users', ['warga_id' => $warga_id], ['id' => $user_id]);
                }

                if ($status_hubungan === 'kepala_keluarga') {
                    $kk_kepala_tracker[$no_kk] = ['kk_id' => $kk_id, 'kepala_warga_id' => $warga_id];
                }

                $berhasil++;
            }

            if (!empty($kk_kepala_tracker)) {
                foreach ($kk_kepala_tracker as $no_kk => $d) {
                    $this->db->update('kartu_keluarga', ['kepala_keluarga_id' => $d['kepala_warga_id']], ['id' => $d['kk_id']]);
                }
            }

        } catch (Exception $e) {
            $this->session->set_flashdata('error', 'Gagal membaca file Excel: ' . $e->getMessage());
            redirect('kartu-keluarga');
            return;
        }

        $this->session->set_flashdata('import_result', [
            'berhasil' => $berhasil,
            'dilewati' => $dilewati,
            'errors'   => $errors,
        ]);
        redirect('kartu-keluarga');
    }

    private function _check_admin() {
        $role = $this->session->userdata('role');
        if (!in_array($role, ['admin', 'rt', 'ketua_rt'])) {
            $this->session->set_flashdata('error', 'Akses ditolak');
            redirect('kartu-keluarga');
        }
    }
}