<?php
use PHPUnit\Framework\TestCase;

require_once dirname(__FILE__) . "/initialise.php";
require_once dirname(__FILE__) . "/../include/db/IDB.php";
require_once dirname(__FILE__) . "/../include/db/MySQL.php";
require_once dirname(__FILE__) . "/../include/db/QueryBuilder.php";

abstract class DBTest extends TestCase
{
	private static $TABLE_DBVERSION = "tests_dbversion";
	
	private $mysqli;
	
	private $db;
	
	private $doTruncateTables;
	
	/**
	 * @before
	 */
	public function prepare() {
		$this->verifyDB();
		$this->configure();
		$this->doTruncateTables = true;
	}

	abstract protected function configure();

	protected function getDB() {
		if( $this->doTruncateTables ) {
			$this->truncateTables();
			$this->doTruncateTables = false;
		}
		$_SESSION['sess_uid'] = 0;
		if( $this->db == NULL ) {
			$this->db = new MySQL(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB);
			$GLOBALS['DB'] = $this->db;
		}
		return $this->db;
	}
	
	private function verifyDB() {
		$this->connect();
		$res = $this->mysqli->query("SELECT `version` FROM `" . self::$TABLE_DBVERSION . "`");
		if( !$res || $res->num_rows != 1 ) {
			$this->prepareDB();
		}else {
			$row = $res->fetch_assoc();
			$sha1 = sha1_file(__FILE__);
			if( $row['version'] != $sha1 ) {
				$this->prepareDB();
			}
		}
	}
	
	private function prepareDB() {
		$this->cleanDB();
		$this->createTableAbBodies();
		$this->createTableApplications();
		$this->createTableBodies();
		$this->createTableCurrencies();
		$this->createTableMailout();
		$this->createTableQuerylog();
		
		$this->executeQuery("CREATE TABLE `" . self::$TABLE_DBVERSION . "` (
				`version` varchar(40) NOT NULL
				) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;");
		$sha1 = sha1_file(__FILE__);
		$this->executeQuery("INSERT INTO `" . self::$TABLE_DBVERSION . "` VALUES ('" . $sha1 . "')");
	}
	
	private function connect() {
		if( $this->mysqli == NULL ) {
			$this->mysqli = new mysqli(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB);
		}
	}
		
	private function cleanDB() {
		$this->connect();
		$res = $this->mysqli->query("SHOW TABLES");
		while( $row = $res->fetch_assoc() ) {
			$query = "DROP TABLE `" . $row['Tables_in_test'] . "`";
			$this->mysqli->query($query);
		}
	}

	private function truncateTables() {
		$this->connect();
		$res = $this->mysqli->query("SHOW TABLES");
		while( $row = $res->fetch_assoc() ) {
			$query = "DELETE FROM `" . $row['Tables_in_test'] . "`";
			$row['Tables_in_test'] != self::$TABLE_DBVERSION && $this->mysqli->query($query);
		}
	}

	private function executeQuery($query) {
		$this->connect();
		if( $this->mysqli->query($query) ) {
			return true;
		}else {
			echo "DB failure: " . $this->mysqli->error;
			return false;
		}
	}
	
	private function createTableAbBodies() {
		$this->executeQuery("CREATE TABLE `ab_bodies` ( 
				`bodycode` varchar(3) NOT NULL, 
				`bodyname` varchar(50) NOT NULL, 
				`email` varchar(70) DEFAULT NULL, 
				`bodystatus` varchar(2) DEFAULT NULL, 
				`bodycategory` varchar(25) NOT NULL, 
				`bodycategoryorder` int(11) NOT NULL, 
				`memberno` int(11) DEFAULT NULL, 
				`memberfeecurrencyid` int(11) DEFAULT NULL, 
				`membersource` varchar(9) DEFAULT NULL, 
				`memberclaimed` int(11) DEFAULT NULL, 
				`memberclaimedby` varchar(64) DEFAULT NULL, 
				`memberlastupdate` datetime DEFAULT NULL, 
				`bodynameascii` varchar(50) DEFAULT NULL, 
				`netcomcode` char(1) DEFAULT NULL, 
				`countrycode` varchar(3) DEFAULT NULL, 
				`password` varchar(32) DEFAULT NULL, 
				`nodelegates` int(11) NOT NULL DEFAULT '-1', 
				`noenvoys` int(11) NOT NULL DEFAULT '-1', 
				`novisitors` int(11) NOT NULL DEFAULT '-1', 
				`noobservers` int(11) NOT NULL DEFAULT '-1', 
				PRIMARY KEY (`bodycode`), 
				KEY `fk_bodies_countrycode` (`countrycode`) 
				) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
	}
	
	private function createTableApplications() {
		$this->executeQuery("CREATE TABLE `applications` (
				`id` int(11) NOT NULL AUTO_INCREMENT,
				`username` varchar(64) DEFAULT NULL,
				`firstname` varchar(50) NOT NULL,
				`lastname` varchar(50) NOT NULL,
				`bodycode` varchar(3) NOT NULL,
				`email` varchar(70) DEFAULT NULL,
				`participanttype` enum('-','delegate','envoy','observer','visitor') NOT NULL DEFAULT '-',
				`participantorder` int(11) DEFAULT NULL,
				`log` text,
				PRIMARY KEY (`id`), 
				KEY `fk_applications_bodycode` (`bodycode`) 
				) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");
	}
	
