BEGIN TRANSACTION;
CREATE TABLE DailyStats (
    dateRep STRING,
    geoId INTEGER,
    cases INTEGER,
    deaths INTEGER,
    FOREIGN KEY (dateRep) REFERENCES DateInfo(dateRep),
    FOREIGN KEY (geoId) REFERENCES CountryInfo(geoId)
);

CREATE INDEX idx_entry_id on DailyStats(dateRep, geoId);

CREATE TABLE DateInfo (
    dateRep STRING PRIMARY KEY,
    day INTEGER,
    month INTEGER,
    year INTEGER,
    UNIQUE(dateRep, day, month, year)
);

CREATE TABLE CountryInfo (
    geoId STRING PRIMARY KEY,
    countriesAndTerritories STRING,
    ctc_id INTEGER,
    popData2018 INTEGER,
    FOREIGN KEY (ctc_id) REFERENCES ContinentInfo(ctc_id),
    UNIQUE(geoId, countriesAndTerritories, ctc_id, popData2018)
);

CREATE TABLE ContinentInfo(
    ctc_id INTEGER PRIMARY KEY AUTOINCREMENT,
    countryterritoryCode STRING,
    continentExp STRING,
    UNIQUE(countryterritoryCode, continentExp)
);
COMMIT;