<?php
/**
 * Created by PhpStorm.
 * User: jimmy
 * Date: 29/01/19
 * Time: 16:38
 */

require_once(__DIR__.'/../inc/conf/Conf.php');
require_once(__DIR__.'/../model/Section.php');
require_once(__DIR__.'/../model/Manager.php');
require_once(__DIR__.'/../model/Distribution.php');
require_once(__DIR__.'/../model/Package.php');
require_once(__DIR__.'/../model/Container.php');
require_once(__DIR__.'/../model/Grade.php');
require_once(__DIR__.'/../model/Label.php');
require_once(__DIR__.'/../model/User.php');
require_once(__DIR__.'/../model/ContainerLabel.php');

class DBquery
{

    var $dbh = NULL;

    public function __construct() {
        $this->openConnection();
    }

    /**
     * For open a database connection
     **/
    public function openConnection() {
        $this->dbh = mysqli_connect(Conf::$DB_HOSTNAME, conf::$DB_USERNAME, Conf::$DB_PP, Conf::$DB_NAME);

        if(!$this->dbh){
            die('Could not connect: ' . mysqli_error($this->dbh));
        }

        if (mysqli_connect_errno()) {
            printf("Echec connection : %s\n", mysqli_connect_error());
            exit();
        }
    }

    /**
     * Escapes special characters in a string for use in an SQL statement
     * @param string $value
     * @return string
     */
    public function escape($value) {
        return mysqli_real_escape_string($this->dbh, $value);
    }

