<?php

/**
 * This file is part of the CodeIgniter 4 framework.
 *
 * (c) CodeIgniter Foundation <admin@codeigniter.com>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace CodeIgniter\Database\SQLSRV;

use CodeIgniter\Database\BaseConnection;
use CodeIgniter\Database\Exceptions\DatabaseException;
use Exception;
use stdClass;

/**
 * Connection for SQLSRV
 */
class Connection extends BaseConnection
{
	/**
	 * Database driver
	 *
	 * @var string
	 */
	public $DBDriver = 'SQLSRV';

	/**
	 * Database name
	 *
	 * @var string
	 */
	public $database;

	/**
	 * Scrollable flag
	 *
	 * Determines what cursor type to use when executing queries.
	 *
	 * FALSE or SQLSRV_CURSOR_FORWARD would increase performance,
	 * but would disable num_rows() (and possibly insert_id())
	 *
	 * @var mixed
	 */
	public $scrollable;

	/**
	 * Identifier escape character
	 *
	 * @var string
	 */
	public $escapeChar = '"';

	/**
	 * Database schema
	 *
	 * @var string
	 */
	public $schema = 'dbo';

	/**
	 * Quoted identifier flag
	 *
	 * Whether to use SQL-92 standard quoted identifier
	 * (double quotes) or brackets for identifier escaping.
	 *
	 * @var boolean
	 */
	protected $_quoted_identifier = true;

	/**
	 * List of reserved identifiers
	 *
	 * Identifiers that must NOT be escaped.
	 *
	 * @var string[]
	 */
	protected $_reserved_identifiers = ['*'];

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

	/**
	 * Class constructor
	 *
	 * @param  array $params
	 * @return void
	 */
	public function __construct($params)
	{
		parent::__construct($params);

		// This is only supported as of SQLSRV 3.0
		if ($this->scrollable === null)
		{
			$this->scrollable = defined('SQLSRV_CURSOR_CLIENT_BUFFERED') ? SQLSRV_CURSOR_CLIENT_BUFFERED : false;
		}
	}

	/**
	 * Connect to the database.
	 *
	 * @param  boolean $persistent
	 * @return mixed
	 */
	public function connect(bool $persistent = false)
	{
		$charset = in_array(strtolower($this->charset), ['utf-8', 'utf8'], true) ? 'UTF-8' : SQLSRV_ENC_CHAR;

		$connection = [
			'UID'                  => empty($this->username) ? '' : $this->username,
			'PWD'                  => empty($this->password) ? '' : $this->password,
			'Database'             => $this->database,
			'ConnectionPooling'    => ($persistent === true) ? 1 : 0,
			'CharacterSet'         => $charset,
			'Encrypt'              => ($this->encrypt === true) ? 1 : 0,
			'ReturnDatesAsStrings' => 1,
		];

		// If the username and password are both empty, assume this is a
		// 'Windows Authentication Mode' connection.
		if (empty($connection['UID']) && empty($connection['PWD']))
		{
			unset($connection['UID'], $connection['PWD']);
		}

		if (false !== ($this->connID = sqlsrv_connect($this->hostname, $connection)))
		{
			/* Disable warnings as errors behavior. */
			sqlsrv_configure('WarningsReturnAsErrors', 0);

			// Determine how identifiers are escaped
			$query = $this->query('SELECT CASE WHEN (@@OPTIONS | 256) = @@OPTIONS THEN 1 ELSE 0 END AS qi');
			$query = $query->getResultObject();

			$this->_quoted_identifier = empty($query) ? false : (bool) $query[0]->qi;
			$this->escapeChar         = ($this->_quoted_identifier) ? '"' : ['[', ']'];
		}

		return $this->connID;
	}

	/**
	 * Keep or establish the connection if no queries have been sent for
	 * a length of time exceeding the server's idle timeout.
	 *
	 * @return void
	 */
	public function reconnect()
	{
		$this->close();
		$this->initialize();
	}

	/**
	 * Close the database connection.
	 *
	 * @return void
	 */
	protected function _close()
	{
		sqlsrv_close($this->connID);
	}

	/**
	 * Platform-dependant string escape
	 *
	 * @param  string $str
	 * @return string
	 */
	protected function _escapeString(string $str): string
	{
		return str_replace("'", "''", remove_invisible_characters($str, false));
	}

	/**
	 * Insert ID
	 *
	 * @return integer
	 */
	public function insertID(): int
	{
		return $this->query('SELECT SCOPE_IDENTITY() AS insert_id')->getRow()->insert_id ?? 0;
	}

	/**
	 * Generates the SQL for listing tables in a platform-dependent manner.
	 *
	 * @param boolean $prefixLimit
	 *
	 * @return string
	 */
	protected function _listTables(bool $prefixLimit = false): string
	{
		$sql = 'SELECT [TABLE_NAME] AS "name"'
				. ' FROM [INFORMATION_SCHEMA].[TABLES] '
				. ' WHERE '
				. " [TABLE_SCHEMA] = '" . $this->schema . "'    ";

		if ($prefixLimit === true && $this->DBPrefix !== '')
		{
			$sql .= " AND [TABLE_NAME] LIKE '" . $this->escapeLikeString($this->DBPrefix) . "%' "
					. sprintf($this->likeEscapeStr, $this->likeEscapeChar);
		}

		return $sql;
	}

