<?php

/**
 * Part of the Joomla Framework Database Package
 *
 * @copyright  Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved.
 * @license    GNU General Public License version 2 or later; see LICENSE
 */

namespace Joomla\Database\Sqlsrv;

use Joomla\Database\Exception\ExecutionFailureException;
use Joomla\Database\Exception\PrepareStatementFailureException;
use Joomla\Database\FetchMode;
use Joomla\Database\FetchOrientation;
use Joomla\Database\ParameterType;
use Joomla\Database\StatementInterface;

/**
 * SQL Server Database Statement.
 *
 * This class is modeled on \Doctrine\DBAL\Driver\SQLSrv\SQLSrvStatement
 *
 * @since  2.0.0
 */
class SqlsrvStatement implements StatementInterface
{
    /**
     * The database connection resource.
     *
     * @var    resource
     * @since  2.0.0
     */
    protected $connection;

    /**
     * The default fetch mode for the statement.
     *
     * @var    integer
     * @since  2.0.0
     */
    protected $defaultFetchStyle = FetchMode::MIXED;

    /**
     * The default class to use for building object result sets.
     *
     * @var    integer
     * @since  2.0.0
     */
    protected $defaultObjectClass = \stdClass::class;

    /**
     * Mapping array converting fetch modes to the native engine type.
     *
     * @var    array
     * @since  2.0.0
     */
    private $fetchMap = [
        FetchMode::MIXED       => SQLSRV_FETCH_BOTH,
        FetchMode::ASSOCIATIVE => SQLSRV_FETCH_ASSOC,
        FetchMode::NUMERIC     => SQLSRV_FETCH_NUMERIC,
    ];

    /**
     * The query string being prepared.
     *
     * @var    string
     * @since  2.0.0
     */
    protected $query;

    /**
     * Internal tracking flag to set whether there is a result set available for processing
     *
     * @var    boolean
     * @since  2.0.0
     */
    private $result = false;

    /**
     * The prepared statement.
     *
     * @var    resource
     * @since  2.0.0
     */
    protected $statement;

    /**
     * Bound parameter types.
     *
     * @var    array
     * @since  2.0.0
     */
    protected $typesKeyMapping;

    /**
     * References to the variables bound as statement parameters.
     *
     * @var    array
     * @since  2.0.0
     */
    private $bindedValues = [];

    /**
     * Mapping between named parameters and position in query.
     *
     * @var    array
     * @since  2.0.0
     */
    protected $parameterKeyMapping;

    /**
     * Mapping array for parameter types.
     *
     * @var    array
     * @since  2.0.0
     */
    protected $parameterTypeMapping = [
        ParameterType::BOOLEAN      => ParameterType::BOOLEAN,
        ParameterType::INTEGER      => ParameterType::INTEGER,
        ParameterType::LARGE_OBJECT => ParameterType::LARGE_OBJECT,
        ParameterType::NULL         => ParameterType::NULL,
        ParameterType::STRING       => ParameterType::STRING,
    ];

