<?php

namespace fluXtore\Core\Admin\Stores;

use DateTime;
use DateInterval;

trait AnalyticsCalculationsTrait {
    protected function normalise_rate($conversions, $visits) {
        $visits = (int) $visits;
        $conversions = (int) $conversions;

        if ($visits <= 0) {
            return 0.0;
        }

        return max(0.0, min(1.0, $conversions / $visits));
    }

    protected function build_dropoff_rows(array $steps, array $metrics) {
        $dropoff = [];
        $baseline = null;
        $previous = null;

        foreach ($steps as $position => $step) {
            $stepId = (int) $step['id'];
            $data = $metrics[$stepId] ?? ['visits' => 0, 'conversions' => 0, 'revenue' => 0.0];

            $visits = (int) ($data['visits'] ?? 0);
            $conversions = (int) ($data['conversions'] ?? 0);

            if ($baseline === null) {
                $baseline = $visits > 0 ? $visits : max($conversions, 1);
            }

            $throughput = $baseline > 0 ? $conversions / $baseline : 0.0;
            $conversionRate = $this->normalise_rate($conversions, $visits);

            $dropoffRate = null;
            if ($previous !== null && $previous > 0) {
                $dropoffRate = 1 - ($conversions / $previous);
                if ($dropoffRate < 0) {
                    $dropoffRate = 0.0;
                }
            }

            $dropoff[] = [
                'id' => $stepId,
                'label' => $step['title'],
                'tag' => $step['tag'],
                'value' => $conversions,
                'visits' => $visits,
                'conversions' => $conversions,
                'conversion_rate' => $conversionRate,
                'throughput_rate' => $throughput,
                'dropoff_rate' => $dropoffRate,
            ];

            $previous = max($conversions, 0);
        }

        return $dropoff;
    }

    protected function format_interval_key(DateTime $date, $interval) {
        switch ($interval) {
            case 'week':
                return $date->format('o-\WW');
            case 'month':
                return $date->format('Y-m');
            case 'quarter':
                $month = (int) $date->format('n');
                $quarter = (int) floor(($month - 1) / 3) + 1;
                return $date->format('Y') . '-Q' . $quarter;
            case 'day':
            default:
                return $date->format('Y-m-d');
        }
    }

    protected function build_timeline_rows(array $seriesRows, array $steps, $interval) {
        $timeline = [];

        foreach ($steps as $step) {
            $timeline[(int) $step['id']] = [];
        }

        $grouped = [];

        foreach ($seriesRows as $row) {
            $date = new DateTime($row['metric_date']);
            $stepId = (int) $row['step_id'];
            $key = $this->format_interval_key($date, $interval);

            if (!isset($grouped[$stepId])) {
                $grouped[$stepId] = [];
            }

            if (!isset($grouped[$stepId][$key])) {
                $grouped[$stepId][$key] = ['visits' => 0, 'conversions' => 0];
            }

            $grouped[$stepId][$key]['visits'] += (int) ($row['visits'] ?? 0);
            $grouped[$stepId][$key]['conversions'] += (int) ($row['conversions'] ?? 0);
        }

        foreach ($steps as $step) {
            $stepId = (int) $step['id'];
            $buckets = $grouped[$stepId] ?? [];
            ksort($buckets);

            $timeline[$stepId] = [
                'id' => $stepId,
                'label' => $step['title'],
                'tag' => $step['tag'],
                'data' => [],
            ];

            foreach ($buckets as $key => $bucket) {
                $visits = (int) $bucket['visits'];
                $conversions = (int) $bucket['conversions'];
                $timeline[$stepId]['data'][] = [
                    'x' => $key,
                    'y' => $this->normalise_rate($conversions, $visits),
                    'visits' => $visits,
                    'conversions' => $conversions,
                ];
            }
        }

        return array_values($timeline);
    }

