<?php

namespace forge12\contactform7\CF7DoubleOptIn {
    if (!defined('ABSPATH')) {
        exit;
    }

    /**
     * Class Frontend
     * Responsible to handle the frontend of the Double OptIn
     *
     * @package forge12\contactform7\CF7DoubleOptIn
     */
    class OptIn
    {
        /**
         * Stores the DB ID for the entry
         *
         * @var int
         */
        private $id = 0;

        /**
         * Stores the ID of the contact form 7 form.
         *
         * @var int
         */
        private $cf_form_id = 0;
        /**
         * Stores if the optin has been confirmed (1) or not (0)
         *
         * @var int
         */
        private $doubleoptin = 0;
        /**
         * Stores the content of the contact form 7 form content as a serialized string.
         *
         * @var string
         */
        private $content = "";
        /**
         * Stores the unique hash used to identify the opt-ins
         *
         * @var string
         */
        private $hash = "";
        /**
         * Stores the timestamp the opt-in mail has been sent to the user.
         *
         * @var string
         */
        private $createtime = "";
        /**
         * Stores the timestamp the opt-in has been confirmed by the user.
         */
        private $updatetime = "";
        /**
         * Stores the timestamp the opt-out has been confirmed by the user.
         */
        private $optouttime = "";
        /**
         * IP Address used to trigger the opt-in mail.
         *
         * @var string
         */
        private $ipaddr_register = "";
        /**
         * IP Address used to confirm the opt-in mail.
         */
        private $ipaddr_confirmation = "";
        /**
         * IP Address used to output.
         */
        private $ipaddr_optout = "";
        /**
         * Stores the files used within this form as a serialized string.
         */
        private $files = "";
        /**
         * Store the Category ID
         */
        private $category = 0;
        /**
         * Stores the form as html
         */
        private $form = '';
        /**
         * Stores the Mail send for the optin
         *
         * @var string
         */
        private $mail_optin = '';
        /**
         * Stores the E-Mail
         *
         * @var string
         */
        private $email = '';

        /**
         * The constructor
         */
        public function __construct(array $properties)
        {
            foreach ($properties as $name => $value) {
                if (isset($this->{$name})) {
                    $this->{$name} = $value;
                }
            }
        }

        /**
         * @param int $cf_form_id
         */
        public function set_cf_form_id($cf_form_id)
        {
            $this->cf_form_id = (int)$cf_form_id;
        }

        /**
         * @return int
         */
        public function get_cf_form_id()
        {
            return $this->cf_form_id;
        }

        /**
         * Return the ID of the Optin
         *
         * @return int
         */
        public function get_id()
        {
            return $this->id;
        }

        /**
         * Return the hash of the optin.
         *
         * @return string
         */
        public function get_hash()
        {
            return $this->hash;
        }

        /**
         * Return true|false depending on confirm status
         *
         * @return bool
         */
        public function is_confirmed()
        {
            return (bool)$this->get_doubleoptin();
        }

        /**
         * Return true|false wether the form has been confirmed or not.
         *
         * @return bool
         */
        public function is_optout()
        {
            if (true != $this->get_doubleoptin() && !empty($this->get_ipaddr_optout()) && !empty($this->get_optouttime())) {
                return true;
            }

            return false;
        }

        /**
         * update the optin value
         *
         * @param int $confirmed
         */
        public function set_doubleoptin($confirmed)
        {
            $this->doubleoptin = (int)$confirmed;
        }

        /**
         * Return the optin value for the form.
         *
         * @return int
         */
        public function get_doubleoptin()
        {
            return $this->doubleoptin;
        }

        /**
         * @param string $timestamp
         */
        public function set_createtime($timestamp)
        {
            $this->createtime = $timestamp;
        }

        /**
         * Return the Date how long the opt in will be valid
         *
         * @return string
         */
        public function get_valid_until()
        {
            $settings = CF7DoubleOptIn::getInstance()->getSettings();

            $createtime = $this->get_createtime();
            $dt = new \DateTime();
            $dt->setTimestamp($createtime);

            if ($this->is_confirmed()) {
                $month = (int)$settings['delete'];
                $period = $settings['delete_period'];
            } else {
                $month = (int)$settings['delete_unconfirmed'];
                $period = $settings['delete_unconfirmed_period'];
            }

            $dt->modify('+' . (int)$month . ' ' . $period);
            $dt->modify('+1 day');

            return $dt->format('d.m.Y');
        }

