# Script prepares the geological database of ETH Zurich for publication using the BioCASE provider software
# It can then be registered with the GeoCASE portal (SimpleUI adaption)

# First of all, correct correct invalid entries in table objekt
update objekt
left join objektgruppe using (id_objektgruppe)
set objekt.id_systematik = If(objekt.id_systematik, objekt.id_systematik, objektgruppe.id_systematik),
    objekt.id_herkunft = If(objekt.id_herkunft, objekt.id_herkunft, objektgruppe.id_herkunft),
    objekt.id_lithologie = If(objekt.id_lithologie, objekt.id_lithologie, objektgruppe.id_lithologie),
    objekt.id_geologie = If(objekt.id_geologie, objekt.id_geologie, objektgruppe.id_geologie),
    objekt.id_zeitalter = If(objekt.id_zeitalter, objekt.id_zeitalter, objektgruppe.id_zeitalter),
    objekt.image = If(objekt.image, objekt.image, NULL);


# loop through table "herkunft" and get localityTexts, namedAreas and CountryNames
create table tmp
select h1.id_herkunft as id_herkunft,
			 h1.id_herkunft_typ as h1_typ, h1.ort as h1_ort,
			 h2.id_herkunft_typ as h2_typ, h2.ort as h2_ort,
			 h3.id_herkunft_typ as h3_typ, h3.ort as h3_ort,
			 h4.id_herkunft_typ as h4_typ, h4.ort as h4_ort,
			 h5.id_herkunft_typ as h5_typ, h5.ort as h5_ort
from herkunft h1
left join herkunft h2 on h1.id_self = h2.id_herkunft
left join herkunft h3 on h2.id_self = h3.id_herkunft
left join herkunft h4 on h3.id_self = h4.id_herkunft
left join herkunft h5 on h4.id_self = h5.id_herkunft;

create table tmp_geoNames
select id_herkunft, h1_typ as typ, h1_ort as ort from tmp where h1_typ is not null and h1_ort <> ''
	union
select id_herkunft, h2_typ, h2_ort from tmp where h2_typ is not null and h2_ort <> ''
	union
select id_herkunft, h3_typ, h3_ort from tmp where h3_typ is not null and h3_ort <> ''
	union
select id_herkunft, h4_typ, h4_ort from tmp where h4_typ is not null and h4_ort <> ''
	union
select id_herkunft, h5_typ, h5_ort from tmp where h5_typ is not null and h5_ort <> '';

create table abcd_namedArea
select id_objekt, ort as namedArea
from tmp_geoNames
join objekt using (id_herkunft)
where typ in (3, 4);

alter table abcd_namedArea
  add column id int auto_increment primary key first,
	add key (id_objekt);

delete from tmp_geoNames
where typ in (3, 4, 1);

alter table tmp_geoNames
	add key (id_herkunft),
	add key (typ);


# Do the same with table "systematik" -> higherTaxa
create table tmp
select s1.id_systematik,
      s1.id_systematik_typ as typ1, s1.lebewesen_lat as name1,
      s2.id_systematik_typ as typ2, s2.lebewesen_lat as name2,
      s3.id_systematik_typ as typ3, s3.lebewesen_lat as name3,
      s4.id_systematik_typ as typ4, s4.lebewesen_lat as name4,
      s5.id_systematik_typ as typ5, s5.lebewesen_lat as name5,
      s6.id_systematik_typ as typ6, s6.lebewesen_lat as name6,
      s7.id_systematik_typ as typ7, s7.lebewesen_lat as name7,
      s8.id_systematik_typ as typ8, s8.lebewesen_lat as name8,
      s9.id_systematik_typ as typ9, s9.lebewesen_lat as name9,
      s10.id_systematik_typ as typ10, s10.lebewesen_lat as name10,
      s11.id_systematik_typ as typ11, s11.lebewesen_lat as name11,
      s12.id_systematik_typ as typ12, s12.lebewesen_lat as name12,
      s13.id_systematik_typ as typ13, s13.lebewesen_lat as name13
from systematik s1
left join systematik s2 on s1.id_self = s2.id_systematik
left join systematik s3 on s2.id_self = s3.id_systematik
left join systematik s4 on s3.id_self = s4.id_systematik
left join systematik s5 on s4.id_self = s5.id_systematik
left join systematik s6 on s5.id_self = s6.id_systematik
left join systematik s7 on s6.id_self = s7.id_systematik
left join systematik s8 on s7.id_self = s8.id_systematik
left join systematik s9 on s8.id_self = s9.id_systematik
left join systematik s10 on s9.id_self = s10.id_systematik
left join systematik s11 on s10.id_self = s11.id_systematik
left join systematik s12 on s11.id_self = s12.id_systematik
left join systematik s13 on s12.id_self = s13.id_systematik;

