------------------------------ version tables ---------------------------------

-------------------table "base1".kpi;----------------------------------------------------------
CREATE TABLE IF NOT EXISTS "base1".kpi
(
    id serial,
    tsup_mean_ep numeric,
    tsup_max_ep numeric,
    tsup_min_ep numeric,
    tret_mean_ep numeric,
    tret_max_ep numeric,
    tret_min_ep numeric,
    qsup_heat_ep numeric,
    qsup_cold_ep numeric,
    qsup_ep numeric,
    tsup_mean_c numeric,
    tsup_max_c numeric,
    tsup_min_c numeric,
    tret_mean_c numeric,
    tret_max_c numeric,
    tret_min_c numeric,
    qsup_heat_c numeric,
    qsup_cold_c numeric,
    qsup_c numeric,
    qamb numeric,
    CONSTRAINT kpi_pkey PRIMARY KEY (id)
);

-------------------table "base1".boreholes;----------------------------------------------------------
DROP TABLE IF EXISTS "base1".boreholes CASCADE;
CREATE TABLE "base1".boreholes(
    id serial,
    geom GEOMETRY (POINTZ,25832),
    plant_id int DEFAULT 1,
    "group" int DEFAULT 1,
    mir boolean DEFAULT FALSE,
    CONSTRAINT boreholes_pkey PRIMARY KEY (id)
);

-------------------table "base1".borehole_fields;----------------------------------------------------------
DROP TABLE IF EXISTS "base1".borehole_fields CASCADE;
CREATE TABLE "base1".borehole_fields(
    id serial,
    ep_id integer,
    zhole numeric CHECK (zhole > 0),
    rhole numeric CHECK (rhole > 0),
    rb numeric CHECK (rb >= 0), 
    rpipeearth numeric CHECK (rpipeearth > 0), 
    rpipegrout numeric CHECK (rpipegrout > 0), 
    rringearth numeric CHECK (rringearth > 0), 
    rgroutearth numeric CHECK (rgroutearth > 0), 
    rgroutgrout numeric CHECK (rgroutgrout > 0), 
    mir int,
    rmax numeric CHECK (rmax > 0),
    nring int CHECK (nring > 0),
    nzhole int CHECK (nzhole > 0),
    nlayt int CHECK (nlayt > 0),
    n1 int CHECK (n1 >= 0),
    n2 int CHECK (n2 >= 0),
    n3 int CHECK (n3 >= 0),
    toutput numeric CHECK (toutput >= 0),
    cpgrd numeric CHECK (cpgrd > 0),
    lambgrd numeric CHECK (lambgrd > 0),
    rhogrd numeric CHECK (rhogrd > 0),
    cpgrout numeric CHECK (cpgrout > 0),
    lambgrout numeric CHECK (lambgrout > 0),
    rhogrout numeric CHECK (rhogrout > 0),
    rpipe numeric CHECK (rpipe > 0),
    thickpipe numeric CHECK (thickpipe > 0),
    cppipe numeric CHECK (cppipe > 0),
    lambpipe numeric CHECK (lambpipe > 0),    
    lcasting numeric CHECK (lcasting > 0),
    lambda numeric CHECK (lambda > 0),
    rhosurface numeric CHECK (rhosurface > 0),
    cpsurface numeric CHECK (cpsurface > 0),  
    liqtype int,
    tfreeze numeric,
    lambliq numeric CHECK (lambliq > 0),
    tmean numeric,
    geotgrad numeric,
    CONSTRAINT borehole_fields_pkey PRIMARY KEY (id)
);

-------------------table "base1".climate;------------------------------------
DROP TABLE IF EXISTS "base1".climate CASCADE;
CREATE TABLE "base1".climate
(
  id serial,
  name text DEFAULT 'Graz',
  file_name text DEFAULT 'C:\Program Files (x86)\IDA districts\climate\BROMMA.PRN',
  latitude numeric DEFAULT 59.366,
  longitude numeric DEFAULT -17.9999,
  timezone integer DEFAULT -1,
  height numeric DEFAULT 27,
  CONSTRAINT climate_pkey PRIMARY KEY (id)
);

-------------------table "base1".submodels;------------------------------------
DROP TABLE IF EXISTS "base1".submodels CASCADE;
CREATE TABLE "base1".submodels
(
  id serial,
  submodel text,
  geom geometry(MultiPolygon,25832),
  CONSTRAINT submodel_pkey PRIMARY KEY (id)
);

-------------------table "base1".network;------------------------------------
DROP TABLE IF EXISTS "base1".network CASCADE;
CREATE TABLE "base1".network
(
  id serial,
  description text,
  CONSTRAINT network_pkey PRIMARY KEY (id)
);

-------------------table "base1".customers;------------------------------------
DROP TABLE IF EXISTS "base1".customers CASCADE;
CREATE TABLE "base1".customers
(
	id serial,
	geom geometry(PointZ,25832),
	template integer DEFAULT 1,
    network integer[] DEFAULT ARRAY[1],
	submodel integer DEFAULT 1, --reference to submodelname in subnet_line 
	CONSTRAINT customers_pkey PRIMARY KEY (id)
);


