/**
 * Implementation details - MSSQL 2005
 *
 * - Use DATETIME.
 *      MSSQL can only have one TIMESTAMP in each table. So there we use 
 *      DATETIME instead. But Derby does not have DATETIME, so we use 
 *      TIMESTAMP everywhere in Derby.
 */

CREATE TABLE version (
    database_version int NOT NULL
);

CREATE TABLE version_history (
    from_version int NOT NULL,
    to_version int NOT NULL,
    success smallint NOT NULL,
    updated_time datetime NOT NULL,
    message NVARCHAR(MAX)
);

INSERT INTO version VALUES (83);
INSERT INTO version_history (from_version, to_version, success, updated_time, message)
VALUES (0, 83, 1, CURRENT_TIMESTAMP, 'Created');


/**

        A

**/
CREATE TABLE api_keys (
    id bigint IDENTITY(1,1) CONSTRAINT pk_api_keys PRIMARY KEY,
    uuid NVarChar(36) NOT NULL UNIQUE,
    application_name NVARCHAR(255) NOT NULL UNIQUE,
    api_key NVARCHAR(255) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL,
    expires_at DATETIME,
    status NVARCHAR(50) NOT NULL DEFAULT 'ACTIVE'
);

/**

        B

**/



/**

        C

**/

CREATE TABLE color_scheme (
    id bigint NOT NULL CONSTRAINT pk_color_scheme PRIMARY KEY,
    uuid varchar(36) NOT NULL UNIQUE,
    scheme_name NVarChar(255) NOT NULL UNIQUE,
    scheme_data NVARCHAR(MAX),
    is_default smallint NOT NULL DEFAULT 0
);

/**
 * Configuration templates that the user can choose from when creating a new
 * repository on the server.
 */
CREATE TABLE config_template (
    id bigint NOT NULL CONSTRAINT pk_config_template PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    title NVarChar(255) NOT NULL,
    config NVARCHAR(MAX)
);

CREATE TABLE customer_group (
    id bigint NOT NULL CONSTRAINT pk_customer_group PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    title NVarChar(255) NOT NULL
);
CREATE INDEX customer_group_idx1 ON customer_group (uuid);


/**

        D

**/

CREATE TABLE description (
    id bigint NOT NULL CONSTRAINT pk_description PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    fk_type bigint NOT NULL DEFAULT 1,
    fk_symbol bigint,
    fk_model bigint,
    fk_repository bigint NOT NULL
);
CREATE INDEX description_idx1 ON description (fk_type);
CREATE INDEX description_idx2 ON description (fk_symbol);
CREATE INDEX description_idx3 ON description (fk_model);
CREATE INDEX description_idx4 ON description (fk_repository);

CREATE TABLE description_type (
    id bigint NOT NULL CONSTRAINT pk_description_type PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    fk_repository bigint NOT NULL,
    data_type_descr_xml NVARCHAR(MAX) NOT NULL,
    last_modified datetime NOT NULL,
    history_path NVARCHAR(MAX),
    history_edited smallint NOT NULL,
    input_type NVarChar(255) NOT NULL DEFAULT 'rich-text-editor',
    icon NVarChar(255) NOT NULL DEFAULT 'icon:o16/object_ball.png'
);
CREATE INDEX description_type_idx1 ON description_type (fk_repository);

CREATE TABLE description_type_entry (
    id bigint NOT NULL CONSTRAINT pk_description_type_entry PRIMARY KEY,
    object_type NVarChar(255) NOT NULL,
    list_pos int NOT NULL,
    fk_type bigint NOT NULL,
    hidden NVARCHAR(MAX) /*A set of model type ids where the entry is hidden*/
);
CREATE INDEX description_type_entry_idx1 ON description_type_entry (fk_type);

CREATE TABLE doc_type (
    id bigint NOT NULL CONSTRAINT pk_doc_type PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    fk_repository bigint NOT NULL,
    last_modified datetime NOT NULL,
    history_path NVARCHAR(MAX),
    history_edited smallint NOT NULL
);
CREATE INDEX doc_type_idx1 ON doc_type (fk_repository);






/**

        E

**/

CREATE TABLE edge (
    id bigint NOT NULL ,
    from_vertex bigint NOT NULL ,
    to_vertex bigint NOT NULL ,
    edge_type NVarChar(50) NOT NULL ,
    fk_model bigint NOT NULL,
    uuid NVarChar(36) NOT NULL,
    txt_font NVarChar(50) NOT NULL,
    txt_size int NOT NULL,
    txt_style int NOT NULL,
    txt_color int NOT NULL,
    z_order int NOT NULL,
    manual_label_color smallint NOT NULL,
    hide_arrows smallint NOT NULL,
    router_id NVarChar(50) NOT NULL,
    history_path NVARCHAR(MAX),
    history_edited smallint NOT NULL,
    rotate_label smallint NOT NULL,
    stroke varchar(255) DEFAULT NULL,
    points varchar(1024)
);
CREATE INDEX edge_idx1 ON edge (from_vertex);
CREATE INDEX edge_idx2 ON edge (to_vertex);
CREATE INDEX edge_idx3 ON edge (fk_model);

CREATE TABLE edge_user_data (
    id bigint NOT NULL CONSTRAINT pk_edge_user_data PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    user_data NVARCHAR(MAX),
    fk_edge bigint NOT NULL
);

CREATE TABLE email_subscriber (
    id bigint NOT NULL PRIMARY KEY,
    uuid NVarChar(36) NOT NULL UNIQUE DEFAULT NEWID(),
    e_mail NVarChar(255) NOT NULL UNIQUE,
    first_name NVarChar(255),
    last_name NVarChar(255)
);

CREATE TABLE external_doc (
    id bigint NOT NULL CONSTRAINT pk_external_link PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    copy_on_publish smallint NOT NULL,
    fk_repository bigint NOT NULL,
    fk_owner NVarChar(50) NOT NULL,
    fk_group bigint NULL,
    owner_read smallint NOT NULL,
    owner_write smallint NOT NULL,
    group_read smallint NOT NULL,
    group_write smallint NOT NULL,
    all_read smallint NOT NULL,
    all_write smallint NOT NULL,
    created datetime NOT NULL,
    fk_created_by NVarChar(50) NOT NULL,
    last_modified datetime NOT NULL,
    fk_last_modified_by NVarChar(50) NOT NULL,
    fk_responsible bigint,
    document_data_type NVarChar(50) NOT NULL,
    history_path NVARCHAR(MAX),
    history_edited smallint NOT NULL,
    icon_url NVarChar(255),
    show_in_panel smallint NOT NULL,
    short_id NVarChar(20),
    short_id_locked smallint NOT NULL DEFAULT 0
);
CREATE INDEX external_doc_idx1 ON external_doc (fk_repository);
CREATE INDEX external_doc_idx2 ON external_doc (fk_owner);
CREATE INDEX external_doc_idx3 ON external_doc (fk_group);
CREATE INDEX external_doc_idx4 ON external_doc (fk_created_by);
CREATE INDEX external_doc_idx5 ON external_doc (fk_last_modified_by);
CREATE INDEX external_doc_idx6 ON external_doc (fk_responsible);

CREATE TABLE external_doc_doc_type (
    doc_id bigint NOT NULL,
    type_id bigint NOT NULL
);
CREATE INDEX external_doc_doc_type_idx1 ON external_doc_doc_type (doc_id);
CREATE INDEX external_doc_doc_type_idx2 ON external_doc_doc_type (type_id);

CREATE TABLE external_doc_property (
    fk_doc bigint NOT NULL,
    plugin_uuid varchar(255) NOT NULL,
    property_name NVarChar(255) NOT NULL,
    property_value NVarChar(1024),
    PRIMARY KEY (fk_doc, plugin_uuid, property_name)
);
CREATE INDEX external_doc_property_idx1 ON external_doc_property (fk_doc);
 
CREATE TABLE extension_document (
        id bigint NOT NULL CONSTRAINT pk_extension_document PRIMARY KEY,
        is_leaf smallint NOT NULL,
        name_ varchar(512) NOT NULL,
        provider varchar(512) NOT NULL,
        list_position int NOT NULL,
        fk_repository bigint NOT NULL,
        href varchar(1024),
        uuid varchar(36),
        created datetime,
        is_new smallint,
        icon IMAGE,
        fk_model bigint,
        fk_symbol bigint
);
CREATE INDEX extension_document_idx1 ON extension_document (fk_repository);
CREATE INDEX extension_document_idx2 ON extension_document (fk_model);
CREATE INDEX extension_document_idx3 ON extension_document (fk_symbol);



/**

        F

**/


