/*

addInspection 



*/

CREATE OR REPLACE FUNCTION addInspection(
 	s_account       	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[][]
) 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;
	-- first we insert into sites
	INSERT INTO SITES (id,account,maf_ref,area,landowner,name,description,address_1,address_2,latlng)  VALUES (DEFAULT,s_account,s_maf_ref,s_area,s_landowner,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
			--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;
	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;

