-- ************************************************************************************
-- Changes to be made for installing jBoss Portal on jBoss 4.0.1 using Postgresql:
-- ************************************************************************************
-- 1. Create the database:
--        $> createdb jbossportal
-- 2. Load setup.ddl in Postgres
--        jbossportal=# \i /usr/local/jboss-portal-2.0-alpha/setup.ddl
-- 3. Modify portal-ds.xml
--        <?xml version="1.0" encoding="UTF-8"?>
--        <datasources>
--           <local-tx-datasource>
--                <jndi-name>PortalDS</jndi-name>
--                <connection-url>jdbc:postgres:jbossportal</connection-url>
--                <driver-class>org.postgresql.Driver</driver-class>
--                <user-name>postgres</user-name>
--                <password>postgres</password>
--           </local-tx-datasource>
--         </datasources>
--
--  4. Add postgres.jar to /usr/local/jBoss/server/default/lib/
--
-- Submitted by Karan M.G. (karan@resolution.com).
-- 
-- Modified setup.ddl for Postgresql follows.
-- ************************************************************************************

drop table jbp_user_prop;
drop table jbp_users;
drop table jbp_role_membership;
drop table jbp_user_pref;
drop table jbp_user_pref_set;
drop table jbp_user_pref_prop_value;
drop table jbp_roles;
drop table jbp_forums_topics;
drop table jbp_forums_users;
drop table jbp_forums_forums;
drop table jbp_forums_categories;
drop table jbp_forums_watch;
drop table jbp_forums_posts;
drop table jbp_forums_polls;

create table jbp_user_prop (
   jbp_uid INTEGER not null,
   jbp_value VARCHAR(255),
   jbp_name VARCHAR(255) not null,
   primary key (jbp_uid, jbp_name)
);
create table jbp_users (
   jbp_uid SERIAL PRIMARY KEY,
   jbp_root_pref_set_id INTEGER unique,
   jbp_uname VARCHAR(255) unique,
   jbp_givenname VARCHAR(255),
   jbp_familyname VARCHAR(255),
   jbp_password VARCHAR(255),
   jbp_realemail VARCHAR(255),
   jbp_fakeemail VARCHAR(255),
   jbp_regdate TIMESTAMP,
   jbp_viewrealemail BIT,
   jbp_enabled BIT
);
create table jbp_role_membership (
   jbp_rid INTEGER not null,
   jbp_uid INTEGER not null,
   primary key (jbp_uid, jbp_rid)
);
create table jbp_user_pref (
   jbp_id SERIAL PRIMARY KEY,
   jbp_name VARCHAR(255) not null,
   jbp_type INTEGER,
   jbp_set_id INTEGER
);
create table jbp_user_pref_set (
   jbp_id SERIAL PRIMARY KEY,
   name VARCHAR(255) not null,
   jbp_parent_id INTEGER
);
create table jbp_user_pref_prop_value (
   jbp_prop_id INTEGER not null,
   jbp_value VARCHAR(255),
   jbp_idx INTEGER not null,
   primary key (jbp_prop_id, jbp_idx)
);
create table jbp_roles (
   jbp_rid SERIAL PRIMARY KEY,
   jbp_name VARCHAR(255) unique,
   jbp_displayname VARCHAR(255) unique
);
create table jbp_forums_topics (
   jbp_id SERIAL PRIMARY KEY,
   jbp_forum_id INTEGER,
   jbp_views INTEGER,
   jbp_replies INTEGER,
   jbp_first_post_id INTEGER,
   jbp_last_post_id INTEGER,
   jbp_last_post_date TIMESTAMP,
   jbp_poster INTEGER,
   jbp_type INTEGER,
   jbp_status INTEGER,
   jbp_subject VARCHAR(255)
);
create table jbp_forums_users (
   jbp_id SERIAL PRIMARY KEY,
   jbp_username VARCHAR(255),
   jbp_nb_posts INTEGER
);
create table jbp_forums_forums (
   jbp_id SERIAL PRIMARY KEY,
   jbp_category_id INTEGER,
   jbp_description VARCHAR(255),
   jbp_last_post_id INTEGER,
   jbp_name VARCHAR(255),
   jbp_order INTEGER,
   jbp_topic_size INTEGER,
   jbp_post_size INTEGER,
   jbp_prune_enable BIT,
   jbp_prune_next INTEGER,
   jbp_status INTEGER,
   jbp_type INTEGER
);
create table jbp_forums_categories (
   jbp_id SERIAL PRIMARY KEY,
   jbp_order INTEGER,
   jbp_title VARCHAR(255)
);
create table jbp_forums_watch (
   jbp_id SERIAL PRIMARY KEY,
   jbp_forum_id INTEGER,
   jbp_mode INTEGER,
   jbp_user_id INTEGER
);
create table jbp_forums_posts (
   jbp_id SERIAL PRIMARY KEY,
   jbp_topic_id INTEGER,
   jbp_edit_count INTEGER,
   jbp_edit_date TIMESTAMP,
   jbp_create_date TIMESTAMP,
   jbp_subject VARCHAR(255),
   jbp_text VARCHAR(255),
   jbp_poster INTEGER
);
create table jbp_forums_polls (
   jbp_id SERIAL PRIMARY KEY
);

