DROP TRIGGER recipe_fti_trigger on tb_c_recipe;
DROP TRIGGER step_fti_trigger on tb_c_recipe_step;
DROP TRIGGER ing_fti_trigger on tb_c_ingredient;
DROP FUNCTION fti() CASCADE;

DROP INDEX recipe_fti_string_idx;
DROP INDEX recipe_fti_id_idx;
DROP INDEX recipe_fti_oid_idx;

DROP INDEX recipe_step_fti_string_idx;
DROP INDEX recipe_step_fti_id_idx;
DROP INDEX recipe_step_fti_oid_idx;

DROP INDEX ingredient_fti_string_idx;
DROP INDEX ingredient_fti_id_idx;
DROP INDEX ingredient_fti_oid_idx;

DROP TABLE tb_c_recipe_fti;
DROP TABLE tb_c_step_fti;
DROP TABLE tb_c_ingredient_fti;

DROP TABLE tb_c_recipe_ingredient;
DROP TABLE tb_c_ingredient_group_member;
DROP TABLE tb_c_ingredient_group;
DROP TABLE tb_c_ingredient;
DROP TABLE tb_c_recipe_category_member;
DROP TABLE tb_c_recipe_category;
DROP TABLE tb_c_recipe_step;
DROP TABLE tb_c_recipe;
DROP TABLE tb_c_unit;
DROP SEQUENCE seq_tb_recipe;
DROP SEQUENCE seq_tb_unit;
DROP SEQUENCE seq_tb_ingredient;
DROP SEQUENCE seq_tb_ingredient_group;
DROP SEQUENCE seq_tb_recipe_category;


DROP FUNCTION sp_delete_unit(integer);
DROP FUNCTION sp_insert_unit(varchar, varchar,varchar);
DROP FUNCTION sp_update_unit(integer,varchar, varchar, varchar);

DROP FUNCTION sp_insert_recipe_ingredient(integer, NUMERIC(2), integer, integer);
DROP FUNCTION sp_delete_recipe_ingredient(integer, integer);

DROP FUNCTION sp_delete_ingredient(integer);
DROP FUNCTION sp_insert_ingredient(varchar, varchar);
DROP FUNCTION sp_update_ingredient(integer, varchar, varchar);

DROP FUNCTION sp_move_ingredient(integer, integer, integer);
DROP FUNCTION sp_insert_ingredient_group_member(integer, integer);
DROP FUNCTION sp_delete_ingredient_group_member(integer, integer);

DROP FUNCTION sp_insert_ingredient_group(varchar, integer);
DROP FUNCTION sp_delete_ingredient_group(integer);

DROP FUNCTION sp_insert_recipe_category(varchar, integer);
DROP FUNCTION sp_delete_recipe_category(integer);

DROP FUNCTION sp_move_recipe(integer, integer, integer);
DROP FUNCTION sp_insert_recipe_category_member(integer, integer);

DROP FUNCTION sp_insert_recipe_step(integer, varchar, varchar);
DROP FUNCTION sp_insert_recipe_step(integer, integer, varchar, varchar);
DROP FUNCTION sp_delete_recipe_step(integer, integer);
DROP FUNCTION sp_update_recipe_step(integer, integer, integer, varchar, varchar);

DROP FUNCTION sp_insert_recipe(varchar, varchar, varchar);
DROP FUNCTION sp_update_recipe(integer, varchar, varchar, varchar);

CREATE FUNCTION fti() RETURNS TRIGGER AS '/usr/lib/postgresql/lib/fti.so' LANGUAGE 'C';

CREATE SEQUENCE "seq_tb_recipe" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1;
CREATE SEQUENCE "seq_tb_unit" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1;
CREATE SEQUENCE "seq_tb_ingredient" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1;
CREATE SEQUENCE "seq_tb_ingredient_group" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1;
CREATE SEQUENCE "seq_tb_recipe_category" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1;

-- Create tables

CREATE TABLE tb_c_unit (id int4 DEFAULT nextval('seq_tb_unit'::text) PRIMARY KEY,
												label	VARCHAR(64),
												short_fmt VARCHAR(6),
												long_fmt	VARCHAR(64));

