""" Module de gestion de la base de données SQLite Stocke l'historique des sauvegardes pour le dashboard web """ import sqlite3 import logging from datetime import datetime, timedelta, timezone import json from config import DATABASE_CONFIG, MONITORING_CONFIG logger = logging.getLogger(__name__) class BackupDatabase: """Gestionnaire de base de données pour l'historique des sauvegardes""" def __init__(self): """Initialise la connexion à la base de données""" self.db_file = DATABASE_CONFIG['db_file'] self.table_name = DATABASE_CONFIG['table_name'] self.init_database() def get_connection(self): """Crée une nouvelle connexion à la base de données""" return sqlite3.connect(self.db_file) def init_database(self): """Initialise la base de données et crée les tables si nécessaire""" try: with self.get_connection() as conn: cursor = conn.cursor() # Créer la table principale cursor.execute(f''' CREATE TABLE IF NOT EXISTS {self.table_name} ( id INTEGER PRIMARY KEY AUTOINCREMENT, email_id TEXT UNIQUE, backup_name TEXT NOT NULL, backup_type TEXT NOT NULL, status TEXT NOT NULL, received_date TEXT NOT NULL, subject TEXT, size TEXT, duration TEXT, start_time TEXT, end_time TEXT, details TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') # Créer les index pour optimiser les requêtes cursor.execute(f''' CREATE INDEX IF NOT EXISTS idx_backup_name ON {self.table_name}(backup_name) ''') cursor.execute(f''' CREATE INDEX IF NOT EXISTS idx_received_date ON {self.table_name}(received_date) ''') cursor.execute(f''' CREATE INDEX IF NOT EXISTS idx_status ON {self.table_name}(status) ''') cursor.execute(f''' CREATE INDEX IF NOT EXISTS idx_backup_type ON {self.table_name}(backup_type) ''') # Table pour les alertes envoyées cursor.execute(''' CREATE TABLE IF NOT EXISTS alerts_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, alert_type TEXT NOT NULL, backup_name TEXT, sent_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, details TEXT ) ''') conn.commit() logger.info("Base de données initialisée avec succès") except Exception as e: logger.error(f"Erreur lors de l'initialisation de la base de données: {str(e)}") def insert_backup_record(self, backup_info): """ Insère ou met à jour un enregistrement de sauvegarde Args: backup_info: Dict avec les informations de la sauvegarde Returns: bool: True si succès """ try: with self.get_connection() as conn: cursor = conn.cursor() # Convertir les détails en JSON details_json = json.dumps(backup_info.get('details', {})) # Insérer ou mettre à jour cursor.execute(f''' INSERT OR REPLACE INTO {self.table_name} (email_id, backup_name, backup_type, status, received_date, subject, size, duration, start_time, end_time, details, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP) ''', ( backup_info.get('id'), backup_info.get('backup_name'), backup_info.get('backup_type'), backup_info.get('status'), backup_info.get('received_date'), backup_info.get('subject'), backup_info.get('size'), backup_info.get('duration'), backup_info.get('start_time'), backup_info.get('end_time'), details_json )) conn.commit() return True except Exception as e: logger.error(f"Erreur lors de l'insertion: {str(e)}") return False def bulk_insert_records(self, backup_records): """ Insère plusieurs enregistrements en une fois Args: backup_records: Liste de dicts avec les informations Returns: int: Nombre d'enregistrements insérés """ inserted = 0 for record in backup_records: if self.insert_backup_record(record): inserted += 1 logger.info(f"Inséré {inserted}/{len(backup_records)} enregistrements") return inserted def get_latest_backups(self): """ Récupère la dernière sauvegarde de chaque type Returns: list: Liste des dernières sauvegardes """ try: with self.get_connection() as conn: cursor = conn.cursor() # Requête pour obtenir la dernière sauvegarde de chaque nom query = f''' SELECT * FROM {self.table_name} WHERE id IN ( SELECT MAX(id) FROM {self.table_name} GROUP BY backup_name ) ORDER BY received_date DESC ''' cursor.execute(query) columns = [col[0] for col in cursor.description] results = [] for row in cursor.fetchall(): record = dict(zip(columns, row)) # Parser les détails JSON if record.get('details'): try: record['details'] = json.loads(record['details']) except: record['details'] = {} results.append(record) return results except Exception as e: logger.error(f"Erreur lors de la récupération des dernières sauvegardes: {str(e)}") return [] def get_all_recent_backups(self, days=3): """ Récupère TOUTES les sauvegardes des X derniers jours (pas seulement la dernière par nom) Args: days: Nombre de jours à récupérer Returns: list: Liste de toutes les sauvegardes de la période """ try: with self.get_connection() as conn: cursor = conn.cursor() # Date limite date_limit = (datetime.now(timezone.utc) - timedelta(days=days)).isoformat() # Requête pour TOUTES les sauvegardes de la période query = f''' SELECT * FROM {self.table_name} WHERE received_date >= ? ORDER BY received_date DESC ''' cursor.execute(query, (date_limit,)) columns = [col[0] for col in cursor.description] results = [] for row in cursor.fetchall(): record = dict(zip(columns, row)) # Parser les détails JSON if record.get('details'): try: record['details'] = json.loads(record['details']) except: record['details'] = {} results.append(record) return results except Exception as e: logger.error(f"Erreur lors de la récupération de toutes les sauvegardes récentes: {str(e)}") return [] def get_backup_history(self, backup_name, days=60): """ Récupère l'historique d'une sauvegarde spécifique Args: backup_name: Nom de la sauvegarde days: Nombre de jours d'historique Returns: list: Historique de la sauvegarde """ try: with self.get_connection() as conn: cursor = conn.cursor() # Date limite date_limit = (datetime.now(timezone.utc) - timedelta(days=days)).isoformat() query = f''' SELECT * FROM {self.table_name} WHERE backup_name = ? AND received_date >= ? ORDER BY received_date DESC ''' cursor.execute(query, (backup_name, date_limit)) columns = [col[0] for col in cursor.description] results = [] for row in cursor.fetchall(): record = dict(zip(columns, row)) # Parser les détails JSON if record.get('details'): try: record['details'] = json.loads(record['details']) except: record['details'] = {} results.append(record) return results except Exception as e: logger.error(f"Erreur lors de la récupération de l'historique: {str(e)}") return [] def get_statistics(self, days=30): """ Calcule les statistiques sur une période Args: days: Nombre de jours pour les statistiques Returns: dict: Statistiques calculées """ try: with self.get_connection() as conn: cursor = conn.cursor() # Date limite date_limit = (datetime.now(timezone.utc) - timedelta(days=days)).isoformat() stats = {} # Total des sauvegardes cursor.execute(f''' SELECT COUNT(*) FROM {self.table_name} WHERE received_date >= ? ''', (date_limit,)) stats['total'] = cursor.fetchone()[0] # Par statut cursor.execute(f''' SELECT status, COUNT(*) FROM {self.table_name} WHERE received_date >= ? GROUP BY status ''', (date_limit,)) stats['by_status'] = dict(cursor.fetchall()) # Par type cursor.execute(f''' SELECT backup_type, COUNT(*) FROM {self.table_name} WHERE received_date >= ? GROUP BY backup_type ''', (date_limit,)) stats['by_type'] = dict(cursor.fetchall()) # Taux de succès success = stats['by_status'].get('success', 0) stats['success_rate'] = (success / stats['total'] * 100) if stats['total'] > 0 else 0 # Sauvegardes uniques cursor.execute(f''' SELECT COUNT(DISTINCT backup_name) FROM {self.table_name} WHERE received_date >= ? ''', (date_limit,)) stats['unique_backups'] = cursor.fetchone()[0] return stats except Exception as e: logger.error(f"Erreur lors du calcul des statistiques: {str(e)}") return {} def get_failed_backups(self, hours=24): """ Récupère les sauvegardes en échec récentes Args: hours: Nombre d'heures à regarder Returns: list: Liste des échecs """ try: with self.get_connection() as conn: cursor = conn.cursor() # Date limite date_limit = (datetime.now(timezone.utc) - timedelta(hours=hours)).isoformat() query = f''' SELECT * FROM {self.table_name} WHERE status = 'failure' AND received_date >= ? ORDER BY received_date DESC ''' cursor.execute(query, (date_limit,)) columns = [col[0] for col in cursor.description] results = [] for row in cursor.fetchall(): record = dict(zip(columns, row)) if record.get('details'): try: record['details'] = json.loads(record['details']) except: record['details'] = {} results.append(record) return results except Exception as e: logger.error(f"Erreur lors de la récupération des échecs: {str(e)}") return [] def check_missing_backups(self): """ Vérifie les sauvegardes manquantes (pas reçues depuis >48h) Returns: dict: Sauvegardes manquantes """ try: with self.get_connection() as conn: cursor = conn.cursor() # Obtenir la dernière sauvegarde de chaque nom query = f''' SELECT backup_name, backup_type, MAX(received_date) as last_seen, status FROM {self.table_name} GROUP BY backup_name ''' cursor.execute(query) missing = {} threshold = timedelta(hours=MONITORING_CONFIG['alert_threshold_hours']) current_time = datetime.now(timezone.utc) for row in cursor.fetchall(): backup_name, backup_type, last_seen, status = row try: # Parser la date - elle vient de Graph API donc avec timezone last_date = datetime.fromisoformat(last_seen.replace('Z', '+00:00')) # Vérifier si trop ancien if current_time - last_date > threshold: hours_ago = int((current_time - last_date).total_seconds() / 3600) missing[backup_name] = { 'type': backup_type, 'last_seen': last_seen, 'hours_ago': hours_ago, 'last_status': status } except Exception as e: logger.error(f"Erreur parsing date pour {backup_name}: {str(e)}") return missing except Exception as e: logger.error(f"Erreur lors de la vérification des manquants: {str(e)}") return {} def cleanup_old_records(self, days=90): """ Supprime les enregistrements plus vieux que X jours Args: days: Nombre de jours à conserver Returns: int: Nombre d'enregistrements supprimés """ try: with self.get_connection() as conn: cursor = conn.cursor() # Date limite date_limit = (datetime.now(timezone.utc) - timedelta(days=days)).isoformat() cursor.execute(f''' DELETE FROM {self.table_name} WHERE received_date < ? ''', (date_limit,)) deleted = cursor.rowcount conn.commit() logger.info(f"Supprimé {deleted} enregistrements de plus de {days} jours") return deleted except Exception as e: logger.error(f"Erreur lors du nettoyage: {str(e)}") return 0 def log_alert(self, alert_type, backup_name=None, details=None): """ Enregistre une alerte envoyée Args: alert_type: Type d'alerte (failure, missing, summary) backup_name: Nom de la sauvegarde concernée details: Détails additionnels """ try: with self.get_connection() as conn: cursor = conn.cursor() details_json = json.dumps(details) if details else None cursor.execute(''' INSERT INTO alerts_history (alert_type, backup_name, details) VALUES (?, ?, ?) ''', (alert_type, backup_name, details_json)) conn.commit() except Exception as e: logger.error(f"Erreur lors de l'enregistrement de l'alerte: {str(e)}")