<?php

class StranaspkLogframeSummary2024Controller extends BaseController
{
    public function read_all_data(){
        $this->checkIsAuthenticated();
        // $getParams = $this->request->getJsonRawBody(true);
        $db = $this->getDI()->get('databaseNewStranas2024');
        $db->begin();
        try {
            $query = "SELECT
            mi.urutan_instansi, 
            mi.nama_instansi, 
            mkm.nama_milestone,
            mkm.id AS milestone_id,
            mkm.urutan_milestone, 
            mka.nama_aksi,
            mka.id AS aksi_id,
            mka.urutan_aksi,
            mko.nama_output,
            mko.id AS output_id,
            mko.urutan_output,
            timv.score_b03,
            timv.score_b06,
            timv.score_b09,
            timv.score_b12,
            timv.score_b15,
            timv.score_b18,
            timv.score_b21,
            timv.score_b24,
            timv.keterangan_verificator_b03,
            timv.keterangan_verificator_b06,
            timv.keterangan_verificator_b09,
            timv.keterangan_verificator_b12,
            timv.keterangan_verificator_b15,
            timv.keterangan_verificator_b18,
            timv.keterangan_verificator_b21,
            timv.keterangan_verificator_b24,
            tim.periode_milestone,
            STRING_AGG(CASE 
                   WHEN tim.keterangan_pelapor IS NOT NULL OR timv.score_b03 = 100 THEN 'true'
                   WHEN tim.keterangan_pelapor IS NOT NULL OR timv.score_b06 = 100 THEN 'true'
                   WHEN tim.keterangan_pelapor IS NOT NULL OR timv.score_b09 = 100 THEN 'true'
                   WHEN tim.keterangan_pelapor IS NOT NULL OR timv.score_b12 = 100 THEN 'true'
                   WHEN tim.keterangan_pelapor IS NOT NULL OR timv.score_b15 = 100 THEN 'true'
                   WHEN tim.keterangan_pelapor IS NOT NULL OR timv.score_b18 = 100 THEN 'true'
                   WHEN tim.keterangan_pelapor IS NOT NULL OR timv.score_b21 = 100 THEN 'true'
                   WHEN tim.keterangan_pelapor IS NOT NULL OR timv.score_b24 = 100 THEN 'true'
                  ELSE 'false'
               END, '(+) ') AS keterangan_pelapor,  
            CASE 
                   WHEN timv.score_b03 = 100 THEN 'true'
                   WHEN timv.score_b06 = 100 THEN 'true'
                   WHEN timv.score_b09 = 100 THEN 'true'
                   WHEN timv.score_b12 = 100 THEN 'true'
                   WHEN timv.score_b15 = 100 THEN 'true'
                   WHEN timv.score_b18 = 100 THEN 'true'
                   WHEN timv.score_b21 = 100 THEN 'true'
                   WHEN timv.score_b24 = 100 THEN 'true'
                  ELSE 'false'
            END AS keterangan_verificator_validation
        FROM m_instansi AS mi 
        LEFT JOIN t_instansi_milestone AS tim ON tim.m_instansi_id = mi.id
        LEFT JOIN m_kl_milestone AS mkm ON tim.m_kl_milestone_id = mkm.id
        LEFT JOIN m_kl_output AS mko ON mkm.m_kl_output_id = mko.id
        LEFT JOIN m_kl_aksi AS mka ON mko.m_kl_aksi_id = mka.id
        LEFT JOIN t_inputan_milestone_verificator AS timv ON timv.m_kl_milestone_id = mkm.id
        GROUP BY
            mi.urutan_instansi,
            mi.nama_instansi, 
            mkm.nama_milestone,
            mkm.id,
            mkm.urutan_milestone, 
            mka.nama_aksi,
            mka.id,
            mka.urutan_aksi,
            mko.nama_output,
            mko.id,
            mko.urutan_output,
            timv.score_b03,
            timv.score_b06,
            timv.score_b09,
            timv.score_b12,
            timv.score_b15,
            timv.score_b18,
            timv.score_b21,
            timv.score_b24,
            timv.keterangan_verificator_b03,
            timv.keterangan_verificator_b06,
            timv.keterangan_verificator_b09,
            timv.keterangan_verificator_b12,
            timv.keterangan_verificator_b15,
            timv.keterangan_verificator_b18,
            timv.keterangan_verificator_b21,
            timv.keterangan_verificator_b24,
            tim.periode_milestone,
            mi.urutan_instansi
        ORDER BY 
            mi.urutan_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 set_all_100(){
        $this->checkIsAuthenticated();
        // $getParams = $this->request->getJsonRawBody(true);
        $db = $this->getDI()->get('databaseNewStranas2024');
        $db->begin();
        try {
            $query = "UPDATE t_inputan_milestone_verificator
            SET
                score_b06 = CASE WHEN score_b03 = 100 THEN 100 ELSE score_b06 END,
                score_b09 = CASE WHEN score_b03 = 100 OR score_b06 = 100 THEN 100 ELSE score_b09 END,
                score_b12 = CASE WHEN score_b03 = 100 OR score_b06 = 100 OR score_b09 = 100 THEN 100 ELSE score_b12 END,
                score_b15 = CASE WHEN score_b03 = 100 OR score_b06 = 100 OR score_b09 = 100 OR score_b12 = 100 THEN 100 ELSE score_b15 END,
                score_b18 = CASE WHEN score_b03 = 100 OR score_b06 = 100 OR score_b09 = 100 OR score_b12 = 100 OR score_b15 = 100 THEN 100 ELSE score_b18 END,
                score_b21 = CASE WHEN score_b03 = 100 OR score_b06 = 100 OR score_b09 = 100 OR score_b12 = 100 OR score_b15 = 100 OR score_b18 = 100 THEN 100 ELSE score_b21 END,
                score_b24 = CASE WHEN score_b03 = 100 OR score_b06 = 100 OR score_b09 = 100 OR score_b12 = 100 OR score_b15 = 100 OR score_b18 = 100 OR score_b21 = 100 THEN 100 ELSE score_b24 END
            WHERE
                score_b03 = 100
                OR score_b06 = 100
                OR score_b09 = 100
                OR score_b12 = 100
                OR score_b15 = 100
                OR score_b18 = 100
                OR score_b21 = 100
                OR score_b24 = 100";
            
            $query_target = "UPDATE t_kl_target
            SET
                nilai_b06 = CASE WHEN nilai_b03 = 100 THEN 100 ELSE nilai_b06 END,
                nilai_b09 = CASE WHEN nilai_b03 = 100 OR nilai_b06 = 100 THEN 100 ELSE nilai_b09 END,
                nilai_b12 = CASE WHEN nilai_b03 = 100 OR nilai_b06 = 100 OR nilai_b09 = 100 THEN 100 ELSE nilai_b12 END,
                nilai_b15 = CASE WHEN nilai_b03 = 100 OR nilai_b06 = 100 OR nilai_b09 = 100 OR nilai_b12 = 100 THEN 100 ELSE nilai_b15 END,
                nilai_b18 = CASE WHEN nilai_b03 = 100 OR nilai_b06 = 100 OR nilai_b09 = 100 OR nilai_b12 = 100 OR nilai_b15 = 100 THEN 100 ELSE nilai_b18 END,
                nilai_b21 = CASE WHEN nilai_b03 = 100 OR nilai_b06 = 100 OR nilai_b09 = 100 OR nilai_b12 = 100 OR nilai_b15 = 100 OR nilai_b18 = 100 THEN 100 ELSE nilai_b21 END,
                nilai_b24 = CASE WHEN nilai_b03 = 100 OR nilai_b06 = 100 OR nilai_b09 = 100 OR nilai_b12 = 100 OR nilai_b15 = 100 OR nilai_b18 = 100 OR nilai_b21 = 100 THEN 100 ELSE nilai_b24 END
            WHERE
                nilai_b03 = 100
                OR nilai_b06 = 100
                OR nilai_b09 = 100
                OR nilai_b12 = 100
                OR nilai_b15 = 100
                OR nilai_b18 = 100
                OR nilai_b21 = 100
                OR nilai_b24 = 100
            ";
            
            $db->execute($query_target, null);

            $db->execute($query, null);
            
            $db->commit();

            return new SimpleResponse([
                'message' => "Sukses Mengupdate Database"
            ]);
        }catch (Exception $e) {
            // Rollback transaksi jika terjadi kesalahan
            $db->rollback();

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