BEGIN;
-- ANNOTATION_TYPES
CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;

CREATE TABLE annotation_types (
    id              NUMBER(38,0) NOT NULL, 
    name            VARCHAR2(255),
    PRIMARY KEY     (id)
);

CREATE OR REPLACE TRIGGER annotation_types_trigger BEFORE INSERT ON annotation_types FOR each ROW
    BEGIN
        SELECT ANNOTATION_TYPES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

-- ANNOTATIONS
CREATE SEQUENCE ANNOTATIONS_ID_SEQ;

CREATE TABLE annotations (
    id              NUMBER(38,0) NOT NULL, 
    attribute_id    NUMBER(38,0),
    edge_id         NUMBER(38,0),
    position_id     NUMBER(38,0),
    range_id        NUMBER(38,0),
    type_id         NUMBER(38,0),
    PRIMARY KEY     (id)
);

CREATE OR REPLACE TRIGGER annotations_trigger BEFORE INSERT ON annotations FOR each ROW
    BEGIN
        SELECT ANNOTATIONS_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE annotations ADD CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES ranges;
ALTER TABLE annotations ADD CONSTRAINT constraint_edges FOREIGN KEY (edge_id) REFERENCES edges;
ALTER TABLE annotations ADD CONSTRAINT constraint_positions FOREIGN KEY (position_id) REFERENCES positions;
ALTER TABLE annotations ADD CONSTRAINT constraint_attributes FOREIGN KEY (attribute_id) REFERENCES attributes;
ALTER TABLE annotations ADD CONSTRAINT constraint_types FOREIGN KEY (type_id) REFERENCES annotation_types;

-- ATTRIBUTES 
CREATE SEQUENCE ATTRIBUTES_ID_SEQ;

CREATE TABLE attributes (
    id              NUMBER(38,0) NOT NULL, 
    value           VARCHAR2(255), 
    primary key     (id)
);

CREATE OR REPLACE TRIGGER attributes_trigger BEFORE INSERT ON attributes FOR each ROW
    BEGIN
        SELECT ATTRIBUTES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

-- CROSS_SECTION_LINES
CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;

