DROP TABLE IF EXISTS run_iterations CASCADE;
DROP TABLE IF EXISTS test_runs CASCADE;
DROP TABLE IF EXISTS test_configurations CASCADE;
DROP TYPE IF EXISTS test_configuration_type CASCADE;
DROP TABLE IF EXISTS aggregators CASCADE;
DROP TABLE IF EXISTS builds CASCADE;
DROP TABLE IF EXISTS committers CASCADE;
DROP TABLE IF EXISTS commits CASCADE;
DROP TABLE IF EXISTS build_commits CASCADE;
DROP TABLE IF EXISTS commit_ownerships CASCADE;
DROP TABLE IF EXISTS build_workers CASCADE;
DROP TABLE IF EXISTS builders CASCADE;
DROP TABLE IF EXISTS repositories CASCADE;
DROP TABLE IF EXISTS platforms CASCADE;
DROP TABLE IF EXISTS platform_groups CASCADE;
DROP TABLE IF EXISTS test_metrics CASCADE;
DROP TABLE IF EXISTS tests CASCADE;
DROP TABLE IF EXISTS reports CASCADE;
DROP TABLE IF EXISTS tracker_repositories CASCADE;
DROP TABLE IF EXISTS bug_trackers CASCADE;
DROP TABLE IF EXISTS task_commits CASCADE;
DROP TABLE IF EXISTS analysis_tasks CASCADE;
DROP TABLE IF EXISTS analysis_strategies CASCADE;
DROP TYPE IF EXISTS analysis_task_result_type CASCADE;
DROP TABLE IF EXISTS build_triggerables CASCADE;
DROP TABLE IF EXISTS triggerable_configurations CASCADE;
DROP TABLE IF EXISTS triggerable_repository_groups CASCADE;
DROP TABLE IF EXISTS triggerable_repositories CASCADE;
DROP TABLE IF EXISTS uploaded_files CASCADE;
DROP TABLE IF EXISTS bugs CASCADE;
DROP TABLE IF EXISTS analysis_test_groups CASCADE;
DROP TYPE IF EXISTS analysis_test_group_repetition_type CASCADE;
DROP TABLE IF EXISTS commit_sets CASCADE;
DROP TABLE IF EXISTS commit_set_items CASCADE;
DROP TABLE IF EXISTS build_requests CASCADE;
DROP TYPE IF EXISTS build_request_status_type CASCADE;


CREATE TABLE platform_groups (
    platformgroup_id serial PRIMARY KEY,
    platformgroup_name varchar(64) NOT NULL,
    CONSTRAINT platform_group_name_must_be_unique UNIQUE (platformgroup_name));

CREATE TABLE platforms (
    platform_id serial PRIMARY KEY,
    platform_name varchar(64) NOT NULL,
    platform_group integer REFERENCES platform_groups DEFAULT NULL,
    platform_hidden boolean NOT NULL DEFAULT FALSE);

CREATE TABLE repositories (
    repository_id serial PRIMARY KEY,
    repository_owner integer REFERENCES repositories ON DELETE CASCADE,
    repository_name varchar(64) NOT NULL,
    repository_url varchar(1024),
    repository_blame_url varchar(1024));

CREATE UNIQUE INDEX repository_name_owner_unique_index ON repositories (repository_owner, repository_name)
    WHERE repository_owner IS NOT NULL;
CREATE UNIQUE INDEX repository_name_unique_index ON repositories (repository_name)
    WHERE repository_owner IS NULL;

CREATE TABLE bug_trackers (
    tracker_id serial PRIMARY KEY,
    tracker_name varchar(64) NOT NULL,
    tracker_bug_url varchar(1024),
    tracker_new_bug_url varchar(1024));

CREATE TABLE tracker_repositories (
    tracrepo_tracker integer NOT NULL REFERENCES bug_trackers ON DELETE CASCADE,
    tracrepo_repository integer NOT NULL REFERENCES repositories ON DELETE CASCADE);

CREATE TABLE builders (
    builder_id serial PRIMARY KEY,
    builder_name varchar(256) NOT NULL UNIQUE,
    builder_password_hash character(64),
    builder_build_url varchar(1024));

CREATE TABLE build_workers (
    worker_id serial PRIMARY KEY,
    worker_name varchar(64) NOT NULL UNIQUE,
    worker_password_hash character(64));

CREATE TABLE builds (
    build_id serial PRIMARY KEY,
    build_builder integer REFERENCES builders ON DELETE CASCADE,
    build_worker integer REFERENCES build_workers ON DELETE CASCADE,
    build_tag varchar(64) NOT NULL,
    build_time timestamp NOT NULL,
    build_latest_revision timestamp,
    CONSTRAINT builder_build_time_tuple_must_be_unique UNIQUE(build_builder, build_tag, build_time));
