<?php

namespace App\Models\Dashboard;

use App\Models\BaseModel;

class DashareaModel extends BaseModel
{
    public function find_by_name($name)
    {
        $db = \Config\Database::connect();
        $builder = $db->table($this->table);
        $builder->select('link_dashboard.id, link_dashboard.namadb, link_dashboard.linkdb, link_dashboard.id_user, link_dashboard.wkt_upd, users.username');

        $builder->join('users', 'users.id = link_dashboard.id_user');
        $builder->where('namadb', $name);

        return $builder->get()->getRow();
    }

    // Methods for branch office detail dashboard
    public function countKegiatanPromosiCabang($tahun, $id_kc)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('promosi_main');
        
        $builder->where('YEAR(tgl_1)', $tahun);
        $builder->where('id_kantor', $id_kc);
        
        return $builder->countAllResults();
    }

    public function countCalonNasabahCabang($tahun, $id_kc)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('nasabah_calon nc');
        $builder->join('promosi_main pm', 'pm.id = nc.id_kegiatan');
        
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        $builder->where('pm.id_kantor', $id_kc);
        
        return $builder->countAllResults();
    }

    public function countClosingNasabahCabang($tahun, $id_kc)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('nasabah_calon nc');
        $builder->join('promosi_main pm', 'pm.id = nc.id_kegiatan');
        
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        $builder->where('pm.id_kantor', $id_kc);
        $builder->where('nc.closing', 1);
        
        return $builder->countAllResults();
    }

    public function getPersentaseKonversiCabang($tahun, $id_kc)
    {
        $total_calon = $this->countCalonNasabahCabang($tahun, $id_kc);
        $total_closing = $this->countClosingNasabahCabang($tahun, $id_kc);
        
        if ($total_calon == 0) {
            return 0;
        }
        
        return round(($total_closing / $total_calon) * 100, 1);
    }

    public function getDataBulananCabang($tahun, $id_kc)
    {
        $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);
        $builder->where('id_kantor', $id_kc);
        $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);
        $builder->where('pm.id_kantor', $id_kc);
        $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);
        $builder->where('pm.id_kantor', $id_kc);
        $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 getDataJenisPromosiCabang($tahun, $id_kc)
    {
        $db = \Config\Database::connect();
        
        $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');
        
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        $builder->where('pm.id_kantor', $id_kc);
        
        $builder->groupBy('pm.id_jenis, pp.nama_kegiatan');
        $builder->orderBy('persentase_konversi', 'DESC');
        
        $result = $builder->get()->getResultArray();
        
        return $result;
    }

    public function getDataJenisPromosiPieChartCabang($tahun, $id_kc)
    {
        $db = \Config\Database::connect();
        
        $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');
        
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        $builder->where('pm.id_kantor', $id_kc);
        
        $builder->groupBy('pm.id_jenis, pp.nama_kegiatan');
        $builder->having('SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) > 0');
        $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 getDataProdukDiminatiCabang($tahun, $id_kc)
    {
        $db = \Config\Database::connect();
        
        $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');
        
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        $builder->where('pm.id_kantor', $id_kc);
        
        $builder->groupBy('nc.id_produk, pl.uraian');
        $builder->having('COUNT(*) >= 3');
        $builder->orderBy('persentase_konversi', 'DESC');
        $builder->limit(10);
        
        $result = $builder->get()->getResultArray();
        
        return $result;
    }

    public function getDataKategoriLokasiCabang($tahun, $id_kc)
    {
        $db = \Config\Database::connect();
        
        $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');
        
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        $builder->where('pm.id_kantor', $id_kc);
        
        $builder->groupBy('lm.id_kategori, lk.nama_kat');
        $builder->orderBy('persentase_konversi', 'DESC');
        
        $result = $builder->get()->getResultArray();
        
        return $result;
    }

    public function getDataKategoriLokasiPieChartCabang($tahun, $id_kc)
    {
        $db = \Config\Database::connect();
        
        $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');
        
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        $builder->where('pm.id_kantor', $id_kc);
        
        $builder->groupBy('lm.id_kategori, lk.nama_kat');
        $builder->having('SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) > 0');
        $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 getDataKaryawanEfektifCabang($tahun, $id_kc, $limit = 5)
    {
        $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
        ');
        
        $builder->join('promosi_main pm', 'u.id = pm.id_rek', 'left');
        $builder->join('nasabah_calon nc', 'nc.id_rek = u.id AND nc.id_kegiatan = pm.id', 'left');
        
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        $builder->where('u.id_kantor', $id_kc);
        
        $builder->groupBy('u.id, u.username, u.nip, u.avatar');
        $builder->having('COUNT(DISTINCT pm.id) > 0');
        $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'];
            
            $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;
    }
}