"""MySQL logic test module."""

import os
import subprocess
import tempfile
from unittest.mock import MagicMock, patch

import pymysql
import pytest

import config
from src.logic import mysql


@pytest.fixture()
def mock_db_credentials():
    """Return mock database credentials."""
    return {
        "host": "test-mysql-host.amazonaws.com",
        "username": "testuser",
        "password": "testpassword",
    }


@pytest.fixture()
def mock_databases():
    """Return mock list of databases."""
    return ["app_db", "user_db", "analytics_db"]


@pytest.fixture()
def mock_all_databases():
    """Return mock list of all databases including system ones."""
    return [
        "information_schema",
        "mysql",
        "performance_schema",
        "sys",
        "app_db",
        "user_db",
    ]


@pytest.fixture()
def temp_workdir():
    """Create a temporary workdir and chdir into it, reverting after."""
    original_cwd = os.getcwd()
    with tempfile.TemporaryDirectory() as tmp_dir:
        os.chdir(tmp_dir)
        try:
            yield tmp_dir
        finally:
            os.chdir(original_cwd)


def test_get_connection_success(mock_db_credentials):
    """Test successful database connection."""
    mock_connection = MagicMock()

    with patch("pymysql.connect", return_value=mock_connection) as mock_connect:
        result = mysql._get_connection(mock_db_credentials)

        mock_connect.assert_called_once_with(
            host="test-mysql-host.amazonaws.com",
            user="testuser",
            password="testpassword",
        )
        assert result == mock_connection


def test_get_connection_failure(mock_db_credentials):
    """Test database connection failure."""
    with patch("pymysql.connect", side_effect=pymysql.Error("Connection failed")):
        with pytest.raises(pymysql.Error, match="Connection failed"):
            mysql._get_connection(mock_db_credentials)


def test_get_database_ddl_success(
    mock_db_credentials, mock_all_databases, monkeypatch, temp_workdir
):
    """Test successful database DDL extraction."""
    # Mock the connection and cursor
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [(db,) for db in mock_all_databases]

    mock_connection = MagicMock()
    mock_connection.cursor.return_value.__enter__ = MagicMock(
        return_value=mock_cursor
    )
    mock_connection.cursor.return_value.__exit__ = MagicMock(return_value=None)
    mock_connection.__enter__ = MagicMock(return_value=mock_connection)
    mock_connection.__exit__ = MagicMock(return_value=None)

    # Mock config to exclude system databases
    monkeypatch.setattr(
        config,
        "OMITTED_DATABASES",
        {
            "mysql": [
                "information_schema",
                "mysql",
                "performance_schema",
                "sys",
            ],
            "postgresql": config.OMITTED_DATABASES.get("postgresql", []),
        },
    )

    with (
        patch("pymysql.connect", return_value=mock_connection),
        patch("subprocess.run") as mock_subprocess,
    ):
        result = mysql.get_database_ddl(mock_db_credentials)

        # Verify result
        assert result == "/tmp/001_schema.sql"

        # Verify cursor was used correctly
        mock_cursor.execute.assert_called_once_with("SHOW DATABASES;")

        # Verify subprocess call
        expected_cmd = [
            "mysqldump",
            "--host=test-mysql-host.amazonaws.com",
            "--user=testuser",
            "--password=testpassword",
            "--single-transaction",
            "--no-data",
            "--routines",
            "--events",
            "--triggers",
            "--databases",
            "app_db",
            "user_db",
        ]

        mock_subprocess.assert_called_once()
        call_args = mock_subprocess.call_args
        assert call_args[0][0] == expected_cmd
        assert call_args[1]["check"] is True
        assert "stdout" in call_args[1]


def test_get_database_ddl_no_databases(
    mock_db_credentials, monkeypatch, temp_workdir
):
    """Test database DDL extraction with no user databases."""
    # Mock the connection and cursor to return only system databases
    system_dbs = ["information_schema", "mysql", "performance_schema", "sys"]
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [(db,) for db in system_dbs]

    mock_connection = MagicMock()
    mock_connection.cursor.return_value.__enter__ = MagicMock(
        return_value=mock_cursor
    )
    mock_connection.cursor.return_value.__exit__ = MagicMock(return_value=None)
    mock_connection.__enter__ = MagicMock(return_value=mock_connection)
    mock_connection.__exit__ = MagicMock(return_value=None)

    # Mock config to exclude system databases
    monkeypatch.setattr(
        config,
        "OMITTED_DATABASES",
        {
            "mysql": system_dbs,
            "postgresql": config.OMITTED_DATABASES.get("postgresql", []),
        },
    )

    with (
        patch("pymysql.connect", return_value=mock_connection),
        patch("subprocess.run") as mock_subprocess,
    ):
        result = mysql.get_database_ddl(mock_db_credentials)

        # Verify result
        assert result == "/tmp/001_schema.sql"

        # Verify subprocess called with empty databases list
        expected_cmd = [
            "mysqldump",
            "--host=test-mysql-host.amazonaws.com",
            "--user=testuser",
            "--password=testpassword",
            "--single-transaction",
            "--no-data",
            "--routines",
            "--events",
            "--triggers",
            "--databases",
        ]

        mock_subprocess.assert_called_once()
        call_args = mock_subprocess.call_args
        assert call_args[0][0] == expected_cmd


