-- This document was automatically created by the ADE-Manager tool of 3DCityDB (https://www.3dcitydb.org) on 2021-03-29 18:02:06 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create tables ************************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWES_energysurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWES_energysurvey
(
    id INTEGER NOT NULL,
    biomasselectricitypotential NUMBER,
    biomasselectricitypotentialu VARCHAR2(1000),
    biomassprimaryenergypotent_1 VARCHAR2(1000),
    biomassprimaryenergypotentia NUMBER,
    biomassthermalpotential NUMBER,
    biomassthermalpotentialunit VARCHAR2(1000),
    electricitypotentialfrompv_1 VARCHAR2(1000),
    electricitypotentialfrompvbu NUMBER,
    residentialelectricitydema_1 VARCHAR2(1000),
    residentialelectricitydemand NUMBER,
    spacecoolingdemand NUMBER,
    spacecoolingdemandunit VARCHAR2(1000),
    spaceheatingdemand NUMBER,
    spaceheatingdemandunit VARCHAR2(1000),
    systemsurvey_energysurvey_id INTEGER,
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWES_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWES_foodsurvey
(
    id INTEGER NOT NULL,
    foodcategory VARCHAR2(1000),
    foodcategory_codespace VARCHAR2(1000),
    foodconsumption NUMBER,
    foodconsumptionunit VARCHAR2(1000),
    fooddemand NUMBER,
    fooddemandunit VARCHAR2(1000),
    foodimport NUMBER,
    foodimportunit VARCHAR2(1000),
    foodproduction NUMBER,
    foodproductionenergydemand NUMBER,
    foodproductionenergydemandun VARCHAR2(1000),
    foodproductionunit VARCHAR2(1000),
    foodproductionwaterdemand NUMBER,
    foodproductionwaterdemanduni VARCHAR2(1000),
    systemsurvey_foodsurvey_id INTEGER,
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWES_fwesystem 
-- -------------------------------------------------------------------- 
CREATE TABLE FWES_fwesystem
(
    id INTEGER NOT NULL,
    fwesystemname VARCHAR2(1000),
    lod0multisurface_id INTEGER,
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWES_systemsurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWES_systemsurvey
(
    id INTEGER NOT NULL,
    buildingfootprintarea NUMBER,
    buildingfootprintarea_uom VARCHAR2(1000),
    fwesystem_consistsoffwesu_id INTEGER,
    objectclass_id INTEGER,
    openplotarea NUMBER,
    openplotarea_uom VARCHAR2(1000),
    population INTEGER,
    settlementarea NUMBER,
    settlementarea_uom VARCHAR2(1000),
    surfacearea NUMBER,
    surfacearea_uom VARCHAR2(1000),
    surveydescription VARCHAR2(1000),
    surveyyear INTEGER,
    surveyyearversion NUMBER,
    trafficarea NUMBER,
    trafficarea_uom VARCHAR2(1000),
    vegetationarea NUMBER,
    vegetationarea_uom VARCHAR2(1000),
    waterbodyarea NUMBER,
    waterbodyarea_uom VARCHAR2(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWES_watersurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWES_watersurvey
(
    id INTEGER NOT NULL,
    domestichotwaterdemand NUMBER,
    domestichotwaterdemandunit VARCHAR2(1000),
    domesticsolidwaste NUMBER,
    domesticsolidwasteunit VARCHAR2(1000),
    domesticwaterdemand NUMBER,
    domesticwaterdemandunit VARCHAR2(1000),
    domesticwaterwaste NUMBER,
    domesticwaterwasteunit VARCHAR2(1000),
    systemsurvey_watersurvey_id INTEGER,
    totaldomesticwaste NUMBER,
    totaldomesticwasteenergypo_1 VARCHAR2(1000),
    totaldomesticwasteenergypote NUMBER,
    totaldomesticwasteunit VARCHAR2(1000),
    PRIMARY KEY (id)
);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create foreign keys ******************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWES_energysurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWES_energysurvey ADD CONSTRAINT FWES_energysurvey_fk FOREIGN KEY (id)
REFERENCES FWES_systemsurvey (id);

ALTER TABLE FWES_energysurvey ADD CONSTRAINT FWES_energy_syste_energ_fk FOREIGN KEY (systemsurvey_energysurvey_id)
REFERENCES FWES_systemsurvey (id);

-- -------------------------------------------------------------------- 
-- FWES_foodsurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWES_foodsurvey ADD CONSTRAINT FWES_foodsurvey_fk FOREIGN KEY (id)
REFERENCES FWES_systemsurvey (id);

ALTER TABLE FWES_foodsurvey ADD CONSTRAINT FWES_foodsu_syste_foods_fk FOREIGN KEY (systemsurvey_foodsurvey_id)
REFERENCES FWES_systemsurvey (id);

-- -------------------------------------------------------------------- 
-- FWES_fwesystem 
-- -------------------------------------------------------------------- 
ALTER TABLE FWES_fwesystem ADD CONSTRAINT FWES_fwesystem_fk FOREIGN KEY (id)
REFERENCES cityobject (id);

ALTER TABLE FWES_fwesystem ADD CONSTRAINT FWES_fwesystem_lod0mult_fk FOREIGN KEY (lod0multisurface_id)
REFERENCES surface_geometry (id);

-- -------------------------------------------------------------------- 
-- FWES_systemsurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWES_systemsurvey ADD CONSTRAINT FWES_systemsurvey_fk FOREIGN KEY (id)
REFERENCES cityobject (id);

ALTER TABLE FWES_systemsurvey ADD CONSTRAINT FWES_systemsur_objectcl_fk FOREIGN KEY (objectclass_id)
REFERENCES objectclass (id);

ALTER TABLE FWES_systemsurvey ADD CONSTRAINT FWES_system_fwesy_consi_fk FOREIGN KEY (fwesystem_consistsoffwesu_id)
REFERENCES FWES_fwesystem (id);

-- -------------------------------------------------------------------- 
-- FWES_watersurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWES_watersurvey ADD CONSTRAINT FWES_watersurvey_fk FOREIGN KEY (id)
REFERENCES FWES_systemsurvey (id);

ALTER TABLE FWES_watersurvey ADD CONSTRAINT FWES_waters_syste_water_fk FOREIGN KEY (systemsurvey_watersurvey_id)
REFERENCES FWES_systemsurvey (id);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create Indexes ************************************* 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWES_energysurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWES_energ_syste_energ_fkx ON FWES_energysurvey (systemsurvey_energysurvey_id);

-- -------------------------------------------------------------------- 
-- FWES_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWES_foods_syste_foods_fkx ON FWES_foodsurvey (systemsurvey_foodsurvey_id);

-- -------------------------------------------------------------------- 
-- FWES_fwesystem 
-- -------------------------------------------------------------------- 
CREATE INDEX FWES_fwesyste_lod0mult_fkx ON FWES_fwesystem (lod0multisurface_id);

-- -------------------------------------------------------------------- 
-- FWES_systemsurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWES_syste_fwesy_consi_fkx ON FWES_systemsurvey (fwesystem_consistsoffwesu_id);

CREATE INDEX FWES_systemsu_objectcl_fkx ON FWES_systemsurvey (objectclass_id);

-- -------------------------------------------------------------------- 
-- FWES_watersurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWES_water_syste_water_fkx ON FWES_watersurvey (systemsurvey_watersurvey_id);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create Sequences *********************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++