DELIMITER $$
CREATE DEFINER=`pordenonelegge_it`@`localhost` PROCEDURE `sp_allinea_addetti_2026`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE db_name VARCHAR(128);

    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 = 1;

    /* =========================================================
       1) Assicuro esistenza pnlegge_common.tbl_addetti
          copiando la struttura dal primo tenant 2026
       ========================================================= */
    SELECT schema_name INTO db_name
    FROM information_schema.schemata
    WHERE schema_name LIKE 'pordenonelegge_it_2026%'
    ORDER BY schema_name
    LIMIT 1;

    IF db_name IS NOT NULL THEN
        SET @create_sql = CONCAT(
            'CREATE TABLE IF NOT EXISTS `pnlegge_common`.`tbl_addetti` LIKE `',
            REPLACE(db_name,'`','``'),
            '`.`tbl_addetti`'
        );
        PREPARE stmt FROM @create_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    /* =========================================================
       2) Loop su tutti i tenant 2026
       ========================================================= */
    OPEN db_cursor;

    read_loop: LOOP
        FETCH db_cursor INTO db_name;
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;

        /* ---------------------------------------------------------
           A) DB -> COMMON
           - prende addetti dal tenant
           - risale alla denominazione editore nel tenant
           - mappa l’editore sul common (ce)
           - inserisce solo se quell’addetto+editore non è già presente nel common
        --------------------------------------------------------- */
        SET @copy_to_common = CONCAT(
            'INSERT INTO `pnlegge_common`.`tbl_addetti` (`cognome`, `nome`, `telefono`, `cellulare`, `email`, `editoreId`) ',
            'SELECT a.`cognome`, a.`nome`, a.`telefono`, a.`cellulare`, a.`email`, ce.`casaEditriceId` ',
            'FROM `', REPLACE(db_name,'`','``'), '`.`tbl_addetti` a ',
            'LEFT JOIN `', REPLACE(db_name,'`','``'), '`.`tbl_editori` e ON a.`editoreId` = e.`casaEditriceId` ',
            'LEFT JOIN (',
            '   SELECT aa.`id`, aa.`nome`, aa.`cognome`, ee.`casaEditriceId`, ee.`denominazione` ',
            '   FROM `pnlegge_common`.`tbl_addetti` aa ',
            '   JOIN `pnlegge_common`.`tbl_editori` ee ON aa.`editoreId` = ee.`casaEditriceId`',
            ') ca ON ',
            '   LOWER(TRIM(a.`cognome`)) = LOWER(TRIM(ca.`cognome`)) ',
            '   AND LOWER(TRIM(a.`nome`)) = LOWER(TRIM(ca.`nome`)) ',
            '   AND LOWER(TRIM(e.`denominazione`)) = LOWER(TRIM(ca.`denominazione`)) ',
            'LEFT JOIN `pnlegge_common`.`tbl_editori` ce ON LOWER(TRIM(e.`denominazione`)) = LOWER(TRIM(ce.`denominazione`)) ',
            'WHERE ca.`id` IS NULL AND ce.`casaEditriceId` IS NOT NULL'
        );

        PREPARE stmt FROM @copy_to_common;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        /* ---------------------------------------------------------
           B) COMMON -> DB
           - prende addetti dal common
           - risale alla denominazione editore nel common
           - mappa l’editore sul tenant (ce)
           - inserisce solo se quell’addetto+editore non è già presente nel tenant
        --------------------------------------------------------- */
        SET @copy_to_db = CONCAT(
            'INSERT INTO `', REPLACE(db_name,'`','``'), '`.`tbl_addetti` (`cognome`, `nome`, `telefono`, `cellulare`, `email`, `editoreId`) ',
            'SELECT a.`cognome`, a.`nome`, a.`telefono`, a.`cellulare`, a.`email`, ce.`casaEditriceId` ',
            'FROM `pnlegge_common`.`tbl_addetti` a ',
            'LEFT JOIN `pnlegge_common`.`tbl_editori` e ON a.`editoreId` = e.`casaEditriceId` ',
            'LEFT JOIN (',
            '   SELECT aa.`id`, aa.`nome`, aa.`cognome`, ee.`casaEditriceId`, ee.`denominazione` ',
            '   FROM `', REPLACE(db_name,'`','``'), '`.`tbl_addetti` aa ',
            '   JOIN `', REPLACE(db_name,'`','``'), '`.`tbl_editori` ee ON aa.`editoreId` = ee.`casaEditriceId`',
            ') ca ON ',
            '   LOWER(TRIM(a.`cognome`)) = LOWER(TRIM(ca.`cognome`)) ',
            '   AND LOWER(TRIM(a.`nome`)) = LOWER(TRIM(ca.`nome`)) ',
            '   AND LOWER(TRIM(e.`denominazione`)) = LOWER(TRIM(ca.`denominazione`)) ',
            'LEFT JOIN `', REPLACE(db_name,'`','``'), '`.`tbl_editori` ce ON LOWER(TRIM(e.`denominazione`)) = LOWER(TRIM(ce.`denominazione`)) ',
            'WHERE ca.`id` IS NULL AND ce.`casaEditriceId` IS NOT NULL'
        );

        PREPARE stmt FROM @copy_to_db;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;

    CLOSE db_cursor;
END$$
DELIMITER ;