"""L0: db/connection read-only enforcement against real Postgres (testcontainers).

The ported read-only contract: the psd_ro role can SELECT but not write, and
can_create_in_schema / writable_tables reflect that. This is where 'ported code
is not assumed reliable' gains real teeth for the data layer.
"""
import pytest
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

from tht.db.connection import can_create_in_schema, ping, writable_tables

pytestmark = [pytest.mark.l0]


def test_ping_succeeds_on_read_only_role(ro_url):
    engine = create_engine(ro_url)
    try:
        ping(engine)  # SELECT 1 — must not raise
    finally:
        engine.dispose()


def test_read_only_role_cannot_create_in_schema(ro_url):
    engine = create_engine(ro_url)
    try:
        # psd_ro has USAGE + SELECT only, not CREATE on the dw schema.
        assert can_create_in_schema(engine, "dw") is False
    finally:
        engine.dispose()


def test_writable_tables_empty_for_read_only_role(ro_url):
    engine = create_engine(ro_url)
    try:
        tables = writable_tables(engine, "dw")
        assert tables == []  # read-only role has no INSERT/UPDATE/DELETE grants
    finally:
        engine.dispose()


def test_read_only_role_cannot_insert(ro_url):
    """The hard guarantee: a write attempt raises (enforced by Postgres, surfaced
    by our engine)."""
    engine = create_engine(ro_url)
    try:
        with pytest.raises(SQLAlchemyError), engine.begin() as conn:
            conn.execute(text('INSERT INTO dw.dim_pazienti VALUES (999, %s, %s)'),
                         ("test", "test"))
    finally:
        engine.dispose()


def test_admin_engine_can_create_in_schema(admin_engine):
    # Sanity: the admin (table owner) CAN create — confirms the test harness itself.
    assert can_create_in_schema(admin_engine, "dw") is True