CREATE TABLE `DATABASE`
(
    database_id   varchar(255) NOT NULL,
    database_name varchar(255) NOT NULL,
    IPaddress     varchar(16)  NOT NULL,
    port          int       NOT NULL,
    PRIMARY KEY (database_id),
    UNIQUE (database_name, IPaddress, port)
);

CREATE TABLE GROUPBYOPERATOR
(
    groupbyoperator_id       varchar(255) NOT NULL,
    groupbyoperator_name     varchar(255) NOT NULL UNIQUE,
    groupbyoperator_synonyms varchar(1000),
    PRIMARY KEY (groupbyoperator_id)
);

CREATE TABLE HIERARCHY
(
    hierarchy_id       varchar(255) NOT NULL,
    hierarchy_name     varchar(255) NOT NULL UNIQUE,
    hierarchy_synonyms varchar(1000),
    PRIMARY KEY (hierarchy_id)
);

CREATE TABLE FACT
(
    fact_id       varchar(255) NOT NULL,
    fact_name     varchar(255) NOT NULL UNIQUE,
    fact_synonyms varchar(1000),
    database_id   varchar(255) NULL REFERENCES `DATABASE` (database_id) ON DELETE CASCADE,
    PRIMARY KEY (fact_id)
);

CREATE TABLE `TABLE`
(
    table_id     varchar(255) NOT NULL,
    table_name   varchar(255) NOT NULL UNIQUE,
    table_type   varchar(255) NOT NULL,
    fact_id      varchar(255) DEFAULT NULL REFERENCES fact (fact_id),
    hierarchy_id varchar(255) DEFAULT NULL REFERENCES hierarchy (hierarchy_id) ON DELETE CASCADE,
    PRIMARY KEY (table_id)
);

CREATE TABLE RELATIONSHIP
(
    relationship_id varchar(255) NOT NULL,
    table1          varchar(255) NOT NULL REFERENCES `TABLE` (table_id) ON DELETE CASCADE,
    table2          varchar(255) NOT NULL REFERENCES `TABLE` (table_id) ON DELETE CASCADE,
    PRIMARY KEY (relationship_id)
);

CREATE TABLE `COLUMN`
(
    column_id       varchar(255) NOT NULL,
    column_name     varchar(255) NOT NULL,
    column_type     varchar(255) NOT NULL,
    isKey           int NOT NULL,
    relationship_id varchar(255) DEFAULT NULL,
    table_id        varchar(255) NOT NULL REFERENCES `TABLE` (table_id) ON DELETE CASCADE,
    PRIMARY KEY (column_id) -- , UNIQUE (column_name, table_id)
);

CREATE TABLE LEVEL
(
    level_id          varchar(255) NOT NULL,
    level_type        varchar(255) NOT NULL,
    level_description varchar(200),
    level_name        varchar(255) NOT NULL UNIQUE,
    cardinality       int           DEFAULT NULL,
    hierarchy_id      varchar(255) NOT NULL REFERENCES HIERARCHY (hierarchy_id) ON DELETE CASCADE,
    level_synonyms    varchar(1000),
    column_id         varchar(255) NOT NULL REFERENCES `COLUMN` (column_id),
    MIN             DOUBLE PRECISION DEFAULT NULL,
    MAX             DOUBLE PRECISION DEFAULT NULL,
    AVG             DOUBLE PRECISION DEFAULT NULL,
    isDescriptive     int DEFAULT 0,
    mindate           DATE             DEFAULT NULL,
    maxdate           DATE             DEFAULT NULL,
    PRIMARY KEY (level_id)
);

CREATE TABLE HIERARCHY_IN_FACT
(
    fact_id      varchar(255) NOT NULL REFERENCES fact (fact_id),
    hierarchy_id varchar(255) NOT NULL REFERENCES hierarchy (hierarchy_id) ON DELETE CASCADE,
    PRIMARY KEY (fact_id, hierarchy_id)
);

CREATE TABLE LANGUAGE_PREDICATE
(
    language_predicate_id       varchar(255) NOT NULL,
    language_predicate_name     varchar(255) NOT NULL UNIQUE,
    language_predicate_synonyms varchar(1000) DEFAULT NULL,
    language_predicate_type     varchar(255)  DEFAULT NULL,
    PRIMARY KEY (language_predicate_id)
);

CREATE TABLE LANGUAGE_OPERATOR
(
    language_operator_id       varchar(255) NOT NULL,
    language_operator_name     varchar(255) NOT NULL UNIQUE,
    language_operator_synonyms varchar(1000) DEFAULT NULL,
    language_operator_type     varchar(255)  DEFAULT NULL,
    PRIMARY KEY (language_operator_id)
);

CREATE TABLE MEASURE
(
    measure_id       varchar(255) NOT NULL,
    measure_name     varchar(255) NOT NULL,
    fact_id          varchar(255) NOT NULL REFERENCES fact (fact_id),
    measure_synonyms varchar(1000),
    column_id        varchar(255) NOT NULL REFERENCES `COLUMN` (column_id) ON DELETE CASCADE,
    PRIMARY KEY (measure_id),
    UNIQUE (measure_name, fact_id)
);

CREATE TABLE MEMBER
(
    member_id       varchar(255) NOT NULL,
    member_name     varchar(255) NOT NULL,
    level_id        varchar(255) NOT NULL REFERENCES LEVEL (level_id) ON DELETE CASCADE,
    member_synonyms varchar(1000),
    PRIMARY KEY (member_id),
    UNIQUE (member_name, level_id)
);

CREATE TABLE GROUPBYOPERATOR_OF_MEASURE
(
    groupbyoperator_id varchar(255) NOT NULL REFERENCES groupbyoperator (groupbyoperator_id) ON DELETE CASCADE,
    measure_id         varchar(255) NOT NULL REFERENCES measure (measure_id) ON DELETE CASCADE,
    PRIMARY KEY (groupbyoperator_id, measure_id)
);

CREATE TABLE SYNONYM
(
    synonym_id   varchar(255) NOT NULL,
    table_name   varchar(255) NOT NULL,
    reference_id varchar(255) NOT NULL, -- id of the Entity in the given table
    TERM       varchar(255) NOT NULL,
    PRIMARY KEY (synonym_id),
    UNIQUE (term, reference_id, table_name)
);

CREATE TABLE OLAPSESSION
(
    TIMESTAMP             int,
    session_id              varchar(255),
    annotation_id           varchar(255),
    value_en                varchar(1000),
    value_ita               varchar(1000),
    `LIMIT`                   long,
    fullquery_serialized    blob,
    fullquery_tree          varchar(1000),
    olapoperator_serialized blob
);

CREATE TABLE QUERIES
(
    `query` varchar(1000),
    `mc`    varchar(255) default null,
    `gc`    varchar(255) default null,
    `sc`    varchar(255) default null
);