<?php

/**
 * This file is part of LEPTON Core, released under the GNU GPL
 * Please see LICENSE and COPYING files in your package for details, specially for terms and warranties.
 * 
 * NOTICE:LEPTON CMS Package has several different licenses.
 * Please see the individual license in the header of each single file or info.php of modules and templates.
 *
 * @author          LEPTON Project
 * @copyright       2010-2021 LEPTON Project
 * @link            https://lepton-cms.org
 * @license         http://www.gnu.org/licenses/gpl.html
 * @license_terms   please see LICENSE and COPYING files in your package
 *
 */

/**
 *  The LEPTON database connector.
 *
 */
class LEPTON_database
{
    
    use LEPTON_secure_database;
    // use lib_comp;
    
    const DESCRIBE_RAW           = 0;
    const DESCRIBE_ASSOC         = 1;
    const DESCRIBE_ONLY_NAMES    = 2;
    
    /**
     *  Singleton The reference to *Singleton* instance of this class
     *  @var    object  $instance
     */
    private static $instance;
    
    private $bXrunInstalled = false;
    
    /**
     *  Private var for the error-messages.
     *  @var    string  $error;
     *  @access private
     */
    private $error = '';
    
    /**
     *  The internal db handle.
     *  @var    object
     *  @access private
     */
    private $db_handle = false;

    /**
     *  Private var to handle the session check.
     *  @var    boolean $override_session_check
     */
    private $override_session_check = false;
    
    /**
     *  Public var to handle displaying the errors during the processes.
     *  (Makes use of LEPTON_tools::display)
     *  @var        boolean $bHaltOnError
     *  @seeAlso    LEPTON_tools::display
     */
    public $bHaltOnError = true;
    
    public $bForceDieOnError = true;
    
    /**
     *  Return the (singelton) instance of this class.
     *
     *  @param  array    $settings   Optional params - see "connect" for details
     *
     *  @see    connect
     */
    public static function getInstance( &$settings=array() )
    {
        if (null === static::$instance) {
            static::$instance = new static();
            
            static::$instance->bXrunInstalled = class_exists("lib_comp", true);
            
            static::$instance->connect($settings);
        }
        
        return static::$instance;
    }
    
    /**
     *  Constructor of the class database
     *
     *  @param    array    Assoc. array with the connection-settings. Pass by reference!
     *
     *  @seealso        Method "connect" for details.        
     */
    public function __construct(&$settings = array())
    {
        $this->connect($settings);
    }
    
    /**
     *  Destructor of the class database
     */
    public function __destruct()
    {

    }
    
    /**
     *  Set error
     *  @param string
     */
    protected function set_error($error = '')
    {
        $this->error = $error;
    }
    
    /**
     *  Return the last error
     *  @return string
     */
    public function get_error()
    {
        return $this->error;
    }
    
    /**
     *  Check if there occured any error
     *  @return boolean
     */
    public function is_error()
    {
        return (!empty($this->error)) ? true : false;
    }
        
    /**
     *  Get the MySQL DB handle
     * 
     *  @return resource or boolean false if no connection is established
     */
    public function get_db_handle()
    {
        return $this->db_handle;
    }

    /**
     *  Get the internal DB key
     * 
     *  @return string
     */    
    public function get_db_key()
    {
        return self::$db_key;
    }  
    
