-- Tables:
-- * Global_id         -- Generates ids for other tables
--
-- * Users             -- All users in the system
-- * Usergroup         -- Different user groups in the system
-- * Groupmember       -- Members of various groups
-- * Usersetting       -- Miscelanneous settings for users
-- * Contactinfo       -- Numbers / email addresses of the users
--
-- * Projectfolder     -- Project containers; Each project is in some folder (or in virtual root folder)
--
-- * Project           -- All projects in the system
-- * Projectversion    -- Project may be divided into more than one version;
                       -- bugs and tasks reference different versions
-- * Projectnote       -- Generic notes
-- * Projectsetting    -- Miscellaneous project specific settings
-- * Staffmember       -- Users that are set as responsible persons in certain
                       -- project
-- * Accessright       -- The access rights assigned for certain person in a
                       -- project
-- * Projectgroup      -- A group of users assigned to a project
-- * Bug               -- Bugs in certain project
-- * Bugresponsible    -- Responsible people in different bugs. This relation
                       -- is required because multiple responsible persons are
                       -- allowed
-- * Bugnote           -- Added when someone changes bug status
-- * Bugrelatedbug     -- Bugs that are related with each other
-- * Task              -- Tasks in certain project
-- * Taskresponsible   -- Responsible people in different tasks. This relation
                       -- is required because multiple responsible persons are
                       -- allowed
-- * Tasknote          -- Added when someone changes task status
-- * Taskrelatedtask   -- Tasks that are related with each other
-- * Bugrelatedtask    -- Tasks that are related to some bugs (and vica versa)
--
-- * Spenteffort       -- Spent time that is not related to any task or bug
--
-- * Responsiblerole   -- Differentiates the roles of responsibles
--
-- * Featurerequest    -- Requests / proposals as forthcoming tasks
-- * Requestnote       -- Comments regarding some note
--
-- * Attachment        -- File uploaded to the server. May be related with tasks, bugs and specifications
--
-- * Positiontemplate  -- Template for staff member position (programmer / tester / ...)
--
-- * Tag               -- Used to store "important dates", such as minor versions
--
-- * Auditentry        -- This table holds information regarding all user actions
--
-- * Typedsetting      -- Holder for any elements that consist of name (and value)
-- * Setting           -- General settings for the system
-- * Statistics        -- Statistics about the system in general and about all users


CREATE TABLE global_id(
type INTEGER NOT NULL PRIMARY KEY,
id INTEGER NOT NULL
);


CREATE TABLE users(
id INTEGER NOT NULL PRIMARY KEY,
loginname VARCHAR(32) NOT NULL, -- name used in login
loginname_upper VARCHAR(32) NOT NULL, -- same as loginname but in upper case. used to find users case insensitively
password VARCHAR(64) NOT NULL, -- password used in login
type INTEGER NOT NULL, -- type of this user: Administrator / Normal user
name VARCHAR(128) NOT NULL, -- actual name of the user
language INTEGER NOT NULL, -- the language in which to show the webpages
streetaddress VARCHAR(255), -- street address
city VARCHAR(128), -- city
country VARCHAR(128), -- country
company VARCHAR(128), -- company
areaofexpertise VARCHAR(255), -- special expertise areas
disabled INTEGER, -- whether or not this account is disabled
flags INTEGER NOT NULL -- miscellaneous settings
);


CREATE TABLE usergroup(
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL -- name of the group
);


CREATE TABLE groupmember(
id INTEGER NOT NULL PRIMARY KEY,
groupid INTEGER NOT NULL, -- id of the group
userid INTEGER NOT NULL, -- id of the user
FOREIGN KEY (groupid) REFERENCES usergroup (id),
FOREIGN KEY (userid) REFERENCES users (id)
);


CREATE TABLE usersetting(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- the user whose setting this is
name VARCHAR(64) NOT NULL, -- name of the setting
data VARCHAR(255) NOT NULL, -- value of the setting
FOREIGN KEY (userid) REFERENCES users (id)
);


CREATE TABLE contactinfo(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- id of the user this contact info references to
type INTEGER NOT NULL, -- type of this contact info: GSM / Phone / Email / FAX
data VARCHAR(255) NOT NULL, -- email address / phone number
active INTEGER NOT NULL, -- whether or not this contact info can be used to notify the user when
                         -- he / she is set as the responsible person in some bug or task: true / false
FOREIGN KEY (userid) REFERENCES users (id)
);


CREATE TABLE projectfolder(
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL, -- name of the folder
parentid INTEGER -- id of the parent folder (may be null for the root folders)
);


