select
	concat("chr", t4.name) as ref,
	"Vega" as source,
	"exon" as method,
	t1.seq_region_start as start,
	t1.seq_region_end as end,
	"." as score,
	case
		when t1.seq_region_strand = 1 then "+"
		when t1.seq_region_strand = -1 then "-"
		else "."
	end as strand,
	"." as frame,
	case
		when t6.entrez_gene_id = 0 then
			concat("Native_id ", t3.stable_id, ";Build ", t5.version)
		else
			concat("Native_id ", t3.stable_id, ";Build ", t5.version, ";EntrezGene ", t6.entrez_gene_id)
	end as atts
from exon t1
inner join exon_transcript t2 on t1.exon_id=t2.exon_id
inner join transcript_stable_id t3 on t2.transcript_id=t3.transcript_id
inner join seq_region t4 on t1.seq_region_id=t4.seq_region_id
inner join coord_system t5 on t4.coord_system_id = t5.coord_system_id
inner join VegaInfo t6 on t3.stable_id = t6.stable_id
left outer join translation t7 on t2.transcript_id=t7.transcript_id
where t7.transcript_id is null
union
select
	concat("chr", t4.name) as ref,
	"Vega" as source,
	"CDS" as method,
	case
		when t1.seq_region_strand = 1 and t1.exon_id = t7.start_exon_id then t1.seq_region_start + t7.seq_start - 1
		when t1.seq_region_strand = -1 and t1.exon_id = t7.end_exon_id then t1.seq_region_end - t7.seq_end + 1
		else t1.seq_region_start
	end as start,
	case
		when t1.seq_region_strand = 1 and t1.exon_id = t7.end_exon_id then t1.seq_region_start + t7.seq_end - 1
		when t1.seq_region_strand = -1 and t1.exon_id = t7.start_exon_id then t1.seq_region_end - t7.seq_start + 1
		else t1.seq_region_end
	end as end,
	"." as score,
	case
		when t1.seq_region_strand = 1 then "+"
		when t1.seq_region_strand = -1 then "-"
		else "."
	end as strand,
	"." as frame,
	case
		when t6.entrez_gene_id = 0 then
			concat("Native_id ", t3.stable_id, ";Build ", t5.version)
		else
			concat("Native_id ", t3.stable_id, ";Build ", t5.version, ";EntrezGene ", t6.entrez_gene_id)
	end as atts
from exon t1
inner join exon_transcript t2 on t1.exon_id=t2.exon_id
inner join transcript_stable_id t3 on t2.transcript_id=t3.transcript_id
inner join seq_region t4 on t1.seq_region_id=t4.seq_region_id
inner join coord_system t5 on t4.coord_system_id = t5.coord_system_id
inner join VegaInfo t6 on t3.stable_id = t6.stable_id
inner join translation t7 on t2.transcript_id=t7.transcript_id
union
select
	concat("chr", t4.name) as ref,
	"Vega" as source,
	"UTR" as method,
	case
		when t1.seq_region_strand = 1 and t1.exon_id = t7.end_exon_id then t1.seq_region_start + t7.seq_end
		when t1.seq_region_strand = -1 and t1.exon_id = t7.start_exon_id then (t1.seq_region_end + 1) - (t7.seq_start - 1)
		else t1.seq_region_start
	end as start,
	case
		when t1.seq_region_strand = 1 and t1.exon_id = t7.start_exon_id then t1.seq_region_end - t7.seq_start + 1
		when t1.seq_region_strand = -1 and t1.exon_id = t7.end_exon_id then t1.seq_region_end - t7.seq_end
		else t1.seq_region_end
	end as end,
	"." as score,
	case
		when t1.seq_region_strand = 1 then "+"
		when t1.seq_region_strand = -1 then "-"
		else "."
	end as strand,
	"." as frame,
	case
		when t6.entrez_gene_id = 0 then
			concat("Native_id ", t3.stable_id, ";Build ", t5.version)
		else
			concat("Native_id ", t3.stable_id, ";Build ", t5.version, ";EntrezGene ", t6.entrez_gene_id)
	end as atts
from exon t1
inner join exon_transcript t2 on t1.exon_id=t2.exon_id
inner join transcript_stable_id t3 on t2.transcript_id=t3.transcript_id
inner join seq_region t4 on t1.seq_region_id=t4.seq_region_id
inner join coord_system t5 on t4.coord_system_id = t5.coord_system_id
inner join VegaInfo t6 on t3.stable_id = t6.stable_id
inner join translation t7 on t2.transcript_id=t7.transcript_id
where ((t1.exon_id = t7.start_exon_id and t7.seq_start <> 1) or t1.exon_id = t7.end_exon_id)
	and t7.start_exon_id <> t7.end_exon_id
order by ref, start;

