PRAGMA FOREIGN_KEYS = OFF;
DROP TABLE IF EXISTS ASSEMBLY_LINE;
DROP TABLE IF EXISTS MIXIN;
DROP TABLE IF EXISTS FLAVOR;
DROP TABLE IF EXISTS RECIPE;
DROP TABLE IF EXISTS RECIPE_MIXINS;
DROP TABLE IF EXISTS CONTAINER;
DROP TABLE IF EXISTS FREEZER;
PRAGMA FOREIGN_KEYS = ON;

CREATE TABLE ASSEMBLY_LINE (
    NUMBER integer PRIMARY KEY,
    RECIPE text,
    MIXINS text,
    CAPACITY integer,
    FOREIGN KEY (RECIPE) REFERENCES RECIPE(NAME)
);

CREATE TABLE FLAVOR (
    NAME text,
    COST_PER_CARTON real,
    FREEZER integer,
    PRIMARY KEY (NAME),
    FOREIGN KEY (FREEZER) REFERENCES FREEZER(ID),
    UNIQUE(FREEZER)
);

CREATE TABLE FREEZER (
    ID integer,
    CAPACITY integer,
    LOAD integer,
    PRIMARY KEY (ID)
);

CREATE TABLE RECIPE (
    NAME text PRIMARY KEY,
    BASEFLAVOR text,
    CARTONS_ORDERED integer,
    FOREIGN KEY (BASEFLAVOR) REFERENCES FLAVOR(NAME)
);

CREATE TABLE MIXIN (
    MIXIN text,
    NEEDS_REF text,
    COST_PER_OZ real, 
    PRIMARY KEY (MIXIN)
);

CREATE TABLE RECIPE_MIXINS (
    RECIPE_NAME text,
    MIXIN text,
    PRIMARY KEY (RECIPE_NAME, MIXIN),
    FOREIGN KEY (RECIPE_NAME) REFERENCES RECIPE(NAME),
    FOREIGN KEY (MIXIN) REFERENCES MIXIN(MIXIN)
);

CREATE TABLE CONTAINER (
    ID integer,
    REF text,
    CAPACITY integer,
    LOAD integer,
    MIXIN text,
    PRIMARY KEY (ID),
    FOREIGN KEY (MIXIN) REFERENCES MIXIN(MIXIN)
);

INSERT INTO FLAVOR (NAME, COST_PER_CARTON) VALUES ('chocolate', 0.75);
INSERT INTO FLAVOR VALUES ('banana', 0.85, NULL);
INSERT INTO FREEZER (ID, LOAD) VALUES (17, 100);
UPDATE FLAVOR set FREEZER=17 where name='banana';
INSERT INTO FREEZER (ID, CAPACITY, LOAD) VALUES (29, 500, 500);
INSERT INTO FLAVOR (NAME,COST_PER_CARTON,FREEZER) VALUES ('vanilla',.55,29);
INSERT INTO MIXIN VALUES ('Heath bar', 'yes', .1);
INSERT INTO CONTAINER VALUES (1, 'yes', 500, 500, 'Heath bar');
INSERT INTO CONTAINER VALUES (2, 'yes', 500, 500, 'Heath bar');
INSERT INTO CONTAINER VALUES (3, 'yes', 500, 500, 'Heath bar');
INSERT INTO ASSEMBLY_LINE VALUES (1, NULL, 'yes', 40),
                                 (2, NULL, 'yes', 25),
                                 (3, NULL, 'no', 25);
INSERT INTO RECIPE VALUES ('NYSFC', 'chocolate', 100000);
INSERT INTO MIXIN VALUES ('almonds','no',.999);
INSERT INTO MIXIN VALUES ('walnuts','no',.999);
INSERT INTO MIXIN VALUES ('pecans','no',.999);
INSERT INTO MIXIN VALUES ('fudge flakes','yes',.999);
INSERT INTO RECIPE_MIXINS VALUES ('NYSFC', 'almonds');
INSERT INTO RECIPE_MIXINS VALUES ('NYSFC', 'walnuts');
INSERT INTO RECIPE_MIXINS VALUES ('NYSFC', 'fudge flakes');
INSERT INTO RECIPE_MIXINS VALUES ('NYSFC', 'pecans');