DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS categories;

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
	name TEXT NOT NULL
);

CREATE TABLE items (
    ref INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    subscriberPrice REAL NOT NULL,
	category INTEGER NOT NULL,
	FOREIGN KEY (category) REFERENCES categories (id)
);

INSERT INTO categories (id, name)
    VALUES (1, "Boissons"),
	       (2, "Snacks");

INSERT INTO items (ref, name, price, subscriberPrice, category)
    VALUES (1, "Jus d'orange", 1.00, 0.50, 1),
           (2, "Coca-Cola",    1.50, 1.00, 1),
		   (3, "Twix",         1.10, 0.80, 2);