<?php

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

class AIEO_DMM_Orders_Export extends AIEO_DMM_Module_Base {

    public function get_id() { return 'orders_export'; }
    public function get_label() { return __('Orders Export', 'ai-eshop-optimizer'); }
    public function get_priority() { return 5; }
    public function get_group() { return 'import_export'; }

    const FIELDS_OPTION    = 'aieo_dmm_orders_export_fields';

    
    const STATUSES_OPTION  = 'aieo_dmm_orders_export_statuses';

    
    const LAST_RANGE_OPTION = 'aieo_dmm_orders_export_last_range';

    
    const LAST_RESULT_OPTION = 'aieo_dmm_orders_export_last_result';

    
    const EXPORT_SUBDIR = 'exports';

    public function register_hooks() {
        add_action('wp_ajax_aieo_dmm_orders_export_save_fields', array($this, 'ajax_save_fields'));
        add_action('wp_ajax_aieo_dmm_orders_export_start', array($this, 'ajax_start'));
        add_action('wp_ajax_aieo_dmm_orders_export_process', array($this, 'ajax_process'));
    }

    public function render_subtab() {
        $last_range  = get_option(self::LAST_RANGE_OPTION,  array());
        $last_result = get_option(self::LAST_RESULT_OPTION, array());

        $this->render_template('orders-export.php', array(
            'available_fields'    => $this->get_available_fields(),
            'field_map'           => $this->get_field_map(),
            'selectable_statuses' => $this->selectable_statuses(),
            'selected_statuses'   => $this->get_selected_statuses(),
            'recent_jobs'         => $this->recent_jobs('orders_export', 10),
            
            
            'last_first_id'    => is_array($last_range) && !empty($last_range['first_id']) ? (int) $last_range['first_id'] : 1,
            'last_last_id'     => is_array($last_range) && !empty($last_range['last_id'])  ? (int) $last_range['last_id']  : 0,
            'last_result'      => is_array($last_result) ? $last_result : array(),
        ));
    }

    
    private function get_export_dir() {
        $uploads = wp_upload_dir();
        $base_dir = isset($uploads['basedir']) ? (string) $uploads['basedir'] : trailingslashit(WP_CONTENT_DIR) . 'uploads';
        $base_url = isset($uploads['baseurl']) ? (string) $uploads['baseurl'] : trailingslashit(WP_CONTENT_URL) . 'uploads';
        $path = trailingslashit($base_dir) . self::EXPORT_SUBDIR;
        $url  = trailingslashit($base_url) . self::EXPORT_SUBDIR;
        if (!file_exists($path)) {
            wp_mkdir_p($path);
        }
        return array('path' => $path, 'url' => $url);
    }

    
    public function get_available_fields() {
        
        
        
        
        
        
        return array(
            'OrderId'           => array('label' => __('Order ID', 'ai-eshop-optimizer'),                    'default_header' => 'OrderId'),
            'TrnDate'           => array('label' => __('Transaction date (YYYYMMDD)', 'ai-eshop-optimizer'), 'default_header' => 'TrnDate'),
            'Series'            => array('label' => __('Series', 'ai-eshop-optimizer'),                      'default_header' => 'Series'),
            'ceMail'            => array('label' => __('Customer email', 'ai-eshop-optimizer'),              'default_header' => 'ceMail'),
            'cName'             => array('label' => __('Customer name', 'ai-eshop-optimizer'),               'default_header' => 'cName'),
            'cCompanyName'      => array('label' => __('Company name', 'ai-eshop-optimizer'),                'default_header' => 'cCompanyName'),
            'cVat'              => array('label' => __('VAT number', 'ai-eshop-optimizer'),                  'default_header' => 'cVat'),
            'cVatStatus'        => array('label' => __('VAT status', 'ai-eshop-optimizer'),                  'default_header' => 'cVatStatus'),
            'ExpVal1'           => array('label' => __('Export value 1', 'ai-eshop-optimizer'),              'default_header' => 'ExpVal1'),
            'ExpVal2'           => array('label' => __('Export value 2', 'ai-eshop-optimizer'),              'default_header' => 'ExpVal2'),
            'cDoy'              => array('label' => __('Tax office (ΔΟΥ)', 'ai-eshop-optimizer'),            'default_header' => 'cDoy'),
            'billAddress'       => array('label' => __('Billing address', 'ai-eshop-optimizer'),             'default_header' => 'billAddress'),
            'billCity'          => array('label' => __('Billing city', 'ai-eshop-optimizer'),                'default_header' => 'billCity'),
            'billZip'           => array('label' => __('Billing postcode', 'ai-eshop-optimizer'),            'default_header' => 'billZip'),
            'billCountry'       => array('label' => __('Billing country', 'ai-eshop-optimizer'),             'default_header' => 'billCountry'),
            'shipAddress'       => array('label' => __('Shipping address', 'ai-eshop-optimizer'),            'default_header' => 'shipAddress'),
            'shipCity'          => array('label' => __('Shipping city', 'ai-eshop-optimizer'),               'default_header' => 'shipCity'),
            'shipZip'           => array('label' => __('Shipping postcode', 'ai-eshop-optimizer'),           'default_header' => 'shipZip'),
            'shipCountry'       => array('label' => __('Shipping country', 'ai-eshop-optimizer'),            'default_header' => 'shipCountry'),
            'mSKU'              => array('label' => __('Item SKU', 'ai-eshop-optimizer'),                    'default_header' => 'mSKU'),
            'cPhone01'          => array('label' => __('Phone 1', 'ai-eshop-optimizer'),                     'default_header' => 'cPhone01'),
            'cPhone02'          => array('label' => __('Phone 2', 'ai-eshop-optimizer'),                     'default_header' => 'cPhone02'),
            'PrimaryQty'        => array('label' => __('Quantity', 'ai-eshop-optimizer'),                    'default_header' => 'PrimaryQty'),
            'Price'             => array('label' => __('Unit price', 'ai-eshop-optimizer'),                  'default_header' => 'Price'),
            'DiscVal'           => array('label' => __('Discount value', 'ai-eshop-optimizer'),              'default_header' => 'DiscVal'),
            'cPostalCost'       => array('label' => __('Shipping cost', 'ai-eshop-optimizer'),               'default_header' => 'cPostalCost'),
            'cAntikatavoliCost' => array('label' => __('COD fee', 'ai-eshop-optimizer'),                     'default_header' => 'cAntikatavoliCost'),
            'LineValue'         => array('label' => __('Line value', 'ai-eshop-optimizer'),                  'default_header' => 'LineValue'),
            'LineVatValue'      => array('label' => __('Line VAT value', 'ai-eshop-optimizer'),              'default_header' => 'LineVatValue'),
            'cPayment'          => array('label' => __('Payment method', 'ai-eshop-optimizer'),              'default_header' => 'cPayment'),
            'cShipment'         => array('label' => __('Shipping method', 'ai-eshop-optimizer'),             'default_header' => 'cShipment'),
        );
    }

    
    public function get_field_map() {
        $stored = get_option(self::FIELDS_OPTION, null);
        if (!is_array($stored) || empty($stored)) {
            
            $out = array();
            foreach ($this->get_available_fields() as $key => $info) {
                $out[] = array('key' => $key, 'header' => $info['default_header'], 'enabled' => true);
            }
            return $out;
        }
        return $stored;
    }

