<?php

namespace App\Controllers;

use App\Controllers\BaseController;

class AdminBidangController extends BaseController
{
    private $db;

    public function __construct()
    {
        $this->db = \Config\Database::connect();
    }

    public function index()
    {
        $session = session();
        $bidangId = $session->get('bidang_id');
        $req = service('request');
        $tahun = (int) ($req->getGet('tahun') ?: date('Y'));
        $triwulan = (int) ($req->getGet('triwulan') ?: (int)ceil(date('n') / 3));

        // Get OPD under this Bidang
        $opdList = $this->db->table('opd')->where('bidang_id', $bidangId)->get()->getResultArray();
        $opdIds = array_column($opdList, 'id');

        $totalOpd = count($opdList);
        $totalLaporan = 0;
        $laporanPending = 0;
        $laporanDisetujui = 0;
        $statusPerOpd = [];

        if (!empty($opdIds)) {
            $builder = $this->db->table('realisasi');
            $builder->whereIn('opd_id', $opdIds)->where('tahun', $tahun)->where('triwulan', $triwulan);
            $totalLaporan = $builder->countAllResults(false);
            $laporanPending = $builder->where('status', 'Diajukan')->countAllResults(false);
            $laporanDisetujui = $builder->where('status', 'Disetujui')->countAllResults(false);
            $grouped = $this->db->table('realisasi')
                ->select('opd_id, status, COUNT(*) as cnt')
                ->whereIn('opd_id', $opdIds)
                ->where('tahun', $tahun)
                ->where('triwulan', $triwulan)
                ->groupBy('opd_id, status')
                ->get()->getResultArray();
            foreach ($opdIds as $oid) {
                $statusPerOpd[$oid] = ['Diajukan' => 0, 'Disetujui' => 0, 'Draft' => 0, 'total' => 0];
            }
            foreach ($grouped as $g) {
                $oid = (int)$g['opd_id'];
                $st = $g['status'];
                $cnt = (int)$g['cnt'];
                if (!isset($statusPerOpd[$oid])) {
                    $statusPerOpd[$oid] = ['Diajukan' => 0, 'Disetujui' => 0, 'Draft' => 0, 'total' => 0];
                }
                if (!isset($statusPerOpd[$oid][$st])) {
                    $statusPerOpd[$oid][$st] = 0;
                }
                $statusPerOpd[$oid][$st] += $cnt;
                $statusPerOpd[$oid]['total'] += $cnt;
            }
        }

        $data = [
            'title' => 'Dashboard Admin Bidang',
            'opdList' => $opdList,
            'tahun' => $tahun,
            'triwulan' => $triwulan,
            'statusPerOpd' => $statusPerOpd,
            'stats' => [
                'totalOpd' => $totalOpd,
                'totalLaporan' => $totalLaporan,
                'pending' => $laporanPending,
                'disetujui' => $laporanDisetujui
            ]
        ];
        return view('admin_bidang/dashboard', $data);
    }

    public function verifikasi()
    {
        $session = session();
        $bidangId = $session->get('bidang_id');
        $req = service('request');
        $opdIdFilter = $req->getGet('opd_id');
        $tahun = $req->getGet('tahun');
        $triwulan = $req->getGet('triwulan');
        $programId = $req->getGet('program_id');

        $opdList = $this->db->table('opd')->where('bidang_id', $bidangId)->get()->getResultArray();
        $laporan = [];
        {
            $builder = $this->db->table('realisasi');
            $builder->select('realisasi.*, opd.nama_opd, sub_kegiatan.nama_sub_kegiatan, kegiatan.nama_kegiatan');
            $builder->join('opd', 'opd.id = realisasi.opd_id');
            $builder->join('sub_kegiatan', 'sub_kegiatan.id = realisasi.sub_kegiatan_id');
            $builder->join('kegiatan', 'kegiatan.id = sub_kegiatan.kegiatan_id');
            $builder->where('opd.bidang_id', (int)$bidangId);
            $builder->where('realisasi.status !=', 'Draft');
            if ($opdIdFilter) {
                $builder->where('realisasi.opd_id', (int)$opdIdFilter);
            }
            if ($tahun) {
                $builder->where('realisasi.tahun', (int)$tahun);
            }
            if ($triwulan) {
                $builder->where('realisasi.triwulan', (int)$triwulan);
            }
            if ($programId) {
                $builder->where('kegiatan.program_id', (int)$programId);
            }
            $builder->orderBy('realisasi.created_at', 'DESC');
            $laporan = $builder->get()->getResultArray();
        }

        $data = [
            'title' => 'Verifikasi Laporan',
            'laporan' => $laporan
        ];
        return view('admin_bidang/verifikasi', $data);
    }

