CREATE TABLE subregion_ext (
	ug	INTEGER NOT NULL UNIQUE,
	region	TEXT NOT NULL,
	PRIMARY KEY(ug)
);

CREATE TABLE xy_med_int (
	ug	INTEGER NOT NULL,
	x	REAL NOT NULL,
	y	REAL NOT NULL
);


CREATE TABLE xy_med_ext (
	ug	INTEGER NOT NULL,
	x	REAL NOT NULL,
	y	REAL NOT NULL
);


CREATE TABLE xy_int (
	ug	INTEGER NOT NULL,
	x	REAL NOT NULL,
	y	REAL NOT NULL
);


CREATE TABLE xy_ext (
	ug	INTEGER NOT NULL,
	x	REAL NOT NULL,
	y	REAL NOT NULL
);

CREATE TABLE region_ext (
	ug	INTEGER NOT NULL UNIQUE,
	region	TEXT NOT NULL,
	PRIMARY KEY(ug)
);

CREATE TABLE region_int (
	ug	INTEGER NOT NULL UNIQUE,
	region	TEXT NOT NULL,
	PRIMARY KEY(ug)
);

CREATE TABLE perc_r10 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	perc_r10_total	REAL NOT NULL
);

CREATE TABLE perc_r5 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	perc_r5_total	REAL NOT NULL
);

CREATE TABLE perc_r0 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	perc_r0_total	REAL NOT NULL
);

CREATE TABLE sbiom0 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	sbiom0_total	REAL NOT NULL
);

CREATE TABLE sbiom5 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	sbiom5_total	REAL NOT NULL
);

CREATE TABLE sbiom10 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	sbiom10_total	REAL NOT NULL
);


CREATE TABLE r0 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	r0_total	REAL NOT NULL
);
CREATE TABLE r5 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	r5_total	REAL NOT NULL
);
CREATE TABLE r10 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	r10_total	REAL NOT NULL
);

CREATE TABLE npv0 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	npv0_total	REAL NOT NULL
);
CREATE TABLE npv5 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	npv5_total	REAL NOT NULL
);
CREATE TABLE npv10 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	npv10_total	REAL NOT NULL
);

CREATE TABLE rait0 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	rait0_total	REAL NOT NULL
);
CREATE TABLE rait5 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	rait5_total	REAL NOT NULL
);
CREATE TABLE rait10 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	rait10_total	REAL NOT NULL
);

CREATE TABLE perc_rait0 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	perc_rait0_total	REAL NOT NULL
);
CREATE TABLE perc_rait5 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	perc_rait5_total	REAL NOT NULL
);
CREATE TABLE perc_rait10 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	perc_rait10_total	REAL NOT NULL
);

CREATE TABLE cashflow0 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	cashflow0_total	REAL NOT NULL
);
CREATE TABLE cashflow5 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	cashflow5_total	REAL NOT NULL
);
CREATE TABLE cashflow10 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	cashflow10_total	REAL NOT NULL
);

CREATE TABLE biodiversity0 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	biodiversity0_total	REAL NOT NULL
);
CREATE TABLE biodiversity5 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	biodiversity5_total	REAL NOT NULL
);
CREATE TABLE biodiversity10 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	biodiversity10_total	REAL NOT NULL
);

CREATE TABLE cstock0 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	cstock0_total	REAL NOT NULL
);
CREATE TABLE cstock5 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	cstock5_total	REAL NOT NULL
);
CREATE TABLE cstock10 (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	cstock10_total	REAL NOT NULL
);



CREATE TABLE soilloss (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	soilloss	REAL NOT NULL
);