    protected function build_cohort_matrix(array $seriesRows, array $steps) {
        if (empty($steps)) {
            return [];
        }

        $firstStepId = (int) $steps[0]['id'];
        $byWeek = [];

        foreach ($seriesRows as $row) {
            $date = new DateTime($row['metric_date']);
            $weekStart = clone $date;
            $weekStart->modify('monday this week');
            $weekKey = $weekStart->format('Y-m-d');
            $stepId = (int) $row['step_id'];

            if (!isset($byWeek[$weekKey])) {
                $byWeek[$weekKey] = [];
            }

            if (!isset($byWeek[$weekKey][$stepId])) {
                $byWeek[$weekKey][$stepId] = ['visits' => 0, 'conversions' => 0];
            }

            $byWeek[$weekKey][$stepId]['visits'] += (int) ($row['visits'] ?? 0);
            $byWeek[$weekKey][$stepId]['conversions'] += (int) ($row['conversions'] ?? 0);
        }

        ksort($byWeek);

        $matrix = [];
        foreach ($byWeek as $week => $entries) {
            $baseline = $entries[$firstStepId]['visits'] ?? 0;
            $row = [
                'cohort' => $week,
                'values' => [],
            ];

            foreach ($steps as $step) {
                $stepId = (int) $step['id'];
                $visits = (int) ($entries[$stepId]['visits'] ?? 0);
                $conversions = (int) ($entries[$stepId]['conversions'] ?? 0);
                $relative = $baseline > 0 ? $conversions / $baseline : ($visits > 0 ? $conversions / $visits : 0);
                $row['values'][] = [
                    'step_id' => $stepId,
                    'label' => $step['title'],
                    'rate' => max(0.0, min(1.0, $relative)),
                    'visits' => $visits,
                    'conversions' => $conversions,
                ];
            }

            $matrix[] = $row;
        }

        return $matrix;
    }

    protected function summarise_products(array $metrics, array $steps, callable $productNameResolver) {
        $products = [];

        foreach ($steps as $step) {
            $stepId = (int) $step['id'];
            $productId = isset($step['product_id']) ? (int) $step['product_id'] : 0;
            $metric = $metrics[$stepId] ?? null;

            if ($productId <= 0 || !$metric) {
                continue;
            }

            if (!isset($products[$productId])) {
                $products[$productId] = [
                    'product_id' => $productId,
                    'name' => call_user_func($productNameResolver, $productId),
                    'conversions' => 0,
                    'revenue' => 0.0,
                    'steps' => [],
                ];
            }

            $products[$productId]['conversions'] += (int) ($metric['conversions'] ?? 0);
            $products[$productId]['revenue'] += (float) ($metric['revenue'] ?? 0.0);
            $products[$productId]['steps'][] = $step['title'];
        }

        usort($products, static function ($a, $b) {
            if ($a['revenue'] === $b['revenue']) {
                return $b['conversions'] <=> $a['conversions'];
            }

            return $b['revenue'] <=> $a['revenue'];
        });

        foreach ($products as &$product) {
            $product['revenue_formatted'] = function_exists('wc_price') ? wc_price($product['revenue']) : $product['revenue'];
        }
        unset($product);

        return $products;
    }

    protected function generate_insights(array $currentMetrics, array $previousMetrics, array $steps, array $products) {
        $insights = [];

        $largestDrop = null;
        $largestDropStep = null;

        foreach ($steps as $index => $step) {
            $stepId = (int) $step['id'];
            $current = $currentMetrics[$stepId] ?? ['conversions' => 0, 'visits' => 0];
            $previous = $previousMetrics[$stepId] ?? null;

            if (!$previous) {
                continue;
            }

            $currentRate = $this->normalise_rate($current['conversions'] ?? 0, $current['visits'] ?? 0);
            $previousRate = $this->normalise_rate($previous['conversions'] ?? 0, $previous['visits'] ?? 0);
            $delta = $currentRate - $previousRate;

            if ($delta < 0 && ($largestDrop === null || $delta < $largestDrop)) {
                $largestDrop = $delta;
                $largestDropStep = $step;
            }
        }

        if ($largestDrop !== null && $largestDropStep) {
            $percentage = round(abs($largestDrop) * 100, 1);
            $insights[] = [
                'level' => 'warning',
                'message' => sprintf(
                    /* translators: 1: Step title, 2: Percentage */
                    __('%1$s conversion fell by %2$s%% compared to the previous period.', 'fluxtore'),
                    $largestDropStep['title'],
                    $percentage
                ),
            ];
        }

        if (!empty($products)) {
            $topProduct = $products[0];
            $insights[] = [
                'level' => 'info',
                'message' => sprintf(
                    /* translators: 1: Product name, 2: Revenue amount */
                    __('%1$s generated the most funnel revenue (approximately %2$s).', 'fluxtore'),
                    $topProduct['name'],
                    wc_price($topProduct['revenue'])
                ),
            ];
        }

        if (empty($insights)) {
            $insights[] = [
                'level' => 'info',
                'message' => __('Funnel performance is stable across the selected period.', 'fluxtore'),
            ];
        }

        return $insights;
    }
}

