<?php
	/**************************************************************************\
	* phpGroupWare - Setup                                                     *
	* http://www.phpgroupware.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 2 of the License, or (at your   *
	* option) any later version.                                               *
	\**************************************************************************/	
	$test[] = '0.4.1';
	function contactcenter_upgrade0_4_1() {
		
		$oProc = $GLOBALS['phpgw_setup']->oProc;
		$oProc->DropTable('phpgw_cc_city');
		$oProc->DropTable('phpgw_cc_state');
		$oProc->CreateTable(
			'phpgw_cc_state',array(
				'fd' => array(
					'id_state' => array( 'type' => 'int', 'precision' => 8, 'nullable' => false),
					'id_country' => array( 'type' => 'char', 'nullable' => false, 'precision' => 2),
					'state_name' => array( 'type' => 'varchar', 'precision' => 30),
					'state_symbol' => array( 'type' => 'varchar', 'precision' => 10)
				),
				'pk' => array('id_state'),
				'fk' => array('id_country' => array('phpgw_common_country_list' => 'id_country')),
				'ix' => array(),
				'uc' => array()
			)
		);
		$oProc->CreateTable(
			'phpgw_cc_city', array(
				'fd' => array(
					'id_city' => array( 'type' => 'int', 'precision' => 8, 'nullable' => false ),
					'id_state' => array( 'type' => 'int', 'precision' => 8 ),
					'id_country' => array( 'type' => 'char', 'nullable' => false, 'precision' => 2),
					'city_timezone' => array( 'type' => 'int', 'precision' => 2 ),
					'city_geo_location' => array(  'type'  => 'varchar', 'precision' => 40 ),
					'city_name' => array( 'type'  => 'varchar', 'precision' => 60, 'nullable' => 'false' ),				
				),
				'pk' => array('id_city'),
				'fk' => array('id_state'   => array('phpgw_cc_state' => 'id_state')	),
				'ix' => array(),
				'uc' => array()
			)		
		);		
		
		include("states_pt-br.inc.php"); 
		include("cities_pt-br.inc.php");
		$GLOBALS['setup_info']['contactcenter']['currentver'] = '1.0.0';
		return $GLOBALS['setup_info']['contactcenter']['currentver'];
	}
	$test[] = '1.0.0';
	function contactcenter_upgrade1_0_0() {
		$GLOBALS['setup_info']['contactcenter']['currentver'] = '1.0.005';
		return $GLOBALS['setup_info']['contactcenter']['currentver'];
	}
	$test[] = '1.0.005';
	function contactcenter_upgrade1_0_005() {
		$GLOBALS['setup_info']['contactcenter']['currentver'] = '1.14';
		return $GLOBALS['setup_info']['contactcenter']['currentver'];
	}
	$test[] = '1.14';
	function contactcenter_upgrade1_14() {				
		$GLOBALS['phpgw_setup']->db->query("ALTER TABLE phpgw_cc_connections ALTER COLUMN connection_value TYPE varchar(100)");
		$GLOBALS['setup_info']['contactcenter']['currentver'] = '1.15';
		return $GLOBALS['setup_info']['contactcenter']['currentver'];
	}
	$test[] = '1.15';
	function contactcenter_upgrade1_15() {		
		$GLOBALS['phpgw_setup']->oProc->AddColumn('phpgw_cc_contact','last_status', array('type' => 'char', 'precision' => 1, 'default' => 'N'));
		$GLOBALS['phpgw_setup']->oProc->AddColumn('phpgw_cc_contact','category', array('type' => 'varchar', 'precision' => 20));				
		// Verify if last_update column exists.....
		$GLOBALS['phpgw_setup']->oProc->m_oTranslator->_GetColumns($GLOBALS['phpgw_setup']->oProc,'phpgw_cc_contact', &$sColumns);
 		if(array_search("last_update",explode(",",$sColumns)) === FALSE) {
			$GLOBALS['phpgw_setup']->db->query("ALTER TABLE phpgw_cc_contact ADD COLUMN last_update int8 DEFAULT (date_part('epoch'::text, ('now'::text)::timestamp(3) with time zone) * (1000)::double precision)");
 		}
		$GLOBALS['setup_info']['contactcenter']['currentver'] = '1.20';
		return $GLOBALS['setup_info']['contactcenter']['currentver'];
	}
	$test[] = '1.20';
	function contactcenter_upgrade1_20() {
		$GLOBALS['setup_info']['contactcenter']['currentver'] = '1.20.001';
		return $GLOBALS['setup_info']['contactcenter']['currentver'];
	}
	$test[] = '1.20.001';
	function contactcenter_upgrade1_20_001() {
		$GLOBALS['setup_info']['contactcenter']['currentver'] = '1.21';
		// Verify if plpgsql language is installed.
		$res = $GLOBALS['phpgw_setup']->db->query("select lanname from pg_language where lanname = 'plpgsql'");
		if(!strstr($res,'plpgsql')){
			$GLOBALS['phpgw_setup']->db->query("create language 'plpgsql'");
		}		
		// Verify if trigger Insert() for Shared Contacts exists, else create it.....
		$res = $GLOBALS['phpgw_setup']->db->query("select tgname from pg_trigger where tgname = 'trig_share_catalog_insert'");
		if(!strstr($res,'trig_share_catalog_insert')){
			$GLOBALS['phpgw_setup']->db->query("create function share_catalog_insert() returns trigger as ' begin if ".
				"new.acl_appname = ''contactcenter'' and  new.acl_location <> ''run'' then ".
				"insert into phpgw_cc_contact_rels (id_contact,id_related,id_typeof_contact_relation) ".
				"values (new.acl_location::integer,new.acl_account,1); end if; return new; end;' language 'plpgsql';");		

			$GLOBALS['phpgw_setup']->db->query("create trigger trig_share_catalog_insert after insert on phpgw_acl for " .
				"each row execute procedure share_catalog_insert()"); 
		}
		// Verify if trigger Delete() for Shared Contacts exists, else create it.....
		$res = $GLOBALS['phpgw_setup']->db->query("select tgname from pg_trigger where tgname = 'trig_share_catalog_delete'");		
		if(!strstr($res,'trig_share_catalog_delete')){
			$GLOBALS['phpgw_setup']->db->query("create function share_catalog_delete() returns trigger as '	begin if " .
								"old.acl_appname = ''contactcenter'' then delete from phpgw_cc_contact_rels where " .
								"id_contact=old.acl_location and id_related=old.acl_account and " .
								"id_typeof_contact_relation=1; end if; return new; end;' language 'plpgsql'");
	
			$GLOBALS['phpgw_setup']->db->query("create trigger trig_share_catalog_delete after delete on phpgw_acl for " .
				"each row execute procedure share_catalog_delete()");
		}
		return $GLOBALS['setup_info']['contactcenter']['currentver'];
	}
	$test[] = '1.21';
	function contactcenter_upgrade1_21() {
		$GLOBALS['setup_info']['contactcenter']['currentver'] = '2.0.000';
		// Bug fixing for type cast problem PGSQL version > 8.1. Replacing trigger function:
			$GLOBALS['phpgw_setup']->db->query("CREATE OR REPLACE function share_catalog_delete() returns trigger as '".
					"begin if old.acl_appname = ''contactcenter'' and old.acl_location!=''run'' then delete from ".
					"phpgw_cc_contact_rels where id_contact=old.acl_location::bigint and id_related=old.acl_account ".
					"and id_typeof_contact_relation=1; end if; return new; end;' language 'plpgsql'");
		return $GLOBALS['setup_info']['contactcenter']['currentver'];
	}
	$test[] = '2.0.000';
	function contactcenter_upgrade2_0_000() {
		$GLOBALS['setup_info']['contactcenter']['currentver'] = '2.0.001';
		$GLOBALS['phpgw_setup']->db->query("ALTER TABLE phpgw_cc_contact ADD COLUMN web_page character varying(100)");
 		$GLOBALS['phpgw_setup']->db->query("ALTER TABLE phpgw_cc_contact ADD COLUMN corporate_name character varying(100)");
		$GLOBALS['phpgw_setup']->db->query("ALTER TABLE phpgw_cc_contact ADD COLUMN job_title character varying(40)");
		$GLOBALS['phpgw_setup']->db->query("ALTER TABLE phpgw_cc_contact ADD COLUMN department character varying(30)");
		return $GLOBALS['setup_info']['contactcenter']['currentver'];
	}
	$test[] = '2.0.001';
	function contactcenter_upgrade2_0_001() {
		$GLOBALS['setup_info']['contactcenter']['currentver'] = '2.1.000';
		return $GLOBALS['setup_info']['contactcenter']['currentver'];
	}
?>