CREATE SEQUENCE twaccid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twaccid');

CREATE TABLE twig_accounts (
   id 		int4 DEFAULT nextval ( 'twaccid'),
   username	varchar(128),
   password 	varchar(128),
   SID		int4,
   onnow	INT,
   expire	INT,
   laston	INT,
   PRIMARY KEY  (id)
);

CREATE INDEX twig_accounts_username_index ON twig_accounts (username);

CREATE SEQUENCE twasid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twasid');

CREATE TABLE twig_acls (
   SID 		int4 DEFAULT nextval ( 'twasid'),
   ident 	varchar(128) NOT NULL,
   alevel 	int4 DEFAULT 0
);

CREATE INDEX twig_acls_sid_index ON twig_acls (SID);
CREATE INDEX twig_acls_ident_index ON twig_acls (ident);

CREATE TABLE twig_acl_groups (
   SID 		int4 DEFAULT 0 NOT NULL,
   groupname 	varchar(64) NOT NULL,
   username 	varchar(128) NOT NULL
);

CREATE INDEX twig_acl_groups_groupname_index ON twig_acl_groups (groupname);
CREATE INDEX twig_acl_groups_username_index ON twig_acl_groups (username);

CREATE SEQUENCE twanid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twanid');

CREATE TABLE twig_announce (
   	id 		int4 DEFAULT nextval ( 'twanid'),
   	thedate		TIMESTAMP,
   	username 	VARCHAR(128) NOT NULL,
	email		varchar(128),
   	subject 	VARCHAR(128) NOT NULL,
   	announce 	TEXT NOT NULL,
   	PRIMARY KEY 	(id)
);

CREATE SEQUENCE twbmid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twbmid');

CREATE TABLE twig_bookmarks (
   id	   	int4 DEFAULT nextval ( 'twbmid'),
   username	VARCHAR(128),
   thedate	TIMESTAMP,
   groupid	INT,
   item		TEXT,
   description	TEXT,
   url		TEXT,
   PRIMARY KEY  (id)
);

CREATE INDEX twig_acl_bookmarks_username_index ON twig_bookmarks (username);
CREATE INDEX twig_acl_bookmarks_groupid_index ON twig_bookmarks (groupid);

CREATE SEQUENCE twbprf start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twbprf');

CREATE TABLE twig_bookmarks_prefs (
   id 		int4 DEFAULT nextval ( 'twbprf' ),
   username 	varchar(128),
   sortby 	varchar(10) NOT NULL,
   sortorder 	int4 DEFAULT '0' NOT NULL,
   dgroup 	int4 DEFAULT '0' NOT NULL,
   pagesize 	int4 DEFAULT '0' NOT NULL,
   viewgrp 	varchar(10) DEFAULT '0' NOT NULL,
   PRIMARY KEY  (id)
);

CREATE INDEX twig_bookmarks_prefs_username_index ON twig_bookmarks_prefs (username);

CREATE SEQUENCE twconid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twconid');

CREATE TABLE twig_contacts (
 id		int4 DEFAULT nextval ( 'twconid') ,
 username	VARCHAR(128),
 thedate	TIMESTAMP,
 groupid	INT,
 item		TEXT,
 description	TEXT,
 url		TEXT,
 firstname	VARCHAR(100),
 lastname	VARCHAR(100),
 email		VARCHAR(100),
 company	VARCHAR(100),
 home_phone	VARCHAR(20),
 work_phone	VARCHAR(20),
 mobile		VARCHAR(30),
 fax		VARCHAR(20),
 address1	VARCHAR(200),
 address2	VARCHAR(200),
 city		VARCHAR(100),
 state		VARCHAR(10),
 zipcode	VARCHAR(10),
 country	VARCHAR(100),
 email2 	VARCHAR(100),
 email3 	VARCHAR(200),
 title 		VARCHAR(100),
 pager 		VARCHAR(30),
 ofax 		VARCHAR(30),
 department 	VARCHAR(100),
 office 	VARCHAR(100),
 spouse 	VARCHAR(100),
 bday 		VARCHAR(40),
 oaddress1 	VARCHAR(100),
 oaddress2 	VARCHAR(100),
 ocity 		VARCHAR(100),
 ozip 		VARCHAR(10),
 ostate 	VARCHAR(10),
 ocountry 	VARCHAR(100),
 type		int4,
 userpers	int4,
 PRIMARY KEY	(id)
);

