<?php

namespace app\classes;

class PDODatabase {

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

    /** @var list<array{0: string, 1: list<mixed>}> */
    private $recoveryCommands = [];

    /** @var list<array{0: string, 1: list<mixed>}> */
    private $recoveryTransactionCommands = [];

    /** @var int */
    private $transactionDepth = 0;

    /** @var string|null */
    private $recoveryDatabaseName;

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

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

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

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


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

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

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

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

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

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

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

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

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

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

        $sql .= $idCondition;

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

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

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

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

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

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

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

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

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

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

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

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

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

        if ($fetch_assoc) {
            return $query;
        }

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

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

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

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

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

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

    public function disableRealTime() {
        $this->realTime = false;
    }

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

    public function commit() {
        $this->sqlCommand("COMMIT; SET autocommit = 1;", array());
        if ($this->transactionDepth <= 0) {
            return;
        }
        $this->transactionDepth--;
        if ($this->transactionDepth === 0 && $this->recoveryTransactionCommands !== []) {
            foreach ($this->recoveryTransactionCommands as $command) {
                $this->recoveryCommands[] = $command;
            }
            $this->recoveryTransactionCommands = [];
        }
    }

    public function rollback() {
        $this->sqlCommand("ROLLBACK; SET autocommit = 1;", array());
        $this->transactionDepth = 0;
        $this->recoveryTransactionCommands = [];
    }

    /**
     * Buffer a successful data-modifying statement for the request recovery log.
     *
     * @param string $sql
     * @param array<string|int, mixed> $params
     */
    private function queueRecoveryLogCommand($sql, array $params) {
        if (!$this->isDataModifyingSql($sql) || $this->shouldSkipRecoveryLog($sql)) {
            return;
        }

        $command = $this->formatRecoveryCommand($sql, $params);
        if ($this->transactionDepth > 0) {
            $this->recoveryTransactionCommands[] = $command;
            return;
        }

        $this->recoveryCommands[] = $command;
    }

