<?php
class Dashboard_model extends CI_Model {

    // === SUPERADMIN ===
    public function get_superadmin_stats() {
        $stats = [];

        $stats['total_unor'] = $this->db->count_all('unor');
        
        $this->db->where('unor_mandiri', 1);
        $stats['unor_mandiri'] = $this->db->count_all_results('unor');

        $this->db->where('unor_mandiri', 0);
        $stats['unor_non_mandiri'] = $this->db->count_all_results('unor');

        $stats['total_pegawai'] = $this->db->count_all('pegawai');

        $this->db->where('jenis_jabatan', '1');
        $stats['jabatan_struktural'] = $this->db->count_all_results('pegawai');

        $this->db->where('jenis_jabatan', '2');
        $stats['jabatan_fungsional'] = $this->db->count_all_results('pegawai');

        $this->db->select('u.unor_induk_nama, COUNT(p.id) as total');
        $this->db->from('pegawai p');
        $this->db->join('unor u', 'p.unor_id = u.unor_id', 'left');
        $this->db->where('u.unor_induk_nama IS NOT NULL');
        $this->db->group_by('u.unor_induk_nama');
        $this->db->order_by('total', 'DESC');
        $stats['pegawai_per_unor_induk'] = $this->db->get()->result();

        $sql = "
            SELECT 
                u.unor_induk_nama,
                dk.template,
                dk.status,
                COUNT(*) as total
            FROM dialog_kinerja dk
            JOIN pegawai p ON dk.pegawai_id = p.id_peg
            JOIN unor u ON p.unor_id = u.unor_id
            WHERE u.unor_induk_nama IS NOT NULL
            GROUP BY u.unor_induk_nama, dk.template, dk.status
            ORDER BY u.unor_induk_nama, dk.template, dk.status
        ";
        $stats['dialog_per_unor_induk'] = $this->db->query($sql)->result();

        return $stats;
    }

    // === ADMIN OPD ===
    public function get_admin_opd_stats($unor_id) {
        $stats = [];

        // ✅ MUAT Pegawai_model secara eksplisit
        $this->load->model('Pegawai_model');

        // ✅ GUNAKAN FUNGSI BARU: get_all_child_unor_ids()
        $unor_ids = $this->Pegawai_model->get_all_child_unor_ids($unor_id);

        if (empty($unor_ids)) {
            $stats['total_pegawai'] = 0;
            $stats['jabatan_struktural'] = 0;
            $stats['jabatan_fungsional'] = 0;
            $stats['dialog_per_tahapan'] = [];
            return $stats;
        }

        // Total Pegawai
        $this->db->where_in('unor_id', $unor_ids);
        $stats['total_pegawai'] = $this->db->count_all_results('pegawai');

        // Jabatan Struktural
        $this->db->where_in('unor_id', $unor_ids)->where('jenis_jabatan', '1');
        $stats['jabatan_struktural'] = $this->db->count_all_results('pegawai');

        // Jabatan Fungsional
        $this->db->where_in('unor_id', $unor_ids)->where('jenis_jabatan', '2');
        $stats['jabatan_fungsional'] = $this->db->count_all_results('pegawai');

        // Dialog per Tahapan
        $unor_ids_str = "'" . implode("','", $unor_ids) . "'";
        $sql = "
            SELECT 
                dk.template,
                dk.status,
                COUNT(*) as total
            FROM dialog_kinerja dk
            JOIN pegawai p ON dk.pegawai_id = p.id_peg
            WHERE p.unor_id IN ($unor_ids_str)
            GROUP BY dk.template, dk.status
            ORDER BY dk.template, FIELD(dk.status, 'draft', 'on_proses', 'selesai')
        ";
        $stats['dialog_per_tahapan'] = $this->db->query($sql)->result();

        return $stats;
    }

    // === USER ASN ===
    public function get_user_asn_stats($pegawai_id) {
        $stats = [];
        $stats['profil'] = $this->db->get_where('pegawai', ['id_peg' => $pegawai_id])->row();

        $user = $this->db->get_where('pegawai', ['id_peg' => $pegawai_id])->row();
        if ($user) {
            $unor_user = $this->db->get_where('unor', ['unor_id' => $user->unor_id])->row();
            if ($unor_user) {
                $sql_bawahan = "
                    SELECT p.*, 
                           COALESCE(dk.status, 'belum_mulai') as status_dialog
                    FROM pegawai p
                    LEFT JOIN unor u ON p.unor_id = u.unor_id
                    LEFT JOIN (
                        SELECT pegawai_id, status 
                        FROM dialog_kinerja 
                        WHERE template = 5
                    ) dk ON p.id_peg = dk.pegawai_id
                    WHERE u.unor_atasan_id = ?
                    AND p.id_peg != ?
                ";
                $stats['bawahan'] = $this->db->query($sql_bawahan, [$user->unor_id, $pegawai_id])->result();
            }
        }
        return $stats;
    }

