-- Stored Procedure per sincronizzare tbl_strOspiti
-- 
-- FASE 1: Raccoglie tutti i record da tutti i db pordenonelegge_it_2026* -> pnlegge_common
-- FASE 2: Redistribuisce da pnlegge_common -> tutti i db pordenonelegge_it_2026*
--
-- Uso: 
-- 1. Creare la procedura nel database pnlegge_common:
--    mysql -u root -p pnlegge_common < sync_strospiti_procedure.sql
--
-- 2. Eseguire la procedura:
--    mysql -u root -p pnlegge_common -e "CALL sync_strOspiti_consolidate_and_distribute();"

DELIMITER $$

DROP PROCEDURE IF EXISTS sync_strOspiti_consolidate_and_distribute$$

CREATE PROCEDURE sync_strOspiti_consolidate_and_distribute()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE db_name VARCHAR(100);
    DECLARE sql_stmt TEXT;
    DECLARE affected_rows INT;
    DECLARE total_collected INT DEFAULT 0;
    DECLARE total_distributed INT DEFAULT 0;
    
    -- Cursore per iterare sui database che iniziano con "pordenonelegge_it_2026"
    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;
    
    -- ========================================================================
    -- FASE 1: RACCOLTA - Consolida tutti i record in pnlegge_common
    -- ========================================================================
    SELECT '========================================================================' AS msg;
    SELECT 'FASE 1: RACCOLTA - Consolidamento in pnlegge_common' AS msg;
    SELECT '========================================================================' AS msg;
    SELECT '';
    
    OPEN db_cursor;
    
    collect_loop: LOOP
        FETCH db_cursor INTO db_name;
        IF done THEN
            LEAVE collect_loop;
        END IF;
        
        SELECT CONCAT('Raccolta da: ', db_name) AS processing;
        
        -- Verifica se la tabella esiste nel database sorgente
        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_strOspiti'''
        );
        PREPARE stmt FROM @check_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        IF @table_exists > 0 THEN
            -- Inserisce in pnlegge_common i record che non esistono ancora
            SET @collect_sql = CONCAT(
                'INSERT INTO `', DATABASE(), '`.`tbl_strOspiti` ',
                '(ospiteId, curatoreId, referenteId, casaEditriceId, stato, ',
                'nome, cognome, accompagnatoreNome, accompagnatoreCognome, ',
                'tipoOspite, straniero, richiestoInterprete, richiestoAngelo, ',
                'telefono, cellulare, telefonoAssistente, notaTelefono, ',
                'email, emailAssistente, notaEmail, tipoPagamento, compenso, ',
                'anniPartecipazione, modalitaPagamento, createUserId, createTime, ',
                'updateUserId, updateTime, soggettoIva, luogoNascita, dataNascita, ',
                'resIndir, resCitta, resCap, resProv, resStato, codiceFiscale, ',
                'partitaIva, banca, iban, compensoConcordato, tipoCompenso, ',
                'aliquotaIva, aliquotaRitenuta, dich5000, luogoArrivo, luogoPartenza) ',
                'SELECT ',
                's.ospiteId, s.curatoreId, s.referenteId, s.casaEditriceId, s.stato, ',
                's.nome, s.cognome, s.accompagnatoreNome, s.accompagnatoreCognome, ',
                's.tipoOspite, s.straniero, s.richiestoInterprete, s.richiestoAngelo, ',
                's.telefono, s.cellulare, s.telefonoAssistente, s.notaTelefono, ',
                's.email, s.emailAssistente, s.notaEmail, s.tipoPagamento, s.compenso, ',
                's.anniPartecipazione, s.modalitaPagamento, s.createUserId, s.createTime, ',
                's.updateUserId, s.updateTime, s.soggettoIva, s.luogoNascita, s.dataNascita, ',
                's.resIndir, s.resCitta, s.resCap, s.resProv, s.resStato, s.codiceFiscale, ',
                's.partitaIva, s.banca, s.iban, s.compensoConcordato, s.tipoCompenso, ',
                's.aliquotaIva, s.aliquotaRitenuta, s.dich5000, s.luogoArrivo, s.luogoPartenza ',
                'FROM `', db_name, '`.`tbl_strOspiti` s ',
                'LEFT JOIN `', DATABASE(), '`.`tbl_strOspiti` d ',
                '    ON s.cognome = d.cognome AND s.nome = d.nome ',
                'WHERE d.id IS NULL'
            );
            
            PREPARE stmt FROM @collect_sql;
            EXECUTE stmt;
            SET affected_rows = ROW_COUNT();
            SET total_collected = total_collected + affected_rows;
            DEALLOCATE PREPARE stmt;
            
            SELECT CONCAT('  ✓ Raccolti ', affected_rows, ' nuovi record da ', db_name) AS result;
        ELSE
            SELECT CONCAT('  ⚠ Tabella tbl_strOspiti non trovata in: ', db_name) AS warning;
        END IF;
        
        SELECT '';
        
    END LOOP;
    
    CLOSE db_cursor;
    
    SELECT CONCAT('FASE 1 completata: ', total_collected, ' record totali raccolti in pnlegge_common') AS summary;
    SELECT '';
    SELECT '';
    
    -- ========================================================================
    -- FASE 2: DISTRIBUZIONE - Distribuisce da pnlegge_common a tutti i db
    -- ========================================================================
    SELECT '========================================================================' AS msg;
    SELECT 'FASE 2: DISTRIBUZIONE - Da pnlegge_common a tutti i database' AS msg;
    SELECT '========================================================================' AS msg;
    SELECT '';
    
    -- Resetta il cursore
    SET done = FALSE;
    OPEN db_cursor;
    
    distribute_loop: LOOP
        FETCH db_cursor INTO db_name;
        IF done THEN
            LEAVE distribute_loop;
        END IF;
        
        SELECT CONCAT('Distribuzione verso: ', db_name) AS processing;
        
        -- Verifica se la tabella esiste nel database target
        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_strOspiti'''
        );
        PREPARE stmt FROM @check_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        IF @table_exists > 0 THEN
            -- Distribuisce da pnlegge_common al database target
            SET @distribute_sql = CONCAT(
                'INSERT INTO `', db_name, '`.`tbl_strOspiti` ',
                '(ospiteId, curatoreId, referenteId, casaEditriceId, stato, ',
                'nome, cognome, accompagnatoreNome, accompagnatoreCognome, ',
                'tipoOspite, straniero, richiestoInterprete, richiestoAngelo, ',
                'telefono, cellulare, telefonoAssistente, notaTelefono, ',
                'email, emailAssistente, notaEmail, tipoPagamento, compenso, ',
                'anniPartecipazione, modalitaPagamento, createUserId, createTime, ',
                'updateUserId, updateTime, soggettoIva, luogoNascita, dataNascita, ',
                'resIndir, resCitta, resCap, resProv, resStato, codiceFiscale, ',
                'partitaIva, banca, iban, compensoConcordato, tipoCompenso, ',
                'aliquotaIva, aliquotaRitenuta, dich5000, luogoArrivo, luogoPartenza) ',
                'SELECT ',
                's.ospiteId, s.curatoreId, s.referenteId, s.casaEditriceId, s.stato, ',
                's.nome, s.cognome, s.accompagnatoreNome, s.accompagnatoreCognome, ',
                's.tipoOspite, s.straniero, s.richiestoInterprete, s.richiestoAngelo, ',
                's.telefono, s.cellulare, s.telefonoAssistente, s.notaTelefono, ',
                's.email, s.emailAssistente, s.notaEmail, s.tipoPagamento, s.compenso, ',
                's.anniPartecipazione, s.modalitaPagamento, s.createUserId, s.createTime, ',
                's.updateUserId, s.updateTime, s.soggettoIva, s.luogoNascita, s.dataNascita, ',
                's.resIndir, s.resCitta, s.resCap, s.resProv, s.resStato, s.codiceFiscale, ',
                's.partitaIva, s.banca, s.iban, s.compensoConcordato, s.tipoCompenso, ',
                's.aliquotaIva, s.aliquotaRitenuta, s.dich5000, s.luogoArrivo, s.luogoPartenza ',
                'FROM `', DATABASE(), '`.`tbl_strOspiti` s ',
                'LEFT JOIN `', db_name, '`.`tbl_strOspiti` d ',
                '    ON s.cognome = d.cognome AND s.nome = d.nome ',
                'WHERE d.id IS NULL'
            );
            
            PREPARE stmt FROM @distribute_sql;
            EXECUTE stmt;
            SET affected_rows = ROW_COUNT();
            SET total_distributed = total_distributed + affected_rows;
            DEALLOCATE PREPARE stmt;
            
            SELECT CONCAT('  ✓ Distribuiti ', affected_rows, ' record verso ', db_name) AS result;
        ELSE
            SELECT CONCAT('  ⚠ Tabella tbl_strOspiti non trovata in: ', db_name) AS warning;
        END IF;
        
        SELECT '';
        
    END LOOP;
    
    CLOSE db_cursor;
    
    -- Riepilogo finale
    SELECT '========================================================================' AS msg;
    SELECT 'SINCRONIZZAZIONE COMPLETATA!' AS msg;
    SELECT '========================================================================' AS msg;
    SELECT CONCAT('Totale record raccolti in pnlegge_common: ', total_collected) AS summary1;
    SELECT CONCAT('Totale record distribuiti ai database: ', total_distributed) AS summary2;
    SELECT '';
END$$

DELIMITER ;

-- Per eseguire la procedura:
-- CALL sync_strOspiti_consolidate_and_distribute();