    /**
     *  Establish the connection to the desired database defined in /config.php.
     *
     *  This function does not connect multiple times, if the connection is
     *  already established the existing database handle will be used.
     *
     *  @param    array    Assoc. array within optional settings. Pass by reference!
     *  @return nothing
     *
     *  @notice Param 'settings' is an assoc. array with the connection-settins, e.g.:
     *            $settings = array(
     *                'host'    => "example.tld",
     *                'user'    => "example_user_string",
     *                'pass'    => "example_user_password",
     *                'name'    => "example_database_name",
     *                'port'    =>    "1003",
     *                'key'    =>    "a unique key",         // optional
     *              'cipher'    => "aes-256-cbc",       // optional
     *              'iv'        => "12_%#0123773345_",  // MUST be match to the given 'cipher'! 
     *              'ivlen'     => 16,                  // MUST be set if 'iv' has a different lenght than 16!     
     *                'charset' => "utf8"
     *            );
     *
     *          KEEP in mind, that the optional keys are set in the lepton.ini.php!
     *          So if you try to use the secure_* methods on a new connection via the $settings there 
     *          can be cause some unintended results if theese keys are missing or missformated!
     *
     *            To set up the connection to another charset as 'utf8' you can
     *            also define another one inside the config.php e.g.
     *                define('DB_CHARSET', 'utf8');
     *
     */
    final function connect(&$settings = array())
    {
        
        if(!defined("DB_USER")) {
            $ini_file_name = LEPTON_PATH."/config/lepton.ini.php";
            
            if( true == file_exists( $ini_file_name ) ) {
                $config = parse_ini_string(";".file_get_contents($ini_file_name), true );
                if(!isset($settings['host'])) $settings['host'] = $config['database']['host'];
                if(!isset($settings['user'])) $settings['user'] = $config['database']['user'];
                if(!isset($settings['pass'])) $settings['pass'] = $config['database']['pass'];
                if(!isset($settings['name'])) $settings['name'] = $config['database']['name'];
                if(!isset($settings['port'])) $settings['port'] = $config['database']['port'];
                
                if(isset($config['database']['charset']))
                {
                    if(!isset($settings['charset'])) $settings['charset'] = $config['database']['charset'];
                }
                
                if(!defined("TABLE_PREFIX")) define("TABLE_PREFIX", $config['database']['prefix']);
            
                // [4]  For the new secure methods in L* IV
                if( isset($config['database']['key']) )
                {
                    self::$db_key = $config['database']['key'];
                }
                
                if( isset($config['database']['options']) )
                {
                    self::$default_openssl_options = $config['database']['options'];
                }
                
                if( isset($config['database']['cipher']) )
                {
                    if( "0" === $config['database']['cipher'] )
                    {
                        $config['database']['cipher'] = 0;
                    }
                    self::$default_openssl_method = $config['database']['cipher'];
                }
                
                if( isset($config['database']['iv']) )
                {
                    self::$default_openssl_iv = $config['database']['iv'];
                }
                
                if( isset($config['database']['ivlen']) )
                {
                    self::$default_openssl_ivlen = $config['database']['ivlen'];
                }
                
                /**
                 *  Any System-constants?
                 */
                if(isset($config['system_const']))
                {
                    foreach($config['system_const'] as $key => $value)
                    {
                        if(!defined($key))
                        {
                            define( $key, $value );
                        }
                    }
                }
                
            } else {
                // Problem: no lepton.ini.php 
                exit('<p><b>Sorry, but this installation seems to be damaged! Please contact your webmaster!</b></p>');
            }
        }
        
        // [4.1]  For the new secure methods in L* IV
        if(isset($settings['key']))
        {
            self::$db_key = $settings['key'];
        }
        if(isset($settings['options']))
        {
            self::$default_openssl_options = $settings['options'];
        }
        if(isset($settings['cipher']))
        {
            self::$default_openssl_method = $settings['cipher'];
        }
        if(isset($settings['iv']))
        {
            self::$default_openssl_iv = $settings['iv'];
        }
        if(isset($settings['ivlen']))
        {
            self::$default_openssl_ivlen = $settings['ivlen'];
        }
        
        $setup = array(
            'host' => (array_key_exists('host', $settings) ? $settings['host'] : DB_HOST),
            'user' => (array_key_exists('user', $settings) ? $settings['user'] : DB_USERNAME),
            'pass' => (array_key_exists('pass', $settings) ? $settings['pass'] : DB_PASSWORD),
            'name' => (array_key_exists('name', $settings) ? $settings['name'] : DB_NAME),
            'port' => (array_key_exists('port', $settings) ? $settings['port'] : DB_PORT),
            'charset' => (array_key_exists('charset', $settings) ? $settings['charset'] : (defined('DB_CHARSET') ? DB_CHARSET : "utf8"))
        );
        
        // use DB_PORT only if it differ from the standard port 3306
        if ($setup['port'] !== '3306')
            $setup['host'] .= ';port=' . $setup['port'];
       
        $dsn = "mysql:dbname=".$setup['name'].";host=".$setup['host'].";charset=".$setup['charset'];
        
        try {
        
            $this->db_handle = new PDO(
                $dsn,
                $setup['user'],
                $setup['pass'],
                array(
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_PERSISTENT => true,
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
                )
            );
            
            /**
             *    Try to set the charset.
             */
            $this->simple_query("SET NAMES '".$setup['charset']."'");
            
            /**
             *    Since 4.0 there could be also a setting for the MYSQL-Mode in the lepton.ini.
             *    
             *    @see https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-important
             *
             */
            if(isset($config)) { 
                if(isset( $config['database']['mode'] ))
                {
                    $this->simple_query("SET GLOBAL sql_mode='".$config['database']['mode']."'"); 
                }
                unset($config); 
            }

            $this->simple_query("USE `".$setup['name']."`");

        } catch (PDOException $oError) {
            $this->set_error( $oError->getMessage() );
            echo (LEPTON_tools::display('Connection failed: ' . $oError->getMessage(),'pre','ui message'));
            echo (LEPTON_tools::display('Runtime Code: ' . $oError->getCode(),'pre','ui message'));
        }
        
    }
          