CREATE INDEX twig_contacts_username_index ON twig_contacts (username);
CREATE INDEX twig_contacts_groupid_index ON twig_contacts (groupid);

CREATE SEQUENCE twcprf start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twcprf');

CREATE TABLE twig_contacts_lists_entries (
   listid	int4 DEFAULT 0 NOT NULL,
   type		int4 DEFAULT 0 NOT NULL,
   item		int4 DEFAULT 0 NOT NULL,
   subitem	int4
);

CREATE INDEX twig_contacts_lists_entries_listid_index ON twig_contacts_lists_entries (listid);
CREATE INDEX twig_contacts_lists_entries_item_index ON twig_contacts_lists_entries (item);

CREATE TABLE twig_contacts_prefs (
   id 		int4 DEFAULT nextval ( 'twcprf' ),
   username 	varchar(128),
   sortby 	varchar(10) NOT NULL,
   sortorder 	int4 DEFAULT 0 NOT NULL,
   dgroup 	int4 DEFAULT 0 NOT NULL,
   pagesize	int4 DEFAULT 0 NOT NULL,
   viewgrp	varchar(10) DEFAULT 0 NOT NULL,
   PRIMARY KEY  (id)
);

CREATE INDEX twig_contacts_prefs_username_index ON twig_contacts_prefs (username);

CREATE TABLE twig_context (
   id 		varchar(40) NOT NULL,
   username 	varchar(128) NOT NULL,
   context 	text NOT NULL,
   sid 		int4 DEFAULT 0 NOT NULL
);

CREATE INDEX twig_context_id_index ON twig_context (id);
CREATE INDEX twig_context_username_index ON twig_context (username);
CREATE INDEX twig_context_sid_index ON twig_context (sid);

CREATE SEQUENCE twfoldid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twfoldid');

CREATE TABLE twig_folders (
   id 		int4 DEFAULT nextval ( 'twfoldid'),
   owner 	VARCHAR(128),
   server 	VARCHAR(255),
   port 	int4,
   groupname 	VARCHAR(255),
   username 	VARCHAR(128),
   password 	VARCHAR(128),
   servertype 	VARCHAR(10),
   PRIMARY KEY  (id)
);

CREATE INDEX twig_folders_owner_index ON twig_folders (owner);
CREATE INDEX twig_folders_groupname_index ON twig_folders (groupname);

CREATE SEQUENCE twgpid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twgpid');

CREATE TABLE twig_global_prefs (
   id 		int4 DEFAULT nextval ( 'twgpid'),
   username 	varchar(128),
   mstyle1 	int4 DEFAULT 0 NOT NULL,
   mstyle2 	int4 DEFAULT 3 NOT NULL,
   tstyle1 	int4 DEFAULT 5 NOT NULL,
   language 	varchar(20),
   mailfoot	int4,
   prevnext	varchar(20),
   timeformat	int4,
   PRIMARY KEY (id)
);

CREATE INDEX twig_global_prefs_username_index ON twig_global_prefs (username);

CREATE SEQUENCE groupid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('groupid');

CREATE TABLE twig_groups (
   id		int4 DEFAULT nextval ( 'groupid' ),
   owner	varchar(128),
   groupname	varchar(100),
   type		varchar(20),
   PRIMARY KEY	(id)
);

CREATE INDEX twig_groups_owner_index ON twig_groups (owner);

CREATE TABLE twig_lhsqltable (
   	LID 		varchar(128) DEFAULT 0,
   	username 	varchar(128) NOT NULL,
   	password 	varchar(128) NOT NULL,
   	expire 		int DEFAULT 0,
	PRIMARY KEY	(LID)
);

CREATE SEQUENCE twprefsid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twprefsid');

CREATE TABLE twig_mail_prefs (
	id		int4 DEFAULT nextval ( 'twprefsid') ,
	username	varchar(128),
	realname	TEXT,
	signature	TEXT,
 	linewrap	TEXT,
	replyto		TEXT,
	pagesize	INT,
	sortby		varchar(10),
	sortorder	INT,
	dande		INT,
   	savesent 	INT,
   	sentfold 	TEXT,
   	draftfold 	TEXT,
	cmdline2	INT,
	newwindow	INT,
	newcontact	INT,
	numup		INT,
	mailtree 	INT,
	mtexpand 	INT,
	mtview 		varchar(255),
	mdn	 	INT,
	mdn_mode 	INT,
	areply		INT,
	aforward	INT,
	clines		INT,
	mande		INT,
	dreturn		INT,
	fromaddress	varchar(128),
	PRIMARY KEY	(id)
);