    public function ajax_save_fields() {
        AIEO_DMM::verify_ajax_request();

        $raw = isset($_POST['fields']) && is_array($_POST['fields'])
            ? map_deep(wp_unslash($_POST['fields']), 'sanitize_text_field')
            : array();
        $available = $this->get_available_fields();
        $out = array();
        foreach ($raw as $entry) {
            if (!is_array($entry) || empty($entry['key'])) {
                continue;
            }
            $key = sanitize_key($entry['key']);
            if (!isset($available[$key])) {
                continue;
            }
            $out[] = array(
                'key'     => $key,
                'header'  => isset($entry['header']) ? sanitize_text_field($entry['header']) : $available[$key]['default_header'],
                'enabled' => !empty($entry['enabled']),
            );
        }
        update_option(self::FIELDS_OPTION, $out, false);
        wp_send_json_success(array('message' => __('Field map saved.', 'ai-eshop-optimizer')));
    }

    public function ajax_start() {
        AIEO_DMM::verify_ajax_request();

        $first_id = isset($_POST['first_order_id']) ? absint(wp_unslash($_POST['first_order_id'])) : 1;
        $last_id  = isset($_POST['last_order_id']) ? absint(wp_unslash($_POST['last_order_id'])) : 0;
        $format   = isset($_POST['format']) && sanitize_key(wp_unslash($_POST['format'])) === 'xml' ? 'xml' : 'csv';
        $cache_mode_allowed = array('stable_url', 'version_query', 'unique_filename');
        $cache_mode = isset($_POST['cache_mode']) ? sanitize_key(wp_unslash($_POST['cache_mode'])) : 'stable_url';
        if (!in_array($cache_mode, $cache_mode_allowed, true)) {
            $cache_mode = 'stable_url';
        }

        
        
        
        
        
        $selectable   = array_keys($this->selectable_statuses());
        $req_statuses = (isset($_POST['statuses']) && is_array($_POST['statuses']))
            ? array_map('sanitize_text_field', wp_unslash($_POST['statuses']))
            : array();
        $statuses = array_values(array_intersect($req_statuses, $selectable));
        if (empty($statuses)) {
            $statuses = $this->get_selected_statuses(); 
        }
        update_option(self::STATUSES_OPTION, $statuses, false);

        
        $args = array('limit' => -1, 'type' => 'shop_order', 'return' => 'ids');
        
        global $wpdb;
        $table_orders = $this->get_orders_table();
        $where = array('type = %s');
        $params = array('shop_order');
        $where[] = 'id >= %d';
        $params[] = $first_id;
        if ($last_id > 0) {
            $where[] = 'id <= %d';
            $params[] = $last_id;
        }
        
        
        $sql = "SELECT COUNT(*) FROM {$table_orders} WHERE " . implode(' AND ', $where) . $this->status_in_sql($statuses);
        
        $total = (int) $wpdb->get_var($wpdb->prepare($sql, $params));

        if ($total <= 0) {
            wp_send_json_error(array('message' => __('No orders match the range.', 'ai-eshop-optimizer')));
        }

        
        update_option(self::LAST_RANGE_OPTION, array(
            'first_id'  => (int) $first_id,
            'last_id'   => (int) $last_id,
            'saved_at'  => current_time('mysql', true),
        ), false);

        
        
        
        $dir    = $this->get_export_dir();
        $job_id = wp_generate_password(16, false, false);
        $ext    = ($format === 'xml') ? 'xml' : 'csv';

        
        
        
        
        
        
        
        if ($cache_mode === 'unique_filename') {
            $filename = 'orders-export-' . $job_id . '.' . $ext;
        } else {
            
            
            $filename = 'exported_orders.' . $ext;
        }
        $path = trailingslashit($dir['path']) . $filename;
        $url  = trailingslashit($dir['url']) . $filename;

        
        
        
        
        
        $headers = $this->softone_headers();
        $h = fopen($path, 'w');
        if (!$h) {
            wp_send_json_error(array('message' => __('Cannot create export file.', 'ai-eshop-optimizer')));
        }
        if ($format === 'xml') {
            fwrite($h, "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<orders>\n");
        } else {
            fputcsv($h, $headers);
        }
        fclose($h);

        global $wpdb;
        $jobs = $wpdb->prefix . 'aieo_dmm_export_jobs';
        $wpdb->insert($jobs, array(
            'job_id'    => $job_id,
            'type'      => 'orders_export',
            'status'    => 'running',
            'total'     => $total,
            'processed' => 0,
            'filename'  => $filename,
            'params'    => wp_json_encode(array(
                'first_id'   => $first_id,
                'last_id'    => $last_id,
                'format'     => $format,
                'cache_mode' => $cache_mode,
                'statuses'   => $statuses,
                'path'       => $path,
                'url'        => $url,
                'cursor'     => max(0, $first_id - 1),
            )),
            'user_id'   => get_current_user_id(),
        ), array('%s', '%s', '%s', '%d', '%d', '%s', '%s', '%d'));

        wp_send_json_success(array('job_id' => $job_id, 'total' => $total));
    }