create index FK143B175794E6B1C9 on jbp_user_prop (jbp_uid);
alter table jbp_user_prop add constraint FK143B175794E6B1C9 foreign key (jbp_uid) references jbp_users (jbp_uid);

create index FKF606014114931AFE on jbp_users (jbp_root_pref_set_id);
alter table jbp_users add constraint FKF606014114931AFE foreign key (jbp_root_pref_set_id) references jbp_user_pref_set (jbp_id);

create index FKF410173894E6A686 on jbp_role_membership (jbp_rid);
alter table jbp_role_membership add constraint FKF410173894E6A686 foreign key (jbp_rid) references jbp_roles (jbp_rid);

create index FKF410173894E6B1C9 on jbp_role_membership (jbp_uid);
alter table jbp_role_membership add constraint FKF410173894E6B1C9 foreign key (jbp_uid) references jbp_users (jbp_uid);

create index FK93CC44D0C691B5FF on jbp_user_pref (jbp_set_id);
alter table jbp_user_pref add constraint FK93CC44D0C691B5FF foreign key (jbp_set_id) references jbp_user_pref_set (jbp_id);

create index FK97AA4873FAF7DF89 on jbp_user_pref_set (jbp_parent_id);
alter table jbp_user_pref_set add constraint FK97AA4873FAF7DF89 foreign key (jbp_parent_id) references jbp_user_pref_set (jbp_id);

create index FKC496306482E27870 on jbp_user_pref_prop_value (jbp_prop_id);
alter table jbp_user_pref_prop_value add constraint FKC496306482E27870 foreign key (jbp_prop_id) references jbp_user_pref (jbp_id);

create index FK6C1A04CA2AE40318 on jbp_forums_topics (jbp_last_post_id);
alter table jbp_forums_topics add constraint FK6C1A04CA2AE40318 foreign key (jbp_last_post_id) references jbp_forums_posts (jbp_id);

create index FK6C1A04CAC0A088C4 on jbp_forums_topics (jbp_first_post_id);
alter table jbp_forums_topics add constraint FK6C1A04CAC0A088C4 foreign key (jbp_first_post_id) references jbp_forums_posts (jbp_id);

create index FK6C1A04CAC1FFF134 on jbp_forums_topics (jbp_poster);
alter table jbp_forums_topics add constraint FK6C1A04CAC1FFF134 foreign key (jbp_poster) references jbp_forums_users (jbp_id);

create index FK6C1A04CA31595B20 on jbp_forums_topics (jbp_forum_id);
alter table jbp_forums_topics add constraint FK6C1A04CA31595B20 foreign key (jbp_forum_id) references jbp_forums_forums (jbp_id);

create index FK543743182AE40318 on jbp_forums_forums (jbp_last_post_id);
alter table jbp_forums_forums add constraint FK543743182AE40318 foreign key (jbp_last_post_id) references jbp_forums_posts (jbp_id);

create index FK543743183BA91075 on jbp_forums_forums(jbp_category_id);
alter table jbp_forums_forums add constraint FK543743183BA91075 foreign key (jbp_category_id) references jbp_forums_categories (jbp_id);

create index FKF31C8C6931595B20 on jbp_forums_watch (jbp_forum_id);
alter table jbp_forums_watch add constraint FKF31C8C6931595B20 foreign key (jbp_forum_id) references jbp_forums_forums (jbp_id);

create index FKF31C8C698C8A7708 on jbp_forums_watch(jbp_user_id);
alter table jbp_forums_watch add constraint FKF31C8C698C8A7708 foreign key (jbp_user_id) references jbp_forums_users (jbp_id);

create index FKF2C0436DDB927852 on jbp_forums_posts (jbp_topic_id);
alter table jbp_forums_posts add constraint FKF2C0436DDB927852 foreign key (jbp_topic_id) references jbp_forums_topics (jbp_id);

create index FKF2C0436DC1FFF134 on jbp_forums_posts (jbp_poster);
alter table jbp_forums_posts add constraint FKF2C0436DC1FFF134 foreign key (jbp_poster) references jbp_forums_users (jbp_id);

insert into jbp_users (jbp_uid, jbp_uname, jbp_password, jbp_realemail, jbp_regdate, jbp_viewrealemail, jbp_enabled) values ('1', 'admin', MD5('admin'), 'portal@example.com', NOW(), '1', '1');  
insert into jbp_users (jbp_uid, jbp_uname, jbp_password, jbp_realemail, jbp_regdate, jbp_viewrealemail, jbp_enabled) values ('2', 'user', MD5('user'), 'portal@example.com', NOW(), '1', '1');  
insert into jbp_roles values ('1', 'Admins', 'Administrators');
insert into jbp_roles values ('2', 'Users', 'Users');
insert into jbp_role_membership values ('1', '1');
insert into jbp_role_membership values ('2', '2');
insert into jbp_forums_categories values ('1', '1', 'Dummy demo category');
insert into jbp_forums_forums values ('1', '1', 'First description', null, 'First forum', '10', '0', '0', '1', '0', '0', '0');
insert into jbp_forums_forums values ('2', '1', 'Second description', null, 'Second forum', '20', '0', '0', '1', '0', '0', '0');