<?php

/**
 * Database abstraction layer
 */
abstract class Db
{
    /** @var int Constant used by insert() method */
    public const INSERT = 1;

    /** @var int Constant used by insert() method */
    public const INSERT_IGNORE = 2;

    /** @var int Constant used by insert() method */
    public const REPLACE = 3;

    /** @var int Constant used by insert() method */
    public const ON_DUPLICATE_KEY = 4;

    /** @var string Server (eg. localhost) */
    protected $server;

    /** @var string Database user (eg. root) */
    protected $user;

    /** @var string Database password */
    protected $password;

    /** @var string Database name */
    protected $database;

    /** @var bool */
    protected $is_cache_enabled;

    /** @var PDO|mysqli|resource|null Resource link */
    protected $link;

    /** @var PDOStatement|mysqli_result|resource|bool SQL cached result */
    protected $result;

    /** @var array List of DB instances */
    public static $instance = [];

    /** @var array List of server settings */
    public static $_servers = [];

    /** @var bool|null Flag used to load slave servers only once */
    public static $_slave_servers_loaded = null;

    /** @var string Store last executed query */
    protected $last_query;

    /** @var string Store hash of the last executed query */
    protected $last_query_hash;

    /** @var bool Last cached query */
    protected $last_cached;

    /**
     * Opens a database connection
     *
     * @return PDO|mysqli|resource
     */
    abstract public function connect();

    /**
     * Closes database connection
     */
    abstract public function disconnect();

    /**
     * Execute a query and get result resource
     *
     * @param string $sql
     * @return PDOStatement|mysqli_result|resource|bool
     */
    abstract protected function _query($sql);

    /**
     * Get number of rows in a result
     *
     * @param mixed $result
     * @return int
     */
    abstract protected function _numRows($result);

    /**
     * Get the ID generated from the previous INSERT operation
     *
     * @return int|string
     */
    abstract public function Insert_ID();

    /**
     * Get number of affected rows in previous database operation
     *
     * @return int
     */
    abstract public function Affected_Rows();

    /**
     * Get next row for a query which does not return an array
     *
     * @param PDOStatement|mysqli_result|resource|bool $result
     * @return array|object|false|null
     */
    abstract public function nextRow($result = false);

    /**
     * Get all rows for a query which return an array
     *
     * @param PDOStatement|mysqli_result|resource|bool|null $result
     * @return array
     */
    abstract protected function getAll($result = false);

    /**
     * Get database version
     *
     * @return string
     */
    abstract public function getVersion();

    /**
     * Protect string against SQL injections
     *
     * @param string $str
     * @return string
     */
    abstract public function _escape($str);

    /**
     * Returns the text of the error message from previous database operation
     *
     * @return string
     */
    abstract public function getMsgError();

    /**
     * Returns the number of the error from previous database operation
     *
     * @return int
     */
    abstract public function getNumberError();

    /**
     * Sets the current active database
     *
     * @param string $db_name
     * @return bool|int
     */
    abstract public function set_db($db_name);

    /**
     * Selects best table engine
     *
     * @return string
     */
    abstract public function getBestEngine();

    /**
     * Get singleton instance
     *
     * @param bool $master
     * @return Db
     */
    public static function getInstance($master = true)
    {
        $id = $master ? 0 : 1;

        if (!isset(self::$instance[$id])) {
            $class = self::getClass();
            self::$instance[$id] = new $class(
                _DB_SERVER_,
                _DB_USER_,
                _DB_PASSWD_,
                _DB_NAME_,
                true
            );
        }

        return self::$instance[$id];
    }

    /**
     * Get database class
     *
     * @return string
     */
    public static function getClass()
    {
        $class = 'DbPDO';
        if (class_exists($class)) {
            return $class;
        }

        return 'DbMySQLi';
    }

    /**
     * Constructor
     *
     * @param string $server
     * @param string $user
     * @param string $password
     * @param string $database
     * @param bool $connect
     */
    public function __construct($server, $user, $password, $database, $connect = true)
    {
        $this->server = $server;
        $this->user = $user;
        $this->password = $password;
        $this->database = $database;
        $this->is_cache_enabled = _PS_CACHE_ENABLED_;

        if ($connect) {
            $this->connect();
        }
    }

