--
--

SET escape_string_warning = off;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;


--
-- Name: broodstype; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE broodstype AS (
	id integer,
	name character varying,
	broodcount integer
);


ALTER TYPE public.broodstype OWNER TO postgres;

--
-- Name: siteinspectiontype; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE siteinspectiontype AS (
	id integer,
	site character varying,
	date date,
	health character varying,
	type character varying,
	afbcheck character(1),
	pestcontrol character varying,
	photo character varying,
	broods text[],
	extrainfo text
);


ALTER TYPE public.siteinspectiontype OWNER TO postgres;

--
-- Name: addareas(integer, integer, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION addareas(s_account integer, s_id integer, s_name character varying, OUT areaid integer, OUT status character varying, OUT newareaid integer) RETURNS record
    LANGUAGE plpgsql
    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 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;
$$;


ALTER FUNCTION public.addareas(s_account integer, s_id integer, s_name character varying, OUT areaid integer, OUT status character varying, OUT newareaid integer) OWNER TO postgres;

--
-- Name: addschedule(integer, integer, character varying, integer, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION addschedule(s_account integer, s_site integer, s_date character varying, s_inspectiontype integer, s_info text) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
	scheduleID integer;
BEGIN
	-- first we insert into sites
	INSERT INTO SITESCHEDULE (id,account,site,date,type,additionalinfo)  VALUES (DEFAULT,s_account,s_site,to_date(s_date, 'DD-MM-YYYY') ,s_inspectionType,s_info) RETURNING id INTO scheduleID;
	IF FOUND THEN
		-- insert was successful
		RETURN scheduleID;
	ELSE
		RAISE EXCEPTION 'insert failed on site schedule';
	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;
$$;


ALTER FUNCTION public.addschedule(s_account integer, s_site integer, s_date character varying, s_inspectiontype integer, s_info text) OWNER TO postgres;

--
-- Name: addsite(integer, integer, character varying, integer, integer, character varying, text, character varying, character varying, character varying, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION addsite(s_account integer, s_siteid integer, s_maf_ref character varying, s_area integer, s_landowner integer, s_name character varying, s_description text, s_address_1 character varying, s_address_2 character varying, s_latlng character varying, brood integer[], OUT siteid integer, OUT status character varying, OUT newsiteid integer) RETURNS record
    LANGUAGE plpgsql
    AS $$
DECLARE
	site_id 	integer;
	brood_size	integer;
	-- site_maf_ref	integer;	

BEGIN
	brood_size      := array_upper( brood, 1 );
	siteId    	:= s_siteid;
	newSiteId 	:= 0;

	-- 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
			--make sure site_id isent taken
			SELECT id INTO site_id from sites where id = s_siteid;
			IF FOUND THEN
			INSERT INTO SITES (id,account, name,area,maf_ref ) values (DEFAULT,s_account,s_name,s_area,'') RETURNING id INTO site_id;
				
			ELSE
				INSERT INTO SITES (id,account, name,area,maf_ref ) values (s_siteid,s_account,s_name,s_area,'');
				SELECT id INTO site_id FROM sites where account = s_account and id = s_siteid and name  = s_name;
			END IF;
			IF site_id <> s_siteid THEN
				newSiteId := site_id;
			END IF;	
			status := 'NEW';	
		ELSE
			--already inserted
			status := 'ALREADY';
		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;
			status 	:= 'NEW';
			newSiteId := site_id;
			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;
			ELSE
				RAISE EXCEPTION 'select failed on site';
			END IF;
		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;
                

END;
$$;


ALTER FUNCTION public.addsite(s_account integer, s_siteid integer, s_maf_ref character varying, s_area integer, s_landowner integer, s_name character varying, s_description text, s_address_1 character varying, s_address_2 character varying, s_latlng character varying, brood integer[], OUT siteid integer, OUT status character varying, OUT newsiteid integer) OWNER TO postgres;

--
-- Name: addsiteinspection(integer, integer, character varying, character varying, character varying, character varying, character varying, integer, text, character varying, integer[], text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION addsiteinspection(s_account integer, s_site_id integer, s_date character varying, s_health character varying, s_type character varying, s_hives character varying, s_afb character varying, s_pestcontrol integer, s_info text, s_photo character varying, s_brood integer[], s_hash text) RETURNS boolean
    LANGUAGE plpgsql
    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;
$$;


ALTER FUNCTION public.addsiteinspection(s_account integer, s_site_id integer, s_date character varying, s_health character varying, s_type character varying, s_hives character varying, s_afb character varying, s_pestcontrol integer, s_info text, s_photo character varying, s_brood integer[], s_hash text) OWNER TO postgres;

--
-- Name: getsiteinspection(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION getsiteinspection(s_account integer, s_id integer) RETURNS siteinspectiontype
    LANGUAGE plpgsql
    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;
$$;


ALTER FUNCTION public.getsiteinspection(s_account integer, s_id integer) OWNER TO postgres;

--
-- Name: getsiteinspectionbroods(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION getsiteinspectionbroods(s_account integer, s_id integer) RETURNS character varying
    LANGUAGE plpgsql
    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
$$;


ALTER FUNCTION public.getsiteinspectionbroods(s_account integer, s_id integer) OWNER TO postgres;

--
-- Name: getsiteinspections(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION getsiteinspections(s_account integer) RETURNS SETOF record
    LANGUAGE plpgsql
    AS $$
DECLARE
inspections RECORD; 
BEGIN
        -- open cursor 
	FOR  inspections IN 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 from siteinspections ins, sites s, pestcontrol pest where s.id=ins.site and pest.id= ins.pestcontrol and  ins.account = s_account LOOP
	RETURN NEXT inspections;
        END LOOP; 
	RETURN;                                                    
        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;
$$;


ALTER FUNCTION public.getsiteinspections(s_account integer) OWNER TO postgres;

--
-- Name: getsitesschedules(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION getsitesschedules(s_account integer) RETURNS refcursor
    LANGUAGE plpgsql
    AS $$
DECLARE
ref refcursor;    
BEGIN
        -- open cursor 
	OPEN ref FOR SELECT id,account,site,date,type,additionalinfo from SITESCHEDULE  WHERE account = s_account;
        RETURN ref;                                                     
        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;
$$;


ALTER FUNCTION public.getsitesschedules(s_account integer) OWNER TO postgres;

--
-- Name: updatesite(integer, character varying, integer, integer, integer, character varying, text, character varying, character varying, character varying, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION updatesite(s_account integer, s_maf_ref character varying, s_site_id integer, s_area integer, s_landowner integer, s_name character varying, s_description text, s_address_1 character varying, s_address_2 character varying, s_latlng character varying, brood integer[]) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	brood_size	integer;
	siteland_id	integer;
	-- site_maf_ref	integer;	
BEGIN
	brood_size      := array_upper( brood, 1 );
	-- site_maf_ref	:= s_maf_ref;
	-- first we insert into sites
	UPDATE sites set area = s_area, latlng = s_latlng,  description = s_description, maf_ref = s_maf_ref where id = s_site_id;
	RAISE NOTICE 'about to start loop brood_size = %', brood_size;
	IF brood_size IS NOT NULL THEN
		FOR I IN 1..brood_size LOOP
			RAISE NOTICE 'about to update sitehives! site_id = %', s_site_id;
			UPDATE sitehives SET number = brood[I][2]::int where brood_type =  brood[I][1]::int and site = s_site_id;
			IF  NOT FOUND THEN
				RAISE EXCEPTION 'no update sitehives';
			END IF;

		END LOOP;
	END IF;
	SELECT ID INTO siteland_id FROM sitelandowner where landowner = s_landowner and site = s_site_id;
	IF FOUND THEN
		UPDATE sitelandowner set landowner = s_landowner where site = s_site_id;
	ELSE
		INSERT INTO sitelandowner values(DEFAULT, s_site_id, s_landowner, s_account);
	END IF;	
	RETURN TRUE;
	
 
	EXCEPTION
                WHEN RAISE_EXCEPTION or  STRING_DATA_LENGTH_MISMATCH or PLPGSQL_ERROR THEN
                RAISE NOTICE 'exception caught in stored function updateSite for name %', s_name;
                RETURN FALSE;

END;
$$;


ALTER FUNCTION public.updatesite(s_account integer, s_maf_ref character varying, s_site_id integer, s_area integer, s_landowner integer, s_name character varying, s_description text, s_address_1 character varying, s_address_2 character varying, s_latlng character varying, brood integer[]) OWNER TO postgres;

--
-- Name: seq_account; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_account
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_account OWNER TO postgres;

--
-- Name: seq_account; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_account', 2, true);




--
-- Name: account; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE account (
    id integer DEFAULT nextval('seq_account'::regclass) NOT NULL,
    account_ref character varying(10),
    maf_ref character varying(10) NOT NULL,
    title character varying(4),
    f_name character varying(50) NOT NULL,
    l_name character varying(50) NOT NULL,
    company_name character varying(100),
    email character varying(100),
    address_1 character varying(100) NOT NULL,
    address_2 character varying(100),
    postcode character varying(10),
    city character varying(100) NOT NULL,
    username character varying(100) NOT NULL,
    password character varying(100) NOT NULL,
    CONSTRAINT valid_title CHECK (((title)::text = ANY ((ARRAY['MR'::character varying, 'MRS'::character varying, 'MISS'::character varying, 'SIR'::character varying, 'NONE'::character varying])::text[])))
);


ALTER TABLE public.account OWNER TO postgres;

--
-- Name: seq_area; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_area
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_area OWNER TO postgres;

--
-- Name: seq_area; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_area', 72, true);


--
-- Name: area; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE area (
    id integer DEFAULT nextval('seq_area'::regclass) NOT NULL,
    account integer NOT NULL,
    name character varying(100) NOT NULL
);


ALTER TABLE public.area OWNER TO postgres;

--
-- Name: areas; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE areas (
    id integer DEFAULT nextval('seq_area'::regclass) NOT NULL,
    account integer NOT NULL,
    name character varying(100)
);


ALTER TABLE public.areas OWNER TO postgres;

--
-- Name: seq_broodtype; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_broodtype
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_broodtype OWNER TO postgres;

--
-- Name: seq_broodtype; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_broodtype', 9, true);


--
-- Name: broodtype; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE broodtype (
    id integer DEFAULT nextval('seq_broodtype'::regclass) NOT NULL,
    account integer,
    name character varying(100) NOT NULL
);


ALTER TABLE public.broodtype OWNER TO postgres;

--
-- Name: inspections; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE inspections (
    id integer,
    site character varying(100),
    date date,
    health character varying(100),
    type character varying(100),
    afbcheck character(1),
    pestcontrol character varying(100),
    photo character varying(100),
    getsiteinspectionbroods character varying,
    extra_info text
);


ALTER TABLE public.inspections OWNER TO postgres;

--
-- Name: seq_inspection_type; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_inspection_type
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_inspection_type OWNER TO postgres;

--
-- Name: seq_inspection_type; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_inspection_type', 5, true);


--
-- Name: inspectiontype; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE inspectiontype (
    id integer DEFAULT nextval('seq_inspection_type'::regclass) NOT NULL,
    account integer NOT NULL,
    name character varying(100),
    updated date DEFAULT now()
);


ALTER TABLE public.inspectiontype OWNER TO postgres;

--
-- Name: seq_landowner; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_landowner
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_landowner OWNER TO postgres;

--
-- Name: seq_landowner; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_landowner', 24, true);


--
-- Name: landowner; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE landowner (
    id integer DEFAULT nextval('seq_landowner'::regclass) NOT NULL,
    account integer NOT NULL,
    ref_number character varying(10),
    name character varying(100) NOT NULL,
    company character varying(100),
    address_1 character varying(100),
    address_2 character varying(100),
    postcode character(4),
    city character varying(100) NOT NULL
);


ALTER TABLE public.landowner OWNER TO postgres;

--
-- Name: seq_pestcontrol; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_pestcontrol
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_pestcontrol OWNER TO postgres;

--
-- Name: seq_pestcontrol; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_pestcontrol', 11, true);


--
-- Name: pestcontrol; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE pestcontrol (
    id integer DEFAULT nextval('seq_pestcontrol'::regclass) NOT NULL,
    account integer NOT NULL,
    description character varying(100),
    updated date
);


ALTER TABLE public.pestcontrol OWNER TO postgres;

--
-- Name: seq_disease; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_disease
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_disease OWNER TO postgres;

--
-- Name: seq_disease; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_disease', 1, false);


--
-- Name: seq_disease_action; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_disease_action
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_disease_action OWNER TO postgres;

--
-- Name: seq_disease_action; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_disease_action', 1, false);


--
-- Name: seq_inspection; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_inspection
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_inspection OWNER TO postgres;

--
-- Name: seq_inspection; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_inspection', 1, false);


--
-- Name: seq_site; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_site
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_site OWNER TO postgres;

--
-- Name: seq_site; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_site', 78, true);


--
-- Name: seq_site_hives; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_site_hives
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_site_hives OWNER TO postgres;

--
-- Name: seq_site_hives; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_site_hives', 14, true);


--
-- Name: seq_site_landowner; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_site_landowner
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_site_landowner OWNER TO postgres;

--
-- Name: seq_site_landowner; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_site_landowner', 228, true);


--
-- Name: seq_site_schedule; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_site_schedule
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_site_schedule OWNER TO postgres;

--
-- Name: seq_site_schedule; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_site_schedule', 66, true);


--
-- Name: seq_siteinspection; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_siteinspection
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_siteinspection OWNER TO postgres;

--
-- Name: seq_siteinspection; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_siteinspection', 78, true);


--
-- Name: seq_siteinspection_broods; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_siteinspection_broods
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_siteinspection_broods OWNER TO postgres;

--
-- Name: seq_siteinspection_broods; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_siteinspection_broods', 1, false);


--
-- Name: seq_title; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_title
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_title OWNER TO postgres;

--
-- Name: seq_title; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_title', 1, false);


--
-- Name: seq_users; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE seq_users
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.seq_users OWNER TO postgres;

--
-- Name: seq_users; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('seq_users', 2, true);


--
-- Name: sitehives; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE sitehives (
    id integer DEFAULT nextval('seq_site_hives'::regclass) NOT NULL,
    account integer,
    brood_type integer,
    site integer,
    number integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.sitehives OWNER TO postgres;

--
-- Name: siteinspections; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE siteinspections (
    id integer DEFAULT nextval('seq_siteinspection'::regclass) NOT NULL,
    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 integer NOT NULL,
    extra_info text,
    photo_path character varying(100),
    insp_key text NOT NULL,
    hives character varying(10)
);


ALTER TABLE public.siteinspections OWNER TO postgres;

--
-- Name: siteinspections_broods; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE siteinspections_broods (
    broodid integer NOT NULL,
    inspectionid integer NOT NULL,
    account integer NOT NULL,
    broodcount integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.siteinspections_broods OWNER TO postgres;

--
-- Name: sitelandowner; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE sitelandowner (
    id integer DEFAULT nextval('seq_site_landowner'::regclass) NOT NULL,
    site integer NOT NULL,
    landowner integer NOT NULL,
    account integer
);


ALTER TABLE public.sitelandowner OWNER TO postgres;

--
-- Name: sites; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE sites (
    id integer DEFAULT nextval('seq_site'::regclass) NOT NULL,
    account integer NOT NULL,
    area integer,
    name character varying(100) NOT NULL,
    description text,
    address_1 character varying(100),
    latlng character varying(30),
    maf_ref character varying(10) NOT NULL,
    address_2 character varying(100)
);


ALTER TABLE public.sites OWNER TO postgres;

--
-- Name: siteschedule; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE siteschedule (
    id integer DEFAULT nextval('seq_site_schedule'::regclass) NOT NULL,
    account integer NOT NULL,
    site integer NOT NULL,
    date date,
    type integer NOT NULL,
    additionalinfo character varying(100)
);


ALTER TABLE public.siteschedule OWNER TO postgres;

--
-- Name: users; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE users (
    id integer DEFAULT nextval('seq_users'::regclass) NOT NULL,
    account integer NOT NULL,
    username character varying(100),
    password character varying(100),
    maf_ref character varying(5)
);


ALTER TABLE public.users OWNER TO postgres;

--
-- Data for Name: account; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY account (id, account_ref, maf_ref, title, f_name, l_name, company_name, email, address_1, address_2, postcode, city, username, password) FROM stdin;
0	\N	system	\N	system	system	\N	\N	system	\N	\N	system	system	system
1	001	N30	MR	Hayden	Bell	Dunedin Honey	hayden@atozmedia.co.nz	PO Box 2151	\N	\N	Dunedin	hayden	695d3555929f09cca1f9cc2295df8ca2
2	\N	N32	MR	Blair	Dale	Strathdale Honey	blair@strathdalehoney.com	PO Box 23			Middlemarch	blair	695d3555929f09cca1f9cc2295df8ca2
\.


--
-- Data for Name: area; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY area (id, account, name) FROM stdin;
3	1	Middlemarch
4	1	Hyde
6	1	Rock & Pillar
7	1	Moonlight
8	1	Nenthorn
9	1	Macraes
\.


--
-- Data for Name: areas; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY areas (id, account, name) FROM stdin;
2	1	Middlemarch
11	1	Rock & Pillar
12	1	Moonlight
13	1	Nenthorn
14	1	Macraes
10	1	hyde
15	1	Dunedin
50	1	wanaka
51	1	ddd
52	1	ff
53	1	Joni
54	1	a1
55	1	ffff
56	1	h1
57	1	kj
58	1	vc
59	1	hh
60	1	xc
61	1	mm
62	1	test
63	1	sammieeet
64	1	ironic
65	1	ironic two
66	1	home
67	2	Middlemarch 
68	2	MANIOTOTO
69	2	
70	2	Hartfeild 
71	2	Hartfie
72	2	HARTFIELD
\.


--
-- Data for Name: broodtype; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY broodtype (id, account, name) FROM stdin;
4	1	Singles
5	1	Doubles
6	1	Jumbo
7	2	Singles
8	2	Double
9	2	Jumbo
\.


--
-- Data for Name: inspections; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY inspections (id, site, date, health, type, afbcheck, pestcontrol, photo, getsiteinspectionbroods, extra_info) FROM stdin;
13	Wilsons, Gladbrook Rd	2013-07-19	(null)	Inspection	N	Apiguard	no photo	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
14	Gravel Pit	2013-07-19	(null)	Inspection	N	None	no photo	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
15	Wilsons, Gladbrook Rd	2013-07-19	(null)	Inspection	N	Apiguard	no photo	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
16	Wilsons, Gladbrook Rd	2013-07-19	(null)	Inspection	N	None	no photo	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
38	Farm Road Corner	2013-07-24	(null)	Inspection	N	None	no photo	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
39	Wilsons, Gladbrook Rd	2013-07-24	(null)	Inspection	N	None	no photo	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
42	Wilsons, Gladbrook Rd	2013-07-24	(null)	Inspection	N	None	myhive/sitephotos/site_3_3953288148.jpg	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
47	Wilsons, Gladbrook Rd	2013-07-24	(null)	Inspection	N	None	no photo	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
48	Wilsons, Gladbrook Rd	2013-07-25	(null)	Inspection	N	None	myhive/sitephotos/site_3_0506137208.jpg	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
51	Wilsons, Gladbrook Rd	2013-07-26	(null)	Inspection	N	None	/var/mobile/Applications/5547F76B-9BE1-46AE-831B-A34073C36AE1/Documents/site_3_0595484957.jpg	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
53	Wilsons, Gladbrook Rd	2013-07-26	(null)	Inspection	N	None	/var/mobile/Applications/5547F76B-9BE1-46AE-831B-A34073C36AE1/Documents/site_3_3254952998.jpg	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
54	Wilsons, Gladbrook Rd	2013-07-26	(null)	Inspection	N	None	/var/mobile/Applications/5547F76B-9BE1-46AE-831B-A34073C36AE1/Documents/site_3_2863789775.jpg	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
55	Wilsons, Gladbrook Rd	0001-01-01 BC		Inspection	Y	None	no photo	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
56	Gravel Pit	2013-08-06		Inspection	Y	Apiguard	site_6_1034139112.jpg	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
57	Wilsons, Gladbrook Rd	2013-08-06		Inspection	Y	None	site_3_3686376828.jpg	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
59	Avocvale	2013-08-06		Inspection	Y	None	site_19_4164200257.jpg	{{4,Singles,2},{5,Doubles,22},{6,Jumbo,12}}	
\.


--
-- Data for Name: inspectiontype; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY inspectiontype (id, account, name, updated) FROM stdin;
1	1	Feed	2013-04-24
2	1	Harvest	2013-04-24
3	1	Pest Control	2013-04-24
4	1	Maintenance	2013-04-24
5	1	Other	2013-04-24
\.


--
-- Data for Name: landowner; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY landowner (id, account, ref_number, name, company, address_1, address_2, postcode, city) FROM stdin;
1	1	N001	Robert & Callum Wilson	\N	\N	\N	\N	Middlemarch
10	1	Noo2	C G M Smith		Farm Road		    	Middlemarch
11	1		Linsay Geddes	Kelvin Grove	Top Road		    	Middlemarch
12	1	N004	Bales Elliot		McKinnon Road		    	MiddleMarch
13	1	33	D A & N J Tisdall	Avoca Tce	Mason Road		    	Middlemarch
14	1	N005	N O Grant	Glencreag	SH 87		    	Middlemarch
15	1		The Wandle	NZ Deer Ltd	Ngapuna Road		    	Middlemarch
16	1		Merry Creek	Lonestar Farms Ltd	Ngapuna Road		    	Rock & Pilliar
18	1		John Harvie	Avocvale	Nenthorn Road		    	Nenthorn
17	1		Lagon & Sue Latta	Lagon & Sue Latta	Nenthorn Road		    	Nenthorn
21	2	\N	John Harvie	Avocvale	Nenthorn Road	\N	\N	Nenthorn
22	2	\N	Logan & Sue Latta	Lagon & sue Latta	Nenthorn Road	\N	\N	Nenthorn
24	2	\N	Default	\N	\N	\N	\N	default
\.


--
-- Data for Name: pestcontrol; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY pestcontrol (id, account, description, updated) FROM stdin;
1	1	Apiguard	2013-06-10
2	1	Apilife	2013-06-10
3	1	Apistan	2013-06-10
4	1	Apitol	2013-06-10
5	1	Folbex	2013-06-10
0	1	None	2013-07-19
6	2	Apiguard	2013-12-17
7	2	Apilife	2013-12-17
8	2	Apistan	2013-12-17
9	2	Apitol	2013-12-17
10	2	Folbex	2013-12-17
11	2	None	2013-12-17
\.


--
-- Data for Name: sitehives; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY sitehives (id, account, brood_type, site, number) FROM stdin;
3	1	5	2	5
4	1	4	2	22
1	1	5	6	5
2	1	4	6	12
6	1	5	3	43
5	1	4	3	12
11	1	5	18	0
12	1	4	18	0
13	1	5	19	2
14	1	4	19	2
\.


--
-- Data for Name: siteinspections; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY siteinspections (id, account, site, insp_date, health, type, afb, pestcontrol, extra_info, photo_path, insp_key, hives) FROM stdin;
13	1	3	2013-07-19	(null)	Inspection	N	1		no photo	3af60643e9be66091e3382fd545b3a3b	\N
14	1	6	2013-07-19	(null)	Inspection	N	0		no photo	066c061d4135515f83fa8693eab5afae	\N
15	1	3	2013-07-19	(null)	Inspection	N	1		no photo	d0c3146da473dcc3d9c80a9ebea68fc7	\N
16	1	3	2013-07-19	(null)	Inspection	N	0		no photo	e7b690133a0c24002fcaf32229759b56	\N
38	1	2	2013-07-24	(null)	Inspection	N	0		no photo	e16569dfa2c299eee77d7c044633013f	\N
39	1	3	2013-07-24	(null)	Inspection	N	0		no photo	92ec307481c628d74c083ea72c52ea7c	\N
42	1	3	2013-07-24	(null)	Inspection	N	0		myhive/sitephotos/site_3_3953288148.jpg	cf7cf28e02d67e98d009fac5be3c8a1e	\N
47	1	3	2013-07-24	(null)	Inspection	N	0		no photo	da7f76a0cc853bca3efa1582595b0793	\N
48	1	3	2013-07-25	(null)	Inspection	N	0		myhive/sitephotos/site_3_0506137208.jpg	1e135482066b6cc9962a76db0474c081	\N
51	1	3	2013-07-26	(null)	Inspection	N	0		/var/mobile/Applications/5547F76B-9BE1-46AE-831B-A34073C36AE1/Documents/site_3_0595484957.jpg	2f6eb28a904c2e428307ea0462ba0a1e	\N
53	1	3	2013-07-26	(null)	Inspection	N	0		/var/mobile/Applications/5547F76B-9BE1-46AE-831B-A34073C36AE1/Documents/site_3_3254952998.jpg	d66e88ae38aa227cf24498ff686f9340	\N
54	1	3	2013-07-26	(null)	Inspection	N	0		/var/mobile/Applications/5547F76B-9BE1-46AE-831B-A34073C36AE1/Documents/site_3_2863789775.jpg	9b0a29c73f65fd2eb54b9ac5587219f0	\N
55	1	3	0001-01-01 BC		Inspection	Y	0		no photo	791c9badeed782872ee4216aa1479544	\N
56	1	6	2013-08-06		Inspection	Y	1		site_6_1034139112.jpg	f1302f910eb51bf529d276aa1be5f504	\N
57	1	3	2013-08-06		Inspection	Y	0		site_3_3686376828.jpg	e704ad0829d11053061cfb421108148b	\N
59	1	19	2013-08-06		Inspection	Y	0		site_19_4164200257.jpg	dac0e67abb062b40b781e05d1aa0748e	\N
61	1	18	2013-08-09		Inspection	Y	1		site_18_2623594475.jpg	9b7efbaa2dbf58d1637714999221e4eb	\N
62	1	3	2013-09-24		Inspection	Y	0		site_3_3628819032.jpg	64e592a25c4dcf3da217a88d7b777cd3	\N
63	1	3	2013-11-25		Inspection	Y	1		site_3_4269767905.jpg	29c8cc85ab4fcabf24dcbe8f979f2a56	\N
64	1	3	2013-09-25		Inspection	Y	0		no photo	c4b77d48779665b87e901b35cca70575	\N
65	1	19	2013-09-25		Inspection	N	0		site_19_1346283997.jpg	361065efdde1fa5dc9ee1751679efa83	\N
66	1	3	2013-09-25		Inspection	N	0		no photo	f2316fcbff627b092a22d95b011a24ea	\N
67	1	18	2013-09-25		Inspection	N	0		site_18_2159765024.jpg	e456b898963628f744e04164572d0fe3	\N
68	1	3	2013-09-27		Inspection	N	0		no photo	16be24756a5eca95d5e437ce67c19ded	\N
70	1	6	2013-09-27	Good	Inspection	Y	0		no photo	bab6d6f33d2d94c80f3a66570903e770	0
71	1	19	2013-09-27	Good	Inspection	Y	0		site_19_3911452125.jpg	c4d9c667abdc87313fe6469660945078	0
73	1	18	2013-11-06	Good	Inspection	Y	0		site_18_1535330917.jpg	ff0039ba6866573559fdfbbe15498fed	0
78	2	36	2014-05-02	Good	Inspection	Y	0		no photo	89b64bbe546829c1249ceee5841db37a	0
\.


--
-- Data for Name: siteinspections_broods; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY siteinspections_broods (broodid, inspectionid, account, broodcount) FROM stdin;
4	13	1	0
5	13	1	0
6	13	1	0
4	14	1	2
5	14	1	0
6	14	1	0
4	15	1	0
5	15	1	2
6	15	1	5
4	16	1	22
5	16	1	0
6	16	1	12
4	38	1	0
5	38	1	0
6	38	1	0
4	39	1	2
5	39	1	2
6	39	1	2
4	42	1	2
5	42	1	2
6	42	1	2
4	47	1	0
5	47	1	0
6	47	1	0
4	48	1	0
5	48	1	0
6	48	1	0
4	51	1	0
5	51	1	0
6	51	1	0
4	53	1	0
5	53	1	0
6	53	1	0
4	54	1	0
5	54	1	0
6	54	1	0
4	55	1	2
5	55	1	2
6	55	1	2
4	56	1	22
5	56	1	13
6	56	1	2
4	57	1	2
5	57	1	12
6	57	1	22
4	59	1	2
5	59	1	22
6	59	1	12
4	61	1	2
5	61	1	2
6	61	1	22
4	62	1	0
5	62	1	0
6	62	1	0
4	63	1	22
5	63	1	0
6	63	1	0
4	64	1	0
5	64	1	0
6	64	1	0
4	65	1	2
5	65	1	2
6	65	1	2
4	66	1	0
5	66	1	0
6	66	1	0
4	67	1	0
5	67	1	0
6	67	1	0
5	68	1	0
6	68	1	0
4	70	1	0
5	70	1	0
6	70	1	0
4	71	1	0
5	71	1	0
6	71	1	0
4	73	1	0
5	73	1	0
6	73	1	0
8	78	2	0
7	78	2	0
9	78	2	0
\.


--
-- Data for Name: sitelandowner; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY sitelandowner (id, site, landowner, account) FROM stdin;
1	18	15	1
2	19	18	1
3	3	1	1
5	6	13	1
6	20	13	1
8	28	12	1
10	22	18	1
173	30	18	1
177	33	10	1
178	1	21	2
4	2	22	1
179	2	22	2
31	29	18	1
7	21	11	1
\.


--
-- Data for Name: sites; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY sites (id, account, area, name, description, address_1, latlng, maf_ref, address_2) FROM stdin;
36	2	68	Dam Wall	\N	\N	\N		\N
29	1	61	gg		\N	-45.8732 , 170.5	 	\N
18	1	11	NZ Deer Farms Ltd	3km past homestead on left, 2km up farm track.	State Highway 87		N32	
19	1	13	Avocvale	In Nenthorn valley before heading out, north, in around gully on right. Just past old sheep yards.	Nenthorn Road	-45.463502,170.32465	145	
3	1	2	Wilsons, Gladbrook Rd	On road side, down broom covered fence line.		-45.50826,170.122539	bb	
47	2	67	Moy 1	\N	\N	\N		\N
48	2	67	Moy 1	\N	\N	\N		\N
21	1	53	ff		\N	-45.8555 , 170.477	 	\N
6	1	2	Gravel Pit	Just before the Ngapuna Road turnoff on the left		-45.492288,170.140809	61	
31	1	66	home	\N	\N	\N		\N
20	1	2	hayden	test	test	\N	n33	test
32	1	11	rock1	\N	\N	\N		\N
34	1	66	home2	\N	\N	\N		\N
35	1	66	home3	\N	\N	\N		\N
30	1	65	one	 	\N	-45.8746 , 170.509	 	\N
33	1	11	rock2		\N	-45.8675 , 170.599	 	\N
1	2	2	home		\N		 	\N
23	1	55	Sam	\N	\N	\N		\N
24	1	56	ddd	\N	\N	\N		\N
25	1	57	gg	\N	\N	\N		\N
26	1	58	dd	\N	\N	\N		\N
27	1	59	dd	\N	\N	\N		\N
49	2	67	Moy 1	\N	\N	\N		\N
28	1	60	dd		\N		 	\N
22	1	54	Joni		\N		 	\N
50	2	67	Moy 1	\N	\N	\N		\N
51	2	67	moy1	\N	\N	\N		\N
52	2	72	Wilsons 1	\N	\N	\N		\N
2	1	67	Farm Road Corner	 		 	 	
\.


--
-- Data for Name: siteschedule; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY siteschedule (id, account, site, date, type, additionalinfo) FROM stdin;
5	1	2	2013-04-29	5	
10	1	2	2014-07-29	3	
13	1	3	2014-04-29	2	
14	1	2	2013-04-29	5	
19	1	2	2014-07-29	3	
22	1	3	2014-04-29	2	
38	1	18	2013-04-29	5	
39	1	2	2013-05-02	2	
40	1	2	2014-07-30	3	
41	1	2	2014-07-30	3	tyttttytt
42	1	19	2013-04-30	3	hope
43	1	2	2014-07-30	3	tyttttytt
44	1	19	2013-04-30	3	hope
45	1	19	2013-04-30	3	nothing
46	1	2	2014-07-30	3	tyttttytt
47	1	19	2013-04-30	3	hope
48	1	19	2013-04-30	3	nothing
49	1	19	2013-04-30	3	ddddd
50	1	2	2013-07-30	3	new
51	1	2	2013-07-30	3	new
52	1	19	2013-04-30	3	nothing
53	1	2	2013-04-30	3	gggggggg
54	1	2	2013-04-30	3	nothing
55	1	2	0015-10-05	4	nothing to report
56	1	2	0015-10-05	4	nothing to report
57	1	2	2013-05-01	5	latest
58	1	2	2013-05-01	2	nothing
59	1	2	2013-05-01	3	nothing
60	1	2	2013-05-01	3	nothing
61	1	2	2013-05-01	3	nothing
62	1	2	2013-05-01	3	nothing
63	1	2	2013-05-01	3	nothing
64	1	2	2013-05-01	3	nothing
65	1	6	2013-07-10	3	today it is very. old
66	1	2	2015-03-10	3	very cold
\.


--
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY users (id, account, username, password, maf_ref) FROM stdin;
2	1	hayden	695d3555929f09cca1f9cc2295df8ca2	N30
1	2	blair	695d3555929f09cca1f9cc2295df8ca2	N32
\.


--
-- Name: account_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY account
    ADD CONSTRAINT account_pkey PRIMARY KEY (id);


--
-- Name: area_account_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY area
    ADD CONSTRAINT area_account_key UNIQUE (account, name);


--
-- Name: area_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY area
    ADD CONSTRAINT area_pkey PRIMARY KEY (id);


--
-- Name: areas_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY areas
    ADD CONSTRAINT areas_pkey PRIMARY KEY (id);


--
-- Name: broodtype_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY broodtype
    ADD CONSTRAINT broodtype_pkey PRIMARY KEY (id);


--
-- Name: inspectiontype_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY inspectiontype
    ADD CONSTRAINT inspectiontype_pkey PRIMARY KEY (id);


--
-- Name: landowner_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY landowner
    ADD CONSTRAINT landowner_pkey PRIMARY KEY (id);


--
-- Name: pestcontrol_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY pestcontrol
    ADD CONSTRAINT pestcontrol_pkey PRIMARY KEY (id);


--
-- Name: site_landowner_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY sitelandowner
    ADD CONSTRAINT site_landowner_pkey PRIMARY KEY (id);


--
-- Name: sitehives_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY sitehives
    ADD CONSTRAINT sitehives_pkey PRIMARY KEY (id);


--
-- Name: siteinspections_broods_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY siteinspections_broods
    ADD CONSTRAINT siteinspections_broods_pkey PRIMARY KEY (broodid, inspectionid);


--
-- Name: siteinspections_insp_key_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY siteinspections
    ADD CONSTRAINT siteinspections_insp_key_key UNIQUE (insp_key);


--
-- Name: siteinspections_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY siteinspections
    ADD CONSTRAINT siteinspections_pkey PRIMARY KEY (id);


--
-- Name: sites_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY sites
    ADD CONSTRAINT sites_pkey PRIMARY KEY (id);


--
-- Name: siteschedule_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY siteschedule
    ADD CONSTRAINT siteschedule_pkey PRIMARY KEY (id);


--
-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- Name: area_account_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY area
    ADD CONSTRAINT area_account_fkey FOREIGN KEY (account) REFERENCES account(id);


--
-- Name: areas_account_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY areas
    ADD CONSTRAINT areas_account_fkey FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;


--
-- Name: broodtype_account_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY broodtype
    ADD CONSTRAINT broodtype_account_fkey FOREIGN KEY (account) REFERENCES account(id);


--
-- Name: landowner_account_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY landowner
    ADD CONSTRAINT landowner_account_fkey FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;


--
-- Name: site_landowner_landowner_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sitelandowner
    ADD CONSTRAINT site_landowner_landowner_fkey FOREIGN KEY (landowner) REFERENCES landowner(id) ON DELETE CASCADE;


--
-- Name: site_landowner_site_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sitelandowner
    ADD CONSTRAINT site_landowner_site_fkey FOREIGN KEY (site) REFERENCES sites(id) ON DELETE CASCADE;


--
-- Name: sitehives_account_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sitehives
    ADD CONSTRAINT sitehives_account_fkey FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;


--
-- Name: sitehives_broodtype_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sitehives
    ADD CONSTRAINT sitehives_broodtype_fkey FOREIGN KEY (brood_type) REFERENCES broodtype(id) ON DELETE CASCADE;


--
-- Name: sitehives_site_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sitehives
    ADD CONSTRAINT sitehives_site_fkey FOREIGN KEY (site) REFERENCES sites(id) ON DELETE CASCADE;


--
-- Name: siteinspections_broods_broodid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY siteinspections_broods
    ADD CONSTRAINT siteinspections_broods_broodid_fkey FOREIGN KEY (broodid) REFERENCES broodtype(id) ON DELETE CASCADE;


--
-- Name: siteinspections_broods_inspectionid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY siteinspections_broods
    ADD CONSTRAINT siteinspections_broods_inspectionid_fkey FOREIGN KEY (inspectionid) REFERENCES siteinspections(id) ON DELETE CASCADE;


--
-- Name: siteinspections_pestcontrol_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY siteinspections
    ADD CONSTRAINT siteinspections_pestcontrol_fkey FOREIGN KEY (pestcontrol) REFERENCES pestcontrol(id);


--
-- Name: siteinspections_site_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY siteinspections
    ADD CONSTRAINT siteinspections_site_fkey FOREIGN KEY (site) REFERENCES sites(id);


--
-- Name: sites_account_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sites
    ADD CONSTRAINT sites_account_fkey FOREIGN KEY (account) REFERENCES account(id);


--
-- Name: sites_area_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sites
    ADD CONSTRAINT sites_area_fkey FOREIGN KEY (area) REFERENCES areas(id);


--
-- Name: siteschedule_site_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY siteschedule
    ADD CONSTRAINT siteschedule_site_fkey FOREIGN KEY (site) REFERENCES sites(id);


--
-- Name: siteschedule_type_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY siteschedule
    ADD CONSTRAINT siteschedule_type_fkey FOREIGN KEY (type) REFERENCES inspectiontype(id) ON DELETE CASCADE;


--
-- Name: users_account_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY users
    ADD CONSTRAINT users_account_fkey FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
--

