<?php
/**
 * Class DB
 * Simple MySQL database handler using mysqli.
 * 
 * Now supports:
 *  1. Passing a PHP array as a value to insert()/insert_safe() → it will be json_encode()'d & escaped.
 *  2. In update(), if a key is "columnName->$.some.json.path", then:
 *       - If its value is NULL, we perform JSON_REMOVE(columnName, '$.some.json.path').
 *       - Otherwise, we perform JSON_SET(columnName, '$.some.json.path', <your JSON literal>).
 *  3. All original methods (insert, update, delete, select, etc.) keep their exact signatures.
 */
class DB {
    private $dbConnect;
    private $dbserver;
    private $dbuse;
    private $dbpass;
    private $dbname;
    private $dbSelected;
    private $query;
    private $tableName;
    private $value;
    private $where;
    private $limit;
    private $sqlSafe;

    /**
     * Connect to the MySQL database.
     *
     * @param string $db     Database name
     * @param string $server Hostname (default: "localhost")
     * @param string $user   Username (default: "root")
     * @param string $pass   Password (default: "")
     * @throws Exception     If connection or database selection fails
     */
    public function connect($db, $server = "localhost", $user = "root", $pass = "") {
        $this->dbserver = trim($server);
        $this->dbuse    = trim($user);
        $this->dbpass   = trim($pass);
        $this->dbname   = trim($db);

        $this->dbConnect = mysqli_connect($this->dbserver, $this->dbuse, $this->dbpass);

        if ($this->dbConnect) {
            $this->dbSelected = mysqli_select_db($this->dbConnect, $this->dbname);
            if (!$this->dbSelected) {
                throw new Exception($this->debug(mysqli_error($this->dbConnect)));
            }
            mysqli_set_charset($this->dbConnect, 'utf8');
        } else {
            throw new Exception($this->debug(mysqli_error($this->dbConnect)));
        }
    }

    /**
     * Perform a SELECT query and return the result as an array.
     *
     * @param string $query SQL SELECT query
     * @return array        Result rows
     * @throws Exception    If the query fails
     */
    public function select($query) {
        $output       = [];
        $this->query  = $query;
        $result       = mysqli_query($this->dbConnect, $this->query);
        if (!$result) {
            throw new Exception($this->debug(mysqli_error($this->dbConnect), $this->query));
        }

        while ($rows = mysqli_fetch_assoc($result)) {
            $output[] = $rows;
        }
        mysqli_free_result($result);
        return $output;
    }

    /**
     * Perform an UPDATE query.
     *
     * Now supports “columnName->$.json.path” syntax inside $values:
     *  - If you set ['json_col->$.user.name' => '"Alice"'], it becomes:
     *      json_col = JSON_SET(json_col, '$.user.name', "Alice")
     *  - If you set ['json_col->$.items[2]' => null], it becomes:
     *      json_col = JSON_REMOVE(json_col, '$.items[2]')
     *
     * @param string $table  Table name
     * @param array  $values Associative array of column => value
     *                        • If value is an actual PHP array → will be json_encode()'d & quoted.
     *                        • If key is 'col->json_path':
     *                            – value = NULL  → JSON_REMOVE(col, 'json_path')
     *                            – value = <JSON literal> → JSON_SET(col, 'json_path', <literal>)
     * @param string $where  WHERE clause (without the word "WHERE")
     * @param int    $limit  LIMIT value
     * @return bool          True on success
     * @throws Exception     If the query fails or $values is not an array
     */
    public function update($table, $values, $where = "1", $limit = 1) {
        $this->tableName = trim($table);
        $this->value     = $values;
        $this->where     = $where;
        $this->limit     = (int) $limit;

        if (!is_array($this->value)) {
            throw new Exception($this->debug('The Value that you are trying to deal with is not an array'));
        }

        $countParts = 0;
        $setClauses = [];

        foreach ($this->value as $rawKey => $rawVal) {
            // 1) If $rawVal is a PHP array, auto‐json_encode() & escape it.
            if (is_array($rawVal)) {
                $jsonString = json_encode($rawVal, JSON_UNESCAPED_UNICODE);
                if ($jsonString === false) {
                    throw new Exception($this->debug('Failed to json_encode the provided array for key: ' . $rawKey));
                }
                // Quote it for SQL:
                $escapedJson = $this->sqlSafe($jsonString);
                // Now treat it as though caller passed that quoted JSON string for the column name.
                $rawVal = $escapedJson;
            }

            // 2) Check if the key is a “column->json_path” pattern.
            if (strpos($rawKey, '->') !== false) {
                // Split into column and JSON path
                list($column, $jsonPath) = explode('->', $rawKey, 2);
                $column = trim($column);
                $jsonPath = trim($jsonPath);

                // If value is NULL → generate JSON_REMOVE
                if (is_null($rawVal)) {
                    $clause = sprintf(
                        "`%s` = JSON_REMOVE(`%s`, '%s')",
                        $column,
                        $column,
                        $jsonPath
                    );
                } else {
                    // Otherwise, rawVal must be a valid JSON literal, e.g. '"string"' or '123'
                    $clause = sprintf(
                        "`%s` = JSON_SET(`%s`, '%s', %s)",
                        $column,
                        $column,
                        $jsonPath,
                        $rawVal
                    );
                }
            } else {
                // 3) Normal column = value. Assume caller gives either:
                //      • a raw SQL fragment (e.g. "NOW()", "123", "'foo'")
                //      • or a quoted string (e.g. "'bar'")
                // Already handled the case where $rawVal was a PHP array.
                $column = trim($rawKey);
                $val    = $rawVal;
                $clause = sprintf("`%s` = %s", $column, $val);
            }

            $setClauses[] = $clause;
            $countParts++;
        }

        if ($countParts === 0) {
            throw new Exception($this->debug('No columns/values provided to update.'));
        }

        $this->query = 'UPDATE `' . $this->tableName . '` SET ' 
                     . implode(', ', $setClauses) 
                     . " WHERE $this->where LIMIT $this->limit";

        $result = mysqli_query($this->dbConnect, $this->query);
        if (!$result) {
            throw new Exception($this->debug(mysqli_error($this->dbConnect), $this->query));
        }
        return true;
    }

