<?php
/**
 * create the database tables per WL upload (full analysis & rules)
 * @param unknown $filename
 * @return $wl_mysql
 */
function createWLDatabase($mysqli, $filename, $resultsCols, $rules_arr, $override, $project_wl = false){
    //A - First - creates the WL Database - check if exists (if so - return error)
    $databaseName = strtolower("wl_". clean($filename));
    
    $unique_key =", UNIQUE KEY `digest_person` (`digestkey`,`person_ref`)";
    
    //only if not override - check if wl exists
    if(!$override){
         $res = sqlQuery($mysqli, "SELECT SCHEMA_NAME
                      FROM INFORMATION_SCHEMA.SCHEMATA
                     WHERE SCHEMA_NAME = '$databaseName'");
         $obj = $res->fetch_object();
         if(!empty($obj)){
             return ["error" => "watchlist already exists"];
         }
    }
     sqlQuery($mysqli, "DROP DATABASE IF EXISTS $databaseName");
    
    sqlQuery($mysqli, "CREATE DATABASE $databaseName CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci");
    $wl_mysql = sqlCreateDBConnection($databaseName);
    
    //B - Second - create the full-analysis table for the WL
    $cols = ["sqlId INT NOT NULL AUTO_INCREMENT, person_ref varchar(150), na_index int, digestkey varchar(600)"];
    foreach($resultsCols as $col){  
            $cols[] = "$col TEXT";
    }
    
    //mark project wl_database (can't be delete as others)
    if($project_wl){
        $sql = "CREATE TABLE `site_indicator` (`id` INT NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`))";
        $result = sqlQuery($wl_mysql, $sql);
    }
    
    $cols_str = implode(',', $cols);
    $sql = "CREATE TABLE IF NOT EXISTS full_analysis ($cols_str,PRIMARY KEY (sqlId) $unique_key)";
    $result = sqlQuery($wl_mysql, $sql);
    

    
    //C - Third - creates the rules tables
    foreach($rules_arr as $rule=>$data){
        $initials_columns = $data["initials_tables"];
        //C1 - creates the main rule table
        $sql = "CREATE TABLE IF NOT EXISTS $rule (id INT NOT NULL AUTO_INCREMENT,
                                                  person_ref varchar(250),
                                                  na_index LONGTEXT, 
                                                  rule_key varchar(250),
                                                  PRIMARY KEY (id),
                                                  UNIQUE KEY `rule_key_person_ref` (`rule_key`,`person_ref`) )";
        $result = sqlQuery($wl_mysql, $sql);
        
        //C2 - creates from manadatory fields sub-initials tables 
        foreach($initials_columns as $col){
            $sql = "CREATE TABLE IF NOT EXISTS $rule"."_initial_$col (id INT NOT NULL AUTO_INCREMENT,
            person_ref varchar(250),
             na_index LONGTEXT, 
            rule_key varchar(250),
            PRIMARY KEY (id),
             UNIQUE KEY `rule_key_person_ref` (`rule_key`,`person_ref`) )";
            $result = sqlQuery($wl_mysql, $sql);
           
        }
    }

    //Last - return the mysql connection to the WL DB
    return $wl_mysql;
}


function clean($string) {
    $string = str_replace([' ','-'], '_', $string); // Replaces all spaces with hyphens.
    
    return preg_replace('/[^A-Za-z0-9\-]/', '_', $string); // Removes special chars.
}


/**
 * insertPersonDataToDB - insert all the name parts of the person to full_analysis table in DB
 * @param unknown $wl_mysql
 * @param unknown $lst_name
 * @param unknown $person_data
 */
function insertPersonDataToDB($wl_mysql, $bulk_limit, &$sql_final, &$sql_rules_final, $index, $lst_name, $person_ref, $person_data, $name, $rule_json){
    insertFullDetails($wl_mysql, $bulk_limit, $sql_final, $index, $lst_name, $person_ref, $person_data, $name); //insert the full data to full-analysis table
    insertToRules($wl_mysql, $bulk_limit, $sql_rules_final, $index, $lst_name, $person_ref, $person_data, $rule_json); //insert data to rules tables
}

/**
 * insertFullDetails - insert the full data to full-analysis table
 * @param unknown $wl_mysql
 * @param unknown $lst_name
 * @param unknown $person_data
 */
