<?php
class Sqlgenerator extends MY_Controller
{
    public function __construct()
    {
        
        parent::__construct();
        $this->db_onedev = $this->load->database("onedev", true);
        $this->db_corporate = $this->load->database("cpone_corporate", true);
        $this->db_log = $this->load->database("log", true);
    }

    // private $passphrase = 'my-key-123';
    private $passphrase = null;

    private $MAGIC = "SQDUMP1\0"; 
    private $ALG   = "AES256GCMHKDFv1";
    private $INFO  = "generic_sql_dump/sql";


    public function list_mcu_corporate()
    {
        // Ambil input POST
        $db_name = $this->sys_input['db_name'] ?? '';

        // Validasi
        if ($db_name == '') {
            echo json_encode([
                "status" => false,
                "error" => "db_name is required"
            ]);
            return;
        }

        // Tentukan koneksi database
        switch ($db_name) {
            case 'cpone_corporate':
                $db = $this->db_corporate;
                break;

            case 'onedev':
                $db = $this->db_onedev;
                break;

            case 'log':
                $db = $this->db_log;
                break;

            default:
                echo json_encode([
                    "status" => false,
                    "error" => "Invalid db_name: $db_name"
                ]);
                return;
        }

        // Eksekusi Query
        $query = $db->select('Mcu_NumberID, Mcu_NumberMgm_McuID ,Mcu_NumberCorporateName, Mcu_NumberStartDate, Mcu_NumberEndDate')
                    ->from('mcu_number')
                    ->order_by('Mcu_NumberID', 'DESC')
                    ->get();

        // Cek error DB
        $dbError = $db->error();
        if ($dbError['code'] !== 0) {
            echo json_encode([
                "status" => false,
                "error_code" => $dbError['code'],
                "error_message" => $dbError['message']
            ]);
            return;
        }

        // Response OK
        echo json_encode([
            "status" => true,
            "data" => $query->result()
        ]);
    }

    public function get_count_kelainan()
    {
        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'] ?? null;

        if (!$mgm_mcuid) {
            $this->sys_error("Mgm_McuID required");
            exit;
        }

        // Query untuk mengambil semua kelainan yang mungkin DAN menandai yang sudah dipilih
        $sql = "
            SELECT 
                all_kelainan.Mcu_KelainanID,
                all_kelainan.Mcu_KelainanName,
                all_kelainan.total_patient,
                CASE 
                    WHEN ks.Mcu_KelainanID IS NOT NULL THEN 1 
                    ELSE 0 
                END AS is_selected
            FROM (
                -- Subquery untuk menghitung total pasien per kelainan
                SELECT 
                    kd.Mcu_KelainanID,
                    kd.Mcu_KelainanName,
                    COUNT(DISTINCT oh.T_OrderHeaderM_PatientID) AS total_patient
                FROM cpone_corporate.kelainan_details kd
                JOIN cpone.t_orderheader oh 
                    ON kd.T_OrderHeaderID = oh.T_OrderHeaderID
                WHERE oh.T_OrderHeaderMgm_McuID = ?
                AND oh.T_OrderHeaderIsActive = 'Y'
                GROUP BY kd.Mcu_KelainanID, kd.Mcu_KelainanName
            ) AS all_kelainan
            LEFT JOIN cpone_corporate.kelainan_summary ks 
                ON all_kelainan.Mcu_KelainanID = ks.Mcu_KelainanID 
                AND ks.Mcu_KelainanMgm_McuID = ?
            ORDER BY all_kelainan.total_patient DESC
        ";
        
        // Parameter untuk query (digunakan dua kali)
        $params = [$mgm_mcuid, $mgm_mcuid];
        $query = $this->db->query($sql, $params);

        if (!$query) {
            $this->sys_error_db("get count kelainan", $this->db);
            exit;
        }

        $result = $query->result_array();

        $this->sys_ok([
            "mgm_mcuid" => $mgm_mcuid,
            "data" => $result
        ]);
        exit;
    }

