<?php


/**
 * Created by PhpStorm.
 * User: youse
 * Date: 18/07/17
 * Time: 8:33 PM
 */


namespace common\component\helpers;


use common\models\AutoExport;
use common\models\Action;
use common\models\ActionAnswer;
use common\models\Answer;
use common\models\AnswerSource;
use common\models\AutoGenerate;
use common\models\Bacteria;
use common\models\Badge;
use common\models\BadgeCategory;
use common\models\Biomarker;
use common\models\BiomarkerDefinition;
use common\models\BiomarkerGroup;
use common\models\BiomarkerRegion;
use common\models\BiomarkerScore;
use common\models\BiomarkerSource;
use common\models\BiomarkerSubProject;
use common\models\BiomarkerUnit;
use common\models\Branch;
use common\models\CertaMap;
use common\models\Concept;
use common\models\ConceptSource;
use common\models\Country;
use common\models\CountryBasedQuestion;
use common\models\Definition;
use common\models\DefinitionSource;
use common\models\Field;
use common\models\HomeScreenImage;
use common\models\Insight;
use common\models\InsightDefinition;
use common\models\InsightSource;
use common\models\InsightSubProject;
use common\models\InsightType;
use common\models\Lab;
use common\models\Language;
use common\models\LanguageTranslate;
use common\models\LingualReadings;
use common\models\LingualReadingsSynonyms;
use common\models\MedicalReviewer;
use common\models\Note;
use common\models\Panel;
use common\models\PregnancyDeliveryPreparation;
use common\models\PregnancyDeliveryPreparationSource;
use common\models\PregnancyInfo;
use common\models\PregnancySource;
use common\models\PregnancyVisit;
use common\models\PregnancyVisitTest;
use common\models\ProcedureAnswer;
use common\models\Question;
use common\models\QuestionSubProject;
use common\models\QuestionType;
use common\models\Quote;
use common\models\Region;
use common\models\Sample;
use common\models\Setting;
use common\models\Tag;
use common\models\Tooltip;
use common\models\Unit;
use common\models\UnitSynonym;
use common\models\SmartAlertRule;
use common\models\SmartAlertRulePart;
use SQLite3;
use Yii;
use yii\helpers\ArrayHelper;
use yii\web\User;


class CerboHelper
{
    public static $encryption = true;
    public static $link = "https://centro.medicus.ai/frontend/web/";

    public static function encryptString($string)
    {

        if (self::$encryption) {

            if (strlen($string) == 0) return "";

            if ($string == "id") return $string;

            $key = "NAGI9P95XW8WKRCLY8YVFQ3P2SC6KAJU";

            return utf8_encode(MedicusHelper::xorEncrypt($string, $key));
        }

        return $string;
    }

    public static function decryptString($string)
    {

        if (empty($string)) return "";

        if ($string == "id") return $string;

        $key = "NAGI9P95XW8WKRCLY8YVFQ3P2SC6KAJU";

        return MedicusHelper::xorDecrypt(utf8_decode($string), $key);

    }

    public static function encryptColumnNames($str)
    {
        $columnsMap = [];
        $encryptedColumns = '';
        $columns = explode(',', $str);
        foreach ($columns as $column) {
            $encryptedColumn = self::encryptString($column);
            $encryptedColumns .= '`' . $encryptedColumn . '`,';
            $columnsMap[$column] = $encryptedColumn;
        }
        $encryptedColumns = rtrim($encryptedColumns, ',');
        return $encryptedColumns;
    }

    public static function mobileSQLAdapter($value, $type = 0)
    {
        if ($type === -1)
            $type = 'text not null';
        if ($type === 0)
            $type = 'text';
        if ($type === 1)
            $type = 'integer';
        if ($type === 2)
            $type = 'icon';

        $_type_string_not_nullable = 'text not null';
        $_type_string_nullable = 'text';
        $_type_int_not_null = 'integer not null';
        $_type_int = 'integer';
        $_type_icon = 'icon';
        $double = 'double';
        $real = 'real';
        $real_not_null = 'real not null';
        $_type_icon_not_null = -2;
        switch ($type) {
            case $_type_string_nullable:
                if ($value === NULL || $value == "")
                    return "NULL";
                else {
                    if (self::$encryption === false) {
                        $str = htmlspecialchars_decode($value, ENT_QUOTES);
                        $str = str_replace("'", "''", $str);
                        $str = str_replace("&#39;", "&#39;&#39;", $str);
                        $str = str_replace("\\", "\\\\", $str);
//                        $str = preg_replace("/\n/", "\\n", $str);
//                        $str = preg_replace("/\r/", "\\r", $str);
                        return "'" . self::encryptString($str) . "'";
                    } else
                        return "'" . self::encryptString($value) . "'";
                }
                break;
            case $_type_string_not_nullable:
                if ($value === NULL || $value == "") {
                    return "''";
                } else {
                    if (self::$encryption === false) {
                        $str = $value;
                        $str = str_replace("'", "''", $str);
                        $str = str_replace("&#39;", "&#39;&#39;", $str);
                        $str = str_replace("\\", "\\\\", $str);
//                        $str = preg_replace("/\n/", "\\n", $str);
//                        $str = preg_replace("/\r/", "\\r", $str);
                        return "'" . self::encryptString($str) . "'";
                    } else
                        return "'" . self::encryptString($value) . "'";
                }
                break;
            case $_type_int:
                if (is_numeric($value) && $value == 0) {
                    return 0;
                } elseif ($value === NULL || $value === "" || $value == "") {
                    return "NULL";
                } else {
                    return $value;
                }
                break;
            case $_type_int_not_null:
                if (is_numeric($value) && $value == 0) {
                    return 0;
                } elseif ($value === NULL || $value === "" || $value == "") {
                    return "NULL";
                } else {
                    return $value;
                }
                break;
            case $double:
                if (is_numeric($value) && $value == 0) {
                    return 0;
                } elseif ($value === NULL || $value === "" || $value == "") {
                    return "NULL";
                } else {
                    return $value;
                }
                break;
            case $real:
                if (is_numeric($value) && $value == 0) {
                    return 0;
                } elseif ($value === NULL || $value === "" || $value == "") {
                    return "NULL";
                } else {
                    return $value;
                }
                break;
            case $real_not_null:
                if (is_numeric($value) && $value == 0) {
                    return 0;
                } elseif ($value === NULL || $value === "" || $value == "") {
                    return "NULL";
                } else {
                    return $value;
                }
                break;
            case $_type_icon:
                if ($value === NULL || $value === '')
                    return "''";
                else {
                    $str = self::$link . $value;
                    $str = str_replace("http://localhost", "https://centro.medicus.ai", $str);
                    $str = str_replace("\\", "/", $str);
                    return "'" . self::encryptString($str) . "'";
                }
                break;
            case $_type_icon_not_null:
                if ($value === NULL || $value === '')
                    return "''";
                else {
                    $str = self::$link . $value;
                    $str = str_replace("http://localhost", "https://centro.medicus.ai", $str);
                    $str = str_replace("\\", "/", $str);
                    return "'" . self::encryptString($str) . "'";
                }
                break;
        }
        return $value;
    }

