<?

/**
 * Framework Database Class
 *
 * Manages all database connection instances
 * Provides all necessary database manipulation functions
 *
 * @category   Framework
 * @package    Core
 * @author     Anne Willems <a.willems@ittica.nl>
 * @author     Mark IJmker <m.ijmker@ittica.nl>
 * @copyright  Ittica Media <http:/www.itticamedia.nl>
 */

class Database{

    public static $backtrace = [];

    /**
     * array with all instances of the class Database that are in use
     *
     * @var array
     */
    protected static $oInstances;

    public $mode = 'default'; //default|generic,  generic = NOT a fwc15 db

    public $data;

    /**
     * the name of the current active Database connection
     *
     * @var string
     */
    public static $sSelected;

    /**
     * PDO class object
     *
     * @var PDO
     */
    protected $oConnection;

    /**
     * is Database class object currently in transaction mode?
     *
     * @var bool
     */
    protected $inTransaction;

    /**
     * [$_query description]
     *
     * @var [type]
     */
    protected $query;

    public static $queryCounter = 0;

    public static $log = false;
    public static $logTime;
    public static $logList = [];

    /**
     * bool statement if the query has errors
     *
     * @var bool
     */
    protected $bError = false;

    /**
     * count for the returned/affected record(s)
     *
     * @var int
     */
    public $iCount;
    public $_sTablePrefix;

    /**
     * if Database Class is in debug mode.
     * If in debug mode Database::query() will run debugDumpParams(); for the current query
     *
     * @var bool
     */
    protected $_bDebug = false;


    public function __construct(string $sDatabase) {

        try {

            $aDb = DATABASES[$sDatabase];

            self::$sSelected = $sDatabase;

            if (isset($aDb['mode'])) {
                $this->mode = $aDb['mode'];
            }

            $this->data = $aDb;

            $aDb['driver'] = isset($aDb['driver'])
                ? $aDb['driver']
                : 'mysql';

            $this->_sTablePrefix = isset($aDb['prefix'])
                ? $aDb['prefix']
                : '';

            $aOptions = array(
                PDO::ATTR_EMULATE_PREPARES => true,
                PDO::ATTR_PERSISTENT, true,
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            );

            $aDsn = [
                'host' => $aDb['host'],
                'dbname' => $aDb['name']
            ];

            if (isset($aDb['port'])) {
                $aDsn['port'] = $aDb['port'];
            }

            if (isset($aDb['charset'])) {
                $aDsn['charset'] = $aDb['charset'];

                $aOptions[PDO::MYSQL_ATTR_INIT_COMMAND] = "SET NAMES '".$aDb['charset']."'";
                if (isset($aDb['collation'])) {
                    $aOptions[PDO::MYSQL_ATTR_INIT_COMMAND] .= " COLLATE '".$aDb['collation']."'";
                }
            }

            $aDsn = array_map(function($sKey, $sVal) {
                return $sKey."=".$sVal;
            }, array_keys($aDsn), $aDsn);

            $this->oConnection = new PDO(
                $aDb['driver'].':'.implode(';', $aDsn),
                $aDb['user'],
                $aDb['pass'],
                $aOptions
            );

        } catch (PDOException $e) {
            die("Database connection failed");
        }
    }


