BEGIN;


--Add new column wsts.sinfo_selection

ALTER TABLE wsts ADD COLUMN sinfo_selection VARCHAR(1) CHECK((sinfo_selection IS NULL) OR (sinfo_selection IN ('W','Q')));
COMMENT ON COLUMN wsts.sinfo_selection IS 'Whether and how the WST series is selected within S-INFO calculation types ("W"=without-Q-group, "Q"=with-Q-group, NULL=not-selectable)' ;


--Add new columns bed_height.sounding_width_info and bed_height.comment

ALTER TABLE bed_height
  ADD sounding_width_info VARCHAR(256),
  ADD notes VARCHAR(256);
COMMENT ON COLUMN bed_height.sounding_width_info IS 'File header line info "ausgewertete Peilbreite"' ;
COMMENT ON COLUMN bed_height.notes IS 'File header line info "weitere Bemerkungen"' ;


--Add new columns bed_height_values.min_height (Talweg) and bed_height_values.max_height (Kammlage) (same type as existing height column)

ALTER TABLE bed_height_values
  ADD min_height NUMERIC,
  ADD max_height NUMERIC,
  ADD height01 NUMERIC,
  ADD height02 NUMERIC,
  ADD height03 NUMERIC,
  ADD height04 NUMERIC,
  ADD height05 NUMERIC,
  ADD height06 NUMERIC,
  ADD height07 NUMERIC,
  ADD height08 NUMERIC,
  ADD height09 NUMERIC,
  ADD height10 NUMERIC;
COMMENT ON COLUMN bed_height_values.min_height IS 'Minimum bed height (Talweg) in m' ;
COMMENT ON COLUMN bed_height_values.max_height IS 'Maximum bed height (Kammlage) in m' ;
COMMENT ON COLUMN bed_height_values.height01 IS 'Bed height of section 1 in m' ;
COMMENT ON COLUMN bed_height_values.height02 IS 'Bed height of section 2 in m' ;
COMMENT ON COLUMN bed_height_values.height03 IS 'Bed height of section 3 in m' ;
COMMENT ON COLUMN bed_height_values.height04 IS 'Bed height of section 4 in m' ;
COMMENT ON COLUMN bed_height_values.height05 IS 'Bed height of section 5 in m' ;
COMMENT ON COLUMN bed_height_values.height06 IS 'Bed height of section 6 in m' ;
COMMENT ON COLUMN bed_height_values.height07 IS 'Bed height of section 7 in m' ;
COMMENT ON COLUMN bed_height_values.height08 IS 'Bed height of section 8 in m' ;
COMMENT ON COLUMN bed_height_values.height09 IS 'Bed height of section 9 in m' ;
COMMENT ON COLUMN bed_height_values.height10 IS 'Bed height of section 10 in m' ;


--Bed mobility

CREATE TABLE bed_mobility (
    id  NUMERIC(9,0) PRIMARY KEY,
    river_id  integer NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE,
    kmrange_info  VARCHAR(32),
    filename  VARCHAR(256) NOT NULL,
    notes  VARCHAR(256)
);
COMMENT ON TABLE bed_mobility IS 'Longitudinal section of the bed mobility of a river' ;
COMMENT ON COLUMN bed_mobility.kmrange_info IS 'File header line info "Strecke"' ;
COMMENT ON COLUMN bed_mobility.filename IS 'Name without type extension of the imported file' ;
COMMENT ON COLUMN bed_mobility.notes IS 'File header line info "weitere Bemerkungen"' ;

CREATE SEQUENCE BED_MOBILITY_ID_SEQ ;


