-- DROP ROLE gwbase;

CREATE ROLE gwbase WITH 
	SUPERUSER
	CREATEDB
	CREATEROLE
	INHERIT
	LOGIN
	REPLICATION
	BYPASSRLS
	CONNECTION LIMIT -1;

-- DROP SCHEMA gwbase;

CREATE SCHEMA gwbase AUTHORIZATION gwbase;

-- public.gwb_userselection definition

-- Drop table

-- DROP TABLE public.gwb_userselection;

CREATE TABLE public.gwb_userselection (
	user_id int4 NOT NULL,
	well_id int4 NOT NULL,
	list_type int4 NULL
);


-- public.gwb_version definition

-- Drop table

-- DROP TABLE public.gwb_version;

CREATE TABLE public.gwb_version (
	"version" bpchar(10) NULL,
	version9 bpchar(10) NULL,
	backupdate timestamp NULL
);


-- public.gwb_wellstdtodocategories definition

-- Drop table

-- DROP TABLE public.gwb_wellstdtodocategories;

CREATE TABLE public.gwb_wellstdtodocategories (
	well_id int4 NOT NULL,
	category_id int4 NOT NULL,
	repeatmonths int4 NULL,
	CONSTRAINT gwb_wellstdtodocategories_pk PRIMARY KEY (well_id, category_id)
);
-- DROP FUNCTION gwbase.getdate();

CREATE OR REPLACE FUNCTION gwbase.getdate()
 RETURNS timestamp with time zone
 LANGUAGE sql
 STABLE
AS $function$select now()$function$
;
-- gwbase.app_config_ definition

-- Drop table

-- DROP TABLE gwbase.app_config_;

CREATE TABLE gwbase.app_config_ (
	id serial4 NOT NULL,
	module_id varchar(32) NULL,
	"key" varchar(32) NULL,
	valuei int4 NULL,
	valuef float8 NULL,
	"values" text NULL,
	key_ varchar(32) NULL,
	value_i int4 NULL,
	value_f float8 NULL,
	value_s text NULL,
	mandant_id int4 NULL,
	CONSTRAINT pk_app_config_ PRIMARY KEY (id)
);
CREATE INDEX ix_appcfg_mandmodkey ON gwbase.app_config_ USING btree (mandant_id, module_id, key_);
CREATE INDEX ix_appcfg_module ON gwbase.app_config_ USING btree (module_id);
CREATE INDEX ix_appcfg_modulekey ON gwbase.app_config_ USING btree (module_id, key_);


-- gwbase.db_version_ definition

-- Drop table

-- DROP TABLE gwbase.db_version_;

CREATE TABLE gwbase.db_version_ (
	module_id varchar(32) NOT NULL,
	mandant_id int4 NULL,
	"version" int4 NULL,
	CONSTRAINT pk_db_version_ PRIMARY KEY (module_id)
);
CREATE INDEX ix_dbvers_mandmod ON gwbase.db_version_ USING btree (mandant_id, module_id);


-- gwbase.gpd_abweichungen definition

-- Drop table

-- DROP TABLE gwbase.gpd_abweichungen;

CREATE TABLE gwbase.gpd_abweichungen (
	msid int4 NOT NULL,
	recid int4 NOT NULL,
	aw_bis float8 NULL,
	aw_neigung float8 NULL,
	aw_azimut float8 NULL,
	aw_xw float8 NULL,
	aw_yw float8 NULL,
	aw_zw float8 NULL,
	aw_datum timestamp NULL,
	CONSTRAINT gpd_abweichungen_pk PRIMARY KEY (msid, recid)
);


-- gwbase.gpd_plfirma definition

-- Drop table

-- DROP TABLE gwbase.gpd_plfirma;

CREATE TABLE gwbase.gpd_plfirma (
	plf_id int4 NOT NULL,
	plf_listid int4 NULL,
	plf_name1 varchar(64) NULL,
	plf_name2 varchar(64) NULL,
	plf_plz varchar(10) NULL,
	plf_ort varchar(64) NULL,
	plf_strasse varchar(64) NULL,
	plf_telefon1 varchar(64) NULL,
	plf_telefon2 varchar(64) NULL,
	plf_fax1 varchar(64) NULL,
	plf_fax2 varchar(64) NULL,
	plf_email1 varchar(64) NULL,
	plf_email2 varchar(64) NULL,
	plf_typ int4 NULL,
	plf_land int4 NULL,
	plf_www varchar(255) NULL,
	plf_bemerkung varchar(255) NULL,
	CONSTRAINT gpd_plfirma_pk PRIMARY KEY (plf_id)
);


-- gwbase.gpd_plperson definition

-- Drop table

-- DROP TABLE gwbase.gpd_plperson;

CREATE TABLE gwbase.gpd_plperson (
	plp_id int4 NOT NULL,
	plp_listid int4 NULL,
	plp_vorname varchar(64) NULL,
	plp_nachname varchar(64) NULL,
	plp_name varchar(128) NULL,
	CONSTRAINT gpd_plperson_pk PRIMARY KEY (plp_id)
);


-- gwbase.gpd_plstandard definition

-- Drop table

-- DROP TABLE gwbase.gpd_plstandard;

CREATE TABLE gwbase.gpd_plstandard (
	pls_id int4 NOT NULL,
	pls_listid int4 NULL,
	pls_keysep varchar(12) NULL,
	pls_key varchar(12) NULL,
	pls_name varchar(255) NULL,
	pls_pttypeindex int4 NULL,
	pls_flag1 int4 NULL,
	pls_las varchar(12) NULL,
	pls_issep varchar(1) NULL,
	CONSTRAINT gpd_plstandard_pk PRIMARY KEY (pls_id)
);


-- gwbase.gpd_stratigraphie definition

-- Drop table

-- DROP TABLE gwbase.gpd_stratigraphie;

CREATE TABLE gwbase.gpd_stratigraphie (
	strt_id int4 NOT NULL,
	strt_parentid int4 NULL,
	strt_hierarchiestufe int4 NULL,
	strt_listid int4 NULL,
	strt_order int4 NULL,
	strt_kuerzel varchar(24) NULL,
	strt_kuerzeloffiziell varchar(24) NULL,
	strt_bezeichnung varchar(128) NULL,
	strt_bemerkung varchar(255) NULL,
	strt_lithologieid int4 NULL,
	strt_farbe int4 NULL,
	strt_importsynonyme varchar(255) NULL,
	strt_bereichdefjn varchar(1) NULL,
	strt_bereichvon int4 NULL,
	strt_bereichbis int4 NULL,
	strt_sepzs varchar(32) NULL,
	strt_issep varchar(1) NULL,
	CONSTRAINT gpd_stratigraphie_pk PRIMARY KEY (strt_id)
);


-- gwbase.gpd_stratlists definition

-- Drop table

-- DROP TABLE gwbase.gpd_stratlists;

CREATE TABLE gwbase.gpd_stratlists (
	slst_id int4 NOT NULL,
	slst_hierarchiebasis int4 NULL,
	slst_bezeichnung varchar(255) NULL,
	slst_profiltecfile varchar(255) NULL,
	slst_bemerkung varchar(255) NULL,
	CONSTRAINT gpd_stratlists_pk PRIMARY KEY (slst_id)
);


-- gwbase.gpd_synonymkat definition

-- Drop table

-- DROP TABLE gwbase.gpd_synonymkat;

CREATE TABLE gwbase.gpd_synonymkat (
	id int4 NOT NULL,
	syn_sortierung int4 NULL,
	syn_name varchar(50) NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwtransferdate timestamp NULL,
	CONSTRAINT gpd_synonymkat_pk PRIMARY KEY (id)
);


-- gwbase.gpd_tempausbau definition

-- Drop table

-- DROP TABLE gwbase.gpd_tempausbau;

CREATE TABLE gwbase.gpd_tempausbau (
	msid int4 NOT NULL,
	recid int4 NOT NULL,
	taro_ok float8 NULL,
	taro_uk float8 NULL,
	taro_typ int4 NULL,
	taro_material int4 NULL,
	taro_durchmesser float8 NULL,
	taro_wandstaerke float8 NULL,
	taro_bemerkung varchar(254) NULL,
	CONSTRAINT gpd_tempausbau_pk PRIMARY KEY (msid, recid)
);


-- gwbase.gpd_tempschichten definition

-- Drop table

-- DROP TABLE gwbase.gpd_tempschichten;

CREATE TABLE gwbase.gpd_tempschichten (
	msid int4 NOT NULL,
	recid int4 NOT NULL,
	tsd_uk float8 NULL,
	tsd_art varchar(128) NULL,
	tsd_stratigrafie varchar(254) NULL,
	tsd_petrografie varchar(254) NULL,
	tsd_bemerkung varchar(254) NULL,
	CONSTRAINT gpd_tempschichten_pk PRIMARY KEY (msid, recid)
);


-- gwbase.gwb_analymethods definition

-- Drop table

-- DROP TABLE gwbase.gwb_analymethods;

CREATE TABLE gwbase.gwb_analymethods (
	id int4 NOT NULL,
	methodname varchar(100) NULL,
	isold varchar(1) NULL,
	CONSTRAINT gwb_analymethods_pk PRIMARY KEY (id)
);


-- gwbase.gwb_berichte definition

-- Drop table

-- DROP TABLE gwbase.gwb_berichte;

CREATE TABLE gwbase.gwb_berichte (
	berichtsnr int4 NOT NULL,
	titel1 varchar(50) NULL,
	titel2 varchar(50) NULL,
	titel3 varchar(50) NULL,
	titel4 varchar(50) NULL,
	CONSTRAINT gwb_berichte_pk PRIMARY KEY (berichtsnr)
);


-- gwbase.gwb_betreib definition

-- Drop table

-- DROP TABLE gwbase.gwb_betreib;

CREATE TABLE gwbase.gwb_betreib (
	betreiber varchar(50) NOT NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_betreib_pk PRIMARY KEY (betreiber)
);


-- gwbase.gwb_bohrfirm definition

-- Drop table

-- DROP TABLE gwbase.gwb_bohrfirm;

CREATE TABLE gwbase.gwb_bohrfirm (
	bohrfirma varchar(50) NOT NULL,
	adresse1 varchar(50) NULL,
	adresse2 varchar(50) NULL,
	bemerkungen varchar(250) NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_bohrfirm_pk PRIMARY KEY (bohrfirma)
);


-- gwbase.gwb_bohrverf definition

-- Drop table

-- DROP TABLE gwbase.gwb_bohrverf;

CREATE TABLE gwbase.gwb_bohrverf (
	bohrverfahren varchar(50) NOT NULL,
	bemerkungen varchar(250) NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_bohrverf_pk PRIMARY KEY (bohrverfahren)
);


-- gwbase.gwb_buildingmaterialcat definition

-- Drop table

-- DROP TABLE gwbase.gwb_buildingmaterialcat;

