<?php

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

class AIEO_DMM_Supplier_Orders extends AIEO_DMM_Module_Base {

    public function get_id() { return 'supplier_orders'; }
    public function get_icon()     { return 'clipboard-list'; }

    public function get_label() { return __('Supplier Orders', 'ai-eshop-optimizer'); }

    
    public function get_priority() { return 75; }

    public function get_group() { return 'catalog_sync'; }

    
    const MAX_ORDERS = 500;

    public function register_hooks() {
        add_action('wp_ajax_aieo_dmm_supplier_order_items', array($this, 'ajax_items'));
        add_action('wp_ajax_aieo_dmm_supplier_order_suppliers', array($this, 'ajax_suppliers'));
        add_action('wp_ajax_aieo_dmm_supplier_order_picker', array($this, 'ajax_picker_search'));
        add_action('wp_ajax_aieo_dmm_supplier_email_items', array($this, 'ajax_email'));
        add_action('admin_post_aieo_supplier_items_export', array($this, 'handle_export'));

        
        
        
        add_filter('aieo_report_bulk', array($this, 'report_bulk'), 10, 3);
        add_action('admin_post_aieo_preorder_batch_export', array($this, 'handle_batch_export'));
        add_action('wp_ajax_aieo_dmm_preorder_batch_email', array($this, 'ajax_batch_email'));
    }

    
    
    

    
    public static function statuses_for($mode) {
        return ('period' === $mode)
            ? array('pending', 'processing', 'on-hold', 'completed')
            : array('pending', 'processing', 'on-hold');
    }

    
    
    private static $supplier_memo = array();

    
    public static function warm_supplier_memo(array $product_ids) {
        global $wpdb;
        $ids = array_values(array_unique(array_filter(array_map('intval', $product_ids))));
        $ids = array_values(array_diff($ids, array_keys(self::$supplier_memo)));
        if (empty($ids)) { return; }

        
        $in      = implode(',', $ids);
        $parents = array();
        foreach ((array) $wpdb->get_results("SELECT ID, post_parent FROM {$wpdb->posts} WHERE ID IN ({$in})") as $row) {
            $parents[(int) $row->ID] = ((int) $row->post_parent > 0) ? (int) $row->post_parent : (int) $row->ID;
        }
        foreach ($ids as $id) { if (!isset($parents[$id])) { $parents[$id] = $id; } }

        $lookup_ids = array_values(array_unique(array_merge($ids, array_values($parents))));
        
        
        $pmap = $parents;
        foreach ($lookup_ids as $lid) { if (!isset($pmap[$lid])) { $pmap[$lid] = $lid; } }
        $found = array(); 
        
        
        
        
        $pending = function () use (&$found, $lookup_ids, $pmap) {
            $rest = array();
            foreach ($lookup_ids as $lid) {
                if (!isset($found[$lid]) && !isset($found[$pmap[$lid]])) { $rest[] = $lid; }
            }
            return $rest;
        };

        
        
        
        $rin = implode(',', $lookup_ids);
        foreach ((array) $wpdb->get_results(
            "SELECT tr.object_id, tt.term_id
               FROM {$wpdb->term_relationships} tr
               JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
              WHERE tt.taxonomy = 'supplier' AND tr.object_id IN ({$rin})"
        ) as $row) {
            $oid = (int) $row->object_id;
            if (!isset($found[$oid])) { $found[$oid] = (int) $row->term_id; }
        }

        
        
        $rest   = $pending();
        $vitals = $wpdb->prefix . 'aieo_core_product_vitals';
        if ($rest && function_exists('aieo_dmm_table_exists') && aieo_dmm_table_exists($vitals)) {
            $vin = implode(',', $rest);
            foreach ((array) $wpdb->get_results("SELECT product_id, supplier_id FROM {$vitals} WHERE product_id IN ({$vin}) AND supplier_id > 0") as $row) {
                $found[(int) $row->product_id] = (int) $row->supplier_id;
            }
        }

        
        $rest = $pending();
        $map  = $wpdb->prefix . 'aieo_dmm_supplier_sku_map';
        $pml  = $wpdb->prefix . 'wc_product_meta_lookup';
        if ($rest && function_exists('aieo_dmm_table_exists') && aieo_dmm_table_exists($map) && aieo_dmm_table_exists($pml)) {
            $rin = implode(',', $rest);
            foreach ((array) $wpdb->get_results(
                "SELECT pml.product_id, m.supplier_term_id
                   FROM {$pml} pml
                   JOIN {$map} m ON m.sku = pml.sku
                  WHERE pml.product_id IN ({$rin}) AND m.supplier_term_id IS NOT NULL"
            ) as $row) {
                $pid = (int) $row->product_id;
                if (!isset($found[$pid])) { $found[$pid] = (int) $row->supplier_term_id; }
            }
        }

        
        $rest = $pending();
        if ($rest && class_exists('AIEO_DMM_Brand_Extras_Data') && taxonomy_exists('product_brand')) {
            $rin   = implode(',', $rest);
            $brand = array();
            foreach ((array) $wpdb->get_results(
                "SELECT tr.object_id, tt.term_id
                   FROM {$wpdb->term_relationships} tr
                   JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
                  WHERE tt.taxonomy = 'product_brand' AND tr.object_id IN ({$rin})"
            ) as $row) {
                $brand[(int) $row->object_id][] = (int) $row->term_id;
            }
            $brand_supplier = array();
            foreach ($brand as $oid => $bids) {
                foreach ($bids as $bid) {
                    if (!array_key_exists($bid, $brand_supplier)) {
                        $extras = AIEO_DMM_Brand_Extras_Data::get('product_brand', $bid);
                        $brand_supplier[$bid] = (int) ($extras['supplier_term_id'] ?? 0);
                    }
                    if ($brand_supplier[$bid] > 0 && !isset($found[$oid])) {
                        $found[$oid] = $brand_supplier[$bid];
                        break;
                    }
                }
            }
        }

        
        
        foreach ($ids as $id) {
            self::$supplier_memo[$id] = (int) ($found[$id] ?? $found[$parents[$id]] ?? 0);
        }
    }

