__( 'Get cross-session entry credit', 'ai-eshop-optimizer' ), 'description' => __( 'What each entry page earned INCLUDING customers who came back and bought days or weeks later, from `wp_aieo_entry_credit_daily` (WooCommerce persisted attribution, dated by the order). Use this to value blog posts and other content that rarely sells in the same visit, where the same-session landing report shows zero. Group by content type to compare blog posts against products and categories. This is NOT the same measure as the landing report\'s purchases, which is same-session only, and the two will not add up.', '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( 'manage_woocommerce' ); } private static function input_schema() { return array( 'default' => array(), 'type' => 'object', 'properties' => array( 'period' => array( 'type' => 'string', 'default' => 'last_30_days', 'enum' => PeriodHelper::ALLOWED, 'description' => 'Time window, by ORDER date. Defaults to last_30_days.', ), 'object_type' => array( 'type' => 'string', 'default' => '', 'description' => "Restrict to one content type, e.g. 'post' for blog posts, 'product', 'product_cat'. Empty returns every type.", ), 'group_by_type' => array( 'type' => 'boolean', 'default' => false, 'description' => 'Return one row per content type instead of one row per page.', ), 'limit' => array( 'type' => 'integer', 'default' => 25, 'minimum' => 1, 'maximum' => 200, 'description' => 'Max rows. The response says when it truncated.', ), ), ); } public static function execute( $input ) { global $wpdb; $input = is_array( $input ) ? $input : array(); $period = PeriodHelper::resolve( $input['period'] ?? 'last_30_days' ); $table = $wpdb->prefix . 'aieo_entry_credit_daily'; $exists = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $table ) ); if ( ! $exists ) { return array( 'period' => $period, 'rows' => array(), 'note' => __( 'wp_aieo_entry_credit_daily is not installed yet. It needs the nightly rollup, or bin/post-deploy-entry-credit-backfill.php for history.', 'ai-eshop-optimizer' ), ); } $type = isset( $input['object_type'] ) ? sanitize_key( (string) $input['object_type'] ) : ''; $group = ! empty( $input['group_by_type'] ); $limit = isset( $input['limit'] ) ? max( 1, min( 200, (int) $input['limit'] ) ) : 25; $where = 'WHERE `date` BETWEEN %s AND %s'; $args = array( gmdate( 'Y-m-d', strtotime( (string) $period['since'] . ' UTC' ) ), gmdate( 'Y-m-d', strtotime( (string) $period['until'] . ' UTC' ) ), ); if ( '' !== $type ) { $where .= ' AND object_type = %s'; $args[] = $type; } if ( $group ) { $rows = $wpdb->get_results( $wpdb->prepare( "SELECT IF(object_type = '', 'not_a_single_page', object_type) AS object_type, COUNT(DISTINCT entry_hash) AS pages, SUM(orders) AS orders, ROUND(SUM(revenue), 2) AS revenue FROM `{$table}` {$where} GROUP BY object_type ORDER BY revenue DESC", $args ), ARRAY_A ); $out = array(); foreach ( (array) $rows as $r ) { $out[] = array( 'object_type' => (string) $r['object_type'], 'pages' => (int) $r['pages'], 'orders' => (int) $r['orders'], 'revenue' => (float) $r['revenue'], ); } return array( 'period' => $period, 'measure' => 'cross_session', 'rows' => $out, 'note' => __( 'Cross-session credit, dated by the order. Not comparable with the same-session landing report; do not add the two together.', 'ai-eshop-optimizer' ), ); } $args[] = $limit + 1; $rows = $wpdb->get_results( $wpdb->prepare( "SELECT entry_path, object_type, object_id, SUM(orders) AS orders, ROUND(SUM(revenue), 2) AS revenue FROM `{$table}` {$where} GROUP BY entry_hash, entry_path, object_type, object_id ORDER BY revenue DESC LIMIT %d", $args ), ARRAY_A ); $truncated = count( (array) $rows ) > $limit; $rows = array_slice( (array) $rows, 0, $limit ); $out = array(); foreach ( $rows as $r ) { $oid = (int) $r['object_id']; $out[] = array( 'entry_path' => (string) $r['entry_path'], 'object_type' => (string) $r['object_type'], 'object_id' => $oid, 'title' => $oid > 0 ? (string) get_the_title( $oid ) : '', 'orders' => (int) $r['orders'], 'revenue' => (float) $r['revenue'], ); } return array( 'period' => $period, 'measure' => 'cross_session', 'rows' => $out, 'truncated' => $truncated, 'note' => $truncated ? __( 'More pages exist than the limit returned. Cross-session credit, dated by the order; not comparable with the same-session landing report.', 'ai-eshop-optimizer' ) : __( 'Cross-session credit, dated by the order. Not comparable with the same-session landing report; do not add the two together.', 'ai-eshop-optimizer' ), ); } }