"""Read-only UDC movement history window."""

from __future__ import annotations

import tkinter as tk
from tkinter import messagebox, ttk
from typing import Any

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, theme_value
from window_placement import place_window_fullsize_below_parent_later


SQL_STORICO_UDC = """
WITH direct AS (
    SELECT
        ID,
        IDRiferimento
    FROM dbo.MagazziniPallet
    WHERE (:udc IS NULL OR Attributo COLLATE Latin1_General_CI_AS LIKE CONCAT('%', :udc, '%'))
),
shipped AS (
    SELECT
        shipped.BarcodePallet,
        shipped.NumeroPallet,
        shipped.IDMagazzino,
        shipped.IDArea,
        shipped.IDCella
    FROM dbo.XMag_GiacenzaPalletPlistChiuse shipped
    WHERE (:udc IS NULL OR shipped.BarcodePallet COLLATE Latin1_General_CI_AS LIKE CONCAT('%', :udc, '%'))
),
has_physical_p AS (
    SELECT DISTINCT
        s.BarcodePallet
    FROM shipped s
    JOIN dbo.MagazziniPallet seed
      ON seed.Attributo COLLATE Latin1_General_CI_AS =
         s.BarcodePallet COLLATE Latin1_General_CI_AS
    JOIN dbo.MagazziniPallet mp
      ON mp.Tipo = 'P'
     AND (
            mp.Attributo COLLATE Latin1_General_CI_AS =
            s.BarcodePallet COLLATE Latin1_General_CI_AS
         OR mp.ID = seed.ID
         OR mp.IDRiferimento = seed.ID
         OR (seed.IDRiferimento IS NOT NULL AND seed.IDRiferimento > 0 AND mp.ID = seed.IDRiferimento)
         OR (seed.IDRiferimento IS NOT NULL AND seed.IDRiferimento > 0 AND mp.IDRiferimento = seed.IDRiferimento)
     )
),
roots AS (
    SELECT ID AS RootID
    FROM direct
    UNION
    SELECT IDRiferimento AS RootID
    FROM direct
    WHERE IDRiferimento IS NOT NULL AND IDRiferimento > 0
    UNION
    SELECT mp.ID AS RootID
    FROM dbo.MagazziniPallet mp
    JOIN shipped s
      ON s.BarcodePallet COLLATE Latin1_General_CI_AS =
         mp.Attributo COLLATE Latin1_General_CI_AS
)
SELECT TOP (500) *
FROM (
SELECT
    CAST(mp.ID AS int) AS ID,
    CAST(mp.Tipo AS varchar(8)) AS Tipo,
    CAST(mp.IDRiferimento AS int) AS IDRiferimento,
    CAST(mp.NumeroPallet AS int) AS NumeroPallet,
    CAST(mp.Attributo AS varchar(16)) AS UDC,
    CAST(mp.IDMagazzino AS int) AS IDMagazzino,
    CAST(mp.IDArea AS int) AS IDArea,
    CAST(mp.IDCella AS int) AS IDCella,
    UPPER(
        CONCAT(
            COALESCE(LTRIM(RTRIM(c.Corsia)), 'NA'), '.',
            COALESCE(LTRIM(RTRIM(CAST(c.Colonna AS varchar(32)))), 'NA'), '.',
            COALESCE(LTRIM(RTRIM(CAST(c.Fila AS varchar(32)))), 'NA')
        )
    ) AS Ubicazione,
    CAST(mp.DataMagazzino AS datetime) AS DataMagazzino,
    CAST(mp.InsUtente AS varchar(50)) AS InsUtente,
    CAST(mp.InsDataOra AS datetime) AS InsDataOra,
    CAST(mp.ModUtente AS varchar(50)) AS ModUtente,
    CAST(mp.ModDataOra AS datetime) AS ModDataOra
FROM dbo.MagazziniPallet mp
LEFT JOIN dbo.Celle c ON c.ID = mp.IDCella
WHERE
    :udc IS NULL
    OR mp.Attributo COLLATE Latin1_General_CI_AS LIKE CONCAT('%', :udc, '%')
    OR mp.ID IN (SELECT RootID FROM roots)
    OR mp.IDRiferimento IN (SELECT RootID FROM roots)
UNION ALL
SELECT
    CAST(NULL AS int) AS ID,
    CAST('SPED' AS varchar(8)) AS Tipo,
    CAST(NULL AS int) AS IDRiferimento,
    CAST(s.NumeroPallet AS int) AS NumeroPallet,
    CAST(s.BarcodePallet AS varchar(16)) AS UDC,
    CAST(s.IDMagazzino AS int) AS IDMagazzino,
    CAST(s.IDArea AS int) AS IDArea,
    CAST(s.IDCella AS int) AS IDCella,
    UPPER(
        CONCAT(
            COALESCE(LTRIM(RTRIM(c.Corsia)), 'NA'), '.',
            COALESCE(LTRIM(RTRIM(CAST(c.Colonna AS varchar(32)))), 'NA'), '.',
            COALESCE(LTRIM(RTRIM(CAST(c.Fila AS varchar(32)))), 'NA')
        )
    ) AS Ubicazione,
    CAST(NULL AS datetime) AS DataMagazzino,
    CAST('Picking list chiusa' AS varchar(50)) AS InsUtente,
    CAST(NULL AS datetime) AS InsDataOra,
    CAST(NULL AS varchar(50)) AS ModUtente,
    CAST(NULL AS datetime) AS ModDataOra
FROM shipped s
LEFT JOIN dbo.Celle c ON c.ID = s.IDCella
LEFT JOIN has_physical_p hp
  ON hp.BarcodePallet COLLATE Latin1_General_CI_AS =
     s.BarcodePallet COLLATE Latin1_General_CI_AS
WHERE hp.BarcodePallet IS NULL
) rows
ORDER BY UDC, CASE WHEN ID IS NULL THEN 0 ELSE 1 END DESC, ID DESC;
"""


