with prot_table as (
/*
 map proteins to genes
*/
     select fs2.feature_id gene_id, f.feature_id protein_id
   from chado.feature f, chado.feature_relationship fr , chado.feature fs, 
    chado.feature_relationship fr2 , chado.feature fs2
   where f.type_id in (select cvterm_id from chado.cvterm where name='polypeptide')
   and fr.subject_id =f.feature_id
   and fr.object_id = fs.feature_id 
   and fr2.subject_id =fs.feature_id
   and fr2.object_id = fs2.feature_id 
   and fs2.type_id in (select cvterm_id from chado.cvterm where name='gene')
)
select distinct F.feature_id, F.name AS name, F.uniquename AS uniquename, F.seqlen AS feature_seqlen, (SELECT genus || ' ' || species FROM chado.organism WHERE organism_id = F.organism_id) AS organism, (SELECT common_name FROM chado.organism WHERE organism_id = F.organism_id) AS organism_common_name, (SELECT name FROM chado.cvterm WHERE cvterm_id = F.type_id) AS feature_type, 
LOC.srcfeature_id,
      LOC.name AS landmark,
      (LOC.fmin + 1) AS fmin,
      LOC.fmax,
      LOC.strand,
      LOC.name || ':' || (fmin + 1) || '..' || fmax AS location,
/*
   assigne annotation name using nomenclatures
*/
case when F.uniquename like 'CS10G%' then 'cs10g21t' when F.uniquename like 'CS10R%' then 'cs10g21t' when F.organism_id=1 then 'cs10' when F.uniquename like 'PKGMv1%' then 'pkgmv1' when F.uniquename like 'PKFDv1%' then 'pkfdv1' when F.uniquename like 'FNFDv1%' then 'fnfdv1'  else 'Csativa' end AS analysis , null AS blast_value, null as kegg_value, 
case when interproterms.interpro_term is not null and interprotermsmrna.interpro_term is not null then interproterms.interpro_term || ',' || interprotermsmrna.interpro_term  when interproterms.interpro_term is not null then   interproterms.interpro_term when interprotermsmrna.interpro_term is not null then interprotermsmrna.interpro_term  end AS interpro_value, 
go_term.go_terms AS go_term,
gbterms.gbterm as gb_keyword
 FROM chado.feature F LEFT JOIN chado.analysisfeature AF ON AF.feature_id = F.feature_id left JOIN chado.analysis A ON A.analysis_id = AF.analysis_id 
LEFT join ( select fl.feature_id , fl.srcfeature_id, fsrc.name, fl.fmin, fl.fmax,  fl.strand from  chado.featureloc fl, chado.feature fsrc
    where fl.srcfeature_id = fsrc.feature_id 
  ) as LOC ON LOC.feature_id = F.feature_id
