ALTER TABLE `tpm_user_stories` ADD COLUMN `acceptingUserId` INTEGER AFTER `name`;
ALTER TABLE `tpm_user_stories` ADD COLUMN `acceptanceStatus` INTEGER AFTER `name`;
ALTER TABLE `tpm_user_stories` ADD COLUMN `ownerId` INTEGER;

alter table tpm_user_stories 
    add index FK1EC53FEDAA149CCB (acceptingUserId), 
    add constraint FK1EC53FEDAA149CCB 
    foreign key (acceptingUserId) 
    references tpm_users (id);

alter table tpm_user_stories 
    add index FK1EC53FED61976F59 (ownerId), 
    add constraint FK1EC53FED61976F59 
    foreign key (ownerId) 
    references tpm_users (id);


-- add active status to projects
alter table tpm_projects add column active bit not null default 1;

-- make project owner a accepting user by default
update tpm_user_stories us, tpm_projects p 
	set us.acceptingUserId = p.ownerId 
	where us.acceptingUserId is null and p.id = us.projectId;

-- create versioninig tables
create table tpm_comments_versions (
    id integer not null,
    revision integer not null,
    revisionType tinyint,
    authorId integer,
    authorName varchar(255),
    body text,
    date datetime,
    primary key (id, revision)
) type=InnoDB;

create table tpm_iterations_versions (
    id integer not null,
    revision integer not null,
    revisionType tinyint,
    duration integer,
    name varchar(128),
    position integer,
    startDate date,
    projectId integer,
    primary key (id, revision)
) type=InnoDB;

create table tpm_priorities_versions (
    id integer not null,
    revision integer not null,
    revisionType tinyint,
    name varchar(32),
    priority integer,
    primary key (id, revision)
) type=InnoDB;

create table tpm_project_comments_versions (
    projectId integer not null,
    commentId integer not null,
    revision integer not null,
    revisionType tinyint,
    primary key (projectId, commentId, revision)
) type=InnoDB;

create table tpm_project_users_versions (
    projectId integer not null,
    userId integer not null,
    revision integer not null,
    revisionType tinyint,
    primary key (projectId, userId, revision)
) type=InnoDB;

 create table tpm_projects_versions (
        id integer not null,
        revision integer not null,
        revisionType tinyint,
        active bit,
        createdAt datetime,
        createdBy varchar(255),
        lastModifiedAt datetime,
        lastModifiedBy varchar(255),
        code varchar(16),
        defaultTask varchar(255),
        description text,
        iterationLength integer,
        name varchar(64),
        startDate date,
        targetFinishDate date,
        ownerId integer,
        primary key (id, revision)
    ) type=InnoDB;

create table tpm_revisions (
    id integer not null auto_increment,
    timestamp bigint not null,
    userId integer,
    userName varchar(64),
    historyEventId integer,
    primary key (id)
) type=InnoDB;

create table tpm_roles_versions (
    id integer not null,
    revision integer not null,
    revisionType tinyint,
    name varchar(64),
    primary key (id, revision)
) type=InnoDB;

create table tpm_tags_versions (
    id integer not null,
    revision integer not null,
    revisionType tinyint,
    value varchar(255),
    primary key (id, revision)
) type=InnoDB;

create table tpm_task_assigned_users_versions (
    taskId integer not null,
    assignedUsersId integer not null,
    revision integer not null,
    revisionType tinyint,
    primary key (taskId, assignedUsersId, revision)
) type=InnoDB;

create table tpm_task_comments_versions (
    taskId integer not null,
    commentId integer not null,
    revision integer not null,
    revisionType tinyint,
    primary key (taskId, commentId, revision)
) type=InnoDB;

create table tpm_tasks_versions (
    id integer not null,
    revision integer not null,
    revisionType tinyint,
    createdAt datetime,
    createdBy varchar(255),
    lastModifiedAt datetime,
    lastModifiedBy varchar(255),
    completedAt date,
    description text,
    name varchar(128),
    startedAt date,
    status integer,
    userStoryId integer,
    primary key (id, revision)
) type=InnoDB;

