<?php

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

class AIEO_DMM_ROI_Wizard extends AIEO_DMM_Module_Base {

	const NONCE = 'aieo_roi_wizard';
	const CAP   = 'manage_woocommerce';

	
	const LEDGER_DIMS = array( 'channel', 'campaign', 'creative' );

	public function get_id() {
		return 'roi_wizard';
	}

	public function get_label() {
		return __( 'ROI Wizard', 'ai-eshop-optimizer' );
	}

	public function get_group() {
		return 'attribution';
	}

	public function get_priority() {
		return 15;
	}

	
	private $utm = null;

	private function utm() {
		if ( null === $this->utm && class_exists( 'AIEO_DMM_UTM_Explorer' ) ) {
			$this->utm = new AIEO_DMM_UTM_Explorer();
		}
		return $this->utm;
	}

	public function register_hooks() {
		add_action( 'wp_ajax_aieo_roi_values', array( $this, 'ajax_values' ) );
		add_action( 'wp_ajax_aieo_roi_value_search', array( $this, 'ajax_value_search' ) );
		add_action( 'wp_ajax_aieo_roi_type_search', array( $this, 'ajax_type_search' ) );
		add_action( 'wp_ajax_aieo_roi_compute', array( $this, 'ajax_compute' ) );
		add_action( 'wp_ajax_aieo_roi_cost_save', array( $this, 'ajax_cost_save' ) );
		add_action( 'wp_ajax_aieo_roi_cost_delete', array( $this, 'ajax_cost_delete' ) );
		add_action( 'wp_ajax_aieo_roi_cost_import', array( $this, 'ajax_cost_import' ) );
		add_action( 'wp_ajax_aieo_roi_cost_list', array( $this, 'ajax_cost_list' ) );
		add_action( 'wp_ajax_aieo_roi_cost_export', array( $this, 'ajax_cost_export' ) );
		
		if ( $this->utm() ) {
			$this->utm()->register_hooks();
		}
	}

	

	private function guard() {
		check_ajax_referer( self::NONCE, 'nonce' );
		if ( ! current_user_can( self::CAP ) ) {
			wp_send_json_error( array( 'msg' => 'forbidden' ), 403 );
		}
	}

	
	private function dates() {
		$to   = isset( $_POST['to'] ) ? sanitize_text_field( wp_unslash( $_POST['to'] ) ) : '';
		$from = isset( $_POST['from'] ) ? sanitize_text_field( wp_unslash( $_POST['from'] ) ) : '';
		$d    = '/^\d{4}-\d{2}-\d{2}$/';
		if ( ! preg_match( $d, $to ) ) {
			$to = gmdate( 'Y-m-d' );
		}
		if ( ! preg_match( $d, $from ) ) {
			$from = gmdate( 'Y-m-d', strtotime( $to . ' -30 days' ) );
		}
		return array( $from, $to );
	}

	
	private function compare_dates( $from, $to, $mode ) {
		if ( 'prev' === $mode ) {
			$days = (int) round( ( strtotime( $to ) - strtotime( $from ) ) / 86400 ) + 1;
			return array( gmdate( 'Y-m-d', strtotime( $from . ' -' . $days . ' days' ) ), gmdate( 'Y-m-d', strtotime( $from . ' -1 day' ) ) );
		}
		if ( 'yoy' === $mode ) {
			return array( gmdate( 'Y-m-d', strtotime( $from . ' -1 year' ) ), gmdate( 'Y-m-d', strtotime( $to . ' -1 year' ) ) );
		}
		return array( '', '' );
	}

	private function ledger_dim_for( $type ) {
		return in_array( $type, self::LEDGER_DIMS, true ) ? $type : '';
	}

	

	public function ajax_values() {
		$this->guard();
		global $wpdb;
		$type = isset( $_POST['type'] ) ? sanitize_key( $_POST['type'] ) : '';
		$p    = $wpdb->prefix;
		$out  = array();
		if ( 'offer' === $type ) {
			$out = $wpdb->get_col( "SELECT DISTINCT offer_type FROM `{$p}aieo_dmm_offer_attributions` WHERE offer_type<>'' ORDER BY offer_type" );
		} elseif ( in_array( $type, array( 'channel', 'campaign', 'creative' ), true ) ) {
			$col = array( 'channel' => 'ordersource', 'campaign' => 'orderutmcampaign', 'creative' => 'orderutmcontent' )[ $type ];
			$out = $wpdb->get_col( "SELECT DISTINCT `{$col}` v FROM `{$p}aieo_order_stats` WHERE `{$col}` IS NOT NULL AND `{$col}`<>'' ORDER BY v LIMIT 500" );
		}
		
		wp_send_json_success( array( 'values' => array_values( array_filter( (array) $out ) ) ) );
	}

	

	
	public function ajax_value_search() {
		$this->guard();
		global $wpdb;
		$p    = $wpdb->prefix;
		
		
		
		$types = isset( $_REQUEST['type'] ) ? array_filter( array_map( 'sanitize_key', (array) wp_unslash( $_REQUEST['type'] ) ) ) : array();
		$dims  = array( 'channel', 'campaign', 'creative', 'offer' );
		$types = array_values( array_intersect( $types, $dims ) );
		if ( empty( $types ) ) {
			$types = $dims;
		}
		$q      = isset( $_REQUEST['q'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['q'] ) ) : '';
		$like   = '%' . $wpdb->esc_like( $q ) . '%';
		$labels = $this->type_options();
		$multi  = count( $types ) > 1;
		$out    = array();
		$seen   = array();
		foreach ( $types as $type ) {
			if ( 'offer' === $type ) {
				$vals = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT offer_type FROM `{$p}aieo_dmm_offer_attributions` WHERE offer_type<>'' AND offer_type LIKE %s ORDER BY offer_type LIMIT 100", $like ) );
			} else {
				$col  = array( 'channel' => 'ordersource', 'campaign' => 'orderutmcampaign', 'creative' => 'orderutmcontent' )[ $type ];
				$vals = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT `{$col}` FROM `{$p}aieo_order_stats` WHERE `{$col}` IS NOT NULL AND `{$col}`<>'' AND `{$col}` LIKE %s ORDER BY `{$col}` LIMIT 100", $like ) );
				
				
				
				
				if ( in_array( $type, AIEO_Attribution_Cost::DIMENSIONS, true ) && class_exists( 'AIEO_Attribution_Cost' ) && AIEO_Attribution_Cost::table_exists() ) {
					$ct    = AIEO_Attribution_Cost::table_name();
					$cvals = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT dimension_value FROM `{$ct}` WHERE dimension_type=%s AND dimension_value<>'' AND dimension_value LIKE %s ORDER BY dimension_value LIMIT 100", $type, $like ) );
					$vals  = array_merge( (array) $vals, (array) $cvals );
				}
			}
			$suffix = $multi ? ( ' · ' . wp_strip_all_tags( isset( $labels[ $type ] ) ? $labels[ $type ] : $type ) ) : '';
			foreach ( (array) $vals as $v ) {
				if ( isset( $seen[ $v ] ) ) {
					continue;
				}
				$seen[ $v ] = 1;
				$out[] = array( 'value' => (string) $v, 'label' => (string) $v . $suffix );
			}
		}
		wp_send_json( array( 'results' => $out ) );
	}

	
	public function ajax_type_search() {
		$this->guard();
		$q   = isset( $_REQUEST['q'] ) ? mb_strtolower( sanitize_text_field( wp_unslash( $_REQUEST['q'] ) ) ) : '';
		$out = array();
		foreach ( $this->type_options() as $k => $label ) {
			if ( '' === $q || false !== mb_strpos( mb_strtolower( $label ), $q ) || false !== strpos( $k, $q ) ) {
				$out[] = array( 'value' => $k, 'label' => $label );
			}
		}
		wp_send_json( array( 'results' => $out ) );
	}

	

