"""Tests for PostgreSQL src.logic."""

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

import pytest
from src.logic.postgresql import MissingDatabaseDirective
from src.logic.postgresql import _grant_memberships
from src.logic.postgresql import _parse_target_database
from src.logic.postgresql import _upsert_role
from src.logic.postgresql import copy_users
from src.logic.postgresql import run_scripts


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


@pytest.fixture
def mock_get_roles(mocker):
    """Return a mock for the _get_roles function."""
    return mocker.patch('src.logic.postgresql._get_roles')


@pytest.fixture
def mock_get_memberships(mocker):
    """Return a mock for the _get_memberships function."""
    return mocker.patch('src.logic.postgresql._get_memberships')


@pytest.fixture
def mock_get_existing_role_names(mocker):
    """Return a mock for the _get_existing_role_names function."""
    return mocker.patch('src.logic.postgresql._get_existing_role_names')


@pytest.fixture
def mock_upsert_role(mocker):
    """Return a mock for the _upsert_role function."""
    return mocker.patch('src.logic.postgresql._upsert_role')


@pytest.fixture
def mock_grant_memberships(mocker):
    """Return a mock for the _grant_memberships function."""
    return mocker.patch('src.logic.postgresql._grant_memberships')


def test_run_scripts_multiple_files(mocker, mock_get_connection):
    """Test running multiple SQL scripts, connecting to the declared db."""
    mock_cursor = mocker.MagicMock()
    mock_connection = mocker.MagicMock()
    mock_connection.__enter__.return_value = mock_connection
    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'
    }
    count = run_scripts(credentials, 'tests/resources/sql/postgres')

    assert count == 2
    # The `-- @database: testdb` directive routes the connection.
    mock_get_connection.assert_has_calls((
        call(credentials, dbname='testdb'),
        call(credentials, dbname='testdb'),
    ), any_order=True)
    # sqlparse keeps the leading directive comment attached to the first
    # statement of each file, so assert on the SQL each command contains
    # rather than on exact strings.
    executed = [c.args[0] for c in mock_cursor.execute.call_args_list]
    assert any('CREATE TABLE sanitise_test (id int);' in c for c in executed)
    assert any(
        'insert into sanitise_test (id) values (1);' in c for c in executed)
    assert any(
        'insert into sanitise_test (id) values (2);' in c for c in executed)


def test_run_scripts_missing_directive_raises(mocker, tmp_path):
    """A script with no @database directive raises MissingDatabaseDirective."""
    (tmp_path / '01-test.sql').write_text('select 1;')
    credentials = {'host': 'h', 'username': 'u', 'password': 'p'}
    with pytest.raises(MissingDatabaseDirective):
        run_scripts(credentials, str(tmp_path))


def test_parse_target_database_returns_name():
    """The directive value is returned, even when not the first line."""
    body = '-- a leading comment\n-- @database: songwhip_api\nselect 1;'
    assert _parse_target_database(body, 'x.sql') == 'songwhip_api'


def test_parse_target_database_missing_raises():
    """A body without the directive raises naming the file."""
    with pytest.raises(MissingDatabaseDirective, match='x.sql'):
        _parse_target_database('select 1;', 'x.sql')


def test_copy_users(
        mock_get_connection,
        mock_get_roles,
        mock_get_memberships,
        mock_get_existing_role_names,
        mock_upsert_role,
        mock_grant_memberships):
    """Test copying of roles from source to target."""
    mock_get_roles.return_value = [
        {'rolname': 'srcrole1'},
        {'rolname': 'app_read'}
    ]
    mock_get_memberships.side_effect = [
        ['app_read'],
        []
    ]
    # app_read already exists on the target (restored from snapshot); srcrole1
    # is new.
    mock_get_existing_role_names.return_value = {'app_read'}

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

    count = copy_users(
        source_credentials, target_credentials,
        {'srcrole1': 'p1'})

    assert count == 2
    assert mock_upsert_role.call_count == 2
    # srcrole1 created (exists=False) with its mapped password; app_read
    # altered (exists=True) with no password (absent from the map).
    mock_upsert_role.assert_any_call(
        ANY,
        {'rolname': 'srcrole1', 'member_of': ['app_read']},
        False,
        'p1')
    mock_upsert_role.assert_any_call(
        ANY,
        {'rolname': 'app_read', 'member_of': []},
        True,
        None)
    assert mock_grant_memberships.call_count == 2


