<?php
include_once("utilities/na_utils.php");
include_once("utilities/utilities.php");
include_once("utilities/sql_utilies.php");

require_once '../allInOneWeb/demosettings.php';
require_once '../allInOneWeb/sqlUtils.php';

class WLUtils{
    private $mysqli = null;
    private $data = array();
    private $ivEntityMatcherService = "";
    private $resultsCols = [];
    private $mappedCols = [];
    private $mappedColsView = [];
    private $accumColToSingle = [];
    
    private $metadata = []; //metadata from file (type, size etc)
    private $input_fields = []; //
    private $allowed_ext = []; //allowed ext (in config.php)
    
    private $names_upload = []; //array for names fetched from csv to upload as WL
    private $csv_seperator = ""; //in config.php - for csv parsing
    private $name_index; //index of name column in csv - could be array of few columns
    private $sqlValues = [];    
    private $bulk_limit;
    private $wl_table;
    private $rules_json;
    private $rules_relation_json;
    private $columns_metadata;
    private $ethnicities_mapping;

    function __construct($mysqli, $data = null){   
        session_write_close();
        
        global $ivProjectName;
        require('..'.DIRECTORY_SEPARATOR.'iventitymatcherhtm'.DIRECTORY_SEPARATOR .'config.php');
        
        $this->mysqli = $mysqli;
        
        $text = $data["content"] ?? $data["fileContent"] ?? $data[0] ?? null; //fetch teh text data from the file (base64)
        
        //to assure it is delcared 
        if(empty($csv_seperator)){
            global $csv_seperator;
        }
        if(empty($allowed_ext)){
            global $allowed_ext;
        }
 
        if(empty($ivEntityMatcherService)){
            global $ivEntityMatcherService;
        }
        
        if(empty(($bulk_limit))){
            global $bulk_limit;
        }
        
        $this->data = $text ?? $data;
        $this->metadata = $data["fileData"] ?? null;
        
        //from config.php
        $this->ivEntityMatcherService = getSystemSettingsProp($this->mysqli, "ivEntityMatcherService");
  
        $this->csv_seperator =  getSystemSettingsProp($this->mysqli, "csv_seperator");
        $this->allowed_ext = $allowed_ext;
        
        $this->bulk_limit = getSystemSettingsProp($this->mysqli, "bulk_limit");
        
        $this->wl_table = $data[0]["table_name"] ?? $data["table_name"] ?? null;
        
        $possible_same_person_rules = json_decode(file_get_contents('../iventitymatcherhtm/jsons/possible_same_person_rules.json',true));
        $relations_rules = json_decode(file_get_contents('../iventitymatcherhtm/jsons/relations_rules.json',true));
        
        $this->rules_json = json_decode(json_encode($possible_same_person_rules), TRUE);           
        $this->rules_relation_json = json_decode(json_encode($relations_rules), TRUE);
        
        $this->rules_json = 
        !empty($data[0]["relationMatching"]) || $_REQUEST["Action"] == "uploadWL" || ($_REQUEST["relationMatching"] ?? null) ? 
                            array_merge($this->rules_json, $this->rules_relation_json) :
                            $this->rules_json;
        
        $columns_json = json_decode(file_get_contents('../iventitymatcherhtm/jsons/columns.json',true));
        $columns_arr = json_decode(json_encode($columns_json), TRUE);
        $this->resultsCols = $columns_arr["resultsCols"];
        $this->mappedCols = $columns_arr["mappedCols"];
        $this->mappedColsView = $columns_arr["mappedColsView"];
        
        $this->accumColToSingle = $columns_arr["accumColToSingle"];
        
        $this->columns_metadata = ["mappedCols" => $this->mappedCols, "accumColToSingle" => $this->accumColToSingle, "mappedColsView" => $this->mappedColsView];
        
       
    }

    
    /**
     * uploadWL - upload a watchlist
     * @param unknown $sent_data
     * @return string[]
     */
    function uploadWL($sent_data, $override = false){
        //iterate over list of possible WL to upload
        foreach($this->data as $list){
            
            $ext = pathinfo($list["fileData"]["name"], PATHINFO_EXTENSION);
            if(!in_array(strtolower(trim($ext)), $this->allowed_ext)){
                write_to_log("ERROR", "extenstion <$ext> not allowed");
                continue;                
            }
            
            if($ext == 'csv'){    
                $wlData = $this->fetchCSVNames($list["content"]);
            }
            else{
                $wlData = $list["content"];
            }     
            $lst_name = getWLName($list);
            $filename = pathinfo($lst_name, PATHINFO_FILENAME); // returns 'filename.md'
   
            //create the database tables (full analysis & rules)
            
            $ret = createWLDatabase($this->mysqli, $filename, $this->resultsCols, $this->rules_json, $override);
            if(is_array($ret) && !empty($ret["error"])){
                return $ret;
            }
            $wl_mysqli = $ret;
            $wlData = explode("\n", base64_decode($wlData));
            
            $person_ref = 1;
            $sql_final = $sql_rules_final = [];
            foreach($wlData as $name){
                $person_data = fetchPersonData($this->ivEntityMatcherService, $name,  true);
                insertPersonDataToDB($wl_mysqli, $this->bulk_limit, $sql_final, $sql_rules_final, $person_ref, $lst_name, $person_ref, $person_data, $name, $this->rules_json);
                $person_ref++;
            }
            //once finish the list of names, insert the rest of the left names to DB
            if(!empty($sql_final)){
                write_to_log("INFO", "Upload Watchlist - $person_ref 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 person_ref =  CONCAT(IFNULL(person_ref, ',') , ',',  VALUES(person_ref)) ";
                    $res = sqlQuery($wl_mysqli, $sql);
                }
            }
            if(!empty($sql_rules_final)){
                write_to_log("INFO", "Upload Watchlist Rules - $person_ref 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_mysqli, $sql);
                }
            }
            
            
      }        
        return true;
    }

