all: subregion
D = $$0
S = $
O = $$1
T = $$2
R = $$3

subregion: xy
	sed s/$(S)/,NorthEast/ subregions/i_fulleast > i.csv
	sed s/$(S)/,NorthWest/ subregions/i_fullwest > ii.csv
	sed s/$(S)/,PaivaWest/ subregions/ip_continent > iii.csv
	sed s/$(S)/,PaivaIslands/ subregions/ip_notmideast > iiii.csv
	sed s/$(S)/,PaivaEast/ subregions/ip_mideast > iiiii.csv
	sqlite3 database_modfire.db  < imp/import_subregion_ext.sql


xy: region
	cut -f1 input/UG_Vertice_Concelho.txt > i.csv
	cut -f3 input/UG_Vertice_Concelho.txt > x.csv
	cut -f4 input/UG_Vertice_Concelho.txt > y.csv
	paste --delimiter=',' x.csv y.csv > ii.csv
	paste --delimiter=',' i.csv ii.csv > iii.csv
	sed '1d' iii.csv > ii.csv
	awk '!/1551,-/' ii.csv | awk '!/1552,-/' | awk '!/1553,-/'  | awk '!/1591,-/'  | awk '!/1592,-/'  | awk '!/1607,-/' | awk '!/1549,-/'  | awk '!/1550,-/' > iii.csv
	sqlite3 database_modfire.db  < imp/import_xy_ext.sql
	sqlite3 database_modfire.db  < imp/import_xy_ext.sql
	cut -d, -f1 iii.csv > i.csv
	cut -d, -f2 iii.csv > x.csv
	cut -d, -f3 iii.csv > y.csv
	paste --delimiter=',' x.csv y.csv > ii.csv
	python3 convert_u.py > ids.csv
	paste --delimiter='' ids.csv ii.csv > iii.csv
	sqlite3 database_modfire.db  < imp/import_xy_int.sql
	
region: conditions
	cut -f1 input/UG_Vertice_Concelho.txt > i.csv
	cut -f5 input/UG_Vertice_Concelho.txt > ii.csv
	paste --delimiter=',' i.csv ii.csv > iii.csv
	awk '!seen[$(D)]++' iii.csv > ii.csv
	sed -i '1d' ii.csv
	awk '!/1551/' ii.csv | awk '!/1552/' | awk '!/1553/'  | awk '!/1591/'  | awk '!/1592/'  | awk '!/1607/' | awk '!/1549/'  | awk '!/1550/' > iii.csv
	sqlite3 database_modfire.db  < imp/import_reg_ext.sql
	cut -d, -f1 iii.csv > i.csv
	cut -d, -f2 iii.csv > ii.csv
	python3 convert_u.py > ids.csv
	paste --delimiter='' ids.csv ii.csv > iii.csv
	sqlite3 database_modfire.db  < imp/import_reg_int.sql

conditions: conditions2
	sqlite3 database_modfire.db 'insert into wood_rewards (ug,presc,wood) select Id, Presc,sum(V_harv+V_thin)  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into soilloss (ug,presc,soilloss) select Id, Presc,sum(Soilloss)  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into perc_r0 (ug,presc,perc_r0_total) select Id, Presc, sum(Perc_r0 * area)/50  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into perc_r5 (ug,presc,perc_r5_total) select Id, Presc, sum(Perc_r5 * area)/50  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into perc_r10 (ug,presc,perc_r10_total) select Id, Presc, sum(Perc_r10 * area)/50  from action_internal group by Id,Presc;'

conditions2: adj_int_gt
	sqlite3 database_modfire.db 'insert into sbiom0 (ug,presc,sbiom0_total) select Id, Presc, sum(SBiom0)  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into sbiom5 (ug,presc,sbiom5_total) select Id, Presc, sum(SBiom5)  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into sbiom10 (ug,presc,sbiom10_total) select Id, Presc, sum(SBiom10)  from action_internal group by Id,Presc;'
	
	sqlite3 database_modfire.db 'insert into r0 (ug,presc,r0_total) select Id, Presc, sum(R0 * area)/50  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into r5 (ug,presc,r5_total) select Id, Presc, sum(R5 * area)/50  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into r10 (ug,presc,r10_total) select Id, Presc, sum(R10 * area)/50  from action_internal group by Id,Presc;'
	
	sqlite3 database_modfire.db 'insert into npv0 (ug,presc,npv0_total) select Id, Presc, sum(Npv0)  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into npv5 (ug,presc,npv5_total) select Id, Presc, sum(Npv5)  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into npv10 (ug,presc,npv10_total) select Id, Presc, sum(Npv10)  from action_internal group by Id,Presc;'
	
	sqlite3 database_modfire.db 'insert into rait0 (ug,presc,rait0_total) select Id, Presc, sum(Rait0 * area)/50  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into rait5 (ug,presc,rait5_total) select Id, Presc, sum(Rait5 * area)/50  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into rait10 (ug,presc,rait10_total) select Id, Presc, sum(Rait10 * area)/50  from action_internal group by Id,Presc;'
	
	sqlite3 database_modfire.db 'insert into cashflow0 (ug,presc,cashflow0_total) select Id, Presc, sum(Cashflow0) from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into cashflow5 (ug,presc,cashflow5_total) select Id, Presc, sum(Cashflow5) from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into cashflow10 (ug,presc,cashflow10_total) select Id, Presc, sum(Cashflow10) from action_internal group by Id,Presc;'
	
	sqlite3 database_modfire.db 'insert into cstock0 (ug,presc,cstock0_total) select Id, Presc, sum(Cstock0) from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into cstock5 (ug,presc,cstock5_total) select Id, Presc, sum(Cstock5) from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into cstock10 (ug,presc,cstock10_total) select Id, Presc, sum(Cstock10) from action_internal group by Id,Presc;'
	
	sqlite3 database_modfire.db 'insert into perc_rait0 (ug,presc,perc_rait0_total) select Id, Presc, sum(Perc_rait0 * area)/50  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into perc_rait5 (ug,presc,perc_rait5_total) select Id, Presc, sum(Perc_rait5 * area)/50  from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into perc_rait10 (ug,presc,perc_rait10_total) select Id, Presc, sum(Perc_rait10 * area)/50  from action_internal group by Id,Presc;'
	
	sqlite3 database_modfire.db 'insert into biodiversity0 (ug,presc,biodiversity0_total) select Id, Presc, sum(Biodiversity0) from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into biodiversity5 (ug,presc,biodiversity5_total) select Id, Presc, sum(Biodiversity5) from action_internal group by Id,Presc;'
	sqlite3 database_modfire.db 'insert into biodiversity10 (ug,presc,biodiversity10_total) select Id, Presc, sum(Biodiversity10) from action_internal group by Id,Presc;'
	

