
---- Import MB counts CSV file
--DROP TABLE IF EXISTS testing.mb_2016_counts;
--CREATE TABLE testing.mb_2016_counts (
--    mb_2016_code bigint,
--    mb_category_name_2016 text NOT NULL,
--    area_albers_sqkm double precision,
--    dwelling integer default 0,
--    person integer default 0,
--	address_count integer default 0,
--    state smallint NOT NULL,
--    geom geometry(MultiPolygon, 4283),
--    CONSTRAINT abs_2011_mb_pk PRIMARY KEY (mb_2016_code)
--);
--
--COPY testing.mb_2016_counts (mb_2016_code, mb_category_name_2016, area_albers_sqkm, dwelling, person, state)
--FROM '/Users/hugh.saalmans/git/minus34/gnaf-loader/supporting-files/2016 census mesh block counts.csv' WITH (FORMAT CSV, HEADER);
--
--ANALYSE testing.mb_2016_counts;
--
---- Get address counts per meshblock -- 1 min
--WITH counts AS (
--	SELECT mb_2016_code,
--		   count(*) AS address_count
--	FROM gnaf_202408.address_principals
--	GROUP BY mb_2016_code
--)
--UPDATE testing.mb_2016_counts AS mb
--  SET address_count = counts.address_count
--  FROM counts
--  WHERE mb.mb_2016_code = counts.mb_2016_code
--;
--ANALYSE testing.mb_2016_counts;
--
---- add geoms
--UPDATE testing.mb_2016_counts AS mb
--  SET geom = bdys.geom
--  FROM admin_bdys_202408.abs_2016_mb as bdys
--  WHERE mb.mb_2016_code = bdys.mb_16code::bigint;
--
--ANALYSE testing.mb_2016_counts;
--
--CREATE INDEX mb_2016_counts_geom_idx ON testing.mb_2016_counts USING gist(geom);
--ALTER TABLE testing.mb_2016_counts CLUSTER ON mb_2016_counts_geom_idx;


-- create an address accurate dwelling map of Australia
-- get the correct number of addresses from GNAF for each meshblock, based on dwelling counts -- 10 mins

--    1. where address count is greater than dwelling count
DROP TABLE IF EXISTS testing.address_principals_dwelling;
CREATE TABLE testing.address_principals_dwelling AS
WITH adr AS (
SELECT gnaf.gnaf_pid,
       gnaf.postcode,
       gnaf.mb_2016_code,
       mb.dwelling,
       mb.person,
       mb.address_count,
       gnaf.geom
FROM gnaf_202408.address_principals as gnaf
INNER JOIN testing.mb_2016_counts AS mb on gnaf.mb_2016_code = mb.mb_2016_code
WHERE mb.address_count >= mb.dwelling
  AND mb.dwelling > 0
), row_nums as (
 SELECT *, row_number() OVER (PARTITION BY mb_2016_code ORDER BY random()) as row_num
 FROM adr
)
SELECT gnaf_pid,
       postcode,
       mb_2016_code,
       address_count,
       dwelling,
       person,
       'too many addresses'::text as dwelling_count_type,
       geom
FROM row_nums
WHERE row_num <= dwelling
ORDER BY mb_2016_code,
      row_num
;
ANALYSE testing.address_principals_dwelling;

--    2. where address count is less than dwelling count
INSERT INTO testing.address_principals_dwelling
WITH adr AS (
SELECT gnaf.gnaf_pid,
       gnaf.postcode,
       gnaf.mb_2016_code,
       mb.dwelling,
       mb.person,
       mb.address_count,
       gnaf.geom,
       generate_series(1, ceiling(mb.dwelling::float / mb.address_count::float)::integer) as duplicate_number
FROM gnaf_202408.address_principals as gnaf
INNER JOIN testing.mb_2016_counts AS mb on gnaf.mb_2016_code = mb.mb_2016_code
WHERE mb.address_count < mb.dwelling
    AND address_count > 0
), row_nums as (
 SELECT *, row_number() OVER (PARTITION BY mb_2016_code ORDER BY duplicate_number, random()) as row_num
 FROM adr
)
SELECT gnaf_pid,
       postcode,
       mb_2016_code,
       address_count,
       dwelling,
       person,
       'too few addresses' as dwelling_count_type,
       geom
FROM row_nums
WHERE row_num <= dwelling
ORDER BY mb_2016_code,
      row_num
;
ANALYSE testing.address_principals_dwelling;

--   3. add random points in meshblocks that have no addresses (8,903 dwellings affected)
INSERT INTO testing.address_principals_dwelling
SELECT 'MB' || mb_2016_code::text || '_' || (row_number() OVER ())::text as gnaf_pid,
       null::text as postcode,
       mb_2016_code,
       address_count,
       dwelling,
       person,
       'no addresses' as dwelling_count_type,
       (ST_Dump(ST_GeneratePoints(geom, dwelling))).geom as geom
