<?php
namespace ArrayFunnel\Features\Analytics\Services;

defined( 'ABSPATH' ) || exit;

/**
 * Reads the locally stored conversion events.
 *
 * Every result is cached in a transient, so the direct queries below run at most once per
 * cache window rather than on each dashboard render.
 */
final class AnalyticsQuery {

    private const OVERVIEW_CACHE_TTL = 10 * MINUTE_IN_SECONDS;
    private const REVENUE_CACHE_TTL  = 10 * MINUTE_IN_SECONDS;
    private const EVENTS_CACHE_TTL   = 2 * MINUTE_IN_SECONDS;

    private function normalizeDays( int $days ): int {
        return max( 1, $days );
    }

    private function getDateFrom( int $days = 7 ): string {
        $days = $this->normalizeDays( $days );
        return gmdate( 'Y-m-d 00:00:00', strtotime( "-{$days} days" ) );
    }

    public function getOverview( int $days = 7 ): array {
        global $wpdb;

        $days = $this->normalizeDays( $days );
        $date_from = $this->getDateFrom( $days );
        $cache_key = "arrayfunnel_overview_{$days}";
        $cached    = get_transient( $cache_key );

        if ( false !== $cached ) {
            return $cached;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; the result is cached in a transient immediately below.
        $totals = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT
                    SUM(CASE WHEN event_type = %s THEN revenue ELSE 0 END) AS revenue,
                    SUM(CASE WHEN event_type = %s THEN 1 ELSE 0 END) AS orders,
                    SUM(CASE WHEN event_type = %s THEN 1 ELSE 0 END) AS bump_impressions,
                    SUM(CASE WHEN event_type IN (%s, %s) THEN 1 ELSE 0 END) AS bump_accepted,
                    SUM(CASE WHEN event_type = %s THEN 1 ELSE 0 END) AS checkout_views
                 FROM {$wpdb->prefix}arrayfunnel_analytics
                 WHERE created_at >= %s
                   AND event_type IN (%s, %s, %s, %s, %s)",
                'order_completed',
                'order_completed',
                'bump_impression',
                'bump_accepted',
                'bump_converted',
                'checkout_view',
                $date_from,
                'order_completed',
                'bump_impression',
                'bump_accepted',
                'bump_converted',
                'checkout_view'
            )
        );

        $revenue          = (float) ( $totals->revenue ?? 0 );
        $orders           = (int) ( $totals->orders ?? 0 );
        $bump_impressions = (int) ( $totals->bump_impressions ?? 0 );
        $bump_accepted    = (int) ( $totals->bump_accepted ?? 0 );
        $checkout_views   = (int) ( $totals->checkout_views ?? 0 );
        $aov              = $orders > 0 ? round( $revenue / $orders, 2 ) : 0;
        $bump_rate        = $bump_impressions > 0 ? round( ( $bump_accepted / $bump_impressions ) * 100, 1 ) : 0;

        // Conversion rate
        $conversion_rate = $checkout_views > 0
            ? round( ( $orders / $checkout_views ) * 100, 1 )
            : 0;

        // Abandoned cart stats
        $abandon_stats = ( new \ArrayFunnel\Features\AbandonedCart\Services\CartTracker( false ) )->getStats();

        $data = [
            'revenue'            => $revenue,
            'orders'             => $orders,
            'aov'                => $aov,
            'bump_impressions'   => $bump_impressions,
            'bump_accepted'      => $bump_accepted,
            'bump_rate'          => $bump_rate,
            'checkout_views'     => $checkout_views,
            'conversion_rate'    => $conversion_rate,
            'abandoned_carts'    => $abandon_stats['total_abandoned'],
            'recovered_carts'    => $abandon_stats['total_recovered'],
            'recovered_revenue'  => $abandon_stats['revenue_recovered'],
            'days'               => $days,
        ];

