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 (
USER_userID INTEGER NOT NULL AUTO_INCREMENT,
USER_userName VARCHAR(100) NOT NULL,
USER_password VARCHAR(100) NOT NULL,

CONSTRAINT USER_PK 
	PRIMARY KEY (USER_userID)
)
;


CREATE TABLE SCHEDULE (
SCHEDULE_scheduleID INTEGER NOT NULL AUTO_INCREMENT,
SCHEDULE_zeroTime DATE NOT NULL,

CONSTRAINT SCHEDULE_PK 
	PRIMARY KEY (SCHEDULE_scheduleID)
)
;


CREATE TABLE RESEARCH (
RESEARCH_researchID INTEGER NOT NULL AUTO_INCREMENT,
RESEARCH_description VARCHAR(100) NOT NULL,
RESEARCH_name VARCHAR(100) NOT NULL,

CONSTRAINT RESEARCH_PK 
	PRIMARY KEY (RESEARCH_researchID)
)
;


CREATE TABLE ROLE (
ROLE_roleID INTEGER NOT NULL AUTO_INCREMENT,
ROLE_description VARCHAR(100) NOT NULL,

CONSTRAINT ROLE_PK 
	PRIMARY KEY (ROLE_roleID)
)
;


CREATE TABLE TASKTYPE (
TASKTYPE_taskTypeID INTEGER NOT NULL AUTO_INCREMENT,
TASKTYPE_description VARCHAR(100) NOT NULL,
TASKTYPE_binarydataMin INTEGER NOT NULL,
TASKTYPE_textMin INTEGER NOT NULL,
TASKTYPE_textMax INTEGER NOT NULL,
TASKTYPE_binarydataMax INTEGER NOT NULL,

CONSTRAINT TASKTYPE_PK 
	PRIMARY KEY (TASKTYPE_taskTypeID)
)
;


CREATE TABLE EVENTTYPE (
EVENTTYPE_eventTypeID INTEGER NOT NULL AUTO_INCREMENT,
EVENTTYPE_description VARCHAR(100) NOT NULL,

CONSTRAINT EVENTTYPE_PK 
	PRIMARY KEY (EVENTTYPE_eventTypeID)
)
;


CREATE TABLE EVENTTIME (
EVENTTIME_eventTimeID INTEGER NOT NULL AUTO_INCREMENT,
EVENTTIME_absolute INTEGER NOT NULL,
EVENTTIME_year INTEGER NULL,
EVENTTIME_month INTEGER NULL,
EVENTTIME_day INTEGER NULL,
EVENTTIME_weekday INTEGER NULL,
EVENTTIME_minute INTEGER NULL,
EVENTTIME_week INTEGER NULL,
EVENTTIME_fromHour INTEGER NULL,
EVENTTIME_toHour INTEGER NULL,

CONSTRAINT EVENTTIME_PK 
	PRIMARY KEY (EVENTTIME_eventTimeID)
)
;