    public static function mobileTagSQL()
    {
        $models = Tag::find()->all();
        $rows = [];
        $models_sql = [];
        $models_total_count = count($models);
        if ($models_total_count > 0) {
            $models_sql[] = "INSERT INTO `" . self::encryptString("Tag") . "` (" . self::encryptColumnNames("id,name") . ") VALUES";
            foreach ($models as $model) {
                $rows[] = "(" .
                    self::mobileSQLAdapter($model->id, 1) . "," .
                    self::mobileSQLAdapter($model->name, -1) . ")";
            }
            $models_sql[] = implode(",", $rows);
            $models_sql[] = ";\n";
        }
        return implode("", $models_sql);
    }

    public static function mobileLanguageTranslateSourceSQL($language, $request, $lab_id = null)
    {
        $currentLab = ($lab_id)?Lab::findOne($lab_id):Lab::findOne(1);
        $lang_male = "";
        $lang_female = "";
        if (array_search($language->language_id, explode(',', $request)) === false)
            $models = [];
        else
            $models = LanguageTranslate::find()
                ->where(["language" => $language->language_id])
                ->joinWith('languageSource')->orderBy('language_source.category')
                ->all();
        $rows = [];
        $models_sql = [];
        $languageCountry = str_replace([' (France)', ' (China)', ' (Hong Kong)'], '', $language->name_ascii);
        $languageCountry = str_replace(' (', '_', $languageCountry);
        $languageCountry = str_replace(")", '', $languageCountry);
        $languageCountry = str_replace('Simplified Chinese', 'Chinese_China', $languageCountry);
        $languageCountry = str_replace('Traditional Chinese', 'Traditional_China', $languageCountry);
        $models_sql[] = "DELETE FROM `" . self::encryptString($languageCountry) . "` WHERE 1;\n";
        $models_total_count = count($models);
        if ($models_total_count > 0) {
            $insert_statement = "INSERT INTO `" . self::encryptString($languageCountry) . "` (" . self::encryptColumnNames("id,key,localizedString,type,gender") . ") VALUES\n";
            $models_sql[] = $insert_statement;
            $regions = Region::find()->where(["leaf" => 1])->all();
            $inserted = [];
            $inserted_es_la_male = [];
            $inserted_es_la_female = [];
            $i = 1;
            foreach ($regions as $region) {
                Biomarker::$static_region_id = $region->id;
                BiomarkerRegion::$static_region_id = $region->id;
                $biomarkers_variables_en = VariableHelper::getAllBiomarkersVariable($region->id, "en");
                $biomarkers_variables_ln = VariableHelper::getAllBiomarkersVariable($region->id, $language->language_id);

                foreach ($models as $model) {
                    if (empty($model->languageSource) || empty($model->languageSource->message) || empty($model->languageSource->translation)) {
                        continue;
                    }
                    Yii::$app->language = "en";

                    $key = VariableHelper::replaceText($model->languageSource->message, $biomarkers_variables_en, Yii::$app->language);
                    if ($model->languageSource->category === 'setting')
                        $key = str_replace('Color ', '', $key);

                    Yii::$app->language = $language->language_id;
                    if (in_array($model->languageSource->category . "_" . $key, $inserted)) {
                        continue;
                    }
                    if (count($rows) % 500 == 0 && !empty($rows)) {
                        $models_sql[] = implode(",\n", $rows) . ";\n";
                        $models_sql[] = $insert_statement;
                        $rows = [];
                    }
                    $rows[] = "(" .
                        self::mobileSQLAdapter($i, 1) . ","
                        . self::mobileSQLAdapter($key, -1) . ","
                        . self::mobileSQLAdapter(LanguageHelper::replaceTextAndVariables($model->languageSource->category, $model->languageSource->message, $biomarkers_variables_ln, $language->language_id), -1) . ","
                        . self::mobileSQLAdapter($model->languageSource->category, -1)
                        . "," . "'all'"
                        . ")";
                    $inserted [] = $model->languageSource->category . "_" . $key;
                    $i = $i + 1;
                }
                if($language->language_id == 'es-LA' && $currentLab->is_informal){
                    $male_models = LanguageTranslate::find()->where(['language'=> 'es-LA_male'])
                        ->joinWith('languageSource')->all();
                    foreach ($male_models as $male_model) {
                        if (empty($male_model->languageSource) || empty($male_model->languageSource->message) || empty($male_model->languageSource->translation)) {
                            continue;
                        }
                        Yii::$app->language = "en";
                        $key = VariableHelper::replaceText($male_model->languageSource->message, $biomarkers_variables_en, Yii::$app->language);
                        if ($male_model->languageSource->category === 'setting')
                            $key = str_replace('Color ', '', $key);
                        Yii::$app->language = $language->language_id;
                        if (in_array($male_model->languageSource->category . "_" . $key, $inserted_es_la_male)) {
                            continue;
                        }
                        if (count($rows) % 500 == 0 && !empty($rows)) {
                            $models_sql[] = implode(",\n", $rows) . ";\n";
                            $models_sql[] = $insert_statement;
                            $rows = [];
                        }
                        $rows[] = "(" .
                            self::mobileSQLAdapter(++$i, 1) . ","
                            . self::mobileSQLAdapter($key, -1) . ","
                            . self::mobileSQLAdapter(LanguageHelper::replaceTextAndVariables($male_model->languageSource->category, $male_model->languageSource->message, $biomarkers_variables_ln, 'es-LA_male'), -1) . ","
                            . self::mobileSQLAdapter($male_model->languageSource->category, -1)
                            . "," . "'male'"
                            . ")";
                        $inserted_es_la_male [] = $male_model->languageSource->category . "_" . $key;
                        $i = $i + 1;
                    }
                    $female_models = LanguageTranslate::find()->where(['language'=> 'es-LA_female'])
                        ->joinWith('languageSource')->all();
                    foreach ($female_models as $female_model) {
                        if (empty($female_model->languageSource) || empty($female_model->languageSource->message) || empty($female_model->languageSource->translation)) {
                            continue;
                        }
                        Yii::$app->language = "en";
                        $key = VariableHelper::replaceText($female_model->languageSource->message, $biomarkers_variables_en, Yii::$app->language);
                        if ($female_model->languageSource->category === 'setting')
                            $key = str_replace('Color ', '', $key);
                        Yii::$app->language = $language->language_id;
                        if (in_array($female_model->languageSource->category . "_" . $key, $inserted_es_la_female)) {
                            continue;
                        }
                        if (count($rows) % 500 == 0 && !empty($rows)) {
                            $models_sql[] = implode(",\n", $rows) . ";\n";
                            $models_sql[] = $insert_statement;
                            $rows = [];
                        }
                        $rows[] = "(" .
                            self::mobileSQLAdapter(++$i, 1) . ","
                            . self::mobileSQLAdapter($key, -1) . ","
                            . self::mobileSQLAdapter(LanguageHelper::replaceTextAndVariables($female_model->languageSource->category, $female_model->languageSource->message, $biomarkers_variables_ln, 'es-LA_female'), -1) . ","
                            . self::mobileSQLAdapter($female_model->languageSource->category, -1)
                            . "," . "'female'"
                            . ")";
                        $inserted_es_la_female [] = $female_model->languageSource->category . "_" . $key;
                        $i = $i + 1;
                    }
                }
            }
            $models_sql[] = implode(",\n", $rows);
            $models_sql[] = ";\n";
        }
        return implode("", $models_sql);
    }

