/* SQL for upgrade iEat 1.0 Postgres to 1.2.
 * ===================================================================
 * This program is free software; you can redistribute it and/or 
 * modify it under the terms of the GNU General Public License as 
 * published by the Free Software Foundation; either version 2 of 
 * the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful, 
 * but WITHOUT ANY WARRANTY; without even the implied warranty of 
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 
 * General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License 
 * along with this program; if not, write to the Free Software 
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 
 * 02111-1307 USA
 * ===================================================================
 * $Id: upgrade-1.0-1.2.sql 100 2009-05-19 03:29:09Z msqr $
 * ===================================================================
 */

drop table settings;
alter table setting rename to settings;

alter table Meal drop constraint FK2487E3653F2B3;
alter table Recipe drop constraint FK91AB41AED03B847F;
alter table Recipe drop constraint FK91AB41AEB3B58A9A;
alter table Recipe drop constraint FK91AB41AE2E06D1;
alter table Recipe drop constraint FK91AB41AEAF42E01B;
alter table Recipe drop constraint FK91AB41AE6D0BF7BB;
alter table Recipe drop constraint FK91AB41AE653F2B3;
alter table Recipe drop constraint FK91AB41AECB1C722F;
alter table RecipeIngredient drop constraint FK6743CE1FE56A120C;
alter table RecipeIngredient drop constraint FK6743CE1FF3B271D1;
alter table RecipeIngredient drop constraint FK6743CE1F36D984;
alter table RecipeIngredient drop constraint FK6743CE1FC846558E;
alter table RecipeStep drop constraint FK61DE359AE56A120C;
alter table RelatedRecipe drop constraint FK35D10179C846558E;
alter table RelatedRecipe drop constraint FK35D10179323B94;
alter table RelatedRecipe drop constraint FK35D10179E56A120C;
alter table MealRecipe drop constraint FK38351351AF42E01B;
alter table MealRecipe drop constraint FK38351351B45637;
alter table MealRecipe drop constraint FK38351351C846558E;
alter table RecipeComment drop constraint FKAB44A4D1E56A120C;
alter table RecipeRating drop constraint FK606D64EBE56A120C;

drop sequence ethnicity_id_seq;
drop sequence prep_time_id_seq;
drop sequence recipe_rating_id_seq;
drop sequence ingredient_id_seq;
drop sequence course_id_seq;
drop sequence difficulty_id_seq;
drop sequence unit_id_seq;
drop sequence user_id_seq;
drop sequence recipe_id_seq;
drop sequence meal_id_seq;
drop sequence recipe_comment_id_seq;
drop sequence base_id_seq;

create sequence hibernate_integer_seq;
create sequence hibernate_sequence;

SELECT setval('hibernate_integer_seq',
	(select max(id) from (
	select baseid as id from base union 
	select courseid as id from course union 
	select difficultyid as id from difficulty union 
	select ethnicityid as id from ethnicity union 
	select ingredientid as id from ingredient union 
	select preptimeid as id from preptime union 
	select relationkindid as id from relationkind union 
	select systemid as id from system union 
	select unitid as id from unit union 
	select userid as id from users
	) as sub));

SELECT setval('hibernate_sequence',
	(select max(id) from (
	select mealid as id from meal union 
	select recipeid as id from recipe union 
	select commentid as id from recipecomment 
	) as sub));

alter table base rename column "value" to value_;
alter table course rename column "value" to value_;
alter table difficulty rename column "value" to value_;
alter table ethnicity rename column "value" to value_;
alter table ingredient rename column "name" to ingr_name;

alter table meal alter column mealid type bigint;
alter table meal rename column "owner" to ownerid;
alter table meal rename column "name" to meal_name;

alter table mealrecipe
	drop constraint mealrecipe_pkey,
	add column mealrecipeid bigint;
update mealrecipe set mealrecipeid = nextval('hibernate_sequence');
alter table mealrecipe
	add primary key (mealrecipeid),
	drop column idinternal;
alter table mealrecipe rename column recipe to recipeid;
alter table mealrecipe rename column course to courseid;
alter table mealrecipe rename column mealimpl_id to mealid;
alter table mealrecipe rename column recipeinternal_index to pos;

alter table preptime rename column "value" to value_;

alter table recipe alter column recipeid type bigint;
alter table recipe rename column "owner" to ownerid;
alter table recipe rename column "course" to courseid;
alter table recipe rename column "system" to systemid;
alter table recipe rename column "base" to baseid;
alter table recipe rename column "preptime" to preptimeid;
alter table recipe rename column "ethnicity" to ethnicityid;
alter table recipe rename column "name" to recipe_name;
alter table recipe rename column "difficulty" to difficultyid;

