<?php /*

 Composr
 Copyright (c) Christopher Graham, 2004-2024

 See docs/LICENSE.md for full licensing information.


 NOTE TO PROGRAMMERS:
   Do not edit this file. If you need to make changes, save your changed file to the appropriate *_custom folder
   **** If you ignore this advice, then your website upgrades (e.g. for bug fixes) will likely kill your changes ****

*/

/*EXTRA FUNCTIONS: pg\_.+|get_current_user*/

/**
 * @license    http://opensource.org/licenses/cpal_1.0 Common Public Attribution License
 * @copyright  Christopher Graham
 * @package    core_database_drivers
 */

// See tut_postgresql tutorial for documentation on using PostgreSQL.

/**
 * Database driver class.
 *
 * @package core_database_drivers
 */
class Database_Static_postgresql extends DatabaseDriver
{
    protected $cache_db = [];

    protected $table_prefix;

    /**
     * Set up the database driver.
     *
     * @param  string $table_prefix Table prefix
     */
    public function __construct(string $table_prefix)
    {
        $this->table_prefix = $table_prefix;
    }

    /**
     * Get the default user for making db connections (used by the installer as a default).
     *
     * @return string The default user for db connections
     */
    public function default_user() : string
    {
        if ((php_function_allowed('get_current_user'))) {
            //$_ret = posix_getpwuid(posix_getuid()); $ret = $_ret['name'];
            //$ret = posix_getlogin();
            $ret = get_current_user();
            if (!in_array($ret, ['apache', 'nobody', 'www', '_www'])) {
                return $ret;
            }
        }
        return 'postgres';
    }

    /**
     * Get the default password for making db connections (used by the installer as a default).
     *
     * @return string The default password for db connections
     */
    public function default_password() : string
    {
        return '';
    }

    /**
     * Get a database connection. This function shouldn't be used by you, as a connection to the database is established automatically.
     *
     * @param  boolean $persistent Whether to create a persistent connection
     * @param  string $db_name The database name
     * @param  string $db_host The database host (the server)
     * @param  string $db_user The database connection username
     * @param  string $db_password The database connection password
     * @param  boolean $fail_ok Whether to on error echo an error and return with a null, rather than giving a critical error
     * @return ?mixed A database connection (null: failed)
     */
    public function get_connection(bool $persistent, string $db_name, string $db_host, string $db_user, string $db_password, bool $fail_ok = false)
    {
        // Potential caching
        if (isset($this->cache_db[$db_name][$db_host])) {
            return $this->cache_db[$db_name][$db_host];
        }

        if (!function_exists('pg_pconnect')) {
            $error = 'The postgreSQL PHP extension not installed (anymore?). You need to contact the system administrator of this server.';
            if ($fail_ok) {
                echo ((running_script('install')) && (get_param_string('type', '') == 'ajax_db_details')) ? strip_html($error) : $error;
                return null;
            }
            critical_error('PASSON', $error);
        }

        $connection = $persistent ? @pg_pconnect('host=' . $db_host . ' dbname=' . $db_name . ' user=' . $db_user . ' password=' . $db_password) : @pg_connect('host=' . $db_host . ' dbname=' . $db_name . ' user=' . $db_user . ' password=' . $db_password);
        if ($connection === false) {
            $error = 'Could not connect to database-server (' . @strval(pg_last_error()) . ', ' . cms_error_get_last() . ')';
            if ($fail_ok) {
                echo ((running_script('install')) && (get_param_string('type', '') == 'ajax_db_details')) ? strip_html($error) : $error;
                return null;
            }
            critical_error('PASSON', $error);
        }

        $this->cache_db[$db_name][$db_host] = $connection;
        return $connection;
    }

