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 owbb_repository_seq start with 1 increment by 1');

  exec('create table owbb_repositories (
	 	repository_id number(38) not null,
	 	workspace_id number(9) default sys_context(''owb_workspace'',''workspaceID'') not null,
		repos_type varchar2(128) not null,
		repos_version varchar2(128) not null,
	 	name varchar2(128) not null,
	 	dblink_name varchar2(128) not null,
	 	description varchar2(4000),
	 	primary key (repository_id,workspace_id),
	 	foreign key (repos_type, repos_version)
      references owbb_repos_types (repos_type, repos_version),
	 	unique (name),
	 	unique (dblink_name)
	  )
  ');

  exec('create or replace view owbb_db_links_v as
    SELECT  a.db_link,
      a.USERNAME,
      a.PASSWORD,
      a.HOST,
      a.CREATED,
      b.REPOSITORY_ID,
      b.WORKSPACE_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,
          owbb_repositories b
    where a.db_link = b.dblink_name(+)
  ');

  exec('create or replace view owbb_repository_statuses_v as
    SELECT
      b.REPOSITORY_ID,
      b.WORKSPACE_ID,
      b.repository_name,
      owbb_api_administration.application_type(b.db_link) repository_type,
      owbb_api_administration.status(b.db_link) status,
      owbb_api_administration.view_version(b.db_link) version,
      owbb_api_administration.application_type_nls_id(b.db_link) application_type_nls_id,
      b.db_link dblink_name,
      b.HOST_NAME,
      b.DB_SID,
      b.username REPOS_SCHEMA,
      b.DESCRIPTION,
      owbb_api_administration.sql_error(b.db_link) sql_error
    from  owbb_db_links_v b
    where b.REPOSITORY_ID is not null
  ');

  exec('create table owbb_user_repositories (
	 	user_id number(38) not null,
	 	repository_id number(38) not null,
	 	workspace_id number(9) default sys_context(''owb_workspace'',''workspaceID'') not null,
	 	primary key (user_id, repository_id, workspace_id),
	 	foreign key (repository_id, workspace_id) references owbb_repositories (repository_id, workspace_id)
    )
  ');

  exec('create table owbb_group_repositories (
	 	group_id number(38) not null,
	 	repository_id number(38) not null,
	 	workspace_id number(9) default sys_context(''owb_workspace'',''workspaceID'') not null,
	 	primary key (group_id, repository_id, workspace_id),
	 	foreign key (repository_id, workspace_id) references owbb_repositories (repository_id, workspace_id)
    )
  ');

  exec('create or replace view owbb_repositories_v as
    SELECT  a.db_link,
      a.USERNAME,
      a.PASSWORD,
      a.HOST,
      a.CREATED,
      b.REPOSITORY_ID,
      b.WORKSPACE_ID,
      b.name repository_name,
      owbb_api_administration.application_type(a.db_link) repository_type,
      owbb_api_administration.status(a.db_link) status,
      owbb_api_administration.view_version(a.db_link) version,
      owbb_api_administration.application_type_nls_id(a.db_link) application_type_nls_id,
      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,
      a.USERNAME REPOS_SCHEMA,
      b.DESCRIPTION,
      owbb_api_administration.sql_error(a.db_link) sql_error
    from  user_db_links a,
          owbb_repositories b
    where a.db_link = b.dblink_name(+)
    union
    SELECT  b.dblink_name db_link,
      b.name username,
      null password,
      b.dblink_name host,
      null CREATED,
      b.REPOSITORY_ID,
      b.WORKSPACE_ID,
      b.name repository_name,
      b.repos_type repository_type,
      owbb_api_administration.status status,
      owbb_api_administration.view_version version,
      owbb_api_administration.application_type_nls_id application_type_nls_id,
      b.dblink_name host_name,
      null db_sid,
      null port,
      b.name REPOS_SCHEMA,
      b.DESCRIPTION,
      owbb_api_administration.sql_error sql_error
    from  owbb_repositories b
    where b.dblink_name not in (select db_link from user_db_links)
  ');



  exec('create table owbb_group_roles (
	 	group_id number(38) not null,
	 	role_name  varchar2(128) not null,
	 	primary key (group_id, role_name),
	 	foreign key (role_name) references owbb_roles (role_name)
	  )
  ');

  exec('create table owbb_user_roles (
	 	user_id number(38) not null,
	 	role_name varchar2(128) not null,
	 	primary key (user_id, role_name),
	 	foreign key (role_name) references owbb_roles (role_name)
    )
  ');


  exec('create table owbb_administrators (
	 	id number not null,
	 	type varchar2(8) check (type in (''USER'', ''GROUP'')) not null,
	 	primary key (id, type)
    )
  ');

  exec('create or replace view owbb_administrators_v as
	 	select
      id,
      decode(type, ''USER'', owbb_api_administration.user_name(id),
        owbb_api_administration.group_name(id)) name,
	 		type
	 	from
	 		owbb_administrators
		where id >= 0
  ');

  exec('create or replace view owbb_role_access_v as
	 	select
      role_name,
      user_id access_id,
      owbb_api_administration.user_name(user_id) name,
	 		''USER'' access_type,
				''N''	is_admin
	 	from
	 		owbb_user_roles
		where user_id not in (select id from  owbb_administrators where type =''USER'')
		and	user_id >= 0
    union
	 	select
      role_name,
      group_id access_id,
      owbb_api_administration.group_name(group_id) name,
	 		''GROUP'' access_type,
				''N''	is_admin
	 	from
	 		owbb_group_roles
		where group_id not in (select id from  owbb_administrators where type =''GROUP'')
		and	group_id >= 0
	union
		select	a.ROLE_NAME,
				b.id access_id,
				b.name,
 				b.type access_type,
				''Y''	is_admin
		from		owbb_roles a,
				owbb_administrators_v b
		where 	a.role_name <> ''*''
		and	b.id >= 0
  ');

  exec('create or replace view owbb_repository_access_v as
	 	select
      		repository_id,
      		workspace_id,
		      user_id access_id,
		      owbb_api_administration.user_name(user_id) name,
	 		''USER'' access_type,
			''N''	is_admin
	 	from
	 		owbb_user_repositories
		where user_id not in (select id from  owbb_administrators where type =''USER'')
		and	user_id >= 0
	union
	 	select
      		a.repository_id,
      		a.workspace_id,
		      b.id access_id,
		      owbb_api_administration.user_name(b.id) name,
	 		''USER'' access_type,
			''Y''	is_admin
	 	from
	 		owbb_repositories a, owbb_administrators b
		where b.type =''USER''
		and	b.id >= 0
    union
	 	select
      repository_id,
      workspace_id,
      group_id access_id,
      owbb_api_administration.group_name(group_id) name,
	 		''GROUP'' access_type,
			''N''	is_admin
	 	from
	 		owbb_group_repositories
		where group_id not in (select id from  owbb_administrators where type =''GROUP'')
		and	group_id >= 0
	union
	 	select
      		a.repository_id,
      		a.workspace_id,
		      b.id access_id,
		      owbb_api_administration.group_name(b.id) name,
	 		''GROUP'' access_type,
			''Y''	is_admin
	 	from
	 		owbb_repositories a, owbb_administrators b
		where b.type =''GROUP''
		and	b.id >= 0
  ');

		--Portlets

		exec('create table owbb_group_portlets
	 (
	 	group_name varchar2(128) not null,
	 	group_id number(38),
	 	portlet_name varchar2(128) not null,
	 	primary key (group_name, portlet_name)
	 )
   ');

		exec('create table owbb_user_portlets
	 (
	 	user_name varchar2(128) not null,
	 	user_id number(38),
	 	portlet_name varchar2(128) not null,
	 	primary key (user_name, portlet_name)
	 )
   ');

  exec('create table owbb_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)
    )
    ');

		--|
		--|Favorites
		--|	

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

	exec('create table owbb_navigation_favorites ' ||
	'( ' ||
	'	favorite_id number(38) not null, ' ||
	'	user_id number(38) not null, ' ||
	'	repository_id number(38) not null, ' ||
	'	workspace_id number(9) default sys_context(''owb_workspace'',''workspaceID'') 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, workspace_id, role_name, type_name, object_id),   ' ||
	'	foreign key (repository_id, workspace_id) references owbb_repositories (repository_id, workspace_id), ' ||
	'	foreign key (role_name) references owbb_roles (role_name), ' ||
	'	foreign key (type_name) references owbb_types (type_name) ' ||
	')');

	exec('create table owbb_report_favorites ' ||
	'( ' ||
	'	favorite_id number(38) not null, ' ||
	'	user_id number(38) not null, ' ||
	'	repository_id number(38) not null, ' ||
	'	workspace_id number(9) default sys_context(''owb_workspace'',''workspaceID'') 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, workspace_id, role_name, report_id, object_id), ' ||
	'	foreign key (repository_id, workspace_id) references owbb_repositories (repository_id, workspace_id), ' ||
	'	foreign key (role_name) references owbb_roles (role_name), ' ||
	'	foreign key (report_id) references owbb_reports (report_id) ' ||
	')');


		 --Log

		exec('create table owbb_errors
	 (
	 	timestamp date not null,
	 	user_name varchar2(128),
	 	source varchar2(100),
	 	error_code number(38),
	 	error_message varchar2(4000),
	 	other_info varchar2(4000),
	 	failed_stmt varchar2(4000)
	 )
   ');

end;

end;