CREATE TABLE twig_accounts (
   	id 		NUMERIC IDENTITY PRIMARY KEY,
   	username 	varchar(128) NOT NULL,
   	password 	varchar(128),
	SID		numeric,
   	onnow		numeric,
   	expire		numeric,
   	laston		numeric
);

CREATE INDEX twig_accounts_id_index ON twig_accounts (id, username);

CREATE TABLE twig_acls (
   	SID 		int DEFAULT '0' NOT NULL PRIMARY KEY,
   	ident	 	varchar(128) NOT NULL,
   	alevel 		int DEFAULT '0' NOT NULL
);

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

CREATE TABLE twig_acl_groups (
   	SID 		numeric DEFAULT '0' NOT NULL,
   	groupname 	varchar(64) NOT NULL PRIMARY KEY,
   	username 	varchar(128) NOT NULL,
);

CREATE INDEX twig_acl_groups_username_index ON twig_acl_groups (username);

CREATE TABLE twig_announce (
   	id 		NUMERIC IDENTITY PRIMARY KEY,
   	thedate 	TIMESTAMP,
   	username 	varchar(128) NOT NULL,
	email		varchar(128),
   	subject 	varchar(128) NOT NULL,
   	announce 	TEXT NOT NULL
);

CREATE INDEX twig_announce_id_index ON twig_announce (id);

CREATE TABLE twig_bookmarks (
 	id		NUMERIC IDENTITY PRIMARY KEY,
 	username	VARCHAR(128),
 	thedate		TIMESTAMP,
 	groupid		INT NOT NULL,
 	item		varchar(100),
 	description	varchar(100),
 	url		varchar(100)
);

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

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

CREATE INDEX twig_bookmarks_prefs_username_index ON twig_bookmarks_prefs (username);

CREATE TABLE twig_contacts (
 	id		NUMERIC IDENTITY PRIMARY KEY,
 	username	VARCHAR(128),
 	thedate		TIMESTAMP,
 	groupid		INT NOT NULL,
 	item		TEXT,
 	description	TEXT,
 	url		TEXT,
 	firstname	VARCHAR(100),
 	lastname	VARCHAR(100),
 	email		VARCHAR(100),
 	company		VARCHAR(100),
 	home_phone	VARCHAR(30),
 	work_phone	VARCHAR(30),
 	mobile		VARCHAR(30),
 	fax		VARCHAR(30),
 	address1	VARCHAR(200),
 	address2	VARCHAR(200),
 	city		VARCHAR(100),
 	state		VARCHAR(10),
 	zipcode		VARCHAR(10),
 	country		VARCHAR(100),
 	email2 		VARCHAR(100),
 	email3 		VARCHAR(100),
 	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		int,
	userpers	int
);

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

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

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 		NUMERIC IDENTITY PRIMARY KEY,
   	username 	varchar(128),
   	sortby 		varchar(10) NOT NULL,
   	sortorder 	tinyint DEFAULT '0' NOT NULL,
   	dgroup 		int DEFAULT '0' NOT NULL,
	pagesize	int NOT NULL,
   	viewgrp		varchar(10) DEFAULT '0' NOT NULL
);

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 		numeric DEFAULT '0' NOT NULL,
);

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

CREATE TABLE twig_folders (
   	id 		NUMERIC IDENTITY PRIMARY KEY,
   	owner 		varchar(128) NOT NULL,
   	server 		varchar(255) NOT NULL,
   	port 		int DEFAULT '0' NOT NULL,
   	groupname 	varchar(255) NOT NULL,
   	username 	varchar(128),
   	password 	varchar(128),
   	servertype 	varchar(10),
);

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

CREATE TABLE twig_global_prefs (
   	id 		NUMERIC IDENTITY PRIMARY KEY,
   	username 	varchar(128),
   	mstyle1 	tinyint DEFAULT '0' NOT NULL,
   	mstyle2 	tinyint DEFAULT '3' NOT NULL,
   	tstyle1 	tinyint DEFAULT '5' NOT NULL,
   	language 	varchar(20),
   	mailfoot	tinyint,
   	prevnext	varchar(20),
	timeformat	NUMERIC,
	stylesheet	varchar(32),
);

CREATE INDEX twig_global_prefs_username_index ON twig_global_prefs (username);

CREATE TABLE twig_groups (
	id		INT IDENTITY PRIMARY KEY,
	owner		varchar(128),
	groupname	varchar(100),
	type		varchar(20)
);

CREATE INDEX twig_groups_owner_index ON twig_groups (owner);

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

CREATE TABLE twig_mail_prefs (
	id		INT IDENTITY PRIMARY KEY,
	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),
	cgroup		varchar(10)
);

CREATE INDEX twig_mail_prefs_username_index ON twig_mail_prefs (username);

CREATE TABLE twig_main_prefs (
   	id 		NUMERIC IDENTITY PRIMARY KEY,
   	username 	varchar(128),
   	features 	varchar(128),
	maxmail		INT,
	maxmeetings	INT,
	maxschedule	INT,
	maxtodo		INT,
);

CREATE INDEX twig_main_prefs_username_index ON twig_main_prefs (username);