    /**
     * Disable cache
     */
    public function disableCache()
    {
        $this->is_cache_enabled = false;
    }

    /**
     * Enable cache
     */
    public function enableCache()
    {
        $this->is_cache_enabled = true;
    }

    /**
     * Execute query
     *
     * @param string $sql
     * @return bool
     */
    public function query($sql)
    {
        if ($sql instanceof DbQuery) {
            $sql = $sql->build();
        }

        $this->result = $this->_query($sql);

        if (!$this->result && $this->getNumberError() == 2006) {
            $this->connect();
            $this->result = $this->_query($sql);
        }

        if (_PS_DEBUG_SQL_) {
            $this->displayError($sql);
        }

        return $this->result;
    }

    /**
     * Execute INSERT query
     *
     * @param string $table
     * @param array $data
     * @param bool $null_values
     * @param bool $use_cache
     * @param int $type
     * @param bool $add_prefix
     * @return bool
     */
    public function insert($table, $data, $null_values = false, $use_cache = true, $type = self::INSERT, $add_prefix = true)
    {
        if (!$data && !$null_values) {
            return true;
        }

        if ($add_prefix) {
            $table = _DB_PREFIX_ . $table;
        }

        if ($type == self::INSERT) {
            $insert_keyword = 'INSERT';
        } elseif ($type == self::INSERT_IGNORE) {
            $insert_keyword = 'INSERT IGNORE';
        } elseif ($type == self::REPLACE) {
            $insert_keyword = 'REPLACE';
        } elseif ($type == self::ON_DUPLICATE_KEY) {
            $insert_keyword = 'INSERT';
        } else {
            throw new Exception('Bad keyword, must be Db::INSERT or Db::INSERT_IGNORE or Db::REPLACE or Db::ON_DUPLICATE_KEY');
        }

        // Check if $data is a list of row
        $current = current($data);
        if (!is_array($current) || isset($current['type'])) {
            $data = [$data];
        }

        $keys = [];
        $values_stringified = [];
        $first_loop = true;
        $duplicate_key_stringified = '';

        foreach ($data as $row_data) {
            $values = [];
            foreach ($row_data as $key => $value) {
                if (!$first_loop) {
                    if (!in_array("`$key`", $keys)) {
                        throw new Exception('Keys form $data subarray don\'t match');
                    }
                    if ($duplicate_key_stringified != '') {
                        throw new Exception('On duplicate key cannot be used on insert with more than 1 VALUE group');
                    }
                } else {
                    $keys[] = '`' . $this->escape($key) . '`';
                }

                if (!is_array($value)) {
                    $value = ['type' => 'text', 'value' => $value];
                }
                if ($value['type'] == 'sql') {
                    $values[] = $string_value = $value['value'];
                } else {
                    $values[] = $string_value = $null_values && ($value['value'] === '' || null === $value['value']) ? 'NULL' : "'{$value['value']}'";
                }

                if ($type == self::ON_DUPLICATE_KEY) {
                    $duplicate_key_stringified .= '`' . $this->escape($key) . '` = ' . $string_value . ',';
                }
            }
            $first_loop = false;
            $values_stringified[] = '(' . implode(', ', $values) . ')';
        }
        $keys_stringified = implode(', ', $keys);

        $sql = $insert_keyword . ' INTO `' . $table . '` (' . $keys_stringified . ') VALUES ' . implode(', ', $values_stringified);
        if ($type == self::ON_DUPLICATE_KEY) {
            $sql .= ' ON DUPLICATE KEY UPDATE ' . substr($duplicate_key_stringified, 0, -1);
        }

        return (bool) $this->q($sql, $use_cache);
    }

