View for Red List API Index

api_index.sql

create view api_index as
select
  CASE WHEN y.infra_rank is null THEN
    y.genus_name || ' ' || y.species_name
  ELSE
    trim(trailing from y.genus_name || ' ' || y.species_name || ' ' || y.infra_rank || ' ' || y.infra_name)
  END as scientific_name,
  not(is_synonym) as primary,
  class_name as class,
  order_name as order,
  family_name as family,
  y.genus_name as genus,
  y.species_name as species,
  y.authority as authority,
  y.infra_name,
  y.infra_rank,
  y.infra_authority,
  species.id as species_id, 
  modified_year,
  categories.code as category,
  criteria
from synonyms y
join species
  on y.species_id = species.id
join assessment_mappings
  on assessment_mappings.region_id = 0 and assessment_mappings.species_id = species.id
join assessments
  on assessments.id = assessment_mappings.assessment_id
join categories
  on assessments.category_id = categories.id
where coalesce(y.species_name,'')!='';