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 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,
	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,
	Soilloss	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,
	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,
	Soilloss	REAL NOT NULL,
	Cork	REAL NOT NULL
);