    // === FUNGSI BARU UNTUK KLAIM PIMPINAN ===
    public function claim_as_pimpinan_with_level($pegawai_id, $level) {
        if (!in_array($level, ['top_leader', 'middle_leader', 'low_leader'])) {
            return false;
        }

        $exists = $this->db->where('pimpinan_id', $pegawai_id)->get('pimpinan_bawahan')->row();
        if ($exists) {
            $this->db->where('pimpinan_id', $pegawai_id)
                     ->update('pimpinan_bawahan', ['level' => $level]);
        } else {
            $this->db->insert('pimpinan_bawahan', [
                'pimpinan_id' => $pegawai_id,
                'bawahan_id' => $pegawai_id,
                'level' => $level
            ]);
        }
        return true;
    }

    public function unclaim_pimpinan($pegawai_id) {
        $this->db->where('pimpinan_id', $pegawai_id)->delete('pimpinan_bawahan');
    }

    public function is_pimpinan($pegawai_id) {
        return $this->db->where('pimpinan_id', $pegawai_id)
                        ->where('bawahan_id !=', $pegawai_id)
                        ->count_all_results('pimpinan_bawahan') > 0;
    }

    public function get_pimpinan_level($pegawai_id) {
        $row = $this->db->select('level')
                        ->where('pimpinan_id', $pegawai_id)
                        ->get('pimpinan_bawahan')
                        ->row();
        return $row ? $row->level : null;
    }

    public function add_bawahan($pimpinan_id, $nip_bawahan) {
        $pegawai = $this->db->select('id_peg')->get_where('pegawai', ['nip' => $nip_bawahan])->row();
        if (!$pegawai || $pegawai->id_peg == $pimpinan_id) return false;

        $this->db->where('pimpinan_id', $pimpinan_id);
        $this->db->where('bawahan_id', $pegawai->id_peg);
        if ($this->db->count_all_results('pimpinan_bawahan') > 0) return false;

        $this->db->insert('pimpinan_bawahan', [
            'pimpinan_id' => $pimpinan_id,
            'bawahan_id' => $pegawai->id_peg
        ]);
        return true;
    }

    public function remove_bawahan($pimpinan_id, $bawahan_id) {
        $this->db->where('pimpinan_id', $pimpinan_id);
        $this->db->where('bawahan_id', $bawahan_id);
        $this->db->delete('pimpinan_bawahan');
    }

    public function get_bawahan_list($pimpinan_id) {
        $sql = "
            SELECT 
                p.id_peg,
                p.nip,
                p.nama,
                p.jabatan,
                COALESCE(dk.status, 'belum_mulai') as status_dialog
            FROM pimpinan_bawahan pb
            JOIN pegawai p ON pb.bawahan_id = p.id_peg
            LEFT JOIN (
                SELECT pegawai_id, status 
                FROM dialog_kinerja 
                WHERE template = 5
            ) dk ON p.id_peg = dk.pegawai_id
            WHERE pb.pimpinan_id = ?
            AND pb.bawahan_id != pb.pimpinan_id
            ORDER BY p.nama
        ";
        return $this->db->query($sql, [$pimpinan_id])->result();
    }

    public function get_bawahan_for_select($pimpinan_id) {
        $sql = "
            SELECT p.id_peg, p.nama, p.nip
            FROM pimpinan_bawahan pb
            JOIN pegawai p ON pb.bawahan_id = p.id_peg
            WHERE pb.pimpinan_id = ?
            AND p.id_peg != ?
            ORDER BY p.nama
        ";
        return $this->db->query($sql, [$pimpinan_id, $pimpinan_id])->result();
    }
    
    public function tambah_bawahan($pimpinan_id, $nip_bawahan) {
        // 1. Cari data bawahan berdasarkan NIP
        $bawahan = $this->db->get_where('pegawai', ['nip' => $nip_bawahan])->row();
    
        if (!$bawahan) {
            return false; // NIP tidak ditemukan
        }
    
        // 2. Cek apakah bawahan ini sudah pernah ditambahkan oleh pimpinan ini
        $cek = $this->db->get_where('pimpinan_bawahan', [
            'pimpinan_id' => $pimpinan_id,
            'bawahan_id'  => $bawahan->id_peg // Sesuaikan field ID pegawai Anda
        ])->num_rows();
    
        if ($cek > 0) {
            return false; // Sudah ada di daftar
        }
    
        // 3. Ambil level pimpinan untuk dimasukkan ke tabel pimpinan_bawahan
        $level_pimpinan = $this->get_pimpinan_level($pimpinan_id);
    
        // 4. Siapkan data untuk insert
        $data = [
            'pimpinan_id'       => $pimpinan_id,
            'bawahan_id'        => $bawahan->id_peg,
            'level'             => $level_pimpinan,
            'created_at'        => date('Y-m-d H:i:s'),
            'intervensi_rhk'    => 0, // Default nilai awal
            'intervensi_akhlak' => 0  // Default nilai awal
        ];
    
        return $this->db->insert('pimpinan_bawahan', $data);
    }
    
    // Tambahkan juga fungsi hapus jika belum ada
    public function hapus_bawahan($pimpinan_id, $bawahan_id) {
        $this->db->where('pimpinan_id', $pimpinan_id);
        $this->db->where('bawahan_id', $bawahan_id);
        return $this->db->delete('pimpinan_bawahan');
    }
}