-- table definitions
CREATE TABLE active_sessions ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
user_id INTEGER,
session_id CHAR(255),
lastused DATETIME,
ip CHAR(30)
);

CREATE TABLE document_fields ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(255) NOT NULL,
data_type CHAR(100) NOT NULL,
is_generic BIT,
has_lookup BIT
);

CREATE TABLE document_fields_link ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
document_id INTEGER NOT NULL,
document_field_id INTEGER NOT NULL,
value CHAR(255) NOT NULL
);

CREATE TABLE document_fields_lookup (
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
document_field_id INTEGER NOT NULL,
name CHAR(255) NOT NULL
);

CREATE TABLE document_transaction_types_lookup ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(100) NOT NULL
);

CREATE TABLE document_transactions ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
document_id INTEGER NOT NULL,
version CHAR(50),
user_id INTEGER NOT NULL,
datetime DATETIME NOT NULL,
ip CHAR(30),
filename CHAR(255) NOT NULL,
comment CHAR(255) NOT NULL,
transaction_id INTEGER
);

CREATE TABLE document_type_fields_link ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
document_type_id INTEGER NOT NULL,
field_id INTEGER NOT NULL,
is_mandatory BIT NOT NULL
);

CREATE TABLE document_types_lookup ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(100)
);

CREATE TABLE document_words_link ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
word_id INTEGER NOT NULL,
document_id INTEGER NOT NULL
);

CREATE TABLE documents ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
document_type_id INTEGER NOT NULL,
name CHAR(80) NOT NULL,
filename CHAR(50) NOT NULL,
size BIGINT NOT NULL,
creator_id INTEGER NOT NULL,
modified DATE NOT NULL,
description CHAR(200) NOT NULL,
security INTEGER NOT NULL,
mime_id INTEGER NOT NULL,
folder_id INTEGER NOT NULL,
major_version INTEGER NOT NULL,
minor_version INTEGER NOT NULL,
is_checked_out BIT NOT NULL
);

CREATE TABLE document_subscriptions ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
user_id INTEGER NOT NULL,
document_id INTEGER NOT NULL,
is_alerted BIT
);

CREATE TABLE folders ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(255),
description CHAR(255),
parent_id INTEGER,
creator_id INTEGER,
document_type_id INTEGER NOT NULL,
unit_id INTEGER,
is_public BIT NOT NULL
);

CREATE TABLE folder_subscriptions ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
user_id INTEGER NOT NULL,
folder_id INTEGER NOT NULL,
is_alerted BIT
);

CREATE TABLE folders_users_roles_link ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
group_folder_approval_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
document_id INTEGER NOT NULL,
datetime DATETIME,
done BIT,
active BIT
);

CREATE TABLE groups_folders_approval_link ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
folder_id INTEGER NOT NULL,
group_id INTEGER NOT NULL,
precedence INTEGER NOT NULL,
role_id INTEGER NOT NULL
);

CREATE TABLE groups_folders_link (
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
group_id INTEGER NOT NULL,
folder_id INTEGER NOT NULL,
can_read BIT NOT NULL,
can_write BIT NOT NULL
);

CREATE TABLE groups_lookup ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(100) NOT NULL,
is_sys_admin BIT NOT NULL,
is_unit_admin BIT NOT NULL
);

CREATE TABLE groups_units_link ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
group_id INTEGER NOT NULL,
unit_id INTEGER NOT NULL
);

CREATE TABLE links ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(100) NOT NULL,
url CHAR(100) NOT NULL,
rank INTEGER NOT NULL
);

CREATE TABLE language_lookup ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(100) NOT NULL
);

CREATE TABLE mime_types ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
filetypes CHAR(100) NOT NULL,
mimetypes CHAR(100) NOT NULL,
icon_path CHAR(255) 
);

CREATE TABLE organisations_lookup ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(100) NOT NULL
);

CREATE TABLE roles ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(255) NOT NULL,
active BIT NOT NULL,
can_read BIT NOT NULL,
can_write BIT NOT NULL
);

-- sitemap tables
CREATE TABLE site_sections_lookup (
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(255)
);

CREATE TABLE site_access_lookup (
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(255)
);

CREATE TABLE sitemap (
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
action CHAR(50),
page CHAR(255),
section_id INTEGER,
access_id INTEGER,
link_text CHAR(255),
is_default BIT,
is_enabled BIT DEFAULT 1
);

