DB=./afdelingen.sqlite3

install:
	pip install xlsx2csv

drop_all:
	rm -f ${DB}

download_municipalities:
	wget -O ./raw/gemeenten.xlsx https://www.cbs.nl/-/media/_excel/2020/47/gemeenten-alfabetisch-2021.xlsx

download_map_continental:
	wget -O ./raw/gemeenten.svg https://upload.wikimedia.org/wikipedia/commons/3/30/Nederland_gemeenten_2021.svg

download_map_caribbean:
	wget -O ./raw/caribbean.svg https://upload.wikimedia.org/wikipedia/commons/a/ab/Dutch_Caribbean_location_map.svg

# https://www.cbs.nl/nl-nl/maatwerk/2020/39/buurt-wijk-en-gemeente-2020-voor-postcode-huisnummer
download_pc7:
	wget -O ./raw/pc7.zip https://www.cbs.nl/-/media/_excel/2020/39/2020-cbs-pc6huisnr20200801-buurt.zip

download_regex_plugin:
	wget -O re.so https://github.com/nalgeon/sqlean/releases/download/0.15.1/re.so

download_all: download_municipalities download_map_continental download_map_caribbean download_pc7 download_regex_plugin

convert_gemeenten:
	xlsx2csv ./raw/gemeenten.xlsx > ./raw/gemeenten.csv

convert_pc7:
	unzip ./raw/pc7.zip -d ./raw/pc7; \
	rm ./raw/pc7.zip; \
	./scripts/pc7.sh

convert_airtable:
	dos2unix ./source/leden-Alles.csv

convert_all: convert_gemeenten convert_pc7 convert_airtable

import_afdelingen:
	sqlite3 ${DB} 'DROP TABLE IF EXISTS afdelingen;'; \
	sqlite3 ${DB} -separator ";" '.import ./source/afdelingen.csv afdelingen'

import_gemeenten_2020:
	sqlite3 ${DB} 'DROP TABLE IF EXISTS gemeenten_2020;'; \
	sqlite3 ${DB} -separator ";" '.import ./out/gemeenten2020_2021.csv gemeenten_2020'

import_gemeenten:
	sqlite3 ${DB} 'DROP TABLE IF EXISTS gemeenten;'; \
	sqlite3 ${DB} -separator "," '.import ./raw/gemeenten.csv gemeenten'

import_pc6_huisnr:
	sqlite3 ${DB} 'DROP TABLE IF EXISTS pc6_huisnr;'; \
	sqlite3 ${DB} -separator ";" '.import ./raw/pc7/pc6hnr20200801_gwb.csv pc6_huisnr'

import_airtable_leden:
	sqlite3 ${DB} 'DROP TABLE IF EXISTS airtable_leden;'; \
	sqlite3 ${DB} -separator "," '.import ./source/leden-Alles.csv airtable_leden'

import_bij1_gebieden:
	sqlite3 ${DB} 'DROP TABLE IF EXISTS bij1_gebieden;'; \
	sqlite3 ${DB} -separator "," '.import ./source/bij1-gebieden.csv bij1_gebieden'

import_all: import_afdelingen import_gemeenten_2020 import_gemeenten import_pc6_huisnr import_airtable_leden import_bij1_gebieden

# join source with our annotations
join_gemeenten:
	sqlite3 ${DB} 'DROP VIEW IF EXISTS afdelingsgebieden; CREATE VIEW afdelingsgebieden AS SELECT Gemeentecode, gemeenten.GemeentecodeGM, gemeenten.Gemeentenaam, Provinciecode, ProvinciecodePV, Provincienaam, is_kern_gemeente, afdeling, benodigde_ondersteuningsverklaringen, verzamelde_ondersteuningsverklaringen FROM gemeenten LEFT JOIN bij1_gebieden ON gemeenten.GemeentecodeGM = bij1_gebieden.GemeentecodeGM;'

join_pc7:
	sqlite3 ${DB} 'DROP VIEW IF EXISTS pc7; CREATE VIEW pc7 AS SELECT PC6, Huisnummer, Gemeentenaam2020 FROM pc6_huisnr LEFT JOIN gemeenten_2020 ON pc6_huisnr.Gemeente2020 = gemeenten_2020.Gemcode2020;'

join_pc7_bij1:
	sqlite3 ${DB} 'DROP VIEW IF EXISTS pc7_bij1; CREATE VIEW pc7_bij1 AS SELECT * FROM pc7 LEFT JOIN afdelingsgebieden ON pc7.Gemeentenaam2020 = afdelingsgebieden.Gemeentenaam;'

# CREATE VIEW leden_pc7 AS 
join_airtable_pc7:
	sqlite3 ${DB} "SELECT load_extension('${PWD}/re')" "DROP VIEW IF EXISTS leden_pc7; SELECT *, REPLACE(UPPER(airtable_leden.Postcode), ' ', '') AS PC6, regexp_replace(airtable_leden.Straat, '[^0-9]+', '') AS Huisnummer FROM airtable_leden;"

join_airtable:
	sqlite3 ${DB} "DROP VIEW IF EXISTS leden_afdeling; CREATE VIEW leden_afdeling AS SELECT * FROM leden_pc7 LEFT JOIN pc7_bij1 ON airtable_leden.PC6 = pc7_bij1.PC6 AND airtable_leden.Huisnummer = pc7_bij1.Huisnummer;"

join_all: join_gemeenten join_pc7 join_pc7_bij1 join_airtable_pc7 join_airtable

dump_afdelingsgebieden:
	sqlite3 -header -csv ${DB} "select * from afdelingsgebieden;" > ./out/afdelingsgebieden.csv

dump_leden:
	sqlite3 -header -csv ${DB} "select * from leden_afdeling;" > ./out/leden_afdeling.csv

dump_all: dump_afdelingsgebieden dump_leden

svg_tooltips:
	cp ./raw/gemeenten.svg ./generated/gemeenten-tooltipped.svg; \
	./scripts/svg-tooltip.sh

svg_afdelingen:
	cp ./generated/gemeenten-tooltipped.svg ./generated/gemeenten-afdelingen.svg; \
	./scripts/svg-afdelingen.sh

svg_ondersteuningsverklaringen:
	cp ./generated/gemeenten-tooltipped.svg ./generated/gemeenten-ondersteuningsverklaringen.svg; \
	./scripts/svg-ondersteuningsverklaringen.sh

svg_all: svg_tooltips svg_afdelingen svg_ondersteuningsverklaringen

run_all: install drop_all download_all convert_all import_all join_all dump_all svg_all

bij1_to_afdelingen_svg: import_gemeenten import_bij1_gebieden join_gemeenten dump_afdelingsgebieden svg_afdelingen

bij1_to_ondersteuningsverklaringen_svg: import_gemeenten import_bij1_gebieden join_gemeenten dump_afdelingsgebieden svg_ondersteuningsverklaringen