CREATE TABLE gwbase.gwb_buildingmaterialcat (
	id int4 NOT NULL,
	itemname varchar(50) NOT NULL,
	CONSTRAINT gwb_buildingmaterialcat_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX gwb_buildigmaterialcat_idx1 ON gwbase.gwb_buildingmaterialcat USING btree (itemname);


-- gwbase.gwb_catchmentareas definition

-- Drop table

-- DROP TABLE gwbase.gwb_catchmentareas;

CREATE TABLE gwbase.gwb_catchmentareas (
	id int4 NOT NULL,
	areaname varchar(50) NOT NULL
);
CREATE UNIQUE INDEX gwb_catchmentarea_idx1 ON gwbase.gwb_catchmentareas USING btree (areaname);


-- gwbase.gwb_changelogger definition

-- Drop table

-- DROP TABLE gwbase.gwb_changelogger;

CREATE TABLE gwbase.gwb_changelogger (
	id serial4 NOT NULL,
	modifiedby varchar(20) NOT NULL,
	modifieddate timestamp NOT NULL,
	modifiedfields text NULL,
	commandtype varchar(1) NULL,
	tablename varchar(50) NULL,
	tabledisplayname varchar(50) NULL,
	identifiertext varchar(50) NULL,
	identifierid int4 NULL,
	CONSTRAINT gwb_changelogger_pk PRIMARY KEY (id)
);


-- gwbase.gwb_dbversions definition

-- Drop table

-- DROP TABLE gwbase.gwb_dbversions;

CREATE TABLE gwbase.gwb_dbversions (
	id int4 NOT NULL,
	pidrill int4 NULL,
	pisqleditor int4 NULL,
	piweb int4 NULL,
	CONSTRAINT gwb_dbversions_pk PRIMARY KEY (id)
);


-- gwbase.gwb_dischargestatistic definition

-- Drop table

-- DROP TABLE gwbase.gwb_dischargestatistic;

CREATE TABLE gwbase.gwb_dischargestatistic (
	well_id int4 NOT NULL,
	statyear int4 NULL,
	qsum_1 float8 NULL,
	qsum_2 float8 NULL,
	qsum_3 float8 NULL,
	qsum_4 float8 NULL,
	qsum_5 float8 NULL,
	qsum_6 float8 NULL,
	qsum_7 float8 NULL,
	qsum_8 float8 NULL,
	qsum_9 float8 NULL,
	qsum_10 float8 NULL,
	qsum_11 float8 NULL,
	qsum_12 float8 NULL,
	qsum_total float8 NULL,
	qdailymax_1 float8 NULL,
	qdatedailymax_1 timestamp NULL,
	qdailymax_2 float8 NULL,
	qdatedailymax_2 timestamp NULL,
	qdailymax_3 float8 NULL,
	qdatedailymax_3 timestamp NULL,
	qdailymax_4 float8 NULL,
	qdatedailymax_4 timestamp NULL,
	qdailymax_5 float8 NULL,
	qdatedailymax_5 timestamp NULL,
	qdailymax_6 float8 NULL,
	qdatedailymax_6 timestamp NULL,
	qdailymax_7 float8 NULL,
	qdatedailymax_7 timestamp NULL,
	qdailymax_8 float8 NULL,
	qdatedailymax_8 timestamp NULL,
	qdailymax_9 float8 NULL,
	qdatedailymax_9 timestamp NULL,
	qdailymax_10 float8 NULL,
	qdatedailymax_10 timestamp NULL,
	qdailymax_11 float8 NULL,
	qdatedailymax_11 timestamp NULL,
	qdailymax_12 float8 NULL,
	qdatedailymax_12 timestamp NULL
);


-- gwbase.gwb_doccategories definition

-- Drop table

-- DROP TABLE gwbase.gwb_doccategories;

CREATE TABLE gwbase.gwb_doccategories (
	id int4 NOT NULL,
	category varchar(50) NOT NULL,
	CONSTRAINT gwb_doccategories_pk PRIMARY KEY (id)
);


-- gwbase.gwb_doctypes definition

-- Drop table

-- DROP TABLE gwbase.gwb_doctypes;

CREATE TABLE gwbase.gwb_doctypes (
	id int4 NOT NULL,
	doctype varchar(50) NOT NULL,
	CONSTRAINT gwb_doctypes_pk PRIMARY KEY (id)
);


-- gwbase.gwb_einheit definition

-- Drop table

-- DROP TABLE gwbase.gwb_einheit;

CREATE TABLE gwbase.gwb_einheit (
	einheit varchar(10) NOT NULL,
	keynr varchar(10) NULL,
	unit_type int4 NULL,
	CONSTRAINT gwb_einheit_pk PRIMARY KEY (einheit)
);


-- gwbase.gwb_elevationquality definition

-- Drop table

-- DROP TABLE gwbase.gwb_elevationquality;

CREATE TABLE gwbase.gwb_elevationquality (
	id int4 NOT NULL,
	qualitytext varchar(50) NOT NULL,
	CONSTRAINT gwb_elevationquality_pk PRIMARY KEY (id)
);


-- gwbase.gwb_fieldchecks definition

-- Drop table

-- DROP TABLE gwbase.gwb_fieldchecks;

CREATE TABLE gwbase.gwb_fieldchecks (
	tname varchar(100) NOT NULL,
	fname varchar(100) NOT NULL,
	fnamedisplay varchar(100) NULL,
	"options" int4 NULL,
	valuemin float8 NULL,
	valuemax float8 NULL,
	fcondition varchar(100) NULL,
	CONSTRAINT gwb_fieldchecks_pk PRIMARY KEY (tname, fname)
);


-- gwbase.gwb_flowmeasurementcat definition

-- Drop table

-- DROP TABLE gwbase.gwb_flowmeasurementcat;

CREATE TABLE gwbase.gwb_flowmeasurementcat (
	id int4 NOT NULL,
	itemname varchar(50) NOT NULL,
	CONSTRAINT gwb_flowmeasurementcat_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX gwb_flowmeasurementcat_idx1 ON gwbase.gwb_flowmeasurementcat USING btree (itemname);


-- gwbase.gwb_geologie definition

-- Drop table

-- DROP TABLE gwbase.gwb_geologie;

CREATE TABLE gwbase.gwb_geologie (
	id int4 NOT NULL,
	geologischeeinheit varchar(50) NOT NULL,
	seqnr int4 NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_geologie_pk PRIMARY KEY (id)
);
CREATE INDEX gwb_geologie_idx1 ON gwbase.gwb_geologie USING btree (geologischeeinheit);
CREATE INDEX gwb_geologie_idx2 ON gwbase.gwb_geologie USING btree (seqnr);


-- gwbase.gwb_geraete definition

-- Drop table

-- DROP TABLE gwbase.gwb_geraete;

CREATE TABLE gwbase.gwb_geraete (
	probennahmegeraet varchar(50) NOT NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_geraete_pk PRIMARY KEY (probennahmegeraet)
);


-- gwbase.gwb_graphics definition

-- Drop table

-- DROP TABLE gwbase.gwb_graphics;

CREATE TABLE gwbase.gwb_graphics (
	id int4 NOT NULL,
	well_id int4 NULL,
	graphicfile varchar(255) NULL,
	filedescription varchar(255) NULL,
	filetype varchar(10) NULL,
	sortno int4 NULL,
	creator_id int4 NULL,
	user_id int4 NULL,
	project_id int4 NULL,
	doctype_id int4 NULL,
	category_id int4 NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	displayname varchar(64) NULL,
	fileordir varchar(1) DEFAULT 'f'::character varying NULL,
	CONSTRAINT gwb_graphics_pk PRIMARY KEY (id)
);


-- gwbase.gwb_grenzen definition

-- Drop table

-- DROP TABLE gwbase.gwb_grenzen;

CREATE TABLE gwbase.gwb_grenzen (
	xxmin float8 NULL,
	xxmax float8 NULL,
	ymin float8 NULL,
	ymax float8 NULL
);


-- gwbase.gwb_groups definition

-- Drop table

-- DROP TABLE gwbase.gwb_groups;

CREATE TABLE gwbase.gwb_groups (
	g_id int4 NOT NULL,
	p_id int4 NOT NULL,
	g_type int2 NULL,
	g_name varchar(50) NULL,
	g_xcoord float8 NULL,
	g_ycoord float8 NULL,
	po1_id int4 NULL,
	po2_id int4 NULL,
	po1_ref varchar(25) NULL,
	po2_ref varchar(25) NULL,
	g_comments text NULL,
	datechange timestamp NULL,
	proj_id int4 NULL,
	CONSTRAINT gwb_groups_pk PRIMARY KEY (g_id)
);


-- gwbase.gwb_gwstockw definition

-- Drop table

-- DROP TABLE gwbase.gwb_gwstockw;

CREATE TABLE gwbase.gwb_gwstockw (
	id int4 NOT NULL,
	grundwasserstockwerk varchar(50) NOT NULL,
	seqnr int4 NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_gwstockw_pk PRIMARY KEY (id)
);
CREATE INDEX gwb_gwstockw_idx1 ON gwbase.gwb_gwstockw USING btree (grundwasserstockwerk);
CREATE INDEX gwb_gwstockw_idx2 ON gwbase.gwb_gwstockw USING btree (seqnr);


-- gwbase.gwb_imports definition

-- Drop table

-- DROP TABLE gwbase.gwb_imports;

CREATE TABLE gwbase.gwb_imports (
	id int4 NOT NULL,
	importdate timestamp NULL,
	importedby varchar(30) NULL,
	fileordir varchar(255) NULL,
	nooffiles int4 NULL,
	importformat varchar(100) NULL,
	CONSTRAINT gwb_import_pk PRIMARY KEY (id)
);


-- gwbase.gwb_ionratios definition

-- Drop table

-- DROP TABLE gwbase.gwb_ionratios;

CREATE TABLE gwbase.gwb_ionratios (
	date_id int4 NOT NULL,
	mg_ca float8 NULL,
	na_mg float8 NULL,
	nak_cl float8 NULL,
	cl_so4 float8 NULL,
	cl_na float8 NULL,
	camg_nak float8 NULL,
	na_k float8 NULL,
	cl_br float8 NULL,
	hco3_clso4 float8 NULL,
	k_ca float8 NULL,
	done varchar(1) DEFAULT 'n'::character varying NULL
);


-- gwbase.gwb_keyword_objects definition

-- Drop table

-- DROP TABLE gwbase.gwb_keyword_objects;

CREATE TABLE gwbase.gwb_keyword_objects (
	keyword_id int4 NOT NULL,
	object_id int4 NOT NULL,
	object_type varchar(1) NOT NULL,
	CONSTRAINT gwb_keyword_objects_pk PRIMARY KEY (keyword_id, object_id, object_type)
);


-- gwbase.gwb_keywords definition

-- Drop table

-- DROP TABLE gwbase.gwb_keywords;

CREATE TABLE gwbase.gwb_keywords (
	id int4 NOT NULL,
	keyword varchar(50) NOT NULL,
	category_id int4 NULL,
	CONSTRAINT gwb_keywords_pk PRIMARY KEY (id)
);


-- gwbase.gwb_labels definition

-- Drop table

-- DROP TABLE gwbase.gwb_labels;

CREATE TABLE gwbase.gwb_labels (
	label_wellnr1 varchar(50) NULL,
	label_wellnr2 varchar(50) NULL,
	label_wellname varchar(50) NULL,
	label_map varchar(50) NULL,
	label_welltype varchar(50) NULL,
	label_wellusage varchar(50) NULL,
	label_wellcity varchar(50) NULL,
	label_welldistrict varchar(50) NULL,
	label_wellsubdistr varchar(50) NULL,
	label_wellsubdistrno varchar(50) NULL,
	label_position varchar(50) NULL,
	label_owner varchar(50) NULL,
	label_operatedby varchar(50) NULL,
	label_xcoord varchar(50) NULL,
	label_ycoord varchar(50) NULL,
	label_userfield1 varchar(50) NULL,
	label_userfield2 varchar(50) NULL,
	label_userfield3 varchar(50) NULL,
	label_userfield4 varchar(50) NULL,
	label_userfield5 varchar(50) NULL,
	label_projrefno1 varchar(50) NULL,
	label_projrefno2 varchar(50) NULL,
	label_projrefno3 varchar(50) NULL,
	label_projrefno4 varchar(50) NULL,
	label_projrefno5 varchar(50) NULL,
	label_projlogic1 varchar(50) NULL,
	label_projlogic2 varchar(50) NULL,
	label_projlogic3 varchar(50) NULL,
	label_projlogic4 varchar(50) NULL,
	label_projlogic5 varchar(50) NULL,
	label_projtext1 varchar(50) NULL,
	label_projtext2 varchar(50) NULL,
	label_projtext3 varchar(50) NULL,
	label_projtext4 varchar(50) NULL,
	label_projtext5 varchar(50) NULL,
	label_surfacewater varchar(50) NULL,
	label_surfacewaterpart varchar(50) NULL,
	label_surfacewaterkm varchar(50) NULL,
	label_sap_key varchar(50) NULL
);


-- gwbase.gwb_labors definition

-- Drop table

-- DROP TABLE gwbase.gwb_labors;

CREATE TABLE gwbase.gwb_labors (
	id int4 NOT NULL,
	labor varchar(100) NOT NULL,
	adresse1 varchar(50) NULL,
	adresse2 varchar(50) NULL,
	bemerkungen varchar(250) NULL,
	keynr varchar(10) NULL,
	relationfile varchar(255) NULL,
	CONSTRAINT gwb_labors_pk PRIMARY KEY (id)
);


-- gwbase.gwb_levelstatistic definition

-- Drop table

-- DROP TABLE gwbase.gwb_levelstatistic;

CREATE TABLE gwbase.gwb_levelstatistic (
	well_id int4 NOT NULL,
	statyear int4 NULL,
	maxlevel_sl float8 NULL,
	minlevel_sl float8 NULL,
	avlevel_sl float8 NULL
);


-- gwbase.gwb_logins definition

-- Drop table

-- DROP TABLE gwbase.gwb_logins;

CREATE TABLE gwbase.gwb_logins (
	user_id int4 NOT NULL,
	logintime timestamp NOT NULL,
	logofftime timestamp NULL,
	CONSTRAINT gwb_logins_pk PRIMARY KEY (user_id, logintime)
);


-- gwbase.gwb_maps definition

-- Drop table

-- DROP TABLE gwbase.gwb_maps;

CREATE TABLE gwbase.gwb_maps (
	mapfile varchar(255) NULL,
	mapcomment varchar(50) NOT NULL,
	xminmap float8 NULL,
	xmaxmap float8 NULL,
	yminmap float8 NULL,
	ymaxmap float8 NULL,
	contourmap bpchar(1) DEFAULT 'n'::bpchar NULL,
	selectmap bpchar(1) DEFAULT 'n'::bpchar NULL,
	digitizemap bpchar(1) DEFAULT 'n'::bpchar NULL,
	blankarea bpchar(1) DEFAULT 'n'::bpchar NULL,
	blitmode bpchar(1) DEFAULT 'd'::bpchar NULL,
	pixelsx int4 NULL,
	pixelsy int4 NULL,
	resolution int4 NULL,
	project_id int4 NULL,
	linecolor int4 NULL,
	linewidth int4 NULL,
	allwaysuse varchar(1) NULL,
	id int4 NULL,
	utm_east_minmap float8 NULL,
	utm_north_minmap float8 NULL,
	utm_east_maxmap float8 NULL,
	utm_north_maxmap float8 NULL,
	layerinfo text NULL,
	mapuseoption int4 NULL,
	CONSTRAINT gwb_maps_pk PRIMARY KEY (mapcomment)
);


-- gwbase.gwb_maps_projects definition

-- Drop table

-- DROP TABLE gwbase.gwb_maps_projects;

CREATE TABLE gwbase.gwb_maps_projects (
	map_id int4 NOT NULL,
	project_id int4 NOT NULL,
	CONSTRAINT gwb_maps_projects_pk PRIMARY KEY (map_id, project_id)
);


-- gwbase.gwb_msart definition

-- Drop table

-- DROP TABLE gwbase.gwb_msart;

CREATE TABLE gwbase.gwb_msart (
	msart varchar(50) NOT NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_msart_pk PRIMARY KEY (msart)
);


-- gwbase.gwb_nutzung definition

-- Drop table

-- DROP TABLE gwbase.gwb_nutzung;

CREATE TABLE gwbase.gwb_nutzung (
	nutzung varchar(50) NOT NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_nutzung_pk PRIMARY KEY (nutzung)
);


-- gwbase.gwb_objecttypes definition

-- Drop table

-- DROP TABLE gwbase.gwb_objecttypes;

CREATE TABLE gwbase.gwb_objecttypes (
	id int4 NOT NULL,
	description varchar(100) NOT NULL,
	CONSTRAINT pk_gwb_objecttypes PRIMARY KEY (id)
);


-- gwbase.gwb_organisation definition

-- Drop table

-- DROP TABLE gwbase.gwb_organisation;

CREATE TABLE gwbase.gwb_organisation (
	org_id int4 NOT NULL,
	org_no int4 NULL,
	org_info varchar(50) NULL,
	org_name1 varchar(50) NULL,
	org_name2 varchar(50) NULL,
	org_name3 varchar(50) NULL,
	org_name4 varchar(50) NULL,
	org_zip1 varchar(10) NULL,
	org_street varchar(50) NULL,
	org_zip2 varchar(10) NULL,
	org_city varchar(50) NULL,
	org_tel varchar(25) NULL,
	org_fax varchar(25) NULL,
	org_email varchar(50) NULL,
	datechange timestamp NULL,
	org_key varchar(20) NULL,
	org_pobox varchar(50) NULL,
	CONSTRAINT gwb_organisation_pk PRIMARY KEY (org_id)
);


-- gwbase.gwb_paramlists definition

-- Drop table

-- DROP TABLE gwbase.gwb_paramlists;

CREATE TABLE gwbase.gwb_paramlists (
	id int4 NOT NULL,
	listname varchar(100) NULL,
	paramids text NULL,
	CONSTRAINT gwb_paramlists_pk PRIMARY KEY (id)
);


-- gwbase.gwb_piconfig definition

-- Drop table

-- DROP TABLE gwbase.gwb_piconfig;

CREATE TABLE gwbase.gwb_piconfig (
	piid int4 NOT NULL,
	fieldname varchar(64) NOT NULL,
	vals varchar(255) NULL,
	vali int4 NULL,
	valf float8 NULL,
	valdt timestamp NULL,
	CONSTRAINT gwb_piconfig_pk PRIMARY KEY (piid, fieldname)
);


-- gwbase.gwb_pipematerialcat definition

-- Drop table

-- DROP TABLE gwbase.gwb_pipematerialcat;

CREATE TABLE gwbase.gwb_pipematerialcat (
	id int4 NOT NULL,
	itemname varchar(50) NOT NULL,
	CONSTRAINT gwb_pipematerialcat_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX gwb_pipematerialcat_idx1 ON gwbase.gwb_pipematerialcat USING btree (itemname);


-- gwbase.gwb_pipes definition

-- Drop table

-- DROP TABLE gwbase.gwb_pipes;

CREATE TABLE gwbase.gwb_pipes (
	id int4 NULL,
	pipename varchar(50) NULL
);
CREATE UNIQUE INDEX gwb_pipes_ix1 ON gwbase.gwb_pipes USING btree (pipename);


-- gwbase.gwb_pipetypecat definition

-- Drop table

-- DROP TABLE gwbase.gwb_pipetypecat;

CREATE TABLE gwbase.gwb_pipetypecat (
	id int4 NOT NULL,
	itemname varchar(50) NOT NULL,
	CONSTRAINT gwb_pipetypecat_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX gwb_pipetypecat_idx1 ON gwbase.gwb_pipetypecat USING btree (itemname);


-- gwbase.gwb_proggroups definition

-- Drop table

-- DROP TABLE gwbase.gwb_proggroups;

CREATE TABLE gwbase.gwb_proggroups (
	id int4 NOT NULL,
	groupname varchar(50) NULL,
	CONSTRAINT gwb_proggroups_pk PRIMARY KEY (id)
);


-- gwbase.gwb_projects definition

-- Drop table

-- DROP TABLE gwbase.gwb_projects;

CREATE TABLE gwbase.gwb_projects (
	id int4 NOT NULL,
	projectdescr varchar(50) NULL,
	projectcomment varchar(50) NULL,
	referenceno_1 varchar(50) NULL,
	referenceno_2 varchar(50) NULL,
	referenceno_3 varchar(50) NULL,
	referenceno_4 varchar(50) NULL,
	referenceno_5 varchar(50) NULL,
	text_1 varchar(100) NULL,
	logical_1 varchar(1) DEFAULT 'n'::character varying NULL,
	text_2 varchar(100) NULL,
	logical_2 varchar(1) DEFAULT 'n'::character varying NULL,
	text_3 varchar(100) NULL,
	logical_3 varchar(1) DEFAULT 'n'::character varying NULL,
	text_4 varchar(100) NULL,
	logical_4 varchar(1) DEFAULT 'n'::character varying NULL,
	text_5 varchar(100) NULL,
	logical_5 varchar(1) DEFAULT 'n'::character varying NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwtransferdate timestamp NULL,
	owner_id int4 NULL,
	epsg_code varchar(20) NULL,
	nad_grid varchar(200) NULL,
	group_id int4 NULL,
	isdefaultgroup int4 NULL,
	stationsunique int4 DEFAULT 0 NULL,
	CONSTRAINT gwb_projects_pk PRIMARY KEY (id)
);
CREATE INDEX gwb_projects_idx1 ON gwbase.gwb_projects USING btree (projectdescr);
CREATE UNIQUE INDEX gwb_projects_idx2 ON gwbase.gwb_projects USING btree (projectdescr, owner_id);


-- gwbase.gwb_projectsettings definition

-- Drop table

-- DROP TABLE gwbase.gwb_projectsettings;

CREATE TABLE gwbase.gwb_projectsettings (
	id int4 NULL,
	projectsettings text NULL,
	lastopendate timestamp NULL
);


-- gwbase.gwb_pumpen definition

-- Drop table

-- DROP TABLE gwbase.gwb_pumpen;

CREATE TABLE gwbase.gwb_pumpen (
	pumpentyp varchar(50) NOT NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_pumpen_pk PRIMARY KEY (pumpentyp)
);


-- gwbase.gwb_reportgroups definition

-- Drop table

-- DROP TABLE gwbase.gwb_reportgroups;

CREATE TABLE gwbase.gwb_reportgroups (
	id int4 NOT NULL,
	groupname varchar(100) NOT NULL,
	title1 varchar(50) NULL,
	title2 varchar(50) NULL,
	title3 varchar(50) NULL,
	title4 varchar(50) NULL,
	CONSTRAINT gwb_reportgroups_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX gwb_reportgroups_idx1 ON gwbase.gwb_reportgroups USING btree (groupname);


-- gwbase.gwb_samplecolor definition

-- Drop table

-- DROP TABLE gwbase.gwb_samplecolor;

CREATE TABLE gwbase.gwb_samplecolor (
	color varchar(20) NOT NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_samplecolor_pk PRIMARY KEY (color)
);


-- gwbase.gwb_samplematerial definition

-- Drop table

-- DROP TABLE gwbase.gwb_samplematerial;

CREATE TABLE gwbase.gwb_samplematerial (
	id int4 NOT NULL,
	shortname varchar(24) NULL,
	longname varchar(80) NULL,
	CONSTRAINT gwb_samplematerial_pk PRIMARY KEY (id)
);


-- gwbase.gwb_samplesediment definition

-- Drop table

-- DROP TABLE gwbase.gwb_samplesediment;

CREATE TABLE gwbase.gwb_samplesediment (
	sediment varchar(50) NOT NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_samplesediment_pk PRIMARY KEY (sediment)
);


-- gwbase.gwb_samplesmell definition

-- Drop table

-- DROP TABLE gwbase.gwb_samplesmell;

CREATE TABLE gwbase.gwb_samplesmell (
	smell varchar(50) NOT NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_samplesmell_pk PRIMARY KEY (smell)
);


-- gwbase.gwb_sampleturbidity definition

-- Drop table

-- DROP TABLE gwbase.gwb_sampleturbidity;

CREATE TABLE gwbase.gwb_sampleturbidity (
	turbidity varchar(50) NOT NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_sampleturbidity_pk PRIMARY KEY (turbidity)
);


-- gwbase.gwb_sampletype definition

-- Drop table

-- DROP TABLE gwbase.gwb_sampletype;

CREATE TABLE gwbase.gwb_sampletype (
	sampletype varchar(50) NOT NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_sampletype_pk PRIMARY KEY (sampletype)
);


-- gwbase.gwb_sampleweather definition

-- Drop table

-- DROP TABLE gwbase.gwb_sampleweather;

CREATE TABLE gwbase.gwb_sampleweather (
	weather varchar(50) NOT NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_sampleweather_pk PRIMARY KEY (weather)
);


-- gwbase.gwb_samplingreasons definition

-- Drop table

-- DROP TABLE gwbase.gwb_samplingreasons;

CREATE TABLE gwbase.gwb_samplingreasons (
	id int4 NOT NULL,
	reason varchar(50) NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_samplingreasons_pk PRIMARY KEY (id)
);


-- gwbase.gwb_selections definition

-- Drop table

-- DROP TABLE gwbase.gwb_selections;

CREATE TABLE gwbase.gwb_selections (
	id int4 NOT NULL,
	"owner" varchar(20) NULL,
	condname varchar(255) NULL,
	projcond varchar(255) NULL,
	wellcond text NULL,
	progcond varchar(255) NULL,
	CONSTRAINT gwb_selections_pk PRIMARY KEY (id)
);


-- gwbase.gwb_siteowners definition

-- Drop table

-- DROP TABLE gwbase.gwb_siteowners;

CREATE TABLE gwbase.gwb_siteowners (
	id int4 NOT NULL,
	"name" varchar(50) NULL,
	zip varchar(10) NULL,
	street varchar(50) NULL,
	town varchar(50) NULL,
	tel varchar(20) NULL,
	CONSTRAINT gwb_siteeowners_pk PRIMARY KEY (id)
);


-- gwbase.gwb_sqlrecherche definition

-- Drop table

-- DROP TABLE gwbase.gwb_sqlrecherche;

CREATE TABLE gwbase.gwb_sqlrecherche (
	id int4 NOT NULL,
	art varchar(1) NULL,
	bezeichnung varchar(64) NULL,
	beschreibung varchar(128) NULL,
	gruppe int4 NULL,
	kmsalle varchar(1) NULL,
	kmsteilgebiet int4 NULL,
	kmsroute int4 NULL,
	kmshwstockw int4 NULL,
	kmsgeoleinheit int4 NULL,
	kwswasserstandjn varchar(1) NULL,
	kwsperson int4 NULL,
	kwskennung varchar(1) NULL,
	kwsattribut int4 NULL,
	kwshinweisjn varchar(1) NULL,
	kwsmitohnewert varchar(1) NULL,
	kwsrelation varchar(1) NULL,
	kwswert float8 NULL,
	kwsbezug varchar(1) NULL,
	kwswsunterfilteruk varchar(1) NULL,
	kwsgelotetetiefe varchar(1) NULL,
	kpbprobejn varchar(1) NULL,
	kpberledigtjn varchar(1) NULL,
	kpbprobennehmer varchar(64) NULL,
	kpbuprogramm int4 NULL,
	kayanalysejn varchar(1) NULL,
	kaymitparam varchar(1) NULL,
	kayparamanzahl varchar(1) NULL,
	kaymitohnewert varchar(1) NULL,
	kayparameter int4 NULL,
	kayrelation varchar(1) NULL,
	kaywert float8 NULL,
	kaylabor int4 NULL,
	kdtdatumjn varchar(1) NULL,
	viscollage varchar(1) NULL,
	viscolanalymethod varchar(1) NULL,
	CONSTRAINT gwb_sqlrecherche_pk PRIMARY KEY (id)
);


-- gwbase.gwb_stationreports definition

-- Drop table

-- DROP TABLE gwbase.gwb_stationreports;

CREATE TABLE gwbase.gwb_stationreports (
	id int4 NOT NULL,
	description varchar(50) NULL,
	sortorder int4 NULL,
	parameter_id int4 NOT NULL,
	stn1_id int4 NULL,
	stn2_id int4 NULL,
	stn3_id int4 NULL,
	stn4_id int4 NULL,
	stn5_id int4 NULL,
	stn6_id int4 NULL,
	stn7_id int4 NULL,
	stn8_id int4 NULL,
	stn9_id int4 NULL,
	stn10_id int4 NULL,
	thresholdtext varchar(50) NULL,
	thresholdvalue float8 NULL,
	CONSTRAINT gwb_stationreports_pk PRIMARY KEY (id)
);


-- gwbase.gwb_storedrivernets definition

-- Drop table

-- DROP TABLE gwbase.gwb_storedrivernets;

CREATE TABLE gwbase.gwb_storedrivernets (
	id int4 NOT NULL,
	rivernetname varchar(50) NULL,
	CONSTRAINT gwb_srnet_pk PRIMARY KEY (id)
);


-- gwbase.gwb_streetcat definition

-- Drop table

-- DROP TABLE gwbase.gwb_streetcat;

CREATE TABLE gwbase.gwb_streetcat (
	streetkey varchar(20) NOT NULL,
	streetname varchar(100) NOT NULL,
	CONSTRAINT gwb_streetcat_pk PRIMARY KEY (streetkey)
);


-- gwbase.gwb_structures definition

-- Drop table

-- DROP TABLE gwbase.gwb_structures;

CREATE TABLE gwbase.gwb_structures (
	id int4 NOT NULL,
	structname varchar(50) NULL,
	structtype bpchar(1) NULL,
	priority int2 NULL,
	CONSTRAINT gwb_structures_pk PRIMARY KEY (id)
);


-- gwbase.gwb_surfacewaters definition

-- Drop table

-- DROP TABLE gwbase.gwb_surfacewaters;

CREATE TABLE gwbase.gwb_surfacewaters (
	id int4 NOT NULL,
	itemname varchar(50) NOT NULL,
	CONSTRAINT gwb_surfacewaters_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX gwb_surfacewaters_idx1 ON gwbase.gwb_surfacewaters USING btree (itemname);


-- gwbase.gwb_symbols definition

-- Drop table

-- DROP TABLE gwbase.gwb_symbols;

CREATE TABLE gwbase.gwb_symbols (
	symbolid int4 NOT NULL,
	symbolchar bpchar(1) NULL,
	symboltext varchar(50) NULL,
	CONSTRAINT gwb_symbols_pk PRIMARY KEY (symbolid)
);


-- gwbase.gwb_symbolscategories definition

-- Drop table

-- DROP TABLE gwbase.gwb_symbolscategories;

CREATE TABLE gwbase.gwb_symbolscategories (
	id int4 NOT NULL,
	field_name varchar(50) NOT NULL,
	category varchar(100) NULL,
	icon varchar(100) NULL,
	iconposition varchar(1) NULL,
	CONSTRAINT gwb_symbolscategories_pk PRIMARY KEY (id)
);


-- gwbase.gwb_taste definition

-- Drop table

-- DROP TABLE gwbase.gwb_taste;

CREATE TABLE gwbase.gwb_taste (
	id int4 NOT NULL,
	taste varchar(50) NULL,
	keynr varchar(10) NULL,
	CONSTRAINT gwb_taste_pk PRIMARY KEY (id)
);


-- gwbase.gwb_techequip_manufacturercat definition

-- Drop table

-- DROP TABLE gwbase.gwb_techequip_manufacturercat;

CREATE TABLE gwbase.gwb_techequip_manufacturercat (
	id int4 NOT NULL,
	manufacturer varchar(70) NOT NULL,
	CONSTRAINT gwb_techequip_manufacturercat_pk PRIMARY KEY (id)
);


-- gwbase.gwb_techequip_typecat definition

-- Drop table

-- DROP TABLE gwbase.gwb_techequip_typecat;

CREATE TABLE gwbase.gwb_techequip_typecat (
	id int4 NOT NULL,
	equiptype varchar(70) NOT NULL,
	CONSTRAINT gwb_techequip_typecat_pk PRIMARY KEY (id)
);


-- gwbase.gwb_techequipment definition

-- Drop table

-- DROP TABLE gwbase.gwb_techequipment;

CREATE TABLE gwbase.gwb_techequipment (
	id int4 NOT NULL,
	category bpchar(1) NULL,
	"type" varchar(50) NULL,
	person varchar(30) NULL,
	inventoryno varchar(50) NULL,
	material varchar(20) NULL,
	price float8 NULL,
	dateaquired timestamp NULL,
	manufacturer varchar(50) NULL,
	yearmade int2 NULL,
	remarks text NULL,
	datafile varchar(255) NULL,
	ratedflow float8 NULL,
	ratedflowunit varchar(10) NULL,
	ratedcurrent float8 NULL,
	ratedhead float8 NULL,
	ratedheadunit varchar(5) NULL,
	diameter float8 NULL,
	diameterunit varchar(5) NULL,
	length float8 NULL,
	lengthunit varchar(5) NULL,
	systemlength float8 NULL,
	systemlengthunit varchar(5) NULL,
	simcardno varchar(20) NULL,
	serialno varchar(50) NULL,
	inactive varchar(1) DEFAULT 'N'::character varying NULL,
	CONSTRAINT gwb_techequipment_pk PRIMARY KEY (id)
);


-- gwbase.gwb_techpartscat definition

-- Drop table

-- DROP TABLE gwbase.gwb_techpartscat;

CREATE TABLE gwbase.gwb_techpartscat (
	id int4 NOT NULL,
	class_id int4 NOT NULL,
	itemname varchar(50) NOT NULL,
	CONSTRAINT gwb_techpartscat_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX gwb_techpartscat_idx1 ON gwbase.gwb_techpartscat USING btree (itemname);


-- gwbase.gwb_techpartsclasscat definition

-- Drop table

-- DROP TABLE gwbase.gwb_techpartsclasscat;

CREATE TABLE gwbase.gwb_techpartsclasscat (
	id int4 NOT NULL,
	itemname varchar(50) NOT NULL,
	CONSTRAINT gwb_techpartsclasscat_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX gwb_techpartsclasscat_idx1 ON gwbase.gwb_techpartsclasscat USING btree (itemname);


-- gwbase.gwb_todocategories definition

-- Drop table

-- DROP TABLE gwbase.gwb_todocategories;

CREATE TABLE gwbase.gwb_todocategories (
	id int4 NOT NULL,
	categoryname varchar(50) NULL,
	sap_key varchar(50) NULL,
	repeatmonths int4 NULL,
	CONSTRAINT gwb_todocategories_pk PRIMARY KEY (id)
);


-- gwbase.gwb_topograf definition

-- Drop table

-- DROP TABLE gwbase.gwb_topograf;

CREATE TABLE gwbase.gwb_topograf (
	project_id int4 NOT NULL,
	rechtswert float8 NOT NULL,
	hochwert float8 NOT NULL,
	hoehenn float8 NULL,
	CONSTRAINT gwb_topograf_pk PRIMARY KEY (project_id, rechtswert, hochwert)
);


-- gwbase.gwb_ukat definition

-- Drop table

-- DROP TABLE gwbase.gwb_ukat;

CREATE TABLE gwbase.gwb_ukat (
	katalognr int4 NOT NULL,
	katalogbezeichnung varchar(50) NULL,
	katalogtype int4 NULL,
	seqnr int4 NULL,
	lab_id int4 NULL,
	active varchar(1) NULL,
	CONSTRAINT gwb_ukat_pk PRIMARY KEY (katalognr)
);


-- gwbase.gwb_umrech definition

-- Drop table

-- DROP TABLE gwbase.gwb_umrech;

CREATE TABLE gwbase.gwb_umrech (
	einheit1 varchar(10) NOT NULL,
	einheit2 varchar(10) NOT NULL,
	faktor float8 NULL,
	CONSTRAINT gwb_umrech_pk PRIMARY KEY (einheit1, einheit2)
);


-- gwbase.gwb_users definition

-- Drop table

-- DROP TABLE gwbase.gwb_users;

CREATE TABLE gwbase.gwb_users (
	us_loginname varchar(20) NOT NULL,
	us_username varchar(30) NULL,
	us_password varchar(10) NULL,
	us_isadmin bpchar(1) DEFAULT 'n'::bpchar NULL,
	us_allowlookuplists bpchar(1) DEFAULT 'y'::bpchar NULL,
	us_allowwarnlist bpchar(1) DEFAULT 'y'::bpchar NULL,
	us_privatemaps bpchar(1) DEFAULT 'y'::bpchar NULL,
	us_lastuser bpchar(1) NULL,
	us_tablelayoutchange varchar(1) NULL,
	id int4 NULL,
	us_loggerdata varchar(1) NULL,
	u_priv_wells int4 NULL,
	u_priv_level int4 NULL,
	u_priv_analysis int4 NULL,
	u_priv_discharge int4 NULL,
	u_priv_rainfall int4 NULL,
	us_privatesubprojects varchar(1) NULL,
	us_winusername varchar(255) NULL,
	templatedirectory varchar(255) NULL,
	us_passwordhash varchar(255) NULL,
	us_allprojects varchar(1) NULL,
	us_samplestatechange varchar(1) NULL,
	CONSTRAINT gwb_users_pk PRIMARY KEY (us_loginname)
);


-- gwbase.gwb_userselection definition

-- Drop table

-- DROP TABLE gwbase.gwb_userselection;

CREATE TABLE gwbase.gwb_userselection (
	user_id int4 NOT NULL,
	well_id int4 NOT NULL,
	list_type int4 NULL
);


-- gwbase.gwb_valuemarkers definition

-- Drop table

-- DROP TABLE gwbase.gwb_valuemarkers;

CREATE TABLE gwbase.gwb_valuemarkers (
	marker varchar(1) NOT NULL,
	markertext varchar(50) NULL,
	color int4 NULL,
	id serial4 NOT NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwtransferdate timestamp NULL,
	hasflag bpchar(1) DEFAULT '-'::bpchar NULL,
	flagdefinition text NULL,
	flagpreview bytea NULL,
	CONSTRAINT gwb_valuemarkers_pk PRIMARY KEY (marker)
);


-- gwbase.gwb_valvetypecat definition

-- Drop table

-- DROP TABLE gwbase.gwb_valvetypecat;

CREATE TABLE gwbase.gwb_valvetypecat (
	id int4 NOT NULL,
	itemname varchar(50) NOT NULL,
	CONSTRAINT gwb_valvetypecat_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX gwb_valvetypecat_idx1 ON gwbase.gwb_valvetypecat USING btree (itemname);


-- gwbase.gwb_version definition

-- Drop table

-- DROP TABLE gwbase.gwb_version;

CREATE TABLE gwbase.gwb_version (
	"version" varchar(10) NULL,
	version7 varchar(10) NULL,
	version8 varchar(10) NULL,
	backupdate timestamp NULL,
	version9 varchar(10) NULL
);


-- gwbase.gwb_wasserrecht definition

-- Drop table

-- DROP TABLE gwbase.gwb_wasserrecht;

CREATE TABLE gwbase.gwb_wasserrecht (
	id int4 NOT NULL,
	zuord int2 NULL,
	zuord_id int4 NULL,
	e_a int4 NULL,
	e_m int4 NULL,
	e_dm int4 NULL,
	e_dd int4 NULL,
	e_h int4 NULL,
	prob_int varchar(35) NULL,
	art int2 NULL,
	anfang timestamp NULL,
	ende timestamp NULL,
	az1 varchar(25) NULL,
	az2 varchar(25) NULL,
	bemerk text NULL,
	neben text NULL,
	zweck varchar(30) NULL,
	dat_l_ae timestamp NULL,
	CONSTRAINT gwb_wasserrecht_pk PRIMARY KEY (id)
);


-- gwbase.gwb_watertypecat definition

-- Drop table

-- DROP TABLE gwbase.gwb_watertypecat;

CREATE TABLE gwbase.gwb_watertypecat (
	id int4 NOT NULL,
	itemname varchar(50) NOT NULL,
	CONSTRAINT gwb_watertypecat_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX gwb_watertypecat_idx1 ON gwbase.gwb_watertypecat USING btree (itemname);


-- gwbase.gwb_wellproperties definition

-- Drop table

-- DROP TABLE gwbase.gwb_wellproperties;

CREATE TABLE gwbase.gwb_wellproperties (
	id int4 NOT NULL,
	property_name varchar(50) NOT NULL,
	property_key varchar(20) NULL,
	usecatalog int4 NULL,
	CONSTRAINT gwb_wellproperties_pk PRIMARY KEY (id)
);


-- gwbase.gwb_wellproperties_wells definition

-- Drop table

-- DROP TABLE gwbase.gwb_wellproperties_wells;

CREATE TABLE gwbase.gwb_wellproperties_wells (
	property_id int4 NOT NULL,
	well_id int4 NOT NULL,
	propertytext varchar(255) NULL,
	propertydate timestamp NULL,
	CONSTRAINT gwb_wellprops__wells_pk PRIMARY KEY (property_id, well_id)
);


-- gwbase.gwb_wellpumprates definition

-- Drop table

-- DROP TABLE gwbase.gwb_wellpumprates;

CREATE TABLE gwbase.gwb_wellpumprates (
	id int4 NOT NULL,
	well_id int4 NOT NULL,
	startdate timestamp NULL,
	enddate timestamp NULL,
	pumprate float8 NULL,
	pumpunit varchar(1) NULL,
	CONSTRAINT gwb_wellpumprates_pk PRIMARY KEY (id)
);


-- gwbase.gwb_wellstatuscat definition

-- Drop table

-- DROP TABLE gwbase.gwb_wellstatuscat;

CREATE TABLE gwbase.gwb_wellstatuscat (
	id int4 NOT NULL,
	statusname varchar(50) NULL,
	CONSTRAINT gwb_wellstatuscat_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX gwb_wellstatuscat_ix1 ON gwbase.gwb_wellstatuscat USING btree (statusname);


-- gwbase.gwb_wellstdtodocategories definition

-- Drop table

-- DROP TABLE gwbase.gwb_wellstdtodocategories;

CREATE TABLE gwbase.gwb_wellstdtodocategories (
	well_id int4 NOT NULL,
	category_id int4 NOT NULL,
	repeatmonths int4 NULL,
	CONSTRAINT gwb_wellstdtodocategories_pk PRIMARY KEY (well_id, category_id)
);


-- gwbase.gwb_welltodolistsap_err definition

-- Drop table

-- DROP TABLE gwbase.gwb_welltodolistsap_err;

CREATE TABLE gwbase.gwb_welltodolistsap_err (
	gwb_id int4 NOT NULL,
	"type" varchar(1) NOT NULL,
	id varchar(20) NULL,
	"number" varchar(3) NULL,
	message varchar(220) NULL,
	log_no varchar(20) NULL,
	log_msg_no varchar(6) NULL,
	message_v1 varchar(50) NULL,
	message_v2 varchar(50) NULL,
	message_v3 varchar(50) NULL,
	message_v4 varchar(50) NULL,
	"parameter" varchar(32) NULL,
	"row" int4 NULL,
	field varchar(30) NULL,
	"system" varchar(10) NULL,
	message_date timestamp DEFAULT getdate() NULL
);


-- gwbase.gwc_wellaquifers definition

-- Drop table

-- DROP TABLE gwbase.gwc_wellaquifers;

CREATE TABLE gwbase.gwc_wellaquifers (
	id int4 NOT NULL,
	orgid int4 NULL,
	"name" varchar(50) NOT NULL,
	seqnr int4 DEFAULT 32000 NULL,
	symbolfilename varchar(32) NULL,
	symboldefinition varchar(128) NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	CONSTRAINT pk_gwc_wellaquifers PRIMARY KEY (id)
);


-- gwbase.gwc_wellcategories definition

-- Drop table

-- DROP TABLE gwbase.gwc_wellcategories;

CREATE TABLE gwbase.gwc_wellcategories (
	type_id int4 NOT NULL,
	category_id int4 NOT NULL,
	"name" varchar(128) NOT NULL,
	seqnr int4 DEFAULT 32000 NULL,
	symbolfilename varchar(32) NULL,
	symboldefinition varchar(128) NULL,
	symbolwidth int4 NULL,
	symbolheight int4 NULL,
	symbolanchorx int4 DEFAULT 0 NULL,
	symbolanchory int4 DEFAULT 0 NULL,
	symbolhotspot bpchar(2) DEFAULT 'cc'::bpchar NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	CONSTRAINT pk_gwc_wellcategories PRIMARY KEY (type_id, category_id)
);


-- gwbase.gwc_wellcategory_types definition

-- Drop table

-- DROP TABLE gwbase.gwc_wellcategory_types;

CREATE TABLE gwbase.gwc_wellcategory_types (
	id int4 NOT NULL,
	tablename varchar(30) NULL,
	"name" varchar(50) NOT NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwtransferdate timestamp NULL,
	CONSTRAINT pk_gwc_wellcategory_types PRIMARY KEY (id)
);


-- gwbase.gwc_wellgeology definition

-- Drop table

-- DROP TABLE gwbase.gwc_wellgeology;

CREATE TABLE gwbase.gwc_wellgeology (
	id int4 NOT NULL,
	orgid int4 NULL,
	"name" varchar(50) NOT NULL,
	seqnr int4 DEFAULT 32000 NULL,
	symbolfilename varchar(32) NULL,
	symboldefinition varchar(128) NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	CONSTRAINT pk_gwc_wellgeology PRIMARY KEY (id)
);


-- gwbase.gwc_wellgroundowner definition

-- Drop table

-- DROP TABLE gwbase.gwc_wellgroundowner;

CREATE TABLE gwbase.gwc_wellgroundowner (
	id int4 NOT NULL,
	"name" varchar(50) NOT NULL,
	seqnr int4 DEFAULT 32000 NULL,
	symbolfilename varchar(32) NULL,
	symboldefinition varchar(128) NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	CONSTRAINT pk_gwc_wellgroundowner PRIMARY KEY (id)
);


-- gwbase.gwc_wellkind definition

-- Drop table

-- DROP TABLE gwbase.gwc_wellkind;

CREATE TABLE gwbase.gwc_wellkind (
	id int4 NOT NULL,
	"name" varchar(50) NOT NULL,
	seqnr int4 DEFAULT 32000 NULL,
	symbolfilename varchar(32) NULL,
	symboldefinition varchar(128) NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	CONSTRAINT pk_gwc_wellkind PRIMARY KEY (id)
);


-- gwbase.gwc_welllocation definition

-- Drop table

-- DROP TABLE gwbase.gwc_welllocation;

CREATE TABLE gwbase.gwc_welllocation (
	id int4 NOT NULL,
	"name" varchar(50) NOT NULL,
	seqnr int4 DEFAULT 32000 NULL,
	symbolfilename varchar(32) NULL,
	symboldefinition varchar(128) NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	CONSTRAINT pk_gwc_welllocation PRIMARY KEY (id)
);


-- gwbase.gwc_welllocationfl definition

-- Drop table

-- DROP TABLE gwbase.gwc_welllocationfl;

CREATE TABLE gwbase.gwc_welllocationfl (
	id int4 NOT NULL,
	"name" varchar(50) NOT NULL,
	seqnr int4 DEFAULT 32000 NULL,
	symbolfilename varchar(32) NULL,
	symboldefinition varchar(128) NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	CONSTRAINT pk_gwc_welllocationfl PRIMARY KEY (id)
);


-- gwbase.gwc_welllocationfs definition

-- Drop table

-- DROP TABLE gwbase.gwc_welllocationfs;

CREATE TABLE gwbase.gwc_welllocationfs (
	id int4 NOT NULL,
	"name" varchar(50) NOT NULL,
	seqnr int4 DEFAULT 32000 NULL,
	symbolfilename varchar(32) NULL,
	symboldefinition varchar(128) NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	CONSTRAINT pk_gwc_welllocationfs PRIMARY KEY (id)
);


-- gwbase.gwc_welllocationge definition

-- Drop table

-- DROP TABLE gwbase.gwc_welllocationge;

CREATE TABLE gwbase.gwc_welllocationge (
	id int4 NOT NULL,
	"name" varchar(50) NOT NULL,
	seqnr int4 DEFAULT 32000 NULL,
	symbolfilename varchar(32) NULL,
	symboldefinition varchar(128) NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	CONSTRAINT pk_gwc_welllocationge PRIMARY KEY (id)
);


-- gwbase.gwc_welloperator definition

-- Drop table

-- DROP TABLE gwbase.gwc_welloperator;

CREATE TABLE gwbase.gwc_welloperator (
	id int4 NOT NULL,
	"name" varchar(50) NOT NULL,
	seqnr int4 DEFAULT 32000 NULL,
	symbolfilename varchar(32) NULL,
	symboldefinition varchar(128) NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	CONSTRAINT pk_gwc_welloperator PRIMARY KEY (id)
);


-- gwbase.gwc_wellsite definition

-- Drop table

-- DROP TABLE gwbase.gwc_wellsite;

CREATE TABLE gwbase.gwc_wellsite (
	id int4 NOT NULL,
	"name" varchar(50) NOT NULL,
	seqnr int4 DEFAULT 32000 NULL,
	symbolfilename varchar(32) NULL,
	symboldefinition varchar(128) NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	CONSTRAINT pk_gwc_wellsite PRIMARY KEY (id)
);


-- gwbase.gwc_welluse definition

-- Drop table

-- DROP TABLE gwbase.gwc_welluse;

CREATE TABLE gwbase.gwc_welluse (
	id int4 NOT NULL,
	"name" varchar(50) NOT NULL,
	seqnr int4 DEFAULT 32000 NULL,
	symbolfilename varchar(32) NULL,
	symboldefinition varchar(128) NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	CONSTRAINT pk_gwc_welluse PRIMARY KEY (id)
);


-- gwbase.gwm_persons definition

-- Drop table

-- DROP TABLE gwbase.gwm_persons;

CREATE TABLE gwbase.gwm_persons (
	person_id int4 NOT NULL,
	person_name varchar(20) NULL,
	person_pin varchar(4) NULL,
	allowseelastvalue bpchar(1) NULL,
	allowmodifywelldata bpchar(1) NULL,
	persontype varchar(1) NULL,
	sap_key varchar(50) NULL,
	CONSTRAINT gwb_persons_pk PRIMARY KEY (person_id)
);


-- gwbase.gwm_picturecategoriescat definition

-- Drop table

-- DROP TABLE gwbase.gwm_picturecategoriescat;

CREATE TABLE gwbase.gwm_picturecategoriescat (
	id int4 NOT NULL,
	picturecategory varchar(50) NOT NULL,
	CONSTRAINT gwm_picturecategoriescat_pk PRIMARY KEY (id)
);


-- gwbase.gwm_routes definition

-- Drop table

-- DROP TABLE gwbase.gwm_routes;

CREATE TABLE gwbase.gwm_routes (
	route_id int4 NOT NULL,
	route_name varchar(50) NULL,
	route_type varchar(1) NULL,
	person1_id int4 NULL,
	person2_id int4 NULL,
	CONSTRAINT gwb_routes_pk PRIMARY KEY (route_id)
);


-- gwbase.gww_datastatus definition

-- Drop table

-- DROP TABLE gwbase.gww_datastatus;

CREATE TABLE gwbase.gww_datastatus (
	msid int4 NOT NULL,
	"datatype" int4 NOT NULL,
	paramid int4 NOT NULL,
	count_ int4 NULL,
	mindate timestamp NULL,
	maxdate timestamp NULL,
	CONSTRAINT pk_gww_datastatus PRIMARY KEY (msid, datatype, paramid)
);


-- gwbase.gww_layers definition

-- Drop table

-- DROP TABLE gwbase.gww_layers;

CREATE TABLE gwbase.gww_layers (
	id int4 NOT NULL,
	filename varchar(255) NOT NULL,
	directory varchar(255) NOT NULL,
	layerclass int4 NULL,
	geotype varchar(20) NULL,
	displayname varchar(128) NULL,
	description text NULL,
	definition text NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	sortno int4 DEFAULT 32000 NULL,
	CONSTRAINT pk_gww_layers PRIMARY KEY (id)
);


-- gwbase.gww_maps definition

-- Drop table

-- DROP TABLE gwbase.gww_maps;

CREATE TABLE gwbase.gww_maps (
	id int4 NOT NULL,
	displayname varchar(128) NULL,
	"name" varchar(128) NULL,
	title varchar(128) NULL,
	url text NULL,
	providername varchar(128) NULL,
	providerurl text NULL,
	sourcename varchar(128) NULL,
	sourceurl text NULL,
	crs varchar(16) NULL,
	crsname varchar(64) NULL,
	availablecrs text NULL,
	abstract text NULL,
	servicetype varchar(32) NULL,
	serviceversion varchar(16) NULL,
	jslayerdef text NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwtransferdate timestamp NULL,
	sortno int4 DEFAULT 32000 NULL,
	isoverlay varchar(1) DEFAULT '-'::character varying NULL,
	CONSTRAINT pk_gww_maps PRIMARY KEY (id)
);


-- gwbase.gww_mapservices definition

-- Drop table

-- DROP TABLE gwbase.gww_mapservices;

CREATE TABLE gwbase.gww_mapservices (
	id int4 NOT NULL,
	"name" varchar(128) NULL,
	title varchar(128) NULL,
	servicetype varchar(32) NULL,
	serviceversion varchar(16) NULL,
	displaytitle varchar(128) NULL,
	abstract text NULL,
	url text NULL,
	providername varchar(128) NULL,
	providerurl text NULL,
	sourcename varchar(128) NULL,
	sourceurl text NULL,
	fees text NULL,
	accessconstraints text NULL,
	keywords text NULL,
	contact text NULL,
	attribution text NULL,
	authorityurlname varchar(64) NULL,
	authorityurlhref text NULL,
	identifierauthority varchar(64) NULL,
	identifierurl text NULL,
	username varchar(64) NULL,
	userpassword varchar(64) NULL,
	sortno int4 DEFAULT 32000 NULL,
	CONSTRAINT pk_gww_mapservices PRIMARY KEY (id)
);


-- gwbase.gww_profiles definition

-- Drop table

-- DROP TABLE gwbase.gww_profiles;

CREATE TABLE gwbase.gww_profiles (
	id int4 NOT NULL,
	filename varchar(255) NOT NULL,
	directory varchar(255) NOT NULL,
	"type" int4 NULL,
	displayname varchar(128) NULL,
	description text NULL,
	definition text NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	sortno int4 DEFAULT 32000 NULL,
	CONSTRAINT pk_gww_profiles PRIMARY KEY (id)
);


-- gwbase.gww_webconfig definition

-- Drop table

-- DROP TABLE gwbase.gww_webconfig;

CREATE TABLE gwbase.gww_webconfig (
	id int4 NOT NULL,
	webarea_id int4 NULL,
	weborg_id int4 NULL,
	weborg_krz varchar(20) NULL,
	username varchar(50) NULL,
	userpassword varchar(50) NULL,
	webdomain varchar(50) NULL,
	"path" varchar(50) NULL,
	requestscript varchar(50) NULL,
	port int4 NULL,
	ssl bpchar(1) DEFAULT '-'::bpchar NULL,
	connecttimeout int4 DEFAULT 5 NULL,
	readtimeout int4 DEFAULT 20 NULL,
	http_paramsok bpchar(1) DEFAULT '-'::bpchar NULL,
	ftp_user varchar(50) NULL,
	ftp_password varchar(50) NULL,
	ftp_server varchar(50) NULL,
	ftp_path varchar(50) NULL,
	ftp_port int4 NULL,
	ftp_ssl bpchar(1) DEFAULT '-'::bpchar NULL,
	ftp_passivemode bpchar(1) DEFAULT '+'::bpchar NULL,
	ftp_connecttimeout int4 DEFAULT 6 NULL,
	ftp_readtimeout int4 DEFAULT 60 NULL,
	ftp_paramsok bpchar(1) DEFAULT '-'::bpchar NULL,
	proxy_user varchar(50) NULL,
	proxy_password varchar(50) NULL,
	proxy_server varchar(50) NULL,
	proxy_port int4 NULL,
	ftpproxy_user varchar(50) NULL,
	ftpproxy_password varchar(50) NULL,
	ftpproxy_server varchar(50) NULL,
	ftpproxy_port int4 NULL,
	usagefotos bpchar(1) DEFAULT '+'::bpchar NULL,
	usagewelldocuments bpchar(1) DEFAULT '+'::bpchar NULL,
	usagelevels int4 DEFAULT 2 NULL,
	usageananlyses int4 DEFAULT 2 NULL,
	usagelogger int4 DEFAULT 2 NULL,
	usagerates int4 DEFAULT 2 NULL,
	usageprecipitate int4 DEFAULT 2 NULL,
	usageclimate int4 DEFAULT 2 NULL,
	usageanamediumwater bpchar(1) DEFAULT '+'::bpchar NULL,
	usageanamediumsolid bpchar(1) DEFAULT '-'::bpchar NULL,
	usageanamediumeluat bpchar(1) DEFAULT '-'::bpchar NULL,
	usageanamediumair bpchar(1) DEFAULT '-'::bpchar NULL,
	usagecategories bpchar(1) DEFAULT '+'::bpchar NULL,
	usagesubprojects bpchar(1) DEFAULT '+'::bpchar NULL,
	usagesynonymes bpchar(1) DEFAULT '+'::bpchar NULL,
	usagelayers bpchar(1) DEFAULT '+'::bpchar NULL,
	usageprofiles bpchar(1) DEFAULT '+'::bpchar NULL,
	usageprojectdocument bpchar(1) DEFAULT '+'::bpchar NULL,
	gwbepsg varchar(16) NULL,
	gwbepsgname varchar(128) NULL,
	gwbutmepsg varchar(16) NULL,
	gwbutmepsgname varchar(128) NULL,
	gwwpreferedepsgtyp int4 NULL,
	gwwpreferedepsg varchar(16) NULL,
	gwwpreferedepsgname varchar(128) NULL,
	provevaluelistno int4 NULL,
	warningvaluelistno int4 NULL,
	logofile varchar(255) NULL,
	ftpproxy_http bpchar(1) DEFAULT '-'::bpchar NULL,
	ftpproxy_httpauthmethod varchar(20) NULL,
	preferedmapid int4 NULL,
	loginlogofile varchar(255) NULL,
	logoposition varchar(32) DEFAULT 'legbottom'::character varying NULL,
	usagevaluemarkers bpchar(1) DEFAULT '-'::bpchar NULL,
	usagelevelcomments bpchar(1) DEFAULT '-'::bpchar NULL,
	logoimg bytea NULL,
	loginlogoimg bytea NULL,
	levelcommentshasflag bpchar(1) DEFAULT '-'::bpchar NULL,
	levelcommentsflagdefinition text NULL,
	levelcommentsflagpreview bytea NULL,
	levelbglnegative bpchar(1) DEFAULT '-'::bpchar NULL,
	CONSTRAINT pk_gww_webconfig PRIMARY KEY (id)
);


-- gwbase.gww_welldatacounts definition

-- Drop table

-- DROP TABLE gwbase.gww_welldatacounts;

CREATE TABLE gwbase.gww_welldatacounts (
	msid int4 NOT NULL,
	countlv int4 NULL,
	countan int4 NULL,
	countlg int4 NULL,
	countrt int4 NULL,
	countrn int4 NULL,
	countcl int4 NULL,
	countdo int4 NULL,
	CONSTRAINT pk_gww_welldatacounts PRIMARY KEY (msid)
);


-- gwbase.repitems definition

-- Drop table

-- DROP TABLE gwbase.repitems;

CREATE TABLE gwbase.repitems (
	id int4 NOT NULL,
	reporttype int4 NOT NULL,
	"name" varchar(50) NOT NULL,
	"size" int4 NULL,
	"template" bytea NULL,
	project varchar(50) NULL,
	CONSTRAINT repitems_pk PRIMARY KEY (id)
);


-- gwbase.rst_appconfig definition

-- Drop table

-- DROP TABLE gwbase.rst_appconfig;

CREATE TABLE gwbase.rst_appconfig (
	mandant_id int4 NOT NULL,
	module_id varchar(16) NOT NULL,
	key_ varchar(100) NOT NULL,
	value_i int4 NULL,
	value_f float8 NULL,
	value_s text NULL,
	value_dt date NULL,
	CONSTRAINT pk_rst_appconfig PRIMARY KEY (mandant_id, module_id, key_)
);


-- gwbase.rst_dbversion definition

-- Drop table

-- DROP TABLE gwbase.rst_dbversion;

CREATE TABLE gwbase.rst_dbversion (
	mandant_id int4 NOT NULL,
	module_id varchar(32) NOT NULL,
	"version" int4 NULL,
	CONSTRAINT pk_rst_dbversion PRIMARY KEY (mandant_id, module_id)
);


-- gwbase.gwb_levelcomments definition

-- Drop table

-- DROP TABLE gwbase.gwb_levelcomments;

CREATE TABLE gwbase.gwb_levelcomments (
	commentid int4 NOT NULL,
	commenttext varchar(50) NULL,
	keynr varchar(10) NULL,
	quality bpchar(1) DEFAULT '+'::bpchar NULL,
	todocategory_id int4 NULL,
	CONSTRAINT gwb_levelcomments_pk PRIMARY KEY (commentid),
	CONSTRAINT gwb_levelcomm_todocat_fk FOREIGN KEY (todocategory_id) REFERENCES gwbase.gwb_todocategories(id)
);


-- gwbase.gwb_msstamm definition

-- Drop table

-- DROP TABLE gwbase.gwb_msstamm;

CREATE TABLE gwbase.gwb_msstamm (
	id int4 NOT NULL,
	kennnr varchar(30) NULL,
	kennnr2 varchar(20) NULL,
	bezeichnung varchar(50) NULL,
	rechtswert float8 NULL,
	hochwert float8 NULL,
	xlabel float8 NULL,
	ylabel float8 NULL,
	hoehemp float8 NULL,
	hoeheokg float8 NULL,
	tk25 varchar(20) NULL,
	betreiber varchar(50) NULL,
	ort varchar(50) NULL,
	gemarkung varchar(50) NULL,
	flur varchar(20) NULL,
	flurstueck varchar(20) NULL,
	grundeigner varchar(50) NULL,
	lage varchar(100) NULL,
	baujahr bpchar(4) NULL,
	beseitigt bpchar(4) NULL,
	nachfolger varchar(20) NULL,
	vorgaenger varchar(20) NULL,
	art varchar(50) NULL,
	nutzung varchar(50) NULL,
	kat1 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat2 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat3 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat4 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat5 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat6 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat7 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat8 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat9 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat10 bpchar(1) DEFAULT 'n'::bpchar NULL,
	lageplandatei varchar(200) NULL,
	bilddatei varchar(200) NULL,
	bohrfirma varchar(50) NULL,
	bohrdurchmesser varchar(50) NULL,
	bohrverfahren varchar(50) NULL,
	bohrtiefe varchar(10) NULL,
	ausbaumaterial varchar(50) NULL,
	ausbaudurchm varchar(50) NULL,
	ausbautiefe varchar(10) NULL,
	filterstrecken varchar(250) NULL,
	filtertop float8 NULL,
	filterbottom float8 NULL,
	schlitzweite varchar(10) NULL,
	filterkies varchar(20) NULL,
	tonsperren varchar(50) NULL,
	ausbaudatei varchar(200) NULL,
	geology_id int4 NULL,
	gesteinsart varchar(50) NULL,
	kwert varchar(10) NULL,
	aquifer_id int4 NULL,
	aquifertop float8 NULL,
	aquiferbase float8 NULL,
	gwgespannt bpchar(1) DEFAULT 'n'::bpchar NULL,
	schichtendatei varchar(200) NULL,
	messintervallws int4 NULL,
	letztemessung timestamp NULL,
	beprobintervall int4 NULL,
	letztebeprobung timestamp NULL,
	wplan bpchar(1) DEFAULT 'y'::bpchar NULL,
	aplan bpchar(1) DEFAULT 'y'::bpchar NULL,
	uprogrammnr int4 NULL,
	statistikanfang timestamp NULL,
	statistikende timestamp NULL,
	nnmittel float8 NULL,
	nnmax float8 NULL,
	nnmin float8 NULL,
	flurabstmittel float8 NULL,
	flurabstmax float8 NULL,
	flurabstmin float8 NULL,
	datummin timestamp NULL,
	datummax timestamp NULL,
	symbol int4 NULL,
	qeinheit bpchar(1) NULL,
	qart bpchar(1) NULL,
	messpunkt varchar(25) NULL,
	toleranz float8 NULL,
	extprog1name varchar(20) NULL,
	extprog1fileext bpchar(3) NULL,
	extprog1path varchar(100) NULL,
	extprog1file varchar(200) NULL,
	extprog2name varchar(20) NULL,
	extprog2fileext bpchar(3) NULL,
	extprog2path varchar(100) NULL,
	extprog2file varchar(200) NULL,
	messpunktbilddatei varchar(200) NULL,
	barcode varchar(20) NULL,
	entnahmeort bpchar(1) NULL,
	rltg_l int2 NULL,
	rltg_dm int2 NULL,
	aufsatz_dm int2 NULL,
	gla_nr varchar(30) NULL,
	datechange timestamp NULL,
	anzahlfilter int2 NULL,
	filterlaenge float8 NULL,
	fassung_id int4 NULL,
	p_rep_id int4 NULL,
	negatelevels varchar(1) NULL,
	depthreference varchar(1) NULL,
	cnt_levels int4 NULL,
	cnt_samples int4 NULL,
	cnt_rates int4 NULL,
	cnt_files int4 NULL,
	utm_east float8 NULL,
	utm_north float8 NULL,
	visibletabs varchar(40) NULL,
	web_wellcat varchar(2) NULL,
	web_levelcat varchar(2) NULL,
	web_analycat varchar(2) NULL,
	web_ratecat varchar(2) NULL,
	web_levelstatcat varchar(2) NULL,
	web_analystatcat varchar(2) NULL,
	web_ratestatcat varchar(2) NULL,
	levelupperbound float8 NULL,
	levellowerbound float8 NULL,
	filterbottom_sl float8 NULL,
	filtertop_sl float8 NULL,
	absmaxlevel_sl float8 NULL,
	avmaxlevel_sl float8 NULL,
	absminlevel_sl float8 NULL,
	avminlevel_sl float8 NULL,
	avmeanlevel_sl float8 NULL,
	seqno int4 NULL,
	datebuild timestamp NULL,
	datedestroyed timestamp NULL,
	drillelevation float8 NULL,
	samplingdepth float8 NULL,
	status_id int4 NULL,
	standardsamplingtype varchar(50) NULL,
	stationtype int4 DEFAULT 1 NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwtransferdate timestamp NULL,
	gwwlevels bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwrates bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwanalyses bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwlogger bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwrain bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwclimate bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwfiledate timestamp NULL,
	sap_key varchar(50) NULL,
	surfacewater_id int4 NULL,
	surfacewaterpart_id int4 NULL,
	km float8 NULL,
	surfacewaterdistance float8 NULL,
	catchmentarea_id int4 NULL,
	aquiferthickness varchar(20) NULL,
	filterlength float8 NULL,
	streetkey varchar(20) NULL,
	houseno varchar(10) NULL,
	modifydate timestamp NULL,
	groupno varchar(50) NULL,
	drilleddepth float8 NULL,
	sumptraplength float8 NULL,
	CONSTRAINT gwb_msstamm_pk PRIMARY KEY (id),
	CONSTRAINT gwb_msstamm_status_fk FOREIGN KEY (status_id) REFERENCES gwbase.gwb_wellstatuscat(id),
	CONSTRAINT gwb_wells_aquifer_fk FOREIGN KEY (aquifer_id) REFERENCES gwbase.gwb_gwstockw(id),
	CONSTRAINT gwb_wells_geology_fk FOREIGN KEY (geology_id) REFERENCES gwbase.gwb_geologie(id)
);
CREATE UNIQUE INDEX gwb_msstamm_idx1 ON gwbase.gwb_msstamm USING btree (kennnr);
CREATE INDEX gwb_msstamm_idx2 ON gwbase.gwb_msstamm USING btree (kennnr2);


-- gwbase.gwb_object_pipes definition

-- Drop table

-- DROP TABLE gwbase.gwb_object_pipes;

CREATE TABLE gwbase.gwb_object_pipes (
	id int4 NOT NULL,
	object_id int4 NOT NULL,
	itemname varchar(50) NOT NULL,
	watertype_id int4 NULL,
	inputpipe_id int4 NULL,
	outputpipe_id int4 NULL,
	pipetype_id int4 NULL,
	elevation float8 NULL,
	diameter int4 NULL,
	material_id int4 NULL,
	CONSTRAINT gwb_object_pipes_pk PRIMARY KEY (id),
	CONSTRAINT gwb_object_pipes_material_fk FOREIGN KEY (material_id) REFERENCES gwbase.gwb_pipematerialcat(id),
	CONSTRAINT gwb_object_pipes_object_fk FOREIGN KEY (object_id) REFERENCES gwbase.gwb_msstamm(id) ON DELETE CASCADE,
	CONSTRAINT gwb_object_pipes_pipetype_fk FOREIGN KEY (pipetype_id) REFERENCES gwbase.gwb_pipetypecat(id),
	CONSTRAINT gwb_object_pipes_watertype_fk FOREIGN KEY (watertype_id) REFERENCES gwbase.gwb_watertypecat(id)
);


-- gwbase.gwb_object_techparts definition

-- Drop table

-- DROP TABLE gwbase.gwb_object_techparts;

CREATE TABLE gwbase.gwb_object_techparts (
	id int4 NOT NULL,
	object_id int4 NULL,
	part_id int4 NULL,
	commenttext varchar(255) NULL,
	CONSTRAINT gwb_object_techparts_pk PRIMARY KEY (id),
	CONSTRAINT gwb_object_techparts_object_fk FOREIGN KEY (object_id) REFERENCES gwbase.gwb_msstamm(id) ON DELETE CASCADE,
	CONSTRAINT gwb_object_techparts_part_fk FOREIGN KEY (part_id) REFERENCES gwbase.gwb_techpartscat(id)
);


-- gwbase.gwb_paramet definition

-- Drop table

-- DROP TABLE gwbase.gwb_paramet;

CREATE TABLE gwbase.gwb_paramet (
	id int4 NOT NULL,
	parameterkuerzel varchar(20) NULL,
	parametername varchar(100) NULL,
	standardeinheit varchar(10) NULL,
	standardunitsoil varchar(10) NULL,
	standarduniteluat varchar(10) NULL,
	keynr varchar(10) NULL,
	keynr2 varchar(10) NULL,
	paramtype int4 NULL,
	issumparam bpchar(1) DEFAULT 'n'::bpchar NULL,
	isonsiteparam bpchar(1) DEFAULT 'n'::bpchar NULL,
	molmasse float8 NULL,
	color int4 NULL,
	kat1 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat2 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat3 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat4 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat5 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat6 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat7 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat8 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat9 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat10 bpchar(1) DEFAULT 'n'::bpchar NULL,
	bemerkungparameter varchar(250) NULL,
	allownegative bpchar(1) DEFAULT 'n'::bpchar NULL,
	grenzwert1 float8 NULL,
	grenzwert1name varchar(50) NULL,
	grenzwert2 float8 NULL,
	grenzwert2name varchar(50) NULL,
	grenzwert3 float8 NULL,
	grenzwert3name varchar(50) NULL,
	grenzwert4 float8 NULL,
	grenzwert4name varchar(50) NULL,
	grenzwert5 float8 NULL,
	grenzwert5name varchar(50) NULL,
	grenzwert6 float8 NULL,
	grenzwert6name varchar(50) NULL,
	grenzwert7 float8 NULL,
	grenzwert7name varchar(50) NULL,
	grenzwert8 float8 NULL,
	grenzwert8name varchar(50) NULL,
	stdgrenzwert bpchar(1) NULL,
	seqnr int4 NULL,
	standardunitair varchar(10) NULL,
	lowerbound float8 NULL,
	upperbound float8 NULL,
	web_cat varchar(2) NULL,
	stdmethod_id int4 NULL,
	detectlimitwater float8 NULL,
	detectlimitsoil float8 NULL,
	detectlimitaqsol float8 NULL,
	detectlimitair float8 NULL,
	decimalplaces int4 NULL,
	valenceno int4 NULL,
	paramgroup varchar(1) NULL,
	symbol int4 NULL,
	gww bpchar(1) DEFAULT '-'::bpchar NULL,
	gwwtransferdate timestamp NULL,
	hasupperlimit bpchar(1) DEFAULT '+'::bpchar NULL,
	CONSTRAINT gwb_paramet_pk PRIMARY KEY (id),
	CONSTRAINT gwb_param_methods_fk FOREIGN KEY (stdmethod_id) REFERENCES gwbase.gwb_analymethods(id)
);
CREATE UNIQUE INDEX gwb_paramet_idx1 ON gwbase.gwb_paramet USING btree (parameterkuerzel);


-- gwbase.gwb_prog_param definition

-- Drop table

-- DROP TABLE gwbase.gwb_prog_param;

CREATE TABLE gwbase.gwb_prog_param (
	prog_id int4 NOT NULL,
	seqnr int4 NOT NULL,
	param_id int4 NOT NULL,
	detectlimit float8 NULL,
	paramunit varchar(10) NULL,
	CONSTRAINT gwb_progparam_pk PRIMARY KEY (prog_id, seqnr, param_id),
	CONSTRAINT gwb_progparam_parameter_fk FOREIGN KEY (param_id) REFERENCES gwbase.gwb_paramet(id),
	CONSTRAINT gwb_progparam_prog_fk FOREIGN KEY (prog_id) REFERENCES gwbase.gwb_ukat(katalognr)
);


-- gwbase.gwb_proggroups_progs definition

-- Drop table

-- DROP TABLE gwbase.gwb_proggroups_progs;

CREATE TABLE gwbase.gwb_proggroups_progs (
	group_id int4 NOT NULL,
	prog_id int4 NOT NULL,
	seqnr int4 NULL,
	CONSTRAINT gwb_proggroups_progs_pk PRIMARY KEY (group_id, prog_id),
	CONSTRAINT gwb_proggroups_group_fk FOREIGN KEY (group_id) REFERENCES gwbase.gwb_proggroups(id),
	CONSTRAINT gwb_proggroups_prog_fk FOREIGN KEY (prog_id) REFERENCES gwbase.gwb_ukat(katalognr)
);


-- gwbase.gwb_projects_users definition

-- Drop table

-- DROP TABLE gwbase.gwb_projects_users;

CREATE TABLE gwbase.gwb_projects_users (
	pu_projectid int4 NOT NULL,
	pu_loginname varchar(20) NOT NULL,
	pu_priv_wells int2 NULL,
	pu_priv_level int2 NULL,
	pu_priv_analysis int2 NULL,
	pu_priv_discharge int2 NULL,
	pu_priv_rainfall int2 NULL,
	CONSTRAINT gwb_projectsusers_pk PRIMARY KEY (pu_projectid, pu_loginname),
	CONSTRAINT gwb_projectsusers_projects_fk FOREIGN KEY (pu_projectid) REFERENCES gwbase.gwb_projects(id),
	CONSTRAINT gwb_projectsusers_users_fk FOREIGN KEY (pu_loginname) REFERENCES gwbase.gwb_users(us_loginname)
);


-- gwbase.gwb_pump_data definition

-- Drop table

-- DROP TABLE gwbase.gwb_pump_data;

CREATE TABLE gwbase.gwb_pump_data (
	id int4 NOT NULL,
	well_id int4 NOT NULL,
	equip_id int4 NOT NULL,
	dateav timestamp NULL,
	head_vol float8 NULL,
	head_curr float8 NULL,
	vol_curr float8 NULL,
	datacount int4 NULL,
	CONSTRAINT gwb_pump_data_pk PRIMARY KEY (id),
	CONSTRAINT gwb_pumpdata_t_fk FOREIGN KEY (equip_id) REFERENCES gwbase.gwb_techequipment(id),
	CONSTRAINT gwb_pumpdata_well_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_rainfall definition

-- Drop table

-- DROP TABLE gwbase.gwb_rainfall;

CREATE TABLE gwbase.gwb_rainfall (
	well_id int4 NOT NULL,
	date1 timestamp NOT NULL,
	date2 timestamp NULL,
	rainfall float8 NULL,
	import_id int4 NULL,
	CONSTRAINT gwb_rainfall_pk PRIMARY KEY (well_id, date1),
	CONSTRAINT gwb_rainfall_wells_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_rates definition

-- Drop table

-- DROP TABLE gwbase.gwb_rates;

CREATE TABLE gwbase.gwb_rates (
	messtellenid int4 NOT NULL,
	datum1 timestamp NOT NULL,
	datum2 timestamp NULL,
	qwert float8 NULL,
	ophours float8 NULL,
	import_id int4 NULL,
	currentreading int4 NULL,
	commenttext varchar(50) NULL,
	valveposition float8 NULL,
	CONSTRAINT gwb_rates_pk PRIMARY KEY (messtellenid, datum1),
	CONSTRAINT gwb_rates_wells_fk FOREIGN KEY (messtellenid) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_reportgroups_params definition

-- Drop table

-- DROP TABLE gwbase.gwb_reportgroups_params;

CREATE TABLE gwbase.gwb_reportgroups_params (
	group_id int4 NOT NULL,
	param_id int4 NOT NULL,
	CONSTRAINT gwb_reportgroups_params_pk PRIMARY KEY (group_id, param_id),
	CONSTRAINT gwb_rgp_group_fk FOREIGN KEY (group_id) REFERENCES gwbase.gwb_reportgroups(id) ON DELETE CASCADE,
	CONSTRAINT gwb_rgp_param_fk FOREIGN KEY (param_id) REFERENCES gwbase.gwb_paramet(id) ON DELETE CASCADE
);


-- gwbase.gwb_reportgroups_stations definition

-- Drop table

-- DROP TABLE gwbase.gwb_reportgroups_stations;

CREATE TABLE gwbase.gwb_reportgroups_stations (
	group_id int4 NOT NULL,
	station_id int4 NOT NULL,
	CONSTRAINT gwb_reportgroups_stations_pk PRIMARY KEY (group_id, station_id),
	CONSTRAINT gwb_rgs_group_fk FOREIGN KEY (group_id) REFERENCES gwbase.gwb_reportgroups(id) ON DELETE CASCADE,
	CONSTRAINT gwb_rgs_station_fk FOREIGN KEY (station_id) REFERENCES gwbase.gwb_msstamm(id) ON DELETE CASCADE
);


-- gwbase.gwb_reportgroups_templates definition

-- Drop table

-- DROP TABLE gwbase.gwb_reportgroups_templates;

CREATE TABLE gwbase.gwb_reportgroups_templates (
	id int4 NOT NULL,
	group_id int4 NOT NULL,
	reporttemplate varchar(255) NOT NULL,
	seqno int4 NOT NULL,
	CONSTRAINT gwb_reportgroups_templates_pk PRIMARY KEY (id),
	CONSTRAINT gwb_rgt_group_fk FOREIGN KEY (group_id) REFERENCES gwbase.gwb_reportgroups(id) ON DELETE CASCADE
);


-- gwbase.gwb_samplecalendar definition

-- Drop table

-- DROP TABLE gwbase.gwb_samplecalendar;

CREATE TABLE gwbase.gwb_samplecalendar (
	sampledate int4 NOT NULL,
	well_id int4 NOT NULL,
	proggroup_id int4 NOT NULL,
	done varchar(1) NULL,
	CONSTRAINT gwb_samplecaledar_pk PRIMARY KEY (sampledate, well_id),
	CONSTRAINT gwb_samplecalendar_prgr_fk FOREIGN KEY (proggroup_id) REFERENCES gwbase.gwb_proggroups(id),
	CONSTRAINT gwb_samplecalendar_well_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_sensordepth definition

-- Drop table

-- DROP TABLE gwbase.gwb_sensordepth;

CREATE TABLE gwbase.gwb_sensordepth (
	id int4 NOT NULL,
	well_id int4 NOT NULL,
	sensordepth float8 NOT NULL,
	sensordate timestamp NOT NULL,
	commenttext varchar(100) NULL,
	mtype int4 NULL,
	CONSTRAINT gwb_sensordepth_pk PRIMARY KEY (id),
	CONSTRAINT gwb_sensordepth_well_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_spec_yield definition

-- Drop table

-- DROP TABLE gwbase.gwb_spec_yield;

CREATE TABLE gwbase.gwb_spec_yield (
	id int4 NOT NULL,
	well_id int4 NOT NULL,
	mdate timestamp NULL,
	drawdown float8 NULL,
	flow float8 NULL,
	commenttext varchar(50) NULL,
	mdate_static timestamp NULL,
	static_level float8 NULL,
	pressure float8 NULL,
	power float8 NULL,
	CONSTRAINT gwb_spec_yield_pk PRIMARY KEY (id),
	CONSTRAINT gwb_spec_yield_well_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id)
);
CREATE UNIQUE INDEX gwb_spec_yield_ix1 ON gwbase.gwb_spec_yield USING btree (well_id, mdate);


-- gwbase.gwb_stnflowdevicehistory definition

-- Drop table

-- DROP TABLE gwbase.gwb_stnflowdevicehistory;

CREATE TABLE gwbase.gwb_stnflowdevicehistory (
	id int4 NOT NULL,
	station_id int4 NOT NULL,
	flowdevice_id int4 NOT NULL,
	flowdevicedate timestamp NOT NULL,
	flowdevicecommenttext varchar(100) NULL,
	CONSTRAINT gwb_stnflowdevicehistory_pk PRIMARY KEY (id),
	CONSTRAINT gwb_stnflowdevicehistory_device_fk FOREIGN KEY (flowdevice_id) REFERENCES gwbase.gwb_flowmeasurementcat(id),
	CONSTRAINT gwb_stnflowdevicehistory_stn_fk FOREIGN KEY (station_id) REFERENCES gwbase.gwb_msstamm(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX gwb_stnflowdevicehistory_ix1 ON gwbase.gwb_stnflowdevicehistory USING btree (flowdevice_id, flowdevicedate);


-- gwbase.gwb_stoffkl definition

-- Drop table

-- DROP TABLE gwbase.gwb_stoffkl;

CREATE TABLE gwbase.gwb_stoffkl (
	class_id int4 NOT NULL,
	"name" varchar(50) NULL,
	classtype int4 NULL,
	kat1 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat2 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat3 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat4 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat5 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat6 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat7 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat8 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat9 bpchar(1) DEFAULT 'n'::bpchar NULL,
	kat10 bpchar(1) DEFAULT 'n'::bpchar NULL,
	standardeinheit varchar(10) NULL,
	standardunitsoil varchar(10) NULL,
	standarduniteluat varchar(10) NULL,
	sumparam_id int4 NULL,
	color int4 NULL,
	sumcalconcomplete bpchar(1) DEFAULT 'n'::bpchar NULL,
	standardunitair varchar(10) NULL,
	CONSTRAINT gwb_stoffkl_pk PRIMARY KEY (class_id),
	CONSTRAINT gwb_stoffkl_paramet_fk FOREIGN KEY (sumparam_id) REFERENCES gwbase.gwb_paramet(id)
);
CREATE INDEX gwb_stoffkl_idx1 ON gwbase.gwb_stoffkl USING btree (name);


-- gwbase.gwb_storedrivernet_aquifers definition

-- Drop table

-- DROP TABLE gwbase.gwb_storedrivernet_aquifers;

CREATE TABLE gwbase.gwb_storedrivernet_aquifers (
	storedrivernet_id int4 NOT NULL,
	aquifer_id int4 NOT NULL,
	CONSTRAINT gwb_srnetaqui_pk PRIMARY KEY (storedrivernet_id, aquifer_id),
	CONSTRAINT gwb_srnetaqui_aquifer_fk FOREIGN KEY (aquifer_id) REFERENCES gwbase.gwb_gwstockw(id),
	CONSTRAINT gwb_srnetaqui_rivernet_fk FOREIGN KEY (storedrivernet_id) REFERENCES gwbase.gwb_storedrivernets(id)
);


-- gwbase.gwb_storedrivernet_points definition

-- Drop table

-- DROP TABLE gwbase.gwb_storedrivernet_points;

CREATE TABLE gwbase.gwb_storedrivernet_points (
	storedrivernet_id int4 NOT NULL,
	seqnr int4 NOT NULL,
	x float8 NULL,
	y float8 NULL,
	z float8 NULL,
	connectiontype int2 NULL,
	connectionfactor float8 NULL,
	CONSTRAINT gwb_srnetpnts_pk PRIMARY KEY (storedrivernet_id, seqnr),
	CONSTRAINT gwb_srnetpnts_rivernet_fk FOREIGN KEY (storedrivernet_id) REFERENCES gwbase.gwb_storedrivernets(id)
);


-- gwbase.gwb_structparts definition

-- Drop table

-- DROP TABLE gwbase.gwb_structparts;

CREATE TABLE gwbase.gwb_structparts (
	id int4 NOT NULL,
	struct_id int4 NULL,
	structpartname varchar(50) NULL,
	connectiontype int2 NULL,
	connectionfactor float8 NULL,
	elevationtop float8 NULL,
	nextpart_id int4 NULL,
	prevpart_id int4 NULL,
	CONSTRAINT gwb_structparts_pk PRIMARY KEY (id),
	CONSTRAINT gwb_structparts_struct_fk FOREIGN KEY (struct_id) REFERENCES gwbase.gwb_structures(id)
);


-- gwbase.gwb_structparts_aquifers definition

-- Drop table

-- DROP TABLE gwbase.gwb_structparts_aquifers;

CREATE TABLE gwbase.gwb_structparts_aquifers (
	structpart_id int4 NOT NULL,
	aquifer_id int4 NOT NULL,
	CONSTRAINT gwb_structpartaqui_pk PRIMARY KEY (structpart_id, aquifer_id),
	CONSTRAINT gwb_spaqui_aquifer_fk FOREIGN KEY (aquifer_id) REFERENCES gwbase.gwb_gwstockw(id),
	CONSTRAINT gwb_spaqui_structpart_fk FOREIGN KEY (structpart_id) REFERENCES gwbase.gwb_structparts(id)
);


-- gwbase.gwb_structpoints definition

-- Drop table

-- DROP TABLE gwbase.gwb_structpoints;

CREATE TABLE gwbase.gwb_structpoints (
	id int4 NOT NULL,
	x float8 NULL,
	y float8 NULL,
	station_id int4 NULL,
	CONSTRAINT gwb_structpoints_pk PRIMARY KEY (id),
	CONSTRAINT gwb_structpoints_wells_fk FOREIGN KEY (station_id) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_surfacewater_parts definition

-- Drop table

-- DROP TABLE gwbase.gwb_surfacewater_parts;

CREATE TABLE gwbase.gwb_surfacewater_parts (
	id int4 NOT NULL,
	main_id int4 NOT NULL,
	itemname varchar(100) NOT NULL,
	seqnr int4 NULL,
	CONSTRAINT gwb_surfacewater_parts_pk PRIMARY KEY (id),
	CONSTRAINT gwb_swparts_mainsw_fk FOREIGN KEY (main_id) REFERENCES gwbase.gwb_surfacewaters(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX gwb_surfacewaterparts_idx2 ON gwbase.gwb_surfacewater_parts USING btree (main_id, itemname);


-- gwbase.gwb_techequip_history definition

-- Drop table

-- DROP TABLE gwbase.gwb_techequip_history;

CREATE TABLE gwbase.gwb_techequip_history (
	id int4 NOT NULL,
	equip_id int4 NOT NULL,
	well_id int4 NOT NULL,
	startdate timestamp NULL,
	enddate timestamp NULL,
	historytype bpchar(1) NULL,
	CONSTRAINT gwb_techequip_history_pk PRIMARY KEY (id),
	CONSTRAINT gwb_tehistory_equip_fk FOREIGN KEY (equip_id) REFERENCES gwbase.gwb_techequipment(id),
	CONSTRAINT gwb_tehistory_well_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_techequip_repairs definition

-- Drop table

-- DROP TABLE gwbase.gwb_techequip_repairs;

CREATE TABLE gwbase.gwb_techequip_repairs (
	id int4 NOT NULL,
	equip_id int4 NOT NULL,
	history_id int4 NOT NULL,
	address varchar(255) NULL,
	person varchar(30) NULL,
	repairreason varchar(255) NULL,
	repaircosts float8 NULL,
	remarks text NULL,
	CONSTRAINT gwb_techequip_repairs_pk PRIMARY KEY (id),
	CONSTRAINT gwb_terepairs_equip_fk FOREIGN KEY (equip_id) REFERENCES gwbase.gwb_techequipment(id),
	CONSTRAINT gwb_terepairs_history_fk FOREIGN KEY (history_id) REFERENCES gwbase.gwb_techequip_history(id)
);


-- gwbase.gwb_techequip_stores definition

-- Drop table

-- DROP TABLE gwbase.gwb_techequip_stores;

CREATE TABLE gwbase.gwb_techequip_stores (
	id int4 NOT NULL,
	equip_id int4 NOT NULL,
	history_id int4 NOT NULL,
	storeaddress varchar(255) NULL,
	remarks text NULL,
	CONSTRAINT gwb_techequip_stores_pk PRIMARY KEY (id),
	CONSTRAINT gwb_testores_equip_fk FOREIGN KEY (equip_id) REFERENCES gwbase.gwb_techequipment(id),
	CONSTRAINT gwb_testores_history_fk FOREIGN KEY (history_id) REFERENCES gwbase.gwb_techequip_history(id)
);


-- gwbase.gwb_techequip_wells definition

-- Drop table

-- DROP TABLE gwbase.gwb_techequip_wells;

CREATE TABLE gwbase.gwb_techequip_wells (
	id int4 NOT NULL,
	equip_id int4 NOT NULL,
	history_id int4 NOT NULL,
	"depth" float8 NULL,
	depthunit varchar(5) NULL,
	remarks text NULL,
	CONSTRAINT gwb_techequip_wells_pk PRIMARY KEY (id),
	CONSTRAINT gwb_tewells_equip_fk FOREIGN KEY (equip_id) REFERENCES gwbase.gwb_techequipment(id),
	CONSTRAINT gwb_tewells_history_fk FOREIGN KEY (history_id) REFERENCES gwbase.gwb_techequip_history(id)
);


-- gwbase.gwb_valves definition

-- Drop table

-- DROP TABLE gwbase.gwb_valves;

CREATE TABLE gwbase.gwb_valves (
	id int4 NOT NULL,
	object_id int4 NOT NULL,
	elevation float8 NULL,
	diameter int4 NULL,
	type_id int4 NULL,
	maxposition int4 NULL,
	description varchar(50) NULL,
	CONSTRAINT gwb_valves_pk PRIMARY KEY (id),
	CONSTRAINT gwb_valves_object_fk FOREIGN KEY (object_id) REFERENCES gwbase.gwb_msstamm(id) ON DELETE CASCADE
);


-- gwbase.gwb_virtualwells definition

-- Drop table

-- DROP TABLE gwbase.gwb_virtualwells;

CREATE TABLE gwbase.gwb_virtualwells (
	id int4 NOT NULL,
	stationname varchar(20) NULL,
	referencestation_id int4 NOT NULL,
	x float8 NULL,
	y float8 NULL,
	z float8 NULL,
	timelag int2 NULL,
	shift float8 NULL,
	isabsolute bpchar(1) DEFAULT 'n'::bpchar NULL,
	isactive bpchar(1) DEFAULT 'y'::bpchar NULL,
	createdby varchar(20) NULL,
	"comments" varchar(255) NULL,
	symbol int4 NULL,
	utm_east float8 NULL,
	utm_north float8 NULL,
	CONSTRAINT gwb_virtualwells_pk PRIMARY KEY (id),
	CONSTRAINT gwb_virtualwells_wells_fk FOREIGN KEY (referencestation_id) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_waterquantity definition

-- Drop table

-- DROP TABLE gwbase.gwb_waterquantity;

CREATE TABLE gwbase.gwb_waterquantity (
	org_id int4 NOT NULL,
	"year" int2 NOT NULL,
	groundwater float8 NULL,
	sourcewater float8 NULL,
	surfacewater float8 NULL,
	externalwater float8 NULL,
	CONSTRAINT gwb_waterquantity_pk PRIMARY KEY (org_id, year),
	CONSTRAINT gwb_waterquantity_org_fk FOREIGN KEY (org_id) REFERENCES gwbase.gwb_organisation(org_id)
);


-- gwbase.gwb_wellgeology definition

-- Drop table

-- DROP TABLE gwbase.gwb_wellgeology;

CREATE TABLE gwbase.gwb_wellgeology (
	id int4 NOT NULL,
	well_id int4 NULL,
	geology_id int4 NULL,
	depthtobase float8 NULL,
	deeper varchar(1) NULL,
	CONSTRAINT gwb_wellgeology_pk PRIMARY KEY (id),
	CONSTRAINT gwb_wellgeology_geol_fk FOREIGN KEY (geology_id) REFERENCES gwbase.gwb_geologie(id),
	CONSTRAINT gwb_wellgeology_well_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_wellproperties_values definition

-- Drop table

-- DROP TABLE gwbase.gwb_wellproperties_values;

CREATE TABLE gwbase.gwb_wellproperties_values (
	id int4 NOT NULL,
	property_id int4 NOT NULL,
	valuetext varchar(50) NOT NULL,
	CONSTRAINT gwb_wellproperties_values_pk PRIMARY KEY (id),
	CONSTRAINT gwb_wpvalues_property_fk FOREIGN KEY (property_id) REFERENCES gwbase.gwb_wellproperties(id) ON DELETE CASCADE
);


-- gwbase.gwb_wells_projects definition

-- Drop table

-- DROP TABLE gwbase.gwb_wells_projects;

CREATE TABLE gwbase.gwb_wells_projects (
	well_id int4 NOT NULL,
	project_id int4 NOT NULL,
	CONSTRAINT gwb_wells_projects_pk PRIMARY KEY (well_id, project_id),
	CONSTRAINT gwb_wellsprojects_projects_fk FOREIGN KEY (project_id) REFERENCES gwbase.gwb_projects(id),
	CONSTRAINT gwb_wellsprojects_wells_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id)
);
CREATE INDEX gwb_wells_projects_idx1 ON gwbase.gwb_wells_projects USING btree (project_id, well_id);


-- gwbase.gwb_wellstatushistory definition

-- Drop table

-- DROP TABLE gwbase.gwb_wellstatushistory;

CREATE TABLE gwbase.gwb_wellstatushistory (
	id int4 NOT NULL,
	well_id int4 NOT NULL,
	status_id int4 NOT NULL,
	statusdate timestamp NOT NULL,
	statuscommenttext varchar(100) NULL,
	CONSTRAINT gwb_wellstatushistory_pk PRIMARY KEY (id),
	CONSTRAINT gwb_wellstatushistory_status_fk FOREIGN KEY (status_id) REFERENCES gwbase.gwb_wellstatuscat(id),
	CONSTRAINT gwb_wellstatushistory_well_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX gwb_wellstatushistory_ix1 ON gwbase.gwb_wellstatushistory USING btree (well_id, statusdate);


-- gwbase.gwb_welltodolist definition

-- Drop table

-- DROP TABLE gwbase.gwb_welltodolist;

CREATE TABLE gwbase.gwb_welltodolist (
	id int4 NOT NULL,
	well_id int4 NULL,
	category_id int4 NULL,
	description varchar(255) NULL,
	createdate timestamp NULL,
	todountil timestamp NULL,
	user_id int4 NOT NULL,
	donedate timestamp NULL,
	donecomment varchar(255) NULL,
	doneuser_id int4 NULL,
	sap_notifynumber varchar(12) NULL,
	sap_ordernumber varchar(12) NULL,
	sap_status varchar(10) NULL,
	status int4 NULL,
	CONSTRAINT gwb_welltodolist_pk PRIMARY KEY (id),
	CONSTRAINT gwb_welltodolist_cat_fk FOREIGN KEY (category_id) REFERENCES gwbase.gwb_todocategories(id),
	CONSTRAINT gwb_welltodolist_well_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_wstand definition

-- Drop table

-- DROP TABLE gwbase.gwb_wstand;

CREATE TABLE gwbase.gwb_wstand (
	id int4 NOT NULL,
	messtellenid int4 NOT NULL,
	datumzeit timestamp NOT NULL,
	wasserstand float8 NULL,
	bezugmp bpchar(1) DEFAULT 'y'::bpchar NULL,
	quality bpchar(1) DEFAULT '+'::bpchar NULL,
	person_id int4 NULL,
	import_id int4 NULL,
	"datatype" int4 DEFAULT 0 NULL,
	level_sl float8 NULL,
	loggeddepth float8 NULL,
	loggeddepth_sl float8 NULL,
	CONSTRAINT gwb_wstand_pk PRIMARY KEY (id),
	CONSTRAINT gwb_wstand_wells_fk FOREIGN KEY (messtellenid) REFERENCES gwbase.gwb_msstamm(id)
);
CREATE UNIQUE INDEX gwb_wstand_idx1 ON gwbase.gwb_wstand USING btree (messtellenid, datumzeit);
CREATE INDEX gwb_wstand_idx2 ON gwbase.gwb_wstand USING btree (messtellenid, datumzeit DESC);


-- gwbase.gwm_depths definition

-- Drop table

-- DROP TABLE gwbase.gwm_depths;

CREATE TABLE gwbase.gwm_depths (
	well_id int4 NOT NULL,
	"depth" float8 NOT NULL,
	CONSTRAINT gwb_depths_pk PRIMARY KEY (well_id, depth),
	CONSTRAINT gwm_depths_well_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwm_routes_wells definition

-- Drop table

-- DROP TABLE gwbase.gwm_routes_wells;

CREATE TABLE gwbase.gwm_routes_wells (
	route_id int4 NOT NULL,
	well_id int4 NOT NULL,
	seqnr int4 NULL,
	CONSTRAINT gwb_routes_wells_pk PRIMARY KEY (route_id, well_id),
	CONSTRAINT gwm_routeswells_route_fk FOREIGN KEY (route_id) REFERENCES gwbase.gwm_routes(route_id),
	CONSTRAINT gwm_routeswells_well_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gpd_msstamm definition

-- Drop table

-- DROP TABLE gwbase.gpd_msstamm;

CREATE TABLE gwbase.gpd_msstamm (
	msid int4 NOT NULL,
	stm_locid int4 NULL,
	stm_bohrid int4 NULL,
	stm_status int4 NULL,
	stm_vertraulichkeit int4 NULL,
	stm_bohrart int4 NULL,
	stm_bohrverf int4 NULL,
	stm_bohrzweck int4 NULL,
	stm_koordfind int4 NULL,
	stm_koordsys int4 NULL,
	stm_endrechtwert float8 NULL,
	stm_endhochwert float8 NULL,
	stm_bohransatzhoehe float8 NULL,
	stm_bohrendhoehe float8 NULL,
	stm_hfind int4 NULL,
	stm_hsys int4 NULL,
	stm_aktenzeichen varchar(64) NULL,
	stm_archivfach int4 NULL,
	stm_arnum int4 NULL,
	stm_tiefbarchivnr int4 NULL,
	stm_tk5 int4 NULL,
	stm_land int4 NULL,
	stm_bundesland int4 NULL,
	stm_landkreis int4 NULL,
	stm_regierungsbezirk int4 NULL,
	stm_behoerde int4 NULL,
	stm_auftraggeber int4 NULL,
	stm_frmbaustelle int4 NULL,
	stm_frmbohrung int4 NULL,
	stm_frmausbau int4 NULL,
	stm_frmkellerausbau int4 NULL,
	stm_betreuendesbuero int4 NULL,
	stm_dtmbohrbeginn timestamp NULL,
	stm_dtmbohrende timestamp NULL,
	stm_dtmbohrprot timestamp NULL,
	stm_dtmdverfass timestamp NULL,
	stm_dtmantrag timestamp NULL,
	stm_dtmgenehmigung timestamp NULL,
	stm_dtmbplatzeinrichten timestamp NULL,
	stm_dtmbplatzraeumen timestamp NULL,
	stm_dtmimport timestamp NULL,
	stm_bearbeiterbohr int4 NULL,
	stm_bearbeitergeol int4 NULL,
	stm_bearbeiterhydro int4 NULL,
	stm_bohrmeister int4 NULL,
	stm_dverfasser varchar(80) NULL,
	stm_bauleiter int4 NULL,
	stm_dathinw varchar(48) NULL,
	stm_bemerkungen varchar(254) NULL,
	stm_hydaufschlussnr int4 NULL,
	stm_msart int4 NULL,
	stm_stratigrafieliste int4 NULL,
	stm_ptfileyn varchar(1) NULL,
	stm_issepimport varchar(1) NULL,
	stm_issep varchar(1) NULL,
	stm_sepvalid varchar(1) NULL,
	stm_oldwellid int4 NULL,
	CONSTRAINT gpd_msstamm_pk PRIMARY KEY (msid),
	CONSTRAINT fk_gpd_msstamm_1 FOREIGN KEY (msid) REFERENCES gwbase.gwb_msstamm(id) ON DELETE CASCADE
);


-- gwbase.gpd_mssynonyme definition

-- Drop table

-- DROP TABLE gwbase.gpd_mssynonyme;

CREATE TABLE gwbase.gpd_mssynonyme (
	msid int4 NOT NULL,
	mssyn_id int4 NOT NULL,
	mssyn_synid int4 NULL,
	mssyn_bezeichnung varchar(128) NULL,
	CONSTRAINT gpd_mssynonyme_pk PRIMARY KEY (msid, mssyn_id),
	CONSTRAINT fk_gpd_mssynonyme_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gpd_packertest definition

-- Drop table

-- DROP TABLE gwbase.gpd_packertest;

CREATE TABLE gwbase.gpd_packertest (
	msid int4 NOT NULL,
	recid int4 NOT NULL,
	pt_testnr varchar(50) NULL,
	pt_datumzeit timestamp NULL,
	pt_analysenansatz varchar(50) NULL,
	pt_intervallende float8 NULL,
	pt_intervallanfang float8 NULL,
	pt_streckenlaenge float8 NULL,
	pt_zone int4 NULL,
	pt_bemerkung varchar(255) NULL,
	pt_art_t_kf int4 NULL,
	pt_t float8 NULL,
	pt_t_zfgolder float8 NULL,
	pt_kf float8 NULL,
	pt_s float8 NULL,
	pt_ss float8 NULL,
	pt_elf float8 NULL,
	pt_ph float8 NULL,
	pt_intervallmittegok float8 NULL,
	pt_ansatzhoehe float8 NULL,
	pt_intervallmittenn float8 NULL,
	pt_statischergwspiegelgok float8 NULL,
	pt_statischergwspiegelnn float8 NULL,
	pt_produziertesvolumen float8 NULL,
	pt_geoleinhvon int4 NULL,
	pt_geoleinhbis int4 NULL,
	pt_geoleinhimporterr int4 NULL,
	CONSTRAINT gpd_packertest_pk PRIMARY KEY (msid, recid),
	CONSTRAINT fk_gpd_packertest_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gpd_schichten definition

-- Drop table

-- DROP TABLE gwbase.gpd_schichten;

CREATE TABLE gwbase.gpd_schichten (
	msid int4 NOT NULL,
	sd_layerid int4 NOT NULL,
	sd_layercntr int4 NOT NULL,
	sd_layerno int4 NULL,
	sd_sublayerno int4 NULL,
	sd_ok float8 NULL,
	sd_uk float8 NULL,
	sd_art varchar(128) NULL,
	sd_stratigrafie varchar(254) NULL,
	sd_stratigrafieid int4 NULL,
	sd_petrografie varchar(254) NULL,
	sd_genese varchar(254) NULL,
	sd_farbe varchar(254) NULL,
	sd_zusatz varchar(254) NULL,
	sd_bemerkung varchar(128) NULL,
	sd_beschaffenheitbohrgut varchar(128) NULL,
	sd_bohrvorgang varchar(128) NULL,
	sd_bodengruppe varchar(128) NULL,
	sd_kalkgehalt varchar(128) NULL,
	sd_intversion int4 NULL,
	sd_intgruppe varchar(12) NULL,
	sd_intklasse varchar(12) NULL,
	sd_intnotitz text NULL,
	sd_textur int4 NULL,
	sd_kristallinitaet int4 NULL,
	sd_trennflaechenfuellung int4 NULL,
	sd_streichenfallen varchar(64) NULL,
	sd_ergbemerkung varchar(255) NULL,
	sd_ueblichebez varchar(64) NULL,
	sd_geolbez varchar(64) NULL,
	sd_bodenklasse int4 NULL,
	sd_verwitterung int4 NULL,
	sd_wetzones varchar(64) NULL,
	sd_fractures varchar(64) NULL,
	sd_petroklar varchar(255) NULL,
	sd_seppetro varchar(254) NULL,
	sd_sepfarbe varchar(254) NULL,
	sd_sepbeschbg varchar(128) NULL,
	sd_sepbeschbv varchar(128) NULL,
	sd_sepkalkgeh varchar(128) NULL,
	sd_stratigrafiekrz varchar(64) NULL,
	sd_artklar text NULL,
	sd_geneseklar text NULL,
	sd_farbeklar text NULL,
	sd_zusatzklar text NULL,
	sd_beschbgklar text NULL,
	sd_bohrvorgangklar text NULL,
	sd_bodengruppeklar text NULL,
	sd_kalkgehaltklar text NULL,
	sd_seperror int4 NULL,
	CONSTRAINT gpd_schichten_pk PRIMARY KEY (msid, sd_layerid, sd_layercntr),
	CONSTRAINT fk_gpd_schichten_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gpd_schichttitel definition

-- Drop table

-- DROP TABLE gwbase.gpd_schichttitel;

CREATE TABLE gwbase.gpd_schichttitel (
	msid int4 NOT NULL,
	recid int4 NOT NULL,
	st_intversion int4 NULL,
	st_intprojleiter int4 NULL,
	st_intbearbeiter int4 NULL,
	st_dtmintbearbeitung timestamp NULL,
	st_dtmintaenderung timestamp NULL,
	st_uebernahmekennung varchar(50) NULL,
	st_opfeldbearbeiter varchar(64) NULL,
	st_ausw varchar(24) NULL,
	st_intgruppe varchar(12) NULL,
	st_intbasisversion int4 NULL,
	st_intneu int4 NULL,
	st_intbemerkung varchar(254) NULL,
	CONSTRAINT gpd_schichttitel_pk PRIMARY KEY (msid, recid),
	CONSTRAINT fk_gpd_schichttitel_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gpd_spuelung definition

-- Drop table

-- DROP TABLE gwbase.gpd_spuelung;

CREATE TABLE gwbase.gpd_spuelung (
	msid int4 NOT NULL,
	recid int4 NOT NULL,
	sp_anfang float8 NULL,
	sp_ende float8 NULL,
	sp_art int4 NULL,
	sp_zusatz int4 NULL,
	sp_dichte float8 NULL,
	sp_dichteeinh int4 NULL,
	sp_verhalten varchar(255) NULL,
	sp_menge float8 NULL,
	CONSTRAINT gpd_spuelung_pk PRIMARY KEY (msid, recid),
	CONSTRAINT fk_gpd_spuelung_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gpd_wasserrecht definition

-- Drop table

-- DROP TABLE gwbase.gpd_wasserrecht;

CREATE TABLE gwbase.gpd_wasserrecht (
	msid int4 NOT NULL,
	typ varchar(50) NULL,
	nr float8 NULL,
	"name" varchar(50) NULL,
	wr1 varchar(255) NULL,
	wr2 varchar(255) NULL,
	wr3 varchar(255) NULL,
	nutzer varchar(50) NULL,
	qmax_a float8 NULL,
	qmax float8 NULL,
	qmax_h float8 NULL,
	qmax_s float8 NULL,
	qmit float8 NULL,
	qmit_h float8 NULL,
	bemerkung text NULL,
	datenquelle varchar(50) NULL,
	CONSTRAINT gpd_wasserrecht_pk PRIMARY KEY (msid),
	CONSTRAINT fk_gpd_wasserrecht_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gwb_checkrules definition

-- Drop table

-- DROP TABLE gwbase.gwb_checkrules;

CREATE TABLE gwbase.gwb_checkrules (
	cr_wellid int4 NOT NULL,
	cr_paramid int4 NOT NULL,
	cr_ruletype int2 NOT NULL,
	cr_checkvalue float8 NULL,
	cr_validfrom timestamp NULL,
	cr_validuntil timestamp NULL,
	cr_valuetype bpchar(1) DEFAULT 'a'::bpchar NULL,
	cr_valuedescr varchar(50) NULL,
	cr_active bpchar(1) DEFAULT 'y'::bpchar NULL,
	id int4 NULL,
	CONSTRAINT gwb_checkrules_pk PRIMARY KEY (cr_wellid, cr_paramid, cr_ruletype),
	CONSTRAINT gwb_checkrules_param_fk FOREIGN KEY (cr_paramid) REFERENCES gwbase.gwb_paramet(id),
	CONSTRAINT gwb_checkrules_well_fk FOREIGN KEY (cr_wellid) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_checkrules2 definition

-- Drop table

-- DROP TABLE gwbase.gwb_checkrules2;

CREATE TABLE gwbase.gwb_checkrules2 (
	cr_id int4 NOT NULL,
	cr_wellid int4 NOT NULL,
	cr_paramid int4 NOT NULL,
	cr_ruletype int4 NOT NULL,
	cr_checkvalue float8 NULL,
	cr_valuedescr varchar(255) NULL,
	cr_active bpchar(1) DEFAULT 'y'::bpchar NULL,
	cr_samples int2 NULL,
	cr_criticalsamples int2 NULL,
	cr_lookaheadrange int2 NULL,
	cr_lookaheadunit bpchar(1) NULL,
	cr_lookbehindrange int2 NULL,
	cr_lookbehindunit bpchar(1) NULL,
	CONSTRAINT gwb_checkrules2_pk PRIMARY KEY (cr_id),
	CONSTRAINT gwb_checkrules2_param_fk FOREIGN KEY (cr_paramid) REFERENCES gwbase.gwb_paramet(id),
	CONSTRAINT gwb_checkrules2_well_fk FOREIGN KEY (cr_wellid) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_checkrules_templates definition

-- Drop table

-- DROP TABLE gwbase.gwb_checkrules_templates;

CREATE TABLE gwbase.gwb_checkrules_templates (
	cr_templatename varchar(100) NULL,
	cr_paramid int4 NOT NULL,
	cr_ruletype int2 NOT NULL,
	cr_checkvalue float8 NULL,
	cr_validfrom timestamp NULL,
	cr_validuntil timestamp NULL,
	cr_valuetype bpchar(1) DEFAULT 'a'::bpchar NULL,
	cr_valuedescr varchar(50) NULL,
	CONSTRAINT gwb_checkrulestempl_param_fk FOREIGN KEY (cr_paramid) REFERENCES gwbase.gwb_paramet(id)
);


-- gwbase.gwb_class_param definition

-- Drop table

-- DROP TABLE gwbase.gwb_class_param;

CREATE TABLE gwbase.gwb_class_param (
	param_id int4 NOT NULL,
	class_id int4 NOT NULL,
	CONSTRAINT gwb_classparam_pk PRIMARY KEY (param_id, class_id),
	CONSTRAINT gwb_classparam_class_fk FOREIGN KEY (class_id) REFERENCES gwbase.gwb_stoffkl(class_id),
	CONSTRAINT gwb_classparam_parameter_fk FOREIGN KEY (param_id) REFERENCES gwbase.gwb_paramet(id)
);
CREATE UNIQUE INDEX gwb_class_param_idx1 ON gwbase.gwb_class_param USING btree (class_id, param_id);


-- gwbase.gwb_hchange definition

-- Drop table

-- DROP TABLE gwbase.gwb_hchange;

CREATE TABLE gwbase.gwb_hchange (
	messtellenid int4 NOT NULL,
	datumaenderung timestamp NOT NULL,
	neuehoehemp float8 NULL,
	neuehoeheokg float8 NULL,
	quality_id int4 NULL,
	"comment" varchar(100) NULL,
	filterbtm float8 NULL,
	CONSTRAINT gwb_hchange_pk PRIMARY KEY (messtellenid, datumaenderung),
	CONSTRAINT gwb_hchange_wells_fk FOREIGN KEY (messtellenid) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_inoutletstructure definition

-- Drop table

-- DROP TABLE gwbase.gwb_inoutletstructure;

CREATE TABLE gwbase.gwb_inoutletstructure (
	object_id int4 NULL,
	length float8 NULL,
	width float8 NULL,
	height float8 NULL,
	gaugewidth float8 NULL,
	filterareasize float8 NULL,
	commenttext text NULL,
	CONSTRAINT gwb_inoutletstructure_object_fk FOREIGN KEY (object_id) REFERENCES gwbase.gwb_msstamm(id) ON DELETE CASCADE
);


-- gwbase.gwb_level_attributes definition

-- Drop table

-- DROP TABLE gwbase.gwb_level_attributes;

CREATE TABLE gwbase.gwb_level_attributes (
	la_date_id int4 NOT NULL,
	la_attribute_id int4 NOT NULL,
	CONSTRAINT gwb_level_attributes_pk PRIMARY KEY (la_date_id, la_attribute_id),
	CONSTRAINT gwb_levelattr_comments_fk FOREIGN KEY (la_attribute_id) REFERENCES gwbase.gwb_levelcomments(commentid),
	CONSTRAINT gwb_levelattr_date_fk FOREIGN KEY (la_date_id) REFERENCES gwbase.gwb_wstand(id)
);


-- gwbase.gwb_manhole definition

-- Drop table

-- DROP TABLE gwbase.gwb_manhole;

CREATE TABLE gwbase.gwb_manhole (
	object_id int4 NULL,
	geometrytype varchar(1) NULL,
	length float8 NULL,
	width float8 NULL,
	height float8 NULL,
	material_id int4 NULL,
	accessgeometrytype varchar(1) NULL,
	accesslength float8 NULL,
	accesswidth float8 NULL,
	electrification varchar(1) NULL,
	remotecontrol varchar(1) NULL,
	commenttext text NULL,
	totalheight float8 NULL,
	CONSTRAINT gwb_manhole_material_fk FOREIGN KEY (material_id) REFERENCES gwbase.gwb_buildingmaterialcat(id),
	CONSTRAINT gwb_manhole_object_fk FOREIGN KEY (object_id) REFERENCES gwbase.gwb_msstamm(id) ON DELETE CASCADE
);


-- gwbase.gwb_measdate definition

-- Drop table

-- DROP TABLE gwbase.gwb_measdate;

CREATE TABLE gwbase.gwb_measdate (
	id int4 NOT NULL,
	logger bpchar(1) DEFAULT '0'::bpchar NULL,
	messtellenid int4 NULL,
	messdatum timestamp NULL,
	progid int4 NULL,
	"depth" float8 NULL,
	info varchar(2) NULL,
	import_id int4 NULL,
	depthrange int4 NULL,
	marker varchar(1) DEFAULT '+'::character varying NULL,
	samplestate float8 NULL,
	CONSTRAINT gwb_measdate_pk PRIMARY KEY (id),
	CONSTRAINT gwb_measdate_well_fk FOREIGN KEY (messtellenid) REFERENCES gwbase.gwb_msstamm(id)
);
CREATE INDEX gwb_measdate_idx1 ON gwbase.gwb_measdate USING btree (messtellenid, messdatum);
CREATE INDEX gwb_measdate_idx2 ON gwbase.gwb_measdate USING btree (logger, messtellenid, messdatum DESC);
CREATE INDEX ix_measdate_logger ON gwbase.gwb_measdate USING btree (logger, messtellenid, messdatum);


-- gwbase.gwb_memos definition

-- Drop table

-- DROP TABLE gwbase.gwb_memos;

CREATE TABLE gwbase.gwb_memos (
	well_id int4 NOT NULL,
	memotype int4 NOT NULL,
	date_id int4 NOT NULL,
	commenttext text NULL,
	CONSTRAINT gwb_memos_pk PRIMARY KEY (well_id, memotype, date_id),
	CONSTRAINT gwb_memos_wells_fk FOREIGN KEY (well_id) REFERENCES gwbase.gwb_msstamm(id)
);


-- gwbase.gwb_miscstructure definition

-- Drop table

-- DROP TABLE gwbase.gwb_miscstructure;

CREATE TABLE gwbase.gwb_miscstructure (
	object_id int4 NULL,
	commenttext text NULL,
	CONSTRAINT gwb_miscstructure_object_fk FOREIGN KEY (object_id) REFERENCES gwbase.gwb_msstamm(id) ON DELETE CASCADE
);


-- gwbase.gwb_param_valueclasses definition

-- Drop table

-- DROP TABLE gwbase.gwb_param_valueclasses;

CREATE TABLE gwbase.gwb_param_valueclasses (
	id int4 NOT NULL,
	param_id int4 NOT NULL,
	medium int4 NOT NULL,
	classvalue float8 NOT NULL,
	classcolor int4 NULL,
	CONSTRAINT gwb_param_valueclasses_pk PRIMARY KEY (id),
	CONSTRAINT gwb_pvc_param_fk FOREIGN KEY (param_id) REFERENCES gwbase.gwb_paramet(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX gwb_param_valueclasses_idx1 ON gwbase.gwb_param_valueclasses USING btree (param_id, medium, classvalue);


-- gwbase.gwb_proben definition

-- Drop table

-- DROP TABLE gwbase.gwb_proben;

CREATE TABLE gwbase.gwb_proben (
	datumid int4 NOT NULL,
	probennr varchar(20) NULL,
	probennehmer varchar(20) NULL,
	wetter varchar(50) NULL,
	labor varchar(100) NULL,
	entnahmetiefe float8 NULL,
	entnahmetiefebis float8 NULL,
	foerderrate varchar(10) NULL,
	foerderdauer varchar(10) NULL,
	foerdergeraet varchar(50) NULL,
	geraetetyp varchar(50) NULL,
	probenart varchar(50) NULL,
	truebung varchar(50) NULL,
	farbe varchar(50) NULL,
	geruch varchar(50) NULL,
	bodensatz varchar(50) NULL,
	uprogramm int4 NULL,
	programmeingegeben bpchar(1) DEFAULT 'n'::bpchar NULL,
	sampledescription varchar(100) NULL,
	levelbefore float8 NULL,
	levelafter float8 NULL,
	levelconst bpchar(1) NULL,
	"locked" bpchar(1) DEFAULT 'n'::bpchar NULL,
	taste varchar(50) NULL,
	createdby int4 NULL,
	userfield1 varchar(50) NULL,
	userfield2 varchar(50) NULL,
	userfield3 varchar(50) NULL,
	userfield4 varchar(50) NULL,
	userfield5 varchar(50) NULL,
	reason_id int4 NULL,
	material_id int4 NULL,
	hardness varchar(50) NULL,
	sumcations float8 NULL,
	sumanions float8 NULL,
	sumcationsceq float8 NULL,
	sumanionsceq float8 NULL,
	CONSTRAINT gwb_proben_pk PRIMARY KEY (datumid),
	CONSTRAINT gwb_proben_measdate_fk FOREIGN KEY (datumid) REFERENCES gwbase.gwb_measdate(id),
	CONSTRAINT gwb_proben_reason_fk FOREIGN KEY (reason_id) REFERENCES gwbase.gwb_samplingreasons(id)
);


-- gwbase.gwb_reportgroups_paramgroups definition

-- Drop table

-- DROP TABLE gwbase.gwb_reportgroups_paramgroups;

CREATE TABLE gwbase.gwb_reportgroups_paramgroups (
	group_id int4 NOT NULL,
	paramgroup_id int4 NOT NULL,
	CONSTRAINT gwb_reportgroups_paramgroups_pk PRIMARY KEY (group_id, paramgroup_id),
	CONSTRAINT gwb_rgpg_group_fk FOREIGN KEY (group_id) REFERENCES gwbase.gwb_reportgroups(id) ON DELETE CASCADE,
	CONSTRAINT gwb_rgpg_paramgroup_fk FOREIGN KEY (paramgroup_id) REFERENCES gwbase.gwb_stoffkl(class_id) ON DELETE CASCADE
);


-- gwbase.gwb_samples_progs definition

-- Drop table

-- DROP TABLE gwbase.gwb_samples_progs;

CREATE TABLE gwbase.gwb_samples_progs (
	sample_id int4 NOT NULL,
	prog_id int4 NOT NULL,
	seqnr int4 NULL,
	CONSTRAINT gwb_samplesprogs_pk PRIMARY KEY (sample_id, prog_id),
	CONSTRAINT gwb_samplesprogs_prog_fk FOREIGN KEY (prog_id) REFERENCES gwbase.gwb_ukat(katalognr),
	CONSTRAINT gwb_samplesprogs_sample_fk FOREIGN KEY (sample_id) REFERENCES gwbase.gwb_measdate(id)
);


-- gwbase.gwb_structparts_points definition

-- Drop table

-- DROP TABLE gwbase.gwb_structparts_points;

CREATE TABLE gwbase.gwb_structparts_points (
	structpart_id int4 NOT NULL,
	point_id int4 NOT NULL,
	nextpoint_id int4 NULL,
	prevpoint_id int4 NULL,
	CONSTRAINT gwb_sppoints_pk PRIMARY KEY (structpart_id, point_id),
	CONSTRAINT gwb_sppoints_structpart_fk FOREIGN KEY (structpart_id) REFERENCES gwbase.gwb_structparts(id),
	CONSTRAINT gwb_sppoints_structpoint_fk FOREIGN KEY (point_id) REFERENCES gwbase.gwb_structpoints(id)
);


-- gwbase.gwb_valveposition definition

-- Drop table

-- DROP TABLE gwbase.gwb_valveposition;

CREATE TABLE gwbase.gwb_valveposition (
	id int4 NOT NULL,
	valve_id int4 NOT NULL,
	date_time timestamp NULL,
	valveposition int4 NULL,
	maxvalveposition int4 NULL,
	CONSTRAINT gwb_valveposition_pk PRIMARY KEY (id),
	CONSTRAINT gwb_valveposition_valve_fk FOREIGN KEY (valve_id) REFERENCES gwbase.gwb_valves(id) ON DELETE CASCADE
);


-- gwbase.gpd_asbbohrloch definition

-- Drop table

-- DROP TABLE gwbase.gpd_asbbohrloch;

CREATE TABLE gwbase.gpd_asbbohrloch (
	msid int4 NOT NULL,
	recid int4 NOT NULL,
	abl_ok float8 NULL,
	abl_uk float8 NULL,
	abl_verfahren int4 NULL,
	abl_werkzeug int4 NULL,
	abl_durchmesser int4 NULL,
	abl_durchmzoll varchar(12) NULL,
	abl_notiz varchar(254) NULL,
	CONSTRAINT gpd_asbbohrloch_pk PRIMARY KEY (msid, recid),
	CONSTRAINT fk_gpd_asbbohrloch_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gpd_asbsonderbau definition

-- Drop table

-- DROP TABLE gwbase.gpd_asbsonderbau;

CREATE TABLE gwbase.gpd_asbsonderbau (
	msid int4 NOT NULL,
	recid int4 NOT NULL,
	ase_ok float8 NULL,
	ase_uk float8 NULL,
	ase_typ int4 NULL,
	ase_durchmesser int4 NULL,
	ase_wandstaerke float8 NULL,
	ase_bemerkung varchar(80) NULL,
	ase_durchmzoll varchar(12) NULL,
	ase_linewidth int4 NULL,
	CONSTRAINT gpd_asbsonderbau_pk PRIMARY KEY (msid, recid),
	CONSTRAINT fk_gpd_asbsonderbau_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gpd_asbverfuellung definition

-- Drop table

-- DROP TABLE gwbase.gpd_asbverfuellung;

CREATE TABLE gwbase.gpd_asbverfuellung (
	msid int4 NOT NULL,
	recid int4 NOT NULL,
	avf_material int4 NULL,
	avf_ok float8 NULL,
	avf_uk float8 NULL,
	avf_durchmesser int4 NULL,
	avf_korngroessemin float8 NULL,
	avf_korngroessemax float8 NULL,
	avf_bemerkung varchar(80) NULL,
	avf_durchmzoll varchar(12) NULL,
	avf_teilverfuellung varchar(1) NULL,
	CONSTRAINT gpd_asbverfuellung_pk PRIMARY KEY (msid, recid),
	CONSTRAINT fk_gpd_asbverfuellung_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gpd_asbverrohrung definition

-- Drop table

-- DROP TABLE gwbase.gpd_asbverrohrung;

CREATE TABLE gwbase.gpd_asbverrohrung (
	msid int4 NOT NULL,
	recid int4 NOT NULL,
	aro_tourid int4 NULL,
	aro_ok float8 NULL,
	aro_uk float8 NULL,
	aro_typ int4 NULL,
	aro_material int4 NULL,
	aro_wandstaerke float8 NULL,
	aro_bemerkung varchar(80) NULL,
	aro_durchmesser int4 NULL,
	aro_durchmzoll varchar(12) NULL,
	aro_displaypos int4 NULL,
	aro_ignorefilter varchar(1) NULL,
	aro_fueruebersicht varchar(1) NULL,
	aro_linewidth int4 NULL,
	CONSTRAINT gpd_asbverrohrung_pk PRIMARY KEY (msid, recid),
	CONSTRAINT fk_gpd_asbverrohrung_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gpd_hydroparam definition

-- Drop table

-- DROP TABLE gwbase.gpd_hydroparam;

CREATE TABLE gwbase.gpd_hydroparam (
	msid int4 NOT NULL,
	gwl float8 NULL,
	t_rep float8 NULL,
	t_sim float8 NULL,
	kf_sim float8 NULL,
	s_sim float8 NULL,
	p_sim varchar(255) NULL,
	b_sim varchar(255) NULL,
	pv_lit varchar(50) NULL,
	t_lit float8 NULL,
	kf_lit float8 NULL,
	s_lit float8 NULL,
	p_lit varchar(255) NULL,
	dquelle varchar(255) NULL,
	wsp_fis float8 NULL,
	datum_fis timestamp NULL,
	kf_fis float8 NULL,
	t_fis float8 NULL,
	b_fis varchar(255) NULL,
	s_fis float8 NULL,
	e_fis float8 NULL,
	t_logan float8 NULL,
	e_logan float8 NULL,
	q_log float8 NULL,
	q_log2 float8 NULL,
	s_log float8 NULL,
	wsp_r_log float8 NULL,
	wsp_a_log float8 NULL,
	beharr_log varchar(50) NULL,
	b_log varchar(255) NULL,
	CONSTRAINT gpd_hydroparam_pk PRIMARY KEY (msid),
	CONSTRAINT fk_gpd_hydroparam_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gpd_kluefte definition

-- Drop table

-- DROP TABLE gwbase.gpd_kluefte;

CREATE TABLE gwbase.gpd_kluefte (
	msid int4 NOT NULL,
	recid int4 NOT NULL,
	kl_anfang float8 NULL,
	kl_ende float8 NULL,
	kl_neigung int4 NULL,
	kl_art int4 NULL,
	kl_bemerkung varchar(255) NULL,
	CONSTRAINT gpd_kluefte_pk PRIMARY KEY (msid, recid),
	CONSTRAINT fk_gpd_kluefte_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gpd_log definition

-- Drop table

-- DROP TABLE gwbase.gpd_log;

CREATE TABLE gwbase.gpd_log (
	msid int4 NOT NULL,
	recid int4 NOT NULL,
	lg_name varchar(40) NULL,
	lg_firma int4 NULL,
	lg_datum timestamp NULL,
	lg_anfang float8 NULL,
	lg_ende float8 NULL,
	lg_bezug int4 NULL,
	lg_notitz varchar(254) NULL,
	lg_ergebnis varchar(254) NULL,
	lg_methode int4 NULL,
	lg_unit int4 NULL,
	CONSTRAINT gpd_log_pk PRIMARY KEY (msid, recid),
	CONSTRAINT fk_gpd_log_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gpd_logdata definition

-- Drop table

-- DROP TABLE gwbase.gpd_logdata;

CREATE TABLE gwbase.gpd_logdata (
	msid int4 NOT NULL,
	ld_logrecid int4 NOT NULL,
	ld_recid int4 NOT NULL,
	ld_datumzeit timestamp NULL,
	ld_depth float8 NULL,
	ld_value float8 NULL,
	ld_unit int4 NULL,
	CONSTRAINT gpd_logdata_pk PRIMARY KEY (msid, ld_logrecid, ld_recid),
	CONSTRAINT fk_gpd_logdata_1 FOREIGN KEY (msid,ld_logrecid) REFERENCES gwbase.gpd_log(msid,recid) ON DELETE CASCADE
);


-- gwbase.gpd_mspassconfig definition

-- Drop table

-- DROP TABLE gwbase.gpd_mspassconfig;

CREATE TABLE gwbase.gpd_mspassconfig (
	msid int4 NOT NULL,
	config text NULL,
	CONSTRAINT gpd_mspassconfig_pk PRIMARY KEY (msid),
	CONSTRAINT fk_gpd_mspassconfig_1 FOREIGN KEY (msid) REFERENCES gwbase.gpd_msstamm(msid) ON DELETE CASCADE
);


-- gwbase.gwb_analysen definition

-- Drop table

-- DROP TABLE gwbase.gwb_analysen;

CREATE TABLE gwbase.gwb_analysen (
	datumid int4 NOT NULL,
	parameterid int4 NOT NULL,
	messwert float8 NULL,
	einheit varchar(10) NULL,
	zusatz bpchar(1) DEFAULT '0'::bpchar NULL,
	"locked" bpchar(1) DEFAULT 'n'::bpchar NULL,
	method_id int4 NULL,
	valuestdunit float8 NULL,
	stdunit varchar(10) NULL,
	iscalculated varchar(1) DEFAULT 'n'::character varying NULL,
	"datatype" int4 DEFAULT 0 NULL,
	CONSTRAINT gwb_analysen_pk PRIMARY KEY (datumid, parameterid),
	CONSTRAINT gwb_analy_methods_fk FOREIGN KEY (method_id) REFERENCES gwbase.gwb_analymethods(id),
	CONSTRAINT gwb_analysen_measdate_fk FOREIGN KEY (datumid) REFERENCES gwbase.gwb_measdate(id),
	CONSTRAINT gwb_analysen_parameter_fk FOREIGN KEY (parameterid) REFERENCES gwbase.gwb_paramet(id)
);


-- gwbase.gpd_asbfilter definition

-- Drop table

-- DROP TABLE gwbase.gpd_asbfilter;

CREATE TABLE gwbase.gpd_asbfilter (
	msid int4 NOT NULL,
	afi_rohrrecid int4 NOT NULL,
	afi_schlitzweite float8 NULL,
	afi_pos int4 NULL,
	afi_hydgeoleinheit int4 NULL,
	afi_gwltyp int4 NULL,
	afi_hydraulik int4 NULL,
	afi_gwstockwerk int4 NULL,
	afi_bemerkung varchar(255) NULL,
	CONSTRAINT gpd_asbfilter_pk PRIMARY KEY (msid, afi_rohrrecid),
	CONSTRAINT fk_gpd_asbfilter_1 FOREIGN KEY (msid,afi_rohrrecid) REFERENCES gwbase.gpd_asbverrohrung(msid,recid) ON DELETE CASCADE
);