<?php

/*
 * Textpattern Content Management System
 * https://textpattern.com/
 *
 * Copyright (C) 2025 The Textpattern Development Team
 *
 * This file is part of Textpattern.
 *
 * Textpattern is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation, version 2.
 *
 * Textpattern is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Textpattern. If not, see <https://www.gnu.org/licenses/>.
 */

/**
 * Database abstraction layer.
 *
 * @package DB
 */

if (!defined('PFX')) {
    /**
     * Database table prefix.
     */

    define('PFX', !empty($txpcfg['table_prefix']) ? $txpcfg['table_prefix'] : '');
}

/**
 * Initialises a database connection.
 *
 * @package DB
 */

class DB
{
    /**
     * The database server hostname.
     *
     * @var string
     */

    public $host;

    /**
     * The database server port.
     *
     * @var int
     */

    public $port;

    /**
     * The database server socket.
     *
     * @var string
     */

    public $socket;

    /**
     * The database name.
     *
     * @var string
     */

    public $db;

    /**
     * The username.
     *
     * @var string
     */

    public $user;

    /**
     * The password.
     *
     * @var string
     */

    public $pass;

    /**
     * Database table prefix.
     *
     * @var   string
     * @since 4.6.0
     */

    public $table_prefix = '';

    /**
     * Database client flags.
     *
     * @var int
     */

    public $client_flags = 0;

    /**
     * SSL parameters.
     *
     * @var string
     */

    public $ssl;

    /**
     * Database connection charset.
     *
     * @var   string
     * @since 4.6.0
     */

    public $charset = '';

    /**
     * The database link identifier.
     *
     * @var resource
     */

    public $link;

    /**
     * Database server version.
     *
     * @var string
     */

    public $version;

    /**
     * Default table options definition.
     *
     * @var array
     */

    public $table_options = array();

    /**
     * The default character set for the connection.
     *
     * @var   string
     * @since 4.6.0
     */

    public $default_charset;

    /**
     * Creates a new link.
     */

    public function __construct()
    {
        global $txpcfg, $connected;

        $client_flags = MYSQLI_CLIENT_FOUND_ROWS;

        $this->link = mysqli_init();

        if (strpos($txpcfg['host'], ':') === false) {
            $this->host = $txpcfg['host'];
            $this->port = ini_get("mysqli.default_port");
        } else {
            list($this->host, $this->port) = explode(':', $txpcfg['host'], 2);
            $this->port = intval($this->port);
        }

        if (isset($txpcfg['socket'])) {
            $this->socket = $txpcfg['socket'];
        } else {
            $this->socket = ini_get("mysqli.default_socket");
        }

        if (isset($txpcfg['ssl']) && is_array($txpcfg['ssl'])) {
            $client_flags = $client_flags | MYSQLI_CLIENT_SSL;

            foreach (array('key', 'cert', 'ca', 'capath', 'ciphers') as $ssl_param) {
                if (isset($txpcfg['ssl'][$ssl_param])) {
                    $this->ssl[$ssl_param] = $txpcfg['ssl'][$ssl_param];
                } else {
                    $this->ssl[$ssl_param] = null;
                }
            }

            if (isset($txpcfg['ssl']['flags']) && is_array($txpcfg['ssl']['flags'])) {
                foreach ($txpcfg['ssl']['flags'] as $ssl_flag => $ssl_flag_value) {
                    switch ($ssl_flag_value) {
                        case 'true':
                            mysqli_options($this->link, $ssl_flag, true);
                            break;
                        case 'false':
                            mysqli_options($this->link, $ssl_flag, false);
                            break;
                        default:
                            mysqli_options($this->link, $ssl_flag, $ssl_flag_value);
                            break;
                    }
                }
            }

            mysqli_ssl_set(
                $this->link,
                $this->ssl['key'],
                $this->ssl['cert'],
                $this->ssl['ca'],
                $this->ssl['capath'],
                $this->ssl['ciphers']
            );
        }

        $this->db = $txpcfg['db'];
        $this->user = $txpcfg['user'];
        $this->pass = $txpcfg['pass'];
        $this->table_options['engine'] = !empty($txpcfg['dbengine']) ? $txpcfg['dbengine'] : 'MyISAM';

        if (!empty($txpcfg['table_prefix'])) {
            $this->table_prefix = $txpcfg['table_prefix'];
        }

        if (isset($txpcfg['client_flags'])) {
            $this->client_flags = $txpcfg['client_flags'];
        } else {
            $this->client_flags = $client_flags;
        }

        if (isset($txpcfg['dbcharset'])) {
            $this->charset = $txpcfg['dbcharset'];
        }

        // Suppress screen output from mysqli_real_connect().
        try {
            $error_reporting = error_reporting();
            error_reporting($error_reporting & ~(E_WARNING | E_NOTICE));
            mysqli_real_connect($this->link, $this->host, $this->user, $this->pass, $this->db, $this->port, $this->socket, $this->client_flags);
            error_reporting($error_reporting);
        } catch (Exception $e) {
            die(db_down());
        }

        $version = $this->version = mysqli_get_server_info($this->link);
        $connected = true;

        // Be backwards compatible.
        if ($this->charset) {
            if (method_exists('mysqli', 'set_charset')) {
                mysqli_set_charset($this->link, $this->charset);
            } else {
                mysqli_query($this->link, "SET NAMES ".$this->charset);
            }

            $this->table_options['charset'] = $this->charset;

            if (isset($txpcfg['table_collation'])) {
                $this->table_options['collate'] = $txpcfg['table_collation'];
            } else {
                if ($this->charset == 'utf8mb4') {
                    $this->table_options['collate'] = "utf8mb4_unicode_ci";
                } elseif ($this->charset == 'utf8') {
                    $this->table_options['collate'] = "utf8_general_ci";
                }
            }
        }

        $this->default_charset = mysqli_character_set_name($this->link);
    }
}

/**
 * Current database link.
 *
 * @access private
 * @global DB $DB
 */

$DB = new DB;

/**
 * Prefixes a database table's name for use in a query.
 *
 * Textpattern can be installed to a shared database, this is achieved by
 * prefixing database tables. This function can be used to add those prefixes to
 * a known named table when building SQL statements.
 *
 * Always use this function, or the safe_pfx_j(), when you refer tables in raw
 * SQL statements, including where clauses, joins and sub-queries.
 *
 * This function will also quote the table name if necessary.
 *
 * You don't need to use this function in any of the dedicated "table"
 * parameters database functions offer. Any table used in a table parameter is
 * prefixed for you.
 *
 * @param  string $table The database table
 * @return string The $table with a prefix
 * @see    safe_pfx_j()
 * @example
 * if (safe_query('DROP TABLE '.safe_pfx('myTable'))
 * {
 *     echo 'myTable dropped';
 * }
 */