FROM testing.mb_2016_counts
WHERE geom is not null
AND address_count = 0
AND dwelling > 0
;
ANALYSE testing.address_principals_dwelling;

CREATE INDEX basic_address_principals_dwelling_geom_idx ON testing.address_principals_dwelling USING gist (geom);
ALTER TABLE testing.address_principals_dwelling CLUSTER ON basic_address_principals_dwelling_geom_idx;

CREATE INDEX basic_address_principals_dwelling_mb_2016_code_idx ON testing.address_principals_dwelling USING btree(mb_2016_code);
CREATE INDEX basic_address_principals_dwelling_postcode_idx ON testing.address_principals_dwelling USING btree(postcode);


-- create an address accurate population map of Australia
-- get the correct number of addresses from GNAF for each meshblock, based on population -- 23 mins

--    1. where dwellings are greater than population
DROP TABLE IF EXISTS testing.address_principals_persons;
CREATE TABLE testing.address_principals_persons AS
WITH adr AS (
	SELECT gnaf.gnaf_pid,
		   gnaf.postcode,
           gnaf.mb_2016_code,
	       mb.dwelling,
	       mb.person,
           mb.address_count,
           gnaf.geom
	FROM testing.address_principals_dwelling as gnaf
	INNER JOIN testing.mb_2016_counts AS mb on gnaf.mb_2016_code = mb.mb_2016_code
	WHERE mb.dwelling >= mb.person
	  AND mb.dwelling > 0
), row_nums as (
    SELECT *, row_number() OVER (PARTITION BY mb_2016_code ORDER BY random()) as row_num
    FROM adr
)
SELECT gnaf_pid,
       postcode,
	   mb_2016_code,
	   address_count,
	   dwelling,
	   person,
	   'too many dwellings'::text as person_count_type,
	   geom
FROM row_nums
WHERE row_num <= person
ORDER BY mb_2016_code,
         row_num
;
ANALYSE testing.address_principals_persons;

--    2. where dwelling count is less than population (in other words - multiple people live at each address)
INSERT INTO testing.address_principals_persons
WITH adr AS (
	SELECT gnaf.gnaf_pid,
           gnaf.postcode,
           gnaf.mb_2016_code,
	       mb.dwelling,
	       mb.person,
           mb.address_count,
           gnaf.geom,
		   generate_series(1, ceiling(mb.person::float / mb.dwelling::float)::integer) as duplicate_number
	FROM testing.address_principals_dwelling as gnaf
	INNER JOIN testing.mb_2016_counts AS mb on gnaf.mb_2016_code = mb.mb_2016_code
	WHERE mb.dwelling < mb.person
), row_nums as (
    SELECT *, row_number() OVER (PARTITION BY mb_2016_code ORDER BY duplicate_number, random()) as row_num
    FROM adr
)
SELECT gnaf_pid,
       postcode,
	   mb_2016_code,
	   address_count,
	   dwelling,
	   person,
	   'too few dwellings' as person_count_type,
	   geom
FROM row_nums
WHERE row_num <= person
ORDER BY mb_2016_code,
         row_num
;
ANALYSE testing.address_principals_persons;

--    3. where dwellings = 0 and addresses are greater than population
INSERT INTO testing.address_principals_persons
WITH adr AS (
	SELECT gnaf.gnaf_pid,
           gnaf.postcode,
           gnaf.mb_2016_code,
	       mb.dwelling,
	       mb.person,
           mb.address_count,
           gnaf.geom
	FROM gnaf_202408.address_principals as gnaf
	INNER JOIN testing.mb_2016_counts AS mb on gnaf.mb_2016_code = mb.mb_2016_code
	WHERE mb.address_count >= mb.person
	  AND mb.dwelling = 0
), row_nums as (
    SELECT *, row_number() OVER (PARTITION BY mb_2016_code ORDER BY random()) as row_num
    FROM adr
)
SELECT gnaf_pid,
	   postcode,
	   mb_2016_code,
	   address_count,
	   dwelling,
	   person,
	   'too many addresses'::text as person_count_type,
	   geom
FROM row_nums
WHERE row_num <= person
ORDER BY mb_2016_code,
         row_num
;
ANALYSE testing.address_principals_persons;

--    4. where dwellings = 0 and addresses are less than population
INSERT INTO testing.address_principals_persons
WITH adr AS (
	SELECT gnaf.gnaf_pid,
           gnaf.postcode,
           gnaf.mb_2016_code,
	       mb.dwelling,
	       mb.person,
           mb.address_count,
           gnaf.geom,
		   generate_series(1, ceiling(mb.person::float / mb.address_count::float)::integer) as duplicate_number
	FROM gnaf_202408.address_principals as gnaf
	INNER JOIN testing.mb_2016_counts AS mb on gnaf.mb_2016_code = mb.mb_2016_code
	WHERE mb.address_count < mb.person
	  AND mb.address_count > 0
	  AND mb.dwelling = 0
), row_nums as (
    SELECT *, row_number() OVER (PARTITION BY mb_2016_code ORDER BY duplicate_number, random()) as row_num
    FROM adr
)
SELECT gnaf_pid,
	   postcode,
	   mb_2016_code,
	   address_count,
	   dwelling,
	   person,
	   'too few addresses' as person_count_type,
	   geom
