<?php

namespace Velis\Db;

use ArrayObject;
use LogicException;
use PDO;
use PDOException;
use PDOStatement;
use ReflectionException;
use Velis\App;
use Velis\Arrays;
use Velis\Config\ConfigInterface;
use Velis\Db\EntityHook\EntityHookExecutor;
use Velis\Db\EntityHook\EventType;
use Velis\Filter;
use Velis\Log\Sentry\BreadcrumbHelper;
use Velis\Model\Cacheable;
use Velis\Model\DataObject;
use Velis\Model\ItemCacheable;

/**
 * Database abstraction library
 * @author Olek Procki <olo@velis.pl>
 */
abstract class Db
{
    protected ?PDO $_pdo = null;
    protected ?string $_currentHost = null;
    protected $_debug                = true;
    protected $_transactionStarted   = false;
    protected $_transactionTriggers  = [];
    protected $_affectedRows;
    protected $_lastExecutionTime    = 0;
    protected $_objectBirthTime      = 0;

    /**
     * @var Profiler
     */
    protected $_profiler;

    /**
     * @var array<string, array<string, mixed>>
     */
    protected array $_executedQueries = [];

    /**
     * @var array<string, array<string, mixed>>
     */
    protected array $_requestQueryCache = [];

    protected bool $_duplicatedQueryCheck = false;

    public $extractMetadata      = false;
    public $metadata             = array();

    protected ?ConfigInterface $_connectionParams = null;

    /**
     * columns list buffer
     * @var array
     */
    protected $_objectsColumns = [];


    /**
     * Delayed queries list
     */
    protected $_queryQueue = [];


    /**
     * @var EntityRepository[]
     */
    private array $repositories = [];

    /**
     * @var ClassMetadata[]
     */
    private array $classMetadata = [];

    public function __construct(ConfigInterface $connectionParams = null)
    {
        $this->_debug                = App::devMode();
        $this->_objectBirthTime      = microtime(true);
        $this->_duplicatedQueryCheck = (bool) App::$config->db->duplicatedQueryCheck;

        if (!App::isConsole()) {
            $this->_profiler = App::$di->get('db.profiler');
        }

        if ($connectionParams) {
            $this->_connectionParams = $connectionParams;
        }
    }


    /**
     * Checks if database has been initialized
     * @return bool
     */
    public static function isInitialized()
    {
        return self::$_instance != null;
    }

    /**
     * @param ?array<string, mixed> $profilerInfo
     */
    abstract public function connect(array &$profilerInfo = null): self;

    /**
     * Reconnect if no connection
     * @param ?array<string, mixed> $profilerInfo
     */
    public function checkConnection(array &$profilerInfo = null): void
    {
        if (!$this->isConnected()) {
            $this->connect($profilerInfo);
        }
    }

    abstract public function disconnect(): self;

    abstract public function isConnected(): bool;

    /**
     * Returns datasource (table/view) columns list
     * @return array<string, array<string, mixed>>
     */
    abstract public function getColumns(string $dataSource): array;

    /**
     * Returns profiler array
     * @return Profiler
     */
    public function getProfiler()
    {
        return $this->_profiler;
    }


    /**
     * Turns off debug mode
     * @return $this
     */
    public function setDebugOff()
    {
        $this->_debug = false;

        return $this;
    }


    /**
     * Turn on debug mode
     * @return $this
     */
    public function setDebugOn()
    {
        $this->_debug = true;

        return $this;
    }


    /**
     * Returns caller info (from debug_backtrace())
     * @return array
     */
    protected function _getCaller()
    {
        $backtrace = debug_backtrace();
        $callerInfo = null;

        foreach ($backtrace as $key => $backtraceRow) {
            $backtraceRow['file'] = $backtraceRow['file'] ?? '';
            $backtraceRow['line'] = $backtraceRow['line'] ?? null;

            if ($backtraceRow['file'] != __FILE__) {
                $fileName = str_replace(APP_PATH, '', $backtraceRow['file']);
                $fileName = str_replace(ROOT_PATH, '', $fileName);

                if (!isset($callerInfo)) {
                    $callerInfo = [];

                    $callerInfo['file'] = $fileName;
                    $callerInfo['line'] = $backtraceRow['line'];
                    $callerInfo['func'] = $backtrace[$key + 1]['function'];
                } else {
                    if (array_key_exists($key + 1, $backtrace)) {
                        $func = $backtrace[$key + 1]['function'];
                        if ($func == '__get') {
                            $func .= '(' . implode(',', $backtrace[$key + 1]['args']) . ')';
                        }
                    } else {
                        $func = '';
                    }

                    $callerInfo['backtrace'][] = [
                        'file' => $fileName,
                        'line' => $backtraceRow['line'],
                        'func' => $func,
                    ];
                }
            }
        }

        return $callerInfo;
    }


