<?php

namespace Mnv\Core\Database;

use Closure;
use PDO;
use PDOException;


/**
 * Database - Полезный конструктор запросов и класс PDO
 *
 * Class Database
 * @package Mnv\Database
 */
class Database implements DatabaseInterface
{
    /**
     * Database Version
     *
     * @var string
     */
    const VERSION = '1.6.1';

    /**
     * @var Database|null
     */
    public $pdo = null;

    /**
     * @var mixed Переменные запроса
     */
    protected $select   = '*';
    protected $from     = null;
    protected $where    = null;
    protected $limit    = null;
    protected $offset   = null;
    protected $join     = null;
    protected $orderBy  = null;
    protected $groupBy  = null;
    protected $having   = null;
    protected $grouped  = false;
    protected $numRows  = 0;
    protected $insertId = null;
    protected $query    = null;
    protected $error    = null;
    protected $result   = [];
    protected $prefix   = null;
    protected $map      = null;

    protected $indexKey = null;
    protected $valueKey = null;

    /**
     * @var array Операторы SQL
     */
//    protected $operators = ['=', '!=', '<', '>', '<=', '>=', '<>', 'REGEXP', 'AGAINST'];
    protected $operators = ['=', '!=', '<', '>', '<=', '>=', '<>'];

    /**
     * @var Cache|null
     */
    protected $cache = null;

    /**
     * @var string|null Каталог кеша
     */
    protected $cacheDir = null;

    /**
     * @var int Общее количество запросов
     */
    protected $queryCount = 0;

    /**
     * @var bool
     */
    protected $debug = true;

    /**
     * @var int Общее количество транзакций
     */
    protected $transactionCount = 0;

    private $databaseName;

    private array $_credentials = [
        'dsn'                   => '',
        'driver'                => 'mysql',
        'host'                  => 'localhost',
        'database'              => '',
        'username'              => 'root',
        'password'              => null,
        'charset'               => 'utf8mb4',
        'collation'             => 'utf8mb4_unicode_ci',
        'prefix'                => 'ls_',
        'cacheDir'                => __DIR__ . '/cache/',
        'timestampFormat'       => 'c',
        'readable'              => true,
        'writable'              => true,
        'deletable'             => true,
        'updatable'             => true,
        'return'                => null,
        'debug'                 => false,
        'log'                   => null,
        'profiler'              => false,
    ];
    /**
     * Database constructor.
     *
     * @param array $config
     */
    public function __construct(array $config)
    {
        $this->_credentials['driver']       = $config['driver'] ?? 'mysql';
        $this->_credentials['host']         = $config['host'] ?? 'localhost';
        $this->_credentials['username']     = $config['username'] ?? 'root';
        $this->_credentials['password']     = $config['password'] ?? '';
        $this->_credentials['charset']      = $config['charset'] ?? 'utf8mb4';
        $this->_credentials['collation']    = $config['collation'] ?? 'utf8mb4_general_ci';
        $this->_credentials['port']         = $config['port'] ?? (strpos($config['host'], ':') !== false ? explode(':', $config['host'])[1] : '');
        $this->_credentials['prefix']       = $config['prefix'] ?? '';
        $this->_credentials['database']     = $config['database'];
        $this->_credentials['debug']        = $config['debug'] ?? false;
        $this->_credentials['cacheDir']     = $config['cachedir'] ?? __DIR__ . '/cache/';
        $this->databaseName                 = $config['database'];

        if (in_array($this->_credentials['driver'], ['', 'mysql', 'pgsql'], true)) {
            $this->_credentials['dsn'] = $this->_credentials['driver'] . ':host=' . str_replace(':' . $this->_credentials['port'], '', $this->_credentials['host']) . ';'
                . ($this->_credentials['port'] !== '' ? 'port=' . $this->_credentials['port'] . ';' : '')
                . 'dbname=' . $this->_credentials['database'];
        }
        elseif ($this->_credentials['driver'] === 'sqlite') {
            $this->_credentials['dsn']  = 'sqlite:' . $this->_credentials['database'];
        }
        elseif ($this->_credentials['driver'] === 'oracle') {
            $this->_credentials['dsn']  = 'oci:dbname=' . $this->_credentials['host'] . '/' . $this->_credentials['database'];
        }

        try {
            $this->pdo = new PDO($this->_credentials['dsn'], $this->_credentials['username'], $this->_credentials['password'], $this->_credentials['options'] ?? null);
            $this->pdo->exec("SET NAMES '" . $this->_credentials['charset'] . "' COLLATE '" . $this->_credentials['collation'] . "'");
            $this->pdo->exec("SET CHARACTER SET '" . $this->_credentials['charset'] . "'");
            $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
        } catch (PDOException $e) {
            die('Cannot the connect to Database with PDO. ' . $e->getMessage());
        }

        return $this->pdo;
    }

    final public function newInstance(array $config = []): Database
    {
        return new Database(empty($config) ? $this->_credentials : array_merge($this->_credentials, $config));
    }

