import os
import logging
import tempfile
from median.views import RawConfig
from typing import List, Tuple
from median.models import Service
from median.database import mysql_db


logger = logging.getLogger("median")
REPORT_PREFIX = "REPORT : "


def log_and_print(log_type, message, warning=False):
    log_method = getattr(logger, log_type.lower(), None)
    if log_method is None:
        print(f"Warning: Unknown log type '{log_type}', using default 'error'")
        log_method = logger.error

    log_method(f"{REPORT_PREFIX}{message}")
    if warning:
        print(f"\033[91m{REPORT_PREFIX}{message}\033[0m")
    else:
        print(f"{REPORT_PREFIX}{message}")


def report_entry_point():
    # Call all functions here

    # Check all directories
    print("CHECK_DIRECTORIES".center(40, "="))
    check_directories()

    # Check database tables
    print("CHECK_TABLES".center(40, "="))
    check_tables()

    # Check database views
    print("CHECK_VIEWS".center(40, "="))
    check_views()

    # Check database triggers
    print("CHECK_TRIGGERS".center(40, "="))
    check_triggers()

    # Check database events
    print("CHECK_EVENTS".center(40, "="))
    check_events()


def check_views() -> None:
    """
    Verify the existence and structure of important database views
    """
    try:
        # Get all views from current database
        cursor = mysql_db.execute_sql(
            """
            SELECT TABLE_NAME, CHECK_OPTION, IS_UPDATABLE, DEFINER
            FROM information_schema.VIEWS
            WHERE TABLE_SCHEMA = DATABASE()
        """
        )

        found_views = set()
        for view_name, check_option, is_updatable, definer in cursor.fetchall():
            found_views.add(view_name)

            # Check if the view can be queried
            try:
                view_cursor = mysql_db.execute_sql(f"SELECT * FROM {view_name} LIMIT 1")
                view_cursor.fetchall()
                log_and_print("info", f"View '{view_name}' [{definer}] is queryable")
            except Exception as e:
                log_and_print("error", f"View '{view_name}' [{definer}] query error: {str(e)}", warning=True)

    except Exception as e:
        log_and_print("error", f"Database error while checking views: {str(e)}", warning=True)


def check_triggers() -> None:
    """
    List and test database triggers in the current database
    """
    try:
        # Get all triggers from current database
        cursor = mysql_db.execute_sql(
            """
            SELECT
                TRIGGER_NAME,
                EVENT_MANIPULATION,
                EVENT_OBJECT_TABLE,
                ACTION_TIMING,
                DEFINER
            FROM information_schema.TRIGGERS
            WHERE TRIGGER_SCHEMA = DATABASE()
            ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING
        """
        )

        found_triggers = []
        tables_with_triggers = set()

        for trigger_name, event, table, timing, definer in cursor.fetchall():
            found_triggers.append(
                {"name": trigger_name, "event": event, "table": table, "timing": timing, "definer": definer}
            )
            tables_with_triggers.add(table)

        if found_triggers:
            log_and_print("info", f"Found {len(found_triggers)} triggers across {len(tables_with_triggers)} tables")

            for table in sorted(tables_with_triggers):
                table_triggers = [t for t in found_triggers if t["table"] == table]
                log_and_print("info", f"Table '{table}' has {len(table_triggers)} trigger(s)")
        else:
            log_and_print("warning", "No triggers found in the database")

    except Exception as e:
        log_and_print("error", f"Database error while checking triggers: {str(e)}")