CREATE INDEX twig_mail_prefs_username_index ON twig_mail_prefs (username);

CREATE SEQUENCE twmpid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twmpid');

CREATE TABLE twig_main_prefs (
   id 		int4 DEFAULT nextval ( 'twmpid'),
   username 	varchar(128),
   features 	varchar(128),
   maxmail	INT,
   maxmeetings	INT,
   maxschedule	INT,
   maxtodo	INT,
   PRIMARY KEY	(id)
);

CREATE INDEX twig_main_prefs_username_index ON twig_main_prefs (username);

CREATE SEQUENCE twmeetid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twmeetid');

CREATE TABLE twig_meetings (
  	id 		int4 DEFAULT nextval ('twmeetid'),
  	groupid 	int4 DEFAULT '0' NOT NULL,
  	username 	varchar(128),
  	secretary	varchar(128),
  	responsible	varchar(128),
  	thedate 	TIMESTAMP,
  	itemstart 	TIMESTAMP,
  	itemstop 	TIMESTAMP,
  	itemdone 	TIMESTAMP,
  	item 		text,
  	description 	text,
  	minutes		text,
  	conclusion 	text,
  	actionitems 	text,
  	location	int4 DEFAULT '0' NOT NULL,
  	userlimit 	int4 DEFAULT '0' NOT NULL,
  	closed 		int4 DEFAULT '0' NOT NULL,
  	deleted 	int4 DEFAULT '0' NOT NULL,
  	private 	int4 DEFAULT '0' NOT NULL,
	PRIMARY KEY	(id)
);

CREATE SEQUENCE twmeetpid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twmeetpid');

CREATE TABLE twig_meetings_prefs (
  	id 		int4 DEFAULT nextval ('twmeetpid'),
  	username 	varchar(128),
  	dgroup 		int4 DEFAULT '0' NOT NULL,
  	mainview	int4 DEFAULT '0' NOT NULL,
  	pagesize	int4 DEFAULT '0' NOT NULL,
  	viewgrp		varchar(10) DEFAULT '0' NOT NULL,
	PRIMARY KEY	(id)
);

CREATE SEQUENCE twmeetrid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twmeetrid');

CREATE TABLE twig_meetings_registration (
  	id 		int4 DEFAULT nextval ('twmeetrid'),
  	username 	varchar(128),
  	eventid 	int4 DEFAULT '0' NOT NULL,
  	thedate 	TIMESTAMP,
  	rsvp 		int4 DEFAULT '0' NOT NULL,
  	PRIMARY KEY 	(id)
);

CREATE SEQUENCE twmeetiid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twmeetiid');

CREATE TABLE twig_meetings_invitations (
  	id 		int4 DEFAULT nextval ('twmeetiid'),
  	username 	varchar(128),
  	eventid 	int4 DEFAULT '0' NOT NULL,
  	thedate		TIMESTAMP,
  	priority	int4 DEFAULT '0' NOT NULL,
  	PRIMARY KEY 	(id)
);

CREATE SEQUENCE memberid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('memberid');

CREATE TABLE twig_members (
	id		int4 DEFAULT nextval ( 'memberid' ),
	groupid		INT,
	username	varchar(128),
	writeperms	INT,
	PRIMARY KEY	(id)
);

CREATE INDEX twig_members_groupid_index ON twig_members (groupid);

CREATE SEQUENCE twnpid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twnpid');

CREATE TABLE twig_news_prefs (
   id 		int4 DEFAULT nextval ( 'twnpid'),
   username	varchar(128),
   replyto 	text,
   pagesize 	int4,
   newwindow 	int4,
   linewrap 	text,
   realname 	text,
   signature 	text,
   inline 	int4,
   PRIMARY KEY  (id)
);

CREATE INDEX twig_news_prefs_username_index ON twig_news_prefs (username);

CREATE SEQUENCE twnoteid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twnoteid');

CREATE TABLE twig_notes (
	id		int4 DEFAULT nextval ( 'twnoteid' ),
        username        VARCHAR(128) NOT NULL,
        thedate         TIMESTAMP,
        groupid         INT4 NOT NULL,
        item            TEXT,
        description     TEXT,
        attachname      TEXT,
	attachmime	TEXT,
        attachment      OID,
	PRIMARY KEY	(id)
);