    final public function clone(): Database
    {
        return new self($this->_credentials);
    }
    /**
     * Получить версию MySQL:
     * @return mixed
     */
    public function version()
    {
        return $this->pdo->getAttribute( PDO::ATTR_SERVER_VERSION );
    }

    public function mysqlVersion()
    {
        $res = $this->query("SELECT VERSION() AS `version`")->fetch();
        return $res->version;
    }

    /**
     * @param $table
     * @return $this
     */
    public function table($table): Database
    {

        if (is_array($table)) {
            $from = '';
            foreach ($table as $key) {
                $from .= $this->parseTable($key) . ', ';
            }
            $this->from = rtrim($from, ', ');
        } else {
            if (strpos($table, ',') > 0) {
                $tables = explode(',', $table);
                foreach ($tables as $key => &$value) {
                    $value = $this->parseTable(ltrim($value));
                }
                $this->from = implode(', ', $tables);
            } else {
                $this->from = $this->parseTable($table);
            }
        }

        return $this;
    }

    /**
     * @param array|string $fields
     *
     * @return $this
     */
    public function select($fields): Database
    {
        $select = is_array($fields) ? implode(', ', $fields) : $fields;
        $this->optimizeSelect($select);

        return $this;
    }

    /**
     * @param string      $field
     * @param string|null $name
     *
     * @return $this
     */
    public function max(string $field, ?string $name = null): Database
    {
        $column = 'MAX(' . $field . ')' . (!is_null($name) ? ' AS ' . $name : '');
        $this->optimizeSelect($column);

        return $this;
    }

    /**
     * @param string      $field
     * @param string|null $name
     *
     * @return $this
     */
    public function min(string $field, ?string $name = null): Database
    {
        $column = 'MIN(' . $field . ')' . (!is_null($name) ? ' AS ' . $name : '');
        $this->optimizeSelect($column);

        return $this;
    }

    /**
     * @param string      $field
     * @param string|null $name
     *
     * @return $this
     */
    public function sum(string $field, ?string $name = null): Database
    {
        $column = 'SUM(' . $field . ')' . (!is_null($name) ? ' AS ' . $name : '');
        $this->optimizeSelect($column);

        return $this;
    }

    /**
     * @param string      $field
     * @param string|null $name
     *
     * @return $this
     */
    public function count($field = '*', ?string $name = null): Database
    {
        $column = 'COUNT(' . $field . ')' . (!is_null($name) ? ' AS ' . $name : '');
        $this->optimizeSelect($column);

        return $this;
    }

    /**
     * @param string      $field
     * @param string|null $name
     *
     * @return $this
     */
    public function countDistinct($field = '*'): Database
    {
        $column = 'COUNT(DISTINCT ' . $field . ')';
        $this->optimizeSelect($column);

        return $this;
    }

    public function withTotalCount(): Database
    {
        $column = 'SQL_CALC_FOUND_ROWS *';
        $this->optimizeSelect($column);

        return $this;
    }

    /**
     * @param string      $field
     * @param string|null $name
     *
     * @return $this
     */
    public function avg(string $field, ?string $name = null): Database
    {
        $column = 'AVG(' . $field . ')' . (!is_null($name) ? ' AS ' . $name : '');
        $this->optimizeSelect($column);

        return $this;
    }

    /**
     * @param string      $table
     * @param string|null $field1
     * @param string|null $operator
     * @param string|null $field2
     * @param string      $type
     *
     * @return $this
     */
    public function join($table, $field1 = null, $operator = null, $field2 = null, $type = ''): Database
    {
        $on = $field1;

        $prefixAndTable = $this->parseTable(ltrim($table));

        if (!is_null($operator)) {
            $on = !in_array($operator, $this->operators, true)
                ? $field1 . ' = ' . $operator . (!is_null($field2) ? ' ' . $field2 : '')
                : $field1 . ' ' . $operator . ' ' . $field2;
        }

        $this->join = (is_null($this->join))
            ? ' ' . $type . 'JOIN' . ' ' . $prefixAndTable . ' ON ' . $on
            : $this->join . ' ' . $type . 'JOIN' . ' ' . $prefixAndTable . ' ON ' . $on;
        return $this;
    }


    /**
     * @param string $table
     * @param string|null $field
     * @param string $type
     * @return $this
     */
    public function usingJoin(string $table, string $field = null, string $type = ''): Database
    {
        $prefixAndTable = $this->parseTable($table);

        $this->join = (is_null($this->join)) ? ' ' . $type . 'JOIN' . ' ' . $prefixAndTable . ' USING(' . $field . ')' : $this->join . ' ' . $type . 'JOIN' . ' ' . $prefixAndTable . ' USING(' . $field . ')';

        return $this;
    }


    /**
     * @param string $table
     * @param string $field1
     * @param string $operator
     * @param string $field2
     *
     * @return $this
     */
    public function innerJoin($table, $field1, $operator = '', $field2 = ''): Database
    {
        return $this->join($table, $field1, $operator, $field2, 'INNER ');
    }

