<?php

namespace App\Controllers\Dashboard;

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

class Area extends BaseController
{
    use ResponseTrait;

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

    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 area office id
        $user_id = auth()->id();
        $id_area = null;
        
        if ($user_id) {
            $db = \Config\Database::connect();
            $user_data = $db->table('users')->select('id_kantor')->where('id', $user_id)->get()->getRow();
            $id_area = $user_data->id_kantor ?? null;
        }

        // Initialize default values
        $jumlah_kegiatan = 0;
        $jumlah_calon = 0;
        $jumlah_closing = 0;
        $persentase_konversi = 0;
        $data_bulanan = [];

        if ($id_area) {
            $db = \Config\Database::connect();
            
            // Get summary data from sum_area table
            $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;
            }
            
            // Get monthly data for chart
            $data_bulanan = $this->getDataBulananArea($tahun, $id_area);
            
            // Get branch offices data
            $data_cabang = $this->getDataCabangArea($tahun, $id_area);
        } else {
            $data_cabang = [];
        }

        return view('dashboard/area/area_main', [
            'title' => 'Summary Area',
            'tahun_aktif' => $tahun,
            'jumlah_kegiatan' => $jumlah_kegiatan,
            'jumlah_calon' => $jumlah_calon,
            'jumlah_closing' => $jumlah_closing,
            'persentase_konversi' => $persentase_konversi,
            'data_bulanan' => $data_bulanan,
            'data_cabang' => $data_cabang,
            'link_sync' => base_url('dashboard/area/sync'),
            'link_hmap' => base_url('dashboard/area/hmap'),
        ]);
    }

    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 area 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_area = $user_data->id_kantor; // xx
            
            // Get area office data and validate it's an area office
            $area_office = $db->table('pdk_satker')->where('id', $id_area)->get()->getRow();
            if (!$area_office || $area_office->tipe !== 'Area') {
                return $this->response->setJSON([
                    'status' => 'error',
                    'message' => 'User bukan dari kantor area'
                ]);
            }
            
            $id_kanwil = $area_office->id_kanwil;
            
            // 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();
            
            if (empty($branches)) {
                return $this->response->setJSON([
                    'status' => 'warning',
                    'message' => 'Tidak ada kantor cabang di bawah area ini'
                ]);
            }
            
            $summary_data = [];
            
            // Process each branch
            foreach ($branches as $branch) {
                $id_kc = $branch->id; // zz
                
                // 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_kanwil,
                    '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);
                }
                
                $summary_data[] = array_merge($data, ['id_kc' => $id_kc]);
            }
            
            return $this->response->setJSON([
                'status' => 'success',
                'message' => 'Data berhasil disinkronisasi',
                'tahun' => $tahun,
                'total_cabang' => count($branches),
                'data_summary' => $summary_data
            ]);
            
        } catch (\Exception $e) {
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Gagal sinkronisasi: ' . $e->getMessage()
            ]);
        }
    }

    public function detailCabang($id_kc)
    {
        // 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_kc
        if (!$id_kc || !is_numeric($id_kc)) {
            throw new \CodeIgniter\Exceptions\PageNotFoundException('Kantor cabang tidak ditemukan');
        }

        $db = \Config\Database::connect();
        
        // Get kantor cabang data
        $kantor_cabang = $db->table('pdk_satker')->where('id', $id_kc)->get()->getRow();
        if (!$kantor_cabang || $kantor_cabang->tipe !== 'Cabang/ UPC/ UPS') {
            throw new \CodeIgniter\Exceptions\PageNotFoundException('Kantor cabang tidak valid');
        }

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

        // Use DashareaModel methods for branch-specific data
        $jumlah_kegiatan = $this->DashboardModel->countKegiatanPromosiCabang($tahun, $id_kc);
        $jumlah_calon = $this->DashboardModel->countCalonNasabahCabang($tahun, $id_kc);
        $jumlah_closing = $this->DashboardModel->countClosingNasabahCabang($tahun, $id_kc);
        $persentase_konversi = $this->DashboardModel->getPersentaseKonversiCabang($tahun, $id_kc);
        
        // Get detailed data for charts and tables
        $data_bulanan = $this->DashboardModel->getDataBulananCabang($tahun, $id_kc);
        $data_jenis_promosi = $this->DashboardModel->getDataJenisPromosiCabang($tahun, $id_kc);
        $data_jenis_promosi_pie = $this->DashboardModel->getDataJenisPromosiPieChartCabang($tahun, $id_kc);
        $data_produk_diminati = $this->DashboardModel->getDataProdukDiminatiCabang($tahun, $id_kc);
        $data_kategori_lokasi = $this->DashboardModel->getDataKategoriLokasiCabang($tahun, $id_kc);
        $data_kategori_lokasi_pie = $this->DashboardModel->getDataKategoriLokasiPieChartCabang($tahun, $id_kc);
        $data_karyawan_efektif = $this->DashboardModel->getDataKaryawanEfektifCabang($tahun, $id_kc, 5);

        return view('dashboard/area/detail_cabang', [
            'title' => 'Detail ' . $kantor_cabang->satker,
            'kantor_cabang' => $kantor_cabang,
            'tahun_aktif' => $tahun,
            'jumlah_kegiatan' => $jumlah_kegiatan,
            'jumlah_calon' => $jumlah_calon,
            'jumlah_closing' => $jumlah_closing,
            'persentase_konversi' => $persentase_konversi,
            'data_bulanan' => $data_bulanan,
            'data_jenis_promosi' => $data_jenis_promosi,
            'data_jenis_promosi_pie' => $data_jenis_promosi_pie,
            'data_produk_diminati' => $data_produk_diminati,
            'data_kategori_lokasi' => $data_kategori_lokasi,
            'data_kategori_lokasi_pie' => $data_kategori_lokasi_pie,
            'data_karyawan_efektif' => $data_karyawan_efektif,
            'link_back' => base_url('dashboard/area'),
        ]);
    }

    private function getDataBulananArea($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
        // 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 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);
    }

    private function getDataCabangArea($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;
    }
}