<?php

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

class AIEO_DMM_Attribution extends AIEO_DMM_Module_Base {

    public function get_id()       { return 'attribution'; }
    public function get_label()    { return __('Dashboard', 'ai-eshop-optimizer'); }
    public function get_group()    { return 'attribution'; }
    public function get_priority() { return 10; } 

    public function is_pro_locked() { return true; }
    public function get_pro_promo() {
        return array(
            'title'       => __('Attribution Dashboard', 'ai-eshop-optimizer'),
            'description' => __('Know exactly which traffic source, recommendation rail, or archive page drove every add-to-cart and order. Replace gut feel with hard data on what is working — and stop spending on what is not.', 'ai-eshop-optimizer'),
            'bullets'     => array(
                __('Per-channel adds, orders, and conversion rates (paid, organic, push, social, referral, direct).', 'ai-eshop-optimizer'),
                __('Reco-assist split — see which sources benefit most from on-site recommendations.', 'ai-eshop-optimizer'),
                __('Branch / surface / slot breakdowns with searchable detail rows + CSV export.', 'ai-eshop-optimizer'),
            ),
        );
    }

    public function register_hooks() {
        add_action('admin_post_aieo_attribution_clear',  array($this, 'handle_clear'));
        add_action('admin_post_aieo_attribution_export', array($this, 'handle_export'));
        add_action('wp_ajax_aieo_attribution_rows',      array($this, 'ajax_rows'));
    }

    public function enqueue_admin_assets() {
        
        
        $rel  = 'assets/js/aieo-attribution-admin.js';
        $url  = plugins_url($rel, AIEO_FILE);
        $path = dirname(AIEO_FILE) . '/' . $rel;
        $ver  = file_exists($path) ? filemtime($path) : (defined('AIEO_VERSION') ? AIEO_VERSION : false);
        wp_enqueue_script('aieo-attribution-admin', $url, array('jquery'), $ver, true);
        wp_localize_script('aieo-attribution-admin', 'aieoAttrAdmin', array(
            'ajaxUrl' => admin_url('admin-ajax.php'),
            'nonce'   => wp_create_nonce('aieo_attr_admin'),
            'i18n'    => array(
                'loading'           => __('Loading…', 'ai-eshop-optimizer'),
                'empty'             => __('No reco-assisted adds match this filter.', 'ai-eshop-optimizer'),
                'error'             => __('Failed to load rows.', 'ai-eshop-optimizer'),
                'searchPlaceholder' => __('Search product or order…', 'ai-eshop-optimizer'),
                'badgeLabel'        => __('Badge', 'ai-eshop-optimizer'),
                'badgeAny'          => __('Any badge', 'ai-eshop-optimizer'),
                'badgeGift'         => __('Free gift', 'ai-eshop-optimizer'),
                'badgeMonthly'      => __('Monthly offer', 'ai-eshop-optimizer'),
                
                'showing'           => __('Showing %1$d–%2$d of %3$d', 'ai-eshop-optimizer'),
                'perPage'           => __('Rows per page', 'ai-eshop-optimizer'),
                'columns'           => array(
                    'status'         => __('Status', 'ai-eshop-optimizer'),
                    'bought'         => __('Bought / added', 'ai-eshop-optimizer'),
                    'closingSource'  => __('Closed via', 'ai-eshop-optimizer'),
                    'assistType'     => __('Reco type', 'ai-eshop-optimizer'),
                    'assistSurface'  => __('Reco surface', 'ai-eshop-optimizer'),
                    'assistPosition' => __('Slot', 'ai-eshop-optimizer'),
                    'anchor'         => __('Shown on / under', 'ai-eshop-optimizer'),
                    'gap'            => __('Gap', 'ai-eshop-optimizer'),
                    'addedAt'        => __('Added at', 'ai-eshop-optimizer'),
                ),
                'inCart'             => __('In cart', 'ai-eshop-optimizer'),
                
                'purchased'          => __('Purchased %s', 'ai-eshop-optimizer'),
            ),
        ));
    }