CREATE TABLE bed_mobility_values (
    id  NUMERIC(9,0) PRIMARY KEY,
    bed_mobility_id  NUMERIC(9,0) NOT NULL CONSTRAINT cBedMobilityValuesBedMobility REFERENCES bed_mobility(id) ON DELETE CASCADE,
    station  NUMERIC(7,3) NOT NULL,
    moving  NUMERIC(1,0) DEFAULT 0 NOT NULL CHECK(moving IN (0,1))
);
COMMENT ON TABLE bed_mobility_values IS 'Bed mobility of a km' ;
COMMENT ON COLUMN bed_mobility_values.station IS 'River km' ;
COMMENT ON COLUMN bed_mobility_values.moving IS 'Whether the river bed at the station is moving' ;

CREATE SEQUENCE BED_MOBILITY_VALUES_ID_SEQ ;


--Infrastructure

CREATE TABLE infrastructure (
    id  NUMERIC(9,0) PRIMARY KEY,
    river_id  integer NOT NULL CONSTRAINT cInfrastructureRivers REFERENCES rivers(id) ON DELETE CASCADE,
    annotation_type_id  integer NOT NULL CONSTRAINT cInfrastructureAnnotationType REFERENCES annotation_types(id),
    group_id  integer NOT NULL CONSTRAINT cInfrastructureGroupAnnoType REFERENCES annotation_types(id),
    year  NUMERIC(4,0) CHECK((year >= 1700) AND (year <= 2199)),
    dataprovider  VARCHAR(256),
    evaluation_by  VARCHAR(256),
    kmrange_info  VARCHAR(32),
    filename  VARCHAR(256) NOT NULL,
    notes  VARCHAR(256)
);
COMMENT ON TABLE infrastructure IS 'Longitudinal section of infrastructures of a river and a type' ;
COMMENT ON COLUMN infrastructure.group_id IS 'Reference to the infrastructure type group';
COMMENT ON COLUMN infrastructure.year IS 'File header line info "Stand"' ;
COMMENT ON COLUMN infrastructure.dataprovider IS 'File header line info "Datenherkunft"' ;
COMMENT ON COLUMN infrastructure.evaluation_by IS 'File header line info "Auswerter"' ;
COMMENT ON COLUMN infrastructure.kmrange_info IS 'File header line info "Strecke"' ;
COMMENT ON COLUMN infrastructure.filename IS 'Name without type extension of the imported file' ;
COMMENT ON COLUMN infrastructure.notes IS 'File header line info "weitere Bemerkungen"' ;

CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ;


CREATE TABLE infrastructure_values (
    id  NUMERIC(9,0) PRIMARY KEY,
    infrastructure_id  NUMERIC(9,0) CONSTRAINT cInfrastructureValuesInfrastructure REFERENCES infrastructure(id) ON DELETE CASCADE,
    station  NUMERIC(7,3) NOT NULL,
    attribute_id  integer CONSTRAINT cInfrastructureValuesAttributes REFERENCES attributes(id),
    height  NUMERIC(6,2)
);
COMMENT ON TABLE infrastructure_values IS 'Infrastructure at a river station' ;
COMMENT ON COLUMN infrastructure_values.station IS 'River km' ;
COMMENT ON COLUMN infrastructure_values.height IS 'Geodetic height of the top of the infrastructure in m' ;

CREATE SEQUENCE INFRASTRUCTURE_VALUES_ID_SEQ ;


--Channel

CREATE TABLE channel (
    id  NUMERIC(9,0) PRIMARY KEY,
    river_id  integer NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE,
    kmrange_info  VARCHAR(32),
    filename  VARCHAR(256) NOT NULL,
    notes  VARCHAR(256),
    year_from  NUMERIC(4,0),
    year_to  NUMERIC(4,0)
);
COMMENT ON TABLE channel IS 'Longitudinal section of the navigable channel of a river' ;
COMMENT ON COLUMN channel.kmrange_info IS 'File header line info "Strecke"' ;
COMMENT ON COLUMN channel.filename IS 'Name without type extension of the imported file' ;
COMMENT ON COLUMN channel.notes IS 'File header line info "weitere Bemerkungen"' ;
COMMENT ON COLUMN channel.year_from IS 'Start year of the period for which the channel values are valid, NULL when valid for the whole past';
COMMENT ON COLUMN channel.year_to IS 'End year of the period for which the channel values are valid, NULL when unlimited';


