-- This file is part of SystemTestPortal.
-- Copyright (C) 2017  Institute of Software Technology, University of Stuttgart
--
-- SystemTestPortal is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- SystemTestPortal is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with SystemTestPortal.  If not, see <http://www.gnu.org/licenses/>.

-- +migrate Up

PRAGMA defer_foreign_keys = "1";
PRAGMA foreign_key = "0";

Create TABLE project_roles_new (
  id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL,
  name VARCHAR (255) NOT NULL,
  display_project BIT(1) DEFAULT 1,
  execute BIT(1),
  create_case BIT(1),
  edit_case BIT(1),
  delete_case BIT(1),
  duplicate_case BIT(1),
  assign_case BIT(1),
  create_sequence BIT(1),
  edit_sequence BIT(1),
  delete_sequence BIT(1),
  duplicate_sequence BIT(1),
  assign_sequence BIT(1),
  edit_members BIT(1),
  edit_project BIT(1),
  delete_project BIT(1),
  edit_permissions BIT(1),
  CONSTRAINT uniq_project_id_name UNIQUE (project_id, name)
);

INSERT INTO project_roles_new (
  id,
  project_id,
  name,
  execute,
  create_case,
  edit_case,
  delete_case,
  duplicate_case,
  assign_case,
  create_sequence,
  edit_sequence,
  delete_sequence,
  duplicate_sequence,
  assign_sequence,
  edit_members,
  edit_project,
  delete_project,
  edit_permissions)
SELECT
  id,
  project_id,
  name,
  execute,
  create_case,
  edit_case,
  delete_case,
  duplicate_case,
  assign_case,
  create_sequence,
  edit_sequence,
  delete_sequence,
  duplicate_sequence,
  assign_sequence,
  edit_members,
  edit_project,
  delete_project,
  edit_permissions
FROM project_roles;

UPDATE project_roles_new SET display_project = 1 WHERE display_project IS NULL;

DROP TABLE project_roles;

Create TABLE project_roles (
  id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name VARCHAR (255) NOT NULL,
  display_project BIT(1) DEFAULT 1,
  execute BIT(1),
  create_case BIT(1),
  edit_case BIT(1),
  delete_case BIT(1),
  duplicate_case BIT(1),
  assign_case BIT(1),
  create_sequence BIT(1),
  edit_sequence BIT(1),
  delete_sequence BIT(1),
  duplicate_sequence BIT(1),
  assign_sequence BIT(1),
  edit_members BIT(1),
  edit_project BIT(1),
  delete_project BIT(1),
  edit_permissions BIT(1),
  CONSTRAINT uniq_project_id_name UNIQUE (project_id, name)
);
INSERT INTO project_roles SELECT * FROM  project_roles_new;

DROP TABLE project_roles_new;

PRAGMA defer_foreign_keys = "0";
PRAGMA foreign_key = "1";

-- +migrate Down