CREATE TABLE tb_c_recipe(id int4 PRIMARY KEY,
												 title 				VARCHAR(512) NOT NULL,
												 description 	VARCHAR(4096),
												 source				VARCHAR(255));

CREATE TABLE tb_c_recipe_fti(string VARCHAR(5120), id oid);
CREATE TABLE tb_c_step_fti(string VARCHAR(4351), id oid);
CREATE TABLE tb_c_ingredient_fti(string VARCHAR(10000000), id oid);

CREATE TABLE tb_c_recipe_step(recipe_id int4,
															id int4,
															ordernum int4,
															label VARCHAR(255),
															description	VARCHAR(4096),
															PRIMARY KEY (recipe_id, id),
															FOREIGN KEY (recipe_id) REFERENCES tb_c_recipe (id));
															
CREATE TABLE tb_c_ingredient(id int4 PRIMARY KEY,
														 label 	VARCHAR(255),
														 description VARCHAR(4096));

CREATE TABLE tb_c_recipe_ingredient(recipe_id int4,
																		id int4,
																		amount NUMERIC(2),
																		unit_id int4,
																		ingredient_id int4,
																		PRIMARY KEY(recipe_id, id),
																		FOREIGN KEY(recipe_id) REFERENCES tb_c_recipe(id),
																		FOREIGN KEY(unit_id) REFERENCES tb_c_unit(id),
																		FOREIGN KEY(ingredient_id) REFERENCES tb_c_ingredient(id));

CREATE TABLE tb_c_ingredient_group(id int4 DEFAULT nextval('seq_tb_ingredient_group'::text) PRIMARY KEY,
																	 label VARCHAR(255),
																	 parent_group_id int4,
																	 FOREIGN KEY (parent_group_id) REFERENCES tb_c_ingredient_group(id));

CREATE TABLE tb_c_ingredient_group_member(group_id int4,
																					ingredient_id int4,
																					PRIMARY KEY (group_id, ingredient_id),
																					FOREIGN KEY (group_id) REFERENCES tb_c_ingredient_group(id),
																					FOREIGN KEY (ingredient_id) REFERENCES tb_c_ingredient(id));
																					
CREATE TABLE tb_c_recipe_category(id int4 DEFAULT nextval('seq_tb_recipe_category'::text) PRIMARY KEY,
																	label VARCHAR(255),
																	parent_recipe_category_id int4,
																	FOREIGN KEY (parent_recipe_category_id) REFERENCES tb_c_recipe_category(id));

CREATE TABLE tb_c_recipe_category_member(recipe_category_id int4,
																				 recipe_id int4,
																				 PRIMARY KEY (recipe_category_id, recipe_id),
																				 FOREIGN KEY (recipe_category_id) REFERENCES tb_c_recipe_category(id),
																				 FOREIGN KEY (recipe_id) REFERENCES tb_c_recipe(id));

-- Triggers for FTI
CREATE TRIGGER "step_fti_trigger" AFTER UPDATE OR INSERT OR DELETE
ON tb_c_recipe_step FOR EACH ROW
EXECUTE PROCEDURE fti(tb_c_step_fti, label, description);

CREATE TRIGGER "recipe_fti_trigger" AFTER UPDATE OR INSERT OR DELETE
ON tb_c_recipe FOR EACH ROW
EXECUTE PROCEDURE fti(tb_c_recipe_fti, title, description, source);

CREATE TRIGGER "ing_fti_trigger" AFTER UPDATE OR INSERT OR DELETE
ON tb_c_ingredient FOR EACH ROW
EXECUTE PROCEDURE fti(tb_c_ingredient_fti, description);



-- Indexes for FTI
CREATE INDEX recipe_fti_string_idx ON tb_c_recipe_fti(string);
CREATE INDEX recipe_fti_id_idx ON tb_c_recipe_fti(id);
CREATE INDEX recipe_fti_oid_idx ON tb_c_recipe_fti(oid);

CREATE INDEX recipe_step_fti_string_idx ON tb_c_recipe_fti(string);
CREATE INDEX recipe_step_fti_id_idx ON tb_c_recipe_fti(id);
CREATE INDEX recipe_step_fti_oid_idx ON tb_c_recipe_fti(oid);