    public static function resolve_supplier($product_id) {
        $memo = &self::$supplier_memo;
        $product_id = (int) $product_id;
        if (isset($memo[$product_id])) { return $memo[$product_id]; }

        $product = function_exists('wc_get_product') ? wc_get_product($product_id) : null;
        $parent  = $product ? ((int) $product->get_parent_id() ?: $product_id) : $product_id;

        
        
        
        
        $terms = wp_get_object_terms($parent, 'supplier', array('fields' => 'ids'));
        if (is_array($terms) && $terms) {
            return $memo[$product_id] = (int) $terms[0];
        }

        
        
        
        global $wpdb;
        $vitals = $wpdb->prefix . 'aieo_core_product_vitals';
        if (function_exists('aieo_dmm_table_exists') && aieo_dmm_table_exists($vitals)) {
            $vit = $wpdb->get_var($wpdb->prepare(
                "SELECT supplier_id FROM {$vitals} WHERE product_id = %d LIMIT 1",
                $product_id
            ));
            if (null !== $vit && (int) $vit > 0) {
                return $memo[$product_id] = (int) $vit;
            }
        }

        
        $sku = $product ? (string) $product->get_sku() : '';
        if ($sku !== '') {
            global $wpdb;
            $map = $wpdb->prefix . 'aieo_dmm_supplier_sku_map';
            if (function_exists('aieo_dmm_table_exists') && aieo_dmm_table_exists($map)) {
                $sid = (int) $wpdb->get_var($wpdb->prepare(
                    "SELECT supplier_term_id FROM {$map} WHERE sku = %s AND supplier_term_id IS NOT NULL LIMIT 1",
                    $sku
                ));
                if ($sid > 0) { return $memo[$product_id] = $sid; }
            }
        }

        
        if (class_exists('AIEO_DMM_Brand_Extras_Data') && taxonomy_exists('product_brand')) {
            $brands = wp_get_object_terms($parent, 'product_brand', array('fields' => 'ids'));
            foreach ((is_array($brands) ? $brands : array()) as $bid) {
                $extras = AIEO_DMM_Brand_Extras_Data::get('product_brand', (int) $bid);
                if (!empty($extras['supplier_term_id'])) {
                    return $memo[$product_id] = (int) $extras['supplier_term_id'];
                }
            }
        }

        return $memo[$product_id] = 0;
    }

    
    private static function supplier_product_ids($supplier_id) {
        global $wpdb;
        $supplier_id = (int) $supplier_id;
        $ids = array();

        
        $vitals = $wpdb->prefix . 'aieo_core_product_vitals';
        if (function_exists('aieo_dmm_table_exists') && aieo_dmm_table_exists($vitals)) {
            $ids = array_merge($ids, (array) $wpdb->get_col($wpdb->prepare(
                "SELECT product_id FROM {$vitals} WHERE supplier_id = %d",
                $supplier_id
            )));
        }

        
        $ids = array_merge($ids, (array) $wpdb->get_col($wpdb->prepare(
            "SELECT tr.object_id
               FROM {$wpdb->term_relationships} tr
               JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
              WHERE tt.taxonomy = 'supplier' AND tt.term_id = %d",
            $supplier_id
        )));

        
        $map = $wpdb->prefix . 'aieo_dmm_supplier_sku_map';
        $pml = $wpdb->prefix . 'wc_product_meta_lookup';
        if (function_exists('aieo_dmm_table_exists') && aieo_dmm_table_exists($map) && aieo_dmm_table_exists($pml)) {
            $ids = array_merge($ids, (array) $wpdb->get_col($wpdb->prepare(
                "SELECT pml.product_id
                   FROM {$map} m
                   JOIN {$pml} pml ON pml.sku = m.sku
                  WHERE m.supplier_term_id = %d",
                $supplier_id
            )));
        }

        
        
        
        
        if (taxonomy_exists('product_brand')) {
            $brand_ids = array();
            $bx = $wpdb->prefix . 'aieo_dmm_brand_extras';
            if (function_exists('aieo_dmm_table_exists') && aieo_dmm_table_exists($bx)) {
                $brand_ids = array_map('intval', (array) $wpdb->get_col($wpdb->prepare(
                    "SELECT term_id FROM {$bx} WHERE taxonomy = 'product_brand' AND supplier_term_id = %d",
                    $supplier_id
                )));
            } elseif (class_exists('AIEO_DMM_Brand_Extras_Data')) {
                $brands = get_terms(array('taxonomy' => 'product_brand', 'hide_empty' => false, 'fields' => 'ids'));
                foreach ((is_wp_error($brands) ? array() : (array) $brands) as $bid) {
                    $extras = AIEO_DMM_Brand_Extras_Data::get('product_brand', (int) $bid);
                    if ((int) ($extras['supplier_term_id'] ?? 0) === $supplier_id) {
                        $brand_ids[] = (int) $bid;
                    }
                }
            }
            if ($brand_ids) {
                $in = implode(',', array_map('intval', $brand_ids));
                $ids = array_merge($ids, (array) $wpdb->get_col(
                    "SELECT tr.object_id
                       FROM {$wpdb->term_relationships} tr
                       JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
                      WHERE tt.taxonomy = 'product_brand' AND tt.term_id IN ({$in})"
                ));
            }
        }

        $ids = array_values(array_unique(array_filter(array_map('intval', $ids))));
        if (empty($ids)) { return array(); }

        
        
        
        $in     = implode(',', $ids);
        $parent = array();
        foreach ((array) $wpdb->get_results("SELECT ID, post_parent FROM {$wpdb->posts} WHERE ID IN ({$in})") as $row) {
            $parent[(int) $row->ID] = (int) $row->post_parent;
        }
        $out = array();
        foreach ($ids as $id) {
            
            
            
            $p = (int) ($parent[$id] ?? 0);
            $out[$p > 0 ? $p : $id] = true;
        }
        return array_keys($out);
    }

    
    private static function items_via_lookup($supplier_id, $mode, $from, $to) {
        global $wpdb;
        $lookup = $wpdb->prefix . 'wc_order_product_lookup';
        if (!function_exists('aieo_dmm_table_exists') || !aieo_dmm_table_exists($lookup)) {
            return null;
        }

        
        $hpos = class_exists('\Automattic\WooCommerce\Utilities\OrderUtil')
            && \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
        if ($hpos) {
            $orders_table = $wpdb->prefix . 'wc_orders';
            if (!aieo_dmm_table_exists($orders_table)) { return null; }
            $join   = "JOIN {$orders_table} o ON o.id = lu.order_id";
            $status = 'o.status';
        } else {
            $join   = "JOIN {$wpdb->posts} o ON o.ID = lu.order_id";
            $status = 'o.post_status';
        }

        $statuses = array_map(function ($s) { return 'wc-' . $s; }, self::statuses_for($mode));
        $status_in = "'" . implode("','", array_map('esc_sql', $statuses)) . "'";

        
        $where_date = '';
        if ('period' === $mode) {
            $from = preg_match('/^\d{4}-\d{2}-\d{2}$/', (string) $from) ? $from : wp_date('Y-m-d', time() - 30 * DAY_IN_SECONDS);
            $to   = preg_match('/^\d{4}-\d{2}-\d{2}$/', (string) $to) ? $to : wp_date('Y-m-d');
            $where_date = $wpdb->prepare(
                ' AND lu.date_created BETWEEN %s AND %s ',
                $from . ' 00:00:00',
                $to . ' 23:59:59'
            );
        }

        
        $orders_scanned = (int) $wpdb->get_var(
            "SELECT COUNT(DISTINCT lu.order_id) FROM {$lookup} lu {$join}
              WHERE {$status} IN ({$status_in}) {$where_date}"
        );

        $parents = self::supplier_product_ids($supplier_id);
        
        
        self::warm_supplier_memo($parents);
        $parents = array_values(array_filter($parents, function ($pid) use ($supplier_id) {
            return self::resolve_supplier($pid) === (int) $supplier_id;
        }));
        if (empty($parents)) {
            return array('rows' => array(), 'orders_scanned' => $orders_scanned, 'capped' => false);
        }
        $parents_in = implode(',', array_map('intval', $parents));

        
        
        $rows = $wpdb->get_results(
            "SELECT COALESCE(NULLIF(lu.variation_id, 0), lu.product_id) AS pid,
                    SUM(lu.product_qty)              AS qty,
                    COUNT(DISTINCT lu.order_id)      AS order_count,
                    MAX(lu.date_created)             AS last_order,
                    GROUP_CONCAT(DISTINCT lu.order_id ORDER BY lu.order_id DESC SEPARATOR ',') AS order_ids
               FROM {$lookup} lu
               {$join}
              WHERE {$status} IN ({$status_in})
                AND lu.product_id IN ({$parents_in})
                {$where_date}
           GROUP BY pid
           ORDER BY qty DESC",
            ARRAY_A
        );

        
        
        
        $labels = self::product_labels(wp_list_pluck((array) $rows, 'pid'));

        $out = array();
        foreach ((array) $rows as $r) {
            $pid = (int) $r['pid'];
            $ids = array_slice(array_filter(explode(',', (string) $r['order_ids'])), 0, 12);
            $out[] = array(
                'product_id'  => $pid,
                'sku'         => (string) ($labels[$pid]['sku'] ?? ''),
                'name'        => (string) ($labels[$pid]['name'] ?? ''),
                'qty'         => (int) $r['qty'],
                'order_count' => (int) $r['order_count'],
                'orders'      => implode(', ', array_map(function ($id) { return '#' . (int) $id; }, $ids)),
                'last_order'  => substr((string) $r['last_order'], 0, 10),
            );
        }

        return array('rows' => $out, 'orders_scanned' => $orders_scanned, 'capped' => false);
    }

    
    private static function product_labels(array $ids) {
        global $wpdb;
        $ids = array_values(array_unique(array_filter(array_map('intval', $ids))));
        if (empty($ids)) { return array(); }

        $in  = implode(',', $ids);
        $pml = $wpdb->prefix . 'wc_product_meta_lookup';
        $has_pml = function_exists('aieo_dmm_table_exists') && aieo_dmm_table_exists($pml);
        $sku_select = $has_pml ? 'pml.sku' : "''";
        $sku_join   = $has_pml ? "LEFT JOIN {$pml} pml ON pml.product_id = p.ID" : '';

        $rows = $wpdb->get_results(
            "SELECT p.ID, p.post_title, p.post_parent, pp.post_title AS parent_title, {$sku_select} AS sku
               FROM {$wpdb->posts} p
          LEFT JOIN {$wpdb->posts} pp ON pp.ID = p.post_parent
               {$sku_join}
              WHERE p.ID IN ({$in})",
            ARRAY_A
        );

        $out = array();
        foreach ((array) $rows as $r) {
            $name = ((int) $r['post_parent'] > 0 && '' !== (string) $r['parent_title'])
                ? (string) $r['parent_title']
                : (string) $r['post_title'];
            $out[(int) $r['ID']] = array('sku' => (string) $r['sku'], 'name' => $name);
        }
        return $out;
    }

    
    private static function window_sql($mode, $from, $to) {
        global $wpdb;
        $lookup = $wpdb->prefix . 'wc_order_product_lookup';
        if (!function_exists('aieo_dmm_table_exists') || !aieo_dmm_table_exists($lookup)) {
            return null;
        }
        $hpos = class_exists('\Automattic\WooCommerce\Utilities\OrderUtil')
            && \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
        if ($hpos) {
            $orders_table = $wpdb->prefix . 'wc_orders';
            if (!aieo_dmm_table_exists($orders_table)) { return null; }
            $join   = "JOIN {$orders_table} o ON o.id = lu.order_id";
            $status = 'o.status';
        } else {
            $join   = "JOIN {$wpdb->posts} o ON o.ID = lu.order_id";
            $status = 'o.post_status';
        }
        $statuses  = array_map(function ($s) { return 'wc-' . $s; }, self::statuses_for($mode));
        $status_in = "'" . implode("','", array_map('esc_sql', $statuses)) . "'";

        $where_date = '';
        if ('period' === $mode) {
            $from = preg_match('/^\d{4}-\d{2}-\d{2}$/', (string) $from) ? $from : wp_date('Y-m-d', time() - 30 * DAY_IN_SECONDS);
            $to   = preg_match('/^\d{4}-\d{2}-\d{2}$/', (string) $to) ? $to : wp_date('Y-m-d');
            $where_date = $wpdb->prepare(
                ' AND lu.date_created BETWEEN %s AND %s ',
                $from . ' 00:00:00',
                $to . ' 23:59:59'
            );
        }
        return array(
            'lookup'     => $lookup,
            'join'       => $join,
            'where'      => "{$status} IN ({$status_in})",
            'where_date' => $where_date,
        );
    }

    
    public static function suppliers_in_window($mode = 'pending', $from = '', $to = '') {
        global $wpdb;
        $w = self::window_sql($mode, $from, $to);
        if (null === $w) {
            
            $terms = taxonomy_exists('supplier')
                ? get_terms(array('taxonomy' => 'supplier', 'hide_empty' => false, 'number' => 500, 'orderby' => 'name'))
                : array();
            $out = array();
            foreach ((is_wp_error($terms) ? array() : (array) $terms) as $t) {
                $out[] = array('id' => (int) $t->term_id, 'name' => $t->name, 'lines' => null, 'qty' => null);
            }
            return array('suppliers' => $out, 'orders_scanned' => 0, 'exact' => false);
        }

        $orders_scanned = (int) $wpdb->get_var(
            "SELECT COUNT(DISTINCT lu.order_id) FROM {$w['lookup']} lu {$w['join']}
              WHERE {$w['where']} {$w['where_date']}"
        );

        
        $rows = $wpdb->get_results(
            "SELECT COALESCE(NULLIF(lu.variation_id, 0), lu.product_id) AS pid,
                    lu.product_id                AS parent_id,
                    SUM(lu.product_qty)          AS qty,
                    COUNT(DISTINCT lu.order_id)  AS order_count
               FROM {$w['lookup']} lu
               {$w['join']}
              WHERE {$w['where']} {$w['where_date']}
           GROUP BY pid, parent_id",
            ARRAY_A
        );
        if (empty($rows)) {
            return array('suppliers' => array(), 'orders_scanned' => $orders_scanned, 'exact' => true);
        }

        
        $ids = array();
        foreach ($rows as $r) { $ids[] = (int) $r['pid']; $ids[] = (int) $r['parent_id']; }
        self::warm_supplier_memo($ids);

        $agg = array();
        foreach ($rows as $r) {
            
            
            
            
            $sid = self::resolve_supplier((int) $r['parent_id']);
            if ($sid <= 0) { continue; }
            if (!isset($agg[$sid])) { $agg[$sid] = array('lines' => 0, 'qty' => 0); }
            $agg[$sid]['lines']++;
            $agg[$sid]['qty'] += (int) $r['qty'];
        }

        $out = array();
        foreach ($agg as $sid => $a) {
            $term = get_term((int) $sid, 'supplier');
            if (!$term || is_wp_error($term)) { continue; }
            $out[] = array(
                'id'    => (int) $sid,
                'name'  => $term->name,
                'lines' => (int) $a['lines'],
                'qty'   => (int) $a['qty'],
            );
        }
        usort($out, function ($a, $b) {
            return ($b['qty'] === $a['qty']) ? strcoll($a['name'], $b['name']) : ($b['qty'] - $a['qty']);
        });

        return array('suppliers' => $out, 'orders_scanned' => $orders_scanned, 'exact' => true);
    }

    
    public static function items_for_suppliers(array $supplier_ids, $mode = 'pending', $from = '', $to = '') {
        $supplier_ids = array_values(array_unique(array_filter(array_map('intval', $supplier_ids))));
        if (empty($supplier_ids)) {
            return array('rows' => array(), 'orders_scanned' => 0, 'capped' => false);
        }
        if (1 === count($supplier_ids)) {
            return self::items_for_supplier($supplier_ids[0], $mode, $from, $to);
        }

        $merged  = array();
        $scanned = 0;
        $capped  = false;
        foreach ($supplier_ids as $sid) {
            $one = self::items_for_supplier($sid, $mode, $from, $to);
            $scanned = max($scanned, (int) $one['orders_scanned']);
            $capped  = $capped || !empty($one['capped']);
            $term    = get_term($sid, 'supplier');
            $name    = ($term && !is_wp_error($term)) ? $term->name : '';
            foreach ($one['rows'] as $row) {
                
                
                $key = (int) $row['product_id'];
                if (!isset($merged[$key])) {
                    $row['supplier_id']   = $sid;
                    $row['supplier_name'] = $name;
                    $merged[$key] = $row;
                }
            }
        }
        $rows = array_values($merged);
        usort($rows, function ($a, $b) { return $b['qty'] - $a['qty']; });

        return array('rows' => $rows, 'orders_scanned' => $scanned, 'capped' => $capped);
    }

    
    public static function items_for_supplier($supplier_id, $mode = 'pending', $from = '', $to = '') {
        $fast = self::items_via_lookup((int) $supplier_id, $mode, $from, $to);
        if (null !== $fast) {
            return $fast;
        }
        return self::items_by_walking_orders($supplier_id, $mode, $from, $to);
    }

    
    private static function items_by_walking_orders($supplier_id, $mode = 'pending', $from = '', $to = '') {
        $supplier_id = (int) $supplier_id;
        $args = array(
            'status'  => self::statuses_for($mode),
            'limit'   => self::MAX_ORDERS + 1,
            'orderby' => 'date',
            'order'   => 'DESC',
            'type'    => 'shop_order',
        );
        if ('period' === $mode) {
            
            
            
            
            $from = preg_match('/^\d{4}-\d{2}-\d{2}$/', (string) $from) ? $from : wp_date('Y-m-d', time() - 30 * DAY_IN_SECONDS);
            $to   = preg_match('/^\d{4}-\d{2}-\d{2}$/', (string) $to) ? $to : wp_date('Y-m-d');
            
            
            
            
            try {
                $tz      = wp_timezone();
                $from_ts = (new \DateTime($from . ' 00:00:00', $tz))->getTimestamp();
                $to_ts   = (new \DateTime($to . ' 23:59:59', $tz))->getTimestamp();
                $args['date_created'] = $from_ts . '...' . $to_ts;
            } catch (\Throwable $e) {
                $args['date_created'] = $from . '...' . $to;
            }
        }

        $orders = function_exists('wc_get_orders') ? wc_get_orders($args) : array();
        $capped = count($orders) > self::MAX_ORDERS;
        if ($capped) { $orders = array_slice($orders, 0, self::MAX_ORDERS); }

        $rows = array();
        foreach ($orders as $order) {
            foreach ($order->get_items('line_item') as $item) {
                $pid = (int) ($item->get_variation_id() ?: $item->get_product_id());
                if ($pid <= 0) { continue; }
                
                
                
                $owner = (int) $item->get_product_id();
                if (self::resolve_supplier($owner > 0 ? $owner : $pid) !== $supplier_id) { continue; }

                if (!isset($rows[$pid])) {
                    $product    = wc_get_product($pid);
                    $rows[$pid] = array(
                        'product_id' => $pid,
                        'sku'        => $product ? (string) $product->get_sku() : '',
                        'name'       => $product ? (string) $product->get_name() : (string) $item->get_name(),
                        'qty'        => 0,
                        'orders'     => array(),
                        'last_order' => '',
                    );
                }
                $rows[$pid]['qty'] += (int) $item->get_quantity();
                $rows[$pid]['orders'][$order->get_id()] = '#' . $order->get_order_number();
                $created = $order->get_date_created();
                $when    = $created ? $created->date_i18n('Y-m-d') : '';
                if ($when > $rows[$pid]['last_order']) { $rows[$pid]['last_order'] = $when; }
            }
        }

        foreach ($rows as &$row) {
            $row['order_count'] = count($row['orders']);
            $row['orders']      = implode(', ', array_slice(array_values($row['orders']), 0, 12));
        }
        unset($row);
        usort($rows, function ($a, $b) { return $b['qty'] - $a['qty']; });

        return array(
            'rows'           => array_values($rows),
            'orders_scanned' => count($orders),
            'capped'         => $capped,
        );
    }

    
    private static function view_params() {
        $src = array_merge($_GET, $_POST); 

        $ids = array();
        if (isset($src['supplier_ids'])) {
            $raw = is_array($src['supplier_ids'])
                ? $src['supplier_ids']
                : explode(',', (string) $src['supplier_ids']);
            $ids = array_map('intval', (array) $raw);
        }
        if (isset($src['supplier_id'])) {
            $ids[] = (int) $src['supplier_id'];
        }
        $ids = array_values(array_unique(array_filter($ids)));

        return array(
            $ids,
            (isset($src['mode']) && 'period' === $src['mode']) ? 'period' : 'pending',
            sanitize_text_field((string) ($src['from'] ?? '')),
            sanitize_text_field((string) ($src['to'] ?? '')),
        );
    }

    
    private static function valid_supplier_ids(array $ids) {
        $out = array();
        foreach ($ids as $id) {
            if ((int) $id > 0 && term_exists((int) $id, 'supplier')) { $out[] = (int) $id; }
        }
        return $out;
    }

    
    
    