	/**
	 * Generates a platform-specific query string so that the column names can be fetched.
	 *
	 * @param string $table
	 *
	 * @return string
	 */
	protected function _listColumns(string $table = ''): string
	{
		return 'SELECT [COLUMN_NAME] '
				. ' FROM [INFORMATION_SCHEMA].[COLUMNS]'
				. ' WHERE  [TABLE_NAME] = ' . $this->escape($this->DBPrefix . $table)
				. ' AND [TABLE_SCHEMA] = ' . $this->escape($this->schema);
	}

	/**
	 * Returns an array of objects with index data
	 *
	 * @param  string $table
	 * @return stdClass[]
	 * @throws DatabaseException
	 */
	public function _indexData(string $table): array
	{
		$sql = 'EXEC sp_helpindex ' . $this->escape($table);

		if (($query = $this->query($sql)) === false)
		{
			throw new DatabaseException(lang('Database.failGetIndexData'));
		}
		$query = $query->getResultObject();

		$retVal = [];
		foreach ($query as $row)
		{
			$obj       = new stdClass();
			$obj->name = $row->index_name;

			$_fields     = explode(',', trim($row->index_keys));
			$obj->fields = array_map(function ($v) {
				return trim($v);
			}, $_fields);

			if (strpos($row->index_description, 'primary key located on') !== false)
			{
				$obj->type = 'PRIMARY';
			}
			else
			{
				$obj->type = (strpos($row->index_description, 'nonclustered, unique') !== false) ? 'UNIQUE' : 'INDEX';
			}

			$retVal[$obj->name] = $obj;
		}

		return $retVal;
	}

	/**
	 * Returns an array of objects with Foreign key data
	 * referenced_object_id  parent_object_id
	 *
	 * @param  string $table
	 * @return stdClass[]
	 * @throws DatabaseException
	 */
	public function _foreignKeyData(string $table): array
	{
		$sql = 'SELECT '
				. 'f.name as constraint_name, '
				. 'OBJECT_NAME (f.parent_object_id) as table_name, '
				. 'COL_NAME(fc.parent_object_id,fc.parent_column_id) column_name, '
				. 'OBJECT_NAME(f.referenced_object_id) foreign_table_name, '
				. 'COL_NAME(fc.referenced_object_id,fc.referenced_column_id) foreign_column_name '
				. 'FROM  '
				. 'sys.foreign_keys AS f '
				. 'INNER JOIN  '
				. 'sys.foreign_key_columns AS fc  '
				. 'ON f.OBJECT_ID = fc.constraint_object_id '
				. 'INNER JOIN  '
				. 'sys.tables t  '
				. 'ON t.OBJECT_ID = fc.referenced_object_id '
				. 'WHERE  '
				. 'OBJECT_NAME (f.parent_object_id) = ' . $this->escape($table);

		if (($query = $this->query($sql)) === false)
		{
			throw new DatabaseException(lang('Database.failGetForeignKeyData'));
		}
		$query = $query->getResultObject();

		$retVal = [];
		foreach ($query as $row)
		{
			$obj                      = new stdClass();
			$obj->constraint_name     = $row->constraint_name;
			$obj->table_name          = $row->table_name;
			$obj->column_name         = $row->column_name;
			$obj->foreign_table_name  = $row->foreign_table_name;
			$obj->foreign_column_name = $row->foreign_column_name;
			$retVal[]                 = $obj;
		}

		return $retVal;
	}

	/**
	 * Disables foreign key checks temporarily.
	 *
	 * @return string
	 */
	protected function _disableForeignKeyChecks()
	{
		return 'EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"';
	}

	/**
	 * Enables foreign key checks temporarily.
	 *
	 * @return string
	 */
	protected function _enableForeignKeyChecks()
	{
		return 'EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"';
	}

	/**
	 * Returns an array of objects with field data
	 *
	 * @param  string $table
	 * @return stdClass[]
	 * @throws DatabaseException
	 */
	public function _fieldData(string $table): array
	{
		$sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT
			FROM INFORMATION_SCHEMA.COLUMNS
			WHERE TABLE_NAME= ' . $this->escape(($table));

		if (($query = $this->query($sql)) === false)
		{
			throw new DatabaseException(lang('Database.failGetFieldData'));
		}
		$query = $query->getResultObject();

		$retVal = [];
		for ($i = 0, $c = count($query); $i < $c; $i++)
		{
			$retVal[$i]             = new stdClass();
			$retVal[$i]->name       = $query[$i]->COLUMN_NAME;
			$retVal[$i]->type       = $query[$i]->DATA_TYPE;
			$retVal[$i]->default    = $query[$i]->COLUMN_DEFAULT;
			$retVal[$i]->max_length = $query[$i]->CHARACTER_MAXIMUM_LENGTH > 0 ? $query[$i]->CHARACTER_MAXIMUM_LENGTH : $query[$i]->NUMERIC_PRECISION;
		}

		return $retVal;
	}