    public function ajax_process() {
        AIEO_DMM::verify_ajax_request();

        $job_id = isset($_POST['job_id']) ? sanitize_text_field(wp_unslash($_POST['job_id'])) : '';
        global $wpdb;
        $jobs = $wpdb->prefix . 'aieo_dmm_export_jobs';
        
        $job = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$jobs} WHERE job_id = %s", $job_id), ARRAY_A);
        if (!$job || $job['type'] !== 'orders_export') {
            wp_send_json_error(array('message' => __('Invalid job.', 'ai-eshop-optimizer')));
        }
        $params = json_decode($job['params'], true);
        if (!is_array($params)) {
            wp_send_json_error(array('message' => __('Corrupted job.', 'ai-eshop-optimizer')));
        }
        $first_id = (int) $params['first_id'];
        $last_id  = (int) $params['last_id'];
        $cursor   = (int) $params['cursor'];
        $path     = (string) $params['path'];
        $format   = isset($params['format']) && $params['format'] === 'xml' ? 'xml' : 'csv';
        
        
        $statuses = (isset($params['statuses']) && is_array($params['statuses'])) ? $params['statuses'] : array();

        $batch_size = 100;

        $table_orders = $this->get_orders_table();
        $where = 'type = %s AND id > %d';
        $args  = array('shop_order', $cursor);
        if ($last_id > 0) {
            $where .= ' AND id <= %d';
            $args[] = $last_id;
        }
        
        
        
        
        $where .= $this->status_in_sql($statuses);
        
