<?php

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

class AIEO_Marketing_Stats {

	
	const ORIGIN_MANUAL = "manual";
	const ORIGIN_ASSIST = "assisted";
	const ORIGIN_AI     = "rooster_ai";

	
	private static $wf_origin = array();

	
	public static function origin_label( $o ) {
		$m = array(
			self::ORIGIN_MANUAL => __( "Your own", "ai-eshop-optimizer" ),
			self::ORIGIN_ASSIST => __( "Rooster-assisted", "ai-eshop-optimizer" ),
			self::ORIGIN_AI     => __( "Rooster-driven", "ai-eshop-optimizer" ),
		);
		return $m[ (string) $o ] ?? (string) $o;
	}

	const HOOK       = 'aieo_marketing_stats_rebuild';
	const BUILT_OPT  = 'aieo_marketing_stats_built_at';
	const FRESH_SECS = 900;

	
	private static $booted = false;

	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_marketing_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' ), 32 );
		add_action( 'init', array( __CLASS__, 'maybe_schedule' ), 32 );
	}

	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() + 25 * MINUTE_IN_SECONDS, 6 * HOUR_IN_SECONDS, self::HOOK, array(), 'aieo' );
			}
		} elseif ( ! wp_next_scheduled( self::HOOK ) ) {
			wp_schedule_event( time() + 25 * 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;
		if ( ! self::ensure_table() ) {
			return array( 'ok' => false, 'reason' => 'no_table' );
		}
		
		
		if ( 1 !== (int) $wpdb->get_var( "SELECT GET_LOCK('aieo_marketing_rebuild', 0)" ) ) {
			return array( 'ok' => false, 'reason' => 'locked' );
		}
		try {
			return self::rebuild_locked();
		} finally {
			$wpdb->query( "SELECT RELEASE_LOCK('aieo_marketing_rebuild')" );
		}
	}

	private static function rebuild_locked() {
		global $wpdb;
		$rows = array();

		
		
		
		
		
		if ( class_exists( 'AIEO_Workflow_Stats' ) && class_exists( 'AIEO_Workflows' ) ) {
			AIEO_Workflow_Stats::maybe_refresh();
			$names = array();
			foreach ( AIEO_Workflows::list_all() as $w ) {
				$cfg = $w['trigger_config'] ?? null;
				if ( is_string( $cfg ) ) {
					$cfg = json_decode( $cfg, true );
				}
				$names[ (int) $w['id'] ] = array(
					'name'   => (string) $w['name'],
					'status' => (string) $w['status'],
					'origin' => ( is_array( $cfg ) && ! empty( $cfg['proposal_kind'] ) ) ? self::ORIGIN_AI : self::ORIGIN_MANUAL,
				);
			}
			self::$wf_origin = wp_list_pluck( $names, 'origin' );
			foreach ( AIEO_Workflow_Stats::get_all() as $wid => $s ) {
				$m = isset( $names[ (int) $wid ] ) ? $names[ (int) $wid ] : array( 'name' => 'Workflow #' . $wid, 'status' => '', 'origin' => self::ORIGIN_MANUAL );
				$rows[] = array(
					'initiative_type' => 'workflow',
					'initiative_id'   => (string) $wid,
					'label'           => $m['name'],
					'status'          => $m['status'],
					'channel'         => 'owned',
					'origin'          => $m['origin'],
					'spend'           => 0.0,
					'reach'           => (int) $s['sent'],
					'clicks'          => (int) $s['clicks'],
					'orders'          => (int) $s['coupons_redeemed'],
					'revenue'         => (float) $s['revenue'],
					'last_at'         => $s['last_send_at'],
				);
			}
		}

		
		
		$msg = $wpdb->prefix . 'aieo_msg_stats';
		if ( self::table_exists( $msg ) ) {
			
			$r = $wpdb->get_results(
				"SELECT trigger_id,
				        SUM(sent) sent, SUM(clicked) clicks, SUM(converted_strict) orders,
				        COALESCE(SUM(revenue_strict),0) revenue, MAX(stat_date) last_at
				 FROM `{$msg}`
				 WHERE trigger_id <> '' AND trigger_id NOT LIKE 'wf\\_%' AND trigger_id NOT LIKE 'campaign:%'
				 GROUP BY trigger_id",
				ARRAY_A
			);
			foreach ( (array) $r as $t ) {
				$rows[] = array(
					'initiative_type' => 'push_trigger',
					'origin'          => self::ORIGIN_MANUAL,
					'initiative_id'   => (string) $t['trigger_id'],
					'label'           => self::humanize( (string) $t['trigger_id'] ),
					'status'          => 'active',
					'channel'         => 'owned_push',
					'spend'           => 0.0,
					'reach'           => (int) $t['sent'],
					'clicks'          => (int) $t['clicks'],
					'orders'          => (int) $t['orders'],
					'revenue'         => (float) $t['revenue'],
					'last_at'         => $t['last_at'],
				);
			}
		}

		
		
		
		$bc = $wpdb->prefix . 'aieo_push_campaigns';
		$disp = $wpdb->prefix . 'aieo_push_dispatch';
		if ( self::table_exists( $bc ) ) {
			$has_disp = self::table_exists( $disp );
			
			$r = $wpdb->get_results( "SELECT campaign_id, name, sent_count, launched_at, status FROM `{$bc}`", ARRAY_A );
			foreach ( (array) $r as $c ) {
				$cid    = (int) $c['campaign_id'];
				$clicks = 0;
				$rev    = 0.0;
				$ord    = 0;
				if ( $has_disp ) {
					$dd = $wpdb->get_row( $wpdb->prepare(
						
						"SELECT SUM(state='clicked') clicks, SUM(CASE WHEN converted_order_id>0 THEN 1 ELSE 0 END) orders, COALESCE(SUM(revenue),0) rev FROM `{$disp}` WHERE trigger_id = %s",
						'campaign:' . $cid
					), ARRAY_A );
					$clicks = (int) ( $dd['clicks'] ?? 0 );
					$ord    = (int) ( $dd['orders'] ?? 0 );
					$rev    = (float) ( $dd['rev'] ?? 0 );
				}
				$rows[] = array(
					'initiative_type' => 'broadcast',
					'origin'          => self::ORIGIN_MANUAL,
					'initiative_id'   => (string) $cid,
					'label'           => (string) $c['name'],
					'status'          => (string) $c['status'],
					'channel'         => 'broadcast',
					'spend'           => 0.0,
					'reach'           => (int) $c['sent_count'],
					'clicks'          => $clicks,
					'orders'          => $ord,
					'revenue'         => $rev,
					'last_at'         => $c['launched_at'],
				);
			}
		}

		
		
		
		$camp = $wpdb->prefix . 'aieo_dmm_campaigns';
		$cost = $wpdb->prefix . 'aieo_attribution_cost';
		$os   = $wpdb->prefix . 'aieo_order_stats';
		if ( self::table_exists( $camp ) ) {
			$has_cost = self::table_exists( $cost );
			$has_os   = self::table_exists( $os ) && self::has_col( $os, 'orderutmcampaign' ) && self::has_col( $os, 'orderspend' );
			
			
			
			
			
			
			$camps = $wpdb->get_results( "SELECT id, slug, label, status FROM `{$camp}` WHERE status <> 'archived' AND slug NOT LIKE 'wf\\_%' AND slug NOT LIKE 'campaign:%'", ARRAY_A );
			foreach ( (array) $camps as $c ) {
				$slug  = (string) $c['slug'];
				
				
				
				
				$slug_norm = str_replace( '+', ' ', $slug );
				$spend = 0.0;
				if ( $has_cost ) {
					$spend = (float) $wpdb->get_var( $wpdb->prepare(
						
						"SELECT COALESCE(SUM(amount),0) FROM `{$cost}` WHERE dimension_type='campaign' AND REPLACE(dimension_value,'+',' ') = %s",
						$slug_norm
					) );
				}
				$rev = 0.0;
				$ord = 0;
				if ( $has_os ) {
					$d = $wpdb->get_row( $wpdb->prepare(
						
						"SELECT COALESCE(SUM(orderspend),0) rev, COUNT(*) orders FROM `{$os}` WHERE REPLACE(orderutmcampaign,'+',' ') = %s",
						$slug_norm
					), ARRAY_A );
					$rev = (float) ( $d['rev'] ?? 0 );
					$ord = (int) ( $d['orders'] ?? 0 );
				}
				
				if ( $spend <= 0 && $rev <= 0 && $ord <= 0 ) {
					continue;
				}
				$rows[] = array(
					'initiative_type' => 'ad_campaign',
					'origin'          => self::ORIGIN_MANUAL,
					'initiative_id'   => (string) $c['id'],
					'label'           => (string) ( $c['label'] ?: $slug ),
					'status'          => (string) $c['status'],
					'channel'         => 'paid',
					'spend'           => $spend,
					'reach'           => 0,
					'clicks'          => 0,
					'orders'          => $ord,
					'revenue'         => $rev,
					'last_at'         => null,
				);
			}
		}

		
		
		
		
		
		
		$sp  = $wpdb->prefix . 'aieo_social_posts';
		
		
		
		
		
		
		
		
		$spm = $wpdb->prefix . 'aieo_social_post_metrics';
		if ( self::table_exists( $sp ) && self::table_exists( $spm ) ) {
			
			$sgroups = $wpdb->get_results(
				"SELECT p.wf_id AS wf_id,
				        COALESCE(SUM(m.impressions),0) AS reach,
				        COALESCE(SUM(m.link_clicks),0) AS clicks,
				        COUNT(DISTINCT p.id) AS posts,
				        MAX(p.published_at) AS last_at
				   FROM `{$sp}` p
				   LEFT JOIN `{$spm}` m ON m.social_post_id = p.id
				  WHERE p.status = 'published'
				  GROUP BY p.wf_id",
				ARRAY_A
			);
			foreach ( (array) $sgroups as $g ) {
				if ( (int) $g['reach'] <= 0 && (int) $g['clicks'] <= 0 && (int) $g['posts'] <= 0 ) {
					continue;
				}
				$wf_id = (int) $g['wf_id'];
				$rows[] = array(
					'initiative_type' => 'social_post',
					'origin'          => $wf_id > 0 ? ( self::$wf_origin[ $wf_id ] ?? self::ORIGIN_ASSIST ) : self::ORIGIN_ASSIST,
					'initiative_id'   => (string) ( $wf_id > 0 ? 'wf_' . $wf_id : 'adhoc' ),
					'label'           => $wf_id > 0 ? ( 'Social — wf_' . $wf_id ) : 'Social — ad-hoc posts',
					'status'          => 'active',
					'channel'         => 'social',
					'spend'           => 0.0,
					'reach'           => (int) $g['reach'],
					'clicks'          => (int) $g['clicks'],
					'orders'          => 0,
					'revenue'         => 0.0,
					'last_at'         => $g['last_at'],
				);
			}
		}
		$sb = $wpdb->prefix . 'aieo_social_boosts';
		if ( self::table_exists( $sb ) ) {
			
			$boosts = $wpdb->get_results(
				"SELECT campaign_name, COALESCE(SUM(budget),0) AS spend, MAX(created_at) AS last_at
				   FROM `{$sb}` WHERE status = 'live' AND sandbox = 0
				  GROUP BY campaign_name",
				ARRAY_A
			);
			foreach ( (array) $boosts as $b ) {
				if ( (float) $b['spend'] <= 0 ) {
					continue;
				}
				$rows[] = array(
					'initiative_type' => 'social_boost',
					'origin'          => self::ORIGIN_AI,
					'initiative_id'   => (string) $b['campaign_name'],
					'label'           => 'Boost — ' . (string) $b['campaign_name'],
					'status'          => 'active',
					'channel'         => 'paid_social',
					'spend'           => (float) $b['spend'],
					'reach'           => 0,
					'clicks'          => 0,
					'orders'          => 0,
					'revenue'         => 0.0,
					'last_at'         => $b['last_at'],
				);
			}
		}

		$t   = self::table();
		$now = gmdate( 'Y-m-d H:i:s' );
		
		
		
		
		$wpdb->query( 'START TRANSACTION' );
		
		$ok = ( false !== $wpdb->query( "DELETE FROM `{$t}`" ) );
		if ( $ok ) {
			foreach ( $rows as $r ) {
				$res = $wpdb->insert( $t, array(
					'initiative_type' => substr( $r['initiative_type'], 0, 16 ),
					'initiative_id'   => substr( (string) $r['initiative_id'], 0, 64 ),
					'label'           => substr( (string) $r['label'], 0, 190 ),
					'status'          => substr( (string) $r['status'], 0, 16 ),
					'channel'         => substr( (string) $r['channel'], 0, 16 ),
					'origin'          => substr( (string) ( $r['origin'] ?? self::ORIGIN_MANUAL ), 0, 12 ),
					'spend'           => (float) $r['spend'],
					'reach'           => (int) $r['reach'],
					'clicks'          => (int) $r['clicks'],
					'orders'          => (int) $r['orders'],
					'revenue'         => (float) $r['revenue'],
					'last_at'         => $r['last_at'],
					'computed_at'     => $now,
				) );
				if ( false === $res ) {
					$ok = false;
					break;
				}
			}
		}
		if ( ! $ok ) {
			$wpdb->query( 'ROLLBACK' );
			return array( 'ok' => false, 'reason' => 'write_failed' );
		}
		$wpdb->query( 'COMMIT' );
		update_option( self::BUILT_OPT, $now, false );
		return array( 'ok' => true, 'initiatives' => count( $rows ) );
	}

	
	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}` ORDER BY revenue DESC, reach DESC", ARRAY_A ) as $r ) {
				$out[] = self::derive( $r );
			}
		}
		return $out;
	}

	
	public static function by_origin() {
		$blank = array( 'initiatives' => 0, 'spend' => 0.0, 'reach' => 0, 'clicks' => 0, 'orders' => 0, 'revenue' => 0.0 );
		$acc   = array(
			self::ORIGIN_MANUAL => $blank,
			self::ORIGIN_ASSIST => $blank,
			self::ORIGIN_AI     => $blank,
		);
		foreach ( self::get_all() as $r ) {
			$o = (string) ( $r['origin'] ?? self::ORIGIN_MANUAL );
			if ( ! isset( $acc[ $o ] ) ) {
				$o = self::ORIGIN_MANUAL;
			}
			$acc[ $o ]['initiatives']++;
			$acc[ $o ]['spend']   += (float) $r['spend'];
			$acc[ $o ]['reach']   += (int) $r['reach'];
			$acc[ $o ]['clicks']  += (int) $r['clicks'];
			$acc[ $o ]['orders']  += (int) $r['orders'];
			$acc[ $o ]['revenue'] += (float) $r['revenue'];
		}
		foreach ( $acc as $k => $v ) {
			$acc[ $k ]['label'] = self::origin_label( $k );
			$acc[ $k ]['roas']  = $v['spend'] > 0 ? round( $v['revenue'] / $v['spend'], 2 ) : null;
			$acc[ $k ]['rev_per_initiative'] = $v['initiatives'] > 0 ? round( $v['revenue'] / $v['initiatives'], 2 ) : 0.0;
		}
		
		$rooster_rev  = $acc[ self::ORIGIN_AI ]['revenue'] + $acc[ self::ORIGIN_ASSIST ]['revenue'];
		$rooster_init = $acc[ self::ORIGIN_AI ]['initiatives'] + $acc[ self::ORIGIN_ASSIST ]['initiatives'];
		$own          = $acc[ self::ORIGIN_MANUAL ];
		$total_rev    = $rooster_rev + $own['revenue'];
		$r_per        = $rooster_init > 0 ? $rooster_rev / $rooster_init : 0.0;
		$o_per        = $own['initiatives'] > 0 ? $own['revenue'] / $own['initiatives'] : 0.0;

		return array(
			'buckets' => $acc,
			'lift'    => array(
				'rooster_revenue'      => round( $rooster_rev, 2 ),
				'own_revenue'          => round( $own['revenue'], 2 ),
				'rooster_share_pct'    => $total_rev > 0 ? round( 100 * $rooster_rev / $total_rev, 1 ) : null,
				'rooster_per_initiative' => round( $r_per, 2 ),
				'own_per_initiative'   => round( $o_per, 2 ),
				
				'multiple'             => $o_per > 0 ? round( $r_per / $o_per, 2 ) : null,
				'comparable'           => ( $rooster_init > 0 && $own['initiatives'] > 0 ),
			),
		);
	}

	
	public static function totals() {
		$blank = array( 'spend' => 0.0, 'reach' => 0, 'clicks' => 0, 'orders' => 0, 'revenue' => 0.0, 'count' => 0 );
		$acc   = array( 'owned' => $blank, 'paid' => $blank );
		foreach ( self::get_all() as $r ) {
			$b = ( 'paid' === $r['channel'] ) ? 'paid' : 'owned';
			$acc[ $b ]['spend']   += (float) $r['spend'];
			$acc[ $b ]['reach']   += (int) $r['reach'];
			$acc[ $b ]['clicks']  += (int) $r['clicks'];
			$acc[ $b ]['orders']  += (int) $r['orders'];
			$acc[ $b ]['revenue'] += (float) $r['revenue'];
			$acc[ $b ]['count']++;
		}
		$acc['paid']['roas'] = $acc['paid']['spend'] > 0 ? round( $acc['paid']['revenue'] / $acc['paid']['spend'], 2 ) : null;
		return $acc;
	}

	private static function derive( $r ) {
		$spend   = (float) ( $r['spend'] ?? 0 );
		$revenue = (float) ( $r['revenue'] ?? 0 );
		$reach   = (int) ( $r['reach'] ?? 0 );
		$r['roas']         = $spend > 0 ? round( $revenue / $spend, 2 ) : null;        
		$r['rev_per_1k']   = $reach > 0 ? round( 1000 * $revenue / $reach, 2 ) : 0.0;  
		$r['ctr']          = $reach > 0 ? round( 100 * (int) ( $r['clicks'] ?? 0 ) / $reach, 1 ) : 0.0;
		$r['net']          = round( $revenue - $spend, 2 );
		$r['is_paid']      = ( 'paid' === ( $r['channel'] ?? '' ) );
		
		
		$bases = array(
			'workflow'     => 'coupon-redemption',
			'push_trigger' => 'strict-conversion',
			'broadcast'    => 'click+conversion',
			'ad_campaign'  => 'last-click',
		);
		$r['attribution_basis'] = isset( $bases[ (string) ( $r['initiative_type'] ?? '' ) ] ) ? $bases[ $r['initiative_type'] ] : '';
		return $r;
	}

	private static function humanize( $s ) {
		return ucwords( str_replace( array( '_', '-' ), ' ', (string) $s ) );
	}

	public static function ensure_table() {
		global $wpdb;
		$t = self::table();
		if ( self::table_exists( $t ) ) {
			
			
			
			
			self::ensure_origin_column( $t );
			return true;
		}
		$charset = aieo_get_charset_collate();
		
		$wpdb->query( "CREATE TABLE IF NOT EXISTS `{$t}` (
			`initiative_type` varchar(16) NOT NULL,
			`initiative_id` varchar(64) NOT NULL,
			`label` varchar(190) NOT NULL DEFAULT '',
			`status` varchar(16) NOT NULL DEFAULT '',
			`channel` varchar(16) NOT NULL DEFAULT '',
			`origin` varchar(12) NOT NULL DEFAULT 'manual',
			`spend` decimal(14,2) NOT NULL DEFAULT 0,
			`reach` int unsigned NOT NULL DEFAULT 0,
			`clicks` int unsigned NOT NULL DEFAULT 0,
			`orders` int unsigned NOT NULL DEFAULT 0,
			`revenue` decimal(14,2) NOT NULL DEFAULT 0,
			`last_at` datetime DEFAULT NULL,
			`computed_at` datetime DEFAULT NULL,
			PRIMARY KEY (`initiative_type`,`initiative_id`),
			KEY `revenue` (`revenue`)
		) {$charset};" );
		return self::table_exists( $t );
	}

	
	private static function ensure_origin_column( $t ) {
		global $wpdb;
		static $done = array();
		if ( isset( $done[ $t ] ) ) {
			return;
		}
		$done[ $t ] = true;
		$has = $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM `' . $t . '` LIKE %s', 'origin' ) );
		if ( ! $has ) {
			
			$wpdb->query( "ALTER TABLE `{$t}` ADD COLUMN `origin` varchar(12) NOT NULL DEFAULT 'manual' AFTER `channel`" );
		}
	}

	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 ) );
	}
}