from median.base import mysql_db
from median.migration import DbMigrator
from median.models import Profil, Rights
from common.models import (
    WebLang,
    WebMenu,
    WebMenuTranslation,
    WebForm,
    WebLogActions,
    WebThresholdSheet,
    WebThresholdParameter,
)
from peewee import DoesNotExist, fn
from playhouse.dataset import DataSet
import logging
import os
import json

from common.models import WebImportRefHeader, WebImportRefLine

logger = logging.getLogger("median.webserver")

DATA_DIR = os.path.join(os.path.dirname(os.path.realpath(__file__)), "data")

DEFAULT_CHARSET = "utf8mb4_0900_ai_ci"


def check_model(table_name, model_name, importfile=None):
    """Check if table exists, if not we creat it from the model"""
    if not mysql_db.table_exists(table_name):
        logger.warning("Table %s does not exists, we create it from %s" % (table_name, model_name))  # noqa
        print("Table %s does not exists, we create it from %s" % (table_name, model_name))  # noqa
        mysql_db.create_tables([model_name])
        mysql_db.close()
        if importfile:
            # kwargs = {'table_names': [table_name]}
            dsdb = DataSet(mysql_db, table_names=[table_name])
            tbl = dsdb[table_name]
            tbl.thaw(format="json", filename=os.path.join(DATA_DIR, importfile))
            print("Import file %s in table %s" % (importfile, table_name))
    else:
        logger.info("Table %s exists" % (table_name,))
        print("Table %s exists" % (table_name,))

    return True


def delete_model(table_name, model_name):
    """
    Delete the table associate to the model
    """
    if mysql_db.table_exists(table_name):
        logger.warning("Delete Table %s from %s" % (table_name, model_name))
        print("Delete Table %s from %s" % (table_name, model_name))
        mysql_db.drop_tables([model_name])
        mysql_db.close()
    return True


def sync_menu(table_name, model_name, importfile=None):
    """Check if table exists, if not we creat it from the model"""
    menu_ids = []
    if mysql_db.table_exists(table_name):
        if importfile:
            # we loop around all value, and replace all fields
            js = json.load(open(os.path.join(DATA_DIR, importfile)))
            for j in js:
                menu_ids.append(j["id"])
                try:
                    mnu = WebMenu.get(WebMenu.pk == j["id"])
                    mnu.name = j["name"]
                    mnu.code = j["code"]
                    mnu.icon = j["icon"]
                    mnu.parent = j["parent_id"]
                    mnu.sequence = j["seq_order"]
                    mnu.url = j["url"]
                    mnu.anchor = j["anchor"]
                    mnu.save()
                    print("Update menu %s (%s)" % (j["name"], j["id"]))
                except DoesNotExist:
                    mnu = WebMenu(
                        pk=j["id"],
                        name=j["name"],
                        code=j["code"],
                        icon=j["icon"],
                        parent=j["parent_id"],
                        sequence=j["seq_order"],
                        url=j["url"],
                        anchor=j["anchor"],
                    )
                    mnu.save(force_insert=True)
                    print("Create menu %s (%s)" % (mnu.name, mnu.pk))

            if menu_ids:
                print("Delete old menus")
                q = WebMenu.delete().where(WebMenu.pk.not_in(menu_ids))
                q.execute()
    else:
        logger.info("Table does not exists %s" % (table_name,))
        print("Table does not exists %s" % (table_name,))


def sync_profil():
    """Scan all menus and add on profil for ADMIN and ECO-DEX, DEENOVA"""
    print("Sync profil")
    for prf in ["ECO-DEX", "DEENOVA", "ADMIN", "TECH"]:
        print("-> Profil: %s" % prf)
        menus = Rights.select(Rights.ressource, Rights.libelle).where(fn.LOWER(Rights.libelle).startswith("medianweb"))
        for mnu in menus:
            try:
                p = Profil.get(Profil.profil == prf, Profil.ressource == mnu.ressource)
                p.visu = 1
                p.edit = 1
                p.save()
            except DoesNotExist:
                p = Profil(profil=prf, ressource=mnu.ressource, visu=1, edit=1)
                p.save()

    print("Sync profil done")


