[RFC PATCH v2 02/17] telegram: Refactor Telegram bot database method

[Muhammad Rizki <kiizuha@gnuweeb.org>]

Refactor the Telegram bot database to make it more clean and
manageable to maintain.

Signed-off-by: Muhammad Rizki <kiizuha@gnuweeb.org>
---
 daemon/telegram/database/__init__.py          |   7 +
 daemon/telegram/database/core.py              |  20 ++
 daemon/telegram/database/methods/__init__.py  |  17 ++
 .../database/methods/deletion/__init__.py     |  14 ++
 .../database/methods/deletion/delet_atom.py   |  15 ++
 .../methods/deletion/delete_broadcast.py      |  15 ++
 .../database/methods/getter/__init__.py       |  18 ++
 .../database/methods/getter/get_atom_urls.py  |  21 ++
 .../methods/getter/get_broadcast_chats.py     |  21 ++
 .../database/methods/getter/get_email_id.py   |  62 +++++
 .../methods/getter/get_telegram_reply.py      |  33 +++
 .../database/methods/insertion/__init__.py    |  18 ++
 .../database/methods/insertion/insert_atom.py |  27 +++
 .../methods/insertion/insert_broadcast.py     |  56 +++++
 .../methods/insertion/insert_email.py         |  27 +++
 .../methods/insertion/insert_telegram.py      |  21 ++
 daemon/telegram/db.sql                        |  18 +-
 daemon/telegram/packages/client.py            |   4 +-
 .../packages/plugins/commands/manage_atom.py  |   2 +-
 .../plugins/commands/manage_broadcast.py      |   2 +-
 daemon/telegram/scraper/bot.py                |   8 +-
 daemon/telegram/scraper/db.py                 | 217 ------------------
 22 files changed, 409 insertions(+), 234 deletions(-)
 create mode 100644 daemon/telegram/database/__init__.py
 create mode 100644 daemon/telegram/database/core.py
 create mode 100644 daemon/telegram/database/methods/__init__.py
 create mode 100644 daemon/telegram/database/methods/deletion/__init__.py
 create mode 100644 daemon/telegram/database/methods/deletion/delet_atom.py
 create mode 100644 daemon/telegram/database/methods/deletion/delete_broadcast.py
 create mode 100644 daemon/telegram/database/methods/getter/__init__.py
 create mode 100644 daemon/telegram/database/methods/getter/get_atom_urls.py
 create mode 100644 daemon/telegram/database/methods/getter/get_broadcast_chats.py
 create mode 100644 daemon/telegram/database/methods/getter/get_email_id.py
 create mode 100644 daemon/telegram/database/methods/getter/get_telegram_reply.py
 create mode 100644 daemon/telegram/database/methods/insertion/__init__.py
 create mode 100644 daemon/telegram/database/methods/insertion/insert_atom.py
 create mode 100644 daemon/telegram/database/methods/insertion/insert_broadcast.py
 create mode 100644 daemon/telegram/database/methods/insertion/insert_email.py
 create mode 100644 daemon/telegram/database/methods/insertion/insert_telegram.py
 delete mode 100644 daemon/telegram/scraper/db.py

