database.py

import sqlite3
import os

DATABASE_NAME = "teams_bot_settings.db"
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
DATABASE_PATH = os.path.join(SCRIPT_DIR, DATABASE_NAME)

def create_connection():
    conn = None
    try:
        conn = sqlite3.connect(DATABASE_PATH)
    except sqlite3.Error as e:
        print(f"Error al conectar con la base de datos: {e}")
    return conn

def create_tables():
    conn = create_connection()
    if conn is not None:
        try:
            cursor = conn.cursor()
            
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS configuration (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    analysis_interval_seconds INTEGER NOT NULL DEFAULT 10,
                    auto_analysis_enabled BOOLEAN NOT NULL DEFAULT 0
                );
            """)
            
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS whitelist (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_teams_id TEXT NOT NULL UNIQUE,
                    user_name TEXT,
                    start_date TEXT,
                    end_date TEXT,
                    reason TEXT
                );
            """)
            
            cursor.execute("SELECT COUNT(*) FROM configuration")
            if cursor.fetchone()[0] == 0:
                cursor.execute("INSERT INTO configuration (analysis_interval_seconds, auto_analysis_enabled) VALUES (?, ?)", (10, 0))
            
            conn.commit()
            print("Tablas creadas/verificadas exitosamente.")
        except sqlite3.Error as e:
            print(f"Error al crear tablas: {e}")
        finally:
            conn.close()
    else:
        print("Error: No se pudo crear la conexión a la base de datos.")

def get_configuration():
    conn = create_connection()
    config = {"analysis_interval_seconds": 10, "auto_analysis_enabled": False}
    if conn is not None:
        try:
            cursor = conn.cursor()
            cursor.execute("SELECT analysis_interval_seconds, auto_analysis_enabled FROM configuration WHERE id = 1")
            row = cursor.fetchone()
            if row:
                config["analysis_interval_seconds"] = row[0]
                config["auto_analysis_enabled"] = bool(row[1])
        except sqlite3.Error as e:
            print(f"Error al obtener configuración: {e}")
        finally:
            conn.close()
    return config

def save_configuration(interval_seconds, auto_enabled):
    conn = create_connection()
    if conn is not None:
        try:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE configuration 
                SET analysis_interval_seconds = ?, auto_analysis_enabled = ? 
                WHERE id = 1
            """, (interval_seconds, 1 if auto_enabled else 0))
            conn.commit()
            print(f"Configuración guardada: Intervalo {interval_seconds}s, Auto-Análisis {'Activado' if auto_enabled else 'Desactivado'}.")
        except sqlite3.Error as e:
            print(f"Error al guardar configuración: {e}")
        finally:
            conn.close()

def add_to_whitelist(user_teams_id, user_name, start_date_str, end_date_str, reason=""):
    conn = create_connection()
    if conn is not None:
        try:
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO whitelist (user_teams_id, user_name, start_date, end_date, reason)
                VALUES (?, ?, ?, ?, ?)
            """, (user_teams_id, user_name, start_date_str, end_date_str, reason))
            conn.commit()
            print(f"Usuario {user_name} ({user_teams_id}) añadido a la lista blanca.")
            return cursor.lastrowid
        except sqlite3.Error as e:
            print(f"Error al añadir a la lista blanca: {e}")
            return None
        finally:
            conn.close()

def get_whitelist():
    conn = create_connection()
    whitelist_entries = []
    if conn is not None:
        try:
            cursor = conn.cursor()
            cursor.execute("SELECT id, user_teams_id, user_name, start_date, end_date, reason FROM whitelist ORDER BY user_name")
            rows = cursor.fetchall()
            for row in rows:
                whitelist_entries.append({
                    "id": row[0], "user_teams_id": row[1], "user_name": row[2],
                    "start_date": row[3], "end_date": row[4], "reason": row[5]
                })
        except sqlite3.Error as e:
            print(f"Error al obtener lista blanca: {e}")
        finally:
            conn.close()
    return whitelist_entries

def remove_from_whitelist(entry_id):
    conn = create_connection()
    if conn is not None:
        try:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM whitelist WHERE id = ?", (entry_id,))
            conn.commit()
            print(f"Entrada con ID {entry_id} eliminada de la lista blanca.")
            return True
        except sqlite3.Error as e:
            print(f"Error al eliminar de la lista blanca: {e}")
            return False
        finally:
            conn.close()

def is_user_name_whitelisted_now(user_name_to_check):
    conn = create_connection()
    if conn is not None:
        try:
            from datetime import datetime
            cursor = conn.cursor()
            current_date_str = datetime.now().strftime("%Y-%m-%d")
            
            cursor.execute("""
                SELECT COUNT(*) FROM whitelist 
                WHERE user_name = ? 
                AND (start_date IS NULL OR start_date <= ?)
                AND (end_date IS NULL OR end_date >= ?)
            """, (user_name_to_check, current_date_str, current_date_str))
            count = cursor.fetchone()[0]
            return count > 0
        except sqlite3.Error as e:
            print(f"Error al verificar lista blanca por nombre para {user_name_to_check}: {e}")
            return False 
        finally:
            conn.close()
    return False

def is_user_whitelisted_now(user_teams_id):
    conn = create_connection()
    if conn is not None:
        try:
            from datetime import datetime
            cursor = conn.cursor()
            # Obtener la fecha actual en formato YYYY-MM-DD
            current_date_str = datetime.now().strftime("%Y-%m-%d")
            
            cursor.execute("""
                SELECT COUNT(*) FROM whitelist 
                WHERE user_teams_id = ? 
                AND (start_date IS NULL OR start_date <= ?)
                AND (end_date IS NULL OR end_date >= ?)
            """, (user_teams_id, current_date_str, current_date_str))
            count = cursor.fetchone()[0]
            return count > 0
        except sqlite3.Error as e:
            print(f"Error al verificar lista blanca para {user_teams_id}: {e}")
            return False 
        finally:
            conn.close()
    return False

if __name__ == '__main__':
    create_tables()