    public function detail_verifikasi($id)
    {
        $laporan = $this->db->table('realisasi')
            ->select('realisasi.*, opd.nama_opd, sub_kegiatan.nama_sub_kegiatan, kegiatan.nama_kegiatan, programs.nama_program')
            ->join('opd', 'opd.id = realisasi.opd_id')
            ->join('sub_kegiatan', 'sub_kegiatan.id = realisasi.sub_kegiatan_id')
            ->join('kegiatan', 'kegiatan.id = sub_kegiatan.kegiatan_id')
            ->join('programs', 'programs.id = kegiatan.program_id')
            ->where('realisasi.id', $id)
            ->get()->getRowArray();

        if (!$laporan) {
            return redirect()->to('/admin_bidang/verifikasi')->with('error', 'Laporan tidak ditemukan');
        }

        $data = [
            'title' => 'Detail Laporan',
            'laporan' => $laporan
        ];
        return view('admin_bidang/detail_verifikasi', $data);
    }

    public function update_status()
    {
        $id = $this->request->getPost('id');
        $status = $this->request->getPost('status');
        $catatan = $this->request->getPost('catatan');

        $this->db->table('realisasi')->where('id', $id)->update([
            'status' => $status,
            'catatan' => $catatan,
            'updated_at' => date('Y-m-d H:i:s')
        ]);

        return redirect()->to('/admin_bidang/verifikasi')->with('success', 'Status laporan berhasil diperbarui');
    }

    public function renja()
    {
        $session = session();
        $bidangId = $session->get('bidang_id');

        $opdList = $this->db->table('opd')->where('bidang_id', $bidangId)->get()->getResultArray();
        $opdIds = array_column($opdList, 'id');

        $programs = [];
        if (!empty($opdIds)) {
            $programs = $this->db->table('programs')
                ->select('programs.*, opd.nama_opd')
                ->join('opd', 'opd.id = programs.opd_id')
                ->whereIn('programs.opd_id', $opdIds)
                ->get()->getResultArray();
            $programIds = array_column($programs, 'id');
            if (!empty($programIds)) {
                $kegiatanCounts = $this->db->table('kegiatan')
                    ->select('program_id, COUNT(*) as cnt')
                    ->whereIn('program_id', $programIds)
                    ->groupBy('program_id')
                    ->get()->getResultArray();
                $countsMap = [];
                foreach ($kegiatanCounts as $kc) {
                    $countsMap[(int)$kc['program_id']] = (int)$kc['cnt'];
                }
                $paguRows = $this->db->table('sub_kegiatan')
                    ->select('kegiatan.program_id as program_id, SUM(sub_kegiatan.pagu_anggaran) as pagu')
                    ->join('kegiatan', 'kegiatan.id = sub_kegiatan.kegiatan_id')
                    ->whereIn('kegiatan.program_id', $programIds)
                    ->groupBy('kegiatan.program_id')
                    ->get()->getResultArray();
                $paguMap = [];
                foreach ($paguRows as $pr) {
                    $paguMap[(int)$pr['program_id']] = (float)$pr['pagu'];
                }
                foreach ($programs as &$p) {
                    $pid = (int)$p['id'];
                    $p['jumlah_kegiatan'] = $countsMap[$pid] ?? 0;
                    $p['total_pagu'] = $paguMap[$pid] ?? 0.0;
                }
                unset($p);
            }
        }

        $data = [
            'title' => 'Rencana Kerja OPD',
            'programs' => $programs,
            'opdList' => $opdList
        ];
        return view('admin_bidang/renja', $data);
    }