def test_get_database_ddl_connection_failure(mock_db_credentials):
    """Test database DDL extraction with connection failure."""
    with patch("pymysql.connect", side_effect=pymysql.Error("Connection failed")):
        with pytest.raises(pymysql.Error, match="Connection failed"):
            mysql.get_database_ddl(mock_db_credentials)


def test_get_database_ddl_subprocess_failure(
    mock_db_credentials, mock_all_databases, monkeypatch, temp_workdir
):
    """Test database DDL extraction with subprocess failure."""
    # Mock successful connection
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [(db,) for db in mock_all_databases]

    mock_connection = MagicMock()
    mock_connection.cursor.return_value.__enter__ = MagicMock(
        return_value=mock_cursor
    )
    mock_connection.cursor.return_value.__exit__ = MagicMock(return_value=None)
    mock_connection.__enter__ = MagicMock(return_value=mock_connection)
    mock_connection.__exit__ = MagicMock(return_value=None)

    monkeypatch.setattr(
        config,
        "OMITTED_DATABASES",
        {
            "mysql": [
                "information_schema",
                "mysql",
                "performance_schema",
                "sys",
            ],
            "postgresql": config.OMITTED_DATABASES.get("postgresql", []),
        },
    )

    with (
        patch("pymysql.connect", return_value=mock_connection),
        patch(
            "subprocess.run",
            side_effect=subprocess.CalledProcessError(1, "mysqldump"),
        ),
    ):
        with pytest.raises(subprocess.CalledProcessError):
            mysql.get_database_ddl(mock_db_credentials)


def test_get_database_ddl_file_creation(
    mock_db_credentials, mock_all_databases, monkeypatch, temp_workdir
):
    """Test that the output file is created and has expected content."""
    # Mock the connection
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [(db,) for db in mock_all_databases]

    mock_connection = MagicMock()
    mock_connection.cursor.return_value.__enter__ = MagicMock(
        return_value=mock_cursor
    )
    mock_connection.cursor.return_value.__exit__ = MagicMock(return_value=None)
    mock_connection.__enter__ = MagicMock(return_value=mock_connection)
    mock_connection.__exit__ = MagicMock(return_value=None)

    monkeypatch.setattr(
        config,
        "OMITTED_DATABASES",
        {
            "mysql": ["information_schema", "mysql", "performance_schema", "sys"],
            "postgresql": config.OMITTED_DATABASES.get("postgresql", []),
        },
    )

    # Mock subprocess to write some content to the file
    def mock_subprocess_run(*args, **kwargs):
        if "stdout" in kwargs:
            kwargs["stdout"].write("-- Mock SQL DDL content\n")
            kwargs["stdout"].write("CREATE DATABASE app_db;\n")
        return MagicMock(returncode=0)

    with (
        patch("pymysql.connect", return_value=mock_connection),
        patch("subprocess.run", side_effect=mock_subprocess_run),
    ):
        result = mysql.get_database_ddl(mock_db_credentials)

        # Verify file was created
        assert os.path.exists(result)
        assert result == "/tmp/001_schema.sql"

        # Verify file has content
        with open(result, "r") as f:
            content = f.read()
            assert "-- Mock SQL DDL content" in content
            assert "CREATE DATABASE app_db;" in content


def test_get_database_ddl_custom_config(
    mock_db_credentials, monkeypatch, temp_workdir
):
    """Test database DDL extraction with custom configuration."""
    # Test with different omitted databases
    all_dbs = ["test_db", "custom_system_db", "app_db"]
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [(db,) for db in all_dbs]

    mock_connection = MagicMock()
    mock_connection.cursor.return_value.__enter__ = MagicMock(
        return_value=mock_cursor
    )
    mock_connection.cursor.return_value.__exit__ = MagicMock(return_value=None)
    mock_connection.__enter__ = MagicMock(return_value=mock_connection)
    mock_connection.__exit__ = MagicMock(return_value=None)

    # Custom omitted databases
    monkeypatch.setattr(
        config,
        "OMITTED_DATABASES",
        {
            "mysql": ["custom_system_db"],
            "postgresql": config.OMITTED_DATABASES.get("postgresql", []),
        },
    )

    with (
        patch("pymysql.connect", return_value=mock_connection),
        patch("subprocess.run") as mock_subprocess,
    ):
        mysql.get_database_ddl(mock_db_credentials)

        # Verify only non-omitted databases are included
        call_args = mock_subprocess.call_args[0][0]
        assert "test_db" in call_args
        assert "app_db" in call_args
        assert "custom_system_db" not in call_args