#!/bin/sh -ex
# get raw data file from DECIPHER

#cp $1  decipherRawNew.txt -p
sort $1| grep -v '#' | uniq >  decipherRawNew.txt 

hgsql hg19 -e 'drop table if exists decipherRawNew'
hgsql hg19 -e 'create table decipherRawNew select * from decipherRaw limit 0'

hgsql hg19 -e \
'load data local infile "decipherRawNew.txt" into table decipherRawNew'

hgsql hg19 -N -e 'select "chr", chr, start-1, end, id from decipherRawNew ' |\
sed -e 's/chr\t/chr/' |sort -u>j.tmp

cp j.tmp decipherNew.bed
# fix some out of range of entries
#cat j.tmp|sed -e 's/243000000/242951149/' |\
#sed -e 's/115090019/114142980/' >decipher.bed
#rm j.tmp

# Load decipher table
hgLoadBed hg19 decipherNew decipherNew.bed

# Create knownToDecipher table
hgMapToGene -noLoad -all -type="bed 4" hg19 decipherNew knownGene knownToDecipherNew
hgsql hg19 -e 'drop table if exists knownToDecipherNew'
hgsql hg19 -e 'create table knownToDecipherNew select * from knownToDecipher limit 0'
sort knownToDecipherNew.tab | uniq > knownToDecipherNew.uniq
hgsql hg19 -e \
'load data local infile "knownToDecipherNew.uniq" into table knownToDecipherNew'

# Create knownCanonToDecipher table

hgsql hg19 -N -e \
'select d.* from knownToDecipherNew d, knownCanonical c where c.transcript=d.name' >knownCanonToDecipherNew.tab

hgsql hg19 -e 'drop table if exists knownCanonToDecipherNew'
hgsql hg19 -e 'create table knownCanonToDecipherNew select * from knownCanonToDecipher limit 0'
hgsql hg19 -e 'load data local infile "knownCanonToDecipherNew.tab" into table knownCanonToDecipherNew'