        /**
         * Return a timestamp
         *
         * @param string $view Select how to return the content. raw is the stored db value. use formatted to return a
         *                     formatted string.
         *
         * @return string
         */
        public function get_createtime($view = "raw")
        {
            if ($view != "raw") {
                if (!is_numeric($this->createtime)) {
                    $date = date('d.m.Y', strtotime($this->createtime));
                    $time = date('H:i:s', strtotime($this->createtime));
                } else {
                    $date = date('d.m.Y', $this->createtime);
                    $time = date('H:i:s', $this->createtime);
                }

                return $date . ' ' . __('/', 'double-opt-in') . ' ' . $time;
            } else {
                return $this->createtime;
            }
        }

        /**
         * @param string $timestamp
         */
        public function set_updatetime($timestamp)
        {
            $this->updatetime = $timestamp;
        }

        /**
         * Return a timestamp
         *
         * @param string $view Select how to return the content. raw is the stored db value. use formatted to return a
         *                     formatted string.
         *
         * @return string
         */
        public function get_updatetime($view = 'raw')
        {
            if ($view != "raw") {
                if (!is_numeric($this->updatetime)) {
                    $date = date('d.m.Y', strtotime($this->updatetime));
                    $time = date('H:i:s', strtotime($this->updatetime));
                } else {
                    $date = date('d.m.Y', $this->updatetime);
                    $time = date('H:i:s', $this->updatetime);
                }

                return $date . ' ' . __('/', 'double-opt-in') . ' ' . $time;
            } else {
                return $this->updatetime;
            }
        }

        /**
         * @param string $timestamp
         */
        public function set_optouttime($timestamp)
        {
            $this->optouttime = $timestamp;
        }

        /**
         * Return a timestamp
         *
         * @param string $view Select how to return the content. raw is the stored db value. use formatted to return a
         *                     formatted string.
         *
         * @return string
         */
        public function get_optouttime($view = 'raw')
        {
            if ($view != "raw") {
                if (!is_numeric($this->optouttime)) {
                    $date = date('d.m.Y', strtotime($this->optouttime));
                    $time = date('H:i:s', strtotime($this->optouttime));
                } else {
                    $date = date('d.m.Y', $this->optouttime);
                    $time = date('H:i:s', $this->optouttime);
                }

                return $date . ' ' . __('/', 'f12-cf7-doubleoptin') . ' ' . $time;
            } else {
                return $this->optouttime;
            }
        }

        /**
         * @param string $ip_addr
         */
        public function set_ipaddr_optout($ip_addr)
        {
            $this->ipaddr_optout = $ip_addr;
        }

        /**
         * @return string
         */
        public function get_ipaddr_optout()
        {
            return $this->ipaddr_optout;
        }

        /**
         * @param string $ip_addr
         */
        public function set_ipaddr_register($ip_addr)
        {
            $this->ipaddr_register = $ip_addr;
        }

        /**
         * @return string
         */
        public function get_ipaddr_register()
        {
            return $this->ipaddr_register;
        }

        /**
         * @param string $ip_addr
         */
        public function set_ipaddr_confirmation($ip_addr)
        {
            $this->ipaddr_confirmation = $ip_addr;
        }

        /**
         * @return string
         */
        public function get_ipaddr_confirmation()
        {
            return $this->ipaddr_confirmation;
        }

        /**
         * Return a serialized string
         *
         * @return mixed
         */
        public function get_content()
        {
            return $this->content;
        }

        /**
         * @param string - a serialized string
         */
        public function set_content($content)
        {
            $this->content = $content;
        }

        /**
         * @param string - a serialized string
         */
        public function set_files($files)
        {
            $this->files = $files;
        }

        /**
         * @return string use maybe_unserialize to deserialize the string
         */
        public function get_files()
        {
            return $this->files;
        }

        /**
         * Return the assigned Category ID
         *
         * @return int
         */
        public function get_category()
        {
            if (!is_numeric($this->category)) {
                return 0;
            }

            return $this->category;
        }

        /**
         * Assign a ID to the category
         *
         * @param int $id
         *
         * @return void
         */
        public function set_category($id)
        {
            $this->category = $id;
        }

        /**
         * Check if the given Opt In was created by Contact Form 7
         *
         * @return bool
         */
        public function isTypeCF7()
        {
            $form_id = $this->get_cf_form_id();
            $form = get_post($form_id);

            if ('wpcf7_contact_form' === $form->post_type) {
                return true;
            }

            return false;
        }

