<?php
namespace App\Cron\Job;

/**
 * Copies all tables structure and content from RD to a new database available for the same db user.
 * @author Gabriel
 */
class BiDatabaseSync extends \App\Cron\Job\Utils\LongRunningCronJobAbstract
{

	/**
	 * Cronjob category: {system|operational|financial|reporting}
	 * @var string
	 */
	protected $category = 'reporting';


	/**
	 * Name of the application used for blocking RD
	 * @var string
	 */
	private $reportsDataBlockApplicationName = "BI Resync";

	/**
	 * (non-PHPdoc)
	 * @see \App\Cron\Job\Utils\GeneralAbstract::getSettingsDialog()
	 */
	public function getSettingsDialog(array $fields)
	{
		$form = parent::getSettingsDialog($fields);

		$_LANG = \App\Loader_Lang::getInstance()->getGlobal();
		$lang = \App\Loader_Lang::getInstance()->getSection("Admin/CronJobs/Management/BiDatabaseSync");

		$formContainer = \Engine\Form\Container\Fieldset::create($_LANG['Admin']['CronJobs']['Management']['formFields']['legend']);

		$form->addContainer($formContainer);

		$fld = new \Engine\Form\Input\Text($lang['options']['biDatabaseName'], 'biDatabaseName', $fields['settings']['biDatabaseName']);
		$fld->setTooltip($lang['options']['biDatabaseNameTooltip']);
		$fld->setDetails($lang['options']['biDatabaseNameDetails']);
		$fld->setMandatory(true);
		$formContainer->addElement($fld);

		$formContainer->groupInputsInArray("settings");
		return $form;
	}


	/**
	 * {@inheritDoc}
	 * @see \App\Cron\Job\Utils\GeneralAbstract::validateSettings()
	 */
	public function validateSettings(\App\Entity\Messages $msgs, array $fields)
	{

		$lang = \App\Loader_Lang::getInstance()->getSection("Admin/CronJobs/Management/BiDatabaseSync");

		// VALIDATE THE DATABASE NAME -------------------------------------------------------------
		if (!$fields['settings']['biDatabaseName']) {
			$msgs->addWarningText($lang['errors']['noDbName']);
		}

		// CHECK THAT THE CRON DOES NOT RUN MORE THAN ONCE PER DAY --------------------------------
		if ($fields['scheduleOptions']['type']!='scheduled') {
			$msgs->addWarningText($lang['errors']['invalidSchedule']);
		}

	}

