<?php

class LogPpjErrorsController extends BaseController
{
    public function list()
    {
        $this->checkScopes(['admin.view']);

        $rawParams = $this->request->get();
        $rawParams['limit'] = array_key_exists("limit", $rawParams) ? $rawParams['limit'] : 10;
        $rawParams['offset'] = array_key_exists("offset", $rawParams) ? $rawParams['offset'] : 0;
        $rawParams['kabupaten_kota_code'] = array_key_exists("kabupaten_kota_code", $rawParams) ? $rawParams['kabupaten_kota_code'] : null;
        $rawParams['periode'] = array_key_exists("periode", $rawParams) ? $rawParams['periode'] : null;
        $rawParams['trashed'] = array_key_exists("trashed", $rawParams) ? $rawParams['trashed'] : null;
        $rawParams['halaman'] = array_key_exists("halaman", $rawParams) ? $rawParams['halaman'] : null;

        $params = $this->validate($rawParams, [
            'limit' => ['validators' => ['digit', 'required']],
            'offset' => ['validators' => ['digit', 'required']],
            'kabupaten_kota_code' => ['validators' => ['digit']],
            'periode' => ['validators' => ['digit']],
            'trashed' => ['validators' => []],
            'halaman' => ['validators' => ['digit']],
        ]);

        $conditions = "1 = 1";
        $bind = [];
        if (empty($params['trashed'])) {
            $conditions .= " and deleted_at is null";
        }

        if (!empty($params['kabupaten_kota_code'])) {
            $conditions .= " and kabupaten_kota_code = :kabupaten_kota_code:";
            $bind['kabupaten_kota_code'] = $params['kabupaten_kota_code'];
        }

        if (!empty($params['periode'])) {
            $conditions .= " and periode = :periode:";
            $bind['periode'] = $params['periode'];
        }

        if (!empty($params['halaman'])) {
            $conditions .= " and halaman = :halaman:";
            $bind['halaman'] = $params['halaman'];
        }

        $rows = LogPpjErrors::find([
//            'columns' => 'kabupaten_kota_code, instansi',
            'conditions' => $conditions,
            'bind' => $bind,
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'order' => 'id desc'
        ]);

        $total = LogPpjErrors::count([
            'conditions' => $conditions,
            'bind' => $bind
        ]);

        return new PaginatedResponse($rows, $total, $params['limit'], $params['offset']);
    }

    public function update($id) {
        $this->checkScopes(['admin.view']);

        /** @var $data LogPpjErrors */
        $data = LogPpjErrors::findFirst($id);
        if (!$data) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data tidak ditemukan");
        }

        $request = $this->request->getJsonRawBody(true);
        $payload = $this->validate($request,
            [
                'retry_count' => [
                    'validators' => [
                        'required',
                        'digit',
                        [
                            'name' => 'min',
                            'params' => 0
                        ],
                        [
                            'name' => 'max',
                            'params' => intval(AppConfig::getValueByKode("MAX_RETRY_PPJ_ERROR"))
                        ]
                    ]
                ]
            ]
        );

        $data->retry_count = $payload['retry_count'];
        if (!$data->save()) {
            $this->throwObjectValidationException($data);
        }

