<?php
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Central analytics and metrics service.
 */
class EEP_Analytics_Service {

    /**
     * @var string
     */
    private $post_type;

    /**
     * @var EEP_Runtime_Log_Service|null
     */
    private $logger;

    /**
     * @var string
     */
    private $analytics_table = 'eep_event_analytics';

    /**
     * @var string
     */
    private $boost_orders_table = 'eep_boost_orders';

    /**
     * @var string
     */
    private $ticket_orders_table = 'eep_ticket_orders';

    /**
     * Constructor.
     *
     * @param string $post_type
     * @param mixed $logger
     */
    public function __construct($post_type = 'nexdirectory_event', $logger = null) {
        $this->post_type = sanitize_key((string) $post_type);
        if ($this->post_type === '') {
            $this->post_type = 'nexdirectory_event';
        }
        $this->logger = is_object($logger) ? $logger : null;
    }

    /**
     * Inserts one analytics row.
     *
     * @param int $event_id
     * @param string $metric
     * @param array<string, mixed> $args
     * @return bool
     */
    public function record_analytics_event($event_id, $metric, array $args = []) {
        global $wpdb;

        $metric = sanitize_key((string) $metric);
        if ($metric === '') {
            return false;
        }

        $table = $this->get_analytics_table_name();
        $this->ensure_analytics_table_exists();
        if (!$this->table_exists($table)) {
            return false;
        }

        $event_id = max(0, absint((int) $event_id));
        $context = sanitize_key((string) ($args['context'] ?? ''));
        if ($context === '') {
            $context = 'single';
        }

        $meta_key = sanitize_key((string) ($args['meta_key'] ?? ''));
        $meta_value = sanitize_text_field((string) ($args['meta_value'] ?? ''));
        $value = isset($args['value']) ? (float) $args['value'] : 1.0;
        if (!is_finite($value) || $value <= 0) {
            $value = 1.0;
        }

        $user_id = isset($args['user_id']) ? absint((int) $args['user_id']) : absint((int) get_current_user_id());
        $owner_id = isset($args['owner_id']) ? absint((int) $args['owner_id']) : 0;
        if ($owner_id <= 0 && $event_id > 0 && get_post_type($event_id) === $this->post_type) {
            $owner_id = absint((int) get_post_field('post_author', $event_id));
        }

        $created_at_raw = sanitize_text_field((string) ($args['created_at'] ?? ''));
        $created_ts = $created_at_raw !== '' ? strtotime($created_at_raw) : false;
        $created_at = $created_ts ? gmdate('Y-m-d H:i:s', (int) $created_ts) : gmdate('Y-m-d H:i:s');

        $inserted = $wpdb->insert(
            $table,
            [
                'event_id' => $event_id,
                'owner_id' => $owner_id,
                'user_id' => $user_id,
                'metric' => $metric,
                'context' => $context,
                'meta_key' => $meta_key,
                'meta_value' => $meta_value,
                'value' => $value,
                'created_at' => $created_at,
            ],
            ['%d', '%d', '%d', '%s', '%s', '%s', '%s', '%f', '%s']
        ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

        return $inserted !== false;
    }

    /**
     * Batch-inserts analytics rows for a list of events.
     *
     * @param array<int, mixed> $event_ids
     * @param string $metric
     * @param array<string, mixed> $args
     * @return int
     */
    public function record_analytics_for_event_ids(array $event_ids, $metric, array $args = []) {
        $ids = array_values(array_unique(array_filter(array_map('absint', $event_ids))));
        if (empty($ids)) {
            return 0;
        }

        $count = 0;
        foreach ($ids as $event_id) {
            if ($this->record_analytics_event((int) $event_id, $metric, $args)) {
                $count++;
            }
        }

        return $count;
    }

    /**
     * Builds owner analytics report payload for dashboard + export.
     *
     * @param int $owner_id
     * @param int $days
     * @return array<string, mixed>
     */
    public function get_owner_analytics_report($owner_id, $days = 30) {
        $owner_id = absint((int) $owner_id);
        if ($owner_id <= 0) {
            return $this->get_empty_owner_report();
        }

        $range = $this->normalize_day_range($days);
        $start = $range['start_gmt'];
        $end = $range['end_gmt'];
        $previous_start = gmdate('Y-m-d H:i:s', strtotime($start . ' UTC') - ($range['days'] * DAY_IN_SECONDS));
        $previous_end = $start;

        $totals_current = $this->get_owner_metric_totals($owner_id, $start, $end);
        $totals_previous = $this->get_owner_metric_totals($owner_id, $previous_start, $previous_end);
        $rows = $this->get_owner_listing_rows($owner_id, $start, $end);
        $trend_rows = $this->get_owner_trend_rows($owner_id, $start, $end, (int) $range['days']);
        $trend_max = $this->compute_trend_max($trend_rows);

        $report = [
            'owner_id' => $owner_id,
            'days' => (int) $range['days'],
            'start_gmt' => $start,
            'end_gmt' => $end,
            'totals' => $totals_current,
            'rows' => $rows,
            'trend' => $trend_rows,
            'trend_meta' => [
                'max_value' => $trend_max,
            ],
            'comparison' => [
                'current' => $totals_current,
                'previous' => $totals_previous,
                'deltas' => $this->build_metric_deltas($totals_current, $totals_previous),
                'previous_start_gmt' => $previous_start,
                'previous_end_gmt' => $previous_end,
            ],
            'boost_impact' => $this->build_boost_impact_snapshot($rows),
        ];

        return $report;
    }

    /**
     * Builds platform analytics report payload for admin metrics.
     *
     * @param int $days
     * @return array<string, mixed>
     */
    public function get_platform_analytics_report($days = 30) {
        $range = $this->normalize_day_range($days);
        $start = $range['start_gmt'];
        $end = $range['end_gmt'];

        $summary = $this->get_platform_summary($start, $end, (int) $range['days']);
        $report = [
            'days' => (int) $range['days'],
            'start_gmt' => $start,
            'end_gmt' => $end,
            'summary' => $summary,
            'growth' => $this->get_growth_rows($start, $end),
            'most_viewed_listings' => $this->get_most_viewed_rows($start, $end),
            'most_searched_categories' => $this->get_most_searched_category_rows($start, $end),
            'most_active_areas' => $this->get_most_active_area_rows($start, $end),
            'top_performing_businesses' => $this->get_top_owner_performance_rows($start, 10),
        ];

        return $report;
    }

    /**
     * Top owners by weighted engagement score.
     *
     * @param string $since_gmt
     * @param int $limit
     * @return array<int, array<string, mixed>>
     */
    public function get_top_owner_performance_rows($since_gmt, $limit = 10) {
        global $wpdb;

        $table = $this->get_analytics_table_name();
        if (!$this->table_exists($table)) {
            return [];
        }

        $since_gmt = sanitize_text_field((string) $since_gmt);
        if ($since_gmt === '') {
            $since_gmt = gmdate('Y-m-d H:i:s', time() - (30 * DAY_IN_SECONDS));
        }
        $limit = max(1, min(100, (int) $limit));

        $sql = "
            SELECT
                perf.owner_id,
                COALESCE(u.display_name, u.user_login, '') AS owner_name,
                COALESCE(owner_posts.listing_count, 0) AS listing_count,
                perf.listing_views,
                perf.enquiries_received,
                perf.click_phone,
                perf.click_website,
                perf.click_whatsapp,
                ROUND(CASE
                    WHEN perf.listing_views > 0 THEN (perf.enquiries_received / perf.listing_views) * 100
                    ELSE 0
                END, 2) AS conversion_rate
            FROM (
                SELECT
                    owner_id,
                    SUM(CASE WHEN metric = 'listing_view' THEN value ELSE 0 END) AS listing_views,
                    SUM(CASE WHEN metric = 'enquiry_received' THEN value ELSE 0 END) AS enquiries_received,
                    SUM(CASE WHEN metric = 'click_phone' THEN value ELSE 0 END) AS click_phone,
                    SUM(CASE WHEN metric = 'click_website' THEN value ELSE 0 END) AS click_website,
                    SUM(CASE WHEN metric = 'click_whatsapp' THEN value ELSE 0 END) AS click_whatsapp
                FROM {$table}
                WHERE owner_id > 0
                  AND created_at >= %s
                GROUP BY owner_id
            ) AS perf
            LEFT JOIN (
                SELECT post_author AS owner_id, COUNT(*) AS listing_count
                FROM {$wpdb->posts}
                WHERE post_type = %s
                  AND post_status IN ('publish', 'pending', 'draft', 'future', 'private')
                GROUP BY post_author
            ) AS owner_posts ON owner_posts.owner_id = perf.owner_id
            LEFT JOIN {$wpdb->users} AS u ON u.ID = perf.owner_id
            ORDER BY (
                (perf.enquiries_received * 5)
                + ((perf.click_phone + perf.click_website + perf.click_whatsapp) * 2)
                + (perf.listing_views * 0.1)
            ) DESC
            LIMIT %d
        ";

        $prepared = $wpdb->prepare($sql, $since_gmt, $this->post_type, $limit);
        $rows = $wpdb->get_results($prepared, ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        if (!is_array($rows)) {
            return [];
        }

        $normalized = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $owner_id = absint((int) ($row['owner_id'] ?? 0));
            if ($owner_id <= 0) {
                continue;
            }
            $owner_name = sanitize_text_field((string) ($row['owner_name'] ?? ''));
            if ($owner_name === '') {
                $user = get_user_by('id', $owner_id);
                if ($user instanceof WP_User) {
                    $owner_name = sanitize_text_field((string) ($user->display_name ?: $user->user_login));
                }
            }
            if ($owner_name === '') {
                $owner_name = '#' . (string) $owner_id;
            }
            $normalized[] = [
                'owner_id' => $owner_id,
                'owner_name' => $owner_name,
                'listing_count' => (int) round((float) ($row['listing_count'] ?? 0)),
                'listing_views' => (int) round((float) ($row['listing_views'] ?? 0)),
                'enquiries_received' => (int) round((float) ($row['enquiries_received'] ?? 0)),
                'click_phone' => (int) round((float) ($row['click_phone'] ?? 0)),
                'click_website' => (int) round((float) ($row['click_website'] ?? 0)),
                'click_whatsapp' => (int) round((float) ($row['click_whatsapp'] ?? 0)),
                'conversion_rate' => round((float) ($row['conversion_rate'] ?? 0), 2),
            ];
        }

        return $normalized;
    }

    /**
     * Ensures analytics table exists after refactor deployments.
     *
     * @return void
     */
    private function ensure_analytics_table_exists() {
        global $wpdb;

        $table = $this->get_analytics_table_name();
        if ($this->table_exists($table)) {
            return;
        }

        $charset = $wpdb->get_charset_collate();
        $sql = "CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            event_id bigint(20) unsigned NOT NULL DEFAULT 0,
            owner_id bigint(20) unsigned NOT NULL DEFAULT 0,
            user_id bigint(20) unsigned NOT NULL DEFAULT 0,
            metric varchar(64) NOT NULL,
            context varchar(64) NOT NULL DEFAULT '',
            meta_key varchar(64) NOT NULL DEFAULT '',
            meta_value varchar(191) NOT NULL DEFAULT '',
            value decimal(18,4) NOT NULL DEFAULT 1.0000,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY event_metric_date (event_id, metric, created_at),
            KEY owner_metric_date (owner_id, metric, created_at),
            KEY user_metric_date (user_id, metric, created_at),
            KEY metric_date (metric, created_at),
            KEY context_metric_date (context, metric, created_at),
            KEY metric_meta_lookup (metric, meta_key, meta_value(64))
        ) {$charset};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);
    }

    /**
     * @return string
     */
    private function get_analytics_table_name() {
        global $wpdb;
        return (string) $wpdb->prefix . $this->analytics_table;
    }

    /**
     * @return string
     */
    private function get_boost_orders_table_name() {
        global $wpdb;
        return (string) $wpdb->prefix . $this->boost_orders_table;
    }

    /**
     * @return string
     */
    private function get_ticket_orders_table_name() {
        global $wpdb;
        return (string) $wpdb->prefix . $this->ticket_orders_table;
    }

    /**
     * @param string $table
     * @return bool
     */
    private function table_exists($table) {
        global $wpdb;
        $table = sanitize_text_field((string) $table);
        if ($table === '') {
            return false;
        }
        $found = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        return $found === $table;
    }

    /**
     * @param int $days
     * @return array<string, mixed>
     */
    private function normalize_day_range($days) {
        $days = max(1, min(3650, absint((int) $days)));
        $end_ts = time();
        $start_ts = $end_ts - ($days * DAY_IN_SECONDS);
        return [
            'days' => $days,
            'start_gmt' => gmdate('Y-m-d H:i:s', $start_ts),
            'end_gmt' => gmdate('Y-m-d H:i:s', $end_ts),
        ];
    }

    /**
     * @param int $owner_id
     * @param string $start_gmt
     * @param string $end_gmt
     * @return array<string, int|float>
     */
    private function get_owner_metric_totals($owner_id, $start_gmt, $end_gmt) {
        global $wpdb;

        $table = $this->get_analytics_table_name();
        $base = $this->blank_metric_totals();
        if (!$this->table_exists($table)) {
            return $base;
        }

        $sql = "SELECT
            SUM(CASE WHEN analytics.metric = 'listing_view' THEN analytics.value ELSE 0 END) AS listing_views,
            SUM(CASE WHEN analytics.metric = 'enquiry_received' THEN analytics.value ELSE 0 END) AS enquiries_received,
            SUM(CASE WHEN analytics.metric = 'click_phone' THEN analytics.value ELSE 0 END) AS click_phone,
            SUM(CASE WHEN analytics.metric = 'click_website' THEN analytics.value ELSE 0 END) AS click_website,
            SUM(CASE WHEN analytics.metric = 'click_whatsapp' THEN analytics.value ELSE 0 END) AS click_whatsapp,
            SUM(CASE WHEN analytics.metric = 'map_impression' THEN analytics.value ELSE 0 END) AS map_impressions,
            SUM(CASE WHEN analytics.metric = 'search_appearance' THEN analytics.value ELSE 0 END) AS search_appearances
            FROM {$table} AS analytics
            INNER JOIN {$wpdb->posts} AS posts ON posts.ID = analytics.event_id
            WHERE analytics.owner_id = %d
              AND analytics.created_at >= %s
              AND analytics.created_at <= %s
              AND posts.post_type = %s
              AND posts.post_status IN ('publish', 'pending', 'draft', 'future', 'private')";

        $row = $wpdb->get_row($wpdb->prepare($sql, $owner_id, $start_gmt, $end_gmt, $this->post_type), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        if (!is_array($row)) {
            return $base;
        }

        $base['listing_views'] = (int) round((float) ($row['listing_views'] ?? 0));
        $base['enquiries_received'] = (int) round((float) ($row['enquiries_received'] ?? 0));
        $base['click_phone'] = (int) round((float) ($row['click_phone'] ?? 0));
        $base['click_website'] = (int) round((float) ($row['click_website'] ?? 0));
        $base['click_whatsapp'] = (int) round((float) ($row['click_whatsapp'] ?? 0));
        $base['map_impressions'] = (int) round((float) ($row['map_impressions'] ?? 0));
        $base['search_appearances'] = (int) round((float) ($row['search_appearances'] ?? 0));
        $base['conversion_rate'] = $base['listing_views'] > 0
            ? round(($base['enquiries_received'] / max(1, $base['listing_views'])) * 100, 2)
            : 0.0;

        return $base;
    }

    /**
     * @param int $owner_id
     * @param string $start_gmt
     * @param string $end_gmt
     * @return array<int, array<string, mixed>>
     */
    private function get_owner_listing_rows($owner_id, $start_gmt, $end_gmt) {
        global $wpdb;

        $table = $this->get_analytics_table_name();
        if (!$this->table_exists($table)) {
            return [];
        }

        $sql = "SELECT
            analytics.event_id AS event_id,
            SUM(CASE WHEN analytics.metric = 'listing_view' THEN analytics.value ELSE 0 END) AS listing_views,
            SUM(CASE WHEN analytics.metric = 'enquiry_received' THEN analytics.value ELSE 0 END) AS enquiries_received,
            SUM(CASE WHEN analytics.metric = 'click_phone' THEN analytics.value ELSE 0 END) AS click_phone,
            SUM(CASE WHEN analytics.metric = 'click_website' THEN analytics.value ELSE 0 END) AS click_website,
            SUM(CASE WHEN analytics.metric = 'click_whatsapp' THEN analytics.value ELSE 0 END) AS click_whatsapp,
            SUM(CASE WHEN analytics.metric = 'map_impression' THEN analytics.value ELSE 0 END) AS map_impressions,
            SUM(CASE WHEN analytics.metric = 'search_appearance' THEN analytics.value ELSE 0 END) AS search_appearances
            FROM {$table} AS analytics
            INNER JOIN {$wpdb->posts} AS posts ON posts.ID = analytics.event_id
            WHERE analytics.owner_id = %d
              AND analytics.event_id > 0
              AND analytics.created_at >= %s
              AND analytics.created_at <= %s
              AND posts.post_type = %s
              AND posts.post_status IN ('publish', 'pending', 'draft', 'future', 'private')
            GROUP BY analytics.event_id
            ORDER BY listing_views DESC";

        $rows = $wpdb->get_results($wpdb->prepare($sql, $owner_id, $start_gmt, $end_gmt, $this->post_type), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        if (!is_array($rows)) {
            return [];
        }

        $normalized = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $event_id = absint((int) ($row['event_id'] ?? 0));
            if ($event_id <= 0 || get_post_type($event_id) !== $this->post_type) {
                continue;
            }
            $listing_views = (int) round((float) ($row['listing_views'] ?? 0));
            $enquiries = (int) round((float) ($row['enquiries_received'] ?? 0));
            $normalized[] = [
                'event_id' => $event_id,
                'title' => sanitize_text_field((string) get_the_title($event_id)),
                'url' => esc_url_raw((string) get_permalink($event_id)),
                'is_boosted' => $this->is_listing_boosted($event_id) ? 1 : 0,
                'listing_views' => $listing_views,
                'enquiries_received' => $enquiries,
                'click_phone' => (int) round((float) ($row['click_phone'] ?? 0)),
                'click_website' => (int) round((float) ($row['click_website'] ?? 0)),
                'click_whatsapp' => (int) round((float) ($row['click_whatsapp'] ?? 0)),
                'map_impressions' => (int) round((float) ($row['map_impressions'] ?? 0)),
                'search_appearances' => (int) round((float) ($row['search_appearances'] ?? 0)),
                'conversion_rate' => $listing_views > 0 ? round(($enquiries / max(1, $listing_views)) * 100, 2) : 0.0,
            ];
        }

        return $normalized;
    }

    /**
     * @param int $owner_id
     * @param string $start_gmt
     * @param string $end_gmt
     * @param int $days
     * @return array<int, array<string, mixed>>
     */
    private function get_owner_trend_rows($owner_id, $start_gmt, $end_gmt, $days) {
        global $wpdb;

        $table = $this->get_analytics_table_name();
        if (!$this->table_exists($table)) {
            return [];
        }

        $days = max(1, min(3650, (int) $days));
        $sql = "SELECT
            DATE(analytics.created_at) AS day_bucket,
            SUM(CASE WHEN analytics.metric = 'listing_view' THEN analytics.value ELSE 0 END) AS listing_views,
            SUM(CASE WHEN analytics.metric = 'enquiry_received' THEN analytics.value ELSE 0 END) AS enquiries_received
            FROM {$table} AS analytics
            INNER JOIN {$wpdb->posts} AS posts ON posts.ID = analytics.event_id
            WHERE analytics.owner_id = %d
              AND analytics.created_at >= %s
              AND analytics.created_at <= %s
              AND posts.post_type = %s
              AND posts.post_status IN ('publish', 'pending', 'draft', 'future', 'private')
            GROUP BY DATE(analytics.created_at)
            ORDER BY day_bucket ASC";

        $raw = $wpdb->get_results($wpdb->prepare($sql, $owner_id, $start_gmt, $end_gmt, $this->post_type), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $indexed = [];
        if (is_array($raw)) {
            foreach ($raw as $row) {
                if (!is_array($row)) {
                    continue;
                }
                $day = sanitize_text_field((string) ($row['day_bucket'] ?? ''));
                if ($day === '') {
                    continue;
                }
                $indexed[$day] = [
                    'listing_views' => (int) round((float) ($row['listing_views'] ?? 0)),
                    'enquiries_received' => (int) round((float) ($row['enquiries_received'] ?? 0)),
                ];
            }
        }

        $trend = [];
        $start_ts = strtotime($start_gmt . ' UTC');
        if (!$start_ts) {
            return $trend;
        }
        for ($offset = 0; $offset <= $days; $offset++) {
            $ts = $start_ts + ($offset * DAY_IN_SECONDS);
            if ($ts > time()) {
                break;
            }
            $day_key = gmdate('Y-m-d', $ts);
            $values = $indexed[$day_key] ?? ['listing_views' => 0, 'enquiries_received' => 0];
            $trend[] = [
                'bucket' => $day_key,
                'label' => wp_date('M j', $ts),
                'listing_views' => (int) ($values['listing_views'] ?? 0),
                'enquiries_received' => (int) ($values['enquiries_received'] ?? 0),
            ];
        }

        return $trend;
    }

    /**
     * @param array<int, array<string, mixed>> $trend_rows
     * @return int
     */
    private function compute_trend_max(array $trend_rows) {
        $max_value = 0;
        foreach ($trend_rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $max_value = max(
                $max_value,
                (int) ($row['listing_views'] ?? 0),
                (int) ($row['enquiries_received'] ?? 0)
            );
        }
        return max(1, $max_value);
    }

    /**
     * @param array<string, int|float> $current
     * @param array<string, int|float> $previous
     * @return array<string, array<string, float|string>>
     */
    private function build_metric_deltas(array $current, array $previous) {
        $keys = ['listing_views', 'enquiries_received', 'click_phone', 'click_website', 'click_whatsapp', 'map_impressions', 'search_appearances', 'conversion_rate'];
        $deltas = [];
        foreach ($keys as $key) {
            $current_value = (float) ($current[$key] ?? 0);
            $previous_value = (float) ($previous[$key] ?? 0);
            $change_pct = 0.0;
            if ($previous_value > 0.0) {
                $change_pct = (($current_value - $previous_value) / $previous_value) * 100;
            } elseif ($current_value > 0.0) {
                $change_pct = 100.0;
            }
            $trend = 'flat';
            if ($change_pct > 0.00001) {
                $trend = 'up';
            } elseif ($change_pct < -0.00001) {
                $trend = 'down';
            }
            $deltas[$key] = [
                'current' => round($current_value, 2),
                'previous' => round($previous_value, 2),
                'change_pct' => round($change_pct, 2),
                'trend' => $trend,
            ];
        }
        return $deltas;
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @return array<string, mixed>
     */
    private function build_boost_impact_snapshot(array $rows) {
        $boosted_rows = [];
        $non_boosted_rows = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            if (!empty($row['is_boosted'])) {
                $boosted_rows[] = $row;
            } else {
                $non_boosted_rows[] = $row;
            }
        }

        $boosted = $this->summarize_listing_rows($boosted_rows);
        $non_boosted = $this->summarize_listing_rows($non_boosted_rows);

        $total_views = (float) $boosted['listing_views'] + (float) $non_boosted['listing_views'];
        $total_enquiries = (float) $boosted['enquiries_received'] + (float) $non_boosted['enquiries_received'];
        $views_share = $total_views > 0 ? ((float) $boosted['listing_views'] / $total_views) * 100 : 0.0;
        $enquiries_share = $total_enquiries > 0 ? ((float) $boosted['enquiries_received'] / $total_enquiries) * 100 : 0.0;

        $boosted_views_per_listing = (int) $boosted['listings'] > 0 ? ((float) $boosted['listing_views'] / (float) $boosted['listings']) : 0.0;
        $non_views_per_listing = (int) $non_boosted['listings'] > 0 ? ((float) $non_boosted['listing_views'] / (float) $non_boosted['listings']) : 0.0;
        $boosted_enquiries_per_listing = (int) $boosted['listings'] > 0 ? ((float) $boosted['enquiries_received'] / (float) $boosted['listings']) : 0.0;
        $non_enquiries_per_listing = (int) $non_boosted['listings'] > 0 ? ((float) $non_boosted['enquiries_received'] / (float) $non_boosted['listings']) : 0.0;

        $uplift_views = $this->calculate_uplift_percent($boosted_views_per_listing, $non_views_per_listing);
        $uplift_enquiries = $this->calculate_uplift_percent($boosted_enquiries_per_listing, $non_enquiries_per_listing);
        $uplift_conversion = $this->calculate_uplift_percent((float) $boosted['conversion_rate'], (float) $non_boosted['conversion_rate']);

        return [
            'boosted' => $boosted,
            'non_boosted' => $non_boosted,
            'shares' => [
                'views_boosted_pct' => round($views_share, 2),
                'enquiries_boosted_pct' => round($enquiries_share, 2),
            ],
            'uplift' => [
                'views_per_listing_pct' => round($uplift_views, 2),
                'enquiries_per_listing_pct' => round($uplift_enquiries, 2),
                'conversion_rate_pct' => round($uplift_conversion, 2),
            ],
        ];
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @return array<string, int|float>
     */
    private function summarize_listing_rows(array $rows) {
        $summary = [
            'listings' => 0,
            'listing_views' => 0,
            'enquiries_received' => 0,
            'conversion_rate' => 0.0,
        ];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $summary['listings']++;
            $summary['listing_views'] += (int) ($row['listing_views'] ?? 0);
            $summary['enquiries_received'] += (int) ($row['enquiries_received'] ?? 0);
        }
        $summary['conversion_rate'] = $summary['listing_views'] > 0
            ? round(($summary['enquiries_received'] / max(1, $summary['listing_views'])) * 100, 2)
            : 0.0;

        return $summary;
    }

    /**
     * @param float $current
     * @param float $baseline
     * @return float
     */
    private function calculate_uplift_percent($current, $baseline) {
        $current = (float) $current;
        $baseline = (float) $baseline;
        if ($baseline > 0.0) {
            return (($current - $baseline) / $baseline) * 100;
        }
        if ($current > 0.0) {
            return 100.0;
        }
        return 0.0;
    }

    /**
     * @param string $start_gmt
     * @param string $end_gmt
     * @param int $days
     * @return array<string, int|float>
     */
    private function get_platform_summary($start_gmt, $end_gmt, $days) {
        global $wpdb;

        $counts = wp_count_posts($this->post_type);
        $published = (int) ($counts->publish ?? 0);
        $pending = (int) ($counts->pending ?? 0);
        $draft = (int) ($counts->draft ?? 0);
        $future = (int) ($counts->future ?? 0);
        $private = (int) ($counts->private ?? 0);
        $expired = (int) ($counts->expired ?? 0);

        $total_submissions = $published + $pending + $draft + $future + $private + $expired;
        $total_listings = $published + $pending + $draft + $future + $private;
        $total_users = 0;
        if (function_exists('count_users')) {
            $counted_users = (array) count_users();
            $total_users = (int) ($counted_users['total_users'] ?? 0);
        } else {
            $total_users = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->users}"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        }

        $metric_totals = $this->get_platform_metric_totals($start_gmt, $end_gmt);
        $listing_views = (int) ($metric_totals['listing_views'] ?? 0);
        $enquiries_received = (int) ($metric_totals['enquiries_received'] ?? 0);
        $view_to_enquiry = $listing_views > 0 ? round(($enquiries_received / max(1, $listing_views)) * 100, 2) : 0.0;
        $approval_conversion = $total_submissions > 0 ? round(($published / max(1, $total_submissions)) * 100, 2) : 0.0;

        $imports = (int) get_option('eep_import_events_count', 0);
        $spam_option = (int) get_option('eep_spam_blocks_count', 0);
        $spam_metric = (int) ($metric_totals['spam_blocks'] ?? 0);
        $spam_blocks = max($spam_option, $spam_metric);

        $boost = $this->get_boost_summary($days);
        $ticket_revenue = $this->get_ticket_revenue($start_gmt, $end_gmt);
        $revenue_estimate = round(((float) ($boost['boost_revenue'] ?? 0)) + $ticket_revenue, 2);

        return [
            'total_submissions' => $total_submissions,
            'approved' => $published,
            'pending' => $pending,
            'imports' => $imports,
            'spam_blocks' => $spam_blocks,
            'approval_conversion' => $approval_conversion,
            'total_listings' => $total_listings,
            'total_users' => $total_users,
            'listing_views' => $listing_views,
            'enquiries_received' => $enquiries_received,
            'view_to_enquiry_conversion' => $view_to_enquiry,
            'revenue_estimate' => $revenue_estimate,
            'boosts_sold' => (int) ($boost['boosts_sold'] ?? 0),
            'boost_revenue' => round((float) ($boost['boost_revenue'] ?? 0), 2),
            'renewal_rate' => round((float) ($boost['renewal_rate'] ?? 0), 2),
            'active_boosted_listings' => (int) ($boost['active_boosted'] ?? 0),
        ];
    }

    /**
     * @param string $start_gmt
     * @param string $end_gmt
     * @return array<string, int>
     */
    private function get_platform_metric_totals($start_gmt, $end_gmt) {
        global $wpdb;

        $table = $this->get_analytics_table_name();
        $base = [
            'listing_views' => 0,
            'enquiries_received' => 0,
            'click_phone' => 0,
            'click_website' => 0,
            'click_whatsapp' => 0,
            'map_impressions' => 0,
            'search_appearances' => 0,
            'spam_blocks' => 0,
        ];
        if (!$this->table_exists($table)) {
            return $base;
        }

        $sql = "SELECT
            SUM(CASE WHEN metric = 'listing_view' THEN value ELSE 0 END) AS listing_views,
            SUM(CASE WHEN metric = 'enquiry_received' THEN value ELSE 0 END) AS enquiries_received,
            SUM(CASE WHEN metric = 'click_phone' THEN value ELSE 0 END) AS click_phone,
            SUM(CASE WHEN metric = 'click_website' THEN value ELSE 0 END) AS click_website,
            SUM(CASE WHEN metric = 'click_whatsapp' THEN value ELSE 0 END) AS click_whatsapp,
            SUM(CASE WHEN metric = 'map_impression' THEN value ELSE 0 END) AS map_impressions,
            SUM(CASE WHEN metric = 'search_appearance' THEN value ELSE 0 END) AS search_appearances,
            SUM(CASE WHEN metric = 'spam_block' THEN value ELSE 0 END) AS spam_blocks
            FROM {$table}
            WHERE created_at >= %s
              AND created_at <= %s";

        $row = $wpdb->get_row($wpdb->prepare($sql, $start_gmt, $end_gmt), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        if (!is_array($row)) {
            return $base;
        }

        foreach ($base as $key => $unused) {
            $base[$key] = (int) round((float) ($row[$key] ?? 0));
        }
        return $base;
    }

    /**
     * @param string $start_gmt
     * @param string $end_gmt
     * @return array<int, array<string, mixed>>
     */
    private function get_growth_rows($start_gmt, $end_gmt) {
        global $wpdb;

        $start_ts = strtotime($start_gmt . ' UTC');
        $end_ts = strtotime($end_gmt . ' UTC');
        if (!$start_ts || !$end_ts || $start_ts > $end_ts) {
            return [];
        }

        $posts_sql = "SELECT DATE_FORMAT(post_date_gmt, '%%Y-%%m') AS ym, COUNT(*) AS total
            FROM {$wpdb->posts}
            WHERE post_type = %s
              AND post_date_gmt >= %s
              AND post_date_gmt <= %s
              AND post_status IN ('publish', 'pending', 'draft', 'future', 'private')
            GROUP BY DATE_FORMAT(post_date_gmt, '%%Y-%%m')";
        $post_rows = $wpdb->get_results($wpdb->prepare($posts_sql, $this->post_type, $start_gmt, $end_gmt), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

        $users_sql = "SELECT DATE_FORMAT(user_registered, '%%Y-%%m') AS ym, COUNT(*) AS total
            FROM {$wpdb->users}
            WHERE user_registered >= %s
              AND user_registered <= %s
            GROUP BY DATE_FORMAT(user_registered, '%%Y-%%m')";
        $user_rows = $wpdb->get_results($wpdb->prepare($users_sql, $start_gmt, $end_gmt), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

        $posts_map = [];
        foreach ((array) $post_rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $key = sanitize_text_field((string) ($row['ym'] ?? ''));
            if ($key === '') {
                continue;
            }
            $posts_map[$key] = (int) round((float) ($row['total'] ?? 0));
        }
        $users_map = [];
        foreach ((array) $user_rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $key = sanitize_text_field((string) ($row['ym'] ?? ''));
            if ($key === '') {
                continue;
            }
            $users_map[$key] = (int) round((float) ($row['total'] ?? 0));
        }

        $rows = [];
        $cursor = strtotime(gmdate('Y-m-01 00:00:00', $start_ts) . ' UTC');
        $last = strtotime(gmdate('Y-m-01 00:00:00', $end_ts) . ' UTC');
        while ($cursor && $cursor <= $last) {
            $ym = gmdate('Y-m', $cursor);
            $rows[] = [
                'bucket' => $ym,
                'label' => wp_date('M Y', $cursor),
                'listings' => (int) ($posts_map[$ym] ?? 0),
                'users' => (int) ($users_map[$ym] ?? 0),
            ];
            $cursor = strtotime('+1 month', $cursor);
        }

        return $rows;
    }

    /**
     * @param string $start_gmt
     * @param string $end_gmt
     * @return array<int, array<string, mixed>>
     */
    private function get_most_viewed_rows($start_gmt, $end_gmt) {
        global $wpdb;

        $table = $this->get_analytics_table_name();
        if (!$this->table_exists($table)) {
            return [];
        }

        $sql = "SELECT event_id, SUM(value) AS total
            FROM {$table}
            WHERE metric = 'listing_view'
              AND event_id > 0
              AND created_at >= %s
              AND created_at <= %s
            GROUP BY event_id
            ORDER BY total DESC
            LIMIT 10";
        $rows = $wpdb->get_results($wpdb->prepare($sql, $start_gmt, $end_gmt), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

        $normalized = [];
        foreach ((array) $rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $event_id = absint((int) ($row['event_id'] ?? 0));
            if ($event_id <= 0 || get_post_type($event_id) !== $this->post_type) {
                continue;
            }
            $normalized[] = [
                'event_id' => $event_id,
                'title' => sanitize_text_field((string) get_the_title($event_id)),
                'url' => esc_url_raw((string) get_permalink($event_id)),
                'total' => (int) round((float) ($row['total'] ?? 0)),
            ];
        }
        return $normalized;
    }

    /**
     * @param string $start_gmt
     * @param string $end_gmt
     * @return array<int, array<string, mixed>>
     */
    private function get_most_searched_category_rows($start_gmt, $end_gmt) {
        global $wpdb;

        $table = $this->get_analytics_table_name();
        if (!$this->table_exists($table)) {
            return [];
        }

        $sql = "SELECT meta_value AS slug, SUM(value) AS total
            FROM {$table}
            WHERE metric = 'search_filter'
              AND meta_key IN ('event_type', 'event_mode', 'event_tag', 'event_price')
              AND meta_value <> ''
              AND created_at >= %s
              AND created_at <= %s
            GROUP BY meta_value
            ORDER BY total DESC
            LIMIT 10";
        $rows = $wpdb->get_results($wpdb->prepare($sql, $start_gmt, $end_gmt), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

        $normalized = [];
        foreach ((array) $rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $slug = sanitize_key((string) ($row['slug'] ?? ''));
            if ($slug === '') {
                continue;
            }
            $label = $slug;
            $term = get_term_by('slug', $slug, 'event_type');
            if (!$term || is_wp_error($term)) {
                $term = get_term_by('slug', $slug, 'event_mode');
            }
            if (!$term || is_wp_error($term)) {
                $term = get_term_by('slug', $slug, 'event_tag');
            }
            if (!$term || is_wp_error($term)) {
                $term = get_term_by('slug', $slug, 'event_price_range');
            }
            if ($term && !is_wp_error($term)) {
                $label = sanitize_text_field((string) $term->name);
            } else {
                $label = ucwords(str_replace('-', ' ', $slug));
            }
            $normalized[] = [
                'slug' => $slug,
                'label' => $label,
                'total' => (int) round((float) ($row['total'] ?? 0)),
            ];
        }
        return $normalized;
    }

    /**
     * @param string $start_gmt
     * @param string $end_gmt
     * @return array<int, array<string, mixed>>
     */
    private function get_most_active_area_rows($start_gmt, $end_gmt) {
        global $wpdb;

        $table = $this->get_analytics_table_name();
        if (!$this->table_exists($table)) {
            return [];
        }

        $sql = "SELECT meta_value AS bucket, SUM(value) AS total
            FROM {$table}
            WHERE metric = 'map_area_search'
              AND meta_key = 'bbox'
              AND meta_value <> ''
              AND created_at >= %s
              AND created_at <= %s
            GROUP BY meta_value
            ORDER BY total DESC
            LIMIT 10";
        $rows = $wpdb->get_results($wpdb->prepare($sql, $start_gmt, $end_gmt), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

        $normalized = [];
        foreach ((array) $rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $bucket = sanitize_text_field((string) ($row['bucket'] ?? ''));
            if ($bucket === '') {
                continue;
            }
            $normalized[] = [
                'bucket' => $bucket,
                'total' => (int) round((float) ($row['total'] ?? 0)),
            ];
        }
        return $normalized;
    }

    /**
     * @param int $days
     * @return array<string, int|float>
     */
    private function get_boost_summary($days = 30) {
        global $wpdb;

        $table = $this->get_boost_orders_table_name();
        if (!$this->table_exists($table)) {
            return [
                'boosts_sold' => 0,
                'boost_revenue' => 0.0,
                'renewal_rate' => 0.0,
                'active_boosted' => 0,
            ];
        }

        $days = max(0, (int) $days);
        $params = [];
        $date_clause = '';
        if ($days > 0) {
            $date_clause = ' AND created_at >= %s';
            $params[] = gmdate('Y-m-d H:i:s', time() - ($days * DAY_IN_SECONDS));
        }

        $summary_sql = "SELECT
            SUM(CASE WHEN order_status IN ('active','paid','renewed') AND is_renewal = 0 THEN 1 ELSE 0 END) AS boosts_sold,
            SUM(CASE WHEN order_status IN ('active','paid','renewed') THEN total_amount ELSE 0 END) AS revenue,
            SUM(CASE WHEN order_status IN ('active','paid') AND is_renewal = 1 THEN 1 ELSE 0 END) AS renewals_success,
            SUM(CASE WHEN is_renewal = 1 THEN 1 ELSE 0 END) AS renewals_total
            FROM {$table}
            WHERE 1=1{$date_clause}";
        if (!empty($params)) {
            $summary_row = $wpdb->get_row($wpdb->prepare($summary_sql, $params), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        } else {
            $summary_row = $wpdb->get_row($summary_sql, ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        }

        $active_sql = "SELECT COUNT(DISTINCT post_id)
            FROM {$table}
            WHERE order_status IN ('active','paid')
              AND expires_at IS NOT NULL
              AND expires_at >= %s";
        $active_count = (int) $wpdb->get_var($wpdb->prepare($active_sql, gmdate('Y-m-d H:i:s'))); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

        $boosts_sold = (int) round((float) ($summary_row['boosts_sold'] ?? 0));
        $revenue = (float) ($summary_row['revenue'] ?? 0);
        $renewals_success = (int) round((float) ($summary_row['renewals_success'] ?? 0));
        $renewals_total = (int) round((float) ($summary_row['renewals_total'] ?? 0));
        $renewal_rate = $renewals_total > 0 ? round(($renewals_success / max(1, $renewals_total)) * 100, 2) : 0.0;

        return [
            'boosts_sold' => $boosts_sold,
            'boost_revenue' => round($revenue, 2),
            'renewal_rate' => $renewal_rate,
            'active_boosted' => $active_count,
        ];
    }

    /**
     * @param string $start_gmt
     * @param string $end_gmt
     * @return float
     */
    private function get_ticket_revenue($start_gmt, $end_gmt) {
        global $wpdb;

        $table = $this->get_ticket_orders_table_name();
        if (!$this->table_exists($table)) {
            return 0.0;
        }

        $sql = "SELECT COALESCE(SUM(ticket_total), 0) AS total
            FROM {$table}
            WHERE purchased_at >= %s
              AND purchased_at <= %s
              AND (payment_collected = 1 OR payment_status IN ('confirmed', 'paid', 'completed'))";
        $total = (float) $wpdb->get_var($wpdb->prepare($sql, $start_gmt, $end_gmt)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        return round(max(0.0, $total), 2);
    }

    /**
     * @param int $event_id
     * @return bool
     */
    private function is_listing_boosted($event_id) {
        $event_id = absint((int) $event_id);
        if ($event_id <= 0) {
            return false;
        }

        $active = (string) get_post_meta($event_id, 'eep_boost_active', true);
        if ($active !== '1') {
            return false;
        }

        $expires_gmt = sanitize_text_field((string) get_post_meta($event_id, 'eep_boost_expires_gmt', true));
        if ($expires_gmt === '') {
            return true;
        }
        $expires_ts = strtotime($expires_gmt . ' UTC');
        if (!$expires_ts) {
            return true;
        }
        return $expires_ts >= time();
    }

    /**
     * @return array<string, mixed>
     */
    private function get_empty_owner_report() {
        return [
            'owner_id' => 0,
            'days' => 30,
            'start_gmt' => '',
            'end_gmt' => '',
            'totals' => $this->blank_metric_totals(),
            'rows' => [],
            'trend' => [],
            'trend_meta' => ['max_value' => 1],
            'comparison' => [
                'current' => $this->blank_metric_totals(),
                'previous' => $this->blank_metric_totals(),
                'deltas' => $this->build_metric_deltas($this->blank_metric_totals(), $this->blank_metric_totals()),
                'previous_start_gmt' => '',
                'previous_end_gmt' => '',
            ],
            'boost_impact' => [
                'boosted' => $this->summarize_listing_rows([]),
                'non_boosted' => $this->summarize_listing_rows([]),
                'shares' => ['views_boosted_pct' => 0.0, 'enquiries_boosted_pct' => 0.0],
                'uplift' => ['views_per_listing_pct' => 0.0, 'enquiries_per_listing_pct' => 0.0, 'conversion_rate_pct' => 0.0],
            ],
        ];
    }

    /**
     * @return array<string, int|float>
     */
    private function blank_metric_totals() {
        return [
            'listing_views' => 0,
            'click_phone' => 0,
            'click_website' => 0,
            'click_whatsapp' => 0,
            'enquiries_received' => 0,
            'map_impressions' => 0,
            'search_appearances' => 0,
            'conversion_rate' => 0.0,
        ];
    }
}