CREATE TABLE system_settings ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(255) NOT NULL,
value CHAR(255) NOT NULL
);

CREATE TABLE sys_deleted ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
table_name CHAR(255) NOT NULL,
datetime DATETIME NOT NULL
);

CREATE TABLE units_lookup ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(100) NOT NULL
);

CREATE TABLE units_organisations_link ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
unit_id INTEGER NOT NULL,
organisation_id INTEGER NOT NULL
);

CREATE TABLE users (
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
username CHAR(255) NOT NULL,
name CHAR(255) NOT NULL,
password CHAR(255) NOT NULL,
quota_max INTEGER NOT NULL,
quota_current INTEGER NOT NULL,
email CHAR(255),
mobile CHAR(255),
email_notification BIT NOT NULL,
sms_notification BIT NOT NULL,
ldap_dn CHAR(255),
max_sessions INTEGER,
language_id INTEGER
) 
;

CREATE TABLE users_groups_link ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
user_id INTEGER NOT NULL,
group_id INTEGER NOT NULL
) 
;

CREATE TABLE web_documents ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
document_id INTEGER NOT NULL,
web_site_id INTEGER NOT NULL,
unit_id INTEGER NOT NULL,
status_id INTEGER NOT NULL,
datetime DATETIME NOT NULL
);

CREATE TABLE web_documents_status_lookup ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
name CHAR(50) NOT NULL
);

CREATE TABLE web_sites ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
web_site_name CHAR(100) NOT NULL,
web_site_url CHAR(50) NOT NULL,
web_master_id INTEGER NOT NULL
);

CREATE TABLE words_lookup ( 
id INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
word CHAR(255) NOT NULL
);

-- pk constraints
ALTER TABLE active_sessions
ADD CONSTRAINT PK_active_sessions
PRIMARY KEY (id)
;

ALTER TABLE document_fields
ADD CONSTRAINT PK_document_fields
PRIMARY KEY (id)
;

ALTER TABLE document_fields_link
ADD CONSTRAINT PK_document_fields_link
PRIMARY KEY (id)
;

ALTER TABLE document_fields_lookup
ADD CONSTRAINT PK_document_fields_lookup
PRIMARY KEY (id)
;

ALTER TABLE document_transaction_types_lookup
ADD CONSTRAINT PK_document_transaction_types_lookup
PRIMARY KEY (id)
;

ALTER TABLE document_transactions
ADD CONSTRAINT PK_document_transactions
PRIMARY KEY (id)
;

ALTER TABLE document_type_fields_link
ADD CONSTRAINT PK_document_type_fields_link
PRIMARY KEY (id)
;

ALTER TABLE document_types_lookup
ADD CONSTRAINT PK_document_types_lookup
PRIMARY KEY (id)
;

ALTER TABLE document_words_link
ADD CONSTRAINT PK_document_words_link
PRIMARY KEY (id)
;

ALTER TABLE documents
ADD CONSTRAINT PK_documents
PRIMARY KEY (id)
;

ALTER TABLE document_subscriptions
ADD CONSTRAINT PK_document_subscriptions
PRIMARY KEY (id)
;

ALTER TABLE folders
ADD CONSTRAINT PK_folders
PRIMARY KEY (id)
;

ALTER TABLE folder_subscriptions
ADD CONSTRAINT PK_folder_subscriptions
PRIMARY KEY (id)
;

ALTER TABLE folders_users_roles_link
ADD CONSTRAINT PK_folders_users_roles_link
PRIMARY KEY (id)
;

ALTER TABLE groups_folders_approval_link
ADD CONSTRAINT PK_groups_folders_approval_link
PRIMARY KEY (id)
;

ALTER TABLE groups_lookup
ADD CONSTRAINT PK_groups_lookup
PRIMARY KEY (id)
;

ALTER TABLE language_lookup
ADD CONSTRAINT PK_language_lookup
PRIMARY KEY (id)
;

ALTER TABLE groups_units_link
ADD CONSTRAINT PK_groups_units_link
PRIMARY KEY (id)
;

ALTER TABLE links
ADD CONSTRAINT PK_links
PRIMARY KEY (id)
;

ALTER TABLE mime_types
ADD CONSTRAINT PK_mimes_types
PRIMARY KEY (id)
;