CREATE INDEX build_builder_index ON builds(build_builder);

CREATE TABLE committers (
    committer_id serial PRIMARY KEY,
    committer_repository integer NOT NULL REFERENCES repositories ON DELETE CASCADE,
    committer_account varchar(320) NOT NULL,
    committer_name varchar(128),
    CONSTRAINT committer_in_repository_must_be_unique UNIQUE(committer_repository, committer_account));
CREATE INDEX committer_account_index ON committers(committer_account);
CREATE INDEX committer_name_index ON committers(committer_name);

CREATE TABLE commits (
    commit_id serial PRIMARY KEY,
    commit_repository integer NOT NULL REFERENCES repositories ON DELETE CASCADE,
    commit_revision varchar(64) NOT NULL,
    commit_revision_identifier varchar(64) DEFAULT NULL,
    commit_previous_commit integer REFERENCES commits ON DELETE CASCADE,
    commit_time timestamp,
    commit_order bigint,
    commit_committer integer REFERENCES committers ON DELETE CASCADE,
    commit_message text,
    commit_reported boolean NOT NULL DEFAULT FALSE,
    commit_testability varchar(128) DEFAULT NULL,
    CONSTRAINT commit_in_repository_must_be_unique UNIQUE(commit_repository, commit_revision),
    CONSTRAINT commit_string_identifier_in_repository_must_be_unique UNIQUE(commit_repository, commit_revision_identifier));
CREATE INDEX commit_time_index ON commits(commit_time);
CREATE INDEX commit_order_index ON commits(commit_order);

CREATE TABLE commit_ownerships (
    commit_owner integer NOT NULL REFERENCES commits ON DELETE CASCADE,
    commit_owned integer NOT NULL REFERENCES commits ON DELETE CASCADE,
    PRIMARY KEY (commit_owner, commit_owned)
);

CREATE TABLE build_commits (
    commit_build integer NOT NULL REFERENCES builds ON DELETE CASCADE,
    build_commit integer NOT NULL REFERENCES commits ON DELETE CASCADE,
    PRIMARY KEY (commit_build, build_commit));

CREATE TABLE aggregators (
    aggregator_id serial PRIMARY KEY,
    aggregator_name varchar(64),
    aggregator_definition text);

CREATE TABLE tests (
    test_id serial PRIMARY KEY,
    test_name varchar(255) NOT NULL,
    test_parent integer REFERENCES tests ON DELETE CASCADE,
    test_url varchar(1024) DEFAULT NULL,
    CONSTRAINT parent_test_must_be_unique UNIQUE(test_parent, test_name));

CREATE TABLE test_metrics (
    metric_id serial PRIMARY KEY,
    metric_test integer NOT NULL REFERENCES tests ON DELETE CASCADE,
    metric_name varchar(64) NOT NULL,
    metric_aggregator integer REFERENCES aggregators ON DELETE CASCADE);

CREATE TYPE test_configuration_type as ENUM ('current', 'baseline', 'target');
CREATE TABLE test_configurations (
    config_id serial PRIMARY KEY,
    config_metric integer NOT NULL REFERENCES test_metrics ON DELETE CASCADE,
    config_platform integer NOT NULL REFERENCES platforms ON DELETE CASCADE,
    config_type test_configuration_type NOT NULL,
    config_is_in_dashboard boolean NOT NULL DEFAULT FALSE,
    config_runs_last_modified timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
    CONSTRAINT configuration_must_be_unique UNIQUE(config_metric, config_platform, config_type));
CREATE INDEX config_platform_index ON test_configurations(config_platform);

CREATE TABLE test_runs (
    run_id serial PRIMARY KEY,
    run_config integer NOT NULL REFERENCES test_configurations ON DELETE CASCADE,
    run_build integer NOT NULL REFERENCES builds ON DELETE CASCADE,
    run_iteration_count_cache smallint,
    run_mean_cache double precision,
    run_sum_cache double precision,
    run_square_sum_cache double precision,
    run_marked_outlier boolean NOT NULL DEFAULT FALSE,
    CONSTRAINT test_config_build_must_be_unique UNIQUE(run_config, run_build));
CREATE INDEX run_config_index ON test_runs(run_config);
CREATE INDEX run_build_index ON test_runs(run_build);

