-- boundaries table
CREATE TABLE boundary (boundary_id SERIAL PRIMARY KEY, boundary GEOMETRY)

-- region groups, the main regiontype groupings
CREATE TABLE regiongroup (
	id SERIAL PRIMARY KEY,
	name_en TEXT,
	name_de TEXT,
	name_fr TEXT,
	name_sp TEXT,
	name_pt TEXT,
	name_fi TEXT,
	name_nl TEXT,
	name_ja TEXT,
	name_ru TEXT
	);
	
INSERT INTO regiongroup (name_en) VALUES ('continent');
INSERT INTO regiongroup (name_en) VALUES ('region');
INSERT INTO regiongroup (name_en) VALUES ('country');
INSERT INTO regiongroup (name_en) VALUES ('state');
INSERT INTO regiongroup (name_en) VALUES ('county');
INSERT INTO regiongroup (name_en) VALUES ('city');
INSERT INTO regiongroup (name_en) VALUES ('borough');
INSERT INTO regiongroup (name_en) VALUES ('neighborhood');
INSERT INTO regiongroup (name_en) VALUES ('zip');
INSERT INTO regiongroup (name_en) VALUES ('zone');
INSERT INTO regiongroup (name_en) VALUES ('block');
INSERT INTO regiongroup (name_en) VALUES ('lot');

-- region types
CREATE TABLE regiontype (
	id SERIAL PRIMARY KEY,
	name_en TEXT,
	name_de TEXT,
	name_fr TEXT,
	name_sp TEXT,
	name_pt TEXT,
	name_fi TEXT,
	name_nl TEXT,
	name_ru TEXT,
	name_ja TEXT,
	regiongroupid INT REFERENCES regiongroup (id) MATCH SIMPLE,
	parent_id INT REFERENCES regiontype (id) MATCH SIMPLE,
	child_id INT REFERENCES regiontype (id) MATCH SIMPLE,
	info TEXT -- text to give short info to editors or similar (?)
	);
COMMENT ON COLUMN regiontype.id IS 'regiongroup equivalence, for making statistical groupings';
COMMENT ON COLUMN regiontype.parent_id IS 'regiontype parent, immediate containing regiontype';
COMMENT ON COLUMN regiontype.child_id IS 'default regiontype child, when selecting by hierarchy the next type';

-- region table for generic regions system
CREATE TABLE region (
	id INT PRIMARY KEY, 
	name_en TEXT,
	name_de TEXT,
	name_fr TEXT,
	name_sp TEXT,
	name_pt TEXT,
	name_fi TEXT,
	name_nl TEXT,
	name_ja TEXT,
	name_ru TEXT,
	id INT REFERENCES regiontype (id) MATCH SIMPLE,
	parent_object_id INT REFERENCES region (object_id) MATCH SIMPLE,
	boundary_id INT REFERENCES boundary (boundary_id) MATCH SIMPLE,
	zoomlevel INT DEFAULT 1,
	center POINT
  );
COMMENT ON COLUMN region.center IS 'center map point for base view';

-- equivalence table for types, for example berlin is (primarily) a city, but also county and state
CREATE TABLE region_equivalence (
	region_id INT REFERENCES region (region_id) MATCH SIMPLE, 
	id REFERENCES regiongroup (id) MATCH SIMPLE,
	CONSTRAINT PRIMARY KEY region_id, id
	);

-- connection table for structures
CREATE TABLE region_structure_connection (region_id INT REFERENCES region (region_id) MATCH SIMPLE, 
	structure_id INT REFERENCES structure (structure_id) MATCH SIMPLE, 
	CONSTRAINT PRIMARY KEY region_id, structure_id);