def _rows_to_dicts(res: dict[str, Any] | None) -> list[dict[str, Any]]:
    if not isinstance(res, dict):
        return []
    rows = res.get("rows") or []
    cols = res.get("columns") or []
    if rows and isinstance(rows[0], dict):
        return [row for row in rows if isinstance(row, dict)]
    out: list[dict[str, Any]] = []
    for row in rows:
        if isinstance(row, (list, tuple)) and cols:
            out.append({str(cols[i]): row[i] for i in range(min(len(cols), len(row)))})
    return out


class StoricoUDCWindow(ctk.CTkToplevel):
    """Window that shows the P/V movement timeline of one or more UDCs."""

    def __init__(self, parent: tk.Widget, db_client, session=None, initial_udc: str | None = None):
        super().__init__(parent)
        self.db_client = db_client
        self.session = session
        self._theme = theme_section("history_udc_window", theme_section("search_window", {}))
        self._locale_catalog = load_locale_catalog()
        self._async = AsyncRunner(self)
        self._busy = InlineBusyOverlay(self, self._theme)
        self.var_udc = tk.StringVar(value=str(initial_udc or ""))

        self.title(loc_text("history.udc.title", catalog=self._locale_catalog, default="Storico movimenti UDC"))
        self.geometry(str(theme_value(self._theme, "window_geometry", "1100x720")))
        minsize = theme_value(self._theme, "window_minsize", [900, 560])
        self.minsize(int(minsize[0]), int(minsize[1]))
        try:
            self.configure(fg_color=theme_color(self._theme, "window_fg_color", ("#efefef", "#2f2f2f")))
        except Exception:
            pass

        self._build_ui()
        if initial_udc:
            self.after(250, self._do_search)

    def _build_ui(self) -> None:
        self.grid_rowconfigure(1, weight=1)
        self.grid_columnconfigure(0, weight=1)

        top = ctk.CTkFrame(
            self,
            fg_color=theme_color(self._theme, "toolbar_frame_fg_color", ("#d7d7d7", "#3b3b3b")),
        )
        top.grid(row=0, column=0, sticky="ew", padx=8, pady=8)
        top.grid_columnconfigure(3, weight=1)

        label_font = theme_font(self._theme, "toolbar_label_font", ("Segoe UI", 10))
        entry_font = theme_font(self._theme, "entry_font", ("Segoe UI", 10))
        button_font = theme_font(self._theme, "toolbar_button_font", ("Segoe UI", 10, "bold"))

        ctk.CTkLabel(top, text="UDC:", font=label_font).grid(row=0, column=0, sticky="w")
        ctk.CTkEntry(top, textvariable=self.var_udc, width=160, font=entry_font).grid(
            row=0, column=1, sticky="w", padx=(4, 12)
        )
        ctk.CTkButton(
            top,
            text=loc_text("history.udc.button.search", catalog=self._locale_catalog, default="Cerca"),
            command=self._do_search,
            font=button_font,
        ).grid(
            row=0, column=2, sticky="w"
        )

        wrap = ctk.CTkFrame(self)
        wrap.grid(row=1, column=0, sticky="nsew", padx=8, pady=(0, 8))
        wrap.grid_rowconfigure(0, weight=1)
        wrap.grid_columnconfigure(0, weight=1)

        cols = (
            "ID",
            "Tipo",
            "Rif",
            "UDC",
            "Cella",
            "Ubicazione",
            "Data",
            "InsUtente",
            "InsDataOra",
            "ModUtente",
            "ModDataOra",
        )
        self.tree = ttk.Treeview(wrap, columns=cols, show="headings")
        for col in cols:
            self.tree.heading(col, text=col)
            self.tree.column(col, width=90, anchor="w")
        self.tree.column("ID", width=70, anchor="e")
        self.tree.column("Tipo", width=55, anchor="center")
        self.tree.column("Rif", width=70, anchor="e")
        self.tree.column("UDC", width=110)
        self.tree.column("Cella", width=70, anchor="e")
        self.tree.column("Ubicazione", width=130)
        self.tree.column("Data", width=150)
        self.tree.column("InsDataOra", width=150)
        self.tree.column("ModDataOra", width=150)
        self.tree.tag_configure("versamento", background="#EAF7EA")
        self.tree.tag_configure("prelievo", background="#FFF0E6")
        self.tree.tag_configure("spedita", background="#FFECEC", foreground="#B00020")

        sy = ttk.Scrollbar(wrap, orient="vertical", command=self.tree.yview)
        sx = ttk.Scrollbar(wrap, orient="horizontal", command=self.tree.xview)
        self.tree.configure(yscrollcommand=sy.set, xscrollcommand=sx.set)
        self.tree.grid(row=0, column=0, sticky="nsew")
        sy.grid(row=0, column=1, sticky="ns")
        sx.grid(row=1, column=0, sticky="ew")

    def _do_search(self) -> None:
        udc = str(self.var_udc.get() or "").strip()
        params = {"udc": udc or None}

        async def _job():
            return await self.db_client.query_json(SQL_STORICO_UDC, params)

        def _ok(res):
            rows = _rows_to_dicts(res)
            self._fill(rows)
            if not rows:
                messagebox.showinfo(
                    loc_text("history.udc.msg.title", catalog=self._locale_catalog, default="Storico UDC"),
                    loc_text("history.udc.msg.empty", catalog=self._locale_catalog, default="Nessun movimento trovato."),
                    parent=self,
                )

        def _err(ex):
            messagebox.showerror(
                loc_text("history.udc.msg.title", catalog=self._locale_catalog, default="Storico UDC"),
                loc_text(
                    "history.udc.msg.error",
                    catalog=self._locale_catalog,
                    default="Errore ricerca:\n{error}",
                ).format(error=ex),
                parent=self,
            )

        self._async.run(
            _job(),
            _ok,
            _err,
            busy=self._busy,
            message=loc_text("history.udc.busy", catalog=self._locale_catalog, default="Carico storico UDC..."),
        )

    def _fill(self, rows: list[dict[str, Any]]) -> None:
        self.tree.delete(*self.tree.get_children(""))
        def _value(row: dict[str, Any], name: str) -> Any:
            value = row.get(name, "")
            return "" if value is None else value

        for row in rows:
            tipo = str(row.get("Tipo") or "")
            tag = "versamento" if tipo == "V" else "prelievo" if tipo == "P" else "spedita" if tipo == "SPED" else ""
            self.tree.insert(
                "",
                "end",
                values=(
                    _value(row, "ID"),
                    tipo,
                    _value(row, "IDRiferimento"),
                    _value(row, "UDC"),
                    _value(row, "IDCella"),
                    _value(row, "Ubicazione"),
                    _value(row, "DataMagazzino"),
                    _value(row, "InsUtente"),
                    _value(row, "InsDataOra"),
                    _value(row, "ModUtente"),
                    _value(row, "ModDataOra"),
                ),
                tags=(tag,) if tag else (),
            )


def open_storico_udc_window(parent: tk.Misc, db_client, session=None, initial_udc: str | None = None) -> tk.Misc:
    """Open the UDC history window."""

    win = StoricoUDCWindow(parent, db_client, session=session, initial_udc=initial_udc)
    place_window_fullsize_below_parent_later(parent, win)
    win.bind("<Escape>", lambda _e: win.destroy())
    return win