<?php
class Sqlgenerator extends MY_Controller
{
    public function __construct()
    {
        // Memanggil kontruktor parent dari MY_Controller 
        parent::__construct();
        // Memuat koneksi database bernama onedev
        $this->db = $this->load->database("onedev", true);
        // Memuat koneksi database bernama cpone_corporate
        $this->db_corporate = $this->load->database("cpone_corporate", true);
        // Memuat koneksi database bernama log
        $this->db_log = $this->load->database("log", true);
    }

        /**
     * STEP: Generate data list Corporate 
     * ---------------------------------------------
     * Endpoint: POST /sqlgenerator/dump_database
     * Request : { "db_name": "cpone_corporate" }
     * Response: { "message": "...", "sql": "CREATE TABLE ...; INSERT INTO ...;" }
     * 
     * Fungsi:
     * - Generate SQL untuk semua tabel di database MySQL
     * - Termasuk struktur tabel (CREATE) dan isi tabel (INSERT)
     * - Bisa digunakan untuk backup atau migrasi database
     */
    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()
        ]);
    }


    


function list_pasien_mcu()
{
    // Query untuk mengambil data pasien dari kelainan_details
    $sql = "
        SELECT 
            Mcu_KelainanID,
            M_PatientID,
            M_PatientName
        FROM kelainan_details
        WHERE Mcu_KelainanID IS NOT NULL
        ORDER BY 
            Mcu_KelainanID ASC,
            M_PatientID ASC
    ";

    // Eksekusi query
    $query = $this->db_corporate->query($sql);

    // Error handling
    if (!$query) {
        $this->sys_error_db("Failed to get patient list", $this->db_corporate);
        exit;
    }

    // Ambil hasil
    $result = $query->result_array();

    // Response sukses
    $this->sys_ok($result);
    exit;
}

function generate_count_kelainan(): never
{
        $sql = "
            SELECT 
                Mcu_KelainanID,
                Mcu_KelainanName,
            COUNT(T_OrderHeaderID) 
            FROM kelainan_details
            GROUP BY Mcu_KelainanID
        ";
        $query = $this->db_corporate->query($sql);
        if (!$query) {
            $this->sys_error_db("Failed to generate count kelainan", $this->db_corporate);
            exit;
        }
        $result = $query->result_array();
        $this->sys_ok($result);
        exit;
        
}


function total_pasien_mcu()
{
    // Query hitung total pasien unik berdasarkan T_OrderHeaderID
    $sql = "
        SELECT COUNT(DISTINCT T_OrderHeaderID) AS total_pasien
        FROM kelainan_details
        WHERE Mcu_KelainanID IS NOT NULL
    ";

    $query = $this->db_corporate->query($sql);

    if (!$query) {
        $this->sys_error_db("Failed to count total patients", $this->db_corporate);
        exit;
    }

    $row = $query->row_array();

    // Response success
    $this->sys_ok([
        "total_pasien" => intval($row['total_pasien'])
    ]);
    exit;
}

public function select_sepuluh_pasien()
{
    // Ambil input JSON
    $prm = $this->sys_input;

    if (!isset($prm['pasien_id']) || empty($prm['pasien_id'])) {
        $this->sys_error("Parameter pasien_id wajib diisi.");
        exit;
    }

    // Pisahkan string menjadi array
    $pasien_ids = array_map('trim', explode(',', $prm['pasien_id']));

    // Pastikan minimal 10 pasien
    if (count($pasien_ids) < 10) {
        $this->sys_error("Data pasien tidak cukup, minimal 10 pasien dibutuhkan.");
        exit;
    }

    // Ambil data pasien sesuai ID
    $sql = "
        SELECT 
            Mcu_KelainanID,
            M_PatientID,
            M_PatientName
        FROM kelainan_details
        WHERE M_PatientID IN (" . implode(',', array_map('intval', $pasien_ids)) . ")
        ORDER BY M_PatientID ASC
    ";

    $query = $this->db_corporate->query($sql);

    if (!$query) {
        $this->sys_error_db("Failed to select patients", $this->db_corporate);
        exit;
    }

    $result = $query->result_array();

    // Response sukses
    $this->sys_ok($result);
}

    /**
     * STEP: Dump seluruh database (struktur & data)
     * ---------------------------------------------
     * Endpoint: POST /sqlgenerator/dump_database
     * Request : { "db_name": "cpone_corporate" }
     * Response: { "message": "...", "sql": "CREATE TABLE ...; INSERT INTO ...;" }
     * 
     * Fungsi:
     * - Generate SQL untuk semua tabel di database MySQL
     * - Termasuk struktur tabel (CREATE) dan isi tabel (INSERT)
     * - Bisa digunakan untuk backup atau migrasi database
     */
    function dump_database_sql()
    {
        $prm = $this->sys_input;
        $db_name = isset($prm['db_name']) ? $prm['db_name'] : '';

        if ($db_name == '') {
            $this->sys_error("db_name is required");
            exit;
        }

        // Pilih koneksi database sesuai db_name
        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:
                $this->sys_error("Invalid db_name: {$db_name}");
                exit;
        }

        try {
            $sql_dump = "";

            // 1. Ambil semua tabel
            $tables_query = $db->query("SHOW TABLES");
            $tables = $tables_query->result_array();
            $table_key = "Tables_in_" . $db_name;

            foreach ($tables as $tbl) {
                $table_name = $tbl[$table_key];

                // 2. Ambil struktur tabel
                $create_query = $db->query("SHOW CREATE TABLE `{$table_name}`");
                $create_row = $create_query->row_array();
                $sql_dump .= $create_row['Create Table'] . ";\n\n";

                // 3. Ambil data tabel
                $data_query = $db->query("SELECT * FROM `{$table_name}`");
                $rows = $data_query->result_array();

                foreach ($rows as $row) {
                    $columns = array_keys($row);
                    $values = array_map(function($val) {
                        if (is_null($val)) return "NULL";
                        return "'" . str_replace("'", "''", $val) . "'";
                    }, array_values($row));

                    $sql_dump .= "INSERT INTO `" . $table_name . "` (" . implode(',', $columns) . ") VALUES(" . implode(",", $values) . ");\n";
                }
                $sql_dump .= "\n";
            }

            // 4. Commit
            $sql_dump .= "COMMIT;\n";

            // 5. Header untuk download file
            header('Content-Type: application/sql');
            header('Content-Disposition: attachment; filename="' . $db_name . '_dump.sql"');
            header('Pragma: no-cache');
            header('Expires: 0');

            // 6. Output langsung
            echo $sql_dump;
            exit;

        } catch (Exception $e) {
            $this->sys_error("Error generating database dump: " . $e->getMessage());
            exit;
        }
    }

}