<?php

/**
 * Retention Analytics REST Controller
 *
 * Provides summary cards, chart data, and product search endpoints.
 *
 * @package ArraySubs\Features\RetentionAnalytics\REST
 * @since 1.9.0
 */

namespace ArraySubs\Features\RetentionAnalytics\REST;

defined('ABSPATH') || exit;

use ArraySubs\Features\RetentionAnalytics\Migrations\CreateRetentionLogsTable;
use ArraySubs\Supports\BaseRestController;
use WP_REST_Request;
use WP_REST_Response;

/**
 * Analytics controller — summary cards, charts, product search
 */
class AnalyticsController extends BaseRestController
{
    /**
     * Enable auto-loading
     *
     * @var bool
     */
    public static $loadable = true;

    /**
     * Constructor
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Register REST routes
     *
     * @return void
     */
    public function registerRoutes(): void
    {
        \register_rest_route(
            $this->namespace,
            '/retention-analytics/summary',
            [
                'methods'             => 'GET',
                'callback'            => [$this, 'getSummary'],
                'permission_callback' => [$this, 'checkAdminPermission'],
                'args'                => $this->getDateFilterArgs(),
            ]
        );

        \register_rest_route(
            $this->namespace,
            '/retention-analytics/products',
            [
                'methods'             => 'GET',
                'callback'            => [$this, 'searchProducts'],
                'permission_callback' => [$this, 'checkAdminPermission'],
                'args'                => [
                    'search'   => [
                        'type'              => 'string',
                        'sanitize_callback' => 'sanitize_text_field',
                        'default'           => '',
                    ],
                    'page'     => [
                        'type'              => 'integer',
                        'sanitize_callback' => 'absint',
                        'default'           => 1,
                    ],
                    'per_page' => [
                        'type'              => 'integer',
                        'sanitize_callback' => 'absint',
                        'default'           => 20,
                    ],
                ],
            ]
        );
    }

    /**
     * Check admin permission
     *
     * @return bool
     */
    public function checkAdminPermission(): bool
    {
        if (!\is_user_logged_in()) {
            return false;
        }

        return \current_user_can('manage_woocommerce') || \current_user_can('manage_options');
    }