    /**
     * Registers executed query
     *
     * @param string $sql
     * @param array|ArrayObject $params
     */
    protected function _registerQuery($sql, $params = [])
    {
        $queryHash = md5($sql . serialize($params));
        $queryStats = $this->_executedQueries[$queryHash] ?? null;

        if ($queryStats) {
            $queryStats['calls']++;
            $queryStats['trace'][] = $this->_getCaller();
        } else {
            $queryStats = [
                'sql' => str_replace("\n", '\n', $sql),
                'params' => $params,
                'calls' => 1,
                'trace' => [
                    $this->_getCaller(),
                ],
            ];
        }
        $this->_executedQueries[$queryHash] = $queryStats;
    }


    /**
     * Returns duplicated query list
     * @return array
     */
    public function getDuplicatedQueries()
    {
        $duplicated = array();
        foreach ($this->_executedQueries as $queryStats) {
            if ($queryStats['calls'] > 1) {
                $duplicated[] = $queryStats;
            }
        }

        return $duplicated;
    }

    /**
     * @return array<string, array<string, mixed>>
     */
    public function getExecutedQueries(): array
    {
        return $this->_executedQueries;
    }

    /**
     * Turns on/off duplicated query check
     * @param bool $enabled
     * @return bool
     */
    public function checkDuplicatedQueries($enabled = true)
    {
        $alreadyEnabled = $this->_duplicatedQueryCheck;
        $this->_duplicatedQueryCheck = $enabled;
        return $alreadyEnabled != $enabled;
    }


    /**
     * Runs queries (without binding params)
     *
     * @param array<string, mixed> $profilerInfo
     * @return PDOStatement|false
     * @throws Exception
     */
    protected function _query(string $sql, array &$profilerInfo = null)
    {
        if ($this->_debug) {
            $profilerInfo['query'] = $sql;
        }

        try {
            $this->_lastExecutionTime = 0;
            $startTime = microtime(true);

            App::getService(BreadcrumbHelper::class)->addSqlQuery($sql);

            $stmt = $this->_pdo->query($sql);

            if ($this->_duplicatedQueryCheck) {
                $this->_registerQuery($sql);
            }

            $this->_lastExecutionTime = microtime(true) - $startTime;

            if ($this->_debug) {
                $profilerInfo['execTime'] = $this->_lastExecutionTime;
            }

            if ($this->extractMetadata) {
                $this->_extractMetadata($stmt);
            }

            return $stmt;
        } catch (PDOException $e) {
            throw new Exception(
                $e->getMessage(),
                $e->getCode(),
                $this->_currentHost,
                $sql
            );
        }
    }


    /**
     * Run queries (with binding params & DML queries)
     *
     * @param string $sql - kwerenda SQL
     * @param array|ArrayObject $bindParams - parameters for prepared statement
     * @param array<string, mixed> $profilerInfo
     * @return PDOStatement|int|false
     * @throws Exception
     */
    protected function _exec(string $sql, $bindParams = null, array &$profilerInfo = null)
    {
        if ($this->_debug) {
            $profilerInfo['query']  = $sql;
            $profilerInfo['params'] = $bindParams;
            $profilerInfo['server'] = $this->_currentHost;
        }

        $this->checkConnection($profilerInfo);

        $params = $bindParams instanceof ArrayObject ? $bindParams->getArrayCopy() : $bindParams;

        if ($this->_debug) {
            $profilerInfo['params'] = $params;
        }

        try {
            $this->_lastExecutionTime = 0;
            $startTime = microtime(true);
            $stmt = null;

            if ($params && count($params)) {
                $stmt = $this->_pdo->prepare($sql);
                foreach ($params as $param => $data) {
                    if ($data instanceof Lob) {
                        $stmt->bindValue(':' . $param, $data, PDO::PARAM_LOB);
                    } else {
                        $stmt->bindValue(':' . $param, $data, PDO::PARAM_STR);
                    }
                }

                if ($this->_duplicatedQueryCheck) {
                    $this->_registerQuery($sql, $params);
                }

                App::getService(BreadcrumbHelper::class)->addSqlQuery($sql, $params);

                $stmt->execute();

                $this->_affectedRows = $stmt->rowCount();
            } else {
                $this->_affectedRows = $this->_pdo->exec($sql);
            }

            $this->_lastExecutionTime = microtime(true) - $startTime;

            if ($this->_debug) {
                $profilerInfo['execTime'] = $this->_lastExecutionTime;
            }

            if ($this->extractMetadata && $stmt) {
                $this->_extractMetadata($stmt);
            }

            return $stmt ? $stmt : $this->_affectedRows;
        } catch (PDOException $e) {
            throw new Exception(
                $e->getMessage(),
                $e->getCode(),
                $this->_currentHost,
                $sql,
                $params
            );
        }
    }