    /**
     * delete WL $wlDeleted
     * @param unknown $wlDeleted
     */
    function deleteWL($wlDeleted){
        $exclude_sql = "SELECT * FROM information_schema.tables
        WHERE table_schema = '$wlDeleted' AND
        table_name = 'site_indicator' LIMIT 1;";
        $res = sqlquery($this->mysqli, $exclude_sql);
        if (!empty($res->fetch_object())){
            return ["error" => "cant delete project wl"];
        }
        $res = sqlquery($this->mysqli, "DROP DATABASE $wlDeleted");
        return true;
    }
    
    /**
     * getWLs - get the current WL list at the DB
     * @return boolean|string[]
     */
    function getWLs(){
        $exclude_project = $_REQUEST["exclude"] ?? false; //exclude site wl from be presented in the upload/delete wl page
        write_to_log("TRACE", "Getting Watchlists array, using: $this->ivEntityMatcherService");
        $results = [];
        $res = sqlquery($this->mysqli, "SHOW DATABASES   LIKE \"wl_%\"");
        if (!empty($res)){
           $arr = $res->fetch_all();
           foreach($arr as $key=>$element){
               if($exclude_project){
                   $exclude_sql = "SELECT * FROM information_schema.tables
                                     WHERE table_schema = '$element[0]' AND 
                                    table_name = 'site_indicator' LIMIT 1;";
                       $res = sqlquery($this->mysqli, $exclude_sql);
                   if (!empty($res->fetch_object())){
                       continue;
                   }
               }
               $results[] = $element[0];
           }
        }
        return $results;
    }
    
    /**
     * getLatestWLResult - expert the data in wl-table-analyse-output in DB as csv file
     * @param unknown $name
     */
    function getLatestWLResult($wlname, $wl_refrence = null){
        include_once 'utilities/report.php';
        
        $filter_sql = !empty($_REQUEST["chart_name"]) ? buildFilter($_REQUEST["chart_name"], $_REQUEST["filter_name"]) : "";
        
        if($wl_refrence){
            $this->mysqli = sqlCreateDBConnection($wl_refrence);
            $sql = "SELECT * FROM full_analysis $filter_sql";
        }
        else{
            $sql = "SELECT * FROM $wlname $filter_sql";
        }
        
        $res = sqlquery($this->mysqli, $sql);
        header('Content-Type:: application/csv');
        header('Content-Disposition: attachment; filename="'.$wlname.'.csv";');
        //open the "output" stream
        $stdout_file = fopen("php://output", 'w');
        $first = true;
        fwrite($stdout_file, "sep=\t".PHP_EOL);
        while ($row = $res->fetch_object()){
            
            if($first){
                $first = false;
                $headers = array_keys((array)$row);
                
                fputcsv($stdout_file, $headers, "\t");
                continue;
            }

            
            
            fputcsv($stdout_file, get_object_vars($row), "\t");
                
            
        }
        fclose($stdout_file);
    }
    
