<?php

class StranaspkLogframePerubahan2025Controller extends BaseController
{

    public function sendtoperubahanlog(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        // Eksekusi query dengan parameter binding
        $kategori_perubahan = $getParams['kategori_perubahan'];
        $nama_perubahan = $getParams['nama_perubahan'];
        $nama_permintaan_perubahan = $getParams['nama_permintaan_perubahan'];
        $nama_verifikator = $getParams['nama_verifikator'];
        $nama_kohar = $getParams['nama_kohar'];
        $penjelasan_perubahan = $getParams['penjelasan_perubahan'];
        $periode = $getParams['periode'];
        $attachment = $getParams['attachments'];

        $db->begin();
        try{
            if($kategori_perubahan == 'Aksi'){
                $query_select = 'SELECT nama_aksi FROM m_kl_aksi WHERE id = :id';
            }else if($kategori_perubahan == 'Output'){
                $query_select = 'SELECT nama_output FROM m_kl_output WHERE id = :id';  
            }else if($kategori_perubahan == 'Milestone'){
                $query_select = 'SELECT nama_milestone FROM m_kl_milestone WHERE id = :id';
            }else if($kategori_perubahan == 'Target'){
                $query_select = 'SELECT nama_target FROM t_kl_target WHERE id = :id';
            }else if($kategori_perubahan == 'Instansi'){
                $query_select = 'SELECT nama_instansi FROM m_instansi WHERE id = :id';
            }else if($kategori_perubahan == 'Indikator Penilaian Target'){
                $query_select = 'SELECT indikator_penilaian_target FROM t_kl_target WHERE id = :id';
            }else if($kategori_perubahan == 'Nilai Target'){
                if($periode == 'B03'){
                    $query_select = 'SELECT nilai_b03 FROM t_kl_target WHERE id = :id';
                }else if($periode == 'B06'){
                    $query_select = 'SELECT nilai_b06 FROM t_kl_target WHERE id = :id';
                }else if($periode == 'B09'){
                    $query_select = 'SELECT nilai_b09 FROM t_kl_target WHERE id = :id';
                }else if($periode == 'B12'){
                    $query_select = 'SELECT nilai_b12 FROM t_kl_target WHERE id = :id';
                }else if($periode == 'B15'){
                    $query_select = 'SELECT nilai_b15 FROM t_kl_target WHERE id = :id';
                }else if($periode == 'B18'){
                    $query_select = 'SELECT nilai_b18 FROM t_kl_target WHERE id = :id';
                }else if($periode == 'B21'){
                    $query_select = 'SELECT nilai_b21 FROM t_kl_target WHERE id = :id';
                }else if($periode == 'B24'){
                    $query_select = 'SELECT nilai_b24 FROM t_kl_target WHERE id = :id';
                }
            }else if($kategori_perubahan == 'Nilai Milestone'){
                if($periode == 'B03'){
                    $query_select = 'SELECT score_b03 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = :id';
                }else if($periode == 'B06'){
                    $query_select = 'SELECT score_b06 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = :id';
                }else if($periode == 'B09'){
                    $query_select = 'SELECT score_b09 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = :id';
                }else if($periode == 'B12'){
                    $query_select = 'SELECT score_b12 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = :id';
                }else if($periode == 'B15'){
                    $query_select = 'SELECT score_b15 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = :id';
                }else if($periode == 'B18'){
                    $query_select = 'SELECT score_b18 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = :id';
                }else if($periode == 'B21'){
                    $query_select = 'SELECT score_b21 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = :id';
                }else if($periode == 'B24'){
                    $query_select = 'SELECT score_b24 FROM t_inputan_milestone_verificator WHERE m_kl_milestone_id = :id';
                }
            }else if ($kategori_perubahan == 'Milestone - Instansi'){
                  $query_select = "SELECT mkm.nama_milestone, mi.nama_instansi FROM m_kl_milestone AS mkm LEFT JOIN t_instansi_milestone AS tim ON mkm.id = tim.m_kl_milestone_id LEFT JOIN m_instansi AS mi ON mi.id = tim.m_instansi_id WHERE mkm.id = :id AND tim.periode_milestone = 'B03'";
            }
            
            $values = [
                'id' => $nama_perubahan
            ];
            
            $result = $db->query($query_select, $values);
            // Menggunakan fetchAll untuk mendapatkan hasil query
            $data = $result->fetchAll(PDO::FETCH_ASSOC);
            
            $query = "INSERT INTO perubahan_log (kategori_perubahan, nama_perubahan, nama_permintaan_perubahan, nama_verifikator, nama_kohar, penjelasan_perubahan, periode, tanggal_pengajuan) VALUES(:kategori_perubahan, :nama_perubahan, :nama_permintaan_perubahan, :nama_verifikator, :nama_kohar, :penjelasan_perubahan, :periode, :tanggal_pengajuan)";
            
            $user = $this->shared->user;
            $umId = $user->um_id;
            $username = $this->CheckNamaUser($umId);
            $array_data = [];
            
            if($kategori_perubahan == 'Aksi'){
               $variable_nama_perubahan = $data[0]['nama_aksi'];
            }else if($kategori_perubahan == 'Output'){
               $variable_nama_perubahan = $data[0]['nama_output'];
            }else if($kategori_perubahan == 'Milestone'){
                $variable_nama_perubahan = $data[0]['nama_milestone'];
            }else if($kategori_perubahan == 'Target'){
                $variable_nama_perubahan = $data[0]['nama_target'];
            }else if($kategori_perubahan == 'Instansi'){
                $variable_nama_perubahan = $data[0]['nama_instansi'];
            }else if($kategori_perubahan == 'Indikator Penilaian Target'){
                $variable_nama_perubahan = $data[0]['indikator_penilaian_target'];
            }else if($kategori_perubahan == 'Nilai Target'){
                if($periode == 'B03'){
                    $variable_nama_perubahan = $data[0]['nilai_b03'];
                }else if($periode == 'B06'){
                    $variable_nama_perubahan = $data[0]['nilai_b06'];
                }else if($periode == 'B09'){
                    $variable_nama_perubahan = $data[0]['nilai_b09'];
                }else if($periode == 'B12'){
                    $variable_nama_perubahan = $data[0]['nilai_b12'];
                }else if($periode == 'B15'){
                    $variable_nama_perubahan = $data[0]['nilai_b15'];
                }else if($periode == 'B18'){
                    $variable_nama_perubahan = $data[0]['nilai_b18'];
                }else if($periode == 'B21'){
                    $variable_nama_perubahan = $data[0]['nilai_b21'];
                }else if($periode == 'B24'){
                    $variable_nama_perubahan = $data[0]['nilai_b24'];
                }
            }else if($kategori_perubahan == 'Nilai Milestone'){
                if($periode == 'B03'){
                    $variable_nama_perubahan = $data[0]['score_b03'];
                }else if($periode == 'B06'){
                    $variable_nama_perubahan = $data[0]['score_b06'];
                }else if($periode == 'B09'){
                    $variable_nama_perubahan = $data[0]['score_b09'];
                }else if($periode == 'B12'){
                    $variable_nama_perubahan = $data[0]['score_b12'];
                }else if($periode == 'B15'){
                    $variable_nama_perubahan = $data[0]['score_b15'];
                }else if($periode == 'B18'){
                    $variable_nama_perubahan = $data[0]['score_b18'];
                }else if($periode == 'B21'){
                    $variable_nama_perubahan = $data[0]['score_b21'];
                }else if($periode == 'B24'){
                    $variable_nama_perubahan = $data[0]['score_b24'];
                }
            }else if($kategori_perubahan == 'Milestone - Instansi'){   
                for ($i = 0; $i < count($nama_permintaan_perubahan); $i++) {
                    $queryTambahan = 'SELECT nama_instansi FROM m_instansi WHERE id = :id';
                    $values = [
                        'id' => $nama_permintaan_perubahan[$i]
                    ];
                    // Jalankan query
                    $m_instansi = $db->query($queryTambahan, $values);
                    // Ambil hasil query
                    $id = $m_instansi->fetchAll(PDO::FETCH_ASSOC);
                    // Tambahkan data ke array
                    $array_data = array_merge($array_data, $id);
                }

                $pemisah = '-';
                $variable_nama_perubahan = $data[0]['nama_milestone'].$pemisah.$data[0]['nama_instansi'];
            }

            if(is_array($nama_permintaan_perubahan)){
                // Ambil nama_instansi dari setiap elemen array
                $nama_instansi_array = array_column($array_data, 'nama_instansi');
                // Mengonversi array menjadi string dengan pemisah koma
                $string_data = implode(', ', $nama_instansi_array);
                
                $values = [
                    'kategori_perubahan' => $kategori_perubahan,
                    'nama_perubahan' => $variable_nama_perubahan,
                    'nama_permintaan_perubahan' => $string_data,
                    'nama_verifikator' => $username[0]['nama'],
                    'nama_kohar' => $nama_kohar,
                    'penjelasan_perubahan' => $penjelasan_perubahan,
                    'periode' => $periode,
                    'tanggal_pengajuan' => date("Y-m-d H:i:s")
                ];
            }else{
                $values = [
                    'kategori_perubahan' => $kategori_perubahan,
                    'nama_perubahan' => $variable_nama_perubahan,
                    'nama_permintaan_perubahan' => $nama_permintaan_perubahan,
                    'nama_verifikator' => $username[0]['nama'],
                    'nama_kohar' => $nama_kohar,
                    'penjelasan_perubahan' => $penjelasan_perubahan,
                    'periode' => $periode,
                    'tanggal_pengajuan' => date("Y-m-d H:i:s")
                ];
            }
            
            $db->execute($query, $values);

            if(count($attachment) > 0){

                $lastInsertedId = $db->lastInsertId();

                            $counter = 1;
                            for($i=0; $i<count($attachment); $i++){
                                $placeholders_data_dukung_perubahan[] = "(:uuid{$counter}, :perubahan_log_id{$counter}, :nama_label{$counter}, :nama_dokumen{$counter}, :created_at{$counter}, :updated_at{$counter})";
            
                                // Insert into t_attachment_kl_verificator
                                $values_attachment[":uuid{$counter}"] = $attachment[$i]["uuid"];
                                $values_attachment[":perubahan_log_id{$counter}"] = $lastInsertedId; 
                                $values_attachment[":nama_label{$counter}"] = $attachment[$i]["label"];
                                $values_attachment[":nama_dokumen{$counter}"] = $attachment[$i]["filename"];
                                $values_attachment[":created_at{$counter}"] = date("Y-m-d H:i:s");
                                $values_attachment[":updated_at{$counter}"] = date("Y-m-d H:i:s"); 
                                // Insert to t_inputan_milestone_verificator  
                                $counter++;
                            }

                            $query_attachment = "INSERT into data_dukung_perubahan (uuid, perubahan_log_id, nama_label, nama_dokumen, created_at, updated_at) VALUES " . implode(", ", $placeholders_data_dukung_perubahan) . "
                            ";

                            $db->execute($query_attachment, $values_attachment);
            }
            
           
            $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 setuju_perubahan(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        $id = $getParams['id'];
        $komentar_kohar = $getParams['komentar_kohar'];
        $persetujuan = $getParams['persetujuan'];
        $db->begin();
        try {
            $query = "UPDATE perubahan_log SET persetujuan_kohar = :persetujuan_kohar, komentar_kohar = :komentar_kohar WHERE id = :id";
            
            if($persetujuan == 'setuju'){
                $values = [
                    'id' => $id,
                    'persetujuan_kohar' => date('Y-m-d H:i:s'),
                    'komentar_kohar' => !empty($komentar_kohar) ? $komentar_kohar : null
                ];
            }else {
                $values = [
                    'id' => $id,
                    'persetujuan_kohar' => null,
                    'komentar_kohar' => !empty($komentar_kohar) ? $komentar_kohar : null
                ];
            }
            

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

            $db->commit();

            return new SimpleResponse("Sukses Menyimpan Data");
    
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

            return new SimpleResponse([
                'Message' => "Data tidak di temukan " . $e->getMessage()
            ]);
        } 
    }
    
    public function setuju_perubahan_ta_monev(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        $id = $getParams['id'];
        $komentar_ta_monev = $getParams['komentar_ta_monev'];
        $persetujuan = $getParams['persetujuan'];
        $db->begin();
        try {
            $query = "UPDATE perubahan_log SET persetujuan_admin_ta = :persetujuan_admin_ta, komentar_ta_monev = :komentar_ta_monev WHERE id = :id";
            
            if($persetujuan == 'setuju'){
                $values = [
                    'id' => $id,
                    'persetujuan_admin_ta' => date('Y-m-d H:i:s'),
                    'komentar_ta_monev' => !empty($komentar_ta_monev) ? $komentar_ta_monev : null
                ];
            }else {
                $values = [
                    'id' => $id,
                    'persetujuan_admin_ta' => null,
                    'komentar_ta_monev' => !empty($komentar_ta_monev) ? $komentar_ta_monev : null
                ];
            }
            

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

            $db->commit();

            return new SimpleResponse("Sukses Menyimpan Data");
    
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

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

    public function read_perubahan(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        $nama_kohar = $getParams['nama_kohar'];
        $db->begin();
        try {
            if($nama_kohar == 'all'){
                $query = "SELECT * FROM perubahan_log ORDER BY id DESC";
                $result = $db->query($query);
            }else{
                $query = "SELECT * FROM perubahan_log WHERE nama_kohar = :nama_kohar ORDER BY id DESC";
                $values = [
                    'nama_kohar' => $nama_kohar
                ];
                
                $result = $db->query($query, $values);
            }
            

            
            // 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()
            ]);
        }
    }

    private function CheckNamaUser($username){
        $dbJaga = $this->getDI()->get('db');
        $dbJaga->begin();
        try {
            $query = "SELECT nama FROM um_user WHERE um_id = :um_id";
            $result = $dbJaga->query($query, [
                'um_id' => $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()
            ]);
        } 
    }

}