    function save_kelainan_selection()
    {
        $prm = $this->sys_input;

        $mgm_mcu_id = isset($prm['mgm_mcu_id']) ? intval($prm['mgm_mcu_id']) : 0;
        $selected_kelainan = isset($prm['selected_kelainan']) ? $prm['selected_kelainan'] : [];

        if ($mgm_mcu_id <= 0) {
            $this->sys_error("mgm_mcu_id tidak valid");
            exit;
        }

        // Validasi frontend sudah cukup ketat, namun validasi backend tetap penting sebagai pengaman.
        // Logika validasi di sini memastikan jumlah yang dipilih tidak kurang dari yang seharusnya.
        $sql_total = "
            SELECT COUNT(DISTINCT kd.Mcu_KelainanID) AS total_available
            FROM cpone_corporate.kelainan_details kd
            JOIN cpone.t_orderheader oh ON kd.T_OrderHeaderID = oh.T_OrderHeaderID
            WHERE oh.T_OrderHeaderMgm_McuID = ? AND oh.T_OrderHeaderIsActive = 'Y'
        ";
        $query_total = $this->db->query($sql_total, [$mgm_mcu_id]);
        $total_available = $query_total->row()->total_available;

        $total_selected = count($selected_kelainan);

        if ($total_available >= 10) {
            if ($total_selected != 10) {
                $this->sys_error("Harap pilih tepat 10 kelainan.");
                exit;
            }
        } else {
            if ($total_selected != $total_available) {
                $this->sys_error("Harap pilih semua {$total_available} kelainan yang tersedia.");
                exit;
            }
        }



        // Wajib gunakan DB corporate karena tabel kelainan_summary ada di cpone_corporate
        $db = $this->db_corporate;

        // Mulai transaksi
        $db->trans_begin();

        // Hapus data sebelumnya untuk MCU ini
        $sql_delete = "DELETE FROM kelainan_summary WHERE Mcu_KelainanMgm_McuID = ?";
        $db->query($sql_delete, [$mgm_mcu_id]);

        // Query insert
        $sql_insert = "
            INSERT INTO kelainan_summary 
            (Mcu_KelainanID, Mcu_KelainanMgm_McuID, Mcu_KelainanName)
            VALUES (?, ?, ?)
        ";

        foreach ($selected_kelainan as $item) {

            if (!isset($item["id"]) || !isset($item["name"])) {
                $db->trans_rollback();
                $this->sys_error("Format data kelainan tidak valid");
                exit;
            }

            $db->query($sql_insert, [
                $item["id"],
                $mgm_mcu_id,
                $item["name"]
            ]);
        }

        if ($db->trans_status() === FALSE) {
            $err = $db->error();
            $db->trans_rollback();
            $this->sys_error("Gagal menyimpan data. DB Error: " . $err['message']);
            exit;
        }

        $db->trans_commit();

        $this->sys_ok([
            "message" => "Kelainan berhasil disimpan",
            "total_saved" => count($selected_kelainan),
            "saved_data" => $selected_kelainan
        ]);
    }

    public function get_kelainan_selected()
    {
        $prm = $this->sys_input;

        $mgm_mcu_id = isset($prm['mgm_mcu_id']) ? intval($prm['mgm_mcu_id']) : 0;

        if ($mgm_mcu_id <= 0) {
            $this->sys_error("mgm_mcu_id tidak valid");
            return;
        }

        // Ambil kelainan yang sudah dipilih user sebelumnya
        $sql = "
            SELECT 
                Mcu_KelainanID AS id,
                Mcu_KelainanName AS name
            FROM kelainan_summary
            WHERE Mcu_KelainanMgm_McuID = ?
            ORDER BY Mcu_KelainanID
        ";

        $query = $this->db_corporate->query($sql, [$mgm_mcu_id]);

        if (!$query) {
            $this->sys_error_db("get kelainan selected", $this->db_corporate);
            return;
        }

        $this->sys_ok([
            "mgm_mcu_id" => $mgm_mcu_id,
            "selected" => $query->result_array()
        ]);
    }


    private function generate_sql_dump($db_name)
    {
        // Pilih koneksi
        switch ($db_name) {
            case 'onedev': $db = $this->db_onedev; break;
            case 'cpone_corporate': $db = $this->db_corporate; break;
            case 'log': $db = $this->db_log; break;
            default:
                throw new Exception("Invalid db_name: {$db_name}");
        }

        $sql_dump = "";
        $tables = $db->query("SHOW TABLES")->result_array();
        $table_key = "Tables_in_" . $db_name;

        foreach ($tables as $tbl) {
            $table_name = $tbl[$table_key];

            // Struktur tabel
            $create_row = $db->query("SHOW CREATE TABLE `{$table_name}`")->row_array();
            $sql_dump .= $create_row['Create Table'] . ";\n\n";

            // Data tabel
            $rows = $db->query("SELECT * FROM `{$table_name}`")->result_array();
            foreach ($rows as $row) {
                $columns = array_keys($row);
                $values = array_map(function ($val) use ($db) {
                    return is_null($val) ? "NULL" : "'" . $db->escape_str($val) . "'";
                }, array_values($row));

                $sql_dump .= "INSERT INTO `{$table_name}` (`" . implode('`,`', $columns) .
                            "`) VALUES(" . implode(",", $values) . ");\n";
            }

            $sql_dump .= "\n";
        }

        return $sql_dump . "COMMIT;\n";
    }

