ids.alfacom.it/extracted_idf/cleanup_ddos_ips_hours_debug.sql

-- ============================================
-- STORED PROCEDURE DEBUG PER PULIZIA IP ORARIA
-- Versione con logging dettagliato per diagnostica
-- ============================================

DELIMITER //

DROP PROCEDURE IF EXISTS cleanup_ddos_ips_hours_debug//

CREATE PROCEDURE cleanup_ddos_ips_hours_debug(
    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 '';
    DECLARE has_error BOOLEAN DEFAULT FALSE;
    
    -- Gestione errori con logging dettagliato
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET has_error = TRUE;
        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 SQL: State=', @p1, ' Message=', @p2));
        
        SELECT CONCAT('❌ ERRORE: ', @p1, ' - ', @p2) as debug_error;
    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;
    
    -- Log inizio con parametri
    INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, status, message)
    VALUES (NOW(), 'CLEANUP_HOURS', list_name_filter, 'START', 
            CONCAT('DEBUG: retention_hours=', retention_hours, ', dry_run=', dry_run));
    
    -- Inizio transazione
    START TRANSACTION;
    
    -- Conta totale IP nella lista
    SELECT COUNT(*) INTO total_ips
    FROM ip_list 
    WHERE list_name = list_name_filter;
    
    -- Log conteggio totale
    INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, status, message)
    VALUES (NOW(), 'CLEANUP_HOURS', list_name_filter, 'INFO', 
            CONCAT('DEBUG: Totale IP in lista: ', total_ips));
    
    -- Conta IP da rimuovere con condizione dettagliata
    SELECT COUNT(*) INTO old_ips
    FROM ip_list 
    WHERE list_name = list_name_filter
    AND retrieved_at < DATE_SUB(NOW(), INTERVAL retention_hours HOUR);
    
    -- Log conteggio IP vecchi
    INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, status, message)
    VALUES (NOW(), 'CLEANUP_HOURS', list_name_filter, 'INFO', 
            CONCAT('DEBUG: IP da rimuovere (>', retention_hours, 'h): ', old_ips));
    
    -- Verifica condizione temporale
    SELECT DATE_SUB(NOW(), INTERVAL retention_hours HOUR) as cutoff_time;
    
    SET report_message = CONCAT(
        'Lista: ', list_name_filter, 
        ' | Totale IP: ', total_ips,
        ' | IP da rimuovere: ', old_ips,
        ' | Ritenzione: ', retention_hours, ' ore'
    );
    
    IF old_ips > 0 THEN
        IF dry_run = FALSE THEN
            -- Log prima della DELETE
            INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, status, message)
            VALUES (NOW(), 'CLEANUP_HOURS', list_name_filter, 'INFO', 
                    'DEBUG: Esecuzione DELETE...');
            
            -- Esegui DELETE con logging
            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 risultato DELETE
            INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, status, message)
            VALUES (NOW(), 'CLEANUP_HOURS', list_name_filter, 'INFO', 
                    CONCAT('DEBUG: ROW_COUNT() dopo DELETE: ', deleted_count));
            
            -- Verifica errori
            IF has_error = FALSE THEN
                -- Log successo
                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: ', deleted_count));
                
                -- Commit transazione
                COMMIT;
                
                INSERT INTO ip_cleanup_log (operation_time, operation_type, list_name, status, message)
                VALUES (NOW(), 'CLEANUP_HOURS', list_name_filter, 'INFO', 'DEBUG: COMMIT eseguito');
                
                SELECT CONCAT('✅ PULIZIA ORARIA COMPLETATA: Rimossi ', deleted_count, ' IP dalla lista ', list_name_filter, ' (>', retention_hours, 'h)') as result;
            ELSE
                ROLLBACK;
                SELECT '❌ ERRORE: Transazione annullata' as result;
            END IF;
            
        ELSE
            -- Modalità dry run
            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'));
            
            COMMIT;
            
            SELECT CONCAT('🔍 DRY RUN ORARIO: Verrebbero rimossi ', old_ips, ' IP dalla lista ', list_name_filter, ' (>', retention_hours, 'h)') 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_HOURS', list_name_filter, 
               total_ips, 0, 'INFO',
               CONCAT(report_message, ' | Nessun IP da rimuovere'));
        
        COMMIT;
        
        SELECT CONCAT('ℹ️ NESSUNA PULIZIA ORARIA NECESSARIA: Tutti gli IP in ', list_name_filter, ' sono più recenti di ', retention_hours, ' ore') as result;
    END IF;
    
END//

DELIMITER ;

-- ============================================
-- QUERY DIAGNOSTICHE AGGIUNTIVE
-- ============================================

-- Procedura per verificare constraint e trigger
DELIMITER //

DROP PROCEDURE IF EXISTS check_table_constraints//

CREATE PROCEDURE check_table_constraints()
BEGIN
    -- Verifica foreign keys
    SELECT 
        CONSTRAINT_NAME,
        REFERENCED_TABLE_NAME,
        REFERENCED_COLUMN_NAME
    FROM information_schema.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = 'LOG_MIKROTIK' 
    AND TABLE_NAME = 'ip_list'
    AND REFERENCED_TABLE_NAME IS NOT NULL;
    
    -- Verifica trigger
    SELECT 
        TRIGGER_NAME,
        EVENT_MANIPULATION,
        ACTION_TIMING
    FROM information_schema.TRIGGERS 
    WHERE EVENT_OBJECT_SCHEMA = 'LOG_MIKROTIK' 
    AND EVENT_OBJECT_TABLE = 'ip_list';
    
    -- Verifica autocommit
    SELECT @@autocommit as autocommit_status;
    
END//

DELIMITER ;