    /**
     * Insert data into a table.
     *
     * Now: if any $values[<col>] is a PHP array, it automatically becomes JSON.
     *
     * @param string $table  Table name
     * @param array  $values Associative array of column => value
     *                        • If value is a PHP array → json_encode() & escape.
     *                        • Otherwise, assume “value” is already either:
     *                            – a properly quoted string (e.g. "'Alice'") 
     *                            – or a raw SQL fragment (e.g. "NOW()", "123").
     * @return int           Insert ID
     * @throws Exception     If the query fails or $values is not an array
     */
    public function insert($table, $values) {
        $this->tableName = trim($table);
        $this->value     = $values;

        if (!is_array($this->value)) {
            throw new Exception($this->debug('The Value that you are trying to deal with is not an array'));
        }

        $fields       = [];
        $fieldsValues = [];

        foreach ($this->value as $rawKey => $rawVal) {
            $fields[]  = "`" . trim($rawKey) . "`";

            // If it’s a PHP array, json_encode & escape it:
            if (is_array($rawVal)) {
                $jsonString = json_encode($rawVal, JSON_UNESCAPED_UNICODE);
                if ($jsonString === false) {
                    throw new Exception($this->debug('Failed to json_encode the provided array for key: ' . $rawKey));
                }
                $escapedJson = $this->sqlSafe($jsonString);
                $fieldsValues[] = $escapedJson;
            } else {
                // Otherwise, assume caller already gave a properly quoted/escaped or raw SQL fragment:
                $fieldsValues[] = $rawVal;
            }
        }

        $this->query = sprintf(
            "INSERT INTO `%s` (%s) VALUES (%s)",
            $this->tableName,
            implode(", ", $fields),
            implode(", ", $fieldsValues)
        );

        $result = mysqli_query($this->dbConnect, $this->query);
        if (!$result) {
            throw new Exception($this->debug(mysqli_error($this->dbConnect), $this->query));
        }

        return mysqli_insert_id($this->dbConnect);
    }

    /**
     * Safe insert using escaped values.
     *
     * Now: if any $values[<col>] is a PHP array, it automatically becomes JSON.
     *
     * @param string $table  Table name
     * @param array  $values Associative array of column => value
     *                        • If value is a PHP array → json_encode() & escape.
     *                        • Otherwise, value is treated as an unescaped string and will be sqlSafe()’d.
     * @return int           Insert ID
     * @throws Exception     If the query fails
     */
    public function insert_safe($table, $values) {
        $this->tableName = trim($table);
        $this->value     = $values;

        if (!is_array($this->value)) {
            throw new Exception($this->debug('The Value that you are trying to deal with is not an array'));
        }

        $fields       = [];
        $fieldsValues = [];

        foreach ($this->value as $rawKey => $rawVal) {
            $fields[] = "`" . trim($rawKey) . "`";

            if (is_array($rawVal)) {
                // 1) PHP array → JSON string → escape & quote
                $jsonString = json_encode($rawVal, JSON_UNESCAPED_UNICODE);
                if ($jsonString === false) {
                    throw new Exception($this->debug('Failed to json_encode the provided array for key: ' . $rawKey));
                }
                $escapedJson = $this->sqlSafe($jsonString);
                $fieldsValues[] = $escapedJson;
            } else {
                // 2) Not an array → escape & quote normally
                $fieldsValues[] = $this->sqlSafe($rawVal);
            }
        }

        $this->query = sprintf(
            "INSERT INTO `%s` (%s) VALUES (%s)",
            $this->tableName,
            implode(", ", $fields),
            implode(", ", $fieldsValues)
        );

        $result = mysqli_query($this->dbConnect, $this->query);
        if (!$result) {
            throw new Exception($this->debug(mysqli_error($this->dbConnect), $this->query));
        }

        return mysqli_insert_id($this->dbConnect);
    }

