<?php

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

require_once __DIR__ . '/class-aieo-order-store.php';

class AIEO_Workflow_Incrementality {

	const DEFAULT_WINDOW = 14; 
	const MAX_RECIPIENTS = 50000;

	
	public static function compute( $wf_id, $window_days = self::DEFAULT_WINDOW ) {
		global $wpdb;
		$wf_id   = (int) $wf_id;
		$window  = max( 1, min( 90, (int) $window_days ) );
		$trigger = 'wf_' . $wf_id;
		$disp    = $wpdb->prefix . 'aieo_push_dispatch';

		$blank = array(
			'available'   => false,
			'window_days' => $window,
			'note'        => __( 'No holdout data yet. Set a holdout % on the workflow and let it run — a control arm is needed to measure incrementality.', 'ai-eshop-optimizer' ),
		);
		if ( ! self::table_exists( $disp ) ) {
			return $blank;
		}
		
		
		$floor  = (string) $wpdb->get_var( $wpdb->prepare( "SELECT MIN(created_at) FROM `{$disp}` WHERE trigger_id = %s", $trigger ) );
		$orders = self::orders_subquery( $floor );
		if ( '' === $orders ) {
			$blank['note'] = __( 'No WooCommerce order store found to measure conversions against.', 'ai-eshop-optimizer' );
			return $blank;
		}

		$max = (int) self::MAX_RECIPIENTS;
		
		
		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT r.arm AS arm, COUNT(*) AS n, SUM(r.converted) AS conv, SUM(r.rev) AS rev
			 FROM (
				SELECT d.arm AS arm, d.rk AS rk,
				       MAX(CASE WHEN o.id IS NOT NULL THEN 1 ELSE 0 END) AS converted,
				       SUM(COALESCE(o.total_amount, 0)) AS rev
				FROM (
					SELECT CASE WHEN channel = 'holdout' THEN 'hold' ELSE 'treat' END AS arm,
					       CASE WHEN user_id > 0 THEN CONCAT('u', user_id) ELSE CONCAT('e', recipient_email) END AS rk,
					       MIN(user_id) AS uid, MIN(recipient_email) AS em, MIN(created_at) AS t0
					FROM `{$disp}`
					WHERE trigger_id = %s
					  AND ( state = 'holdout' OR state IN ('sent','clicked','converted','dismissed') )
					GROUP BY arm, rk
					LIMIT {$max}
				) d
				LEFT JOIN {$orders} o
					ON ( ( d.uid > 0 AND o.customer_id = d.uid )
					     OR ( d.em <> '' AND o.billing_email = d.em COLLATE utf8mb4_general_ci ) )
					   AND o.date_created_gmt >= d.t0
					   AND o.date_created_gmt < ( d.t0 + INTERVAL %d DAY )
				GROUP BY d.arm, d.rk
			 ) r
			 GROUP BY r.arm",
			$trigger,
			$window
		), ARRAY_A );

		$arm = array( 'treat' => array( 'n' => 0, 'conv' => 0, 'rev' => 0.0 ), 'hold' => array( 'n' => 0, 'conv' => 0, 'rev' => 0.0 ) );
		foreach ( (array) $rows as $r ) {
			$k = ( 'hold' === $r['arm'] ) ? 'hold' : 'treat';
			$arm[ $k ] = array( 'n' => (int) $r['n'], 'conv' => (int) $r['conv'], 'rev' => (float) $r['rev'] );
		}
		$nt = $arm['treat']['n'];
		$nh = $arm['hold']['n'];
		if ( $nt <= 0 || $nh <= 0 ) {
			$blank['treatment_n'] = $nt;
			$blank['holdout_n']   = $nh;
			return $blank;
		}

		$crt = $arm['treat']['conv'] / $nt;
		$crh = $arm['hold']['conv'] / $nh;
		$rprt = $arm['treat']['rev'] / $nt;
		$rprh = $arm['hold']['rev'] / $nh;
		$lift_abs = $crt - $crh;                              
		$lift_rel = $crh > 0 ? ( ( $crt - $crh ) / $crh ) : null;
		$incr_conv = (int) round( $lift_abs * $nt );          
		$incr_rev  = ( $rprt - $rprh ) * $nt;                 
		$z = self::ztest( $arm['treat']['conv'], $nt, $arm['hold']['conv'], $nh );

		return array(
			'available'               => true,
			'window_days'             => $window,
			'treatment'               => array(
				'n'                => $nt,
				'conversions'      => $arm['treat']['conv'],
				'conv_rate_pct'    => round( 100 * $crt, 2 ),
				'revenue'          => round( $arm['treat']['rev'], 2 ),
				'rev_per_recipient' => round( $rprt, 2 ),
			),
			'holdout'                 => array(
				'n'                => $nh,
				'conversions'      => $arm['hold']['conv'],
				'conv_rate_pct'    => round( 100 * $crh, 2 ),
				'revenue'          => round( $arm['hold']['rev'], 2 ),
				'rev_per_recipient' => round( $rprh, 2 ),
			),
			'lift_pct_points'         => round( 100 * $lift_abs, 2 ),
			'relative_lift_pct'       => ( null === $lift_rel ) ? null : round( 100 * $lift_rel, 1 ),
			'incremental_conversions' => $incr_conv,
			'incremental_revenue'     => round( $incr_rev, 2 ),
			'z_score'                 => round( $z, 2 ),
			'significant'             => abs( $z ) >= 1.96,   
			'note'                    => __( 'Incremental = treated minus held-out. A positive, significant lift (|z|≥1.96) means the campaign CAUSED conversions the control group did not reach on its own. Conversions = paid orders within the window, matched by customer or billing email.', 'ai-eshop-optimizer' ),
		);
	}

	
	private static function orders_subquery( $floor = '' ) {
		global $wpdb;
		$hpos = $wpdb->prefix . 'wc_orders';
		if ( AIEO_Order_Store::uses_hpos() && self::table_exists( $hpos ) ) {
			$fl = ( '' !== $floor ) ? " AND date_created_gmt >= '" . esc_sql( $floor ) . "'" : '';
			return "(SELECT id, customer_id, billing_email, date_created_gmt, total_amount
			         FROM `{$hpos}`
			         WHERE type = 'shop_order' AND status IN ('wc-completed','wc-processing'){$fl})";
		}
		
		$posts = $wpdb->posts;
		$meta  = $wpdb->postmeta;
		if ( ! self::table_exists( $posts ) ) {
			return '';
		}
		$fl = ( '' !== $floor ) ? " AND p.post_date_gmt >= '" . esc_sql( $floor ) . "'" : '';
		return "(SELECT p.ID AS id,
		                CAST(COALESCE(cu.meta_value, 0) AS UNSIGNED) AS customer_id,
		                COALESCE(be.meta_value, '') AS billing_email,
		                p.post_date_gmt AS date_created_gmt,
		                CAST(COALESCE(tot.meta_value, 0) AS DECIMAL(14,2)) AS total_amount
		         FROM `{$posts}` p
		         LEFT JOIN `{$meta}` cu  ON cu.post_id = p.ID AND cu.meta_key = '_customer_user'
		         LEFT JOIN `{$meta}` be  ON be.post_id = p.ID AND be.meta_key = '_billing_email'
		         LEFT JOIN `{$meta}` tot ON tot.post_id = p.ID AND tot.meta_key = '_order_total'
		         WHERE p.post_type = 'shop_order' AND p.post_status IN ('wc-completed','wc-processing'){$fl})";
	}

	
	private static function ztest( $c1, $n1, $c2, $n2 ) {
		$n1 = (int) $n1;
		$n2 = (int) $n2;
		if ( $n1 <= 0 || $n2 <= 0 ) {
			return 0.0;
		}
		$p1 = $c1 / $n1;
		$p2 = $c2 / $n2;
		$p  = ( $c1 + $c2 ) / ( $n1 + $n2 );
		$se = sqrt( $p * ( 1 - $p ) * ( 1 / $n1 + 1 / $n2 ) );
		return $se > 0 ? ( ( $p1 - $p2 ) / $se ) : 0.0;
	}

	private static function table_exists( $t ) {
		global $wpdb;
		return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) === $t;
	}
}