<?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\Import;

use function Vvveb\globBrace;

#[\AllowDynamicProperties]
class Sql {
	private $db;

	private $prefix;

	function __construct($driver = DB_ENGINE, $host = DB_HOST, $dbname = DB_NAME, $user = DB_USER, $pass = DB_PASS, $port = DB_PASS, $prefix = DB_PREFIX) {
		$this->sqlPath = DIR_ROOT . "install/sql/$driver/";
		$engine        = '\Vvveb\System\Db\\' . ucfirst($driver);

		$this->prefix = $prefix;

		try {
			$this->db = new $engine($host, $dbname, $user, $pass, $port, $prefix);
		} catch (\Exception $e) {
			//unknown database, try to create
			if ($e->getCode() == 1049) {
				$this->db = new $engine($host, '', $user, $pass, $port, $prefix);

				if ($driver !== 'sqlite') {
					$this->createDb($dbname);
				}
			} else {
				throw($e);
			}
		}
	}

	private $sqlPath = '';

	function setPath($path) {
		$this->sqlPath = $path;
	}

	function createDb($dbname) {
		if (! $this->db->query("CREATE DATABASE IF NOT EXISTS `$dbname`")) {
			throw new \Exception($this->db->error);
		}

		if (! $this->db->select_db($dbname)) {
			throw new \Exception($this->db->error);
		}
	}