def median_upgrade(force=False):
    """Call the python median Method to add fields on existing model"""
    mig = DbMigrator(mysql_db)
    mig.migrate(force)
    return True


def medianweb_upgrade(force=False):
    dbi = median_information()
    print("Database information\nHost: %(server)s\nPort: %(port)s\nName: %(name)s\nUser: %(user)s\n-----------" % dbi)
    if force:
        print("Force mode detected, delete tables before recreate\n-----------")
        delete_model("web_form_i18n", WebForm)
        delete_model("web_menu_i18n", WebMenuTranslation)
        delete_model("web_menu", WebMenu)
        delete_model("web_lang", WebLang)

    check_model("web_lang", WebLang, "web_lang.json")
    check_model("web_menu", WebMenu, "web_menu.json")
    sync_menu("web_menu", WebMenu, "web_menu.json")
    check_model("web_menu_i18n", WebMenuTranslation)
    check_model("web_form_i18n", WebForm)
    check_model("web_log_actions", WebLogActions)
    check_model("web_threshold_sheet", WebThresholdSheet)
    check_model("web_threshold_parameter", WebThresholdParameter)
    check_model("web_importref_header", WebImportRefHeader)
    check_model("web_importref_lines", WebImportRefLine)
    mig = DbMigrator(mysql_db)
    mig.check_field("web_lang", "enable", "ALTER TABLE web_lang ADD COLUMN enable tinyint(1) DEFAULT '0';")
    return True


def median_information():
    """Display Database informations"""
    dbinfo = {
        "name": os.environ.get("MEDIAN_DB_NAME", "unknown"),
        "server": os.environ.get("MEDIAN_DB_HOST", "unknown"),
        "user": os.environ.get("MEDIAN_DB_USER", "unknown"),
        "port": os.environ.get("MEDIAN_DB_PORT", "unknown"),
    }
    return dbinfo


def mysql_server_version():
    if mysql_db.is_closed():
        mysql_db.connect()
        version = mysql_db.server_version
        mysql_db.close()
    else:
        version = mysql_db.server_version
    return version


def convert_table_charset(table_name, database):
    """Convert a table to utf8mb4_0900_ai_ci charset if needed"""
    try:
        # Get table charset info
        collation = mysql_db.execute_sql(
            """
            SELECT table_collation
            FROM information_schema.tables
            WHERE table_schema = %s
            AND table_name = %s
        """,
            [database, table_name],
        ).fetchone()[0]

        if collation != DEFAULT_CHARSET:
            msg = f"Converting table {table_name} to {DEFAULT_CHARSET}"
            logger.info(msg)
            print(msg)
            mysql_db.execute_sql(
                """
                ALTER TABLE `{}`
                CONVERT TO CHARACTER SET utf8mb4
                COLLATE {}
            """.format(table_name, DEFAULT_CHARSET)
            )

        # Convert columns after table ?

    except Exception as e:
        msg = f"Error converting table {table_name}: {str(e)}"
        logger.error(msg)
        print(f"-> Error: {msg}")
        return False

    return True


def convert_charset():
    """Convert all tables and columns to utf8mb4_0900_ai_ci charset"""
    try:
        # Get database name
        database = mysql_db.execute_sql("SELECT DATABASE()").fetchone()[0]

        # Get all tables
        tables = mysql_db.execute_sql(
            """
            SELECT table_name
            FROM information_schema.tables
            WHERE table_schema = %s
            AND table_type = 'BASE TABLE'
        """,
            [database],
        ).fetchall()

        with mysql_db.connection_context():
            total_tables = len(tables)

            for idx, table in enumerate(tables, 1):
                table_name = table[0]
                print(f"Processing table {idx}/{total_tables}: {table_name}")
                convert_table_charset(table_name, database)

    except Exception as e:
        logger.error(f"Error in charset conversion: {str(e)}")
        return False

    return True