CREATE INDEX ingredient_fti_string_idx ON tb_c_ingredient_fti(string);
CREATE INDEX ingredient_fti_id_idx ON tb_c_ingredient_fti(id);
CREATE INDEX ingredient_fti_oid_idx ON tb_c_ingredient_fti(oid);

-- Stored procedures
CREATE FUNCTION sp_insert_unit(varchar, varchar, varchar) RETURNS integer AS '
DECLARE 
	label ALIAS FOR $1;
	shortfmt ALIAS FOR $2;
	longfmt ALIAS FOR $3;
	id integer;
	result boolean;
BEGIN
	id := nextval(''seq_tb_unit'');
	INSERT INTO tb_c_unit VALUES (id, label, shortfmt,longfmt);
	result := FOUND;
	IF result = FALSE THEN
		RAISE EXCEPTION ''Error inserting into tb_c_unit'';
	END IF;
	RETURN id;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_delete_unit(integer) RETURNS boolean AS '
DECLARE
	punitid ALIAS FOR $1;
BEGIN
	DELETE FROM tb_c_unit WHERE id=punitid;
	RETURN TRUE;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_update_unit(integer, varchar, varchar, varchar) RETURNS boolean AS '
DECLARE
	punitid ALIAS FOR $1;
	plabel ALIAS FOR $2;
	pshortfmt ALIAS FOR $3;
	plongfmt ALIAS FOR $4;
	result boolean;
BEGIN
	UPDATE tb_c_unit SET label = plabel, short_fmt = pshortfmt, long_fmt = plongfmt WHERE id=punitid; 
	result := FOUND;
	RETURN result;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_delete_recipe_ingredient(integer, integer) RETURNS boolean AS '
DECLARE 
	precipeid ALIAS FOR $1;
	pid ALIAS FOR $2;
BEGIN
	DELETE FROM tb_c_recipe_ingredient 
	WHERE recipe_id = precipeid AND id = pid;
	
	RETURN TRUE;
END;
' LANGUAGE plpgsql;


CREATE FUNCTION sp_insert_recipe_ingredient(integer, NUMERIC(2), integer, integer) RETURNS integer AS '
DECLARE 
	precipeid ALIAS FOR $1;
	pamount ALIAS FOR $2;
	punitid ALIAS FOR $3;
	pingredientid ALIAS FOR $4;
	new_id integer;
	result boolean;
BEGIN
	SELECT (COALESCE(MAX(id),0) + 1) INTO new_id
	FROM tb_c_recipe_ingredient
	WHERE recipe_id = precipeid;

	INSERT INTO tb_c_recipe_ingredient VALUES (precipeid, new_id,pamount,punitid,pingredientid);
	result := FOUND;
	IF result = FALSE THEN
		RAISE EXCEPTION ''Error inserting into tb_c_recipe_ingredient'';
	END IF;
	RETURN new_id;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_update_ingredient(integer, varchar, varchar) RETURNS boolean AS '
DECLARE
	pingredientid ALIAS FOR $1;
	plabel ALIAS FOR $2;
	pdesc ALIAS FOR $3;
	result boolean;
BEGIN
	UPDATE tb_c_ingredient SET label = plabel, description = pdesc WHERE id=pingredientid; 
	result := FOUND;
	RETURN result;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_delete_ingredient(integer) RETURNS boolean AS '
DECLARE
	pingredientid ALIAS FOR $1;
BEGIN
	DELETE FROM tb_c_ingredient WHERE id=pingredientid;
	RETURN TRUE;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_insert_ingredient(varchar, varchar) RETURNS integer as '
DECLARE 
	plabel ALIAS FOR $1;
	pdescription ALIAS FOR $2;
	pid integer;
	result boolean;
BEGIN
	pid := nextval(''seq_tb_ingredient'');
	INSERT INTO tb_c_ingredient VALUES (pid, plabel, pdescription);
	result := FOUND;
	IF result = FALSE THEN
		RAISE EXCEPTION ''Error inserting into tb_c_ingredient'';
	END IF;
	RETURN pid;	
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_move_ingredient(integer, integer, integer) RETURNS boolean AS '
DECLARE
	pgroupid 	ALIAS FOR $1;
	pingredientid   ALIAS FOR $2;
	ptargetgroup	ALIAS FOR $3;

