<?php

declare(strict_types=1);

/**
 * 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-2026 LEPTON Project
 * @link            https://lepton-cms.org
 * @license         https://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;
    
    const DESCRIBE_RAW           = 0;
    const DESCRIBE_ASSOC         = 1;
    const DESCRIBE_ONLY_NAMES    = 2;
    
    /**
     *  Singleton The reference to *Singleton* instance of this class
     *  @var    LEPTON_database|null  $instance
     */
    private static LEPTON_database|null $instance = null;


	/**
	 *  needed for addons using old methods (lib_comp)
	 */
	private bool $bXrunInstalled = false;	

	 
    /**
     *  Private var for the error-messages.
     *  @var    string  $error;
     *  @access private
     */
    private string $error = '';
    
    /**
     *  The internal db handle.
     *  @var    PDO
     *  @access private
     */
    private PDO $db_handle;

    /**
     *  Public var to handle displaying the errors during the processes.
     *  (Makes use of LEPTON_tools::display)
     *  @var        boolean $bHaltOnError
     *  @seeAlso    LEPTON_tools::display
     */
    public bool $bHaltOnError = true;
    
    public bool $bForceDieOnError = true;
    
    /**
     *  Return the (singleton) instance of this class.
     *
     *  @param  array    $settings   Optional params - see "connect" for details
     *
     *  @see    connect
     */
    public static function getInstance(array &$settings = []): object
    {
        if (null === static::$instance)
        {
            static::$instance = new static();

            static::$instance->bXrunInstalled = class_exists("lib_comp", true);

            static::$instance->bOpenSslInstalled = defined('OPENSSL_VERSION_NUMBER');

            static::$instance->connect($settings);
        }
        
        return static::$instance;
    }
    
    /**
     *  Constructor of the class database
     *
     *  @param    array    $settings Assoc. array with the connection-settings. Call by reference!
     *
     *  @seealso        Method "connect" for details.        
     */
    public function __construct(array &$settings = [])
    {
        $this->connect($settings);
    }
    
    /**
     *  Destructor of the class database
     */
    public function __destruct()
    {

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

    /**
     *  Get the internal DB key
     * 
     *  @return string
     */    
    public function get_db_key(): string
    {
        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 $settings   Assoc. array within optional settings. Pass by reference!
     *  @return void
     *
     *  @notice Param 'settings' is an assoc. array with the connection-settings, 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 length 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 causes some unintended results if these keys are missing or formatted!
     *
     *            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(array &$settings = []): void
    {
        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);
                
                // [3.1]
                if (!isset($settings['host']))
                {
                    $settings['host'] = $config['database']['host'];
                }
                // [3.2]
                if (!isset($settings['user']))
                {
                    $settings['user'] = $config['database']['user'];
                }
                // [3.3]
                if (!isset($settings['pass']))
                {
                    $settings['pass'] = $config['database']['pass'];
                }
                // [3.4]
                if (!isset($settings['name']))
                {
                    $settings['name'] = $config['database']['name'];
                }
                // [3.5]
                if (!isset($settings['port']))
                {
                    $settings['port'] = $config['database']['port'];
                }
                // [3.6]
                if ((isset($config['database']['charset'])) && (!isset($settings['charset'])))
                {
                    $settings['charset'] = $config['database']['charset'];
                }
                // [3.7]
                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 = intval($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 = [
            '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
        ];

        if (array_key_exists('charset', $settings))
        {
            $setup['charset'] = $settings['charset'];
        } else {
            $setup['charset'] = (defined('DB_CHARSET') ? DB_CHARSET : "utf8");
        }

        // use DB_PORT only if it differs 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');
        }
        
    }
          
    /**
     *  Execute a mysql-query without returning a result.
     *  A typical use is e.g. "DROP TABLE IF EXIST" or "SET NAMES ..."
     *
     *  @param  string  $sMySQL_Query Any (MySQL-) Query
     *  @param  array   $aParams      Optional array within the values if place-holders are used.
     *
     *  @return int    if success number of affected rows
     *
     *  @code{.php} 
     *      LEPTON_database::getInstance()->simple_query("DROP TABLE `example` IF EXIST");
     *      // or
     *      LEPTON_database::getInstance()->simple_query(
     *          "INSERT into `TABLE_example` (`name`,`state`) VALUES('?','?');",
     *              array(
     *                  ['example','none'],
     *                  ['master', 'confirmed']
     *              )
     *      );
     *      // or
     *      LEPTON_database::getInstance()->simple_query(
     *          "INSERT into `TABLE_example` (`name`,`state`) VALUES( :name , :sta );",
     *              array(
     *                  "name"  => "Aldus",
     *                  "sta"   => "internal"
     *              )
     *      );
     *  @endcode
     */
    public function simple_query(string $sMySQL_Query = "", array $aParams = []): int
    {
        $this->error = "";
        try
        {
            $oStatement=$this->db_handle->prepare( $sMySQL_Query );
            if (!empty($aParams))
            {
                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 $oStatement->rowCount();
        } catch (PDOException $error) 
        {
            $this->error = $error->getMessage() . "\n<p>Query: " . $sMySQL_Query . "\n</p>\n";
            $this->HandleDisplayError("301");
            return -1;  // conform to mysql
        }
    }
    
    /**
     *    Execute a SQL query and return the first row of the result array
     *  
     *    @param    string  $SQL Any SQL-Query or statement
     *
     *    @return   mixed   Value of the table-field or null for error
     *
     */
    public function get_one(string $SQL): mixed
    {
        $this->error = "";
        
        try
        {
            $oStatement = $this->db_handle->prepare($SQL);
            $oStatement->execute();

            $data = $oStatement->fetch(PDO::FETCH_ASSOC);
            if (false === $data)
            {
                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 table-names of the current database
     *
     * @code{.php} 
     *      $tables = LEPTON_database::getInstance()->list_tables(TABLE_PREFIX);   
     *      if (in_array("my_table",$tables))
     *      {
     *         do this or don't do this 
     *      } 
     *  @endcode
     *
     * @param string $strip A string to 'strip' chars from the table-names, e.g. the prefix.
     * @return array|bool An array within the table-names of the current database, or FALSE (bool).
     *
     */
    public function list_tables(string $strip = ""): array|bool
    {
        $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 = [];
        foreach($data as &$ref)
        {
            $ret_value[] = array_shift( $ref );
        }
        unset($ref);
        
        if ($strip != "")
        {
            foreach ($ret_value as &$ref2)
            {
                $ref2 = str_replace($strip, "", $ref2);
            }
        }
        unset($ref2);
        
        return $ret_value;
    }
    
    /**
     *  Placed for all fields from a given table(-name) an assoc. array
     *  inside a given storage-array.
     *
     *  @param  string  $sTableName A valid table-name.
     *  @param  array   $aStorage   An array to store the results. Call by reference!
     *
     *  @return bool    True if success, otherwise false.
     *
     */
    public function describe_table(string $sTableName, array &$aStorage = [], int $iForm = self::DESCRIBE_RAW ): bool
    {
        $this->error = "";
        try
        {
            $oStatement=$this->db_handle->prepare( "DESCRIBE `" . $sTableName . "`" );
            $oStatement->execute();
            
            $aStorage = $oStatement->fetchAll();

            switch ($iForm)
            {
                case (self::DESCRIBE_ASSOC):
                    $aTemp = [];
                    foreach($aStorage as $values)
                    {
                        $aTemp[ $values["Field"] ] = $values;
                    }
                    unset($values);
                    $aStorage = $aTemp;
                    break;
                    
                case (self::DESCRIBE_ONLY_NAMES):
                    $aTemp = [];
                    foreach($aStorage as $values)
                    {
                        $aTemp[] = $values["Field"];
                    }
                    unset($values);
                    $aStorage = $aTemp;
                    break;
                    
                default:
                    // nothing - keep the storage as it is
                    break;
            }
            
            return true;
        }
        catch (Exception $error) 
        {
            $this->error = $error->getMessage();
            $this->HandleDisplayError("2");
            return false;
        }
    }

    /**
     *  Public "shortcut" for executing a single mySql-query without passing values.
     *
     *
     *  @param    string  $aQuery A valid mySQL query.
     *  @param    bool    $bFetch Fetching the result - default is false.
     *  @param    array   $aStorage A storage array for the fetched results. Pass by reference!
     *  @param    bool    $bFetchAll Try to get all entries. Default is true.
     *  @return   int 	  If success number of affected rows.
     *
     *  @example
     *      $results_array = [];       
     *      $database->execute_query( 
     *          "SELECT * from ".TABLE_PREFIX."pages WHERE page_id = ".$page_id." ",
     *          true, 
     *          $results_array, 
     *          false 
     *      );
     *        
     *
     */
    public function execute_query(string $aQuery="", bool $bFetch=false, array &$aStorage=[], bool $bFetchAll=true ) : int
    {
        $this->error = "";
        try{
            $oStatement=$this->db_handle->prepare($aQuery);
            $oStatement->execute();

            if (($oStatement->rowCount() > 0) && (true === $bFetch))
            {
                $aStorage = (true === $bFetchAll)
                    ? $oStatement->fetchAll()
                    : $oStatement->fetch()
                    ;
            
            }
            return $oStatement->rowCount();
        } catch( PDOException $error) {
            $this->error = $error->getMessage();
            $this->HandleDisplayError("10");
            return -1;
        }
    }

    /**
     *  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 table-name (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  bool $display_query     An optional value to display query as a string for development (only)
     *
     *  @return int    If success number of affected rows.
     *
     */
    public function build_and_execute(string $type, string $table_name, array $table_values, string $condition="", bool $display_query=false): int
    {
        $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;

            default:
                die(__line__.": build_and_execute-> type unknown!");
        }
		
		if($display_query === true)
		{
			die(LEPTON_tools::display_dev($q, 'pre','ui orange message'));
		}

        try 
        {
        
            $oStatement=$this->db_handle->prepare($q);
            $oStatement->execute( $table_values );
            return $oStatement->rowCount();
        } 
        catch( PDOException $error) 
        {
            $this->error = $error->getMessage()."\n<p>Query: ".$q."\n</p>\n";
            $this->HandleDisplayError("12");
            return -1;
        }

    }

    /**
     * Private function to handle the errors.
     *
     * @param string $sPrefix
     */
    private function HandleDisplayError(string $sPrefix = "" ): void
    {
        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;
            }
        }
    }


    /**
     *  Execute a SQL query and return a handle to queryMySQL
     *
     *  @param  string     $sSqlQuery   The query string to be executed.
     *  @return object|null   Object or null for error
     *
     */
    public function query(string $sSqlQuery = ""): object|null
    {
        $this->error = "";
        try{
            $oStatement = $this->db_handle->prepare($sSqlQuery);
            $oStatement->execute();

            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 to be extended.
     *  @param string  $column  Name
     *  @param string  $desc    column type and default
     *
     *  @return bool   true.
     *  @code{.php}
     *      LEPTON_database::getInstance()->add_column('news','new_name','varchar(64) NOT NULL DEFAULT "0"');
     *  @endcode
     */
    public function add_column(string $table = '', string $column = '', string $desc = ''): bool
    {
        $result = []; 
        $this->execute_query(
            "DESCRIBE `" . TABLE_PREFIX . $table . "` `" . $column . "`",
            true,
            $result,
            false
        );

        if (empty($result)) // no entry
        {
            $this->simple_query("ALTER TABLE  `" . TABLE_PREFIX . $table . "` ADD `" . $column . "` " . $desc);
        }

        return true;
    }
    
    /**
     *  Returns a linear array with all selected table-names for specified addon
     *
     *    @code{.php}
     *      $aTables = LEPTON_database::getInstance()->select_addon_tables('mod_procalendar');
     *      foreach ($aTables as $todo)
     *          {         
     *             do this or don't do this 
     *          } 
     *  @endcode
     *     
     *     @return array|bool An array within the table-names of the current database, or FALSE (bool).
     *     Keep in mind, that the TABLE_PREFIX is always included
     *
     *      mod_procalendar_actions
     *      mod_procalendar_eventgroups
     *      mod_procalendar_settings
     *
     */
    public function select_addon_tables(string $sAddonName = ""): array
    {
        $ret_value = [];

        $aAllTables = $this->list_tables(TABLE_PREFIX);

        foreach ($aAllTables as $sTempTablename)
        {
            if ($sTempTablename == $sAddonName)
            {
                $ret_value[] = $sAddonName;
            }
            
            if (str_starts_with($sTempTablename, $sAddonName."_"))
            {
                $ret_value[] =  $sTempTablename;
            }
        }
 
        return $ret_value;
    }
}