function safe_pfx($table)
{
    global $DB;
    $name = $DB->table_prefix.$table;

    if (preg_match('@[^\w._$]@', $name)) {
        return '`'.$name.'`';
    }

    return $name;
}

/**
 * Prefixes a database table's name for use in a joined query.
 *
 * This function prefixes the given table name similarly to safe_pfx(), but also
 * creates a named, unprefixed, AS alias for it.
 *
 * The created alias is same as the table name given. This function is here to
 * help to make joined queries where you need to refer to two or more tables in
 * a one query.
 *
 * As with safe_pfx(), you don't need to use this function in any of the
 * dedicated "table" parameters database functions offer. Any table used in a
 * table parameter is prefixed for you.
 *
 * @param  string $table The database table, or comma-separated list of tables
 * @return string The $table with a prefix
 * @see    safe_pfx()
 * @example
 * if ($r = getRows('SELECT id FROM '.safe_pfx_j('tableA').' JOIN '.safe_pfx('tableB').' ON tableB.id = tableA.id and tableB.active = 1'))
 * {
 *     print_r($r);
 * }
 */

function safe_pfx_j($table)
{
    global $DB;
    $ts = array();

    foreach (explode(',', $table) as $t) {
        $name = $DB->table_prefix.trim($t);
        if (preg_match('@[^\w._$]@', $name)) {
            $ts[] = "`$name`".($DB->table_prefix ? " as `$t`" : '');
        } else {
            $ts[] = "$name".($DB->table_prefix ? " as $t" : '');
        }
    }

    return join(', ', $ts);
}

/**
 * Escapes special characters in a string for use in an SQL statement.
 *
 * @param  string $in The input string
 * @return string
 * @since  4.5.0
 * @see    doSlash()
 * @example
 * if (safe_update('myTable', "value = '".doSlash($user_value)."'", "name = '".doSlash($user_name)."'"))
 * {
 *     echo 'Updated.';
 * }
 */

function safe_escape($in = '')
{
    global $DB;

    return isset($in) ? mysqli_real_escape_string($DB->link, $in) : '';
}

/**
 * Escape LIKE pattern's wildcards in a string for use in an SQL statement.
 *
 * @param  string $in The input string
 * @return string
 * @since  4.6.0
 * @see    doLike()
 * @example
 * if (safe_update('myTable', "value = '".doLike($user_value)."'", "name LIKE '".doLike($user_name)."'"))
 * {
 *     echo 'Updated.';
 * }
 */

function safe_escape_like($in = '')
{
    return safe_escape(str_replace(
        array('\\', '%', '_', '\''),
        array('\\\\', '\\%', '\\_', '\\\''),
        (string) $in
    ));
}

/**
 * Executes an SQL statement.
 *
 * @param  string $q     The SQL statement to execute
 * @param  bool   $debug Dump query
 * @param  bool   $unbuf If TRUE, executes the statement without fetching and buffering the results
 * @return mixed
 * @example
 * echo safe_query('SELECT * FROM table');
 */

function safe_query($q = '', $debug = false, $unbuf = false)
{
    global $DB, $trace, $production_status;

    $method = ($unbuf) ? MYSQLI_USE_RESULT : MYSQLI_STORE_RESULT;

    if (!$q) {
        return false;
    }

    if ($debug or TXP_DEBUG === 1) {
        dmp($q);
    }

    if ($production_status !== 'live') {
        $trace->start("[SQL: $q ]", true);
    }

    try {
        $result = mysqli_query($DB->link, $q, $method);
    } catch (mysqli_sql_exception $e) {
        if ($debug or TXP_DEBUG === 1) {
            dmp($e->getMessage());
            trigger_error(mysqli_error($DB->link));
        }

        return false;
    }

    if ($production_status !== 'live') {
        if (is_bool($result)) {
            $trace->stop();
        } else {
            $trace->stop("[Rows: ".intval(mysqli_num_rows($result))."]");
        }
    }

    if ($result === false) {
        trigger_error(mysqli_error($DB->link));
    }

    if (!$result) {
        return false;
    }

    return $result;
}

/**
 * Deletes a row from a table.
 *
 * @param  string $table The table
 * @param  string $where The where clause
 * @param  bool   $debug Dump query
 * @return bool FALSE on error
 * @see    safe_update()
 * @see    safe_insert()
 * @example
 * if (safe_delete('myTable', "name = 'test'"))
 * {
 *     echo "'test' removed from 'myTable'.";
 * }
 */

function safe_delete($table, $where, $debug = false)
{
    return (bool) safe_query("DELETE FROM ".safe_pfx($table)." WHERE $where", $debug);
}

/**
 * Updates a table row.
 *
 * @param  string $table The table
 * @param  string $set   The set clause
 * @param  string $where The where clause
 * @param  bool   $debug Dump query
 * @return bool FALSE on error
 * @see    safe_insert()
 * @see    safe_delete()
 * @example
 * if (safe_update('myTable', "myField = 'newValue'", "name = 'test'"))
 * {
 *     echo "'test' updated, 'myField' set to 'newValue'";
 * }
 */

function safe_update($table, $set, $where, $debug = false)
{
    return (bool) safe_query("UPDATE ".safe_pfx($table)." SET $set WHERE $where", $debug);
}

/**
 * Inserts a new row into a table.
 *
 * @param  string $table The table
 * @param  string $set   The set clause
 * @param  bool   $debug Dump query
 * @return int|bool The last generated ID or FALSE on error. If the ID is 0, returns TRUE
 * @see    safe_update()
 * @see    safe_delete()
 * @example
 * if ($id = safe_insert('myTable', "name = 'test', myField = 'newValue'"))
 * {
 *     echo "Created a row to 'myTable' with the name 'test'. It has ID of {$id}.";
 * }
 */

function safe_insert($table, $set, $debug = false)
{
    global $DB;
    $q = "INSERT INTO ".safe_pfx($table)." SET $set";

    if ($r = safe_query($q, $debug)) {
        $id = mysqli_insert_id($DB->link);

        return ($id === 0 ? true : $id);
    }

    return false;
}