    /**
     * @param string $table
     * @param string $field1
     * @param string $operator
     * @param string $field2
     *
     * @return $this
     */
    public function leftJoin($table, $field1, $operator = '', $field2 = ''): Database
    {
        return $this->join($table, $field1, $operator, $field2, 'LEFT ');
    }

    /**
     * @param string $table
     * @param string $field1
     * @param string $operator
     * @param string $field2
     *
     * @return $this
     */
    public function rightJoin($table, $field1, $operator = '', $field2 = ''): Database
    {
        return $this->join($table, $field1, $operator, $field2, 'RIGHT ');
    }

    /**
     * @param string $table
     * @param string $field1
     * @param string $operator
     * @param string $field2
     *
     * @return $this
     */
    public function fullOuterJoin($table, $field1, $operator = '', $field2 = ''): Database
    {
        return $this->join($table, $field1, $operator, $field2, 'FULL OUTER ');
    }

    /**
     * @param string $table
     * @param string $field1
     * @param string $operator
     * @param string $field2
     *
     * @return $this
     */
    public function leftOuterJoin($table, $field1, $operator = '', $field2 = ''): Database
    {
        return $this->join($table, $field1, $operator, $field2, 'LEFT OUTER ');
    }

    /**
     * @param string $table
     * @param string $field1
     * @param string $operator
     * @param string $field2
     *
     * @return $this
     */
    public function rightOuterJoin($table, $field1, $operator = '', $field2 = ''): Database
    {
        return $this->join($table, $field1, $operator, $field2, 'RIGHT OUTER ');
    }

    /**
     * @param array|string $where
     * @param string       $operator
     * @param string       $val
     * @param string       $type
     * @param string       $andOr
     *
     * @return $this
     */
    public function where($where, $operator = null, $val = null, $type = '', $andOr = 'AND'): Database
    {

        if (is_array($where) && !empty($where)) {
            $_where = [];
            foreach ($where as $column => $data) {
                $_where[] = $type . $column . '=' . $this->escape($data);
            }
            $where = implode(' ' . $andOr . ' ', $_where);
        } else {
            if (is_null($where) || empty($where)) {
                return $this;
            }

            if (is_array($operator)) {
                $params = explode('?', $where);
                $_where = '';
                foreach ($params as $key => $value) {
                    if (!empty($value)) {
                        $_where .= $type . $value . (isset($operator[$key]) ? $this->escape($operator[$key]) : '');
                    }
                }
                $where = $_where;
            } else if (!in_array($operator, $this->operators, true) || $operator == false) {
                $where = $type . $where . ' = ' . $this->escape($operator);
            } else {
                $where = $type . $where . ' ' . $operator . ' ' . $this->escape($val);
            }
        }



        if ($this->grouped) {
            $where = '(' . $where;
            $this->grouped = false;
        }

        $this->where = is_null($this->where) ? $where : $this->where . ' ' . $andOr . ' ' . $where;

        return $this;
    }

    /**
     * @param array|string $where
     * @param string|null  $operator
     * @param string|null  $val
     *
     * @return $this
     */
    public function orWhere($where, $operator = null, $val = null): Database
    {
        return $this->where($where, $operator, $val, '', 'OR');
    }

    /**
     * @param array|string $where
     * @param string|null  $operator
     * @param string|null  $val
     *
     * @return $this
     */
    public function notWhere($where, $operator = null, $val = null): Database
    {
        return $this->where($where, $operator, $val, 'NOT ', 'AND');
    }

    /**
     * @param array|string $where
     * @param string|null  $operator
     * @param string|null  $val
     *
     * @return $this
     */
    public function orNotWhere($where, $operator = null, $val = null): Database
    {
        return $this->where($where, $operator, $val, 'NOT ', 'OR');
    }

    /**
     * @param string $where
     * @param bool   $not
     *
     * @return $this
     */
    public function whereNull($where, $not = false): Database
    {
        $where .= ' IS ' . ($not ? 'NOT' : '') . ' NULL';
        $this->where = is_null($this->where) ? $where : $this->where . ' ' . 'AND ' . $where;

        return $this;
    }

    /**
     * @param string $where
     * @param $operator
     * @param $val
     *
     * @return $this
     */
    public function whereISNullOR(string $where, $operator, $val): Database
    {
        $where = '(' . $where . ' IS NULL OR ' . $where . ' ' . $operator .' ' . $val . ')';
        $this->where = is_null($this->where) ? $where : $this->where . ' ' . 'AND ' . $where;

        return $this;
    }

    /**
     * @param string $where
     *
     * @return $this
     */
    public function whereNotNull($where): Database
    {
        return $this->whereNull($where, true);
    }


    /**
     * @param $where
     * @param $val
     * @param string $andOr
     *
     * @return $this
     */
    public function whereAgainst($where, $val, $andOr = 'AND'): Database
    {
        $where = 'MATCH(' . $where . ') AGAINST (' . $this->escape($val) . ' IN BOOLEAN MODE)' ;
        $this->where = is_null($this->where) ? $where : $this->where . ' ' . $andOr . ' ' . $where;

        return $this;
    }

//    /**
//     * @param $fieldName
//     * @param $val
//     * @param string $andOr
//     *
//     * @return $this
//     */
//    public function whereJson($fieldName, $val, $andOr = 'AND')
//    {
//        $where = 'JSON_EXTRACT('. $fieldName .', $[' . $val . '])' ;
//        $this->where = is_null($this->where) ? $where : $this->where . ' ' . $andOr . ' ' . $where;
//
//        return $this;
//    }

