/*

getSiteInspection



*/

DROP TYPE IF EXISTS BROODSTYPE CASCADE;
CREATE TYPE BROODSTYPE AS ( id integer, name varchar, broodcount integer);

DROP TYPE IF EXISTS SITEINSPECTIONTYPE CASCADE;
CREATE  TYPE SITEINSPECTIONTYPE AS ( id integer, site varchar, date date, health varchar, type varchar, afbcheck char(1), pestcontrol varchar,photo varchar, broods text[][], extrainfo text); 

DROP FUNCTION IF EXISTS  getSiteInspectionBroods( INTEGER, INTEGER);

CREATE OR REPLACE FUNCTION getSiteInspectionBroods(
	s_account	integer,
	s_id		integer

) RETURNS VARCHAR AS $$
DECLARE
broods     broodstype%rowtype;
broodAR    VARCHAR(100);
counter    INTEGER;

BEGIN
	counter := 1;
	FOR broods IN SELECT bt.id, bt.name, sb.broodcount from broodtype bt, siteinspections_broods sb where sb.account = s_account and sb.account = s_account and bt.id = sb.broodid and sb.inspectionid = s_id LOOP
        	IF counter = 1 THEN
			broodAr :=  '{{' || broods.id || ',' || broods.name || ','  || broods.broodcount || '}';
		ELSIF counter > 1 THEN
			broodAr := broodAr || ',{' || broods.id || ',' || broods.name || ',' || broods.broodcount || '}';
		END IF;
		counter := counter + 1;

        END LOOP;
	broodAR := broodAR || '}';
	RETURN broodAR;
	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;

CREATE OR REPLACE FUNCTION getSiteInSpection(
        s_account               integer,
	s_id			integer
) RETURNS  SITEINSPECTIONTYPE AS  $$
DECLARE
inspections siteinspectiontype%rowtype; 
broods      broodstype%rowtype;
BEGIN
	
	
        -- open cursor 
	SELECT ins.id as id, s.name as site, ins.insp_date as date,ins.health as health, ins.type as type, ins.afb as afbcheck, pest.description as pestcontrol, ins.photo_path as photo, getSiteInspectionBroods(s_account, s_id), ins.extra_info INTO inspections from siteinspections ins, sites s, pestcontrol pest where s.id=ins.site and pest.id= ins.pestcontrol and  ins.account = s_account and ins.id = s_id;
	RETURN inspections;                                                 
        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;