left join 
(
select ut.feature_id, string_agg(distinct 'GO:' || ut.accession || ' ' || ut.name,', ') as go_terms from 
(
/*
 transitive list of protein GO terms from root
*/
select fg.name as gname,  pt.gene_id as feature_id, dxo.accession, cto.name from  chado.cvtermpath ctp, chado.cvterm cts, chado.cvterm cto, chado.cv cvs,  chado.cv cvo, chado.dbxref dxo,  chado.dbxref dxs, 
chado.analysisfeatureprop afp, chado.feature f, chado.analysisfeature af,  prot_table pt, chado.feature fg
where 
cvo.name in ('biological_process', 'cellular_component', 'molecular_function') and cvo.cv_id=cto.cv_id
and cvs.name in ('biological_process', 'cellular_component', 'molecular_function') and cvs.cv_id=cts.cv_id
and cto.dbxref_id=dxo.dbxref_id  
and cts.dbxref_id=dxs.dbxref_id  
and (ctp.object_id=cto.cvterm_id and ctp.subject_id=cts.cvterm_id)
and afp.type_id=cts.cvterm_id 
and af.analysisfeature_id = afp.analysisfeature_id
and af.feature_id = f.feature_id and pt.protein_id=f.feature_id 
and fg.feature_id=pt.gene_id

union
/*
 protein GO terms
*/
select f.name as gname, pt.gene_id as feature_id, dx.accession, c2.name from chado.analysisfeatureprop afp, chado.cvterm c2 , chado.cv,  chado.dbxref dx, chado.analysisfeature af, chado.feature f , prot_table pt
  where afp.type_id=c2.cvterm_id 
  and af.analysisfeature_id = afp.analysisfeature_id 
  and af.feature_id = f.feature_id 
  and cv.cv_id=c2.cv_id
  and cv.name in ('biological_process', 'cellular_component', 'molecular_function') and c2.dbxref_id=dx.dbxref_id 
  and pt.protein_id=f.feature_id

union

/*
  gene,mRNA GO terms
*/
select f.name as gname, f.feature_id as feature_id , dx.accession, c2.name from chado.analysisfeatureprop afp, chado.cvterm c2 , chado.cv,  chado.dbxref dx, chado.analysisfeature af, chado.feature f 
  where afp.type_id=c2.cvterm_id 
  and af.analysisfeature_id = afp.analysisfeature_id 
  and af.feature_id = f.feature_id 
  and cv.cv_id=c2.cv_id
  and cv.name in ('biological_process', 'cellular_component', 'molecular_function') and c2.dbxref_id=dx.dbxref_id 
  and f.type_id in (select cvterm_id from chado.cvterm where name in ('mRNA','gene'))

union

/*
 transitive list of gene,mRNA GO terms from root
*/
select f.name as gname, f.feature_id , dxo.accession , cto.name from chado.analysisfeatureprop afp, chado.analysisfeature af, chado.feature f , 
chado.cvtermpath ctp, chado.cvterm cto, chado.dbxref dxo 
  where afp.type_id=ctp.subject_id
  and ctp.object_id=cto.cvterm_id
  and af.analysisfeature_id = afp.analysisfeature_id 
  and af.feature_id = f.feature_id 
  and cto.dbxref_id=dxo.dbxref_id 
  and cto.cv_id in (select cv_id from chado.cv where name in  ('biological_process', 'cellular_component', 'molecular_function'))
  and ctp.cv_id in (select cv_id from chado.cv where name in  ('biological_process', 'cellular_component', 'molecular_function'))
  and f.type_id in (select cvterm_id from chado.cvterm where name in ('mRNA','gene'))
) ut 
group by ut.feature_id 
) as go_term on go_term.feature_id=F.feature_id 

/*
  assign Notes, description to gbterms
*/
left join ( select fp.feature_id, string_agg(fp.value,', ') as gbterm from chado.featureprop fp, chado.cvterm fpct 
        where fpct.cvterm_id=fp.type_id and fpct.name in ('Note','Description')
        group by fp.feature_id
) as gbterms on gbterms.feature_id=F.feature_id
/*
  Interppro terms for protein
*/
left join (select distinct pt.gene_id as feature_id , string_agg(dx.accession || ',' || c2.name,', ')  as interpro_term from chado.analysisfeatureprop afp, chado.cvterm c2 ,  chado.dbxref dx, chado.cv, chado.analysisfeature af, chado.feature f , prot_table pt
  where afp.type_id=c2.cvterm_id 
  and af.analysisfeature_id = afp.analysisfeature_id 
  and af.feature_id = f.feature_id 
  and cv.cv_id=c2.cv_id
  and cv.name in ('INTERPRO')  and c2.dbxref_id=dx.dbxref_id 
  and pt.protein_id=f.feature_id
  group by pt.gene_id
) as interproterms on interproterms.feature_id=F.feature_id

/*
  Interppro terms for gene,mRNA
*/
left join (select distinct f.feature_id as feature_id , string_agg(dx.accession || ',' || c2.name,', ')  as interpro_term from chado.analysisfeatureprop afp, chado.cvterm c2 ,  chado.dbxref dx, chado.cv, chado.analysisfeature af, chado.feature f 
  where afp.type_id=c2.cvterm_id 
  and af.analysisfeature_id = afp.analysisfeature_id 
  and af.feature_id = f.feature_id 
  and cv.cv_id=c2.cv_id
  and cv.name in ('INTERPRO')  and c2.dbxref_id=dx.dbxref_id 
  group by f.feature_id
) as interprotermsmrna on interprotermsmrna.feature_id=F.feature_id
where F.type_id IN (SELECT cvterm_id FROM chado.cvterm WHERE name IN ('gene','mRNA') AND cv_id = (SELECT cv_id FROM chado.cv WHERE name = 'sequence'))