/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
DELIMITER $$

DROP PROCEDURE IF EXISTS ezos_refresh_2025 $$
CREATE PROCEDURE ezos_refresh_2025()
BEGIN
  DECLARE v_table VARCHAR(255);
  DECLARE exit_loop INT DEFAULT 0;

  DECLARE cur_options CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = DATABASE() AND table_name REGEXP '_options$';

  DECLARE cur_posts CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = DATABASE() AND table_name REGEXP '_posts$';

  DECLARE cur_postmeta CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = DATABASE() AND table_name REGEXP '_postmeta$';

  DECLARE cur_links CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = DATABASE() AND table_name REGEXP '_links$';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = 1;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @ezos_ignore = 1;

  /* Options-Tabellen: Parent-Theme Divi + Child-Theme easy-open-source-hosting-child, SEO-Basics */
  SET exit_loop = 0;
  OPEN cur_options;
  options_loop: LOOP
    FETCH cur_options INTO v_table;
    IF exit_loop = 1 THEN SET exit_loop = 0; LEAVE options_loop; END IF;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET option_value="Divi" WHERE option_name="template"');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET option_value="easy-open-source-hosting-child" WHERE option_name="stylesheet"');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    /* theme_mods vom Divi-Parent auf Child kopieren, wenn Child noch keine Mods hat */
    SET @sql = CONCAT(
      'INSERT INTO `', v_table, '` (option_name, option_value, autoload) ',
      'SELECT "theme_mods_easy-open-source-hosting-child", option_value, "yes" FROM `', v_table, '` ',
      'WHERE option_name="theme_mods_Divi" ',
      'AND NOT EXISTS (SELECT 1 FROM `', v_table, '` WHERE option_name="theme_mods_easy-open-source-hosting-child") ',
      'LIMIT 1'
    );
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET option_value="/%postname%/" WHERE option_name="permalink_structure"');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET option_value="1" WHERE option_name="blog_public"');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur_options;

  /* Postmeta: Menü-Links zum Support-Portal */
  SET exit_loop = 0;
  OPEN cur_postmeta;
  postmeta_loop: LOOP
    FETCH cur_postmeta INTO v_table;
    IF exit_loop = 1 THEN SET exit_loop = 0; LEAVE postmeta_loop; END IF;

    SET @sql = CONCAT(
      'UPDATE `', v_table, '` SET meta_value="https://support.ezoshosting.com" ',
      'WHERE meta_key="_menu_item_url" AND (',
      'meta_value LIKE "%clientarea%" OR meta_value LIKE "%whmcs%" OR meta_value LIKE "%support%" OR ',
      'meta_value LIKE "%submitticket%" OR meta_value LIKE "%knowledgebase%" OR meta_value LIKE "%kb%" OR ',
      'meta_value LIKE "%tickets%" OR meta_value LIKE "%announcements%" OR meta_value LIKE "%cart.php%")'
    );
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur_postmeta;

  /* Links-Tabelle (Blogroll), falls vorhanden) */
  SET exit_loop = 0;
  OPEN cur_links;
  links_loop: LOOP
    FETCH cur_links INTO v_table;
    IF exit_loop = 1 THEN SET exit_loop = 0; LEAVE links_loop; END IF;

    SET @sql = CONCAT(
      'UPDATE `', v_table, '` SET link_url="https://support.ezoshosting.com" ',
      'WHERE link_url LIKE "%clientarea%" OR link_url LIKE "%whmcs%" OR link_url LIKE "%support%" OR ',
      'link_url LIKE "%submitticket%" OR link_url LIKE "%knowledgebase%" OR link_url LIKE "%kb%" OR ',
      'link_url LIKE "%tickets%" OR link_url LIKE "%announcements%" OR link_url LIKE "%cart.php%"'
    );
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur_links;

  /* Posts-Inhalte: gängige WHMCS/Clientarea-Pfade auf Support-Portal & Jahreszahl */
  SET exit_loop = 0;
  OPEN cur_posts;
  posts_loop: LOOP
    FETCH cur_posts INTO v_table;
    IF exit_loop = 1 THEN SET exit_loop = 0; LEAVE posts_loop; END IF;

    /* HTTPS erzwingen */
    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, "http://support.ezoshosting.com", "https://support.ezoshosting.com")');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    /* Häufige WHMCS/Clientarea Pfade => Support-Portal Root */
    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="clientarea.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="/clientarea.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="submitticket.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="/submitticket.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="supporttickets.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="/supporttickets.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="announcements.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="/announcements.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="knowledgebase.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="/knowledgebase.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="kb.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="/kb.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="billing.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="/billing.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="clients.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="/clients.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="viewinvoice.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="/viewinvoice.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="cart.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, ''href="/cart.php'', ''href="https://support.ezoshosting.com'')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    /* Jahreszahl, konservativ nur im Content */
    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, "© 2008", "© 2025")');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    SET @sql = CONCAT('UPDATE `', v_table, '` SET post_content=REPLACE(post_content, "©2008", "© 2025")');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur_posts;

END $$
CALL ezos_refresh_2025() $$
DROP PROCEDURE IF EXISTS ezos_refresh_2025 $$

DELIMITER ;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;