        $sql = $wpdb->prepare(
            "SELECT id FROM {$table_orders} WHERE {$where} AND id >= %d ORDER BY id ASC LIMIT %d",
            array_merge($args, array($first_id, $batch_size))
        );
        $order_ids = array_map('intval', (array) $wpdb->get_col($sql));

        $rows_written = 0;

        $h = fopen($path, 'a');
        if (!$h) {
            wp_send_json_error(array('message' => __('Cannot append to export file.', 'ai-eshop-optimizer')));
        }

        
        
        
        
        
        $results = $this->softone_query($order_ids);
        $cols    = $this->softone_headers();
        foreach ($results as $r) {
            $row = $this->softone_row($r);
            
            
            $row = apply_filters('aieo_dmm_export_orders_row', $row, array('result' => $r, 'order_id' => (int) $r->OrderId));
            if ($format === 'xml') {
                fwrite($h, "  <order>\n");
                foreach (array_values($row) as $i => $val) {
                    $tag = isset($cols[$i]) ? preg_replace('/[^A-Za-z0-9_]/', '', (string) $cols[$i]) : ('col' . $i);
                    fwrite($h, "    <{$tag}>" . htmlspecialchars((string) $val, ENT_QUOTES | ENT_XML1, 'UTF-8') . "</{$tag}>\n");
                }
                fwrite($h, "  </order>\n");
            } else {
                fputcsv($h, array_values($row));
            }
            $rows_written++;
        }
        fclose($h);
        if (!empty($order_ids)) { $cursor = max($order_ids); }

        $processed = (int) $job['processed'] + count($order_ids);
        $complete = count($order_ids) < $batch_size;

        $params['cursor'] = $cursor;

        
        
