<?php

namespace App\Models\Dashboard;

use App\Models\BaseModel;

class DashcabangModel extends BaseModel
{
    public function countKegiatanPromosi($tahun)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('promosi_main');
        
        // Hitung kegiatan promosi berdasarkan tahun dari tgl_1
        $builder->where('YEAR(tgl_1)', $tahun);
        
        // Filter berdasarkan id_kantor user yang login
        $user_id = session()->get('user')['id'] ?? null;
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            if ($user_data && $user_data->id_kantor) {
                $builder->where('id_kantor', $user_data->id_kantor);
            }
        }
        
        return $builder->countAllResults();
    }

    public function countCalonNasabah($tahun)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('nasabah_calon nc');
        $builder->join('promosi_main pm', 'pm.id = nc.id_kegiatan');
        
        // Filter berdasarkan tahun dari tabel promosi_main
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        
        // Filter berdasarkan id_kantor user yang login
        $user_id = session()->get('user')['id'] ?? null;
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            if ($user_data && $user_data->id_kantor) {
                $builder->where('pm.id_kantor', $user_data->id_kantor);
            }
        }
        
        return $builder->countAllResults();
    }

    public function countClosingNasabah($tahun)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('nasabah_calon nc');
        $builder->join('promosi_main pm', 'pm.id = nc.id_kegiatan');
        
        // Filter berdasarkan tahun dari tabel promosi_main
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        
        // Filter hanya yang closing = 1
        $builder->where('nc.closing', 1);
        
        // Filter berdasarkan id_kantor user yang login
        $user_id = session()->get('user')['id'] ?? null;
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            if ($user_data && $user_data->id_kantor) {
                $builder->where('pm.id_kantor', $user_data->id_kantor);
            }
        }
        
        return $builder->countAllResults();
    }

    public function getPersentaseKonversi($tahun)
    {
        $total_calon = $this->countCalonNasabah($tahun);
        $total_closing = $this->countClosingNasabah($tahun);
        
        if ($total_calon == 0) {
            return 0;
        }
        
        return round(($total_closing / $total_calon) * 100, 1);
    }

    public function getDataBulanan($tahun)
    {
        // Get user id_kantor
        $user_id = session()->get('user')['id'] ?? null;
        $user_kantor = null;
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            $user_kantor = $user_data->id_kantor ?? null;
        }

        $db = \Config\Database::connect();
        
        // Initialize data array untuk 12 bulan
        $data_bulanan = [];
        for ($bulan = 1; $bulan <= 12; $bulan++) {
            $data_bulanan[$bulan] = [
                'bulan' => $bulan,
                'kegiatan_promosi' => 0,
                'calon_nasabah' => 0,
                'closing_nasabah' => 0
            ];
        }

        // Query kegiatan promosi per bulan
        $builder = $db->table('promosi_main');
        $builder->select('MONTH(tgl_1) as bulan, COUNT(*) as jumlah');
        $builder->where('YEAR(tgl_1)', $tahun);
        if ($user_kantor) {
            $builder->where('id_kantor', $user_kantor);
        }
        $builder->groupBy('MONTH(tgl_1)');
        $kegiatan = $builder->get()->getResultArray();

        foreach ($kegiatan as $row) {
            $data_bulanan[$row['bulan']]['kegiatan_promosi'] = (int)$row['jumlah'];
        }

        // Query calon nasabah per bulan
        $builder = $db->table('nasabah_calon nc');
        $builder->select('MONTH(pm.tgl_1) as bulan, COUNT(*) as jumlah');
        $builder->join('promosi_main pm', 'pm.id = nc.id_kegiatan');
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        if ($user_kantor) {
            $builder->where('pm.id_kantor', $user_kantor);
        }
        $builder->groupBy('MONTH(pm.tgl_1)');
        $calon = $builder->get()->getResultArray();

        foreach ($calon as $row) {
            $data_bulanan[$row['bulan']]['calon_nasabah'] = (int)$row['jumlah'];
        }

        // Query closing nasabah per bulan
        $builder = $db->table('nasabah_calon nc');
        $builder->select('MONTH(pm.tgl_1) as bulan, COUNT(*) as jumlah');
        $builder->join('promosi_main pm', 'pm.id = nc.id_kegiatan');
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        $builder->where('nc.closing', 1);
        if ($user_kantor) {
            $builder->where('pm.id_kantor', $user_kantor);
        }
        $builder->groupBy('MONTH(pm.tgl_1)');
        $closing = $builder->get()->getResultArray();

        foreach ($closing as $row) {
            $data_bulanan[$row['bulan']]['closing_nasabah'] = (int)$row['jumlah'];
        }

        return array_values($data_bulanan);
    }

    public function getDataJenisPromosi($tahun)
    {
        // Get user id_kantor
        $user_id = session()->get('user')['id'] ?? null;
        $user_kantor = null;
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            $user_kantor = $user_data->id_kantor ?? null;
        }

        $db = \Config\Database::connect();
        
        // Query untuk mendapatkan tingkat konversi per jenis promosi
        // Mulai dari promosi_main, lalu join ke nasabah_calon untuk hitung konversi
        $builder = $db->table('promosi_main pm');
        $builder->select('
            pp.nama_kegiatan as jenis_promosi,
            COUNT(DISTINCT pm.id) as total_kegiatan,
            COUNT(nc.id) as total_calon,
            SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) as total_closing,
            CASE 
                WHEN COUNT(nc.id) > 0 THEN ROUND((SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) / COUNT(nc.id)) * 100, 1)
                ELSE 0 
            END as persentase_konversi
        ');
        
        $builder->join('pdk_promo pp', 'pp.id = pm.id_jenis');
        $builder->join('nasabah_calon nc', 'nc.id_kegiatan = pm.id', 'left');
        
        // Filter berdasarkan tahun
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        
        // Filter berdasarkan id_kantor user yang login
        if ($user_kantor) {
            $builder->where('pm.id_kantor', $user_kantor);
        }
        
        $builder->groupBy('pm.id_jenis, pp.nama_kegiatan');
        $builder->orderBy('persentase_konversi', 'DESC');
        
        $result = $builder->get()->getResultArray();
        
        return $result;
    }

    public function getDataJenisPromosiPieChart($tahun)
    {
        // Get user id_kantor
        $user_id = session()->get('user')['id'] ?? null;
        $user_kantor = null;
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            $user_kantor = $user_data->id_kantor ?? null;
        }

        $db = \Config\Database::connect();
        
        // Query untuk pie chart berdasarkan proporsi nasabah closing per jenis promosi
        $builder = $db->table('promosi_main pm');
        $builder->select('
            pp.nama_kegiatan as jenis_promosi,
            SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) as jumlah_closing,
            COUNT(nc.id) as total_calon,
            CASE 
                WHEN COUNT(nc.id) > 0 THEN ROUND((SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) / COUNT(nc.id)) * 100, 1)
                ELSE 0 
            END as persentase_konversi
        ');
        
        $builder->join('pdk_promo pp', 'pp.id = pm.id_jenis');
        $builder->join('nasabah_calon nc', 'nc.id_kegiatan = pm.id', 'left');
        
        // Filter berdasarkan tahun
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        
        // Filter berdasarkan id_kantor user yang login
        if ($user_kantor) {
            $builder->where('pm.id_kantor', $user_kantor);
        }
        
        $builder->groupBy('pm.id_jenis, pp.nama_kegiatan');
        $builder->having('SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) > 0'); // Hanya jenis promosi yang ada closing
        $builder->orderBy('jumlah_closing', 'DESC');
        
        $result = $builder->get()->getResultArray();
        
        // Hitung total closing untuk proporsi pie chart
        $total_closing_semua = 0;
        foreach ($result as $row) {
            $total_closing_semua += $row['jumlah_closing'];
        }
        
        // Tambahkan proporsi pie chart ke setiap item
        foreach ($result as &$row) {
            $row['proporsi_pie'] = $total_closing_semua > 0 ? round(($row['jumlah_closing'] / $total_closing_semua) * 100, 1) : 0;
        }
        
        return $result;
    }

    public function getDataProdukDiminati($tahun)
    {
        // Get user id_kantor
        $user_id = session()->get('user')['id'] ?? null;
        $user_kantor = null;
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            $user_kantor = $user_data->id_kantor ?? null;
        }

        $db = \Config\Database::connect();
        
        // Query untuk mendapatkan produk paling diminati berdasarkan % konversi
        $builder = $db->table('nasabah_calon nc');
        $builder->select('
            pl.uraian as nama_produk,
            COUNT(*) as total_calon,
            SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) as total_closing,
            CASE 
                WHEN COUNT(*) > 0 THEN ROUND((SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) / COUNT(*)) * 100, 1)
                ELSE 0 
            END as persentase_konversi
        ');
        
        $builder->join('promosi_main pm', 'pm.id = nc.id_kegiatan');
        $builder->join('pdk_laym pl', 'pl.id = nc.id_produk');
        
        // Filter berdasarkan tahun
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        
        // Filter berdasarkan id_kantor user yang login
        if ($user_kantor) {
            $builder->where('pm.id_kantor', $user_kantor);
        }
        
        $builder->groupBy('nc.id_produk, pl.uraian');
        $builder->having('COUNT(*) >= 3'); // Minimal 3 calon nasabah untuk relevansi
        $builder->orderBy('persentase_konversi', 'DESC');
        $builder->limit(10); // Top 10 produk
        
        $result = $builder->get()->getResultArray();
        
        return $result;
    }

    public function getDataKategoriLokasi($tahun)
    {
        // Get user id_kantor
        $user_id = session()->get('user')['id'] ?? null;
        $user_kantor = null;
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            $user_kantor = $user_data->id_kantor ?? null;
        }

        $db = \Config\Database::connect();
        
        // Query untuk mendapatkan tingkat konversi per kategori lokasi
        $builder = $db->table('promosi_main pm');
        $builder->select('
            lk.nama_kat as kategori_lokasi,
            COUNT(DISTINCT pm.id) as total_kegiatan,
            COUNT(nc.id) as total_calon,
            SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) as total_closing,
            CASE 
                WHEN COUNT(nc.id) > 0 THEN ROUND((SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) / COUNT(nc.id)) * 100, 1)
                ELSE 0 
            END as persentase_konversi
        ');
        
        $builder->join('lokasi_main lm', 'lm.id = pm.id_lokasi');
        $builder->join('lokasi_kate lk', 'lk.id = lm.id_kategori');
        $builder->join('nasabah_calon nc', 'nc.id_kegiatan = pm.id', 'left');
        
        // Filter berdasarkan tahun
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        
        // Filter berdasarkan id_kantor user yang login
        if ($user_kantor) {
            $builder->where('pm.id_kantor', $user_kantor);
        }
        
        $builder->groupBy('lm.id_kategori, lk.nama_kat');
        $builder->orderBy('persentase_konversi', 'DESC');
        
        $result = $builder->get()->getResultArray();
        
        return $result;
    }

    public function getDataKategoriLokasiPieChart($tahun)
    {
        // Get user id_kantor
        $user_id = session()->get('user')['id'] ?? null;
        $user_kantor = null;
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            $user_kantor = $user_data->id_kantor ?? null;
        }

        $db = \Config\Database::connect();
        
        // Query untuk pie chart berdasarkan proporsi nasabah closing per kategori lokasi
        $builder = $db->table('promosi_main pm');
        $builder->select('
            lk.nama_kat as kategori_lokasi,
            SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) as jumlah_closing,
            COUNT(nc.id) as total_calon,
            CASE 
                WHEN COUNT(nc.id) > 0 THEN ROUND((SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) / COUNT(nc.id)) * 100, 1)
                ELSE 0 
            END as persentase_konversi
        ');
        
        $builder->join('lokasi_main lm', 'lm.id = pm.id_lokasi');
        $builder->join('lokasi_kate lk', 'lk.id = lm.id_kategori');
        $builder->join('nasabah_calon nc', 'nc.id_kegiatan = pm.id', 'left');
        
        // Filter berdasarkan tahun
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        
        // Filter berdasarkan id_kantor user yang login
        if ($user_kantor) {
            $builder->where('pm.id_kantor', $user_kantor);
        }
        
        $builder->groupBy('lm.id_kategori, lk.nama_kat');
        $builder->having('SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) > 0'); // Hanya kategori lokasi yang ada closing
        $builder->orderBy('jumlah_closing', 'DESC');
        
        $result = $builder->get()->getResultArray();
        
        // Hitung total closing untuk proporsi pie chart
        $total_closing_semua = 0;
        foreach ($result as $row) {
            $total_closing_semua += $row['jumlah_closing'];
        }
        
        // Tambahkan proporsi pie chart ke setiap item
        foreach ($result as &$row) {
            $row['proporsi_pie'] = $total_closing_semua > 0 ? round(($row['jumlah_closing'] / $total_closing_semua) * 100, 1) : 0;
        }
        
        return $result;
    }
    
    /*
    public function getDataKaryawanEfektif($tahun, $limit = 5)
    {
        // Get user id_kantor
        $user_id = session()->get('user')['id'] ?? null;
        $user_kantor = null;
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            $user_kantor = $user_data->id_kantor ?? null;
        }

        $db = \Config\Database::connect();
        
        // Query untuk mendapatkan efektivitas karyawan
        $builder = $db->table('users u');
        $builder->select('
            u.id as user_id,
            u.username as nama_karyawan,
            u.nip as nip_karyawan,
            u.avatar as foto_karyawan,
            COUNT(DISTINCT pm.id) as total_kegiatan,
            COUNT(nc.id) as total_calon,
            SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) as total_closing,
            CASE 
                WHEN COUNT(nc.id) > 0 THEN ROUND((SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) / COUNT(nc.id)) * 100, 1)
                ELSE 0 
            END as persentase_konversi
        ');
        
        // Join ke promosi_main untuk mendapatkan kegiatan yang direkam karyawan
        $builder->join('promosi_main pm', 'u.id = pm.id_rek', 'left');
        
        // Join ke nasabah_calon untuk mendapatkan nasabah yang direkam karyawan
        $builder->join('nasabah_calon nc', 'nc.id_rek = u.id AND nc.id_kegiatan = pm.id', 'left');
        
        // Filter berdasarkan tahun dan kantor
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        if ($user_kantor) {
            $builder->where('u.id_kantor', $user_kantor);
        }
        
        $builder->groupBy('u.id, u.username, u.nip, u.avatar');
        $builder->having('COUNT(DISTINCT pm.id) > 0'); // Hanya karyawan yang pernah ikut kegiatan
        $builder->orderBy('persentase_konversi', 'DESC');
        $builder->orderBy('total_closing', 'DESC');
        $builder->limit($limit);
        
        $result = $builder->get()->getResultArray();
        
        // Hitung efektivitas untuk setiap karyawan
        foreach ($result as &$row) {
            $konversi = (float)$row['persentase_konversi'];
            $kegiatan = (int)$row['total_kegiatan'];
            
            // Skor efektivitas: tinggi konversi dengan sedikit kegiatan
            $row['skor_efektivitas'] = $kegiatan > 0 ? round(($konversi / $kegiatan) * 10, 2) : 0;
        }
        
        // Sort ulang berdasarkan efektivitas
        usort($result, function($a, $b) {
            return $b['skor_efektivitas'] <=> $a['skor_efektivitas'];
        });
        
        return $result;
    }
    */
    
    /* revisi */
    public function getDataKaryawanEfektif($tahun, $limit = 5)
    {
        $user_id = session()->get('user')['id'] ?? null;
        $user_kantor = null;
    
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')
                ->select('id_kantor')
                ->where('id', $user_id)
                ->get()
                ->getRow();
            $user_kantor = $user_data->id_kantor ?? null;
        }
    
        $db = \Config\Database::connect();
    
        $builder = $db->table('users u');
        $builder->select('
            u.id as user_id,
            u.username as nama_karyawan,
            u.nip as nip_karyawan,
            u.avatar as foto_karyawan,
            COUNT(DISTINCT pm.id) as total_kegiatan,
            COUNT(nc.id) as total_calon,
            SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) as total_closing,
            CASE 
                WHEN COUNT(nc.id) > 0 THEN ROUND((SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) / COUNT(nc.id)) * 100, 1)
                ELSE 0 
            END as persentase_konversi
        ');
    
        // Join ke nasabah_calon berdasarkan user
        $builder->join('nasabah_calon nc', 'nc.id_rek = u.id', 'left');
    
        // Join ke promosi_main hanya lewat id_kegiatan (bukan id_rek)
        $builder->join('promosi_main pm', 'pm.id = nc.id_kegiatan', 'left');
    
        // Filter tahun berdasarkan kegiatan di promosi_main
        $builder->where('YEAR(pm.tgl_1)', $tahun);
    
        if ($user_kantor) {
            $builder->where('u.id_kantor', $user_kantor);
        }
    
        $builder->groupBy('u.id, u.username, u.nip, u.avatar');
        $builder->having('COUNT(DISTINCT pm.id) > 0'); // hanya yang ikut kegiatan
        $builder->orderBy('persentase_konversi', 'DESC');
        $builder->orderBy('total_closing', 'DESC');
        $builder->limit($limit);
    
        $result = $builder->get()->getResultArray();
    
        foreach ($result as &$row) {
            $konversi = (float)$row['persentase_konversi'];
            $kegiatan = (int)$row['total_kegiatan'];
            $row['skor_efektivitas'] = $kegiatan > 0
                ? round(($konversi / $kegiatan) * 10, 2)
                : 0;
        }
    
        usort($result, function ($a, $b) {
            return $b['skor_efektivitas'] <=> $a['skor_efektivitas'];
        });
    
        return $result;
    }

    /* end revisi */

    public function getDataKantorUser()
    {
        // Get user id_kantor
        $user_id = session()->get('user')['id'] ?? null;
        $user_kantor = null;
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            $user_kantor = $user_data->id_kantor ?? null;
        }

        if (!$user_kantor) {
            return null;
        }

        $db = \Config\Database::connect();
        
        // Ambil data kantor dari pdk_satker
        $builder = $db->table('pdk_satker');
        $builder->select('id, satker, lati, longi');
        $builder->where('id', $user_kantor);
        
        $result = $builder->get()->getRow();
        
        return $result;
    }

    public function getDataLokasiHeatmap($tahun)
    {
        // Get user id_kantor
        $user_id = session()->get('user')['id'] ?? null;
        $user_kantor = null;
        if ($user_id) {
            $db_user = \Config\Database::connect();
            $user_data = $db_user->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            $user_kantor = $user_data->id_kantor ?? null;
        }

        $db = \Config\Database::connect();
        
        // Query untuk mendapatkan data lokasi dengan efektivitas dan konversi
        $builder = $db->table('lokasi_main lm');
        $builder->select('
            lm.id as lokasi_id,
            lm.nama_lokasi,
            lm.lati,
            lm.longi,
            COUNT(DISTINCT pm.id) as total_kegiatan,
            COUNT(nc.id) as total_calon,
            SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) as total_closing,
            CASE 
                WHEN COUNT(nc.id) > 0 THEN ROUND((SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) / COUNT(nc.id)) * 100, 1)
                ELSE 0 
            END as persentase_konversi
        ');
        
        // Join ke promosi_main dan nasabah_calon
        $builder->join('promosi_main pm', 'pm.id_lokasi = lm.id', 'left');
        $builder->join('nasabah_calon nc', 'nc.id_kegiatan = pm.id', 'left');
        
        // Filter berdasarkan tahun dan kantor
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        if ($user_kantor) {
            $builder->where('pm.id_kantor', $user_kantor);
        }
        
        // Filter hanya lokasi yang memiliki koordinat valid
        $builder->where('lm.lati IS NOT NULL');
        $builder->where('lm.longi IS NOT NULL');
        $builder->where('lm.lati !=', '');
        $builder->where('lm.longi !=', '');
        
        $builder->groupBy('lm.id, lm.nama_lokasi, lm.lati, lm.longi');
        $builder->having('COUNT(DISTINCT pm.id) > 0'); // Hanya lokasi yang ada kegiatannya
        
        $result = $builder->get()->getResultArray();
        
        // Hitung efektivitas untuk setiap lokasi
        foreach ($result as &$row) {
            $konversi = (float)$row['persentase_konversi'];
            $kegiatan = (int)$row['total_kegiatan'];
            
            // Skor efektivitas: tinggi konversi dengan sedikit kegiatan
            $row['skor_efektivitas'] = $kegiatan > 0 ? round(($konversi / $kegiatan) * 10, 2) : 0;
        }
        
        return $result;
    }
}