CREATE TABLE run_iterations (
    iteration_run integer NOT NULL REFERENCES test_runs ON DELETE CASCADE,
    iteration_order smallint NOT NULL CHECK(iteration_order >= 0),
    iteration_group smallint CHECK(iteration_group >= 0),
    iteration_value double precision,
    iteration_relative_time float,
    PRIMARY KEY (iteration_run, iteration_order));

CREATE OR REPLACE FUNCTION update_config_last_modified() RETURNS TRIGGER AS $update_config_last_modified$
    BEGIN
        IF TG_OP != 'DELETE' THEN
            UPDATE test_configurations SET config_runs_last_modified = (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') WHERE config_id = NEW.run_config;
        ELSE
            UPDATE test_configurations SET config_runs_last_modified = (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') WHERE config_id = OLD.run_config;
        END IF;
        RETURN NULL;
    END;
$update_config_last_modified$ LANGUAGE plpgsql;

CREATE TRIGGER update_config_last_modified AFTER INSERT OR UPDATE OR DELETE ON test_runs
    FOR EACH ROW EXECUTE PROCEDURE update_config_last_modified();

CREATE TABLE reports (
    report_id serial PRIMARY KEY,
    report_builder integer NOT NULL REFERENCES builders ON DELETE RESTRICT,
    report_worker integer REFERENCES build_workers ON DELETE RESTRICT,
    report_build_tag varchar(64),
    report_build integer REFERENCES builds,
    report_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
    report_committed_at timestamp,
    report_content text,
    report_failure varchar(64),
    report_failure_details text);

CREATE TABLE analysis_strategies (
    strategy_id serial PRIMARY KEY,
    strategy_name varchar(64) NOT NULL);

CREATE TYPE analysis_task_result_type as ENUM ('progression', 'regression', 'unchanged', 'inconclusive');
CREATE TABLE analysis_tasks (
    task_id serial PRIMARY KEY,
    task_name varchar(256) NOT NULL,
    task_author varchar(256),
    task_segmentation integer REFERENCES analysis_strategies,
    task_test_range integer REFERENCES analysis_strategies,
    task_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
    task_platform integer REFERENCES platforms,
    task_metric integer REFERENCES test_metrics,
    task_start_run integer REFERENCES test_runs,
    task_start_run_time timestamp,
    task_end_run integer REFERENCES test_runs,
    task_end_run_time timestamp,
    task_result analysis_task_result_type,
    task_needed boolean,
    CONSTRAINT analysis_task_should_be_unique_for_range UNIQUE(task_start_run, task_end_run),
    CONSTRAINT analysis_task_must_be_associated_with_run_or_be_custom
        CHECK ((task_start_run IS NULL AND task_start_run_time IS NULL
                AND task_end_run IS NULL AND task_end_run_time IS NULL
                AND task_platform IS NULL AND task_metric IS NULL)
            OR (task_start_run IS NOT NULL AND task_start_run_time IS NOT NULL
                AND task_end_run IS NOT NULL AND task_end_run_time IS NOT NULL
                AND task_platform IS NOT NULL AND task_metric IS NOT NULL)));

CREATE TABLE task_commits (
    taskcommit_task integer NOT NULL REFERENCES analysis_tasks ON DELETE CASCADE,
    taskcommit_commit integer NOT NULL REFERENCES commits ON DELETE CASCADE,
    taskcommit_is_fix boolean NOT NULL,
    CONSTRAINT task_commit_must_be_unique UNIQUE(taskcommit_task, taskcommit_commit));

CREATE TABLE bugs (
    bug_id serial PRIMARY KEY,
    bug_task integer REFERENCES analysis_tasks NOT NULL,
    bug_tracker integer REFERENCES bug_trackers NOT NULL,
    bug_number integer NOT NULL);

CREATE TABLE build_triggerables (
    triggerable_id serial PRIMARY KEY,
    triggerable_name varchar(64) NOT NULL UNIQUE,
    triggerable_disabled boolean NOT NULL DEFAULT FALSE);

CREATE TABLE triggerable_repository_groups (
    repositorygroup_id serial PRIMARY KEY,
    repositorygroup_triggerable integer REFERENCES build_triggerables NOT NULL,
    repositorygroup_name varchar(256) NOT NULL,
    repositorygroup_description varchar(256),
    repositorygroup_accepts_roots boolean NOT NULL DEFAULT FALSE,
    repositorygroup_hidden boolean NOT NULL DEFAULT FALSE,
    CONSTRAINT repository_group_name_must_be_unique_for_triggerable UNIQUE(repositorygroup_triggerable, repositorygroup_name));

CREATE TABLE triggerable_repositories (
    trigrepo_repository integer REFERENCES repositories NOT NULL,
    trigrepo_group integer REFERENCES triggerable_repository_groups NOT NULL,
    trigrepo_accepts_patch boolean NOT NULL DEFAULT FALSE,
    CONSTRAINT repository_must_be_unique_for_repository_group UNIQUE(trigrepo_repository, trigrepo_group));

CREATE TABLE triggerable_configurations (
    trigconfig_test integer REFERENCES tests NOT NULL,
    trigconfig_platform integer REFERENCES platforms NOT NULL,
    trigconfig_triggerable integer REFERENCES build_triggerables NOT NULL,
    CONSTRAINT triggerable_must_be_unique_for_test_and_platform UNIQUE(trigconfig_test, trigconfig_platform));

CREATE TABLE uploaded_files (
    file_id serial PRIMARY KEY,
    file_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
    file_deleted_at timestamp,
    file_mime varchar(64),
    file_filename varchar(1024) NOT NULL,
    file_extension varchar(16),
    file_author varchar(256),
    file_size bigint NOT NULL,
    file_sha256 char(64) NOT NULL);
CREATE INDEX file_author_index ON uploaded_files(file_author);
CREATE UNIQUE INDEX file_sha256_index ON uploaded_files(file_sha256) WHERE file_deleted_at is NULL;

CREATE TYPE analysis_test_group_repetition_type as ENUM ('alternating', 'sequential');
CREATE TABLE analysis_test_groups (
    testgroup_id serial PRIMARY KEY,
    testgroup_task integer REFERENCES analysis_tasks NOT NULL,
    testgroup_name varchar(256),
    testgroup_author varchar(256),
    testgroup_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
    testgroup_hidden boolean NOT NULL DEFAULT FALSE,
    testgroup_needs_notification boolean NOT NULL DEFAULT FALSE,
    testgroup_notification_sent_at timestamp DEFAULT NULL,
    testgroup_initial_repetition_count integer NOT NULL,
    testgroup_repetition_type analysis_test_group_repetition_type NOT NULL DEFAULT 'alternating',
    testgroup_may_need_more_requests boolean DEFAULT FALSE,
    CONSTRAINT testgroup_name_must_be_unique_for_each_task UNIQUE(testgroup_task, testgroup_name));
CREATE INDEX testgroup_task_index ON analysis_test_groups(testgroup_task);

CREATE TABLE commit_sets (
    commitset_id serial PRIMARY KEY);

CREATE TABLE commit_set_items (
    commitset_set integer REFERENCES commit_sets NOT NULL,
    commitset_commit integer REFERENCES commits,
    commitset_commit_owner integer REFERENCES commits DEFAULT NULL,
    commitset_patch_file integer REFERENCES uploaded_files,
    commitset_root_file integer REFERENCES uploaded_files,
    commitset_requires_build boolean DEFAULT FALSE,
    CONSTRAINT commitset_must_have_commit_or_root CHECK (commitset_commit IS NOT NULL OR commitset_root_file IS NOT NULL),
    CONSTRAINT commitset_with_patch_must_have_commit CHECK (commitset_patch_file IS NULL OR commitset_commit IS NOT NULL),
    CONSTRAINT commitset_item_with_patch_must_requires_build CHECK (commitset_patch_file IS NULL OR commitset_requires_build = TRUE),
    CONSTRAINT commitset_item_with_owned_commit_must_requires_build CHECK (commitset_commit_owner IS NULL OR commitset_requires_build = TRUE));

CREATE TYPE build_request_status_type as ENUM ('pending', 'scheduled', 'running', 'failed', 'completed', 'canceled');
CREATE TABLE build_requests (
    request_id serial PRIMARY KEY,
    request_triggerable integer REFERENCES build_triggerables NOT NULL,
    request_repository_group integer REFERENCES triggerable_repository_groups,
    request_platform integer REFERENCES platforms NOT NULL,
    request_test integer REFERENCES tests,
    request_group integer REFERENCES analysis_test_groups NOT NULL,
    request_order integer NOT NULL,
    request_commit_set integer REFERENCES commit_sets NOT NULL,
    request_status build_request_status_type NOT NULL DEFAULT 'pending',
    request_url varchar(1024),
    request_build integer REFERENCES builds,
    request_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
    CONSTRAINT build_request_order_must_be_unique_in_group UNIQUE(request_group, request_order),
    CONSTRAINT build_request_order_must_be_positive_for_testing
        CHECK ((request_test IS NOT NULL AND request_order >= 0) OR (request_test IS NULL AND request_order < 0)));
CREATE INDEX build_request_triggerable ON build_requests(request_triggerable);
CREATE INDEX build_request_build ON build_requests(request_build);