<?php
/**
 * Automatic database updates
 *
 * @author		Roland Haeder <webmaster@shipsimu.org>
 * @version		3.0.0
 * @copyright	Copyright (c) 2009 - 2017 Cracker Tracker Team
 * @license		GNU GPL 3.0 or any newer version
 * @link		http://www.shipsimu.org
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */

// Init all updates
function crackerTrackerInitUpdates () {
	// Add all
	$GLOBALS['ctracker_updates'] = [
		// Ticket system:
		[
			'CREATE TABLE IF NOT EXISTS `ctracker_ticket` (
`ctracker_ticket` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`ctracker_ticket_remote_addr` varchar(15) NOT NULL,
`ctracker_ticket_user_agent` tinytext NOT NULL,
`ctracker_ticket_name` varchar(255) NOT NULL,
`ctracker_ticket_email` varchar(255) NOT NULL,
`ctracker_ticket_comment` tinytext NOT NULL,
PRIMARY KEY ( `ctracker_ticket` ),
UNIQUE KEY (`ctracker_ticket_remote_addr`)
) TYPE=InnoDB',
			'CREATE TABLE IF NOT EXISTS `ctracker_ticket_data` (
`ctracker_ticket_data_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`ctracker_ticket_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
`ctracker_data_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY ( `ctracker_ticket_data_id` ),
UNIQUE KEY `ctracker_ticket_data` ( `ctracker_ticket_id`, `ctracker_data_id` ),
INDEX ( `ctracker_data_id` ),
FOREIGN KEY ( `ctracker_ticket_id` ) REFERENCES `' . $GLOBALS['ctracker_dbname'] . '`.`ctracker_ticket` (
`ctracker_ticket`
) ON DELETE CASCADE,
FOREIGN KEY ( `ctracker_data_id` ) REFERENCES `' . $GLOBALS['ctracker_dbname'] . '`.`ctracker_data` (
`id`
) ON DELETE CASCADE
) TYPE=InnoDB',
		],

		// NULL for missing values
		[
			"ALTER TABLE `ctracker_data` CHANGE `script_name` `script_name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Full script name'",
			"UPDATE `ctracker_data` SET `script_name`=NULL WHERE `script_name`=''",
			"ALTER TABLE `ctracker_data` CHANGE `server_name` `server_name` TINYTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Server''s host name'",
			"UPDATE `ctracker_data` SET `server_name`=NULL WHERE `server_name`=''",
		],

		// Also store proxy address
		[
			"ALTER TABLE `ctracker_data` ADD `proxy_addr` VARCHAR(15) NULL DEFAULT NULL AFTER `remote_addr`",
			"ALTER TABLE `ctracker_ticket` ADD `ctracker_ticket_proxy_addr` VARCHAR(15) NULL DEFAULT NULL",
		],

		// Better key for always repeating query
		[
			"ALTER TABLE `ctracker_data` ADD INDEX `remote_proxy_last` ( `remote_addr`, `proxy_addr`, `last_attempt` DESC )",
		],

		// HTTP request method
		[
			"ALTER TABLE `ctracker_data` ADD `request_method` VARCHAR( 255 ) NULL DEFAULT NULL COMMENT 'HTTP request method' AFTER `referer`, ADD INDEX ( `request_method` )",
		],

		// Index on count
		[
			"ALTER TABLE `ctracker_data` ADD INDEX `count` (`count`)",
		],

		// Log also UA after check
		[
			"ALTER TABLE `ctracker_data` ADD `check_ua` tinytext COLLATE 'utf8_general_ci' NULL COMMENT 'User agent after check' AFTER `post_data`",
			"ALTER TABLE `ctracker_data` CHANGE `check_worm` `check_get` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'GET data after check' AFTER `check_ua`",
			"ALTER TABLE `ctracker_data` CHANGE `check_post` `check_post` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'POST data after check' AFTER `check_get`",
		],

		// View for request methods
		[
			"CREATE VIEW `view_most_request_methods` AS
			SELECT SQL_CALC_FOUND_ROWS COUNT(`id`) AS `total_rows`, `request_method`
			FROM `ctracker_data`
			GROUP BY `request_method`"
		],

		// Anti-spambot field
		[
			"ALTER TABLE `ctracker_data` CHANGE `proxy_addr` `proxy_addr` varchar(15) COLLATE 'latin1_swedish_ci' NULL COMMENT 'Detected proxy address' AFTER `remote_addr`",
			"ALTER TABLE `ctracker_data` ADD `spam_bot_dectections` bigint unsigned NOT NULL DEFAULT '0' COMMENT 'Spam-bot attempts'",
		],

		// UTF-8
		[
			"ALTER TABLE `ctracker_ticket` CHANGE `ctracker_ticket_remote_addr` `ctracker_ticket_remote_addr` varchar(15) COLLATE 'utf8_general_ci' NOT NULL AFTER `ctracker_ticket`",
			"ALTER TABLE `ctracker_ticket` CHANGE `ctracker_ticket_proxy_addr` `ctracker_ticket_proxy_addr` varchar(15) COLLATE 'utf8_general_ci' NULL AFTER `ctracker_ticket_remote_addr`",
			"ALTER TABLE `ctracker_ticket` CHANGE `ctracker_ticket_user_agent` `ctracker_ticket_user_agent` tinytext COLLATE 'utf8_general_ci' NOT NULL AFTER `ctracker_ticket_proxy_addr`",
			"ALTER TABLE `ctracker_ticket` CHANGE `ctracker_ticket_name` `ctracker_ticket_name` varchar(255) COLLATE 'utf8_general_ci' NOT NULL AFTER `ctracker_ticket_user_agent`",
			"ALTER TABLE `ctracker_ticket` CHANGE `ctracker_ticket_email` `ctracker_ticket_email` varchar(255) COLLATE 'utf8_general_ci' NOT NULL AFTER `ctracker_ticket_name`",
			"ALTER TABLE `ctracker_ticket` CHANGE `ctracker_ticket_comment` `ctracker_ticket_comment` tinytext COLLATE 'utf8_general_ci' NOT NULL AFTER `ctracker_ticket_email`",
			"ALTER TABLE `ctracker_ticket` COLLATE 'utf8_general_ci'",
		],

		// More UTF-8
		[
			"ALTER TABLE `ctracker_data` CHANGE `remote_addr` `remote_addr` varchar(15) COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Remote address' AFTER `id`",
			"ALTER TABLE `ctracker_data` CHANGE `user_agent` `user_agent` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'User agent string' AFTER `remote_addr`",
			"ALTER TABLE `ctracker_data` CHANGE `get_data` `get_data` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Serialized GET data' AFTER `user_agent`",
			"ALTER TABLE `ctracker_data` CHANGE `post_data` `post_data` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Serialized POST data' AFTER `get_data`",
			"ALTER TABLE `ctracker_data` CHANGE `check_get` `check_get` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Manipulated GET data' AFTER `check_ua`",
			"ALTER TABLE `ctracker_data` CHANGE `check_post` `check_post` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Manipulated GET data' AFTER `check_get`",
			"ALTER TABLE `ctracker_data` CHANGE `referer` `referer` varchar(255) COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Referer' AFTER `script_name`",
			"ALTER TABLE `ctracker_data` CHANGE `request_method` `request_method` varchar(255) COLLATE 'utf8_general_ci' NULL COMMENT 'HTTP request method' AFTER `referer`",
			"ALTER TABLE `ctracker_data` CHANGE `proxy_used` `proxy_used` enum('Y','N') COLLATE 'utf8_general_ci' NOT NULL DEFAULT 'N' COMMENT 'Proxy used?' AFTER `request_method`",
			"ALTER TABLE `ctracker_data` CHANGE `last_attempt` `last_attempt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last attempt' AFTER `first_attempt`",
			"ALTER TABLE `ctracker_data` CHANGE `proxy_addr` `proxy_addr` varchar(15) COLLATE 'utf8_general_ci' NULL COMMENT 'Detected proxy address' AFTER `count`",
			"ALTER TABLE `ctracker_data` COLLATE 'utf8_general_ci'",
		],

		// Tzop fixed
		[
			"ALTER TABLE `ctracker_data` CHANGE `spam_bot_dectections` `spam_bot_detections` bigint unsigned NOT NULL DEFAULT '0' COMMENT 'Spam-bot attempts'",
		],
	];
}

// Runs the given updates at number X
function runCrackerTrackerUpdates ($update) {
	// We assume it is set
	foreach ($GLOBALS['ctracker_updates'][$update] as $sql) {
		// Run the SQL command
		runCrackerTrackerSql($sql, __FUNCTION__, __LINE__);
	}
}