    public static function mobileSqliteSequenceSQL($request, $lab_id = null)
    {
        if($lab_id)
            $currentLab = Lab::findOne($lab_id)?:Lab::findOne(1);
        $modelsAr = LanguageTranslate::find()->where(["language" => "ar"])->with('languageSource')->with('language0')->all();
        $models_total_countAR = count($modelsAr);
        $modelsFr = LanguageTranslate::find()->where(["language" => "fr"])->with('languageSource')->with('language0')->all();
        $models_total_countFr = count($modelsFr);
        $modelsDe = LanguageTranslate::find()->where(["language" => "de"])->with('languageSource')->with('language0')->all();
        $models_total_countDe = count($modelsDe);
        $modelsPT = array_search("pt-PT", explode(',', $request)) !== false ?
            LanguageTranslate::find()->where(["language" => "pt-PT"])->with('languageSource')->with('language0')->all() : [];
        $models_total_countPT = count($modelsPT);
        $modelsBR = array_search("pt-BR", explode(',', $request)) !== false ?
            LanguageTranslate::find()->where(["language" => "pt-BR"])->with('languageSource')->with('language0')->all() : [];
        $models_total_countBR = count($modelsBR);
        $modelsCN = LanguageTranslate::find()->where(["language" => "zh-CN"])->with('languageSource')->with('language0')->all();
        $models_total_countCN = count($modelsCN);
        $modelsHK = LanguageTranslate::find()->where(["language" => "zh-HK"])->with('languageSource')->with('language0')->all();
        $models_total_countHK = count($modelsHK);
        $modelsNL = LanguageTranslate::find()->where(["language" => "nl-NL"])->with('languageSource')->with('language0')->all();
        $models_total_countNL = count($modelsNL);
        $modelsTR = LanguageTranslate::find()->where(["language" => "tr-TR"])->with('languageSource')->with('language0')->all();
        $models_total_countTR = count($modelsTR);
        $modelsIT = LanguageTranslate::find()->where(["language" => "it-IT"])->with('languageSource')->with('language0')->all();
        $models_total_countIT = count($modelsIT);
        $modelsLA = LanguageTranslate::find()->where(["language" => "es-LA"])->with('languageSource')->with('language0')->all();
        $models_total_countLA = count($modelsLA);
        if(isset($currentLab) && $currentLab->is_informal == 1) {
            $modelsLAFemale = LanguageTranslate::find()->where(["language" => "es-LA_female"])->with('languageSource')->with('language0')->all();
            $models_total_countLAFemale = count($modelsLAFemale);
            $modelsLAMale = LanguageTranslate::find()->where(["language" => "es-LA_male"])->with('languageSource')->with('language0')->all();
            $models_total_countLAMale = count($modelsLAMale);
        }
        $rows = [];
        $models_sql = [];
        $models_sql[] = "DELETE FROM `" . self::encryptString('Sequence') . "` WHERE 1;\n";
        $models_sql[] = "INSERT INTO `" . self::encryptString('Sequence') . "` (" . self::encryptColumnNames("name,seq") . ") VALUES\n";
        $rows[] = "(" .
            self::mobileSQLAdapter("Arabic") . "," .
            self::mobileSQLAdapter($models_total_countAR, 1) . ")";
        $rows[] = "(" .
            self::mobileSQLAdapter("French") . "," .
            self::mobileSQLAdapter($models_total_countFr, 1) . ")";
        $rows[] = "(" .
            self::mobileSQLAdapter("German") . "," .
            self::mobileSQLAdapter($models_total_countDe, 1) . ")";
        $rows[] = "(" .
            self::mobileSQLAdapter("Portuguese_Portugal") . "," .
            self::mobileSQLAdapter($models_total_countPT, 1) . ")";
        $rows[] = "(" .
            self::mobileSQLAdapter("Portuguese_Brazil") . "," .
            self::mobileSQLAdapter($models_total_countBR, 1) . ")";
        $rows[] = "(" .
            self::mobileSQLAdapter("Chinese_China") . "," .
            self::mobileSQLAdapter($models_total_countCN, 1) . ")";
        $rows[] = "(" .
            self::mobileSQLAdapter("Traditional_China") . "," .
            self::mobileSQLAdapter($models_total_countHK, 1) . ")";
        $rows[] = "(" .
            self::mobileSQLAdapter("Dutch") . "," .
            self::mobileSQLAdapter($models_total_countNL, 1) . ")";
        $rows[] = "(" .
            self::mobileSQLAdapter("Turkish") . "," .
            self::mobileSQLAdapter($models_total_countTR, 1) . ")";
        $rows[] = "(" .
            self::mobileSQLAdapter("Italian") . "," .
            self::mobileSQLAdapter($models_total_countIT, 1) . ")";
        $rows[] = "(" .
            self::mobileSQLAdapter("Spanish") . "," .
            self::mobileSQLAdapter($models_total_countLA, 1) . ")";
        if(isset($currentLab) && $currentLab->is_informal == 1) {
            $rows[] = "(" .
                self::mobileSQLAdapter("Spanish_female") . "," .
                self::mobileSQLAdapter($models_total_countLAFemale, 1) . ")";
            $rows[] = "(" .
                self::mobileSQLAdapter("Spanish_male") . "," .
                self::mobileSQLAdapter($models_total_countLAMale, 1) . ")";
        }
        $models_sql[] = implode(",\n", $rows);
        $models_sql[] = ";\n";

        return implode("", $models_sql);
    }

