<?php

/**
 * Vvveb
 *
 * Copyright (C) 2022  Ziadin Givan
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program 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 Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 */

namespace Vvveb\System\Sqlp;

use Vvveb\System\Db;

/**
 * SQL Parser — parses .sqlp files and generates PHP model classes.
 */
#[\AllowDynamicProperties]
class Sqlp {
	/** @var string Indentation used when generating PHP code. */
	private const TAB = "\n\n\t\t";

	/** @var array<string, string> Map of PHP type names to parameter-type codes. */
	private array $types = ['int' => 'i', 'double' => 'd', 'decimal' => 'd', 'blob' => 'b', 'array' => 'a'/*, 'CHAR' => 's'*/];

	/** @var string Table-name prefix applied to every query. */
	private string $prefix = DB_PREFIX;

	/** @var array<string, mixed> Configuration loaded from config.php. */
	private array $config = [];

	/** @var array<string, array{name: string, statement: string, params: list<array<string, mixed>>, fetch?: string, param_types?: string, vars?: string}> Parsed method tree. */
	private array $tree = [];

	/** @var array<string, array<string, array{name: string, n: bool, d?: string, e?: string}>> Column-filter definitions keyed by prefixed table name. */
	private array $filters = [];

	/** @var array<string, array<string, string>> Parameter type maps keyed by method name. */
	private array $paramTypes = [];

	/** @var Db Database driver instance. */
	private object $db;

	/** @var string Namespace for the generated model class. */
	private string $namespace;

	/** @var array Model config. */
	private array $model = [];

	/** @var string Model name derived from the .sqlp filename or passed explicitly. */
	private string $modelName;

	/** @var string Full path to the .sqlp file being parsed. */
	private string $filename;

	/**
	 * Initialise the parser: connect to the database and load the engine-specific model template.
	 */
	public function __construct() {
		$this->db     = Db::getInstance();
		$this->config = include __DIR__ . '/config.php';
		//load model for selected database
		$modelFile = __DIR__ . DS . DB_ENGINE . '.tpl';
		$this->loadModel(file_get_contents($modelFile), $this->model);
	}

	/**
	 * Recursively load a model template by expanding nested `%name_start%…%name_end%` blocks.
	 *
	 * @param  string               $text   Raw template text.
	 * @param  array<string, mixed> $config Populated in-place with extracted blocks.
	 * @return string               Template text with block markers replaced by single `%name%` placeholders.
	 */
	public function loadModel(string $text, array &$config): string {
		$regex = '/%(.+?)_start%(.+?)%\1_end%/ms';

		$text = preg_replace_callback($regex, function ($matches) use (&$config) {
			$config[$matches[1]] = $this->loadModel($matches[2], $config);

			return '%' . $matches[1] . '%';
		}, $text);

		return $text;
	}

	/*
	 * Convert array dot notation to php notation 
	 * Ex: my.array.key to ['my']['array']['key']
	 */
	/**
	 * Convert a dot-notation key to a PHP array-access string.
	 *
	 * @param  string $key Dot-separated key, e.g. "my.array.key".
	 * @return string PHP array-access suffix, e.g. "['my']['array']['key']".
	 */
	public function sqlPhpArrayKey(string $key): string {
		return '[\'' . str_replace('.', '\'][\'', $key) . '\']';
	}

	/*
	 * Get table name or alias to use as array key when returning values for the query
	 */
	/**
	 * Extract the alias intended for use as an array key from a SELECT query.
	 *
	 * @param  string $query SQL SELECT statement.
	 * @return string The alias name, or an empty string if none is found.
	 */
	public function getQueryArrayKey(string $query): string {
		$arrayKeyRegex = '@SELECT.*?`?(\w+)`?\(?\)?\s+(as|AS)\s+(_|array_key)[\s,].*FROM@msi';

		if (preg_match($arrayKeyRegex, $query, $matches)) {
			return $matches[1];
		}

		return '';
	}

