import sqlite3


class DatabaseConnector(object):
    def __init__(self, filename=None):
        self._conn = None
        self._db = None
        if filename:
            self._conn = sqlite3.connect(filename)
            self._db = self._conn.cursor()
        else:
            raise NameError('Database filename not defined!')

    def get_all_spec_id_inchi(self, ion_type):
        sql = "SELECT Information.SpecID, Molecule.Inchi FROM Information,Molecule " \
              "WHERE ((Information.MolID==Molecule.ID) AND (Information.Type=='{ion_type}'))".format(
            ion_type=ion_type
        )
        self._db.execute(sql)
        result = self._db.fetchall()
        return result

    def get_all_spec_id_inchi_inchikey(self, ion_type):
        sql = "SELECT Information.SpecID, Molecule.Inchi,Molecule.Inchikey FROM Information,Molecule " \
              "WHERE ((Information.MolID==Molecule.ID) AND (Information.Type=='{ion_type}'))".format(
            ion_type=ion_type
        )
        self._db.execute(sql)
        result = self._db.fetchall()
        return result

    def get_all_spec_id_inchi_splash(self, ion_type):
        sql = "SELECT Information.SpecID, Molecule.Inchi,Information.Splash FROM Information,Molecule " \
              "WHERE ((Information.MolID==Molecule.ID) AND (Information.Type=='{ion_type}'))".format(
            ion_type=ion_type
        )
        self._db.execute(sql)
        result = self._db.fetchall()
        return result

    def get_spec_from_spec_id(self, spec_id):
        sql = "SELECT Spectrum.MZ,Spectrum.Intensity FROM Spectrum WHERE Spectrum.ID=={spec_id}".format(
            spec_id=spec_id
        )
        self._db.execute(sql)
        result = self._db.fetchall()
        return result

    def get_mol_id_from_spec_id(self, spec_id):
        sql = "SELECT Information.MolID FROM Information WHERE Information.SpecID=={spec_id}".format(
            spec_id=spec_id
        )
        self._db.execute(sql)
        result = self._db.fetchall()[0][0]
        return result

    def get_inchi_from_mol_id(self, mol_id):
        sql = "SELECT Molecule.Inchi FROM Molecule WHERE Molecule.ID=={mol_id}".format(
            mol_id=mol_id
        )
        self._db.execute(sql)
        result = self._db.fetchall()[0][0]
        return result.encode('utf-8')

    def get_inchi_from_spec_id(self, spec_id):
        sql = "SELECT Molecule.Inchi FROM Molecule WHERE Molecule.ID==" \
              "(SELECT Information.MolID FROM Information WHERE Information.SpecID=={spec_id})".format(
            spec_id=spec_id
        )
        self._db.execute(sql)
        result = self._db.fetchall()[0][0]
        return result.encode('utf-8')

    pass
