192 lines
6.4 KiB
Python
192 lines
6.4 KiB
Python
import sqlite3
|
||
import os
|
||
import time
|
||
|
||
DB_FILE = "/var/lib/bot/messages.db"
|
||
|
||
def get_db_connection():
|
||
"""Obtiene una conexión a la base de datos SQLite con soporte de diccionario."""
|
||
conn = sqlite3.connect(DB_FILE)
|
||
conn.row_factory = sqlite3.Row # Permite acceder a columnas por nombre
|
||
return conn
|
||
|
||
def init_db():
|
||
"""Inicializa la base de datos y crea las tablas necesarias."""
|
||
os.makedirs(os.path.dirname(DB_FILE), exist_ok=True)
|
||
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
|
||
# Tabla para almacenar mensajes generales
|
||
cursor.execute("""
|
||
CREATE TABLE IF NOT EXISTS messages (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
nick TEXT NOT NULL,
|
||
target TEXT NOT NULL,
|
||
message TEXT NOT NULL,
|
||
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
)
|
||
""")
|
||
|
||
# Tabla para almacenar grabs (frases guardadas)
|
||
cursor.execute("""
|
||
CREATE TABLE IF NOT EXISTS grabs (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
nick TEXT NOT NULL,
|
||
message TEXT NOT NULL,
|
||
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
)
|
||
""")
|
||
|
||
# Tabla para usuarios autenticados (ejecución de plugins)
|
||
cursor.execute("""
|
||
CREATE TABLE IF NOT EXISTS authorized_users (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
nick TEXT UNIQUE NOT NULL,
|
||
auth_time INTEGER NOT NULL
|
||
)
|
||
""")
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
# ====================== GESTIÓN DE MENSAJES ======================
|
||
|
||
def store_message(nick, target, message, max_messages=10000):
|
||
"""Guarda un mensaje en la base de datos SQLite y elimina los más antiguos si es necesario."""
|
||
if message.startswith("."): # Ignorar comandos
|
||
return
|
||
|
||
try:
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute("INSERT INTO messages (nick, target, message) VALUES (?, ?, ?)", (nick, target, message))
|
||
|
||
cursor.execute("SELECT COUNT(*) FROM messages")
|
||
total_messages = cursor.fetchone()[0]
|
||
|
||
if total_messages > max_messages:
|
||
cursor.execute("""
|
||
DELETE FROM messages WHERE id IN (
|
||
SELECT id FROM messages ORDER BY id ASC LIMIT ?
|
||
)
|
||
""", (total_messages - max_messages,))
|
||
|
||
conn.commit()
|
||
except Exception as e:
|
||
print(f" Error al almacenar mensaje en la base de datos: {e}")
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def get_last_message(nick):
|
||
"""Obtiene el último mensaje enviado por un usuario."""
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
cursor.execute("SELECT message FROM messages WHERE nick = ? ORDER BY id DESC LIMIT 1", (nick,))
|
||
row = cursor.fetchone()
|
||
conn.close()
|
||
return row["message"] if row else None
|
||
|
||
def get_messages_by_nick(nick):
|
||
"""Obtiene todos los mensajes enviados por un usuario."""
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
cursor.execute("SELECT message FROM messages WHERE nick = ? ORDER BY id", (nick,))
|
||
rows = cursor.fetchall()
|
||
conn.close()
|
||
return [row["message"] for row in rows]
|
||
|
||
def get_message_by_index(nick, index):
|
||
"""Obtiene un mensaje específico de un usuario por índice."""
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
cursor.execute("SELECT message FROM messages WHERE nick = ? ORDER BY id LIMIT 1 OFFSET ?",
|
||
(nick, index - 1))
|
||
row = cursor.fetchone()
|
||
conn.close()
|
||
return row["message"] if row else None
|
||
|
||
# ====================== GESTIÓN DE GRABS ======================
|
||
|
||
def store_grab(nick, message):
|
||
"""Guarda un grab (frase destacada) en la base de datos."""
|
||
try:
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
cursor.execute("INSERT INTO grabs (nick, message) VALUES (?, ?)", (nick, message))
|
||
conn.commit()
|
||
return f" Grab guardado para {nick}."
|
||
except Exception as e:
|
||
return f"️ Error al guardar grab: {e}"
|
||
finally:
|
||
conn.close()
|
||
|
||
def get_grabs_by_nick(nick):
|
||
"""Obtiene todos los grabs guardados de un usuario."""
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
cursor.execute("SELECT id, message FROM grabs WHERE nick = ? ORDER BY id", (nick,))
|
||
rows = cursor.fetchall()
|
||
conn.close()
|
||
return {row["id"]: row["message"] for row in rows}
|
||
|
||
def get_grab_by_index(nick, index):
|
||
"""Obtiene un grab específico de un usuario según el índice."""
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
cursor.execute("SELECT message FROM grabs WHERE nick = ? ORDER BY id LIMIT 1 OFFSET ?",
|
||
(nick, index - 1))
|
||
row = cursor.fetchone()
|
||
conn.close()
|
||
return row["message"] if row else None
|
||
|
||
def get_random_grab(nick):
|
||
"""Obtiene un grab aleatorio de un usuario."""
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
cursor.execute("SELECT message FROM grabs WHERE nick = ? ORDER BY RANDOM() LIMIT 1", (nick,))
|
||
row = cursor.fetchone()
|
||
conn.close()
|
||
return row["message"] if row else None
|
||
|
||
# ====================== AUTENTICACIÓN DE USUARIOS ======================
|
||
|
||
def authenticate_user(nick):
|
||
"""Guarda al usuario como autenticado con timestamp."""
|
||
try:
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
cursor.execute("INSERT OR REPLACE INTO authorized_users (nick, auth_time) VALUES (?, ?)",
|
||
(nick, int(time.time())))
|
||
conn.commit()
|
||
return f" {nick}, ahora puedes ejecutar comandos de plugins restringidos."
|
||
except Exception as e:
|
||
return f"️ Error al autenticar usuario: {e}"
|
||
finally:
|
||
conn.close()
|
||
|
||
def is_user_authenticated(nick):
|
||
"""Verifica si un usuario está autenticado."""
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
cursor.execute("SELECT auth_time FROM authorized_users WHERE nick = ?", (nick,))
|
||
row = cursor.fetchone()
|
||
conn.close()
|
||
return row is not None # Retorna True si está autenticado
|
||
|
||
def remove_authentication(nick):
|
||
"""Elimina la autenticación del usuario."""
|
||
try:
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
cursor.execute("DELETE FROM authorized_users WHERE nick = ?", (nick,))
|
||
conn.commit()
|
||
return f" {nick}, has cerrado sesión de los comandos restringidos."
|
||
except Exception as e:
|
||
return f"️ Error al cerrar sesión: {e}"
|
||
finally:
|
||
conn.close()
|
||
|