alter table recipecomment alter column commentid type bigint;
alter table recipecomment rename column "value" to value_;
alter table recipecomment rename column "recipeimpl_id" to recipeid;
alter table recipecomment rename column "commentinternal_index" to pos;

alter table recipeingredient
	drop constraint recipeingredient_pkey,
	add column recipeingredientid bigint;
update recipeingredient set recipeingredientid = nextval('hibernate_sequence');
alter table recipeingredient
	add primary key (recipeingredientid),
	drop column idinternal;
alter table recipeingredient rename column "ingredient" to ingredientid;
alter table recipeingredient rename column "unit" to unitid;
alter table recipeingredient rename column "recipe" to ingredientrecipeid;
alter table recipeingredient rename column "recipeimpl_id" to recipeid;
alter table recipeingredient rename column "ingredientinternal_index" to pos;

alter table reciperating rename column "recipeimpl_id" to recipeid;
alter table reciperating rename column "ratinginternal_index" to pos;

alter table recipestep
	drop constraint recipestep_pkey,
	add column stepid bigint;
update recipestep set stepid = nextval('hibernate_sequence');
alter table recipestep
	add primary key (stepid),
	drop column idinternal;
alter table recipestep rename column "value" to value_;
alter table recipestep rename column "recipeimpl_id" to recipeid;
alter table recipestep rename column "stepinternal_index" to pos;

alter table relatedrecipe
	drop constraint relatedrecipe_pkey,
	add column relatedrecipeid bigint;
update relatedrecipe set relatedrecipeid = nextval('hibernate_sequence');
alter table relatedrecipe
	add primary key (relatedrecipeid),
	drop column idinternal;
alter table relatedrecipe rename column "kind" to relationkindid;
alter table relatedrecipe rename column "recipeimpl_id" to recipeid;
alter table relatedrecipe rename column "relatedrecipeinternal_index" to pos;

alter table relationkind rename column "value" to value_;

alter table "system" rename to measurement_system;
alter table measurement_system rename column "value" to value_;

alter table unit rename column "value" to value_;

alter table users rename column "name" to "user_name";

/* Start copy from create.sql */
alter table meal add constraint meal_user_fk foreign key (ownerid) references users;
alter table mealrecipe add constraint mealrecipe_recipe_fk foreign key (recipeid) references recipe;
alter table mealrecipe add constraint mealrecipe_course_fk foreign key (courseid) references course;
alter table mealrecipe add constraint mealrecipe_meal_fk foreign key (mealid) references meal;
alter table recipe add constraint recipe_ethnicity_fk foreign key (ethnicityid) references ethnicity;
alter table recipe add constraint recipe_preptime_fk foreign key (preptimeid) references preptime;
alter table recipe add constraint recipe_base_fk foreign key (baseid) references base;
alter table recipe add constraint recipe_user_fk foreign key (ownerid) references users;
alter table recipe add constraint recipe_course_fk foreign key (courseid) references course;
alter table recipe add constraint recipe_difficulty_fk foreign key (difficultyid) references difficulty;
alter table recipe add constraint recipe_system_fk foreign key (systemid) references measurement_system;
alter table recipecomment add constraint recipecomment_recipe_fk foreign key (recipeid) references recipe;
alter table recipeingredient add constraint recipeingredient_recipe_fk foreign key (recipeid) references recipe;
alter table recipeingredient add constraint recipeingredient_unit_fk foreign key (unitid) references unit;
alter table recipeingredient add constraint recipeingredient_ingredientrecipe_fk foreign key (ingredientrecipeid) references recipe;
alter table recipeingredient add constraint recipeingredient_ingredient_fk foreign key (ingredientid) references ingredient;
alter table reciperating add constraint reciperating_recipe_fk foreign key (recipeid) references recipe;
alter table recipestep add constraint recipestep_recipe_fk foreign key (recipeid) references recipe;
alter table relatedrecipe add constraint relatedrecipe_recipe_fk foreign key (recipeid) references recipe;
alter table relatedrecipe add constraint relatedrecipe_relationkind_fk foreign key (relationkindid) references relationkind;
/* End copy from create.sql */

delete from settings where skey in ('env:hibernate.dialect', 'env:setup.requireadmin', 'env:setup.complete');
insert into settings (skey, svalue) values ('env:setup.requireadmin', 'false');
insert into settings (skey, svalue) values ('env:setup.complete', 'false');