<?php

class KeluhanController extends BaseController
{
    const PELABUHAN_RAPOR_HIJAU = 'Hijau';
    const PELABUHAN_RAPOR_KUNING = 'Kuning';
    const PELABUHAN_RAPOR_MERAH = 'Merah';

    public function statistikPelabuhan() {
        $req = $this->request->get();
        $params = $this->validate(
            $req,
            [
                'id_instansi' => ['validators' => ['digit']],
                'start_date' => ['validators' => ['date']],
                'end_date' => ['validators' => ['date']],
            ]
        );

        $where = "t.type = :type";
        $bind = ["type" => "PELABUHAN"];
        $instansi = null;
        if (!empty($req['id_instansi'])) {
            $idInstansi = $params['id_instansi'];
            $instansi = Instansi::findFirst($idInstansi);
            if (!$instansi) throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan!");
            $where .= " AND ds.id_instansi_origin = :id_instansi";
            $bind['id_instansi'] = $idInstansi;
        }
        $minDate = "2023-01-01";
        if (!empty($req['start_date'])) {
            $where .= " AND ds.created_at >= :start_date";
            $bind["start_date"] = "'".$params['start_date']." 00:00:00'";
        } else if (!empty($req['end_date'])) {
            $where .= " AND ds.created_at >= :start_date";
            $req["start_date"] = $minDate;
            $bind["start_date"] = $minDate;
        }
        if (!empty($req['end_date'])) {
            $where .= " AND ds.created_at <= :end_date";
            $bind["end_date"] = "'".$params['end_date']." 23:59:59'";
        } elseif (!empty($req['start_date'])) {
            $where .= " AND ds.created_at <= :end_date";
            $req["end_date"] = date('Y-m-d 00:00:00');
            $bind["end_date"] = date('Y-m-d 23:59:59');
        }

        if (!empty($req['start_date'])) {
            $start = date_create($req['start_date']);
            $end = date_create($req['end_date']);
            $diff = intval($end->diff($start)->days) + 1;
        } else {
            $start = date_create($minDate);
            $end = date_create();
            $diff = intval($end->diff($start)->days) + 1;
        }

        if ($diff <= 31) {
            $data = $this->statNormalizeDate($this->statKeluhanByDate($where, $bind), $start, $end);
            $label = "dalam hari";
        } elseif ($diff <= 181) {
            $data = $this->statNormalizeWeek($this->statKeluhanByWeek($where, $bind), $start, $end);
            $label = "dalam pekan";
        } else {
            $data = $this->statNormalizeMonth($this->statKeluhanByMonth($where, $bind), $start, $end);
            $label = "dalam bulan";
        }

        $total = $this->totalKeluhan($where, $bind);

        $rapor = $this->raporPelabuhan($instansi ? $instansi->nama : null);

        return new SimpleResponse([
            'total' => $total,
            'rapor' => $rapor,
            'label' => $label,
            'data' => $data,
        ]);
    }

    public function statistikBpjs() {
        $req = $this->request->get();
        $params = $this->validate(
            $req,
            [
                'start_date' => ['validators' => ['date']],
                'end_date' => ['validators' => ['date']],
            ]
        );

        $where = "t.type = :type";
        $bind = ["type" => "BPJS"];
        $instansi = Instansi::findFirst(
            [
                "lower(nama) ILIKE :nama_instansi:",
                "bind" => [
                    "nama_instansi" => DiskusiBansos::BPJS_KESEHATAN_NAME
                ]
            ]
        );
        $idInstansi = $instansi->id;
        if (!$instansi) throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan!");
        $where .= " AND ds.id_instansi_origin = :id_instansi";
        $bind['id_instansi'] = $idInstansi;

        $minDate = "2024-01-01";
        if (!empty($req['start_date'])) {
            $where .= " AND ds.created_at >= :start_date";
            $bind["start_date"] = "'".$params['start_date']." 00:00:00'";
        } else if (!empty($req['end_date'])) {
            $where .= " AND ds.created_at >= :start_date";
            $req["start_date"] = $minDate;
            $bind["start_date"] = $minDate;
        }
        if (!empty($req['end_date'])) {
            $where .= " AND ds.created_at <= :end_date";
            $bind["end_date"] = "'".$params['end_date']." 23:59:59'";
        } elseif (!empty($req['start_date'])) {
            $where .= " AND ds.created_at <= :end_date";
            $req["end_date"] = date('Y-m-d 00:00:00');
            $bind["end_date"] = date('Y-m-d 23:59:59');
        }

        if (!empty($req['start_date'])) {
            $start = date_create($req['start_date']);
            $end = date_create($req['end_date']);
            $diff = intval($end->diff($start)->days) + 1;
        } else {
            $start = date_create($minDate);
            $end = date_create();
            $diff = intval($end->diff($start)->days) + 1;
        }

        if ($diff <= 31) {
            $data = $this->statNormalizeDate($this->statKeluhanByDate($where, $bind), $start, $end);
            $label = "dalam hari";
        } elseif ($diff <= 181) {
            $data = $this->statNormalizeWeek($this->statKeluhanByWeek($where, $bind), $start, $end);
            $label = "dalam pekan";
        } else {
            $data = $this->statNormalizeMonth($this->statKeluhanByMonth($where, $bind), $start, $end);
            $label = "dalam bulan";
        }

        $total = $this->totalKeluhan($where, $bind);

        return new SimpleResponse([
            'total' => $total,
            'label' => $label,
            'data' => $data,
        ]);
    }

