"""
Portfolio (greedy-knapsack) results hotlink viewer.

The Portfolio engine produces a richer structure than the generic table
viewer can convey: a per-row InPortfolioSort flag (0/1), a budget
report (spent vs. allocated, per-silo balance, efficacy), and the
strategy chosen by the user. This dialog surfaces all of that.

Layout:
  - Portfolio Report tab — strategy, selection counts, budget bar, silos.
  - Selected Items tab   — only rows with InPortfolioSort == 1.
  - Excluded Items tab   — only rows with InPortfolioSort == 0.
  - All Items tab        — full result table.
  - Map tab              — local canvas with the persisted result layer.
  - Summary tab          — workflow metadata + result column listing.

The same shared toolbar as LPA / CDP (Print Tab, Export Tab CSV,
Export Map PNG, Export All Tabs XLSX, Add Calculated Field) keeps the
portfolio output consistent with the rest of the workbench.
"""

from __future__ import annotations

from typing import Any, Dict, List, Optional

import pandas as pd

from .qt_compat import (
    QAbstractItemView,
    QDialog,
    QFormLayout,
    QFrame,
    QHBoxLayout,
    QHeaderView,
    QLabel,
    QLineEdit,
    QMessageBox,
    QProgressBar,
    QPushButton,
    QSplitter,
    QTabWidget,
    QTableView,
    QTextEdit,
    QVBoxLayout,
    QWidget,
    Qt,
    exec_dialog,
)

from . import debug_logger
from .result_export_helpers import (
    TabExportSpec,
    export_dataframe_to_csv,
    export_dataframes_to_xlsx,
    export_widget_as_png,
    print_table_view,
)
from .results_grid_dialog import ResultsFilterProxyModel
from .results_grid_model import DataFrameTableModel
from .translator import tr, add_listener, remove_listener

try:
    from qgis.core import QgsProject, QgsVectorLayer
    from qgis.gui import QgsMapCanvas
except Exception:  # pragma: no cover - QGIS not available in standalone tests
    QgsProject = None
    QgsVectorLayer = None
    QgsMapCanvas = None


_INPORTFOLIO_FIELD_CANDIDATES = (
    "InPortfolioSort",
    "InPortfolio",
    "PortfolioSort",
    "in_portfolio",
    "in_portfolio_sort",
)


def _strategy_label(strategy_value: str) -> str:
    """Resolve a strategy code (e.g. 'costbenefit') to its translated label."""
    if not strategy_value:
        return tr("portfolio_results.strategy_not_reported")
    key = f"portfolio_strategies.{strategy_value}"
    label = tr(key)
    # tr() returns the key string when the lookup fails — fall back to raw value.
    return label if label != key else strategy_value