	/*
	 * Get the value for array_value alias to use as array key when returning values
	 */
	/**
	 * Extract the column aliased as `array_value` from a SELECT query.
	 *
	 * @param  string $query SQL SELECT statement.
	 * @return string The column name, or an empty string if none is found.
	 */
	public function getQueryArrayValue(string $query): string {
		$arrayKeyRegex = '@SELECT.*?`?(\w+)`?\(?\)?\s+(as|AS)\s+array_value[\s,].*FROM@msi';

		if (preg_match($arrayKeyRegex, $query, $matches)) {
			return $matches[1];
		}

		return '';
	}

	/*
	 * Replace subqueries with provided string to keep only main query for getTableName to correctly extract table name
	 */
	/**
	 * Recursively replace balanced parenthesis groups (sub-queries / macros) with a replacement string.
	 *
	 * @param  string $query     SQL to process.
	 * @param  string $replace   Replacement for each matched group.
	 * @param  string $startChar Opening delimiter (default "(").
	 * @param  string $endChar   Closing delimiter (default ")").
	 * @return string Processed SQL.
	 */
	/**
	 * Recursively replace balanced parenthesis groups (sub-queries / macros) with a replacement string.
	 *
	 * @param  string $query     SQL to process.
	 * @param  string $replace   Replacement for each matched group.
	 * @param  string $startChar Opening delimiter (default "(").
	 * @param  string $endChar   Closing delimiter (default ")").
	 * @return string Processed SQL.
	 */
	public function removeParanthesis(string $query, string $replace = '', string $startChar = '(', string $endChar = ')'): string {
		$level = 0;
		$start = null;

		for ($i = 0, $len = strlen($query); $i < $len; $i++) {
			$char = $query[$i];

			if ($char == $startChar) {
				if ($start === null) {
					$start = $i;
				}
				$level++;
			} else {
				if ($char === $endChar) {
					//clamp at zero so stray closing delimiters don't break the balance
					$level = max(0, $level - 1);
				}
			}

			//strict null check - a group starting at position 0 is falsy but valid
			if ($start !== null && $level == 0) {
				$end   = $i + 1;
				$query = substr_replace($query, $replace, $start, $end - $start);
				//reset
				$i     = 0;
				$start = null;
				$len = strlen($query);
			}
		}

		return $query;
	}

	/*
	 * Extract table name from sql statement
	 */
	/**
	 * Extract the primary table name from a SQL statement.
	 *
	 * Handles SELECT, INSERT INTO, UPDATE, DELETE FROM, and aggregate/function forms.
	 *
	 * @param  string $query SQL statement.
	 * @return string Table name (without quotes or prefix).
	 */
	public function getTableName(string $query): string {
		$tableName = '';

		//remove subselects
		$query = $this->removeParanthesis($query, 'replace_subselect');
		//remove macros
		$count = 1;
		while ($count !== 0) {
			$query = preg_replace('/@(\w+)[^@]+?@\1/ms', 'replaced_macro', $query, -1, $count);
		}

		//avoid subselects with negative lookbehind for (
		$selectRegex   = '/(@[^\s]+\s*)?(?<!\()\s*SELECT.*?FROM\s*([`"]?\w+[`"]? AS \w+|[`"]?\w+[`"]?)/ims';
		$insertRegex   = '/(@[^\s]+\s*)?INSERT\s*INTO\s*([`"]?\w+[`"]? AS \w+|[`"]?\w+[`"]?)/ims';
		$updateRegex   = '/(@[^\s]+\s*)?UPDATE\s*([`"]?\w+[`"]? AS \w+|[`"]?\w+[`"]?)/ims';
		$deleteRegex   = '/(@[^\s]+\s*)?DELETE.*FROM\s*([`"]?\w+[`"]? AS \w+|[`"]?\w+[`"]?)/ims';
		$functionRegex = '/(@[^\s]+\s*)?(?<!\()SELECT.*?\w+\(\)\s*AS\s*[`"]?(\w+)[`"]?/ims';
		$countRegex    = '/(@[^\s]+\s*)?SELECT\s*count\(.*?\s*AS\s*[`"]?(\w+)[`"]?$/ims';

		if (preg_match($selectRegex, $query, $matches1) ||
			preg_match($insertRegex, $query, $matches1) ||
			preg_match($updateRegex, $query, $matches1) ||
			preg_match($deleteRegex, $query, $matches1) ||
			preg_match($functionRegex, $query, $matches1) ||
			preg_match($countRegex, $query, $matches1)) {
			$tableName = $matches1[2] ?? ($matches1[3] ?? '');

			if (preg_match('@`?\w+`?$@i', $tableName, $matches2)) {
				$tableName = $matches2[0];
			}
		}

		return trim($tableName, '`"\'');
	}

