#!/usr/bin/python3
import psycopg2
from Object_classes import Gen
from Object_classes import Eiwit
from Object_classes import RNA
from Object_classes import EC_nummer
from Object_classes import Pathway
from Object_classes import Cancer_pathway

import Insert_functions


# Een functie om met een database te connecten, het returned de connection en de cursor.
def connect(host, dbname, user, password):
    conn_string = "host= '%s' dbname= '%s' user= '%s' password='%s'" % (host, dbname, user, password)
    print("Connecting to database...")
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()
    print("Connected!\n")
    return conn, cursor

# Leegt de database als hij gecalled wordt, en verbonden is.
def empty_database(ed_cursor):
    # drop de tabellen als ze al bestaan
    ed_cursor.execute("""DROP TABLE IF EXISTS gen_A6 CASCADE;""")
    ed_cursor.execute("""DROP TABLE IF EXISTS RNA_A6 CASCADE;""")
    ed_cursor.execute("""DROP TABLE IF EXISTS eiwit_A6 CASCADE;""")
    ed_cursor.execute("""DROP TABLE IF EXISTS eiwit_EC_A6 CASCADE;""")
    ed_cursor.execute("""DROP TABLE IF EXISTS pathway_A6 CASCADE;""")
    ed_cursor.execute("""DROP TABLE IF EXISTS EC_pathway_A6 CASCADE;""")
    ed_cursor.execute("""DROP TABLE IF EXISTS EC_nummer_A6 CASCADE;""")
    ed_cursor.execute("""DROP TABLE IF EXISTS Pathway_Cancer_A6 CASCADE;""")
    ed_cursor.execute("""DROP TABLE IF EXISTS pathway_SHR_A6 CASCADE;""")
    print("Dropped previous tables with the same name if they existed...")

# Roept de verwijderfunctie aan, maakt de tabellen en roept vervolgens de foreign keys en constaints functie aan.
def tabellen_maken(conn, cursor):
    empty_database(cursor)
    sql = """
    CREATE TABLE gen_A6(
        Entry_id INT PRIMARY KEY,
        Gen_id INT,
        Gen_naam VARCHAR(100),
        Gen_splicing_count_begin INT,
        Gen_splicing_count_end INT
    );



    CREATE TABLE Pathway_Cancer_A6(
        Cancer_id INT PRIMARY KEY ,
        KO_code VARCHAR(7),
        Naam_cancer_protein TEXT
    );

    CREATE TABLE Pathway_A6(
	    Pathway TEXT PRIMARY KEY
    );



    CREATE TABLE EC_nummer_A6(
        EC_nummer VARCHAR(25) PRIMARY KEY,
        Reactie TEXT,

        Functie TEXT

    );

    --Koppel pathway en EC nummer
    CREATE TABLE EC_pathway_A6(
        Pathway TEXT REFERENCES Pathway_A6 (Pathway),
        EC_nummer VARCHAR(25) REFERENCES EC_nummer_A6 (EC_nummer),
	    PRIMARY KEY (Pathway, EC_nummer)
    );

    CREATE TABLE eiwit_A6(
        Eiwit_naam VARCHAR(200),
        Eiwit_sequence TEXT,
        Eiwit_id TEXT PRIMARY KEY,
        KO_code VARCHAR(7)
    );
    CREATE TABLE pathway_SHR_A6(
        Eiwit_id TEXT REFERENCES eiwit_A6(Eiwit_id),
        Pathway TEXT REFERENCES Pathway_A6 (Pathway),
        SHR_code VARCHAR(10),
	    PRIMARY KEY (Pathway, SHR_code)
    );



    --Koppel gen en eiwit
    CREATE TABLE rna_a6(
        Gen_id INT,
        Entry_id INT,
        Gen_splicing_count_begin INT ,
        Gen_splicing_count_end INT ,
        RNA_code VARCHAR(15),
        Eiwit_id TEXT REFERENCES eiwit_A6 (Eiwit_id),
        RNA_sequence TEXT,
        PRIMARY KEY (Entry_id, Gen_id, Gen_splicing_count_begin, Gen_splicing_count_end)
    );


    """
    cursor.execute(sql)
    conn.commit()
    print("New tables created!")