adj_int_gt: adj
	sed -i '1s/^/UG1,UG2,BORDER\n/' ii.csv
	python3 remove_adj_below.py 50
	sed -i '1d' i.csv
	cut -d, -f2,3,4 i.csv > iii.csv
	sqlite3 database_modfire.db  < imp/import_adj_int_gt50.sql
adj: area
	cut -f1,2,3 input/Adjacencias_VS22.txt > i.csv
	sed -i '1d' i.csv
	sed -i '/-1/d' i.csv
	awk '! a[$(D)]++' i.csv | sed "s/\t/,/g" > ii.csv
	awk -F "," '$(O) != 1551' ii.csv | awk -F "," '$(O) != 1552' | awk -F "," '$(O) != 1553' | awk -F "," '$(O) != 1591' | awk -F "," '$(O) != 1592' | awk -F "," '$(O) != 1607' > iii.csv
	awk -F "," '$(T) != 1551' iii.csv | awk -F "," '$(T) != 1552' | awk -F "," '$(T) != 1553' | awk -F "," '$(T) != 1591' | awk -F "," '$(T) != 1592' | awk -F "," '$(T) != 1607' > ii.csv
	sqlite3 database_modfire.db  < imp/import_adj_ext.sql	
	cut -d, -f1 ii.csv > i.csv
	python3 convert_u.py > ids.csv
	cut -d, -f2 ii.csv > i.csv
	python3 convert_u.py > iii.csv
	paste --delimiter='' ids.csv iii.csv > i.csv
	cut -d, -f3 ii.csv > iii.csv
	paste --delimiter='' i.csv iii.csv > ii.csv
	sqlite3 database_modfire.db  < imp/import_adj_int.sql
	



area:	action_internal
	cut -f1,5 input/Adjacencias_VS22.txt > i.csv
	sed -i '1d' i.csv
	sed -i '/-1/d' i.csv
	awk '! a[$(D)]++' i.csv | sed "s/\t/,/g" > ii.csv
	sed -i '/1551,4.23199781869/d' ii.csv
	sed -i '/1552,14.5795859997/d' ii.csv
	sed -i '/1553,11.3507636964/d' ii.csv
	sed -i '/1591,0.372829596169/d' ii.csv
	sed -i '/1592,2.11169189261/d' ii.csv
	sed -i '/1607,17.4850819576/d' ii.csv
	echo | cat missings >> ii.csv
	sqlite3 database_modfire.db  < imp/import_area_ext.sql
	cut -d, -f1 ii.csv > i.csv
	cut -d, -f2 ii.csv > iii.csv
	python3 convert_u.py > ids.csv
	paste --delimiter='' ids.csv iii.csv > ii.csv
	sqlite3 database_modfire.db  < imp/import_area_int.sql
	

action_internal: action_external
	cut -f2,3,4,5,6,7,8,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40 input/Prescricoes_VS22.txt | sed "s/\t/,/g" > ii.csv
	sed -i '1d' ii.csv
	cut -f1 input/Prescricoes_VS22.txt > i.csv
	sed -i '1d' i.csv
	python3 convert_u.py > ids.csv
	paste --delimiter='' ids.csv ii.csv > i.csv
	awk '!seen[$D]++' i.csv > iii.csv
	sqlite3 database_modfire.db  < imp/import_action_internal.sql

action_external: ug_ids
	cut -f1,2,3,4,5,6,7,8,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40 input/Prescricoes_VS22.txt | sed "s/\t/,/g" > ii.csv
	sed -i '1d' ii.csv
	awk '!seen[$D]++' ii.csv > i.csv

	sqlite3 database_modfire.db  < imp/import_action_external.sql


ug_ids: create_tables
	cut -f1 input/Prescricoes_VS22.txt > i.csv
	sed -i '1d' i.csv
	awk '!seen[$(D)]++' i.csv > ii.csv
	sqlite3 database_modfire.db < imp/import_ug.sql



create_tables:
	rm -rf *.db
	rm -rf i.csv
	rm -rf ii.csv
	rm -rf iii.csv
	rm -rf ids.csv
	sqlite3 database_modfire.db  < create.sql


clean:
	rm -rf *.db
	rm -rf *.csv
	rm -rf i.csv
	rm -rf ii.csv
	rm -rf iii.csv