<?php

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

class AIEO_DMM_Saved_Abandoned_Carts extends AIEO_DMM_Module_Base {

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

	public function get_label() {
		return __( 'Saved & Abandoned Carts', 'ai-eshop-optimizer' );
	}

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

	public function get_priority() {
		return 72.5; 
	}

	
	
	

	private static function t_carts() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_carts';
	}

	private static function t_items() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_cart_items';
	}

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

	private static function load_xlsx_writer() {
		if ( ! class_exists( 'AIEO_DMM_XLSX_Writer' ) ) {
			require_once AIEO_DIR . 'includes/dmm/services/class-aieo-dmm-xlsx-writer.php';
		}
	}

	
	
	

	public function register_hooks() {
		add_action( 'wp_ajax_aieo_carts_save_settings', array( $this, 'ajax_save_settings' ) );
		add_action( 'wp_ajax_aieo_carts_export', array( $this, 'ajax_export' ) );
		add_action( 'wp_ajax_aieo_carts_capture_now', array( $this, 'ajax_capture_now' ) );
	}

	
	
	

	
	public static function compute_kpis() {
		global $wpdb;
		$c = self::t_carts();

		$k = array(
			'abandoned_count'   => 0,
			'abandoned_value'   => 0.0,
			'open_count'        => 0,
			'open_value'        => 0.0,
			'converted_count'   => 0,
			'converted_value'   => 0.0,
			'expired_count'     => 0,
			'expired_value'     => 0.0,
			'conversion_rate'   => 0.0,
			'restocked_count'   => 0,
			'restocked_value'   => 0.0,
			'saved_count'       => 0,
			'saved_value'       => 0.0,
			'saved_resumes'     => 0,
		);

		if ( ! self::table_exists( $c ) ) {
			return $k;
		}

		$ab = $wpdb->get_row(
			"SELECT
				COUNT(*) AS n,
				COALESCE(SUM(total),0) AS v,
				SUM(outcome='open') AS open_n,
				COALESCE(SUM(CASE WHEN outcome='open' THEN total ELSE 0 END),0) AS open_v,
				SUM(outcome='converted') AS conv_n,
				COALESCE(SUM(CASE WHEN outcome='converted' THEN total ELSE 0 END),0) AS conv_v,
				SUM(outcome='expired') AS exp_n,
				COALESCE(SUM(CASE WHEN outcome='expired' THEN total ELSE 0 END),0) AS exp_v,
				SUM(contains_restocked=1) AS rs_n,
				COALESCE(SUM(CASE WHEN contains_restocked=1 THEN total ELSE 0 END),0) AS rs_v
			 FROM `{$c}` WHERE kind='abandoned'",
			ARRAY_A
		);
		if ( is_array( $ab ) ) {
			$k['abandoned_count'] = (int) $ab['n'];
			$k['abandoned_value'] = (float) $ab['v'];
			$k['open_count']      = (int) $ab['open_n'];
			$k['open_value']      = (float) $ab['open_v'];
			$k['converted_count'] = (int) $ab['conv_n'];
			$k['converted_value'] = (float) $ab['conv_v'];
			$k['expired_count']   = (int) $ab['exp_n'];
			$k['expired_value']   = (float) $ab['exp_v'];
			$k['restocked_count'] = (int) $ab['rs_n'];
			$k['restocked_value'] = (float) $ab['rs_v'];
			$decided              = $k['converted_count'] + $k['expired_count'];
			$k['conversion_rate'] = $decided > 0 ? ( $k['converted_count'] / $decided ) : 0.0;
		}

		$sv = $wpdb->get_row(
			"SELECT COUNT(*) AS n, COALESCE(SUM(total),0) AS v, COALESCE(SUM(resume_count),0) AS r
			 FROM `{$c}` WHERE kind='saved'",
			ARRAY_A
		);
		if ( is_array( $sv ) ) {
			$k['saved_count']   = (int) $sv['n'];
			$k['saved_value']   = (float) $sv['v'];
			$k['saved_resumes'] = (int) $sv['r'];
		}

		return $k;
	}

	
	public static function top_abandoned_products( $limit = 25 ) {
		global $wpdb;
		$c = self::t_carts();
		$i = self::t_items();
		if ( ! self::table_exists( $c ) || ! self::table_exists( $i ) ) {
			return array();
		}
		$rows = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					ci.product_id,
					ci.variation_id,
					MAX(ci.sku) AS sku,
					MAX(ci.name) AS name,
					MAX(ci.is_restocked) AS is_restocked,
					COUNT(DISTINCT CASE WHEN c.outcome <> 'converted' THEN ci.cart_id END) AS carts,
					COALESCE(SUM(CASE WHEN c.outcome <> 'converted' THEN ci.quantity END),0) AS units,
					COALESCE(SUM(CASE WHEN c.outcome <> 'converted' THEN ci.line_total END),0) AS lost_value,
					COUNT(DISTINCT CASE WHEN c.outcome = 'converted' THEN ci.cart_id END) AS sold_transactions,
					COALESCE(SUM(CASE WHEN c.outcome = 'converted' THEN ci.quantity END),0) AS sold_units
				 FROM `{$i}` ci
				 JOIN `{$c}` c ON c.id = ci.cart_id
				 WHERE c.kind='abandoned'
				 GROUP BY ci.product_id, ci.variation_id
				 HAVING carts > 0
				 ORDER BY lost_value DESC, units DESC
				 LIMIT %d",
				$limit
			),
			ARRAY_A
		);

		
		foreach ( $rows as &$r ) {
			$converted           = (int) $r['sold_transactions'];
			$checkout_carts      = $converted + (int) $r['carts'];
			$r['checkout_carts'] = $checkout_carts;
			$r['success_rate']   = $checkout_carts > 0 ? round( $converted / $checkout_carts * 100, 1 ) : 0.0;
		}
		unset( $r );

		return $rows;
	}

	
	public static function recent_carts( $limit = 100 ) {
		global $wpdb;
		$c = self::t_carts();
		if ( ! self::table_exists( $c ) ) {
			return array();
		}
		return (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, token, kind, source, wc_order_id, user_id, customer_email, name,
				        currency, item_count, total, status, outcome, contains_restocked,
				        resume_count, created_at, converted_at
				 FROM `{$c}`
				 ORDER BY created_at DESC, id DESC
				 LIMIT %d",
				$limit
			),
			ARRAY_A
		);
	}

	
	
	

	
	public function ajax_save_settings() {
		AIEO_DMM::verify_ajax_request();

		$enabled     = ! empty( $_POST['save_enabled'] ) ? '1' : '0';
		$label       = isset( $_POST['save_label'] ) ? sanitize_text_field( wp_unslash( $_POST['save_label'] ) ) : '';
		$ttl         = isset( $_POST['ttl_days'] ) ? max( 0, (int) $_POST['ttl_days'] ) : 60;
		$on_checkout = ! empty( $_POST['show_on_checkout'] ) ? '1' : '0';
		$capture_on  = ! empty( $_POST['capture_enabled'] ) ? '1' : '0';

		update_option( 'aieo_carts_save_enabled', $enabled, false );
		update_option( 'aieo_carts_save_label', $label, false );
		update_option( 'aieo_carts_saved_ttl_days', $ttl, false );
		update_option( 'aieo_carts_show_on_checkout', $on_checkout, false );
		update_option( 'aieo_carts_capture_enabled', $capture_on, false );

		wp_send_json_success( array( 'message' => __( 'Settings saved.', 'ai-eshop-optimizer' ) ) );
	}

	
	public function ajax_capture_now() {
		AIEO_DMM::verify_ajax_request();
		if ( ! class_exists( 'AIEO_Carts_Capture' ) ) {
			wp_send_json_error( array( 'message' => __( 'Capture job unavailable.', 'ai-eshop-optimizer' ) ) );
		}
		$res = AIEO_Carts_Capture::instance()->run_capture( 300, 800, true );
		wp_send_json_success(
			array(
				'message' => sprintf(
					
					__( 'Captured %1$d new, resolved %2$d.', 'ai-eshop-optimizer' ),
					(int) $res['captured'],
					(int) $res['resolved']
				),
			)
		);
	}

	
	public function ajax_export() {
		global $wpdb;
		AIEO_DMM::verify_ajax_request();
		self::load_xlsx_writer();

		$c    = self::t_carts();
		$rows = self::table_exists( $c )
			? (array) $wpdb->get_results(
				"SELECT * FROM `{$c}` ORDER BY created_at DESC, id DESC LIMIT 100000",
				ARRAY_A
			)
			: array();

		$yn = static function ( $v ) {
			return ( (int) $v === 1 ) ? __( 'yes', 'ai-eshop-optimizer' ) : '';
		};

		$w = new AIEO_DMM_XLSX_Writer( 'Carts' );
		$w->add_header_row(
			array(
				__( 'When', 'ai-eshop-optimizer' ),
				__( 'Kind', 'ai-eshop-optimizer' ),
				__( 'Source', 'ai-eshop-optimizer' ),
				__( 'Outcome', 'ai-eshop-optimizer' ),
				__( 'Items', 'ai-eshop-optimizer' ),
				__( 'Value', 'ai-eshop-optimizer' ),
				__( 'Currency', 'ai-eshop-optimizer' ),
				__( 'Has restocked', 'ai-eshop-optimizer' ),
				__( 'Customer email', 'ai-eshop-optimizer' ),
				__( 'User ID', 'ai-eshop-optimizer' ),
				__( 'WC order', 'ai-eshop-optimizer' ),
				__( 'Converted order', 'ai-eshop-optimizer' ),
				__( 'Converted at', 'ai-eshop-optimizer' ),
				__( 'Resumes', 'ai-eshop-optimizer' ),
				__( 'Name', 'ai-eshop-optimizer' ),
				__( 'Token', 'ai-eshop-optimizer' ),
			)
		);
		foreach ( $rows as $r ) {
			$w->add_row(
				array(
					(string) $r['created_at'],
					(string) $r['kind'],
					(string) $r['source'],
					(string) ( $r['outcome'] !== null ? $r['outcome'] : $r['status'] ),
					(int) $r['item_count'],
					(float) $r['total'],
					(string) $r['currency'],
					$yn( $r['contains_restocked'] ),
					(string) $r['customer_email'],
					(int) $r['user_id'],
					$r['wc_order_id'] ? (int) $r['wc_order_id'] : '',
					$r['converted_order_id'] ? (int) $r['converted_order_id'] : '',
					(string) ( $r['converted_at'] !== null ? $r['converted_at'] : '' ),
					(int) $r['resume_count'],
					(string) $r['name'],
					(string) $r['token'],
				),
				array( 4 => 'integer', 5 => 'currency', 9 => 'integer', 13 => 'integer' )
			);
		}
		$w->stream_download( 'saved-abandoned-carts-' . gmdate( 'Ymd-His' ) . '.xlsx' );
	}

	
	
	

	public function render_subtab() {
		$have = self::table_exists( self::t_carts() );

		$last = get_option( 'aieo_carts_last_capture', array() );

		$this->render_template(
			'saved-abandoned-carts.php',
			array(
				'nonce'        => wp_create_nonce( AIEO_DMM::NONCE_ACTION ),
				'have_tables'  => $have,
				'kpis'         => self::compute_kpis(),
				'top_products' => self::top_abandoned_products( 25 ),
				'recent'       => self::recent_carts( 100 ),
				'last_capture' => is_array( $last ) ? $last : array(),
				'settings'     => array(
					'save_enabled'     => '0' !== (string) get_option( 'aieo_carts_save_enabled', '1' ),
					'save_label'       => (string) get_option( 'aieo_carts_save_label', '' ),
					'ttl_days'         => (int) get_option( 'aieo_carts_saved_ttl_days', 60 ),
					'show_on_checkout' => '1' === (string) get_option( 'aieo_carts_show_on_checkout', '0' ),
					'capture_enabled'  => '0' !== (string) get_option( 'aieo_carts_capture_enabled', '1' ),
				),
				'share_base'   => home_url( '/?aieo_cart=' ),
			)
		);
	}
}
