"""MySQL logic."""

import subprocess
from typing import Dict

import pymysql

import config


def _get_connection(db_credentials: Dict[str, str]) -> pymysql.Connection:
    """Return a database connection from the given credentials.

    Args:
        db_credentials: Database credentials containing host, username, password.

    Returns:
        MySQL database connection object.
    """
    return pymysql.connect(
        host=db_credentials["host"],
        user=db_credentials["username"],
        password=db_credentials["password"],
    )


def get_database_ddl(credentials: Dict[str, str]) -> str:
    """Get the DDL for a MySQL database and its tables.

    Args:
        credentials: Database credentials containing host, username, password.

    Returns:
        Path to the output file containing the DDL statements.
    """
    connection = _get_connection(credentials)
    with connection:
        with connection.cursor() as cursor:
            cursor.execute("SHOW DATABASES;")
            databases = [
                row[0]
                for row in cursor.fetchall()
                if row[0] not in config.OMITTED_DATABASES["mysql"]
            ]
    output_file = "/tmp/001_schema.sql"

    cmd = [
        "mysqldump",
        f"--host={credentials['host']}",
        f"--user={credentials['username']}",
        f"--password={credentials['password']}",
        "--single-transaction",
        "--no-data",
        "--routines",
        "--events",
        "--triggers",
        "--databases",
    ]
    cmd.extend(databases)

    with open(output_file, "w") as file:
        subprocess.run(cmd, stdout=file, check=True)

    return output_file