-- Database schema version 0 corresponds to what ceres v0.4.0 used.

PRAGMA foreign_keys = ON;

CREATE TABLE metadata (
	key   TEXT PRIMARY KEY,
	value TEXT
);

CREATE TABLE recipes (
	id           INTEGER PRIMARY KEY,
	title        TEXT    NOT NULL,
	portions     INTEGER NOT NULL,
	url          TEXT    NOT NULL,
	notes        TEXT    NOT NULL,
	created      INTEGER NOT NULL,     -- Unix time stamp
	last_changed INTEGER NOT NULL      -- Unix time stamp
);

CREATE TABLE steps (
	id      INTEGER PRIMARY KEY,
	'index' INTEGER NOT NULL,
	text    TEXT    NOT NULL,
	recipe  INTEGER NOT NULL,
	FOREIGN KEY(recipe) REFERENCES recipes(id)
);

CREATE TABLE ingredients (
	id      INTEGER PRIMARY KEY,
	'index' INTEGER NOT NULL,
	amount  REAL    NOT NULL,
	unit    TEXT    NOT NULL,
	'type'  TEXT    NOT NULL,
	step    INTEGER NOT NULL,
	FOREIGN KEY(step) REFERENCES steps(id)
);

INSERT INTO metadata
	(key, value)
VALUES
	('version', 'v0.4.0');