<?php
/**
 * Aggregate events into wmk_stats_daily once per day and serve dashboards.
 *
 * @package SuiteMkt3k
 */

namespace SuiteMkt3k\Modules\Statistics;

use SuiteMkt3k\Installer;
use SuiteMkt3k\Modules\Subscribers\Subscriber_Manager;

defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

final class Stats_Aggregator {

	public static function register(): void {
		add_action( 'suitemkt3k_cron_daily', array( __CLASS__, 'rollup' ) );
	}

	public static function rollup(): void {
		global $wpdb;
		$events = Installer::table( 'events' );
		$daily  = Installer::table( 'stats_daily' );
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO %i (day, entity_type, entity_id, impressions, clicks, conversions, opens, revenue)
			 SELECT DATE(created_at) AS day, entity_type, COALESCE(entity_id,0) AS entity_id,
			   SUM(event_type='impression') AS impressions,
			   SUM(event_type='click') AS clicks,
			   SUM(event_type='conversion') AS conversions,
			   SUM(event_type='open') AS opens,
			   0 AS revenue
			 FROM %i
			 WHERE DATE(created_at) = CURDATE() - INTERVAL 1 DAY
			 GROUP BY day, entity_type, entity_id
			 ON DUPLICATE KEY UPDATE impressions=VALUES(impressions), clicks=VALUES(clicks), conversions=VALUES(conversions), opens=VALUES(opens)",
				$daily,
				$events
			)
		);
	}

	public static function overview( string $range = '30d', string $category = 'all', int $entity_id = 0 ): array {
		global $wpdb;
		$days = self::range_days( $range );

		$events = Installer::table( 'events' );
		$subs   = Installer::table( 'subscribers' );
		$news   = Installer::table( 'newsletters' );
		$carts  = Installer::table( 'abandoned_carts' );

		$since = 0 === $days ? '' : gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );

		// Entity filter: optionally narrow to a specific category.
		$entity_type = '';
		switch ( $category ) {
			case 'coupons':     $entity_type = 'coupon'; break;
			case 'newsletters': $entity_type = 'newsletter'; break;
			case 'cart':        $entity_type = 'cart'; break;
		}

		$count_event = static function ( string $type, ?string $forced_entity_type = null ) use ( $wpdb, $events, $entity_type, $entity_id, $since ) {
			$effective_entity = null !== $forced_entity_type ? $forced_entity_type : $entity_type;
			$effective_id     = $entity_id > 0 ? $entity_id : 0;
			return (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM %i
					WHERE event_type = %s
					AND (%s = '' OR created_at >= %s)
					AND (%s = '' OR entity_type = %s)
					AND (%d = 0 OR entity_id = %d)",
					$events,
					$type,
					$since,
					$since,
					$effective_entity,
					$effective_entity,
					$effective_id,
					$effective_id
				)
			);
		};

		$kpi_impr   = $count_event( 'impression' );
		$kpi_clk    = $count_event( 'click' );
		$kpi_open   = $count_event( 'open' );
		$kpi_conv   = $count_event( 'conversion' );
		$kpi_sent   = $count_event( 'sent' );
		$kpi_coupon_impr = $count_event( 'impression', 'coupon' );
		$kpi_coupon_clk  = $count_event( 'click', 'coupon' );
		$kpi_coupon_conv = $count_event( 'conversion', 'coupon' );
		$kpi_news_impr   = $count_event( 'impression', 'newsletter' );
		$kpi_news_open   = $count_event( 'open', 'newsletter' );
		$kpi_news_clk    = $count_event( 'click', 'newsletter' );
		$kpi_news_sent   = $count_event( 'sent', 'newsletter' );
		$kpi_cart_sent   = $count_event( 'sent', 'cart' );
		$kpi_cart_open   = $count_event( 'open', 'cart' );
		$kpi_cart_clk    = $count_event( 'click', 'cart' );
		$kpi_cart_conv   = $count_event( 'conversion', 'cart' );

		$kpi_subs = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i
				WHERE status = %s
				AND (%s = '' OR subscribed_at >= %s)",
				$subs,
				'active',
				$since,
				$since
			)
		);

		$kpi_total_subs = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status='active'", $subs ) );

		$kpi_unsubscribed = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i
				WHERE status = %s
				AND unsubscribed_at IS NOT NULL
				AND (%s = '' OR unsubscribed_at >= %s)",
				$subs,
				Subscriber_Manager::STATUS_UNSUBSCRIBED,
				$since,
				$since
			)
		);

		$kpi_recovered = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i
				WHERE status = %s
				AND (%s = '' OR recovered_at >= %s)",
				$carts,
				'recovered',
				$since,
				$since
			)
		);
		$kpi_carts_captured = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i
				WHERE status != %s
				AND (%s = '' OR recovered_at >= %s)",
				$carts,
				'deleted',
				$since,
				$since
			)
		);
		$kpi_carts_pending = (int) $wpdb->get_var(
			$wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE status = %s', $carts, 'pending' )
		);
		$kpi_carts_emailed = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i
				WHERE status = %s
				AND (%s = '' OR recovered_at >= %s)",
				$carts,
				'emailed',
				$since,
				$since
			)
		);

		$revenue_recovered = self::sum_recovered_revenue( $since );

		$series = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(created_at) AS d,
			 SUM(event_type='impression') AS impressions,
			 SUM(event_type='click') AS clicks,
			 SUM(event_type='open') AS opens,
			 SUM(event_type='conversion') AS conversions
			 FROM %i
			 WHERE (%s = '' OR created_at >= %s)
			 AND (%s = '' OR entity_type = %s)
			 AND (%d = 0 OR entity_id = %d)
			 GROUP BY DATE(created_at)
			 ORDER BY d ASC",
				$events,
				$since,
				$since,
				$entity_type,
				$entity_type,
				$entity_id,
				$entity_id
			),
			ARRAY_A
		);

		// Optional per-entity breakdown table.
		$entities = array();
		if ( 'coupons' === $category ) {
			$coupons_table = Installer::table( 'coupons' );
			$entities = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT c.id, c.name, c.usage_count,
					(SELECT COUNT(*) FROM %i WHERE entity_type='coupon' AND entity_id=c.id AND event_type='impression' AND (%s = '' OR created_at >= %s)) AS impressions,
					(SELECT COUNT(*) FROM %i WHERE entity_type='coupon' AND entity_id=c.id AND event_type='click' AND (%s = '' OR created_at >= %s)) AS clicks,
					(SELECT COUNT(*) FROM %i WHERE entity_type='coupon' AND entity_id=c.id AND event_type='conversion' AND (%s = '' OR created_at >= %s)) AS conversions
					FROM %i c ORDER BY c.id DESC LIMIT 100",
					$events,
					$since,
					$since,
					$events,
					$since,
					$since,
					$events,
					$since,
					$since,
					$coupons_table
				),
				ARRAY_A
			);
		} elseif ( 'newsletters' === $category ) {
			$news_table = Installer::table( 'newsletters' );
			$entities = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT n.id, n.name,
					(SELECT COUNT(*) FROM %i WHERE entity_type='newsletter' AND entity_id=n.id AND event_type='impression' AND (%s = '' OR created_at >= %s)) AS impressions,
					(SELECT COUNT(*) FROM %i WHERE entity_type='newsletter' AND entity_id=n.id AND event_type='open' AND (%s = '' OR created_at >= %s)) AS opens,
					(SELECT COUNT(*) FROM %i WHERE entity_type='newsletter' AND entity_id=n.id AND event_type='click' AND (%s = '' OR created_at >= %s)) AS clicks
					FROM %i n ORDER BY n.id DESC LIMIT 100",
					$events,
					$since,
					$since,
					$events,
					$since,
					$since,
					$events,
					$since,
					$since,
					$news_table
				),
				ARRAY_A
			);
		}

		return array(
			'kpis' => array(
				'new_subscribers'     => $kpi_subs,
				'total_subscribers'   => $kpi_total_subs,
				'unsubscribed_users'  => $kpi_unsubscribed,
				'impressions'         => $kpi_impr,
				'clicks'              => $kpi_clk,
				'opens'               => $kpi_open,
				'conversions'         => $kpi_conv,
				'emails_sent'         => $kpi_sent,
				'carts_recovered'     => $kpi_recovered,
				'revenue_recovered'   => $revenue_recovered,
				'coupon_impressions'  => $kpi_coupon_impr,
				'coupon_clicks'       => $kpi_coupon_clk,
				'coupon_conversions'  => $kpi_coupon_conv,
				'newsletter_impressions' => $kpi_news_impr,
				'newsletter_opens'    => $kpi_news_open,
				'newsletter_clicks'   => $kpi_news_clk,
				'newsletter_sent'     => $kpi_news_sent,
				'cart_emails_sent'    => $kpi_cart_sent,
				'cart_email_opens'    => $kpi_cart_open,
				'cart_email_clicks'   => $kpi_cart_clk,
				'cart_conversions'    => $kpi_cart_conv,
				'carts_captured'      => $kpi_carts_captured,
				'carts_pending'       => $kpi_carts_pending,
				'carts_emailed'       => $kpi_carts_emailed,
			),
			'series'   => is_array( $series ) ? $series : array(),
			'entities' => is_array( $entities ) ? $entities : array(),
			'range'    => $range,
			'days'     => $days,
			'category' => $category,
		);
	}

	private static function range_days( string $range ): int {
		switch ( $range ) {
			case '7d':   return 7;
			case '90d':  return 90;
			case '180d': return 180;
			case 'total': return 0;
			default:     return 30;
		}
	}

	/**
	 * Sum recovered revenue using final WooCommerce order totals when available.
	 *
	 * Fallback to the captured cart total if the linked order cannot be loaded.
	 *
	 * @param string $since MySQL datetime filter or empty for total.
	 */
	private static function sum_recovered_revenue( string $since ): float {
		global $wpdb;
		$carts = Installer::table( 'abandoned_carts' );
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT recovered_order_id, total FROM %i
				WHERE status = 'recovered'
				AND (%s = '' OR recovered_at >= %s)",
				$carts,
				$since,
				$since
			),
			ARRAY_A
		);
		if ( ! is_array( $rows ) || ! $rows ) {
			return 0.0;
		}

		$sum = 0.0;
		foreach ( $rows as $row ) {
			$order_id = isset( $row['recovered_order_id'] ) ? (int) $row['recovered_order_id'] : 0;
			if ( $order_id > 0 && function_exists( 'wc_get_order' ) ) {
				$order = wc_get_order( $order_id );
				if ( $order ) {
					$sum += (float) $order->get_total();
					continue;
				}
			}
			$sum += isset( $row['total'] ) ? (float) $row['total'] : 0.0;
		}

		return $sum;
	}
}
