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 ( $wf_id > 0 && class_exists( 'AIEO_Workflow_Advertised_Offers' ) ) { $tags = array_values( array_unique( array_merge( $tags, AIEO_Workflow_Advertised_Offers::campaign_slugs( $wf_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; } const ERROR_FORECASTS = 20; public static function error_of_kind( $kind ) { global $wpdb; $out = array( 'forecasts' => 0, 'ratio' => null ); $kind = sanitize_key( (string) $kind ); $t = self::table(); if ( '' === $kind || ! self::table_exists( $t ) ) { return $out; } $ratios = (array) $wpdb->get_col( $wpdb->prepare( "SELECT error_ratio FROM `{$t}` WHERE reconciled_at IS NOT NULL AND error_ratio IS NOT NULL AND components_json LIKE %s ORDER BY id DESC LIMIT %d", '%' . $wpdb->esc_like( '"kind":"' . $kind . '"' ) . '%', self::ERROR_FORECASTS ) ); if ( $ratios ) { $out['forecasts'] = count( $ratios ); $out['ratio'] = round( array_sum( array_map( 'floatval', $ratios ) ) / count( $ratios ), 4 ); } return $out; } public static function of_proposals( array $proposal_ids ) { global $wpdb; $ids = array_values( array_unique( array_filter( array_map( 'intval', $proposal_ids ), static function ( $id ) { return $id > 0; } ) ) ); $t = self::table(); if ( ! $ids || ! self::table_exists( $t ) ) { return array(); } $rows = (array) $wpdb->get_results( "SELECT id, proposal_id, predicted_orders, predicted_revenue, components_json, created_at FROM `{$t}` WHERE proposal_id IN (" . implode( ',', $ids ) . ') ORDER BY id ASC', ARRAY_A ); $out = array(); foreach ( $rows as $row ) { $parts = json_decode( (string) $row['components_json'], true ); $parts = is_array( $parts ) ? $parts : array(); $out[ (int) $row['proposal_id'] ] = array( 'proposal_id' => (int) $row['proposal_id'], 'forecast_id' => (int) $row['id'], 'created_gmt' => (string) $row['created_at'], 'days' => (int) ( $parts['days'] ?? 0 ), 'redemptions' => isset( $parts['redemptions'] ) ? (float) $parts['redemptions'] : (float) $row['predicted_orders'], 'predicted_revenue' => (float) $row['predicted_revenue'], ); } 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; } }