/**
 * Inserts a new row, or updates an existing if a matching row is found.
 *
 * @param  string       $table The table
 * @param  string|array $set   The set clause
 * @param  string|array $where The where clause
 * @param  bool         $debug Dump query
 * @return int|bool The last generated ID or FALSE on error. If the ID is 0, returns TRUE
 * @example
 * if ($r = safe_upsert('myTable', "data = 'foobar'", "name = 'example'"))
 * {
 *     echo "Inserted new row to 'myTable', or updated 'example'.";
 * }
 */

function safe_upsert($table, $set, $where, $debug = false)
{
    global $DB;

    if (is_array($set)) {
        $set = join_qs(quote_list($set), ',');
    }

    $whereset = is_array($where) ? join_qs(quote_list($where), null) : array($where);
    $where = implode(' AND ', $whereset);

    // FIXME: lock the table so this is atomic?
    $r = safe_update($table, $set, $where, $debug);

    if ($r && (mysqli_affected_rows($DB->link) || safe_count($table, $where, $debug))) {
        return $r;
    } else {
        return safe_insert($table, join(', ', array(implode(', ', $whereset), $set)), $debug);
    }
}

/**
 * Changes the structure of a table.
 *
 * @param  string $table The table
 * @param  string $alter The statement to execute
 * @param  bool   $debug Dump query
 * @return bool FALSE on error
 * @example
 * if (safe_alter('myTable', 'ADD myColumn TINYINT(1)'))
 * {
 *     echo "'myColumn' added to 'myTable'";
 * }
 */

function safe_alter($table, $alter, $debug = false)
{
    return (bool) safe_query("ALTER TABLE ".safe_pfx($table)." $alter", $debug);
}

/**
 * Locks a table.
 *
 * The $table argument accepts comma-separated list of table names, if you need
 * to lock multiple tables at once.
 *
 * @param  string $table The table
 * @param  string $type  The lock type
 * @param  bool   $debug Dump the query
 * @return bool TRUE if the tables are locked
 * @since  4.6.0
 * @example
 * if (safe_lock('myTable'))
 * {
 *     echo "'myTable' is 'write' locked.";
 * }
 */

function safe_lock($table, $type = 'write', $debug = false)
{
    return (bool) safe_query("LOCK TABLES ".join(' '.$type.', ', doArray(do_list_unique($table), 'safe_pfx')).' '.$type, $debug);
}

/**
 * Unlocks tables.
 *
 * @param  bool $debug Dump the query
 * @return bool TRUE if tables aren't locked
 * @since  4.6.0
 * @example
 * if (safe_unlock())
 * {
 *     echo 'Tables are unlocked.';
 * }
 */

function safe_unlock($debug = false)
{
    return (bool) safe_query("UNLOCK TABLES", $debug);
}

/**
 * Gets an array of information about an index.
 *
 * @param  string $table The table
 * @param  string $index The index
 * @param  bool   $debug Dump the query
 * @return array|bool Array of information about the index, or FALSE on error
 * @since  4.6.0
 * @example
 * if ($index = safe_index('myTable', 'myIndex'))
 * {
 *     echo "'myIndex' found in 'myTable' with the type of {$index['Index_type']}.";
 * }
 */

function safe_index($table, $index, $debug = false)
{
    $index = strtolower($index);

    if ($r = safe_show("INDEX", $table, $debug)) {
        foreach ($r as $a) {
            if (strtolower($a['Key_name']) === $index) {
                return $a;
            }
        }
    }

    return false;
}

/**
 * Creates an index.
 *
 * @param  string $table   The table
 * @param  string $columns Indexed columns
 * @param  string $name    The name
 * @param  string $index   The index. Either 'unique', 'fulltext', 'spatial' (default = standard index)
 * @param  string $type    The index type
 * @param  bool   $debug   Dump the query
 * @return bool TRUE if index exists
 * @since  4.6.0
 * @example
 * if (safe_create_index('myTable', 'col1(11), col2(11)', 'myIndex'))
 * {
 *     echo "'myIndex' exists in 'myTable'.";
 * }
 */

function safe_create_index($table, $columns, $name, $index = '', $type = '', $debug = false)
{
    if (safe_index($table, $name, $debug)) {
        return true;
    }

    if (strtolower($name) == 'primary') {
        $q = "ALTER TABLE ".safe_pfx($table)." ADD PRIMARY KEY ($columns)";
    } else {
        $q = "CREATE $index INDEX `$name` ".($type ? " USING ".$type : '')." ON ".safe_pfx($table)." ($columns)";
    }

    return (bool) safe_query($q, $debug);
}

/**
 * Removes an index.
 *
 * @param  string $table The table
 * @param  string $index The index
 * @param  bool   $debug Dump the query
 * @return bool TRUE if the index no longer exists
 * @since  4.6.0
 * @example
 * if (safe_drop_index('myTable', 'primary'))
 * {
 *     echo "Primary key no longer exists in 'myTable'.";
 * }
 */

function safe_drop_index($table, $index, $debug = false)
{
    if (!safe_index($table, $index, $debug)) {
        return true;
    }

    if (strtolower($index) === 'primary') {
        $q = "ALTER TABLE ".safe_pfx($table)." DROP PRIMARY KEY";
    } else {
        $q = "DROP INDEX `$index` ON ".safe_pfx($table);
    }

    return (bool) safe_query($q, $debug);
}

/**
 * Optimises a table.
 *
 * @param  string $table The table
 * @param  bool   $debug Dump query
 * @return bool FALSE on error
 * @example
 * if (safe_optimize('myTable'))
 * {
 *     echo "myTable optimised successfully.";
 * }
 */

function safe_optimize($table, $debug = false)
{
    return (bool) safe_query("OPTIMIZE TABLE ".safe_pfx($table), $debug);
}

/**
 * Repairs a table.
 *
 * @param  string $table The table
 * @param  bool   $debug Dump query
 * @return bool   FALSE on error
 * @example
 * if (safe_repair('myTable'))
 * {
 *     echo "myTable repaired successfully.";
 * }
 */

function safe_repair($table, $debug = false)
{
    return (bool) safe_query("REPAIR TABLE ".safe_pfx($table), $debug);
}

/**
 * Truncates a table.
 *
 * Running this function empties a table completely, resets indexes and the auto
 * increment value.
 *
 * @param  string $table The table
 * @param  bool   $debug Dump query
 * @return bool   TRUE if the table is empty
 * @see    safe_delete()
 * @since  4.6.0
 * @example
 * if (safe_truncate('myTable'))
 * {
 *     echo "myTable emptied successfully.";
 * }
 */

