<?php
/**
 * @version        4.3.0
 * @package        Joomla
 * @subpackage     Helpdesk Pro
 * @author         Tuan Pham Ngoc
 * @copyright      Copyright (C) 2013 - 2021 Ossolution Team
 * @license        GNU/GPL, see LICENSE.php
 */

defined('_JEXEC') or die;

use Joomla\CMS\Application\ApplicationHelper;
use Joomla\CMS\Component\ComponentHelper;
use Joomla\CMS\Factory;
use Joomla\CMS\Filesystem\File;
use Joomla\CMS\Filesystem\Folder;
use Joomla\CMS\Table\Table;

class com_helpdeskproInstallerScript
{
	public static $languageFiles = ['en-GB.com_helpdeskpro.ini'];

	/**
	 * Method to run before installing the component
	 */
	public function preflight($type, $parent)
	{
		if (!version_compare(JVERSION, '3.9.0', 'ge'))
		{
			Factory::getApplication()->enqueueMessage('Cannot install Helpdesk Pro in a Joomla release prior to 3.9.0', 'warning');

			return false;
		}

		//Backup the old language file
		foreach (self::$languageFiles as $languageFile)
		{
			if (File::exists(JPATH_ROOT . '/language/en-GB/' . $languageFile))
			{
				File::copy(JPATH_ROOT . '/language/en-GB/' . $languageFile, JPATH_ROOT . '/language/en-GB/bak.' . $languageFile);
			}
		}

		// Files, Folders clean up
		$deleteFiles = [
			JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/controller.php',
			JPATH_ROOT . '/components/com_helpdeskpro/controller.php',
			JPATH_ROOT . '/components/com_helpdeskpro/helper/fields.php',
		];

		$deleteFolders = [
			JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/assets',
			JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/controllers',
			JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/libraries',
			JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/models',
			JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/tables',
			JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/views',
			JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/controller',
			JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/dispatcher',
			JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/model',
			JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/view',
			JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/table',
			JPATH_ROOT . '/components/com_helpdeskpro/controller',
			JPATH_ROOT . '/components/com_helpdeskpro/dispatcher',
			JPATH_ROOT . '/components/com_helpdeskpro/helper',
			JPATH_ROOT . '/components/com_helpdeskpro/model',
			JPATH_ROOT . '/components/com_helpdeskpro/view',
			JPATH_ROOT . '/components/com_helpdeskpro/assets',
			JPATH_ROOT . '/components/com_helpdeskpro/views',
		];

		foreach ($deleteFiles as $file)
		{
			if (File::exists($file))
			{
				File::delete($file);
			}
		}

		foreach ($deleteFolders as $folder)
		{
			if (Folder::exists($folder))
			{
				Folder::delete($folder);
			}
		}
	}

	/**
	 * method to install the component
	 *
	 * @return void
	 */
	public function install($parent)
	{
		self::updateDatabaseSchema();
	}

	/**
	 * Method run when update the component
	 *
	 * @param $parent
	 */
	public function update($parent)
	{
		self::updateDatabaseSchema();
	}

