 	FORMAT CSV, DELIMITER ',', HEADER TRUE
 	)
 ;
+*/
+
+SELECT
+		count(sa_code),
+--		mn_code,
+--		dc_code,
+--		pr_code,
+--		lz_code, -- || ': '  || lz_name || ' (' || lz_abbrev || ')' AS lz,
+		lz_affected
+--		zaf.tbl_ofa_sas.wg,
+--		soc_sec AS s,
+--		pop_size,
+--		pop_curr,
+--		round(pop_curr * pc_pop * CAST( surv_def > 0.005 AS INTEGER), 0) AS pop_surv,
+--		round(pop_curr * pc_pop * CAST( lhood_def > 0.005 AS INTEGER), 0) AS pop_lhood,
+--		round(pop_curr * pc_pop * surv_def * 2100 / 3360.0 / 1000, 4) AS maize_eq,
+--		round(hh_curr * pc_pop * lhood_def, 0) AS lhood_nad
+	FROM
+		zaf.tbl_ofa_sas
+--		(VALUES (1, 'very poor'), (2, 'poor'), (3, 'middle'), (4, 'rich'), (4, 'better off'), (4, 'better-off')) AS f (ordnum,wg)
+	GROUP BY
+		lz_affected
+--		lower(zaf.tbl_ofa_sas.wg) = f.wg
+--	ORDER BY
+--		sa_code,
+--		hazard,
+--		s,
+--		f.ordnum
+;
 /*
-SELECT
-		sa_code,
-		region_nam AS region,
-		constitue1 AS constituency,
-		lz_code || ': '  || lz_name || ' (' || lz_abbrev || ')' AS lz,
-		hazard,
-		zaf.tbl_ofa_sas.wg,
-		soc_sec AS s,
---		pop_size,
-		pop_curr,
-		round(pop_curr * pc_pop * CAST( surv_def > 0.005 AS INTEGER), 0) AS pop_surv,
-		round(pop_curr * pc_pop * CAST( lhood_def > 0.005 AS INTEGER), 0) AS pop_lhood,
-		round(pop_curr * pc_pop * surv_def * 2100 / 3360.0 / 1000, 4) AS maize_eq,
-		round(hh_curr * pc_pop * lhood_def, 0) AS lhood_nad
-	FROM
-		zaf.tbl_ofa_sas,
-		(VALUES (1, 'very poor'), (2, 'poor'), (3, 'middle'), (4, 'rich'), (4, 'better off'), (4, 'better-off')) AS f (ordnum,wg)
-	WHERE
-		lower(zaf.tbl_ofa_sas.wg) = f.wg
-	ORDER BY
-		sa_code,
-		hazard,
-		s,
-		f.ordnum
-;*/
-
 COMMIT;
 
-/*
-COPY (*/
+
+COPY (
 SELECT
 			row_name[1] AS region,
 			row_name[2] AS constituency,

	lz_code integer,
--	lz_abbrev VARCHAR(5),
--	lz_name VARCHAR(254),
	wg integer,
	-- Whether they have social security (soc_sec) and the hazards they're
	-- affected by
	wg_affected varchar(30),
	lz_affected varchar(30),
	-- outcomes, percent population affected (pc_pop), livelihood deficit
	-- (lhood_def) and survival deficit (surv_def)
	pc_pop numeric,
	threshold varchar(30),
	deficit numeric,
--	surv_def NUMERIC
	)
;

-- insert the data where the hazard has been worst
SELECT 'Add in the EAS that are completely contained within the hazard area'::text;

INSERT INTO zaf.tbl_ofa_sas (
	EXTRACT (year FROM current_date),
	EXTRACT (month FROM current_date),
	sa_code,
	mn_code,
	dc_code,
	pr_code,
	pop_size,
	pop_curr,
--	hh_curr,
	lz_code,
--	lz_abbrev,
--	lz_name,
	wg,
	wg_affected,
	lz_affected,
	pc_pop,
	threshold,
	deficit
	)
	-- data comes from nested query combining SAs, SPI data, rural and urban livelihoods tables
	SELECT
			h.sa_code,
			h.mn_code,
			h.dc_code,
			h.pr_code,
			h.pop_size,
			h.pop_curr,
--			g.hh_curr,
			lz_code,
			wg,
			wg_affected,
			lz_affected,
			pc_pop,
			threshold,
			deficit
		FROM
			(
				SELECT
						lz_code,
					FROM
						zaf.tbl_ofa_outcomes,
						zaf.tbl_wgs
					WHERE
							lz_affected = 'drought'
						AND
							zaf.tbl_ofa_outcomes.wg = zaf.tbl_wgs.
				) AS f,
			(
				SELECT
						the_geom
					FROM
						zaf.vci_1601_buffer
				) AS g,
			(
				SELECT
						the_geom,
						sa_code,
						mn_code,
						dc_code,
						pr_code,
						zaf.demog_sas.pop_size,
						zaf.demog_sas.pop_size * pop_rate AS pop_curr,
--						zaf.demog_sas.hh_size * pop_2016 / zaf.tbl_pop_proj.pop_size  AS hh_curr,
						zaf.demog_sas.lz_code AS lz_code,
						h.lz_name,
						h.lz_abbrev
					FROM
						zaf.demog_sas,
						zaf.tbl_pop_agegender_12y,
						(
							SELECT
									dc_code,
									pop / total AS pop_rate,
								FROM
									(
										SELECT
												dc_name,
												sum(pop)
											AS
												pop
											FROM
												zaf.tbl_pop_proj
											WHERE
												year_mid = EXTRACT(year FROM current_date)
											GROUP BY
												dc_name
										) AS h,
									(
										SELECT
												dc_name
												total
											FROM
												zaf.tbl_pop_agegender_12y,
												zaf.demog_sas
											WHERE
												zaf.demog_sas.sa_code = zaf.tbl_pop_agegender_12y.sa_code
											GROUP BY
												dc_name
										) AS i
								WHERE
									h.dc_code = i.dc_code
							) AS j
					WHERE
							j.sa_code = zaf.tbl_pop_agegender_12y.sa_code
						AND
							zaf.demog_sas.dc_code = i.dc_code
				) AS k
		WHERE
				k.lz_code = f.lz_code
			AND
				ST_Within(k.the_geom, g.the_geom)
;

-- insert the data where the hazard is lighter
SELECT 'Add in all the hazard data in the less-affected area'::text;

-- social security, hazard and outcome information
CREATE TABLE zaf.tbl_ofa_sas (
	tid serial primary key,
	the_geom geometry(multipolygon, 201100),
-- year and month of analysis (unique to each analysis event)
	ofa_year integer,
	ofa_month integer,
