-- Script per rimuovere duplicati e risincronizzare
-- Eseguire questo script per correggere il problema dei doppioni

DELIMITER $$

DROP PROCEDURE IF EXISTS fix_duplicates_and_resync$$

CREATE PROCEDURE fix_duplicates_and_resync()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE db_name VARCHAR(100);
    
    DECLARE db_cursor CURSOR FOR 
        SELECT schema_name 
        FROM information_schema.schemata 
        WHERE schema_name LIKE 'pordenonelegge_it_2026%'
        ORDER BY schema_name;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    SELECT '========================================================================' AS msg;
    SELECT 'PULIZIA DUPLICATI IN PNLEGGE_COMMON' AS msg;
    SELECT '========================================================================' AS msg;
    
    -- STEP 1: Rimuovi duplicati da tbl_editori in pnlegge_common
    -- Mantiene solo il record con casaEditriceId più basso per ogni denominazione
    SELECT 'Rimozione duplicati editori da pnlegge_common...' AS processing;
    
    DELETE e1 FROM pnlegge_common.tbl_editori e1
    INNER JOIN pnlegge_common.tbl_editori e2 
    WHERE e1.denominazione = e2.denominazione 
    AND e1.casaEditriceId > e2.casaEditriceId;
    
    SELECT CONCAT('✓ Duplicati editori rimossi da pnlegge_common') AS result;
    SELECT '';
    
    -- STEP 2: Rimuovi duplicati da tbl_addetti in pnlegge_common
    SELECT 'Rimozione duplicati addetti da pnlegge_common...' AS processing;
    
    DELETE a1 FROM pnlegge_common.tbl_addetti a1
    INNER JOIN pnlegge_common.tbl_addetti a2 
    INNER JOIN pnlegge_common.tbl_editori e1 ON a1.editoreId = e1.casaEditriceId
    INNER JOIN pnlegge_common.tbl_editori e2 ON a2.editoreId = e2.casaEditriceId
    WHERE a1.cognome = a2.cognome 
    AND a1.nome = a2.nome 
    AND e1.denominazione = e2.denominazione
    AND a1.id > a2.id;
    
    SELECT CONCAT('✓ Duplicati addetti rimossi da pnlegge_common') AS result;
    SELECT '';
    
    SELECT '========================================================================' AS msg;
    SELECT 'PULIZIA DUPLICATI NEI DATABASE 2026' AS msg;
    SELECT '========================================================================' AS msg;
    
    OPEN db_cursor;
    
    clean_loop: LOOP
        FETCH db_cursor INTO db_name;
        IF done THEN
            LEAVE clean_loop;
        END IF;
        
        SELECT CONCAT('Pulizia duplicati in: ', db_name) AS processing;
        
        -- Verifica se le tabelle esistono
        SET @table_exists = 0;
        SET @check_sql = CONCAT(
            'SELECT COUNT(*) INTO @table_exists FROM information_schema.tables ',
            'WHERE table_schema = ''', db_name, ''' AND table_name = ''tbl_editori'''
        );
        PREPARE stmt FROM @check_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        IF @table_exists > 0 THEN
            -- Rimuovi duplicati editori nel database target
            SET @clean_sql = CONCAT(
                'DELETE e1 FROM `', db_name, '`.tbl_editori e1 ',
                'INNER JOIN `', db_name, '`.tbl_editori e2 ',
                'WHERE e1.denominazione = e2.denominazione ',
                'AND e1.casaEditriceId > e2.casaEditriceId'
            );
            PREPARE stmt FROM @clean_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            
            -- Verifica se tbl_addetti esiste
            SET @addetti_exists = 0;
            SET @check_sql = CONCAT(
                'SELECT COUNT(*) INTO @addetti_exists FROM information_schema.tables ',
                'WHERE table_schema = ''', db_name, ''' AND table_name = ''tbl_addetti'''
            );
            PREPARE stmt FROM @check_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            
            IF @addetti_exists > 0 THEN
                -- Rimuovi duplicati addetti nel database target
                SET @clean_sql = CONCAT(
                    'DELETE a1 FROM `', db_name, '`.tbl_addetti a1 ',
                    'INNER JOIN `', db_name, '`.tbl_addetti a2 ',
                    'INNER JOIN `', db_name, '`.tbl_editori e1 ON a1.editoreId = e1.casaEditriceId ',
                    'INNER JOIN `', db_name, '`.tbl_editori e2 ON a2.editoreId = e2.casaEditriceId ',
                    'WHERE a1.cognome = a2.cognome ',
                    'AND a1.nome = a2.nome ',
                    'AND e1.denominazione = e2.denominazione ',
                    'AND a1.id > a2.id'
                );
                PREPARE stmt FROM @clean_sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
            
            SELECT CONCAT('  ✓ Duplicati rimossi da ', db_name) AS result;
        END IF;
        
        SELECT '';
        
    END LOOP;
    
    CLOSE db_cursor;
    
    SELECT '========================================================================' AS msg;
    SELECT 'PULIZIA COMPLETATA!' AS msg;
    SELECT '========================================================================' AS msg;
    
    -- Conta record finali
    SELECT COUNT(*) INTO @total_editori FROM pnlegge_common.tbl_editori;
    SELECT COUNT(*) INTO @total_addetti FROM pnlegge_common.tbl_addetti;
    
    SELECT CONCAT('Editori unici in pnlegge_common: ', @total_editori) AS summary1;
    SELECT CONCAT('Addetti unici in pnlegge_common: ', @total_addetti) AS summary2;
    
END$$

DELIMITER ;
