<?php 

//
// Open Web Analytics - An Open Source Web Analytics Framework
//
// Copyright 2006 Peter Adams. All rights reserved.
//
// Licensed under GPL v2.0 http://www.gnu.org/copyleft/gpl.html
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
//
// $Id$
//

/**
 * 005 Update Class
 * 
 * @author      Peter Adams <peter@openwebanalytics.com>
 * @copyright   Copyright &copy; 2006 Peter Adams <peter@openwebanalytics.com>
 * @license     http://www.gnu.org/copyleft/gpl.html GPL v2.0
 * @category    owa
 * @package     owa
 * @version		$Revision$	      
 * @since		owa 1.3
 */


class owa_base_005_update extends owa_update {
	
	var $schema_version = 5;
	var $is_cli_mode_required = true;
	
	function up() {
		
		$tables = array('owa_session', 'owa_request', 'owa_click', 'owa_feed_request');
		
		foreach ($tables as $table) {
				
			// add yyyymmdd column to owa_session
			$db = owa_coreAPI::dbSingleton();
			$db->addColumn($table, 'yyyymmdd', 'INT');
			$db->addIndex($table, 'yyyymmdd');
			$ret = $db->query("update $table set yyyymmdd = 
						concat(cast(year as CHAR), lpad(CAST(month AS CHAR), 2, '0'), lpad(CAST(day AS CHAR), 2, '0')) ");
			
			if ($ret == true) {
				$this->e->notice('Added yyyymmdd column to '.$table);
			} else {
				$this->e->notice('Failed to add yyyymmdd column to '.$table);
				return false;
			}	
		}
		
		$visitor = owa_coreAPI::entityFactory('base.visitor');
		
		$ret = $visitor->addColumn('num_prior_sessions');
		
		if (!$ret) {
			$this->e->notice('Failed to add num_prior_sessions column to owa_visitor');
			return false;
		}
		
		$ret = $visitor->addColumn('first_session_yyyymmdd');
		
		if (!$ret) {
			$this->e->notice('Failed to add first_session_yyyymmdd column to owa_visitor');
			return false;
		}
		
		$ret = $db->query("update owa_visitor set first_session_yyyymmdd = 
						concat(cast(first_session_year as CHAR), lpad(CAST(first_session_month AS CHAR), 2, '0'), lpad(CAST(first_session_day AS CHAR), 2, '0')) ");
						
		if (!$ret) {
			$this->e->notice('Failed to populate first_session_yyyymmdd column in owa_visitor');
			return false;
		}
		
		$request = owa_coreAPI::entityFactory('base.request');
		
		$ret = $request->addColumn('prior_document_id');
		
		if (!$ret) {
			$this->e->notice('Failed to add prior_document_id column to owa_request');
			return false;
		}
		
		$ret = $request->addColumn('num_prior_sessions');
		
		if (!$ret) {
			$this->e->notice('Failed to add num_prior_sessions column to owa_request');
			return false;
		}
		
		
		$session = owa_coreAPI::entityFactory('base.session');
		
		$ret = $session->addColumn('num_prior_sessions');
		
		if (!$ret) {
			$this->e->notice('Failed to add num_prior_sessions column to owa_session');
			return false;
		}
		
		
		$ret = $session->addColumn('is_bounce');
		
		if (!$ret) {
			$this->e->notice('Failed to add is_bounce column to owa_session');
			return false;
		}
		
		$ret = $db->query("update owa_session set is_bounce = true WHERE num_pageviews = 1");
		
		if (!$ret) {
			$this->e->notice('Failed to populate is_bounce column in owa_session');
			return false;
		}
		
		$ret = $session->addColumn('referring_search_term_id');
		
		if (!$ret) {
			$this->e->notice('Failed to add referring_search_term_id column in owa_session');
			return false;
		}
		
		$ret = $session->addColumn('days_since_prior_session');
		
		if (!$ret) {
			$this->e->notice('Failed to add days_since_prior_session column in owa_session');
			return false;
		}
		
		$ret = $db->query("update owa_session set days_since_prior_session = round(time_sinse_priorsession/(3600*24)) WHERE time_sinse_priorsession IS NOT NULL and time_sinse_priorsession > 0");
		
		if (!$ret) {
			$this->e->notice('Failed to populate days_since_prior_session column in owa_session');
			return false;
		}

		$ret = $session->addColumn('days_since_first_session');
		
		
		if (!$ret) {
			$this->e->notice('Failed to add days_since_first_session column in owa_session');
			return false;
		}
		
		$ret = $db->query("update owa_session, owa_visitor set owa_session.days_since_first_session = round((owa_session.timestamp - owa_visitor.first_session_timestamp)/(3600*24)) WHERE owa_session.visitor_id = owa_visitor.id AND owa_visitor.first_session_timestamp IS NOT NULL");
		
		if (!$ret) {
			$this->e->notice('Failed to populate days_since_first_session column in owa_session');
			return false;
		}
		
		// add api column
		$u = owa_coreAPI::entityFactory('base.user');
		$ret = $u->addColumn('api_key');
		
		if (!$ret) {
			$this->e->notice('Failed to add api_key column to owa_user');
			return false;
		}
		
		// add uri column
		$d = owa_coreAPI::entityFactory('base.document');
		$d->addColumn('uri');
		$ret = $db->query("update owa_document set uri = substring_index(SUBSTR(url FROM 1+ length(substring_index(url, '/', 3))), '#', 1) ");
		
		if (!$ret) {
			$this->e->notice('Failed to add uri column to owa_document');
			return false;
		}
		
		$a = owa_coreAPI::entityFactory('base.action_fact');
		$ret = $a->createTable();
		
		if ($ret === true) {
			$this->e->notice('Action fact entity table created');
		} else {
			$this->e->notice('Action fact entity table creation failed');
			return false;
		}		
		
		$st = owa_coreAPI::entityFactory('base.search_term_dim');
		$ret = $st->createTable();
		
		if ($ret === true) {
			$this->e->notice('Search Term Dimension entity table created');
		} else {
			$this->e->notice('Search Term Dimension  entity table creation failed');
			return false;
		}
		
		// migrate search terms to new table
		$ret = $db->query(
			"INSERT INTO
				owa_search_term_dim (id, terms, term_count) 
			SELECT 
				distinct(CRC32(LOWER(query_terms))) as id, 
				query_terms as terms, 
				length(query_terms) + 1 - length(replace(query_terms,' ','')) as term_count 
			FROM 
				owa_referer
			WHERE
				query_terms != ''"
		);
		
		if (!$ret) {
			$this->e->notice('Failed to migrate search terms to new table.');
			return false;
		}
		
		//populate search term foreign key in session table
		$ret = $db->query(
			"UPDATE 
				owa_session as session, owa_referer as referer
			SET
    			session.referring_search_term_id = (CRC32(LOWER(referer.query_terms))) 
			WHERE
    			session.referer_id = referer.id and
    			session.referer_id != 0 AND
    			referer.query_terms != ''"
    	);
		
		if (!$ret) {
			$this->e->notice('Failed to add referring_search_term_id values to owa_session');
			return false;
		}		
		
		//populate search source in session table
		$ret = $db->query(
			"UPDATE 
				owa_session as session
			SET
    			session.source = 'organic-search'
			WHERE
    			session.referring_search_term_id IS NOT null"
    	);
		
		if (!$ret) {
			$this->e->notice('Failed to populate session.source values for organic-search');
			return false;
		}
		
		//populate search source in session table
		$ret = $db->query(
			"UPDATE 
				owa_session as session
			SET
    			session.source = 'referral'
			WHERE
    			session.referer_id != 0 AND
    			session.referer_id != '' AND
    			session.referer_id IS NOT null AND
    			session.source != 'feed' AND
    			session.source != 'organic-search'"
    	);
		
		if (!$ret) {
			$this->e->notice('Failed to populate session.source values for referral');
			return false;
		}		
		
		
		// add apiKeys to each user
		$users = $db->get_results("select user_id from owa_user");
		
		foreach ($users as $user) {
			
			$u = owa_coreAPI::entityFactory('base.user');
			$u->load($user['user_id'],'user_id');
			
			if (!$u->get('api_key')) {
				$u->set('api_key', $u->generateTempPasskey($u->get('user_id')));
				$u->update();
			}
		}
		
		// change character encoding to UTF-8
		$tables = array('owa_request', 'owa_session', 'owa_feed_request', 'owa_click', 'owa_document', 'owa_ua', 'owa_site', 'owa_user', 'owa_configuration', 'owa_visitor', 'owa_os', 'owa_impression', 'owa_host', 'owa_exit','owa_domstream');
		
		foreach ($tables as $table) {
			
			// change snippet dtd 
			$ret = $db->query(sprintf("ALTER TABLE %s CONVERT TO CHARACTER SET utf8", $table));
			
			if (!$ret) {
				$this->e->notice('Failed to change table character encoding for: ' .$table);
				return false;
			}
			
		}
		
		// change snippet dtd 
		$ret = $db->query("ALTER TABLE owa_referer MODIFY snippet MEDIUMTEXT");
		
		if (!$ret) {
			$this->e->notice('Failed to modify snippet column of owa_referer');
			return false;
		}
		
		// change snippet dtd 
		$ret = $db->query("ALTER TABLE owa_domstream MODIFY page_url VARCHAR(255)");
		
		if (!$ret) {
			$this->e->notice('Failed to modify page_url column of owa_domstream');
			return false;
		}
		
		// change snippet dtd 
		$ret = $db->query("ALTER TABLE owa_domstream MODIFY events MEDIUMTEXT");
		
		if (!$ret) {
			$this->e->notice('Failed to modify events column of owa_domstream');
			return false;
		}
		
		// change snippet dtd 
		$ret = $db->query("ALTER TABLE owa_site MODIFY description MEDIUMTEXT");
		
		if (!$ret) {
			$this->e->notice('Failed to modify description column of owa_site');
			return false;
		}
		
		// check for bad permissions on config file
		if (file_exists(OWA_DIR . 'owa-config.php')) {
			@chmod(OWA_DIR . 'owa-config.php', 0750);
		}
		
		if (file_exists(OWA_DIR . 'conf/owa-config.php')) {
			@chmod(OWA_DIR . 'conf/owa-config.php', 0750);
		}
		
		if (file_exists(OWA_DIR . 'cli.php')) {
			@chmod(OWA_DIR . 'cli.php', 0700);
		}
		
		// must return true
		return true;
	}
	
	function down() {
	
		$visitor = owa_coreAPI::entityFactory('base.visitor');
		$visitor->dropColumn('num_prior_sessions');
		$visitor->dropColumn('first_session_yyyymmdd');
		$session = owa_coreAPI::entityFactory('base.session');
		$session->dropColumn('yyyymmdd');
		$session->dropColumn('is_bounce');
		$session->dropColumn('referring_search_term_id');
		$session->dropColumn('days_since_first_session');
		$session->dropColumn('days_since_prior_session');
		$session->dropColumn('num_prior_sessions');
		$request = owa_coreAPI::entityFactory('base.request');
		$request->dropColumn('yyyymmdd');
		$request->dropColumn('prior_document_id');
		$request->dropColumn('num_prior_sessions');
		$click = owa_coreAPI::entityFactory('base.click');
		$click->dropColumn('yyyymmdd');
		$feed_request = owa_coreAPI::entityFactory('base.feed_request');
		$feed_request->dropColumn('yyyymmdd');
		$u = owa_coreAPI::entityFactory('base.user');
		$u->dropColumn('api_key');
		$u = owa_coreAPI::entityFactory('base.document');
		$u->dropColumn('uri');
		$af = owa_coreAPI::entityFactory('base.action_fact');
		$af->dropTable();
		$st = owa_coreAPI::entityFactory('base.search_term_dim');
		$st->dropTable();
		
		return true;
	}
}

?>