PRAGMA FOREIGN_KEYS = off;

DROP TABLE IF EXISTS assembly_line;
DROP TABLE IF EXISTS flavor;
DROP TABLE IF EXISTS freezer;
DROP TABLE IF EXISTS recipe;
DROP TABLE IF EXISTS recipe_mixins;
DROP TABLE IF EXISTS container;
DROP TABLE IF EXISTS mixin;

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 primary key,
    cpc real,
    freezer integer,
    foreign key (freezer) references freezer(id)
);

CREATE TABLE freezer (
    id integer primary key,
    capacity integer,
    load integer
);

CREATE TABLE recipe (
    name text,
    baseflavor text,
    co integer,
    primary key (name),
    foreign key (baseflavor) references flavor(name)
);

CREATE TABLE mixin (
    mixin text primary key,
    needs_ref text,
    cpoz real
);

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 primary key,
    ref text,
    capacity integer,
    load integer,
    mixin text,
    foreign key (mixin) references mixin(mixin)
);

INSERT INTO flavor VALUES ('Chocolate',.75,1);
INSERT INTO flavor VALUES ('Banana',.85,17);
INSERT INTO freezer VALUES (17,NULL,100);
INSERT INTO freezer VALUES (1,1000,500);
INSERT INTO flavor VALUES ('Vanilla',.55,29);
INSERT INTO freezer VALUES (29,500,500);
INSERT INTO mixin (needs_ref,mixin) VALUES ('no','HB Chunks');
INSERT INTO container VALUES
    (1,'no',500,500,'HB Chunks'),
    (2,'no',500,500,'HB Chunks'),
    (3,'no',500,500,'HB Chunks');
INSERT INTO mixin VALUES ('Cherries','yes',.15);
INSERT INTO container VALUES
    (4,'yes',800,400,'Cherries');
INSERT INTO recipe VALUES
    ('MMV','Vanilla',NULL);
INSERT INTO assembly_line VALUES
    (1,'MMV','no',25),
    (2,'MMV','yes',25),
    (3,'MMV','yes',40);
INSERT INTO mixin VALUES
    ('Fudge flakes','yes',NULL),
    ('Pecans','no',NULL),
    ('Walnuts','no',NULL),
    ('Almonds','no',NULL);
INSERT INTO mixin VALUES
    ('Marshmallow','no',NULL);
INSERT INTO recipe_mixins VALUES
    ('NYSFC','Fudge flakes'),
    ('NYSFC','Pecans'),
    ('NYSFC','Walnuts'),
    ('NYSFC','Almonds');
INSERT INTO recipe VALUES
    ('NYSFC','Chocolate',NULL);
INSERT INTO recipe VALUES
    ('VHBC','Vanilla',NULL);
INSERT INTO recipe_mixins VALUES
    ('VHBC','HB Chunks');
INSERT INTO flavor VALUES
    ('Caramel',NULL,NULL);
INSERT INTO recipe VALUES
    ('OSW','Caramel',NULL);
INSERT INTO recipe_mixins VALUES
    ('OSW','Marshmallow'),
    ('OSW','Fudge flakes');
INSERT INTO recipe VALUES
    ('CM','Banana',400);
INSERT INTO recipe_mixins VALUES
    ('CM','Walnuts'),
    ('CM','Fudge flakes');



PRAGMA FOREIGN_KEYS = on;
