import pymysql
import json
import os
import time

try:
    from .validation import is_valid_db_identifier
except ImportError:
    from validation import is_valid_db_identifier


def _load_mysql_config():
    paths = [
        os.path.join(os.path.dirname(__file__), 'instance/mysql_config.json'),
        "/opt/limristem-web/instance/mysql_config.json"
    ]
    for config_path in paths:
        if os.path.exists(config_path):
            with open(config_path, 'r') as f:
                return json.load(f), None
    return None, "MySQL config not found"


def _quote_identifier(name):
    return f"`{name}`"

def get_connection():
    conf, err = _load_mysql_config()
    if not conf:
        return None, err

    try:
        connect_kwargs = dict(
            host=conf.get('host', '127.0.0.1'),
            port=int(conf.get('port', 3306)),
            user=conf['user'],
            password=conf['password'],
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        if conf.get('socket'):
            connect_kwargs['unix_socket'] = conf['socket']
        conn = pymysql.connect(**connect_kwargs)
        return conn, None
    except Exception as e:
        return None, str(e)

def list_databases():
    conn, err = get_connection()
    if not conn: return [], err
    
    try:
        with conn.cursor() as cursor:
            cursor.execute("SHOW DATABASES")
            dbs = [d['Database'] for d in cursor.fetchall() if d['Database'] not in ('information_schema', 'mysql', 'performance_schema', 'sys')]
        conn.close()
        return dbs, None
    except Exception as e:
        return [], str(e)

def list_users():
    conn, err = get_connection()
    if not conn: return [], err
    
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT User, Host FROM mysql.user WHERE User != 'root' AND User != ''")
            users = cursor.fetchall()
        conn.close()
        return users, None
    except Exception as e:
        return [], str(e)

def create_database(name):
    conn, err = get_connection()
    if not conn: return False, err
    
    name = (name or '').strip()
    if not is_valid_db_identifier(name):
        return False, "Invalid DB name"
    
    try:
        with conn.cursor() as cursor:
            cursor.execute(f"CREATE DATABASE {_quote_identifier(name)}")
        conn.commit()
        conn.close()
        return True, "Database created"
    except Exception as e:
        return False, str(e)

def create_user(username, password):
    conn, err = get_connection()
    if not conn: return False, err
    
    username = (username or '').strip()
    if not is_valid_db_identifier(username):
        return False, "Invalid username"
    if not password:
        return False, "Password is required"
    
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                f"CREATE USER '{username}'@'localhost' IDENTIFIED BY %s",
                (password,)
            )
        conn.commit()
        conn.close()
        return True, "User created"
    except Exception as e:
        return False, str(e)

def grant_privileges(dbname, username):
    conn, err = get_connection()
    if not conn: return False, err
    
    dbname = (dbname or '').strip()
    username = (username or '').strip()
    if not is_valid_db_identifier(dbname) or not is_valid_db_identifier(username):
        return False, "Invalid inputs"
    
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                f"GRANT ALL PRIVILEGES ON {_quote_identifier(dbname)}.* TO '{username}'@'localhost'"
            )
            cursor.execute("FLUSH PRIVILEGES")
        conn.commit()
        conn.close()
        return True, "Privileges granted"
    except Exception as e:
        return False, str(e)

import subprocess

def install_mariadb_system():
    """Installs MariaDB Server via apt-get and secures it."""
    try:
        # Install
        subprocess.run(["apt-get", "update"], check=True)
        subprocess.run(["apt-get", "install", "-y", "mariadb-server"], check=True)
        
        # Wait for MySQL to be ready
        max_retries = 30
        for i in range(max_retries):
            try:
                subprocess.run(["mysql", "-e", "SELECT 1"], check=True, capture_output=True)
                break
            except subprocess.CalledProcessError:
                if i == max_retries - 1:
                     raise
                time.sleep(1)

        # Secure
        # Generate Password
        password = subprocess.run(["openssl", "rand", "-base64", "12"], capture_output=True, text=True).stdout.strip()
        
        # Run SQL commands to secure
        # 1. Set Password (using socket auth which works initially)
        # Use ALTER USER for compatibility with modern MariaDB on current Debian releases.
        # Note: This might disable socket auth, so subsequent commands need credentials.
        # Use pymysql with socket authentication to avoid leaking password in process list
        try:
             # On Debian, socket is usually at /run/mysqld/mysqld.sock
             socket_path = "/run/mysqld/mysqld.sock"
             if not os.path.exists(socket_path):
                 socket_path = "/var/run/mysqld/mysqld.sock"
             
             conn = pymysql.connect(user='root', unix_socket=socket_path)
             with conn.cursor() as cursor:
                 cursor.execute("ALTER USER 'root'@'localhost' IDENTIFIED BY %s", (password,))
             conn.commit()
             conn.close()
        except Exception:
             # Fallback to subprocess with stdin if pymysql fails (though pymysql is preferred)
             # Passing via stdin avoids leaking password
             # Validate password charset to prevent SQL injection
             import re
             if not re.match(r'^[A-Za-z0-9+/=]+$', password):
                 raise ValueError("Generated password contains unsafe characters for SQL interpolation")
             p = subprocess.Popen(["mysql"], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE, text=True)
             stdout, stderr = p.communicate(input=f"ALTER USER 'root'@'localhost' IDENTIFIED BY '{password}';")
             if p.returncode != 0:
                 raise subprocess.CalledProcessError(p.returncode, "mysql", output=stdout, stderr=stderr)
        
        # 2. Perform Cleanup (using new credentials via pymysql)
        # This avoids putting password in process list via subprocess arguments
        conn = pymysql.connect(host='127.0.0.1', user='root', password=password)
        with conn.cursor() as cursor:
            cursor.execute("DELETE FROM mysql.user WHERE User='';")
            cursor.execute("DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');")
            cursor.execute("DROP DATABASE IF EXISTS test;")
            cursor.execute("DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';")
            cursor.execute("FLUSH PRIVILEGES;")
        conn.commit()
        conn.close()
            
        # Configure bind-address
        conf_file = "/etc/mysql/mariadb.conf.d/50-server.cnf"
        if os.path.exists(conf_file):
            subprocess.run(["sed", "-i", "s/^bind-address.*/bind-address = 127.0.0.1/", conf_file], check=True)
            subprocess.run(["systemctl", "restart", "mariadb"], check=True)
            
        # Save Config
        configure_mysql_connection(password)
        
        return True, "MariaDB Installed", password
    except subprocess.CalledProcessError as e:
        return False, f"Install failed: {e}", None
    except Exception as e:
        return False, str(e), None

def configure_mysql_connection(password):
    """Saves the MySQL configuration to json."""
    config = {
        "host": "127.0.0.1",
        "user": "root",
        "password": password
    }
    
    # Try finding instance path relative to app root
    # Ideally passed from app context, but for manager simplicity we guess standard locations
    paths = [
        os.path.join(os.path.dirname(__file__), 'instance/mysql_config.json'),
        "/opt/limristem-web/instance/mysql_config.json"
    ]
    
    saved = False
    for path in paths:
        try:
            os.makedirs(os.path.dirname(path), exist_ok=True)
            with open(path, 'w') as f:
                json.dump(config, f)
            # Secure the file (600)
            os.chmod(path, 0o600)
            saved = True
        except:
            pass
            
    return saved