DROP SEQUENCE CITYGML_ID_seq;

DROP INDEX ADDRESS_FKFEATURE_IDX;
DROP INDEX BUILDING_FKFEATURE_IDX;
DROP INDEX CityFurniture_FKFEATURE_IDX;
DROP INDEX DATE_ATTRIBUTE_FKFEATURE_IDX;
DROP INDEX EXTERNALREF_FKFEATURE_IDX;
DROP INDEX FLOAT_ATTRIBUTE_FKFEATURE_IDX;
DROP INDEX INT_ATTRIBUTE_FKFEATURE_IDX;
DROP INDEX INNER_BOUNDARY_fksolid_IDX;
DROP INDEX INNER_BOUNDARY_fkpoly_IDX;
DROP INDEX JT_LFG_LFG_fksource_IDX;
DROP INDEX JT_LFG_LFG_fktarget_IDX;
DROP INDEX LINK_FEAT_FEAT_fktarget_IDX;
DROP INDEX LINK_FEAT_GEOM_fkfeature_IDX;
DROP INDEX POLYGON_fkmaterial_IDX;
DROP INDEX POLYGON_fktexture_IDX;
DROP INDEX POLYGON_fkgeometry_IDX;
DROP INDEX POLYGON_FKSOLID_EX_IDX;
DROP INDEX POLYGON_FKSOLID_IN_IDX;
DROP INDEX Railway_FKFEATURE_IDX;
DROP INDEX Road_FKFEATURE_IDX;
DROP INDEX SOLID_FKGEOMETRY_IDX;
DROP INDEX TEXT_ATTRIBUTE_FKFEATURE_IDX;
DROP INDEX TraficArea_FKFEATURE_IDX;
DROP INDEX URI_ATTRIBUTE_FKFEATURE_IDX;
DROP INDEX WPVSData_FKFEATURE_IDX;

DROP INDEX spx_wpvsdata;
DROP INDEX spx_polygon;
DROP INDEX spx_feature_env;
DROP INDEX spx_feature_elod1tis;
DROP INDEX spx_feature_elod2tis;
DROP INDEX spx_feature_elod3tis;
DROP INDEX spx_feature_elod4tis;

SELECT DropGeometryColumn('', 'feature','envelope');
SELECT DropGeometryColumn('', 'feature','lod1tis');
SELECT DropGeometryColumn('', 'feature','lod2tis');
SELECT DropGeometryColumn('', 'feature','lod3tis');
SELECT DropGeometryColumn('', 'feature','lod4tis');
SELECT DropGeometryColumn('', 'polygon','geometry');
SELECT DropGeometryColumn('', 'wpvsdata','geometry');

DROP TABLE ADDRESS;
DROP TABLE BUILDING;
DROP TABLE CityFurniture;
DROP TABLE DATE_ATTRIBUTE;
DROP TABLE EXTERNALREF;
DROP TABLE FEATURE;
DROP TABLE FLOAT_ATTRIBUTE;
DROP TABLE GEOMETRY_;
DROP TABLE INNER_BOUNDARY;
DROP TABLE INT_ATTRIBUTE;
DROP TABLE JT_LFG_LFG;
DROP TABLE LINK_FEAT_FEAT;
DROP TABLE LINK_FEAT_GEOM;
DROP TABLE MATERIAL;
DROP TABLE POLYGON;
DROP TABLE Railway;
DROP TABLE Road;
DROP TABLE SOLID;
DROP TABLE TEXT_ATTRIBUTE;
DROP TABLE TEXTURE;
DROP TABLE TraficArea;
DROP TABLE URI_ATTRIBUTE;
DROP TABLE WPVSData;

CREATE SEQUENCE CITYGML_ID_seq;

CREATE TABLE ADDRESS ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	fk_feature INTEGER,
	code VARCHAR(50),
	street VARCHAR(250) NOT NULL,
	postalcode VARCHAR(50),
	citydistrict VARCHAR(150),
	city VARCHAR(250) NOT NULL,
	administrativearea VARCHAR(150),
	country VARCHAR(150) NOT NULL
)  ;


CREATE TABLE BUILDING ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	class VARCHAR(150),
	yearOfConstruction INTEGER,	
	fk_feature INTEGER NOT NULL,
	measuredHeight FLOAT,
	storeysAboveGround INTEGER,
	storeysBelowGround INTEGER,
	storeyHeightsAboveGround VARCHAR(250),
	storeyHeightsBelowGround VARCHAR(250),
	roofType VARCHAR(150)
) ;

