-- 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 foreign_keys = "0";
PRAGMA defer_foreign_keys = "1";

ALTER TABLE test_case_versions RENAME TO test_case_versions_old;
ALTER TABLE test_sequence_versions RENAME TO test_sequence_versions_old;

CREATE TABLE test_case_versions (
    id INTEGER PRIMARY KEY,
    test_case_id INTEGER NOT NULL REFERENCES test_cases(id) ON DELETE CASCADE,
    version_nr INTEGER NOT NULL,
    description VARCHAR(255),
    duration INTEGER,
    message VARCHAR(255),
    is_minor BIT(1),
    creation_date TIMESTAMP,
    CONSTRAINT uniq_test_case_row_id_version_nr UNIQUE (test_case_id, version_nr)
);

CREATE TABLE test_sequence_versions (
    id INTEGER PRIMARY KEY,
    test_sequence_id INTEGER NOT NULL REFERENCES test_sequences(id) ON DELETE CASCADE,
    version_nr INTEGER NOT NULL,
    description VARCHAR(255),
    message VARCHAR(255),
    is_minor BIT(1),
    creation_date TIMESTAMP,
    CONSTRAINT uniq_test_sequence_row_id_version_nr UNIQUE (test_sequence_id, version_nr)
);

ALTER TABLE test_steps RENAME TO test_steps_old;
ALTER TABLE test_case_version_testers RENAME TO test_case_version_testers_old;
ALTER TABLE test_case_version_project_variants RENAME TO test_case_version_project_variants_old;
ALTER TABLE test_sequence_version_test_cases RENAME TO test_sequence_version_test_cases_old;
ALTER TABLE test_sequence_version_testers RENAME TO test_sequence_version_testers_old;
ALTER TABLE test_case_preconditions RENAME TO test_case_preconditions_old;
ALTER TABLE test_sequence_preconditions RENAME TO test_sequence_preconditions_old;

CREATE TABLE test_case_preconditions(
    id INTEGER PRIMARY KEY,
    test_version INTEGER NOT NULL REFERENCES test_case_versions(id) ON DELETE CASCADE,
    content VARCHAR(255)
);

CREATE TABLE test_sequence_preconditions(
    id INTEGER PRIMARY KEY,
    test_version INTEGER NOT NULL REFERENCES test_sequence_versions(id) ON DELETE CASCADE,
    content VARCHAR(255)
);

