begin

declare
	procedure exec(p_stmt in varchar2)
	is
	begin
		execute immediate p_stmt;
	exception
		when others then
      dbms_output.put_line('===>' ||sqlcode || ' ' || sqlerrm);
      dbms_output.put_line('    ' || substr(p_stmt, 1, 250));
	end;
begin

	
	-- Repositories

	exec('create sequence wmp_repository_seq start with 1 increment by 1');

	exec('create table wmp_repositories ' ||
	'( ' ||
	'	repository_id number(38) not null, ' ||
	'	name varchar2(64) not null, ' ||
	'	host_name varchar2(128) not null, ' ||
	'	dblink_name varchar2(128) not null, ' ||
	'	db_sid varchar2(200) not null, ' ||
	'	repos_schema varchar2(30) not null, ' ||
	'	description varchar2(4000), ' ||
	'	primary key (repository_id), ' ||
	'	unique (name), ' ||
	'	unique (dblink_name) ' ||
	')');

	exec('create table wmp_group_repositories ' ||
	'( ' ||
	'	group_id number(38) not null, ' ||
	'	repository_id number(38) not null, ' ||
	'	primary key (group_id, repository_id), ' ||
	'	foreign key (repository_id) references wmp_repositories (repository_id) ' ||
	')');

	exec('create table wmp_user_repositories ' ||
	'( ' ||
	'	user_id number(38) not null, ' ||
	'	repository_id number(38) not null, ' ||
	'	primary key (user_id, repository_id), ' ||
	'	foreign key (repository_id) references wmp_repositories (repository_id) ' ||
	')');

	exec('create or replace view wmp_accessible_repositories ' ||
	'as ' ||
	'	select  ' ||
	'		repository_id, ' ||
	'		name, ' ||
	'		host_name, ' ||
	'		dblink_name, ' ||
	'		description ' ||
	'	from  ' ||
	'		wmp_repositories ' ||
	'	where ' ||
	'		wmp_api_secure.user_can_access_repository(repository_id) <> 0');

  exec('create or replace view wmp_db_links_v as
    SELECT  a.db_link,
      a.USERNAME,
      a.PASSWORD,
      a.HOST,           
      a.CREATED,        
      b.REPOSITORY_ID,          
      b.name repository_name,
      substr(replace(upper(a.host), '' '', ''''), instr(replace(upper(a.host), '' '', ''''), ''HOST='')+5, instr(replace(upper(a.host), '' '', ''''), '')'', instr(replace(upper(a.host), '' '', ''''), ''HOST='')) -instr(replace(upper(a.host), '' '', ''''), ''HOST='') -5) host_name,
      substr(replace(upper(a.host), '' '', ''''), instr(replace(upper(a.host), '' '', ''''), ''SERVICE_NAME='')+13, instr(replace(upper(a.host), '' '', ''''), '')'', instr(replace(upper(a.host), '' '', ''''), ''SERVICE_NAME='')) -instr(replace(upper(a.host), '' '', ''''), ''SERVICE_NAME='') -13) ||
        substr(replace(upper(a.host), '' '', ''''), instr(replace(upper(a.host), '' '', ''''), ''SID='')+4, instr(replace(upper(a.host), '' '', ''''), '')'', instr(replace(upper(a.host), '' '', ''''), ''SID='')) -instr(replace(upper(a.host), '' '', ''''), ''SID='') -4) db_sid,
      substr(replace(upper(a.host), '' '', ''''), instr(replace(upper(a.host), '' '', ''''), ''PORT='')+5, instr(replace(upper(a.host), '' '', ''''), '')'', instr(replace(upper(a.host), '' '', ''''), ''PORT='')) -instr(replace(upper(a.host), '' '', ''''), ''PORT='') -5) port,
      b.DESCRIPTION            
    from  user_db_links a,
          wmp_repositories b
    where a.db_link = b.dblink_name(+)
  ');

	
		-- Roles
	  
	exec('create table wmp_roles ' ||
	'( ' ||
	'	role_name varchar2(64) not null, ' ||
	'	display_name varchar2(128) not null, ' ||
	'	parent_role_name varchar2(64), ' ||
	'	primary key (role_name), ' ||
	'	foreign key (parent_role_name) references wmp_roles (role_name) ' ||
	')');

	exec('create table wmp_group_roles ' ||
	'( ' ||
	'	group_id number(38) not null, ' ||
	'	role_name varchar2(64) not null, ' ||
	'	primary key (group_id, role_name), ' ||
	'	foreign key (role_name) references wmp_roles (role_name) ' ||
	')');

	exec('create table wmp_user_roles ' ||
	'( ' ||
	'	user_id number(38) not null, ' ||
	'	role_name varchar2(64) not null, ' ||
	'	primary key (user_id, role_name), ' ||
	'	foreign key (role_name) references wmp_roles (role_name) ' ||
	')');

	exec('create or replace view wmp_accessible_roles ' ||
	'as ' ||
	'	select  ' ||
	'		role_name, ' ||
	'		display_name, ' ||
	'		parent_role_name ' ||
	'	from  ' ||
	'		wmp_roles ' ||
	'	where ' ||
	'		wmp_api_secure.user_can_assume_role(role_name) <> 0');

	
		 -- Portlets
	

	exec('create table wmp_group_portlets ' ||
	'( ' ||
	'	group_id number(38) not null, ' ||
	'	portlet_name varchar2(64) not null, ' ||
	'	primary key (group_id, portlet_name) ' ||
	')');

	exec('create table wmp_user_portlets ' ||
	'( ' ||
	'	user_id number(38) not null, ' ||
	'	portlet_name varchar2(64) not null, ' ||
	'	primary key (user_id, portlet_name) ' ||
	')');

		 
		-- Types
	
	  
	exec('create table wmp_images ' ||
	'( ' ||
	'	image_name varchar2(64) not null, ' ||
	'	size_category varchar2(1) check (size_category in (''L'', ''M'', ''S'')) not null, ' ||
	'	gif_file varchar2(1000) not null, ' ||
	'	primary key (image_name, size_category) ' ||
	')');	

	exec('create table wmp_types ' ||
	'( ' ||
	'	type_name varchar2(64) not null, ' ||
	'	image_name varchar2(64), ' ||
	'	display_name varchar2(128) not null, ' ||
	'	primary key (type_name) ' ||
	')');	

	exec('create table wmp_type_aliases ' ||
	'( ' ||
	'	alias_name varchar2(128) not null, ' ||
	'	type_name varchar2(64) not null, ' ||
	'	primary key (alias_name), ' ||
	'	foreign key (type_name) references wmp_types (type_name) ' ||
	')');	

	exec('create table wmp_queries ' ||
	'( ' ||
	'	query_id number(38) not null, ' ||
	'	type_name varchar2(64) not null, ' ||
	'	repository_version varchar2(16) not null, ' ||
	'	usage varchar2(2) check (usage in (''PA'', ''CO'', ''RE'', ''PR'', ''DE'', ''NA'')) not null, ' ||
	'	query_text varchar2(4000), ' ||
	'	primary key (query_id) ' ||
	')');	

	exec('create table wmp_role_queries ' ||
	'( ' ||
	'	query_id number(38) not null, ' ||
	'	role_name varchar2(64) not null, ' ||
	'	primary key (query_id, role_name), ' ||
	'	foreign key (query_id) references wmp_queries (query_id), ' ||
	'	foreign key (role_name) references wmp_roles (role_name) ' ||
	')');	

	
		-- Reports
  
	 
	exec('create sequence wmp_custom_report_seq start with 1 increment by 1');

	exec('create table wmp_reports ' ||
	'( ' ||
	'	report_id number(38) not null, ' ||
	'	type_name varchar2(64) not null, ' ||
	'	report_display_name varchar2(100) not null, ' ||
	'	repository_version varchar2(16) not null, ' ||
	'	report_helper varchar2(128) not null, ' ||
	'	report_category varchar2(64), ' ||
	'	dblink_name varchar2(128), ' ||
	'	alt_dispatch varchar2(3) check (alt_dispatch in (''OPA'')), ' ||
	'	primary key (report_id), ' ||
	'	unique (type_name, report_display_name), ' ||
	'	unique (type_name, report_helper, report_category), ' ||
	'	foreign key (type_name) references wmp_types (type_name) ' ||
  -- for fixing bug 2015795
	--'	foreign key (dblink_name) references wmp_repositories (dblink_name) ' ||
	')');

	exec('create table wmp_role_reports ' ||
	'( ' ||
	'	report_id number(38) not null, ' ||
	'	role_name varchar2(64) not null, ' ||
	'	primary key (report_id, role_name), ' ||
	'	foreign key (report_id) references wmp_reports (report_id), ' ||
	'	foreign key (role_name) references wmp_roles (role_name) ' ||
	')');

	exec('create table lia_xml_table2 ' ||
	'( ' ||
	'	objectid number(9) not null, ' ||
	'	liatype varchar2(10) not null, ' ||
	'	xml clob, '||
	'	html clob, '||
	'	scale number(9), '||
	'	dblink varchar2(128) not null ' ||
	')');

	
	--	Favorites
	 

	exec('create sequence wmp_favorites_seq start with 1 increment by 1');

	exec('create table wmp_navigation_favorites ' ||
	'( ' ||
	'	favorite_id number(38) not null, ' ||
	'	user_id number(38) not null, ' ||
	'	repository_id number(38) not null, ' ||
	'	role_name varchar2(64) not null, ' ||
	'	type_name varchar2(64) not null, ' ||
	'	object_id number(38), ' ||
	'	description varchar2(4000), ' ||
	'	primary key (favorite_id), ' ||
	'	unique (user_id, repository_id, role_name, type_name, object_id),   ' ||
	'	foreign key (repository_id) references wmp_repositories (repository_id), ' ||
	'	foreign key (role_name) references wmp_roles (role_name), ' ||
	'	foreign key (type_name) references wmp_types (type_name) ' ||
	')');

	exec('create table wmp_report_favorites ' ||
	'( ' ||
	'	favorite_id number(38) not null, ' ||
	'	user_id number(38) not null, ' ||
	'	repository_id number(38) not null, ' ||
	'	role_name varchar2(64) not null, ' ||
	'	report_id number(38) not null, ' ||
	'	object_id number(38), ' ||
	'	description varchar2(4000), ' ||
	'	primary key (favorite_id), ' ||
	'	unique (user_id, repository_id, role_name, report_id, object_id), ' ||
	'	foreign key (repository_id) references wmp_repositories (repository_id), ' ||
	'	foreign key (role_name) references wmp_roles (role_name), ' ||
	'	foreign key (report_id) references wmp_reports (report_id) ' ||
	')');

	
	--	Documents
	
	
	exec('create table wmp_binary_docs  ' ||
	'( ' ||
	'	doc_name varchar2(256), ' ||
	'	doc_value blob, ' ||
	'	primary key (doc_name) ' ||
	')');

	exec('create table wmp_text_docs  ' ||
	'( ' ||
	'	doc_name varchar2(256), ' ||
	'	doc_value clob, ' ||
	'	language varchar2(100), ' ||
	'	primary key (doc_name, language) ' ||
	')');

	
		-- Log
 
	   
	exec('create table wmp_errors ' ||
	'( ' ||
	'	timestamp date not null, ' ||
	'	user_name varchar2(64), ' ||
	'	source varchar2(100) not null, ' ||
	'	error_code number(38) not null, ' ||
	'	error_message varchar2(4000) not null, ' ||
	'	other_info varchar2(4000), ' ||
	'	failed_stmt varchar2(4000) ' ||
	')');

	exec('create table lia_refresh_log ' ||
	'( ' ||
	'	user_name varchar2(64), ' ||
	'	timestamp date, ' ||
	'	elapsed_time number, ' ||
      '	repository_id number, ' ||
	'	session_id number ' ||
	')');

	
		-- Preferences
 
	
	exec('create table wmp_preferences ' ||
	'( ' ||
	'	path varchar2(256) not null, ' ||
	'	level_type varchar2(4) check (level_type in (''U'', ''G'', ''S'', ''I'')) not null, ' ||
	'	level_id number(38) not null, ' ||
	'	name varchar2(256) not null, ' ||
	'	value varchar2(256), ' ||
	'	unique (path, level_type, level_id, name) ' ||
	')');

	
		-- Links
	
	  
	exec('create sequence wmp_links_seq start with 1 increment by 1');

	exec('create table wmp_links ' ||
	'( ' ||
	'	link_id number(38) not null, ' ||
	'	link_name varchar2(400) not null, ' ||
	'	user_id varchar2(100) not null, ' ||
	'	repository_id number(38) not null, ' ||
	'	role_name varchar2(64) not null, ' ||
	'	type_name varchar2(64) not null, ' ||
	'	object_id number(38), ' ||
	'	url varchar2(4000), ' ||
	'	is_type_wise varchar2(1), ' ||
	'	is_public varchar2(1), ' ||
	'	is_action varchar2(1), ' ||
	'	is_owb_object varchar2(1), ' ||
	'	description varchar2(4000), ' ||
	'	primary key (link_id), ' ||
	'	unique (user_id, repository_id, role_name, type_name, object_id, link_name),   ' ||
	'	foreign key (repository_id) references wmp_repositories (repository_id), ' ||
	'	foreign key (role_name) references wmp_roles (role_name), ' ||
	'	foreign key (type_name) references wmp_types (type_name) ' ||
	')');

	
	-- NLS
	
	exec('create table wmp_nls ' ||
	'( ' ||
	'	name varchar2(128) ' ||
	')');

	
		-- Preferences Migration 
 
	  
	exec('create sequence wmp_migration_log_seq start with 1 increment by 1');

	exec('create table wmp_migration_log ' ||
	'( ' ||
	'	migration_id  number(38), ' ||
	'	object_name varchar2(2000), ' ||
	'	object_type varchar2(200), ' ||
	'	result number(38), ' ||
	'	status varchar2(200), ' ||
	'	error varchar2(4000) ' ||
	')');

	
		 -- User Defined Properties 
	  
	exec('create table wmp_extended_properties ' ||
	'( ' ||
	'	user_defined_properties varchar2(5) ' ||
	')');

	exec('insert into wmp_extended_properties values(''YES'')');
end;

end;