	/**
	 * Begin Transaction
	 *
	 * @return boolean
	 */
	protected function _transBegin(): bool
	{
		return (bool) sqlsrv_begin_transaction($this->connID);
	}

	/**
	 * Commit Transaction
	 *
	 * @return boolean
	 */
	protected function _transCommit(): bool
	{
		return (bool) sqlsrv_commit($this->connID);
	}

	/**
	 * Rollback Transaction
	 *
	 * @return boolean
	 */
	protected function _transRollback(): bool
	{
		return (bool) sqlsrv_rollback($this->connID);
	}

	/**
	 * Returns the last error code and message.
	 *
	 * Must return an array with keys 'code' and 'message':
	 *
	 *  return ['code' => null, 'message' => null);
	 *
	 * @return array
	 */
	public function error(): array
	{
		$error = [
			'code'    => '00000',
			'message' => '',
		];

		$sqlsrvErrors = sqlsrv_errors(SQLSRV_ERR_ERRORS);

		if (! is_array($sqlsrvErrors))
		{
			return $error;
		}

		$sqlsrvError = array_shift($sqlsrvErrors);
		if (isset($sqlsrvError['SQLSTATE']))
		{
			$error['code'] = isset($sqlsrvError['code']) ? $sqlsrvError['SQLSTATE'] . '/' . $sqlsrvError['code'] : $sqlsrvError['SQLSTATE'];
		}
		elseif (isset($sqlsrvError['code']))
		{
			$error['code'] = $sqlsrvError['code'];
		}

		if (isset($sqlsrvError['message']))
		{
			$error['message'] = $sqlsrvError['message'];
		}

		return $error;
	}

	/**
	 * Returns the total number of rows affected by this query.
	 *
	 * @return integer
	 */
	public function affectedRows(): int
	{
		return sqlsrv_rows_affected($this->resultID);
	}

	/**
	 * Select a specific database table to use.
	 *
	 * @param string|null $databaseName
	 *
	 * @return mixed
	 */
	public function setDatabase(string $databaseName = null)
	{
		if (empty($databaseName))
		{
			$databaseName = $this->database;
		}

		if (empty($this->connID))
		{
			$this->initialize();
		}

		if ($this->execute('USE ' . $this->_escapeString($databaseName)))
		{
			$this->database  = $databaseName;
			$this->dataCache = [];
			return true;
		}

		return false;
	}

	/**
	 * Executes the query against the database.
	 *
	 * @param string $sql
	 *
	 * @return mixed
	 */
	public function execute(string $sql)
	{
		$stmt = ($this->scrollable === false || $this->isWriteType($sql)) ?
			sqlsrv_query($this->connID, $sql) :
			sqlsrv_query($this->connID, $sql, [], ['Scrollable' => $this->scrollable]);

		if ($stmt === false)
		{
			$error = $this->error();

			log_message('error', $error['message']);
			if ($this->DBDebug)
			{
				throw new Exception($error['message']);
			}
		}

		return $stmt;
	}

	/**
	 * Determines if a query is a "write" type.
	 *
	 * @param  string $sql An SQL query string
	 * @return boolean
	 */
	public function isWriteType($sql)
	{
		return (bool) preg_match('/^\s*"?(SET|INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|TRUNCATE|LOAD|COPY|ALTER|RENAME|GRANT|REVOKE|LOCK|UNLOCK|REINDEX|MERGE)\s/i', $sql);
	}

	/**
	 * Returns the last error encountered by this connection.
	 *
	 * @return mixed
	 */
	public function getError()
	{
		$error = [
			'code'    => '00000',
			'message' => '',
		];

		$sqlsrvErrors = sqlsrv_errors(SQLSRV_ERR_ERRORS);

		if (! is_array($sqlsrvErrors))
		{
			return $error;
		}

		$sqlsrvError = array_shift($sqlsrvErrors);
		if (isset($sqlsrvError['SQLSTATE']))
		{
			$error['code'] = isset($sqlsrvError['code']) ? $sqlsrvError['SQLSTATE'] . '/' . $sqlsrvError['code'] : $sqlsrvError['SQLSTATE'];
		}
		elseif (isset($sqlsrvError['code']))
		{
			$error['code'] = $sqlsrvError['code'];
		}

		if (isset($sqlsrvError['message']))
		{
			$error['message'] = $sqlsrvError['message'];
		}

		return $error;
	}

	/**
	 * The name of the platform in use (MySQLi, mssql, etc)
	 *
	 * @return string
	 */
	public function getPlatform(): string
	{
		return $this->DBDriver;
	}

	/**
	 * Returns a string containing the version of the database being used.
	 *
	 * @return string
	 */
	public function getVersion(): string
	{
		if (isset($this->dataCache['version']))
		{
			return $this->dataCache['version'];
		}

		if (! $this->connID || empty($info = sqlsrv_server_info($this->connID)))
		{
			$this->initialize();
		}

		return isset($info['SQLServerVersion']) ? $this->dataCache['version'] = $info['SQLServerVersion'] : false;
	}
}
