#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# coding: utf8
import json
from concurrent.futures import ThreadPoolExecutor
from typing import Any

from ffxiv_aku import *

_user_and_db = "d03e984f"
_pass = "evM7L4Vm2HF9Sv6KyTxXKa39oq"
dbtype = "mysql"
# dbtype = "sqlite3"

TABLE_ELEMENTS: dict[str, str] = {
    "ID":                           "INT",
    "0xID":                         "",
    "Name_de":                      "",
    "Name_en":                      "",
    "Name_fr":                      "",
    "Name_ja":                      "",
    "Icon":                         "",
    "Patch":                        "",
    "ClassJobCategory":             "",
    "Level_Equip":                  "INT",
    "Level_Item":                   "INT",
    "IsAdvancedMeldingPermitted":   "BOOLEAN",
    "IsDyeable":                    "BOOLEAN",
    "IsHQ":                         "BOOLEAN",
    "IsUnique":                     "BOOLEAN",
    "IsUntradable":                 "BOOLEAN",
    "ItemSearchCategory":           "BOOLEAN",
    "LimitToHQ":                    "BOOLEAN",
    "MateriaSlotCount":             "INT",
    "Rarity":                       "INT",
    "Phys. Basiswert":              "INT",
    "Verteidigung":                 "INT",
    "Mag. Basiswert":               "INT",
    "Magieabwehr":                  "INT",
    "Konstitution":                 "INT",
    "Stärke":                       "INT",
    "Intelligenz":                  "INT",
    "Frömmigkeit":                  "INT",
    "Willenskraft":                 "INT",
    "Geschick":                     "INT",
    "Unbeugsamkeit":                "INT",
    "Kritischer Treffer":           "INT",
    "Direkter Treffer":             "INT",
    "Entschlossenheit":             "INT",
    "Zaubertempo":                  "INT",
    "Schnelligkeit":                "INT",
    "Kunstfertigkeit":              "INT",
    "Kontrolle":                    "INT",
    "HP":                           "INT",
    "Sammelgeschick":               "INT",
    "Expertise":                    "INT",
    "SP":                           "INT"
}

_NAME_FIELDS = ("Name_de", "Name_en", "Name_fr", "Name_ja")


def _sanitize_cat(cat: str) -> str:
    return cat.replace(" (", "_").replace(")", "").replace("-", "_").replace(" ", "_")


def _get_present_columns(items: list[dict[str, Any]]) -> list[str]:
    """Collect columns that appear in ANY item (directly or under Stats or Name_*)."""
    present = set()
    for it in items:
        nm = it.get("Name")
        stats = it.get("Stats")
        for col in TABLE_ELEMENTS.keys():
            if col in _NAME_FIELDS:
                if isinstance(nm, dict) and nm.get(col.split("_")[1]) is not None:
                    present.add(col)
            else:
                v = it.get(col, None)
                if v is not None:
                    present.add(col)
                elif isinstance(stats, dict) and stats.get(col, None) is not None:
                    present.add(col)
    # Keep original TABLE_ELEMENTS order
    return [c for c in TABLE_ELEMENTS.keys() if c in present]


def insert_into(filename: str, data: list[dict[str, Any]]) -> tuple[str, int]:
    """
    Multi-row INSERT per category with a single, consistent header.
    Missing values per row are emitted as NULL.
    Returns (sql_chunk, max_len_for_TEXT).
    """
    # Precompute 0xID for all rows (so the column is considered "present")
    for item in data:
        item['0xID'] = str(hex(int(item['ID']))[2:]).upper()

    columns = _get_present_columns(data)
    header_str = ", ".join(f"`{c}`" for c in columns)

    rows: list[str] = []
    max_len = 0

    for it in data:
        nm = it.get("Name")
        stats = it.get("Stats")
        vals: list[str] = []

        for col in columns:
            if col in _NAME_FIELDS:
                v = nm.get(col.split("_")[1]) if isinstance(nm, dict) else None
            else:
                v = it.get(col, None)
                if v is None and isinstance(stats, dict):
                    v = stats.get(col, None)

            if v is None:
                vals.append("NULL")
                continue

            # track TEXT width like original (only for present values)
            ln = len(str(v)) + 1
            if ln > max_len:
                max_len = ln

            if isinstance(v, str):
                x = v.replace("'", "''")
                vals.append(f"'{x}'")
            else:
                vals.append(str(v))

        rows.append(f"\t({', '.join(vals)})")

    if not rows:
        return "", max_len

    query = f"INSERT INTO {filename} ({header_str}) VALUES \n" + ",\n".join(rows) + ";\n"
    return query, max_len


def createTable(filename: str, max_len: int) -> str:
    result: str = ""
    header: str = ""
    for key, value in TABLE_ELEMENTS.items():
        if value == "":
            value = f"TEXT({max_len})"
        header += f"`{key}` {value}, "
    header = header[:-2]
    query: str = f'CREATE TABLE `{filename}` ({header});\n'
    result += f'Drop TABLE IF EXISTS {filename};\n'
    result += query
    return result


def _process_category(cat_and_items: tuple[str, list[dict[str, Any]]]) -> tuple[str, str]:
    cat, items = cat_and_items
    filename = _sanitize_cat(cat)
    iresult, max_len = insert_into(filename, items)
    cresult = createTable(filename, max_len)
    return filename, (cresult + iresult + "\n\n\n")


def run() -> None:
    print("[CETSAF] Create Equipment SQL!")
    parts: list[str] = [f"USE {_user_and_db};\n"]

    with open("..//equipment.json", encoding="utf-8") as f:
        data: dict[str, Any] = json.load(f)

    ordered = list(data.items())
    with ThreadPoolExecutor(max_workers=4) as pool:
        futs = [pool.submit(_process_category, pair) for pair in ordered]
        for (orig_cat, _), fut in zip(ordered, futs):
            filename, chunk = fut.result()
            print(filename)
            parts.append(chunk)

    with open("SQL_Equipment.sql", "w", encoding="utf8") as f:
        f.write("".join(parts))

    print("[CETSAF] DONE Create Equipment SQL!")


def executeScriptsFromFile():
    import mysql.connector
    cnx = mysql.connector.connect(user=_user_and_db, password=_pass,
                                  host='w01dc079.kasserver.com', database=_user_and_db)
    cursor = cnx.cursor()
    with open("SQL_Equipment.sql", "r", encoding="utf8") as f:
        sqlFile = "".join(f.readlines())
    for command in sqlFile.split(';'):
        try:
            if command.rstrip() != '':
                cursor.execute(command)
        except Exception as msg:
            print(f"Command skipped: {msg} => {command}")
            asdf
    cnx.commit()
    cursor.close()
    cnx.close()
    print("UPDATED EQUIPMENT ON SERVER")


if __name__ == "__main__":
    run()
    executeScriptsFromFile()