<?php

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

class AIEO_Campaign_Forecasts {

	
	const RECONCILE_HOOK = 'aieo_social_forecast_reconcile';

	
	const HOST_HOOK = 'aieo_marketing_stats_rebuild';

	
	const SETTLE_DAYS = 3;

	
	private static $col_cache = array();

	
	public static function init() {
		add_action( self::RECONCILE_HOOK, array( __CLASS__, 'reconcile' ) );

		if ( class_exists( 'AIEO_Marketing_Stats' ) || has_action( self::HOST_HOOK ) ) {
			
			add_action( self::HOST_HOOK, array( __CLASS__, 'reconcile' ), 20 );
			return;
		}

		
		if ( function_exists( 'as_next_scheduled_action' ) && function_exists( 'as_schedule_recurring_action' ) ) {
			if ( false === as_next_scheduled_action( self::RECONCILE_HOOK, null, 'aieo' ) ) {
				as_schedule_recurring_action( time() + HOUR_IN_SECONDS, DAY_IN_SECONDS, self::RECONCILE_HOOK, array(), 'aieo' );
			}
		} elseif ( ! wp_next_scheduled( self::RECONCILE_HOOK ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', self::RECONCILE_HOOK );
		}
	}

	
	
	

	
	public static function snapshot( array $args ) {
		global $wpdb;

		if ( ! self::ensure_table() ) {
			return 0;
		}

		$components = isset( $args['components_json'] ) ? $args['components_json'] : '';
		if ( is_array( $components ) ) {
			$components = wp_json_encode( $components );
		}

		
		
		
		$data = array(
			'wf_id'              => isset( $args['wf_id'] ) ? (int) $args['wf_id'] : 0,
			'proposal_id'        => isset( $args['proposal_id'] ) ? (int) $args['proposal_id'] : 0,
			'vector_slug'        => substr( (string) ( $args['vector'] ?? '' ), 0, 20 ),
			'object_type'        => substr( (string) ( $args['object_type'] ?? '' ), 0, 20 ),
			'object_id'          => isset( $args['object_id'] ) ? (int) $args['object_id'] : 0,
			'components_json'    => is_string( $components ) ? $components : '',
			'weights_version'    => substr( (string) ( $args['weights_version'] ?? '' ), 0, 20 ),
			'predicted_reach'    => isset( $args['predicted_reach'] ) ? (int) $args['predicted_reach'] : 0,
			'predicted_ctr'      => isset( $args['predicted_ctr'] ) ? (float) $args['predicted_ctr'] : 0.0,
			'predicted_sessions' => isset( $args['predicted_sessions'] ) ? (int) $args['predicted_sessions'] : 0,
			'predicted_orders'   => isset( $args['predicted_orders'] ) ? (int) $args['predicted_orders'] : 0,
			'predicted_revenue'  => isset( $args['predicted_revenue'] ) ? (float) $args['predicted_revenue'] : 0.0,
			'predicted_cost'     => isset( $args['predicted_cost'] ) ? (float) $args['predicted_cost'] : 0.0,
			'predicted_roi'      => isset( $args['predicted_roi'] ) ? (float) $args['predicted_roi'] : 0.0,
			'confidence'         => substr( (string) ( $args['confidence'] ?? '' ), 0, 10 ),
			'model_version'      => substr( (string) ( $args['model_version'] ?? 'v1' ), 0, 20 ),
			'created_at'         => current_time( 'mysql', true ),
		);

		$ok = $wpdb->insert(
			self::table(),
			$data,
			array( '%d', '%d', '%s', '%s', '%d', '%s', '%s', '%d', '%f', '%d', '%d', '%f', '%f', '%f', '%s', '%s', '%s' )
		);

		return $ok ? (int) $wpdb->insert_id : 0;
	}

	
	
	

	
	public static function reconcile() {
		global $wpdb;

		$out = array( 'reconciled' => 0 );
		$t   = self::table();
		if ( ! self::table_exists( $t ) ) {
			return array( 'reconciled' => 0, 'skipped' => 'no_table' );
		}

		$cutoff = gmdate( 'Y-m-d H:i:s', (int) current_time( 'timestamp', true ) - self::SETTLE_DAYS * DAY_IN_SECONDS );

		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT id, wf_id, object_id, predicted_revenue
			   FROM `{$t}`
			  WHERE reconciled_at IS NULL AND created_at < %s
			  ORDER BY id ASC
			  LIMIT 500",
			$cutoff
		), ARRAY_A );

		if ( empty( $rows ) ) {
			return $out;
		}

		
		$ws          = $wpdb->prefix . 'aieo_workflow_stats';
		$has_ws      = self::table_exists( $ws );
		$ws_reach    = $has_ws && self::has_col( $ws, 'social_impressions' );
		$ws_sessions = $has_ws && self::has_col( $ws, 'social_clicks' );
		$ws_posts    = $has_ws && self::has_col( $ws, 'posts_published' );
		$ws_any      = $ws_reach || $ws_sessions || $ws_posts;