    public function program_detail($id)
    {
        $session = session();
        $bidangId = $session->get('bidang_id');
        $req = service('request');
        $tahun = (int) ($req->getGet('tahun') ?: date('Y'));
        $triwulan = (int) ($req->getGet('triwulan') ?: (int)ceil(date('n') / 3));
        $program = $this->db->table('programs')
            ->select('programs.*, opd.nama_opd')
            ->join('opd', 'opd.id = programs.opd_id')
            ->where('programs.id', (int)$id)
            ->get()->getRowArray();
        if (!$program) {
            return redirect()->to('/admin_bidang/renja')->with('error', 'Program tidak ditemukan');
        }
        $opdCheck = $this->db->table('opd')->where('id', $program['opd_id'])->where('bidang_id', $bidangId)->get()->getRowArray();
        if (!$opdCheck) {
            return redirect()->to('/admin_bidang/renja')->with('error', 'Akses ditolak');
        }
        $kegiatan = $this->db->table('kegiatan')
            ->where('program_id', (int)$id)
            ->orderBy('nama_kegiatan', 'ASC')
            ->get()->getResultArray();
        $kegiatanIds = array_column($kegiatan, 'id');
        $subList = [];
        $agg = [];
        if (!empty($kegiatanIds)) {
            $subList = $this->db->table('sub_kegiatan')
                ->whereIn('kegiatan_id', $kegiatanIds)
                ->orderBy('nama_sub_kegiatan', 'ASC')
                ->get()->getResultArray();
            foreach ($subList as $s) {
                $kid = (int)$s['kegiatan_id'];
                if (!isset($agg[$kid])) {
                    $agg[$kid] = ['jml' => 0, 'pagu' => 0.0];
                }
                $agg[$kid]['jml']++;
                $agg[$kid]['pagu'] += (float)$s['pagu_anggaran'];
            }
            foreach ($kegiatan as &$k) {
                $kid = (int)$k['id'];
                $k['jml_sub'] = $agg[$kid]['jml'] ?? 0;
                $k['total_pagu'] = $agg[$kid]['pagu'] ?? 0.0;
            }
            unset($k);
        }
        $statusMap = [];
        if (!empty($kegiatanIds)) {
            $rows = $this->db->table('realisasi')
                ->select('sub_kegiatan.kegiatan_id as kegiatan_id, realisasi.status, COUNT(*) as cnt')
                ->join('sub_kegiatan', 'sub_kegiatan.id = realisasi.sub_kegiatan_id')
                ->whereIn('sub_kegiatan.kegiatan_id', $kegiatanIds)
                ->where('realisasi.tahun', $tahun)
                ->where('realisasi.triwulan', $triwulan)
                ->groupBy('sub_kegiatan.kegiatan_id, realisasi.status')
                ->get()->getResultArray();
            foreach ($kegiatanIds as $kid) {
                $statusMap[$kid] = ['Diajukan' => 0, 'Disetujui' => 0, 'Draft' => 0, 'total' => 0];
            }
            foreach ($rows as $r) {
                $kid = (int)$r['kegiatan_id'];
                $st = $r['status'];
                $cnt = (int)$r['cnt'];
                if (!isset($statusMap[$kid])) {
                    $statusMap[$kid] = ['Diajukan' => 0, 'Disetujui' => 0, 'Draft' => 0, 'total' => 0];
                }
                if (!isset($statusMap[$kid][$st])) {
                    $statusMap[$kid][$st] = 0;
                }
                $statusMap[$kid][$st] += $cnt;
                $statusMap[$kid]['total'] += $cnt;
            }
        }
        $data = [
            'title' => 'Detail Program',
            'program' => $program,
            'kegiatan' => $kegiatan,
            'sub_list' => $subList,
            'tahun' => $tahun,
            'triwulan' => $triwulan,
            'status_map' => $statusMap
        ];
        return view('admin_bidang/program_detail', $data);
    }

