<?php

class StranaspkLogframeTarget2025Controller extends BaseController
{
     public function get_all_logframe_target(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $db->begin();
        try {
            $query = "SELECT tkt.id, tkt.m_kl_output_id, tkt.urutan_target, tkt.nama_target, tkt.urutan_fokus, tkt.nama_fokus, mko.nama_output, tkt.indikator_penilaian_target, nilai_b03, nilai_b06, nilai_b09, nilai_b12, nilai_b15, nilai_b18, nilai_b21, nilai_b24 FROM t_kl_target AS tkt LEFT JOIN m_kl_output AS mko ON mko.id = tkt.m_kl_output_id ORDER BY tkt.id";
            $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()
            ]);
        }
     }

     private function now() {
        $dt = new DateTime();
        $dt->setTimezone(new DateTimeZone('Asia/Jakarta'));
        $dt->setTimestamp(round(microtime(true)));
        return $dt->format('Y-m-d H:i:s');
    }

     public function submit_logframe_target(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        
        $getParams = $this->request->getJsonRawBody(true);
        $m_kl_output_id = $getParams["m_kl_output_id"];
        $urutan_target = $getParams["urutan_target"];
        $nama_target = $getParams["nama_target"];
        $urutan_fokus = $getParams["urutan_fokus"];
        $nama_fokus = $getParams["nama_fokus"];
        $indikator_penilaian_target = $getParams["indikator_penilaian_target"];
        $username = $getParams["username"];
        $db->begin();

        try{

            $this->setchange_by();

            $query = "INSERT INTO t_kl_target (m_kl_output_id, urutan_target, nama_target, urutan_fokus, nama_fokus, indikator_penilaian_target, created_at, updated_at) VALUES(:m_kl_output_id, :urutan_target, :nama_target, :urutan_fokus, :nama_fokus, :indikator_penilaian_target, :created_at, :updated_at)";
 
            $values = [
             'm_kl_output_id' => $m_kl_output_id,
             'urutan_target' => intval($urutan_target),
             'nama_target' => $nama_target,
             'urutan_fokus' => intval($urutan_fokus),
             'nama_fokus' => $nama_fokus,
             'indikator_penilaian_target' => $indikator_penilaian_target,
             'created_at' => $this->now(),
             'updated_at' => $this->now()
            ];

           $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_nilai_target(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        
        $getParams = $this->request->getJsonRawBody(true);
        $id = $getParams["id"];
        $username = $getParams["username"];
        $nilai_b03 = $getParams['nilai_b03'];
        $nilai_b06 = $getParams['nilai_b06'];
        $nilai_b09 = $getParams['nilai_b09'];
        $nilai_b12 = $getParams['nilai_b12'];
        $nilai_b15 = $getParams['nilai_b15'];
        $nilai_b18 = $getParams['nilai_b18'];
        $nilai_b21 = $getParams['nilai_b21'];
        $nilai_b24 = $getParams['nilai_b24'];
        $db->begin();

        try{

            $this->setchange_by();

            $query = "UPDATE t_kl_target SET nilai_b03 = :nilai_b03, nilai_b06 = :nilai_b06, nilai_b09 = :nilai_b09, nilai_b12 = :nilai_b12, nilai_b15 = :nilai_b15, nilai_b18 = :nilai_b18, nilai_b21 = :nilai_b21, nilai_b24 = :nilai_b24, updated_at = :updated_at WHERE id = :id";
 
            $values = [
                'id' => $id,
                'updated_at' => $this->now(),
                'nilai_b03' => $nilai_b03,
                'nilai_b06' => $nilai_b06,
                'nilai_b09' => $nilai_b09,
                'nilai_b12' => $nilai_b12,
                'nilai_b15' => $nilai_b15,
                'nilai_b18' => $nilai_b18,
                'nilai_b21' => $nilai_b21,
                'nilai_b24' => $nilai_b24,
               ];

           $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 edit_logframe_target(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        
        $getParams = $this->request->getJsonRawBody(true);
        $m_kl_output_id = $getParams["m_kl_output_id"];
        $urutan_target = $getParams["urutan_target"];
        $nama_target = $getParams["nama_target"];
        $urutan_fokus = $getParams["urutan_fokus"];
        $nama_fokus = $getParams["nama_fokus"];
        $indikator_penilaian_target = $getParams["indikator_penilaian_target"];
        $id = $getParams["id"];
        $username = $getParams["username"];
        $db->begin();

        try{

            $this->setchange_by();

            $query = "UPDATE t_kl_target SET m_kl_output_id = :m_kl_output_id, urutan_target = :urutan_target, nama_target = :nama_target, urutan_fokus = :urutan_fokus, nama_fokus = :nama_fokus, indikator_penilaian_target = :indikator_penilaian_target, updated_at = :updated_at WHERE id = :id";
 
            $values = [
                'id' => $id,
                'm_kl_output_id' => $m_kl_output_id,
                'urutan_target' => intval($urutan_target),
                'nama_target' => $nama_target,
                'urutan_fokus' => intval($urutan_fokus),
                'nama_fokus' => $nama_fokus,
                'indikator_penilaian_target' => $indikator_penilaian_target,
                'updated_at' => $this->now()
               ];

           $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_target(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        $id = $getParams["id"];
        $username = $getParams["username"];
        $db->begin();

        try{

            $this->setchange_by();

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

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

            $db->commit();

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

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

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

        try{
              $query = "SELECT tkt.id, tkt.m_kl_output_id, tkt.urutan_target, tkt.nama_target, tkt.urutan_fokus, tkt.nama_fokus, mko.nama_output, tkt.indikator_penilaian_target, nilai_b03, nilai_b06, nilai_b09, nilai_b12, nilai_b15, nilai_b18, nilai_b21, nilai_b24 FROM t_kl_target AS tkt LEFT JOIN m_kl_output AS mko ON mko.id = tkt.m_kl_output_id WHERE nama_target 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()
            ]);
        }
     }

     public function read_target_by_output_id(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        $db->begin();
        $m_kl_output_id = $getParams['m_kl_output_id'];
        try {
            $query = "SELECT tkt.id, tkt.m_kl_output_id, tkt.urutan_target, tkt.nama_target, tkt.urutan_fokus, tkt.nama_fokus, mko.nama_output, tkt.indikator_penilaian_target FROM t_kl_target AS tkt LEFT JOIN m_kl_output AS mko ON mko.id = tkt.m_kl_output_id WHERE mko.id = :m_kl_output_id";

            $values = [
                'm_kl_output_id' => $m_kl_output_id
            ];

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

     public function get_nilai_target(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        $db->begin();
        $target_id = $getParams['target_id'];
        try {
            $query = "SELECT * FROM t_kl_target WHERE id = :id";

            $values = [
                'id' => $target_id
            ];

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

     public function get_target_by_output_id_perubahan(){
        $db = $this->getDI()->get('databaseNewStranas2025');
        $getParams = $this->request->getJsonRawBody(true);
        $output_id = $getParams["output_id"];
        $db->begin();
        try {
            $query = "SELECT * from t_kl_target where m_kl_output_id = :output_id ORDER BY urutan_target";
            $values = [
                'output_id' => $output_id
            ];
            $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()
            ]);
        } 
    }

     private function setchange_by(){
        $db = $this->getDI()->get('databaseNewStranas2025');
        $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);
    }
}