<?php

class StranaspkLogframeAksi2024Controller extends BaseController
{
     public function get_all_logframe_aksi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        $db->begin();
        try {
            $query = "SELECT * FROM m_kl_aksi ORDER BY urutan_aksi ASC";
            $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 submit_logframe_aksi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        
        $getParams = $this->request->getJsonRawBody(true);
        $nama_aksi = $getParams["nama_aksi"];
        $nomor_urut_aksi = $getParams["nomor_urut_aksi"];
        $username = $getParams["username"];
        $db->begin();

        try{

            $this->setchange_by();

            $query_select = "SELECT MAX(id) AS last_id FROM m_kl_aksi";
            $result = $db->query($query_select);

            // Ambil hasil query
            $row = $result->fetch();
            $last_id = $row["last_id"];

            $query = "INSERT INTO m_kl_aksi (id, nama_aksi, urutan_aksi, created_at, updated_at) VALUES(:id, :nama_aksi, :urutan_aksi, :created_at, :updated_at)";
 
            $values = [
             'id' => $last_id+1,
             'nama_aksi' => $nama_aksi,
             'urutan_aksi' => $nomor_urut_aksi,
             '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_aksi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        
        $getParams = $this->request->getJsonRawBody(true);
        $nama_aksi = $getParams["nama_aksi"];
        $nomor_urut_aksi = $getParams["nomor_urut_aksi"];
        $id = $getParams["id"];
        $username = $getParams["username"];
        $db->begin();

        try{

            $this->setchange_by();

            $query = "UPDATE m_kl_aksi SET nama_aksi = :nama_aksi, urutan_aksi = :urutan_aksi, updated_at = :updated_at WHERE id = :id";
 
            $values = [
             'id' => $id,
             'nama_aksi' => $nama_aksi,
             'urutan_aksi' => $nomor_urut_aksi,
             '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_aksi(){
        $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_kl_aksi WHERE id = :id";
 
            $values = [
             'id' => $id
            ];

           $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 search_logframe_aksi(){
        $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_kl_aksi WHERE nama_aksi 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 search_logframe_aksi_by_instansi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        $getParams = $this->request->getJsonRawBody(true);
        // Eksekusi query dengan parameter binding
        $nama_instansi = $getParams['nama_instansi'];
        $db->begin();

        try{
             $query = "SELECT DISTINCT
             mka.nama_aksi,
             mka.urutan_aksi
             from m_kl_aksi as mka 
             left join m_kl_output as mko 
             on mka.id = mko.m_kl_aksi_id
             left join m_kl_milestone as mkm 
             on mko.id = mkm.m_kl_output_id
             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 mi.nama_instansi = :nama_instansi
             ORDER BY mka.urutan_aksi ASC";
             $values = [
                'nama_instansi' => $nama_instansi
             ];  
            $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_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('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);
    }

    public function read_all_audit_aksi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        $db->begin();
        try {
            $query = "SELECT * FROM audit_log ORDER BY id DESC";
            $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){
            $db->rollback();
            return new SimpleResponse([
                'Message' => "Gagal Menampilkan Data" . $e->getMessage()
            ]);
        }  
    }

    public function read_audit_aksi(){
        $this->checkIsAuthenticated();
        $db = $this->getDI()->get('databaseNewStranas2024');
        $getParams = $this->request->getJsonRawBody(true);
        $record_id = $getParams["record_id"];
        $table_name = $getParams["table_name"];
        $db->begin();
        try {
            
            $query = "SELECT * FROM audit_log WHERE record_id = :record_id AND table_name = :table_name ORDER BY id DESC";
            $result = $db->query($query, [
                'record_id' => $record_id,
                'table_name' => $table_name
            ]);
            // 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){
            $db->rollback();
            return new SimpleResponse([
                'Message' => "Gagal Menampilkan Data" . $e->getMessage()
            ]);
        }  
    }
}