function safe_truncate($table, $debug = false)
{
    return (bool) safe_query("TRUNCATE TABLE ".safe_pfx($table), $debug);
}

/**
 * Removes a table.
 *
 * This function removes all data and definitions associated with a table.
 *
 * @param  string $table The table
 * @param  bool   $debug Dump query
 * @return bool   TRUE if the table no longer exists
 * @since  4.6.0
 * @example
 * if (safe_drop('myTable'))
 * {
 *     echo "'myTable' no longer exists.";
 * }
 */

function safe_drop($table, $debug = false)
{
    return (bool) safe_query("DROP TABLE IF EXISTS ".safe_pfx($table), $debug);
}

/**
 * Check if given table(s) exist in the database.
 *
 * @param  string|array $table Table(s) to check
 * @param  bool         $debug Dump query
 * @return bool         True if all nominated tables exist
 * @since 4.9.0
 * @example
 * if (!safe_exists('myTable, myOtherTable'))
 * {
 *     // Create tables here;
 * }
 */

function safe_exists($table, $debug = false)
{
    $table = is_array($table) ? $table : do_list_unique($table);
    $table_set = array();

    foreach ($table as $tbl) {
        $table_set[] = "TABLE_NAME = '". safe_pfx($tbl) . "'";
    }

    $expected = count($table_set);
    $table_clause = implode(' OR ', $table_set);

    $ret = getThing("SELECT count(1) as num FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND (".$table_clause.")", $debug);

    return $ret == $expected;
}

/**
 * Creates a table.
 *
 * Creates a table with the given name. This table will be created with
 * identical properties to core tables, ensuring the best possible compatibility.
 *
 * @param  string $table      The table
 * @param  string $definition The create definition
 * @param  string $options    Table options
 * @param  bool   $debug      Dump the query
 * @return bool   TRUE if table exists
 * @since  4.6.0
 * @example
 * if (safe_create('myTable', "id int(11)"))
 * {
 *     echo "'myTable' exists.";
 * }
 */

function safe_create($table, $definition, $options = '', $debug = false)
{
    global $DB;

    foreach ($DB->table_options as $name => $value) {
        $options .= ' '.strtoupper($name).' = '.$value;
    }

    $q = "CREATE TABLE IF NOT EXISTS ".safe_pfx($table)." ($definition) $options";

    return (bool) safe_query($q, $debug);
}

/**
 * Renames a table.
 *
 * @param  string $table   The table
 * @param  string $newname The new name
 * @param  bool   $debug   Dump the query
 * @return bool   FALSE on error
 * @since  4.6.0
 */

function safe_rename($table, $newname, $debug = false)
{
    return (bool) safe_query("RENAME TABLE ".safe_pfx($table)." TO ".safe_pfx($newname), $debug);
}

/**
 * Gets a field from a row.
 *
 * If the query results in multiple matches, the first row returned is used.
 *
 * @param  string $thing The field
 * @param  string $table The table
 * @param  string $where The where clause
 * @param  bool   $debug Dump query
 * @return mixed  The field or FALSE on error
 * @example
 * if ($field = safe_field("column", 'table', "1 = 1"))
 * {
 *     echo $field;
 * }
 */

function safe_field($thing, $table, $where, $debug = false)
{
    $q = "SELECT $thing FROM ".safe_pfx_j($table)." WHERE $where";
    $r = safe_query($q, $debug);

    if ($r && mysqli_num_rows($r) > 0) {
        $row = mysqli_fetch_row($r);
        mysqli_free_result($r);

        return $row[0];
    }

    return false;
}

/**
 * Gets a list of values from a table's column.
 *
 * @param  string $thing The column
 * @param  string $table The table
 * @param  string $where The where clause
 * @param  bool   $debug Dump query
 * @return array
 */

function safe_column($thing, $table, $where = '1', $debug = false)
{
    if (is_array($thing)) {
        list($index, $thing) = $thing + array(null, '*');
        $multiple = $thing === '*' || strpos($thing, ',') !== false;
        $things = $thing === '*' ? $thing : implode(',', do_list_unique($index.','.$thing));
    } else {
        $things = $thing;
    }

    $q = "SELECT $things FROM ".safe_pfx_j($table)." WHERE $where";
    $rs = getRows($q, $debug);

    if ($rs) {
        if (isset($index) && isset($rs[0][$index])) {
            $out = array_column($rs, $multiple ? null : $thing, $index);
        } elseif (isset($rs[0][$thing])) {
            $out = array_column($rs, $thing, $thing);
        } else {
            foreach ($rs as $a) {
                $v = array_shift($a);
                $out[$v] = $v;
            }
        }
    }

    return empty($out) ? array() : $out;
}

/**
 * Fetch a column as an numeric array.
 *
 * @param  string $thing The field
 * @param  string $table The table
 * @param  string $where The where clause
 * @param  bool   $debug Dump query
 * @return array  Numeric array of column values
 * @since  4.5.0
 */

function safe_column_num($thing, $table, $where, $debug = false)
{
    $q = "SELECT $thing FROM ".safe_pfx_j($table)." WHERE $where";
    $rs = getRows($q, $debug);
    if ($rs) {
        foreach ($rs as $a) {
            $v = array_shift($a);
            $out[] = $v;
        }

        return $out;
    }

    return array();
}

/**
 * Gets a row from a table as an associative array.
 *
 * @param  string $things The select clause
 * @param  string $table  The table
 * @param  string $where  The where clause
 * @param  bool   $debug  Dump query
 * @return array
 * @see    safe_rows()
 * @see    safe_rows_start()
 * @uses   getRow()
 * @example
 * if ($row = safe_row("column", 'table', "1 = 1"))
 * {
 *     echo $row['column'];
 * }
 */

function safe_row($things, $table, $where, $debug = false)
{
    $q = "SELECT $things FROM ".safe_pfx_j($table)." WHERE $where";
    $rs = getRow($q, $debug);

    if ($rs) {
        return $rs;
    }

    return array();
}

/**
 * Gets a list rows from a table as an associative array.
 *
 * When working with large result sets remember that this function, unlike
 * safe_rows_start(), loads results to memory all at once. To optimise
 * performance in such situations, use safe_rows_start() instead.
 *
 * @param  string $things The select clause
 * @param  string $table  The table
 * @param  string $where  The where clause
 * @param  bool   $debug  Dump query
 * @return array  Returns an empty array if no matches are found
 * @see    safe_row()
 * @see    safe_rows_start()
 * @uses   getRows()
 * @example
 * $rs = safe_rows("column", 'table', "1 = 1");
 * foreach ($rs as $row)
 * {
 *     echo $row['column'];
 * }
 */