CREATE TABLE wood_rewards (
	ug	INTEGER NOT NULL,
	presc	INTEGER NOT NULL,
	wood	REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS adj_int_gt50(
	ug1 INTEGER NOT NULL,
	ug2 INTEGER NOT NULL,
	front REAL NOT NULL,
	PRIMARY KEY(ug1,ug2)
	FOREIGN KEY(ug1) REFERENCES ug(ug)
	FOREIGN KEY(ug2) REFERENCES ug(ug));

CREATE TABLE IF NOT EXISTS adj_int(
	ug1 INTEGER NOT NULL,
	ug2 INTEGER NOT NULL,
	front REAL NOT NULL,
	PRIMARY KEY(ug1,ug2)
	FOREIGN KEY(ug1) REFERENCES ug(ug)
	FOREIGN KEY(ug2) REFERENCES ug(ug));


CREATE TABLE IF NOT EXISTS adj_ext(
	ug1 INTEGER NOT NULL,
	ug2 INTEGER NOT NULL,
	front REAL NOT NULL,
	PRIMARY KEY(ug1,ug2)
	FOREIGN KEY(ug1) REFERENCES ug(ug)
	FOREIGN KEY(ug2) REFERENCES ug(ug));

CREATE TABLE IF NOT EXISTS area_internal (
	ug	INTEGER NOT NULL UNIQUE,
	area	REAL NOT NULL,
	PRIMARY KEY(ug)
	FOREIGN KEY(ug) REFERENCES ug(ug));

CREATE TABLE IF NOT EXISTS area_external (
	ug	INTEGER NOT NULL UNIQUE,
	area	REAL NOT NULL,
	PRIMARY KEY(ug)
	FOREIGN KEY(ug) REFERENCES ug(ug));

CREATE TABLE IF NOT EXISTS ug_ids (
	external	TEXT NOT NULL UNIQUE,
	internal	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE
);


CREATE TABLE IF NOT EXISTS action_external (
	Id	TEXT NOT NULL,
	Area	REAL NOT NULL,
	Presc	INTEGER NOT NULL,
	Year	INTEGER NOT NULL,
	Age	INTEGER NOT NULL,
	TotalVolume REAL NOT NULL,
	V_thin	REAL NOT NULL,
	V_harv	REAL NOT NULL,
	Species	TEXT NOT NULL,
	SBiom0	REAL NOT NULL,
	R0	REAL NOT NULL,
	Npv0	REAL NOT NULL,
	Rait0	REAL NOT NULL,
	SBiom5	REAL NOT NULL,
	R5	REAL NOT NULL,
	Npv5	REAL NOT NULL,
	Rait5	REAL NOT NULL,
	SBiom10	REAL NOT NULL,
	R10	REAL NOT NULL,
	Npv10	REAL NOT NULL,
	Rait10	REAL NOT NULL,
	Biodiversity0	REAL NOT NULL,
	Biodiversity5	REAL NOT NULL,	
	Biodiversity10	REAL NOT NULL,
	Perc_r0	REAL NOT NULL,
	Perc_r5	REAL NOT NULL,
	Perc_r10	REAL NOT NULL,
	Perc_rait0	REAL NOT NULL,
	Perc_rait5	REAL NOT NULL,
	Perc_rait10	REAL NOT NULL,
	Soilloss	REAL NOT NULL,
	Cashflow0	REAL NOT NULL,
	Cashflow5	REAL NOT NULL,
	Cashflow10	REAL NOT NULL,
	Cstock0	REAL NOT NULL,
	Cstock5	REAL NOT NULL,
	Cstock10	REAL NOT NULL,
	Cork	REAL NOT NULL
);
CREATE TABLE IF NOT EXISTS action_internal (
	Id	TEXT NOT NULL,
	Area	REAL NOT NULL,
	Presc	INTEGER NOT NULL,
	Year	INTEGER NOT NULL,
	Age	INTEGER NOT NULL,
	TotalVolume REAL NOT NULL,
	V_thin	REAL NOT NULL,
	V_harv	REAL NOT NULL,
	Species	TEXT NOT NULL,
	SBiom0	REAL NOT NULL,
	R0	REAL NOT NULL,
	Npv0	REAL NOT NULL,
	Rait0	REAL NOT NULL,
	SBiom5	REAL NOT NULL,
	R5	REAL NOT NULL,
	Npv5	REAL NOT NULL,
	Rait5	REAL NOT NULL,
	SBiom10	REAL NOT NULL,
	R10	REAL NOT NULL,
	Npv10	REAL NOT NULL,
	Rait10	REAL NOT NULL,
	Biodiversity0	REAL NOT NULL,
	Biodiversity5	REAL NOT NULL,	
	Biodiversity10	REAL NOT NULL,
	Perc_r0	REAL NOT NULL,
	Perc_r5	REAL NOT NULL,
	Perc_r10	REAL NOT NULL,
	Perc_rait0	REAL NOT NULL,
	Perc_rait5	REAL NOT NULL,
	Perc_rait10	REAL NOT NULL,
	Soilloss	REAL NOT NULL,
	Cashflow0	REAL NOT NULL,
	Cashflow5	REAL NOT NULL,
	Cashflow10	REAL NOT NULL,
	Cstock0	REAL NOT NULL,
	Cstock5	REAL NOT NULL,
	Cstock10	REAL NOT NULL,
	Cork	REAL NOT NULL
);