    private function totalKeluhan($where, $bind) {
        return $this->rawQuery("
        SELECT
            COUNT(*) FILTER (WHERE ds.status = 1) AS total_diterima,
            COUNT(*) FILTER (WHERE ds.status = 2) AS total_diteruskan,
            COUNT(*) FILTER (WHERE ds.status = 3) AS total_ditindaklanjuti,
            COUNT(*) FILTER (WHERE ds.status = 4) AS total_diselesaikan
        FROM
            jaga.diskusi_bansos ds
        LEFT JOIN
            jaga.topik_diskusi_bansos t ON ds.id_topik = t.id
        WHERE ".$where.";
        ", $bind);
    }

    private function statKeluhanByDate($where, $bind) {
        return $this->rawQuery("
            SELECT
                ds.created_at::TIMESTAMP::DATE as x_axis,
                COUNT(*) FILTER (WHERE ds.status = 1) AS diterima,
                COUNT(*) FILTER (WHERE ds.status = 2) AS diteruskan,
                COUNT(*) FILTER (WHERE ds.status = 3) AS ditindaklanjuti,
                COUNT(*) FILTER (WHERE ds.status = 4) AS diselesaikan
            FROM
                jaga.diskusi_bansos ds
            LEFT JOIN
                jaga.topik_diskusi_bansos t ON ds.id_topik = t.id
            WHERE ".$where."
            GROUP BY x_axis
            ORDER BY x_axis;
        ", $bind);
    }

    private function statKeluhanByWeek($where, $bind) {
        return $this->rawQuery("
            SELECT
                tahun || '-' || lpad(x_axis::TEXT, 2, '0') as x_axis,
                diterima,
                diteruskan,
                ditindaklanjuti,
                diselesaikan
            FROM
            (
                SELECT
                    date_part('year', ds.created_at::TIMESTAMP::DATE) AS tahun,
                    date_part('week', ds.created_at::TIMESTAMP::DATE) AS x_axis,
                    COUNT(*) FILTER (WHERE ds.status = 1) AS diterima,
                    COUNT(*) FILTER (WHERE ds.status = 2) AS diteruskan,
                    COUNT(*) FILTER (WHERE ds.status = 3) AS ditindaklanjuti,
                    COUNT(*) FILTER (WHERE ds.status = 4) AS diselesaikan
                FROM
                    jaga.diskusi_bansos ds
                LEFT JOIN
                    jaga.topik_diskusi_bansos t ON ds.id_topik = t.id
                WHERE " . $where . "
                GROUP BY tahun, x_axis
                ORDER BY tahun, x_axis
            ) a;
        ", $bind);
    }

    private function statKeluhanByMonth($where, $bind) {
        return $this->rawQuery("
            SELECT
                tahun || '-' || lpad(x_axis::TEXT, 2, '0') as x_axis,
                diterima,
                diteruskan,
                ditindaklanjuti,
                diselesaikan
            FROM
            (
                SELECT
                    date_part('year', ds.created_at::TIMESTAMP::DATE) AS tahun,
                    date_part('month', ds.created_at::TIMESTAMP::DATE) AS x_axis,
                    COUNT(*) FILTER (WHERE ds.status = 1) AS diterima,
                    COUNT(*) FILTER (WHERE ds.status = 2) AS diteruskan,
                    COUNT(*) FILTER (WHERE ds.status = 3) AS ditindaklanjuti,
                    COUNT(*) FILTER (WHERE ds.status = 4) AS diselesaikan
                FROM
                    jaga.diskusi_bansos ds
                LEFT JOIN
                    jaga.topik_diskusi_bansos t ON ds.id_topik = t.id
                WHERE " . $where . "
                GROUP BY tahun, x_axis
                ORDER BY tahun, x_axis
            ) a;
        ", $bind);
    }

    private function statNormalizeDate($stat, DateTime $start, DateTime $end) {
        $res = [];
        $interval = DateInterval::createFromDateString('1 day');
        $period = new DatePeriod($start, $interval, $end->modify('+1 day'));

        foreach ($period as $dt) {
            $date = $dt->format("Y-m-d");
            $found = $this->findObjectInArrayWithValue($stat, 'x_axis', $date);
            if ($found) {
                array_push($res, $found);
            } else {
                array_push(
                    $res,
                    [
                        "x_axis" => $date,
                        "diterima" => 0,
                        "diteruskan" => 0,
                        "ditindaklanjuti" => 0,
                        "diselesaikan" => 0
                    ]
                );
            }
        }

        return $res;
    }

    private function statNormalizeWeek($stat, DateTime $start, DateTime $end) {
        $res = [];
        $interval = DateInterval::createFromDateString('1 week');
        $period = new DatePeriod($start, $interval, $end->modify('+1 day'));
        foreach($period as $dt) {
            $week = $dt->format('Y-W');
            $found = $this->findObjectInArrayWithValue($stat, 'x_axis', $week);
            if ($found) {
                array_push($res, $found);
            } else {
                array_push(
                    $res,
                    [
                        "x_axis" => $week,
                        "diterima" => 0,
                        "diteruskan" => 0,
                        "ditindaklanjuti" => 0,
                        "diselesaikan" => 0
                    ]
                );
            }
        }

        return $res;
    }

    private function raporPelabuhan($namaPelabuhan) {
        $rapor = [
            'Pelabuhan Cilegon Mandiri' => $this->rapor(self::PELABUHAN_RAPOR_HIJAU, 'Progres sudah baik'),
            'Pelabuhan Tanjung Emas' => $this->rapor(self::PELABUHAN_RAPOR_HIJAU, 'Progres sudah baik'),
            'Pelabuhan Balikpapan' => $this->rapor(self::PELABUHAN_RAPOR_HIJAU, 'Progres sudah baik'),
            'Pelabuhan Samarinda' => $this->rapor(self::PELABUHAN_RAPOR_HIJAU, 'Progres sudah baik'),
            'Pelabuhan Nusantara Kendari' => $this->rapor(self::PELABUHAN_RAPOR_HIJAU, 'Progres sudah baik'),
            'Pelabuhan Soekarno-Hatta' => $this->rapor(self::PELABUHAN_RAPOR_HIJAU, 'Progres sudah baik'),
            'Pelabuhan Belawan' => $this->rapor(self::PELABUHAN_RAPOR_KUNING, 'Ada masalah pada tempat proses pemeriksaan bea cukai dan karantina'),
            'Pelabuhan Batu Ampar' => $this->rapor(self::PELABUHAN_RAPOR_MERAH, 'Bukan daerah operasional Pelindo'),
            'Pelabuhan Tanjung Priok' => $this->rapor(self::PELABUHAN_RAPOR_MERAH, 'Ada masalah pada layanan bea cukai dan karantina'),
            'Pelabuhan Tanjung Perak' => $this->rapor(self::PELABUHAN_RAPOR_HIJAU, 'Progress sudah baik'),
            'Pelabuhan Dumai' => $this->rapor(self::PELABUHAN_RAPOR_MERAH, 'Ada masalah pada proses pelayanan kapal'),
            'Pelabuhan Boom Baru' => $this->rapor(self::PELABUHAN_RAPOR_MERAH, 'Ada masalah pada proses pelayanan kapal dan pemeriksaan bea cukai dan karantina'),
            'Pelabuhan Panjang' => $this->rapor(self::PELABUHAN_RAPOR_KUNING, 'Ada masalah pada proses pelayanan kapal'),
            'Pelabuhan Dwikora' => $this->rapor(self::PELABUHAN_RAPOR_MERAH, 'Ada masalah pada proses pelayanan kapal'),
        ];

        if ($namaPelabuhan) {
            if (array_key_exists(trim($namaPelabuhan), $rapor)) {
                $res = $rapor[trim($namaPelabuhan)];
            } else {
                $res = [];
            }
        } else {
            $res = $rapor;
        }

        return $res;
    }

    private function hex($color) {
        $hex = '#ddd';
        switch ($color) {
            case self::PELABUHAN_RAPOR_HIJAU:
                $hex = '#3e7309';
                break;
            case self::PELABUHAN_RAPOR_KUNING:
                $hex = '#ddc64c';
                break;
            case self::PELABUHAN_RAPOR_MERAH:
                $hex = '#d02224';
                break;
            default:
                break;
        }

        return $hex;
    }

    private function rapor($color, $identification) {
        return [
            'rapor' => $color,
            'hex' => $this->hex($color),
            'identifikasi' => $identification
        ];
    }

    private function statNormalizeMonth($stat, DateTime $start, DateTime $end) {
        $res = [];
        $interval = DateInterval::createFromDateString('1 month');
        $period = new DatePeriod($start, $interval, $end->modify('+1 day'));
        foreach($period as $dt) {
            $week = $dt->format('Y-m');
            $found = $this->findObjectInArrayWithValue($stat, 'x_axis', $week);
            if ($found) {
                array_push($res, $found);
            } else {
                array_push(
                    $res,
                    [
                        "x_axis" => $week,
                        "diterima" => 0,
                        "diteruskan" => 0,
                        "ditindaklanjuti" => 0,
                        "diselesaikan" => 0
                    ]
                );
            }
        }

        return $res;
    }

    /**
     * @param array $array
     * @param $objectKey
     * @param $objectValue
     * @return false|array
     */
    private function findObjectInArrayWithValue(array $array, $objectKey, $objectValue) {
        foreach ($array as $element) {
            if ($element[$objectKey] == $objectValue) {
                return $element;
            }
        }
        return false;
    }

    public function submitKeluhanDesa()
    {
        try {
            $this->db->begin();
            date_default_timezone_set('Asia/Jakarta');
            $this->checkScopes(['api.consume', 'keluhan.desa.manage', 'discussion.create']);
            $params = $this->request->getJsonRawBody(true);
            $validatedRequest = $this->keluhanDesaValidation($params);

            $service = new DiskusiService($this->config, $this->shared, $this->request, $this->minio);
            $result = $service->create($validatedRequest, 0, $this->config["api_consume_daily_limit"]);

            if ($result['res']) {
                $diskusi = Diskusi::findFirst(
                    [
                        "id = :id_diskusi:",
                        "bind" => [
                            "id_diskusi" => $result['diskusi']->id
                        ]
                    ]
                );

                $diskusi->is_attachment_public = 0;
                $result['res'] = $diskusi->save();

                $diskusiObjek = new DiskusiObjek();
                $diskusiObjek->diskusi_id = $result['diskusi']->id;
                $diskusiObjek->nama_objek = $validatedRequest['nama_desa'];
                $diskusiObjek->objek_id = 0;
                $diskusiObjek->save();
            }

            $isAllSuccess = $result['res'] && $result['saveRespon'] && $result['saveDiskusi'];

            if ($isAllSuccess) {
                $idInstansi = $validatedRequest['id_instansi'];

                $params["nik"] = array_key_exists("nik", $params) ? $validatedRequest["nik"] : "";
                $diskusiBansos = new DiskusiBansos();
                $diskusiBansos->id_diskusi = $result['diskusi']->id;
                $diskusiBansos->id_instansi = $diskusiBansos->id_instansi_origin = $idInstansi;
                $diskusiBansos->status = DiskusiBansos::NEW;
                $diskusiBansos->id_topik = $validatedRequest["id_topik"];
                $diskusiBansos->phone = $validatedRequest["phone"];
                $diskusiBansos->phone = $validatedRequest["phone"];
                $diskusiBansos->source = DiskusiBansos::SOURCE_SIPEMANDU;
                $saveDiskusiBansos = $diskusiBansos->create();

                $saveDiskusiBansosNIK = true;
                if (!empty($validatedRequest["nik"])) {
                    $validatedRequest["nik"] = explode(";", $validatedRequest["nik"]);
                    foreach ($validatedRequest["nik"] as $nik) {
                        if ($saveDiskusiBansosNIK) {
                            $diskusiBansosNIK = new DiskusiBansosNIK();
                            $diskusiBansosNIK->id_diskusi_bansos = $diskusiBansos->id;
                            $diskusiBansosNIK->nik = $nik;
                            $diskusiBansosNIK->pkh = 0;
                            $saveDiskusiBansosNIK = $diskusiBansosNIK->create();
                        }
                    }
                }

                $diskusiBansosHistory = new DiskusiBansosHistory();
                $diskusiBansosHistory->id_diskusi_bansos = $diskusiBansos->id;
                $diskusiBansosHistory->keterangan = 'Membuat Keluhan Bansos';
                $diskusiBansosHistory->status = $diskusiBansos->status;
                $diskusiBansosHistory->id_instansi = $diskusiBansos->id_instansi;
                $diskusiBansosHistory->id_user = $this->shared->user->um_id;
                $diskusiBansosHistory->create();

                $validatedRequestKeluhanDesaSipemandu = $this->validateKeluhanDesaSipemandu($params);
                $keluhanDesaSipemandu = new KeluhanDesaSipemandu();
                $keluhanDesaSipemandu->id_diskusi_bansos = $diskusiBansos->id;
                $keluhanDesaSipemandu->nomor_tiket = $validatedRequestKeluhanDesaSipemandu['nomor_tiket'];
                $keluhanDesaSipemandu->nama_pelapor = $validatedRequestKeluhanDesaSipemandu['nama_pelapor'];
                $keluhanDesaSipemandu->email_pelapor = $validatedRequestKeluhanDesaSipemandu['email_pelapor'];
                $keluhanDesaSipemandu->alamat_pelapor = $validatedRequestKeluhanDesaSipemandu['alamat_pelapor'];
                $keluhanDesaSipemandu->id_provinsi = $validatedRequestKeluhanDesaSipemandu['id_provinsi'];
                $keluhanDesaSipemandu->nama_provinsi = $validatedRequestKeluhanDesaSipemandu['nama_provinsi'];
                $keluhanDesaSipemandu->id_kota_kabupaten = $validatedRequestKeluhanDesaSipemandu['id_kota_kabupaten'];
                $keluhanDesaSipemandu->nama_kota_kabupaten = $validatedRequestKeluhanDesaSipemandu['nama_kota_kabupaten'];
                $keluhanDesaSipemandu->id_kecamatan = $validatedRequestKeluhanDesaSipemandu['id_kecamatan'];
                $keluhanDesaSipemandu->nama_kecamatan = $validatedRequestKeluhanDesaSipemandu['nama_kecamatan'];
                $keluhanDesaSipemandu->id_desa = $validatedRequestKeluhanDesaSipemandu['id_desa'];
                $keluhanDesaSipemandu->nama_desa = $validatedRequestKeluhanDesaSipemandu['nama_desa'];
                $saveKeluhanDesaSipemandu = $keluhanDesaSipemandu->create();

                $keluhanDesaSipemandu->saveAttachments($validatedRequestKeluhanDesaSipemandu['files']);
            }


            if ($isAllSuccess && $saveDiskusiBansos && $saveDiskusiBansosNIK && $saveKeluhanDesaSipemandu) {
                try {
                    $service->notifyStakeHolder($result['diskusi'], $result['produk'], $validatedRequest);
                    $service->notifyKorwilPemdaUser($diskusiBansos, DiskusiService::BANSOS_NOTIFICATION_CREATE);
                } catch (\Exception $e) {
                    // pengiriman notifikasi gagal
                }
                $this->db->commit();
                return new ObjectResponse(
                    [
                        "success" => true,
                        "data" => [
                            "diskusi_uuid" => $result['diskusi']->uuid,
                            "diskusi_id" => $result['diskusi']->id,
                        ],
                    ]
                );
            } else {
                if (!$result['res']) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Create Diskusi Error", $result['diskusi']->getMessages());
                }

                if (!$result['saveRespon']) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Create Diskusi Response Error", $result['diskusi']->getMessages());
                }

                if (!$result['saveDiskusi']) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Create Diskusi Detail Error", $result['diskusi']->getMessages());
                }

                if (!$saveDiskusiBansos) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Create Diskusi Bansos Error", $result['diskusi']->getMessages());
                }

                if (!$saveDiskusiBansosNIK) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Create Diskusi Bansos NIK Error", $result['diskusi']->getMessages());
                }

                if (!$saveKeluhanDesaSipemandu) {
                    $messages = [];
                    foreach ($keluhanDesaSipemandu->getMessages() as $error) {
                        $arr = [
                            'field' => $error->getField(),
                            'message' => $error->getMessage()
                        ];

                        array_push($messages, $arr);
                    }
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Create Keluhan Desa Error", $messages);
                }

                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "SQL Error", $result['diskusi']->getMessages());
            }
        } catch (Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function retrieveKeluhanDesa() {
        if (!$this->hasScopes(['api.consume', 'keluhan.desa.manage']) && !$this->hasScopes(['bansos.view_all'])) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
        }

        $request = $this->request->get();
        $validatedRequest = $this->validate($request, [
            'nomor' => [
                'validators' => [
                    'required',
                    [
                        'name' => 'max_string',
                        'params' => 100
                    ]
                ]
            ]
        ]);
        $nomorTiket = $validatedRequest['nomor'];

        /** @var KeluhanDesaSipemandu $keluhanDesa */
        $keluhanDesa = KeluhanDesaSipemandu::findByNomorTiket($nomorTiket);
        if (!$keluhanDesa) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Keluhan dengan nomor tiket " . $nomorTiket . " tidak ditemukan.");
        }

        return new SimpleResponse($keluhanDesa->toDetail());
    }

    private function keluhanDesaValidation($params)
    {
        $validatedRequest = $this->validate(
            $params,
            [
                'judul' => [
                    'validators' => [
                        'required',
                        [
                            'name' => 'max_string',
                            'params' => 255
                        ],
                        [
                            'name' => 'min_string',
                            'params' => 5
                        ]
                    ]
                ],
                'konten_diskusi' => [
                    'validators' => [
                        'required',
                        [
                            'name' => 'max_string',
                            'params' => 3000
                        ]
                    ]
                ],
                'nama_desa' => [
                    'validators' => [
                        'required',
                        [
                            'name' => 'max_string',
                            'params' => 255
                        ],
                        [
                            'name' => 'min_string',
                            'params' => 3
                        ]
                    ]
                ],
                'nomor_tiket' => [
                    'validators' => [
                        'required',
                        [
                            'name' => 'max_string',
                            'params' => 50
                        ],
                        [
                            'name' => 'min_string',
                            'params' => 5
                        ]
                    ]
                ],
                'id_kota_kabupaten' => ['validators' => ['required', 'digit']],
                'nik' => ['validators' => ['required']],
                'phone' => ['validators' => ['required']],
            ]
        );

        $instansi = Instansi::findFirst([
            'conditions' => 'id_kota_kabupaten = :kode_wilayah: AND id_tipe = 2 AND deleted_at IS NULL',
            'bind' => ['kode_wilayah' => $validatedRequest['id_kota_kabupaten']]
        ]);
        if (!$instansi) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi dengan kode wilayah " . $validatedRequest['id_kota_kabupaten'] . " tidak ditemukan.");
        }
        $validatedRequest['id_instansi'] = $instansi->id;

        $topikDanaDesa = TopikDiskusiBansos::findFirst([
            'conditions' => 'deskripsi ILIKE :topik: AND type = :type:',
            'bind' => ['topik' => '%Pengelolaan keuangan desa%', 'type' => TopikDiskusiBansos::TYPE_DESA]
        ]);
        if (!$topikDanaDesa) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Topik keluhan Dana Desa tidak ditemukan.");
        }
        $validatedRequest['id_topik'] = $topikDanaDesa->id;

        $validatedRequest['nama_objek'] = $validatedRequest['nama_desa'];
        $validatedRequest['objek_id'] = null;
        $validatedRequest['produk_id'] = Produk::PRODUK_ID_BANSOS;
        $validatedRequest['is_anonymous'] = 0;
        $validatedRequest['link_terkait'] = null;


        //Phone Validation
        $arrPhone = explode(";", $validatedRequest["phone"]);
        foreach ($arrPhone as $phone) {
            if (strlen($phone) < 7 || strlen($phone) > 13) {
                throw new ValidationException([
                    [
                        'field' => 'phone',
                        'message' => 'Nomor telepon (' . $phone . ') harus memiliki 7-13 digit',
                    ],
                ]);
            }

            $phonePattern = "0";
            if (!($phone[0] === $phonePattern[0] ? strncmp($phone, $phonePattern, strlen($phonePattern)) === 0 : false)) {
                throw new ValidationException([
                    [
                        'field' => 'phone',
                        'message' => 'Nomor telepon (' . $phone . ') tidak valid',
                    ],
                ]);
            }
        }

        //NIK Validation
        if (!empty($validatedRequest["nik"])) {
            $nikpkhs = explode(";", $validatedRequest["nik"]);
            foreach ($nikpkhs as $nik) {

                if (strlen($nik) != 16) {
                    throw new ValidationException([
                        [
                            'field' => 'nik',
                            'message' => 'NIK harus memiliki panjang 16 karakter(' . $nik . ')',
                        ],
                    ]);
                }

                if (!preg_match("/^\d+$/", $nik)) {
                    throw new ValidationException([
                        [
                            'field' => 'nik',
                            'message' => 'NIK harus terdiri dari angka(' . $nik . ')',
                        ],
                    ]);
                }
            }
        }

        return $validatedRequest;
    }

    private function validateKeluhanDesaSipemandu($request) {
        $validatedRequest = $this->validate(
            $request,
            [
                'nomor_tiket' => [
                    'validators' => [
                        'required',
                        [
                            'name' => 'max_string',
                            'params' => 50
                        ],
                    ]
                ],
                'nama_pelapor' => [
                    'validators' => [
                        [
                            'name' => 'max_string',
                            'params' => 100
                        ],
                    ]
                ],
                'email_pelapor' => [
                    'validators' => [
                        'email',
                        [
                            'name' => 'max_string',
                            'params' => 100
                        ],
                    ]
                ],
                'alamat_pelapor' => [
                    'validators' => [
                        [
                            'name' => 'max_string',
                            'params' => 255
                        ],
                    ]
                ],
                'id_provinsi' => ['validators' => ['digit']],
                'nama_provinsi' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 100
                ]]],
                'id_kota_kabupaten' => ['validators' => ['digit']],
                'nama_kota_kabupaten' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 100
                ]]],
                'id_kecamatan' => ['validators' => ['digit']],
                'nama_kecamatan' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 100
                ]]],
                'id_desa' => ['validators' => ['digit']],
                'nama_desa' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 100
                ]]],
                'files' => ['validators' => []]
            ]
        );

        $existing = KeluhanDesaSipemandu::findByNomorTiket($validatedRequest['nomor_tiket']);
        if ($existing) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Keluhan dengan nomor tiket " . $validatedRequest['nomor_tiket'] . " telah terdaftar sebelumnya.");
        }

        $fileParams = $validatedRequest['files'];
        $validatedFileParams = [];
        if (!empty($fileParams)) {
            foreach ($fileParams as $fileParam) {
                try {
                    $validatedFileParams[] = $this->validate($fileParam, [
                        'name' => ['validators' => ['required', [
                            'name' => 'max_string',
                            'params' => 255
                        ]]],
                        'type' => ['validators' => ['required', [
                            'name' => 'max_string',
                            'params' => 50
                        ]]],
                        'url' => ['validators' => ['required', [
                            'name' => 'max_string',
                            'params' => 255
                        ]]]
                    ]);
                } catch (ValidationException $e) {
                    $messages = [];
                    foreach ($e->getMessages() as $msg) {
                        $arr = $msg;
                        $arr['field'] = "files.".$msg['field'];
                        array_push($messages, $arr);
                    }
                    throw new ValidationException($messages);
                }
            }
            $validatedRequest['files'] = $validatedFileParams;
        }

        return $validatedRequest;
    }

    public function pemiluLaporanTerbanyakPartai() {
        $req = $this->request->get();
        $req['limit'] = array_key_exists('limit', $req) ? $req['limit'] : 5;

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

        $limit = $validatedRequest['limit'];

        $q = "
            SELECT
            	COUNT(dkp.id_objek_pemilu) AS jumlah_laporan,
            	dkp.id_objek_pemilu,
            	okp.nama
            FROM
            	jaga.diskusi_keluhan_pemilu dkp
            	LEFT JOIN objek_keluhan_pemilu okp ON dkp.id_objek_pemilu = okp.ID 
            	LEFT JOIN diskusi ds ON dkp.id_diskusi = ds.id
            WHERE
            	dkp.tipe_objek_pemilu = 'POLUANG'
            	AND ds.is_deleted = 0
            GROUP BY dkp.id_objek_pemilu, okp.nama
            ORDER BY COUNT(dkp.id_objek_pemilu) DESC
            LIMIT $limit
        ";

        $result = $this->rawQuery($q);

        return new SimpleResponse($result);
    }

    public function pemiluLaporanNilaiValuasi() {
        $req = $this->request->get();
        $req['limit'] = array_key_exists('limit', $req) ? $req['limit'] : 5;

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

        $limit = $validatedRequest['limit'];

        $q = "
            SELECT
            	SUM(dkp.total_nilai_gratifikasi) AS total_nilai_gratifikasi,
            	dkp.id_objek_pemilu,
            	okp.nama
            FROM
            	jaga.diskusi_keluhan_pemilu dkp
            	LEFT JOIN objek_keluhan_pemilu okp ON dkp.id_objek_pemilu = okp.ID 
            	LEFT JOIN diskusi ds ON dkp.id_diskusi = ds.id
            WHERE
            	dkp.tipe_objek_pemilu = 'POLUANG'
            	AND ds.is_deleted = 0
            GROUP BY dkp.id_objek_pemilu, okp.nama
            ORDER BY SUM(dkp.total_nilai_gratifikasi) DESC
            LIMIT $limit
        ";

        $result = $this->rawQuery($q);

        return new SimpleResponse($result);
    }

    public function pemiluListPartai() {
        $req = $this->request->get();
        $req['keyword'] = array_key_exists('keyword', $req) ? $req['keyword'] : '';
        $params = $this->validate(
            $req,
            [
                'keyword' => ['validators' => []],
            ]
        );

        $result = ObjekKeluhanPemilu::find([
            'tipe_objek = ?0 AND nama ILIKE ?1',
            'bind' => [
                ObjekKeluhanPemilu::TIPE_POLITIK_UANG,
                '%' . $req['keyword'] . '%'
            ],
            'order' => 'view_order ASC'
        ]);

        return new SimpleResponse($result);
    }

    public function pemiluPerPartai() {
        $req = $this->request->get();
        $req['id_objek_pemilu'] = array_key_exists('id_objek_pemilu', $req) ? $req['id_objek_pemilu'] : null;
        $req['id_instansi'] = array_key_exists('id_instansi', $req) ? $req['id_instansi'] : null;
        $params = $this->validate(
            $req,
            [
                'id_objek_pemilu' => ['validators' => ['digit', 'required']],
                'id_instansi' => ['validators' => ['digit']],
            ]
        );

        $bind = [];
        $qWhere = '';
        $cteWhere = '';

        if(!is_null($params['id_objek_pemilu'])) {
            $qWhere = 'WHERE okp.id = :id_objek_pemilu';
            $bind['id_objek_pemilu'] = $params['id_objek_pemilu'];
        }

        if(!is_null($params['id_instansi'])) {
            if($params['id_instansi'] > 0) {
                $cteWhere = 'AND db.id_instansi_origin = :id_instansi';
                $bind['id_instansi'] = $params['id_instansi'];
            }
        }

        $q = "
            WITH summary_pemilu_CTE AS (
                SELECT
                	dkp.id_objek_pemilu,
                	db.id_instansi_origin,
                	COUNT(dkp.id_objek_pemilu) AS jumlah_laporan,
                	SUM(dkp.total_nilai_gratifikasi) AS total_nilai_gratifikasi
                FROM
                	jaga.diskusi_keluhan_pemilu dkp
                	LEFT JOIN diskusi ds ON dkp.id_diskusi = ds.id
                	LEFT JOIN diskusi_bansos db ON dkp.id_diskusi_bansos = db.id
                WHERE
                	dkp.tipe_objek_pemilu = 'POLUANG'
                	AND ds.is_deleted = 0
                	$cteWhere
                GROUP BY 
                	dkp.id_objek_pemilu,
                	db.id_instansi_origin
            )
            
            SELECT
            	okp.id,	
            	okp.nama,
            	SUM(cte.jumlah_laporan) AS jumlah_laporan,
            	SUM(cte.total_nilai_gratifikasi) AS total_nilai_gratifikasi
            FROM
            	objek_keluhan_pemilu okp 
            	LEFT JOIN summary_pemilu_CTE cte ON okp.id = cte.id_objek_pemilu
            $qWhere
            GROUP BY 
            	okp.id,	
            	okp.nama
        ;";

        $result = $this->rawQuery($q, $bind);

        return new SimpleResponse($result);
    }

    public function pemiluJumlahLaporanPerPaslon() {
        $q = "
            WITH 
            paslon_CTE AS (
                SELECT paslon_didukung
            	FROM (VALUES (1), (2), (3)) AS table_alias(paslon_didukung)
            ),
            
            jumlah_laporan_paslon_CTE AS (
                SELECT
                    okp.paslon_didukung,
                    COUNT(okp.paslon_didukung) AS jumlah_laporan
                FROM
                	diskusi_keluhan_pemilu dkp 
                LEFT JOIN objek_keluhan_pemilu okp ON dkp.id_objek_pemilu = okp.id
                WHERE
                	dkp.tipe_objek_pemilu = 'POLUANG'
                	AND okp.paslon_didukung IS NOT NULL
                GROUP BY
                	okp.paslon_didukung
            )
            
            SELECT
            	ps.paslon_didukung,
              COALESCE(jps.jumlah_laporan, 0) AS jumlah_laporan
            FROM
            	paslon_CTE ps
            LEFT JOIN	jumlah_laporan_paslon_CTE jps ON ps.paslon_didukung = jps.paslon_didukung
        ;";

        $result = $this->rawQuery($q);

        return new SimpleResponse($result);
    }

    public function pemiluLhkpnPerPaslon() {
        $q = "
            WITH 
            paslon_CTE AS (
                SELECT paslon_didukung
            	FROM (VALUES (1), (2), (3)) AS table_alias(paslon_didukung)
            ),
            
            lhkpn_paslon_CTE AS (
                SELECT
                	okp.paslon_didukung,
                	COUNT(lp.id_objek_keluhan) AS jumlah_partai_lhkpn,
                	SUM(lp.wajib_lapor) AS total_wajib_lapor,
	                ROUND(100.0 * SUM(lp.lengkap) / SUM(lp.wajib_lapor), 2) AS rerata_kepatuhan,
	                MIN(data_date) AS data_date
                FROM lhkpn_pemilu lp
                LEFT JOIN objek_keluhan_pemilu okp ON lp.id_objek_keluhan = okp.id
                GROUP BY okp.paslon_didukung
            )
            
            SELECT
            	ps.paslon_didukung,
                COALESCE(lp.jumlah_partai_lhkpn, 0) AS jumlah_partai_lhkpn,
                COALESCE(lp.total_wajib_lapor, 0) AS total_wajib_lapor,
                COALESCE(lp.rerata_kepatuhan, 0) AS rerata_kepatuhan,
	            data_date
            FROM
            	paslon_CTE ps
            LEFT JOIN	lhkpn_paslon_CTE lp ON ps.paslon_didukung = lp.paslon_didukung
            ORDER BY ps.paslon_didukung
        ;";

        $result = $this->rawQuery($q);

        return new SimpleResponse($result);
    }

    public function pemiluKasusPerPaslon() {
        $q = "
            WITH
            paslon_CTE AS (
                SELECT paslon_didukung
            	FROM (VALUES (1), (2), (3)) AS table_alias(paslon_didukung)
            ),
            
            kasus_CTE AS (
                SELECT
                	okp.paslon_didukung,
                	SUM(pkp.jumlah_kasus) AS jumlah_kasus,
                	SUM(pkp.total_gratif) AS total_gratif,
                	SUM(pkp.total_calon_mantan_napi) AS total_calon_mantan_napi
                FROM pemilu_kasus_partai pkp
                LEFT JOIN objek_keluhan_pemilu okp ON pkp.id_objek_keluhan = okp.id
                WHERE okp.paslon_didukung IS NOT NULL
                GROUP BY okp.paslon_didukung
            )
            
            SELECT
            	ps.paslon_didukung,
                COALESCE(ks.jumlah_kasus, 0) AS jumlah_kasus,
                COALESCE(ks.total_gratif, 0) AS total_gratif,
                COALESCE(ks.total_calon_mantan_napi, 0) AS total_calon_mantan_napi
            FROM
            	paslon_CTE ps
            LEFT JOIN	kasus_CTE ks ON ps.paslon_didukung = ks.paslon_didukung
            ORDER BY ps.paslon_didukung
        ;";

        $result = $this->rawQuery($q);

        return new SimpleResponse($result);
    }

    public function pemiluRekamJejakPerPartai($id) {
        $bind = [
            'id' => $id
        ];

        $q = "
            WITH
            detail_partai_CTE AS (
                SELECT
                	okp.id,
                	okp.nama,
                	COALESCE(lp.wajib_lapor, 0) AS wajib_lapor,
                	COALESCE(ROUND( 100.0 * lp.lengkap / lp.wajib_lapor, 2 ), 0) AS kepatuhan,
                	lp.data_date AS lhkpn_data_date,
                	pkp.jumlah_kasus,
                	pkp.total_gratif,
                	pkp.total_calon_mantan_napi
                FROM
                	objek_keluhan_pemilu okp
                	LEFT JOIN lhkpn_pemilu lp ON okp.id = lp.id_objek_keluhan
                	LEFT JOIN pemilu_kasus_partai pkp ON okp.id = pkp.id_objek_keluhan
                WHERE okp.tipe_objek = 'POLUANG'
                AND okp.id = :id
            ),

            keluhan_CTE AS (
                SELECT
                	id_objek_pemilu,
                	COUNT ( id_objek_pemilu ) AS jumlah_laporan 
                FROM
                	diskusi_keluhan_pemilu 
                WHERE
                	tipe_objek_pemilu = 'POLUANG'
                	AND id_objek_pemilu = :id 
                GROUP BY
                	id_objek_pemilu
            )

            SELECT 
            	dp.*,
            	COALESCE(kc.jumlah_laporan, 0) AS jumlah_laporan
            FROM 
            	detail_partai_CTE dp
            	LEFT JOIN keluhan_CTE kc ON dp.id = kc.id_objek_pemilu
        ;";

        $result = $this->rawQuery($q, $bind);

        return new SimpleResponse($result);
    }

    public function pemiluLhkpnCapresCawapres() {
        $q = "
            SELECT 
                	id,
                	no_urut_paslon,
                	year,
                    id_lhkpn,
                	nama,
                	jabatan,
                    tanggal_lapor,
                    status_laporan,
                	(total_harta_tidak_bergerak + total_alat_transportasi + total_harta_bergerak_lain + total_surat_berharga + total_kas + total_harta_lainnya - total_hutang) AS total_kekayaan
                FROM
                	jaga.lhkpn_pemilu_capres_cawapres
        ;";

        $data = $this->rawQuery($q);

        $result = [
            1 => [],
            2 => [],
            3 => [],
        ];

        foreach ($data as $item) {
            $result[$item['no_urut_paslon']][] = $item;
        }

        return new SimpleResponse($result);
    }

    public function pemiluLaporan() {
        $q = "
            WITH 
            paslon_CTE AS (
                SELECT paslon_didukung
            	FROM (VALUES (1), (2), (3)) AS table_alias(paslon_didukung)
            ),
            
            jumlah_laporan_paslon_CTE AS (
                SELECT
                    okp.paslon_didukung,
                    COUNT(okp.paslon_didukung) AS jumlah_laporan
                FROM
                	diskusi_keluhan_pemilu dkp 
                LEFT JOIN objek_keluhan_pemilu okp ON dkp.id_objek_pemilu = okp.id
                WHERE
                	dkp.tipe_objek_pemilu = 'POLUANG'
                	AND okp.paslon_didukung IS NOT NULL
                GROUP BY
                	okp.paslon_didukung
            )
            
            SELECT
            	ps.paslon_didukung,
              COALESCE(jps.jumlah_laporan, 0) AS jumlah_laporan
            FROM
            	paslon_CTE ps
            LEFT JOIN	jumlah_laporan_paslon_CTE jps ON ps.paslon_didukung = jps.paslon_didukung
        ;";

        $laporanPerPaslon = $this->rawQuery($q);

        $q = "
            WITH 
            paslon_CTE AS (
                SELECT paslon_didukung
            	FROM (VALUES (1), (2), (3)) AS table_alias(paslon_didukung)
            ),
            
            lhkpn_paslon_CTE AS (
                SELECT
                	okp.paslon_didukung,
                	COUNT(lp.id_objek_keluhan) AS jumlah_partai_lhkpn,
                	SUM(lp.wajib_lapor) AS total_wajib_lapor,
	                ROUND(100.0 * SUM(lp.lengkap) / SUM(lp.wajib_lapor), 2) AS rerata_kepatuhan,
	                MIN(data_date) AS data_date
                FROM lhkpn_pemilu lp
                LEFT JOIN objek_keluhan_pemilu okp ON lp.id_objek_keluhan = okp.id
                GROUP BY okp.paslon_didukung
            )
            
            SELECT
            	ps.paslon_didukung,
                COALESCE(lp.jumlah_partai_lhkpn, 0) AS jumlah_partai_lhkpn,
                COALESCE(lp.total_wajib_lapor, 0) AS total_wajib_lapor,
                COALESCE(lp.rerata_kepatuhan, 0) AS rerata_kepatuhan,
	            data_date
            FROM
            	paslon_CTE ps
            LEFT JOIN	lhkpn_paslon_CTE lp ON ps.paslon_didukung = lp.paslon_didukung
            ORDER BY ps.paslon_didukung
        ;";

        $lhkpnPerPaslon = $this->rawQuery($q);

        $q = "
            SELECT 
                	id,
                	no_urut_paslon,
                	year,
                    id_lhkpn,
                	nama,
                	jabatan,
                    tanggal_lapor,
                    status_laporan,
                	(total_harta_tidak_bergerak + total_alat_transportasi + total_harta_bergerak_lain + total_surat_berharga + total_kas + total_harta_lainnya - total_hutang) AS total_kekayaan
                FROM
                	jaga.lhkpn_pemilu_capres_cawapres
        ;";

        $data = $this->rawQuery($q);

        $lhkpnCapresCawapres = [
            1 => [],
            2 => [],
            3 => [],
        ];

        foreach ($data as $item) {
            $lhkpnCapresCawapres[$item['no_urut_paslon']][] = $item;
        }

        return new SimpleResponse([
            "laporanPerPaslon" => $laporanPerPaslon,
            "lhkpnPerPaslon" => $lhkpnPerPaslon,
            "lhkpnCapresCawapres" => $lhkpnCapresCawapres
        ]);
    }

    public function pemiluStat() {
        $req = $this->request->get();
        $req['limit'] = array_key_exists('limit', $req) ? $req['limit'] : 5;

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

        $limit = $validatedRequest['limit'];

        $q = "
            SELECT
            	COUNT(dkp.id_objek_pemilu) AS jumlah_laporan,
            	dkp.id_objek_pemilu,
            	okp.nama
            FROM
            	jaga.diskusi_keluhan_pemilu dkp
            	LEFT JOIN objek_keluhan_pemilu okp ON dkp.id_objek_pemilu = okp.ID 
            	LEFT JOIN diskusi ds ON dkp.id_diskusi = ds.id
            WHERE
            	dkp.tipe_objek_pemilu = 'POLUANG'
            	AND ds.is_deleted = 0
            GROUP BY dkp.id_objek_pemilu, okp.nama
            ORDER BY COUNT(dkp.id_objek_pemilu) DESC
            LIMIT $limit
        ";

        $laporanTerbanyak = $this->rawQuery($q);

        $q = "
            SELECT
            	SUM(dkp.total_nilai_gratifikasi) AS total_nilai_gratifikasi,
            	dkp.id_objek_pemilu,
            	okp.nama
            FROM
            	jaga.diskusi_keluhan_pemilu dkp
            	LEFT JOIN objek_keluhan_pemilu okp ON dkp.id_objek_pemilu = okp.ID 
            	LEFT JOIN diskusi ds ON dkp.id_diskusi = ds.id
            WHERE
            	dkp.tipe_objek_pemilu = 'POLUANG'
            	AND ds.is_deleted = 0
            GROUP BY dkp.id_objek_pemilu, okp.nama
            ORDER BY SUM(dkp.total_nilai_gratifikasi) DESC
            LIMIT $limit
        ";

        $nilaiValuasi = $this->rawQuery($q);

        return new SimpleResponse([
            "laporanTerbanyak" => $laporanTerbanyak,
            "nilaiValuasi" => $nilaiValuasi
        ]);
    }

    public function keluhanUser()
    {
        $params = $this->request->get();

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

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

        if (!is_null($params["keluhan_type"])) {
            $strWhere = "Produk.id_index = :produk_id: and TopikDiskusiBansos.type = :keluhan_type:";

            $bind = [
                "produk_id" => 9,
                "keluhan_type" => $params["keluhan_type"]
            ];
        } else {
            $strWhere = "Produk.id_index = :produk_id: ";

            $bind = [
                "produk_id" => 9
            ];
        }

        $strWhere .= " and Diskusi.user_id = :user_id: and Diskusi.type = 0 and is_deleted = 0 and is_anonymous = 0";

        $bind["user_id"] = $params["user_id"];

        $query = Diskusi::query()
            ->columns("Diskusi.id, Diskusi.uuid, Diskusi.title, Diskusi.content, Diskusi.created_at, Diskusi.updated_at, UmUser.um_user_name as nama, UmUser.uuid_user, Produk.nama as nama_produk, TopikDiskusiBansos.type as keluhan_type")
            ->join('UmUser', 'Diskusi.user_id = UmUser.um_id')
            ->join('KategoriDiskusi', 'Diskusi.kategori_diskusi_id = KategoriDiskusi.id')
            ->join('Produk', 'KategoriDiskusi.produk_id = Produk.id_index')
            ->join('DiskusiBansos', 'DiskusiBansos.id_diskusi = Diskusi.id')
            ->join('TopikDiskusiBansos', 'TopikDiskusiBansos.id = DiskusiBansos.id_topik');

        $queryCount = Diskusi::query()
            ->columns("count(1) as jumlah")
            ->join('UmUser', 'Diskusi.user_id = UmUser.um_id')
            ->join('KategoriDiskusi', 'Diskusi.kategori_diskusi_id = KategoriDiskusi.id')
            ->join('Produk', 'KategoriDiskusi.produk_id = Produk.id_index')
            ->join('DiskusiBansos', 'DiskusiBansos.id_diskusi = Diskusi.id')
            ->join('TopikDiskusiBansos', 'TopikDiskusiBansos.id = DiskusiBansos.id_topik');


        $orderBy = "Diskusi.updated_at desc";

        $result = $query
            ->where($strWhere)
            ->bind($bind)
            ->limit($params["limit"], $params["offset"])
            ->orderBy($orderBy)
            ->execute()
            ->toArray();

        for ($i = 0; $i < count($result); $i++) {
            $diskusi_id = $result[$i]["id"];
            $result[$i]['count_like'] = DiskusiLike::find("diskusi_id='$diskusi_id'")->count();
            $result[$i]['count_comment'] = DiskusiComment::find("diskusi_id='$diskusi_id'")->count();
            $report = Diskusi::getReport($result[$i]['id']);
            if (empty($report)) {
                $result[$i]['laporan'] = null;
            } else {
                $result[$i]['laporan'] = $report->simpleToArray();
            }
        }


        $totalCount = $queryCount
            ->where($strWhere)
            ->bind($bind)
            ->execute()
            ->toArray();

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

    public function updateStatus() {
        date_default_timezone_set('Asia/Jakarta');
        $this->checkScopes(['discussion.react']);

        // validasi dan init service variables
        $service = new BansosService($this);
        $rawBody = $this->request->getPost();
        $validatedRequest = $service->validateUpdateStatus($rawBody);

        try {
            $this->db->begin();
            $now = date('Y-m-d H:i:s');

            // simpan komentar
            $komentar = new DiskusiComment();
            $komentar->user_id = $this->shared->user->um_id;
            $komentar->diskusi_id = $service->diskusi->id;
            $komentar->level = 0;
            $komentar->comment = $validatedRequest["komentar"];
            $komentar->created_at = $now;
            $komentar->updated_at = $now;
            if (!$komentar->save()) {
                throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Gagal menyimpan komentar");
            }
            $service->komentar = DiskusiComment::findFirst(
                [
                    "id = :id:",
                    "bind" => [
                        "id" => $komentar->id
                    ]
                ]
            );

            // upload file jika ada
            $diskusiService = new DiskusiService($this->config, $this->shared, $this->request, $this->minio);
            $service->komentar->commentAttachment = $diskusiService->processCommentAttachment($komentar->id);
            $service->komentar->save();

            DiskusiRespon::saveResponByDiskusiAndKomentar($service->diskusi, $service->komentar);
            DiskusiLikeComment::saveLikeCommentByDiskusi($service->diskusi);

            // Handle Keluhan is_responded
            if ($service->diskusi->user_id != $this->shared->user->um_id) {
                if($this->hasOneOfRoles(['admin_bansos_klop', 'superadmin_bansos', 'admin_bansos_korwil', 'admin_keluhan_ikn_klop','admin_keluhan_desa_klop','admin_keluhan_kampus_klop','admin_keluhan_pelabuhan_klop','admin_keluhan_bpjs_klop', 'admin_keluhan_pendidikan_klop'])) {
                    $service->keluhan->isresponded = 0;
                    $service->keluhan->save();
                }
            } elseif($service->keluhan->status == 1) {
                    $service->keluhan->isresponded = 1;
                    $service->keluhan->save();
            }

            $this->shared->user->jmlh_dukung_crt_mskn++;
            $this->shared->user->save();

            //NOTE: Disini penerusan keluhannya
            $service->handleActivity($validatedRequest, $komentar);

            $userData = UmUser::findFirst([
                "columns" => ["um_id", "foto", "presigned_url", "presigned_exp"],
                "conditions" => "um_id = :um_id:",
                "bind" => ["um_id" => $this->shared->user->um_id]
            ])->toArray();

            $this->db->commit();

            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "komentar_id" => $komentar->id,
                        "komentar" => $komentar->comment,
                        "uuid_user" => $this->shared->user->uuid_user,
                        "profile_picture_url" => MediaHelper::getPresignedUrlFoto($userData, UmUser::class, $this->config, $this->minio)
                    ]
                ]
            );
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }
}