/**

        G

**/

CREATE TABLE group_subscriber (
    id bigint NOT NULL PRIMARY KEY,
    group_name NVarChar(255),
    uuid NVarChar(36) NOT NULL
);

/**

        H

**/


/**

        I

**/

CREATE TABLE id_gen (
    table_id NVarChar (255) NOT NULL,
    id bigint NOT NULL 
);

CREATE TABLE installed_plugin (
    id bigint NOT NULL PRIMARY KEY,
    plugin_id NVarChar(255) NOT NULL,
    version NVarChar(20) NOT NULL,
    min_api_version NVarChar(20) NOT NULL,
    max_api_version_exclusive NVarChar(20) NOT NULL,
    data IMAGE NOT NULL,
    auto_upgrade smallint NOT NULL,
    repo_restricted smallint NOT NULL,
    user_restricted smallint NOT NULL,
    logo NVARCHAR(MAX),
    target_version NVarChar(36)
);
ALTER TABLE installed_plugin ADD CONSTRAINT unique_plugin_version UNIQUE (plugin_id, version);
CREATE INDEX idx_installed_plugin_id ON installed_plugin (plugin_id);
CREATE INDEX idx_installed_plugin_version ON installed_plugin (version);

CREATE TABLE installed_plugin_repos (
    fk_installed_plugin bigint NOT NULL,
    fk_repository bigint NOT NULL,
    PRIMARY KEY (fk_installed_plugin, fk_repository)
);

CREATE TABLE installed_plugin_users (
    fk_installed_plugin bigint NOT NULL,
    fk_user NVarChar(50) NOT NULL,
    PRIMARY KEY (fk_installed_plugin, fk_user)
);


/**

        J

**/





/**

        K

**/

CREATE TABLE keyed_value (
    id NVarChar (255) NOT NULL,
    string NVarChar (255)  ,
    bool smallint NOT NULL ,
    long_value bigint,
    uuid NVarChar(36) NOT NULL
); 



/**

        L

**/

CREATE TABLE language_description (
    id bigint NOT NULL CONSTRAINT pk_language_description PRIMARY KEY,
    description_text NVARCHAR(MAX),
    fk_description bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    uuid NVarChar(36) NOT NULL
);
CREATE INDEX language_description_idx1 ON language_description (fk_description);
CREATE INDEX language_description_idx2 ON language_description (fk_language);

CREATE TABLE language_description_type (
    id bigint NOT NULL CONSTRAINT pk_language_description_type PRIMARY KEY,
    title NVarChar(255) NOT NULL,
    summary NVarChar(255),
    fk_description_type bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    uuid NVarChar(36) NOT NULL,
    refinement NVARCHAR(MAX)
);
CREATE INDEX language_description_type_idx1 ON language_description_type (fk_description_type);
CREATE INDEX language_description_type_idx2 ON language_description_type (fk_language);


CREATE TABLE language_document_type (
    id bigint NOT NULL CONSTRAINT pk_language_document_type PRIMARY KEY,
    title NVarChar(255) NOT NULL,
    fk_document_type bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    uuid NVarChar(36) NOT NULL
);
CREATE INDEX language_document_type_idx1 ON language_document_type (fk_document_type);
CREATE INDEX language_document_type_idx2 ON language_document_type (fk_language);

CREATE TABLE language_external_doc (
    id bigint NOT NULL CONSTRAINT pk_language_external_doc PRIMARY KEY,
    title NVarChar(255) NOT NULL,
    fk_external_doc bigint NOT NULL,
    fk_language bigint NOT NULL,
    uuid NVarChar(36) NOT NULL,
    translated smallint NOT NULL,
    xml_data NVARCHAR(MAX)
);
CREATE INDEX language_external_doc_idx1 ON language_external_doc (fk_external_doc);
CREATE INDEX language_external_doc_idx2 ON language_external_doc (fk_language);

CREATE TABLE language_bounds (
    id bigint NOT NULL CONSTRAINT pk_language_bounds PRIMARY KEY,
    x int NOT NULL,
    y int NOT NULL,
    width int NOT NULL,
    height int NOT NULL,
    fk_language bigint NOT NULL,
    fk_vertex bigint,
    fk_edge bigint,
    uuid NVarChar(36) NOT NULL,
    cpindex int NOT NULL
);
CREATE INDEX language_bounds_idx1 ON language_bounds (fk_language);
CREATE INDEX language_bounds_idx2 ON language_bounds (fk_vertex);
CREATE INDEX language_bounds_idx3 ON language_bounds (fk_edge);


CREATE TABLE language_model (
    id bigint NOT NULL CONSTRAINT pk_language_model PRIMARY KEY,
    title NVarChar(255) NOT NULL,
    fk_model bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    uuid NVarChar(36) NOT NULL
);
CREATE INDEX language_model_idx1 ON language_model (fk_model);
CREATE INDEX language_model_idx2 ON language_model (fk_language);

CREATE TABLE language_edge (
    id bigint NOT NULL CONSTRAINT pk_language_edge PRIMARY KEY,
    title NVarChar(255) NOT NULL,
    fk_edge bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    uuid NVarChar(36) NOT NULL,
    raw_title NVarChar(512),
);
CREATE INDEX language_edge_idx1 ON language_edge (fk_edge);
CREATE INDEX language_edge_idx2 ON language_edge (fk_language);

CREATE TABLE language_symbol (
    id bigint NOT NULL CONSTRAINT pk_language_symbol PRIMARY KEY,
    title NVarChar(255) NOT NULL,
    field1 NVarChar(255),
    field2 NVARCHAR(MAX),
    fk_symbol bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    uuid NVarChar(36) NOT NULL,
    raw_title NVarChar(512),
);
CREATE INDEX language_symbol_idx1 ON language_symbol (fk_symbol);
CREATE INDEX language_symbol_idx2 ON language_symbol (fk_language);

CREATE TABLE language_layer (
    id bigint NOT NULL CONSTRAINT pk_language_layer PRIMARY KEY,
    title NVarChar(255) NOT NULL,
    fk_layer_type bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    CONSTRAINT language_layer_unique UNIQUE (fk_layer_type, fk_language)
);
CREATE INDEX language_layer_idx1 ON language_layer (fk_layer_type);
CREATE INDEX language_layer_idx2 ON language_layer (fk_language);

CREATE TABLE layer (
    id bigint NOT NULL CONSTRAINT pk_layer PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    fk_model bigint NOT NULL,
    fk_layer_type bigint NOT NULL
);
CREATE INDEX layer_idx1 ON layer (fk_model);
CREATE INDEX layer_idx2 ON layer (fk_layer_type);

CREATE TABLE layer_type (
    id bigint NOT NULL CONSTRAINT pk_layer_type PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    zorder int NOT NULL,
    model_type NVarChar(50) NOT NULL,
    object_types NVARCHAR(MAX),
    fk_repository bigint NOT NULL,
    ikon varchar(50)
);
CREATE INDEX layer_type_idx1 ON layer_type (fk_repository);

CREATE TABLE live_configuration (
    id bigint NOT NULL PRIMARY KEY,
    title NVarChar(256) NOT NULL,
    context_root NVarChar(256) NOT NULL,
    api_key NVarChar(1024) NOT NULL,
    fk_profile bigint NOT NULL,
    fk_model_context bigint NOT NULL,
    fk_snapshot bigint NOT NULL
);

CREATE TABLE live_access_control (
    id bigint NOT NULL PRIMARY KEY,
    fk_live_configuration bigint NOT NULL,
    access_type NVarChar(36) NOT NULL,
    access_data NVarChar(1024)
);

CREATE TABLE list (
    id bigint NOT NULL CONSTRAINT pk_list PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    action_object NVarChar(60) NOT NULL,
    script NVARCHAR(MAX) NOT NULL,
    ui_generated smallint NOT NULL DEFAULT 0, /**used on read back to determine if we can edit the script with ui*/
    columns NVARCHAR(MAX) NOT NULL,
    fk_repository bigint NOT NULL,
    last_modified datetime NOT NULL,
    history_path NVARCHAR(MAX),
    history_edited smallint NOT NULL
);
CREATE INDEX list_idx1 ON list (fk_repository);

CREATE TABLE language_list (
    id bigint NOT NULL CONSTRAINT pk_language_list PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    title NVarChar(255) NOT NULL,
    fk_list bigint NOT NULL,
    fk_language bigint NOT NULL
);
CREATE INDEX language_list_idx1 ON language_list (fk_list);
CREATE INDEX language_list_idx2 ON language_list (fk_language);

/**

        M

**/