CREATE SEQUENCE CHANNEL_ID_SEQ ;


CREATE TABLE channel_values (
    id  NUMERIC(9,0) PRIMARY KEY,
    channel_id  NUMERIC(9,0) CONSTRAINT cChannelValuesChannel REFERENCES channel(id) ON DELETE CASCADE,
    station  NUMERIC(7,3) NOT NULL,
    width  NUMERIC(6,2),
    depth  NUMERIC(6,2)
);
COMMENT ON TABLE channel_values IS 'Nominal size of the navigable channel at a river station' ;
COMMENT ON COLUMN channel_values.station IS 'River km' ;
COMMENT ON COLUMN channel_values.width IS 'Nominal width of the channel in m' ;
COMMENT ON COLUMN channel_values.depth IS 'Nominal depth of the channel in m' ;

CREATE SEQUENCE CHANNEL_VALUES_ID_SEQ ;


--Collision

CREATE TABLE collision_type (
    id  NUMERIC(9,0) PRIMARY KEY,
    name  VARCHAR(64) NOT NULL UNIQUE
);
COMMENT ON TABLE collision_type IS 'Type of a ship collision' ;
COMMENT ON COLUMN collision_type.name IS 'Name of the collision type' ;

CREATE SEQUENCE COLLISION_TYPE_ID_SEQ ;


CREATE TABLE collision (
    id  NUMERIC(9,0) PRIMARY KEY,
    river_id  integer NOT NULL CONSTRAINT cCollisionRivers REFERENCES rivers(id) ON DELETE CASCADE,
    year  NUMERIC(4,0) NOT NULL,
    kmrange_info  VARCHAR(32),
    filename  VARCHAR(256) NOT NULL,
    notes  VARCHAR(256)
);
COMMENT ON TABLE collision IS 'Longitudinal section of ship collisions with the river bed' ;
COMMENT ON COLUMN collision.year IS 'File header line info "Jahr"' ;
COMMENT ON COLUMN collision.kmrange_info IS 'File header line info "Strecke"' ;
COMMENT ON COLUMN collision.filename IS 'Name without type extension of the imported file' ;
COMMENT ON COLUMN collision.notes IS 'File header line info "weitere Bemerkungen"' ;

CREATE SEQUENCE COLLISION_ID_SEQ ;


CREATE TABLE collision_values (
    id  NUMERIC(9,0) PRIMARY KEY,
    collision_id  NUMERIC(9,0) NOT NULL CONSTRAINT cCollisionValueCollision REFERENCES collision(id) ON DELETE CASCADE,
    station  NUMERIC(7,3) NOT NULL,
    event_date  DATE NOT NULL,
    gauge_w  NUMERIC(6,2),
    gauge_name  VARCHAR(64),
    collision_type_id  NUMERIC(9,0) NOT NULL CONSTRAINT cCollisionValuesCollisionType REFERENCES collision_type(id)
);
COMMENT ON TABLE collision_values IS 'Collision event' ;
COMMENT ON COLUMN collision_values.station IS 'River km' ;
COMMENT ON COLUMN collision_values.event_date IS 'Date of the collision' ;
COMMENT ON COLUMN collision_values.gauge_w IS 'Waterlevel during the collision' ;
COMMENT ON COLUMN collision_values.gauge_name IS 'Name of the gauge' ;

CREATE SEQUENCE COLLISION_VALUES_ID_SEQ ;


--Tkh