    /**
     *  Exec a mysql-query without returning a result.
     *  A typical use is e.g. "DROP TABLE IF EXIST" or "SET NAMES ..."
     *
     *  @param  string  Any (MySQL-) Query
     *  @param  array   Optional array within the values if place-holders are used.
     *
     *  @return bool    True if success, otherwise false.
     *
     *  @code{.php} 
     *      LEPTON_database::getInstance()->simple_query("DROP TABLE `xxxxx` IF EXIST");
     *      // or
     *      LEPTON_database::getInstance()->simple_query(
     *          "INSERT into `TABLE_xxxxx` (`name`,`state`) VALUES('?','?');",
     *              array(
     *                  ['example','none'],
     *                  ['master', 'confirmed']
     *              )
     *      );
     *      // or
     *      LEPTON_database::getInstance()->simple_query(
     *          "INSERT into `TABLE_xxxxx` (`name`,`state`) VALUES( :name , :sta );",
     *              array(
     *                  "name"  => "Aldus",
     *                  "sta"   => "internal"
     *              )
     *      );
     *  @endcode
     */
    public function simple_query( $sMySQL_Query="", $aParams=array() )
    {
        $this->error = "";
        try
        {
            $oStatement=$this->db_handle->prepare( $sMySQL_Query );
            if (count($aParams) > 0)
            {
                if( (isset($aParams[0])) && (is_array($aParams[0])) )
                {
                    foreach($aParams as &$temp_params)
                    {
                        $oStatement->execute( $temp_params );
                    }
                } else {
                    $oStatement->execute( $aParams );
                }
            } else {
                $oStatement->execute();
            }
            return true;
        } catch( PDOException $error) {
            $this->error = $error->getMessage()."\n<p>Query: ".$sMySQL_Query."\n</p>\n";
            $this->HandleDisplayError("301");
            return false;
        }
    }
    
    /**
     *    Execute a SQL query and return the first row of the result array
     *  
     *    @param    string  Any SQL-Query or statement
     *
     *    @return   mixed   Value of the table-field or NULL for error
     *
     *    @notice Changed in L* 4.5.0! There may be side-effects!
     */
    public function get_one($SQL)
    {
        $this->error = "";
        
        try
        {
            $oStatement=$this->db_handle->prepare( $SQL );
            $oStatement->execute();
            
            $data = $oStatement->fetch( PDO::FETCH_ASSOC );
            if(false === $data)
            {
                // LEPTON_tools::display( $oStatement->errorInfo(), "pre", "ui message olive" );
                return NULL;
            } else {
                return array_shift($data);
            }
        }
        catch( Exception $error ) 
        {
            $this->error = $error->getMessage();
            $this->HandleDisplayError("GO 101");
            return NULL;
        }
    }
   
    
    /**
     *    Returns a linear array within the tablenames of the current database
     *
     *    @param    string  Optional string to 'strip' chars from the tablenames, e.g. the prefix.
     *    @return   mixed   An array within the tablenames of the current database, or FALSE (bool).
     *
     */
    public function list_tables( $strip = "" )
    {
        $this->error = "";
        try
        {
            $oStatement=$this->db_handle->prepare( "SHOW tables" );
            $oStatement->execute();
            
            $data = $oStatement->fetchAll();
        }
        catch (Exception $error) 
        {
            $this->error = $error->getMessage();
            $this->HandleDisplayError("1");
            return false;
        }
        
        $ret_value = array();
        foreach($data as &$ref)
        {
            $ret_value[] = array_shift( $ref );
        }
        if ($strip != "")
        {
            foreach ($ret_value as &$ref)
                $ref = str_replace($strip, "", $ref);
        }
        return $ret_value;
    }
    