CREATE TABLE test_sequence_version_testers (
    id INTEGER PRIMARY KEY,
    test_sequence_version INTEGER NOT NULL REFERENCES test_sequence_versions(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT uniq_project_id_user_id UNIQUE (test_sequence_version, user_id)
);
CREATE TABLE test_sequence_version_test_cases (
    test_sequence_version INTEGER NOT NULL REFERENCES test_sequence_versions(id) ON DELETE CASCADE,
    test_case INTEGER NOT NULL REFERENCES test_cases(id),
    case_index INTEGER NOT NULL,
    CONSTRAINT uniq_test_sequence_version_test_case_case_index UNIQUE (test_sequence_version, test_case)
);
CREATE TABLE test_case_version_project_variants (
    id INTEGER PRIMARY KEY,
    test_case_version INTEGER NOT NULL REFERENCES test_case_versions(id) ON DELETE CASCADE,
    project_variant INTEGER NOT NULL REFERENCES project_variants(id),
    CONSTRAINT uniq_test_case_version_project_variant UNIQUE (test_case_version, project_variant)
);
CREATE TABLE test_case_version_testers (
    id INTEGER PRIMARY KEY,
    test_case_version INTEGER NOT NULL REFERENCES test_case_versions(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT uniq_project_id_user_id UNIQUE (test_case_version, user_id)
);

CREATE TABLE test_steps (
    id INTEGER PRIMARY KEY,
    test_case_version_id INTEGER NOT NULL REFERENCES test_case_versions(id) ON DELETE CASCADE,
    step_index INTEGER NOT NULL,
    action VARCHAR(255),
    expected_result VARCHAR(255),
    CONSTRAINT uniq_test_case_version_row_id_index UNIQUE (test_case_version_id, step_index)
);

INSERT INTO test_steps SELECT * FROM test_steps_old;
INSERT INTO test_case_version_testers  SELECT * FROM test_case_version_testers_old;
INSERT INTO test_case_version_project_variants  SELECT * FROM test_case_version_project_variants_old;
INSERT INTO test_sequence_version_test_cases  SELECT * FROM test_sequence_version_test_cases_old;
INSERT INTO test_sequence_version_testers  SELECT * FROM test_sequence_version_testers_old;
INSERT INTO test_case_preconditions SELECT * FROM test_case_preconditions_old;
INSERT INTO test_sequence_preconditions SELECT * FROM test_sequence_preconditions_old;

DROP TABLE test_case_version_testers_old;
DROP TABLE test_case_version_project_variants_old;
DROP TABLE test_sequence_version_test_cases_old;
DROP TABLE test_sequence_version_testers_old;
DROP TABLE test_case_preconditions_old;
DROP TABLE test_sequence_preconditions_old;


INSERT INTO test_case_versions SELECT id, test_case_id, version_nr, description, duration, message, is_minor, creation_date FROM test_case_versions_old;
INSERT INTO test_sequence_versions SELECT id, test_sequence_id, version_nr, description, message, is_minor, creation_date FROM test_sequence_versions_old;

DROP TABLE test_case_versions_old;
DROP TABLE test_sequence_versions_old;

PRAGMA foreign_keys = "1";
PRAGMA defer_foreign_keys = "0";


-- +migrate Down

PRAGMA foreign_keys = OFF;
PRAGMA defer_foreign_keys = "1";

ALTER TABLE test_case_versions RENAME TO test_case_versions_old;
ALTER TABLE test_sequence_versions RENAME TO test_sequence_versions_old;


CREATE TABLE test_case_versions (
    id INTEGER PRIMARY KEY,
    test_case_id INTEGER NOT NULL REFERENCES test_cases(id) ON DELETE CASCADE,
    version_nr INTEGER NOT NULL,
    description VARCHAR(255),
    preconditions VARCHAR(255),
    duration INTEGER,
    message VARCHAR(255),
    is_minor BIT(1),
    creation_date TIMESTAMP,
    CONSTRAINT uniq_test_case_row_id_version_nr UNIQUE (test_case_id, version_nr)
);


CREATE TABLE test_sequence_versions (
    id INTEGER PRIMARY KEY,
    test_sequence_id INTEGER NOT NULL REFERENCES test_sequences(id) ON DELETE CASCADE,
    version_nr INTEGER NOT NULL,
    description VARCHAR(255),
    preconditions VARCHAR(255),
    message VARCHAR(255),
    is_minor BIT(1),
    creation_date TIMESTAMP,
    CONSTRAINT uniq_test_sequence_row_id_version_nr UNIQUE (test_sequence_id, version_nr)
);

ALTER TABLE test_steps RENAME TO test_steps_old;
ALTER TABLE test_case_version_testers RENAME TO test_case_version_testers_old;
ALTER TABLE test_case_version_project_variants RENAME TO test_case_version_project_variants_old;
ALTER TABLE test_sequence_version_test_cases RENAME TO test_sequence_version_test_cases_old;
ALTER TABLE test_sequence_version_testers RENAME TO test_sequence_version_testers_old;

CREATE TABLE test_sequence_version_testers (
    id INTEGER PRIMARY KEY,
    test_sequence_version INTEGER NOT NULL REFERENCES test_sequence_versions(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT uniq_project_id_user_id UNIQUE (test_sequence_version, user_id)
);
CREATE TABLE test_sequence_version_test_cases (
    test_sequence_version INTEGER NOT NULL REFERENCES test_sequence_versions(id) ON DELETE CASCADE,
    test_case INTEGER NOT NULL REFERENCES test_cases(id),
    case_index INTEGER NOT NULL,
    CONSTRAINT uniq_test_sequence_version_test_case_case_index UNIQUE (test_sequence_version, test_case)
);
CREATE TABLE test_case_version_project_variants (
    id INTEGER PRIMARY KEY,
    test_case_version INTEGER NOT NULL REFERENCES test_case_versions(id) ON DELETE CASCADE,
    project_variant INTEGER NOT NULL REFERENCES project_variants(id),
    CONSTRAINT uniq_test_case_version_project_variant UNIQUE (test_case_version, project_variant)
);
CREATE TABLE test_case_version_testers (
    id INTEGER PRIMARY KEY,
    test_case_version INTEGER NOT NULL REFERENCES test_case_versions(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT uniq_project_id_user_id UNIQUE (test_case_version, user_id)
);

CREATE TABLE test_steps (
    id INTEGER PRIMARY KEY,
    test_case_version_id INTEGER NOT NULL REFERENCES test_case_versions(id) ON DELETE CASCADE,
    step_index INTEGER NOT NULL,
    action VARCHAR(255),
    expected_result VARCHAR(255),
    CONSTRAINT uniq_test_case_version_row_id_index UNIQUE (test_case_version_id, step_index)
);

INSERT INTO test_steps SELECT * FROM test_steps_old;
INSERT INTO test_case_version_testers  SELECT * FROM test_case_version_testers_old;
INSERT INTO test_case_version_project_variants  SELECT * FROM test_case_version_project_variants_old;
INSERT INTO test_sequence_version_test_cases  SELECT * FROM test_sequence_version_test_cases_old;
INSERT INTO test_sequence_version_testers  SELECT * FROM test_sequence_version_testers_old;


DROP TABLE test_case_version_testers_old;
DROP TABLE test_case_version_project_variants_old;
DROP TABLE test_sequence_version_test_cases_old;
DROP TABLE test_sequence_version_testers_old;

INSERT INTO test_case_versions(id, test_case_id, version_nr, description, duration, message, is_minor, creation_date) SELECT id, test_case_id, version_nr, description, duration, message, is_minor, creation_date FROM test_case_versions_old ;
INSERT INTO test_sequence_versions(id, test_sequence_id, version_nr, description, duration, message, is_minor, creation_date) SELECT id, test_sequence_id, version_nr, description, message, is_minor, creation_date FROM test_sequence_versions_old;

DROP TABLE test_case_versions_old;
DROP TABLE test_sequence_versions_old;

PRAGMA foreign_keys = ON;
PRAGMA defer_foreign_keys = "0";