__( 'Social publishing performance', 'ai-eshop-optimizer' ), 'description' => __( 'READ-ONLY, no AI credits. Per-platform organic social performance over a rolling window: posts_published and summed impressions, reach and link-clicks from the social post metrics, plus the most recent published posts. Params: days (default 30), platform (optional filter, e.g. facebook/instagram/tiktok). Use it to see which platforms are getting traction; it never publishes or changes anything.', 'ai-eshop-optimizer' ), 'category' => \AIEO\Claude\Bootstrap::CATEGORY, 'input_schema' => self::input_schema(), 'output_schema' => array( 'type' => 'object' ), 'execute_callback' => array( __CLASS__, 'execute' ), 'permission_callback' => array( __CLASS__, 'permission_check' ), 'meta' => array( 'annotations' => array( 'readonly' => true, 'idempotent' => true, ), 'show_in_rest' => true, ), ) ); } public static function permission_check( $input = null ) { unset( $input ); return current_user_can( defined( 'AIEO_CAPABILITY' ) ? AIEO_CAPABILITY : 'edit_pages' ); } private static function input_schema() { return array( 'default' => array(), 'type' => 'object', 'properties' => array( 'days' => array( 'type' => 'integer', 'default' => 30, 'minimum' => 1, 'maximum' => 365, 'description' => 'Look-back window in days. Defaults to 30.', ), 'platform' => array( 'type' => 'string', 'description' => 'Optional platform code to filter to (e.g. facebook, instagram, tiktok).', ), ), ); } private static function table_exists( $table ) { global $wpdb; return (bool) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s", $table ) ); } private static function columns( $table ) { global $wpdb; $cols = $wpdb->get_col( $wpdb->prepare( "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s", $table ) ); $out = array(); foreach ( (array) $cols as $c ) { $out[ strtolower( (string) $c ) ] = true; } return $out; } public static function execute( $input ) { global $wpdb; $input = is_array( $input ) ? $input : array(); $days = isset( $input['days'] ) ? (int) $input['days'] : 30; $days = max( 1, min( 365, $days ) ); $platform = isset( $input['platform'] ) ? trim( (string) $input['platform'] ) : ''; $posts_table = $wpdb->prefix . 'aieo_social_posts'; $metrics_table = $wpdb->prefix . 'aieo_social_post_metrics'; $result = array( 'window_days' => $days, 'by_platform' => array(), 'recent' => array(), ); if ( ! self::table_exists( $posts_table ) ) { $result['note'] = __( 'The social posts ledger is not installed on this site yet.', 'ai-eshop-optimizer' ); return $result; } $post_cols = self::columns( $posts_table ); $since = gmdate( 'Y-m-d H:i:s', time() - ( $days * DAY_IN_SECONDS ) ); $date_col = isset( $post_cols['published_at'] ) ? 'published_at' : ( isset( $post_cols['created_at'] ) ? 'created_at' : '' ); $where = array(); $where_args = array(); if ( $date_col ) { $where[] = "p.`{$date_col}` >= %s"; $where_args[] = $since; } if ( '' !== $platform && isset( $post_cols['platform'] ) ) { $where[] = 'p.platform = %s'; $where_args[] = $platform; } $where_sql = $where ? ( 'WHERE ' . implode( ' AND ', $where ) ) : ''; $has_metrics = self::table_exists( $metrics_table ); $m_cols = $has_metrics ? self::columns( $metrics_table ) : array(); if ( $has_metrics ) { $any = $wpdb->get_var( "SELECT 1 FROM `{$metrics_table}` LIMIT 1" ); if ( null === $any ) { $result['metrics_note'] = __( 'Impressions, reach and link clicks are shown as 0 because no engagement data is collected on this site — the platform insights job does not exist yet. Treat them as unknown, not as zero engagement. Publish counts and statuses below are real.', 'ai-eshop-optimizer' ); } } $metric_map = array( 'impressions' => isset( $m_cols['impressions'] ), 'reach' => isset( $m_cols['reach'] ), 'clicks' => isset( $m_cols['link_clicks'] ), ); $select_metrics = ''; if ( $has_metrics && isset( $m_cols['social_post_id'] ) ) { $select_metrics .= $metric_map['impressions'] ? ', COALESCE(SUM(m.impressions),0) AS impressions' : ', 0 AS impressions'; $select_metrics .= $metric_map['reach'] ? ', COALESCE(SUM(m.reach),0) AS reach' : ', 0 AS reach'; $select_metrics .= $metric_map['clicks'] ? ', COALESCE(SUM(m.link_clicks),0) AS clicks' : ', 0 AS clicks'; $join_sql = "LEFT JOIN {$metrics_table} m ON m.social_post_id = p.id"; } else { $select_metrics = ', 0 AS impressions, 0 AS reach, 0 AS clicks'; $join_sql = ''; } $published_expr = isset( $post_cols['status'] ) ? "SUM(CASE WHEN p.status = 'published' THEN 1 ELSE 0 END)" : 'COUNT(*)'; $sql = "SELECT p.platform AS platform, {$published_expr} AS posts {$select_metrics} FROM {$posts_table} p {$join_sql} {$where_sql} GROUP BY p.platform ORDER BY posts DESC"; if ( $where_args ) { $sql = $wpdb->prepare( $sql, $where_args ); } $rows = $wpdb->get_results( $sql, ARRAY_A ); foreach ( (array) $rows as $r ) { if ( '' !== $platform && isset( $r['platform'] ) && (string) $r['platform'] !== $platform ) { continue; } $result['by_platform'][] = array( 'platform' => (string) ( $r['platform'] ?? '' ), 'posts' => (int) ( $r['posts'] ?? 0 ), 'impressions' => (int) ( $r['impressions'] ?? 0 ), 'reach' => (int) ( $r['reach'] ?? 0 ), 'clicks' => (int) ( $r['clicks'] ?? 0 ), ); } $recent_where = array(); $recent_args = array(); if ( isset( $post_cols['status'] ) ) { $recent_where[] = "status = 'published'"; } if ( $date_col ) { $recent_where[] = "`{$date_col}` >= %s"; $recent_args[] = $since; } if ( '' !== $platform && isset( $post_cols['platform'] ) ) { $recent_where[] = 'platform = %s'; $recent_args[] = $platform; } $recent_where_sql = $recent_where ? ( 'WHERE ' . implode( ' AND ', $recent_where ) ) : ''; $order_col = $date_col ? $date_col : 'id'; $recent_sql = "SELECT platform, object_id, permalink, " . ( $date_col ? "`{$date_col}` AS published_at" : "'' AS published_at" ) . ", " . ( isset( $post_cols['status'] ) ? 'status' : "'' AS status" ) . " FROM {$posts_table} {$recent_where_sql} ORDER BY `{$order_col}` DESC LIMIT 20"; if ( $recent_args ) { $recent_sql = $wpdb->prepare( $recent_sql, $recent_args ); } $recent_rows = $wpdb->get_results( $recent_sql, ARRAY_A ); foreach ( (array) $recent_rows as $r ) { $result['recent'][] = array( 'platform' => (string) ( $r['platform'] ?? '' ), 'object_id' => isset( $r['object_id'] ) ? (int) $r['object_id'] : 0, 'permalink' => (string) ( $r['permalink'] ?? '' ), 'published_at' => (string) ( $r['published_at'] ?? '' ), 'status' => (string) ( $r['status'] ?? '' ), ); } if ( ! $has_metrics ) { $result['note'] = __( 'The social post metrics table is not installed yet; metric totals are reported as zero.', 'ai-eshop-optimizer' ); } return $result; } }