from __future__ import annotations

import os
import re
import shutil
import subprocess
from config import load_config, Config
from logger import get_logger

log = get_logger("actions.database")

# Validation pattern: alphanumeric and underscores only
VALID_NAME_PATTERN = re.compile(r"^[a-zA-Z0-9_]+$")


def escape_mysql_string(val: str) -> str:
    """Escape string literals for safe embedding in SQL statements."""
    return val.replace("\\", "\\\\").replace("'", "''")



def run_mysql_stmt_raw(sql: str, config: Config) -> subprocess.CompletedProcess:
    """Run a SQL statement on MySQL using the CLI with arguments hidden."""
    if not shutil.which("mysql"):
        raise OSError("mysql CLI not found on system")

    env = os.environ.copy()
    if config.mysql_password:
        env["MYSQL_PWD"] = config.mysql_password

    cmd = ["mysql", f"-u{config.mysql_user}"]
    if config.mysql_socket:
        cmd.append(f"--socket={config.mysql_socket}")
    
    cmd.extend(["-N", "-B"])

    return subprocess.run(
        cmd,
        env=env,
        input=sql,
        capture_output=True,
        text=True,
        timeout=10,
        check=False,
    )


def run_mysql_stmt(sql: str, config: Config, error_msg: str) -> None:
    """Run a SQL statement and raise OSError if it fails."""
    res = run_mysql_stmt_raw(sql, config)
    if res.returncode != 0:
        raise OSError(f"{error_msg}: {res.stderr.strip()}")


def db_exists(db_name: str, config: Config) -> bool:
    """Check if a database schema exists on the server."""
    q = f"SHOW DATABASES LIKE '{db_name}';"
    res = run_mysql_stmt_raw(q, config)
    if res.returncode == 0 and res.stdout.strip():
        return True
    return False


def user_exists(username: str, host: str, config: Config) -> bool:
    """Check if a database user exists on the server."""
    q = f"SELECT 1 FROM mysql.user WHERE User='{username}' AND Host='{host}';"
    res = run_mysql_stmt_raw(q, config)
    if res.returncode == 0 and res.stdout.strip():
        return True
    return False


def handle_database_create(payload: dict) -> dict:
    """Create a new logical MySQL database with prefix wolf_."""
    try:
        name = payload.get("name")
        if not name:
            raise ValueError("Database name is required")

        if not VALID_NAME_PATTERN.match(name):
            raise ValueError("Invalid database name: must contain only alphanumeric/underscores")

        db_name = f"wolf_{name}"
        log.info("Attempting to create database: %s", db_name)

        config = load_config()
        if db_exists(db_name, config):
            raise ValueError(f"Database {db_name} already exists")

        sql = f"CREATE DATABASE {db_name} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
        run_mysql_stmt(sql, config, "Failed to create database")

        log.info("Database %s created successfully", db_name)
        return {
            "success": True,
            "message": f"Database {db_name} created successfully",
            "database": db_name,
        }

    except Exception as exc:
        err_msg = str(exc)
        log.error("Database creation failed: %s", err_msg)
        return {
            "success": False,
            "error": err_msg,
        }


def handle_database_drop(payload: dict) -> dict:
    """Drop a logical MySQL database with prefix wolf_."""
    try:
        name = payload.get("name")
        if not name:
            raise ValueError("Database name is required")

        if not name.startswith("wolf_"):
            raise ValueError("Database name must start with 'wolf_' for isolation")

        if not VALID_NAME_PATTERN.match(name):
            raise ValueError("Invalid database name: must contain only alphanumeric/underscores")

        log.info("Attempting to drop database: %s", name)

        config = load_config()
        if not db_exists(name, config):
            raise ValueError(f"Database {name} does not exist")

        sql = f"DROP DATABASE {name};"
        run_mysql_stmt(sql, config, "Failed to drop database")

        log.info("Database %s dropped successfully", name)
        return {
            "success": True,
            "message": f"Database {name} dropped successfully",
            "database": name,
        }

    except Exception as exc:
        err_msg = str(exc)
        log.error("Database dropping failed: %s", err_msg)
        return {
            "success": False,
            "error": err_msg,
        }


def handle_database_user_create(payload: dict) -> dict:
    """Create a database user and assign all privileges on the specified database."""
    try:
        username = payload.get("username")
        password = payload.get("password")
        database = payload.get("database")

        if not username or not password or not database:
            raise ValueError("username, password, and database are required")

        if not username.startswith("wolf_"):
            raise ValueError("Username must start with 'wolf_' for isolation")

        if not VALID_NAME_PATTERN.match(username):
            raise ValueError("Invalid username: must contain only alphanumeric/underscores")

        if not database.startswith("wolf_"):
            raise ValueError("Database name must start with 'wolf_' for isolation")

        if not VALID_NAME_PATTERN.match(database):
            raise ValueError("Invalid database name: must contain only alphanumeric/underscores")

        log.info("Attempting to create database user: %s on %s", username, database)

        config = load_config()
        if user_exists(username, "localhost", config):
            raise ValueError(f"User '{username}'@'localhost' already exists")

        # Pass user and privileges SQL via stdin securely
        escaped_password = escape_mysql_string(password)
        sql = f"CREATE USER '{username}'@'localhost' IDENTIFIED BY '{escaped_password}';\n"
        sql += f"GRANT ALL PRIVILEGES ON {database}.* TO '{username}'@'localhost';"

        run_mysql_stmt(sql, config, "Failed to create database user or grant privileges")

        log.info("Database user %s created successfully", username)
        return {
            "success": True,
            "message": f"Database user {username} created and granted privileges on {database} successfully",
            "username": username,
        }

    except Exception as exc:
        err_msg = str(exc)
        for key in ("password",):
            val = payload.get(key)
            if val:
                err_msg = err_msg.replace(val, "____")
        log.error("Database user creation failed (passwords hidden): %s", err_msg)
        return {
            "success": False,
            "error": err_msg,
        }