    /**
     * Get summary + chart data
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function getSummary(WP_REST_Request $request): WP_REST_Response
    {
        global $wpdb;

        $start_date  = $this->sanitizeDate($request->get_param('start_date'));
        $end_date    = $this->sanitizeDate($request->get_param('end_date'));
        $product_ids = $this->sanitizeProductIds($request->get_param('product_ids'));

        $table         = CreateRetentionLogsTable::getTableName();
        $product_where = $this->buildProductWhere($product_ids, 'rl');

        // --- Summary cards ---

        $date_start = $start_date . ' 00:00:00';
        $date_end   = $end_date . ' 23:59:59';

        // Total cancellations.
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- table name via esc_sql(), product IDs via absint()
        $total_cancellations = (int) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM ' . \esc_sql($table) . ' rl
                 WHERE rl.event_type = %s
                   AND rl.created_at BETWEEN %s AND %s' . $product_where,
                'cancelled',
                $date_start,
                $date_end
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        // Live subscribers at period start (churn rate denominator).
        $live_statuses      = array('arraysubs-active', 'arraysubs-trial', 'arraysubs-on-hold');
        $status_placeholders = implode(', ', array_fill(0, count($live_statuses), '%s'));
        $product_sub_join   = $this->buildProductSubJoin($product_ids);

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
        $opening_base = (int) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM ' . \esc_sql($wpdb->posts) . ' p '
                    . $product_sub_join
                    . ' WHERE p.post_type = %s
                   AND p.post_date <= %s
                   AND (p.post_status IN (' . $status_placeholders . ')
                        OR (p.post_status = %s
                            AND p.post_modified >= %s))',
                array_merge(
                    array('arraysubs_data', $date_start),
                    $live_statuses,
                    array('arraysubs-cancelled', $date_start)
                )
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        $churn_rate = $opening_base > 0
            ? round(($total_cancellations / $opening_base) * 100, 1)
            : 0;

        // Averages (age + payments).
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
        $averages = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT
                    AVG(rl.subscription_age) as avg_age,
                    AVG(rl.payments_count) as avg_payments
                 FROM ' . \esc_sql($table) . ' rl
                 WHERE rl.event_type = %s
                   AND rl.created_at BETWEEN %s AND %s' . $product_where,
                'cancelled',
                $date_start,
                $date_end
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        // Offer stats.
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
        $offer_stats = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT
                    SUM(CASE WHEN rl.event_type = %s THEN 1 ELSE 0 END) as shown,
                    SUM(CASE WHEN rl.event_type = %s THEN 1 ELSE 0 END) as accepted
                 FROM ' . \esc_sql($table) . ' rl
                 WHERE rl.event_type IN (%s, %s)
                   AND rl.created_at BETWEEN %s AND %s' . $product_where,
                'offer_shown',
                'offer_accepted',
                'offer_shown',
                'offer_accepted',
                $date_start,
                $date_end
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        $shown    = (int) ($offer_stats->shown ?? 0);
        $accepted = (int) ($offer_stats->accepted ?? 0);

        // Retained revenue.
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
        $retained_revenue = (float) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COALESCE(SUM(rl.recurring_amount), 0)
                 FROM ' . \esc_sql($table) . ' rl
                 INNER JOIN ' . \esc_sql($wpdb->posts) . ' p ON rl.subscription_id = p.ID
                 WHERE rl.event_type = %s
                   AND rl.created_at BETWEEN %s AND %s
                   AND p.post_status IN (' . $status_placeholders . ')
                   AND rl.recurring_amount > 0' . $product_where,
                array_merge(
                    array('offer_accepted', $date_start, $date_end),
                    $live_statuses
                )
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        // --- Chart: Reasons breakdown ---

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
        $reason_rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT rl.subscription_id, rl.reason
                 FROM ' . \esc_sql($table) . ' rl
                 WHERE rl.event_type IN (%s, %s)
                   AND rl.created_at BETWEEN %s AND %s' . $product_where,
                'cancelled',
                'scheduled_cancel',
                $date_start,
                $date_end
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        $reason_counts = [];
        foreach ($reason_rows ?: [] as $row) {
            $reason = \sanitize_text_field((string) $row->reason);

            if ($reason === '') {
                $subscription_id = \absint($row->subscription_id);
                $reason = \sanitize_text_field((string) (
                    \get_post_meta($subscription_id, '_cancellation_reason', true)
                    ?: \get_post_meta($subscription_id, '_cancellation_reason_at_offer', true)
                ));
            }

            if ($reason === '') {
                $reason = 'not_provided';
            }

            $reason_counts[$reason] = ($reason_counts[$reason] ?? 0) + 1;
        }

        \arsort($reason_counts);
        $reasons = [];
        foreach ($reason_counts as $reason => $count) {
            $reasons[] = (object) [
                'reason_key' => $reason,
                'count'      => $count,
            ];
        }

        // --- Chart: Offer outcomes ---

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
        $offer_outcomes = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT rl.offer_type, COUNT(*) as count
                 FROM ' . \esc_sql($table) . ' rl
                 WHERE rl.event_type = %s
                   AND rl.created_at BETWEEN %s AND %s' . $product_where . '
                 GROUP BY rl.offer_type
                 ORDER BY count DESC',
                'offer_accepted',
                $date_start,
                $date_end
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        // Declined count (offer_shown + cancelled, but not accepted).
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
        $declined_count = (int) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM ' . \esc_sql($table) . ' rl
                 WHERE rl.event_type = %s
                   AND rl.created_at BETWEEN %s AND %s' . $product_where,
                'offer_declined',
                $date_start,
                $date_end
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        // --- Chart: Trends ---

        $range_days = $this->getDaysBetween($start_date, $end_date);
        $trends     = $this->getTrendData($start_date, $end_date, $range_days, $product_ids);

        $response = [
            'period'  => [
                'start' => $start_date,
                'end'   => $end_date,
            ],
            'cards'   => [
                'total_cancellations'       => $total_cancellations,
                'churn_rate'                => $churn_rate,
                'avg_subscription_age_days' => round((float) ($averages->avg_age ?? 0)),
                'avg_subscription_age'      => round((float) ($averages->avg_age ?? 0)),
                'avg_payments_before_cancel' => round((float) ($averages->avg_payments ?? 0), 1),
                'offers_shown'              => $shown,
                'offers_accepted'           => $accepted,
                'offer_acceptance_rate'     => $shown > 0 ? round(($accepted / $shown) * 100, 1) : 0,
                'offer_success_rate'        => $shown > 0 ? round(($accepted / $shown) * 100, 1) : 0,
                'retained_revenue'          => round($retained_revenue, 2),
            ],
            'reasons' => array_map(
                function ($row) {
                    return [
                        'reason' => $row->reason_key,
                        'count'  => (int) $row->count,
                    ];
                },
                $reasons ?: []
            ),
            'offers'  => array_merge(
                array_map(
                    function ($row) {
                        return [
                            'type'  => $row->offer_type,
                            'count' => (int) $row->count,
                        ];
                    },
                    $offer_outcomes ?: []
                ),
                $declined_count > 0 ? [['type' => 'declined', 'count' => $declined_count]] : []
            ),
            'trends'  => $trends,
        ];

        return arraysubs_rest_response($response, 200);
    }

    /**
     * Search subscription products for the filter dropdown
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function searchProducts(WP_REST_Request $request): WP_REST_Response
    {
        $search   = \sanitize_text_field($request->get_param('search'));
        $page     = max(1, \absint($request->get_param('page')));
        $per_page = min(20, max(1, \absint($request->get_param('per_page'))));

        // Cap at 5 pages.
        if ($page > 5) {
            return arraysubs_rest_response([], 200);
        }

        $offset        = ($page - 1) * $per_page;
        $search_term   = strtolower($search);
        $results       = [];
        $seen_values   = [];
        $seen_parents  = [];
        $query_args    = [
            'post_type'      => ['product', 'product_variation'],
            'post_status'    => 'publish',
            'posts_per_page' => $per_page * 4,
            'offset'         => $offset,
            'orderby'        => 'title',
            'order'          => 'ASC',
            'fields'         => 'ids',
        ];

        if (!empty($search)) {
            $query_args['s'] = $search;
        }

        $query = new \WP_Query($query_args);

        foreach ($query->posts as $post_id) {
            $product = wc_get_product($post_id);
            if (!$product) {
                continue;
            }

            if ($product->is_type('variation')) {
                if (!arraysubs_is_subscription_product($post_id)) {
                    continue;
                }

                $parent_id = $product->get_parent_id();
                $parent    = $parent_id ? wc_get_product($parent_id) : null;

                if ($parent && !isset($seen_parents[$parent_id])) {
                    $subscription_variation_ids = array_values(array_filter(
                        array_map('absint', $parent->get_children()),
                        'arraysubs_is_subscription_product'
                    ));

                    if (!empty($subscription_variation_ids)) {
                        $seen_parents[$parent_id] = true;
                        $parent_option = [
                            'id'        => $parent_id,
                            'value'     => (string) $parent_id,
                            'label'     => $parent->get_name(),
                            'name'      => $parent->get_name(),
                            'type'      => 'variable',
                            'scope_ids' => $subscription_variation_ids,
                        ];

                        $results[] = $parent_option;
                        $seen_values[$parent_option['value']] = true;
                    }
                }

                $variation_name = $product->get_name();
                if (!empty($search_term) && false === strpos(strtolower($variation_name), $search_term)) {
                    continue;
                }

                $variation_option = [
                    'id'        => $post_id,
                    'value'     => (string) $post_id,
                    'label'     => $variation_name,
                    'name'      => $variation_name,
                    'type'      => 'variation',
                    'parent_id' => $parent_id,
                    'scope_ids' => [$post_id],
                ];

                if (!isset($seen_values[$variation_option['value']])) {
                    $results[] = $variation_option;
                    $seen_values[$variation_option['value']] = true;
                }

                continue;
            }

            if ($product->is_type('variable')) {
                $subscription_variation_ids = array_values(array_filter(
                    array_map('absint', $product->get_children()),
                    'arraysubs_is_subscription_product'
                ));

                if (empty($subscription_variation_ids)) {
                    continue;
                }

                $parent_option = [
                    'id'        => $post_id,
                    'value'     => (string) $post_id,
                    'label'     => $product->get_name(),
                    'name'      => $product->get_name(),
                    'type'      => 'variable',
                    'scope_ids' => $subscription_variation_ids,
                ];

                if (!isset($seen_values[$parent_option['value']])) {
                    $results[] = $parent_option;
                    $seen_values[$parent_option['value']] = true;
                    $seen_parents[$post_id] = true;
                }

                foreach ($subscription_variation_ids as $variation_id) {
                    $variation = wc_get_product($variation_id);
                    if (!$variation) {
                        continue;
                    }

                    $variation_name = $variation->get_name();
                    if (!empty($search_term) && false === strpos(strtolower($variation_name), $search_term)) {
                        continue;
                    }

                    $variation_option = [
                        'id'        => $variation_id,
                        'value'     => (string) $variation_id,
                        'label'     => $variation_name,
                        'name'      => $variation_name,
                        'type'      => 'variation',
                        'parent_id' => $post_id,
                        'scope_ids' => [$variation_id],
                    ];

                    if (!isset($seen_values[$variation_option['value']])) {
                        $results[] = $variation_option;
                        $seen_values[$variation_option['value']] = true;
                    }
                }

                continue;
            }

            if (!arraysubs_is_subscription_product($post_id)) {
                continue;
            }

            $simple_option = [
                'id'        => $post_id,
                'value'     => (string) $post_id,
                'label'     => $product->get_name(),
                'name'      => $product->get_name(),
                'type'      => 'simple',
                'scope_ids' => [$post_id],
            ];

            if (!isset($seen_values[$simple_option['value']])) {
                $results[] = $simple_option;
                $seen_values[$simple_option['value']] = true;
            }
        }

        return arraysubs_rest_response(array_slice($results, 0, $per_page), 200);
    }

    /**
     * Get trend data grouped by appropriate interval
     *
     * @param string $start_date Start date Y-m-d.
     * @param string $end_date   End date Y-m-d.
     * @param int    $range_days Number of days in range.
     * @param array  $product_ids Product IDs filter.
     * @return array
     */
    private function getTrendData(string $start_date, string $end_date, int $range_days, array $product_ids): array
    {
        global $wpdb;

        $table         = CreateRetentionLogsTable::getTableName();
        $product_where = $this->buildProductWhere($product_ids, 'rl');
        $date_start    = $start_date . ' 00:00:00';
        $date_end      = $end_date . ' 23:59:59';

        // Determine grouping resolution.
        if ($range_days <= 60) {
            $group_expr = 'DATE(rl.created_at)';
            $select_as  = 'DATE(rl.created_at) as period_key';
        } elseif ($range_days <= 180) {
            $group_expr = "DATE_FORMAT(rl.created_at, '%x-%v')";
            $select_as  = "DATE_FORMAT(rl.created_at, '%x-%v') as period_key";
        } else {
            $group_expr = "DATE_FORMAT(rl.created_at, '%Y-%m')";
            $select_as  = "DATE_FORMAT(rl.created_at, '%Y-%m') as period_key";
        }

        $safe_table = \esc_sql($table);

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
        $log_trends = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT ' . $select_as . ',
                    SUM(CASE WHEN rl.event_type = %s THEN 1 ELSE 0 END) as cancellations,
                    SUM(CASE WHEN rl.event_type = %s THEN 1 ELSE 0 END) as offers_shown,
                    SUM(CASE WHEN rl.event_type = %s THEN 1 ELSE 0 END) as offers_accepted
                 FROM ' . $safe_table . ' rl
                 WHERE rl.created_at BETWEEN %s AND %s' . $product_where . '
                 GROUP BY ' . $group_expr . '
                 ORDER BY period_key ASC',
                'cancelled',
                'offer_shown',
                'offer_accepted',
                $date_start,
                $date_end
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        // New subscriptions (from posts table).
        $product_sub_join = $this->buildProductSubJoin($product_ids);

        if ($range_days <= 60) {
            $ns_group  = 'DATE(p.post_date)';
            $ns_select = 'DATE(p.post_date) as period_key';
        } elseif ($range_days <= 180) {
            $ns_group  = "DATE_FORMAT(p.post_date, '%x-%v')";
            $ns_select = "DATE_FORMAT(p.post_date, '%x-%v') as period_key";
        } else {
            $ns_group  = "DATE_FORMAT(p.post_date, '%Y-%m')";
            $ns_select = "DATE_FORMAT(p.post_date, '%Y-%m') as period_key";
        }

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
        $new_subs = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT ' . $ns_select . ', COUNT(*) as new_subscriptions
                 FROM ' . \esc_sql($wpdb->posts) . ' p '
                    . $product_sub_join
                    . ' WHERE p.post_type = %s
                   AND p.post_date BETWEEN %s AND %s
                 GROUP BY ' . $ns_group . '
                 ORDER BY period_key ASC',
                'arraysubs_data',
                $date_start,
                $date_end
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        // Merge both into keyed map.
        $merged = [];
        foreach ($log_trends as $row) {
            $merged[$row->period_key] = [
                'date'            => $row->period_key,
                'period'          => $row->period_key,
                'cancellations'   => (int) $row->cancellations,
                'offers_shown'    => (int) $row->offers_shown,
                'offers_accepted' => (int) $row->offers_accepted,
                'new_subscriptions' => 0,
            ];
        }

        foreach ($new_subs as $row) {
            if (isset($merged[$row->period_key])) {
                $merged[$row->period_key]['new_subscriptions'] = (int) $row->new_subscriptions;
            } else {
                $merged[$row->period_key] = [
                    'date'            => $row->period_key,
                    'period'          => $row->period_key,
                    'cancellations'   => 0,
                    'offers_shown'    => 0,
                    'offers_accepted' => 0,
                    'new_subscriptions' => (int) $row->new_subscriptions,
                ];
            }
        }

        ksort($merged);

        return array_values($merged);
    }

