"""PostgreSQL logic test module."""

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

import psycopg
import pytest

from src.logic import postgresql


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


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

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

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


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


def test_get_database_ddl_success(mock_db_credentials, monkeypatch):
    """Test successful per-database schema extraction using pg_dump."""
    # Mock connection and list of databases returned by query
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [("app_db",), ("user_db",)]
    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)

    with tempfile.TemporaryDirectory() as temp_dir:
        original_cwd = os.getcwd()
        os.chdir(temp_dir)
        try:
            with (
                patch("psycopg.connect", return_value=mock_connection),
                patch("subprocess.run") as mock_subprocess,
                patch.dict("os.environ", {}, clear=True),
            ):
                result = postgresql.get_database_ddl(mock_db_credentials)
                assert result == "/tmp/001_schema.sql"

                # Two pg_dump calls expected (one per database)
                assert mock_subprocess.call_count == 2
                calls = [c[0][0] for c in mock_subprocess.call_args_list]
                for cmd in calls:
                    assert cmd[0] == "pg_dump"
                    assert f"--host={mock_db_credentials['host']}" in cmd
                    assert f"--username={mock_db_credentials['username']}" in cmd
                    assert "--schema-only" in cmd
                    assert "--no-owner" in cmd
                    assert "--no-privileges" in cmd
                # Ensure databases included
                flat = " ".join(sum(calls, []))
                assert "app_db" in flat and "user_db" in flat
        finally:
            os.chdir(original_cwd)


def test_get_database_ddl_connection_failure(mock_db_credentials):
    """Test database DDL extraction with connection failure."""
    with patch("psycopg.connect", side_effect=psycopg.Error("Connection failed")):
        with tempfile.TemporaryDirectory() as temp_dir:
            original_cwd = os.getcwd()
            os.chdir(temp_dir)
            try:
                with pytest.raises(psycopg.Error, match="Connection failed"):
                    postgresql.get_database_ddl(mock_db_credentials)

                # Verify empty file was created before re-raising
                assert os.path.exists("/tmp/001_schema.sql")
                with open("/tmp/001_schema.sql", "r") as f:
                    assert f.read() == ""

            finally:
                os.chdir(original_cwd)


def test_get_database_ddl_subprocess_failure(mock_db_credentials):
    """Test one pg_dump failure raises exception."""
    # One user database so a single failing pg_dump triggers exception.
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [("app_db",)]
    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)
    with tempfile.TemporaryDirectory() as temp_dir:
        original_cwd = os.getcwd()
        os.chdir(temp_dir)
        try:
            with (
                patch("psycopg.connect", return_value=mock_connection),
                patch(
                    "subprocess.run",
                    side_effect=subprocess.CalledProcessError(1, "pg_dump"),
                ),
            ):
                with pytest.raises(subprocess.CalledProcessError):
                    postgresql.get_database_ddl(mock_db_credentials)
        finally:
            os.chdir(original_cwd)


def test_get_database_ddl_file_creation(mock_db_credentials):
    """Test output file aggregates multiple pg_dump outputs with headers."""
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [("app_db",), ("user_db",)]
    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)

    def mock_subprocess_run(*args, **kwargs):
        if "stdout" in kwargs:
            # Different content per invocation distinguished by db name
            cmd = args[0]
            db = cmd[-1]
            kwargs["stdout"].write(f"-- Dump for {db}\n")
            kwargs["stdout"].write(f"CREATE DATABASE {db};\n")
        return MagicMock(returncode=0)

    with tempfile.TemporaryDirectory() as temp_dir:
        original_cwd = os.getcwd()
        os.chdir(temp_dir)
        try:
            with (
                patch("psycopg.connect", return_value=mock_connection),
                patch("subprocess.run", side_effect=mock_subprocess_run),
            ):
                result = postgresql.get_database_ddl(mock_db_credentials)
                assert os.path.exists(result)
                with open(result, "r") as f:
                    content = f.read()
                assert "-- ===== Database: app_db =====" in content
                assert "-- ===== Database: user_db =====" in content
                assert "CREATE DATABASE app_db;" in content
                assert "CREATE DATABASE user_db;" in content
        finally:
            os.chdir(original_cwd)