CREATE TABLE cross_section_lines (
    id                  NUMBER(38,0) NOT NULL,
    km                  NUMBER(38,2),
    cross_section_id    NUMBER(38,0), 
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER cross_section_lines_trigger BEFORE INSERT ON cross_section_lines FOR each ROW
    BEGIN
        SELECT CROSS_SECTION_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE cross_section_lines ADD CONSTRAINT constraint_cross_sections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;

-- CROSS_SECTION_POINTS
CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;

CREATE TABLE cross_section_points (
    id                      NUMBER(38,0) NOT NULL,
    col_pos                 NUMBER(38,0),
    x                       NUMBER(38,2),
    y                       NUMBER(38,2),
    cross_section_line_id   NUMBER(10,0),
    PRIMARY KEY             (id)
);

CREATE OR REPLACE TRIGGER cross_section_points_trigger BEFORE INSERT ON cross_section_points FOR each ROW
    BEGIN
        SELECT CROSS_SECTION_POINTS_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE cross_section_points ADD CONSTRAINT constraint_cross_section_lines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;

-- CROSS_SECTIONS
create sequence CROSS_SECTIONS_ID_SEQ;

create table cross_sections (
    id                  NUMBER(38,0) NOT NULL,
    description         VARCHAR2(255),
    river_id            NUMBER(38,0),
    time_interval_id    NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER cross_sections_trigger BEFORE INSERT ON cross_sections FOR each ROW
    BEGIN
        SELECT CROSS_SECTIONS_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE cross_sections ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
ALTER TABLE cross_sections ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;

-- DISCHARGE_TABLE_VALUES
CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;

CREATE TABLE discharge_table_values (
    id                  NUMBER(38,0) NOT NULL,
    q                   NUMBER(38,2),
    w                   NUMBER(38,2),
    table_id            NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER discharge_table_values_trigger BEFORE INSERT ON discharge_table_values FOR each ROW
    BEGIN
        SELECT DISCHARGE_TABLES_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE discharge_table_values ADD CONSTRAINT constraint_discharge_tables foreign key (table_id) REFERENCES discharge_tables;

-- DISCHARGE_TABLES
CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;

CREATE TABLE discharge_tables (
    id                  NUMBER(38,0) NOT NULL,
    description         VARCHAR2(255),
    kind                NUMBER(38,0),
    gauge_id            NUMBER(38,0),
    time_interval_id    NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER discharge_tables_trigger BEFORE INSERT ON discharge_tables FOR each ROW
    BEGIN
        SELECT DISCHARGE_TABLES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE discharge_tables ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
ALTER TABLE discharge_tables ADD CONSTRAINT constraint_gauges FOREIGN KEY (gauge_id) REFERENCES gauges;

-- EDGES
CREATE SEQUENCE EDGES_ID_SEQ;

CREATE TABLE edges (
    id                  NUMBER(38,0) NOT NULL,
    bottom              NUMBER(38,2),
    top                 NUMBER(38,2),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER edges_trigger BEFORE INSERT ON edges FOR each ROW
    BEGIN
        SELECT EDGES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

-- GAUGES
CREATE SEQUENCE GAUGES_ID_SEQ;

CREATE TABLE gauges (
    id                  NUMBER(38,0) NOT NULL,
    aeo                 NUMBER(38,2),
    datum               NUMBER(38,2), 
    name                VARCHAR2(255),
    station             NUMBER(38,2),
    range_id            NUMBER(38,0),
    river_id            NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER gauges_trigger BEFORE INSERT ON gauges FOR each ROW
    BEGIN
        SELECT GAUGES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE gauges add CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
ALTER TABLE gauges add CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES ranges;

-- HYK_ENTRIES
CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;

CREATE TABLE hyk_entries (
    id                  NUMBER(38,0) NOT NULL,
    km                  NUMBER(38,2),
    measure             TIMESTAMP,
    hyk_id              NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER hyk_entries_trigger BEFORE INSERT ON hyk_entries FOR each ROW
    BEGIN
        SELECT HYK_ENTRIES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE hyk_entries ADD CONSTRAINT constraint_hyks FOREIGN KEY (hyk_id) REFERENCES hyks;

-- HYK_FLOW_ZONE_TYPES
CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;

CREATE TABLE hyk_flow_zone_types (
    id                  NUMBER(38,0) NOT NULL,
    description         VARCHAR2(255),
    name                VARCHAR2(255),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER hyk_flow_zone_types_trigger BEFORE INSERT ON hyk_flow_zone_types FOR each ROW
    BEGIN
        SELECT HYK_FLOW_ZONE_TYPES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

-- HYK_FLOW_ZONES
CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;

CREATE TABLE hyk_flow_zones (
    id                  NUMBER(38,0) NOT NULL,
    a                   NUMBER(38,2),
    b                   NUMBER(38,2),
    formation_id        NUMBER(38,0),
    type_id             NUMBER(38,0),
    primary key         (id)
);

CREATE OR REPLACE TRIGGER hyk_flow_zones_trigger BEFORE INSERT ON hyk_flow_zones FOR each ROW
    BEGIN
        SELECT HYK_FLOW_ZONES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE hyk_flow_zones ADD CONSTRAINT constraint_hyk_formations FOREIGN KEY (formation_id) REFERENCES hyk_formations;
ALTER TABLE hyk_flow_zones ADD CONSTRAINT constraint_hyk_flow_zone_types FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;

-- HYK_FORMATIONS
CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;

CREATE TABLE hyk_formations (
    id                  NUMBER(38,0) NOT NULL,
    bottom              NUMBER(38,2),
    distance_hf         NUMBER(38,2),
    distance_vl         NUMBER(38,2),
    distance_vr         NUMBER(38,2),
    formation_num       NUMBER(38,0),
    top                 NUMBER(38,2),
    hyk_entry_id        NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER hyk_formations_trigger BEFORE INSERT ON hyk_formations FOR each ROW
    BEGIN
        SELECT HYK_FORMATIONS_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE hyk_formations ADD CONSTRAINT constraint_hyk_entries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;

-- HYKS
CREATE SEQUENCE HYKS_ID_SEQ;

CREATE TABLE hyks (
    id                  NUMBER(38,0) NOT NULL,
    description         VARCHAR2(255),
    river_id            NUMBER(38,0),
    primary key         (id)
);

CREATE OR REPLACE TRIGGER hyks_trigger BEFORE INSERT ON hyks FOR each ROW
    BEGIN
        SELECT HYKS_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE hyks ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;

-- MAIN_VALUE_TYPES
CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;

CREATE TABLE main_value_types (
    id                  NUMBER(38,0) NOT NULL,
    name                VARCHAR2(255),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER main_value_types_trigger BEFORE INSERT ON main_value_types FOR each ROW
    BEGIN
        SELECT MAIN_VALUE_TYPES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

-- MAIN_VALUES
CREATE SEQUENCE MAIN_VALUES_ID_SEQ;

CREATE TABLE main_values (
    id                  NUMBER(38,0) NOT NULL,
    value               NUMBER(38,2),
    gauge_id            NUMBER(38,0),
    named_value_id      NUMBER(38,0),
    time_interval_id    NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER main_values_trigger BEFORE INSERT ON main_values FOR each ROW
    BEGIN
        SELECT MAIN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE main_values ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
ALTER TABLE main_values ADD CONSTRAINT constraint_gauges FOREIGN KEY (gauge_id) REFERENCES gauges;
ALTER TABLE main_values ADD CONSTRAINT constraint_named_main_values FOREIGN KEY (named_value_id) REFERENCES named_main_values;

-- NAMED_MAIN_VALUES
CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;

CREATE TABLE named_main_values (
    id                  NUMBER(38,0) NOT NULL,
    name                VARCHAR2(255),
    type_id             NUMBER(38,0),
    PRIMARY KEY (id)
);

CREATE OR REPLACE TRIGGER named_main_values_trigger BEFORE INSERT ON named_main_values FOR each ROW
    BEGIN
        SELECT NAMED_MAIN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE named_main_values ADD CONSTRAINT constraint_main_value_types FOREIGN KEY (type_id) REFERENCES main_value_types;

-- POSITIONS
CREATE SEQUENCE POSITIONS_ID_SEQ;

CREATE TABLE positions (
    id                  NUMBER(10,0) NOT NULL,
    value               VARCHAR2(255 char),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER positions_trigger BEFORE INSERT ON positions FOR each ROW
    BEGIN
        SELECT POSITIONS_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

--- RANGES
CREATE SEQUENCE RANGES_ID_SEQ;

CREATE TABLE ranges (
    id                  NUMBER(38,0) NOT NULL,
    a                   NUMBER(38,2),
    b                   NUMBER(38,2),
    river_id            NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER ranges_trigger BEFORE INSERT ON ranges FOR each ROW
    BEGIN
        SELECT RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE ranges ADD CONSTRAINT contraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;

-- RIVERS
CREATE SEQUENCE RIVERS_ID_SEQ;

CREATE TABLE rivers (
    id                  NUMBER(38,0) NOT NULL,
    km_up               NUMBER(38,0),
    name                VARCHAR2(255),
    wst_unit_id         NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER rivers_trigger BEFORE INSERT ON rivers FOR each ROW
    BEGIN
        SELECT RIVERS_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE rivers ADD CONSTRAINT contraint_units FOREIGN KEY (wst_unit_id) REFERENCES units;

-- TIME_INTERVALS
CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;

CREATE TABLE time_intervals (
    id                  NUMBER(38,0) NOT NULL, 
    start_time          TIMESTAMP,
    stop_time           TIMESTAMP,
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER time_intervals_trigger BEFORE INSERT ON time_intervals FOR each ROW
    BEGIN
        SELECT TIME_INTERVALS_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

--- UNITS
CREATE SEQUENCE UNITS_ID_SEQ;

CREATE TABLE units (
    id                  NUMBER(10,0) NOT NULL,
    name                VARCHAR2(255),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER units_trigger BEFORE INSERT ON units FOR each ROW
    BEGIN
        SELECT UNITS_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

-- WST_COLUMN_Q_RANGES
CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;

CREATE TABLE wst_column_q_ranges (
    id                  NUMBER(38,0) NOT NULL,
    wst_column_id       NUMBER(38,0),
    wst_q_range_id      NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER wst_column_q_ranges_trigger BEFORE INSERT ON wst_column_q_ranges FOR each ROW
    BEGIN
        SELECT WST_COLUMN_Q_RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE wst_column_q_ranges ADD CONSTRAINT constraint_wst_colums FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
ALTER TABLE wst_column_q_ranges ADD CONSTRAINT constraint_wst_q_ranges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges;

-- WST_COLUMN_VALUES
CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;

CREATE TABLE wst_column_values (
    id                  NUMBER(38,0) NOT NULL,
    position            NUMBER(38,2),
    w                   NUMBER(38,2),
    wst_column_id       NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER wst_column_values_trigger BEFORE INSERT ON wst_column_values FOR each ROW
    BEGIN
        SELECT WST_COLUMN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE wst_column_values ADD CONSTRAINT constraint_wst_columns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;

-- WST_COLUMNS
CREATE SEQUENCE WST_COLUMNS_ID_SEQ;

CREATE TABLE wst_columns (
    id                  NUMBER(38,0) NOT NULL,
    description         VARCHAR2(255),
    name                VARCHAR2(255),
    position            NUMBER(38,0),
    time_interval_id    NUMBER(38,0),
    wst_id              NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER wst_columns_trigger BEFORE INSERT ON wst_columns FOR each ROW
    BEGIN
        SELECT WST_COLUMNS_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE wst_columns ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
ALTER TABLE wst_columns ADD CONSTRAINT constraint_wsts FOREIGN KEY (wst_id) REFERENCES wsts;

-- WST_Q_RANGES
CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;

CREATE TABLE wst_q_ranges (
    id                  NUMBER(38,0) NOT NULL,
    q                   NUMBER(38,2),
    range_id            NUMBER(38,0),
    PRIMARY KEY         (id)
);

CREATE OR REPLACE TRIGGER wst_q_ranges_trigger BEFORE INSERT ON wst_q_ranges FOR each ROW
    BEGIN
        SELECT WST_Q_RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE wst_q_ranges ADD CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES RANGES;

-- WSTS
CREATE SEQUENCE WSTS_ID_SEQ;

CREATE TABLE wsts (
    id                  NUMBER(38,0) NOT NULL,
    description         VARCHAR2(255),
    kind                NUMBER(38,0),
    river_id            NUMBER(38,0),
    PRIMARY KEY         (id)
);


CREATE OR REPLACE TRIGGER wsts_trigger BEFORE INSERT ON wsts FOR each ROW
    BEGIN
        SELECT WSTS_ID_SEQ.nextval INTO :new.id FROM dual;
    END;
/

ALTER TABLE wsts ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;

COMMIT;