<?php

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

class AIEO_DMM_Shipping_Cost_Resolver {

    const HOOK = 'aieo_dmm_resolve_shipping_costs';
    const BATCH_SIZE = 200;        

    private static $instance = null;
    public static function instance() {
        if (self::$instance === null) { self::$instance = new self(); }
        return self::$instance;
    }

    private function __construct() {
        add_action(self::HOOK, array($this, 'run'));
        
        
        
        
        if (!wp_next_scheduled(self::HOOK)) {
            wp_schedule_event(strtotime('tomorrow 03:30'), 'daily', self::HOOK);
        }

        
        
        
        
        add_action('wp_ajax_aieo_dmm_fs_cost_step', array($this, 'ajax_step'));
    }

    const QUEUE_TRANSIENT  = 'aieo_dmm_fs_cost_queue';
    const SCAN_BATCH_SIZE  = 200;          
    const RESOLVE_BATCH_SIZE = 50;          

    
    public function run() {
        if (!function_exists('wc_get_order')) { return 0; }
        global $wpdb;
        $tbl = $wpdb->prefix . 'aieo_dmm_offer_attributions';
        if (!aieo_dmm_table_exists($tbl)) { return 0; }

        
        
        
        
        
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT attribution_id, order_id FROM {$tbl}
             WHERE offer_type = 'free_shipping'
               AND (actual_cost IS NULL OR shipping_method IS NULL)
             ORDER BY attribution_id ASC
             LIMIT %d",
            self::BATCH_SIZE
        ), ARRAY_A);
        if (empty($rows)) { return 0; }

        $resolved = 0;
        foreach ($rows as $row) {
            $update = $this->build_resolve_update($row);
            $wpdb->update($tbl, $update, array('attribution_id' => (int) $row['attribution_id']));
            $resolved++;
        }
        return $resolved;
    }

    
    private function build_resolve_update(array $row) {
        $result = $this->compute_shipping_cost_for_order((int) $row['order_id']);
        if ($result === null) {
            
            
            
            
            return array(
                'actual_cost'        => 0,
                'cost_basis'         => 'unknown_proxy',
                'shipping_method'    => '(unresolved)',
                'shipping_method_id' => '(unresolved)',
                'shipping_zone'      => '(unresolved)',
                'shipping_zone_id'   => 0,
            );
        }
        
        
        
        return array(
            'actual_cost'        => round((float) $result['cost'], 2),
            'applied_value'      => round((float) $result['cost'], 2),
            'cost_basis'         => 'forgone_revenue',
            'shipping_method'    => $result['method_label'] !== '' ? mb_substr($result['method_label'], 0, 120) : '(unknown)',
            'shipping_method_id' => $result['method_id']    !== '' ? mb_substr($result['method_id'],    0, 60)  : '(unknown)',
            'shipping_zone'      => $result['zone_name']    !== '' ? mb_substr($result['zone_name'],    0, 80)  : '(unknown)',
            'shipping_zone_id'   => $result['zone_id']      > 0    ? (int) $result['zone_id']                   : 0,
        );
    }

    
    public function ajax_step() {
        AIEO_DMM::verify_ajax_request();
        $phase = isset($_POST['phase']) ? sanitize_key(wp_unslash($_POST['phase'])) : 'init';
        $days  = isset($_POST['days']) ? max(1, min(365, absint(wp_unslash($_POST['days'])))) : 90;

        if ($phase === 'init') {
            $queue = $this->build_scan_queue($days);
            set_transient(self::QUEUE_TRANSIENT, $queue, HOUR_IN_SECONDS);
            wp_send_json_success(array(
                'phase'        => empty($queue) ? 'resolve' : 'scan',
                'total_to_scan' => count($queue),
                'scanned'      => 0,
                'inserted'     => 0,
                'resolved'     => 0,
                'remaining'    => $this->count_pending_resolve(),
                'message'      => sprintf(
                    
                    __('Scan queue built: %1$d candidate orders, %2$d attribution rows already pending cost resolution.', 'ai-eshop-optimizer'),
                    count($queue),
                    $this->count_pending_resolve()
                ),
            ));
        }

        if ($phase === 'scan') {
            $queue = (array) get_transient(self::QUEUE_TRANSIENT);
            if (empty($queue)) {
                
                wp_send_json_success(array(
                    'phase'     => 'resolve',
                    'inserted'  => 0,
                    'remaining' => $this->count_pending_resolve(),
                    'message'   => __('Scan complete — switching to cost resolution.', 'ai-eshop-optimizer'),
                ));
            }
            $batch = array_splice($queue, 0, self::SCAN_BATCH_SIZE);
            $inserted = $this->scan_orders_batch($batch);
            
            if (!empty($queue)) {
                set_transient(self::QUEUE_TRANSIENT, $queue, HOUR_IN_SECONDS);
                $next_phase = 'scan';
            } else {
                delete_transient(self::QUEUE_TRANSIENT);
                $next_phase = 'resolve';
            }
            wp_send_json_success(array(
                'phase'      => $next_phase,
                'inserted'   => $inserted,
                'remaining'  => count($queue),
                'pending_resolve' => $this->count_pending_resolve(),
                'message'    => sprintf(
                    
                    __('Scanned %1$d more orders; %2$d still queued.', 'ai-eshop-optimizer'),
                    $inserted,
                    count($queue)
                ),
            ));
        }

        if ($phase === 'resolve') {
            $resolved = $this->run_batch(self::RESOLVE_BATCH_SIZE);
            $pending  = $this->count_pending_resolve();
            wp_send_json_success(array(
                'phase'      => $pending > 0 ? 'resolve' : 'done',
                'resolved'   => $resolved,
                'remaining'  => $pending,
                'message'    => $pending > 0
                    ? sprintf(
                        
                        __('Resolved %1$d more rows; %2$d still pending.', 'ai-eshop-optimizer'),
                        $resolved,
                        $pending
                    )
                    : __('All free-shipping rows resolved.', 'ai-eshop-optimizer'),
            ));
        }

        wp_send_json_success(array('phase' => 'done', 'message' => __('Nothing to do.', 'ai-eshop-optimizer')));
    }

    
    private function build_scan_queue($days) {
        global $wpdb;
        $tbl = $wpdb->prefix . 'aieo_dmm_offer_attributions';
        if (!aieo_dmm_table_exists($tbl)) { return array(); }

        $orders_tbl = $wpdb->prefix . 'wc_orders';
        $opdata_tbl = $wpdb->prefix . 'wc_order_operational_data';

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

        
        
        
        
        $sql = $wpdb->prepare(
            "SELECT o.id
             FROM {$orders_tbl} o
             INNER JOIN {$opdata_tbl} od ON od.order_id = o.id
             LEFT JOIN {$tbl} a
                 ON a.order_id = o.id AND a.offer_type = 'free_shipping'
             WHERE o.type = 'shop_order'
               AND o.status IN ('wc-processing','wc-completed','wc-on-hold','processing','completed','on-hold')
               AND od.shipping_total_amount = 0
               AND o.date_created_gmt >= DATE_SUB(NOW(), INTERVAL %d DAY)
               AND a.attribution_id IS NULL
             ORDER BY o.date_created_gmt ASC",
            (int) $days
        );
        $ids = $wpdb->get_col($sql);
        if (!is_array($ids)) { return array(); }

        $out = array();
        foreach ($ids as $id) { $out[] = (int) $id; }
        return $out;
    }

    
    private function scan_orders_batch(array $order_ids) {
        global $wpdb;
        $tbl = $wpdb->prefix . 'aieo_dmm_offer_attributions';
        if (!aieo_dmm_table_exists($tbl) || empty($order_ids)) { return 0; }
        $inserted = 0;
        foreach ($order_ids as $order_id) {
            $order = wc_get_order((int) $order_id);
            if (!$order) { continue; }
            $shipping_total = (float) $order->get_shipping_total();
            $shipping_lines = $order->get_shipping_methods();
            if ($shipping_total > 0 || empty($shipping_lines)) { continue; }

            $entry_channel = (string) $order->get_meta('_aieo_entry_channel', true);
            if ($entry_channel === '') { $entry_channel = 'unattributed'; }

            
            
            
            
            
            $order_date = $order->get_date_created();
            $fired_at   = $order_date ? $order_date->date('Y-m-d H:i:s') : current_time('mysql', true);

            $wpdb->insert($tbl, array(
                'order_id'          => (int) $order_id,
                'offer_id'          => 0,
                'offer_type'        => 'free_shipping',
                'offer_label'       => '(retrospective)',
                'applied_value'     => 0.00,
                'actual_cost'       => null,
                'cost_basis'        => 'forgone_revenue',
                'order_total'       => (float) $order->get_total(),
                'entry_channel'    => $entry_channel,
                'user_id'           => (int) $order->get_user_id(),
                'target_product_id' => 0,
                'fired_at'          => $fired_at,
            ));
            $inserted++;
        }
        return $inserted;
    }

    
    private function count_pending_resolve() {
        global $wpdb;
        $tbl = $wpdb->prefix . 'aieo_dmm_offer_attributions';
        if (!aieo_dmm_table_exists($tbl)) { return 0; }
        
        return (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$tbl}
             WHERE offer_type = 'free_shipping'
               AND (actual_cost IS NULL OR shipping_method IS NULL)"
        );
    }

    
    private function run_batch($batch_size) {
        if (!function_exists('wc_get_order')) { return 0; }
        global $wpdb;
        $tbl = $wpdb->prefix . 'aieo_dmm_offer_attributions';
        if (!aieo_dmm_table_exists($tbl)) { return 0; }
        
        
        
        
        
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT attribution_id, order_id FROM {$tbl}
             WHERE offer_type = 'free_shipping'
               AND (actual_cost IS NULL OR shipping_method IS NULL)
             ORDER BY attribution_id ASC LIMIT %d",
            (int) $batch_size
        ), ARRAY_A);
        if (empty($rows)) { return 0; }
        $resolved = 0;
        foreach ($rows as $row) {
            $update = $this->build_resolve_update($row);
            $wpdb->update($tbl, $update, array('attribution_id' => (int) $row['attribution_id']));
            $resolved++;
        }
        return $resolved;
    }

    
    public function resolve_for_order($order_id) {
        return $this->compute_shipping_cost_for_order((int) $order_id);
    }

    
    private function compute_shipping_cost_for_order($order_id) {
        $order = wc_get_order($order_id);
        if (!$order) { return null; }
        if (!class_exists('WC_Shipping')) { return null; }

        
        
        
        
        
        
        
        $shipping_lines = $order->get_shipping_methods();
        if (empty($shipping_lines)) { return null; }
        $line = reset($shipping_lines);

        $actual_method_label = (string) $line->get_name();
        $actual_method_slug  = (string) $line->get_method_id();          
        $actual_instance_id  = (int)    $line->get_instance_id();
        $actual_rate_id      = $actual_instance_id > 0
            ? $actual_method_slug . ':' . $actual_instance_id
            : $actual_method_slug;

        
        
        if (strpos($actual_method_slug, 'local_pickup') !== false
            || strpos($actual_method_slug, 'pickup_location') !== false
            || strpos($actual_method_slug, 'store_pickup') !== false
            || strpos($actual_method_label, 'Παραλαβή') !== false
            || strpos($actual_method_label, 'pickup') !== false) {
            return array(
                'cost'         => 0.0,
                'method_id'    => $actual_rate_id !== '' ? $actual_rate_id : 'local_pickup',
                'method_label' => $actual_method_label !== '' ? $actual_method_label : 'Local pickup',
                'zone_id'      => 0,
                'zone_name'    => '',
            );
        }

        
        $package = array(
            'contents'        => array(),
            'contents_cost'   => 0,
            'applied_coupons' => array(),
            'destination'     => array(
                'country'   => $order->get_shipping_country() ?: $order->get_billing_country(),
                'state'     => $order->get_shipping_state()   ?: $order->get_billing_state(),
                'postcode'  => $order->get_shipping_postcode()?: $order->get_billing_postcode(),
                'city'      => $order->get_shipping_city()    ?: $order->get_billing_city(),
                'address'   => $order->get_shipping_address_1()?: $order->get_billing_address_1(),
                'address_2' => $order->get_shipping_address_2()?: $order->get_billing_address_2(),
            ),
            'user'            => array('ID' => $order->get_user_id()),
            'cart_subtotal'   => (float) $order->get_subtotal(),
        );
        foreach ($order->get_items() as $line) {
            $product_id = (int) ($line->get_variation_id() ?: $line->get_product_id());
            $product = wc_get_product($product_id);
            if (!$product) { continue; }
            $qty = max(1, (int) $line->get_quantity());
            $package['contents'][] = array(
                'data'         => $product,
                'product_id'   => (int) $line->get_product_id(),
                'variation_id' => (int) $line->get_variation_id(),
                'quantity'     => $qty,
                'line_total'   => (float) $line->get_total(),
                'line_subtotal'=> (float) $line->get_subtotal(),
            );
            $package['contents_cost'] += (float) $line->get_total();
        }
        if (empty($package['contents'])) { return null; }

        
        
        
        
        $zone_id   = 0;
        $zone_name = '';
        if (class_exists('WC_Shipping_Zones')) {
            $zone = WC_Shipping_Zones::get_zone_matching_package($package);
            if ($zone) {
                $zone_id   = (int) $zone->get_id();
                $zone_name = (string) $zone->get_zone_name();
            }
        }

        
        
        
        $shipping = WC_Shipping::instance();
        if (!$shipping || !method_exists($shipping, 'calculate_shipping_for_package')) { return null; }

        
        
        $fs_module = class_exists('AIEO_DMM') ? AIEO_DMM::instance()->get_module('free_shipping') : null;
        $had_filter = false;
        if ($fs_module && method_exists($fs_module, 'apply_unified_free_shipping_rules')) {
            $had_filter = (bool) remove_filter('woocommerce_package_rates', array($fs_module, 'apply_unified_free_shipping_rules'), 110);
        }

        try {
            $rates_pkg = $shipping->calculate_shipping_for_package($package);
        } catch (\Throwable $e) {
            $rates_pkg = false;
        }

        if ($had_filter && $fs_module) {
            
            add_filter('woocommerce_package_rates', array($fs_module, 'apply_unified_free_shipping_rules'), 110, 2);
        }

        if (!$rates_pkg || empty($rates_pkg['rates'])) {
            
            
            
            return array(
                'cost'         => 0.0,
                'method_id'    => $actual_rate_id,
                'method_label' => $actual_method_label,
                'zone_id'      => $zone_id,
                'zone_name'    => $zone_name,
            );
        }

        
        
        
        
        
        $matching_rate = null;
        $cheapest      = null;
        $cheapest_rate = null;
        foreach ($rates_pkg['rates'] as $rate) {
            if (!is_object($rate) || !method_exists($rate, 'get_cost')) { continue; }
            $rate_id   = method_exists($rate, 'get_id') ? (string) $rate->get_id() : (string) ($rate->id ?? '');
            $rate_cost = (float) $rate->get_cost();

            
            if ($actual_rate_id !== '' && $rate_id === $actual_rate_id) {
                $matching_rate = $rate;
                break;
            }
            
            if ($rate_cost > 0 && ($cheapest === null || $rate_cost < $cheapest)) {
                $cheapest      = $rate_cost;
                $cheapest_rate = $rate;
            }
        }

        
        
        if (!$matching_rate && $actual_method_slug !== '') {
            foreach ($rates_pkg['rates'] as $rate) {
                if (!is_object($rate)) { continue; }
                $rate_method = method_exists($rate, 'get_method_id') ? (string) $rate->get_method_id() : (string) ($rate->method_id ?? '');
                if ($rate_method === $actual_method_slug) { $matching_rate = $rate; break; }
            }
        }

        $picked = $matching_rate ?: $cheapest_rate;
        $cost   = $picked && method_exists($picked, 'get_cost') ? (float) $picked->get_cost() : 0.0;

        
        
        
        
        
        
        
        if ($cost <= 0 && $actual_method_label !== '') {
            $cost = $this->avg_paid_cost_for_method($actual_method_label, $zone_name);
        }

        return array(
            'cost'         => $cost,
            
            
            'method_id'    => $actual_rate_id !== '' ? $actual_rate_id : (
                $picked && method_exists($picked, 'get_id') ? (string) $picked->get_id() : ''
            ),
            'method_label' => $actual_method_label !== '' ? $actual_method_label : (
                $picked && method_exists($picked, 'get_label') ? (string) $picked->get_label() : ''
            ),
            'zone_id'      => $zone_id,
            'zone_name'    => $zone_name,
        );
    }

    
    private static $paid_avg_cache = null;
    private function avg_paid_cost_for_method($method_label, $zone_name) {
        if (self::$paid_avg_cache === null) {
            self::$paid_avg_cache = $this->build_paid_avg_lookup();
        }
        $key = mb_strtolower(trim($method_label)) . '|' . mb_strtolower(trim($zone_name));
        if (isset(self::$paid_avg_cache[$key])) {
            return (float) self::$paid_avg_cache[$key];
        }
        
        $key_method = mb_strtolower(trim($method_label));
        return isset(self::$paid_avg_cache['__method__' . $key_method])
            ? (float) self::$paid_avg_cache['__method__' . $key_method]
            : 0.0;
    }

    
    private function build_paid_avg_lookup() {
        global $wpdb;
        $orders_tbl   = $wpdb->prefix . 'wc_orders';
        $opdata_tbl   = $wpdb->prefix . 'wc_order_operational_data';
        $items_tbl    = $wpdb->prefix . 'woocommerce_order_items';
        $itemmeta_tbl = $wpdb->prefix . 'woocommerce_order_itemmeta';
        $zonemeth_tbl = $wpdb->prefix . 'woocommerce_shipping_zone_methods';
        $zones_tbl    = $wpdb->prefix . 'woocommerce_shipping_zones';

        
        
        
        $rows = $wpdb->get_results(
            "SELECT
                LOWER(TRIM(oi.order_item_name)) AS method_lc,
                LOWER(TRIM(COALESCE(z.zone_name, ''))) AS zone_lc,
                AVG(od.shipping_total_amount) AS avg_cost
             FROM {$orders_tbl} o
             INNER JOIN {$opdata_tbl} od ON od.order_id = o.id
             INNER JOIN {$items_tbl}  oi ON oi.order_id = o.id AND oi.order_item_type = 'shipping'
             LEFT JOIN {$itemmeta_tbl} mi ON mi.order_item_id = oi.order_item_id AND mi.meta_key = 'instance_id'
             LEFT JOIN {$zonemeth_tbl} zm ON zm.instance_id = CAST(mi.meta_value AS UNSIGNED)
             LEFT JOIN {$zones_tbl}    z  ON z.zone_id = zm.zone_id
             WHERE o.type = 'shop_order'
               AND o.status IN ('wc-processing','wc-completed','wc-on-hold','processing','completed','on-hold')
               AND od.shipping_total_amount > 0
               AND oi.order_item_name <> ''
               AND o.date_created_gmt >= DATE_SUB(NOW(), INTERVAL 36 MONTH)
             GROUP BY method_lc, zone_lc",
            ARRAY_A
        );
        $cache = array();
        if (is_array($rows)) {
            foreach ($rows as $r) {
                $key = $r['method_lc'] . '|' . $r['zone_lc'];
                $cache[$key] = (float) $r['avg_cost'];
            }
        }

        
        $rows_method = $wpdb->get_results(
            "SELECT
                LOWER(TRIM(oi.order_item_name)) AS method_lc,
                AVG(od.shipping_total_amount) AS avg_cost
             FROM {$orders_tbl} o
             INNER JOIN {$opdata_tbl} od ON od.order_id = o.id
             INNER JOIN {$items_tbl}  oi ON oi.order_id = o.id AND oi.order_item_type = 'shipping'
             WHERE o.type = 'shop_order'
               AND o.status IN ('wc-processing','wc-completed','wc-on-hold','processing','completed','on-hold')
               AND od.shipping_total_amount > 0
               AND oi.order_item_name <> ''
               AND o.date_created_gmt >= DATE_SUB(NOW(), INTERVAL 36 MONTH)
             GROUP BY method_lc",
            ARRAY_A
        );
        if (is_array($rows_method)) {
            foreach ($rows_method as $r) {
                $cache['__method__' . $r['method_lc']] = (float) $r['avg_cost'];
            }
        }

        return $cache;
    }

    
    public static function on_activation() {
        wp_clear_scheduled_hook(self::HOOK);
    }
}