    /**
     * Execute UPDATE query
     *
     * @param string $table
     * @param array $data
     * @param string $where
     * @param int $limit
     * @param bool $null_values
     * @param bool $use_cache
     * @param bool $add_prefix
     * @return bool
     */
    public function update($table, $data, $where = '', $limit = 0, $null_values = false, $use_cache = true, $add_prefix = true)
    {
        if (!$data) {
            return true;
        }

        if ($add_prefix) {
            $table = _DB_PREFIX_ . $table;
        }

        $sql = 'UPDATE `' . $this->escape($table) . '` SET ';
        foreach ($data as $key => $value) {
            if (!is_array($value)) {
                $value = ['type' => 'text', 'value' => $value];
            }
            if ($value['type'] == 'sql') {
                $sql .= '`' . $this->escape($key) . "` = {$value['value']},";
            } else {
                $sql .= ($null_values && ($value['value'] === '' || null === $value['value'])) ? '`' . $this->escape($key) . '` = NULL,' : '`' . $this->escape($key) . "` = '{$value['value']}',";
            }
        }

        $sql = rtrim($sql, ',');
        if ($where) {
            $sql .= ' WHERE ' . $where;
        }
        if ($limit) {
            $sql .= ' LIMIT ' . (int) $limit;
        }

        return (bool) $this->q($sql, $use_cache);
    }

    /**
     * Execute DELETE query
     *
     * @param string $table
     * @param string $where
     * @param int $limit
     * @param bool $use_cache
     * @param bool $add_prefix
     * @return bool
     */
    public function delete($table, $where = '', $limit = 0, $use_cache = true, $add_prefix = true)
    {
        if ($add_prefix) {
            $table = _DB_PREFIX_ . $table;
        }

        $this->result = false;
        $sql = 'DELETE FROM `' . $this->escape($table) . '`' . ($where ? ' WHERE ' . $where : '') . ($limit ? ' LIMIT ' . (int) $limit : '');
        $res = $this->query($sql);
        if ($use_cache && $this->is_cache_enabled) {
            Cache::getInstance()->deleteQuery($sql);
        }

        return (bool) $res;
    }

    /**
     * Execute query
     *
     * @param string|DbQuery $sql
     * @param bool $use_cache
     * @return bool
     */
    public function execute($sql, $use_cache = true)
    {
        if ($sql instanceof DbQuery) {
            $sql = $sql->build();
        }

        $this->result = $this->query($sql);
        if ($use_cache && $this->is_cache_enabled) {
            Cache::getInstance()->deleteQuery($sql);
        }

        return (bool) $this->result;
    }

    /**
     * Execute query and return all results
     *
     * @param string|DbQuery $sql
     * @param bool $array
     * @param bool $use_cache
     * @return array
     */
    public function executeS($sql, $array = true, $use_cache = true)
    {
        if ($sql instanceof DbQuery) {
            $sql = $sql->build();
        }

        $this->result = $this->query($sql);
        if (!$this->result) {
            return [];
        }

        $result = $this->getAll($this->result);
        if ($use_cache && $this->is_cache_enabled) {
            Cache::getInstance()->deleteQuery($sql);
        }

        return $result;
    }

    /**
     * Get single row
     *
     * @param string|DbQuery $sql
     * @param bool $use_cache
     * @return array|false
     */
    public function getRow($sql, $use_cache = true)
    {
        if ($sql instanceof DbQuery) {
            $sql = $sql->build();
        }

        $this->result = $this->query($sql);
        if (!$this->result) {
            return false;
        }

        $result = $this->nextRow($this->result);
        if ($use_cache && $this->is_cache_enabled) {
            Cache::getInstance()->deleteQuery($sql);
        }

        return $result;
    }

    /**
     * Get single value
     *
     * @param string|DbQuery $sql
     * @param bool $use_cache
     * @return string|false
     */
    public function getValue($sql, $use_cache = true)
    {
        if ($sql instanceof DbQuery) {
            $sql = $sql->build();
        }

        $this->result = $this->query($sql);
        if (!$this->result) {
            return false;
        }

        $result = $this->nextRow($this->result);
        if ($use_cache && $this->is_cache_enabled) {
            Cache::getInstance()->deleteQuery($sql);
        }

        if (!$result) {
            return false;
        }

        return reset($result);
    }

    /**
     * Get number of rows
     *
     * @return int
     */
    public function numRows()
    {
        if (!$this->result) {
            return 0;
        }

        return $this->_numRows($this->result);
    }

