# make a connection
connection = sqlite3.connect("dmel.db")

# open the datasets
drome_genes = pd.read_csv("drome_genes.csv",header=0,index_col=0)
drome_gene_interactions = pd.read_csv("drome_gene_interactions.csv",header=0,index_col=0)
gene_expression_tissues = pd.read_csv("gene_expression_tissues.csv",header=0,index_col=0)
drome_gene_pathway = pd.read_csv("drome_gene_pathway.csv",header=0,index_col=0)

# to sqlite3
drome_genes.to_sql("Genes", connection, if_exists='replace',index=False)
drome_gene_interactions.to_sql("Interactions", connection, if_exists='replace',index=False)
gene_expression_tissues.to_sql("Expression", connection, if_exists='replace',index=False)
drome_gene_pathway.to_sql("Pathway", connection, if_exists='replace',index=False)

# queries
info=pd.read_sql_query("SELECT * FROM sqlite_master;", connection)
genes = pd.read_sql_query("select * from Genes;", connection)
pd.read_sql_query("SELECT expression from Expression where updown='Up' and tissue='Brain' UNION SELECT expression from Expression where updown='Up' and tissue='Larval CNS';", connection)
pd.read_sql_query("SELECT * from Expression where symbol='imd' and updown ='Up'",connection)

pd.read_sql_query("SELECT distinct tissue from Expression;", connection)
pd.read_sql_query("SELECT distinct pathway from Pathway;", connection)
pd.read_sql_query("SELECT symbol from Expression where updown='Up' and tissue='Brain' UNION SELECT symbol from Expression where updown='Up' and tissue='Larval CNS';", connection)
pd.read_sql_query("SELECT symbol from Expression where updown='Up' and tissue='Brain' INTERSECT SELECT symbol from Expression where updown='Up' and tissue='Larval CNS';", connection)
pd.read_sql_query("SELECT COUNT(symbol),chr from Genes GROUP BY chr;", connection)
pd.read_sql_query("SELECT Pathway.symbol,Pathway.pathway FROM Pathway JOIN Expression ON Pathway.symbol = Expression.symbol where updown='Up' and tissue='Brain'", connection)
pd.read_sql_query("SELECT distinct pathway FROM Pathway JOIN Expression ON Pathway.symbol = Expression.symbol where updown='Up' and tissue='Brain'", connection)

pd.read_sql_query("SELECT distinct relationship from Interactions;", connection)
pd.read_sql_query("SELECT distinct type from Interactions;", connection)

pd.read_sql_query("SELECT expression from Expression where tissue='Brain' ;", connection).mean()
pd.read_sql_query("SELECT AVG(expression) from Expression where tissue='Brain' ;", connection)
pd.read_sql_query("SELECT expression from Expression where tissue='Brain' ;", connection).median()
pd.read_sql_query("SELECT expression from Expression where tissue='Brain' ;", connection).std()

pd.read_sql_query("SELECT symbol from Expression where updown='Up' and tissue='Brain' and expression > 5.516247+3*10.512941;", connection)

pd.read_sql_query("SELECT distinct pathway FROM Pathway JOIN Expression ON Pathway.symbol = Expression.symbol where updown='Up' and tissue='Brain'", connection)


command = """
SELECT G.symbol AS symbol,
       G.stop - G.start AS size
FROM Genes G
JOIN Expression E
ON G.symbol = E.symbol
WHERE E.tissue='Brain'
AND E.updown='Up'
AND E.expression>30
AND G.chr = '2L'
"""
plt.hist(pd.read_sql_query(command,connection)["size"])
plt.show()



command = """
SELECT G1.symbol AS symbol1,
       G2.symbol AS symbol2
FROM Genes G1
JOIN Interactions I
ON G1.ID = I.id1
JOIN Genes G2
ON G2.ID = I.id2
WHERE I.type='genetic'
"""
pd.read_sql_query(command,connection)


command = """
SELECT DISTINCT
    G1.symbol AS symbol1,
    G2.symbol AS symbol2,
    E1.expression as exp1,
    E2.expression as exp2
FROM Genes G1
JOIN Interactions I
ON G1.ID = I.id1
JOIN Genes G2
ON G2.ID = I.id2
JOIN Expression E1
ON E1.symbol = G1.symbol
JOIN Expression E2
ON E2.symbol = G2.symbol
WHERE I.type='genetic'
AND E1.tissue='Brain'
AND E2.tissue='Brain'
AND E1.updown='Up'
AND E2.updown='Up'
AND E1.expression>35
AND E2.expression>35
"""

pd.read_sql_query(command, connection)
