1. Table: users;
CREATE TABLE IF NOT EXISTS users (email VARCHAR(255) PRIMARY KEY, password VARCHAR(255) NOT NULL, enabled BOOLEAN NOT NULL, name VARCHAR(255) NOT NULL, created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

INSERT INTO users (email, password, enabled, name) VALUES ('admin@vpcat.com', 'adminonly', true, 'AT admin');
INSERT INTO users (email, password, enabled, name) VALUES ('visitor@vpcat.com', 'atonly', true, 'Visitor');

2. Table: test_cycles 
CREATE TABLE IF NOT EXISTS test_cycles (id IDENTITY PRIMARY KEY, suite VARCHAR(255) NOT NULL, module VARCHAR(255) NOT NULL, environment VARCHAR(255) NOT NULL, comments VARCHAR(4048), execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

INSERT INTO test_cycles (suite, module, environment) VALUES ('ts-suite-smoke', 'TS', 'N1');

3. Table: case_status
CREATE TABLE IF NOT EXISTS case_status (id INTEGER PRIMARY KEY, value VARCHAR(255) NOT NULL);

INSERT INTO case_status (id, value) VALUES (1, 'PASSED');
INSERT INTO case_status (id, value) VALUES (20, 'FAILED');
INSERT INTO case_status (id, value) VALUES (30, 'SKIPPED');


4. Table: case_results
CREATE TABLE IF NOT EXISTS case_results (class VARCHAR(512) NOT NULL, method VARCHAR(512) NOT NULL, test_cycle_id INTEGER NOT NULL, status INTEGER NOT NULL, test_ids VARCHAR(512), title VARCHAR(512), sub_module VARCHAR(512), duration INTEGER, description VARCHAR(2024), records VARCHAR(4048), clue TEXT, logs TEXT, screenshot_base64 TEXT, FOREIGN KEY(test_cycle_id) REFERENCES test_cycles(id), FOREIGN KEY(status) REFERENCES case_status(id), PRIMARY KEY(class, method, test_cycle_id)); 

INSERT INTO case_results (class, method, test_cycle_id, status, test_ids, title, sub_module, duration, description, records, clue, logs, screenshot_base64) VALUES ('classA', 'method1', 1, 1, 'testidsss', 'title', 'submodule', 2, 'description....', 'records....', 'clue....', 'logs.....', 'screenshot_base64');

5. View: case_results_join_test_cycles, test_cycle_results_total, test_cycle_results_passed, test_cycle_results_failed, test_cycle_results_skipped, test_cycle_results
CREATE VIEW IF NOT EXISTS case_results_join_test_cycles AS SELECT test_cycles.id AS test_cycle_id, test_cycles.suite, test_cycles.module, test_cycles.environment, test_cycles.execution_time, test_cycles.comments, case_results.status AS case_status, case_results.duration AS case_duration FROM test_cycles, case_results WHERE test_cycles.id = case_results.test_cycle_id;
CREATE VIEW IF NOT EXISTS test_cycle_results_total AS SELECT test_cycle_id, suite, module, environment, SUM(case_duration) AS total_duration, execution_time, comments, count(*) AS total_cases from case_results_join_test_cycles GROUP BY test_cycle_id;
CREATE VIEW IF NOT EXISTS test_cycle_results_passed AS SELECT test_cycle_id, count(*) AS passed_cases from case_results_join_test_cycles WHERE case_status = 1 GROUP BY test_cycle_id;
CREATE VIEW IF NOT EXISTS test_cycle_results_failed AS SELECT test_cycle_id, count(*) AS failed_cases from case_results_join_test_cycles WHERE case_status = 20 GROUP BY test_cycle_id;
CREATE VIEW IF NOT EXISTS test_cycle_results_skipped AS SELECT test_cycle_id, count(*) AS skipped_cases from case_results_join_test_cycles WHERE case_status = 30 GROUP BY test_cycle_id;
CREATE VIEW IF NOT EXISTS test_cycle_results AS SELECT total_pass_fail.*,  skipped_cases FROM (SELECT total_pass.*, failed_cases FROM (SELECT test_cycle_results_total.*,  passed_cases FROM test_cycle_results_total LEFT OUTER JOIN test_cycle_results_passed ON test_cycle_results_total.test_cycle_id = test_cycle_results_passed.test_cycle_id) AS total_pass LEFT OUTER JOIN test_cycle_results_failed ON total_pass.test_cycle_id = test_cycle_results_failed.test_cycle_id) AS total_pass_fail LEFT OUTER JOIN test_cycle_results_skipped ON total_pass_fail.test_cycle_id = test_cycle_results_skipped.test_cycle_id;

