 
 CREATE TABLE public.collectors as
 (
-  select 
+  select
     nextval('public.collectors_abcd_id_seq') as id,
-    f.id as flat_id,
+    s.id as specimen_id,
     c.order_by,
     ins.formated_name as institution_formated_name,
     p.formated_name as people_formated_name,

DROP SEQUENCE IF EXISTS public.mineral_vernacular_name_id_seq;
DROP SEQUENCE IF EXISTS public.identifier_abcd_id_seq;
DROP SEQUENCE IF EXISTS public.identifier_institution_id_seq;
DROP SEQUENCE IF EXISTS public.flat_properties_id_seq;
DROP SEQUENCE IF EXISTS public.taxonomy_id_seq;
DROP SEQUENCE IF EXISTS public.darwin_flat_id_seq;
DROP SEQUENCE IF EXISTS public.catalogue_levels_id_seq;

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

