drop database if exists foodmart;
create database if not exists foodmart;
use foodmart;

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID    INT         PRIMARY KEY,
    CITY           VARCHAR(30) DEFAULT NULL,
    STATE_PROVINCE VARCHAR(30) DEFAULT NULL,
    COUNTRY        VARCHAR(30) DEFAULT NULL,
    BIRTHDATE      DATE        DEFAULT NULL,
    MARITAL_STATUS VARCHAR(30) DEFAULT NULL,
    YEARLY_INCOME  VARCHAR(30) DEFAULT NULL,
    GENDER         VARCHAR(30) DEFAULT NULL,
    MEMBER_CARD    VARCHAR(30) DEFAULT NULL,
    OCCUPATION     VARCHAR(30) DEFAULT NULL,
    FULLNAME       VARCHAR(60) DEFAULT NULL,
    POPULATION     int
);

CREATE TABLE DATASET_PATRICK
(
    ID        int          PRIMARY KEY,
    ORIGIN    VARCHAR(255) DEFAULT NULL,
    GPSJ      VARCHAR(1)   DEFAULT NULL,
    QUERY     VARCHAR(255) DEFAULT NULL,
    MC        VARCHAR(255) DEFAULT NULL,
    GC        VARCHAR(255) DEFAULT NULL,
    SC        VARCHAR(255) DEFAULT NULL,
    MISSING   VARCHAR(255) DEFAULT NULL,
    AMBIGUITY VARCHAR(255) DEFAULT NULL,
    NOTES     VARCHAR(255) DEFAULT NULL
);

CREATE TABLE PRODUCT
(
    PRODUCT_ID          INT         PRIMARY KEY,
    BRAND_NAME          VARCHAR(60) DEFAULT NULL,
    PRODUCT_NAME        VARCHAR(60) DEFAULT NULL,
    PRODUCT_SUBCATEGORY VARCHAR(30) DEFAULT NULL,
    PRODUCT_CATEGORY    VARCHAR(30) DEFAULT NULL,
    PRODUCT_DEPARTMENT  VARCHAR(30) DEFAULT NULL,
    PRODUCT_FAMILY      VARCHAR(30)
);

CREATE TABLE PROMOTION
(
    PROMOTION_ID   int          PRIMARY KEY,
    PROMOTION_NAME VARCHAR(45)  DEFAULT NULL,
    MEDIA_TYPE     VARCHAR(45)  DEFAULT NULL,
    COST           int          DEFAULT NULL,
    PSTART_DATE    VARCHAR(100) DEFAULT NULL,
    PEND_DATE      VARCHAR(100)
);

CREATE TABLE STORE
(
    STORE_ID      int         PRIMARY KEY,
    STORE_TYPE    VARCHAR(45) DEFAULT NULL,
    STORE_NAME    VARCHAR(45) DEFAULT NULL,
    STORE_CITY    VARCHAR(45) DEFAULT NULL,
    STORE_STATE   VARCHAR(45) DEFAULT NULL,
    STORE_COUNTRY VARCHAR(45) DEFAULT NULL,
    COFFEE_BAR    INT         DEFAULT NULL,
    VIDEO_STORE   INT         DEFAULT NULL,
    SALAD_BAR     INT         DEFAULT NULL,
    FLORIST       INT         DEFAULT NULL
);

CREATE TABLE TIME_BY_DAY
(
    TIME_ID   INT         PRIMARY KEY,
    THE_DATE  DATE        DEFAULT NULL,
    THE_MONTH VARCHAR(30) DEFAULT NULL,
    THE_YEAR  INT         DEFAULT NULL,
    `QUARTER` VARCHAR(30)
);

CREATE TABLE TIME_BY_DAY_OLD
(
    TIME_ID   INT         PRIMARY KEY,
    THE_DATE  DATE        DEFAULT NULL,
    THE_MONTH VARCHAR(30) DEFAULT NULL,
    THE_YEAR  DATE        DEFAULT NULL,
    QUARTER   VARCHAR(30)
);

CREATE TABLE SALES_FACT_1997
(
    PRODUCT_ID   INT references PRODUCT(PRODUCT_ID),
    TIME_ID      INT references TIME_BY_DAY(TIME_ID),
    CUSTOMER_ID  INT references CUSTOMER(CUSTOMER_ID),
    PROMOTION_ID INT references PROMOTION(PROMOTION_ID),
    STORE_ID     INT references STORE(STORE_ID),
    STORE_SALES  FLOAT,
    STORE_COST   FLOAT,
    UNIT_SALES   FLOAT,
    PRIMARY KEY(PRODUCT_ID, TIME_ID, CUSTOMER_ID, PROMOTION_ID, STORE_ID)
);