function insertFullDetails($wl_mysql, $bulk_limit, &$sql_final, $index, $lst_name, $person_ref, $person_data, $name){
     $sql_cols_vals = $sql_cols_vals_instances = [];
     $na_index = 0;
    foreach($person_data["Name Alternatives"] as $na){
        $sql_cols_vals = ["person_ref" => $person_ref,"na_index"=> $na_index, "input" => $wl_mysql->real_escape_string($name)];
        $na_index++;
        $digestNAkey = "";
        foreach ($na as $slot=>$val){
            $col = strtolower(str_replace('-', '_', $slot));
            
            if(is_array($val)){
                $caption = $val["caption"];
                $instance_arr = $val["instance_arr"];
                $instances_str = implode('^',$instance_arr);
                $sql_cols_vals[$col] = $wl_mysql->real_escape_string($caption."=>".$instances_str);
                $digestNAkey .= createNADigestElement($col,$sql_cols_vals[$col]);
                continue;
            }
            $val = $wl_mysql->real_escape_string($val);
            $sql_cols_vals[$col] = $val;
            $sql_cols_vals_instances[$col] = $val;
            
            $digestNAkey .= createNADigestElement($col,$val);
            
        }
        
        //skip if the data too long
        if(strlen($digestNAkey)> 600){
            write_to_log("ERROR", "Digest key too long - skipping ".json_encode($na,true));
            continue;
        }
        
        $sql_cols_vals["digestkey"] = base64_encode(gzcompress($digestNAkey,9));
    
        $sqlColumns = "(".implode(',', array_keys($sql_cols_vals)).")";
        $sqlValues = "('".implode("','", array_values($sql_cols_vals))."')";
        $sql_final[$sqlColumns][] = $sqlValues;
        
    }
    
    //for tracing - bulk limit / 10
    if($bulk_limit > 1 && $index % ($bulk_limit/10) == 0){
        write_to_log("INFO", "Upload Watchlist - $index passed");
    }
    
    //insert 1000 names batch
    if($index % $bulk_limit == 0){
        write_to_log("INFO", "Upload Watchlist - $index passed - insert to DB");
        
        foreach($sql_final as $cols=>$vals){
            $vals_str = implode(',', $vals);
            $sql = "INSERT INTO full_analysis $cols VALUES $vals_str 
            ON DUPLICATE KEY UPDATE na_index  = CONCAT(IFNULL(CAST(na_index AS CHAR), ',') , ',',  VALUES(na_index)) ";
            $res = sqlQuery($wl_mysql, $sql);         
        } 
        $sql_final = [];
    }
}

function createNADigestElement($col,$val){
    return $col.$val;
}

/**
 * insertToRules - insert data to rules tables
 * @param unknown $wl_mysql
 * @param unknown $lst_name
 * @param unknown $person_ref
 * @param unknown $person_data
 * @param unknown $rule_arr
 */
function insertToRules($wl_mysql, $bulk_limit, &$sql_rules_final, $index, $lst_name, $person_ref, $person_data, $rule_arr){
    //foreach name alternative
    $na_index = 0;
    foreach ($person_data["Name Alternatives"] as $na){
        if(array_key_exists("warning", $na) && $na["warning"] == "No Analysis"){
            continue;
        }
        //iterate over the rule array 
        //A - get the mendatory fields and generate matching key to insert to the db
        foreach($rule_arr as $rule=>$rule_data){
            $initials_tables = $rule_data["initials_tables"];
            $keys = $rule_data["keys"];
            $conditions = $rule_data["conditions"];
            $mapped_cols = $rule_data["mapped_cols"] ?? null;
            if(!check_rule_conditions($na, $conditions)){
                continue;
            }
           
            insertMatchingKeysToRuleDB($wl_mysql, $sql_rules_final, $index, $rule, $initials_tables, $person_ref, $na, $na_index, $keys, $mapped_cols);  
        }
        $na_index++;
        
    }
    
    //insert #bulk_limit name OR if person ref is not int (from regular site upload)
    if(!is_int($person_ref) || $person_ref % $bulk_limit == 0){
        write_to_log("INFO", "Upload Watchlist Rules - $index passed - insert to DB");
        
        foreach($sql_rules_final as $table=>$values){
            $values = array_unique($values);
            $vals_str = implode('),(', $values);
            $sql = "INSERT INTO $table (rule_key, person_ref, na_index) VALUES ($vals_str)
            ON DUPLICATE KEY UPDATE na_index  = CONCAT(IFNULL(na_index, ',') , ',',  VALUES(na_index)) ";
            $res = sqlQuery($wl_mysql, $sql);
        }
        $sql_rules_final = [];
    }
   
}

/**
 * 
 * @param unknown $person_data
 * @param unknown $conditions
 */
