<?php


class dbConnect{

    private $pdo;
    public function __construct(){

        try {
            $this->pdo = new  PDO('' . getenv("TRENDONE_DB", false), '' . getenv("TRENDONE_DB_USER", false), '' . getenv("TRENDONE_DB_PW", false));
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        }catch(PDOException $e){
            //Error Message
            $failWrap = [
                'success' => false,
                'message' => "Internal Server Error. Pleas try again later",
                'code' => 500
            ];
            header('Content-type: application/json; charset=utf-8');
            echo json_encode($failWrap);
            exit();
        }

    }


    //Gets data for Megatrend with specified id
    public function getMegaTrend($id){

        $reqBuild = $this->getRequestSuccessWrap();
        $megaIds = explode(",",$id);
        $anzMegaIds = count($megaIds);
        $failArray = array();
        $TrendOneUrl = '/trendexplorer/tab1/portfolio/0/c/';


        $megaQueryString = "";
        //Checks if id is numeric
        $numericCheck = true;

        //We build a string which we will set in the query
        for($i = 0; $i < $anzMegaIds; $i++) {
            //Adding as many "microtrendID = ?" as we have ids
            //$megaQueryString = $megaQueryString.'megatrend.megatrendID = ? ';
            $megaQueryString = $megaQueryString.'megatrendID = ? ';

            if($i == $anzMegaIds - 1) {

            } else {
                $megaQueryString = $megaQueryString.'OR ';
            }

            //Checks if ids are numeric
            if(!is_numeric($megaIds[$i])){
                $numericCheck = false;
            }
        }

       if($numericCheck == true) {

           $megaData = array();



           $statement = $this->pdo->prepare("SELECT * FROM megatrend WHERE ".$megaQueryString." ");
           $statement->execute($megaIds);


           while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {

               $megaPoint = [
                   "ID" => intval($row['megatrendID']),
                   "NameDE" => $row['title_de'],
                   "DescriptionDE" => $row['description_de'],
                   "NameEN" => $row['title_en'],
                   "DescriptionEN" => $row['description_en'],
                   "Visual" => $row['visual'],
                   "CountMicro" => intval($row['countMicro']),
                   "CountMicro30Days" => intval($row['countMicro30Days']),
                   "GrowthMicro30Days"=> $row['growthMicro30Days'],
                   "Created" => $row['createdAt'],
                   "Updated" => $row['updatedAt'],
                   "Status" => $row['status'],
                   "TrendOneURL" => $TrendOneUrl.$row['megatrendID'],
               ];
               $megaData[] = $megaPoint;
           }


           //Checks if amount of ids is the same as amount of results if not it checks what ids dont have results and creates point for them
           if($anzMegaIds != count($megaData)){
               foreach($megaIds as $index => $h){
                   $pointCheck = false;
                   foreach($megaData as $res => $p){
                       if(intval($megaIds[$index]) == $megaData[$res]['ID']){
                           $pointCheck = true;
                       }

                   }

                   if($pointCheck == false){
                       $megaPoint = [
                           "ID" => intval($megaIds[$index]),
                           "Error" => "Not found",
                       ];
                       $megaData[] = $megaPoint;
                   }
               }

               //Sorts the array
               $point = array();
               foreach($megaData as $key => $row){
                   $point[$key] = $row['ID'];
               }
               array_multisort($point, SORT_ASC, $megaData);
           }

           $reqBuild['response'] = $megaData;

       }else{
           $reqBuild['response'] = $failArray;
           $reqBuild['success'] = false;
       }
        return $reqBuild;

    }


