
drop procedure if exists sp_UpdateForeignKeys;


DROP PROCEDURE IF EXISTS RecalculateEHR;

DROP PROCEDURE IF EXISTS RecalculateEHR3;

DROP PROCEDURE IF EXISTS RecalculateEHREntity;

DROP PROCEDURE IF EXISTS RecalculateEHRTreeParams;


do $$
begin
		drop function if exists public.recalculateehr();
end;
$$ language plpgsql;


do $$
begin
		drop function if exists public.recalculateehrtreeparams( uuid, int, int);
end;
$$ language plpgsql;


DROP FUNCTION IF EXISTS RecalculateEHREntity(integer);




DROP PROCEDURE IF EXISTS RecalculateEHR;

DROP PROCEDURE IF EXISTS RecalculateEHR3;

DROP PROCEDURE IF EXISTS RecalculateEHREntity;

DROP PROCEDURE IF EXISTS RecalculateEHRTreeParams;

DROP FUNCTION IF EXISTS ehr_rus_tree_changeparent();

DROP FUNCTION IF EXISTS ehr_rus_tree_delete();

DROP FUNCTION IF EXISTS ehr_rus_tree_insert();


DO 
$$ 
BEGIN 
                    DROP TRIGGER IF EXISTS ehr_rus_tree_afterdelete ON EHR.T_EHR_RUS_TREE;
END; 
$$ ;


DO 
$$ 
BEGIN 
                    DROP TRIGGER IF EXISTS ehr_rus_tree_afterinsert ON EHR.T_EHR_RUS_TREE;
END; 
$$ ;


DO 
$$ 
BEGIN 
                    DROP TRIGGER IF EXISTS ehr_rus_tree_changeparent ON EHR.T_EHR_RUS_TREE;
END; 
$$ ;


do $$
begin
		drop function if exists RecalculateEHREntity(integer);
end;
$$ language plpgsql;


do $$
begin
		drop function if exists public.recalculateehrrustreetreeparams(uuid, int, int);
end;
$$ language plpgsql;


do $$
begin
		drop function if exists public.recalculateehrrustree();
end;
$$ language plpgsql;


DELETE FROM EHR.T_EHR_REFERENCE USING VCLib.T_ENTITIES e 
WHERE EhrEntity = e.EntityID AND e.EntName = 'EHR_RUS_TREE' ;

DELETE FROM EHR.T_EHR_REFERENCE USING VCLib.T_ENTITIES e 
WHERE EhrEntity = e.EntityID AND e.EntName = 'EHR_RUS' ;


DROP TABLE EHR.T_EHR_RUS_TREE;

DROP TABLE EHR.T_EHR_RUS;


DO $$
DECLARE "EntityEHR_RUS" int;
DECLARE "EntityEHR_RUS_TREE" int;
BEGIN

	SELECT "EntityEHR_RUS" = EntityID FROM VCLib.T_ENTITIES e WHERE e.EntName = 'EHR_RUS' and e.[Schema] = 'EHR';
	SELECT "EntityEHR_RUS_TREE" = EntityID FROM VCLib.T_ENTITIES e WHERE e.EntName = 'EHR_RUS_TREE' and e.[Schema] = 'EHR';
	UPDATE e SET BaseEntity = null FROM VCLib.T_ENTITIES e WHERE BaseEntity = "EntityEHR_RUS" or BaseEntity = "EntityEHR_RUS_TREE";

	
	
	
	
	select EntityID 
	INTO ENTITY
	from VCLib.T_ENTITIES e
	where e.EntName in (
	'EHR_RUS',
	'EHR_RUS_TREE'
	)
	and e.Schema = 'EHR';
	
	Delete 
	From VCLib.T_USER_COLUMNS col
		using VCLib.T_USER_CONTEXTS ctx, ENTITY e 
		where col.UserContextID = ctx.UserContextID and ctx.EntityID = e.EntityID;
	
	Delete 
	From VCLib.T_USER_CONTEXTS ctx
		using ENTITY e where  ctx.EntityID = e.EntityID;
	
	delete 
	from VCLib.T_DELETED_OBJECT del
		using ENTITY e where  del.Entities = e.EntityID;
	
	delete 
	From VCLib.T_TEMPLATE_FIELD_VALUE fv
	using VCLib.T_TEMPLATE_FIELD fld, ENTITY e 
	where fv.TemplateField = fld.TemplateFieldID and  fld.Entity = e.EntityID;
	
	delete 
	From VCLib.T_TEMPLATE_FIELD fld
		using ENTITY e where  fld.Entity = e.EntityID;
	
	delete 
	From VCLib.T_Report rep
		using ENTITY e where  rep.EntityID = e.EntityID;
	
	DELETE 
	FROM VCLib.T_USER_SEARCHES  us
		using ENTITY e where  us.EntityID = e.EntityID;
	
	delete  
	from VCLib.T_ENTITIES_ATTRIBUTES e
		using ENTITY ee where  e.EntityID = ee.EntityID;
	
	delete 
	from VCLib.T_ENTITIES e
		using ENTITY ee where  e.EntityID = ee.EntityID;
	
	drop table ENTITY;
	
	
END;
$$