        /**
         * Check if the given Opt In was created by Elementor
         *
         * @return bool
         */
        public function isTypeElementor()
        {
            if (!class_exists('ElementorPro\Plugin')) {
                return false;
            }

            $page_id = $this->get_cf_form_id();

            $post = get_post($page_id);

            // skip if post not existing anymore
            if (null === $post) {
                return false;
            }

            // skip if page is not build with elementor
            if (!\Elementor\Plugin::$instance->documents->get($page_id)->is_built_with_elementor()) {
                return false;
            }

            // Load the page's data
            $document = \Elementor\Plugin::$instance->documents->get($page_id);

            // Skip if document not exist
            if (!$document) {
                return false;
            }

            // Loop through the elements
            $elements_data = $document->get_elements_data();

            $content = maybe_unserialize($this->get_content());

            // skip if form id not available
            if (!isset($content['form_id'])) {
                return false;
            }

            $form_id = $content['form_id'];

            // Find the Form Element
            foreach ($elements_data as $element) {
                if (isset($element['elements'])) {
                    foreach ($element['elements'] as $el) {
                        if ($form_id !== $el['id'] || 'form' !== $el['widgetType']) {
                            continue;
                        }

                        return true;
                    }
                    return true;
                }

                // Check if this is the form widget
                if (isset($element['widgetType']) && isset($element['id']) && $element['widgetType'] === 'form' && $element['id'] === $form_id) {
                    // Return the form settings
                    return true;
                }
            }
            return false;
        }

        /**
         * Check if the given Opt In was created by Avada
         *
         * @return bool|void
         */
        public function isTypeAvada()
        {
            $form_id = $this->get_cf_form_id();
            $form = get_post($form_id);

            if ('fusion_form' === $form->post_type) {
                return true;
            }

            return false;
        }

        /**
         * Check if the Opt In was created by 'cf7' or 'avada'.
         *
         * @param string $type use either 'cf7' or 'avada'
         *
         * @return bool
         */
        public function isType($type)
        {
            if ($type == 'cf7') {
                return $this->isTypeCF7();
            } elseif ($type == 'elementor') {
                return $this->isTypeElementor();
            } else {
                return $this->isTypeAvada();
            }
        }

        /**
         * Load an OptIn by the given hash code. Returns either an Object of Type OptIn or null if not found.
         *
         * @param string $hash
         *
         * @return OptIn|null
         */
        public static function get_by_hash(string $hash)
        {
            global $wpdb;
            $table = $wpdb->prefix . 'f12_cf7_doubleoptin';

            if (null == $wpdb) {
                return null;
            }

            $row = $wpdb->get_row('SELECT * FROM ' . $table . ' WHERE hash="' . $hash . '"', ARRAY_A);

            if (null == $row) {
                return null;
            }

            return new OptIn($row);
        }

        /**
         * Get number of optins for the given post.
         *
         * @param $post_id
         *
         * @return int
         */
        public static function get_count($post_id)
        {
            global $wpdb;

            if (!$wpdb) {
                return 0;
            }

            $tableName = $wpdb->prefix . 'f12_cf7_doubleoptin';
            $result = $wpdb->get_results($wpdb->prepare('SELECT count(*) AS counter FROM ' . $tableName . ' WHERE cf_form_id=%d ', $post_id));

            if (is_array($result)) {
                return $result[0]->counter;
            }

            return 0;
        }

        /**
         * Return a list of opt ins stored in the database.
         *
         * @param int   $category_id
         *
         * @param array $atts          array (
         *                             'perPage' => 10, // Posts per page
         *                             'page' => 0, // Current page
         *                             'order' => DESC, // Order (ASC/DESC)
         *                             );
         *
         * @param null  $numberOfPages - Stores the number of pages found if limited
         *
         * @return array
         */
        public static function get_list_by_category_id($category_id, $atts = [], &$numberOfPages = null)
        {
            global $wpdb;

            $attr = [
                'perPage' => 10,
                'page' => 1,
                'order' => 'DESC',
                'keyword' => ''
            ];

            /**
             * Parameter
             */
            foreach ($atts as $key => $value) {
                if (isset($attr[$key])) {
                    $attr[$key] = $atts[$key];
                }
            }

            /*
             * Update keyword
             */
            $where = '';
            if (!empty($attr['keyword'])) {
                $where = ' AND content LIKE "%%' . $attr['keyword'] . '%%"';
            }


            $tableName = $wpdb->prefix . 'f12_cf7_doubleoptin';
            $pageNum = (int)$attr['page'] - 1;

            if ($numberOfPages !== null) {
                $result = $wpdb->get_results($wpdb->prepare('SELECT count(*) AS counter FROM ' . $tableName . ' WHERE category=%d ' . $where, $category_id));

                $itemCounter = 0;

                if (is_array($result)) {
                    $itemCounter = $result[0]->counter;
                }

                $numberOfPages = 0;

                if ($itemCounter != 0) {
                    $numberOfPages = ceil($itemCounter / (int)$attr['perPage']);
                }
            }

            $list = [];

            if ($numberOfPages == 0) {
                return $list;
            }

            $offset = $pageNum * (int)$attr['perPage'];

            $rows = $wpdb->get_results($wpdb->prepare('SELECT * FROM ' . $tableName . ' WHERE category=%d ' . $where . ' ORDER BY ID ' . $attr['order'] . ' LIMIT ' . $attr['perPage'] . ' OFFSET %d', $category_id, $offset), ARRAY_A);

            foreach ($rows as $row) {
                $list[] = new OptIn($row);
            }

            return $list;
        }

