<?php
defined('NEARESTSUPPLIERPLUGIN') || die('direct access not allowed!');

function nearestsupplier_db_update() {
	
	global $wpdb;
	global $nearestsupplier_version;
	
	$installed_version = get_option('nearestsupplier.version', '0.0.0');
	$sql = array();
	
	// add initial sql scripts if fresh install
	if (version_compare($installed_version, '0.0.0') <= 0) {
	
		// make sure the option is autoloaded next time
		add_option('nearestsupplier.version', '0.0.0');
	
		$sql[] = "DROP TABLE IF EXISTS {$wpdb->prefix}nearestsupplier_suppliers;";
		$sql[] = "
		CREATE TABLE `{$wpdb->prefix}nearestsupplier_suppliers` (
		`id` mediumint(9) NOT NULL AUTO_INCREMENT,
		`name` tinytext NOT NULL,
		`address` varchar(100) DEFAULT NULL,
		`postalcode` varchar(10) DEFAULT NULL,
		`city` varchar(100) DEFAULT NULL,
		`country` char(2) DEFAULT NULL,
		`latitude` varchar(20) DEFAULT NULL,
		`longitude` varchar(20) DEFAULT NULL,
		`credits` int(11) DEFAULT NULL,
		`website` varchar(256) DEFAULT NULL,
		`phonenr` varchar(20) DEFAULT NULL,
		`email` varchar(256) DEFAULT NULL,
		`email_subject` text,
		`email_body` text,
		`email_attachment` varchar(256) DEFAULT NULL,
		`redirect_url` varchar(256) DEFAULT NULL,
		`max_radius` int(11) DEFAULT NULL,
		PRIMARY KEY (`id`)
		)";
	
		$sql[] = "
		CREATE TABLE `{$wpdb->prefix}nearestsupplier_mails` (
		`id` int(11) NOT NULL AUTO_INCREMENT,
		`supplier_id` int(11) DEFAULT NULL,
		`created` datetime DEFAULT NULL,
		`email_to` varchar(256) DEFAULT NULL,
		`postalcode` varchar(10) DEFAULT NULL,
		`distance` float DEFAULT NULL,
		PRIMARY KEY (`id`)
		)";
	
	}
	
	// add scripts added to version 1.1.0
	if (version_compare($installed_version, '1.1.0') < 0) {
		
		$sql[] = "
		ALTER TABLE `{$wpdb->prefix}nearestsupplier_mails`
		ADD COLUMN `credits_charged` INT NULL  AFTER `distance`;
		";
	}
	
	if (version_compare($installed_version, '1.3.0') < 0) {
		
		add_option('credit_validity', 30);
		add_option('credit_alert_threshold', 10);
		
		$sql[] = "
		ALTER TABLE `{$wpdb->prefix}nearestsupplier_suppliers` 
		ADD COLUMN `remarks` TEXT NULL  AFTER `max_radius`;
		";
	}
	
	if (version_compare($installed_version, '1.4.0') < 0) {
		
		add_option('use_products', 0);
		
		$sql[] = "
		ALTER TABLE `{$wpdb->prefix}nearestsupplier_mails` 
		ADD COLUMN `post` TEXT NULL  AFTER `credits_charged` ;
		";
		
		$sql[] = "
		ALTER TABLE `{$wpdb->prefix}nearestsupplier_suppliers`
		ADD COLUMN `costs_formula` VARCHAR(200) NULL  AFTER `remarks` ;
		";
		
		$sql[] = "
		ALTER TABLE `{$wpdb->prefix}nearestsupplier_suppliers`
		ADD COLUMN `min_costs` INT NULL  AFTER `costs_formula` , 
		ADD COLUMN `max_costs` INT NULL  AFTER `min_costs` ;
		";		
		
		$sql[] = "
		CREATE  TABLE `{$wpdb->prefix}nearestsupplier_products` (
			`id` INT NOT NULL AUTO_INCREMENT ,
			`name` VARCHAR(100) NULL ,
			`costs` DOUBLE NULL,
			`order` INT NULL,
			PRIMARY KEY (`id`) );
		";
		
		$sql[] = "
		CREATE  TABLE `{$wpdb->prefix}nearestsupplier_suppliers_products` (
				`id` INT NOT NULL AUTO_INCREMENT ,
				`supplier_id` INT NULL ,
				`product_id` INT NULL ,
				`costs` DOUBLE NULL ,
				PRIMARY KEY (`id`) );
		";
		
	}

	if (version_compare($installed_version, '1.5.0') < 0) {
		$sql[] = "
			ALTER TABLE `{$wpdb->prefix}nearestsupplier_products` 
			ADD COLUMN `max_radius` INT NULL  AFTER `order`,
			ADD COLUMN `factor` INT NULL  AFTER `max_radius`;
		";
		
		$sql[] = "
			ALTER TABLE `{$wpdb->prefix}nearestsupplier_suppliers_products`
			ADD COLUMN `max_radius` VARCHAR(45) NULL  AFTER `costs` ;
		";
	}
	
    if (version_compare($installed_version, '1.5.5') < 0) {
        $sql[] = "
            ALTER TABLE `{$wpdb->prefix}nearestsupplier_products` 
                CHANGE COLUMN `factor` `factor` DOUBLE NULL DEFAULT NULL;
        ";
    }

	// queries are gathered now execute them all
	$result = true;
	for ($i = 0; ($i < count($sql)) && $result; $i++) {
		$result = $wpdb->query($sql[$i]);
	}
	
	// everything ok -> update version
	if ($result) {
		update_option("nearestsupplier.version", $nearestsupplier_version);
	}
}
add_action('plugins_loaded', 'nearestsupplier_db_update');