    public static function addIndexes($tables, $has_is_mdc)
    {
        $createStatement = '';
        if (array_search('Unit', $tables) !== false) {
            $createStatement .= "CREATE INDEX IF NOT EXISTS `" . self::encryptString('idx_Unit_symbol')
                . "` ON `" . self::encryptString('Unit') .
                "` ( `" . self::encryptString('symbol') . "` );";
        }
        if (array_search('Question', $tables) !== false) {
            $createStatement .= "CREATE INDEX IF NOT EXISTS `" . self::encryptString('idx_Question_symbol')
                . "` ON `" . self::encryptString('Question') .
                "` ( `" . self::encryptString('symbol') . "` );";
        }
        if (array_search('Biomarker', $tables) !== false) {
            $createStatement .= "CREATE INDEX IF NOT EXISTS `" . self::encryptString('idx_Biomarker_symbol')
                . "` ON `" . self::encryptString('Biomarker') .
                "` ( `" . self::encryptString('symbol') . "` );";
        }
        if (array_search('Biomarker', $tables) !== false) {
            $createStatement .= "CREATE INDEX IF NOT EXISTS `" . self::encryptString('idx_Answer_symbol')
                . "` ON `" . self::encryptString('Answer') .
                "` ( `" . self::encryptString('symbol') . "` );";
        }
        if (array_search('Condition', $tables) !== false) {
            $createStatement .= "CREATE INDEX IF NOT EXISTS `" . self::encryptString('idx_condition_symbol')
                . "` ON `" . self::encryptString('Condition') .
                "` ( `" . self::encryptString('symbol') . "` );";
        }
        if (array_search('Symptom', $tables) !== false) {
            $createStatement .= "CREATE INDEX IF NOT EXISTS `" . self::encryptString('idx_symptom_symbol')
                . "` ON `" . self::encryptString('Symptom') .
                "` ( `" . self::encryptString('symbol') . "` );";
        }
        if (array_search('Procedure', $tables) !== false) {
            $createStatement .= "CREATE INDEX IF NOT EXISTS `" . self::encryptString('idx_procedure_symbol')
                . "` ON `" . self::encryptString('Procedure') .
                "` ( `" . self::encryptString('symbol') . "` );";
        }
        if ($has_is_mdc) {
            $createStatement .= "CREATE INDEX IF NOT EXISTS `" . self::encryptString('idx_condition_is_mcd')
                . "` ON `" . self::encryptString('Condition') .
                "` ( `" . self::encryptString('isMcd') . "` );";
            $createStatement .= "CREATE INDEX IF NOT EXISTS `" . self::encryptString('idx_procedure_is_mcd')
                . "` ON `" . self::encryptString('Procedure') .
                "` ( `" . self::encryptString('isMcd') . "` );";
            $createStatement .= "CREATE INDEX IF NOT EXISTS `" . self::encryptString('idx_symptom_is_mcd')
                . "` ON `" . self::encryptString('Symptom') .
                "` ( `" . self::encryptString('isMcd') . "` );";
        }
        return $createStatement;
    }

    public static function setEncrypt($encrypt)
    {
        if ($encrypt === "none") {
            self::$encryption = false;
        } else
            self::$encryption = true;
    }