    /**
     *  Placed for all fields from a given table(-name) an assocc. array
     *  inside a given storage-array.
     *
     *  @param  string  The (valid) tablename.
     *  @param  array   An array to store the results. Pass by reference!
     *
     *  @return bool    True if success, otherwise false.
     *
     */
    public function describe_table($sTableName, &$aStorage = array(), $iForm = self::DESCRIBE_RAW )
    {
        $this->error = "";
        try
        {
            $oStatement=$this->db_handle->prepare( "DESCRIBE `" . $sTableName . "`" );
            $oStatement->execute();
            
            $aStorage = $oStatement->fetchAll();
            
            switch( intval($iForm) )
            {
                case (self::DESCRIBE_ASSOC):
                    $aTemp = [];
                    foreach($aStorage as &$values)
                    {
                        $aTemp[ $values["Field"] ] = $values;
                    }
                    $aStorage = $aTemp;
                    
                    break;
                    
                case (self::DESCRIBE_ONLY_NAMES):
                    $aTemp = [];
                    foreach($aStorage as &$values)
                    {
                        $aTemp[] = $values["Field"];
                    }
                    $aStorage = $aTemp;
                    break;
            }
            
            return true;
        }
        catch (Exception $error) 
        {
            $this->error = $error->getMessage();
            $this->HandleDisplayError("2");
            return false;
        }
    }

    /**
     *  Public "shortcut" for executeing a single mySql-query without passing values.
     *
     *
     *  @param    string  A valid mySQL query.
     *  @param    bool    Fetch the result - default is false.
     *  @param    array   A storage array for the fetched results. Pass by reference!
     *  @param    bool    Try to get all entries. Default is true.
     *  @return   bool    False if fails, otherwise true.
     *
     *  @example
     *      $results_array = array();       
     *      $this->db_handle->execute_query( 
     *          "SELECT * from ".TABLE_PREFIX."pages WHERE page_id = '".$page_id."' ",
     *          true, 
     *          $results_array, 
     *          false 
     *      );
     *        
     *
     */
    public function execute_query( $aQuery="", $bFetch=false, &$aStorage=array(), $bFetchAll=true ) {
        $this->error = "";
        try{
            $oStatement=$this->db_handle->prepare($aQuery);
            $oStatement->execute();
        
            if ($oStatement->rowCount() > 0) {
                if ( true === $bFetch ){
                    $aStorage = (true === $bFetchAll)
                        ? $oStatement->fetchAll()
                        : $oStatement->fetch()
                        ;
                }
            }
            return true;
        } catch( PDOException $error) {
            $this->error = $error->getMessage();
            $this->HandleDisplayError("10");
            return false;
        }
    }

    /**
     *  Public function to build and execute a mySQL query direct.
     *  Use this function/method for update and insert values only.
     *  As for a simple select you can use "prepare_and_execute" above.
     *
     *  @param  string  $type           A "job"-type: this time only "update" and "insert" are supported.
     *  @param  string  $table_name     A valid tablename (incl. table-prefix).
     *  @param  array   $table_values   An array within the table-field-names and values.
     *  @param  string  $condition      An optional condition for "update" - this time a simple string.
     *  @param  string  $key            An optional "no update" key field to be excluded on update while "insert_on_duplicate_key_update" - a simple string containing 1 key field.
     *
     *  @return bool    False if fails, otherwise true.
     *
     */
    public function build_and_execute( $type, $table_name, $table_values, $condition="", $key="" )
    {
        $this->error = "";
        switch( strtolower($type) )
        {
            case 'update':
                $q = "UPDATE `". $table_name ."` SET ";
                foreach($table_values as $field => $value)
                {
                    $q .= "`". $field ."`= :".$field.", ";
                }
                $q = substr($q, 0, -2) . (($condition != "") ? " WHERE " . $condition : "");
                break;
               
            case 'insert':
                $keys = array_keys($table_values);
                $q = "INSERT into `" . $table_name . "` (`";
                $q .= implode("`,`", $keys) . "`) VALUES (:";
                $q .= implode(", :", $keys) . ")";
                break;

            case 'insert_on_duplicate_key_update':
                $keys = array_keys($table_values);
                $q = "INSERT into `" . $table_name . "` (`";
                $q .= implode("`,`", $keys) . "`) VALUES (:";
                $q .= implode(", :", $keys) . ")";
                $q .= "ON DUPLICATE KEY UPDATE";
                foreach($table_values as $field => $value)
                {
                    if ($field != $key)
                    {
                        $q .= "`". $field ."`= :".$field.", ";
                    }
                }
                $q = substr($q, 0, -2);
                break;

            case 'replace':
                $keys = array_keys($table_values);
                $q = "REPLACE into `" . $table_name . "` (`";
                $q .= implode("`,`", $keys) . "`) VALUES (:";
                $q .= implode(", :", $keys) . ")";
                break;
    
            default:
                die("<build_and_execute>:: type unknown!");
                break; 
        }

        try {
        
            $oStatement=$this->db_handle->prepare($q);
            $oStatement->execute( $table_values );

        } catch( PDOException $error) {
            $this->error = $error->getMessage()."\n<p>Query: ".$q."\n</p>\n";
            $this->HandleDisplayError("12");
            return false;
        }
        
        return true;
    }
    
