DROP TABLE IF EXISTS article_color_lookup;
CREATE TABLE article_color_lookup (
    article_code INT NOT NULL,
    color_code INT NOT NULL,
    article_label VARCHAR(45),
    color_label VARCHAR(30),
    category VARCHAR(25),
    sale_price DECIMAL(8,2),
    family_name VARCHAR(20),
    family_code VARCHAR(3),
    PRIMARY KEY (article_code, color_code)
);

LOAD DATA LOCAL INFILE './emode/article_color_lookup.csv' INTO TABLE article_color_lookup FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
(
    article_code,
    color_code,
    article_label,
    color_label,
    category,
    sale_price,
    family_name,
    family_code
);

DROP TABLE IF EXISTS article_lookup;
CREATE TABLE article_lookup (
    article_code INT NOT NULL,
    article_label VARCHAR(45),
    category VARCHAR(25),
    sale_price DECIMAL(8,2),
    family_name VARCHAR(20),
    family_code VARCHAR(3),
    PRIMARY KEY (article_code)
);

LOAD DATA LOCAL INFILE './emode/article_lookup.csv' INTO TABLE article_lookup FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (
    article_code,
    article_label,
    category,
    sale_price,
    family_name,
    family_code
);

DROP TABLE IF EXISTS calendar_year_lookup;
CREATE TABLE calendar_year_lookup (
    week_key INT NOT NULL,
    week_in_year INT NOT NULL,
    year INT NOT NULL,
    fiscal_period VARCHAR(4),
    year_week VARCHAR(7) NOT NULL,
    quarter INT NOT NULL,
    month_name VARCHAR(10) NOT NULL,
    month INT NOT NULL,
    holiday_flag VARCHAR(1),
    PRIMARY KEY (week_key)
);

LOAD DATA LOCAL INFILE './emode/calendar_year_lookup.csv' INTO TABLE calendar_year_lookup FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
(
    week_key,
    week_in_year,
    year,
    fiscal_period,
    year_week,
    quarter,
    month_name,
    month,
    holiday_flag
);

DROP TABLE IF EXISTS outlet_lookup;
CREATE TABLE outlet_lookup (
    shop_name VARCHAR(30),
    address_1 VARCHAR(20),
    manager VARCHAR(10),
    date_open DATE NOT NULL,
    open VARCHAR(1),
    owned_outright VARCHAR(1),
    floor_space INT,
    zip_code VARCHAR(6),
    city VARCHAR(20),
    state VARCHAR(20),
    shop_code INT NOT NULL,
    PRIMARY KEY (shop_code)
);

LOAD DATA LOCAL INFILE './emode/outlet_lookup.csv' INTO TABLE outlet_lookup FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
(
    shop_name,
    address_1,
    manager,
    @date_open,
    open,
    owned_outright,
    floor_space,
    zip_code,
    city,
    state,
    shop_code
)
SET date_open = STR_TO_DATE(@date_open, "%d-%b-%y");

DROP TABLE IF EXISTS shop_facts;
CREATE TABLE shop_facts (
    id INT NOT NULL AUTO_INCREMENT,
    article_code INT NOT NULL,
    color_code INT NOT NULL,
    week_key INT NOT NULL,
    shop_code INT NOT NULL,
    margin DECIMAL(13,2) NOT NULL,
    amount_sold DECIMAL(13,2) NOT NULL,
    quantity_sold DECIMAL(13,2) NOT NULL,
    PRIMARY KEY (id)
);

LOAD DATA LOCAL INFILE './emode/shop_facts.csv' INTO TABLE shop_facts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (
    id,
    article_code,
    color_code,
    week_key,
    shop_code,
    margin,
    amount_sold,
    quantity_sold
);