        $public_url = $params['url'];
        $etag = '';
        if ($complete) {
            if ($format === 'xml') {
                $closing = fopen($path, 'a');
                if ($closing) {
                    fwrite($closing, "</orders>\n");
                    fclose($closing);
                }
            }
            $etag = @md5_file($path);
            $etag_short = $etag ? substr($etag, 0, 10) : (string) time();
            $params['etag'] = $etag_short;

            $cache_mode = isset($params['cache_mode']) ? (string) $params['cache_mode'] : 'stable_url';
            $public_url = AIEO_DMM_Marketplace_Feeds::compute_public_url(
                $path,
                
                ($cache_mode === 'stable_url' || $cache_mode === 'version_query') ? 'version_query' : $cache_mode,
                $etag_short
            );
            $params['public_url'] = $public_url;

            
            
            
            
            
            update_option(self::LAST_RESULT_OPTION, array(
                'public_url'    => $public_url,
                'path'          => $path,
                'filename'      => isset($params['url']) ? basename((string) $params['url']) : basename($path),
                'etag'          => $etag_short,
                'rows_total'    => (int) $job['total'],
                'rows_processed'=> (int) ($processed ?? 0),
                'first_id'      => (int) ($params['first_id'] ?? 0),
                'last_id'       => (int) ($params['last_id'] ?? 0),
                
                
                
                'completed_at'  => current_time('mysql', true),
            ), false);
        }

        $wpdb->update($jobs, array(
            'processed' => $processed,
            'status'    => $complete ? 'complete' : 'running',
            'params'    => wp_json_encode($params),
        ), array('job_id' => $job_id), array('%d', '%s', '%s'), array('%s'));

