/*

addSite 



*/

CREATE OR REPLACE FUNCTION addSite(
 	s_account       	integer,
	s_siteid		integer,
	s_maf_ref		varchar(100),
 	s_area      		integer, 
	s_landowner		integer,             
 	s_name      		varchar(100),
 	s_description   	text ,                  
	s_address_1		varchar(100),
	s_address_2		varchar(100),
	s_latlng    		varchar(20),
	brood			int[][],
	OUT siteId              integer,
        OUT status              varchar(10),
        OUT newSiteId           integer
) RETURNS BOOLEAN AS $$
DECLARE
	site_id 	integer;
	brood_size	integer;
	-- site_maf_ref	integer;	

BEGIN
	brood_size      := array_upper( brood, 1 );
	-- site_maf_ref	:= s_maf_ref;
	IF s_siteid > 0 THEN
		SELECT id INTO site_id FROM sites where account = s_account and id = s_siteid and name  = s_name;
		IF NOT FOUND THEN
			INSERT INTO SITES (id,account, name,area,maf_ref ) values (s_siteid,s_account,s_name,s_area,'');
			RETURN TRUE;
		ELSE
			--already inserted
			RETURN TRUE; 			
		END IF;
	ELSE


		-- first we insert into sites
		INSERT INTO SITES (id,account,maf_ref,area,name,description,address_1,address_2,latlng)  VALUES (DEFAULT,s_account,s_maf_ref,s_area,s_name,s_description,s_address_1,s_address_2,s_latlng);
		IF FOUND THEN
			-- insert was successful
			-- now get id from sites use maf_ref because this is a unique field
			SELECT id INTO site_id FROM sites where account = s_account and maf_ref = s_maf_ref;
			IF FOUND THEN
				INSERT INTO sitelandowner values(DEFAULT,site_id,s_landowner);
				--now we loop over brood and insert into sitehives
			 	FOR I IN 1..brood_size LOOP
                                	RAISE NOTICE 'brood value = %', brood[I];
					INSERT INTO sitehives values(DEFAULT, s_account, brood[I][1]::int, site_id,brood[I][2]::int);
				
				END LOOP;
				RETURN TRUE;
			ELSE
				RAISE EXCEPTION 'select failed on site';
			END IF;
			RETURN TRUE;
		ELSE
			RAISE EXCEPTION 'insert failed on site';
		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;
                RETURN FALSE;

END;
$$ LANGUAGE plpgsql;