


drop table if exists suchtabelle_#{jahr};
drop table if exists suchtabelle_en_#{jahr};
drop table if exists betriebe_#{jahr};
drop table if exists betriebe_gis_#{jahr};

create table suchtabelle_#{jahr} (
  betrieb_id integer,
  betriebseinrichtung varchar(210),
  muttergesellschaft varchar(210),
  eigentuemer_betreiber varchar(410),
  plz varchar(7),
  gemeinde varchar(170),
  bundesland_schluessel integer,
  bundesland varchar(240),
  flusseinzugsgebiet_schluessel varchar(3),
  flusseinzugsgebiet varchar(150),
  
  haupttaetigkeit_schluessel integer,
  haupttaetigkeit varchar(4),
  branchengruppe integer,
  taetigkeit_anhang1_schluessel varchar(10),
  taetigkeit_anhang1 varchar(150),
  nace_schluessel integer,
  nace varchar(150),
  
  -- Freisetzungen und Abwasserverbringungen:
  kompartiment_schluessel varchar(2),
  kompartiment varchar(150),
  schadstoffgruppe_schluessel varchar(2),
  schadstoffgruppe varchar(150),
  stoff_schluessel varchar(3),
  stoff varchar(400),
  bestimmungsmethode varchar(150),

  -- Abfall:
  beseitigung_verwertung_schluessel varchar(2),
  beseitigung_verwertung varchar(150),
  gefaehrlich_schluessel varchar(2),
  gefaehrlich varchar(150),
  inland_ausland_schluessel varchar(2),
  inland_ausland varchar(150),
  vbname varchar(150),
  vbort varchar(60),
  vbstaat_schluessel varchar(2),
  vbsort varchar(60),
  vbsstaat_schluessel varchar(2),
  bestimmungsmethode_abfall varchar(150)
);

create table suchtabelle_en_#{jahr} (like suchtabelle_#{jahr});

create table betriebe_#{jahr} (
  id integer,
  p_betrieb_id integer,
  kennnr varchar(30),
  name varchar(210),
  strasse varchar(60),
  hausnummer varchar(12),
  plz varchar(7),
  ort varchar(60),
  bundesland varchar(48)
);

create table betriebe_gis_#{jahr} (
  id integer,
  p_betrieb_id integer,
  kennnr varchar(30),
  betriebseinrichtung varchar(210),
  strasse varchar(60),
  hausnummer varchar(12),
  plz varchar(7),
  ort varchar(60),
  bundesland varchar(48),
  prtr_haupttaet_schluessel varchar(10),
  prtr_haupttaet varchar(150),
  prtr_haupttaet_en varchar(150),
  prtr_branchengruppe varchar(1)
);

SELECT AddGeometryColumn('public', 'betriebe_gis_#{jahr}', 'geo_etrs89_utm32', 25832, 'POINT', 2);
SELECT AddGeometryColumn('public', 'betriebe_gis_#{jahr}', 'geo_wgs84',         4326, 'POINT', 2);
SELECT AddGeometryColumn('public', 'betriebe_#{jahr}',     'geo_etrs89_utm32', 25832, 'POINT', 2);


alter table betriebe_#{jahr} add constraint betriebe_#{jahr}_i1 primary key (id);
alter table betriebe_#{jahr} add constraint betriebe_#{jahr}_fk1_p_betrieb
  foreign key (p_betrieb_id) references p_betrieb (id) match full;

alter table betriebe_gis_#{jahr} add constraint betriebe_gis_#{jahr}_i1 primary key (id);
alter table betriebe_gis_#{jahr} add constraint betriebe_gis_#{jahr}_fk1_p_betrieb
  foreign key (p_betrieb_id) references p_betrieb (id) match full;
create index betriebe_gis_#{jahr}_i2 on betriebe_gis_#{jahr} (prtr_branchengruppe);
create index betriebe_gis_#{jahr}_i3 on betriebe_gis_#{jahr} (prtr_haupttaet_schluessel);
create index betriebe_gis_#{jahr}_i4 on betriebe_gis_#{jahr} (geo_etrs89_utm32);


alter table suchtabelle_#{jahr} add constraint suchtabelle_#{jahr}_fk1_betrieb
  foreign key (betrieb_id) references betriebe_#{jahr} (id) match full;

create index suchtabelle_#{jahr}_i2 on suchtabelle_#{jahr} (plz);
create index suchtabelle_#{jahr}_i3 on suchtabelle_#{jahr} (flusseinzugsgebiet_schluessel);