ALTER TABLE groups_folders_link
ADD CONSTRAINT PK_groups_folders_link
PRIMARY KEY (id)
;

ALTER TABLE organisations_lookup
ADD CONSTRAINT PK_organisations_lookup
PRIMARY KEY (id)
;

ALTER TABLE roles
ADD CONSTRAINT PK_roles
PRIMARY KEY (id)
;

ALTER TABLE site_sections_lookup
ADD CONSTRAINT PK_site_sections_lookup
PRIMARY KEY (id)
;

ALTER TABLE site_access_lookup
ADD CONSTRAINT PK_site_access_lookup
PRIMARY KEY (id)
;

ALTER TABLE sitemap
ADD CONSTRAINT PK_sitemap
PRIMARY KEY (id)
;

ALTER TABLE system_settings
ADD CONSTRAINT PK_system_settings
PRIMARY KEY (id)
;

ALTER TABLE sys_deleted
ADD CONSTRAINT PK_sys_deleted
PRIMARY KEY (id)
;

ALTER TABLE units_lookup
ADD CONSTRAINT PK_units_lookup
PRIMARY KEY (id)
;

ALTER TABLE units_organisations_link
ADD CONSTRAINT PK_units_organisations_link
PRIMARY KEY (id)
;

ALTER TABLE users
ADD CONSTRAINT PK_users
PRIMARY KEY (id)
;

ALTER TABLE users_groups_link
ADD CONSTRAINT PK_users_groups_link
PRIMARY KEY (id)
;

ALTER TABLE web_documents
ADD CONSTRAINT PK_web_documents
PRIMARY KEY (id)
;

ALTER TABLE web_documents_status_lookup
ADD CONSTRAINT PK_web_documents_status
PRIMARY KEY (id);

ALTER TABLE web_sites
ADD CONSTRAINT PK_web_sites
PRIMARY KEY (id);

ALTER TABLE words_lookup
ADD CONSTRAINT PK_word_list
PRIMARY KEY (id);