    /**
     * Adjust an SQL query to apply offset/limit restriction.
     *
     * @param  string $query The complete SQL query
     * @param  ?integer $max The maximum number of rows to affect (null: no limit)
     * @param  integer $start The start row to affect
     */
    public function apply_sql_limit_clause(string &$query, ?int $max = null, int $start = 0)
    {
        if ((cms_strtoupper_ascii(substr(ltrim($query), 0, 7)) == 'SELECT ') || (cms_strtoupper_ascii(substr(ltrim($query), 0, 8)) == '(SELECT ')) {
            if (($max !== null) && ($start != 0)) {
                $query .= ' LIMIT ' . strval(intval($max)) . ' OFFSET ' . strval(intval($start));
            } elseif ($max !== null) {
                $query .= ' LIMIT ' . strval(intval($max));
            } elseif ($start != 0) {
                $query .= ' OFFSET ' . strval(intval($start));
            }
        }
    }

    /**
     * This function is a very basic query executor. It shouldn't usually be used by you, as there are abstracted versions available.
     *
     * @param  string $query The complete SQL query
     * @param  mixed $connection The DB connection
     * @param  ?integer $max The maximum number of rows to affect (null: no limit)
     * @param  integer $start The start row to affect
     * @param  boolean $fail_ok Whether to output an error on failure
     * @param  boolean $get_insert_id Whether to get the autoincrement ID created for an insert query
     * @param  boolean $save_as_volatile Whether we are saving as a 'volatile' file extension
     * @return ?mixed The results (null: no results), or the insert ID
     */
    public function query(string $query, $connection, ?int $max = null, int $start = 0, bool $fail_ok = false, bool $get_insert_id = false, bool $save_as_volatile = false)
    {
        $this->apply_sql_limit_clause($query, $max, $start);

        $sub = substr(ltrim($query), 0, 4);
        $has_results = (($sub === '(SEL') || ($sub === 'SELE') || ($sub === 'sele') || ($sub === 'CHEC') || ($sub === 'EXPL') || ($sub === 'REPA') || ($sub === 'DESC') || ($sub === 'SHOW'));

        $results = @pg_query($connection, $query);
        if ((($results === false) || (($has_results) && ($results === true))) && (!$fail_ok)) {
            $err = pg_last_error($connection);
            if (function_exists('ocp_mark_as_escaped')) {
                ocp_mark_as_escaped($err);
            }
            if ((!running_script('upgrader')) && ((!get_mass_import_mode()) || (current_fatalistic() > 0))) {
                if ((!function_exists('do_lang')) || (do_lang('QUERY_FAILED', null, null, null, null, false) === null)) {
                    $this->failed_query_exit(htmlentities('Query failed: ' . $query . ' : ' . $err));
                }

                $this->failed_query_exit(do_lang_tempcode('QUERY_FAILED', escape_html($query), ($err)));
            } else {
                $this->failed_query_echo(htmlentities('Database query failed: ' . $query . ' [') . ($err) . htmlentities(']'));
                return null;
            }
        }

        if (($results !== true) && ($has_results) && ($results !== false)) {
            return $this->get_query_rows($results, $query, $start);
        }

        if ($get_insert_id) {
            if (cms_strtoupper_ascii(substr(ltrim($query), 0, 7)) == 'UPDATE ') {
                return null;
            }

            // Inefficient :(
            $pos = strpos($query, '(');
            $table_name = substr($query, 12, $pos - 13);

            $r3 = @pg_query($connection, 'SELECT last_value FROM ' . $table_name . '_id_seq');
            if ($r3) {
                $seq_array = pg_fetch_row($r3, 0);
                return intval($seq_array[0]);
            }
        }

        return null;
    }

