   ( SELECT name from darwin2.chronostratigraphy c2  WHERE level_ref = 57  AND c1.path like '%/' || c2.id || '/%' ) as subera,
   ( SELECT name from darwin2.chronostratigraphy c2  WHERE level_ref = 58  AND c1.path like '%/' || c2.id || '/%' ) as system,
   ( SELECT name from darwin2.chronostratigraphy c2  WHERE level_ref = 59  AND c1.path like '%/' || c2.id || '/%' ) as serie,
-  ( SELECT name from darwin2.chronostratigraphy c2  WHERE level_ref = 60  AND c1.path like '%/' || c2.id || '/%' ) as stage, 
+  ( SELECT name from darwin2.chronostratigraphy c2  WHERE level_ref = 60  AND c1.path like '%/' || c2.id || '/%' ) as stage,
   ( SELECT name from darwin2.chronostratigraphy c2  WHERE level_ref = 61  AND c1.path like '%/' || c2.id || '/%' ) as substage
   from darwin2.chronostratigraphy c1
-    
+
 );
 
 ALTER TABLE public.chronostratigraphy_abc ADD CONSTRAINT pk_chrono_abc PRIMARY KEY (id);
 
 CREATE TABLE public.accomp_mineral AS
 (
-
-
   SELECT
-    a.id,
-    f.id as flat_id,
+    r.id,
+    s.id as specimen_id,
     m.classification as classification,
     m.name as mineral_name
-   FROM 
-    darwin2.darwin_flat f
-    INNER JOIN darwin2.specimens_accompanying a ON f.spec_ref = a.specimen_ref
-    INNER JOIN darwin2.mineralogy m ON a.mineral_ref = m.id
-    WHERE accompanying_type ='mineral'
+   FROM
+    darwin2.specimens s
+    INNER JOIN darwin2.specimens_relationships r on s.id = r.specimen_ref
+    INNER JOIN darwin2.mineralogy m ON r.mineral_ref = m.id
+    WHERE unit_type ='mineralogy' and relationship_type ='combination'
 
 );
 

    nextval('public.taxon_identified_id_seq') as id,
    i.id as identification_ref,
    c.value_defined as taxon_name,
    CASE WHEN c.value_defined = f.taxon_name THEN f.taxon_ref ELSE null::integer END as taxon_ref,
    CASE WHEN c.value_defined = f.taxon_name THEN f.taxon_parent_ref ELSE null::integer END as taxon_parent_ref,
    CASE WHEN c.value_defined = f.taxon_name THEN f.taxon_level_name ELSE null::varchar END as taxon_level_name,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='AuthorTeam' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141538/%')
     LIMIT 1
    ) as AuthorTeam,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='AuthorTeamParenthesis' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141538/%')
     LIMIT 1
    ) as AuthorTeamParenthesis,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='AuthorTeamAndYear' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141541/%')
     LIMIT 1
    ) as AuthorTeamAndYear,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='AuthorTeamOriginalAndYear' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/1/%')
     LIMIT 1
    ) as AuthorTeamOriginalAndYear,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='AuthorTeamParenthesisAndYear' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/1/%')
     LIMIT 1
    ) as AuthorTeamParenthesisAndYear,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='CombinationAuthorTeamAndYear' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/1/%')
     LIMIT 1
    ) as CombinationAuthorTeamAndYear,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='CultivarGroupName' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141538/%')
     LIMIT 1
    ) as CultivarGroupName,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='CultivarName' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141538/%')
     LIMIT 1
    ) as CultivarName,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='GenusOrMonomial' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141538/%')
     LIMIT 1
    ) as BotanyGenusOrMonomial,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='GenusOrMonomial' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/1/%')
     LIMIT 1
    ) as ZooGenusOrMonomial,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='GenusOrMonomial' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141541/%')
     LIMIT 1
    ) as BacterialGenusOrMonomial,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='Subgenus' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/1/%')
     LIMIT 1
    ) as ZooSubgenus,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='Subgenus' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141541/%')
     LIMIT 1
    ) as BacterialSubgenus,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='FirstEpithet' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141538/%')
     LIMIT 1
    ) as FirstEpithet,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='SpeciesEpithet' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/1/%')
     LIMIT 1
    ) as ZooSpeciesEpithet,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='SpeciesEpithet' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141541/%')
     LIMIT 1
    ) as BacterialSpeciesEpithet,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='InfraspecificEpithet' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141538/%')
     LIMIT 1
    ) as InfraspecificEpithet,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='Breed' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/1/%')
     LIMIT 1
    ) as Breed,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='SubspeciesEpithet' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/1/%')
     LIMIT 1
    ) as ZooSubspeciesEpithet,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='SubspeciesEpithet' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141541/%')
     LIMIT 1
    ) as BacterialSubspeciesEpithet,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='SubgenusAuthorAndYear' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/141541/%')
     LIMIT 1
    ) as SubgenusAuthorAndYear,
    (SELECT keyword FROM darwin2.classification_keywords where 
          referenced_relation = 'taxonomy' and record_id = f.taxon_ref AND keyword_type='NamedIndividual' AND CASE WHEN c.value_defined = f.taxon_name THEN true ELSE false END AND
          exists (select 1 from taxonomy where id = f.taxon_ref and path like '/-1/1/%')
     LIMIT 1
    ) as NamedIndividual
  FROM 
    public.identifications_abdc i
    INNER JOIN darwin2.identifications as c ON i.old_identification_id = c.id
    INNER JOIN darwin2.darwin_flat f ON  f.id = i.flat_id
    WHERE 
      c.notion_concerned = 'taxonomy'
);


                     on p.id = pr.person_1_ref
                   )
                 on d.people_ref = p.id and p.is_physical = true
               )
    on s.id = d.record_id  AND d.referenced_relation = 'specimens' AND d.people_type = 'donator'
  union
  select
    nextval('public.donators_abcd_id_seq') as id,
    s.id as specimen_id,
    d.order_by,
    p.formated_name as institution_formated_name,
    null::varchar as people_formated_name,
