/* sqlite commands for creating databases used by ruby on rails as the foundation
of a recipe management software application. */


CREATE TABLE units (

/* table that keeps track of metric/standard units for conversions
the multiplier is based off of one base unit (probably liter, gram, etc) */

	id		INTEGER PRIMARY KEY,
	name		varchar(50),
	short		varchar(5),
	multiplier	INTEGER
);

CREATE TABLE foods (

/* food table, will include nutrition information for each food */
	id		INTEGER PRIMARY KEY,
	name		varchar(100)
);

CREATE TABLE tags (

/* tags can be assigned to any number of recipes for sorting purposes. */
	id		INTEGER PRIMARY KEY,
	name		varchar(50),
	description	TEXT
);

CREATE TABLE recipes (

/* Basic recipe table, recipe level nutrition data will be stored here in the future */

	id		INTEGER PRIMARY KEY,
	name		VARCHAR(255),
	created_at	DATETIME,
	modified_at	DATETIME,
	image_url	VARCHAR(255),
	source		VARCHAR(100),	
	description	TEXT,
	yield_number	INTEGER,
	yield_unit	varchar(50),
	rating		INTEGER
);

CREATE TABLE recipes_link (

/* allows for a self-referential many-to-many relationship between recipes
so that a recipe can include other recipes (eg: spaghetti includes marinarra sauce).

It will be important to check that parent_id and child_id are not the same in order
to avoid adding a recipe to itself and causing a loop */

	parent_id	INTEGER NOT NULL,
	child_id	INTEGER NOT NULL,
	FOREIGN KEY (parent_id) REFERENCES recipes,
	FOREIGN KEY (child_id) REFERENCES recipes
);

CREATE TABLE recipes_tags (

/* table used for the many-to-many relationship of tags and recipes.
In ruby on rails, this is has_and_belongs_to_many */

	recipe_id	INTEGER NOT NULL,
	tag_id		INTEGER NOT NULL,
	FOREIGN KEY (recipe_id) REFERENCES recipes,
	FOREIGN KEY (tag_id) REFERENCES tags
);


CREATE TABLE ingredients (

/* ingredient in a recipe, each will belong to one recipe, point to a single food item,
and also point to a single unit */

        id              INTEGER PRIMARY KEY,
        food_id         INTEGER NOT NULL,
        ammount         INTEGER,
        unit_id         INTEGER NOT NULL,
        recipe_id       INTEGER NOT NULL,
        preparation     TEXT,
        FOREIGN KEY (food_id) REFERENCES foods,
        FOREIGN KEY (unit_id) REFERENCES units,
        FOREIGN KEY (recipe_id) REFERENCES recipes ON DELETE CASCADE
);

CREATE TABLE steps (

/* table for recipe steps, allows each recipe to hold infinite steps.
Possible future additions may be adding images to steps. */
        id              INTEGER PRIMARY KEY,
        recipe_id       INTEGER NOT NULL,
        directions      TEXT,
        FOREIGN KEY (recipe_id) REFERENCES recipes ON DELETE CASCADE
);