-- Table: ews.ews_anlage

-- DROP TABLE ews.ews_anlage;

CREATE TABLE ews.ews_anlage
(
    t_id bigint NOT NULL DEFAULT nextval('ews.t_ili2db_seq'::regclass),
    t_basket bigint NOT NULL DEFAULT 4,
    t_ili_tid uuid DEFAULT uuid_generate_v4(),
    anlage_nr character varying(7) COLLATE pg_catalog."default" NOT NULL,
    waermetraegerfluessigkeit bigint,
    gesamtfuellmenge integer,
    max_leistung numeric(6,2),
    verwendungszweck bigint,
    fabrikat_waermepumpe character varying(100) COLLATE pg_catalog."default",
    bohrrapport_eing boolean,
    hydrogeo_report_verl boolean,
    hydrogeo_report_eing boolean,
    geol_bohrprofil_verl boolean,
    geol_bohrprofil_eing boolean,
    bohrdatum date,
    eingangsdatum_gesuch date,
    erfassungsdatum date NOT NULL,
    bewilligungsdatum date,
    status bigint,
    bemerkungen text COLLATE pg_catalog."default",
    adresse bigint NOT NULL,
    bohrfirma bigint,
    eigentuemer bigint NOT NULL,
    geologe bigint,
    heizungsplaner bigint,
    geometrie geometry(Point,2056),
    CONSTRAINT ews_anlage_pkey PRIMARY KEY (t_id),
    CONSTRAINT ews_anlage_adresse_fkey FOREIGN KEY (adresse)
        REFERENCES ews.ews_anlage_adresse (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT ews_anlage_bohrfirma_fkey FOREIGN KEY (bohrfirma)
        REFERENCES ews.bohrfirma (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT ews_anlage_eigentuemer_fkey FOREIGN KEY (eigentuemer)
        REFERENCES ews.eigentuemer (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT ews_anlage_geologe_fkey FOREIGN KEY (geologe)
        REFERENCES ews.geologe (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT ews_anlage_heizungsplaner_fkey FOREIGN KEY (heizungsplaner)
        REFERENCES ews.heizungsplaner (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT ews_anlage_status_fkey FOREIGN KEY (status)
        REFERENCES ews.anlagenstatus_catalogue (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT ews_anlage_t_basket_fkey FOREIGN KEY (t_basket)
        REFERENCES ews.t_ili2db_basket (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT ews_anlage_verwendungszweck_fkey FOREIGN KEY (verwendungszweck)
        REFERENCES ews.verwendungszweck_catalogue (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT ews_anlage_waermetraegerfluessigkeit_fkey FOREIGN KEY (waermetraegerfluessigkeit)
        REFERENCES ews.waermetraegerfluessigkeit_catalogue (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT ews_anlage_gesamtfuellmenge_check CHECK (gesamtfuellmenge >= 0 AND gesamtfuellmenge <= 1000000),
    CONSTRAINT ews_anlage_max_leistung_check CHECK (max_leistung >= 0.0 AND max_leistung <= 9999.99)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE ews.ews_anlage
    OWNER to ews;

GRANT ALL ON TABLE ews.ews_anlage TO ews;

GRANT SELECT ON TABLE ews.ews_anlage TO PUBLIC;

COMMENT ON TABLE ews.ews_anlage
    IS '@iliname Erdwaermesonden_V1.Erdwaerme.EWS_Anlage';

COMMENT ON COLUMN ews.ews_anlage.anlage_nr
    IS '@iliname Anlage_Nr';

COMMENT ON COLUMN ews.ews_anlage.gesamtfuellmenge
    IS '@iliname Gesamtfuellmenge';

COMMENT ON COLUMN ews.ews_anlage.max_leistung
    IS '@iliname Max_Leistung';

COMMENT ON COLUMN ews.ews_anlage.fabrikat_waermepumpe
    IS '@iliname Fabrikat_Waermepumpe';

COMMENT ON COLUMN ews.ews_anlage.bohrrapport_eing
    IS '@iliname Bohrrapport_Eing';

COMMENT ON COLUMN ews.ews_anlage.hydrogeo_report_verl
    IS '@iliname Hydrogeo_Report_Verl';

COMMENT ON COLUMN ews.ews_anlage.hydrogeo_report_eing
    IS '@iliname Hydrogeo_Report_Eing';

COMMENT ON COLUMN ews.ews_anlage.geol_bohrprofil_verl
    IS '@iliname Geol_Bohrprofil_Verl';

COMMENT ON COLUMN ews.ews_anlage.geol_bohrprofil_eing
    IS '@iliname Geol_Bohrprofil_Eing';

COMMENT ON COLUMN ews.ews_anlage.bohrdatum
    IS '@iliname Bohrdatum';

COMMENT ON COLUMN ews.ews_anlage.eingangsdatum_gesuch
    IS '@iliname Eingangsdatum_Gesuch';

COMMENT ON COLUMN ews.ews_anlage.erfassungsdatum
    IS '@iliname Erfassungsdatum';

COMMENT ON COLUMN ews.ews_anlage.bewilligungsdatum
    IS '@iliname Bewilligungsdatum';

COMMENT ON COLUMN ews.ews_anlage.bemerkungen
    IS '@iliname Bemerkungen';

COMMENT ON COLUMN ews.ews_anlage.geometrie
    IS '@iliname Geometrie';

-- Index: ews_anlage_adresse_idx

-- DROP INDEX ews.ews_anlage_adresse_idx;

CREATE INDEX ews_anlage_adresse_idx
    ON ews.ews_anlage USING btree
    (adresse)
    TABLESPACE pg_default;

-- Index: ews_anlage_anlage_nr_key

-- DROP INDEX ews.ews_anlage_anlage_nr_key;

CREATE UNIQUE INDEX ews_anlage_anlage_nr_key
    ON ews.ews_anlage USING btree
    (anlage_nr COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: ews_anlage_bohrfirma_idx

-- DROP INDEX ews.ews_anlage_bohrfirma_idx;

CREATE INDEX ews_anlage_bohrfirma_idx
    ON ews.ews_anlage USING btree
    (bohrfirma)
    TABLESPACE pg_default;

-- Index: ews_anlage_eigentuemer_idx

-- DROP INDEX ews.ews_anlage_eigentuemer_idx;

CREATE INDEX ews_anlage_eigentuemer_idx
    ON ews.ews_anlage USING btree
    (eigentuemer)
    TABLESPACE pg_default;

-- Index: ews_anlage_geologe_idx

-- DROP INDEX ews.ews_anlage_geologe_idx;

CREATE INDEX ews_anlage_geologe_idx
    ON ews.ews_anlage USING btree
    (geologe)
    TABLESPACE pg_default;

-- Index: ews_anlage_geometrie_idx

-- DROP INDEX ews.ews_anlage_geometrie_idx;

CREATE INDEX ews_anlage_geometrie_idx
    ON ews.ews_anlage USING gist
    (geometrie)
    TABLESPACE pg_default;

-- Index: ews_anlage_heizungsplaner_idx

-- DROP INDEX ews.ews_anlage_heizungsplaner_idx;

CREATE INDEX ews_anlage_heizungsplaner_idx
    ON ews.ews_anlage USING btree
    (heizungsplaner)
    TABLESPACE pg_default;

-- Index: ews_anlage_status_idx

-- DROP INDEX ews.ews_anlage_status_idx;

CREATE INDEX ews_anlage_status_idx
    ON ews.ews_anlage USING btree
    (status)
    TABLESPACE pg_default;

-- Index: ews_anlage_t_basket_idx

-- DROP INDEX ews.ews_anlage_t_basket_idx;

CREATE INDEX ews_anlage_t_basket_idx
    ON ews.ews_anlage USING btree
    (t_basket)
    TABLESPACE pg_default;

-- Index: ews_anlage_verwendungszweck_idx

-- DROP INDEX ews.ews_anlage_verwendungszweck_idx;

CREATE INDEX ews_anlage_verwendungszweck_idx
    ON ews.ews_anlage USING btree
    (verwendungszweck)
    TABLESPACE pg_default;

-- Index: ews_anlage_waermetraegerfluessigkeit_idx

-- DROP INDEX ews.ews_anlage_waermetraegerfluessigkeit_idx;

CREATE INDEX ews_anlage_waermetraegerfluessigkeit_idx
    ON ews.ews_anlage USING btree
    (waermetraegerfluessigkeit)
    TABLESPACE pg_default;