-- mime types
-- TODO: add icon paths to inserts
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('ai', 'application/postscript');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('aif', 'audio/x-aiff');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('aifc', 'audio/x-aiff');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('aiff', 'audio/x-aiff');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('asc', 'text/plain');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('au', 'audio/basic');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('avi', 'video/x-msvideo');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('bcpio', 'application/x-bcpio');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('bin', 'application/octet-stream');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('bmp', 'image/bmp');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('cdf', 'application/x-netcdf');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('class', 'application/octet-stream');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('cpio', 'application/x-cpio');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('cpt', 'application/mac-compactpro');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('csh', 'application/x-csh');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('css', 'text/css');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('dcr', 'application/x-director');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('dir', 'application/x-director');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('dms', 'application/octet-stream');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('doc', 'application/msword');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('dvi', 'application/x-dvi');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('dxr', 'application/x-director');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('eps', 'application/postscript');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('etx', 'text/x-setext');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('exe', 'application/octet-stream');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('ez', 'application/andrew-inset');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('gif', 'image/gif');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('gtar', 'application/x-gtar');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('hdf', 'application/x-hdf');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('hqx', 'application/mac-binhex40');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('htm', 'text/html');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('html', 'text/html');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('ice', 'x-conference/x-cooltalk');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('ief', 'image/ief');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('iges', 'model/iges');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('igs', 'model/iges');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('jpe', 'image/jpeg');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('jpeg', 'image/jpeg');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('jpg', 'image/jpeg');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('js', 'application/x-javascript');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('kar', 'audio/midi');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('latex', 'application/x-latex');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('lha', 'application/octet-stream');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('lzh', 'application/octet-stream');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('man', 'application/x-troff-man');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('me', 'application/x-troff-me');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('mesh', 'model/mesh');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('mid', 'audio/midi');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('midi', 'audio/midi');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('mif', 'application/vnd.mif');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('mov', 'video/quicktime');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('movie', 'video/x-sgi-movie');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('mp2', 'audio/mpeg');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('mp3', 'audio/mpeg');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('mpe', 'video/mpeg');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('mpeg', 'video/mpeg');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('mpg', 'video/mpeg');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('mpga', 'audio/mpeg');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('ms', 'application/x-troff-ms');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('msh', 'model/mesh');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('nc', 'application/x-netcdf');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('oda', 'application/oda');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('pbm', 'image/x-portable-bitmap');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('pdb', 'chemical/x-pdb');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('pdf', 'application/pdf');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('pgm', 'image/x-portable-graymap');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('pgn', 'application/x-chess-pgn');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('png', 'image/png');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('pnm', 'image/x-portable-anymap');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('ppm', 'image/x-portable-pixmap');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('ppt', 'application/vnd.ms-powerpoint');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('ps', 'application/postscript');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('qt', 'video/quicktime');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('ra', 'audio/x-realaudio');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('ram', 'audio/x-pn-realaudio');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('ras', 'image/x-cmu-raster');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('rgb', 'image/x-rgb');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('rm', 'audio/x-pn-realaudio');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('roff', 'application/x-troff');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('rpm', 'audio/x-pn-realaudio-plugin');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('rtf', 'text/rtf');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('rtx', 'text/richtext');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('sgm', 'text/sgml');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('sgml', 'text/sgml');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('sh', 'application/x-sh');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('shar', 'application/x-shar');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('silo', 'model/mesh');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('sit', 'application/x-stuffit');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('skd', 'application/x-koan');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('skm', 'application/x-koan');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('skp', 'application/x-koan');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('skt', 'application/x-koan');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('smi', 'application/smil');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('smil', 'application/smil');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('snd', 'audio/basic');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('spl', 'application/x-futuresplash');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('src', 'application/x-wais-source');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('sv4cpio', 'application/x-sv4cpio');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('sv4crc', 'application/x-sv4crc');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('swf', 'application/x-shockwave-flash');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('t', 'application/x-troff');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('tar', 'application/x-tar');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('tcl', 'application/x-tcl');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('tex', 'application/x-tex');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('texi', 'application/x-texinfo');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('texinfo', 'application/x-texinfo');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('tif', 'image/tiff');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('tiff', 'image/tiff');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('tr', 'application/x-troff');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('tsv', 'text/tab-separated-values');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('txt', 'text/plain');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('ustar', 'application/x-ustar');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('vcd', 'application/x-cdlink');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('vrml', 'model/vrml');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('wav', 'audio/x-wav');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('wrl', 'model/vrml');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('xbm', 'image/x-xbitmap');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('xls', 'application/vnd.ms-excel');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('xml', 'text/xml');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('xpm', 'image/x-xpixmap');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('xwd', 'image/x-xwindowdump');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('xyz', 'chemical/x-pdb');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('zip', 'application/zip');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('gz', 'application/x-gzip');
INSERT INTO mime_types (filetypes, mimetypes) VALUES ('tgz', 'application/x-gzip');

-- supported languages (not really ;)
INSERT INTO language_lookup (name) VALUES ("English");
INSERT INTO language_lookup (name) VALUES ("Chinese");
INSERT INTO language_lookup (name) VALUES ("Danish");
INSERT INTO language_lookup (name) VALUES ("Deutsch");
INSERT INTO language_lookup (name) VALUES ("Dutch");
INSERT INTO language_lookup (name) VALUES ("Francais");
INSERT INTO language_lookup (name) VALUES ("Hungarian");
INSERT INTO language_lookup (name) VALUES ("Italian");
INSERT INTO language_lookup (name) VALUES ("Norwegian");
INSERT INTO language_lookup (name) VALUES ("Portuguese");
INSERT INTO language_lookup (name) VALUES ("Spanish");

---- system settings
-- ldap
INSERT INTO system_settings (name, value) values ("ldapServer", "192.168.1.9");
INSERT INTO system_settings (name, value) values ("ldapRootDn", "o=Medical Research Council");
-- email settings
INSERT INTO system_settings (name, value) values ("emailServer", "mail.jamwarehouse.com");
INSERT INTO system_settings (name, value) values ("emailFrom", "owl@jamwarehouse.com");
INSERT INTO system_settings (name, value) values ("emailFromName", "MRC Document Management System");
-- directories
INSERT INTO system_settings (name, value) values ("filesystemRoot", "/usr/local/www/owl/dms");
INSERT INTO system_settings (name, value) values ("documentRoot", "/usr/local/www/owl/dms/Documents");
INSERT INTO system_settings (name, value) values ("languageDirectory", "/usr/local/www/owl/dms/locale");
INSERT INTO system_settings (name, value) values ("uiDirectory", "/usr/local/www/owl/dms/presentation/lookAndFeel/knowledgeTree");
-- urls
INSERT INTO system_settings (name, value) values ("rootUrl", "/dms");
INSERT INTO system_settings (name, value) values ("graphicsUrl", "/dms/graphics");
INSERT INTO system_settings (name, value) values ("uiUrl", "/dms/presentation/lookAndFeel/knowledgeTree");
-- general settings
INSERT INTO system_settings (name, value) values ("useFs", "true");
INSERT INTO system_settings (name, value) values ("defaultLanguage", "NewEnglish");
--INSERT INTO system_settings (name, value) values ("notificationLink", "http://$_SERVER[SERVER_NAME]$default->owl_root_url/");
INSERT INTO system_settings (name, value) values ("sessionTimeout", "1200");