		$os          = $wpdb->prefix . 'aieo_order_stats';
		$has_os      = self::table_exists( $os )
			&& self::has_col( $os, 'orderutmcampaign' )
			&& self::has_col( $os, 'orderspend' );

		$now = current_time( 'mysql', true );

		foreach ( $rows as $r ) {
			$id       = (int) $r['id'];
			$wf_id    = (int) $r['wf_id'];
			$obj_id   = (int) $r['object_id'];
			$pred_rev = (float) $r['predicted_revenue'];

			$update  = array( 'reconciled_at' => $now );
			$formats = array( '%s' );

			
			if ( $ws_any && $wf_id > 0 ) {
				$sel = array();
				if ( $ws_posts ) {
					$sel[] = 'COALESCE(posts_published,0) AS a_posts';
				}
				if ( $ws_reach ) {
					$sel[] = 'COALESCE(social_impressions,0) AS a_reach';
				}
				if ( $ws_sessions ) {
					$sel[] = 'COALESCE(social_clicks,0) AS a_sessions';
				}
				
				$w = $wpdb->get_row( $wpdb->prepare(
					'SELECT ' . implode( ',', $sel ) . " FROM `{$ws}` WHERE workflow_id = %d",
					$wf_id
				), ARRAY_A );
				if ( $w ) {
					if ( $ws_posts ) {
						$update['actual_posts'] = (int) $w['a_posts'];
						$formats[]              = '%d';
					}
					if ( $ws_reach ) {
						$update['actual_reach'] = (int) $w['a_reach'];
						$formats[]              = '%d';
					}
					if ( $ws_sessions ) {
						
						$update['actual_sessions'] = (int) $w['a_sessions'];
						$formats[]                 = '%d';
					}
				}
			}

			
			
			
			$actual_rev = null;
			if ( $has_os ) {
				$tags = array();
				if ( $wf_id > 0 ) {
					$tags[] = 'wf_' . $wf_id;
				}
				if ( $obj_id > 0 ) {
					$tags[] = 'social_' . $obj_id;
				}
				if ( $tags ) {
					$place = implode( ',', array_fill( 0, count( $tags ), '%s' ) );
					
					$d = $wpdb->get_row( $wpdb->prepare(
						"SELECT COALESCE(SUM(orderspend),0) AS rev, COUNT(*) AS orders
						   FROM `{$os}`
						  WHERE orderutmcampaign IN ({$place})",
						...$tags
					), ARRAY_A );
					if ( $d ) {
						$actual_rev              = (float) $d['rev'];
						$update['actual_revenue'] = $actual_rev;
						$formats[]                = '%f';
						$update['actual_orders']  = (int) $d['orders'];
						$formats[]                = '%d';
					}
				}
			}

			
			
			if ( null !== $actual_rev && $pred_rev > 0 ) {
				$update['error_ratio'] = round( $actual_rev / $pred_rev, 4 );
				$formats[]             = '%f';
			}

			$done = $wpdb->update( $t, $update, array( 'id' => $id ), $formats, array( '%d' ) );
			if ( false !== $done ) {
				$out['reconciled']++;
			}
		}

		return $out;
	}

	
	
	

	
	public static function table() {
		global $wpdb;
		return function_exists( 'aieo_campaign_forecasts_table' )
			? aieo_campaign_forecasts_table()
			: $wpdb->prefix . 'aieo_campaign_forecasts';
	}

	
	public static function ensure_table() {
		$t = self::table();
		if ( self::table_exists( $t ) ) {
			return true;
		}
		
		
		
		if ( ! function_exists( 'aieo_social_install_schema' ) && defined( 'AIEO_DIR' ) ) {
			$file = AIEO_DIR . 'includes/dmm/sql/social-schema.php';
			if ( file_exists( $file ) ) {
				require_once $file;
			}
		}
		if ( function_exists( 'aieo_social_install_schema' ) ) {
			aieo_social_install_schema();
		}
		return self::table_exists( $t );
	}

	private static function table_exists( $t ) {
		global $wpdb;
		if ( isset( self::$col_cache[ 'T:' . $t ] ) ) {
			return self::$col_cache[ 'T:' . $t ];
		}
		$exists = (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) === $t;
		return self::$col_cache[ 'T:' . $t ] = $exists;
	}

	private static function has_col( $t, $c ) {
		global $wpdb;
		$key = $t . '::' . $c;
		if ( isset( self::$col_cache[ $key ] ) ) {
			return self::$col_cache[ $key ];
		}
		
		$has = (bool) $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$t}` LIKE %s", $c ) );
		return self::$col_cache[ $key ] = $has;
	}
}