	/*
	 * Add prefix to table name
	 */
	/**
	 * Prefix all table references in a SQL statement with {@see $this->prefix}.
	 *
	 * @param  string $query SQL statement.
	 * @return string SQL with prefixed table names.
	 */
	public function prefixTable(string $query): string {
		$regexs   = [];
		$regexs[] = '/(UPDATE\s+`?)(\w+`? AS \w+\s+SET|\w+`?\s+SET)/ims';
		$regexs[] = '/(INSERT\s+INTO\s+`?)(\w+`? AS \w+|\w+`?)/ims';
		//$regexs[] = '/(DELETE\s+FROM\s+`?)(\w+`? AS \w+|\w+`?)/ims';
		$regexs[] = '/(\s+JOIN\s+`?)(\w+ AS \w+|\w+`?)/ims';
		$regexs[] = '/(CREATE\s+TABLE\s+`?)(\w+ AS \w+|\w+`?)/ims';
		$regexs[] = '/(\s+IF\s+EXISTS\s+`?)(\w+ AS \w+|\w+`?)/ims';
		$regexs[] = '/(\s+FROM\s+`?)(\w+ AS \w+|\w+`?)/ims';

		foreach ($regexs as $regex) {
			$query = preg_replace_callback(
				$regex,
				function ($matches) {
					return $matches[1] . $this->prefix . $matches[2];
				},
				$query
			);
		}

		return $query;
	}

	/**
	 * Return the parameter-type character for a given SQL type name.
	 *
	 * @param  string $type SQL type name (e.g. "int", "double").
	 * @return string Single-character type code (i / d / b / a / s).
	 */
	public function paramType(string $type): string {
		return $this->types[strtolower($type)] ?? 's';
	}

	/**
	 * Return the PHP expression that fetches the desired result shape.
	 *
	 * @param  string|null $type One of 'insert_id', 'affected_rows', 'fetch_row', 'fetch_one',
	 *                           'fetch_all', or '@result.<key>'.
	 * @return string      PHP expression string to embed in generated code.
	 */
	public function fetchType(?string $type): string {
		if (isset($type[0]) && $type[0] == '@') {
			$key = str_replace('@result.', '', $type);

			$return = $this->template($this->model['fetch_result'], ['key' => $key]);
		} else {
			$return = match ($type) {
				'insert_id'     => $this->model['insert_id'],
				'affected_rows' => $this->model['affected_rows'],
				'fetch_row'     => $this->model['fetch_row'],
				'fetch_one'     => $this->model['fetch_one'],
				default         => $this->model['fetch_all'],
			};
		}

		return trim($return);
	}

	/**
	 * Replace `%variable%` placeholders in a template string.
	 *
	 * @param  string               $template  Template containing `%key%` placeholders.
	 * @param  array<string, mixed> $variables Values to substitute.
	 * @return string               Rendered string.
	 */
	public function template(string $template, array $variables): string {
		//$template = preg_replace('/\t+/', "\t\t", $template);

		$keys = array_map(function ($value) {
			return "%$value%";
		}, array_keys($variables));

		$values = array_values($variables);

		$result = str_replace($keys, $values, $template);
		//remove unmatched variables
		//$result = preg_replace('/%\w+%/', '', $result);

		return $result;
	}