    public static function setStaticRegionAndIsMobile($region_id, $ismobile)
    {
        Region::setStaticRegion($region_id);
        Panel::$static_platform = $ismobile;
    }

    public static function printInCMD($message, $download)
    {
        if (!$download) {
            ob_start();
            MedicusHelper::vd($message);
        }
    }

    public static function generateMobileSql($region_id, $file_name, $destination, $ismobile, $projects, $branch_id = 1, $download = false, $withInsight = true, $version__ = null)
    {
        $projects = !empty($projects) ? explode(',', $projects) : [];
        $encrypt = "none";
        self::setEncrypt($encrypt);

        Yii::$app->language = "en";
        self::printInCMD("Start function", $download);

        self::setStaticRegionAndIsMobile($region_id, $ismobile);

        self::printInCMD("Get branches and fields", $download);

        $branches = Branch::getParents($branch_id);
        $branch_method = Branch::find()->where(['id' => $branch_id])->one()->structure_type;
        $TF = Field::FieldsTablesMap($branches);
        $tablesField = $TF['tables'];
        $tables = $TF['tableMap'];

        self::printInCMD("Get Bio Var", $download);
        $biomarkers_variables = [];
        $biomarkers_variables[Yii::$app->language] = VariableHelper::getAllBiomarkersVariable($region_id, Yii::$app->language);
        $biomarkers_variables_inInsight[Yii::$app->language] = [];
        $biomarkers_variables_inPregnancyInfo[Yii::$app->language] = [];
        foreach ($biomarkers_variables[Yii::$app->language] as $key => $value) {
            if (strpos($value, "_N") !== false)
                $value = strtolower($value);

            $biomarkers_variables_inInsight[Yii::$app->language][$key] = $value;
            $biomarkers_variables_inPregnancyInfo[Yii::$app->language][$key] = $value;
        }

        if (!empty($projects)) {
            $biomarker_ids = ArrayHelper::map(BiomarkerSubProject::find()->where(['sub_project_id' => $projects])->all(), 'biomarker_id', 'biomarker_id');
            $biomarkerModels = Biomarker::find()->where(['validity' => 2, 'id' => $biomarker_ids])->indexBy('symbol')->all();

            $insight_ids = [];
            if($withInsight)
                $insight_ids = ArrayHelper::map(InsightSubProject::find()->where(['sub_project_id' => $projects])->all(), 'insight_id', 'insight_id');
            
            $insights = Insight::find()->where(['validity' => 2, 'id' => $insight_ids])->with('insightSources')->all();

            $question_ids = ArrayHelper::map(QuestionSubProject::find()->where(['sub_project_id' => $projects])->all(), 'question_id', 'question_id');
            $questions = Question::find()->where(['validity' => 2, 'id' => $question_ids])->indexBy('symbol')->all();

        } else {
            $biomarkerModels = Biomarker::find()->where(['validity' => 2])->indexBy('symbol')->all();
            
            $insights = [];
            if($withInsight)
                $insights = Insight::find()->where(['validity' => 2])->with('insightSources')->all();
            
            $questions = Question::find()->where(['validity' => 2])->indexBy('symbol')->all();

        }
        $valid_questions = ArrayHelper::getColumn($questions, 'id');

        self::printInCMD("Get biomarker sources by region", $download);
        $biomarker_sources = BiomarkerSource::getBiomarkerSourcesByRegionEdited($biomarkerModels, $region_id);

        self::printInCMD("Get insight sources by region", $download);
        $insight_sources = InsightSource::getInsightSourcesByRegionEdited($insights, $region_id);

        self::printInCMD("Get all tables", $download);
        $current_region = Region::find()->where(["id" => $region_id])->one();
        $settings = $current_region;
        $settings->db_name = MedicusHelper::$DB_name;
        $settings->export_date = time();

        if(isset($tablesField['Setting'])){
            foreach($tablesField['Setting'] as $field__){
                if($field__->name == 'version'){
                    if($version__){
                        $settings->version = $version__;
                    }
                    break;
                }
            }
        }

        $tablesSQL = [
            'Action' => Action::setActionFieldsForMcd(Action::find()->all()),
            'ActionAnswer' => ActionAnswer::setActionAnswerFieldsForMcd(ActionAnswer::find()->all()),
            'Biomarker' => Biomarker::setBiomarerSynonymText($biomarkerModels, $branch_method, $region_id, $current_region->name, $projects),
            'Clips' => [$current_region],//clips file names in region table(files need to be loaded),
            'Color' => Setting::find()->where(["like", "key", "color_"])->all(),
            'Unit' => Unit::find()->indexBy('symbol')->all(),
            'UnitSynonym' => UnitSynonym::find()->all(),
            'Panel' => Panel::find()->where(['validity' => 2])->indexBy('name')->all(),
            'Region' => Region::find()->where(["leaf" => 1])->indexBy("id")->all(),
            'Country' => Country::find()->indexBy('symbol')->all(),
            'CountryBasedQuestion' => CountryBasedQuestion::find()->all(),
            'QuestionType' => QuestionType::find()->all(),
            'InsightType' => $withInsight ? InsightType::find()->all() : [],
            'Insight' => Insight::addInsightImages($insights),
            'Question' => $questions,
            'BiomarkerUnit' => BiomarkerUnit::setBiomarkerUnitMetadata(BiomarkerUnit::find()->all(), $branch_method, $region_id, $current_region->name),
            'Definition' => Definition::find()->where(['validity' => 2])->all(),
            'Answer' => Answer::AnswerParents(Answer::find()->where(['validity' => 2])->andWhere(['question_id' =>$valid_questions])->with('definition0')->indexBy('id')->all()),
            'Language' => Language::find()->where(['language_id' => ['en']])->all(),
            'BiomarkerSource' => $biomarker_sources,
            'AnswerSource' => AnswerSource::find()->all(),
            'DefinitionSource' => DefinitionSource::find()->orderBy(['order' => 'order', 'id' => 'id'])->all(),
            'InsightSource' => $insight_sources,
            'InsightDefinition' => $withInsight ? InsightDefinition::getCoreInsightDefinitions($region_id) : [],
            'BiomarkerDefinition' => BiomarkerDefinition::find()->joinWith('definition')->where(['region_id' => $region_id])->andWhere(['definition.validity' => 2])->all(),
            'Tooltip' => $withInsight ? Tooltip::find()->all() : [],
            'Setting' => [$settings],
            'CuratedContent' => [],
            'Sample' => Sample::find()->indexBy('symbol')->all(),
            'Bacteria' => Bacteria::FormatGender(Bacteria::find()->all()),
            'BacteriaCluster' => Bacteria::generateBacteriaClusters(),
            'Condition' => Concept::FormatGender(Concept::find()->where(['like', 'type', "condition"])->orWhere(['like', 'type', "both"])->indexBy('symbol')->all()),
            'Symptom' => Concept::FormatGender(Concept::find()->where(['like', 'type', "sign_and_symptom"])->orWhere(['like', 'type', "both"])->indexBy('symbol')->all()),
            'Procedure' => Concept::find()->where(['like', 'type', "procedure"])->indexBy('symbol')->all(),
            'ProcedureAnswer' => ProcedureAnswer::find()->all(),
            'ConditionParent' => [],
            'ConditionSource' => ConceptSource::find()->all(),
            'Note' => Note::find()->all(),
            'SymptomParent' => [],
            'ProcedureParent' => [],
            'Badge' => Badge::find()->all(),
            'BadgeCategory' => BadgeCategory::find()->all(),
            'UnitConversion' => $branch_method === 1 ? self::fillUnitConversionTableData() : [],
            'PregnancyDeliveryPreparation' => PregnancyDeliveryPreparation::find()->all(),
            'PregnancyDeliveryPreparationSource' => PregnancyDeliveryPreparationSource::find()->all(),
            'PregnancyVisit' => PregnancyVisit::find()->all(),
            'PregnancyVisitTest' => PregnancyVisitTest::find()->all(),
            'PregnancyInfo' => PregnancyInfo::find()->where(['region_id' => $region_id])->all(),
            'PregnancySource' => PregnancySource::find()->where(['region_id' => $region_id])->all(),
            'Quote' => Quote::find()->all(),
            'CertaMap' => CertaMap::find()->all(),
            'LingualReadingSynonym' => LingualReadingsSynonyms::find()->all(),
            'LingualReading' => LingualReadings::find()->all(),
            'BiomarkerGroup' => BiomarkerGroup::fillBiomarkerGroupsForMetadata($biomarkerModels),
            'BiomarkerScore' => BiomarkerScore::find()->all(),
            'HomeScreenImages' => HomeScreenImage::find()->all(),
            'MedicalReviewer' => MedicalReviewer::find()->all(),
            'SmartAlertRule' => SmartAlertRule::find()->all(),
            'SmartAlertRulePart' => SmartAlertRulePart::find()->all(),
        ];

        $sqlDataRows = [];
        $sqlDataRows[] = "BEGIN TRANSACTION;\n";
        $createStatement = '';
        self::printInCMD("Create Statement", $download);

        $has_is_mdc = false;
        foreach ($tables as $table) {
            $createStatement .= "CREATE TABLE IF NOT EXISTS" . " `" . self::encryptString($table) . "` (";
            $fields = $tablesField[$table];

            $createFields = [];
            $keys = [];

            foreach ($fields as $field) {
                if ($table == 'Condition') {
                    if ($field->name == 'isMcd') {
                        $has_is_mdc = true;
                    }
                }
                $default = '';
                if (isset($field->default_value)) {
                    $default = ' DEFAULT ' . $field->default_value;
                }
                $createFields [] = "`" . self::encryptString($field->name) . "` " . strtoupper($field->type) . $default;


                if (isset($field->key)) {
                    if ($field->key == 'primary')
                        $keys [] = "PRIMARY KEY(`" . self::encryptString($field->name) . "`)";
                    else {
                        $foreign = explode(',', $field->key);
                        if (count($foreign) == 2) {
                            $keyText = "FOREIGN KEY(`" . self::encryptString($field->name);
                            $keyText .= "`) REFERENCES `" . self::encryptString($foreign[0]);
                            $keyText .= "`(`" . self::encryptString($foreign[1]) . "`) ON DELETE CASCADE";
                            $keys [] = $keyText;
                        }
                    }
                }

            }
            $createStatement .= implode(',', $createFields);

            if (count($keys) > 0) {
                $createStatement .= ",";
                $createStatement .= implode(',', $keys);
            }

            $createStatement .= ");";
        }

        $createStatement .= self::addIndexes($tables, $has_is_mdc);
        $sqlDataRows[] = $createStatement;
        self::printInCMD("Insert Statement", $download);

        foreach ($tables as $table) {
            self::printInCMD("Insert table " . $table, $download);

            $fields = $tablesField[$table];
            $fieldsMap = ArrayHelper::map($fields, 'id', 'name');

            $models = $tablesSQL[$table];
            $rows = [];
            $models_sql = [];
            $models_total_count = count($models);

            if ($models_total_count > 0) {
                $models_sql[] = "INSERT INTO `" . self::encryptString($table) . "` (" . self::encryptColumnNames(implode(',', $fieldsMap)) . ") VALUES";
                foreach ($models as $model) {
                    $insertRow = [];

                    foreach ($fields as $field) {

                        if (isset($field->static_value)) {
                            if ($field->static_value == 'NULL') {
                                $value = NULL;
                            } else {
                                $value = $field->static_value;
                            }
                        } else {
                            $att = $field->attribute;
                            if (strpos($att, '->') !== false) {
                                $att = explode('->', $att);
                                $value = $model;
                                foreach ($att as $a) {
                                    $value = isset($value) ? $value->$a : null;
                                }
                            } else {
                                $value = $model->$att;
                            }

                            if ($field->with_variable) {
                                if ($field->table == "Insight")
                                    $value = isset($value) ?
                                        VariableHelper::replaceText($value, $biomarkers_variables_inInsight[Yii::$app->language], Yii::$app->language)
                                        : $value;
                                elseif ($field->table == "PregnancyInfo")
                                    $value = isset($value) ?
                                        VariableHelper::replaceText($value, $biomarkers_variables_inPregnancyInfo[Yii::$app->language], Yii::$app->language)
                                        : $value;
                                else
                                    $value = isset($value) ?
                                        VariableHelper::replaceText($value, $biomarkers_variables[Yii::$app->language], Yii::$app->language)
                                        : $value;
                            }
                            if ($field->rijndael_encrypt === 1) {
                                if ($encrypt == "old")
                                    $value = isset($value) ? MedicusHelper::Rijndael_Encrypt($value, MedicusHelper::getEncryptionKey()) : $value;
                                else if ($encrypt == "new")
                                    $value = isset($value) ? MedicusHelper::encryptECB($value, MedicusHelper::getEncryptionKey()) : $value;
                            }
                            if (isset($field->null_condition))
                                $value = isset($value) ? $value : $field->null_condition;
                        }
                        $field_type = $field->is_image === 1 ? "icon" : $field->type;
                        if ($field->name === 'icon' && $field->table === 'Country') {
                            if (self::$encryption) {
                                $key = "NAGI9P95XW8WKRCLY8YVFQ3P2SC6KAJU";

                                if (empty($value))
                                    $insertRow [] = "''";
                                else
                                    $insertRow [] = "'" . utf8_encode(MedicusHelper::xorEncrypt($value, $key)) . "'";
                            } else {
                                $insertRow [] = "'" . $value . "'";
                            }

                        } else {
                            $insertRow [] = self::mobileSQLAdapter($value, $field_type);
                        }
                    }
                    $rows[] = "(" . implode(',', $insertRow) . ")";

                }
                $models_sql[] = implode(",", $rows);
                $models_sql[] = ";\n";
            }
            $sqlDataRows[] = implode("", $models_sql);

        }
        $sqlDataRows[] = "\nCOMMIT;\n";
        if (is_file($destination . $file_name)) {
            unlink($destination . $file_name);
        }
        self::printInCMD("Replace Script", $download);

        $script = htmlspecialchars_decode(implode("\n", $sqlDataRows), ENT_QUOTES);
        self::printInCMD("New DB", $download);

        $db = new SQLite3($destination . $file_name);
        self::printInCMD("Exec DB", $download);
        $db->exec($script);
        self::printInCMD("Finish", $download);

        if ($download && file_exists($destination . $file_name)) {
            return Yii::$app->response->sendFile($destination . $file_name, $file_name);
        }

    }