    /**
     * Delete rows from a table.
     *
     * @param string $table Table name
     * @param string $where WHERE condition (without "WHERE")
     * @return bool         True if rows were affected
     * @throws Exception    If the query fails
     */
    public function delete($table, $where) {
        $this->tableName = trim($table);
        $this->where     = $where;
        $this->query     = sprintf("DELETE FROM `%s` WHERE %s", $this->tableName, $this->where);

        $result = mysqli_query($this->dbConnect, $this->query);
        if (!$result) {
            throw new Exception($this->debug(mysqli_error($this->dbConnect), $this->query));
        }

        return (mysqli_affected_rows($this->dbConnect) > 0);
    }

    /**
     * Run a general SQL query (no result returned).
     *
     * @param string $query SQL query
     * @return string       'done' on success
     * @throws Exception    If the query fails
     */
    public function dbquery($query) {
        $this->query = $query;
        $result      = mysqli_query($this->dbConnect, $this->query);
        if (!$result) {
            throw new Exception($this->debug(mysqli_error($this->dbConnect), $this->query));
        }
        return 'done';
    }

    /**
     * Run a general SQL query and return the raw mysqli result.
     *
     * @param string $query SQL query
     * @return mysqli_result Result set
     * @throws Exception     If the query fails
     */
    public function query($query) {
        $this->query = $query;
        $result      = mysqli_query($this->dbConnect, $this->query);
        if (!$result) {
            throw new Exception($this->debug(mysqli_error($this->dbConnect), $this->query));
        }
        return $result;
    }

    /**
     * Escape and quote a value safely for use in SQL.
     *
     * @param string $value Value to escape
     * @param string $quote Quote character to use (default: single quote)
     * @return string       Escaped and quoted value
     */
    public function sqlSafe($value, $quote = "'") {
        $value = str_replace(["\\'", "'"], "&#39;", $value);
        $value = stripslashes($value);
        $value = mysqli_real_escape_string($this->dbConnect, $value);
        return $quote . $value . $quote;
    }

    /**
     * Get all column names from a table.
     *
     * @param string $table Table name
     * @return array        Column names
     */
    public function getColumns($table) {
        $this->tableName = trim($table);
        $this->query     = "SHOW COLUMNS FROM `$this->tableName`";
        $result          = mysqli_query($this->dbConnect, $this->query)
                         or die($this->debug(mysqli_error($this->dbConnect), $this->query));

        $columns = [];
        while ($row = mysqli_fetch_assoc($result)) {
            $columns[] = $row['Field'];
        }

        mysqli_free_result($result);
        return $columns;
    }

    /**
     * Get all column names and types from a table.
     *
     * @param string $table Table name
     * @return array        Column name => type pairs
     */
    public function getColumnsType($table) {
        $this->tableName = trim($table);
        $this->query     = "SHOW COLUMNS FROM `$this->tableName`";
        $result          = mysqli_query($this->dbConnect, $this->query)
                         or die($this->debug(mysqli_error($this->dbConnect), $this->query));

        $columns = [];
        while ($row = mysqli_fetch_assoc($result)) {
            $columns[] = [ $row['Field'] => $row['Type'] ];
        }

        mysqli_free_result($result);
        return $columns;
    }

    /**
     * Close the database connection.
     */
    public function close() {
        mysqli_close($this->dbConnect);
    }

    /**
     * Internal function to format error/debug messages.
     *
     * @param string      $error Error message
     * @param string|null $query Optional query to include
     * @return string            Combined message
     */
    private function debug($error, $query = NULL) {
        $message = $error;
        if ($query) {
            $message .= "\nSQL: " . $query;
        }
        return $message;
    }
}
?>
