<?php
/**
 * ConversionTracker — attributes WooCommerce orders to CartCue plays.
 *
 * @package CartCue
 */

namespace CartCue;

defined( 'ABSPATH' ) || exit;

final class ConversionTracker {

	/** Attribution window in days. */
	public const WINDOW_DAYS = 14;

	public static function init(): void {
		add_action( 'woocommerce_payment_complete',       [ __CLASS__, 'handle' ], 20, 1 );
		add_action( 'woocommerce_order_status_completed', [ __CLASS__, 'handle' ], 20, 1 );
		add_action( 'woocommerce_applied_coupon',         [ __CLASS__, 'store_coupon_attribution' ], 10, 1 );
		add_action( 'woocommerce_checkout_order_processed', [ __CLASS__, 'apply_coupon_attribution' ], 20, 1 );
	}

	public static function handle( int $order_id ): void {
		self::record( $order_id );
	}

	public static function store_coupon_attribution( string $coupon_code ): void {
		if ( ! function_exists( 'WC' ) || ! WC()->session ) {
			return;
		}
		$coupon = new \WC_Coupon( $coupon_code );
		if ( ! $coupon->get_id() ) {
			return;
		}
		$workflow_id = (int) $coupon->get_meta( '_cartcue_workflow_id' );
		if ( ! $workflow_id ) {
			$workflow_id = (int) $coupon->get_meta( '_wooautomate_workflow_id' );
		}
		if ( $workflow_id ) {
			WC()->session->set( 'cartcue_attributed_workflow_id', $workflow_id );
		}
	}

	public static function apply_coupon_attribution( int $order_id ): void {
		if ( ! function_exists( 'WC' ) || ! WC()->session ) {
			return;
		}
		$workflow_id = (int) WC()->session->get( 'cartcue_attributed_workflow_id' );
		if ( ! $workflow_id ) {
			return;
		}
		self::record( $order_id, $workflow_id );
		WC()->session->__unset( 'cartcue_attributed_workflow_id' );
	}

	private static function record( int $order_id, int $forced_workflow_id = 0 ): void {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$existing = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT id FROM %i WHERE order_id = %d LIMIT 1',
				DB::conversions(),
				$order_id
			)
		);
		if ( $existing ) {
			return;
		}

		$order = wc_get_order( $order_id );
		if ( ! $order ) {
			return;
		}

		$email       = $order->get_billing_email();
		$window_days = (int) apply_filters( 'cartcue_conversion_window_days', self::WINDOW_DAYS );
		$workflow_id = $forced_workflow_id ?: EmailTracker::find_recent_workflow( $email, $window_days );

		if ( ! $workflow_id ) {
			return;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$wpdb->insert(
			DB::conversions(),
			[
				'workflow_id'    => $workflow_id,
				'customer_email' => sanitize_email( $email ),
				'order_id'       => $order_id,
				'order_total'    => (float) $order->get_total(),
				'converted_at'   => current_time( 'mysql' ),
			],
			[ '%d', '%s', '%d', '%f', '%s' ]
		);
	}

	public static function get_revenue( int $days = 30 ): float {
		global $wpdb;

		if ( $days > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$total = $wpdb->get_var(
				$wpdb->prepare(
					'SELECT SUM(order_total) FROM %i WHERE converted_at >= DATE_SUB(NOW(), INTERVAL %d DAY)',
					DB::conversions(),
					$days
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$total = $wpdb->get_var(
				$wpdb->prepare(
					'SELECT SUM(order_total) FROM %i',
					DB::conversions()
				)
			);
		}

		return (float) ( $total ?? 0 );
	}

	public static function get_revenue_by_play( int $limit = 10, int $days = 30 ): array {
		global $wpdb;

		if ( $days > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery
			return $wpdb->get_results(
				$wpdb->prepare(
					'SELECT c.workflow_id, w.name AS play_name,
					        SUM(c.order_total) AS revenue,
					        COUNT(c.id) AS orders
					 FROM %i c
					 LEFT JOIN %i w ON w.id = c.workflow_id
					 WHERE c.converted_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
					 GROUP BY c.workflow_id
					 ORDER BY revenue DESC
					 LIMIT %d',
					DB::conversions(),
					DB::workflows(),
					$days,
					$limit
				),
				ARRAY_A
			) ?: [];
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT c.workflow_id, w.name AS play_name,
				        SUM(c.order_total) AS revenue,
				        COUNT(c.id) AS orders
				 FROM %i c
				 LEFT JOIN %i w ON w.id = c.workflow_id
				 GROUP BY c.workflow_id
				 ORDER BY revenue DESC
				 LIMIT %d',
				DB::conversions(),
				DB::workflows(),
				$limit
			),
			ARRAY_A
		) ?: [];
	}

	public static function get_revenue_by_plays( array $ids ): array {
		$ids = array_values( array_filter( array_map( 'absint', $ids ) ) );
		if ( empty( $ids ) ) {
			return [];
		}

		global $wpdb;
		$ids          = array_map( 'absint', $ids );
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT workflow_id, SUM(order_total) AS revenue, COUNT(id) AS orders
				 FROM %i WHERE workflow_id IN ($placeholders)
				 GROUP BY workflow_id",
				array_merge( [ DB::conversions() ], $ids )
			),
			ARRAY_A
		) ?: [];
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

		return $results;
	}
}