    /**
     * Build SQL WHERE clause for product filtering
     *
     * @param array  $product_ids Product IDs.
     * @param string $alias       Table alias.
     * @return string
     */
    private function buildProductWhere(array $product_ids, string $alias): string
    {
        if (empty($product_ids)) {
            return '';
        }

        $ids = implode(',', array_map('absint', $product_ids));

        return ' AND ' . \esc_sql($alias) . '.effective_product_id IN (' . $ids . ')';
    }

    /**
     * Build an INNER JOIN clause for filtering subscriptions by product meta
     *
     * @param array $product_ids Product IDs.
     * @return string
     */
    private function buildProductSubJoin(array $product_ids): string
    {
        if (empty($product_ids)) {
            return '';
        }

        global $wpdb;

        $ids        = implode(',', array_map('absint', $product_ids));
        $meta_table = \esc_sql($wpdb->postmeta);

        return 'INNER JOIN ' . $meta_table . ' pm_prod ON p.ID = pm_prod.post_id
                AND (
                    (pm_prod.meta_key = \'_variation_id\' AND pm_prod.meta_value IN (' . $ids . ') AND pm_prod.meta_value != \'\' AND pm_prod.meta_value != \'0\')
                    OR (pm_prod.meta_key = \'_product_id\' AND pm_prod.meta_value IN (' . $ids . ') AND NOT EXISTS (
                        SELECT 1 FROM ' . $meta_table . ' vpm
                        WHERE vpm.post_id = p.ID AND vpm.meta_key = \'_variation_id\'
                          AND vpm.meta_value != \'\' AND vpm.meta_value != \'0\'
                    ))
                )';
    }