function safe_rows($things, $table, $where, $debug = false)
{
    $q = "SELECT $things FROM ".safe_pfx_j($table)." WHERE $where";
    $rs = getRows($q, $debug);

    if ($rs) {
        return $rs;
    }

    return array();
}

/**
 * Selects rows from a table and returns result as a resource.
 *
 * @param  string        $things The select clause
 * @param  string        $table  The table
 * @param  string        $where  The where clause
 * @param  bool          $debug  Dump query
 * @return resource|bool A result resource or FALSE on error
 * @see    nextRow()
 * @see    numRows()
 * @example
 * if ($rs = safe_rows_start("column", 'table', "1 = 1"))
 * {
 *     while ($row = nextRow($rs))
 *     {
 *         echo $row['column'];
 *     }
 * }
 */

function safe_rows_start($things, $table, $where, $debug = false)
{
    $q = "SELECT $things FROM ".safe_pfx_j($table)." WHERE $where";

    return startRows($q, $debug);
}

/**
 * Counts number of rows in a table.
 *
 * @param  string   $table The table
 * @param  string   $where The where clause
 * @param  bool     $debug Dump query
 * @return int|bool Number of rows or FALSE on error
 * @example
 * if (($count = safe_count("table", "1 = 1")) !== false)
 * {
 *     echo "myTable contains {$count} rows.";
 * }
 */

function safe_count($table, $where, $debug = false)
{
    return getCount($table, $where, $debug);
}

/**
 * Shows information about a table.
 *
 * @param  string   $thing The information to show, e.g. "index", "columns"
 * @param  string   $table The table
 * @param  bool     $debug Dump query
 * @return array
 * @example
 * print_r(safe_show('columns', 'myTable'));
 */

function safe_show($thing, $table, $debug = false)
{
    $q = "SHOW $thing FROM ".safe_pfx($table)."";
    $rs = getRows($q, $debug);

    if ($rs) {
        return $rs;
    }

    return array();
}

/**
 * Gets a field from a row.
 *
 * This function offers an alternative short-hand syntax to safe_field().
 * Most notably, this internally manages value escaping.
 *
 * @param  string $col   The field to get
 * @param  string $table The table
 * @param  string $key   The searched field
 * @param  string $val   The searched value
 * @param  bool   $debug Dump query
 * @return mixed  The field or FALSE on error
 * @see    safe_field()
 * @example
 * echo fetch('name', 'myTable', 'id', 12);
 */

function fetch($col, $table, $key, $val, $debug = false)
{
    $key = doSlash($key);
    $val = (is_int($val)) ? $val : "'".doSlash($val)."'";
    $q = "SELECT $col FROM ".safe_pfx($table)." WHERE `$key` = $val LIMIT 1";

    if ($r = safe_query($q, $debug)) {
        if (mysqli_num_rows($r) > 0) {
            $row = mysqli_fetch_row($r);
            mysqli_free_result($r);

            return $row[0];
        }

        return '';
    }

    return false;
}

/**
 * Gets a row as an associative array.
 *
 * @param  string     $query The SQL statement to execute
 * @param  bool       $debug Dump query
 * @return array|bool The row's values or FALSE on error
 * @see    safe_row()
 */

function getRow($query, $debug = false)
{
    if ($r = safe_query($query, $debug)) {
        $row = (mysqli_num_rows($r) > 0) ? mysqli_fetch_assoc($r) : false;
        mysqli_free_result($r);

        return $row;
    }

    return false;
}

/**
 * Gets multiple rows as an associative array.
 *
 * If you need to run simple SELECT queries that select rows from a table,
 * please see safe_rows() and safe_rows_start() first.
 *
 * @param  string     $query The SQL statement to execute
 * @param  bool       $debug Dump query
 * @return array|bool The rows or FALSE on error
 * @see    safe_rows()
 * @example
 * if ($rs = getRows('SELECT * FROM table'))
 * {
 *     print_r($rs);
 * }
 */

function getRows($query, $debug = false)
{
    if ($r = safe_query($query, $debug)) {
        if (mysqli_num_rows($r) > 0) {
            while ($a = mysqli_fetch_assoc($r)) {
                $out[] = $a;
            }

            mysqli_free_result($r);

            return $out;
        }
    }

    return false;
}

/**
 * Executes an SQL statement and returns results.
 *
 * This function is identical to safe_query() apart from the missing
 * $unbuf argument.
 *
 * @param  string $query The SQL statement to execute
 * @param  bool   $debug Dump query
 * @return mixed
 * @see    safe_query()
 * @access private
 */

function startRows($query, $debug = false)
{
    return safe_query($query, $debug);
}

/**
 * Gets a next row as an associative array from a result resource.
 *
 * The function will free up memory reserved by the result resource if called
 * after the last row.
 *
 * @param   resource    $r The result resource
 * @return  array|bool  The row, or FALSE if there are no more rows
 * @see     safe_rows_start()
 * @example
 * if ($rs = safe_rows_start("column", 'table', "1 = 1"))
 * {
 *     while ($row = nextRow($rs))
 *     {
 *         echo $row['column'];
 *     }
 * }
 */

function nextRow($r)
{
    $row = mysqli_fetch_assoc($r);

    if ($row === false) {
        mysqli_free_result($r);
    }

    return $row;
}

/**
 * Gets the number of rows in a result resource.
 *
 * @param  resource $r The result resource
 * @return int|bool The number of rows or FALSE on error
 * @see    safe_rows_start()
 * @example
 * if ($rs = safe_rows_start("column", 'table', "1 = 1"))
 * {
 *     echo numRows($rs);
 * }
 */

function numRows($r)
{
    return mysqli_num_rows($r);
}

/**
 * Gets the contents of a single cell from a resource set.
 *
 * @param  string      $query The SQL statement to execute
 * @param  bool        $debug Dump query
 * @return string|bool The contents, empty if no results were found or FALSE on error
 */

function getThing($query, $debug = false)
{
    if ($r = safe_query($query, $debug)) {
        if (mysqli_num_rows($r) != 0) {
            $row = mysqli_fetch_row($r);
            mysqli_free_result($r);

            return $row[0];
        }

        return '';
    }

    return false;
}

/**
 * Return values of one column from multiple rows in a num indexed array.
 *
 * @param  string $query The SQL statement to execute
 * @param  bool   $debug Dump query
 * @return array
 */