BEGIN
	UPDATE tb_c_ingredient_group_member SET group_id = ptargetgroup WHERE group_id=pgroupid AND ingredient_id = pingredientid;
	RETURN TRUE;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_delete_ingredient_group_member(integer, integer) RETURNS boolean AS '
DECLARE
	pgroupid 	ALIAS FOR $1;
	pingredientid 	ALIAS FOR $2;
BEGIN
	DELETE FROM tb_c_ingredient_group_member WHERE group_id=pgroupid AND ingredient_id=pingredientid;
	RETURN TRUE;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_insert_ingredient_group_member(integer, integer) RETURNS boolean AS '
DECLARE 
	groupid ALIAS FOR $1;
	ingredientid ALIAS FOR $2;
	result boolean;
BEGIN
	INSERT INTO tb_c_ingredient_group_member VALUES (groupid, ingredientid);
	result := FOUND;
	IF result = FALSE THEN
		RAISE EXCEPTION ''Error inserting into tb_c_ingredient_group_member'';
	END IF;
	RETURN TRUE;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_delete_ingredient_group(integer) RETURNS boolean AS '
DECLARE
	pgid ALIAS FOR $1;
BEGIN
	DELETE FROM tb_c_ingredient_group WHERE id=pgid;
	RETURN TRUE;
END;
' LANGUAGE plpgsql;


CREATE FUNCTION sp_insert_ingredient_group(varchar,integer) RETURNS integer AS '
DECLARE 
	label ALIAS FOR $1;
	parentgroupid ALIAS FOR $2;
	targetid integer;
	id integer;
	result boolean;
BEGIN
	id := nextval(''seq_tb_ingredient_group'');
	IF parentgroupid = 0 THEN
		targetid = id;
	ELSE
		targetid = parentgroupid;
	END IF;
	INSERT INTO tb_c_ingredient_group VALUES (id,label, targetid);
	result := FOUND;
	IF result = FALSE THEN
		RAISE EXCEPTION ''Error inserting into tb_c_ingredient_group'';
	END IF;
	RETURN id;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_delete_recipe_category(integer) RETURNS boolean AS '
DECLARE
	prid ALIAS FOR $1;
BEGIN
	DELETE FROM tb_c_recipe_category WHERE id=prid;
	RETURN TRUE;
END;
' LANGUAGE plpgsql;


CREATE FUNCTION sp_insert_recipe_category(varchar, integer) RETURNS integer AS '
DECLARE 
	label ALIAS FOR $1;
	parent ALIAS FOR $2;
	id integer;
	pid integer;
	result boolean;
BEGIN
	id := nextval(''seq_tb_recipe_category'');
	IF parent = 0 THEN
		pid := id;
	ELSE
		pid := parent;
	END IF;
	INSERT INTO tb_c_recipe_category VALUES (id,label,pid);
	result := FOUND;
	IF result = FALSE THEN
		RAISE EXCEPTION ''Error inserting into tb_c_recipe_category'';
	END IF;
	RETURN id;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_insert_recipe_category_member(integer, integer) RETURNS boolean AS '
DECLARE 
	categoryid ALIAS FOR $1;
	recipeid ALIAS FOR $2;
	result boolean;
BEGIN
	INSERT INTO tb_c_recipe_category_member VALUES (categoryid, recipeid);
	result := FOUND;
	IF result = FALSE THEN
		RAISE EXCEPTION ''Error inserting into tb_c_recipe_category_member'';
	END IF;
	RETURN result;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_move_recipe(integer, integer, integer) RETURNS boolean AS '
DECLARE
	pcatid 			ALIAS FOR $1;
	precipeid   	ALIAS FOR $2;
	ptargetcatid	ALIAS FOR $3;

