import spotdl
import os
import logging
from datetime import datetime
from crontab import CronTab
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

# Configuration des chemins
MUSIC_DIR = "/media/nas/music"
PLAYLIST_FILE = "/scripts/playlists.txt"
LOG_FILE = "/scripts/spotdl_sync.log"

# Configuration du logging
logging.basicConfig(
    filename=LOG_FILE,
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

# Configuration du serveur SMTP pour les notifications
SMTP_SERVER = "smtp.gmail.com"
SMTP_PORT = 587
SMTP_USER = "admin@example.com"
SMTP_PASSWORD = "password_securise"
NOTIFICATION_EMAIL = "notification@example.com"

def send_notification(subject, message):
    try:
        msg = MIMEMultipart()
        msg['From'] = SMTP_USER
        msg['To'] = NOTIFICATION_EMAIL
        msg['Subject'] = subject
        
        msg.attach(MIMEText(message, 'plain'))
        
        server = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
        server.starttls()
        server.login(SMTP_USER, SMTP_PASSWORD)
        server.send_message(msg)
        server.quit()
        
        logging.info("Notification envoyée avec succès")
    except Exception as e:
        logging.error(f"Erreur lors de l'envoi de la notification: {str(e)}")

def read_playlists():
    try:
        with open(PLAYLIST_FILE, 'r') as f:
            return [line.strip() for line in f.readlines() if line.strip()]
    except Exception as e:
        logging.error(f"Erreur lors de la lecture des playlists: {str(e)}")
        return []

def sync_playlists():
    start_time = datetime.now()
    logging.info(f"Démarrage de la synchronisation: {start_time}")
    
    playlists = read_playlists()
    if not playlists:
        logging.error("Aucune playlist trouvée")
        send_notification("Erreur SpotDL", "Aucune playlist trouvée pour la synchronisation")
        return
    
    total_tracks = 0
    success_tracks = 0
    error_tracks = 0
    
    for playlist_url in playlists:
        try:
            logging.info(f"Traitement de la playlist: {playlist_url}")
            
            # Configuration de spotdl pour cette playlist
            downloader = spotdl.Spotdl(
                client_id="votre_client_id",
                client_secret="votre_client_secret",
                output_format="mp3",
                output_dir=MUSIC_DIR,
                overwrite="skip"
            )
            
            # Téléchargement de la playlist
            results = downloader.download_playlist(playlist_url)
            
            # Comptabilisation des résultats
            playlist_total = len(results)
            playlist_success = sum(1 for r in results if r['status'] == 'success')
            playlist_error = playlist_total - playlist_success
            
            total_tracks += playlist_total
            success_tracks += playlist_success
            error_tracks += playlist_error
            
            logging.info(f"Playlist terminée: {playlist_success}/{playlist_total} titres réussis")
            
        except Exception as e:
            logging.error(f"Erreur lors du traitement de la playlist {playlist_url}: {str(e)}")
            error_tracks += 1
            
    end_time = datetime.now()
    duration = (end_time - start_time).total_seconds() / 60
    
    # Journalisation du résumé
    summary = f"Synchronisation terminée en {duration:.2f} minutes. "
    summary += f"Total: {total_tracks} titres, Réussis: {success_tracks}, Échecs: {error_tracks}"
    logging.info(summary)
    
    # Envoi de la notification
    send_notification(
        "Rapport de synchronisation SpotDL",
        f"Synchronisation terminée le {end_time}\n\n"
        f"Durée: {duration:.2f} minutes\n"
        f"Titres traités: {total_tracks}\n"
        f"Titres réussis: {success_tracks}\n"
        f"Titres en échec: {error_tracks}\n\n"
        f"Consultez les logs pour plus de détails."
    )

if __name__ == "__main__":
    # Configuration automatique dans cron si besoin
    if not os.path.exists("/var/spool/cron/crontabs/"):
        cron = CronTab(user=True)
        job = cron.new(command='python3 /scripts/music_sync.py')
        job.hour.on(2)  # Exécution quotidienne à 2h
        cron.write()
        logging.info("Tâche cron configurée pour exécution quotidienne à 2h")
    
    # Exécution de la synchronisation
    sync_playlists()
                

-- Création d'une table partitionnée par date
CREATE TABLE logs_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_time TIMESTAMP NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    user_id INT NOT NULL,
    ip_address VARCHAR(15),
    details TEXT,
    INDEX idx_event_time (event_time),
    INDEX idx_event_type (event_type),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB
PARTITION BY RANGE (UNIX_TIMESTAMP(event_time)) (
    PARTITION p_2024_01 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')),
    PARTITION p_2024_02 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Création d'un index columnstore pour les requêtes analytiques
ALTER TABLE analytics_data
ADD INDEX idx_analytics_columnstore (date, metric_type, value) USING COLUMNSTORE;

-- Optimisation de requête avec EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT 
    DATE(event_time) AS day,
    event_type,
    COUNT(*) AS event_count
FROM 
    logs_events
WHERE 
    event_time BETWEEN '2024-01-01' AND '2024-03-31'
    AND event_type IN ('login', 'logout', 'password_change')
GROUP BY 
    day, event_type
ORDER BY 
    day, event_count DESC;

-- Procédure stockée pour purge automatique de données anciennes
DELIMITER //
CREATE PROCEDURE purge_old_logs(IN retention_months INT)
BEGIN
    DECLARE cutoff_date DATE;
    SET cutoff_date = DATE_SUB(CURRENT_DATE(), INTERVAL retention_months MONTH);
    
    -- Supprimer les partitions plus anciennes que la rétention
    -- Cette approche est beaucoup plus rapide que de supprimer des lignes individuelles
    SET @sql = CONCAT('ALTER TABLE logs_events DROP PARTITION ', 
                      (SELECT GROUP_CONCAT(p.partition_name)
                       FROM information_schema.partitions
                       WHERE p.table_name = 'logs_events'
                       AND p.partition_name != 'p_future'
                       AND p.partition_description < UNIX_TIMESTAMP(cutoff_date)));
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- Recréer la partition pour le mois en cours (si nécessaire)
    SET @current_month = DATE_FORMAT(CURRENT_DATE(), '%Y_%m');
    SET @next_month_start = DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01');
    
    SET @check_partition = (SELECT COUNT(*)
                           FROM information_schema.partitions
                           WHERE table_name = 'logs_events'
                           AND partition_name = CONCAT('p_', @current_month));
    
    IF @check_partition = 0 THEN
        SET @sql = CONCAT('ALTER TABLE logs_events ADD PARTITION (PARTITION p_', 
                          @current_month, 
                          ' VALUES LESS THAN (UNIX_TIMESTAMP(''', 
                          @next_month_start, 
                          ''')))');
        
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
    
    -- Optimiser la table après les modifications
    OPTIMIZE TABLE logs_events;
END //
DELIMITER ;

-- Script Python pour exécuter la purge régulièrement
'''
import mysql.connector
import logging
from datetime import datetime

# Configuration de la connexion
db_config = {
    'host': 'localhost',
    'user': 'admin',
    'password': 'secure_password',
    'database': 'application_db'
}

# Configuration du logging
logging.basicConfig(
    filename='/var/log/db_maintenance.log',
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

try:
    # Se connecter à la base de données
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()
    
    logging.info("Lancement de la purge des logs anciens")
    
    # Définir la période de rétention (13 mois pour RGPD)
    retention_months = 13
    
    # Exécuter la procédure stockée
    cursor.callproc('purge_old_logs', [retention_months])
    
    # Valider les changements
    conn.commit()
    
    # Récupérer les statistiques après la purge
    cursor.execute("SELECT COUNT(*) FROM logs_events")
    count_after = cursor.fetchone()[0]
    
    logging.info(f"Purge terminée. {count_after} enregistrements restants dans la table.")
    
except Exception as e:
    logging.error(f"Erreur lors de la purge: {str(e)}")
finally:
    if 'conn' in locals() and conn.is_connected():
        cursor.close()
        conn.close()
        logging.info("Connexion à la base de données fermée")
'''