def check_directories() -> None:
    """
    Verify the existence and accessibility of required directories for the application.
    """
    missing_definitions = []
    missing_directories = []
    inaccessible_directories: List[Tuple[str, str]] = []  # key + folder

    raw_config = RawConfig("MEDIANWEB")

    dirs_to_check = [
        "REPORTING_FOLDER",
        "IMAGE_FOLDER",
        "FRONT_DIRECTORY",
        "RESOURCE_DIRECTION",
        "k_eco_dir_fiche",
        "k_eco_dir_fiche_pince",
        "k_eco_dir_fiche_ventouse",
    ]

    for test_dir_name in dirs_to_check:
        check = False

        # search key in environment
        test_directory = os.environ.get(test_dir_name)

        if test_directory is None:
            # else, search key in f_config
            cfg = raw_config.read(test_dir_name)
            test_directory = cfg.value if cfg is not None else None

        if test_directory is None or test_directory == "":
            # the key isn't defined anywhere
            missing_definitions.append(test_dir_name)
        elif not os.path.exists(test_directory):
            # the key is defined but the target doesn't exist
            missing_directories.append(test_directory)
        elif not os.path.isdir(test_directory):
            inaccessible_directories.append((test_dir_name, test_directory))
        else:
            try:
                # Try to list directory contents to verify read access
                os.listdir(test_directory)
                # Try to create a temporary file to verify write access
                with tempfile.NamedTemporaryFile(dir=test_directory, delete=True) as temp_file:
                    temp_file.write(b"test")
                    temp_file.flush()

                # == Final state of the test for this directory, check passed! ==
                check = True
                # ====
            except (PermissionError, OSError, IOError):
                # The directory can't be read or written into
                inaccessible_directories.append((test_dir_name, test_directory))

        if check is True:
            log_and_print("info", f"[{test_dir_name}] CORRECT -> {test_directory}")

    # Error logging
    for item in missing_definitions:
        log_and_print("error", f"[{item}] missing env variables or config")
    for item in inaccessible_directories:
        log_and_print("error", f"[{item[0]}] can't read or write in dir {item[1]}", warning=True)


def check_tables() -> None:
    """
    Verify the table engines, charsets and columns
    """
    tables_to_check = ["f_mag", "f_dest", "f_ref", "f_user"]

    try:
        # Get all tables from current database
        cursor = mysql_db.execute_sql(
            """
            SELECT TABLE_NAME, ENGINE, TABLE_COLLATION
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_TYPE = 'BASE TABLE'
        """
        )

        found_tables = set()
        for table_name, engine, collation in cursor.fetchall():
            found_tables.add(table_name)

            # Check if the table has proper metadata
            if engine is None or collation is None:
                log_and_print(
                    "error", f"Table '{table_name}' has missing metadata (engine: {engine}, collation: {collation})"
                )
                continue

            # Check engine only for specific tables
            if table_name in tables_to_check:
                if engine.upper() != "INNODB":
                    log_and_print(
                        "error", f"Table '{table_name}' uses {engine} engine instead of required InnoDB", warning=True
                    )
                else:
                    log_and_print("info", f"Table '{table_name}' correctly uses InnoDB engine")

            # Check charset for all tables
            if not collation.startswith("utf8"):
                log_and_print(
                    "error",
                    f"Table '{table_name}' uses {collation} charset instead of required UTF8",
                )
            else:
                # Charset OK
                pass

        # Check for missing required tables
        for table in tables_to_check:
            if table not in found_tables:
                log_and_print("error", f"Table '{table}' not found in database")

    except Exception as e:
        log_and_print("error", f"Database error while checking tables: {str(e)}")


def check_config_values():
    """
    Add verifications here to verify some specific values of f_config
    """
    # k_ua_pui & k_ua_transfert (TODO: Add more of these? filter out the numerous obsolete values...)
    values_to_test = ["k_ua_pui", "k_ua_transfert"]

    for ua_value in values_to_test:
        serviceConfig = RawConfig().read(param=ua_value).value
        service = Service.get_or_none(Service.code == serviceConfig)
        if service is None:
            log_and_print("error", f"Bad config value : {ua_value} = {serviceConfig}, no service found")