class DataStore {
        use AnalyticsCalculationsTrait;

        const DATETIME_FORMAT = 'Y-m-d H:i:s';
        const DATE_FORMAT = 'Y-m-d';

        protected static $metrics_table_cache = null;

    public function get_data($args) { return []; }

    public function loop_bumps($orders) {
        $result = [];

        foreach ( $orders as $order_id ) {
            if ( fluxtore_order_has_bump( $order_id ) ) {
                $result []= $order_id;
            }
        }

        return $result;
    }

    public function loop_upsells($orders) {
        $result = [];

        foreach ( $orders as $order_id ) {
			if (  fluxtore_order_is_upsell( $order_id ) ) {
                $result []= $order_id;
            }
        }

        return $result;
    }

    public function loop_downsells($orders) {
        $result = [];

        foreach ( $orders as $order_id ) {
            if ( fluxtore_order_is_downsell( $order_id ) ) {
                $result []= $order_id;
            }
        }

        return $result;
    }

	/**
	 * @param \WP_Post $step
	 * @param $args
	 *
	 * @return int[]|\WP_Post[]
	 * @throws \Exception
	 */
    public function get_orders_by_step($step, $args) {
        $date_after = new DateTime($args['after']);
		$date_before = new DateTime($args['before']);

		$args = [
            'numberposts' => -1,
            'post_type' => 'shop_order',
            'post_status'    => ['wc-completed', 'wc-processing', 'wc-on-hold', 'wc-pending'],
            'meta_query' => [
                'relation' => 'AND',
                [
                    'key' => FLUXTORE_PREFIX . 'order_flow',
                    'value' => absint($args['flow_id']),
                    'type' => 'NUMERIC',
                    'compare' => '='
                ],
                [
                    'key' =>  FLUXTORE_PREFIX . 'order_step',
                    'value' => absint($step->ID),
                    'type' => 'NUMERIC',
                    'compare' => '='
                ]
            ],
            'date_query' => [
                [
                    'before' =>  $date_before->format(self::DATETIME_FORMAT),
                    'after' => $date_after->format(self::DATE_FORMAT),
                    'inclusive' => true,
                ]
            ],
            'fields' => 'ids'
		];
            
      $order_ids = wc_get_orders( $args );

			if ( ! empty( $order_ids ) ) {
				// Do something with the order IDs
				foreach ( $order_ids as $order_id ) {
					//echo $order_id . '<br>';
				}
			} else {
				// No orders found
				//echo 'No orders found.';
			}

			return $order_ids; 
			//   return get_posts($args);
    }

	private function get_order_args_by_flow_id($flow_id = null) {
		if ($flow_id) {
			$args = [
				'meta_key' => FLUXTORE_PREFIX . 'order_flow',
				'meta_value' => $flow_id,
			];
		} else {
			$args = ['meta_key' => FLUXTORE_PREFIX . 'is_fluxtore_order'];
		}

		return $args;
	}

	private function get_orders_by_date($date, $flow_id = null) {
		$args = $this->get_order_args_by_flow_id($flow_id);

		$defaults = [
			'limit' => -1,
			'status' => ['wc-completed', 'wc-processing', 'wc-on-hold', 'wc-pending'],
			'return' => 'ids',
			'date_created' => $date
		];

		$args = wp_parse_args( $args, $defaults );

		return wc_get_orders($args);
	}