-- document statuses
INSERT INTO web_documents_status_lookup (name) VALUES ("Pending");
INSERT INTO web_documents_status_lookup (name) VALUES ("Published");
INSERT INTO web_documents_status_lookup (name) VALUES ("Not Published");

-- document transaction types
INSERT INTO document_transaction_types_lookup (name) VALUES ("Create");
INSERT INTO document_transaction_types_lookup (name) VALUES ("Update");
INSERT INTO document_transaction_types_lookup (name) VALUES ("Delete");
INSERT INTO document_transaction_types_lookup (name) VALUES ("Rename");
INSERT INTO document_transaction_types_lookup (name) VALUES ("Move");
INSERT INTO document_transaction_types_lookup (name) VALUES ("Download");

-- roles
INSERT INTO roles (name, active, can_read, can_write) VALUES ('Editor', 1, 1, 1);
INSERT INTO roles (name, active, can_read, can_write) VALUES ('Spell Checker', 1, 1, 0);
INSERT INTO roles (name, active, can_read, can_write) VALUES ('Web Publisher', 1, 1, 0);

-- mrc organisation
INSERT INTO organisations_lookup (name) VALUES ("Medical Research Council");

-- setup mrc units
INSERT INTO units_lookup (name) VALUES ("ADARG"); -- id=1
INSERT INTO units_lookup (name) VALUES ("AfroAids"); -- id=2
INSERT INTO units_lookup (name) VALUES ("Diabetes"); -- id=3
INSERT INTO units_lookup (name) VALUES ("Burden of Disease"); -- id=4

INSERT INTO units_organisations_link (unit_id, organisation_id) VALUES (1, 1); -- id=1
INSERT INTO units_organisations_link (unit_id, organisation_id) VALUES (2, 1); -- id=2
INSERT INTO units_organisations_link (unit_id, organisation_id) VALUES (3, 1); -- id=3
INSERT INTO units_organisations_link (unit_id, organisation_id) VALUES (4, 1); -- id=4

-- setup groups
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("System Administrators", 1, 0); -- id=1
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("ADARG Unit Administrators", 0, 1); -- id=2
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("AfroAids Unit Administrators", 0, 1); -- id=3
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Diabetes Unit Administrators", 0, 1); -- id=4
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Burden of Disease Unit Administrators", 0, 1); -- id=5
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Anonymous", 0, 0); -- id=6
-- adarg unit groups
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Spell Checkers", 0, 0); -- id=7
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Editors", 0, 0); -- id=8
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Finance", 0, 0); -- id=9
-- afroaids unit groups
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Spell Checkers", 0, 0); -- id=10
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Editors", 0, 0); -- id=11
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Finance", 0, 0); -- id=12
-- diabetes unit groups
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Spell Checkers", 0, 0); -- id=13
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Editors", 0, 0); -- id=14
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Finance", 0, 0); -- id=15
-- disease unit groups
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Spell Checkers", 0, 0); -- id=16
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Editors", 0, 0); -- id=17
INSERT INTO groups_lookup (name, is_sys_admin, is_unit_admin) VALUES ("Finance", 0, 0); -- id=18

------ map groups to units
---- adarg
-- administrators
INSERT INTO groups_units_link (group_id, unit_id) VALUES (2, 1);
-- other groups
INSERT INTO groups_units_link (group_id, unit_id) VALUES (7, 1);
INSERT INTO groups_units_link (group_id, unit_id) VALUES (8, 1);
INSERT INTO groups_units_link (group_id, unit_id) VALUES (9, 1);