CREATE TABLE maintainers (
    fk_model bigint NOT NULL,
    fk_user NVarChar(50) NOT NULL,
    workflow_role NVarChar(50) NOT NULL,
    PRIMARY KEY (fk_model, fk_user, workflow_role)
);
CREATE INDEX maintainers_idx1 ON maintainers (fk_model);
CREATE INDEX maintainers_idx2 ON maintainers (fk_user);

CREATE TABLE model (
    model_id bigint NOT NULL ,
    model_type NVarChar(50) NOT NULL,
    uuid NVarChar(36) NOT NULL,
    ikon NVarChar(255),
    discriminator NVarChar(50) NOT NULL,
    last_modified datetime NOT NULL,
    created datetime NOT NULL,
    fk_repository bigint NOT NULL,
    fk_owner NVarChar(50) NOT NULL,
    fk_group bigint NULL,
    owner_read smallint NOT NULL,
    owner_write smallint NOT NULL,
    group_read smallint NOT NULL,
    group_write smallint NOT NULL,
    all_read smallint NOT NULL,
    all_write smallint NOT NULL,
    fk_last_modified_by NVarChar(50) NOT NULL,
    fk_created_by NVarChar(50) NOT NULL,
    locked_date datetime,
    rev int NOT NULL,
    rev_from int,
    rev_locked smallint NOT NULL,
    alt_id NVarChar(255),
    locked_by NVarChar(50),
    history_path NVARCHAR(MAX),
    history_edited smallint NOT NULL,
    short_id NVarChar(20),
    short_id_locked smallint NOT NULL DEFAULT 0,
    workflow_state NVarChar(20) NOT NULL,
    notify smallint NOT NULL DEFAULT 1,
    CONSTRAINT model_unique UNIQUE (uuid, fk_repository, rev)
);
CREATE INDEX model_idx2 ON model (fk_repository);
CREATE INDEX model_idx3 ON model (fk_owner);
CREATE INDEX model_idx4 ON model (fk_group);
CREATE INDEX model_idx5 ON model (fk_last_modified_by);
CREATE INDEX model_idx6 ON model (fk_created_by);
CREATE INDEX model_idx8 ON model (workflow_state);

CREATE TABLE model_context (
    id bigint NOT NULL CONSTRAINT pk_model_context PRIMARY KEY
);

CREATE TABLE model_context_entry (
    fk_context bigint NOT NULL,
    fk_model bigint NOT NULL,
    PRIMARY KEY (fk_context, fk_model)
);

CREATE TABLE model_breakdown (
    model_breakdown_id bigint NOT NULL,
    is_owning smallint NOT NULL,
    fk_model bigint NOT NULL,
    fk_edge bigint,
    uuid NVarChar(36) NOT NULL,
    fk_symbol bigint
);
CREATE INDEX model_breakdown_idx1 ON model_breakdown (fk_model);
CREATE INDEX model_breakdown_idx2 ON model_breakdown (fk_edge);
CREATE INDEX model_breakdown_idx3 ON model_breakdown (fk_symbol);

CREATE TABLE model_property (
    fk_model bigint NOT NULL,
    plugin_uuid varchar(255) NOT NULL,
    property_name NVarChar(255) NOT NULL,
    property_value NVarChar(1024),
    PRIMARY KEY (fk_model, plugin_uuid, property_name)
);
CREATE INDEX model_property_idx1 ON model_property (fk_model);

CREATE TABLE model_removal_history (
    fk_model bigint NOT NULL,
    uuid varchar(36) NOT NULL,
    history_path NVARCHAR(MAX)
);
CREATE INDEX model_removal_history_idx1 ON model_removal_history (fk_model);

CREATE TABLE model_subscriber (
    fk_model bigint NOT NULL,
    fk_email_subscriber bigint DEFAULT NULL,
    fk_user_subscriber bigint DEFAULT NULL,
    fk_group_subscriber bigint DEFAULT NULL,
    CONSTRAINT model_subscriber_unique UNIQUE (fk_model, fk_email_subscriber, fk_user_subscriber, fk_group_subscriber)
);


/**

        N

**/

CREATE TABLE notification_model (
    id bigint NOT NULL CONSTRAINT pk_notification_model PRIMARY KEY,
    fk_publication bigint NOT NULL,
    model_uuid varchar(36) NOT NULL,
    workflow_state varchar(20) NOT NULL,
    rev int NOT NULL,
    publish_date DATETIME NOT NULL,
    CONSTRAINT notification_model_unique UNIQUE (fk_publication, model_uuid, rev)
)

CREATE TABLE notification_receipt (
    id NVarChar(100) NOT NULL PRIMARY KEY,
    fk_notification_model bigint NOT NULL,
    fk_email_subscriber bigint,
    fk_user_subscriber bigint,
    fk_group_subscriber bigint,
    confirmed smallint NOT NULL,
    token NVarChar(7) NOT NULL UNIQUE,
    bulk_token NVarChar(7),
    confirmed_time datetime,
    CONSTRAINT notification_receipt_unique UNIQUE (fk_notification_model, fk_email_subscriber, fk_user_subscriber, fk_group_subscriber)
)

/**

        O

**/

CREATE TABLE object_link (
    id bigint NOT NULL CONSTRAINT pk_object_link PRIMARY KEY,
    link_pos int NOT NULL,
    uuid NVarChar(36) NOT NULL,
    source_id varchar(255),
    doc_id varchar(255),
    fk_symbol bigint,
    fk_model bigint
    CONSTRAINT unique_model_document UNIQUE (doc_id, fk_model),
    CONSTRAINT unique_symbol_document UNIQUE (doc_id, fk_symbol)
);
CREATE INDEX object_link_idx2 ON object_link (fk_symbol);
CREATE INDEX object_link_idx3 ON object_link (fk_model);
CREATE INDEX object_link_idx4 ON object_link (source_id);
CREATE INDEX object_link_idx5 ON object_link (doc_id);





/**

        P

**/

CREATE TABLE permission_group (
    id bigint NOT NULL CONSTRAINT pk_permission_group PRIMARY KEY,
    uuid varchar(36) NOT NULL UNIQUE,
    fk_repository bigint NOT NULL,
    fk_group bigint NOT NULL,
    owner_read smallint NOT NULL,
    owner_write smallint NOT NULL,
    group_read smallint NOT NULL,
    group_write smallint NOT NULL,
    all_read smallint NOT NULL,
    all_write smallint NOT NULL,
    CONSTRAINT permission_group_unique UNIQUE (fk_repository, fk_group)
);
CREATE INDEX permission_group_idx1 ON permission_group (fk_repository);
CREATE INDEX permission_group_idx3 ON permission_group (fk_group);

CREATE TABLE plugin_data (
    id bigint NOT NULL CONSTRAINT pk_plugin_data PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    plugin_uuid varchar(36) NOT NULL,
    data_key varchar(50) NOT NULL,
    content image,
    fk_model bigint,
    fk_symbol bigint,
    fk_document bigint,
    fk_repository bigint,
    fk_snapshot bigint,
    text_content NVARCHAR(MAX),
    is_root smallint NOT NULL
);
CREATE INDEX plugin_data_idx1 ON plugin_data (data_key);
CREATE INDEX plugin_data_idx2 ON plugin_data (is_root);
CREATE INDEX plugin_data_idx3 ON plugin_data (fk_model);
CREATE INDEX plugin_data_idx4 ON plugin_data (fk_symbol);
CREATE INDEX plugin_data_idx5 ON plugin_data (fk_document);
CREATE INDEX plugin_data_idx6 ON plugin_data (fk_repository);
CREATE INDEX plugin_data_idx7 ON plugin_data (fk_snapshot);

CREATE TABLE plugin_relation (
    fk_parent bigint NOT NULL,
    fk_child bigint NOT NULL,
    relation_type varchar(50) NOT NULL,
    pos int NOT NULL,
    strong smallint NOT NULL,
    PRIMARY KEY (fk_parent, fk_child, relation_type)
);
CREATE INDEX plugin_relation_idx1 ON plugin_relation (relation_type);
CREATE INDEX plugin_relation_idx2 ON plugin_relation (strong);
CREATE INDEX plugin_relation_idx3 ON plugin_relation (fk_parent);
CREATE INDEX plugin_relation_idx4 ON plugin_relation (fk_child);

CREATE TABLE publication (
    id bigint NOT NULL CONSTRAINT pk_publication PRIMARY KEY,
    uuid varchar(36) NOT NULL UNIQUE,
    fk_snapshot bigint NOT NULL,
    publish_date DATETIME NOT NULL,
    base_url varchar(255) NOT NULL,
    profile_id NVarChar(255) NOT NULL,
    CONSTRAINT publication_unique UNIQUE (profile_id, fk_snapshot)
)