    /**
     * Get the rows returned from a SELECT query.
     *
     * @param  resource $results The query result pointer
     * @param  string $query The complete SQL query (useful for debugging)
     * @param  integer $start Where to start reading from
     * @return array A list of row maps
     */
    protected function get_query_rows($results, string $query, int $start) : array
    {
        $num_fields = pg_num_fields($results);
        $types = [];
        $names = [];
        for ($x = 1; $x <= $num_fields; $x++) {
            $types[$x - 1] = pg_field_type($results, $x - 1);
            $names[$x - 1] = cms_strtolower_ascii(pg_field_name($results, $x - 1));
        }

        $out = [];
        $i = 0;
        while (($row = pg_fetch_row($results)) !== false) {
            $j = 0;
            $newrow = [];
            foreach ($row as $v) {
                $name = $names[$j];
                $type = $types[$j];

                if (($type == 'INTEGER') || ($type == 'SMALLINT') || ($type == 'SERIAL') || ($type == 'UINTEGER') || ($type == 'int8') || ($type == 'int4') || ($type == 'int2')) {
                    if ($v !== null) {
                        $newrow[$name] = intval($v);
                    } else {
                        $newrow[$name] = null;
                    }
                } elseif ((substr($type, 0, 5) == 'FLOAT') || ($type == 'numeric')) {
                        $newrow[$name] = floatval($v);
                } elseif (($type == 'varchar') || ($type == 'text')) {
                    $newrow[$name] = $v;
                } else {
                    $newrow[$name] = $v;
                }

                $j++;
            }

            $out[] = $newrow;

            $i++;
        }
        pg_free_result($results);
        return $out;
    }

    /**
     * Get a map of software field types, to actual database types.
     *
     * @param  boolean $for_alter Whether this is for adding a table field
     * @return array The map
     */
    public function get_type_remap(bool $for_alter = false) : array
    {
        $type_remap = [
            'AUTO' => $for_alter ? 'integer' : 'serial',
            'AUTO_LINK' => 'integer',
            'INTEGER' => 'integer',
            'UINTEGER' => 'bigint',
            'SHORT_INTEGER' => 'smallint',
            'REAL' => 'real',
            'BINARY' => 'smallint',
            'MEMBER' => 'integer',
            'GROUP' => 'integer',
            'TIME' => 'bigint',
            'LONG_TRANS' => 'bigint',
            'SHORT_TRANS' => 'bigint',
            'LONG_TRANS__COMCODE' => 'integer',
            'SHORT_TRANS__COMCODE' => 'integer',
            'SHORT_TEXT' => 'varchar(255)',
            'TEXT' => 'varchar(4000)', // Set consistently as 4000 across all drivers due to SQL Server having the lowest limit ; this field type should only be used as an alternative to LONG_TEXT that can be defaulted to '' if not specified, necessary for adding fields to the table's of external systems
            'LONG_TEXT' => 'text',
            'ID_TEXT' => 'varchar(80)',
            'MINIID_TEXT' => 'varchar(40)',
            'IP' => 'varchar(40)',
            'LANGUAGE_NAME' => 'varchar(5)',
            'TOKEN' => 'varchar(15)',
            'URLPATH' => 'varchar(255)',
        ];
        return $type_remap;
    }

    /**
     * Get SQL for creating a new table.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  array $fields A map of field names to software field types (with *#? encodings)
     * @param  mixed $connection The DB connection to make on
     * @param  ID_TEXT $raw_table_name The table name with no table prefix
     * @param  boolean $save_bytes Whether to use lower-byte table storage, with trade-offs of not being able to support all unicode characters; use this if key length is an issue
     * @return array List of SQL queries to run
     */
    public function create_table__sql(string $table_name, array $fields, $connection, string $raw_table_name, bool $save_bytes = false) : array
    {
        $type_remap = $this->get_type_remap();

        $_fields = '';
        $keys = '';
        foreach ($fields as $name => $type) {
            if ($type[0] == '*') { // Is a key
                $type = substr($type, 1);
                if ($keys != '') {
                    $keys .= ', ';
                }
                $keys .= $name;
            }

            if ($type[0] == '?') { // Is perhaps null
                $type = substr($type, 1);
                $perhaps_null = 'NULL';
            } else {
                $perhaps_null = 'NOT NULL';
            }

            $type = isset($type_remap[$type]) ? $type_remap[$type] : $type;

            $_fields .= '    ' . $name . ' ' . $type;

            // We specify default values for special Comcode fields, so we don't need to worry about populating them when manually editing the database
            if (substr($name, -13) == '__text_parsed') {
                $_fields .= ' DEFAULT \'\'';
            } elseif (substr($name, -13) == '__source_user') {
                $_fields .= ' DEFAULT ' . strval(db_get_first_id());
            }

            $_fields .= ' ' . $perhaps_null . ',' . "\n";
        }

        $query = 'CREATE TABLE ' . $table_name . ' (' . "\n" . $_fields . '    PRIMARY KEY (' . $keys . ")\n)";
        return [$query];
    }