        return new ObjectResponse(['success' => true]);
    }

    public function updateByPeriod() {
        $this->checkScopes(['admin.view']);

        $request = $this->request->getJsonRawBody(true);
        $payload = $this->validate($request,
            [
                'periode' => [
                    'validators' => [
                        'required',
                        'digit'
                    ]
                ],
                'retry_count' => [
                    'validators' => [
                        'required',
                        'digit',
                        [
                            'name' => 'min',
                            'params' => 0
                        ],
                        [
                            'name' => 'max',
                            'params' => intval(AppConfig::getValueByKode("MAX_RETRY_PPJ_ERROR"))
                        ]
                    ]
                ]
            ]
        );

        $res = $this->rawQuery("UPDATE jaga.log_ppj_errors SET retry_count = :retry_count WHERE periode = :periode",
            [
                'retry_count' => intval($payload['retry_count']),
                'periode' => intval($payload['periode'])
            ]
        );
        return new SimpleResponse([
            'affected' => count($res)
        ]);
    }

    public function insertByPeriod() {
        $this->checkScopes(['admin.view']);

        $request = $this->request->getJsonRawBody(true);
        $payload = $this->validate($request,
            [
                'periode' => [
                    'validators' => [
                        'required',
                        'digit'
                    ]
                ],
                'pemda' => [
                    'validators' => [
                        'required'
                    ]
                ],
                'halaman' => [
                    'validators' => [
                        'digit',
                        [
                            'name' => 'inclusion_in',
                            'params' => [1, 2, 3]
                        ]
                    ]
                ]
            ]
        );

        if (!is_array($payload['pemda'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Format payload tidak valid");
        }

        $this->db->begin();
        try {
            foreach ($payload['pemda'] as $pemda) {
                $load = $this->validate($pemda,[
                    'kabupaten_kota_code' => ['validators' => ['required', 'digit']],
                    'instansi' => ['validators' => ['required']]
                ]);
                $log = new LogPpjErrors($load);
                $log->periode = $payload['periode'];
                $log->retry_count = 0;
                $log->halaman = $payload['halaman'];
                if (!$log->save()) {
                    $this->throwObjectValidationException($log);
                }
            }
            $this->db->commit();
            return new SimpleResponse([
                'affected' => count($payload['pemda'])
            ]);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function listPemdaNotCompleted() {
        $this->checkScopes(['admin.view']);

        $rawParams = $this->request->get();
        $params = $this->validate($rawParams,
            [
                'periode' => [
                    'validators' => [
                        'required',
                        'digit'
                    ]
                ],
                'halaman' => [
                    'validators' => [
                        'digit',
                        [
                            'name' => 'inclusion_in',
                            'params' => [null, 1, 2, 3]
                        ]
                    ]
                ]
            ]
        );
        $periode = $params['periode'];

        if (!empty($params['halaman'])) {
            if($params['halaman'] == 1) {
                $ppj = 'ppj_hal1';
            } elseif ($params['halaman'] == 2) {
                $ppj = 'ppj_hal2';
            } elseif ($params['halaman'] == 3) {
                $ppj = 'ppj_piutang_pemda';
            } else {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Halaman tidak valid");
            }
            $instansis = $this->rawQuery("
                WITH params AS (SELECT $periode::int AS thbllap),
    
                inst AS (
                  SELECT
                    i.nama,
                    i.id_provinsi,
                    i.id_kota_kabupaten
                  FROM jaga.instansi i, params
                  WHERE i.deleted_at IS NULL
                    AND i.id_tipe = 2
                    AND i.id_kota_kabupaten IS NOT NULL
                ),
                
                present_all AS (
                  SELECT kd_kabmendagri
                  FROM (
                    SELECT DISTINCT kd_kabmendagri
                    FROM jaga.".$ppj." h1, params
                    WHERE h1.deleted_at IS NULL AND h1.thbllap = params.thbllap
                  ) a
                )
                
                SELECT i.nama, i.id_provinsi, i.id_kota_kabupaten
                FROM inst i
                LEFT JOIN present_all pa
                  ON pa.kd_kabmendagri::INT = i.id_kota_kabupaten
                WHERE pa.kd_kabmendagri IS NULL
                ORDER BY i.nama
                ;
            ");
        } else {
            $instansis = $this->rawQuery("
                WITH params AS (SELECT $periode::int AS thbllap),
    
                inst AS (
                  SELECT
                    i.nama,
                    i.id_provinsi,
                    i.id_kota_kabupaten
                  FROM jaga.instansi i, params
                  WHERE i.deleted_at IS NULL
                    AND i.id_tipe = 2
                    AND i.id_kota_kabupaten IS NOT NULL
                ),
                
                present_all AS (
                  SELECT kd_kabmendagri
                  FROM (
                    SELECT DISTINCT kd_kabmendagri
                    FROM jaga.ppj_hal1 h1, params
                    WHERE h1.deleted_at IS NULL AND h1.thbllap = params.thbllap
                  ) a
                  INTERSECT
                  SELECT kd_kabmendagri
                  FROM (
                    SELECT DISTINCT kd_kabmendagri
                    FROM jaga.ppj_hal2 h2, params
                    WHERE h2.deleted_at IS NULL AND h2.thbllap = params.thbllap
                  ) b
                  INTERSECT
                  SELECT kd_kabmendagri
                  FROM (
                    SELECT DISTINCT kd_kabmendagri
                    FROM jaga.ppj_piutang_pemda p, params
                    WHERE p.deleted_at IS NULL AND p.thbllap = params.thbllap
                  ) c
                )
                
                SELECT i.nama, i.id_provinsi, i.id_kota_kabupaten
                FROM inst i
                LEFT JOIN present_all pa
                  ON pa.kd_kabmendagri::INT = i.id_kota_kabupaten
                WHERE pa.kd_kabmendagri IS NULL
                ORDER BY i.nama
                ;
            ");
        }

        return new CollectionPaginatedResponse($instansis);
    }
}