function check_rule_conditions($na, $conditions){
    foreach($conditions as $cond){
        $arr = explode('=', $cond);
        
        //check just if key exists (as kunya)
        if(count($arr)==1){
            return array_key_exists($arr[0], $na);         
        }
        
        $key = $arr[0];
        $val = $arr[1];
        
       
        if(!array_key_exists($key, $na)){
            return false;
        }
        if(stripos($na[$key], $val) === false){
            return false;
        }
    }
    return true;
}

function getInstance($mysql, $value){
    if(empty($value)){
        return null;
    }
    if(is_array($value)){
        return $mysql->real_escape_string($value["instance"]);
    }
    return $mysql->real_escape_string($value);
}

/**
 * 
 * @param unknown $na
 * @return string[]
 */
function kunyaSpecialCase($mysql, $na, $flip_fields = false){
    $matching_keys_arr=  [];
    $fpn = getInstance($mysql,$na["father_patrimonyal_name"] ?? null);
    $son = getInstance($mysql,$na["son"] ?? null);
    $gn = getInstance($mysql,$na["given_name"] ?? null);
    $fn = getInstance($mysql, $na["family_name"] ?? null);
    
    if(array_key_exists("father_patrimonyal_name", $na)){
        $base_key = $flip_fields ? "|s:$fpn|" : "|fpn:$fpn|";
        $found_f = false;
        if(array_key_exists("given_name", $na)){
            $found_f = true;     
            $key = $base_key."|gn:$gn|";
            $matching_keys_arr[] = $key;
        }
        if(array_key_exists("family_name", $na)){
            $found_f = true;
            $key = $base_key."|fn:$fn|";
            $matching_keys_arr[] = $key;
        }
        if(!$found_f){
            $matching_keys_arr[] = $base_key;
        }
    }
    if(array_key_exists("son", $na)){
        $found_f = false;
        $base_key = $flip_fields ? "|fpn:$son|" : "|s:$son|";
        if(array_key_exists("given_name", $na)){
            $found_f = true;
            $key = $base_key."|gn:$gn|";
            $matching_keys_arr[] = $key;
        }
        if(array_key_exists("family_name", $na)){
            $found_f = true;
            $key = $base_key."|fn:$fn|";
            $matching_keys_arr[] = $key;
        }
        if(!$found_f){
            $matching_keys_arr[] = $base_key;
        }
    }
    return $matching_keys_arr;
}


/**
 * special care for mapped columns (as in kunya son=>father_patronomic_name)
 * @param unknown $mapped_cols
 * @param unknown $col
 * @return unknown
 */
function checkMappedCols($mapped_cols, $col){
    if(empty($mapped_cols)){
        return $col;
    }
    if(array_key_exists($col, $mapped_cols)){
        return $mapped_cols[$col];
    }
    return $col;
}

/**
 * 
 * @param unknown $wl_mysql
 * @param unknown $rule
 * @param unknown $initials_tables
 * @param unknown $person_ref
 * @param unknown $na
 * @param unknown $keys_arr
 * @param unknown $mapped_cols
 */
function insertMatchingKeysToRuleDB($wl_mysql,  &$sql_rules_final, $index, $rule, $initials_tables, $person_ref, $na, $na_index, $keys_arr, $mapped_cols){
   
    foreach($keys_arr as $key_str){
        $mandatory_values_cond = true; //boolean to make sure we have ALL mandatory fields
        $keys = explode('&', $key_str);
        $matching_key_arr = [];
        foreach($keys as $k){
            $curr_data = $na[$k] ?? "";
            $k = checkMappedCols($mapped_cols, $k); //convert name of columns in needed (as in kunya)
            $curr_key = getShortKeyVal($wl_mysql, $k, $curr_data);
            if(empty(($curr_key))){
                $mandatory_values_cond = false;
                break;
            }
            $matching_key_arr[$k] = $curr_key;          
        }
       
        if($mandatory_values_cond){
            ksort($matching_key_arr);
            $matching_key = join('',array_values($matching_key_arr));
            $matching_key = prepareMatchingKeyForSql($wl_mysql, $matching_key);            
            $sql_rules_final[$rule][]= "\"$matching_key\", \"$person_ref\", $na_index";
        }
    }

    //B - manadatory fields sub-initials tables
    foreach($initials_tables as $init_mand_f){
         $matching_key_initials = "";
            
         foreach($keys_arr as $key_str){
             $keys = explode('&', $key_str);
             foreach($keys as $k){
                 if(array_key_exists($k, $na)){
                     if(is_array($na[$k]) && $init_mand_f == $k){
                         $na[$k] = $na[$k]["caption"];
                     }
                 }
                $curr_data =  ($init_mand_f == $k) ? $na[$k][0] ?? "" : $na[$k] ?? "";
                $curr_key = getShortKeyVal($wl_mysql, $k, $curr_data);
                if(empty(($curr_key))){
                    $mandatory_values_cond = false;
                    break;
                }
                $matching_key_initials .= $curr_key;
             }
        }
        if($mandatory_values_cond){
            $matching_key_initials = prepareMatchingKeyForSql($wl_mysql, $matching_key_initials);
            $sql_rules_final[$rule."_initial_$init_mand_f"][] = "\"$matching_key_initials\", \"$person_ref\", $na_index";
            
        }   
    }
    
}

