<?php
class Generatedata extends MY_Controller
{
    public function __construct()
    {
      parent::__construct();
      $this->db = $this->load->database("onedev", true);
      $this->db_corporate = $this->load->database("cpone_corporate", true);
      $this->db_dashboard = $this->load->database("cpone_dashboard", true);
      $this->db_log = $this->load->database("log", true);
    }

    function generate_code_string(){
        $length = 10;
        $characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        
        do {
            $code = '';
            for ($i = 0; $i < $length; $i++) {
                $code .= $characters[rand(0, strlen($characters) - 1)];
            }
            
            // Check if code already exists in mcu_number table
            $sql = "SELECT COUNT(*) as count FROM mcu_number WHERE Mcu_NumberTableName = ?";
            $query = $this->db_corporate->query($sql, [$code]);
            $result = $query->row_array();
            $exists = $result['count'] > 0;
            
        } while ($exists);
        
        return $code;
    }

    private function dashboard_table_exists($table_name)
    {
        return $this->db_dashboard->table_exists($table_name);
    }

    private function dashboard_column_exists($table_name, $column_name)
    {
        $query = $this->db_dashboard->query("SHOW COLUMNS FROM `{$table_name}` LIKE ?", [$column_name]);
        return $query && $query->num_rows() > 0;
    }