CREATE INDEX twig_notes_username_index ON twig_notes (username);

CREATE SEQUENCE twnotepid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twnotepid');

CREATE TABLE twig_notes_prefs (
	id		int4 DEFAULT nextval ( 'twnotepid' ),
	username	VARCHAR(128) NOT NULL,
	sortby		VARCHAR(10) NOT NULL,
	sortorder	int4 DEFAULT '0' NOT NULL,
	dgroup		int4 DEFAULT '0' NOT NULL,
	width		int4,
	height		int4,
	pagesize	int4,
	viewgrp		varchar(10),
	PRIMARY KEY	(id)
);

CREATE INDEX twig_notes_prefs_username_index ON twig_notes_prefs (username);

CREATE SEQUENCE twschedid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twschedid');

CREATE TABLE twig_schedule (
   id	        int4 DEFAULT nextval ( 'twschedid') ,
   username	VARCHAR(128),
   thedate	TIMESTAMP,
   groupid	INT NOT NULL,
   itemstart	TIMESTAMP,
   itemstop	TIMESTAMP,
   item		TEXT,
   description	TEXT,
   notime	INT,
   recurtype	INT,
   recurid	INT,
   PRIMARY KEY	(id)
);

CREATE INDEX twig_schedule_username_index ON twig_schedule (username);
CREATE INDEX twig_schedule_thedate_index ON twig_schedule (thedate);
CREATE INDEX twig_schedule_groupid_index ON twig_schedule (groupid);
CREATE INDEX twig_schedule_start_index ON twig_schedule (itemstart);
CREATE INDEX twig_schedule_finish_index ON twig_schedule (itemstop);

CREATE SEQUENCE twsrecid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twsrecid');

CREATE TABLE twig_schedule_recurring (
	id		int4 DEFAULT nextval ( 'twsrecid'),
	schid		int4,
	recurtype	int4 not null,
	schedule	int4,
	cycle		int4,
	"position"	int4,
	days		varchar(13),
	endtype		int4 not null,
	"end"		DATETIME not null,
	PRIMARY KEY	(id)
	);

CREATE INDEX twig_schedule_recurring_schid_index ON twig_schedule_recurring (schid);
CREATE INDEX twig_schedule_recurring_recurtype_index ON twig_schedule_recurring (recurtype);
CREATE INDEX twig_schedule_recurring_endtype_index ON twig_schedule_recurring (endtype);
CREATE INDEX twig_schedule_recurring_end_index ON twig_schedule_recurring ("end");

CREATE SEQUENCE twsprf start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twsprf');

CREATE TABLE twig_schedule_prefs (
   id 		int4 DEFAULT nextval ( 'twsprf' ),
   username 	varchar(128),
   dgroup 	int4 DEFAULT 0 NOT NULL,
   viewgrp 	varchar(10) DEFAULT 0 NOT NULL,
   PRIMARY KEY	(id)
);

CREATE INDEX twig_schedule_prefs_username_index ON twig_schedule_prefs (username);

CREATE TABLE twig_sclhsqltable (
   	LID 		varchar(128) DEFAULT 0,
   	pwdata 		varchar(128) DEFAULT 0,
   	username 	varchar(128) NOT NULL,
   	expire 		int DEFAULT 0,
	PRIMARY KEY	(LID)
);

CREATE TABLE twig_session (
   id 		varchar(40) NOT NULL,
   username 	varchar(128) NOT NULL,
   sdata	text NOT NULL,
   sid 		int4 DEFAULT 0 NOT NULL
);

CREATE INDEX twig_session_id_index ON twig_session (id);
CREATE INDEX twig_session_username_index ON twig_session (username);
CREATE INDEX twig_session_sid_index ON twig_session (sid);

CREATE SEQUENCE twtdid start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ( 'twtdid');

CREATE TABLE twig_todo (
   id	        int4 DEFAULT nextval ( 'twtdid') ,	
   username	VARCHAR(128),
   thedate	TIMESTAMP,
   groupid	INT,
   item		TEXT,
   description	TEXT,
   priority	INT,
   completed	INT,
   duedate	TIMESTAMP,
   duetype	INT,
   PRIMARY KEY	(id)
);

CREATE INDEX twig_todo_username_index ON twig_todo (username);