    public function encrypt_and_download(string $sql, string $filename = "dump.dat")
    {
        // --- generate material ---
        $salt  = random_bytes(16);
        $nonce = random_bytes(12);
        $key   = hash_hkdf('sha256', $this->passphrase, 32, $this->INFO, $salt);

        $tag = null;
        $ciphertext = openssl_encrypt(
            $sql,
            'aes-256-gcm',
            $key,
            OPENSSL_RAW_DATA,
            $nonce,
            $tag
        );

        if ($ciphertext === false) {
            show_error('Encryption failed', 500);
            return;
        }

        // --- format blob seperti modul Dl_sqlite ---
        $blob =
            $this->MAGIC .
            chr(strlen($this->ALG)) . $this->ALG .
            chr(strlen($salt))      . $salt .
            chr(strlen($nonce))     . $nonce .
            chr(strlen($tag))       . $tag .
            $ciphertext;

        // --- stream download .dat ---
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Content-Length: ' . strlen($blob));
        header('Cache-Control: no-cache, no-store, must-revalidate');
        header('Pragma: no-cache');
        header('Expires: 0');

        echo $blob;
        exit;
    }

    public function download()
    {
        // --- Ambil parameter dari GET ---
        $db_name = $this->input->get('db_name');
        $mcu_id  = intval($this->input->get('Mgm_McuID'));

        if (!$db_name) {
            show_error("db_name is required", 400);
            return;
        }

        if ($mcu_id <= 0) {
            show_error("Mgm_McuID is required", 400);
            return;
        }

        // --- Ambil passphrase dari DB corporate ---
        try {
            $this->passphrase = $this->get_passphrase_from_mcu($mcu_id);
        } catch (Exception $e) {
            show_error($e->getMessage(), 500);
            return;
        }

        // --- Generate SQL dump ---
        $sql = $this->generate_sql_dump($db_name);

        // --- Encrypt dan kirim file download ---
        $this->encrypt_and_download(
            $sql,
            "dump_{$db_name}_{$mcu_id}.dat"
        );
    }

    function show_key(): void
    {
        $this->sys_ok([
            "passphrase" => $this->passphrase,
            "info" => $this->INFO,
            "algorithm" => $this->ALG,
            "note" => "The final key is derived using HKDF-SHA256 with the passphrase, info, and a random salt generated for each download."
        ]);
    }

    function truncate_corporate_tables()
    {
        // Untuk keamanan, endpoint ini memerlukan parameter konfirmasi.
        $prm = $this->sys_input;
        if (!isset($prm['confirm_truncate']) || $prm['confirm_truncate'] !== true) {
            $this->sys_error("Operasi TRUNCATE memerlukan konfirmasi. Kirim { \"confirm_truncate\": true } untuk melanjutkan.");
            return;
        }

        $tables_to_truncate = [
            'kelainan_details',
            'kelainan_summary',
            'mcu_result_all',
            'mcu_number'
        ];

        $truncated_tables = [];
        $errors = [];

        foreach ($tables_to_truncate as $table) {
            if ($this->db_corporate->table_exists($table)) {
                if ($this->db_corporate->truncate($table)) {
                    $truncated_tables[] = $table;
                } else {
                    $errors[] = "Gagal melakukan TRUNCATE pada tabel: {$table}";
                }
            }
        }

        if (!empty($errors)) {
            $this->sys_error("Terjadi kesalahan saat TRUNCATE tabel.", $errors);
            return;
       }

        $this->sys_ok([
            "message" => "Semua tabel yang relevan berhasil di-TRUNCATE.",
            "truncated_tables" => $truncated_tables
        ]);
    }