	private function createTableBodies() {
		$this->executeQuery("CREATE TABLE `bodies` ( 
				`BodyCode` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '', 
				`BodyName` varchar(64) CHARACTER SET utf8 NOT NULL, 
				`BodyNameAscii` varchar(64) CHARACTER SET utf8 NOT NULL, 
				`BodyStatus` varchar(2) CHARACTER SET utf8 NOT NULL, 
				`BodyCategory` varchar(25) CHARACTER SET utf8 NOT NULL DEFAULT '', 
				`BodyCategoryOrder` int(11) unsigned NOT NULL DEFAULT '0', 
				`CountryCode` varchar(3) CHARACTER SET utf8 DEFAULT NULL, 
				`BodyNameShort` varchar(50) CHARACTER SET utf8 NOT NULL, 
				`NetcomCode` char(1) CHARACTER SET utf8 NOT NULL, 
				`Url` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '', 
				`Email` varchar(50) CHARACTER SET utf8 NOT NULL, 
				`MemberNo` int(11) unsigned NOT NULL, 
				`Modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
				PRIMARY KEY (`BodyCode`), 
				KEY `BodyName` (`BodyName`) 
				) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;");
	}
	
	private function createTableCurrencies() {
		$this->executeQuery("CREATE TABLE `currencies` (
				`id` int(11) NOT NULL AUTO_INCREMENT, 
				`iso_2` varchar(2) NOT NULL, 
				`name` varchar(24) NOT NULL, 
				`short` varchar(12) NOT NULL, 
				`rate` decimal(12,10) NOT NULL, 
				PRIMARY KEY (`id`) 
				) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");
	}
	
	private function createTableMailout() {
		$this->executeQuery("CREATE TABLE `mailout` (
				`id` int(11) NOT NULL AUTO_INCREMENT,
				`date` datetime NOT NULL,
				`to` varchar(128) NOT NULL,
				`content` varchar(32) NOT NULL,
				`mail` blob NOT NULL,
				PRIMARY KEY (`id`)
				) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");
	}
	
	private function createTableQuerylog() {
		$this->executeQuery("CREATE TABLE `querylog` (
				`id` int(11) NOT NULL AUTO_INCREMENT, 
				`date` datetime NOT NULL, 
				`sid` int(11) NOT NULL, 
				`user` varchar(32) DEFAULT NULL, 
				`query` text NOT NULL, 
				PRIMARY KEY (`id`) 
				) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");
	}
	
	protected function getBodyAachen() {
		$data = array();
		$data['bodycode'] = "AAC";
		$data['bodyname'] = "AEGEE-Aachen";
		$data['bodynameascii'] = $data['bodyname'];
		$data['bodystatus'] = "A";
		$data['bodycategory'] = "Locals";
		$data['bodycategoryorder'] = 7;
		$data['countrycode'] = "DE";
		$data['email'] = EMAIL;
		return $data;
	}

	protected function insertBodyAachen() {
		$data = $this->getBodyAachen();
		$this->getDB()->insert("ab_bodies", $data, false);
		return $data;
	}
	
	protected function getBodyEnschede() {
		$data = array();
		$data['bodycode'] = "ENS";
		$data['bodyname'] = "AEGEE-Enschede";
		$data['bodynameascii'] = $data['bodyname'];
		$data['bodystatus'] = "A";
		$data['bodycategory'] = "Locals";
		$data['bodycategoryorder'] = 7;
		$data['countrycode'] = "NL";
		$data['email'] = EMAIL;
		return $data;
	}
	
	protected function insertBodyEnschede() {
		$data = $this->getBodyEnschede();
		$this->getDB()->insert("ab_bodies", $data, false);
		return $data;
	}
	
	protected function getBodyContactGlasgow() {
		$data = array();
		$data['bodycode'] = "GLA";
		$data['bodyname'] = "Glasgow";
		$data['bodynameascii'] = $data['bodyname'];
		$data['bodystatus'] = "C";
		$data['bodycategory'] = "Contacts";
		$data['bodycategoryorder'] = 8;
		$data['countrycode'] = "GB";
		$data['email'] = EMAIL;
		return $data;
	}
	
	protected function insertBodyContactGlasgow() {
		$data = $this->getBodyContactGlasgow();
		$this->getDB()->insert("ab_bodies", $data, false);
		return $data;
	}
	
	protected function getBodyITWG() {
		$data = array();
		$data['bodycode'] = "IUG";
		$data['bodyname'] = "Information Technology Committee";
		$data['bodynameascii'] = $data['bodyname'];
		$data['bodystatus'] = "S";
		$data['bodycategory'] = "Working Groups";
		$data['bodycategoryorder'] = 5;
		$data['countrycode'] = "XXX";
		$data['email'] = EMAIL;
		return $data;
	}
	
	protected function insertBodyITWG() {
		$data = $this->getBodyITWG();
		$this->getDB()->insert("ab_bodies", $data, false);
		return $data;
	}

	protected function insertApplication($id, $bodycode, $participanttype, $participantorder) {
		$data = array();
		$data['id'] = $id;
		$data['username'] = "User" . $id;
		$data['firstname'] = "Firstname" . $id;
		$data['lastname'] = "Lastname" . $id;
		$data['bodycode'] = $bodycode;
		$data['email'] = EMAIL;
		$data['participanttype'] = $participanttype;
		$data['participantorder'] = $participantorder;
		$this->getDB()->insert("applications", $data, false);
		return $data;
	}
}

function debug($msg) {
	echo "[debug] " . $msg . "[/debug]";
}

function debug_array($name, $array) {
	debug($name.":<br />".nl2br(strtr(print_r($array, true), array(" " => "&nbsp;"))));
}
?>