	function prefixTable($query, $prefix) {
		$tableName = '';

		//$regexs[] = '/(SELECT.+FROM\s+`?)(\w+`? AS \w+|\w+`?)/ims';
		$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) use ($prefix) {
					return $matches[1] . $prefix . $matches[2];
				},
				$query
			);
		}

		return $query;
	}

	function multiQuery($sql, $filename = '') {
		if (! $sql) {
			return;
		}

		if ($this->prefix) {
			$sql = $this->prefixTable($sql, $this->prefix);
		}

		try {
			if (DB_ENGINE == 'mysqli' || DB_ENGINE == 'pgsql') {
				if (! ($stmt = $this->db->multi_query($sql))) {
					throw new \Exception($this->db->error() . "\n\n in $filename\n\n" . substr($sql, 0, 256));
				}
			} else {
				if (($stmt = $this->db->query($sql)) === false) {
					throw new \Exception($this->db->error() . "\n\n in $filename\n\n" . substr($sql, 0, 256));
				}
			}
		} catch (\Exception $e) {
			throw new \Exception($e->getMessage() . "\n\n" . substr($sql, 0, 256) . "\n\n in $filename", $e->getCode());
		}

		if (DB_ENGINE == 'mysqli') {
			try {
				do {
					/* store first result set */
					if ($result = $this->db->store_result()) {
						/*
						while ($row = $result->fetchRow()) {
						}*/
						$result->free();
					}
				} while ($this->db->more_results() && $this->db->next_result());
			} catch (\Exception $e) {
				throw new \Exception($e->getMessage() . "\n\n" . substr($sql, 0, 256) . "\n\n in `$filename`", $e->getCode());
			}
		} else {
			if (DB_ENGINE == 'sqlite') {
				$result = [];

				if ($stmt) {
					try {
						$num_rows = $stmt->numColumns() && $stmt->columnType(0) != SQLITE3_NULL;

						if ($num_rows) {
							while ($row = $stmt->fetchArray(SQLITE3_ASSOC)) {
								$result[] = $row;
							}
							//$stmt->finalize();
						}
					} catch (\Exception $e) {
						throw new \Exception($e->getMessage() . "\n\n" . substr($sql, 0, 256) . "\n\n in `$filename`", $e->getCode());
					}
				}

				$return = $this->db->insert_id ?: $this->db->affected_rows ?: $result;

				if (! $return && ! $this->db->errorCode()) {
					$return = true;
				}

				return $return;
			}
		}

		return true;
	}

	function createTables($files = []) {
		if (DB_ENGINE == 'sqlite') {
			//try to speed up install
			$query       = 'pragma journal_mode = WAL;pragma synchronous = normal;pragma temp_store = memory;pragma mmap_size = 30000000000;PRAGMA writable_schema = 1;';
			$this->db->query($query , 'journal_mode WAL');

			//check if sql file has minimum version and if current version is supported
			//$currentVersion = \SQLite3::version()['versionString'] ?? '3.0.0';
			//$fts5Support = (version_compare($currentVersion,'3.9.0') >= 0);
			$fts5Support = true;

			$query       = 'DROP TABLE IF EXISTS fts5_module_available_test';
			$this->db->query($query , 'fts5 test module');

			try {
				$query = 'CREATE VIRTUAL TABLE fts5_module_available_test USING fts5(sender, title, body)';
				$this->db->query($query , 'fts5 test module');
				$fts5Support = ! $this->db->errorCode();
			} catch (\Exception $e) {
				$fts5Support = false;
			}

			$query = 'DROP TABLE IF EXISTS fts5_module_available_test';
			$this->db->query($query , 'fts5 test module');
		}

		$glob   = ['', '*/*/', '*/'];

		//$files = glob($name, GLOB_BRACE);
		if (! $files) {
			$files = globBrace($this->sqlPath, ['', '**/', '*/*/'], '*.sql');
		}

		foreach ($files as $filename) {
			$sql      = file_get_contents($filename);
			$filename = str_replace($this->sqlPath, '', $filename);
			//remove comments
			$sql = preg_replace('/-- .+/', '', $sql);

			if (DB_ENGINE == 'mysqli' || DB_ENGINE == 'pgsql') {
				$this->multiQuery($sql, $filename);
			} else {
				if (DB_ENGINE == 'sqlite') {
					//$this->multiQuery($sql, $filename);
					//sqlite has problems running multiple queries

					//older sqlite bellow 3.9.0 do not have fts5 full text search module use fts4 instead
					$fts = \Vvveb\pregMatch('/fts(\d+).sql$/', $filename, 1);

					if ($fts) {
						if ($fts5Support) {
							if ($fts == '4') {
								continue;
							}
						} else {
							if ($fts == '5') {
								continue;
							}
						}
					}
					$queries = preg_split('/;\s*[\n\r]/', $sql);

					foreach ($queries as $query) {
						$query = trim($query);

						if (empty($query) || strncmp($query, '-- ', 3) === 0) {
							continue;
						}
						$this->multiQuery($query . ';', $filename);
					}
				}
			}
		}
	}

	function insertEscape($sql) {
		//replace ` with database escape quote eg: "
		$quote = $this->db->quote;

		if ($quote == '`') {
			return $sql;
		}

		$sql = preg_replace_callback('/INSERT\s+INTO\s*`.+?`\s*(\(.+?\))?\s*VALUES/i', function ($m) use ($quote) {
			return str_replace('`', $quote, $m[0]);
		}, $sql);

		return $sql;
	}

	function insertData($include = [], $exclude = []) {
		$glob   = ['', '*/*/', '*/'];

		//$files = glob($name, GLOB_BRACE);
		$files = globBrace($this->sqlPath, ['', '**/', '*/*/'], '*.sql');

		//expand * and transform to regex
		$includeRegex = '';

		if ($include) {
			foreach ($include as $filter) {
				if ($includeRegex) {
					$includeRegex .= '|';
				}
				$includeRegex .= str_replace('*', '.*', addslashes($filter));
			}

			$includeRegex = '/' . $includeRegex . '/';
		}

		$excludeRegex = '';

		if ($exclude) {
			foreach ($exclude as $filter) {
				if ($excludeRegex) {
					$excludeRegex .= '|';
				}
				$excludeRegex .= str_replace('*', '.*', addslashes($filter));
			}

			$excludeRegex = '/' . $excludeRegex . '/';
		}

		foreach ($files as $filename) {
			$name = basename($filename);

			if ($excludeRegex && preg_match($excludeRegex, $name)) {
				continue;
			}

			if ($includeRegex && ! preg_match($includeRegex, $name)) {
				continue;
			}

			$sql = file_get_contents($filename);
			$sql = $this->insertEscape($sql);
			$this->multiQuery($sql,  str_replace($this->sqlPath, '', $filename));
		}
	}
}