/**
 * createOutputWLSTable - creates mysql table for the output of analyse/compare processes
 * @param unknown $mysqli
 * @param unknown $name
 * @param unknown $cols
 * @return unknown|string[]
 */
function createOutputWLSTable($mysqli, $name, $cols){
    $cols_str = "";
    
    sqlQuery($mysqli, "DROP TABLE IF EXISTS $name");
    foreach($cols as $col){
        $col = str_replace([' ','-'], '_', strtolower($col));
        $cols_str .= in_array(strtolower($col), ['warning','person_ref',  'matching_results','matching_metadata',"digestkey","ethnicity"]) ? "$col LONGTEXT, " : "$col VARCHAR(150), ";
    }
    $cols_str = rtrim($cols_str, ', ');
    $query = "CREATE TABLE IF NOT EXISTS $name(
    $cols_str
    )";
    $res = sqlQuery($mysqli, $query);
    
    if(!empty($res)){
        return $name;
    }
    return array("Error" => "could not create wl $name table" );
    
}

/**
 * prepareMatchingKeyForSql
 * @param unknown $wl_mysql
 * @param unknown $matching_key
 * @return string
 */
function prepareMatchingKeyForSql($wl_mysql, $matching_key){
//     $matching_key = $wl_mysql->real_escape_string($matching_key);
    $matching_key =  base64_encode(gzcompress($matching_key, 9));
    return $matching_key;
}

/**
 * 
 * @param unknown $wl_mysql
 * @param unknown $wl_name
 * @return array[]
 */
function getWlFullAnalyseData($wl_mysql, $wl_name, $caption_only = false){
    $response = [];
    
    $sql = "SELECT * FROM full_analysis LIMIT 1000000";
    $res = sqlquery($wl_mysql, $sql);
    while ($row = $res->fetch_object()){
        $curr_row = (array)$row;
        if($caption_only){
            foreach($curr_row as $key=>&$item){
                $item = getCaptionFromFullAnalysis($item);
            }
        }
        if(empty($response[$curr_row['person_ref']])){
            $response[$curr_row['person_ref']] = [];
        }
        $response[$curr_row['person_ref']][] = $curr_row;
    }
    return $response; 
}

function getCaptionFromFullAnalysis($str){
    if(empty($str)){
        return "";
    }
    if(is_array($str)){
        $str = $str['caption'];
    }
    if(strpos($str, "=>") !== false){
        $arr = explode("=>", $str);
        $str = $arr[0];
    }
    return $str;
}
/**
 * getRuleDBData
 * @param unknown $wl_target
 * @param unknown $rule
 * @return NULL[]
 */
function getRuleDBData($wl_target, $rule){
    $results = [];
    $sql = "SELECT person_ref, rule_key FROM $rule";
    $res = sqlquery($wl_target, $sql);
    while ($row = $res->fetch_object()){
        $results[$row->rule_key] = $row->person_ref;
    }
    return $results;
}

/**
 * insertBulkDB - insert current this->sqlValues to the DB
 * @param unknown $cur_res
 * @param unknown $first_row
 * @param unknown $wlSelected
 */
function insertBulkDB($mysqli, &$sqlValues, $wl_table, $columns, &$first_row,$wlSelected){
    write_to_log("INFO", "Inserting info to DB!");
    if($first_row){
        $first_row = false;
        
        createOutputWLSTable($mysqli, $wl_table,$columns);
    }
    
    $sql  = "INSERT INTO $wl_table";
    $sql .= " (`".implode("`, `", $columns)."`)";
    $sql .= " VALUES ".implode(", ", $sqlValues)." ";
    sqlquery($mysqli, $sql);
}

/**
 * addValuesDB
 * @param unknown $mysqli
 * @param unknown $cur_res
 * @param unknown $sqlValues
 */
function addValuesDB($mysqli, $cur_res, &$sqlValues){
    foreach ($cur_res as &$var){
        if(empty($var)){
            $var = "";
        }
        else{
            $var = is_array($var) ? $mysqli->real_escape_string(json_encode($var, true)) : $mysqli->real_escape_string($var);
        }
    }
    
    $sqlValues[] = " ('".implode("', '", $cur_res)."') ";
}
