<?php

namespace App\Models\Dashboard;

use App\Models\BaseModel;

class DashwilayahModel extends BaseModel
{
    public function getDataAreaWilayah($tahun, $id_kw)
    {
        $db = \Config\Database::connect();
        
        // Query data area dari sum_area dengan join ke pdk_satker untuk nama kantor
        $builder = $db->table('sum_area sa');
        $builder->select('
            sa.id_area,
            SUM(sa.kegiatan) as total_kegiatan,
            SUM(sa.calon) as total_calon,
            SUM(sa.closing) as total_closing,
            CASE 
                WHEN SUM(sa.calon) > 0 THEN ROUND((SUM(sa.closing) / SUM(sa.calon)) * 100, 2)
                ELSE 0 
            END as konversi,
            CASE 
                WHEN SUM(sa.kegiatan) > 0 THEN ROUND(((SUM(sa.closing) / SUM(sa.calon)) * 100 / SUM(sa.kegiatan)) * 10, 2)
                ELSE 0 
            END as efektivitas,
            ps.satker as nama_area,
            ps.lati,
            ps.longi
        ');
        
        // Join dengan pdk_satker untuk mendapatkan nama kantor area
        $builder->join('pdk_satker ps', 'ps.id = sa.id_area');
        
        // Filter berdasarkan kanwil dan tahun
        $builder->where('sa.id_kw', $id_kw);
        $builder->where('sa.tahun', $tahun);
        $builder->where('ps.tipe', 'Area');
        
        // Group by area
        $builder->groupBy('sa.id_area, ps.satker, ps.lati, ps.longi');
        
        // Default order by total kegiatan promosi descending
        $builder->orderBy('total_kegiatan', 'DESC');
        
        $result = $builder->get()->getResultArray();
        
        return $result;
    }

    public function getDataBulananWilayah($tahun, $id_kw)
    {
        $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 data from promosi_main for monthly breakdown
        // We need to get from original tables since sum_area only stores yearly totals
        $builder = $db->table('promosi_main pm');
        $builder->select('
            MONTH(pm.tgl_1) as bulan,
            COUNT(DISTINCT pm.id) as kegiatan_promosi,
            COUNT(nc.id) as calon_nasabah,
            SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) as closing_nasabah
        ');
        
        // Join dengan nasabah_calon untuk mendapatkan data calon dan closing
        $builder->join('nasabah_calon nc', 'nc.id_kegiatan = pm.id', 'left');
        
        // Join dengan pdk_satker untuk filter berdasarkan kanwil
        $builder->join('pdk_satker ps', 'ps.id = pm.id_kantor');
        
        // Filter berdasarkan tahun dan kanwil
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        $builder->where('ps.id_kanwil', $id_kw);
        $builder->where('ps.tipe', 'Cabang/ UPC/ UPS');
        
        $builder->groupBy('MONTH(pm.tgl_1)');
        
        $result = $builder->get()->getResultArray();

        // Populate data bulanan dengan hasil query
        foreach ($result as $row) {
            $bulan = (int)$row['bulan'];
            if ($bulan >= 1 && $bulan <= 12) {
                $data_bulanan[$bulan] = [
                    'bulan' => $bulan,
                    'kegiatan_promosi' => (int)$row['kegiatan_promosi'],
                    'calon_nasabah' => (int)$row['calon_nasabah'],
                    'closing_nasabah' => (int)$row['closing_nasabah']
                ];
            }
        }

        return array_values($data_bulanan);
    }

    public function getDataKantorWilayah($id_kw)
    {
        $db = \Config\Database::connect();
        
        // Ambil data kantor wilayah dari pdk_satker
        $builder = $db->table('pdk_satker');
        $builder->select('id, satker, lati, longi');
        $builder->where('id', $id_kw);
        $builder->where('tipe', 'Wilayah');
        
        $result = $builder->get()->getRow();
        
        return $result;
    }

    public function getDataHeatmapArea($tahun, $id_kw)
    {
        $db = \Config\Database::connect();
        
        // Query untuk mendapatkan data area dengan efektivitas dan konversi untuk heatmap
        $builder = $db->table('pdk_satker ps');
        $builder->select('
            ps.id as area_id,
            ps.satker as nama_area,
            ps.lati,
            ps.longi,
            COALESCE(SUM(sa.kegiatan), 0) as total_kegiatan,
            COALESCE(SUM(sa.calon), 0) as total_calon,
            COALESCE(SUM(sa.closing), 0) as total_closing,
            CASE 
                WHEN SUM(sa.calon) > 0 THEN ROUND((SUM(sa.closing) / SUM(sa.calon)) * 100, 1)
                ELSE 0 
            END as persentase_konversi,
            CASE 
                WHEN SUM(sa.kegiatan) > 0 THEN ROUND(((SUM(sa.closing) / SUM(sa.calon)) * 100 / SUM(sa.kegiatan)) * 10, 2)
                ELSE 0 
            END as skor_efektivitas
        ');
        
        // Left join dengan sum_area untuk mendapatkan data summary
        $builder->join('sum_area sa', 'sa.id_area = ps.id AND sa.tahun = ' . (int)$tahun, 'left');
        
        // Filter untuk kantor area di bawah kanwil ini
        $builder->where('ps.tipe', 'Area');
        $builder->where('ps.id_kanwil', $id_kw);
        
        // Filter hanya area yang memiliki koordinat valid
        $builder->where('ps.lati IS NOT NULL');
        $builder->where('ps.longi IS NOT NULL');
        $builder->where('ps.lati !=', '');
        $builder->where('ps.longi !=', '');
        
        $builder->groupBy('ps.id, ps.satker, ps.lati, ps.longi');
        
        $result = $builder->get()->getResultArray();
        
        return $result;
    }

    public function getSummaryWilayah($tahun, $id_kw)
    {
        $db = \Config\Database::connect();
        
        // Get summary data from sum_area table grouped by kanwil
        $summary = $db->table('sum_area')
            ->select('SUM(kegiatan) as total_kegiatan, SUM(calon) as total_calon, SUM(closing) as total_closing')
            ->where('id_kw', $id_kw)
            ->where('tahun', $tahun)
            ->get()
            ->getRow();
        
        if ($summary) {
            $jumlah_kegiatan = (int)($summary->total_kegiatan ?? 0);
            $jumlah_calon = (int)($summary->total_calon ?? 0);
            $jumlah_closing = (int)($summary->total_closing ?? 0);
            $persentase_konversi = $jumlah_calon > 0 ? round(($jumlah_closing / $jumlah_calon) * 100, 1) : 0;
        } else {
            $jumlah_kegiatan = 0;
            $jumlah_calon = 0;
            $jumlah_closing = 0;
            $persentase_konversi = 0;
        }
        
        return [
            'jumlah_kegiatan' => $jumlah_kegiatan,
            'jumlah_calon' => $jumlah_calon,
            'jumlah_closing' => $jumlah_closing,
            'persentase_konversi' => $persentase_konversi
        ];
    }

    // Method untuk drill down ke area tertentu tanpa bergantung session user
    public function getDataAreaDrillDown($tahun, $id_area)
    {
        $db = \Config\Database::connect();
        
        // Get summary data from sum_area table for specific area
        $summary = $db->table('sum_area')
            ->select('SUM(kegiatan) as total_kegiatan, SUM(calon) as total_calon, SUM(closing) as total_closing')
            ->where('id_area', $id_area)
            ->where('tahun', $tahun)
            ->get()
            ->getRow();
        
        if ($summary) {
            $jumlah_kegiatan = (int)($summary->total_kegiatan ?? 0);
            $jumlah_calon = (int)($summary->total_calon ?? 0);
            $jumlah_closing = (int)($summary->total_closing ?? 0);
            $persentase_konversi = $jumlah_calon > 0 ? round(($jumlah_closing / $jumlah_calon) * 100, 1) : 0;
        } else {
            $jumlah_kegiatan = 0;
            $jumlah_calon = 0;
            $jumlah_closing = 0;
            $persentase_konversi = 0;
        }
        
        return [
            'jumlah_kegiatan' => $jumlah_kegiatan,
            'jumlah_calon' => $jumlah_calon,
            'jumlah_closing' => $jumlah_closing,
            'persentase_konversi' => $persentase_konversi
        ];
    }

    public function getDataBulananAreaDrillDown($tahun, $id_area)
    {
        $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 data from promosi_main for monthly breakdown
        $builder = $db->table('promosi_main pm');
        $builder->select('
            MONTH(pm.tgl_1) as bulan,
            COUNT(DISTINCT pm.id) as kegiatan_promosi,
            COUNT(nc.id) as calon_nasabah,
            SUM(CASE WHEN nc.closing = 1 THEN 1 ELSE 0 END) as closing_nasabah
        ');
        
        // Join dengan nasabah_calon untuk mendapatkan data calon dan closing
        $builder->join('nasabah_calon nc', 'nc.id_kegiatan = pm.id', 'left');
        
        // Join dengan pdk_satker untuk filter berdasarkan area
        $builder->join('pdk_satker ps', 'ps.id = pm.id_kantor');
        
        // Filter berdasarkan tahun dan area
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        $builder->where('ps.id_area', $id_area);
        $builder->where('ps.tipe', 'Cabang/ UPC/ UPS');
        
        $builder->groupBy('MONTH(pm.tgl_1)');
        
        $result = $builder->get()->getResultArray();

        // Populate data bulanan dengan hasil query
        foreach ($result as $row) {
            $bulan = (int)$row['bulan'];
            if ($bulan >= 1 && $bulan <= 12) {
                $data_bulanan[$bulan] = [
                    'bulan' => $bulan,
                    'kegiatan_promosi' => (int)$row['kegiatan_promosi'],
                    'calon_nasabah' => (int)$row['calon_nasabah'],
                    'closing_nasabah' => (int)$row['closing_nasabah']
                ];
            }
        }

        return array_values($data_bulanan);
    }

    public function getDataCabangAreaDrillDown($tahun, $id_area)
    {
        $db = \Config\Database::connect();
        
        // Query data cabang dari sum_area dengan join ke pdk_satker untuk nama kantor
        $builder = $db->table('sum_area sa');
        $builder->select('
            sa.id_kc,
            sa.kegiatan,
            sa.calon,
            sa.closing,
            sa.konversi,
            sa.efektivitas,
            ps.satker as nama_kantor
        ');
        
        // Join dengan pdk_satker untuk mendapatkan nama kantor
        $builder->join('pdk_satker ps', 'ps.id = sa.id_kc');
        
        // Filter berdasarkan area dan tahun
        $builder->where('sa.id_area', $id_area);
        $builder->where('sa.tahun', $tahun);
        
        // Default order by kegiatan promosi descending
        $builder->orderBy('sa.kegiatan', 'DESC');
        
        $result = $builder->get()->getResultArray();
        
        return $result;
    }
}
