/*
	JBoss Portal 2.0 (beta 1) setup script for MS SQL Server 2000
	Made from the MySQL setup skrict
	Modified by zapodot@users.sourceforge.net
	Drop tables (and constraints) if they already exist in the database
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[jbp_user_prop]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	alter table jbp_user_prop drop constraint FK93CC461094E6B1C9 
	drop table [jbp_user_prop]
end
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[jbp_user_pref_prop_value]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	alter table jbp_user_pref_prop_value drop constraint FKC496306482E27870 
	drop table [jbp_user_pref_prop_value]
end
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[jbp_user_pref]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	alter table jbp_user_pref drop constraint FK93CC44D0C691B5FF 
	drop table [jbp_user_pref]
end
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[jbp_role_membership]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	alter table jbp_role_membership drop constraint FKF410173894E6A686 
	alter table jbp_role_membership drop constraint FKF410173894E6B1C9 
	drop table [jbp_role_membership]
end
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[jbp_users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	alter table jbp_users drop constraint FKF606014114931AFE 
	drop table [jbp_users]
end
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[jbp_roles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	drop table [jbp_roles]
end
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[jbp_user_pref_set]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	alter table jbp_user_pref_set drop constraint FK97AA4873FAF7DF89 
	drop table [jbp_user_pref_set]
end
GO

-- Create tables
create table jbp_users (
   jbp_uid int IDENTITY PRIMARY KEY ,
   jbp_root_pref_set_id INTEGER unique,
   jbp_uname NVARCHAR(255) unique,
   jbp_givenname NVARCHAR(255),
   jbp_familyname NVARCHAR(255),
   jbp_password NVARCHAR(255),
   jbp_realemail NVARCHAR(255),
   jbp_fakeemail NVARCHAR(255),
   jbp_regdate DATETIME,
   jbp_viewrealemail BIT,
   jbp_enabled BIT
)
go
create table jbp_role_membership (
   jbp_rid INTEGER not null,
   jbp_uid INTEGER not null
   primary key (jbp_uid, jbp_rid)
)
go
create table jbp_user_pref (
   jbp_id int IDENTITY PRIMARY KEY,
   jbp_name NVARCHAR(255) not null,
   jbp_type INTEGER,
   jbp_set_id INTEGER
)
go
create table jbp_user_pref_set (
   jbp_id int IDENTITY PRIMARY KEY ,
   jbp_parent_id INTEGER,
   name NVARCHAR(255) not null
)
go
create table jbp_user_pref_prop_value (
   jbp_prop_id INT not null,
   jbp_value NVARCHAR(255),
   jbp_idx INT not null
   primary key (jbp_prop_id, jbp_idx)
)
create table jbp_roles (
   jbp_rid int IDENTITY PRIMARY KEY,
   jbp_name NVARCHAR(255) unique,
   jbp_displayname NVARCHAR(255) unique
)
go
create table jbp_user_prop (
   jbp_uid INT not null,
   jbp_value NVARCHAR(255),
   jbp_name NVARCHAR(255) not null
   primary key (jbp_uid, jbp_name)
)
go

alter table jbp_users add constraint FKF606014114931AFE foreign key (jbp_root_pref_set_id) references jbp_user_pref_set (jbp_id)
alter table jbp_role_membership add constraint FKF410173894E6A686 foreign key (jbp_rid) references jbp_roles (jbp_rid)
alter table jbp_role_membership add constraint FKF410173894E6B1C9 foreign key (jbp_uid) references jbp_users (jbp_uid)
alter table jbp_user_pref add constraint FK93CC44D0C691B5FF foreign key (jbp_set_id) references jbp_user_pref_set (jbp_id)
alter table jbp_user_pref_set add constraint FK97AA4873FAF7DF89 foreign key (jbp_parent_id) references jbp_user_pref_set (jbp_id)
alter table jbp_user_pref_prop_value add constraint FKC496306482E27870 foreign key (jbp_prop_id) references jbp_user_pref (jbp_id)
alter table jbp_user_prop add constraint FK93CC461094E6B1C9 foreign key (jbp_uid) references jbp_users (jbp_uid)
go



/*
alter table jbp_users add index FKF606014114931AFE (jbp_root_pref_set_id), add constraint FKF606014114931AFE foreign key (jbp_root_pref_set_id) references jbp_user_pref_set (jbp_id);
alter table jbp_role_membership add index FKF410173894E6A686 (jbp_rid), add constraint FKF410173894E6A686 foreign key (jbp_rid) references jbp_roles (jbp_rid);
alter table jbp_role_membership add index FKF410173894E6B1C9 (jbp_uid), add constraint FKF410173894E6B1C9 foreign key (jbp_uid) references jbp_users (jbp_uid);
alter table jbp_user_pref add index FK93CC44D0C691B5FF (jbp_set_id), add constraint FK93CC44D0C691B5FF foreign key (jbp_set_id) references jbp_user_pref_set (jbp_id);
alter table jbp_user_pref_set add index FK97AA4873FAF7DF89 (jbp_parent_id), add constraint FK97AA4873FAF7DF89 foreign key (jbp_parent_id) references jbp_user_pref_set (jbp_id);
alter table jbp_user_pref_prop_value add index FKC496306482E27870 (jbp_prop_id), add constraint FKC496306482E27870 foreign key (jbp_prop_id) references jbp_user_pref (jbp_id);
alter table jbp_user_prop add index FK93CC461094E6B1C9 (jbp_uid), add constraint FK93CC461094E6B1C9 foreign key (jbp_uid) references jbp_users (jbp_uid);
*/