        set_transient( $cache_key, $data, self::OVERVIEW_CACHE_TTL );
        return $data;
    }

    public function getRevenueChart( int $days = 7 ): array {
        global $wpdb;

        $days = $this->normalizeDays( $days );
        $date_from = $this->getDateFrom( $days );
        $cache_key = "arrayfunnel_revenue_chart_{$days}";
        $cached    = get_transient( $cache_key );

        if ( false !== $cached ) {
            return $cached;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; the result is cached in a transient immediately below.
        $results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT
                    DATE(created_at) AS date,
                    SUM(CASE WHEN event_type = %s THEN revenue ELSE 0 END) AS revenue,
                    SUM(CASE WHEN event_type = %s THEN 1 ELSE 0 END) AS orders,
                    SUM(CASE WHEN event_type = %s THEN 1 ELSE 0 END) AS checkout_views,
                    SUM(CASE WHEN event_type = %s THEN 1 ELSE 0 END) AS bump_impressions,
                    SUM(CASE WHEN event_type IN (%s, %s) THEN 1 ELSE 0 END) AS bump_accepted
                 FROM {$wpdb->prefix}arrayfunnel_analytics
                 WHERE created_at >= %s
                   AND event_type IN (%s, %s, %s, %s, %s)
                 GROUP BY DATE(created_at)
                 ORDER BY date ASC",
                'order_completed',
                'order_completed',
                'checkout_view',
                'bump_impression',
                'bump_accepted',
                'bump_converted',
                $date_from,
                'order_completed',
                'checkout_view',
                'bump_impression',
                'bump_accepted',
                'bump_converted'
            )
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; the result is cached in a transient immediately below.
        $recovered_results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT
                    DATE(recovered_at) AS date,
                    COUNT(*) AS recovered_carts
                 FROM {$wpdb->prefix}arrayfunnel_abandoned_carts
                 WHERE recovered_at IS NOT NULL
                   AND recovered_at >= %s
                   AND status = %s
                 GROUP BY DATE(recovered_at)
                 ORDER BY date ASC",
                $date_from,
                'recovered'
            )
        );

        // Fill in missing days
        $chart_data = [];
        for ( $i = $days - 1; $i >= 0; $i-- ) {
            $date = gmdate( 'Y-m-d', strtotime( "-{$i} days" ) );
            $chart_data[ $date ] = [
                'date'             => $date,
                'label'            => gmdate( 'M j', strtotime( $date ) ),
                'revenue'          => 0,
                'orders'           => 0,
                'checkout_views'   => 0,
                'bump_impressions' => 0,
                'bump_accepted'    => 0,
                'recovered_carts'  => 0,
            ];
        }

        foreach ( $results as $row ) {
            if ( isset( $chart_data[ $row->date ] ) ) {
                $chart_data[ $row->date ]['revenue']          = (float) $row->revenue;
                $chart_data[ $row->date ]['orders']           = (int) $row->orders;
                $chart_data[ $row->date ]['checkout_views']   = (int) $row->checkout_views;
                $chart_data[ $row->date ]['bump_impressions'] = (int) $row->bump_impressions;
                $chart_data[ $row->date ]['bump_accepted']    = (int) $row->bump_accepted;
            }
        }

        foreach ( $recovered_results as $row ) {
            if ( isset( $chart_data[ $row->date ] ) ) {
                $chart_data[ $row->date ]['recovered_carts'] = (int) $row->recovered_carts;
            }
        }

        $data = array_values( $chart_data );
        set_transient( $cache_key, $data, self::REVENUE_CACHE_TTL );
        return $data;
    }

    public function getEventsLog( int $limit = 50, int $offset = 0, int $days = 7 ): array {
        global $wpdb;

        $days      = $this->normalizeDays( $days );
        $date_from = $this->getDateFrom( $days );
        $cache_key = "arrayfunnel_events_log_{$days}_{$limit}_{$offset}";
        $cached    = get_transient( $cache_key );

        if ( false !== $cached ) {
            return $cached;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; the result is cached in a transient immediately below.
        $events = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, event_type, order_id, revenue, created_at
                 FROM {$wpdb->prefix}arrayfunnel_analytics
                 WHERE created_at >= %s
                 ORDER BY created_at DESC
                 LIMIT %d OFFSET %d",
                $date_from, $limit, $offset
            )
        );

        set_transient( $cache_key, $events, self::EVENTS_CACHE_TTL );
        return $events;
    }
}