	public function ajax_compute() {
		$this->guard();
		$types = isset( $_POST['types'] ) ? array_values( array_filter( array_map( 'sanitize_key', (array) wp_unslash( $_POST['types'] ) ) ) ) : array();
		if ( empty( $types ) && isset( $_POST['type'] ) && '' !== $_POST['type'] ) {
			$types = array( sanitize_key( wp_unslash( $_POST['type'] ) ) ); 
		}
		$types = array_values( array_intersect( $types, array_keys( $this->type_options() ) ) );
		if ( empty( $types ) ) {
			$types = array( 'reco' ); 
		}
		$values = isset( $_POST['values'] ) ? array_values( array_filter( array_map( 'sanitize_text_field', (array) wp_unslash( $_POST['values'] ) ), 'strlen' ) ) : array();
		if ( empty( $values ) && isset( $_POST['value'] ) && '' !== $_POST['value'] ) {
			$values = array( sanitize_text_field( wp_unslash( $_POST['value'] ) ) );
		}
		list( $from, $to ) = $this->dates();
		$labels = $this->type_options();
		$blocks = array();
		foreach ( $types as $tp ) {
			$tv       = $values; 
			$blocks[] = array(
				'type'  => $tp,
				'label' => isset( $labels[ $tp ] ) ? $labels[ $tp ] : $tp,
				'rows'  => $this->compute( $tp, $tv, $from, $to ),
			);
		}
		
		$compare = isset( $_POST['compare'] ) ? sanitize_key( wp_unslash( $_POST['compare'] ) ) : '';
		$cfrom   = '';
		$cto     = '';
		if ( in_array( $compare, array( 'prev', 'yoy' ), true ) ) {
			list( $cfrom, $cto ) = $this->compare_dates( $from, $to, $compare );
			foreach ( $blocks as $bi => $b ) {
				$map = array();
				foreach ( $this->compute( $b['type'], $values, $cfrom, $cto ) as $cr ) {
					$k = isset( $cr['label'] ) ? (string) $cr['label'] : ( isset( $cr['channel'] ) ? (string) $cr['channel'] : '' );
					$map[ $k ] = $cr;
				}
				foreach ( $blocks[ $bi ]['rows'] as $ri => $r ) {
					$k   = isset( $r['label'] ) ? (string) $r['label'] : ( isset( $r['channel'] ) ? (string) $r['channel'] : '' );
					$c   = isset( $map[ $k ] ) ? $map[ $k ] : null;
					$prv = $c ? (float) $c['revenue'] : 0.0;
					$pro = $c ? (int) $c['orders'] : 0;
					$blocks[ $bi ]['rows'][ $ri ]['d_orders']  = $pro > 0 ? round( 100 * ( (int) $r['orders'] - $pro ) / $pro, 1 ) : null;
					$blocks[ $bi ]['rows'][ $ri ]['d_revenue'] = $prv > 0 ? round( 100 * ( (float) $r['revenue'] - $prv ) / $prv, 1 ) : null;
				}
			}
		}
		wp_send_json_success( array(
			'blocks'      => $blocks,
			'type'        => $blocks[0]['type'], 
			'rows'        => $blocks[0]['rows'],
			'from'        => $from,
			'to'          => $to,
			'compare'     => $compare,
			'cfrom'       => $cfrom,
			'cto'         => $cto,
			
			
			'ops_period'  => class_exists( 'AIEO_DMM' ) ? round( AIEO_DMM::get_ops_cost_for_period( $from, $to ), 2 ) : 0,
			'ops_monthly' => class_exists( 'AIEO_DMM' ) ? round( AIEO_DMM::get_ops_cost_monthly(), 2 ) : 0,
		) );
	}

	
	private function compute( $type, $values, $from, $to ) {
		global $wpdb;
		$p    = $wpdb->prefix;
		$f    = $from . ' 00:00:00';
		$t    = $to . ' 23:59:59';
		$rows = array();

		if ( 'reco' === $type ) {
			$r = $wpdb->get_row( $wpdb->prepare(
				"SELECT COUNT(*) AS orders, COALESCE(SUM(os.orderspend),0) AS revenue
				 FROM ( SELECT DISTINCT e.order_id
				        FROM `{$p}aieo_addcart_events` e
				        INNER JOIN `{$p}aieo_reco_events` r
				          ON r.session_id = e.session_id AND r.target_pid = e.product_id
				         AND r.ts < e.ts_added AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)
				        WHERE e.order_id > 0 AND e.ts_added >= %s AND e.ts_added <= %s ) o
				 INNER JOIN `{$p}aieo_order_stats` os ON os.orderid = o.order_id",
				$f, $t
			) );
			$rows[] = $this->mk_row( __( 'Recommendation-assisted', 'ai-eshop-optimizer' ), (int) ( $r->orders ?? 0 ), (float) ( $r->revenue ?? 0 ), 0.0, false );
			return $rows;
		}