create table tmp2
select id_systematik, typ1 as typ, name1 as name from tmp where typ1 is not null and name1 <> ''
  union
select id_systematik, typ2, name2 from tmp where typ2 is not null and name2 <> ''
  union
select id_systematik, typ3, name3 from tmp where typ3 is not null and name3 <> ''
  union
select id_systematik, typ4, name4 from tmp where typ4 is not null and name4 <> ''
  union
select id_systematik, typ5, name5 from tmp where typ5 is not null and name5 <> ''
  union
select id_systematik, typ6, name6 from tmp where typ6 is not null and name6 <> ''
  union
select id_systematik, typ7, name7 from tmp where typ7 is not null and name7 <> ''
  union
select id_systematik, typ8, name8 from tmp where typ8 is not null and name8 <> ''
  union
select id_systematik, typ9, name9 from tmp where typ9 is not null and name9 <> ''
  union
select id_systematik, typ10, name10 from tmp where typ10 is not null and name10 <> ''
  union
select id_systematik, typ11, name11 from tmp where typ11 is not null and name11 <> ''
  union
select id_systematik, typ12, name12 from tmp where typ12 is not null and name12 <> ''
  union
select id_systematik, typ13, name13 from tmp where typ13 is not null and name13 <> '';

create table abcd_higherTaxon
select id_objekt, name, systematik_typ as rank
from objekt
join tmp2 using (id_systematik)
join systematik_typ on typ = id_systematik_typ
where typ <> 1;

alter table abcd_higherTaxon
  add column id int auto_increment primary key first,
  add key (id_objekt);


# "zeitalter" -> ChronoStratigraphic Attribution
create table tmp
select z1.id_zeitalter as id_zeitalter,
			 z1.id_zeiteinheit as einheit1, z1.zeitalter as name1,
			 z2.id_zeiteinheit as einheit2, z2.zeitalter as name2,
			 z3.id_zeiteinheit as einheit3, z3.zeitalter as name3,
			 z4.id_zeiteinheit as einheit4, z4.zeitalter as name4,
			 z5.id_zeiteinheit as einheit5, z5.zeitalter as name5,
			 z6.id_zeiteinheit as einheit6, z6.zeitalter as name6,
			 z7.id_zeiteinheit as einheit7, z7.zeitalter as name7
from zeitalter z1
left join zeitalter z2 on z1.id_self = z2.id_zeitalter
left join zeitalter z3 on z2.id_self = z3.id_zeitalter
left join zeitalter z4 on z3.id_self = z4.id_zeitalter
left join zeitalter z5 on z4.id_self = z5.id_zeitalter
left join zeitalter z6 on z5.id_self = z6.id_zeitalter
left join zeitalter z7 on z6.id_self = z7.id_zeitalter;

create table tmp2
select id_zeitalter, einheit1 as einheit, name1 as name from tmp where einheit1 is not null and name1 <> ''
  union
select id_zeitalter, einheit2 as einheit, name2 as name from tmp where einheit2 is not null and name2 <> ''
  union
select id_zeitalter, einheit3 as einheit, name3 as name from tmp where einheit3 is not null and name3 <> ''
  union
select id_zeitalter, einheit4 as einheit, name4 as name from tmp where einheit4 is not null and name4 <> ''
  union
select id_zeitalter, einheit5 as einheit, name5 as name from tmp where einheit5 is not null and name5 <> ''
  union
select id_zeitalter, einheit6 as einheit, name6 as name from tmp where einheit6 is not null and name6 <> ''
  union
select id_zeitalter, einheit7 as einheit, name7 as name from tmp where einheit7 is not null and name7 <> '';

create table abcd_chronoStratigraphy
SELECT id_objekt, name, zeiteinheit
FROM tmp2
join objekt using (id_zeitalter)
left join zeiteinheit on einheit = id_zeiteinheit;

alter table abcd_chronoStratigraphy
  add column id int auto_increment primary key first,
  add key (id_objekt);

# "geology" -> Lithostratigraphic Attribution
create table tmp
select g1.id_geologie as id_geologie,
			 g1.id_geologie_typ as typ1, g1.einheit as einheit1,
			 g2.id_geologie_typ as typ2, g2.einheit as einheit2,
			 g3.id_geologie_typ as typ3, g3.einheit as einheit3,
			 g4.id_geologie_typ as typ4, g4.einheit as einheit4,
			 g5.id_geologie_typ as typ5, g5.einheit as einheit5