CREATE TABLE project(
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(128) NOT NULL, -- name of the project
specification TEXT, -- project specification
modifieddate VARCHAR(64), -- last date when the specifications were modified
modifierid INTEGER, -- id of the user who modified the specification
defaultaccess INTEGER NOT NULL, -- default access rights to this project (-1 if none)
flags INTEGER NOT NULL, -- different flags, currently only FG_DISABLED is supported
folderid INTEGER NOT NULL -- id of the folder this project belongs to (-1 if none)
);


CREATE TABLE projectversion(
id INTEGER NOT NULL PRIMARY KEY,
projectid INTEGER NOT NULL, -- references to the project whose version this row specifies
version VARCHAR(255) NOT NULL, -- string descriptor for the version (e.g. "1.00")
deadline VARCHAR(64), -- deadline for this version
implementationdate VARCHAR(84) NOT NULL,
FOREIGN KEY (projectid) REFERENCES project (id)
);


CREATE TABLE projectnote(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER, -- id of the user who added this note
projectid INTEGER NOT NULL, -- id of the project this note references to
modifierid INTEGER, -- id of the user who last modified this note
modifydate VARCHAR(64) NOT NULL, -- the date when the note was modified
title VARCHAR(255) NOT NULL, -- title of the note
text TEXT NOT NULL, -- note text
FOREIGN KEY (projectid) REFERENCES project (id)
);


CREATE TABLE projectsetting(
id INTEGER NOT NULL PRIMARY KEY,
projectid INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
value VARCHAR(255),
FOREIGN KEY (projectid) REFERENCES project (id)
);


CREATE TABLE staffmember(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- id of the user assigned to the project
projectid INTEGER NOT NULL, -- id of the project the user is assigned to
position VARCHAR(128), -- the job description for this user (project manager / programmer / tester / ...)
flags INTEGER NOT NULL, -- different flags, currently the only supported flags is FG_DISABLED
viagrouprelation INTEGER NOT NULL, -- 1 if this membership is based only on group relation, 0 otherwise
FOREIGN KEY (userid) REFERENCES users (id),
FOREIGN KEY (projectid) REFERENCES project (id)
);


CREATE TABLE accessright(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- id of the user to whom this right affects
projectid INTEGER NOT NULL, -- id of the project for which this right grants access
access INTEGER NOT NULL, -- type of access: add task / read & write / read / other
viagrouprelation INTEGER NOT NULL, -- 1 if this access right is based only on group relation, 0 otherwise
FOREIGN KEY (userid) REFERENCES users (id),
FOREIGN KEY (projectid) REFERENCES project (id)
);


CREATE TABLE projectgroup(
id INTEGER NOT NULL PRIMARY KEY,
usergroupid INTEGER NOT NULL, -- the id of the related user group
projectid INTEGER NOT NULL, -- the id of the related project
grouprights INTEGER NOT NULL, -- default rights for new group members
grouprole VARCHAR(255) NOT NULL, -- default role name for new group members
grouptype INTEGER NOT NULL, -- 0 = staff member group, 1 = other access right group
FOREIGN KEY (usergroupid) REFERENCES usergroup (id),
FOREIGN KEY (projectid) REFERENCES project (id)
);


CREATE TABLE bug(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- id of the user who added the report of this bug
projectversionid INTEGER NOT NULL, -- id of the project in which the bug was located
title VARCHAR(255) NOT NULL, -- title for the bug
description TEXT, -- more detailed description
howtorepeat TEXT, -- information on how to repeat the bug
environment TEXT, -- information about the environment in which the bug occured (OS, CPU, ...)
suggestedfix TEXT, -- suggestion as a fix for the bug
adddate VARCHAR(64) NOT NULL, -- date when this bug report was added
modifydate VARCHAR(64) NOT NULL, -- last date when this bug report was modified
status INTEGER NOT NULL, -- status of the bug (open / being fixed / in testing / fixed / was not a bug / other)
priority INTEGER NOT NULL, -- severity of the bug (critical / high / medium / low / ...)
deadline VARCHAR(64), -- the date this bug needs to be fixed by latest
notified INTEGER, -- whether or not a notification of approaching deadline has been sent
flags INTEGER NOT NULL, -- miscellaneous settings
category INTEGER NOT NULL,
FOREIGN KEY (userid) REFERENCES users (id),
FOREIGN KEY (projectversionid) REFERENCES projectversion (id)
);


CREATE TABLE bugresponsible(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- id of this responsible person
bugid INTEGER NOT NULL, -- id of the bug for which this responsibility applies
type INTEGER, -- the type of this responsibility
FOREIGN KEY (userid) REFERENCES users (id),
FOREIGN KEY (bugid) REFERENCES bug (id)
);


