create table Issue_new (
   id integer not null auto_increment,
   reporter integer not null,
   description text,
   developer integer,
   name varchar(64),
   priority integer not null,
   project integer not null,
   status integer not null,
   type integer not null,
   primary key (id)
);

create table User_new (
   id integer not null auto_increment,
   administrator bit not null,
   email varchar(128),
   enabled bit not null,
   guest bit not null,
   name varchar(64) unique,
   password varchar(64),
   primary key (id)
);

create table Project_new (
   id integer not null auto_increment,
   enabled bit not null,
   name varchar(64) unique,
   primary key (id)
);

create table HistoryEntry_new (
   id integer not null auto_increment,
   comment text,
   date datetime,
   editor integer not null,
   status integer not null,
   issue integer,
   position integer,
   primary key (id)
);

create table Role_new (
   id integer not null auto_increment,
   developer bit not null,
   manager bit not null,
   reporter bit not null,
   user integer,
   project integer,
   primary key (id)
);

alter table Issue_new add index Issue_reporter (reporter), add constraint Issue_reporter foreign key (reporter) references User_new (id);
alter table Issue_new add index Issue_project (project), add constraint Issue_project foreign key (project) references Project_new (id);
alter table Issue_new add index Issue_developer (developer), add constraint Issue_developer foreign key (developer) references User_new (id);
alter table HistoryEntry_new add index Issue_historyEntries (issue), add constraint Issue_historyEntries foreign key (issue) references Issue_new (id);
alter table HistoryEntry_new add index Comment_editor (editor), add constraint Comment_editor foreign key (editor) references User_new (id);
alter table Role_new add index User_roles (user), add constraint User_roles foreign key (user) references User_new (id);
alter table Role_new add index Role_project (project), add constraint Role_project foreign key (project) references Project_new (id);

update TASK set CREATED_USER_ID = null where CREATED_USER_ID = 0;
update TASK set ASSIGNED_USER_ID = null where ASSIGNED_USER_ID = 0;
update TASK set PROJECT_ID = null where PROJECT_ID = 0;

insert into Project_new (id, enabled, name)
	select PROJECT_ID, ENABLED, NAME from PROJECT;

# Note! Since the password hashing differs, the users must set a new password!
# The migration script resets the password to 'password'!

insert into User_new (id, administrator, email, enabled, guest, name, password)
	select USER_ID, 1, EMAIL, ENABLED, 0, NAME, '5f4dcc3b5aa765d61d8327deb882cf99' from USER where TYPE <= 2;
insert into User_new (id, administrator, email, enabled, guest, name, password)
	select USER_ID, 0, EMAIL, ENABLED, 0, NAME, '5f4dcc3b5aa765d61d8327deb882cf99' from USER where TYPE > 2;

insert into Role_new (manager, developer, reporter, project, user)
	select 1, 1, 1, P.PROJECT_ID, U.USER_ID from PROJECT P, USER U where U.TYPE <= 3;
insert into Role_new (manager, developer, reporter, project, user)
	select 0, 1, 1, P.PROJECT_ID, U.USER_ID from PROJECT P, USER U where U.TYPE = 4;
insert into Role_new (manager, developer, reporter, project, user)
	select 0, 0, 1, P.PROJECT_ID, U.USER_ID from PROJECT P, USER U where U.TYPE = 5;

insert into Issue_new (id, reporter, description, developer, name, priority, project, status, type)
	select TASK_ID, CREATED_USER_ID, DESCRIPTION, ASSIGNED_USER_ID, NAME, PRIORITY, PROJECT_ID, STATUS, TYPE from TASK;
insert into HistoryEntry_new (comment, date, editor, status, issue, position)
	select COMMENTS, now(), CREATED_USER_ID, STATUS, TASK_ID, 0 from TASK;

drop table TASK;
drop table USER;
drop table PROJECT;

alter table Project_new rename to Project;
alter table User_new rename to User;
alter table Role_new rename to Role;
alter table Issue_new rename to Issue;
alter table HistoryEntry_new rename to HistoryEntry;
