<?php

class StranaspkLogframeInstansi2024Controller extends BaseController
{
     public function get_all_logframe_instansi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        $db->begin();
        try {
            $query = "SELECT id, nama_instansi, urutan_instansi, nama_pendek_instansi, m_jenis_instansi_id FROM m_instansi";
            $result = $db->query($query);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            if (!empty($data)) {
                return $data;
            } else {
                return null;
            }
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function get_all_score_provinsi(){
        $db = $this->getDI()->get('databaseNewStranas2024');
        $getParams = $this->request->getJsonRawBody(true);
        // $all_id_instansi = $getParams["id"];
        // Mengonversi array PHP menjadi string yang sesuai untuk klausa IN dalam SQL
        // Buat string placeholder dengan jumlah yang sesuai
        $placeholders = implode(', ', array_fill(0, count($getParams), '?'));
        $db->begin();
        try {
            $query = "WITH InstansiCTE AS (
                SELECT
                    id AS instansi_id,
                    nama_instansi
                FROM
                    m_instansi
                WHERE
                    m_jenis_instansi_id = 2 -- Ganti dengan nilai yang sesuai
            )
            SELECT
                ic.instansi_id,
                ic.nama_instansi,
                mka.id AS aksi_id,
                mko.id AS output_id,
                mkm.id AS milestone_id,
                mka.urutan_aksi,
                mko.urutan_output,
                mkm.urutan_milestone,
                mkm.nama_milestone,
                mko.nama_output,
                mka.nama_aksi,
                timv.score_b03,
                timv.score_b06,
                timv.score_b09,
                timv.score_b12,
                timv.score_b15,
                timv.score_b18,
                timv.score_b21,
                timv.score_b24,
                COALESCE(timv.score_b03, 0) AS new_score_b03,
                COALESCE(timv.score_b06, timv.score_b03) AS new_score_b06,
                COALESCE(timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b09,
                COALESCE(timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b12,
                COALESCE(timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b15,
                COALESCE(timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b18,
                COALESCE(timv.score_b21, timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b21,
                COALESCE(timv.score_b24, timv.score_b21, timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b24
            FROM
                m_kl_milestone AS mkm
            LEFT JOIN
                m_kl_output AS mko ON mko.id = mkm.m_kl_output_id
            LEFT JOIN
                m_kl_aksi AS mka ON mka.id = mko.m_kl_aksi_id
            LEFT JOIN
                t_instansi_milestone AS tim ON tim.m_kl_milestone_id = mkm.id
            LEFT JOIN
                InstansiCTE AS ic ON ic.instansi_id = tim.m_instansi_id
            LEFT JOIN
                t_inputan_milestone_verificator AS timv ON timv.m_kl_milestone_id = mkm.id
            WHERE
                tim.periode_milestone = 'B03' AND
                ic.instansi_id IN ($placeholders)
            ORDER BY
                mka.urutan_aksi";
                
            $result = $db->query($query, $getParams);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            if (!empty($data)) {
                return new SimpleResponse($data);
            } else {
                return new SimpleResponse([]);
            }
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }
     

     public function get_all_score_kabupaten(){
        $db = $this->getDI()->get('databaseNewStranas2024');
        $getParams = $this->request->getJsonRawBody(true);
        // $all_id_instansi = $getParams["id"];
        // Mengonversi array PHP menjadi string yang sesuai untuk klausa IN dalam SQL
        // Buat string placeholder dengan jumlah yang sesuai
        $placeholders = implode(', ', array_fill(0, count($getParams), '?'));
        $db->begin();
        try {
            $query = "WITH InstansiCTE AS (
                SELECT
                    id AS instansi_id,
                    nama_instansi
                FROM
                    m_instansi
                WHERE
                    m_jenis_instansi_id = 3 -- Ganti dengan nilai yang sesuai
            )
            SELECT
                ic.instansi_id,
                ic.nama_instansi,
                mka.id AS aksi_id,
                mko.id AS output_id,
                mkm.id AS milestone_id,
                mka.urutan_aksi,
                mko.urutan_output,
                mkm.urutan_milestone,
                mkm.nama_milestone,
                mko.nama_output,
                mka.nama_aksi,
                timv.score_b03,
                timv.score_b06,
                timv.score_b09,
                timv.score_b12,
                timv.score_b15,
                timv.score_b18,
                timv.score_b21,
                timv.score_b24,
                COALESCE(timv.score_b03, 0) AS new_score_b03,
                COALESCE(timv.score_b06, timv.score_b03) AS new_score_b06,
                COALESCE(timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b09,
                COALESCE(timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b12,
                COALESCE(timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b15,
                COALESCE(timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b18,
                COALESCE(timv.score_b21, timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b21,
                COALESCE(timv.score_b24, timv.score_b21, timv.score_b18, timv.score_b15, timv.score_b12, timv.score_b09, timv.score_b09, timv.score_b06, timv.score_b03) AS new_score_b24
            FROM
                m_kl_milestone AS mkm
            LEFT JOIN
                m_kl_output AS mko ON mko.id = mkm.m_kl_output_id
            LEFT JOIN
                m_kl_aksi AS mka ON mka.id = mko.m_kl_aksi_id
            LEFT JOIN
                t_instansi_milestone AS tim ON tim.m_kl_milestone_id = mkm.id
            LEFT JOIN
                InstansiCTE AS ic ON ic.instansi_id = tim.m_instansi_id
            LEFT JOIN
                t_inputan_milestone_verificator AS timv ON timv.m_kl_milestone_id = mkm.id
            WHERE
                tim.periode_milestone = 'B03' AND
                ic.instansi_id IN ($placeholders)
            ORDER BY
                mka.urutan_aksi";
                
            $result = $db->query($query, $getParams);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            if (!empty($data)) {
                return new SimpleResponse($data);
            } else {
                return new SimpleResponse([]);
            }
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function get_logframe_instansi(){ 
        $db = $this->getDI()->get('databaseNewStranas2024');
        $db->begin();
        try {
            $query = "SELECT id, nama_instansi, urutan_instansi, nama_pendek_instansi, logo_instansi FROM m_instansi WHERE m_jenis_instansi_id = 1";
            $result = $db->query($query);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            if (!empty($data)) {
                return $data;
            } else {
                return null;
            }
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function get_logframe_provinsi(){ 
        $db = $this->getDI()->get('databaseNewStranas2024');
        $db->begin();
        try {
            $query = "SELECT id, nama_instansi, urutan_instansi, nama_pendek_instansi, logo_instansi FROM m_instansi WHERE m_jenis_instansi_id = 2";
            $result = $db->query($query);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            if (!empty($data)) {
                return $data;
            } else {
                return null;
            }
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function get_logframe_kabupaten(){ 
        $db = $this->getDI()->get('databaseNewStranas2024');
        $db->begin();
        try {
            $query = "SELECT id, nama_instansi, urutan_instansi, nama_pendek_instansi, logo_instansi FROM m_instansi WHERE m_jenis_instansi_id = 3";
            $result = $db->query($query);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            if (!empty($data)) {
                return $data;
            } else {
                return null;
            }
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function getdataInstansiByJenisInstansi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        $getParams = $this->request->getJsonRawBody(true);
        $m_jenis_instansi_id = $getParams['m_jenis_instansi_id'];
        $db->begin();
        try {
            $query = "SELECT nama_instansi, nama_pendek_instansi FROM m_instansi WHERE m_jenis_instansi_id = :m_jenis_instansi_id;
            ;";

            $value = [
                'm_jenis_instansi_id' => $m_jenis_instansi_id
            ];
            $result = $db->query($query, $value);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            if (!empty($data)) {
                return new SimpleResponse($data);
            } else {
                return new SimpleResponse([]);
            }
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function getdataInstansiByMilestoneId(){
        $db = $this->getDI()->get('databaseNewStranas2024');
        $getParams = $this->request->getJsonRawBody(true);
        $milestone_id = $getParams['milestone_id'];
        $db->begin();
        try {
            $query = "SELECT mi.nama_instansi from m_kl_milestone as mkm 
            left join t_instansi_milestone as tim 
            on tim.m_kl_milestone_id = mkm.id
            left join m_instansi as mi on mi.id = tim.m_instansi_id
            where mkm.id = :milestone_id;
            ;";

            $value = [
                'milestone_id' => $milestone_id
            ];
            $result = $db->query($query, $value);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            if (!empty($data)) {
                return new SimpleResponse($data);
            } else {
                return new SimpleResponse([]);
            }
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function get_logframe_all_instansi(){ 
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        $db->begin();
        try {
            $query = "SELECT id, nama_instansi, urutan_instansi, nama_pendek_instansi, logo_instansi FROM m_instansi";
            $result = $db->query($query);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            if (!empty($data)) {
                return $data;
            } else {
                return null;
            }
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function get_logframe_jenis_instansi(){ 
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        $db->begin();
        try {
            $query = "SELECT id, kode_instansi FROM m_jenis_instansi";
            $result = $db->query($query);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $db->commit();

            if (!empty($data)) {
                return new SimpleResponse($data);
            } else {
                return new SimpleResponse([]);
            }
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        }
     }

     public function submit_logframe_instansi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        
        $getParams = $this->request->getJsonRawBody(true);
        $nama_instansi = $getParams["nama_instansi"];
        $urutan_instansi = $getParams["urutan_instansi"];
        $nama_pendek_instansi = $getParams["nama_pendek_instansi"];
        $m_jenis_instansi_id = $getParams["m_jenis_instansi_id"];
        $username = $getParams["username"];
        $db->begin();

        try{

            $this->setchange_by();

            $query = "INSERT INTO m_instansi (nama_instansi, urutan_instansi, nama_pendek_instansi, m_jenis_instansi_id, created_at, updated_at) VALUES(:nama_instansi, :urutan_instansi, :nama_pendek_instansi, :m_jenis_instansi_id, :created_at, :updated_at)";
 
            $values = [
             'nama_instansi' => $nama_instansi,
             'urutan_instansi' => intval($urutan_instansi),
             'nama_pendek_instansi' => $nama_pendek_instansi,
             'm_jenis_instansi_id' => $m_jenis_instansi_id,
             'created_at' => date('Y-m-d H:i:s'),
             'updated_at' => date('Y-m-d H:i:s')
            ];

           $db->execute($query, $values);

           $db->commit();

           return new SimpleResponse([
              'message' => "Sukses Insert Kedalam Database"
           ]);
        }catch(Exception $e){
            $db->rollback();

            return new SimpleResponse([
                'message' => "Gagal Mengupdate Data " . $e->getMessage()
            ]);
        }
     }

     public function edit_logframe_instansi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        
        $getParams = $this->request->getJsonRawBody(true);
        $nama_instansi = $getParams["nama_instansi"];
        $urutan_instansi = $getParams["urutan_instansi"];
        $nama_pendek_instansi = $getParams["nama_pendek_instansi"];
        $m_jenis_instansi_id = $getParams["m_jenis_instansi_id"];
        $id = $getParams["id"];
        $username = $getParams["username"];
        $db->begin();

        try{

            $this->setchange_by();

            $query = "UPDATE m_instansi SET nama_instansi = :nama_instansi, urutan_instansi = :urutan_instansi, nama_pendek_instansi = :nama_pendek_instansi, m_jenis_instansi_id = :m_jenis_instansi_id, updated_at = :updated_at WHERE id = :id";
 
            $values = [
                'id' => $id,
                'nama_instansi' => $nama_instansi,
                'urutan_instansi' => intval($urutan_instansi),
                'nama_pendek_instansi' => $nama_pendek_instansi,
                'm_jenis_instansi_id' => $m_jenis_instansi_id,
                'updated_at' => date('Y-m-d H:i:s')
            ];

           $db->execute($query, $values);

           $db->commit();

            return new SimpleResponse([
                'message' => "Sukses Mengupdate Database"
            ]);
        }catch(Exception $e){
            $db->rollback();
            return new SimpleResponse([
                'message' => "Gagal Mengupdate Data " . $e->getMessage()
            ]);
        }
     }

     public function delete_logframe_instansi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        $getParams = $this->request->getJsonRawBody(true);
        $id = $getParams["id"];
        $username = $getParams["username"];
        $db->begin();

        try{

            $this->setchange_by();

            $query = "DELETE FROM m_instansi WHERE id = :id";
 
            $values = [
             'id' => $id
            ];

            $db->execute($query, $values);

            $db->commit();

            return new SimpleResponse([
                'message' => "Sukses Delete data output"
            ]);
        }catch(Exception $e){
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Gagal Mengupdate Data " . $e->getMessage()
            ]);
        }
     }

     public function search_logframe_instansi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        $getParams = $this->request->getJsonRawBody(true);
        // Eksekusi query dengan parameter binding
        $keyword = '%' . $getParams["keyword"] . '%';
        $db->begin();

        try{
              $query = "SELECT * FROM m_instansi WHERE nama_instansi ILIKE :keyword OR nama_pendek_instansi ILIKE :keyword";
 
              $values = [
                'keyword' => $keyword
              ];
  
              $result = $db->query($query, $values);
              // Menggunakan fetchAll untuk mendapatkan hasil query
              $data = $result->fetchAll(PDO::FETCH_ASSOC);
  
              $db->commit();
  
              // Menggunakan fetchAll untuk mendapatkan hasil query
              if (!empty($data)) {
                return new SimpleResponse($data);
              } else {
                return new SimpleResponse([]);
              }
        }catch(Exception $e){
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Gagal Mengupdate Data " . $e->getMessage()
            ]);
        }
     }

     private function CheckNamaUser($username){
        $dbJaga = $this->getDI()->get('db');
        $dbJaga->begin();
        try {
            $query = "SELECT nama FROM um_user WHERE um_user_name = :username OR email = :email";
            $result = $dbJaga->query($query, [
                'username' => $username,
                'email' => $username
            ]);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);

            $dbJaga->commit();

            if (!empty($data)) {
                return $data;
            } else {
                return null;
            }
        }catch(Exception $e){
            $dbJaga->rollback();
            return new SimpleResponse([
                'Message' => "Gagal Menampilkan Data" . $e->getMessage()
            ]);
        } 
    }

     private function setchange_by(){
        $db = $this->getDI()->get('databaseNewStranas2024');
        $user = $this->shared->user;
        $umId = $user->um_id;
        $namaUser = $this->CheckNamaUser($umId);
        $current_user = $namaUser[0]['nama'];
        $set_username = "UPDATE session_username SET nama = :nama WHERE id = 1";
        $value = [
            'nama' => $current_user
        ];
        $db->execute($set_username, $value);
    }
}