function getThings($query, $debug = false)
{
    $rs = getRows($query, $debug);

    if ($rs) {
        foreach ($rs as $a) {
            $out[] = array_shift($a);
        }

        return $out;
    }

    return array();
}

/**
 * Counts number of rows in a table.
 *
 * This function is identical to safe_count().
 *
 * @param  array|string $table The table/thing to count
 * @param  string       $where The where clause
 * @param  bool         $debug Dump query
 * @return int|bool     Number of rows or FALSE on error
 * @access private
 * @see    safe_count()
 */

function getCount($table, $where, $debug = false, $pfx = true)
{
    if (is_array($table)) {
        //$thing is expected to be sanitized by the caller
        list($table, $thing) = $table + array(null, '*');
    } else {
        $thing = '*';
    }

    $table = $pfx ? safe_pfx_j($table) : $table;

    return getThing("SELECT COUNT($thing) FROM $table WHERE $where", $debug);
}

// -------------------------------------------------------------

// Output a nested array of categories.
function get_tree($atts = array(), $tbl = 'txp_category')
{
    static $cache = array(), $level = 0, $lastmod = -1, $lAtts = array(
        'categories'   => null,
        'exclude'      => '',
        'parent'       => '',
        'children'     => true,
        'sort'         => 'name ASC',
        'type'         => 'article',
        'where'        => '1',
        'limit'        => '',
        'offset'       => '',
        'flatten'      => true,
    );

    extract(array_intersect_key($atts, $lAtts) + $lAtts);

    if (isset($categories) && !($categories = do_list_unique($categories))) {
        return array();
    } elseif (!$categories) {
        $categories = array();
    }

    $level++;
    $catonly = $categories && !$parent;
    $roots = do_list_unique($parent) or $roots = $categories or $roots = array('root');
    $rooted = in_array('root', $roots);
    $multiple = count($roots) > 1;
    sort($roots);
    $root = implode(',', $roots);
    $children = $children === true ? PHP_INT_MAX : intval(is_numeric($children) ? $children : !empty($children));
    $exclude = $exclude ? ($exclude === true ? $roots : do_list_unique($exclude)) : array();

    $sql_query = "$where AND type = '".doSlash($type)."'".($sort ? ' order by '.sanitizeForSort($sort) : ($categories ? " order by FIELD(name, ".quote_list($categories, ',').")": ''));
    $sql_limit = $limit !== '' || $offset ? "LIMIT ".intval($offset).", ".($limit === '' || $limit === true ? PHP_INT_MAX : intval($limit)) : '';
    $sql_exclude = $exclude && $sql_limit ? " and name not in(".quote_list($exclude, ',').")" : '';

    $unix_ts = (int)strtotime(get_pref('lastmod'));

    if ($unix_ts > $lastmod) {
        $lastmod = $unix_ts;
        $cache = array();
    }

    $nocache = !$children || $sql_limit || $children == $level;
    $hash = txp_hash($nocache ? uniqid() : $sql_query);

    if (!isset($cache[$hash])) {
        $cache[$hash] = array('' => array());
    }

    !$rooted or $cache[$hash]['']['root'] = false;

    if (!isset($cache[$hash][$root]) || !$multiple && $root != 'root' && empty($cache[$hash][$root][$root])) {
        $cache[$hash][$root] = array();

        if (!$children || !$rooted || $categories) {
            $names = array_unique(array_merge($roots, $categories));

            if ($catonly) {
                $cats = safe_rows('id, name, parent, title, description', $tbl, "name IN (".quote_list($names, ',').") AND $sql_query");
            } elseif ($cats = safe_rows('id, name, parent, title, description, lft, rgt', $tbl, "name IN (".quote_list($names, ',').") AND $sql_query")) {
                $retrieved = empty($categories);
                $between = $beyond = array();

                foreach ($cats as $cat) {
                    extract($cat);
                    unset($cat['lft'], $cat['rgt']);
                    $sname = doSlash($name);

                    if (in_array($name, $roots)) {
                        $between[] = $children ? "lft>=$lft AND rgt<=$rgt" : "name='$sname' OR parent='$sname'";
                        $retrieved = $retrieved && $rgt - $lft == 1;
                    }
 
                    if (in_array($name, $categories)) {
                        $beyond[] = $children ? "lft<=$lft AND rgt>=$rgt" : "name='$sname'";
                    }
                }

                $bounds = ($between ? '('.implode(' OR ', $between).')' : '1').' AND '.($beyond ? '('.implode(' OR ', $beyond).')' : '1');
                $retrieved or $cats = safe_rows('id, name, parent, title, description', $tbl, "name != 'root' $sql_exclude AND $bounds AND $sql_query $sql_limit");
            }
        } else {
            $cats = safe_rows('id, name, parent, title, description', $tbl, "name != 'root' $sql_exclude AND $sql_query $sql_limit");
        }

        $cats or $cats = array();

        foreach ($cats as $cat) {
            extract($cat);
            $node = $children == $level ? $root : $name;

            if (!isset($cache[$hash][$node])) {
                $cache[$hash][$node] = array();
            }

            $cache[$hash][$node][$name] = $cat;

            if ($children != $level) {
                if ($multiple && in_array($name, $roots)) {
                    $cache[$hash][$root][$name] = $cat;
                }

                if (!isset($cache[$hash][$parent])) {
                    $cache[$hash][$parent] = array();
                }

                $cache[$hash][''][$name] = false;
                $cache[$hash][$parent][$name] = $cat;
                isset($cache[$hash][''][$parent]) or $cache[$hash][''][$parent] = true;

                if ($multiple && in_array($parent, $roots)) {
                    $cache[$hash][$root][$name] = $cat;
                }
            }
        }

        $cache[$hash][''] = array_filter($cache[$hash]['']);
    }
/*
    if (isset($atts['limit']) && is_numeric($atts['limit'])) {
        $count = count($cache[$hash]['']);
        $atts['limit'] = $limit = $limit - $count;
    }
*/
    $out = array();

    foreach ($cache[$hash][$root] as $name => $cat) {
        if (!in_array($name, $exclude) && (!$categories || !$catonly || in_array($name, $categories))
            && ($level > 1 || $children <= $level || $rooted || in_array($name, $roots) || in_array($cat['parent'], $exclude))
        ) {
            $out[$name] = $cat;
            $out[$name]['level'] = $level - 1;

            if (isset($cache[$hash][$name]) && $children > $level && count($cache[$hash][$name]) > 1 && //(int)$limit > 0 &&
                $nodes = get_tree(array(
                    'parent'  => $name,
                    'exclude' => array_merge($exclude, array($name))
                ) + $atts, $tbl))
            {
                if ($flatten) {
                    $out[$name]['children'] = count($nodes);
                    $out += $nodes;
                } else {
                    $out[$name]['children'] = $nodes;
                }
            }
        }
    }

    $level--;

    if ($nocache) {
        unset($cache[$hash]);
    } elseif ($level <= 0) {
        foreach ($cache[$hash][''] as $parent => $delete) {
            unset($cache[$hash][$parent]);
        }
    }

    return $out;//array_slice($out, 0, (int)$limit);
}