    /**
     * Sanitize a Y-m-d date string
     *
     * @param mixed $date Date input.
     * @return string
     */
    private function sanitizeDate($date): string
    {
        $date = \sanitize_text_field((string) $date);

        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
            return $date;
        }

        return '';
    }

    /**
     * Sanitize product_ids parameter into an array of integers
     *
     * @param mixed $ids Input product IDs.
     * @return array
     */
    private function sanitizeProductIds($ids): array
    {
        if (empty($ids)) {
            return [];
        }

        if (is_string($ids)) {
            $ids = explode(',', $ids);
        }

        if (!is_array($ids)) {
            return [];
        }

        return array_filter(array_map('absint', $ids));
    }

    /**
     * Calculate days between two Y-m-d dates
     *
     * @param string $start Start date.
     * @param string $end   End date.
     * @return int
     */
    private function getDaysBetween(string $start, string $end): int
    {
        $start_ts = strtotime($start);
        $end_ts   = strtotime($end);

        if (!$start_ts || !$end_ts) {
            return 30;
        }

        return max(1, (int) (($end_ts - $start_ts) / DAY_IN_SECONDS));
    }

    /**
     * Common date filter args for REST routes
     *
     * @return array
     */
    private function getDateFilterArgs(): array
    {
        return [
            'start_date'  => [
                'type'              => 'string',
                'sanitize_callback' => 'sanitize_text_field',
                'default'           => gmdate('Y-m-d', time() - (30 * DAY_IN_SECONDS)),
            ],
            'end_date'    => [
                'type'              => 'string',
                'sanitize_callback' => 'sanitize_text_field',
                'default'           => gmdate('Y-m-d'),
            ],
            'product_ids' => [
                'type'    => 'string',
                'default' => '',
            ],
        ];
    }
}
