-- Homebrew recipe log database
-- TODO
--  - Separate recipe revisions and brew days
--  - Clean up the database structure
--  - More fields (basically everything that beerxml does)

CREATE TABLE style
(
	id SERIAL,
	name text,
	og_low real,
	og_high real,
	fg_low real,
	fg_high real,
	ibu_low integer,
	ibu_high integer,
	srm_low integer, -- °L/SRM
	srm_high integer,
	PRIMARY KEY (id)
);

-- A master recipe just stores the name and basic description of the
-- recipe, and serves as the top level for recipe revisions which
-- contain the actual steps. All recipe_ids in the other tables refer
-- to the revision.

CREATE TABLE master_recipe
(
	name VARCHAR (64) UNIQUE NOT NULL,
	description TEXT,
	style_id integer,
	PRIMARY KEY (name),
	FOREIGN KEY (style_id) REFERENCES style (id)
);

-- A recipe steps consists of a revision, a set of steps (e.g. "Steep
-- grain at 160F for 30 Minutes"), ingredients (split into
-- hops/grains/yeast/misc), and notes (split into recipe/batch [brew
-- day, during fermentation, bottle day], and tasting with timestamps
-- added to each [e.g. to note how the fermentation is going two or
-- three times, or to add tasting notes for a large beer when it is
-- fresh and after it has aged]).

CREATE TABLE recipe_revision
(
	id SERIAL,
	recipe_name VARCHAR (64) NOT NULL,
	revision INTEGER,
	batch_size real, -- liters
	boil_size  real, -- liters (for the boil)
	og         real, -- actual original gravity
	fg         real, -- actual final gravity
	PRIMARY KEY (id),
	UNIQUE (recipe_name, revision),
	FOREIGN KEY (recipe_name) REFERENCES master_recipe (name)
);

CREATE TABLE recipe_step
(
	recipe_id integer,
	step integer,
	direction text,
	UNIQUE (recipe_id, step),
	FOREIGN KEY (recipe_id) REFERENCES recipe_revision (id)
);

CREATE TABLE recipe_hop
(
	recipe_id integer,
	hop_id    integer,
	quantity  real, -- kg
	alpha     real, -- actual AA% of hop used
	time      time, -- addition time
	FOREIGN KEY (recipe_id) REFERENCES recipe_revision (id),
	FOREIGN KEY (hop_id)    REFERENCES hop (id)
);

CREATE TABLE recipe_grain
(
	recipe_id integer,
	grain_id  integer,
	quantity  real, -- kg
	yield     real, -- % actual yield (if steeped or mashed, nil if extract)
	FOREIGN KEY (recipe_id)   REFERENCES recipe_revision (id),
	FOREIGN KEY (grain_id)    REFERENCES grain (id)
);

CREATE TABLE recipe_yeast
(
	recipe_id integer,
	yeast_id  integer,
	attenuation real, -- % actual attenuation
	FOREIGN KEY (recipe_id) REFERENCES recipe_revision (id),
	FOREIGN KEY (yeast_id)  REFERENCES yeast           (id)
);

CREATE TABLE recipe_note
(
	recipe_id integer,
	type      varchar (32), -- recipe, batch, fermentation, bottling, 
				-- tasting
	note      text,
	when      timestamp,
	FOREIGN KEY (recipe_id) REFERENCES recipe_revision (id)
);

CREATE TABLE hop
(
	id SERIAL,
	name text,
	alpha real, -- AA%
	form  varchar (32), -- pellet/plug/whole
	PRIMARY KEY (id)
);

CREATE TABLE grain
(
	id SERIAL,
	name text,
	color real, -- °L
	extract real, -- max gravity of 1kg/1L water
	use varchar (64), -- TODO: use_id + uses table
	PRIMARY KEY (id)
);

CREATE TABLE yeast
(
	id SERIAL,
	name text,
	attenuation_min real, -- % attenuation
	attenuation_max real, -- % attenuation
	temp_min        real, -- ° C
	temp_max        real, -- ° C
	PRIMARY KEY (id)
);