<?php

namespace classes;

use Exception;
use mysqli;
use mysqli_sql_exception;

class MysqliVeu extends mysqli
{
    private array $dataArray = [];
    private int $lastInsertedId;

    /**
     * Class constructor initializing a database connection with the specified parameters.
     *
     * @param string $host The database host address.
     * @param string $username The username used for database connection.
     * @param string $password The password used for database connection.
     * @param string $database The name of the database to connect to.
     * @param int $dbPort The port number for the database connection.
     * @param int $connectionTimeout The timeout duration for the database connection.
     * @param string $charset The character set to use for the database connection.
     * @return void
     * @throws Exception If the database credentials are missing or the connection fails.
     */
    public function __construct(
        string $host = '',
        string $username = '',
        string $password = '',
        string $database = '',
        int    $dbPort = 3306,
        int    $connectionTimeout = 10,
        string $charset = 'utf8mb4'
    )
    {
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

        if (empty($host))
        {
            if (isset($_ENV['database']))
            {
                $host = $_ENV['database']['DB_HOST'] ?? '';
                $username = $_ENV['database']['DB_USERNAME'] ?? '';
                $password = $_ENV['database']['DB_PASSWORD'] ?? '';
                $database = $_ENV['database']['DB_DATABASE'] ?? '';
                $dbPort = $_ENV['database']['DB_PORT'];
            }
        }

        if (empty($host) || empty($username))
        {
            throw new Exception('Database credentials are required.');
        }

        try
        {
            parent::__construct($host, $username, $password, $database, $dbPort);
            $this->set_charset($charset);
        }
        catch (mysqli_sql_exception $error)
        {
            // throw new Exception('Database connection failed: ' . $error->getMessage(), (int) $error->getCode(), $error);
            throw new Exception('Database connection failed');
        }
        $this->options(MYSQLI_OPT_CONNECT_TIMEOUT, $connectionTimeout);
    }

    /**
     * Retrieves the column names from a specified table.
     *
     * @param string $table The name of the table from which to retrieve column names.
     * @return array An array of column names from the specified table.
     * @throws Exception
     */
    public function getTableColumns(string $table): array
    {
        $result = $this->query("SHOW COLUMNS FROM $table");
        $columns = [];
        while ($row = $result->fetch_assoc())
        {
            $columns[] = $row['Field'];
        }
        return $columns;
    }


    /**
     * Executes a SQL query and fetches all resulting rows as an associative array.
     *
     * @param string $sql The SQL query to be executed.
     * @return array An array of associative arrays representing the fetched rows.
     * @throws Exception If the query execution fails.
     */
    public function fetchArray(string $sql): array
    {
        $data = [];
        $result = $this->query($sql);
        while ($row = $result->fetch_assoc())
        {
            $data[] = $row;
        }
        return $data;
    }

    /**
     * Executes a SQL query and fetches a single resulting row as an associative array.
     *
     * @param string $sql The SQL query to be executed.
     * @return array an associative array representing the fetched row.
     * @throws Exception If the query execution fails.
     */
    public function fetchSingleRow(string $sql): array
    {
        $result = $this->query($sql);
        return $result->fetch_assoc();
    }

    /**
     * Executes a parameterized SQL query and retrieves a single row of results.
     *
     * @param string $sql The SQL query to be executed.
     * @param array $params An associative array where the keys are parameter placeholders in the SQL query and the values are the corresponding parameter values.
     * @return array An associative array representing the fetched row from the query result.
     */
    public function queryParamsRow(string $sql, array $params): array
    {
        $stmt = $this->prepare($sql);
        $paramsTypesString = $this->setParamsTypesString($params);
        $stmt->bind_param($paramsTypesString, ...array_values($params));
        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_assoc() ?? [];
    }


