"""Tests for MySQL src.logic."""

from unittest.mock import ANY
from unittest.mock import call

from pymysql import DatabaseError
from pymysql.constants import ER
import pytest
from src.logic.mysql import _create_user
from src.logic.mysql import _drop_user
from src.logic.mysql import _get_grants
from src.logic.mysql import _get_users
from src.logic.mysql import copy_users
from src.logic.mysql import run_scripts


@pytest.fixture
def mock_get_connection(mocker):
    """Return a mock for the MySQL connection."""
    return mocker.patch('src.logic.mysql._get_connection')


@pytest.fixture
def mock_get_users(mocker):
    """Return a mock for the _get_users function."""
    return mocker.patch('src.logic.mysql._get_users')


@pytest.fixture
def mock_get_grants(mocker):
    """Return a mock for the _get_grants function."""
    return mocker.patch('src.logic.mysql._get_grants')


@pytest.fixture
def mock_drop_user(mocker):
    """Return a mock for the _drop_user function."""
    return mocker.patch('src.logic.mysql._drop_user')


@pytest.fixture
def mock_create_user(mocker):
    """Return a mock for the _create_user function."""
    return mocker.patch('src.logic.mysql._create_user')


def test_run_scripts_multiple_files(mocker, mock_get_connection):
    """Test running multiple SQL scripts from a directory."""
    mock_cursor = mocker.MagicMock()
    mock_connection = mocker.MagicMock()
    mock_connection.cursor.return_value.__enter__.return_value = mock_cursor
    mock_get_connection.return_value = mock_connection

    credentials = {
        'host': 'db-host',
        'username': 'db-user',
        'password': 'db-password'
    }
    run_scripts(credentials, 'tests/resources/sql/multiple_files')

    mock_cursor.execute.assert_has_calls((
        call('CREATE DATABASE test;'),
        call('CREATE TABLE test.table (id int);'),
        call('insert into test.table (id) values (1);'),
        call('insert into test.table (id) values (2);')
    ))


def test_run_scripts_with_comments(mocker, mock_get_connection):
    """Test running SQL scripts containing comments."""
    mock_cursor = mocker.MagicMock()
    mock_connection = mocker.MagicMock()
    mock_connection.cursor.return_value.__enter__.return_value = mock_cursor
    mock_get_connection.return_value = mock_connection

    credentials = {
        'host': 'db-host',
        'username': 'db-user',
        'password': 'db-password'
    }
    run_scripts(credentials, 'tests/resources/sql/comments')

    mock_cursor.execute.assert_has_calls((
        call('SELECT 1+1;     # This comment continues to the end of line'),
        call('SELECT 1+1;     -- This comment continues to the end of line'),
        call('SELECT 1/* this is an in-line comment * */+1;'),
        call("""-- This line is just a comment
SELECT 1+
/*
this is a
multiple-line comment
*/
1;"""),
        call("SELECT 'these are # not comments';"),
        call("SELECT 'these are -- not comments';"),
        call("SELECT 'these are /* not comments */';"),
    ))


def test_copy_users(
        mock_get_connection,
        mock_get_users,
        mock_get_grants,
        mock_drop_user,
        mock_create_user):
    """Test copying of users."""
    mock_get_users.side_effect = [
        [
            {
                'user': 'srcuser1',
                'host': '%',
                'authentication_string': '*abc'},
            {
                'user': 'srcuser2',
                'host': '%',
                'authentication_string': '*def'
            }
        ],
        [
            {
                'user': 'targetuser1',
                'host': '%',
                'authentication_string': '*ghi'
            }
        ]
    ]
    mock_get_grants.side_effect = [
        [
            'GRANT ALL ON *.* TO srcuser1@%'
        ],
        [
            'GRANT SELECT ON a.b1 TO srcuser2@%',
            'GRANT INSERT ON a.b2 TO srcuser2@%'
        ]
    ]

    source_credentials = {
        'host': 'src',
        'username': 'src_connection_user',
        'password': 'src_connection_password'
    }
    target_credentials = {
        'host': 'target',
        'username': 'target_connection_user',
        'password': 'target_connection_password'
    }

    copy_users(source_credentials, target_credentials)

    mock_drop_user.assert_called_once_with(
        ANY, {
            'user': 'targetuser1',
            'host': '%',
            'authentication_string': '*ghi'
        })
    assert mock_create_user.call_count == 2
    mock_create_user.assert_any_call(
        ANY, {
            'user': 'srcuser1',
            'host': '%',
            'authentication_string': '*abc',
            'grants': [
                'GRANT ALL ON *.* TO srcuser1@%'
            ]
        })
    mock_create_user.assert_any_call(
        ANY, {
            'user': 'srcuser2',
            'host': '%',
            'authentication_string': '*def',
            'grants': [
                'GRANT SELECT ON a.b1 TO srcuser2@%',
                'GRANT INSERT ON a.b2 TO srcuser2@%'
            ]
        })