CREATE TABLE USERGROUP (
USERGROUP_groupID INTEGER NOT NULL AUTO_INCREMENT,
USERGROUP_name VARCHAR(100) NOT NULL,
USERGROUP_description VARCHAR(100) NULL,
USERGROUP_editedBy INTEGER NOT NULL,
USERGROUP_editTime DATE NOT NULL,
USERGROUP_SCHEDULE_scheduleID INTEGER NOT NULL,
USERGROUP_RESEARCH_researchID INTEGER NOT NULL,
USERGROUP_ROLE_roleID INTEGER NOT NULL,

CONSTRAINT USERGROUP_PK 
	PRIMARY KEY (USERGROUP_groupID),
CONSTRAINT USERGROUP_scheduleID 
	FOREIGN KEY (USERGROUP_SCHEDULE_scheduleID)
	REFERENCES SCHEDULE (SCHEDULE_scheduleID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT USERGROUP_researchID 
	FOREIGN KEY (USERGROUP_RESEARCH_researchID)
	REFERENCES RESEARCH (RESEARCH_researchID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT USERGROUP_roleID 
	FOREIGN KEY (USERGROUP_ROLE_roleID)
	REFERENCES ROLE (ROLE_roleID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE EVENT (
EVENT_eventID INTEGER NOT NULL AUTO_INCREMENT,
EVENT_title VARCHAR(100) NOT NULL,
EVENT_description VARCHAR(100) NOT NULL,
EVENT_estimatedTime INTEGER NULL,
EVENT_status INTEGER NOT NULL,
EVENT_SCHEDULE_scheduleID INTEGER NOT NULL,
EVENT_EVENTTYPE_eventTypeID INTEGER NOT NULL,
EVENT_EVENTTIME_eventTimeID INTEGER NOT NULL,

CONSTRAINT EVENT_PK 
	PRIMARY KEY (EVENT_eventID),
CONSTRAINT EVENT_scheduleID 
	FOREIGN KEY (EVENT_SCHEDULE_scheduleID)
	REFERENCES SCHEDULE (SCHEDULE_scheduleID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT EVENT_eventTypeID 
	FOREIGN KEY (EVENT_EVENTTYPE_eventTypeID)
	REFERENCES EVENTTYPE (EVENTTYPE_eventTypeID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT EVENT_eventTimeID 
	FOREIGN KEY (EVENT_EVENTTIME_eventTimeID)
	REFERENCES EVENTTIME (EVENTTIME_eventTimeID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE TASK (
TASK_taskID INTEGER NOT NULL AUTO_INCREMENT,
TASK_description VARCHAR(100) NULL,
TASK_content VARCHAR(100) NULL,
TASK_url VARCHAR(100) NULL,
TASK_sequence INTEGER NOT NULL,
TASK_EVENT_eventID INTEGER NOT NULL,
TASK_TASKTYPE_taskTypeID INTEGER NOT NULL,

CONSTRAINT TASK_PK 
	PRIMARY KEY (TASK_taskID),
CONSTRAINT TASK_eventID 
	FOREIGN KEY (TASK_EVENT_eventID)
	REFERENCES EVENT (EVENT_eventID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT TASK_taskTypeID 
	FOREIGN KEY (TASK_TASKTYPE_taskTypeID)
	REFERENCES TASKTYPE (TASKTYPE_taskTypeID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE CHOICE (
CHOICE_choiceID INTEGER NOT NULL AUTO_INCREMENT,
CHOICE_description VARCHAR(100) NULL,
CHOICE_sequence INTEGER NOT NULL,
CHOICE_value INTEGER NOT NULL,
CHOICE_TASK_contains_taskID INTEGER NOT NULL,
CHOICE_TASK_leadsto_taskID INTEGER NULL,

CONSTRAINT CHOICE_PK 
	PRIMARY KEY (CHOICE_choiceID),
CONSTRAINT CHOICE_contains_taskID 
	FOREIGN KEY (CHOICE_TASK_contains_taskID)
	REFERENCES TASK (TASK_taskID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT CHOICE_leadsto_taskID 
	FOREIGN KEY (CHOICE_TASK_contains_taskID)
	REFERENCES TASK (TASK_taskID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE TASKDATA (
TASKDATA_taskDataID INTEGER NOT NULL AUTO_INCREMENT,
TASKDATA_executionTime DATE NOT NULL,
TASKDATA_sequence INTEGER NOT NULL,
TASKDATA_USER_userID INTEGER NOT NULL,
TASKDATA_CHOICE_choiceID INTEGER NOT NULL,

CONSTRAINT TASKDATA_PK 
	PRIMARY KEY (TASKDATA_taskDataID),
CONSTRAINT TASKDATA_userID 
	FOREIGN KEY (TASKDATA_USER_userID)
	REFERENCES USER (USER_userID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT TASKDATA_choiceID 
	FOREIGN KEY (TASKDATA_CHOICE_choiceID)
	REFERENCES CHOICE (CHOICE_choiceID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
)
;


CREATE TABLE BINARYDATA (
BINARYDATA_binarydataID INTEGER NOT NULL AUTO_INCREMENT,
BINARYDATA_sequence INTEGER NOT NULL,
BINARYDATA_TASKDATA_taskDataID INTEGER NOT NULL,

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


CREATE TABLE TEXT (
TEXT_textID INTEGER NOT NULL AUTO_INCREMENT,
TEXT_sequence INTEGER NOT NULL,
TEXT_TASKDATA_taskDataID INTEGER NOT NULL,

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


CREATE TABLE administers (
administers_editTime DATE NOT NULL,
administers_description INTEGER NOT NULL,
administers_USER_userID INTEGER NOT NULL,
administers_EVENT_eventID INTEGER NOT NULL,

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


CREATE TABLE belongs (
belongs_editTime DATE NOT NULL,
belongs_USERGROUP_groupID INTEGER NOT NULL,
belongs_USER_userID INTEGER NOT NULL,

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


insert into RESEARCH (RESEARCH_description, RESEARCH_name) values ('Testitutkimus 1', 'Testitutkimus1');
insert into RESEARCH (RESEARCH_description, RESEARCH_name) values ('Testitutkimus 2', 'Testitutkimus2');

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

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

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

insert into USERGROUP (USERGROUP_name, USERGROUP_description, USERGROUP_editedBy, USERGROUP_editTime, USERGROUP_SCHEDULE_scheduleID, USERGROUP_RESEARCH_researchID, USERGROUP_ROLE_roleID)
values ('Testitutkijaryhma1', 'Testitutkijaryhmä 1', '0', '2012-04-04', '1', '1', '1');
insert into USERGROUP (USERGROUP_name, USERGROUP_description, USERGROUP_editedBy, USERGROUP_editTime, USERGROUP_SCHEDULE_scheduleID, USERGROUP_RESEARCH_researchID, USERGROUP_ROLE_roleID)
values ('Testitutkijaryhma2', 'Testitutkijaryhmä 2', '0', '2012-04-04', '1', '1', '1');
insert into USERGROUP (USERGROUP_name, USERGROUP_description, USERGROUP_editedBy, USERGROUP_editTime, USERGROUP_SCHEDULE_scheduleID, USERGROUP_RESEARCH_researchID, USERGROUP_ROLE_roleID)
values ('Testikayttajaryhma1', 'Testikäyttäjäryhmä 1', '0', '2012-04-04', '2', '1', '2');
insert into USERGROUP (USERGROUP_name, USERGROUP_description, USERGROUP_editedBy, USERGROUP_editTime, USERGROUP_SCHEDULE_scheduleID, USERGROUP_RESEARCH_researchID, USERGROUP_ROLE_roleID)
values ('Testikayttajaryhma2', 'Testikäyttäjäryhmä 2', '0', '2012-04-04', '2', '1', '2');

insert into TASKTYPE (TASKTYPE_description, TASKTYPE_binarydataMin, TASKTYPE_binarydataMax, TASKTYPE_textMin, TASKTYPE_textMax) values ('Teksti', '0', '0', '0', '0');
insert into TASKTYPE (TASKTYPE_description, TASKTYPE_binarydataMin, TASKTYPE_binarydataMax, TASKTYPE_textMin, TASKTYPE_textMax) values ('Radio', '0', '0', '0', '0');
insert into TASKTYPE (TASKTYPE_description, TASKTYPE_binarydataMin, TASKTYPE_binarydataMax, TASKTYPE_textMin, TASKTYPE_textMax) values ('Sensori', '0', '0', '0', '0');

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

insert into EVENTTIME (EVENTTIME_absolute, EVENTTIME_day) values ('0', '3');
insert into EVENTTIME (EVENTTIME_absolute, EVENTTIME_day) values ('0', '5');
insert into EVENTTIME (EVENTTIME_absolute, EVENTTIME_day) values ('0', '7');

insert into EVENT (EVENT_title, EVENT_description, EVENT_estimatedTime, EVENT_status, EVENT_SCHEDULE_scheduleID, EVENT_EVENTTYPE_eventTypeID, EVENT_EVENTTIME_eventTimeID)
values ('Mielikuvaharjoitus', 'Seuraava tapahtuma jumppaa älynystyröitäsi.', '5', 'Scheduled', '2', '3', '1');
insert into EVENT (EVENT_title, EVENT_description, EVENT_estimatedTime, EVENT_status, EVENT_SCHEDULE_scheduleID, EVENT_EVENTTYPE_eventTypeID, EVENT_EVENTTIME_eventTimeID)
values ('Kävely', 'Seuraava tapahtuma jumppaa jalkojasi.', '60', 'Scheduled', '2', '1', '2');
insert into EVENT (EVENT_title, EVENT_description, EVENT_estimatedTime, EVENT_status, EVENT_SCHEDULE_scheduleID, EVENT_EVENTTYPE_eventTypeID, EVENT_EVENTTIME_eventTimeID)
values ('Kysely', 'Hyvinvointikysely.', '10', 'Scheduled', '2', '2', '3');

insert into TASK (TASK_description, TASK_content, TASK_url, TASK_sequence, TASK_EVENT_eventID, TASK_TASKTYPE_taskTypeID) 
values ('Lue seuraava teksti.', 'Ajattele punaista omenaa...', NULL, '1', '1', '1');
insert into TASK (TASK_description, TASK_content, TASK_url, TASK_sequence, TASK_EVENT_eventID, TASK_TASKTYPE_taskTypeID) 
values ('Mitä mieltä olet juuri lukemastasi tekstistä?', NULL, NULL, '2', '1', '2');
insert into TASK (TASK_description, TASK_content, TASK_url, TASK_sequence, TASK_EVENT_eventID, TASK_TASKTYPE_taskTypeID) 
values ('Tapahtuma päättyi.', NULL, NULL, '3', '1', '1');

insert into TASK (TASK_description, TASK_content, TASK_url, TASK_sequence, TASK_EVENT_eventID, TASK_TASKTYPE_taskTypeID) 
values ('Lähde kävelylle.', 'Paina painiketta käynnistääksesi sen ja sen.', NULL, '1', '2', '1');
insert into TASK (TASK_description, TASK_content, TASK_url, TASK_sequence, TASK_EVENT_eventID, TASK_TASKTYPE_taskTypeID) 
values ('Olet kävelemässä.', 'Paina painiketta lopettaaksesi sen ja sen.', NULL, '2', '2', '3');
insert into TASK (TASK_description, TASK_content, TASK_url, TASK_sequence, TASK_EVENT_eventID, TASK_TASKTYPE_taskTypeID) 
values ('Kävely päättyi.', NULL, NULL, '3', '2', '1');

insert into TASK (TASK_description, TASK_content, TASK_url, TASK_sequence, TASK_EVENT_eventID, TASK_TASKTYPE_taskTypeID) 
values ('Vastaa seuraaviin kysymyksiin.', 'Jappa dappa.', NULL, '1', '3', '1');
insert into TASK (TASK_description, TASK_content, TASK_url, TASK_sequence, TASK_EVENT_eventID, TASK_TASKTYPE_taskTypeID) 
values ('Sapettaako?', NULL, NULL, '2', '3', '2');
insert into TASK (TASK_description, TASK_content, TASK_url, TASK_sequence, TASK_EVENT_eventID, TASK_TASKTYPE_taskTypeID) 
values ('Kysely päättyi.', NULL, NULL, '3', '3', '1');

insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values (NULL,          '0',  '1', '1', '2');
insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values ('Pidin.',      '10', '1', '2', '3');
insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values ('Menetteli.',  '5',  '2', '2', '3');
insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values ('En pitänyt.', '1',  '3', '2', '3');
insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values (NULL,          '0',  '1', '3', NULL);

insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values (NULL, '0', '1', '4', '5');
insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values (NULL, '0', '1', '5', '6');
insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values (NULL, '0', '1', '6', NULL);

insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values (NULL,             '0',  '1', '7', '8');
insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values ('Kyllä.',         '1',  '1', '8', '9');
insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values ('En osaa sanoa.', '5',  '2', '8', '9');
insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values ('Ei.',            '10', '3', '8', '9');
insert into CHOICE (CHOICE_description, CHOICE_value, CHOICE_sequence, CHOICE_TASK_contains_taskID, CHOICE_TASK_leadsto_taskID) values (NULL,             '0',  '1', '9', NULL);

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

insert into administers (administers_editTime, administers_description, administers_USER_userID, administers_EVENT_eventID) values ('2012-04-04', 'Täyteteksti', '1', '1');
insert into administers (administers_editTime, administers_description, administers_USER_userID, administers_EVENT_eventID) values ('2012-04-04', 'Täyteteksti', '1', '2');
insert into administers (administers_editTime, administers_description, administers_USER_userID, administers_EVENT_eventID) values ('2012-04-04', 'Täyteteksti', '1', '3');