    /**
     *  Private function to handle the errors during the processes.
     *
     */
    private function HandleDisplayError( $sPrefix = "" )
    {
        if( true === $this->bHaltOnError)
        {    
            //  [1]
            $sMessage = LEPTON_tools::display(
                (($sPrefix!= "") ? "[".$sPrefix."] " : "").$this->error,
                "pre",
                "ui message red"
            );
            
            //  [2]
            ob_start();
                debug_print_backtrace();
                $sBugTrace = ob_get_clean();
            
            $sMessage .= LEPTON_tools::display(
                $sBugTrace,
                "pre",
                "ui message orange"
            );
            
            //  [3]
            if(true === $this->bForceDieOnError)
            {
                die($sMessage);
                
            } else {
                echo $sMessage;
            }
        }
    }

    /**
     *  Method is called when a requested method in this class does not exists.
     *
     *  see: https://www.php.net/manual/de/language.oop5.overloading.php#object.call
     *
     */
    public function __call( $sName, $aParams )
    {
        switch(strtolower($sName))
        {
            default:
                echo (LEPTON_tools::display(
                    __CLASS__." [1010] call to an unknown method '".$sName."'",
                    "pre",
                    "ui message red"
                ));
                echo (LEPTON_tools::display(
                   debug_backtrace() ,
                    "pre",
                    "ui message orange"
                ));
                die (LEPTON_tools::display(
                    $aParams,
                    "pre",
                    "ui message red"
                ));
        } 
    }
    
    /**
     *  Method is called when a requested static method in this class does not exists.
     *
     *  see: https://www.php.net/manual/de/language.oop5.overloading.php#object.call
     *
     */
    public static function __callStatic( $sName, $aParams )
    {
        switch(strtolower($sName))
        {

            default:
                echo (LEPTON_tools::display(
                    __CLASS__." [1020] call to an unknown static method '".$sName."'",
                    "pre",
                    "ui message red"
                ));

                die (LEPTON_tools::display(
                    $aParams,
                    "pre",
                    "ui message red"
                ));
        } 
    }
    /**
     *  Execute a SQL query and return a handle to queryMySQL
     *
     *  @param  str     $sSqlQuery   The query string to be executed.
     *  @return mixed   Object or NULL for error
     *
     */
    public function query( $sSqlQuery = "" )
    {
        $this->error = "";
        try{
            $oStatement = $this->db_handle->prepare( $sSqlQuery );
            $oStatement->execute();

            // LEPTON_tools::use_var_dump( true );
            // echo LEPTON_tools::display( lib_comp::$bXRunInit );

            if( ( true === $this->bXrunInstalled ) && ( true === lib_comp::$bXRunInit ) )
            {
                return new lib_comp_query( $oStatement );
            } else {
                return $oStatement;
            } 

        } catch( PDOException $error ) {
            $this->set_error( $error->getMessage() );
            $this->HandleDisplayError("xRun 101");
            return NULL;
        }
    }

    /**
     *  Add column to existing table
     *
     *  @param string  $table   The table name o be extended.
     *  @param string  $column	Name
     *  @param string  $desc	column type and deafult	 
     *
     *  @return bool   true.
     *  @code{.php}
     *      LEPTON_database::getInstance()->add_column('news','new_name','varchar(64) NOT NULL DEFAULT "0"');
     *  @endcode
     */
    public function add_column($table = '', $column = '', $desc= '') {
        $result = array(); 
        $this->execute_query(
            "DESCRIBE `".TABLE_PREFIX.$table."` `".$column."` ",
            true,
            $result,
            false
        );

        if(count($result) == 0) 
        {
            $this->simple_query("ALTER TABLE  `".TABLE_PREFIX.$table."` ADD `".$column."` ".$desc);
        }

        return(true);
    }
}