    /**
     * Protected query method
     *
     * @param string $sql
     * @param bool $use_cache
     * @return bool
     */
    protected function q($sql, $use_cache = true)
    {
        $this->result = $this->query($sql);
        if ($use_cache && $this->is_cache_enabled) {
            Cache::getInstance()->deleteQuery($sql);
        }

        return (bool) $this->result;
    }

    /**
     * Display error
     *
     * @param bool|string $sql
     */
    public function displayError($sql = false)
    {
        if (_PS_DEBUG_SQL_) {
            $error = $this->getMsgError();
            if ($sql) {
                $error .= ' - SQL: ' . $sql;
            }
            echo '<pre>' . $error . '</pre>';
        }
    }

    /**
     * Escape string
     *
     * @param string $string
     * @param bool $html_ok
     * @param bool $bq_sql
     * @return string
     */
    public function escape($string, $html_ok = false, $bq_sql = false)
    {
        if (_PS_MAGIC_QUOTES_GPC_) {
            $string = stripslashes($string);
        }

        if (!$html_ok) {
            $string = strip_tags(Tools::nl2br($string));
        }

        if (!$bq_sql) {
            $string = str_replace('`', '\`', $string);
        }

        return $this->_escape($string);
    }

    /**
     * Check database connection
     *
     * @param string $server
     * @param string $user
     * @param string $pwd
     * @param string $db
     * @param bool $new_db_link
     * @param string|null $engine
     * @param int $timeout
     * @return bool
     */
    public static function checkConnection($server, $user, $pwd, $db, $new_db_link = true, $engine = null, $timeout = 5)
    {
        $class = self::getClass();
        return $class::checkConnection($server, $user, $pwd, $db, $new_db_link, $engine, $timeout);
    }

    /**
     * Check encoding
     *
     * @param string $server
     * @param string $user
     * @param string $pwd
     * @return bool
     */
    public static function checkEncoding($server, $user, $pwd)
    {
        $class = self::getClass();
        return $class::checkEncoding($server, $user, $pwd);
    }

    /**
     * Check if table with same prefix exists
     *
     * @param string $server
     * @param string $user
     * @param string $pwd
     * @param string $db
     * @param string $prefix
     * @return bool
     */
    public static function hasTableWithSamePrefix($server, $user, $pwd, $db, $prefix)
    {
        $class = self::getClass();
        return $class::hasTableWithSamePrefix($server, $user, $pwd, $db, $prefix);
    }

    /**
     * Check create privilege
     *
     * @param string $server
     * @param string $user
     * @param string $pwd
     * @param string $db
     * @param string $prefix
     * @param string|null $engine
     * @return bool
     */
    public static function checkCreatePrivilege($server, $user, $pwd, $db, $prefix, $engine = null)
    {
        $class = self::getClass();
        return $class::checkCreatePrivilege($server, $user, $pwd, $db, $prefix, $engine);
    }

    /**
     * Check select privilege
     *
     * @param string $server
     * @param string $user
     * @param string $pwd
     * @param string $db
     * @param string $prefix
     * @param string|null $engine
     * @return bool
     */
    public static function checkSelectPrivilege($server, $user, $pwd, $db, $prefix, $engine = null)
    {
        $class = self::getClass();
        return $class::checkSelectPrivilege($server, $user, $pwd, $db, $prefix, $engine);
    }

    /**
     * Get link
     *
     * @return PDO|mysqli|resource|null
     */
    public function getLink()
    {
        return $this->link;
    }

    /**
     * Get database prefix
     *
     * @return string
     */
    public function getPrefix()
    {
        return _DB_PREFIX_;
    }

    /**
     * Set instance for testing
     *
     * @param Db $test_db
     */
    public static function setInstanceForTesting($test_db)
    {
        self::$instance[0] = $test_db;
    }

    /**
     * Delete testing instance
     */
    public static function deleteTestingInstance()
    {
        self::$instance = [];
    }

