--
-- The cocoa producers
--
CREATE TABLE IF NOT EXISTS producers(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    country_name TEXT NOT NULL
);

INSERT INTO producers(name, country_name) VALUES ('Jose Garcia', 'Peru');
INSERT INTO producers(name, country_name) VALUES ('Cacao Tree', 'Brazil');
INSERT INTO producers(name, country_name) VALUES ('Alfonso', 'Benin');
INSERT INTO producers(name, country_name) VALUES ('Black Cats', 'Ghana');
INSERT INTO producers(name, country_name) VALUES ('Sweet Coop', 'Ghana');
INSERT INTO producers(name, country_name) VALUES ('Daily Trumpets', 'Peru');
INSERT INTO producers(name, country_name) VALUES ('White Hats', 'Salvador');
INSERT INTO producers(name, country_name) VALUES ('Famous Trees', 'Ethiopia');

--
-- The chocolate bars
--
CREATE TABLE IF NOT EXISTS chocolates(
    producer_id INTEGER NOT NULL,
    cocoa_level INTEGER NOT NULL,
    price REAL NOT NULL,
    type TEXT NOT NULL,
    CONSTRAINT pk_chocolates PRIMARY KEY (producer_id, cocoa_level),
    CONSTRAINT fk_producer_id FOREIGN KEY (producer_id) REFERENCES producers(id)
);

INSERT INTO chocolates(producer_id, cocoa_level, price, type) VALUES (3, 50, 2, 'black');
INSERT INTO chocolates(producer_id, cocoa_level, price, type) VALUES (1, 75, 2.3, 'black');
INSERT INTO chocolates(producer_id, cocoa_level, price, type) VALUES (4, 30, 1.8, 'white');
INSERT INTO chocolates(producer_id, cocoa_level, price, type) VALUES (8, 50, 1.5, 'black with almonds');
INSERT INTO chocolates(producer_id, cocoa_level, price, type) VALUES (5, 65, 1.9, 'black with grapefruit');
INSERT INTO chocolates(producer_id, cocoa_level, price, type) VALUES (4, 45, 2.3, 'milk');
INSERT INTO chocolates(producer_id, cocoa_level, price, type) VALUES (5, 45, 2.5, 'milk with nuts');
INSERT INTO chocolates(producer_id, cocoa_level, price, type) VALUES (6, 85, 3.1, 'strong black');
INSERT INTO chocolates(producer_id, cocoa_level, price, type) VALUES (2, 75, 2.3, 'black');