    public static function addLanguages($destination, $encryption, $symbols, $languages, $database)
    {
        if ($encryption === 'none')
            self::$encryption = false;
        else
            self::$encryption = true;
        foreach ($symbols as $symbol) {
            if (!is_dir($destination . "metaadata_" . $symbol))
                mkdir($destination . "metaadata_" . $symbol);
        }
        $symbol_string = implode('-', $symbols);
        if (!is_dir($destination . "metaadata_" . $symbol_string))
            mkdir($destination . "metaadata_" . $symbol_string);
        else
            DirectoryHelper::deleteDirectory($destination . "metaadata_" . $symbol_string . "/*");

        $models = Language::find()->where(['language_id' => $languages])->all();
        $models_sql = [];
        $sqlDataRows = [];
        if (self::$encryption)
            $sqlDataRows [] = "INSERT INTO `" . self::encryptString("Language") . "` (" . self::encryptColumnNames("id,name,name_ascii") . ") VALUES";
        else
            $sqlDataRows [] = "INSERT INTO `Language` (id,name,name_ascii) VALUES";
        $rows = [];
        foreach ($models as $model) {
            if ($model->language_id == "pt-PT")
                $model->language_id = "pt";
            $rows[] = "(" .
                self::mobileSQLAdapter($model->language_id, 'text not null') . "," .
                self::mobileSQLAdapter($model->name, 'text not null') . "," .
                self::mobileSQLAdapter($model->name_ascii, 'text not null') . ")";
        }
        $models_sql[] = implode(",", $rows);
        $models_sql[] = ";\n";
        $sqlDataRows [] = implode("", $models_sql);

        $regions = Region::find()->where(["leaf" => 1])->indexBy("id")->all();
        foreach ($regions as $region) {
            $versionNumber = Setting::findOne(['key' => 'metadata_version_number'])->value;
            $file_name = "Medicus_" . $region->id;
            if (is_file($destination . $file_name)) {
                if (is_file($destination . "metaadata_" . $symbol_string . DIRECTORY_SEPARATOR . $file_name . "_" . $versionNumber))
                    unlink($destination . "metaadata_" . $symbol_string . DIRECTORY_SEPARATOR . $file_name . "_" . $versionNumber);
                if (copy($destination . $file_name, $destination . "metaadata_" . $symbol_string . DIRECTORY_SEPARATOR . $file_name . "_" . $versionNumber)) {
                    $script = htmlspecialchars_decode(implode("\n", $sqlDataRows), ENT_QUOTES);
                    $db = new SQLite3($destination . "metaadata_" . $symbol_string . DIRECTORY_SEPARATOR . $file_name . "_" . $versionNumber);
                    $db->exec($script);
                }
            }

        }
        $versionNumber = Setting::findOne(['key' => 'metadata_version_number'])->value;
        $zip_name = "MetadataDatabases_" . $versionNumber . ".zip";
        if (is_file($destination . "metaadata_" . $symbol_string . DIRECTORY_SEPARATOR . $zip_name))
            unlink($destination . "metaadata_" . $symbol_string . DIRECTORY_SEPARATOR . $zip_name);
        SyncHelper::zipFiles($destination . "metaadata_" . $symbol_string . DIRECTORY_SEPARATOR, $destination . "metaadata_" . $symbol_string . DIRECTORY_SEPARATOR . $zip_name, null);

        $auto_export = new AutoExport();
        $auto_export->item = 'metadata-export';
        $auto_export->file = implode(',', $languages);
        $auto_export->path = implode(',', $languages);
        $auto_export->type = 'metadata';
        $auto_export->database = $database;
        $auto_export->status = "done";
        $auto_export->generated_date = strtotime("now");
        $auto_export->save();

        Yii::$app->response->sendFile($destination . "metaadata_" . $symbol_string . DIRECTORY_SEPARATOR . $zip_name, $zip_name);

    }