        /**
         * Return a list of opt ins stored in the database.
         *
         * @param string $email
         *
         * @return array
         */
        public static function get_list_by_email($email)
        {
            global $wpdb;

            $tableName = $wpdb->prefix . 'f12_cf7_doubleoptin';

            $list = [];

            $rows = $wpdb->get_results($wpdb->prepare('SELECT * FROM ' . $tableName . ' WHERE email=%s', $email), ARRAY_A);

            foreach ($rows as $row) {
                $list[] = new OptIn($row);
            }

            return $list;
        }

	    /**
	     * Return a list of opt ins stored in the database.
	     *
	     * @param string $email
	     *
	     * @return array
	     */
	    public static function get_list_by_email_confirmed($email)
	    {
		    global $wpdb;

		    $tableName = $wpdb->prefix . 'f12_cf7_doubleoptin';

		    $list = [];

		    $rows = $wpdb->get_results($wpdb->prepare('SELECT * FROM ' . $tableName . ' WHERE email=%s AND doubleoptin=1', $email), ARRAY_A);

		    foreach ($rows as $row) {
			    $list[] = new OptIn($row);
		    }

		    return $list;
	    }

	    /**
	     * Return a list of opt ins stored in the database.
	     *
	     * @param string $email
	     *
	     * @return array
	     */
	    public static function get_list_by_email_unconfirmed($email)
	    {
		    global $wpdb;

		    $tableName = $wpdb->prefix . 'f12_cf7_doubleoptin';

		    $list = [];

		    $rows = $wpdb->get_results($wpdb->prepare('SELECT * FROM ' . $tableName . ' WHERE email=%s AND doubleoptin=0', $email), ARRAY_A);

		    foreach ($rows as $row) {
			    $list[] = new OptIn($row);
		    }

		    return $list;
	    }

        /**
         * Return a list of opt ins stored in the database.
         *
         * @param      $atts          array (
         *                            'perPage' => 10, // Posts per page
         *                            'page' => 0, // Current page
         *                            'order' => DESC, // Order (ASC/DESC)
         *                            );
         *
         * @param null $numberOfPages - Stores the number of pages found if limited
         *
         * @return array
         */
        public static function get_list($atts = [], &$numberOfPages = null)
        {
            global $wpdb;

            $attr = [
                'perPage' => 10,
                'page' => 1,
                'order' => 'DESC',
                'keyword' => '',
                'cf_form_id' => '',
            ];

            /*
             * Update atts
             */
            foreach ($atts as $key => $value) {
                if (isset($attr[$key])) {
                    $attr[$key] = $atts[$key];
                }
            }

            /*
             * Update WHERE for Query
             */
            $where = '';
            $where_condition = [];

            /*
             * Add Keyword
             */
            if (!empty($attr['keyword'])) {
                $where_condition[] = 'content LIKE "%%' . $attr['keyword'] . '%%"';
            }

            /*
             * Add Form ID
             */
            if (!empty($attr['cf_form_id'])) {
                $where_condition[] = 'cf_form_id="' . $attr['cf_form_id'] . '"';
            }

            /*
             * build where string
             */
            if (!empty($where_condition)) {
                $where = ' WHERE ' . implode(' AND ', $where_condition);
            }

            /*
             * set table
             */
            $tableName = $wpdb->prefix . 'f12_cf7_doubleoptin';
            $pageNum = (int)$attr['page'] - 1;

            if ($numberOfPages !== null) {
                $result = $wpdb->get_results('SELECT count(*) as counter FROM ' . $tableName . $where);

                $itemCounter = 0;

                if (is_array($result)) {
                    $itemCounter = $result[0]->counter;
                }

                $numberOfPages = 0;

                if ($itemCounter != 0) {
                    $numberOfPages = ceil($itemCounter / (int)$attr['perPage']);
                }
            }

            $list = [];

            if ($numberOfPages == 0) {
                return $list;
            }

            $offset = $pageNum * (int)$attr['perPage'];

            $rows = $wpdb->get_results($wpdb->prepare('SELECT * FROM ' . $tableName . $where . ' ORDER BY ID ' . $attr['order'] . ' LIMIT ' . $attr['perPage'] . ' OFFSET % d', $offset), ARRAY_A);

            foreach ($rows as $row) {
                $list[] = new OptIn($row);
            }

            return $list;
        }