BEGIN
	UPDATE tb_c_recipe_category_member SET recipe_category_id = ptargetcatid WHERE recipe_category_id=pcatid AND recipe_id = precipeid;
	RETURN TRUE;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_insert_recipe_step(integer, integer, varchar, varchar) RETURNS integer AS '
DECLARE 
	precipeid ALIAS FOR $1;
	porder ALIAS FOR $2;
	plabel ALIAS FOR $3;
	pdescription ALIAS FOR $4;
	new_id 	integer;
	result boolean;
BEGIN
	SELECT (COALESCE(MAX(id),0) + 1) INTO new_id
	FROM tb_c_recipe_step
	WHERE recipe_id = precipeid;

	INSERT INTO tb_c_recipe_step VALUES (precipeid,new_id,porder,plabel,pdescription);
	result := FOUND;
	IF result = FALSE THEN
		RAISE EXCEPTION ''Error inserting into tb_c_recipe_step'';
	END IF;
	RETURN new_id;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_delete_recipe_step(integer, integer) RETURNS boolean AS '
DECLARE 
	precipeid ALIAS FOR $1;
	pid ALIAS FOR $2;
BEGIN
	DELETE FROM tb_c_recipe_step
	WHERE recipe_id = precipeid
	AND id = pid;

	RETURN TRUE;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_update_recipe_step(integer, integer, integer, varchar, varchar) RETURNS boolean AS '
DECLARE 
	precipeid ALIAS FOR $1;
	pid ALIAS FOR $2;
	porder ALIAS FOR $3;
	plabel ALIAS FOR $4;
	pdescription ALIAS FOR $5;
BEGIN
	UPDATE tb_c_recipe_step
	SET ordernum = porder, label = plabel, description = pdescription
	WHERE recipe_id = precipeid AND id = pid;
	
	RETURN TRUE;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_insert_recipe_step(integer, varchar, varchar) RETURNS integer AS '
DECLARE 
	precipeid ALIAS FOR $1;
	plabel ALIAS FOR $2;
	pdescription ALIAS FOR $3;
	new_id 	integer;
	new_ordernum integer;
	result boolean;
BEGIN
	SELECT (COALESCE(MAX(id),0) + 1) INTO new_id
	FROM tb_c_recipe_step
	WHERE recipe_id = precipeid;

	SELECT (COALESCE(MAX(ordernum),0) + 1) INTO new_ordernum 
	FROM tb_c_recipe_step
	WHERE recipe_id = precipeid;

	INSERT INTO tb_c_recipe_step VALUES (precipeid,new_id,new_ordernum,plabel,pdescription);
	result := FOUND;
	IF result = FALSE THEN
		RAISE EXCEPTION ''Error inserting into tb_c_recipe_step'';
	END IF;
	RETURN new_id;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION sp_update_recipe(integer, varchar, varchar, varchar) RETURNS boolean AS '
DECLARE 
	pid ALIAS FOR $1;
	ptitle ALIAS FOR $2;
	pdescription ALIAS FOR $3;
	psource ALIAS FOR $4;
BEGIN
	UPDATE tb_c_recipe SET 	title = ptitle, description = pdescription, source = psource
	WHERE id = pid;

	RETURN TRUE;
END;
' LANGUAGE plpgsql;


CREATE FUNCTION sp_insert_recipe(varchar, varchar, varchar) RETURNS integer AS '
DECLARE 
	title ALIAS FOR $1;
	description ALIAS FOR $2;
	source ALIAS FOR $3;
	id integer;
	result boolean;
BEGIN
	id := nextval(''seq_tb_recipe'');
	INSERT INTO tb_c_recipe VALUES (id,title, description, source);
	result := FOUND;
	IF result = FALSE THEN
		RAISE EXCEPTION ''Error inserting into tb_c_recipe'';
	END IF;
	RETURN id;
END;
' LANGUAGE plpgsql;

SELECT sp_insert_unit('teaspoon', 'tsp.', '');
SELECT sp_insert_unit('tablespoon', 'tbsp.', '');
SELECT sp_insert_unit('cup', 'c.', '');
SELECT sp_insert_unit('ounce', 'oz.', '');
SELECT sp_insert_unit('pound', 'lb.', '');
SELECT sp_insert_unit('pint', 'pt.', '');
SELECT sp_insert_unit('quart', 'qt.', '');
SELECT sp_insert_unit('gallon', 'gal.', '');
SELECT sp_insert_unit('package', 'pkg.', '');

