/*

addSiteInspection 


$VAR1 = {
           'pestcontrol' => 'None',
           'account' => '1',
           'date' => '09/06/2013',
           'photo' => 'no photo',
           'health' => '(null)',
           'afp' => 'no',
           'siteId' => '2',
           'type' => 'Inspection',
	   'hash' => 'ddsalfafkalmalmlasmfl',
           'broods' => [
                         {
                           'broodKey' => '4',
                           'broodCount' => '0'
                         },
          {

			     'broodKey' => '5',
                           'broodCount' => '0'
           },
                        {
                           'broodKey' => '6',
                           'broodCount' => '0'
                         }
          ]
 };








*/

CREATE OR REPLACE FUNCTION addSiteInspection(
 	s_account       	integer,
	s_site_id		integer,
	s_date			varchar(10),
	s_health		varchar(100),
	s_type			varchar(100),
	s_hives			varchar(10),
	s_afb			varchar(1),
	s_pestcontrol		integer,
	s_info			text,
	s_photo			varchar(100),
	s_brood			int[][],
	s_hash			text
) RETURNS BOOLEAN AS $$
DECLARE
	site_id 		integer;
	brood_size		integer;
	-- site_maf_ref		integer;
	site_inspection_id	integer;	

BEGIN
	brood_size      := array_upper( s_brood, 1 );
	-- site_maf_ref	:= s_maf_ref;
	-- first we insert into sites

	/* add into siteinsspections table 

		id          | integer                | not null default nextval('seq_siteinspection'::regclass)
 		account     | integer                | not null
 		site        | integer                | not null
 		insp_date   | date                   | not null
 		health | character varying(100) | not null
 		type        | character varying(100) | not null
 		afb         | character(1)           | default 'n'::bpchar
 		pestcontrol | character varying(100) | not null
 		extra_info  | text                   | 
 		photo_path  | character varying(100) |
	*/

	/* first we make sure not already inserted */
	PERFORM id from SITEINSPECTIONS where account = s_account and site = s_site_id and insp_date = to_date(s_date,'DD/MM/YYYY') and health = s_health and type = s_type and insp_key = s_hash;
	IF FOUND THEN
		RAISE NOTICE 'Row already exists with inspec_key =%', s_hash;
		return TRUE;
	END IF;

	INSERT INTO SITEINSPECTIONS (id,account,site,insp_date,health,type,hives,afb,pestcontrol,extra_info, photo_path, insp_key)   VALUES (DEFAULT,s_account,s_site_id,to_date(s_date,'DD/MM/YYYY'),s_health,s_type,s_hives,s_afb,s_pestcontrol,s_info,s_photo, s_hash);
	IF FOUND THEN
		-- insert was successful
		-- now get id from sites use maf_ref because this is a unique field
		site_inspection_id =  CURRVAL('seq_siteinspection');
		IF FOUND THEN
			--now we loop over brood and insert into sitehives
			 FOR I IN 1..brood_size LOOP
                                RAISE NOTICE 'brood value = %', s_brood[I];
				INSERT INTO SITEINSPECTIONS_BROODS values(s_brood[I][1]::int,site_inspection_id, s_account,s_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;