    /**
     * Sets the parameter types based on the provided parameters and types arrays.
     *
     * @param array $params An array of parameters to determine their types.
     * @param array $types An optional array of types corresponding to the parameters.
     * @return string A string
     */
    private function setParamsTypesString(array $params, array $types = []): string
    {
        $typeString = '';

        for ($i = 0; $i < count($params); $i++)
        {
            if (empty($types[$i]))
            {
                if (is_integer($params[$i]))
                {
                    $typeString .= 'i';
                }
                elseif (is_float($params[$i]))
                {
                    $typeString .= 'd';
                }
                else
                {
                    $typeString .= 's';
                }
            }
            else
            {
                $typeString .= $types[$i];
            }
        }

        return $typeString;
    }


    /**
     * Executes a prepared SQL statement with bound parameters.
     *
     * @param string $sql The SQL statement to be executed.
     * @param array $params An associative array of parameters to bind to the SQL statement.
     * @return bool True on success, False on failure.
     * @throws Exception
     */
    public function execute(string $sql, array $params): bool
    {
        $data = [];
        $stmt = $this->prepare($sql);
        $paramsTypesString = $this->setParamsTypesString($params);
        $stmt->bind_param($paramsTypesString, ...array_values($params));
        $executeStatus = $stmt->execute();
        $this->lastInsertedId = $stmt->insert_id;
        return $executeStatus;
    }


    /**
     * Retrieves the last inserted ID.
     *
     * @return int The integer representing the last inserted ID.
     */
    public function getLastInsertedId(): int
    {
        return $this->lastInsertedId;
    }