def test_get_users(mocker):
    """Test retrieval of users."""
    mock_cursor = mocker.MagicMock()
    mock_cursor.fetchall.return_value = [
        {
            'user': 'user',
            'host': '%',
            'authentication_string': '*abc',
            'plugin': 'mysql_native_password'
        }
    ]

    users = _get_users(mock_cursor, 'excludeme')

    mock_cursor.execute.assert_called_with(
        (
            'select user, host, plugin, authentication_string from mysql.user '
            '  where user not in ('
            "    'rdsadmin',"
            "    'rdsrepladmin',"
            "    'rdsrepladmin_priv_checks_user',"
            "    'rds_superuser_role',"
            "    'mysql.infoschema',"
            "    'mysql.session',"
            "    'mysql.sys',"
            "    'AWS_BEDROCK_ACCESS',"
            "    'AWS_COMPREHEND_ACCESS',"
            "    'AWS_LAMBDA_ACCESS',"
            "    'AWS_LOAD_S3_ACCESS',"
            "    'AWS_SAGEMAKER_ACCESS',"
            "    'AWS_SELECT_S3_ACCESS',"
            '    %s'
            '  )'
        ), 'excludeme')
    assert users == [
        {
            'user': 'user',
            'host': '%',
            'authentication_string': '*abc',
            'plugin': 'mysql_native_password'
        }
    ]


def test_get_grants(mocker):
    """Test retrieval of grants for a user."""
    mock_cursor = mocker.MagicMock()
    mock_cursor.fetchall.return_value = [
        ['GRANT SELECT ON a.b1 TO user@%'],
        ['GRANT INSERT ON a.b2 TO user@%']
    ]

    grants = _get_grants(
        mock_cursor,
        {
            'user': 'user',
            'host': '%',
            'authentication_string': '*abc'
        })

    mock_cursor.execute.assert_called_with(
        'show grants for %s@%s', ('user', '%'))
    assert grants == [
        'GRANT SELECT ON a.b1 TO user@%',
        'GRANT INSERT ON a.b2 TO user@%'
    ]


def test_drop_user(mocker):
    """Test dropping a user."""
    mock_cursor = mocker.MagicMock()

    _drop_user(mock_cursor, {
        'user': 'user',
        'host': '%',
        'authentication_string': '*abc'
    })

    mock_cursor.execute.assert_called_with('drop user %s@%s', ('user', '%'))


def test_create_user(mocker):
    """Test creation of a user."""
    mock_cursor = mocker.MagicMock()

    grants = [
        'GRANT SELECT ON a.b1 TO user@%',
        'GRANT INSERT ON a.b2 TO user@%'
    ]
    user = {
        'user': 'user',
        'host': '%',
        'authentication_string': '*abc',
        'grants': grants,
        'plugin': 'mysql_native_password'
    }
    _create_user(mock_cursor, user)

    assert mock_cursor.execute.call_count == 3
    mock_cursor.execute.assert_any_call(
        'create user %s@%s identified with %s as %s',
        ('user', '%', 'mysql_native_password', '*abc')
    )
    mock_cursor.execute.assert_any_call('GRANT SELECT ON a.b1 TO user@%')
    mock_cursor.execute.assert_any_call('GRANT INSERT ON a.b2 TO user@%')


def test_create_user_ignores_grant_errors_for_nonexistent_tables(mocker):
    """Test user creation with grants on non-existent tables."""
    mock_cursor = mocker.MagicMock()
    mock_cursor.execute.side_effect = [
        None,
        DatabaseError(ER.NO_SUCH_TABLE, 'Table does not exist')
    ]

    grants = [
        'GRANT SELECT ON a.b TO user@%',
    ]
    user = {
        'user': 'user',
        'host': '%',
        'authentication_string': '*abc',
        'grants': grants,
        'plugin': 'mysql_native_password'
    }
    _create_user(mock_cursor, user)
    assert mock_cursor.execute.call_count == 2


def test_create_user_ignores_grant_errors_for_nonexistent_columns(mocker):
    """Test user creation with grants on non-existent columns."""
    mock_cursor = mocker.MagicMock()
    mock_cursor.execute.side_effect = [
        None,
        DatabaseError(ER.BAD_FIELD_ERROR, 'Column does not exist')
    ]

    grants = [
        'GRANT SELECT (c) ON a.b TO user@%',
    ]
    user = {
        'user': 'user',
        'host': '%',
        'authentication_string': '*abc',
        'grants': grants,
        'plugin': 'mysql_native_password'
    }
    _create_user(mock_cursor, user)
    assert mock_cursor.execute.call_count == 2


def test_create_user_ignores_grant_errors_for_nonexistent_procedures(mocker):
    """Test user creation with grants on non-existent stored procedures."""
    mock_cursor = mocker.MagicMock()
    mock_cursor.execute.side_effect = [
        None,
        DatabaseError(ER.SP_DOES_NOT_EXIST, 'Procedure does not exist')
    ]

    grants = [
        'GRANT EXECUTE ON PROCEDURE a.b TO user@%',
    ]
    user = {
        'user': 'user',
        'host': '%',
        'authentication_string': '*abc',
        'grants': grants,
        'plugin': 'mysql_native_password'
    }
    _create_user(mock_cursor, user)
    assert mock_cursor.execute.call_count == 2


def test_create_user_fails_on_any_other_error(mocker):
    """Test user creation fails on other errors."""
    mock_cursor = mocker.MagicMock()
    mock_cursor.execute.side_effect = [
        None,
        DatabaseError(ER.DISK_FULL, 'Disk full')
    ]

    grants = [
        'GRANT SELECT ON a.b TO user@%',
    ]
    user = {
        'user': 'user',
        'host': '%',
        'authentication_string': '*abc',
        'grants': grants,
        'plugin': 'mysql_native_password'
    }

    with pytest.raises(DatabaseError):
        _create_user(mock_cursor, user)