        /**
         * This will update all categories from A to B, e.g.: All Opt-Ins with id 1 to category id 2.
         *
         * @param int $from_id The origin Category ID
         * @param int $to_id   The new Category ID
         *
         * @return bool|int|\mysqli_result|resource|null Return the number of rows affected by the query.
         */
        public static function bulk_update_category($from_id, $to_id)
        {
            global $wpdb;
            $table = $wpdb->prefix . 'f12_cf7_doubleoptin';

            return $wpdb->query($wpdb->prepare('UPDATE ' . $table . ' SET category =%d WHERE category =%d', $to_id, $from_id));
        }

        /**
         * Update the category of the given opt in id.
         *
         * @param int $optin_id    The ID of the OptIn
         * @param int $category_id The ID of the Category
         *
         * @return bool|int|\mysqli_result|resource|null
         */
        public static function update_category_by_id($optin_id, $category_id)
        {
            global $wpdb;
            $table = $wpdb->prefix . 'f12_cf7_doubleoptin';

            return $wpdb->query($wpdb->prepare('UPDATE ' . $table . ' SET category =%d WHERE id =%d', $category_id, $optin_id));
        }

        /**
         * Update or create the given Object
         */
        public function save()
        {
            global $wpdb;
            $table = $wpdb->prefix . 'f12_cf7_doubleoptin';

            f12_debug_log('save method called', [
                'table' => $table,
                'hash' => $this->get_hash(),
            ]);

            /**
             * Wenn ein Hash existiert, wird die Zeile aktualisiert
             */
            if (!empty($this->get_hash())) {
                f12_debug_log('Updating existing record', [
                    'hash' => $this->get_hash(),
                    'cf_form_id' => $this->get_cf_form_id(),
                    'doubleoptin' => $this->get_doubleoptin(),
                ]);

                $result = $wpdb->update($table, [
                    'doubleoptin' => $this->get_doubleoptin(),
                    'createtime' => $this->get_createtime(),
                    'updatetime' => $this->get_updatetime(),
                    'ipaddr_confirmation' => $this->get_ipaddr_confirmation(),
                    'ipaddr_register' => $this->get_ipaddr_register(),
                    'cf_form_id' => $this->get_cf_form_id(),
                    'content' => $this->get_content(),
                    'files' => $this->get_files(),
                    'category' => $this->get_category(),
                    'optouttime' => $this->get_optouttime(),
                    'ipaddr_optout' => $this->get_ipaddr_optout(),
                    'mail_optin' => $this->get_mail_optin(),
                ], [
                    'hash' => $this->get_hash()
                ]);

                f12_debug_log('Update operation completed', ['result' => $result]);

                return $result;
            }

            /**
             * Wenn kein Hash existiert, wird ein neuer Datensatz eingefügt
             */
            f12_debug_log('Inserting new record', [
                'cf_form_id' => $this->get_cf_form_id(),
                'email' => $this->get_email(),
                'doubleoptin' => $this->get_doubleoptin(),
            ]);

            $result = $wpdb->insert(
                $table,
                $data = [ // Daten in einer Variablen speichern, um sie anschließend zu loggen
                    'cf_form_id' => $this->get_cf_form_id(),
                    'doubleoptin' => $this->get_doubleoptin(),
                    'createtime' => $this->get_createtime(),
                    'updatetime' => $this->get_updatetime(),
                    'ipaddr_confirmation' => $this->get_ipaddr_confirmation(),
                    'ipaddr_register' => $this->get_ipaddr_register(),
                    'content' => $this->get_content(),
                    'files' => $this->get_files(),
                    'category' => $this->get_category(),
                    'form' => $this->get_form(true),
                    'mail_optin' => $this->get_mail_optin(),
                    'email' => $this->get_email(),
                ]
            );

            $formValue = $this->get_form();
            f12_debug_log('Form Data Information', [
                'form_length' => strlen($formValue), // Länge des Inhalts
                'form_data_sample' => substr($formValue, 0, 500), // Nur die ersten 500 Zeichen loggen
            ]);

            $log_data = $data;
            $log_data['form'] = substr($formValue, 0, 200);

            f12_debug_log('Preparing to insert new record', [
                'table' => $table,
                'data' => $log_data
            ]);

            if (false === $result) {
                f12_debug_log('Insert operation failed', [
                    'mysql_error' => $wpdb->last_error
                ]);

                $this->validateEncoding($formValue);
                $this->validateMaxAllowedPacket($formValue);
                $this->validateFormField();

                return false;
            }

            f12_debug_log('Insert operation completed', [
                'result' => $result,
                'insert_id' => $wpdb->insert_id,
            ]);

            if ($result > 0) {
                $this->id = $wpdb->insert_id;
                f12_debug_log('New record inserted, ID assigned', ['id' => $this->id]);

                $hashResult = $this->createHash();
                f12_debug_log('Hash created for new record', ['hashResult' => $hashResult]);

                return $hashResult;
            }

            f12_debug_log('Failed to insert new record');
            return false;
        }

