DROP TABLE IF EXISTS `administers`;
DROP TABLE IF EXISTS `belongs`;
DROP TABLE IF EXISTS `BINARYDATA`;
DROP TABLE IF EXISTS `CHOICE`;
DROP TABLE IF EXISTS `EVENT`;
DROP TABLE IF EXISTS `EVENTTIME`;
DROP TABLE IF EXISTS `EVENTTYPE`;
DROP TABLE IF EXISTS `RESEARCH`;
DROP TABLE IF EXISTS `ROLE`;
DROP TABLE IF EXISTS `SCHEDULE`;
DROP TABLE IF EXISTS `TASK`;
DROP TABLE IF EXISTS `TASKDATA`;
DROP TABLE IF EXISTS `TASKTYPE`;
DROP TABLE IF EXISTS `TEXT`;
DROP TABLE IF EXISTS `USER`;
DROP TABLE IF EXISTS `USERGROUP`;


CREATE TABLE USER (
userID INTEGER NOT NULL AUTO_INCREMENT,
userName VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL,

CONSTRAINT USER_PK 
	PRIMARY KEY (userID)
)
;


CREATE TABLE SCHEDULE (
scheduleID INTEGER NOT NULL AUTO_INCREMENT,
zeroTime DATE NOT NULL,

CONSTRAINT SCHEDULE_PK 
	PRIMARY KEY (scheduleID)
)
;


CREATE TABLE RESEARCH (
researchID INTEGER NOT NULL AUTO_INCREMENT,
description VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,

CONSTRAINT RESEARCH_PK 
	PRIMARY KEY (researchID)
)
;


CREATE TABLE ROLE (
roleID INTEGER NOT NULL AUTO_INCREMENT,
description VARCHAR(100) NOT NULL,

CONSTRAINT ROLE_PK 
	PRIMARY KEY (roleID)
)
;


CREATE TABLE TASKTYPE (
taskTypeID INTEGER NOT NULL AUTO_INCREMENT,
description VARCHAR(100) NOT NULL,
binarydataMin INTEGER NOT NULL,
textMin INTEGER NOT NULL,
textMax INTEGER NOT NULL,
binarydataMax INTEGER NOT NULL,

CONSTRAINT TASKTYPE_PK 
	PRIMARY KEY (taskTypeID)
)
;


CREATE TABLE EVENTTYPE (
eventTypeID INTEGER NOT NULL AUTO_INCREMENT,
description VARCHAR(100) NOT NULL,

CONSTRAINT EVENTTYPE_PK 
	PRIMARY KEY (eventTypeID)
)
;


CREATE TABLE EVENTTIME (
eventTimeID INTEGER NOT NULL AUTO_INCREMENT,
absolute INTEGER NOT NULL,
year INTEGER NULL,
month INTEGER NULL,
day INTEGER NULL,
weekday INTEGER NULL,
minute INTEGER NULL,
week INTEGER NULL,
fromHour INTEGER NULL,
toHour INTEGER NULL,

CONSTRAINT EVENTTIME_PK 
	PRIMARY KEY (eventTimeID)
)
;


