vpd-discord-bot/VPD_BOT/cogs/inaktive_remider_dm.py

import os
from dotenv import load_dotenv
import discord
from discord.ext import commands, tasks
from discord.commands import Option
from discord.commands import slash_command
import configparser
import time
import mysql.connector


## Note: this script will be pretty hardcoded and specific for our use case. It is deactivatable in the config.cfg file.


class remiderinactive(commands.Cog):
    def __init__(self, bot: discord.Bot):
        self.bot = bot
    
    def _load_config(self):
        config = configparser.ConfigParser()
        configFilePath = r'config.cfg'
        config.read(configFilePath)
        return config

        
    @commands.Cog.listener()
    async def on_ready(self):
        self.check_inactive_members.start()
    
    @tasks.loop(hours=48)
    async def check_inactive_members(self):
        config = self._load_config()
        enable_inavtive_reminder_dm = config.getboolean("Welcome","enable_inavtive_reminder_dm")
        if not enable_inavtive_reminder_dm:
            return # Exit the function if the feature is disabled in the config

        log_channel_id = config.getint("Welcome","log_channel_id")
        log_channel = self.bot.get_channel(log_channel_id)
    
    #Load .env file for the gameserver database
        dbhost = os.getenv("HOST2")
        if dbhost is None:
            raise ValueError("HOST2 not found in .env file")
        dbname = os.getenv("NAME2")
        if dbname is None:
            raise ValueError("NAME2 not found in .env file")
        dbpsswd = os.getenv("PASSWORD2")
        if dbpsswd is None:
            raise ValueError("PASSWORD2 not found in .env file")
        dbdb = os.getenv("DATABASE2")
        if dbdb is None:
            raise ValueError("DATABASE2 not found in .env file")
        

        #Database initialization
        conn = mysql.connector.connect(
            host=dbhost,
            user=dbname,
            password=dbpsswd,
            charset='utf8mb4',
            collation='utf8mb4_unicode_ci'
        )
        cursor = conn.cursor()
        conn.database = dbdb

        inaktive_players = []
        
        cursor.execute("""
        SELECT license, discord FROM users WHERE license in (SELECT license FROM players WHERE last_logged_out < CURDATE() - INTERVAL 14 DAY)
        """)
        for license, discord in cursor.fetchall():
            inaktive_players.append((discord))


        for discord in inaktive_players:
            discord_raw = discord.split(":")
            for i in range(len(discord_raw)):
                if discord_raw[i].isdigit():
                    user_id = int(discord_raw[i])
                    user = self.bot.get_user(user_id)
                    if user is not None:
                        try:
                            await user.send("Hey! We noticed that you haven't been active in the server for a while. If you want to stay in the server, please log in to the game at least once every 14 days. If you have any questions, feel free to reach out to the moderators.")
                            await log_channel.send(f"Sent inactivity reminder DM to {user.name} ({user.id})")
                        except Exception as e:
                            print(f"Could not send DM to {user.name}: {e}")
                            await log_channel.send(f"Could not send DM to {user.name} ({user.id}): {e}")
                    else:
                        print(f"Could not find user with ID {user_id}")
                        await log_channel.send(f"Could not find user with ID {user_id}")
        
        cursor.close()
        conn.close()



def setup(bot: discord.Bot):
    bot.add_cog(remiderinactive(bot))