        private function validateFormField()
        {
            global $wpdb;
            // SQL-Anfrage, um die Felddefinitionen abzurufen
            $table = $wpdb->prefix . 'f12_cf7_doubleoptin';

            $columns = $wpdb->get_results("SHOW FIELDS FROM $table");

            // Überprüfung der Feldtypen
            foreach ($columns as $column) {
                f12_debug_log('Field Info', [
                    'Field' => $column->Field,
                    'Type' => $column->Type,
                    'Null' => $column->Null,
                    'Default' => $column->Default
                ]);
            }
        }

        private function validateEncoding(string $data)
        {
            if (!mb_check_encoding($data, 'UTF-8')) {
                f12_debug_log('Encoding Issue', ['form_data' => substr($data, 0, 500)]);
                return false;
            }
            f12_debug_log('Encoding OK', []);
            return true;
        }

        private function validateMaxAllowedPacket(string $data)
        {
            $maxAllowedPacketMB = $this->getMaxAllowedPacket();

            $dataSize = strlen($data); // Size in bytes
            $dataSizeMB = $dataSize / (1024 * 1024); // Convert bytes to MB

            f12_debug_log('Data Size Check', [
                'data_size_bytes' => $dataSize,
                'data_size_mb' => $dataSizeMB,
            ]);

            if ($dataSize > $maxAllowedPacketMB) {
                f12_debug_log('max_allowed_packet Too Small', [
                    'data_size_mb' => $dataSizeMB,
                    'max_allowed_packet_mb' => $maxAllowedPacketMB
                ]);
                return false;
            } else {
                f12_debug_log('max_allowed_packet OK', []);
                return true;
            }
        }

        private function getMaxAllowedPacket()
        {
            global $wpdb;

            $result = $wpdb->get_row("SHOW VARIABLES LIKE 'max_allowed_packet'", OBJECT);

            /**
             * Prüfen, ob die Variable erfolgreich abgerufen wurde
             * Die zweite Spalte der Abfrage enthält den Wert (Value)
             */
            // Abfrage, um den aktuellen Wert von max_allowed_packet zu erhalten
            if ($result) {
                $maxAllowedPacket = $result->Value; // Wert aus der zweiten Spalte
                f12_debug_log('MySQL max_allowed_packet Value', [
                    'max_allowed_packet_bytes' => $maxAllowedPacket,
                    'max_allowed_packet_mb' => $maxAllowedPacket / (1024 * 1024) // In MB
                ]);
            } else {
                // Falls das Ergebnis leer ist (z. B. Fehler oder keine Berechtigung)
                f12_debug_log('Error fetching max_allowed_packet', [
                    'error' => $wpdb->last_error
                ]);
                return 0;
            }
            return $maxAllowedPacket;
        }

        /**
         * Creates a unique hash code and adds it to the current OptIn Object
         */
        private function createHash()
        {
            global $wpdb;

            if (null == $wpdb) {
                return false;
            }

            $table = $wpdb->prefix . 'f12_cf7_doubleoptin';

            // add the hash to the element

            $this->hash = base64_encode($this->get_createtime() . $this->get_id() . $this->get_cf_form_id());

            return $wpdb->update($table, [
                'hash' => $this->hash,
            ], [
                'id' => $this->get_id()
            ]);
        }

        private function is_base64_encoded($string)
        {
            // Prüfen, ob der String Base64-kompatible Zeichen enthält
            if (preg_match('/^[A-Za-z0-9+\/=]*$/', $string)) {
                // Prüfen, ob die Länge des Strings ein Vielfaches von 4 ist
                return strlen($string) % 4 === 0;
            }
            return false;
        }

        /**
         * Return the Form
         *
         * @return string
         */
        public function get_form($encoded = false)
        {
            if (!$encoded) {
                if ($this->is_base64_encoded($this->form)) {
                    return base64_decode($this->form);
                } else {
                    return $this->form;
                }
            } else {
                if ($this->is_base64_encoded($this->form)) {
                    return $this->form;
                } else {
                    return base64_encode($this->form);
                }
            }
        }

