<?php

/**
 * DVC Listing Notifier
 *
 * @package @DVC
 */

namespace DVCNotifier;

require __DIR__ . '/class.Prices.php';
require __DIR__ . '/class.Log.php';

// Add error handling for vendor dependencies
try {
    require 'vendor/autoload.php';
} catch (Error $e) {
    error_log('DVC Notifier: Failed to load vendor dependencies: ' . $e->getMessage());
    if (function_exists('\newrelic_notice_error')) {
        \newrelic_notice_error('DVC Notifier vendor dependency error: ' . $e->getMessage());
    }
}

use SparkPost\SparkPost;
use GuzzleHttp\Client;
use Http\Adapter\Guzzle7\Client as GuzzleAdapter;

/**
 * Class Notifier
 *
 * @package DVCNotifier
 */
class Notifier_V2
{

    /**
     * Array of Resorts
     *
     * @var array
     */
    private $resorts = array();

    /**
     * Price class
     *
     * @var Prices
     */
    private $prices;

    /**
     * Log Class
     *
     * @var Log Log class.
     */
    private $log;

    /**
     * Default settings
     *
     * @var array Default settings
     */
    private $settings = array(
        'tables'        => array(
            'posts_table'    => '',
            'meta_table'     => '',
            'term_table'     => '',
            'term_tax_table' => '',
        ),
        'log'           => array(
            'uri' => '',
        ),
        'sparkpost_key' => '',
        'timeout'       => 10,
        'unsubscribe'   => '',
        'salt'          => '',
        'debug'         => true,
    );

    /**
     * Notifier constructor.
     *
     * @param array $settings Specific settings.
     */
    public function __construct($settings)
    {

        $this->settings = array_merge($this->settings, $settings);
        $this->log      = new Log($settings['log']);
        $this->prices   = new Prices($settings['tables'], $this);
    }

    /**
     * Begin the notification process.
     */
    public function start()
    {
        if (function_exists('\newrelic_name_transaction')) {
            \newrelic_name_transaction('DVCNotifier/NotifierV3/start');
        }
        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('start_memory_usage', memory_get_usage());
            \newrelic_add_custom_parameter('start_memory_peak', memory_get_peak_usage());
            \newrelic_add_custom_parameter('php_version', PHP_VERSION);
            \newrelic_add_custom_parameter('wp_version', get_bloginfo('version'));
        }

        $total_start = microtime(true);
        if (function_exists('\newrelic_start_segment')) {
            $main_segment = \newrelic_start_segment('DVCNotifier/NotifierV3/complete_execution');
        }