SELECT sp_insert_recipe_category('Beef', 0);
SELECT sp_insert_recipe_category('Chicken', 0);
SELECT sp_insert_recipe_category('Soup', 0);
SELECT sp_insert_recipe_category('Bread', 0);
SELECT sp_insert_recipe_category('Desserts', 0);
SELECT sp_insert_recipe_category('Chocolate', 5);
SELECT sp_insert_recipe_category('Pies', 5);
SELECT sp_insert_recipe_category('Cakes', 5);
SELECT sp_insert_recipe_category('Bread Machine', 4);

SELECT sp_insert_recipe('Chocolate Bread', 'Chocolate Bread for the Bread Machine', 'Bread Machine book');
SELECT sp_insert_recipe('Sweet Butter Bread', 'Sweet white bread', 'Bread Machine book');
SELECT sp_insert_recipe('French Bread', 'French', 'Ana');
SELECT sp_insert_recipe('Apple Pie', 'Nasty Apple Pie', 'Ana');
SELECT sp_insert_recipe('Chocolate Pie', 'Yummy chocolate pie', 'Ana');
SELECT sp_insert_recipe('Hershey''s Chocolate Cake', 'Yummy chocolate cake', 'Ana');
SELECT sp_insert_recipe('White Anglefood Cake', 'Gross cake', 'Ana');

SELECT sp_insert_recipe_step(1, 'no label', 'Mix the ingredients together');
SELECT sp_insert_recipe_step(1, 'nope, no label', 'eat the food');

SELECT sp_insert_recipe_category_member(4,3);
SELECT sp_insert_recipe_category_member(7,4);
SELECT sp_insert_recipe_category_member(7,5);
SELECT sp_insert_recipe_category_member(8,6);
SELECT sp_insert_recipe_category_member(8,7);
SELECT sp_insert_recipe_category_member(9,1);
SELECT sp_insert_recipe_category_member(9,2);

SELECT sp_insert_ingredient_group('Fruit', 0);
SELECT sp_insert_ingredient_group('Vegetables', 0);
SELECT sp_insert_ingredient_group('Spices', 0);
SELECT sp_insert_ingredient_group('Herbs', 0);
SELECT sp_insert_ingredient_group('Potatos', 2);
SELECT sp_insert_ingredient_group('Tomatoes', 2);
SELECT sp_insert_ingredient_group('Beans', 2);
SELECT sp_insert_ingredient_group('Apples', 1);
SELECT sp_insert_ingredient_group('Oranges', 1);
SELECT sp_insert_ingredient_group('Peppers', 3);

SELECT sp_insert_ingredient('Russet Potatoes', 'Russet Potatoes');
SELECT sp_insert_ingredient('Idaho Potatoes', 'Idaho Potatoes');
SELECT sp_insert_ingredient('Sweet Potatoes', 'Sweet Potatoes');
SELECT sp_insert_ingredient('Johnathon', 'Johnathon Apples');
SELECT sp_insert_ingredient('Granny Smith', 'Granny Smith Apples');
SELECT sp_insert_ingredient('Red Delicious', 'Red Delicious Apples');
SELECT sp_insert_ingredient('Green Beans', 'Green Beans');
SELECT sp_insert_ingredient('Pinto Beans', 'Pinto Beans');
SELECT sp_insert_ingredient('Garbanzo Beans', 'Garbanzo Beans');

SELECT sp_insert_ingredient_group_member(5,1);
SELECT sp_insert_ingredient_group_member(5,2);
SELECT sp_insert_ingredient_group_member(5,3);
SELECT sp_insert_ingredient_group_member(8,4);
SELECT sp_insert_ingredient_group_member(8,5);
SELECT sp_insert_ingredient_group_member(8,6);
SELECT sp_insert_ingredient_group_member(7,7);
SELECT sp_insert_ingredient_group_member(7,8);
SELECT sp_insert_ingredient_group_member(7,9);