    public function get_orders_by_flow($args) {
		$date_after = new DateTime($args['after']);
		$date_before = new DateTime($args['before']);

		$date_between = $date_after->format(self::DATETIME_FORMAT) .
		                '...' .
		                $date_before->format(self::DATE_FORMAT);

		return $this->get_orders_by_date($date_between, $args['flow_id']);
	}

    public function get_order_per_flow_for_checkout(array $order_ids) {
		return array_filter($order_ids, function($id) {
			$step_id = get_post_meta($id, FLUXTORE_PREFIX . 'order_step', true);
			return fluxtore_get_step_tag($step_id) === 'Checkout';
		});
    }

    public function get_orders_by_day($day, $flow_id) {
	    return $this->get_orders_by_date($day, $flow_id);
    }

    public function get_orders_by_week($week_start, $week_end, $flow_id) {
        $date_between = $week_start->format(self::DATETIME_FORMAT) .
                        '...' .
                        $week_end->modify('-1 day')->format(self::DATE_FORMAT);

        return $this->get_orders_by_date($date_between, $flow_id);
    }

	public function get_orders_by_month($month_start, $month_end, $flow_id) {
		$date_between = $month_start->format(self::DATETIME_FORMAT) .
                        '...' .
                        $month_end->format(self::DATE_FORMAT);

		return $this->get_orders_by_date($date_between, $flow_id);
    }

    public function get_orders_by_quarter($quarter_start, $quarter_end, $flow_id) {
		$date_between = $quarter_start->format(self::DATETIME_FORMAT) .
		                '...' .
		                $quarter_end->format(self::DATE_FORMAT);

		return $this->get_orders_by_date($date_between, $flow_id);
    }

    public function get_orders_by_year($year_start, $year_end) {
		$date_between = $year_start->format(self::DATE_FORMAT) .
                        '...' .
                        $year_end->format(self::DATE_FORMAT);

		return $this->get_orders_by_date($date_between);
	}

	public function get_orders_total( $args) {
		$orders = $this->get_orders_by_flow($args);
		$total = 0;

		foreach ( $orders as $order_id ) {
			$total += floatval( get_post_meta( $order_id, '_order_total', true ) );
		}

		return $total;
		
	}

    //visits
	private function get_visitors_array($args) {
		if ($args['flow_id']) {
			$visitors = get_post_meta($args['flow_id'], FLUXTORE_PREFIX . 'step_visitors', true);
		} else if ($args['step_id']) {
			$visitors = get_post_meta($args['step_id'], FLUXTORE_PREFIX . 'visitors', true);
		} else {
			$visitors = get_option('__fluxtore__visitors');
		}

		return $visitors;
	}

	protected function get_visits($args) {
		if (is_a($args['after'], DateTime::class)) {
			$dateAfter = $args['after'];
		} else {
			$dateAfter = new DateTime($args['after']);
		}
		if (is_a($args['before'], DateTime::class)) {
			$dateBefore = $args['before'];
		} else {
			$dateBefore = new DateTime($args['before']);
		}

		$visitors = $this->get_visitors_array($args);
		$total = 0;

		if (empty($visitors)) {
			return $total;
		}

		foreach ($visitors as $visitor_date) {
			if (is_array($visitor_date) && empty($visitor_date)) {
				continue;
			}

			$date = new DateTime($visitor_date);

			if (is_null($dateBefore)) {
				if ($date->format(self::DATE_FORMAT) === $dateAfter->format(self::DATE_FORMAT)) {
					$total++;
				}
			} else if ($date >= $dateAfter && $date <= $dateBefore) {
				$total++;
			}
		}

		return $total;
	}

    public function get_flow_visits($args) {
        return $this->get_visits($args);
    }

    public function get_step_visits($args, $step_id) {
		$args['step_id'] = $step_id;
        return $this->get_visits($args);
    }

