from modules.module_base import ModuleBase
from bot import Bot
import sqlite3


class ModuleConversationListener(ModuleBase):
    def __init__(self, bot):
        ModuleBase.__init__(self, bot)
        self.name = "ConversationListener"
        self.connexion = sqlite3.connect('modules/resources/conversations.db')
        self.cursor = self.connexion.cursor()
        self.listUserId = self.getListUserId()

    def getListUserId(self):
        self.cursor.execute('SELECT user.id_user FROM user')
        listrows = self.cursor.fetchall()
        print(listrows)
        return listrows

    def insertUser(self, from_attr):
        self.cursor.execute('INSERT INTO user (id_user, first_name, last_name, username) VALUES (?,?,?,?)',
                            (from_attr['id'], from_attr['first_name'], from_attr['last_name'], from_attr['username']))
        pass

    def insertMessage(self, message_id, from_attr, date, chat, text):
        if from_attr["id"] not in self.listUserId:
            self.insertUser(from_attr)

        self.cursor.execute('INSERT INTO message (id_message, id_user, id_chat, date, text) VALUES (?,?,?,?,?)',
                            (message_id, from_attr['id'], chat['id'], date, text))

        pass

    # if you need both notify_text AND notify_command, don't forget to call super().notify_text(message_id, from_attr, date, chat, text) at the beginning of the inherited notify_text function
    def notify_text(self, message_id, from_attr, date, chat, text):
        self.insertMessage(message_id, from_attr, date, chat, text)
        pass

    def stop(self):
        self.connexion.commit()
        self.connexion.close()
@@ -119,16 +119,53 @@
 eBase):%0A
+    NUM_MESSAGES_BEFORE_COMMIT = 10%0A%0A
     def 
@@ -267,16 +267,198 @@
 stener%22%0A
+        self.initConnection()%0A        self.listUserId = %5B%5D%0A        self.counter = 0%0A%0A    def initConnection(self):%0A        try:%0A            print(%22Opened database successfully%22)%0A    
         
@@ -528,32 +528,36 @@
 ns.db')%0A        
+    
 self.cursor = se
@@ -578,32 +578,36 @@
 ursor()%0A        
+ 
+   
 self.listUserId 
@@ -633,226 +633,1291 @@
 d()%0A
-%0A    def getListUserId(self):%0A        self.cursor.execute('SELECT user.id_user FROM user')%0A        listrows = self.cursor.fetchall()%0A        print(listrows)%0A        return listrows%0A%0A    def insertUser(self, from_attr):
+        except:%0A%0A            with open(%22modules/resources/conversations.db%22):%0A                pass%0A            print(%22Created database successfully%22)%0A%0A            self.connexion = sqlite3.connect('modules/resources/conversations.db')%0A            self.cursor = self.connexion.cursor()%0A%0A            self.cursor.execute(%0A                %22CREATE TABLE 'message' ( 'id_message'%09INTEGER NOT NULL, 'id_user'%09INTEGER NOT NULL, 'id_chat'%09INTEGER NOT NULL, 'date' INTEGER, 'text' TEXT )%22)%0A            self.cursor.execute(%0A                %22CREATE TABLE 'user' ('id_user'%09INTEGER, 'first_name'%09TEXT, 'last_name'%09TEXT, 'username'%09TEXT )%22)%0A            print(%22Table created successfully%22)%0A%0A            self.connexion.commit()%0A%0A%0A    def getListUserId(self):%0A        self.cursor.execute('SELECT user.id_user FROM user')%0A        listrows = self.cursor.fetchall()%0A        print(listrows)%0A        return listrows%0A%0A    def insertUser(self, from_attr):%0A%0A        # Check if username and lastname are not set in Telegram%0A        if not ModuleBase.checkForAttribute(from_attr, 'username'):%0A            from_attr%5B'username'%5D = from_attr%5B'first_name'%5D%0A        if not ModuleBase.checkForAttribute(from_attr, 'last_name'):%0A            from_attr%5B'last_name'%5D = from_attr%5B'first_name'%5D%0A%0A        # Insert user in DB
 %0A   
@@ -2140,24 +2140,25 @@
 ame'%5D))%0A
+%0A
         
 pass%0A%0A  
@@ -2141,36 +2141,137 @@
 me'%5D))%0A%0A        
-pass
+self.connexion.commit()%0A%0A        # Add user in listUserID%0A        self.listUserId.append(from_attr%5B'id'%5D)
 %0A%0A    def insert
@@ -2613,29 +2613,186 @@
  text))%0A
-%0A        pass
+        self.counter += 1%0A        self.counter %25= ModuleConversationListener.NUM_MESSAGES_BEFORE_COMMIT%0A%0A        if self.counter == 0:%0A            self.connexion.commit()
 %0A%0A    # 
@@ -3209,8 +3209,9 @@
 .close()
+%0A

Insert conversations and users in DB - Create DB if needed