---- afroaids
-- administrators
INSERT INTO groups_units_link (group_id, unit_id) VALUES (3, 2);
-- other groups
INSERT INTO groups_units_link (group_id, unit_id) VALUES (10, 2);
INSERT INTO groups_units_link (group_id, unit_id) VALUES (11, 2);
INSERT INTO groups_units_link (group_id, unit_id) VALUES (12, 2);

---- diabetes
-- administrators
INSERT INTO groups_units_link (group_id, unit_id) VALUES (4, 3);
-- other groups
INSERT INTO groups_units_link (group_id, unit_id) VALUES (13, 3);
INSERT INTO groups_units_link (group_id, unit_id) VALUES (14, 3);
INSERT INTO groups_units_link (group_id, unit_id) VALUES (15, 3);

---- disease
-- administrators
INSERT INTO groups_units_link (group_id, unit_id) VALUES (5, 4);
-- other groups
INSERT INTO groups_units_link (group_id, unit_id) VALUES (16, 4);
INSERT INTO groups_units_link (group_id, unit_id) VALUES (17, 4);
INSERT INTO groups_units_link (group_id, unit_id) VALUES (18, 4);


------ users & map users to groups
---- system administrator
INSERT INTO users (username, name, password, quota_max, quota_current, email, mobile, email_notification, sms_notification, ldap_dn, max_sessions, language_id)
            VALUES ("admin", "Administrator", "21232f297a57a5a743894a0e4a801fc3", "0", "0", "", "", 1, 1, "", 1, 1);
INSERT INTO users_groups_link (group_id, user_id) VALUES (1, 1);
            
---- guest user
INSERT INTO users (username, name, password, quota_max, quota_current, email, mobile, email_notification, sms_notification, ldap_dn, max_sessions, language_id)
            VALUES ("guest", "Anonymous", "084e0343a0486ff05530df6c705c8bb4", "0", "0", "", "", 0, 0, "", 19, 1);
INSERT INTO users_groups_link (group_id, user_id) VALUES (6, 2);
            
---- unit administrators
-- adarg
INSERT INTO users (username, name, password, quota_max, quota_current, email, mobile, email_notification, sms_notification, ldap_dn, max_sessions, language_id)
            VALUES ("adargAdmin", "ADARG Unit Administrator", "21232f297a57a5a743894a0e4a801fc3", "0", "0", "", "", 0, 0, "", 1, 1);
INSERT INTO users_groups_link (group_id, user_id) VALUES (2, 3);

-- afroaids
INSERT INTO users (username, name, password, quota_max, quota_current, email, mobile, email_notification, sms_notification, ldap_dn, max_sessions, language_id)
            VALUES ("afroaidsAdmin", "afroAIDS Unit Administrator", "21232f297a57a5a743894a0e4a801fc3", "0", "0", "", "", 0, 0, "", 1, 1);
INSERT INTO users_groups_link (group_id, user_id) VALUES (3, 4);

-- diabetes
INSERT INTO users (username, name, password, quota_max, quota_current, email, mobile, email_notification, sms_notification, ldap_dn, max_sessions, language_id)
            VALUES ("diabetesAdmin", "Diabetes Unit Administrator", "21232f297a57a5a743894a0e4a801fc3", "0", "0", "", "", 0, 0, "", 1, 1);
INSERT INTO users_groups_link (group_id, user_id) VALUES (4, 5);    
        
-- disease
INSERT INTO users (username, name, password, quota_max, quota_current, email, mobile, email_notification, sms_notification, ldap_dn, max_sessions, language_id)
            VALUES ("diseaseAdmin", "Burden of Disease Unit Administrator", "21232f297a57a5a743894a0e4a801fc3", "0", "0", "", "", 0, 0, "", 1, 1);
INSERT INTO users_groups_link (group_id, user_id) VALUES (5, 6);

---- unit users
-- adarg unit user
INSERT INTO users (username, name, password, quota_max, quota_current, email, mobile, email_notification, sms_notification, ldap_dn, max_sessions, language_id)
            VALUES ("adargUser", "adargUser", "084e0343a0486ff05530df6c705c8bb4", "0", "0", "", "", 0, 0, "", 1, 1);
-- spell checker and editor
INSERT INTO users_groups_link (group_id, user_id) VALUES (7, 7);
INSERT INTO users_groups_link (group_id, user_id) VALUES (8, 7);