/**
 * Gets a tree structure.
 *
 * This function is used by categories.
 *
 * @param  string $root  The roots
 * @param  string $type  The type
 * @param  string $where The where clause
 * @param  string $tbl   The table
 * @return array
 */

function getTree($root = 'root', $type = 'article', $where = "1", $tbl = 'txp_category', $depth = true)
{
    if (!$depth) {
        $roots = array('categories' => $root);
    } else {
        $roots = array('parent' => $root);
        $depth === true or $levels = array_map('intval', do_list($depth, array(',', '-')));
    }

    $names = is_array($root);
    $rows = get_tree($roots + compact('type', 'where') + array('children' => !empty($depth)), $tbl);
    $out = array();

    foreach ($rows as $name => $row) {
        if (!isset($levels) || in_array($row['level'], $levels)) {
            if ($names) {
                $out[$row['id']] = $name;
            } else {
                $out[] = $row;
            }
        }
    }

    return $out;
}

/**
 * Gets a target/path/up/to/root array.
 *
 * This function is used by categories.
 *
 * @param  string $target The target
 * @param  string $type   The category type
 * @param  string $tbl    The table
 * @param  string $root   The root (excluded)
 * @return array
 */

function getRootPath($target, $type = 'article', $tbl = 'txp_category', $root = 'root')
{
    static $cache = array(
        'article' => array(),
        'file'    => array(),
        'image'   => array(),
        'link'    => array()
    );
    $out = array();

    if (!isset($cache[$type])) {
        return $out;
    } elseif (empty($cache[$type])) {
        foreach (safe_rows('id, name, parent, title, description', $tbl, "type = '".doSlash($type)."'") as $row) {
            $cache[$type][$row['name']] = $row;
        }
    }

    while ($target !== $root && isset($cache[$type][$target])) {
        $out[] = $cache[$type][$target];
        $target = $cache[$type][$target]['parent'];
    }

    return $out;
}

/**
 * Gets a tree path up to a target.
 *
 * This function is used by categories.
 *
 * @param  string $target The target
 * @param  string $type   The category type
 * @param  string $tbl    The table
 * @return array
 */

function getTreePath($target, $type, $tbl = 'txp_category')
{
    $rs = safe_row(
        "lft AS l, rgt AS r",
        $tbl,
        "name = '".doSlash($target)."' AND type = '".doSlash($type)."'"
    );

    if (!$rs) {
        return array();
    }

    extract($rs);

    $rs = safe_rows_start(
        "*",
        $tbl,
        "lft <= $l AND rgt >= $r AND type = '".doSlash($type)."' ORDER BY lft ASC"
    );

    $out = array();
    $right = array();

    while ($rs and $row = nextRow($rs)) {
        extract($row);

        while (count($right) > 0 && $right[count($right) - 1] < $rgt) {
            array_pop($right);
        }

        $out[] = array(
            'id' => $id,
            'name' => $name,
            'title' => $title,
            'description' => $description,
            'type' => $type,
            'level' => count($right),
            'children' => ($rgt - $lft - 1) / 2,
        );

        $right[] = $rgt;
    }

    return $out;
}

/**
 * Rebuilds a nested tree set.
 *
 * This function is used by categories.
 *
 * @param  string $parent The parent
 * @param  string $left   The left ID
 * @param  string $type   The category type
 * @param  string $tbl    The table
 * @return int The next left ID
 */

function rebuild_tree($parent, $left, $type, $tbl = 'txp_category')
{
    $left = intval($left);
    $right = $left + 1;

    $parents[] = $parent;
    $parent = doSlash($parent);
    $stype = doSlash($type);

    $result = safe_column(
        "name",
        $tbl,
        "parent = '$parent' AND type = '$stype' ORDER BY name"
    );

    foreach ($result as $row) {
        $right = rebuild_tree($row, $right, $type, $tbl);
    }

    safe_update(
        $tbl,
        "lft = $left, rgt = $right",
        "name = '$parent' AND type = '$stype'"
    );

    return $right + 1;
}

/**
 * Rebuilds a tree.
 *
 * This function is used by categories.
 *
 * @param  string $type   The category type
 * @param  string $tbl    The table
 * @return int The next left ID
 */

function rebuild_tree_full($type, $tbl = 'txp_category')
{
    $stype = doSlash($type);
    // Fix circular references, otherwise rebuild_tree() could get stuck in a loop.
    safe_upsert($tbl, "parent = ''", array('type' => $stype, 'name' => 'root'));
    safe_update($tbl, "lft = 0, rgt = 0", "type = '".$stype."'");
    rebuild_tree('root', 1, $type, $tbl);

    // Attach lost nodes to root
    if (safe_count($tbl, "type = '".$stype."' AND rgt = 0")) {
        safe_update($tbl, "parent = 'root'", "type = '".$stype."' AND rgt = 0");
        rebuild_tree('root', 1, $type, $tbl);
    }
}

/**
 * Inserts categories.
 *
 * This function is used by categories.
 *
 * @param  array $data   The category data
 * @param  string $type  The category type
 * @param  string $tbl   The table
 * @return bool
 */

