<?php

namespace App\Models\Dashboard;

use App\Models\BaseModel;

class DashpusatModel extends BaseModel
{
    public function getDataWilayahPusat($tahun)
    {
        $db = \Config\Database::connect();
        
        // Query data wilayah dari sum_area dengan join ke pdk_satker untuk nama kantor
        $builder = $db->table('sum_area sa');
        $builder->select('
            sa.id_kw,
            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_wilayah,
            ps.lati,
            ps.longi
        ');
        
        // Join dengan pdk_satker untuk mendapatkan nama kantor wilayah
        $builder->join('pdk_satker ps', 'ps.id = sa.id_kw');
        
        // Filter berdasarkan tahun dan tipe wilayah
        $builder->where('sa.tahun', $tahun);
        $builder->where('ps.tipe', 'Wilayah');
        
        // Group by wilayah
        $builder->groupBy('sa.id_kw, 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 getDataBulananPusat($tahun)
    {
        $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
        // Get all data (pusat = all wilayah)
        $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');
        
        // Filter berdasarkan tahun saja (semua wilayah)
        $builder->where('YEAR(pm.tgl_1)', $tahun);
        
        $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 getDataKantorPusat()
    {
        $db = \Config\Database::connect();
        
        // Ambil data kantor pusat dari pdk_satker
        $builder = $db->table('pdk_satker');
        $builder->select('id, satker, lati, longi');
        $builder->where('tipe', 'Pusat');
        $builder->limit(1); // Asumsi hanya ada 1 kantor pusat
        
        $result = $builder->get()->getRow();
        
        return $result;
    }

    public function getDataHeatmapWilayah($tahun)
    {
        $db = \Config\Database::connect();
        
        // Query untuk mendapatkan data wilayah dengan efektivitas dan konversi untuk heatmap
        $builder = $db->table('pdk_satker ps');
        $builder->select('
            ps.id as wilayah_id,
            ps.satker as nama_wilayah,
            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_kw = ps.id AND sa.tahun = ' . (int)$tahun, 'left');
        
        // Filter untuk kantor wilayah
        $builder->where('ps.tipe', 'Wilayah');
        
        // Filter hanya wilayah 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 getSummaryPusat($tahun)
    {
        $db = \Config\Database::connect();
        
        // Get summary data from sum_area table for all data (pusat)
        $summary = $db->table('sum_area')
            ->select('SUM(kegiatan) as total_kegiatan, SUM(calon) as total_calon, SUM(closing) as total_closing')
            ->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 wilayah tertentu tanpa bergantung session user  
    public function getDataWilayahDrillDown($tahun, $id_kw)
    {
        $db = \Config\Database::connect();
        
        // Get summary data from sum_area table for specific wilayah
        $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
        ];
    }

    public function getDataBulananWilayahDrillDown($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
        $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 getDataAreaWilayahDrillDown($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
        ');
        
        // 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');
        
        // Default order by total kegiatan promosi descending
        $builder->orderBy('total_kegiatan', 'DESC');
        
        $result = $builder->get()->getResultArray();
        
        return $result;
    }

    public function syncDataPusat($tahun)
    {
        $db = \Config\Database::connect();
        
        // Get all kantor wilayah
        $wilayah_list = $db->table('pdk_satker')
            ->select('id')
            ->where('tipe', 'Wilayah')
            ->get()
            ->getResult();
        
        if (empty($wilayah_list)) {
            return [
                'status' => 'warning',
                'message' => 'Tidak ada kantor wilayah ditemukan'
            ];
        }
        
        $summary_data = [];
        $total_processed = 0;
        
        // Process each wilayah
        foreach ($wilayah_list as $wilayah) {
            $id_kw = $wilayah->id;
            
            // Get all area offices under this wilayah
            $areas = $db->table('pdk_satker')
                ->select('id')
                ->where('tipe', 'Area')
                ->where('id_kanwil', $id_kw)
                ->get()
                ->getResult();
            
            // Process each area under this wilayah
            foreach ($areas as $area) {
                $id_area = $area->id;
                
                // Get all branch offices under this area
                $branches = $db->table('pdk_satker')
                    ->select('id')
                    ->where('tipe', 'Cabang/ UPC/ UPS')
                    ->where('id_area', $id_area)
                    ->get()
                    ->getResult();
                
                // Process each branch under this area
                foreach ($branches as $branch) {
                    $id_kc = $branch->id;
                    
                    // Count kegiatan promosi
                    $kegiatan = $db->table('promosi_main')
                        ->where('YEAR(tgl_1)', $tahun)
                        ->where('id_kantor', $id_kc)
                        ->countAllResults();
                    
                    // Count calon nasabah
                    $calon_query = $db->table('nasabah_calon nc')
                        ->join('promosi_main pm', 'pm.id = nc.id_kegiatan')
                        ->where('YEAR(pm.tgl_1)', $tahun)
                        ->where('pm.id_kantor', $id_kc);
                    $calon = $calon_query->countAllResults();
                    
                    // Count closing nasabah
                    $closing_query = $db->table('nasabah_calon nc')
                        ->join('promosi_main pm', 'pm.id = nc.id_kegiatan')
                        ->where('YEAR(pm.tgl_1)', $tahun)
                        ->where('pm.id_kantor', $id_kc)
                        ->where('nc.closing', 1);
                    $closing = $closing_query->countAllResults();
                    
                    // Calculate konversi percentage
                    $konversi = $calon > 0 ? round(($closing / $calon) * 100, 2) : 0;
                    
                    // Calculate efektivitas
                    $efektivitas = $kegiatan > 0 ? round(($konversi / $kegiatan) * 10, 2) : 0;
                    
                    // Check if record already exists for this year and branch
                    $existing = $db->table('sum_area')
                        ->where('id_kc', $id_kc)
                        ->where('tahun', $tahun)
                        ->get()
                        ->getRow();
                    
                    $data = [
                        'id_kw' => $id_kw,
                        'id_area' => $id_area,
                        'id_kc' => $id_kc,
                        'tahun' => $tahun,
                        'kegiatan' => $kegiatan,
                        'calon' => $calon,
                        'closing' => $closing,
                        'konversi' => $konversi,
                        'efektivitas' => $efektivitas,
                        'wkt' => date('Y-m-d H:i:s')
                    ];
                    
                    if ($existing) {
                        // Update existing record
                        $db->table('sum_area')->where('id', $existing->id)->update($data);
                    } else {
                        // Insert new record
                        $db->table('sum_area')->insert($data);
                    }
                    
                    $total_processed++;
                }
            }
        }
        
        return [
            'status' => 'success',
            'message' => 'Data berhasil disinkronisasi',
            'tahun' => $tahun,
            'total_wilayah' => count($wilayah_list),
            'total_cabang_processed' => $total_processed
        ];
    }
}