CREATE TABLE publish_profile (
    id bigint NOT NULL CONSTRAINT pk_publish_profile PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    profile_type NVarChar(50) NOT NULL,
    profile_name NVarChar(255) NOT NULL,
    profile_description NVarChar(255),
    profile_data IMAGE NOT NULL,
    fk_repository bigint NOT NULL
);
CREATE INDEX publish_profile_idx1 ON publish_profile (fk_repository);

CREATE TABLE publish_template (
    id bigint NOT NULL CONSTRAINT pk_publish_template PRIMARY KEY,
    uuid varchar(36) NOT NULL UNIQUE,
    template_name NVarChar(255) NOT NULL,
    template_data IMAGE NOT NULL,
    is_default smallint NOT NULL DEFAULT 0,
    template_type NVarChar(50) NOT NULL
);

/**

        Q

**/


/**

        R

**/

CREATE TABLE repository (
    id bigint NOT NULL CONSTRAINT pk_repository PRIMARY KEY,
    name NVarChar(255) NOT NULL,
    uuid NVarChar(36) NOT NULL,
    status NVarChar(50) NOT NULL DEFAULT 'ONLINE',
    creation_date DATETIME,
    last_modified_date DATETIME,
    fk_owner NVarChar(50) NOT NULL,
    fk_color_scheme bigint,
    admin_lock smallint NOT NULL,
    template smallint NOT NULL,
    fk_server_directory bigint,
    enable_permissions smallint NOT NULL,
    draft smallint NOT NULL DEFAULT 0
);
CREATE INDEX repository_idx1 ON repository (fk_server_directory);


CREATE TABLE server_directory (
    id bigint NOT NULL CONSTRAINT pk_server_directory PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    name NVarChar(255) NOT NULL,
    fk_server_directory bigint
);
CREATE INDEX server_directory_idx1 ON server_directory (fk_server_directory);

CREATE TABLE repository_timestamp (
    id bigint NOT NULL CONSTRAINT pk_repository_timestamp PRIMARY KEY ,
    uuid varchar(36) NOT NULL,
    type_id NVarChar(50) NOT NULL,
    title NVarChar(255) NOT NULL,
    time_value datetime NOT NULL,
    fk_repository bigint NOT NULL
);
CREATE INDEX repository_timestamp_idx1 ON repository_timestamp (fk_repository);

CREATE TABLE repository_access (
    id bigint NOT NULL CONSTRAINT pk_repository_access PRIMARY KEY,
    uuid varchar(36) NOT NULL UNIQUE,
    fk_repository bigint NOT NULL,
    fk_user NVarChar(50) NULL,
    fk_group bigint NULL,
    privilege bigint DEFAULT 0 NOT NULL,
    CONSTRAINT repo_access_unique UNIQUE (fk_repository, fk_user, fk_group)
);
CREATE INDEX repository_access_idx1 ON repository_access (fk_repository);
CREATE INDEX repository_access_idx2 ON repository_access (fk_user);
CREATE INDEX repository_access_idx3 ON repository_access (fk_group);

CREATE TABLE repository_variable (
    id bigint NOT NULL CONSTRAINT pk_repository_variable PRIMARY KEY,
    uuid NVarChar(36) NOT NULL DEFAULT NEWID(),
    var_key varchar(255) NOT NULL,
    var_value NVarChar(255),
    var_type varchar(50) NOT NULL,
    fk_repository bigint NOT NULL,
    last_modified datetime NOT NULL,
    history_path NVARCHAR(MAX),
    history_edited smallint NOT NULL
);
CREATE INDEX variable_type ON repository_variable (var_type);
CREATE INDEX repository_variable_idx1 ON repository_variable (fk_repository);

CREATE TABLE language_repository_variable (
    id bigint NOT NULL CONSTRAINT pk_language_repository_variable PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    fk_repository_variable bigint NOT NULL,
    fk_language bigint NOT NULL,
    title NVarChar(255) NOT NULL
);
CREATE INDEX language_repository_variable_idx1 ON language_repository_variable (fk_repository_variable);
CREATE INDEX language_repository_variable_idx2 ON language_repository_variable (fk_language);

CREATE TABLE revision_log_item (
    id bigint NOT NULL CONSTRAINT pk_revision_log_item PRIMARY KEY,
    fk_model bigint,
    fk_symbol bigint,
    fk_trigger bigint,
    fk_user NVarChar(50) NOT NULL,
    log_message NVarChar(512),
    reason NVarChar(50),
    is_new_revision smallint NOT NULL,    
    rev_time DATETIME NOT NULL,
    model_svg Text,
    workflow_state NVarChar(20)
);
CREATE INDEX revision_log_item_idx1 ON revision_log_item (fk_model);
CREATE INDEX revision_log_item_idx2 ON revision_log_item (fk_symbol);
CREATE INDEX revision_log_item_idx3 ON revision_log_item (fk_trigger);




/**

        S

**/

CREATE TABLE server_settings (
    id bigint NOT NULL CONSTRAINT pk_server_settings PRIMARY KEY,
    setting_id NVarChar(50) NOT NULL,
    setting_value NVarChar(4000)
);

CREATE TABLE snapshot (
    id bigint NOT NULL CONSTRAINT pk_snapshot PRIMARY KEY,
    title NVarChar(255),
    creation_date datetime NOT NULL,
    locked smallint NOT NULL,
    fk_parent bigint,
    fk_repository bigint NOT NULL,
    config NVARCHAR(MAX),
    active smallint NOT NULL
);
CREATE INDEX snapshot_idx1 ON snapshot (fk_parent);
CREATE INDEX snapshot_idx2 ON snapshot (fk_repository);

CREATE TABLE snapshot_removal_history (
    fk_snapshot bigint NOT NULL,
    uuid varchar(36) NOT NULL,
    history_path NVARCHAR(MAX)
);
CREATE INDEX snapshot_removal_history_idx1 ON snapshot_removal_history (fk_snapshot);

CREATE TABLE snapshot_model (
    fk_snapshot bigint NOT NULL,
    fk_model bigint NOT NULL,
    model_uuid NVarChar(36) NOT NULL
);
CREATE INDEX snapshot_model_idx1 ON snapshot_model (fk_snapshot);
CREATE INDEX snapshot_model_idx2 ON snapshot_model (fk_model);

CREATE TABLE snapshot_symbol (
    fk_snapshot bigint NOT NULL,
    fk_symbol bigint NOT NULL,
    symbol_uuid NVarChar(36) NOT NULL
);
CREATE INDEX snapshot_symbol_idx1 ON snapshot_symbol (fk_snapshot);
CREATE INDEX snapshot_symbol_idx2 ON snapshot_symbol (fk_symbol);

CREATE TABLE stored_query (
    id bigint NOT NULL ,
    [NAME] NVarChar (255),
    query_string NVARCHAR(MAX),
    uuid NVarChar(36) NOT NULL
);

CREATE TABLE group_subscriber_member (
    fk_group bigint NOT NULL,
    fk_email_subscriber_member bigint DEFAULT NULL,
    fk_user_subscriber_member bigint DEFAULT NULL,
    CONSTRAINT group_subscriber_member_unique UNIQUE (fk_group, fk_email_subscriber_member, fk_user_subscriber_member)
);

CREATE TABLE swimlane_relation (
    id bigint NOT NULL CONSTRAINT pk_swimlane_relation PRIMARY KEY,
    object_type NVarChar(50) NOT NULL,
    edge_type NVarChar(50) NOT NULL,
    direction NVarChar(10) NOT NULL,
    fk_settings bigint NOT NULL
);
CREATE INDEX swimlane_relation_idx1 ON swimlane_relation (fk_settings);

CREATE TABLE swimlane_settings (
    id bigint NOT NULL CONSTRAINT pk_swimlane_settings PRIMARY KEY,
    fk_model bigint NOT NULL ,
    object_type NVarChar(50) NOT NULL
);
CREATE INDEX swimlane_settings_idx1 ON swimlane_settings (fk_model);

