<?php

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

class AIEO_Fulfillment_Migrator {

    const STEP_SETTINGS = 'settings';
    const STEP_TRACKING = 'tracking';
    const STEP_WPDF     = 'wpdf';
    const STEP_ACS      = 'acs';

    public static function steps() {
        return array(
            self::STEP_SETTINGS => __('1. Shop info & template (one-shot)', 'ai-eshop-optimizer'),
            self::STEP_TRACKING => __('2. WC Shipment Tracking → vouchers', 'ai-eshop-optimizer'),
            self::STEP_WPDF     => __('3. WPO PDF Invoices → documents',    'ai-eshop-optimizer'),
            self::STEP_ACS      => __('4. ACS legacy plugin → vouchers',    'ai-eshop-optimizer'),
        );
    }

    
    public static function audit() {
        global $wpdb;
        $om = $wpdb->prefix . 'wc_orders_meta';
        $hpos = (bool) $wpdb->get_var("SHOW TABLES LIKE '{$om}'");
        $tbl = $hpos ? $om : $wpdb->postmeta;
        $key_col = $hpos ? 'order_id' : 'post_id';

        return array(
            'hpos'              => $hpos,
            'tracking_orders'   => (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT {$key_col}) FROM {$tbl} WHERE meta_key=%s AND meta_value<>%s", '_wc_shipment_tracking_items', 'a:0:{}')),
            'wpdf_invoice'      => (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT {$key_col}) FROM {$tbl} WHERE meta_key=%s AND meta_value<>''", '_wcpdf_invoice_number')),
            'wpdf_packing'      => (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT {$key_col}) FROM {$tbl} WHERE meta_key=%s AND meta_value<>''", '_wcpdf_packing-slip_number')),
            'wpdf_proforma'     => (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT {$key_col}) FROM {$tbl} WHERE meta_key=%s AND meta_value<>''", '_wcpdf_proforma_number')),
            'wpdf_credit_note'  => (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT {$key_col}) FROM {$tbl} WHERE meta_key=%s AND meta_value<>''", '_wcpdf_credit-note_number')),
            'acs_legacy'        => (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT {$key_col}) FROM {$tbl} WHERE meta_key=%s AND meta_value=%s", 'wpslash_voucher_courier', 'acs')),
            'destination_vouchers'  => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}aieo_dmm_fulfillment_vouchers"),
            'destination_documents' => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}aieo_dmm_fulfillment_documents"),
        );
    }

    
    public static function run_batch($step, $batch_size = 500) {
        $batch_size = max(50, min(5000, (int) $batch_size));

        switch ($step) {
            case self::STEP_SETTINGS:
                self::migrate_settings();
                $progress = self::progress();
                $progress[self::STEP_SETTINGS] = array('done' => true);
                self::set_progress($progress);
                return array('done' => true, 'cursor' => 0, 'created' => 0, 'skipped' => 0, 'total' => 0, 'processed' => 0);

            case self::STEP_TRACKING:
                return self::run_tracking_batch($batch_size);

            case self::STEP_WPDF:
                return self::run_wpdf_batch($batch_size);

            case self::STEP_ACS:
                return self::run_acs_batch($batch_size);
        }
        return array('done' => true, 'cursor' => 0, 'created' => 0, 'skipped' => 0, 'total' => 0, 'processed' => 0);
    }

    
    public static function reset_progress($step = null) {
        if ($step === null) {
            self::set_progress(array());
            return;
        }
        $p = self::progress();
        unset($p[$step]);
        self::set_progress($p);
    }

    public static function progress() {
        $p = AIEO_Fulfillment_Settings::get('migration_progress', array());
        return is_array($p) ? $p : array();
    }

    private static function set_progress(array $p) {
        AIEO_Fulfillment_Settings::set('migration_progress', $p);
    }

    

    public static function migrate_settings() {
        $wpo = get_option('wpo_wcpdf_settings_general', array());
        if (!is_array($wpo) || empty($wpo)) { return array('skipped' => 'wpo_general_missing'); }

        $resolve = static function ($v) {
            return is_array($v) && isset($v['default']) ? (string) $v['default'] : (is_scalar($v) ? (string) $v : '');
        };
        $existing = (array) AIEO_Fulfillment_Settings::get('general', array());
        $patch = array(
            'shop_name'      => $resolve($wpo['shop_name']            ?? ''),
            'shop_address_1' => $resolve($wpo['shop_address_line_1']  ?? ''),
            'shop_address_2' => $resolve($wpo['shop_address_line_2']  ?? ''),
            'shop_city'      => $resolve($wpo['shop_address_city']    ?? ''),
            'shop_postcode'  => $resolve($wpo['shop_address_postcode'] ?? ''),
            'shop_country'   => $resolve($wpo['shop_address_country'] ?? ''),
            'shop_phone'     => $resolve($wpo['shop_phone_number']    ?? ''),
            'shop_email'     => $resolve($wpo['shop_email_address']   ?? ''),
            'vat_number'     => $resolve($wpo['vat_number']           ?? ''),
            'language'       => (string) ($wpo['document_language']   ?? 'el'),
            'paper_size'     => (string) ($wpo['paper_size']          ?? 'a4'),
        );
        $merged = $existing;
        foreach ($patch as $k => $v) {
            if (($merged[$k] ?? '') === '' && $v !== '') { $merged[$k] = $v; }
        }
        if (!empty($existing['signing_secret'])) { $merged['signing_secret'] = $existing['signing_secret']; }
        AIEO_Fulfillment_Settings::set('general', $merged);

        
        $template_path = (string) ($wpo['template_path'] ?? '');
        if ($template_path !== '') {
            $template_id  = preg_replace('#^.*/#', '', $template_path);
            $existing_tpl = (array) AIEO_Fulfillment_Settings::get('templates', array());
            if (empty($existing_tpl['active'])) {
                AIEO_Fulfillment_Settings::set('templates', array_merge($existing_tpl, array('active' => $template_id)));
            }
        }
        return array('ok' => true);
    }

    

    private static function run_tracking_batch($batch_size) {
        global $wpdb;
        $progress = self::progress();
        $cur = $progress[self::STEP_TRACKING] ?? array('cursor' => 0, 'created' => 0, 'skipped' => 0, 'total' => 0);

        if (empty($cur['total'])) {
            $cur['total'] = self::count_meta_rows('_wc_shipment_tracking_items');
        }

        $rows = self::pull_meta_rows_after('_wc_shipment_tracking_items', (int) $cur['cursor'], $batch_size);
        if (empty($rows)) {
            $cur['done'] = true;
            $progress[self::STEP_TRACKING] = $cur;
            self::set_progress($progress);
            return array('done' => true, 'cursor' => $cur['cursor'], 'created' => $cur['created'], 'skipped' => $cur['skipped'], 'total' => $cur['total'], 'processed' => $cur['cursor']);
        }

        $vt = $wpdb->prefix . 'aieo_dmm_fulfillment_vouchers';
        foreach ($rows as $row) {
            $cur['cursor'] = (int) $row['meta_id'];
            $items = is_serialized($row['meta_value']) ? maybe_unserialize($row['meta_value']) : null;
            if (!is_array($items)) { $cur['skipped']++; continue; }

            foreach ($items as $item) {
                if (!is_array($item)) { continue; }
                $tracking_no = (string) ($item['tracking_number'] ?? '');
                if ($tracking_no === '') { continue; }

                $provider_predef = trim((string) ($item['tracking_provider']        ?? ''));
                $provider_custom = trim((string) ($item['custom_tracking_provider'] ?? ''));
                $name = $provider_predef !== '' ? $provider_predef : $provider_custom;
                $carrier = self::map_provider_to_carrier($name);

                $existing_id = (int) $wpdb->get_var($wpdb->prepare(
                    "SELECT id FROM {$vt} WHERE carrier=%s AND voucher_no=%s LIMIT 1",
                    $carrier, $tracking_no
                ));
                if ($existing_id) { $cur['skipped']++; continue; }

                $date_shipped = (int) ($item['date_shipped'] ?? 0);
                $created_at   = $date_shipped > 0 ? gmdate('Y-m-d H:i:s', $date_shipped) : current_time('mysql', true);

                $r = $wpdb->insert($vt, array(
                    'order_id'        => (int) $row['order_id'],
                    'carrier'         => $carrier,
                    'voucher_no'      => $tracking_no,
                    'carrier_account' => $carrier === 'manual' ? $name : '',
                    'status'          => 'in_transit',
                    'pieces'          => 1,
                    'services_csv'    => '',
                    'created_at'      => $created_at,
                    'updated_at'      => current_time('mysql', true),
                ));
                if ($r) { $cur['created']++; } else { $cur['skipped']++; }
            }
        }

        $progress[self::STEP_TRACKING] = $cur;
        self::set_progress($progress);

        return array(
            'done'      => count($rows) < $batch_size,
            'cursor'    => $cur['cursor'],
            'created'   => $cur['created'],
            'skipped'   => $cur['skipped'],
            'total'     => $cur['total'],
            'processed' => $cur['created'] + $cur['skipped'],
        );
    }

    

    private static function run_wpdf_batch($batch_size) {
        global $wpdb;
        $progress = self::progress();
        $cur = $progress[self::STEP_WPDF] ?? array('cursor' => 0, 'created' => 0, 'skipped' => 0, 'total' => 0, 'phase' => 0);

        
        $types = array(
            array('type' => 'invoice',      'meta_no' => '_wcpdf_invoice_number',      'meta_dt' => '_wcpdf_invoice_date'),
            array('type' => 'packing-slip', 'meta_no' => '_wcpdf_packing-slip_number', 'meta_dt' => '_wcpdf_packing-slip_date'),
            array('type' => 'proforma',     'meta_no' => '_wcpdf_proforma_number',     'meta_dt' => '_wcpdf_proforma_date'),
            array('type' => 'credit-note',  'meta_no' => '_wcpdf_credit-note_number',  'meta_dt' => '_wcpdf_credit-note_date'),
        );

        if (empty($cur['total'])) {
            $sum = 0;
            foreach ($types as $t) { $sum += self::count_meta_rows($t['meta_no']); }
            $cur['total'] = $sum;
        }

        $phase = (int) $cur['phase'];
        if ($phase >= count($types)) {
            $cur['done'] = true;
            $progress[self::STEP_WPDF] = $cur;
            self::set_progress($progress);
            return array('done' => true, 'cursor' => $cur['cursor'], 'created' => $cur['created'], 'skipped' => $cur['skipped'], 'total' => $cur['total'], 'processed' => $cur['created'] + $cur['skipped']);
        }
        $info = $types[$phase];
        $rows = self::pull_meta_rows_after($info['meta_no'], (int) $cur['cursor'], $batch_size);
        if (empty($rows)) {
            
            $cur['phase']  = $phase + 1;
            $cur['cursor'] = 0;
            $progress[self::STEP_WPDF] = $cur;
            self::set_progress($progress);
            return array('done' => false, 'cursor' => 0, 'created' => $cur['created'], 'skipped' => $cur['skipped'], 'total' => $cur['total'], 'processed' => $cur['created'] + $cur['skipped']);
        }

        
        $order_ids = array_unique(array_map('intval', wp_list_pluck($rows, 'order_id')));
        $date_map = self::pull_meta_map_for($info['meta_dt'], $order_ids);

        $dt = $wpdb->prefix . 'aieo_dmm_fulfillment_documents';
        foreach ($rows as $row) {
            $cur['cursor'] = (int) $row['meta_id'];
            $order_id = (int) $row['order_id'];
            $number   = trim((string) $row['meta_value']);
            if ($order_id <= 0 || $number === '') { $cur['skipped']++; continue; }

            $existing_id = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$dt} WHERE type=%s AND number=%s LIMIT 1",
                $info['type'], $number
            ));
            if ($existing_id) { $cur['skipped']++; continue; }

            $issued_str = isset($date_map[$order_id]) ? (string) $date_map[$order_id] : '';
            $issued_dt  = $issued_str !== '' ? gmdate('Y-m-d H:i:s', (int) strtotime($issued_str . ' UTC')) : current_time('mysql', true);

            $r = $wpdb->insert($dt, array(
                'order_id'  => $order_id,
                'type'      => $info['type'],
                'number'    => $number,
                'issued_at' => $issued_dt,
                'language'  => 'el',
                'created_at'=> current_time('mysql', true),
                'updated_at'=> current_time('mysql', true),
            ));
            if ($r) { $cur['created']++; } else { $cur['skipped']++; }
        }

        $progress[self::STEP_WPDF] = $cur;
        self::set_progress($progress);

        return array(
            'done'      => false,
            'cursor'    => $cur['cursor'],
            'created'   => $cur['created'],
            'skipped'   => $cur['skipped'],
            'total'     => $cur['total'],
            'processed' => $cur['created'] + $cur['skipped'],
            'phase'     => $cur['phase'],
        );
    }

    

    private static function run_acs_batch($batch_size) {
        global $wpdb;
        $progress = self::progress();
        $cur = $progress[self::STEP_ACS] ?? array('cursor' => 0, 'created' => 0, 'skipped' => 0, 'total' => 0);

        if (empty($cur['total'])) {
            $cur['total'] = self::count_meta_rows_eq('wpslash_voucher_courier', 'acs');
        }

        $rows = self::pull_meta_rows_after_eq('wpslash_voucher_courier', 'acs', (int) $cur['cursor'], $batch_size);
        if (empty($rows)) {
            $cur['done'] = true;
            $progress[self::STEP_ACS] = $cur;
            self::set_progress($progress);
            return array('done' => true, 'cursor' => $cur['cursor'], 'created' => $cur['created'], 'skipped' => $cur['skipped'], 'total' => $cur['total'], 'processed' => $cur['created'] + $cur['skipped']);
        }

        $order_ids = array_unique(array_map('intval', wp_list_pluck($rows, 'order_id')));
        $tracking_map = self::pull_meta_map_for('wpslash_voucher_courier_tracking', $order_ids);
        $date_map     = self::pull_meta_map_for('wpslash_voucher_courier_date',     $order_ids);

        $vt = $wpdb->prefix . 'aieo_dmm_fulfillment_vouchers';
        foreach ($rows as $row) {
            $cur['cursor'] = (int) $row['meta_id'];
            $order_id = (int) $row['order_id'];
            $voucherno = (string) ($tracking_map[$order_id] ?? '');
            if ($order_id <= 0 || $voucherno === '') { $cur['skipped']++; continue; }

            $existing_id = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$vt} WHERE carrier=%s AND voucher_no=%s LIMIT 1",
                'acs', $voucherno
            ));
            if ($existing_id) { $cur['skipped']++; continue; }

            $created_at = !empty($date_map[$order_id])
                ? gmdate('Y-m-d H:i:s', (int) strtotime($date_map[$order_id] . ' UTC'))
                : current_time('mysql', true);

            $r = $wpdb->insert($vt, array(
                'order_id'   => $order_id,
                'carrier'    => 'acs',
                'voucher_no' => $voucherno,
                'status'     => 'in_transit',
                'pieces'     => 1,
                'services_csv' => '',
                'created_at' => $created_at,
                'updated_at' => current_time('mysql', true),
            ));
            if ($r) { $cur['created']++; } else { $cur['skipped']++; }
        }

        $progress[self::STEP_ACS] = $cur;
        self::set_progress($progress);

        return array(
            'done'      => count($rows) < $batch_size,
            'cursor'    => $cur['cursor'],
            'created'   => $cur['created'],
            'skipped'   => $cur['skipped'],
            'total'     => $cur['total'],
            'processed' => $cur['created'] + $cur['skipped'],
        );
    }

    

    private static function meta_source() {
        global $wpdb;
        $om = $wpdb->prefix . 'wc_orders_meta';
        $hpos = (bool) $wpdb->get_var("SHOW TABLES LIKE '{$om}'");
        return $hpos
            ? array('table' => $om, 'meta_id_col' => 'id', 'order_col' => 'order_id')
            : array('table' => $wpdb->postmeta, 'meta_id_col' => 'meta_id', 'order_col' => 'post_id');
    }

    private static function count_meta_rows($meta_key) {
        global $wpdb;
        $s = self::meta_source();
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$s['table']} WHERE meta_key=%s AND meta_value<>'' AND meta_value<>'a:0:{}'",
            $meta_key
        ));
    }

    private static function count_meta_rows_eq($meta_key, $meta_value) {
        global $wpdb;
        $s = self::meta_source();
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$s['table']} WHERE meta_key=%s AND meta_value=%s",
            $meta_key, $meta_value
        ));
    }

    private static function pull_meta_rows_after($meta_key, $cursor, $limit) {
        global $wpdb;
        $s = self::meta_source();
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT {$s['meta_id_col']} AS meta_id, {$s['order_col']} AS order_id, meta_value
             FROM {$s['table']}
             WHERE meta_key=%s AND meta_value<>'' AND meta_value<>'a:0:{}'
               AND {$s['meta_id_col']} > %d
             ORDER BY {$s['meta_id_col']} ASC LIMIT %d",
            $meta_key, (int) $cursor, max(1, (int) $limit)
        ), ARRAY_A);
        return is_array($rows) ? $rows : array();
    }

    private static function pull_meta_rows_after_eq($meta_key, $meta_value, $cursor, $limit) {
        global $wpdb;
        $s = self::meta_source();
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT {$s['meta_id_col']} AS meta_id, {$s['order_col']} AS order_id, meta_value
             FROM {$s['table']}
             WHERE meta_key=%s AND meta_value=%s AND {$s['meta_id_col']} > %d
             ORDER BY {$s['meta_id_col']} ASC LIMIT %d",
            $meta_key, $meta_value, (int) $cursor, max(1, (int) $limit)
        ), ARRAY_A);
        return is_array($rows) ? $rows : array();
    }

    private static function pull_meta_map_for($meta_key, array $order_ids) {
        global $wpdb;
        $order_ids = array_filter(array_map('intval', $order_ids));
        if (empty($order_ids)) { return array(); }
        $s = self::meta_source();
        $placeholders = implode(',', array_fill(0, count($order_ids), '%d'));
        $args = array_merge(array($meta_key), $order_ids);
        $sql = "SELECT {$s['order_col']} AS order_id, meta_value
                FROM {$s['table']}
                WHERE meta_key=%s AND {$s['order_col']} IN ({$placeholders})";
        $rows = $wpdb->get_results($wpdb->prepare($sql, $args), ARRAY_A);
        $out = array();
        foreach ((array) $rows as $r) { $out[(int) $r['order_id']] = (string) $r['meta_value']; }
        return $out;
    }

    private static function map_provider_to_carrier($name) {
        $u = mb_strtoupper(trim((string) $name));
        if ($u === '') { return 'manual'; }
        if (mb_strpos($u, 'ACS') !== false)                                          { return 'acs'; }
        if (mb_strpos($u, 'ΓΕΝΙΚ') !== false || mb_strpos($u, 'TAXYDROMIKI') !== false) { return 'geniki'; }
        if (mb_strpos($u, 'BOX NOW') !== false || mb_strpos($u, 'BOXNOW') !== false) { return 'boxnow'; }
        if (mb_strpos($u, 'ELTA') !== false || mb_strpos($u, 'ΕΛΤΑ') !== false)      { return 'elta'; }
        if (mb_strpos($u, 'SPEEDEX') !== false)                                      { return 'speedex'; }
        return 'manual';
    }
}
