import sqlite3

class Db:

    def __init__(self,database):
        self.database = database

    def main(self,database,type,command):
        try:
          cnx = self.connect(self.database)
          crs = cnx.cursor()
          result = []
          if type == 'DDL' or type == 'DML':
            crs.execute(command)
            cnx.commit()
          if type == 'DQL':
            rst = crs.execute(command)
            result.append(list(rst))
          cnx.close()
          return result[0]
        except:
          return 1

    def connect(self,database):
        try:
          cnx = sqlite3.connect(self.database)
          return cnx
        except:
          return 1

    def create_database(self,database):
        try:
          self.connect(self.database)
        except:
          return 1
  
    def create_table(self,table,columns):
        try:
          command = f'CREATE TABLE IF NOT EXISTS {table} ({columns});'
          self.main(self.database,'DDL',command)
        except:
          return 1

    def truncate_table(self,table):
        try:
          command = f'DELETE FROM {table};'
          self.main(self.database,'DML',command)
        except:
          return 1

    def drop_table(self,table):
        try:
          command = f'DROP TABLE {table};'
          self.main(self.database,'DDL',command)
        except:
          return 1

    def select(self,table,columns,where='',groupby='',orderby=''):
        try:
          vWhere = ''
          vGroupby = ''
          vOrderby = ''
          if len(where.strip()) > 0:
            vWhere = f'WHERE {where.replace(","," AND ")}'
          if len(groupby.strip()) > 0:
            vGroupby = f'GROUP BY {groupby}'
          if len(orderby.strip()) > 0:
            vOrderby = f'ORDER BY {orderby}'
          command = f'SELECT {columns} FROM {table} {vWhere} {vGroupby} {vOrderby};'
          result = self.main(self.database,'DQL',command)
          return result
        except:
          return 1

    def insert(self,table,columns,values):
        try:
          command = f'INSERT INTO {table} ({columns}) VALUES ({values});'
          self.main(self.database,'DML',command)
        except:
          return 1

    def update(self,table,set,where=''):
        try:
          vWhere = ''
          if len(where.strip()) > 0:
            vWhere = f'WHERE {where.replace(","," AND ")}'
          command = f'UPDATE {table} SET {set} {vWhere};'
          self.main(self.database,'DML',command)
        except:
          return 1

    def delete(self,table,where=''):
        try:
          vWhere = ''
          if len(where.strip()) > 0:
            vWhere = f'WHERE {where.replace(","," AND ")}'
          command = f'DELETE FROM {table} {vWhere};'
          self.main(self.database,'DML',command)
        except:
          return 1

    def sql(self,sql):
        try:
          command = sql.strip()
          result = self.main(self.database,'DQL',command)
          return result
        except:
          return 1