    /**
     * Prepares and Excutes an MYSQL query with the given $sSql string and $aParams key value list
     * if STAGE = develop and debug mode true, will dump the debug params using debugDumpParams();
     * _bDebug can be set using: Database::debug( boolean ) before calling this function.
     *
     * With the returning Database object you can chain fetch() or fetchAll() to retrieve all MYSQL data.
     * returning object->_iCount = count of all affected records
     * returning object->_bError = boolean if the query had errors
     * returning object->_query = the current query object
     *
     * @param   string  $sSql     MYSQL statement to execute
     * @param   array   $aParams  array with all values for the MYSQL query
     *
     * @return  Database          Database object with the query
     */
    public static function query(string $sSql, $aParams = array(), $dumpQuery = false): Database {
        self::$backtrace = debug_backtrace();
        $db = self::getInstance();

        $dumpParams = $aParams;

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

        try{
            if($db->query = $db->oConnection->prepare($sSql)){
                $r = count($aParams);
                for ($i = 1; $i <= $r; ++$i) {
                    $db->query->bindValue($i, array_shift($aParams));
                }

                if($dumpQuery) {
                    d($sSql);
                    d($dumpParams);
                    dd(self::getDumpParams());
                }

                if(!$db->query->execute()) {
                    $db->bError = true;
                }

                if (
                    Devtools::enabled() 
                    || (
                        defined('LOG') &&
                        isset(LOG['QUERIES']) &&
                        LOG['QUERIES']
                    )
                ) {

                    $queryDepth = 5;
                    if(defined('LOG') && isset(LOG['QUERIES_TRACE_DEPTH'])){
                        $queryDepth = LOG['QUERIES_TRACE_DEPTH'];
                    }

                    $i = 0;
                    $trace = [];
                    while ($i < count(self::$backtrace) && $i < $queryDepth) {
                        if(!isset(self::$backtrace[$i]['file'])){
                            $trace[$i] = [
                                self::$backtrace[$i]['class'],
                                self::$backtrace[$i]['function']
                            ];
                        } else{
                            $trace[$i] = [
                                self::$backtrace[$i]['file'],
                                self::$backtrace[$i]['line']
                            ];
                        }
                        ++$i;
                    }

                    self::$logList[] = [
                        microtime(true)-self::$logTime,
                        self::getDumpParams(),
                        $trace
                    ];
                }

                if ($db->_bDebug) {
                    echo self::getDumpParams();
                }
            } else {
                $db->bError = true;
            }

        } catch (Exception $e) {

            trigger_error(
                $e->getMessage().'<hr />'.self::getDumpParams(),
                E_USER_ERROR);

            $db->bError = true;

            if ($db->inTransaction) {
                $db->oConnection->rollBack();

                Log::add([
                    'subject' => 'database.rollback',
                    'text' => 'A rollback has occured.',
                    'data' => self::getDumpParams()
                ]);

                $db->inTransaction = false;
            }
        }

        ++self::$queryCounter;

        return $db;
    }


    /**
     * Updates or inserts data.
     * Checks by using function: Database::rowCount($table, $wheres) if a record exists with the given $wheres statements.
     * If a table record exists it will update that record with $fields data.
     * If no table record was found a new record will be inserted with $fields data.
     *
     * @param   string   $table   table name to update or insert at
     * @param   array    $fields  key value array of all table columns and values
     * @param   array    $wheres  key value array of all columns for the where statement
     *
     * @return  bool               true if update or insert query was executed succesfull, false otherwise
     */
    public static function upsert(string $table, array $fields, ...$args)
    {
        $inTransaction = self::inTransaction();

        //if we have translation data -> start a transaction
        if (!$inTransaction){
            self::begin();
        }

        $db = self::getInstance();

        $wheres = [];

        if (count($args) > 0 &&
            is_array($args[0])) {
            $wheres = array_shift($args);
        }

        $returnInsertedId = false;
        if (count($args) > 0 &&
            is_bool($args[0])) {
            $returnInsertedId = array_shift($args);
        }

        //compare insert columns with where columns and use where columns in inserting columns for the mathing columns in the compare
        $aColumnsInsert = array_merge($fields, $wheres);

        $updateFields = $db->mode == 'default'
            ? ", updated_at = NOW(3)"
            : "";

        //preform the "normal" data insert/update query
        // if ($table == 'products') {
        //     d("
        //         INSERT INTO ".$table."
        //             (`".implode('`, `', array_keys($aColumnsInsert))."`)
        //         VALUES
        //             (".implode(', ', array_fill(0, count($aColumnsInsert), '?')).")
        //         ".(count($fields) > 0
        //             ? "ON DUPLICATE KEY UPDATE ".
        //                 implode(', ', array_map(function($sKey) {
        //                     return "`".$sKey."` = ?";
        //                 }, array_keys($fields)))
        //             : "").$updateFields,
        //         array_merge(
        //             array_values($aColumnsInsert),
        //             array_values($fields)));

        //     }
        $query = self::query("
            INSERT INTO ".$table."
                (`".implode('`, `', array_keys($aColumnsInsert))."`)
            VALUES
                (".implode(', ', array_fill(0, count($aColumnsInsert), '?')).")
            ".(count($fields) > 0
                ? "ON DUPLICATE KEY UPDATE ".
                    implode(', ', array_map(function($sKey) {
                        return "`".$sKey."` = ?";
                    }, array_keys($fields)))
                : "").$updateFields,
            array_merge(
                array_values($aColumnsInsert),
                array_values($fields)));