    public function sub_kegiatan_store()
    {
        $session = session();
        $bidangId = $session->get('bidang_id');
        $programId = (int) $this->request->getPost('program_id');
        $kegiatanId = (int) $this->request->getPost('kegiatan_id');
        $kode = trim((string)$this->request->getPost('kode_sub_kegiatan'));
        $nama = trim((string)$this->request->getPost('nama_sub_kegiatan'));
        $target = trim((string)$this->request->getPost('target_kinerja'));
        $satuan = trim((string)$this->request->getPost('satuan'));
        $pagu = $this->parseCurrency((string)$this->request->getPost('pagu_anggaran'));

        $program = $this->db->table('programs')
            ->select('programs.*, opd.bidang_id')
            ->join('opd', 'opd.id = programs.opd_id')
            ->where('programs.id', $programId)
            ->get()->getRowArray();
        if (!$program || (int)$program['bidang_id'] !== (int)$bidangId) {
            return redirect()->to('/admin_bidang/renja')->with('error', 'Akses ditolak atau program tidak ditemukan');
        }
        $kegiatan = $this->db->table('kegiatan')
            ->where('id', $kegiatanId)
            ->where('program_id', $programId)
            ->get()->getRowArray();
        if (!$kegiatan) {
            return redirect()->to('/admin_bidang/program_detail/' . $programId)->with('error', 'Kegiatan tidak valid');
        }
        if ($nama === '') {
            return redirect()->to('/admin_bidang/program_detail/' . $programId)->with('error', 'Nama Sub Kegiatan wajib diisi');
        }

        $data = [
            'kegiatan_id' => $kegiatanId,
            'kode_sub_kegiatan' => $kode !== '' ? $kode : 'N/A',
            'nama_sub_kegiatan' => $nama,
            'target_kinerja' => $target !== '' ? $target : null,
            'satuan' => $satuan !== '' ? $satuan : null,
            'pagu_anggaran' => $pagu,
            'created_at' => date('Y-m-d H:i:s'),
            'updated_at' => date('Y-m-d H:i:s'),
        ];
        $this->db->table('sub_kegiatan')->insert($data);

        return redirect()->to('/admin_bidang/program_detail/' . $programId)->with('success', 'Sub Kegiatan berhasil ditambahkan');
    }

    public function program_store()
    {
        $session = session();
        $bidangId = $session->get('bidang_id');
        $opdId = (int)$this->request->getPost('opd_id');
        $kode = trim((string)$this->request->getPost('kode_program'));
        $nama = trim((string)$this->request->getPost('nama_program'));
        $opd = $this->db->table('opd')->where('id', $opdId)->where('bidang_id', $bidangId)->get()->getRowArray();
        if (!$opd || $nama === '') {
            return redirect()->to('/admin_bidang/renja')->with('error', 'Data tidak valid');
        }
        $this->db->table('programs')->insert([
            'opd_id' => $opdId,
            'kode_program' => $kode !== '' ? $kode : 'N/A',
            'nama_program' => $nama,
            'created_at' => date('Y-m-d H:i:s'),
            'updated_at' => date('Y-m-d H:i:s'),
        ]);
        return redirect()->to('/admin_bidang/renja')->with('success', 'Program berhasil ditambahkan');
    }