    /**
     * @param Closure $obj
     *
     * @return $this
     */
    public function grouped(Closure $obj): Database
    {
        $this->grouped = true;
        call_user_func_array($obj, [$this]);
        $this->where .= ')';

        return $this;
    }

    /**
     * @param string $field
     * @param array  $keys
     * @param string $type
     * @param string $andOr
     *
     * @return $this
     */
    public function in($field, array $keys, $type = '', $andOr = 'AND'): Database
    {
        if (is_array($keys)) {
            $_keys = [];
            foreach ($keys as $k => $v) {
                $_keys[] = is_numeric($v) ? $v : $this->escape($v);
            }
            $where = $field . ' ' . $type . 'IN (' . implode(', ', $_keys) . ')';

            if ($this->grouped) {
                $where = '(' . $where;
                $this->grouped = false;
            }

            $this->where = is_null($this->where) ? $where : $this->where . ' ' . $andOr . ' ' . $where;
        }

        return $this;
    }

    /**
     * @param string $field
     * @param array  $keys
     *
     * @return $this
     */
    public function notIn($field, array $keys): Database
    {
        return $this->in($field, $keys, 'NOT ', 'AND');
    }

    /**
     * @param string $field
     * @param array  $keys
     *
     * @return $this
     */
    public function orIn($field, array $keys): Database
    {
        return $this->in($field, $keys, '', 'OR');
    }

    /**
     * @param string $field
     * @param array  $keys
     *
     * @return $this
     */
    public function orNotIn($field, array $keys): Database
    {
        return $this->in($field, $keys, 'NOT ', 'OR');
    }


    /**
     * @param string         $field
     * @param string|integer $key
     * @param string         $type
     * @param string         $andOr
     *
     * @return $this
     */
    public function findInSet($field, $key, $type = '', $andOr = 'AND'): Database
    {
        $key = is_numeric($key) ? $key : $this->escape($key);
        $where =  $type . 'FIND_IN_SET (' . $key . ', '.$field.')';

        if ($this->grouped) {
            $where = '(' . $where;
            $this->grouped = false;
        }

        $this->where = is_null($this->where)
            ? $where
            : $this->where . ' ' . $andOr . ' ' . $where;

        return $this;
    }

    /**
     * @param string $field
     * @param string $key
     *
     * @return $this
     */
    public function notFindInSet($field, $key): Database
    {
        return $this->findInSet($field, $key, 'NOT ');
    }

    /**
     * @param string $field
     * @param string $key
     *
     * @return $this
     */
    public function orFindInSet($field, $key): Database
    {
        return $this->findInSet($field, $key, '', 'OR');
    }

    /**
     * @param string $field
     * @param string $key
     *
     * @return $this
     */
    public function orNotFindInSet($field, $key): Database
    {
        return $this->findInSet($field, $key, 'NOT ', 'OR');
    }
    /**
     * @param string     $field
     * @param string|int $value1
     * @param string|int $value2
     * @param string     $type
     * @param string     $andOr
     *
     * @return $this
     */
    public function between($field, $value1, $value2, $type = '', $andOr = 'AND'): Database
    {
        $where = '(' . $field . ' ' . $type . 'BETWEEN ' . ($this->escape($value1) . ' AND ' . $this->escape($value2)) . ')';
        if ($this->grouped) {
            $where = '(' . $where;
            $this->grouped = false;
        }

        $this->where = is_null($this->where) ? $where : $this->where . ' ' . $andOr . ' ' . $where;

        return $this;
    }

    /**
     * @param string     $field
     * @param string|int $value1
     * @param string|int $value2
     *
     * @return $this
     */
    public function notBetween($field, $value1, $value2): Database
    {
        return $this->between($field, $value1, $value2, 'NOT ', 'AND');
    }

    /**
     * @param string     $field
     * @param string|int $value1
     * @param string|int $value2
     *
     * @return $this
     */
    public function orBetween($field, $value1, $value2): Database
    {
        return $this->between($field, $value1, $value2, '', 'OR');
    }

    /**
     * @param string     $field
     * @param string|int $value1
     * @param string|int $value2
     *
     * @return $this
     */
    public function orNotBetween($field, $value1, $value2): Database
    {
        return $this->between($field, $value1, $value2, 'NOT ', 'OR');
    }

    /**
     * @param string $field
     * @param string $data
     * @param string $type
     * @param string $andOr
     *
     * @return $this
     */
    public function like($field, $data, $type = '', $andOr = 'AND'): Database
    {
        $like = $this->escape($data);
        $where = $field . ' ' . $type . 'LIKE ' . $like;

        if ($this->grouped) {
            $where = '(' . $where;
            $this->grouped = false;
        }

        $this->where = is_null($this->where) ? $where : $this->where . ' ' . $andOr . ' ' . $where;

        return $this;
    }