CREATE TABLE tkh (
    id  NUMERIC(9,0) PRIMARY KEY,
    river_id  integer NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE,
    day  DATE,
    kmrange_info  VARCHAR(32),
    filename  VARCHAR(256) NOT NULL,
    groupname  VARCHAR(32) NOT NULL CHECK(groupname IN ('messung','berechnung')),
    sounding_info  VARCHAR(64),
    evaluation_by  VARCHAR(256),
    notes  VARCHAR(256)
);
COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkörperhöhe of a river' ;
COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ;
COMMENT ON COLUMN tkh.kmrange_info IS 'File header line info "Strecke"' ;
COMMENT ON COLUMN tkh.filename IS 'Name without type extension of the imported file' ;
COMMENT ON COLUMN tkh.groupname IS 'Group the file belongs to' ;
COMMENT ON COLUMN tkh.sounding_info IS 'File header line info "Peilung"' ;
COMMENT ON COLUMN tkh.evaluation_by IS 'File header line info "Auswerter"' ;
COMMENT ON COLUMN tkh.notes IS 'File header line info "weitere Bemerkungen"' ;

CREATE SEQUENCE TKH_ID_SEQ ;


CREATE TABLE tkh_column (
    id  NUMERIC(9,0) PRIMARY KEY,
    tkh_id  NUMERIC(9,0) NOT NULL CONSTRAINT cTkhColumnTkh REFERENCES tkh(id) ON DELETE CASCADE,
    name  VARCHAR(64) NOT NULL
);
COMMENT ON TABLE tkh_column IS 'Longitudinal section of computed Transportkörperhöhe for a waterlevel series' ;
COMMENT ON COLUMN tkh_column.name IS 'Name of the tkh computation column' ;

CREATE SEQUENCE TKH_COLUMN_ID_SEQ ;


CREATE TABLE tkh_values (
    id  NUMERIC(9,0) PRIMARY KEY,
    tkh_column_id  NUMERIC(9,0) NOT NULL CONSTRAINT cTkhValuesTkhColumn REFERENCES tkh_column(id) ON DELETE CASCADE,
    station  NUMERIC(7,3) NOT NULL,
    tkheight  NUMERIC(7,3)
);
COMMENT ON TABLE tkh_values IS 'Transportkörperhöhe of a river station and referenced file column' ;
COMMENT ON COLUMN tkh_values.station IS 'River km' ;
COMMENT ON COLUMN tkh_values.tkheight IS 'Transportkörperhöhe of a river station computed for a waterlevel in  m' ;

CREATE SEQUENCE TKH_VALUES_ID_SEQ ;


--Flow depth

CREATE TABLE flow_depth (
    id  NUMERIC(9,0) PRIMARY KEY,
    river_id  integer NOT NULL CONSTRAINT cFlowDepthRivers REFERENCES rivers(id) ON DELETE CASCADE,
    year  NUMERIC(4,0) NOT NULL,
    kmrange_info  VARCHAR(32),
    filename  VARCHAR(256) NOT NULL,
    sounding_info  VARCHAR(64),
    evaluation_by  VARCHAR(255),
    notes  VARCHAR(256)
);

COMMENT ON TABLE flow_depth IS 'Longitudinal section of computed flow depth of a river' ;
COMMENT ON COLUMN flow_depth.year IS 'File header line info "Bezugsjahr"' ;
COMMENT ON COLUMN flow_depth.kmrange_info IS 'File header line info "Strecke"' ;
COMMENT ON COLUMN flow_depth.filename IS 'Name without type extension of the imported file' ;
COMMENT ON COLUMN flow_depth.sounding_info IS 'File header line info "Peilung"' ;
COMMENT ON COLUMN flow_depth.evaluation_by IS 'File header line info "Auswerter"' ;
COMMENT ON COLUMN flow_depth.notes IS 'File header line info "weitere Bemerkungen"' ;

CREATE SEQUENCE FLOW_DEPTH_ID_SEQ ;


CREATE TABLE flow_depth_column (
    id  NUMERIC(9,0) PRIMARY KEY,
    flow_depth_id  NUMERIC(9,0) NOT NULL CONSTRAINT cFlowDepthColumnFlowDepth REFERENCES flow_depth(id) ON DELETE CASCADE,
    name  VARCHAR(64) NOT NULL
);