    public function get_flow_visits_for_chart($date_after, $date_before, $flow_id) {
		$args = [
			'after' => $date_after,
			'before' => $date_before,
			'flow_id' => $flow_id
		];

        return $this->get_visits($args);
    }

    public function get_flow_revenue($args) {
        $orders = $this->get_orders_by_flow($args);
        $total = 0;

		foreach ( $orders as $order_id ) {
            $order = wc_get_order($order_id);
			$total += (float) $order->get_subtotal();
		}

		return $total;
    }

    public function get_orders_revenue_by_orders($orders) {
        $total = 0;

		foreach ( $orders as $order_id ) {
            $order = wc_get_order($order_id);
			$total += (float) $order->get_subtotal();
		}

		return $total;
    }

    public function get_revenue_from_visits($args) {
        $total_revenue = $this->get_flow_revenue($args);
        $total_visits = $this->get_flow_visits($args);
        return ( (float) $total_revenue / ($total_visits === 0 ? 1 : (int) $total_visits ));
    }

    public function get_avg_order_value($args) {
        $total = $this->get_flow_revenue($args);
        $orders = $this->get_orders_by_flow($args);
        if(count($orders) > 0) {
            return ($total / count($orders));
        }
        return $total;
    }

    public static function reset_metrics_table_cache() {
        self::$metrics_table_cache = null;
    }

    public static function get_metrics_table_name() {
        global $wpdb;

        return $wpdb->prefix . 'fluxtore_funnel_metrics';
    }