-- afroaids unit user
INSERT INTO users (username, name, password, quota_max, quota_current, email, mobile, email_notification, sms_notification, ldap_dn, max_sessions, language_id)
            VALUES ("afroaidsUser", "afroaidsUser", "084e0343a0486ff05530df6c705c8bb4", "0", "0", "", "", 0, 0, "", 1, 1);
-- just spell checker
INSERT INTO users_groups_link (group_id, user_id) VALUES (10, 8);

-- diabetes unit user
INSERT INTO users (username, name, password, quota_max, quota_current, email, mobile, email_notification, sms_notification, ldap_dn, max_sessions, language_id)
            VALUES ("diabetesUser", "diabetesUser", "084e0343a0486ff05530df6c705c8bb4", "0", "0", "", "", 0, 0, "", 1, 1);
-- editor and finance
INSERT INTO users_groups_link (group_id, user_id) VALUES (14, 9);
INSERT INTO users_groups_link (group_id, user_id) VALUES (15, 9);

-- disease unit user
INSERT INTO users (username, name, password, quota_max, quota_current, email, mobile, email_notification, sms_notification, ldap_dn, max_sessions, language_id)
            VALUES ("diseaseUser", "diseaseUser", "084e0343a0486ff05530df6c705c8bb4", "0", "0", "", "", 0, 0, "", 1, 1);
-- spell checker, editor and finance
INSERT INTO users_groups_link (group_id, user_id) VALUES (16, 10);
INSERT INTO users_groups_link (group_id, user_id) VALUES (17, 10);
INSERT INTO users_groups_link (group_id, user_id) VALUES (18, 10);

-- default document type fields
INSERT INTO document_fields (name, data_type, is_generic) VALUES ("Category", "String", 1);
INSERT INTO document_fields (name, data_type, is_generic) VALUES ("Keywords", "String", 1);
INSERT INTO document_fields (name, data_type, is_generic) VALUES ("Comments", "String", 1);
INSERT INTO document_fields (name, data_type, is_generic) VALUES ("Author(s)", "String", 1);

-- default document types
INSERT INTO document_types_lookup (name) VALUES ("Admin");
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (1, 1, 1);
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (1, 2, 0);
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (1, 3, 0);
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (1, 4, 1);

INSERT INTO document_types_lookup (name) VALUES ("Proposal");
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (2, 1, 1);
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (2, 2, 0);
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (2, 3, 0);
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (2, 4, 1);

INSERT INTO document_types_lookup (name) VALUES ("Publications");
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (3, 1, 1);
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (3, 2, 0);
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (3, 3, 0);
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (3, 4, 1);

INSERT INTO document_types_lookup (name) VALUES ("Research");
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (4, 1, 1);
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (4, 2, 0);
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (4, 3, 0);
INSERT INTO document_type_fields_link (document_type_id, field_id, is_mandatory) VALUES (4, 4, 1);


-- define folder structure
---- mrc organisation root folder
INSERT INTO folders (name, description, parent_id, creator_id, document_type_id, unit_id, is_public)
             VALUES ("Medical Research Council", "MRC Root Document Folder", 0, 1, 0, 0, 0); -- id=1

---- adarg unit folders
-- [7,8,9]
INSERT INTO folders (name, description, parent_id, creator_id, document_type_id, unit_id, is_public)
             VALUES ("ADARG", "ADARG Unit Root Folder", 1, 1, 0, 1, 0);  -- id=2
             -- unit admins have write access
             INSERT INTO groups_folders_link (group_id, folder_id, can_read, can_write) VALUES (2, 2, 0, 1);
                 
---- afroaids unit folders
-- [10,11,12]
INSERT INTO folders (name, description, parent_id, creator_id, document_type_id, unit_id, is_public)
             VALUES ("AfroAIDS", "AfroAIDS Unit Root Folder", 1, 1, 0, 2, 0); -- id=14
             -- unit admins have write access
             INSERT INTO groups_folders_link (group_id, folder_id, can_read, can_write) VALUES (3, 14, 0, 1);             
             
---- diabetes unit folders
--[13,14,15]
INSERT INTO folders (name, description, parent_id, creator_id, document_type_id, unit_id, is_public)
             VALUES ("Diabetes", "Diabetes Unit Root Folder", 1, 1, 0, 3, 0);  -- id=26
             -- unit admins have write access
             INSERT INTO groups_folders_link (group_id, folder_id, can_read, can_write) VALUES (4, 26, 0, 1);
             
