/*

addSite 



*/

CREATE OR REPLACE FUNCTION addAreas(
 	s_account       	integer,
	s_id			integer,
 	s_name      		varchar(100),
	OUT areaId		integer,
	OUT status		varchar(10),
	OUT newAreaId		integer
) AS $$
DECLARE
	area_id 	integer;
	area_name	varchar(100);

BEGIN

	-- frist we check that the id passed is free
	newAreaId := 0;	
	RAISE NOTICE 's_id = %', s_id;
	IF s_id NOTNULL and s_id != 0 THEN
		RAISE NOTICE 'is not null';
		SELECT id into area_id from areas where id = s_id;
		IF NOT FOUND THEN
			RAISE NOTICE 'area_id = %', area_id;
			SELECT name from Areas into area_name where name = s_name;
			IF NOT FOUND THEN
				-- both id and name don't exist so insert
				INSERT INTO AREAS ( id, account, name ) values (DEFAULT, s_account, s_name );
				SELECT id INTO area_id from areas where name = s_name;
				IF FOUND THEN
					IF s_id = area_id THEN
						areaId := s_id;
						status := 'UPDATE';
						newAreaId := 0;
					ELSE
						areaId := s_id;
                                                status := 'NEW';
                                                newAreaId := area_id;

					END IF;
				END IF;
			ELSE
				-- name exist found but id is not
				areaId := s_id;
				status := 'ALREADY';
			END IF;
		ELSE
			-- id aready exist just check the name is the same
			SELECT name from Areas into area_name where s_name = s_name;
			IF FOUND THEN
				areaId := s_id;
				status := 'ALREADY';
			ELSE
				INSERT INTO AREAS ( id, account, name ) values (DEFAULT, s_account, s_name );
				SELECT id INTO area_id from areas where name = s_name;
				IF FOUND THEN
					areaId := s_id;
					status := 'NEW';
					newAreaId := area_id;
				END IF;
			END IF;
		END IF;
	ELSE
		--no id is passed in so just inert
		INSERT INTO AREAS ( id, account, name ) values (DEFAULT, s_account, s_name );
		SELECT id INTO area_id from areas where name = s_name;
			RAISE NOTICE 'After insert area_id = %', area_id;
		IF FOUND THEN
			areaId := area_id;
			status := 'NEW';
			newAreaId := area_id; 
		END IF;
	END IF;
	EXCEPTION
                WHEN RAISE_EXCEPTION or  STRING_DATA_LENGTH_MISMATCH or PLPGSQL_ERROR THEN
                RAISE NOTICE 'exception caught in stored function addStte for name %', name;

END;
$$ LANGUAGE plpgsql;