-------------------table "base1".energy_plants;------------------------------------
DROP TABLE IF EXISTS "base1".energy_plants CASCADE;
CREATE TABLE "base1".energy_plants
(
	id serial,
	geom geometry(PointZ,25832),
	template integer DEFAULT 1,
    network integer[] DEFAULT ARRAY[1],
	submodel integer DEFAULT 1, --reference to submodelname in subnet_line 
	CONSTRAINT energy_plants_pkey PRIMARY KEY (id)
);


-------------------table "base1".lines;------------------------------------
DROP TABLE IF EXISTS "base1".lines CASCADE;
CREATE TABLE "base1".lines
(
	id serial,
	geom geometry(LineStringZ,25832),
    network integer DEFAULT 1,
	type integer DEFAULT 0,
    pipe_bundle_type_id integer DEFAULT 1,
    zeta numeric DEFAULT 0,
	submodel integer[] DEFAULT ARRAY[1],
	CONSTRAINT lines_pkey PRIMARY KEY (id)
);

-------------------table "base1".junctions------------------------------------
DROP TABLE IF EXISTS "base1".junctions CASCADE;
CREATE TABLE "base1".junctions
(
	id serial,
	geom geometry(PointZ,25832),
	type integer,
    n_connections integer check(n_connections >= 1),
	submodel integer DEFAULT 1, --reference to submodelname in subnet_line 
    network integer DEFAULT 1, --reference to submodelname in subnet_line 
    zeta numeric DEFAULT 0,
	CONSTRAINT junctions_pkey PRIMARY KEY (id)
);

-- Table: "base1".buildings
-- DROP TABLE "base1".buildings;
CREATE TABLE "base1".buildings
(
  id serial,
  b_id integer,
  z_id integer,
  substation_id integer,
  submodel integer DEFAULT 1,
  geom geometry(MultiPolygon,25832),
  z_bh_m numeric,
  z_height_m numeric,
  CONSTRAINT buildings_pkey PRIMARY KEY (id)
);

-- Table: "base1".junction_connections
-- DROP TABLE "base1".junction_connections;
CREATE TABLE "base1".junction_connections
(
  id serial,
  jid integer,
  lid integer,
  CONSTRAINT junction_connections_pkey PRIMARY KEY (id)
);

-- Table: "base1".customer_connections
-- DROP TABLE "base1".customer_connections;
CREATE TABLE "base1".customer_connections
(
  id serial,
  cid integer,
  c_seq integer,
  lid integer,
  CONSTRAINT customer_connections_pkey PRIMARY KEY (id)
);
  
-- Table: "base1".energy_plant_connections
-- DROP TABLE "base1".energy_plant_connections;
CREATE TABLE "base1".energy_plant_connections
(
  id serial,
  epid integer,
  ep_seq integer,
  lid integer,
  CONSTRAINT energy_plant_connections_pkey PRIMARY KEY (id)
);

-- Table: "base1".pipes_model
-- DROP TABLE "base1".pipes_model;
CREATE TABLE "base1".pipes_model
(
  id serial,
  sim_model integer,
  pid integer,
  co_sim integer,
  CONSTRAINT id_pkey PRIMARY KEY (id)
);

-- Table: "base1".segment_lines_00
-- DROP TABLE "base1".segment_lines_00;
CREATE TABLE "base1".segment_lines_00
(
  lines_id integer,
  rc_split_multi geometry
);

-- Index: "base1".segment_lines_00_rc_split_multi_idx
-- DROP INDEX "base1".segment_lines_00_rc_split_multi_idx;
CREATE INDEX segment_lines_00_rc_split_multi_idx
  ON "base1".segment_lines_00
  USING gist
  (rc_split_multi);

-- Table: "base1".streets
-- DROP TABLE "base1".streets;
CREATE TABLE "base1".streets
(
  id serial,
  geom geometry(LineString,25832),
  costs_eur7m numeric DEFAULT 100,
  source integer,
  target integer,
  CONSTRAINT streets_pkey PRIMARY KEY (id)
);

	
-- Table: "base1".time_manager_tair
-- DROP TABLE "base1".time_manager_tair;
CREATE TABLE "base1".time_manager_tair
(
  id serial,
  b_id serial,
  the_geom geometry(MultiPolygon,25832),
  time_h text,
  time_stamp timestamp without time zone,
  t_air_c numeric,
  CONSTRAINT time_manager_tair_pkey PRIMARY KEY (id)
);

-- Table: "base1".invoked_sf
-- DROP TABLE IF EXISTS "base1".invoked_sf;
CREATE TABLE IF NOT EXISTS "base1".invoked_sf
(
    id serial,
    sf text,
    type text,
    vars text[],
    CONSTRAINT invoked_sf_pkey PRIMARY KEY (id)
);