"""Editor for the per-document picking-list pickup sequence.

The barcode client remains unaware of the editor: it reads the effective order
from ``dbo.py_ViewPackingListPrelievo``.  This module only manages the Python-
specific configuration rows used by that view.
"""

from __future__ import annotations

from dataclasses import dataclass
from typing import Any, Callable
import tkinter as tk
from tkinter import messagebox

import customtkinter as ctk

from busy_overlay import InlineBusyOverlay
from gestione_aree import AsyncRunner
from locale_text import load_locale_catalog, text as loc_text
from ui_theme import theme_color, theme_font, theme_section
from user_session import UserSession
from version_info import module_version, versioned_title
from tooltips import WidgetToolTip, load_tooltip_catalog, tooltip_text

__version__ = module_version(__name__)


SQL_LOAD_GROUPS = """
SELECT
    GruppoPrelievoChiave,
    MAX(GruppoPrelievo) AS GruppoPrelievo,
    MIN(OrdineGruppoDefault) AS OrdineGruppoDefault,
    MIN(OrdineGruppoConfigurato) AS OrdineGruppoConfigurato
FROM dbo.py_ViewPackingListPrelievo
WHERE CAST(Documento AS varchar(50)) = :documento
  AND Cella <> 9999
GROUP BY GruppoPrelievoChiave
ORDER BY
    CASE WHEN MIN(OrdineGruppoConfigurato) IS NULL THEN 1 ELSE 0 END,
    MIN(OrdineGruppoConfigurato),
    MIN(OrdineGruppoDefault),
    MAX(GruppoPrelievo);
"""


SQL_RESET_SEQUENCE = """
SET NOCOUNT ON;
SET XACT_ABORT ON;
UPDATE dbo.py_PickingListPrelievoSequenza
SET DelUtente = :utente,
    DelDataOra = SYSDATETIME(),
    ModUtente = :utente,
    ModDataOra = SYSDATETIME()
WHERE Documento = :documento
  AND DelDataOra IS NULL;
SELECT CAST(0 AS int) AS ElementiSalvati;
"""


def _rows_to_dicts(result: dict[str, Any] | None) -> list[dict[str, Any]]:
    """Convert the shared database payload to dictionaries."""

    if not isinstance(result, dict):
        return []
    rows = result.get("rows") or []
    columns = result.get("columns") or []
    if rows and isinstance(rows[0], dict):
        return [dict(row) for row in rows]
    return [
        {str(columns[index]): row[index] for index in range(min(len(columns), len(row)))}
        for row in rows
        if isinstance(row, (list, tuple))
    ]


@dataclass(frozen=True)
class PickupGroup:
    """One rack group, or the conventional non-shelved group."""

    key: str
    label: str
    default_order: int