COMMENT ON TABLE flow_depth_column IS 'Longitudinal section of computed flow depth for a waterlevel series' ;
COMMENT ON COLUMN flow_depth_column.name IS 'Name of the flow depth computation column' ;

CREATE SEQUENCE FLOW_DEPTH_COLUMN_ID_SEQ ;


CREATE TABLE flow_depth_values (
    id  NUMERIC(9,0) PRIMARY KEY,
    flow_depth_column_id  NUMERIC(9,0) NOT NULL CONSTRAINT cFlowDepthValuesFlowDepthColumn REFERENCES flow_depth_column(id) ON DELETE CASCADE,
    station  NUMERIC(7,3) NOT NULL,
    depth  NUMERIC(7,3)
);

COMMENT ON TABLE flow_depth_values IS 'Flow depth of a river station and referenced file column' ;
COMMENT ON COLUMN flow_depth_values.station IS 'River km' ;
COMMENT ON COLUMN flow_depth_values.depth IS 'Flow depth of a river station computed for a waterlevel in  m' ;

CREATE SEQUENCE FLOW_DEPTH_VALUES_ID_SEQ ;


--Depth evolution

CREATE TABLE depth_evolution (
    id  NUMERIC(9,0) PRIMARY KEY,
    river_id  integer NOT NULL CONSTRAINT cDepthEvolutionRivers REFERENCES rivers(id) ON DELETE CASCADE,
    reference_year  NUMERIC(4,0) NOT NULL,
    start_year  NUMERIC(4,0) NOT NULL,
    curr_sounding  VARCHAR(64) NOT NULL,
    old_sounding  VARCHAR(64) NOT NULL,
    kmrange_info  VARCHAR(32),
    curr_glw  VARCHAR(64) NOT NULL,
    old_glw  VARCHAR(64) NOT NULL,
    filename  VARCHAR(256) NOT NULL,
    groupname  VARCHAR(32) NOT NULL CHECK(groupname IN ('aktuell','etappe')),
    notes  VARCHAR(256)
);
COMMENT ON TABLE depth_evolution IS 'Longitudinal section of the evolution of the flow depth of a river' ;
COMMENT ON COLUMN depth_evolution.reference_year IS 'File header line info "Bezugsjahr" (GlW)' ;
COMMENT ON COLUMN depth_evolution.start_year IS 'File header line info "Ausgangsjahr" (GlW)' ;
COMMENT ON COLUMN depth_evolution.curr_sounding IS 'File header line info "Aktuelle Peilung / Epoche"' ;
COMMENT ON COLUMN depth_evolution.old_sounding IS 'File header line info "Historische Peilung / Epoche"' ;
COMMENT ON COLUMN depth_evolution.kmrange_info IS 'File header line info "Strecke"' ;
COMMENT ON COLUMN depth_evolution.curr_glw IS 'File header line info "Aktuelle Wasserspiegellage"' ;
COMMENT ON COLUMN depth_evolution.old_glw IS 'File header line info "Historische Wasserspiegellage"' ;
COMMENT ON COLUMN depth_evolution.filename IS 'Name without type extension of the imported file' ;
COMMENT ON COLUMN depth_evolution.groupname IS 'Group the file belongs to' ;
COMMENT ON COLUMN depth_evolution.notes IS 'File header line info "weitere Bemerkungen"' ;

CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ;