	/**
	 * Parse a single macro block against its regex and expand the template for every match.
	 *
	 * @param  string                           $statement SQL statement containing macro tokens.
	 * @param  array<int, array<string, mixed>> $params    Method parameters.
	 * @param  string                           $regex     PREG pattern that captures macro placeholders.
	 * @param  string                           $template  PHP code template to expand for each match.
	 * @return string                           Expanded SQL statement.
	 */
	public function parseMacro(string $statement, array $params, string $regex, string $template): string {
		$macro = $template;

		if (preg_match_all($regex, $statement, $matches, PREG_SET_ORDER)) {
			foreach ($matches as $match) {
				$macro = $template;

				//replace macro template variables %$variable
				$macro = preg_replace_callback(
					'@\$%(\w+)@',
					function ($varMatch) use ($match) {
						return
						preg_replace_callback(
							$this->config['varRegex'],
							function ($matches) {
								return '$' . \Vvveb\dotToArrayKey('params.' . $matches[1]);
								//return '$params[\'' . $matches[1] . '\']';
							},
							$match[$varMatch[1]]);
					},
					$macro);

				//replace macro template placeholders %placeholder
				$macro = preg_replace_callback(
					'@\%(\w+)@',
					function ($varMatch) use ($match) {
						return $match[$varMatch[1]];
					},
					$macro);

				$statement = str_replace($match[0], $macro, $statement);
			}
		}

		return $statement;
	}