class PickingListSequenceWindow(ctk.CTkToplevel):
    """Drag-and-drop editor for one picking-list pickup sequence."""

    def __init__(
        self,
        parent: tk.Misc,
        db_client,
        *,
        documento: str,
        session: UserSession | None,
        on_saved: Callable[[], None] | None = None,
    ):
        super().__init__(parent)
        self.db_client = db_client
        self.documento = str(documento or "").strip()
        self.session = session
        self.on_saved = on_saved
        self._catalog = load_locale_catalog()
        self._tooltip_catalog = load_tooltip_catalog()
        self._theme = theme_section("picking_sequence_window", {})
        self.runner = AsyncRunner(self)
        self.busy = InlineBusyOverlay(self, self._theme)
        self.available: list[PickupGroup] = []
        self.selected: list[PickupGroup] = []
        self._selected_key: str | None = None
        self._selected_side: str | None = None
        self._drag_group: PickupGroup | None = None
        self._drag_side: str | None = None

        self.title(versioned_title(self._t("picking.sequence.title", "Sequenza di prelievo"), __name__))
        self.geometry("780x560")
        self.minsize(680, 480)
        self.transient(parent)
        self.configure(fg_color=theme_color(self._theme, "window_fg_color", ("#efefef", "#2f2f2f")))
        self.protocol("WM_DELETE_WINDOW", self.destroy)
        self.bind("<Escape>", lambda _event: self.destroy())

        self._build_layout()
        self.withdraw()
        self.after_idle(self._show_and_load)

    def _t(self, key: str, default: str, **values: Any) -> str:
        """Return one localized and formatted label."""

        value = loc_text(key, catalog=self._catalog, default=default)
        try:
            return value.format(**values)
        except Exception:
            return value

    def _build_layout(self) -> None:
        """Build the two lists and the explicit keyboard-friendly controls."""

        self.grid_columnconfigure(0, weight=1)
        self.grid_columnconfigure(2, weight=1)
        self.grid_rowconfigure(2, weight=1)

        ctk.CTkLabel(
            self,
            text=self._t("picking.sequence.document", "Picking List {documento}", documento=self.documento),
            font=theme_font(self._theme, "title_font", ("Segoe UI", 16, "bold")),
        ).grid(row=0, column=0, columnspan=3, padx=16, pady=(14, 4), sticky="w")

        ctk.CTkLabel(
            self,
            text=self._t(
                "picking.sequence.hint",
                "Trascina gli scaffali a destra. Quelli non inseriti saranno accodati secondo l'ordine predefinito.",
            ),
            anchor="w",
            justify="left",
            wraplength=720,
        ).grid(row=1, column=0, columnspan=3, padx=16, pady=(0, 10), sticky="ew")

        self.available_panel, self.available_list = self._build_list_panel(
            0, self._t("picking.sequence.available", "Scaffali disponibili")
        )
        controls = ctk.CTkFrame(self, fg_color="transparent")
        controls.grid(row=2, column=1, padx=10, pady=4)
        for label, command in (
            (self._t("picking.sequence.button.add", "Aggiungi >"), self._add_selected),
            (self._t("picking.sequence.button.remove", "< Rimuovi"), self._remove_selected),
            (self._t("picking.sequence.button.up", "Su"), lambda: self._move_selected(-1)),
            (self._t("picking.sequence.button.down", "Giu'"), lambda: self._move_selected(1)),
        ):
            button = ctk.CTkButton(controls, text=label, width=120, command=command)
            button.pack(pady=5)
            tooltip_key = {
                self._t("picking.sequence.button.add", "Aggiungi >"): "picking.sequence.add",
                self._t("picking.sequence.button.remove", "< Rimuovi"): "picking.sequence.remove",
                self._t("picking.sequence.button.up", "Su"): "picking.sequence.up",
                self._t("picking.sequence.button.down", "Giu'"): "picking.sequence.down",
            }.get(label, "")
            self._attach_tooltip(button, tooltip_key)

        self.selected_panel, self.selected_list = self._build_list_panel(
            2, self._t("picking.sequence.selected", "Ordine personalizzato")
        )

        ctk.CTkLabel(
            self,
            text=self._t(
                "picking.sequence.default_hint",
                "Default: prima Non scaff., poi gli scaffali secondo l'ordine WMS.",
            ),
            anchor="w",
            text_color=theme_color(self._theme, "secondary_text_color", "#4b5563"),
        ).grid(row=3, column=0, columnspan=3, padx=16, pady=(8, 4), sticky="ew")

        bottom = ctk.CTkFrame(self)
        bottom.grid(row=4, column=0, columnspan=3, padx=16, pady=(6, 14), sticky="ew")
        bottom.grid_columnconfigure(0, weight=1)
        reset_button = ctk.CTkButton(
            bottom,
            text=self._t("picking.sequence.button.reset", "Ripristina default"),
            command=self._reset_default,
            fg_color=theme_color(self._theme, "reset_button_fg_color", "#6b7280"),
        )
        reset_button.grid(row=0, column=1, padx=5, pady=8)
        self._attach_tooltip(reset_button, "picking.sequence.reset")
        save_button = ctk.CTkButton(
            bottom,
            text=self._t("picking.sequence.button.save", "Salva"),
            command=self._save,
        )
        save_button.grid(row=0, column=2, padx=5, pady=8)
        self._attach_tooltip(save_button, "picking.sequence.save")
        close_button = ctk.CTkButton(
            bottom,
            text=self._t("picking.sequence.button.close", "Chiudi"),
            command=self.destroy,
        )
        close_button.grid(row=0, column=3, padx=5, pady=8)
        self._attach_tooltip(close_button, "picking.sequence.close")

    def _attach_tooltip(self, widget: tk.Misc, key: str) -> None:
        """Attach a localized tooltip when the catalog contains one."""

        value = tooltip_text(key, catalog=self._tooltip_catalog) if key else ""
        if value:
            WidgetToolTip(widget, value)

    def _build_list_panel(self, column: int, title: str):
        """Create one titled, scrollable group list."""

        panel = ctk.CTkFrame(self)
        panel.grid(row=2, column=column, padx=(16 if column == 0 else 0, 16 if column == 2 else 0), pady=4, sticky="nsew")
        panel.grid_columnconfigure(0, weight=1)
        panel.grid_rowconfigure(1, weight=1)
        ctk.CTkLabel(panel, text=title, font=("Segoe UI", 12, "bold")).grid(
            row=0, column=0, padx=10, pady=(10, 6), sticky="w"
        )
        content = ctk.CTkScrollableFrame(panel, fg_color=theme_color(self._theme, "list_fg_color", "#f8fafc"))
        content.grid(row=1, column=0, padx=8, pady=(0, 8), sticky="nsew")
        content.grid_columnconfigure(0, weight=1)
        return panel, content

    def _show_and_load(self) -> None:
        """Center the ready window and start its asynchronous load."""

        try:
            self.update_idletasks()
            parent = self.master
            x = max(0, parent.winfo_rootx() + (parent.winfo_width() - self.winfo_width()) // 2)
            y = max(0, parent.winfo_rooty() + (parent.winfo_height() - self.winfo_height()) // 2)
            self.geometry(f"+{x}+{y}")
        except Exception:
            pass
        self.deiconify()
        self.lift()
        self.focus_force()
        self._load()

    def _load(self) -> None:
        """Load available groups and the optional saved sequence."""

        async def _job():
            return await self.db_client.query_json(SQL_LOAD_GROUPS, {"documento": self.documento})

        def _ok(result):
            rows = _rows_to_dicts(result)
            configured: list[tuple[int, PickupGroup]] = []
            available: list[PickupGroup] = []
            for row in rows:
                group = PickupGroup(
                    key=str(row.get("GruppoPrelievoChiave") or "").strip(),
                    label=str(row.get("GruppoPrelievo") or "").strip(),
                    default_order=int(row.get("OrdineGruppoDefault") or 0),
                )
                configured_order = row.get("OrdineGruppoConfigurato")
                if configured_order is None:
                    available.append(group)
                else:
                    configured.append((int(configured_order), group))
            self.available = sorted(available, key=lambda item: (item.default_order, item.label))
            self.selected = [item for _order, item in sorted(configured, key=lambda pair: pair[0])]
            self._render_lists()

        def _error(exc):
            messagebox.showerror(
                self._t("picking.sequence.title", "Sequenza di prelievo"),
                self._t("picking.sequence.msg.load_error", "Errore durante il caricamento:\n{error}", error=exc),
                parent=self,
            )

        self.runner.run(
            _job(),
            on_success=_ok,
            on_error=_error,
            busy=self.busy,
            message=self._t("picking.sequence.busy.load", "Carico gli scaffali..."),
        )

    def _render_lists(self) -> None:
        """Redraw list buttons after a move or a database load."""

        for host in (self.available_list, self.selected_list):
            for child in host.winfo_children():
                child.destroy()
        for row, group in enumerate(self.available):
            self._render_group_button(self.available_list, group, "available", row)
        for row, group in enumerate(self.selected):
            self._render_group_button(self.selected_list, group, "selected", row)

    def _render_group_button(self, host, group: PickupGroup, side: str, row: int) -> None:
        """Render one selectable and draggable group button."""

        is_selected = self._selected_key == group.key and self._selected_side == side
        prefix = f"{row + 1}. " if side == "selected" else ""
        button = ctk.CTkButton(
            host,
            text=f"{prefix}{group.label}",
            anchor="w",
            height=34,
            fg_color="#1976d2" if is_selected else theme_color(self._theme, "item_fg_color", "#2ebf74"),
            command=lambda g=group, s=side: self._select(g, s),
        )
        button.grid(row=row, column=0, padx=4, pady=4, sticky="ew")
        button.bind("<Double-Button-1>", lambda _event, g=group, s=side: self._toggle_group(g, s), add="+")
        drag_widgets = [button]
        for attr in ("_canvas", "_text_label", "_image_label"):
            widget = getattr(button, attr, None)
            if widget is not None:
                drag_widgets.append(widget)
        for widget in drag_widgets:
            widget.bind("<ButtonPress-1>", lambda _event, g=group, s=side: self._start_drag(g, s), add="+")
            widget.bind("<ButtonRelease-1>", self._finish_drag, add="+")
        self._attach_tooltip(button, "picking.sequence.item")

    def _select(self, group: PickupGroup, side: str) -> None:
        """Select one group for the explicit move controls."""

        self._selected_key = group.key
        self._selected_side = side
        self._render_lists()

    def _toggle_group(self, group: PickupGroup, side: str) -> None:
        """Move a double-clicked group to the opposite side."""

        self._selected_key = group.key
        self._selected_side = side
        self._add_selected() if side == "available" else self._remove_selected()

    def _selected_group(self) -> PickupGroup | None:
        """Return the group selected in the active side."""

        source = self.available if self._selected_side == "available" else self.selected
        return next((item for item in source if item.key == self._selected_key), None)

    def _add_selected(self) -> None:
        """Append the selected available group to the custom sequence."""

        if self._selected_side != "available":
            return
        group = self._selected_group()
        if group is None:
            return
        self.available = [item for item in self.available if item.key != group.key]
        self.selected.append(group)
        self._selected_side = "selected"
        self._render_lists()

    def _remove_selected(self) -> None:
        """Return the selected configured group to the available list."""

        if self._selected_side != "selected":
            return
        group = self._selected_group()
        if group is None:
            return
        self.selected = [item for item in self.selected if item.key != group.key]
        self.available.append(group)
        self.available.sort(key=lambda item: (item.default_order, item.label))
        self._selected_side = "available"
        self._render_lists()

    def _move_selected(self, offset: int) -> None:
        """Move one configured group up or down."""

        if self._selected_side != "selected":
            return
        index = next((i for i, item in enumerate(self.selected) if item.key == self._selected_key), -1)
        target = index + int(offset)
        if index < 0 or target < 0 or target >= len(self.selected):
            return
        self.selected[index], self.selected[target] = self.selected[target], self.selected[index]
        self._render_lists()

    def _start_drag(self, group: PickupGroup, side: str) -> None:
        """Remember the item being dragged."""

        self._drag_group = group
        self._drag_side = side

    @staticmethod
    def _is_descendant(widget: tk.Misc | None, ancestor: tk.Misc) -> bool:
        """Return whether a widget belongs to one list panel."""

        current = widget
        while current is not None:
            if current == ancestor:
                return True
            current = getattr(current, "master", None)
        return False

    def _finish_drag(self, event) -> None:
        """Drop the dragged group into a list and place it near the pointer."""

        group = self._drag_group
        if group is None:
            return
        self._drag_group = None
        try:
            target_widget = self.winfo_containing(event.x_root, event.y_root)
        except Exception:
            return
        if self._is_descendant(target_widget, self.selected_panel):
            self.available = [item for item in self.available if item.key != group.key]
            self.selected = [item for item in self.selected if item.key != group.key]
            target_y = event.y_root - self.selected_list.winfo_rooty()
            row_height = 42
            target_index = max(0, min(len(self.selected), target_y // row_height))
            self.selected.insert(target_index, group)
            self._selected_side = "selected"
        elif self._is_descendant(target_widget, self.available_panel):
            self.selected = [item for item in self.selected if item.key != group.key]
            if not any(item.key == group.key for item in self.available):
                self.available.append(group)
            self.available.sort(key=lambda item: (item.default_order, item.label))
            self._selected_side = "available"
        else:
            return
        self._selected_key = group.key
        self._render_lists()

    def _save_sql(self) -> tuple[str, dict[str, Any]]:
        """Build one atomic soft-delete/insert batch using bound parameters."""

        params: dict[str, Any] = {
            "documento": self.documento,
            "utente": self.session.login if self.session else "SYSTEM",
        }
        values: list[str] = []
        for index, group in enumerate(self.selected):
            params[f"key_{index}"] = group.key
            params[f"label_{index}"] = group.label
            params[f"order_{index}"] = index
            values.append(
                f"(:documento, :key_{index}, :label_{index}, :order_{index}, :utente, SYSDATETIME(), :utente, SYSDATETIME())"
            )
        insert = ""
        if values:
            insert = """
INSERT INTO dbo.py_PickingListPrelievoSequenza (
    Documento, GruppoChiave, GruppoEtichetta, Ordine,
    InsUtente, InsDataOra, ModUtente, ModDataOra
)
VALUES
""" + ",\n".join(values) + ";"
        sql = f"""
SET NOCOUNT ON;
SET XACT_ABORT ON;
UPDATE dbo.py_PickingListPrelievoSequenza
SET DelUtente = :utente,
    DelDataOra = SYSDATETIME(),
    ModUtente = :utente,
    ModDataOra = SYSDATETIME()
WHERE Documento = :documento
  AND DelDataOra IS NULL;
{insert}
SELECT CAST({len(values)} AS int) AS ElementiSalvati;
"""
        return sql, params

    def _save(self) -> None:
        """Persist the configured prefix atomically."""

        sql, params = self._save_sql()

        async def _job():
            return await self.db_client.query_json(sql, params, commit=True)

        self.runner.run(
            _job(),
            on_success=lambda _result: self._save_completed("saved"),
            on_error=self._save_error,
            busy=self.busy,
            message=self._t("picking.sequence.busy.save", "Salvo la sequenza..."),
        )

    def _reset_default(self) -> None:
        """Delete the custom sequence so the shared default becomes active."""

        async def _job():
            return await self.db_client.query_json(
                SQL_RESET_SEQUENCE,
                {
                    "documento": self.documento,
                    "utente": self.session.login if self.session else "SYSTEM",
                },
                commit=True,
            )

        self.runner.run(
            _job(),
            on_success=lambda _result: self._save_completed("reset"),
            on_error=self._save_error,
            busy=self.busy,
            message=self._t("picking.sequence.busy.save", "Salvo la sequenza..."),
        )

    def _save_completed(self, mode: str) -> None:
        """Refresh the editor and notify the parent after a successful save."""

        message_key = "picking.sequence.msg.reset" if mode == "reset" else "picking.sequence.msg.saved"
        default = (
            "Sequenza personalizzata rimossa. E' attivo l'ordine predefinito."
            if mode == "reset"
            else "Sequenza salvata."
        )
        messagebox.showinfo(self._t("picking.sequence.title", "Sequenza di prelievo"), self._t(message_key, default), parent=self)
        if self.on_saved:
            self.on_saved()
        self._selected_key = None
        self._selected_side = None
        self._load()

    def _save_error(self, exc: BaseException) -> None:
        """Show a localized save error."""

        messagebox.showerror(
            self._t("picking.sequence.title", "Sequenza di prelievo"),
            self._t("picking.sequence.msg.save_error", "Errore durante il salvataggio:\n{error}", error=exc),
            parent=self,
        )


def open_pickinglist_sequence_window(
    parent: tk.Misc,
    db_client,
    *,
    documento: str,
    session: UserSession | None,
    on_saved: Callable[[], None] | None = None,
) -> PickingListSequenceWindow:
    """Open at most one sequence editor for the picking-list window."""

    key = "_pickinglist_sequence_window_singleton"
    existing = getattr(parent, key, None)
    if existing is not None:
        try:
            if existing.winfo_exists() and existing.documento == str(documento).strip():
                existing.on_saved = on_saved
                existing.deiconify()
                existing.lift()
                existing.focus_force()
                return existing
            if existing.winfo_exists():
                existing.destroy()
        except Exception:
            pass

    window = PickingListSequenceWindow(
        parent,
        db_client,
        documento=documento,
        session=session,
        on_saved=on_saved,
    )
    setattr(parent, key, window)

    def _clear_reference() -> None:
        try:
            if getattr(parent, key, None) is window:
                setattr(parent, key, None)
        finally:
            window.destroy()

    window.protocol("WM_DELETE_WINDOW", _clear_reference)
    return window