<?php

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

class AIEO_DMM_Shipment_Cost_Reports extends AIEO_DMM_Module_Base {

    public function get_id()       { return 'shipment_cost_reports'; }
    public function get_label()    { return __('Shipment cost reports', 'ai-eshop-optimizer'); }
    public function get_priority() { return 15; } 
    public function get_group()    { return 'tools'; }

    public function is_pro_locked() { return true; }
    public function get_pro_promo() {
        return array(
            'title'       => __('Shipment Cost Reports', 'ai-eshop-optimizer'),
            'description' => __('Per-order shipping margin analysis. Catches under-priced shipping zones, sub-par carrier mixes, and lost-margin SKUs you did not know you were absorbing — the single biggest hidden cost on most WooCommerce stores.', 'ai-eshop-optimizer'),
            'bullets'     => array(
                __('Per-order P&L: charged vs actual carrier cost, broken down by zone, weight, and method.', 'ai-eshop-optimizer'),
                __('Carrier comparison — see which courier is winning on price for which zone.', 'ai-eshop-optimizer'),
                __('CSV export ready for the accountant.', 'ai-eshop-optimizer'),
            ),
        );
    }

    public function register_hooks() {
        add_action('wp_ajax_aieo_dmm_shipping_pnl',         array($this, 'ajax_shipping_pnl'));
        add_action('wp_ajax_aieo_dmm_shipping_pnl_export',  array($this, 'ajax_shipping_pnl_export'));
    }