CREATE TABLE CityFurniture ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	fk_feature INTEGER NOT NULL,
	dummyValue INTEGER NOT NULL
) ;


CREATE TABLE DATE_ATTRIBUTE ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	name VARCHAR(150) NOT NULL,
	value DATE,
	fk_feature INTEGER NOT NULL
) ;

CREATE TABLE EXTERNALREF ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	fk_feature INTEGER NOT NULL,
	name VARCHAR(250),
	uri VARCHAR(500),
	informationSystem VARCHAR(500)
) ;

CREATE TABLE FEATURE ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	UUID VARCHAR(50),
	type VARCHAR(150),
	creation DATE,
	revision DATE,
	deletion DATE,
	name VARCHAR(250),
	functionName VARCHAR(250),
	usage VARCHAR(250)
) ;
SELECT AddGeometryColumn('', 'feature','envelope',31466,'GEOMETRY',2);
SELECT AddGeometryColumn('', 'feature','lod1tis',31466,'GEOMETRY',3);
SELECT AddGeometryColumn('', 'feature','lod2tis',31466,'GEOMETRY',3);
SELECT AddGeometryColumn('', 'feature','lod3tis',31466,'GEOMETRY',3);
SELECT AddGeometryColumn('', 'feature','lod4tis',31466,'GEOMETRY',3);


CREATE TABLE FLOAT_ATTRIBUTE ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	name VARCHAR(150) NOT NULL,
	value FLOAT,
	fk_feature INTEGER NOT NULL
) ;


CREATE TABLE GEOMETRY_ ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	type INTEGER NOT NULL,
	functionDesc VARCHAR(150) DEFAULT 'multi'
) ;

CREATE TABLE INT_ATTRIBUTE ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	name VARCHAR(150) NOT NULL,
	value INTEGER,
	fk_feature INTEGER NOT NULL
) ;


CREATE TABLE INNER_BOUNDARY ( 
    ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	fk_solID INTEGER NOT NULL,
	fk_polygon INTEGER NOT NULL
);

CREATE TABLE JT_LFG_LFG ( 
	fk_source INTEGER NOT NULL,
	fk_target INTEGER NOT NULL
);

CREATE TABLE LINK_FEAT_FEAT ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	fk_source INTEGER NOT NULL,
	fk_target INTEGER NOT NULL,
	creation DATE,
	deletion DATE,
	role VARCHAR(150)
) ;


CREATE TABLE LINK_FEAT_GEOM ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	fk_feature INTEGER,
	lod INTEGER,
	type INTEGER,
	functionDesc VARCHAR(250),
	transmatrix VARCHAR(280)
) ;

CREATE TABLE MATERIAL ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	shininess FLOAT,
	transparency FLOAT,
	ambientIntensity FLOAT,
	specularColor VARCHAR(50),
	diffuseColor VARCHAR(50),
	emissiveColor VARCHAR(50)
) ;

CREATE TABLE POLYGON ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	creation DATE,
	deletion DATE,
	revision DATE,
	orientation CHAR(1) DEFAULT '+',
	functionDesc VARCHAR(250),
	fk_material INTEGER,
	fk_texture INTEGER,
	fk_geometry INTEGER,
	FK_SOLID_EX INTEGER,
    fk_solid_in INTEGER
) ;
SELECT AddGeometryColumn('', 'polygon','geometry',31466,'GEOMETRY',3);

CREATE TABLE Railway ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	fk_feature INTEGER NOT NULL,
	dummyValue INTEGER NOT NULL
) ;

CREATE TABLE Road ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	fk_feature INTEGER NOT NULL,
	dummyValue INTEGER NOT NULL
) ;

CREATE TABLE SOLID ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	functionDesc VARCHAR(150),
	fk_geometry INTEGER NOT NULL
) ;


CREATE TABLE TEXT_ATTRIBUTE ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	name VARCHAR(150) NOT NULL,
	value VARCHAR(4000),
	fk_feature INTEGER NOT NULL
) ;

CREATE TABLE TEXTURE ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	textureMap VARCHAR(500) NOT NULL,
	textureCoordinates VARCHAR(2000) NOT NULL,
	textureType VARCHAR(10) NOT NULL,
	repeat INTEGER DEFAULT 0 NOT NULL
) ;