def _role(**overrides):
    """Return a role dict with sensible defaults for upsert tests."""
    role = {
        'rolname': 'app_user',
        'rolinherit': True,
        'rolcreaterole': False,
        'rolcreatedb': False,
        'rolcanlogin': True,
        'rolconnlimit': -1,
    }
    role.update(overrides)
    return role


def test_upsert_role_create(mocker):
    """Test a new role with no supplied password omits the PASSWORD clause."""
    mock_warning = mocker.patch('src.logic.postgresql.logger.warning')
    mock_cursor = mocker.MagicMock()
    _upsert_role(mock_cursor, _role(), exists=False)
    mock_cursor.execute.assert_called_once()
    statement = mock_cursor.execute.call_args[0][0].as_string(None)
    assert statement.lower().startswith('create role')
    assert 'LOGIN' in statement
    # No password supplied, so no PASSWORD clause is emitted.
    assert 'PASSWORD' not in statement
    # Superuser-only attributes are never emitted: the RDS master user cannot
    # set them, even in their negative form (see module docstring).
    assert 'SUPERUSER' not in statement
    assert 'REPLICATION' not in statement
    assert 'BYPASSRLS' not in statement
    # A new login role with no password is flagged.
    mock_warning.assert_called_once()


def test_upsert_role_create_with_password(mocker):
    """Test a new role with a supplied password sets it and is not flagged."""
    mock_warning = mocker.patch('src.logic.postgresql.logger.warning')
    mock_cursor = mocker.MagicMock()
    _upsert_role(mock_cursor, _role(), exists=False, password='s3cret')
    statement = mock_cursor.execute.call_args[0][0].as_string(None)
    assert statement.lower().startswith('create role')
    assert 'PASSWORD' in statement
    assert "'s3cret'" in statement
    # A login role created with a password does not need flagging.
    mock_warning.assert_not_called()


def test_upsert_role_alter_existing(mocker):
    """Test an existing role with no password is altered, password intact."""
    mock_warning = mocker.patch('src.logic.postgresql.logger.warning')
    mock_cursor = mocker.MagicMock()
    _upsert_role(mock_cursor, _role(), exists=True)
    mock_cursor.execute.assert_called_once()
    statement = mock_cursor.execute.call_args[0][0].as_string(None)
    assert statement.lower().startswith('alter role')
    # No password supplied: the ALTER leaves any existing password intact.
    assert 'PASSWORD' not in statement
    mock_warning.assert_not_called()


def test_upsert_role_alter_with_password(mocker):
    """Test a supplied password overwrites an existing role's password."""
    mock_cursor = mocker.MagicMock()
    _upsert_role(mock_cursor, _role(), exists=True, password='new_pw')
    statement = mock_cursor.execute.call_args[0][0].as_string(None)
    assert statement.lower().startswith('alter role')
    assert 'PASSWORD' in statement
    assert "'new_pw'" in statement


def test_upsert_role_nologin(mocker):
    """Test a group/NOLOGIN role emits NOLOGIN and is not flagged."""
    mock_warning = mocker.patch('src.logic.postgresql.logger.warning')
    mock_cursor = mocker.MagicMock()
    _upsert_role(mock_cursor, _role(rolcanlogin=False), exists=False)
    statement = mock_cursor.execute.call_args[0][0].as_string(None)
    assert 'NOLOGIN' in statement
    # A NOLOGIN role does not authenticate, so no password warning is needed.
    mock_warning.assert_not_called()


def test_grant_memberships(mocker):
    """Test granting role memberships."""
    mock_cursor = mocker.MagicMock()
    role = {'rolname': 'app_user', 'member_of': ['app_read', 'app_write']}
    _grant_memberships(mock_cursor, role)
    assert mock_cursor.execute.call_count == 2