<?php

/**
 * Vvveb
 *
 * Copyright (C) 2020  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;

define('TAB', "\n\n\t\t");

class SqlP {
	const FUNCTION_REGEX = '/(CREATE\s+)?PROCEDURE\s+(?<name>\w+)\((?<params>.*?)\)\s+BEGIN\s+(?<statement>.*?)END(?!\s*@)/ms';

	const PARAM_REGEX = '/(IN|OUT|INOUT|LOCAL)\s+([\.@\w]+)\s*(\w+)?(\(\d+\))?[,$\n\s]?/ms';

	const IFTHEN_REGEX = '/\s*@IF\s*(?<cond>.+?)\s*THEN\s*(?<then>.+?)\s*END @IF?\s*/ms';

	const KEYS_REGEX = '/\s*@KEYS\s*\(\s*:(?<keys>[\w_]+)\s*\)\s*/ms';

	const LIST_REGEX = '/\s*@LIST\s*\(\s*:(?<list>[\w\[\]]+)\s*\)\s*/ms';

	const VALUES_REGEX = '/\s*@VALUES\s*\(\s*:(?<list>[\w\[\]]+)\s*\)\s*/ms';

	const EACH_REGEX = '/\s*@EACH\s*\(\s*(\w+)\s*\,\s*([\w\.]+)\s*\)\s*/ms';

	const EACH_VAR_REGEX = '/\s*@EACH\s*\(\s*:(.+?)\s*\)\s*/ms';

	const SQL_COUNT = '/\s*@SQL_COUNT\s*\(\s*(?<column>.+?),\s*(?<table>.+?)\s*\)\s*/ms';

	const FILTER_REGEX = '/\s*:?(?<return>[\w_]+)?\s*=?\s*@FILTER\s*\(\s*:(?<data>[\w\._]+)\s*\,\s*(?<columns>[\w_]+),?\s*(?<addmissing>true|false)?,?\s*(?<array>true|false)?\s*\)\s*/ms';

	const IFTHENELSE_REGEX = '/\s*@IF\s*(?<cond>.+?)\s*THEN\s*(?<then>.+?)\s*ELSE\s*(?<else>.+?)\s*END @IF?\s*/ms';

	const VAR_REGEX = '/:(\w+)/ms';

	//Generated model templates
	const MODEL_TEMPLATE =
'
namespace Vvveb\Sql;

class %name%SQL extends \Vvveb\System\Db\\' . DB_ENGINE . '
{

	public function __construct(){
		parent::__construct();
	}

	%methods%
}';

	const PARAMS_TEMPLATE = '';

	const VARS_TEMPLATE =