    /**
     * Imports large amounts of data using one prepared statement
     *
     * @param string $table
     * @param array  $rows
     */
    public function import($table, $rows)
    {
        $row = reset($rows);

        $columns = ($row instanceof ArrayObject) ? $row->getArrayCopy() : $row;

        $query = "INSERT INTO $table(" . implode(",", array_keys($columns)) . ") VALUES(";
        $query .= ':' . implode(',:', $columns) . ')';

        $stmt = $this->_pdo->prepare($query);

        foreach ($rows as $row) {
            $stmt->execute($row);
        }
    }


    /**
     * Prepares SQL statement
     * @param string $sql
     * @return PDOStatement
     */
    public function prepare($sql)
    {
        return $this->_pdo->prepare($sql);
    }


    /**
     * Quote string
     * @param string $value
     * @return string
     */
    public function quote($value)
    {
        $this->checkConnection();

        return $this->_pdo->quote($value);
    }


    /**
     * Returns NullValue object
     * @return NullValue
     */
    public function getNull()
    {
        return new NullValue();
    }


    /**
     * Run DML statement (no result expected - INSERT, UPDATE, DELETE)
     *
     * @param string $sql
     * @param array|ArrayObject $bindParams
     * @param array|null $profilerInfo
     * @return int
     */
    public function execDML($sql, $bindParams = null, array &$profilerInfo = null)
    {
        if ($this->_debug && $profilerInfo === null) {
            $profilerInfo = array();
            $profilerInfo['caller']    = $this->_getCaller();
            $profilerInfo['func']      = __FUNCTION__;
            $profilerInfo['startTime'] = $this->getCurrentLifeTime();
            $profilerInfo['server']    = $this->_currentHost;
        }

        $this->checkConnection($profilerInfo);
        $result = $this->_exec($sql, $bindParams, $profilerInfo);

        if ($this->shouldLogToProfiler()) {
            $this->_profiler->addRow($profilerInfo);
        }

        return $result;
    }


    /**
     * Adds query to queue - execute before connection closing
     * @param string $sql
     * @param array|ArrayObject $bindParams
     */
    public function execDelayed($sql, $bindParams = null)
    {
        $this->_queryQueue[] = [
            'sql' => $sql,
            'params' => $bindParams,
        ];
    }


    /**
     * Executes delayed queries
     * @return $this
     */
    protected function _executeDelayedQueries()
    {
        foreach ($this->_queryQueue as $query) {
            try {
                $this->_exec($query['sql'], $query['params']);
            } catch (Exception $e) {
                // ignore unique constraint violation exceptions
                if ($e->getCode() !== Exception::CODE_DUPLICATED) {
                    throw $e;
                }
            }
        }

        return $this;
    }


    /**
     * Gets query result
     *
     * @param array|ArrayObject $bindParams
     * @param array<string, mixed> $profilerInfo
     * @return PDOStatement|resource|int
     */
    public function get(string $sql, $bindParams = null, array &$profilerInfo = null)
    {
        if ($profilerInfo === null) {
            $profilerInfo = [];
            $profilerInfo['caller']    = $this->_getCaller();
            $profilerInfo['startTime'] = $this->getCurrentLifeTime();
            $profilerInfo['func']      = __FUNCTION__;
            $profilerInfo['server']    = $this->_currentHost;

            $innerCall = false;
        } else {
            $innerCall = true;
        }

        $this->checkConnection($profilerInfo);

        if (is_iterable($bindParams) && count($bindParams)) {
            $result = $this->_exec($sql, $bindParams, $profilerInfo);
        } else {
            $result = $this->_query($sql, $profilerInfo);
        }

        if (!$innerCall && $this->shouldLogToProfiler()) {
            $this->_profiler->addRow($profilerInfo);
        }

        return $result;
    }


    /**
     * Gets all result fetched into array
     *
     * @param string $sql
     * @param array|ArrayObject $bindParams
     * @param string $assocKey
     */
    public function getAll($sql, $bindParams = null, $assocKey = null): array
    {
        if ($this->_debug) {
            $profilerInfo = [];
            $profilerInfo['caller']    = $this->_getCaller();
            $profilerInfo['startTime'] = $this->getCurrentLifeTime();
            $profilerInfo['func']      = __FUNCTION__;
            $profilerInfo['server']    = $this->_currentHost;
        }

        $fetchStartTime = microtime(true);

        $stmt = $this->get($sql, $bindParams, $profilerInfo);
        $result = [];

        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $row = $this->filterRow($row);
            $this->fetchLobs($row);
            if ($assocKey != null) {
                $result[$row[$assocKey]] = $row;
            } else {
                $result[] = $row;
            }
        }

