PRAGMA foreign_keys = ON;

CREATE TABLE instances (
	id           INTEGER PRIMARY KEY,
	uuid         TEXT    NOT NULL,
	name         TEXT    NOT NULL,
	is_self      BOOLEAN NOT NULL,
	last_changed INTEGER NOT NULL
);

CREATE TABLE users (
	id           INTEGER PRIMARY KEY,
	uuid         TEXT    NOT NULL,
	name         TEXT    NOT NULL,
	home         INTEGER NOT NULL,
	last_changed INTEGER NOT NULL,
	FOREIGN KEY(home) REFERENCES instances(id)
);

CREATE TABLE recipes (
	id   INTEGER PRIMARY KEY,
	uuid TEXT    NOT NULL
);

CREATE TABLE recipe_versions (
	id           INTEGER PRIMARY KEY,
	parent       INTEGER NOT NULL,
	recipe       INTEGER NOT NULL,
	author       INTEGER NOT NULL,
	instance     INTEGER NOT NULL,
	language     TEXT    NOT NULL,
	title        TEXT    NOT NULL,
	portions     INTEGER NOT NULL,
	url          TEXT    NOT NULL,
	notes        TEXT    NOT NULL,
	last_changed INTEGER NOT NULL,
	FOREIGN KEY(parent)   REFERENCES recipe_versions(id),
	FOREIGN KEY(recipe)   REFERENCES recipes(id),
	FOREIGN KEY(author)   REFERENCES users(id),
	FOREIGN KEY(instance) REFERENCES instances(id)
);

CREATE TABLE steps (
	id   INTEGER PRIMARY KEY,
	text TEXT    NOT NULL
);

CREATE TABLE ingredients (
	id            INTEGER PRIMARY KEY,
	ingredient_id TEXT    NOT NULL,
	amount        REAL    NOT NULL,
	unit          INTEGER NOT NULL -- must match unit enum from application code
);

CREATE TABLE foodstuffs (
	id     INTEGER PRIMARY KEY,
	uuid   TEXT    NOT NULL,
	name   TEXT    NOT NULL, -- in US english
	plural TEXT              -- in US english or NULL if uncountable
);

CREATE TABLE products (
	id   INTEGER PRIMARY KEY,
	uuid TEXT    NOT NULL,
	name TEXT    NOT NULL,
	gtin TEXT
);