	/**
	 * Expand `@EACH` variable macros into foreach loops within the generated PHP code.
	 *
	 * @param  string                           $statement SQL statement containing @EACH tokens.
	 * @param  array<int, array<string, mixed>> $params    Method parameters.
	 * @return string                           Statement with @EACH blocks expanded.
	 */
	public function parseEach(string $statement, array $params): string {
		//EACH VAR
		if (preg_match_all($this->config['eachVarRegex'], $statement, $matches, PREG_SET_ORDER)) {
			foreach ($matches as $match) {
				$resultKey = $this->sqlPhpArrayKey($match[1]);

				$each = "\n" . self::TAB . 'if (isset($params' . $resultKey . ') && is_array($params' . $resultKey . '))' .
				'foreach ($params' . $resultKey . ' as $key => $rowParent) { ' . "\n" . self::TAB .
					' $params[\'each\'] = $rowParent;
					$params[\'each_key\'] = $key;
					if (is_array($params[\'each\'])) {
						$paramTypes[\'each\'] = \'a\';
					} else if (is_int($params[\'each\'])) {
						$paramTypes[\'each\'] = \'i\';
					} else {
						$paramTypes[\'each\'] = \'s\';
					}
					$sql = \'';

				$statement = str_replace('$sql = \'' . $match[0], $each, $statement) . "\n" . self::TAB . ' }
					unset($params[\'each\']);
					unset($paramTypes[\'each\']);
					';
			}
		}

		return $statement;
	}

	/**
	 * Wrap a COUNT statement with the required assignment prefix.
	 *
	 * @param  string                           $statement SQL statement.
	 * @param  array<int, array<string, mixed>> $params    Method parameters.
	 * @return string                           Prefixed statement.
	 */
	public function parseSQLCount(string $statement, array $params): string {
		//EACH VAR
		$statement = '$sql = ' . $statement;

		return $statement;
	}

	/**
	 * Parse all macro constructs in a SQL statement: variables, conditionals, result references, and filters.
	 *
	 * @param  string                           $statement Raw SQL with macro tokens.
	 * @param  array<int, array<string, mixed>> $params    Method parameters.
	 * @return string                           PHP code string ready for embedding in a generated method.
	 */
	public function parseMacros(string $statement, array $params): string {
		//replace variables
		$statement =
				preg_replace_callback(
					'/\$([\w_-]+)/',
					function ($matches) {
						$key = $matches[1];

						return "' . (isset(\$params['$key']) ? \$params['$key'] : 'NULL') . '";
						//return "' . \$results['". $matches[1] . "'] . '";
					},
					$statement);

		$lex       = new Lexer($this->config['tokenMap'],  $this->config['macroMap']);
		$structure = $lex->lex($statement);

		$output    = $lex->treeMacro($structure);
		$statement = $lex->treeToPhp($output, $this->config['macroMap']);

		//@result
		//replace result variables
		$statement =
					preg_replace_callback(
						'/@result\.([\w\.]+)/',
						function ($matches) {
							$key = \Vvveb\dotToArrayKey('$results.' . $matches[1]);

							return "' . (isset($key) ? $key : 'NULL') . '";
						},
						$statement);

		return $this->processFilters($statement);
	}

	/**
	 * Process @FILTER directives: read column metadata for the referenced table,
	 * cache the filter definition and embed db->filter() calls in the generated code.
	 *
	 * @param  string $statement Generated code containing @FILTER tokens.
	 * @return string Statement with filter processing embedded.
	 */
	private function processFilters(string $statement): string {
		if (! preg_match_all($this->config['filterRegex'], $statement, $matches, PREG_SET_ORDER)) {
			return $statement;
		}

		foreach ($matches as $match) {
			$filter             = [];
			$columns            = $this->db->getColumnsMeta($this->prefix . $match['columns']);
			$addMissingDefaults = $match['addmissing'] ?? 'false';
			$isArray            = $match['array'] ?? 'false';

			foreach ($columns as $column) {
				$name = $column['name'];
				unset($column['name']);

				if (empty($column['e'])) {
					unset($column['e']);
				}
				$column['n']   = ($column['n'] === 'NO') ? false : true;

				if (isset($column['d'])) {
					$column['d'] = trim($column['d'], '\'');
				}
				$filter[$name] = $column;
			}

			$return      = ! empty($match['return']) ? $match['return'] : $match['data'];
			$return      = '$params' . $this->sqlPhpArrayKey($return);
			$key         = '$params' . $this->sqlPhpArrayKey($match['data']);
			$filterName  = '$this->filters[\'' . $this->prefix . $match['columns'] . '\']';

			$filterFunction                                   = '\';' . self::TAB;
			$this->filters[$this->prefix . $match['columns']] = $filter;

			if ($isArray === 'true') {
				$filterFunction .= 'if (isset(' . $key . ') && is_array(' . $key . ')) foreach ( ' . $key . ' as $key => &$filter) ' . $return . '[$key] = $this->db->filter($filter, ' . $filterName . ',' . $addMissingDefaults . ');' . self::TAB . '$sql = \'';
			} else {
				$filterFunction .= $return . '= $this->db->filter(' . $key . ', ' . $filterName . ',' . $addMissingDefaults . ');' . self::TAB . '$sql = \'';
			}

			$statement = str_replace($match[0], $filterFunction, $statement);
		}

		return $statement;
	}

	/**
	 * Parse the parameter declarations block of a stored-procedure definition.
	 *
	 * @param  string                                                                                 $params Raw parameter text from the .sqlp file.
	 * @return list<array{in_out: string, name: string, type: string, length: int, comment?: string}>
	 */
	public function parseParameters(string $params): array {
		$parameters = [];

		if (preg_match_all($this->config['paramRegex'], $params, $matches, PREG_SET_ORDER)) {
			foreach ($matches as $match) {
				$param['in_out'] = trim($match[1]);
				$param['name']   = trim($match[2]);

				$param['type'] = $param['length'] = '';

				if (isset($match[3])) {
					$param['type'] = trim($match[3]);
				}

				if (isset($match[4])) {
					$param['length'] = (int) preg_replace('/[^\d]/', '', $match[4]);
				}

				if (isset($match[5])) {
					$param['comment'] = trim($match[5], " \n\r\t\v\x00-");
				}

				$parameters[] = $param;
			}
		}

		return $parameters;
	}

	/**
	 * Resolve `import(…)` directives by replacing them with the contents of the referenced file.
	 *
	 * @param  string $sql Raw SQL text.
	 * @return string SQL with imports inlined.
	 */
	public function processImports(string $sql): string {
		$sql = preg_replace_callback($this->config['importRegex'], function ($matches) {
			$file =  $matches[1];
			//absolute path
			if ($file[0] === '/') {
				$path = explode('/', substr($file, 1));
				$app  = $path[0];
				unset($path[0]);

				if ($app === 'plugins') {
					$plugin = $path[1] ?? '';
					unset($path[1]);
				} else {
					$plugin = '';
				}

				$file = implode('/', $path);
				$file = DIR_ROOT . $app . ($plugin ? '/' . $plugin : '') . '/sql/' . DB_ENGINE . '/' . $file;
			} else {//relative path
				$file = DIR_SQL . $matches[1];
			}

			if (file_exists($file)) {
				return file_get_contents($file);
			}

			return '';
		}, $sql);

		return $sql;
	}

	/**
	 * Parse a `.sqlp` file and populate the internal method tree.
	 *
	 * @param  string      $filename  Path to the `.sqlp` file.
	 * @param  string|bool $modelName Optional explicit model name; when false it is derived from the filename.
	 * @param  string      $namespace Optional namespace for the generated class.
	 * @return void
	 */
	public function parseSqlPfile(string $filename, string|bool $modelName = false, string $namespace = ''): void {
		if ($modelName) {
			$this->modelName = $modelName;
		} else {
			//strip .sql/.sqlp extension and camel-case underscored names
			$this->modelName = str_replace('.sql', '', preg_replace_callback('/_(\w)/',
				function ($m) {
					return ucfirst($m[1]);
				}, basename($filename)));
		}

		$this->namespace = $namespace;
		$this->filename  = $filename;

		$sql = file_get_contents($filename);
		//process imports
		$sql = $this->processImports($sql);

		//remove comments
		$sql        = preg_replace('@(--.*)\s+@', '', $sql);
		$this->tree = [];

		if (preg_match_all($this->config['functionRegex'], $sql, $matches, PREG_SET_ORDER)) {
			foreach ($matches as $match) {
				$method['name']      = trim($match['name'], '`"\'');
				//add slashes only for single quotes
				$method['statement'] = str_replace("'", "\'",trim($match['statement']));
				$method['statement'] = preg_replace('@(--.*)\s+@', '', $method['statement']);

				$method['params'] = $this->parseParameters($match['params']);

				//$this->tree[] = $method;
				//overwrite method if redefined
				$this->tree[$method['name']] = $method;
			}
		}
	}

	/**
	 * Return the parsed method tree.
	 *
	 * @return array<string, array{name: string, statement: string, params: list<array<string, mixed>>}>
	 */
	public function getModel(): array {
		return $this->tree;
	}

	/**
	 * Generate the complete PHP model source code from the parsed tree.
	 *
	 * @return string PHP class source code.
	 */
	public function generateModel(): string {
		$methods = '';

		if ($this->tree) {
			foreach ($this->tree as $i => $method) {
				$methods .= $this->generateMethod($method, $i);
			}
		}

		return $this->template($this->model['model'], [
			'name'       => ucfirst($this->modelName),
			'namespace'  => ucfirst($this->namespace),
			'filename'   => $this->filename,
			'methods'    => $methods,
			'filters'    => var_export($this->filters, true),
			'paramTypes' => var_export($this->paramTypes, true),
		]);
	}

	/**
	 * Generate the code for a single model method (one per stored procedure).
	 *
	 * @param  array{name: string, statement: string, params: list<array<string, mixed>>} $method       Parsed method.
	 * @param  string|int                                                                 $methodIndex Tree key of the method (its name); compared strictly against the OUT-parameter counter.
	 * @return string Generated method code.
	 */
	private function generateMethod(array $method, string|int $methodIndex): string {
		//NOTE: the legacy implementation initialised a "/*Model - method*/" comment
		//here but overwrote it before use, so generated models never contained it;
		//kept out to preserve byte-identical output
		$statements = '';

		$queries         = explode(';', $method['statement']);
		$method['fetch'] = $this->fetchType('fetch_all');

		//OUT parameters select the fetch style of their corresponding query
		$fetch = [];

		foreach ($method['params'] as $param) {
			if ($param['in_out'] === 'OUT') {
				$fetch[] = $param['name'];
			}
		}

		foreach ($queries as $qIndex => $query) {
			$statement = '';
			$query     = $this->prefixTable(trim($query));

			if (empty($query)) {
				continue;
			}

			$template = $this->model['query'];

			$queryId    = preg_replace('/^' . $this->prefix . '/', '',  $this->getTableName($query));
			$arrayKey   = $this->getQueryArrayKey($query);
			$arrayValue = $this->getQueryArrayValue($query);

			$statement .= $this->template($template,
				[
					'statement'   => $this->parseMacros($query, $method['params']),
					'query_id'    => $queryId,
					'array_key'   => $arrayKey,
					'array_value' => $arrayValue,
				]);

			$statement = $this->parseEach($statement, $method['params']);
			$statement = $this->expandArrayParams($statement, $method['params']);

			//clean empty sql strings
			$statement = preg_replace('@\$sql \.= \'\s+\n\s*\';@ms', '$sql .= \' \';', $statement);
			$statement = preg_replace('@\s*\$sql \.= \'\';@ms', '', $statement);

			if (isset($fetch[$qIndex])) {
				$method['fetch'] = $this->fetchType($fetch[$qIndex]);
			} else {
				if (isset($fetch[0])) {
					$method['fetch'] = $this->fetchType($fetch[0]);
				}
			}

			$statement = $this->template($statement, ['fetch' => $method['fetch']]);

			$statements .= $statement;
		}

		$method['statement'] = $statements;

		//collect typed IN parameters for bind-param
		$paramTypes = [];

		foreach ($method['params'] as $param) {
			if ($param['in_out'] === 'IN' && ($type = $this->paramType($param['type']))) {
				$paramTypes[$param['name']] = $type;
			}
		}

		$this->paramTypes[$method['name']] = $paramTypes;

		$method['param_types'] = '$this->paramTypes[\'' . $method['name'] . '\']';

		$method['fetch'] = $this->fetchType('fetch_all');

		//pick the OUT parameter matching the method index, else the first one
		$fetch = false;
		$o     = 0;

		foreach ($method['params'] as $param) {
			if ($param['in_out'] === 'OUT') {
				if (! $fetch) {
					$fetch = $param['name'];
				}

				if ($o === $methodIndex) {
					$fetch = $param['name'];

					break;
				}
				++$o;
			}
		}

		$method['fetch'] = $this->fetchType($fetch);

		$method['params'] = trim(implode(', ', array_map(
			function ($param) {
				if ($param['in_out'] === 'IN') {
					return '$' . $param['name'];
				}
			}, $method['params'])), ', ');

		return $this->template($this->model['methodMultipleTemplate'], $method);
	}

	/**
	 * Expand ARRAY-typed parameters into db->expandArray() calls embedded in the generated code.
	 *
	 * @param  string                           $statement Generated statement code.
	 * @param  list<array<string, mixed>>       $params    Method parameters.
	 * @return string Statement with array expansions injected.
	 */
	private function expandArrayParams(string $statement, array $params): string {
		foreach ($params as $param) {
			if ($param['type'] === 'ARRAY') {
				$expandArray = '\';' . self::TAB . ' list($_sql, $_params) = $this->db->expandArray($params[\'' . $param['name'] . '\'], \''. $param['name'] . '\');'
								 . self::TAB . '$sql .= $_sql;'
								 . self::TAB . 'if (is_array($_params)) $paramTypes = array_merge($paramTypes, $_params);'
								 . self::TAB . '$sql .= \' ';

				$statement = preg_replace('@:' . $param['name'] . '(?!_\.) @', $expandArray, $statement);
			}
		}

		return $statement;
	}
}