    /**
     * @param string $field
     * @param string $data
     *
     * @return $this
     */
    public function orLike($field, $data): Database
    {
        return $this->like($field, $data, '', 'OR');
    }

    /**
     * @param string $field
     * @param string $data
     *
     * @return $this
     */
    public function notLike($field, $data): Database
    {
        return $this->like($field, $data, 'NOT ', 'AND');
    }

    /**
     * @param string $field
     * @param string $data
     *
     * @return $this
     */
    public function orNotLike($field, $data): Database
    {
        return $this->like($field, $data, 'NOT ', 'OR');
    }

    /**
     * @param int      $limit
     * @param int|null $limitEnd
     *
     * @return $this
     */
    public function limit($limit, $limitEnd = null): Database
    {
        $this->limit = !is_null($limitEnd) ? $limit . ', ' . $limitEnd : $limit;

        return $this;
    }

    /**
     * @param int $offset
     *
     * @return $this
     */
    public function offset($offset): Database
    {
        $this->offset = $offset;

        return $this;
    }

    /**
     * @param int $perPage
     * @param int $page
     *
     * @return $this
     */
    public function pagination($perPage, $page): Database
    {
        $this->limit = $perPage;
        $this->offset = (($page > 0 ? $page : 1) - 1) * $perPage;

        return $this;
    }

    /**
     * @param string      $orderBy
     * @param string|null $orderDir
     *
     * @return $this
     */
    public function orderBy(string $orderBy, string $orderDir = null): Database
    {
        if (!is_null($orderDir)) {
            $this->orderBy = $orderBy . ' ' . strtoupper($orderDir);
        } else {
            $this->orderBy = stristr($orderBy, ' ') || strtolower($orderBy) === 'rand()' ? $orderBy : $orderBy . ' ASC';
        }

        return $this;
    }

    /**
     * @param string|array $groupBy
     *
     * @return $this
     */
    public function groupBy($groupBy): Database
    {
        $this->groupBy = is_array($groupBy) ? implode(', ', $groupBy) : $groupBy;

        return $this;
    }

    /**
     * @param string            $field
     * @param string|array|null $operator
     * @param string|null       $val
     *
     * @return $this
     */
    public function having($field, $operator = null, $val = null): Database
    {
        if (is_array($operator)) {
            $fields = explode('?', $field);
            $where = '';
            foreach ($fields as $key => $value) {
                if (!empty($value)) {
                    $where .= $value . (isset($operator[$key]) ? $this->escape($operator[$key]) : '');
                }
            }
            $this->having = $where;
        } elseif (!in_array($operator, $this->operators, true)) {
            $this->having = $field . ' > ' . $this->escape($operator);
        } else {
            $this->having = $field . ' ' . $operator . ' ' . $this->escape($val);
        }

        return $this;
    }

    /**
     * @return int
     */
    public function numRows(): int
    {
        return $this->numRows;
    }

    /**
     * @return int|null
     */
    public function insertId(): ?int
    {
        return $this->insertId;
    }

    /**
     * @throw PDOException
     */
    public function error(): void
    {
        if ($this->_credentials['debug'] === true) {
            if (php_sapi_name() === 'cli') {
                die("Query: " . $this->query . PHP_EOL . "Error: " . $this->error . PHP_EOL);
            }

            $msg = '<h1>Database Error</h1>';
            $msg .= '<h4>Query: <em style="font-weight:normal;">"' . $this->query . '"</em></h4>';
            $msg .= '<h4>Error: <em style="font-weight:normal;">' . $this->error . '</em></h4>';

            die($msg);
        }

        throw new PDOException($this->error . '. (' . $this->query . ')');
    }

    /**
     * @param string|bool $type
     * @param string|null $argument
     *
     * @return mixed
     */
    public function getValue($type = null, $argument = null)
    {
        $this->limit = 1;
        $query = $this->getAll(true);

        if ($result = $this->query($query, true, $type, $argument)) {
            foreach ($result[0] as $value) {
                return $value;
            }
        }

        return null;
    }

    /**
     * @param string|bool $type
     * @param string|null $argument
     *
     * @return mixed
     */
    public function get($type = null, $argument = null)
    {
        $this->limit = 1;
        $query = $this->getAll(true);

        return $type === true ? $query : $this->query($query, false, $type, $argument);
    }

    /**
     * @param bool|string $type
     * @param string|null $argument
     *
     * @return mixed
     */
    public function getAll($type = null, $argument = null)
    {
        $query = 'SELECT ' . $this->select . ' FROM ' . $this->from;

        if (!is_null($this->join)) {
            $query .= $this->join;
        }

        if (!is_null($this->where)) {
            $query .= ' WHERE ' . $this->where;
        }

        if (!is_null($this->groupBy)) {
            $query .= ' GROUP BY ' . $this->groupBy;
        }

        if (!is_null($this->having)) {
            $query .= ' HAVING ' . $this->having;
        }

        if (!is_null($this->orderBy)) {
            $query .= ' ORDER BY ' . $this->orderBy;
        }

        if (!is_null($this->limit)) {
            $query .= ' LIMIT ' . $this->limit;
        }

        if (!is_null($this->offset)) {
            $query .= ' OFFSET ' . $this->offset;
        }

        return $type === true ? $query : $this->query($query, true, $type, $argument);
    }