    /**
     * Prepares the query parameters for an SQL statement.
     *
     * @param string $sql The SQL query string.
     * @param array $params The parameters to be used in the SQL query.
     * @return array An array containing the prepared query parameters.
     */
    public function queryParamsArray(string $sql, array $params): array
    {
        $data = [];
        $stmt = $this->prepare($sql);
        $paramsTypesString = $this->setParamsTypesString($params);
        $stmt->bind_param($paramsTypesString, ...array_values($params));
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = $result->fetch_assoc())
        {
            $data[] = $row;
        }
        return $data;
    }


    /**
     * Prepares the query parameters for an SQL statement.
     *
     * @param string $sql The SQL query string.
     * @param array $params The parameters to be used in the SQL query.
     * @return int An array containing the prepared query parameters.
     * @throws Exception
     */
    public function numRows(string $sql, array $params = []): int
    {
        $stmt = $this->prepare($sql);

        if (!$stmt)
        {
            throw new Exception('FAIL-Error preparing SQL statement: ' . $this->error);
        }

        if (!empty($params))
        {
            $paramsTypesString = $this->setParamsTypesString($params);
            $stmt->bind_param($paramsTypesString, ...array_values($params));
        }

        if (!$stmt->execute())
        {
            throw new Exception('FAIL-Error executing SQL statement: ' . $stmt->error);
        }

        $result = $stmt->get_result();

        return $result->num_rows;
    }


    /**
     * Executes a parameterized SQL query and returns the results in JSON format.
     *
     * @param string $sql The SQL query string to be executed.
     * @param array|string $params The parameters to bind to the SQL query, either as an array or a comma-separated string.
     * @param array $types Optional. An array specifying the types of the parameters for binding.
     * @return string A JSON-encoded string containing the query results and a status message.
     * @throws Exception If there is an error preparing or executing the SQL statement, or if there is an issue with parameter binding.
     */
    public function queryParamsJson(string $sql, $params = [], array $types = [] ): string
    {
        $data = array();
        $message = '';

        // Get Query type from sql string
        $qryType = $this->getQueryType($sql);

        // Check if Params are a string
        if (is_string($params))
        {
            $paramArray = $this->paramToArray($params);
            $params = array_merge([], $paramArray);
        }

        // If input value is an associative array It's assumed to be a named list
        $status = $this->isAssociativeArray($params);
        if ($status)
        {
            $resultArray = $this->mapNames($sql, $params);
            if ($resultArray['status'])
            {
                $sql = $resultArray['sql'];
                $params = $resultArray['values'];
            }
        }

        // If INSERT build ? string if required
        if ($qryType == 'INSERT')
        {
            if (stripos($sql, 'value') === false)
            {
                $values = '';
                for ($i = 0; $i < count($params); $i++)
                {
                    if (!empty($values))
                    {
                        $values .= ',';
                    }
                    $values .= '?';
                }
                $sql .= ' VALUES(' . $values . ')';
            }
        }

        // If SELECT qry check for LIMIT value in sql string
        if ($qryType == 'SELECT')
        {
            // Add 1 to the passed limit, in order to check for EOF.
            $limit = null;
            $pattern = '/(?:LIMIT|limit)\s+`?(\w+)`?/i';
            if (preg_match($pattern, $sql, $matches))
            {
                $limit = (int) $matches[1];
                $new_limit = $limit + 1;
                // Replace the original LIMIT value with the new one
                $sql = preg_replace($pattern, 'LIMIT ' . $new_limit, $sql);
            }
        }

        // Prepares the SQL query
        $stmt = $this->prepare($sql);
        if (!$stmt)
        {
            throw new Exception('FAIL-Error preparing SQL statement: ' . $this->error);
        }

        // Gets Table name from sql string
        $table = $this->getTableName($sql);

        // Checks Parameter count against placeholders
        if (!empty($params))
        {
            $return = $this->paramCount($sql, count($params));
            if (!$return)
            {
                throw new Exception('FAIL-Incorrect values');
            }
        }
        // Sets Parameter types
        if (!empty($params))
        {
            $typeString = $this->setParamsTypesString($params, $types);

        // Binds the parameters
            $stmt->bind_param($typeString, ...$params);
        }

        // Executes the statement and check for errors
        if (!$stmt->execute())
        {
            throw new Exception('FAIL-Error executing SQL statement: ' . $stmt->error);
        }

        if ($qryType == 'SELECT')
        {
            // SELECT Results
            $result = $stmt->get_result();

            if ($result->num_rows > 0)
            {
                $numRows = 0;
                $i = 0;
                while ($row = $result->fetch_assoc())
                {
                    $i++;
                    if (empty($limit) or $i <= $limit)
                    {
                        $data[] = $row;
                        $numRows ++;
                    }
                }
                if (empty($limit) || mysqli_num_rows($result) < ($limit + 1))
                {
                    $message = 'End of Table';
                }
            }
            else
            {
                $message = 'No results found';
            }

            if (empty($message))
            {
                $message = 'OK  -' . $table;
            }
            if (empty($numRows))
            {
                $numRows = $result->num_rows;
            }
        }

        elseif ($qryType == "UPDATE")
        {
            $message = "OK  -".$this->affected_rows;
            $numRows = $this->affected_rows;
        }
        
        elseif ($qryType == "INSERT")
        {
            $message = "OK  -".$this->insert_id;
            $numRows = $this->insert_id;
        }
                
        elseif ($qryType == "DELETE")
        {
            $message = "OK  -".$this->affected_rows;
            $numRows = $this->affected_rows;
        }
        else
        {
            $message = "FAIL-Unknown QUERY ".$sql;
        }
// Return the values
        $output = array(
        'data'              =>  $data,
        'numrows'           =>  $numRows,
        'message'           =>  $message
        );  
        return json_encode($output);
    }

    /**
     * Determines the type of SQL query based on the first word of the query string.
     *
     * @param string $sql The SQL query string.
     * @return string The type of the query (e.g., SELECT, INSERT, UPDATE, DELETE, or UNKNOWN).
     */
    private function getQueryType(string $sql): string
    {
        $sql = trim($sql);
		$i = strpos($sql,' ');
		if ($i !== false)
		{
			$firstWord = strtoupper(substr($sql, 0, $i));
			if (strtoupper($firstWord) == "SHOW")
			{
				$firstWord = "SELECT";
			}
			switch ($firstWord)
			{
				case 'SELECT':
				case 'INSERT':
				case 'UPDATE':
				case 'DELETE':
					return $firstWord;
				default:
					return 'UNKNOWN';
			}
		}
		else
		{
			return 'UNKNOWN';
		}
    }

    /**
     * Converts a parameter string into an associative array or a simple array.
     *
     * @param string $params The parameter string to be converted.
     * @return array An associative array if the parameter string contains colon-separated key-value pairs; otherwise, a simple array.
     */
    private function paramToArray(string $params): array
    {
        $paramArray = [];
        if (!empty($params))
        {
            // Tests if param string contains ":"
            if (strpos($params, ':') !== false)
            {
                $pairs = explode(',', $params);
                foreach ($pairs as $pair)
                {
                    list($key, $value) = explode(':', $pair, 2);
                    $key = trim($key);
                    $value = trim($value, "'");
                    $paramArray[$key] = $value;
                }
            }
            else
            {
                $paramArray = explode(',', $params);
            }
        }
        return $paramArray;
    }


    /**
     * Determines if an array is an associative array.
     *
     * @param array $array The array to check.
     * @return bool True if the array is associative, false otherwise.
     */
    private function isAssociativeArray(array $array): bool
    {
        if (!empty($array))
        {
            // Check if any of the keys are not integers
            foreach (array_keys($array) as $key)
            {
                if (!is_int($key))
                {
                    return true;
                }
            }
        }
        return false;
    }


    /**
     * Maps named placeholders in a SQL query to positional placeholders and prepares corresponding binding values.
     *
     * @param string $sql The SQL query containing named placeholders.
     * @param array $values An associative array of values where keys correspond to placeholder names in the SQL query.
     * @return array An array containing the modified SQL query, the binding values, and the status of the operation.
     * @throws Exception
     */
    private function mapNames(string $sql, array $values): array
    {
        // Create an indexed array for binding
        $bindValues = [];

        // Replace named placeholders with ? and prepare binding values
        $sql = preg_replace_callback('/:(\w+)/', function ($matches) use ($values, &$bindValues)
        {
            $key = $matches[1];
            if (array_key_exists($key, $values))
            {
                $bindValues[] = $values[$key]; // Add the value to the binding array
                return '?'; // Replace with ?
            }
            return $matches[0];

        }, $sql);
        if (count($bindValues) > 0)
        {
            $status = true;
        }
        else
        {
            $status = false;
        }
        return [
            'sql' => $sql,
            'values' => $bindValues,
            'status' => $status
        ];
    }


    /**
     * Extracts the table name from a given SQL query.
     *
     * @param string $sql The SQL query string from which to extract the table name.
     * @return string|null The table name if found, or null if not found.
     */
    private function getTableName(string $sql): ?string
    {
        $pattern = '/(?:FROM|INTO|UPDATE|DELETE)\s+`?(\w+)`?/i';
        if (preg_match($pattern, $sql, $matches))
        {
            return $matches[1];
        }
        return null;
    }


    /**
     * Compares the number of placeholders in an SQL statement to the provided parameter count.
     *
     * @param string $sql The SQL statement containing placeholders.
     * @param int $paramCount The expected number of parameters.
     * @return bool True if the number of placeholders matches the parameter count, false otherwise.
     */
    private function paramCount(string $sql, int $paramCount): bool
    {
        $count = substr_count($sql, '?');
        if (empty($count))
        {
            $count = substr_count($sql, ':');
        }
        return ($count === $paramCount);
    }


    /**
     * Retrieves the data array.
     *
     * @return array The array containing the data.
     */
    public function getDataArray(): array
    {
        return $this->dataArray;
    }


    /**
     * Destructor method to clean up resources.
     *
     * Closes any open connections or resources when the object is destroyed.
     *
     * @return void
     */
    public function __destruct()
    {
        $this->close();
    }


    /**
     * Extracts the LIMIT value from an SQL query string.
     *
     * @param string $sql The SQL query string from which to extract the LIMIT clause.
     * @return int|null The LIMIT value as an integer if present, otherwise null.
     */
    private function getLimit(string $sql): ?int
    {
        $pattern = '/(?:LIMIT|limit)\s+`?(\w+)`?/i';
        if (preg_match($pattern, $sql, $matches))
        {
            return (int) $matches[1];
        }
        else
        {
            return null;
        }
    }
}
