Fix all chats 2

1 files changed, 13 insertions, 4 deletions

diff --git a/velascobot2/database.py b/velascobot2/database.py
index 156f1f5..720d10c 100644
--- a/velascobot2/database.py
+++ b/velascobot2/database.py
@@ -243,11 +243,20 @@ class Database:
 
     # I made an oopsie when converting the old jsons to sqlite. I will remove this in the next commit
     def fix_all_chats(self):
+        self.merge_and_rename_key('', ' ')
+
+    def merge_and_rename_key(self, old_key: str, new_key: str):
         with _SQLiteContextMgr(database=self.db_name) as db:
-            db.execute(
-                "UPDATE vocab SET key = ? WHERE key = ?",
-                ('', ' ')
-            )
+            db.execute("""
+                INSERT INTO vocab (chat_id, key, word, count)
+                SELECT chat_id, ?, word, count
+                FROM vocab
+                WHERE key = ?
+                ON CONFLICT(chat_id, key, word) DO UPDATE SET count = count + excluded.count
+            """, (new_key, old_key))
+
+            # delete old key entries
+            db.execute("DELETE FROM vocab WHERE key = ?", (old_key,))
 
     def get_all_chats(self) -> list[ChatData]:
         with _SQLiteContextMgr(database=self.db_name) as db: