<?php

class Tac_FoolBotHoneyPot_Install
{
	public static function installCode()
	{	
		$version = "2.2.12"; 
		$build = "52";
		
		$db = XenForo_Application::get('db');
		


		/* The primary key and structure of this table has changed, so drop and rebuild for upgrades / new installs */
		$db->query("
			DROP TABLE IF EXISTS
				`sf_foolbothoneypot_uuids` 
		");
		
		$db->query("		
			CREATE TABLE IF NOT EXISTS `sf_foolbothoneypot_version` (
			  `id` int(11) NOT NULL AUTO_INCREMENT,
			  `version` varchar(10) NOT NULL,
			  `build` int(11) NOT NULL,
			  `time_stamp` int(11) NOT NULL,
			  PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;	
		");		

		$db->query("		
			CREATE TABLE IF NOT EXISTS `sf_stopbotters` (
			  `id` int(11) NOT NULL AUTO_INCREMENT,
			  `ip_address` varchar(30) NOT NULL,
			  `known_bot` int(1) NOT NULL DEFAULT '0',
			  `time_stamp` int(11) NOT NULL,
			  PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		");				
		
		$db->query("		
			CREATE TABLE IF NOT EXISTS `sf_foolbothoneypot_uuids` (
			  `id` int(11) NOT NULL AUTO_INCREMENT,
			  `number` int(11) NOT NULL,
			  `uuid` varchar(18) NOT NULL,
			  `explanation` varchar(120) NOT NULL,
			  `session_id` varchar(60) NOT NULL,
			  PRIMARY KEY (`id`),
			  UNIQUE KEY `uuid` (`uuid`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;	
		");
		
		

				
		$db->query("		
			CREATE TABLE IF NOT EXISTS `sf_foolbothoneypot_timer` (
  				`id` int(11) NOT NULL AUTO_INCREMENT,
  				`time_start` int(11) NOT NULL,
  				`session_id` text NOT NULL,
  				PRIMARY KEY (`id`)
			) ENGINE=InnoDB  DEFAULT CHARSET=utf8;	
		");
		
		
		$db->query("		
			CREATE TABLE IF NOT EXISTS `sf_foolbothoneypot_clientside_detection` (
			  `id` int(11) NOT NULL AUTO_INCREMENT,
			  `session_id` text NOT NULL,
			  `javascript_on` int(1) NOT NULL,
			  `detected_plugins` text NOT NULL,
			  `mouse_click_count` int(6) NOT NULL,
			  `key_press_count` int(6) NOT NULL,
			  `screen_touch_count` int(6) NOT NULL,
			  `key_click_touch_pattern` text NOT NULL,
			  `time_stamp` int(11) NOT NULL,
			  `updated` int(1) NOT NULL DEFAULT '0',
			  `uuid` varchar(32) NOT NULL,
			  `correct_uuid` int(1) NOT NULL,
			  PRIMARY KEY (`id`)
			) ENGINE=InnoDB  DEFAULT CHARSET=utf8;			
		");

		$db->query("		
			CREATE TABLE IF NOT EXISTS `sf_foolbothoneypot_log` (
			  `log_id` int(10) NOT NULL AUTO_INCREMENT,
			  `log_date` int(10) NOT NULL,
			  `ip_address` text NOT NULL,
			  `username` varchar(50) NOT NULL,
			  `email` varchar(120) NOT NULL,
			  `log_type` varchar(255) NOT NULL,
			  `altered_hidden_fields` text NOT NULL,
			  `registration_errors` text NOT NULL,
			  `message` text NOT NULL,
			  `user_agent` text NOT NULL,
			  `port` text NOT NULL,
			  `server` MEDIUMTEXT NOT NULL,			  
			  `registration_time` int(10) NOT NULL,
			  `is_proxy` text NOT NULL,
			  `browser_plugins_detected` text NOT NULL,
			  `javascript_enabled` int(1) NOT NULL,
			  `found_on_stopbotters` int(1) NOT NULL,
			  `is_spam_api1` int(1) NOT NULL DEFAULT '0',
			  `is_spam_api2` int(1) NOT NULL DEFAULT '0',
			  `is_spam_api3` int(1) NOT NULL DEFAULT '0',
			  `is_spam_api4` int(1) NOT NULL DEFAULT '0',
			  `is_spam_api5` int(1) NOT NULL DEFAULT '0',
			  `is_spam_api6` int(1) NOT NULL DEFAULT '0',
			  `is_spam_api7` int(1) NOT NULL DEFAULT '0',
			  `is_spam_api8` int(1) NOT NULL DEFAULT '0',
			  `is_spam_api9` int(1) NOT NULL DEFAULT '0',
			  `is_spam_api10` int(1) NOT NULL DEFAULT '0',
			  PRIMARY KEY (`log_id`)
			) ENGINE=InnoDB  DEFAULT CHARSET=utf8;	
		");
		
	
		// next version we can use the version table to check for upgrade... this check is flawed if logs arent available
		//$isUpgrade = self::tableHasRows($db, "sf_foolbothoneypot_log", "log_id");

			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "user_agent", "text NOT NULL");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "port", "text NOT NULL");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "registration_time", "int(10) NOT NULL");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "server", "text NOT NULL");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "is_proxy", "text NOT NULL");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "browser_plugins_detected", "text NOT NULL");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "javascript_enabled", "int(1) NOT NULL");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "found_on_stopbotters", "int(1) NOT NULL");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "is_spam_api1", "int(1) NOT NULL DEFAULT '0'");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "is_spam_api2", "int(1) NOT NULL DEFAULT '0'");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "is_spam_api3", "int(1) NOT NULL DEFAULT '0'");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "is_spam_api4", "int(1) NOT NULL DEFAULT '0'");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "is_spam_api5", "int(1) NOT NULL DEFAULT '0'");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "is_spam_api6", "int(1) NOT NULL DEFAULT '0'");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "is_spam_api7", "int(1) NOT NULL DEFAULT '0'");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "is_spam_api8", "int(1) NOT NULL DEFAULT '0'");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "is_spam_api9", "int(1) NOT NULL DEFAULT '0'");
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_log", "is_spam_api10", "int(1) NOT NULL DEFAULT '0'");
			
			self::addColumnIfNotExist($db, "sf_foolbothoneypot_uuids", "time_stamp", "int(11) NOT NULL DEFAULT '0'"); 
			
			
			$db->query("ALTER TABLE sf_foolbothoneypot_log MODIFY log_type varchar (255);");
			
			self::modifyTableType($db, "sf_foolbothoneypot_log", "server", "MEDIUMTEXT");
					
			self::modifyTableType($db, "sf_foolbothoneypot_log", "ip_address", "varbinary(16) NOT NULL");

		
	
		
		$db->query("
			INSERT IGNORE INTO `sf_foolbothoneypot_version` (`version`, `build`, `time_stamp`) VALUES
			('".$version."', ".$build.", ".time().")
			");	

		
		
		
		$indexExists = self::indexExists($db, "sf_foolbothoneypot_uuids", "sesnum");
		
		if(!$indexExists)
		{
			$db->query("ALTER TABLE sf_foolbothoneypot_uuids ADD INDEX sesnum (session_id, number);");
			
		}
		
		
		
		
		
		
		
		
		
		return true;
	}

	
	public static function modifyTableType($db, $table, $field, $attr)
	{	
		$db->query("ALTER TABLE `".$table."` MODIFY `".$field."` ".$attr); 
	}	
	
	public static function uninstallCode()
	{
		$db = XenForo_Application::get('db');
		// drop tables
		$db->query("
			DROP TABLE IF EXISTS
				`sf_foolbothoneypot_uuids`, 
				`sf_foolbothoneypot_log`,
				`sf_foolbothoneypot_timer`
		");
		return true;
	}

	
	public static function addColumnIfNotExist($db, $table, $field, $attr)
	{
		if ($db->fetchRow('SHOW columns FROM `'.$table.'` WHERE Field = ?', $field))
		{
			return false;
		}
		
		return $db->query("ALTER TABLE `".$table."` ADD `".$field."` ".$attr);
	}
	
	
	public static function tableHasRows($db, $tableName, $rowName)
	{
		$stmt  = $db->query('SELECT COUNT('.$rowName.') AS c FROM '.$tableName.' LIMIT 1');
		$row = $stmt->fetch();
		$num_rows = $row['c'];
		if($num_rows > 0){return true;}
		return false;	
	}
	
	public static function indexExists($db, $tableName, $indexName)
	{
		return $db->query('SHOW INDEX FROM `'.$tableName.'` WHERE KEY_NAME = "'.$indexName.'"');	
	}
	
	

	
	

}