CREATE TABLE depth_evolution_values (
    id  NUMERIC(9,0) PRIMARY KEY,
    depth_evolution_id  NUMERIC(9,0) NOT NULL CONSTRAINT cDepthEvolutionValuesDepthEvolution REFERENCES depth_evolution(id) ON DELETE CASCADE,
    station  NUMERIC(7,3) NOT NULL,
    total_change  NUMERIC(8,4),
    change_per_year  NUMERIC(8,4)
);
COMMENT ON TABLE depth_evolution_values IS 'Evolution of the flow depth of a river station in a referenced period of time' ;
COMMENT ON COLUMN depth_evolution_values.station IS 'River km' ;
COMMENT ON COLUMN depth_evolution_values.total_change IS 'Flow depth change of the whole period of time in m' ;
COMMENT ON COLUMN depth_evolution_values.change_per_year IS 'Average yearly flow depth change in the referenced period of time in m' ;

CREATE SEQUENCE DEPTH_EVOLUTION_VALUES_ID_SEQ ;


--Daily discharge

CREATE TABLE daily_discharge (
    id  NUMERIC(9,0) PRIMARY KEY,
    gauge_id  integer NOT NULL CONSTRAINT cDailyDischargeGauge REFERENCES gauges(id) ON DELETE CASCADE,
    filename  VARCHAR(256) NOT NULL
);

COMMENT ON TABLE daily_discharge IS 'Daily discharge value series' ;
COMMENT ON COLUMN daily_discharge.filename IS 'Name without type extension of the imported file' ;

CREATE SEQUENCE DAILY_DISCHARGE_ID_SEQ ;


CREATE TABLE daily_discharge_values (
    id  NUMERIC(9,0) PRIMARY KEY,
    daily_discharge_id  NUMERIC(9,0) NOT NULL CONSTRAINT cDailyDischargeValueDailyDischarge REFERENCES daily_discharge(id) ON DELETE CASCADE,
    day  DATE NOT NULL,
    discharge  NUMERIC(8,3) NOT NULL
);

COMMENT ON TABLE daily_discharge_values IS 'Mean daily discharge of a date and a gauge' ;
COMMENT ON COLUMN daily_discharge_values.day IS 'Date of the discharge value' ;
COMMENT ON COLUMN daily_discharge_values.discharge IS 'Mean daily discharge in m^3/s' ;

CREATE SEQUENCE DAILY_DISCHARGE_VALUES_ID_SEQ ;


--Salix line

CREATE TABLE salix (
    id  NUMERIC(9,0) PRIMARY KEY,
    river_id  integer NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE,
    evaluation_by  VARCHAR(255),
    kmrange_info  VARCHAR(32),
    filename  VARCHAR(256) NOT NULL,
    notes  VARCHAR(256)
);

COMMENT ON TABLE salix IS 'Longitudinal section of the salix line parameters of a river' ;
COMMENT ON COLUMN salix.evaluation_by IS 'File header line info "Auswerter"' ;
COMMENT ON COLUMN salix.kmrange_info IS 'File header line info "Strecke"' ;
COMMENT ON COLUMN salix.filename IS 'Name without type extension of the imported file' ;
COMMENT ON COLUMN salix.notes IS 'File header line info "weitere Bemerkungen"' ;

CREATE SEQUENCE SALIX_ID_SEQ ;


CREATE TABLE salix_values (
    id  NUMERIC(9,0) PRIMARY KEY,
    salix_id  NUMERIC(9,0) NOT NULL CONSTRAINT cSalixValuesSalix REFERENCES salix(id) ON DELETE CASCADE,
    station  NUMERIC(7,3) NOT NULL,
    factor  NUMERIC(6,2) NOT NULL,
    mnw_mw_diff  NUMERIC(6,2)
);

COMMENT ON COLUMN salix_values.station IS 'River km' ;
COMMENT ON COLUMN salix_values.factor IS 'Salix "factor" of the station in m' ;
COMMENT ON COLUMN salix_values.mnw_mw_diff IS 'Difference between MNW and MW in m (less than zero)' ;

CREATE SEQUENCE SALIX_VALUES_ID_SEQ ;


