DROP TABLE IF EXISTS xy_gene;
CREATE TABLE xy_gene (entrezgene_id INTEGER PRIMARY KEY);

CREATE TEMPORARY TABLE total_xy
SELECT
	t1.gid, t1.ftypeid,
	COUNT(*) AS total
FROM fdata t1
WHERE 	(t1.fref='chrX' OR t1.fref='chrY')
GROUP BY t1.gid, t1.ftypeid;

CREATE TEMPORARY TABLE potential_xy
SELECT
	t1.gid, t1.ftypeid,
	COUNT(*) AS total
FROM fdata t1
INNER JOIN fdata t2
WHERE 	t1.gid=t2.gid
	AND t1.fstart=t2.fstart
	AND t1.fstop=t2.fstop
	AND t1.fref='chrX'
	AND t2.fref='chrY'
GROUP BY t1.gid, t1.ftypeid;

CREATE TEMPORARY TABLE anomalous_xy
SELECT t1.gid, t1.total as potential_total, t2.total as total_total
FROM potential_xy t1
INNER JOIN total_xy t2
INNER JOIN ftype t3 ON t1.ftypeid=t3.ftypeid
WHERE 	t1.gid=t2.gid
	AND t1.ftypeid=t2.ftypeid
GROUP BY t1.gid, t1.total, t2.total
HAVING t1.total<>(t2.total/(2/COUNT(distinct t3.fsource)));

CREATE TEMPORARY TABLE gid_xy
SELECT t1.gid, t1.total as potential_total, t2.total as total_total
FROM potential_xy t1
INNER JOIN total_xy t2
INNER JOIN ftype t3 ON t1.ftypeid=t3.ftypeid
WHERE 	t1.gid=t2.gid
	AND t1.ftypeid=t2.ftypeid
GROUP BY t1.gid, t1.total, t2.total
HAVING t1.total=(t2.total/(2/COUNT(distinct t3.fsource)));

INSERT INTO xy_gene
SELECT DISTINCT t3.fattribute_value
FROM gid_xy t1
INNER JOIN fdata t2 ON t1.gid=t2.gid
INNER JOIN fattribute_to_feature t3 ON t2.fid=t3.fid
INNER JOIN fattribute t4 ON t3.fattribute_id=t4.fattribute_id
WHERE fattribute_name='EntrezGene';