create table tpm_user_stories_versions (
    id integer not null,
    revision integer not null,
    revisionType tinyint,
    acceptanceStatus integer,
    createdAt datetime,
    createdBy varchar(255),
    lastModifiedAt datetime,
    lastModifiedBy varchar(255),
    color varchar(255),
    description text,
    estimatedEffort double precision,
    name varchar(128),
    acceptingUserId integer,
    iterationId integer,
    ownerId integer,
    priorityId integer,
    projectId integer,
    primary key (id, revision)
) type=InnoDB;

create table tpm_user_story_tags_versions (
    userStoryId integer not null,
    tagId integer not null,
    revision integer not null,
    revisionType tinyint,
    primary key (userStoryId, tagId, revision)
) type=InnoDB;

create table tpm_users_versions (
    id integer not null,
    revision integer not null,
    revisionType tinyint,
    active bit,
    email varchar(255),
    login varchar(32),
    name varchar(64),
    password varchar(32),
    roleId integer,
    primary key (id, revision)
) type=InnoDB;

create table tpm_userstory_comments_versions (
    userStoryId integer not null,
    comment integer not null,
    revision integer not null,
    revisionType tinyint,
    primary key (userStoryId, comment, revision)
) type=InnoDB;

alter table tpm_comments_versions 
    add index FK294FD538BA619436 (revision), 
    add constraint FK294FD538BA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_iterations_versions 
    add index FK52DD6BB6BA619436 (revision), 
    add constraint FK52DD6BB6BA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_priorities_versions 
    add index FK1FCBB52ABA619436 (revision), 
    add constraint FK1FCBB52ABA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_project_comments_versions 
    add index FK604F0392BA619436 (revision), 
    add constraint FK604F0392BA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_project_users_versions 
    add index FKDF00E766BA619436 (revision), 
    add constraint FKDF00E766BA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_projects_versions 
    add index FKA0211172BA619436 (revision), 
    add constraint FKA0211172BA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_revisions 
    add index FK9BDAA6AA256B0D07 (historyEventId), 
    add constraint FK9BDAA6AA256B0D07 
    foreign key (historyEventId) 
    references tpm_history_events (id);

alter table tpm_roles_versions 
    add index FK8C4E26BBA619436 (revision), 
    add constraint FK8C4E26BBA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_tags_versions 
    add index FK8247C1B3BA619436 (revision), 
    add constraint FK8247C1B3BA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_task_assigned_users_versions 
    add index FK9F885CD7BA619436 (revision), 
    add constraint FK9F885CD7BA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_task_comments_versions 
    add index FKCACBFADABA619436 (revision), 
    add constraint FKCACBFADABA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_tasks_versions 
    add index FK748DD0BABA619436 (revision), 
    add constraint FK748DD0BABA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_user_stories_versions 
    add index FK78C30AADBA619436 (revision), 
    add constraint FK78C30AADBA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_user_story_tags_versions 
    add index FK8C97CAD1BA619436 (revision), 
    add constraint FK8C97CAD1BA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_users_versions 
    add index FKC22BE780BA619436 (revision), 
    add constraint FKC22BE780BA619436 
    foreign key (revision) 
    references tpm_revisions (id);

alter table tpm_userstory_comments_versions 
    add index FKC67C8DC3BA619436 (revision), 
    add constraint FKC67C8DC3BA619436 
    foreign key (revision) 
    references tpm_revisions (id);

insert into `tpm_revisions`(id, timestamp, userId, userName) 
select 1, UNIX_TIMESTAMP() * 1000, min(id), '' from tpm_users;

-- comments
insert into `tpm_comments_versions` (id, revision, revisionType, authorId, authorName, body, `date`)
select id, 1, 0, authorId, authorName, body, `date` from tpm_comments;

-- iterations
insert into `tpm_iterations_versions` (id, revision, revisionType, duration, name, position, startDate, projectId)
select id, 1, 0, duration, name, position, startDate, projectId from tpm_iterations;

-- priorities
insert into `tpm_priorities_versions` (id, revision, revisionType, name, priority)
select id, 1, 0, name, priority from tpm_priorities;

-- project comments
insert into `tpm_project_comments_versions` (projectId, commentId, revision, revisionType)
select projectId, commentId, 1, 0 from tpm_project_comments;

-- project users
insert into `tpm_project_users_versions` (projectId, userId, revision, revisionType)
select projectId, userId, 1, 0 from tpm_project_users;