        $sub_start = microtime(true);
        if (function_exists('\newrelic_start_segment')) {
            $sub_segment = \newrelic_start_segment('DVCNotifier/NotifierV3/get_subscribers');
        }
        $subscribers = $this->get_subscribers();
        if (isset($sub_segment) && function_exists('\newrelic_end_segment')) {
            \newrelic_end_segment($sub_segment);
        }
        $sub_duration = microtime(true) - $sub_start;
        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('subscribers_query_duration', $sub_duration);
            \newrelic_add_custom_parameter('subscribers_count', count($subscribers));
        }
        if (0 === count($subscribers)) {
            $this->log->write('No subscribers found.');
            $this->log->close();

            return;
        }
        $subscriber_data = array();
        foreach ($subscribers as $subscriber) {
            $subscriber_data[] = $this->process_subscriber_data($subscriber);
        }

        $listing_data = array();

        // Get all listings with detailed monitoring
        $listings_start = microtime(true);
        if (function_exists('\newrelic_start_segment')) {
            $listings_segment = \newrelic_start_segment('DVCNotifier/NotifierV3/get_all_listings');
        }

        $reduced_listings = $this->get_reduced_listings();
        $bonus_listings   = $this->get_bonus_listings();
        $bonus2_listings  = $this->get_bonus2_listings();
        $new_listings     = $this->get_new_listings(array_column($reduced_listings, 'ID'));

        if (isset($listings_segment) && function_exists('\newrelic_end_segment')) {
            \newrelic_end_segment($listings_segment);
        }
        $listings_duration = microtime(true) - $listings_start;

        // Bulk load all listing metadata at once
        $all_listing_ids = array_unique(array_merge(
            array_column($new_listings, 'ID'),
            array_column($reduced_listings, 'ID'),
            array_column($bonus_listings, 'ID'),
            array_column($bonus2_listings, 'ID')
        ));

        $meta_start = microtime(true);
        if (function_exists('\newrelic_start_segment')) {
            $meta_segment = \newrelic_start_segment('DVCNotifier/NotifierV3/bulk_load_metas');
        }
        $all_metas = $this->get_all_listing_metas($all_listing_ids);
        if (isset($meta_segment) && function_exists('\newrelic_end_segment')) {
            \newrelic_end_segment($meta_segment);
        }
        $meta_duration = microtime(true) - $meta_start;

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('listings_query_duration', $listings_duration);
            \newrelic_add_custom_parameter('meta_bulk_load_duration', $meta_duration);
            \newrelic_add_custom_parameter('total_listings_to_process', count($all_listing_ids));
        }

        $this->log->write(sprintf('Found %s new listings', count($new_listings)));
        $this->log->write(sprintf('Found %s reduced listings', count($reduced_listings)));
        $this->log->write(sprintf('Found %s bonus listings', count($bonus_listings)));
        $this->log->write(sprintf('Found %s bonus 2 listings', count($bonus2_listings)));

        // Process all listing types using bulk-loaded meta data
        if (function_exists('\newrelic_start_segment')) {
            $processing_segment = \newrelic_start_segment('DVCNotifier/NotifierV3/process_all_listings');
        }
        $processing_start = microtime(true);

        if (0 === count($new_listings)) {
            $this->log->write('No new listings found.');
        } else {
            foreach ($new_listings as $key => $listing) {
                $listing_meta = isset($all_metas[$listing['ID']]) ? $all_metas[$listing['ID']] : array();
                $listing_data[] = $this->process_listing_data($key, $listing, $listing_meta);
            }
            usort($listing_data, $this->listing_sorter('listing_id'));
            foreach ($listing_data as $key => $listing) {
                $listing_data[$key] = $this->add_background_color_data($key, $listing);
            }
        }

        $reduced_data = array();
        if (0 === count($reduced_listings)) {
            $this->log->write('No reduced listings found.');
        } else {
            foreach ($reduced_listings as $key => $listing) {
                $listing_meta = isset($all_metas[$listing['ID']]) ? $all_metas[$listing['ID']] : array();
                $reduced_data[] = $this->process_listing_data($key, $listing, $listing_meta);
            }
            usort($reduced_data, $this->listing_sorter('listing_id'));
            foreach ($reduced_data as $key => $listing) {
                $reduced_data[$key] = $this->add_background_color_data($key, $listing);
            }
        }

        $bonus_data = array();
        if (0 === count($bonus_listings)) {
            $this->log->write('No bonus listings found.');
        } else {
            foreach ($bonus_listings as $key => $listing) {
                $listing_meta = isset($all_metas[$listing['ID']]) ? $all_metas[$listing['ID']] : array();
                $bonus_data[] = $this->process_listing_data($key, $listing, $listing_meta);
            }
            usort($bonus_data, $this->listing_sorter('listing_id'));
            foreach ($bonus_data as $key => $listing) {
                $bonus_data[$key] = $this->add_background_color_data($key, $listing);
            }
        }

        $bonus2_data = array();
        if (0 === count($bonus2_listings)) {
            $this->log->write('No bonus2 listings found.');
        } else {
            foreach ($bonus2_listings as $key => $listing) {
                $listing_meta = isset($all_metas[$listing['ID']]) ? $all_metas[$listing['ID']] : array();
                $bonus2_data[] = $this->process_listing_data($key, $listing, $listing_meta);
            }
            usort($bonus2_data, $this->listing_sorter('listing_id'));
            foreach ($bonus2_data as $key => $listing) {
                $bonus2_data[$key] = $this->add_background_color_data($key, $listing);
            }
        }

        $processing_duration = microtime(true) - $processing_start;
        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('all_listings_processing_duration', $processing_duration);
        }
        if (isset($processing_segment) && function_exists('\newrelic_end_segment')) {
            \newrelic_end_segment($processing_segment);
        }

        if (empty($new_listings) && empty($reduced_listings) && empty($bonus_listings) && empty($bonus2_listings)) {
            $this->log->write('No listings found.');
            $this->log->close();

            return;
        }

        $posts     = $this->get_posts();
        $post_data = array();
        if (0 === count($posts)) {
            $this->log->write('No blog posts.');
        } else {
            foreach ($posts as $post) {
                $post_data[] = $this->process_post_data($post);
            }
        }

        $email = $this->mail($subscriber_data, $listing_data, $reduced_data, $bonus_data, $bonus2_data, $post_data);
        if ($email) {
            $this->log->write('Email successfully sent.');
            if (! isset($_GET['test'])) {
                $this->prices->sync_prices();
            }
        } else {
            $this->log->write('Email failed to send.');
        }

        $this->log->close();

        // Final timing and memory metrics
        $total_duration = microtime(true) - $total_start;

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('end_memory_usage', memory_get_usage());
            \newrelic_add_custom_parameter('end_memory_peak', memory_get_peak_usage());
            \newrelic_add_custom_parameter('total_execution_duration', $total_duration);
        }

        // Record custom event with summary metrics
        if (function_exists('\newrelic_record_custom_event')) {
            \newrelic_record_custom_event('DVCNotifierComplete', [
                'execution_time' => floatval($total_duration),
                'memory_peak' => intval(memory_get_peak_usage()),
                'subscribers_processed' => intval(count($subscribers ?? [])),
                'new_listings' => intval(count($new_listings ?? [])),
                'reduced_listings' => intval(count($reduced_listings ?? [])),
                'bonus_listings' => intval(count($bonus_listings ?? [])),
                'bonus2_listings' => intval(count($bonus2_listings ?? [])),
                'total_listings' => intval(count($all_listing_ids ?? [])),
                'email_sent' => ($email ? 1 : 0)
            ]);
        }

        if (isset($main_segment) && function_exists('\newrelic_end_segment')) {
            \newrelic_end_segment($main_segment);
        }
    }

    /**
     * Sort listings array by listing_id
     *
     * @param string $key The key on whch to sort.
     *
     * @return int
     */
    private function listing_sorter($key)
    {

        return function ($a, $b) use ($key) {
            return strcasecmp($a[$key], $b[$key]);
        };
    }

    /**
     * Processes a sigle subscrober into array of data for SparkPost recipients data
     *
     * @param array $subscriber Subscriber data.
     *
     * @return array
     */
    private function process_subscriber_data($subscriber)
    {

        return array(
            'email' => $subscriber['email_address'],
            'ulink' => $this->generate_link($subscriber['email_address']),
            'name'  => $subscriber['name'],
        );
    }

    /**
     * Adds a background color key and value for table striping
     *
     * @param int   $key Index key.
     * @param array $listing Listing data array.
     *
     * @return array
     */
    private function add_background_color_data($key, $listing)
    {

        $background_color            = ($key + 1) & 1 ? '#F9F9F9' : '#FFFFFF';
        $listing['background_color'] = $background_color;

        return $listing;
    }

    /**
     * Processes a single listing into the array of data required for SparkPost substitution_data
     *
     * @param int   $key Index key.
     * @param array $listing Listing data.
     * @param array $meta Pre-loaded meta data for this listing.
     *
     * @return array
     */
    private function process_listing_data($key, $listing, $meta = array())
    {
        if (function_exists('\newrelic_start_segment')) {
            $segment = \newrelic_start_segment('DVCNotifier/NotifierV3/process_single_listing');
        }

        $process_start = microtime(true);
        $this->log->write('Processing listing ' . $listing['ID'] . '.');

        if (empty($this->resorts)) {
            $this->resorts = $this->get_resorts();
        }

        // Use pre-loaded meta data if available, otherwise fall back to individual query
        if (empty($meta)) {
            $meta = $this->get_metas($listing['ID']);
        }

        $listing_resort = $this->get_meta_details($this->resorts, $meta, 'listing_resort');
        $listing_year   = substr($this->get_meta($meta, 'listing_year'), 0, 3) . '.';

        $listing_points_contract = $this->get_meta($meta, 'listing_points_contract');
        $listing_points_price    = $this->get_meta($meta, 'listing_points_price');
        $listing_price           = floatval(intval($listing_points_contract) * intval($listing_points_price));
        $listing_price_previous  = $this->get_previous_price($listing['ID'], $listing_price);

        $points_available = 0;

        // OPTIMIZED: Replace ACF get_field() with direct meta access
        $acf_start = microtime(true);
        $points_fields = array();

        // Try to get points data from meta first
        $repeater_key = 'listing_points_available_repeater';
        if (isset($meta[$repeater_key])) {
            $repeater_count = intval($meta[$repeater_key]);
            for ($i = 0; $i < $repeater_count; $i++) {
                $year_key = $repeater_key . '_' . $i . '_listing_points_available_year';
                $points_key = $repeater_key . '_' . $i . '_listing_points_available_points';

                if (isset($meta[$year_key]) && isset($meta[$points_key])) {
                    $points_fields[] = array(
                        'listing_points_available_year' => $meta[$year_key],
                        'listing_points_available_points' => $meta[$points_key]
                    );
                }
            }
        }

        // TEMPORARY: Always use ACF for now to ensure data accuracy
        // TODO: Fix direct meta parsing after testing
        if (function_exists('get_field')) {
            $points_fields = get_field('listing_points_available_repeater', $listing['ID']) ?: array();

            // Log if direct parsing would have failed
            if (empty($points_fields) && !empty($meta[$repeater_key])) {
                $this->log->write("ACF direct parsing would have failed for listing " . $listing['ID']);
            }
        }
        $acf_duration = microtime(true) - $acf_start;

        // This is the "middle" year used in the pulling of points data.
        // The following code will pull the year before and after this setting
        // which will then be used by the email that goes out via SparkPost.
        $year  = 2026;
        $years = array(0, 0, 0);
        foreach ($points_fields as $points_field) {
            if ($points_field['listing_points_available_year'] == $year - 1) {
                $years[0] = $points_field['listing_points_available_points'];
            } elseif ($points_field['listing_points_available_year'] == $year) {
                $points_available = $points_field['listing_points_available_points'];
                $years[1]         = $points_field['listing_points_available_points'];
            } elseif ($points_field['listing_points_available_year'] == $year + 1) {
                $years[2] = $points_field['listing_points_available_points'];
            }
        }
        $points_coming = implode(' | ', $years);

        $process_duration = microtime(true) - $process_start;

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('single_listing_process_duration', $process_duration);
            \newrelic_add_custom_parameter('acf_points_processing_duration', $acf_duration);
            \newrelic_add_custom_parameter('points_fields_found', count($points_fields));
        }

        $data = array(
            'listing_id'               => $listing['post_title'],
            'date'                     => $listing['post_date'],
            'resort'                   => $listing_resort,
            'listing_points_contract'  => $listing_points_contract,
            'listing_year'             => $listing_year,
            'listing_points_price'     => $listing_points_price,
            'listing_price'            => $this->format_price($listing_price),
            'listing_price_previous'   => $this->format_price($listing_price_previous),
            'listing_points_available' => $points_available,
            'listing_points_coming'    => $points_coming,
        );

        if (isset($segment) && function_exists('\newrelic_end_segment')) {
            \newrelic_end_segment($segment);
        }

        return $data;
    }

    /**
     * Processes a single post into the array of data required for SparkPost substitution_data
     *
     * @param \WP_Post $post Post object.
     */
    private function process_post_data($post)
    {

        return array(
            'title' => html_entity_decode(get_the_title($post)),
            'url'   => get_permalink($post),
        );
    }

    /**
     * Formats the listing price
     *
     * @param string|int $price Listing price.
     *
     * @return string
     */
    private function format_price($price)
    {

        return '$' . number_format(floatval($price), 0, '.', ',');
    }

    /**
     * Get the previous listing price
     *
     * @param string $listing_id The listing ID.
     * @param string $current_price The current listing price.
     *
     * @return string
     */
    private function get_previous_price($listing_id, $current_price)
    {

        $cache_key      = 'li_price_' . $listing_id;
        $previous_price = wp_cache_get($cache_key);
        if (! $previous_price) {
            global $wpdb;
            $previous_price = $wpdb->get_var($wpdb->prepare('SELECT price FROM li_prices WHERE id = %d LIMIT 1;', $listing_id));
            if (null === $previous_price || intval($previous_price) <= intval($current_price)) {
                $previous_price = '0';
            }
            wp_cache_set($cache_key, $previous_price);
        }

        return $previous_price;
    }

    /**
     * Send mail.
     *
     * @param array $subscribers Array of subscribers.
     * @param array $new         Array of new listings.
     * @param array $reduced     Array of reduced listings.
     * @param array $bonus       Array of "bonus" listings.
     * @param array $bonus2      Array of second "bonus" listings.
     * @param array $posts       Array of WP_Post objects.
     *
     * @return array|bool
     */
    private function mail($subscribers, $new, $reduced, $bonus, $bonus2, $posts)
    {
        if (function_exists('\newrelic_start_segment')) {
            $segment = \newrelic_start_segment('DVCNotifier/NotifierV3/mail');
        }
        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('mail_memory_usage', memory_get_usage());
            \newrelic_add_custom_parameter('mail_memory_peak', memory_get_peak_usage());
            \newrelic_add_custom_parameter('mail_recipients', count($subscribers));
            \newrelic_add_custom_parameter('mail_new', count($new));
            \newrelic_add_custom_parameter('mail_reduced', count($reduced));
            \newrelic_add_custom_parameter('mail_bonus', count($bonus));
            \newrelic_add_custom_parameter('mail_bonus2', count($bonus2));
        }

        global $cnp_listing_post_type;
        $contract_count = $cnp_listing_post_type->get_listings_count_with_status_as_of_date('sale-pending');

        global $cnp_post_type_trivia;
        $trivia = $cnp_post_type_trivia->get();

        $result = false;

        $transmission = array(
            'campaign_id'       => 'daily-listing-notifier-v5',
            'content'           => array(
                'template_id' => 'daily-listing-notifier-v5',
            ),
            'substitution_data' => array(
                'today'          => strtoupper(date('F j, Y')),
                'weekday'        => date('l'),
                'listing_count'  => intval(count($new) + count($reduced)),
                'contract_count' => intval($contract_count),
            ),
            'recipients'        => array(),
        );

        if (0 !== count($new)) {
            $transmission['substitution_data']['listings'] = $new;
        }

        if (0 !== count($reduced)) {
            $transmission['substitution_data']['listings_reduced'] = $reduced;
        }

        if (0 !== count($bonus)) {
            $transmission['substitution_data']['listings_bonus'] = $bonus;
        }

        if (0 !== count($bonus2)) {
            $transmission['substitution_data']['listings_bonus2'] = $bonus2;
        }

        if (0 !== count($posts)) {
            $transmission['substitution_data']['posts'] = $posts;
        }

        if ($trivia) {
            $transmission['substitution_data']['trivia'] = $trivia;
        }

        foreach ($subscribers as $subscriber) {
            // Sparkpost now required all valid emails.
            if (! filter_var(trim($subscriber['email']), FILTER_VALIDATE_EMAIL)) {
                continue;
            }

            $transmission['recipients'][] = array(
                'address'           => array(
                    'email' => trim($subscriber['email']),
                    'name'  => $subscriber['name'],
                ),
                'substitution_data' => array(
                    'unsubscribe_link' => $subscriber['ulink'],
                    'name'             => $subscriber['name'],
                ),
            );
        }

        try {
            if (isset($_GET['dump'])) {
                echo '<pre>';
                var_dump($transmission);
                echo '</pre>';
            } else {
                $api_start = microtime(true);
                try {
                    // Suppress deprecation warnings from Guzzle
                    $old_error_reporting = error_reporting(E_ALL & ~E_DEPRECATED);

                    $http_adapter = new GuzzleAdapter(new Client());
                    $sparky       = new SparkPost(
                        $http_adapter,
                        array(
                            'key'   => $this->settings['sparkpost_key'],
                            'async' => false,
                        )
                    );
                    $result = $sparky->transmissions->post($transmission);

                    // Restore error reporting
                    error_reporting($old_error_reporting);

                } catch (Exception $sparkpost_error) {
                    error_reporting($old_error_reporting); // Restore on exception too
                    throw $sparkpost_error; // Re-throw to be caught by outer catch
                }

                $api_end = microtime(true);
                if (function_exists('\newrelic_add_custom_parameter')) {
                    \newrelic_add_custom_parameter('sparkpost_api_duration', $api_end - $api_start);
                }
                if (function_exists('\newrelic_record_custom_event')) {
                    $result_scalar = 'unknown';
                    if (is_scalar($result)) {
                        $result_scalar = (string) $result;
                    } elseif (is_array($result) || is_object($result)) {
                        $result_scalar = 'success'; // Simplified status instead of JSON
                    }

                    \newrelic_record_custom_event('DVCNotifierEvent', [
                        'event' => 'sparkpost_api_call',
                        'duration' => floatval($api_end - $api_start),
                        'result_status' => $result_scalar
                    ]);
                }
            }

            $this->log->write(json_encode($result));
        } catch (\Exception $e) {
            if (function_exists('\newrelic_notice_error')) {
                \newrelic_notice_error($e->getMessage());
            }
            $this->log->write($e);
        }

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('mail_end_memory_usage', memory_get_usage());
            \newrelic_add_custom_parameter('mail_end_memory_peak', memory_get_peak_usage());
        }
        return $result;
    }

    /**
     * Get Meta from array.
     *
     * @param array  $metas Meta array.
     * @param string $key Meta key.
     *
     * @return object|bool
     */
    public function get_meta($metas, $key)
    {

        if (isset($metas[$key])) {
            return $metas[$key];
        } else {
            return false;
        }
    }

    /**
     * Get all listing metas for multiple IDs in bulk (OPTIMIZED)
     *
     * @param array $listing_ids Array of listing IDs.
     *
     * @return array
     */
    public function get_all_listing_metas($listing_ids)
    {
        if (empty($listing_ids)) {
            return array();
        }

        if (function_exists('\newrelic_start_segment')) {
            $segment = \newrelic_start_segment('DVCNotifier/NotifierV3/bulk_meta_query');
        }

        global $wpdb;
        $meta = $this->settings['tables']['meta_table'];

        // Sanitize IDs
        $sanitized_ids = array_map('intval', $listing_ids);
        $ids_placeholder = implode(',', $sanitized_ids);

        $query_start = microtime(true);
        $metas = $wpdb->get_results(
            "SELECT post_id, meta_key, meta_value
             FROM $meta
             WHERE post_id IN ($ids_placeholder)
               AND meta_key LIKE 'listing%'",
            ARRAY_A
        );
        $query_duration = microtime(true) - $query_start;

        // Group by post_id for easy access
        $metas_by_post = array();
        foreach ($metas as $meta_row) {
            $metas_by_post[$meta_row['post_id']][$meta_row['meta_key']] = $meta_row['meta_value'];
        }

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('bulk_meta_query_duration', $query_duration);
            \newrelic_add_custom_parameter('bulk_meta_rows_returned', count($metas));
            \newrelic_add_custom_parameter('bulk_meta_posts_processed', count($metas_by_post));
        }

        if (isset($segment) && function_exists('\newrelic_end_segment')) {
            \newrelic_end_segment($segment);
        }

        $this->log->write('Bulk loaded metas for ' . count($listing_ids) . ' listings in ' . round($query_duration, 4) . 's');

        return $metas_by_post;
    }

    /**
     * Get all Metas by id (LEGACY - kept for compatibility).
     *
     * @param int $id Listing id.
     *
     * @return array
     */
    public function get_metas($id)
    {
        if (function_exists('\newrelic_start_segment')) {
            $segment = \newrelic_start_segment('DVCNotifier/NotifierV3/individual_meta_query');
        }

        global $wpdb;

        $meta      = $this->settings['tables']['meta_table'];
        $meta_keys = array();

        $query_start = microtime(true);
        $metas = $wpdb->get_results(
            "SELECT meta_key, meta_value FROM $meta WHERE post_id = '$id'",
            ARRAY_A
        );
        $query_duration = microtime(true) - $query_start;

        foreach ($metas as $meta) {
            if (strpos($meta['meta_key'], 'listing') === 0) {
                $meta_keys[$meta['meta_key']] = $meta['meta_value'];
            }
        }

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('individual_meta_query_duration', $query_duration);
        }

        if (isset($segment) && function_exists('\newrelic_end_segment')) {
            \newrelic_end_segment($segment);
        }

        $this->log->write("Individual metas loaded for pid #$id in " . round($query_duration, 4) . "s");

        return $meta_keys;
    }

    /**
     * Get all subscribers.
     *
     * @return array|null|object
     */
    public function get_subscribers()
    {

        if (isset($_GET['test'])) {
            if (defined('DVC_SUBSCRIBER_TEST_DATA')) {
                return DVC_SUBSCRIBER_TEST_DATA;
            }

            return array(
                array(
                    'ID'            => 0,
                    'name'          => 'WordPress Admin',
                    'email_address' => get_option('admin_email'),
                ),
            );
        }

        global $wpdb;

        $posts = $this->settings['tables']['posts_table'];
        $meta  = $this->settings['tables']['meta_table'];

        return $wpdb->get_results(
            "
			SELECT
				$posts.ID,
				$posts.post_title AS name,
				$meta.meta_value AS email_address
			FROM $posts
			INNER JOIN $meta
				ON $posts.ID = $meta.post_id
			WHERE $posts.post_type = 'subscriber'
				AND $posts.post_status = 'publish'
				AND $meta.meta_key = 'subscriber_email'
		",
            ARRAY_A
        );
    }

    /**
     * Get listings whith price reductions.
     */
    public function get_reduced_listings()
    {
        if (function_exists('\newrelic_start_segment')) {
            $segment = \newrelic_start_segment('DVCNotifier/NotifierV3/get_reduced_listings');
        }
        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('get_reduced_listings_memory_usage', memory_get_usage());
            \newrelic_add_custom_parameter('get_reduced_listings_memory_peak', memory_get_peak_usage());
        }

        global $wpdb;

        $posts = $this->settings['tables']['posts_table'];

        $reduced_listings_ids = $this->prices->get_reduced_listings();
        if (0 === count($reduced_listings_ids)) {
            return array();
        }
        $prepared_reduced_listings_ids = implode(',', $reduced_listings_ids);
        $reduced_listings_query        = "SELECT DISTINCT ID, post_title, post_date, guid
			FROM $posts
			WHERE $posts.post_type = 'listing' AND $posts.post_status = 'publish' AND $posts.ID IN ($prepared_reduced_listings_ids)";

        $results = $wpdb->get_results($reduced_listings_query, ARRAY_A);

        if (false === $results) {
            return array();
        }

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('get_reduced_listings_count', count($results));
        }

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('get_reduced_listings_end_memory_usage', memory_get_usage());
            \newrelic_add_custom_parameter('get_reduced_listings_end_memory_peak', memory_get_peak_usage());
        }

        if (isset($segment) && function_exists('\newrelic_end_segment')) {
            \newrelic_end_segment($segment);
        }
        return $results;
    }

    /**
     * Get listings with bonus status.
     */
    public function get_bonus_listings()
    {
        if (function_exists('\newrelic_start_segment')) {
            $segment = \newrelic_start_segment('DVCNotifier/NotifierV3/get_bonus_listings');
        }
        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('get_bonus_listings_memory_usage', memory_get_usage());
            \newrelic_add_custom_parameter('get_bonus_listings_memory_peak', memory_get_peak_usage());
        }

        global $wpdb;

        $posts = $this->settings['tables']['posts_table'];
        $metas = $this->settings['tables']['meta_table'];

        $bonus_listings_query = "SELECT DISTINCT P.ID, P.post_title, P.post_date, P.guid
                    FROM $posts P
                        JOIN $metas PM ON PM.post_id = P.ID
                    WHERE P.post_type = 'listing'
                        AND P.post_status = 'publish'
                        AND PM.meta_key LIKE 'listing_additional_details_%_listing_status'
                        AND PM.meta_value = 'BONUS! FULL POINTS FOR 2025 WITH NO DUES!'"; // Use LIKE for dynamic index matching
        $results = $wpdb->get_results($bonus_listings_query, ARRAY_A);


        if (false === $results) {
            return array();
        }

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('get_bonus_listings_count', count($results));
        }

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('get_bonus_listings_end_memory_usage', memory_get_usage());
            \newrelic_add_custom_parameter('get_bonus_listings_end_memory_peak', memory_get_peak_usage());
        }

        if (isset($segment) && function_exists('\newrelic_end_segment')) {
            \newrelic_end_segment($segment);
        }
        return $results;
    }

    /**
     * Get listings with bonus status.
     */
    public function get_bonus2_listings()
    {
        if (function_exists('\newrelic_start_segment')) {
            $segment = \newrelic_start_segment('DVCNotifier/NotifierV3/get_bonus2_listings');
        }
        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('get_bonus2_listings_memory_usage', memory_get_usage());
            \newrelic_add_custom_parameter('get_bonus2_listings_memory_peak', memory_get_peak_usage());
        }

        global $wpdb;

        $posts = $this->settings['tables']['posts_table'];
        $metas = $this->settings['tables']['meta_table'];

        $bonus_listings_query = "SELECT DISTINCT P.ID, P.post_title, P.post_date, P.guid
            FROM $posts P
                JOIN $metas PM ON PM.post_id = P.ID
            WHERE P.post_type = 'listing'
                AND P.post_status = 'publish'
                AND PM.meta_key LIKE 'listing_additional_details_%_listing_additional_details_label'
                AND PM.meta_value = 'BONUS! FULL POINTS FOR 2025 WITH NO DUES!'";
        $results = $wpdb->get_results($bonus_listings_query, ARRAY_A);

        if (false === $results) {
            return array();
        }

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('get_bonus2_listings_count', count($results));
        }

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('get_bonus2_listings_end_memory_usage', memory_get_usage());
            \newrelic_add_custom_parameter('get_bonus2_listings_end_memory_peak', memory_get_peak_usage());
        }

        if (isset($segment) && function_exists('\newrelic_end_segment')) {
            \newrelic_end_segment($segment);
        }
        return $results;
    }

    /**
     * Get 3 most recent published blog posts
     *
     * @return \WP_Post
     */
    public function get_posts()
    {

        return wp_get_recent_posts(
            array(
                'numberposts' => 3,
                'post_status' => 'publish',
            ),
            OBJECT
        );
    }

    /**
     * Get new listings. New listing are those posted yesterday regardless of status (except offer-accepted and sale-pending)
     *
     * @param array $exclude Array of listing IDs to exclude from results.
     *
     * @return array|null|object
     */
    public function get_new_listings($exclude = array())
    {
        if (function_exists('\newrelic_start_segment')) {
            $segment = \newrelic_start_segment('DVCNotifier/NotifierV3/get_new_listings');
        }
        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('get_new_listings_memory_usage', memory_get_usage());
            \newrelic_add_custom_parameter('get_new_listings_memory_peak', memory_get_peak_usage());
        }

        global $wpdb;

        $yesterday = date('Y-m-d H:i:s', strtotime(date('Y-m-d', time()) . ' 00:00:00') - 86400);
        $posts     = $this->settings['tables']['posts_table'];
        $meta      = $this->settings['tables']['meta_table'];

        $status_arr            = array();
        $status_offer_accepted = get_term_by('slug', 'offer-accepted', 'statuses');
        $status_sale_pending   = get_term_by('slug', 'sale-pending', 'statuses');

        if (false !== $status_offer_accepted) {
            $status_arr[] = $status_offer_accepted->term_id;
        }

        if (false !== $status_sale_pending) {
            $status_arr[] = $status_sale_pending->term_id;
        }

        $listings_query = '';

        if (! empty($status_arr)) {
            $statuses = implode(',', $status_arr);

            $listings_query .= "SELECT DISTINCT
					ID,
					post_title,
					post_date,
					guid
				FROM $posts
				INNER JOIN $meta
					ON $posts.ID = $meta.post_id
				WHERE $posts.post_type = 'listing'
					AND $posts.post_status = 'publish'
					AND $meta.meta_key = 'listing_status'
					AND $meta.meta_value NOT IN ($statuses)
			";
        } else {
            $listings_query .= "SELECT DISTINCT
					ID,
					post_title,
					post_date,
					guid
				FROM $posts
				WHERE $posts.post_type = 'listing'
				      AND $posts.post_status = 'publish'
          	";
        }

        if (count($exclude) > 0) {
            $exclude_ids     = implode(',', $exclude);
            $listings_query .= " AND (post_date >= '$yesterday' AND ID NOT IN ($exclude_ids))";
        } else {
            $listings_query .= " AND post_date >= '$yesterday'";
        }

        $results = $wpdb->get_results($listings_query, ARRAY_A);

        if (false === $results) {
            return array();
        }

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('get_new_listings_count', count($results));
        }

        if (function_exists('\newrelic_add_custom_parameter')) {
            \newrelic_add_custom_parameter('get_new_listings_end_memory_usage', memory_get_usage());
            \newrelic_add_custom_parameter('get_new_listings_end_memory_peak', memory_get_peak_usage());
        }

        if (isset($segment) && function_exists('\newrelic_end_segment')) {
            \newrelic_end_segment($segment);
        }
        return $results;
    }

    /**
     * Get all resorts.
     *
     * @return array
     */
    public function get_resorts()
    {

        global $wpdb;
        $posts        = $this->settings['tables']['posts_table'];
        $resort_names = array();

        $resorts = $wpdb->get_results(
            "
			SELECT
				ID,
				post_title
			FROM $posts
			WHERE post_type = 'resort'
				AND post_status = 'publish'
		",
            ARRAY_A
        );

        foreach ($resorts as $resort) {
            $resort_names[$resort['ID']] = $resort['post_title'];
        }
        $this->log->write('Resorts loaded.');

        return $resort_names;
    }

    /**
     * Get all statuses.
     *
     * @return array
     */
    public function get_statuses()
    {

        global $wpdb;

        $terms        = $this->settings['tables']['term_table'];
        $tax          = $this->settings['tables']['term_tax_table'];
        $status_names = array();

        $statuses = $wpdb->get_results(
            "
			SELECT
				$terms.term_id,
				$terms.slug,
				$terms.name
			FROM $terms
			INNER JOIN $tax
				ON $terms.term_id = $tax.term_id
			WHERE $tax.taxonomy = 'statuses'
		",
            ARRAY_A
        );

        foreach ($statuses as $status) {
            $status_names[$status['term_id']] = $status['name'];
        }

        $this->log->write('Statuses loaded.');

        return $status_names;
    }

    /**
     * Get Meta details.
     *
     * @param object $details Details.
     * @param array  $metas Metas.
     * @param string $key Meta key.
     *
     * @return string
     */
    public function get_meta_details($details, $metas, $key)
    {

        $value = '';
        if (isset($metas[$key]) && isset($details[$metas[$key]])) {
            $value = $details[$metas[$key]];
        }

        return $value;
    }

    /**
     * Generate unsubscribe link.
     *
     * @param string $email User email.
     *
     * @return string
     */
    public function generate_link($email)
    {

        return '<a href="' . $this->settings['unsubscribe'] . '?email=' . $email . '&key=' . crypt($email, $this->settings['salt']) . '" style="Margin:0;color:#2199E8;font-family:Arial,sans-serif;font-weight:400;line-height:1.3;margin:0;padding:0;text-align:left;text-decoration:none">UNSUBSCRIBE</a>';
    }
}