    /**
     * fetchSavedTables
     * @param unknown $username
     * @param unknown $isAdmin
     * @return NULL[]|mixed[]
     */
    function fetchSavedTables($username, $isAdmin){
        $username_str = $isAdmin ? "" : "_$username";
        $sql = "SELECT 
                     table_name AS n,create_time  AS c 
                    FROM 
                     information_schema.tables
                    WHERE 
                     table_schema = DATABASE() AND table_name LIKE \"wl_res$username_str%\" ORDER BY C DESC";
        $res = sqlquery($this->mysqli, $sql);
        while ($res && $row = $res->fetch_object())
        {
            $arr = explode('$', $row->n);
            $caption = $arr[0];
            $tables[] = ["name"=>$row->n, "caption"=>$caption, "date"=>$row->c];
        }
        return $tables;
    }
 
    /**
     * refresh the table and return current results
     */
    function refreshTable($table_name, $wl_refrence = null){
        include_once 'utilities/report.php';
        $results = $final_res = [];
        $allowed_cols = [];
        $filter_sql = !empty($_REQUEST["chart_name"]) ? buildFilter($_REQUEST["chart_name"], $_REQUEST["filter_name"]) : "";
        
        if($wl_refrence){
            $this->mysqli = sqlCreateDBConnection($wl_refrence);
            $sql = "SELECT * FROM full_analysis  $filter_sql LIMIT 10000";
        }
        else{
            $sql = "SELECT * FROM $table_name $filter_sql LIMIT 10000";
        }
        $res = sqlquery($this->mysqli, $sql);
        while ($res && $row = $res->fetch_object()){
            $arr = (array)$row; 
         
            unset($arr["digestkey"]); //no need for it in the response
            $results[] = $arr;
          
            $s= array_filter($arr);
            $allowed_cols = array_unique(array_merge($allowed_cols, array_keys($s)));
        }
        
        $final_res = [];
        
        foreach($results as $element){
            $temp_element = [];
            foreach ($element as $key => $value)
            {
               
                if (in_array($key, $allowed_cols))
                {
                    
                    $temp_element[$key] = !empty($value) ? 
                        (isStringJson($value) && $key!=='rules_string' ? json_decode($value,true) : getCaptionFromFullAnalysis($value)) :
                    "";
                }
            }
            $final_res[] = $temp_element;
        }
        return $final_res;
    }
    
  
    /**
     * fetchCSVnames
     * @param unknown $content
     * @return string
     */
    function fetchCSVnames($content){
        $decoded_data = base64_decode($content);
        
        $lines = preg_split("/\\r\\n|\\r|\\n/", $decoded_data);
        $totalNames = count($lines);
        $first = true;
        $input_fields = [];
        $name_index = [];
        $curr_id = 0;
        foreach ($lines as $name) {
            $curr_id++;
            $input_data = handleCSV($name, $this->csv_seperator, $first, $input_fields, $name_index, $curr_id);
            
            $name = $input_data["name"] ?? null;
            $name = ucwords(strtolower($name));
            $name = str_replace('.', '', $name);
            $extra_input = $input_data["extra_input"] ?? null;
            if(empty($name)){
                continue;
            }
            $names_arr[] = $name;
        }
        $names_str = implode("\n", $names_arr);
        $wlData = base64_encode($names_str);
        return $wlData;
    }
    
    function fetchReport($name){
        include_once 'utilities/report.php';
        return generateReport($this->mysqli, $name, $_REQUEST["wl_ref"] ?? null);
    }
    
    function clearNMHistory($username, $isAdmin){
        $tables = $this->fetchSavedTables($username, $isAdmin);
        foreach($tables as $table){
            $table = $table["name"];
            $sql = "DROP TABLE $table";
            sqlquery($this->mysqli, $sql);
        }
    }

    
}