-- projects
insert into `tpm_projects_versions` (id, revision, active, revisionType, createdAt, createdBy, lastModifiedAt, lastModifiedBy, code, defaultTask, description, iterationLength, name, startDate, targetFinishDate, ownerId)
select id, 1, active, 0, createdAt, createdBy, lastModifiedAt, lastModifiedBy, code, defaultTask, description, iterationLength, name, startDate, targetFinishDate, ownerId from tpm_projects;

-- roles 		
insert into `tpm_roles_versions` (id, revision, revisionType, name)
select id, 1, 0, name from tpm_roles;

-- task assigned users
insert into `tpm_task_assigned_users_versions` (taskId, assignedUsersId, revision, revisionType)
select taskId, assignedUsersId, 1, 0 from tpm_task_assigned_users;

-- task comments 
insert into `tpm_task_comments_versions` (taskId, commentId, revision, revisionType)
select taskId, commentId, 1, 0 from tpm_task_comments;

-- tasks
insert into `tpm_tasks_versions` (id, revision, revisionType, createdAt, createdBy, lastModifiedAt, lastModifiedBy, completedAt, description, name, startedAt, status, userStoryId)
select id, 1, 0, createdAt, createdBy, lastModifiedAt, lastModifiedBy, completedAt, description, name, startedAt, status, userStoryId from tpm_tasks;

-- user stories
insert into `tpm_user_stories_versions` (id, revision, revisionType, createdAt, createdBy, lastModifiedAt, lastModifiedBy, color, description, estimatedEffort, name, iterationId, priorityId, projectId, acceptingUserId, acceptanceStatus, ownerId)
select id, 1, 0, createdAt, createdBy, lastModifiedAt, lastModifiedBy, color, description, estimatedEffort, name, iterationId, priorityId, projectId, acceptingUserId, acceptanceStatus, ownerId from tpm_user_stories;

-- user story tags
insert into `tpm_user_story_tags_versions` (userStoryId, tagId, revision, revisionType)
select userStoryId, tagId, 1, 0 from tpm_user_story_tags;

-- users
insert into `tpm_users_versions` (id, revision, revisionType, active, email, login, name, password, roleId)
select id, 1, 0, active, email, login, name, password, roleId from tpm_users;

-- userstory comments
insert into `tpm_userstory_comments_versions` (userStoryId, comment, revision, revisionType)
select userStoryId, comment, 1, 0 from tpm_userstory_comments;

create table tpm_logo (
    id integer not null auto_increment,
    image mediumblob,
    primary key (id)
) type=InnoDB;

insert into `tpm_features` (code, `position`) values
('userStoryManager.acceptance',6);

insert into `tpm_privilege_group_features` (privilegeGroupCode, featureCode) values
('USER_STORIES','userStoryManager.acceptance');


-- beta 2
insert into `tpm_features` (code, `position`) values
('backlogResource.exportData',6);

insert into `tpm_privilege_group_features` (privilegeGroupCode, featureCode) values
('USER_STORIES','backlogResource.exportData');

insert into `tpm_role_features` (roleId, featureCode) select rf.roleId, 'backlogResource.exportData' from `tpm_role_features` rf where rf.featureCode = 'userStoryManager.list';

update `tpm_features` set `position` = 7 where code = 'userStoryManager.acceptance';

alter table `tpm_user_stories` modify column `acceptanceStatus` varchar(255);
alter table `tpm_user_stories_versions` modify column `acceptanceStatus` varchar(255);

update `tpm_user_stories` set `acceptanceStatus` = 'ACCEPTED' where `acceptanceStatus` = '0';
update `tpm_user_stories` set `acceptanceStatus` = 'REJECTED' where `acceptanceStatus` = '1';
update `tpm_user_stories` set `acceptanceStatus` = 'PENDING' where `acceptanceStatus` IS NULL;

update `tpm_user_stories_versions` set `acceptanceStatus` = 'ACCEPTED' where `acceptanceStatus` = '0';
update `tpm_user_stories_versions` set `acceptanceStatus` = 'REJECTED' where `acceptanceStatus` = '1';
update `tpm_user_stories_versions` set `acceptanceStatus` = 'PENDING' where `acceptanceStatus` IS NULL;

alter table `tpm_tasks` modify column `completedAt` datetime;
alter table `tpm_tasks` modify column `startedAt` datetime;

alter table `tpm_tasks_versions` modify column `completedAt` datetime;
alter table `tpm_tasks_versions` modify column `startedAt` datetime;