    public function kegiatan_store()
    {
        $session = session();
        $bidangId = $session->get('bidang_id');
        $programId = (int)$this->request->getPost('program_id');
        $kode = trim((string)$this->request->getPost('kode_kegiatan'));
        $nama = trim((string)$this->request->getPost('nama_kegiatan'));
        $program = $this->db->table('programs')
            ->select('programs.*, opd.bidang_id')
            ->join('opd', 'opd.id = programs.opd_id')
            ->where('programs.id', $programId)
            ->get()->getRowArray();
        if (!$program || (int)$program['bidang_id'] !== (int)$bidangId || $nama === '') {
            return redirect()->to('/admin_bidang/program_detail/' . $programId)->with('error', 'Data tidak valid');
        }
        $this->db->table('kegiatan')->insert([
            'program_id' => $programId,
            'kode_kegiatan' => $kode !== '' ? $kode : 'N/A',
            'nama_kegiatan' => $nama,
            'created_at' => date('Y-m-d H:i:s'),
            'updated_at' => date('Y-m-d H:i:s'),
        ]);
        return redirect()->to('/admin_bidang/program_detail/' . $programId)->with('success', 'Kegiatan berhasil ditambahkan');
    }

    public function input_progres()
    {
        // This page allows Admin Bidang to input progress on behalf of OPD if needed
        // Or view progress input history
        
        $session = session();
        $bidangId = $session->get('bidang_id');

        // Get OPD under this Bidang
        $opdList = $this->db->table('opd')->where('bidang_id', $bidangId)->get()->getResultArray();
        
        $data = [
            'title' => 'Input Progres OPD',
            'opdList' => $opdList
        ];
        return view('admin_bidang/input_progres', $data);
    }

    public function input_progres_form($opdId)
    {
        $session = session();
        $bidangId = $session->get('bidang_id');
        $opd = $this->db->table('opd')->where('id', (int)$opdId)->where('bidang_id', $bidangId)->get()->getRowArray();
        if (!$opd) {
            return redirect()->to('/admin_bidang/input_progres')->with('error', 'OPD tidak ditemukan atau bukan binaan Anda');
        }
        $req = service('request');
        $tahun = (int) ($req->getGet('tahun') ?: date('Y'));
        $triwulan = (int) ($req->getGet('triwulan') ?: (int)ceil(date('n') / 3));
        $programId = $req->getGet('program_id');
        $kegiatanId = $req->getGet('kegiatan_id');

        $programs = $this->db->table('programs')
            ->where('opd_id', (int)$opdId)
            ->orderBy('nama_program', 'ASC')
            ->get()->getResultArray();
        $kegiatanList = [];
        if ($programId) {
            $kegiatanList = $this->db->table('kegiatan')
                ->where('program_id', (int)$programId)
                ->orderBy('nama_kegiatan', 'ASC')
                ->get()->getResultArray();
        }

        $builderSubs = $this->db->table('sub_kegiatan')
            ->select('sub_kegiatan.*, kegiatan.nama_kegiatan, kegiatan.program_id')
            ->join('kegiatan', 'kegiatan.id = sub_kegiatan.kegiatan_id')
            ->join('programs', 'programs.id = kegiatan.program_id')
            ->where('programs.opd_id', (int)$opdId);
        if ($programId) {
            $builderSubs->where('kegiatan.program_id', (int)$programId);
        }
        if ($kegiatanId) {
            $builderSubs->where('sub_kegiatan.kegiatan_id', (int)$kegiatanId);
        }
        $subs = $builderSubs
            ->orderBy('kegiatan.nama_kegiatan', 'ASC')
            ->orderBy('sub_kegiatan.nama_sub_kegiatan', 'ASC')
            ->get()->getResultArray();
        $subIds = array_column($subs, 'id');
        $realisasiMap = [];
        if (!empty($subIds)) {
            $rows = $this->db->table('realisasi')
                ->whereIn('sub_kegiatan_id', $subIds)
                ->where('opd_id', (int)$opdId)
                ->where('tahun', $tahun)
                ->where('triwulan', $triwulan)
                ->get()->getResultArray();
            foreach ($rows as $r) {
                $realisasiMap[(int)$r['sub_kegiatan_id']] = $r;
            }
        }

        $data = [
            'title' => 'Input Progres untuk ' . $opd['nama_opd'],
            'opd' => $opd,
            'tahun' => $tahun,
            'triwulan' => $triwulan,
            'programs' => $programs,
            'program_id' => $programId,
            'kegiatan_list' => $kegiatanList,
            'kegiatan_id' => $kegiatanId,
            'subs' => $subs,
            'realisasiMap' => $realisasiMap
        ];
        return view('admin_bidang/input_progres_form', $data);
    }