    public function getDistributions() {
        $sql = "SELECT * FROM `Distrib` ORDER BY `Distrib`.`name` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $d = new Distribution($row['ID'], $row['name'], $row['version'], $row['manager'], $row['active']);
            array_push($results, $d);
        }
        return $results;
    }

    public function getActiveDistributions() {
        $sql = "SELECT * FROM `Distrib` WHERE active = 1 ORDER BY `Distrib`.`name` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $d = new Distribution($row['ID'], $row['name'], $row['version'], $row['manager'], $row['active']);
            array_push($results, $d);
        }
        return $results;
    }

    public function getActiveDistributionsFullName() {
        $sql = "SELECT name,version  FROM `Distrib` WHERE active = 1 ORDER BY `Distrib`.`name` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            array_push($results, $row['name'].":".$row['version']);
        }
        return $results;
    }

    public function getManagers() {
        $sql = "SELECT * FROM `Manager`;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $m = new Manager($row['ID'], $row['name'], $row['value']);
            array_push($results, $m);
        }
        return $results;
    }

    public function getUsers() {
        $sql = "SELECT * FROM `User`;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $u = new User($row['ID'], $row['login'], $row['gradeId']);
            array_push($results, $u);
        }
        return $results;
    }

    public function getGradeWithLogin($login) {
        $sql = "SELECT * FROM `User` WHERE `login` = '$login';";
        $result = mysqli_query($this->dbh, $sql);

        while($row = mysqli_fetch_assoc($result)){
            $u = new User($row['ID'], $row['login'], $row['gradeId']);
            return $this->getGradeWithId($u->gradeId);
        }
  
        return $this->getLowerGrades();
    }

    public function getLabelsWithGrade($grade) {

        $labels = $this->getLabels();

        $results = array();

        foreach ($labels as $label){ 

            $grd = $this->getGradeWithId($label->gradeId);

            if($grd->level >= $grade->level) {
                array_push($results, $label);
            }

        }

        return $results;
    }

    public function getGrades() {
        $sql = "SELECT * FROM `Grade` ORDER BY `level` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $m = new Grade($row['ID'], $row['name'], $row['level']);
            array_push($results, $m);
        }
        return $results;
    }

    public function getGradeWithId($ID) {
        $sql = "SELECT * FROM `Grade` WHERE `ID` = '$ID';";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $m = new Grade($row['ID'], $row['name'], $row['level']);
            return $m;
        }
        return $results;
    }

    public function getLowerGrades() {
        $sql = "SELECT * FROM `Grade` ORDER BY `level` DESC LIMIT 1;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $m = new Grade($row['ID'], $row['name'], $row['level']);
            return $m;
        }
        return $results;
    }

    public function getMaxGrades() {
        $sql = "SELECT * FROM `Grade` ORDER BY `level` ASC LIMIT 1;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $m = new Grade($row['ID'], $row['name'], $row['level']);
            return $m;
        }
        return $results;
    }

    public function getContainers($type, $author, $labels, $tags, $descriptions) {
        $sql = "SELECT * FROM `Container`";

        if($type == "Singularity" || $type == "Docker") {
            $sql .= " WHERE `type` = '$type'";
        } else {
            $sql .= " WHERE 1";
        }

        if($author != NULL) {
            $sql .= " AND `author` = '$author'";
        }

        if(sizeof($labels) > 0) {
            $subsql = "(SELECT DISTINCT containerId FROM `ContainerLabel` WHERE labelId IN (";

            $index = 0;
            
            foreach ($labels as $label) {
                if($index != 0) {
                    $subsql .= ","; 
                } 

                $subsql .= $label;

                $index++;
            }
            $subsql .= "))";

            $sql .= " AND ID IN " . $subsql;
        }

        if(sizeof($tags) > 0) {
            foreach ($tags as $tag) {
                $sql .= " AND tags LIKE '%".$tag."%'";
            }
        }

        if(sizeof($descriptions) > 0) {
            foreach ($descriptions as $d) {
                $sql .= " AND description LIKE '%".$d."%'";
            }
        }

        $sql .= " ORDER BY `Container`.`date` DESC;";
        
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $cnt = new Container($row['ID'], $row['name'], $row['value'], $row['type'], $row['visibility'], $row['description'], $row['author'], $row['date'], $row['tags']);
            array_push($results, $cnt);
        }
        return $results;
    }

    public function getContainerWithId($id) {
        $sql = "SELECT * FROM `Container` WHERE `ID` = '$id';";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $cnt = new Container($row['ID'], $row['name'], $row['value'], $row['type'], $row['visibility'], $row['description'], $row['author'], $row['date']);
            return $cnt;
        }
        return $results;
    }

    public function getLabels() {
        $sql = "SELECT * FROM `Label`;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $l = new Label($row['ID'], $row['name'], $row['color'], $row['gradeId']);
            array_push($results, $l);
        }
        return $results;
    }

    public function getLabelsWithId($id) {
        $sql = "SELECT * FROM `Label` WHERE `ID` = $id;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $l = new Label($row['ID'], $row['name'], $row['color'], $row['gradeId']);
            return $l;
        }
        return $results;
    }

    public function getMaxSectionArrangement() {
        $sql = "SELECT MAX(arrangement) FROM `Section` WHERE `Section`.`parent` IS NULL;";

        
    }

    public function getLabelWithContainerId($id) {
        $sql = "SELECT * FROM `ContainerLabel` WHERE `containerId` = $id";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $l = $this->getLabelsWithId($row['labelId']);
            array_push($results, $l);
        }
        return $results;
    }

    public function getAllActiveSectionParent() {
        $sql = "SELECT * FROM `Section` WHERE `Section`.`parent` IS NULL AND active = 1 ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $s = new Section($row['ID'], $row['name'], $row['visual'], $row['active'], $row['color'], $row['arrangement'], $row['parent']);
            array_push($results,$s);
        }
        return $results;
    }

    public function getAllSectionParent() {
        $sql = "SELECT * FROM `Section` WHERE `Section`.`parent` IS NULL ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $s = new Section($row['ID'], $row['name'], $row['visual'], $row['active'], $row['color'], $row['arrangement'], $row['parent']);
            array_push($results,$s);
        }
        return $results;
    }

    public function getSectionWhithParent($parent) {
        $sql = "SELECT * FROM `Section` WHERE `Section`.`parent` = '$parent' AND active = 1 ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            array_push($results,array($row['name'], $row['visual'], $row['active'], $row['color']));
        }
        return $results;
    }

    public function getNameSectionWhithParent($parent) {
        $sql = "SELECT name FROM `Section` WHERE `Section`.`parent` = '$parent' AND active = 1 ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            array_push($results,$row['name']);
        }
        return $results;
    }

    public function getAllSectionWhithParent($parent) {
        $sql = "SELECT * FROM `Section` WHERE `Section`.`parent` = '$parent' ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $s = new Section($row['ID'], $row['name'], $row['visual'], $row['active'], $row['color'], $row['arrangement'], $row['parent']);
            array_push($results,$s);
        }
        return $results;
    }

    public function getActiveSection() {
        $sql = "SELECT * FROM `Section` WHERE active = 1 ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $s = new Section($row['ID'], $row['name'], $row['visual'], $row['active'], $row['color'], $row['arrangement'], $row['parent']);
            array_push($results,$s);
        }
        return $results;
    }

    public function getSection() {
        $sql = "SELECT * FROM `Section` ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $s = new Section($row['ID'], $row['name'], $row['visual'], $row['active'], $row['color'], $row['arrangement'], $row['parent']);
            array_push($results,$s);
        }
        return $results;
    }

    public function getNumberPackage($name) {

        $sql = "SELECT COUNT(*) as number FROM `$name` WHERE 1 ";

        $result = mysqli_query($this->dbh, $sql);
        while($row = mysqli_fetch_assoc($result)){
            return $row['number'];
        }
        return 0;
    }

    public function getPackages($distrib, $filter, $patternName, $patternDescription, $limit) {
        if($patternName and $patternDescription) {
            $sql = "SELECT * FROM `$distrib` WHERE `name` LIKE '%$filter%' or `description` LIKE '%$filter%' ORDER BY CASE  WHEN `name` LIKE '$filter' THEN 1 WHEN `name` LIKE '$filter%' THEN 2 WHEN `name` LIKE '%$filter' THEN 4 ELSE 3 END LIMIT $limit;";
        }
        elseif ($patternName) {
            $sql = " SELECT * FROM `$distrib` WHERE `name` LIKE '%$filter%' ORDER BY CASE  WHEN `name` LIKE '$filter' THEN 1 WHEN `name` LIKE '$filter%' THEN 2 WHEN `name` LIKE '%$filter' THEN 4 ELSE 3 END LIMIT $limit;";
        }
        elseif ($patternDescription) {
            $sql = "SELECT * FROM `$distrib` WHERE `description` LIKE '%$filter%' LIMIT $limit;";
        } else {
            return array();
        }

        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $package = new Package($row['name'], $row['version'], $row['description'], $row['doc'], $row['other']);
            array_push($results,$package);
        }
        return $results;
    }

    public function checkTableExist($name) {
        $sql = "SELECT count(*) as number FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'wicopa') AND (TABLE_NAME = '$name')";
        
        $result = mysqli_query($this->dbh, $sql);
        if($result) {

            while($row = mysqli_fetch_assoc($result)){
                return $row['number'];
            }

        } else {
            var_dump(mysqli_error($this->dbh));
            die();
        }
    }

    public function create($object) {

        $result = mysqli_query($this->dbh, $object->getInsert());

        if($result) {
            
        } else {
            var_dump(mysqli_error($this->dbh));
            die();
        }
    }


    public function update($object) {
        $result = mysqli_query($this->dbh, $object->getUpdate());
        if($result) {
            
        } else {
            var_dump(mysqli_error($this->dbh));
            die();
        }
    }


    public function delete($object) {
        $result = mysqli_query($this->dbh, $object->getDelete());
        if($result) {
            
        } else {
            var_dump(mysqli_error($this->dbh));
            die();
        }
    }

    public function verifyPass($username, $pass) {
        if($username == "admin" && Conf::dbEncodePass($pass)==Conf::$KK) {
            return 1;
        } else {
            # if you want guest admin
            //if($username == "guest" && $pass == "2019mbb") {
            //    return 2;
            //} else {
                return 0;
            //}
        }
    }

    public function getNumberDockerContainer() {
        $sql = 'SELECT count(*) as docker FROM `Container` WHERE type = "Docker" AND visibility = 1;';
        $result = mysqli_query($this->dbh, $sql);
        if($result) {

            while($row = mysqli_fetch_assoc($result)){
                return $row['docker'];
            }

        }

        return 0;
    }

    public function getNumberSingularityContainer() {
        $sql = 'SELECT count(*) as singularity FROM `Container` WHERE type = "Singularity" AND visibility = 1';
        $result = mysqli_query($this->dbh, $sql);
        if($result) {

            while($row = mysqli_fetch_assoc($result)){
                return $row['singularity'];
            }

        }

        return 0;
    }


    //LOAD DATA LOCAL INFILE "/home/jimmy/jimmy/web/wicopa/packages/fedora:28/packages.csv" INTO TABLE fedora COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n';
}