create index suchtabelle_#{jahr}_i4 on suchtabelle_#{jahr} (haupttaetigkeit_schluessel, taetigkeit_anhang1_schluessel);
create index suchtabelle_#{jahr}_i5 on suchtabelle_#{jahr} (taetigkeit_anhang1_schluessel);
create index suchtabelle_#{jahr}_i6 on suchtabelle_#{jahr} (schadstoffgruppe_schluessel, kompartiment_schluessel);
create index suchtabelle_#{jahr}_i7 on suchtabelle_#{jahr} (stoff_schluessel, kompartiment_schluessel);
create index suchtabelle_#{jahr}_i8 on suchtabelle_#{jahr} (kompartiment_schluessel);
create index suchtabelle_#{jahr}_i9 on suchtabelle_#{jahr} (beseitigung_verwertung_schluessel,gefaehrlich_schluessel,inland_ausland_schluessel);
create index suchtabelle_#{jahr}_ia on suchtabelle_#{jahr} (gefaehrlich_schluessel, inland_ausland_schluessel);
create index suchtabelle_#{jahr}_ib on suchtabelle_#{jahr} (beseitigung_verwertung_schluessel, inland_ausland_schluessel);
create index suchtabelle_#{jahr}_ic on suchtabelle_#{jahr} (inland_ausland_schluessel);

alter table suchtabelle_en_#{jahr} add constraint suchtabelle_en_#{jahr}_fk1_betrieb
  foreign key (betrieb_id) references betriebe_#{jahr} (id) match full;

create index suchtabelle_en_#{jahr}_i2 on suchtabelle_#{jahr} (plz);
create index suchtabelle_en_#{jahr}_i3 on suchtabelle_#{jahr} (flusseinzugsgebiet_schluessel);

create index suchtabelle_en_#{jahr}_i4 on suchtabelle_en_#{jahr} (haupttaetigkeit_schluessel, taetigkeit_anhang1_schluessel);
create index suchtabelle_en_#{jahr}_i5 on suchtabelle_en_#{jahr} (taetigkeit_anhang1_schluessel);
create index suchtabelle_en_#{jahr}_i6 on suchtabelle_en_#{jahr} (schadstoffgruppe_schluessel, kompartiment_schluessel);
create index suchtabelle_en_#{jahr}_i7 on suchtabelle_en_#{jahr} (stoff_schluessel, kompartiment_schluessel);
create index suchtabelle_en_#{jahr}_i8 on suchtabelle_en_#{jahr} (kompartiment_schluessel);
create index suchtabelle_en_#{jahr}_i9 on suchtabelle_en_#{jahr} (beseitigung_verwertung_schluessel,gefaehrlich_schluessel,inland_ausland_schluessel);
create index suchtabelle_en_#{jahr}_ia on suchtabelle_en_#{jahr} (gefaehrlich_schluessel, inland_ausland_schluessel);
create index suchtabelle_en_#{jahr}_ib on suchtabelle_en_#{jahr} (beseitigung_verwertung_schluessel, inland_ausland_schluessel);
create index suchtabelle_en_#{jahr}_ic on suchtabelle_en_#{jahr} (inland_ausland_schluessel);


comment on table suchtabelle_#{jahr} is 'Zentrale Beschleunigungstabelle für die Ermittlung von betrieb_id-Mengen für das Jahr #{jahr}';
comment on table suchtabelle_en_#{jahr} is 'Zentrale Beschleunigungstabelle für die Ermittlung von betrieb_id-Mengen für das Jahr #{jahr} (Englisch)';
comment on table betriebe_#{jahr} is 'Beschleunigungstabelle mit den Betriebsadressen für das Jahr #{jahr}';
comment on table betriebe_gis_#{jahr} is 'Beschleunigungstabelle mit Betriebsadressen, Brancheninfo und GIS-Daten für das Jahr #{jahr}';

comment on column suchtabelle_#{jahr}.betrieb_id is 'Referenziert betriebe_xxx.id. NICHT mit p_betrieb_id verwechseln!';
comment on column suchtabelle_en_#{jahr}.betrieb_id is 'Referenziert betriebe_xxx.id. NICHT mit p_betrieb_id verwechseln!';
comment on column betriebe_#{jahr}.id is 'Wird von suchtabelle_xxx.betrieb_id referenziert';
comment on column betriebe_#{jahr}.p_betrieb_id is 'Nur für Forensik: Referenziert p_betrieb.id, die Haupt-Fachtabelle, aus der die Such- und Beschleunigungstabellen abgeleitet werden.';
comment on column betriebe_gis_#{jahr}.id is 'Wird von suchtabelle_xxx.betrieb_id referenziert';
comment on column betriebe_gis_#{jahr}.p_betrieb_id is 'Nur für Forensik: Referenziert p_betrieb.id, die Haupt-Fachtabelle, aus der die Such- und Beschleunigungstabellen abgeleitet werden.';

-- Das hat hier keinen Sinn: vacuum full analyze;
-- Nach 72_ ist es eher sinnvoll, wobei das -- wenn sechs Jahre verarbeitet werden -- einmal am Ende reicht.