CREATE TABLE bugnote(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- id of the user who added this note
bugid INTEGER NOT NULL, -- id of the bug this note) REFERENCES to
adddate VARCHAR(64) NOT NULL, -- the date when the note was added
text TEXT NOT NULL, -- note text
newstatus INTEGER NOT NULL, -- status of the bug after modification
fixing_time INTEGER, -- time taken to fix the bug
workcategory INTEGER,
FOREIGN KEY (userid) REFERENCES users (id),
FOREIGN KEY (bugid) REFERENCES bug (id)
);


CREATE TABLE bugrelatedbug(
id INTEGER NOT NULL PRIMARY KEY,
bugid1 INTEGER NOT NULL, -- id of first bug
bugid2 INTEGER NOT NULL, -- id of second bug
FOREIGN KEY (bugid1) REFERENCES bug (id),
FOREIGN KEY (bugid2) REFERENCES bug (id)
);


CREATE TABLE task(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- id of the user who added this task (might not be needed)
projectversionid INTEGER NOT NULL, -- id of the project this task belongs to
title VARCHAR(255) NOT NULL, -- title of the task
description TEXT NOT NULL, -- more detailed description of the task
adddate VARCHAR(64) NOT NULL, -- the date this task was added (might not be needed)
modifydate VARCHAR(64) NOT NULL, -- last date when this task was modified
status INTEGER NOT NULL, -- status of the task (not started / designing / implementing / testing / done)
importance INTEGER NOT NULL, -- importance of the feature (high / medium / low)
deadline VARCHAR(64) NOT NULL, -- the date this task must be completed (defaults to project deadline)
estimated_duration INTEGER, -- estimated time that the task will take
notified INTEGER, -- whether or not a notification of approaching deadline has been sent
flags INTEGER NOT NULL, -- miscellaneous settings
tasktype INTEGER NOT NULL,
implementationdate VARCHAR(84) NOT NULL,
category INTEGER NOT NULL,
FOREIGN KEY (userid) REFERENCES users (id),
FOREIGN KEY (projectversionid) REFERENCES projectversion (id)
);


CREATE TABLE taskresponsible(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- id of this responsible person
taskid INTEGER NOT NULL, -- id of the task for which this responsibility applies
type INTEGER, -- the type of this responsibility
FOREIGN KEY (userid) REFERENCES users (id),
FOREIGN KEY (taskid) REFERENCES task (id)
);


CREATE TABLE tasknote(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- id of the user who added this note
taskid INTEGER NOT NULL, -- id of the task this note) REFERENCES to
adddate VARCHAR(64) NOT NULL, -- the date when the note was added
text TEXT NOT NULL, -- note text
newstatus INTEGER NOT NULL, -- new status of the task
effort INTEGER, -- time spent to achieve the status change
workcategory INTEGER,
FOREIGN KEY (userid) REFERENCES users (id),
FOREIGN KEY (taskid) REFERENCES task (id)
);


CREATE TABLE taskrelatedtask(
id INTEGER NOT NULL PRIMARY KEY,
taskid1 INTEGER NOT NULL, -- id of first task
taskid2 INTEGER NOT NULL, -- id of second task
FOREIGN KEY (taskid1) REFERENCES task (id),
FOREIGN KEY (taskid2) REFERENCES task (id)
);


CREATE TABLE bugrelatedtask(
id INTEGER NOT NULL PRIMARY KEY,
bugid INTEGER NOT NULL, -- id of the bug
taskid INTEGER NOT NULL, -- id of the task
FOREIGN KEY (bugid) REFERENCES bug (id),
FOREIGN KEY (taskid) REFERENCES task (id)
);


CREATE TABLE spenteffort(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- id of the user by whom this time was spent
projectid INTEGER NOT NULL, -- id of the project to which this time was spent
adddate VARCHAR(64) NOT NULL, -- the date when this entry was added
effort INTEGER NOT NULL, -- the time spent
categoryid INTEGER, -- id of the work category (0 if none)
title VARCHAR(255) NOT NULL, -- title of the effort
description TEXT, -- description of the effort
FOREIGN KEY (userid) REFERENCES users (id),
FOREIGN KEY (projectid) REFERENCES project (id)
);


CREATE TABLE responsiblerole(
id INTEGER NOT NULL PRIMARY KEY,
description VARCHAR(255) NOT NULL,
flags INTEGER NOT NULL -- FG_ISDEFAULT, FG_NORMALNOTIFICATION, FG_DONENOTIFICATION, FG_ALWAYSNOTIFY
);