CREATE TABLE USERGROUP (
groupID INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
description VARCHAR(100) NULL,
editedBy INTEGER NOT NULL,
editTime DATE NOT NULL,
SCHEDULE_scheduleID INTEGER NOT NULL,
RESEARCH_researchID INTEGER NOT NULL,
ROLE_roleID INTEGER NOT NULL,

CONSTRAINT USERGROUP_PK 
	PRIMARY KEY (groupID),
CONSTRAINT USERGROUP_scheduleID 
	FOREIGN KEY (SCHEDULE_scheduleID)
	REFERENCES SCHEDULE (scheduleID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT USERGROUP_researchID 
	FOREIGN KEY (RESEARCH_researchID)
	REFERENCES RESEARCH (researchID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT USERGROUP_roleID 
	FOREIGN KEY (ROLE_roleID)
	REFERENCES ROLE (roleID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE EVENT (
eventID INTEGER NOT NULL AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
description VARCHAR(100) NOT NULL,
estimatedTime INTEGER NULL,
status VARCHAR(100) NOT NULL,
SCHEDULE_scheduleID INTEGER NOT NULL,
EVENTTYPE_eventTypeID INTEGER NOT NULL,
EVENTTIME_eventTimeID INTEGER NOT NULL,

CONSTRAINT EVENT_PK 
	PRIMARY KEY (eventID),
CONSTRAINT EVENT_scheduleID 
	FOREIGN KEY (SCHEDULE_scheduleID)
	REFERENCES SCHEDULE (scheduleID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT EVENT_eventTypeID 
	FOREIGN KEY (EVENTTYPE_eventTypeID)
	REFERENCES EVENTTYPE (eventTypeID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT EVENT_eventTimeID 
	FOREIGN KEY (EVENTTIME_eventTimeID)
	REFERENCES EVENTTIME (eventTimeID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE TASK (
taskID INTEGER NOT NULL AUTO_INCREMENT,
description VARCHAR(100) NULL,
content VARCHAR(100) NULL,
url VARCHAR(100) NULL,
sequence INTEGER NOT NULL,
EVENT_eventID INTEGER NOT NULL,
TASKTYPE_taskTypeID INTEGER NOT NULL,

CONSTRAINT TASK_PK 
	PRIMARY KEY (taskID),
CONSTRAINT TASK_eventID 
	FOREIGN KEY (EVENT_eventID)
	REFERENCES EVENT (eventID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT TASK_taskTypeID 
	FOREIGN KEY (TASKTYPE_taskTypeID)
	REFERENCES TASKTYPE (taskTypeID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE CHOICE (
choiceID INTEGER NOT NULL AUTO_INCREMENT,
description VARCHAR(100) NULL,
sequence INTEGER NOT NULL,
value INTEGER NOT NULL,
TASK_contains_taskID INTEGER NOT NULL,
TASK_leadsto_taskID INTEGER NULL,

CONSTRAINT CHOICE_PK 
	PRIMARY KEY (choiceID),
CONSTRAINT CHOICE_contains_taskID 
	FOREIGN KEY (TASK_contains_taskID)
	REFERENCES TASK (taskID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT CHOICE_leadsto_taskID 
	FOREIGN KEY (TASK_contains_taskID)
	REFERENCES TASK (taskID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE TASKDATA (
taskDataID INTEGER NOT NULL AUTO_INCREMENT,
executionTime DATE NOT NULL,
sequence INTEGER NOT NULL,
USER_userID INTEGER NOT NULL,
CHOICE_choiceID INTEGER NOT NULL,

CONSTRAINT TASKDATA_PK 
	PRIMARY KEY (taskDataID),
CONSTRAINT TASKDATA_userID 
	FOREIGN KEY (USER_userID)
	REFERENCES USER (userID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT TASKDATA_choiceID 
	FOREIGN KEY (CHOICE_choiceID)
	REFERENCES CHOICE (choiceID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE BINARYDATA (
binarydataID INTEGER NOT NULL AUTO_INCREMENT,
sequence INTEGER NOT NULL,
TASKDATA_taskDataID INTEGER NOT NULL,

CONSTRAINT BINARYDATA_PK 
	PRIMARY KEY (binarydataID),
CONSTRAINT BINARYDATA_taskDataID 
	FOREIGN KEY (TASKDATA_taskDataID)
	REFERENCES TASKDATA (taskDataID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE TEXT (
textID INTEGER NOT NULL AUTO_INCREMENT,
sequence INTEGER NOT NULL,
TASKDATA_taskDataID INTEGER NOT NULL,

CONSTRAINT TEXT_PK 
	PRIMARY KEY (textID),
CONSTRAINT TEXT_taskDataID 
	FOREIGN KEY (TASKDATA_taskDataID)
	REFERENCES TASKDATA (taskDataID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE administers (
editTime DATE NOT NULL,
description INTEGER NOT NULL,
USER_userID INTEGER NOT NULL,
EVENT_eventID INTEGER NOT NULL,

CONSTRAINT administers_PK
	PRIMARY KEY (USER_userID,EVENT_eventID),
CONSTRAINT administers_userID 
	FOREIGN KEY (USER_userID)
	REFERENCES USER (userID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT administers_eventID 
	FOREIGN KEY (EVENT_eventID)
	REFERENCES EVENT (eventID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE belongs (
editTime DATE NOT NULL,
USERGROUP_groupID INTEGER NOT NULL,
USER_userID INTEGER NOT NULL,

CONSTRAINT belongs_PK
	PRIMARY KEY (USERGROUP_groupID,USER_userID),
CONSTRAINT belongs_groupID 
	FOREIGN KEY (USERGROUP_groupID)
	REFERENCES USERGROUP (groupID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT belongs_userID 
	FOREIGN KEY (USER_userID)
	REFERENCES USER (userID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


insert into RESEARCH (description, name) values ('Testitutkimus 1', 'Testitutkimus1');
insert into RESEARCH (description, name) values ('Testitutkimus 2', 'Testitutkimus2');

insert into SCHEDULE (zeroTime) values ('2012-04-30');
insert into SCHEDULE (zeroTime) values ('2012-04-30');
insert into SCHEDULE (zeroTime) values ('2012-04-30');

insert into ROLE (description) values ('Testitutkijarooli1');
insert into ROLE (description) values ('Testikäyttäjärooli1');

insert into USER (userName, password) values ('Testiyllapitaja', 'salasana');
insert into USER (userName, password) values ('Testitutkija1', 'salasana');
insert into USER (userName, password) values ('Testitutkija2', 'salasana');
insert into USER (userName, password) values ('Testikuntoutettava1', 'salasana');
insert into USER (userName, password) values ('Testikuntoutettava2', 'salasana');
insert into USER (userName, password) values ('Testikuntoutettava3', 'salasana');
insert into USER (userName, password) values ('Testikuntoutettava4', 'salasana');

insert into USERGROUP (name, description, editedBy, editTime, SCHEDULE_scheduleID, RESEARCH_researchID, ROLE_roleID)
values ('Testitutkijaryhma1', 'Testitutkijaryhmä 1', '0', '2012-04-04', '1', '1', '1');
insert into USERGROUP (name, description, editedBy, editTime, SCHEDULE_scheduleID, RESEARCH_researchID, ROLE_roleID)
values ('Testitutkijaryhma2', 'Testitutkijaryhmä 2', '0', '2012-04-04', '1', '1', '1');
insert into USERGROUP (name, description, editedBy, editTime, SCHEDULE_scheduleID, RESEARCH_researchID, ROLE_roleID)
values ('Testikayttajaryhma1', 'Testikäyttäjäryhmä 1', '0', '2012-04-04', '2', '1', '2');
insert into USERGROUP (name, description, editedBy, editTime, SCHEDULE_scheduleID, RESEARCH_researchID, ROLE_roleID)
values ('Testikayttajaryhma2', 'Testikäyttäjäryhmä 2', '0', '2012-04-04', '2', '1', '2');

insert into TASKTYPE (description, binarydataMin, binarydataMax, textMin, textMax) values ('Teksti', '0', '0', '0', '0');
insert into TASKTYPE (description, binarydataMin, binarydataMax, textMin, textMax) values ('Radio', '0', '0', '0', '0');
insert into TASKTYPE (description, binarydataMin, binarydataMax, textMin, textMax) values ('Sensori', '0', '0', '0', '0');

insert into EVENTTYPE (description) values ('Liikunta');
insert into EVENTTYPE (description) values ('Kysely');
insert into EVENTTYPE (description) values ('Aivojumppa');

insert into EVENTTIME (absolute, day) values ('0', '3');
insert into EVENTTIME (absolute, day) values ('0', '5');
insert into EVENTTIME (absolute, day) values ('0', '7');

insert into EVENT (title, description, estimatedTime, status, SCHEDULE_scheduleID, EVENTTYPE_eventTypeID, EVENTTIME_eventTimeID)
values ('Mielikuvaharjoitus', 'Seuraava tapahtuma jumppaa älynystyröitäsi.', '5', 'Scheduled', '2', '3', '1');
insert into EVENT (title, description, estimatedTime, status, SCHEDULE_scheduleID, EVENTTYPE_eventTypeID, EVENTTIME_eventTimeID)
values ('Kävely', 'Seuraava tapahtuma jumppaa jalkojasi.', '60', 'Scheduled', '2', '1', '2');
insert into EVENT (title, description, estimatedTime, status, SCHEDULE_scheduleID, EVENTTYPE_eventTypeID, EVENTTIME_eventTimeID)
values ('Kysely', 'Hyvinvointikysely.', '10', 'Scheduled', '2', '2', '3');

insert into TASK (description, content, url, sequence, EVENT_eventID, TASKTYPE_taskTypeID) 
values ('Lue seuraava teksti.', 'Ajattele punaista omenaa...', NULL, '1', '1', '1');
insert into TASK (description, content, url, sequence, EVENT_eventID, TASKTYPE_taskTypeID) 
values ('Mitä mieltä olet juuri lukemastasi tekstistä?', NULL, NULL, '2', '1', '2');
insert into TASK (description, content, url, sequence, EVENT_eventID, TASKTYPE_taskTypeID) 
values ('Tapahtuma päättyi.', NULL, NULL, '3', '1', '1');

insert into TASK (description, content, url, sequence, EVENT_eventID, TASKTYPE_taskTypeID) 
values ('Lähde kävelylle.', 'Paina painiketta käynnistääksesi sen ja sen.', NULL, '1', '2', '1');
insert into TASK (description, content, url, sequence, EVENT_eventID, TASKTYPE_taskTypeID) 
values ('Olet kävelemässä.', 'Paina painiketta lopettaaksesi sen ja sen.', NULL, '2', '2', '3');
insert into TASK (description, content, url, sequence, EVENT_eventID, TASKTYPE_taskTypeID) 
values ('Kävely päättyi.', NULL, NULL, '3', '2', '1');

insert into TASK (description, content, url, sequence, EVENT_eventID, TASKTYPE_taskTypeID) 
values ('Vastaa seuraaviin kysymyksiin.', 'Jappa dappa.', NULL, '1', '3', '1');
insert into TASK (description, content, url, sequence, EVENT_eventID, TASKTYPE_taskTypeID) 
values ('Sapettaako?', NULL, NULL, '2', '3', '2');
insert into TASK (description, content, url, sequence, EVENT_eventID, TASKTYPE_taskTypeID) 
values ('Kysely päättyi.', NULL, NULL, '3', '3', '1');

insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values (NULL,          '0',  '1', '1', '2');
insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values ('Pidin.',      '10', '1', '2', '3');
insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values ('Menetteli.',  '5',  '2', '2', '3');
insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values ('En pitänyt.', '1',  '3', '2', '3');
insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values (NULL,          '0',  '1', '3', NULL);

insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values (NULL, '0', '1', '4', '5');
insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values (NULL, '0', '1', '5', '6');
insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values (NULL, '0', '1', '6', NULL);

insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values (NULL,             '0',  '1', '7', '8');
insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values ('Kyllä.',         '1',  '1', '8', '9');
insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values ('En osaa sanoa.', '5',  '2', '8', '9');
insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values ('Ei.',            '10', '3', '8', '9');
insert into CHOICE (description, value, sequence, TASK_contains_taskID, TASK_leadsto_taskID) values (NULL,             '0',  '1', '9', NULL);

insert into belongs (USERGROUP_groupID, USER_userID) values ('1', '1');
insert into belongs (USERGROUP_groupID, USER_userID) values ('2', '2');
insert into belongs (USERGROUP_groupID, USER_userID) values ('2', '3');
insert into belongs (USERGROUP_groupID, USER_userID) values ('3', '4');
insert into belongs (USERGROUP_groupID, USER_userID) values ('3', '5');
insert into belongs (USERGROUP_groupID, USER_userID) values ('4', '6');
insert into belongs (USERGROUP_groupID, USER_userID) values ('4', '7');

insert into administers (editTime, description, USER_userID, EVENT_eventID) values ('2012-04-04', 'Täyteteksti', '1', '1');
insert into administers (editTime, description, USER_userID, EVENT_eventID) values ('2012-04-04', 'Täyteteksti', '1', '2');
insert into administers (editTime, description, USER_userID, EVENT_eventID) values ('2012-04-04', 'Täyteteksti', '1', '3');