    public function render_subtab() {
        $nonce = wp_create_nonce(AIEO_DMM::NONCE_ACTION);
        $ajax  = admin_url('admin-ajax.php');

        echo '<div class="aieo-dmm-section">';
        echo '<h2>' . esc_html__('Shipment cost reports', 'ai-eshop-optimizer') . '</h2>';
        echo '<p class="description">' . esc_html__('All shipping-cost analytics in one place: scan past orders to recover what free-shipping promos actually cost you, and see the per-carrier × per-zone P&L vs. what customers paid.', 'ai-eshop-optimizer') . '</p>';

        $this->render_retrospective_scan_card($nonce, $ajax);
        $this->render_shipping_pnl_card($nonce, $ajax);

        echo '</div>';
    }

    
    private function render_retrospective_scan_card($nonce, $ajax) {
        echo '<div class="aieo-dmm-card" style="margin-top:18px;">';
        echo '<h3 style="margin:0 0 4px;">' . esc_html__('Free-shipping cost — retrospective analysis', 'ai-eshop-optimizer') . '</h3>';
        echo '<p class="description" style="margin:0 0 10px;font-size:12px;">'
            . esc_html__('Scans past orders where shipping was free, re-runs WooCommerce shipping calculation (using each order\'s shipping postcode + cart weight + line subtotal — same inputs as table-rate shipping at checkout) and records the would-have-been carrier cost. Lets you compute the actual P&L of historical free-shipping campaigns. The same job runs nightly via cron; this button is for ad-hoc analysis.', 'ai-eshop-optimizer')
            . '</p>';
        echo '<p style="margin:0 0 10px;">';
        echo '<label style="margin-right:14px;">' . esc_html__('Look-back window (days):', 'ai-eshop-optimizer') . ' ';
        echo '<input type="number" id="aieo-fs-cost-days" value="90" min="1" max="9999" step="1" style="width:80px;"></label>';
        echo '<button type="button" class="button button-primary" id="aieo-fs-cost-run">'
            . esc_html__('Run now', 'ai-eshop-optimizer') . '</button>';
        echo '</p>';

        echo '<div id="aieo-fs-cost-progress-wrap" style="display:none;margin:8px 0;">';
        echo '<div style="height:14px;background:#e5e7eb;border-radius:4px;overflow:hidden;">';
        echo '<div id="aieo-fs-cost-progress-bar" style="height:100%;width:0%;background:#15803d;transition:width 0.3s ease;"></div>';
        echo '</div>';
        echo '<div id="aieo-fs-cost-progress-label" style="font-size:12px;color:#374151;margin-top:6px;">' . esc_html__('Idle.', 'ai-eshop-optimizer') . '</div>';
        echo '</div>';

        echo '<p id="aieo-fs-cost-status" style="margin:6px 0 0;font-size:12px;color:#646970;"></p>';
        echo '<p class="description" style="margin:8px 0 0;font-size:11px;color:#9ca3af;">'
            . esc_html__('You can close this page once the run starts — the daily cron will pick up where you stopped at 03:30 the next morning.', 'ai-eshop-optimizer')
            . '</p>';
        echo '</div>';

        echo '<script>(function(){
            var btn      = document.getElementById("aieo-fs-cost-run");
            var status   = document.getElementById("aieo-fs-cost-status");
            var days     = document.getElementById("aieo-fs-cost-days");
            var wrap     = document.getElementById("aieo-fs-cost-progress-wrap");
            var bar      = document.getElementById("aieo-fs-cost-progress-bar");
            var label    = document.getElementById("aieo-fs-cost-progress-label");
            if (!btn) return;

            var ajax = "' . esc_js($ajax) . '";
            var nonce = "' . esc_js($nonce) . '";

            function step(phase) {
                var fd = new FormData();
                fd.set("action", "aieo_dmm_fs_cost_step");
                fd.set("nonce", nonce);
                fd.set("phase", phase);
                fd.set("days",  String(parseInt(days.value, 10) || 90));
                return fetch(ajax, {method:"POST", credentials:"same-origin", body:fd}).then(function(r){ return r.json(); });
            }

            function setProgress(pct, text) {
                if (pct < 0) pct = 0; if (pct > 100) pct = 100;
                bar.style.width = pct.toFixed(1) + "%";
                label.textContent = text;
            }

            btn.addEventListener("click", function(){
                btn.disabled = true;
                wrap.style.display = "block";
                setProgress(0, "' . esc_js(__('Scanning order index…', 'ai-eshop-optimizer')) . '");
                status.style.color = "#646970";
                status.textContent = "";
                step("init").then(function(j){
                    if (!j || !j.success) {
                        status.style.color = "#b91c1c";
                        status.textContent = (j && j.data && j.data.message) ? j.data.message : "' . esc_js(__('Init failed.', 'ai-eshop-optimizer')) . '";
                        btn.disabled = false; return;
                    }
                    var totalScan    = parseInt(j.data.total_to_scan, 10) || 0;
                    var totalResolve = parseInt(j.data.remaining,     10) || 0;
                    var phase = (j.data.phase === "resolve" || totalScan === 0) ? "resolve" : "scan";
                    runLoop(phase, {totalScan: totalScan, scanned: 0, totalResolve: totalResolve, resolvedSoFar: 0});
                }).catch(function(){
                    status.style.color = "#b91c1c";
                    status.textContent = "' . esc_js(__('Network error during init.', 'ai-eshop-optimizer')) . '";
                    btn.disabled = false;
                });
            });

            function runLoop(phase, st) {
                step(phase).then(function(j){
                    if (!j || !j.success) {
                        status.style.color = "#b91c1c";
                        status.textContent = (j && j.data && j.data.message) ? j.data.message : "' . esc_js(__('Step failed.', 'ai-eshop-optimizer')) . '";
                        btn.disabled = false; return;
                    }
                    var d = j.data;
                    if (d.phase === "scan") {
                        var inserted = parseInt(d.inserted, 10) || 0;
                        st.scanned += inserted;
                        var remaining = parseInt(d.remaining, 10) || 0;
                        var totalNow = st.scanned + remaining;
                        if (totalNow > st.totalScan) { st.totalScan = totalNow; }
                        var pct = st.totalScan > 0 ? (st.scanned / st.totalScan) * 50 : 0;
                        setProgress(pct, "' . esc_js(__('Scanning past orders:', 'ai-eshop-optimizer')) . ' " + st.scanned + " / " + st.totalScan);
                        runLoop("scan", st);
                    } else if (d.phase === "resolve") {
                        var resolved = parseInt(d.resolved, 10) || 0;
                        st.resolvedSoFar += resolved;
                        var pendingNow  = parseInt(d.remaining, 10) || 0;
                        var totalRes = st.resolvedSoFar + pendingNow;
                        if (totalRes > st.totalResolve) { st.totalResolve = totalRes; }
                        var pct = 50 + (st.totalResolve > 0 ? (st.resolvedSoFar / st.totalResolve) * 50 : 50);
                        setProgress(pct, "' . esc_js(__('Resolving shipping costs:', 'ai-eshop-optimizer')) . ' " + st.resolvedSoFar + " / " + st.totalResolve);
                        runLoop("resolve", st);
                    } else {
                        setProgress(100, "' . esc_js(__('Done.', 'ai-eshop-optimizer')) . '");
                        status.style.color = "#15803d";
                        status.textContent = "' . esc_js(__('Retrospective analysis complete. Scanned', 'ai-eshop-optimizer')) . ' " + st.scanned + " ' . esc_js(__('orders, resolved', 'ai-eshop-optimizer')) . ' " + st.resolvedSoFar + " ' . esc_js(__('attribution rows.', 'ai-eshop-optimizer')) . '";
                        btn.disabled = false;
                    }
                }).catch(function(){
                    status.style.color = "#b91c1c";
                    status.textContent = "' . esc_js(__('Network error mid-run — click again to resume.', 'ai-eshop-optimizer')) . '";
                    btn.disabled = false;
                });
            }
        })();</script>';
    }

    
    private function render_shipping_pnl_card($nonce, $ajax) {
        echo '<div class="aieo-dmm-card" style="margin-top:18px;">';
        echo '<h3 style="margin:0 0 4px;">' . esc_html__('Shipping P&L by carrier × zone — monthly', 'ai-eshop-optimizer') . '</h3>';
        echo '<p class="description" style="margin:0 0 10px;font-size:12px;">'
            . esc_html__('What each carrier (DHL, Geniki, ELTA, …) actually invoiced you per month, broken down by shipping zone (Greek mainland / islands / EU / world). "Revenue collected" = shipping fees customers paid you on paid orders. "Free shipping cost" = the carrier rate you absorbed for free-shipping orders (computed by the resolver above). "Total carrier cost" = both summed — that\'s your real shipping bill. The "Free %" column tells you what fraction of each carrier\'s spend was eaten by free-shipping promos.', 'ai-eshop-optimizer')
            . '</p>';

        echo '<p style="margin:0 0 10px;display:flex;flex-wrap:wrap;gap:14px;align-items:end;">';

        echo '<label>' . esc_html__('Period:', 'ai-eshop-optimizer') . ' ';
        echo '<select id="aieo-sp-months">';
        echo '<option value="current">' . esc_html__('Current month', 'ai-eshop-optimizer') . '</option>';
        echo '<option value="last">' . esc_html__('Last month', 'ai-eshop-optimizer') . '</option>';
        foreach (array(3, 6, 12, 24, 36) as $m) {
            $sel = $m === 12 ? ' selected' : '';
            
            $label = sprintf(_n('Last %d month', 'Last %d months', $m, 'ai-eshop-optimizer'), $m);
            echo '<option value="' . $m . '"' . $sel . '>' . esc_html($label) . '</option>'; 
        }
        echo '</select></label>';

        echo '<label>' . esc_html__('Group by:', 'ai-eshop-optimizer') . ' ';
        echo '<select id="aieo-sp-group">';
        echo '<option value="month_method_zone">' . esc_html__('Month × Method × Zone', 'ai-eshop-optimizer') . '</option>';
        echo '<option value="month_method">' . esc_html__('Month × Method', 'ai-eshop-optimizer') . '</option>';
        echo '<option value="month_zone">' . esc_html__('Month × Zone', 'ai-eshop-optimizer') . '</option>';
        echo '<option value="method_zone">' . esc_html__('Method × Zone (all months)', 'ai-eshop-optimizer') . '</option>';
        echo '<option value="month">' . esc_html__('Month total', 'ai-eshop-optimizer') . '</option>';
        echo '<option value="method">' . esc_html__('Method total', 'ai-eshop-optimizer') . '</option>';
        echo '<option value="zone">' . esc_html__('Zone total', 'ai-eshop-optimizer') . '</option>';
        echo '</select></label>';

        echo '<label>' . esc_html__('Method:', 'ai-eshop-optimizer') . ' ';
        echo '<select id="aieo-sp-method"><option value="">' . esc_html__('All', 'ai-eshop-optimizer') . '</option></select></label>';

        echo '<label>' . esc_html__('Zone:', 'ai-eshop-optimizer') . ' ';
        echo '<select id="aieo-sp-zone"><option value="">' . esc_html__('All', 'ai-eshop-optimizer') . '</option></select></label>';

        echo '<button type="button" class="button" id="aieo-sp-refresh">'
            . esc_html__('Refresh', 'ai-eshop-optimizer') . '</button>';
        echo '<button type="button" class="button button-primary" id="aieo-sp-export">'
            . esc_html__('Export XLSX', 'ai-eshop-optimizer') . '</button>';

        echo '</p>';

        echo '<div id="aieo-sp-summary" style="margin:8px 0;font-size:12px;color:#374151;"></div>';
        echo '<div id="aieo-sp-table-wrap" style="margin-top:8px;"></div>';

        echo '<script>(function(){
            var ajax  = "' . esc_js($ajax) . '";
            var nonce = "' . esc_js($nonce) . '";

            var monthsSel  = document.getElementById("aieo-sp-months");
            var groupSel   = document.getElementById("aieo-sp-group");
            var methodSel  = document.getElementById("aieo-sp-method");
            var zoneSel    = document.getElementById("aieo-sp-zone");
            var refreshBtn = document.getElementById("aieo-sp-refresh");
            var exportBtn  = document.getElementById("aieo-sp-export");
            var summary    = document.getElementById("aieo-sp-summary");
            var tableWrap  = document.getElementById("aieo-sp-table-wrap");

            function escapeHtml(s){var d=document.createElement("div");d.appendChild(document.createTextNode(String(s==null?"":s)));return d.innerHTML;}
            function fmtEur(n){var v=parseFloat(n);if(isNaN(v))return"—";return v.toLocaleString("el-GR",{minimumFractionDigits:2,maximumFractionDigits:2})+" €";}
            function fmtInt(n){var v=parseInt(n,10);if(isNaN(v))return"0";return v.toLocaleString("el-GR");}
            function fmtPct(n){var v=parseFloat(n);if(isNaN(v))return"—";return (v*100).toFixed(1)+" %";}

            function buildParams(){
                var p = new URLSearchParams();
                p.set("nonce", nonce);
                p.set("months", monthsSel.value);
                p.set("group_by", groupSel.value);
                p.set("method", methodSel.value);
                p.set("zone", zoneSel.value);
                return p;
            }

            function fillSelect(el, values){
                if (!el) return;
                var current = el.value;
                var html = "<option value=\"\">' . esc_js(__('All', 'ai-eshop-optimizer')) . '</option>";
                values.forEach(function(c){
                    if (!c) return;
                    var sel = (c === current) ? " selected" : "";
                    html += "<option value=\"" + escapeHtml(c) + "\"" + sel + ">" + escapeHtml(c) + "</option>";
                });
                el.innerHTML = html;
            }

            function fetchData(){
                refreshBtn.disabled = true;
                tableWrap.innerHTML = "<p style=\"color:#646970;font-size:12px;\">' . esc_js(__('Loading…', 'ai-eshop-optimizer')) . '</p>";
                var fd = new FormData();
                fd.set("action", "aieo_dmm_shipping_pnl");
                buildParams().forEach(function(v,k){ fd.set(k, v); });
                fetch(ajax, {method:"POST", credentials:"same-origin", body:fd})
                    .then(function(r){ return r.json(); })
                    .then(function(j){
                        refreshBtn.disabled = false;
                        if (!j || !j.success) {
                            tableWrap.innerHTML = "<p style=\"color:#b91c1c;\">" + escapeHtml((j && j.data && j.data.message) || "Error") + "</p>";
                            return;
                        }
                        renderTable(j.data);
                        fillSelect(methodSel, j.data.methods || []);
                        fillSelect(zoneSel,   j.data.zones   || []);
                    })
                    .catch(function(){
                        refreshBtn.disabled = false;
                        tableWrap.innerHTML = "<p style=\"color:#b91c1c;\">' . esc_js(__('Network error.', 'ai-eshop-optimizer')) . '</p>";
                    });
            }

            function renderTable(d){
                var rows = d.rows || [];
                var totals = d.totals || {paid_orders:0, free_orders:0, total_orders:0, shipping_revenue:0, free_cost:0, carrier_invoice:0};
                var pctFree = totals.carrier_invoice > 0 ? (totals.free_cost / totals.carrier_invoice) : 0;

                summary.innerHTML =
                    "<strong>' . esc_js(__('Totals:', 'ai-eshop-optimizer')) . '</strong> " +
                    "' . esc_js(__('Orders', 'ai-eshop-optimizer')) . ' " + fmtInt(totals.total_orders) +
                    " (' . esc_js(__('free', 'ai-eshop-optimizer')) . ' " + fmtInt(totals.free_orders) + ")" +
                    " &nbsp;|&nbsp; ' . esc_js(__('Customer revenue', 'ai-eshop-optimizer')) . ' " + fmtEur(totals.shipping_revenue) +
                    " &nbsp;|&nbsp; ' . esc_js(__('Free shipping cost', 'ai-eshop-optimizer')) . ' " + fmtEur(totals.free_cost) +
                    " &nbsp;|&nbsp; <strong>' . esc_js(__('Total carrier invoice', 'ai-eshop-optimizer')) . ' " + fmtEur(totals.carrier_invoice) + "</strong>" +
                    " (' . esc_js(__('free %', 'ai-eshop-optimizer')) . ' " + fmtPct(pctFree) + ")";

                if (!rows.length) {
                    tableWrap.innerHTML = "<p style=\"color:#646970;font-size:12px;\">' . esc_js(__('No shipping data for this window. If you just deployed, run the retrospective scan above so historical free-shipping rows have method + zone populated.', 'ai-eshop-optimizer')) . '</p>";
                    return;
                }

                var cols = d.columns || [];
                // Cache the unsorted rows so the third click on a header
                // (asc → desc → unsorted) can restore the server-side
                // ordering without a fetch round-trip.
                var originalRows = rows.slice();
                var sortState = { key: null, dir: 0 };  // dir: 1=asc, -1=desc

                function isNumericFmt(fmt) {
                    return fmt === "currency" || fmt === "currency_strong" || fmt === "integer" || fmt === "percent";
                }
                function sortableHeaderHtml(col, active) {
                    var icon = "⇅", opacity = "0.4";
                    if (active.key === col.key && active.dir !== 0) {
                        icon = active.dir === 1 ? "▲" : "▼";
                        opacity = "1";
                    }
                    return "<th data-sort-key=\"" + escapeHtml(col.key) + "\" data-sort-fmt=\"" + escapeHtml(col.fmt || "string") + "\" style=\"cursor:pointer;user-select:none;\" title=\"' . esc_js(__('Click to sort', 'ai-eshop-optimizer')) . '\">" +
                           escapeHtml(col.label) + " <span class=\"aieo-sp-sort-icon\" style=\"opacity:" + opacity + ";\">" + icon + "</span></th>";
                }
                function rowsHtml(rowsToRender) {
                    var html = "";
                    rowsToRender.forEach(function(row){
                        html += "<tr>";
                        cols.forEach(function(col){
                            var v = row[col.key];
                            if (col.fmt === "currency") {
                                html += "<td style=\"text-align:right;font-variant-numeric:tabular-nums;\">" + fmtEur(v) + "</td>";
                            } else if (col.fmt === "currency_strong") {
                                html += "<td style=\"text-align:right;font-weight:600;font-variant-numeric:tabular-nums;\">" + fmtEur(v) + "</td>";
                            } else if (col.fmt === "integer") {
                                html += "<td style=\"text-align:right;font-variant-numeric:tabular-nums;\">" + fmtInt(v) + "</td>";
                            } else if (col.fmt === "percent") {
                                var pct = parseFloat(v) || 0;
                                var color = pct > 0.30 ? "#b91c1c" : (pct > 0.15 ? "#b45309" : "#15803d");
                                html += "<td style=\"text-align:right;color:" + color + ";font-variant-numeric:tabular-nums;\">" + fmtPct(pct) + "</td>";
                            } else {
                                html += "<td>" + escapeHtml(v) + "</td>";
                            }
                        });
                        html += "</tr>";
                    });
                    return html;
                }
                function renderTable(rowsToRender) {
                    var html = "<table class=\"wp-list-table widefat fixed striped aieo-sp-sortable\"><thead><tr>";
                    cols.forEach(function(col){ html += sortableHeaderHtml(col, sortState); });
                    html += "</tr></thead><tbody>" + rowsHtml(rowsToRender) + "</tbody></table>";
                    tableWrap.innerHTML = html;
                    // Re-bind header click after every re-render (innerHTML
                    // wipes the listeners).
                    tableWrap.querySelectorAll("th[data-sort-key]").forEach(function(th){
                        th.addEventListener("click", function(){
                            var key = th.dataset.sortKey;
                            var fmt = th.dataset.sortFmt;
                            if (sortState.key === key) {
                                sortState.dir = sortState.dir === 1 ? -1 : (sortState.dir === -1 ? 0 : 1);
                            } else {
                                sortState.key = key; sortState.dir = 1;
                            }
                            var next;
                            if (sortState.dir === 0) {
                                next = originalRows.slice();
                            } else {
                                next = rows.slice();
                                var dir = sortState.dir;
                                var numeric = isNumericFmt(fmt);
                                next.sort(function(a, b){
                                    var va = a[key], vb = b[key];
                                    if (numeric) {
                                        var na = parseFloat(va) || 0, nb = parseFloat(vb) || 0;
                                        return dir * (na - nb);
                                    }
                                    return dir * String(va == null ? "" : va).localeCompare(String(vb == null ? "" : vb), undefined, { numeric: true, sensitivity: "base" });
                                });
                            }
                            rows = next;
                            renderTable(next);
                        });
                    });
                }
                renderTable(rows);
            }

            refreshBtn.addEventListener("click", fetchData);
            monthsSel.addEventListener("change", fetchData);
            groupSel.addEventListener("change", fetchData);
            methodSel.addEventListener("change", fetchData);
            zoneSel.addEventListener("change", fetchData);

            exportBtn.addEventListener("click", function(){
                var p = buildParams();
                p.set("action", "aieo_dmm_shipping_pnl_export");
                window.location.href = ajax + "?" + p.toString();
            });

            fetchData();
        })();</script>';
        echo '</div>';
    }

    public function ajax_shipping_pnl() {
        AIEO_DMM::verify_ajax_request();
        if (!current_user_can('manage_woocommerce')) {
            wp_send_json_error(array('message' => __('Insufficient permissions.', 'ai-eshop-optimizer')));
        }
        wp_send_json_success($this->compute_shipping_pnl($_POST));
    }

    public function ajax_shipping_pnl_export() {
        AIEO_DMM::verify_ajax_request();
        if (!current_user_can('manage_woocommerce')) { wp_die(esc_html__('Insufficient permissions.', 'ai-eshop-optimizer')); }
        $payload = $this->compute_shipping_pnl($_GET);
        if (!class_exists('AIEO_DMM_XLSX_Writer')) { wp_die(esc_html__('XLSX writer missing.', 'ai-eshop-optimizer')); }

        $writer = new AIEO_DMM_XLSX_Writer('Shipping P&L');
        $headers = array(); foreach ($payload['columns'] as $col) { $headers[] = $col['label']; }
        $writer->add_header_row($headers);

        $col_formats = array();
        foreach ($payload['columns'] as $i => $col) {
            if ($col['fmt'] === 'currency' || $col['fmt'] === 'currency_strong') { $col_formats[$i] = 'currency'; }
            elseif ($col['fmt'] === 'integer') { $col_formats[$i] = 'integer'; }
            elseif ($col['fmt'] === 'percent') { $col_formats[$i] = 'number'; }
            else { $col_formats[$i] = 'string'; }
        }
        foreach ($payload['rows'] as $row) {
            $cells = array();
            foreach ($payload['columns'] as $col) {
                $cells[] = isset($row[$col['key']]) ? $row[$col['key']] : '';
            }
            $writer->add_row($cells, $col_formats);
        }

        
        $totals_row = array();
        foreach ($payload['columns'] as $i => $col) {
            $k = $col['key'];
            if ($i === 0) { $totals_row[] = __('TOTAL', 'ai-eshop-optimizer'); }
            elseif (isset($payload['totals'][$k])) { $totals_row[] = $payload['totals'][$k]; }
            elseif ($k === 'pct_free') {
                $inv = (float) $payload['totals']['carrier_invoice'];
                $totals_row[] = $inv > 0 ? ((float) $payload['totals']['free_cost'] / $inv) : 0;
            } else { $totals_row[] = ''; }
        }
        $writer->add_row($totals_row, $col_formats);

        $months = (int) $payload['filters']['months'];
        $writer->stream_download('shipping-pnl-' . $months . 'mo-' . gmdate('Y-m-d') . '.xlsx');
    }

    
    private function period_window_sql($period_value, $date_col) {
        $period = (string) $period_value;
        if ($period === 'current') {
            return array(
                'sql'   => "{$date_col} >= DATE_FORMAT(NOW(), '%Y-%m-01')",
                'tag'   => 'current-month',
                'months_for_compat' => 1,
            );
        }
        if ($period === 'last') {
            return array(
                'sql'   => "{$date_col} >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
                            AND {$date_col} <  DATE_FORMAT(NOW(), '%Y-%m-01')",
                'tag'   => 'last-month',
                'months_for_compat' => 1,
            );
        }
        $n = max(1, min(60, (int) $period));
        return array(
            'sql'   => "{$date_col} >= DATE_SUB(NOW(), INTERVAL {$n} MONTH)",
            'tag'   => $n . 'mo',
            'months_for_compat' => $n,
        );
    }

    private function compute_shipping_pnl($input) {
        global $wpdb;
        $prefix = $wpdb->prefix;

        $period_in = isset($input['months']) ? sanitize_text_field((string) $input['months']) : '12';
        $period    = $this->period_window_sql($period_in, 'o.date_created_gmt');
        $months    = $period['months_for_compat'];
        $group  = isset($input['group_by']) ? sanitize_key((string) $input['group_by']) : 'month_method_zone';
        $valid  = array('month_method_zone','month_method','month_zone','method_zone','month','method','zone');
        if (!in_array($group, $valid, true)) { $group = 'month_method_zone'; }
        $f_method = isset($input['method']) ? sanitize_text_field((string) $input['method']) : '';
        $f_zone   = isset($input['zone'])   ? sanitize_text_field((string) $input['zone'])   : '';

        $orders_tbl   = $prefix . 'wc_orders';
        $opdata_tbl   = $prefix . 'wc_order_operational_data';
        $items_tbl    = $prefix . 'woocommerce_order_items';
        $itemmeta_tbl = $prefix . 'woocommerce_order_itemmeta';
        $zones_tbl    = $prefix . 'woocommerce_shipping_zones';
        $zonemeth_tbl = $prefix . 'woocommerce_shipping_zone_methods';
        $attr_tbl     = $prefix . 'aieo_dmm_offer_attributions';

        $hpos_ok = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $orders_tbl)) === $orders_tbl;
        $unmatched_zone = __('Unmatched zone', 'ai-eshop-optimizer');
        $unknown_method = __('Unknown method', 'ai-eshop-optimizer');

        $empty = function() use ($months, $group, $f_method, $f_zone) {
            return array(
                'rows'    => array(),
                'columns' => array(),
                'totals'  => array('paid_orders'=>0,'free_orders'=>0,'total_orders'=>0,'shipping_revenue'=>0,'free_cost'=>0,'carrier_invoice'=>0,'pct_free'=>0),
                'methods' => array(),
                'zones'   => array(),
                'filters' => array('months'=>$months,'group_by'=>$group,'method'=>$f_method,'zone'=>$f_zone),
                'message' => '',
            );
        };

        if (!$hpos_ok) {
            $r = $empty();
            $r['message'] = __('HPOS (wp_wc_orders) is required for the shipping P&L view. Enable WooCommerce → Settings → Advanced → Features → "High-Performance order storage".', 'ai-eshop-optimizer');
            return $r;
        }

        $paid_sql = $wpdb->prepare(
            "SELECT
                DATE_FORMAT(o.date_created_gmt, %s)            AS month_key,
                COALESCE(z.zone_name, %s)                       AS zone_name,
                COALESCE(NULLIF(oi.order_item_name, ''), %s)    AS method_label,
                COUNT(DISTINCT o.id)                            AS paid_orders,
                COALESCE(SUM(od.shipping_total_amount), 0)      AS shipping_revenue,
                0                                               AS free_orders,
                0                                               AS free_cost
             FROM {$orders_tbl} o
             INNER JOIN {$opdata_tbl} od ON od.order_id = o.id
             LEFT 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.status IN ('wc-processing','wc-completed','wc-on-hold','processing','completed','on-hold')
               AND o.type = 'shop_order'
               AND od.shipping_total_amount > 0
               AND ({$period['sql']})
             GROUP BY month_key, zone_name, method_label",
            '%Y-%m', $unmatched_zone, $unknown_method
        );

        $free_sql = $wpdb->prepare(
            "SELECT
                DATE_FORMAT(o.date_created_gmt, %s)             AS month_key,
                COALESCE(NULLIF(a.shipping_zone, ''), %s)       AS zone_name,
                COALESCE(NULLIF(a.shipping_method, ''), %s)     AS method_label,
                0                                               AS paid_orders,
                0                                               AS shipping_revenue,
                COUNT(*)                                        AS free_orders,
                COALESCE(SUM(a.actual_cost), 0)                 AS free_cost
             FROM {$attr_tbl} a
             INNER JOIN {$orders_tbl} o ON o.id = a.order_id
             WHERE a.offer_type = 'free_shipping'
               AND o.type = 'shop_order'
               AND ({$period['sql']})
             GROUP BY month_key, zone_name, method_label",
            '%Y-%m', $unmatched_zone, $unknown_method
        );

        $select_dims = array();
        $group_dims  = array();
        $columns     = array();
        if (in_array($group, array('month_method_zone','month_method','month_zone','month'), true)) {
            $select_dims[] = 'month_key';
            $group_dims[]  = 'month_key';
            $columns[]     = array('key'=>'month_key', 'label'=>__('Month', 'ai-eshop-optimizer'), 'fmt'=>'string');
        }
        if (in_array($group, array('month_method_zone','month_method','method_zone','method'), true)) {
            $select_dims[] = 'method_label';
            $group_dims[]  = 'method_label';
            $columns[]     = array('key'=>'method_label', 'label'=>__('Shipping method', 'ai-eshop-optimizer'), 'fmt'=>'string');
        }
        if (in_array($group, array('month_method_zone','month_zone','method_zone','zone'), true)) {
            $select_dims[] = 'zone_name';
            $group_dims[]  = 'zone_name';
            $columns[]     = array('key'=>'zone_name', 'label'=>__('Zone', 'ai-eshop-optimizer'), 'fmt'=>'string');
        }

        $columns[] = array('key'=>'total_orders',     'label'=>__('Orders', 'ai-eshop-optimizer'),                'fmt'=>'integer');
        $columns[] = array('key'=>'free_orders',      'label'=>__('Free orders', 'ai-eshop-optimizer'),           'fmt'=>'integer');
        $columns[] = array('key'=>'shipping_revenue', 'label'=>__('Customer revenue (€)', 'ai-eshop-optimizer'),  'fmt'=>'currency');
        $columns[] = array('key'=>'free_cost',        'label'=>__('Free shipping cost (€)', 'ai-eshop-optimizer'),'fmt'=>'currency');
        $columns[] = array('key'=>'carrier_invoice',  'label'=>__('Total carrier cost (€)', 'ai-eshop-optimizer'),'fmt'=>'currency_strong');
        $columns[] = array('key'=>'pct_free',         'label'=>__('Free %', 'ai-eshop-optimizer'),                'fmt'=>'percent');

        $select_csv = empty($select_dims) ? "''" : implode(', ', $select_dims);
        $group_csv  = empty($group_dims)  ? "''" : implode(', ', $group_dims);

        $outer_where = array();
        $outer_params = array();
        if ($f_method !== '') { $outer_where[] = 'method_label = %s'; $outer_params[] = $f_method; }
        if ($f_zone   !== '') { $outer_where[] = 'zone_name = %s';    $outer_params[] = $f_zone; }
        $outer_where_sql = $outer_where ? ('WHERE ' . implode(' AND ', $outer_where)) : '';

        $order_sql = 'ORDER BY ';
        $order_parts = array();
        if (in_array('month_key',    $group_dims, true)) { $order_parts[] = 'month_key DESC'; }
        if (in_array('method_label', $group_dims, true)) { $order_parts[] = 'method_label ASC'; }
        if (in_array('zone_name',    $group_dims, true)) { $order_parts[] = 'zone_name ASC'; }
        $order_sql .= empty($order_parts) ? 'carrier_invoice DESC' : implode(', ', $order_parts);

        $final_sql = "SELECT {$select_csv},
                             SUM(paid_orders) AS paid_orders,
                             SUM(free_orders) AS free_orders,
                             SUM(paid_orders + free_orders) AS total_orders,
                             SUM(shipping_revenue) AS shipping_revenue,
                             SUM(free_cost) AS free_cost,
                             SUM(shipping_revenue + free_cost) AS carrier_invoice
                      FROM (
                        {$paid_sql}
                        UNION ALL
                        {$free_sql}
                      ) sub
                      {$outer_where_sql}
                      " . (empty($group_dims) ? '' : "GROUP BY {$group_csv}") . "
                      {$order_sql}";

        $prepared = $outer_params ? $wpdb->prepare($final_sql, $outer_params) : $final_sql;
        $rows_raw = $wpdb->get_results($prepared, ARRAY_A);
        if (!is_array($rows_raw)) { $rows_raw = array(); }

        $rows = array();
        $totals = array(
            'paid_orders'      => 0,
            'free_orders'      => 0,
            'total_orders'     => 0,
            'shipping_revenue' => 0.0,
            'free_cost'        => 0.0,
            'carrier_invoice'  => 0.0,
        );
        foreach ($rows_raw as $r) {
            $invoice = (float) $r['carrier_invoice'];
            $free    = (float) $r['free_cost'];
            $row = array(
                'paid_orders'      => (int) $r['paid_orders'],
                'free_orders'      => (int) $r['free_orders'],
                'total_orders'     => (int) $r['total_orders'],
                'shipping_revenue' => round((float) $r['shipping_revenue'], 2),
                'free_cost'        => round($free, 2),
                'carrier_invoice'  => round($invoice, 2),
                'pct_free'         => $invoice > 0 ? round($free / $invoice, 4) : 0,
            );
            if (isset($r['month_key']))    { $row['month_key']    = $r['month_key']; }
            if (isset($r['method_label'])) { $row['method_label'] = $r['method_label']; }
            if (isset($r['zone_name']))    { $row['zone_name']    = $r['zone_name']; }
            $rows[] = $row;

            $totals['paid_orders']      += (int) $r['paid_orders'];
            $totals['free_orders']      += (int) $r['free_orders'];
            $totals['total_orders']     += (int) $r['total_orders'];
            $totals['shipping_revenue'] += (float) $r['shipping_revenue'];
            $totals['free_cost']        += $free;
            $totals['carrier_invoice']  += $invoice;
        }
        foreach (array('shipping_revenue','free_cost','carrier_invoice') as $k) {
            $totals[$k] = round($totals[$k], 2);
        }
        $totals['pct_free'] = $totals['carrier_invoice'] > 0 ? round($totals['free_cost'] / $totals['carrier_invoice'], 4) : 0;

        $methods = $wpdb->get_col("SELECT DISTINCT method_label FROM (
            ({$paid_sql})
            UNION ALL
            ({$free_sql})
        ) m WHERE method_label <> '' ORDER BY method_label ASC");
        if (!is_array($methods)) { $methods = array(); }
        $zones = $wpdb->get_col("SELECT DISTINCT zone_name FROM (
            ({$paid_sql})
            UNION ALL
            ({$free_sql})
        ) z WHERE zone_name <> '' ORDER BY zone_name ASC");
        if (!is_array($zones)) { $zones = array(); }

        return array(
            'rows'    => $rows,
            'columns' => $columns,
            'totals'  => $totals,
            'methods' => $methods,
            'zones'   => $zones,
            'filters' => array('months'=>$months,'group_by'=>$group,'method'=>$f_method,'zone'=>$f_zone),
        );
    }
}