    /**
     * @param array $data
     * @param bool  $type
     *
     * @return bool|string|int|null
     */
    public function insert(array $data, $type = false)
    {
        $query = 'INSERT INTO ' . $this->from;

        $values = array_values($data);
        if (isset($values[0]) && is_array($values[0])) {
            $column = implode(', ', array_keys($values[0]));
            $query .= ' (' . $column . ') VALUES ';
            foreach ($values as $value) {
                $val = implode(', ', array_map([$this, 'escape'], $value));
                $query .= '(' . $val . '), ';
            }
            $query = trim($query, ', ');
        } else {
            $column = implode(', ', array_keys($data));
            $val = implode(', ', array_map([$this, 'escape'], $data));
            $query .= ' (' . $column . ') VALUES (' . $val . ')';
        }

        if ($type === true) {
            return $query;
        }

        if ($this->query($query, false)) {
            $this->insertId = $this->pdo->lastInsertId();
            return $this->insertId();
        }

        return false;
    }

    /**
     * @param array $data
     * @param bool  $type
     *
     * @return bool|string|int|null
     */
    public function replace(array $data, $type = false)
    {
        $query = 'REPLACE INTO ' . $this->from;

        $values = array_values($data);
        if (isset($values[0]) && is_array($values[0])) {
            $column = implode(', ', array_keys($values[0]));
            $query .= ' (' . $column . ') VALUES ';
            foreach ($values as $value) {
                $val = implode(', ', array_map([$this, 'escape'], $value));
                $query .= '(' . $val . '), ';
            }
            $query = trim($query, ', ');
        } else {
            $column = implode(', ', array_keys($data));
            $val = implode(', ', array_map([$this, 'escape'], $data));
            $query .= ' (' . $column . ') VALUES (' . $val . ')';
        }

        if ($type === true) {
            return $query;
        }

        if ($this->query($query, false)) {
            $this->insertId = $this->pdo->lastInsertId();
            return $this->insertId();
        }

        return false;
    }

    /**
     * @param array $data
     * @param bool  $type
     *
     * @return mixed|string
     */
    public function update(array $data, $type = false)
    {
        $query = 'UPDATE ' . $this->from . ' SET ';
        $values = [];

        foreach ($data as $column => $val) {
            $values[] = $column . '=' . $this->escape($val);
        }
        $query .= implode(',', $values);

        if (!is_null($this->where)) {
            $query .= ' WHERE ' . $this->where;
        }

        if (!is_null($this->orderBy)) {
            $query .= ' ORDER BY ' . $this->orderBy;
        }

        if (!is_null($this->limit)) {
            $query .= ' LIMIT ' . $this->limit;
        }

        return $type === true ? $query : $this->query($query, false);
    }

    /**
     * @param array $data
     * @param bool  $type
     *
     * @return mixed|string
     */
    public function updateLow(array $data, $type = false)
    {
        $query = 'UPDATE LOW_PRIORITY ' . $this->from . ' SET ';
        $values = [];

        foreach ($data as $column => $val) {
            $values[] = $column . '=' . $this->escape($val);
        }
        $query .= implode(',', $values);

        if (!is_null($this->where)) {
            $query .= ' WHERE ' . $this->where;
        }

        if (!is_null($this->orderBy)) {
            $query .= ' ORDER BY ' . $this->orderBy;
        }

        if (!is_null($this->limit)) {
            $query .= ' LIMIT ' . $this->limit;
        }

        return $type === true ? $query : $this->query($query, false);
    }

    /**
     * @return mixed
     * UPDATE `table` SET `url` = CONCAT('/about2', '/', fileName, '.htm') WHERE `sectionId` = '2'
     */
    public function updateConcat($field, $concat)
    {
        $query = 'UPDATE ' . $this->from . ' SET ' . $field . '=' . $concat;

        if (!is_null($this->where)) {
            $query .= ' WHERE ' . $this->where;
        }

        if (!is_null($this->orderBy)) {
            $query .= ' ORDER BY ' . $this->orderBy;
        }

        if (!is_null($this->limit)) {
            $query .= ' LIMIT ' . $this->limit;
        }

        return $this->query($query, false);
    }

    /**
     * @param bool $type
     *
     * @return mixed|string
     */
    public function delete($type = false)
    {
        $query = 'DELETE FROM ' . $this->from;

        if (!is_null($this->where)) {
            $query .= ' WHERE ' . $this->where;
        }

        if (!is_null($this->orderBy)) {
            $query .= ' ORDER BY ' . $this->orderBy;
        }

        if (!is_null($this->limit)) {
            $query .= ' LIMIT ' . $this->limit;
        }

        if ($query === 'DELETE FROM ' . $this->from) {
            $query = 'TRUNCATE TABLE ' . $this->from;
        }

        return $type === true ? $query : $this->query($query, false);
    }