def check_events() -> None:
    """
    List and verify scheduled events in the database
    """
    try:
        # Check if event scheduler is enabled
        status_cursor = mysql_db.execute_sql("SHOW VARIABLES LIKE 'event_scheduler'")
        event_scheduler_status = status_cursor.fetchone()

        if event_scheduler_status and event_scheduler_status[1].lower() == "on":
            log_and_print("info", "Event scheduler is enabled")
        else:
            status_value = event_scheduler_status[1] if event_scheduler_status else "UNKNOWN"
            log_and_print("warning", f"Event scheduler is not enabled (status: {status_value})")
            log_and_print("info", "Events won't run unless event_scheduler is set to ON")

        # Get all events from current database
        cursor = mysql_db.execute_sql(
            """
            SELECT
                EVENT_NAME,
                EVENT_DEFINITION,
                EVENT_TYPE,
                EXECUTE_AT,
                INTERVAL_VALUE,
                INTERVAL_FIELD,
                STATUS,
                LAST_EXECUTED,
                DEFINER
            FROM information_schema.EVENTS
            WHERE EVENT_SCHEMA = DATABASE()
            """
        )

        events = cursor.fetchall()

        if not events:
            log_and_print("warning", "No scheduled events found in the database")
            return

        # Process each event
        for (
            event_name,
            definition,
            event_type,
            execute_at,
            interval_value,
            interval_field,
            status,
            last_executed,
            definer,
        ) in events:
            # Format the schedule for display
            if event_type == "ONE TIME":
                schedule = f"ONE TIME at {execute_at}"
            else:  # RECURRING
                schedule = f"Every {interval_value} {interval_field}"

            # Format last execution time
            last_exec = last_executed if last_executed else "Never executed"

            log_and_print("info", f"Event '{event_name}' [{status}] by {definer}")
            log_and_print("info", f"  Schedule: {schedule}, Last executed: {last_exec}")

            # Check if event is enabled
            if status != "ENABLED":
                log_and_print("warning", f"  Event '{event_name}' is not enabled (Status: {status})")

    except Exception as e:
        log_and_print("error", f"Database error while checking events: {str(e)}")


def list_event_details(event_name=None):
    """
    Get detailed information about a specific event or all events

    Args:
        event_name (str, optional): Name of specific event to check

    Returns:
        dict: Detailed information about the events
    """
    results = {"success": False, "events": []}

    try:
        # Build the query
        query = """
            SELECT
                EVENT_NAME,
                EVENT_DEFINITION,
                EVENT_TYPE,
                EXECUTE_AT,
                INTERVAL_VALUE,
                INTERVAL_FIELD,
                STATUS,
                CREATED,
                LAST_ALTERED,
                LAST_EXECUTED,
                DEFINER,
                STARTS,
                ENDS,
                ON_COMPLETION
            FROM information_schema.EVENTS
            WHERE EVENT_SCHEMA = DATABASE()
        """

        params = []
        if event_name:
            query += " AND EVENT_NAME = %s"
            params.append(event_name)

        cursor = mysql_db.execute_sql(query, params)
        events = cursor.fetchall()

        if not events:
            return {
                "success": True,
                "events": [],
                "message": f"No events found{' matching ' + event_name if event_name else ''}",
            }

        # Process each event
        for (
            name,
            definition,
            event_type,
            execute_at,
            interval_value,
            interval_field,
            status,
            created,
            altered,
            executed,
            definer,
            starts,
            ends,
            on_completion,
        ) in events:
            # Create event details object
            event_info = {
                "name": name,
                "definition": definition,
                "type": event_type,
                "execute_at": str(execute_at) if execute_at else None,
                "interval": f"{interval_value} {interval_field}" if interval_value and interval_field else None,
                "status": status,
                "created": str(created) if created else None,
                "last_altered": str(altered) if altered else None,
                "last_executed": str(executed) if executed else None,
                "definer": definer,
                "starts": str(starts) if starts else None,
                "ends": str(ends) if ends else None,
                "on_completion": on_completion,
            }

            results["events"].append(event_info)

        results["success"] = True

    except Exception as e:
        results["error"] = str(e)

    return results
