"""DB-backed test for the S3a data normalization (migration 0060): a JSONB scalar `null` in the
plex tag columns becomes SQL NULL, while a real array is left untouched. Runs the migration's exact
UPDATE statements against seeded rows (the migration itself is a no-op in the empty test DB).

Uses the DB lane (`db` fixture) — skipped when no Postgres is reachable.
"""
from sqlalchemy import text

from app.models import PlexLibrary, PlexShow

# Mirror of migration 0060's statements (plex_items runs against an empty table here — proving the
# SQL is valid for both tables; the assertions below check the seeded plex_shows rows).
_NORMALIZE = [
    f"UPDATE {t} SET {c} = NULL WHERE jsonb_typeof({c}) = 'null'"
    for t in ("plex_items", "plex_shows")
    for c in ("genres", "directors", "cast_names", "collection_keys")
]


def test_scalar_null_normalized_array_preserved(db):
    lib = PlexLibrary(plex_key="1", title="Shows", kind="show", enabled=True)
    db.add(lib)
    db.flush()
    db.add_all(
        [
            PlexShow(rating_key="null_row", library_id=lib.id, title="Null genres"),
            PlexShow(rating_key="array_row", library_id=lib.id, title="Real genres"),
        ]
    )
    db.commit()
    # Set a pre-0.53.1 JSONB scalar null on one, a genuine array on the other.
    db.execute(text("UPDATE plex_shows SET genres = 'null'::jsonb WHERE rating_key = 'null_row'"))
    db.execute(text("""UPDATE plex_shows SET genres = '["Drama"]'::jsonb WHERE rating_key = 'array_row'"""))
    db.commit()
    assert db.scalar(text("SELECT jsonb_typeof(genres) FROM plex_shows WHERE rating_key='null_row'")) == "null"

    for stmt in _NORMALIZE:
        db.execute(text(stmt))
    db.commit()

    # The scalar null is now SQL NULL (jsonb_typeof of SQL NULL is itself NULL, not 'null')...
    assert db.scalar(text("SELECT genres IS NULL FROM plex_shows WHERE rating_key='null_row'")) is True
    # ...and a real array is untouched.
    assert db.scalar(text("SELECT jsonb_typeof(genres) FROM plex_shows WHERE rating_key='array_row'")) == "array"