function insert_nodes($id = null, $data = array(), $type = 'article', $tbl = 'txp_category')
{
    extract(doSlash($data));
    !empty($parent) or $parent = 'root';
    $type = doSlash($type);
    //$res = safe_row("lft AS newlft, rgt AS newrgt, name = '$parent' AS first", $tbl, "type = '$type' AND ((parent = '$parent' AND name < '$name') OR name = '$parent') ORDER BY lft DESC");

    if (empty($at) && !($at = safe_field("rgt", $tbl, "type = '$type' AND name = '$parent'"))) {
        return false;
    }

//    extract($res);
//    $at = ($first ? $newlft : $newrgt) + 1;

    if (isset($id)) {// existing node
        $ids = array_filter(array_map('intval', do_list($id)));
        $res = !empty($ids);
//        $ids and safe_update('txp_category', "parent = '$parent'", "id IN (".implode(',', $ids).") AND type = '$type'");
        foreach($ids as $id) {
            if ($row = safe_row('id, lft, rgt', $tbl, "id = $id")) {
                extract($row);
                $width = $rgt - $lft + 1;

                if ($at < $lft) {
                    $offset = $at - $lft;
                    safe_update($tbl,
                        "lft = lft + $width*(lft<$lft AND lft>=$at) + $offset*(lft BETWEEN $lft AND $rgt),
                        rgt = rgt + $width*(rgt<$lft AND rgt>=$at) + $offset*(rgt BETWEEN $lft AND $rgt)",
                        "type = '$type' AND rgt >= $at AND lft <= $rgt"
                    );
                    $at += $width;
                } elseif ($at > $rgt) {
                    $offset = $at - $rgt - 1;
                    safe_update($tbl,
                        "lft = lft - $width*(lft>$rgt AND lft<$at) + $offset*(lft BETWEEN $lft AND $rgt),
                        rgt = rgt - $width*(rgt>$rgt AND rgt<$at) + $offset*(rgt BETWEEN $lft AND $rgt)",
                        "type = '$type' AND rgt >= $lft AND lft < $at"
                    );
                }
            }
        }
    } else {// new node
        safe_update($tbl, "lft = lft+2*(lft>=$at), rgt = rgt+2", "type = '$type' AND rgt >= $at");
        $res = safe_insert($tbl, "title = '$title', lft = $at, rgt = $at+1, type = '$type', name = '$name', parent = '$parent'");
    }

    return !empty($res);
}

/**
 * Deletes categories.
 *
 * This function is used by categories.
 *
 * @param  array $ids    The IDs
 * @param  string $type  The type
 * @param  string $tbl   The table
 * @return array The deleted IDs
 */
/*
function deleteNodes($ids, $type = 'article', $tbl = 'txp_category')
{
    $type = doSlash($type);
    $ids = implode(',', array_filter(array_map('intval', do_list($ids))));
    $deleted = array();
    $rows = safe_rows('id, name, parent, lft, rgt', $tbl, "id IN ($ids) ORDER BY lft DESC");

    if ($n = count($rows) && safe_delete($tbl, "id IN ($ids)")) {
        for ($i = 0; $i < $n; $i++) {
            extract(doSlash($rows[$i]));
            $deleted[] = $id;

            safe_update($tbl,
                "parent = IF(parent = '$name', '$parent', parent),
                lft = lft - (lft > $lft) - (lft > $rgt),
                rgt = rgt - 1 - (rgt > $rgt)",
                "type = '$type' AND rgt > $lft"
            );

            for ($j = $i+1; $j < $n ; $j++) {
                if ($rows[$j]['rgt'] > $rgt) {
                    $rows[$j]['rgt'] -= 2;
                }
            }
        }
    }

    return $deleted;
}
*/
function delete_nodes($ids, $type = 'article', $tbl = 'txp_category')
{
    $deleted = array();
    $type = doSlash($type);
    $ids = implode(',', array_filter(array_map('intval', do_list($ids))));
    $rows = safe_rows('id, name, parent, lft, rgt', $tbl, "id IN ($ids) ORDER BY lft");// parent first

    if ($rows && safe_delete($tbl, "id IN ($ids)")) {
        $parents = array_column($rows, 'parent', 'name');

        foreach ($rows as $row) {
            extract($row);
            $deleted[$name] = $id;

            if ($rgt - $lft > 1) {
                isset($parents[$parent]) and $parents[$name] = $parents[$parent];
            } else {
                unset($parents[$name]);
            }
        }

        if ($parents) {
            $names = quote_list(array_keys($parents), ',');
            $parents = quote_list($parents, ',');
        }

        $intervals = array_merge(array_column($rows, 'lft'), array_column($rows, 'rgt'));
        sort($intervals);
        $min = $intervals[0];
        $intervals = implode(',', $intervals);

        safe_update($tbl,
            ($parents ? "parent = ELT(1+FIELD(parent, $names), parent, $parents), " : '').
            "lft = lft - INTERVAL(lft, $intervals), rgt = rgt - INTERVAL(rgt, $intervals)",
            "type = '$type' AND rgt > $min"
        );
    }

    return $deleted;
}

/**
 * Returns an error page.
 *
 * This function is used to return a bailout page when resolving database
 * connections fails. Sends a HTTP 503 error status and displays the last logged
 * MySQL error message.
 *
 * @return string HTML5 document
 * @access private
 */

function db_down()
{
    global $DB;
    // 503 status might discourage search engines from indexing or caching the
    // error message.
    txp_status_header('503 Service Unavailable');
    if (is_object($DB)) {
        $error = txpspecialchars(mysqli_error($DB->link));
    } else {
        $error = '$DB object is not available.';
    }

    return <<<eod
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="robots" content="noindex">
    <title>Database unavailable</title>
</head>
<body>
    <p>Database unavailable.</p>
    <!-- $error -->
</body>
</html>
eod;
}

/**
 * Replacement for SQL NOW()
 *
 * This function can be used when constructing SQL SELECT queries as a
 * replacement for the NOW() function to allow the SQL server to cache the
 * queries. Should only be used when comparing with the Posted or Expired
 * columns from the textpattern (articles) table, the Created column from
 * the txp_file table or the Date column from the txp_link table.
 *
 * @param  string $type   Column name, lower case (one of 'posted', 'expires', 'created', 'date')
 * @param  bool   $update Force update
 * @return string SQL query string partial
 */

function now($type, $update = false)
{
    static $nows = array();
    static $time = null;

    if (!in_array($type = strtolower($type), array('posted', 'expires', 'created', 'date'))) {
        return false;
    }

    if (isset($nows[$type])) {
        $now = $nows[$type];
    } else {
        if ($time === null) {
            $time = time();
        }

        $pref = 'sql_now_'.$type;
        $now = get_pref($pref, $time - 1);

        if ($time > $now or $update) {
            $table = ($type === 'date') ? 'txp_link' : (($type === 'created') ? 'txp_file' : 'textpattern');
            $where = '1=1 having utime > '.$time.' order by utime asc limit 1';
            $now = safe_field('TIMESTAMPDIFF(SECOND, FROM_UNIXTIME(0), '.$type.') as utime', $table, $where);
            $now = ($now === false) ? 2147483647 : intval($now) - 1;
            update_pref($pref, $now);
            $nows[$type] = $now;
        }
    }

    return "(FROM_UNIXTIME(0) + INTERVAL $now SECOND)";
}