        wp_send_json_success(array(
            'processed'    => $processed,
            'total'        => (int) $job['total'],
            'written'      => $rows_written,
            'complete'     => $complete,
            'percentage'   => (int) $job['total'] > 0 ? (int) floor(min(100, ($processed / (int) $job['total']) * 100)) : 100,
            'download_url' => $complete ? $public_url : null,
            'path'         => $complete ? $path : null,
            'public_url'   => $complete ? $public_url : null,
            'etag'         => $complete ? ($etag ? substr($etag, 0, 10) : '') : '',
        ));
    }

    
    public static function excluded_statuses() {
        return (array) apply_filters('aieo_dmm_orders_export_excluded_statuses', array(
            'wc-checkout-draft',
            'checkout-draft', 
            'auto-draft',
            'trash',
        ));
    }

    
    public function selectable_statuses() {
        $out = array();
        if (function_exists('wc_get_order_statuses')) {
            foreach (wc_get_order_statuses() as $slug => $label) {
                $out[(string) $slug] = (string) $label;
            }
        }
        foreach (self::excluded_statuses() as $ex) {
            unset($out[$ex]);
        }
        return $out;
    }

    
    public function get_selected_statuses() {
        $valid  = array_keys($this->selectable_statuses());
        $stored = get_option(self::STATUSES_OPTION, null);
        if (is_array($stored)) {
            $sel = array_values(array_intersect(array_map('strval', $stored), $valid));
            if (!empty($sel)) {
                return $sel;
            }
        }
        
        
        return in_array('wc-completed', $valid, true) ? array('wc-completed') : $valid;
    }

    
    private function status_in_sql(array $slugs) {
        $slugs = array_values(array_unique(array_filter(array_map('strval', $slugs))));
        if (empty($slugs)) {
            $slugs = array('wc-completed');
        }
        $in = "'" . implode("','", array_map('esc_sql', $slugs)) . "'";
        return " AND status IN ({$in})";
    }

    
    private function get_orders_table() {
        global $wpdb;
        $hpos = $wpdb->prefix . 'wc_orders';
        if (aieo_dmm_table_exists($hpos)) {
            return $hpos;
        }
        return $wpdb->posts;
    }

    
    private function softone_headers() {
        $out = array();
        foreach ($this->get_available_fields() as $info) {
            $out[] = $info['default_header'];
        }
        return $out;
    }

    
    private function softone_row($r) {
        $out = array();
        foreach (array_keys($this->get_available_fields()) as $alias) {
            $out[] = isset($r->$alias) ? (string) $r->$alias : '';
        }
        return $out;
    }

    
    private function softone_query(array $order_ids) {
        global $wpdb;
        $order_ids = array_values(array_unique(array_filter(array_map('intval', $order_ids))));
        if (empty($order_ids)) {
            return array();
        }
        $in = implode(',', $order_ids);
        $p  = $wpdb->prefix;

        $sql = "SELECT o.id as OrderId,
        CAST(SUBSTR(DATE_FORMAT(o.date_created_gmt, '%Y%m%d'), 1, 10) AS CHAR) as TrnDate,
        1 as Series,
        oba.email as ceMail,
        concat(oba.first_name, ' ' , oba.last_name) as cName,
        replace(pmcompany.meta_value, ',', ' ') as cCompanyName,
        pmvat.meta_value as cVat,
        (case when pmvat.meta_value <>'' then 2 else 1 end) as cVatStatus,
        '222' as ExpVal1,
        '22222' as ExpVal2,
        pmdoy.meta_value as cDoy,
        replace(concat(oba.address_1, ' ', oba.address_2),  ',', ' ') as billAddress,
        replace(oba.city, ',', ' ') as  billCity,
        replace(oba.postcode, ',', ' ') as  billZip,
        replace(oba.country, ',', ' ') as  billCountry,
        replace(concat(osa.address_1, ' ', osa.address_2),  ',', ' ') as shipAddress,
        replace(osa.city, ',', ' ') as  shipCity,
        replace(osa.postcode, ',', ' ') as  shipZip,
        replace(osa.country, ',', ' ') as  shipCountry,
        (CASE WHEN oimpv.meta_value=0 THEN
        pv.sku  ELSE pvv.sku
        END) as mSKU,
        oba.phone as cPhone01,
        '' as cPhone02,
        convert(oimq.meta_value, decimal(3,0)) as PrimaryQty,
        convert(convert(oims.meta_value, decimal(7,2))/convert(oimq.meta_value, decimal(3,0)), decimal(5,2)) as Price,
        convert(ood.discount_total_amount, decimal(7,2)) as DiscVal,
        convert(ood.shipping_total_amount, decimal(7,2)) as cPostalCost,
        (CASE WHEN o.payment_method_title = 'Αντικαταβολή' THEN
         1.5 ELSE   0
         END)  as cAntikatavoliCost,
        convert(oims.meta_value, decimal(7,2)) as LineValue,
        '' as LineVatValue,
        o.payment_method_title as cPayment,
        (SELECT order_item_name FROM {$p}woocommerce_order_items  where order_item_type='shipping' and order_id=o.id LIMIT 1) as cShipment

        FROM {$p}woocommerce_order_items oi
        join {$p}wc_orders o on o.id=oi.order_id
        join {$p}woocommerce_order_itemmeta oimp on oi.order_item_id=oimp.order_item_id and oimp.meta_key='_product_id'
        join {$p}woocommerce_order_itemmeta oimpv on oi.order_item_id=oimpv.order_item_id and oimpv.meta_key='_variation_id'
        left join {$p}woocommerce_order_itemmeta oimq on oi.order_item_id=oimq.order_item_id and oimq.meta_key='_qty'
        left join {$p}woocommerce_order_itemmeta oims on oi.order_item_id=oims.order_item_id and oims.meta_key='_line_subtotal'
        left join {$p}wc_order_operational_data ood on  ood.order_id=o.id
        left join {$p}aieo_core_product_vitals pv on oimp.meta_value=pv.product_id
        left join {$p}aieo_core_product_vitals pvv on oimpv.meta_value=pvv.product_id
        join {$p}wc_order_addresses oba on oba.order_id = o.id and oba.address_type='billing'
        join {$p}wc_order_addresses osa on osa.order_id = o.id and osa.address_type='shipping'
        left join {$p}wc_orders_meta pmcompany on pmcompany.order_id=ood.order_id and pmcompany.meta_key='_billing_store'
        left join {$p}wc_orders_meta pmvat on pmvat.order_id=o.id and pmvat.meta_key='_billing_vat'
        left join {$p}wc_orders_meta pmdoy on pmdoy.order_id=o.id and pmdoy.meta_key='_billing_irs'
        where oi.order_item_type='line_item' and o.type='shop_order' and oi.order_id IN ({$in})";

        return (array) $wpdb->get_results($sql);
    }

    
    private function build_row($order, $item, array $map) {
        $row = array();
        foreach ($map as $entry) {
            if (empty($entry['enabled'])) {
                continue;
            }
            $row[$entry['header']] = $this->field_value($entry['key'], $order, $item);
        }
        return $row;
    }

    private function field_value($key, $order, $item) {
        switch ($key) {
            case 'order_id':          return (string) $order->get_id();
            case 'order_date':        return $order->get_date_created() ? $order->get_date_created()->date('Y-m-d H:i:s') : '';
            case 'status':            return $order->get_status();
            case 'currency':          return $order->get_currency();
            case 'total':             return (string) $order->get_total();
            case 'subtotal':          return (string) $order->get_subtotal();
            case 'discount_total':    return (string) $order->get_discount_total();
            case 'tax_total':         return (string) $order->get_total_tax();
            case 'shipping_total':    return (string) $order->get_shipping_total();
            case 'customer_email':    return (string) $order->get_billing_email();
            case 'customer_name':     return trim($order->get_billing_first_name() . ' ' . $order->get_billing_last_name());
            case 'company':           return (string) $order->get_billing_company();
            case 'vat':               return (string) ($order->get_meta('_billing_vat_number') ?: $order->get_meta('_vat_number'));
            case 'billing_address_1': return (string) $order->get_billing_address_1();
            case 'billing_city':      return (string) $order->get_billing_city();
            case 'billing_postcode':  return (string) $order->get_billing_postcode();
            case 'billing_country':   return (string) $order->get_billing_country();
            case 'shipping_address_1':return (string) $order->get_shipping_address_1();
            case 'shipping_city':     return (string) $order->get_shipping_city();
            case 'shipping_postcode': return (string) $order->get_shipping_postcode();
            case 'shipping_country':  return (string) $order->get_shipping_country();
            case 'payment_method':    return (string) $order->get_payment_method();
            case 'shipping_method':   return (string) $order->get_shipping_method();
            case 'item_sku':
                $p = is_callable(array($item, 'get_product')) ? $item->get_product() : null;
                return $p ? (string) $p->get_sku() : '';
            case 'item_name':
                return is_callable(array($item, 'get_name')) ? (string) $item->get_name() : '';
            case 'item_qty':
                return is_callable(array($item, 'get_quantity')) ? (string) $item->get_quantity() : '';
            case 'item_price':
                return is_callable(array($item, 'get_subtotal')) && $item->get_quantity() > 0
                    ? (string) ((float) $item->get_subtotal() / (int) $item->get_quantity())
                    : '';
            case 'item_total':
                return is_callable(array($item, 'get_total')) ? (string) $item->get_total() : '';
            case 'item_tax':
                return is_callable(array($item, 'get_total_tax')) ? (string) $item->get_total_tax() : '';
        }
        return '';
    }

    
    public function recent_jobs($type, $limit) {
        global $wpdb;
        $table = $wpdb->prefix . 'aieo_dmm_export_jobs';
        if (!aieo_dmm_table_exists($table)) {
            return array();
        }
        
        
        
        
        
        
        
        self::sweep_stale_running_jobs($type, 5);
        
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} WHERE type = %s ORDER BY created_at DESC LIMIT %d",
            $type,
            (int) $limit
        ), ARRAY_A);
        return is_array($rows) ? $rows : array();
    }

    
    public static function sweep_stale_running_jobs($type, $minutes = 5) {
        global $wpdb;
        $table = $wpdb->prefix . 'aieo_dmm_export_jobs';
        if (!aieo_dmm_table_exists($table)) {
            return 0;
        }
        $minutes = max(1, (int) $minutes);
        
        $count = (int) $wpdb->query($wpdb->prepare(
            "UPDATE {$table}
                SET status = 'aborted',
                    log    = CONCAT(IFNULL(log, ''), '\n[auto-aborted: page navigated away — last tick ', updated_at, ' UTC]')
              WHERE type = %s
                AND status = 'running'
                AND updated_at < (UTC_TIMESTAMP() - INTERVAL {$minutes} MINUTE)",
            $type
        ));
        return $count;
    }
}
