<?php

class KemenkeuApbdController extends BaseController
{
    public function getWilayah() {
        $params = $this->validate(
            $this->request->get(),
            [
                'search' => [],
            ]
        );

        $result = KemenkeuMasterPemda::query();
        $conditions = "kodekabkota IS NULL AND kodeprovinsi IS NOT NULL";
        $bind = [];

        if($params['search'] != null && !empty($params['search'])){
            $conditions .= " AND namapemda ILIKE :namapemda:";
            $bind['namapemda'] = '%' . $params['search'] . '%';
        }
        $result->where($conditions);
        if(!empty($bind)) $result->bind($bind);

        $result = $result->orderBy("namapemda")->execute()->toArray();

        return new SimpleResponse($result);
    }

    public function getSubWilayah() {
        $params = $this->validate(
            $this->request->get(),
            [
                'kodeprovinsi' => ['validators' => ['required']],
                'search' => [],
            ]
        );

        $result = KemenkeuMasterPemda::query();
        $conditions = "kodekabkota IS NOT NULL AND kodeprovinsi = :kodeprovinsi:";
        $bind = [
            "kodeprovinsi" => $params['kodeprovinsi']
        ];

        if($params['search'] != null && !empty($params['search'])){
            $conditions .= " AND namapemda ILIKE :namapemda:";
            $bind['namapemda'] = '%' . $params['search'] . '%';
        }
        $result->where($conditions);
        if(!empty($bind)) $result->bind($bind);

        $result = $result->orderBy("namapemda")->execute()->toArray();

        return new SimpleResponse($result);
    }

    public function getApbd()
    {
        $params = $this->validate(
            $this->request->get(),
            [
                'tipe' => ['validators' => ['required'], [
                    'name' => 'inclusion_in',
                    'params' => ['tkdd', 'apbd']
                ]],
                'tahun' => ['validators' => ['required']],
                'kodewilayah' => ['validators' => ['required']],
                'kodesubwilayah' => [],
            ]
        );

        $kodePemda = null;

        if ($params['kodesubwilayah'] != null && !empty($params['kodesubwilayah']))
            $kodePemda = $params['kodesubwilayah'];
        else if ($params['kodewilayah'] != null && !empty($params['kodewilayah']))
            $kodePemda = $params['kodewilayah'];

        $result = [];

        if($params['tipe'] == 'apbd') {
            $result = $this->getDataApbd($params, $kodePemda);
        }
        else if($params['tipe'] == 'tkdd') {
            $result = $this->getDataTkdd($params, $kodePemda);
        }

        return new SimpleResponse($result);
    }

    public function getTahun() {
        $tahuns = $this->rawQuery("
            SELECT DISTINCT tahun FROM jaga.kemenkeu_realisasi_apbd
            UNION
            SELECT DISTINCT tahun FROM jaga.stunting_anggaran
            UNION
            SELECT DISTINCT tahun FROM jaga.stunting_realisasi
            ORDER BY tahun ASC;
        ");

        return new SimpleResponse(array_column($tahuns, 'tahun'));
    }

    public function syncApbd()
    {
        $this->hasScopes(['admin.view']);
        AuthHelper::validateToolsKey($this->request, $this->config);

        $rawParams =  $this->request->getJsonRawBody(true);
        $params = $this->validate($rawParams, [
            'lower' => ['validators' => ['required', 'digit']],
            'upper' => ['validators' => ['required', 'digit']],
        ]);
        $lower = $params['lower'];
        $upper = $params['upper'];

        try {
            $before = $this->rawQuery("SELECT tahun, count(tahun) as total FROM kemenkeu_realisasi_apbd GROUP BY tahun ORDER BY tahun DESC;");
            $processedApbd = 0;
            $client = new KemenkeuClient($this->config["kemenkeu_config"]);
            $client->requestTokenRealisasiApbd();
            $dataApbd = $client->getRealisasiApbd($lower . '-' . $upper);
            foreach ($dataApbd as $itemApbd) {
                $client->saveDataApbd($itemApbd);
                $processedApbd++;
            }
            $after = $this->rawQuery("SELECT tahun, count(tahun) as total FROM kemenkeu_realisasi_apbd GROUP BY tahun ORDER BY tahun DESC;");
            return new SimpleResponse([
                'processedApbd' => $processedApbd,
                'before' => $before,
                'after' => $after
            ]);
        } catch (\Exception $e) {
            throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, $e->getMessage());
        }
    }