    public function ajax_items() {
        AIEO_DMM::verify_ajax_request();
        list($supplier_ids, $mode, $from, $to) = self::view_params();
        $supplier_ids = self::valid_supplier_ids($supplier_ids);
        if (empty($supplier_ids)) {
            wp_send_json_error(array('message' => __('Pick a supplier first.', 'ai-eshop-optimizer')));
        }
        $data = self::items_for_suppliers($supplier_ids, $mode, $from, $to);
        $data['multi'] = count($supplier_ids) > 1;
        $data['table'] = self::items_table((array) ($data['rows'] ?? array()), $data['multi']);
        wp_send_json_success($data);
    }

    
    private static function items_table(array $items, $multi) {
        if (!class_exists('AIEO_Table')) {
            return array('html' => '', 'icons' => '');
        }
        $columns = array(
            array('key' => 'sku', 'label' => __('SKU', 'ai-eshop-optimizer'), 'type' => 'code', 'sort_type' => 'text'),
            array('key' => 'name', 'label' => __('Product', 'ai-eshop-optimizer'), 'sort_type' => 'text'),
        );
        if ($multi) {
            $columns[] = array('key' => 'supplier', 'label' => __('Supplier', 'ai-eshop-optimizer'), 'sort_type' => 'text');
        }
        $columns[] = array('key' => 'qty', 'label' => __('Quantity', 'ai-eshop-optimizer'), 'type' => 'num', 'sort_type' => 'number');
        $columns[] = array('key' => 'orders', 'label' => __('Orders', 'ai-eshop-optimizer'), 'type' => 'num', 'sort_type' => 'number');
        $columns[] = array('key' => 'last', 'label' => __('Last order', 'ai-eshop-optimizer'), 'sort_type' => 'text');
        $rows = array();
        foreach ($items as $i => $r) {
            $sku    = (string) ($r['sku'] ?? '');
            $name   = (string) ($r['name'] ?? '');
            $rows[] = array(
                '_id'      => (string) ($i + 1),
                'sku'      => array('value' => $sku),
                'name'     => $name,
                'supplier' => (string) ($r['supplier_name'] ?? ''),
                'qty'      => array('html' => '<strong>' . esc_html(number_format_i18n((int) ($r['qty'] ?? 0))) . '</strong>'),
                'orders'   => array('html' => esc_html((string) ($r['order_count'] ?? '')) . ' <span class="rooster-admin-table__sub">' . esc_html((string) ($r['orders'] ?? '')) . '</span>'),
                'last'     => (string) ($r['last_order'] ?? ''),
                '_s'       => array(
                    'sku'      => $sku,
                    'name'     => $name,
                    'supplier' => (string) ($r['supplier_name'] ?? ''),
                    'qty'      => (int) ($r['qty'] ?? 0),
                    'orders'   => (int) ($r['order_count'] ?? 0),
                    'last'     => (string) ($r['last_order'] ?? ''),
                ),
                '_text'    => $sku . ' ' . $name,
            );
        }
        return AIEO_Table::local_answer('aieo-so-table', $columns, $rows, array(
            'prefix'  => 'so_items_',
            'size'    => 25,
            'caption' => __('Items to order, by supplier', 'ai-eshop-optimizer'),
            'empty'   => esc_html__('No items for this supplier in the chosen orders.', 'ai-eshop-optimizer'),
            'search'  => array('label' => __('Search', 'ai-eshop-optimizer')),
        ));
    }

    
    public function ajax_suppliers() {
        AIEO_DMM::verify_ajax_request();
        list(, $mode, $from, $to) = self::view_params();
        wp_send_json_success(self::suppliers_in_window($mode, $from, $to));
    }

    
    public function ajax_picker_search() {
        AIEO_DMM::verify_ajax_request();
        list(, $mode, $from, $to) = self::view_params();
        
        $q = isset($_GET['q']) ? sanitize_text_field(wp_unslash($_GET['q'])) : '';

        $window  = self::suppliers_in_window($mode, $from, $to);
        $results = array();
        foreach ($window['suppliers'] as $s) {
            if ('' !== $q && false === mb_stripos($s['name'], $q)) {
                continue;
            }
            $label = $s['name'];
            if (null !== $s['qty']) {
                $label .= sprintf(
                    
                    ' — ' . __('%s pieces', 'ai-eshop-optimizer'),
                    number_format_i18n((int) $s['qty'])
                );
            }
            $results[] = array(
                'id'           => (int) $s['id'],
                'text'         => $label,
                'name'         => $s['name'],
                'type'         => 'simple',
                'status'       => 'publish',
                'n_variations' => 0,
            );
        }
        wp_send_json(array('results' => $results));
    }

