<?php

class LitbangKajianController extends BaseController
{
    public function dashboard()
    {
        $summaryPerYear = $this->rawQuery("
            SELECT tahun, count(*) as total
            FROM jaga.litbang_kajian
            WHERE deleted_at is null
            GROUP BY tahun
            ORDER BY tahun
        ", []);

        $summaryPerSektor = $this->rawQuery("
            SELECT sektor.id, sektor.nama, count(*) as total
            FROM jaga.litbang_kajian kajian
                JOIN jaga.litbang_kajian_sektor sektor on kajian.sektor_id = sektor.id
            WHERE kajian.deleted_at is null
            GROUP BY sektor.id, sektor.nama
            ORDER BY total
        ");

        $summaryPerInstansi = $this->rawQuery(
            "
            SELECT i.id, i.nama, count(*) as total
            FROM jaga.litbang_kajian_klpd lkk
                JOIN jaga.instansi i on lkk.instansi_id = i.id 
            WHERE lkk.deleted_at is null
            GROUP BY i.id, i.nama
            ORDER BY total DESC
        ",
            []
        );

        return new SimpleResponse([
            'summary_per_year' => $summaryPerYear,
            'summary_per_sektor' => $summaryPerSektor,
            'summary_per_instansi' => $summaryPerInstansi
        ]);
    }

    public function years()
    {
        $result = $this->rawQuery("SELECT distinct(tahun) FROM jaga.litbang_kajian", []);
        $finalResult = array_map(function ($el) {
            return $el['tahun'];
        }, $result);

        return new CollectionPaginatedResponse($finalResult);
    }

    private function baseList($withDeletedAt)
    {
        $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['sektor_id'] = array_key_exists('sektor_id', $getParams) ? $getParams['sektor_id'] : -1;
        $getParams['instansi_id'] = array_key_exists('instansi_id', $getParams) ? $getParams['instansi_id'] : -1;
        $getParams['tahun'] = array_key_exists('tahun', $getParams) ? $getParams['tahun'] : -1;
        $getParams['instansi'] = array_key_exists('instansi', $getParams) ? $getParams['instansi'] : '';
        $getParams['order_by'] = array_key_exists('order_by', $getParams) ? strtolower($getParams['order_by']) : 'id';
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? strtolower($getParams['order_direction']) : 'asc';

        $allowed_order = [
            'id',
            'judul',
            'tahun'
            // 'sektor_id'
        ];
        $allowed_order_direction = ['asc', 'desc'];

        $params =  $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'tahun' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'instansi' => ['validators' => []],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]]
            ]
        );

        $deletedAtCondition = $withDeletedAt ? 'AND kajian.deleted_at is null' : '';
        $orderBy = $params['order_by'];
        $orderDirection = $params['order_direction'];
        $queryParams = [
            'instansi_id' => $getParams['instansi_id'],
            // 'sektor_id' => $getParams['sektor_id'],
            'tahun' => $getParams['tahun'],
            'keyword' => '%' . $getParams['keyword'] . '%',
            'limit' => $getParams['limit'],
            'offset' => $getParams['offset']
        ];

        $instansiCondition = "";
        if (!empty($getParams['instansi'])) {
            $instansiCondition = "HAVING string_agg(instansi.nama, ';') ilike :instansi";
            $queryParams['instansi'] = '%' . $getParams['instansi'] . '%';
        }

        $mainQuery = "
            FROM jaga.litbang_kajian kajian
                -- JOIN jaga.litbang_kajian_sektor sektor ON kajian.sektor_id = sektor.id
                -- JOIN jaga.litbang_kajian_jenis jenis ON kajian.jenis_id = jenis.id
                LEFT JOIN jaga.attachments attachments on kajian.attachment_id = attachments.id
                LEFT JOIN jaga.litbang_kajian_klpd klpd ON klpd.kajian_id = kajian.id
                LEFT JOIN jaga.instansi instansi ON instansi.id = klpd.instansi_id
            WHERE
                (0 > :instansi_id OR :instansi_id = klpd.instansi_id)
                -- AND (0 > :sektor_id OR kajian.sektor_id = :sektor_id)
                AND (0 > :tahun OR kajian.tahun = :tahun)
                AND kajian.judul ilike :keyword
                $deletedAtCondition
            GROUP BY 
                kajian.id, 
                kajian.judul,
                kajian.tahun,
                kajian.kode,
                -- sektor.nama,
                -- jenis.nama,
                kajian.attachment_id,
                attachments.uuid,
                attachments.filename
            $instansiCondition
        ";


        $result = $this->rawQuery("
            SELECT
                kajian.id,
                kajian.judul,
                kajian.tahun,
                kajian.kode,
                -- sektor.nama as sektor_label,
                -- jenis.nama as jenis_label,
                kajian.deleted_at,
                kajian.attachment_id,
                attachments.uuid as attachment_uuid,
                attachments.filename as attachment_filename,
                string_agg(instansi.nama, ';') as instansi_list
            $mainQuery
            ORDER BY $orderBy $orderDirection
            LIMIT :limit
            OFFSET :offset
        ", $queryParams);

        unset($queryParams['limit']);
        unset($queryParams['offset']);

        $total_result = $this->rawQuery(
            "
            SELECT DISTINCT COUNT(*) OVER ()
            $mainQuery
        ",
            $queryParams
        );

        $total_record = 0;
        if (!empty($total_result)) {
            $total_record = $total_result[0]['count'];
        }

        return new PaginatedResponse($result, $total_record, $params['limit'], $params['offset']);
    }

    public function list()
    {
        return $this->baseList(true);
    }

    public function all()
    {
        $this->checkScopes(['litbang.access']);

        return $this->baseList(false);
    }

    public function detail($id)
    {
        $record = LitbangKajian::findFirst([
            'conditions' => 'id = :id: AND deleted_at is null',
            'bind' => [
                'id' => $id
            ]
        ]);

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

        $result = $record->toDetail();

        return new SimpleResponse($result);
    }

    public function getRandom()
    {
        $randomRecords = $this->rawQuery("
            SELECT id
            FROM jaga.litbang_kajian
            WHERE deleted_at is null and attachment_id is not null and attachment_id > 0
            ORDER BY random()
            LIMIT 1
        ", []);

        if (count($randomRecords) < 1) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Kajian dengan cover tidak ditemukan');
        }

        return $this->detail($randomRecords[0]['id']);
    }

    public function getLatest()
    {
        $latest = $this->rawQuery("
            SELECT
                id
            FROM
                jaga.litbang_kajian
            WHERE
                deleted_at IS NULL
                AND attachment_id IS NOT NULL
                AND attachment_id > 0
            ORDER BY
                id DESC
            LIMIT
                1
        ", []);

        return $this->detail($latest[0]['id']);
    }

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

        $record = LitbangKajian::findFirst([
            'conditions' => 'id = :id: AND deleted_at is null',
            'bind' => [
                'id' => $id
            ]
        ]);


        if (empty($record)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Kajian Tidak Ditemukan');
        }

        // $rawBody = $this->request->getJsonRawBody(true);
        $rawBody = $this->request->getPost();

        $params = $this->validate(
            $rawBody,
            [
                'kode' => ['validators' => []],
                'sektor_id' => ['validators' => []],
                'jenis_id' => ['validators' => []],
                'judul' => ['validators' => ['required']],
                'deskripsi' => ['validators' => ['required']],
                'tahun' => ['validators' => ['required', 'digit']],
                'potensi_penyelamatan_keuangan' => ['validators' => []],
                'daftar_pustaka' => ['validators' => []],
                'attachment_uuid' => ['validators' => []],
                'kajian_document' => ['validators' => []],
                'kajian_document_from_db' => ['validators' => []]
            ]
        );

        // if ($record->sektor_id != $params['sektor_id']) {
        //     $sektor = LitbangKajianSektor::findFirst([
        //         'conditions' => 'id = :sektor_id: AND deleted_at is null',
        //         'bind' => [
        //             'sektor_id' => $params['sektor_id']
        //         ]
        //     ]);

        //     if (empty($sektor)) {
        //         throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sektor tidak ditemukan');
        //     }
        // }

        // if ($record->jenis_id != $params['jenis_id']) {
        //     $jenis = LitbangKajianJenis::findFirst([
        //         'conditions' => 'id = :jenis_id: AND deleted_at is null',
        //         'bind' => [
        //             'jenis_id' => $params['jenis_id']
        //         ]
        //     ]);

        //     if (empty($jenis)) {
        //         throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Jenis tidak ditemukan');
        //     }
        // }

        if (!empty($params['kajian_document']) && !empty($params['kajian_document_from_db'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Dokumen yang lama masih tersimpan');
        }

        $record->kode = $params['kode'];
        // $record->sektor_id = $params['sektor_id'];
        // $record->jenis_id = $params['jenis_id'];
        $record->judul = $params['judul'];
        $record->deskripsi = $params['deskripsi'];
        $record->tahun = $params['tahun'];
        // $record->potensi_penyelamatan_keuangan = $params['potensi_penyelamatan_keuangan'];
        // $record->daftar_pustaka = $params['daftar_pustaka'];
        $record->updated_at = date('Y-m-d H:i:s');

        if (empty($params['kajian_document_from_db'])) {
            $record->kajian_document_uuid = null;
        }

        $files = $this->request->getUploadedFiles();
        foreach ($files as $file) {
            if ($file->getKey() === 'kajian_document') {
                $params['kajian_document'] = $file;
            }
        }

        // upload new kajian document
        $document = null;
        if (!empty($params['kajian_document'])) {
            $document = LitbangService::uploadDocument(
                $this->shared,
                $this->minio,
                $this->request,
                LitbangKajianPengajuan::DOCUMENT_DIR,
                ['application/pdf']
            );
        }

        if (!empty($params['attachment_uuid'])) {
            $attachment_record = Attachments::findFirstByUuid($params['attachment_uuid']);

            if (!empty($attachment_record) && empty($attachment_record->deleted_at)) {
                if ($record->attachment_id > 0) { //remove old cover
                    $oldAttachment = Attachments::findFirst([
                        'conditions' => 'id = :id: AND deleted_at is null',
                        'bind' => [
                            'id' => $record->attachment_id
                        ]
                    ]);

                    if ($oldAttachment->id != $attachment_record->id) {
                        AttachmentHelper::delete($oldAttachment, true, $this->minio, 'litbang-kajian-cover');
                    }
                }

                $record->attachment_id = $attachment_record->id;

                $attachment_record->status = Attachments::STATUS_REFERRED;
                $attachment_record->referer = Attachments::REFERER_LITBANG_KAJIAN_COVER;
                $attachment_record->save();
            }
        }

        // replace document kajian with the new one
        if (!empty($document[0]->uuid)) {
            $attachment_record = Attachments::findFirstByUuid($document[0]->uuid);

            if (!empty($attachment_record) && empty($attachment_record->deleted_at)) {
                $record->kajian_document_uuid = $attachment_record->uuid;

                $attachment_record->status = Attachments::STATUS_REFERRED;
                $attachment_record->referer = Attachments::REFERER_LITBANG_KAJIAN_DOCUMENT;
                $attachment_record->save();
            }
        }

        $status = $record->save();

        if (!$status) {
            $errors = $record->getMessage();
            $error_messages = [];

            foreach ($errors as $key => $error) {
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessage()
                ];
            }

            throw new ValidationsException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$record]);
        }
    }

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

        $record = LitbangKajian::findFirst([
            'conditions' => 'id = :id: AND deleted_at is null',
            'bind' => [
                'id' => $id
            ]
        ]);


        if (empty($record)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Kajian Tidak Ditemukan');
        }

        $now = date('Y-m-d H:i:s');
        $record->updated_at = $now;
        $record->deleted_at = $now;
        $status = $record->save();

        if (!$status) {
            $errors = $record->getMessages();
            $error_messages = [];

            foreach ($errors as $key => $error) {
                $error_messages[] = ["field" => $error->getField(), "message" => $error->getMessages()];
            }

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$record]);
        }
    }

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

        $record = LitbangKajian::findFirst([
            'conditions' => 'id = :id: AND deleted_at is not null',
            'bind' => [
                'id' => $id
            ]
        ]);

        if (empty($record)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Kajian Terhapus Tidak Ditemukan');
        }

        $now = date('Y-m-d H:i:s');
        $record->updated_at = $now;
        $record->deleted_at = null;
        $status = $record->save();

        if (!$status) {
            $errors = $record->getMessages();
            $error_messages = [];

            foreach ($errors as $key => $error) {
                $error_messages[] = ["field" => $error->getField(), "message" => $error->getMessages()];
            }

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$record]);
        }
    }

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

        // $rawBody = $this->request->getJsonRawBody(true);
        $rawBody = $this->request->getPost();

        $params = $this->validate(
            $rawBody,
            [
                'kode' => ['validators' => []],
                'sektor_id' => ['validators' => []],
                'jenis_id' => ['validators' => []],
                'judul' => ['validators' => ['required']],
                'deskripsi' => ['validators' => ['required']],
                'tahun' => ['validators' => ['required', 'digit']],
                'potensi_penyelamatan_keuangan' => ['validators' => []],
                'daftar_pustaka' => ['validators' => []],
                'attachment_uuid' => ['validators' => []],
                'kajian_document' => ['validators' => []]
            ]
        );

        $files = $this->request->getUploadedFiles();
        foreach ($files as $file) {
            if ($file->getKey() === 'kajian_document') {
                $params['kajian_document'] = $file;
            }
        }


        // if (!empty($params['sektor_id'])) {
        //     $sektor = LitbangKajianSektor::findFirst([
        //         'conditions' => 'id = :sektor_id: AND deleted_at is null',
        //         'bind' => [
        //             'sektor_id' => $params['sektor_id']
        //         ]
        //     ]);

        //     if (empty($sektor)) {
        //         throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sektor tidak ditemukan');
        //     }
        // }

        // if (!empty($params['jenis_id'])) {
        //     $jenis = LitbangKajianJenis::findFirst([
        //         'conditions' => 'id = :jenis_id: AND deleted_at is null',
        //         'bind' => [
        //             'jenis_id' => $params['jenis_id']
        //         ]
        //     ]);

        //     if (empty($jenis)) {
        //         throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Jenis tidak ditemukan');
        //     }
        // }

        // upload kajian document
        $document = null;
        if (!empty($params['kajian_document'])) {
            $document = LitbangService::uploadDocument(
                $this->shared,
                $this->minio,
                $this->request,
                LitbangKajianPengajuan::DOCUMENT_DIR,
                ['application/pdf']
            );
        }

        $record = new LitbangKajian();
        $record->kode = $params['kode'];
        // $record->sektor_id = $params['sektor_id'];
        // $record->jenis_id = $params['jenis_id'];
        $record->judul = $params['judul'];
        $record->deskripsi = $params['deskripsi'];
        $record->tahun = intval($params['tahun']);
        // $record->potensi_penyelamatan_keuangan = $params['potensi_penyelamatan_keuangan'];
        // $record->daftar_pustaka = $params['daftar_pustaka'];
        $record->created_at = date('Y-m-d H:i:s');
        $record->updated_at = date('Y-m-d H:i:s');

        if (!empty($params['attachment_uuid'])) {

            $attachment_record = Attachments::findFirstByUuid($params['attachment_uuid']);

            if (!empty($attachment_record) && empty($attachment_record->deleted_at)) {
                $record->attachment_id = $attachment_record->id;

                $attachment_record->status = Attachments::STATUS_REFERRED;
                $attachment_record->referer = Attachments::REFERER_LITBANG_KAJIAN_COVER;
                $attachment_record->save();
            }
        }

        if (!empty($document[0]->uuid)) {
            $attachment_record = Attachments::findFirstByUuid($document[0]->uuid);

            if (!empty($attachment_record) && empty($attachment_record->deleted_at)) {
                $record->kajian_document_uuid = $attachment_record->uuid;

                $attachment_record->status = Attachments::STATUS_REFERRED;
                $attachment_record->referer = Attachments::REFERER_LITBANG_KAJIAN_DOCUMENT;
                $attachment_record->save();
            }
        }

        $status = $record->save();

        if (!$status) {
            $errors = $record->getMessages();
            $error_messages = [];

            foreach ($errors as $key => $error) {
                $error_messages[] = ["field" => $error->getField(), "message" => $error->getMessage()];
            }

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$record]);
        }
    }

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

        $postParams = $this->request->getPost();
        $this->validate($postParams, [
            'keterangan' => ['validators' => []],
            'metadata' => ['validators' => []]
        ]);

        $directory = 'litbang-kajian-cover' . DIRECTORY_SEPARATOR;
        $result = LitbangService::uploadImage($this->shared, $this->minio, $this->request, $directory);

        return new CollectionPaginatedResponse($result);
    }

    public function downloadCover($uuid)
    {
        $directory = 'litbang-kajian-cover' . DIRECTORY_SEPARATOR;
        return LitbangService::downloadAttachment($this->shared, $this->minio, $directory, $uuid);
    }

    public function downloadDocument($uuid)
    {
        return LitbangService::downloadAttachment($this->shared, $this->minio, LitbangKajianPengajuan::DOCUMENT_DIR, $uuid, true);
    }

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

        $rawBody = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $rawBody,
            [
                'id' => ['validators' => ['required']],
                'instansi_ids' => ['validators' => ['required']]
            ]
        );

        if (!is_array($params['instansi_ids'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format Instansi IDs tidak valid');
        }

        $instansi_ids = array_unique(array_values($params['instansi_ids']));

        $kajian = LitbangKajian::findFirst($params['id']);

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

        if (!empty($kajian->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Kajian sudah terhapus');
        }


        $instansiList = Instansi::find([
            'conditions' => 'id in ({ids:array}) AND deleted_at is NULL',
            'bind' => [
                'ids' => array_values($instansi_ids)
            ]
        ]);

        // Check Expected sub indikator List exists in master data

        $existing_instansi_ids = [];

        foreach ($instansiList as $key => $instansi) {
            $existing_instansi_ids[] = $instansi->id;
        }

        $not_exist_ids = array_diff($instansi_ids, $existing_instansi_ids);

        if (!empty($not_exist_ids)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi dengan ID ' . implode(",", $not_exist_ids) . ' tidak aktif/terdaftar');
        }

        $currentMembers = LitbangKajianKlpd::find([
            'conditions' => 'kajian_id=:kajian_id: AND src = :src: AND deleted_at is NULL',
            'bind' => [
                'kajian_id' => $params['id'],
                'src' => LitbangKajianKlpd::SRC_KAJIAN
            ]
        ]);


        $current_instansi_ids = [];

        // Delete all current member that doesn't exist in params
        foreach ($currentMembers as $key => $member) {
            $current_instansi_ids[] = $member->instansi_id;
            if (!in_array($member->instansi_id, $existing_instansi_ids)) {
                $member->deleted_at = date('Y-m-d H:i:s');
                $member->save();
            }
        }

        // Otherwise, Register new member to this team
        foreach ($existing_instansi_ids as $key => $instansiId) {
            if (!in_array($instansiId, $current_instansi_ids)) {
                $member = new LitbangKajianKlpd();
                $member->kajian_id = $params['id'];
                $member->instansi_id = $instansiId;
                $status = $member->save();
            }
        }

        return new CollectionPaginatedResponse([]);
    }

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

        $rawBody = $this->request->getJsonRawBody(true);

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

        $kajian = LitbangKajian::findFirst($id);

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

        if (!empty($kajian->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Kajian sudah terhapus');
        }


        $instansi = Instansi::findFirst([
            'conditions' => 'id=:id: AND deleted_at is NULL',
            'bind' => [
                'id' => $params['instansi_id']
            ]
        ]);

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

        $existingInstansi = LitbangKajianKlpd::findFirst([
            'conditions' => 'kajian_id=:kajian_id: AND instansi_id=:instansi_id: AND src = :src: AND deleted_at is NULL',
            'bind' => [
                'instansi_id' => $params['instansi_id'],
                'kajian_id' => $id,
                'src' => LitbangKajianKlpd::SRC_KAJIAN
            ]
        ]);

        if (!empty($existingInstansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi sudah terdaftar pada kajian ini');
        }

        $member = new LitbangKajianKlpd();
        $member->kajian_id = $id;
        $member->instansi_id = $params['instansi_id'];
        $member->created_at = date('Y-m-d H:i:s');
        $member->updated_at = date('Y-m-d H:i:s');
        $status = $member->save();

        return new SimpleResponse([]);
    }

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

        $rawBody = $this->request->getJsonRawBody(true);

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

        $kajian = LitbangKajian::findFirst($id);

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

        if (!empty($kajian->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Kajian sudah terhapus');
        }


        $instansi = Instansi::findFirst([
            'conditions' => 'id=:id: AND deleted_at is NULL',
            'bind' => [
                'id' => $params['instansi_id']
            ]
        ]);

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

        $existingInstansi = LitbangKajianKlpd::findFirst([
            'conditions' => 'kajian_id=:kajian_id: AND instansi_id=:instansi_id: AND src = :src: AND deleted_at is NULL',
            'bind' => [
                'instansi_id' => $params['instansi_id'],
                'kajian_id' => $id,
                'src' => LitbangKajianKlpd::SRC_KAJIAN
            ]
        ]);

        if (empty($existingInstansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Relasi instansi dengan kajian tidak ditemukan');
        }

        $existingInstansi->updated_at = date('Y-m-d H:i:s');
        $existingInstansi->deleted_at = date('Y-m-d H:i:s');
        $existingInstansi->save();

        return new SimpleResponse([]);
    }

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

        $rawBody = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $rawBody,
            [
                'kajian_id' => ['validators' => ['required']],
                'kajian_klpd_id' => ['validators' => ['required']],
                'konten' => ['validators' => []],
                'potensi_penyelamatan_keuangan_negara' => ['validators' => []]
            ]
        );

        $kajian = LitbangKajian::findFirst($params['kajian_id']);

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

        if (!empty($kajian->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Kajian sudah terhapus');
        }

        $kajianKlpd = LitbangKajianKlpd::findFirst([
            'conditions' => 'kajian_id=:kajian_id: AND id=:id: AND src = :src: AND deleted_at is NULL',
            'bind' => [
                'id' => $params['kajian_klpd_id'],
                'kajian_id' => $params['kajian_id'],
                'src' => LitbangKajianKlpd::SRC_KAJIAN
            ]
        ]);

        if (empty($kajianKlpd)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Relasi kajian dan instansi tidak ditemukan');
        }

        $rekomendasi = new LitbangKajianKlpdRekomendasi();
        $rekomendasi->kajian_klpd_id = $params['kajian_klpd_id'];
        $rekomendasi->konten = $params['konten'];
        $rekomendasi->potensi_penyelamatan_keuangan_negara = $params['potensi_penyelamatan_keuangan_negara'];
        $rekomendasi->created_at = date('Y-m-d H:i:s');
        $rekomendasi->updated_at = date('Y-m-d H:i:s');
        $status = $rekomendasi->save();

        return new SimpleResponse([]);
    }

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

        $rawBody = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $rawBody,
            [
                'kajian_id' => ['validators' => ['required']],
                'kajian_klpd_id' => ['validators' => ['required']],
                'konten' => ['validators' => []],
                'potensi_penyelamatan_keuangan_negara' => ['validators' => []]
            ]
        );

        $kajian = LitbangKajian::findFirst($params['kajian_id']);

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

        if (!empty($kajian->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Kajian sudah terhapus');
        }

        $kajianKlpd = LitbangKajianKlpd::findFirst([
            'conditions' => 'kajian_id=:kajian_id: AND id=:id: AND src = :src: AND deleted_at is NULL',
            'bind' => [
                'id' => $params['kajian_klpd_id'],
                'kajian_id' => $params['kajian_id'],
                'src' => LitbangKajianKlpd::SRC_KAJIAN
            ]
        ]);

        if (empty($kajianKlpd)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Relasi kajian dan instansi tidak ditemukan');
        }

        $rekomendasi = LitbangKajianKlpdRekomendasi::findFirst([
            'conditions' => 'kajian_klpd_id=:kajian_klpd_id: AND id=:id: AND deleted_at is NULL',
            'bind' => [
                'kajian_klpd_id' => $params['kajian_klpd_id'],
                'id' => $id
            ]
        ]);

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

        $rekomendasi->konten = $params['konten'];
        $rekomendasi->potensi_penyelamatan_keuangan_negara = $params['potensi_penyelamatan_keuangan_negara'];
        $rekomendasi->created_at = date('Y-m-d H:i:s');
        $rekomendasi->updated_at = date('Y-m-d H:i:s');
        $status = $rekomendasi->save();

        return new SimpleResponse([]);
    }

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

        $rawBody = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $rawBody,
            [
                'kajian_id' => ['validators' => ['required']],
                'kajian_klpd_id' => ['validators' => ['required']]
            ]
        );

        $kajian = LitbangKajian::findFirst($params['kajian_id']);

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

        if (!empty($kajian->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Kajian sudah terhapus');
        }

        $kajianKlpd = LitbangKajianKlpd::findFirst([
            'conditions' => 'kajian_id=:kajian_id: AND id=:id: AND src = :src: AND deleted_at is NULL',
            'bind' => [
                'id' => $params['kajian_klpd_id'],
                'kajian_id' => $params['kajian_id'],
                'src' => LitbangKajianKlpd::SRC_KAJIAN
            ]
        ]);

        if (empty($kajianKlpd)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Relasi kajian dan instansi tidak ditemukan');
        }

        $rekomendasi = LitbangKajianKlpdRekomendasi::findFirst([
            'conditions' => 'kajian_klpd_id=:kajian_klpd_id: AND id=:id: AND deleted_at is NULL',
            'bind' => [
                'kajian_klpd_id' => $params['kajian_klpd_id'],
                'id' => $id
            ]
        ]);

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

        $rekomendasi->deleted_at = date('Y-m-d H:i:s');
        $rekomendasi->updated_at = date('Y-m-d H:i:s');
        $status = $rekomendasi->save();

        return new SimpleResponse([]);
    }
}