"""Fixtures for PostgreSQL integration tests.

These tests run against the local postgres-source / postgres-target services
defined in docker-compose.yaml, exercising the role-copy and script-running
logic against a real database rather than mocks.
"""

import os

import psycopg
import pytest
from psycopg import sql


def _creds(host_env):
    """Build a credentials dict for the given host environment variable."""
    return {
        'host': os.environ[host_env],
        'username': os.environ['DB_MASTER_USER'],
        'password': os.environ['DB_MASTER_PASSWORD'],
    }


@pytest.fixture
def source_credentials():
    """Return master credentials for the source database."""
    return _creds('SOURCE_DB_HOST')


@pytest.fixture
def target_credentials():
    """Return master credentials for the target database."""
    return _creds('TARGET_DB_HOST')


def _connect(credentials):
    """Open an autocommit connection to the given database."""
    return psycopg.connect(
        host=credentials['host'],
        user=credentials['username'],
        password=credentials['password'],
        dbname='postgres',
        autocommit=True,
    )


def _create_login_role(rolname, password):
    """Compose a CREATE ROLE ... LOGIN PASSWORD statement for seeding."""
    return sql.SQL('create role {name} login password {password}').format(
        name=sql.Identifier(rolname),
        password=sql.Literal(password))


def _drop_test_roles(credentials):
    """Drop any roles left over from a previous run on the given database."""
    with _connect(credentials) as conn:
        with conn.cursor() as cur:
            # Reassign/own objects must go before the owning role is dropped.
            cur.execute('drop table if exists owned_by_app')
            for role in ('app_login', 'app_group', 'app_owner', 'extra_role',
                         'non_superuser'):
                cur.execute(f'drop role if exists {role}')


@pytest.fixture
def clean_databases(source_credentials, target_credentials):
    """Ensure both databases start and end free of the test roles."""
    _drop_test_roles(source_credentials)
    _drop_test_roles(target_credentials)
    yield
    _drop_test_roles(source_credentials)
    _drop_test_roles(target_credentials)


@pytest.fixture
def seeded_source(source_credentials, clean_databases):
    """Seed the source database with a representative set of roles.

    Covers the cases the unit tests cannot: a login role, a NOLOGIN group role
    used as a membership parent, a role that owns an object (so DROP ROLE would
    fail), and a membership edge. Source passwords are seeded so the roles are
    realistic, but copy_users does not migrate them from the source (the master
    user cannot read password hashes on RDS). The tests assert attribute and
    membership replication, that a password supplied in role_passwords is
    applied/overwritten, and that a role absent from that map keeps its own
    target password.
    """
    with _connect(source_credentials) as conn:
        with conn.cursor() as cur:
            cur.execute(
                'create role app_group nologin')
            cur.execute(_create_login_role('app_login', 'login_secret'))
            cur.execute(_create_login_role('app_owner', 'owner_secret'))
            # app_login is a member of the app_group group role.
            cur.execute('grant app_group to app_login')
            # app_owner owns an object; DROP ROLE would fail on the target.
            cur.execute(
                'create table owned_by_app (id int)')
            cur.execute(
                'alter table owned_by_app owner to app_owner')
    return source_credentials


@pytest.fixture
def non_superuser_target_credentials(target_credentials, clean_databases):
    """Credentials for a CREATEROLE-but-not-superuser role on the target.

    Local Docker postgres connects as a real superuser, which masks the RDS
    restriction that SUPERUSER/REPLICATION/BYPASSRLS can only be set by a true
    superuser. On RDS the master user is only rds_superuser. CREATEROLE is the
    closest local analogue: it can create/alter roles but, like rds_superuser,
    cannot set those superuser-only attributes. Running copy_users as this role
    proves the omitted clauses no longer trip the privilege check.
    """
    with _connect(target_credentials) as conn:
        with conn.cursor() as cur:
            cur.execute(sql.SQL(
                'create role non_superuser login createrole '
                'password {}').format(sql.Literal('np_secret')))
    return {
        'host': target_credentials['host'],
        'username': 'non_superuser',
        'password': 'np_secret',
    }