    /**
     * @return mixed
     */
    public function analyze()
    {
        return $this->query('ANALYZE TABLE ' . $this->from, false);
    }

    /**
     * @return mixed
     */
    public function check()
    {
        return $this->query('CHECK TABLE ' . $this->from, false);
    }

    /**
     * @return mixed
     */
    public function checksum()
    {
        return $this->query('CHECKSUM TABLE ' . $this->from, false);
    }

    /**
     * @return mixed
     */
    public function optimize()
    {
        return $this->query('OPTIMIZE TABLE ' . $this->from, false);
    }

    /**
     * @return mixed
     */
    public function repair()
    {
        return $this->query('REPAIR TABLE ' . $this->from, false);
    }


    /**
     * @return bool
     */
    public function transaction(): bool
    {
        if (!$this->transactionCount++) {
            return $this->pdo->beginTransaction();
        }

        $this->pdo->exec('SAVEPOINT trans' . $this->transactionCount);
        return $this->transactionCount >= 0;
    }

    /**
     * фиксирует текущую транзакцию, делая ее изменения постоянными.
     * @return bool
     */
    public function commit(): bool
    {
        if (!--$this->transactionCount) {
            return $this->pdo->commit();
        }

        return $this->transactionCount >= 0;
    }

    /**
     * откатывает текущую транзакцию, отменяя ее изменения.
     * @return bool
     */
    public function rollBack(): bool
    {
        if (--$this->transactionCount) {
            $this->pdo->exec('ROLLBACK TO trans' . ($this->transactionCount + 1));
            return true;
        }

        return $this->pdo->rollBack();
    }

    /**
     * @return mixed
     */
    public function exec()
    {
        if (is_null($this->query)) {
            return null;
        }

        $query = $this->pdo->exec($this->query);
        if ($query === false) {
            $this->error = $this->pdo->errorInfo()[2];
            $this->error();
        }

        return $query;
    }

    /**
     * @param string $type
     * @param string $argument
     * @param bool   $all
     *
     * @return mixed
     */
    public function fetch($type = null, $argument = null, $all = false)
    {
        if (is_null($this->query)) {
            return null;
        }

        $query = $this->pdo->query($this->query);
        if (!$query) {
            $this->error = $this->pdo->errorInfo()[2];
            $this->error();
        }

        $type = $this->getFetchType($type);
        if ($type === PDO::FETCH_CLASS) {
            $query->setFetchMode($type, $argument);
        } else {
            $query->setFetchMode($type);
        }

        $result = $all ? $query->fetchAll() : $query->fetch();
        $this->numRows = is_array($result) ? count($result) : 1;

        return $result;
    }

    /**
     * @param string $type
     * @param string $argument
     *
     * @return mixed
     */
    public function fetchAll($type = null, $argument = null)
    {
        return $this->fetch($type, $argument, true);
    }

    /**
     * @param string|bool $type
     * @param string|null $argument
     *
     * @return mixed
     */
    public function getAllIndexes($type = null, $argument = null)
    {

        $query = $this->getAll(true);

        $result = $this->query($query, true, $type, $argument);

        if (is_null($this->indexKey)) {
            if (is_null($this->valueKey)) {
                $this->result = $result;
            } else {
                foreach($result as $row) {
                    $rows[] = is_array($row) ? $row[$this->valueKey] : $row->{$this->valueKey};
                }
            }
        } elseif (is_null($this->valueKey)) {
            foreach ($result as $row) {
                if (is_array($row)) {
                    $rows[$row[$this->indexKey]] = $row;
                } else {
                    $rows[$row->{$this->indexKey}] = $row;
                }
            }
        } else {
            foreach($result as $row) {
                if (is_array($row)) {
                    $rows[$row[$this->indexKey]] = $row[$this->valueKey];
                } else {
                    $rows[$row->{$this->indexKey}] = $row->{$this->valueKey};
                }
            }
        }

        if (!empty($rows)) {
            $this->resetIndexes();
            return $this->result = $rows;
        }

        return null;
    }

    /**
     * @param null $indexKey
     * @return $this
     */
    public function indexKey($indexKey = null): Database
    {
        $this->indexKey = $indexKey;
        return $this;
    }

    /**
     * @param null $valueKey
     * @return $this
     */
    public function valueKey($valueKey = null): Database
    {
        $this->valueKey = $valueKey;
        return $this;
    }



