<?php

class SipdController extends BaseController
{
    public function liveYears()
    {
        return new CollectionPaginatedResponse([2021, 2020, 2019]);
    }

    public function livePemdaList()
    {
        $getParams = $this->request->get();

        $params = $this->validate(
            $getParams,
            [
                'tipe' => ['validators' => ['required']],
                'tahun' => ['validators' => ['required', 'digit']],
            ]
        );

        if ($params['tipe'] != 'provinsi' && $params['tipe'] != 'kabkot') {
            throw new CustomErrorException(400, 'Nilai parameter tipe tidak valid');
        }

        $sipdClient = new SipdKemendagriClient($this->config["sipd_kemendagri_config"]);
        $sipdClient->setLogger($this->logger);
        try {
            $result = $sipdClient->pemdaList($params['tahun'], $params['tipe']);
            return new CollectionPaginatedResponse($result);
        } catch (\Exception $ex) {
            throw $ex;
        }
    }

    public function liveSkpdList()
    {
        $getParams = $this->request->get();

        $params = $this->validate(
            $getParams,
            [
                'kode_pemda' => ['validators' => ['required']],
                'tahun' => ['validators' => ['required', 'digit']],
            ]
        );

        $sipdClient = new SipdKemendagriClient($this->config["sipd_kemendagri_config"]);
        $sipdClient->setLogger($this->logger);
        try {
            $result = $sipdClient->skpdList($params['tahun'], $params['kode_pemda']);

            return new CollectionPaginatedResponse($result);
        } catch (\Exception $ex) {
            throw $ex;
        }
    }

    public function liveSkpdDetail()
    {
        $getParams = $this->request->get();

        $params = $this->validate(
            $getParams,
            [
                'kode_pemda' => ['validators' => ['required']],
                'kode_skpd' => ['validators' => ['required']],
                'tahun' => ['validators' => ['required', 'digit']],
            ]
        );

        $sipdClient = new SipdKemendagriClient($this->config["sipd_kemendagri_config"]);
        $sipdClient->setLogger($this->logger);
        try {
            $result = $sipdClient->skpdDetail($params['tahun'], $params['kode_pemda'], $params['kode_skpd']);

            return new CollectionPaginatedResponse($result);
        } catch (\Exception $ex) {
            throw $ex;
        }
    }

    public function liveYearList()
    {
        $getParams = $this->request->get();

        $params = $this->validate(
            $getParams,
            [
                'kode_pemda' => ['validators' => ['required']],
            ]
        );

        $sipdClient = new SipdKemendagriClient($this->config["sipd_kemendagri_config"]);
        $sipdClient->setLogger($this->logger);
        try {
            $result = $sipdClient->yearList($params['kode_pemda']);

            return new CollectionPaginatedResponse($result);
        } catch (\Exception $ex) {
            throw $ex;
        }
    }

    public function years()
    {
        $years = SipdSkpd::find(
            [
                "columns" => "distinct(tahun) as tahun",
                "order" => "tahun asc"
            ]
        )
            ->toArray();
        return new CollectionPaginatedResponse($years);
    }

    public function pemdaList()
    {
        $getParams = $this->request->get();

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : "";

        $params = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'tahun' => ['validators' => ['required', 'digit']],
                'keyword' => ['validators' => []],
                'type' => ['validators' => []]
            ]
        );

        if ($params['type'] == 1) {
            // Prov
            $result = $this->rawQuery('
            SELECT prov."NAMA_PROP" as nama, prov."KODE" as kode
            FROM jaga.kemdagri_mkode_prov prov
                JOIN (SELECT DISTINCT(kode_pemda) as kode_pemda
                        FROM jaga.sipd_skpd
                        WHERE tahun = :tahun
                    ) s on s.kode_pemda = prov."KODE"
            WHERE prov."NAMA_PROP" ilike :keyword', ["tahun" => $params['tahun'], "keyword" => '%' . $params['keyword'] . '%']);
        } else {
            // KabKot
            $result = $this->rawQuery(
                '
            SELECT kk."NAMA_KAB" as nama, kk."KODE" as kode, kk."NAMA_PROP" as provinsi
            FROM jaga.kemdagri_mkode_kabkot kk
                JOIN (SELECT DISTINCT(kode_pemda) as kode
                        FROM jaga.sipd_skpd
                        WHERE tahun = :tahun
                    ) s on s.kode = kk."KODE"
            WHERE kk."NAMA_KAB" ilike :keyword',
                [
                    'tahun' => $params['tahun'],
                    'keyword' => '%' . $params['keyword'] . '%'
                ]
            );
        }

        return new CollectionPaginatedResponse($result);
    }

    public function skpdList()
    {
        $getParams = $this->request->get();

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['tahun'] = array_key_exists('tahun', $getParams) ? $getParams['tahun'] : '';
        $getParams['kode_pemda'] = array_key_exists('kode_pemda', $getParams) ? $getParams['kode_pemda'] : '';

        $params = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'tahun' => ['validators' => ['required', 'digit']],
                'kode_pemda' => ['validators' => ['required']],
                'keyword' => ['validators' => []]
            ]
        );

        $skpdList = SipdSkpd::find([
            'conditions' => 'urai ilike :keyword: AND kode_pemda=:kode_pemda: AND tahun=:tahun:',
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%',
                'kode_pemda' => $params['kode_pemda'],
                'tahun' => $params['tahun']
            ],
            'limit' => $params['limit'],
            'offset' => $params['offset'],
        ]);

        $count = SipdSkpd::count([
            'conditions' => 'urai ilike :keyword: AND kode_pemda=:kode_pemda: AND tahun=:tahun:',
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%',
                'kode_pemda' => $params['kode_pemda'],
                'tahun' => $params['tahun']
            ]
        ]);

        return new PaginatedResponse($skpdList, $count, $params['limit'], $params['offset']);
    }

    public function skpdDetail($id)
    {
        $skpd = SipdSkpd::findFirst($id);

        if (empty($skpd)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'SKPD tidak ditemukan');
        }

        return new CollectionPaginatedResponse([$skpd->toDetail()]);
    }
    public function listTahunSkpd($kode)
    {
        $result = $this->rawQuery("
            SELECT DISTINCT (tahun) FROM jaga.sipd_skpd WHERE kode_pemda = :kode_pemda
        ", ['kode_pemda' => $kode]);

        return new CollectionPaginatedResponse($result);
    }
}