	/**
	 *
	 * {@inheritDoc}
	 * @see \App\Cron\Job\Utils\GeneralAbstract::executeJob()
	 */
	protected function executeJob()
	{

		$db = \DB::getInstance();
		$biDatabaseName = $this->settings['biDatabaseName'];

		// CHECK BI DATABASE //////////////////////////////////////////////////////////////////////
		if (!$biDatabaseName) {
			$this->logger->warning("You have not set the external BI database name.");
			throw new \Exception\CronJob\StopJob('No BI database name set.');
		}
		if ($db->database==$biDatabaseName) {
			$this->logger->error("You must NOT select the production database for the BI integration. Please select another database name.");
			throw new \Exception\CronJob\StopJob('Invalid BI database name set - it cannot be the same as the production.');
		}

		// CHECK RD STATUS ////////////////////////////////////////////////////////////////////////
		if (\App\ReportsData\Params::getInstance()->getStatus() == "disabled") {
			$this->logger->warning("The Reports Data cronjob is disabled.");
			throw new \Exception\CronJob\StopJob('RD cron is disabled.');
		}

		// CHECK IF THE RD STATUS FROM FAST AND MAIN QUEUES DID NOT STOP FOR A PERIOD OF 3 DAYS ---
		$dateValidityThreshold = \Engine\DateTime::create('-1 days')->formatTime();
		$reportsDataObj = \App\ReportsData\ChangesQueue\Manager::getInstance();
		$mainQueueOldestRecord = $reportsDataObj->getOldestRecordMainQueue()['addTime'];
		$fastQueueOldestRecords = $reportsDataObj->getOldestRecordFastQueue()['addTime'];

		if ($mainQueueOldestRecord and $mainQueueOldestRecord < $dateValidityThreshold) {
			$this->logger->warning("The Reports Data cronjob did not run for more than 24 hours (there are records in the main queue older than 24h). Make sure that the Reports Data cron is up and running.");
			throw new \Exception\CronJob\StopJob('RD main queue not up to date.');
		}

		if ($fastQueueOldestRecords and $fastQueueOldestRecords < $dateValidityThreshold) {
			$this->logger->warning("The Reports Data cronjob did not run for more than 24 hours (there are records in the fast queue older than 24h). Make sure that the Reports Data cron is up and running.");
			throw new \Exception\CronJob\StopJob('RD fast queue not up to date.');
		}


		// keep RD still blocked
		$reportsDataStatusObj = \App\ReportsData\Params::getAdmin();
		$reportsDataStatusObj->saveStatusToBlocked(20*60, $this->reportsDataBlockApplicationName);


		// DROP ALL OLD TABLES FROM BI ////////////////////////////////////////////////////////////

		$tablesList = $this->getSyncingTables();

		$this->logger->info('Drop old tables from BI database');

		foreach ($tablesList as $tinaTableName=>$biTableName) {
			$db->Execute("DROP TABLE IF EXISTS `{$biDatabaseName}`.`{$biTableName}`");
			if ($db->ErrorNo()) {
				$this->logger->critical(sprintf('Could not delete old table from BI database named `%s`. Error message: %s.', $biTableName, $db->ErrorMsg()));
				\Log::getInstance()->dbError(sprintf('Could not delete old table from BI database named `%s`.', $biTableName));
				throw new \Exception\CronJob\StopJob('SQL error for table drop.');
			}
		}

		$this->logger->success("Successfully dropped previous BI tables.");



		// GO TROUGH ALL RD TABLES AND COPY THEM INTO THE NEW BI DATABASE /////////////////////////

		$this->logger->info('Start copying tables content to BI database.');
		$tblIdx = 0;
		foreach ($tablesList as $tinaTableName=>$biTableName) {

			$tblIdx++;
			$this->updateStatus(['progress'=>$tblIdx/count($tablesList)]);

			// keep RD still blocked
			$reportsDataStatusObj->saveStatusToBlocked(8*60, $this->reportsDataBlockApplicationName);

			// CREATE TABLE WITH THE SAME STRUCTURE -----------------------------------------------
			$db->Execute("CREATE TABLE `{$biDatabaseName}`.`{$biTableName}` LIKE `{$tinaTableName}`");
			if ($db->ErrorNo()) {
				$this->logger->critical(sprintf('Could not create new table into BI database named `%s`. Error message: %s.', $biTableName, $db->ErrorMsg()));
				\Log::getInstance()->dbError(sprintf('Could not create the new table `%s` into BI database.', $biTableName));
				throw new \Exception\CronJob\StopJob('SQL error for table create.');
			}

			// COPY TABLE CONTENT FROM TINA DB TABLE ----------------------------------------------
			$db->Execute("INSERT INTO `{$biDatabaseName}`.`{$biTableName}` SELECT * FROM `{$tinaTableName}`");
			if ($db->ErrorNo()) {
				$this->logger->critical(sprintf('Could not insert new table into BI database named `%s`. Error message: %s.', $biTableName, $db->ErrorMsg()));
				\Log::getInstance()->dbError(sprintf('Could not copy the content for table `%s` into the BI database.', $biTableName));
				throw new \Exception\CronJob\StopJob('SQL error for table insert.');
			}

			// add success
			$this->logger->success(sprintf("Successfully synced table `%s`.", $biTableName));

		}


	}