        /**
         * Returns the Name of the form if available
         *
         * @return string The name of the Form
         * @since 2.4.0
         */
        public function get_form_name()
        {
            $post = get_post($this->get_cf_form_id());

            if (null === $post) {
                return __('Deleted', 'double-opt-in');
            }

            return $post->post_title;
        }

        /**
         * Returns the URL to the form depending on the Form Type.
         *
         * @return string The Backend URL to the Form, empty if the post is not found.
         * @since 2.4.0
         *
         */
        public function get_form_link()
        {
            if ($this->isTypeElementor()) {
                return get_edit_post_link($this->get_cf_form_id());
            } elseif ($this->isTypeAvada()) {
                return get_edit_post_link($this->get_cf_form_id());
            } elseif ($this->isTypeCF7()) {
                $post = get_post($this->get_cf_form_id());

                if (!$post) {
                    return '';
                }

                return admin_url('admin.php?page=wpcf7&post=' . esc_attr($this->get_cf_form_id()) . '&action=edit');
            }

            return '';
        }


        /**
         * Retrieves the form settings.
         *
         * If the form is created using Elementor, retrieves the settings using the `get_settings_by_elementor()`
         * method.
         *
         * @return array The form settings.
         * @since 2.4.0
         *
         */
        public function get_form_settings()
        {
            if ($this->isTypeElementor()) {
                return $this->get_settings_by_elementor();
            } else if ($this->isTypeCF7()) {
                return $this->get_settings_by_cf7();
            } else if ($this->isTypeAvada()) {
                return $this->get_settings_by_avada();
            }

            return [];
        }

        /**
         * Retrieves the settings of a specific element using avada.
         *
         * @return array
         * @since 2.4.0
         *
         * @see   OptIn::maybe_normalize_settings() for details
         */
        private function get_settings_by_avada()
        {

            $form_id = $this->get_cf_form_id();

            $settings = CF7DoubleOptIn::getInstance()->getParameter($form_id);

            return $this->maybe_normalize_settings($settings);
        }

        /**
         * Retrieves the settings of a specific element using cf7.
         *
         * @return array
         * @since 2.4.0
         *
         * @see   OptIn::maybe_normalize_settings() for details
         */
        private function get_settings_by_cf7()
        {
            $form_id = $this->get_cf_form_id();

            $settings = get_post_meta($form_id, 'f12-cf7-doubleoptin', true);

            if (null == $settings || !is_array($settings)) {
                return [];
            }

            $settings = $this->maybe_normalize_settings($settings);

            return $settings;
        }

        /**
         * Retrieves the settings of a specific element using Elementor.
         *
         * @return array
         * @since 2.4.0
         *
         * @see   OptIn::maybe_normalize_settings() for details
         */
        private function get_settings_by_elementor()
        {
            $page_id = $this->get_cf_form_id();

            $post = get_post($page_id);

            // skip if post not existing anymore
            if (null === $post) {
                return [];
            }

            // skip if page is not build with elementor
            if (!\Elementor\Plugin::$instance->documents->get($page_id)->is_built_with_elementor()) {
                return [];
            }

            // Load the page's data
            $document = \Elementor\Plugin::$instance->documents->get($page_id);

            // Skip if document not exist
            if (!$document) {
                return [];
            }

            // Loop through the elements
            $elements_data = $document->get_elements_data();

            $content = maybe_unserialize($this->get_content());

            // skip if form id not available
            if (!isset($content['form_id'])) {
                return [];
            }

            $form_id = $content['form_id'];

            $form_element = null;

            // Find the Form Element
            foreach ($elements_data as $element) {
                if (isset($element['elements'])) {
                    foreach ($element['elements'] as $el) {
                        if ($form_id !== $el['id'] || 'form' !== $el['widgetType']) {
                            continue;
                        }

                        $form_element = $el;
                    }
                }

                // Check if this is the form widget
                if (isset($element['widgetType']) && isset($element['id']) && $element['widgetType'] === 'form' && $element['id'] === $form_id) {
                    // Return the form settings
                    $form_element = $element;
                }
            }

            // skip if form element not found on the page anymore
            if ($form_element == null || !isset($form_element['settings'])) {
                return [];
            }

            // Normalize Settings
            return $this->maybe_normalize_settings($form_element['settings']);
        }

