<?php

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

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

class AIEO_Workflow_Stats {

	const HOOK        = 'aieo_workflow_stats_rebuild';
	const BUILT_OPT   = 'aieo_workflow_stats_built_at';
	const FRESH_SECS  = 900; 

	
	const SCHEMA_OPTION  = 'aieo_workflow_stats_schema';
	const SCHEMA_VERSION = '2';

	
	private static $booted = false;

	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_workflow_stats';
	}

	public static function bootstrap() {
		if ( self::$booted ) {
			return;
		}
		self::$booted = true;
		add_action( self::HOOK, array( __CLASS__, 'rebuild' ) );
		add_action( 'admin_init', array( __CLASS__, 'maybe_schedule' ), 31 );
		add_action( 'init', array( __CLASS__, 'maybe_schedule' ), 31 );
	}

	public static function maybe_schedule() {
		if ( class_exists( 'AIEO_Schedule_Gate', false ) && ! AIEO_Schedule_Gate::due( __METHOD__ ) ) {
			return; 
		}
		static $checked = false;
		if ( $checked ) {
			return;
		}
		$checked = true;
		if ( function_exists( 'as_next_scheduled_action' ) && function_exists( 'as_schedule_recurring_action' ) ) {
			if ( false === as_next_scheduled_action( self::HOOK, null, 'aieo' ) ) {
				as_schedule_recurring_action( time() + 20 * MINUTE_IN_SECONDS, 6 * HOUR_IN_SECONDS, self::HOOK, array(), 'aieo' );
			}
		} elseif ( ! wp_next_scheduled( self::HOOK ) ) {
			wp_schedule_event( time() + 20 * MINUTE_IN_SECONDS, 'twicedaily', self::HOOK );
		}
	}

	
	public static function maybe_refresh() {
		$built = (int) strtotime( (string) get_option( self::BUILT_OPT, '' ) . ' UTC' );
		if ( $built <= 0 || ( time() - $built ) > self::FRESH_SECS ) {
			self::rebuild();
		}
	}

	
	public static function rebuild() {
		global $wpdb;
		$t = self::table();
		if ( ! self::ensure_table() ) {
			return array( 'ok' => false, 'reason' => 'no_table' );
		}
		$dispatch = $wpdb->prefix . 'aieo_push_dispatch';
		$grants   = function_exists( 'aieo_coupon_grants_table' ) ? aieo_coupon_grants_table() : ( $wpdb->prefix . 'aieo_coupon_grants' );
		$acc      = array(); 

		$row = function ( &$acc, $wid ) {
			$wid = (int) $wid;
			if ( $wid > 0 && ! isset( $acc[ $wid ] ) ) {
				$acc[ $wid ] = array( 'sent' => 0, 'push_clicks' => 0, 'opens' => 0, 'coupons_minted' => 0, 'coupons_sent' => 0, 'coupons_clicked' => 0, 'coupons_redeemed' => 0, 'revenue' => 0.0, 'last_send_at' => null );
			}
			return $wid;
		};

		
		if ( self::table_exists( $dispatch ) ) {
			
			$opens_sel = self::has_col( $dispatch, 'opened_at' ) ? 'SUM(opened_at IS NOT NULL)' : '0';
			
			$rows = $wpdb->get_results(
				"SELECT CAST(SUBSTRING_INDEX(trigger_id, '_', -1) AS UNSIGNED) AS wid,
				        SUM(state IN ('sent','clicked','dismissed','converted')) AS sent,
				        SUM(state = 'clicked') AS push_clicks,
				        {$opens_sel} AS opens,
				        MAX(sent_at) AS last_send
				 FROM `{$dispatch}`
				 WHERE trigger_id LIKE 'wf\\_%'
				 GROUP BY wid",
				ARRAY_A
			);
			foreach ( (array) $rows as $r ) {
				$wid = $row( $acc, $r['wid'] );
				if ( ! $wid ) {
					continue;
				}
				$acc[ $wid ]['sent']         = (int) $r['sent'];
				$acc[ $wid ]['push_clicks']  = (int) $r['push_clicks'];
				$acc[ $wid ]['opens']        = (int) $r['opens'];
				$acc[ $wid ]['last_send_at'] = $r['last_send'];
			}
		}

		
		
		
		
		if ( self::table_exists( $grants ) ) {
			$rev_join = '';
			$rev_col  = '0';
			
			if ( AIEO_Order_Store::uses_hpos() && self::table_exists( $wpdb->prefix . 'wc_orders' ) ) { 
				$rev_join = "LEFT JOIN `{$wpdb->prefix}wc_orders` o ON o.id = g.redeemed_order_id AND o.type = 'shop_order'";
				$rev_col  = 'o.total_amount';
			} else {
				$rev_join = "LEFT JOIN `{$wpdb->postmeta}` o ON o.post_id = g.redeemed_order_id AND o.meta_key = '_order_total'";
				$rev_col  = 'o.meta_value';
			}
			
			$rows = $wpdb->get_results(
				"SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(g.source, '_', 2), '_', -1) AS UNSIGNED) AS wid,
				        COUNT(*) AS minted,
				        SUM(g.sent_at IS NOT NULL) AS csent,
				        SUM(g.clicked_at IS NOT NULL) AS cclick,
				        SUM(g.status = 'redeemed') AS credeem,
				        COALESCE(SUM(CASE WHEN g.status = 'redeemed' THEN {$rev_col} ELSE 0 END), 0) AS revenue
				 FROM `{$grants}` g
				 {$rev_join}
				 WHERE g.source LIKE 'wf\\_%'
				   AND g.source NOT LIKE '%\\_test' AND g.source NOT LIKE '%\\_preview'
				 GROUP BY wid",
				ARRAY_A
			);
			foreach ( (array) $rows as $r ) {
				$wid = $row( $acc, $r['wid'] );
				if ( ! $wid ) {
					continue;
				}
				$acc[ $wid ]['coupons_minted']   = (int) $r['minted'];
				$acc[ $wid ]['coupons_sent']     = (int) $r['csent'];
				$acc[ $wid ]['coupons_clicked']  = (int) $r['cclick'];
				$acc[ $wid ]['coupons_redeemed'] = (int) $r['credeem'];
				$acc[ $wid ]['revenue']          = (float) $r['revenue'];
			}
		}

		
		$os = $wpdb->prefix . 'aieo_order_stats';
		if ( self::table_exists( $os ) && self::has_col( $os, 'orderutmcampaign' ) && self::has_col( $os, 'orderspend' ) ) {
			
			$rows = $wpdb->get_results(
				"SELECT CAST(SUBSTRING_INDEX(orderutmcampaign, '_', -1) AS UNSIGNED) AS wid,
				        COALESCE(SUM(orderspend), 0) AS rev, COUNT(*) AS orders
				 FROM `{$os}`
				 WHERE orderutmcampaign LIKE 'wf\\_%'
				 GROUP BY wid",
				ARRAY_A
			);
			foreach ( (array) $rows as $r ) {
				$wid = $row( $acc, $r['wid'] );
				if ( ! $wid ) {
					continue;
				}
				$acc[ $wid ]['lastclick_revenue'] = (float) $r['rev'];
				$acc[ $wid ]['lastclick_orders']  = (int) $r['orders'];
			}
		}

		
		
		
		
		$orders = array(); 
		$take   = static function ( $rows ) use ( &$orders, &$acc, $row ) {
			foreach ( (array) $rows as $r ) {
				$wid = $row( $acc, $r['wid'] );
				$oid = (int) $r['oid'];
				if ( $wid && $oid > 0 && ! isset( $orders[ $wid ][ $oid ] ) ) {
					$orders[ $wid ][ $oid ] = (float) $r['amount'];
				}
			}
		};
		if ( self::table_exists( $grants ) ) {
			
			$take( $wpdb->get_results(
				"SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(g.source, '_', 2), '_', -1) AS UNSIGNED) AS wid,
				        g.redeemed_order_id AS oid, COALESCE({$rev_col}, 0) AS amount
				 FROM `{$grants}` g
				 {$rev_join}
				 WHERE g.status = 'redeemed' AND g.redeemed_order_id > 0 AND g.source LIKE 'wf\\_%'
				   AND g.source NOT LIKE '%\\_test' AND g.source NOT LIKE '%\\_preview'",
				ARRAY_A
			) );
		}
		if ( self::table_exists( $os ) && self::has_col( $os, 'orderutmcampaign' ) && self::has_col( $os, 'orderspend' ) && self::has_col( $os, 'orderid' ) ) {
			
			$take( $wpdb->get_results(
				"SELECT CAST(SUBSTRING_INDEX(orderutmcampaign, '_', -1) AS UNSIGNED) AS wid, orderid AS oid, orderspend AS amount
				 FROM `{$os}` WHERE orderutmcampaign LIKE 'wf\\_%' AND orderid > 0",
				ARRAY_A
			) );
		}
		foreach ( $orders as $wid => $by_order ) {
			$acc[ $wid ]['attributed_revenue'] = round( array_sum( $by_order ), 2 );
			$acc[ $wid ]['attributed_orders']  = count( $by_order );
		}

		
		
		$wpdb->query( "DELETE FROM `{$t}`" );
		$now = gmdate( 'Y-m-d H:i:s' );
		foreach ( $acc as $wid => $m ) {
			$wpdb->insert( $t, array(
				'workflow_id'       => (int) $wid,
				'sent'              => (int) $m['sent'],
				'push_clicks'       => (int) $m['push_clicks'],
				'email_opens'       => (int) ( $m['opens'] ?? 0 ),
				'coupons_minted'    => (int) $m['coupons_minted'],
				'coupons_sent'      => (int) $m['coupons_sent'],
				'coupons_clicked'   => (int) $m['coupons_clicked'],
				'coupons_redeemed'  => (int) $m['coupons_redeemed'],
				'revenue'           => (float) $m['revenue'],
				'lastclick_revenue' => (float) ( $m['lastclick_revenue'] ?? 0 ),
				'lastclick_orders'  => (int) ( $m['lastclick_orders'] ?? 0 ),
				'attributed_revenue' => (float) ( $m['attributed_revenue'] ?? 0 ),
				'attributed_orders'  => (int) ( $m['attributed_orders'] ?? 0 ),
				'last_send_at'      => $m['last_send_at'],
				'computed_at'       => $now,
			) );
		}
		update_option( self::BUILT_OPT, $now, false );
		return array( 'ok' => true, 'workflows' => count( $acc ) );
	}

	
	public static function get( $workflow_id ) {
		global $wpdb;
		$t = self::table();
		$base = array( 'sent' => 0, 'push_clicks' => 0, 'email_opens' => 0, 'coupons_minted' => 0, 'coupons_sent' => 0, 'coupons_clicked' => 0, 'coupons_redeemed' => 0, 'revenue' => 0.0, 'lastclick_revenue' => 0.0, 'lastclick_orders' => 0, 'attributed_revenue' => 0.0, 'attributed_orders' => 0, 'last_send_at' => null );
		if ( self::table_exists( $t ) ) {
			$r = $wpdb->get_row( $wpdb->prepare(
				
				"SELECT * FROM `{$t}` WHERE workflow_id = %d",
				(int) $workflow_id
			), ARRAY_A );
			if ( $r ) {
				$base = array_merge( $base, $r );
			}
		}
		return self::derive( $base );
	}

	
	public static function get_all() {
		global $wpdb;
		$out = array();
		$t   = self::table();
		if ( self::table_exists( $t ) ) {
			
			foreach ( (array) $wpdb->get_results( "SELECT * FROM `{$t}`", ARRAY_A ) as $r ) {
				$out[ (int) $r['workflow_id'] ] = self::derive( $r );
			}
		}
		return $out;
	}

	
	private static function derive( $r ) {
		$sent     = (int) ( $r['sent'] ?? 0 );
		$clicks   = (int) ( $r['coupons_clicked'] ?? 0 ) + (int) ( $r['push_clicks'] ?? 0 );
		$redeemed = (int) ( $r['coupons_redeemed'] ?? 0 );
		$revenue  = (float) ( $r['revenue'] ?? 0 );
		$opens    = (int) ( $r['email_opens'] ?? 0 );
		$r['opens']        = $opens;
		$r['open_rate']    = $sent > 0 ? round( 100 * $opens / $sent, 1 ) : 0.0;    
		$r['clicks']       = $clicks;
		$r['ctr']          = $sent > 0 ? round( 100 * $clicks / $sent, 1 ) : 0.0;   
		$r['redeem_rate']  = $sent > 0 ? round( 100 * $redeemed / $sent, 1 ) : 0.0; 
		$r['rev_per_send'] = $sent > 0 ? round( $revenue / $sent, 2 ) : 0.0;
		return $r;
	}

	

	public static function ensure_table() {
		global $wpdb;
		$t = self::table();
		if ( ! self::table_exists( $t ) ) {
			$charset = aieo_get_charset_collate();
			
			$wpdb->query( "CREATE TABLE IF NOT EXISTS `{$t}` (
				`workflow_id` bigint unsigned NOT NULL,
				`sent` int unsigned NOT NULL DEFAULT 0,
				`push_clicks` int unsigned NOT NULL DEFAULT 0,
				`email_opens` int unsigned NOT NULL DEFAULT 0,
				`coupons_minted` int unsigned NOT NULL DEFAULT 0,
				`coupons_sent` int unsigned NOT NULL DEFAULT 0,
				`coupons_clicked` int unsigned NOT NULL DEFAULT 0,
				`coupons_redeemed` int unsigned NOT NULL DEFAULT 0,
				`revenue` decimal(14,2) NOT NULL DEFAULT 0,
				`lastclick_revenue` decimal(14,2) NOT NULL DEFAULT 0,
				`lastclick_orders` int unsigned NOT NULL DEFAULT 0,
				`attributed_revenue` decimal(14,2) NOT NULL DEFAULT 0,
				`attributed_orders` int unsigned NOT NULL DEFAULT 0,
				`last_send_at` datetime DEFAULT NULL,
				`computed_at` datetime DEFAULT NULL,
				PRIMARY KEY (`workflow_id`)
			) {$charset};" );
		}
		
		
		
		if ( self::table_exists( $t ) && self::SCHEMA_VERSION !== (string) get_option( self::SCHEMA_OPTION, '' ) && AIEO_Schema_Lock::acquire( self::SCHEMA_OPTION ) ) {
			
			if ( ! self::has_col( $t, 'email_opens' ) ) {
				
				$wpdb->query( "ALTER TABLE `{$t}` ADD COLUMN `email_opens` int unsigned NOT NULL DEFAULT 0 AFTER `push_clicks`" );
			}
			
			if ( ! self::has_col( $t, 'attributed_revenue' ) ) {
				
				$wpdb->query( "ALTER TABLE `{$t}` ADD COLUMN `attributed_revenue` decimal(14,2) NOT NULL DEFAULT 0 AFTER `lastclick_orders`, ADD COLUMN `attributed_orders` int unsigned NOT NULL DEFAULT 0 AFTER `attributed_revenue`" );
			}
			update_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION, false );
		}
		return self::table_exists( $t );
	}

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

	private static function has_col( $t, $c ) {
		global $wpdb;
		return (bool) $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$t}` LIKE %s", $c ) );
	}
}