"""
GUI nástroj (customtkinter) na generovanie JSON konfigurácie
pre Migration Tool s ručným mapovaním stĺpcov.

Funkcie:
- pripojí sa na source a target PostgreSQL databázu,
- načíta stĺpce zo starej a novej tabuľky,
- v GUI umožní pri každom source stĺpci vybrať target stĺpec (alebo ignorovať),
- vygeneruje JSON konfiguráciu v správnom formáte pre Migration Tool.

Použitie:
    1. pip install customtkinter sqlalchemy psycopg2-binary
    2. python migration_config_gui.py
"""

from __future__ import annotations

import json
from typing import Any, Dict, List, Tuple

import customtkinter as ctk
from tkinter import messagebox, filedialog

from sqlalchemy import create_engine, inspect
from sqlalchemy.engine import Engine
import pymysql

# ---------------------------------------------------------------------------
# DB introspekcia a generovanie konfigurácie
# ---------------------------------------------------------------------------

def create_db_engine(url: str) -> Engine:
    """Vytvorí SQLAlchemy Engine pre daný DB URL."""
    return create_engine(url)


def introspect_table(
    engine: Engine,
    schema_name: str,
    table_name: str,
) -> Dict[str, Any]:
    """
    Načíta stĺpce a PK info z DB a vráti ich v tvare:
    {
        "name": <table_name>,
        "columns": [
            {
                "name": ...,
                "data_type": ...,
                "is_nullable": ...,
                "is_primary_key": ...,
                "comment": ...,
            },
            ...
        ]
    }
    """
    inspector = inspect(engine)

    columns = inspector.get_columns(table_name, schema=schema_name)
    pk_info = inspector.get_pk_constraint(table_name, schema=schema_name) or {}
    pk_cols = set(pk_info.get("constrained_columns") or [])

    col_defs: List[Dict[str, Any]] = []
    for col in columns:
        name = col["name"]
        raw_type = col.get("type")
        data_type = str(raw_type).upper() if raw_type is not None else "TEXT"

        is_nullable = bool(col.get("nullable", True))
        is_pk = name in pk_cols
        comment = col.get("comment")

        # Odporúčaný cieľový typ podľa zvoleného dialektu
        target_type = map_type_to_target(data_type, engine.name)

        col_defs.append(
            {
                "name": name,
                "data_type": data_type,
                "target_type": target_type,
                "is_nullable": is_nullable,
                "is_primary_key": is_pk,
                "comment": comment,
            }
        )

    return {
        "name": table_name,
        "columns": col_defs,
    }

def map_type_to_target(data_type: str, dialect: str) -> str:
    """
    Vráti odporúčaný cieľový dátový typ pre daný zdrojový typ a dialekt.

    Používa jednoduché mapovanie medzi PostgreSQL/Oracle typmi a MySQL.
    Ak typ nepozná, vráti pôvodný.
    """
    dt = data_type.upper()

    mysql_map = {
        "INTEGER": "INT",
        "BIGINT": "BIGINT",
        "SMALLINT": "SMALLINT",
        "SERIAL": "INT AUTO_INCREMENT",
        "BIGSERIAL": "BIGINT AUTO_INCREMENT",
        "BOOLEAN": "TINYINT(1)",
        "TEXT": "LONGTEXT",
        "VARCHAR": "VARCHAR(255)",
        "CHARACTER VARYING": "VARCHAR(255)",
        "CHAR": "CHAR(1)",
        "NUMERIC": "DECIMAL(10,2)",
        "DECIMAL": "DECIMAL(10,2)",
        "FLOAT": "FLOAT",
        "DOUBLE PRECISION": "DOUBLE",
        "REAL": "FLOAT",
        "DATE": "DATE",
        "TIMESTAMP": "DATETIME",
        "TIMESTAMP WITHOUT TIME ZONE": "DATETIME",
        "TIMESTAMP WITH TIME ZONE": "DATETIME",
        "BYTEA": "BLOB",
        "JSON": "JSON",
        "JSONB": "JSON",
    }

    pg_map = {
        "INT": "INTEGER",
        "INTEGER": "INTEGER",
        "BIGINT": "BIGINT",
        "SMALLINT": "SMALLINT",
        "TINYINT": "SMALLINT",
        "BIT": "BOOLEAN",
        "BOOLEAN": "BOOLEAN",
        "TEXT": "TEXT",
        "LONGTEXT": "TEXT",
        "MEDIUMTEXT": "TEXT",
        "VARCHAR": "VARCHAR(255)",
        "CHAR": "CHAR(1)",
        "DECIMAL": "NUMERIC(10,2)",
        "NUMERIC": "NUMERIC(10,2)",
        "FLOAT": "REAL",
        "DOUBLE": "DOUBLE PRECISION",
        "DATETIME": "TIMESTAMP",
        "TIMESTAMP": "TIMESTAMP",
        "BLOB": "BYTEA",
        "JSON": "JSONB",
    }

    if dialect == "mysql":
        for key, val in mysql_map.items():
            if key in dt:
                return val
    elif dialect == "postgresql":
        for key, val in pg_map.items():
            if key in dt:
                return val

    return dt