CREATE TABLE twig_todo_lists_entries (
   listid	int4 DEFAULT 0 NOT NULL,
   type		int4 DEFAULT 0 NOT NULL,
   item		int4 DEFAULT 0 NOT NULL,
   subitem	int4
);

CREATE INDEX twig_todo_lists_entries_listid_index ON twig_todo_lists_entries (listid);
CREATE INDEX twig_todo_lists_entries_item_index ON twig_todo_lists_entries (item);

CREATE SEQUENCE twtprf start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('twtprf');

CREATE TABLE twig_todo_prefs (
   id 		int4 DEFAULT nextval ( 'twtprf' ),
   username 	varchar(128),
   sortby 	varchar(10) NOT NULL,
   sortorder 	int4 DEFAULT '0' NOT NULL,
   dgroup 	int4 DEFAULT '0' NOT NULL,
   priority	int4 DEFAULT '0' NOT NULL,
   pagesize	int4 DEFAULT '0' NOT NULL,
   viewgrp	varchar(10) DEFAULT '0' NOT NULL,
   duetype	int4 DEFAULT '0' NOT NULL,
   PRIMARY KEY  (id)
);

CREATE INDEX twig_todo_prefs_username_index ON twig_todo_prefs (username);

GRANT ALL ON twig_groups TO nobody;
GRANT ALL ON twig_members TO nobody;
GRANT ALL ON twig_mail_prefs TO nobody;
GRANT ALL ON twig_schedule TO nobody;
GRANT ALL ON twig_todo TO nobody;
GRANT ALL ON twig_bookmarks TO nobody;
GRANT ALL ON twig_contacts TO nobody;
GRANT ALL ON twig_folders TO nobody;
GRANT ALL ON twig_news_prefs TO nobody;
GRANT ALL ON twig_accounts TO nobody;
GRANT ALL ON twig_main_prefs TO nobody;
GRANT ALL ON twig_global_prefs TO nobody;
GRANT ALL ON twig_contacts_prefs TO nobody;
GRANT ALL ON twig_contacts_lists_entries TO nobody;
GRANT ALL ON twig_bookmarks_prefs TO nobody;
GRANT ALL ON twig_todo_prefs TO nobody;
GRANT ALL ON twig_schedule_prefs TO nobody;
GRANT ALL ON twig_schedule_recurring TO nobody;
GRANT ALL ON twig_session TO nobody;
GRANT ALL ON twig_context TO nobody;
GRANT ALL ON twig_acls TO nobody;
GRANT ALL ON twig_acl_groups TO nobody;
GRANT ALL ON twig_notes TO nobody;
GRANT ALL ON twig_notes_prefs TO nobody;
GRANT ALL ON twig_lhsqltable TO nobody;
GRANT ALL ON twig_meetings TO nobody;
GRANT ALL ON twig_meetings_prefs TO nobody;
GRANT ALL ON twig_meetings_registration TO nobody;
GRANT ALL ON twig_sclhsqltable TO nobody;

GRANT UPDATE ON twaccid TO nobody;
GRANT UPDATE ON twasid TO nobody;
GRANT UPDATE ON twanid TO nobody;
GRANT UPDATE ON twbmid TO nobody;
GRANT UPDATE ON twbmprf TO nobody;
GRANT UPDATE ON twconid TO nobody;
GRANT UPDATE ON twcprf TO nobody;
GRANT UPDATE ON twfoldid TO nobody;
GRANT UPDATE ON twgpid TO nobody;
GRANT UPDATE ON groupid TO nobody;
GRANT UPDATE ON twprefsid TO nobody;
GRANT UPDATE ON twmpid TO nobody;
GRANT UPDATE ON twmeetid TO nobody;
GRANT UPDATE ON twmeetpid TO nobody;
GRANT UPDATE ON twmeetrid TO nobody;
GRANT UPDATE ON twmeetiid TO nobody;
GRANT UPDATE ON memberid TO nobody;
GRANT UPDATE ON twnpid TO nobody;
GRANT UPDATE ON twnoteid TO nobody;
GRANT UPDATE ON twnotepid TO nobody;
GRANT UPDATE ON twschedid TO nobody;
GRANT UPDATE ON twsrecid TO nobody;
GRANT UPDATE ON twsprf TO nobody;
GRANT UPDATE ON twtdid TO nobody;
GRANT UPDATE ON twprf TO nobody;