    public function render_subtab() {
        global $wpdb;

        $range_days = isset($_GET['attr_days']) ? max(1, (int) $_GET['attr_days']) : 30;
        $since      = gmdate('Y-m-d H:i:s', time() - ($range_days * DAY_IN_SECONDS));

        $addcart = AIEO_Attribution::get_table_name();
        $reco    = AIEO_Attribution::get_reco_table_name();

        echo '<div class="aieo-dmm-section">';
        echo '<h2>' . esc_html__('Recommendation attribution', 'ai-eshop-optimizer') . '</h2>';
        echo '<p class="description">'
            . esc_html__('Products bought after a recommendation impression or click, grouped by surface, slot, and anchor product.', 'ai-eshop-optimizer')
            . '</p>';

        if (isset($_GET['cleared'])) {
            echo '<div class="notice notice-success is-dismissible"><p>'
               . esc_html__('Attribution tables cleared.', 'ai-eshop-optimizer') . '</p></div>';
        }

        $this->render_toolbar_card($range_days);
        $this->render_stats_card($addcart, $reco, $since);
        $this->render_traffic_source_card($addcart, $reco, $since);
        $this->render_goals_channel_card($range_days);
        $this->render_branch_breakdown_card($since);
        $this->render_surface_card($addcart, $since);
        $this->render_slot_card($addcart, $reco, $since);
        $this->render_top_products_card($addcart, $reco, $since);
        $this->render_top_anchors_card($addcart, $reco, $since);
        $this->render_detail_card($range_days);

        echo '</div>';
    }

    
    private function render_goals_channel_card($range_days) {
        global $wpdb;
        $t = $wpdb->prefix . 'aieo_landing_campaign_daily';
        if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $t)) !== $t) {
            return;
        }
        $since = gmdate('Y-m-d', time() - $range_days * DAY_IN_SECONDS);
        
        $has_ladder = (bool) $wpdb->get_var("SHOW COLUMNS FROM `{$t}` LIKE 'purchases'");
        $ladder_sel = $has_ladder ? ', SUM(engaged_sessions) AS engaged, SUM(purchases) AS purchases' : ', 0 AS engaged, 0 AS purchases';
        
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT channel, referrer_host,
                    SUM(sessions) AS sessions, SUM(cart_adds) AS cart_adds, SUM(goals) AS goals{$ladder_sel}
               FROM `{$t}`
              WHERE `date` >= %s
              GROUP BY channel, referrer_host
              ORDER BY purchases DESC, goals DESC, sessions DESC
              LIMIT 25",
            $since
        ), ARRAY_A);

        echo '<div class="aieo-dmm-card">';
        echo '<h3>' . esc_html__('Goal conversions by traffic source', 'ai-eshop-optimizer') . '</h3>';
        echo '<p class="description">'
            . esc_html__('Sessions → goal conversions per channel and referring site, from the canonical landing/campaign rollup. Goals are the conversions of content sites and portals (form submissions, newsletter signups) — pick them on SEO → General → Conversion goals.', 'ai-eshop-optimizer')
            . '</p>';
        if (empty($rows)) {
            echo '<p>' . esc_html__('No data yet — the rollup builds nightly once session tracking is enabled.', 'ai-eshop-optimizer') . '</p></div>';
            return;
        }
        
        
        
        echo '<table class="widefat striped"><thead><tr>'
            . '<th>' . esc_html__('Channel', 'ai-eshop-optimizer') . '</th>'
            . '<th>' . esc_html__('Referring site', 'ai-eshop-optimizer') . '</th>'
            . '<th class="num">' . esc_html__('Sessions', 'ai-eshop-optimizer') . '</th>'
            . '<th class="num">' . esc_html__('Engaged', 'ai-eshop-optimizer') . '</th>'
            . '<th class="num">' . esc_html__('Goals', 'ai-eshop-optimizer') . '</th>'
            . '<th class="num">' . esc_html__('Cart adds', 'ai-eshop-optimizer') . '</th>'
            . '<th class="num">' . esc_html__('Purchases', 'ai-eshop-optimizer') . '</th>'
            . '<th class="num">' . esc_html__('Conv. %', 'ai-eshop-optimizer') . '</th>'
            . '</tr></thead><tbody>';
        foreach ($rows as $r) {
            $s     = (int) $r['sessions'];
            $g     = (int) $r['goals'];
            $p     = (int) $r['purchases'];
            $conv  = $p > 0 ? $p : $g; 
            $label = class_exists('AIEO_Attribution_Block') ? AIEO_Attribution_Block::channel_label($r['channel']) : $r['channel'];
            echo '<tr>'
                . '<td>' . esc_html($label) . '</td>'
                . '<td>' . esc_html('' !== (string) $r['referrer_host'] ? $r['referrer_host'] : '—') . '</td>'
                . '<td class="num">' . esc_html(number_format_i18n($s)) . '</td>'
                . '<td class="num">' . esc_html(number_format_i18n((int) $r['engaged'])) . '</td>'
                . '<td class="num">' . esc_html(number_format_i18n($g)) . '</td>'
                . '<td class="num">' . esc_html(number_format_i18n((int) $r['cart_adds'])) . '</td>'
                . '<td class="num">' . esc_html(number_format_i18n($p)) . '</td>'
                . '<td class="num">' . esc_html($s > 0 ? number_format_i18n(100 * $conv / $s, 2) : '0') . '</td>'
                . '</tr>';
        }
        echo '</tbody></table></div>';
    }

    
    private function render_traffic_source_card($addcart, $reco, $since) {
        global $wpdb;
        $sessions = $wpdb->prefix . 'aieo_sessions_statics';

        
        $exists = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $sessions)) === $sessions;
        if (!$exists) { return; }

        
        
        
        

        
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT
                COALESCE(NULLIF(s.entry_channel, ''), 'unattributed') AS channel,
                COUNT(*)                                    AS adds,
                SUM(e.order_id > 0)                         AS converted,
                COUNT(DISTINCT IF(e.order_id > 0, e.order_id, NULL)) AS orders,
                SUM(EXISTS (
                    SELECT 1 FROM {$reco} r
                    WHERE r.session_id = e.session_id
                      AND r.target_pid = e.product_id
                      AND r.ts < e.ts_added
                      AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)
                ))                                          AS with_assist,
                ROUND(100 * SUM(e.order_id > 0) / COUNT(*), 1) AS conv_pct,
                COUNT(DISTINCT s.entry_campaign)            AS campaigns_seen
             FROM {$addcart} e
             LEFT JOIN {$sessions} s
               ON s.session_id COLLATE utf8mb4_uca1400_ai_ci
                = e.session_id COLLATE utf8mb4_uca1400_ai_ci
             WHERE e.ts_added >= %s
             GROUP BY channel
             ORDER BY adds DESC",
            $since
        ));

        echo '<div class="aieo-dmm-card" style="background:#eff6ff;border-left:3px solid #1d4ed8;">';
        echo '<h3>' . esc_html__('Traffic source — channel breakdown', 'ai-eshop-optimizer') . '</h3>';
        echo '<p class="description" style="margin:0 0 12px;font-size:12px;color:#1e3a8a;">'
            . esc_html__('Add-to-cart events grouped by how the visitor arrived: web push (our own dispatches), email newsletter, paid Google / Facebook campaigns (gclid / fbclid auto-detected), organic search referrers, social referrers, direct traffic. Any URL with a UTM tag captures the campaign name in the right-most column. Push notifications get auto-tagged with aieo_src=push + dispatch_id.', 'ai-eshop-optimizer')
            . '</p>';

        if (empty($rows)) {
            echo '<p style="font-size:12px;color:#9ca3af;">'
                . esc_html__('No add-to-cart events yet in this range — channel breakdown will populate as traffic flows.', 'ai-eshop-optimizer') . '</p>';
            echo '</div>';
            return;
        }

        echo '<table class="widefat striped aieo-attr-sortable-table" style="font-size:12px;">';
        echo '<thead><tr>';
        echo '<th data-sort-type="text">' . esc_html__('Channel', 'ai-eshop-optimizer') . '</th>';
        echo '<th data-sort-type="num">'  . esc_html__('Adds', 'ai-eshop-optimizer') . '</th>';
        echo '<th data-sort-type="num">'  . esc_html__('w/ Reco assist', 'ai-eshop-optimizer') . '</th>';
        echo '<th data-sort-type="num">'  . esc_html__('Converted lines', 'ai-eshop-optimizer') . '</th>';
        echo '<th data-sort-type="num">'  . esc_html__('Orders', 'ai-eshop-optimizer') . '</th>';
        echo '<th data-sort-type="num">'  . esc_html__('Conv %', 'ai-eshop-optimizer') . '</th>';
        echo '<th data-sort-type="num">'  . esc_html__('Campaigns', 'ai-eshop-optimizer') . '</th>';
        echo '</tr></thead><tbody>';

        $colors = array(
            'web_push'        => '#7c3aed',
            'newsletter'      => '#d97706',
            'paid_google'     => '#1d4ed8',
            'paid_facebook'   => '#1877f2',
            'paid_microsoft'  => '#00a4ef',
            'paid_tiktok'     => '#000000',
            'paid_other'      => '#475569',
            'organic_search'  => '#15803d',
            'social'          => '#db2777',
            'referral'        => '#ca8a04',
            'direct'          => '#6b7280',
            'internal'        => '#9ca3af',
            'unattributed'    => '#9ca3af',
        );
        foreach ($rows as $r) {
            $ch = (string) $r->channel;
            $color = $colors[$ch] ?? '#374151';
            echo '<tr>';
            echo '<td><span style="display:inline-block;width:8px;height:8px;border-radius:50%;background:' . esc_attr($color) . ';margin-right:6px;"></span><strong>' . esc_html($ch) . '</strong></td>';
            echo '<td>' . esc_html(number_format_i18n((int) $r->adds)) . '</td>';
            echo '<td>' . esc_html(number_format_i18n((int) $r->with_assist)) . '</td>';
            echo '<td>' . esc_html(number_format_i18n((int) $r->converted)) . '</td>';
            echo '<td>' . esc_html(number_format_i18n((int) $r->orders)) . '</td>';
            echo '<td>' . esc_html((string) $r->conv_pct) . '%</td>';
            echo '<td>' . esc_html(number_format_i18n((int) $r->campaigns_seen)) . '</td>';
            echo '</tr>';
        }
        echo '</tbody></table>';

        
        
        $top_campaigns = $wpdb->get_results($wpdb->prepare(
            "SELECT
                COALESCE(NULLIF(s.entry_channel, ''), 'unattributed') AS channel,
                s.entry_campaign AS campaign,
                COUNT(*) AS adds,
                COUNT(DISTINCT IF(e.order_id > 0, e.order_id, NULL)) AS orders
             FROM {$addcart} e
             JOIN {$sessions} s
               ON s.session_id COLLATE utf8mb4_uca1400_ai_ci
                = e.session_id COLLATE utf8mb4_uca1400_ai_ci
             WHERE e.ts_added >= %s
               AND s.entry_campaign <> ''
             GROUP BY channel, campaign
             ORDER BY orders DESC, adds DESC
             LIMIT 15",
            $since
        ));
        if (!empty($top_campaigns)) {
            echo '<h4 style="margin:14px 0 6px;font-size:13px;">' . esc_html__('Top campaigns (by orders)', 'ai-eshop-optimizer') . '</h4>';
            echo '<table class="widefat striped" style="font-size:12px;">';
            echo '<thead><tr>';
            echo '<th>' . esc_html__('Channel', 'ai-eshop-optimizer') . '</th>';
            echo '<th>' . esc_html__('Campaign', 'ai-eshop-optimizer') . '</th>';
            echo '<th>' . esc_html__('Adds', 'ai-eshop-optimizer') . '</th>';
            echo '<th>' . esc_html__('Orders', 'ai-eshop-optimizer') . '</th>';
            echo '</tr></thead><tbody>';
            foreach ($top_campaigns as $r) {
                echo '<tr>';
                echo '<td><code>' . esc_html((string) $r->channel) . '</code></td>';
                echo '<td>' . esc_html((string) $r->campaign) . '</td>';
                echo '<td>' . esc_html(number_format_i18n((int) $r->adds)) . '</td>';
                echo '<td>' . esc_html(number_format_i18n((int) $r->orders)) . '</td>';
                echo '</tr>';
            }
            echo '</tbody></table>';
        }

        echo '</div>';
    }

    
    private function render_branch_breakdown_card($since) {
        global $wpdb;
        $attr_tbl   = $wpdb->prefix . 'aieo_dmm_offer_attributions';
        $branch_tbl = $wpdb->prefix . 'aieo_dmm_branches';
        if (!aieo_dmm_table_exists($attr_tbl) || !aieo_dmm_table_exists($branch_tbl)) { return; }

        
        $any_branch = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$branch_tbl}");
        if ($any_branch === 0) { return; }

        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT
                COALESCE(b.label, 'eshop / unmapped') AS branch_label,
                a.entry_branch_id                     AS branch_id,
                COUNT(*)                              AS attribution_rows,
                COUNT(DISTINCT a.order_id)            AS orders,
                ROUND(SUM(a.applied_value), 2)        AS applied_value_total,
                ROUND(SUM(IFNULL(a.actual_cost, 0)), 2) AS actual_cost_total,
                ROUND(SUM(a.order_total) / NULLIF(COUNT(DISTINCT a.order_id), 0), 2) AS avg_order_value
             FROM {$attr_tbl} a
             LEFT JOIN {$branch_tbl} b ON b.id = a.entry_branch_id
             WHERE a.fired_at >= %s
             GROUP BY a.entry_branch_id
             ORDER BY orders DESC",
            $since
        ));

        echo '<div class="aieo-dmm-card" style="background:#fef3c7;border-left:3px solid #ca8a04;">';
        echo '<h3>' . esc_html__('Per-branch attribution breakdown', 'ai-eshop-optimizer') . '</h3>';
        echo '<p class="description" style="margin:0 0 12px;font-size:12px;color:#92400e;">'
            . esc_html__('Offer attributions grouped by the physical branch the order came from. Resolved at fire-time from the order\'s `soft1_branches` post-meta against the SoftOne ID column on Branch Scanner. NULL = eshop-only orders or branches without a SoftOne mapping yet.', 'ai-eshop-optimizer')
            . '</p>';

        if (empty($rows)) {
            echo '<p style="font-size:12px;color:#92400e;">'
                . esc_html__('No per-branch attribution data in this range. Set the SoftOne ID for each branch in Branch Scanner, then trigger a few in-store orders to populate.', 'ai-eshop-optimizer') . '</p>';
            echo '</div>';
            return;
        }

        echo '<table class="widefat striped" style="font-size:12px;">';
        echo '<thead><tr>';
        echo '<th>' . esc_html__('Branch', 'ai-eshop-optimizer') . '</th>';
        echo '<th>' . esc_html__('Attribution rows', 'ai-eshop-optimizer') . '</th>';
        echo '<th>' . esc_html__('Orders', 'ai-eshop-optimizer') . '</th>';
        echo '<th>' . esc_html__('Applied value (€)', 'ai-eshop-optimizer') . '</th>';
        echo '<th>' . esc_html__('Actual cost (€)', 'ai-eshop-optimizer') . '</th>';
        echo '<th>' . esc_html__('Avg order value (€)', 'ai-eshop-optimizer') . '</th>';
        echo '</tr></thead><tbody>';
        foreach ($rows as $r) {
            $is_unmapped = ($r->branch_id === null || (int) $r->branch_id === 0);
            $row_style = $is_unmapped ? ' style="color:#9ca3af;"' : '';
            echo '<tr' . $row_style . '>'; 
            echo '<td><strong>' . esc_html((string) $r->branch_label) . '</strong></td>';
            echo '<td>' . esc_html(number_format_i18n((int) $r->attribution_rows)) . '</td>';
            echo '<td>' . esc_html(number_format_i18n((int) $r->orders)) . '</td>';
            echo '<td>' . esc_html(number_format_i18n((float) $r->applied_value_total, 2)) . '</td>';
            echo '<td>' . esc_html(number_format_i18n((float) $r->actual_cost_total, 2)) . '</td>';
            echo '<td>' . esc_html(number_format_i18n((float) $r->avg_order_value, 2)) . '</td>';
            echo '</tr>';
        }
        echo '</tbody></table>';
        echo '</div>';
    }

    
    
    

    private function render_toolbar_card($current) {
        $post_url = admin_url('admin-post.php');
        echo '<div class="aieo-dmm-card">';
        echo '<div style="display:flex;gap:12px;flex-wrap:wrap;align-items:flex-end;">';

        echo '<form method="get" style="display:inline-flex;gap:8px;align-items:flex-end;">';
        echo '<input type="hidden" name="page" value="ai-eshop-dmm">';
        echo '<input type="hidden" name="dmm_tab" value="' . esc_attr($this->get_id()) . '">';
        echo '<p style="margin:0;"><label><strong>' . esc_html__('Range', 'ai-eshop-optimizer') . '</strong><br>';
        echo '<select name="attr_days">';
        foreach (array(7, 14, 30, 60, 90, 180, 365) as $d) {
            echo '<option value="' . esc_attr($d) . '"' . selected($current, $d, false) . '>'
                . sprintf(
                    
                    esc_html__('Last %d days', 'ai-eshop-optimizer'),
                    (int) $d
                ) . '</option>';
        }
        echo '</select></label></p>';
        submit_button(__('Apply', 'ai-eshop-optimizer'), 'secondary', 'submit', false);
        echo '</form>';

        echo '<div style="flex:1;"></div>';

        echo '<form method="post" action="' . esc_url($post_url) . '" style="display:inline;">';
        wp_nonce_field('aieo_attribution_export');
        echo '<input type="hidden" name="action" value="aieo_attribution_export">';
        echo '<input type="hidden" name="attr_days" value="' . esc_attr($current) . '">';
        echo '<button type="submit" class="button button-secondary aieo-dmm-btn-fixed">⬇ ' . esc_html__('Export XLSX', 'ai-eshop-optimizer') . '</button>';
        echo '</form>';

        $clear_msg = __('Clear ALL attribution data (add-cart events + reco events)? This cannot be undone.', 'ai-eshop-optimizer');
        echo '<form method="post" action="' . esc_url($post_url) . '" style="display:inline;" onsubmit="' . esc_attr("return confirm('" . esc_js($clear_msg) . "');") . '">';
        wp_nonce_field('aieo_attribution_clear');
        echo '<input type="hidden" name="action" value="aieo_attribution_clear">';
        echo '<button type="submit" class="button aieo-dmm-btn-fixed" style="color:#b32d2e;border-color:#b32d2e;">🗑 ' . esc_html__('Clear tables', 'ai-eshop-optimizer') . '</button>';
        echo '</form>';

        echo '</div></div>';
    }

    
    
    

    private function render_stats_card($addcart, $reco, $since) {
        global $wpdb;

        $total_adds = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$addcart} WHERE ts_added >= %s", $since
        ));
        $orders = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT order_id) FROM {$addcart} WHERE order_id > 0 AND ts_added >= %s", $since
        ));
        $adds_converted = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$addcart} WHERE order_id > 0 AND ts_added >= %s", $since
        ));
        $impressions = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$reco} WHERE event_type = 'impression' AND ts >= %s", $since
        ));
        $reco_clicks = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$reco} WHERE event_type = 'reco_click' AND ts >= %s", $since
        ));
        $adds_with_assist_all = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT e.id)
             FROM {$addcart} e
             WHERE e.ts_added >= %s
               AND EXISTS (
                   SELECT 1 FROM {$reco} r
                   WHERE r.session_id = e.session_id
                     AND r.target_pid = e.product_id
                     AND r.ts < e.ts_added
                     AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)
               )",
            $since
        ));
        $adds_with_assist_converted = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT e.id)
             FROM {$addcart} e
             WHERE e.order_id > 0 AND e.ts_added >= %s
               AND EXISTS (
                   SELECT 1 FROM {$reco} r
                   WHERE r.session_id = e.session_id
                     AND r.target_pid = e.product_id
                     AND r.ts < e.ts_added
                     AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)
               )",
            $since
        ));
        $assist_pct = $total_adds > 0 ? round(100 * $adds_with_assist_all / $total_adds, 1) : 0;

        echo '<div class="aieo-dmm-card">';
        echo '<h3>' . esc_html__('Headline numbers', 'ai-eshop-optimizer') . '</h3>';
        echo '<div class="aieo-attr-tiles">';
        $this->tile(__('Add-to-cart events', 'ai-eshop-optimizer'), number_format_i18n($total_adds));
        $this->tile(__('… with reco assist', 'ai-eshop-optimizer'), number_format_i18n($adds_with_assist_all) . ' (' . $assist_pct . '%)');
        $this->tile(__('Orders attributed', 'ai-eshop-optimizer'), number_format_i18n($orders));
        $this->tile(__('Purchased lines', 'ai-eshop-optimizer'), number_format_i18n($adds_converted));
        $this->tile(__('… purchased w/ assist', 'ai-eshop-optimizer'), number_format_i18n($adds_with_assist_converted));
        $this->tile(__('Reco impressions', 'ai-eshop-optimizer'), number_format_i18n($impressions));
        $this->tile(__('Reco click-throughs', 'ai-eshop-optimizer'), number_format_i18n($reco_clicks));
        echo '</div></div>';
    }

    private function tile($label, $value) {
        echo '<div class="aieo-attr-tile">';
        echo '<div class="aieo-attr-tile-label">' . esc_html($label) . '</div>';
        echo '<div class="aieo-attr-tile-value">' . esc_html($value) . '</div>';
        echo '</div>';
    }

    
    
    

    private function render_surface_card($addcart, $since) {
        global $wpdb;
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT
                source,
                COUNT(*)                                     AS adds,
                SUM(order_id > 0)                            AS converted,
                ROUND(100 * SUM(order_id > 0) / COUNT(*), 1) AS conv_pct
             FROM {$addcart}
             WHERE ts_added >= %s
             GROUP BY source
             ORDER BY adds DESC",
            $since
        ));
        echo '<div class="aieo-dmm-card">';
        echo '<h3>' . esc_html__('Conversion by surface', 'ai-eshop-optimizer') . '</h3>';
        echo '<p class="description" style="margin:0 0 8px;">'
           . esc_html__('Which surface closes the most sales — add-to-cart → order conversion rate per surface.', 'ai-eshop-optimizer')
           . '</p>';
        echo '<table class="widefat striped aieo-attr-sortable-table"><thead><tr>';
        echo '<th data-sort-type="text">' . esc_html__('Surface', 'ai-eshop-optimizer') . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Adds', 'ai-eshop-optimizer')       . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Converted', 'ai-eshop-optimizer')  . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Conv. rate', 'ai-eshop-optimizer') . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '</tr></thead><tbody>';
        if (empty($rows)) {
            echo '<tr><td colspan="4" style="text-align:center;color:#646970;">'
               . esc_html__('No events in this range yet.', 'ai-eshop-optimizer') . '</td></tr>';
        } else {
            foreach ($rows as $r) {
                echo '<tr>';
                echo '<td data-sort-value="' . esc_attr($r->source) . '"><code>' . esc_html($r->source) . '</code> <span style="color:#646970;font-size:12px;">' . esc_html($this->surface_label($r->source)) . '</span></td>';
                echo '<td data-sort-value="' . (int) $r->adds . '">' . (int) $r->adds . '</td>';
                echo '<td data-sort-value="' . (int) $r->converted . '">' . (int) $r->converted . '</td>';
                echo '<td data-sort-value="' . esc_attr($r->conv_pct) . '">' . esc_html($r->conv_pct) . '%</td>';
                echo '</tr>';
            }
        }
        echo '</tbody></table></div>';
    }

    
    
    

    private function render_slot_card($addcart, $reco, $since) {
        global $wpdb;
        
        
        
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT
                r.surface,
                r.position,
                SUM(r.event_type = 'impression')                         AS impressions,
                SUM(r.event_type = 'reco_click')                         AS clicks,
                COUNT(DISTINCT CASE WHEN e.id IS NOT NULL THEN r.id END) AS assisted_adds,
                SUM(CASE WHEN e.order_id > 0 THEN 1 ELSE 0 END)          AS assisted_purchases
             FROM {$reco} r
             LEFT JOIN {$addcart} e
               ON e.session_id = r.session_id
              AND e.product_id = r.target_pid
              AND e.ts_added   > r.ts
              AND e.ts_added   < DATE_ADD(r.ts, INTERVAL 7 DAY)
             WHERE r.ts >= %s AND r.position > 0
             GROUP BY r.surface, r.position
             ORDER BY r.surface, r.position",
            $since
        ));

        echo '<div class="aieo-dmm-card">';
        echo '<h3>' . esc_html__('Slot performance', 'ai-eshop-optimizer') . '</h3>';
        echo '<p class="description" style="margin:0 0 8px;">'
           . esc_html__('Which slot within each recommendation block converts best. Reads: of shoppers who saw a card in slot #N, what fraction ended up adding it to cart.', 'ai-eshop-optimizer')
           . '</p>';
        echo '<table class="widefat striped aieo-attr-sortable-table"><thead><tr>';
        echo '<th data-sort-type="text">' . esc_html__('Surface', 'ai-eshop-optimizer')              . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Slot', 'ai-eshop-optimizer')                 . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Impressions', 'ai-eshop-optimizer')          . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Clicks', 'ai-eshop-optimizer')               . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Assisted adds', 'ai-eshop-optimizer')        . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Assisted purchases', 'ai-eshop-optimizer')   . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Add rate', 'ai-eshop-optimizer')             . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '</tr></thead><tbody>';
        if (empty($rows)) {
            echo '<tr><td colspan="7" style="text-align:center;color:#646970;">'
               . esc_html__('No slot-level data yet.', 'ai-eshop-optimizer') . '</td></tr>';
        } else {
            foreach ($rows as $r) {
                $imp  = (int) $r->impressions;
                $rate = $imp > 0 ? round(100 * ((int) $r->assisted_adds) / $imp, 1) : 0;
                echo '<tr>';
                echo '<td data-sort-value="' . esc_attr($r->surface) . '"><code>' . esc_html($r->surface) . '</code></td>';
                echo '<td data-sort-value="' . esc_attr((int) $r->position) . '" style="text-align:center;">#' . esc_html((int) $r->position) . '</td>';
                echo '<td data-sort-value="' . esc_attr($imp) . '">' . esc_html($imp) . '</td>';
                echo '<td data-sort-value="' . esc_attr((int) $r->clicks) . '">' . esc_html((int) $r->clicks) . '</td>';
                echo '<td data-sort-value="' . esc_attr((int) $r->assisted_adds) . '">' . esc_html((int) $r->assisted_adds) . '</td>';
                echo '<td data-sort-value="' . esc_attr((int) $r->assisted_purchases) . '">' . esc_html((int) $r->assisted_purchases) . '</td>';
                echo '<td data-sort-value="' . esc_attr($rate) . '">' . esc_html($rate) . '%</td>';
                echo '</tr>';
            }
        }
        echo '</tbody></table></div>';
    }

    
    
    

    private function render_top_products_card($addcart, $reco, $since) {
        global $wpdb;
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT
                e.product_id                                        AS pid,
                COUNT(DISTINCT e.id)                                AS assisted_adds,
                SUM(CASE WHEN e.order_id > 0 THEN 1 ELSE 0 END)     AS assisted_purchases,
                COUNT(DISTINCT r.surface)                           AS distinct_surfaces,
                GROUP_CONCAT(DISTINCT r.surface ORDER BY r.surface SEPARATOR ', ') AS surfaces
             FROM {$addcart} e
             INNER JOIN {$reco} r
               ON r.session_id = e.session_id
              AND r.target_pid = e.product_id
              AND r.ts         < e.ts_added
              AND e.ts_added   < DATE_ADD(r.ts, INTERVAL 7 DAY)
             WHERE e.ts_added >= %s
             GROUP BY e.product_id
             ORDER BY assisted_adds DESC
             LIMIT 20",
            $since
        ));

        echo '<div class="aieo-dmm-card">';
        echo '<h3>' . esc_html__('Top products discovered via recommendations', 'ai-eshop-optimizer') . '</h3>';
        echo '<p class="description" style="margin:0 0 8px;">'
           . esc_html__('Products most often added to cart after appearing as a recommendation — ranked by number of reco-assisted adds.', 'ai-eshop-optimizer')
           . '</p>';
        echo '<table class="widefat striped aieo-attr-sortable-table"><thead><tr>';
        echo '<th data-sort-type="text">' . esc_html__('Product', 'ai-eshop-optimizer')           . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Assisted adds', 'ai-eshop-optimizer')     . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Purchases', 'ai-eshop-optimizer')         . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="text">' . esc_html__('Shown on surfaces', 'ai-eshop-optimizer') . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '</tr></thead><tbody>';
        if (empty($rows)) {
            echo '<tr><td colspan="4" style="text-align:center;color:#646970;">'
               . esc_html__('No assisted adds yet.', 'ai-eshop-optimizer') . '</td></tr>';
        } else {
            foreach ($rows as $r) {
                $title = (string) get_the_title((int) $r->pid);
                echo '<tr>';
                
                echo '<td data-sort-value="' . esc_attr($title) . '">' . $this->product_link((int) $r->pid) . '</td>';
                echo '<td data-sort-value="' . esc_attr((int) $r->assisted_adds) . '">' . esc_html((int) $r->assisted_adds) . '</td>';
                echo '<td data-sort-value="' . esc_attr((int) $r->assisted_purchases) . '">' . esc_html((int) $r->assisted_purchases) . '</td>';
                echo '<td data-sort-value="' . esc_attr($r->surfaces) . '"><span style="font-size:11px;color:#646970;">' . esc_html($r->surfaces) . '</span></td>';
                echo '</tr>';
            }
        }
        echo '</tbody></table></div>';
    }

    
    
    

    private function render_top_anchors_card($addcart, $reco, $since) {
        global $wpdb;
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT
                r.anchor_pid                                      AS anchor,
                COUNT(DISTINCT e.id)                              AS assisted_adds,
                SUM(CASE WHEN e.order_id > 0 THEN 1 ELSE 0 END)   AS assisted_purchases,
                COUNT(DISTINCT r.target_pid)                      AS unique_targets
             FROM {$addcart} e
             INNER JOIN {$reco} r
               ON r.session_id = e.session_id
              AND r.target_pid = e.product_id
              AND r.ts         < e.ts_added
              AND e.ts_added   < DATE_ADD(r.ts, INTERVAL 7 DAY)
             WHERE e.ts_added >= %s
               AND r.anchor_pid > 0
             GROUP BY r.anchor_pid
             ORDER BY assisted_adds DESC
             LIMIT 20",
            $since
        ));

        echo '<div class="aieo-dmm-card">';
        echo '<h3>' . esc_html__('Top anchor products', 'ai-eshop-optimizer') . '</h3>';
        echo '<p class="description" style="margin:0 0 8px;">'
           . esc_html__('Product pages whose recommendation blocks drive the most cross-discovery — the "traffic hubs" of your catalog.', 'ai-eshop-optimizer')
           . '</p>';
        echo '<table class="widefat striped aieo-attr-sortable-table"><thead><tr>';
        echo '<th data-sort-type="text">' . esc_html__('Anchor product', 'ai-eshop-optimizer')    . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Assisted adds', 'ai-eshop-optimizer')     . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Purchases', 'ai-eshop-optimizer')         . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '<th data-sort-type="num">'  . esc_html__('Distinct targets', 'ai-eshop-optimizer')  . ' <span class="aieo-attr-dt-sort-ind">⇅</span></th>';
        echo '</tr></thead><tbody>';
        if (empty($rows)) {
            echo '<tr><td colspan="4" style="text-align:center;color:#646970;">'
               . esc_html__('No anchor-attributed adds yet.', 'ai-eshop-optimizer') . '</td></tr>';
        } else {
            foreach ($rows as $r) {
                $title = (string) get_the_title((int) $r->anchor);
                echo '<tr>';
                
                echo '<td data-sort-value="' . esc_attr($title) . '">' . $this->product_link((int) $r->anchor) . '</td>';
                echo '<td data-sort-value="' . esc_attr((int) $r->assisted_adds) . '">' . esc_html((int) $r->assisted_adds) . '</td>';
                echo '<td data-sort-value="' . esc_attr((int) $r->assisted_purchases) . '">' . esc_html((int) $r->assisted_purchases) . '</td>';
                echo '<td data-sort-value="' . esc_attr((int) $r->unique_targets) . '">' . esc_html((int) $r->unique_targets) . '</td>';
                echo '</tr>';
            }
        }
        echo '</tbody></table></div>';
    }

    
    
    

    private function render_detail_card($range_days) {
        echo '<div class="aieo-dmm-card">';
        echo '<h3>' . esc_html__('Products added / bought after seeing them as recommendations', 'ai-eshop-optimizer') . '</h3>';
        echo '<p class="description" style="margin:0 0 8px;">'
           . esc_html__('One row per (cart-add, reco touch) pair. Sort headers, paginate, or search by product name / order.', 'ai-eshop-optimizer')
           . '</p>';
        echo '<div id="aieo-attr-detail-table" class="aieo-attr-detail-table" data-days="' . (int) $range_days . '"></div>';
        echo '</div>';
    }

    
    
    

    public function ajax_rows() {
        check_ajax_referer('aieo_attr_admin', 'nonce');
        if (!current_user_can(AIEO_CAPABILITY)) { wp_send_json_error(array('message' => 'denied'), 403); }

        global $wpdb;
        $addcart = AIEO_Attribution::get_table_name();
        $reco    = AIEO_Attribution::get_reco_table_name();

        $days     = isset($_POST['days']) ? max(1, (int) $_POST['days']) : 30;
        $since    = gmdate('Y-m-d H:i:s', time() - ($days * DAY_IN_SECONDS));
        $page     = max(1, isset($_POST['page']) ? (int) $_POST['page'] : 1);
        $per_page = max(10, min(200, isset($_POST['per_page']) ? (int) $_POST['per_page'] : 25));
        $search   = isset($_POST['search']) ? sanitize_text_field((string) wp_unslash($_POST['search'])) : '';
        
        $badge_in = isset($_POST['badge']) ? sanitize_key((string) wp_unslash($_POST['badge'])) : '';
        $badge_clauses = array(
            'sale'    => 'e.bcs_was_on_sale = 1',
            'new'     => 'e.bcs_is_new = 1',
            'gift'    => 'e.bcs_gift_offer_id IS NOT NULL AND e.bcs_gift_offer_id > 0',
            'monthly' => 'e.bcs_monthly_offer_id IS NOT NULL AND e.bcs_monthly_offer_id > 0',
        );
        $badge_where = isset($badge_clauses[$badge_in]) ? ' AND ' . $badge_clauses[$badge_in] : '';

        
        
        $valid_sort = array(
            'status'         => 'order_id',
            'bought_pid'     => 'bought_pid',
            'closing_source' => 'closing_source',
            'assist_type'    => 'assist_type',
            'assist_surface' => 'assist_surface',
            'assist_position'=> 'assist_position',
            'anchor_pid'     => 'anchor_pid',
            'bought_ts'      => 'bought_ts',
            'gap_minutes'    => 'gap_minutes',
        );
        $sort_key = isset($_POST['sort']) ? sanitize_key((string) wp_unslash($_POST['sort'])) : 'bought_ts';
        if (!isset($valid_sort[$sort_key])) { $sort_key = 'bought_ts'; }
        $sort_col = $valid_sort[$sort_key];
        $dir      = (isset($_POST['dir']) && strtolower(sanitize_text_field(wp_unslash($_POST['dir']))) === 'asc') ? 'ASC' : 'DESC';

        
        
        $search_join  = '';
        $search_where = '';
        $search_args  = array();
        if ($search !== '') {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $search_join = "
                LEFT JOIN {$wpdb->posts} p_bought ON p_bought.ID = e.product_id
                LEFT JOIN {$wpdb->posts} p_anchor ON p_anchor.ID = r.anchor_pid";
            $search_where = "AND (
                p_bought.post_title LIKE %s
                OR p_anchor.post_title LIKE %s
                OR e.product_id = %d
                OR r.anchor_pid = %d
                OR e.order_id   = %d
            )";
            $pid_guess = (int) preg_replace('/\D/', '', $search);
            $search_args = array($like, $like, $pid_guess, $pid_guess, $pid_guess);
        }

        
        
        
        
        
        
        
        $base_select = "SELECT
                e.id                           AS addcart_id,
                e.order_id,
                e.product_id                   AS bought_pid,
                e.source                       AS closing_source,
                e.ts_added                     AS bought_ts,
                e.bcs_was_on_sale              AS bcs_sale,
                e.bcs_is_new                   AS bcs_new,
                e.bcs_gift_offer_id            AS bcs_gift,
                e.bcs_monthly_offer_id         AS bcs_monthly,
                r.event_type                   AS assist_type,
                r.surface                      AS assist_surface,
                r.position                     AS assist_position,
                r.anchor_pid                   AS anchor_pid,
                r.anchor_term_id               AS anchor_term_id,
                r.anchor_term_tax              AS anchor_term_tax,
                r.ts                           AS assist_ts,
                TIMESTAMPDIFF(MINUTE, r.ts, e.ts_added) AS gap_minutes,
                ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY r.ts DESC, r.id DESC) AS rn
            FROM {$addcart} e
            INNER JOIN {$reco} r
              ON r.session_id = e.session_id
             AND r.target_pid = e.product_id
             AND r.ts         < e.ts_added
             AND e.ts_added   < DATE_ADD(r.ts, INTERVAL 7 DAY)";

        
        $count_sql = "SELECT COUNT(*) FROM ({$base_select}
            {$search_join}
            WHERE e.ts_added >= %s {$search_where} {$badge_where}
        ) sub WHERE sub.rn = 1";
        $count_args = array_merge(array($since), $search_args);
        $filtered = (int) $wpdb->get_var($wpdb->prepare($count_sql, $count_args));

        
        $total = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM ({$base_select}
                WHERE e.ts_added >= %s
             ) sub WHERE sub.rn = 1", $since
        ));

        
        $offset = ($page - 1) * $per_page;
        $rows_sql = "SELECT * FROM ({$base_select}
            {$search_join}
            WHERE e.ts_added >= %s {$search_where} {$badge_where}
            ) sub
            WHERE sub.rn = 1
            ORDER BY {$sort_col} {$dir}, addcart_id DESC
            LIMIT %d OFFSET %d";
        $rows_args = array_merge(array($since), $search_args, array($per_page, $offset));
        $rows = $wpdb->get_results($wpdb->prepare($rows_sql, $rows_args));

        
        $out = array();
        foreach ($rows as $r) {
            $term_info = $this->resolve_term_info((int) $r->anchor_term_id, (string) $r->anchor_term_tax);
            $out[] = array(
                'status'          => (int) $r->order_id > 0 ? 'purchased' : 'in_cart',
                'order_id'        => (int) $r->order_id,
                'order_url'       => (int) $r->order_id > 0 ? admin_url('admin.php?page=wc-orders&action=edit&id=' . (int) $r->order_id) : '',
                'bought_pid'      => (int) $r->bought_pid,
                'bought_name'     => (string) get_the_title((int) $r->bought_pid),
                'bought_url'      => admin_url('post.php?post=' . (int) $r->bought_pid . '&action=edit'),
                'closing_source'  => (string) $r->closing_source,
                'assist_type'     => (string) $r->assist_type,
                'assist_surface'  => (string) $r->assist_surface,
                'assist_surface_label' => $this->surface_label((string) $r->assist_surface),
                'assist_position' => (int) $r->assist_position,
                'anchor_pid'      => (int) $r->anchor_pid,
                'anchor_name'     => (int) $r->anchor_pid > 0 ? (string) get_the_title((int) $r->anchor_pid) : '',
                'anchor_url'      => (int) $r->anchor_pid > 0 ? admin_url('post.php?post=' . (int) $r->anchor_pid . '&action=edit') : '',
                'anchor_term_id'  => (int) $r->anchor_term_id,
                'anchor_term_tax' => (string) $r->anchor_term_tax,
                'anchor_term_name' => $term_info['name'],
                'anchor_term_url'  => $term_info['url'],
                'anchor_fallback' => $this->anchor_fallback_label((string) $r->assist_surface),
                'bought_ts'       => (string) $r->bought_ts,
                'gap_minutes'     => (int) $r->gap_minutes,
            );
        }

        wp_send_json_success(array(
            'rows'     => $out,
            'total'    => $total,
            'filtered' => $filtered,
            'page'     => $page,
            'per_page' => $per_page,
        ));
    }

    
    
    

    public function handle_clear() {
        if (!current_user_can(AIEO_CAPABILITY)) {
            wp_die(esc_html__('Permission denied.', 'ai-eshop-optimizer'), 403);
        }
        check_admin_referer('aieo_attribution_clear');
        global $wpdb;
        $wpdb->query('TRUNCATE TABLE ' . AIEO_Attribution::get_table_name());
        $wpdb->query('TRUNCATE TABLE ' . AIEO_Attribution::get_reco_table_name());
        wp_safe_redirect(add_query_arg(
            array('page' => 'ai-eshop-dmm', 'dmm_tab' => $this->get_id(), 'cleared' => 1),
            admin_url('admin.php')
        ));
        exit;
    }

    public function handle_export() {
        if (!current_user_can(AIEO_CAPABILITY)) {
            wp_die(esc_html__('Permission denied.', 'ai-eshop-optimizer'), 403);
        }
        check_admin_referer('aieo_attribution_export');
        global $wpdb;
        $addcart = AIEO_Attribution::get_table_name();
        $reco    = AIEO_Attribution::get_reco_table_name();
        $days    = isset($_POST['attr_days']) ? max(1, (int) $_POST['attr_days']) : 30;
        $since   = gmdate('Y-m-d H:i:s', time() - ($days * DAY_IN_SECONDS));

        
        
        $assist_rows = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM (
                SELECT
                    e.order_id,
                    e.product_id                AS bought_pid,
                    e.source                    AS closing_source,
                    e.ts_added                  AS bought_ts,
                    r.event_type                AS assist_type,
                    r.surface                   AS assist_surface,
                    r.position                  AS assist_position,
                    r.anchor_pid                AS anchor_pid,
                    r.ts                        AS assist_ts,
                    TIMESTAMPDIFF(MINUTE, r.ts, e.ts_added) AS gap_minutes,
                    ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY r.ts DESC, r.id DESC) AS rn
                FROM {$addcart} e
                INNER JOIN {$reco} r
                  ON r.session_id = e.session_id
                 AND r.target_pid = e.product_id
                 AND r.ts         < e.ts_added
                 AND e.ts_added   < DATE_ADD(r.ts, INTERVAL 7 DAY)
                WHERE e.ts_added >= %s
            ) sub
            WHERE sub.rn = 1
            ORDER BY bought_ts DESC, assist_ts DESC",
            $since
        ), ARRAY_A);

        $sheet = array();
        foreach ($assist_rows as $r) {
            $sheet[] = array(
                'Status'         => (int) $r['order_id'] > 0 ? 'Purchased' : 'In cart',
                'Order ID'       => (int) $r['order_id'] > 0 ? (int) $r['order_id'] : '',
                'Bought PID'     => (int) $r['bought_pid'],
                'Bought name'    => (string) get_the_title((int) $r['bought_pid']),
                'Closing source' => (string) $r['closing_source'],
                'Reco type'      => (string) $r['assist_type'],
                'Reco surface'   => (string) $r['assist_surface'],
                'Slot #'         => (int) $r['assist_position'],
                'Anchor PID'     => (int) $r['anchor_pid'],
                'Anchor name'    => (int) $r['anchor_pid'] > 0 ? (string) get_the_title((int) $r['anchor_pid']) : '',
                'Reco shown at'  => (string) $r['assist_ts'],
                'Added at'       => (string) $r['bought_ts'],
                'Gap (min)'      => (int) $r['gap_minutes'],
            );
        }

        $filename = 'aieo-attribution-' . gmdate('Y-m-d-His') . '.xlsx';
        $this->stream_xlsx($filename, array(
            array(
                'name'    => 'Reco-assisted adds',
                'headers' => array(
                    'Status','Order ID','Bought PID','Bought name','Closing source',
                    'Reco type','Reco surface','Slot #','Anchor PID','Anchor name',
                    'Reco shown at','Added at','Gap (min)'
                ),
                'rows'    => $sheet,
            ),
        ));
        exit;
    }

    
    
    

    private function product_link($pid) {
        if ($pid <= 0) { return '—'; }
        $title = get_the_title($pid);
        if ($title === '' || $title === null) {
            return '#' . $pid . ' <span style="color:#646970;">(' . esc_html__('deleted', 'ai-eshop-optimizer') . ')</span>';
        }
        $url = admin_url('post.php?post=' . $pid . '&action=edit');
        return '<a href="' . esc_url($url) . '">' . esc_html($title) . '</a> <span style="color:#646970;">#' . $pid . '</span>';
    }

    private function surface_label($source) {
        $map = array(
            'archive'                  => __('Shop / category listing', 'ai-eshop-optimizer'),
            'home'                     => __('Home page', 'ai-eshop-optimizer'),
            'single_product'           => __('Product page — main', 'ai-eshop-optimizer'),
            'cart'                     => __('Cart page', 'ai-eshop-optimizer'),
            'checkout'                 => __('Checkout page', 'ai-eshop-optimizer'),
            'product_upsells'          => __('Product page — upsells', 'ai-eshop-optimizer'),
            'product_crosssells'       => __('Product page — cross-sells', 'ai-eshop-optimizer'),
            'product_needs'            => __('Product page — product needs', 'ai-eshop-optimizer'),
            'product_related'          => __('Product page — related', 'ai-eshop-optimizer'),
            'product_recently_viewed'  => __('Product page — recently viewed', 'ai-eshop-optimizer'),
            'sidecart_recently_viewed' => __('Side cart — recently viewed', 'ai-eshop-optimizer'),
            'sidecart_favorites'       => __('Side cart — favorites', 'ai-eshop-optimizer'),
            'sidecart_may_also_like'   => __('Side cart — you may also like', 'ai-eshop-optimizer'),
            'sidecart_product_needs'   => __('Side cart — product needs', 'ai-eshop-optimizer'),
            'sidecart_might_like'      => __('Side cart — you might like', 'ai-eshop-optimizer'),
            'sidecart_quickview'       => __('Side cart — quick view', 'ai-eshop-optimizer'),
            'unknown'                  => __('Unknown', 'ai-eshop-optimizer'),
        );
        return isset($map[$source]) ? $map[$source] : $source;
    }

    private function anchor_fallback_label($surface) {
        if (strpos($surface, 'sidecart_') === 0) { return __('side cart', 'ai-eshop-optimizer'); }
        if ($surface === 'archive')              { return __('archive listing', 'ai-eshop-optimizer'); }
        if ($surface === 'home')                 { return __('home page', 'ai-eshop-optimizer'); }
        return __('no anchor', 'ai-eshop-optimizer');
    }

    
    private function resolve_term_info($term_id, $taxonomy) {
        if ($term_id <= 0 || $taxonomy === '') {
            return array('name' => '', 'url' => '');
        }
        $term = get_term($term_id, $taxonomy);
        if (!$term || is_wp_error($term)) {
            return array('name' => '#' . $term_id, 'url' => '');
        }
        $url = get_edit_term_link($term_id, $taxonomy);
        return array(
            'name' => (string) $term->name,
            'url'  => $url ? (string) $url : '',
        );
    }

    private function taxonomy_label($taxonomy) {
        $map = array(
            'product_cat'   => __('Category', 'ai-eshop-optimizer'),
            'product_tag'   => __('Tag', 'ai-eshop-optimizer'),
            'product_brand' => __('Brand', 'ai-eshop-optimizer'),
        );
        return isset($map[$taxonomy]) ? $map[$taxonomy] : $taxonomy;
    }

    
    
    

    private function stream_xlsx($filename, array $sheets) {
        if (!class_exists('ZipArchive')) {
            wp_die(esc_html__('XLSX export requires the ZipArchive PHP extension.', 'ai-eshop-optimizer'));
        }
        $tmp = wp_tempnam($filename);
        $zip = new ZipArchive();
        if (@$zip->open($tmp, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== true) {
            wp_die(esc_html__('Could not create XLSX workbook.', 'ai-eshop-optimizer'));
        }

        $types = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
               . '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
               . '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
               . '<Default Extension="xml" ContentType="application/xml"/>'
               . '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>';
        foreach ($sheets as $i => $_) {
            $n = $i + 1;
            $types .= '<Override PartName="/xl/worksheets/sheet' . $n . '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
        }
        $types .= '</Types>';
        $zip->addFromString('[Content_Types].xml', $types);

        $zip->addFromString('_rels/.rels',
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
          . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
          . '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
          . '</Relationships>'
        );

        $wb_sheets = ''; $wb_rels = '';
        foreach ($sheets as $i => $s) {
            $n = $i + 1;
            $wb_sheets .= '<sheet name="' . self::xml_esc(mb_substr($s['name'], 0, 31)) . '" sheetId="' . $n . '" r:id="rId' . $n . '"/>';
            $wb_rels   .= '<Relationship Id="rId' . $n . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' . $n . '.xml"/>';
        }
        $zip->addFromString('xl/workbook.xml',
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
          . '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
          . '<sheets>' . $wb_sheets . '</sheets></workbook>'
        );
        $zip->addFromString('xl/_rels/workbook.xml.rels',
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
          . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
          . $wb_rels . '</Relationships>'
        );

        foreach ($sheets as $i => $s) {
            $n = $i + 1;
            $all = array_merge(array($s['headers']), array_map('array_values', $s['rows']));
            $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
                 . '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>';
            foreach ($all as $ri => $row) {
                $r_num = $ri + 1;
                $xml  .= '<row r="' . $r_num . '">';
                $ci = 0;
                foreach ($row as $cell) {
                    $ref = self::col_ref($ci++) . $r_num;
                    if (is_numeric($cell) && $cell !== '') {
                        $xml .= '<c r="' . $ref . '"><v>' . self::xml_esc((string) $cell) . '</v></c>';
                    } else {
                        $xml .= '<c r="' . $ref . '" t="inlineStr"><is><t>' . self::xml_esc((string) $cell) . '</t></is></c>';
                    }
                }
                $xml .= '</row>';
            }
            $xml .= '</sheetData></worksheet>';
            $zip->addFromString('xl/worksheets/sheet' . $n . '.xml', $xml);
        }

        $zip->close();

        while (ob_get_level() > 0) { ob_end_clean(); }
        nocache_headers();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Content-Length: ' . filesize($tmp));
        readfile($tmp);
        @unlink($tmp);
    }

    private static function col_ref($i) {
        $i = (int) $i;
        if ($i < 26) { return chr(65 + $i); }
        return chr(65 + intdiv($i, 26) - 1) . chr(65 + ($i % 26));
    }
    private static function xml_esc($s) { return htmlspecialchars($s, ENT_QUOTES | ENT_XML1, 'UTF-8'); }
}
