<?php

function generateReport($mysqli, $table, $wl_ref = null){  
    $i = 0;
    $full_report = [];
    if(!empty($wl_ref)){
        $mysqli = sqlCreateDBConnection($wl_ref);
        $sql = "SELECT * FROM full_analysis";
    }
    else{
        $sql = "SELECT * FROM $table";
    }
    $res = sqlquery($mysqli, $sql);
    
    $full_report["rules"] = $full_report["gender"] = $analysed_list = [];
    while ($res && $row = $res->fetch_object()) {
        $i++;
        fetch_data($row, $row->matching_results, $full_report, $analysed_list);
    }   
    
    foreach($full_report as $catagory=>&$arr){
        $amount = array_column($arr, 'amount');
        
        array_multisort($amount, SORT_DESC, $arr);
        $arr = array_slice($arr, 0, 100);
    }
  
    
    return $full_report;
}


/**
 * 
 * @param unknown $row
 * @param unknown $input
 * @param unknown $full_report
 */
function fetch_data($row, &$input, &$full_report, &$analysed_list){   
    //matching rules stat
    $rules = json_decode($row->matching_results, true) ?? [];
    foreach($rules as $rule=>$data){
        if(empty($data) || !is_array($data)){
            continue;
        }
        
        $rule_index = array_search($rule, array_column($full_report["rules"], 'id'));
        
        if(!notempty($rule_index)){
            $full_report["rules"][] = ["name"=>$rule, "id"=>$rule,"amount"=>0, "chart_name"=>"matching_results"];
        }
        $rule_index = array_search($rule, array_column($full_report["rules"], 'id'));
      
        $full_report["rules"][$rule_index]["amount"]++;
        
    }
    //handle Gender
    handleGender($full_report, $row);
    
    //handleAnalysis
    handleAnalysis($full_report, $row, $analysed_list);
    
    //naming convention    
    handleNamingConvention($full_report, $row);
}

/**
 * 
 * @param unknown $full_report
 * @param unknown $row
 */
function handleGender(&$full_report, $row){
    $gender = !empty($row->gender) ? $row->gender : "No Gender";

    $gender_index = array_search($gender, array_column($full_report["gender"], 'id'));
    
    if(!notempty($gender_index)){
        $full_report["gender"][] = ["name"=>$gender, "id"=>$gender,"amount"=>0, "chart_name"=>"gender"];
    }
    $gender_index = array_search($gender, array_column($full_report["gender"], 'id'));
    
    $full_report["gender"][$gender_index]["amount"]++;
    
}
/**
 *  handleAnalysis(&$full_report, $row)
 */
function handleAnalysis(&$full_report, $row, &$analysed_list){
    if(!array_key_exists("analysis_status", $full_report)){
        $full_report["analysis_status"] = [];   
    }
    $analysis_status = ($row->warning == 'No Analysis') ? "No Analysis" : "analysed";
    
    if(in_array($row->person_ref ?? $row->id, $analysed_list)){
        return;
    }
    $analysed_list[] = $row->person_ref ?? $row->id;
    $analysis_index = array_search($analysis_status, array_column($full_report["analysis_status"], 'id'));
    
    if(!notempty($analysis_index)){
        $full_report["analysis_status"][] = ["name"=>$analysis_status, "id"=>$analysis_status,"amount"=>0, "chart_name"=>"warning"];
    }
    $analysis_index = array_search($analysis_status, array_column($full_report["analysis_status"], 'id'));
    
    $full_report["analysis_status"][$analysis_index]["amount"]++;
}
/**
 * 
 * @param unknown $full_report
 * @param unknown $row
 */
function handleNamingConvention(&$full_report, $row){
    $eth_arr = array_filter(preg_split("/[^A-Za-z- ]/", $row->ethnicity));
    foreach($eth_arr as $eth){
        $eth = preg_replace("/[^A-Za-z- ]/", '', $eth);

        $eth = strtolower(trim($eth));
    
        if(empty($eth)){
            $eth = "No Data";
        }
        if(!array_key_exists("ethnicity", $full_report)){
            $full_report["ethnicity"] = [];
        }
        //first
        $eth_index = array_search($eth, array_column($full_report["ethnicity"], 'id'));
        if(!notempty($eth_index)){
            $full_report["ethnicity"][] = ["name"=>$eth, "id"=>$eth, "chart_name"=>"ethnicity", "amount" => 0];
        }
        $eth_index = array_search($eth, array_column($full_report["ethnicity"], 'id'));
    
        $full_report["ethnicity"][$eth_index]["amount"]++;
    }
}

function handleNamingConventionObs(&$full_report, $row){
    $eth = preg_replace("/[^A-Za-z- ]/", '', $row->ethnicity);
    $eth = trim($eth);
    $naming_convention = $row->naming_convention;
    
    if(empty($naming_convention)){
        $naming_convention = "No data";
        $eth = "No Data";
    }
    if(!array_key_exists("naming_convention", $full_report)){
        $full_report["naming_convention"] = [];
    }
    //first
    $nc_index = array_search($naming_convention, array_column($full_report["naming_convention"], 'id'));
    if(!notempty($nc_index)){
        $full_report["naming_convention"][] = ["name"=>$naming_convention, "id"=>$naming_convention,"children"=>[], "chart_name"=>"naming_convention"];
    }
    $nc_index = array_search($naming_convention, array_column($full_report["naming_convention"], 'id'));
    
    //get naming convention index first
    $eth_index = array_search($eth, array_column($full_report["naming_convention"][$nc_index]["children"], 'id'));
    if(!notempty($eth_index)){
        $full_report["naming_convention"][$nc_index]["children"][] = ["name"=>$eth, "id"=>$eth,"amount"=>0, "chart_name"=>"naming_convention"];
    }
    $eth_index = array_search($eth, array_column($full_report["naming_convention"][$nc_index]["children"], 'id'));
    
    $full_report["naming_convention"][$nc_index]["children"][$eth_index]["amount"]++;
}


/**
 * 
 * @param unknown $var
 * @return boolean
 */
function notempty($var) {
    $val = ($var===0 || $var);
    return $val;
}

/**
 * filter for presenting the report
 * @param unknown $chart_name
 * @param unknown $filter_name
 * @return string
 */
function buildFilter($chart_name, $filter_name){
    if($chart_name == "warning"){
        $filter_name = !in_array($filter_name,["No Analysis"]) ? "" : $filter_name;
        $where_sql = "WHERE  REGEXP_REPLACE($chart_name, '[^a-zA-Z ]', '')LIKE \"$filter_name\"";
        if(empty($filter_name)){
            $where_sql .= " OR $chart_name IS NULL";
        }
    }
    elseif($chart_name == "matching_results"){
        $where_sql = "WHERE JSON_EXTRACT(matching_results, '$.$filter_name') IS NOT NULL";
    }
    else{
        $filter_name = in_array($filter_name,["No Data", "No Gender"]) ? "" : $filter_name;
        $where_sql = "WHERE  $chart_name LIKE \"%$filter_name%\"";
        if(empty($filter_name)){
            $where_sql .= " OR $chart_name IS NULL";
        }
    }
    
    return $where_sql;
}