    public function handle_export() {
        if (!current_user_can(AIEO_CAPABILITY)) { wp_die(esc_html__('Insufficient permissions.', 'ai-eshop-optimizer')); }
        check_admin_referer('aieo_supplier_items_export');
        if (!class_exists('\Rooster\Admin\Export') && function_exists('rooster_admin_builders')) {
            rooster_admin_builders();
        }
        if (!class_exists('\Rooster\Admin\Export')) { wp_die(esc_html__('XLSX writer unavailable.', 'ai-eshop-optimizer')); }

        list($supplier_ids, $mode, $from, $to) = self::view_params();
        $supplier_ids = self::valid_supplier_ids($supplier_ids);
        if (empty($supplier_ids)) { wp_die(esc_html__('That supplier no longer exists.', 'ai-eshop-optimizer')); }

        $names = array();
        foreach ($supplier_ids as $sid) {
            $t = get_term($sid, 'supplier');
            if ($t && !is_wp_error($t)) { $names[] = $t->name; }
        }
        $title = implode(' + ', $names);
        $multi = count($supplier_ids) > 1;

        $data = self::items_for_suppliers($supplier_ids, $mode, $from, $to);

        $whole   = array('format' => array('decimals' => 0));
        $columns = array(
            'sku'  => array('label' => __('SKU', 'ai-eshop-optimizer'), 'type' => 'text'),
            'name' => __('Product', 'ai-eshop-optimizer'),
        );
        if ($multi) { $columns['supplier_name'] = __('Supplier', 'ai-eshop-optimizer'); }
        $columns += array(
            'qty'         => array('label' => __('Quantity', 'ai-eshop-optimizer')) + $whole,
            'order_count' => array('label' => __('Orders', 'ai-eshop-optimizer')) + $whole,
            'orders'      => array('label' => __('Order numbers', 'ai-eshop-optimizer'), 'type' => 'text'),
            'last_order'  => __('Last order', 'ai-eshop-optimizer'),
        );
        $slug = $multi ? (sanitize_title($names[0]) . '-plus-' . (count($names) - 1)) : sanitize_title($title);
        \Rooster\Admin\Export::xlsx('supplier-items-' . $slug . '-' . $mode . '.xlsx', array(array('name' => $title, 'columns' => $columns, 'rows' => $data['rows'])));
        exit;
    }

    
    public static function email_supplier_items($supplier_id, $mode = 'pending', $from = '', $to = '') {
        $term = get_term((int) $supplier_id, 'supplier');
        if (!$term || is_wp_error($term)) {
            return new WP_Error('aieo_supplier_missing', __('That supplier no longer exists.', 'ai-eshop-optimizer'));
        }

        $address = '';
        if (class_exists('AIEO_DMM_Supplier_Extras_Data')) {
            $extras  = AIEO_DMM_Supplier_Extras_Data::get('supplier', (int) $supplier_id);
            $address = (string) ($extras['email'] ?? '');
            if ('' === $address) { $address = (string) ($extras['corporate_email'] ?? ''); }
        }
        if ('' === $address || !is_email($address)) {
            return new WP_Error('aieo_supplier_no_email', __('This supplier has no order-taking email on record. Add one on the Suppliers page first.', 'ai-eshop-optimizer'));
        }

        $data = self::items_for_supplier((int) $supplier_id, $mode, $from, $to);
        if (empty($data['rows'])) {
            return new WP_Error('aieo_supplier_no_items', __('Nothing to order: the chosen view holds no items for this supplier.', 'ai-eshop-optimizer'));
        }

        $shop  = wp_specialchars_decode(get_bloginfo('name'), ENT_QUOTES);
        $lines = array();
        
        $lines[] = sprintf(__('Dear %s,', 'ai-eshop-optimizer'), $term->name);
        $lines[] = '';
        $lines[] = __('We would like to order the following items:', 'ai-eshop-optimizer');
        $lines[] = '';
        foreach ($data['rows'] as $r) {
            $lines[] = sprintf(
                '- %1$s × %2$d — %3$s',
                ('' !== $r['sku']) ? $r['sku'] : __('(no SKU)', 'ai-eshop-optimizer'),
                (int) $r['qty'],
                $r['name']
            );
        }
        $lines[] = '';
        $lines[] = __('Please confirm availability and expected delivery.', 'ai-eshop-optimizer');
        $lines[] = '';
        $lines[] = __('Kind regards,', 'ai-eshop-optimizer');
        $lines[] = $shop;

        
        $subject = sprintf(__('Restock order from %s', 'ai-eshop-optimizer'), $shop);
        $sent    = wp_mail($address, $subject, implode("\n", $lines));
        if (!$sent) {
            return new WP_Error('aieo_supplier_mail_failed', __('The email could not be sent — check the site\'s outgoing mail.', 'ai-eshop-optimizer'));
        }
        return array('sent_to' => $address, 'items' => count($data['rows']));
    }

    
    public function ajax_email() {
        AIEO_DMM::verify_ajax_request();
        list($supplier_ids, $mode, $from, $to) = self::view_params();
        $supplier_ids = self::valid_supplier_ids($supplier_ids);
        if (empty($supplier_ids)) {
            wp_send_json_error(array('message' => __('Pick a supplier first.', 'ai-eshop-optimizer')));
        }

        $sent   = array();
        $items  = 0;
        $errors = array();
        foreach ($supplier_ids as $sid) {
            $result = self::email_supplier_items($sid, $mode, $from, $to);
            if (is_wp_error($result)) {
                $term = get_term($sid, 'supplier');
                $name = ($term && !is_wp_error($term)) ? $term->name : ('#' . $sid);
                $errors[] = $name . ': ' . $result->get_error_message();
                continue;
            }
            $sent[]  = $result['sent_to'];
            $items  += (int) $result['items'];
        }

        if (empty($sent)) {
            wp_send_json_error(array('message' => implode(' · ', $errors)));
        }
        $message = sprintf(
            
            __('Sent %1$s item lines to %2$s.', 'ai-eshop-optimizer'),
            number_format_i18n($items),
            implode(', ', $sent)
        );
        if ($errors) {
            $message .= ' — ' . implode(' · ', $errors);
        }
        wp_send_json_success(array('message' => $message));
    }

    
    
    

    
    public function report_bulk($result, $ctx, $def) {
        if (false !== $result) { return $result; }
        if ('supplier_preorders_queue' !== (string) $ctx['report']) { return $result; }
        
        
        
        
        $map = array(
            'approve'      => 'approve',
            'approved'     => 'approve',
            'mark_ordered' => 'mark_ordered',
            'ordered'      => 'mark_ordered',
            'reject'       => 'reject',
        );
        if (!isset($map[(string) $ctx['action']])) { return $result; }
        $action = $map[(string) $ctx['action']];
        if (!class_exists('AIEO_DMM_Branch_Scanner')) {
            return __('The pre-order status flow is unavailable.', 'ai-eshop-optimizer');
        }

        global $wpdb;
        $table = $wpdb->prefix . 'aieo_dmm_supplier_preorders';
        $ids   = array_values(array_unique(array_filter(array_map('intval', (array) $ctx['ids']))));
        if (empty($ids)) {
            return array('updated' => 0, 'skipped' => 0);
        }
        $in   = implode(',', $ids);
        $rows = $wpdb->get_results(
            
            "SELECT id, status, fulfilled, supplier_term_id FROM {$table} WHERE id IN ({$in})",
            ARRAY_A
        );
        $rows = is_array($rows) ? $rows : array();

        $user_id = (int) get_current_user_id();
        
        
        $day     = wp_date('Ymd');
        $updated = 0;
        $skipped = count($ids) - count($rows);
        $batches = array();

        foreach ($rows as $r) {
            $id = (int) $r['id'];
            $st = (string) $r['status'];
            $ok = false;
            if ('approve' === $action) {
                if ('requested' === $st) {
                    $ok = !is_wp_error(AIEO_DMM_Branch_Scanner::apply_preorder_status(
                        $id, 'approved', null, $user_id, array('source' => 'queue', 'note' => 'bulk approve')
                    ));
                }
            } elseif ('reject' === $action) {
                if (0 === (int) $r['fulfilled']) {
                    $ok = !is_wp_error(AIEO_DMM_Branch_Scanner::apply_preorder_status(
                        $id, 'rejected', null, $user_id, array('source' => 'queue', 'note' => 'bulk reject')
                    ));
                }
            } elseif (in_array($st, array('requested', 'approved'), true)) {
                $batch = 'PO-' . $day . '-' . (int) $r['supplier_term_id'];
                $ok    = !is_wp_error(AIEO_DMM_Branch_Scanner::apply_preorder_status(
                    $id, 'ordered', null, $user_id, array('source' => 'queue', 'batch_ref' => $batch)
                ));
                if ($ok) { $batches[$batch] = true; }
            }
            $ok ? $updated++ : $skipped++;
        }

        $message = '';
        if ('mark_ordered' === $action && !empty($batches)) {
            $message = sprintf(
                
                __('Batches stamped: %s. Export or email each from the Ordered batches card.', 'ai-eshop-optimizer'),
                implode(', ', array_keys($batches))
            );
        }
        return array('updated' => $updated, 'skipped' => $skipped, 'message' => $message);
    }

    
    private static function batch_lines($batch_ref) {
        global $wpdb;
        $table = $wpdb->prefix . 'aieo_dmm_supplier_preorders';
        $pv    = $wpdb->prefix . 'aieo_core_product_vitals';
        $rows  = $wpdb->get_results($wpdb->prepare(
            
            "SELECT p.product_id,
                    MIN( COALESCE( NULLIF( v.product_name, '' ), NULLIF( v.name, '' ), CONCAT( '#', p.product_id ) ) ) AS name,
                    MIN( v.sku ) AS sku,
                    SUM( p.requested_qty ) AS qty,
                    MIN( p.supplier_term_id ) AS supplier_term_id
               FROM {$table} p
               LEFT JOIN {$pv} v ON v.product_id = p.product_id
              WHERE p.batch_ref = %s
              GROUP BY p.product_id
              ORDER BY qty DESC",
            $batch_ref
        ), ARRAY_A);
        $rows = is_array($rows) ? $rows : array();
        $sid  = 0;
        foreach ($rows as $r) {
            if ((int) $r['supplier_term_id'] > 0) { $sid = (int) $r['supplier_term_id']; break; }
        }
        return array('rows' => $rows, 'supplier_term_id' => $sid);
    }

    
    public function handle_batch_export() {
        if (!current_user_can(AIEO_CAPABILITY)) { wp_die(esc_html__('Insufficient permissions.', 'ai-eshop-optimizer')); }
        check_admin_referer('aieo_preorder_batch_export');

        $batch = isset($_GET['batch']) ? sanitize_text_field(wp_unslash($_GET['batch'])) : '';
        if ('' === $batch) { wp_die(esc_html__('No batch named.', 'ai-eshop-optimizer')); }

        if (!class_exists('\Rooster\Admin\Export') && function_exists('rooster_admin_builders')) {
            rooster_admin_builders();
        }
        if (!class_exists('\Rooster\Admin\Export')) { wp_die(esc_html__('XLSX writer unavailable.', 'ai-eshop-optimizer')); }

        $data = self::batch_lines($batch);
        if (empty($data['rows'])) { wp_die(esc_html__('That batch holds no lines.', 'ai-eshop-optimizer')); }

        $columns = array(
            'sku'   => array('label' => __('SKU', 'ai-eshop-optimizer'), 'type' => 'text'),
            'name'  => __('Product', 'ai-eshop-optimizer'),
            'qty'   => array('label' => __('Quantity', 'ai-eshop-optimizer'), 'format' => array('decimals' => 0)),
            'batch' => __('Batch', 'ai-eshop-optimizer'),
        );
        $lines = array();
        foreach ($data['rows'] as $r) {
            $lines[] = array('sku' => (string) $r['sku'], 'name' => (string) $r['name'], 'qty' => (int) $r['qty'], 'batch' => $batch);
        }
        \Rooster\Admin\Export::xlsx('preorder-batch-' . sanitize_title($batch) . '.xlsx', array(array('name' => $batch, 'columns' => $columns, 'rows' => $lines)));
        exit;
    }

    
    public function ajax_batch_email() {
        AIEO_DMM::verify_ajax_request();
        
        $batch = isset($_POST['batch']) ? sanitize_text_field(wp_unslash($_POST['batch'])) : '';
        if ('' === $batch) {
            wp_send_json_error(array('message' => __('No batch named.', 'ai-eshop-optimizer')));
        }

        $data = self::batch_lines($batch);
        if (empty($data['rows'])) {
            wp_send_json_error(array('message' => __('That batch holds no lines.', 'ai-eshop-optimizer')));
        }
        $sid  = (int) $data['supplier_term_id'];
        $term = $sid > 0 ? get_term($sid, 'supplier') : null;
        if (!$term || is_wp_error($term)) {
            wp_send_json_error(array('message' => __('This batch has no resolved supplier, so there is nobody to email. Export the XLSX instead.', 'ai-eshop-optimizer')));
        }

        $address = '';
        if (class_exists('AIEO_DMM_Supplier_Extras_Data')) {
            $extras  = AIEO_DMM_Supplier_Extras_Data::get('supplier', $sid);
            $address = (string) ($extras['email'] ?? '');
            if ('' === $address) { $address = (string) ($extras['corporate_email'] ?? ''); }
        }
        if ('' === $address || !is_email($address)) {
            wp_send_json_error(array('message' => __('This supplier has no order-taking email on record. Add one on the Suppliers page first.', 'ai-eshop-optimizer')));
        }

        $shop  = wp_specialchars_decode(get_bloginfo('name'), ENT_QUOTES);
        $lines = array();
        
        $lines[] = sprintf(__('Dear %s,', 'ai-eshop-optimizer'), $term->name);
        $lines[] = '';
        
        $lines[] = sprintf(__('We would like to order the following items (order reference %s):', 'ai-eshop-optimizer'), $batch);
        $lines[] = '';
        foreach ($data['rows'] as $r) {
            $lines[] = sprintf(
                '- %1$s × %2$d — %3$s',
                ('' !== (string) $r['sku']) ? (string) $r['sku'] : __('(no SKU)', 'ai-eshop-optimizer'),
                (int) $r['qty'],
                (string) $r['name']
            );
        }
        $lines[] = '';
        
        $lines[] = sprintf(__('Please confirm availability and expected delivery, quoting reference %s.', 'ai-eshop-optimizer'), $batch);
        $lines[] = '';
        $lines[] = __('Kind regards,', 'ai-eshop-optimizer');
        $lines[] = $shop;

        
        $subject = sprintf(__('Restock order %1$s from %2$s', 'ai-eshop-optimizer'), $batch, $shop);
        $sent    = wp_mail($address, $subject, implode("\n", $lines));
        if (!$sent) {
            wp_send_json_error(array('message' => __('The email could not be sent — check the site\'s outgoing mail.', 'ai-eshop-optimizer')));
        }
        wp_send_json_success(array(
            'message' => sprintf(
                
                __('Sent %1$s item lines to %2$s.', 'ai-eshop-optimizer'),
                number_format_i18n(count($data['rows'])),
                $address
            ),
        ));
    }

    
    
    

    
    
    

    
    public static function fetch_preorder_summary_by_supplier() {
        global $wpdb;
        $table = $wpdb->prefix . 'aieo_dmm_supplier_preorders';
        if (!function_exists('aieo_dmm_table_exists') || !aieo_dmm_table_exists($table)) { return array(); }
        $rows = $wpdb->get_results(
            "SELECT supplier_term_id,
                    COUNT(*)            AS line_count,
                    SUM(requested_qty)  AS total_qty,
                    MAX(created_at)     AS most_recent_at
               FROM {$table}
              WHERE fulfilled = 0
              GROUP BY supplier_term_id
              ORDER BY total_qty DESC",
            ARRAY_A
        );
        if (!is_array($rows)) { return array(); }
        foreach ($rows as &$r) {
            $r['supplier_name'] = $r['supplier_term_id']
                ? (function ($id) { $t = get_term((int) $id); return ($t && !is_wp_error($t)) ? $t->name : '— unresolved —'; })($r['supplier_term_id'])
                : '— unresolved —';
        }
        return $rows;
    }

    
    
    

    public function render_subtab() {
        
        
        
        
        if (class_exists('AIEO_DMM_Product_Picker')) {
            AIEO_DMM_Product_Picker::enqueue_assets();
        }
        $this->render_template('supplier-orders.php', array(
            'preorder_summary'  => self::fetch_preorder_summary_by_supplier(),
            'export_base'       => wp_nonce_url(admin_url('admin-post.php?action=aieo_supplier_items_export'), 'aieo_supplier_items_export'),
            
            
            'batch_export_base' => wp_nonce_url(admin_url('admin-post.php?action=aieo_preorder_batch_export'), 'aieo_preorder_batch_export'),
        ));
    }
}