	/**
	 * Returns the list of RD tables (associative array of TINA DB table name => BI DB table name).
	 * @return array
	 */
	private function getSyncingTables()
	{

		global $DBTRd_activityDomains, $DBTRd_activityDomainCategories, $DBTRd_agencyBankAccounts, $DBTRd_autoProcessRules, $DBTRd_balances, $DBTRd_banks, $DBTRd_bankOffices;
		global $DBTRd_cashbookDocuments, $DBTRd_cities, $DBTRd_clientInvoices, $DBTRd_clientInvoiceRows, $DBTRd_clientInvoiceCashings, $DBTRd_contactPersons;
		global $DBTRd_corporates, $DBTRd_corporateOffices, $DBTRd_corporateSegments, $DBTRd_corporateGroups, $DBTRd_corporateContacts, $DBTRd_corporateContracts, $DBTRd_currencyRates, $DBTRd_currencyReferences;
		global $DBTRd_customers, $DBTRd_customerPassports, $DBTRd_customerSegments, $DBTRd_countries, $DBTRd_departments, $DBTRd_documents, $DBTRd_docSeries, $DBTRd_docTemplates;
		global $DBTRd_importedOrderSystems, $DBTRd_importedOrders, $DBTRd_importedOrderServices, $DBTRd_importedDocSystems, $DBTRd_importedDocs;
		global $DBTRd_miceProjects, $DBTRd_miceProjectTypes, $DBTRd_offers, $DBTRd_offices,$DBTRd_orders, $DBTRd_services, $DBTRd_stations;
		global $DBTRd_suppliers, $DBTRd_supplierProducts, $DBTRd_supplierContracts, $DBTRd_supplierInvoices, $DBTRd_supplierInvoiceRows, $DBTRd_supplierInvoicePayments, $DBTRd_supplierGroups, $DBTRd_trusts, $DBTRd_users, $DBTRd_userProfiles;

		$tablesList = [
			$DBTRd_activityDomains, $DBTRd_activityDomainCategories, $DBTRd_agencyBankAccounts, $DBTRd_autoProcessRules, $DBTRd_balances, $DBTRd_banks, $DBTRd_bankOffices,
			$DBTRd_cashbookDocuments, $DBTRd_cities, $DBTRd_clientInvoices, $DBTRd_clientInvoiceRows, $DBTRd_clientInvoiceCashings, $DBTRd_contactPersons,
			$DBTRd_corporates, $DBTRd_corporateOffices, $DBTRd_corporateSegments, $DBTRd_corporateGroups, $DBTRd_corporateContacts, $DBTRd_corporateContracts, $DBTRd_currencyRates, $DBTRd_currencyReferences,
			$DBTRd_customers, $DBTRd_customerPassports, $DBTRd_customerSegments, $DBTRd_countries, $DBTRd_departments, $DBTRd_documents, $DBTRd_docSeries, $DBTRd_docTemplates,
			$DBTRd_importedOrderSystems, $DBTRd_importedOrders, $DBTRd_importedOrderServices, $DBTRd_importedDocSystems, $DBTRd_importedDocs,
			$DBTRd_miceProjects, $DBTRd_miceProjectTypes, $DBTRd_offers, $DBTRd_offices,$DBTRd_orders, $DBTRd_services, $DBTRd_stations,
			$DBTRd_suppliers, $DBTRd_supplierProducts, $DBTRd_supplierContracts, $DBTRd_supplierInvoices, $DBTRd_supplierInvoiceRows, $DBTRd_supplierInvoicePayments, $DBTRd_supplierGroups, $DBTRd_trusts, $DBTRd_users, $DBTRd_userProfiles
		];

		// table names are the same without the leading "rd_"
		$return = [];
		foreach ($tablesList as $tbl) {
			if (!$tbl or substr($tbl, 0, 3)!='rd_') {
				\Log::getInstance()->dbError( sprintf('One of the RD tables is empty or does not start with `rd_` (table name: %s)', $tbl) );
				throw new \Exception\CronJob\StopJob('SQL error for table insert.');
			}
			$return[$tbl] = substr($tbl, 3);
		}

		// add extra tables
		$return['importedOrders_matches'] = 'impOrderMatches';
		$return['importedDocuments_matches'] = 'impDocMatches';

		return $return;

	}

}