    public static function generateLocalData($request, $lab_id = null)
    {
        if ($request->encryption_type === "none") {
            self::$encryption = false;
        } else
            self::$encryption = true;

        MedicusHelper::changeDbConnection($request->database);
        $file_name = "MedicusLocalization";
        $destination = Yii::getAlias('@backend') . Setting::findOne(['key' => 'platform_dir_path'])->value .
            $request->database . DIRECTORY_SEPARATOR . 'localization' . DIRECTORY_SEPARATOR;
        DirectoryHelper::validateDirectory($destination);

        $localization_create_statement = CerboDbHelper::$localization_create_statement;
        $currentLab = $lab_id?Lab::findOne($lab_id):Lab::findOne(1);
        if($currentLab->is_informal){
            $localization_create_statement = CerboDbHelper::$localization_create_statement . ' ' . CerboDbHelper::$localization_create_statement_informal;
        }

        $sqlDataRows[] = "BEGIN TRANSACTION;\n";
        $sqlDataRows[] = self::$encryption ? CerboDbHelper::$localization_create_statement_encrypted : $localization_create_statement;

        $languages = Language::find()->where(["status" => Language::STATUS_ACTIVE])->andWhere(["<>", "language_id", "en"]);
        if($currentLab->is_informal == 0){
            $languages->andWhere(["<>", "language_id", "es-LA_male"]);
            $languages->andWhere(["<>", "language_id", "es-LA_female"]);
        }
        $languages = $languages->all();

        foreach ($languages as $language) {
            $sqlDataRows[] = self::mobileLanguageTranslateSourceSQL($language, $request->language, $lab_id);
        }
        $db_file_name = $destination . $file_name;
        if (file_exists($db_file_name)) {
            unlink($db_file_name);
        }
        $sqlDataRows[] = self::mobileSqliteSequenceSQL($request->language, $lab_id);
        
        if($request->version){
            $sqlDataRows[] = "DELETE FROM `" . self::encryptString('LocalizationSetting') . "` WHERE 1;\n";
            $sqlDataRows[] = "INSERT INTO `" . self::encryptString('LocalizationSetting') . "` (" . self::encryptColumnNames("id,version") . ") VALUES(".self::mobileSQLAdapter(1, 1).",".self::mobileSQLAdapter($request->version).");\n";
        }
        
        $sqlDataRows[] = "\nCOMMIT;\n";
        $script = htmlspecialchars_decode(implode("\n", $sqlDataRows), ENT_QUOTES);
        $db = new SQLite3($db_file_name);
        $db->exec($script);
        if ($request->for_all) {
            $databases = MedicusHelper::getAllDatabases();
            sort($databases);
            $dataKey = array_search($request->database, $databases);
            if ($dataKey + 1 < count($databases))
                AutoGenerate::updateAll(['status' => 'waiting', 'database' => $databases[$dataKey + 1]], ['id' => $request->id]);
            else
                AutoGenerate::updateAll(['status' => 'done'], ['id' => $request->id]);
        } else
            if (isset($request->id))
                AutoGenerate::updateAll(['status' => 'done'], ['id' => $request->id]);

        Lab::unlockVersion($request->database, 'localization_version');
    }