        if ($this->shouldLogToProfiler()) {
            $profilerInfo['fetchTime'] = microtime(true) - $fetchStartTime;
            $this->_profiler->addRow($profilerInfo);
        }

        return $result;
    }

    /**
     * Perform some additional operations on row fetched from database, if needed
     * @param array<string, mixed> $row
     * @return array<string, mixed>
     */
    protected function filterRow(array $row): array
    {
        return $row;
    }

    /**
     * Adds a cursor declaration and returns cursor name
     *
     * @param string $name cursor name
     * @param string $sql query
     * @param array $params
     * @return string
     */
    public function addCursorDeclaration(string $name, string $sql, array $params = null): string
    {
        $name = Filter::filterAlnum($name);
        if (str_contains($sql, ';')) {
            throw new Exception('Cursor query cannot contain semicolon');
        }
        $cursorSql = "DECLARE $name SCROLL CURSOR FOR SELECT v.* FROM ($sql) v";
        $stmt = $this->prepare($cursorSql);
        $stmt->execute($params);
        return $name;
    }


    /**
     * Fetches data from cursor into array
     */
    public function fetchCursor(PDOStatement $stmt): array
    {
        if ($this->_debug) {
            $profilerInfo = array();
            $profilerInfo['caller']    = $this->_getCaller();
            $profilerInfo['func']      = __FUNCTION__;
            $profilerInfo['startTime'] = $this->getCurrentLifeTime();
            $profilerInfo['server']    = $this->_currentHost;
        }

        $this->_lastExecutionTime = 0;
        $startTime = microtime(true);

        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $this->_lastExecutionTime = microtime(true) - $startTime;

        if ($this->_debug) {
            $profilerInfo['execTime'] = $this->_lastExecutionTime;
        }

        if ($this->extractMetadata) {
            $this->_extractMetadata($stmt);
        }

        if ($this->shouldLogToProfiler()) {
            $this->_profiler->addRow($profilerInfo);
        }
        return $result;
    }

    /**
     * Fetch lobs for row
     * @param array<string, mixed> $row
     */
    public function fetchLobs(array &$row): void
    {
        foreach ($row as $field => $data) {
            if (is_resource($data)) {
                $row[$field] = stream_get_contents($data);
            }
        }
    }

    /**
     * Fetches all rows from table
     *
     * @param string $tableName
     * @param string $assocKey
     *
     * @return array
     */
    public function fetchTable($tableName, $assocKey = null)
    {
        return $this->getAll("SELECT * FROM " . $tableName, null, $assocKey);
    }

    /**
     * Returns one row from result
     *
     * @param array<string, mixed>|ArrayObject<string, mixed> $bindParams
     * @return array<string, mixed>|false
     */
    public function getRow(string $sql, $bindParams = null): array|false
    {
        if ($this->_debug) {
            $profilerInfo = [];
            $profilerInfo['caller']    = $this->_getCaller();
            $profilerInfo['startTime'] = $this->getCurrentLifeTime();
            $profilerInfo['func']      = __FUNCTION__;
            $profilerInfo['server']    = $this->_currentHost;
        }

        $fetchStartTime = microtime(true);

        $result = $this
            ->get($sql, $bindParams, $profilerInfo)
            ->fetch(PDO::FETCH_ASSOC);

        if (is_array($result)) {
            $result = $this->filterRow($result);
            $this->fetchLobs($result);
        }

        if ($this->shouldLogToProfiler()) {
            $profilerInfo['fetchTime'] = microtime(true) - $fetchStartTime;
            $this->_profiler->addRow($profilerInfo);
        }

        return $result;
    }

    /**
     * Returns single(first) field value from first result row
     *
     * @param array<string, mixed>|ArrayObject<string, mixed> $bindParams
     * @return scalar|null
     */
    public function getOne(string $sql, $bindParams = null)
    {
        $row = $this->getRow($sql, $bindParams);
        if (!$row) {
            return null;
        }

        $result = reset($row);

        return ($result === false) ? null : $result;
    }

    /**
     * Gets query result from app cache
     *
     * @param string $sql
     * @param array|ArrayObject $bindParams
     * @param string $assocKey
     * @param int|bool $cacheTime (passing false = default cache time)
     * @param array $profilerInfo
     *
     * @return array
     */
    protected function _cacheExec($sql, $bindParams = null, $assocKey = null, $cacheTime = false, array &$profilerInfo = null)
    {
        $startTime = microtime(true);
        $cacheKey  = 'sql_' . md5($sql . serialize($bindParams));

        if ($this->_debug) {
            $profilerInfo['query']  = $sql;
            $profilerInfo['params'] = $bindParams;
            $profilerInfo['info']   = "CACHED for " . ($cacheTime ?: "request") . " with id: " . $cacheKey;
        }

        $cachedValue = null;

        if ($cacheTime) {
            $cachedValue = App::$cache[$cacheKey];
        } else {
            if (array_key_exists($cacheKey, $this->_requestQueryCache)) {
                $cachedValue = $this->_requestQueryCache[$cacheKey];
            }
        }

        if ($cachedValue === null) {
            $cachedValue = [
                'sql' => $sql,
                'result' => $this->getAll($sql, $bindParams, $assocKey),
            ];
            if ($cacheTime) {
                App::$cache->set($cacheKey, $cachedValue, $cacheTime);
            } else {
                $this->_requestQueryCache[$cacheKey] = $cachedValue;
            }
        }

        if ($this->_debug) {
            $profilerInfo['cacheAccessTime'] = microtime(true) - $startTime;
        }

        return $cachedValue['result'];
    }


    /**
     * Returns cached query full result
     *
     * @param string $sql
     * @param array|ArrayObject $bindParams
     * @param string|null $assocKey
     * @param int|bool $cacheTime
     *
     * @return array
     */
    public function cacheGetAll($sql, $bindParams = null, $assocKey = null, $cacheTime = false)
    {
        if ($this->_debug) {
            $profilerInfo = array();
            $profilerInfo['caller']    = $this->_getCaller();
            $profilerInfo['func']      = __FUNCTION__;
            $profilerInfo['startTime'] = $this->getCurrentLifeTime();
        }

        $result = $this->_cacheExec($sql, $bindParams, $assocKey, $cacheTime, $profilerInfo);

        if ($this->shouldLogToProfiler()) {
            $this->_profiler->addRow($profilerInfo);
        }

        return $result;
    }


    /**
     * Returns single row from cached query result
     *
     * @param string $sql
     * @param array|ArrayObject $bindParams
     * @param int|bool $cacheTime
     *
     * @return array
     */
    public function cacheGetRow($sql, $bindParams = null, $cacheTime = false)
    {
        if ($this->_debug) {
            $profilerInfo = array();
            $profilerInfo['caller']    = $this->_getCaller();
            $profilerInfo['func']      = __FUNCTION__;
            $profilerInfo['startTime'] = $this->getCurrentLifeTime();
        }

        $result = $this->_cacheExec($sql, $bindParams, null, $cacheTime, $profilerInfo);

        if ($this->shouldLogToProfiler()) {
            $this->_profiler->addRow($profilerInfo);
        }

        return reset($result);
    }


    /**
     * Returns single value from cached query result
     *
     * @param array<string, mixed>|ArrayObject<string, mixed> $bindParams
     * @param int|bool $cacheTime
     *
     * @return mixed
     */
    public function cacheGetOne(string $sql, array $bindParams = null, $cacheTime = false)
    {
        $result = Arrays::toArray($this->cacheGetRow($sql, $bindParams, $cacheTime));

        if (!$result) {
            return null;
        }

        return reset($result);
    }


    /**
     * Inserts row into $table
     *
     * @param string $table
     * @param array<string, mixed>|ArrayObject<string, mixed> $params
     * @param array<string> $noQuote
     * @param bool $bind
     * @param bool $delayed
     * @param bool $ignore (perform insert ignore)
     * @return PDOStatement|int|void
     */
    public function insert(
        $table,
        array|ArrayObject $params,
        array $noQuote = [],
        $bind = true,
        $delayed = false,
        $ignore = false,
    ) {
        [$bindParams, $query] = $this->prepareInsertQuery($params, $table, $bind, $noQuote);

        if ($ignore) {
            $query .= " ON CONFLICT DO NOTHING";
        }

        if ($delayed) {
            $this->execDelayed($query, $bindParams);
        } else {
            return $this->execDML($query, $bindParams);
        }
    }

    /**
     * @param array<string, mixed>|ArrayObject<string, mixed> $params
     * @return scalar|null
     */
    public function insertReturning(string $table, string $returningField, array $params): mixed
    {
        [$bindParams, $query] = $this->prepareInsertQuery($params, $table);
        $query .= " RETURNING $returningField";

        return $this->getOne($query, $bindParams);
    }


    /**
     * Adds insert query to delayed queue
     *
     * @param string $table
     * @param array|ArrayObject $params
     * @param array $noQuote
     * @param bool $bind
     * @param bool $ignore
     *
     * @return PDOStatement|int|null
     */
    public function insertDelayed($table, $params = null, array $noQuote = array(), $bind = true, $ignore = false)
    {
        return $this->insert($table, $params, $noQuote, $bind, true, $ignore);
    }


    /**
     * Shortcut for insert with IGNORE option
     *
     * @param string $table
     * @param array|ArrayObject $params
     * @param array $noQuote
     * @param bool $bind
     * @param bool $delayed
     * @return PDOStatement|int
     */
    public function insertIgnore($table, $params = null, $noQuote = array(), $bind = true, $delayed = false)
    {
        return $this->insert($table, $params, $noQuote, $bind, $delayed, true);
    }


    /**
     * Updates row(s) in $table
     *
     * @param string $table              table name
     * @param array|ArrayObject $params  values to update
     * @param array $where               condition values
     * @param array $noQuote             no quote/no bind (use also with statements like NOW() etc)
     * @param bool $bind                 use variable binding
     * @param bool $updateOne            update only one row
     *
     * @return int
     */
    public function update($table, $params, array $where, array $noQuote = array(), $bind = true, $updateOne = false)
    {
        if (!count($params)) {
            return 0;
        }

        if ($bind) {
            $bindParams = [];
        }

        $query = "UPDATE $table SET ";
        $queryValues = '';

        foreach ($params as $key => $value) {
            if (strlen($queryValues)) {
                $queryValues .= ",";
            }

            if (gettype($value) === 'boolean') {
                $value = $value ? 't' : 'f';
            }

            if ($bind === true && !in_array($key, $noQuote) && $value !== '') {
                $bindKey = str_replace('.', '_', $key);
                $queryValues .= "$key=:" . $bindKey;
                $bindParams[$bindKey] = $value;
            } else {
                if (in_array($key, $noQuote)) {
                    $queryValues .= "$key=$value";
                } elseif ($value === '') {
                    $queryValues .= $key . ' = NULL';
                } else {
                    $queryValues .= "$key='" . ($this->_quotedParams ? $value : $this->_pdo->quote($value)) . "'";
                }
            }
        }

        if (sizeof($where)) {
            $query .= "$queryValues WHERE 1=1";
            $whereQuery = '';

            foreach ($where as $key => $value) {
                if ($bind == true && !in_array($key, $noQuote)) {
                    if (!is_array($value)) {
                        $bindKey = str_replace('.', '_', $key);
                        if ($value === null || $value instanceof NullValue) {
                            $whereQuery .= " AND " . $key . " IS NULL ";
                        } elseif ($value instanceof AnyValue) {
                            $whereQuery .= " AND " . $key . " IS NOT NULL ";
                        } else {
                            $whereQuery .= " AND $key=:$bindKey";
                            $bindParams[$bindKey] = $value;
                        }
                    } else {
                        // array of values
                        $values    = Arrays::filterKeepZeros($value);
                        $separator = "";

                        if (count($values)) {
                            $whereQuery .= " AND " . $key . " IN ( ";

                            foreach ($values as $k => $oneValue) {
                                $whereQuery .= $separator . ":" . $key . "_" . $k;
                                $bindParams[$key . "_" . $k] = $oneValue;

                                $separator = ", ";
                            }
                            $whereQuery .= " ) ";
                        }
                    }
                } else {
                    $whereQuery .= " AND $key=";
                    if (in_array($key, $noQuote)) {
                        $whereQuery .= $value;
                    } else {
                        $whereQuery .= $key . "='" . ($this->_quotedParams ? $value : $this->_pdo->quote($value)) . "'";
                    }
                }
            }
            $query .= $whereQuery;
        }
        if ($updateOne) {
            $query .= " LIMIT 1";
        }

        $this->execDML($query, $bindParams);

        return $this->getAffectedRows();
    }


    /**
     * Returns object lifetime as formatted string
     * @return string
     */
    public function getCurrentLifeTime()
    {
        return str_pad(round(microtime(true) - $this->_objectBirthTime, 4), 6, "0", STR_PAD_RIGHT);
    }


    /**
     * Gets last inserted id from autoincrement field in last query (current sequence value)
     * @return int
     */
    public function getLastInsertId()
    {
        return $this->_pdo->lastInsertId();
    }


    /**
     * Returns SQL error code
     * @return int
     */
    public function getError()
    {
        return $this->_pdo->errorCode();
    }


    /**
     * Returns rows affected by last query
     * @return int
     */
    public function getAffectedRows()
    {
        return $this->_affectedRows;
    }


    /**
     * Starts new transaction
     *
     * @param array $profilerInfo
     */
    public function startTrans(&$profilerInfo = null): bool
    {
        if (!$this->_transactionStarted) {
            $this->_transactionStarted = $this->_pdo->beginTransaction();
            if ($this->shouldLogToProfiler()) {
                $this->_profiler->addRow($profilerInfo);
            }

            return true;
        }

        return false;
    }


    /**
     * Returns true if transaction is started
     * @return bool
     */
    public function isTransactionStarted()
    {
        return $this->_transactionStarted;
    }


    /**
     * Registers triggers to be executed at the end of transaction
     * @param callable|array<callable> $trigger
     */
    public function onCommit($trigger)
    {
        if (is_callable($trigger)) {
            $this->_transactionTriggers[] = $trigger;
        } elseif (is_array($trigger)) {
            foreach ($trigger as $callback) {
                if (is_callable($callback)) {
                    $this->_transactionTriggers[] = $callback;
                }
            }
        }
    }

    /**
     * Commits transaction
     * @throws \Exception
     */
    public function commit(): self
    {
        if ($this->_transactionStarted) {
            try {
                if (count($this->_transactionTriggers)) {
                    $triggers = $this->_transactionTriggers;
                    $this->_transactionTriggers = array();

                    foreach ($triggers as $callback) {
                        call_user_func($callback);
                    }
                }

                if ($this->_transactionStarted) {
                    $this->_pdo->commit();
                }
            } catch (\Exception $e) {
                $this->rollback();
                throw $e;
            }
            $this->_transactionStarted = false;
        }

        return $this;
    }


    /**
     * Rollbacks transaction
     * @return $this
     */
    public function rollback(): self
    {
        if ($this->_transactionStarted) {
            $this->_pdo->rollBack();

            $this->_transactionTriggers = array();
            $this->_transactionStarted  = false;
        }

        return $this;
    }


    /**
     * Closes connections
     * @return bool
     */
    public function close()
    {
        $this->_executeDelayedQueries();

        // rollback hanging transaction
        if ($this->_transactionStarted) {
            $this->rollback();
        }

        $this->_pdo = null;

        return true;
    }


    /**
     * disallows object cloning
     */
    public function __clone()
    {
        throw new Exception('Clone is not allowed.');
    }


    /**
     * Destructor
     */
    public function __destruct()
    {
        $this->close();
    }


    /**
     * set column types to $this->metadata
     * @param PDOStatement $stmt
     */
    protected function _extractMetadata($stmt)
    {
        $this->metadata = array();

        foreach (range(0, $stmt->columnCount() - 1) as $column_index) {
            if ($column_index >= 0) {
                $metaColumn = $stmt->getColumnMeta($column_index);
                $this->metadata[$metaColumn['name']] = $metaColumn;
            }
        }
    }


    /**
     * @template T of DataObject
     * @param class-string<T> $entityClass
     * @return EntityRepository<T>
     * @throws ReflectionException
     * @throws \Exception
     */
    public function getRepository(string $entityClass): EntityRepository
    {
        if (!array_key_exists($entityClass, $this->repositories)) {
            $this->repositories[$entityClass] = $this->createRepository($entityClass);
        }

        return $this->repositories[$entityClass];
    }


    /**
     * @template T of DataObject
     * @param class-string<T> $entityClass
     * @return EntityRepository<T>
     * @throws \Exception
     */
    private function createRepository(string $entityClass): EntityRepository
    {
        if (!class_exists($entityClass)) {
            throw new Exception(sprintf('Class %s not found', $entityClass));
        }

        $entityInstance = new $entityClass();
        if (!$entityInstance instanceof DataObject) {
            throw new Exception(sprintf('%s is not an instance of %s', $entityClass, DataObject::class));
        }

        $classMetadata = $this->getClassMetadata($entityClass);
        $repositoryClass = $classMetadata->getRepositoryClass();

        return new $repositoryClass($this, $entityClass);
    }

    /**
     * @param class-string<DataObject> $entityClass
     * @throws \Exception
     */
    public function getClassMetadata(string $entityClass): ClassMetadata
    {
        if (!array_key_exists($entityClass, $this->classMetadata)) {
            $classMetadata = App::$cache->get('db.classMetadata') ?? [];

            if (!array_key_exists($entityClass, $classMetadata)) {
                $classMetadata[$entityClass] = new ClassMetadata($entityClass);
                App::$cache->set('db.classMetadata', $classMetadata);
            }

            $this->classMetadata = $classMetadata;
        }

        return $this->classMetadata[$entityClass];
    }

    protected function unsetCache(DataObject $entity): void
    {
        if ($entity instanceof Cacheable) {
            $entity->unsetEntityCacheBuffer();

            // for ItemCacheable we should also unset item cache
            if ($entity instanceof ItemCacheable) {
                $itemCacheName = $entity::getItemCacheName($entity->id());
                unset(App::$cache[$itemCacheName]);
                $listKey = $entity::getListCacheName($entity);
                if (is_array(App::$cache[$listKey])) {
                    unset(App::$cache[$listKey][$entity->id()]);
                }
            } else {
                $entity::unsetListCache();
            }
        }
    }

    /**
     * @throws \Exception
     */
    public function remove(DataObject $entity): void
    {
        $this->executeHooks(EventType::PRE_REMOVE, $entity);

        $classMetadata = $this->getClassMetadata(get_class($entity));

        $query = 'DELETE FROM ' . $classMetadata->getTableName() . ' WHERE ';
        $primaryKeyField = $classMetadata->getPrimaryKeyField();

        $conditions = [];

        if (!is_array($primaryKeyField)) {
            $conditions[] = $primaryKeyField . ' = :' . $primaryKeyField;
            $params = [$primaryKeyField => $entity->id()];
        } else {
            foreach ($primaryKeyField as $fieldName) {
                $conditions[] = $fieldName . ' = :' . $fieldName;
            }
            $params = $entity->id();
        }

        $query .= implode(' AND ', $conditions);

        $this->execDML($query, $params);
        $this->unsetCache($entity);

        $this->executeHooks(EventType::POST_REMOVE, $entity);
    }

    /**
     * @throws \Exception
     */
    public function persist(DataObject $entity): void
    {
        $this->executeHooks(EventType::PRE_PERSIST, $entity);

        $class = get_class($entity);
        $classMetadata = $this->getClassMetadata($class);

        $fields = $class::getObjectFields();
        $entity->prepareCompositeFields();
        $data = $entity->getArrayCopy();
        $filteredData = Arrays::extractFields($data, $fields);

        if ($classMetadata->getPrimaryKeySeq()) {
            $id = $this->insertReturning(
                $classMetadata->getTableName(),
                $classMetadata->getPrimaryKeyField(),
                $filteredData
            );
            $entity[$classMetadata->getPrimaryKeyField()] = $id;
        } else {
            $this->insert($classMetadata->getTableName(), $filteredData);
        }

        $this->unsetCache($entity);

        $this->executeHooks(EventType::POST_PERSIST, $entity);
    }

    /**
     * @throws \Exception
     */
    public function merge(DataObject $entity): void
    {
        $this->executeHooks(EventType::PRE_MERGE, $entity);

        $class = get_class($entity);
        $classMetadata = $this->getClassMetadata($class);

        $fields = $class::getObjectFields();
        $entity->prepareCompositeFields();
        $data = $entity->getArrayCopy();
        $filteredData = Arrays::extractFields($data, $fields);
        $keyField = $classMetadata->getPrimaryKeyField();

        if (!is_array($keyField)) {
            $keyField = [$keyField];
        }

        $this->update($classMetadata->getTableName(), $filteredData, Arrays::extractFields($filteredData, $keyField));
        $this->unsetCache($entity);

        $this->executeHooks(EventType::POST_MERGE, $entity);
    }

    /**
     * @param array<string, mixed>|ArrayObject<string, mixed> $params
     * @param array<string> $noQuote
     * @return array{array<string, mixed>, string}
     */
    private function prepareInsertQuery(
        array|ArrayObject $params,
        string $table,
        bool $bind = true,
        array $noQuote = [],
    ): array {
        $columns = ($params instanceof ArrayObject) ? $params->getArrayCopy() : $params;
        $bindParams = [];

        $query = "INSERT " . "INTO $table(" . implode(",", array_keys($columns)) . ") VALUES(";
        $queryValues = '';

        foreach ($columns as $key => $value) {
            if (strlen($queryValues)) {
                $queryValues .= ", ";
            }
            if ($bind && !in_array($key, $noQuote) && $value !== '') {
                $queryValues .= ":" . $key;
                $bindParams[$key] = $value;
            } else {
                if (in_array($key, $noQuote)) {
                    $queryValues .= $value;
                } elseif ($value === '') {
                    $queryValues .= 'NULL';
                } else {
                    $queryValues .= "'" . ($this->_quotedParams ? $value : $this->_pdo->quote($value)) . "'";
                }
            }
        }
        $query .= $queryValues . ")";
        return [$bindParams, $query];
    }

    protected function shouldLogToProfiler(): bool
    {
        return $this->_debug && $this->_profiler;
    }

    /**
     * Transforms query parameters into SQL where statement
     *
     * @param array|ArrayObject $params
     * @return string
     */
    abstract public function conditions(&$params);

    /**
     * Get current value of a sequence
     */
    public function currval(string $sequence): int
    {
        throw new LogicException('Method not implemented');
    }

    /**
     * Set value of a sequence
     */
    public function setval(string $sequence, int $value): int
    {
        throw new LogicException('Method not implemented');
    }

    /**
     * Get the next value of a sequence
     */
    public function nextval(string $sequence): int
    {
        throw new LogicException('Method not implemented');
    }

    /**
     * @template T of DataObject
     * @param class-string<T> $entityClass
     * @return EntityCache<T>
     * @throws ReflectionException
     */
    public function getEntityCache(string $entityClass): EntityCache
    {
        return new EntityCache($this, $entityClass, App::$cache);
    }

    private function executeHooks(EventType $event, DataObject $object): void
    {
        $hookExecutor = App::getService(EntityHookExecutor::class);
        $hookExecutor->executeHooks($event, $object);
    }
}
