CREATE TABLE ancestors (
	id INTEGER NOT NULL,
	ancestor_id INTEGER NOT NULL,
	ancestor_placetype TEXT,
	lastmodified INTEGER
);

CREATE INDEX ancestors_by_id ON ancestors (id,ancestor_placetype,lastmodified);
CREATE INDEX ancestors_by_ancestor ON ancestors (ancestor_id,ancestor_placetype,lastmodified);
CREATE INDEX ancestors_by_lastmod ON ancestors (lastmodified);

CREATE TABLE concordances (
	id INTEGER NOT NULL,
	concordance_id INTEGER NOT NULL,
	concordance_souce TEXT,
	lastmodified INTEGER
);

CREATE INDEX concordances_by_id ON concordances (id,lastmodified);
CREATE INDEX concordances_by_other ON concordances (other_source,other_id);	
CREATE INDEX concordances_by_other_lastmod ON concordances (other_source,other_id,lastmodified);
CREATE INDEX ancestors_by_lastmod ON concordances (lastmodified);`

CREATE TABLE geojson (
	id INTEGER NOT NULL,
	body TEXT,
	source TEXT,
	is_alt BOOLEAN,
	alt_label TEXT,
	lastmodified INTEGER
);

CREATE UNIQUE INDEX geojson_by_id ON %s (id, source, alt_label);
CREATE INDEX geojson_by_alt ON %s (id, is_alt, alt_label);
CREATE INDEX geojson_by_lastmod ON %s (lastmodified);

CREATE TABLE geometry (
	id INTEGER NOT NULL,
	body TEXT,
	is_alt BOOLEAN,
	alt_label TEXT,
	lastmodified INTEGER
);

CREATE UNIQUE INDEX geojson_by_id ON %s (id, alt_label);
CREATE INDEX geojson_by_alt ON %s (id, is_alt, alt_label);
CREATE INDEX geojson_by_lastmod ON %s (lastmodified);

CREATE TABLE geometries (
	id INTEGER NOT NULL,
	type TEXT,
	is_alt TINYINT,
	alt_label TEXT,
	lastmodified INTEGER
);

SELECT InitSpatialMetaData();
SELECT AddGeometryColumn('geometries', 'geom', 4326, 'GEOMETRY', 'XY');
SELECT CreateSpatialIndex('geometries', 'geom');

CREATE UNIQUE INDEX by_id ON geometries (id, alt_label);
CREATE INDEX geometries_by_lastmod ON geometries (lastmodified);`

CREATE TABLE names (
       id INTEGER NOT NULL,
       placetype TEXT,
       country TEXT,
       language TEXT,
       extlang TEXT,
       script TEXT,
       region TEXT,
       variant TEXT,
       extension TEXT,
       privateuse TEXT,
       name TEXT,
       lastmodified INTEGER
);

CREATE INDEX names_by_lastmod ON names (lastmodified);
CREATE INDEX names_by_country ON names (country,privateuse,placetype);
CREATE INDEX names_by_language ON names (language,privateuse,placetype);
CREATE INDEX names_by_placetype ON names (placetype,country,privateuse);
CREATE INDEX names_by_name ON names (name, placetype, country);
CREATE INDEX names_by_name_private ON names (name, privateuse, placetype, country);
CREATE INDEX names_by_wofid ON names (id);

CREATE TABLE properties (
	id INTEGER NOT NULL,
	body TEXT,
	is_alt BOOLEAN,
	alt_label TEXT,
	lastmodified INTEGER
);

CREATE UNIQUE INDEX geojson_by_id ON %s (id, alt_label);
CREATE INDEX geojson_by_alt ON %s (id, is_alt, alt_label);
CREATE INDEX geojson_by_lastmod ON %s (lastmodified);

CREATE VIRTUAL TABLE %s USING rtree (
	id,
	min_x,
	max_x,
	min_y,
	max_y,
	+wof_id INTEGER,
	+is_alt TINYINT,
	+alt_label TEXT,
	+geometry BLOB,	
	+lastmodified INTEGER
);

CREATE VIRTUAL TABLE search USING fts4(
	id, placetype,
	name, names_all, names_preferred, names_variant, names_colloquial,		
	is_current, is_ceased, is_deprecated, is_superseded
);

CREATE TABLE spr (
	id INTEGER NOT NULL PRIMARY KEY,
	parent_id INTEGER,
	name TEXT,
	placetype TEXT,
	country TEXT,
	repo TEXT,
	latitude REAL,
	longitude REAL,
	min_latitude REAL,
	min_longitude REAL,
	max_latitude REAL,
	max_longitude REAL,
	is_current INTEGER,
	is_deprecated INTEGER,
	is_ceased INTEGER,
	is_superseded INTEGER,
	is_superseding INTEGER,
	superseded_by TEXT,
	supersedes TEXT,
	is_alt TINYINT,
	alt_label TEXT,	
	lastmodified INTEGER
);

CREATE INDEX spr_by_lastmod ON spr (lastmodified);
CREATE INDEX spr_by_parent ON spr (parent_id, is_current, lastmodified);
CREATE INDEX spr_by_placetype ON spr (placetype, is_current, lastmodified);
CREATE INDEX spr_by_country ON spr (country, placetype, is_current, lastmodified);
CREATE INDEX spr_by_name ON spr (name, placetype, is_current, lastmodified);
CREATE INDEX spr_by_centroid ON spr (latitude, longitude, is_current, lastmodified);
CREATE INDEX spr_by_bbox ON spr (min_latitude, min_longitude, max_latitude, max_longitude, placetype, is_current, lastmodified);
CREATE INDEX spr_by_repo ON spr (repo, lastmodified);
CREATE INDEX spr_by_current ON spr (is_current, lastmodified);
CREATE INDEX spr_by_deprecated ON spr (is_deprecated, lastmodified);
CREATE INDEX spr_by_ceased ON spr (is_ceased, lastmodified);
CREATE INDEX spr_by_superseded ON spr (is_superseded, lastmodified);
CREATE INDEX spr_by_superseding ON spr (is_superseding, lastmodified);
CREATE INDEX spr_obsolete ON spr (is_deprecated, is_superseded);

CREATE TABLE %s (
	id INTEGER NOT NULL,
	superseded_id INTEGER NOT NULL,
	superseded_by_id INTEGER NOT NULL,
	lastmodified INTEGER
);

CREATE UNIQUE INDEX supersedes_by ON %s (id,superseded_id, superseded_by_id);