CREATE TABLE twig_meetings (
  	id 		NUMERIC IDENTITY PRIMARY KEY,
  	groupid 	INT NOT NULL,
  	username 	varchar(128),
  	secretary	varchar(128),
  	responsible	varchar(128),
  	thedate 	TIMESTAMP,
  	itemstart 	DATETIME,
  	itemstop 	DATETIME,
  	itemdone 	DATETIME,
  	item 		text,
  	description 	text,
  	minutes		text,
  	conclusion 	text,
  	actionitems 	text,
  	location	INT NOT NULL,
  	userlimit 	INT NOT NULL,
  	closed 		INT NOT NULL,
  	deleted 	INT NOT NULL,
  	private 	INT NOT NULL
);

CREATE TABLE twig_meetings_prefs (
  	id 		NUMERIC IDENTITY PRIMARY KEY,
  	username 	varchar(128),
  	dgroup 		INT NOT NULL,
  	mainview	INT NOT NULL,
  	pagesize	INT NOT NULL,
   	viewgrp		varchar(10) DEFAULT '0' NOT NULL
);

CREATE TABLE twig_meetings_registration (
  	id 		NUMERIC IDENTITY PRIMARY KEY,
  	username 	varchar(128),
  	eventid 	INT NOT NULL,
  	thedate		TIMESTAMP,
  	rsvp 		INT NOT NULL
);

CREATE TABLE twig_meetings_invitations (
  	id 		NUMERIC IDENTITY PRIMARY KEY,
  	username	varchar(128),
  	eventid 	INT NOT NULL,
  	thedate 	TIMESTAMP,
  	priority 	INT NOT NULL
);

CREATE TABLE twig_members (
	id		INT IDENTITY PRIMARY KEY,
	groupid		INT NOT NULL,
	username	varchar(128),
	writeperms	INT
);

CREATE TABLE twig_news_prefs (
   	id 		NUMERIC IDENTITY PRIMARY KEY,
   	username	varchar(128) NOT NULL,
   	replyto 	text NOT NULL,
   	pagesize 	int DEFAULT '0' NOT NULL,
   	newwindow 	int DEFAULT '1' NOT NULL,
	linewrap	text NOT NULL,
	realname	text NOT NULL,
	signature	text NOT NULL,
   	inline 		int
);

CREATE INDEX twig_news_prefs_username_index ON twig_news_prefs (username);

CREATE TABLE twig_notes (
	id		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
        username        VARCHAR(128) NOT NULL,
        thedate         TIMESTAMP,
        groupid         INT NOT NULL,
        item            TEXT,
        description     TEXT,
        attachname      TEXT,
	attachmime	TEXT,
        attachment      IMAGE
);

CREATE INDEX twig_notes_username_index ON twig_notes (username);

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

CREATE INDEX twig_notes_prefs_username_index ON twig_notes_prefs (username);

CREATE TABLE twig_schedule (
 	id		NUMERIC IDENTITY PRIMARY KEY,
 	username	VARCHAR(128),
 	thedate		TIMESTAMP,
 	groupid		INT NOT NULL,
 	itemstart	DATETIME,
 	itemstop	DATETIME,
 	item		TEXT,
 	description	TEXT,
 	notime		INT,
	recurtype	INT,
	recurid		INT
	remind_time	TIMESTAMP,
	remind_qty	INT,
	remind_units	VARCHAR(10),
	remind_address	VARCHAR(100),
);

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 TABLE twig_schedule_recurring (
	id		NUMERIC IDENTITY PRIMARY KEY,
	schid		INT,
	recurtype	INT not null,
	schedule	INT,
	cycle		INT,
	position	INT,
	days		VARCHAR (13),
	endtype		INT not null,
	end		DATETIME not null
	)

CREATE INDEX twig_schedule_recurring_id_index ON twig_schedule_recurring (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 TABLE twig_schedule_prefs (
   	id 		NUMERIC IDENTITY PRIMARY KEY,
   	username 	varchar(128),
   	dgroup 		int DEFAULT '0' NOT NULL,
   	viewgrp		varchar(10) DEFAULT '0' NOT NULL
);

CREATE INDEX twig_schedule_prefs_username_index ON twig_schedule_prefs (username);

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

CREATE TABLE twig_session (
   	id 		varchar(40) NOT NULL,
   	username 	varchar(128) NOT NULL,
   	sdata 		text NOT NULL,
   	sid 		numeric 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 TABLE twig_todo (
 	id		NUMERIC IDENTITY PRIMARY KEY,
 	username	varchar(128),
 	thedate		TIMESTAMP,
 	groupid		INT NOT NULL,
 	item		TEXT,
 	description	TEXT,
 	priority	INT,
 	completed	INT,
	duedate		DATETIME,
	duetype		INT
);

CREATE INDEX twig_todo_username_index ON twig_todo (username);

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

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 TABLE twig_todo_prefs (
   	id 		NUMERIC IDENTITY PRIMARY KEY,
   	username 	varchar(128),
   	sortby 		varchar(10) NOT NULL,
   	sortorder 	int DEFAULT '0' NOT NULL,
   	dgroup 		int DEFAULT '0' NOT NULL,
   	priority 	int DEFAULT '0' NOT NULL,
   	pagesize 	int DEFAULT '0' NOT NULL,
   	viewgrp		varchar(10) DEFAULT '0' NOT NULL
);

CREATE INDEX twig_todo_prefs_username_index ON twig_todo_prefs (username);