FROM row_nums
WHERE row_num <= person
ORDER BY mb_2016_code,
         row_num
;
ANALYSE testing.address_principals_persons;

--   5. add random points in meshblocks that have no addresses (8,903 dwellings affected)
INSERT INTO testing.address_principals_persons
SELECT 'MB' || mb_2016_code::text || '_' || (row_number() OVER ())::text as gnaf_pid,
       null::text as postcode,
	   mb_2016_code,
	   address_count,
	   dwelling,
	   person,
	   'no addresses' as dwelling_count_type,
       (ST_Dump(ST_GeneratePoints(geom, person))).geom as geom
FROM testing.mb_2016_counts
WHERE geom is not null
AND address_count = 0
AND dwelling = 0
AND person > 0
;
ANALYSE testing.address_principals_persons;

CREATE INDEX basic_address_principals_persons_geom_idx ON testing.address_principals_persons USING gist (geom);
ALTER TABLE testing.address_principals_persons CLUSTER ON basic_address_principals_persons_geom_idx;

CREATE INDEX basic_address_principals_persons_mb_2016_code_idx ON testing.address_principals_persons USING btree(mb_2016_code);
CREATE INDEX basic_address_principals_persons_postcode_idx ON testing.address_principals_persons USING btree(postcode);







-- QA

-- check dwelling counts by meshblock -- all good!
select sum(dwelling) from testing.mb_2016_counts where geom is not null; -- 9,913,151
select sum(dwelling) from testing.mb_2016_counts where geom is null; -- 286
select count(*) from testing.address_principals_dwelling; -- 9,910,984

-- 9 rows -- 2 with >10 addreses
select * from testing.mb_2016_counts
where mb_2016_code NOT IN (select distinct mb_2016_code from testing.address_principals_dwelling)
and geom is not null
and dwelling > 0;


-- dwelling count differences by MB
with gnaf as (
	select  mb_2016_code, count(*) as dwelling from testing.address_principals_dwelling group by mb_2016_code
)
select mb.dwelling - gnaf.dwelling as dwelling_diff,
       mb.*
from testing.mb_2016_counts as mb
inner join gnaf on mb.mb_2016_code = gnaf.mb_2016_code
    and mb.dwelling <> gnaf.dwelling
order by dwelling_diff desc
;

-- check population counts by meshblock -- all good!
select sum(person) from testing.mb_2016_counts where geom is not null; -- 23351637
select sum(person) from testing.mb_2016_counts where geom is null; -- 46354
select count(*) from testing.address_principals_persons; -- 23347751

-- 16 rows -- 4 >=10 persons
select * from testing.mb_2016_counts
where mb_2016_code NOT IN (select distinct mb_2016_code from testing.address_principals_persons)
and geom is not null
and person > 0
;

-- MBs missing persons in the address points -- 184
with gnaf as (
	select  mb_2016_code, count(*) as person from testing.address_principals_persons group by mb_2016_code
)
select mb.person - gnaf.person as person_diff,
       mb.*
from testing.mb_2016_counts as mb
inner join gnaf on mb.mb_2016_code = gnaf.mb_2016_code
and mb.person <> gnaf.person
order by person_diff desc
;

-- TO DO
-- create a table of random points per person based on meshblocks




-- testing

---- 32509 MBs with less addresses than dwellings
--SELECT count(*) as cnt,
--       avg(dwelling - address_count),
--	   max(dwelling - address_count)
--FROM testing.mb_2016_counts
--WHERE address_count < dwelling;
--
--select *, dwelling - address_count as extra_dwellings
--FROM testing.mb_2016_counts
--WHERE address_count < dwelling
--order by extra_dwellings desc;

-- -- 269858 MBs with more addresses than dwellings
-- SELECT count(*) FROM testing.mb_2016_counts
-- WHERE address_count > dwelling;

-- -- 15363 MBs with the same address count as dwellings
-- SELECT count(*) FROM testing.mb_2016_counts
-- WHERE address_count = dwelling;


---- 8484 MBs with less population than dwellings
--SELECT count(*) as cnt,
--       avg(dwelling - person),
--	   max(dwelling - person)
--FROM testing.mb_2016_counts
--WHERE person < dwelling;
--
--select *, dwelling - person as extra_dwellings
--FROM testing.mb_2016_counts
--WHERE person < dwelling
--order by extra_dwellings desc;
