<?php

namespace app\classes;

class PDODatabase {

    protected $con;
    private static $instance;
    private $time;
    private $lastInsertId = 0;
    private $realTime = false;

    protected function __construct() {
        try {
            $this->con = new \PDO(\app\constants\PDODatabaseConfig::DNS, \app\constants\PDODatabaseConfig::USER, \app\constants\PDODatabaseConfig::PASS);
            $this->con->query('SET NAMES utf8');
            $this->time = date("Y-m-d H:i:s");
        } catch (\PDOException $ex) {
            printError($ex->getMessage());
            die();
        }
    }

    /**
     * @return PDODatabase
     */
    public static function getInstance() {
        if (PDODatabase::$instance == NULL) {
            PDODatabase::$instance = new PDODatabase();
        }
        return PDODatabase::$instance;
    }

    /**
     * @return \PDO
     */
    public function getConnection() {
        return $this->con;
    }

    /**
     * Add new record to database.
     * @param string $table <p>database table name</p>   
     * @param array $dataArray <p>array of variables to insert (columnName => value)</p>   
     * @param string $excludeFromDataArray <p>array of variables to exclude in update given in $dataArray (columnName1, columnName2, ...)</p> 
     * @param string $idColumnName <p>identificator column name</p>        
     * @return void <p>No value is returned.</p>     
     */
    public function addRecord($table, array $dataArray, array $excludeFromDataArray = array(), $idColumnName = "id", $autoTimeMark = true) {
        $columns = "";
        $values = "";
        $command = "INSERT INTO " . $table . "(";
        foreach ($dataArray as $var => $post) {
            if (!in_array($var, $excludeFromDataArray)) {
                $columns .= "`" . $var . "`, ";
                $values .= ":" . $var . ", ";
                $dataArray[":" . $var] = str_replace(" ", "", $dataArray[$var] === "") ? null : $dataArray[$var];
                unset($dataArray[$var]);
            } else {
                unset($dataArray[$var]);
            }
        }
        $sql = $command . substr($columns, 0, -2) . ") VALUES(" . substr($values, 0, -2) . ");";


        $query = $this->con->prepare($sql);

        foreach ($dataArray as $key => &$val) {
            $query->bindParam($key, $val, is_int($val) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
        }

        if (!$query->execute()) {
            throw new \Exception(trim($query->errorInfo()[2]) === "" ? $query->errorInfo()[0] : $query->errorInfo()[2], $query->errorInfo()[1]);
        }

        if ($idColumnName !== false && $autoTimeMark) {
            $time = $this->getTime();
            $email = $this->getLoggedEmail();
            $id = $this->getLastInsertId();
            $idCondition = " WHERE " . $idColumnName . "=:" . $idColumnName;

            try {
                $query2 = $this->con->prepare("UPDATE " . $table . " SET last_update=:last_update " . $idCondition);
                $query2->bindParam(":last_update", $time, \PDO::PARAM_STR);
                $query2->bindParam(":" . $idColumnName, $id, is_int($id) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
                @$query2->execute();
            } catch (\Exception $ex) {
                // no last update column
            }

            if ($this->getLoggedEmail() !== false) {
                try {
                    $query3 = $this->con->prepare("UPDATE " . $table . " SET last_update_by=:last_update_by" . $idCondition);
                    $query3->bindParam(":last_update_by", $email, \PDO::PARAM_STR);
                    $query3->bindParam(":" . $idColumnName, $id, is_int($id) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
                    @$query3->execute();
                } catch (\Exception $ex) {
                    //no last update by column
                }
                try {
                    $query4 = $this->con->prepare("UPDATE " . $table . " SET created_by=:created_by" . $idCondition);
                    $query4->bindParam(":created_by", $email, \PDO::PARAM_STR);
                    $query4->bindParam(":" . $idColumnName, $id, is_int($id) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
                    @$query4->execute();
                } catch (\Exception $ex) {
                    //no created by column
                }
            }
            try {
                $query5 = $this->con->prepare("UPDATE " . $table . " SET created=:created " . $idCondition);
                $query5->bindParam(":created", $time, \PDO::PARAM_STR);
                $query5->bindParam(":" . $idColumnName, $id, is_int($id) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
                @$query5->execute();
            } catch (\Exception $ex) {
                //no created column
            }
        }
    }

    /**
     * Delete element from database. Not recommended to use this function!
     * @return void <p>No value is returned.</p>     
     */
    public function deleteRecord($table, $id, $idColumnName = "id") {
        $sql = "DELETE FROM " . $table . " WHERE " . $idColumnName . "= :" . $idColumnName . ";";
        $query = $this->con->prepare($sql);
        if ($query->execute(array(":id" => $id))) {
            return true;
        }
        return false;
    }

    /**
     * Updates element in database.
     * @param string $table <p>database table name</p>    
     * @param array $dataArray <p>array of variables to update (columnName => value)</p>   
     * @param mixed $id <p>id value of element to update</p>
     * @param mixed $idColumnName <p>identificator column name</p>      
     * @param string $exclude <p>array of variables to exclude in update given in $dataArray (columnName1, columnName2, ...)</p>    
     * @return void <p>No value is returned.</p>   
     */
    public function updateRecord($table, array $dataArray, $id, $idColumnName, array $exclude = array()) {
        $command = "UPDATE " . $table . " SET ";
        foreach ($dataArray as $var => $post) {
            if (!in_array($var, $exclude)) {
                $command .= "`" . $var . "`" . " = :" . $var . ", ";
                $dataArray[":" . $var] = str_replace(" ", "", $post === "") ? null : $post;
            }
            unset($dataArray[$var]);
        }
        $dataArray[":" . $idColumnName] = $id;

        $sql = substr($command, 0, -2);

        if (is_array($id)) {
            $c = 0;
            $ids = array();
            foreach ($id as $key => $val) {
                $c++;
                $dataArray[":id" . $c] = $val;
                $ids[] = ":id" . $c;
            }
            $idCondition = " WHERE " . $idColumnName . " IN(" . implode(", ", $ids) . ")";
        } else {
            $ids = array(":" . $idColumnName => $id);
            $dataArray[":" . $idColumnName] = $id;
            $idCondition = " WHERE " . $idColumnName . " = :" . $idColumnName . ";";
        }

        $sql .= $idCondition;

        $query = $this->con->prepare($sql);

        foreach ($dataArray as $key => &$val) {
            $query->bindParam($key, $val, is_int($val) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
        }

        if (!$query->execute()) {
            throw new \Exception(trim($query->errorInfo()[2]) === "" ? $query->errorInfo()[0] : $query->errorInfo()[2], $query->errorInfo()[1]);
        }

        $time = $this->getTime();
        $email = $this->getLoggedEmail();

        try {
            $query2 = $this->con->prepare("UPDATE " . $table . " SET last_update=:last_update " . $idCondition);
            $query2->bindParam(":last_update", $time, \PDO::PARAM_STR);
            foreach ($ids as $key => &$val) {
                $query2->bindParam($key, $val, is_int($val) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
            }
            @$query2->execute();
        } catch (\Exception $ex) {
            //no last update column
        }

        if ($this->getLoggedEmail() !== false) {
            try {
                $query3 = $this->con->prepare("UPDATE " . $table . " SET last_update_by=:last_update_by" . $idCondition);
                $query3->bindParam(":last_update_by", $email, \PDO::PARAM_STR);
                foreach ($ids as $key => &$val) {
                    $query3->bindParam($key, $val, is_int($val) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
                }
                @$query3->execute();
            } catch (\Exception $ex) {
                //no last update by column
            }
        }
    }

    /**
     * Get identificator value (auto increment column) of last inserted record to db.    
     * @return int <p>id value</p>   
     */
    public function getLastInsertId() {
        $lastInsertId = $this->con->lastInsertId();
        if (intval($lastInsertId) !== 0 || !is_numeric($lastInsertId)) {
            $this->lastInsertId = $lastInsertId;
        }
        return $this->lastInsertId;
    }

    /**
     * Get max value in numeric column.   
     * @param string $table <p>table name</p> 
     * @param string $column <p>column name</p> 
     * @return int <p>max value</p>   
     */
    public function getMaxValue($table, $column) {
        $sql = "SELECT MAX($column) FROM $table LIMIT 1";
        $query = $this->con->prepare($sql);
        if (!$query->execute()) {
            return NULL;
        }
        $max = $query->fetch();
        if (empty($max[0])) {
            $value = NULL;
        } else {
            $value = $max[0];
        }
        return $value;
    }

    public function getRow($table, $id, $idColumnName = "id", $condition = false, array $bindArray = array(), $customSelection = false) {
        if ($condition === false && $customSelection === false) {
            $sql = "SELECT * FROM $table WHERE $idColumnName = :" . $idColumnName;
            $query = $this->con->prepare($sql);
            $bindColumnName = ":" . $idColumnName;
            $query->bindParam($bindColumnName, $id, is_int($id) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
        } else if ($condition !== false) {
            $sql = "SELECT * FROM $table WHERE " . $idColumnName . "= :" . $idColumnName . " AND " . $condition;
            $query = $this->con->prepare($sql);
            foreach ($bindArray as $key => $val) {
                $bindArray[":" . $key] = $val;
                unset($bindArray[$key]);
            }
        } else {
            $sql = $customSelection;
            $query = $this->con->prepare($sql);
            foreach ($bindArray as $key => $val) {
                $bindArray[":" . $key] = $val;
                unset($bindArray[$key]);
            }
        }

//        if ($table === "") {
//            echo $sql;
//            var_dump($bindArray);
//        }

        if (!$query->execute($bindArray)) {
            throw new \Exception(trim($query->errorInfo()[2]) === "" ? $query->errorInfo()[0] : $query->errorInfo()[2], $query->errorInfo()[1]);
        }
        return $query->fetch(\PDO::FETCH_ASSOC);
    }

    public function getRows($table, $idColumn, $customSelection = false, array $bindArray = array(), $fetch_assoc = false, $condition = false) {
        if ($condition === false) {
            $sql = ($customSelection === false) ? "SELECT * FROM " . $table : $customSelection;
        } else {
            $sql = "SELECT * FROM " . $table . " " . $condition;
        }

        $query = $this->con->prepare($sql);
        foreach ($bindArray as $key => $val) {
            $bindArray[":" . $key] = $val;
            unset($bindArray[$key]);
        }
        $rows = array();
        $query->execute($bindArray);
        if (!$query->execute()) {
            throw new \Exception(trim($query->errorInfo()[2]) === "" ? $query->errorInfo()[0] : $query->errorInfo()[2], $query->errorInfo()[1]);
        }

        if ($fetch_assoc) {
            return $query;
        }

        if (is_array($idColumn)) {
            foreach ($query->fetchAll(\PDO::FETCH_ASSOC) as $key => $val) {
                if (!isset($rows[$val[$idColumn[0]]]) || !is_array($rows[$val[$idColumn[0]]])) {
                    $rows[$val[$idColumn[0]]] = array();
                }
                $rows[$val[$idColumn[0]]][$val[$idColumn[1]]] = $val;
            }
        } else {
            foreach ($query->fetchAll(\PDO::FETCH_ASSOC) as $key => $val) {
                $rows[$val[$idColumn]] = $val;
            }
        }
        return $rows;
    }

    /**
     * Get single value from database according to condition. LIMIT 1 is automatically added to query.   
     * @param string $sql <p>sql query string</p> 
     * @param array $bindArray <p>array ov bind variables</p> 
     * @return mixed <p>Returns selected value or NULL if no value was selected.</p>   
     */
    public function getValue($sql, array $bindArray = array()) {
        $query = $this->con->prepare($sql . " LIMIT 1");
        foreach ($bindArray as $key => &$val) {
            $query->bindParam(":" . $key, $val, is_int($val) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
        }
        if (!$query->execute()) {
            throw new \Exception(trim($query->errorInfo()[2]) === "" ? $query->errorInfo()[0] : $query->errorInfo()[2], $query->errorInfo()[1]);
        }
        $row = $query->fetch();
        if (is_array($row)) {
            return $row[0];
        }
        return NULL;
    }

    /**
     * Execute SQL query.  
     * @param string $sql <p>sql query string</p> 
     * @param array $bindArray <p>array ov bind variables</p>  
     * @return void <p>No value is returned.</p>   
     */
    public function sqlCommand($sql, $bindArray) {
        $query = $this->con->prepare($sql);
        if (!$query->execute($bindArray)) {
            throw new \Exception(trim($query->errorInfo()[2]) === "" ? $query->errorInfo()[0] : $query->errorInfo()[2], $query->errorInfo()[1]);
        }
    }

    /**
     * Get time of instance initialization.  
     * @param bool $integer <p>return time as int value</p> 
     * @return mixed <p>Returns time int value or date and time as string.</p>   
     */
    public function getTime($integer = false) {
        if ($this->realTime) {
            return $integer ? time() : date("Y-m-d H:i:s");
        } else {
            return $integer ? strtotime($this->time) : $this->time;
        }
    }

    public function getLoggedEmail() {
        $email = isset($_SESSION["email"]) ? $_SESSION["email"] : false;
        if ($email === false && key_exists("HTTP_HOST", $_SERVER)) {
            try {
                $user = BasicUser::getLoggedUser();
                $email = $user->_get("email");
            } catch (\Exception $ex) {
                
            }
        }
        return $email;
    }

    public function enableRealTime() {
        $this->realTime = true;
    }

    public function disableRealTime() {
        $this->realTime = false;
    }
    
    public function startTransaction() {
        $this->sqlCommand("SET autocommit = 0; START TRANSACTION;", array());
    }

    public function commit() {
        $this->sqlCommand("COMMIT; SET autocommit = 1;", array());
    }
    
    public function rollback() {
        $this->sqlCommand("ROLLBACK; SET autocommit = 1;", array());
    }

}