        //if we have translation data -> commit the transaction
        $bCommitState = true;
        if (!$inTransaction){
            $bCommitState = self::commit();
        }

        //return false if there where errors
        if ($query->error() || !$bCommitState)
            return false;

        $rowCount = $query->query->rowCount();

        if ($returnInsertedId) {

            if ($rowCount != 0) {

                return self::getLastAffected($table);

            }

            if (count($wheres) > 0) {

                return self::query("
                    SELECT id
                    FROM ".$table."
                    WHERE ".implode(' AND ', array_map(function($key, $val) {

                        return !is_null($val)
                            ? $key." = ?"
                            : $key." IS NULL";

                    }, array_keys($wheres), array_values($wheres))), array_merge(
                    array_filter($wheres, function($v, $k) {
                        return !is_null($v);

                    }, ARRAY_FILTER_USE_BOTH)
                ))->fetch('column');
            } else {
                return false;
            }
        }

        return $rowCount == 0
            ? 'equal'
            : ($rowCount == 1
                ? 'insert'
                : 'update');

    }


    public static function insert(string $table, array $fields, $returnInsertedId = false)
    {
        $inTransaction = self::inTransaction();

        //if we have translation data -> start a transaction
        if (!$inTransaction){
            self::begin();
        }

        $db = self::getInstance();

        $list = [];
        foreach ($fields as $k => $v) {
            if (!is_null($v)) {
                $list[$k] = $v;
            }
        }
        $fields = $list;

        $query = self::query("
            INSERT INTO ".$table."
            (`".implode('`, `', array_keys($fields))."`)
            VALUES (".implode(", ", str_split(str_repeat("?", count(array_values($fields))))).")",
            array_map(function($value) {
                return is_null($value)
                    ? 'NULL'
                    : $value;

            }, array_values($fields)));

        //if we have translation data -> commit the transaction
        $bCommitState = true;
        if (!$inTransaction){
            $bCommitState = self::commit();
        }

        //return false if there where errors
        if ($query->error() || !$bCommitState)
            return false;

        return $returnInsertedId
            ? self::getLastInserted($table)
            : true;
    }


    public static function update(string $table, array $fields, array $wheres = []): bool
    {

        $inTransaction = self::inTransaction();

        //if we have translation data -> start a transaction
        if (!$inTransaction){
            self::begin();
        }

        $db = self::getInstance();

        if ($db->mode == 'default') {
            $fields['updated_at'] = date("Y-m-d H:i:s");
        }

        //preform the "normal" data insert/update query
        $query = self::query("
            UPDATE ".$table."
            SET ".implode(', ', array_map(function($key) {
                return $key." = ?";
            }, array_keys($fields)))."
            WHERE ".implode(' AND ', array_map(function($key, $val) {

                return !is_null($val)
                    ? $key." = ?"
                    : $key." IS NULL";

            }, array_keys($wheres), array_values($wheres))), array_merge(
                array_values($fields),
                array_filter($wheres, function($v, $k) {
                    return !is_null($v);

                }, ARRAY_FILTER_USE_BOTH)
            )
        );

        //if we have translation data -> commit the transaction
        $bCommitState = true;
        if (!$inTransaction){
            $bCommitState = self::commit();
        }

        //return false if there where errors
        if ($query->error() || !$bCommitState)
            return false;

        return true;
    }


    /**
     * Deletes records for the given where statements.
     *
     * @param   string   $table   table name where to delete record(s)
     * @param   array    $wheres  key value array with the where statement(s)
     *
     * @return  bool               true if query executed successfull, false otherwise
     */
    public static function delete(string $table, $mWheres) {

        if (is_array($mWheres)){
            $mWheres = implode(' AND ', array_map(function($sKey, $sVal) {

                if (is_null($sVal)) {
                    return "`{$sKey}` IS NULL";
                }

                return "`{$sKey}` = '{$sVal}'";

            }, array_keys($mWheres), $mWheres));
        }

        return self::query("
            DELETE FROM ".$table."
            WHERE ".$mWheres);
    }


    public static function countRows(string $table, array $wheres): int {
        $query = self::query("
            SELECT COUNT(*) as `count`
            FROM ".$table."
            WHERE ".implode(' AND ',  array_map(function($sKey, $sVal) {
                return "`{$sKey}` = '?'";
            }, array_keys($wheres), $wheres), array_values($wheres)));

        if ($query->count() == 0){
            trigger_error('Where statement fot count rows has no matches for:<br>'.self::getDumpParams(),
            E_USER_ERROR);
        }

        return $query->fetch('column');
    }


    public static function getLastAffected(string $table): int {

        $rowId = self::query("
            SELECT row_id
            FROM _mutations
            WHERE
                tablename = '".$table."'
            ORDER BY id DESC
            LIMIT 1")->fetch("column");

        if(empty($rowId)){

            $rowId = self::query("
                SELECT id
                FROM ".$table."
                ORDER BY GREATEST(
                    (
                        CASE WHEN updated_at IS NULL
                            THEN '0000-00-00 00:00:00'
                            ELSE updated_at
                        END
                    ),
                    created_at
                ) DESC
                LIMIT 1
            ")->fetch('column');
        }

        return $rowId;
    }


    public static function getLastInserted(string $table): int {

        $ai = self::query("
            SELECT AUTO_INCREMENT
            FROM INFORMATION_SCHEMA.TABLES
            WHERE
                TABLE_NAME = ? AND
                TABLE_SCHEMA = ?", [$table, DATABASES[self::$sSelected]['name']])->fetch('column');

        if ($ai == 0){
            $ai = 1;
        }

        return $ai - 1;
    }


    public static function begin(string $name = '', object $oFunction = null, $aParams = []) {

        $db = self::getInstance();

        if ($db->inTransaction) {
            return false;
        }

        $db->oConnection->beginTransaction();
        $db->inTransaction = true;

        if ($db->mode == 'default' &&
            App::getName() != 'frontend') {
            self::query("
                INSERT INTO _transactions
                (user_id, name)
                VALUES (".(isset($_SESSION['account_id']) ? $_SESSION['account_id'] : (isset($_SESSION['user_id']) ? $_SESSION['user_id'] : '0')).", '".$name."')");
        }

        if ($oFunction != null) {

            $mReturn = call_user_func_array($oFunction, $aParams);

            $db->oConnection->commit();
            $db->inTransaction = false;

            return $mReturn;
        }

        return true;
    }


    public static function commit(): bool {

        $db = self::getInstance();

        if (!$db->inTransaction)
            return false;

        $db->oConnection->commit();
        $db->inTransaction = false;

        return true;
    }


    public static function inTransaction(): bool {
        return self::getInstance()->inTransaction
            ? true
            : false;
    }


    public static function log($state = true) {
        self::$logTime = microtime(true);
    }


    public static function debug(bool $bState = true): void {
        self::getInstance()->_bDebug = $bState == 'debug'
            ? true
            : false;
    }


    public static function setConnection(string $sDatabase, object $oFunction = null) {

        if (!array_key_exists($sDatabase, DATABASES))
            return false;

        $sCurrentSelectedDababase = self::$sSelected;
        self::$sSelected = $sDatabase;

        if ($oFunction != null) {

            $mReturn = call_user_func($oFunction, null);
            self::$sSelected = $sCurrentSelectedDababase;

            return $mReturn;
        }

        return true;
    }


    public function fetch(string $sFetchType = 'assoc') {
        return $this->query->fetch($this->getFetchType($sFetchType));
    }


    public function fetchAll(string $sFetchType = 'assoc') {
        return $this->query->fetchAll($this->getFetchType($sFetchType));
    }


    public function count(): bool {
        return $this->query->rowCount();
    }


    public function error(): bool {
        return $this->bError;
    }


    private static function getFetchType(string $sFetchType): int {

        return $sFetchType == 'object'
            ? PDO::FETCH_OBJ
            : ($sFetchType == 'column'
                ? PDO::FETCH_COLUMN
                : ($sFetchType == 'key_pair'
                    ? PDO::FETCH_KEY_PAIR
                    : PDO::FETCH_ASSOC));
    }


    private static function getDumpParams(): string {
        $db = self::getInstance();
        ob_start();
        $db->query->debugDumpParams();
        $txt = ob_get_contents();

        preg_match_all('/SQL\:\s\[\d+\](.*)\s(Params|Sent)/isU', $txt, $matches);
        if (count($matches[0]) > 0) {
            $txt = end($matches[1]);
        }

        ob_end_clean();
        return $txt;
    }


    protected static function getInstance(): Database {

        if (!isset(self::$sSelected)){
            self::$sSelected = DATABASE_DEFAULT;
        }

        if (!isset(self::$oInstances[self::$sSelected]))
            self::$oInstances[self::$sSelected] = new Database(self::$sSelected);

        return self::$oInstances[self::$sSelected];
    }


    public static function getTableColumns(string $table): array
    {

        return db::query("
            SELECT column_name
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE table_name = ? AND table_schema = ?
            ORDER BY ordinal_position;
        ", [
            $table,
            DATABASES[self::$sSelected]['name'],
        ])->fetchAll('column');

    }


    public static function blackmagicEnabled(){

        if(ENVIRONMENT != 'production'){
            return;
        }

        db::query("
            SELECT trigger_schema, trigger_name, action_statement
            FROM information_schema.triggers 
            WHERE trigger_schema = ? AND trigger_name LIKE 'bm_%'
        ")->fetchAll('assoc');
    }


    /**
     * Checks each table structure (hashed column names) against previous hash, if changed, recreate the triggers for those tables
     * TODO: implement chunks for first run. Triggers for all tables have a too large packet_size to run the query.
     */
    public static function updateBlackMagicTriggers()
    {
        $instance = self::getInstance();

        if($instance->mode != 'default'){
            throw new Exception('Only allowed in default mode. Switch connection before running this function');
        }

        $currentStructure = [];
        if(file_exists(CACHE_DIR.'/blackmagic.cache')){

            // Get current structure from cache. If JSON is invalid,
            // set structure to empty to regenerate from scratch.
            try{
                $currentStructure = json_decode(
                    file_get_contents(CACHE_DIR.'/blackmagic.cache'),
                    true,
                    512,
                    JSON_THROW_ON_ERROR
                );
            } catch(Exception $e){
                $currentStructure = [];
            }

        }

        // Get all tables to apply blackmagic
        $tables = db::query("
            SELECT table_name
            FROM information_schema.tables 
            WHERE table_schema = ? 
            AND table_type = 'BASE TABLE' 
            AND TABLE_NAME NOT IN (SELECT name FROM _tables_excluded)
        ", [
            DATABASES[self::$sSelected]['name']
        ])->fetchAll('column');

        // Hard force ignore tables starting with an underscore. In case _tables_excluded is empty
        // ! Very important that _mutations and _transactions are ignored to avoid recursion which WILL crash the database!
        $tables = array_filter($tables, function($table){
            return substr($table, 0, 1) != '_';
        });

        $baseTrigger = '
            CREATE TRIGGER bm%s_%s 
                AFTER %s
                ON `%s` FOR EACH ROW
            BEGIN
                %s
            END
        ';

        // TODO: Fix transaction id. Don't just get last from table...
        $triggerTypes = [

            1 => [
                'operation' => 'insert',
                'mutation' => '
                    INSERT INTO _mutations (`trans_id`, `type`, `tablename`, `columnname`, `row_id`, `old_value`, `new_value`) VALUES 
                    ((SELECT id FROM _transactions ORDER BY id DESC LIMIT 1), [type], "[table]", "[column]", NEW.id, NULL, [new_value]);
                ',
            ],

            2 => [
                'operation' => 'update',
                'mutation' => '
                    IF OLD.[column] != NEW.[column] THEN
                        INSERT INTO _mutations (`trans_id`, `type`, `tablename`, `columnname`, `row_id`, `old_value`, `new_value`) VALUES 
                        ((SELECT id FROM _transactions ORDER BY id DESC LIMIT 1), [type], "[table]", "[column]", OLD.id, [old_value], [new_value]);
                    END IF;
                ',
            ],

            3 => [
                'operation' => 'delete',
                'mutation' => '
                    INSERT INTO _mutations (`trans_id`, `type`, `tablename`, `columnname`, `row_id`, `old_value`, `new_value`) VALUES 
                    ((SELECT id FROM _transactions ORDER BY id DESC LIMIT 1), [type], "[table]", "[column]", OLD.id, [old_value], NULL);
                ',
            ]

        ];

        foreach($tables as $table){

            $columns = db::getTableColumns($table);

            $newHash = md5($table.implode('', $columns));

            if(isset($currentStructure[$table]) && $currentStructure[$table]['hash'] == $newHash){
                continue;
            }

            foreach($triggerTypes as $key => $type){
                $queries = [];

                foreach($columns as $column){

                    if($column == 'secret') continue;
                    
                    $query = $type['mutation'];
                    $query = str_replace('[type]', $key, $query);
                    $query = str_replace('[table]', $table, $query);
                    $query = str_replace('[column]', $column, $query);
                    $query = str_replace('[old_value]', "OLD.".$column, $query);
                    $query = str_replace('[new_value]', "NEW.".$column, $query);

                    $queries[] = $query;

                }

                $trigger = sprintf(
                    $baseTrigger,
                    $type['operation'][0], // first letter of operation. So: i, d or u
                    $table,
                    strtoupper($type['operation']),
                    $table,
                    implode('', $queries)
                );

                $trigger = preg_replace([
                    "/\t/",
                    "/\n/",
                    '/\s+/',
                ], [
                    '',
                    '',
                    ' ',
                ], $trigger);

                // First drop existing trigger, then insert new one.
                db::query('DROP TRIGGER IF EXISTS bm'.$type['operation'][0].'_'.$table);
                $db = db::query($trigger);

                // When executed without errors, update table
                if(!$db->error()){
                    $currentStructure[$table]['hash'] = $newHash;
                    $currentStructure[$table]['updated_at'] = date("Y-m-d H:i:s");
                } else{
                    echo "Failed $table";
                    // TODO: error handling if trigger execution failed
                }
            }

        }

        file_put_contents(CACHE_DIR.'/blackmagic.cache', json_encode($currentStructure));

        return $currentStructure;

    }


}

abstract class db extends Database {}