drop database if exists covid_weekly;
create database covid_weekly;
use covid_weekly;

CREATE TABLE DT_SPACE
(
    COUNTRY    text,
    CONTINENT  text,
    POPULATION text
);

CREATE TABLE DT_TIME
(
    WEEK  text,
    YEAR  text,
    MONTH text
);

CREATE TABLE FT
(
    WEEK    text,
    COUNTRY text,
    DEATHS  int,
    CASES   int
);

CREATE TABLE `COLUMN`
(
    COLUMN_ID       VARCHAR(255) DEFAULT NULL,
    `COLUMN_NAME`   VARCHAR(255) DEFAULT NULL,
    COLUMN_TYPE     VARCHAR(255) DEFAULT NULL,
    ISKEY           bool         DEFAULT NULL,
    RELATIONSHIP_ID VARCHAR(255) DEFAULT NULL,
    TABLE_ID        VARCHAR(255)
);

CREATE TABLE `DATABASE`
(
    DATABASE_ID   VARCHAR(255) DEFAULT NULL,
    DATABASE_NAME VARCHAR(255) DEFAULT NULL,
    IPADDRESS     VARCHAR(16)  DEFAULT NULL,
    `PORT`        int
);

CREATE TABLE FACT
(
    FACT_ID       VARCHAR(255)  DEFAULT NULL,
    FACT_NAME     VARCHAR(255)  DEFAULT NULL,
    FACT_SYNONYMS VARCHAR(1000) DEFAULT NULL,
    DATABASE_ID   VARCHAR(255)
);

CREATE TABLE GROUPBYOPERATOR
(
    GROUPBYOPERATOR_ID       VARCHAR(255) DEFAULT NULL,
    GROUPBYOPERATOR_NAME     VARCHAR(255) DEFAULT NULL,
    GROUPBYOPERATOR_SYNONYMS VARCHAR(1000)
);

CREATE TABLE GROUPBYOPERATOR_OF_MEASURE
(
    GROUPBYOPERATOR_ID VARCHAR(255) DEFAULT NULL,
    MEASURE_ID         VARCHAR(255)
);

CREATE TABLE HIERARCHY
(
    HIERARCHY_ID       VARCHAR(255) DEFAULT NULL,
    HIERARCHY_NAME     VARCHAR(255) DEFAULT NULL,
    HIERARCHY_SYNONYMS VARCHAR(1000)
);

CREATE TABLE HIERARCHY_IN_FACT
(
    FACT_ID      VARCHAR(255) DEFAULT NULL,
    HIERARCHY_ID VARCHAR(255)
);

CREATE TABLE LANGUAGE_OPERATOR
(
    LANGUAGE_OPERATOR_ID       VARCHAR(255)  DEFAULT NULL,
    LANGUAGE_OPERATOR_NAME     VARCHAR(255)  DEFAULT NULL,
    LANGUAGE_OPERATOR_SYNONYMS VARCHAR(1000) DEFAULT NULL,
    LANGUAGE_OPERATOR_TYPE     VARCHAR(255)  DEFAULT NULL
);

CREATE TABLE LANGUAGE_PREDICATE
(
    LANGUAGE_PREDICATE_ID       VARCHAR(255)  DEFAULT NULL,
    LANGUAGE_PREDICATE_NAME     VARCHAR(255)  DEFAULT NULL,
    LANGUAGE_PREDICATE_SYNONYMS VARCHAR(1000) DEFAULT NULL,
    LANGUAGE_PREDICATE_TYPE     VARCHAR(255)  DEFAULT NULL
);

CREATE TABLE LEVEL
(
    LEVEL_ID          VARCHAR(255)  DEFAULT NULL,
    LEVEL_TYPE        VARCHAR(255)  DEFAULT NULL,
    LEVEL_DESCRIPTION VARCHAR(200)  DEFAULT NULL,
    LEVEL_NAME        VARCHAR(255)  DEFAULT NULL,
    CARDINALITY       int           DEFAULT NULL,
    HIERARCHY_ID      VARCHAR(255)  DEFAULT NULL,
    LEVEL_SYNONYMS    VARCHAR(1000) DEFAULT NULL,
    COLUMN_ID         VARCHAR(255)  DEFAULT NULL,
    MIN               FLOAT         DEFAULT NULL,
    MAX               FLOAT         DEFAULT NULL,
    AVG               FLOAT         DEFAULT NULL,
    ISDESCRIPTIVE     boolean       DEFAULT 0 DEFAULT NULL,
    MINDATE           DATE          DEFAULT NULL,
    MAXDATE           DATE          DEFAULT NULL
);

CREATE TABLE MEASURE
(
    MEASURE_ID       VARCHAR(255)  DEFAULT NULL,
    MEASURE_NAME     VARCHAR(255)  DEFAULT NULL,
    FACT_ID          VARCHAR(255)  DEFAULT NULL,
    MEASURE_SYNONYMS VARCHAR(1000) DEFAULT NULL,
    COLUMN_ID        VARCHAR(255)
);

CREATE TABLE MEMBER
(
    MEMBER_ID       VARCHAR(255) DEFAULT NULL,
    MEMBER_NAME     VARCHAR(255) DEFAULT NULL,
    LEVEL_ID        VARCHAR(255) DEFAULT NULL,
    MEMBER_SYNONYMS VARCHAR(1000)
);

CREATE TABLE OLAPSESSION
(
    TIMESTAMP               long          DEFAULT NULL,
    SESSION_ID              VARCHAR(255)  DEFAULT NULL,
    ANNOTATION_ID           VARCHAR(255)  DEFAULT NULL,
    VALUE_EN                VARCHAR(1000) DEFAULT NULL,
    VALUE_ITA               VARCHAR(1000) DEFAULT NULL,
    `LIMIT`                 LONG          DEFAULT NULL,
    FULLQUERY_SERIALIZED    BLOB          DEFAULT NULL,
    FULLQUERY_TREE          VARCHAR(1000) DEFAULT NULL,
    OLAPOPERATOR_SERIALIZED BLOB
);

CREATE TABLE QUERIES
(
    QUERY VARCHAR(1000) DEFAULT NULL,
    MC    VARCHAR(255)  DEFAULT NULL,
    GC    VARCHAR(255)  DEFAULT NULL,
    SC    VARCHAR(255)  DEFAULT null
);

CREATE TABLE RELATIONSHIP
(
    RELATIONSHIP_ID VARCHAR(255) DEFAULT NULL,
    TABLE1          VARCHAR(255) DEFAULT NULL,
    TABLE2          VARCHAR(255)
);

CREATE TABLE SYNONYM
(
    SYNONYM_ID   VARCHAR(255) DEFAULT NULL,
    TABLE_NAME   VARCHAR(255) DEFAULT NULL,
    REFERENCE_ID VARCHAR(255) DEFAULT NULL,
    TERM         VARCHAR(255)
);

CREATE TABLE `TABLE`
(
    TABLE_ID     VARCHAR(255) DEFAULT NULL,
    TABLE_NAME   VARCHAR(255) DEFAULT NULL,
    TABLE_TYPE   VARCHAR(255) DEFAULT NULL,
    FACT_ID      VARCHAR(255) DEFAULT NULL,
    HIERARCHY_ID VARCHAR(255) DEFAULT NULL
);