    /**
     * @param string     $query
     * @param array|bool $all
     * @param string|null     $type
     * @param string|null      $argument
     *
     * @return $this|mixed
     */
    public function query(string $query, $all = true, ?string $type = null, ?string $argument = null)
    {
        $this->reset();

        if (is_array($all) || func_num_args() === 1) {
            $params = explode('?', $query);
            $newQuery = '';
            foreach ($params as $key => $value) {
                if (!empty($value)) {
                    $newQuery .= $value . (isset($all[$key]) ? $this->escape($all[$key]) : '');
                }
            }
            $this->query = $newQuery;
            return $this;
        }

        $this->query = preg_replace('/\s\s+|\t\t+/', ' ', trim($query));

        $str = false;
        foreach (['select', 'optimize', 'check', 'repair', 'checksum', 'analyze'] as $value) {
            if (stripos($this->query, $value) === 0) {
                $str = true;
                break;
            }
        }

        $type = $this->getFetchType($type);
        $cache = false;
        if (!is_null($this->cache) && $type !== PDO::FETCH_CLASS) {
            $cache = $this->cache->getCache($this->query, $type === PDO::FETCH_ASSOC);
        }

        if (!$cache && $str) {
            $sql = $this->pdo->query($this->query);
            if ($sql) {
                $this->numRows = $sql->rowCount();
                if ($this->numRows > 0) {
                    if ($type === PDO::FETCH_CLASS) {
                        $sql->setFetchMode($type, $argument);
                    } else {
                        $sql->setFetchMode($type);
                    }

                    $this->result = $all ? $sql->fetchAll() : $sql->fetch();
                }

                if (!is_null($this->cache) && $type !== PDO::FETCH_CLASS) {
                    $this->cache->setCache($this->query, $this->result);
                }
                $this->cache = null;
            } else {
                $this->cache = null;
                $this->error = $this->pdo->errorInfo()[2];
                $this->error();
            }
        } else if ((!$cache && !$str) || ($cache && !$str)) {
            $this->cache = null;
            $this->result = $this->pdo->exec($this->query);

            if ($this->result === false) {
                $this->error = $this->pdo->errorInfo()[2];
                $this->error();
            }
        } else {
            $this->cache = null;
            $this->result = $cache;
            $this->numRows = is_array($this->result) ? count($this->result) : ($this->result == '' ? 0 : 1);
        }

        $this->queryCount++;

        return $this->result;
    }



    /**
     * @param $data
     *
     * @return string
     */
    public function escape($data)
    {
        return $data === null ? 'NULL' : (
        is_int($data) || is_float($data) ? $data : $this->pdo->quote($data)
        );
    }

    /**
     * @param $time
     *
     * @return $this
     */
    public function cache($time): Database
    {
        $this->cache = new Cache($this->_credentials['cacheDir'], $time);

        return $this;
    }

    /**
     * @return int
     */
    public function queryCount(): int
    {
        return $this->queryCount;
    }

    /**
     * @return string|null
     */
    public function getQuery(): ?string
    {
        return $this->query;
    }

    /**
     * @return void
     */
    public function __destruct()
    {
        $this->pdo = null;
    }

    /**
     * @return void
     */
    protected function reset(): void
    {
        $this->select = '*';
        $this->from = null;
        $this->where = null;
        $this->limit = null;
        $this->offset = null;
        $this->orderBy = null;
        $this->groupBy = null;
        $this->having = null;
        $this->join = null;
        $this->grouped = false;
        $this->numRows = 0;
        $this->insertId = null;
        $this->query = null;
        $this->error = null;
        $this->result = [];
        $this->transactionCount = 0;

    }

    /**
     * @return void
     */
    protected function resetIndexes(): void
    {
        $this->indexKey = null;
        $this->valueKey = null;
    }

    /**
     * @param $type
     * @return int
     */
    protected function getFetchType($type): int
    {
        return $type == 'class' ? PDO::FETCH_CLASS : ($type == 'array' ? PDO::FETCH_ASSOC : PDO::FETCH_OBJ);
    }

    /**
     * Optimize Selected fields for the query
     *
     * @param string $fields
     *
     * @return void
     */
    private function optimizeSelect(string $fields): void
    {
        $this->select = $this->select === '*' ? $fields : $this->select . ', ' . $fields;
    }

    /**
     * @param $table
     * @return mixed|string
     */
    private function parseTable($table)
    {
        global $tbl;

        $matches = explode(' ', $table);

        if (is_array($matches)) {
            if (isset($matches[1])) {
                $prefixAndTable = $tbl[str_replace(' ', '', $matches[0])];
                return  $prefixAndTable  . ' AS ' . str_replace(' ', '', $matches[2]);
            }

            return $tbl[str_replace(' ', '', $matches[0])];
        }

        return $tbl[$table];
    }

    public function isColumn($columnName)
    {
        return $this->query("SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$this->databaseName' AND TABLE_NAME = '$this->from' AND COLUMN_NAME = '$columnName'");
    }
    public function createColumn($columnName, $datatype)
    {
        return $this->query("ALTER TABLE `$this->from` ADD COLUMN `$columnName` {$datatype}");
    }

    public function renameColumn($oldColumnName, $newColumnName, $schema)
    {
        return $this->query("ALTER TABLE `$this->from` RENAME COLUMN `$oldColumnName` to  `$newColumnName`");
    }

    public function changeColumnDatatype($columnName, $datatype)
    {
        return $this->query("ALTER TABLE `$this->from` MODIFY COLUMN `$columnName` {$datatype}");
    }

    public function removeColumn($columnName)
    {
        return $this->query("ALTER TABLE `$this->from` DROP COLUMN `$columnName`");
    }
}