    /**
     * Get SQL for renaming a table.
     *
     * @param  ID_TEXT $old Old name
     * @param  ID_TEXT $new New name
     * @return string SQL query to run
     */
    public function rename_table__sql(string $old, string $new) : string
    {
        return 'ALTER TABLE ' . $old . ' RENAME TO ' . $new;
    }

    /**
     * Find whether drop table "if exists" is present.
     *
     * @return boolean Whether it is
     */
    public function has_drop_table_if_exists() : bool
    {
        return true;
    }

    /**
     * Get SQL for changing the type of a DB field in a table.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  ID_TEXT $name The field name
     * @param  ID_TEXT $db_type The new field type
     * @param  boolean $may_be_null If the field may be null
     * @param  ?boolean $is_autoincrement Whether it is an autoincrement field (null: could not set it, returned by reference)
     * @param  ID_TEXT $new_name The new field name
     * @return array List of SQL queries to run
     */
    public function alter_table_field__sql(string $table_name, string $name, string $db_type, bool $may_be_null, ?bool &$is_autoincrement, string $new_name) : array
    {
        $delimiter_start = $this->get_delimited_identifier(false);
        $delimiter_end = $this->get_delimited_identifier(true);

        $queries = [];

        $query = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $delimiter_start . $name . $delimiter_end . ' SET DATA TYPE ' . $db_type;
        if (strpos($db_type, 'int') !== false) {
            $query .= ' USING ' . $name . '::integer'; // Because the conversion is not guaranteed to be perfect we have to force a cast
        } elseif (strpos($db_type, 'real') !== false) {
            $query .= ' USING ' . $name . '::real'; // Because the conversion is not guaranteed to be perfect we have to force a cast
        }
        $queries[] = $query;

        if ($may_be_null) {
            $queries[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $delimiter_start . $name . $delimiter_end . ' DROP NOT NULL';
        } else {
            $queries[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $delimiter_start . $name . $delimiter_end . ' SET NOT NULL';
        }

        if ($name != $new_name) {
            $queries[] = 'ALTER TABLE ' . $table_name . ' RENAME COLUMN ' . $delimiter_start . $name . $delimiter_end . ' TO ' . $new_name;
        }

        if ($is_autoincrement) {
            $queries[] = 'CREATE SEQUENCE ' . $table_name . '_' . $name . '_seq OWNED BY ' . $table_name . '.' . $name;
            $queries[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $name . ' SET DEFAULT nextval(\'' . $table_name . '_' . $name . '_seq\')';
        }

        return $queries;
    }

    /**
     * Get SQL for creating a table index.
     *
     * @param  ID_TEXT $table_name The name of the table to create the index on
     * @param  ID_TEXT $index_name The index name (not really important at all)
     * @param  string $_fields Part of the SQL query: a comma-separated list of fields to use on the index
     * @param  mixed $connection_read The DB connection, may be used for running checks
     * @param  ID_TEXT $raw_table_name The table name with no table prefix
     * @param  string $unique_key_fields The name of the unique key field for the table
     * @param  string $table_prefix The table prefix
     * @return array List of SQL queries to run
     */
    public function create_index__sql(string $table_name, string $index_name, string $_fields, $connection_read, string $raw_table_name, string $unique_key_fields, string $table_prefix) : array
    {
        if ($index_name[0] == '#') {
            $index_name = substr($index_name, 1);

            $postgres_fulltext_language = function_exists('get_value') ? get_value('postgres_fulltext_language') : null/*backup restore?*/;
            if ($postgres_fulltext_language === null) {
                $postgres_fulltext_language = 'english';
            }

            $aggregation = '';
            foreach (explode(',', $_fields) as $_field) {
                if ($aggregation != '') {
                    $aggregation .= ' || \' \' || ';
                }
                $aggregation .= '\'' . $this->escape_string($_field) . '\'';
            }

            return ['CREATE INDEX ' . $index_name . '__' . $table_name . ' ON ' . $table_name . ' USING gin(to_tsvector(\'pg_catalog.' . $postgres_fulltext_language . '\', ' . $aggregation . '))'];
        }

        $_fields = preg_replace('#\(\d+\)#', '', $_fields);

        $fields = explode(',', $_fields);
        foreach ($fields as $field) {
            $sql = 'SELECT m_type FROM ' . $table_prefix . 'db_meta WHERE ' . $this->string_equal_to('m_table', $raw_table_name) . ' AND ' . $this->string_equal_to('m_name', $field);
            $values = $this->query($sql, $connection_read, null, 0, true);
            if (!isset($values[0])) {
                continue; // No result found
            }
            $first = $values[0];
            $field_type = current($first); // Result found

            if (strpos($field_type, 'LONG') !== false) {
                // We can't support this in PostgreSQL, too much data will give an error when inserting into the index
                return [];
            }
        }

        return ['CREATE INDEX ' . $index_name . '__' . $table_name . ' ON ' . $table_name . '(' . $_fields . ')'];
    }

    /**
     * Get SQL for deleting a table index.
     *
     * @param  ID_TEXT $table_name The name of the table the index is on
     * @param  ID_TEXT $index_name The index name
     * @return ?string SQL query to run (null: not supported)
     */
    public function drop_index__sql(string $table_name, string $index_name) : ?string
    {
        return 'DROP INDEX ' . $index_name . '__' . $table_name;
    }

    /**
     * Get SQL for changing the primary key of a table.
     *
     * @param  string $table_prefix The table prefix
     * @param  ID_TEXT $table_name The name of the table to create the index on
     * @param  array $new_key A list of fields to put in the new key
     * @return array List of SQL queries to run
     */
    public function change_primary_key__sql(string $table_prefix, string $table_name, array $new_key) : array
    {
        $queries = [];
        $queries[] = 'ALTER TABLE ' . $table_prefix . $table_name . ' DROP CONSTRAINT ' . $table_prefix . $table_name . '_pkey';
        if (!empty($new_key)) {
            $queries[] = 'ALTER TABLE ' . $table_prefix . $table_name . ' ADD PRIMARY KEY (' . implode(',', $new_key) . ')';
        }
        return $queries;
    }

    /**
     * Get the number of rows in a table, with approximation support for performance (if necessary on the particular database backend).
     *
     * @param  string $table The table name
     * @param  mixed $connection The DB connection
     * @return ?integer The count (null: do it normally)
     */
    public function get_table_count_approx(string $table, $connection) : ?int
    {
        $sql = 'SELECT n_live_tup FROM pg_stat_all_tables WHERE relname=\'' . $this->escape_string($table) . '\'';
        $values = $this->query($sql, $connection, null, 0, true);
        if (!isset($values[0])) {
            return null; // No result found
        }
        $first = $values[0];
        $v = current($first); // Result found
        return $v;
    }

    /**
     * Get minimum search length.
     * This is broadly MySQL-specific. For other databases we will usually return 4, although there may truly not be a limit on it.
     *
     * @param  mixed $connection The DB connection
     * @return integer Search length
     */
    public function get_minimum_search_length($connection) : int
    {
        return 1;
    }

    /**
     * Find the maximum number of indexes supported.
     *
     * @return ?integer Maximum number of indexes (null: no limit or inconsequentially-large limit)
     */
    public function get_max_indexes() : ?int
    {
        return null;
    }

    /**
     * Find whether full-text-search is present.
     *
     * @param  mixed $connection The DB connection
     * @return boolean Whether it is
     */
    public function has_full_text($connection) : bool
    {
        return true;
    }

    /**
     * Find whether full-text-boolean-search is present.
     *
     * @return boolean Whether it is
     */
    public function has_full_text_boolean() : bool
    {
        return true; // Actually it is always boolean for PostgreSQL
    }

    /**
     * Assemble part of a WHERE clause for doing full-text search.
     *
     * @param  string $content Our match string (assumes "?" has been stripped already)
     * @return string Part of a WHERE clause for doing full-text search
     */
    public function full_text_assemble(string $content) : string
    {
        static $stopwords = null;
        if ($stopwords === null) {
            require_code('database_search');
            $stopwords = get_stopwords_list();
        }
        if (isset($stopwords[trim(cms_mb_strtolower($content), '"')])) {
            if (($GLOBALS['DEV_MODE']) || (!has_solemnly_declared(I_UNDERSTAND_SQL_INJECTION))) {
                require_code('database_security_filter');
                $GLOBALS['DB_ESCAPE_STRING_LIST'][$this->escape_string(trim($content, '"'))] = true;
            }

            // This is an imperfect solution for searching for a stop-word
            // It will not cover the case where the stop-word is within the wider text. But we can't handle that case efficiently anyway
            return db_string_equal_to('?', trim($content, '"'));
        }

        $postgres_fulltext_language = get_value('postgres_fulltext_language');
        if ($postgres_fulltext_language === null) {
            $postgres_fulltext_language = 'english';
        }

        if (($GLOBALS['DEV_MODE']) || (!has_solemnly_declared(I_UNDERSTAND_SQL_INJECTION))) {
            require_code('database_security_filter');
            $GLOBALS['DB_ESCAPE_STRING_LIST'][$this->escape_string($content)] = true;
        }

        return 'to_tsvector(?) @@ websearch_to_tsquery(\'pg_catalog.' . $postgres_fulltext_language . '\', \'' . $this->escape_string($content) . '\')';
    }

    /**
     * Whether 'OFFSET' syntax is used on limit clauses.
     *
     * @return boolean Whether it is
     */
    public function uses_offset_syntax() : bool
    {
        return true;
    }

    /**
     * Set a time limit on future queries.
     * Not all database drivers support this.
     *
     * @param  integer $seconds The time limit in seconds
     * @param  mixed $connection The DB connection
     */
    public function set_query_time_limit(int $seconds, $connection)
    {
        $this->query('SET statement_timeout TO ' . strval($seconds * 1000), $connection, null, 0, true);
    }

    /**
     * Encode an SQL statement fragment for a conditional to see if two strings are equal.
     *
     * @param  ID_TEXT $attribute The attribute
     * @param  string $compare The comparison
     * @return string The SQL
     */
    public function string_equal_to(string $attribute, string $compare) : string
    {
        return $attribute . "='" . $this->escape_string($compare) . "'";
    }

    /**
     * Escape a string so it may be inserted into a query. If SQL statements are being built up and passed using db_query then it is essential that this is used for security reasons. Otherwise, the abstraction layer deals with the situation.
     *
     * @param  string $string The string
     * @return string The escaped string
     */
    public function escape_string(string $string) : string
    {
        $string = fix_bad_unicode($string);

        return pg_escape_string($string);
    }

    /**
     * Close the database connections. We don't really need to close them (will close at exit), just disassociate so we can refresh them.
     */
    public function close_connections()
    {
        foreach ($this->cache_db as $db_names => $connections) {
            foreach ($connections as $connection) {
                @pg_close($connection);
            }
        }
        $this->cache_db = [];
    }
}