    /**
     * Write buffered recovery commands to disk once per request (JSON Lines).
     * Uncommitted transaction commands are discarded.
     */
    private function flushRecoveryLog() {
        $this->recoveryTransactionCommands = [];

        if ($this->recoveryCommands === []) {
            return;
        }

        $dir = \app\constants\DbChangelogConfig::getLogDirectory();
        if (!is_dir($dir) && !@mkdir($dir, 0755, true) && !is_dir($dir)) {
            return;
        }

        $entry = [
            'time' => $this->formatRecoveryTimestamp(microtime(true)),
            'database' => (string) ($this->recoveryDatabaseName ?? ''),
            'commands' => $this->recoveryCommands,
        ];

        $email = $this->getLoggedEmail();
        if ($email !== false) {
            $entry['user'] = $email;
        }

        $json = json_encode($entry, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        if ($json === false) {
            $this->recoveryCommands = [];
            return;
        }

        $file = $dir . DIRECTORY_SEPARATOR . date('Y-m-d') . '.log';
        @file_put_contents($file, $json . "\n", FILE_APPEND | LOCK_EX);
        $this->recoveryCommands = [];
    }

    /**
     * @param string $sql
     */
    private function isDataModifyingSql($sql) {
        return (bool) preg_match('/^\s*(INSERT|UPDATE|DELETE)\b/i', (string) $sql);
    }

    /**
     * Apply {@see \app\constants\DbChangelogConfig} table/column blacklists.
     *
     * @param string $sql
     */
    private function shouldSkipRecoveryLog($sql) {
        $sql = (string) $sql;
        $table = $this->extractRecoveryTableName($sql);
        if ($table !== null && \app\constants\DbChangelogConfig::isTableBlacklisted($table)) {
            return true;
        }

        if (!preg_match('/^\s*UPDATE\b/i', $sql)) {
            return false;
        }

        $columns = $this->extractUpdateSetColumns($sql);
        return \app\constants\DbChangelogConfig::areOnlyBlacklistedColumns($table, $columns);
    }

    /**
     * @param string $sql
     * @return string|null
     */
    private function extractRecoveryTableName($sql) {
        if (preg_match('/^\s*INSERT\s+INTO\s+`?([a-zA-Z0-9_]+)`?/i', $sql, $matches)) {
            return $matches[1];
        }
        if (preg_match('/^\s*UPDATE\s+`?([a-zA-Z0-9_]+)`?/i', $sql, $matches)) {
            return $matches[1];
        }
        if (preg_match('/^\s*DELETE\s+FROM\s+`?([a-zA-Z0-9_]+)`?/i', $sql, $matches)) {
            return $matches[1];
        }

        return null;
    }

    /**
     * @param string $sql
     * @return list<string>
     */
    private function extractUpdateSetColumns($sql) {
        if (!preg_match('/^\s*UPDATE\b.+?\bSET\s+(.+?)(?:\s+WHERE\b|\s*$)/is', $sql, $matches)) {
            return [];
        }

        if (!preg_match_all('/`?([a-zA-Z_][a-zA-Z0-9_]*)`?\s*=/', $matches[1], $columnMatches)) {
            return [];
        }

        return array_values(array_unique($columnMatches[1]));
    }

    /**
     * Normalize SQL + params to a compact replay tuple: [sqlWithPlaceholders, params].
     *
     * @param string $sql
     * @param array<string|int, mixed> $params
     * @return array{0: string, 1: list<mixed>}
     */
    private function formatRecoveryCommand($sql, array $params) {
        $sql = (string) $sql;
        $normalized = [];
        foreach ($params as $key => $value) {
            $normalizedValue = $this->normalizeRecoveryParamValue($value);
            if (is_int($key)) {
                $normalized[$key] = $normalizedValue;
                continue;
            }
            $name = (string) $key;
            if ($name !== '' && $name[0] !== ':') {
                $name = ':' . $name;
            }
            $normalized[$name] = $normalizedValue;
        }

        if (!preg_match('/:[a-zA-Z_][a-zA-Z0-9_]*/', $sql)) {
            return [$sql, array_values($normalized)];
        }

        $ordered = [];
        $convertedSql = preg_replace_callback(
            '/:([a-zA-Z_][a-zA-Z0-9_]*)/',
            function ($matches) use ($normalized, &$ordered) {
                $name = ':' . $matches[1];
                if (array_key_exists($name, $normalized)) {
                    $ordered[] = $normalized[$name];
                } else {
                    $ordered[] = null;
                }
                return '?';
            },
            $sql
        );

        return [(string) $convertedSql, $ordered];
    }

    /**
     * @param mixed $value
     * @return mixed
     */
    private function normalizeRecoveryParamValue($value) {
        if ($value === null || is_bool($value) || is_int($value) || is_float($value) || is_string($value)) {
            return $value;
        }
        if (is_array($value)) {
            return $value;
        }
        if (is_object($value) && method_exists($value, '__toString')) {
            return (string) $value;
        }

        return null;
    }

    /**
     * @param float $microtime
     */
    private function formatRecoveryTimestamp($microtime) {
        $seconds = (int) floor($microtime);
        $fraction = (int) round(($microtime - $seconds) * 1000000);
        if ($fraction >= 1000000) {
            $seconds++;
            $fraction = 0;
        }

        return date('Y-m-d\TH:i:s', $seconds) . sprintf('.%06d', $fraction);
    }

    private function resolveRecoveryDatabaseName() {
        if (defined('PDO_DATABASE_CONFIG_DB_NAME')) {
            return (string) PDO_DATABASE_CONFIG_DB_NAME;
        }

        $dsn = (string) \app\constants\PDODatabaseConfig::DNS;
        if (preg_match('/dbname=([^;]+)/i', $dsn, $matches)) {
            return $matches[1];
        }

        return '';
    }

}