ids.alfacom.it/extracted_idf/cleanup_ddos_ips_hours_fixed.sql

-- ============================================
-- STORED PROCEDURE PULIZIA IP ORARIA - FIXED
-- Risolve il problema di collation nel CONCAT
-- ============================================

DELIMITER //

DROP PROCEDURE IF EXISTS cleanup_ddos_ips_hours_fixed//

CREATE PROCEDURE cleanup_ddos_ips_hours_fixed(
    IN retention_hours INT,
    IN list_name_filter VARCHAR(100),
    IN dry_run BOOLEAN
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE total_ips INT DEFAULT 0;
    DECLARE old_ips INT DEFAULT 0;
    DECLARE deleted_count INT DEFAULT 0;
    DECLARE report_message TEXT DEFAULT '';
    
    -- Gestione errori
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        GET DIAGNOSTICS CONDITION 1
            @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, status, message)
        VALUES (NOW(), 'CLEANUP_HOURS', list_name_filter, 'ERROR', 
                CONCAT('ERRORE: ', CAST(@p1 AS CHAR), ' - ', CAST(@p2 AS CHAR)));
    END;
    
    -- Gestione valori default
    IF retention_hours IS NULL THEN
        SET retention_hours = 24;
    END IF;
    
    IF list_name_filter IS NULL OR list_name_filter = '' THEN
        SET list_name_filter = 'ddos_detect_v03';
    END IF;
    
    IF dry_run IS NULL THEN
        SET dry_run = FALSE;
    END IF;
    
    -- Inizio transazione
    START TRANSACTION;
    
    -- Log inizio operazione (FIX: conversione esplicita di INT)
    INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, status, message)
    VALUES (NOW(), 'CLEANUP_HOURS', list_name_filter, 'START', 
            CONCAT('Inizio pulizia ORARIA - Ritenzione: ', CAST(retention_hours AS CHAR), ' ore - Dry run: ', CAST(dry_run AS CHAR)));
    
    -- Conta totale IP nella lista
    SELECT COUNT(*) INTO total_ips
    FROM ip_list 
    WHERE list_name = list_name_filter;
    
    -- Conta IP da rimuovere (basato su ore)
    SELECT COUNT(*) INTO old_ips
    FROM ip_list 
    WHERE list_name = list_name_filter
    AND retrieved_at < DATE_SUB(NOW(), INTERVAL retention_hours HOUR);
    
    -- Prepara messaggio report (FIX: conversioni esplicite)
    SET report_message = CONCAT(
        'Lista: ', list_name_filter, 
        ' | Totale IP: ', CAST(total_ips AS CHAR),
        ' | IP da rimuovere: ', CAST(old_ips AS CHAR),
        ' | Ritenzione: ', CAST(retention_hours AS CHAR), ' ore'
    );
    
    IF old_ips > 0 THEN
        IF dry_run = FALSE THEN
            -- Esegui pulizia reale
            DELETE FROM ip_list 
            WHERE list_name = list_name_filter
            AND retrieved_at < DATE_SUB(NOW(), INTERVAL retention_hours HOUR);
            
            -- Ottieni numero righe eliminate
            SET deleted_count = ROW_COUNT();
            
            -- Log successo (FIX: conversioni esplicite)
            INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, 
                                      records_before, records_removed, records_after, status, message)
            VALUES (NOW(), 'CLEANUP_HOURS', list_name_filter, 
                   total_ips, deleted_count, (total_ips - deleted_count), 'SUCCESS',
                   CONCAT(report_message, ' | Rimossi: ', CAST(deleted_count AS CHAR)));
                   
            SELECT CONCAT('✅ PULIZIA ORARIA COMPLETATA: Rimossi ', CAST(deleted_count AS CHAR), ' IP dalla lista ', list_name_filter, ' (>', CAST(retention_hours AS CHAR), 'h)') as result;
            
        ELSE
            -- Modalità dry run - solo log (FIX: conversioni esplicite)
            INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, 
                                      records_before, records_removed, status, message)
            VALUES (NOW(), 'DRY_RUN_HOURS', list_name_filter, 
                   total_ips, old_ips, 'INFO',
                   CONCAT(report_message, ' | SIMULAZIONE - Nessuna modifica eseguita'));
                   
            SELECT CONCAT('🔍 DRY RUN ORARIO: Verrebbero rimossi ', CAST(old_ips AS CHAR), ' IP dalla lista ', list_name_filter, ' (>', CAST(retention_hours AS CHAR), 'h)') as result;
        END IF;
    ELSE
        -- Nessun IP da rimuovere (FIX: conversioni esplicite)
        INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, 
                                  records_before, records_removed, status, message)
        VALUES (NOW(), 'CLEANUP_HOURS', list_name_filter, 
               total_ips, 0, 'INFO',
               CONCAT(report_message, ' | Nessun IP da rimuovere'));
               
        SELECT CONCAT('ℹ️ NESSUNA PULIZIA ORARIA NECESSARIA: Tutti gli IP in ', list_name_filter, ' sono più recenti di ', CAST(retention_hours AS CHAR), ' ore') as result;
    END IF;
    
    -- Commit transazione
    COMMIT;
    
END//

DELIMITER ;

-- ============================================
-- STORED PROCEDURE GIORNI CORRETTA
-- ============================================