    /**
     * Load slave servers
     */
    protected static function loadSlaveServers()
    {
        if (self::$_slave_servers_loaded !== null) {
            return;
        }

        self::$_slave_servers_loaded = false;

        if (file_exists(_PS_ROOT_DIR_ . '/app/config/database.yml')) {
            $config = file_get_contents(_PS_ROOT_DIR_ . '/app/config/database.yml');
            if (preg_match_all('/slave_[0-9]+:\s*host:\s*([^\r\n]+)/im', $config, $matches)) {
                foreach ($matches[1] as $server) {
                    self::$_servers[] = [
                        'server' => $server,
                        'user' => _DB_USER_,
                        'password' => _DB_PASSWD_,
                        'database' => _DB_NAME_
                    ];
                }
            }
        }

        self::$_slave_servers_loaded = true;
    }

    /**
     * Begin transaction
     *
     * @return bool
     */
    public function beginTransaction()
    {
        if ($this->link instanceof PDO) {
            return $this->link->beginTransaction();
        }

        return $this->execute('START TRANSACTION');
    }

    /**
     * Commit transaction
     *
     * @return bool
     */
    public function commit()
    {
        if ($this->link instanceof PDO) {
            return $this->link->commit();
        }

        return $this->execute('COMMIT');
    }

    /**
     * Rollback transaction
     *
     * @return bool
     */
    public function rollback()
    {
        if ($this->link instanceof PDO) {
            return $this->link->rollback();
        }

        return $this->execute('ROLLBACK');
    }

    /**
     * Execute multiple queries
     *
     * @param array $queries
     * @return bool
     */
    public function executeMultiple($queries)
    {
        $this->beginTransaction();

        try {
            foreach ($queries as $query) {
                if (!$this->execute($query)) {
                    $this->rollback();
                    return false;
                }
            }

            $this->commit();
            return true;
        } catch (Exception $e) {
            $this->rollback();
            return false;
        }
    }

    /**
     * Get table structure
     *
     * @param string $table
     * @return array
     */
    public function getTableStructure($table)
    {
        $sql = 'DESCRIBE ' . _DB_PREFIX_ . $table;
        return $this->executeS($sql);
    }

    /**
     * Check if table exists
     *
     * @param string $table
     * @return bool
     */
    public function tableExists($table)
    {
        $sql = 'SHOW TABLES LIKE \'' . _DB_PREFIX_ . $table . '\'';
        $result = $this->executeS($sql);
        return !empty($result);
    }