        /**
         * Normalizes the settings by converting the value of the settings
         *
         * @param array      $settings The settings array to be normalized.
         * @formatter:off
         *
		 * @return array {
		 * The settings of the element if found or an empty array if nothing was found.
		 *      @type string $doi_email_to
		 *      @type string $doi_email_subject
		 *      @type string $doi_email_content
		 *      @type string $doi_email_from
		 *      @type string $doi_email_from_name
		 *      @type string $doi_email_template
		 *      @type string $doi_email_body
		 * }
		 *
		 * @formatter:on
         */
        private function maybe_normalize_settings($settings)
        {
            if (isset($settings['sender'])) {
                $settings['doi_email_from'] = $settings['sender'];
            }

            if (isset($settings['sender_name'])) {
                $settings['doi_email_from_name'] = $settings['sender_name'];
            }

            if (isset($settings['subject'])) {
                $settings['doi_email_subject'] = $settings['subject'];
            }

            if (isset($settings['recipient'])) {
                $settings['doi_email_to'] = $settings['recipient'];
            }

            if (isset($settings['template'])) {
                $settings['doi_email_template'] = $settings['template'];
            }

            if (isset($settings['body'])) {
                $settings['doi_email_body'] = $settings['body'];
            }

            /**
             * Normalize the settings between the different form plugins
             *
             * @formatter:off
			 *
			 * @param array {
			 *       @type string $doi_email_to
			 *       @type string $doi_email_subject
			 *       @type string $doi_email_content
			 *       @type string $doi_email_from
			 *       @type string $doi_email_from_name
			 *       @type string $doi_email_template
			 *       @type string $doi_email_body
			 * }
			 *
			 * @formatter:on
             *
             * @since    3.0.0
             */
            return apply_filters('f12_cf7_doubleoption_normalize_settings', $settings);
        }

        /**
         * Set the Form as HTML
         *
         * @param string $form
         *
         * @return void
         */
        public function set_form($form)
        {
            $this->form = $form;
        }

        /**
         * Return the Form
         *
         * @return string
         */
        public function get_mail_optin()
        {
            return $this->mail_optin;
        }

        /**
         * Return the E-mail
         *
         * @return string
         */
        public function get_email()
        {
            return $this->email;
        }

        /**
         * Store the email
         *
         * @param string $email
         *
         * @return void
         */
        public function set_email($email)
        {
            $this->email = $email;
        }

        /**
         * Set the Form as HTML
         *
         * @param string $form
         *
         * @return void
         */
        public function set_mail_optin($mail_html)
        {
            $this->mail_optin = $mail_html;
        }

        /**
         * Return the link to the detail view
         *
         * @return string
         */
        public function get_link_ui()
        {
            return admin_url('admin.php?page=f12-cf7-doubleoptin_optin_view&hash=' . $this->get_hash());
        }

        /**
         * Return the link to delete
         *
         * @return string
         */
        public function get_link_delete()
        {
            return admin_url('admin.php?page=f12-cf7-doubleoptin&option=delete&hash=' . $this->get_hash());
        }

        /**
         * Return the link to the export
         *
         * @return string
         */
        public function get_link_export()
        {
            return admin_url('admin.php?page=f12-cf7-doubleoptin&export=csv&id=' . $this->get_id());
        }

        /**
         * Return the link to the export
         *
         * @return string
         */
        public function get_link_export_v2()
        {
            return admin_url('admin.php?page=f12-cf7-doubleoptin&export=csv_v2&id=' . $this->get_id());
        }

        /**
         * Return the link to the optin
         *
         * @return string
         */
        public function get_link_optin($parameter = [])
        {
            if ($this->get_cf_form_id() == 0) {
                return get_home_url() . '?optin=' . $this->get_hash();
            }

            $parameter = array_merge(CF7DoubleOptIn::getInstance()->getParameter($this->get_cf_form_id()), $parameter);

            $page_id = $parameter['page'] ?? -1;

            if ($page_id == -1 || !$page_id || $page_id == 0) {
                $link = get_home_url() . '?optin=' . $this->get_hash();
            } else {

                $link = get_permalink($page_id);

                if (strpos($link, "?") !== false) {
                    $link .= '&optin=' . $this->get_hash();
                } else {
                    $link .= '?optin=' . $this->get_hash();
                }
            }

            return $link;
        }

        /**
         * Return the link to the optout
         *
         * @return string
         */
        public function get_link_optout()
        {
            $settings = CF7DoubleOptIn::getInstance()->getSettings();

            if (!isset($settings['optout_page']) || $settings['optout_page'] == 0) {
                return get_home_url() . '?optout=' . $this->get_hash();
            } else {
                $url = get_permalink($settings['optout_page']);

                if (str_contains($url, '?')) {
                    // If '?' exists in the URL, it means there are already parameters, so append with '&'
                    $url .= '&optout=' . $this->get_hash();
                } else {
                    // Else, there are no parameters yet, so append with '?'
                    $url .= '?optout=' . $this->get_hash();
                }

                return $url;

            }
        }
    }
}