    //Gets data for Macrotrend with specified id
    public function getMacroTrend($id, $chart){

        $reqBuild = $this->getRequestSuccessWrap();
        $macroIds = explode(",",$id);
        $anzMacroIds = count($macroIds);
        $failArray = array();
        $TrendOneUrl = '/trendexplorer/tab1/portfolio/0/c/';



        $macroQueryString = "";
        //Checks if id is numeric
        $numericCheck = true;

        //We build a string which we will set in the query
        for($i = 0; $i < $anzMacroIds; $i++) {
            //Adding as many "microtrendID = ?" as we have ids
            //$macroQueryString = $macroQueryString.'macrotrend.macrotrendID = ? ';
            $macroQueryString = $macroQueryString.'macrotrendID = ? ';

            if($i == $anzMacroIds - 1) {

            } else {
                $macroQueryString = $macroQueryString.'OR ';
            }

            //Checks if ids are numeric
            if(!is_numeric($macroIds[$i])){
                $numericCheck = false;
            }
        }

        if($numericCheck == true) {

            $macroData = array();
            $trendCounter = 0;

            $statement = $this->pdo->prepare("SELECT * FROM macrotrend WHERE ".$macroQueryString);
            $statement->execute($macroIds);


            while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {

                $macroPoint = [
                    "ID" => intval($row['macrotrendID']),
                    "NameDE" => $row['title_de'],
                    "DescriptionDE" => $row['description_de'],
                    "NameEN" => $row['title_en'],
                    "DescriptionEN" => $row['description_en'],
                    "Visual" => $row['visual'],
                    "CountMicro" => intval($row['micValue']),
                    "CountMicro30Days" => intval($row['countMicro30Days']),
                    "GrowthMicro30Days" => $row['trendValue'],
                    "Created" => $row['createdAt'],
                    "Updated" => $row['updatedAt'],
                    "Status" => $row['status'],
                    "TrendOneURL" => $TrendOneUrl.$row['megatrendID']."/".$row['macrotrendID'],
                ];
                $macroData[] = $macroPoint;

                //This only happens if we want the chart data
                if ($chart == true) {
                    $macroData[$trendCounter]['ChartData'] = $this->getMacroTrendData(intval($row['macrotrendID']));
                }
                $trendCounter++;
            }

            //Checks if amount of ids is the same as amount of results if not it checks what ids dont have results and creates point for them
            if($anzMacroIds != count($macroData)){
                foreach($macroIds as $index => $h){
                    $pointCheck = false;
                    foreach($macroData as $res => $p){
                        if(intval($macroIds[$index]) == $macroData[$res]['ID']){
                            $pointCheck = true;
                        }

                    }

                    if($pointCheck == false){
                        $macroPoint = [
                            "ID" => intval($macroIds[$index]),
                            "Error" => "Not found",
                        ];
                        $macroData[] = $macroPoint;
                    }
                }

                //Sorts the array
                $point = array();
                foreach($macroData as $key => $row){
                    $point[$key] = $row['ID'];
                }
                array_multisort($point, SORT_ASC, $macroData);
            }

            $reqBuild['response'] = $macroData;

        }else{
            $reqBuild['response'] = $failArray;
            $reqBuild['success'] = false;
        }

        return $reqBuild;
    }


    //Gets data for Microtrend with specified id
    public function getMicroTrend($id){

        $reqBuild = $this->getRequestSuccessWrap();
        $microIds = explode(",",$id);
        $anzMicroIds = count($microIds);
        $failArray = array();
        $TrendOneUrl = '/microtrends/c/';

        $microQueryString = "";
        //Checks if id is numeric
        $numericCheck = true;

        //We build a string which we will set in the query
        for($i = 0; $i < $anzMicroIds; $i++) {
            //Adding as many "microtrendID = ?" as we have ids
            $microQueryString = $microQueryString.'microtrendID = ? ';

            if($i == $anzMicroIds - 1) {

            } else {
                $microQueryString = $microQueryString.'OR ';
            }

            //Checks if ids are numeric
            if(!is_numeric($microIds[$i])){
                $numericCheck = false;
            }
        }

        if($numericCheck == true){

            $microData = array();

            $statement = $this->pdo->prepare("SELECT * FROM microtrend WHERE ".$microQueryString);
            $statement->execute($microIds);

            while($row = $statement->fetch(PDO::FETCH_ASSOC)){
                $extra = $this->getExtraMicrotrendData($row['categoryID'], $row['macrotrendIDs'], $row['industryIDs']);
                $microPoint = [
                    "ID" => intval($row['microtrendID']),
                    "NameDE" => $row['title_de'],
                    "DescriptionDE" => $row['description_de'],
                    "NameEN" => $row['title_en'],
                    "DescriptionEN" => $row['description_en'],
                    "Url" => $row['url'],
                    "TrendOneURL" => $TrendOneUrl.$row['microtrendID'],
                    "Initiators"=> $row['initiators'],
                    "Category"=> $extra['category']['Name'],
                    "Industries" => $extra['industry'],
                    "Context"=> $extra['context'],
                    "CountryCode" => $row['countryCode'],
                    "Visual" => json_decode($row['pictures'], true),
                    "Created" => $row['createdAt'],
                    "Updated" => $row['updatedAt'],
                    "Status" => $row['status']
                ];
                $microData[] = $microPoint;
            }

            //Checks if amount of ids is the same as amount of results if not it checks what ids dont have results and creates point for them
            if($anzMicroIds != count($microData)){
                foreach($microIds as $index => $h){
                    $pointCheck = false;
                    foreach($microData as $res => $p){
                        if(intval($microIds[$index]) == $microData[$res]['ID']){
                            $pointCheck = true;
                        }

                    }

                    if($pointCheck == false){
                        $microPoint = [
                            "ID" => intval($microIds[$index]),
                            "Error" => "Not found",
                        ];
                        $microData[] = $microPoint;
                    }
                }

                //Sorts the array
                $point = array();
                foreach($microData as $key => $row){
                    $point[$key] = $row['ID'];
                }
                array_multisort($point, SORT_ASC, $microData);
            }

            $reqBuild['response'] = $microData;

        }else{
            $reqBuild['response'] = $failArray;
            $reqBuild['success'] = false;
        }
        return $reqBuild;

    }