def build_schema_block(table_def: Dict[str, Any]) -> Dict[str, Any]:
    """Zabalí definíciu tabuľky do bloku 'schema', ktorý Migration Tool očakáva."""
    return {
        "tables": [table_def]
    }


def build_options_block(
    dialect: str,
    target_schema_name: str,
) -> Dict[str, Any]:
    """Vytvorí blok 'options' pre Migration Tool."""
    return {
        "dialect": dialect,
        "target_schema_name": target_schema_name,
        "wrap_in_transaction": True,
        "include_safe_delete_comment": True,
        "include_header_comment": True,
        "statement_terminator": ";",
    }


# ---------------------------------------------------------------------------
# GUI aplikácia (customtkinter)
# ---------------------------------------------------------------------------

class MigrationConfigApp(ctk.CTk):
    """Hlavné okno GUI aplikácie."""

    IGNORE_LABEL = "<ignorovať>"

    def __init__(self) -> None:
        super().__init__()

        ctk.set_appearance_mode("System")
        ctk.set_default_color_theme("blue")

        self.title("Migration Tool – JSON Config Generator")
        self.geometry("1100x700")

        # uložené schémy po načítaní z DB
        self.source_table_def: Dict[str, Any] | None = None
        self.target_table_def: Dict[str, Any] | None = None

        # mapping UI – zoznam (source_col_name, combobox_widget)
        self.mapping_widgets: List[Tuple[str, ctk.CTkComboBox]] = []

        self._build_ui()

    # ---------- UI Aufbau ----------

    def _build_ui(self) -> None:
        main_frame = ctk.CTkFrame(self)
        main_frame.pack(fill="both", expand=True, padx=20, pady=20)

        title_label = ctk.CTkLabel(
            main_frame,
            text="Migration Tool – generátor JSON konfigurácie s ručným mapovaním",
            font=ctk.CTkFont(size=20, weight="bold"),
        )
        title_label.pack(pady=(10, 15))

        top_frame = ctk.CTkFrame(main_frame)
        top_frame.pack(fill="x", expand=False, padx=10, pady=(0, 10))

        # SOURCE
        source_frame = ctk.CTkFrame(top_frame)
        source_frame.pack(side="left", fill="both", expand=True, padx=(0, 10), pady=10)

        source_label = ctk.CTkLabel(
            source_frame,
            text="Zdrojová databáza (source – stará tabuľka)",
            font=ctk.CTkFont(size=14, weight="bold"),
        )
        source_label.grid(row=0, column=0, columnspan=2, pady=(10, 10), sticky="w")

        self.src_host = self._add_labeled_entry(
            parent=source_frame, row=1,
            label_text="Host (IP / server):", placeholder="localhost",
        )
        self.src_port = self._add_labeled_entry(
            parent=source_frame, row=2,
            label_text="Port:", placeholder="5432",
        )
        self.src_db = self._add_labeled_entry(
            parent=source_frame, row=3,
            label_text="Databáza:", placeholder="source_db",
        )
        self.src_user = self._add_labeled_entry(
            parent=source_frame, row=4,
            label_text="Používateľ:", placeholder="postgres",
        )
        self.src_password = self._add_labeled_entry(
            parent=source_frame, row=5,
            label_text="Heslo:", placeholder="", show="*",
        )
        self.src_schema = self._add_labeled_entry(
            parent=source_frame, row=6,
            label_text="Schéma:", placeholder="public",
        )
        self.src_table = self._add_labeled_entry(
            parent=source_frame, row=7,
            label_text="Tabuľka:", placeholder="employees_old",
        )

        for i in range(2):
            source_frame.grid_columnconfigure(i, weight=1)

        # TARGET
        target_frame = ctk.CTkFrame(top_frame)
        target_frame.pack(side="left", fill="both", expand=True, padx=(10, 0), pady=10)

        target_label = ctk.CTkLabel(
            target_frame,
            text="Cieľová databáza (target – nová tabuľka)",
            font=ctk.CTkFont(size=14, weight="bold"),
        )
        target_label.grid(row=0, column=0, columnspan=2, pady=(10, 10), sticky="w")

        self.tgt_host = self._add_labeled_entry(
            parent=target_frame, row=1,
            label_text="Host (IP / server):", placeholder="localhost",
        )
        self.tgt_port = self._add_labeled_entry(
            parent=target_frame, row=2,
            label_text="Port:", placeholder="5432",
        )
        self.tgt_db = self._add_labeled_entry(
            parent=target_frame, row=3,
            label_text="Databáza:", placeholder="target_db",
        )
        self.tgt_user = self._add_labeled_entry(
            parent=target_frame, row=4,
            label_text="Používateľ:", placeholder="postgres",
        )
        self.tgt_password = self._add_labeled_entry(
            parent=target_frame, row=5,
            label_text="Heslo:", placeholder="", show="*",
        )
        self.tgt_schema = self._add_labeled_entry(
            parent=target_frame, row=6,
            label_text="Schéma:", placeholder="public",
        )
        self.tgt_table = self._add_labeled_entry(
            parent=target_frame, row=7,
            label_text="Tabuľka:", placeholder="employees_new",
        )

        for i in range(2):
            target_frame.grid_columnconfigure(i, weight=1)

        # Spodný panel – dialekt, výstup, tlačidlá
        bottom_frame = ctk.CTkFrame(main_frame)
        bottom_frame.pack(fill="x", expand=False, padx=10, pady=(10, 10))

        dialect_label = ctk.CTkLabel(bottom_frame, text="Dialekt pre Migration Tool:")
        dialect_label.grid(row=0, column=0, padx=(10, 5), pady=(10, 5), sticky="w")

        self.dialect_combo = ctk.CTkComboBox(
        bottom_frame,
        values=["postgresql", "oracle", "mysql"],
        )

        self.dialect_combo.set("postgresql")
        self.dialect_combo.grid(row=0, column=1, padx=(0, 20), pady=(10, 5), sticky="w")

        out_label = ctk.CTkLabel(bottom_frame, text="Výstupný JSON súbor:")
        out_label.grid(row=1, column=0, padx=(10, 5), pady=(10, 5), sticky="w")

        self.output_entry = ctk.CTkEntry(
            bottom_frame,
            placeholder_text="migration_config.json",
            width=260,
        )
        self.output_entry.grid(row=1, column=1, padx=(0, 5), pady=(10, 5), sticky="w")

        browse_button = ctk.CTkButton(
            bottom_frame,
            text="Vybrať...",
            command=self._choose_output_file,
            width=100,
        )
        browse_button.grid(row=1, column=2, padx=(5, 10), pady=(10, 5), sticky="w")

        load_button = ctk.CTkButton(
            bottom_frame,
            text="Načítať stĺpce z DB",
            command=self.load_schema_clicked,
            width=170,
        )
        load_button.grid(row=0, column=2, padx=(5, 10), pady=(10, 5), sticky="e")

        for i in range(3):
            bottom_frame.grid_columnconfigure(i, weight=0)
        bottom_frame.grid_columnconfigure(1, weight=1)

        # Mapping frame
        self.mapping_frame = ctk.CTkFrame(main_frame)
        self.mapping_frame.pack(fill="both", expand=True, padx=10, pady=(10, 10))

        self._build_mapping_placeholder()

    def _add_labeled_entry(
        self,
        parent: ctk.CTkFrame,
        row: int,
        label_text: str,
        placeholder: str = "",
        show: str | None = None,
    ) -> ctk.CTkEntry:
        label = ctk.CTkLabel(parent, text=label_text)
        label.grid(row=row, column=0, padx=(10, 5), pady=5, sticky="w")

        entry = ctk.CTkEntry(parent, placeholder_text=placeholder, show=show)
        entry.grid(row=row, column=1, padx=(0, 10), pady=5, sticky="we")

        return entry

    def _choose_output_file(self) -> None:
        filename = filedialog.asksaveasfilename(
            title="Uložiť JSON konfiguráciu",
            defaultextension=".json",
            filetypes=[("JSON súbory", "*.json"), ("Všetky súbory", "*.*")],
        )
        if filename:
            self.output_entry.delete(0, "end")
            self.output_entry.insert(0, filename)

    # ---------- Mapping UI ----------

    def _build_mapping_placeholder(self) -> None:
        """Zobrazí info, kým ešte nemáme načítané stĺpce."""
        for widget in self.mapping_frame.winfo_children():
            widget.destroy()

        info_label = ctk.CTkLabel(
            self.mapping_frame,
            text=(
                "1. Vyplňte pripojenie na source/target DB a názvy tabuliek.\n"
                "2. Kliknite na 'Načítať stĺpce z DB'.\n"
                "3. Potom tu uvidíte zoznam stĺpcov na mapovanie."
            ),
            justify="center",
        )
        info_label.pack(expand=True)

    def _build_mapping_ui(self) -> None:
        """Postaví mapping tabuľku – zoznam source stĺpcov a comboboxy s target stĺpcami."""
        for widget in self.mapping_frame.winfo_children():
            widget.destroy()

        if not self.source_table_def or not self.target_table_def:
            self._build_mapping_placeholder()
            return

        src_cols = [c["name"] for c in self.source_table_def["columns"]]
        tgt_cols = [c["name"] for c in self.target_table_def["columns"]]

        if not src_cols or not tgt_cols:
            info_label = ctk.CTkLabel(
                self.mapping_frame,
                text="Neboli nájdené žiadne stĺpce v jednej z tabuliek.",
            )
            info_label.pack(expand=True)
            return

        header_label = ctk.CTkLabel(
            self.mapping_frame,
            text="Ručné mapovanie stĺpcov (source → target)",
            font=ctk.CTkFont(size=16, weight="bold"),
        )
        header_label.grid(row=0, column=0, columnspan=3, pady=(10, 10), sticky="w")

        src_header = ctk.CTkLabel(
            self.mapping_frame,
            text="Source stĺpec",
            font=ctk.CTkFont(weight="bold"),
        )
        src_header.grid(row=1, column=0, padx=(10, 5), pady=(5, 5), sticky="w")

        arrow_header = ctk.CTkLabel(
            self.mapping_frame,
            text="→",
            font=ctk.CTkFont(weight="bold"),
        )
        arrow_header.grid(row=1, column=1, padx=5, pady=(5, 5))

        tgt_header = ctk.CTkLabel(
            self.mapping_frame,
            text="Target stĺpec (alebo ignorovať)",
            font=ctk.CTkFont(weight="bold"),
        )
        tgt_header.grid(row=1, column=2, padx=(5, 10), pady=(5, 5), sticky="w")

        self.mapping_widgets.clear()

        values_with_ignore = [self.IGNORE_LABEL] + tgt_cols

        for idx, src_name in enumerate(src_cols, start=2):
            lbl = ctk.CTkLabel(self.mapping_frame, text=src_name)
            lbl.grid(row=idx, column=0, padx=(10, 5), pady=3, sticky="w")

            arrow = ctk.CTkLabel(self.mapping_frame, text="→")
            arrow.grid(row=idx, column=1, padx=5, pady=3)

            combo = ctk.CTkComboBox(
                self.mapping_frame,
                values=values_with_ignore,
                width=220,
            )

            # predvyplň ak existuje target stĺpec s rovnakým názvom
            if src_name in tgt_cols:
                combo.set(src_name)
            else:
                combo.set(self.IGNORE_LABEL)

            combo.grid(row=idx, column=2, padx=(5, 10), pady=3, sticky="w")

            self.mapping_widgets.append((src_name, combo))

        # tlačidlo generovania
        generate_button = ctk.CTkButton(
            self.mapping_frame,
            text="Vygenerovať JSON konfiguráciu",
            command=self.generate_config_clicked,
            height=36,
            font=ctk.CTkFont(size=13, weight="bold"),
        )
        generate_button.grid(
            row=len(src_cols) + 3,
            column=0,
            columnspan=3,
            padx=10,
            pady=(15, 10),
            sticky="e",
        )

        for col in range(3):
            self.mapping_frame.grid_columnconfigure(col, weight=0)
        self.mapping_frame.grid_columnconfigure(2, weight=1)

    # ---------- Akcie ----------

    def load_schema_clicked(self) -> None:
        """Načíta schémy tabuliek zo source/target DB a zobrazí mapping UI."""
        try:
            src_host = self.src_host.get().strip() or "localhost"
            src_port = self.src_port.get().strip() or "5432"
            src_db = self.src_db.get().strip()
            src_user = self.src_user.get().strip()
            src_password = self.src_password.get().strip()
            src_schema = self.src_schema.get().strip() or "public"
            src_table = self.src_table.get().strip()

            tgt_host = self.tgt_host.get().strip() or "localhost"
            tgt_port = self.tgt_port.get().strip() or "5432"
            tgt_db = self.tgt_db.get().strip()
            tgt_user = self.tgt_user.get().strip()
            tgt_password = self.tgt_password.get().strip()
            tgt_schema = self.tgt_schema.get().strip() or "public"
            tgt_table = self.tgt_table.get().strip()

            mandatory = [
                ("Zdrojová databáza", src_db),
                ("Zdrojový používateľ", src_user),
                ("Zdrojová tabuľka", src_table),
                ("Cieľová databáza", tgt_db),
                ("Cieľový používateľ", tgt_user),
                ("Cieľová tabuľka", tgt_table),
            ]
            missing = [name for name, value in mandatory if not value]
            if missing:
                messagebox.showerror(
                    "Chýbajúce údaje",
                    "Vyplňte prosím tieto polia:\n- " + "\n- ".join(missing),
                    parent=self,
                )
                return

            dialect = self.dialect_combo.get().strip() or "postgresql"

            # Default: PostgreSQL
            driver = "postgresql+psycopg2"

            # Pre e-shopy na MySQL / MariaDB:
            if dialect == "mysql":
                driver = "mysql+pymysql"

            src_url = (
                f"{driver}://{src_user}:{src_password}"
                f"@{src_host}:{src_port}/{src_db}"
            )
            tgt_url = (
                f"{driver}://{tgt_user}:{tgt_password}"
                f"@{tgt_host}:{tgt_port}/{tgt_db}"
            )


            src_engine = create_db_engine(src_url)
            tgt_engine = create_db_engine(tgt_url)

            try:
                self.source_table_def = introspect_table(
                    src_engine, schema_name=src_schema, table_name=src_table
                )
                self.target_table_def = introspect_table(
                    tgt_engine, schema_name=tgt_schema, table_name=tgt_table
                )
            finally:
                src_engine.dispose()
                tgt_engine.dispose()

            self._build_mapping_ui()

        except Exception as exc:
            messagebox.showerror(
                "Chyba pri načítaní schémy",
                f"Nepodarilo sa načítať stĺpce:\n{exc}",
                parent=self,
            )

    def generate_config_clicked(self) -> None:
        """Prečíta mapping z UI, zloží JSON a uloží ho do súboru."""
        if not self.source_table_def or not self.target_table_def:
            messagebox.showerror(
                "Chýbajú údaje",
                "Najprv načítajte schémy tabuliek tlačidlom 'Načítať stĺpce z DB'.",
                parent=self,
            )
            return

        try:
            output_path = self.output_entry.get().strip() or "migration_config.json"

            dialect = self.dialect_combo.get().strip() or "postgresql"
            tgt_schema = self.tgt_schema.get().strip() or "public"
            src_table_name = self.source_table_def["name"]
            tgt_table_name = self.target_table_def["name"]

            # postav mappings zo zvolených párov
            mappings: List[Dict[str, Any]] = []
            for src_name, combo in self.mapping_widgets:
                tgt_name = combo.get().strip()
                if not tgt_name or tgt_name == self.IGNORE_LABEL:
                    continue

                transform = {
                    "trim": False,
                    "to_upper": False,
                    "to_lower": False,
                    "custom_sql_expression": None,
                    "default_value_literal": None,
                }

                mappings.append(
                    {
                        "source": {
                            "table": src_table_name,
                            "column": src_name,
                        },
                        "target": {
                            "table": tgt_table_name,
                            "column": tgt_name,
                        },
                        "transform": transform,
                    }
                )

            if not mappings:
                messagebox.showerror(
                    "Žiadne mapovania",
                    "Nie sú zvolené žiadne páry stĺpcov. Vyberte aspoň jedno mapovanie.",
                    parent=self,
                )
                return

            config: Dict[str, Any] = {
                "source_schema": build_schema_block(self.source_table_def),
                "target_schema": build_schema_block(self.target_table_def),
                "mappings": mappings,
                "options": build_options_block(
                    dialect=dialect,
                    target_schema_name=tgt_schema,
                ),
            }

            with open(output_path, "w", encoding="utf-8") as f:
                json.dump(config, f, ensure_ascii=False, indent=2)
                f.write("\n")

            messagebox.showinfo(
                "Hotovo",
                f"JSON konfigurácia bola uložená do:\n{output_path}",
                parent=self,
            )

        except Exception as exc:
            messagebox.showerror(
                "Chyba pri generovaní",
                f"Nepodarilo sa vygenerovať konfiguráciu:\n{exc}",
                parent=self,
            )


def main() -> None:
    app = MigrationConfigApp()
    app.mainloop()


if __name__ == "__main__":
    main()