/Core/Database.php

<?php

namespace Core;

use \PDO;

class Database {

    private static $_instance;
    protected $host;
    protected $userDB;
    protected $passwordDB;
    protected $nameDB;
    protected $db;

    protected $where = [];
    protected $bind = [];
    protected $orWhere = [];
    protected $order = [];
    protected $class;
    protected $one = FALSE;
    protected $type;
    protected $backState;
    protected $joinType = [];
    protected $joinLink = [];
    protected $joinNum = -1;


    /**
     * @param $host
     * @param $userDB
     * @param $passwordDB
     * @param $nameDB
     */
    public function __construct($host, $userDB, $passwordDB, $nameDB) {

        $this->host = $host;
        $this->userDB = $userDB;
        $this->passwordDB = $passwordDB;
        $this->nameDB = $nameDB;
        $this->unique = uniqid();

        self::$_instance = $this;
    }

    public static function getInstance() {
        return self::$_instance;
    }

    /**
     * Do the connection to mysql server
     * @return PDO
     */
    private function connect() {

        if($this->db === null) {
            try {
                $db = new PDO('mysql:dbname='.$this->nameDB.';$host='.$this->host, $this->userDB, $this->passwordDB);
                $db->exec("SET NAMES 'UTF8'");
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $this->db = $db;
            } catch (\PDOException $e ) {
                die($e->getMessage());
            }
        }
        return $this->db;
    }

    /**
     * prepare and execute the query
     * @param $query
     * @internal param params $bind if we used conditions
     * @return array
     */
    private function prepare($query) {

        $method = $this->backState;

        try {
            $sql = $this->connect()->prepare($query);
            $sql->execute($this->bind);

            if($this->type == 'VIEW') {

                $sql->setFetchMode(PDO::FETCH_CLASS, $this->class);

                if($this->one == TRUE) {

                    if(is_null($method)) {
                        $results = $sql->fetch();
                        if($results == false){
                            $results = null;
                        }
                    } else {
                        $result = $sql->fetch();

                        if($result != false){
                            $results = $result->$method();
                        } else {
                            $results = null;
                        }
                    }

                } else {
                    if(is_null($method)) {
                        $results = $sql->fetchAll();
                    } else {
                        $result = $sql->fetchAll();
                        foreach($result as $data) {
                            $results[] = $data->$method();
                        }
                    }
                }
            } elseif($this->type == 'INSERT') {

                $results = $this->connect()->lastInsertId();
            } elseif($this-> type == 'UP_DEL') {

                $results = $sql->rowCount();
            }


        } catch (\PDOException $e) {
            $results = $e->getMessage();
        }

        // KILL CONDITIONS
        $this->reset();
        $sql->closeCursor();

        return $results;
    }

    /**
     * Insert method
     * @param $table string
     * @param array
     * @return array
     */
    public function insert($table, $data = []) {

        $fields = implode(', ', array_keys($data));
        $values = implode(', :', array_keys($data));
        $sql = "INSERT INTO " . $table ." (" . $fields . ") VALUES (:" . $values . ")";

        $this->type = 'INSERT';
        $this->bind = $data;

        return $this->prepare($sql);
    }

    /**
     * @param $table string
     * @param array|as $data as $k => $v
     * @return array
     */
    public function update($table, $data = []) {

        $field = null;
        foreach($data as $k => $v) {

            $field .= $k . ' = ';

            if(is_array($v)) {
                $field .= $k . ' ' . $v['I'] . ', ';
                unset($data[$k]);
            } else {
                $field .= '?, ';
            }
        }

        $fields = substr($field, 0, -2);

        $values = array_values($data);

        $sql = "UPDATE " . $table . " SET " . $fields;
        if($this->where) {
            $sql .= ' WHERE ';
            $sql .= implode(' AND ', $this->where);
        }
        $this->type = 'UP_DEL';
        $this->bind = array_merge($values, $this->bind);

        return $this->prepare($sql);
    }

    /**
     * @param $table
     * @return array
     */
    public function delete($table) {

        $sql = "DELETE FROM " . $table . " WHERE " . implode(' AND ', $this->where);
        $this->type = 'UP_DEL';

        return $this->prepare($sql);
    }

