<?php
class WPCSR_Install_DB{
    public static function wpcsr_plugin_activated(){
		global $wpdb;
		$options = get_option('wpcsr_option_settings');
		$shipment_rate_table = $wpdb->prefix . WPCSR_DB_RATE;
		if($wpdb->get_var("SHOW TABLES LIKE '$shipment_rate_table'") != $shipment_rate_table) {
			$charset_collate = $wpdb->get_charset_collate();
			$wpcsr_sql    = "CREATE TABLE IF NOT EXISTS $shipment_rate_table (
							`id` int(100) NOT NULL auto_increment,
							`zone_id` int(100) NOT NULL,
							`brand` VARCHAR(255) NOT NULL,
							`service` VARCHAR(600) NOT NULL,
							`price` VARCHAR(100) NULL,
							UNIQUE KEY `id` (`id`)
							) $charset_collate;";
			require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
			dbDelta($wpcsr_sql);
		}
		$shipment_rate_zone = $wpdb->prefix . WPCSR_DB_ZONE;
		if($wpdb->get_var("SHOW TABLES LIKE '$shipment_rate_zone'") != $shipment_rate_zone) {
			$charset_collate = $wpdb->get_charset_collate();
			$wpcsr_zone_sql  = "CREATE TABLE IF NOT EXISTS $shipment_rate_zone (
							`zone_id` int(100) NOT NULL auto_increment,
							`label` VARCHAR(255) NOT NULL,
							`origin` VARCHAR(255) NOT NULL,
							`zone_location` LONGTEXT NOT NULL,
							UNIQUE KEY `zone_id` (`zone_id`)
							) $charset_collate;";
			require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
			dbDelta($wpcsr_zone_sql);
		}
		$shipment_rate_location = $wpdb->prefix . WPCSR_DB_LOCATION;
		if($wpdb->get_var("SHOW TABLES LIKE '$shipment_rate_location'") != $shipment_rate_location) {
			$charset_collate = $wpdb->get_charset_collate();
			$wpcsr_location_sql  = "CREATE TABLE IF NOT EXISTS $shipment_rate_location (
							`location_id` int(100) NOT NULL auto_increment,
							`location` VARCHAR(255) NOT NULL,
							`location_slug` VARCHAR(255) NOT NULL,
							UNIQUE KEY `location_id` (`location_id`)
							) $charset_collate;";
			require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
			dbDelta($wpcsr_location_sql);
		}
	}
	public static function update_table( ){
		global $wpdb;
		$rate_table 		= $wpdb->prefix . WPCSR_DB_RATE;
        $pickup_table 		= $wpdb->prefix . WPCSR_DB_PICKUP;
        $delivery_table 	= $wpdb->prefix . WPCSR_DB_DELIVERY;
        if( version_compare( WPCSR_DB_VERSION, '3.0.0' ) > 0 ){
        	
        	$delivery_column 	= 'delivery_id';
			$sql 				= "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '".$rate_table."'";
			$table_columns 		= $wpdb->get_col( "DESC " . $rate_table, 0 );
			// $table_columns 		= $wpdb->get_col( $sql );
			// Alter Shipping rate table for the new updates
			if( !empty( $table_columns ) && !in_array( $delivery_column, $table_columns ) ){
				$sql_alter = "ALTER TABLE `".$rate_table."` ADD `{$delivery_column}` INT(60) NOT NULL AFTER `service`";
                $wpdb->query($sql_alter);
			}
			if($wpdb->get_var("SHOW TABLES LIKE '$delivery_table'") != $delivery_table) {
				$charset_collate = $wpdb->get_charset_collate();
				$wpcsr_location_sql  = "CREATE TABLE IF NOT EXISTS $delivery_table (
								`id` int(100) NOT NULL auto_increment,
								`title` VARCHAR(255) NOT NULL,
								`description` VARCHAR(255) NOT NULL,
								UNIQUE KEY `id` (`id`)
								) $charset_collate;";
				require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
				dbDelta($wpcsr_location_sql);
			}
		}
		if( version_compare( WPCSR_DB_VERSION, '3.1.0' ) > 0  ){
			$old_table_columns 		= $wpdb->get_col( "DESC " . $rate_table, 0 );
			if( !in_array( 'description', $old_table_columns) ){
				$sql_alter = "ALTER TABLE `{$rate_table}` ADD `logo_url` VARCHAR(200) NOT NULL AFTER `price`, ADD `rate_protection` INT(20) NOT NULL AFTER `logo_url`, ADD `coverage` INT(20) NOT NULL AFTER `rate_protection`, ADD `description` TEXT NOT NULL AFTER `coverage`";
                $wpdb->query($sql_alter);
			}
		}
		if( version_compare( WPCSR_DB_VERSION, '5.0.0' ) > 0  ){
			if($wpdb->get_var("SHOW TABLES LIKE '$pickup_table'") != $pickup_table) {
				$charset_collate = $wpdb->get_charset_collate();
				$wpcsr_sql    = "CREATE TABLE IF NOT EXISTS $pickup_table (
								`id` int(100) NOT NULL auto_increment,
								`name` VARCHAR(255) NOT NULL,
								`warehouse` VARCHAR(160) NOT NULL,
								`zone` VARCHAR(160) NOT NULL,
								`min_weight` DOUBLE(6,2) NULL,
								`max_weight` DOUBLE(6,2) NULL,
								`price` DOUBLE(6,2) NULL,
								UNIQUE KEY `id` (`id`)
								) $charset_collate;";
				require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
				dbDelta($wpcsr_sql);
			}
		}
		if( version_compare( WPCSR_DB_VERSION, '5.1.0' ) > 0  ){
			$old_table_columns 		= $wpdb->get_col( "DESC " . $rate_table, 0 );
			if( !in_array( 'min_weight', $old_table_columns) ){
				$sql_alter = "ALTER TABLE `{$rate_table}` ADD `min_weight` FLOAT NOT NULL AFTER `delivery_id`, ADD `max_weight` FLOAT NOT NULL AFTER `min_weight`";
                $wpdb->query($sql_alter);
			}
			if( !in_array( 'rate_type', $old_table_columns) ){
				$sql_alter = "ALTER TABLE `{$rate_table}` ADD `rate_type` VARCHAR(2) NOT NULL AFTER `delivery_id`, ADD `weight_cost` FLOAT NOT NULL AFTER `rate_type`";
                $wpdb->query($sql_alter);
			}		
		}	
		if( version_compare( WPCSR_DB_VERSION, '5.3.2' ) > 0  ){
			$old_table_columns 		= $wpdb->get_col( "DESC " . $rate_table, 0 );
			if( !in_array( 'user_group', $old_table_columns) ){
				$sql_alter = "ALTER TABLE `{$rate_table}` ADD `user_group` TEXT NOT NULL AFTER `description`";
                $wpdb->query($sql_alter);
			}	
		}
		if( version_compare( WPCSR_DB_VERSION, '5.3.4' ) > 0  ){
			$old_table_columns 		= $wpdb->get_col( "DESC " . $rate_table, 0 );
			if( in_array( 'min_weight', $old_table_columns) ){
				$sql_alter = "ALTER TABLE `{$rate_table}` MODIFY COLUMN `min_weight` varchar(11)";
                $wpdb->query($sql_alter);
			}
			if( in_array( 'max_weight', $old_table_columns) ){
				$sql_alter = "ALTER TABLE `{$rate_table}` MODIFY COLUMN `max_weight` varchar(11)";
                $wpdb->query($sql_alter);
			}
			if( in_array( 'weight_cost', $old_table_columns) ){
				$sql_alter = "ALTER TABLE `{$rate_table}` MODIFY COLUMN `weight_cost` varchar(11)";
                $wpdb->query($sql_alter);
			}
			if( !in_array( 'piece_type', $old_table_columns) ){
				$sql_alter = "ALTER TABLE `{$rate_table}` ADD `piece_type` VARCHAR(125) NOT NULL AFTER `user_group`";
                $wpdb->query($sql_alter);
			}	
			$user_group_table = $wpdb->prefix . WPCSR_DB_USER_GROUP;
			if($wpdb->get_var("SHOW TABLES LIKE '$user_group_table'") != $user_group_table) {
				$charset_collate = $wpdb->get_charset_collate();
				$wpcsr_ug_sql    = "CREATE TABLE IF NOT EXISTS $user_group_table (
								`user_group_id` int(100) NOT NULL auto_increment,
								`label` VARCHAR(100) NOT NULL,
								`description` VARCHAR(255) NOT NULL,
								`users` VARCHAR(100) NULL,
								UNIQUE KEY `user_group_id` (`user_group_id`)
								) $charset_collate;";
				require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
				dbDelta($wpcsr_ug_sql);
			}
		}
	}

	public static function create_delivery(){
		global $wpdb;
		$sql  = array(
			"INSERT INTO `{$wpdb->prefix}wpcsr_delivery` ( `title`, `description` ) VALUES ( 'Economy', 'Donec nec justo eget felis facilisis fermentum.' )",
			"INSERT INTO `{$wpdb->prefix}wpcsr_delivery` ( `title`, `description` ) VALUES ( 'Standard', 'Morbi in sem quis dui placerat ornare.' )",
			"INSERT INTO `{$wpdb->prefix}wpcsr_delivery` ( `title`, `description` ) VALUES ( 'Premium', 'Donec consectetuer ligula vulputate sem tristique cursus.' )"
		);
		$results = array();
		foreach ($sql as $query ) {
			$query_id = $wpdb->query( $query );
			if( $query_id ){
				$results[] = $wpdb->insert_id;
			}
			
		}
		return $results;
	}
	public static function create_location(){
		global $wpdb;
		$sql  = array(
			"INSERT INTO `{$wpdb->prefix}wpcsr_location` ( `location`, `location_slug` ) VALUES ( 'Manila', 'manila' )",
			"INSERT INTO `{$wpdb->prefix}wpcsr_location` ( `location`, `location_slug` ) VALUES ( 'Iloilo', 'iloilo' )",
			"INSERT INTO `{$wpdb->prefix}wpcsr_location` ( `location`, `location_slug` ) VALUES ( 'Cebu', 'cebu' )"
		);
		$results = array();
		foreach ($sql as $query ) {
			$query_id = $wpdb->query( $query );
			if( $query_id ){
				$results[] = $wpdb->insert_id;
			}
		}
		return $results;
	}
	public static function create_zone( $locations ){
		global $wpdb;
		$origin 		= array_shift( $locations );
		$destination 	= maybe_serialize( array_map('trim', $locations ) );
		$zones 			= array();
		for ( $zone_count=1; $zone_count <= 3 ; $zone_count++) { 
			$zonename 	= 'Zone10'.$zone_count;
			$sql 		= "INSERT INTO `{$wpdb->prefix}wpcsr_zone` ( `label`, `origin`, `zone_location` ) VALUES ( '{$zonename}', '{$origin}', '{$destination}' )";
			$query_id 	= $wpdb->query( $sql );
			if( $query_id ){
				$zones[] = $wpdb->insert_id;
			}
		}

		return $zones;
	}

	public static function create_pickup_rate(){
		global $wpdb;
		$pin 				= 1;
		$warehouse 	= "Main";
		for ( $zone_count=1; $zone_count <= 5 ; $zone_count++) { 
			$min 	= $pin;
			$min 	= $min <= 1 ? $min : $min+1;
			$max 	= $min + 4;
			$pin 	= $max;
			$price 	= ( $max / 2 ) + $zone_count * .5 / 2;

			$ratename 	= "PK10".$zone_count;
			
			$description = "Donec odio. Quisque volutpat mattis eros. Nullam malesuada erat ut turpis.";
			
			$sql = $wpdb->prepare( 
				"INSERT INTO `{$wpdb->prefix}wpcsr_pickup_rates` 
				( `name`, `warehouse`, `zone`, `min_weight`, `max_weight`, `price` ) 
				VALUES 
				( %s, %s, %s, %f, %f, %f )", 
				 $ratename, $warehouse, 'Iloilo', $min, $max, $price
			);
			$wpdb->query( $sql );
		}
	}
	public static function create_rate(){
		global $wpdb;
		// Check if the database table exist
		$shipment_rate_table = $wpdb->prefix . WPCSR_DB_RATE;
		// Return false if no table found
		if( $wpdb->get_var("SHOW TABLES LIKE '$shipment_rate_table'") != $shipment_rate_table) {
			return false;
		}
		// Check if rates has been create
		// if rate has been create return false
		$rates_count = $wpdb->get_var( "SELECT COUNT(*) FROM {$shipment_rate_table}" );
		if( $rates_count ){
			return false;
		}
		// Create Delivery
		$deliveries 	= self::create_delivery();
		// Create Location
		$locations 		= self::create_location();

		// Craete Zone
		$zones 			= self::create_zone( $locations );
		// Create Pickup Rate
		$pickup_rates 	= self::create_pickup_rate();

		$zone_id 		= array_shift( $zones );
		$delivery_id 	= array_shift( $deliveries );

		$rates 	= array();
		$pin 	= 1;
		for ( $zone_count=1; $zone_count <= 5 ; $zone_count++) { 
			$min 	= $pin;
			$min 	= $min <= 1 ? $min : $min+1;
			$max 	= $min + 4;
			$pin 	= $max;
			$price 	= ( $max / 2 ) + $zone_count * 3.5 / 2;

			$ratename 	= "Rate10".$zone_count;
			$service 	= "Standard";
			$description = "Donec odio. Quisque volutpat mattis eros. Nullam malesuada erat ut turpis.";
			
			$sql = $wpdb->prepare( 
				"INSERT INTO `{$wpdb->prefix}wpcsr_rate` 
				( `zone_id`, `brand`, `service`, `delivery_id`, `rate_type`, `weight_cost`, `min_weight`, `max_weight`, `price`, `rate_protection`, `coverage`, `description` ) 
				VALUES 
				( %d, %s, %s, %d, %d, %f, %f, %f, %f, %f, %f, %s )", 
				$zone_id, $ratename, $service, $delivery_id, 0, 0, $min, $max, $price, 0, 0, $description 
			);
			$wpdb->query( $sql );
		}
	}
}