CREATE TABLE symbol (
    id bigint NOT NULL CONSTRAINT pk_symbol PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    ikon NVarChar(50),
    alt_symbol NVarChar(50),
    fk_repository bigint NOT NULL,
    discriminator NVarChar(50),
    fk_owner NVarChar(50) NOT NULL,
    fk_group bigint NULL,
    owner_read smallint NOT NULL,
    owner_write smallint NOT NULL,
    group_read smallint NOT NULL,
    group_write smallint NOT NULL,
    all_read smallint NOT NULL,
    all_write smallint NOT NULL,
    created datetime NOT NULL,
    last_modified datetime NOT NULL,
    fk_last_modified_by NVarChar(50) NOT NULL,
    locked_date datetime,
    fk_created_by NVarChar(50) NOT NULL,
    rev int NOT NULL,
    rev_from int,
    rev_locked smallint NOT NULL,
    alt_id NVarChar(255),
    locked_by NVarChar(50),
    history_path NVARCHAR(MAX),
    history_edited smallint NOT NULL,
    short_id NVarChar(20),
    short_id_locked smallint NOT NULL DEFAULT 0
);
CREATE INDEX symbol_idx2 ON symbol (fk_repository);
CREATE INDEX symbol_idx3 ON symbol (fk_owner);
CREATE INDEX symbol_idx4 ON symbol (fk_group);
CREATE INDEX symbol_idx5 ON symbol (fk_last_modified_by);
CREATE INDEX symbol_idx6 ON symbol (fk_created_by);

CREATE TABLE symbol_property (
    fk_symbol bigint NOT NULL,
    plugin_uuid varchar(255) NOT NULL,
    property_name NVarChar(255) NOT NULL,
    property_value NVarChar(1024),
    PRIMARY KEY (fk_symbol, plugin_uuid, property_name)
)
CREATE INDEX symbol_property_idx1 ON symbol_property (fk_symbol);



/**

        T

**/

CREATE TABLE thumbnail (
    model_id bigint NOT NULL CONSTRAINT pk_thumbnail PRIMARY KEY,
    image_data IMAGE
);


/**

        U

**/


/**
 * This table is used by both glassfish and the application.
 * Glassfish looks in user_id and password.
 *
 */
CREATE TABLE users (
    user_id NVarChar(50) NOT NULL CONSTRAINT pk_users PRIMARY KEY,
    first_name NVarChar(255) NOT NULL,
    last_name NVarChar(255) NOT NULL,
    administrator smallint NOT NULL,
    privilege bigint NOT NULL,
    disabled smallint NOT NULL,
    last_seen bigint,
    global_id NVarChar(256),
    group_ids NText,
    avatar NText
);
create unique nonclustered index users_unique_global_id on users(global_id) where global_id is not null;

CREATE TABLE user_setting (
    fk_user NVarChar(50) NOT NULL,
    setting_id NVarChar(256),
    setting_value NVarChar(4000),
    PRIMARY KEY (fk_user, setting_id)
);
CREATE INDEX user_setting_idx1 ON user_setting (fk_user);

CREATE TABLE user_image (
    id bigint NOT NULL CONSTRAINT pk_user_image PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    image_name NVarChar(255),
    image_data IMAGE,
    fk_repository bigint NOT NULL
);
CREATE INDEX user_image_idx1 ON user_image (fk_repository);

CREATE TABLE user_language (
    id bigint NOT NULL CONSTRAINT pk_user_language PRIMARY KEY,
    uuid NVarChar(36) NOT NULL,
    language_name NVarChar(255) NOT NULL,
    ikon NVarChar(50),
    is_default smallint NOT NULL,
    fk_repository bigint NOT NULL,
    hyphen_dict NVarChar(10),
    iso NVarChar(50)
);
CREATE INDEX user_language_idx1 ON user_language (fk_repository);
CREATE INDEX user_language_idx2 ON user_language (is_default);

CREATE TABLE user_subscriber (
    id bigint NOT NULL PRIMARY KEY,
    fk_user NVarChar(50) NOT NULL UNIQUE
);

/**

        V

**/

CREATE TABLE vertex (
    id bigint NOT NULL ,
    bounds_x int NOT NULL ,
    bounds_y int NOT NULL ,
    bounds_width int NOT NULL ,
    bounds_height int NOT NULL ,
    do_owner smallint NOT NULL ,
    fk_model bigint NOT NULL,
    z_order int NOT NULL,
    uuid NVarChar(36) NOT NULL,
    txt_font NVarChar(50) NOT NULL,
    txt_size int NOT NULL,
    txt_style int NOT NULL,
    txt_color int NOT NULL,
    is_breakdown_symbol smallint NOT NULL DEFAULT 0,
    image_name NVarChar(255),
    show_label smallint NOT NULL DEFAULT 1,
    lock_label smallint NOT NULL DEFAULT 0,
    transform_rgb int DEFAULT NULL,
    transform_hue float DEFAULT NULL,
    transform_sat float DEFAULT NULL,
    transform_br float DEFAULT NULL,
    center_label smallint NOT NULL DEFAULT 0,
    manual_label_color smallint NOT NULL DEFAULT 0,
    lock_proportions smallint NOT NULL DEFAULT 0,
    size_ratio float NOT NULL DEFAULT 1,
    fk_symbol bigint NOT NULL,
    elidable smallint NOT NULL,
    locked smallint NOT NULL,
    fold_surrounding smallint NOT NULL DEFAULT 0,
    foldable smallint NOT NULL DEFAULT 0,
    fk_layer bigint,
    history_path NVARCHAR(MAX),
    history_edited smallint NOT NULL,
    border varchar(255)
);
CREATE INDEX vertex_idx1 ON vertex (fk_model);
CREATE INDEX vertex_idx2 ON vertex (fk_symbol);
CREATE INDEX vertex_idx3 ON vertex (fk_layer);

CREATE TABLE vertex_property (
    fk_vertex bigint NOT NULL,
    plugin_uuid varchar(255) NOT NULL,
    property_name NVarChar(255) NOT NULL,
    property_value NVarChar(1024),
    PRIMARY KEY (fk_vertex, plugin_uuid, property_name)
);
CREATE INDEX vertex_property_idx1 ON vertex_property (fk_vertex);



/**

    ADD INDEXES ON uuid

**/

CREATE INDEX uuid_description ON description (uuid);
CREATE INDEX uuid_doc_type ON doc_type (uuid);
CREATE INDEX uuid_edge ON edge (uuid);
CREATE INDEX uuid_external_doc ON external_doc (uuid);
CREATE INDEX uuid_keyed_value ON keyed_value (uuid);
CREATE INDEX uuid_model ON model (uuid);
CREATE INDEX uuid_model_breakdown ON model_breakdown (uuid);
CREATE INDEX uuid_object_link ON object_link(uuid);
CREATE INDEX uuid_stored_query ON stored_query (uuid);
CREATE INDEX uuid_symbol ON symbol (uuid);
CREATE INDEX uuid_vertex ON vertex (uuid);
CREATE INDEX uuid_repository ON repository (uuid);
CREATE INDEX uuid_user_language ON user_language (uuid);
CREATE INDEX uuid_snapshot_model ON snapshot_model (model_uuid);
CREATE INDEX uuid_snapshot_symbol ON snapshot_symbol (symbol_uuid);


/**

    SET PRIMARY KEY CONSTRAINTS

**/


ALTER TABLE edge ADD CONSTRAINT pk_edge PRIMARY KEY   (id);
ALTER TABLE external_doc_doc_type ADD CONSTRAINT pk_external_doc_doc_type PRIMARY KEY (doc_id, type_id);
ALTER TABLE id_gen ADD CONSTRAINT pk_table_generator PRIMARY KEY   (table_id);
ALTER TABLE keyed_value ADD CONSTRAINT pk_keyed_value PRIMARY KEY   (id);
ALTER TABLE model ADD CONSTRAINT pk_model PRIMARY KEY   (model_id);
ALTER TABLE model_breakdown ADD CONSTRAINT pk_model_breakdown PRIMARY KEY( model_breakdown_id );
ALTER TABLE vertex ADD CONSTRAINT pk_vertex PRIMARY KEY   (id);
ALTER TABLE snapshot_model ADD CONSTRAINT pk_snapshot_model PRIMARY KEY (fk_snapshot, fk_model);
ALTER TABLE snapshot_symbol ADD CONSTRAINT pk_snapshot_symbol PRIMARY KEY (fk_snapshot, fk_symbol);




/**

    SET FOREIGN KEY CONSTRAINTS

**/

ALTER TABLE description ADD CONSTRAINT fk_description_description_type FOREIGN KEY
    (fk_type) REFERENCES description_type (id) ON DELETE CASCADE;
