<?php

use Phalcon\Queue\Beanstalk;

class ActivityController extends BaseController
{
    public function testMethodActivity()
    {
        $this->checkScopes(['activity.admin']);

        $service = new ActivityService($this->shared, $this->logger);
        $array = $service->processRecomendation();
        return new SimpleResponse($array);
    }

    public function getUserActivityList()
    {
        $this->checkIsAuthenticated();

        $params = $this->request->get();
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['user_id'] = array_key_exists('user_id', $params) ? $params['user_id'] : null;
        $params['produk_id'] = array_key_exists('produk_id', $params) ? $params['produk_id'] : null;
        $params['start_date'] = array_key_exists('start_date', $params) ? $params['start_date'] : date('Y-m-d', strtotime('-1 week'));
        $params['end_date'] = array_key_exists('end_date', $params) ? $params['end_date'] : date('Y-m-d', strtotime('+1 day'));

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'user_id' => ['validators' => ['digit']],
                'produk_id' => ['validators' => ['digit']],
                'start_date' => ['validators' => ['date']],
                'end_date' => ['validators' => ['date']]
            ]
        );

        $userActivityList = [];
        $totalCount = 0;

        if (!$this->hasScopes(['activity.admin']) && (is_null($params['user_id']) || $this->shared->user->um_id != $params['user_id'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Anda tidak memiliki akses untuk membuka aktivitas atas user tersebut.');
        }

        $strWhere = "UserActivity.deleted_at is null and UserActivity.created_at between :start_date: and :end_date:";
        $bind['start_date'] = $validatedRequest['start_date'];
        $bind['end_date'] = $validatedRequest['end_date'];

        if (!is_null($params['user_id'])) {
            $strWhere .= " and UserActivity.user_id = :user_id:";
            $bind['user_id'] = $validatedRequest['user_id'];
        }

        if (!is_null($params['produk_id'])) {
            $strWhere .= " and UserActivity.produk_id = :produk_id:";
            $bind['produk_id'] = $validatedRequest['produk_id'];
        }

        // if (!is_null($params['start_date'])) {
        //     if(is_null($params['end_date'])) {
        //         $validatedRequest['end_date'] = date('Y-m-d');
        //     }

        //     $strWhere .= " and UserActivity.created_at between :start_date: and :end_date:";
        //     $bind['start_date'] = $validatedRequest['start_date'];
        //     $bind['end_date'] = $validatedRequest['end_date'];
        // }

        $totalCount = UserActivity::count(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $activityList = UserActivity::find(
            [
                $strWhere,
                "bind" => $bind,
                "order" => "created_at desc",
                "limit" => $validatedRequest['limit'],
                "offset" => $validatedRequest['offset']
            ]
        );

        $result = [];
        foreach ($activityList as $activity) {
            $result[] = $activity->toArrayWithDetail();
        }

        return new PaginatedResponse($result, $totalCount, $validatedRequest["limit"], $validatedRequest["offset"]);
    }

    public function deleteDataActivity()
    {
        $this->checkIsAuthenticated();

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

        $validatedRequest = $this->validate(
            $rawBody,
            [
                'activity_id' => ['validators' => ['required', 'digit']],
            ]
        );

        $userActivity = UserActivity::findFirst(
            [
                "id = :activity_id: and deleted_at is null",
                "bind" => [
                    "activity_id" => $validatedRequest['activity_id']
                ]
            ]
        );

        if (empty($userActivity)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Aktivitas tidak ditemukan');
        }

        if (!$this->hasScopes(['activity.admin']) && $this->shared->user->um_id != $userActivity->user_id) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Anda tidak memiliki akses untuk menghapus aktivitas tersebut.');
        }

        $userActivity->deleted_at = 'now()';
        $result = $userActivity->save();

        return new SimpleResponse($result);
    }

    public function deleteDataActivityByUser()
    {
        $this->checkIsAuthenticated();

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

        $validatedRequest = $this->validate(
            $rawBody,
            [
                'user_id' => ['validators' => ['required', 'digit']],
            ]
        );

        $userActivity = UserActivity::find(
            [
                "user_id = :user_id: and deleted_at is null",
                "bind" => [
                    "user_id" => $validatedRequest['user_id']
                ]
            ]
        );

        if (empty($userActivity)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Aktivitas tidak ditemukan');
        }

        if (!$this->hasScopes(['activity.admin']) && $this->shared->user->um_id != $userActivity->user_id) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Anda tidak memiliki akses untuk menghapus aktivitas tersebut.');
        }

        $result = null;

        foreach ($userActivity as $activity) {
            $activity->deleted_at = 'now()';
            $result = $activity->save();
        }


        return new SimpleResponse($result);
    }

    public function getUserActivitySummary()
    {
        $this->checkIsAuthenticated();

        $params = $this->request->get();
        $params['user_id'] = array_key_exists('user_id', $params) ? $params['user_id'] : null;
        $params['produk_id'] = array_key_exists('produk_id', $params) ? $params['produk_id'] : null;
        $params['start_date'] = array_key_exists('start_date', $params) ? $params['start_date'] : date('Y-m-d', strtotime('-1 week'));
        $params['end_date'] = array_key_exists('end_date', $params) ? $params['end_date'] : date('Y-m-d');

        $validatedRequest = $this->validate(
            $params,
            [
                'user_id' => ['validators' => ['digit']],
                'produk_id' => ['validators' => ['digit']],
                'start_date' => ['validators' => ['date']],
                'end_date' => ['validators' => ['date']]
            ]
        );

        if (!$this->hasScopes(['activity.admin']) && $this->shared->user->um_id != $params['user_id']) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Anda tidak memiliki akses untuk melihat data tersebut.');
        }

        $trendProductList = $this->getProductTrendByUser($validatedRequest['user_id']);
        $result['fav_produk'] = $trendProductList;
        $listFavObjek = [];
        foreach ($trendProductList as $trendProduct) {
            $favObjek["product"] =  $trendProduct["nama_produk"];
            $favObjek["fav_object"] = $this->getObjekTrendByUser($validatedRequest['user_id'], $trendProduct['id_index']);

            $listFavObjek[] = $favObjek;
        }

        $result['fav_objek'] = $listFavObjek;

        return new SimpleResponse($result);
    }

    public function getDataActivityMatrix()
    {
        $this->checkIsAuthenticated();

        $params = $this->request->get();
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
            ]
        );

        if (!$this->hasScopes(['activity.admin'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Anda tidak memiliki akses untuk melihat data tersebut.');
        }

        $strWhere = "ActivityKeyMatrix.deleted_at is null";
        $bind = [];

        $totalCount = $this->rawQuery("SELECT COUNT(*) FROM jaga.activity_key_matrix WHERE deleted_at IS NULL")[0]['count'];

        $listMatrix = ActivityKeyMatrix::find(
            [
                $strWhere,
                "bind" => $bind,
                "limit" => $validatedRequest['limit'],
                "offset" => $validatedRequest['offset']
            ]
        );

        $result = [];
        foreach ($listMatrix as $matrix) {
            $result[] = $matrix->toArrayWithDetail();
        }

        return new PaginatedResponse($result, $totalCount, $validatedRequest["limit"], $validatedRequest["offset"]);
    }

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

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

        $validatedRequest = $this->validate(
            $rawBody,
            [
                'matrix_id' => ['validators' => ['required', 'digit']],
            ]
        );

        $activityMatrix = ActivityKeyMatrix::findFirst(
            [
                "id = :matrix_id: and deleted_at is null",
                "bind" => [
                    "matrix_id" => $validatedRequest['matrix_id']
                ]
            ]
        );

        if (empty($activityMatrix)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Matriks tidak ditemukan');
        }

        $activityMatrix->deleted_at = 'now()';
        $result = $activityMatrix->save();

        return new SimpleResponse($result);
    }

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

        $params = $this->request->get();
        $params['mode'] = array_key_exists('mode', $params) ? $params['mode'] : 0;

        $validatedRequest = $this->validate(
            $params,
            [
                'mode' => ['validators' => ['required', 'digit']],
            ]
        );

        $listProduk = [];

        if ($validatedRequest['mode'] == 0) {
            $listProduk = UserActivity::query()
                ->columns("replace(Produk.nama, 'Default', 'Umum') as nama_produk, count(1) as jumlah_aktivitas")
                ->join('Produk', 'Produk.id_index = UserActivity.produk_id')
                ->where('UserActivity.deleted_at is null')
                ->groupBy('Produk.nama')
                ->orderBy('jumlah_aktivitas DESC')
                ->execute()
                ->toArray();
        }

        return new SimpleResponse($listProduk);
    }

    private function getProductTrendByUser($userID)
    {
        $listProduk = [];

        if ($userID != 0) {
            $listProduk = UserActivity::query()
                ->columns("Produk.id_index, replace(Produk.nama, 'Default', 'Umum') as nama_produk, count(1) as jumlah_aktivitas")
                ->join('Produk', 'Produk.id_index = UserActivity.produk_id')
                ->where('UserActivity.deleted_at is null and UserActivity.user_id = :user_id:')
                ->bind(["user_id" => $userID])
                ->groupBy('Produk.id_index, Produk.nama')
                ->orderBy('jumlah_aktivitas DESC')
                ->execute()
                ->toArray();
        }

        return $listProduk;
    }

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

        $params = $this->request->get();
        $params['mode'] = array_key_exists('mode', $params) ? $params['mode'] : 0;
        $params['produk_id'] = array_key_exists('produk_id', $params) ? $params['produk_id'] : null;

        $validatedRequest = $this->validate(
            $params,
            [
                'mode' => ['validators' => ['required', 'digit']],
                'produk_id' => ['validators' => ['required', 'digit']],
            ]
        );

        $listObjek = [];
        switch ($validatedRequest['produk_id']) {
            case UserActivity::PRODUK_PENDIDIKAN:
                $listObjek = UserActivity::query()
                    ->columns("Sekolah.nama as nama_sekolah, count(1) as jumlah_aktivitas")
                    ->join('Sekolah', 'Sekolah.npsn = UserActivity.objek_id')
                    ->where('UserActivity.deleted_at is null')
                    ->groupBy('Sekolah.nama')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
            case UserActivity::PRODUK_KESEHATAN:
                $listObjek = UserActivity::query()
                    ->columns("coalesce(RumahSakit.nama, Puskesmas.nama_puskesmas) as nama_faskes, count(1) as jumlah_aktivitas")
                    ->leftjoin('RumahSakit', 'RumahSakit.kode_rs = UserActivity.objek_id')
                    ->leftjoin('Puskesmas', 'Puskesmas.kode_puskesmas = UserActivity.objek_id')
                    ->where('UserActivity.deleted_at is null')
                    ->groupBy('RumahSakit.nama, Puskesmas.nama_puskesmas')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
            case UserActivity::PRODUK_DESA:
                $listObjek = UserActivity::query()
                    ->columns("OmspanMasterDesa.nama_desa as nama_desa, count(1) as jumlah_aktivitas")
                    ->join('OmspanMasterDesa', 'OmspanMasterDesa.kode_desa = UserActivity.objek_id')
                    ->where('UserActivity.deleted_at is null')
                    ->groupBy('OmspanMasterDesa.nama_desa')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
                break;
            case UserActivity::PRODUK_PERIZINAN:
                //$listObjek = UserActivity::query()
                // ->columns("Perizinan.nama as nama_perizinan, count(1) as jumlah_aktivitas")
                // ->join('Perizinan', 'Perizinan.id = UserActivity.objek_id')
                // ->where('UserActivity.deleted_at is null')
                // ->groupBy('Perizinan.nama')
                // ->orderBy('jumlah_aktivitas DESC')
                // ->execute()
                // ->toArray();
                break;
            case UserActivity::PRODUK_BANSOS:
                $listObjek = UserActivity::query()
                    ->columns("Instansi.nama as nama_instansi, count(1) as jumlah_aktivitas")
                    ->join('Instansi', 'cast(Instansi.id as varchar) = UserActivity.objek_id')
                    ->where('UserActivity.deleted_at is null')
                    ->groupBy('Instansi.nama')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
        }

        return new SimpleResponse($listObjek);
    }

    private function getObjekTrendByUser($userId, $produkId)
    {
        $listObjek = [];
        switch ($produkId) {
            case UserActivity::PRODUK_PENDIDIKAN:
                $listObjek = UserActivity::query()
                    ->columns("Sekolah.nama as nama_sekolah, count(1) as jumlah_aktivitas")
                    ->join('Sekolah', 'Sekolah.npsn = UserActivity.objek_id')
                    ->where('UserActivity.deleted_at is null and UserActivity.user_id = :user_id:')
                    ->bind(["user_id" => $userId])
                    ->groupBy('Sekolah.nama')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
            case UserActivity::PRODUK_KESEHATAN:
                $listObjek = UserActivity::query()
                    ->columns("coalesce(RumahSakit.nama, Puskesmas.nama_puskesmas) as nama_faskes, count(1) as jumlah_aktivitas")
                    ->leftjoin('RumahSakit', 'RumahSakit.kode_rs = UserActivity.objek_id')
                    ->leftjoin('Puskesmas', 'Puskesmas.kode_puskesmas = UserActivity.objek_id')
                    ->where('UserActivity.deleted_at is null and UserActivity.user_id = :user_id:')
                    ->bind(["user_id" => $userId])
                    ->groupBy('RumahSakit.nama, Puskesmas.nama_puskesmas')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
            case UserActivity::PRODUK_DESA:
                $listObjek = UserActivity::query()
                    ->columns("OmspanMasterDesa.nama_desa as nama_desa, count(1) as jumlah_aktivitas")
                    ->join('OmspanMasterDesa', 'OmspanMasterDesa.kode_desa = UserActivity.objek_id')
                    ->where('UserActivity.deleted_at is null and UserActivity.user_id = :user_id:')
                    ->bind(["user_id" => $userId])
                    ->groupBy('OmspanMasterDesa.nama_desa')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
                break;
            case UserActivity::PRODUK_PERIZINAN:
                // $listObjek = UserActivity::query()
                //     ->columns("Perizinan.nama as nama_perizinan, count(1) as jumlah_aktivitas")
                //     ->join('Perizinan', 'Perizinan.id = UserActivity.objek_id')
                //     ->where('UserActivity.deleted_at is null and UserActivity.user_id = :user_id:')
                //     ->bind(["user_id" => $userId])
                //     ->groupBy('Perizinan.nama')
                //     ->orderBy('jumlah_aktivitas DESC')
                //     ->execute()
                //     ->toArray();
                break;
            case UserActivity::PRODUK_BANSOS:
                $listObjek = UserActivity::query()
                    ->columns("Instansi.nama as nama_instansi, count(1) as jumlah_aktivitas")
                    ->join('Instansi', 'cast(Instansi.id as varchar) = UserActivity.objek_id')
                    ->where('UserActivity.deleted_at is null and UserActivity.user_id = :user_id:')
                    ->bind(["user_id" => $userId])
                    ->groupBy('Instansi.nama')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
        }

        return $listObjek;
    }

    public function getRecommendationByUser()
    {
        $this->checkIsAuthenticated();
        //AuthHelper::validateToolsKey($this->request, $this->config);
        $params = $this->request->get();
        $params['user_id'] = array_key_exists('user_id', $params) ? $params['user_id'] : 0;
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;

        $validatedRequest = $this->validate(
            $params,
            [
                'user_id' => ['validators' => ['required', 'digit']],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
            ]
        );

        if (!$this->hasScopes(['activity.admin']) && $this->shared->user->um_id != $params['user_id']) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Anda tidak memiliki akses untuk melihat rekomendasi atas user tersebut.');
        }

        $listRecommendation = RecommendationContainer::find(
            [
                "user_id = :user_id: and deleted_at is null",
                "bind" => [
                    "user_id" => $validatedRequest['user_id']
                ]
            ]
        );
        $listDiskusi = [];
        $countDiskusi = 0;

        if (count($listRecommendation) > 0) {
            $listDiskusiUUID = [];
            foreach ($listRecommendation as $recommendation) {
                $listDiskusiUUID[] = $recommendation->diskusi_uuid;
            }

            $listDiskusi = Diskusi::find(
                [
                    "uuid in ({list_uuid:array}) and is_deleted = :is_deleted:",
                    "bind" => [
                        "list_uuid" => $listDiskusiUUID,
                        "is_deleted" => 0
                    ],
                    "limit" => $validatedRequest['limit'],
                    "offset" => $validatedRequest['offset']
                ]
            );

            $countDiskusi = Diskusi::count(
                [
                    "uuid in ({list_uuid:array}) and is_deleted = :is_deleted:",
                    "bind" => [
                        "list_uuid" => $listDiskusiUUID,
                        "is_deleted" => 0
                    ]
                ]
            );
        }

        return new PaginatedResponse($listDiskusi, $countDiskusi, $validatedRequest["limit"], $validatedRequest["offset"]);
    }

    public function getRecommendation()
    {
        $this->checkIsAuthenticated();
        $params = $this->request->get();
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
            ]
        );

        $userId = $this->shared->user->um_id;

        $listRecommendation = RecommendationContainer::find(
            [
                "user_id = :user_id: and deleted_at is null",
                "bind" => [
                    "user_id" => $userId
                ]
            ]
        );
        $listDiskusi = [];
        $countDiskusi = 0;

        if (count($listRecommendation) > 0) {
            $listDiskusiUUID = [];
            foreach ($listRecommendation as $recommendation) {
                $listDiskusiUUID[] = $recommendation->diskusi_uuid;
            }

            $listDiskusi = Diskusi::find(
                [
                    "uuid in ({list_uuid:array}) and is_deleted = :is_deleted:",
                    "bind" => [
                        "list_uuid" => $listDiskusiUUID,
                        "is_deleted" => 0
                    ],
                    "limit" => $validatedRequest['limit'],
                    "offset" => $validatedRequest['offset']
                ]
            );

            $countDiskusi = Diskusi::count(
                [
                    "uuid in ({list_uuid:array}) and is_deleted = :is_deleted:",
                    "bind" => [
                        "list_uuid" => $listDiskusiUUID,
                        "is_deleted" => 0
                    ]
                ]
            );
        }

        return new PaginatedResponse($listDiskusi, $countDiskusi, $validatedRequest["limit"], $validatedRequest["offset"]);
    }

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

        $params = $this->request->get();
        $params['produk_id'] = array_key_exists('produk_id', $params) ? $params['produk_id'] : null;

        $validatedRequest = $this->validate(
            $params,
            [
                'produk_id' => ['validators' => ['required', 'digit']],
            ]
        );

        $listTrend = [];

        switch ($validatedRequest['produk_id']) {
            case UserActivity::PRODUK_PENDIDIKAN:
                $listTrend = UserActivity::query()
                    ->columns("ActivityKeyMatrix.key_4 as key_provinsi, ActivityKeyMatrix.key_4 as nama_provinsi, count(1) as jumlah_aktivitas")
                    ->join('ActivityKeyMatrix', 'ActivityKeyMatrix.produk_id = UserActivity.produk_id and ActivityKeyMatrix.objek_id = UserActivity.objek_id')
                    ->where('UserActivity.produk_id = :produk_id:')
                    ->bind(['produk_id' => $validatedRequest['produk_id']])
                    ->groupBy('ActivityKeyMatrix.key_4')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
            case UserActivity::PRODUK_KESEHATAN:
                $listTrend = UserActivity::query()
                    ->columns("ActivityKeyMatrix.key_3 as key_provinsi, ActivityKeyMatrix.key_3 as nama_provinsi, count(1) as jumlah_aktivitas")
                    ->join('ActivityKeyMatrix', 'ActivityKeyMatrix.produk_id = UserActivity.produk_id and ActivityKeyMatrix.objek_id = UserActivity.objek_id')
                    ->where('UserActivity.produk_id = :produk_id:')
                    ->bind(['produk_id' => $validatedRequest['produk_id']])
                    ->groupBy('ActivityKeyMatrix.key_3')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
            case UserActivity::PRODUK_DESA:
                $listTrend = UserActivity::query()
                    ->columns("OmSpanMasterDesa.kode_provinsi as kode_provinsi, OmSpanMasterDesa.nama_provinsi as nama_provinsi, count(1) as jumlah_aktivitas")
                    ->join('OmSpanMasterDesa', 'OmSpanMasterDesa.kode_desa = UserActivity.objek_id')
                    ->where('UserActivity.produk_id = :produk_id:')
                    ->bind(['produk_id' => $validatedRequest['produk_id']])
                    ->groupBy('OmSpanMasterDesa.nama_provinsi, OmSpanMasterDesa.kode_provinsi')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
            case UserActivity::PRODUK_BANSOS:
                $listTrend = UserActivity::query()
                    ->columns("ActivityKeyMatrix.key_2 as key_provinsi, Replace(coalesce(OtherInstansi.nama, Provinsi.nama), 'Pemerintah ','') as nama_provinsi, count(1) as jumlah_aktivitas")
                    ->join('ActivityKeyMatrix', 'ActivityKeyMatrix.produk_id = UserActivity.produk_id and ActivityKeyMatrix.objek_id = UserActivity.objek_id')
                    ->leftjoin('Instansi', 'Provinsi.id_tipe = 1 and cast(Provinsi.id_provinsi as varchar) = ActivityKeyMatrix.key_2', 'Provinsi')
                    ->leftjoin('Instansi', '(OtherInstansi.id_tipe is null or OtherInstansi.id_tipe = 3) and cast(OtherInstansi.id as varchar) = UserActivity.objek_id', 'OtherInstansi')
                    ->where('UserActivity.produk_id = :produk_id:')
                    ->bind(['produk_id' => $validatedRequest['produk_id']])
                    ->groupBy('ActivityKeyMatrix.key_2, Provinsi.nama, OtherInstansi.nama')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
        }

        return new SimpleResponse($listTrend);
    }

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

        $params = $this->request->get();
        $params['produk_id'] = array_key_exists('produk_id', $params) ? $params['produk_id'] : null;
        $params['key_provinsi'] = array_key_exists('key_provinsi', $params) ? $params['key_provinsi'] : null;

        $validatedRequest = $this->validate(
            $params,
            [
                'produk_id' => ['validators' => ['required', 'digit']],
                'key_provinsi' => ['validators' => ['required']],
            ]
        );

        $listTrend = [];

        switch ($validatedRequest['produk_id']) {
            case UserActivity::PRODUK_PENDIDIKAN:
                $listTrend = UserActivity::query()
                    ->columns("ActivityKeyMatrix.key_3 as key_kabupaten, ActivityKeyMatrix.key_3 as nama_kabupaten, count(1) as jumlah_aktivitas")
                    ->join('ActivityKeyMatrix', 'ActivityKeyMatrix.produk_id = UserActivity.produk_id and ActivityKeyMatrix.objek_id = UserActivity.objek_id')
                    ->where('UserActivity.produk_id = :produk_id: and ActivityKeyMatrix.key_4 = :key_provinsi:')
                    ->bind(['produk_id' => $validatedRequest['produk_id'], 'key_provinsi' => $validatedRequest['key_provinsi']])
                    ->groupBy('ActivityKeyMatrix.key_3')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
            case UserActivity::PRODUK_KESEHATAN:
                $listTrend = UserActivity::query()
                    ->columns("ActivityKeyMatrix.key_2 as key_kabupaten, ActivityKeyMatrix.key_2 as nama_kabupaten, count(1) as jumlah_aktivitas")
                    ->join('ActivityKeyMatrix', 'ActivityKeyMatrix.produk_id = UserActivity.produk_id and ActivityKeyMatrix.objek_id = UserActivity.objek_id')
                    ->where('UserActivity.produk_id = :produk_id: and ActivityKeyMatrix.key_3 = :key_provinsi:')
                    ->bind(['produk_id' => $validatedRequest['produk_id'], 'key_provinsi' => $validatedRequest['key_provinsi']])
                    ->groupBy('ActivityKeyMatrix.key_2')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
            case UserActivity::PRODUK_DESA:
                $listTrend = UserActivity::query()
                    ->columns("OmspanMasterDesa.kode_pemda as key_kabupaten, OmspanMasterDesa.nama_pemda as nama_kabupaten, count(1) as jumlah_aktivitas")
                    ->join('OmspanMasterDesa', 'OmspanMasterDesa.kode_desa = UserActivity.objek_id')
                    ->where('UserActivity.produk_id = :produk_id: and OmspanMasterDesa.kode_provinsi = :key_provinsi:')
                    ->bind(['produk_id' => $validatedRequest['produk_id'], 'key_provinsi' => $validatedRequest['key_provinsi']])
                    ->groupBy('OmspanMasterDesa.nama_kabupaten, OmspanMasterDesa.kode_kabupaten')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
            case UserActivity::PRODUK_BANSOS:
                $listTrend = UserActivity::query()
                    ->columns("ActivityKeyMatrix.key_1 as key_kabupaten, Replace(Kabupaten.nama, 'Pemerintah ','') as nama_kabupaten, count(1) as jumlah_aktivitas")
                    ->join('ActivityKeyMatrix', 'ActivityKeyMatrix.produk_id = UserActivity.produk_id and ActivityKeyMatrix.objek_id = UserActivity.objek_id')
                    ->leftjoin('Instansi', 'Kabupaten.id_tipe = 2 and cast(Kabupaten.id as varchar) = ActivityKeyMatrix.key_1', 'Kabupaten')
                    ->where('UserActivity.produk_id = :produk_id: and ActivityKeyMatrix.key_2 = :key_provinsi:')
                    ->bind(['produk_id' => $validatedRequest['produk_id'], 'key_provinsi' => $validatedRequest['key_provinsi']])
                    ->groupBy('ActivityKeyMatrix.key_1, Kabupaten.nama')
                    ->orderBy('jumlah_aktivitas DESC')
                    ->execute()
                    ->toArray();
                break;
        }

        return new SimpleResponse($listTrend);
    }
}