    /**
     * Build the query and send it to $this->prepare()
     * @param $table
     * @param string $columns
     * @param null $limit
     * @param bool $join
     * @return array
     */
    public function get($table, $columns = '*', $limit = null, $join = false) {
        $this->class = '\\App\\Entity\\' . $table. 'Entity';

        $columns = is_array($columns) ? implode(', ', $columns) : $columns;

        $sql = "SELECT " . $columns . " FROM " . $table;

        if($join == true) {
            $sql .= $this->buildJointure();
        }

        if($this->where) {
            $sql .= ' WHERE ';
            $sql .= implode(' AND ', $this->where);
        } elseif($this->orWhere) {
            $sql .= ' WHERE ';
            $sql .= implode(' OR ', $this->orWhere);
        }

        if($this->order) {
            $sql .= ' ORDER BY ';
                $sql .= implode(', ', $this->order);
        }

        $sql .= $this->limit($limit);

        $this->type = 'VIEW';

        return $this->prepare($sql, $this->bind);
    }

    /**
     * put $this->one TRUE and keep it with get() method
     * @param $table
     * @param string $columns
     * @param null $limit
     * @param bool $join
     * @return array
     */
    public function getOne($table, $columns = '*', $limit = null, $join = false) {

        $this->one = TRUE;
        return $this->get($table, $columns, $limit, $join);
    }

    /**
     * instance a method to sent result from prepare to $tableEntity
     * @param $method
     * @return $this
     */
    public function backState($method) {
        $this->backState = $method;
        return $this;
    }

    /**
     * @param $col
     * @param $value
     * @param string $operator
     * @return $this
     */
    public function where($col, $value, $operator = '=') {

        $this->where[] = $col . ' ' . $operator . '  ?';
        $this->bind[] = $value;
        return $this;
    }

    /**
     * @param $col
     * @param $value
     * @param string $operator
     * @return $this
     */
    public function orWhere($col, $value, $operator = '=') {

        $this->orWhere[] = $col . ' ' . $operator . '  ?';
        $this->bind[] = $value;
        return $this;
    }

    /**
     * Build an order by
     * @param $column
     * @param $order
     */
    public function orderBy($column, $order) {

        $orderAllowed = ['ASC', 'DESC'];
        if(!in_array(strtoupper($order), $orderAllowed)) {
            die('Only ASC and DESC are allowed.');
        }
        $this->order[] = $column . ' ' . $order;
    }

    /**
     * Build limit for the query
     * @param $limit
     * @return null|string
     */
    private function limit($limit) {

        if (!isset ($limit))
            return null;
        if(is_array($limit)) {
            return $sql = ' LIMIT ' . $limit[0] . ', ' . $limit[1];
        } else {
           return $sql = ' LIMIT ' . $limit;
        }
    }

    // Function Aggregate
    /**
     * Count()
     * @param $table
     * @param $column
     * @return array
     */
    public function count($table, $column = '*') {

        $instruction = 'COUNT(' . $column .') as cnt';
        return $this->getOne($table, $instruction, null);
    }

    /**
     * MIN()
     * @param $table
     * @param $column
     * @return array
     */
    public function min($table, $column) {

        $instruction = 'MIN(' . $column .') as min';
        return $this->getOne($table, $instruction, null);
    }

    /**
     * MAX()
     * @param $table
     * @param $column
     * @return array
     */
    public function max($table, $column) {

        $instruction = 'MAX(' . $column .') as max';
        return $this->getOne($table, $instruction, null);
    }

    /**
     * SUM()
     * @param $table
     * @param $column
     * @return array
     */
    public function sum($table, $column) {

        $instruction = 'SUM(' . $column .') as sum';
        return $this->getOne($table, $instruction, null);
    }

    /**
     * method to increment a column I as Increment
     * @param string $num
     * @return array
     */
    public function inc($num = '1') {
        return ["I" => "+" . (int)$num];
    }

    /**
     * method to de-increment a column I as Increment
     * @param string $num
     * @return array
     */
    public function dec($num = '1') {
        return ["I" => "-" . (int)$num];
    }

    /**
     * Reset params for a new query
     */
    private function reset() {
        $this->where = [];
        $this->bind = [];
        $this->order = [];
        $this->class = null;
        $this->type = null;
        $this->one = FALSE;
        $this->backState = null;
        $this->joinType = [];
        $this->joinLink = null;
        $this->joinNum = -1;
    }

    public function join($type, $table) {
        $this->joinNum++;
        $this->joinType[] = ' ' . strtoupper($type) . ' JOIN ' . $table;
    }

    public function joinOn($condition) {

        $this->joinLink[$this->joinNum][] = $condition;
    }

    private function buildJointure() {
        var_dump($this->joinLink);
        $build = null;
        foreach ($this->joinType as $k => $v) {
            $build .= $v . ' ON ';
            $i = $k;
            var_dump($i);
            var_dump($this->joinLink[$i]);
            $build .= implode(' AND ', $this->joinLink[$i]);
        }
        return $build;
    }
}