'
		if (isset($params[\'%name%\']))
		$stmt->bindValue(\':%name%\', (%type%)$params[\'%name%\'], PDO::PARAM_%type%);
';

	const MYSQL_QUERY =
'
		$prevSql = $sql ?? \'\';
		$sql = \'%statement%\';

		if ($sql) {
		$stmt[\'%query_id%\'] = $this->execute($sql, $params, $paramTypes);
		
		$result = false;

		if ($stmt[\'%query_id%\']) {
			if (method_exists($stmt[\'%query_id%\'], \'get_result\')) {
				$result = $stmt[\'%query_id%\']->get_result();
			} else 	{
				$result = $this->get_result($stmt[\'%query_id%\']);
			}
		}
		
		/*
		if (\'%query_id%\' == \'_\') {
			$value = %fetch%;
			if (is_array($value))
			{
				$results = $results + $value;
			} else
			{
				$results = $value;
			}
		} else { */
		    if (!empty(\'%array_key%\'))
		    {
				if ($result)
				while ($row = $result->fetch_array(MYSQLI_ASSOC))
				{
					$values = $row;
					if (!empty(\'%array_value%\'))
					{
						//$values = $row[\'%array_value%\'];
						$values = $row[\'array_value\'];
					} 
				
					if (\'%query_id%\' == \'_\') {
						//$results[$row[\'%array_key%\']] = $values;
						$results[$row[\'array_key\']] = $values;
					} else {
						//$results[\'%query_id%\'][$row[\'%array_key%\']] = $values;
						
						$results[\'%query_id%\'][$row[\'array_key\']] = $values;
						
					}
				}
			} else
			{
				if (\'%query_id%\' == \'_\') {
					$value = %fetch%;
					if (is_array($value))
					{
						$results = $results + $value;
					} else
					{
						$results = $value;
					}
				} else 
				{
					$results[\'%query_id%\'] = %fetch%;
				}
			}
		//}
	}
';

	const MYSQL_EACH_QUERY =
'
		%statement%;
';

	const MYSQL_PLACEHOLDER = '?';

	const PGSQL_PLACEHOLDER = '$%i%';

	const METHOD_MYSQL_TEMPLATE =
'
	function %name%($params = array())
	{
		$results = array();
		//$this->validate(%params%);
		$paramTypes = %param_types%;
		
		$sql = \'%statement%\';
		$stmt = $this->execute($sql, $params, $paramTypes);

		if (!$stmt) {
			//echo "\nPDO::errorInfo():\n";
			//print_r($this->db->errorInfo());
		}
		
		//$stmt->debugDumpParams();
		//$this->query($sql);

		$result = $stmt->get_result();

		$results = %fetch%;
		
		//$results = $result->fetch_all(MYSQLI_ASSOC);

/*
		var_dump($result);
		var_dump($results);
*/		
		//if ($results)
		return $results;
	}
';

	const METHOD_MULTIPLE_MYSQL_TEMPLATE =
'
	function %name%($params = array())
	{
		//multitple
		$results = array();
        $stmt = [];
		$paramTypes = %param_types%;

		%statement%

		if ($results)
		return $results;
	}
';

	//macros

	const IFTHENELSE_MACRO =
	'\';
	
	if ($%cond) {
		$sql .= \' %then \';
	} else {
		$sql .= \' %else \';
	} 
	
	$sql .= \' 
	';

	const IFTHEN_MACRO =
	'\';
	
	if ($%cond) {
		$sql .= \' %then \';
	} 
	
	$sql .= \' 
	';

	const KEYS_MACRO =
	'\';
	$sql .= \'`\' . implode(\'`,`\', array_keys($params[\'%keys\'])); 
	$sql .= \'` ';

	const LIST_MACRO =
	'\';
	
	list($_sql, $_params) = $this->expandList($params[\'%list\'], \'%list\');

	$sql .= \' \' . $_sql;

	if (is_array($_params)) $paramTypes = array_merge($paramTypes, $_params);

	$sql .= \' \' . \'
	';

	const SQL_COUNT_MACRO =
	'\'; 
	
	$sql .= $this->sqlCount($prevSql, \'%column\', $this->prefix . \'%table\'); 
	$sql .= \'
	
	';

	private $prefix = DB_PREFIX;

	function getColumnsMeta($tableName) {
		$db = '\Vvveb\System\Db\\' . DB_ENGINE;
		$db = new $db();

		$sql =
		'SELECT COLUMN_NAME as name, COLUMN_DEFAULT as d, IS_NULLABLE  as n, DATA_TYPE as t, EXTRA as e
		FROM `INFORMATION_SCHEMA`.`COLUMNS` 
		WHERE `TABLE_SCHEMA`= "' . DB_NAME . '" 
			AND `TABLE_NAME`="' . $tableName . '"';

		if ($result = $db->query($sql)) {
			//$columns = $result->fetch_all(MYSQLI_ASSOC);
			$columns = [];

			while ($row = $result->fetch_assoc()) {
				$columns[] = $row;
			}

			/* free result set */
			$result->close();

			return $columns;
		} else {
		}

		return false;
	}

	function sqlPhpArrayKey($key) {
		return '[\'' . str_replace('.', '\'][\'', $key) . '\']';
	}

	function getQueryArrayKey($query) {
		$arrayKeyRegex = '@SELECT.*?`?(\w+)`?\(?\)?\s+(as|AS)\s+(_|array_key)[\s,].*FROM@msi';

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

		return '';
	}

	function getQueryArrayValue($query) {
		$arrayKeyRegex = '@SELECT.*?`?(\w+)`?\(?\)?\s+(as|AS)\s+array_value[\s,].*FROM@msi';

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

		return '';
	}

	/*
	 * Extract table name from sql statement
	 */
	function getTableName($query) {
		$tableName = '';

		$selectRegex   = '/(@[^\s]+\s*)?SELECT.*FROM\s*(`?\w+`? AS \w+|`?\w+`?)/ims';
		$updateRegex   = '/(@[^\s]+\s*)?UPDATE\s*(`?\w+`? AS \w+|`?\w+`?)/ims';
		$insertRegex   = '/(@[^\s]+\s*)?INSERT\s*INTO\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($updateRegex, $query, $matches1) ||
				preg_match($insertRegex, $query, $matches1) ||
				preg_match($deleteRegex, $query, $matches1) ||
				preg_match($functionRegex, $query, $matches1) ||
				preg_match($countRegex, $query, $matches1)
			) {
			$tableName = $matches1[2];

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

		return $tableName;
	}

	/*
	 * Extract table name from sql statement
	 */
	function prefixTable($query) {
		$tableName = '';

		//$regexs[] = '/(SELECT.+FROM\s+`?)(\w+`? AS \w+|\w+`?)/ims';
		$regexs[] = '/(UPDATE\s+`?)(\w+`? AS \w+|\w+`?)/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;
	}

	function paramType($type) {
		switch ($type) {
			case 'INT':
				return  'i';

			case 'DOUBLE':
				return  'd';

			case 'BLOB':
				return  'b';

			case 'ARRAY':
				return  'a';

			default:
			 return 's';
		}
	}

	function fetchType($type) {
		switch ($type) {
			case 'insert_id':
				return  '$this->insert_id';

			case 'affected_rows':
				return  '$this->affected_rows';

			case 'fetch_row':
				return  '$result->fetch_array(MYSQLI_ASSOC)';

			case 'fetch_one':
				return  '$result->fetch_array(MYSQLI_NUM)[0]';

			case (isset($type[0]) && $type[0] == '@'):
				 $key = str_replace('@result.', '', $type);

				 return "isset(\$results['$key']) ? \$results['$key'] : 'NULL'";

			case 'fetch_all':
			default:
			 return '$result->fetch_all(MYSQLI_ASSOC)';
		}
	}

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

		$values = array_values($variables);

		return str_replace($keys, $values, $template);
	}

	function parseMacro($statement, $params, $regex, $template) {
		$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::VAR_REGEX,
						function ($matches) {
							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;
	}

	function parseEach($statement, $params) {
		//EACH VAR
		if (preg_match_all($this::EACH_VAR_REGEX, $statement, $matches, PREG_SET_ORDER)) {
			foreach ($matches as $match) {
				$tableName = $this->getTableName($statement);
				$resultKey = $this->sqlPhpArrayKey($match[1]);

				$each = "\n" . TAB . 'foreach ($params' . $resultKey . ' as $key => $rowParent) { ' . "\n" . TAB . ' 
					$params[\'each\'] = $rowParent;
					$paramTypes[\'each\'] = \'a\';
					$sql = \'';

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

		return $statement;
	}

	function parseSQLCount($statement, $params) {
		//EACH VAR
		$statement = '$sql = ' . $statement;

		return $statement;
	}

	function parseMacros($statement, $params) {
		//error_log($statement);
		//if then else
		$space = "\n\n\t\t";

		//replace variables
		$statement =
				preg_replace_callback(
					'/\$([\w_-]+)/',
					function ($matches) {
						$key = $matches[1];

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

		$statement = $this->parseMacro($statement, $params,
				$this::IFTHENELSE_REGEX,
				$this::IFTHENELSE_MACRO
			);

		//if then
		$statement = $this->parseMacro($statement, $params,
				$this::IFTHEN_REGEX,
				$this::IFTHEN_MACRO
			);

		//@KEYS
		$statement = $this->parseMacro($statement, $params,
				$this::KEYS_REGEX,
				$this::KEYS_MACRO
			);

		//@LIST
		$statement = $this->parseMacro($statement, $params,
				$this::LIST_REGEX,
				$this::LIST_MACRO
			);

		//@SQL_COUNT
		$statement = $this->parseMacro($statement, $params,
				$this::SQL_COUNT,
				$this::SQL_COUNT_MACRO
			);

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

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

		//$variable

		//FILTER
		if (preg_match_all($this::FILTER_REGEX, $statement, $matches, PREG_SET_ORDER)) {
			foreach ($matches as $match) {
				$filter             = [];
				$columns            = $this->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;
					$filter[$name] = $column;
				}

				$filterArray = var_export($filter, true);
				$return      = ! empty($match['return']) ? $match['return'] : $match['data'];
				$return      = '$params' . $this->sqlPhpArrayKey($return);
				$key         = '$params' . $this->sqlPhpArrayKey($match['data']);

				$filterFunction = '\';' . TAB . '$filterArray = ' . $filterArray . ';';

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

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

		//EACH SQL
		/*
		if (preg_match_all($this::EACH_REGEX, $statement, $matches, PREG_SET_ORDER))
		{
			foreach ($matches as $match)
			{
				$tableName = $this->getTableName($statement);
				$resultKey = $this->sqlPhpArrayKey($match[2]);
				$rowKey = $this->sqlPhpArrayKey($match[2]);

				$statement = str_replace($match[0], '', $statement) . "';\n" . TAB . 
				'foreach ($results' .  $resultKey . ' as $rowParent) {
						
					while ($row = $result->fetch_assoc()) {
							
						$results[\''. $tableName . '\'][ $row'. $rowKey . '  ] = $row;
							
					}
				}' . TAB .' $sql .=\'';
			}
		}*/
		return $statement;
	}

	function parseParameters($params) {
		$parameters = [];

		if (preg_match_all($this::PARAM_REGEX, $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]);
				}

				$parameters[] = $param;
			}
		}

		return $parameters;
	}

	function parseSqlPfile($filename) {
		$this->modelName = str_replace('.sql', '',  preg_replace_callback('/_(\w)/',
			function ($m) {
				return ucfirst($m[1]);
			} , basename($filename)));

		$sql = file_get_contents($filename);

		//remove comments
		$sql = preg_replace('@(--.*)\s+@', '', $sql);

		if (preg_match_all($this::FUNCTION_REGEX, $sql, $matches, PREG_SET_ORDER)) {
			foreach ($matches as $match) {
				$method['name']      = trim($match['name']);
				$method['statement'] = addslashes(trim($match['statement']));

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

				$this->tree[] = $method;
			}
		}
	}

	function generateModel() {
		$methods = '';

		foreach ($this->tree as $i => $method) {
			$statement = "/*$this->modelName - ${method['name']}*/\n\t\t";

			$queries      = explode(';', $method['statement']);
			$queriesCount = count($queries);

			$statements = '';

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

			$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), DB_PREFIX);

				if (empty($query)) {
					continue;
				}
				$hasEach = (0 === substr_compare($query, '@EACH', 0, 5));

				$template = /*$hasEach?$this::MYSQL_EACH_QUERY:*/$this::MYSQL_QUERY;

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

				//$query = $this->parseSQLCount($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']);

				//expand array parameters
				foreach ($method['params'] as $param) {
					if ($param['type'] == 'ARRAY') {
						$expandArray = '\';' . TAB . ' list($_sql, $_params) = $this->expandArray($params[\'' . $param['name'] . '\'], \'' . $param['name'] . '\');'
											 . TAB . '$sql .= $_sql;'
											 . TAB . 'if (is_array($_params)) $paramTypes = array_merge($paramTypes, $_params);'
											 . TAB . '$sql .= \' ';

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

				//clean empty sql strings
				$statement = preg_replace('@\s*\$sql .= \'\s*\';@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;

			//generate function parameter list
			$method['vars'] = trim(implode("\n\t", array_map(
								function ($param) {
									if ($param['in_out'] == 'IN' && ($param['type'] = $this->paramType($param['type']))) {
										return $this->template($this::VARS_TEMPLATE, $param);
									}
								} ,$method['params'])), "\n\t");

			$method['param_types'] = 'array(' . trim(implode(', ', array_map(
								function ($param) {
									if ($param['in_out'] == 'IN' && ($type = $this->paramType($param['type']))) {
										return '\'' . $param['name'] . '\' => \'' . $type . '\'';
									}
								} ,$method['params'])), ', ') . ')';

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

			$fetch = false;
			$o     = 0;

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

					if ($o == $i) {
						$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'])), ', ');

			//error_log(print_r($method, 1));

			//if ($queriesCount > 1)
			//{
			$methods .= $this->template($this::METHOD_MULTIPLE_MYSQL_TEMPLATE, $method);
			//} else
			//{
				//$methods .= $this->template($this::METHOD_MYSQL_TEMPLATE, $method);
			//}
		}

		$model = $this->template($this::MODEL_TEMPLATE,
									['name'    => $this->modelName,
										'methods' => $methods,
									]);

		return $model;
	}
}