    /**
     * Create table
     *
     * @param string $table
     * @param array $fields
     * @param string $engine
     * @return bool
     */
    public function createTable($table, $fields, $engine = 'InnoDB')
    {
        $sql = 'CREATE TABLE IF NOT EXISTS ' . _DB_PREFIX_ . $table . ' (';
        $field_definitions = [];

        foreach ($fields as $field => $definition) {
            $field_definitions[] = $field . ' ' . $definition;
        }

        $sql .= implode(', ', $field_definitions);
        $sql .= ') ENGINE=' . $engine . ' DEFAULT CHARSET=utf8';

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

    /**
     * Drop table
     *
     * @param string $table
     * @return bool
     */
    public function dropTable($table)
    {
        $sql = 'DROP TABLE IF EXISTS ' . _DB_PREFIX_ . $table;
        return $this->execute($sql);
    }

    /**
     * Add column
     *
     * @param string $table
     * @param string $column
     * @param string $definition
     * @return bool
     */
    public function addColumn($table, $column, $definition)
    {
        $sql = 'ALTER TABLE ' . _DB_PREFIX_ . $table . ' ADD COLUMN ' . $column . ' ' . $definition;
        return $this->execute($sql);
    }

    /**
     * Drop column
     *
     * @param string $table
     * @param string $column
     * @return bool
     */
    public function dropColumn($table, $column)
    {
        $sql = 'ALTER TABLE ' . _DB_PREFIX_ . $table . ' DROP COLUMN ' . $column;
        return $this->execute($sql);
    }

    /**
     * Add index
     *
     * @param string $table
     * @param string $index
     * @param array $columns
     * @return bool
     */
    public function addIndex($table, $index, $columns)
    {
        $sql = 'ALTER TABLE ' . _DB_PREFIX_ . $table . ' ADD INDEX ' . $index . ' (' . implode(', ', $columns) . ')';
        return $this->execute($sql);
    }

    /**
     * Drop index
     *
     * @param string $table
     * @param string $index
     * @return bool
     */
    public function dropIndex($table, $index)
    {
        $sql = 'ALTER TABLE ' . _DB_PREFIX_ . $table . ' DROP INDEX ' . $index;
        return $this->execute($sql);
    }

    /**
     * Get table size
     *
     * @param string $table
     * @return int
     */
    public function getTableSize($table)
    {
        $sql = 'SELECT COUNT(*) as count FROM ' . _DB_PREFIX_ . $table;
        $result = $this->getValue($sql);
        return (int) $result;
    }

    /**
     * Optimize table
     *
     * @param string $table
     * @return bool
     */
    public function optimizeTable($table)
    {
        $sql = 'OPTIMIZE TABLE ' . _DB_PREFIX_ . $table;
        return $this->execute($sql);
    }

    /**
     * Repair table
     *
     * @param string $table
     * @return bool
     */
    public function repairTable($table)
    {
        $sql = 'REPAIR TABLE ' . _DB_PREFIX_ . $table;
        return $this->execute($sql);
    }

    /**
     * Get database size
     *
     * @return int
     */
    public function getDatabaseSize()
    {
        $sql = 'SELECT SUM(data_length + index_length) as size FROM information_schema.tables WHERE table_schema = \'' . _DB_NAME_ . '\'';
        $result = $this->getValue($sql);
        return (int) $result;
    }

    /**
     * Get slow queries
     *
     * @param int $limit
     * @return array
     */
    public function getSlowQueries($limit = 10)
    {
        $sql = 'SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT ' . (int) $limit;
        return $this->executeS($sql);
    }

    /**
     * Get query statistics
     *
     * @return array
     */
    public function getQueryStatistics()
    {
        $sql = 'SHOW STATUS LIKE \'Com_%\'';
        return $this->executeS($sql);
    }

    /**
     * Get process list
     *
     * @return array
     */
    public function getProcessList()
    {
        $sql = 'SHOW PROCESSLIST';
        return $this->executeS($sql);
    }

    /**
     * Kill process
     *
     * @param int $process_id
     * @return bool
     */
    public function killProcess($process_id)
    {
        $sql = 'KILL ' . (int) $process_id;
        return $this->execute($sql);
    }

    /**
     * Get table status
     *
     * @param string $table
     * @return array
     */
    public function getTableStatus($table)
    {
        $sql = 'SHOW TABLE STATUS LIKE \'' . _DB_PREFIX_ . $table . '\'';
        return $this->getRow($sql);
    }

    /**
     * Get database variables
     *
     * @return array
     */
    public function getDatabaseVariables()
    {
        $sql = 'SHOW VARIABLES';
        return $this->executeS($sql);
    }

    /**
     * Set database variable
     *
     * @param string $variable
     * @param string $value
     * @return bool
     */
    public function setDatabaseVariable($variable, $value)
    {
        $sql = 'SET ' . $variable . ' = \'' . $this->escape($value) . '\'';
        return $this->execute($sql);
    }

    /**
     * Get last query time
     *
     * @return float
     */
    public function getLastQueryTime()
    {
        return $this->last_query_time ?? 0;
    }

    /**
     * Get query count
     *
     * @return int
     */
    public function getQueryCount()
    {
        return $this->query_count ?? 0;
    }

    /**
     * Reset query statistics
     */
    public function resetQueryStatistics()
    {
        $this->last_query_time = 0;
        $this->query_count = 0;
    }

    /**
     * Get cached queries
     *
     * @return array
     */
    public function getCachedQueries()
    {
        if ($this->is_cache_enabled) {
            return Cache::getInstance()->get('db_cached_queries') ?? [];
        }

        return [];
    }

    /**
     * Clear query cache
     */
    public function clearQueryCache()
    {
        if ($this->is_cache_enabled) {
            Cache::getInstance()->clean('db_*');
        }
    }

    /**
     * Get database performance metrics
     *
     * @return array
     */
    public function getPerformanceMetrics()
    {
        return [
            'query_count' => $this->getQueryCount(),
            'last_query_time' => $this->getLastQueryTime(),
            'cached_queries' => count($this->getCachedQueries()),
            'database_size' => $this->getDatabaseSize(),
            'slow_queries' => count($this->getSlowQueries())
        ];
    }
}