    private function ensure_dashboard_table_structures()
    {
        if (!$this->dashboard_table_exists('mcu_generate')) {
            $sql = "CREATE TABLE IF NOT EXISTS `mcu_generate` (
                        `Mcu_GenerateID` BIGINT NOT NULL AUTO_INCREMENT,
                        `Mgm_McuID` INT NOT NULL,
                        `Mcu_ProjectID` INT DEFAULT NULL,
                        `GenerateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        PRIMARY KEY (`Mcu_GenerateID`),
                        KEY `idx_mgm_mcuid` (`Mgm_McuID`),
                        KEY `idx_project_id` (`Mcu_ProjectID`),
                        KEY `idx_generate_date` (`GenerateDate`)
                    ) ENGINE=InnoDB";
            $query = $this->db_dashboard->query($sql);
            if (!$query) {
                $this->sys_error_db("create table mcu_generate", $this->db_dashboard);
                exit;
            }
        }

        $targetTables = ['kelainan_details', 'kelainan_summary', 'mcu_result_all'];
        foreach ($targetTables as $tableName) {
            if (!$this->dashboard_table_exists($tableName)) {
                $query = $this->db_dashboard->query("CREATE TABLE IF NOT EXISTS `{$tableName}` LIKE `cpone_corporate`.`{$tableName}`");
                if (!$query) {
                    $this->sys_error_db("create dashboard table {$tableName}", $this->db_dashboard);
                    exit;
                }
            }

            if (!$this->dashboard_column_exists($tableName, 'Mgm_McuID')) {
                $query = $this->db_dashboard->query("ALTER TABLE `{$tableName}` ADD COLUMN `Mgm_McuID` INT NULL");
                if (!$query) {
                    $this->sys_error_db("add column Mgm_McuID on {$tableName}", $this->db_dashboard);
                    exit;
                }
            }

            if (!$this->dashboard_column_exists($tableName, 'Mcu_GenerateID')) {
                $query = $this->db_dashboard->query("ALTER TABLE `{$tableName}` ADD COLUMN `Mcu_GenerateID` BIGINT NULL");
                if (!$query) {
                    $this->sys_error_db("add column Mcu_GenerateID on {$tableName}", $this->db_dashboard);
                    exit;
                }
            }

            if (!$this->dashboard_column_exists($tableName, 'Mcu_ProjectID')) {
                $query = $this->db_dashboard->query("ALTER TABLE `{$tableName}` ADD COLUMN `Mcu_ProjectID` INT NULL");
                if (!$query) {
                    $this->sys_error_db("add column Mcu_ProjectID on {$tableName}", $this->db_dashboard);
                    exit;
                }
            }
        }
    }

    private function resolve_dashboard_project_id($mgm_mcuid)
    {
        if (!$this->dashboard_table_exists('mcu_project')) {
            return null;
        }

        $columns = $this->db_dashboard->query("SHOW COLUMNS FROM `mcu_project`")->result_array();
        if (count($columns) === 0) {
            return null;
        }

        $colNames = array_map(function($col) {
            return $col['Field'];
        }, $columns);

        $projectIdCol = null;
        foreach (['Mcu_ProjectID', 'mcu_project_id', 'ProjectID', 'id'] as $candidate) {
            if (in_array($candidate, $colNames, true)) {
                $projectIdCol = $candidate;
                break;
            }
        }
        if ($projectIdCol === null) {
            return null;
        }

        $mgmCol = null;
        foreach (['Mgm_McuID', 'Mcu_ProjectMcuID', 'Mcu_ProjectMgm_McuID', 'McuProjectMgmMcuID', 'mgm_mcuid', 'mgm_mcu_id'] as $candidate) {
            if (in_array($candidate, $colNames, true)) {
                $mgmCol = $candidate;
                break;
            }
        }
        if ($mgmCol === null) {
            return null;
        }

        $sql = "SELECT `{$projectIdCol}` AS project_id
                FROM `mcu_project`
                WHERE `{$mgmCol}` = ?
                ORDER BY `{$projectIdCol}` DESC
                LIMIT 1";
        $query = $this->db_dashboard->query($sql, [$mgm_mcuid]);
        if (!$query || $query->num_rows() === 0) {
            return null;
        }

        return $query->row_array()['project_id'];
    }

    private function sync_to_dashboard($mgm_mcuid)
    {
        $mgm_mcuid = (int)$mgm_mcuid;
        if ($mgm_mcuid <= 0) {
            $this->sys_error("Mgm_McuID is required for dashboard sync");
            exit;
        }

        $this->ensure_dashboard_table_structures();

        $setupQuery = $this->db_corporate->query(
            "SELECT * FROM mcu_number WHERE Mcu_NumberMgm_McuID = ? AND Mcu_NumberIsActive = 'Y' ORDER BY Mcu_NumberID DESC LIMIT 1",
            [$mgm_mcuid]
        );
        if (!$setupQuery || $setupQuery->num_rows() === 0) {
            $this->sys_error("MGM MCU not found for dashboard sync");
            exit;
        }
        $setup = $setupQuery->row_array();
        $numbering = $setup['Mcu_NumberTableName'];

        $mcu_project_id = $this->resolve_dashboard_project_id($mgm_mcuid);

        $this->db_dashboard->trans_begin();

        $insertGenerate = $this->db_dashboard->query(
            "INSERT INTO mcu_generate (Mgm_McuID, Mcu_ProjectID, GenerateDate) VALUES (?, ?, NOW())",
            [$mgm_mcuid, $mcu_project_id]
        );
        if (!$insertGenerate) {
            $this->db_dashboard->trans_rollback();
            $this->sys_error_db("insert mcu_generate", $this->db_dashboard);
            exit;
        }
        $generate_id = $this->db_dashboard->insert_id();

        $query = $this->db_dashboard->query("DELETE FROM kelainan_details WHERE Mgm_McuID = ?", [$mgm_mcuid]);
        if (!$query) {
            $this->db_dashboard->trans_rollback();
            $this->sys_error_db("delete dashboard kelainan_details", $this->db_dashboard);
            exit;
        }

        $query = $this->db_dashboard->query("DELETE FROM kelainan_summary WHERE Mcu_KelainanMgm_McuID = ? OR Mgm_McuID = ?", [$mgm_mcuid, $mgm_mcuid]);
        if (!$query) {
            $this->db_dashboard->trans_rollback();
            $this->sys_error_db("delete dashboard kelainan_summary", $this->db_dashboard);
            exit;
        }

        $query = $this->db_dashboard->query("DELETE FROM mcu_result_all WHERE Mcu_ResultAllMgm_McuID = ? OR Mgm_McuID = ?", [$mgm_mcuid, $mgm_mcuid]);
        if (!$query) {
            $this->db_dashboard->trans_rollback();
            $this->sys_error_db("delete dashboard mcu_result_all", $this->db_dashboard);
            exit;
        }

        $query = $this->db_dashboard->query(
            "INSERT INTO kelainan_details
             SELECT kd.*, ?, ?, ?
             FROM cpone_corporate.kelainan_details kd
             WHERE kd.Numbering = ?",
            [$mgm_mcuid, $generate_id, $mcu_project_id, $numbering]
        );
        if (!$query) {
            $this->db_dashboard->trans_rollback();
            $this->sys_error_db("insert dashboard kelainan_details", $this->db_dashboard);
            exit;
        }

        $query = $this->db_dashboard->query(
            "INSERT INTO kelainan_summary
             SELECT ks.*, ?, ?, ?
             FROM cpone_corporate.kelainan_summary ks
             WHERE ks.Mcu_KelainanMgm_McuID = ?",
            [$mgm_mcuid, $generate_id, $mcu_project_id, $mgm_mcuid]
        );
        if (!$query) {
            $this->db_dashboard->trans_rollback();
            $this->sys_error_db("insert dashboard kelainan_summary", $this->db_dashboard);
            exit;
        }

        $query = $this->db_dashboard->query(
            "INSERT INTO mcu_result_all
             SELECT mra.*, ?, ?, ?
             FROM cpone_corporate.mcu_result_all mra
             WHERE mra.Mcu_ResultAllMgm_McuID = ?",
            [$mgm_mcuid, $generate_id, $mcu_project_id, $mgm_mcuid]
        );
        if (!$query) {
            $this->db_dashboard->trans_rollback();
            $this->sys_error_db("insert dashboard mcu_result_all", $this->db_dashboard);
            exit;
        }

        $this->db_dashboard->trans_commit();

        return array(
            "Mcu_GenerateID" => $generate_id,
            "Mcu_ProjectID" => $mcu_project_id,
            "Mgm_McuID" => $mgm_mcuid
        );
    }

    function generate_table_setup(){
        /*if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}*/

        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];
     
        $userid = 0;
        
        $sql = "SELECT * 
                FROM mgm_mcu 
                JOIN corporate ON CorporateID = Mgm_McuCorporateID
                WHERE 
                    Mgm_McuID = ? AND 
                    Mgm_McuIsActive = 'Y'
                LIMIT 1";
        $query = $this->db->query($sql, [$mgm_mcuid]);
       
        $data = $query->result_array();
        if(count($data) > 0){

            $sql = "SELECT COUNT(*) as total
                    FROM t_orderheader
                    WHERE
                    T_OrderHeaderMgm_McuID = ? AND
                    T_OrderHeaderIsActive = 'Y'";
            $query = $this->db->query($sql, [$mgm_mcuid]);
            $result = $query->row_array();
            $total_participants = $result['total'];

            $code = $this->generate_code_string();

            $sql = "SELECT COUNT(*) as total
                    FROM `mcu_number`
                    WHERE Mcu_NumberMgm_McuID = ? AND Mcu_NumberIsActive = 'Y'";
            $query = $this->db_corporate->query($sql, [$mgm_mcuid]);
            $result = $query->row_array();
            $total_mcu_number = $result['total'];

            if($total_mcu_number > 0){
                $this->sys_error("MGM MCU already has a table setup");
                exit;
            }

            $sql = "INSERT INTO `mcu_number` (
                        `Mcu_NumberMgm_McuID`,
                        `Mcu_NumberMgm_McuNumber`,
                        `Mcu_NumberTableName`,
                        `Mcu_NumberStartDate`,
                        `Mcu_NumberEndDate`,
                        `Mcu_NumberTotalParticipants`,
                        `Mcu_NumberCorporateCode`,
                        `Mcu_NumberCorporateName`,
                        `Mcu_NumberCorporateAddress`,
                        `Mcu_NumberCreated`,
                        `Mcu_NumberCreatedUserID`
                    ) VALUES(?,?,?,?,?,?,?,?,?,NOW(),?)";
            $query = $this->db_corporate->query($sql, [
                $data[0]['Mgm_McuID'],
                $data[0]['Mgm_McuNumber'],
                $code,
                $data[0]['Mgm_McuStartDate'],
                $data[0]['Mgm_McuEndDate'],
                $total_participants,
                $data[0]['CorporateCode'],
                $data[0]['CorporateName'],
                $data[0]['CorporateAddress'],
                $userid
            ]);
            if(!$query){
                $this->sys_error_db("save table setup", $this->db_corporate);
                exit;
            }else{
                $result = array("code" => $code, "sql"=> '', 'message' => 'Table setup generated successfully');
                $this->sys_ok($result);
                exit;
            }
        }else{
            $this->sys_error("MGM MCU not found");
            exit;
        }
    }

    function generate_kelainan_lab()
    {
        /*if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }*/
        
        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];

        $sql = "SELECT * FROM mcu_number WHERE Mcu_NumberMgm_McuID = ? AND Mcu_NumberIsActive = 'Y' ORDER BY Mcu_NumberID DESC LIMIT 1";
        $query = $this->db_corporate->query($sql, [$mgm_mcuid]);
        if(!$query){
            $this->sys_error_db("select data mcu_number", $this->db_corporate);
            //echo $this->db_corporate->error_message();
            echo $this->db_corporate->last_query();
            exit;
        }
        $data_setup = $query->result_array();
        if(count($data_setup) == 0){
            $this->sys_error("MGM MCU not found");
            exit;
        }
        //print_r($data_setup); exit;

        $numbering_column = $data_setup[0]['Mcu_NumberTableName'];

        $table_name = "kelainan_details";
        $exist_table = $this->db_corporate->table_exists($table_name);

        $this->db_corporate->trans_begin();

        if(!$exist_table){
            $sql = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
                        Tx_KelainanID INT,
                        Tx_Type VARCHAR(10),
                        T_OrderHeaderID INT,
                        T_OrderHeaderDate DATETIME,
                        T_OrderHeaderLabNumber VARCHAR(100),
                        AgePatient INT,
                        M_PatientID INT,
                        M_PatientNoReg VARCHAR(50),
                        M_PatientDOB DATE,
                        M_PatientGender VARCHAR(10),
                        M_PatientIdentifierValue VARCHAR(100),
                        M_PatientNIP VARCHAR(50),
                        M_PatientJob VARCHAR(100),
                        M_PatientPosisi VARCHAR(100),
                        M_PatientDivisi VARCHAR(100),
                        M_PatientLocation VARCHAR(100),
                        M_PatientDepartement VARCHAR(100),
                        PatientName VARCHAR(255),
                        M_PatientName VARCHAR(255),
                        GroupResult VARCHAR(100),
                        Nat_TestID INT,
                        Nat_TestCode VARCHAR(50),
                        Nat_TestName VARCHAR(255),
                        T_TestName VARCHAR(255),
                        Result VARCHAR(255),
                        Mcu_KelainanID INT,
                        Mcu_KelainanName VARCHAR(255),
                        Mcu_KelainanGroupSummaryID INT,
                        Mcu_KelainanGroupSummaryName VARCHAR(255),
                        Mcu_FitnessCategoryID INT,
                        Mcu_FitnessCategoryName VARCHAR(100),
                        Mcu_FitnessCategoryEng VARCHAR(100),
                        Mcu_FitnessCategoryLevel INT,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        INDEX idx_kelainan_lab (Tx_KelainanID),
                        INDEX idx_order_header (T_OrderHeaderID),
                        INDEX idx_order_lab_number (T_OrderHeaderLabNumber),
                        INDEX idx_patient_id (M_PatientID),
                        INDEX idx_patient_noreg (M_PatientNoReg),
                        INDEX idx_patient_location (M_PatientLocation),
                        INDEX idx_patient_posisi (M_PatientPosisi),
                        INDEX idx_patient_divisi (M_PatientDivisi),
                        INDEX idx_patient_departement (M_PatientDepartement),
                        INDEX idx_test (Nat_TestID),
                        INDEX idx_kelainan (Mcu_KelainanID),
                        INDEX idx_group_result (GroupResult),
                        INDEX idx_numbering (Numbering)
                    ) ENGINE=InnoDB";
            $query = $this->db_corporate->query($sql);
            
            if(!$query){
                $this->db_corporate->trans_rollback();
                $this->sys_error_db("create table kelainan_details", $this->db_corporate);
                exit;
            }
        }

        $sql = "INSERT INTO `cpone_corporate`.`{$table_name}` (
                    Numbering,
                    Tx_KelainanID,
                    Tx_Type,
                    T_OrderHeaderID,
                    T_OrderHeaderDate,
                    T_OrderHeaderLabNumber,
                    AgePatient,
                    M_PatientID,
                    M_PatientNoReg,
                    M_PatientDOB,
                    M_PatientGender,
                    M_PatientIdentifierValue,
                    M_PatientNIP,
                    M_PatientJob,
                    M_PatientPosisi,
                    M_PatientDivisi,
                    M_PatientLocation,
                    M_PatientDepartement,
                    PatientName,
                    M_PatientName,
                    GroupResult,
                    Nat_TestID,
                    Nat_TestCode,
                    Nat_TestName,
                    T_TestName,
                    Result,
                    Mcu_KelainanID,
                    Mcu_KelainanName,
                    Mcu_KelainanGroupSummaryID,
                    Mcu_KelainanGroupSummaryName,
                    Mcu_FitnessCategoryID,
                    Mcu_FitnessCategoryName,
                    Mcu_FitnessCategoryEng,
                    Mcu_FitnessCategoryLevel
                )
                SELECT 
                    '{$numbering_column}',
                    T_KelainanLabID as Tx_KelainanID, 
                    'LAB' as Tx_Type,
                    T_OrderHeaderID, 
                    T_OrderHeaderDate, 
                    T_OrderHeaderLabNumber, 
                    T_OrderHeaderM_PatientAge as AgePatient,
                    M_PatientID,
                    M_PatientNoReg, 
                    M_PatientDOB,
                    M_PatientGender,
                    M_PatientIdentifierValue,
                    M_PatientNIP,
                    M_PatientJob,
                    M_PatientPosisi,
                    M_PatientDivisi,
                    M_PatientLocation,
                    M_PatientDepartement,
                    CONCAT(
                        IF(M_TitleName IS NULL, '', CONCAT(M_TitleName, '. ')), 
                        IF(M_PatientPrefix IS NULL, '', CONCAT(M_PatientPrefix, ' ')), 
                        M_PatientName, 
                        IF(M_PatientSuffix IS NULL, '', CONCAT(M_PatientSuffix, ' '))
                    ) as PatientName, 
                    M_PatientName,
                    IF(Group_ResultName = 'LAB', 'LAB', Nat_TestName) as GroupResult,
                    Nat_TestID,
                    Nat_TestCode, 
                    Nat_TestName,
                    T_TestName,
                    IFNULL(T_OrderDetailResult, '') as Result,
                    Mcu_KelainanID,
                    Mcu_KelainanName,
                    Mcu_KelainanGroupSummaryID,
                    Mcu_KelainanGroupSummaryName,
                    Mcu_FitnessCategoryID,
                    Mcu_FitnessCategoryName,
                    Mcu_FitnessCategoryEng,
                    Mcu_FitnessCategoryLevel
                FROM t_kelainan_lab
                JOIN t_orderdetail ON T_KelainanLabT_OrderDetailID = T_OrderDetailID AND T_OrderDetailIsActive = 'Y'
                JOIN t_orderheader ON T_KelainanLabT_OrderHeaderID = T_OrderHeaderID 
                    AND T_OrderHeaderIsActive = 'Y'
                JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID 
                    AND M_PatientIsActive = 'Y'
                JOIN mgm_mcu ON T_OrderHeaderMgm_McuID = Mgm_McuID 
                    AND Mgm_McuID = ?
                JOIN mcu_summarylab ON T_KelainanLabMcu_SummaryLabID = Mcu_SummaryLabID 
                    AND Mcu_SummaryLabIsActive = 'Y'
                JOIN nat_test ON Mcu_SummaryLabNat_TestID = Nat_TestID 
                    AND Nat_TestIsActive = 'Y'
                JOIN t_test ON T_TestNat_TestID = Nat_TestID AND T_TestIsActive = 'Y'
                JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_TestID AND Group_ResultDetailIsActive = 'Y'
                JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultIsActive = 'Y'
                JOIN mcu_kelainan ON Mcu_summaryLabMcu_KelainanID = Mcu_KelainanID 
                    AND Mcu_KelainanIsActive = 'Y'
                JOIN mcu_kelainangroupsummarydetail ON Mcu_KelainanGroupSummaryDetailMcu_KelainanID = Mcu_KelainanID 
                    AND Mcu_KelainanGroupSummaryDetailIsActive = 'Y'
                JOIN mcu_kelainangroupsummary ON Mcu_KelainanGroupSummaryDetailMcu_KelainanGroupSummaryID = Mcu_KelainanGroupSummaryID 
                    AND Mcu_KelainanGroupSummaryIsActive = 'Y'
                JOIN mcu_fitness_category ON Mcu_SummaryLabMcu_FitnessCategoryID = Mcu_FitnessCategoryID 
                    AND Mcu_FitnessCategoryIsActive = 'Y'
                LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID 
                    AND M_TitleIsActive = 'Y'
                WHERE T_KelainanLabIsActive = 'Y'
                GROUP BY T_KelainanLabID";
                
        $query = $this->db->query($sql,[$mgm_mcuid]);
        if(!$query){
            $this->db->trans_rollback();
            $this->sys_error_db("insert data lab kelainan_details", $this->db);
            exit;
        }

        $this->db_corporate->trans_commit();

        $dashboard_sync = $this->sync_to_dashboard($mgm_mcuid);
        $result = array("message" => "Data lab kelainan_details generated successfully", "dashboard_sync" => $dashboard_sync);
        $this->sys_ok($result);
        exit;
       
    }

    function generate_kelainan_nonlab()
    {
        /*if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }*/
        
        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];

        $sql = "SELECT * 
                FROM mcu_number
                WHERE Mcu_NumberMgm_McuID = ? AND
                Mcu_NumberIsActive = 'Y'
                ORDER BY Mcu_NumberID DESC
                LIMIT 1";
        $query = $this->db_corporate->query($sql, [$mgm_mcuid]);
        $data_setup = $query->result_array();
        if(count($data_setup) == 0){
            $this->sys_error("MGM MCU not found");
            exit;
        }
        $numbering_column = $data_setup[0]['Mcu_NumberTableName'];


        $table_name = "kelainan_details";
        $exist_table = $this->db_corporate->table_exists($table_name);

        $this->db_corporate->trans_begin();

        if(!$exist_table){
            $sql = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
                        Numbering VARCHAR(150),
                        Tx_KelainanID INT,
                        Tx_Type VARCHAR(10),
                        T_OrderHeaderID INT,
                        T_OrderHeaderDate DATETIME,
                        T_OrderHeaderLabNumber VARCHAR(100),
                        AgePatient INT,
                        M_PatientID INT,
                        M_PatientNoReg VARCHAR(50),
                        M_PatientDOB DATE,
                        M_PatientGender VARCHAR(10),
                        M_PatientIdentifierValue VARCHAR(100),
                        M_PatientNIP VARCHAR(50),
                        M_PatientJob VARCHAR(100),
                        M_PatientPosisi VARCHAR(100),
                        M_PatientDivisi VARCHAR(100),
                        M_PatientLocation VARCHAR(100),
                        M_PatientDepartement VARCHAR(100),
                        PatientName VARCHAR(255),
                        M_PatientName VARCHAR(255),
                        GroupResult VARCHAR(100),
                        Nat_TestID INT,
                        Nat_TestCode VARCHAR(50),
                        Nat_TestName VARCHAR(255),
                        T_TestName VARCHAR(255),
                        Result VARCHAR(255),
                        Mcu_KelainanID INT,
                        Mcu_KelainanName VARCHAR(255),
                        Mcu_KelainanGroupSummaryID INT,
                        Mcu_KelainanGroupSummaryName VARCHAR(255),
                        Mcu_FitnessCategoryID INT,
                        Mcu_FitnessCategoryName VARCHAR(100),
                        Mcu_FitnessCategoryEng VARCHAR(100),
                        Mcu_FitnessCategoryLevel INT,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        INDEX idx_kelainan_lab (Tx_KelainanID),
                        INDEX idx_order_header (T_OrderHeaderID),
                        INDEX idx_order_lab_number (T_OrderHeaderLabNumber),
                        INDEX idx_patient_id (M_PatientID),
                        INDEX idx_patient_noreg (M_PatientNoReg),
                        INDEX idx_patient_location (M_PatientLocation),
                        INDEX idx_patient_posisi (M_PatientPosisi),
                        INDEX idx_patient_divisi (M_PatientDivisi),
                        INDEX idx_patient_departement (M_PatientDepartement),
                        INDEX idx_test (Nat_TestID),
                        INDEX idx_kelainan (Mcu_KelainanID),
                        INDEX idx_group_result (GroupResult),
                        INDEX idx_numbering (Numbering)
                    ) ENGINE=InnoDB";
            $query = $this->db_corporate->query($sql);
            if(!$query){
                $this->db_corporate->trans_rollback();
                $this->sys_error_db("create table kelainan_details", $this->db_corporate);
                exit;
            }
        }

        $sql = "INSERT INTO `cpone_corporate`.`{$table_name}` (
                    Numbering,
                    Tx_KelainanID,
                    Tx_Type,
                    T_OrderHeaderID,
                    T_OrderHeaderDate,
                    T_OrderHeaderLabNumber,
                    AgePatient,
                    M_PatientID,
                    M_PatientNoReg,
                    M_PatientDOB,
                    M_PatientGender,
                    M_PatientIdentifierValue,
                    M_PatientNIP,
                    M_PatientJob,
                    M_PatientPosisi,
                    M_PatientDivisi,
                    M_PatientLocation,
                    M_PatientDepartement,
                    PatientName,
                    M_PatientName,
                    GroupResult,
                    Nat_TestID,
                    Nat_TestCode,
                    Nat_TestName,
                    T_TestName,
                    Result,
                    Mcu_KelainanID,
                    Mcu_KelainanName,
                    Mcu_KelainanGroupSummaryID,
                    Mcu_KelainanGroupSummaryName,
                    Mcu_FitnessCategoryID,
                    Mcu_FitnessCategoryName,
                    Mcu_FitnessCategoryEng,
                    Mcu_FitnessCategoryLevel
                )
                SELECT 
                '{$numbering_column}',
                T_KelainanNonLabID as Tx_KelainanID,
                'NONLAB' as Tx_Type,
                T_OrderHeaderID, 
                T_OrderHeaderDate, 
                T_OrderHeaderLabNumber, 
                T_OrderHeaderM_PatientAge as AgePatient,
                M_PatientID,
                M_PatientNoReg, 
                M_PatientDOB,
                M_PatientGender,
                M_PatientIdentifierValue,
                M_PatientNIP,
                M_PatientJob,
                M_PatientPosisi,
                M_PatientDivisi,
                M_PatientLocation,
                M_PatientDepartement,
                CONCAT(IF(M_TitleName IS NULL,'',CONCAT(M_TitleName,'. ')), IF(M_PatientPrefix IS NULL,'',CONCAT(M_PatientPrefix,' ')), M_PatientName, IF(M_PatientSuffix IS NULL,'',CONCAT(M_PatientPrefix,' '))) as PatientName, 
                M_PatientName,
                IF(Group_ResultName = 'LAB', 'LAB', Nat_TestName) as GroupResult,
                Nat_TestID,
                Nat_TestCode, 
                Nat_TestName,
                T_TestName,
                '',
                Mcu_KelainanID,
                Mcu_KelainanName,
                Mcu_KelainanGroupSummaryID,
                Mcu_KelainanGroupSummaryName,
                Mcu_FitnessCategoryID,
                Mcu_FitnessCategoryName,
                Mcu_FitnessCategoryEng,
                Mcu_FitnessCategoryLevel
                FROM t_kelainan_nonlab
                JOIN t_orderheader ON T_KelainanNonLabT_OrderHeaderID = T_OrderHeaderID AND
                T_OrderHeaderIsActive = 'Y'
                JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID AND M_PatientIsActive = 'Y'
                JOIN mgm_mcu ON T_OrderHeaderMgm_McuID = Mgm_McuID AND
                Mgm_McuID = ?
                JOIN mcu_summarynonlab ON T_KelainanNonLabMcu_SummaryNonlabID = Mcu_SummaryNonlabID AND
                Mcu_SummaryNonlabIsActive = 'Y'
                JOIN nat_test ON Mcu_SummaryNonlabNat_TestID = Nat_TestID AND Nat_TestIsActive = 'Y'
                JOIN t_test ON T_TestNat_TestID = Nat_TestID AND T_TestIsActive = 'Y'
                JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_TestID AND Group_ResultDetailIsActive = 'Y'
                JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultIsActive = 'Y'
                JOIN mcu_kelainan ON Mcu_SummaryNonlabMcu_KelainanID = Mcu_KelainanID AND Mcu_KelainanIsActive = 'Y'
                JOIN mcu_kelainangroupsummarydetail ON Mcu_KelainanGroupSummaryDetailMcu_KelainanID = Mcu_KelainanID AND Mcu_KelainanGroupSummaryDetailIsActive = 'Y'
                JOIN mcu_kelainangroupsummary ON Mcu_KelainanGroupSummaryDetailMcu_KelainanGroupSummaryID = Mcu_KelainanGroupSummaryID AND Mcu_KelainanGroupSummaryIsActive = 'Y'
                JOIN mcu_fitness_category ON Mcu_SummaryNonlabMcu_FitnessCategoryID = Mcu_FitnessCategoryID AND Mcu_FitnessCategoryIsActive = 'Y'
                LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID AND M_TitleIsActive = 'Y'
                WHERE
                T_KelainanNonLabIsActive = 'Y'";
                
        $query = $this->db->query($sql,[$mgm_mcuid]);
        //echo $this->db->last_query();
        //exit;
        if(!$query){
            $this->db->trans_rollback();
            $this->sys_error_db("insert data nonlab kelainan_details", $this->db);
            exit;
        }

        $this->db_corporate->trans_commit();

        $dashboard_sync = $this->sync_to_dashboard($mgm_mcuid);
        $result = array("message" => "Data nonlab kelainan_details generated successfully", "dashboard_sync" => $dashboard_sync);
        $this->sys_ok($result);
        exit;
       
    }

    function generate_kelainan_fisik()
    {
        /*if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }*/
        
        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];

        $sql = "SELECT * 
                FROM mcu_number
                WHERE Mcu_NumberMgm_McuID = ? AND
                Mcu_NumberIsActive = 'Y'
                ORDER BY Mcu_NumberID DESC
                LIMIT 1";
        $query = $this->db_corporate->query($sql, [$mgm_mcuid]);
        $data_setup = $query->result_array();
        if(count($data_setup) == 0){
            $this->sys_error("MGM MCU not found");
            exit;
        }

        $numbering_column = $data_setup[0]['Mcu_NumberTableName'];

        $table_name = "kelainan_details";
        $exist_table = $this->db_corporate->table_exists($table_name);

        $this->db_corporate->trans_begin();

        if(!$exist_table){
            $sql = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
                        Numbering VARCHAR(150),
                        Tx_KelainanID INT,
                        Tx_Type VARCHAR(10),
                        T_OrderHeaderID INT,
                        T_OrderHeaderDate DATETIME,
                        T_OrderHeaderLabNumber VARCHAR(100),
                        AgePatient INT,
                        M_PatientID INT,
                        M_PatientNoReg VARCHAR(50),
                        M_PatientDOB DATE,
                        M_PatientGender VARCHAR(10),
                        M_PatientIdentifierValue VARCHAR(100),
                        M_PatientNIP VARCHAR(50),
                        M_PatientJob VARCHAR(100),
                        M_PatientPosisi VARCHAR(100),
                        M_PatientDivisi VARCHAR(100),
                        M_PatientLocation VARCHAR(100),
                        M_PatientDepartement VARCHAR(100),
                        PatientName VARCHAR(255),
                        M_PatientName VARCHAR(255),
                        GroupResult VARCHAR(100),
                        Nat_TestID INT,
                        Nat_TestCode VARCHAR(50),
                        Nat_TestName VARCHAR(255),
                        T_TestName VARCHAR(255),
                        Result VARCHAR(255),
                        Mcu_KelainanID INT,
                        Mcu_KelainanName VARCHAR(255),
                        Mcu_KelainanGroupSummaryID INT,
                        Mcu_KelainanGroupSummaryName VARCHAR(255),
                        Mcu_FitnessCategoryID INT,
                        Mcu_FitnessCategoryName VARCHAR(100),
                        Mcu_FitnessCategoryEng VARCHAR(100),
                        Mcu_FitnessCategoryLevel INT,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        INDEX idx_numbering (Numbering),
                        INDEX idx_kelainan_lab (Tx_KelainanID),
                        INDEX idx_order_header (T_OrderHeaderID),
                        INDEX idx_order_lab_number (T_OrderHeaderLabNumber),
                        INDEX idx_patient_id (M_PatientID),
                        INDEX idx_patient_noreg (M_PatientNoReg),
                        INDEX idx_patient_location (M_PatientLocation),
                        INDEX idx_patient_posisi (M_PatientPosisi),
                        INDEX idx_patient_divisi (M_PatientDivisi),
                        INDEX idx_patient_departement (M_PatientDepartement),
                        INDEX idx_test (Nat_TestID),
                        INDEX idx_kelainan (Mcu_KelainanID),
                        INDEX idx_group_result (GroupResult)
                    ) ENGINE=InnoDB";
            $query = $this->db_corporate->query($sql);
            if(!$query){
                $this->db_corporate->trans_rollback();
                $this->sys_error_db("create table kelainan_details", $this->db_corporate);
                exit;
            }
        }

        $sql = "INSERT INTO `cpone_corporate`.`{$table_name}` (
                    Numbering,
                    Tx_KelainanID,
                    Tx_Type,
                    T_OrderHeaderID,
                    T_OrderHeaderDate,
                    T_OrderHeaderLabNumber,
                    AgePatient,
                    M_PatientID,
                    M_PatientNoReg,
                    M_PatientDOB,
                    M_PatientGender,
                    M_PatientIdentifierValue,
                    M_PatientNIP,
                    M_PatientJob,
                    M_PatientPosisi,
                    M_PatientDivisi,
                    M_PatientLocation,
                    M_PatientDepartement,
                    PatientName,
                    M_PatientName,
                    GroupResult,
                    Nat_TestID,
                    Nat_TestCode,
                    Nat_TestName,
                    T_TestName,
                    Result,
                    Mcu_KelainanID,
                    Mcu_KelainanName,
                    Mcu_KelainanGroupSummaryID,
                    Mcu_KelainanGroupSummaryName,
                    Mcu_FitnessCategoryID,
                    Mcu_FitnessCategoryName,
                    Mcu_FitnessCategoryEng,
                    Mcu_FitnessCategoryLevel
                )
                SELECT '{$numbering_column}',
                T_KelainanFiskID as Tx_KelainanID,
                'FISIK' as Tx_Type,
                T_OrderHeaderID, 
                T_OrderHeaderDate, 
                T_OrderHeaderLabNumber, 
                T_OrderHeaderM_PatientAge as AgePatient,
                M_PatientID,
                M_PatientNoReg, 
                M_PatientDOB,
                M_PatientGender,
                M_PatientIdentifierValue,
                M_PatientNIP,
                M_PatientJob,
                M_PatientPosisi,
                M_PatientDivisi,
                M_PatientLocation,
                M_PatientDepartement,
                CONCAT(IF(M_TitleName IS NULL,'',CONCAT(M_TitleName,'. ')), IF(M_PatientPrefix IS NULL,'',CONCAT(M_PatientPrefix,' ')), M_PatientName, IF(M_PatientSuffix IS NULL,'',CONCAT(M_PatientPrefix,' '))) as PatientName, 
                M_PatientName,
                IF(Group_ResultName = 'LAB', 'LAB', Nat_TestName) as GroupResult,
                Nat_TestID,
                Nat_TestCode, 
                Nat_TestName,
                CONCAT(IF(label IS NULL, Mcu_KelainanGroupName, CONCAT(label, ' - ',IF(level = 1, parent_level_1,IF(level = 2, parent_level_2, IF(level = 3,parent_level_3,parent_level_4)))))) as T_TestName,
                IF(Mcu_FisikValueNote = '',Mcu_FisikValueLabel,Mcu_FisikValueNote) as Result,
                Mcu_KelainanID,
                Mcu_KelainanName,
                Mcu_KelainanGroupSummaryID,
                Mcu_KelainanGroupSummaryName,
                Mcu_FitnessCategoryID,
                Mcu_FitnessCategoryName,
                Mcu_FitnessCategoryEng,
                Mcu_FitnessCategoryLevel
                FROM t_kelainan_fisik
                JOIN t_orderheader ON T_KelainanFiskT_OrderHeaderID = T_OrderHeaderID AND
                T_OrderHeaderIsActive = 'Y'
                JOIN so_resultentry ON T_KelainanFiskSo_ResultEntryID = So_ResultEntryID AND So_ResultEntryIsActive = 'Y'
                JOIN t_orderdetail ON So_ResultEntryT_OrderDetailID = T_OrderDetailID AND T_OrderDetailIsActive = 'Y'
                JOIN nat_test ON Nat_TestCode = T_OrderDetailT_TestCode AND Nat_TestIsActive = 'Y'
                JOIN t_test ON T_TestNat_TestID = Nat_TestID AND T_TestIsActive = 'Y'
                JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_TestID AND Group_ResultDetailIsActive = 'Y'
                JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultIsActive = 'Y'
                JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID AND M_PatientIsActive = 'Y'
                JOIN mgm_mcu ON T_OrderHeaderMgm_McuID = Mgm_McuID AND
                Mgm_McuID = ?
                JOIN mcu_fisiksummary ON T_KelainanFiskMcu_FisikSummaryID = Mcu_FisikSummaryID AND
                Mcu_FisikSummaryIsActive = 'Y'
                JOIN mcu_fisiksummarydetail ON Mcu_FisikSummaryDetailMcu_FisikSummaryID = Mcu_FisikSummaryID AND Mcu_FisikSummaryDetailIsActive = 'Y'
                JOIN mcu_kelainan ON Mcu_FisikSummaryMcu_KelainanID = Mcu_KelainanID AND Mcu_KelainanIsActive = 'Y'
                JOIN mcu_kelainangroup ON Mcu_KelainanMcu_KelainanGroupID = Mcu_KelainanGroupID AND Mcu_KelainanGroupIsActive = 'Y'
                JOIN mcu_kelainangroupsummarydetail ON Mcu_KelainanGroupSummaryDetailMcu_KelainanID = Mcu_KelainanID AND 
                Mcu_KelainanGroupSummaryDetailIsActive = 'Y'
                JOIN mcu_kelainangroupsummary ON Mcu_KelainanGroupSummaryDetailMcu_KelainanGroupSummaryID = Mcu_KelainanGroupSummaryID AND Mcu_KelainanGroupSummaryIsActive = 'Y'
                JOIN mcu_fitness_category ON Mcu_FisikSummaryMcu_FitnessCategoryID = Mcu_FitnessCategoryID AND Mcu_FitnessCategoryIsActive = 'Y'
                JOIN mcu_fisikvalue ON Mcu_FisikValueT_OrderHeaderID = T_OrderHeaderID AND Mcu_FisikValueIsActive = 'Y' AND
                Mcu_FisikValueCode = Mcu_FisikSummaryDetailCode
                LEFT JOIN fisik_template_map ON id_code = Mcu_FisikSummaryDetailCode 
                LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID AND M_TitleIsActive = 'Y'
                WHERE
                T_KelainanFiskIsActive = 'Y'";
                
        $query = $this->db->query($sql,[$mgm_mcuid]);
        if(!$query){
            $this->db->trans_rollback();
            $this->sys_error_db("insert data fisik kelainan_details", $this->db);
            exit;
        }

        $this->db_corporate->trans_commit();

        $dashboard_sync = $this->sync_to_dashboard($mgm_mcuid);
        $result = array("message" => "Data fisik kelainan_details generated successfully", "dashboard_sync" => $dashboard_sync);
        $this->sys_ok($result);
        exit;
       
    }


    function generate_all_results()
    {
        /*if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }*/

        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];

        $sql = "SELECT * FROM mcu_number WHERE Mcu_NumberMgm_McuID = ? AND Mcu_NumberIsActive = 'Y' ORDER BY Mcu_NumberID DESC LIMIT 1";
        $query = $this->db_corporate->query($sql, [$mgm_mcuid]);
        if(!$query){
            $this->sys_error_db("select data mcu_number", $this->db_corporate);
            //echo $this->db_corporate->error_message();
            echo $this->db_corporate->last_query();
            exit;
        }
        $data_setup = $query->result_array();
        if(count($data_setup) == 0){
            $this->sys_error("MGM MCU not found");
            exit;
        }

        $numbering_column = $data_setup[0]['Mcu_NumberTableName'];

        $table_name = "mcu_result_all";
        $exist_table = $this->db_corporate->table_exists($table_name);

        $this->db_corporate->trans_begin();

        if(!$exist_table){
            $sql = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
                        `Mcu_ResultAllID` int NOT NULL AUTO_INCREMENT,
                        `Numbering` varchar(150) DEFAULT NULL,
                        `Mcu_ResultAllM_LangID` int NOT NULL DEFAULT '1',
                        `Mcu_ResultAllMgm_McuID` int DEFAULT NULL,
                        `Mcu_ResultAllT_OrderHeaderID` int DEFAULT NULL,
                        `Mcu_ResultAllT_OrderHeaderLabNumber` varchar(50) DEFAULT NULL,
                        `Mcu_ResultAllReffID` int DEFAULT NULL,
                        `Mcu_ResultAllT_TestCode` varchar(50) DEFAULT NULL,
                        `Mcu_ResultAllT_TestName` varchar(255) DEFAULT NULL,
                        `Mcu_ResultAllResult` text,
                        `Mcu_ResultAllUnit` varchar(255) DEFAULT NULL,
                        `Mcu_ResultAllRefference` text,
                        `Mcu_ResultAllCreated` datetime DEFAULT NULL,
                        PRIMARY KEY (`Mcu_ResultAllID`),
                        KEY `Mcu_ResultAllMgm_McuID` (`Mcu_ResultAllMgm_McuID`),
                        KEY `Mcu_ResultAllT_OrderHeaderID` (`Mcu_ResultAllT_OrderHeaderID`),
                        KEY `Mcu_ResultAllT_OrderHeaderLabNumber` (`Mcu_ResultAllT_OrderHeaderLabNumber`),
                        KEY `Mcu_ResultAllReffID` (`Mcu_ResultAllReffID`),
                        KEY `Mcu_ResultAllT_TestCode` (`Mcu_ResultAllT_TestCode`),
                        KEY `Numbering` (`Numbering`)
                    ) ENGINE=InnoDB";
            $query = $this->db_corporate->query($sql);
            
            if(!$query){
                $this->db_corporate->trans_rollback();
                $this->sys_error_db("create table group_summary", $this->db_corporate);
                exit;
            }
        }

        $sql = "INSERT INTO `cpone_corporate`.`{$table_name}` (
                    Numbering,
                    Mcu_ResultAllM_LangID,
                    Mcu_ResultAllMgm_McuID,
                    Mcu_ResultAllT_OrderHeaderID,
                    Mcu_ResultAllT_OrderHeaderLabNumber,
                    Mcu_ResultAllReffID,
                    Mcu_ResultAllT_TestCode,
                    Mcu_ResultAllT_TestName,
                    Mcu_ResultAllResult,
                    Mcu_ResultAllUnit,
                    Mcu_ResultAllRefference,
                    Mcu_ResultAllCreated
                )
                SELECT '{$numbering_column}',
                Mcu_ResultAllM_LangID,
                Mcu_ResultAllMgm_McuID,
                Mcu_ResultAllT_OrderHeaderID,
                Mcu_ResultAllT_OrderHeaderLabNumber,
                Mcu_ResultAllReffID,
                Mcu_ResultAllT_TestCode,
                Mcu_ResultAllT_TestName,
                Mcu_ResultAllResult,
                Mcu_ResultAllUnit,
                Mcu_ResultAllRefference,
                Mcu_ResultAllCreated
                FROM `cpone_log`.`mcu_result_all`
                WHERE
                Mcu_ResultAllMgm_McuID = ?";
                
        $query = $this->db->query($sql,[$mgm_mcuid]);
        if(!$query){
            $this->db->trans_rollback();
            $this->sys_error_db("insert data mcu_result_all", $this->db);
            exit;
        }

        $this->db_corporate->trans_commit();

        $dashboard_sync = $this->sync_to_dashboard($mgm_mcuid);
        $result = array("message" => "Data mcu_result_all generated successfully", "dashboard_sync" => $dashboard_sync);
        $this->sys_ok($result);
        exit;
       
    }

    function summary_kelainan_sepuluh()
    {
        /*if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }*/
        
        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];

        $sql = "SELECT * FROM mcu_number WHERE Mcu_NumberMgm_McuID = ? AND Mcu_NumberIsActive = 'Y' ORDER BY Mcu_NumberID DESC LIMIT 1";
        $query = $this->db_corporate->query($sql, [$mgm_mcuid]);
        if(!$query){
            $this->sys_error_db("select data mcu_number", $this->db_corporate);
            //echo $this->db_corporate->error_message();
            echo $this->db_corporate->last_query();
            exit;
        }
        $data_setup = $query->result_array();
        if(count($data_setup) == 0){
            $this->sys_error("MGM MCU not found");
            exit;
        }

        $table_name = "kelainan_summary";
        $exist_table = $this->db_corporate->table_exists($table_name);

        $this->db_corporate->trans_begin();

        if(!$exist_table){
            $sql = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
                        Mcu_KelainanID INT,
                        Mcu_KelainanMgm_McuID INT,
                        Mcu_KelainanName VARCHAR(250),
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        INDEX idx_kelainan_summary (Mcu_KelainanID)
                    ) ENGINE=InnoDB";
            $query = $this->db_corporate->query($sql);
            
            if(!$query){
                $this->db_corporate->trans_rollback();
                $this->sys_error_db("create table group_summary", $this->db_corporate);
                exit;
            }
        }

        $sql = "INSERT INTO `cpone_corporate`.`{$table_name}` (
                    Mcu_KelainanID,
                    Mcu_KelainanMgm_McuID,
                    Mcu_KelainanName
                )
                SELECT Mcu_KelainanID,
                Kelainan_SummariesProjectMgm_McuID,
                Mcu_KelainanName
                FROM mcu_kelainan
                JOIN kelainan_summaries_project ON Kelainan_SummariesProjectKelainanID = Mcu_KelainanID 
                WHERE
                Kelainan_SummariesProjectMgm_McuID = ? AND Kelainan_SummariesProjectIsActive = 'Y'";
                
        $query = $this->db->query($sql,[$mgm_mcuid]);
        if(!$query){
            $this->db->trans_rollback();
            $this->sys_error_db("insert data group_summary", $this->db);
            exit;
        }

        $this->db_corporate->trans_commit();

        $dashboard_sync = $this->sync_to_dashboard($mgm_mcuid);
        $result = array("message" => "Data kelainan_summary generated successfully", "dashboard_sync" => $dashboard_sync);
        $this->sys_ok($result);
        exit;
       
    }


    /*
   UPDATE kelainan_details_JYMXG02AQ5
SET M_PatientDepartement = CASE 
    WHEN MOD(T_OrderHeaderID, 5) = 0 THEN 'IT Department'
    WHEN MOD(T_OrderHeaderID, 5) = 1 THEN 'HR Department'
    WHEN MOD(T_OrderHeaderID, 5) = 2 THEN 'Finance Department'
    WHEN MOD(T_OrderHeaderID, 5) = 3 THEN 'Operations Department'
    WHEN MOD(T_OrderHeaderID, 5) = 4 THEN 'Marketing Department'
END;
    */

    // ==================== FISIK TEMPLATE MAP FUNCTIONS ====================
    /*
     * FISIK TEMPLATE MAP - Dokumentasi & Urutan Penggunaan
     * ========================================================
     * 
     * TUJUAN:
     * Mengekstrak dan menyimpan semua id_code, label, dan parent hierarchy dari
     * JSON template (FisikTemplateJSON) ke dalam satu tabel terstruktur (fisik_template_map)
     * untuk memudahkan query dan mapping data.
     * 
     * URUTAN PENGGUNAAN:
     * ------------------
     * 
     * STEP 1: Lihat Template yang Aktif (Opsional)
     *         Endpoint: /generatedata/list_active_fisik_templates
     *         Fungsi  : list_active_fisik_templates()
     *         Tujuan  : Melihat list semua template dengan FisikTemplateIsActive = 'Y'
     *         Request : Tidak ada parameter
     *         Response: { total: 9, data: [template_list...] }
     * 
     * STEP 2: Lihat Detail Template (Opsional)
     *         Endpoint: /generatedata/get_fisik_template_detail
     *         Fungsi  : get_fisik_template_detail()
     *         Tujuan  : Melihat detail template tertentu dengan JSON parsed
     *         Request : { FisikTemplateCode: "RI00" }
     *         Response: { FisikTemplateJSON_Parsed: {...} }
     * 
     * STEP 3: Buat Tabel Mapping (Hanya Sekali)
     *         Endpoint: /generatedata/create_fisik_template_map_table
     *         Fungsi  : create_fisik_template_map_table()
     *         Tujuan  : Membuat tabel fisik_template_map jika belum ada
     *         Request : Tidak ada parameter
     *         Response: { message: "Table created successfully" }
     * 
     * STEP 4a: Generate Semua Template (Full Generate)
     *          Endpoint: /generatedata/generate_fisik_template_map
     *          Fungsi  : generate_fisik_template_map()
     *          Tujuan  : Extract & simpan mapping SEMUA template aktif
     *          Request : Tidak ada parameter
     *          Response: { total_records: 250, total_templates: 9 }
     *          Note    : Truncate semua data lama, insert semua data baru
     * 
     * STEP 4b: Generate Template Tertentu (Partial Update)
     *          Endpoint: /generatedata/generate_fisik_template_map_by_code
     *          Fungsi  : generate_fisik_template_map_by_code()
     *          Tujuan  : Extract & update mapping untuk satu template saja
     *          Request : { FisikTemplateCode: "RI00" }
     *          Response: { template_code: "RI00", total_records: 9 }
     *          Note    : Hapus data lama template ini saja, insert data baru
     *          Use Case: Ketika ada perubahan di satu template, tidak perlu regenerate semua
     * 
     * STEP 5a: Query Data Mapping
     *          Endpoint: /generatedata/get_fisik_template_map
     *          Fungsi  : get_fisik_template_map()
     *          Tujuan  : Ambil data mapping dengan filter opsional
     *          Request : { FisikTemplateCode: "RI00", level: 2 } (opsional)
     *          Response: Array of mapping data
     * 
     * STEP 5b: Search Data Mapping
     *          Endpoint: /generatedata/search_fisik_template_map
     *          Fungsi  : search_fisik_template_map()
     *          Tujuan  : Cari mapping berdasarkan id_code atau label
     *          Request : { keyword: "perut" }
     *          Response: Array of matching records
     * 
     * FUNGSI HELPER (Internal):
     * -------------------------
     * - get_active_fisik_templates()       : Helper untuk query template aktif
     * - extract_data_from_json()           : Extract data dari JSON recursively
     * - extract_flat_details()             : Extract untuk structure flat
     * - extract_nested_with_name()         : Extract untuk structure dengan grouping
     * - extract_sub_details()              : Extract sub-details recursively
     * 
     * STRUKTUR TABEL MAPPING:
     * ----------------------
     * - MapID              : Primary key auto increment
     * - FisikTemplateID    : ID template source
     * - FisikTemplateCode  : Code template (RI00, RI01, FI00, dst)
     * - FisikTemplateType  : Type template (Riwayat, Fisik, K3)
     * - id_code            : ID unik dari JSON (perut_1, perut_14_1, dst)
     * - label              : Label dari item
     * - parent_level_1     : Title utama (PERUT / ABDOMEN)
     * - parent_level_2     : Section/Group name (Hernia, Abdomen, dst)
     * - parent_level_3     : Sub-item parent
     * - parent_level_4     : Sub-sub-item parent
     * - level              : Kedalaman level (1-5)
     * - table_name         : Nama tabel target (perut, fisik_etnis, dst)
     * - segment_name       : Segment name dari item
     * - type_form          : Type form (XVS, XVS-LXX, XV, dst)
     * 
     * CONTOH QUERY DATA:
     * ------------------
     * 1. Ambil semua item dari template RI00:
     *    SELECT * FROM fisik_template_map WHERE FisikTemplateCode = 'RI00'
     * 
     * 2. Ambil semua item level 2 (biasanya item utama):
     *    SELECT * FROM fisik_template_map WHERE level = 2
     * 
     * 3. Cari berdasarkan id_code:
     *    SELECT * FROM fisik_template_map WHERE id_code = 'perut_14_1'
     * 
     * 4. Cari item dengan parent tertentu:
     *    SELECT * FROM fisik_template_map 
     *    WHERE parent_level_1 = 'PERUT / ABDOMEN' 
     *      AND parent_level_2 = 'Hernia'
     * 
     * UPDATE WORKFLOW:
     * ----------------
     * Jika ada perubahan di fisik_template:
     * 1. Update JSON di tabel fisik_template
     * 2. Jalankan generate_fisik_template_map_by_code() untuk template yg berubah
     *    (Tidak perlu regenerate semua template)
     * 3. Mapping otomatis terupdate untuk template tersebut
     */
    
    /**
     * STEP 3: Create table fisik_template_map
     * ----------------------------------------
     * Endpoint: POST /generatedata/create_fisik_template_map_table
     * Request : No parameters
     * Response: { message: "Table created successfully" }
     */
    function create_fisik_template_map_table()
    {
        $sql = "CREATE TABLE IF NOT EXISTS `fisik_template_map` (
                    `MapID` int NOT NULL AUTO_INCREMENT,
                    `FisikTemplateID` int NOT NULL,
                    `FisikTemplateCode` varchar(50) NOT NULL,
                    `FisikTemplateType` varchar(25) NOT NULL,
                    `id_code` varchar(100) NOT NULL,
                    `label` varchar(255) NOT NULL,
                    `parent_level_1` varchar(255) NOT NULL DEFAULT '' COMMENT 'Title utama',
                    `parent_level_2` varchar(255) NOT NULL DEFAULT '' COMMENT 'Section/Group name',
                    `parent_level_3` varchar(255) NOT NULL DEFAULT '' COMMENT 'Sub-item parent',
                    `parent_level_4` varchar(255) NOT NULL DEFAULT '' COMMENT 'Sub-sub-item parent',
                    `level` int NOT NULL DEFAULT '1' COMMENT 'Kedalaman level',
                    `table_name` varchar(100) NOT NULL DEFAULT '',
                    `segment_name` varchar(255) NOT NULL DEFAULT '',
                    `type_form` varchar(50) NOT NULL DEFAULT '',
                    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`MapID`),
                    KEY `idx_template_id` (`FisikTemplateID`),
                    KEY `idx_id_code` (`id_code`),
                    KEY `idx_template_code` (`FisikTemplateCode`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        
        $query = $this->db->query($sql);
        
        if(!$query){
            $this->sys_error_db("create table fisik_template_map", $this->db);
            exit;
        }
        
        $result = array("message" => "Table fisik_template_map created successfully");
        $this->sys_ok($result);
        exit;
    }

    /**
     * HELPER FUNCTION (Internal)
     * ---------------------------
     * Get active fisik templates from database
     * Mengambil data template yang aktif (FisikTemplateIsActive = 'Y')
     * 
     * @param int|null $template_id Filter berdasarkan ID tertentu
     * @param string|null $template_code Filter berdasarkan code tertentu
     * @return array|false Array of templates atau false jika error
     * 
     * Usage:
     * - $this->get_active_fisik_templates()              // Semua template aktif
     * - $this->get_active_fisik_templates(1, null)       // Template dengan ID = 1
     * - $this->get_active_fisik_templates(null, 'RI00')  // Template dengan code = RI00
     */
    function get_active_fisik_templates($template_id = null, $template_code = null)
    {
        $sql = "SELECT FisikTemplateID, FisikTemplateType, FisikTemplateCode, 
                       FisikTemplateTitle, FisikTemplateJSON, FisikTemplateTableName
                FROM fisik_template 
                WHERE FisikTemplateIsActive = 'Y'";
        
        $params = array();
        
        if ($template_id !== null) {
            $sql .= " AND FisikTemplateID = ?";
            $params[] = $template_id;
        }
        
        if ($template_code !== null) {
            $sql .= " AND FisikTemplateCode = ?";
            $params[] = $template_code;
        }
        
        $sql .= " ORDER BY FisikTemplateID";
        
        $query = $this->db->query($sql, $params);
        
        if (!$query) {
            return false;
        }
        
        return $query->result_array();
    }

    /**
     * Extract data from JSON recursively
     */
    function extract_data_from_json($data, $templateInfo, &$results)
    {
        $mainTitle = isset($data['title']) ? $data['title'] : '';
        $typeForm = isset($data['type_form']) ? $data['type_form'] : '';
        
        if (isset($data['details']) && is_array($data['details'])) {
            $firstDetail = reset($data['details']);
            
            if (isset($firstDetail['name'])) {
                // Structure dengan grouping name
                $this->extract_nested_with_name($data['details'], $mainTitle, $typeForm, $templateInfo, $results);
            } else {
                // Structure flat
                $this->extract_flat_details($data['details'], $mainTitle, $typeForm, $templateInfo, $results);
            }
        }
    }

    /**
     * Extract flat details (tanpa grouping name)
     */
    function extract_flat_details($details, $mainTitle, $typeForm, $templateInfo, &$results)
    {
        foreach ($details as $item) {
            $result = array(
                'FisikTemplateID' => $templateInfo['FisikTemplateID'],
                'FisikTemplateCode' => $templateInfo['FisikTemplateCode'],
                'FisikTemplateType' => $templateInfo['FisikTemplateType'],
                'id_code' => isset($item['id_code']) ? $item['id_code'] : '',
                'label' => isset($item['label']) ? $item['label'] : '',
                'parent_level_1' => $mainTitle,
                'parent_level_2' => '',
                'parent_level_3' => '',
                'parent_level_4' => '',
                'level' => 1,
                'table_name' => isset($item['table_name']) ? $item['table_name'] : '',
                'segment_name' => isset($item['segment_name']) ? $item['segment_name'] : '',
                'type_form' => isset($item['type_form']) ? $item['type_form'] : $typeForm
            );
            $results[] = $result;
            
            // Cek sub-details
            if (isset($item['details']) && is_array($item['details'])) {
                $this->extract_sub_details(
                    $item['details'], 
                    $mainTitle, 
                    '', 
                    isset($item['label']) ? $item['label'] : '', 
                    '', 
                    $templateInfo, 
                    $results, 
                    isset($item['table_name']) ? $item['table_name'] : '', 
                    isset($item['segment_name']) ? $item['segment_name'] : '', 
                    $typeForm
                );
            }
        }
    }

    /**
     * Extract nested with name grouping
     */
    function extract_nested_with_name($details, $mainTitle, $typeForm, $templateInfo, &$results)
    {
        foreach ($details as $section) {
            $sectionName = isset($section['name']) ? $section['name'] : '';
            
            if (isset($section['details']) && is_array($section['details'])) {
                foreach ($section['details'] as $item) {
                    $result = array(
                        'FisikTemplateID' => $templateInfo['FisikTemplateID'],
                        'FisikTemplateCode' => $templateInfo['FisikTemplateCode'],
                        'FisikTemplateType' => $templateInfo['FisikTemplateType'],
                        'id_code' => isset($item['id_code']) ? $item['id_code'] : '',
                        'label' => isset($item['label']) ? $item['label'] : '',
                        'parent_level_1' => $mainTitle,
                        'parent_level_2' => $sectionName,
                        'parent_level_3' => '',
                        'parent_level_4' => '',
                        'level' => 2,
                        'table_name' => isset($item['table_name']) ? $item['table_name'] : '',
                        'segment_name' => isset($item['segment_name']) ? $item['segment_name'] : '',
                        'type_form' => isset($item['type_form']) ? $item['type_form'] : $typeForm
                    );
                    $results[] = $result;
                    
                    // Cek sub-details
                    if (isset($item['details']) && is_array($item['details'])) {
                        $this->extract_sub_details(
                            $item['details'], 
                            $mainTitle, 
                            $sectionName, 
                            isset($item['label']) ? $item['label'] : '', 
                            '', 
                            $templateInfo, 
                            $results, 
                            isset($item['table_name']) ? $item['table_name'] : '', 
                            isset($item['segment_name']) ? $item['segment_name'] : '', 
                            $typeForm
                        );
                    }
                }
            }
        }
    }

    /**
     * Extract sub-details recursively
     */
    function extract_sub_details($subDetails, $parentL1, $parentL2, $parentL3, $parentL4, 
                                        $templateInfo, &$results, $tableName, $segmentName, $typeForm)
    {
        foreach ($subDetails as $subItem) {
            // Tentukan level berdasarkan parent yang terisi
            $level = 3;
            if ($parentL4 != '') $level = 5;
            elseif ($parentL3 != '') $level = 4;
            elseif ($parentL2 != '') $level = 3;
            else $level = 2;
            
            $result = array(
                'FisikTemplateID' => $templateInfo['FisikTemplateID'],
                'FisikTemplateCode' => $templateInfo['FisikTemplateCode'],
                'FisikTemplateType' => $templateInfo['FisikTemplateType'],
                'id_code' => isset($subItem['id_code']) ? $subItem['id_code'] : '',
                'label' => isset($subItem['label']) ? $subItem['label'] : '',
                'parent_level_1' => $parentL1,
                'parent_level_2' => $parentL2,
                'parent_level_3' => $parentL3,
                'parent_level_4' => $parentL4,
                'level' => $level,
                'table_name' => isset($subItem['table_name']) ? $subItem['table_name'] : $tableName,
                'segment_name' => isset($subItem['segment_name']) ? $subItem['segment_name'] : $segmentName,
                'type_form' => isset($subItem['type_form']) ? $subItem['type_form'] : $typeForm
            );
            $results[] = $result;
            
            // Cek sub-details lagi (rekursif)
            if (isset($subItem['details']) && is_array($subItem['details'])) {
                if ($level == 3) {
                    $this->extract_sub_details(
                        $subItem['details'], 
                        $parentL1, 
                        $parentL2, 
                        $parentL3, 
                        isset($subItem['label']) ? $subItem['label'] : '', 
                        $templateInfo, 
                        $results, 
                        $tableName, 
                        $segmentName, 
                        $typeForm
                    );
                } elseif ($level == 4) {
                    $this->extract_sub_details(
                        $subItem['details'], 
                        $parentL1, 
                        $parentL2, 
                        $parentL3, 
                        isset($subItem['label']) ? $subItem['label'] : '', 
                        $templateInfo, 
                        $results, 
                        $tableName, 
                        $segmentName, 
                        $typeForm
                    );
                }
            }
        }
    }

    /**
     * STEP 4a: Generate fisik template map data (semua template aktif)
     * ------------------------------------------------------------------
     * Endpoint: POST /generatedata/generate_fisik_template_map
     * Request : No parameters
     * Response: { message: "...", total_records: 250, total_templates: 9 }
     * 
     * Proses:
     * 1. Ambil SEMUA template aktif (FisikTemplateIsActive = 'Y')
     * 2. Extract semua id_code, label, dan hierarchy dari JSON
     * 3. TRUNCATE tabel fisik_template_map (hapus semua data lama)
     * 4. Insert semua data baru secara batch
     * 
     * Kapan digunakan:
     * - Setup awal (pertama kali)
     * - Regenerate semua data mapping
     * - Sinkronisasi ulang semua template
     * 
     * Note: Akan hapus SEMUA data lama dan insert ulang semua
     */
    function generate_fisik_template_map()
    {
        // Cek apakah tabel sudah ada
        if (!$this->db->table_exists('fisik_template_map')) {
            $this->sys_error("Table fisik_template_map does not exist. Please create it first.");
            exit;
        }

        $this->db->trans_begin();

        try {
            // Ambil data dari fisik_template menggunakan helper function
            $templates = $this->get_active_fisik_templates();
            
            if ($templates === false) {
                $this->db->trans_rollback();
                $this->sys_error_db("select fisik_template", $this->db);
                exit;
            }

            if (count($templates) == 0) {
                $this->db->trans_rollback();
                $this->sys_error("No active templates found");
                exit;
            }

            $allResults = array();
            $totalProcessed = 0;

            foreach ($templates as $row) {
                $templateInfo = array(
                    'FisikTemplateID' => $row['FisikTemplateID'],
                    'FisikTemplateCode' => $row['FisikTemplateCode'],
                    'FisikTemplateType' => $row['FisikTemplateType']
                );
                
                // Parse JSON
                $jsonData = json_decode($row['FisikTemplateJSON'], true);
                
                if ($jsonData === null) {
                    continue;
                }
                
                $results = array();
                $this->extract_data_from_json($jsonData, $templateInfo, $results);
                
                $allResults = array_merge($allResults, $results);
                $totalProcessed += count($results);
            }

            // Truncate tabel sebelum insert
            $this->db->truncate('fisik_template_map');

            // Insert data
            if (count($allResults) > 0) {
                // Insert batch untuk performa lebih baik
                $batchSize = 100;
                $batches = array_chunk($allResults, $batchSize);
                
                foreach ($batches as $batch) {
                    $this->db->insert_batch('fisik_template_map', $batch);
                }
            }

            $this->db->trans_commit();

            $result = array(
                "message" => "Fisik template map generated successfully",
                "total_records" => $totalProcessed,
                "total_templates" => count($templates)
            );
            $this->sys_ok($result);
            exit;

        } catch (Exception $e) {
            $this->db->trans_rollback();
            $this->sys_error("Error: " . $e->getMessage());
            exit;
        }
    }

    /**
     * STEP 4b: Generate fisik template map untuk template tertentu saja
     * -------------------------------------------------------------------
     * Endpoint: POST /generatedata/generate_fisik_template_map_by_code
     * Request : { "FisikTemplateCode": "RI00" }
     * Response: { message: "...", template_code: "RI00", total_records: 9 }
     * 
     * Proses:
     * 1. Ambil satu template berdasarkan code (harus aktif)
     * 2. Extract semua id_code, label, dan hierarchy dari JSON
     * 3. DELETE hanya data template ini saja dari tabel
     * 4. Insert data baru untuk template ini
     * 
     * Kapan digunakan:
     * - Update satu template saja (tidak perlu regenerate semua)
     * - Ada perubahan di JSON satu template tertentu
     * - Testing/debugging untuk satu template
     * 
     * Keuntungan:
     * - Lebih cepat (hanya proses satu template)
     * - Tidak affect data template lain
     * - Ideal untuk maintenance/update parsial
     * 
     * Note: Hanya hapus & update data template yang dipilih saja
     */
    function generate_fisik_template_map_by_code()
    {
        $prm = $this->sys_input;
        $template_code = isset($prm['FisikTemplateCode']) ? $prm['FisikTemplateCode'] : '';

        if ($template_code == '') {
            $this->sys_error("FisikTemplateCode is required");
            exit;
        }

        // Cek apakah tabel sudah ada
        if (!$this->db->table_exists('fisik_template_map')) {
            $this->sys_error("Table fisik_template_map does not exist. Please create it first.");
            exit;
        }

        $this->db->trans_begin();

        try {
            // Ambil data template berdasarkan code
            $templates = $this->get_active_fisik_templates(null, $template_code);
            
            if ($templates === false) {
                $this->db->trans_rollback();
                $this->sys_error_db("select fisik_template", $this->db);
                exit;
            }

            if (count($templates) == 0) {
                $this->db->trans_rollback();
                $this->sys_error("Template with code '{$template_code}' not found or inactive");
                exit;
            }

            $allResults = array();
            $totalProcessed = 0;

            foreach ($templates as $row) {
                $templateInfo = array(
                    'FisikTemplateID' => $row['FisikTemplateID'],
                    'FisikTemplateCode' => $row['FisikTemplateCode'],
                    'FisikTemplateType' => $row['FisikTemplateType']
                );
                
                // Parse JSON
                $jsonData = json_decode($row['FisikTemplateJSON'], true);
                
                if ($jsonData === null) {
                    $this->db->trans_rollback();
                    $this->sys_error("Failed to parse JSON for template {$template_code}");
                    exit;
                }
                
                $results = array();
                $this->extract_data_from_json($jsonData, $templateInfo, $results);
                
                $allResults = array_merge($allResults, $results);
                $totalProcessed += count($results);
            }

            // Hapus data lama untuk template ini
            $this->db->where('FisikTemplateCode', $template_code);
            $this->db->delete('fisik_template_map');

            // Insert data baru
            if (count($allResults) > 0) {
                // Insert batch untuk performa lebih baik
                $batchSize = 100;
                $batches = array_chunk($allResults, $batchSize);
                
                foreach ($batches as $batch) {
                    $this->db->insert_batch('fisik_template_map', $batch);
                }
            }

            $this->db->trans_commit();

            $result = array(
                "message" => "Fisik template map generated successfully for {$template_code}",
                "template_code" => $template_code,
                "total_records" => $totalProcessed
            );
            $this->sys_ok($result);
            exit;

        } catch (Exception $e) {
            $this->db->trans_rollback();
            $this->sys_error("Error: " . $e->getMessage());
            exit;
        }
    }

    /**
     * STEP 1: List semua fisik template yang aktif
     * ----------------------------------------------
     * Endpoint: POST /generatedata/list_active_fisik_templates
     * Request : No parameters
     * Response: { message: "...", total: 9, data: [...] }
     * 
     * Fungsi:
     * - Menampilkan list semua template dengan FisikTemplateIsActive = 'Y'
     * - Tidak termasuk FisikTemplateJSON (terlalu besar)
     * - Info: ID, Code, Type, Title, TableName
     * 
     * Kapan digunakan:
     * - Lihat template apa saja yang aktif
     * - Get list template code untuk parameter fungsi lain
     * - Monitoring template yang available
     */
    function list_active_fisik_templates()
    {
        $templates = $this->get_active_fisik_templates();
        
        if ($templates === false) {
            $this->sys_error_db("select fisik_template", $this->db);
            exit;
        }

        // Hapus FisikTemplateJSON dari response (terlalu besar)
        foreach ($templates as &$template) {
            unset($template['FisikTemplateJSON']);
        }

        $result = array(
            "message" => "Active templates retrieved successfully",
            "total" => count($templates),
            "data" => $templates
        );
        $this->sys_ok($result);
        exit;
    }

    /**
     * STEP 2: Get detail satu fisik template berdasarkan code
     * ---------------------------------------------------------
     * Endpoint: POST /generatedata/get_fisik_template_detail
     * Request : { "FisikTemplateCode": "RI00" }
     * Response: { FisikTemplateJSON_Parsed: {...}, ... }
     * 
     * Fungsi:
     * - Menampilkan detail lengkap satu template
     * - Termasuk FisikTemplateJSON yang sudah di-parse
     * - Untuk melihat struktur JSON sebelum generate mapping
     * 
     * Kapan digunakan:
     * - Debug/inspect struktur JSON template
     * - Validasi JSON sebelum generate
     * - Melihat detail template tertentu
     */
    function get_fisik_template_detail()
    {
        $prm = $this->sys_input;
        $template_code = isset($prm['FisikTemplateCode']) ? $prm['FisikTemplateCode'] : '';

        if ($template_code == '') {
            $this->sys_error("FisikTemplateCode is required");
            exit;
        }

        $templates = $this->get_active_fisik_templates(null, $template_code);
        
        if ($templates === false) {
            $this->sys_error_db("select fisik_template", $this->db);
            exit;
        }

        if (count($templates) == 0) {
            $this->sys_error("Template with code '{$template_code}' not found or inactive");
            exit;
        }

        $template = $templates[0];
        
        // Parse JSON untuk ditampilkan
        $template['FisikTemplateJSON_Parsed'] = json_decode($template['FisikTemplateJSON'], true);
        
        $result = $template;
        $this->sys_ok($result);
        exit;
    }

    /**
     * STEP 5a: Get fisik template map data
     * -------------------------------------
     * Endpoint: POST /generatedata/get_fisik_template_map
     * Request : { "FisikTemplateCode": "RI00", "level": 2 } (opsional)
     * Response: Array of mapping data
     * 
     * Filter opsional:
     * - FisikTemplateCode : Filter berdasarkan template tertentu
     * - level             : Filter berdasarkan level (1, 2, 3, 4, 5)
     * 
     * Contoh request:
     * 1. Semua data       : {}
     * 2. Template RI00    : { "FisikTemplateCode": "RI00" }
     * 3. Level 2 saja     : { "level": 2 }
     * 4. RI00 level 2     : { "FisikTemplateCode": "RI00", "level": 2 }
     * 
     * Kapan digunakan:
     * - Query data mapping untuk ditampilkan
     * - Get list items untuk satu template
     * - Filter berdasarkan level hierarchy
     */
    function get_fisik_template_map()
    {
        $prm = $this->sys_input;
        $template_code = isset($prm['FisikTemplateCode']) ? $prm['FisikTemplateCode'] : '';
        $level = isset($prm['level']) ? $prm['level'] : '';

        $sql = "SELECT * FROM fisik_template_map WHERE 1=1";
        $params = array();

        if ($template_code != '') {
            $sql .= " AND FisikTemplateCode = ?";
            $params[] = $template_code;
        }

        if ($level != '') {
            $sql .= " AND level = ?";
            $params[] = $level;
        }

        $sql .= " ORDER BY MapID";

        $query = $this->db->query($sql, $params);
        
        if(!$query){
            $this->sys_error_db("select fisik_template_map", $this->db);
            exit;
        }

        $result = $query->result_array();
        $this->sys_ok($result);
        exit;
    }

    /**
     * STEP 5b: Search fisik template map by id_code or label
     * --------------------------------------------------------
     * Endpoint: POST /generatedata/search_fisik_template_map
     * Request : { "keyword": "perut" }
     * Response: Array of matching records
     * 
     * Fungsi:
     * - Search berdasarkan id_code atau label (LIKE %keyword%)
     * - Case insensitive search
     * - Return semua matching records dengan full info
     * 
     * Contoh penggunaan:
     * 1. Cari "perut"     : Find semua item dengan id_code/label mengandung "perut"
     * 2. Cari "perut_14"  : Find item dengan id_code perut_14*
     * 3. Cari "Hernia"    : Find item dengan label mengandung "Hernia"
     * 
     * Kapan digunakan:
     * - Cari item tertentu berdasarkan keyword
     * - Lookup id_code untuk mapping
     * - Cari label untuk matching data
     * 
     * Note: Keyword wajib diisi
     */
    function search_fisik_template_map()
    {
        $prm = $this->sys_input;
        $keyword = isset($prm['keyword']) ? $prm['keyword'] : '';

        if ($keyword == '') {
            $this->sys_error("Keyword is required");
            exit;
        }

        $sql = "SELECT * FROM fisik_template_map 
                WHERE id_code LIKE ? OR label LIKE ?
                ORDER BY FisikTemplateCode, level, MapID";
        
        $query = $this->db->query($sql, ["%{$keyword}%", "%{$keyword}%"]);
        
        if(!$query){
            $this->sys_error_db("search fisik_template_map", $this->db);
            exit;
        }

        $result = $query->result_array();
        $this->sys_ok($result);
        exit;
    }

    function generate_result_mcu_lab(){
        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];

        $sql = "SELECT *
                FROM t_orderheader
                WHERE
                T_OrderheaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y'";
        $query = $this->db->query($sql, [$mgm_mcuid]);
        if(!$query){
            $this->sys_error_db("select t_orderheader", $this->db);
            exit;
        }
        $result = $query->result_array();
        if(count($result) == 0){
            $this->sys_error("No data found");
            exit;
        }

        foreach ($result as $row) {
            $this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
        }

        $this->sys_ok($result);
        exit;
    }

    function generate_result_mcu_nonlab(){
        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];

        $this->load->library('mcuresult');
        
        $sql = "SELECT So_ResultEntryID, So_ResultEntryT_OrderHeaderID, So_ResultEntryT_OrderDetailID
                FROM so_resultentry
                JOIN t_orderheader ON So_ResultEntryT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y' AND
                    T_OrderHeaderMgm_McuID = ? AND So_ResultEntryT_OrderHeaderID = T_OrderHeaderID
                JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailIsActive = 'Y' AND
                    So_ResultEntryT_OrderDetailID = T_OrderDetailID AND T_OrderDetailIsActive = 'Y'
                JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_OrderDetailT_TestID AND Group_ResultDetailIsActive = 'Y'
                JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultID NOT IN (35,27)
                WHERE
                So_ResultEntryIsActive = 'Y' AND So_ResultEntryStatus <> 'NEW'
                GROUP BY So_ResultEntryT_OrderHeaderID, So_ResultEntryT_OrderDetailID
                ";
        $query = $this->db->query($sql, [$mgm_mcuid]);
        if(!$query){
            $this->sys_error_db("select t_orderheader", $this->db);
            exit;
        }
        $result = $query->result_array();
        if(count($result) == 0){
            $this->sys_error("No data found");
            exit;
        }

        foreach ($result as $row) {
            //$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
            $this->mcuresult->generate_result_nonlab($row['So_ResultEntryID']);
        }

        $this->sys_ok($result);
        exit;
    }

    function generate_result_mcu_kenal_warna(){
        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];

        $this->load->library('mcuresult');
        
        $sql = "SELECT T_OrderHeaderID, T_OrderHeaderLabNumber, T_OrderHeaderMgm_McuID as Mgm_McuID
                FROM t_orderheader
                WHERE T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y'
                ";
        $query = $this->db->query($sql, [$mgm_mcuid]);
        if(!$query){
            $this->sys_error_db("select t_orderheader", $this->db);
            exit;
        }
        $result = $query->result_array();
        if(count($result) == 0){
            $this->sys_error("No data found");
            exit;
        }

        foreach ($result as $row) {
            //$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
            $this->mcuresult->generate_kenal_warna($row);
        }

        $this->sys_ok($result);
        exit;
    }

    function generate_result_mcu_visus(){
        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];

        $this->load->library('mcuresult');
        
        $sql = "SELECT T_OrderHeaderID, T_OrderHeaderLabNumber, T_OrderHeaderMgm_McuID as Mgm_McuID
                FROM t_orderheader
                WHERE T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y'
         
                ";
        $query = $this->db->query($sql, [$mgm_mcuid]);
        if(!$query){
            $this->sys_error_db("select t_orderheader", $this->db);
            exit;
        }
        $result = $query->result_array();
        if(count($result) == 0){
            $this->sys_error("No data found");
            exit;
        }

        foreach ($result as $row) {
            //$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
            $this->mcuresult->generate_visus($row);
        }

        $this->sys_ok($result);
        exit;
    }

    function generate_result_mcu_status_gizi(){
        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];

        $this->load->library('mcuresult');
        
        $sql = "SELECT T_OrderHeaderID, T_OrderHeaderLabNumber, T_OrderHeaderMgm_McuID as Mgm_McuID
        FROM t_orderheader
        JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailIsActive = 'Y' AND
        T_OrderDetailT_TestCode = '40110901'
        WHERE T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y'
        GROUP BY T_OrderHeaderID
        LIMIT 1";
        $query = $this->db->query($sql, [$mgm_mcuid]);
        if(!$query){
            $this->sys_error_db("select t_orderheader", $this->db);
            exit;
        }
        $result = $query->result_array();
        if(count($result) == 0){
            $this->sys_error("No data found");
            exit;
        }

        foreach ($result as $row) {
            //$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
            $this->mcuresult->generate_status_gizi($row);
        }

        $this->sys_ok($result);
        exit;
    }

    function generate_result_mcu_bodyfatmonitoring(){
        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];

        $this->load->library('mcuresult');
        
        $sql = "SELECT T_OrderHeaderID, T_OrderHeaderLabNumber, T_OrderHeaderMgm_McuID as Mgm_McuID
                FROM t_orderheader
                JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailIsActive = 'Y' AND
                T_OrderDetailT_TestCode = '40110902'
                WHERE T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y'
                GROUP BY T_OrderHeaderID
                LIMIT 1
                ";
        $query = $this->db->query($sql, [$mgm_mcuid]);
        if(!$query){
            $this->sys_error_db("select t_orderheader", $this->db);
            exit;
        }
        $result = $query->result_array();
        if(count($result) == 0){
            $this->sys_error("No data found");
            exit;
        }

        foreach ($result as $row) {
            //$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
            $this->mcuresult->generate_bodyfatmonitoring($row);
        }

        $this->sys_ok($result);
        exit;
    }

    function generate_result_mcu_fisik(){
        $prm = $this->sys_input;
        $mgm_mcuid = $prm['Mgm_McuID'];

        $this->load->library('mcuresult');
        
        $sql = "SELECT So_ResultEntryID, So_ResultEntryT_OrderHeaderID, So_ResultEntryT_OrderDetailID
                FROM so_resultentry
                JOIN t_orderheader ON So_ResultEntryT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y' AND
                    T_OrderHeaderMgm_McuID = ? AND So_ResultEntryT_OrderHeaderID = T_OrderHeaderID
                JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailIsActive = 'Y' AND
                    So_ResultEntryT_OrderDetailID = T_OrderDetailID AND T_OrderDetailIsActive = 'Y'
                JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_OrderDetailT_TestID AND Group_ResultDetailIsActive = 'Y'
                JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID
                WHERE
                So_ResultEntryIsActive = 'Y' AND So_ResultEntryStatus <> 'NEW' AND So_ResultEntryNonlab_TemplateID = 27
                GROUP BY So_ResultEntryT_OrderHeaderID, So_ResultEntryT_OrderDetailID
                ";
        $query = $this->db->query($sql, [$mgm_mcuid]);
        if(!$query){
            $this->sys_error_db("select t_orderheader", $this->db);
            exit;
        }
        $result = $query->result_array();
        if(count($result) == 0){
            $this->sys_error("No data found");
            exit;
        }

        foreach ($result as $row) {
            //$this->generate_result_lab($row['T_OrderHeaderID'], $row['T_OrderHeaderLabNumber']);
            $this->mcuresult->get_data_fisik_resultentry($row['So_ResultEntryID']);
        }

        $this->sys_ok($result);
        exit;
    }

    
    function generate_result_lab($T_OrderHeaderID = 0,$T_OrderHeaderLabNumber = '')
    {
    

        $this->db_log->trans_begin();
     
        $sql = "SELECT T_OrderHeaderMgm_McuID as Mgm_McuID, 
                T_OrderHeaderID,
                T_OrderHeaderLabNumber,
                T_OrderDetailID as ReffID,
                Group_ResultID,
                Group_ResultName,
                T_TestCode,
                T_TestName,
                IFNULL(T_OrderDetailResult,'') as Result,
                IFNULL(T_OrderDetailNat_UnitName,'') as UnitName,
                IFNULL(T_OrderDetailNormalValueNote,'') as refference
                FROM t_orderdetail
                JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND 
                    ( T_OrderHeaderID = ? OR T_OrderHeaderLabNumber = ?)
                JOIN t_test ON T_OrderDetailT_TestID = T_TestID
                JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_TestID AND  Group_ResultDetailIsactive = 'Y'
                JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultIsActive = 'Y' AND Group_ResultResumeMcu = 'LAB'
                WHERE
                T_OrderDetailIsActive = 'Y' 
                ORDER BY T_OrderDetailT_TestSasCode ASC";
        $query = $this->db->query($sql, [$T_OrderHeaderID, $T_OrderHeaderLabNumber]);

        $result = $query->result_array();
        if(count($result) == 0){
            $results = array("message" => "No data found", "data" => array());
            $this->sys_ok($results);
            exit;
        }

        foreach ($result as $row) {
            $sql = "SELECT * FROM mcu_result_all WHERE 
            Mcu_ResultAllMgm_McuID = ? AND
            Mcu_ResultAllT_OrderHeaderID = ? AND
            Mcu_ResultAllT_OrderHeaderLabNumber = ? AND
            Mcu_ResultAllReffID = ? AND
            Mcu_ResultAllT_TestCode = ? AND
            Mcu_ResultAllT_TestName = ?";
            $query = $this->db_log->query($sql, [
                $row['Mgm_McuID'],
                $row['T_OrderHeaderID'],
                $row['T_OrderHeaderLabNumber'],
                $row['ReffID'],
                $row['T_TestCode'],
                $row['T_TestName']
            ]);
            
            $exist_result = $query->result_array();
            if(count($exist_result) > 0){
                foreach ($exist_result as $key => $row_exist) {
                    $sql = "DELETE FROM mcu_result_all WHERE 
                        Mcu_ResultAllID = ?";
                    $query = $this->db_log->query($sql, [$row_exist['Mcu_ResultAllID']]);
                    if(!$query){
                        $this->db_log->trans_rollback();
                        $this->sys_error_db("delete mcu_result_".$row_exist['Mcu_ResultAllT_TestCode'], $this->db_log);
                        exit;
                    }
                }
            }

            $sql = "INSERT INTO mcu_result_all (
                        Mcu_ResultAllMgm_McuID,
                        Mcu_ResultAllT_OrderHeaderID,
                        Mcu_ResultAllT_OrderHeaderLabNumber,
                        Mcu_ResultAllReffID,
                        Mcu_ResultAllT_TestCode,
                        Mcu_ResultAllT_TestName,
                        Mcu_ResultAllResult,
                        Mcu_ResultAllUnit,
                        Mcu_ResultAllRefference,
                        Mcu_ResultAllCreated
                    )
                    VALUES(?,?,?,?,?,?,?,?,?,NOW())";
            $query = $this->db_log->query($sql, [
                $row['Mgm_McuID'],
                $row['T_OrderHeaderID'],
                $row['T_OrderHeaderLabNumber'],
                $row['ReffID'],
                $row['T_TestCode'],
                $row['T_TestName'],
                $row['Result'],
                $row['UnitName'],
                $row['refference']
            ]);
            
            if(!$query){
                $this->db_log->trans_rollback();
                $this->sys_error_db("insert mcu_result_".$row['T_TestCode'], $this->db_log);
                exit;
            }
            
        }


        $this->db_log->trans_commit();

        $results = array("message" => "Data generated successfully", "data" => $result);
        return $results;

    }

    function generate_sqlite_by_mgm_mcuid()
    {
        // Get MGM_MCUID from input
        $prm = $this->sys_input;
        $mgm_mcuid = isset($prm['Mgm_McuID']) ? $prm['Mgm_McuID'] : 0;

        if (empty($mgm_mcuid)) {
            $this->sys_error("Mgm_McuID is required");
            exit;
        }

        // Get table name from mcu_number
        $sql = "SELECT * FROM mcu_number WHERE Mcu_NumberMgm_McuID = ? AND Mcu_NumberIsActive = 'Y' LIMIT 1";
        $query = $this->db_corporate->query($sql, [$mgm_mcuid]);
        
        if ($query->num_rows() == 0) {
            $this->sys_error("MCU Number not found for Mgm_McuID: " . $mgm_mcuid);
            exit;
        }

        $mcu_data = $query->row_array();
        $table_suffix = $mcu_data['Mcu_NumberTableName'];
        $mcu_number = $mcu_data['Mcu_NumberMgm_McuNumber'];
        
        // Define output file path
        $output_dir = FCPATH; // Root directory
        $output_file = $output_dir . 'mcu_' . $table_suffix . '.sqlite';
        
        // Remove existing file if exists
        if (file_exists($output_file)) {
            unlink($output_file);
        }

        try {
            // Create SQLite database
            $sqlite = new SQLite3($output_file);
            $sqlite->exec('PRAGMA foreign_keys = OFF;');
            $sqlite->exec('BEGIN TRANSACTION;');

            // Table names to export
            $tables_to_export = [
                'kelainan_details',
                'kelainan_summary',
                'mcu_result_all'
            ];

            foreach ($tables_to_export as $table_name) {
                // Check if table exists
                $check_sql = "SHOW TABLES LIKE ?";
                $check_query = $this->db_corporate->query($check_sql, [$table_name]);
                
                if ($check_query->num_rows() == 0) {
                    continue; // Skip if table doesn't exist
                }

                // Get table structure
                $structure_sql = "SHOW CREATE TABLE `{$table_name}`";
                $structure_query = $this->db_corporate->query($structure_sql);
                $structure_row = $structure_query->row_array();
                $create_table_sql = $structure_row['Create Table'];
                
                // Convert MySQL to SQLite syntax
                $create_table_sql = $this->convert_mysql_to_sqlite($create_table_sql, $table_name);
                
                // Create table in SQLite
                $sqlite->exec($create_table_sql);

                // Get data from MySQL
                $data_sql = "SELECT * FROM `{$table_name}`";
                $data_query = $this->db_corporate->query($data_sql);
                $rows = $data_query->result_array();

                if (count($rows) > 0) {
                    // Prepare insert statement
                    $columns = array_keys($rows[0]);
                    $placeholders = implode(',', array_fill(0, count($columns), '?'));
                    $insert_sql = "INSERT INTO {$table_name} (" . implode(',', $columns) . ") VALUES ({$placeholders})";
                    
                    $stmt = $sqlite->prepare($insert_sql);
                    
                    foreach ($rows as $row) {
                        $i = 1;
                        foreach ($row as $value) {
                            $stmt->bindValue($i++, $value);
                        }
                        $stmt->execute();
                        $stmt->reset();
                    }
                    $stmt->close();
                }
            }

            // Also include mcu_number table with current record only
            // Get mcu_number table structure from MySQL
            $mcu_structure_sql = "SHOW CREATE TABLE mcu_number";
            $mcu_structure_query = $this->db_corporate->query($mcu_structure_sql);
            $mcu_structure_row = $mcu_structure_query->row_array();
            $mcu_create_table_sql = $mcu_structure_row['Create Table'];
            
            // Convert MySQL to SQLite syntax
            $mcu_create_table_sql = $this->convert_mysql_to_sqlite($mcu_create_table_sql, 'mcu_number');
            $sqlite->exec($mcu_create_table_sql);
            
            // Insert current mcu_number record (only the one matching Mgm_McuID)
            $columns = array_keys($mcu_data);
            $placeholders = implode(',', array_fill(0, count($columns), '?'));
            $mcu_insert_sql = "INSERT INTO mcu_number (" . implode(',', $columns) . ") VALUES ({$placeholders})";
            
            $stmt = $sqlite->prepare($mcu_insert_sql);
            $i = 1;
            foreach ($mcu_data as $value) {
                $stmt->bindValue($i++, $value);
            }
            $stmt->execute();
            $stmt->close();

            $sqlite->exec('COMMIT;');
            $sqlite->close();

            // Return success with file path
            $all_tables_exported = array_merge($tables_to_export, ['mcu_number']);
            $results = [
                "message" => "SQLite file generated successfully",
                "file" => basename($output_file),
                "path" => $output_file,
                "table_suffix" => $table_suffix,
                "mcu_number" => $mcu_number,
                "mgm_mcuid" => $mgm_mcuid,
                "tables_exported" => $all_tables_exported,
                "note" => "Table mcu_number only contains the record for Mgm_McuID: " . $mgm_mcuid
            ];
            
            $this->sys_ok($results);
            exit;

        } catch (Exception $e) {
            $this->sys_error("Error generating SQLite: " . $e->getMessage());
            exit;
        }
    }

    function convert_mysql_to_sqlite($create_sql, $table_name)
    {
        // Remove MySQL specific syntax
        $create_sql = preg_replace('/ENGINE=\w+/', '', $create_sql);
        $create_sql = preg_replace('/DEFAULT CHARSET=\w+/', '', $create_sql);
        $create_sql = preg_replace('/AUTO_INCREMENT=\d+/', '', $create_sql);
        $create_sql = preg_replace('/COLLATE=\w+/', '', $create_sql);
        
        // Convert data types
        $create_sql = preg_replace('/INT\(\d+\)/', 'INTEGER', $create_sql);
        $create_sql = preg_replace('/TINYINT\(\d+\)/', 'INTEGER', $create_sql);
        $create_sql = preg_replace('/SMALLINT\(\d+\)/', 'INTEGER', $create_sql);
        $create_sql = preg_replace('/MEDIUMINT\(\d+\)/', 'INTEGER', $create_sql);
        $create_sql = preg_replace('/BIGINT\(\d+\)/', 'INTEGER', $create_sql);
        $create_sql = preg_replace('/VARCHAR\(\d+\)/', 'TEXT', $create_sql);
        $create_sql = preg_replace('/DATETIME/', 'DATETIME', $create_sql);
        $create_sql = preg_replace('/TIMESTAMP/', 'DATETIME', $create_sql);
        $create_sql = preg_replace('/DATE/', 'DATE', $create_sql);
        $create_sql = preg_replace('/TEXT/', 'TEXT', $create_sql);
        $create_sql = preg_replace('/LONGTEXT/', 'TEXT', $create_sql);
        $create_sql = preg_replace('/MEDIUMTEXT/', 'TEXT', $create_sql);
        
        // Remove backticks
        $create_sql = str_replace('`', '', $create_sql);
        
        // Handle AUTO_INCREMENT
        $create_sql = preg_replace('/\s+AUTO_INCREMENT\s*/i', ' AUTOINCREMENT ', $create_sql);
        
        // Clean up
        $create_sql = preg_replace('/,\s*\)/', ')', $create_sql);
        $create_sql = preg_replace('/\s+/', ' ', $create_sql);
        
        return $create_sql;
    }

  
}