from geologie g1
left join geologie g2 on g1.id_self = g2.id_geologie
left join geologie g3 on g2.id_self = g3.id_geologie
left join geologie g4 on g3.id_self = g4.id_geologie
left join geologie g5 on g4.id_self = g5.id_geologie;

create table tmp2
select id_geologie, typ1 as typ, einheit1 as einheit from tmp where typ1 is not null and einheit1 <> ''
  union
select id_geologie, typ2 as typ, einheit2 as einheit from tmp where typ2 is not null and einheit2 <> ''
  union
select id_geologie, typ3 as typ, einheit3 as einheit from tmp where typ3 is not null and einheit3 <> ''
  union
select id_geologie, typ4 as typ, einheit4 as einheit from tmp where typ4 is not null and einheit4 <> ''
  union
select id_geologie, typ5 as typ, einheit5 as einheit from tmp where typ5 is not null and einheit5 <> '';

create table abcd_lithoStratigraphy
select geologie.id_geologie,
    bed.einheit as bed, formation.einheit as formation, member.einheit as member, group.einheit as `group`, supergroup.einheit as supergroup
from geologie
left join tmp2 bed         on (geologie.id_geologie = bed.id_geologie and bed.typ = 5)
left join tmp2 formation   on (geologie.id_geologie = formation.id_geologie and formation.typ = 4)
left join tmp2 member      on (geologie.id_geologie = member.id_geologie and member.typ = 3)
left join tmp2 `group`     on (geologie.id_geologie = group.id_geologie and `group`.typ = 2)
left join tmp2 supergroup  on (geologie.id_geologie = supergroup.id_geologie and supergroup.typ = 1);

alter table abcd_lithoStratigraphy
  add primary key (id_geologie);


# Last, create base table abcd_objekt

create table abcd_objekt
select id_objekt, id_kategorie_haupt as id_source, objekt.id_geologie,
   objekt.barcode, inventar_no_alt, inventar_no_neu, objekt_anzahl, praeparator,
   finder, bestimmer, fund_datum, bestimmung_datum,
   erfassung_datum, erworben_von, erwerbsdatum,
   case when objekt.id_objekttyp = 2 then bezeichnung else NULL end as ident_pal,
   case when objekt.id_objekttyp = 2 then NULL else bezeichnung end as ident_min,
   locality.ort as localityText, country.ort as countryName, erwerbart, status,
   case when lithologie = 'Mergel' then 'sedimentary' else NULL end as lithologie,
   case when objekt.id_objekttyp = 2 then 'fossil' else 'specimen' end as recordBasis,
   case when objekt.id_objekttyp = 2 then fossil_typ else objekttyp end as kindOfUnit,
   concat('http://www.collection-db.erdw.ethz.ch/file/object/', cast(id_objekt as char), '/', image) as imageURL,
   objekt.bemerkung, objekt.lastdate as lastEdited, benutzer.name as lastEditor,
	 cast(concat_ws('-', gebaeude_code, raum, abteil, concat_ws(':', kolonne, position)) as char) as position,
	 cast(concat_ws('-', autor, jahr_von, bibl_nr) as char) as ref
from objekt
left join objektgruppe using(id_objektgruppe)
left join schublade on objekt.id_schublade = schublade.id_schublade
     join kategorie_spez using (id_kategorie_spez)
left join basis_position on basis_position.id_basis_position = schublade.id_basis_position
left join raum using(id_raum)
left join gebaeude using (id_gebaeude)
left join referenz on referenz.id_referenz = objektgruppe.id_referenz
left join tmp_geoNames as locality on (objekt.id_herkunft = locality.id_herkunft and locality.typ = 2)
left join tmp_geoNames as country  on (objekt.id_herkunft = country.id_herkunft and country.typ = 5)
left join lithologie on objekt.id_lithologie = lithologie.id_lithologie
left join erwerbart on erwerbart.id_erwerbart = objekt.id_erwerbart
left join status on status.id_status = objekt.id_status
left join objekttyp on objekttyp.id_objekttyp = objekt.id_objekttyp
left join fossil_typ on fossil_typ.id_fossil_typ = objekt.id_fossil_typ
left join benutzer on benutzer.id_benutzer = objekt.id_benutzer
where id_kategorie_haupt in (3, 5, 9, 10, 11, 12, 13);
#where inventar_no_neu = 'B 565'

alter table abcd_objekt
  add primary key (id_objekt),
  add key (id_source),
  add key (id_geologie),
  add index (barcode),
  add index (finder),
  add index (ident_min),
  add index (ident_pal),
  add index (localityText),
  add index (countryName);

# remove temp tables