CREATE TABLE TraficArea ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	fk_feature INTEGER NOT NULL,
	surfaceMaterial VARCHAR(500)
) ;

CREATE TABLE URI_ATTRIBUTE ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	name VARCHAR(150) NOT NULL,
	value VARCHAR(500),
	fk_feature INTEGER NOT NULL
) ;

CREATE TABLE WPVSData ( 
	ID INTEGER DEFAULT NEXTVAL('CITYGML_ID_seq'::TEXT) NOT NULL,
	fk_feature INTEGER NOT NULL,
	shininess FLOAT,
	transparency FLOAT,
	ambientIntensity FLOAT,
	specularColor FLOAT,
	diffuseColor FLOAT,
	emissiveColor FLOAT,
	textureMap VARCHAR(500),
	textureCoordinates VARCHAR(2000),
	textureType VARCHAR(12),
	repeat INTEGER DEFAULT 0,
	lod INTEGER,
	geomsize FLOAT
) ;
SELECT AddGeometryColumn('', 'wpvsdata','geometry',31466,'GEOMETRY',3);
SELECT AddGeometryColumn('', 'wpvsdata','envelope',31466,'GEOMETRY',2);


ALTER TABLE ADDRESS ADD CONSTRAINT PK_ADDRESS 
	PRIMARY KEY (ID) ;

ALTER TABLE BUILDING ADD CONSTRAINT PK_BUILDING 
	PRIMARY KEY (ID) ;

ALTER TABLE CityFurniture ADD CONSTRAINT PK_CityFurniture 
	PRIMARY KEY (ID) ;

ALTER TABLE DATE_ATTRIBUTE ADD CONSTRAINT PK_DATE_ATTRIBUTE 
	PRIMARY KEY (ID) ;

ALTER TABLE EXTERNALREF ADD CONSTRAINT PK_EXTERNALREF 
	PRIMARY KEY (ID) ;

ALTER TABLE FEATURE ADD CONSTRAINT PK_FEATURE 
	PRIMARY KEY (ID) ;

ALTER TABLE FLOAT_ATTRIBUTE ADD CONSTRAINT PK_FLOAT_ATTRIBUTE 
	PRIMARY KEY (ID) ;

ALTER TABLE GEOMETRY_ ADD CONSTRAINT PK_GEOMETRY_ 
	PRIMARY KEY (ID) ;

ALTER TABLE INNER_BOUNDARY ADD CONSTRAINT PK_INNER_BOUNDARY 
	PRIMARY KEY (ID) ;
	
ALTER TABLE INT_ATTRIBUTE ADD CONSTRAINT PK_INT_ATTRIBUTE 
	PRIMARY KEY (ID) ;

ALTER TABLE LINK_FEAT_FEAT ADD CONSTRAINT PK_LINK_FEAT_FEAT 
	PRIMARY KEY (ID) ;

ALTER TABLE LINK_FEAT_GEOM ADD CONSTRAINT PK_LINK_FEAT_GEOM 
	PRIMARY KEY (ID) ;

ALTER TABLE MATERIAL ADD CONSTRAINT PK_MATERIAL 
	PRIMARY KEY (ID) ;

ALTER TABLE POLYGON ADD CONSTRAINT PK_POLYGONS 
	PRIMARY KEY (ID) ;

ALTER TABLE Railway ADD CONSTRAINT PK_Railway 
	PRIMARY KEY (ID) ;

ALTER TABLE Road ADD CONSTRAINT PK_Road 
	PRIMARY KEY (ID) ;

ALTER TABLE SOLID ADD CONSTRAINT PK_SOLID 
	PRIMARY KEY (ID) ;

ALTER TABLE TEXT_ATTRIBUTE ADD CONSTRAINT PK_TEXT_ATTRIBUTE 
	PRIMARY KEY (ID) ;

ALTER TABLE TEXTURE ADD CONSTRAINT PK_TEXTURE 
	PRIMARY KEY (ID) ;

ALTER TABLE TraficArea ADD CONSTRAINT PK_TraficArea 
	PRIMARY KEY (ID) ;

ALTER TABLE URI_ATTRIBUTE ADD CONSTRAINT PK_URI_ATTRIBUTE 
	PRIMARY KEY (ID) ;

ALTER TABLE WPVSData ADD CONSTRAINT PK_WPVSData 
	PRIMARY KEY (ID) ;

commit;