diff --git a/daemon/telegram/database/__init__.py b/daemon/telegram/database/__init__.py
new file mode 100644
index 0000000..930e3d9
--- /dev/null
+++ b/daemon/telegram/database/__init__.py
@@ -0,0 +1,7 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+#
+
+
+from .core import DB
diff --git a/daemon/telegram/database/core.py b/daemon/telegram/database/core.py
new file mode 100644
index 0000000..c34d7a8
--- /dev/null
+++ b/daemon/telegram/database/core.py
@@ -0,0 +1,20 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+# Copyright (C) 2022  Ammar Faizi <ammarfaizi2@gnuweeb.org>
+#
+
+
+from .methods import DBMethods
+
+
+class DB(DBMethods):
+	def __init__(self, conn):
+		self.conn = conn
+		self.conn.autocommit = True
+		self.cur = self.conn.cursor(buffered=True)
+
+
+	def __del__(self):
+		self.cur.close()
+		self.conn.close()
diff --git a/daemon/telegram/database/methods/__init__.py b/daemon/telegram/database/methods/__init__.py
new file mode 100644
index 0000000..961b4e0
--- /dev/null
+++ b/daemon/telegram/database/methods/__init__.py
@@ -0,0 +1,17 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+# Copyright (C) 2022  Ammar Faizi <ammarfaizi2@gnuweeb.org>
+#
+
+
+from .deletion import Deletion
+from .getter import Getter
+from .insertion import Insertion
+
+
+class DBMethods(
+	Deletion,
+	Getter,
+	Insertion
+): pass
diff --git a/daemon/telegram/database/methods/deletion/__init__.py b/daemon/telegram/database/methods/deletion/__init__.py
new file mode 100644
index 0000000..b206929
--- /dev/null
+++ b/daemon/telegram/database/methods/deletion/__init__.py
@@ -0,0 +1,14 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+#
+
+
+from .delet_atom import DeleteAtom
+from .delete_broadcast import DeleteBroadcast
+
+
+class Deletion(
+	DeleteAtom,
+	DeleteBroadcast
+): pass
diff --git a/daemon/telegram/database/methods/deletion/delet_atom.py b/daemon/telegram/database/methods/deletion/delet_atom.py
new file mode 100644
index 0000000..d8ad4bf
--- /dev/null
+++ b/daemon/telegram/database/methods/deletion/delet_atom.py
@@ -0,0 +1,15 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+#
+
+
+class DeleteAtom:
+
+	def delete_atom(self, atom: str):
+		q = """
+			DELETE FROM tg_atoms
+			WHERE url = %(atom)s
+		"""
+		self.cur.execute(q, {"atom": atom})
+		return self.cur.rowcount > 0
diff --git a/daemon/telegram/database/methods/deletion/delete_broadcast.py b/daemon/telegram/database/methods/deletion/delete_broadcast.py
new file mode 100644
index 0000000..d076dec
--- /dev/null
+++ b/daemon/telegram/database/methods/deletion/delete_broadcast.py
@@ -0,0 +1,15 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+#
+
+
+class DeleteBroadcast:
+
+	def delete_broadcast(self, chat_id: int):
+		q = """
+			DELETE FROM tg_broadcasts
+			WHERE chat_id = %(chat_id)s
+		"""
+		self.cur.execute(q, {"chat_id": chat_id})
+		return self.cur.rowcount > 0
diff --git a/daemon/telegram/database/methods/getter/__init__.py b/daemon/telegram/database/methods/getter/__init__.py
new file mode 100644
index 0000000..e978c72
--- /dev/null
+++ b/daemon/telegram/database/methods/getter/__init__.py
@@ -0,0 +1,18 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+#
+
+
+from .get_atom_urls import GetAtomURL
+from .get_broadcast_chats import GetBroadcastChats
+from .get_email_id import GetEmailID
+from .get_telegram_reply import GetTelegramReply
+
+
+class Getter(
+	GetAtomURL,
+	GetBroadcastChats,
+	GetEmailID,
+	GetTelegramReply
+): pass
diff --git a/daemon/telegram/database/methods/getter/get_atom_urls.py b/daemon/telegram/database/methods/getter/get_atom_urls.py
new file mode 100644
index 0000000..04a9315
--- /dev/null
+++ b/daemon/telegram/database/methods/getter/get_atom_urls.py
@@ -0,0 +1,21 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+#
+
+
+class GetAtomURL:
+
+	def get_atom_urls(self):
+		'''
+		Get lore kernel raw email URLs.
+                - Return list of raw email URLs: `List[str]`
+		'''
+		q = """
+			SELECT tg_atoms.url
+			FROM tg_atoms
+		"""
+		self.cur.execute(q)
+		urls = self.cur.fetchall()
+
+		return [u[0] for u in urls]
diff --git a/daemon/telegram/database/methods/getter/get_broadcast_chats.py b/daemon/telegram/database/methods/getter/get_broadcast_chats.py
new file mode 100644
index 0000000..d92e879
--- /dev/null
+++ b/daemon/telegram/database/methods/getter/get_broadcast_chats.py
@@ -0,0 +1,21 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+#
+
+
+class GetBroadcastChats:
+
+	def get_broadcast_chats(self):
+		'''
+		Get broadcast chats that are currently
+                listening for new email.
+                - Return list of chat object: `List[Object]`
+		'''
+		q = """
+			SELECT *
+			FROM tg_broadcasts
+		"""
+		self.cur.execute(q)
+
+		return self.cur.fetchall()
diff --git a/daemon/telegram/database/methods/getter/get_email_id.py b/daemon/telegram/database/methods/getter/get_email_id.py
new file mode 100644
index 0000000..44a6179
--- /dev/null
+++ b/daemon/telegram/database/methods/getter/get_email_id.py
@@ -0,0 +1,62 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+# Copyright (C) 2022  Ammar Faizi <ammarfaizi2@gnuweeb.org>
+#
+
+
+class GetEmailID:
+
+	def get_email_id(self, email_id, chat_id):
+		'''
+		Determine whether the email needs to be sent to @tg_chat_id.
+		 - Return an email id (PK) if it needs to be sent
+		 - Return None if it doesn't need to be sent
+		'''
+		if self.__is_sent(email_id, chat_id):
+			return
+
+		res = self.__email_id(email_id)
+		if not bool(res):
+			return
+
+		return int(res[0])
+
+
+	def __is_sent(self, email_id, chat_id):
+		'''
+		Checking if this email has already been sent
+		or not.
+		 - Return True if it's already been sent
+		'''
+
+		q = """
+			SELECT tg_emails.id, tg_mail_msg.id FROM tg_emails LEFT JOIN tg_mail_msg
+			ON tg_emails.id = tg_mail_msg.email_id
+			WHERE tg_emails.message_id = %(email_id)s
+			AND tg_mail_msg.chat_id = %(chat_id)s
+			LIMIT 1
+		"""
+
+		self.cur.execute(q, {
+			"email_id": email_id,
+			"chat_id": chat_id
+		})
+
+		res = self.cur.fetchone()
+		return bool(res)
+
+
+	def __email_id(self, email_id):
+		'''
+		Get the email id if match with the email message_id.
+		 - Return the result if it's match and exists
+		'''
+
+		q = """
+			SELECT id FROM tg_emails WHERE message_id = %(email_id)s
+		"""
+
+		self.cur.execute(q, {"email_id": email_id})
+		res = self.cur.fetchone()
+		return res
diff --git a/daemon/telegram/database/methods/getter/get_telegram_reply.py b/daemon/telegram/database/methods/getter/get_telegram_reply.py
new file mode 100644
index 0000000..94e3138
--- /dev/null
+++ b/daemon/telegram/database/methods/getter/get_telegram_reply.py
@@ -0,0 +1,33 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+# Copyright (C) 2022  Ammar Faizi <ammarfaizi2@gnuweeb.org>
+#
+
+
+class GetTelegramReply:
+
+	def get_reply_id(self, email_msg_id, chat_id):
+		'''
+		Get Telegram message ID sent match with
+                email message ID and Telegram chat ID.
+                - Return Telegram message ID if exists: `int`
+                - Return None if not exists`
+		'''
+		q = """
+			SELECT tg_mail_msg.tg_msg_id
+			FROM tg_emails INNER JOIN tg_mail_msg
+			ON tg_emails.id = tg_mail_msg.email_id
+			WHERE tg_emails.message_id = %(email_msg_id)s
+			AND tg_mail_msg.chat_id = %(chat_id)s
+		"""
+
+		self.cur.execute(q, {
+                        "email_msg_id": email_msg_id,
+                        "chat_id": chat_id
+                })
+		res = self.cur.fetchone()
+		if not bool(res):
+			return None
+
+		return res[0]
diff --git a/daemon/telegram/database/methods/insertion/__init__.py b/daemon/telegram/database/methods/insertion/__init__.py
new file mode 100644
index 0000000..3604f82
--- /dev/null
+++ b/daemon/telegram/database/methods/insertion/__init__.py
@@ -0,0 +1,18 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+# Copyright (C) 2022  Ammar Faizi <ammarfaizi2@gnuweeb.org>
+#
+
+from .insert_atom import InsertAtom
+from .insert_broadcast import InsertBroadcast
+from .insert_email import InsertEmail
+from .insert_telegram import InsertTelegram
+
+
+class Insertion(
+	InsertAtom,
+	InsertBroadcast,
+	InsertEmail,
+	InsertTelegram
+): pass
diff --git a/daemon/telegram/database/methods/insertion/insert_atom.py b/daemon/telegram/database/methods/insertion/insert_atom.py
new file mode 100644
index 0000000..ac068ae
--- /dev/null
+++ b/daemon/telegram/database/methods/insertion/insert_atom.py
@@ -0,0 +1,27 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+# Copyright (C) 2022  Ammar Faizi <ammarfaizi2@gnuweeb.org>
+#
+
+
+from mysql.connector import errors
+from datetime import datetime
+
+
+class InsertAtom:
+
+	def save_atom(self, atom: str):
+		try:
+			return self.__insert_atom(atom)
+		except errors.IntegrityError:
+			#
+			# Duplicate data, skip!
+			#
+			return None
+
+
+	def __insert_atom(self, atom: str):
+		q = "INSERT INTO tg_atoms (url, created_at) VALUES (%s, %s)"
+		self.cur.execute(q, (atom, datetime.utcnow()))
+		return self.cur.lastrowid
diff --git a/daemon/telegram/database/methods/insertion/insert_broadcast.py b/daemon/telegram/database/methods/insertion/insert_broadcast.py
new file mode 100644
index 0000000..11ebc6e
--- /dev/null
+++ b/daemon/telegram/database/methods/insertion/insert_broadcast.py
@@ -0,0 +1,56 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+# Copyright (C) 2022  Ammar Faizi <ammarfaizi2@gnuweeb.org>
+#
+
+
+from mysql.connector import errors
+from datetime import datetime
+
+
+class InsertBroadcast:
+
+	def save_broadcast(
+		self,
+		chat_id: int,
+		name: str,
+		type: str,
+		created_at: "datetime",
+		username: str = None,
+		link: str = None,
+	):
+		try:
+			return self.__insert_broadcast(
+				chat_id=chat_id,
+				name=name,
+				type=type,
+				created_at=created_at,
+				username=username,
+				link=link
+			)
+		except errors.IntegrityError:
+			#
+			# Duplicate data, skip!
+			#
+			return None
+
+
+	def __insert_broadcast(
+		self,
+		chat_id: int,
+		name: str,
+		type: str,
+		created_at: "datetime",
+		username: str = None,
+		link: str = None,
+	):
+		q = """
+			INSERT INTO tg_broadcasts
+			(chat_id, username, name, type, link, created_at)
+			VALUES
+			(%s, %s, %s, %s, %s, %s)
+		"""
+		values = (chat_id, username, name, type, link, created_at)
+		self.cur.execute(q, values)
+		return self.cur.lastrowid
diff --git a/daemon/telegram/database/methods/insertion/insert_email.py b/daemon/telegram/database/methods/insertion/insert_email.py
new file mode 100644
index 0000000..adbe86b
--- /dev/null
+++ b/daemon/telegram/database/methods/insertion/insert_email.py
@@ -0,0 +1,27 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+# Copyright (C) 2022  Ammar Faizi <ammarfaizi2@gnuweeb.org>
+#
+
+
+from mysql.connector import errors
+from datetime import datetime
+
+
+class InsertEmail:
+
+	def save_email(self, email_msg_id):
+		try:
+			return self.__insert_email(email_msg_id)
+		except errors.IntegrityError:
+			#
+			# Duplicate data, skip!
+			#
+			return None
+
+
+	def __insert_email(self, email_msg_id):
+		q = "INSERT INTO tg_emails (message_id, created_at) VALUES (%s, %s)"
+		self.cur.execute(q, (email_msg_id, datetime.utcnow()))
+		return self.cur.lastrowid
diff --git a/daemon/telegram/database/methods/insertion/insert_telegram.py b/daemon/telegram/database/methods/insertion/insert_telegram.py
new file mode 100644
index 0000000..8e0615c
--- /dev/null
+++ b/daemon/telegram/database/methods/insertion/insert_telegram.py
@@ -0,0 +1,21 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# Copyright (C) 2022  Muhammad Rizki <kiizuha@gnuweeb.org>
+# Copyright (C) 2022  Ammar Faizi <ammarfaizi2@gnuweeb.org>
+#
+
+
+from datetime import datetime
+
+
+class InsertTelegram:
+	
+	def save_telegram_mail(self, email_id, tg_chat_id, tg_msg_id):
+		q = """
+			INSERT INTO tg_mail_msg
+			(email_id, chat_id, tg_msg_id, created_at)
+			VALUES (%s, %s, %s, %s);
+		"""
+		self.cur.execute(q, (email_id, tg_chat_id, tg_msg_id,
+				datetime.utcnow()))
+		return self.cur.lastrowid
diff --git a/daemon/telegram/db.sql b/daemon/telegram/db.sql
index ac6ed0d..c55c464 100644
--- a/daemon/telegram/db.sql
+++ b/daemon/telegram/db.sql
@@ -7,8 +7,8 @@ SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
 
 SET NAMES utf8mb4;
 