# gecodeerd om eiwit_gen_naam.txt uit te lezen en lists te returnen voor alle elementen
# (make sure dat er geen spaties of tabs tussen de komma's zitten!)
def bestanduitlezer(bestandnaam):
    inhoud = open(str(bestandnaam), "r")
    big_gen_list = []
    big_eiwit_list = []
    big_rna_list = []
    for regel in inhoud:
        regel.lstrip()
        inhoud_lijst = regel.split("@")
        inhoud_lijst.remove("\n")
        gen_list = []
        eiwit_list = []
        rna_list = []
        eiwit_list.append(inhoud_lijst[1])
        eiwit_list.append(inhoud_lijst[2])
        eiwit_list.append(inhoud_lijst[0])

        if eiwit_list not in big_eiwit_list:
            big_eiwit_list.append(eiwit_list)

        gen_list.append(inhoud_lijst[7])
        gen_list.append(inhoud_lijst[6])

        gen_list.append(inhoud_lijst[3].split("..")[0])
        gen_list.append(inhoud_lijst[3].split("..")[1])

        big_gen_list.append(gen_list)



        rna_list.append(inhoud_lijst[4])
        rna_list.append(inhoud_lijst[5])

        big_rna_list.append(rna_list)


    return big_gen_list, big_eiwit_list, big_rna_list


def cancerbestanduitlezer(bestandnaam):
    big_cancer_list = []
    inhoud = open(str(bestandnaam), "r")
    for regel in inhoud:
        cancer_list = []
        regel.lstrip()
        inhoud_lijst = regel.split("@")
        inhoud_lijst.remove("\n")
        cancer_list.append(inhoud_lijst[0])
        cancer_list.append(inhoud_lijst[1])
        big_cancer_list.append(cancer_list)
    return big_cancer_list


def pathwaybestanduitlezer(il_file):
    infile = open(il_file, 'r')
    bestand = infile.readlines()
    infile.close()
    gegevens = bestand
    big_pathway_list = []
    Eiwit_KO_Code_list = []
    big_ec_list=[]
    ec_dict={}
    for i in range(len(gegevens)):
        pathway_list = []
        data = gegevens[i].split('@')

        shr, pathway, KO_code = data[0], data[1].split('|'), data[3]

        if data[2] != " " and pathway != "":
            ec_nummer=list(data[2].strip("ec:").split(" "))


            for x in range(len(ec_nummer)):

                if ec_nummer[x] not in ec_dict.keys():
                    ec_dict[ec_nummer[x]] = pathway

                    # print(ec_dict.values(), "hi")



                else:
                    for key, value in ec_dict.items():
                        if key == ec_nummer[x]:
                            for y in pathway:
                                if y not in value:
                                    ec_dict[ec_nummer[x]].append(pathway)

        # print(ec_dict)

        for key, value in ec_dict.items():
            lijst=[]
            lijst.append(key)
            lijst.append(value)
            print(lijst[1][0])
            big_ec_list.append(lijst)
            # print(key, value)
        #





        pathway_list.append(shr)
        pathway_list.append(pathway)
        big_pathway_list.append(pathway_list)
        Eiwit_KO_Code_list.append(KO_code)
    return big_pathway_list, Eiwit_KO_Code_list, big_ec_list


def ECbestanduitlezer(EC_file):
    infile = open(EC_file, 'r')
    gegevens = infile.readlines()
    infile.close()
    big_EC_list = []
    ec_nummer_lijst =[]
    ec_dict={}

    for i in range(len(gegevens)):
        EC_list = []
        data = gegevens[i].split('@')
        EC, reactie, uitleg = data[0], data[1].split('|')[0], data[2]



        if EC not in ec_nummer_lijst:
            ec_nummer_lijst.append(EC)
            EC_list.append(EC)
            EC_list.append(reactie)
            EC_list.append(uitleg)
            big_EC_list.append(EC_list)


    return big_EC_list


# Input zijn 2 lijsten de functie maakt er objects van die die vervolgens in 2 andere lijsten stopt en dan returned
def packager(p_eiwit_lijst, p_gen_lijst, p_rna_lijst, p_pathway_lijst, p_EC_lijst, p_cancer_lijst,
             p_Eiwit_KO_Code_list):
    genobjectlist, eiwitobjectlist, rna_objectlist, EC_objectlist, pathway_objectlist, cancer_objectlist,only_pathways = [], [], [], [], [], [],[]
    for genlijst in p_gen_lijst:
        gen_temp_object = Gen()
        gen_temp_object.Setgen_id(genlijst[0])
        gen_temp_object.Setgen_naam(genlijst[1])
        gen_temp_object.Setgen_splicing_count(genlijst[2:4])
        genobjectlist.append(gen_temp_object)
    for eiwitlijst in p_eiwit_lijst:
        eiwit_temp_object = Eiwit()
        eiwit_temp_object.Seteiwit_naam(eiwitlijst[0])
        eiwit_temp_object.Seteiwit_sequence(eiwitlijst[1])
        eiwit_temp_object.Seteiwit_id(eiwitlijst[2])
        for code in p_Eiwit_KO_Code_list:
            eiwit_temp_object.SetKO_code(code)
        eiwitobjectlist.append(eiwit_temp_object)
    for rnalijst in p_rna_lijst:
        rna_temp_object = RNA()
        rna_temp_object.SetRNA_sequence(rnalijst[1])
        rna_temp_object.SetRNA_code(rnalijst[0])
        rna_objectlist.append(rna_temp_object)
    for eclijst in p_EC_lijst:
        EC_temp_object = EC_nummer()
        EC_temp_object.SetEC_nummer(eclijst[0])
        EC_temp_object.Setuitleg(eclijst[1])
        EC_temp_object.Setreactie(eclijst[2])
        EC_objectlist.append(EC_temp_object)
    for pathwaylijst in p_pathway_lijst:
        pathway_temp_object = Pathway()
        pathway_temp_object.Setpathway(pathwaylijst[1])
        pathway_temp_object.SetSHR(pathwaylijst[0])
        pathway_objectlist.append(pathway_temp_object)
    for cancerlijst in p_cancer_lijst:
        cancer_temp_object = Cancer_pathway()
        cancer_temp_object.SetKO_code(cancerlijst[0])
        cancer_temp_object.Setnaam_prot(cancerlijst[1])
        cancer_objectlist.append(cancer_temp_object)

    for x in p_pathway_lijst:
        for y in x[1]:
            if y !="" and y not in only_pathways:
                only_pathways.append(y)

    return genobjectlist, eiwitobjectlist, rna_objectlist, EC_objectlist, pathway_objectlist, cancer_objectlist, only_pathways


def main():
    conn, cursor = connect('145.97.16.241', 'bpapgea6_db', 'bpapgea6',
                           'BpapgeA6')  # <---- verander deze string voor een andere database!
    tabellen_maken(conn, cursor)

    pathway_list, Eiwit_KO_Code_list, ec_list = pathwaybestanduitlezer("kegg_pathway_EC.txt")
    EC_list = ECbestanduitlezer("kegg_reactie_functie.txt")
    gen_list, eiwit_list, rna_list = bestanduitlezer("tabel_info_nieuw.txt")
    cancer_list = cancerbestanduitlezer("cancer_pathway.txt")

    genobjectlist, eiwitobjectlist, rna_objectlist, EC_objectlist, pathway_objectlist, cancer_objectlist, only_pathway_list = \
        packager(eiwit_list, gen_list, rna_list, pathway_list, EC_list, cancer_list, Eiwit_KO_Code_list)



    Insert_functions.geninserts(conn, cursor, genobjectlist)
    Insert_functions.pathwayinserts(conn, cursor, only_pathway_list)
    Insert_functions.eiwitinserts(conn, cursor, eiwitobjectlist)

    Insert_functions.cancerpathwayinserts(conn, cursor, cancer_objectlist)
    Insert_functions.pathway_SHR_inserts(conn, cursor, pathway_objectlist, eiwitobjectlist)
    Insert_functions.ec_nummerinserts(conn, cursor, EC_objectlist)
    Insert_functions.rnainserts(conn, cursor, genobjectlist, rna_objectlist, eiwitobjectlist)
    Insert_functions.koppeltabelinserts(conn, cursor, ec_list)


    print("Done!")

    conn.close()


if __name__ == "__main__":
    main()