CREATE TABLE salix_rank (
    id  NUMERIC(9,0) PRIMARY KEY,
    min_value  NUMERIC(6,2),
    max_value  NUMERIC(6,2),
    name  VARCHAR(16) NOT NULL
);
INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (1, NULL, -0.3, 'invalid');
INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (2, -0.3, 0.3, 'very good');
INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (3, 0.3, 0.5, 'good');
INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (4, 0.5, 1.0, 'moderate');
INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (5, 1.0, 1.5, 'bad');
INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (6, 1.5, NULL, 'very bad');


--Vegetation zone

CREATE TABLE vegetation_type (
    id  NUMERIC(9,0) PRIMARY KEY,
    name  VARCHAR(256) NOT NULL
);
INSERT INTO vegetation_type (id, name) VALUES (1, 'zonal forest');
INSERT INTO vegetation_type (id, name) VALUES (2, 'dry hartwood forest floodplain');
INSERT INTO vegetation_type (id, name) VALUES (3, 'wet hartwood forest floodplain');
INSERT INTO vegetation_type (id, name) VALUES (4, 'salix alba forest');
INSERT INTO vegetation_type (id, name) VALUES (5, 'salix shrubs');
INSERT INTO vegetation_type (id, name) VALUES (6, 'reed bed');
INSERT INTO vegetation_type (id, name) VALUES (7, 'bank pioneers');
INSERT INTO vegetation_type (id, name) VALUES (8, 'no vegetation');
INSERT INTO vegetation_type (id, name) VALUES (9, 'water');


CREATE TABLE vegetation (
    id  NUMERIC(9,0) PRIMARY KEY,
    river_id  integer NOT NULL CONSTRAINT cVegetationRivers REFERENCES rivers(id) ON DELETE CASCADE,
    name  VARCHAR(256),
    filename  VARCHAR(256) NOT NULL,
    notes  VARCHAR(256)
);

COMMENT ON TABLE vegetation IS 'Classification of the vegetation of a river' ;
COMMENT ON COLUMN vegetation.name IS 'File header line info "Einteilung"' ;
COMMENT ON COLUMN vegetation.filename IS 'Name without type extension of the imported file' ;
COMMENT ON COLUMN vegetation.notes IS 'File header line info "weitere Bemerkungen"' ;

CREATE SEQUENCE VEGETATION_ID_SEQ ;


CREATE TABLE vegetation_zone (
    id  NUMERIC(9,0) PRIMARY KEY,
    vegetation_id  NUMERIC(9,0) NOT NULL CONSTRAINT cVegetationZoneVegetation REFERENCES vegetation(id) ON DELETE CASCADE,
    vegetation_type_id  NUMERIC(9,0) NOT NULL CONSTRAINT cVegetationZoneVegetationType REFERENCES vegetation_type(id) ON DELETE CASCADE,
    min_overflow_days  NUMERIC(3,0) NOT NULL,
    max_overflow_days  NUMERIC(3,0) NOT NULL,
    color_r  NUMERIC(3,0) NOT NULL,
    color_g  NUMERIC(3,0) NOT NULL,
    color_b  NUMERIC(3,0) NOT NULL
);

COMMENT ON TABLE vegetation_zone IS 'Vegetation zone of a river station' ;
COMMENT ON COLUMN vegetation_zone.min_overflow_days IS 'Minimum number (exclusive) of overflow days in a year for the zone type' ;
COMMENT ON COLUMN vegetation_zone.max_overflow_days IS 'Maximum number (inclusive) of overflow days in a year for the zone type' ;
COMMENT ON COLUMN vegetation_zone.color_r IS 'Red value (0-255) of the zone color' ;
COMMENT ON COLUMN vegetation_zone.color_g IS 'Green value (0-255) of the zone color' ;
COMMENT ON COLUMN vegetation_zone.color_b IS 'Blue value (0-255) of the zone color' ;

CREATE SEQUENCE VEGETATION_ZONE_ID_SEQ ;


COMMIT;