    private function getDataApbd($params, $kodePemda)
    {
        $result = [];
        $data = KemenkeuRealisasiApbd::query()
            ->where('tahun = :tahun: AND kodepemda = :kodepemda:')
            ->bind([
                'tahun' => $params['tahun'],
                'kodepemda' => $kodePemda
            ])
            ->orderBy("level1coa90, level2coa90, level3coa90")
            ->execute()
            ->toArray();

        //process data to tree

        foreach ($data as $item) {
            if (!array_key_exists($item['level1coa90'], $result))
                $result[$item['level1coa90']] = [
                    'name' => $item['level1coa90'],
                    'nilaianggaran' => 0.0,
                    'nilairealisasi' => 0.0,
                    'persentase' => 0.0,
                    'totalitem' => 0,
                    'level2' => []
                ];

            if (!array_key_exists($item['level2coa90'], $result[$item['level1coa90']]['level2']))
                $result[$item['level1coa90']]['level2'][$item['level2coa90']] = [
                    'name' => $item['level2coa90'],
                    'nilaianggaran' => 0.0,
                    'nilairealisasi' => 0.0,
                    'persentase' => 0.0,
                    'totalitem' => 0,
                    'level3' => []
                ];

            $result[$item['level1coa90']]['level2'][$item['level2coa90']]['level3'][] = $item;
            $result[$item['level1coa90']]['level2'][$item['level2coa90']]['nilaianggaran'] += $item['nilaianggaran'];
            $result[$item['level1coa90']]['level2'][$item['level2coa90']]['nilairealisasi'] += $item['nilairealisasi'];
            $result[$item['level1coa90']]['level2'][$item['level2coa90']]['persentase'] += $item['persentase'];
            $result[$item['level1coa90']]['level2'][$item['level2coa90']]['totalitem']++;
            $result[$item['level1coa90']]['nilaianggaran'] += $item['nilaianggaran'];
            $result[$item['level1coa90']]['nilairealisasi'] += $item['nilairealisasi'];
            $result[$item['level1coa90']]['persentase'] += $item['persentase'];
            $result[$item['level1coa90']]['totalitem']++;
        }

        foreach ($result as $key => $item) {
            if ($item['totalitem'] > 0)
                $result[$key]['persentase'] = 1.0 * $item['persentase'] / $item['totalitem'];

            foreach ($item['level2'] as $key2 => $level2) {
                if ($level2['totalitem'] > 0)
                    $result[$key]['level2'][$key2]['persentase'] = 1.0 * $level2['persentase'] / $level2['totalitem'];
            }
        }

        return $result;
    }

    private function getDataTkdd($params, $kodePemda)
    {
        $result = [];
        $data = KemenkeuTkdd::query()
            ->where('tahun = :tahun: AND kodepemda = :kodepemda:')
            ->bind([
                'tahun' => $params['tahun'],
                'kodepemda' => $kodePemda
            ])
            ->orderBy("urmap1, urakun")
            ->execute()
            ->toArray();

        $result = [
            0 => [
                "name" => "Transfer Ke Daerah",
                "pagu" => 0.0,
                "realisasi" => 0.0,
                "prosen" => 0.0,
                "totalitem" => 0.0,
                "level2" => []
            ],
            1 => [
                "name" => "Total TKDD",
                "pagu" => 0.0,
                "realisasi" => 0.0,
                "prosen" => 0.0,
                "totalitem" => 0.0,
                "level2" => []
            ]
        ];

        foreach ($data as $item) {
            if(!array_key_exists($item['urmap1'], $result[0]['level2']))
                $result[0]['level2'][$item['urmap1']] = [
                    "name" => $item['urmap1'],
                    "pagu" => 0.0,
                    "realisasi" => 0.0,
                    "prosen" => 0.0,
                    "totalitem" => 0.0,
                    "level3" => []
                ];

            $result[0]['level2'][$item['urmap1']]['level3'][] = $item;
            $result[0]['level2'][$item['urmap1']]['pagu'] += $item['pagu'];
            $result[0]['level2'][$item['urmap1']]['realisasi'] += $item['realisasi'];
            $result[0]['level2'][$item['urmap1']]['prosen'] += $item['prosen'];
            $result[0]['level2'][$item['urmap1']]['totalitem']++;
            $result[0]['pagu'] += $item['pagu'];
            $result[0]['realisasi'] += $item['realisasi'];
            $result[0]['prosen'] += $item['prosen'];
            $result[0]['totalitem']++;
            $result[1]['pagu'] += $item['pagu'];
            $result[1]['realisasi'] += $item['realisasi'];
            $result[1]['prosen'] += $item['prosen'];
            $result[1]['totalitem']++;
        }

        foreach ($result as $key => $item) {
            if ($item['totalitem'] > 0)
                $result[$key]['prosen'] = 1.0 * $item['prosen'] / $item['totalitem'];

            foreach ($item['level2'] as $key2 => $level2) {
                if ($level2['totalitem'] > 0)
                    $result[$key]['level2'][$key2]['prosen'] = 1.0 * $level2['prosen'] / $level2['totalitem'];
            }
        }

        return $result;
    }
}