DROP PROCEDURE IF EXISTS cleanup_ddos_ips_fixed//

CREATE PROCEDURE cleanup_ddos_ips_fixed(
    IN retention_days INT,
    IN list_name_filter VARCHAR(100),
    IN dry_run BOOLEAN
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE total_ips INT DEFAULT 0;
    DECLARE old_ips INT DEFAULT 0;
    DECLARE deleted_count INT DEFAULT 0;
    DECLARE report_message TEXT DEFAULT '';
    
    -- Gestione errori
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        GET DIAGNOSTICS CONDITION 1
            @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, status, message)
        VALUES (NOW(), 'CLEANUP', list_name_filter, 'ERROR', 
                CONCAT('ERRORE: ', CAST(@p1 AS CHAR), ' - ', CAST(@p2 AS CHAR)));
    END;
    
    -- Gestione valori default
    IF retention_days IS NULL THEN
        SET retention_days = 7;
    END IF;
    
    IF list_name_filter IS NULL OR list_name_filter = '' THEN
        SET list_name_filter = 'ddos_detect_v03';
    END IF;
    
    IF dry_run IS NULL THEN
        SET dry_run = FALSE;
    END IF;
    
    -- Inizio transazione
    START TRANSACTION;
    
    -- Log inizio operazione (FIX: conversione esplicita)
    INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, status, message)
    VALUES (NOW(), 'CLEANUP', list_name_filter, 'START', 
            CONCAT('Inizio pulizia - Ritenzione: ', CAST(retention_days AS CHAR), ' giorni - Dry run: ', CAST(dry_run AS CHAR)));
    
    -- Conta totale IP nella lista
    SELECT COUNT(*) INTO total_ips
    FROM ip_list 
    WHERE list_name = list_name_filter;
    
    -- Conta IP da rimuovere
    SELECT COUNT(*) INTO old_ips
    FROM ip_list 
    WHERE list_name = list_name_filter
    AND retrieved_at < DATE_SUB(NOW(), INTERVAL retention_days DAY);
    
    -- Prepara messaggio report (FIX: conversioni esplicite)
    SET report_message = CONCAT(
        'Lista: ', list_name_filter, 
        ' | Totale IP: ', CAST(total_ips AS CHAR),
        ' | IP da rimuovere: ', CAST(old_ips AS CHAR),
        ' | Ritenzione: ', CAST(retention_days AS CHAR), ' giorni'
    );
    
    IF old_ips > 0 THEN
        IF dry_run = FALSE THEN
            -- Esegui pulizia reale
            DELETE FROM ip_list 
            WHERE list_name = list_name_filter
            AND retrieved_at < DATE_SUB(NOW(), INTERVAL retention_days DAY);
            
            -- Ottieni numero righe eliminate
            SET deleted_count = ROW_COUNT();
            
            -- Log successo (FIX: conversioni esplicite)
            INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, 
                                      records_before, records_removed, records_after, status, message)
            VALUES (NOW(), 'CLEANUP', list_name_filter, 
                   total_ips, deleted_count, (total_ips - deleted_count), 'SUCCESS',
                   CONCAT(report_message, ' | Rimossi: ', CAST(deleted_count AS CHAR)));
                   
            SELECT CONCAT('✅ PULIZIA COMPLETATA: Rimossi ', CAST(deleted_count AS CHAR), ' IP dalla lista ', list_name_filter) as result;
            
        ELSE
            -- Modalità dry run - solo log
            INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, 
                                      records_before, records_removed, status, message)
            VALUES (NOW(), 'DRY_RUN', list_name_filter, 
                   total_ips, old_ips, 'INFO',
                   CONCAT(report_message, ' | SIMULAZIONE - Nessuna modifica eseguita'));
                   
            SELECT CONCAT('🔍 DRY RUN: Verrebbero rimossi ', CAST(old_ips AS CHAR), ' IP dalla lista ', list_name_filter) as result;
        END IF;
    ELSE
        -- Nessun IP da rimuovere
        INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, 
                                  records_before, records_removed, status, message)
        VALUES (NOW(), 'CLEANUP', list_name_filter, 
               total_ips, 0, 'INFO',
               CONCAT(report_message, ' | Nessun IP da rimuovere'));
               
        SELECT CONCAT('ℹ️ NESSUNA PULIZIA NECESSARIA: Tutti gli IP in ', list_name_filter, ' sono recenti') as result;
    END IF;
    
    -- Commit transazione
    COMMIT;
    
END//

DELIMITER ;

-- ============================================
-- PROCEDURE WRAPPER CORRETTE
-- ============================================

-- Procedure wrapper per dry run orario con valori default
DROP PROCEDURE IF EXISTS cleanup_ddos_ips_hours_dry_default_fixed//

CREATE PROCEDURE cleanup_ddos_ips_hours_dry_default_fixed()
BEGIN
    CALL cleanup_ddos_ips_hours_fixed(24, 'ddos_detect_v03', TRUE);
END//

-- Procedure wrapper per pulizia oraria con valori default
DROP PROCEDURE IF EXISTS cleanup_ddos_ips_hours_default_fixed//

CREATE PROCEDURE cleanup_ddos_ips_hours_default_fixed()
BEGIN
    CALL cleanup_ddos_ips_hours_fixed(24, 'ddos_detect_v03', FALSE);
END//

DELIMITER ;