		if ( 'reco_lift' === $type ) {
			
			
			
			
			$res = $wpdb->get_results( $wpdb->prepare(
				"SELECT os.ordersource AS channel,
				        COUNT(*) AS orders,
				        COALESCE(SUM(os.orderspend),0) AS revenue,
				        SUM(CASE WHEN o.order_id IS NOT NULL THEN 1 ELSE 0 END) AS reco_orders,
				        COALESCE(SUM(CASE WHEN o.order_id IS NOT NULL THEN os.orderspend ELSE 0 END),0) AS reco_revenue
				 FROM `{$p}aieo_order_stats` os
				 LEFT JOIN ( SELECT DISTINCT e.order_id
				             FROM `{$p}aieo_addcart_events` e
				             INNER JOIN `{$p}aieo_reco_events` r
				               ON r.session_id = e.session_id AND r.target_pid = e.product_id
				              AND r.ts < e.ts_added AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)
				             WHERE e.order_id > 0 AND e.ts_added >= %s AND e.ts_added <= %s ) o
				   ON o.order_id = os.orderid
				 WHERE os.orderdate >= %s AND os.orderdate <= %s AND os.ordersource IS NOT NULL AND os.ordersource <> ''
				 GROUP BY os.ordersource ORDER BY revenue DESC LIMIT 100",
				$f, $t, $from, $to
			) );
			foreach ( (array) $res as $r ) {
				$orders = (int) $r->orders;
				$reco_o = (int) $r->reco_orders;
				$rows[] = array(
					'channel'      => (string) $r->channel,
					'orders'       => $orders,
					'revenue'      => round( (float) $r->revenue, 2 ),
					'reco_orders'  => $reco_o,
					'reco_revenue' => round( (float) $r->reco_revenue, 2 ),
					'lift_pct'     => $orders > 0 ? round( 100 * $reco_o / $orders, 1 ) : 0.0,
				);
			}
			return $rows;
		}

		if ( 'offer' === $type ) {
			$where = 'fired_at >= %s AND fired_at <= %s';
			$args  = array( $f, $t );
			if ( ! empty( $values ) ) {
				$where .= ' AND offer_type IN (' . implode( ',', array_fill( 0, count( $values ), '%s' ) ) . ')';
				$args   = array_merge( $args, array_values( $values ) );
			}
			
			
			
			
			$res = $wpdb->get_results( $wpdb->prepare(
				"SELECT offer_type, COUNT(*) AS orders, SUM(rev) AS revenue, SUM(cost) AS cost
				 FROM ( SELECT offer_type, order_id, MAX(order_total) AS rev, SUM(COALESCE(actual_cost,0)) AS cost
				        FROM `{$p}aieo_dmm_offer_attributions` WHERE {$where}
				        GROUP BY offer_type, order_id ) x
				 GROUP BY offer_type ORDER BY revenue DESC",
				$args
			) );
			foreach ( (array) $res as $r ) {
				$rows[] = $this->mk_row( $r->offer_type, (int) $r->orders, (float) $r->revenue, (float) $r->cost, ( (float) $r->cost ) > 0 );
			}
			return $rows;
		}

		
		$col = array( 'channel' => 'ordersource', 'campaign' => 'orderutmcampaign', 'creative' => 'orderutmcontent' )[ $type ] ?? '';
		if ( '' === $col ) {
			return $rows;
		}
		$where = "`{$col}` IS NOT NULL AND `{$col}`<>'' AND orderdate >= %s AND orderdate <= %s";
		$args  = array( $from, $to );
		if ( ! empty( $values ) ) {
			$where = "`{$col}` IN (" . implode( ',', array_fill( 0, count( $values ), '%s' ) ) . ") AND orderdate >= %s AND orderdate <= %s";
			$args  = array_merge( array_values( $values ), array( $from, $to ) );
		}
		$res = $wpdb->get_results( $wpdb->prepare(
			"SELECT `{$col}` AS label, COUNT(*) AS orders, COALESCE(SUM(orderspend),0) AS revenue
			 FROM `{$p}aieo_order_stats` WHERE {$where} GROUP BY `{$col}` ORDER BY revenue DESC LIMIT 100",
			$args
		) );
		$dim = $this->ledger_dim_for( $type );
		foreach ( (array) $res as $r ) {
			$cost = class_exists( 'AIEO_Attribution_Cost' ) ? AIEO_Attribution_Cost::cost_for( $dim, $r->label, $from, $to ) : 0.0;
			$rows[] = $this->mk_row( (string) $r->label, (int) $r->orders, (float) $r->revenue, (float) $cost, $cost > 0 );
		}
		return $rows;
	}

	private function mk_row( $label, $orders, $revenue, $cost, $cost_known ) {
		$net  = $revenue - $cost;
		$roas = $cost > 0 ? round( $revenue / $cost, 2 ) : null;
		$roi  = $cost > 0 ? round( 100 * $net / $cost, 1 ) : null;
		return array(
			'label'      => $label,
			'orders'     => $orders,
			'revenue'    => round( $revenue, 2 ),
			'cost'       => round( $cost, 2 ),
			'net'        => round( $net, 2 ),
			'roas'       => $roas,
			'roi_pct'    => $roi,
			'cost_known' => (bool) $cost_known,
		);
	}

	

	public function ajax_cost_save() {
		$this->guard();
		$dim   = isset( $_POST['dimension_type'] ) ? sanitize_key( $_POST['dimension_type'] ) : '';
		$val   = isset( $_POST['dimension_value'] ) ? sanitize_text_field( wp_unslash( $_POST['dimension_value'] ) ) : '';
		$ps    = isset( $_POST['period_start'] ) ? sanitize_text_field( wp_unslash( $_POST['period_start'] ) ) : '';
		$pe    = isset( $_POST['period_end'] ) ? sanitize_text_field( wp_unslash( $_POST['period_end'] ) ) : '';
		$amt   = isset( $_POST['amount'] ) ? (float) $_POST['amount'] : 0.0;
		$note  = isset( $_POST['note'] ) ? sanitize_text_field( wp_unslash( $_POST['note'] ) ) : '';
		$ok    = class_exists( 'AIEO_Attribution_Cost' ) && AIEO_Attribution_Cost::upsert( $dim, $val, $ps, $pe, $amt, 'manual', $note );
		if ( ! $ok ) {
			wp_send_json_error( array( 'msg' => __( 'Could not save — check the dimension and dates.', 'ai-eshop-optimizer' ) ) );
		}
		wp_send_json_success( array( 'rows' => $this->cost_rows_for_js( $dim ) ) );
	}

	public function ajax_cost_delete() {
		$this->guard();
		$id  = isset( $_POST['id'] ) ? (int) $_POST['id'] : 0;
		$dim = isset( $_POST['dimension_type'] ) ? sanitize_key( $_POST['dimension_type'] ) : '';
		if ( $id && class_exists( 'AIEO_Attribution_Cost' ) ) {
			AIEO_Attribution_Cost::delete( $id );
		}
		wp_send_json_success( array( 'rows' => $this->cost_rows_for_js( $dim ) ) );
	}

	
	public function ajax_cost_import() {
		$this->guard();
		$csv = isset( $_POST['csv'] ) ? trim( (string) wp_unslash( $_POST['csv'] ) ) : '';
		$dim = isset( $_POST['dimension_type'] ) ? sanitize_key( $_POST['dimension_type'] ) : '';
		$n   = 0;
		$err = 0;
		foreach ( preg_split( '/\r\n|\r|\n/', $csv ) as $line ) {
			$line = trim( $line );
			if ( '' === $line ) {
				continue;
			}
			$c = str_getcsv( $line );
			
			if ( isset( $c[0] ) && in_array( strtolower( trim( $c[0] ) ), array( 'dimension_type', 'type' ), true ) ) {
				continue;
			}
			if ( count( $c ) < 5 ) {
				$err++;
				continue;
			}
			$ok = class_exists( 'AIEO_Attribution_Cost' ) && AIEO_Attribution_Cost::upsert(
				sanitize_key( $c[0] ), sanitize_text_field( $c[1] ), sanitize_text_field( $c[2] ),
				sanitize_text_field( $c[3] ), (float) $c[4], 'csv', isset( $c[5] ) ? sanitize_text_field( $c[5] ) : ''
			);
			$ok ? $n++ : $err++;
		}
		wp_send_json_success( array(
			'msg'  => sprintf( __( 'Imported %1$d rows (%2$d skipped).', 'ai-eshop-optimizer' ), $n, $err ),
			'rows' => $this->cost_rows_for_js( $dim ),
		) );
	}

	public function ajax_cost_list() {
		$this->guard();
		$dim = isset( $_POST['dimension_type'] ) ? sanitize_key( $_POST['dimension_type'] ) : '';
		$ps  = isset( $_POST['period_start'] ) ? sanitize_text_field( wp_unslash( $_POST['period_start'] ) ) : '';
		$pe  = isset( $_POST['period_end'] ) ? sanitize_text_field( wp_unslash( $_POST['period_end'] ) ) : '';
		
		
		$val = isset( $_POST['dimension_value'] ) ? trim( wp_strip_all_tags( (string) wp_unslash( $_POST['dimension_value'] ) ) ) : '';
		wp_send_json_success( array( 'rows' => $this->cost_rows_for_js( $dim, $ps, $pe, $val ) ) );
	}

	private function cost_rows_for_js( $dim, $win_start = '', $win_end = '', $value = '' ) {
		if ( ! class_exists( 'AIEO_Attribution_Cost' ) ) {
			return array();
		}
		$out = array();
		foreach ( AIEO_Attribution_Cost::rows( $dim, 2000, $win_start, $win_end, $value ) as $r ) {
			$out[] = array(
				'id'    => (int) $r['id'],
				'type'  => $r['dimension_type'],
				'value' => $r['dimension_value'],
				'start' => $r['period_start'],
				'end'   => $r['period_end'],
				'amount' => $r['amount'],
				'source' => $r['source'],
				'note'  => $r['note'],
			);
		}
		return $out;
	}

	
	public function ajax_cost_export() {
		$this->guard();
		$dim = isset( $_REQUEST['dimension_type'] ) ? sanitize_key( wp_unslash( $_REQUEST['dimension_type'] ) ) : '';
		$ps  = isset( $_REQUEST['period_start'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['period_start'] ) ) : '';
		$pe  = isset( $_REQUEST['period_end'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['period_end'] ) ) : '';
		$val = isset( $_REQUEST['dimension_value'] ) ? trim( wp_strip_all_tags( (string) wp_unslash( $_REQUEST['dimension_value'] ) ) ) : '';

		if ( ! class_exists( 'AIEO_Attribution_Cost' ) ) {
			wp_die( esc_html__( 'Ad-spend ledger unavailable.', 'ai-eshop-optimizer' ) );
		}
		if ( ! class_exists( 'AIEO_DMM_XLSX_Writer' ) && defined( 'AIEO_FILE' ) ) {
			$f = dirname( AIEO_FILE ) . '/includes/dmm/services/class-aieo-dmm-xlsx-writer.php';
			if ( is_readable( $f ) ) {
				require_once $f;
			}
		}
		if ( ! class_exists( 'AIEO_DMM_XLSX_Writer' ) ) {
			wp_die( esc_html__( 'XLSX writer unavailable.', 'ai-eshop-optimizer' ) );
		}

		$rows = AIEO_Attribution_Cost::rows( $dim, 10000, $ps, $pe, $val );
		$w    = new AIEO_DMM_XLSX_Writer( __( 'Ad-spend ledger', 'ai-eshop-optimizer' ) );
		$w->add_header_row( array(
			__( 'Dimension', 'ai-eshop-optimizer' ),
			__( 'Value', 'ai-eshop-optimizer' ),
			__( 'From', 'ai-eshop-optimizer' ),
			__( 'To', 'ai-eshop-optimizer' ),
			__( 'Amount', 'ai-eshop-optimizer' ),
			__( 'Currency', 'ai-eshop-optimizer' ),
			__( 'Source', 'ai-eshop-optimizer' ),
			__( 'Note', 'ai-eshop-optimizer' ),
			__( 'Updated', 'ai-eshop-optimizer' ),
		) );
		foreach ( (array) $rows as $r ) {
			$w->add_row(
				array(
					(string) $r['dimension_type'],
					(string) $r['dimension_value'],
					(string) $r['period_start'],
					(string) $r['period_end'],
					(float) $r['amount'],
					(string) $r['currency'],
					(string) $r['source'],
					(string) $r['note'],
					(string) $r['updated_at'],
				),
				array( 4 => 'currency' )
			);
		}
		$fname = 'aieo-ad-spend' . ( '' !== $dim ? '-' . $dim : '-all' )
			. ( ( '' !== $ps && '' !== $pe ) ? '-' . $ps . '_' . $pe : '' ) . '.xlsx';
		$w->stream_download( $fname );
	}

	

	
	private function type_options() {
		return array(
			'reco'      => '⭐ ' . __( 'Recommendation engine', 'ai-eshop-optimizer' ),
			'reco_lift' => '⭐ ' . __( 'Reco lift per channel', 'ai-eshop-optimizer' ),
			'offer'     => __( 'Offer / mechanism', 'ai-eshop-optimizer' ),
			'channel'   => __( 'Marketing channel', 'ai-eshop-optimizer' ),
			'campaign'  => __( 'Campaign', 'ai-eshop-optimizer' ),
			'creative'  => __( 'Creative', 'ai-eshop-optimizer' ),
		);
	}

	public function render_subtab() {
		$nonce    = wp_create_nonce( self::NONCE );
		$def_from = gmdate( 'Y-m-d', strtotime( '-30 days' ) );
		$def_to   = gmdate( 'Y-m-d' );
		if ( defined( 'AIEO_FILE' ) ) {
			$vp_abs = dirname( AIEO_FILE ) . '/assets/js/aieo-value-picker.js';
			wp_enqueue_script( 'aieo-value-picker', plugins_url( 'assets/js/aieo-value-picker.js', AIEO_FILE ), array(), is_readable( $vp_abs ) ? (string) filemtime( $vp_abs ) : '1', true );
		}

		echo '<div class="aieo-dmm-section">';
		echo '<div class="aieo-dmm-card">';
		echo '<h3>' . esc_html__( 'ROI Wizard', 'ai-eshop-optimizer' ) . '</h3>';
		echo '<p style="opacity:.8;margin-top:0;max-width:780px">'
			. esc_html__( 'Pick a type, the exact detected value, and a period. AIEO is your single ROI cockpit: revenue (the order we compute) plus your own recommendation / offer attribution, weighed against ad spend you enter or import.', 'ai-eshop-optimizer' )
			. '</p>';

		echo '<div style="display:flex;flex-wrap:wrap;gap:12px;align-items:flex-start;margin:14px 0">';
		echo '<label>' . esc_html__( 'Type', 'ai-eshop-optimizer' ) . '<br>'
			. '<span class="aieo-valpick" id="aieo-roi-type" data-search-action="aieo_roi_type_search" data-multi="1"'
			. ' data-title="' . esc_attr__( 'Choose one or more types', 'ai-eshop-optimizer' ) . '" data-placeholder="' . esc_attr__( 'Search types…', 'ai-eshop-optimizer' ) . '"'
			. ' data-empty="' . esc_attr__( '⭐ Recommendation engine (default)', 'ai-eshop-optimizer' ) . '"'
			. ' data-ajaxurl="' . esc_attr( admin_url( 'admin-ajax.php' ) ) . '" data-nonce="' . esc_attr( $nonce ) . '">'
			. '<input type="hidden" class="aieo-valpick-store" value="">'
			. '<button type="button" class="button aieo-valpick-open">' . esc_html__( '⭐ Recommendation engine (default)', 'ai-eshop-optimizer' ) . '</button>'
			. '<span class="aieo-valpick-chips"></span>'
			. '</span></label>';
		echo '<label>' . esc_html__( 'Exact', 'ai-eshop-optimizer' ) . '<br>'
			. '<span class="aieo-valpick" id="aieo-roi-value" data-search-action="aieo_roi_value_search" data-multi="1"'
			. ' data-title="' . esc_attr__( 'Choose values', 'ai-eshop-optimizer' ) . '" data-placeholder="' . esc_attr__( 'Search detected values…', 'ai-eshop-optimizer' ) . '"'
			. ' data-empty="' . esc_attr__( '— all detected —', 'ai-eshop-optimizer' ) . '" data-context="type:aieo-roi-type"'
			. ' data-ajaxurl="' . esc_attr( admin_url( 'admin-ajax.php' ) ) . '" data-nonce="' . esc_attr( $nonce ) . '">'
			. '<input type="hidden" class="aieo-valpick-store" value="">'
			. '<button type="button" class="button aieo-valpick-open">' . esc_html__( '— all detected —', 'ai-eshop-optimizer' ) . '</button>'
			. '<span class="aieo-valpick-chips"></span>'
			. '</span></label>';
		echo '<label>' . esc_html__( 'Compare', 'ai-eshop-optimizer' ) . '<br><select id="aieo-roi-compare">'
			. '<option value="">' . esc_html__( '— off —', 'ai-eshop-optimizer' ) . '</option>'
			. '<option value="prev">' . esc_html__( 'Previous period', 'ai-eshop-optimizer' ) . '</option>'
			. '<option value="yoy">' . esc_html__( 'Same period last year', 'ai-eshop-optimizer' ) . '</option>'
			. '</select></label>';
		echo '<label style="order:-2">' . esc_html__( 'From', 'ai-eshop-optimizer' ) . '<br><input type="date" id="aieo-roi-from" value="' . esc_attr( $def_from ) . '"></label>';
		echo '<label style="order:-1">' . esc_html__( 'To', 'ai-eshop-optimizer' ) . '<br><input type="date" id="aieo-roi-to" value="' . esc_attr( $def_to ) . '"></label>';
		echo '<span>&nbsp;<br><button type="button" class="button button-primary" id="aieo-roi-go">' . esc_html__( 'Compute ROI', 'ai-eshop-optimizer' ) . '</button></span>';
		echo '</div>';

		echo '<div id="aieo-roi-results" style="margin-top:8px"></div>';
		echo '</div>'; 
		echo '</div>'; 

		
		if ( $this->utm() ) {
			$this->utm()->render_subtab();
		}

		
		echo '<div class="aieo-dmm-section">';
		echo '<div class="aieo-dmm-card" id="aieo-roi-costcard">';
		echo '<h3>' . esc_html__( 'Ad-spend ledger', 'ai-eshop-optimizer' ) . '</h3>';
		echo '<p style="opacity:.8;margin-top:0;max-width:780px">'
			. esc_html__( 'Enter spend per channel / campaign / creative per period. Bulk option: paste a CSV of dimension_type,dimension_value,period_start,period_end,amount[,note]. Offers carry their real cost automatically; the recommendation engine is revenue-first.', 'ai-eshop-optimizer' )
			. '</p>';
		echo '<div style="display:flex;flex-wrap:wrap;gap:10px;align-items:flex-start">';
		echo '<label>' . esc_html__( 'Dimension', 'ai-eshop-optimizer' ) . '<br><select id="aieo-cost-dim"><option value="channel">channel</option><option value="campaign">campaign</option><option value="creative">creative</option></select></label>';
		echo '<label>' . esc_html__( 'Value', 'ai-eshop-optimizer' ) . '<br>'
			. '<span class="aieo-valpick" id="aieo-cost-val" data-search-action="aieo_roi_value_search" data-multi="0"'
			. ' data-title="' . esc_attr__( 'Choose a value', 'ai-eshop-optimizer' ) . '" data-placeholder="' . esc_attr__( 'Search…', 'ai-eshop-optimizer' ) . '"'
			. ' data-empty="' . esc_attr__( 'pick a value…', 'ai-eshop-optimizer' ) . '" data-context="type:aieo-cost-dim"'
			. ' data-ajaxurl="' . esc_attr( admin_url( 'admin-ajax.php' ) ) . '" data-nonce="' . esc_attr( $nonce ) . '">'
			. '<input type="hidden" class="aieo-valpick-store" value="">'
			. '<button type="button" class="button aieo-valpick-open">' . esc_html__( 'pick a value…', 'ai-eshop-optimizer' ) . '</button>'
			. '<span class="aieo-valpick-chips"></span>'
			. '</span></label>';
		echo '<label style="order:-2">' . esc_html__( 'From', 'ai-eshop-optimizer' ) . '<br><input type="date" id="aieo-cost-from"></label>';
		echo '<label style="order:-1">' . esc_html__( 'To', 'ai-eshop-optimizer' ) . '<br><input type="date" id="aieo-cost-to"></label>';
		echo '<label>' . esc_html__( 'Amount', 'ai-eshop-optimizer' ) . '<br><input type="number" step="0.01" id="aieo-cost-amt" style="width:110px"></label>';
		echo '<span>&nbsp;<br><button type="button" class="button" id="aieo-cost-save">' . esc_html__( 'Add / update', 'ai-eshop-optimizer' ) . '</button></span>';
		echo '</div>';
		echo '<details style="margin-top:10px"><summary>' . esc_html__( 'Paste CSV to bulk import', 'ai-eshop-optimizer' ) . '</summary>';
		echo '<textarea id="aieo-cost-csv" rows="5" style="width:100%;margin-top:6px" placeholder="channel,paid_facebook,2026-06-01,2026-06-30,500,June FB"></textarea>';
		echo '<button type="button" class="button" id="aieo-cost-import">' . esc_html__( 'Import CSV', 'ai-eshop-optimizer' ) . '</button> <a href="#" id="aieo-cost-export">' . esc_html__( 'Export to Excel (.xlsx)', 'ai-eshop-optimizer' ) . '</a> <span style="opacity:.6;font-size:12px">' . esc_html__( '(respects the dimension + From/To filter)', 'ai-eshop-optimizer' ) . '</span>';
		echo '</details>';
		echo '<div id="aieo-cost-rows" style="margin-top:12px"></div>';
		echo '</div>'; 
		echo '</div>'; 

		$this->print_js( $nonce );
	}

	private function print_js( $nonce ) {
		$ajax = admin_url( 'admin-ajax.php' );
		?>
		<script>
		(function () {
			var AJAX = <?php echo wp_json_encode( $ajax ); ?>, NONCE = <?php echo wp_json_encode( $nonce ); ?>;
			function $(id){ return document.getElementById(id); }
			function post(action, data){ data = data||{}; data.action=action; data.nonce=NONCE; var fd=new FormData(); for(var k in data){ var v=data[k]; if(Array.isArray(v)){ v.forEach(function(x){ fd.append(k+'[]', x); }); } else { fd.append(k,v); } } return fetch(AJAX,{method:'POST',credentials:'same-origin',body:fd}).then(function(r){return r.json();}); }
			function money(n){ return (n==null?'—':(Math.round(n*100)/100).toLocaleString(undefined,{minimumFractionDigits:2,maximumFractionDigits:2})+' €'); }
			function onTypeChange(){
				var types=(window.aieoValPicker)?aieoValPicker.values($('aieo-roi-type')):[]; var single=(types.length===1)?types[0]:''; var pick=$('aieo-roi-value');
				// A value picked under one type is meaningless under another → clear it.
				/* Exact persists across type changes — each value filters its own dimension */
				// Exact stays VISIBLE; it is only meaningful for a SINGLE dimension
				// type (channel/campaign/creative/offer) — greyed + disabled otherwise
				// (reco/reco-lift have no exact dimension; >1 type is ambiguous).
				var ok=(single&&single!=='reco'&&single!=='reco_lift');
				var btn=pick.querySelector('.aieo-valpick-open');
				pick.style.display='inline-flex'; pick.style.opacity=''; pick.style.pointerEvents='';
				if(btn){ btn.disabled=false; if(window.aieoValPicker && !aieoValPicker.values(pick).length){ btn.textContent=pick.getAttribute('data-empty')||'— all detected —'; } }
			}
			function aieoRoiRenderBlock(type, rows, opsCost){ opsCost=(+opsCost||0);
				function dpct(d){ if(d==null){ return ''; } return ' <span style="font-size:11px;font-weight:600;color:'+(d>=0?'#1a7f37':'#c0392b')+'">'+(d>=0?'+':'')+d+'%</span>'; }
				if(type==='reco_lift'){
					if(!rows.length){ return '<p style="opacity:.7">No data for this period.</p>'; }
					var lh='<table class="widefat striped" style="margin-top:4px"><thead><tr><th>Channel (origin)</th><th>Orders</th><th>Revenue</th><th>Reco-assisted orders</th><th>Reco-assisted revenue</th><th>Reco lift</th></tr></thead><tbody>';
					rows.forEach(function(r){ lh+='<tr><td>'+(r.channel||'(none)')+'</td><td>'+r.orders+dpct(r.d_orders)+'</td><td>'+money(r.revenue)+dpct(r.d_revenue)+'</td><td>'+r.reco_orders+'</td><td>'+money(r.reco_revenue)+'</td><td><strong>'+r.lift_pct+'%</strong></td></tr>'; });
					return lh+'</tbody></table>';
				}
				if(!rows.length){ return '<p style="opacity:.7">No data.</p>'; }
				var h='<table class="widefat striped" style="margin-top:4px"><thead><tr><th>'+type+'</th><th>Orders</th><th>Revenue</th><th>Cost</th><th>Net</th><th>ROAS</th><th>ROI %</th><th title="ROI for the WHOLE operation — adds the prorated operations overhead ('+money(opsCost)+' this period) on top of ad spend.">True ROI</th></tr></thead><tbody>';
				var tO=0,tR=0,tC=0,tN=0,anyCost=false;
				rows.forEach(function(r){ tO+=(+r.orders||0); tR+=(+r.revenue||0); tN+=(+r.net||0); if(r.cost_known){ tC+=(+r.cost||0); anyCost=true; } h+='<tr><td>'+(r.label||'')+'</td><td>'+r.orders+dpct(r.d_orders)+'</td><td>'+money(r.revenue)+dpct(r.d_revenue)+'</td><td>'+(r.cost_known?money(r.cost):'<span style="opacity:.5">—</span>')+'</td><td>'+money(r.net)+'</td><td>'+(r.roas==null?'<span style="opacity:.5">n/a</span>':r.roas+'×')+'</td><td>'+(r.roi_pct==null?'<span style="opacity:.5">n/a</span>':r.roi_pct+'%')+'</td><td><span style="opacity:.4" title="Operations overhead is operation-wide — see the Total row">—</span></td></tr>'; });
				var tRoas=(anyCost&&tC>0)?(Math.round(tR/tC*100)/100+'×'):'<span style="opacity:.5">n/a</span>';
				var tRoi=(anyCost&&tC>0)?(Math.round((tR-tC)/tC*1000)/10+'%'):'<span style="opacity:.5">n/a</span>';
				var tSpend=tC+opsCost; var tTrue=(tSpend>0)?(Math.round((tR-tSpend)/tSpend*1000)/10+'%'):'<span style="opacity:.5">n/a</span>';
				h+='</tbody><tfoot><tr style="font-weight:700;background:#f6f7f7;border-top:2px solid #c3c4c7"><td>Total</td><td>'+tO+'</td><td>'+money(tR)+'</td><td>'+(anyCost?money(tC):'<span style="opacity:.5">—</span>')+'</td><td>'+money(tN)+'</td><td>'+tRoas+'</td><td>'+tRoi+'</td><td title="incl. '+money(opsCost)+' prorated operations cost">'+tTrue+'</td></tr></tfoot>';
				return h+'</table>';
			}
			function compute(){
				var box=$('aieo-roi-results'); box.innerHTML='<p style="opacity:.6">…</p>';
				var vals=(window.aieoValPicker)?aieoValPicker.values($('aieo-roi-value')):[];
				post('aieo_roi_compute',{types:(window.aieoValPicker?aieoValPicker.values($('aieo-roi-type')):[]),values:vals,from:$('aieo-roi-from').value,to:$('aieo-roi-to').value,compare:($('aieo-roi-compare')?$('aieo-roi-compare').value:'')}).then(function(j){
					if(!j||!j.success){ box.innerHTML='<p>Error.</p>'; return; }
					var __blocks=j.data.blocks||[];
					{ var __n='Period '+j.data.from+' → '+j.data.to+'.'; if(j.data.compare && j.data.cfrom){ __n+=' <strong>Δ% vs '+j.data.cfrom+' → '+j.data.cto+'</strong> ('+(j.data.compare==='yoy'?'same period last year':'previous period')+').'; } __n+=' Revenue = the order AIEO computes; cost from offers or the ad-spend ledger.'; if(j.data.ops_monthly>0){ __n+=' <strong>True ROI</strong> on the Total also charges '+money(j.data.ops_period)+' operations overhead ('+money(j.data.ops_monthly)+'/mo, prorated to this period).'; } box.innerHTML=__blocks.map(function(b){ return (__blocks.length>1?'<h4 style="margin:16px 0 2px">'+(b.label||b.type)+'</h4>':'')+aieoRoiRenderBlock(b.type,b.rows||[],j.data.ops_period||0); }).join('')+'<p style="opacity:.6;margin-top:8px">'+__n+'</p>'; return; } /* unified render handles single+multi; legacy block below is unreachable */
					if(j.data.type==='reco_lift'){
							var lr=j.data.rows||[];
							if(!lr.length){ box.innerHTML='<p style="opacity:.7">No data for this period.</p>'; return; }
							var lh='<table class="widefat striped" style="margin-top:8px"><thead><tr><th>Channel (origin)</th><th>Orders</th><th>Revenue</th><th>Reco-assisted orders</th><th>Reco-assisted revenue</th><th>Reco lift</th></tr></thead><tbody>';
							lr.forEach(function(r){ lh+='<tr><td>'+(r.channel||'(none)')+'</td><td>'+r.orders+'</td><td>'+money(r.revenue)+'</td><td>'+r.reco_orders+'</td><td>'+money(r.reco_revenue)+'</td><td><strong>'+r.lift_pct+'%</strong></td></tr>'; });
							lh+='</tbody></table><p style="opacity:.6;margin-top:6px">Period '+j.data.from+' → '+j.data.to+'. <strong>Reco lift</strong> = the share of each channel\'s orders that AIEO\'s recommendation engine assisted (and the € it rode on) — the recommendation\'s incremental impact <strong>over and above</strong> the channel that brought the traffic.</p>';
							box.innerHTML=lh; return;
						}
					var rows=j.data.rows||[];
					if(!rows.length){ box.innerHTML='<p style="opacity:.7">No data for this selection / period.</p>'; return; }
					var h='<table class="widefat striped" style="margin-top:8px"><thead><tr><th>'+j.data.type+'</th><th>Orders</th><th>Revenue</th><th>Cost</th><th>Net</th><th>ROAS</th><th>ROI %</th></tr></thead><tbody>';
					rows.forEach(function(r){
						h+='<tr><td>'+(r.label||'')+'</td><td>'+r.orders+'</td><td>'+money(r.revenue)+'</td><td>'+(r.cost_known?money(r.cost):'<span style="opacity:.5">—</span>')+'</td><td>'+money(r.net)+'</td><td>'+(r.roas==null?'<span style="opacity:.5">n/a</span>':r.roas+'×')+'</td><td>'+(r.roi_pct==null?'<span style="opacity:.5">n/a</span>':r.roi_pct+'%')+'</td></tr>';
					});
					h+='</tbody></table><p style="opacity:.6;margin-top:6px">Period '+j.data.from+' → '+j.data.to+'. Revenue = the order AIEO computes. Cost from offers (automatic) or the ad-spend ledger; "—"/"n/a" = no cost entered yet.</p>';
					box.innerHTML=h;
				});
			}
			function renderCosts(rows){
				var box=$('aieo-cost-rows');
				if(!rows||!rows.length){ var hasWin=(($('aieo-cost-from')&&$('aieo-cost-from').value)||($('aieo-cost-to')&&$('aieo-cost-to').value)); box.innerHTML='<p style="opacity:.6">'+(hasWin?'No spend rows in this date range — clear From/To to see all.':'No spend rows yet for this dimension.')+'</p>'; return; }
				var h='<table class="widefat striped"><thead><tr><th>Value</th><th>From</th><th>To</th><th>Amount</th><th>Src</th><th></th></tr></thead><tbody>';
				rows.forEach(function(r){ h+='<tr><td>'+r.value+'</td><td>'+r.start+'</td><td>'+r.end+'</td><td>'+money(parseFloat(r.amount))+'</td><td>'+r.source+'</td><td><a href="#" data-del="'+r.id+'">delete</a></td></tr>'; });
				h+='</tbody></table>';
				box.innerHTML=h;
				box.querySelectorAll('a[data-del]').forEach(function(a){ a.addEventListener('click',function(e){ e.preventDefault(); post('aieo_roi_cost_delete',{id:a.getAttribute('data-del'),dimension_type:$('aieo-cost-dim').value}).then(function(j){ if(j&&j.success){ listCosts(); } }); }); });
			}
			function costWin(){ return { period_start:($('aieo-cost-from')?$('aieo-cost-from').value:''), period_end:($('aieo-cost-to')?$('aieo-cost-to').value:'') }; }
			function costVal(){ return (window.aieoValPicker)?(aieoValPicker.values($('aieo-cost-val'))[0]||''):''; }
			function listCosts(){ var d={dimension_type:$('aieo-cost-dim').value}; var w=costWin(); d.period_start=w.period_start; d.period_end=w.period_end; d.dimension_value=costVal(); post('aieo_roi_cost_list',d).then(function(j){ if(j&&j.success){ renderCosts(j.data.rows); } }); }
			// init
			$('aieo-roi-type').addEventListener('aieo-valpick:change',onTypeChange);
			$('aieo-roi-go').addEventListener('click',compute);
			onTypeChange();
			$('aieo-cost-dim').addEventListener('change',function(){ if(window.aieoValPicker){ aieoValPicker.clear($('aieo-cost-val')); } listCosts(); });
			if($('aieo-cost-from')){ $('aieo-cost-from').addEventListener('change',listCosts); }
			if($('aieo-cost-to')){ $('aieo-cost-to').addEventListener('change',listCosts); }
			if($('aieo-cost-val')){ $('aieo-cost-val').addEventListener('aieo-valpick:change',listCosts); }
			$('aieo-cost-save').addEventListener('click',function(){
				var cv=(window.aieoValPicker)?(aieoValPicker.values($('aieo-cost-val'))[0]||''):'';
				post('aieo_roi_cost_save',{dimension_type:$('aieo-cost-dim').value,dimension_value:cv,period_start:$('aieo-cost-from').value,period_end:$('aieo-cost-to').value,amount:$('aieo-cost-amt').value,note:''}).then(function(j){ if(j&&j.success){ $('aieo-cost-amt').value=''; if(window.aieoValPicker){ aieoValPicker.clear($('aieo-cost-val')); } listCosts(); } else { alert(j&&j.data&&j.data.msg?j.data.msg:'Save failed'); } });
			});
			$('aieo-cost-import').addEventListener('click',function(){
				post('aieo_roi_cost_import',{csv:$('aieo-cost-csv').value,dimension_type:$('aieo-cost-dim').value}).then(function(j){ if(j&&j.success){ listCosts(); alert(j.data.msg); } });
			});
			$('aieo-cost-export').addEventListener('click',function(e){ e.preventDefault();
				var w=costWin(); var u=AJAX+'?action=aieo_roi_cost_export&nonce='+encodeURIComponent(NONCE)+'&dimension_type='+encodeURIComponent($('aieo-cost-dim').value)+'&period_start='+encodeURIComponent(w.period_start||'')+'&period_end='+encodeURIComponent(w.period_end||'')+'&dimension_value='+encodeURIComponent(costVal()); window.location.href=u;
			});
			listCosts();
		})();
		</script>
		<?php
	}
}