def test_get_database_ddl_environment_variables(mock_db_credentials):
    """Test environment variables passed to each pg_dump invocation."""
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [("app_db",)]
    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)
    existing_env = {"PATH": "/usr/bin", "HOME": "/home/user"}
    with tempfile.TemporaryDirectory() as temp_dir:
        original_cwd = os.getcwd()
        os.chdir(temp_dir)
        try:
            with (
                patch("psycopg.connect", return_value=mock_connection),
                patch("subprocess.run") as mock_subprocess,
                patch.dict("os.environ", existing_env),
            ):
                postgresql.get_database_ddl(mock_db_credentials)
                call_args = mock_subprocess.call_args
                env = call_args[1]["env"]
                assert env["PATH"] == "/usr/bin"
                assert env["HOME"] == "/home/user"
                assert env["PGPASSWORD"] == "testpassword"
        finally:
            os.chdir(original_cwd)


def test_get_database_ddl_command_arguments(mock_db_credentials):
    """Test that pg_dump command arguments are constructed correctly."""
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [("app_db",)]
    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)
    with tempfile.TemporaryDirectory() as temp_dir:
        original_cwd = os.getcwd()
        os.chdir(temp_dir)
        try:
            with (
                patch("psycopg.connect", return_value=mock_connection),
                patch("subprocess.run") as mock_subprocess,
            ):
                postgresql.get_database_ddl(mock_db_credentials)
                cmd = mock_subprocess.call_args[0][0]
                assert cmd[0] == "pg_dump"
                assert f"--host={mock_db_credentials['host']}" in cmd
                assert f"--username={mock_db_credentials['username']}" in cmd
                assert "--schema-only" in cmd
                assert "--no-owner" in cmd
                assert "--no-privileges" in cmd
                # Password should not appear in command
                joined = " ".join(cmd)
                assert "--password" not in joined
                assert mock_db_credentials["password"] not in joined
        finally:
            os.chdir(original_cwd)


def test_get_database_ddl_different_credentials():
    """Test different credentials still applied to pg_dump command and env."""
    credentials = {
        "host": "different-host.example.com",
        "username": "different_user",
        "password": "different_password",
    }
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [("sample_db",)]
    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)
    with tempfile.TemporaryDirectory() as temp_dir:
        original_cwd = os.getcwd()
        os.chdir(temp_dir)
        try:
            with (
                patch("psycopg.connect", return_value=mock_connection),
                patch("subprocess.run") as mock_subprocess,
            ):
                postgresql.get_database_ddl(credentials)
                cmd = mock_subprocess.call_args[0][0]
                env = mock_subprocess.call_args[1]["env"]
                assert f"--host={credentials['host']}" in cmd
                assert f"--username={credentials['username']}" in cmd
                assert env["PGPASSWORD"] == credentials["password"]
        finally:
            os.chdir(original_cwd)


def test_get_database_ddl_encoding_handling(mock_db_credentials):
    """Test UTF-8 content preserved across multiple pg_dump outputs."""
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [("unicode_db",)]
    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)

    def mock_subprocess_run(*args, **kwargs):
        if "stdout" in kwargs:
            kwargs["stdout"].write("-- Unicode header café, naïve\n")
            kwargs["stdout"].write("CREATE DATABASE test_üñíçødé;\n")
        return MagicMock(returncode=0)

    with tempfile.TemporaryDirectory() as temp_dir:
        original_cwd = os.getcwd()
        os.chdir(temp_dir)
        try:
            with (
                patch("psycopg.connect", return_value=mock_connection),
                patch("subprocess.run", side_effect=mock_subprocess_run),
            ):
                result = postgresql.get_database_ddl(mock_db_credentials)
                assert os.path.exists(result)
                with open(result, "r", encoding="utf-8") as f:
                    content = f.read()
                assert "café, naïve" in content
                assert "test_üñíçødé" in content
        finally:
            os.chdir(original_cwd)