<?php

namespace PWH_DCFH\App\Controllers;

if (!class_exists('PWH_DCFH_DB')) {
    class PWH_DCFH_DB
    {
        private static $_instance;

        /**
         * Returns the singleton instance of the class.
         * @return self
         */
        public static function instance()
        {
            if (self::$_instance === null) {
                self::$_instance = new self();
            }

            return self::$_instance;
        }

        /**
         * Get Setting
         *
         * @param $key
         *
         * @return bool
         */
        public static function is_option_enabled($key)
        {
            $options = get_option('et_divi');

            return isset($options[$key]) && 'on' === $options[$key];
        }

        /**
         * Is the Setting Enabled?
         *
         * @param $key
         * @param string $default
         *
         * @return bool
         */
        public static function is_setting_enabled($key, $default = '')
        {
            return 'on' === et_get_option($key, $default);
        }

        /**
         * Get Setting
         *
         * @param $key
         * @param string $default
         *
         * @return string
         */
        public static function get_setting($key, $default = '')
        {
            return et_get_option($key, $default);
        }

        /**
         * Update Contact Form Details
         *
         * @param $unique_id
         * @param $args
         *
         * @return void
         */
        public static function update_contact_form_details($unique_id, $args)
        {
            $option_key = "_pwh_dcfh_contact_form_settings_$unique_id";
            $old_options = get_option($option_key);
            $options = [
                'unique_id' => $unique_id,
                'title' => $args['title'],
                'page_id' => get_the_ID(),
                'entries_threshold' => $args['threshold'],
            ];
            $options = wp_parse_args($options, $old_options);
            if (!pwh_dcfh_helpers()::is_robot()) {
                $options['views'] = isset($old_options['views']) ? $old_options['views'] + 1 : 1;
                $ip_address = pwh_dcfh_helpers()::get_ip_address();
                $cookie_name = 'pwh_dcfh_uniqueviews_'.md5($ip_address).'_'.$unique_id;
                if (false === self::is_user_ip_exists($unique_id) && !isset($_COOKIE[$cookie_name])) {
                    $options['unique_views'] = isset($old_options['unique_views']) ? $old_options['unique_views'] + 1 : 1;
                }
            }
            update_option($option_key, $options, 'no');
        }

        /**
         * Update Contact Form Query Params
         *
         * @param $unique_id
         * @param $query_params
         *
         * @return void
         */
        public static function update_contact_form_query_params($unique_id, $query_params)
        {
            $query_params = wp_list_pluck($query_params, 'label', 'field_id');
            $option_key = "_pwh_dcfh_contact_form_settings_$unique_id";
            $options = get_option($option_key);
            if ($options !== false) {
                if (!isset($options['query_params'])) {
                    $options['query_params'] = [];
                }
                $new_query_params = wp_parse_args($query_params, $options['query_params']);
                $new_query_params = array_unique($new_query_params);
                $options['query_params'] = $new_query_params;
                update_option($option_key, $options, 'no');
            }
        }

        /**
         * Update Contact Form Entries
         *
         * @param $unique_id
         * @param $entries
         *
         * @return void
         */
        public static function update_contact_form_total_entries($unique_id, $entries)
        {
            $option_key = "_pwh_dcfh_contact_form_settings_$unique_id";
            $options = get_option($option_key);
            if ($options !== false) {
                if (!isset($options['total_entries']) || $options['total_entries'] !== $entries) {
                    $options['total_entries'] = $entries;
                    update_option($option_key, $options, 'no');
                }
            }
        }

        /**
         * Reset Form Views
         *
         * @param $unique_id
         *
         * @return void
         */
        public static function reset_contact_form_views($unique_id)
        {
            $option_key = "_pwh_dcfh_contact_form_settings_$unique_id";
            $options = get_option($option_key);
            $options['views'] = 0;
            $options['unique_views'] = 0;
            update_option($option_key, $options, 'no');
        }

        /**
         * Delete Form Options
         *
         * @param $unique_id
         *
         * @return void
         */
        public static function delete_contact_form_options($unique_id)
        {
            $option_key = "_pwh_dcfh_contact_form_settings_$unique_id";
            delete_option($option_key);
        }

        /**
         * Get Form Title
         *
         * @param $unique_id
         *
         * @return mixed|void
         */
        public static function get_contact_form_title($unique_id)
        {
            $option_key = "_pwh_dcfh_contact_form_settings_$unique_id";
            $options = get_option($option_key);
            if ($options !== false && isset($options['title'])) {
                return $options['title'];
            }

            return $unique_id;
        }

        /**
         * Get Contact Forms
         * @return false|mixed
         */
        public static function get_contact_forms_list()
        {
            $key = 'pwh_dcfh_contact_forms_'.md5(__FUNCTION__);
            $contact_forms = wp_cache_get($key);
            if ($contact_forms === false) {
                $contact_forms = [];  // Initialize as an array to avoid the deprecated behavior
                global $wpdb;
                $results = $wpdb->get_results($wpdb->prepare("SELECT option_value FROM $wpdb->options WHERE option_name LIKE %s", '_pwh_dcfh_contact_form_settings_%')); // db call ok; no-cache ok
                if (!empty($results)) {
                    foreach ($results as $result) {
                        $options = maybe_unserialize($result->option_value);
                        if (!empty($options['unique_id'])) {
                            $contact_form = [
                                'unique_id' => $options['unique_id'],
                                'title' => $options['title'] ?? '',
                                'page_id' => $options['page_id'] ?? 0,
                                'entries_threshold' => $options['entries_threshold'] ?? '-1',
                                'total_entries' => $options['total_entries'] ?? 0,
                                'views' => $options['views'] ?? 0,
                                'unique_views' => $options['unique_views'] ?? 0,
                            ];
                            $contact_forms[$options['unique_id']] = $contact_form;
                        }
                    }
                    wp_cache_set($key, $contact_forms, 'pwh_dcfh_cached_queries', 2 * MINUTE_IN_SECONDS);
                }
            }

            return $contact_forms;

        }

        /**
         * Get Contact Form Total Entries
         *
         * @param $contact_form_id
         *
         * @return int
         */
        public static function get_contact_form_total_entries($contact_form_id)
        {
            global $wpdb;
            $count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) AS total_entries FROM $wpdb->postmeta WHERE meta_key = %s AND meta_value LIKE %s", '_pwh_dcfh_contact_form_id', $wpdb->esc_like($contact_form_id))); //db call ok; no-cache ok
            if ($count === null) {
                return 0;
            }

            return (int)$count;
        }

        /**
         * Get Contact Form Last Post-ID
         *
         * @param $contact_form_id
         *
         * @return string|null
         */
        public static function get_contact_form_last_post_id($contact_form_id)
        {
            global $wpdb;

            return $wpdb->get_var($wpdb->prepare("SELECT DISTINCT post_id FROM $wpdb->postmeta WHERE meta_value LIKE %s ORDER BY meta_id DESC", $contact_form_id)); //db call ok; no-cache ok
        }

        /**
         * Get Form CSV Tags
         * @return false|mixed
         */
        public static function get_contact_form_csv_data()
        {
            $key = 'pwh_dcfh_contact_form_csv_'.md5(__FUNCTION__);
            $cached_data = wp_cache_get($key);
            if (!$cached_data) {
                $cached_data = [];  // Initialize as an array to avoid the deprecated behavior
                $contact_forms = self::get_contact_forms_list();
                if (!empty($contact_forms)) {
                    foreach ($contact_forms as $contact_form) {
                        $contact_form_id = $contact_form['unique_id'];
                        $contact_form_title = $contact_form['title'];
                        $total_rows = $contact_form['total_entries'];
                        $post_obj = get_posts([
                            'numberposts' => 1,
                            'orderby' => 'post_date',
                            'order' => 'DESC',
                            'meta_key' => '_pwh_dcfh_contact_form_id',// phpcs:ignore
                            'meta_value' => $contact_form_id,// phpcs:ignore
                            'post_type' => 'pwh_dcfh',
                            'post_status' => 'any',
                            'suppress_filters' => true,
                        ]);
                        if (isset($post_obj[0]->post_content)) {
                            $post_content = $post_obj[0]->post_content ?? '';
                            if (!empty($post_content) && is_serialized($post_content)) {
                                $form_entries = pwh_dcfh_helpers()::maybe_unserialize($post_content);
                                if (!empty($form_entries) && is_array($form_entries)) {
                                    $output = "<ul>";
                                    foreach ($form_entries as $form_entry) {
                                        $field_id = pwh_dcfh_helpers()::sanitize_key($form_entry['original_id']);
                                        $field_label = $form_entry['field_label'];
                                        $output .= sprintf(
                                            "<li><input type='checkbox' id='%s' name='contact_form_fields[%s]' value='%s' checked>".
                                            "<label for='%s'>%s</label></li>",
                                            esc_attr($field_id),
                                            esc_attr($field_id),
                                            esc_attr($field_label),
                                            esc_attr($field_id),
                                            esc_html($field_label)
                                        );
                                    }
                                    foreach (pwh_dcfh_helpers()::fixed_form_placeholders() as $key => $value) {
                                        $output .= sprintf(
                                            "<li><input type='checkbox' id='%s' name='contact_form_fields[%s]' value='%s' checked><label for='%s'>%s</label></li>",
                                            esc_attr($key),
                                            esc_attr($key),
                                            esc_attr($value),
                                            esc_attr($key),
                                            esc_html($value)
                                        );
                                    }
                                    $output .= "</ul>";
                                    $cached_data[$contact_form_id] = [
                                        'contact_form_id' => $contact_form_id,
                                        'contact_form_title' => $contact_form_title,
                                        'contact_form_fields_html' => $output,
                                        'contact_form_total_entries' => number_format_i18n($total_rows)
                                    ];
                                    wp_cache_set($key, $cached_data, 'pwh_dcfh_cached_queries', 2 * MINUTE_IN_SECONDS);

                                }
                            }
                        }
                    }
                }
            }

            return $cached_data;
        }

        /**
         * Get Form Fields
         * @return false|mixed
         */
        public static function get_contact_form_template_data()
        {
            $key = 'pwh_dcfh_contact_form_template_'.md5(__FUNCTION__);
            $cached_data = wp_cache_get($key);
            if (!$cached_data) {
                $cached_data = [];  // Initialize as an array to avoid the deprecated behavior
                $contact_forms = self::get_contact_forms_list();
                if (!empty($contact_forms)) {
                    foreach ($contact_forms as $contact_form) {
                        $contact_form_id = $contact_form['unique_id'];
                        $contact_form_title = $contact_form['title'];
                        $total_rows = $contact_form['total_entries'];
                        $post_obj = get_posts([
                            'numberposts' => 1,
                            'orderby' => 'post_date',
                            'order' => 'DESC',
                            'meta_key' => '_pwh_dcfh_contact_form_id',// phpcs:ignore
                            'meta_value' => $contact_form_id,// phpcs:ignore
                            'post_type' => 'pwh_dcfh',
                            'post_status' => 'any',
                            'suppress_filters' => true,
                        ]);
                        $post_content = $post_obj[0]->post_content ?? '';
                        if (!empty($post_content) && is_serialized($post_content)) {
                            $form_entries = pwh_dcfh_helpers()::maybe_unserialize($post_content);
                            if (!empty($form_entries) && is_array($form_entries)) {
                                $output = "<ul>";
                                foreach ($form_entries as $form_entry) {
                                    $field_id = pwh_dcfh_helpers()::sanitize_key($form_entry['original_id']);
                                    $output .= sprintf("<li class='keyword'>%%%s%%</li>", $field_id);
                                }
                                foreach (pwh_dcfh_helpers()::fixed_site_placeholders() as $placeholder) {
                                    $output .= sprintf("<li class='keyword'>%%%s%%</li>", $placeholder);
                                }
                                $output .= "</ul>";
                                $cached_data[$contact_form_id] = [
                                    'contact_form_id' => $contact_form_id,
                                    'contact_form_title' => $contact_form_title,
                                    'contact_form_fields_html' => $output,
                                    'contact_form_total_entries' => number_format_i18n($total_rows)
                                ];
                                wp_cache_set($key, $cached_data, 'pwh_dcfh_cached_queries', 2 * MINUTE_IN_SECONDS);
                            }
                        }

                    }
                }
            }

            return $cached_data;
        }

        /**
         * Get Send Email Keywords
         *
         * @param $post_id
         *
         * @return void
         */
        public static function get_contact_form_send_email_data($post_id)
        {
            $fields_string = pwh_dcfh_post_meta_handler()::get_contact_form_fields_meta_value($post_id);
            $output = "<ul>";
            if (!empty($fields_string)) {
                $fields = explode(',', $fields_string);
                foreach ($fields as $field) {
                    $field_id = pwh_dcfh_helpers()::sanitize_key($field);
                    $output .= sprintf("<li class='keyword'>%%%s%%</li>", $field_id);
                }
            }
            foreach (pwh_dcfh_helpers()::fixed_site_placeholders() as $placeholder) {
                $output .= sprintf("<li class='keyword'>%%%s%%</li>", $placeholder);
            }
            $output .= '</ul>';
            echo force_balance_tags($output);  // phpcs:ignore
        }

        /**
         * Global Email Keywords
         *
         * @param $post
         *
         * @return array
         */
        public static function get_send_email_static_keywords_values($post)
        {
            $post_id = $post->ID;
            $page_id = pwh_dcfh_post_meta_handler()::get_page_id_meta_value($post_id);

            return [
                '%%dcfh_site_name%%' => get_bloginfo('name'),
                '%%dcfh_site_url%%' => get_bloginfo('url'),
                '%%dcfh_admin_email%%' => get_bloginfo('admin_email'),
                '%%dcfh_post_title%%' => get_the_title($page_id),
                '%%dcfh_post_url%%' => get_the_permalink($page_id),
                '%%dcfh_date%%' => $post->post_date,
            ];
        }

        /**
         * Get Entry Stats From Database
         * @return false|mixed
         */
        public static function get_contact_forms_dashboard_stats()
        {
            $dashboard_stats = get_transient('pwh_dcfh_dashboard_stats');
            // phpcs:disable
            if (false === $dashboard_stats) {
                global $wpdb;
                $today = date('Y-m-d');
                $yesterday = date('Y-m-d', strtotime('-1 day'));
                $week_ago = date('Y-m-d', strtotime('-1 week'));
                $month_ago = date('Y-m-d', strtotime('-1 month'));
                $query = $wpdb->prepare("
                        SELECT
                            CASE
                                WHEN DATE(wpp.post_date) = %s THEN 'today'
                                WHEN DATE(wpp.post_date) = %s THEN 'yesterday'
                                WHEN DATE(wpp.post_date) > %s THEN 'last_week'
                                WHEN DATE(wpp.post_date) > %s THEN 'last_month'
                                ELSE 'other'
                            END AS title,
                            COUNT(wpp.ID) AS total_posts,
                            wppm.meta_value AS contact_form_id
                        FROM $wpdb->posts AS wpp
                        INNER JOIN $wpdb->postmeta AS wppm
                            ON wppm.post_id = wpp.ID
                        WHERE wpp.post_date BETWEEN %s AND %s
                        AND wpp.post_type = 'pwh_dcfh'
                        AND wppm.meta_key = '_pwh_dcfh_contact_form_id'
                        GROUP BY title, wppm.meta_value
                        ORDER BY wppm.meta_value
                    ", $today, $yesterday, $week_ago, $month_ago, $month_ago, $today);
                $results = $wpdb->get_results($query);
                if (!empty($results)) {
                    foreach ($results as $result) {
                        $dashboard_stats[pwh_dcfh_db()::get_contact_form_title($result->contact_form_id)][$result->title] = $result->total_posts;
                    }
                    set_transient('pwh_dcfh_dashboard_stats', $dashboard_stats, 2 * HOUR_IN_SECONDS);
                }
            }

            // phpcs:enable
            return $dashboard_stats;
        }

        /**
         * Get Entry Dynamic Columns
         * @return array|string[]
         */
        public static function dynamic_entry_columns()
        {
            $columns = [];
            $option = et_get_option('pwh_dcfh_entry_list_columns', 'name');
            if (!empty($option) && is_string($option)) {
                $option = preg_replace('/\s*,\s*/', ',', $option);
                $columns = explode(',', strtolower($option));
                $columns = array_filter($columns, 'strlen');
            }

            return $columns;
        }

        /**
         * Check User IP Exist
         *
         * @param $contact_form_id
         *
         * @return bool
         */
        private static function is_user_ip_exists($contact_form_id)
        {
            global $wpdb;
            $ip_address = pwh_dcfh_helpers()::get_ip_address();
            $result = $wpdb->get_var($wpdb->prepare(" SELECT COUNT(*) AS count FROM $wpdb->postmeta AS pm1 INNER JOIN $wpdb->postmeta AS pm2 ON pm1.post_id = pm2.post_id WHERE (pm1.meta_key = '_pwh_dcfh_contact_form_id' AND pm1.meta_value = %s) AND (pm2.meta_key = '_pwh_dcfh_ip_address' AND pm2.meta_value = %s)", $contact_form_id, $ip_address));// db call ok; no-cache ok

            return (int)$result > 0;
        }

        /**
         * Get Attachment ID By Name
         *
         * @param $name
         *
         * @return string|null
         */
        public static function get_attachment_id_by_name($name)
        {
            global $wpdb;
            $name = pathinfo($name, PATHINFO_FILENAME);

            return $wpdb->get_var($wpdb->prepare("SELECT ID FROM $wpdb->posts WHERE post_name = '%s'", $name));  // phpcs:ignore
        }

        /**
         * Update Entry Status
         *
         * @param $post_id
         * @param string $post_status
         * @param string $post_modified
         * @param string $post_modified_gmt
         *
         * @return void
         */
        public static function set_post_status_and_modified($post_id, $post_status = 'draft', $post_modified = '0000-00-00 00:00:00', $post_modified_gmt = '0000-00-00 00:00:00')
        {
            global $wpdb;
            $wpdb->update($wpdb->posts, ['post_status' => $post_status, 'post_modified' => $post_modified, 'post_modified_gmt' => $post_modified_gmt], ['post_status' => 'pwh_dcfh', 'ID' => $post_id], ['%s', '%s', '%s'], ['%d']); //db call ok; no-cache ok
        }

        /**
         * Check if a submitter's post with a specific email is marked as spam.
         *
         * @param string $email
         *
         * @return bool
         */
        public static function is_spam($email)
        {
            global $wpdb;

            return (bool)$wpdb->get_var($wpdb->prepare("SELECT EXISTS(SELECT 1 FROM $wpdb->posts p INNER JOIN $wpdb->postmeta pm ON p.ID = pm.post_id WHERE pm.meta_key = %s AND pm.meta_value = %s AND p.post_status = 'spam' LIMIT 1)", '_pwh_dcfh_contact_email', $email)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        }

        /**
         * Update the post status to 'spam' for posts associated with a specific email.
         *
         * @param string $email
         * @param string $post_type
         *
         * @return int|false
         */
        public static function update_posts_status_to_spam($email, $post_type = 'pwh_dcfh')
        {
            global $wpdb;

            return $wpdb->query($wpdb->prepare("UPDATE $wpdb->posts SET post_status = %s WHERE ID IN (SELECT post_id FROM $wpdb->postmeta WHERE meta_key = '_pwh_dcfh_contact_email' AND meta_value = %s) AND post_type = %s AND post_status IN (%s, %s)", 'spam', strtolower($email), $post_type, 'private', 'draft')); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        }

        /**
         * Update the post status for posts associated with a specific email if they are marked as spam.
         *
         * @param string $email The email associated with the posts to update.
         * @param string $post_status The new post status to set.
         * @param string $post_type The post type to filter by (default: 'pwh_dcfh').
         *
         * @return int|false The number of rows affected, or false on error.
         */
        public static function update_spam_posts_status($email, $post_status, $post_type = 'pwh_dcfh')
        {
            global $wpdb;

            return $wpdb->query($wpdb->prepare("UPDATE $wpdb->posts AS p JOIN $wpdb->postmeta AS pm ON p.ID = pm.post_id SET p.post_status = %s WHERE pm.meta_key = '_pwh_dcfh_contact_email' AND pm.meta_value = %s AND p.post_type = %s AND p.post_status = 'spam'", $post_status, strtolower($email), $post_type)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        }
    }
}