    protected function metrics_table_exists() {
        if (null !== self::$metrics_table_cache) {
            return (bool) self::$metrics_table_cache;
        }

        global $wpdb;
        $table = self::get_metrics_table_name();
        $result = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));
        self::$metrics_table_cache = ($result === $table);

        return (bool) self::$metrics_table_cache;
    }

    protected function query_metrics_rows($flow_id, DateTime $after, DateTime $before) {
        if (!$this->metrics_table_exists()) {
            return [];
        }

        global $wpdb;
        $table = self::get_metrics_table_name();

        $sql = $wpdb->prepare(
            "SELECT step_id, SUM(visits) AS visits, SUM(conversions) AS conversions, SUM(revenue) AS revenue, MAX(product_id) AS product_id
            FROM {$table}
            WHERE flow_id = %d AND metric_date BETWEEN %s AND %s
            GROUP BY step_id",
            $flow_id,
            $after->format('Y-m-d'),
            $before->format('Y-m-d')
        );

        return $wpdb->get_results($sql, ARRAY_A);
    }

    protected function query_series_rows($flow_id, DateTime $after, DateTime $before) {
        if (!$this->metrics_table_exists()) {
            return [];
        }

        global $wpdb;
        $table = self::get_metrics_table_name();

        $sql = $wpdb->prepare(
            "SELECT metric_date, step_id, SUM(visits) AS visits, SUM(conversions) AS conversions
            FROM {$table}
            WHERE flow_id = %d AND metric_date BETWEEN %s AND %s
            GROUP BY metric_date, step_id
            ORDER BY metric_date ASC",
            $flow_id,
            $after->format('Y-m-d'),
            $before->format('Y-m-d')
        );

        return $wpdb->get_results($sql, ARRAY_A);
    }

    protected function get_metrics_for_range($flow_id, DateTime $after, DateTime $before, array $steps_meta) {
        $rows = $this->query_metrics_rows($flow_id, $after, $before);

        if (!empty($rows)) {
            $formatted = [];
            $stepIndex = [];
            foreach ($steps_meta as $meta) {
                $stepIndex[(int) $meta['id']] = $meta;
            }

            foreach ($rows as $row) {
                $stepId = (int) $row['step_id'];
                $formatted[$stepId] = [
                    'visits' => (int) ($row['visits'] ?? 0),
                    'conversions' => (int) ($row['conversions'] ?? 0),
                    'revenue' => (float) ($row['revenue'] ?? 0.0),
                    'product_id' => isset($stepIndex[$stepId]) ? (int) $stepIndex[$stepId]['product_id'] : (int) ($row['product_id'] ?? 0),
                ];
            }

            return $formatted;
        }

        return $this->collect_metrics_from_orders($flow_id, $after, $before, $steps_meta);
    }

    protected function collect_metrics_from_orders($flow_id, DateTime $after, DateTime $before, array $steps_meta) {
        $metrics = [];

        foreach ($steps_meta as $step) {
            $stepId = (int) $step['id'];
            $stepPost = get_post($stepId);

            if (!$stepPost) {
                continue;
            }

            $ordersArgs = [
                'after' => $after->format(self::DATE_FORMAT),
                'before' => $before->format(self::DATETIME_FORMAT),
                'flow_id' => $flow_id,
            ];

            $orders = $this->get_orders_by_step($stepPost, $ordersArgs);

            $visits = $this->get_step_visits([
                'after' => clone $after,
                'before' => clone $before,
            ], $stepId);

            $revenue = 0.0;
            foreach ($orders as $orderId) {
                $order = wc_get_order($orderId);
                if ($order) {
                    $revenue += (float) $order->get_subtotal();
                }
            }

            $metrics[$stepId] = [
                'visits' => (int) $visits,
                'conversions' => count($orders),
                'revenue' => $revenue,
                'product_id' => (int) ($step['product_id'] ?? 0),
            ];
        }

        return $metrics;
    }

    protected function build_series_from_orders($flow_id, DateTime $after, DateTime $before, array $steps_meta) {
        $series = [];
        $start = clone $after;
        $start->setTime(0, 0, 0);
        $end = clone $before;
        $end->setTime(0, 0, 0);
        $endInclusive = clone $end;
        $endInclusive->modify('+1 day');

        $period = new \DatePeriod($start, new DateInterval('P1D'), $endInclusive);

        foreach ($period as $day) {
            $dayStart = clone $day;
            $dayStart->setTime(0, 0, 0);
            $dayEnd = clone $day;
            $dayEnd->setTime(23, 59, 59);

            $daily = $this->collect_metrics_from_orders($flow_id, $dayStart, $dayEnd, $steps_meta);

            foreach ($daily as $stepId => $metric) {
                $series[] = [
                    'metric_date' => $dayStart->format('Y-m-d'),
                    'step_id' => $stepId,
                    'visits' => (int) ($metric['visits'] ?? 0),
                    'conversions' => (int) ($metric['conversions'] ?? 0),
                ];
            }
        }

        return $series;
    }

    protected function get_metrics_series($flow_id, DateTime $after, DateTime $before, array $steps_meta) {
        $rows = $this->query_series_rows($flow_id, $after, $before);

        if (!empty($rows)) {
            return $rows;
        }

        return $this->build_series_from_orders($flow_id, $after, $before, $steps_meta);
    }

    public function collect_metrics_snapshot($flow_id, DateTime $after, DateTime $before) {
        $steps = $this->get_flow_steps_meta($flow_id);

        if (empty($steps)) {
            return [];
        }

        return $this->collect_metrics_from_orders($flow_id, $after, $before, $steps);
    }

    protected function get_previous_period_bounds(DateTime $after, DateTime $before) {
        $rangeDays = max(0, (int) $before->diff($after)->format('%a')) + 1;

        $previousEnd = clone $after;
        $previousEnd->modify('-1 day');
        $previousEnd->setTime(23, 59, 59);

        $previousStart = clone $previousEnd;
        $previousStart->modify(sprintf('-%d days', $rangeDays - 1));
        $previousStart->setTime(0, 0, 0);

        return [$previousStart, $previousEnd];
    }

    protected function resolve_product_name($product_id) {
        $product_id = (int) $product_id;

        if ($product_id <= 0) {
            return __('Unassigned product', 'fluxtore');
        }

        if (function_exists('wc_get_product')) {
            $product = wc_get_product($product_id);
            if ($product && !is_wp_error($product)) {
                return $product->get_name();
            }
        }

        $title = get_the_title($product_id);
        if (!empty($title)) {
            return $title;
        }

        return sprintf(__('Product #%d', 'fluxtore'), $product_id);
    }

    public function get_flow_steps_meta($flow_id) {
        $step_ids = get_post_meta($flow_id, FLUXTORE_PREFIX . 'steps', true);

        if (!is_array($step_ids)) {
            $step_ids = [];
        }

        $steps = [];

        foreach ($step_ids as $step_id) {
            $post = get_post($step_id);
            if (!$post) {
                continue;
            }

            $steps[] = [
                'id' => (int) $step_id,
                'title' => $post->post_title,
                'tag' => get_post_meta($step_id, FLUXTORE_PREFIX . 'tag', true),
                'order' => (int) get_post_meta($step_id, FLUXTORE_PREFIX . 'order', true),
                'product_id' => (int) get_post_meta($step_id, FLUXTORE_PREFIX . 'product', true),
            ];
        }

        usort($steps, static function ($a, $b) {
            return $a['order'] <=> $b['order'];
        });

        foreach ($steps as $index => &$step) {
            $step['position'] = $index;
        }

        return $steps;
    }

    public function get_funnel_conversion_summary($flow_id, DateTime $after, DateTime $before, $interval) {
        $steps = $this->get_flow_steps_meta($flow_id);
        if (empty($steps)) {
            return [
                'dropoff' => [],
                'timeline' => [],
                'products' => [],
                'insights' => [],
                'steps' => [],
            ];
        }

        $metrics = $this->get_metrics_for_range($flow_id, $after, $before, $steps);
        $seriesRows = $this->get_metrics_series($flow_id, $after, $before, $steps);

        [$prevAfter, $prevBefore] = $this->get_previous_period_bounds($after, $before);
        $previousMetrics = $this->get_metrics_for_range($flow_id, $prevAfter, $prevBefore, $steps);

        $products = $this->summarise_products($metrics, $steps, function ($productId) {
            return $this->resolve_product_name($productId);
        });

        return [
            'dropoff' => $this->build_dropoff_rows($steps, $metrics),
            'timeline' => $this->build_timeline_rows($seriesRows, $steps, $interval),
            'products' => $products,
            'insights' => $this->generate_insights($metrics, $previousMetrics, $steps, $products),
            'steps' => $steps,
        ];
    }

    public function get_cohort_matrix_summary($flow_id, DateTime $after, DateTime $before) {
        $steps = $this->get_flow_steps_meta($flow_id);
        if (empty($steps)) {
            return [];
        }

        $seriesRows = $this->get_metrics_series($flow_id, $after, $before, $steps);
        return $this->build_cohort_matrix($seriesRows, $steps);
    }

    public function get_product_performance_summary($flow_id, DateTime $after, DateTime $before) {
        $steps = $this->get_flow_steps_meta($flow_id);
        if (empty($steps)) {
            return [];
        }

        $metrics = $this->get_metrics_for_range($flow_id, $after, $before, $steps);

        return $this->summarise_products($metrics, $steps, function ($productId) {
            return $this->resolve_product_name($productId);
        });
    }

    public function get_flow_conversion_rate_from_range($args) {
        $flow_id = isset($args['flow_id']) ? (int) $args['flow_id'] : 0;
        if ($flow_id <= 0) {
            return 0;
        }

        $after = isset($args['after']) ? new DateTime($args['after']) : new DateTime('now -30 days');
        $before = isset($args['before']) ? new DateTime($args['before']) : new DateTime('now');

        $steps = $this->get_flow_steps_meta($flow_id);
        if (empty($steps)) {
            return 0;
        }

        $metrics = $this->get_metrics_for_range($flow_id, $after, $before, $steps);

        $firstStep = $steps[0]['id'];
        $lastStep = $steps[count($steps) - 1]['id'];

        $baseline = $metrics[$firstStep]['visits'] ?? 0;
        $finalConversions = $metrics[$lastStep]['conversions'] ?? 0;

        if ($baseline <= 0) {
            return 0;
        }

        return max(0, min(1, $finalConversions / $baseline));
    }
}