CREATE TABLE featurerequest(
id INTEGER NOT NULL PRIMARY KEY,
title VARCHAR(255) NOT NULL, -- title of the feature request
description TEXT, -- description of the feature request
adddate VARCHAR(64) NOT NULL, -- date when the request was added
status INTEGER NOT NULL, -- open / disapproved / approved
taskid INTEGER, -- id of the task which was created when the request was approved
responsibleid INTEGER NOT NULL, -- responsible person
projectid INTEGER NOT NULL, -- project for which this request applies
userid INTEGER NOT NULL, -- id of the user who requested this feature
FOREIGN KEY (responsibleid) REFERENCES users (id),
FOREIGN KEY (projectid) REFERENCES project (id),
FOREIGN KEY (userid) REFERENCES users (id)
);


CREATE TABLE requestnote(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- id of the user who added this note
requestid INTEGER NOT NULL, -- id of the request this note) REFERENCES to
adddate VARCHAR(64) NOT NULL, -- the date when the note was added
text TEXT NOT NULL, -- note text
FOREIGN KEY (userid) REFERENCES users (id),
FOREIGN KEY (requestid) REFERENCES featurerequest (id)
);


CREATE TABLE tag(
id INTEGER NOT NULL PRIMARY KEY,
projectid INTEGER NOT NULL, -- id of the project this tag refers to
name VARCHAR(255) NOT NULL, -- name of this tag (e.g. v1.05)
refdate VARCHAR(64) NOT NULL, -- date this tag identifies
FOREIGN KEY (projectid) REFERENCES project (id)
);


CREATE TABLE attachment(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER, -- id of the user who uploaded this attachment
objectid INTEGER NOT NULL, -- id of the object this attachment is linked into (not unique)
objecttype INTEGER NOT NULL, -- type of the object this attachment is linked into (for example ID_BUG)
filename VARCHAR(255) NOT NULL, -- name of the file on server
downloadname VARCHAR(255) NOT NULL, -- the name to which the attachment will be saved (original filename)
uploaddate VARCHAR(64) NOT NULL, -- the time when the file was uploaded
description vARCHAR(255) NOT NULL, -- description of the file (to be used instead of filename if specified)
contenttype VARCHAR(255) NOT NULL, -- content type of the file
contentdisposition VARCHAR(255) NOT NULL, -- content disposition of the file
version VARCHAR(255), -- version or some other identifier string for this attachment
invalidated INTEGER NOT NULL, -- 0 for normal attachments, 1 for attachments that have been replaced by a newer version
predecessorid INTEGER -- id of the attachment that precedes this attachment (null if there are no predecessors)
);


CREATE TABLE positiontemplate(
id INTEGER NOT NULL PRIMARY KEY,
position VARCHAR(255) NOT NULL, -- description of the position (tester / programmer / project leader / ...)
access INTEGER NOT NULL -- default access rights
);


CREATE TABLE auditentry(
id VARCHAR(64) NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- user id if any (-1 if no user id is available [failed logins])
audittime VARCHAR(32) NOT NULL, -- the time of the event (Date.getTime(); GMT). always padded to the length of 20. E.g. (0000000081542454433978)
entrytype INTEGER NOT NULL, -- type of the audit entry, e.g. login, logout, failed login, add entry, sent email, modify entry, remove entry, ...
relationtype INTEGER NOT NULL, -- type of the related entry. -1 if no related type. e.g. task, tasknote, time, ...
relationid INTEGER NOT NULL, -- id of the related entry. -1 if no id is available
entrydata TEXT NOT NULL, -- entry dependant data. for example an IP address or title of the modified entry
entrydata2 TEXT NOT NULL -- optional additional entry dependant data. for example a description of the modifications made
);


CREATE TABLE typedsetting(
id INTEGER NOT NULL PRIMARY KEY,
type INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
data VARCHAR(255) NOT NULL
);


CREATE TABLE setting(
name VARCHAR(64) NOT NULL PRIMARY KEY,
data VARCHAR(255) NOT NULL
);


CREATE TABLE rstatistics(
id INTEGER NOT NULL PRIMARY KEY,
userid INTEGER NOT NULL, -- user identifier or 0 (if 0 this record specifies the statistics of entire system)
logins INTEGER NOT NULL, -- number of logins to the system
failedlogins INTEGER NOT NULL, -- number of failed logins
lastlogin VARCHAR(64) NOT NULL, -- time of last login
pagesrequested INTEGER NOT NULL, -- number of pages requested
downloadedkb INTEGER, -- kilobytes of attachments downloaded by this user
uploadedkb INTEGER -- kilobytes of attachments uploaded by this user
);