try:
    import os, sqlite3
except ImportError as err:
    print (err)
    os._exit(1)

class DB:
    __instance = None
    def __new__(cls, *args, **kwargs):
        if (cls.__instance is None):
            cls.__instance = super(DB, cls).__new__(cls)
        return cls.__instance
    
    def __init__(self, path):
        file = path + "/lensdb.db"
        initialize = os.path.isfile(file)
        self.conn = sqlite3.connect(path + "/lensdb.db")
        self.conn.execute("PRAGMA foreign_keys = '1'")
        self.conn.commit()
        if initialize is False:
           self.init_db() 
    
    def init_db(self):
        cur = self.conn.cursor()
        cur.execute('''drop table if exists lens;''')
        cur.execute('''drop table if exists lensmaker;''')
        cur.execute('''
            create table lensmaker(
            id integer primary key not null,
            maker text not null);
        ''')
        cur.execute('''
            create table lens(
            id integer primary key not null,
            sn int not null,
            maker int not null,
            description text not null,
            notes text,
            constraint fk_maker foreign key (maker) references lensmaker (id)
            on delete cascade);
        ''')
        self.conn.commit()
    
    def addMaker(self, maker):
        cur = self.conn.cursor()
        cur.execute("insert into lensmaker (maker) values (?)", (maker, ))
        self.conn.commit()
        
    def deleteMaker(self, maker):
        cur = self.conn.cursor()
        cur.execute("delete from lensmaker where maker = ?", (maker, ))
        self.conn.commit()

    def addLens(self, maker, tuple):
        cur = self.conn.cursor()
        cur.execute("select id from lensmaker where maker = ?", (maker, ))
        lensmaker = cur.fetchone()
        if lensmaker is None:
            raise Exception(maker + ": not found")
        cur.execute("insert into lens (maker, sn, description, notes) values (?, ?, ?, ?)", lensmaker + tuple)
        self.conn.commit()
        cur.execute("select last_insert_rowid();")
        id = cur.fetchone()
        return id[0]

    def updateLens(self, tuple):
        cur = self.conn.cursor()
        cur.execute("update lens set sn = ?, description = ?, notes = ? where id = ?", tuple)
        self.conn.commit()
        
    def deleteLens(self, id):
        cur = self.conn.cursor()
        cur.execute("delete from lens where id = ?", (id, ))
        self.conn.commit()
        
    def getMakers(self):
        cur = self.conn.cursor()
        cur.execute("select maker from lensmaker")
        rows = cur. fetchall()
        return rows

    def getLenses(self, maker):
        cur = self.conn.cursor()
        if type(maker) == str:
            cur.execute("select id from lensmaker where maker = ?", (maker, ))
            lensmaker = cur.fetchone()
        else:
            lensmaker = (maker, )
        cur.execute("select sn, description, notes, id from lens where maker = ?", lensmaker)
        rows = cur. fetchall()
        return rows
        
    def getLensByPattern(self, pattern):
        cur = self.conn.cursor()
        sql = """
        select m.maker, l.sn, l.description, l.notes, l.id from lensmaker m, lens l
        where l.description like ? and 
        m.id = l.maker COLLATE NOCASE
        """
        cur.execute(sql, ('%'+pattern+'%', ))
        rows = cur. fetchall()
        return rows        

    def getLensByMaker(self, maker):
        cur = self.conn.cursor()
        sql = """
        select m.maker, l.sn, l.description, l.notes, l.id from lensmaker m, lens l
        where l.maker = ? and 
        m.id = l.maker
        """
        if type(maker) == str:
            cur.execute("select id from lensmaker where maker = ? COLLATE NOCASE", (maker, ))
            lensmaker = cur.fetchone()
        else:
            lensmaker = (maker, )
        #cur.execute("select sn, description, notes, id from lens where maker = ?", lensmaker)
        cur.execute(sql, lensmaker)
        rows = cur. fetchall()
        return rows        

    def getLensByMakerPattern(self, maker, pattern):
        cur = self.conn.cursor()
        sql = """
        select m.maker, l.sn, l.description, l.notes, l.id from lensmaker m, lens l
        where l.description like ? and l.maker = ? and
        m.id = l.maker COLLATE NOCASE
        """
        if type(maker) == str:
            cur.execute("select id from lensmaker where maker = ? COLLATE NOCASE", (maker, ))
            lensmaker = cur.fetchone()
        else:
            lensmaker = (maker, )
        #cur.execute("select sn, description, notes, id from lens where description like ? and maker = ? COLLATE NOCASE", ('%'+pattern+'%', ) + lensmaker)
        cur.execute(sql, ('%'+pattern+'%', ) + lensmaker)
        rows = cur. fetchall()
        return rows        

    def __del__(self):
        try:
            self.conn.commit()
            self.conn.close()
        except Exception:
            pass