---- burden of disease unit folders
-- [16,17,18]
INSERT INTO folders (name, description, parent_id, creator_id, document_type_id, unit_id, is_public)
             VALUES ("Burden of Disease", "Burden of Disease Unit Root Folder", 1, 1, 0, 4, 0);  -- id=38
             -- unit admins have write access
             INSERT INTO groups_folders_link (group_id, folder_id, can_read, can_write) VALUES (5, 38, 0, 1);             
             
-- TODO: populate categories_lookup

-- sitemap sections
INSERT INTO site_sections_lookup (name) VALUES ("General");
INSERT INTO site_sections_lookup (name) VALUES ("Manage Documents");
INSERT INTO site_sections_lookup (name) VALUES ("Administration");
INSERT INTO site_sections_lookup (name) VALUES ("Advanced Search");
INSERT INTO site_sections_lookup (name) VALUES ("Preferences");
INSERT INTO site_sections_lookup (name) VALUES ("Help");
INSERT INTO site_sections_lookup (name) VALUES ("Logout");
INSERT INTO site_sections_lookup (name) VALUES ("Tests");
-- sitemap access levels
INSERT INTO site_access_lookup (name) VALUES ("None");
INSERT INTO site_access_lookup (name) VALUES ("Guest");
INSERT INTO site_access_lookup (name) VALUES ("User");
INSERT INTO site_access_lookup (name) VALUES ("UnitAdmin");
INSERT INTO site_access_lookup (name) VALUES ("SysAdmin");
---- sitemap definition
-- general section
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default) VALUES ("login", "/presentation/login.php?loginAction=login", 1, 0, "", 0);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default) VALUES ("loginForm", "/presentation/login.php?loginAction=loginForm", 1, 0, "login", 0); 
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default) VALUES ("dashboard", "/presentation/dashboardBL.php", 1, 1, "dashboard", 0);
-- manage documents section
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default) VALUES ("browse", "/presentation/lookAndFeel/knowledgeTree/documentmanagement/browseBL.php", 2, 2, "browse documents", 1);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default) VALUES ("viewDocument", "/presentation/lookAndFeel/knowledgeTree/documentmanagement/viewBL.php", 2, 2, "", 0);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("addDocument", "/presentation/documentmanagement/addDocument.php", 2, 3, "Add A Document", 0, 0);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("addFolder", "/presentation/documentmanagement/addFolder.php", 2, 4, "Add A Folder", 0, 0);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("modifyFolderProperties", "/presentation/documentmanagement/modifyFolder.php", 2, 4, "Modify Folder Properties", 0, 0);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("deleteFolder", "/presentation/documentmanagement/deleteFolder.php", 2, 4, "Delete A Folder", 0, 0);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("moveFolder", "/presentation/documentmanagement/moveFolder.php", 2, 4, "Move A Folder", 0, 0);
-- pages for administration section
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("administration", "/admin.php", 3, 4, "Administration", 1, 0);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("unitAdministration", "/presentation/unitAdmin.php", 3, 4, "Unit Administration", 0, 0);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("systemAdministration", "/presentation/sysAdmin.php", 3, 5, "System Administration", 0, 0);
-- pages for advanced search section
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("advancedSearch", "/search.php", 4, 2, "Advanced Search", 1, 0);
-- pages for prefs section
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("preferences", "/preferences.php", 5, 3, "Preferences", 1, 0);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("viewPreferences", "/preferences.php", 5, 3, "View Preferences", 0, 0);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("editPreferences", "/preferences.php", 5, 3, "Edit Preferences", 0, 0);
-- pages for Help section
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default) VALUES ("help", "/help.php", 6, 2, "Help", 1);
-- pages for logout section section
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default) VALUES ("logout", "/presentation/logout.php", 7, 2, "Logout", 1);
-- test pages
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("scratchPad", "/tests/scratchPad.php", 8, 2, "scratch", 0, 0);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("sitemap", "/tests/session/SiteMap.php", 8, 2, "sitemap", 0, 0);
INSERT INTO sitemap (action, page, section_id, access_id, link_text, is_default, is_enabled) VALUES ("documentBrowserTest", "/tests/documentmanagement/DocumentBrowser.php", 8, 2, "test the document browser", 0, 0);