    /**
     * Constructor.
     *
     * @param   resource  $connection  The database connection resource
     * @param   string    $query       The query this statement will process
     *
     * @since   2.0.0
     * @throws  PrepareStatementFailureException
     */
    public function __construct($connection, string $query)
    {
        // Initial parameter types for prepared statements
        $this->parameterTypeMapping = [
            ParameterType::BOOLEAN      => SQLSRV_PHPTYPE_INT,
            ParameterType::INTEGER      => SQLSRV_PHPTYPE_INT,
            ParameterType::LARGE_OBJECT => SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),
            ParameterType::NULL         => SQLSRV_PHPTYPE_NULL,
            ParameterType::STRING       => SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR),
        ];

        $this->connection = $connection;
        $this->query      = $this->prepareParameterKeyMapping($query);
    }

    /**
     * Replace named parameters with numbered parameters
     *
     * @param   string  $sql  The SQL statement to prepare.
     *
     * @return  string  The processed SQL statement.
     *
     * @since   2.0.0
     */
    public function prepareParameterKeyMapping($sql)
    {
        $escaped    = false;
        $startPos   = 0;
        $quoteChar  = '';
        $literal    = '';
        $mapping    = [];
        $position   = 0;
        $matches    = [];
        $pattern    = '/([:][a-zA-Z0-9_]+)/';

        if (!preg_match($pattern, $sql, $matches)) {
            return $sql;
        }

        $sql = trim($sql);
        $n   = \strlen($sql);

        while ($startPos < $n) {
            if (!preg_match($pattern, $sql, $matches, 0, $startPos)) {
                break;
            }

            $j = strpos($sql, "'", $startPos);
            $k = strpos($sql, '"', $startPos);

            if (($k !== false) && (($k < $j) || ($j === false))) {
                $quoteChar = '"';
                $j         = $k;
            } else {
                $quoteChar = "'";
            }

            if ($j === false) {
                $j = $n;
            }

            // Search for named prepared parameters and replace it with ? and save its position
            $substring = substr($sql, $startPos, $j - $startPos);

            if (preg_match_all($pattern, $substring, $matches, PREG_PATTERN_ORDER + PREG_OFFSET_CAPTURE)) {
                foreach ($matches[0] as $i => $match) {
                    if ($i === 0) {
                        $literal .= substr($substring, 0, $match[1]);
                    }

                    if (!isset($mapping[$match[0]])) {
                        $mapping[$match[0]] = [];
                    }

                    $mapping[$match[0]][]   = $position++;
                    $endOfPlaceholder       = $match[1] + strlen($match[0]);
                    $beginOfNextPlaceholder = $matches[0][$i + 1][1] ?? strlen($substring);
                    $beginOfNextPlaceholder -= $endOfPlaceholder;
                    $literal                .= '?' . substr($substring, $endOfPlaceholder, $beginOfNextPlaceholder);
                }
            } else {
                $literal .= $substring;
            }

            $startPos = $j;
            $j++;

            if ($j >= $n) {
                break;
            }

            // Quote comes first, find end of quote
            while (true) {
                $k       = strpos($sql, $quoteChar, $j);
                $escaped = false;

                if ($k === false) {
                    break;
                }

                $l = $k - 1;

                while ($l >= 0 && $sql[$l] === '\\') {
                    $l--;
                    $escaped = !$escaped;
                }

                if ($escaped) {
                    $j = $k + 1;

                    continue;
                }

                break;
            }

            if ($k === false) {
                // Error in the query - no end quote; ignore it
                break;
            }

            $literal .= substr($sql, $startPos, $k - $startPos + 1);
            $startPos = $k + 1;
        }

        if ($startPos < $n) {
            $literal .= substr($sql, $startPos, $n - $startPos);
        }

        $this->parameterKeyMapping = $mapping;

        return $literal;
    }

    /**
     * Binds a parameter to the specified variable name.
     *
     * @param   string|integer  $parameter       Parameter identifier. For a prepared statement using named placeholders, this will be a parameter
     *                                           name of the form `:name`. For a prepared statement using question mark placeholders, this will be
     *                                           the 1-indexed position of the parameter.
     * @param   mixed           $variable        Name of the PHP variable to bind to the SQL statement parameter.
     * @param   string          $dataType        Constant corresponding to a SQL datatype, this should be the processed type from the QueryInterface.
     * @param   ?integer        $length          The length of the variable. Usually required for OUTPUT parameters.
     * @param   ?array          $driverOptions   Optional driver options to be used.
     *
     * @return  boolean
     *
     * @since   2.0.0
     */
    public function bindParam($parameter, &$variable, string $dataType = ParameterType::STRING, ?int $length = null, ?array $driverOptions = null)
    {
        $this->bindedValues[$parameter] =& $variable;

        // Validate parameter type
        if (!isset($this->parameterTypeMapping[$dataType])) {
            throw new \InvalidArgumentException(sprintf('Unsupported parameter type `%s`', $dataType));
        }

        $this->typesKeyMapping[$parameter] = $this->parameterTypeMapping[$dataType];

        $this->statement = null;

        return true;
    }

    /**
     * Binds a value to the specified variable.
     *
     * @param   string|integer  $parameter       Parameter identifier. For a prepared statement using named placeholders, this will be a parameter
     *                                           name of the form `:name`. For a prepared statement using question mark placeholders, this will be
     *                                           the 1-indexed position of the parameter.
     * @param   mixed           $variable        Name of the PHP variable to bind to the SQL statement parameter.
     * @param   string          $dataType        Constant corresponding to a SQL datatype, this should be the processed type from the QueryInterface.
     *
     * @return  void
     *
     * @since   2.0.0
     */
    private function bindValue($parameter, $variable, $dataType = ParameterType::STRING)
    {
        $this->bindedValues[$parameter]    = $variable;
        $this->typesKeyMapping[$parameter] = $dataType;
    }

    /**
     * Closes the cursor, enabling the statement to be executed again.
     *
     * @return  void
     *
     * @since   2.0.0
     */
    public function closeCursor(): void
    {
        if (!$this->result || !\is_resource($this->statement)) {
            return;
        }

        // Emulate freeing the result fetching and discarding rows, similarly to what PDO does in this case
        while (sqlsrv_fetch($this->statement)) {
            // Do nothing (see above)
        }

        $this->result = false;
    }

    /**
     * Fetches the SQLSTATE associated with the last operation on the statement handle.
     *
     * @return  string
     *
     * @since   2.0.0
     */
    public function errorCode()
    {
        $errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);

        if ($errors) {
            return $errors[0]['code'];
        }

        return false;
    }

    /**
     * Fetches extended error information associated with the last operation on the statement handle.
     *
     * @return  array
     *
     * @since   2.0.0
     */
    public function errorInfo()
    {
        return sqlsrv_errors(SQLSRV_ERR_ERRORS);
    }

    /**
     * Executes a prepared statement
     *
     * @param   array|null  $parameters  An array of values with as many elements as there are bound parameters in the SQL statement being executed.
     *
     * @return  boolean
     *
     * @since   2.0.0
     */
    public function execute(?array $parameters = null)
    {
        if (empty($this->bindedValues) && $parameters !== null) {
            $hasZeroIndex = array_key_exists(0, $parameters);

            foreach ($parameters as $key => $val) {
                $key = ($hasZeroIndex && is_numeric($key)) ? $key + 1 : $key;
                $this->bindValue($key, $val);
            }
        }

        if (!$this->statement) {
            $this->statement = $this->prepare();
        }

        if (!sqlsrv_execute($this->statement)) {
            $errors = $this->errorInfo();

            throw new ExecutionFailureException($this->query, $errors[0]['message'], $errors[0]['code']);
        }

        $this->result = true;

        return true;
    }

    /**
     * Fetches the next row from a result set
     *
     * @param   integer|null  $fetchStyle         Controls how the next row will be returned to the caller. This value must be one of the
     *                                            FetchMode constants, defaulting to value of FetchMode::MIXED.
     * @param   integer       $cursorOrientation  For a StatementInterface object representing a scrollable cursor, this value determines which row
     *                                            will be returned to the caller. This value must be one of the FetchOrientation constants,
     *                                            defaulting to FetchOrientation::NEXT.
     * @param   integer       $cursorOffset       For a StatementInterface object representing a scrollable cursor for which the cursorOrientation
     *                                            parameter is set to FetchOrientation::ABS, this value specifies the absolute number of the row in
     *                                            the result set that shall be fetched. For a StatementInterface object representing a scrollable
     *                                            cursor for which the cursorOrientation parameter is set to FetchOrientation::REL, this value
     *                                            specifies the row to fetch relative to the cursor position before `fetch()` was called.
     *
     * @return  mixed  The return value of this function on success depends on the fetch type. In all cases, boolean false is returned on failure.
     *
     * @since   2.0.0
     */
    public function fetch(?int $fetchStyle = null, int $cursorOrientation = FetchOrientation::NEXT, int $cursorOffset = 0)
    {
        if (!$this->result) {
            return false;
        }

        $fetchStyle = $fetchStyle ?: $this->defaultFetchStyle;

        if ($fetchStyle === FetchMode::COLUMN) {
            return $this->fetchColumn();
        }

        if (isset($this->fetchMap[$fetchStyle])) {
            return sqlsrv_fetch_array($this->statement, $this->fetchMap[$fetchStyle]) ?: false;
        }

        if (\in_array($fetchStyle, [FetchMode::STANDARD_OBJECT, FetchMode::CUSTOM_OBJECT], true)) {
            return sqlsrv_fetch_object($this->statement, $this->defaultObjectClass) ?: false;
        }

        throw new \InvalidArgumentException("Unknown fetch type '{$fetchStyle}'");
    }

    /**
     * Returns a single column from the next row of a result set
     *
     * @param   integer  $columnIndex  0-indexed number of the column you wish to retrieve from the row.
     *                                 If no value is supplied, the first column is retrieved.
     *
     * @return  mixed  Returns a single column from the next row of a result set or boolean false if there are no more rows.
     *
     * @since   2.0.0
     */
    public function fetchColumn($columnIndex = 0)
    {
        $row = $this->fetch(FetchMode::NUMERIC);

        if ($row === false) {
            return false;
        }

        return $row[$columnIndex] ?? null;
    }

    /**
     * Prepares the SQL Server statement resource for execution
     *
     * @return  resource
     *
     * @since   2.0.0
     */
    private function prepare()
    {
        $params  = [];
        $options = [];

        foreach ($this->bindedValues as $key => &$value) {
            $variable = [
                &$value,
                SQLSRV_PARAM_IN,
            ];

            if ($this->typesKeyMapping[$key] === $this->parameterTypeMapping[ParameterType::LARGE_OBJECT]) {
                $variable[] = $this->typesKeyMapping[$key];
                $variable[] = SQLSRV_SQLTYPE_VARBINARY('max');
            }

            if (isset($this->parameterKeyMapping[$key])) {
                $paramKey = $this->parameterKeyMapping[$key];

                foreach ($paramKey as $currentKey) {
                    $params[$currentKey] = $variable;
                }
            } else {
                $params[] = $variable;
            }
        }

        // Cleanup referenced variable
        unset($value);

        // SQLSRV Function sqlsrv_num_rows requires a static or keyset cursor.
        if (strncmp(strtoupper(ltrim($this->query)), 'SELECT', \strlen('SELECT')) === 0) {
            $options = ['Scrollable' => SQLSRV_CURSOR_KEYSET];
        }

        $statement = sqlsrv_prepare($this->connection, $this->query, $params, $options);

        if (!$statement) {
            $errors = $this->errorInfo();

            throw new PrepareStatementFailureException($errors[0]['message'], $errors[0]['code']);
        }

        return $statement;
    }

    /**
     * Returns the number of rows affected by the last SQL statement.
     *
     * @return  integer
     *
     * @since   2.0.0
     */
    public function rowCount(): int
    {
        if (strncmp(strtoupper(ltrim($this->query)), 'SELECT', \strlen('SELECT')) === 0) {
            return sqlsrv_num_rows($this->statement);
        }

        return sqlsrv_rows_affected($this->statement);
    }

    /**
     * Sets the fetch mode to use while iterating this statement.
     *
     * @param   integer  $fetchMode  The fetch mode, must be one of the FetchMode constants.
     * @param   mixed    ...$args    Optional mode-specific arguments.
     *
     * @return  void
     *
     * @since   2.0.0
     */
    public function setFetchMode(int $fetchMode, ...$args): void
    {
        $this->defaultFetchStyle = $fetchMode;

        if (isset($args[0])) {
            $this->defaultObjectClass = $args[0];
        }
    }
}