-DROP TABLE IF EXISTS `emails`;
-CREATE TABLE `emails` (
+DROP TABLE IF EXISTS `tg_emails`;
+CREATE TABLE `tg_emails` (
   `id` bigint unsigned NOT NULL AUTO_INCREMENT,
   `message_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
   `created_at` datetime NOT NULL,
@@ -18,8 +18,8 @@ CREATE TABLE `emails` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 
 
-DROP TABLE IF EXISTS `tg_emails`;
-CREATE TABLE `tg_emails` (
+DROP TABLE IF EXISTS `tg_mail_msg`;
+CREATE TABLE `tg_mail_msg` (
   `id` bigint unsigned NOT NULL AUTO_INCREMENT,
   `email_id` bigint unsigned NOT NULL,
   `chat_id` bigint NOT NULL,
@@ -30,12 +30,12 @@ CREATE TABLE `tg_emails` (
   KEY `chat_id` (`chat_id`),
   KEY `tg_msg_id` (`tg_msg_id`),
   KEY `created_at` (`created_at`),
-  CONSTRAINT `tg_emails_ibfk_2` FOREIGN KEY (`email_id`) REFERENCES `emails` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+  CONSTRAINT `tg_mail_msg_ibfk_2` FOREIGN KEY (`email_id`) REFERENCES `tg_emails` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 
 
-DROP TABLE IF EXISTS `atom_urls`;
-CREATE TABLE `atom_urls` (
+DROP TABLE IF EXISTS `tg_atoms`;
+CREATE TABLE `tg_atoms` (
   `id` bigint unsigned NOT NULL AUTO_INCREMENT,
   `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
   `created_at` datetime NOT NULL,
@@ -45,8 +45,8 @@ CREATE TABLE `atom_urls` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 
 
-DROP TABLE IF EXISTS `broadcast_chats`;
-CREATE TABLE `broadcast_chats` (
+DROP TABLE IF EXISTS `tg_broadcasts`;
+CREATE TABLE `tg_broadcasts` (
   `id` bigint unsigned NOT NULL AUTO_INCREMENT,
   `chat_id` bigint NOT NULL,
   `username` varchar(32),
diff --git a/daemon/telegram/packages/client.py b/daemon/telegram/packages/client.py
index 282daf6..820c3e2 100644
--- a/daemon/telegram/packages/client.py
+++ b/daemon/telegram/packages/client.py
@@ -9,7 +9,7 @@ from pyrogram.types import Message, InlineKeyboardMarkup, InlineKeyboardButton
 from typing import Union
 from email.message import Message
 from scraper import utils
-from scraper.db import Db
+from database import DB
 from .decorator import handle_flood
 
 
@@ -18,7 +18,7 @@ class DaemonClient(Client):
 		api_hash: str, conn, **kwargs):
 		super().__init__(name, api_id,
 				api_hash, **kwargs)
-		self.db = Db(conn)
+		self.db = DB(conn)
 
 
 	@handle_flood
diff --git a/daemon/telegram/packages/plugins/commands/manage_atom.py b/daemon/telegram/packages/plugins/commands/manage_atom.py
index bcb2f35..4ba422a 100644
--- a/daemon/telegram/packages/plugins/commands/manage_atom.py
+++ b/daemon/telegram/packages/plugins/commands/manage_atom.py
@@ -25,7 +25,7 @@ async def add_atom_url(c: DaemonClient, m: Message):
 	if not is_atom:
 		return await m.reply("Invalid Atom URL")
 
-	inserted = c.db.insert_atom(text)
+	inserted = c.db.save_atom(text)
 	if inserted is None:
 		return await m.reply(f"This URL already listened for new email.")
 
diff --git a/daemon/telegram/packages/plugins/commands/manage_broadcast.py b/daemon/telegram/packages/plugins/commands/manage_broadcast.py
index ffb5a6b..6d75c36 100644
--- a/daemon/telegram/packages/plugins/commands/manage_broadcast.py
+++ b/daemon/telegram/packages/plugins/commands/manage_broadcast.py
@@ -20,7 +20,7 @@ async def add_broadcast(c: DaemonClient, m: Message):
 	else:
 		chat_name = m.chat.title
 
-	inserted = c.db.insert_broadcast(
+	inserted = c.db.save_broadcast(
 		chat_id=m.chat.id,
 		name=chat_name,
 		type=str(m.chat.type),
diff --git a/daemon/telegram/scraper/bot.py b/daemon/telegram/scraper/bot.py
index 7adfb12..9ef5508 100644
--- a/daemon/telegram/scraper/bot.py
+++ b/daemon/telegram/scraper/bot.py
@@ -112,7 +112,7 @@ class Bot():
 				tg_chat_id, text,reply_to, url
 			)
 
-		self.db.insert_telegram(email_id, m.chat.id, m.id)
+		self.db.save_telegram_mail(email_id, m.chat.id, m.id)
 		for d, f in files:
 			await m.reply_document(f"{d}/{f}", file_name=f)
 			await asyncio.sleep(1)
@@ -124,11 +124,11 @@ class Bot():
 
 
 	def __need_to_send_to_telegram(self, email_msg_id, tg_chat_id):
-		email_id = self.db.save_email_msg_id(email_msg_id)
+		email_id = self.db.save_email(email_msg_id)
 		if email_id:
 			return email_id
 
-		email_id = self.db.need_to_send_to_tg(email_msg_id, tg_chat_id)
+		email_id = self.db.get_email_id(email_msg_id, tg_chat_id)
 		return email_id
 
 
@@ -141,4 +141,4 @@ class Bot():
 		if not reply_to:
 			return None
 
-		return self.db.get_tg_reply_to(reply_to, tg_chat_id)
+		return self.db.get_reply_id(reply_to, tg_chat_id)
diff --git a/daemon/telegram/scraper/db.py b/daemon/telegram/scraper/db.py
deleted file mode 100644
index 58601d1..0000000
--- a/daemon/telegram/scraper/db.py
+++ /dev/null
@@ -1,217 +0,0 @@
-# SPDX-License-Identifier: GPL-2.0-only
-#
-# Copyright (C) 2022  Muhammad Rizki <riskimuhammmad1@gmail.com>
-# Copyright (C) 2022  Ammar Faizi <ammarfaizi2@gnuweeb.org>
-#
-
-from datetime import datetime
-import mysql
-
-
-class Db():
-	def __init__(self, conn):
-		self.conn = conn
-		self.conn.autocommit = True
-		self.cur = self.conn.cursor(buffered=True)
-
-
-	def __del__(self):
-		self.cur.close()
-		self.conn.close()
-
-
-	def save_email_msg_id(self, email_msg_id):
-		try:
-			return self.__save_email_msg_id(email_msg_id)
-		except mysql.connector.errors.IntegrityError:
-			#
-			# Duplicate data, skip!
-			#
-			return None
-
-
-	def __save_email_msg_id(self, email_msg_id):
-		q = "INSERT INTO emails (message_id, created_at) VALUES (%s, %s)"
-		self.cur.execute(q, (email_msg_id, datetime.utcnow()))
-		return self.cur.lastrowid
-
-
-	def insert_telegram(self, email_id, tg_chat_id, tg_msg_id):
-		q = """
-			INSERT INTO tg_emails
-			(email_id, chat_id, tg_msg_id, created_at)
-			VALUES (%s, %s, %s, %s);
-		"""
-		self.cur.execute(q, (email_id, tg_chat_id, tg_msg_id,
-				 datetime.utcnow()))
-		return self.cur.lastrowid
-
-
-	#
-	# Determine whether the email needs to be sent to @tg_chat_id.
-	#
-	# - Return an email id (PK) if it needs to be sent.
-	# - Return None if it doesn't need to be sent.
-	#
-	def need_to_send_to_tg(self, email_msg_id, tg_chat_id):
-		q = """
-			SELECT emails.id, tg_emails.id FROM emails LEFT JOIN tg_emails
-			ON emails.id = tg_emails.email_id
-			WHERE emails.message_id = %(email_msg_id)s
-			AND tg_emails.chat_id = %(tg_chat_id)s
-			LIMIT 1
-		"""
-
-		self.cur.execute(
-			q,
-			{
-				"email_msg_id": email_msg_id,
-				"tg_chat_id": tg_chat_id
-			}
-		)
-		res = self.cur.fetchone()
-		if bool(res):
-			#
-			# This email has already been sent to
-			# @tg_chat_id.
-			#
-			return None
-
-		q = """
-			SELECT id FROM emails WHERE message_id = %(email_msg_id)s
-		"""
-		self.cur.execute(q, {"email_msg_id": email_msg_id})
-		res = self.cur.fetchone()
-		if not bool(res):
-			#
-			# Something goes wrong, skip!
-			#
-			return None
-
-		return int(res[0])
-
-
-	def get_tg_reply_to(self, email_msg_id, tg_chat_id):
-		q = """
-			SELECT tg_emails.tg_msg_id
-			FROM emails INNER JOIN tg_emails
-			ON emails.id = tg_emails.email_id
-			WHERE emails.message_id = %(email_msg_id)s
-			AND tg_emails.chat_id = %(chat_id)s
-		"""
-
-		self.cur.execute(
-			q,
-			{
-				"email_msg_id": email_msg_id,
-				"chat_id": tg_chat_id
-			}
-		)
-		res = self.cur.fetchone()
-		if not bool(res):
-			return None
-
-		return res[0]
-
-
-	def insert_atom(self, atom: str):
-		try:
-			return self.__save_atom(atom)
-		except mysql.connector.errors.IntegrityError:
-			#
-			# Duplicate data, skip!
-			#
-			return None
-
-
-	def __save_atom(self, atom: str):
-		q = "INSERT INTO atom_urls (url, created_at) VALUES (%s, %s)"
-		self.cur.execute(q, (atom, datetime.utcnow()))
-		return self.cur.lastrowid
-
-
-	def delete_atom(self, atom: str):
-		q = """
-			DELETE FROM atom_urls
-			WHERE url = %(atom)s
-		"""
-		try:
-			self.cur.execute(q, {"atom": atom})
-			return True
-		except:
-			return False
-
-
-	def get_atom_urls(self):
-		q = """
-			SELECT atom_urls.url
-			FROM atom_urls
-		"""
-		self.cur.execute(q)
-		urls = self.cur.fetchall()
-
-		return [u[0] for u in urls]
-
-
-	def insert_broadcast(
-		self,
-		chat_id: int,
-		name: str,
-		type: str,
-		created_at: "datetime",
-		username: str = None,
-		link: str = None,
-	):
-		try:
-			return self.__save_broadcast(
-				chat_id=chat_id,
-				name=name,
-				type=type,
-				created_at=created_at,
-				username=username,
-				link=link
-			)
-		except mysql.connector.errors.IntegrityError:
-			#
-			# Duplicate data, skip!
-			#
-			return None
-
-
-	def __save_broadcast(
-		self,
-		chat_id: int,
-		name: str,
-		type: str,
-		created_at: "datetime",
-		username: str = None,
-		link: str = None,
-	):
-		q = """
-			INSERT INTO broadcast_chats
-			(chat_id, username, name, type, link, created_at)
-			VALUES
-			(%s, %s, %s, %s, %s, %s)
-		"""
-		values = (chat_id, username, name, type, link, created_at)
-		self.cur.execute(q, values)
-		return self.cur.lastrowid
-
-
-	def delete_broadcast(self, chat_id: int):
-		q = """
-			DELETE FROM broadcast_chats
-			WHERE chat_id = %(chat_id)s
-		"""
-		self.cur.execute(q, {"chat_id": chat_id})
-		return self.cur.rowcount > 0
-
-
-	def get_broadcast_chats(self):
-		q = """
-			SELECT *
-			FROM broadcast_chats
-		"""
-		self.cur.execute(q)
-
-		return self.cur.fetchall()
-- 
Muhammad Rizki