<?php
/** 
 * @package     VikBooking
 * @subpackage  core
 * @author      E4J s.r.l.
 * @copyright   Copyright (C) 2021 E4J s.r.l. All Rights Reserved.
 * @license     http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
 * @link        https://vikwp.com
 */

// No direct access
defined('ABSPATH') or die('No script kiddies please!');

/**
 * SQL Backup export rule.
 * 
 * @since 1.5
 */
class VBOBackupExportRuleSql extends VBOBackupExportRule
{
	/**
	 * An array of SQL statements.
	 * 
	 * @var array
	 */
	protected $queries = [];

	/**
	 * The database table.
	 * 
	 * @var string
	 */
	protected $table;

	/**
	 * The information of the database columns.
	 * 
	 * @var object
	 */
	private $columns;

	/**
	 * Indicates the maximum number of rows under the same INSERT.
	 * 
	 * @var integer
	 */
	private $maxRowsPerInsert = 100;

	/**
	 * Returns the rules instructions.
	 * 
	 * @return 	mixed
	 */
	public function getData()
	{
		return $this->queries;
	}

	/**
	 * Configures the rule to work according to the specified data.
	 * 
	 * @param 	string 	$data  The database table name.
	 * 
	 * @return 	void
	 */
	protected function setup($data)
	{
		$dbo = JFactory::getDbo();

		// register table
		$this->table = $data;

		// get all the columns of the table to export
		$this->columns = $dbo->getTableColumns($this->table, $typeOnly = false);

		// get total count of records
		$count = $this->getCount();

		if (!$count)
		{
			// nothing to export
			return;
		}

		// get current database prefix
		$prefix = $dbo->getPrefix();

		// shows the table CREATE statement that creates the given table
		$createLookup = $dbo->getTableCreate($this->table);

		// check whether the current drivers supports a tool to return the statement that
		// was used to create the database table
		if (isset($createLookup[$this->table]))
		{
			// extract statement from create lookup and replace prefix
			$create = preg_replace("/`{$prefix}(vik(?:booking|channelmanager)_(?:[a-z0-9_]+))`/i", '`#__$1`', $createLookup[$this->table]);

			// register query to recreate the table from scratch
			$this->registerQuery("DROP TABLE IF EXISTS `{$this->table}`");
			$this->registerQuery($create);

			// we don't need to alter the auto increment because it is already included
			// within the create table statement
			$alter_auto_increment = false;
		}
		else
		{
			// cannot fetch create table statment, truncate the table and assume (or "hope")
			// that the database structure is the same
			$this->registerQuery("TRUNCATE TABLE `$this->table`");

			// update auto increment after copying all the records
			$alter_auto_increment = true;
		}

		$insertQuery = $dbo->getQuery(true);

		// prepare INSERT query
		$insertQuery->insert($dbo->qn($this->table));

		$app = JFactory::getApplication();

		/**
		 * Trigger event to allow third party plugins to choose what are the columns to dump
		 * and whether the table should be skipped or not.
		 * 
		 * Fires while attaching a rule to dump some SQL statements.
		 * 
		 * @param 	array    &$columns  An associative array of supported database table columns,
		 *                              where the key is the column name and the value is a nested
		 *                              array holding the column information.
		 * @param 	string   $table     The name of the database table.
		 * 
		 * @return 	boolean  False to avoid including the table into the backup.
		 * 
		 * @since 	1.5
		 */
		$results = $app->triggerEvent('onBeforeBackupDumpSqlVikBooking', [&$this->columns, $this->table]);

		if (in_array(false, $results, true))
		{
			// a third-party plugin decided to skip the table
			return;
		}

		// iterate the columns
		foreach ($this->columns as $column => $type)
		{
			$insertQuery->columns($dbo->qn($column));
		}

		// create SELECT query
		$selectQuery = $dbo->getQuery(true)->select('*')->from($dbo->qn($this->table));

		$offset = 0;

		while ($offset < $count)
		{
			$dbo->setQuery($selectQuery, $offset, $this->maxRowsPerInsert);
			$dbo->execute();

			$rows = $dbo->loadObjectList();

			// clear previous values
			$insertQuery->clear('values');

			foreach ($rows as $row)
			{
				$values = array();

				foreach ($this->columns as $k => $type)
				{
					if (!isset($row->{$k}))
					{
						// use NULL operator
						$values[] = 'NULL';
					}
					else
					{
						// escape the value
						$values[] = $dbo->q($row->{$k});
					}
				}

				$insertQuery->values(implode(',', $values));
			}

			// register query
			$this->registerQuery((string) $insertQuery);

			// increase offset
			$offset += $this->maxRowsPerInsert;

			// free space
			unset($rows);
		}

		// fetch table auto increment
		if ($alter_auto_increment && ($ai = $this->getAutoIncrement($this->table)))
		{
			$this->registerQuery("ALTER TABLE `{$this->table}` AUTO_INCREMENT = {$ai}");
		}
	}

	/**
	 * Helper method used to register the query inside the buffer.
	 * 
	 * @param 	string 	$query  The query to register.
	 * 
	 * @return 	void
	 */
	protected function registerQuery($query)
	{
		/**
		 * @wponly
		 * 
		 * While escaping a SQL string, WordPress replaces any "%" character with a random hash.
		 * Normally that hash is automatically unescaped while executing the query, but in our case,
		 * since we are dumping the query for later use, we have to manually unescape it, otherwise
		 * the WordPress website that is going to import the dump won't be able to revert the hash
		 * back, because the latter changes at every page loading.
		 * 
		 * @since 1.6
		 */
		if (VBOPlatformDetection::isWordPress())
		{
			$query = JFactory::getDbo()->remove_placeholder_escape($query);
		}

		if (!preg_match("/;$/", $query))
		{
			$query .= ';';
		}

		$this->queries[] = $query;
	}

	/**
	 * Counts the total number of rows inside the table.
	 * 
	 * @return 	integer
	 */
	private function getCount()
	{
		$dbo = JFactory::getDbo();

		// count rows
		$q = $dbo->getQuery(true)->select('COUNT(1)')->from($dbo->qn($this->table));

		$dbo->setQuery($q);
		$dbo->execute();

		return (int) $dbo->loadResult();
	}

	/**
	 * Fetches the correct auto increment to set for the given table.
	 * 
	 * @return 	mixed   The auto increment on success, null otherwise.
	 */
	private function getAutoIncrement()
	{
		$dbo = JFactory::getDbo();

		$pk = null;

		// look for the primary key
		foreach ($this->columns as $column => $info)
		{
			if ($info->Extra === 'auto_increment')
			{
				$pk = $column;
			}
		}

		if (!$pk)
		{
			// no primary keys with auto increment
			return null;
		}

		// fetch highest ID
		$q = $dbo->getQuery(true)->select('MAX(' . $dbo->qn($pk) . ')')->from($dbo->qn($this->table));

		$dbo->setQuery($q);
		$dbo->execute();

		return (int) $dbo->loadResult() + 1;
	}
}