def handle_database_user_delete(payload: dict) -> dict:
    """Delete a database user prefixed with wolf_."""
    try:
        username = payload.get("username")
        if not username:
            raise ValueError("username is required")

        if not username.startswith("wolf_"):
            raise ValueError("Username must start with 'wolf_' for isolation")

        if not VALID_NAME_PATTERN.match(username):
            raise ValueError("Invalid username: must contain only alphanumeric/underscores")

        log.info("Attempting to delete database user: %s", username)

        config = load_config()
        if not user_exists(username, "localhost", config):
            raise ValueError(f"User '{username}'@'localhost' does not exist")

        sql = f"DROP USER '{username}'@'localhost';"
        run_mysql_stmt(sql, config, "Failed to drop database user")

        log.info("Database user %s deleted successfully", username)
        return {
            "success": True,
            "message": f"Database user {username} deleted successfully",
            "username": username,
        }

    except Exception as exc:
        err_msg = str(exc)
        log.error("Database user deletion failed: %s", err_msg)
        return {
            "success": False,
            "error": err_msg,
        }


def handle_database_user_reset_password(payload: dict) -> dict:
    """Reset the password of a database user prefixed with wolf_."""
    try:
        username = payload.get("username")
        new_password = payload.get("new_password")

        if not username or not new_password:
            raise ValueError("username and new_password are required")

        if not username.startswith("wolf_"):
            raise ValueError("Username must start with 'wolf_' for isolation")

        if not VALID_NAME_PATTERN.match(username):
            raise ValueError("Invalid username: must contain only alphanumeric/underscores")

        log.info("Attempting to reset password for database user: %s", username)

        config = load_config()
        if not user_exists(username, "localhost", config):
            raise ValueError(f"User '{username}'@'localhost' does not exist")

        escaped_password = escape_mysql_string(new_password)
        sql = f"ALTER USER '{username}'@'localhost' IDENTIFIED BY '{escaped_password}';"
        run_mysql_stmt(sql, config, "Failed to reset password")

        log.info("Password for database user %s reset successfully", username)
        return {
            "success": True,
            "message": f"Password for database user {username} reset successfully",
            "username": username,
        }

    except Exception as exc:
        err_msg = str(exc)
        for key in ("new_password",):
            val = payload.get(key)
            if val:
                err_msg = err_msg.replace(val, "____")
        log.error("Database user password reset failed (passwords hidden): %s", err_msg)
        return {
            "success": False,
            "error": err_msg,
        }


def handle_database_user_update_grants(payload: dict) -> dict:
    """Update database user privileges on a specified database."""
    try:
        username = payload.get("username")
        database = payload.get("database")
        privileges = payload.get("privileges")

        if not username or not database or not privileges:
            raise ValueError("username, database, and privileges are required")

        if not username.startswith("wolf_"):
            raise ValueError("Username must start with 'wolf_' for isolation")

        if not VALID_NAME_PATTERN.match(username):
            raise ValueError("Invalid username: must contain only alphanumeric/underscores")

        if not database.startswith("wolf_"):
            raise ValueError("Database must start with 'wolf_' for isolation")

        if not VALID_NAME_PATTERN.match(database):
            raise ValueError("Invalid database name: must contain only alphanumeric/underscores")

        # Privilege Whitelist Validation
        priv_list = [p.strip().upper() for p in privileges.split(",")]
        valid_privs = {
            "ALL", "ALL PRIVILEGES", "SELECT", "INSERT", "UPDATE", "DELETE",
            "CREATE", "DROP", "INDEX", "ALTER", "CREATE TEMPORARY TABLES",
            "LOCK TABLES", "EXECUTE"
        }
        if not all(p in valid_privs for p in priv_list):
            raise ValueError("Invalid privilege(s) specified")
        priv_str = ", ".join(priv_list)

        log.info("Attempting to update grants for database user: %s on %s to: %s", username, database, priv_str)

        config = load_config()
        if not user_exists(username, "localhost", config):
            raise ValueError(f"User '{username}'@'localhost' does not exist")
        if not db_exists(database, config):
            raise ValueError(f"Database {database} does not exist")

        # 1. Revoke existing grants (ignore error if no grants exist)
        revoke_sql = f"REVOKE ALL PRIVILEGES ON {database}.* FROM '{username}'@'localhost';"
        run_mysql_stmt_raw(revoke_sql, config)

        # 2. Grant new privileges
        grant_sql = f"GRANT {priv_str} ON {database}.* TO '{username}'@'localhost';"
        run_mysql_stmt(grant_sql, config, "Failed to update privileges")

        log.info("Privileges for %s on %s updated successfully to: %s", username, database, priv_str)
        return {
            "success": True,
            "message": f"Privileges for {username} on database {database} updated successfully to: {priv_str}",
            "username": username,
        }

    except Exception as exc:
        err_msg = str(exc)
        log.error("Database user privileges update failed: %s", err_msg)
        return {
            "success": False,
            "error": err_msg,
        }