    public function input_progres_save()
    {
        $opdId = (int)$this->request->getPost('opd_id');
        $tahun = (int)$this->request->getPost('tahun');
        $triwulan = (int)$this->request->getPost('triwulan');
        $status = $this->request->getPost('status') ?: 'Diajukan';
        $rk = $this->request->getPost('rk') ?? [];
        $ra = $this->request->getPost('ra') ?? [];
        $satuanPost = $this->request->getPost('satuan') ?? [];
        $targetPost = $this->request->getPost('target') ?? [];

        if ($opdId <= 0) {
            return redirect()->back()->with('error', 'OPD tidak valid');
        }
        $this->db->transBegin();
        try {
            foreach ($rk as $subId => $valRk) {
                $subId = (int)$subId;
                $valRk = trim((string)$valRk);
                $valRa = isset($ra[$subId]) ? $this->parseCurrency((string)$ra[$subId]) : 0.0;
                $satuan = isset($satuanPost[$subId]) ? trim((string)$satuanPost[$subId]) : null;
                $target = isset($targetPost[$subId]) ? trim((string)$targetPost[$subId]) : null;

                $existing = $this->db->table('realisasi')
                    ->where('sub_kegiatan_id', $subId)
                    ->where('opd_id', $opdId)
                    ->where('tahun', $tahun)
                    ->where('triwulan', $triwulan)
                    ->get()->getRowArray();
                $data = [
                    'sub_kegiatan_id' => $subId,
                    'opd_id' => $opdId,
                    'tahun' => $tahun,
                    'triwulan' => $triwulan,
                    'satuan' => $satuan ?: null,
                    'target_kinerja' => $target !== '' ? $target : null,
                    'realisasi_kinerja' => $valRk !== '' ? $valRk : null,
                    'realisasi_anggaran' => $valRa,
                    'status' => $status,
                    'updated_at' => date('Y-m-d H:i:s'),
                ];
                if (!$existing) {
                    $data['created_at'] = date('Y-m-d H:i:s');
                    $this->db->table('realisasi')->insert($data);
                } else {
                    $this->db->table('realisasi')->where('id', $existing['id'])->update($data);
                }
            }
            $this->db->transCommit();
        } catch (\Throwable $e) {
            $this->db->transRollback();
            return redirect()->back()->with('error', 'Gagal menyimpan: ' . $e->getMessage());
        }

        return redirect()->to('/admin_bidang/input_progres/form/' . $opdId . '?tahun=' . $tahun . '&triwulan=' . $triwulan)
            ->with('success', 'Progres berhasil disimpan');
    }

    private function parseCurrency(string $value): float
    {
        $v = trim($value);
        if ($v === '') {
            return 0.0;
        }
        $v = str_replace(['Rp', 'rp', ' '], '', $v);
        $negative = false;
        if (str_contains($v, '(') && str_contains($v, ')')) {
            $negative = true;
            $v = str_replace(['(', ')'], '', $v);
        }
        $v = preg_replace('/[^0-9,\\.-]/', '', $v) ?? '0';
        $lastComma = strrpos($v, ',');
        $lastDot = strrpos($v, '.');
        if ($lastComma !== false && $lastDot !== false) {
            if ($lastComma > $lastDot) {
                $v = str_replace('.', '', $v);
                $v = str_replace(',', '.', $v);
            } else {
                $v = str_replace(',', '', $v);
            }
        } elseif ($lastComma !== false && $lastDot === false) {
            $v = str_replace('.', '', $v);
            $v = str_replace(',', '.', $v);
        } else {
            $v = str_replace(',', '', $v);
        }
        $num = (float)$v;
        return $negative ? -1 * $num : $num;
    }
}
