<?php

/*

This file handles all initial setup of the plugin

*/
		
global $dwpc_db_version;
$dwpc_db_version = "1.2";

function dwpc_wpopt_install($is_upgrade='') {
	
   global $wpdb;
   global $dwpc_db_version;

	if(DWPC_DB_PREFIX=='get_the_table_prefix') {$table_prefix = $wpdb->prefix; } else {$table_prefix = DWPC_DB_PREFIX;}
  
 	//DWPC Data structure
	  $sql = "	  
			  CREATE TABLE ".$table_prefix."dwpc_plugins (
			id mediumint(9) NOT NULL AUTO_INCREMENT, 
			name VARCHAR(64) DEFAULT '' NOT NULL,
			class_name VARCHAR(10) DEFAULT '' NOT NULL,
			version DECIMAL(3,2) NOT NULL,
			dwpc_version DECIMAL(3,2) NOT NULL,
			time_created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
			UNIQUE KEY id (id)
			  );
	  
			  CREATE TABLE ".$table_prefix."dwpc_visitors (
			id mediumint(9) NOT NULL AUTO_INCREMENT,
			wp_user_id mediumint(9) NOT NULL,
			filtered tinyint(1) NOT NULL,
			campaign_traffic tinyint(1) NOT NULL DEFAULT 0,
			visitor_type enum('Internal','Search','Link','Direct') NOT NULL,
			bounce datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
			time_created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
			cookie VARCHAR(32) DEFAULT '' NOT NULL,
			ip_address VARCHAR(24) DEFAULT '' NOT NULL,
			user_agent VARCHAR(255) DEFAULT '' NOT NULL,
			browser VARCHAR(32) DEFAULT '' NOT NULL,
			operating_system VARCHAR(32) DEFAULT '' NOT NULL,
			referal_url VARCHAR(255) DEFAULT '' NOT NULL,
			country_code VARCHAR(2) DEFAULT '' NOT NULL,
			country_name VARCHAR(64) DEFAULT '' NOT NULL,
			region_code VARCHAR(12) DEFAULT '' NOT NULL,
			region_name VARCHAR(128) DEFAULT '' NOT NULL,
			zipcode VARCHAR(16) DEFAULT '' NOT NULL,
			latitude DECIMAL(7,4) NOT NULL,
			longitude DECIMAL(7,4) NOT NULL,
			UNIQUE KEY id (id),
			UNIQUE KEY cookie (cookie),
			KEY filtered (filtered),
			KEY time_created (time_created),
			KEY time_created_by_filter (filtered,time_created),
			KEY campaign_big (filtered,campaign_traffic,time_created),
			KEY campaign_type_big (filtered,campaign_traffic,visitor_type,time_created),
			KEY type_big (filtered,visitor_type,time_created),
			KEY all_big (campaign_traffic,visitor_type,time_created)
			  );	
			  
			  CREATE TABLE ".$table_prefix."dwpc_impressions (
			id mediumint(9) NOT NULL AUTO_INCREMENT,
			filtered tinyint(1) NOT NULL,
			text_id VARCHAR(7) DEFAULT '' NOT NULL,
			wp_post_id mediumint(9) NOT NULL, 
			dwpc_last_impression_id mediumint(9) NOT NULL, 
			dwpc_visitor_id mediumint(9) NOT NULL,  
			dwpc_ad_id mediumint(9) NOT NULL, 
			time_created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
			ip_address VARCHAR(24) DEFAULT '' NOT NULL,
			impression_url VARCHAR(255) DEFAULT '' NOT NULL,
			referal_url VARCHAR(255) DEFAULT '' NOT NULL,
			wpppc_incoming_click_id mediumint(9) NOT NULL, 
			wpppc_landing_page_id mediumint(9) NOT NULL,
			load_time mediumint(7) NOT NULL,
			UNIQUE KEY id (id),
			KEY text_id (text_id),
			KEY filtered (filtered),
			KEY time_created (time_created),
			KEY time_created_by_filter (filtered,time_created),
			KEY impression_url (impression_url),
			KEY impression_url_by_filter (filtered,impression_url),
			KEY impression_url_by_filter_and_time (filtered,time_created,impression_url)
			  );
			  
			  CREATE TABLE ".$table_prefix."dwpc_click_filter (
			id mediumint(9) NOT NULL AUTO_INCREMENT, 
			time_created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
			ip_address VARCHAR(24) DEFAULT '' NOT NULL,
			user_agent VARCHAR(255) DEFAULT '' NOT NULL,
			note VARCHAR(255) DEFAULT '' NOT NULL,
			UNIQUE KEY id (id),
			KEY ip_address (ip_address),
			KEY user_agent (user_agent),
			KEY user_agent_by_ip (user_agent,ip_address),
			KEY ip_address_by_ua (ip_address,user_agent)
			  );
			  
			  CREATE TABLE ".$table_prefix."dwpc_settings (
			id mediumint(9) NOT NULL AUTO_INCREMENT, 
			name VARCHAR(64) DEFAULT '' NOT NULL,
			value TEXT DEFAULT '' NOT NULL,
			wp_user_id mediumint(9) NOT NULL, 
			UNIQUE KEY id (id),
			KEY name (wp_user_id,name)
			  );
			  
			  ";  
	  
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');	
	
	dbDelta($sql);
	
	//Create Foreign Keys
	$fksql = 
	"
	ALTER TABLE ".$table_prefix."dwpc_impressions
		ADD FOREIGN KEY (wpppc_incoming_click_id) REFERENCES ".$table_prefix."wpppc_incoming_clicks(id),
		ADD FOREIGN KEY (wpppc_landing_page_id) REFERENCES ".$table_prefix."wpppc_landing_pages(id),
		ADD FOREIGN KEY (dwpc_visitor_id) REFERENCES ".$table_prefix."dwpc_visitors(id);
	
	";
	$wpdb->query($fksql);
      
   if($is_upgrade!=1) {
	
	$wpdb->query('SELECT id FROM '.$table_prefix.'dwpc_plugins WHERE name = "opt"');
	if($wpdb->num_rows>0) { $wpdb->delete($table_prefix.'dwpc_plugins',array('name'=>'opt')); }
	
	$this_plugin = array('name'=>'opt','class_name'=>'wpopt_dwpc','version'=>1.0,'dwpc_version'=>1.14,'time_created'=>current_time('mysql'));
	$wpdb->insert($table_prefix.'dwpc_plugins',$this_plugin);
      
   	if($wpdb->query('SELECT * FROM '.$table_prefix.'dwpc_click_filter') < 1)
	{  
	   $user_agents = array('Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)','Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)','Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)','Mozilla/5.0 (compatible; YandexBot/3.0; +http://yandex.com/bots)','Mozilla/5.0 (compatible; YandexImages/3.0; +http://yandex.com/bots)','Mozilla/5.0 (compatible; YandexBot/3.0; MirrorDetector; +http://yandex.com/bots)','msnbot-media/1.1 (+http://search.msn.com/msnbot.htm)','Mozilla/5.0 (compatible; AhrefsBot/4.0; +http://ahrefs.com/robot/)','DoCoMo/2.0 N905i(c100;TB;W24H16) (compatible; Googlebot-Mobile/2.1; +http://www.google.com/bot.html)');
	   
	   foreach($user_agents as $key => $value) {
	   
		   unset($data);
			$data = array(
				'id' => NULL,
				'time_created' => current_time('mysql'),
				'user_agent' => $value
			);
			
			$wpdb->insert($table_prefix."dwpc_click_filter", $data );
		
			}
	}
 
   }else{ //Not upgrade, Convert old visitor, impression, filter tables here!!!
	 $wpdb->update($table_prefix.'dwpc_plugins',array('name'=>'opt','class_name'=>'wpopt_dwpc','version'=>1.0,'dwpc_version'=>1.14),array('name'=>'opt')); 
   }
   
    

   update_option("dwpc_db_version", $dwpc_db_version);
   
}


function dwpc_wpopt_uninstall() {
	
   global $wpdb;
   global $dwpc_db_version;
   
	if(DWPC_DB_PREFIX=='get_the_table_prefix') {$table_prefix = $wpdb->prefix; } else {$table_prefix = DWPC_DB_PREFIX;}
   
   //Delete this plugin's entry in the dwpc_plugins table
   $wpdb->delete($table_prefix.'dwpc_plugins',array('name'=>'opt'));
   
  //Delete DWPC Tables - FULL UNINSTALL REMOVE COMMENT TAGS BELOW AND THEN DEACTIVE PLUGIN
  //THIS WILL DISABLE CORE FUNCTIONALITY FOR ALL OF YOUR PLUGINS
  /*
  $wpdb->query("DROP TABLE ".$table_prefix."dwpc_plugins");
  $wpdb->query("DROP TABLE ".$table_prefix."dwpc_visitors");
  $wpdb->query("DROP TABLE ".$table_prefix."dwpc_impressions");
  $wpdb->query("DROP TABLE ".$table_prefix."dwpc_click_filter");
  $wpdb->query("DROP TABLE ".$table_prefix."dwpc_settings");
  */
  
}
        ?>