    private function generateRandomCode($length = 6)
    {
        $chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
        $code = '';

        for ($i = 0; $i < $length; $i++) {
            $code .= $chars[random_int(0, strlen($chars) - 1)];
        }

        return $code;
    }

    public function create_encrypt_key()
    {
        // --- Ambil input JSON ---
        $prm = json_decode($this->input->raw_input_stream, true);
        $mcu_id = intval($prm['Mgm_McuID'] ?? 0);

        if ($mcu_id <= 0) {
            $this->sys_error("Mgm_McuID wajib diisi");
            return;
        }

        // Jika tidak ada session user, gunakan default 0
        $user_id = 0;

        // --- 1. Nonaktifkan key lama ---
        $this->db
            ->where('Mgm_McuEncryptMgm_McuID', $mcu_id)
            ->update('mgm_mcuencrypt', [
                'Mgm_McuEncryptIsActive' => 'N'
            ]);

        // Reset builder biar query berikutnya bersih
        $this->db->reset_query();

        // --- 2. Generate kode unik ---
        do {
            $code = $this->generateRandomCode(6);

            $query = $this->db
                ->select('Mgm_McuEncryptID')
                ->where('Mgm_McuEncryptCode', $code)
                ->get('mgm_mcuencrypt');

            if (!$query) {
                $err = $this->db->error();
                $this->sys_error("DB Error: " . $err['message']);
                return;
            }

            $exists = $query->num_rows();
        } while ($exists > 0);

        // --- 3. Insert key baru ---
        $insert = [
            'Mgm_McuEncryptMgm_McuID' => $mcu_id,
            'Mgm_McuEncryptCode'      => $code,
            'Mgm_McuEncryptIsActive'  => 'Y',
            'Mgm_McuEncryptCreated'   => date('Y-m-d H:i:s'),
            'Mgm_McuEncryptUserID'    => $user_id // FIX: tidak pakai session
        ];

        $this->db->insert('mgm_mcuencrypt', $insert);

        if ($this->db->affected_rows() <= 0) {
            $err = $this->db->error();
            $this->sys_error("Gagal insert key baru: " . $err['message']);
            return;
        }

        // --- 4. Return response ---
        $this->sys_ok([
            "message" => "Key baru berhasil dibuat",
            "Mgm_McuID" => $mcu_id,
            "new_key" => $code
        ]);
    }

    public function get_active_encrypt_key()
    {
        // --- Ambil input JSON ---
        $prm = json_decode($this->input->raw_input_stream, true);
        $mcu_id = intval($prm['Mgm_McuID'] ?? 0);

        if ($mcu_id <= 0) {
            $this->sys_error("Mgm_McuID wajib diisi");
            return;
        }

        // --- Query untuk mendapatkan key aktif ---
        $query = $this->db
            ->select('Mgm_McuEncryptCode')
            ->from('mgm_mcuencrypt')
            ->where('Mgm_McuEncryptMgm_McuID', $mcu_id)
            ->where('Mgm_McuEncryptIsActive', 'Y')
            ->order_by('Mgm_McuEncryptID', 'DESC')
            ->limit(1)
            ->get();

        if (!$query) {
            $err = $this->db->error();
            $this->sys_error("DB Error: " . $err['message']);
            return;
        }

        $result = $query->row_array();

        if (!$result) {
            $this->sys_error("Tidak ada key enkripsi aktif yang ditemukan untuk MCU ID: " . $mcu_id);
            return;
        }

        // --- Return response ---
        $this->sys_ok([
            "message" => "Key aktif berhasil ditemukan",
            "Mgm_McuID" => $mcu_id,
            "active_key" => $result['Mgm_McuEncryptCode']
        ]);
    }


    private function get_passphrase_from_mcu($mcu_id)
    {
        $query = $this->db
            ->select('Mgm_McuEncryptCode')
            ->from('mgm_mcuencrypt')
            ->where('Mgm_McuEncryptMgm_McuID', $mcu_id)
            ->where('Mgm_McuEncryptIsActive', 'Y')
            ->order_by('Mgm_McuEncryptID', 'DESC')
            ->limit(1)
            ->get();

        if (!$query) {
            $err = $this->db->error();
            throw new Exception("DB error: " . $err['message']);
        }

        $row = $query->row_array();
        if (!$row) {
            throw new Exception("Active encryption key not found for Mgm_McuID: {$mcu_id}");
        }

        return $row['Mgm_McuEncryptCode'];
    }



}