class PortfolioResultsDialog(QDialog):
    """First-pass Portfolio viewer with selection split + budget report."""

    @classmethod
    def from_payload(cls, payload: Dict[str, Any], parent=None) -> "PortfolioResultsDialog":
        return cls(
            workflow_name=str(payload.get("workflow_name", "")).strip(),
            execution_id=str(payload.get("execution_id", "")).strip(),
            results_df=payload.get("results_df"),
            portfolio_report=dict(payload.get("portfolio_report") or {}),
            strategy=str(payload.get("strategy", "") or "").strip(),
            cost_field=str(payload.get("cost_field", "") or "").strip(),
            benefit_field=str(payload.get("benefit_field", "") or "").strip(),
            unique_field=str(payload.get("unique_field", "") or "").strip(),
            inportfolio_field=str(payload.get("inportfolio_field", "") or "").strip(),
            database_path=str(payload.get("database_path", "")).strip(),
            output_table_name=str(payload.get("output_table_name", "")).strip(),
            payload=payload,
            parent=parent,
        )

    def __init__(
        self,
        workflow_name: str,
        execution_id: str,
        results_df: Optional[pd.DataFrame],
        portfolio_report: Optional[Dict[str, Any]] = None,
        *,
        strategy: str = "",
        cost_field: str = "",
        benefit_field: str = "",
        unique_field: str = "",
        inportfolio_field: str = "",
        database_path: str = "",
        output_table_name: str = "",
        payload: Optional[Dict[str, Any]] = None,
        parent=None,
    ):
        super().__init__(parent)
        self.workflow_name = workflow_name or tr("portfolio_results.default_name")
        self.execution_id = execution_id
        self.payload = dict(payload or {})
        self.portfolio_report = dict(portfolio_report or {})
        self.strategy = strategy
        self.cost_field = cost_field
        self.benefit_field = benefit_field
        self.unique_field = unique_field
        self.database_path = database_path
        self.output_table_name = output_table_name

        self.results_df = self._coerce_dataframe(results_df)
        self.inportfolio_field = (
            inportfolio_field
            or self._detect_inportfolio_field(self.results_df)
            or "InPortfolioSort"
        )
        self.selected_df = self._filter_inportfolio(self.results_df, selected=True)
        self.excluded_df = self._filter_inportfolio(self.results_df, selected=False)

        self.layer_name_hint = output_table_name or self.workflow_name
        self._result_layer: Optional["QgsVectorLayer"] = None

        self.setWindowTitle(tr("portfolio_results.window_title", name=self.workflow_name))
        self.resize(1320, 860)
        self._init_ui()
        self._refresh_export_buttons_for_current_tab()
        self.retranslateUi()
        add_listener(self.retranslateUi)

    # ------------------------------------------------------------------
    # Layout
    # ------------------------------------------------------------------
    def _init_ui(self) -> None:
        layout = QVBoxLayout()
        self.setLayout(layout)

        self.header_label = QLabel(self._format_header())
        self.header_label.setWordWrap(True)
        layout.addWidget(self.header_label)

        self.tabs = QTabWidget()
        layout.addWidget(self.tabs, 1)

        self._build_report_tab()
        self._build_selected_tab()
        self._build_excluded_tab()
        self._build_all_items_tab()
        self._build_map_tab()
        self._build_summary_tab()

        self.tabs.setCurrentIndex(0)

        button_row = QHBoxLayout()

        self.btn_print_tab = QPushButton(tr("portfolio_results.btn_print"))
        self.btn_print_tab.setToolTip(tr("portfolio_results.tip_print"))
        self.btn_print_tab.clicked.connect(self._on_print_current_tab)
        button_row.addWidget(self.btn_print_tab)

        self.btn_export_tab_csv = QPushButton(tr("portfolio_results.btn_export_csv"))
        self.btn_export_tab_csv.setToolTip(tr("portfolio_results.tip_export_csv"))
        self.btn_export_tab_csv.clicked.connect(self._on_export_current_tab_csv)
        button_row.addWidget(self.btn_export_tab_csv)

        self.btn_export_map_png = QPushButton(tr("portfolio_results.btn_export_png"))
        self.btn_export_map_png.setToolTip(tr("portfolio_results.tip_export_png"))
        self.btn_export_map_png.clicked.connect(self._on_export_map_png)
        button_row.addWidget(self.btn_export_map_png)

        self.btn_export_all_xlsx = QPushButton(tr("portfolio_results.btn_export_xlsx"))
        self.btn_export_all_xlsx.setToolTip(tr("portfolio_results.tip_export_xlsx"))
        self.btn_export_all_xlsx.clicked.connect(self._on_export_all_tabs_xlsx)
        button_row.addWidget(self.btn_export_all_xlsx)

        self.btn_add_calc_field = QPushButton(tr("portfolio_results.btn_add_calc"))
        self.btn_add_calc_field.clicked.connect(self._on_add_calculated_field)
        button_row.addWidget(self.btn_add_calc_field)

        button_row.addStretch()
        self.close_button = QPushButton(tr("portfolio_results.btn_close"))
        self.close_button.clicked.connect(self.close)
        button_row.addWidget(self.close_button)
        layout.addLayout(button_row)

        self.tabs.currentChanged.connect(self._refresh_export_buttons_for_current_tab)

    def _format_header(self) -> str:
        return tr(
            "portfolio_results.header",
            name=self.workflow_name,
            exec_id=self.execution_id or tr("portfolio_results.execution_unknown"),
            selected=len(self.selected_df.index),
            total=len(self.results_df.index),
            strategy=self._strategy_display_label(),
        )

    def retranslateUi(self) -> None:
        self.setWindowTitle(tr("portfolio_results.window_title", name=self.workflow_name))
        if hasattr(self, "header_label"):
            self.header_label.setText(self._format_header())
        if hasattr(self, "tabs") and self.tabs.count() >= 6:
            self.tabs.setTabText(0, tr("portfolio_results.tab_report"))
            self.tabs.setTabText(1, tr("portfolio_results.tab_selected", count=len(self.selected_df.index)))
            self.tabs.setTabText(2, tr("portfolio_results.tab_excluded", count=len(self.excluded_df.index)))
            self.tabs.setTabText(3, tr("portfolio_results.tab_all"))
            self.tabs.setTabText(4, tr("portfolio_results.tab_map"))
            self.tabs.setTabText(5, tr("portfolio_results.tab_summary"))
        for attr, key in (
            ("btn_print_tab", "portfolio_results.btn_print"),
            ("btn_export_tab_csv", "portfolio_results.btn_export_csv"),
            ("btn_export_map_png", "portfolio_results.btn_export_png"),
            ("btn_export_all_xlsx", "portfolio_results.btn_export_xlsx"),
            ("btn_add_calc_field", "portfolio_results.btn_add_calc"),
            ("close_button", "portfolio_results.btn_close"),
        ):
            btn = getattr(self, attr, None)
            if btn is not None:
                btn.setText(tr(key))
        for attr, key in (
            ("btn_print_tab", "portfolio_results.tip_print"),
            ("btn_export_tab_csv", "portfolio_results.tip_export_csv"),
            ("btn_export_map_png", "portfolio_results.tip_export_png"),
            ("btn_export_all_xlsx", "portfolio_results.tip_export_xlsx"),
        ):
            btn = getattr(self, attr, None)
            if btn is not None:
                btn.setToolTip(tr(key))
        # Refresh dynamic content
        if hasattr(self, "summary_text"):
            self.summary_text.setPlainText(self._build_summary_dump())
        if hasattr(self, "_report_text"):
            self._report_text.setPlainText(self._build_report_textual_dump())
        # Search-row labels in the filter table bundles (each has its own labels).
        for bundle_attr in ("_selected_bundle", "_excluded_bundle", "_all_items_bundle"):
            bundle = getattr(self, bundle_attr, None)
            if bundle and "search_label" in bundle:
                bundle["search_label"].setText(tr("portfolio_results.lbl_search"))
                bundle["search"].setPlaceholderText(tr("portfolio_results.search_placeholder"))
                bundle["btn_clear"].setText(tr("portfolio_results.btn_clear"))
        self._refresh_export_buttons_for_current_tab()
        if hasattr(self, "map_status") and self._result_layer is not None:
            try:
                self.map_status.setText(
                    tr("portfolio_results.map_showing", name=self._result_layer.name(), table_name=self.output_table_name)
                )
            except Exception:
                pass

    def closeEvent(self, event):
        remove_listener(self.retranslateUi)
        super().closeEvent(event)

    # ------------------------------------------------------------------
    # Tab builders
    # ------------------------------------------------------------------
    def _build_report_tab(self) -> None:
        tab = QWidget(self)
        layout = QVBoxLayout()
        tab.setLayout(layout)

        layout.addWidget(QLabel(tr("portfolio_results.report_summary_header", name=self.workflow_name)))

        form_host = QFrame(self)
        form_host.setFrameShape(QFrame.Shape.StyledPanel)
        form = QFormLayout()
        form_host.setLayout(form)
        layout.addWidget(form_host)

        report = self.portfolio_report
        rows_considered = self._coerce_int(report.get("rows_considered"), len(self.results_df.index))
        selected_count = self._coerce_int(
            report.get("selected_count"), len(self.selected_df.index)
        )
        total_spent = self._coerce_float(report.get("total_spent"))
        total_budget = self._coerce_float(report.get("total_budget"))
        budget_left = self._coerce_float(report.get("budget_left"))
        efficacy = self._coerce_float(report.get("efficacy"))
        processing_time_ms = self._coerce_int(report.get("processing_time_ms"))

        unspecified = tr("portfolio_results.unspecified")
        form.addRow(tr("portfolio_results.row_strategy"), QLabel(self._strategy_display_label()))
        form.addRow(tr("portfolio_results.row_cost_field"), QLabel(self.cost_field or unspecified))
        form.addRow(tr("portfolio_results.row_benefit_field"), QLabel(self.benefit_field or unspecified))
        form.addRow(
            tr("portfolio_results.row_selection"),
            QLabel(self._format_selection_summary(selected_count, rows_considered)),
        )
        form.addRow(
            tr("portfolio_results.row_budget"),
            QLabel(self._format_budget_summary(total_spent, total_budget, budget_left)),
        )

        # Budget utilization bar — guards against zero/missing budgets.
        self.budget_bar = QProgressBar(self)
        self.budget_bar.setRange(0, 1000)
        self.budget_bar.setFormat(tr("portfolio_results.budget_bar_format_pct"))
        self.budget_bar.setMinimumWidth(260)
        if total_budget > 0:
            ratio = max(0.0, min(total_spent / total_budget, 1.5))
            self.budget_bar.setValue(int(round(ratio * 1000)))
        else:
            self.budget_bar.setRange(0, 1)
            self.budget_bar.setValue(0)
            self.budget_bar.setFormat(tr("portfolio_results.budget_bar_not_reported"))
        form.addRow(tr("portfolio_results.row_budget_utilization"), self.budget_bar)

        form.addRow(
            tr("portfolio_results.row_efficacy"),
            QLabel(f"{efficacy:,.4f}" if efficacy else tr("portfolio_results.efficacy_not_reported")),
        )
        if processing_time_ms:
            form.addRow(
                tr("portfolio_results.row_engine_time"),
                QLabel(tr("portfolio_results.engine_time_ms", ms=processing_time_ms)),
            )

        # Per-silo bar charts when the strategy used silos.
        silo_summary = self._build_silo_summary(report, total_budget)
        if silo_summary:
            silos_label = QLabel(tr("portfolio_results.silo_balances"))
            silos_label.setStyleSheet("font-weight: bold; margin-top: 8px;")
            layout.addWidget(silos_label)
            silos_host = QFrame(self)
            silos_host.setFrameShape(QFrame.Shape.StyledPanel)
            silos_layout = QFormLayout()
            silos_host.setLayout(silos_layout)
            for silo_label, silo_text in silo_summary:
                silos_layout.addRow(silo_label, QLabel(silo_text))
            layout.addWidget(silos_host)

        # A free-form description of the run for printable export.
        report_text = QTextEdit()
        report_text.setReadOnly(True)
        report_text.setPlainText(self._build_report_textual_dump())
        layout.addWidget(report_text, 1)
        self._report_text = report_text

        self.tabs.addTab(tab, tr("portfolio_results.tab_report"))

    def _build_selected_tab(self) -> None:
        tab = QWidget(self)
        outer = QVBoxLayout()
        tab.setLayout(outer)

        outer.addWidget(QLabel(
            tr("portfolio_results.selected_tab_header", count=len(self.selected_df.index))
        ))

        bundle = self._build_filterable_table(self._sort_selected_for_display(self.selected_df))
        outer.addWidget(bundle["host"], 1)
        self._selected_bundle = bundle

        self.tabs.addTab(tab, tr("portfolio_results.tab_selected", count=len(self.selected_df.index)))

    def _build_excluded_tab(self) -> None:
        tab = QWidget(self)
        outer = QVBoxLayout()
        tab.setLayout(outer)

        outer.addWidget(QLabel(
            tr("portfolio_results.excluded_tab_header", count=len(self.excluded_df.index))
        ))

        bundle = self._build_filterable_table(self.excluded_df)
        outer.addWidget(bundle["host"], 1)
        self._excluded_bundle = bundle

        self.tabs.addTab(tab, tr("portfolio_results.tab_excluded", count=len(self.excluded_df.index)))

    def _build_all_items_tab(self) -> None:
        tab = QWidget(self)
        outer = QVBoxLayout()
        tab.setLayout(outer)
        outer.addWidget(QLabel(tr("portfolio_results.all_tab_header", count=len(self.results_df.index))))
        bundle = self._build_filterable_table(self.results_df)
        outer.addWidget(bundle["host"], 1)
        self._all_items_bundle = bundle
        self.tabs.addTab(tab, tr("portfolio_results.tab_all"))

    def _build_map_tab(self) -> None:
        tab = QWidget(self)
        layout = QVBoxLayout()
        tab.setLayout(layout)

        self.map_status = QLabel("")
        self.map_status.setWordWrap(True)
        layout.addWidget(self.map_status)

        if QgsMapCanvas is None:
            self.map_canvas = None
            placeholder = QLabel(tr("portfolio_results.map_no_qgis"))
            placeholder.setWordWrap(True)
            layout.addWidget(placeholder, 1)
            self.tabs.addTab(tab, tr("portfolio_results.tab_map"))
            return

        self.map_canvas = QgsMapCanvas(self)
        self.map_canvas.setCanvasColor(Qt.GlobalColor.white)
        self.map_canvas.enableAntiAliasing(True)
        layout.addWidget(self.map_canvas, 1)
        self._load_map_layer_if_possible()
        self.tabs.addTab(tab, tr("portfolio_results.tab_map"))

    def _build_summary_tab(self) -> None:
        tab = QWidget(self)
        layout = QVBoxLayout()
        tab.setLayout(layout)

        self.summary_text = QTextEdit()
        self.summary_text.setReadOnly(True)
        self.summary_text.setPlainText(self._build_summary_dump())
        layout.addWidget(self.summary_text, 1)
        self.tabs.addTab(tab, tr("portfolio_results.tab_summary"))

    # ------------------------------------------------------------------
    # Map / layer
    # ------------------------------------------------------------------
    def _load_map_layer_if_possible(self) -> None:
        if QgsVectorLayer is None or self.map_canvas is None:
            return
        if not (self.database_path and self.output_table_name):
            self.map_status.setText(tr("portfolio_results.map_no_gpkg"))
            return

        candidate: Optional[QgsVectorLayer] = None
        uri = f"{self.database_path}|layername={self.output_table_name}"
        if QgsProject is not None:
            for layer in QgsProject.instance().mapLayers().values():
                if isinstance(layer, QgsVectorLayer) and layer.source() == uri:
                    candidate = layer
                    break
        if candidate is None:
            candidate = QgsVectorLayer(uri, self.layer_name_hint, "ogr")
            if not candidate.isValid():
                self.map_status.setText(
                    tr(
                        "portfolio_results.map_open_failed",
                        database_path=self.database_path,
                        table_name=self.output_table_name,
                    )
                )
                return

        self._result_layer = candidate
        try:
            self.map_canvas.setLayers([candidate])
            self.map_canvas.setExtent(candidate.extent())
            self.map_canvas.refresh()
        except Exception as exc:
            debug_logger.warning("Could not bind Portfolio layer to canvas", {"exception": str(exc)})
            self.map_status.setText(tr("portfolio_results.map_drawn_failed", error=exc))
            return

        self.map_status.setText(
            tr("portfolio_results.map_showing", name=candidate.name(), table_name=self.output_table_name)
        )

    # ------------------------------------------------------------------
    # Filterable-table helper (matches CDP's table bundle shape)
    # ------------------------------------------------------------------
    def _build_filterable_table(self, dataframe: pd.DataFrame) -> Dict[str, Any]:
        host = QWidget(self)
        layout = QVBoxLayout()
        layout.setContentsMargins(0, 0, 0, 0)
        host.setLayout(layout)

        search_row = QHBoxLayout()
        search_label = QLabel(tr("portfolio_results.lbl_search"))
        search_row.addWidget(search_label)
        search_box = QLineEdit()
        search_box.setPlaceholderText(tr("portfolio_results.search_placeholder"))
        search_row.addWidget(search_box, 1)
        btn_clear = QPushButton(tr("portfolio_results.btn_clear"))
        btn_clear.clicked.connect(search_box.clear)
        search_row.addWidget(btn_clear)
        layout.addLayout(search_row)

        model = DataFrameTableModel(dataframe, self)
        proxy = ResultsFilterProxyModel(self)
        proxy.setSourceModel(model)
        search_box.textChanged.connect(proxy.set_filter_text)

        view = QTableView()
        view.setModel(proxy)
        view.setSortingEnabled(True)
        view.setAlternatingRowColors(True)
        view.setSelectionBehavior(QAbstractItemView.SelectionBehavior.SelectRows)
        view.setSelectionMode(QAbstractItemView.SelectionMode.SingleSelection)
        view.setEditTriggers(QAbstractItemView.EditTrigger.NoEditTriggers)
        header = view.horizontalHeader()
        header.setStretchLastSection(True)
        header.setSectionResizeMode(QHeaderView.ResizeMode.ResizeToContents)
        layout.addWidget(view, 1)

        return {
            "host": host,
            "model": model,
            "proxy": proxy,
            "view": view,
            "search": search_box,
            "search_label": search_label,
            "btn_clear": btn_clear,
        }

    # ------------------------------------------------------------------
    # Toolbar handlers
    # ------------------------------------------------------------------
    def _refresh_export_buttons_for_current_tab(self, *_args) -> None:
        index = self.tabs.currentIndex()
        # Tab order: 0=Report, 1=Selected, 2=Excluded, 3=All, 4=Map, 5=Summary
        is_table_tab = index in (1, 2, 3)
        is_map_tab = index == 4
        self.btn_print_tab.setEnabled(is_table_tab)
        self.btn_export_tab_csv.setEnabled(is_table_tab)
        self.btn_export_map_png.setEnabled(is_map_tab and self.map_canvas is not None)
        self.btn_export_all_xlsx.setEnabled(not self.results_df.empty)
        self.btn_add_calc_field.setEnabled(bool(self.database_path and self.output_table_name))
        if not (self.database_path and self.output_table_name):
            self.btn_add_calc_field.setToolTip(tr("portfolio_results.tip_calc_disabled"))
        else:
            self.btn_add_calc_field.setToolTip(tr("portfolio_results.tip_calc_enabled"))

    def _current_table_bundle(self) -> Optional[Dict[str, Any]]:
        index = self.tabs.currentIndex()
        if index == 1:
            return getattr(self, "_selected_bundle", None)
        if index == 2:
            return getattr(self, "_excluded_bundle", None)
        if index == 3:
            return getattr(self, "_all_items_bundle", None)
        return None

    def _current_tab_name(self) -> str:
        index = self.tabs.currentIndex()
        return {1: "selected", 2: "excluded", 3: "all-items", 4: "map", 5: "summary"}.get(index, "tab")

    def _current_tab_display_name(self) -> str:
        index = self.tabs.currentIndex()
        return {
            1: tr("portfolio_results.print_tab_selected"),
            2: tr("portfolio_results.print_tab_excluded"),
            3: tr("portfolio_results.print_tab_all"),
        }.get(index, "")

    def _default_export_basename(self) -> str:
        name_part = (self.workflow_name or "portfolio").strip()
        execution_part = (self.execution_id or "").strip()[:8]
        if execution_part:
            return f"{name_part}-{execution_part}".replace(" ", "_")
        return name_part.replace(" ", "_")

    def _on_print_current_tab(self) -> None:
        bundle = self._current_table_bundle()
        if bundle is None:
            QMessageBox.information(
                self,
                tr("portfolio_results.msg_no_table_title"),
                tr("portfolio_results.msg_no_table_body"),
            )
            return
        title = tr(
            "portfolio_results.print_title_template",
            name=self.workflow_name,
            tab=self._current_tab_display_name(),
        )
        print_table_view(bundle["view"], title=title, parent=self)

    def _on_export_current_tab_csv(self) -> None:
        bundle = self._current_table_bundle()
        if bundle is None:
            return
        df = bundle["model"].dataframe()
        default_name = f"{self._default_export_basename()}-{self._current_tab_name()}.csv"
        export_dataframe_to_csv(df, default_name=default_name, parent=self)

    def _on_export_map_png(self) -> None:
        if self.map_canvas is None:
            QMessageBox.information(
                self,
                tr("portfolio_results.msg_no_map_title"),
                tr("portfolio_results.msg_no_map_body"),
            )
            return
        default_name = f"{self._default_export_basename()}-map.png"
        export_widget_as_png(self.map_canvas, default_name=default_name, parent=self)

    def _on_export_all_tabs_xlsx(self) -> None:
        sheets: List[TabExportSpec] = []
        sheets.append(TabExportSpec(name=tr("portfolio_results.sheet_selected"), dataframe=self._selected_bundle["model"].dataframe()))
        sheets.append(TabExportSpec(name=tr("portfolio_results.sheet_excluded"), dataframe=self._excluded_bundle["model"].dataframe()))
        sheets.append(TabExportSpec(name=tr("portfolio_results.sheet_all_items"), dataframe=self._all_items_bundle["model"].dataframe()))
        sheets.append(TabExportSpec(name=tr("portfolio_results.sheet_report"), dataframe=self._report_dataframe()))
        default_name = f"{self._default_export_basename()}-all-tabs.xlsx"
        export_dataframes_to_xlsx(sheets, default_name=default_name, parent=self)

    def _on_add_calculated_field(self) -> None:
        if not (self.database_path and self.output_table_name):
            QMessageBox.information(
                self,
                tr("portfolio_results.calc_title"),
                tr("portfolio_results.calc_not_backed"),
            )
            return
        if QgsVectorLayer is None:
            QMessageBox.critical(self, tr("portfolio_results.calc_title"), tr("portfolio_results.calc_no_qgis"))
            return

        uri = f"{self.database_path}|layername={self.output_table_name}"
        layer = QgsVectorLayer(uri, self.layer_name_hint, "ogr")
        if not layer.isValid():
            QMessageBox.critical(
                self,
                tr("portfolio_results.calc_title"),
                tr("portfolio_results.calc_open_failed", database_path=self.database_path, table_name=self.output_table_name),
            )
            return

        from .calculated_fields import CalculatedFieldError, materialize_calculated_field
        from .dialogs.calculated_field_dialog import CalculatedFieldDialog

        dialog = CalculatedFieldDialog(
            layer=layer,
            existing_field_names=list(self.results_df.columns),
            parent=self,
        )
        if exec_dialog(dialog) != QDialog.DialogCode.Accepted:
            return

        spec = dialog.spec()
        try:
            outcome = materialize_calculated_field(
                layer=layer,
                field_name=spec.name,
                qvariant_type=spec.qvariant_type,
                expression_text=spec.expression,
            )
        except CalculatedFieldError as exc:
            QMessageBox.critical(self, tr("portfolio_results.calc_title"), str(exc))
            return
        except Exception as exc:  # noqa: BLE001
            QMessageBox.critical(
                self,
                tr("portfolio_results.calc_title"),
                tr("portfolio_results.calc_unexpected_error", error=exc),
            )
            return

        # The new column is on every "view" of the results — refresh all three tables.
        new_df = outcome.dataframe.fillna("")
        self.results_df = new_df
        self.selected_df = self._filter_inportfolio(new_df, selected=True)
        self.excluded_df = self._filter_inportfolio(new_df, selected=False)
        self._selected_bundle["model"].set_dataframe(self._sort_selected_for_display(self.selected_df))
        self._excluded_bundle["model"].set_dataframe(self.excluded_df)
        self._all_items_bundle["model"].set_dataframe(self.results_df)
        self.summary_text.setPlainText(self._build_summary_dump())

        message = tr(
            "portfolio_results.calc_success_added",
            name=outcome.field_name,
            table_name=self.output_table_name,
        )
        if outcome.skipped_rows > 0:
            message += tr("portfolio_results.calc_success_skipped_note", count=outcome.skipped_rows)
        QMessageBox.information(self, tr("portfolio_results.calc_title"), message)

    # ------------------------------------------------------------------
    # Report-shaping helpers
    # ------------------------------------------------------------------
    def _strategy_display_label(self) -> str:
        return _strategy_label(self.strategy)

    def _format_selection_summary(self, selected: int, considered: int) -> str:
        if considered <= 0:
            return tr("portfolio_results.selection_just_count", selected=selected)
        ratio = selected / considered
        return tr(
            "portfolio_results.selection_summary",
            selected=selected,
            considered=considered,
            ratio=ratio * 100,
        )

    def _format_budget_summary(self, spent: float, budget: float, left: float) -> str:
        parts: List[str] = []
        parts.append(tr("portfolio_results.budget_spent", value=self._format_currency(spent)))
        if budget > 0:
            parts.append(tr("portfolio_results.budget_total", value=self._format_currency(budget)))
        if left:
            parts.append(tr("portfolio_results.budget_left", value=self._format_currency(left)))
        return "    ".join(parts)

    def _build_silo_summary(
        self,
        report: Dict[str, Any],
        total_budget: float,
    ) -> List[tuple]:
        rows: List[tuple] = []
        for letter, key in (("A", "left_silo_a"), ("B", "left_silo_b"), ("C", "left_silo_c")):
            raw_value = report.get(key)
            if raw_value in (None, ""):
                continue
            remaining = self._coerce_float(raw_value)
            text = tr("portfolio_results.silo_remaining", value=self._format_currency(remaining))
            if total_budget > 0 and remaining is not None:
                share = max(0.0, min(remaining / total_budget, 1.0)) * 100
                text += tr("portfolio_results.silo_share", share=share)
            rows.append((tr("portfolio_results.silo_label", letter=letter), text))
        return rows

    def _build_report_textual_dump(self) -> str:
        report = self.portfolio_report
        if not report and not self.strategy:
            return tr("portfolio_results.report_no_metadata")
        lines = [tr("portfolio_results.report_pipeline", value=self.workflow_name)]
        if self.execution_id:
            lines.append(tr("portfolio_results.report_execution_id", value=self.execution_id))
        lines.append(tr("portfolio_results.report_strategy", value=self._strategy_display_label()))
        if self.cost_field:
            lines.append(tr("portfolio_results.report_cost_field", value=self.cost_field))
        if self.benefit_field:
            lines.append(tr("portfolio_results.report_benefit_field", value=self.benefit_field))
        lines.append("")
        if report:
            lines.append(tr("portfolio_results.report_header"))
            for key in (
                "selected_count",
                "rows_considered",
                "total_spent",
                "total_budget",
                "budget_left",
                "left_silo_a",
                "left_silo_b",
                "left_silo_c",
                "efficacy",
                "processing_time_ms",
            ):
                if key in report:
                    lines.append(tr("portfolio_results.report_line", field=key, value=report[key]))
        return "\n".join(lines)

    def _report_dataframe(self) -> pd.DataFrame:
        report = dict(self.portfolio_report)
        field_col = tr("portfolio_results.report_field_col")
        value_col = tr("portfolio_results.report_value_col")
        rows = [
            {field_col: tr("portfolio_results.row_strategy"), value_col: self._strategy_display_label()},
            {field_col: tr("portfolio_results.row_cost_field"), value_col: self.cost_field},
            {field_col: tr("portfolio_results.row_benefit_field"), value_col: self.benefit_field},
            {field_col: "Unique Field", value_col: self.unique_field},
        ]
        for key, value in report.items():
            rows.append({field_col: key, value_col: value})
        return pd.DataFrame(rows)

    def _build_summary_dump(self) -> str:
        none_text = tr("portfolio_results.summary_none")
        unknown_text = tr("portfolio_results.execution_unknown")
        lines = [
            tr("portfolio_results.summary_pipeline", value=self.workflow_name),
            tr("portfolio_results.summary_execution", value=self.execution_id or unknown_text),
            tr("portfolio_results.summary_geopackage", value=self.database_path or none_text),
            tr("portfolio_results.summary_result_table", value=self.output_table_name or none_text),
            tr("portfolio_results.summary_strategy", value=self._strategy_display_label()),
            tr("portfolio_results.summary_inportfolio_field", value=self.inportfolio_field),
            "",
            tr("portfolio_results.summary_columns_header", count=len(self.results_df.columns)),
        ]
        for column_name in self.results_df.columns:
            lines.append(tr("portfolio_results.summary_column_line", name=column_name))
        return "\n".join(lines)

    # ------------------------------------------------------------------
    # Static helpers
    # ------------------------------------------------------------------
    @staticmethod
    def _coerce_dataframe(value: Any) -> pd.DataFrame:
        if isinstance(value, pd.DataFrame):
            return value.copy().fillna("")
        if value is None:
            return pd.DataFrame()
        try:
            return pd.DataFrame(value).fillna("")
        except Exception:
            return pd.DataFrame()

    @staticmethod
    def _coerce_int(value: Any, fallback: int = 0) -> int:
        try:
            return int(value)
        except (TypeError, ValueError):
            return fallback

    @staticmethod
    def _coerce_float(value: Any, fallback: float = 0.0) -> float:
        try:
            return float(value)
        except (TypeError, ValueError):
            return fallback

    @staticmethod
    def _format_currency(value: float) -> str:
        # The portfolio engine treats budget/cost as opaque numbers, so we
        # don't assume a currency symbol. A grouped numeric format is the
        # safest default and stays readable for very large budgets.
        return f"{value:,.2f}"

    @staticmethod
    def _detect_inportfolio_field(dataframe: pd.DataFrame) -> Optional[str]:
        if dataframe is None or dataframe.empty:
            return None
        columns = {str(name).lower(): str(name) for name in dataframe.columns}
        for candidate in _INPORTFOLIO_FIELD_CANDIDATES:
            actual = columns.get(candidate.lower())
            if actual is not None:
                return actual
        return None

    def _filter_inportfolio(self, dataframe: pd.DataFrame, *, selected: bool) -> pd.DataFrame:
        if dataframe is None or dataframe.empty:
            return pd.DataFrame()
        column = self._detect_inportfolio_field(dataframe) or self.inportfolio_field
        if column not in dataframe.columns:
            return pd.DataFrame()

        # Coerce common 0/1 / true/false / "yes"/"no" representations.
        series = dataframe[column]
        truthy = series.apply(self._is_truthy_inportfolio)
        if selected:
            return dataframe[truthy].reset_index(drop=True)
        return dataframe[~truthy].reset_index(drop=True)

    @staticmethod
    def _is_truthy_inportfolio(value: Any) -> bool:
        if isinstance(value, bool):
            return value
        if isinstance(value, (int, float)):
            try:
                return float(value) >= 0.5
            except Exception:
                return False
        text = str(value).strip().lower()
        if not text:
            return False
        if text in {"1", "1.0", "true", "yes", "y", "selected", "in"}:
            return True
        if text in {"0", "0.0", "false", "no", "n", "excluded", "out"}:
            return False
        try:
            return float(text) >= 0.5
        except ValueError:
            return False

    def _sort_selected_for_display(self, dataframe: pd.DataFrame) -> pd.DataFrame:
        """Sort the Selected tab by benefit/cost ratio when both columns exist."""
        if dataframe is None or dataframe.empty:
            return dataframe

        if self.benefit_field and self.cost_field and self.benefit_field in dataframe.columns and self.cost_field in dataframe.columns:
            try:
                copy = dataframe.copy()
                cost = pd.to_numeric(copy[self.cost_field], errors="coerce")
                benefit = pd.to_numeric(copy[self.benefit_field], errors="coerce")
                # Avoid divide-by-zero — fall back to benefit alone for those rows.
                copy["_emds_score"] = (benefit / cost.replace(0, pd.NA)).fillna(benefit)
                copy = copy.sort_values("_emds_score", ascending=False).drop(columns=["_emds_score"])
                return copy.reset_index(drop=True)
            except Exception:
                return dataframe

        if self.benefit_field and self.benefit_field in dataframe.columns:
            try:
                copy = dataframe.copy()
                copy["_emds_score"] = pd.to_numeric(copy[self.benefit_field], errors="coerce")
                copy = copy.sort_values("_emds_score", ascending=False).drop(columns=["_emds_score"])
                return copy.reset_index(drop=True)
            except Exception:
                return dataframe

        return dataframe
