<?php

namespace App\Controllers\Dashboard;

use App\Controllers\BaseController;
use CodeIgniter\API\ResponseTrait;

class Wilayah extends BaseController
{
    use ResponseTrait;

    public function __construct()
    {
        $this->DashboardModel = model('App\Models\Dashboard\DashwilayahModel');
    }

    public function index()
    {
        // Set tahun default jika belum ada session
        if (!session()->get('tahun_chart')) {
            session()->set('tahun_chart', date('Y'));
        }

        $tahun = session()->get('tahun_chart');
        
        // Get user's wilayah office id
        $user_id = auth()->id();
        $id_kw = null;
        
        if ($user_id) {
            $db = \Config\Database::connect();
            $user_data = $db->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            $id_kw = $user_data->id_kantor ?? null;
        }

        // Initialize default values
        $summary_data = [
            'jumlah_kegiatan' => 0,
            'jumlah_calon' => 0,
            'jumlah_closing' => 0,
            'persentase_konversi' => 0
        ];
        $data_bulanan = [];
        $data_area = [];

        if ($id_kw) {
            // Get summary data for wilayah
            $summary_data = $this->DashboardModel->getSummaryWilayah($tahun, $id_kw);
            
            // Get monthly data for chart
            $data_bulanan = $this->DashboardModel->getDataBulananWilayah($tahun, $id_kw);
            
            // Get area offices data
            $data_area = $this->DashboardModel->getDataAreaWilayah($tahun, $id_kw);
        }

        // Get data for heatmap
        $data_kantor = $this->DashboardModel->getDataKantorWilayah($id_kw);
        $data_heatmap_area = $this->DashboardModel->getDataHeatmapArea($tahun, $id_kw);

        // Fallback data jika null
        if (!$data_kantor) {
            $data_kantor = (object)[
                'id' => null,
                'satker' => 'Kantor Wilayah',
                'lati' => '-6.2088',
                'longi' => '106.8456'
            ];
        }

        return view('dashboard/wilayah/wilayah_main', [
            'title' => 'Summary Wilayah',
            'tahun_aktif' => $tahun,
            'jumlah_kegiatan' => $summary_data['jumlah_kegiatan'],
            'jumlah_calon' => $summary_data['jumlah_calon'],
            'jumlah_closing' => $summary_data['jumlah_closing'],
            'persentase_konversi' => $summary_data['persentase_konversi'],
            'data_bulanan' => $data_bulanan,
            'data_area' => $data_area,
            'data_kantor' => $data_kantor,
            'data_heatmap_area' => $data_heatmap_area ?: [],
            'link_sync' => base_url('dashboard/wilayah/sync'),
            'id_kw' => $id_kw,
        ]);
    }

    public function setTahun()
    {
        $tahun = $this->request->getPost('tahun');
        
        if ($tahun && is_numeric($tahun) && strlen($tahun) == 4) {
            session()->set('tahun_chart', $tahun);
            return $this->response->setJSON([
                'status' => 'success',
                'message' => 'Tahun berhasil diupdate',
                'tahun' => $tahun
            ]);
        }
        
        return $this->response->setJSON([
            'status' => 'error',
            'message' => 'Format tahun tidak valid'
        ]);
    }

    public function sync()
    {
        try {
            $db = \Config\Database::connect();
            
            // Get tahun from session
            $tahun = session()->get('tahun_chart') ?: date('Y');
            
            // Get user's wilayah office id
            $user_id = auth()->id();
            if (!$user_id) {
                return $this->response->setJSON([
                    'status' => 'error',
                    'message' => 'User tidak ditemukan'
                ]);
            }
            
            // Get user's office id
            $user_data = $db->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            if (!$user_data || !$user_data->id_kantor) {
                return $this->response->setJSON([
                    'status' => 'error',
                    'message' => 'Kantor user tidak ditemukan'
                ]);
            }
            
            $id_kw = $user_data->id_kantor; // xx (kanwil id)
            
            // Get wilayah office data and validate it's a wilayah office
            $wilayah_office = $db->table('pdk_satker')->where('id', $id_kw)->get()->getRow();
            if (!$wilayah_office || $wilayah_office->tipe !== 'Wilayah') {
                return $this->response->setJSON([
                    'status' => 'error',
                    'message' => 'User bukan dari kantor wilayah'
                ]);
            }
            
            // Get all area offices under this wilayah
            $areas = $db->table('pdk_satker')
                ->select('id')
                ->where('tipe', 'Area')
                ->where('id_kanwil', $id_kw)
                ->get()
                ->getResult();
            
            if (empty($areas)) {
                return $this->response->setJSON([
                    'status' => 'warning',
                    'message' => 'Tidak ada kantor area di bawah wilayah ini'
                ]);
            }
            
            $summary_data = [];
            $total_processed = 0;
            
            // Process each area
            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 $this->response->setJSON([
                'status' => 'success',
                'message' => 'Data berhasil disinkronisasi',
                'tahun' => $tahun,
                'total_area' => count($areas),
                'total_cabang_processed' => $total_processed
            ]);
            
        } catch (\Exception $e) {
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Gagal sinkronisasi: ' . $e->getMessage()
            ]);
        }
    }

    public function drillDownArea($id_area)
    {
        // Set tahun default jika belum ada session
        if (!session()->get('tahun_chart')) {
            session()->set('tahun_chart', date('Y'));
        }

        $tahun = session()->get('tahun_chart');
        
        // Validate id_area
        if (!$id_area || !is_numeric($id_area)) {
            throw new \CodeIgniter\Exceptions\PageNotFoundException('Kantor area tidak ditemukan');
        }

        $db = \Config\Database::connect();
        
        // Get kantor area data
        $kantor_area = $db->table('pdk_satker')->where('id', $id_area)->get()->getRow();
        if (!$kantor_area || $kantor_area->tipe !== 'Area') {
            throw new \CodeIgniter\Exceptions\PageNotFoundException('Kantor area tidak valid');
        }

        // Verify this area belongs to current user's wilayah
        $user_id = auth()->id();
        if ($user_id) {
            $user_data = $db->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            $id_kw_user = $user_data->id_kantor ?? null;
            
            if ($kantor_area->id_kanwil != $id_kw_user) {
                throw new \CodeIgniter\Exceptions\PageNotFoundException('Akses ditolak: Kantor area bukan milik wilayah Anda');
            }
        }

        // Get data using drill down methods
        $summary_data = $this->DashboardModel->getDataAreaDrillDown($tahun, $id_area);
        $data_bulanan = $this->DashboardModel->getDataBulananAreaDrillDown($tahun, $id_area);
        $data_cabang = $this->DashboardModel->getDataCabangAreaDrillDown($tahun, $id_area);

        return view('dashboard/wilayah/area_drilldown', [
            'title' => 'Detail ' . $kantor_area->satker,
            'kantor_area' => $kantor_area,
            'tahun_aktif' => $tahun,
            'jumlah_kegiatan' => $summary_data['jumlah_kegiatan'],
            'jumlah_calon' => $summary_data['jumlah_calon'],
            'jumlah_closing' => $summary_data['jumlah_closing'],
            'persentase_konversi' => $summary_data['persentase_konversi'],
            'data_bulanan' => $data_bulanan,
            'data_cabang' => $data_cabang,
            'link_back' => base_url('dashboard/wilayah'),
        ]);
    }
}
