/* This query identifies rank and endemism discrepancies between the global and subnational levels. 
Query by Gwen Davis and modified by Dave Almquist 
Run in a local instance of Biotics
2022-08-30 */
 
Select
egt.element_global_id EGT_ID,
egt.elcode_bcd ELCODE,
d_nc.name_type_cd NMTYP,  
d_nc.name_category_desc NAME_CAT,
informal_grp((informal_tax(egt.element_global_id))) INF_GRP,
informal_tax(egt.element_global_id) INF_TAX,
d_cs.classification_status_cd CL_STAT,
egt.rounded_g_rank R_GRANK,
gname.scientific_name GNAME,
case when tg.d_juris_endem_id is null and est.d_endemism_id = 1 then 'CHECK: null'
     when (tg.d_juris_endem_id != 1 and est.d_endemism_id = 1) 
       or (tg.d_juris_endem_id = 1 and (est.d_endemism_id != 1 or est.d_endemism_id is null))
     then 'CHECK: '||d_jend_g.juris_endem_cd ELSE d_jend_g.juris_endem_cd END AS G_J_END,

case when ((tg.d_juris_endem_id = 1 or est.d_endemism_id = 1)
       and (replace(est.s_rank,'S','') != REGEXP_REPLACE(SUBSTR(egt.g_rank,INSTR(egt.g_rank,'T') +0),'([G|T|Q])','')))
      then 'CHECK: '||egt.g_rank ELSE egt.g_rank END AS GRANK,

to_char(egt.g_rank_change_date, 'yyyy-mm-dd') GRK_CH_dt,
to_char(egt.g_rank_review_date, 'yyyy-mm-dd') GRK_RV_dt,
to_char(egr.version_date,'YYYY-MM-DD') EGR_VER_dt,
subn.subnation_code SUBN,
case when est.d_endemism_id is null and tg.d_juris_endem_id = 1 then 'CHECK: null'
     when (est.d_endemism_id = 1 and (tg.d_juris_endem_id != 1 or tg.d_juris_endem_id is null)) 
       or (est.d_endemism_id != 1 and tg.d_juris_endem_id = 1)
     then 'CHECK: '||d_endem_s.endemism_cd ELSE d_endem_s.endemism_cd END AS S_ENDEM,
     
case when ((tg.d_juris_endem_id = 1 or est.d_endemism_id = 1)
       and (replace(est.s_rank,'S','') != REGEXP_REPLACE(SUBSTR(egt.g_rank,INSTR(egt.g_rank,'T') + 0),'([G|T|Q])','')))
     then 'CHECK: '||est.s_rank ELSE est.s_rank END AS SRANK,
esr.S_RANK_REASONS,
esr.S_RANGE_COM S_RANGE_COMMENTS,
d_eot.eo_track_status_cd S_TRK,
to_char(est.s_rank_change_date, 'yyyy-mm-dd') SRK_CH_dt,
to_char(est.s_rank_review_date, 'yyyy-mm-dd') SRK_REV_dt,
to_char(esr.VERSION_DATE,'YYYY-MM-DD') ESR_VER_dt,
sname.scientific_name SNAME,
est.element_subnational_id EST_ID,
egr.G_RANK_REASONS,
egt.G_CLASSIFICATION_COM,
egr.g_range_com G_RANGE_COMMENTS,
DelimList('SELECT d_origin.origin_desc
FROM taxon_natl_dist n_dist, d_origin
WHERE n_dist.d_origin_id = d_origin.d_origin_id
and n_dist.d_dist_confidence_id = 1  /***confident***/
and n_dist.element_national_id = ' || ent.element_national_id || '
ORDER BY n_dist.d_origin_id', ' and ') AS NATION_ORIGIN,
(select d_usesa.usesa_cd from d_usesa
  WHERE tg.d_usesa_id = d_usesa.d_usesa_id) USESA,
tg.interpreted_usesa INTERP_USESA,
(select taxon_subnational.state_interpreted_usesa from taxon_subnational
  WHERE taxon_subnational.element_subnational_id = est.element_subnational_id) STATE_INTERP_USESA

From
element_global egt,
taxon_global tg,
element_national ent,
element_subnational est,
scientific_name gname,
scientific_name sname,
subnation subn,
d_juris_endem d_jend_g,
d_endemism d_endem_s,
d_classification_status d_cs,
element_global_rank egr,
d_name_category d_nc,
D_EO_TRACK_STATUS d_eot,
ELEMENT_SUBNATL_RANK esr

Where egt.element_global_id = egr.element_global_id (+)
and egt.element_global_id = ent.element_global_id
and ent.element_national_id = est.element_national_id
and est.element_subnational_id = esr.element_subnational_id (+)
and est.subnation_id = subn.subnation_id
and est.d_endemism_id = d_endem_s.d_endemism_id (+)
and d_eot.d_eo_track_status_id = est.d_eo_track_status_id
and egt.gname_id = gname.scientific_name_id
and est.sname_id = sname.scientific_name_id
and gname.d_name_category_id = d_nc.d_name_category_id
and egt.element_global_id = tg.element_global_id
and egt.d_classification_status_id = d_cs.d_classification_status_id
and tg.d_juris_endem_id = d_jend_g.d_juris_endem_id (+)

and egt.inactive_ind = 'N'
  -- and subn.subnation_code like 'FL'
and d_nc.name_type_desc in ('Zoological', 'Botanical')
  -- and gname.d_name_category_id = 4  /***vascular plants***/
and est.s_rank not in ('SNR','SNA')
and (
     --endemic to a single state or province
      ((tg.d_juris_endem_id = 1 or est.d_endemism_id = 1)
      and
      -- Srank not same as Grank
      (replace(est.s_rank,'S','') != REGEXP_REPLACE(SUBSTR(egt.g_rank,INSTR(egt.g_rank,'T') + 0),'([G|T|Q])','')))
        
     or
      --EST says endemic EGT does not
     (est.d_endemism_id = 1 and (tg.d_juris_endem_id != 1 or tg.d_juris_endem_id is null))
      --EGT says endemic EST does not
     or (tg.d_juris_endem_id = 1 and (est.d_endemism_id != 1 or est.d_endemism_id is null))
     )
order by NMTYP, INF_GRP, INF_tax, GNAME