<?php
	// check this file's MD5 to make sure it wasn't called before
	$tenantId = Authentication::tenantIdPadded();
	$setupHash = __DIR__ . "/setup{$tenantId}.md5";

	$prevMD5 = @file_get_contents($setupHash);
	$thisMD5 = md5_file(__FILE__);

	// check if this setup file already run
	if($thisMD5 != $prevMD5) {
		// set up tables
		setupTable(
			'patients', " 
			CREATE TABLE IF NOT EXISTS `patients` ( 
				`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
				PRIMARY KEY (`id`),
				`last_name` VARCHAR(40) NOT NULL,
				`first_name` VARCHAR(40) NOT NULL,
				`gender` VARCHAR(10) NOT NULL DEFAULT 'Unknown',
				`sexual_orientation` TEXT NOT NULL,
				`birth_date` DATE NULL,
				`age` INT NULL,
				`image` VARCHAR(40) NULL,
				`address` TEXT NULL,
				`city` VARCHAR(40) NULL,
				`state` VARCHAR(15) NULL,
				`zip` CHAR(8) NULL,
				`home_phone` VARCHAR(40) NULL,
				`work_phone` VARCHAR(40) NULL,
				`mobile` VARCHAR(40) NULL,
				`tobacco_usage` VARCHAR(40) NOT NULL DEFAULT 'Unknown',
				`alcohol_intake` VARCHAR(40) NOT NULL DEFAULT 'Unknown',
				`history` VARCHAR(100) NOT NULL DEFAULT 'Unknown',
				`surgical_history` TEXT NULL,
				`obstetric_history` TEXT NULL,
				`genetic_diseases` TEXT NULL,
				`contact_person` VARCHAR(100) NULL,
				`other_details` TEXT NULL,
				`comments` TEXT NULL,
				`filed` DATETIME NULL,
				`last_modified` DATETIME NULL
			) CHARSET latin1"
		);

		setupTable(
			'disease_symptoms', " 
			CREATE TABLE IF NOT EXISTS `disease_symptoms` ( 
				`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
				PRIMARY KEY (`id`),
				`disease` VARCHAR(200) NULL,
				`symptoms` TEXT NULL,
				`reference` TEXT NULL
			) CHARSET latin1"
		);

		setupTable(
			'medical_records', " 
			CREATE TABLE IF NOT EXISTS `medical_records` ( 
				`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
				PRIMARY KEY (`id`),
				`patient` INT UNSIGNED NULL,
				`image_1` VARCHAR(40) NULL,
				`image_2` VARCHAR(40) NULL,
				`image_3` VARCHAR(40) NULL,
				`image_4` VARCHAR(40) NULL,
				`image_5` VARCHAR(40) NULL,
				`document_1` VARCHAR(40) NULL,
				`document_2` VARCHAR(40) NULL,
				`document_3` VARCHAR(40) NULL,
				`document_4` VARCHAR(40) NULL,
				`document_5` VARCHAR(40) NULL,
				`description` TEXT NULL
			) CHARSET latin1"
		);
		setupIndexes('medical_records', ['patient',]);

		setupTable(
			'events', " 
			CREATE TABLE IF NOT EXISTS `events` ( 
				`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
				PRIMARY KEY (`id`),
				`title` VARCHAR(40) NULL,
				`date` DATE NULL,
				`status` VARCHAR(40) NOT NULL,
				`name_patient` INT UNSIGNED NULL,
				`time` TIME NULL DEFAULT '12:00',
				`prescription` VARCHAR(40) NULL,
				`diagnosis` VARCHAR(40) NULL,
				`comments` TEXT NULL
			) CHARSET latin1"
		);
		setupIndexes('events', ['name_patient',]);



		// save MD5
		@file_put_contents($setupHash, $thisMD5);
	}


	function setupIndexes($tableName, $arrFields) {
		if(!is_array($arrFields) || !count($arrFields)) return false;

		foreach($arrFields as $fieldName) {
			if(!$res = @db_query("SHOW COLUMNS FROM `$tableName` like '$fieldName'")) continue;
			if(!$row = @db_fetch_assoc($res)) continue;
			if($row['Key']) continue;

			@db_query("ALTER TABLE `$tableName` ADD INDEX `$fieldName` (`$fieldName`)");
		}
	}


	function setupTable($tableName, $createSQL = '', $arrAlter = '') {
		global $Translation;
		$oldTableName = '';
		ob_start();

		echo '<div style="padding: 5px; border-bottom:solid 1px silver; font-family: verdana, arial; font-size: 10px;">';

		// is there a table rename query?
		if(is_array($arrAlter)) {
			$matches = [];
			if(preg_match("/ALTER TABLE `(.*)` RENAME `$tableName`/i", $arrAlter[0], $matches)) {
				$oldTableName = $matches[1];
			}
		}

		if($res = @db_query("SELECT COUNT(1) FROM `$tableName`")) { // table already exists
			if($row = @db_fetch_array($res)) {
				echo str_replace(['<TableName>', '<NumRecords>'], [$tableName, $row[0]], $Translation['table exists']);
				if(is_array($arrAlter)) {
					echo '<br>';
					foreach($arrAlter as $alter) {
						if($alter != '') {
							echo "$alter ... ";
							if(!@db_query($alter)) {
								echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
								echo '<div class="text-danger">' . $Translation['mysql said'] . ' ' . db_error(db_link()) . '</div>';
							} else {
								echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
							}
						}
					}
				} else {
					echo $Translation['table uptodate'];
				}
			} else {
				echo str_replace('<TableName>', $tableName, $Translation['couldnt count']);
			}
		} else { // given tableName doesn't exist

			if($oldTableName != '') { // if we have a table rename query
				if($ro = @db_query("SELECT COUNT(1) FROM `$oldTableName`")) { // if old table exists, rename it.
					$renameQuery = array_shift($arrAlter); // get and remove rename query

					echo "$renameQuery ... ";
					if(!@db_query($renameQuery)) {
						echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
						echo '<div class="text-danger">' . $Translation['mysql said'] . ' ' . db_error(db_link()) . '</div>';
					} else {
						echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
					}

					if(is_array($arrAlter)) setupTable($tableName, $createSQL, false, $arrAlter); // execute Alter queries on renamed table ...
				} else { // if old tableName doesn't exist (nor the new one since we're here), then just create the table.
					setupTable($tableName, $createSQL, false); // no Alter queries passed ...
				}
			} else { // tableName doesn't exist and no rename, so just create the table
				echo str_replace("<TableName>", $tableName, $Translation["creating table"]);
				if(!@db_query($createSQL)) {
					echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
					echo '<div class="text-danger">' . $Translation['mysql said'] . db_error(db_link()) . '</div>';

					// create table with a dummy field
					@db_query("CREATE TABLE IF NOT EXISTS `$tableName` (`_dummy_deletable_field` TINYINT)");
				} else {
					echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
				}
			}

			// set Admin group permissions for newly created table if membership_grouppermissions exists
			if($ro = @db_query("SELECT COUNT(1) FROM `membership_grouppermissions`")) {
				// get Admins group id
				$ro = @db_query("SELECT `groupID` FROM `membership_groups` WHERE `name`='Admins'");
				if($ro) {
					$adminGroupID = intval(db_fetch_row($ro)[0]);
					if($adminGroupID) @db_query("INSERT IGNORE INTO `membership_grouppermissions` SET
						`groupID`='$adminGroupID',
						`tableName`='$tableName',
						`allowInsert`=1, `allowView`=1, `allowEdit`=1, `allowDelete`=1
					");
				}
			}
		}

		echo '</div>';

		$out = ob_get_clean();
		if(defined('APPGINI_SETUP') && APPGINI_SETUP) echo $out;
	}