    private function getExtraMicrotrendData($categoryID, $macrotrendIDs, $industryIDs )
    {
        $returnData = array();
        $macrotrendIDs = json_decode($macrotrendIDs, true);
        $industryIDs = json_decode($industryIDs, true);


        $statementCategory = $this->pdo->prepare("SELECT title_de, title_en FROM category WHERE categoryID = ? AND status = 'active'");
        $statementMacroIDs = $this->pdo->prepare("SELECT megatrend.title_de as megaTitle_de, megatrend.title_en as megaTitle_en, macroTitle_de, macroTitle_en, mac.megatrendID, mac.macrotrendID 
                                                        FROM megatrend 
                                                        INNER JOIN (SELECT macrotrend.title_de as macroTitle_de, macrotrend.title_en as macroTitle_en, macrotrend.megatrendID, macrotrendID 
                                                        FROM macrotrend WHERE macrotrendID = ? AND status = 'active') as mac WHERE mac.megatrendID = megatrend.megatrendID AND megatrend.status = 'active'");
        $statementIndusIDs = $this->pdo->prepare("SELECT title_de, title_en FROM industry WHERE industryID = ? AND status = 'active'");

        //get category Title ///////////////////////////////////
        if(intval($categoryID) == 0)
        {
            $categoryO = [
                'Name'=> "-",
            ];
            $returnData['category'] = $categoryO;
        }else
        {
            $statementCategory->execute(array(intval($categoryID)));
            while ($row = $statementCategory->fetch(PDO::FETCH_ASSOC))
            {   //title_de and title_en are the same
                if(isset($row['title_en']))
                {
                    $categoryO = [
                        'Name'=> $row['title_en'],
                    ];
                }
                else{
                    $categoryO = [
                        'Name'=> "-",
                    ];
                }
                $returnData['category'] = $categoryO;
            }
        }

        //Macro Mega ///////////////////////////////////
        $macroMegaPaar = array();

        $anzMacroIDs = count($macrotrendIDs);
        if($anzMacroIDs == 0)
        {
            $macroO = [
                'MegaName' => "-",
                'TrendOneURL_Mega' => "-",
                'MacroName' => "-",
                'TrendOneURL_Macro'=> "-"
            ];
            $macroMegaPaar[] = $macroO;
        }else
        {
            for($i = 0; $i < $anzMacroIDs; $i++)
            {
                $statementMacroIDs->execute(array($macrotrendIDs[$i]));
                while ($row = $statementMacroIDs->fetch(PDO::FETCH_ASSOC)) {
                    if(isset($row['macroTitle_de']))
                    {
                        $macroO = [
                            'MegaName' => $row['megaTitle_de'],
                            'TrendOneURL_Mega' => "/trendexplorer/tab1/portfolio/0/c/".$row['megatrendID'],
                            'MacroName' => $row['macroTitle_de'],
                            'TrendOneURL_Macro' => "/trendexplorer/tab1/portfolio/0/c/".$row['megatrendID']."/".$row['macrotrendID']
                        ];
                        $macroMegaPaar[] = $macroO;
                    }else{

                    }

                }
            }
        }

        $returnData['context'] = $macroMegaPaar;

        // Industrys ///////////////////////////////////
        $industryNames = array();

        $anzIndustrys = count($industryIDs);
        if($anzIndustrys == 0)
        {
            $induO = [
                'Name' => "-",
            ];
            $industryNames[] = $induO;
        }else
        {
            for($i = 0; $i < $anzIndustrys; $i++)
            {
                $statementIndusIDs->execute(array(intval($industryIDs[$i])));
                while ($row =  $statementIndusIDs->fetch(PDO::FETCH_ASSOC)) {
                    if(isset($row['title_de']))
                    {
                        $induO = [
                            'Name' => $row['title_de'],
                        ];
                        $industryNames[] = $induO;
                    }else{

                    }

                }
            }
        }
        $returnData['industry'] = $industryNames;

        return $returnData;
    }


    //Gets every Mega/Macro/Micro Trend with the key word in it
    public function getSearchTrend($keyWord, $rowsStart, $limit, $order, $country, $table)
    {
        $reqBuild = $this->getRequestSuccessWrap();
        $response = array();
        $megaData = array();
        $macroData = array();
        $microData = array();

        $keyWordArray = $this->getWords($keyWord);
        $amountWords = count($keyWordArray);
        if($amountWords == 0){
            return [
                "success" => false,
                'message' => "No valid search word",
                'code' => 400
            ];
        }

        //Build part of the query
        $searchQuery = "(title_de LIKE ? OR title_en LIKE ? OR description_de LIKE ? OR description_en LIKE ?)";
        $searchQuery .= str_repeat(" OR (title_de LIKE ? OR title_en LIKE ? OR description_de LIKE ? OR description_en LIKE ?)",$amountWords-1);

        //Building part of the execution array
        $exeArray = array();
        for($i = 0; $i < $amountWords; $i++) {
            array_push($exeArray, $keyWordArray[$i], $keyWordArray[$i],$keyWordArray[$i], $keyWordArray[$i]);
        }

        $TrendOneUrlMega = '/trendexplorer/tab1/portfolio/0/c/';
        $TrendOneUrlMacro = '/trendexplorer/tab1/portfolio/0/c/';
        $TrendOneUrlMicro = '/microtrends/c/';


        if($order == "recent"){
            $orderPara = 'ORDER BY updatedAt DESC';
        }else{
            $orderPara = "";
        }

        if($table['Mega'] == 1) {
            //Building SQL Querys
            $statementMega = $this->pdo->prepare("SELECT * FROM megatrend WHERE ".$searchQuery." " . $orderPara . "  ");
            $countRequestMega = $this->pdo->prepare("SELECT COUNT(megatrendID) AS results FROM megatrend WHERE ".$searchQuery.";");
        }

        if($table['Macro'] == 1) {
            $statementMacro = $this->pdo->prepare("SELECT * FROM macrotrend WHERE ".$searchQuery." " . $orderPara . "  ");
            $countRequestMacro = $this->pdo->prepare("SELECT COUNT(macrotrendID) AS results FROM macrotrend WHERE ".$searchQuery.";");
        }

        if($table['Micro'] == 1) {
            //Check if country was set
            if ($country != 0) {
                $statementMicro = $this->pdo->prepare("SELECT englishName, germanName , mic.* FROM countryCodes INNER JOIN (SELECT * FROM microtrend WHERE (".$searchQuery.") AND countryCode = ? " . $orderPara . " LIMIT " . $rowsStart . ", " . $limit . ") AS mic ON mic.countryCode = countryCodes.code;");
                $countRequestMicro = $this->pdo->prepare("SELECT COUNT(microtrendID) AS results FROM microtrend WHERE (".$searchQuery.") AND countryCode = ?;");
            } else {
                $statementMicro = $this->pdo->prepare("SELECT englishName, germanName , mic.* FROM countryCodes INNER JOIN (SELECT * FROM microtrend WHERE (".$searchQuery.") " . $orderPara . " LIMIT " . $rowsStart . ", " . $limit . ") AS mic ON mic.countryCode = countryCodes.code;");
                $countRequestMicro = $this->pdo->prepare("SELECT COUNT(microtrendID) AS results FROM microtrend WHERE ".$searchQuery.";");
            }
        }

        if($table['Mega'] == 1) {
            //Getting Megatrends Data
            $countRequestMega->execute($exeArray);
            while ($row = $countRequestMega->fetch(PDO::FETCH_ASSOC)) {
                $megaData['Total'] = intval($row['results']);
            }
            //Sets element at this point to let it appear before results in result.
            $megaData['Count'] = 0;

            $statementMega->execute($exeArray);
            while ($row = $statementMega->fetch(PDO::FETCH_ASSOC)) {
                $megaPoint = [
                    "ID" => intval($row['megatrendID']),
                    "NameDE" => $row['title_de'],
                    "DescriptionDE" => $row['description_de'],
                    "NameEN" => $row['title_en'],
                    "DescriptionEN" => $row['description_en'],
                    "Visual" => $row['visual'],
                    "CountMicro" => intval($row['countMicro']),
                    "CountMicro30Days" => intval($row['countMicro30Days']),
                    "GrowthMicro30Days" => $row['growthMicro30Days'],
                    "Created" => $row['createdAt'],
                    "Updated" => $row['updatedAt'],
                    "Status" => $row['status'],
                    "TrendOneURL" => $TrendOneUrlMega . $row['megatrendID'],
                ];
                $megaData['results'][] = $megaPoint;
            }

            //Counting the results
            if(isset($megaData['results'])){
                $megaData['Count'] = count($megaData['results']);
            }else{
                $megaData['results'] = [];
            }

        }

        if($table['Macro'] == 1 ) {
            //Getting Macrotrends Data
            $countRequestMacro->execute($exeArray);
            while ($row = $countRequestMacro->fetch(PDO::FETCH_ASSOC)) {
                $macroData['Total'] = intval($row['results']);
            }

            $macroData['Count'] = 0;

            $statementMacro->execute($exeArray);
            while ($row = $statementMacro->fetch(PDO::FETCH_ASSOC)) {
                $macroPoint = [
                    "ID" => intval($row['macrotrendID']),
                    "NameDE" => $row['title_de'],
                    "DescriptionDE" => $row['description_de'],
                    "NameEN" => $row['title_en'],
                    "DescriptionEN" => $row['description_en'],
                    "Visual" => $row['visual'],
                    "CountMicro" => intval($row['micValue']),
                    "CountMicro30Days" => intval($row['countMicro30Days']),
                    "GrowthMicro30Days" => $row['trendValue'],
                    "Created" => $row['createdAt'],
                    "Updated" => $row['updatedAt'],
                    "Status" => $row['status'],
                    "TrendOneURL" => $TrendOneUrlMacro . $row['megatrendID'] . "/" . $row['macrotrendID'],
                ];
                $macroData['results'][] = $macroPoint;
            }
            //Counting the results
            if(isset($macroData['results'])){
                $macroData['Count'] = count($macroData['results']);
            }else{
                $macroData['results'] = [];
            }

        }


        if($table['Micro'] == 1) {
            //Getting Microtrends Data
            if ($country != 0) {
                array_push($exeArray, $country);
                $countRequestMicro->execute($exeArray);
            } else {
                $countRequestMicro->execute($exeArray);
            }

            while ($row = $countRequestMicro->fetch(PDO::FETCH_ASSOC)) {
                $microData['Total'] = intval($row['results']);
            }
            $microData['Count'] = 0;

            //Check if $rowStart is acceptable
            if ($microData['Total'] > $rowsStart) {
                if ($country != 0) {
                    $statementMicro->execute($exeArray);
                } else {
                    $statementMicro->execute($exeArray);
                }

                while ($row = $statementMicro->fetch(PDO::FETCH_ASSOC)) {
                    $microPoint = [
                        "ID" => intval($row['microtrendID']),
                        "NameDE" => $row['title_de'],
                        "DescriptionDE" => $row['description_de'],
                        "NameEN" => $row['title_en'],
                        "DescriptionEN" => $row['description_en'],
                        "Visual" => json_decode($row['pictures'], true),
                        "CountryCode" => $row['countryCode'],
                        "CountryGER" => $row['germanName'],
                        "CountryEN" => $row['englishName'],
                        "Created" => $row['createdAt'],
                        "Updated" => $row['updatedAt'],
                        "Status" => $row['status'],
                        "TrendOneURL" => $TrendOneUrlMicro . $row['microtrendID'],
                    ];
                    $microData['results'][] = $microPoint;
                }
                //Counting the results
                $microData['Count'] = count($microData['results']);
            } else {
                if($microData['Total'] != 0){
                    $microData['results'][] = "Not enough row for given start";
                }else{
                    $microData['results'] = [];
                }
                $microData['Count'] = 0;
            }
        }

        //Building every response together
        if($table['Mega'] == 1){
            $response['megatrends'] = $megaData;
        }
        if($table['Macro'] == 1){
            $response['macrotrends'] = $macroData;
        }
        if($table['Micro'] == 1){
            $response['microtrends'] = $microData;
        }


        $reqBuild['response'] = $response;

        //Returning Data
        return $reqBuild;
    }

    private function getWords($query){
        //Splits the search word into chars
        $charArray = preg_split("##u", $query, -1, PREG_SPLIT_NO_EMPTY);
        $len = count($charArray);

        //Array which hold all word we return
        $wordArray = array();
        //Gets set true if a new word begins
        $isNewWord = false;
        $word = "%";
        //Loop runs through every char in the array and looks for '"' or " ", to know when new word should start to be created
        for($i = 0; $i < $len; $i++){
            switch($charArray[$i]){
                case "'":
                case '"':
                    //If word are not empty or more than 2 character then add them to the return array
                    if(($word != "$")&&(strlen($word)>3)){
                        $word.= "%";
                        array_push($wordArray, $word);
                    }
                    $word = "%";
                    //If $isNewWord = false, then we set it true her to show that a new word has started
                    //If $isNewWord = true, then we set it false, to show that a word has ended
                    if($isNewWord){
                        $isNewWord = false;
                    }else{
                        $isNewWord = true;
                    }
                    break;
                case " ":
                    //If we are currently in a word, then just add the char otherwise start a new word
                    if($isNewWord){
                        $word.= $charArray[$i];
                    }else{
                        if(($word != "%")&&(strlen($word)>3)){
                            $word.= "%";
                            array_push($wordArray, $word);
                        }
                        $word = "%";
                    }
                    break;
                default:
                    $word.= $charArray[$i];
                    break;
            }
        }
        //Add the last word, that doesn't get caught by the loop
        if(($word != "%")&&(strlen($word)>3)){
            $word.= "%";
            array_push($wordArray, $word);
        }

        return $wordArray;

    }


    //Gets every Mega/Macro/Micro Trend with the key word in it
    public function getNewsTrend($rowsStart, $limit, $order, $country, $table)
    {
        $reqBuild = $this->getRequestSuccessWrap();
        $response = array();
        $megaData = array();
        $macroData = array();
        $microData = array();
        $createdDate = NULL;

        $TrendOneUrlMega = '/trendexplorer/tab1/portfolio/0/c/';
        $TrendOneUrlMacro = '/trendexplorer/tab1/portfolio/0/c/';
        $TrendOneUrlMicro = '/microtrends/c/';


        if($order == "recent"){
            $orderPara = 'ORDER BY createdAt DESC';
        }else{
            $orderPara = "";
        }

        if($table['Mega'] == 1) {
            //Building SQL Querys
            $statementMega = $this->pdo->prepare("SELECT * FROM megatrend " . $orderPara . "  ");
            $countRequestMega = $this->pdo->prepare("SELECT COUNT(megatrendID) AS results FROM megatrend;");
        }

        if($table['Macro'] == 1) {
            $statementMacro = $this->pdo->prepare("SELECT * FROM macrotrend " . $orderPara . "  ");
            $countRequestMacro = $this->pdo->prepare("SELECT COUNT(macrotrendID) AS results FROM macrotrend;");
        }

	    if($table['Micro'] == 1) {
	    $orderPara .= ", microtrendID ASC";
            //Check if country was set
            if ($rowsStart === NULL) {
                if ($country != 0) {
                    $statementMicro = $this->pdo->prepare("SELECT englishName, germanName , mic.* FROM countryCodes INNER JOIN (SELECT * FROM microtrend WHERE countryCode = ? " . $orderPara . " LIMIT " . $limit .") AS mic ON mic.countryCode = countryCodes.code;");
                    $countRequestMicro = $this->pdo->prepare("SELECT COUNT(microtrendID) AS results FROM microtrend WHERE countryCode = ?;");
                } else {
                    $statementMicro = $this->pdo->prepare("SELECT englishName, germanName , mic.* FROM countryCodes INNER JOIN (SELECT * FROM microtrend " . $orderPara . " LIMIT " . $limit .") AS mic ON mic.countryCode = countryCodes.code;");
                    $countRequestMicro = $this->pdo->prepare("SELECT COUNT(microtrendID) AS results FROM microtrend;");
                }
            } else {
                $statementDate = $this->pdo->prepare("SELECT createdAt FROM microtrend WHERE microtrendID = " . $rowsStart . " LIMIT 1");
                $statementDate->execute();
                while ($row = $statementDate->fetch(PDO::FETCH_ASSOC)) { $createdDate = $row['createdAt']; }

                if ($createdDate !== NULL) {
                    if ($country != 0) {
                        $statementMicro = $this->pdo->prepare("SELECT englishName, germanName , mic.* FROM countryCodes INNER JOIN (SELECT * FROM microtrend WHERE countryCode = ? AND createdAt <= ? AND (createdAt < ? OR microtrendID > " . $rowsStart . ") " . $orderPara . " LIMIT " . $limit .") AS mic ON mic.countryCode = countryCodes.code;");
                        $countRequestMicro = $this->pdo->prepare("SELECT COUNT(microtrendID) AS results FROM microtrend WHERE countryCode = ?;");
                    } else {
                        $statementMicro = $this->pdo->prepare("SELECT englishName, germanName , mic.* FROM countryCodes INNER JOIN (SELECT * FROM microtrend WHERE createdAt <= ? AND (createdAt < ? OR microtrendID > " . $rowsStart . ") " . $orderPara . " LIMIT " . $limit .") AS mic ON mic.countryCode = countryCodes.code;");
                        $countRequestMicro = $this->pdo->prepare("SELECT COUNT(microtrendID) AS results FROM microtrend;");
                    }
                }
                else {
                    if ($country != 0) {
                        $statementMicro = $this->pdo->prepare("SELECT englishName, germanName , mic.* FROM countryCodes INNER JOIN (SELECT * FROM microtrend WHERE countryCode = ? AND microtrendID > " . $rowsStart . " " . $orderPara . " LIMIT " . $limit .") AS mic ON mic.countryCode = countryCodes.code;");
                        $countRequestMicro = $this->pdo->prepare("SELECT COUNT(microtrendID) AS results FROM microtrend WHERE countryCode = ?;");
                    } else {
                        $statementMicro = $this->pdo->prepare("SELECT englishName, germanName , mic.* FROM countryCodes INNER JOIN (SELECT * FROM microtrend WHERE microtrendID > " . $rowsStart . " " . $orderPara . " LIMIT " . $limit .") AS mic ON mic.countryCode = countryCodes.code;");
                        $countRequestMicro = $this->pdo->prepare("SELECT COUNT(microtrendID) AS results FROM microtrend;");
                    }
                }
            }
        }

        if($table['Mega'] == 1) {
            //Getting Megatrends Data
            $countRequestMega->execute();
            while ($row = $countRequestMega->fetch(PDO::FETCH_ASSOC)) {
                $megaData['Total'] = intval($row['results']);
            }
            //Sets element at this point to let it appear before results in result.
            $megaData['Count'] = 0;

            $statementMega->execute();
            while ($row = $statementMega->fetch(PDO::FETCH_ASSOC)) {
                $megaPoint = [
                    "ID" => intval($row['megatrendID']),
                    "NameDE" => $row['title_de'],
                    "DescriptionDE" => $row['description_de'],
                    "NameEN" => $row['title_en'],
                    "DescriptionEN" => $row['description_en'],
                    "Visual" => $row['visual'],
                    "CountMicro" => intval($row['countMicro']),
                    "CountMicro30Days" => intval($row['countMicro30Days']),
                    "GrowthMicro30Days" => $row['growthMicro30Days'],
                    "Created" => $row['createdAt'],
                    "Updated" => $row['updatedAt'],
                    "Status" => $row['status'],
                    "TrendOneURL" => $TrendOneUrlMega . $row['megatrendID'],
                ];
                $megaData['results'][] = $megaPoint;
            }

            //Counting the results
            if(isset($megaData['results'])){
                $megaData['Count'] = count($megaData['results']);
            }else{
                $megaData['results'] = [];
            }

        }

        if($table['Macro'] == 1 ) {
            //Getting Macrotrends Data
            $countRequestMacro->execute();
            while ($row = $countRequestMacro->fetch(PDO::FETCH_ASSOC)) {
                $macroData['Total'] = intval($row['results']);
            }

            $macroData['Count'] = 0;

            $statementMacro->execute();
            while ($row = $statementMacro->fetch(PDO::FETCH_ASSOC)) {
                $macroPoint = [
                    "ID" => intval($row['macrotrendID']),
                    "NameDE" => $row['title_de'],
                    "DescriptionDE" => $row['description_de'],
                    "NameEN" => $row['title_en'],
                    "DescriptionEN" => $row['description_en'],
                    "Visual" => $row['visual'],
                    "CountMicro" => intval($row['micValue']),
                    "CountMicro30Days" => intval($row['countMicro30Days']),
                    "GrowthMicro30Days" => $row['trendValue'],
                    "Created" => $row['createdAt'],
                    "Updated" => $row['updatedAt'],
                    "Status" => $row['status'],
                    "TrendOneURL" => $TrendOneUrlMacro . $row['megatrendID'] . "/" . $row['macrotrendID'],
                ];
                $macroData['results'][] = $macroPoint;
            }
            //Counting the results
            if(isset($macroData['results'])){
                $macroData['Count'] = count($macroData['results']);
            }else{
                $macroData['results'] = [];
            }

        }


        if($table['Micro'] == 1) {
            //Getting Microtrends Data
            if ($country != 0) {
                $countRequestMicro->execute(array($country));
            } else {
                $countRequestMicro->execute();
            }

            while ($row = $countRequestMicro->fetch(PDO::FETCH_ASSOC)) {
                $microData['Total'] = intval($row['results']);
            }
            $microData['Count'] = 0;

            if ($rowsStart === NULL) {
                if ($country != 0) {
                    $statementMicro->execute(array($country));
                } else {
                    $statementMicro->execute(array());
                }
            }
            else {
                if ($createdDate !== NULL) {
                    if ($country != 0) {
                        $statementMicro->execute(array($country, $createdDate, $createdDate));
                    } else {
                        $statementMicro->execute(array($createdDate, $createdDate));
                    }
                }
                else {
                    if ($country != 0) {
                        $statementMicro->execute(array($country));
                    } else {
                        $statementMicro->execute(array());
                    }
                }
            }

            while ($row = $statementMicro->fetch(PDO::FETCH_ASSOC)) {
                $microPoint = [
                    "ID" => intval($row['microtrendID']),
                    "NameDE" => $row['title_de'],
                    "DescriptionDE" => $row['description_de'],
                    "NameEN" => $row['title_en'],
                    "DescriptionEN" => $row['description_en'],
                    "Visual" => json_decode($row['pictures'], true),
                    "CountryCode" => $row['countryCode'],
                    "CountryGER" => $row['germanName'],
                    "CountryEN" => $row['englishName'],
                    "Created" => $row['createdAt'],
                    "Updated" => $row['updatedAt'],
                    "Status" => $row['status'],
                    "TrendOneURL" => $TrendOneUrlMicro . $row['microtrendID'],
                ];
                $microData['results'][] = $microPoint;
            }
            //Counting the results
	    $microData['Count'] = count($microData['results']);

        }

        //Building every response together
        if($table['Mega'] == 1){
            $response['megatrends'] = $megaData;
        }
        if($table['Macro'] == 1){
            $response['macrotrends'] = $macroData;
        }
        if($table['Micro'] == 1){
            $response['microtrends'] = $microData;
        }
        /*$response['megatrends'] = $megaData;
        $response['macrotrends'] = $macroData;
        $response['microtrends'] = $microData;*/

        $reqBuild['response'] = $response;

        //Returning Data
        return $reqBuild;
    }

    //This function get the data for building the Macrotrend Column/Line Chart.
    public function getMacroTrendData($id){

        $timeNow = $this->getLastUpdated();

        $startTime = 0;
        $endTime = 0;

        //Time span in months + 1 because of 0
        $chartTimeSpan = 11;


        //$amountTotal gets the amount of Microtrend the Macrotrend had 12 months ago
        $amountTotal = $this->pdo->prepare("SELECT COUNT(microtrendID) AS anz FROM microtrend WHERE status = 'active' AND macrotrendIDs REGEXP ? AND createdAt <= ?");
        //$createdAmountInterval gets the amount of Microtrend created every month from the date we last updated the DB till 12 months ago
        $createdAmountInterval = $this->pdo->prepare("SELECT COUNT(microtrendID) as anz, DATE_FORMAT(createdAt, '%Y-%m-%d') as date FROM microtrend WHERE status = 'active' AND macrotrendIDs REGEXP ? AND createdAt >= DATE_SUB(?, INTERVAL 1 YEAR) GROUP BY date ;");


        $currTotal = 0;
        $amTotal = array();
        $creaAmInt = array();
        $timeLabel = array();
        $creaProMonth = array();
        $updaAmInt = array();


        //Gets created to this point once //This is the date the chart starts at
        $endTime = $this->getTimeMacroChart($timeNow, $chartTimeSpan+1, 'Y-m-d H:i:s');

        $amountTotal->execute(array("[[:space:] \[]".$id."[,\]]", $endTime));
        while ($row = $amountTotal->fetch(PDO::FETCH_ASSOC)){
            $currTotal=intval($row['anz']);
        }

        $startTime = $this->getTimeMacroChart($timeNow, $chartTimeSpan+1, 'Y-m-d');

        $createdAmountInterval->execute(array('[[:space:] \[]'.$id.'[,\]]', $timeNow));
        while ($row = $createdAmountInterval->fetch(PDO::FETCH_ASSOC)){
            $createObj = [
                'count' => intval($row['anz']),
                'date' => $row['date'],
            ];
            $creaAmInt[] = $createObj;
        }

        //Holds amount of microtrends in a month
        $microAnz = 0;

        for($i = $chartTimeSpan; $i>=0; $i--)
        {
            $endTime = $this->getTimeMacroChart($timeNow, $i, 'Y-m-d');
            foreach($creaAmInt as $res => $r){
                if($endTime >= $creaAmInt[$res]['date'] && $creaAmInt[$res]['date'] > $startTime){
                    $microAnz = $microAnz + $creaAmInt[$res]['count'];
                }
            }

            $creaProMonth[] = $microAnz;
            $currTotal = $currTotal + $microAnz;

            $amTotal[] = $currTotal;

            $date = new DateTime($timeNow);
            $date->modify('-'.$i.' month');
            $timeLabel[] = $date->format('Y-m-d');

            //Resetting micro count for next loop
            $microAnz = 0;
            $startTime = $endTime;
        }


        $returnData = array();
        $returnData['LineData'] = $amTotal;
        $returnData['ColumnData'] = $creaProMonth;
        $returnData['TimeLabel'] = $timeLabel;

        return $returnData;

    }

    public function getMacroMicroLast30Days($id){
        $currTotal = 0;

        $timeNow = $this->getLastUpdated();
        $endTime = $this->getTimeMacroChart($timeNow, 1,'Y-m-d');

        $amountTotal = $this->pdo->prepare("SELECT COUNT(microtrendID) AS anz FROM microtrend WHERE status = 'active' AND macrotrendIDs REGEXP ? AND createdAt >= ?");
        $amountTotal->execute(array("[[:space:] \[]".$id."[,\]]", $endTime));

        while ($row = $amountTotal->fetch(PDO::FETCH_ASSOC)){
            $currTotal=intval($row['anz']);
        }
        return $currTotal;
    }


    public function getTimeMacroChart($time, $timeBack, $formatString){

        $date = new DateTime($time);
        $date->modify('-'.$timeBack.' month');
        $date = $date->format($formatString);   //'Y-m-d H:i:s'

        return $date;
    }


    //Get Request Wrap
    public function getRequestSuccessWrap()
    {
        $reqSuccess = [
            "success" => true,
            "response" => "-"
        ];

        return $reqSuccess;
    }


    public function getLastUpdated(){

        $lastUpdate = array();

        //Gets the time at which the update file was last updated and uses that for the chart endpoint
        $time = $this->pdo->prepare("SELECT updateDate FROM lastUpdated");
        $time->execute();
        while($row = $time->fetch(PDO::FETCH_ASSOC))
        {
            $lastUpdate[] = $row;
        }

        return $lastUpdate[0]['updateDate'];

    }


    //Calculates the percentage growth
    public function getGrowth($anzUpdatedThreeMonths, $anzUpdatedOneMonth){
        $trendValue = 0;

        $anzUpdatedThreeMonths = $anzUpdatedThreeMonths / 3;
        if($anzUpdatedThreeMonths == $anzUpdatedOneMonth)
        {
            //Puts result onto return array
            $trendValue  = "0";
        }
        else
        {   //Calculates how much percent the macrotrend has grown compared to the three months before that
            $pr = $anzUpdatedOneMonth - $anzUpdatedThreeMonths;
            if($anzUpdatedThreeMonths != 0)
            {
                $pr = $pr / $anzUpdatedThreeMonths;
            }
            $pr = $pr * 100;
            $pr = round($pr, 2);
            //Puts result onto return array
            $trendValue = "".$pr;
        }

        return $trendValue;
    }

}