ALTER TABLE description ADD CONSTRAINT fk_description_symbol FOREIGN KEY
    (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE description ADD CONSTRAINT fk_description_model FOREIGN KEY
    (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE description_type_entry ADD CONSTRAINT fk_description_type_entry_description_type FOREIGN KEY
    (fk_type) REFERENCES description_type (id) ON DELETE CASCADE;
ALTER TABLE edge ADD CONSTRAINT fk_edge_model FOREIGN KEY
    (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE edge_user_data ADD CONSTRAINT fk_edge_user_data_edge FOREIGN KEY
    (fk_edge) REFERENCES edge (id) ON DELETE CASCADE;
ALTER TABLE extension_document ADD CONSTRAINT fk_extension_document_model FOREIGN KEY
    (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE extension_document ADD CONSTRAINT fk_extension_document_symbol FOREIGN KEY
    (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE extension_document ADD CONSTRAINT fk_extension_document_repository FOREIGN KEY
    (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE external_doc_doc_type ADD CONSTRAINT fk_external_doc_doc_type_doc
    FOREIGN KEY (doc_id) REFERENCES external_doc (id) ON DELETE CASCADE;
ALTER TABLE external_doc_doc_type ADD CONSTRAINT fk_external_doc_doc_type_type
    FOREIGN KEY (type_id) REFERENCES doc_type (id) ON DELETE CASCADE;
ALTER TABLE model_breakdown ADD CONSTRAINT fk_model_breakdown_model FOREIGN KEY
    ( fk_model ) REFERENCES model ( model_id ) ON DELETE CASCADE;
ALTER TABLE model_breakdown ADD CONSTRAINT fk_model_breakdown_symbol FOREIGN KEY
    (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE object_link ADD CONSTRAINT fk_object_link_symbol FOREIGN KEY
    (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE object_link ADD CONSTRAINT fk_object_link_model FOREIGN KEY
    (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE repository ADD CONSTRAINT fk_repository_color_scheme FOREIGN KEY
    (fk_color_scheme) REFERENCES color_scheme (id) ON DELETE SET NULL;
ALTER TABLE symbol ADD CONSTRAINT fk_created_symbol_users
  FOREIGN KEY (fk_created_by) REFERENCES users (user_id);
ALTER TABLE vertex ADD CONSTRAINT fk_vertex_model FOREIGN KEY
    (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE vertex ADD CONSTRAINT fk_vertex_symbol FOREIGN KEY
    (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE description ADD CONSTRAINT fk_description_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id);
ALTER TABLE description_type ADD CONSTRAINT fk_description_type_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id);
ALTER TABLE doc_type ADD CONSTRAINT fk_doc_type_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id);
ALTER TABLE model ADD CONSTRAINT fk_model_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id);
ALTER TABLE model_context_entry ADD CONSTRAINT fk_context_context
  FOREIGN KEY (fk_context) REFERENCES model_context (id) ON DELETE CASCADE;
ALTER TABLE model_context_entry ADD CONSTRAINT fk_context_model
  FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE symbol ADD CONSTRAINT fk_symbol_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id);
ALTER TABLE user_image ADD CONSTRAINT fk_user_image_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE user_language ADD CONSTRAINT fk_user_language_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE swimlane_settings ADD CONSTRAINT fk_swimlane_model
  FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE swimlane_relation ADD CONSTRAINT fk_swimlane_relation_settings
    FOREIGN KEY (fk_settings) REFERENCES swimlane_settings (id) ON DELETE CASCADE;
ALTER TABLE layer ADD CONSTRAINT fk_layer_model
    FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE layer ADD CONSTRAINT fk_layer_layer_type
    FOREIGN KEY (fk_layer_type) REFERENCES layer_type (id) ON DELETE CASCADE;
ALTER TABLE vertex ADD CONSTRAINT fk_vertex_layer
    FOREIGN KEY (fk_layer) REFERENCES layer (id);
ALTER TABLE language_layer ADD CONSTRAINT fk_language_layer_layer_type
    FOREIGN KEY (fk_layer_type) REFERENCES layer_type (id) ON DELETE CASCADE;
ALTER TABLE language_layer ADD CONSTRAINT fk_language_layer_language
    FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE publish_profile ADD CONSTRAINT fk_publish_profile_repository
    FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE symbol_property ADD CONSTRAINT fk_symbol_property_symbol
    FOREIGN KEY (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE model_property ADD CONSTRAINT fk_model_property_model
    FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE vertex_property ADD CONSTRAINT fk_vertex_property_vertex
    FOREIGN KEY (fk_vertex) REFERENCES vertex (id) ON DELETE CASCADE;
ALTER TABLE external_doc_property ADD CONSTRAINT fk_external_doc_property_doc
    FOREIGN KEY (fk_doc) REFERENCES external_doc (id) ON DELETE CASCADE;
ALTER TABLE plugin_data ADD CONSTRAINT fk_plugin_data_model
    FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE plugin_data ADD CONSTRAINT fk_plugin_data_symbol
    FOREIGN KEY (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE plugin_data ADD CONSTRAINT fk_plugin_data_document
    FOREIGN KEY (fk_document) REFERENCES external_doc (id) ON DELETE CASCADE;
ALTER TABLE plugin_data ADD CONSTRAINT fk_plugin_data_repository
    FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE plugin_data ADD CONSTRAINT fk_plugin_data_snapshot
    FOREIGN KEY (fk_snapshot) REFERENCES snapshot (id) ON DELETE NO ACTION;
ALTER TABLE repository_variable ADD CONSTRAINT fk_repository_var_rep
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE language_repository_variable ADD CONSTRAINT fk_language_rep_var_var
  FOREIGN KEY (fk_repository_variable) REFERENCES repository_variable (id) ON DELETE CASCADE;
ALTER TABLE repository_timestamp ADD CONSTRAINT fk_timestamp_repo
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE repository ADD CONSTRAINT fk_repository_server_directory
  FOREIGN KEY (fk_server_directory) REFERENCES server_directory (id) ON DELETE CASCADE;

/**
 * Users and groups.
 */
ALTER TABLE permission_group ADD CONSTRAINT fk_permission_group_repo
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE permission_group ADD CONSTRAINT fk_permission_group_group
  FOREIGN KEY (fk_group) REFERENCES customer_group (id) ON DELETE CASCADE;
 ALTER TABLE repository_access ADD CONSTRAINT fk_repo_access_repo
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE repository_access ADD CONSTRAINT fk_repo_access_user
  FOREIGN KEY (fk_user) REFERENCES users (user_id) ON DELETE CASCADE;
  ALTER TABLE repository_access ADD CONSTRAINT fk_repo_access_group
  FOREIGN KEY (fk_group) REFERENCES customer_group (id) ON DELETE CASCADE;
ALTER TABLE model ADD CONSTRAINT fk_model_users
  FOREIGN KEY (fk_owner) REFERENCES users (user_id);
  ALTER TABLE model ADD CONSTRAINT fk_model_group
  FOREIGN KEY (fk_group) REFERENCES customer_group (id);
ALTER TABLE symbol ADD CONSTRAINT fk_symbol_users
  FOREIGN KEY (fk_owner) REFERENCES users (user_id);
ALTER TABLE symbol ADD CONSTRAINT fk_symbol_users_modified
  FOREIGN KEY (fk_last_modified_by) REFERENCES users (user_id);
ALTER TABLE repository ADD CONSTRAINT fk_repository_users
  FOREIGN KEY (fk_owner) REFERENCES users (user_id);
ALTER TABLE model ADD CONSTRAINT fk_modified_model_users
  FOREIGN KEY (fk_last_modified_by) REFERENCES users (user_id);
ALTER TABLE model ADD CONSTRAINT fk_created_model_users
  FOREIGN KEY (fk_created_by) REFERENCES users (user_id);
ALTER TABLE maintainers ADD CONSTRAINT fk_maintainers_model
  FOREIGN KEY (fk_model) REFERENCES model (model_id);
ALTER TABLE maintainers ADD CONSTRAINT fk_maintainers_user
  FOREIGN KEY (fk_user) REFERENCES users (user_id);
ALTER TABLE model_subscriber ADD CONSTRAINT fk_model_subscriber_model
  FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE model_subscriber ADD CONSTRAINT fk_model_subscriber_email_subscriber
    FOREIGN KEY (fk_email_subscriber) REFERENCES email_subscriber (id) ON DELETE CASCADE;
ALTER TABLE model_subscriber ADD CONSTRAINT fk_model_subscriber_user_subscriber
    FOREIGN KEY (fk_user_subscriber) REFERENCES user_subscriber (id) ON DELETE CASCADE;
ALTER TABLE model_subscriber ADD CONSTRAINT fk_model_subscriber_group_subscriber
    FOREIGN KEY (fk_group_subscriber) REFERENCES group_subscriber (id) ON DELETE CASCADE;
ALTER TABLE publication ADD CONSTRAINT fk_publication_snapshot
  FOREIGN KEY (fk_snapshot) REFERENCES snapshot (id) ON DELETE CASCADE;
ALTER TABLE notification_model ADD CONSTRAINT fk_notification_model_publication
  FOREIGN KEY (fk_publication) REFERENCES publication (id) ON DELETE CASCADE;
ALTER TABLE notification_receipt ADD CONSTRAINT fk_notification_receipt_notification_model
  FOREIGN KEY (fk_notification_model) REFERENCES notification_model (id) ON DELETE CASCADE;
ALTER TABLE notification_receipt ADD CONSTRAINT fk_notification_receipt_email_subscriber
    FOREIGN KEY (fk_email_subscriber) REFERENCES email_subscriber (id) ON DELETE CASCADE;
ALTER TABLE notification_receipt ADD CONSTRAINT fk_notification_receipt_user_subscriber
    FOREIGN KEY (fk_user_subscriber) REFERENCES user_subscriber (id) ON DELETE CASCADE;
ALTER TABLE notification_receipt ADD CONSTRAINT fk_notification_receipt_group_subscriber
    FOREIGN KEY (fk_group_subscriber) REFERENCES group_subscriber (id) ON DELETE CASCADE;
ALTER TABLE user_subscriber ADD CONSTRAINT fk_user_subscriber_user
  FOREIGN KEY (fk_user) REFERENCES users (user_id) ON DELETE CASCADE;
ALTER TABLE group_subscriber_member ADD CONSTRAINT fk_group_subscriber_group
    FOREIGN KEY (fk_group) REFERENCES group_subscriber (id) ON DELETE CASCADE;
ALTER TABLE group_subscriber_member ADD CONSTRAINT fk_group_subscriber_email_member
    FOREIGN KEY (fk_email_subscriber_member) REFERENCES email_subscriber (id) ON DELETE CASCADE;
ALTER TABLE group_subscriber_member ADD CONSTRAINT fk_group_subscriber_user_member
    FOREIGN KEY (fk_user_subscriber_member) REFERENCES user_subscriber (id) ON DELETE CASCADE;
ALTER TABLE user_setting ADD CONSTRAINT fk_user_setting_user
  FOREIGN KEY (fk_user) REFERENCES users (user_id) ON DELETE CASCADE;

/**
 * External document
 */
ALTER TABLE external_doc ADD CONSTRAINT fk_external_doc_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id);
ALTER TABLE external_doc ADD CONSTRAINT fk_external_doc_users
  FOREIGN KEY (fk_owner) REFERENCES users (user_id);
ALTER TABLE external_doc ADD CONSTRAINT fk_modified_external_doc_users
  FOREIGN KEY (fk_last_modified_by) REFERENCES users (user_id);
ALTER TABLE external_doc ADD CONSTRAINT fk_created_external_doc_users
  FOREIGN KEY (fk_created_by) REFERENCES users (user_id);
ALTER TABLE external_doc ADD CONSTRAINT fk_external_doc_responsible
  FOREIGN KEY (fk_responsible) REFERENCES symbol (id);

/**
 * Plugins
 */
ALTER TABLE installed_plugin_repos ADD CONSTRAINT fk_installed_plugin_repos_plugin
    FOREIGN KEY (fk_installed_plugin) REFERENCES installed_plugin(id) ON DELETE CASCADE;
ALTER TABLE installed_plugin_repos ADD CONSTRAINT fk_installed_plugin_repos_repo
    FOREIGN KEY (fk_repository) REFERENCES repository(id) ON DELETE CASCADE;
ALTER TABLE installed_plugin_users ADD CONSTRAINT fk_installed_plugin_users_plugin
    FOREIGN KEY (fk_installed_plugin) REFERENCES installed_plugin(id) ON DELETE CASCADE;
ALTER TABLE installed_plugin_users ADD CONSTRAINT fk_installed_plugin_users_user
    FOREIGN KEY (fk_user) REFERENCES users(user_id) ON DELETE CASCADE;

/**
 * Languages
 */
ALTER TABLE language_description ADD CONSTRAINT fk_language_description_description
  FOREIGN KEY (fk_description) REFERENCES description (id) ON DELETE CASCADE;
ALTER TABLE language_description ADD CONSTRAINT fk_language_description_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_description_type ADD CONSTRAINT fk_language_description_type_description_type
  FOREIGN KEY (fk_description_type) REFERENCES description_type (id) ON DELETE CASCADE;
ALTER TABLE language_description_type ADD CONSTRAINT fk_language_description_type_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_document_type ADD CONSTRAINT fk_language_document_type_doc_type
  FOREIGN KEY (fk_document_type) REFERENCES doc_type (id) ON DELETE CASCADE;
ALTER TABLE language_document_type ADD CONSTRAINT fk_language_document_type_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_external_doc ADD CONSTRAINT fk_language_external_doc_external_doc
  FOREIGN KEY (fk_external_doc) REFERENCES external_doc (id) ON DELETE CASCADE;
ALTER TABLE language_external_doc ADD CONSTRAINT fk_language_external_doc_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_model ADD CONSTRAINT fk_language_model_model
  FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE language_model ADD CONSTRAINT fk_language_model_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_edge ADD CONSTRAINT fk_language_edge_edge
  FOREIGN KEY (fk_edge) REFERENCES edge (id) ON DELETE CASCADE;
ALTER TABLE language_edge ADD CONSTRAINT fk_language_edge_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_symbol ADD CONSTRAINT fk_language_symbol_symbol
  FOREIGN KEY (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE language_symbol ADD CONSTRAINT fk_language_symbol_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_bounds ADD CONSTRAINT fk_language_bounds_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_list ADD CONSTRAINT fk_language_list_list
  FOREIGN KEY (fk_list) REFERENCES list (id) ON DELETE CASCADE;
ALTER TABLE language_list ADD CONSTRAINT fk_language_list_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;

/**
 * Snapshots/Revisions
 */
ALTER TABLE snapshot ADD CONSTRAINT fk_snapshot_snapshot
  FOREIGN KEY (fk_parent) REFERENCES snapshot (id);
ALTER TABLE snapshot ADD CONSTRAINT fk_snapshot_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE snapshot_model ADD CONSTRAINT fk_snapshot_model_snapshot
  FOREIGN KEY (fk_snapshot) REFERENCES snapshot (id) ON DELETE CASCADE;
ALTER TABLE snapshot_model ADD CONSTRAINT fk_snapshot_model_model
  FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE snapshot_symbol ADD CONSTRAINT fk_snapshot_symbol_snapshot
  FOREIGN KEY (fk_snapshot) REFERENCES snapshot (id) ON DELETE CASCADE;
ALTER TABLE snapshot_symbol ADD CONSTRAINT fk_snapshot_symbol_symbol
  FOREIGN KEY (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE revision_log_item ADD CONSTRAINT fk_revision_log_item_model
    FOREIGN KEY (fk_model) REFERENCES model(model_id) ON DELETE CASCADE;
ALTER TABLE revision_log_item ADD CONSTRAINT fk_revision_log_item_symbol
    FOREIGN KEY (fk_symbol) REFERENCES symbol(id) ON DELETE CASCADE;
ALTER TABLE revision_log_item ADD CONSTRAINT fk_revision_log_item_user
    FOREIGN KEY (fk_user) REFERENCES users(user_id);

/**
 * Live publications
 */
ALTER TABLE live_configuration ADD CONSTRAINT fk_live_configuration_profile
    FOREIGN KEY (fk_profile) REFERENCES publish_profile(id) ON DELETE CASCADE;
ALTER TABLE live_configuration ADD CONSTRAINT fk_live_configuration_context
    FOREIGN KEY (fk_model_context) REFERENCES model_context(id) ON DELETE CASCADE;
ALTER TABLE live_configuration ADD CONSTRAINT fk_live_configuration_snapshot
    FOREIGN KEY (fk_snapshot) REFERENCES snapshot(id) ON DELETE NO ACTION;
ALTER TABLE live_access_control ADD CONSTRAINT fk_live_access_control_configuration
    FOREIGN KEY (fk_live_configuration) REFERENCES live_configuration(id) ON DELETE CASCADE;


ALTER TABLE model_subscriber
    ADD CONSTRAINT check_model_subscriber_only_one_not_null_subscriber
        CHECK (
                ((fk_email_subscriber) IS NOT NULL AND (fk_user_subscriber) IS NULL AND
                 (fk_group_subscriber) IS NULL) OR
                ((fk_email_subscriber) IS NULL AND (fk_user_subscriber) IS NOT NULL AND
                 (fk_group_subscriber) IS NULL) OR
                ((fk_email_subscriber) IS NULL AND (fk_user_subscriber) IS NULL AND (fk_group_subscriber) IS NOT NULL)
            );

ALTER TABLE notification_receipt
    ADD CONSTRAINT check_notification_receipt_only_one_not_null_subscriber
        CHECK (
                ((fk_email_subscriber) IS NOT NULL AND (fk_user_subscriber) IS NULL AND
                 (fk_group_subscriber) IS NULL) OR
                ((fk_email_subscriber) IS NULL AND (fk_user_subscriber) IS NOT NULL AND
                 (fk_group_subscriber) IS NULL) OR
                ((fk_email_subscriber) IS NULL AND (fk_user_subscriber) IS NULL AND (fk_group_subscriber) IS NOT NULL)
            );

GO



/**
*	Create triggers instead of foreign keys that could cause cycles
*   with CASCADE DELETE or SET NULL
**/

/** delete trigger for edge.from_vertex **/
CREATE TRIGGER DTrigger4
	ON vertex
	FOR DELETE
AS
	DELETE edge FROM edge x, DELETED y WHERE x.from_vertex = y.id;
GO

/** delete trigger for edge.to_vertex **/
CREATE TRIGGER DTrigger5
	ON vertex
	FOR DELETE
AS
	DELETE edge FROM edge x, DELETED y WHERE x.to_vertex = y.id;
GO

/** delete trigger for model_breakdown.fk_edge **/
CREATE TRIGGER DTrigger13
	ON edge
	FOR DELETE
AS
	DELETE model_breakdown FROM model_breakdown x, DELETED y WHERE x.fk_edge = y.id;
GO

/** delete trigger for language_bounds.fk_edge **/
CREATE TRIGGER DTrigger20
	ON edge
	FOR DELETE
AS
	DELETE language_bounds FROM language_bounds x, DELETED y WHERE x.fk_edge = y.id;
GO

/** delete trigger for language_bounds.fk_vertex **/
CREATE TRIGGER DTrigger21
	ON vertex
	FOR DELETE
AS
	DELETE language_bounds FROM language_bounds x, DELETED y WHERE x.fk_vertex = y.id;
GO

/** update trigger for removed layers (moves vertex/edge to background layer) **/
CREATE TRIGGER DTrigger22
	ON layer
	FOR DELETE
AS
	UPDATE x SET fk_layer = NULL FROM vertex x, DELETED y WHERE x.fk_layer = y.id;
GO

/** delete trigger for plugin_relation.fk_child and plugin_relation.fk_parent **/
CREATE TRIGGER DTrigger23
	ON plugin_data
	FOR DELETE
AS
	DELETE x FROM plugin_relation x, DELETED y WHERE x.fk_child = y.id;
	DELETE x FROM plugin_relation x, DELETED y WHERE x.fk_parent = y.id;
GO

/** delete trigger for language_repository_variable.fk_language **/
CREATE TRIGGER DTrigger24
	ON user_language
	FOR DELETE
AS
	DELETE x FROM language_repository_variable x, DELETED y WHERE x.fk_language = y.id;
GO

/** delete trigger for server_directory.fk_server_directory **/
CREATE TRIGGER DTrigger27
	ON server_directory
	FOR DELETE
AS
	DELETE server_directory FROM server_directory x, DELETED y WHERE x.fk_server_directory = y.id;
GO

CREATE TRIGGER DTrigger28
	ON snapshot
	FOR DELETE
AS
	DELETE publication FROM publication x, DELETED y WHERE x.fk_snapshot = y.id;
GO

/**
    These are needed to avoid migrating users/groups in a newly installed server
    when registering it in the portal.
 */
insert into server_settings values (1, 'auth.users-merged', 'true');
insert into server_settings values (2, 'auth.groups-merged', 'true');

/** id_gen **/
INSERT INTO id_gen VALUES('COLOR_SCHEME', 2);
INSERT INTO id_gen VALUES('DESCRIPTION', 1);
INSERT INTO id_gen VALUES('DESCRIPTION_TYPE', 2);
INSERT INTO id_gen VALUES('DESCRIPTION_TYPE_ENTRY', 1);
INSERT INTO id_gen VALUES('DOC_TYPE', 1);
INSERT INTO id_gen VALUES('EDGE', 1);
INSERT INTO id_gen VALUES('EDGE_USER_DATA', 1);
INSERT INTO id_gen VALUES('EXTERNAL_LINK', 1);
INSERT INTO id_gen VALUES('LANGUAGE_DESCRIPTION', 1);
INSERT INTO id_gen VALUES('LANGUAGE_DESCRIPTION_TYPE', 1);
INSERT INTO id_gen VALUES('LANGUAGE_DOCUMENT_TYPE', 1);
INSERT INTO id_gen VALUES('LANGUAGE_EXTERNAL_DOC', 1);
INSERT INTO id_gen VALUES('LANGUAGE_BOUNDS', 1);
INSERT INTO id_gen VALUES('LANGUAGE_MODEL', 1);
INSERT INTO id_gen VALUES('LANGUAGE_EDGE', 1);
INSERT INTO id_gen VALUES('LANGUAGE_SYMBOL', 1);
INSERT INTO id_gen VALUES('LANGUAGE_LIST', 1);
INSERT INTO id_gen VALUES('LIST', 1);
INSERT INTO id_gen VALUES('MODEL', 1);
INSERT INTO id_gen VALUES('MODEL_BREAKDOWN', 1);
INSERT INTO id_gen VALUES('OBJECT_LINK', 1);
INSERT INTO id_gen VALUES('REPOSITORY', 2);
INSERT INTO id_gen VALUES('SERVER_SETTING', 3);
INSERT INTO id_gen VALUES('SERVER_DIRECTORY', 1);
INSERT INTO id_gen VALUES('STORED_QUERY', 1);
INSERT INTO id_gen VALUES('SYMBOL', 1);
INSERT INTO id_gen VALUES('VERTEX', 1);
INSERT INTO id_gen VALUES('QLIK_VIEW_SYMBOL_PROPERTY', 1);
INSERT INTO id_gen VALUES('QLIK_VIEW_MODEL_PROPERTY', 1);
INSERT INTO id_gen VALUES('USER_DEFAULT_PERMISSIONS', 1);
INSERT INTO id_gen VALUES('USER_IMAGE', 1);
INSERT INTO id_gen VALUES('USER_LANGUAGE', 1);
INSERT INTO id_gen VALUES('SWIMLANE_SETTINGS', 1);
INSERT INTO id_gen VALUES('SWIMLANE_RELATION', 1);
INSERT INTO id_gen VALUES('EXTENSION_DOCUMENT', 7);
INSERT INTO id_gen VALUES('SNAPSHOT', 1);
INSERT INTO id_gen VALUES('LAYER', 7);
INSERT INTO id_gen VALUES('LANGUAGE_LAYER', 1);
INSERT INTO id_gen VALUES('REVISION_LOG_ITEM', 1);
INSERT INTO id_gen VALUES('CONFIG_TEMPLATE', 1);
INSERT INTO id_gen VALUES('PUBLISH_PROFILE', 1);
INSERT INTO id_gen VALUES('PUBLISH_TEMPLATE', 1);
INSERT INTO id_gen VALUES('LAYER_TYPE', 1);
INSERT INTO id_gen VALUES('WEB_COMPONENT', 1);
INSERT INTO id_gen (table_id, id) VALUES('PLUGIN_DATA', 1);
INSERT INTO id_gen (table_id, id) VALUES('REPOSITORY_VARIABLE', 1);
INSERT INTO id_gen (table_id, id) VALUES('LANGUAGE_REPOSITORY_VARIABLE', 1);
INSERT INTO id_gen (table_id, id) VALUES('REPOSITORY_TIMESTAMP', 1);
INSERT INTO id_gen (table_id, id) VALUES('MODEL_CONTEXT', 1);
INSERT INTO id_gen (table_id, id) VALUES('PUBLICATION', 1);
INSERT INTO id_gen (table_id, id) VALUES('NOTIFICATION_MODEL', 1);
INSERT INTO id_gen (table_id, id) VALUES('SUBSCRIBER', 1);
INSERT INTO id_gen (table_id, id) VALUES('CUSTOMER_GROUP', 1);
INSERT INTO id_gen (table_id, id) VALUES('REPOSITORY_ACCESS', 1);
INSERT INTO id_gen (table_id, id) VALUES('PERMISSION_GROUP', 1);
INSERT INTO id_gen (table_id, id) VALUES('INSTALLED_PLUGIN', 1);
INSERT INTO id_gen (table_id, id) VALUES ('LIVE_CONFIGURATION', 100);
INSERT INTO id_gen (table_id, id) VALUES ('LIVE_ACCESS_CONTROL', 100);