	/**
	 * Update db schema to latest version
	 */
	public static function updateDatabaseSchema()
	{
		$db    = Factory::getDbo();
		$query = $db->getQuery(true);
		$sql   = 'SELECT COUNT(*) FROM #__helpdeskpro_configs';
		$db->setQuery($sql);
		$total = $db->loadResult();

		if (!$total)
		{
			$configSql = JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/sql/config.helpdeskpro.sql';
			self::executeSqlFile($configSql);
		}

		$fields = array_keys($db->getTableColumns('#__helpdeskpro_categories'));

		if (!in_array('level', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_categories` ADD  `level` TINYINT( 4 ) NOT NULL DEFAULT '1';";
			$db->setQuery($sql);
			$db->execute();

			// Update level for categories
			$query->clear()
				->select('id, `parent_id`');
			$query->from('#__helpdeskpro_categories');
			$db->setQuery($query);
			$rows = $db->loadObjectList();

			// first pass - collect children
			if (count($rows))
			{
				$children = [];
				foreach ($rows as $v)
				{
					$pt   = (int) $v->parent_id;
					$list = @$children[$pt] ? $children[$pt] : [];
					array_push($list, $v);
					$children[$pt] = $list;
				}

				$list = self::calculateCategoriesLevel(0, [], $children, 4);

				foreach ($list as $id => $category)
				{
					$sql = "UPDATE #__helpdeskpro_categories SET `level` = $category->level WHERE id = $id";
					$db->setQuery($sql);
					$db->execute();
				}
			}
		}

		if (!in_array('category_type', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_categories` ADD  `category_type` INT NOT NULL DEFAULT  '0' ;";
			$db->setQuery($sql);
			$db->execute();

			$sql = 'UPDATE #__helpdeskpro_categories SET category_type = 1';
			$db->setQuery($sql);
			$db->execute();
		}

		if (!in_array('alias', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_categories` ADD  `alias` VARCHAR( 255 ) NULL;;";
			$db->setQuery($sql);
			$db->execute();

			$query->clear()
				->select('id, title')
				->from('#__helpdeskpro_categories');
			$db->setQuery($query);
			$rows = $db->loadObjectList();

			foreach ($rows as $row)
			{
				$alias = ApplicationHelper::stringURLSafe($row->title);
				$query->clear()
					->update('#__helpdeskpro_categories')
					->set('alias  = ' . $db->quote($alias))
					->where('id = ' . $row->id);
				$db->setQuery($query)
					->execute();
			}
		}


		$fields = array_keys($db->getTableColumns('#__helpdeskpro_fields'));

		if (!in_array('multiple', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_fields` ADD  `multiple` TINYINT NOT NULL DEFAULT  '0';";
			$db->setQuery($sql);
			$db->execute();
		}

		if (!in_array('validation_rules', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_fields` ADD  `validation_rules` VARCHAR( 255 ) NULL;";
			$db->setQuery($sql);
			$db->execute();
		}

		if (!in_array('fieldtype', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_fields` ADD  `fieldtype` VARCHAR( 50 ) NULL;";
			$db->setQuery($sql);
			$db->execute();

			$fieldTypes = [
				0 => 'Text',
				1 => 'Textarea',
				2 => 'List',
				3 => 'Checkboxes',
				4 => 'Radio',
				5 => 'Date',
				6 => 'Heading',
				7 => 'Message',
			];

			foreach ($fieldTypes as $key => $value)
			{
				$sql = "UPDATE #__helpdeskpro_fields SET fieldtype='$value' WHERE field_type='$key'";
				$db->setQuery($sql);
				$db->execute();
			}

			$sql = "UPDATE #__helpdeskpro_fields SET fieldtype='List', multiple=1 WHERE field_type='8'";
			$db->setQuery($sql);
			$db->execute();
		}

		$fields = array_keys($db->getTableColumns('#__helpdeskpro_tickets'));
		if (!in_array('label_id', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_tickets` ADD  `label_id` INT NOT NULL DEFAULT  '0' ;";
			$db->setQuery($sql);
			$db->execute();
		}

		if (!in_array('staff_id', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_tickets` ADD  `staff_id` INT NOT NULL DEFAULT  '0' ;";
			$db->setQuery($sql);
			$db->execute();
		}

		if (!in_array('language', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_tickets` ADD  `language` VARCHAR( 10 ) NULL DEFAULT  '*';";
			$db->setQuery($sql);
			$db->execute();
		}

		$fields = array_keys($db->getTableColumns('#__helpdeskpro_fields'));

		if (!in_array('is_searchable', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_fields` ADD  `is_searchable` TINYINT NOT NULL DEFAULT  '0' ;";
			$db->setQuery($sql);
			$db->execute();
		}

		if (!in_array('show_in_list_view', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_fields` ADD  `show_in_list_view` TINYINT NOT NULL DEFAULT  '0' ;";
			$db->setQuery($sql);
			$db->execute();
		}

		//Create the two new tables
		$sql = 'CREATE TABLE IF NOT EXISTS `#__helpdeskpro_urls` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `md5_key` varchar(32) DEFAULT NULL,
		  `query` text,
		  `segment` text,		  
		  PRIMARY KEY (`id`)
		  ) CHARACTER SET `utf8`;';
		$db->setQuery($sql)
			->execute();

		$sql = 'CREATE TABLE IF NOT EXISTS `#__helpdeskpro_labels` (
		  `id` int(11) NOT NULL AUTO_INCREMENT,
		  `title` varchar(255) DEFAULT NULL,
		  `color_code` varchar(40) DEFAULT NULL,
		  `published` tinyint(3) unsigned DEFAULT NULL,
		  PRIMARY KEY (`id`)
		  ) CHARACTER SET `utf8`;';

		$db->setQuery($sql);
		$db->execute();

		$sql = 'CREATE TABLE IF NOT EXISTS `#__helpdeskpro_replies` (
		  `id` int(11) NOT NULL AUTO_INCREMENT,
		  `title` varchar(225) NOT NULL,
		  `message` text,
		  `published` tinyint(3) unsigned DEFAULT NULL,
		  `ordering` int(11) DEFAULT NULL,
		  PRIMARY KEY (`id`)
		) CHARACTER SET `utf8`;';

		$db->setQuery($sql);
		$db->execute();

		$sql = 'CREATE TABLE IF NOT EXISTS `#__helpdeskpro_emails` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `email_key` VARCHAR(255) NULL,
			  `email_message` TEXT NULL,
			  PRIMARY KEY(`id`)
			)CHARACTER SET `utf8`;';

		$db->setQuery($sql);
		$db->execute();

		$sql = 'CREATE TABLE IF NOT EXISTS `#__helpdeskpro_articles` (
		  `id` int(11) NOT NULL AUTO_INCREMENT,
		  `category_id` int(11) DEFAULT 0,
		  `ticket_id` int(11) DEFAULT 0,
		  `title` varchar(225) NOT NULL,
		  `text` text,
		  `published` tinyint(3) unsigned DEFAULT NULL,
		  `hits` int(11) DEFAULT 0,
		  `ordering` int(11) DEFAULT NULL,
		  PRIMARY KEY (`id`)
		) CHARACTER SET `utf8`;';

		$db->setQuery($sql);
		$db->execute();

		$sql = 'SELECT COUNT(*) FROM #__helpdeskpro_emails';
		$db->setQuery($sql);
		$total = $db->loadResult();

		if (!$total)
		{
			require_once JPATH_ROOT . '/components/com_helpdeskpro/Helper/Helper.php';
			require_once JPATH_ROOT . '/libraries/osl/Config/Config.php';

			//Migrate data from config table
			$config = OSSolution\HelpdeskPro\Site\Helper\Helper::getConfig();
			require_once JPATH_ADMINISTRATOR . '/components/com_helpdeskpro/Table/Email.php';
			$row  = new OSSolution\HelpdeskPro\Admin\Table\Email($db);
			$keys = [
				'new_ticket_admin_email_subject',
				'new_ticket_admin_email_body',
				'new_ticket_user_email_subject',
				'new_ticket_user_email_body',
				'ticket_updated_admin_email_subject',
				'ticket_updated_admin_email_body',
				'ticket_updated_user_email_subject',
				'ticket_updated_user_email_body',
				'ticket_assiged_email_subject',
				'ticket_assiged_email_body',
			];
			foreach ($keys as $key)
			{
				$row->id            = 0;
				$row->email_key     = $key;
				$row->email_message = $config->{$key};
				$row->store();
			}
		}

		$query = $db->getQuery(true);
		$query->update('#__extensions')
			->set('enabled = 1')
			->where('element = "helpdeskpro"')
			->where('folder = "installer"');
		$db->setQuery($query)
			->execute();

		$sql = 'ALTER TABLE #__helpdeskpro_field_value MODIFY `field_value` TEXT;';
		$db->setQuery($sql);
		$db->execute();

		// Articles
		$fields = array_keys($db->getTableColumns('#__helpdeskpro_articles'));

		if (!in_array('ticket_id', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_articles` ADD  `ticket_id` INT NOT NULL DEFAULT  '0' ;";
			$db->setQuery($sql);
			$db->execute();
		}

		if (!in_array('alias', $fields))
		{
			$sql = "ALTER TABLE  `#__helpdeskpro_articles` ADD  `alias` VARCHAR( 255 ) NULL;;";
			$db->setQuery($sql);
			$db->execute();

			$query->clear()
				->select('id, title')
				->from('#__helpdeskpro_articles');
			$db->setQuery($query);
			$rows = $db->loadObjectList();

			foreach ($rows as $row)
			{
				$alias = ApplicationHelper::stringURLSafe($row->title);
				$query->clear()
					->update('#__helpdeskpro_articles')
					->set('alias  = ' . $db->quote($alias))
					->where('id = ' . $row->id);
				$db->setQuery($query)
					->execute();
			}
		}

		// Convert knowledge base articles links
		$component = ComponentHelper::getComponent('com_helpdeskpro');
		$menus     = Factory::getApplication()->getMenu('site');
		$items     = $menus->getItems(['component_id', 'client_id'], [$component->id, 0]);
		$query     = $db->getQuery(true);

		foreach ($items as $item)
		{
			if (!isset($item->query['filter_category_id']))
			{
				continue;
			}

			$link = str_replace('filter_category_id', 'id', $item->link);
			$query->update('#__menu')
				->set('link = ' . $db->quote($link))
				->where('id = ' . $item->id);
			$db->setQuery($query)
				->execute();
		}
	}

	/**
	 * Method to run after installing the component
	 */
	public function postflight($type, $parent)
	{
		//Restore the modified language strings by merging to language files
		$registry = new JRegistry();

		foreach (self::$languageFiles as $languageFile)
		{
			$backupFile  = JPATH_ROOT . '/language/en-GB/bak.' . $languageFile;
			$currentFile = JPATH_ROOT . '/language/en-GB/' . $languageFile;

			if (File::exists($currentFile) && File::exists($backupFile))
			{
				$registry->loadFile($currentFile, 'INI');
				$currentItems = $registry->toArray();
				$registry->loadFile($backupFile, 'INI');
				$backupItems = $registry->toArray();
				$items       = array_merge($currentItems, $backupItems);
				$content     = "";

				foreach ($items as $key => $value)
				{
					$content .= "$key=\"$value\"\n";
				}

				File::write($currentFile, $content);
			}
		}

		// Create custom.css file
		$customCss = JPATH_ROOT . '/media/com_helpdeskpro/assets/css/custom.css';

		if (!file_exists($customCss))
		{
			$fp = fopen($customCss, 'w');
			fclose($fp);
			@chmod($customCss, 0777);
		}

		// Rename permission, workaround for Joomla 3.5
		$asset = Table::getInstance('asset');
		$asset->loadByName('com_helpdeskpro');

		if ($asset)
		{
			$rules        = $asset->rules;
			$rules        = str_replace('helpdeskpro.change_ticket_category', 'helpdeskpro.changeticketcategory', $rules);
			$rules        = str_replace('helpdeskpro.change_ticket_status', 'helpdeskpro.changeticketstatus', $rules);
			$asset->rules = $rules;
			$asset->store();
		}
	}

	/**
	 * Calculate level for categories, used when upgrade from old version to new version
	 *
	 * @param        $id
	 * @param        $list
	 * @param        $children
	 * @param   int  $maxLevel
	 * @param   int  $level
	 *
	 * @return mixed
	 */
	public static function calculateCategoriesLevel($id, $list, &$children, $maxLevel = 9999, $level = 1)
	{
		if (@$children[$id] && $level <= $maxLevel)
		{
			foreach ($children[$id] as $v)
			{
				$id        = $v->id;
				$v->level  = $level;
				$list[$id] = $v;
				$list      = self::calculateCategoriesLevel($id, $list, $children, $maxLevel, $level + 1);
			}
		}

		return $list;
	}

	/**
	 * Execute queries contained in a SQL file
	 *
	 * @param   string  $file  Path to the file contains queries need to be executed
	 */
	public static function executeSqlFile($file)
	{
		$db      = Factory::getDbo();
		$sql     = file_get_contents($file);
		$queries = $db->splitSql($sql);

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

			if ($query != '' && $query[0] != '#')
			{
				$db->setQuery($query)
					->execute();
			}
		}
	}
}