    public static function generateTagDatabase($file_name, $destination, $download)
    {
        $sqlDataRows[] = "BEGIN TRANSACTION;\n";
        $sqlDataRows[] = self::$encryption ? CerboDbHelper::$tag_create_statement_encrypted : CerboDbHelper::$tag_create_statement;
        $sqlDataRows[] = self::mobileTagSQL();
        $sqlDataRows[] = "\nCOMMIT;\n";

        $script = htmlspecialchars_decode(implode("\n", $sqlDataRows), ENT_QUOTES);
        DirectoryHelper::validateDirectory($destination);
        $db_file_name = $destination . $file_name;
        if (file_exists($db_file_name)) {
            unlink($db_file_name);
        }
        $db = new \SQLite3($db_file_name);
        $db->exec($script);

        if (file_exists($db_file_name) && $download) {
            return Yii::$app->response->sendFile($db_file_name, $file_name);
        } else if (!file_exists($db_file_name)) {
            Yii::$app->session->setFlash("error", "Error with exporting Tag database");
        }
    }

    public static function fillUnitConversionTableData()
    {
        $database = isset(\Yii::$app->user) ? \Yii::$app->user->identity->getCurrentLabDbName() : MedicusHelper::$DB_name;
        $path = Yii::getAlias('@backend') . DIRECTORY_SEPARATOR . 'web' . DIRECTORY_SEPARATOR . 'core-files' . DIRECTORY_SEPARATOR . $database . '-core-files' . DIRECTORY_SEPARATOR . 'generated-unit-table.json';
        if (is_file($path)) {
            $file = file_get_contents($path);
            $data = json_decode($file);
            return $data;
        } else
            return [];
    }

}